--------------------------------------------------
-- Issue documentation refactoring
-- 
-- Created 20091027, TNS
--------------------------------------------------

CREATE TABLE IF NOT EXISTS `issue_event` (
  `id` bigint(20) NOT NULL auto_increment,
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `user` varchar(12) NOT NULL,
  `system` varchar(8) NOT NULL,
  `client` varchar(3) NOT NULL,
  `event_type` bigint(20) NOT NULL,
  `issue_id` varchar(32) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `ix1` (`issue_id`),
  CONSTRAINT `fk10` FOREIGN KEY (`issue_id`) REFERENCES `cehior_auth_header` (`unikid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `issue_status` (
  `issue_event_id` bigint(20) NOT NULL,
  `status` varchar(10) NOT NULL,
  PRIMARY KEY  (`issue_event_id`),
  CONSTRAINT `fk` FOREIGN KEY (`issue_event_id`) REFERENCES `issue_event` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `issue_comment` (
  `issue_event_id` bigint(20) NOT NULL,
  `comment` text NOT NULL,
  PRIMARY KEY  (`issue_event_id`),
  CONSTRAINT `fk3` FOREIGN KEY (`issue_event_id`) REFERENCES `issue_event` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `issue_responsible` (
  `issue_event_id` bigint(20) NOT NULL,
  `responsible` varchar(10) NOT NULL,
  PRIMARY KEY  (`issue_event_id`),
  KEY `fk1` (`responsible`),
  KEY `fk200` (`issue_event_id`),
  CONSTRAINT `fk200` FOREIGN KEY (`issue_event_id`) REFERENCES `issue_event` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `issue_task` (
  `issue_event_id` bigint(20) NOT NULL,
  `task_id` bigint(20) NOT NULL,
  PRIMARY KEY  (`issue_event_id`),
  KEY `task` (`task_id`),
  CONSTRAINT `issue_task_ibfk_2` FOREIGN KEY (`task_id`) REFERENCES `cehior_auth_header_task` (`taeller`),
  CONSTRAINT `issue_task_ibfk_1` FOREIGN KEY (`issue_event_id`) REFERENCES `issue_event` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `cehior_auth_header` 
  ADD `tcode` varchar(48) default NULL,
  ADD `description` text;

