CREATE SCHEMA patient;

/******************** Add Table: insurance ************************/
CREATE TABLE patient.insurance
    (
    id SERIAL			PRIMARY KEY,
    acronym				VARCHAR ,
    name				VARCHAR,
    ucm_affiliated		BOOLEAN DEFAULT 'false',
    is_primary			BOOLEAN DEFAULT 'false',
    is_third_party		BOOLEAN DEFAULT 'false',
    is_private			BOOLEAN DEFAULT 'false',
    majoration			FLOAT 	DEFAULT 0,
    disbanded			BOOLEAN DEFAULT 'false',
    hl7_acronym			VARCHAR
    );

ALTER TABLE patient.insurance ADD CONSTRAINT acronym_ukey UNIQUE (acronym);
ALTER TABLE patient.insurance ADD CONSTRAINT hl7_acronym_ukey UNIQUE (hl7_acronym);
ALTER TABLE patient.insurance ADD CONSTRAINT insurance_check_default CHECK 
(
    (id <> 0 AND acronym <> '-')
  OR   (id             = 0
    AND acronym        = '-'
    AND ucm_affiliated = FALSE
    AND ucm_affiliated IS NOT NULL
    AND is_primary     = TRUE
    AND is_primary IS NOT NULL
    AND is_third_party = TRUE
    AND is_third_party IS NOT NULL
    AND is_private     = FALSE
    AND is_private IS NOT NULL
    AND majoration     = 1
    AND majoration IS NOT NULL
    AND disbanded      = FALSE
    AND disbanded IS NOT NULL)
);
/******************** Add Table: insurance plan *****************/

CREATE TABLE patient.insurance_plan
    (
    id  SERIAL 		PRIMARY KEY,
    name          	VARCHAR,
    insurance_id	INTEGER
    );

ALTER TABLE patient.insurance_plan ADD CONSTRAINT fk_insurance_plan_insurance_id
    FOREIGN KEY (insurance_id) REFERENCES patient.insurance (id) ON DELETE NO ACTION;

/******************** Add Table: patient.patient  ************************/
CREATE TABLE patient.patient
	(
 	id						SERIAL PRIMARY KEY,
 	social_security_number 	VARCHAR,
    first_name				VARCHAR,
    sur_name				VARCHAR,
    maiden_name				VARCHAR,
    title					VARCHAR,
    gender					VARCHAR,
    nationality				VARCHAR,
    language				VARCHAR,
    birth_date				DATE ,
    marital_status			VARCHAR,
    email					VARCHAR,
    children_number			INTEGER ,
    id_luxembourg			VARCHAR ,
    id_european				VARCHAR,
    id_future_national		VARCHAR,
    id_ris					VARCHAR,
    creation_date			TIMESTAMP ,
    doctor_id				INTEGER ,
    modified_by				INTEGER ,
    last_modification		TIMESTAMP ,
    lock					INTEGER ,
    insurance_id			INTEGER ,
    complementary_id		INTEGER ,
    policy_number			VARCHAR,
    spouse_id				INTEGER,
    spouse_name 			VARCHAR,
    children_name			TEXT,
    parent_name				TEXT,
    birth_locality			VARCHAR,
    status					INTEGER,
    job 					VARCHAR,
    other_physicians		TEXT,
    mr_declaration_no		VARCHAR DEFAULT NULL,
    search					TEXT,
    complementary_insurance_number VARCHAR DEFAULT NULL,
    storage					TEXT,
    remarks					TEXT
    );

ALTER TABLE patient.patient ADD CONSTRAINT fk_patient_insurance_id
    FOREIGN KEY (insurance_id) REFERENCES patient.insurance (id) ON DELETE NO ACTION;

ALTER TABLE patient.patient ADD CONSTRAINT fk_patient_complementary_id
    FOREIGN KEY (complementary_id) REFERENCES patient.insurance_plan (id) ON DELETE NO ACTION;

ALTER TABLE patient.patient ADD CONSTRAINT fk_patient_spouse_id
    FOREIGN KEY (spouse_id) REFERENCES patient.patient (id) ON DELETE NO ACTION;

CREATE INDEX idx_patient_ssn ON patient.patient (social_security_number);

CREATE INDEX idx_patient_risid ON patient.patient (id_ris);

/******************** Add Table: patient.patient_foto ************************/
CREATE TABLE patient.patient_foto
(
    id SERIAL PRIMARY KEY,
    patient_id INTEGER ,
    data BYTEA NULL
);
ALTER TABLE patient.patient_foto ADD CONSTRAINT fk_patient_foto_patient_id
    FOREIGN KEY (patient_id) REFERENCES patient.patient (id) ON DELETE CASCADE;

