--
-- packages/message/sql/bboard-drop.sql
--
-- @author jmp@arsdigita.com
-- @creation-date 2000-08-31
-- @cvs-id $Id: bboard-drop.sql,v 1.1.1.1 2001/04/20 20:51:09 donb Exp $
--

-- This drop script destroys all messages that belong to forums.
-- It should handle any permission changes that have been done since
-- install by removing any permissions having to do with the standard
-- bboard permissions.

-- We need to get rid of things that might be referring to
-- bboard objects that would prevent removal.  This includes:
-- email-a-friend droppings
-- attachments (files and images)

-- if something else is pointing at one of our messages, this script
-- will fail and removing bboard will be icky.  this is sort of hairy
-- problem with unknown potential intra-package references.

declare
    image_p    integer;

    cursor children_cursor is
	select object_id as child_id, object_type
            from acs_objects
            where (object_type = 'acs_message' 
	          or object_type = 'content_item')
                  and context_id in (select distinct m.message_id
	                                 from acs_messages m, bboard_forum_message_map f
                                         where m.message_id = f.message_id);
begin
    for child_val in children_cursor loop
        if child_val.object_type = 'acs_message' then
   	    acs_message.delete(child_val.child_id);
	elsif child_val.object_type = 'content_item' then
	    select count(*) into image_p
                from images
                where image_id = child_val.child_id;
            if image_p = 1 then
                acs_message.delete_image(child_val.child_id);
            else
                acs_message.delete_file(child_val.child_id);
            end if;
        end if;
    end loop;
end;
/
show errors

-- Delete all messages that belong to forums
declare
    cursor message_cursor is
        select distinct m.message_id
            from acs_messages m, bboard_forum_message_map f
            where m.message_id = f.message_id;
begin
    for message_val in message_cursor loop
        acs_message.delete(message_val.message_id);
    end loop;
end;
/
show errors

-- Delete all subscriptions
delete from bboard_thread_subscribers;
delete from bboard_category_subscribers;
delete from bboard_forum_subscribers;

-- Delete all categories
delete from bboard_category_message_map;
delete from bboard_categories;
delete from acs_objects where object_type = 'bboard_category';

-- Delete all forums
delete from bboard_forum_message_map;
delete from bboard_forums;
delete from acs_objects where object_type = 'bboard_forum';

-- Drop all schema objects
drop package bboard_message;
drop package bboard_category;
drop package bboard_forum;
drop view bboard_messages_by_category;
drop view bboard_messages_all;
drop table bboard_thread_subscribers;
drop table bboard_category_subscribers;
drop table bboard_forum_subscribers;
drop table bboard_category_message_map;
drop table bboard_categories;
drop table bboard_forum_message_map;
drop table bboard_forums;

-- Drop object type metadata
begin
    acs_object_type.drop_type ('bboard_forum');
    acs_object_type.drop_type ('bboard_category');
end;
/
show errors

-- Drop permission metadata
delete from acs_permissions
    where privilege in
        ('bboard_create_forum', 'bboard_create_category',
         'bboard_create_message', 'bboard_write_forum',
         'bboard_write_category', 'bboard_write_message',
         'bboard_read_forum', 'bboard_read_category',
         'bboard_read_message', 'bboard_delete_forum',
         'bboard_delete_category', 'bboard_delete_message',
         'bboard_moderate_forum');

delete from acs_privilege_hierarchy
    where privilege in
        ('bboard_create_forum', 'bboard_create_category',
         'bboard_create_message', 'bboard_write_forum',
         'bboard_write_category', 'bboard_write_message',
         'bboard_read_forum', 'bboard_read_category',
         'bboard_read_message', 'bboard_delete_forum',
         'bboard_delete_category', 'bboard_delete_message',
         'bboard_moderate_forum');

delete from acs_privilege_hierarchy
    where child_privilege in
        ('bboard_create_forum', 'bboard_create_category',
         'bboard_create_message', 'bboard_write_forum',
         'bboard_write_category', 'bboard_write_message',
         'bboard_read_forum', 'bboard_read_category',
         'bboard_read_message', 'bboard_delete_forum',
         'bboard_delete_category', 'bboard_delete_message',
         'bboard_moderate_forum');

delete from acs_privileges
    where privilege in
        ('bboard_create_forum', 'bboard_create_category',
         'bboard_create_message', 'bboard_write_forum',
         'bboard_write_category', 'bboard_write_message',
         'bboard_read_forum', 'bboard_read_category',
         'bboard_read_message', 'bboard_delete_forum',
         'bboard_delete_category', 'bboard_delete_message',
         'bboard_moderate_forum');