--Добавление записей:
DROP TRIGGER IF EXISTS DIVISIONS_AfterInsert ON VCLib.T_DIVISIONS;

CREATE OR REPLACE FUNCTION VCLib.DIVISIONS_AfterInsert()
RETURNS TRIGGER 
LANGUAGE plpgsql
AS $$
DECLARE
    v_left INT;
    v_level INT;
    v_parent UUID;
    v_id UUID;
    curs CURSOR FOR SELECT DivisionID, ParentID FROM inserted;
BEGIN
    OPEN curs;
    LOOP
        FETCH curs INTO v_id, v_parent;
        EXIT WHEN NOT FOUND;
        IF (v_id IS NOT NULL) THEN
            IF (v_parent IS NOT NULL) THEN
                SELECT LeftIndex, TreeLevel INTO v_left, v_level 
                FROM VCLib.T_DIVISIONS WHERE DivisionID = v_parent;
                
                UPDATE VCLib.T_DIVISIONS SET LeftIndex = LeftIndex + 2 WHERE LeftIndex > v_left;
                UPDATE VCLib.T_DIVISIONS SET RightIndex = RightIndex + 2 WHERE RightIndex > v_left;
                UPDATE VCLib.T_DIVISIONS SET 
                    LeftIndex = v_left + 1, 
                    RightIndex = v_left + 2, 
                    TreeLevel = v_level + 1
                WHERE DivisionID = v_id;
            ELSE
                UPDATE VCLib.T_DIVISIONS SET 
                    LeftIndex = LeftIndex + 2, 
                    RightIndex = RightIndex + 2;
                UPDATE VCLib.T_DIVISIONS SET 
                    LeftIndex = 1, 
                    RightIndex = 2, 
                    TreeLevel = 0 
                WHERE DivisionID = v_id;
            END IF;
        END IF;
    END LOOP;
    CLOSE curs;
    
    RETURN NULL;
END;
$$;

CREATE TRIGGER DIVISIONS_AfterInsert
    AFTER INSERT ON VCLib.T_DIVISIONS
    REFERENCING NEW TABLE AS inserted
    FOR EACH STATEMENT
    EXECUTE FUNCTION VCLib.DIVISIONS_AfterInsert();

-- Удаление записей:
DROP TRIGGER IF EXISTS DIVISIONS_AfterDelete ON VCLib.T_DIVISIONS;

CREATE OR REPLACE FUNCTION VCLib.DIVISIONS_AfterDelete()
RETURNS TRIGGER 
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE VCLib.T_DIVISIONS t SET LeftIndex = t.LeftIndex -
        2 * (SELECT COUNT(*) FROM deleted d WHERE t.LeftIndex > d.LeftIndex)
    WHERE t.LeftIndex > ANY(SELECT LeftIndex FROM deleted);
    
    UPDATE VCLib.T_DIVISIONS t SET RightIndex = t.RightIndex -
        2 * (SELECT COUNT(*) FROM deleted d WHERE t.RightIndex > d.RightIndex)
    WHERE t.RightIndex > ANY(SELECT LeftIndex FROM deleted);
    
    RETURN NULL;
END;
$$;

CREATE TRIGGER DIVISIONS_AfterDelete
    AFTER DELETE ON VCLib.T_DIVISIONS
    REFERENCING OLD TABLE AS deleted
    FOR EACH STATEMENT
    EXECUTE FUNCTION VCLib.DIVISIONS_AfterDelete();

-- Изменение родителей записей:
DROP TRIGGER IF EXISTS DIVISIONS_ChangeParent ON VCLib.T_DIVISIONS;

CREATE OR REPLACE FUNCTION VCLib.DIVISIONS_ChangeParent()
RETURNS TRIGGER 
LANGUAGE plpgsql
AS $$
DECLARE
    v_left INT;
    v_right INT;
    v_level INT;
    v_parent UUID;
    v_id UUID;
    v_spread INT;
    v_newparent UUID;
    v_newleft INT;
    v_newright INT;
    v_newlevel INT;
    curs CURSOR FOR 
        SELECT d.DivisionID, d.ParentID, d.LeftIndex, d.RightIndex, d.TreeLevel, 
               i.ParentID as NewParentID,
               t.LeftIndex as NewLeft, t.RightIndex as NewRight, t.TreeLevel as NewLevel
        FROM deleted d 
        INNER JOIN inserted i ON i.DivisionID = d.DivisionID
        LEFT JOIN VCLib.T_DIVISIONS t ON t.DivisionID = i.ParentID;
