-- Tables

/***** Object:  Table AKUZ.T_CDA_SECTION_IMM_VAC_CERT_ELEMENT *****/
CREATE TABLE AKUZ.T_CDA_SECTION_IMM_VAC_CERT_ELEMENT
(
    "cdasectionimmvaccertelementid" uuid NOT NULL,
    "ehrrusjopjk8kmtj" integer NULL,
    "vaccinetype" varchar(255) NULL,
    "introductiondate" timestamp NOT NULL,
    "ehrrusejqdjjqaqj" integer NULL,
    "accountingdate" timestamp NULL,
    "introductionresult" varchar(255) NULL,
    "ehrruss0jlhgtlbj" integer NULL,
    "comment" varchar(255) NULL,
    "cdasectionimmvaccert" uuid NULL,
    "ehrrusd4u765t5th" integer NULL,
    "introductionplace" varchar(255) NULL,
    "revaccination" boolean NOT NULL,
    "revaccinationsequencenumber" integer NULL,
    "preparationserial" varchar(255) NULL,
    "ehrruspokazanijakprimenenijuiblp" integer NULL,
    "expirationdate" timestamp NULL,
    "performer" integer NULL,
    "planneddate" timestamp NULL,
    "vaccinereconstitution" varchar(255) NULL
);

-- Alters

ALTER TABLE AKUZ.T_CDA_SECTION_IMM_VAC_CERT_ELEMENT ADD CONSTRAINT PK_T_CDA_SECTION_IMM_VAC_CERT_ELEMENT
PRIMARY KEY (CdaSectionImmVacCertElementID);

ALTER TABLE AKUZ.T_CDA_SECTION_IMM_VAC_CERT_ELEMENT ADD CONSTRAINT FK_T_EHR_RUS_JOPJK8KMTJ_T_CDA_SECTION_IMM_VAC_CERT_ELE_30362CD9
FOREIGN KEY("ehrrusjopjk8kmtj") REFERENCES EHR.T_EHR_RUS_JOPJK8KMTJ (EhrRusID);

ALTER TABLE AKUZ.T_CDA_SECTION_IMM_VAC_CERT_ELEMENT ADD CONSTRAINT FK_T_EHR_RUS_EJQDJJQAQJ_T_CDA_SECTION_IMM_VAC_CERT_ELE_9E555B2C
FOREIGN KEY("ehrrusejqdjjqaqj") REFERENCES EHR.T_EHR_RUS_EJQDJJQAQJ (EhrRusID);

ALTER TABLE AKUZ.T_CDA_SECTION_IMM_VAC_CERT_ELEMENT ADD CONSTRAINT FK_T_EHR_RUS_S0JLHGTLBJ_T_CDA_SECTION_IMM_VAC_CERT_ELE_6D3CF3DD
FOREIGN KEY("ehrruss0jlhgtlbj") REFERENCES EHR.T_EHR_RUS_S0JLHGTLBJ (EhrRusID);

ALTER TABLE AKUZ.T_CDA_SECTION_IMM_VAC_CERT_ELEMENT ADD CONSTRAINT FK_T_CDA_SECTION_IMM_VAC_CERT_T_CDA_SECTION_IMM_VAC_CE_A1E32C9C
FOREIGN KEY("cdasectionimmvaccert") REFERENCES AKUZ.T_CDA_SECTION_IMM_VAC_CERT (CdaSectionImmVacCertID);

ALTER TABLE AKUZ.T_CDA_SECTION_IMM_VAC_CERT_ELEMENT ADD CONSTRAINT FK_T_EHR_RUS_D4U765T5TH_T_CDA_SECTION_IMM_VAC_CERT_ELE_63389E64
FOREIGN KEY("ehrrusd4u765t5th") REFERENCES EHR.T_EHR_RUS_D4U765T5TH (EhrRusID);

ALTER TABLE AKUZ.T_CDA_SECTION_IMM_VAC_CERT_ELEMENT ADD CONSTRAINT FK_T_EHR_RUS_POKAZANIJA_K_PRIMENENIJU_IBLP_T_CDA_SECTI_EC805B41
FOREIGN KEY("ehrruspokazanijakprimenenijuiblp") REFERENCES EHR.T_EHR_RUS_Pokazanija_k_primeneniju_IBLP (EhrRusID);

ALTER TABLE AKUZ.T_CDA_SECTION_IMM_VAC_CERT_ELEMENT ADD CONSTRAINT FK_T_POST_T_CDA_SECTION_IMM_VAC_CERT_ELEMENT_Performer
FOREIGN KEY("performer") REFERENCES AKUZ.T_POST (ResourceID);

