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

declare
  notify_assignee__task_id                alias for $1;  
  notify_assignee__user_id                alias for $2;  
  notify_assignee__callback               alias for $3;  
  notify_assignee__custom_arg             alias for $4;  
  v_deadline_pretty                       varchar;  
  v_object_name                           text; 
  v_transition_key                        wf_transitions.transition_key%TYPE;
  v_transition_name                       wf_transitions.transition_name%TYPE;
  v_party_from                            parties.party_id%TYPE;
  v_party_to                              parties.party_id%TYPE;
  v_subject                               text; 
  v_body                                  text; 
  v_request_id                            integer; 
  v_workflow_url			  text;      
  v_str                                   text;
begin
        select to_char(ta.deadline,'Mon fmDDfm, YYYY HH24:MI:SS'),
               acs_object__name(c.object_id),
               tr.transition_key,
               tr.transition_name
        into   v_deadline_pretty,
               v_object_name, 
               v_transition_key,
               v_transition_name
          from wf_tasks ta, wf_transitions tr, wf_cases c
         where ta.task_id = notify_assignee__task_id
           and c.case_id = ta.case_id
           and tr.workflow_key = c.workflow_key
           and tr.transition_key = ta.transition_key;

        select apm__get_value(p.package_id,'SystemURL') || site_node__url(s.node_id)
          into v_workflow_url
          from site_nodes s, 
               apm_packages a,
               (select package_id
                from apm_packages 
                where package_key = 'acs-kernel') p
         where s.object_id = a.package_id 
           and a.package_key = 'acs-workflow';

        /* Mail sent from */
          select wfi.principal_party
	    into v_party_from
            from wf_context_workflow_info wfi, wf_tasks ta, wf_cases c
           where ta.task_id = notify_assignee__task_id
             and c.case_id = ta.case_id
             and wfi.workflow_key = c.workflow_key
             and wfi.context_key = c.context_key;
        if NOT FOUND then
            v_party_from := -1;
        end if;

        /* Subject */
        v_subject := 'Assignment: ' || v_transition_name || ' ' || v_object_name;

        /* Body */
        v_body := 'You have been assigned to a task.
' || '
Case        : ' || v_object_name || '
Task        : ' || v_transition_name || '
';

        if v_deadline_pretty != '' and v_deadline_pretty is not null then
            v_body := v_body || 'Deadline    : ' || v_deadline_pretty || '
';
        end if;

	v_body := v_body ||'Task website: '||v_workflow_url||'task?task_id='||notify_assignee__task_id||'
';

        /* 
         * We would like to add a URL to go visit, but how do we get that URL?
         *
         * The notifications should really be sent from the application 
         * server layer, not from the database 
         */
    
        -- FIXME: last three args are also out varibles.

        if notify_assignee__callback != '' and notify_assignee__callback is not null then
            v_str :=  'select ' || notify_assignee__callback || ' (' || 
                      notify_assignee__task_id || ',' ||
                      coalesce(quote_literal(notify_assignee__custom_arg),'null') || 
                      ',' ||
                      notify_assignee__user_id || ',' ||
                      v_party_from || ',' ||
                      quote_literal(v_subject) || ',' ||
                      quote_literal(v_body) || ')';

            execute v_str;
        else
            v_request_id := acs_mail_nt__post_request (       
                v_party_from,                 -- party_from
                notify_assignee__user_id,     -- party_to
                'f',                        -- expand_group
                v_subject,                    -- subject
                v_body,                       -- message
                0                             -- max_retries
            );
        end if;

    return 0; 
end;$$ language plpgsql;