--
-- category__del/1
--
create or replace function category__del(
  integer
) returns int4 as $$

declare
        p_category_id   alias for $1;

        v_tree_id       integer;
	v_left_ind      integer;
	v_right_ind     integer;
        node            record;
begin
        select tree_id, left_ind, right_ind
	into v_tree_id, v_left_ind, v_right_ind
	from categories where category_id = p_category_id;

	for node in 
           select category_id
	     from categories
	    where tree_id = v_tree_id
	      and left_ind >= v_left_ind
	      and right_ind <= v_right_ind 
        loop
	   delete from category_object_map where category_id = node.category_id;
	   delete from category_translations where category_id = node.category_id;
	   delete from categories where category_id = node.category_id;
	   perform acs_object__delete(node.category_id);
	end loop;

	update categories
	set right_ind = (right_ind - (1 + v_right_ind - v_left_ind))
	where left_ind <= v_left_ind
	and right_ind > v_left_ind
	and tree_id = v_tree_id;

	update categories
	set right_ind = (right_ind - (1 + v_right_ind - v_left_ind)),
	    left_ind = (left_ind - (1 + v_right_ind - v_left_ind))
	where left_ind > v_left_ind
	and tree_id = v_tree_id;
	
        -- for debugging reasons
        perform category_tree__check_nested_ind(v_tree_id);
        return 0;
end;
$$ language plpgsql;