/******************** Add Table: patient.patient_phone ************************/
CREATE TABLE patient.patient_phone
(
    id SERIAL 						PRIMARY KEY,
	number							VARCHAR,
	type							VARCHAR,
    patient_id 						INTEGER
);
ALTER TABLE patient.patient_phone ADD CONSTRAINT fk_patient_phone_patient_id
    FOREIGN KEY (patient_id) REFERENCES patient.patient (id) ON DELETE CASCADE;


/******************** Add Table: hospital_department ************************/
CREATE TABLE patient.hospital_department
(
   id SERIAL PRIMARY KEY,
   name varchar(128)
);


/******************** Add Table: hospitalisation_class ************************/
CREATE TABLE patient.hospitalisation_class
(
   id SERIAL PRIMARY KEY,
   acronym varchar(3),
   name varchar(128)
);


/******************** Add Table: hospitalisation_status ************************/
CREATE TABLE patient.hospitalisation_status
(
   id SERIAL PRIMARY KEY,
   name varchar(128)
);


/******************** Add Table: hospitalisation ************************/
CREATE TABLE patient.hospitalisation
(
   id SERIAL PRIMARY KEY,
   pas_id varchar(30),
   patient_id int4 ,
   isaccident bool ,
   accident_nr varchar(50),
   accident_date timestamp
);

ALTER TABLE patient.hospitalisation ADD CONSTRAINT fk_hospitalisation_patient_id
    FOREIGN KEY (patient_id) REFERENCES patient.patient(id) ON DELETE CASCADE;

CREATE INDEX idx_hospitalisation_pasid ON patient.hospitalisation (pas_id);

 /******************** Add Table: hospitalisation_period ************************/
CREATE TABLE patient.hospitalisation_period
(
   id SERIAL PRIMARY KEY,
   description text ,
   hospitalisation_id int4 ,
   hospitalisation_status_id int4 ,
   hospitalisation_class_id int4 ,
   end_date timestamp ,
   start_date timestamp ,
   hospital_department_id int4 ,
   hospital_bed varchar(128)
);
ALTER TABLE patient.hospitalisation_period ADD CONSTRAINT fk_hospitalisation_period_hospitalisation_status_id
    FOREIGN KEY (hospitalisation_status_id) REFERENCES patient.hospitalisation_status(id);

ALTER TABLE patient.hospitalisation_period ADD CONSTRAINT fk_hospitalisation_period_hospitalisation_class_id
    FOREIGN KEY (hospitalisation_class_id) REFERENCES patient.hospitalisation_class(id);

ALTER TABLE patient.hospitalisation_period ADD CONSTRAINT fk_hospitalisation_period_hospitalisation_id
    FOREIGN KEY (hospitalisation_id) REFERENCES patient.hospitalisation(id) ON DELETE CASCADE;

ALTER TABLE patient.hospitalisation_period ADD CONSTRAINT fk_hospitalisation_period_hospital_department_id
    FOREIGN KEY (hospital_department_id) REFERENCES patient.hospital_department(id);


/******************** Add Table: patient.hospital_prescription  ************************/
CREATE TABLE patient.hospital_prescription
	(
    id 				SERIAL PRIMARY KEY,
    hospperiod_id 	INTEGER,
    order_number 	VARCHAR(40),
    date 			TIMESTAMP
 	);

ALTER TABLE patient.hospital_prescription ADD CONSTRAINT fk_hospital_prescription_hospitalisation_period
    FOREIGN KEY (hospperiod_id) REFERENCES patient.hospitalisation_period (id) ON DELETE CASCADE;

CREATE INDEX idx_hospital_prescription_order_number ON patient.hospital_prescription (order_number);

/******************** Add Table: patient.hospital_prescription_page  ************************/

CREATE TABLE patient.hospital_prescription_page
	(
    id 					SERIAL PRIMARY KEY,
    hospprescription_id INTEGER,
    scan_number			VARCHAR(40),
    page_number			INTEGER,
    content 			BYTEA
	);

ALTER TABLE patient.hospital_prescription_page ADD CONSTRAINT fk_hospital_prescription_page
    FOREIGN KEY (hospprescription_id) REFERENCES patient.hospital_prescription (id) ON DELETE CASCADE;

CREATE INDEX idx_hospital_prescription_page_scan_number ON patient.hospital_prescription_page (scan_number);

/******************** Add Table: patient.report  ************************/

CREATE TABLE patient.hospital_report
	(
    id SERIAL PRIMARY KEY,
    hospprescription_id INTEGER,
    interpreter_id INTEGER,
    date TIMESTAMP,
    data BYTEA
	);

