-- use package vars for bounce manager (#of bounced emails to mark user
-- as bouncing, #of days of last mail sent unbounced so that bounce
-- history of user is deleted) and upper limit of attachment size

begin
          -- create the object types
 
        acs_object_type.create_type (
                supertype       =>      'acs_object',
                object_type     =>      'mailing_list',
                pretty_name     =>      'Mailing List',
                pretty_plural   =>      'Mailing Lists',
                table_name      =>      'ml_mailing_lists',
                id_column       =>      'list_id',
                name_method     =>      'ml_mailing_list.name'
        );
        acs_object_type.create_type (
                supertype       =>      'acs_object',
                object_type     =>      'mail_class',
                pretty_name     =>      'Mailing Class',
                pretty_plural   =>      'Mailing Classes',
                table_name      =>      'ml_mail_classes',
                id_column       =>      'mail_class_id',
                name_method     =>      'ml_mail_class.name'
        );
        acs_object_type.create_type (
                supertype       =>      'acs_object',
                object_type     =>      'mail_job',
                pretty_name     =>      'Mailing Job',
                pretty_plural   =>      'Mailing Jobs',
                table_name      =>      'ml_mail_jobs',
                id_column       =>      'mail_job_id',
                name_method     =>      'ml_mail_job.name'
        );
end;
/
show errors

begin
    -- create the privileges
    acs_privilege.create_privilege('mailing_list_admin', 'Mailing Lists Administrator');

    acs_privilege.add_child('admin','mailing_list_admin');
end;
/
show errors

create table ml_mailing_lists (
       list_id		     integer
       			     constraint ml_mailing_lists_pk
			     primary key,
       package_id	     integer
       			     constraint ml_mailing_lists_pck_id_fk
			     references apm_packages,
       name		     varchar2(1000)
       			     constraint ml_mailing_lists_name_nn
			     not null,
       locale		     varchar2(5)
       			     constraint ml_mailing_lists_locale_nn
			     not null
			     constraint ml_mail_lists_locale_fk
			     references ad_locales,
       teaser		     clob,
       public_p		     char(1) default 't'
       			     constraint ml_lists_public_p_ck
			     check (public_p in ('t','f')),
       sender_email	     varchar2(1000),
       confirm_subject	     varchar2(1000),
       confirm_body	     clob,
       confirm_mime_type     varchar2(30) default 'text/plain',
       welcome_subject	     varchar2(1000),
       welcome_body	     clob,
       welcome_mime_type     varchar2(30) default 'text/plain',
       remind_subject	     varchar2(1000),
       remind_body	     clob,
       remind_mime_type      varchar2(30) default 'text/plain',
       expiration_date	     date,
       -- amount of days after sending registration email
       first_reminder	     integer default 7,
       -- 0 if no second reminder
       second_reminder	     integer default 30,
       comments		     clob
);


-- target, distribution and campaign: use categories (will be posted)

create table ml_mailing_list_user_map (
       list_id			   integer
       				   constraint ml_m_l_user_map_list_fk
				   references ml_mailing_lists
				   on delete cascade,
       user_id			   integer
       				   constraint ml_m_l_user_map_user_fk
       				   references users
				   on delete cascade,
       subscription_date	   date default sysdate,
       unsubscription_date	   date,
       subscribed_p		   char(1) default 't' 
       				   constraint ml_user_map_subscr_p_ck
				   check (subscribed_p in ('t','f')),
       confirmed_p		   char(1) default 't'
       				   constraint ml_user_map_confirm_p_ck
				   check (confirmed_p in ('t','f')),
       reminder_count		   integer default 0,
       constraint ml_mailing_list_user_map_pk
       primary key (list_id, user_id)
);

-- to speed up queries to get all lists for a user
create unique index ml_mailing_list_users_ix on ml_mailing_list_user_map (user_id, list_id);
create index ml_mail_list_users_subsc_p_ix on ml_mailing_list_user_map(subscribed_p);


