CREATE SCHEMA labo;

CREATE TABLE labo.masterpassword
    (
    id                     SERIAL PRIMARY KEY,
    office_id            INTEGER,
    password            VARCHAR
    );

ALTER TABLE labo.masterpassword ADD CONSTRAINT fk_masterpassword_office
    FOREIGN KEY (office_id) REFERENCES office.office (id) ON DELETE CASCADE;

CREATE TABLE labo.laboratory
    (
    id        SERIAL PRIMARY KEY,
    name      VARCHAR,
    ucm_code  VARCHAR,
    phone     VARCHAR,            
    mobile    VARCHAR,            
    fax       VARCHAR,            
    email     VARCHAR
    );

CREATE TABLE labo.rel_laboratory_contact
    (
    id              SERIAL PRIMARY KEY,
    laboratory_id   INTEGER,
    contact_id      INTEGER
    );

ALTER TABLE labo.rel_laboratory_contact ADD CONSTRAINT fk_rel_laboratory_contact_laboratory
    FOREIGN KEY (laboratory_id) REFERENCES labo.laboratory (id) ON DELETE CASCADE;

ALTER TABLE labo.rel_laboratory_contact ADD CONSTRAINT fk_rel_laboratory_contact_contact
    FOREIGN KEY (contact_id) REFERENCES addressbook.contact (id) ON DELETE CASCADE;

CREATE TABLE labo.labo_address
    (
    id                     SERIAL PRIMARY KEY,
    laboratory_id         INTEGER,
    streetname             VARCHAR,
    streetnumber         VARCHAR,
    pobox                VARCHAR,
    zip                 VARCHAR,
    locality             VARCHAR,
    country             VARCHAR,
     locality_id         INTEGER
    );

ALTER TABLE labo.labo_address ADD CONSTRAINT fk_labo_address_laboratory
    FOREIGN KEY (laboratory_id) REFERENCES labo.laboratory (id) ON DELETE CASCADE;

ALTER TABLE labo.labo_address ADD CONSTRAINT fk_labo_address_locality
    FOREIGN KEY (locality_id) REFERENCES address.locality (id) ON DELETE NO ACTION;

CREATE TABLE labo.labo_certificate
    (
    id                     SERIAL PRIMARY KEY,
    laboratory_id         INTEGER,
     label                VARCHAR,
     expiry                DATE,
     trusted                BOOLEAN DEFAULT 'false',
     pem_data            BYTEA
     );

ALTER TABLE labo.labo_certificate ADD CONSTRAINT fk_labo_certificate_laboratory
    FOREIGN KEY (laboratory_id) REFERENCES labo.laboratory (id) ON DELETE CASCADE;

ALTER TABLE labo.labo_certificate ADD CONSTRAINT un_labo_certificate_label UNIQUE (label);


CREATE TABLE labo.physician_key
    (
    id                     SERIAL PRIMARY KEY,
    office_id            INTEGER NULL,
    physician_id         INTEGER NULL,
     label                VARCHAR,
     expiry                DATE,
     password            VARCHAR,
     pem_private            BYTEA,
     pem_public            BYTEA
     );

ALTER TABLE labo.physician_key ADD CONSTRAINT fk_physician_key_office
    FOREIGN KEY (office_id) REFERENCES office.office (id) ON DELETE CASCADE;

ALTER TABLE labo.physician_key ADD CONSTRAINT fk_physician_key_physician
    FOREIGN KEY (physician_id) REFERENCES office.physician (id) ON DELETE CASCADE;

ALTER TABLE labo.physician_key ADD CONSTRAINT un_physician_key_label UNIQUE (label);

CREATE TABLE labo.connection
    (
    id                     SERIAL PRIMARY KEY,
    office_id            INTEGER NULL,
    physician_id         INTEGER NULL,
    server                VARCHAR,
    port                INTEGER NULL,
    username            VARCHAR,
    password            VARCHAR,
    passive                BOOLEAN DEFAULT 'true',
    synchronize            BOOLEAN DEFAULT 'false',
    enabled                BOOLEAN DEFAULT 'true'
    );