BEGIN
    IF (TG_OP = 'UPDATE' AND NEW.ParentID IS DISTINCT FROM OLD.ParentID) THEN
        OPEN curs;
        LOOP        
            FETCH curs INTO v_id, v_parent, v_left, v_right, v_level, v_newparent, v_newleft, v_newright, v_newlevel;
            EXIT WHEN NOT FOUND;

            IF (v_id IS NOT NULL AND v_left IS NOT NULL AND v_right IS NOT NULL) THEN
                -- Помечаем перемещаемые узлы отрицательными значениями
                UPDATE VCLib.T_DIVISIONS SET 
                    LeftIndex = -LeftIndex, 
                    RightIndex = -RightIndex
                WHERE LeftIndex >= v_left AND RightIndex <= v_right;
                
                v_spread := v_right - v_left + 1;
                
                IF (v_newparent IS NULL AND v_parent IS NOT NULL) THEN
                    -- Перемещение в корень
                    UPDATE VCLib.T_DIVISIONS SET LeftIndex = LeftIndex + v_spread
                    WHERE LeftIndex < v_left AND LeftIndex > 0;
                    
                    UPDATE VCLib.T_DIVISIONS SET RightIndex = RightIndex + v_spread
                    WHERE RightIndex < v_left AND RightIndex > 0;
                    
                    UPDATE VCLib.T_DIVISIONS 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 VCLib.T_DIVISIONS SET LeftIndex = LeftIndex - v_spread
                        WHERE LeftIndex > v_left AND LeftIndex <= v_newleft;
                        
                        UPDATE VCLib.T_DIVISIONS SET RightIndex = RightIndex - v_spread
                        WHERE RightIndex > v_left AND RightIndex <= v_newleft;
                    ELSE
                        UPDATE VCLib.T_DIVISIONS SET LeftIndex = LeftIndex + v_spread
                        WHERE LeftIndex > v_newleft AND LeftIndex < v_left;
                        
                        UPDATE VCLib.T_DIVISIONS SET RightIndex = RightIndex + v_spread
                        WHERE RightIndex > v_newleft AND RightIndex < v_left;
                    END IF;
                    
                    -- Получаем актуальные значения нового родителя
                    SELECT LeftIndex, RightIndex INTO v_newleft, v_newright
                    FROM VCLib.T_DIVISIONS WHERE DivisionID = v_newparent;
                    
                    UPDATE VCLib.T_DIVISIONS 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;
        END LOOP;
        CLOSE curs;
    END IF;
    
    RETURN NULL;
END;
$$;

CREATE TRIGGER DIVISIONS_ChangeParent
    AFTER UPDATE ON VCLib.T_DIVISIONS
    REFERENCING OLD TABLE AS deleted NEW TABLE AS inserted
    FOR EACH STATEMENT
    EXECUTE FUNCTION VCLib.DIVISIONS_ChangeParent();

-- пересчет служебных полей - ПРОЦЕДУРА для PostgreSQL 15+
DROP PROCEDURE IF EXISTS VCLib.RecalculateDivisionsTreeParams CASCADE;

CREATE OR REPLACE PROCEDURE VCLib.RecalculateDivisionsTreeParams(
    IN p_ParentID UUID DEFAULT NULL,
    IN p_Begin INT DEFAULT 1,
    INOUT p_End INT DEFAULT NULL,
    IN p_Level INT DEFAULT 0
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_ChCount INT;
    v_NestedLevel INT;
    rec_record RECORD;
BEGIN
    IF (p_ParentID IS NULL) THEN
        SELECT COUNT(*) INTO v_ChCount FROM VCLib.T_DIVISIONS WHERE ParentID IS NULL;
    ELSE
        SELECT COUNT(*) INTO v_ChCount FROM VCLib.T_DIVISIONS WHERE ParentID = p_ParentID;
    END IF;
    
    IF (v_ChCount = 0 OR v_ChCount IS NULL) THEN
        p_End := p_Begin + 1;
        RETURN;
    ELSE
        v_NestedLevel := p_Level + 1;
        
        IF (p_ParentID IS NULL) THEN
            p_Begin := 1;
            FOR rec_record IN SELECT DivisionID FROM VCLib.T_DIVISIONS WHERE ParentID IS NULL
            LOOP                
                CALL VCLib.RecalculateDivisionsTreeParams(
                    p_ParentID => rec_record.DivisionID,
                    p_Begin => p_Begin,
                    p_End => p_End,
                    p_Level => v_NestedLevel
                );
                
                UPDATE VCLib.T_DIVISIONS 
                SET LeftIndex = p_Begin, RightIndex = p_End, TreeLevel = p_Level 
                WHERE DivisionID = rec_record.DivisionID;
                
                p_Begin := p_End + 1;
            END LOOP;
        ELSE
            p_Begin := p_Begin + 1;
            FOR rec_record IN SELECT DivisionID FROM VCLib.T_DIVISIONS WHERE ParentID = p_ParentID
            LOOP                
                CALL VCLib.RecalculateDivisionsTreeParams(
                    p_ParentID => rec_record.DivisionID,
                    p_Begin => p_Begin,
                    p_End => p_End,
                    p_Level => v_NestedLevel
                );
                
                UPDATE VCLib.T_DIVISIONS 
                SET LeftIndex = p_Begin, RightIndex = p_End, TreeLevel = p_Level 
                WHERE DivisionID = rec_record.DivisionID;
                
                p_Begin := p_End + 1;
            END LOOP;
        END IF;
        
        p_End := p_End + 1;
    END IF;
END;
$$;

-- Основная процедура пересчета - ПРОЦЕДУРА для PostgreSQL 15+
DROP PROCEDURE IF EXISTS VCLib.RecalculateDIVISIONS;

CREATE OR REPLACE PROCEDURE VCLib.RecalculateDIVISIONS()
LANGUAGE plpgsql
AS $$
DECLARE
    v_begin INT := 1;
    v_end INT;
BEGIN
    -- Отключаем триггеры
    ALTER TABLE VCLib.T_DIVISIONS DISABLE TRIGGER ALL;
    
    -- Вызываем процедуру пересчета
    CALL VCLib.RecalculateDivisionsTreeParams(
        p_ParentID => NULL,
        p_Begin => v_begin,
        p_End => v_end,
        p_Level => 0
    );
    
    -- Включаем триггеры обратно
    ALTER TABLE VCLib.T_DIVISIONS ENABLE TRIGGER ALL;
END;
$$;