DO $$
DECLARE
	EhrEnt int;
	Vers uuid;
BEGIN
	SELECT ee.EntityID INTO EhrEnt FROM VCLib.T_ENTITIES ee WHERE ee.EntName = 'EHR_RUS_M00002' limit 1;
	SELECT reff.EhrRusRefBooksID INTO Vers
-- 	       ,CONCAT(
--                    LPAD(split_part(REPLACE(CurrVersion,',','.'), '.', 1), 2, '0'),'.',
--                    LPAD(split_part(REPLACE(CurrVersion,',','.'), '.', 2), 3, '0'),'.',
--                    LPAD(split_part(REPLACE(CurrVersion,',','.'), '.', 3), 3, '0'))
    FROM EHR.T_EHR_RUS_REF_BOOKS reff
    where currversion is not null
    order by CONCAT(LPAD(split_part(REPLACE(CurrVersion,',','.'), '.', 1), 2, '0'),'.',LPAD(split_part(REPLACE(CurrVersion,',','.'), '.', 2), 3, '0'),'.',LPAD(split_part(REPLACE(CurrVersion,',','.'), '.', 3), 3, '0')) desc
	limit 1;

	CREATE TEMP TABLE ATC AS
    SELECT atc.code AS atc,
    (
        SELECT ehr.EhrRusID
        FROM EHR.T_EHR_RUS ehr
        JOIN EHR.T_EHR_RUS_M00002 a ON a.EhrRusID = ehr.EhrRusID
        WHERE ehr.CodeEHR = atc.Code AND ehr.EhrRusRefBooks = Vers
        LIMIT 1
    ) AS newAtc
    FROM PHARM.T_ATC atc;

END $$;


--T_ATC_LINK_MEDICAMENT
ALTER TABLE AKUZ.T_ATC_LINK_MEDICAMENT DROP CONSTRAINT FK_T_ATC_T_ATC_LINK_MEDICAMENT_Atc;
ALTER TABLE AKUZ.T_ATC_LINK_MEDICAMENT RENAME COLUMN Atc TO AtcOld;
ALTER TABLE AKUZ.T_ATC_LINK_MEDICAMENT ADD Atc uuid NULL;

update AKUZ.T_ATC_LINK_MEDICAMENT trn
set Atc = tt.newAtc
from ATC tt
where trn.Atc is not null and tt.atc = trn.AtcOld;

ALTER TABLE AKUZ.T_ATC_LINK_MEDICAMENT ALTER COLUMN Atc uuid set not null;
ALTER TABLE AKUZ.T_ATC_LINK_MEDICAMENT ADD CONSTRAINT FK_T_EHR_RUS_M00002_T_ATC_LINK_MEDICAMENT_Atc
FOREIGN KEY("atc") REFERENCES EHR.T_EHR_RUS_M00002 (EhrRusID);
ALTER TABLE AKUZ.T_ATC_LINK_MEDICAMENT DROP COLUMN AtcOld;


--T_TREATMENT_DISEASE_LINK_MEDICAMENT
ALTER TABLE AKUZ.T_TREATMENT_DISEASE_LINK_MEDICAMENT DROP CONSTRAINT FK_T_ATC_T_TREATMENT_DISEASE_LINK_MEDICAMENT_Atc;
ALTER TABLE AKUZ.T_TREATMENT_DISEASE_LINK_MEDICAMENT RENAME COLUMN Atc TO AtcOld;
ALTER TABLE AKUZ.T_TREATMENT_DISEASE_LINK_MEDICAMENT ADD Atc uuid NULL;

update AKUZ.T_TREATMENT_DISEASE_LINK_MEDICAMENT trn
set Atc = tt.newAtc
from ATC tt
where trn.Atc is not null and tt.atc = trn.AtcOld;

ALTER TABLE AKUZ.T_TREATMENT_DISEASE_LINK_MEDICAMENT ALTER COLUMN Atc uuid not null;
ALTER TABLE AKUZ.T_TREATMENT_DISEASE_LINK_MEDICAMENT ADD CONSTRAINT FK_T_EHR_RUS_M00002_T_TREATMENT_DISEASE_LINK_MEDICAMENT_Atc
FOREIGN KEY("atc") REFERENCES EHR.T_EHR_RUS_M00002 (EhrRusID);
ALTER TABLE AKUZ.T_TREATMENT_DISEASE_LINK_MEDICAMENT DROP COLUMN AtcOld;


--T_TREATMENT_TEMPLATE_LINK_MEDICAMENT
ALTER TABLE AKUZ.T_TREATMENT_TEMPLATE_LINK_MEDICAMENT DROP CONSTRAINT FK_T_ATC_T_TREATMENT_TEMPLATE_LINK_MEDICAMENT_Atc;
ALTER TABLE AKUZ.T_TREATMENT_TEMPLATE_LINK_MEDICAMENT RENAME COLUMN Atc TO AtcOld;
ALTER TABLE AKUZ.T_TREATMENT_TEMPLATE_LINK_MEDICAMENT ADD Atc uuid NULL;

update AKUZ.T_TREATMENT_TEMPLATE_LINK_MEDICAMENT trn
set Atc = tt.newAtc
from ATC tt
where trn.Atc is not null and tt.atc = trn.AtcOld;

ALTER TABLE AKUZ.T_TREATMENT_TEMPLATE_LINK_MEDICAMENT ADD CONSTRAINT FK_T_EHR_RUS_M00002_T_TREATMENT_TEMPLATE_LINK_MEDICAMENT_Atc
FOREIGN KEY("atc") REFERENCES EHR.T_EHR_RUS_M00002 (EhrRusID);
ALTER TABLE AKUZ.T_TREATMENT_TEMPLATE_LINK_MEDICAMENT DROP COLUMN AtcOld;

drop table ATC;