/********************* 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(100)
	);



/******************** 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;



/************ Foreign Key: fk_prsc_additional_substances_prsc_main_drugs **************
ALTER TABLE prescription.cefip_additional_substances ADD CONSTRAINT fk_cefip_additional_substances_cefip_drugs
    FOREIGN KEY (drug_id) REFERENCES prescription.cefip_drugs (id) ON DELETE CASCADE;
    
    */
/************ Foreign Key: fk_prsc_additional_substances_prsc_substances **************
ALTER TABLE prescription.cefip_additional_substances ADD CONSTRAINT fk_cefip_additional_substances_cefip_substances
    FOREIGN KEY (substance_id) REFERENCES prescription.cefip_substances (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;

/* 
ALTER TABLE prescription.cefip_drugs ADD CONSTRAINT fk_cefip_drugs_cefip_manufacturer_license_admin
    FOREIGN KEY (license_administrator_id) REFERENCES prescription.cefip_manufacturer (id) ON DELETE NO ACTION;

ALTER TABLE prescription.cefip_drugs ADD CONSTRAINT fk_cefip_drugs_cefip_manufacturer_market
    FOREIGN KEY (manufacturer_id) REFERENCES prescription.cefip_manufacturer (id) ON DELETE NO ACTION;
*/




INSERT INTO prescription.drug_lists VALUES(1, 'CEFIP List', 'Luxembourg', null);
    
    
    
    
    

    
    
    
    
    
    
    
    
    
    

    

