# request_specimen_detail =====================================================
CREATE TABLE `request_specimen_detail` (
  `request_id` int NOT NULL default '0',
  `description` enum('biopsy_site','gross_description','specimen_quality') default NULL,
  `detail` varchar(255) NOT NULL,
  PRIMARY KEY  (`request_id`,`description`)
) ENGINE=InnoDB;

DROP TEMPORARY TABLE IF EXISTS temp;
CREATE TEMPORARY TABLE temp LIKE request_specimen_detail;

INSERT INTO temp (request_id, description, detail)
    SELECT request_id, 'gross_description', detail FROM hilis4.request_gross_description;
INSERT INTO temp (request_id, description, detail)
    SELECT request_id, 'specimen_quality', specimen_quality FROM hilis4.request_report;     
INSERT INTO request_specimen_detail (request_id, description, detail)
    SELECT request_id, description, detail FROM temp ORDER BY request_id;       

#ALTER TABLE `request_specimen_detail`
#	ADD CONSTRAINT `request_specimen_detail_ibfk_1` FOREIGN KEY (`request_id`) REFERENCES `requests` (`id`) ON DELETE CASCADE;

# request_report_detail =====================================================
CREATE TABLE `request_report_detail` (
  `request_id` int NOT NULL default '0',
  `description` enum('morphology','comment','clinical_details') default NULL,
  `detail` text,
  PRIMARY KEY  (`request_id`,`description`)
) ENGINE=InnoDB;

DROP TEMPORARY TABLE IF EXISTS temp;
CREATE TEMPORARY TABLE temp LIKE request_report_detail;

INSERT INTO temp (request_id, description, detail)
    SELECT request_id, 'morphology', comment FROM hilis4.request_report WHERE comment IS NOT NULL;       
INSERT INTO temp (request_id, description, detail)
    SELECT request_id, 'clinical_details', clinical_details FROM hilis4.request_report WHERE clinical_details IS NOT NULL;       
INSERT INTO request_report_detail (request_id, description, detail)
	SELECT request_id, description, detail FROM temp ORDER BY request_id;

#ALTER TABLE `request_report_detail`
#	ADD CONSTRAINT `request_report_detail_ibfk_1` FOREIGN KEY (`request_id`) REFERENCES `requests` (`id`) ON DELETE CASCADE;

# request_report_history  =====================================================
ALTER TABLE `request_report_history` ALTER `field` DROP DEFAULT;
ALTER TABLE `request_report_history` CHANGE COLUMN `field` `field` ENUM('morphology','comment') NULL AFTER `request_id`;
UPDATE request_report_history SET field = 'morphology' WHERE field = 'comment';

CREATE TABLE `request_secondary_diagnosis` (
  `request_id` int NOT NULL default '0',
  `secondary_diagnosis_id` smallint NOT NULL,
  `time` timestamp NOT NULL default '0000-00-00 00:00:00' on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`request_id`,`secondary_diagnosis_id`)
) ENGINE=InnoDB;

#ALTER TABLE `request_secondary_diagnosis`
#	ADD CONSTRAINT `request_secondary_diagnosis_ibfk_2` FOREIGN KEY (`secondary_diagnosis_id`) REFERENCES `diagnoses` (`id`),
#   ADD CONSTRAINT `request_secondary_diagnosis_ibfk_1` FOREIGN KEY (`request_id`) REFERENCES `requests` (`id`) ON DELETE CASCADE;

# drop request_gross_description table; delete comment field from request_report;