CREATE TABLE `case` (
  `patient_id` int(11) unsigned NOT NULL,
  `location_id` int(11) unsigned NOT NULL,
  PRIMARY KEY  (`patient_id`,`location_id`),
  KEY `location` (`location_id`),
  CONSTRAINT `case_ibfk_2` FOREIGN KEY (`location_id`) REFERENCES `location` (`id`),
  CONSTRAINT `case_ibfk_1` FOREIGN KEY (`patient_id`) REFERENCES `patient` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE `case_unit_number` (
  `case_patient_id` int(11) unsigned NOT NULL,
  `case_location_id` int(11) unsigned NOT NULL,
  `unit_number` varchar(255) NOT NULL,
  PRIMARY KEY  (`case_patient_id`,`case_location_id`,`unit_number`),
  CONSTRAINT `case` FOREIGN KEY (`case_patient_id`, `case_location_id`) REFERENCES `case` (`patient_id`, `location_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE `clinician_region` (
  `region_code` varchar(3) NOT NULL,
  `referrer_id` int(11) unsigned NOT NULL,
  PRIMARY KEY  (`region_code`,`referrer_id`),
  KEY `referrer` (`referrer_id`),
  CONSTRAINT `clinician_region_ibfk_2` FOREIGN KEY (`referrer_id`) REFERENCES `referrer` (`id`),
  CONSTRAINT `clinician_region_ibfk_1` FOREIGN KEY (`region_code`) REFERENCES `region` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE `general_practice` (
  `location_id` int(11) unsigned NOT NULL,
  `address` text,
  `postal_code` varchar(10) NOT NULL,
  PRIMARY KEY  (`location_id`),
  CONSTRAINT `general_practice_ibfk_1` FOREIGN KEY (`location_id`) REFERENCES `location` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE `hospital` (
  `location_id` int(11) unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `region_code` varchar(3) NOT NULL,
  PRIMARY KEY  (`location_id`),
  KEY `region` (`region_code`),
  CONSTRAINT `hospital_ibfk_2` FOREIGN KEY (`region_code`) REFERENCES `region` (`code`),
  CONSTRAINT `hospital_ibfk_1` FOREIGN KEY (`location_id`) REFERENCES `location` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE `hospital_posting` (
  `referrer_id` int(11) unsigned NOT NULL,
  `hospital_id` int(11) unsigned NOT NULL,
  `speciality_national_code` int(11) unsigned NOT NULL,
  KEY `referrer_posting` (`referrer_id`,`hospital_id`),
  KEY `speciality` (`speciality_national_code`),
  KEY `hospital` (`hospital_id`),
  CONSTRAINT `hospital_posting_ibfk_3` FOREIGN KEY (`hospital_id`) REFERENCES `hospital` (`location_id`),
  CONSTRAINT `hospital_posting_ibfk_1` FOREIGN KEY (`referrer_id`, `hospital_id`) REFERENCES `referrer_posting` (`referrer_id`, `location_id`),
  CONSTRAINT `hospital_posting_ibfk_2` FOREIGN KEY (`speciality_national_code`) REFERENCES `speciality` (`national_code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE `location` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `organisation_code` char(6) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE `patient` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `last_name` varchar(25) NOT NULL,
  `first_name` varchar(30) NOT NULL,
  `dob` date default NULL,
  `gender` enum('M','F','U') NOT NULL,
  `nhs_number` varchar(10) default NULL,
  `time` datetime default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE `referrer` (
  `id` int(11) unsigned NOT NULL auto_increment,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE `referrer_posting` (
  `referrer_id` int(11) unsigned NOT NULL default '0',
  `location_id` int(11) unsigned NOT NULL default '0',
  `created_at` datetime NOT NULL,
  `inactivated_at` datetime default NULL,
  `active` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`referrer_id`,`location_id`),
  KEY `location` (`location_id`),
  CONSTRAINT `location` FOREIGN KEY (`location_id`) REFERENCES `location` (`id`),
  CONSTRAINT `referrer` FOREIGN KEY (`referrer_id`) REFERENCES `referrer` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE `region` (
  `code` varchar(3) NOT NULL,
  `description` varchar(255) NOT NULL,
  PRIMARY KEY  (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE `request` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `created_at` datetime NOT NULL,
  `year` int(5) NOT NULL,
  `request_number` int(5) NOT NULL,
  `case_location_id` int(11) unsigned NOT NULL,
  `case_patient_id` int(11) unsigned NOT NULL,
  `case_unit_number_unit_number` varchar(255) NOT NULL,
  `referrer_id` int(11) unsigned NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `lab_number` (`year`,`request_number`),
  KEY `referrer_posting` (`case_location_id`,`referrer_id`),
  KEY `case_unit_number` (`case_patient_id`,`case_location_id`,`case_unit_number_unit_number`),
  CONSTRAINT `request_ibfk_2` FOREIGN KEY (`case_patient_id`, `case_location_id`, `case_unit_number_unit_number`) REFERENCES `case_unit_number` (`case_patient_id`, `case_location_id`, `unit_number`),
  CONSTRAINT `request_ibfk_1` FOREIGN KEY (`case_location_id`, `referrer_id`) REFERENCES `referrer_posting` (`location_id`, `referrer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE `speciality` (
  `national_code` int(11) unsigned NOT NULL,
  `description` varchar(255) NOT NULL,
  PRIMARY KEY  (`national_code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;