-- Перенос данных из T_ONK_FIRST_PATIENT_DIAG_NOTIFICATION
INSERT INTO AKUZ.T_METASTASIS_LOCALIZATION_LINK (OnkFirstPatientDiagNotification, EhrRusNk0444, MetastasisLocalizationLinkID)
SELECT MedicalRecordID, MetastasisLocalization, NEWID()
FROM AKUZ.T_ONK_FIRST_PATIENT_DIAG_NOTIFICATION
WHERE MetastasisLocalization IS NOT NULL;

-- Перенос данных из T_ONK_NEGLECTED_PROTOCOL
INSERT INTO AKUZ.T_METASTASIS_LOCALIZATION_LINK (OnkNeglectedProtocol, EhrRusNk0444, MetastasisLocalizationLinkID)
SELECT MedicalRecordID, MetastasisLocalization, NEWID()
FROM AKUZ.T_ONK_NEGLECTED_PROTOCOL
WHERE MetastasisLocalization IS NOT NULL;

-- Перенос данных из T_ONK_MEDICAL_CARD_EXTRACT
INSERT INTO AKUZ.T_METASTASIS_LOCALIZATION_LINK (OnkMedicalCardExtract, EhrRusNk0444, MetastasisLocalizationLinkID)
SELECT OnkMedicalCardExtractId, MetastasisLocalization, NEWID()
FROM AKUZ.T_ONK_MEDICAL_CARD_EXTRACT
WHERE MetastasisLocalization IS NOT NULL;

-- Удаление внешних ключей и колонок
IF EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = 'FK_T_EHR_RUS_NK0444_T_ONK_FIRST_PATIENT_DIAG_NOTIFICATION_MetastasisLocalization')
    ALTER TABLE AKUZ.T_ONK_FIRST_PATIENT_DIAG_NOTIFICATION DROP CONSTRAINT FK_T_EHR_RUS_NK0444_T_ONK_FIRST_PATIENT_DIAG_NOTIFICATION_MetastasisLocalization;

ALTER TABLE AKUZ.T_ONK_FIRST_PATIENT_DIAG_NOTIFICATION DROP COLUMN MetastasisLocalization;


IF EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = 'FK_T_EHR_RUS_NK0444_T_ONK_NEGLECTED_PROTOCOL_MetastasisLocalization')
    ALTER TABLE AKUZ.T_ONK_NEGLECTED_PROTOCOL DROP CONSTRAINT FK_T_EHR_RUS_NK0444_T_ONK_NEGLECTED_PROTOCOL_MetastasisLocalization;

ALTER TABLE AKUZ.T_ONK_NEGLECTED_PROTOCOL DROP COLUMN MetastasisLocalization;


IF EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = 'FK_T_EHR_RUS_NK0444_T_ONK_MEDICAL_CARD_EXTRACT_MetastasisLocalization')
    ALTER TABLE AKUZ.T_ONK_MEDICAL_CARD_EXTRACT DROP CONSTRAINT FK_T_EHR_RUS_NK0444_T_ONK_MEDICAL_CARD_EXTRACT_MetastasisLocalization;

ALTER TABLE AKUZ.T_ONK_MEDICAL_CARD_EXTRACT DROP COLUMN MetastasisLocalization;



-- Перенос данных из T_ONK_CHEMOTHERAPY в T_CHEMO_THERAPY_DRUG_LINK
INSERT INTO AKUZ.T_CHEMO_THERAPY_DRUG_LINK (ChemoTherapyDrugLinkID, EhrRusNk0337, OnkConditionTreat)
SELECT 
    NEWID(), -- Генерация нового GUID
    ChemotherapyDrug, -- Значение EhrRusNk0337 (ID препарата)
    OnkConditionTreatID -- ID из T_ONK_CONDITION_TREAT
FROM AKUZ.T_ONK_CHEMOTHERAPY
WHERE ChemotherapyDrug IS NOT NULL;

-- Удаление внешнего ключа перед удалением колонки
IF EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = 'FK_T_EHR_RUS_NK0337_T_ONK_CHEMOTHERAPY_ChemotherapyDrug')
    ALTER TABLE AKUZ.T_ONK_CHEMOTHERAPY DROP CONSTRAINT FK_T_EHR_RUS_NK0337_T_ONK_CHEMOTHERAPY_ChemotherapyDrug;

-- Удаление колонки ChemotherapyDrug из T_ONK_CHEMOTHERAPY
IF EXISTS (SELECT 1 FROM sys.columns WHERE name = 'ChemotherapyDrug' AND object_id = OBJECT_ID('AKUZ.T_ONK_CHEMOTHERAPY'))
    ALTER TABLE AKUZ.T_ONK_CHEMOTHERAPY DROP COLUMN ChemotherapyDrug;
