create or replace function content_item__get_content_type (integer)
returns varchar as '
declare
  get_content_type__item_id                alias for $1;  
  v_content_type                           cr_items.content_type%TYPE;
begin

  select
    content_type into v_content_type
  from 
    cr_items
  where 
    item_id = get_content_type__item_id;  

  return v_content_type;
 
end;' language 'plpgsql' stable strict;



create or replace function content_item__get_best_revision (integer)
returns integer as '
declare
  get_best_revision__item_id                alias for $1;  
  v_revision_id                             cr_revisions.revision_id%TYPE;
begin
    
  select
    coalesce(live_revision, latest_revision )
  into
    v_revision_id
  from
    cr_items
  where
    item_id = get_best_revision__item_id;

  return v_revision_id;
 
end;' language 'plpgsql' stable strict;


create or replace function content_item__get_context (integer)
returns integer as '
declare
  get_context__item_id                alias for $1;  
  v_context_id                        acs_objects.context_id%TYPE;
begin

  select
    context_id
  into
    v_context_id
  from
    acs_objects
  where
    object_id = get_context__item_id;

  if NOT FOUND then 
     raise EXCEPTION ''-20000: Content item % does not exist in content_item.get_context'', get_context__item_id;
  end if;

  return v_context_id;
 
end;' language 'plpgsql' stable;



create or replace function content_item__get_latest_revision (integer)
returns integer as '
declare
  get_latest_revision__item_id                alias for $1;
  v_revision_id                               integer;
  v_rec                                       record;
begin
  for v_rec in 
  select 
    r.revision_id 
  from 
    cr_revisions r, acs_objects o
  where 
    r.revision_id = o.object_id
  and 
    r.item_id = get_latest_revision__item_id
  order by 
    o.creation_date desc
  LOOP
      v_revision_id := v_rec.revision_id;
      exit;
  end LOOP;

  return v_revision_id;
 
end;' language 'plpgsql' strict stable;


create or replace function content_item__get_live_revision (integer)
returns integer as '
declare
  get_live_revision__item_id                alias for $1;  
  v_revision_id                             acs_objects.object_id%TYPE;
begin

  select
    live_revision into v_revision_id
  from
    cr_items
  where
    item_id = get_live_revision__item_id;

  return v_revision_id;
 
end;' language 'plpgsql' stable strict;


-- there was an infinite loop in content_item.get_parent_folder if called with 
-- a child content_item rather than a content item which was directly below a 
-- folder.

create or replace function content_item__get_parent_folder (integer)
returns integer as '
declare
  get_parent_folder__item_id               alias for $1;  
  v_folder_id                              cr_folders.folder_id%TYPE;
  v_parent_folder_p                        boolean default ''f'';       
begin
  v_folder_id := get_parent_folder__item_id;

  while NOT v_parent_folder_p and v_folder_id is not null LOOP

    select
      parent_id, content_folder__is_folder(parent_id) 
    into 
      v_folder_id, v_parent_folder_p
    from
      cr_items
    where
      item_id = v_folder_id;

  end loop; 

  return v_folder_id;
 
end;' language 'plpgsql' stable strict;


create or replace function content_item__get_publish_date (integer,boolean)
returns timestamptz as '
declare
  get_publish_date__item_id                alias for $1;  
  get_publish_date__is_live                alias for $2;  -- default ''f''  
  v_revision_id                            cr_revisions.revision_id%TYPE;
  v_publish_date                           cr_revisions.publish_date%TYPE;
begin

  if get_publish_date__is_live then
    select
	publish_date into v_publish_date
    from
	cr_revisions r, cr_items i
    where
      i.item_id = get_publish_date__item_id
    and
      r.revision_id = i.live_revision;
  else
    select
	publish_date into v_publish_date
    from
	cr_revisions r, cr_items i
    where
      i.item_id = get_publish_date__item_id
    and
      r.revision_id = i.latest_revision;
  end if;

  return v_publish_date;
 
