--------------------------------------------------
-- Create Issue Event table for task status update
-- Created 20100628
--------------------------------------------------

CREATE TABLE IF NOT EXISTS `issue_task_status` (
  `issue_event_id` bigint(20) NOT NULL,
  `task_id` bigint(20) NOT NULL,
  `state` varchar(10) NOT NULL,
  PRIMARY KEY  (`issue_event_id`),
  KEY `task` (`task_id`),
  CONSTRAINT `issue_task_stfk_1` FOREIGN KEY (`issue_event_id`) REFERENCES `issue_event` (`id`),
  CONSTRAINT `issue_task_stfk_2` FOREIGN KEY (`task_id`) REFERENCES `cehior_auth_header_task` (`taeller`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `cehior_auth_task_change_role_data` 
  ADD `event_id` BIGINT( 20 ) NULL AFTER `header_id` ;
  
ALTER TABLE `cehior_auth_task_change_role_data_org` 
  ADD `event_id` BIGINT( 20 ) NULL AFTER `header_id` ;
  
UPDATE `cehior_auth_task_change_role_data` 
  SET `event_id` = (
    SELECT id
    FROM issue_event AS e INNER JOIN issue_task AS t
    ON e.id = t.issue_event_id
    WHERE e.event_type = 7 AND task_id = header_id
  );

UPDATE `cehior_auth_task_change_role_data_org` 
  SET `event_id` = (
    SELECT id
    FROM issue_event AS e INNER JOIN issue_task AS t
    ON e.id = t.issue_event_id
    WHERE e.event_type = 7 AND task_id = header_id
  );

ALTER TABLE `cehior_auth_task_change_role_data` 
  CHANGE `event_id` `event_id` BIGINT( 20 ) NOT NULL ;
  
ALTER TABLE `cehior_auth_task_change_role_data_org` 
  CHANGE `event_id` `event_id` BIGINT( 20 ) NOT NULL ;
  
