--
-- Name: ehr_rus_tree_changeparent(); Type: FUNCTION; Schema: public; Owner: postgres
--
CREATE or replace FUNCTION ehr_rus_tree_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 EHR.T_EHR_RUS_TREE t
	where t.EhrRusID = New.EhrRusTree;
	if Old.EhrRusID is not null and Old.LeftIndex is not null and Old.RightIndex is not null
	then
		UPDATE EHR.T_EHR_RUS_TREE 
		SET	LeftIndex = -LeftIndex
		,	RightIndex = -RightIndex
		WHERE LeftIndex >= Old.LeftIndex 
		AND RightIndex <= Old.RightIndex;

		spread := Old.RightIndex - Old.LeftIndex + 1;
			
		IF New.EhrRusTree IS NULL AND Old.EhrRusTree IS NOT NULL
		then
			UPDATE EHR.T_EHR_RUS_TREE 
			SET LeftIndex = LeftIndex + spread
			WHERE LeftIndex < Old.LeftIndex 
			AND LeftIndex > 0;
			UPDATE EHR.T_EHR_RUS_TREE 
			SET RightIndex = RightIndex + spread
			WHERE RightIndex < Old.LeftIndex 
			AND RightIndex > 0;
			UPDATE EHR.T_EHR_RUS_TREE 
			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 EHR.T_EHR_RUS_TREE 
				SET LeftIndex = LeftIndex - spread
				WHERE LeftIndex > Old.LeftIndex
				AND LeftIndex <= NewLeft;
				UPDATE EHR.T_EHR_RUS_TREE 
				SET RightIndex = RightIndex - spread
				WHERE RightIndex > Old.LeftIndex
				AND RightIndex <= NewLeft;
			ELSE
				UPDATE EHR.T_EHR_RUS_TREE 
				SET LeftIndex = LeftIndex + spread
				WHERE LeftIndex > NewLeft 
				AND LeftIndex < Old.LeftIndex;
				UPDATE EHR.T_EHR_RUS_TREE 
				SET RightIndex = RightIndex + spread
				WHERE RightIndex > NewLeft
				AND RightIndex < Old.LeftIndex;
			end if;
					
			SELECT into 
				NewLeft, NewRight 
				t.LeftIndex, t.RightIndex
			FROM EHR.T_EHR_RUS_TREE t
			WHERE t.EhrRusID = New.EhrRusTree;
			UPDATE EHR.T_EHR_RUS_TREE 
			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: ehr_rus_tree_delete(); Type: FUNCTION; Schema: public; Owner: postgres
--
CREATE or replace FUNCTION ehr_rus_tree_delete() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
	UPDATE EHR.T_EHR_RUS_TREE 
	SET LeftIndex = LeftIndex - 2
	WHERE LeftIndex > Old.LeftIndex;
	UPDATE EHR.T_EHR_RUS_TREE 
	SET RightIndex = RightIndex - 2
	WHERE RightIndex > Old.LeftIndex;
	Return Old;
END;
$$;



--
-- Name: ehr_rus_tree_insert(); Type: FUNCTION; Schema: public; Owner: postgres
--
CREATE or replace FUNCTION ehr_rus_tree_insert() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
Declare	LeftVar int;
	levelVar INT; 
BEGIN
	IF New.EhrRusID IS NOT NULL
	then
		IF New.EhrRusTree IS NOT NULL
		then
			SELECT into
				leftVar, levelVar
				LeftIndex, TreeLevel
			FROM EHR.T_EHR_RUS_TREE 
			WHERE EhrRusID = New.EhrRusTree;
			UPDATE EHR.T_EHR_RUS_TREE 
			SET LeftIndex = LeftIndex + 2 
			WHERE LeftIndex > leftVar;
			UPDATE EHR.T_EHR_RUS_TREE 
			SET RightIndex = RightIndex + 2 
			WHERE RightIndex > leftVar;
			UPDATE EHR.T_EHR_RUS_TREE 
			SET	LeftIndex = leftVar + 1
			,	RightIndex = leftVar + 2
			,	TreeLevel = levelVar + 1
			WHERE EhrRusID = New.EhrRusID;
		ELSE
			UPDATE EHR.T_EHR_RUS_TREE 
			SET	LeftIndex = LeftIndex + 2
			,	RightIndex = RightIndex + 2;
			UPDATE EHR.T_EHR_RUS_TREE 
			SET	LeftIndex = 1
			,	RightIndex = 2
			,	TreeLevel = 0 
			WHERE EhrRusID = New.EhrRusID;
		END if;
	end if;
	Return New;
