ALTER TABLE VCLib.T_ENTITIES ADD if not exists "leftindex" integer NULL;
ALTER TABLE VCLib.T_ENTITIES ADD if not exists "rightindex" integer NULL;
ALTER TABLE VCLib.T_ENTITIES ADD if not exists "treelevel" integer NULL;

--CALL VCLib.RecalculateENTITIES()
--SELECT t0.EntityID, t0.EntName, t0.LeftIndex, t0.RightIndex, t0.TreeLevel FROM VCLib.T_ENTITIES t0 WHERE t0.EntName='DIAGNOSTICS_RESULTS';
--SELECT t0.EntityID, t0.EntName, t0.LeftIndex, t0.RightIndex, t0.TreeLevel FROM VCLib.T_ENTITIES t0 
--WHERE ( EXISTS (SELECT * FROM VCLib.T_ENTITIES t3  WHERE t3.EntName='DIAGNOSTICS_RESULTS' AND t0.LeftIndex>=t3.LeftIndex AND t0.RightIndex<=t3.RightIndex));

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

--CREATE OR REPLACE FUNCTION VCLib.ENTITIES_AfterInsert()
--RETURNS TRIGGER 
--LANGUAGE plpgsql
--AS $$
--DECLARE
--    v_left INT;
--    v_level INT;
--    v_parent INT;
--    v_id INT;
--    curs CURSOR FOR SELECT EntityID, BaseEntity 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_ENTITIES WHERE EntityID = v_parent;
                
--                UPDATE VCLib.T_ENTITIES SET LeftIndex = LeftIndex + 2 WHERE LeftIndex > v_left;
--                UPDATE VCLib.T_ENTITIES SET RightIndex = RightIndex + 2 WHERE RightIndex > v_left;
--                UPDATE VCLib.T_ENTITIES SET 
--                    LeftIndex = v_left + 1, 
--                    RightIndex = v_left + 2, 
--                    TreeLevel = v_level + 1
--                WHERE EntityID = v_id;
--            ELSE
--                UPDATE VCLib.T_ENTITIES SET 
--                    LeftIndex = LeftIndex + 2, 
--                    RightIndex = RightIndex + 2;
--                UPDATE VCLib.T_ENTITIES SET 
--                    LeftIndex = 1, 
--                    RightIndex = 2, 
--                    TreeLevel = 0 
--                WHERE EntityID = v_id;
--            END IF;
--        END IF;
--    END LOOP;
--    CLOSE curs;
    
--    RETURN NULL;
--END;
--$$;

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

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

--CREATE OR REPLACE FUNCTION VCLib.ENTITIES_AfterDelete()
--RETURNS TRIGGER 
--LANGUAGE plpgsql
--AS $$
--BEGIN
--    UPDATE VCLib.T_ENTITIES 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_ENTITIES 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 ENTITIES_AfterDelete
--    AFTER DELETE ON VCLib.T_ENTITIES
--    REFERENCING OLD TABLE AS deleted
--    FOR EACH STATEMENT
--    EXECUTE FUNCTION VCLib.ENTITIES_AfterDelete();

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

--CREATE OR REPLACE FUNCTION VCLib.ENTITIES_ChangeParent()
--RETURNS TRIGGER 
--LANGUAGE plpgsql
--AS $$
--DECLARE
--    v_left INT;
--    v_right INT;
--    v_level INT;
--    v_parent INT;
--    v_id INT;
--    v_spread INT;
--    v_newparent INT;
--    v_newleft INT;
--    v_newright INT;
--    v_newlevel INT;
--    curs CURSOR FOR 
--        SELECT d.EntityID, d.BaseEntity, d.LeftIndex, d.RightIndex, d.TreeLevel, i.BaseEntity as NewEntityID, t.LeftIndex as NewLeft, t.RightIndex as NewRight, t.TreeLevel as NewLevel
--        FROM deleted d 
--        INNER JOIN inserted i ON i.EntityID = d.EntityID
--        LEFT JOIN VCLib.T_ENTITIES t ON t.EntityID = i.BaseEntity;
--BEGIN
--    IF (TG_OP = 'UPDATE' AND NEW.BaseEntity IS DISTINCT FROM OLD.BaseEntity) 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_ENTITIES 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_ENTITIES SET LeftIndex = LeftIndex + v_spread
--                    WHERE LeftIndex < v_left AND LeftIndex > 0;
                    
