--
-- Name: pay_category_changeparent(); Type: FUNCTION; Schema: public; Owner: postgres
--
CREATE or replace FUNCTION pay_category_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 FIES.T_PAY_CATEGORY t
	where t.PayCategoryID = New.ParentId;
	if Old.PayCategoryID is not null and Old.LeftIndex is not null and Old.RightIndex is not null
	then
		UPDATE FIES.T_PAY_CATEGORY 
		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 FIES.T_PAY_CATEGORY 
			SET LeftIndex = LeftIndex + spread
			WHERE LeftIndex < Old.LeftIndex 
			AND LeftIndex > 0;
			UPDATE FIES.T_PAY_CATEGORY 
			SET RightIndex = RightIndex + spread
			WHERE RightIndex < Old.LeftIndex 
			AND RightIndex > 0;
			UPDATE FIES.T_PAY_CATEGORY 
			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 FIES.T_PAY_CATEGORY 
				SET LeftIndex = LeftIndex - spread
				WHERE LeftIndex > Old.LeftIndex
				AND LeftIndex <= NewLeft;
				UPDATE FIES.T_PAY_CATEGORY 
				SET RightIndex = RightIndex - spread
				WHERE RightIndex > Old.LeftIndex
				AND RightIndex <= NewLeft;
			ELSE
				UPDATE FIES.T_PAY_CATEGORY 
				SET LeftIndex = LeftIndex + spread
				WHERE LeftIndex > NewLeft 
				AND LeftIndex < Old.LeftIndex;
				UPDATE FIES.T_PAY_CATEGORY 
				SET RightIndex = RightIndex + spread
				WHERE RightIndex > NewLeft
				AND RightIndex < Old.LeftIndex;
			end if;
					
			SELECT into 
				NewLeft, NewRight 
				t.LeftIndex, t.RightIndex
			FROM FIES.T_PAY_CATEGORY t
			WHERE t.PayCategoryID = New.ParentID;
			UPDATE FIES.T_PAY_CATEGORY 
			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: pay_category_changeparent; Type: TRIGGER; Schema: public; Owner: sa
--
DO 
$$ 
BEGIN 
IF EXISTS(SELECT * 
                     FROM information_schema.triggers 
                     WHERE event_object_table = lower('T_PAY_CATEGORY') 
                     AND trigger_name = lower('pay_category_changeparent')
                     ) 
                    THEN 
                    DROP TRIGGER pay_category_changeparent ON FIES.T_PAY_CATEGORY;
    END IF ; 

END; 
$$ ;

CREATE TRIGGER pay_category_changeparent AFTER UPDATE OF parentid ON FIES.T_PAY_CATEGORY FOR EACH ROW EXECUTE PROCEDURE pay_category_changeparent();
