--
-- bcms/sql/postgresql/bcms-create.sql
--
-- @author
-- @create-date 10 Aug 2002
-- @cvs-id $Id: bcms-create.sql,v 1.4 2003/09/22 07:32:52 juny Exp $
--
-- creates bcms content types

-- TODO look into if we need to define the extra content types

-- this content type are just for convinience, bcms_page are the text pages, bcms_file are binary files
-- bcms_images are for images
--select content_type__create_type ('bcms_page', 'content_revision',	'BCMS Page', 'BCMS Pages', 'cr_bcms_pages', 'page_id', null);
--select content_type__create_type ('bcms_file', 'content_revision',	'BCMS File', 'BCMS Files', 'cr_bcms_files', 'file_id', null);

-- bcms_links are external links... href values
--select content_type__create_type ('bcms_link', 'content_revision',	'BCMS Link', 'BCMS Links', 'cr_bcms_links', 'link_id', null);

-- bcms_nav nav links are used to build the navigation
--select acs_object_type__create_type ('bcms_navlink', 'BCMS Navigation Link', 'BCMS Navigation Links', 'content_item', 'cr_bcms_navlinks', 'navlink_id', null, 'f', null, null);
--select acs_attribute__create_attribute ('bcms_navlink', 'nav_object_id', 'integer', 'Navigation Object ID', 'Navigation Object IDs', null, null, null, 1, 1, null, 'type_specific', 'f');
--select acs_attribute__create_attribute ('bcms_navlink', 'sort_key', 'integer', 'Sort Key', 'Sort Keys', null, null, null, 1, 1, null, 'type_specific', 'f');

-- create the relations, bcms_page may relate to another page, file, image
--select content_type__register_relation_type ('bcms_page', 'bcms_page', 'pages', '0', null);
--select content_type__register_relation_type ('bcms_page', 'bcms_file', 'files', '0', null);
--select content_type__register_relation_type ('bcms_page', 'image', 'images', '0', null);

create or replace function bcms__get_title(integer, varchar)
returns varchar as '
declare
    p_item_id           alias for $1;
    p_revision          alias for $2; -- either live or latest
    v_title             cr_revisions.title%TYPE;
    v_content_type      acs_objects.object_type%TYPE;
begin

-- lets determine the content type

    select object_type from acs_objects into v_content_type
        where object_id = p_item_id;

-- as of now only content_folder is treated differently

    if v_content_type = ''content_folder'' then

-- lets get the revision_id of what we are interested in
-- live or latest

        if p_revision = ''live'' then
            select r.title into v_title
                from cr_items i, cr_revisions r
            where i.parent_id = p_item_id
                and i.name = ''index''
                and r.revision_id = i.live_revision;
            return v_title;    
        end if;    
        if p_revision = ''latest'' then
            select r.title into v_title
                from cr_items i, cr_revisions r
            where i.parent_id = p_item_id
                and i.name = ''index''
                and r.revision_id = i.latest_revision;
            return v_title;    
        end if;    

    end if;


-- lets get the revision_id of what we are interested in
-- live or latest

    if p_revision = ''live'' then
        select r.title into v_title
            from cr_items i, cr_revisions r
        where i.item_id = p_item_id
            and r.revision_id = i.live_revision;
        return v_title;
    end if;    
    if p_revision = ''latest'' then
        select r.title into v_title
            from cr_items i, cr_revisions r
        where i.item_id = p_item_id
            and r.revision_id = i.latest_revision;
        return v_title;
    end if;    


end;
' language 'plpgsql';

	
-- patched function
create or replace function content_item__delete (integer)
returns integer as '
declare
  delete__item_id                alias for $1;  
  -- v_wf_cases_val                 record;
  v_symlink_val                  record;
  v_revision_val                 record;
  v_rel_val                      record;
