-- Tables

/***** Object:  Table AKUZ.T_DISPANSERISATION_DYNAMIC_OBSERVATION *****/
CREATE TABLE AKUZ.T_DISPANSERISATION_DYNAMIC_OBSERVATION
(
    "medicalrecordid" uuid NOT NULL,
    "medicalexamination" uuid NULL,
    "dispanserisationinit" uuid NULL,
    "visitlocation" integer NULL,
    "recorddate" timestamp NULL,
    "post" integer NOT NULL,
    "users" uuid NULL,
    "type_" integer NOT NULL
);

-- Alters

ALTER TABLE AKUZ.T_DISPANSERISATION_DYNAMIC_OBSERVATION ADD CONSTRAINT PK_T_DISPANSERISATION_DYNAMIC_OBSERVATION
PRIMARY KEY (MedicalRecordID);

ALTER TABLE AKUZ.T_DISPANSERISATION_DYNAMIC_OBSERVATION ADD CONSTRAINT FK_T_MEDICAL_EXAMINATION_T_DISPANSERISATION_DYNAMIC_OB_C1435D8B
FOREIGN KEY("medicalexamination") REFERENCES AKUZ.T_MEDICAL_EXAMINATION (MedicalRecordID);

ALTER TABLE AKUZ.T_DISPANSERISATION_DYNAMIC_OBSERVATION ADD CONSTRAINT FK_T_DISPANSERISATION_INIT_T_DISPANSERISATION_DYNAMIC__611712F4
FOREIGN KEY("dispanserisationinit") REFERENCES AKUZ.T_DISPANSERISATION_INIT (MedicalRecordID);

ALTER TABLE AKUZ.T_DISPANSERISATION_DYNAMIC_OBSERVATION ADD CONSTRAINT FK_T_POST_T_DISPANSERISATION_DYNAMIC_OBSERVATION_Post
FOREIGN KEY("post") REFERENCES AKUZ.T_POST (ResourceID);

ALTER TABLE AKUZ.T_DISPANSERISATION_DYNAMIC_OBSERVATION ADD CONSTRAINT FK_T_USERS_T_DISPANSERISATION_DYNAMIC_OBSERVATION_Users
FOREIGN KEY("users") REFERENCES AKUZ.T_USERS (UserID);

ALTER TABLE AKUZ.T_DISPANSERISATION_DYNAMIC_OBSERVATION ADD CONSTRAINT FK_T_ENTITIES_T_DISPANSERISATION_DYNAMIC_OBSERVATION_type_
FOREIGN KEY("type_") REFERENCES VCLib.T_ENTITIES (EntityID);


INSERT INTO  AKUZ.T_DISPANSERISATION_DYNAMIC_OBSERVATION
( MedicalRecordID,
    MedicalExamination,
    DispanserisationInit,
    VisitLocation,
    RecordDate,
    Post,
    Users,
    type_)
select mr.MedicalRecordID,
        dp.MedicalExamination,
        dp.DispanserisationInit,
        dp.VisitLocation,
        mr.RecordTime,
        mr.Post,
        mr.Users,
        mr.type_
from AKUZ.T_MEDICAL_RECORD mr
join AKUZ.T_DISPANSERISATION_PRESENCE dp on dp.MedicalRecordID = mr.MedicalRecordID;

DO $$ 
begin

    if(exists(SELECT * FROM pg_catalog.pg_constraint where conname = 'fk_t_medical_examination_t_dispanserisation_presence_medicalexa'))
    then
        ALTER TABLE AKUZ.T_DISPANSERISATION_PRESENCE DROP CONSTRAINT fk_t_medical_examination_t_dispanserisation_presence_medicalexa;
    end if;
    
    if(exists(SELECT * FROM pg_catalog.pg_constraint where conname = 'fk_t_dispanserisation_init_t_dispanserisation_presence_dispanse'))
    then
        ALTER TABLE AKUZ.T_DISPANSERISATION_PRESENCE DROP CONSTRAINT fk_t_dispanserisation_init_t_dispanserisation_presence_dispanse;
    end if;
    
    if(exists(SELECT * FROM pg_catalog.pg_constraint where conname = 'fk_t_medical_record_t_dispanserisation_presence_medicalrecordid'))
    then
        ALTER TABLE AKUZ.T_DISPANSERISATION_PRESENCE DROP CONSTRAINT fk_t_medical_record_t_dispanserisation_presence_medicalrecordid;
    end if;

ALTER TABLE AKUZ.T_DISPANSERISATION_PRESENCE DROP COLUMN MedicalExamination;
ALTER TABLE AKUZ.T_DISPANSERISATION_PRESENCE DROP COLUMN DispanserisationInit;

ALTER TABLE AKUZ.T_DISPANSERISATION_PRESENCE DROP COLUMN VisitLocation;

ALTER TABLE AKUZ.T_DISPANSERISATION_PRESENCE ADD CONSTRAINT FK_T_DISPANSERISATION_DYNAMIC_OBSERVATION_T_DISPANSERI_BB0A0BEA
FOREIGN KEY("medicalrecordid") REFERENCES AKUZ.T_DISPANSERISATION_DYNAMIC_OBSERVATION (MedicalRecordID);

delete
from AKUZ.T_MEDICAL_RECORD mr
    using AKUZ.T_DISPANSERISATION_PRESENCE dp 
where dp.MedicalRecordID = mr.MedicalRecordID;

end;
$$