ALTER TABLE labo.connection ADD CONSTRAINT fk_connection_office
    FOREIGN KEY (office_id) REFERENCES office.office (id) ON DELETE CASCADE;

ALTER TABLE labo.connection ADD CONSTRAINT fk_connection_physician
    FOREIGN KEY (physician_id) REFERENCES office.physician (id) ON DELETE CASCADE;


CREATE TABLE labo.result
    (
    id                     SERIAL PRIMARY KEY,
    laboratory_id         INTEGER,
    prescriber_id        INTEGER,
    patient_id            INTEGER,
    incident_entry_id    INTEGER,
    result_number        INTEGER NOT NULL,
    prescription_date    TIMESTAMP,
    specimen_date        TIMESTAMP,
    examination_date    TIMESTAMP,
    downloaded            TIMESTAMP,
    reference            VARCHAR,
    examination_status    VARCHAR,
    result_status        VARCHAR,
    comments            VARCHAR    
    );

ALTER TABLE labo.result ADD CONSTRAINT fk_result_laboratory
    FOREIGN KEY (laboratory_id) REFERENCES labo.laboratory (id) ON DELETE SET NULL;

ALTER TABLE labo.result ADD CONSTRAINT fk_result_prescriber
    FOREIGN KEY (prescriber_id) REFERENCES office.physician (id) ON DELETE CASCADE;

ALTER TABLE labo.result ADD CONSTRAINT fk_result_patient
    FOREIGN KEY (patient_id) REFERENCES patient.patient (id) ON DELETE CASCADE;

CREATE TABLE labo.rel_result_contact
    (
    id                     SERIAL PRIMARY KEY,
    result_id            INTEGER,
    contact_id             INTEGER
    );

ALTER TABLE labo.rel_result_contact ADD CONSTRAINT fk_rel_result_contact_result
    FOREIGN KEY (result_id) REFERENCES labo.result (id) ON DELETE CASCADE;

ALTER TABLE labo.rel_result_contact ADD CONSTRAINT fk_rel_result_contact_contact
    FOREIGN KEY (contact_id) REFERENCES addressbook.contact (id) ON DELETE CASCADE;

CREATE TABLE labo.analysis
    (
    id                    SERIAL PRIMARY KEY,
    result_id            INTEGER,
    title                VARCHAR,
    sub_title            VARCHAR,
    code_internal        VARCHAR,
    code_loinc            VARCHAR,
    label_internal        VARCHAR,
    label_loinc            VARCHAR,
    value                VARCHAR,
    unit                VARCHAR,    
    reference_value     VARCHAR,
    previous_value        VARCHAR,
    previous_date        DATE,
    alarm                CHAR(1),
    comment              VARCHAR
    );

ALTER TABLE labo.analysis ADD CONSTRAINT fk_rel_analysis_result
    FOREIGN KEY (result_id) REFERENCES labo.result (id) ON DELETE CASCADE;


CREATE TABLE labo.antibiogram
    (
    id                    SERIAL PRIMARY KEY,
    result_id            INTEGER,
    title               VARCHAR,
    unit                VARCHAR,
    comments            VARCHAR
    );

ALTER TABLE labo.antibiogram ADD CONSTRAINT fk_antibiogram_result
    FOREIGN KEY (result_id) REFERENCES labo.result (id) ON DELETE CASCADE;   

CREATE TABLE labo.antibiotic
    (
    id                    SERIAL PRIMARY KEY,
    antibiogram_id        INTEGER,
    name               VARCHAR
    );

ALTER TABLE labo.antibiotic ADD CONSTRAINT fk_labo_antibiotic_antibiogram
    FOREIGN KEY (antibiogram_id) REFERENCES labo.antibiogram (id) ON DELETE CASCADE;
    
CREATE TABLE labo.culture
    (
    id                    SERIAL PRIMARY KEY,
    antibiogram_id        INTEGER,
    culture_seq_number    INTEGER,
    name                VARCHAR,
    concentration        VARCHAR,
    unit                VARCHAR
    );    
 
