DROP DATABASE technology_university_hospital;
CREATE DATABASE technology_university_hospital;
USE Technology_University_Hospital;

CREATE TABLE Employee (
/* Staff_Number Auto Increment for every new Staff */
Staff_Number INT(9) NOT NULL AUTO_INCREMENT,
/*
Depending of what the Staff_Type is it will determine their type
Since there aren't much Staff_Type the max letter int will be one
*/

Staff_Type INT(24) NOT NULL, INDEX(Staff_Type),
/*
Since there is a slight possibility that the Staff_Number might be the same when deleted,
We will have Contact_Number as a primary key aswell
*/
Employee_Contact_Number INT(24) NOT NULL,
Employee_First_Name VARCHAR(24) NOT NULL,
Employee_Last_Name VARCHAR(24) NOT NULL,
Employee_Gender VARCHAR(1) NOT NULL,

PRIMARY KEY(Staff_Number, Employee_Contact_Number)
);

CREATE TABLE Doctor (
    Staff_Number INT(9),
    Staff_Type INT(24), INDEX(Staff_Type),

    Salary INT(255),
    Address VARCHAR(75),
    Doctor_Types VARCHAR(38),

    PRIMARY KEY (Staff_Number),
    CONSTRAINT  FOREIGN KEY (Staff_Number) REFERENCES Employee(Staff_Number) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT  FOREIGN KEY (Staff_Type) REFERENCES Employee(Staff_Type) ON DELETE CASCADE ON UPDATE CASCADE

);
/*
    CREATE TABLE Intern (

    );
    CREATE TABLE House_Officers (

    );
    CREATE TABLE Registrars (

    );
    CREATE TABLE Staff_Specialist (

    );
    CREATE TABLE Visiting_Medical_Officers (

    );
*/
CREATE TABLE Doctor (
    Staff_Number INT(9),
    Staff_Type INT(24), INDEX(Staff_Type),

    Salary INT(255),
    Address VARCHAR(75),
    Doctor_Types VARCHAR(38),

    PRIMARY KEY (Staff_Number),
    CONSTRAINT  FOREIGN KEY (Staff_Number) REFERENCES Employee(Staff_Number) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT  FOREIGN KEY (Staff_Type) REFERENCES Employee(Staff_Type) ON DELETE CASCADE ON UPDATE CASCADE

);
/*
    CREATE TABLE Intern (

    );
    CREATE TABLE House_Officers (

    );
    CREATE TABLE Registrars (

    );
    CREATE TABLE Staff_Specialist (

    );
    CREATE TABLE Visiting_Medical_Officers (

    );
*/
CREATE TABLE Nurses (
    Staff_Number INT(9),
    Staff_Type INT(20),

    Salary INT(255),
    Address VARCHAR(75),

    PRIMARY KEY (Staff_Number),
    CONSTRAINT FOREIGN KEY (Staff_Number) REFERENCES Employee(Staff_Number) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT FOREIGN KEY (Staff_Type) REFERENCES Employee(Staff_Type) ON DELETE CASCADE ON UPDATE CASCADE

);
    CREATE TABLE Ward (
        Staff_Number INT(9) NOT NULL,
        Ward_ID INT(9) NOT NULL,

        Ward_Name VARCHAR(24) NOT NULL,
        Room_Type VARCHAR(7) NOT NULL, INDEX(Room_Type),
        Bed_ID INT(9) NOT NULL, INDEX(Bed_ID),


        PRIMARY KEY (Staff_Number, Ward_ID, Bed_ID),
        CONSTRAINT FOREIGN KEY (Staff_Number) REFERENCES Nurses(Staff_Number) ON DELETE CASCADE ON UPDATE CASCADE

    );
        CREATE TABLE Private (
            Room_Type VARCHAR(7) NOT NULL,

            Bed_ID INT(9) NOT NULL,
            /* Medical, Surgical, Intensive Care, Psychiatric */
            Discipline VARCHAR(24) NOT NULL,
            Special_Information VARCHAR(255),

            PRIMARY KEY (Bed_ID),
            CONSTRAINT FOREIGN KEY (Bed_ID) REFERENCES Ward(Bed_ID) ON DELETE CASCADE ON UPDATE CASCADE,
            CONSTRAINT FOREIGN KEY (Room_Type) REFERENCES Ward(Room_Type) ON DELETE CASCADE ON UPDATE CASCADE
        );
        CREATE TABLE Shared (
            Room_Type VARCHAR(7) NOT NULL,

            Bed_ID INT(9) NOT NULL,
            /* Medical, Surgical, Intensive Care, Psychiatric */
            Discipline VARCHAR(24),
            Special_Information VARCHAR(255),

            PRIMARY KEY (Bed_ID),
            CONSTRAINT FOREIGN KEY (Bed_ID) REFERENCES Ward(Bed_ID) ON DELETE CASCADE ON UPDATE CASCADE,
            CONSTRAINT FOREIGN KEY (Room_Type) REFERENCES Ward(Room_Type) ON DELETE CASCADE ON UPDATE CASCADE,
        );