ALTER TABLE patient.hospital_report ADD CONSTRAINT fk_hospital_report_hospital_prescription
    FOREIGN KEY (hospprescription_id) REFERENCES patient.hospital_prescription (id) ON DELETE CASCADE;

/******************** Add Table: patient.passage ************************/
CREATE TABLE patient.passage
(
    id              			SERIAL PRIMARY KEY,
    passage_date    			TIMESTAMP NOT NULL,
    patient_id      			INTEGER NOT NULL,
    visit_id        			INTEGER ,
    hospperiod_id   			INTEGER ,
    accession_number			VARCHAR(16),
    treatmentname   			TEXT NULL,
    physician_id    			INTEGER NOT NULL,
    ordphysician    			VARCHAR(100),
    technicianname  			VARCHAR(100),
    treatucmcode    			VARCHAR(50),
    created_by      			INTEGER,
    rx							BOOLEAN DEFAULT 'true',
    verified					BOOLEAN DEFAULT 'false',
    hospprescription_id 		INTEGER
);

ALTER TABLE patient.passage ADD CONSTRAINT fkpassage_patient
    FOREIGN KEY (patient_id)    REFERENCES patient.patient (id) ON DELETE CASCADE;

ALTER TABLE patient.passage ADD CONSTRAINT fkpassage_hospitalisationperiod
    FOREIGN KEY (hospperiod_id) REFERENCES patient.hospitalisation_period(id) ON DELETE CASCADE;

ALTER TABLE patient.passage ADD CONSTRAINT fk_passage_physician
    FOREIGN KEY (physician_id)  REFERENCES office.physician(id) ON DELETE NO ACTION;

ALTER TABLE patient.passage ADD CONSTRAINT fk_passage_created_by
    FOREIGN KEY (created_by)    REFERENCES usermanagement.gecamed_user(id) ON DELETE NO ACTION;

ALTER TABLE patient.passage ADD CONSTRAINT fk_passage_hospital_prescription
    FOREIGN KEY (hospprescription_id) REFERENCES patient.hospital_prescription (id) ON DELETE SET NULL;

CREATE INDEX idx_passage_accession_number ON patient.passage (accession_number);

/******************** Add Table: patient.patient_address ************************/
CREATE TABLE patient.patient_address
(
    id SERIAL PRIMARY KEY,
    patient_id INTEGER NULL,
    streetname VARCHAR,
    streetnumber VARCHAR,
    zip VARCHAR,
    locality VARCHAR,
    country VARCHAR,
    type VARCHAR,
	date DATE,
	locality_id INTEGER
);
ALTER TABLE patient.patient_address ADD CONSTRAINT fk_file_patient_id
    FOREIGN KEY (patient_id) REFERENCES patient.patient (id) ON DELETE CASCADE;
ALTER TABLE patient.patient_address ADD CONSTRAINT fk_file_locality_id
    FOREIGN KEY (locality_id) REFERENCES address.locality (id) ON DELETE NO ACTION;


/******************** Add Table: patient.patient_contact ************************/
CREATE TABLE patient.patient_contact
(
    id SERIAL PRIMARY KEY,
    name VARCHAR,
    patient_id INTEGER NULL,
    refered_patient_id INTEGER NULL,
    streetname VARCHAR,
    streetnumber VARCHAR,
    zip VARCHAR,
    locality VARCHAR,
    country VARCHAR,
    type VARCHAR,
	date DATE,
	locality_id INTEGER
);
ALTER TABLE patient.patient_contact ADD CONSTRAINT fk_patient_contact_id
    FOREIGN KEY (patient_id) REFERENCES patient.patient (id) ON DELETE CASCADE;
ALTER TABLE patient.patient_contact ADD CONSTRAINT fk_patient_contact_locality_id
    FOREIGN KEY (locality_id) REFERENCES address.locality (id) ON DELETE NO ACTION;


/******************** Add Table: patient.patient_relation ************************/
CREATE TABLE patient.patient_relation
(
	id SERIAL,
	parent_id INTEGER NOT NULL,
	child_id INTEGER NOT NULL
);

ALTER TABLE patient.patient_relation ADD CONSTRAINT fk_patient_relation_parent
	FOREIGN KEY (parent_id) REFERENCES patient.patient (id) ON DELETE CASCADE;
ALTER TABLE patient.patient_relation ADD CONSTRAINT fk_patient_relation_child
	FOREIGN KEY (child_id) REFERENCES patient.patient (id) ON DELETE CASCADE;

ALTER TABLE patient.patient_relation ADD CONSTRAINT pk_patient_relation
	PRIMARY KEY (parent_id, child_id, id);


