-- 
-- packages/library/sql/library-drop.sql
--
-- Credit for the ACS 3 version of this module goes to:
-- @author Branimir Dolicki (branimir@arsdigita.com)
-- @author Carsten Clasohm (carsten@arsdigita.com)
-- @author Sarah Arnold (no idea)
--
-- The upgrade of this module to ACS 4 was done by
-- @author Dirk Gomez (dirk@arsdigita.com)
-- @author Jens Kordsmeier (jak@arsdigita.com)
-- in June/July 2000.
--
-- @creation-date 22-June 2001
-- @cvs-id $Id

-- Delete all library question objects and corresponding acs objects

-- declare
--     cursor c_question_id
--     is
--     select question_id
--     from sn_questions
--     where parent_id not in (select question_id from sn_questions);
-- begin	
--     for v_question_id in c_question_id loop
-- 	question.delete(v_question_id.question_id);
--     end loop;
-- end;
-- /
-- show errors

-- delete acs_permissions
-- Drop permission metadata
delete from acs_permissions
    where privilege in
        ('km_instance_admin','km_object_type_read',
         'km_object_type_create','km_object_read',
         'km_object_write','km_publish');

delete from acs_privilege_hierarchy
    where privilege in
        ('km_instance_admin','km_object_type_read',
         'km_object_type_create','km_object_read',
         'km_object_write','km_publish');

delete from acs_privilege_hierarchy
    where child_privilege in
        ('km_instance_admin','km_object_type_read',
         'km_object_type_create','km_object_read',
         'km_object_write','km_publish');

delete from acs_privileges
    where privilege in
        ('km_instance_admin','km_object_type_read',
         'km_object_type_create','km_object_read',
         'km_object_write','km_publish');


declare
    cursor c1 is
      select package_id from apm_packages where package_key='library';
begin
    for pac_rec in c1 loop
       object_type.delete_all_object_types(pac_rec.package_id);
    end loop;
end;
/

alter table sn_questions 
  drop constraint sn_questions_object_type_id_fk;
drop table psn_attachments;
drop table psn_category_trees;
drop table psn_res_application_roles;
drop table psn_res_applications;
drop table km_sweeper;
drop table km_sweeper_publisher;
drop table sn_object_archive_reasons;
drop table km_advanced_search_settings;
drop table km_library_searches;
drop table km_logger_table;
drop table km_flat_object_hierarchy;
drop table sn_question_link_map;
drop table sn_table_name_map;
drop table sn_access_counts;
drop table sn_object_option_map;
drop table sn_answer_options;
drop table sn_object_delete_reasons;
drop table km_object_archive_reasons;
drop table sn_audit_table;
drop table sn_content;
drop table sn_question_object_type_map;
drop table sn_objects;
drop table sn_object_types;
drop table sn_questions;
drop table km_path;

delete from bm_bookmarkable_types where object_type = 'sn_object';

delete from subsite_callbacks where object_type = 'sn_object';

delete from acs_objects_description where object_id in (select object_id from acs_objects where object_type='sn_object');

delete from acs_permissions where object_id in (select object_id from acs_objects where object_type='sn_object');

-- Yeah I know it's stupid.  But it works.
delete from acs_objects o where object_type='sn_object' and not exists (select 1 from acs_objects i where context_id = o.object_id);
delete from acs_objects o where object_type='sn_object' and not exists (select 1 from acs_objects i where context_id = o.object_id);
delete from acs_objects o where object_type='sn_object' and not exists (select 1 from acs_objects i where context_id = o.object_id);
delete from acs_objects o where object_type='sn_object' and not exists (select 1 from acs_objects i where context_id = o.object_id);
delete from acs_objects o where object_type='sn_object' and not exists (select 1 from acs_objects i where context_id = o.object_id);
delete from acs_objects o where object_type='sn_object' and not exists (select 1 from acs_objects i where context_id = o.object_id);
delete from acs_objects o where object_type='sn_object' and not exists (select 1 from acs_objects i where context_id = o.object_id);
delete from acs_objects o where object_type='sn_object' and not exists (select 1 from acs_objects i where context_id = o.object_id);
delete from acs_objects o where object_type='sn_object' and not exists (select 1 from acs_objects i where context_id = o.object_id);
delete from acs_objects o where object_type='sn_object' and not exists (select 1 from acs_objects i where context_id = o.object_id);

-- This is even more...
delete from acs_objects where object_type='sn_comment' and context_id in (select object_id from acs_objects where object_type='sn_object');

delete from acs_objects where object_type = 'sn_object_type';
delete from acs_objects where object_type = 'sn_question';
delete from acs_objects where object_type = 'psn_tree_object';
delete from acs_objects where object_type = 'psn_application';

begin
    acs_object_type.drop_type ('sn_object');
    acs_object_type.drop_type ('sn_object_type');
    acs_object_type.drop_type ('sn_question');
    acs_object_type.drop_type ('psn_tree_object');
    acs_object_type.drop_type ('psn_application');
end;
/
show errors

------------------------------------------------------------------------------
------------------------------------------------------------------------------
------------------------------------------------------------------------------
-- Those procedures will be deleted one after another once the analog ACS4
-- stuff is done.


drop table sn_links;
drop table sn_link_types;
drop sequence sn_links_seq;
drop sequence psn_attachment_id_seq;