-- новая таблица для переноса старой отдельной вакцинации в коллекцию
INSERT INTO akuz.T_CDA_SECTION_IMM_VAC_CERT_ELEMENT
(
    CdaSectionImmVacCertElementID,
    EhrRusJopjk8kmtj, 
    IntroductionDate, 
    EhrRusEjqdjjqaqj, 
    AccountingDate, 
    IntroductionResult, 
    EhrRusS0JLHGTLBJ, 
    Comment, 
	Revaccination,
    CdaSectionImmVacCert
)
SELECT
    gen_random_uuid(),
    cert.EhrRusJopjk8kmtj, 
    cert.IntroductionDate, 
    cert.EhrRusEjqdjjqaqj, 
    cert.AccountingDate, 
    cert.IntroductionResult, 
    cert.EhrRusS0JLHGTLBJ, 
    cert.Comment, 
	false,
    cert.CdaSectionImmVacCertID
FROM akuz.t_CDA_SECTION_IMM_VAC_CERT cert;

ALTER TABLE AKUZ.T_CDA_COMPLICATIONS_VAC_CERT ADD "cdasectionimmvaccertelement" uuid NULL;

UPDATE akuz.t_CDA_COMPLICATIONS_VAC_CERT compl
SET CdaSectionImmVacCertElement = element.CdaSectionImmVacCertElementID
FROM akuz.T_CDA_SECTION_IMM_VAC_CERT_ELEMENT element 
WHERE element.CdaSectionImmVacCert = compl.CdaSectionImmVacCert;

ALTER TABLE AKUZ.T_CDA_COMPLICATIONS_VAC_CERT ALTER COLUMN "cdasectionimmvaccertelement" SET NOT NULL;

ALTER TABLE AKUZ.T_CDA_COMPLICATIONS_VAC_CERT ADD CONSTRAINT FK_T_CDA_SECTION_IMM_VAC_CERT_ELEMENT_T_CDA_COMPLICATI_6938813F
FOREIGN KEY("cdasectionimmvaccertelement") REFERENCES AKUZ.T_CDA_SECTION_IMM_VAC_CERT_ELEMENT (CdaSectionImmVacCertElementID);

-- удаление колонок из таблицы CDA_SECTION_IMM_VAC_CERT, которые были перенесены в CDA_SECTION_IMM_VAC_CERT_ELEMENT
ALTER TABLE AKUZ.T_CDA_SECTION_IMM_VAC_CERT DROP CONSTRAINT FK_T_EHR_RUS_JOPJK8KMTJ_T_CDA_SECTION_IMM_VAC_CERT_Ehr_68F31E90;
ALTER TABLE AKUZ.T_CDA_SECTION_IMM_VAC_CERT DROP CONSTRAINT FK_T_EHR_RUS_EJQDJJQAQJ_T_CDA_SECTION_IMM_VAC_CERT_Ehr_41D41B57;
ALTER TABLE AKUZ.T_CDA_SECTION_IMM_VAC_CERT DROP CONSTRAINT FK_T_EHR_RUS_S0JLHGTLBJ_T_CDA_SECTION_IMM_VAC_CERT_Ehr_34BA179E;
ALTER TABLE AKUZ.T_CDA_SECTION_IMM_VAC_CERT DROP COLUMN "ehrrusjopjk8kmtj";
ALTER TABLE AKUZ.T_CDA_SECTION_IMM_VAC_CERT DROP COLUMN "introductiondate";
ALTER TABLE AKUZ.T_CDA_SECTION_IMM_VAC_CERT DROP COLUMN "ehrrusejqdjjqaqj";
ALTER TABLE AKUZ.T_CDA_SECTION_IMM_VAC_CERT DROP COLUMN "accountingdate";
ALTER TABLE AKUZ.T_CDA_SECTION_IMM_VAC_CERT DROP COLUMN "introductionresult";
ALTER TABLE AKUZ.T_CDA_SECTION_IMM_VAC_CERT DROP COLUMN "ehrruss0jlhgtlbj";
ALTER TABLE AKUZ.T_CDA_SECTION_IMM_VAC_CERT DROP COLUMN "comment";

ALTER TABLE AKUZ.T_CDA_COMPLICATIONS_VAC_CERT DROP CONSTRAINT FK_T_CDA_SECTION_IMM_VAC_CERT_T_CDA_COMPLICATIONS_VAC__9DEBBB7F;
ALTER TABLE AKUZ.T_CDA_COMPLICATIONS_VAC_CERT DROP COLUMN "cdasectionimmvaccert";

