CREATE SCHEMA prescription;

/* Build Table Structure
CREATE TABLE prescription.posologies
(
	id SERIAL PRIMARY KEY,
	quantity VARCHAR(50) NULL,
	adminstrative_form VARCHAR(50) NULL,
	frequence VARCHAR(50) NULL,
	time VARCHAR(100) NULL,
	periode VARCHAR(50) NULL,
	meal VARCHAR(50) NULL,
	text TEXT NULL,
	prescription_drugs_id INTEGER NULL
);

*/


CREATE TABLE prescription.posologytemplates
(
	id SERIAL PRIMARY KEY,
	physicianid INTEGER NULL,
	text VARCHAR(500) NULL
);


CREATE TABLE prescription.drug_lists (
    id SERIAL PRIMARY KEY,
    name VARCHAR(300),
    origin_country VARCHAR(300),
    list_date TIMESTAMP
);


/******************** Add Table: prescription.prescription_drugs ************************/


/* Build Table Structure */
CREATE TABLE prescription.prescription_drugs
(
	id SERIAL PRIMARY KEY,
	prescriptions_id INTEGER NOT NULL,
	name VARCHAR NOT NULL,
	drug_id INTEGER NULL,
	drug_list_id INTEGER NULL,
    posology TEXT NULL
);

/******************** Add Table: prescription.prescription_types ************************/

/* Build Table Structure */
CREATE TABLE prescription.prescription_types
(
	id SERIAL PRIMARY KEY,
	name VARCHAR NOT NULL
);

/******************** Add Table: prescription.prescriptions ************************/

/* Build Table Structure */
CREATE TABLE prescription.prescriptions
(
	id SERIAL PRIMARY KEY,
	incident_id INTEGER NULL,
	incident_entry_id INTEGER NULL,
	creation_date TIMESTAMP NOT NULL,
	created_by INTEGER NULL,
	physician_id INTEGER NOT NULL,
	prescription_types_id INTEGER NULL,
	prescription_date DATE NULL,
	expiry_date DATE NULL,
    accident_nr VARCHAR NULL,
    accident_date DATE NULL,
	patient_name VARCHAR NULL,
	patient_first_name VARCHAR NULL,
	patient_maiden_name VARCHAR NULL,
	patient_address VARCHAR NULL,
	patient_social_security_number VARCHAR NULL,
	physician_full_name VARCHAR NULL,
	physician_ucm_code VARCHAR NULL,
	physician_speciality TEXT NULL,
	physician_address VARCHAR NULL,
	physician_phone VARCHAR NULL,
	physician_fax VARCHAR NULL,
    physician_gsm VARCHAR NULL,
	text_content TEXT NULL,
    deleted TIMESTAMP NULL,
    deleted_by INTEGER NULL,
    is_chronic BOOLEAN NULL DEFAULT 'false',
    description VARCHAR NULL
);

ALTER TABLE prescription.prescriptions  ADD CONSTRAINT fk_prescritpion_entry
    FOREIGN KEY (incident_entry_id) REFERENCES patient.incident_entry(id) ON DELETE CASCADE;

/* Set Comments */
COMMENT ON COLUMN prescription.prescriptions.prescription_date IS 'date of the prescription itself';

/******************** Add Table: prescription.template_drugs ************************/

/* Build Table Structure */
CREATE TABLE prescription.template_drugs
(
	id SERIAL PRIMARY KEY,
	template_id INTEGER NOT NULL,
	name VARCHAR(250) NULL,
	drug_id INTEGER NULL,
	drug_list_id INTEGER NULL,
	posology TEXT NULL
);


/******************** Add Table: prescription.templates ************************/

/* Build Table Structure */
CREATE TABLE prescription.templates
(
	id SERIAL PRIMARY KEY,
	shortcut VARCHAR(30) NULL,
	description VARCHAR(250) NULL,
	prescription_types_id INTEGER NOT NULL,
	text_content TEXT NULL,
	is_warning BOOL NULL DEFAULT 'FALSE',
	warning_msg TEXT NULL,
	owner_id INTEGER NOT NULL,
	created TIMESTAMP NOT NULL,
	modified TIMESTAMP NULL,
	physician_id INTEGER NULL
);



