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 $$;

ALTER TABLE PHARM.T_C_TRN DROP CONSTRAINT IF EXISTS FK_T_ATC_T_C_TRN_Atc;
ALTER TABLE PHARM.T_C_TRN RENAME COLUMN Atc TO AtcOld;
ALTER TABLE PHARM.T_C_TRN ADD COLUMN Atc uuid NULL;

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

ALTER TABLE PHARM.T_C_TRN ADD CONSTRAINT FK_T_EHR_RUS_M00002_T_C_TRN_Atc FOREIGN KEY(atc) REFERENCES EHR.T_EHR_RUS_M00002 (EhrRusID);
ALTER TABLE PHARM.T_C_TRN DROP COLUMN IF EXISTS AtcOld;

ALTER TABLE PHARM.T_ATC_LINK_TRN DROP CONSTRAINT IF EXISTS FK_T_ATC_T_ATC_LINK_TRN_Atc;
ALTER TABLE PHARM.T_ATC_LINK_TRN RENAME COLUMN Atc TO AtcOld;
ALTER TABLE PHARM.T_ATC_LINK_TRN ADD COLUMN Atc uuid NULL;

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

ALTER TABLE PHARM.T_ATC_LINK_TRN ALTER COLUMN Atc set NOT NULL;
ALTER TABLE PHARM.T_ATC_LINK_TRN ADD CONSTRAINT FK_T_EHR_RUS_M00002_T_ATC_LINK_TRN_Atc FOREIGN KEY(atc) REFERENCES EHR.T_EHR_RUS_M00002 (EhrRusID);
ALTER TABLE PHARM.T_ATC_LINK_TRN DROP COLUMN IF EXISTS AtcOld;

ALTER TABLE PHARM.T_CONTRAINDICATIONS DROP CONSTRAINT IF EXISTS FK_T_ATC_T_CONTRAINDICATIONS_Atc;
ALTER TABLE PHARM.T_CONTRAINDICATIONS RENAME COLUMN Atc TO AtcOld;
ALTER TABLE PHARM.T_CONTRAINDICATIONS ADD COLUMN Atc uuid NULL;

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

ALTER TABLE PHARM.T_CONTRAINDICATIONS ADD CONSTRAINT FK_T_EHR_RUS_M00002_T_CONTRAINDICATIONS_Atc FOREIGN KEY(atc) REFERENCES EHR.T_EHR_RUS_M00002 (EhrRusID);
ALTER TABLE PHARM.T_CONTRAINDICATIONS DROP COLUMN IF EXISTS AtcOld;

ALTER TABLE PHARM.T_INDICATIONS DROP CONSTRAINT IF EXISTS FK_T_ATC_T_INDICATIONS_Atc;
ALTER TABLE PHARM.T_INDICATIONS RENAME COLUMN Atc TO AtcOld;
ALTER TABLE PHARM.T_INDICATIONS ADD COLUMN Atc uuid NULL;

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

ALTER TABLE PHARM.T_INDICATIONS ADD CONSTRAINT FK_T_EHR_RUS_M00002_T_INDICATIONS_Atc FOREIGN KEY(atc) REFERENCES EHR.T_EHR_RUS_M00002 (EhrRusID);
ALTER TABLE PHARM.T_INDICATIONS DROP COLUMN IF EXISTS AtcOld;

drop table ATC;

drop table PHARM.T_ATC;

CREATE TEMP TABLE ENTITY AS
select EntityID
from VCLib.T_ENTITIES e
where e.EntName in ('atc')
  and e.Schema = 'pharm';

DELETE FROM VCLib.T_USER_COLUMNS col
USING VCLib.T_USER_CONTEXTS ctx
JOIN ENTITY e ON ctx.EntityID = e.EntityID
WHERE col.UserContextID = ctx.UserContextID;

delete from VCLib.T_USER_CONTEXTS ctx
using ENTITY e 
where ctx.EntityID = e.EntityID;

DELETE FROM VCLib.T_DELETED_OBJECT del
USING ENTITY e
WHERE del.Entities = e.EntityID;

DELETE FROM VCLib.T_TEMPLATE_FIELD_VALUE fv
USING VCLib.T_TEMPLATE_FIELD fld
JOIN ENTITY e ON fld.Entity = e.EntityID
WHERE fv.TemplateField = fld.TemplateFieldID;

DELETE FROM VCLib.T_TEMPLATE_FIELD fld
USING ENTITY e
WHERE fld.Entity = e.EntityID;

DELETE FROM VCLib.T_Report rep
USING ENTITY e
WHERE rep.EntityID = e.EntityID;

DELETE FROM VCLib.T_USER_SEARCHES us
USING ENTITY e
WHERE us.EntityID = e.EntityID;

DELETE FROM VCLib.T_ENTITIES_ATTRIBUTES e
USING ENTITY ee
WHERE e.EntityID = ee.EntityID;

DELETE FROM VCLib.T_ENTITIES e
USING ENTITY ee
WHERE e.EntityID = ee.EntityID;