--
-- Name: organization_changeparent(); Type: FUNCTION; Schema: public; Owner: postgres
--
CREATE or replace FUNCTION organization_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 NSZ.T_ORGANIZATION t
	where t.OrganizationID = New.ParentId;
	if Old.OrganizationID is not null and Old.LeftIndex is not null and Old.RightIndex is not null
	then
		UPDATE NSZ.T_ORGANIZATION 
		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 NSZ.T_ORGANIZATION 
			SET LeftIndex = LeftIndex + spread
			WHERE LeftIndex < Old.LeftIndex 
			AND LeftIndex > 0;
			UPDATE NSZ.T_ORGANIZATION 
			SET RightIndex = RightIndex + spread
			WHERE RightIndex < Old.LeftIndex 
			AND RightIndex > 0;
			UPDATE NSZ.T_ORGANIZATION 
			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 NSZ.T_ORGANIZATION 
				SET LeftIndex = LeftIndex - spread
				WHERE LeftIndex > Old.LeftIndex
				AND LeftIndex <= NewLeft;
				UPDATE NSZ.T_ORGANIZATION 
				SET RightIndex = RightIndex - spread
				WHERE RightIndex > Old.LeftIndex
				AND RightIndex <= NewLeft;
			ELSE
				UPDATE NSZ.T_ORGANIZATION 
				SET LeftIndex = LeftIndex + spread
				WHERE LeftIndex > NewLeft 
				AND LeftIndex < Old.LeftIndex;
				UPDATE NSZ.T_ORGANIZATION 
				SET RightIndex = RightIndex + spread
				WHERE RightIndex > NewLeft
				AND RightIndex < Old.LeftIndex;
			end if;
					
			SELECT into 
				NewLeft, NewRight 
				t.LeftIndex, t.RightIndex
			FROM NSZ.T_ORGANIZATION t
			WHERE t.OrganizationID = New.ParentID;
			UPDATE NSZ.T_ORGANIZATION 
			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;
$$;

--
-- Name: organization_delete(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE or replace FUNCTION organization_delete() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
	UPDATE NSZ.T_ORGANIZATION 
	SET LeftIndex = LeftIndex - 2
	WHERE LeftIndex > Old.LeftIndex;
	UPDATE NSZ.T_ORGANIZATION 
	SET RightIndex = RightIndex - 2
	WHERE RightIndex > Old.LeftIndex;
	Return Old;
END;
$$;

--
-- Name: organization_insert(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE or replace FUNCTION organization_insert() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
Declare	LeftVar int;
	levelVar INT; 
BEGIN
	IF New.OrganizationID IS NOT NULL
	then
		IF New.ParentId IS NOT NULL
		then
			SELECT into
				leftVar, levelVar
				LeftIndex, TreeLevel
			FROM NSZ.T_ORGANIZATION 
			WHERE OrganizationID = New.ParentId;
			UPDATE NSZ.T_ORGANIZATION 
			SET LeftIndex = LeftIndex + 2 
			WHERE LeftIndex > leftVar;
			UPDATE NSZ.T_ORGANIZATION 
			SET RightIndex = RightIndex + 2 
			WHERE RightIndex > leftVar;
			UPDATE NSZ.T_ORGANIZATION 
			SET	LeftIndex = leftVar + 1
			,	RightIndex = leftVar + 2
			,	TreeLevel = levelVar + 1
			WHERE OrganizationID = New.OrganizationID;
		ELSE
			UPDATE NSZ.T_ORGANIZATION 
			SET	LeftIndex = LeftIndex + 2
			,	RightIndex = RightIndex + 2;
			UPDATE NSZ.T_ORGANIZATION 
			SET	LeftIndex = 1
			,	RightIndex = 2
			,	TreeLevel = 0 
			WHERE OrganizationID = New.OrganizationID;
		END if;
	end if;
	Return New;
END;
$$;


--
-- Name: organization_afterdelete; Type: TRIGGER; Schema: public; Owner: sa
--
DO 
$$ 
BEGIN 
IF EXISTS(SELECT * 
                     FROM information_schema.triggers 
                     WHERE event_object_table = lower('T_ORGANIZATION') 
                     AND trigger_name = lower('organization_afterdelete')
                     ) 
                    THEN 
                    DROP TRIGGER organization_afterdelete ON NSZ.T_ORGANIZATION;
    END IF ; 

END; 
$$ ;

CREATE TRIGGER organization_afterdelete AFTER DELETE ON NSZ.T_ORGANIZATION FOR EACH ROW EXECUTE PROCEDURE organization_delete();


--
-- Name: organization_afterinsert; Type: TRIGGER; Schema: public; Owner: sa
--
DO 
$$ 
BEGIN 
IF EXISTS(SELECT * 
                     FROM information_schema.triggers 
                     WHERE event_object_table = lower('T_ORGANIZATION') 
                     AND trigger_name = lower('organization_afterinsert')
                     ) 
                    THEN 
                    DROP TRIGGER organization_afterinsert ON NSZ.T_ORGANIZATION;
    END IF ; 

END; 
$$ ;

CREATE TRIGGER organization_afterinsert AFTER INSERT ON NSZ.T_ORGANIZATION FOR EACH ROW EXECUTE PROCEDURE organization_insert();


--
-- Name: organization_changeparent; Type: TRIGGER; Schema: public; Owner: sa
--
DO 
$$ 
BEGIN 
IF EXISTS(SELECT * 
                     FROM information_schema.triggers 
                     WHERE event_object_table = lower('T_ORGANIZATION') 
                     AND trigger_name = lower('organization_changeparent')
                     ) 
                    THEN 
                    DROP TRIGGER organization_changeparent ON NSZ.T_ORGANIZATION;
    END IF ; 

END; 
$$ ;

CREATE TRIGGER organization_changeparent AFTER UPDATE OF parentid ON NSZ.T_ORGANIZATION FOR EACH ROW EXECUTE PROCEDURE organization_changeparent();



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
	Alter Table nsz.T_organization Disable Trigger All;
	perform public.RecalculateorganizationTreeParams(null, 1, 0);
	Alter Table nsz.T_organization Enable Trigger All;
End;
$$ Language plpgsql;
