--
-- Name: divisions_changeparent(); Type: FUNCTION; Schema: vclib; Owner: postgres
--
DROP FUNCTION IF EXISTS vclib.divisions_changeparent CASCADE;
CREATE FUNCTION vclib.divisions_changeparent() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
Declare	NewLeft int; 
	newright int;
	newlevel int;
	spread int;
BEGIN
	select into
		NewLeft, NewRight, NewLevel
		t.LeftIndex, t.RightIndex, t.TreeLevel
	from VCLib.T_Divisions t
	where t.DivisionId = New.ParentId;
	if Old.DivisionId is not null and Old.LeftIndex is not null and Old.RightIndex is not null
	then
		UPDATE VCLib.T_DIVISIONS 
		SET	LeftIndex = -LeftIndex
		,	RightIndex = -RightIndex
		WHERE LeftIndex >= Old.LeftIndex 
		AND RightIndex <= Old.RightIndex;

		spread := Old.RightIndex - Old.LeftIndex + 1;
			
		IF New.ParentID IS NULL AND Old.ParentID IS NOT NULL
		then
			UPDATE VCLib.T_DIVISIONS 
			SET LeftIndex = LeftIndex + spread
			WHERE LeftIndex < Old.LeftIndex 
			AND LeftIndex > 0;
			UPDATE VCLib.T_DIVISIONS 
			SET RightIndex = RightIndex + spread
			WHERE RightIndex < Old.LeftIndex 
			AND RightIndex > 0;
			UPDATE VCLib.T_DIVISIONS 
			SET 	LeftIndex = 1 - (Old.LeftIndex + LeftIndex)
			,	RightIndex = 1 - (Old.LeftIndex + RightIndex)
			,	TreeLevel = TreeLevel - Old.TreeLevel
			WHERE LeftIndex <= -Old.LeftIndex 
			AND RightIndex >= -Old.RightIndex;
		ELSE
			IF Old.LeftIndex < NewLeft
			then
				UPDATE VCLib.T_DIVISIONS 
				SET LeftIndex = LeftIndex - spread
				WHERE LeftIndex > Old.LeftIndex
				AND LeftIndex <= NewLeft;
				UPDATE VCLib.T_DIVISIONS 
				SET RightIndex = RightIndex - spread
				WHERE RightIndex > Old.LeftIndex
				AND RightIndex <= NewLeft;
			ELSE
				UPDATE VCLib.T_DIVISIONS 
				SET LeftIndex = LeftIndex + spread
				WHERE LeftIndex > NewLeft 
				AND LeftIndex < Old.LeftIndex;
				UPDATE VCLib.T_DIVISIONS 
				SET RightIndex = RightIndex + spread
				WHERE RightIndex > NewLeft
				AND RightIndex < Old.LeftIndex;
			end if;
					
			SELECT into 
				NewLeft, NewRight 
				t.LeftIndex, t.RightIndex
			FROM VCLib.T_DIVISIONS t
			WHERE t.DivisionID = New.ParentID;
			UPDATE VCLib.T_DIVISIONS 
			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 New;	
END;
$$;

CREATE TRIGGER divisions_changeparent AFTER UPDATE OF parentid ON vclib.t_divisions FOR EACH ROW EXECUTE PROCEDURE vclib.divisions_changeparent();

--
-- Name: divisions_delete(); Type: FUNCTION; Schema: vclib; Owner: postgres
--
DROP FUNCTION IF EXISTS vclib.divisions_delete CASCADE;
CREATE FUNCTION vclib.divisions_delete() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
	UPDATE VCLib.T_DIVISIONS 
	SET LeftIndex = LeftIndex - 2
	WHERE LeftIndex > Old.LeftIndex;
	UPDATE VCLib.T_DIVISIONS 
	SET RightIndex = RightIndex - 2
	WHERE RightIndex > Old.LeftIndex;
	Return Old;
END;
$$;

CREATE TRIGGER divisions_afterdelete AFTER DELETE ON vclib.t_divisions FOR EACH ROW EXECUTE PROCEDURE vclib.divisions_delete();

--
-- Name: divisions_insert(); Type: FUNCTION; Schema: vclib; Owner: postgres
--
DROP FUNCTION IF EXISTS vclib.divisions_insert CASCADE;
CREATE FUNCTION vclib.divisions_insert() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
Declare	LeftVar int;
	levelVar INT; 
