CREATE OR REPLACE FUNCTION akuz.f_diagnostics_dictionary_after_delete()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN

    UPDATE akuz.t_diagnostics_dictionary t
    SET leftindex = leftindex -
        2 * (
            SELECT COUNT(*)
            FROM OLD_TABLE d
            WHERE t.leftindex > d.leftindex
        )
    WHERE EXISTS (
        SELECT 1 FROM OLD_TABLE d
        WHERE t.leftindex > d.leftindex
    );

    UPDATE akuz.t_diagnostics_dictionary t
    SET rightindex = rightindex -
        2 * (
            SELECT COUNT(*)
            FROM OLD_TABLE d
            WHERE t.rightindex > d.rightindex
        )
    WHERE EXISTS (
        SELECT 1 FROM OLD_TABLE d
        WHERE t.rightindex > d.leftindex
    );

    RETURN NULL;
END;
$$;

CREATE OR REPLACE TRIGGER diagnostics_dictionary_after_delete
AFTER DELETE ON akuz.t_diagnostics_dictionary
REFERENCING OLD TABLE AS old_table
FOR EACH STATEMENT
EXECUTE FUNCTION akuz.f_diagnostics_dictionary_after_delete();



CREATE OR REPLACE FUNCTION akuz.f_diagnostics_dictionary_after_insert()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
    parent_left  int;
    parent_level int;
BEGIN

    LOCK TABLE akuz.t_diagnostics_dictionary IN EXCLUSIVE MODE;

    IF NEW.parentid IS NOT NULL THEN
        SELECT leftindex, treelevel
        INTO parent_left, parent_level
        FROM akuz.t_diagnostics_dictionary
        WHERE diagnosticsdictionaryid = NEW.parentid
        FOR UPDATE;

        UPDATE akuz.t_diagnostics_dictionary
        SET rightindex = rightindex + 2
        WHERE rightindex > parent_left;

        UPDATE akuz.t_diagnostics_dictionary
        SET leftindex = leftindex + 2
        WHERE leftindex > parent_left;

        NEW.leftindex  := parent_left + 1;
        NEW.rightindex := parent_left + 2;
        NEW.treelevel  := parent_level + 1;

    ELSE
        SELECT COALESCE(MAX(rightindex),0)+1 INTO parent_left
        FROM akuz.t_diagnostics_dictionary;

        NEW.leftindex := parent_left;
        NEW.rightindex := parent_left+1;
        NEW.treelevel := 0;
    END IF;

    RETURN NEW;
END;
$$;

CREATE OR REPLACE TRIGGER diagnostics_dictionary_after_insert
BEFORE INSERT ON akuz.t_diagnostics_dictionary
FOR EACH ROW
EXECUTE FUNCTION akuz.f_diagnostics_dictionary_after_insert();

CREATE OR REPLACE FUNCTION akuz.f_diagnostics_dictionary_change_parent()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
    spread int;
    newleft int;
    newright int;
    newlevel int;
BEGIN

IF NEW.parentid IS DISTINCT FROM OLD.parentid THEN

    -- помечаем поддерево отрицательным
    UPDATE akuz.t_diagnostics_dictionary
    SET leftindex = -leftindex,
        rightindex = -rightindex
    WHERE leftindex >= OLD.leftindex
      AND rightindex <= OLD.rightindex;

    spread := OLD.rightindex - OLD.leftindex + 1;

    -- перенос в корень
    IF NEW.parentid IS NULL THEN

        UPDATE akuz.t_diagnostics_dictionary
        SET leftindex = leftindex + spread
        WHERE leftindex < OLD.leftindex AND leftindex > 0;

        UPDATE akuz.t_diagnostics_dictionary
        SET rightindex = rightindex + spread
        WHERE rightindex < OLD.leftindex AND rightindex > 0;

        UPDATE akuz.t_diagnostics_dictionary
        SET leftindex = 1 - (OLD.leftindex + leftindex),
            rightindex = 1 - (OLD.leftindex + rightindex),
            treelevel = treelevel - OLD.treelevel
        WHERE leftindex <= -OLD.leftindex
          AND rightindex >= -OLD.rightindex;

    ELSE

        SELECT leftindex, rightindex, treelevel
        INTO newleft, newright, newlevel
        FROM akuz.t_diagnostics_dictionary
        WHERE diagnosticsdictionaryid = NEW.parentid;

        IF OLD.leftindex < newleft THEN
            UPDATE akuz.t_diagnostics_dictionary
            SET leftindex = leftindex - spread
            WHERE leftindex > OLD.leftindex AND leftindex <= newleft;


            UPDATE akuz.t_diagnostics_dictionary
            SET rightindex = rightindex - spread
            WHERE rightindex > OLD.leftindex AND rightindex <= newleft;
        ELSE
            UPDATE akuz.t_diagnostics_dictionary
            SET leftindex = leftindex + spread
            WHERE leftindex > newleft AND leftindex < OLD.leftindex;

            UPDATE akuz.t_diagnostics_dictionary
            SET rightindex = rightindex + spread
            WHERE rightindex > newleft AND rightindex < OLD.leftindex;
        END IF;

        UPDATE akuz.t_diagnostics_dictionary
        SET leftindex = newleft - (OLD.leftindex + leftindex) + 1,
            rightindex = newleft - (OLD.leftindex + rightindex) + 1,
            treelevel = newlevel + (treelevel - OLD.treelevel) + 1
        WHERE leftindex <= -OLD.leftindex
          AND rightindex >= -OLD.rightindex;

    END IF;

END IF;

RETURN NULL;
END;
$$;