#===============================================================================
# view 'definer' user MUST exist in mysql.users or views will be inaccessible
# split into tables, views, functions & foreign_key sections
#===============================================================================

$SQL_FOR_TABLE = {

    additional_options => q!
        CREATE TABLE `additional_options` (
          `id` smallint NOT NULL auto_increment,
          `option_name` varchar(255) default NULL,
          `option_label` varchar(255) default NULL,
          `is_active` enum('yes','no') NOT NULL default 'yes',
          PRIMARY KEY  (`id`),
		  UNIQUE KEY `option_label` (`option_label`),
          UNIQUE KEY `option_name` (`option_name`)
        ) ENGINE=InnoDB!,

	admin_log => q!
		CREATE TABLE `admin_log` (
			`id` INT(11) NOT NULL AUTO_INCREMENT,
			`action` VARCHAR(255) NOT NULL DEFAULT '',
			`user_id` SMALLINT(6) NOT NULL DEFAULT '0',
			`time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
			PRIMARY KEY (`id`),
			INDEX `user_id` (`user_id`),
			INDEX `time` (`time`)
		) ENGINE=InnoDB!,

	admin_messages => q!
        CREATE TABLE `admin_messages` (
            `id` INT(11) NOT NULL auto_increment,
            `message` TEXT NOT NULL,
			`valid_until` date default NULL,
			`user_group` enum('internal','external','all') NOT NULL default 'all',
            `time` TIMESTAMP NOT NULL,
            PRIMARY KEY (`id`)
		) ENGINE=InnoDB!,

    anatomical_sites => q!
        CREATE TABLE `anatomical_sites` (
            `id` SMALLINT(6) NOT NULL AUTO_INCREMENT,
            `site_name` VARCHAR(50) NOT NULL,
            `snomed` VARCHAR(6) NOT NULL,
            PRIMARY KEY (`id`),
            UNIQUE INDEX `site_name` (`site_name`),
            INDEX `snomed_code` (`snomed`)
        ) ENGINE=InnoDB!,

    audit_request_categories => q!
        CREATE TABLE `audit_request_categories` (
          `id` smallint NOT NULL auto_increment,
          `description` varchar(255) NOT NULL default '',
          `active` enum('yes','no') NOT NULL default 'yes',
          PRIMARY KEY  (`id`),
          UNIQUE KEY `description` (`description`)
        ) ENGINE=InnoDB!,

    audit_request_options => q!
        CREATE TABLE `audit_request_options` (
          `id` smallint NOT NULL auto_increment,
          `description` varchar(255) NOT NULL default '',
          `category_id` smallint NOT NULL default '0',
          `active` enum('yes','no') NOT NULL default 'yes',
          PRIMARY KEY  (`id`),
          UNIQUE KEY `description` (`description`),
          KEY `category_id` (`category_id`)
        ) ENGINE=InnoDB!,

    clinical_trials => q!
        CREATE TABLE `clinical_trials` (
          `id` smallint NOT NULL auto_increment,
          `trial_name` varchar(50) default NULL,
          `active` enum('yes','no') NOT NULL default 'yes',
          PRIMARY KEY  (`id`),
          UNIQUE KEY `trial_name` (`trial_name`)
        ) ENGINE=InnoDB!,

	consent_options => q!
		CREATE TABLE `consent_options` (
		  `id` smallint NOT NULL auto_increment,
		  `consent_name` varchar(255) NOT NULL default '',
		  `consent_label` varchar(255) NOT NULL default '',
          `is_active` enum('yes','no') NOT NULL default 'yes',
		  PRIMARY KEY  (`id`),
		  UNIQUE KEY `consent_name` (`consent_name`),
		  UNIQUE KEY `consent_label` (`consent_label`)
		) ENGINE=InnoDB!,

	context_warning_diagnosis => q!
		CREATE TABLE `context_warning_diagnosis` (
		  `id` smallint NOT NULL auto_increment,
		  `diagnosis_id` smallint NOT NULL default '0',
		  `context_warning_id` smallint NOT NULL default '0',
		  PRIMARY KEY  (`id`),
		  UNIQUE KEY  (`diagnosis_id`),
		  KEY `context_warning_id` (`context_warning_id`)
		) ENGINE=InnoDB!,

	deleted_requests => q!
		CREATE TABLE `deleted_requests` (
			`id` int(11) NOT NULL auto_increment,
			`request_id` int(11) NOT NULL default '0',
			`request_number` smallint(6) NOT NULL default '0',
			`year` year(4) NOT NULL default '0000',
			`action` text,
			`user_id` smallint(6) NOT NULL default '0',
			`time` datetime default NULL,
		PRIMARY KEY  (`id`)
		) ENGINE=InnoDB!,

	diagnosis_change_options => q!
		CREATE TABLE `diagnosis_change_options` (
		  `id` smallint NOT NULL auto_increment,
		  `option_name` varchar(25) NOT NULL,
		  `description` varchar(255) NOT NULL,
          `is_active` enum('yes','no') NOT NULL default 'yes',
		  PRIMARY KEY  (`id`),
		  UNIQUE KEY `option_name` (`option_name`)
		) ENGINE=InnoDB!,

	diagnosis_context_warnings => q!
		CREATE TABLE `diagnosis_context_warnings` (
		  `id` smallint NOT NULL auto_increment,
		  `description` varchar(255) NOT NULL default '',
          `is_active` enum('yes','no') default 'yes',
		  PRIMARY KEY  (`id`),
		  UNIQUE KEY `description` (`description`)
		) ENGINE=InnoDB!,

    diagnosis_lab_test => q!
        CREATE TABLE `diagnosis_lab_test` (
            `diagnosis_id` SMALLINT(6) NOT NULL DEFAULT '0',
            `lab_test_id` SMALLINT(6) NOT NULL DEFAULT '0',
            PRIMARY KEY (`diagnosis_id`, `lab_test_id`),
            INDEX `diagnosis_id` (`diagnosis_id`)
        )
        COLLATE='latin1_swedish_ci'
        ENGINE=InnoDB!,

    diagnoses => q!
        CREATE TABLE `diagnoses` (
          `id` smallint NOT NULL auto_increment,
          `name` varchar(255) default NULL,
          `icdo3` varchar(6) default NULL,
          `diagnostic_category_id` smallint default NULL,
          `active` enum('yes','no') default 'yes',
          PRIMARY KEY  (`id`),
          UNIQUE KEY `name` (`name`),
		  KEY `icdo3` (`icdo3`)
        ) ENGINE=InnoDB!,

    diagnostic_categories => q!
        CREATE TABLE `diagnostic_categories` (
          `id` smallint NOT NULL auto_increment,
          `description` varchar(255) default NULL,
		  `category_type` enum('main','sub') default NULL,
          `active` enum('yes','no') default 'yes',
          PRIMARY KEY  (`id`),
          UNIQUE KEY `description` (`description`)
        ) ENGINE=InnoDB!,

    draft_report_users => q!
        CREATE TABLE `draft_report_users` (
            `id` SMALLINT(6) NOT NULL AUTO_INCREMENT,
            `username` VARCHAR(50) NOT NULL DEFAULT 'username',
            `first_name` VARCHAR(50) NOT NULL DEFAULT 'first_name',
            `last_name` VARCHAR(50) NOT NULL DEFAULT 'last_name',
            `password` VARCHAR(32) NOT NULL DEFAULT 'password',
            `email` VARCHAR(50) NOT NULL,
			`last_login` TIMESTAMP NULL DEFAULT NULL,
            `active` ENUM('yes','no') NOT NULL DEFAULT 'yes',
            PRIMARY KEY (`id`),
            UNIQUE INDEX `username` (`username`),
            UNIQUE INDEX `login_id` (`first_name`, `last_name`),
            UNIQUE INDEX `email` (`email`)
        ) ENGINE=InnoDB!,

	email_addresses => q!
		CREATE TABLE `email_addresses` (
			`address` varchar(255) NOT NULL default '',
			PRIMARY KEY  (`address`)
		) ENGINE=InnoDB!,

	email_contacts => q!
		CREATE TABLE `email_contacts` (
		  `id` smallint NOT NULL auto_increment,
		  `display_name` varchar(255) NOT NULL default '',
		  `referral_source_id` smallint NOT NULL default '0',
		  `type` enum('mdt','report') default NULL,
		  `scope` enum('hospital','organisation','department') default NULL,
          `department_id` SMALLINT(6) NULL DEFAULT NULL,
		  `contact_address` varchar(255) NOT NULL default '',
          `status` ENUM('all','new', 'manual') NOT NULL DEFAULT 'all',
		  `is_active` enum('yes','no') NOT NULL default 'yes',
		  PRIMARY KEY  (`id`),
          UNIQUE INDEX `src_type_department_address`
            (`referral_source_id`, `type`, `department_id`, `contact_address`),
          INDEX `contact_address` (`contact_address`),
          INDEX `referral_source_id` (`referral_source_id`),
          INDEX `department_id` (`department_id`)
		) ENGINE=InnoDB!,

    error_codes => q!
        CREATE TABLE `error_codes` (
          `id` smallint NOT NULL auto_increment,
          `code` varchar(4) default NULL,
          `description` varchar(255) default NULL,
          `is_unique` enum('yes','no') NOT NULL default 'yes',
          `active` enum('yes','no') NOT NULL default 'yes',
          PRIMARY KEY  (`id`),
          UNIQUE KEY `code` (`code`)
        ) ENGINE=InnoDB!,

	error_code_assignment => q!
		CREATE TABLE `error_code_assignment` (
		  `type` enum('patient','new_request','request_edit','errors') default NULL,
		  `error_code_id` smallint NOT NULL default '0',
		  UNIQUE KEY `error_code_type` (`type`,`error_code_id`),
		  KEY `error_code_id` (`error_code_id`)
		) ENGINE=InnoDB!,
	
	general_comms_log =>q!
		CREATE TABLE `general_comms_log` (
		`id` INT(11) NOT NULL AUTO_INCREMENT,
		`user_id` SMALLINT(6) NOT NULL DEFAULT '0',
		`details` TEXT NOT NULL,
		`time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
		PRIMARY KEY (`id`),
		INDEX `user_id` (`user_id`)
		) ENGINE=InnoDB!,

    hospital_departments => q!
        CREATE TABLE `hospital_departments` (
          `id` smallint NOT NULL default '0',
          `display_name` varchar(50) NOT NULL default '',
          PRIMARY KEY  (`id`),
          UNIQUE KEY `display_name` (`display_name`(20))
        ) ENGINE=InnoDB!,

	icdo_sub_category => q!
		CREATE TABLE `icdo_sub_category` (
		  `diagnostic_category_id` smallint NOT NULL,
		  `icdo3` varchar(6) NOT NULL,
          PRIMARY KEY  (`icdo3`)
        ) ENGINE=InnoDB!,

    lab_section_sample_type => q!
		CREATE TABLE `lab_section_sample_type` (
            `lab_section_id` smallint NOT NULL default '0',
            `sample_type_id` smallint NOT NULL default '0',
		PRIMARY KEY  (`sample_type_id`, `lab_section_id`)
        ) ENGINE=InnoDB!,

	lab_section_status_option => q!
		CREATE TABLE `lab_section_status_option` (
		  `lab_section_id` smallint(6) NOT NULL default '0',
		  `status_option_id` smallint(6) NOT NULL default '0',
          `position` SMALLINT(6) NULL,
		  PRIMARY KEY  (`lab_section_id`,`status_option_id`),
		  KEY `status_option_id` (`status_option_id`),
          UNIQUE KEY `section_position` (`lab_section_id`, `position`)
		) ENGINE=InnoDB!,

    lab_sections => q!
        CREATE TABLE `lab_sections` (
          `id` smallint NOT NULL auto_increment,
          `section_name` varchar(255) NOT NULL,
		  `has_result_summary` enum('yes','no') NOT NULL default 'no',
		  `has_section_notes` enum('yes','no') NOT NULL default 'no',
		  `has_test_sign_out` enum('yes','no') NOT NULL default 'no',
		  `has_foreign_id` enum('yes','no') NOT NULL default 'no',
		  `has_results_import` enum('yes','no') NOT NULL default 'no',
		  `has_labels` enum('yes','no') NOT NULL default 'no',
		  `auto_expand` enum('yes','no') NOT NULL default 'no',
          `is_active` enum('yes','no') NOT NULL default 'yes',
          PRIMARY KEY  (`id`),
          UNIQUE KEY `section_name` (`section_name`)
        ) ENGINE=InnoDB!,

    lab_test_accreditation => q!
        CREATE TABLE `lab_test_accreditation` (
            `lab_test_id` SMALLINT(6) NOT NULL,
            `accreditation_date` DATE NOT NULL,
            PRIMARY KEY (`lab_test_id`)
        ) ENGINE=InnoDB!,

	lab_test_data_type => q!
		CREATE TABLE `lab_test_data_type` (
		  `lab_test_id` smallint(6) NOT NULL default '0',
		  `data_type_id` smallint(6) NOT NULL default '0',
		  `is_active` enum('yes','no') NOT NULL default 'yes',
		  PRIMARY KEY  (`lab_test_id`),
		  KEY `data_type_id` (`data_type_id`)
		) ENGINE=InnoDB!,

	lab_test_result_data_types => q!
		CREATE TABLE `lab_test_result_data_types` (
		  `id` smallint NOT NULL auto_increment,
		  `description` varchar(25) NOT NULL default '',
		  `is_active` enum('yes','no') NOT NULL default 'yes',
		  PRIMARY KEY  (`id`),
		  UNIQUE KEY `description` (`description`)
		) ENGINE=InnoDB!,

	lab_test_result_options => 	q!
		CREATE TABLE `lab_test_result_options` (
		  `id` smallint NOT NULL auto_increment,
		  `data_type_id` smallint NOT NULL default '0',
		  `value` varchar(30) NOT NULL default '',
		  `is_active` enum('yes','no') NOT NULL default 'yes',
		  PRIMARY KEY  (`id`),
		  UNIQUE KEY `data_type_value` (`data_type_id`,`value`)
		) ENGINE=InnoDB!,

    lab_test_sample_type => q!
        CREATE TABLE `lab_test_sample_type` (
            `lab_test_id` SMALLINT(6) NOT NULL DEFAULT '0',
            `sample_type_id` SMALLINT(6) NOT NULL DEFAULT '0',
            PRIMARY KEY (`sample_type_id`, `lab_test_id`),
            INDEX `lab_test_id` (`lab_test_id`)
        )
        ENGINE=InnoDB!,

	lab_test_status_options => q!
		CREATE TABLE `lab_test_status_options` (
		  `id` smallint NOT NULL auto_increment,
		  `description` varchar(255) NOT NULL default '',
		  `is_editable` enum('yes','no') NOT NULL default 'yes',
		  `is_active` enum('yes','no') NOT NULL default 'yes',
		  PRIMARY KEY  (`id`),
		  UNIQUE KEY `description` (`description`)
		) ENGINE=InnoDB!,

    lab_tests => q!
        CREATE TABLE `lab_tests` (
          `id` smallint NOT NULL auto_increment,
          `test_name` varchar(25) NOT NULL,
          `field_label` varchar(25) NOT NULL,
          `lab_section_id` smallint NOT NULL default '0',
		  `test_type` enum('test','panel') default NULL,
		  `has_results` enum('yes','no') default NULL,
          `is_active` enum('yes','no') NOT NULL default 'yes',
          PRIMARY KEY  (`id`),
		  UNIQUE KEY `label_section_type` (`lab_section_id`,`test_type`,`field_label`),
          UNIQUE KEY `name_section_type`  (`lab_section_id`,`test_type`,`test_name`),
          KEY `lab_section_id` (`lab_section_id`)
        ) ENGINE=InnoDB!,

    linked_lab_test => q!
        CREATE TABLE `linked_lab_test` (
            `parent_test_id` SMALLINT(6) NOT NULL DEFAULT '0',
            `linked_test_id` SMALLINT(6) NOT NULL DEFAULT '0',
            PRIMARY KEY (`parent_test_id`, `linked_test_id`),
            INDEX `linked_lab_test` (`linked_test_id`)
        ) ENGINE=InnoDB!,

	local_network_locations => q!
		CREATE TABLE `local_network_locations` (
			`parent_id` smallint(6) NOT NULL default '0',
			PRIMARY KEY  (`parent_id`)
		) ENGINE=InnoDB!,

    logins => q!
        CREATE TABLE `logins` (
          `id` int NOT NULL auto_increment,
          `user_id` smallint NOT NULL default '0',
          `address` varchar(15) default NULL,
          `browser` varchar(255) default NULL,
          `session_id` char(32) default NULL,
          `time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
          PRIMARY KEY  (`id`)
        ) ENGINE=InnoDB!,

	new_national_code => q!
		CREATE TABLE `new_national_code` (
			`national_code` VARCHAR(8) NOT NULL,
			`type` ENUM('referrer','location') NOT NULL,
			`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
			UNIQUE INDEX `national_code` (`national_code`)
		) ENGINE=InnoDB!,

    ngis_clinical_indications => q!
        CREATE TABLE `ngis_clinical_indications` (
            `id` SMALLINT(6) NOT NULL AUTO_INCREMENT,
            `code` CHAR(4) NOT NULL,
            `category` VARCHAR(255) NOT NULL,
            PRIMARY KEY (`id`),
            UNIQUE INDEX `indication_id` (`code`),
            UNIQUE INDEX `category` (`category`)
        ) ENGINE=InnoDB!,

	ngis_diagnosis => q!
		 CREATE TABLE `ngis_diagnosis` (
			`diagnosis_id` SMALLINT(6) NOT NULL,
			`ngis_indication_id` SMALLINT(6) NOT NULL,
			PRIMARY KEY (`diagnosis_id`),
			INDEX `ngis_indication_id` (`ngis_indication_id`)
		) ENGINE=InnoDB!,

   ngis_lab_test => q!
        CREATE TABLE `ngis_lab_test` (
            `id` SMALLINT(6) NOT NULL AUTO_INCREMENT,
            `lab_test_id` SMALLINT(6) NOT NULL,
            `ngis_test_code` VARCHAR(6) NOT NULL,
            PRIMARY KEY (`id`),
            UNIQUE INDEX `lab_test_ngis_code` (`ngis_test_code`, `lab_test_id`),
            INDEX `lab_test_id` (`lab_test_id`)
        ) ENGINE=InnoDB!,

    panel_lab_test => q!
        CREATE TABLE `panel_lab_test` (
        `panel_test_id` SMALLINT(6) NOT NULL DEFAULT '0',
        `lab_test_id` SMALLINT(6) NOT NULL DEFAULT '0',
        PRIMARY KEY (`panel_test_id`, `lab_test_id`),
        INDEX `lab_test_id` (`lab_test_id`)
    ) ENGINE=InnoDB!,

	parent_organisations => q!
		CREATE TABLE `parent_organisations` (
		  `id` smallint NOT NULL auto_increment,
		  `parent_code` varchar(6) NOT NULL default '',
		  `description` varchar(255) NOT NULL default '',
		  `referral_type_id` smallint NOT NULL default '0',
		  PRIMARY KEY  (`id`),
		  UNIQUE KEY `description` (`description`),
		  UNIQUE KEY `parent_code` (`parent_code`)
		) ENGINE=InnoDB!,

    patient_bcr_abl => q!
        CREATE TABLE `patient_bcr_abl` (
            `nhs_number` VARCHAR(10) NOT NULL,
            `diagnosis_date` DATE NOT NULL,
            `first_line_date` DATE NULL DEFAULT NULL,
            `treatment` VARCHAR(50) NOT NULL,
            `line_number` VARCHAR(50) NOT NULL,
            `phase` VARCHAR(50) NOT NULL,
            `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (`nhs_number`)
        ) ENGINE=InnoDB!,

    patient_case => q!
        CREATE TABLE `patient_case` (
          `id` int NOT NULL auto_increment,
          `patient_id` int NOT NULL default '0',
          `referral_source_id` smallint NOT NULL default '0',
          `unit_number` varchar(255) NOT NULL default 'UNKNOWN',
          `time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
          PRIMARY KEY  (`id`),
          UNIQUE KEY `case` (`patient_id`,`unit_number`,`referral_source_id`),
          KEY `patient_id` (`patient_id`),
          KEY `referral_source_id` (`referral_source_id`)
        ) ENGINE=InnoDB!,

	patient_demographics => q!
		CREATE TABLE `patient_demographics` (
		  `patient_id` int(11) NOT NULL default '0',
		  `address` varchar(255) default NULL,
		  `post_code` varchar(8) default NULL,
		  `contact_number` varchar(15) default NULL,
		  `gp_id` int(11) NOT NULL default '0',
		  `practice_id` smallint(6) NOT NULL default '0',
		  `status` enum('alive','dead') NOT NULL default 'alive',
		  `dod` date default NULL,
		  `time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
		  PRIMARY KEY  (`patient_id`),
		  KEY `practice_id` (`practice_id`)
		) ENGINE=InnoDB!,

    patient_demographic_history => q!
        CREATE TABLE `patient_demographic_history` (
          `id` int(11) NOT NULL auto_increment,
          `patient_id` int(11) NOT NULL default '0',
          `user_id` smallint(6) NOT NULL default '0',
          `action` varchar(255) NOT NULL default '',
          `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
          PRIMARY KEY  (`id`)
        ) ENGINE=InnoDB!,

    patient_edits => q!
        CREATE TABLE `patient_edits` (
          `id` int NOT NULL auto_increment,
          `patient_id` int NOT NULL default '0',
          `last_name` varchar(50) default NULL,
          `first_name` varchar(50) default NULL,
          `middle_name` varchar(50) default NULL,
          `dob` date default NULL,
          `gender` enum('M','F','U') default 'U',
          `nhs_number` varchar(10) default NULL,
          `user_id` smallint NOT NULL,
          `error_code_id` smallint NOT NULL,
          `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
          PRIMARY KEY  (`id`),
          KEY `user_id` (`user_id`),
          KEY `patient_id` (`patient_id`)
        ) ENGINE=InnoDB!,

    patient_notes => q!
        CREATE TABLE `patient_notes` (
            `patient_id` INT(11) NOT NULL DEFAULT '0',
            `detail` TEXT NOT NULL,
            PRIMARY KEY (`patient_id`)
        ) ENGINE=InnoDB!,

    patient_trial => q!
        CREATE TABLE `patient_trial` (
          `patient_id` int NOT NULL default '0',
          `trial_id` smallint NOT NULL default '0',
          `trial_number` varchar(255) default NULL,
          PRIMARY KEY  (`patient_id`,`trial_id`),
          KEY `trial_id` (`trial_id`)
        ) ENGINE=InnoDB!,

    patients => q!
        CREATE TABLE `patients` (
          `id` int NOT NULL auto_increment,
          `last_name` varchar(50) default NULL,
          `first_name` varchar(50) default NULL,
          `middle_name` varchar(50) default NULL,
          `dob` date default NULL,
          `gender` enum('M','F','U') default 'U',
          `nhs_number` varchar(10) default NULL,
          `created_at` timestamp NOT NULL default '0000-00-00 00:00:00',
          `updated_at` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
          PRIMARY KEY  (`id`),
          KEY `name` (`last_name`,`first_name`),
          UNIQUE KEY `nhs_number` (`nhs_number`)
        ) ENGINE=InnoDB!,

	phone_log_options => q!
		CREATE TABLE `phone_log_options` (
		  `id` smallint NOT NULL auto_increment,
		  `description` varchar(255) NOT NULL default '',
		  `is_active` enum('yes','no') NOT NULL default 'yes',
		  PRIMARY KEY  (`id`),
		  UNIQUE KEY `option` (`description`)
		) ENGINE=InnoDB!,

	pre_registration => q!
		CREATE TABLE `pre_registration` (
		  `id` int NOT NULL auto_increment,
		  `labno` varchar(8) NOT NULL default '',
		  `surname` varchar(25) default NULL,
		  `is_screened` enum('yes','no') NOT NULL default 'no',
		  `time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
		  PRIMARY KEY  (`id`),
		  UNIQUE KEY `labno` (`labno`)
		) ENGINE=InnoDB!,

	pre_registration_lab_test => q!
		CREATE TABLE `pre_registration_lab_test` (
		  `id` int NOT NULL auto_increment,
		  `reg_specimen_id` int NOT NULL default '0',
		  `lab_test_id` smallint NOT NULL default '0',
		  PRIMARY KEY  (`id`),
		  UNIQUE KEY `reg_specimen_id` (`reg_specimen_id`,`lab_test_id`),
		  KEY `lab_test_id` (`lab_test_id`)
		) ENGINE=InnoDB!,

	pre_registration_specimen => q!
		CREATE TABLE `pre_registration_specimen` (
		  `id` int(11) NOT NULL auto_increment,
		  `pre_reg_id` int(11) NOT NULL default '0',
		  `specimen_code` char(2) NOT NULL default '0',
		  PRIMARY KEY  (`id`),
		  UNIQUE KEY `pre_reg_specimen` (`pre_reg_id`,`specimen_code`)
		) ENGINE=InnoDB!,

    referral_sources => q!
		CREATE TABLE `referral_sources` (
			`id` smallint NOT NULL auto_increment,
			`display_name` varchar(255) NOT NULL default '',
			`organisation_code` varchar(6) NOT NULL default '',
			`parent_organisation_id` smallint NOT NULL default '0',
			`referral_type_id` smallint NOT NULL default '0',
           `is_active` enum('yes','no') NOT NULL default 'yes',
			PRIMARY KEY  (`id`),
			UNIQUE KEY `organisation_code` (`organisation_code`),
			UNIQUE KEY `display_name` (`display_name`),
			KEY `referral_type_id` (`referral_type_id`),
			KEY `parent_organisation_id` (`parent_organisation_id`)
		) ENGINE=InnoDB!,

	referral_types => q!
		CREATE TABLE `referral_types` (
		  `id` smallint NOT NULL auto_increment,
		  `description` varchar(255) NOT NULL default '',
		  `prefix` varchar(2) default NULL,
		  `default_unknown` varchar(8) NOT NULL default '',
		  PRIMARY KEY  (`id`),
          UNIQUE KEY `description` (`description`(255))
		) ENGINE=InnoDB!,

	referrer_department => q!
        CREATE TABLE `referrer_department` (
          `id` int NOT NULL auto_increment,
          `referrer_id` int NOT NULL default '0',
          `parent_organisation_id` smallint NOT NULL default '0',
          `hospital_department_code` smallint NOT NULL default '0',
          `is_active` enum('yes','no') NOT NULL default 'yes',
          PRIMARY KEY  (`id`),
          UNIQUE KEY `referrer_parent_organisation` (`referrer_id`,`parent_organisation_id`),
          KEY `parent_organisation_id` (`parent_organisation_id`),
          KEY `hospital_department_code` (`hospital_department_code`)
        ) ENGINE=InnoDB!,

    referrers => q!
        CREATE TABLE `referrers` (
          `id` int NOT NULL auto_increment,
          `name` varchar(255) NOT NULL default '',
          `national_code` varchar(8) NOT NULL default '',
          `referral_type_id` smallint NOT NULL default '0',
          `active` enum('yes','no') NOT NULL default 'yes',
          PRIMARY KEY  (`id`),
		  KEY `name` (`name`),
		  UNIQUE KEY `national_code` (`national_code`)
        ) ENGINE=InnoDB!,

    report_notification => q!
		CREATE TABLE `report_notification` (
			`id` SMALLINT(6) UNSIGNED NOT NULL AUTO_INCREMENT,
			`name` VARCHAR(50) NOT NULL,
			`type` ENUM('referrer','hospital','organisation') NOT NULL,
			`department_id` SMALLINT(6) NULL DEFAULT NULL,
			`identifier` VARCHAR(50) NOT NULL,
			`status` ENUM('new','all') NOT NULL DEFAULT 'all',
			`contact_address` VARCHAR(255) NOT NULL,
			`is_active` ENUM('yes','no') NOT NULL DEFAULT 'yes',
			`timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
			PRIMARY KEY (`id`),
			UNIQUE INDEX `identity_address` (`name`, `contact_address`, `identifier`),
			INDEX `department` (`department_id`)
		)
		ENGINE=InnoDB!,

	request_audit => q!
        CREATE TABLE `request_audit` (
          `request_id` int NOT NULL default '0',
          `audit_request_option_id` smallint NOT NULL default '0',
          PRIMARY KEY  (`request_id`,`audit_request_option_id`),
          KEY `audit_request_option_id` (`audit_request_option_id`)
        ) ENGINE=InnoDB!,

	request_authorisation_diagnosis => q!
		CREATE TABLE `request_authorisation_diagnosis` (
		  `request_id` int NOT NULL default '0',
		  `report_diagnosis_id` smallint NOT NULL default '0',
		  `auth_diagnosis_id` smallint NOT NULL default '0',
		  `user_id` smallint NOT NULL default '0',
		  `time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
		  PRIMARY KEY  (`request_id`),
		  KEY `report_diagnosis_id` (`report_diagnosis_id`),
		  KEY `auth_diagnosis_id` (`auth_diagnosis_id`),
		  KEY `user_id` (`user_id`)
		) ENGINE=InnoDB!,

    request_consent => q!
        CREATE TABLE `request_consent` (
			`request_id` int NOT NULL default '0',
			`consent_id` smallint NOT NULL default '0',
            `status` enum('yes','no') NOT NULL default 'no',
			PRIMARY KEY  (`request_id`,`consent_id`),
			KEY `consent_id` (`consent_id`)
        ) ENGINE=InnoDB!,

    request_diagnosis_history => q!
        CREATE TABLE `request_diagnosis_history` (
          `id` int NOT NULL auto_increment,
          `request_id` int NOT NULL,
          `diagnosis_id` smallint NOT NULL,
          `option_id` smallint NOT NULL,
          `user_id` smallint NOT NULL,
          `time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
          PRIMARY KEY  (`id`)
        ) ENGINE=InnoDB!,

	request_dispatch_log => q!
		CREATE TABLE `request_dispatch_log` (
		  `id` int NOT NULL auto_increment,
		  `request_id` int NOT NULL default '0',
		  `recipient` varchar(255) NOT NULL,
		  `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
		  PRIMARY KEY  (`id`),
		  KEY `request_id` (`request_id`)
		) ENGINE=InnoDB!,

    request_draft_report => q!
        CREATE TABLE `request_draft_report` (
            `user_id` SMALLINT(6) NOT NULL,
            `request_id` INT(11) NOT NULL,
            `request_notes` TEXT NULL,
            `clinical_details` TEXT NULL,
            `biopsy_site` VARCHAR(255) NULL DEFAULT NULL,
            `gross_description` VARCHAR(255) NULL DEFAULT NULL,
            `morphology` TEXT NULL,
            `comment` TEXT NULL,
            `status` ENUM('new','relapse','default') NOT NULL DEFAULT 'default',
            `diagnosis_id` SMALLINT(6) NOT NULL,
            `secondary_diagnosis_id` SMALLINT(6) NULL DEFAULT NULL,
            `specimen_quality` ENUM('good','adequate','poor') NULL DEFAULT 'adequate',
            `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            `updated_at` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
            PRIMARY KEY (`request_id`, `user_id`),
            INDEX `request_draft_report_ibfk_1` (`user_id`),
            INDEX `request_draft_report_ibfk_2` (`diagnosis_id`),
            INDEX `request_draft_report_ibfk_3` (`secondary_diagnosis_id`)
        ) ENGINE=InnoDB!,

    request_error_code => q!
        CREATE TABLE `request_error_code` (
          `id` int NOT NULL auto_increment,
          `request_id` int NOT NULL default '0',
          `error_code_id` smallint NOT NULL default '0',
          `user_id` smallint NOT NULL default '0',
          `time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
          PRIMARY KEY  (`id`),
          KEY `error_code_id` (`error_code_id`),
          KEY `request_id` (`request_id`),
          KEY `user_id` (`user_id`)
        ) ENGINE=InnoDB!,

    request_error_code_details => q!
        CREATE TABLE `request_error_code_details` (
            `request_error_code_id` INT(11) NOT NULL DEFAULT '0',
            `details` VARCHAR(255),
            PRIMARY KEY (`request_error_code_id`)
        ) ENGINE=InnoDB!,

	request_external_ref => q!
		CREATE TABLE `request_external_ref` (
		  `request_id` int NOT NULL default '0',
		  `external_reference` varchar(255) NOT NULL default '',
		  `status` varchar(255) default NULL,
		  PRIMARY KEY  (`request_id`)
		) ENGINE=InnoDB!,

    request_haematology => q!
        CREATE TABLE `request_haematology` (
			`lab_number` CHAR(8) NOT NULL,
            `status` ENUM('verified','default') NOT NULL DEFAULT 'default',
            `pda` VARCHAR(6) NOT NULL,
            `wbc` CHAR(4) NOT NULL,
            `wbc_f` TINYINT(1) UNSIGNED NOT NULL,
            `rbc` CHAR(4) NOT NULL,
            `rbc_f` TINYINT(1) UNSIGNED NOT NULL,
            `hb` CHAR(4) NOT NULL,
            `hb_f` TINYINT(1) UNSIGNED NOT NULL,
            `hct` CHAR(4) NOT NULL,
            `hct_f` TINYINT(1) UNSIGNED NOT NULL,
            `mcv` CHAR(4) NOT NULL,
            `mcv_f` TINYINT(1) UNSIGNED NOT NULL,
            `mch` CHAR(4) NOT NULL,
            `mch_f` TINYINT(1) UNSIGNED NOT NULL,
            `mchc` CHAR(4) NOT NULL,
            `mchc_f` TINYINT(1) UNSIGNED NOT NULL,
            `plt` CHAR(4) NOT NULL,
            `plt_f` TINYINT(1) UNSIGNED NOT NULL,
            `lymph_percent` CHAR(4) NOT NULL,
            `lymph_percent_f` TINYINT(1) UNSIGNED NOT NULL,
            `mixed_percent` CHAR(4) NOT NULL,
            `mixed_percent_f` TINYINT(1) UNSIGNED NOT NULL,
            `neutr_percent` CHAR(4) NOT NULL,
            `neutr_percent_f` TINYINT(1) UNSIGNED NOT NULL,
            `lymph` CHAR(4) NOT NULL,
            `lymph_f` TINYINT(1) UNSIGNED NOT NULL,
            `mixed` CHAR(4) NOT NULL,
            `mixed_f` TINYINT(1) UNSIGNED NOT NULL,
            `neutr` CHAR(4) NOT NULL,
            `neutr_f` TINYINT(1) UNSIGNED NOT NULL,
            `rdw_sd` CHAR(4) NOT NULL,
            `rdw_sd_f` TINYINT(1) UNSIGNED NOT NULL,
            `rdw_cv` CHAR(4) NOT NULL,
            `rdw_cv_f` TINYINT(1) UNSIGNED NOT NULL,
            `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            `updated` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
            PRIMARY KEY (`lab_number`)
        ) ENGINE=InnoDB!,

    request_history => q!
        CREATE TABLE `request_history` (
          `id` int NOT NULL auto_increment,
          `request_id` int NOT NULL default '0',
          `action` varchar(255) NOT NULL default '',
          `user_id` smallint NOT NULL default '0',
          `time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
          PRIMARY KEY  (`id`),
          KEY `request_id` (`request_id`),
		  KEY `action` (`action`),
          KEY `user_id` (`user_id`),
		  KEY `time` (`time`)
        ) ENGINE=InnoDB!,

	request_hl7_log => q!
		CREATE TABLE `request_hl7_log` (
			`id` INT(11) NOT NULL AUTO_INCREMENT,
			`request_id` INT(11) NOT NULL DEFAULT '0',
            `service` ENUM('ICE','WinPath') NOT NULL,
			`response` VARCHAR(255) NOT NULL,
			`timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY (`id`),
			INDEX `request_id` (`request_id`)
		) ENGINE=InnoDB!,

    request_initial_screen => q!
        CREATE TABLE `request_initial_screen` (
          `request_id` int NOT NULL default '0',
          `screen_id` smallint NOT NULL default '0',
          PRIMARY KEY  (`request_id`),
          KEY `screen_id` (`screen_id`)
          ) ENGINE=InnoDB!,

    request_form => q!
        CREATE TABLE `request_form` (
            `id` VARCHAR(50) NOT NULL,
            `last_name` VARCHAR(50) NOT NULL,
            `first_name` VARCHAR(50) NOT NULL,
            `dob` DATE NOT NULL,
            `gender` ENUM('M','F','U') NOT NULL,
            `nhs_number` VARCHAR(10) NOT NULL,
            `patient_number` VARCHAR(50) NULL DEFAULT NULL,
            `location_name` VARCHAR(50) NOT NULL,
            `location_id` SMALLINT(6) NOT NULL,
            `referrer` VARCHAR(50) NOT NULL,
            `pds_code` VARCHAR(50) NULL DEFAULT NULL,
            `user_id` SMALLINT(6) NOT NULL,
			`requested_by` VARCHAR(50) NOT NULL,
            `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            `imported` DATETIME NULL DEFAULT NULL,
        PRIMARY KEY (`id`)
    ) ENGINE=InnoDB!,

	request_general_notes => q!
		CREATE TABLE `request_general_notes` (
		  `request_id` int NOT NULL default '0',
		  `detail` text,
		  PRIMARY KEY  (`request_id`)
		) ENGINE=InnoDB!,

	request_lab_section_foreign_id => q!
		CREATE TABLE `request_lab_section_foreign_id` (
		  `request_id` int(11) NOT NULL default '0',
		  `lab_section_id` smallint(6) NOT NULL default '0',
		  `foreign_id` varchar(25) NOT NULL default '',
		  `time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
		  PRIMARY KEY  (`request_id`,`lab_section_id`),
		  KEY `lab_section_id` (`lab_section_id`)
		) ENGINE=InnoDB!,

	request_lab_section_notes => q!
		CREATE TABLE `request_lab_section_notes` (
		  `request_id` int NOT NULL default '0',
		  `lab_section_id` smallint NOT NULL default '0',
		  `details` text,
          `time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
		  PRIMARY KEY  (`request_id`,`lab_section_id`),
		  KEY `lab_section_id` (`lab_section_id`)
		) ENGINE=InnoDB!,

    request_lab_test_history => q!
        CREATE TABLE `request_lab_test_history` (
          `id` int NOT NULL auto_increment,
          `request_id` int NOT NULL default '0',
          `action` varchar(255) NOT NULL default '',
          `user_id` smallint NOT NULL default '0',
          `time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
          PRIMARY KEY  (`id`),
          KEY `request_id` (`request_id`),
          KEY `user_id` (`user_id`)
        ) ENGINE=InnoDB!,

	request_lab_test_results => q!
		CREATE TABLE `request_lab_test_results` (
		  `id` int NOT NULL auto_increment,
		  `request_id` int NOT NULL default '0',
		  `lab_test_id` smallint NOT NULL default '0',
		  `result` varchar(255) NOT NULL default '',
		  `time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
		  PRIMARY KEY  (`id`),
		  UNIQUE KEY `request_lab_test` (`request_id`,`lab_test_id`),
		  KEY `lab_test_id` (`lab_test_id`)
		) ENGINE=InnoDB!,

	request_lab_test_status => q!
		CREATE TABLE `request_lab_test_status` (
		  `id` int(11) NOT NULL auto_increment,
		  `request_id` int(11) NOT NULL default '0',
		  `lab_test_id` smallint(6) NOT NULL default '0',
		  `status_option_id` smallint(6) NOT NULL default '0',
          `user_id` smallint NOT NULL default '0',
		  `time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
		  PRIMARY KEY  (`id`),
		  UNIQUE KEY `request_lab_test` (`request_id`,`lab_test_id`),
		  KEY `lab_test_id` (`lab_test_id`),
		  KEY `user_id` (`user_id`),
		  KEY `status_option_id` (`status_option_id`)
		) ENGINE=InnoDB!,

    request_notification => q!
        CREATE TABLE `request_notification` (
            `request_id` INT(11) NOT NULL DEFAULT '0',
            `datetime` DATETIME NOT NULL DEFAULT '1000-01-01 00:00:00',
            PRIMARY KEY (`request_id`)
        )
        ENGINE=MyISAM!,

    request_option => q!
        CREATE TABLE `request_option` (
          `request_id` int NOT NULL default '0',
          `option_id` smallint NOT NULL default '0',
          PRIMARY KEY  (`request_id`,`option_id`),
          KEY `option_id` (`option_id`)
        ) ENGINE=InnoDB!,

	request_phone_log => q!
		CREATE TABLE `request_phone_log` (
		  `id` int NOT NULL auto_increment,
		  `request_id` int NOT NULL default '0',
		  `user_id` smallint NOT NULL default '0',
		  `status` enum('inbound','outbound') default NULL,
		  `contact` varchar(255) NOT NULL default '',
		  `details` text NOT NULL,
		  `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
		  PRIMARY KEY  (`id`),
		  KEY `request_id` (`request_id`),
		  KEY `user_id` (`user_id`)
		) ENGINE=InnoDB!,

	request_print_log => q!
		CREATE TABLE `request_print_log` (
		  `id` int NOT NULL auto_increment,
		  `request_id` int NOT NULL default '0',
		  `user_id` smallint NOT NULL default '0',
		  `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
		  PRIMARY KEY  (`id`),
		  KEY `request_id` (`request_id`),
		  KEY `user_id` (`user_id`)
		) ENGINE=InnoDB!,

    request_report_detail => q!
        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`)
        ) ENGINE=InnoDB!,

	request_report_history => q!
		CREATE TABLE `request_report_history` (
		  `id` int NOT NULL auto_increment,
		  `request_id` int NOT NULL default '0',
		  `field` ENUM('morphology','comment') default NULL,
		  `content` text,
		  `user_id` SMALLINT NOT NULL default '0',
		  `time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
		  PRIMARY KEY  (`id`),
		  KEY `request_id` (`request_id`),
		  KEY `user_id` (`user_id`)
		) ENGINE=InnoDB!,

    request_result_summaries => q!
        CREATE TABLE `request_result_summaries` (
          `request_id` int NOT NULL default '0',
          `lab_section_id` SMALLINT NOT NULL default '0',
          `user_id` SMALLINT NOT NULL default '0',
          `results_summary` text,
          `time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
          PRIMARY KEY  (`request_id`,`lab_section_id`),
          KEY `lab_section_id` (`lab_section_id`)
        ) ENGINE=InnoDB!,

    request_secondary_diagnosis => q!
        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!,

    request_specimen => q!
        CREATE TABLE `request_specimen` (
          `request_id` int NOT NULL default '0',
          `specimen_id` smallint NOT NULL default '0',
          PRIMARY KEY  (`request_id`,`specimen_id`),
          KEY `specimen_id` (`specimen_id`)
        ) ENGINE=InnoDB!,

    request_storage => q!
        CREATE TABLE `request_storage` (
            `vialId` VARCHAR(10) NOT NULL,
            `request_id` INT(11) NOT NULL DEFAULT '0',
            `specimen_id` SMALLINT(6) NOT NULL DEFAULT '0',
            `sample` VARCHAR(25) NOT NULL,
            `part_number` VARCHAR(25) NOT NULL,
            `volume` SMALLINT(6) NULL DEFAULT NULL,
            `concentration` SMALLINT(6) NULL DEFAULT NULL,
            `source` VARCHAR(25) NOT NULL,
            `method` VARCHAR(25) NULL DEFAULT NULL,
            `rack_id` SMALLINT(6) NULL DEFAULT NULL,
            `vial_location` VARCHAR(3) NULL DEFAULT NULL,
            `comment` VARCHAR(255) NULL DEFAULT NULL,
            `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            `signed_out` TIMESTAMP NULL DEFAULT NULL,
            PRIMARY KEY (`vialId`),
            INDEX `specimen_id` (`specimen_id`),
            INDEX `request_id` (`request_id`),
            INDEX `rack_id` (`rack_id`)
        ) ENGINE=InnoDB!,

    request_specimen_detail => q!
        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') NULL,
			`specimen_date` DATETIME NULL,
            PRIMARY KEY (`request_id`)
        ) ENGINE=InnoDB!,

    request_trial => q!
        CREATE TABLE `request_trial` (
          `request_id` int NOT NULL default '0',
          `trial_id` smallint NOT NULL default '0',
          PRIMARY KEY  (`request_id`),
          KEY `trial_id` (`trial_id`)
        ) ENGINE=InnoDB!,

	request_view_log => q!
		CREATE TABLE `request_view_log` (
		  `id` int NOT NULL auto_increment,
		  `request_id` int NOT NULL default '0',
		  `user_id` smallint NOT NULL default '0',
		  `ip_address` varchar(255) NOT NULL,
		  `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
		  PRIMARY KEY  (`id`),
		  KEY `request_id` (`request_id`),
		  KEY `user_id` (`user_id`)
		) ENGINE=InnoDB!,

    requests => q!
        CREATE TABLE `requests` (
          `id` int NOT NULL auto_increment,
          `request_number` smallint(6) UNSIGNED NOT NULL default '0',
		  `year` year(4) NOT NULL default '0000',
          `patient_case_id` int NOT NULL default '0',
          `referrer_department_id` int NOT NULL default '0',
		  `status_option_id` smallint NOT NULL default '1',
          `created_at` timestamp NOT NULL default '0000-00-00 00:00:00',
          `updated_at` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
          PRIMARY KEY  (`id`),
          UNIQUE KEY `lab_no` (`request_number`,`year`),
          KEY `patient_case_id` (`patient_case_id`),
          KEY `referrer_department_id` (`referrer_department_id`),
		  KEY `status_option_id` (`status_option_id`)
        ) ENGINE=InnoDB!,

	report_error_codes => q!
		CREATE TABLE `report_error_codes` (
		  `id` smallint NOT NULL auto_increment,
		  `code` varchar(2) default NULL,
		  `description` varchar(255) default NULL,
		  `active` enum('yes','no') NOT NULL default 'yes',
		  PRIMARY KEY  (`id`),
		  UNIQUE KEY `code` (`code`)
		) ENGINE=InnoDB!,

    result_summary_expansion => q!
        CREATE TABLE `result_summary_expansion` (
            `result_summary_option_id` SMALLINT(6) NOT NULL DEFAULT '0',
            `description` TEXT NOT NULL,
            `is_active` ENUM('yes','no') NOT NULL DEFAULT 'yes',
            PRIMARY KEY (`result_summary_option_id`)
        ) ENGINE=InnoDB!,

    result_summary_lab_test => q!
        CREATE TABLE `result_summary_lab_test` (
            `result_summary_id` SMALLINT(6) NOT NULL,
            `lab_test_id` SMALLINT(6) NOT NULL,
            PRIMARY KEY (`result_summary_id`, `lab_test_id`),
            INDEX `result_summary_lab_test_ibfk2` (`lab_test_id`)
        )
        ENGINE=InnoDB!,

	result_summary_options => q!
		CREATE TABLE `result_summary_options` (
		  `id` smallint NOT NULL auto_increment,
		  `description` varchar(255) NOT NULL default '',
		  `lab_section_id` smallint NOT NULL default '0',
		  `is_active` enum('yes','no') NOT NULL default 'yes',
		  PRIMARY KEY  (`id`),
		  UNIQUE KEY `lab_section_description` (`description`,`lab_section_id`),
		  KEY `lab_section_id` (`lab_section_id`)
		) ENGINE=InnoDB!,

    rfc => q!
        CREATE TABLE IF NOT EXISTS `rfc` (
          `id` int(11) NOT NULL AUTO_INCREMENT,
          `reason` varchar(50) NOT NULL,
          `details` text NOT NULL,
          `status` varchar(50) NOT NULL,
          `user_id` smallint(6) NOT NULL,
          `remote_id` varchar(50) DEFAULT NULL,
          `created` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
          `updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
          PRIMARY KEY (`id`),
          KEY `user_id` (`user_id`)
        ) ENGINE=InnoDB!,

    sample_types => q!
        CREATE TABLE `sample_types` (
            `id` smallint NOT NULL auto_increment,
            `specimen_type` VARCHAR(50) NOT NULL DEFAULT '',
            PRIMARY KEY  (`id`),
            UNIQUE KEY `specimen_type` (`specimen_type`)
          ) ENGINE=InnoDB!,

    screen_category => q!
        CREATE TABLE `screen_category` (
            `id` smallint(6) NOT NULL AUTO_INCREMENT,
            `name` varchar(50) NOT NULL,
		    `is_active` enum('yes','no') NOT NULL default 'yes',
            PRIMARY KEY (`id`)
        ) ENGINE=InnoDB!,

    screen_lab_test_detail => q!
        CREATE TABLE `screen_lab_test_detail` (
          `screen_id` smallint NOT NULL default '0',
          `lab_section_id` smallint NOT NULL default '0',
          `test_details` varchar(255) NOT NULL default '',
          PRIMARY KEY  (`screen_id`,`lab_section_id`),
          KEY `lab_section_id` (`lab_section_id`)
        ) ENGINE=InnoDB!,

    screen_lab_test => q!
        CREATE TABLE `screen_lab_test` (
          `screen_id` smallint NOT NULL default '0',
          `lab_test_id` smallint NOT NULL default '0',
          PRIMARY KEY  (`screen_id`,`lab_test_id`),
          KEY `lab_test_id` (`lab_test_id`)
        ) ENGINE=InnoDB!,

    screens => q!
        CREATE TABLE `screens` (
          `id` smallint NOT NULL auto_increment,
          `description` varchar(50) default NULL,
          `category_id` smallint NOT NULL default '0',
          `active` enum('yes','no') default NULL,
          PRIMARY KEY  (`id`),
          UNIQUE KEY `category_description` (`category_id`, `description`)
        ) ENGINE=InnoDB!,

    sessions => q!
        CREATE TABLE `sessions` (
          `id` varchar(32) NOT NULL default '',
		  `userid` varchar(255) default NULL,
          `a_session` text NOT NULL,
          `time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
          UNIQUE KEY `id` (`id`),
		  UNIQUE KEY `userid` (`userid`)
        ) ENGINE=InnoDB!,

	specimen_lab_test => q!
		CREATE TABLE `specimen_lab_test` (
		`specimen_id` smallint NOT NULL default '0',
		`lab_test_id` smallint NOT NULL default '0',
		PRIMARY KEY  (`specimen_id`,`lab_test_id`),
		KEY `lab_test_id` (`lab_test_id`)
	  ) ENGINE=InnoDB!,

    specimen_sample_type => q!
        CREATE TABLE `specimen_sample_type` (
            `specimen_id` smallint NOT NULL default '0',
            `sample_type_id` smallint NOT NULL default '0',
            PRIMARY KEY  (`sample_type_id`, `specimen_id`)
        ) ENGINE=InnoDB!,

    specimens => q!
        CREATE TABLE `specimens` (
          `id` smallint NOT NULL auto_increment,
          `sample_code` varchar(4) default NULL,
          `description` varchar(40) default NULL,
          `active` enum('yes','no') NOT NULL default 'yes',
          PRIMARY KEY  (`id`),
          UNIQUE KEY `sample_code` (`sample_code`)
        ) ENGINE=InnoDB!,

	status_options => q!
		CREATE TABLE `status_options` (
		  `id` smallint NOT NULL auto_increment,
		  `description` varchar(255) NOT NULL default '',
		  `is_editable` enum('yes','no') NOT NULL default 'yes',
          `is_active` enum('yes','no') NOT NULL default 'yes',
		  PRIMARY KEY  (`id`),
		  UNIQUE KEY `description` (`description`)
		) ENGINE=InnoDB!,

	storage_racks => q!
		CREATE TABLE `storage_racks` (
		  `id` smallint NOT NULL auto_increment,
		  `plateId` varchar(10) NOT NULL,
		  `storage_location` varchar(50) default NULL,
          `is_active` enum('yes','no') NOT NULL default 'yes',
		  PRIMARY KEY  (`id`),
		  UNIQUE KEY `plateID` (`plateID`)
		) ENGINE=InnoDB!,

	user_functions => q!
		CREATE TABLE `user_functions` (
		  `id` smallint NOT NULL auto_increment,
		  `function_name` varchar(20) default NULL,
		  `function_detail` varchar(255) default NULL,
          `active` enum('yes','no') NOT NULL default 'yes',
		  PRIMARY KEY  (`id`),
		  UNIQUE KEY `function_name` (`function_name`),
		  UNIQUE KEY `function_detail` (`function_detail`)
		) ENGINE=InnoDB!,

	user_group_function => q!
		CREATE TABLE `user_group_function` (
		  `group_id` smallint NOT NULL,
		  `function_id` smallint NOT NULL,
		  PRIMARY KEY  (`group_id`,`function_id`),
		  KEY `function` (`function_id`)
		) ENGINE=InnoDB!,

	user_groups => q!
		CREATE TABLE `user_groups` (
		  `id` smallint NOT NULL auto_increment,
		  `group_name` varchar(10) default NULL,
		  `group_label` varchar(30) default NULL,
		  `group_detail` varchar(255) default NULL,
          `active` enum('yes','no') NOT NULL default 'yes',
		  PRIMARY KEY  (`id`),
		  UNIQUE KEY `group_name` (`group_name`),
		  UNIQUE KEY `group_label` (`group_label`)
		) ENGINE=InnoDB!,

    user_locations => q!
        CREATE TABLE `user_locations` (
          `id` smallint NOT NULL auto_increment,
          `location_name` varchar(50) NOT NULL default '',
          `region_code` varchar(6) default NULL,
          `active` enum('yes','no') default 'yes',
          PRIMARY KEY  (`id`),
          KEY `region_code` (`region_code`),
          UNIQUE KEY `location_name` (`location_name`)
        ) ENGINE=InnoDB!,

    user_message => q!
        CREATE TABLE `user_message` (
            `id` INT(11) NOT NULL auto_increment,
            `recipient_id` SMALLINT(6) NOT NULL default '0',
            `sender_id` SMALLINT(6) NOT NULL default '0',
            `message` TEXT NOT NULL,
            `received` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
			`acknowledged` TIMESTAMP NULL DEFAULT NULL,
            PRIMARY KEY (`id`),
            KEY `recipient_id` (`recipient_id`),
            KEY `sender_id` (`sender_id`)
        ) ENGINE=InnoDB!,

    user_permission => q!
        CREATE TABLE `user_permission` (
          `user_id` smallint NOT NULL NOT NULL auto_increment,
          `function_id` smallint NOT NULL default '0',
          PRIMARY KEY  (`user_id`,`function_id`),
          KEY `function_id` (`function_id`)
        ) ENGINE=InnoDB!,

	user_registration => q!
		CREATE TABLE `user_registration` (
			`user_id` SMALLINT(6) NOT NULL DEFAULT 0,
			`registration_number` VARCHAR(8) NOT NULL DEFAULT '',
			PRIMARY KEY (`user_id`),
			UNIQUE INDEX `registration_number` (`registration_number`)
		) ENGINE=InnoDB!,

	users => q!
		CREATE TABLE `users` (
		  `id` smallint NOT NULL auto_increment,
		  `username` varchar(50) NOT NULL default 'username',
		  `first_name` varchar(50) NOT NULL default 'first_name',
		  `last_name` varchar(50) NOT NULL default 'last_name',
		  `password` varchar(32) NOT NULL default 'password',
		  `email` varchar(50) default NULL,
		  `user_location_id` smallint NOT NULL default '0',
		  `designation` varchar(255) NOT NULL default 'designation',
		  `group_id` smallint NOT NULL default '0',
          `last_login` timestamp NOT NULL default current_timestamp,
		  `active` enum('yes','no') NOT NULL default 'yes',
		  PRIMARY KEY  (`id`),
		  UNIQUE KEY `username` (`username`),
		  UNIQUE KEY `login_id` (`first_name`, `last_name`),
		  UNIQUE KEY `email` (`email`),
		  KEY `group_id` (`group_id`),
		  KEY `user_location_id` (`user_location_id`)
		) ENGINE=InnoDB!,
};