/****************** Add Table prescription.print_prescription **************************/
CREATE TABLE prescription.print_prescription
(
    id SERIAL PRIMARY KEY,
    prescription_id INTEGER NOT NULL,
    printed_by_id INTEGER NOT NULL,
    print_date TIMESTAMP NOT NULL,
    is_copy BOOL DEFAULT false
);

ALTER TABLE prescription.print_prescription ADD CONSTRAINT fk_print_prescription_prescriptions
    FOREIGN KEY (prescription_id) REFERENCES prescription.prescriptions(id) ON DELETE CASCADE;

ALTER TABLE prescription.print_prescription ADD CONSTRAINT fk_print_prescription_gecameduser
    FOREIGN KEY (printed_by_id) REFERENCES usermanagement.gecamed_user(id) ON DELETE CASCADE;




/************ Foreign Key: fk_prescription_drugs_prescriptions ***************/
ALTER TABLE prescription.prescription_drugs ADD CONSTRAINT fk_prescription_drugs_prescriptions
	FOREIGN KEY (prescriptions_id) REFERENCES prescription.prescriptions (id) ON DELETE CASCADE;

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

/************ Foreign Key: fk_prescription_physician ***************/
ALTER TABLE prescription.prescriptions ADD CONSTRAINT fk_prescription_physician
	FOREIGN KEY (physician_id) REFERENCES office.physician (id) ON DELETE NO ACTION;

/************ Foreign Key: fk_prescription_prescription_types ***************/
ALTER TABLE prescription.prescriptions ADD CONSTRAINT fk_prescription_prescription_types
	FOREIGN KEY (prescription_types_id) REFERENCES prescription.prescription_types (id) ON DELETE NO ACTION;

/************ Foreign Key: fk_prescription_incidents ***************/
ALTER TABLE prescription.prescriptions ADD CONSTRAINT fk_prescription_visits
	FOREIGN KEY (incident_id) REFERENCES patient.incidents (id) ON DELETE SET NULL;

/************ Foreign Key: fk_template_drugs_templates ***************/
ALTER TABLE prescription.template_drugs ADD CONSTRAINT fk_template_drugs_templates
	FOREIGN KEY (template_id) REFERENCES prescription.templates (id) ON DELETE CASCADE;

/************ Foreign Key: fk_template_posologies_template_drugs **************
ALTER TABLE prescription.template_posologies ADD CONSTRAINT fk_template_posologies_template_drugs
	FOREIGN KEY (template_drugs_id) REFERENCES prescription.template_drugs (id) ON DELETE CASCADE;

*/

/************ Foreign Key: fk_templates_gecamed_user_created ***************/
ALTER TABLE prescription.templates ADD CONSTRAINT fk_templates_gecamed_user_created
	FOREIGN KEY (owner_id) REFERENCES usermanagement.gecamed_user (id) ON DELETE NO ACTION;

/************ Foreign Key: fk_templates_prescription_types ***************/
ALTER TABLE prescription.templates ADD CONSTRAINT fk_templates_prescription_types
	FOREIGN KEY (prescription_types_id) REFERENCES prescription.prescription_types (id) ON DELETE NO ACTION;


















/* **********************************************************
    CEFIP CEFIP CEFIP CEFIP CEFIP
************************************************************* */


/********************* Add Table: prescription.act_classes **************************/
CREATE TABLE prescription.cefip_atc_classes (
	id SERIAL PRIMARY KEY,
	code VARCHAR(7) NOT NULL,
	name VARCHAR(50) ,
    parent INTEGER NULL
);

ALTER TABLE prescription.cefip_atc_classes ADD CONSTRAINT fk_cefip_atc_parent
    FOREIGN KEY (parent) REFERENCES prescription.cefip_atc_classes (id) ON DELETE CASCADE;

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