/******************************************************************************/

/******************** Add Table: patient.patient_memo ************************/
CREATE TABLE patient.patient_memo
	(
	id							SERIAL PRIMARY KEY,
	patient_id	 			INTEGER,
	created 				TIMESTAMP,
	author_id				INTEGER,
	note						TEXT,
	important				BOOLEAN DEFAULT 'false'
	);

ALTER TABLE patient.patient_memo ADD CONSTRAINT fk_patient_memo_patient
      FOREIGN KEY (patient_id) REFERENCES patient.patient(id) ON DELETE CASCADE;

ALTER TABLE patient.patient_memo ADD CONSTRAINT fk_patient_memo_author
      FOREIGN KEY (author_id) REFERENCES usermanagement.gecamed_user(id);


/******************************************************************************/

/******************** Add Table: patient.allergies ************************/

/* Build Table Structure */
CREATE TABLE patient.allergies
(
	id SERIAL PRIMARY KEY,
	patient_id INTEGER NOT NULL,
	allergen_id INTEGER ,
	allergen_name TEXT,
	comment TEXT NULL,
	created TIMESTAMP DEFAULT 'NOW',
	created_by INTEGER,
	deleted_by INTEGER,
	deletion_date TIMESTAMP NULL,
	code VARCHAR
);


/******************** Add Table: patient.antecedents ************************/


/* Build Table Structure */
CREATE TABLE patient.antecedents
(
	id SERIAL PRIMARY KEY,
	patient_id INTEGER NOT NULL,
	shortcut VARCHAR(250) NULL,
	description TEXT NULL,
	created TIMESTAMP DEFAULT 'NOW',
	created_by INTEGER NULL,
	deleted_by INTEGER NULL,
	deleted_date TIMESTAMP NULL,
	locked_by INTEGER NULL
);

ALTER TABLE patient.antecedents ADD CONSTRAINT fk_antecedent_lock
	FOREIGN KEY (locked_by) REFERENCES usermanagement.gecamed_user(id) ON DELETE NO ACTION;



/******************** Add Table: patient.consultations ************************/


/* Build Table Structure */
CREATE TABLE patient.consultations
(
	id SERIAL PRIMARY KEY,
	incident_id INTEGER NOT NULL,
	consultation_date TIMESTAMP NOT NULL DEFAULT 'NOW',
	created_by INTEGER NOT NULL,
	created TIMESTAMP NOT NULL,
	modified TIMESTAMP NULL,
	modified_by INTEGER NULL,
	ananmesis TEXT NULL,
	anamnesis_codes VARCHAR(250) NULL,
	finding TEXT NULL,
	diagnosis TEXT NULL,
	diagnosis_code VARCHAR(250) NULL,
	traitement TEXT NULL
);


/******************** Add Table: patient.incident_relations ************************/


/* Build Table Structure */
CREATE TABLE patient.incident_relations
(
	id SERIAL PRIMARY KEY,
	parent_incident_id INTEGER NOT NULL,
	child_incident_id INTEGER NOT NULL,
	comment TEXT NULL
);



/******************** Add Table: patient.incidents ************************/

/* Build Table Structure */
CREATE TABLE patient.incidents
(
	id SERIAL PRIMARY KEY,
	incident_date TIMESTAMP NOT NULL,
    patient_id INTEGER NOT NULL,
    created TIMESTAMP NOT NULL,
	created_by INTEGER NOT NULL,
	modified TIMESTAMP NULL,
	modified_by INTEGER NULL,
    is_visit BOOL NOT NULL DEFAULT 'FALSE',
    physician_id    INTEGER NULL,
    accident_nr VARCHAR(100) NULL,
	accident_date DATE NULL,
	is_accident BOOL NULL DEFAULT 'FALSE',
	parent_accident_incident_id INTEGER NULL,
	site_id INTEGER NULL
);

ALTER TABLE patient.incidents ADD CONSTRAINT fk_incident_patient
    FOREIGN KEY (patient_id) REFERENCES patient.patient(id) ON DELETE CASCADE;

ALTER TABLE patient.incidents ADD CONSTRAINT fk_incident_physician
    FOREIGN KEY (physician_id) REFERENCES office.physician(id) ON DELETE CASCADE;
    
ALTER TABLE patient.incidents ADD CONSTRAINT fk_incident_site_id
    FOREIGN KEY (site_id) REFERENCES office.site (id) ON DELETE NO ACTION;

/******************** Add Table: patient.incident_entry_types ************************/
CREATE TABLE patient.incident_entry_types (
    id SERIAL PRIMARY KEY,
    name VARCHAR NOT NULL,
    icon VARCHAR,
    mime_type VARCHAR,
    compression VARCHAR,
    deleted BOOLEAN
);


