--
-- content_item__get_id/3
--
create or replace function content_item__get_id(
  character varying,
  integer,
  boolean
) returns int4 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

  if get_id__root_folder_id is null then
    select c_root_folder_id from content_item_globals into v_root_folder_id;
  else
    v_root_folder_id := get_id__root_folder_id;
  end if;

  -- If the request path is the root, then just return the root folder
  if get_id__item_path = '/' then
    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;