# request_specimen_detail =====================================================
DROP TABLE IF EXISTS request_specimen_detail;
CREATE TABLE `request_specimen_detail` (
  `request_id` INT(11) NOT NULL default '0',
  `gross_description` TEXT NULL,
  `biopsy_site` TEXT NULL,
  `specimen_quality` ENUM('good','adequate','poor') NOT NULL DEFAULT 'adequate',
  PRIMARY KEY  (`request_id`)
) ENGINE=InnoDB;

INSERT INTO request_specimen_detail (request_id, gross_description, specimen_quality)
    SELECT t1.request_id, t2.detail, t1.specimen_quality
    FROM hilis4.request_report t1 LEFT JOIN hilis4.request_gross_description t2
        on t1.request_id = t2.request_id ORDER BY t1.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 =====================================================
DROP TABLE IF EXISTS request_report_detail;
CREATE TABLE `request_report_detail` (
	`request_id` INT(11) NOT NULL,
	`clinical_details` TEXT NULL,
	`morphology` TEXT NULL,
	`comment` TEXT NULL,
	`status` ENUM('new','relapse','default') NOT NULL DEFAULT 'default',
	`diagnosis_id` SMALLINT(6) NOT NULL,
	`created_at` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
	`updated_at` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
	PRIMARY KEY (`request_id`),
	INDEX `request_report_ibfk_2` (`diagnosis_id`)
) ENGINE=InnoDB;

INSERT INTO request_report_detail (request_id, clinical_details, comment,
        status, diagnosis_id, created_at, updated_at)
	SELECT request_id, clinical_details, comment, status, diagnosis_id, created_at,
        updated_at FROM request_report 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,
#   ADD CONSTRAINT `request_report_detail_ibfk_2` FOREIGN KEY (`diagnosis_id`) REFERENCES `diagnoses` (`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_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`)
) 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 DB class
# change all request_report to request_report_view in local.sql