[hmrn_rebuild_tables]
DROP TABLE IF EXISTS `imaging_options`;
CREATE TABLE `imaging_options` (
  `id` SMALLINT(6) NOT NULL AUTO_INCREMENT,
  `description` VARCHAR(50) NOT NULL DEFAULT '',
  `option_type` VARCHAR(50) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

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` VARCHAR(50) NOT NULL default '',
	`date` DATE NOT NULL DEFAULT '1901-01-01',
    PRIMARY KEY (`id`)
)
ENGINE=InnoDB;

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;

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;

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',
	PRIMARY KEY (`imaging_event_id`, `imaging_option_id`)
) ENGINE=InnoDB;

[hmrn_calculate_patient_age]
SELECT p.dob, pe.date as 'diagnosis'
FROM patients p
    JOIN ( patient_event pe JOIN events e on pe.event_id = e.id
        and e.description = 'diagnosis' )
    on pe.patient_id = p.id
WHERE patient_id = ?

[hmrn_patient_diagnostic_categories]
SELECT
  DISTINCT(t7.description)
FROM
  requests t1
  JOIN patient_case t2 ON (t1.patient_case_id = t2.id)
  JOIN patients t3 ON (t2.patient_id = t3.id)
  JOIN request_report_view t4 ON (t1.id = t4.request_id)
  JOIN diagnoses t5 ON (t4.diagnosis_id = t5.id)
  JOIN icdo_sub_category t6 ON (t5.icdo3 = t6.icdo3)
  JOIN diagnostic_categories t7 ON (t6.diagnostic_category_id = t7.id)
WHERE
  t2.patient_id = ?

[hmrn_imaging_options]
SELECT id, option_type
FROM imaging_options