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

DECLARE
  v_item_id              cr_items.item_id%TYPE;
  v_latest_revision      cr_revisions.revision_id%TYPE;
BEGIN
  --
  -- Get item_id and the latest revision
  --
  select item_id
  into   v_item_id
  from   cr_revisions 
  where  revision_id = delete__revision_id;

  select latest_revision
  into   v_latest_revision
  from   cr_items
  where  item_id = v_item_id;

  --
  -- Recalculate latest revision in case it was deleted
  --
  if v_latest_revision = delete__revision_id then

      select r.revision_id
       into v_latest_revision
       from cr_revisions r, acs_objects o
      where o.object_id = r.revision_id
        and r.item_id = v_item_id
        and r.revision_id <> delete__revision_id
      order by o.creation_date desc limit 1;

      if NOT FOUND then
         v_latest_revision := null;
      end if;

      update cr_items set latest_revision = v_latest_revision
      where item_id = v_item_id;
      
  end if; 

  --
  -- Delete the revision
  --
  PERFORM acs_object__delete(delete__revision_id);

  return 0; 
END;
$$ language plpgsql;