-- Создание новой таблицы
CREATE TABLE akuz.t_metastasis_localization_link (
    metastasis_localization_link_id UUID NOT NULL,
    ehr_rus_nk0444 INT NOT NULL,
    onk_diagnosis UUID NULL,
    onk_registry_card_talon UUID NULL,
    PRIMARY KEY (metastasis_localization_link_id),
    CONSTRAINT fk_ehr_rus_nk0444 FOREIGN KEY (ehr_rus_nk0444) REFERENCES ehr.t_ehr_rus_nk0444 (ehr_rus_id),
    CONSTRAINT fk_onk_diagnosis FOREIGN KEY (onk_diagnosis) REFERENCES akuz.t_onk_diagnosis (onk_diagnosis_id),
    CONSTRAINT fk_onk_registry_card_talon FOREIGN KEY (onk_registry_card_talon) REFERENCES akuz.t_onk_registry_card_talon (medical_record_id)
);

-- Перенос данных из старой таблицы
INSERT INTO akuz.t_metastasis_localization_link (onk_diagnosis, ehr_rus_nk0444, metastasis_localization_link_id)
SELECT onk_diagnosis_id, metastasis_localization, uuid_generate_v4()
FROM akuz.t_onk_diagnosis
WHERE metastasis_localization IS NOT NULL;

-- Удаление старого столбца и ограничения
ALTER TABLE akuz.t_onk_diagnosis DROP CONSTRAINT IF EXISTS fk_t_ehr_rus_nk0444_t_onk_diagnosis_metastasis_localization;
ALTER TABLE akuz.t_onk_diagnosis DROP COLUMN IF EXISTS metastasis_localization;

-- Изменение типа существующих столбцов
ALTER TABLE akuz.t_onk_diagnosis ALTER COLUMN tumor TYPE INT USING tumor::INT;
ALTER TABLE akuz.t_onk_diagnosis ALTER COLUMN nodus TYPE INT USING nodus::INT;
ALTER TABLE akuz.t_onk_diagnosis ALTER COLUMN metastasis TYPE INT USING metastasis::INT;
ALTER TABLE akuz.t_onk_diagnosis ALTER COLUMN morphology_tumor_type TYPE INT USING morphology_tumor_type::INT;