END;
$$;



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

END; 
$$ ;

CREATE TRIGGER ehr_rus_tree_afterdelete AFTER DELETE ON EHR.T_EHR_RUS_TREE FOR EACH ROW EXECUTE PROCEDURE ehr_rus_tree_delete();



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

END; 
$$ ;

CREATE TRIGGER ehr_rus_tree_afterinsert AFTER INSERT ON EHR.T_EHR_RUS_TREE FOR EACH ROW EXECUTE PROCEDURE ehr_rus_tree_insert();



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

END; 
$$ ;

CREATE TRIGGER ehr_rus_tree_changeparent AFTER UPDATE OF EhrRusTree ON EHR.T_EHR_RUS_TREE FOR EACH ROW EXECUTE PROCEDURE ehr_rus_tree_changeparent();



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

CREATE or Replace Function public.RecalculateehrrustreeTreeParams
(
	"EhrRusTree" UUID = NULL,
	"Begin" INT = 1,
	"End" OUT INT ,
	"Level" INT = 0
)
As 
$$
Declare
	ChCount int;
	IDVar UUID; 
	NestedLevel INT;
Begin
	IF "EhrRusTree" IS NULL 
	then
		SELECT COUNT(*) 
		FROM ehr.T_EHR_RUS_TREE m
		into ChCount
		WHERE m.EhrRusTree IS NULL;
	ELSE 
		SELECT COUNT(*) 
		FROM ehr.T_EHR_RUS_TREE m
		into ChCount
		WHERE m.EhrRusTree = "EhrRusTree";
	end if;
	
	IF ChCount = 0 or ChCount is NULL
	then
		"End" := "Begin" + 1;
	ELSE
		NestedLevel := "Level" + 1;		
		IF "EhrRusTree" IS NULL
		then
			"Begin" := 1;
			FOR IdVar IN select m.ehrrustreeID FROM ehr.T_EHR_RUS_TREE m WHERE m.EhrRusTree IS NULL ORDER BY m.ehrrustreeID LOOP
				select (select * from public.RecalculateehrrustreeTreeParams(IdVar, "Begin", NestedLevel)) into "End";
				UPDATE ehr.T_EHR_RUS_TREE 
				SET 	LeftIndex = "Begin"
				,	RightIndex = "End"
				,	TreeLevel = "Level"
				WHERE ehrrustreeID = IDVar;
				"Begin" := "End" + 1;
			END LOOP;					
		ELSE
			"Begin" := "Begin" + 1;
			FOR IdVar IN select m.ehrrustreeID FROM ehr.T_EHR_RUS_TREE m WHERE m.EhrRusTree = "EhrRusTree" ORDER BY m.ehrrustreeID LOOP
				select (select * from public.RecalculateehrrustreeTreeParams(IdVar, "Begin", NestedLevel)) into "End";
				UPDATE ehr.T_EHR_RUS_TREE 
				SET 	LeftIndex = "Begin"
				,	RightIndex = "End"
				,	TreeLevel = "Level"
				WHERE ehrrustreeID = 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 = 'recalculateehrrustree'
	)
	then
		drop function public.recalculateehrrustree();
	end if;
end;
$$ language plpgsql;

CREATE or Replace Function public.Recalculateehrrustree()
RETURNS Void
As 
$$
Begin
	Alter Table ehr.T_EHR_RUS_TREE Disable Trigger All;
	perform public.RecalculateehrrustreeTreeParams(null, 1, 0);
	Alter Table ehr.T_EHR_RUS_TREE Enable Trigger All;
End;
$$ Language plpgsql;
