-- Active: 1697130764491@@127.0.0.1@3306@hospital_dbms

CREATE TABLE
    physician (
        employeeid INT PRIMARY KEY,
        name VARCHAR(50),
        position VARCHAR(50),
        ssn VARCHAR(50)
    );

CREATE TABLE
    department (
        departmentid INT PRIMARY KEY,
        name VARCHAR(50),
        head INT,
        FOREIGN KEY (head) REFERENCES physician(employeeid)
    );

CREATE TABLE
    affiliated_with (
        physician INT,
        department INT,
        PRIMARY KEY (physician, department),
        FOREIGN KEY (physician) REFERENCES physician(employeeid),
        FOREIGN KEY (department) REFERENCES department(departmentid)
    );

CREATE TABLE
    treatment (
        code INT PRIMARY KEY,
        name VARCHAR(50),
        cost DECIMAL(10, 2)
    );

CREATE TABLE
    trained_in (
        physician INT,
        treatment INT,
        certificationdate DATE,
        certificationexpires DATE,
        PRIMARY KEY (physician, treatment),
        FOREIGN KEY (physician) REFERENCES physician(employeeid),
        FOREIGN KEY (treatment) REFERENCES treatment(code)
    );

CREATE TABLE
    patient (
        ssn VARCHAR(50) PRIMARY KEY,
        name VARCHAR(50),
        address VARCHAR(50),
        phone VARCHAR(50),
        insuranceid VARCHAR(50),
        pcp INT,
        FOREIGN KEY (pcp) REFERENCES physician(employeeid)
    );

CREATE TABLE
    nurse (
        employeeid INT PRIMARY KEY,
        name VARCHAR(50),
        position VARCHAR(50)
    );

CREATE TABLE
    appointment (
        patient VARCHAR(50),
        physician INT,
        date DATE,
        time TIME,
        PRIMARY KEY(patient, physician, date, time),
        FOREIGN KEY(patient) REFERENCES patient(ssn),
        FOREIGN KEY(physician) REFERENCES physician(employeeid)
    );

CREATE TABLE medication (code INT PRIMARY KEY, name VARCHAR(50));

CREATE TABLE
    prescribes (
        physician INT,
        patient VARCHAR(50),
        medication INT,
        dosage DECIMAL(10, 2),
        startdate DATE,
        enddate DATE,
        PRIMARY KEY(patient, medication),
        FOREIGN KEY(patient) REFERENCES patient(ssn),
        FOREIGN KEY(medication) REFERENCES medication(code),
        FOREIGN KEY(physician) REFERENCES physician(employeeid)
    );