/******************** Add Table: patient.incident_entry ************************/
CREATE TABLE patient.incident_entry (
    id SERIAL PRIMARY KEY,
    entry_type_id INTEGER,
    incident_id INTEGER,
    created TIMESTAMP NULL,
    created_by INTEGER NULL,
    modified TIMESTAMP NULL,
    modified_by INTEGER NULL,
    entry_date TIMESTAMP NULL,
    text_content VARCHAR,
    code VARCHAR,
    weight INTEGER NULL,
    filename VARCHAR(200),
    original_filename VARCHAR(200) NULL,
    filesize BIGINT,
    status VARCHAR,
    cda_unique_id VARCHAR,
    sick_leave_start_date DATE NULL,
	sick_leave_end_date DATE NULL
);

ALTER TABLE patient.incident_entry ADD CONSTRAINT fk_incident_entry_type
    FOREIGN KEY (entry_type_id) REFERENCES patient.incident_entry_types(id) ON DELETE NO ACTION;
    
ALTER TABLE patient.incident_entry ADD CONSTRAINT fk_incident_entry_incident
    FOREIGN KEY (incident_id) REFERENCES patient.incidents(id) ON DELETE CASCADE;
    
ALTER TABLE patient.incident_entry ADD CONSTRAINT fk_incident_entry_user
    FOREIGN KEY (created_by) REFERENCES usermanagement.gecamed_user(id) ON DELETE NO ACTION;

ALTER TABLE patient.incident_entry ADD CONSTRAINT fk_incident_entry_modified
    FOREIGN KEY (modified_by) REFERENCES usermanagement.gecamed_user(id) ON DELETE NO ACTION;

/******************** Add Table: patient.measurement_types ************************/


/* Build Table Structure */
CREATE TABLE patient.measurement_types
(
	id SERIAL PRIMARY KEY,
	name VARCHAR(100) NOT NULL,
	unit VARCHAR(50) NOT NULL,
	numeric_type BOOL NOT NULL DEFAULT 'TRUE',
	is_default BOOL NOT NULL DEFAULT 'FALSE',
	alias VARCHAR(100) NULL
);



/******************** Add Table: patient.measurement_values ************************/

/* Build Table Structure */
CREATE TABLE patient.measurement_values
(
	id SERIAL PRIMARY KEY,
	measurement_types_id INTEGER NOT NULL,
    incident_id INTEGER NULL,
    incident_entry_id INTEGER NULL,
	value_numeric NUMERIC(10, 2) NULL,
	value_string VARCHAR(100) NULL,
	measurement_date TIMESTAMP NOT NULL
);


ALTER TABLE patient.measurement_values ADD CONSTRAINT fk_measurement_v_incident
    FOREIGN KEY (incident_id) REFERENCES patient.incidents(id) ON DELETE CASCADE;

ALTER TABLE patient.measurement_values ADD CONSTRAINT fk_measurement_entry
    FOREIGN KEY (incident_entry_id) REFERENCES patient.incident_entry(id) ON DELETE CASCADE;


/******************** Add Table: patient.patient_datas ************************/

/* Build Table Structure */
CREATE TABLE patient.patient_datas
(
	id SERIAL PRIMARY KEY,
	patient_id INTEGER NOT NULL,
	active_problem TEXT NULL,
	chronical_treatments TEXT NULL
);
/************ Add Foreign Keys to Database ***************/

/************ Foreign Key: fk_allergies_patient ***************/
ALTER TABLE patient.allergies ADD CONSTRAINT fk_allergies_patient
	FOREIGN KEY (patient_id) REFERENCES patient.patient (id) ON DELETE CASCADE;

/************ Foreign Key: fk_antecedents_gecamed_user ***************/
ALTER TABLE patient.antecedents ADD CONSTRAINT fk_antecedents_gecamed_user
	FOREIGN KEY (created_by) REFERENCES usermanagement.gecamed_user (id) ON DELETE NO ACTION;

/************ Foreign Key: fk_antecedents_gecamed_user_delete ***************/
ALTER TABLE patient.antecedents ADD CONSTRAINT fk_antecedents_gecamed_user_delete
	FOREIGN KEY (deleted_by) REFERENCES usermanagement.gecamed_user (id) ON DELETE NO ACTION;

/************ Foreign Key: fk_antecedents_patient ***************/
ALTER TABLE patient.antecedents ADD CONSTRAINT fk_antecedents_patient
	FOREIGN KEY (patient_id) REFERENCES patient.patient (id) ON DELETE CASCADE;

