-- Classified Ads Package
--
-- @author Deds Castillo (deds@infiniteinfo.com)
-- @creation-date 2002-10-08
-- @cvs-id $Id: cr-classifieds-drop.sql,v 1.1 2003/07/16 04:52:31 rmello Exp $
--

create function inline_0 ()
returns integer as'
declare
    v_package_key           apm_packages.package_key%TYPE;
    v_name                  cr_items.name%TYPE;
    v_label                 cr_folders.label%TYPE;
    v_folder_description    cr_folders.description%TYPE;
    v_heading               cr_keywords.heading%TYPE;
    v_keyword_description   cr_keywords.description%TYPE;
    v_folder_id             cr_folders.folder_id%TYPE;
    v_keyword_id            cr_keywords.keyword_id%TYPE;
    v_rec                   record;
begin

-- these are magic values
    v_package_key := ''classified-ads'';
    v_name := v_package_key;
    v_label := v_package_key;
    v_folder_description := v_package_key || '' Root Folder, all classified ads items go here'';
    v_heading := v_package_key || ''-main-category'';
    v_keyword_description := v_heading || '' Main Category, this is where all classified ads categories are rooted'';

-- get the root keyword id for classified ads
    select keyword_id into v_keyword_id
    from cr_keywords
    where heading = v_heading and
          description = v_keyword_description;

-- loop through all classified ads child keywords in cr_keywords and delete them
    for v_rec in
        select c2.keyword_id
        from cr_keywords c1, cr_keywords c2
        where c1.keyword_id = v_keyword_id and
              c2.tree_sortkey between tree_left(c1.tree_s\ortkey) and tree_right(c1.tree_sortkey)
        order by c2.tree_sortkey desc
    LOOP
        PERFORM content_keyword__delete (v_rec.keyword_id);
    end LOOP;

-- delete the root keyword for classified ads
    PERFORM content_keyword__delete (v_keyword_id);

-- get the root folder id for classified ads
    select folder_id into v_folder_id
    from cr_folders f, cr_items i
    where i.name = v_name and
          f.label = v_label and
          f.description = v_folder_description;

    
-- delete all folders
    for v_rec in
        select a2.object_id
        from acs_objects a1, acs_objects a2
        where a1.object_id = v_folder_id and
              a2.tree_sortkey between a1.tree_sortkey and tree_right(a1.tree_sortkey) and
              a2.object_type = ''content_folder''
        order by a2.tree_sortkey desc
    LOOP
        PERFORM content_folder__delete (v_rec.object_id);
    end LOOP;


    return null;
end;' language 'plpgsql';

select inline_0();
drop function inline_0();