CREATE TABLE clerical.bookings
(
  pk bigserial NOT NULL,
  fk_patient integer,
  fk_staff integer NOT NULL,
  "begin" timestamp without time zone,
  duration interval,
  notes text,
  fk_staff_booked integer NOT NULL,
  booked_at timestamp without time zone NOT NULL DEFAULT now(),
  fk_clinic integer NOT NULL,
  deleted boolean DEFAULT false,
  fk_lu_appointment_icon integer,
  fk_lu_appointment_status integer DEFAULT 1,
  CONSTRAINT bookings_pkey PRIMARY KEY (pk),
  CONSTRAINT bookings_booked_by_fkey FOREIGN KEY (fk_staff_booked)
      REFERENCES "admin".staff (pk) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT bookings_fk_staff_fkey FOREIGN KEY (fk_staff)
      REFERENCES "admin".staff (pk) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);

COMMENT ON TABLE clerical.bookings IS 'list of all bookings past and future. Note fk_patient can be NULL for non-patient things: meetings, holidays etc';


CREATE TABLE clerical.lu_appointment_icons
(
  pk serial NOT NULL,
  appointment_type text NOT NULL,
  icon_path text NOT NULL,
  CONSTRAINT lu_appointment_icons_pkey PRIMARY KEY (pk)
)
WITH (
  OIDS=FALSE
);

CREATE TABLE clerical.lu_appointment_status
(
  pk serial NOT NULL,
  status text NOT NULL,
  CONSTRAINT lu_appointment_status_pkey PRIMARY KEY (pk)
)
WITH (
  OIDS=FALSE
);

COMMENT ON TABLE clerical.lu_appointment_status IS 'the status of the appointment as it applies to patient: arrived (color code blue), seeing clinician (red), gray (patient gone)';

CREATE OR REPLACE VIEW clerical.vwappointments AS 
 SELECT bookings.pk, bookings.fk_patient, bookings.fk_staff, bookings.begin, bookings.duration, bookings.notes, bookings.fk_staff_booked, bookings.fk_clinic, bookings.fk_lu_appointment_icon, bookings.fk_lu_appointment_status, bookings.deleted, data_patients.fk_person, data_persons.firstname, data_persons.surname, data_persons.birthdate, lu_sex.sex, lu_title.title, ((lu_title.title || ' '::text) || (data_persons.firstname || ' '::text)) || (data_persons.surname || ' '::text) AS wholename
   FROM clerical.bookings
   LEFT JOIN clerical.data_patients ON bookings.fk_patient = data_patients.pk
   LEFT JOIN contacts.data_persons ON data_patients.fk_person = data_persons.pk
   LEFT JOIN contacts.lu_sex ON data_persons.fk_sex = lu_sex.pk
   LEFT JOIN contacts.lu_title ON data_persons.fk_title = lu_title.pk
  ORDER BY bookings.begin;