/************ Foreign Key: fk_consultations_visits ***************/
ALTER TABLE patient.consultations ADD CONSTRAINT fk_consultations_visits
	FOREIGN KEY (incident_id) REFERENCES patient.incidents (id) ON DELETE CASCADE;

/************ Foreign Key: fk_visits_gecamed_user_created ***************/
ALTER TABLE patient.consultations ADD CONSTRAINT fk_visits_gecamed_user_created
	FOREIGN KEY (created_by) REFERENCES usermanagement.gecamed_user (id) ON DELETE NO ACTION;

/************ Foreign Key: fk_visits_gecamed_user_modified ***************/
ALTER TABLE patient.consultations ADD CONSTRAINT fk_visits_gecamed_user_modified
	FOREIGN KEY (modified_by) REFERENCES usermanagement.gecamed_user (id) ON DELETE NO ACTION;

/************ Foreign Key: fk_visit_relations_visits_child ***************/
ALTER TABLE patient.incident_relations ADD CONSTRAINT fk_visit_relations_visits_child
	FOREIGN KEY (child_incident_id) REFERENCES patient.incidents (id) ON DELETE NO ACTION;

/************ Foreign Key: fk_visit_relations_visits_parent ***************/
ALTER TABLE patient.incident_relations ADD CONSTRAINT fk_visit_relations_visits_parent
	FOREIGN KEY (parent_incident_id) REFERENCES patient.incidents (id) ON DELETE NO ACTION;

/************ Foreign Key: fk_measurement_values_measurement_types ***************/
ALTER TABLE patient.measurement_values ADD CONSTRAINT fk_measurement_values_measurement_types
	FOREIGN KEY (measurement_types_id) REFERENCES patient.measurement_types (id) ON DELETE NO ACTION;

/************ Foreign Key: fk_patient_datas_patient ***************/
ALTER TABLE patient.patient_datas ADD CONSTRAINT fk_patient_datas_patient
	FOREIGN KEY (patient_id) REFERENCES patient.patient (id) ON DELETE CASCADE;




CREATE TABLE patient.allergens
(
   id serial PRIMARY KEY not null,
   groupid int4 not null,
   code varchar(128),

   name_en varchar(128) not null,
   synonyms_en varchar(1500),
   description_en varchar(2000),
   invasion_en varchar(500),
   trigger_en varchar(500),
   name_de varchar(128),
   synonyms_de varchar(1500),
   description_de varchar(2000),
   invasion_de varchar(500),
   trigger_de varchar(500),
   name_fr varchar(128),
   synonyms_fr varchar(1500),
   description_fr varchar(2000),
   invasion_fr varchar(500),
   trigger_fr varchar(500),
   name_latin varchar(128),
   family_latin varchar(128),
   order_latin varchar(128),
   food_typeid int4,
   exposition_from int2,
   exposition_to int2,
   clinical_relevance int2,
   datasource int4
);

CREATE AGGREGATE concat (
    BASETYPE = text,
    SFUNC = textcat,
    STYPE = text,
    INITCOND = ''
);

CREATE OR REPLACE VIEW patient.patient_stub AS
SELECT patient.id, patient.social_security_number, patient.sur_name, patient.first_name, patient.maiden_name, patient.gender, patient.insurance_id, patient.id_luxembourg, patient.storage, 
	(TRIM (trailing E'\n' FROM 
		(SELECT concat(((((((((address.zip::text || ' '::text) || address.locality::text) || ' '::text) || address.streetnumber::text) || ', '::text) || address.streetname::text) || ' - '::text) || address.country::text) || E'\n'::text) 
		FROM patient.patient sub_p1
		LEFT JOIN patient.patient_address address ON sub_p1.id = address.patient_id 
		WHERE sub_p1.id = patient.id))) as address,
	(TRIM (trailing E'\n' FROM 
		(SELECT concat(((phone.type::text || ': '::text) || phone.number::text) || E'\n'::text)
		FROM patient.patient sub_p2
		LEFT JOIN patient.patient_phone phone ON sub_p2.id = phone.patient_id
		WHERE sub_p2.id = patient.id))) as phone, patient.doctor_id,
	patient.search
FROM patient.patient patient;


CREATE OR REPLACE VIEW patient.patient_incident_statistics AS
SELECT  incident.id AS "id", patient.title AS "patient_title", patient.sur_name  AS "patient_surname", patient.first_name  AS "patient_firstname", patient.social_security_number  AS "patient_ssn", incident.incident_date AS "incident_date", physician.title AS "dr_title", physician.name AS "dr_surname", physician.first_name AS "dr_firstname", physician.ucm_code  AS "dr_ucmcode", site.name  AS "site_name", site.id  AS "site_id"
FROM patient.incidents AS incident LEFT OUTER JOIN office.site AS site ON (incident.site_id=site.id), patient.patient AS patient, office.physician AS physician 
WHERE patient.id=incident.patient_id 
AND physician.id=incident.physician_id
ORDER BY site.id, incident.incident_date;



