--
-- packages/ams/sql/postgresql/ams-drop.sql
--
-- @author Matthew Geddert openacs@geddert.com
-- @creation-date 2004-09-07
-- @cvs-id $Id: ams-drop.sql,v 1.3 2004/10/27 02:04:18 matthewg Exp $
--
--


select content_folder__delete (folder_id, 't') 
  from cr_folders
 where label = 'AMS Objects'
   and description = 'AMS Object Repository';

delete from cr_folder_type_map where content_type = 'ams_object_revision';
delete from acs_attribute_descriptions where description_key = 'ams_attribute_description';

select drop_package('ams_option');
select drop_package('ams_attribute');
select drop_package('ams_attribute_value');
select drop_package('ams_object_revision');

drop function ams_object__new (integer,integer,timestamptz,integer,varchar);
drop function ams_object_id (integer);
-- select drop_package('ams_object_id');
select drop_package('ams_list');


drop sequence ams_options_seq;
drop sequence ams_option_map_id_seq;
drop sequence ams_list_attribute_sort_order_seq;

drop table ams_list_attribute_map;
drop table ams_lists;
drop view ams_object_revisionsx;
drop view ams_object_revisionsi;
drop table ams_object_revisions cascade;
drop table ams_objects cascade;

create or replace function inline_1 ()
returns varchar as '
declare
        rec                RECORD;
begin

        FOR rec IN 
                select address_id
                  from ams_attribute_values
                 where address_id is not null
        LOOP
                delete from ams_attribute_values where address_id = rec.address_id;
                PERFORM postal_address__del (rec.address_id); 
        END LOOP;

        return ''All Postal Addresses associated with AMS have been deleted'';
end;' language 'plpgsql';

select inline_1() as Notice;
drop function inline_1();

create or replace function inline_2 ()
returns varchar as '
declare
        rec                RECORD;
begin

        FOR rec IN 
                select number_id
                  from ams_attribute_values
                 where number_id is not null
        LOOP
                delete from ams_attribute_values where number_id = rec.number_id;
                PERFORM telecom_number__del (rec.number_id); 
        END LOOP;

        
        return ''All Telecom Numbers Addresses associated with AMS have been deleted'';
end;' language 'plpgsql';

select inline_2() as Notice;
drop function inline_2();



-- select acs_object__delete(address_id) from ams_attribute_values where address_id is not null;
-- select acs_object__delete(number_id) from ams_attribute_values where number_id is not null;


drop table ams_attribute_values cascade;
drop table ams_option_map cascade;
drop table ams_option_map_ids cascade;
drop table ams_options cascade;
drop table ams_attributes cascade;
drop table ams_widgets cascade;
drop table ams_storage_types cascade;
update cr_items set live_revision = null, latest_revision = null where content_type = 'ams_object_revision';
delete from cr_revisions where item_id in ( select item_id from cr_items where content_type = 'ams_object_revision' );
delete from cr_items where content_type = 'ams_object_revision';
delete from acs_objects where object_type in ('ams_list','ams_object_revision','ams_attribute');
select acs_object_type__drop_type('ams_list','f');
select acs_object_type__drop_type('ams_object_revision','f');
select acs_object_type__drop_type('ams_attribute','f');