#----------------------------------------------------------------------------------------------------------#
-- here is the failsafe method to turn off contraints and allows
-- you to drop the tables safely then turn contraints back on
SET FOREIGN_KEY_CHECKS=0;
	#table drops
DROP TABLE IF EXISTS Hospital;
DROP TABLE IF EXISTS Patients;
DROP TABLE IF EXISTS Staff;
DROP TABLE IF EXISTS Medication;
DROP TABLE IF EXISTS Ward;
	#logical table drops
DROP TABLE IF EXISTS patientMedication;
DROP TABLE IF EXISTS patientsOnWard;
DROP TABLE IF EXISTS prescribesMedication;
DROP TABLE IF EXISTS hospitalPatients;
	#trigger drops
DROP TABLE IF EXISTS table1_seq;
DROP TABLE IF EXISTS table2_seq;
DROP TABLE IF EXISTS table3_seq;
DROP TABLE IF EXISTS table4_seq;
DROP TABLE IF EXISTS table5_seq;
SET FOREIGN_KEY_CHECKS=1;
#----------------------------------------------------------------------------------------------------------#

CREATE TABLE IF NOT EXISTS Hospital (
    hospitalID VARCHAR(7) NOT NULL PRIMARY KEY DEFAULT '0',
    hospitalName VARCHAR(50) NOT NULL,
    postcode CHAR(8) NOT NULL,
    generalPhone VARCHAR(14) NOT NULL,
    generalEmail VARCHAR(50),
    hospitalManager VARCHAR(50)
)  ENGINE=INNODB;

CREATE TABLE table1_seq (
    hospitalID INT NOT NULL AUTO_INCREMENT PRIMARY KEY
);

DELIMITER $$
CREATE TRIGGER tg_table1_insert
BEFORE INSERT ON Hospital
FOR EACH ROW
BEGIN
  INSERT INTO table1_seq VALUES (NULL);
  SET NEW.hospitalID = CONCAT('NHS', LPAD(LAST_INSERT_ID(), 3, '0'));
END$$
DELIMITER ;

CREATE TABLE IF NOT EXISTS Patients (
    nationalInsuranceNo VARCHAR(7) NOT NULL PRIMARY KEY DEFAULT '0',
    patientForename VARCHAR(50) NOT NULL,
    patientSurname VARCHAR(50) NOT NULL,
    phoneNo CHAR(14),
    nextOfKin VARCHAR(50)
);

CREATE TABLE table2_seq (
    nationalInsuranceNo INT NOT NULL AUTO_INCREMENT PRIMARY KEY
);

DELIMITER $$
CREATE TRIGGER tg_table2_insert
BEFORE INSERT ON Patients
FOR EACH ROW
BEGIN
  INSERT INTO table2_seq VALUES (NULL);
  SET NEW.nationalInsuranceNo = CONCAT('PAT', LPAD(LAST_INSERT_ID(), 3, '0'));
END$$
DELIMITER ;

CREATE TABLE IF NOT EXISTS Staff (
    staffID VARCHAR(7) NOT NULL PRIMARY KEY DEFAULT '0',
    staffForename VARCHAR(50) NOT NULL,
    staffSurname VARCHAR(50) NOT NULL,
    salary DECIMAL(8 , 2 ),
    position VARCHAR(50)
);

CREATE TABLE table3_seq (
    staffID INT NOT NULL AUTO_INCREMENT PRIMARY KEY
);

DELIMITER $$
CREATE TRIGGER tg_table3_insert
BEFORE INSERT ON Staff
FOR EACH ROW
BEGIN
  INSERT INTO table3_seq VALUES (NULL);
  SET NEW.staffID = CONCAT('STA', LPAD(LAST_INSERT_ID(), 3, '0'));
END$$
DELIMITER ;

