
/***** Object:  Table AKUZ.T_PATIENT_MVD_LINK *****/
CREATE TABLE AKUZ.T_PATIENT_MVD_LINK
(
    "patientmvdlinkid" uuid NOT NULL,
    "patient" uuid NOT NULL,
    "mvddocumenttype" integer NULL,
    "mvddocseries" varchar(255) NULL,
    "mvddocnumber" varchar(255) NULL,
    "mvdbegindate" timestamp NULL,
    "mvdenddate" timestamp NULL,
    "mvdpatientrank" uuid NULL,
    "mvdpatientpost" uuid NULL,
    "mvdserviceplace" varchar(255) NULL
);

ALTER TABLE AKUZ.T_PATIENT_MVD_LINK ADD CONSTRAINT PK_T_PATIENT_MVD_LINK
PRIMARY KEY (PatientMvdLinkID);

ALTER TABLE AKUZ.T_PATIENT_MVD_LINK ADD CONSTRAINT FK_T_PATIENT_T_PATIENT_MVD_LINK_Patient
FOREIGN KEY("patient") REFERENCES AKUZ.T_PATIENT (PatientID);

ALTER TABLE AKUZ.T_PATIENT_MVD_LINK ADD CONSTRAINT FK_T_PATIENT_RANK_MVD_T_PATIENT_MVD_LINK_MVDPatientRank
FOREIGN KEY("mvdpatientrank") REFERENCES AKUZ.T_PATIENT_RANK_MVD (PatientRankMvdID);

ALTER TABLE AKUZ.T_PATIENT_MVD_LINK ADD CONSTRAINT FK_T_PATIENT_POST_MVD_T_PATIENT_MVD_LINK_MVDPatientPost
FOREIGN KEY("mvdpatientpost") REFERENCES AKUZ.T_PATIENT_POST_MVD (PatientPostMvdID);

--- //////////создание уникального индекса на сущности PATIENT
do $$ begin
IF EXISTS(select * from pg_indexes where schemaname ilike 'AKUZ' and tablename ilike 'T_PATIENT_MVD_LINK' and indexname ilike 'UQ_T_PATIENT_MVD_LINK_Patient')
THEN
drop index AKUZ.UQ_T_PATIENT_MVD_LINK_Patient;
END IF;
end; $$;
ALTER TABLE AKUZ.T_PATIENT_MVD_LINK DROP CONSTRAINT if exists UQ_T_PATIENT_MVD_LINK_Patient;
ALTER TABLE AKUZ.T_PATIENT_MVD_LINK DROP COLUMN if exists Patient_UQ;

CREATE UNIQUE INDEX UQ_T_PATIENT_MVD_LINK_Patient ON AKUZ.T_PATIENT_MVD_LINK(Patient);
--- //////////

--- заполнение T_PATIENT_MVD_LINK данными из пациента
insert into akuz.T_PATIENT_MVD_LINK (
	PatientMvdLinkID
	,Patient
	,MVDDocumentType
	,MVDDocSeries
	,MVDDocNumber
	,MVDBeginDate
	,MVDEndDate
	,MVDPatientRank
	,MVDPatientPost
	,MVDServicePlace
	)
select uuid_generate_v4()
	,PatientID
	,MVDDocumentType
	,MVDDocSeries
	,MVDDocNumber
	,MVDBeginDate
	,MVDEndDate
	,MVDPatientRank
	,MVDPatientPost
	,MVDServicePlace
from akuz.t_patient
where MVDDocumentType is not null
	or MVDDocSeries is not null
	or MVDDocNumber is not null
	or MVDBeginDate is not null
	or MVDEndDate is not null
	or MVDPatientRank is not null
	or MVDPatientPost is not null
	or MVDServicePlace is not null;

--- удаление колонок из пациента

ALTER TABLE AKUZ.T_PATIENT DROP CONSTRAINT FK_T_PATIENT_RANK_MVD_T_PATIENT_MVDPatientRank;
ALTER TABLE AKUZ.T_PATIENT DROP COLUMN "mvdpatientrank";

ALTER TABLE AKUZ.T_PATIENT DROP CONSTRAINT FK_T_PATIENT_POST_MVD_T_PATIENT_MVDPatientPost;
ALTER TABLE AKUZ.T_PATIENT DROP COLUMN "mvdpatientpost";

ALTER TABLE AKUZ.T_PATIENT DROP COLUMN "mvddocumenttype";

ALTER TABLE AKUZ.T_PATIENT DROP COLUMN "mvddocseries";

ALTER TABLE AKUZ.T_PATIENT DROP COLUMN "mvddocnumber";

ALTER TABLE AKUZ.T_PATIENT DROP COLUMN "mvdbegindate";

ALTER TABLE AKUZ.T_PATIENT DROP COLUMN "mvdenddate";

ALTER TABLE AKUZ.T_PATIENT DROP COLUMN "mvdserviceplace";