CREATE TABLE prescription.cefip_drugs (
    id  SERIAL PRIMARY KEY,
	NUMNAT      VARCHAR(7),
	DATEHIST    VARCHAR(8),
	DENOM       VARCHAR(50),
	FORMECLAIR  VARCHAR(20),
	DOSAGEQ1    VARCHAR(10),
	DOSAGEU1    VARCHAR(6),
	DOSAGEQP1   VARCHAR(10),
	DOSAGEUP1   VARCHAR(6),
	DOSAGEQ2    VARCHAR(10),
	DOSAGEU2    VARCHAR(6),
	DOSAGEQP2   VARCHAR(10),
	DOSAGEUP2   VARCHAR(6),
	PIECE       VARCHAR(5),
	LARG        VARCHAR(10),
	LONGU       VARCHAR(10),
	POIDS       VARCHAR(10),
	VOLUME      VARCHAR(10),
	CONDITION   VARCHAR(70),
	PRESENT     VARCHAR(30),
	PRESENTA    VARCHAR(20),
	EMBALLAGE   VARCHAR(1),
	VOIEADMIS1  VARCHAR(2),
	VOIEADMIS2  VARCHAR(2),
	VOIEADMIS3  VARCHAR(2),
	VOIEADMIS4  VARCHAR(2),
	CODEATC1    VARCHAR(7),
	CODEATC2    VARCHAR(7),
	CODEATC3    VARCHAR(7),
	CODEATC4    VARCHAR(7),
	CLASSME     VARCHAR(7),
	ATCDEF      VARCHAR(1),
	LIMITENBR   VARCHAR(3),
	LIMITEMJ    VARCHAR(1),
	TDC         VARCHAR(1),
	TDCRECONS   VARCHAR(1),
	DDSTMA      VARCHAR(2),
	DSUNITES    VARCHAR(1),
	RECONSNBR   VARCHAR(2),
	RECONSMJH   VARCHAR(1),
	RENOUV      VARCHAR(1),
	RENOUVMOIS  VARCHAR(2),
	PRESUNIQUE  VARCHAR(3),
	CONDSPEC    VARCHAR(2),
	NMOL        VARCHAR(1),
	CONDDELIV   VARCHAR(2),
	TITULAIRE   VARCHAR(6),
	FABRICANT   VARCHAR(6),
	DISTRIBPHA  VARCHAR(6),
	DISTRIBGRO  VARCHAR(6),
	CODEPAMM    VARCHAR(3),
	IMPORTE     VARCHAR(1),
	PAYS1       VARCHAR(3),
	PAYS2       VARCHAR(3),
	PAYS3       VARCHAR(3),
	PAYS4       VARCHAR(3),
	VIGILAN1    VARCHAR(1),
	VIGILAN2    VARCHAR(1),
	VIGILAN3    VARCHAR(1),
	VIGILAN4    VARCHAR(1),
	PROCEDURE   VARCHAR(1),
	PROCEDDATE  VARCHAR(8),
	CLEANCENR   VARCHAR(7),
	CLENOUVENR  VARCHAR(7),
	DATEDEB     VARCHAR(8),
	CETAT       VARCHAR(2),
	DATEFIN     VARCHAR(8),
	DATEMEM     VARCHAR(8),
	ETATMEM1    VARCHAR(1),
	CETATMEM1   VARCHAR(2),
	ETATMEM2    VARCHAR(1),
	CETATMEM2   VARCHAR(2),
	LUFEXUS     VARCHAR(7),
	LUFPRIX     VARCHAR(7),
	LUFINTERV   VARCHAR(7),
	LUFREFER    VARCHAR(7),
	LUFB1RECOM  VARCHAR(7),
	EUREXUSIN   VARCHAR(9),
	EURPRIX     VARCHAR(9),
	EURINTERV   VARCHAR(9),
	EURREFER    VARCHAR(9),
	EURB1RECOM  VARCHAR(9),
	PAYSREF     VARCHAR(2),
	PAYSORIG    VARCHAR(2),
	TAUX        VARCHAR(3),
	ABATT       VARCHAR(1),
	CATEGORIE   VARCHAR(2),
	DATEPRIX    VARCHAR(8),
	ETATPRIX    VARCHAR(2),
	APCM        VARCHAR(1),
	APCMCOND    VARCHAR(1),
	APCMDETAIL  VARCHAR(2),
	CEFIPNUM    VARCHAR(7),
	CEFIPCBARR  VARCHAR(13),
	CEFIPDENOM  VARCHAR(40),
	CEFIPRESPO  VARCHAR(2),
	CEFPRIXLUF  VARCHAR(7),
	CEFPRIXEUR  VARCHAR(9),
	CEFPHARLUF  VARCHAR(10),
	CEFPHAREUR  VARCHAR(10),
	TVA         VARCHAR(4),
	NBCOMP      VARCHAR(2),
	COMP1       VARCHAR(50),
	CP11        VARCHAR(3),
	CP12        VARCHAR(10),
	CP13        VARCHAR(3),
	CP14        VARCHAR(10),
	CP15        VARCHAR(9),
	COMP2       VARCHAR(50),
	CP21        VARCHAR(3),
	CP22        VARCHAR(10),
	CP23        VARCHAR(3),
	CP24        VARCHAR(10),
	CP25        VARCHAR(9),
	COMP3       VARCHAR(50),
	CP31        VARCHAR(3),
	CP32        VARCHAR(10),
	CP33        VARCHAR(3),
	CP34        VARCHAR(10),
	CP35        VARCHAR(9),
	COMP4       VARCHAR(50),
	CP41        VARCHAR(3),
	CP42        VARCHAR(10),
	CP43        VARCHAR(3),
	CP44        VARCHAR(10),
	CP45        VARCHAR(9),
	COMP5       VARCHAR(50),
	CP51        VARCHAR(3),
	CP52        VARCHAR(10),
	CP53        VARCHAR(3),
	CP54        VARCHAR(10),
	CP55        VARCHAR(9),
	COMP6       VARCHAR(50),
	CP61        VARCHAR(3),
	CP62        VARCHAR(10),
	CP63        VARCHAR(3),
	CP64        VARCHAR(10),
	CP65        VARCHAR(9),
	COMP7       VARCHAR(50),
	CP71        VARCHAR(3),
	CP72        VARCHAR(10),
	CP73        VARCHAR(3),
	CP74        VARCHAR(10),
	CP75        VARCHAR(9),
	COMP8       VARCHAR(50),
	CP81        VARCHAR(3),
	CP82        VARCHAR(10),
	CP83        VARCHAR(3),
	CP84        VARCHAR(10),
	CP85        VARCHAR(9),
	COMP9       VARCHAR(50),
	CP91        VARCHAR(3),
	CP92        VARCHAR(10),
	CP93        VARCHAR(3),
	CP94        VARCHAR(10),
	CP95        VARCHAR(9),
	COMP10      VARCHAR(50),
	CP101       VARCHAR(3),
	CP102       VARCHAR(10),
	CP103       VARCHAR(3),
	CP104       VARCHAR(10),
	CP105       VARCHAR(9),
	DENOMINATI  VARCHAR(512)
	);



