use hilis4;
        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;

       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;

        CREATE TABLE `requests` (
          `id` int NOT NULL auto_increment,
          `request_number` smallint 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;

        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;

		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;

        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;

        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;

 		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`);


use outreach;

    CREATE TABLE `patient_dispatch_detail` (
        `patient_id` INT(11) NOT NULL DEFAULT '0',
        `dispatch_to` ENUM('home','alternate','GP') NOT NULL DEFAULT 'home',
        PRIMARY KEY (`patient_id`)
    )
    COLLATE='latin1_swedish_ci'
    ENGINE=InnoDB;

    CREATE TABLE `request_pack_dispatch` (
        `request_id` INT(11) NOT NULL DEFAULT '0',
        `pack_due` DATE NULL DEFAULT NULL,
        `pack_sent` DATE NULL DEFAULT NULL,
        `return_due` DATE NULL DEFAULT NULL,
        `time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (`request_id`))
    COLLATE='latin1_swedish_ci'
    ENGINE=InnoDB;

    CREATE TABLE `request_results` (
        `request_id` INT(11) NOT NULL DEFAULT '0',
        `param_id` SMALLINT(6) NOT NULL DEFAULT '0',
        `result` VARCHAR(255) NOT NULL DEFAULT '',
        PRIMARY KEY (`request_id`, `param_id`),
        INDEX `request_results_ibfk_1` (`param_id`),
        CONSTRAINT `request_results_ibfk_1` FOREIGN KEY (`param_id`) REFERENCES `lab_params` (`id`)
    )
    COLLATE='latin1_swedish_ci'
    ENGINE=InnoDB;

    CREATE TABLE `request_followup` (
        `request_id` INT(11) NOT NULL DEFAULT '0',
        `followup_option_id` SMALLINT(6) NOT NULL DEFAULT '0',
        `time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (`request_id`),
        INDEX `followup_option_id` (`followup_option_id`),
        CONSTRAINT `request_followup_ibfk_1` FOREIGN KEY (`followup_option_id`) REFERENCES `followup_options` (`id`)
    )
    COLLATE='latin1_swedish_ci'
    ENGINE=InnoDB;

    CREATE TABLE `followup_options` (
       `id` SMALLINT(6) NOT NULL AUTO_INCREMENT,
       `option` VARCHAR(20) NULL DEFAULT NULL,
       `label` VARCHAR(25) NULL DEFAULT NULL,
       PRIMARY KEY (`id`)
   )
   COLLATE='latin1_swedish_ci'
   ENGINE=InnoDB;

    CREATE TABLE `lab_params` (
        `id` SMALLINT(6) NOT NULL AUTO_INCREMENT,
        `param_name` VARCHAR(25) NOT NULL DEFAULT '',
        `field_label` VARCHAR(255) NOT NULL DEFAULT '',
        `field_type` ENUM('menu','int','decimal') NULL DEFAULT 'int',
        `department_id` SMALLINT(6) NOT NULL DEFAULT '0',
        PRIMARY KEY (`id`),
        UNIQUE INDEX `param_name` (`param_name`),
        INDEX `department_id` (`department_id`),
        CONSTRAINT `lab_params_ibfk_1` FOREIGN KEY (`department_id`) REFERENCES `result_types` (`id`)
    )
    COLLATE='latin1_swedish_ci'
    ENGINE=InnoDB;