-- create the eSanté schema
CREATE SCHEMA esante;


-- create the table to store information / properties about the eSanté integration for this GECAMed
-- The information might be valid for 
--     * the whole GECAMed environment (user_id, physician_id, and esante_user_id are null)
--     * A specific GECAMed user (identified by user_id, physician_id and esante_user_id are null)
--     * A specific physician (identified by physician_id, user_id and esante_user_id are null)
--     * A specific esanté platform user, who is identified by a smartcard or login/password (identified esante_user_id, physician_id and by user_id are null)
CREATE TABLE esante.property
(
    id SERIAL 		PRIMARY KEY,
    name      		VARCHAR,
    value     		VARCHAR,
    created   		TIMESTAMP DEFAULT 'now',
    modified  		TIMESTAMP DEFAULT 'now',
    user_id	  		INTEGER,
    physician_id	INTEGER,
    esante_user_id	VARCHAR
);
ALTER TABLE esante.property ADD CONSTRAINT unique_property_name UNIQUE (name, user_id);
ALTER TABLE esante.property ADD CONSTRAINT fk_user_id FOREIGN KEY (user_id) REFERENCES usermanagement.gecamed_user (id) MATCH FULL;


-- create the table to store the logins of the physicians
CREATE TABLE esante.login
(
    id          SERIAL PRIMARY KEY,
    physican_id        INTEGER,
    login              VARCHAR,
    physican_esante_id VARCHAR,
    password           VARCHAR
);
ALTER TABLE esante.login ADD CONSTRAINT fk_login_patient_id
FOREIGN KEY (physican_id) REFERENCES office.physician (id) ON DELETE CASCADE;


CREATE TABLE esante.dsp
(
    id SERIAL    PRIMARY KEY,
    patient_id   INTEGER,
    dsp_oid      VARCHAR,
    match_rate   DECIMAL(5,2),
    givenname    VARCHAR,
    surname      VARCHAR,
    birthname    VARCHAR,
    birthdate    DATE,
    sex          VARCHAR,
    ehr_status   VARCHAR,
    address      VARCHAR
);
ALTER TABLE esante.dsp ADD CONSTRAINT fk_dsp_patient_id
FOREIGN KEY (patient_id) REFERENCES patient.patient (id) ON DELETE CASCADE;
-- add unique constraint on the patient_id column of the table esante.dsp
ALTER TABLE esante.dsp ADD CONSTRAINT patient_id_unique UNIQUE (patient_id);

-- create the table to store the information about the CDA documents
CREATE TABLE esante.cda
(
    id             SERIAL PRIMARY KEY,
    dsp_id                INTEGER,
    incident_entry_id     INTEGER,
    download_time         TIMESTAMP DEFAULT 'now',
    gecamed_origin_ie_id  INTEGER,
    gecamed_filename      VARCHAR,
    read                  BOOLEAN DEFAULT FALSE,
    error_msg             TEXT DEFAULT NULL,
    repository_unique_id  VARCHAR,
    document_unique_id    VARCHAR,
    version               VARCHAR,
    title                 VARCHAR
--    description           VARCHAR,
--    language_code         VARCHAR,
--    author                VARCHAR,
--    content_size          INTEGER,
--    cda_level             INTEGER,
--    class_code            VARCHAR,
--    type_code             VARCHAR,
--    confidentiality_code  VARCHAR,
--    format_code           VARCHAR,
--    practice_setting_code VARCHAR,
--    hcf_type_code         VARCHAR,
--    creation_time         TIMESTAMP NOT NULL,
--    effective_time        TIMESTAMP NULL
);
ALTER TABLE esante.cda ADD CONSTRAINT fk_cda_dsp_id 
FOREIGN KEY (dsp_id) REFERENCES esante.dsp (id) ON DELETE CASCADE;
ALTER TABLE esante.cda ADD CONSTRAINT fk_cda_incident_entry_id
FOREIGN KEY (incident_entry_id) REFERENCES patient.incident_entry (id) ON DELETE SET NULL;


