-- @author jennie@ybos.net
-- @author wirth@ybos.net

-- dropping permissions
delete from acs_permissions
   where privilege in
      ('mp3_view_mp3','mp3_create_mp3','mp3_delete_mp3','mp3_modify_mp3',
       'mp3_view_playlist','mp3_create_playlist','mp3_delete_playlist',
       'mp3_modify_playlist','mp3_admin_mp3','mp3_admin_playlist',
       'mp3_admin');

delete from acs_privilege_hierarchy
    where privilege in
      ('mp3_view_mp3','mp3_create_mp3','mp3_delete_mp3','mp3_modify_mp3',
       'mp3_view_playlist','mp3_create_playlist','mp3_delete_playlist',
       'mp3_modify_playlist','mp3_admin_mp3','mp3_admin_playlist',
       'mp3_admin');

delete from acs_privilege_hierarchy
    where child_privilege in
      ('mp3_view_mp3','mp3_create_mp3','mp3_delete_mp3','mp3_modify_mp3',
       'mp3_view_playlist','mp3_create_playlist','mp3_delete_playlist',
       'mp3_modify_playlist','mp3_admin_mp3','mp3_admin_playlist',
       'mp3_admin');

delete from acs_privileges
    where privilege in
      ('mp3_view_mp3','mp3_create_mp3','mp3_delete_mp3','mp3_modify_mp3',
       'mp3_view_playlist','mp3_create_playlist','mp3_delete_playlist',
       'mp3_modify_playlist','mp3_admin_mp3','mp3_admin_playlist',
       'mp3_admin');


declare
   cursor play_cur is
     select playlist_id from mp3_playlists;
   cursor mp3_cur is
     select mp3_id from mp3_mp3s;
begin
   for play_rec in play_cur loop
     mp3_playlist.del(play_rec.playlist_id);
   end loop;
   for mp3_rec in mp3_cur loop
     mp3.del(mp3_rec.mp3_id);
   end loop;
end;
/
show errors

drop package mp3_playlist;
drop package mp3;
drop view mp3_playlist_song_count;
drop view mp3_mp3s_not_deleted;
drop view mp3_mp3_playlist_map_view;
drop table mp3_mp3_playlist_map;
drop table mp3_mp3_stats;
drop table mp3_playlist_stats;
drop table mp3_votes;
drop table mp3_playlists;
drop table mp3_mp3s;

begin
    acs_object_type.drop_type ('mp3_playlist');
    acs_object_type.drop_type ('mp3');
end;
/
show errors