-- API for Mailing lists

create or replace function ml_mailing_list__new (integer,integer,text,varchar,text,text,text,text,varchar,text,text,varchar,text,text,varchar,timestamptz,integer,integer,text,integer,integer,timestamptz,varchar)
returns integer as '
declare
	new__list_id		alias for $1;	-- default null
	new__package_id		alias for $2;	-- default null
	new__name		alias for $3;
	new__locale		alias for $4;
	new__teaser		alias for $5;
	new__sender_email	alias for $6;   -- default null
	new__confirm_subject	alias for $7;	-- default null
	new__confirm_body	alias for $8;	-- default null
	new__confirm_mime_type 	alias for $9; 	-- default ''text/plain''
	new__welcome_subject	alias for $10;	-- default null
	new__welcome_body	alias for $11;  -- default null
	new__welcome_mime_type 	alias for $12; 	-- default ''text/plain''
	new__remind_subject	alias for $13;	-- default null
	new__remind_body	alias for $14;  -- default null
	new__remind_mime_type 	alias for $15; 	-- default ''text/plain''
	new__expiration_date	alias for $16;	-- default sysdate
	new__first_reminder	alias for $17;	-- default 7
	new__second_reminder 	alias for $18;	-- default 30
	new__comments		alias for $19;	-- default null
	new__context_id		alias for $20;	-- default null
	new__creation_user	alias for $21;	-- default null
	new__creation_date	alias for $22;	-- default sysdate
	new__creation_ip	alias for $23;	-- default null

	v_list_id	ml_mailing_lists.list_id%TYPE;
begin

                v_list_id := acs_object__new (
                        new__list_id,
                        ''mailing_list'',
                        new__creation_date,
                        new__creation_user,
                        new__creation_ip,
                        new__context_id
                );

		insert into ml_mailing_lists
		       (list_id, package_id, name, locale, teaser, sender_email,
		       welcome_subject, welcome_body, welcome_mime_type,
		       confirm_subject, confirm_body, confirm_mime_type,
		       remind_subject, remind_body, remind_mime_type,
		       expiration_date, first_reminder, second_reminder, comments)
		values
		       (v_list_id, new__package_id, new__name, new__locale, new__teaser, new__sender_email,
		       new__welcome_subject, new__welcome_body, new__welcome_mime_type,
		       new__confirm_subject, new__confirm_body, new__confirm_mime_type,
		       new__remind_subject, new__remind_body, new__remind_mime_type,
		       new__expiration_date, new__first_reminder, new__second_reminder, new__comments);

		return v_list_id;
	
end;' language 'plpgsql';




create or replace function ml_mailing_list__delete (integer)
returns integer as '
declare
	delete__list_id		alias for $1;
begin
	delete from ml_mailing_lists where list_id = delete__list_id;
	perform acs_object__delete(delete__list_id);

	return 0;
end;' language 'plpgsql';

create or replace function ml_mailing_list__edit (integer,text,varchar,text,text,text,text,varchar,text,text,varchar,text,text,varchar,timestamptz,integer,integer,text,integer,varchar)
returns integer as '
declare
	 edit__list_id		alias for $1;	-- default null
	 edit__name		alias for $2;
	 edit__locale		alias for $3;
	 edit__teaser		alias for $4;	
	 edit__sender_email		alias for $5;	-- default null
	 edit__confirm_subject 	alias for $6;	-- default null
	 edit__confirm_body		alias for $7;	-- default null
	 edit__confirm_mime_type	alias for $8;	-- default ''text/plain''
	 edit__welcome_subject	alias for $9;	-- default null
	 edit__welcome_body		alias for $10;	-- default null,
	 edit__welcome_mime_type	alias for $11; 	-- default ''text/plain''
	 edit__remind_subject	alias for $12;	-- default null
	 edit__remind_body		alias for $13;	-- default null,
	 edit__remind_mime_type	alias for $14; 	-- default ''text/plain''
	 edit__expiration_date	alias for $15;	-- default sysdate
	 edit__first_reminder		alias for $16;	-- default 7
	 edit__second_reminder	alias for $17;	-- default 30
	 edit__comments		alias for $18;	-- default null
	 edit__modifying_user		alias for $19;	-- default null
	 edit__modifying_ip		alias for $20;	-- default null