-- -------------------
--      FUNCTIONS     
-- -------------------


-- create unaccent function
CREATE OR REPLACE FUNCTION patient.unaccent (TEXT)
  RETURNS TEXT AS $$
BEGIN
  IF $1 IS NULL
  THEN
    RETURN '';
  ELSE
    RETURN regexp_replace(
        regexp_replace(
        regexp_replace(
        regexp_replace(
        regexp_replace(
        regexp_replace(
        regexp_replace(
        regexp_replace(
        regexp_replace(
        regexp_replace(
        regexp_replace(
        regexp_replace(
        regexp_replace(
        regexp_replace(
        regexp_replace(
        regexp_replace(
        regexp_replace(
        regexp_replace(
        regexp_replace(
        regexp_replace(
        regexp_replace(
        regexp_replace(
        regexp_replace(
        regexp_replace(
        regexp_replace(
        regexp_replace(
        regexp_replace($1, 
            E'[ÀÁÂÃÄÅĀÆĂ]', 'A', 'g'), 
            E'[àáâãäåæāă]', 'a', 'g'), 
            E'[ÇČĆ]', 'C', 'g'), 
            E'[çčć]', 'c', 'g'), 
            E'[ĐĎ]', 'D', 'g'),
            E'[đ]', 'd', 'g'),
            E'[ÈÉÊËĒ]', 'E', 'g'), 
            E'[èéêëē]', 'e', 'g'), 
            E'[Ğ]', 'G', 'g'),
            E'[ğ]', 'g', 'g'),
            E'[ÌÍÎÏĪ]', 'I', 'g'), 
            E'[ìíîïī]', 'i', 'g'), 
            E'[ÑŇ]', 'N', 'g'), 
            E'[ñň]', 'n', 'g'), 
            E'[ÒÓÔÕÖØŌŒ]', 'O', 'g'), 
            E'[òóôõöøōœ]', 'o', 'g'), 
            E'[Ř]', 'R', 'g'),
            E'[ř]', 'r', 'g'),
            E'[Š]', 'S', 'g'),
            E'[š]', 's', 'g'),
            E'[Ť]', 'T', 'g'),
            E'[ÙÚÛŨÜŪ]', 'U', 'g'), 
            E'[ùúûũüū]', 'u', 'g'), 
            E'[ŸÝ]', 'Y', 'g'), 
            E'[ÿý]', 'y', 'g'),
            E'[ŽŹŻ]', 'Z', 'g'),
            E'[žźż]', 'z', 'g');
  END IF;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- create function to compare text, considering NULL values
CREATE OR REPLACE FUNCTION not_equals (TEXT, TEXT)
  RETURNS BOOLEAN AS $BODY$
BEGIN
  RETURN ($1 IS NULL AND $2 IS NOT NULL)
      OR ($1 IS NOT NULL AND $2 IS NULL)
      OR ($1 <> $2);
END
$BODY$ LANGUAGE plpgsql;

-- create search update trigger function for updates
CREATE OR REPLACE FUNCTION patient.update_search_column_func ()
  RETURNS TRIGGER AS $$
BEGIN
  IF     not_equals(OLD.first_name, NEW.first_name)
      OR not_equals(OLD.sur_name, NEW.sur_name)
      OR not_equals(OLD.maiden_name, NEW.maiden_name)
      OR not_equals(OLD.social_security_number, NEW.social_security_number)
  THEN
    UPDATE patient.patient 
    SET search = ' '
      || UPPER(patient.unaccent(NEW.first_name)) || ' ' 
      || UPPER(patient.unaccent(NEW.sur_name))   || ' ' 
      || UPPER(patient.unaccent(NEW.maiden_name))|| ' ' 
      || UPPER(patient.unaccent(NEW.social_security_number))
    WHERE id = NEW.id;
  END IF;

  RETURN NEW;
END 
$$ LANGUAGE plpgsql;

-- create search update trigger function
CREATE OR REPLACE FUNCTION patient.insert_search_column_func ()
  RETURNS TRIGGER AS $$
BEGIN
  UPDATE patient.patient 
  SET search = ' '
    || UPPER(patient.unaccent(NEW.first_name)) || ' ' 
    || UPPER(patient.unaccent(NEW.sur_name))   || ' ' 
    || UPPER(patient.unaccent(NEW.maiden_name))|| ' ' 
    || UPPER(patient.unaccent(NEW.social_security_number))
  WHERE id = NEW.id;
  
  RETURN NEW;
