-- Создание таблицы

CREATE TABLE AKUZ.T_IHE_CLINICALDOCUMENT_EVENT_FILE
(
    "fileid" uuid NOT NULL,
    "responseparent" uuid NULL,
    "clinicaldocumentparent" uuid NULL
);

ALTER TABLE AKUZ.T_IHE_CLINICALDOCUMENT_EVENT_FILE ADD CONSTRAINT PK_T_IHE_CLINICALDOCUMENT_EVENT_FILE
PRIMARY KEY (FileID);

CREATE UNIQUE INDEX UQ_T_IHE_CLINICALDOCUMENT_EVENT_FILE_ResponseParent ON AKUZ.T_IHE_CLINICALDOCUMENT_EVENT_FILE(ResponseParent);

CREATE UNIQUE INDEX UQ_T_IHE_CLINICALDOCUMENT_EVENT_FILE_ClinicalDocumentParent ON AKUZ.T_IHE_CLINICALDOCUMENT_EVENT_FILE(ClinicalDocumentParent);

ALTER TABLE AKUZ.T_IHE_CLINICALDOCUMENT_EVENT_FILE ADD CONSTRAINT FK_T_FILE_T_IHE_CLINICALDOCUMENT_EVENT_FILE_FileID
FOREIGN KEY("fileid") REFERENCES VCLib.T_FILE (FileID);

ALTER TABLE AKUZ.T_IHE_CLINICALDOCUMENT_EVENT_FILE ADD CONSTRAINT FK_T_IHE_CLINICALDOCUMENT_EVENT_T_IHE_CLINICALDOCUMENT_11BD6C60
FOREIGN KEY("responseparent") REFERENCES AKUZ.T_IHE_CLINICALDOCUMENT_EVENT (ClinicalDocumentEventID);

ALTER TABLE AKUZ.T_IHE_CLINICALDOCUMENT_EVENT_FILE ADD CONSTRAINT FK_T_IHE_CLINICALDOCUMENT_EVENT_T_IHE_CLINICALDOCUMENT_87BEC3F4
FOREIGN KEY("clinicaldocumentparent") REFERENCES AKUZ.T_IHE_CLINICALDOCUMENT_EVENT (ClinicalDocumentEventID);

--- Перенос значений 


INSERT INTO akuz.t_ihe_clinicaldocument_event_file (fileid, clinicaldocumentparent)
SELECT 
  ev.clinicaldocumentnew,
  ev.clinicaldocumenteventid
FROM akuz.t_ihe_clinicaldocument_event ev
WHERE ev.clinicaldocumentnew IS NOT NULL;

INSERT INTO akuz.t_ihe_clinicaldocument_event_file (fileid, responseparent)
SELECT 
  ev.responsenew,
  ev.clinicaldocumenteventid
FROM akuz.t_ihe_clinicaldocument_event ev
WHERE ev.responsenew IS NOT NULL;


-- Удаление старых колонок

drop index AKUZ.UQ_T_IHE_CLINICALDOCUMENT_EVENT_ResponseNew;

ALTER TABLE AKUZ.T_IHE_CLINICALDOCUMENT_EVENT DROP CONSTRAINT if exists UQ_T_IHE_CLINICALDOCUMENT_EVENT_ResponseNew;

ALTER TABLE AKUZ.T_IHE_CLINICALDOCUMENT_EVENT DROP COLUMN if exists ResponseNew_UQ;

drop index AKUZ.UQ_T_IHE_CLINICALDOCUMENT_EVENT_ClinicalDocumentNew;

ALTER TABLE AKUZ.T_IHE_CLINICALDOCUMENT_EVENT DROP CONSTRAINT if exists UQ_T_IHE_CLINICALDOCUMENT_EVENT_ClinicalDocumentNew;

ALTER TABLE AKUZ.T_IHE_CLINICALDOCUMENT_EVENT DROP COLUMN if exists ClinicalDocumentNew_UQ;