BEGIN
	IF New.DivisionId IS NOT NULL
	then
		IF New.ParentId IS NOT NULL
		then
			SELECT into
				leftVar, levelVar
				LeftIndex, TreeLevel
			FROM VCLib.T_DIVISIONS 
			WHERE DivisionID = New.ParentId;
			UPDATE VCLib.T_DIVISIONS 
			SET LeftIndex = LeftIndex + 2 
			WHERE LeftIndex > leftVar;
			UPDATE VCLib.T_DIVISIONS 
			SET RightIndex = RightIndex + 2 
			WHERE RightIndex > leftVar;
			UPDATE VCLib.T_DIVISIONS 
			SET	LeftIndex = leftVar + 1
			,	RightIndex = leftVar + 2
			,	TreeLevel = levelVar + 1
			WHERE DivisionID = New.DivisionId;
		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 = New.DivisionId;
		END if;
	end if;
	Return New;
END;
$$;

CREATE TRIGGER divisions_afterinsert AFTER INSERT ON vclib.t_divisions FOR EACH ROW EXECUTE PROCEDURE vclib.divisions_insert();


DROP FUNCTION IF EXISTS VCLib.RecalculateDivisionsTreeParams;
CREATE FUNCTION VCLib.RecalculateDivisionsTreeParams(
	parentid uuid DEFAULT NULL::uuid,
	"begin" integer DEFAULT 1,
	OUT "end" integer,
	level integer DEFAULT 0)
    RETURNS integer
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$
DECLARE ChCount integer;
ID  uuid; NestedLevel integer;
curs1 CURSOR FOR 
	SELECT PayCategoryID FROM VCLib.T_DIVISIONS WHERE ParentID IS NULL;
curs1P CURSOR FOR 
	SELECT PayCategoryID FROM VCLib.T_DIVISIONS WHERE ParentID=ParentID;
BEGIN
IF (ParentID IS NULL) then
ChCount := (SELECT COUNT(ParentID) FROM VCLib.T_DIVISIONS WHERE ParentID IS NULL);
ELSE  ChCount := (SELECT COUNT(ParentID) FROM VCLib.T_DIVISIONS WHERE ParentID=ParentID);
end if;

IF (ChCount = 0 or @ChCount is NULL)
	then	"end" := "begin" + 1;
	ELSE
		NestedLevel := Level + 1;
		IF (ParentID IS NULL)
		then
			set "begin" = 1;
			OPEN curs1;
			FETCH NEXT FROM curs1 INTO ID;
			WHILE (Found)
			loop
				EXECUTE VCLib.RecalculateDivisionsTreeParams ID, "begin", "end" , NestedLevel;
				UPDATE VCLib.T_DIVISIONS 
				SET LeftIndex="begin", 
					RightIndex="end", 
					TreeLevel=Level 
				WHERE DivisionID=ID;
				"begin" := "end" + 1;
				FETCH NEXT FROM curs1 INTO ID;
			END loop;
			CLOSE curs1;
			DEALLOCATE curs1;
		ELSE
			"begin" := "begin" + 1;
			OPEN curs1P;
			FETCH NEXT FROM curs1 INTO ID;
			WHILE (found)
			loop
				EXECUTE VCLib.RecalculateDivisionsTreeParams ID, "begin", "end" , NestedLevel ;
				UPDATE VCLib.T_DIVISIONS 
				SET LeftIndex="begin", 
					RightIndex="end", 
					TreeLevel=Level 
				WHERE DivisionID =ID;
				"begin" = "end" + 1;
				FETCH NEXT FROM curs1 INTO ID;
			END loop;
			CLOSE curs1P;
			DEALLOCATE curs1P;
		end if;
		"end" := "end" + 1;
	END if;
end;
$BODY$;

DROP FUNCTION IF EXISTS VCLib.recalculatedivisions;
CREATE FUNCTION VCLib.recalculatedivisions(
	)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$
declare "end" int;
BEGIN
	alter table VCLib.T_DIVISIONS disable trigger all;
	EXECUTE VCLib.RecalculateDivisionsTreeParams 'null', 1, "end", 0;
	alter table VCLib.T_DIVISIONS enable trigger all;
end;
$BODY$;
