--
-- content_item__relate/5
--
create or replace function content_item__relate(
  integer,
  integer,
  character varying,
  integer,
  character varying
) returns int4 as $$

declare
  relate__item_id                alias for $1;  
  relate__object_id              alias for $2;  
  relate__relation_tag           alias for $3;  -- default 'generic'  
  relate__order_n                alias for $4;  -- default null
  relate__relation_type          alias for $5;  -- default 'cr_item_rel'
  v_content_type                 cr_items.content_type%TYPE;
  v_object_type                  acs_objects.object_type%TYPE;
  v_is_valid                     integer;       
  v_rel_id                       integer;       
  v_exists                       integer;       
  v_order_n                      cr_item_rels.order_n%TYPE;
begin

  -- check the relationship is valid
  v_content_type := content_item__get_content_type (relate__item_id);
  v_object_type := content_item__get_content_type (relate__object_id);

  select
    count(1) into v_is_valid
  from
    cr_type_relations
  where
    content_item__is_subclass( v_object_type, target_type ) = 't'
  and
    content_item__is_subclass( v_content_type, content_type ) = 't';

  if v_is_valid = 0 then
    raise EXCEPTION '-20000: There is no registered relation type matching this item relation.';
  end if;

  if relate__item_id != relate__object_id then
    -- check that these two items are not related already
    --dbms_output.put_line( 'checking if the items are already related...');
    
    select
      rel_id, 1 into v_rel_id, v_exists
    from
      cr_item_rels
    where
      item_id = relate__item_id
    and
      related_object_id = relate__object_id
    and
      relation_tag = relate__relation_tag;

    if NOT FOUND then
       v_exists := 0;
    end if;
    
    -- if order_n is null, use rel_id (the order the item was related)
    if relate__order_n is null then
      v_order_n := v_rel_id;
    else
      v_order_n := relate__order_n;
    end if;


    -- if relationship does not exist, create it
    if v_exists <> 1 then
      --dbms_output.put_line( 'creating new relationship...');
      v_rel_id := acs_object__new(
        null,
        relate__relation_type,
        now(),
        null,
        null,
        relate__item_id
      );
      insert into cr_item_rels (
        rel_id, item_id, related_object_id, order_n, relation_tag
      ) values (
        v_rel_id, relate__item_id, relate__object_id, v_order_n, 
        relate__relation_tag
      );

    -- if relationship already exists, update it
    else
      --dbms_output.put_line( 'updating existing relationship...');
      update cr_item_rels set
        relation_tag = relate__relation_tag,
        order_n = v_order_n
      where
        rel_id = v_rel_id;
    end if;

  end if;

  return v_rel_id;
 
end;$$ language plpgsql;