begin
		update ml_mailing_lists
		set name = edit__name,
		    locale = edit__locale,
		    teaser = edit__teaser,
		    sender_email = edit__sender_email,
		    confirm_subject = edit__confirm_subject,
		    confirm_body = edit__confirm_body,
		    confirm_mime_type = edit__confirm_mime_type,
		    welcome_subject = edit__welcome_subject,
		    welcome_body = edit__welcome_body,
		    welcome_mime_type = edit__welcome_mime_type,
		    remind_subject = edit__remind_subject,
		    remind_body = edit__remind_body,
		    remind_mime_type = edit__remind_mime_type,
		    expiration_date = edit__expiration_date,
		    first_reminder = edit__first_reminder,
		    second_reminder = edit__second_reminder,
		    comments = edit__comments
		where list_id = edit__list_id;

		update acs_objects
		set modifying_user = edit__modifying_user,
		    modifying_ip = edit__modifying_ip
		where object_id = edit__list_id;

	return 0;

end;' language 'plpgsql';


create or replace function ml_mailing_list__name (integer)
returns varchar as '
declare
	name__list_id	alias for $1;
	v_name	ml_mailing_lists.name%TYPE;
begin
		select into v_name name
		from ml_mailing_lists
		where list_id = name__list_id;

		return v_name;
end;' language 'plpgsql';


-- API for Mailing Classes


create or replace function ml_mail_class__new (integer,integer,text,varchar,varchar,text,boolean,text,boolean,text,boolean,text,boolean,text,boolean,text,boolean,text,boolean,varchar,text,integer,integer,timestamptz,varchar)
returns integer as '
declare
	new__mail_class_id	alias for $1;	-- default null
	new__package_id		alias for $2;	-- default null,
	new__name		alias for $3;	-- default null,
	new__locale		alias for $4;	-- default null,
	new__sender_email	alias for $5;	-- default null,
	new__subject		alias for $6;	-- default null,
	new__subject_change_p	alias for $7;	-- default ''t'',
	new__text_header	alias for $8;	-- default null,
	new__text_header_change_p alias for $9;	-- default ''t'',
	new__text_body		alias for $10;	-- default null,
	new__text_body_change_p	alias for $11;	-- default ''t'',
	new__text_footer	alias for $12;	-- default null,
	new__text_footer_change_p alias for $13; -- default ''t'',
	new__html_header	alias for $14;	-- default null,
	new__html_header_change_p alias for $15; -- default ''t'',
	new__html_body		alias for $16;	-- default null,
	new__html_body_change_p	alias for $17;	-- default ''t'',
	new__html_footer	alias for $18;	-- default null,
	new__html_footer_change_p alias for $19; -- default ''t'',
	new__mime_type		alias for $20;	-- default ''text/plain'',
	new__comments	 	alias for $21;	-- default null,
	new__context_id		alias for $22;	-- default null,
	new__creation_user	alias for $23;	-- default null,
	new__creation_date	alias for $24;	-- default sysdate,
	new__creation_ip	alias for $25;	-- default null

	v_class_id	ml_mail_classes.mail_class_id%TYPE;

begin
	v_class_id := acs_object__new (
		   new__mail_class_id,
                   ''mail_class'',
                   new__creation_date,
                   new__creation_user,
                   new__creation_ip,
                   new__context_id
	);

	insert into ml_mail_classes
	       (mail_class_id, package_id, name, locale, sender_email,
	       subject, subject_change_p, text_header, text_header_change_p,
	       text_body, text_body_change_p, text_footer, text_footer_change_p,
	       html_header, html_header_change_p, html_body, html_body_change_p,
	       html_footer, html_footer_change_p, mime_type, comments)
	values
	       (v_class_id, new__package_id, new__name, new__locale, new__sender_email,
		new__subject, new__subject_change_p, new__text_header, new__text_header_change_p,
		new__text_body, new__text_body_change_p, new__text_footer, new__text_footer_change_p,
		new__html_header, new__html_header_change_p, new__html_body, new__html_body_change_p,
		new__html_footer, new__html_footer_change_p, new__mime_type, new__comments);

	return v_class_id;

end;' language 'plpgsql';


create or replace function ml_mail_class__delete (integer)
returns integer as '
declare
	delete__mail_class_id	alias for $1;
	v_attachment	cr_items%ROWTYPE;
        v_mail_job      acs_objects%ROWTYPE;
begin
        for v_attachment in select item_id from cr_items
                            where parent_id = delete__mail_class_id
        loop
            perform content_item__delete(v_attachment.item_id);
        end loop;

        update ml_mail_jobs set mail_class_id = NULL where mail_class_id = delete__mail_class_id;

	for v_mail_job in select object_id from acs_objects
                          where context_id = delete__mail_class_id and
                                object_type = ''mail_job''
        loop
            update acs_objects set context_id = (
                   select package_id from ml_mail_jobs
                   where mail_job_id = v_mail_job.object_id)
            where object_id = v_mail_job.object_id;
        end loop;

	delete from ml_mail_classes where mail_class_id = delete__mail_class_id;
	perform acs_object__delete(delete__mail_class_id);

	return 0;