-- create the code and code type tables
CREATE TABLE esante.code_type
(
    id       SERIAL PRIMARY KEY,
    name            VARCHAR,
    uuid            VARCHAR,
    coding_scheme   VARCHAR
);

CREATE TABLE esante.code
(
    id       SERIAL PRIMARY KEY,
    code_type_id    INTEGER,
    code            VARCHAR,
    code_oid        VARCHAR,
    parent_id       INTEGER DEFAULT NULL
);
ALTER TABLE esante.code ADD CONSTRAINT fk_code_code_type_id
FOREIGN KEY (code_type_id) REFERENCES esante.code_type(id) ON DELETE NO ACTION;
ALTER TABLE esante.code ADD CONSTRAINT fk_code_parent_id
FOREIGN KEY (parent_id) REFERENCES esante.code(id) ON DELETE SET NULL;

-- this table holds the display name and description for each code in different languages
CREATE TABLE esante.code_description
(
    id    SERIAL PRIMARY KEY,
    code_id      INTEGER, 
    language     VARCHAR, 
    display_name VARCHAR, 
    description  VARCHAR
);
ALTER TABLE esante.code_description ADD CONSTRAINT fk_code_id
FOREIGN KEY (code_id) REFERENCES esante.code(id) ON DELETE CASCADE;
ALTER TABLE esante.code_description ADD CONSTRAINT unique_code_language
UNIQUE (code_id, language);

-- function that allows to add codes to the different tables. If there is no display name for are certain language, no entry will be added to the table esante.code_description for this code. 
-- A display name in at least one language has to be provided. Descriptions are optional as well as a reference to a parent code.
-- ===========
-- p_code: The code value
-- p_coding_scheme: The oid of the code type this code belongs to
-- p_code_oid: The oid of the code itself
-- p_name_en: The English display name for this code
-- p_name_fr: The French display name for this code
-- p_name_de: The German display name for this code
-- p_description_en: The English description of the meaning of this code
-- p_description_fr: The French description of the meaning of this code
-- p_description_de: The German description of the meaning of this code
-- p_parent_code: The parent code form which this codes depends
CREATE OR REPLACE FUNCTION esante.insert_code_description (p_code character varying, p_coding_scheme character varying, p_code_oid character varying, 
      p_name_en character varying, p_name_fr character varying, p_name_de character varying, p_description_en character varying, 
      p_description_fr character varying, p_description_de character varying, p_parent_code character varying)
  RETURNS void AS
$BODY$
DECLARE
  codeId INTEGER;
  typeId INTEGER;
  parentId INTEGER;
  
BEGIN
  IF (p_name_en IS NULL AND p_name_fr IS NULL AND p_name_de IS NULL) THEN
    RETURN;
  END IF;
  
  typeId := id FROM esante.code_type t
            WHERE t.coding_scheme = p_coding_scheme;
  
  IF (p_parent_code IS NOT NULL) THEN
    parentId := id FROM esante.code c
                WHERE c.code = p_parent_code;
  ELSE
    parentId := NULL;
  END IF;
  
  INSERT INTO esante.code 
  (code_type_id, code, code_oid, parent_id)
  VALUES
  (typeId, p_code, p_code_oid, parentId);
  
  codeId := id FROM esante.code c
		WHERE c.code = p_code
		AND c.code_type_id = typeId;

  IF (p_name_en IS NOT NULL) THEN
    INSERT INTO esante.code_description
    (code_id, language, display_name, description)
    VALUES
    (codeId, 'en', p_name_en, p_description_en);
  END IF;
  
  IF (p_name_fr IS NOT NULL) THEN
    INSERT INTO esante.code_description
    (code_id, language, display_name, description)
    VALUES
    (codeId, 'fr', p_name_fr, p_description_fr);
  END IF;
  
  IF (p_name_de IS NOT NULL) THEN
    INSERT INTO esante.code_description
    (code_id, language, display_name, description)
    VALUES
    (codeId, 'de', p_name_de, p_description_de);
  END IF;
  
  RETURN;
END
$BODY$
  LANGUAGE plpgsql VOLATILE;