create table ml_mail_classes (
       mail_class_id         integer
       			     constraint ml_mail_classes_pk
			     primary key,
       package_id	     integer
       			     constraint ml_mail_classes_pck_id_fk
			     references apm_packages,
       name		     varchar2(1000)
       			     constraint ml_mail_classes_name_nn
			     not null,
       locale		     varchar2(5)
       			     constraint ml_mail_classes_locale_nn
			     not null
			     constraint ml_mail_classes_locale_fk
			     references ad_locales,
       public_p		     char(1) default 'f'
       			     constraint ml_class_public_p_ck
			     check (public_p in ('t','f')),
       sender_email	     varchar2(100),
       subject	             varchar2(1000),
       subject_change_p      char(1) default 't'
       			     constraint ml_class_subj_change_p_ck
			     check (subject_change_p in ('t','f')),
       text_header	     clob,
       text_header_change_p  char(1) default 't'
       			     constraint ml_class_t_head_change_p_ck
			     check (text_header_change_p in ('t','f')),
       text_body	     clob,
       text_body_change_p    char(1) default 't'
       			     constraint ml_class_t_body_change_p_ck
			     check (text_body_change_p in ('t','f')),
       text_footer	     clob,
       text_footer_change_p  char(1) default 't'
       			     constraint ml_class_t_foot_change_p_ck
			     check (text_footer_change_p in ('t','f')),
       html_header	     clob,
       html_header_change_p  char(1) default 't'
       			     constraint ml_class_h_head_change_p_ck
			     check (html_header_change_p in ('t','f')),
       html_body	     clob,
       html_body_change_p    char(1) default 't'
       			     constraint ml_class_h_body_change_p_ck
			     check (html_body_change_p in ('t','f')),
       html_footer	     clob,
       html_footer_change_p  char(1) default 't'
       			     constraint ml_class_h_foot_change_p_ck
			     check (html_footer_change_p in ('t','f')),
       mime_type	     varchar2(30) default 'text/plain',
       comments		     clob
);

create table ml_mail_jobs (
       mail_job_id	  integer
       			  constraint ml_mail_jobs_pk
			  primary key,
       mail_class_id	  integer
       			  constraint ml_mail_jobs_mail_class_id_fk
			  references acs_objects
			  on delete set null,
       list_id		  integer
       			  constraint ml_mail_jobs_list_id_fk
			  references acs_objects
			  on delete set null,
       selection_id	  integer
       			  constraint ml_mail_jobs_sel_id_fk
			  references acs_objects
			  on delete set null,
       category_ids	  varchar(1000),
       category_join	  varchar(5) default 'or',
       package_id	  integer
       			  constraint ml_mail_jobs_package_id_fk
			  references apm_packages,
       locale		  varchar2(5)
       			  constraint ml_mail_jobs_locale_nn
			  not null
			  constraint ml_mail_jobs_locale_fk
			  references ad_locales,
       sender_email	  varchar2(100),
       track_links_p	  char(1) default 'f'
       			  constraint ml_mail_jobs_track_p_ck
			  check (track_links_p in ('t','f')),
       subject		  varchar2(1000),
       text_header	  clob,
       text_body	  clob,
       text_footer	  clob,
       html_header	  clob,
       html_body	  clob,
       html_footer	  clob,
       template_p	  char(1) default 'f'
       			  constraint ml_mail_jobs_template_p_ck
			  check (template_p in ('t','f')),
       mime_type	  varchar2(30) default 'text/plain',
       css_revision_id	  integer
       			  constraint ml_mail_jobs_css_rev_id_fk
			  references cr_revisions on delete set null,
       state		  varchar2(10),
       scheduled_date	  date default sysdate,
       execution_date	  date,
       sql_query	  clob,
       bind_vars	  varchar2(4000),
       mails_sent	  integer default 0,
       mails_bounced	  integer default 0
);

create index ml_jobs_scheduled_date_ix on ml_mail_jobs(scheduled_date);
create index ml_jobs_execution_date_ix on ml_mail_jobs(execution_date);

create table ml_mail_job_bind_vars (
        mail_job_id	integer
			constraint ml_m_job_b_vars_job_id_fk
			references ml_mail_jobs
			on delete cascade,
        name		varchar2(30),
        description	varchar2(4000),
        value   	varchar2(4000),
	constraint ml_mail_job_bind_vars_pk
        primary key (mail_job_id, name)
);

-- make sure that this table doesn't fill up too much
-- i.e. delete all data older than 7 days

create table ml_email_log (
       user_id		  integer
       			  constraint ml_email_log_user_id_fk
			  references users,
       mail_job_id	  integer
       			  constraint ml_email_log_mail_job_id_fk
			  references ml_mail_jobs,
       send_date	  date default sysdate,
       constraint ml_email_log_pk
       primary key (user_id, mail_job_id)
);

create index ml_email_log_date_ix on ml_email_log(send_date);

create table ml_user_email_log (
       user_id		  integer primary key
       			  constraint ml_user_email_log_id_fk
			  references users,
       last_mail_date	  date default null
);

create index ml_user_email_log_date_ix on ml_user_email_log(last_mail_date);

create table ml_bounce_log (
       mail_job_id	   integer
       			   constraint ml_bounce_log_mail_job_id_fk
			   references ml_mail_jobs
			   on delete cascade,
       user_id		   integer
       			   constraint ml_bounce_log_user_id_fk
			   references users
			   on delete cascade,
       sending_time	   date,
       bouncing_time	   date default sysdate,
       constraint ml_bounce_log_pk
       primary key (mail_job_id, user_id)
);

create index ml_bounce_log_bounce_time_ix on ml_bounce_log(bouncing_time);

@@mailing-lists-package.sql
@@mailing-lists-init.sql