begin

  -- Removed this as having workflow stuff in the CR is just plain wrong.
  -- DanW, Aug 25th, 2001.

  --   raise NOTICE ''Deleting associated workflows...'';
  -- 1) delete all workflow cases associated with this item
  --   for v_wf_cases_val in select
  --                           case_id
  --                         from
  --                           wf_cases
  --                         where
  --                           object_id = delete__item_id 
  --   LOOP
  --     PERFORM workflow_case__delete(v_wf_cases_val.case_id);
  --   end loop;

  raise NOTICE ''Deleting symlinks...'';
  -- 2) delete all symlinks to this item
  for v_symlink_val in select 
                         symlink_id
                       from 
                         cr_symlinks
                       where 
                         target_id = delete__item_id 
  LOOP
    PERFORM content_symlink__delete(v_symlink_val.symlink_id);
  end loop;

  raise NOTICE ''Unscheduling item...'';
  delete from cr_release_periods
    where item_id = delete__item_id;

  raise NOTICE ''Deleting associated revisions...'';
  -- 3) delete all revisions of this item
  delete from cr_item_publish_audit
    where item_id = delete__item_id;

  for v_revision_val in select
                          revision_id 
                        from
                          cr_revisions
                        where
                          item_id = delete__item_id 
  LOOP
    PERFORM acs_object__delete(v_revision_val.revision_id);
  end loop;
  
  raise NOTICE ''Deleting associated item templates...'';
  -- 4) unregister all templates to this item
  delete from cr_item_template_map
    where item_id = delete__item_id; 

  raise NOTICE ''Deleting item relationships...'';
  -- Delete all relations on this item
  for v_rel_val in select
                     rel_id
                   from
                     cr_item_rels
                   where
                     item_id = delete__item_id
                   or
                     related_object_id = delete__item_id 
  LOOP
    PERFORM acs_rel__delete(v_rel_val.rel_id);
  end loop;  

  raise NOTICE ''Deleting child relationships...'';
  for v_rel_val in select
                     rel_id
                   from
                     cr_child_rels
                   where
                     child_id = delete__item_id 
  LOOP
    PERFORM acs_rel__delete(v_rel_val.rel_id);
  end loop;  

  raise NOTICE ''Deleting parent relationships...'';
  for v_rel_val in select
                     rel_id, child_id
                   from
                     cr_child_rels
                   where
                     parent_id = delete__item_id 
  LOOP
    PERFORM acs_rel__delete(v_rel_val.rel_id);
    PERFORM content_item__delete(v_rel_val.child_id);
  end loop;  

  raise NOTICE ''Deleting associated permissions...'';
  -- 5) delete associated permissions
  delete from acs_permissions
    where object_id = delete__item_id;

  raise NOTICE ''Deleting keyword associations...'';
  -- 6) delete keyword associations
  delete from cr_item_keyword_map
    where item_id = delete__item_id;

  raise NOTICE ''Deleting associated comments...'';
  -- 7) delete associated comments
  PERFORM journal_entry__delete_for_object(delete__item_id);

  -- context_id debugging loop
  --for v_error_val in c_error_cur loop
  --  raise NOTICE ''ID='' || v_error_val.object_id || '' TYPE='' 
  --    || v_error_val.object_type);
  --end loop;

  raise NOTICE ''Deleting content item...'';
  delete from cr_items where item_id = delete__item_id;
  PERFORM acs_object__delete(delete__item_id);

  return 0; 
end;' language 'plpgsql';



create or replace function content_keyword__get_path_id (integer)
returns text as '
declare
  get_path__keyword_id            alias for $1;  
  v_path                          text default '''';
  v_is_found                      boolean default ''f'';   
  v_keyword_id                    cr_keywords.keyword_id%TYPE;
  v_rec                           record;
begin
  for v_rec in select keyword_id 
               from (select k2.keyword_id, tree_level(k2.tree_sortkey) as tree_level
                     from cr_keywords k1, cr_keywords k2
                     where k1.keyword_id = get_path__keyword_id
                       and k1.tree_sortkey between k2.tree_sortkey and tree_right(k2.tree_sortkey)) k
                order by tree_level
  LOOP
      v_keyword_id := v_rec.keyword_id;
      v_is_found := ''t'';
      v_path := v_path || ''/'' || v_keyword_id;
  end LOOP;

  if v_is_found = ''f'' then
    return null;
  else
    return v_path;
  end if;
 
end;' language 'plpgsql';