END
$$ LANGUAGE plpgsql;



-- -----------------
--      TRIGGER
-- -----------------

-- create search update trigger
DROP TRIGGER IF EXISTS update_search_colmn_trig ON patient.patient;
CREATE TRIGGER update_search_colmn_trig 
AFTER UPDATE ON patient.patient
FOR EACH ROW
EXECUTE PROCEDURE patient.update_search_column_func();


-- create search insert trigger
DROP TRIGGER IF EXISTS insert_search_colmn_trig ON patient.patient;
CREATE TRIGGER insert_search_colmn_trig 
AFTER INSERT
ON patient.patient
FOR EACH ROW
EXECUTE PROCEDURE patient.insert_search_column_func();



-- -----------------
--      INDICES     
-- -----------------


CREATE INDEX idx_patient_allergies_patient_id
ON patient.allergies USING btree (patient_id);
CREATE INDEX idx_patient_allergies_allergen_id
ON patient.allergies USING btree (allergen_id);
CREATE INDEX idx_patient_antecedents_patient_id
ON patient.antecedents USING btree (patient_id);
CREATE INDEX idx_patient_antecedents_locked_by
ON patient.antecedents USING btree (locked_by);
CREATE INDEX idx_patient_hospitalisation_pas_id
ON patient.hospitalisation USING btree (pas_id);
CREATE INDEX idx_patient_hospitalisation_patient_id
ON patient.hospitalisation USING btree (patient_id);
CREATE INDEX idx_patient_incident_entry_incident_id
ON patient.incident_entry USING btree (incident_id);
CREATE INDEX idx_patient_incident_relations_parent
ON patient.incident_relations USING btree (parent_incident_id);
CREATE INDEX idx_patient_incident_relations_child
ON patient.incident_relations USING btree (child_incident_id);
CREATE INDEX idx_patient_incidents_patient_id
ON patient.incidents USING btree (patient_id);
CREATE INDEX idx_patient_measurement_values_incident_id
ON patient.measurement_values USING btree (incident_id);
CREATE INDEX idx_patient_hospital_prescription_hospperiod_id
ON patient.hospital_prescription USING btree (hospperiod_id);
CREATE INDEX idx_patient_hospital_prescription_order_number
ON patient.hospital_prescription USING btree (order_number);
CREATE INDEX idx_patient_hospital_prescription_page_hospprescription_id
ON patient.hospital_prescription_page USING btree (hospprescription_id);
CREATE INDEX idx_patient_hospital_report_hospital_report
ON patient.hospital_report USING btree (hospprescription_id);
CREATE INDEX idx_patient_hospitalisation_period_hospitalisation_id
ON patient.hospitalisation_period USING btree (hospitalisation_id);
CREATE INDEX idx_patient_passage_hospperiod_id
ON patient.passage USING btree (hospperiod_id);
CREATE INDEX idx_patient_patient_id_ris
ON patient.patient USING btree (id_ris);
CREATE INDEX idx_patient_patient_social_security_number
ON patient.patient USING btree (social_security_number);
CREATE INDEX idx_patient_patient_first_name
ON patient.patient USING btree (upper(first_name));
CREATE INDEX idx_patient_patient_sur_name
ON patient.patient USING btree (upper(sur_name));
CREATE INDEX idx_patient_patient_maiden_name
ON patient.patient USING btree (upper(maiden_name));
CREATE INDEX idx_patient_patient_foto_patient_id
ON patient.patient_foto USING btree (patient_id);
CREATE INDEX idx_patient_patient_address_patient_id
ON patient.patient_address USING btree (patient_id);
CREATE INDEX idx_patient_patient_phone_patient_id
ON patient.patient_phone USING btree (patient_id);
CREATE INDEX idx_patient_patient_contact_patient_id
ON patient.patient_contact USING btree (patient_id);
CREATE INDEX idx_patient_patient_datas_patient_id
ON patient.patient_datas USING btree (patient_id);
CREATE INDEX idx_patient_patient_memo_patient_id
ON patient.patient_memo USING btree (patient_id);
CREATE INDEX idx_patient_patient_memo_author_id
ON patient.patient_memo USING btree (author_id);
CREATE INDEX idx_patient_patient_relation_parent_id
ON patient.patient_relation USING btree (parent_id);
CREATE INDEX idx_patient_patient_relation_child_id
ON patient.patient_relation USING btree (child_id);
CREATE INDEX idx_patient_patient_search 
ON patient.patient USING btree (search);
