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

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

INSERT INTO request_specimen_detail (request_id, description, detail)
    SELECT request_id, 'gross_description', detail FROM hilis4.request_gross_description;       

# 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;

CREATE TEMPORARY TABLE temp LIKE request_report_detail;

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

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;

# 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_additional_diagnosis` (
  `request_id` int NOT NULL default '0',
  `diagnosis_id` smallint NOT NULL,
  `time` timestamp NOT NULL default '0000-00-00 00:00:00' on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`request_id`,`diagnosis_id`)
) ENGINE=InnoDB;

#ALTER TABLE `request_additional_diagnosis`
#	ADD CONSTRAINT `request_additional_diagnosis_ibfk_2` FOREIGN KEY (`diagnosis_id`) REFERENCES `diagnoses` (`id`),
#   ADD CONSTRAINT `request_additional_diagnosis_ibfk_1` FOREIGN KEY (`request_id`) REFERENCES `requests` (`id`) ON DELETE CASCADE;

# drop request_gross_description table; delete comment field from request_report;