--                    UPDATE VCLib.T_ENTITIES SET RightIndex = RightIndex + v_spread
--                    WHERE RightIndex < v_left AND RightIndex > 0;
                    
--                    UPDATE VCLib.T_ENTITIES 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_ENTITIES SET LeftIndex = LeftIndex - v_spread
--                        WHERE LeftIndex > v_left AND LeftIndex <= v_newleft;
                        
--                        UPDATE VCLib.T_ENTITIES SET RightIndex = RightIndex - v_spread
--                        WHERE RightIndex > v_left AND RightIndex <= v_newleft;
--                    ELSE
--                        UPDATE VCLib.T_ENTITIES SET LeftIndex = LeftIndex + v_spread
--                        WHERE LeftIndex > v_newleft AND LeftIndex < v_left;
                        
--                        UPDATE VCLib.T_ENTITIES 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_ENTITIES WHERE EntityID = v_newparent;
                    
--                    UPDATE VCLib.T_ENTITIES 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 ENTITIES_ChangeParent
--    AFTER UPDATE ON VCLib.T_ENTITIES
--    REFERENCING OLD TABLE AS deleted NEW TABLE AS inserted
--    FOR EACH STATEMENT
--    EXECUTE FUNCTION VCLib.ENTITIES_ChangeParent();

-- пересчет служебных полей
DROP PROCEDURE IF EXISTS VCLib.RecalculateEntitiesTreeParams CASCADE;

CREATE OR REPLACE PROCEDURE VCLib.RecalculateEntitiesTreeParams(
    IN p_EntityID INT 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_EntityID IS NULL) THEN
        SELECT COUNT(*) INTO v_ChCount FROM VCLib.T_ENTITIES WHERE BaseEntity IS NULL;
    ELSE
        SELECT COUNT(*) INTO v_ChCount FROM VCLib.T_ENTITIES WHERE BaseEntity = p_EntityID;
    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_EntityID IS NULL) THEN
            p_Begin := 1;
            FOR rec_record IN SELECT EntityID FROM VCLib.T_ENTITIES WHERE BaseEntity IS NULL ORDER BY EntityID
            LOOP                
                CALL VCLib.RecalculateEntitiesTreeParams(
                    p_EntityID => rec_record.EntityID,
                    p_Begin => p_Begin,
                    p_End => p_End,
                    p_Level => v_NestedLevel
                );
                
                UPDATE VCLib.T_ENTITIES 
                SET LeftIndex = p_Begin, RightIndex = p_End, TreeLevel = p_Level 
                WHERE EntityID = rec_record.EntityID;
                
                p_Begin := p_End + 1;
            END LOOP;
        ELSE
            p_Begin := p_Begin + 1;
            FOR rec_record IN SELECT EntityID FROM VCLib.T_ENTITIES WHERE BaseEntity = p_EntityID ORDER BY EntityID
            LOOP                
                CALL VCLib.RecalculateEntitiesTreeParams(
                    p_EntityID => rec_record.EntityID,
                    p_Begin => p_Begin,
                    p_End => p_End,
                    p_Level => v_NestedLevel
                );
                
                UPDATE VCLib.T_ENTITIES 
                SET LeftIndex = p_Begin, RightIndex = p_End, TreeLevel = p_Level 
                WHERE EntityID = rec_record.EntityID;
                
                p_Begin := p_End + 1;
            END LOOP;
        END IF;
        
        p_End := p_End + 1;
    END IF;
END;
$$;

-- Основная процедура пересчета
DROP PROCEDURE IF EXISTS VCLib.RecalculateENTITIES;

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