#
# functions:
#

$FUNCTIONS = {
    ucfirst => q!
        CREATE DEFINER=`raj`@`%` FUNCTION `ucFirst`(`x` varchar(255))
            RETURNS varchar(255) CHARSET latin1
            LANGUAGE SQL
            READS SQL DATA
            CONTAINS SQL
            SQL SECURITY DEFINER
            COMMENT ''
        return concat( upper(substring(x,1,1)),lower(substring(x,2)) )!,
};

#
# views after all tables created:
#
$VIEWS = {
    authorised_reports_view => q!
        CREATE ALGORITHM=UNDEFINED DEFINER=`raj`@`%` SQL SECURITY DEFINER
        VIEW `authorised_reports_view` AS
        SELECT `r`.`id`, `r`.`request_number`, `r`.`year`, date(`r`.`created_at`)
            as 'reg_date', `p`.`last_name`, `p`.`first_name`, `p`.`dob`,
            `pc`.`unit_number`, ((DATE_FORMAT(`r`.`created_at`,'%Y') -
                DATE_FORMAT(`p`.`dob`,'%Y')) -
                (DATE_FORMAT(`r`.`created_at`,'00-%m-%d')
                < DATE_FORMAT(`p`.`dob`,'00-%m-%d'))) AS `age`,
            `p`.`nhs_number`, `rs`.`display_name` AS `location`,
			`rs`.`organisation_code`, `po`.`parent_code`,
            `d`.`name` AS `diagnosis`, `d`.`icdo3`, `rr`.`status`,
            date(`rh`.time) as 'auth_date'
        FROM `requests` `r`
            JOIN `patient_case` `pc` on `r`.`patient_case_id` = `pc`.`id`
            JOIN `referral_sources` `rs` on `pc`.`referral_source_id` = `rs`.`id`
            JOIN `parent_organisations` `po`
                on `rs`.`parent_organisation_id`= `po`.`id`
            JOIN `patients` `p` on `pc`.`patient_id` = `p`.`id`
            JOIN `request_report_detail` `rr` on `rr`.`request_id` = `r`.`id`
            JOIN `diagnoses` `d` on `rr`.`diagnosis_id` = `d`.`id`
            JOIN `request_history` `rh` on ( `rh`.`request_id` = `r`.`id`
				and `rh`.`action` = 'authorised' )!,
    # Genomics db only:
    family_id_view => q!
        CREATE ALGORITHM=UNDEFINED DEFINER=`raj`@`%` SQL SECURITY DEFINER
            VIEW `family_id_view` AS
            SELECT r.id as request_id, tr.result as family_id
            FROM requests r
            JOIN ( request_lab_test_results tr JOIN lab_tests lt
                on tr.lab_test_id = lt.id and lt.test_name = 'family_id')
                    on tr.request_id = r.id!,

	request_report_view => q!
		CREATE ALGORITHM=UNDEFINED DEFINER=`raj`@`%` SQL SECURITY DEFINER
			VIEW `request_report_view` AS
            SELECT t1.request_id, t1.clinical_details, t1.morphology, t1.`comment`,
            t1.`status`, t1.diagnosis_id, t2.gross_description, t2.biopsy_site,
            t2.specimen_quality, t2.specimen_date, t1.created_at, t1.updated_at
            FROM request_report_detail t1 JOIN request_specimen_detail t2
            on `t1`.`request_id` = `t2`.`request_id`!,

    request_status_view => q!
		CREATE ALGORITHM=UNDEFINED DEFINER=`raj`@`%` SQL SECURITY DEFINER
            VIEW `request_status_view` AS
            SELECT
                `rh`.`request_id` AS `request_id`,
                (CASE WHEN (`rh`.`action` = 'registered') THEN 1 ELSE `so`.`id` END)
                    AS `status_option_id`,
            `rh`.`action`,
            `u`.`username`,
            `rh`.`time` AS `time`
            FROM `request_history` `rh` JOIN users u ON rh.user_id = u.id
                LEFT JOIN `status_options` `so` ON `rh`.`action` = `so`.`description`
            WHERE `rh`.`action` IN ('registered','screened','reported','authorised')!,

    request_storage_view => q!
		CREATE ALGORITHM=UNDEFINED DEFINER=`raj`@`%` SQL SECURITY DEFINER
            VIEW `request_storage_view` AS
            SELECT vialId, request_id
            FROM requests r
                JOIN request_storage rs on rs.request_id = r.id
            UNION
            SELECT result as 'vialId', request_id
            FROM requests r
                JOIN request_lab_test_results tr on tr.request_id = r.id
                JOIN lab_tests lt on tr.lab_test_id = lt.id
                    and lt.test_name in ('edta1_fluidx','tumour_fluidx')!,
    # bioinf_haemoncdb_view needs user creating in mysql and firewall rules
    # used by haemoncdb django app to get live test status info
    bioinf_haemoncdb_view => q{
        CREATE VIEW bioinf_haemoncdb_view AS
    SELECT CONCAT('H', r.request_number, '/', SUBSTRING(r.year, 3, 2)) AS sample_id,
    lt.test_name, ltso.description, rlts.time AS status_updated
    
    FROM request_lab_test_status rlts
    JOIN requests r ON rlts.request_id=r.id
    JOIN lab_tests lt ON rlts.lab_test_id=lt.id
    JOIN lab_sections ls ON lt.lab_section_id=ls.id
    JOIN lab_test_status_options ltso ON rlts.status_option_id=ltso.id
    
    WHERE ls.section_name='High-throughput sequencing'
		AND ltso.description != 'complete'
	},
};

