do $$
begin
	if exists 
	(
		select * from pg_proc p
		where p.proname = 'recalculateorganizationtreeparams'
	)
	then
		drop function public.recalculateorganizationtreeparams(uuid, int, int);
	end if;
end;
$$ language plpgsql;

CREATE or Replace Function public.RecalculateorganizationTreeParams
(
	"ParentID" UUID = NULL,
	"Begin" INT = 1,
	"End" OUT INT ,
	"Level" INT = 0
)
As 
$$
Declare
	ChCount int;
	IDVar UUID; 
	NestedLevel INT;
Begin
	IF "ParentID" IS NULL 
	then
		SELECT COUNT(*) 
		FROM nsz.T_organization m
		into ChCount
		WHERE m.ParentID IS NULL;
	ELSE 
		SELECT COUNT(*) 
		FROM nsz.T_organization m
		into ChCount
		WHERE m.ParentID = "ParentID";
	end if;
	
	IF ChCount = 0 or ChCount is NULL
	then
		"End" := "Begin" + 1;
	ELSE
		NestedLevel := "Level" + 1;		
		IF "ParentID" IS NULL
		then
			"Begin" := 1;
			FOR IdVar IN select m.organizationID FROM nsz.T_organization m WHERE m.ParentID IS NULL ORDER BY m.organizationID LOOP
				select (select * from public.RecalculateorganizationTreeParams(IdVar, "Begin", NestedLevel)) into "End";
				UPDATE nsz.T_organization 
				SET 	LeftIndex = "Begin"
				,	RightIndex = "End"
				,	TreeLevel = "Level"
				WHERE organizationID = IDVar;
				"Begin" := "End" + 1;
			END LOOP;					
		ELSE
			"Begin" := "Begin" + 1;
			FOR IdVar IN select m.organizationID FROM nsz.T_organization m WHERE m.ParentID = "ParentID" ORDER BY m.organizationID LOOP
				select (select * from public.RecalculateorganizationTreeParams(IdVar, "Begin", NestedLevel)) into "End";
				UPDATE nsz.T_organization 
				SET 	LeftIndex = "Begin"
				,	RightIndex = "End"
				,	TreeLevel = "Level"
				WHERE organizationID = IDVar;
				"Begin" := "End" + 1;
			END LOOP;
		END if;		
		"End" := "End" + 1;
	END if;
End;
$$ Language plpgsql;

do $$
begin
	if exists 
	(
		select * from pg_proc p
		where p.proname = 'recalculateorganization'
	)
	then
		drop function public.recalculateorganization();
	end if;
end;
$$ language plpgsql;

CREATE or Replace Function public.Recalculateorganization()
RETURNS Void
As 
$$
Begin
	perform public.RecalculateorganizationTreeParams(null, 1, 0);
	Alter Table nsz.T_organization Enable Trigger All;
End;
$$ Language plpgsql;