CREATE TABLE Administration_Staff (
    Staff_Number INT(9),

    Salary INT(255),
    Address VARCHAR(75),



    PRIMARY KEY (Staff_Number),
    CONSTRAINT FOREIGN KEY (Staff_Number) REFERENCES Employee(Staff_Number) ON DELETE CASCADE ON UPDATE CASCADE


);
    CREATE TABLE Patients (
        Staff_Number INT(9),
        Patient_ID INT(9) NOT NULL,

        Patient_First_Name VARCHAR(24) NOT NULL,
        Patient_Middle_Name VARCHAR(24) NOT NULL,
        Patient_Last_Name VARCHAR(24) NOT NULL,
        Patient_Gender VARCHAR(1) NOT NULL,
        Date_Of_Birth DATE NOT NULL,
        Patient_Contact_Number INT(24) NOT NULL,
        Patient_Address VARCHAR(75) NOT NULL,
        Patient_Emergency_Contact_Name VARCHAR(24) NOT NULL,
        Patient_Emergency_Contact_Number INT(24) NOT NULL,
        Parient_Insurer VARCHAR(24) NOT NULL,
        Patient_Medicare_Number INT(24) NOT NULL,


        PRIMARY KEY(Patient_ID),
        CONSTRAINT FOREIGN KEY (Staff_Number) REFERENCES Administration_Staff(Staff_Number) ON DELETE CASCADE ON UPDATE CASCADE
    );
        CREATE TABLE Medical_Records (
            Patient_ID INT(9),

            Staff_Number INT(9),
            Date_Of_Treatment DATE,
            Treatments_Given VARCHAR(255),
            Ward_Type VARCHAR(24),
            Ward_Name VARCHAR(24),
            Ward_ID INT(24),
            Bed_ID INT(24),
            Patient_Diagnosis VARCHAR(255) NOT NULL,

            PRIMARY KEY(Patient_ID),
            CONSTRAINT FOREIGN KEY (Patient_ID) REFERENCES Patients(Patient_ID) ON DELETE CASCADE ON UPDATE CASCADE,
            CONSTRAINT FOREIGN KEY (Ward_Type) REFERENCES Ward(Ward_Type) ON DELETE CASCADE ON UPDATE CASCADE,
            CONSTRAINT FOREIGN KEY (Ward_Name) REFERENCES Ward(Ward_Name) ON DELETE CASCADE ON UPDATE CASCADE,
            CONSTRAINT FOREIGN KEY (Ward_ID) REFERENCES Ward(Ward_ID) ON DELETE CASCADE ON UPDATE CASCADE,
            CONSTRAINT FOREIGN KEY (Bed_ID) REFERENCES Ward(Bed_ID) ON DELETE CASCADE ON UPDATE CASCADE
        );
            CREATE TABLE Admission (
                Patient_ID INT(9),

                Ward_ID INT(9),
                /* Don't know if Ward_Type is needed */
                Bed_ID INT(9),
                Staff_Number INT(9),

                PRIMARY KEY (Patient_ID),
                CONSTRAINT FOREIGN KEY (Patient_ID) REFERENCES Patients(Patient_ID) ON DELETE CASCADE ON UPDATE CASCADE,
                CONSTRAINT FOREIGN KEY (Ward_ID) REFERENCES Ward(Ward_ID) ON DELETE CASCADE ON UPDATE CASCADE,
                CONSTRAINT FOREIGN KEY (Bed_ID) REFERENCES Ward(Bed_ID) ON DELETE CASCADE ON UPDATE CASCADE,
                CONSTRAINT FOREIGN KEY (Staff_Number) REFERENCES Medical_Records(Staff_Number) ON DELETE CASCADE ON UPDATE CASCADE
            );
            CREATE TABLE Invoice (
                Patient_ID INT(9),

                Prescribed VARCHAR(255),
                Treatments VARCHAR(255),
                Hospital_Costs Int(255),

                PRIMARY KEY (Patient_ID),
                CONSTRAINT FOREIGN KEY (Patient_ID) REFERENCES Patients(Patient_ID) ON DELETE CASCADE ON UPDATE CASCADE
            );
        CREATE TABLE Medicines (
            Patient_ID INT(9),

            Generic_Name VARCHAR(24),
            Brand_Name VARCHAR(24),
            Manufacturer VARCHAR(24),
            Price_Per_Unit INT(255),
            Quantity_In_Stock INT(255),
            Administration_Method VARCHAR(255),

            PRIMARY KEY (Generic_Name),
            CONSTRAINT FOREIGN KEY (Patient_ID) REFERENCES Patients(Patient_ID) ON DELETE CASCADE ON UPDATE CASCADE,
        );
CREATE TABLE Pharmacy_Staff (
    Staff_Number INT(9),
    Staff_Type INT(20),

    Salary INT(255),
    Address VARCHAR(75),


    PRIMARY KEY (Staff_Number),
    CONSTRAINT FOREIGN KEY (Staff_Number) REFERENCES Employee(Staff_Number) ON DELETE CASCADE ON UPDATE CASCADE
);