\echo '--- Question 4 ---'
\set ON_ERROR_STOP on

DROP TABLE IF EXISTS beds;
DROP TABLE IF EXISTS consumptions;
DROP TABLE IF EXISTS items;
DROP TABLE IF EXISTS laboratories;
DROP TABLE IF EXISTS staff;
DROP TABLE IF EXISTS technicians;
DROP TABLE IF EXISTS timesheets;
DROP TABLE IF EXISTS treatments;
DROP TABLE IF EXISTS visits;

DROP TABLE IF EXISTS patients;
DROP TABLE IF EXISTS physicians;
DROP TABLE IF EXISTS care_centres;
DROP TABLE IF EXISTS nurses;
DROP TABLE IF EXISTS employees;
DROP TABLE IF EXISTS people;

CREATE TABLE people(
  id bigint primary key,
  name varchar(255),
  address varchar(255),
  birth_date timestamp,
  phone_number varchar(255)
);

CREATE TABLE employees(
  id bigint primary key,
  person_id bigint references people(id),
  "type" varchar(255),
  hired_at timestamp
);

CREATE TABLE nurses(
  id bigint primary key,
  employee_id bigint references employees(id),
  certificate text
);

CREATE TABLE care_centres(
  id bigint primary key,
  name varchar(255),
  location text,
  nurse_in_charge_id bigint references nurses(id)
);

CREATE TABLE timesheets(
  id bigint primary key,
  employee_id bigint references employees(id),
  care_centre_id bigint references care_centres(id),
  hours integer,
  week integer
);

CREATE TABLE physicians(
  id bigint primary key,
  employee_id bigint references employees(id),
  specialty text,
  pager_number varchar(255)
);

CREATE TABLE patients(
  id bigint primary key,
  person_id bigint references people(id),
  physician_id bigint references physicians(id),
  referring_physician_id bigint references physicians(id),
  contacted_at timestamp
);


CREATE TABLE consumptions(
  id bigint primary key,
  physican_id bigint references physicians(id),
  patient_id bigint references patients(id),
  item_id bigint,
  consumed_at timestamp,
  quantity integer,
  total_cost decimal
);

CREATE TABLE items(
  id bigint primary key,
  number integer,
  description text,
  unit_cost decimal
);

CREATE TABLE beds(
  id bigint primary key,
  care_centre_id bigint references care_centres(id),
  bed_number integer,
  room_number integer
);

CREATE TABLE laboratories(
  id bigint primary key,
  location text
);

CREATE TABLE staff(
  id bigint primary key,
  employee_id bigint references employees(id),
  job_class varchar(255)
);

CREATE TABLE technicians(
  id bigint primary key,
  employee_id bigint references employees(id),
  skill varchar(255)
);

CREATE TABLE treatments(
  id bigint primary key,
  physician_id bigint references physicians(id),
  patient_id bigint references patients(id),
  number integer,
  name varchar(255),
  occurred_at timestamp,
  results text
);

CREATE TABLE visits(
  id bigint primary key,
  patient_id bigint references patients(id),
  physician_id bigint references physicians(id),
  comments text,
  scheduled_at timestamp
);