DROP TABLE IF EXISTS `imaging_options`;
CREATE TABLE `imaging_options` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `description` VARCHAR(50) NOT NULL DEFAULT '',
  `option_type` enum('upper_nodal','lower_nodal','extranodal','flag') DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

/* dumping data for imaging_options */	
INSERT INTO `imaging_options` (`id`, `description`, `option_type`) VALUES
	(1, 'cns', 'extranodal'),
	(2, 'git', 'extranodal'),
	(3, 'gu', 'extranodal'),
	(4, 'blood', 'extranodal'),
	(5, 'bone', 'extranodal'),
	(6, 'liver', 'extranodal'),
	(7, 'pulmonary_left', 'extranodal'),
	(8, 'pulmonary_right', 'extranodal'),
	(9, 'marrow', 'extranodal'),
	(10, 'orbit_left', 'extranodal'),
	(11, 'orbit_right', 'extranodal'),
	(12, 'skin', 'extranodal'),
	(13, 'salivary_gland_left', 'extranodal'),
	(14, 'salivary_gland_right', 'extranodal'),
	(15, 'thyroid', 'extranodal'),
	(16, 'pericardium', 'extranodal'),
	(17, 'muscle', 'extranodal'),
	(19, 'waldeyer', 'upper_nodal'),
	(20, 'neck_left', 'upper_nodal'),
	(21, 'neck_right', 'upper_nodal'),
	(22, 'infraclavicular_left', 'upper_nodal'),
	(23, 'infraclavicular_right', 'upper_nodal'),
	(24, 'axillary_left', 'upper_nodal'),
	(25, 'axillary_right', 'upper_nodal'),
	(26, 'arm_left', 'upper_nodal'),
	(27, 'arm_right', 'upper_nodal'),
	(28, 'thymus', 'upper_nodal'),
	(29, 'mediastinal', 'upper_nodal'),
	(30, 'hilar_left', 'upper_nodal'),
	(31, 'hilar_right', 'upper_nodal'),
	(32, 'para_aortic', 'lower_nodal'),
	(33, 'mesenteric', 'lower_nodal'),
	(34, 'iliac_left', 'lower_nodal'),
	(35, 'iliac_right', 'lower_nodal'),
	(36, 'inguinal_left', 'lower_nodal'),
	(37, 'inguinal_right', 'lower_nodal'),
	(38, 'popliteal_left', 'lower_nodal'),
	(39, 'popliteal_right', 'lower_nodal'),
	(40, 'spleen', 'lower_nodal');
INSERT INTO `imaging_options` (`id`, `description`, `option_type`) VALUES
	(41, 'extensive', 'flag'),
	(42, 'bulky', 'flag'),
	(43, 'check_scan', 'flag');

/* ========================================================================== */
DROP TABLE IF EXISTS `events`;
CREATE TABLE `events` (
  `id` SMALLINT(6) NOT NULL AUTO_INCREMENT,
  `description` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

/* dumping data for events */
INSERT INTO `events` (`id`, `description`) VALUES
	(1, 'diagnosis'),
	(2, 'first_appointment'),
	(3, 'palliative_care'),
	(4, 'deceased');

/* ========================================================================== */
DROP TABLE IF EXISTS `patient_event`;
CREATE TABLE `patient_event` (
	`patient_id` INT(11) NOT NULL DEFAULT '0',
	`event_id` SMALLINT(6) NOT NULL DEFAULT '0',
	`date` DATE,
    PRIMARY KEY(`patient_id`,`event_id`)
)
ENGINE=InnoDB;

/* dumping data for patient_event */
INSERT INTO patient_event(patient_id, event_id, `date`)
	SELECT patient_id, 1, diagnosed FROM patient_chronologies
        WHERE diagnosed IS NOT NULL;
INSERT INTO patient_event(patient_id, event_id, `date`)
	SELECT patient_id, 2, first_appointment FROM patient_chronologies
        WHERE first_appointment IS NOT NULL;
INSERT INTO patient_event(patient_id, event_id, `date`)
	SELECT patient_id, 3, palliative_care FROM patient_chronologies
        WHERE palliative_care IS NOT NULL;
INSERT INTO patient_event(patient_id, event_id, `date`)
	SELECT patient_id, 4, deceased FROM patient_chronologies
        WHERE deceased IS NOT NULL;

/* ========================================================================== */
DROP TABLE IF EXISTS `patient_imaging_event`;
CREATE TABLE `patient_imaging_event` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`patient_id` INT(11) NOT NULL DEFAULT '0',
	`dataset` SMALLINT(6) NOT NULL DEFAULT '1',
	`scan_type` ENUM('ct','pet') DEFAULT NULL,
	`stage` ENUM('initial','follow-up') NOT NULL DEFAULT 'initial',
	`date` DATE NOT NULL DEFAULT '1901-01-01',
    PRIMARY KEY (`id`),
	INDEX `patient_id` (`patient_id`),
	INDEX `patient_dataset_type` (`patient_id`, `dataset`, `scan_type`)
)
ENGINE=InnoDB;

