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

CREATE TABLE [AKUZ].[T_IHE_CLINICALDOCUMENT_EVENT_FILE]
(
    [FileID] uniqueidentifier NOT NULL,
    [ResponseParent] uniqueidentifier NULL,
    [ClinicalDocumentParent] uniqueidentifier NULL
)
ON [PRIMARY]
GO

ALTER TABLE [AKUZ].[T_IHE_CLINICALDOCUMENT_EVENT_FILE] ADD CONSTRAINT [PK_T_IHE_CLINICALDOCUMENT_EVENT_FILE]
PRIMARY KEY CLUSTERED ([FileID]) ON [PRIMARY]
GO

ALTER TABLE [AKUZ].[T_IHE_CLINICALDOCUMENT_EVENT_FILE]ADD [ResponseParent_UQ] AS (CASE WHEN [ResponseParent] IS NOT NULL THEN NULL ELSE [FileID] END)
GO
CREATE UNIQUE NONCLUSTERED INDEX [UQ_T_IHE_CLINICALDOCUMENT_EVENT_FILE_ResponseParent] ON [AKUZ].[T_IHE_CLINICALDOCUMENT_EVENT_FILE] ([ResponseParent] ASC, [ResponseParent_UQ] ASC) ON [PRIMARY]
GO

ALTER TABLE [AKUZ].[T_IHE_CLINICALDOCUMENT_EVENT_FILE]ADD [ClinicalDocumentParent_UQ] AS (CASE WHEN [ClinicalDocumentParent] IS NOT NULL THEN NULL ELSE [FileID] END)
GO
CREATE UNIQUE NONCLUSTERED INDEX [UQ_T_IHE_CLINICALDOCUMENT_EVENT_FILE_ClinicalDocumentParent] ON [AKUZ].[T_IHE_CLINICALDOCUMENT_EVENT_FILE] ([ClinicalDocumentParent] ASC, [ClinicalDocumentParent_UQ] ASC) ON [PRIMARY]
GO

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])
GO

ALTER TABLE [AKUZ].[T_IHE_CLINICALDOCUMENT_EVENT_FILE] ADD CONSTRAINT [FK_T_IHE_CLINICALDOCUMENT_EVENT_T_IHE_CLINICALDOCUMENT_EVENT_FILE_ResponseParent]
FOREIGN KEY([ResponseParent]) REFERENCES [AKUZ].[T_IHE_CLINICALDOCUMENT_EVENT] ([ClinicalDocumentEventID])
GO

ALTER TABLE [AKUZ].[T_IHE_CLINICALDOCUMENT_EVENT_FILE] ADD CONSTRAINT [FK_T_IHE_CLINICALDOCUMENT_EVENT_T_IHE_CLINICALDOCUMENT_EVENT_FILE_ClinicalDocumentParent]
FOREIGN KEY([ClinicalDocumentParent]) REFERENCES [AKUZ].[T_IHE_CLINICALDOCUMENT_EVENT] ([ClinicalDocumentEventID])
GO

--- ѕеренос в новую таблицу

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
GO


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
GO

--- ƒроп старых колонок

DROP INDEX [UQ_T_IHE_CLINICALDOCUMENT_EVENT_ClinicalDocumentNew] ON [AKUZ].[T_IHE_CLINICALDOCUMENT_EVENT]
GO

ALTER TABLE [AKUZ].[T_IHE_CLINICALDOCUMENT_EVENT] DROP COLUMN [ClinicalDocumentNew_UQ]
GO

DROP INDEX [UQ_T_IHE_CLINICALDOCUMENT_EVENT_ResponseNew] ON [AKUZ].[T_IHE_CLINICALDOCUMENT_EVENT]
GO

ALTER TABLE [AKUZ].[T_IHE_CLINICALDOCUMENT_EVENT] DROP COLUMN [ResponseNew_UQ]
GO
