DO $$
DECLARE
    EntityID INT;
    EhrRusRefBooksID_int INT;
    EhrRusRefBooksID INT;
    EhrRusIDMax INT;
BEGIN
    SELECT e.EntityID INTO EntityID 
    FROM VCLib.T_ENTITIES e 
    WHERE e.EntName = 'EHR_RUS_NK0467' AND e."schema" = 'EHR'
    LIMIT 1;

    CALL sp_generateidforentity('EHR.EHR_RUS_REF_BOOKS', 'PrimaryKey', 1, EhrRusRefBooksID_int);

    INSERT INTO EHR.T_EHR_RUS_REF_BOOKS (EhrRusRefBooksID, Deleted, Code, "name", CurrVersion, UpdateDate, Entities, OID)
    SELECT 
        EhrRusRefBooksID_int, 
        false,
        'NK0467', 
        'РР. Клинические группы больных злокачественными новообразованиями', 
        '1.1', 
        '2026-03-27 04:09:13', 
        EntityID, 
        '1.2.643.5.1.13.13.99.2.146'
    WHERE NOT EXISTS (
        SELECT 1 
        FROM EHR.T_EHR_RUS_REF_BOOKS er 
        WHERE er.Entities = EntityID AND er.Deleted = 0 AND er.CurrVersion = '1.1'
    );

    SELECT er.EhrRusRefBooksID INTO EhrRusRefBooksID
    FROM EHR.T_EHR_RUS_REF_BOOKS er
    WHERE er.Entities = EntityID AND er.Deleted = 0 AND er.CurrVersion = '1.1'
    LIMIT 1;

    CALL sp_generateidforentity('EHR.EHR_RUS_NK0467', 'PrimaryKey', 6, EhrRusIDMax);

    CREATE TEMP TABLE NK0467 AS (
        SELECT EhrRusRefBooksID as EhrRusRefBooks
            , 0 + EhrRusIDMax as EhrRusID
            , 7 as IdEHR
            , 'Iб' as NameEHR
        UNION ALL
        SELECT EhrRusRefBooksID, 1 + EhrRusIDMax, 6, 'Ia'
        UNION ALL
        SELECT EhrRusRefBooksID, 2 + EhrRusIDMax, 5, 'IV'
        UNION ALL
        SELECT EhrRusRefBooksID, 3 + EhrRusIDMax, 4, 'III'
        UNION ALL
        SELECT EhrRusRefBooksID, 4 + EhrRusIDMax, 3, 'II'
        UNION ALL
        SELECT EhrRusRefBooksID, 5 + EhrRusIDMax, 2, 'IIа'
    );

    INSERT INTO EHR.T_EHR_RUS_NK0467 (EhrRusRefBooks, EhrRusID, IdEHR, NameEHR)
    SELECT EhrRusRefBooks
        , EhrRusID
        , IdEHR
        , NameEHR
    FROM NK0467 t
    WHERE NOT EXISTS (
        SELECT 1 
        FROM EHR.T_EHR_RUS_NK0467 er 
        WHERE er.IdEHR = t.IdEHR AND er.EhrRusRefBooks = EhrRusRefBooksID
    );

    DROP TABLE NK0467;

    UPDATE AKUZ.T_ONK_TREATMENT_CASE otc
    SET ONK_GROUP = ern.EhrRusID
    FROM EHR.T_EHR_RUS_NK0467 ern
    WHERE ern.NameEHR = CASE
                          WHEN otc.ONK_GROUP = 11 THEN 'Ia'
                          WHEN otc.ONK_GROUP = 12 THEN 'Iб'
                          WHEN otc.ONK_GROUP = 20 THEN 'II'
                          WHEN otc.ONK_GROUP = 30 THEN 'III'
                          WHEN otc.ONK_GROUP = 40 THEN 'IV'
                        END;

END $$;