DROP TABLE IF EXISTS department CASCADE;
DROP TABLE IF EXISTS nurse CASCADE;
DROP TABLE IF EXISTS treatment_plan CASCADE;
DROP TABLE IF EXISTS treatment_event CASCADE;
DROP TABLE IF EXISTS ward CASCADE;
DROP TABLE IF EXISTS hospital_stay CASCADE;
DROP TABLE IF EXISTS medication CASCADE;
DROP TABLE IF EXISTS drug CASCADE;
DROP TABLE IF EXISTS physiotherapy CASCADE;
DROP TABLE IF EXISTS surgery CASCADE;
DROP TABLE IF EXISTS physician CASCADE;
DROP TABLE IF EXISTS patient CASCADE;

CREATE TABLE department (
  dID   int8 NOT NULL,
  label varchar(255) NOT NULL UNIQUE,
  PRIMARY KEY (dID));
CREATE TABLE ward (
  wID          int8 NOT NULL,
  label        varchar(255),
  numberOfBeds int8 NOT NULL,
  PRIMARY KEY (wID));

CREATE TABLE nurse (
  nID           SERIAL PRIMARY KEY,
  preName       varchar(255) NOT NULL,
  lastName      varchar(255) NOT NULL,
  dateOfBirth   date NOT NULL,
  chefNurse int8);

CREATE TABLE physician (
  phID           SERIAL PRIMARY KEY,
  preName       varchar(255) NOT NULL,
  lastName      varchar(255) NOT NULL,
  dateOfBirth   date NOT NULL,
  academicDegree varchar(255) NOT NULL,
  chefPhysician  int8);

CREATE TABLE drug (
  dID                     SERIAL,
  activeIngredient        int8,
  activeIngredientContent varchar(255) NOT NULL,
  label                   varchar(255) NOT NULL,
  manufacturer            varchar(255) NOT NULL,
  PRIMARY KEY (dID)
  );

CREATE TABLE surgery (
  sID                   SERIAL,
  bodyRegion            varchar(255) NOT NULL,
  label                 varchar(255) NOT NULL,
  organ                 varchar(255) NOT NULL,
  surgicalForm          varchar(255) NOT NULL,
  PRIMARY KEY(sID)
  );

CREATE TABLE physiotherapy (
  pID                   SERIAL,
  duration              varchar(255),
  exerciseForm          varchar(255) NOT NULL,
  label                 varchar(255) NOT NULL,
  PRIMARY KEY(pID)
  );

CREATE TABLE medication (
  medID                     SERIAL,
  dosing                    varchar(255) NOT NULL,
  label                     varchar(255),
  drug                      int8 REFERENCES drug(dID) ON DELETE CASCADE,
  PRIMARY KEY(medID)
  );

CREATE TABLE treatment_event (
  teID                  SERIAL,
  treatment_plantpID    int8 NOT NULL,
  eventDate             date NOT NULL,
  eventTime             time NOT NULL,
  medication            int8 REFERENCES medication(medID),
  surgery               int8 REFERENCES surgery(sID),
  physiotherapy         int8 REFERENCES physiotherapy(pID),
  PRIMARY KEY (teID)
  );

CREATE TABLE treatment_plan (
  tpID        SERIAL,
  description varchar(255) NOT NULL,
  name        varchar(255) NOT NULL,
  treatment_event   int8 REFERENCES treatment_event(teID),
  PRIMARY KEY(tpID)
  );

CREATE TABLE patient (
  pID             SERIAL,
  preName           varchar(255) NOT NULL,
  lastName          varchar(255) NOT NULL,
  dateOfBirth       date NOT NULL,
  insuranceNumber   varchar(255) NOT NULL,
  healthInsurance   varchar(255) NOT NULL,
  treatment_plan    int8 REFERENCES treatment_plan(tpID),
  PRIMARY KEY (pID)
  );

CREATE TABLE hospital_stay (
  entryDate     date NOT NULL,
  releaseDate   date NOT NULL,
  pID           int8 REFERENCES patient(pID));

ALTER TABLE patient ADD FOREIGN KEY (treatment_plan) REFERENCES treatment_plan;