------------------------- AfterInsert -------------------------
DROP TRIGGER IF EXISTS MKB_AfterInsert ON APS.T_MKB;

CREATE OR REPLACE FUNCTION APS.MKB_AfterInsert_Func()
    RETURNS TRIGGER
    LANGUAGE plpgsql
AS
$$
DECLARE
    v_Left   INTEGER;
    v_Level  INTEGER;
    v_Parent uuid;
    v_Id     uuid;
BEGIN
    v_Id := NEW.MkbID;
    v_Parent = NEW.ParentID;

    IF v_ID IS NOT NULL
    THEN
        IF v_Parent IS NOT NULL THEN
            SELECT
                LeftIndex,
                TreeLevel
            FROM APS.T_MKB
            WHERE
                MkbId = v_Parent;

            UPDATE APS.T_MKB
            SET
                LeftIndex = LeftIndex + 2
            WHERE
                LeftIndex > v_Left;

            UPDATE APS.T_MKB
            SET
                RightIndex = RightIndex + 2
            WHERE
                RightIndex > v_Left;

            UPDATE APS.T_MKB
            SET
                LeftIndex  = v_Left + 1,
                RightIndex = v_left + 2,
                TreeLevel  = v_Level + 1
            WHERE
                MkbID = v_Id;

        ELSE

            UPDATE APS.T_MKB
            SET
                LeftIndex  = LeftIndex + 2,
                RightIndex = RightIndex + 2;

            UPDATE APS.T_MKB
            SET
                LeftIndex  = 1,
                RightIndex = 2,
                TreeLevel  = 0
            WHERE
                MKBiD = v_Id;

        END IF;
    END IF;

    RETURN NEW;
END;
$$;

CREATE OR REPLACE TRIGGER MKB_AfterInsert
    AFTER INSERT
    ON APS.T_MKB
    FOR EACH ROW
EXECUTE FUNCTION APS.MKB_AfterInsert_Func();


------------------------- AfterDelete -------------------------
DROP TRIGGER IF EXISTS MKB_AfterDelete ON APS.T_MKB;


CREATE OR REPLACE FUNCTION APS.MKB_AfterDelete_Func()
    RETURNS TRIGGER
    LANGUAGE  plpgsql
AS
$$
DECLARE

BEGIN
    UPDATE APS.T_MKB t
    SET
        LeftIndex =
            t.LeftIndex - 2 * (SELECT COUNT(*) FROM Old d WHERE t.LeftIndex > d.LeftIndex)
    FROM APS.T_MKB t
    WHERE
        t.LeftIndex > ANY (SELECT LeftIndex FROM old);

    UPDATE APS.T_MKB t
    SET
        RightIndex = t.RightIndex - 2 * (SELECT COUNT(*) FROM old d WHERE t.RightIndex > d.RightIndex)
    FROM APS.T_MKB t
    WHERE
        t.RightIndex > ANY (SELECT LeftIndex FROM OLD);
END;
$$;

CREATE OR REPLACE TRIGGER MKB_AfterDelete
    AFTER DELETE
    ON APS.T_MKB
    FOR EACH ROW
EXECUTE FUNCTION APS.MKB_AfterDelete_Func();


------------------------- AfterDelete -------------------------
CREATE OR REPLACE FUNCTION APS.MKB_ChangeParent_Func()
    RETURNS TRIGGER
    LANGUAGE plpgsql
    AS
$$
DECLARE
    v_Left      INTEGER;
    v_Right     INTEGER;
    v_Level     INTEGER;
    v_Parent    uuid;
    v_Id        uuid;
    v_Spread    INTEGER;
    v_NewParent uuid;
    v_NewLeft   INTEGER;
    v_NewRight  INTEGER;
    v_NewLevel  INTEGER;

BEGIN
    IF new.ParentID IS DISTINCT FROM old.ParentID THEN

        v_Spread = v_Right - v_Left + 1;

        SELECT
            d.MkbId,
            d.ParentID,
            d.LeftIndex,
            d.RightIndex,
            d.TreeLevel,
            i.ParentID,
            t.LeftIndex,
            t.RightIndex,
            t.TreeLevel
        INTO
            v_Id,
            v_Parent,
            v_Left,
            v_Right,
            v_Level,
            v_NewParent,
            v_NewLeft,
            v_NewRight,
            v_NewLevel
        FROM OLD d
        JOIN NEW i ON i.MkbID = d.MkbID
        LEFT JOIN APS.T_MKB t ON t.MkbId = i.ParentID;

        UPDATE APS.T_MKB
        SET
            LeftIndex  = -LeftIndex,
            RightIndex = -RightIndex
        WHERE
              LeftIndex >= v_Left
          AND RightIndex <= v_Right;

        IF (v_NewParent IS NULL AND v_Parent IS NOT NULL) THEN

            UPDATE APS.T_MKB
            SET
                LeftIndex = LeftIndex + v_Spread
            WHERE
                  LeftIndex < v_Left
              AND LeftIndex > 0;

            UPDATE APS.T_MKB
            SET
                RightIndex = RightIndex + v_Spread
            WHERE
                  RightIndex < v_Left
              AND RightIndex > 0;

            UPDATE APS.T_MKB
            SET
                LeftIndex  = 1 - (v_Left + LeftIndex),
                RightIndex = 1 - (v_Left + RightIndex),
                TreeLevel  = TreeLevel - v_Level
            WHERE
                  LeftIndex <= -v_Left
              AND RightIndex >= -v_Right;
        ELSE

            IF (v_Left < v_NewLeft) THEN
                UPDATE APS.T_MKB
                SET
                    LeftIndex = LeftIndex - v_Spread
                WHERE
                      LeftIndex > v_Left
                  AND LeftIndex <= v_NewLeft;

                UPDATE APS.T_MKB
                SET
                    RightIndex = RightIndex - v_Spread
                WHERE
                      RightIndex > v_Left
                  AND RightIndex <= v_NewLeft;

            ELSE

                UPDATE APS.T_MKB
                SET
                    LeftIndex = LeftIndex + v_Spread
                WHERE
                      LeftIndex > v_NewLeft
                  AND LeftIndex < v_Left;

                UPDATE APS.T_MKB
                SET
                    RightIndex = RightIndex + v_Spread
                WHERE
                      RightIndex > v_NewLeft
                  AND RightIndex < v_Left;

            END IF;

            SELECT
                LeftIndex,
                RightIndex
            INTO v_NewLeft, v_NewRight
            FROM APS.T_MKB
            WHERE
                MkbId = v_NewParent;

            UPDATE APS.T_MKB
            SET
                LeftIndex  = v_NewLeft - (v_Left + LeftIndex) + 1,
                RightIndex = v_NewLeft - (v_Left + RightIndex) + 1,
                TreeLevel  = v_NewLevel + (TreeLevel - v_Level) + 1
            WHERE
                  LeftIndex <= -v_Left
              AND RightIndex >= -v_Right;

        END IF;


    END IF;

    RETURN NEW;
END;
$$;

CREATE OR REPLACE TRIGGER MKB_ChangeParent
    AFTER UPDATE
    ON APS.T_MKB
    FOR EACH ROW
EXECUTE FUNCTION APS.MKB_ChangeParent_Func();