end;' language 'plpgsql' stable;

create or replace function content_item__get_publish_date (integer,boolean)
returns timestamptz as '
declare
  get_publish_date__item_id                alias for $1;  
  get_publish_date__is_live                alias for $2;  -- default ''f''  
  v_revision_id                            cr_revisions.revision_id%TYPE;
  v_publish_date                           cr_revisions.publish_date%TYPE;
begin

  if get_publish_date__is_live then
    select
	publish_date into v_publish_date
    from
	cr_revisions r, cr_items i
    where
      i.item_id = get_publish_date__item_id
    and
      r.revision_id = i.live_revision;
  else
    select
	publish_date into v_publish_date
    from
	cr_revisions r, cr_items i
    where
      i.item_id = get_publish_date__item_id
    and
      r.revision_id = i.latest_revision;
  end if;

  return v_publish_date;
 
end;' language 'plpgsql' stable;


-- This used to have a pretty gross loop and sort.
--
create or replace function content_item__is_subclass (varchar,varchar)
returns boolean as '
declare
  is_subclass__object_type            alias for $1;  
  is_subclass__supertype              alias for $2;  
  v_subclass_p                        boolean;      
  v_inherit_val                       record;
begin
  select count(*) > 0 into v_subclass_p where exists (
	select 1
          from acs_object_types o, acs_object_types o2
         where o2.object_type = is_subclass__supertype
           and o.object_type = is_subclass__object_type
           and o.tree_sortkey between o2.tree_sortkey and tree_right(o2.tree_sortkey));

  return v_subclass_p;

end;' language 'plpgsql' stable;



create or replace function content_item__is_publishable (integer)
returns boolean as '
declare
  is_publishable__item_id                alias for $1;  
  v_child_count                          integer;       
  v_rel_count                            integer;       
  v_template_id                          cr_templates.template_id%TYPE;
  v_child_type                           record;
  v_rel_type                             record;
  v_content_type			 varchar;
  -- v_pub_wf                               record;
begin
  -- check valid item_id
  select content_item__get_content_type(is_publishable__item_id) into v_content_type;

  if v_content_type is null then 
	raise exception ''content_item__is_publishable item_id % invalid'',is_publishable__item_id;
  end if;

  -- validate children
  -- make sure the # of children of each type fall between min_n and max_n
  for v_child_type in select
                        child_type, min_n, max_n
                      from
                        cr_type_children
                      where
                        parent_type = v_content_type 
	                and (min_n is not null or max_n is not null)
  LOOP
    select
      count(rel_id) into v_child_count
    from
      cr_child_rels
    where
      parent_id = is_publishable__item_id
    and
      content_item__get_content_type(child_id) = v_child_type.child_type;

    -- make sure # of children is in range
    if v_child_type.min_n is not null 
      and v_child_count < v_child_type.min_n then
      return ''f'';
    end if;
    if v_child_type.max_n is not null
      and v_child_count > v_child_type.max_n then
      return ''f'';
    end if;

  end LOOP;

  -- validate relations
  -- make sure the # of ext links of each type fall between min_n and max_n
  -- only check if one of min_n max_n not null
  for v_rel_type in select
                      target_type, min_n, max_n
                    from
                      cr_type_relations
                    where
                      content_type = v_content_type
		      and (max_n is not null or min_n is not null)
  LOOP
    select
      count(rel_id) into v_rel_count
    from
      cr_item_rels i, acs_objects o
    where
      i.related_object_id = o.object_id
    and
      i.item_id = is_publishable__item_id
    and
      coalesce(content_item__get_content_type(o.object_id),o.object_type) = v_rel_type.target_type;
      
    -- make sure # of object relations is in range
    if v_rel_type.min_n is not null 
      and v_rel_count < v_rel_type.min_n then
      return ''f'';
    end if;
    if v_rel_type.max_n is not null 
      and v_rel_count > v_rel_type.max_n then
      return ''f'';
    end if;
  end loop;

  -- validate publishing workflows
  -- make sure any ''publishing_wf'' associated with this item are finished
  -- KG: logic is wrong here.  Only the latest workflow matters, and even
  -- that is a little problematic because more than one workflow may be
  -- open on an item.  In addition, this should be moved to CMS.
  
  -- Removed this as having workflow stuff in the CR is just plain wrong.
  -- DanW, Aug 25th, 2001.

  --   for v_pub_wf in  select
  --                      case_id, state
  --                    from
  --                      wf_cases
  --                    where
  --                      workflow_key = ''publishing_wf''
  --                    and
  --                      object_id = is_publishable__item_id
  -- 
  --   LOOP
  --     if v_pub_wf.state != ''finished'' then
  --        return ''f'';
  --     end if;
  --   end loop;

  -- if NOT FOUND then 
  --   return ''f'';
  -- end if;

  return ''t'';
 
