CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- Перенос данных из t_onk_first_patient_diag_notification
INSERT INTO akuz.t_metastasis_localization_link (onkfirstpatientdiagnotification, ehrrusnk0444, metastasislocalizationlinkid)
SELECT medicalrecordid, metastasislocalization, gen_random_uuid()
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, gen_random_uuid()
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, gen_random_uuid()
FROM akuz.t_onk_medical_card_extract
WHERE metastasislocalization IS NOT NULL;

-- Удаление внешних ключей (если существуют)
DO $$ 
BEGIN
    IF EXISTS (SELECT 1 FROM information_schema.table_constraints WHERE constraint_name = 'fk_t_ehr_rus_nk0444_t_onk_first_patient_diag_notification_metastasislocalization') THEN
        ALTER TABLE akuz.t_onk_first_patient_diag_notification DROP CONSTRAINT fk_t_ehr_rus_nk0444_t_onk_first_patient_diag_notification_metastasislocalization;
    END IF;
END $$;

DO $$ 
BEGIN
    IF EXISTS (SELECT 1 FROM information_schema.table_constraints WHERE constraint_name = 'fk_t_ehr_rus_nk0444_t_onk_neglected_protocol_metastasislocalization') THEN
        ALTER TABLE akuz.t_onk_neglected_protocol DROP CONSTRAINT fk_t_ehr_rus_nk0444_t_onk_neglected_protocol_metastasislocalization;
    END IF;
END $$;

DO $$ 
BEGIN
    IF EXISTS (SELECT 1 FROM information_schema.table_constraints WHERE constraint_name = 'fk_t_ehr_rus_nk0444_t_onk_medical_card_extract_metastasislocalization') THEN
        ALTER TABLE akuz.t_onk_medical_card_extract DROP CONSTRAINT fk_t_ehr_rus_nk0444_t_onk_medical_card_extract_metastasislocalization;
    END IF;
END $$;

-- Удаление колонок (если существуют)
ALTER TABLE akuz.t_onk_first_patient_diag_notification DROP COLUMN IF EXISTS metastasislocalization;
ALTER TABLE akuz.t_onk_neglected_protocol DROP COLUMN IF EXISTS metastasislocalization;
ALTER TABLE akuz.t_onk_medical_card_extract DROP COLUMN IF EXISTS metastasislocalization;

-- Перенос данных из t_onk_chemotherapy в t_chemo_therapy_drug_link
INSERT INTO akuz.t_chemo_therapy_drug_link (chemotherapydruglinkid, ehrrusnk0337, onkconditiontreat)
SELECT 
    gen_random_uuid(), -- Генерация нового UUID
    chemotherapydrug, -- Значение ehr_rus_nk0337 (ID препарата)
    onkconditiontreatid -- ID из t_onk_condition_treat
FROM akuz.t_onk_chemotherapy
WHERE chemotherapydrug IS NOT NULL;

-- Удаление внешнего ключа перед удалением колонки
ALTER TABLE akuz.t_onk_chemotherapy DROP CONSTRAINT IF EXISTS fk_t_ehr_rus_nk0337_t_onk_chemotherapy_chemotherapydrug;

-- Удаление колонки chemotherapydrug из t_onk_chemotherapy
ALTER TABLE akuz.t_onk_chemotherapy DROP COLUMN IF EXISTS chemotherapydrug;