end;' language 'plpgsql';



create or replace function ml_mail_class__edit (integer,text,varchar,varchar,text,boolean,text,boolean,text,boolean,text,boolean,text,boolean,text,boolean,text,boolean,varchar,text,integer,varchar)
returns integer as '
declare
	edit__mail_class_id	alias for $1;	-- default null
	edit__name		alias for $2;	-- default null,
	edit__locale		alias for $3;	-- default null,
	edit__sender_email	alias for $4;	-- default null,
	edit__subject		alias for $5;	-- default null,
	edit__subject_change_p	alias for $6;	-- default ''t'',
	edit__text_header	alias for $7;	-- default null,
	edit__text_header_change_p alias for $8; -- default ''t'',
	edit__text_body		alias for $9;	-- default null,
	edit__text_body_change_p alias for $10;	-- default ''t'',
	edit__text_footer	alias for $11;	-- default null,
	edit__text_footer_change_p alias for $12; -- default ''t'',
	edit__html_header	alias for $13;	-- default null,
	edit__html_header_change_p alias for $14; -- default ''t'',
	edit__html_body		alias for $15;	-- default null,
	edit__html_body_change_p alias for $16;	-- default ''t'',
	edit__html_footer	alias for $17;	-- default null,
	edit__html_footer_change_p alias for $18; -- default ''t'',
	edit__mime_type		alias for $19;	-- default ''text/plain'',
	edit__comments	 	alias for $20;	-- default null,
	edit__modifying_user	alias for $21;	-- default null,
	edit__modifying_ip	alias for $22;	-- default null
begin
		update ml_mail_classes
		set name = edit__name,
		    locale = edit__locale,
		    sender_email = edit__sender_email,
		    subject = edit__subject,
		    subject_change_p = edit__subject_change_p,
		    text_header = edit__text_header,
		    text_header_change_p = edit__text_header_change_p,
		    text_body = edit__text_body,
		    text_body_change_p = edit__text_body_change_p,
		    text_footer = edit__text_footer,
		    text_footer_change_p = edit__text_footer_change_p,
		    html_header = edit__html_header,
		    html_header_change_p = edit__html_header_change_p,
		    html_body = edit__html_body,
		    html_body_change_p = edit__html_body_change_p,
		    html_footer = edit__html_footer,
		    html_footer_change_p = edit__html_footer_change_p,
		    mime_type = edit__mime_type,
		    comments = edit__comments
		where mail_class_id = edit__mail_class_id;

		update acs_objects
		set modifying_user = edit__modifying_user,
		    modifying_ip = edit__modifying_ip
		where object_id = edit__mail_class_id;

		return 0;

end;' language 'plpgsql';


create or replace function ml_mail_class__name (integer)
returns varchar as '
declare
	name__mail_class_id	alias for $1;
	v_name	ml_mail_classes.name%TYPE;
begin
	select into v_name name
	from ml_mail_classes
	where mail_class_id = name__mail_class_id;

	return v_name;

end;' language 'plpgsql';



-- API for Mailing List Jobs

create or replace function ml_mail_job__new (integer,integer,integer,integer,varchar,varchar,integer,varchar,varchar,boolean,text,text,text,text,text,text,text,boolean,varchar,varchar,timestamptz,text,integer,integer,timestamptz,varchar)
returns integer as '
declare
         new__mail_job_id       alias for $1; -- default null
         new__mail_class_id     alias for $2;
         new__list_id           alias for $3; -- default null
         new__selection_id      alias for $4; -- default null
         new__category_ids      alias for $5; -- default null
         new__category_join     alias for $6; -- default null
         new__package_id        alias for $7; -- default null
         new__locale            alias for $8;	
         new__sender_email      alias for $9; -- default null
         new__track_links_p     alias for $10; -- default ''f''
         new__subject           alias for $11; -- default null
         new__text_header       alias for $12; -- default null
         new__text_body         alias for $13; -- default null
         new__text_footer       alias for $14; -- default null
         new__html_header       alias for $15; -- default null
         new__html_body         alias for $16; -- default null
         new__html_footer       alias for $17; -- default null
         new__template_p        alias for $18; -- default ''f''
         new__mime_type         alias for $19; -- default ''text/plain''
         new__state             alias for $20; -- default ''active''
         new__scheduled_date    alias for $21; -- default sysdate
         new__bind_vars         alias for $22; -- default null
         new__context_id        alias for $23; -- default null
         new__creation_user     alias for $24; -- default null
         new__creation_date     alias for $25; -- default sysdate
         new__creation_ip       alias for $26; -- default null

         v_mail_job_id  ml_mail_jobs.mail_job_id%TYPE;
         v_revision_id  cr_revisions.revision_id%TYPE;
