-- $Id: mailing-lists-create.sql,v 1.2 2004/01/21 19:22:14 maltes Exp $


-- 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


-- create the object types

  select acs_object_type__create_type (
    'mailing_list',
    'Mailing List',
    'Mailing Lists',
    'acs_object',
    'ml_mailing_lists',
    'list_id',
    null,
    'f',
    null,
    'ml_mailing_list__name'
   );

  select acs_object_type__create_type (
    'mail_class',
    'Mailing Class',
    'Mailing Classes',
    'acs_object',
    'ml_mail_classes',
    'mail_class_id',
    null,
    'f',
    null,
    'ml_mail_class__name'
   );

  select acs_object_type__create_type (
    'mail_job',
    'Mailing Job',
    'Mailing Jobs',
    'acs_object',
    'ml_mail_jobs',
    'mail_job_id',
    null,
    'f',
    null,
    'ml_mail_job__name'
   );


-- create the privileges

        select acs_privilege__create_privilege('mailing_list_admin','Mailing Lists Administrator', null);

        select acs_privilege__add_child('admin','mailing_list_admin');


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		     text
       			     constraint ml_mailing_lists_name_nn
			     not null,
       locale		     varchar(5)
       			     constraint ml_mailing_lists_locale_nn
			     not null
			     constraint ml_mail_lists_locale_fk
			     references ad_locales,
       teaser		     text,
       public_p		     boolean,
       sender_email	     text,
       confirm_subject	     text,
       confirm_body	     text,
       confirm_mime_type     varchar(30) default 'text/plain',
       welcome_subject	     text,
       welcome_body	     text,
       welcome_mime_type     varchar(30) default 'text/plain',
       remind_subject	     text,
       remind_body	     text,
       remind_mime_type      varchar(30) default 'text/plain',
       expiration_date	     timestamptz,
       -- amount of days after sending registration email
       first_reminder	     integer default 7,
       -- 0 if no second reminder
       second_reminder	     integer default 30,
       comments		     text
);

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

create table ml_mailing_list_user_map (
       list_id			   integer
       				   constraint ml_ml_user_map_list_fk
				   references ml_mailing_lists
				   on delete cascade,
       user_id			   integer
       				   constraint ml_ml_user_map_user_fk
       				   references users
				   on delete cascade,
       subscription_date	   timestamptz default current_timestamp,
       unsubscription_date	   timestamptz,
       subscribed_p		   boolean,
       confirmed_p		   boolean,
       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		     text
       			     constraint ml_mail_classes_name_nn
			     not null,
       locale		     varchar(5)
       			     constraint ml_mail_classes_locale_nn
			     not null
			     constraint ml_mail_classes_locale_fk
			     references ad_locales,
       public_p		     boolean,
       sender_email	     varchar(100),
       subject	             text,
       subject_change_p      boolean,
       text_header	     text,
       text_header_change_p  boolean,
       text_body	     text,
       text_body_change_p    boolean,
       text_footer	     text,
       text_footer_change_p  boolean,
       html_header	     text,
       html_header_change_p  boolean,
       html_body	     text,
       html_body_change_p    boolean,
       html_footer	     text,
       html_footer_change_p  boolean,
       mime_type	     varchar(30) default 'text/plain',
       comments		     text
);

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,
       package_id	  integer
       			  constraint ml_mail_jobs_package_id_fk
			  references apm_packages,
       locale		  varchar(5)
       			  constraint ml_mail_jobs_locale_nn
			  not null
			  constraint ml_mail_jobs_locale_fk
			  references ad_locales,
       sender_email	  varchar(100),
       track_links_p	  boolean,
       subject		  text,
       text_header	  text,
       text_body	  text,
       text_footer	  text,
       html_header	  text,
       html_body	  text,
       html_footer	  text,
       template_p	  boolean,
       mime_type	  varchar(30) default 'text/plain',
       css_revision_id	  integer
       			  constraint ml_mail_jobs_css_rev_id_fk
			  references cr_revisions on delete set null,
       state		  varchar(10),
       scheduled_date	  timestamptz default current_timestamp,
       execution_date	  timestamptz,
       sql_query	  text,
       bind_vars	  text,
       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		varchar(30),
        description	text,
        value   	text,
	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	  timestamptz default current_timestamp,
       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	  timestamptz 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	   timestamptz,
       bouncing_time	   timestamptz default current_timestamp,
       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);

\i mailing-lists-package-create.sql
\i mailing-lists-init.sql