end;' language 'plpgsql' stable;



create or replace function content_item__move (integer,integer)
returns integer as '
declare
  move__item_id                alias for $1;  
  move__target_folder_id       alias for $2;  
begin

  if move__target_folder_id is null then 
	raise exception ''attempt to move item_id % to null folder_id'', move__item_id;
  end if;

  if content_folder__is_folder(move__item_id) = ''t'' then

    PERFORM content_folder__move(move__item_id, move__target_folder_id);

  else if content_folder__is_folder(move__target_folder_id) = ''t'' then
   

    if content_folder__is_registered(move__target_folder_id,
          content_item__get_content_type(move__item_id),''f'') = ''t'' and
       content_folder__is_registered(move__target_folder_id,
          content_item__get_content_type(content_symlink__resolve(move__item_id)),''f'') = ''t''
      then

    -- update the parent_id for the item
    update cr_items 
      set parent_id = move__target_folder_id
      where item_id = move__item_id;
    end if;

  end if; end if;

  return 0; 
end;' language 'plpgsql';



create or replace function content_revision__copy_attributes (varchar,integer,integer)
returns integer as '
declare
  copy_attributes__content_type           alias for $1;  
  copy_attributes__revision_id            alias for $2;  
  copy_attributes__copy_id                alias for $3;  
  v_table_name                            acs_object_types.table_name%TYPE;
  v_id_column                             acs_object_types.id_column%TYPE;
  cols                                    varchar default ''''; 
  attr_rec                                record;
begin

  if copy_attributes__content_type is null or copy_attributes__revision_id is null or copy_attributes__copy_id is null then 
     raise exception ''content_revision__copy_attributes called with null % % %'',copy_attributes__content_type,copy_attributes__revision_id, copy_attributes__copy_id;
  end if;

  select table_name, id_column into v_table_name, v_id_column
  from acs_object_types where object_type = copy_attributes__content_type;

  for attr_rec in select
                    attribute_name
                  from
                    acs_attributes
                  where
                    object_type = copy_attributes__content_type 
  LOOP
    cols := cols || '', '' || attr_rec.attribute_name;
  end loop;

  execute ''insert into '' || v_table_name || '' select '' || copy_attributes__copy_id || 
          '' as '' || v_id_column || cols || '' from '' || 
          v_table_name || '' where '' || v_id_column || '' = '' || 
          copy_attributes__revision_id;
  
  return 0; 
end;' language 'plpgsql';

create or replace function content_keyword__is_assigned (integer,integer,varchar)
returns boolean as '
declare
  is_assigned__item_id                alias for $1;  
  is_assigned__keyword_id             alias for $2;  
  is_assigned__recurse                alias for $3;  -- default ''none''  
  v_ret                               boolean;    
  v_is_assigned__recurse	      varchar;
begin
  if is_assigned__recurse is null then 
	v_is_assigned__recurse := ''none'';
  else
      	v_is_assigned__recurse := is_assigned__recurse;	
  end if;

  -- Look for an exact match
  if v_is_assigned__recurse = ''none'' then
      return count(*) > 0 from cr_item_keyword_map
       where item_id = is_assigned__item_id
         and keyword_id = is_assigned__keyword_id;
  end if;

  -- Look from specific to general
  if v_is_assigned__recurse = ''up'' then
      return count(*) > 0
      where exists (select 1
                    from (select keyword_id from cr_keywords c, cr_keywords c2
	                  where c2.keyword_id = is_assigned__keyword_id
                            and c.tree_sortkey between c2.tree_sortkey and tree_right(c2.tree_sortkey)) t,
                      cr_item_keyword_map m
                    where t.keyword_id = m.keyword_id
                      and m.item_id = is_assigned__item_id);
  end if;

  if v_is_assigned__recurse = ''down'' then
      return count(*) > 0
      where exists (select 1
                    from (select k2.keyword_id
                          from cr_keywords k1, cr_keywords k2
                          where k1.keyword_id = is_assigned__keyword_id
                            and k1.tree_sortkey between k2.tree_sortkey and tree_right(k2.tree_sortkey)) t, 
                      cr_item_keyword_map m
                    where t.keyword_id = m.keyword_id
                      and m.item_id = is_assigned__item_id);

  end if;  

  -- Tried none, up and down - must be an invalid parameter
  raise EXCEPTION ''-20000: The recurse parameter to content_keyword.is_assigned should be \\\'none\\\', \\\'up\\\' or \\\'down\\\''';
  
  return null;
end;' language 'plpgsql' stable;


create or replace function content_folder__is_registered (integer,varchar,boolean)
returns boolean as '
declare
  is_registered__folder_id              alias for $1;  
  is_registered__content_type           alias for $2;  
  is_registered__include_subtypes       alias for $3;  -- default ''f''  
  v_is_registered                       integer;
  v_subtype_val                         record;
begin

  if is_registered__include_subtypes = ''f'' or  is_registered__include_subtypes is null then
    select 
      count(1)
    into 
      v_is_registered
    from
      cr_folder_type_map
    where
      folder_id = is_registered__folder_id
    and
      content_type = is_registered__content_type;

  else
--                         select
--                            object_type
--                          from 
--                            acs_object_types
--                          where 
--                            object_type <> ''acs_object''
--                          connect by 
--                            prior object_type = supertype
--                          start with 
--                            object_type = is_registered.content_type 

    v_is_registered := 1;
    for v_subtype_val in select o.object_type
                         from acs_object_types o, acs_object_types o2
                         where o.object_type <> ''acs_object''
                           and o2.object_type = is_registered__content_type
                           and o.tree_sortkey between o2.tree_sortkey and tree_right(o2.tree_sortkey)
                         order by o.tree_sortkey
    LOOP
      if content_folder__is_registered(is_registered__folder_id,
                       v_subtype_val.object_type, ''f'') = ''f'' then
        v_is_registered := 0;
      end if;
    end loop;
  end if;

  if v_is_registered = 0 then
    return ''f'';
  else
    return ''t'';
  end if;
 
end;' language 'plpgsql' stable;


create or replace function content_revision__content_copy (integer,integer)
returns integer as '
declare
  content_copy__revision_id            alias for $1;  
  content_copy__revision_id_dest       alias for $2;  -- default null  
  v_item_id                            cr_items.item_id%TYPE;
  v_content_length                     cr_revisions.content_length%TYPE;
  v_revision_id_dest                   cr_revisions.revision_id%TYPE;
  v_content                            cr_revisions.content%TYPE;
  v_lob                                cr_revisions.lob%TYPE;
  v_new_lob                            cr_revisions.lob%TYPE;
  v_storage_type                       varchar;
begin
  if content_copy__revision_id is null then 
	raise exception ''content_revision__content_copy attempt to copy a null revision_id'';
  end if;

  select
    content_length, item_id
  into
    v_content_length, v_item_id
  from
    cr_revisions
  where
    revision_id = content_copy__revision_id;

  -- get the destination revision
  if content_copy__revision_id_dest is null then
    select
      latest_revision into v_revision_id_dest
    from
      cr_items
    where
      item_id = v_item_id;
  else
    v_revision_id_dest := content_copy__revision_id_dest;
  end if;


  -- only copy the content if the source content is not null
  if v_content_length is not null and v_content_length > 0 then

    /* The internal LOB types - BLOB, CLOB, and NCLOB - use copy semantics, as 
       opposed to the reference semantics which apply to BFILEs.
       When a BLOB, CLOB, or NCLOB is copied from one row to another row in 
       the same table or in a different table, the actual LOB value is
       copied, not just the LOB locator. */

    select r.content, r.content_length, r.lob, i.storage_type 
      into v_content, v_content_length, v_lob, v_storage_type
      from cr_revisions r, cr_items i 
     where r.item_id = i.item_id 
       and r.revision_id = content_copy__revision_id;

    if v_storage_type = ''lob'' then
        v_new_lob := empty_lob();

        update cr_revisions
           set content = null,
               content_length = v_content_length,
               lob = v_new_lob
         where revision_id = v_revision_id_dest;
        PERFORM lob_copy(v_lob, v_new_lob);
    else 
        -- this will work for both file and text types... well sort of.
        -- this really just creates a reference to the first file which is
        -- wrong since, the item_id, revision_id uniquely describes the 
        -- location of the file in the content repository file system.  
        -- after copy is called, the content attribute needs to be updated 
        -- with the new relative file path:

        -- update cr_revisions
        -- set content = ''[cr_create_content_file $item_id $revision_id [cr_fs_path]$old_rel_path]''
        -- where revision_id = :revision_id
        
        -- old_rel_path is the content attribute value of the content revision
        -- that is being copied.
        update cr_revisions
           set content = v_content,
               content_length = v_content_length,
               lob = null
         where revision_id = v_revision_id_dest;
    end if;

  end if;

  return 0; 
end;' language 'plpgsql';

create or replace function content_type__get_template (varchar,varchar)
returns integer as '
declare
  get_template__content_type           alias for $1;  
  get_template__use_context            alias for $2;  
  v_template_id                        cr_templates.template_id%TYPE;
begin
  select
    template_id
  into
    v_template_id
  from
    cr_type_template_map
  where
    content_type = get_template__content_type
  and
    use_context = get_template__use_context
  and
    is_default = ''t'';

  return v_template_id;
 
end;' language 'plpgsql' stable strict;


create or replace function content_type__trigger_insert_statement (varchar)
returns varchar as '
declare
  trigger_insert_statement__content_type   alias for $1;  
  v_table_name                             acs_object_types.table_name%TYPE;
  v_id_column                              acs_object_types.id_column%TYPE;
  cols                                     varchar default '''';
  vals                                     varchar default '''';
  attr_rec                                 record;
begin
  if trigger_insert_statement__content_type is null then 
	return exception ''content_type__trigger_insert_statement called with null content_type'';
  end if;

  select 
    table_name, id_column into v_table_name, v_id_column
  from 
    acs_object_types 
  where 
    object_type = trigger_insert_statement__content_type;

  for attr_rec in select
                    attribute_name
                  from
                    acs_attributes
                  where
                    object_type = trigger_insert_statement__content_type 
  LOOP
    cols := cols || '', '' || attr_rec.attribute_name;
    vals := vals || '', new.'' || attr_rec.attribute_name;
  end LOOP;

  return ''insert into '' || v_table_name || 
    '' ( '' || v_id_column || cols || '' ) values (cr_dummy.val'' ||
    vals || '')'';
  
end;' language 'plpgsql' stable;


create or replace function rule_exists (varchar,varchar) returns boolean as '
declare
        rule_name       alias for $1;
        table_name      alias for $2;
begin
        return count(*) = 1
          from pg_rules
         where tablename::varchar = lower(table_name)
           and rulename::varchar = lower(rule_name);

end;' language 'plpgsql' stable;