--
-- Note: some of the update operations might take on large sites a
-- couple of minutes, since these operate on the largest tables of
-- OpenACS. You might consider to run this on production offline or
-- with a proxy turned off.
--
-- Make sure there are no stray entries in cr_child_rels
--
delete from cr_child_rels where parent_id in (select parent_id from cr_child_rels except select object_id from acs_objects);
delete from cr_child_rels where child_id in (select child_id from cr_child_rels except select item_id from cr_items);

--
-- Add FK constraints for cr_child_rels with cascade operations
--
ALTER TABLE cr_child_rels DROP CONSTRAINT IF EXISTS cr_child_rels_parent_id_fk;
ALTER TABLE cr_child_rels ADD CONSTRAINT cr_child_rels_parent_id_fk
FOREIGN KEY (parent_id) REFERENCES acs_objects(object_id) ON DELETE CASCADE;

ALTER TABLE cr_child_rels DROP CONSTRAINT IF EXISTS cr_child_rels_child_id_fk;
ALTER TABLE cr_child_rels ADD CONSTRAINT cr_child_rels_child_id_fk
FOREIGN KEY (child_id) REFERENCES cr_items(item_id) ON DELETE CASCADE;

--
-- Add FK constraints for cr_item_rels with cascade operations
--
ALTER TABLE cr_item_rels DROP CONSTRAINT IF EXISTS cr_item_rels_item_id_fk;
ALTER TABLE cr_item_rels ADD CONSTRAINT cr_item_rels_item_id_fk
FOREIGN KEY (item_id) REFERENCES cr_items(item_id) ON DELETE CASCADE;

--
-- alter FK constraints to of context index and of acs_objects.context_id to cascade operations
--
ALTER TABLE acs_object_context_index DROP CONSTRAINT IF EXISTS acs_obj_context_idx_anc_id_fk;
ALTER TABLE acs_object_context_index ADD CONSTRAINT acs_obj_context_idx_anc_id_fk
FOREIGN KEY (ancestor_id) REFERENCES acs_objects(object_id) ON DELETE CASCADE;

ALTER TABLE acs_object_context_index DROP CONSTRAINT IF EXISTS acs_obj_context_idx_obj_id_fk;
ALTER TABLE acs_object_context_index ADD CONSTRAINT acs_obj_context_idx_obj_id_fk
FOREIGN KEY (object_id) REFERENCES acs_objects(object_id) ON DELETE CASCADE;

ALTER TABLE acs_objects DROP CONSTRAINT IF EXISTS acs_objects_context_id_fk;
ALTER TABLE acs_objects ADD CONSTRAINT acs_objects_context_id_fk
FOREIGN KEY (context_id) REFERENCES acs_objects(object_id) ON DELETE CASCADE;

--
-- Since acs_objects_context_id_fk cascades, there is no need for an
-- extra trigger
--
DROP TRIGGER IF EXISTS acs_objects_context_id_del_tr ON acs_objects;
DROP FUNCTION IF EXISTS acs_objects_context_id_del_tr();

--
-- alter FK constraints of symlinks to cascade
--
ALTER TABLE cr_symlinks DROP CONSTRAINT IF EXISTS cr_symlinks_target_id_fk;
ALTER TABLE cr_symlinks ADD CONSTRAINT cr_symlinks_target_id_fk
FOREIGN KEY (target_id) REFERENCES cr_items(item_id) ON DELETE CASCADE;

--
-- procedure content_item__del/1
--
CREATE OR REPLACE FUNCTION content_item__del(
   delete__item_id integer
) RETURNS integer AS $$
DECLARE
  v_revision_val record;
  v_child_val record;
BEGIN
  --
  -- Delete all revisions of this item
  --
  -- The following loop could be dropped / replaced by a cascade
  -- operation, when proper foreign keys are used along the
  -- inheritance path.
  --
  for v_revision_val in select revision_id 
                        from   cr_revisions
                        where  item_id = delete__item_id 
  LOOP
    PERFORM acs_object__delete(v_revision_val.revision_id);
  end loop;

  --
  -- Delete all children of this item via a recursive call.
  --
  -- The following loop is just needed to delete the revisions of
  -- child items. It could be removed, when proper foreign keys are
  -- used along the inheritance path of cr_content_revisions (which is
  -- not enforced and not always the case).
  --
  for v_child_val in select item_id
                      from   cr_items
                      where  parent_id = delete__item_id 
  LOOP     
     PERFORM content_item__delete(v_child_val.item_id);
  end loop; 

  --
  -- Finally, delete the acs_object of the item.
  --
  PERFORM acs_object__delete(delete__item_id);

  return 0; 
END;
$$ LANGUAGE plpgsql;


--
-- The content_search__dtrg tries to add entries on deletion the
-- search queue via
--
--      SELECT search_observer__enqueue(old.revision_id,'DELETE')
--
-- However, we do not need to queue the revision_id for deletion,
-- since the content-repository uses always the revision_id as
-- object_id. It might be a problem, if there would be a way to use
-- the item_id as object_id for search and to remove content based on
-- the revision_id, but this does not seem to be possible. If i am
-- wrong, one has to revive content_search__dtrg.
--
-- Since the search_observer_queue and txt have FK with cascades,
-- there is no need for an extra trigger
--
DROP TRIGGER IF EXISTS content_search__dtrg ON cr_revisions;
DROP FUNCTION IF EXISTS content_search__dtrg();