-- postgresql/photo-album-drop.sql

create function inline_0 () 
returns integer as '
declare
  priv_rec RECORD;
begin
  for priv_rec in select object_id, grantee_id, privilege
    from acs_permissions 
    where privilege = ''pa_create_photo''
    loop
      PERFORM acs_permission__revoke_permission (
        priv_rec.object_id,
	priv_rec.grantee_id,
	priv_rec.privilege
      );
    end loop;
    return 1;
end; ' language 'plpgsql';

select inline_0 ();

drop function inline_0 ();

create function inline_0 () 
returns integer as '
declare
  priv_rec RECORD;
begin
  for priv_rec in select object_id, grantee_id, privilege
    from acs_permissions 
    where privilege = ''pa_create_album''
    loop
      PERFORM acs_permission__revoke_permission (
        priv_rec.object_id,
	priv_rec.grantee_id,
	priv_rec.privilege
      );
    end loop;
    return 1;
end; ' language 'plpgsql';

select inline_0 ();

drop function inline_0 ();

create function inline_0 () 
returns integer as '
declare
  priv_rec RECORD;
begin
  for priv_rec in select object_id, grantee_id, privilege
    from acs_permissions 
    where privilege = ''pa_create_folder''
    loop
      PERFORM acs_permission__revoke_permission (
        priv_rec.object_id,
	priv_rec.grantee_id,
	priv_rec.privilege
      );
    end loop;
    return 1;
end; ' language 'plpgsql';

select inline_0 ();

drop function inline_0 ();

-- kill stuff in permissions.sql
select acs_privilege__remove_child('create', 'pa_create_photo');
select acs_privilege__remove_child('create', 'pa_create_album');
select acs_privilege__remove_child('create', 'pa_create_folder');

select acs_privilege__drop_privilege('pa_create_album');
select acs_privilege__drop_privilege('pa_create_folder');
select acs_privilege__drop_privilege('pa_create_photo');

-- pa_image changed to generic image content_type

select content_type__unregister_child_type (
 'pa_album',
 'pa_photo',
  'generic'
);

select content_type__unregister_child_type (
 'pa_photo',
 'image',
 'generic'
);

-- clear out all the reference that cause key violations when dropping type

-- delete images
-- now that pa_image is just image
-- the query needs to be adjusted to be specific to photo-album
-- this needs to be standardized with content-repository (clearing out files)
-- there isn't currently a image__delete function
-- so this bit won't work
create function inline_0 () 
returns integer as '
declare
  image_rec RECORD;
begin
  for image_rec in select i.item_id
    from cr_items i, cr_child_rels rels,
    cr_items i2
    where i.content_type = ''image''
    and i2.content_type = ''pa_photo''
    and rels.child_id = i.item_id
    and rels.parent_id = i2.item_id
    loop
      PERFORM image__delete (image_rec.item_id);
    end loop;
    return 1;
end; ' language 'plpgsql';

select inline_0 ();

drop function inline_0 ();

-- delete photos
create function inline_0 () 
returns integer as '
declare
  pa_photo_rec RECORD;
begin
  for pa_photo_rec in select item_id
    from cr_items
    where content_type = ''pa_photo''
    loop
      PERFORM pa_photo__delete (pa_photo_rec.item_id);
    end loop;
    return 1;
end; ' language 'plpgsql';

select inline_0 ();

drop function inline_0 ();

-- delete albums
create function inline_0 () 
returns integer as '
declare
  pa_album_rec RECORD;
begin
  for pa_album_rec in select item_id
    from cr_items
    where content_type = ''pa_album''
    loop
      PERFORM pa_album__delete (pa_album_rec.item_id);
    end loop;
    return 1;
end; ' language 'plpgsql';

select inline_0 ();

drop function inline_0 ();

create function inline_0 () returns integer as '
declare
  folder_rec RECORD;
begin
  for folder_rec in select folder_id, content_type
    from cr_folder_type_map where content_type = ''pa_album''
    loop
      PERFORM content_folder__unregister_content_type (
        folder_rec.folder_id,
	folder_rec.content_type,
        ''t''
      );
    end loop;
    return 1;
end; ' language 'plpgsql';

select inline_0 ();

drop function inline_0 ();

-- drop package photo_album;
drop function photo_album__new_root_folder (integer);
drop function photo_album__get_root_folder (integer);

-- this needs to drop the pa_album__ functions
-- drop package pa_album;
drop function pa_album__delete_revision (integer);
drop function pa_album__delete (integer);
drop function pa_album__new (varchar, integer, integer, boolean, integer, varchar, varchar, varchar, text, integer, timestamp, varchar, integer, timestamp, varchar);

-- this needs to drop the pa_photo__ functions
-- drop package pa_photo;  
drop function pa_photo__delete (integer);
drop function pa_photo__delete_revision (integer);
drop function pa_photo__new (varchar,integer,integer,integer,timestamp, integer, varchar, varchar, integer, varchar, varchar, boolean, timestamp, varchar, varchar, text);

-- these drop tables as well
select content_type__drop_type('pa_photo','f','t');

select content_type__drop_type('pa_album','f','t');

-- delete all the folders under the root folders of photo album instances
-- replacing a connect by with the tree_sort business
create function inline_0 () returns integer as '
declare
  folder_rec RECORD;
begin
  for folder_rec in select i1.item_id, tree_level(i1.tree_sortkey) - tree_level(i2.tree_sortkey) as level 
                      from cr_items i1, cr_items i2
                     where i1.tree_sortkey between i2.tree_sortkey and tree_right(i2.tree_sortkey)
                           and i2.item_id in (select folder_id from pa_package_root_folder_map)
                  order by i2.item_id, level desc;
    loop
      if folder_rec.level = 0 then
        -- folder is a root folder, delete it from maping table to avoid fk constraint violation
        delete from pa_package_root_folder_map where folder_id = folder_rec.folder_id;
      end if;
      PERFORM content_folder__delete (folder_rec.folder_id);
    end loop;
    return 0;
end; ' language 'plpgsql';

select inline_0 ();

drop function inline_0 ();
  

drop table pa_package_root_folder_map;