begin
         v_mail_job_id := acs_object__new (
              new__mail_job_id,
              ''mail_job'',
              new__creation_date,
              new__creation_user,
              new__creation_ip,
              new__context_id
         );

         select into v_revision_id live_revision
         from cr_items
         where parent_id = new__mail_class_id;

         IF NOT FOUND THEN
              v_revision_id := null;
         END IF;

         insert into ml_mail_jobs
            (mail_job_id, list_id, selection_id, category_ids, category_join, package_id, locale,
             sender_email, track_links_p, subject, text_header, text_body,
             text_footer, html_header, html_body, html_footer,
             template_p, mime_type, state, scheduled_date,
             css_revision_id, mail_class_id, bind_vars)
         values
             (v_mail_job_id, new__list_id, new__selection_id, new__category_ids, new__category_join, new__package_id, new__locale,
              new__sender_email, new__track_links_p, new__subject, new__text_header, new__text_body,
              new__text_footer, new__html_header, new__html_body, new__html_footer,
              new__template_p, new__mime_type, new__state, new__scheduled_date,
              v_revision_id, new__mail_class_id, new__bind_vars);

         if (new__selection_id is not null) then
              insert into ml_mail_job_bind_vars
                 (select v_mail_job_id as mail_job_id, b.name, b.description,
                         b.default_value as value
                  from us_bind_vars b
                  where b.selection_id = new__selection_id);
         end if;

         return v_mail_job_id;

end;' language 'plpgsql';




create or replace function ml_mail_job__delete (integer)
returns integer as '
declare
	delete__mail_job_id	alias for $1;
	v_attachment	cr_items%ROWTYPE;
begin
	for v_attachment in select item_id from cr_items
			where parent_id = delete__mail_job_id
	loop
		perform content_item__delete(item_id);
	end loop;

	delete from ml_mail_jobs where mail_job_id = delete__mail_job_id;
	perform	acs_object__delete(delete__mail_job_id);

	return 0;

end;' language 'plpgsql';



create or replace function ml_mail_job__edit (integer,varchar,varchar,boolean,text,text,text,text,text,text,text,boolean,varchar,varchar,timestamptz,text,integer,varchar)
returns integer as '
declare
	 edit__mail_job_id	alias for $1;
	 edit__locale		alias for $2;	
	 edit__sender_email	alias for $3; -- default null
	 edit__track_links_p	alias for $4; -- default ''f''
	 edit__subject		alias for $5; -- default null
	 edit__text_header	alias for $6; -- default null
	 edit__text_body	alias for $7; -- default null
	 edit__text_footer	alias for $8; -- default null
	 edit__html_header	alias for $9; -- default null
	 edit__html_body	alias for $10; -- default null
	 edit__html_footer	alias for $11; -- default null
	 edit__template_p	alias for $12; -- default ''f''
	 edit__mime_type	alias for $13; -- default ''text/plain''
	 edit__state		alias for $14; -- default ''active''
	 edit__scheduled_date	alias for $15; -- default sysdate
	 edit__bind_vars	alias for $16; -- default null
	 edit__modifying_user	alias for $17; -- default null
	 edit__modifying_ip	alias for $18; -- default null
begin

		update ml_mail_jobs
		set locale = edit__locale,
		    sender_email = edit__sender_email,
		    track_links_p = edit__track_links_p,
		    subject = edit__subject,
		    text_header = edit__text_header,
		    text_body = edit__text_body,
		    text_footer = edit__text_footer,
		    html_header = edit__html_header,
		    html_body = edit__html_body,
		    html_footer = edit__html_footer,
		    template_p = edit__template_p,
		    mime_type = edit__mime_type,
		    state = edit__state,
		    scheduled_date = edit__scheduled_date,
		    bind_vars = edit__bind_vars
		where mail_job_id = edit__mail_job_id
		and state <> ''done'';

		update acs_objects
		set modifying_user = edit__modifying_user,
		    modifying_ip = edit__modifying_ip
		where object_id = edit__mail_job_id;

		return 0;

end;' language 'plpgsql';

create or replace function ml_mail_job__name (integer)
returns varchar as '
declare
	name__mail_job_id	alias for $1;
	v_name	ml_mail_jobs.subject%TYPE;
begin
		select into v_name subject
		from ml_mail_jobs
		where mail_job_id = name__mail_job_id;

		return v_name;
end;' language 'plpgsql';