--
-- cr_items_tree_update_tr/0
--
create or replace function cr_items_tree_update_tr(
  
) returns trigger as $$

declare
        v_parent_sk     	varbit default null;
        v_max_child_sortkey     varbit;
        v_parent_id            	integer default null;
        v_old_parent_length	integer;
begin
        if new.item_id = old.item_id and 
           ((new.parent_id = old.parent_id) or
            (new.parent_id is null and old.parent_id is null)) then

           return new;

        end if;

        select item_id
    	into   v_parent_id
	from   cr_items
	where  item_id = new.parent_id;

	-- the tree sortkey is going to change so get the new one and update it and all its
	-- children to have the new prefix...
	v_old_parent_length := length(new.tree_sortkey) + 1;

        if new.parent_id = 0 then
            v_parent_sk := int_to_tree_key(new.item_id+1000);
	elsif v_parent_id is null then 
            v_parent_sk := int_to_tree_key(new.parent_id+1000) || int_to_tree_key(new.item_id+1000);
        else
	    SELECT tree_sortkey, tree_increment_key(max_child_sortkey)
	    INTO v_parent_sk, v_max_child_sortkey
	    FROM cr_items
	    WHERE item_id = new.parent_id 
	    FOR UPDATE;

	    UPDATE cr_items
	    SET max_child_sortkey = v_max_child_sortkey
	    WHERE item_id = new.parent_id;

	    v_parent_sk := v_parent_sk || v_max_child_sortkey;
        end if;

	UPDATE cr_items
	SET tree_sortkey = v_parent_sk || substring(tree_sortkey, v_old_parent_length)
	WHERE tree_sortkey between new.tree_sortkey and tree_right(new.tree_sortkey);

        return new;

end;$$ language plpgsql;