#
#  Foreign keys for tables
#
$FOREIGN_KEYS = {
    fk_audit_request_options => q!
        ALTER TABLE `audit_request_options`
            ADD CONSTRAINT `audit_request_options_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `audit_request_categories` (`id`)!,
    fk_admin_log => q!
		ALTER TABLE `admin_log`
			ADD CONSTRAINT `admin_log_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)!,

	fk_context_warning_diagnosis => q!
		ALTER TABLE `context_warning_diagnosis`
		  ADD CONSTRAINT `context_warning_diagnosis_ibfk_1` FOREIGN KEY (`diagnosis_id`) REFERENCES `diagnoses` (`id`),
		  ADD CONSTRAINT `context_warning_diagnosis_ibfk_2` FOREIGN KEY (`context_warning_id`) REFERENCES `diagnosis_context_warnings` (`id`)!,

	fk_deleted_requests => q!
		ALTER TABLE `deleted_requests`
			ADD CONSTRAINT `deleted_requests_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)!,

    fk_diagnoses => q!
        ALTER TABLE `diagnoses`
            ADD CONSTRAINT `diagnoses_ibfk_1` FOREIGN KEY (`diagnostic_category_id`) REFERENCES `diagnostic_categories` (`id`)!,

    fk_diagnosis_lab_test => q!
        ALTER TABLE `diagnosis_lab_test`
            ADD CONSTRAINT `diagnosis_lab_test_ibfk_1` FOREIGN KEY (`diagnosis_id`) REFERENCES `diagnoses` (`id`),
            ADD CONSTRAINT `diagnosis_lab_test_ibfk_2` FOREIGN KEY (`lab_test_id`) REFERENCES `lab_tests` (`id`)!,

	fk_email_contacts => q!
		ALTER TABLE `email_contacts`
			ADD CONSTRAINT `email_contacts_ibfk_1` FOREIGN KEY (`referral_source_id`) REFERENCES `referral_sources` (`id`),
			ADD CONSTRAINT `email_contacts_ibfk_2` FOREIGN KEY (`department_id`) REFERENCES `hospital_departments` (`id`)!,

	fk_error_code_assignment => q!
		ALTER TABLE `error_code_assignment`
		  ADD CONSTRAINT `error_code_config_ibfk_1` FOREIGN KEY (`error_code_id`) REFERENCES `error_codes` (`id`)!,

    fk_general_comms_log => q!
		ALTER TABLE `general_comms_log`
			ADD CONSTRAINT `general_comms_log_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)!,

	fk_icdo_sub_category => q!
		ALTER TABLE `icdo_sub_category`
			ADD CONSTRAINT `icdo_sub_category_ibfk_1` FOREIGN KEY (`diagnostic_category_id`) REFERENCES `diagnostic_categories` (`id`) ON UPDATE CASCADE!,

    fk_lab_test_accreditation => q!
        ALTER TABLE `lab_test_accreditation`
            ADD CONSTRAINT `lab_test_accreditation_ibfk_1` FOREIGN KEY (`lab_test_id`) REFERENCES `lab_tests` (`id`)!,

	fk_lab_test_data_type => q!
		ALTER TABLE `lab_test_data_type`
			ADD CONSTRAINT `lab_test_data_type_ibfk_2` FOREIGN KEY (`lab_test_id`) REFERENCES `lab_tests` (`id`),
		    ADD CONSTRAINT `lab_test_data_type_ibfk_1` FOREIGN KEY (`data_type_id`) REFERENCES `lab_test_result_data_types` (`id`)!,

	fk_lab_test_result_options => q!
		ALTER TABLE `lab_test_result_options`
			ADD CONSTRAINT `lab_test_result_options_ibfk_1` FOREIGN KEY (`data_type_id`) REFERENCES `lab_test_result_data_types` (`id`)!,

    fk_lab_section_sample_type => q!
        ALTER TABLE `lab_section_sample_type`
            ADD CONSTRAINT `lab_section_sample_type_ibfk_1` FOREIGN KEY (`lab_section_id`) REFERENCES `lab_sections` (`id`),
            ADD CONSTRAINT `lab_section_sample_type_ibfk_2` FOREIGN KEY (`sample_type_id`) REFERENCES `sample_types` (`id`)!,

	fk_lab_section_status_option => q!
		ALTER TABLE `lab_section_status_option`
		  ADD CONSTRAINT `lab_section_status_option_ibfk_1` FOREIGN KEY (`lab_section_id`) REFERENCES `lab_sections` (`id`),
		  ADD CONSTRAINT `lab_section_status_option_ibfk_2` FOREIGN KEY (`status_option_id`) REFERENCES `lab_test_status_options` (`id`)!,

    fk_lab_test_sample_type => q!
        ALTER TABLE `lab_test_sample_type`
            ADD CONSTRAINT `lab_test_sample_type_ibfk_1` FOREIGN KEY (`lab_test_id`) REFERENCES `lab_tests` (`id`),
            ADD CONSTRAINT `lab_test_sample_type_ibfk_2` FOREIGN KEY (`sample_type_id`) REFERENCES `sample_types` (`id`)!,

    fk_linked_lab_test => q!
        ALTER TABLE `linked_lab_test`
            ADD CONSTRAINT `linked_lab_test_ibfk_1` FOREIGN KEY (`parent_test_id`) REFERENCES `lab_tests` (`id`),
            ADD CONSTRAINT `linked_lab_test_ibfk_2` FOREIGN KEY (`linked_test_id`) REFERENCES `lab_tests` (`id`)!,

    fk_lab_tests => q!
        ALTER TABLE `lab_tests`
        ADD CONSTRAINT `lab_tests_ibfk_1` FOREIGN KEY (`lab_section_id`) REFERENCES `lab_sections` (`id`);!,

	fk_local_network_locations => q!
		ALTER TABLE `local_network_locations`
		ADD CONSTRAINT `local_network_locations_ibfk_1` FOREIGN KEY(`parent_id`) REFERENCES `parent_organisations` (`id`)!,

    fk_logins => q!
        ALTER TABLE `logins`
            ADD CONSTRAINT `logins_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`);!,

	fk_ngis_diagnosis => q!
		ALTER TABLE `ngis_diagnosis`
			ADD CONSTRAINT `ngis_diagnosis_ibfk_1` FOREIGN KEY (`diagnosis_id`) REFERENCES `diagnoses` (`id`),
			ADD CONSTRAINT `ngis_diagnosis_ibfk_2` FOREIGN KEY (`ngis_indication_id`) REFERENCES `ngis_clinical_indications` (`id`)!,

    fk_ngis_lab_test => q!
        ALTER TABLE `ngis_lab_test`
            ADD CONSTRAINT `ngis_lab_test_ibfk_1` FOREIGN KEY (`lab_test_id`) REFERENCES `lab_tests` (`id`)!,

    fk_panel_lab_test => q!
        ALTER TABLE `panel_lab_test`
            ADD CONSTRAINT `panel_lab_test_ibfk_1` FOREIGN KEY (`panel_test_id`) REFERENCES `lab_tests` (`id`),
            ADD CONSTRAINT `panel_lab_test_ibfk_2` FOREIGN KEY (`lab_test_id`) REFERENCES `lab_tests` (`id`)!,

	fk_parent_organisations => q!
		ALTER TABLE `parent_organisations`
		    ADD CONSTRAINT `parent_organisations_ibfk_1` FOREIGN KEY (`referral_type_id`) REFERENCES `referral_types` (`id`)!,

    fk_patient_case => q!
        ALTER TABLE `patient_case`
        ADD CONSTRAINT `patient_case_ibfk_1` FOREIGN KEY (`referral_source_id`)
            REFERENCES `referral_sources` (`id`),
        ADD CONSTRAINT `patient_case_ibfk_2` FOREIGN KEY (`patient_id`) REFERENCES `patients` (`id`) ON DELETE CASCADE!,

    fk_patient_notes => q!
        ALTER TABLE `patient_notes`
            ADD CONSTRAINT `patient_notes_ibfk_1` FOREIGN KEY (`patient_id`) REFERENCES `patients` (`id`)!,

	fk_patient_demographics => q!
		ALTER TABLE `patient_demographics`
		ADD CONSTRAINT `patient_demographics_ibfk_3` FOREIGN KEY (`gp_id`) REFERENCES `referrers` (`id`),
		ADD CONSTRAINT `patient_demographics_ibfk_2` FOREIGN KEY (`practice_id`) REFERENCES `referral_sources` (`id`),
		ADD CONSTRAINT `patient_demographics_ibfk_1` FOREIGN KEY (`patient_id`) REFERENCES `patients` (`id`)!,


    fk_patient_demographic_history => q!
		ALTER TABLE `patient_demographic_history`
		ADD CONSTRAINT `patient_demographic_history_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
		ADD CONSTRAINT `patient_demographic_history_ibfk_1` FOREIGN KEY (`patient_id`) REFERENCES `patients` (`id`)!,

    fk_patient_edits => q!
        ALTER TABLE `patient_edits`
        ADD CONSTRAINT `patient_edits_ibfk_3` FOREIGN KEY (`error_code_id`) REFERENCES `error_codes` (`id`),
        ADD CONSTRAINT `patient_edits_ibfk_2` FOREIGN KEY (`patient_id`) REFERENCES `patients` (`id`),
        ADD CONSTRAINT `patient_edits_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)!,

    fk_patient_trial => q!
        ALTER TABLE `patient_trial`
        ADD CONSTRAINT `patient_trial_ibfk_2` FOREIGN KEY (`trial_id`) REFERENCES `clinical_trials` (`id`),
        ADD CONSTRAINT `patient_trial_ibfk_1` FOREIGN KEY (`patient_id`) REFERENCES `patients` (`id`)!,

	fk_pre_registration_lab_test => q!
		ALTER TABLE `pre_registration_lab_test`
			ADD CONSTRAINT `pre_registration_lab_test_ibfk_2` FOREIGN KEY (`lab_test_id`) REFERENCES `lab_tests` (`id`),
			ADD CONSTRAINT `pre_registration_lab_test_ibfk_1` FOREIGN KEY (`reg_specimen_id`)
                REFERENCES `pre_registration_specimen` (`id`) ON DELETE CASCADE!,

	fk_pre_registration_specimen => q!
		ALTER TABLE `pre_registration_specimen`
		    ADD CONSTRAINT `pre_registration_specimen_ibfk_1` FOREIGN KEY (`pre_reg_id`)
                REFERENCES `pre_registration` (`id`) ON DELETE CASCADE!,

	fk_referrers => q!
		ALTER TABLE `referrers`
			ADD CONSTRAINT `referrers_ibfk_1` FOREIGN KEY (`referral_type_id`) REFERENCES `referral_types` (`id`)!,

	fk_referral_sources => q!
		ALTER TABLE `referral_sources`
			ADD CONSTRAINT `referral_sources_ibfk_1` FOREIGN KEY (`referral_type_id`) REFERENCES `referral_types` (`id`),
			ADD CONSTRAINT `referral_sources_ibfk_2` FOREIGN KEY (`parent_organisation_id`) REFERENCES `parent_organisations` (`id`)!,

	fk_referrer_department => q!
		ALTER TABLE `referrer_department`
          ADD CONSTRAINT `referrer_department_ibfk_3` FOREIGN KEY (`hospital_department_code`) REFERENCES `hospital_departments` (`id`),
          ADD CONSTRAINT `referrer_department_ibfk_1` FOREIGN KEY (`referrer_id`) REFERENCES `referrers` (`id`),
          ADD CONSTRAINT `referrer_department_ibfk_2` FOREIGN KEY (`parent_organisation_id`) REFERENCES `parent_organisations` (`id`)!,

	fk_report_error_codes => q!
		ALTER TABLE `report_error_codes`
			ADD CONSTRAINT `report_error_codes_ibfk_1` FOREIGN KEY (`code`) REFERENCES `error_codes` (`code`)!,

    fk_user_registration => q!
        ALTER TABLE `user_registration`
            ADD CONSTRAINT `user_registration_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)!,

	fk_report_notification => q!
        ALTER TABLE `report_notification`
            ADD CONSTRAINT `report_notification_ibfk_1` FOREIGN KEY (`department_id`) REFERENCES `hospital_departments` (`id`)!,

    fk_request_audit => q!
        ALTER TABLE `request_audit`
            ADD CONSTRAINT `request_audit_ibfk_2` FOREIGN KEY (`audit_request_option_id`) REFERENCES `audit_request_options` (`id`),
            ADD CONSTRAINT `request_audit_ibfk_1` FOREIGN KEY (`request_id`) REFERENCES `requests` (`id`) ON DELETE CASCADE!,

    fk_request_authorisation_diagnosis => q!
		ALTER TABLE `request_authorisation_diagnosis`
			ADD CONSTRAINT `request_authorisation_diagnosis_ibfk_4` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
			ADD CONSTRAINT `request_authorisation_diagnosis_ibfk_3` FOREIGN KEY (`report_diagnosis_id`) REFERENCES `diagnoses` (`id`),
			ADD CONSTRAINT `request_authorisation_diagnosis_ibfk_2` FOREIGN KEY (`auth_diagnosis_id`) REFERENCES `diagnoses` (`id`),
			ADD CONSTRAINT `request_authorisation_diagnosis_ibfk_1` FOREIGN KEY (`request_id`) REFERENCES `requests` (`id`) ON DELETE CASCADE!,

    fk_request_consent => q!
        ALTER TABLE `request_consent`
			ADD CONSTRAINT `request_consent_ibfk_2` FOREIGN KEY (`consent_id`) REFERENCES `consent_options` (`id`),
			ADD CONSTRAINT `request_consent_ibfk_1` FOREIGN KEY (`request_id`) REFERENCES `requests` (`id`) ON DELETE CASCADE!,

    fk_request_diagnosis_history => q!
        ALTER TABLE `request_diagnosis_history`
            ADD CONSTRAINT `request_diagnosis_history_ibfk_4` FOREIGN KEY (`option_id`) REFERENCES `diagnosis_change_options` (`id`),
            ADD CONSTRAINT `request_diagnosis_history_ibfk_3` FOREIGN KEY (`diagnosis_id`) REFERENCES `diagnoses` (`id`),
            ADD CONSTRAINT `request_diagnosis_history_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
            ADD CONSTRAINT `request_diagnosis_history_ibfk_1` FOREIGN KEY (`request_id`) REFERENCES `requests` (`id`) ON DELETE CASCADE!,

	fk_request_dispatch_log => q!
		ALTER TABLE `request_dispatch_log`
			ADD CONSTRAINT `request_dispatch_log_ibfk_1` FOREIGN KEY (`request_id`) REFERENCES `requests` (`id`) ON DELETE CASCADE!,

    fk_request_draft_report => q!
        ALTER TABLE `request_draft_report`
            ADD CONSTRAINT `request_draft_report_ibfk_1` FOREIGN KEY (`request_id`) REFERENCES `requests` (`id`) ON DELETE CASCADE,
            ADD CONSTRAINT `request_draft_report_ibfk_2` FOREIGN KEY (`diagnosis_id`) REFERENCES `diagnoses` (`id`),
            ADD CONSTRAINT `request_draft_report_ibfk_3` FOREIGN KEY (`user_id`) REFERENCES `draft_report_users` (`id`),
            ADD CONSTRAINT `request_draft_report_ibfk_4` FOREIGN KEY (`secondary_diagnosis_id`) REFERENCES `diagnoses` (`id`)!,

    fk_request_error_code => q!
        ALTER TABLE `request_error_code`
            ADD CONSTRAINT `request_error_code_ibfk_3` FOREIGN KEY (`error_code_id`) REFERENCES `error_codes` (`id`),
            ADD CONSTRAINT `request_error_code_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
            ADD CONSTRAINT `request_error_code_ibfk_1` FOREIGN KEY (`request_id`) REFERENCES `requests` (`id`) ON DELETE CASCADE!,

    fk_request_error_code_details => q!
        ALTER TABLE `request_error_code_details`
            ADD CONSTRAINT `request_error_code_details_ibfk_1` FOREIGN KEY (`request_error_code_id`)
                REFERENCES `request_error_code` (`id`) ON DELETE CASCADE!,

	fk_request_external_ref => q!
		ALTER TABLE `request_external_ref`
			ADD CONSTRAINT `request_external_ref_ibfk_1` FOREIGN KEY (`request_id`) REFERENCES `requests` (`id`) ON DELETE CASCADE!,

    fk_request_form => q!
        ALTER TABLE `request_form`
            ADD CONSTRAINT `request_form_ibfk1` FOREIGN KEY (`user_id`) REFERENCES
                `users` (`id`),
            ADD CONSTRAINT `request_form_ibfk2` FOREIGN KEY (`location_id`) REFERENCES
                `referral_sources` (`id`)!,

	fk_request_general_notes => q!
		ALTER TABLE `request_general_notes`
			ADD CONSTRAINT `request_general_notes_ibfk_1` FOREIGN KEY (`request_id`) REFERENCES `requests` (`id`) ON DELETE CASCADE!,

    fk_request_history => q!
        ALTER TABLE `request_history`
          ADD CONSTRAINT `request_history_ibfk_1` FOREIGN KEY (`request_id`) REFERENCES `requests` (`id`) ON DELETE CASCADE,
          ADD CONSTRAINT `request_history_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)!,

    fk_request_initial_screen => q!
        ALTER TABLE `request_initial_screen`
          ADD CONSTRAINT `request_initial_screen_ibfk_1` FOREIGN KEY (`request_id`) REFERENCES `requests` (`id`) ON DELETE CASCADE,
          ADD CONSTRAINT `request_initial_screen_ibfk_2` FOREIGN KEY (`screen_id`) REFERENCES `screens` (`id`)!,

	fk_request_lab_section_foreign_id => q!
		ALTER TABLE `request_lab_section_foreign_id`
		  ADD CONSTRAINT `request_lab_section_foreign_id_ibfk_2` FOREIGN KEY (`lab_section_id`) REFERENCES `lab_sections` (`id`),
		  ADD CONSTRAINT `request_section_foreign_id_ibfk_1` FOREIGN KEY (`request_id`) REFERENCES `requests` (`id`) ON DELETE CASCADE!,

	fk_request_lab_section_notes => q!
		ALTER TABLE `request_lab_section_notes`
			ADD CONSTRAINT `request_lab_section_notes_ibfk_2` FOREIGN KEY (`lab_section_id`) REFERENCES `lab_sections` (`id`),
		   ADD CONSTRAINT `request_section_notes_ibfk_1` FOREIGN KEY (`request_id`) REFERENCES `requests` (`id`) ON DELETE CASCADE!,

    fk_request_lab_test_history => q!
        ALTER TABLE `request_lab_test_history`
          ADD CONSTRAINT `request_lab_test_history_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
          ADD CONSTRAINT `request_lab_test_history_ibfk_1` FOREIGN KEY (`request_id`) REFERENCES `requests` (`id`) ON DELETE CASCADE!,

	fk_request_lab_test_results => q!
		ALTER TABLE `request_lab_test_results`
			ADD CONSTRAINT `request_lab_test_results_ibfk_2` FOREIGN KEY (`lab_test_id`) REFERENCES `lab_tests` (`id`),
			ADD CONSTRAINT `request_lab_test_results_ibfk_1` FOREIGN KEY (`request_id`) REFERENCES `requests` (`id`) ON DELETE CASCADE!,

	fk_request_lab_test_status => q!
		ALTER TABLE `request_lab_test_status`
			ADD CONSTRAINT `request_lab_test_status_ibfk_1` FOREIGN KEY (`request_id`) REFERENCES `requests` (`id`) ON DELETE CASCADE,
			ADD CONSTRAINT `request_lab_test_status_ibfk_2` FOREIGN KEY (`lab_test_id`) REFERENCES `lab_tests` (`id`),
			ADD CONSTRAINT `request_lab_test_status_ibfk_3` FOREIGN KEY (`status_option_id`) REFERENCES `lab_test_status_options` (`id`),
			ADD CONSTRAINT `request_lab_test_status_ibfk_4` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)!,

    fk_request_option => q!
        ALTER TABLE `request_option`
			ADD CONSTRAINT `request_option_ibfk_1` FOREIGN KEY (`request_id`) REFERENCES `requests` (`id`) ON DELETE CASCADE,
			ADD CONSTRAINT `request_option_ibfk_2` FOREIGN KEY (`option_id`) REFERENCES `additional_options` (`id`)!,

	fk_request_phone_log => q!
		ALTER TABLE `request_phone_log`
			ADD CONSTRAINT `request_phone_log_ibfk_1` FOREIGN KEY (`request_id`) REFERENCES `requests` (`id`) ON DELETE CASCADE,
			ADD CONSTRAINT `request_phone_log_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)!,

	fk_request_print_log => q!
		ALTER TABLE `request_print_log`
			ADD CONSTRAINT `request_print_log_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
			ADD CONSTRAINT `request_print_log_ibfk_1` FOREIGN KEY (`request_id`) REFERENCES `requests` (`id`) ON DELETE CASCADE!,

    fk_request_report_detail => q!
       ALTER TABLE `request_report_detail`
            ADD CONSTRAINT `request_report_detail_ibfk_2` FOREIGN KEY (`diagnosis_id`) REFERENCES `diagnoses` (`id`),
	        ADD CONSTRAINT `request_report_detail_ibfk_1` FOREIGN KEY (`request_id`) REFERENCES `requests` (`id`) ON DELETE CASCADE!,

	fk_request_report_history => q!
		ALTER TABLE `request_report_history`
			ADD CONSTRAINT `request_report_history_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
			ADD CONSTRAINT `request_report_history_ibfk_1` FOREIGN KEY (`request_id`) REFERENCES `requests` (`id`) ON DELETE CASCADE!,

    fk_request_result_summaries => q!
        ALTER TABLE `request_result_summaries`
          ADD CONSTRAINT `request_result_summaries_ibfk_3` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
          ADD CONSTRAINT `request_result_summaries_ibfk_2` FOREIGN KEY (`lab_section_id`) REFERENCES `lab_sections` (`id`),
          ADD CONSTRAINT `request_result_summaries_ibfk_1` FOREIGN KEY (`request_id`) REFERENCES `requests` (`id`) ON DELETE CASCADE!,

    fk_request_secondary_diagnosis => q!
        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!,

    fk_request_specimen => q!
        ALTER TABLE `request_specimen`
			ADD CONSTRAINT `request_specimen_ibfk_1` FOREIGN KEY (`request_id`) REFERENCES `requests` (`id`) ON DELETE CASCADE,
			ADD CONSTRAINT `request_specimen_ibfk_2` FOREIGN KEY (`specimen_id`) REFERENCES `specimens` (`id`)!,

    fk_request_specimen_detail => q!
        ALTER TABLE `request_specimen_detail`
        	ADD CONSTRAINT `request_specimen_detail_ibfk_1` FOREIGN KEY (`request_id`) REFERENCES `requests` (`id`) ON DELETE CASCADE!,

    fk_request_storage => q!
        ALTER TABLE `request_storage`
            ADD CONSTRAINT `request_storage_ibfk_1` FOREIGN KEY (`request_id`) REFERENCES `requests` (`id`) ON DELETE CASCADE,
            ADD CONSTRAINT `request_storage_ibfk_2` FOREIGN KEY (`specimen_id`) REFERENCES `specimens` (`id`),
			ADD CONSTRAINT `request_storage_ibfk_3` FOREIGN KEY (`rack_id`) REFERENCES `storage_racks` (`id`)!,

    fk_request_trial => q!
        ALTER TABLE `request_trial`
			ADD CONSTRAINT `request_trial_ibfk_1` FOREIGN KEY (`request_id`) REFERENCES `requests` (`id`) ON DELETE CASCADE,
			ADD CONSTRAINT `request_trial_ibfk_2` FOREIGN KEY (`trial_id`) REFERENCES `clinical_trials` (`id`)!,

	fk_request_view_log => q!
		ALTER TABLE `request_view_log`
			ADD CONSTRAINT `request_view_log_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
			ADD CONSTRAINT `request_view_log_ibfk_1` FOREIGN KEY (`request_id`) REFERENCES `requests` (`id`) ON DELETE CASCADE!,

    fk_requests => q!
        ALTER TABLE `requests`
          ADD CONSTRAINT `requests_ibfk_3` FOREIGN KEY (`status_option_id`) REFERENCES `status_options` (`id`),
          ADD CONSTRAINT `requests_ibfk_1` FOREIGN KEY (`referrer_department_id`) REFERENCES `referrer_department` (`id`),
          ADD CONSTRAINT `requests_ibfk_2` FOREIGN KEY (`patient_case_id`) REFERENCES `patient_case` (`id`)!,

    fk_result_summary_expansion => q!
        ALTER TABLE `result_summary_expansion`
            ADD CONSTRAINT `result_summary_expansion_ibfk_1` FOREIGN KEY (`result_summary_option_id`)
                REFERENCES `result_summary_options` (`id`)!,

    fk_result_summary_lab_test => q!
        ALTER TABLE `result_summary_lab_test`
            ADD CONSTRAINT `result_summary_lab_test_ibfk1` FOREIGN KEY (`result_summary_id`) REFERENCES `result_summary_options` (`id`),
            ADD CONSTRAINT `result_summary_lab_test_ibfk2` FOREIGN KEY (`lab_test_id`) REFERENCES `lab_tests` (`id`)!,

	fk_result_summary_options => q!
		ALTER TABLE `result_summary_options`
			ADD CONSTRAINT `result_summary_options_ibfk_1` FOREIGN KEY (`lab_section_id`) REFERENCES `lab_sections` (`id`)!,

    fk_rfc => q!
        ALTER TABLE `rfc`
            ADD CONSTRAINT `rfc_user_ibfk1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)!,

    fk_screen_lab_test => q!
        ALTER TABLE `screen_lab_test`
			ADD CONSTRAINT `screen_lab_test_ibfk_1` FOREIGN KEY (`screen_id`) REFERENCES `screens` (`id`),
			ADD CONSTRAINT `screen_lab_test_ibfk_2` FOREIGN KEY (`lab_test_id`) REFERENCES `lab_tests` (`id`);!,

    fk_screen_lab_test_detail => q!
        ALTER TABLE `screen_lab_test_detail`
			ADD CONSTRAINT `screen_lab_test_detail_ibfk_1` FOREIGN KEY (`screen_id`) REFERENCES `screens` (`id`),
			ADD CONSTRAINT `screen_lab_test_detail_ibfk_2` FOREIGN KEY (`lab_section_id`) REFERENCES `lab_sections` (`id`);!,

    fk_screens => q!
        ALTER TABLE `screens`
			ADD CONSTRAINT `screens_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `screen_category` (`id`);!,

	fk_specimen_lab_test => q!
		ALTER TABLE `specimen_lab_test`
			ADD CONSTRAINT `specimen_lab_test_ibfk_2` FOREIGN KEY (`lab_test_id`) REFERENCES `lab_tests` (`id`),
			ADD CONSTRAINT `specimen_lab_test_ibfk_1` FOREIGN KEY (`specimen_id`) REFERENCES `specimens` (`id`)!,

    fk_specimen_sample_type => q!
        ALTER TABLE `specimen_sample_type`
        	ADD CONSTRAINT `specimen_sample_type_ibfk_1` FOREIGN KEY (`specimen_id`) REFERENCES `specimens` (`id`),
            ADD CONSTRAINT `specimen_sample_type_ibfk_2` FOREIGN KEY (`sample_type_id`) REFERENCES `sample_types` (`id`)!,

    fk_user_group_function => q!
		ALTER TABLE `user_group_function`
		ADD CONSTRAINT `user_group_function_ibfk_2` FOREIGN KEY (`group_id`) REFERENCES `user_groups` (`id`),
		  ADD CONSTRAINT `user_group_function_ibfk_1` FOREIGN KEY (`function_id`) REFERENCES `user_functions` (`id`)!,

# script cannot create this FK, but can delete it & can be created manually - WFT???
#    fk_user_message => q!
#        ALTER TABLE `user_message`
#            ADD CONSTRAINT `user_message_ibfk2` FOREIGN KEY (`recipient_id`) REFERENCES `users` (`id`),
#            ADD CONSTRAINT `user_message_ibfk1` FOREIGN KEY (`sender_id`) REFERENCES `users` (`id`)!,

    fk_user_locations => q!
        ALTER TABLE `user_locations`
        ADD CONSTRAINT `user_location_ibfk_1` FOREIGN KEY (`region_code`) REFERENCES `parent_organisations` (`parent_code`)!,

    fk_user_permission => q!
        ALTER TABLE `user_permission`
           ADD CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`function_id`)
            REFERENCES `user_functions` (`id`),
           ADD CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`);!,

    fk_users => q!
        ALTER TABLE `users`
            ADD CONSTRAINT `users_ibfk_1` FOREIGN KEY (`group_id`) REFERENCES `user_groups` (`id`),
            ADD CONSTRAINT `users_ibfk_2` FOREIGN KEY (`user_location_id`) REFERENCES `user_locations` (`id`);!,
};