CREATE TABLE IF NOT EXISTS Medication (
    medID VARCHAR(7) NOT NULL PRIMARY KEY DEFAULT '0',
    medicationName VARCHAR(50) NOT NULL,
    contraIndicator VARCHAR(50),
    medDose VARCHAR(50) NOT NULL,
    medUsage VARCHAR(50) NOT NULL
);

CREATE TABLE table4_seq (
    medID INT NOT NULL AUTO_INCREMENT PRIMARY KEY
);

DELIMITER $$
CREATE TRIGGER tg_table4_insert
BEFORE INSERT ON Medication
FOR EACH ROW
BEGIN
  INSERT INTO table4_seq VALUES (NULL);
  SET NEW.medID = CONCAT('MED', LPAD(LAST_INSERT_ID(), 3, '0'));
END$$
DELIMITER ;

CREATE TABLE IF NOT EXISTS Ward (
    wardID VARCHAR(10) NOT NULL PRIMARY KEY DEFAULT '0',
    staffID CHAR(10) NOT NULL,
    hospitalID CHAR(10) NOT NULL,
    wardName VARCHAR(50) NOT NULL,
    specialism VARCHAR(50) NOT NULL,
    FOREIGN KEY (staffID)
        REFERENCES Staff (staffID),
    FOREIGN KEY (hospitalID)
        REFERENCES Hospital (hospitalID)
);

CREATE TABLE table5_seq (
    wardID INT NOT NULL AUTO_INCREMENT PRIMARY KEY
);

DELIMITER $$
CREATE TRIGGER tg_table5_insert
BEFORE INSERT ON Ward
FOR EACH ROW
BEGIN
  INSERT INTO table5_seq VALUES (NULL);
  SET NEW.wardID = CONCAT('WAR', LPAD(LAST_INSERT_ID(), 3, '0'));
END$$
DELIMITER ;

-- logical
CREATE TABLE IF NOT EXISTS hospitalPatients (
    nationalInsuranceNo CHAR(10) NOT NULL,
    hospitalID CHAR(10) NOT NULL,
    dateOfAdmission DATE NOT NULL,
    PRIMARY KEY (nationalInsuranceNo , hospitalID),
    FOREIGN KEY (nationalInsuranceNo)
        REFERENCES Patients (nationalInsuranceNo),
    FOREIGN KEY (hospitalID)
        REFERENCES Hospital (hospitalID)
);

-- logical
CREATE TABLE IF NOT EXISTS patientsOnWard (
    wardID CHAR(10) NOT NULL,
    nationalInsuranceNo CHAR(10) NOT NULL,
    hospitalID CHAR(10) NOT NULL,
    PRIMARY KEY (wardID , nationalInsuranceNo , hospitalID),
    FOREIGN KEY (wardID)
        REFERENCES Ward (wardID),
    FOREIGN KEY (nationalInsuranceNo)
        REFERENCES Patients (nationalInsuranceNo),
    FOREIGN KEY (hospitalID)
        REFERENCES Hospital (hospitalID)
);

-- logical
CREATE TABLE IF NOT EXISTS patientMedication (
    medID CHAR(10) NOT NULL,
    nationalInsuranceNo CHAR(10) NOT NULL,
    medStart DATE NOT NULL,
    medEnd DATE NOT NULL,
    PRIMARY KEY (medID , nationalInsuranceNo),
    FOREIGN KEY (medID)
        REFERENCES Medication (medID),
    FOREIGN KEY (nationalInsuranceNo)
        REFERENCES Patients (nationalInsuranceNo)
);

-- logical
CREATE TABLE IF NOT EXISTS prescribesMedication (
    medID CHAR(10) NOT NULL,
    staffID CHAR(10) NOT NULL,
    PRIMARY KEY (medID , staffID),
    FOREIGN KEY (medID)
        REFERENCES Medication (medID),
    FOREIGN KEY (staffID)
        REFERENCES Staff (staffID)
);

#----------------------------------------------------------------------------------------------------------#