/******************** Add Table: prescription.manufacturer ************************/
CREATE TABLE prescription.cefip_manufacturer
(
    id SERIAL PRIMARY KEY,
    num VARCHAR(8) NOT NULL,
    nom VARCHAR(40) NULL,
    ad1 VARCHAR(40) NULL,
    ad2 VARCHAR(40) NULL,
    spec VARCHAR(3) NULL,
    du VARCHAR(8) NULL,
    au VARCHAR(8) NULL
);


/******************* Add Table: prescription.rel_cefip_drug_atc_classes ***/

CREATE TABLE prescription.rel_cefip_drugs_atc_classes(
	atc_id INTEGER NOT NULL,
	cefip_drug_id INTEGER NOT NULL
);

ALTER TABLE prescription.rel_cefip_drugs_atc_classes ADD CONSTRAINT pk_cefip_drug_atc
	PRIMARY KEY (atc_id,cefip_drug_id);

ALTER TABLE prescription.rel_cefip_drugs_atc_classes ADD CONSTRAINT fk_rel_cefip_atc_drug_id
	FOREIGN KEY(cefip_drug_id) REFERENCES prescription.cefip_drugs(id) ON DELETE CASCADE;

ALTER TABLE prescription.rel_cefip_drugs_atc_classes ADD CONSTRAINT fk_rel_cefip_atc_atc_id
	FOREIGN KEY(atc_id) REFERENCES prescription.cefip_atc_classes(id) ON DELETE CASCADE;


-- cefip_drugs


-- ALTER TABLE prescription.cefip_drugs ADD CONSTRAINT fk_cefip_drugs_drug_lists_id
--	FOREIGN KEY (drug_lists_id) REFERENCES prescription.drug_lists(id) ON DELETE CASCADE;


/* prescription revisions */


/* Build Table Structure */
CREATE TABLE prescription.prescription_drug_revisions
(
	id SERIAL PRIMARY KEY,
	prescription_rev_id INTEGER NOT NULL,
	name VARCHAR NOT NULL,
	drug_id INTEGER NULL,
	drug_list_id INTEGER NULL,
    posology TEXT NULL
);