ALTER TABLE labo.culture ADD CONSTRAINT fk_labo_culture_antibiogram
    FOREIGN KEY (antibiogram_id) REFERENCES labo.antibiogram (id) ON DELETE CASCADE;
 
 CREATE TABLE labo.reaction
    (
    id                    SERIAL PRIMARY KEY,
    antibiotic_id        INTEGER,
    culture_seq_number    INTEGER,
    interpretation        VARCHAR,
    MIC                    VARCHAR,
    unit                VARCHAR
    );

ALTER TABLE labo.reaction ADD CONSTRAINT fk_labo_reaction_antibiotic
    FOREIGN KEY (antibiotic_id) REFERENCES labo.antibiotic (id) ON DELETE CASCADE;
    
CREATE VIEW labo.result_stub (id, laboratory_id, laboratory_name, prescriber_id, patient_id, patient_first_name, patient_name, patient_ssn, result_number, prescription_date, specimen_date, examination_date, downloaded, reference, examination_status, result_status ) AS
    SELECT DISTINCT result.id, result.laboratory_id, laboratory.name, result.prescriber_id, result.patient_id, patient.first_name, patient.sur_name, patient.social_security_number, result.result_number, result.prescription_date, result.specimen_date, result.examination_date, result.downloaded, result.reference, result.examination_status, result.result_status
    FROM labo.result, labo.laboratory, patient.patient
    WHERE laboratory.id = result.laboratory_id AND patient.id = result.patient_id;

    
CREATE TABLE labo.certifier
(
id              SERIAL PRIMARY KEY,
label           VARCHAR DEFAULT '',
certificate     VARCHAR DEFAULT NULL,
last_modified	TIMESTAMP DEFAULT 'now'
);
ALTER TABLE labo.certifier ADD CONSTRAINT unique_label UNIQUE (label);


CREATE OR REPLACE FUNCTION labo.insert_delete_certifier_function ()
  RETURNS TRIGGER AS $$
BEGIN
  UPDATE labo.certifier
  SET certificate = NULL 
  WHERE label = 'chain';
  
  RETURN NEW;
END $$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION labo.update_certifier_function ()
  RETURNS TRIGGER AS $$
BEGIN
  IF NEW.label <> 'chain'
      AND not_equals(OLD.certificate, NEW.certificate)
  THEN
    UPDATE labo.certifier
    SET certificate = NULL 
    WHERE label = 'chain';

    UPDATE labo.certifier
    SET last_modified = 'now'::TIMESTAMP
    WHERE id = NEW.id;
  END IF;
  
  RETURN NEW;
END $$ LANGUAGE plpgsql;


DROP TRIGGER IF EXISTS update_chain_after_insert ON labo.certifier;
CREATE TRIGGER update_chain_after_insert
AFTER INSERT ON labo.certifier FOR EACH ROW
EXECUTE PROCEDURE labo.insert_delete_certifier_function();

DROP TRIGGER IF EXISTS update_chain_after_delete ON labo.certifier;
CREATE TRIGGER update_chain_after_delete
AFTER DELETE ON labo.certifier FOR EACH ROW
EXECUTE PROCEDURE labo.insert_delete_certifier_function();

DROP TRIGGER IF EXISTS update_chain_after_update ON labo.certifier;
CREATE TRIGGER update_chain_after_update
AFTER UPDATE ON labo.certifier FOR EACH ROW
EXECUTE PROCEDURE labo.update_certifier_function();


CREATE INDEX idx_labo_reaction_antibiotic_id
ON labo.reaction USING btree (antibiotic_id);
CREATE INDEX idx_labo_rel_result_contact_result_contact_id
ON labo.rel_result_contact USING btree (result_id, contact_id);
CREATE INDEX idx_labo_analysis_result_id
ON labo.analysis USING btree (result_id);
CREATE INDEX idx_labo_antibiogram_result_id
ON labo.antibiogram USING btree (result_id);
CREATE INDEX idx_labo_result_incident_entry_id
ON labo.result USING btree (incident_entry_id);
CREATE INDEX idx_labo_result_prescriber_laboratory_result_number
ON labo.result USING btree (prescriber_id, laboratory_id, result_number);