--
-- workflow_case__new/7
--
create or replace function workflow_case__new(
  integer,
  character varying,
  character varying,
  integer,
  timestamp with time zone,
  integer,
  character varying
) returns int4 as $$

declare
  new__case_id                alias for $1;  -- default null  
  new__workflow_key           alias for $2;  
  new__context_key            alias for $3;  -- default null
  new__object_id              alias for $4;  
  new__creation_date          alias for $5;  -- default now()
  new__creation_user          alias for $6;  -- default null
  new__creation_ip            alias for $7;  -- default null
  v_case_id                   integer;
  v_workflow_case_table       varchar;
  v_context_key_for_query     varchar;
begin
        if new__context_key = '' or new__context_key is null then
            v_context_key_for_query := 'default';
        else
            v_context_key_for_query := new__context_key;
        end if;

        /* insert a row into acs_objects */
        v_case_id := acs_object__new(
            new__case_id,
            new__workflow_key,
            new__creation_date,
            new__creation_user,
            new__creation_ip,
            null
        );

        /* insert the case in to the general wf_cases table */
        insert into wf_cases 
            (case_id, workflow_key, context_key, object_id, state)
        values 
            (v_case_id, new__workflow_key, v_context_key_for_query, new__object_id, 'created');
            
        /* insert the case into the workflow-specific cases table */
        select table_name into v_workflow_case_table
        from   acs_object_types
        where  object_type = new__workflow_key;

        execute 'insert into ' || v_workflow_case_table || ' (case_id) values (' || v_case_id || ')';

        return v_case_id;
     
end;$$ language plpgsql;