/* Build Table Structure */
CREATE TABLE prescription.prescription_revisions
(
	id SERIAL PRIMARY KEY,
	prescription_id INTEGER NOT NULL,
	revision_id INTEGER NOT NULL,
	incident_id INTEGER NOT NULL,
	incident_entry_id INTEGER NULL,
	creation_date TIMESTAMP NOT NULL,
	created_by INTEGER NOT NULL,
	physician_id INTEGER NOT NULL,
	prescription_types_id INTEGER NULL,
	prescription_date DATE NULL,
	expiry_date DATE NULL,
    accident_nr VARCHAR NULL,
    accident_date DATE NULL,
	patient_name VARCHAR NULL,
	patient_first_name VARCHAR NULL,
	patient_maiden_name VARCHAR NULL,
	patient_address VARCHAR NULL,
	patient_social_security_number VARCHAR NULL,
	physician_full_name VARCHAR NULL,
	physician_ucm_code VARCHAR NULL,
	physician_speciality TEXT NULL,
	physician_address VARCHAR NULL,
	physician_phone VARCHAR NULL,
	physician_fax VARCHAR NULL,
    physician_gsm VARCHAR NULL,
	text_content TEXT NULL,
    deleted TIMESTAMP NULL,
    deleted_by INTEGER NULL,
    is_chronic BOOLEAN NULL DEFAULT 'false'
);

ALTER TABLE prescription.prescription_revisions ADD CONSTRAINT fk_prescription_revision
	FOREIGN KEY (prescription_id) REFERENCES prescription.prescriptions(id) ON DELETE CASCADE;

ALTER TABLE prescription.prescription_drug_revisions ADD CONSTRAINT fk_presc_drug_rev_presc
	FOREIGN KEY (prescription_rev_id) REFERENCES prescription.prescription_revisions (id) ON DELETE CASCADE;

	/************ Foreign Key: fk_prescription_revisions_types ***************/
ALTER TABLE prescription.prescription_revisions ADD CONSTRAINT fk_prescription_rev_prescription_types
	FOREIGN KEY (prescription_types_id) REFERENCES prescription.prescription_types (id) ON DELETE NO ACTION;

/************ Foreign Key: fk_prescription_physician ***************/
ALTER TABLE prescription.prescription_revisions ADD CONSTRAINT fk_prescription_rev_physician
	FOREIGN KEY (physician_id) REFERENCES office.physician (id) ON DELETE NO ACTION;

/************ Foreign Key: fk_prescription_incidents ***************/
ALTER TABLE prescription.prescription_revisions ADD CONSTRAINT fk_prescription_rev_visits
	FOREIGN KEY (incident_id) REFERENCES patient.incidents (id) ON DELETE CASCADE;

/************ Foreign Key: fk_prescription_gecamed_user ***************/
ALTER TABLE prescription.prescription_revisions ADD CONSTRAINT fk_prescription_rev_gecamed_user
	FOREIGN KEY (created_by) REFERENCES usermanagement.gecamed_user (id) ON DELETE NO ACTION;

ALTER TABLE prescription.prescription_revisions  ADD CONSTRAINT fk_prescritpion_revi_entry
    FOREIGN KEY (incident_entry_id) REFERENCES patient.incident_entry(id) ON DELETE CASCADE;


/******************** drop not null on prescription.prescriptions incident_id - 1.1.02_001 ************************/
ALTER TABLE prescription.prescriptions ALTER COLUMN incident_id DROP NOT NULL;

/******************** change constraint on prescription.prescriptions incident_id to not delete - 1.1.02_001 ************************/
ALTER TABLE prescription.prescriptions DROP CONSTRAINT fk_prescription_visits;
ALTER TABLE prescription.prescriptions ADD CONSTRAINT fk_prescription_visits
    FOREIGN KEY (incident_id) REFERENCES patient.incidents (id) ON DELETE SET NULL;

    
    

CREATE INDEX idx_prescription_prescription_drug_revisions_prescription_rev_id
ON prescription.prescription_drug_revisions USING btree (prescription_rev_id);
CREATE INDEX idx_prescription_prescription_drugs_prescriptions_id
ON prescription.prescription_drugs USING btree (prescriptions_id);
CREATE INDEX idx_prescription_print_prescription_prescription_id
ON prescription.print_prescription USING btree (prescription_id);
CREATE INDEX idx_prescription_prescriptions_prescription_types_id
ON prescription.prescriptions USING btree (prescription_types_id);