alter table ml_mail_classes drop constraint ml_class_subj_change_p_ck;

alter table ml_mail_classes add (
       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	     varchar2(4000),
       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	     varchar2(4000),
       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	     varchar2(4000),
       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	     varchar2(4000),
       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	     varchar2(4000),
       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	     varchar2(4000),
       html_footer_change_p  char(1) default 't' constraint ml_class_h_foot_change_p_ck check (html_footer_change_p in ('t','f'))
);

update ml_mail_classes
set subject = default_subject,
subject_change_p = subject_changeable_p,
text_header = default_header,
text_header_change_p = header_changeable_p,
text_body = default_body,
text_body_change_p = body_changeable_p,
text_footer = default_footer,
text_footer_change_p = footer_changeable_p
where mime_type = 'text/plain';

update ml_mail_classes
set subject = default_subject,
subject_change_p = subject_changeable_p,
html_header = default_header,
html_header_change_p = header_changeable_p,
html_body = default_body,
html_body_change_p = body_changeable_p,
html_footer = default_footer,
html_footer_change_p = footer_changeable_p
where mime_type = 'text/html';

alter table ml_mail_classes drop column default_subject;
alter table ml_mail_classes drop column default_header;
alter table ml_mail_classes drop column default_body;
alter table ml_mail_classes drop column default_footer;
alter table ml_mail_classes drop column subject_changeable_p;
alter table ml_mail_classes drop column header_changeable_p;
alter table ml_mail_classes drop column body_changeable_p;
alter table ml_mail_classes drop column footer_changeable_p;
alter table ml_mail_classes drop column x_field_p;


alter table ml_mail_jobs add (
       text_header	  varchar2(4000),
       text_body	  clob,
       text_footer	  varchar2(4000),
       html_header	  varchar2(4000),
       html_body	  clob,
       html_footer	  varchar2(4000),
       mails_bounced	  integer default 0
);

update ml_mail_jobs
set text_header = header,
text_footer = footer
where mime_type = 'text/plain';

update ml_mail_jobs
set html_header = header,
html_footer = footer
where mime_type = 'text/html';

declare
  new_body clob;
begin
  for t in (select mail_job_id, body, dbms_lob.getlength(body) as lob_length from ml_mail_jobs where mime_type = 'text/plain') loop
    new_body:=empty_clob();
    dbms_lob.copy(new_body, t.body, t.lob_length);
    update ml_mail_jobs set text_body = new_body where mail_job_id = t.mail_job_id;
  end loop;
end;
/

update ml_mail_jobs
set text_body = body
where mime_type = 'text/plain';

update ml_mail_jobs
set html_body = body, text_body = null
where mime_type = 'text/html';

alter table ml_mail_jobs drop column header;
alter table ml_mail_jobs drop column body;
alter table ml_mail_jobs drop column footer;

alter table ml_mailing_lists add (
       new_mime_type	     varchar2(30) default 'text/plain'
);
alter table ml_mail_classes add (
       new_mime_type	     varchar2(30) default 'text/plain'
);
alter table ml_mail_jobs add (
       new_mime_type	     varchar2(30) default 'text/plain'
);

update ml_mailing_lists
set new_mime_type = mime_type;

update ml_mail_classes
set new_mime_type = mime_type;

update ml_mail_jobs
set new_mime_type = mime_type;

alter table ml_mailing_lists drop column mime_type;
alter table ml_mail_classes drop column mime_type;
alter table ml_mail_jobs drop column mime_type;

alter table ml_mailing_lists add (
       mime_type	     varchar2(30) default 'text/plain'
);
alter table ml_mail_classes add (
       mime_type	     varchar2(30) default 'text/plain'
);
alter table ml_mail_jobs add (
       mime_type	     varchar2(30) default 'text/plain'
);

update ml_mailing_lists
set mime_type = new_mime_type;

update ml_mail_classes
set mime_type = new_mime_type;

update ml_mail_jobs
set mime_type = new_mime_type;

alter table ml_mailing_lists drop column new_mime_type;
alter table ml_mail_classes drop column new_mime_type;
alter table ml_mail_jobs drop column new_mime_type;

declare
  v_welcome clob;
begin
  for t in (select list_id, welcome_text, mime_type, dbms_lob.getlength(welcome_text) as lob_length from ml_mailing_lists where welcome_text is not null) loop
    v_welcome:=empty_clob();
    dbms_lob.copy(v_welcome, t.welcome_text, t.lob_length);
    update ml_mailing_lists set welcome_body = v_welcome,
           welcome_mime_type = t.mime_type where list_id = t.list_id;
  end loop;
end;
/

alter table ml_mailing_lists add (
       remind_subject	     varchar2(1000),
       remind_body	     clob,
       remind_mime_type      varchar2(30) default 'text/plain'
);

alter table ml_mailing_list_user_map add (
       reminder_count		   integer default 0
);

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

insert into ml_user_email_log
(select m.user_id, max(l.send_date) as last_mail_date
from ml_email_log l, ml_mailing_list_user_map m
where m.user_id = l.user_id(+)
group by m.user_id);