--
-- bcms/sql/postgresql/bcms-create.sql
--
-- @author
-- @create-date 10 Aug 2002
-- @cvs-id $Id: bcms-create.sql,v 1.9 2003/10/30 15:08:11 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 ('content_revision', 'content_revision', 'pages', '0', null);
select content_type__register_relation_type ('content_revision', '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';

-- patched root folder with resolve_index fixed
create or replace function content_item__get_id (varchar,integer,boolean)
returns integer as '
declare
  get_id__item_path              alias for $1;  
  get_id__root_folder_id         alias for $2;  -- default null
  get_id__resolve_index          alias for $3;  -- default ''f''
  v_item_path                    varchar; 
  v_root_folder_id               cr_items.item_id%TYPE;
  get_id__parent_id              integer;       
  child_id                       integer;       
  start_pos                      integer default 1;        
  end_pos                        integer;       
  counter                        integer default 1;
  item_name                      varchar;  
begin

  v_root_folder_id := coalesce(get_id__root_folder_id, 
                               content_item_globals.c_root_folder_id);

  -- If the request path is the root, then just return the root folder
  if get_id__item_path = ''/'' then
    if get_id__resolve_index = ''t'' then

      -- if the item is a folder and has an index page, then return

      if content_folder__is_folder(v_root_folder_id) = ''t'' and
        content_folder__get_index_page(v_root_folder_id) is not null then 

        v_root_folder_id := content_folder__get_index_page(v_root_folder_id);
      end if;

    end if;
    return v_root_folder_id;
  end if;  

  -- Remove leading, trailing spaces, leading slashes
  v_item_path := rtrim(ltrim(trim(get_id__item_path), ''/''), ''/'');

  get_id__parent_id := v_root_folder_id;

  -- if parent_id is a symlink, resolve it
  get_id__parent_id := content_symlink__resolve(get_id__parent_id);

  LOOP

    end_pos := instr(v_item_path, ''/'', 1, counter);

    if end_pos = 0 then
      item_name := substr(v_item_path, start_pos);
    else
      item_name := substr(v_item_path, start_pos, end_pos - start_pos);
      counter := counter + 1;
    end if;

    select 
      item_id into child_id
    from 
      cr_items
    where
      parent_id = get_id__parent_id
    and
      name = item_name;

    if NOT FOUND then 
       return null;
    end if;

    exit when end_pos = 0;

    get_id__parent_id := child_id;

    -- if parent_id is a symlink, resolve it
    get_id__parent_id := content_symlink__resolve(get_id__parent_id);

    start_pos := end_pos + 1;
      
  end loop;

  if get_id__resolve_index = ''t'' then

    -- if the item is a folder and has an index page, then return

    if content_folder__is_folder(child_id ) = ''t'' and
      content_folder__get_index_page(child_id) is not null then 

      child_id := content_folder__get_index_page(child_id);
    end if;

  end if;

  return child_id;

end;' language 'plpgsql';

-- patched, there is a big problem when this function is called many times in a short time.
create or replace function content_item__get_path (integer,integer)
returns varchar as '
declare
  get_path__item_id                alias for $1;  
  get_path__root_folder_id         alias for $2;  -- default null  
  v_count                          integer;       
  v_resolved_root_id               integer;       
  v_path                           text    default '''';  
  v_rec                            record;
begin

  -- check that the item exists
  select count(*) into v_count from cr_items where item_id = get_path__item_id;

  if v_count = 0 then
    raise EXCEPTION ''-20000: Invalid item ID: %'', get_path__item_id;
  end if;

  -- begin walking down the path to the item (from the repository root)
 
  -- if the root folder is not null then prepare for a relative path

  if get_path__root_folder_id is not null then

    -- if root_folder_id is a symlink, resolve it (child items will point
    -- to the actual folder, not the symlink)

    v_resolved_root_id := content_symlink__resolve(get_path__root_folder_id);

    -- check to see if the item is under or out side the root_id
    PERFORM 1 from cr_items i, 
        (select tree_sortkey from cr_items where item_id = v_resolved_root_id) a
    where tree_ancestor_p(a.tree_sortkey, i.tree_sortkey) and i.item_id = get_path__item_id;

    if NOT FOUND then
        -- if not found then we need to go up the folder and append ../ until we have common ancestor

        for v_rec in select i1.name, i1.parent_id, tree_level(i1.tree_sortkey) as tree_level
                 from cr_items i1, (select tree_ancestor_keys(tree_sortkey) as tree_sortkey from cr_items where item_id = v_resolved_root_id) i2,
                 (select tree_sortkey from cr_items where item_id = get_path__item_id) i3
                 where 
                 i1.parent_id <> 0
                 and i2.tree_sortkey = i1.tree_sortkey
                 and not tree_ancestor_p(i2.tree_sortkey, i3.tree_sortkey)
                 order by tree_level desc
        LOOP
            v_path := v_path || ''../'';
        end loop;
        -- lets now assign the new root_id to be the last parent_id on the loop
        v_resolved_root_id := v_rec.parent_id;

    end if;

    -- go downwards the tree and append the name and /
    for v_rec in select i1.name, i1.item_id, tree_level(i1.tree_sortkey) as tree_level
             from cr_items i1, (select tree_sortkey from cr_items where item_id = v_resolved_root_id) i2,
            (select tree_ancestor_keys(tree_sortkey) as tree_sortkey from cr_items where item_id = get_path__item_id) i3
             where 
             i1.tree_sortkey = i3.tree_sortkey
             and i1.tree_sortkey > i2.tree_sortkey
             order by tree_level
    LOOP
        v_path := v_path || v_rec.name;
        if v_rec.item_id <> get_path__item_id then 
            -- put a / if we are still going down
            v_path := v_path || ''/'';
        end if;
    end loop;

  else

    -- this is an absolute path so prepend a ''/''
    -- loop over the absolute path

    for v_rec in select i2.name, tree_level(i2.tree_sortkey) as tree_level
                 from cr_items i1, cr_items i2
                 where i2.parent_id <> 0
                 and i1.item_id = get_path__item_id
                 and i1.tree_sortkey between i2.tree_sortkey and tree_right(i2.tree_sortkey)
                 order by tree_level
    LOOP
      v_path := v_path || ''/'' || v_rec.name;
    end loop;

  end if;

  return v_path;
 
end;' language 'plpgsql';