-- Create new table
CREATE TABLE [AKUZ].[T_METASTASIS_LOCALIZATION_LINK]
(
    [MetastasisLocalizationLinkID] uniqueidentifier NOT NULL,
    [EhrRusNk0444] int NOT NULL,
    [OnkDiagnosis] uniqueidentifier NULL,
    [OnkRegistryCardTalon] uniqueidentifier NULL
)
ON [PRIMARY]
GO

ALTER TABLE [AKUZ].[T_METASTASIS_LOCALIZATION_LINK] ADD CONSTRAINT [PK_T_METASTASIS_LOCALIZATION_LINK]
PRIMARY KEY CLUSTERED ([MetastasisLocalizationLinkID]) ON [PRIMARY]
GO

ALTER TABLE [AKUZ].[T_METASTASIS_LOCALIZATION_LINK] ADD CONSTRAINT [FK_T_EHR_RUS_NK0444_T_METASTASIS_LOCALIZATION_LINK_EhrRusNk0444]
FOREIGN KEY([EhrRusNk0444]) REFERENCES [EHR].[T_EHR_RUS_NK0444] ([EhrRusID])
GO

ALTER TABLE [AKUZ].[T_METASTASIS_LOCALIZATION_LINK] ADD CONSTRAINT [FK_T_ONK_DIAGNOSIS_T_METASTASIS_LOCALIZATION_LINK_OnkDiagnosis]
FOREIGN KEY([OnkDiagnosis]) REFERENCES [AKUZ].[T_ONK_DIAGNOSIS] ([OnkDiagnosisID])
GO

ALTER TABLE [AKUZ].[T_METASTASIS_LOCALIZATION_LINK] ADD CONSTRAINT [FK_T_ONK_REGISTRY_CARD_TALON_T_METASTASIS_LOCALIZATION_LINK_OnkRegistryCardTalon]
FOREIGN KEY([OnkRegistryCardTalon]) REFERENCES [AKUZ].[T_ONK_REGISTRY_CARD_TALON] ([MedicalRecordID])
GO


-- Transfering data

INSERT INTO AKUZ.T_METASTASIS_LOCALIZATION_LINK (OnkDiagnosis, EhrRusNk0444, MetastasisLocalizationLinkID)
SELECT OnkDiagnosisId, MetastasisLocalization, newid()
FROM AKUZ.T_ONK_DIAGNOSIS
WHERE MetastasisLocalization IS NOT NULL;


-- Drop old column
ALTER TABLE [AKUZ].[T_ONK_DIAGNOSIS] DROP CONSTRAINT [FK_T_EHR_RUS_NK0444_T_ONK_DIAGNOSIS_MetastasisLocalization]
GO
ALTER TABLE [AKUZ].[T_ONK_DIAGNOSIS] DROP COLUMN [MetastasisLocalization]
GO

-- Alter diagnosis

ALTER TABLE [AKUZ].[T_ONK_DIAGNOSIS] ALTER COLUMN [Tumor] int NULL
ALTER TABLE [AKUZ].[T_ONK_DIAGNOSIS] ALTER COLUMN [Nodus] int NULL
ALTER TABLE [AKUZ].[T_ONK_DIAGNOSIS] ALTER COLUMN [Metastasis] int NULL
ALTER TABLE [AKUZ].[T_ONK_DIAGNOSIS] ALTER COLUMN [MorphologyTumorType] int NULL