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

declare
	p_party_from		alias for $1;
	p_party_to			alias for $2;
	p_subject			alias for $3;
	p_message			alias for $4;
begin
	return acs_mail_nt__post_request(
		p_party_from,		-- p_party_from
		p_party_to,			-- p_party_to
		'f',				-- p_expand_group
		p_subject,			-- p_subject
		p_message,			-- p_message
		0					-- p_max_retries
	);
end;$$ language plpgsql;


--
-- acs_mail_nt__post_request/6
--
create or replace function acs_mail_nt__post_request(
  integer,
  integer,
  boolean,
  character varying,
  text,
  integer
) returns int4 as $$

declare
	p_party_from		alias for $1;
	p_party_to			alias for $2;
	p_expand_group		alias for $3;	-- default 'f'
	p_subject			alias for $4;
	p_message			alias for $5;
	p_max_retries		alias for $6;	-- default 0
	v_header_from		acs_mail_bodies.header_from%TYPE;
	v_header_to			acs_mail_bodies.header_to%TYPE;
	v_body_id			acs_mail_bodies.body_id%TYPE;
	v_item_id			cr_items.item_id%TYPE;
	v_revision_id		cr_revisions.revision_id%TYPE;
	v_message_id		acs_mail_queue_messages.message_id%TYPE;
	v_header_to_rec		record;
	v_creation_user		acs_objects.creation_user%TYPE;
begin
	if p_max_retries <> 0 then
	   raise EXCEPTION ' -20000: max_retries parameter not implemented.';
	end if;

	-- get the sender email address
	select max(email) into v_header_from from parties where party_id = p_party_from;

	-- if sender address is null, then use site default OutgoingSender
	if v_header_from is null then
	   	select apm__get_value(package_id, 'OutgoingSender') into v_header_from
		from apm_packages where package_key='acs-kernel';
	end if;

	-- make sure that this party is in users table. If not, let creation_user
	-- be null to prevent integrity constraint violations on acs_objects
	select max(user_id) into v_creation_user 
      from users where user_id = p_party_from;

	-- get the recipient email address
	select max(email) into v_header_to from parties where party_id = p_party_to;

	-- do not let any of these addresses be null
	if v_header_from is null or v_header_to is null then
	   raise EXCEPTION ' -20000: acs_mail_nt: cannot sent email to blank address or from blank address.';
	end if;

	-- create a mail body with empty content

	select acs_mail_body__new (
		null,				   -- p_body_id
		null,				   -- p_body_reply_to
		p_party_from,		   -- p_body_from
		now(),				   -- p_body_date
		null,				   -- p_header_message_id
		null,				   -- p_header_reply_to
		p_subject,			   -- p_header_subject
		null,				   -- p_header_from
		null,				   -- p_header_to
		null,				   -- p_content_item_id
		'acs_mail_body',	   -- p_object_type
		now(),				   -- p_creation_date
		v_creation_user,	   -- p_creation_user
		null,				   -- p_creation_ip
		null				   -- p_context_id
	) into v_body_id;

	-- create a CR item to stick p_message into

	select content_item__new(
        'acs-mail message' || v_body_id,	-- new__name
        null,					-- new__parent_id
        p_subject,				-- new__title
        null,					-- new__description
        p_message				-- new__text
	) into v_item_id;

	-- content_item__new makes a CR revision. We need to get that revision
	-- and make it live

	select content_item__get_latest_revision (v_item_id) into v_revision_id ;
	perform content_item__set_live_revision ( v_revision_id );

	-- set the content of the message
	perform acs_mail_body__set_content_object( v_body_id, v_item_id );

	-- queue the message

	select acs_mail_queue_message__new (
		null,					-- p_mail_link_id
		v_body_id,				-- p_body_id
		null,					-- p_context_id
		now(),					-- p_creation_date
		v_creation_user,		-- p_creation_user
		null,					-- p_creation_ip
		'acs_mail_link'		-- p_object_type
	) into v_message_id;

	-- now put the message into the outgoing queue
	-- i know this seems redundant, but that's the way it was built
	-- the idea is that you put a generic message into the main queue
	-- without from or to address, and then insert a copy of the message
	-- into the outgoing_queue with the specific from and to address

    if p_expand_group = 'f' then
		insert into acs_mail_queue_outgoing
		( message_id, envelope_from, envelope_to )
		values
		( v_message_id, v_header_from, v_header_to );

	else
		-- expand the group
		-- FIXME: need to check if this is a group and if there are members
		--        if not, do we need to notify sender?

		for v_header_to_rec in 
			select email from parties p 
			where party_id in (select member_id from group_approved_member_map 
								where group_id = p_party_to) loop

			insert into acs_mail_queue_outgoing
			( message_id, envelope_from, envelope_to )
			values
			( v_message_id, v_header_from, v_header_to_rec.email );
		end loop;

    end if;

	return v_message_id;
end;$$ language plpgsql;