--
-- content_item__is_publishable/1
--
create or replace function content_item__is_publishable(
  is_publishable__item_id integer
) returns bool as $$

DECLARE
  v_child_count                          integer;       
  v_rel_count                            integer;       
  v_content_type			 varchar;
  v_template_id                          cr_templates.template_id%TYPE;
  v_child_type                           record;
  v_rel_type                             record;
  -- 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(item_id) into v_child_count
    from   cr_items
    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;