/* dumping data for patient_imaging_event */
DROP TABLE IF EXISTS temp;
CREATE TEMPORARY TABLE temp(patient_id INT(11));
INSERT INTO temp(patient_id) SELECT patient_id FROM patient_staging_site;
INSERT INTO temp(patient_id) SELECT patient_id FROM patient_staging_other;
INSERT INTO temp(patient_id) SELECT patient_id FROM patient_sites_other;
INSERT INTO patient_imaging_event(patient_id, scan_type) /* others default */
    SELECT DISTINCT(patient_id), 'ct' FROM temp;

/* ========================================================================== */
DROP TABLE IF EXISTS `patient_imaging_option`;
CREATE TABLE `patient_imaging_option` (
	`imaging_event_id` INT(11) NOT NULL DEFAULT '0',
	`imaging_option_id` SMALLINT(6) NOT NULL DEFAULT '0',
	INDEX `imaging_event_id` (`imaging_event_id`)
) ENGINE=InnoDB;

/* dumping data for patient_imaging_site */
INSERT INTO patient_imaging_option(imaging_event_id, imaging_option_id)
    SELECT t2.id, t1.site_id FROM patient_staging_site t1 JOIN patient_imaging_event
        t2 on t1.patient_id = t2.patient_id;
INSERT INTO patient_imaging_option(imaging_event_id, imaging_option_id)
    SELECT t2.id, 41 FROM patient_staging_other t1 JOIN patient_imaging_event
        t2 on t1.patient_id = t2.patient_id WHERE t1.detail = 'extensive';
INSERT INTO patient_imaging_option(imaging_event_id, imaging_option_id)
    SELECT t2.id, 42 FROM patient_staging_other t1 JOIN patient_imaging_event
        t2 on t1.patient_id = t2.patient_id WHERE t1.detail = 'bulky';
INSERT INTO patient_imaging_option(imaging_event_id, imaging_option_id)
    SELECT t2.id, 43 FROM patient_staging_other t1 JOIN patient_imaging_event
        t2 on t1.patient_id = t2.patient_id WHERE t1.detail = 'check_ct';

/* ========================================================================== */
DROP TABLE IF EXISTS `patient_imaging_other`;
CREATE TABLE `patient_imaging_other` (
	`imaging_event_id` INT(11) NOT NULL DEFAULT '0',
	`details` VARCHAR(255) NOT NULL DEFAULT '',
	INDEX `imaging_event_id` (`imaging_event_id`)
)
ENGINE=InnoDB;

/* dumping data for patient_imaging_other */
INSERT INTO patient_imaging_other(imaging_event_id, details)
	SELECT t2.id, t1.details FROM patient_sites_other t1 JOIN patient_imaging_event
        t2 on t1.patient_id = t2.patient_id WHERE LENGTH(details) >= 3; /* eliminate blanks */