-- webmail.sql
-- by Jin Choi <jsc@arsdigita.com>
-- Feb 28, 2000

-- Data model to support web based email system.

-- Database user must have javasyspriv permission granted to it:
-- connect system
-- grant javasyspriv to <username>;

-- ctxsys must grant EXECUTE on ctx_ddl to this Oracle user:
-- connect ctxsys
-- grant execute on ctx_ddl to <username>;

-- webmail.sql,v 1.5 2000/03/10 05:02:25 jsc Exp


-- Domains we receive email for.
create table wm_domains (
	-- short text key
	short_name		varchar(100) not null primary key,
	-- fully qualified domain name
	full_domain_name	varchar(100) not null
);


-- Maps email accounts to ACS users.
create table wm_email_user_map (
	email_user_name	varchar(100) not null,
	domain		varchar(100) references wm_domains,
	user_id		integer not null references users,
	primary key (email_user_name, domain, user_id)
);

-- Main mail message table. Stores body of the email, along
-- with a parsed text version with markers for attachments for MIME
-- messages.
create table wm_message_lobs (
       msg_id           integer,
       lob              integer references lobs,
       primary key (msg_id,lob)
);

create sequence wm_msg_id_sequence;
create table wm_messages (
        msg_id          integer primary key references wm_message_lobs(msg_id),
        --body            clob,
        body            integer references lobs,
	-- plain text portions of MIME message; empty if 
	-- entire message is of type text/*.
	-- mime_text	clob,
	mime_text	integer references lobs,
        message_id      varchar(200), -- RFC822 Message-ID field
	unique_id	integer -- for both POP3 UIDL and IMAP UID
);

create index wm_messages_by_message_id on wm_messages(message_id);


create trigger wm_message_lobs_tr before delete or update or insert
on wm_message_lobs for each row execute procedure on_lob_ref();

-- drop function on_wm_lobs_delete();
create function on_wm_lobs_delete() returns opaque as '
begin
	delete from wm_messages where msg_id = old.msg_id;
	return old;
end;' language 'plpgsql';


-- drop trigger wm_lobs_delete_trig on wm_message_lobs;
create trigger wm_lobs_delete_trig before delete on wm_message_lobs
for each row execute procedure on_wm_lobs_delete();



-- Stores attachments for MIME messages.
create table wm_attachments (
	msg_id		integer not null references wm_messages,
	-- File name associated with attachment.
	filename	varchar(600) not null,
	-- MIME type of attachment.
	content_type	varchar(100),
--	data		blob,
        lob             integer references lobs,
	format		varchar(10) check (format in ('binary', 'text')), -- for interMedia INSO filter
	primary key (msg_id, filename)
);

create trigger wm_attachments_tr before delete or update or insert
on wm_attachments for each row execute procedure on_lob_ref();


-- Maps mailboxes (folders, in more common terminology) to ACS users.
create sequence wm_mailbox_id_sequence;

create table wm_mailboxes (
	mailbox_id	integer primary key,
	name		varchar(100) not null,
	creation_user	integer references users(user_id),
	creation_date	timestamp,
	uid_validity	integer, -- Needed for IMAP
	unique(creation_user, name)
);

-- Maps messages to mailboxes (and thus to users).
create table wm_message_mailbox_map (
	mailbox_id	integer references wm_mailboxes,
	msg_id		integer references wm_messages,
	seen_p		char(1) default 'f' check(seen_p in ('t','f')),
	answered_p	char(1) default 'f' check(answered_p in ('t','f')),
	flagged_p	char(1) default 'f' check(flagged_p in ('t','f')),
	deleted_p	char(1) default 'f' check(deleted_p in ('t','f')),
	draft_p		char(1) default 'f' check(draft_p in ('t','f')),
	recent_p	char(1) default 't' check(recent_p in ('t','f')),
	primary key (msg_id, mailbox_id)
);


-- Parsed recipients for a message; enables search by recipient.
create table wm_recipients (
        msg_id          integer not null references wm_messages,
        header          varchar(100) not null, -- to, cc, etc.
        email           varchar(300) not null,
        name            varchar(200)
);

create index wm_recipients_by_msg_id on wm_recipients(msg_id);


-- Headers for a message.
create table wm_headers (
        msg_id          integer not null references wm_messages,
	-- field name as specified in the email
        name            varchar(100) not null,
	-- lowercase version for case insensitive searches
        lower_name      varchar(100) not null,
        value           varchar(4000),
        -- various parsed versions of the value
        time_value      timestamp, -- date/time fields
        -- email and name, for singleton address fields like From
        email_value     varchar(300),
        name_value      varchar(200),
        -- original order of headers
        sort_order      integer not null
);

create index wm_headers_by_msg_id_name on wm_headers (msg_id, lower_name);


-- Table for recording messages that we failed to parse for whatever reason.
create table wm_parse_errors (
	filename		varchar(255) primary key not null, -- message queue file
	error_message		varchar(4000),
	first_parse_attempt	timestamp default sysdate() not null
);

-- Used for storing attachments for outgoing messages.
-- Should be cleaned out periodically.

create sequence wm_outgoing_msg_id_sequence;

create table wm_outgoing_messages (
	outgoing_msg_id		integer not null primary key,
	body			text,  -- used to be a clob
--	composed_message	text,  -- used to be a clob, needs to be a lob?
        lob                     integer references lobs,
	creation_date		timestamp default sysdate() not null,
	creation_user		integer not null references users
);

create trigger wm_out_messages_tr before delete or update or insert
on wm_outgoing_messages for each row execute procedure on_lob_ref();

create table wm_outgoing_headers (
	outgoing_msg_id		integer not null references wm_outgoing_messages on delete cascade,
	name			varchar(100) not null,
	value			varchar(4000),
	sort_order		integer not null
);

create unique index wm_outgoing_headers_idx on wm_outgoing_headers (outgoing_msg_id, name);


create sequence wm_outgoing_parts_sequence;
create table wm_outgoing_message_parts (
	outgoing_msg_id		integer not null references wm_outgoing_messages on delete cascade,
--	data			blob,
        lob                     integer not null references lobs,
	filename		varchar(600) not null,
	content_type		varchar(100), -- mime type of data
	sort_order		integer not null,
	primary key (outgoing_msg_id, sort_order)
);

create trigger wm_messages_tr before delete or update or insert
on wm_outgoing_message_parts for each row execute procedure on_lob_ref();


-- DanW - this functionality should be implemented by a scheduled process
--        inside of aolserver

-- Create a job to clean up orphaned outgoing messages every day.
-- create or replace procedure wm_cleanup_outgoing_msgs as
-- begin
--   delete from wm_outgoing_messages
--     where creation_date < sysdate() - 1;
-- end;
-- /

-- declare
--   job number;
-- begin
--   dbms_job.submit(job, 'wm_cleanup_outgoing_msgs;',
-- 		  interval => 'sysdate() + 1');
-- end;
-- /

-- variable jobno number;
-- exec dbms_job.submit(:jobno, 'wm_cleanup_outgoing_msgs;', sysdate(), 'sysdate() + 1');

-- Sean's POP3 server stuff (currently unused).
create sequence wm_pop3_servers_seq;
create table wm_pop3_servers (
	server_id	integer primary key,
	user_id		integer references users,
	server_name	varchar(100) not null,
	port_number	integer default 110,
	user_name	varchar(200) not null,
	password	varchar(200) not null,
	last_uidl	varchar(200) default 'None',
	mailbox_size 	integer default 0,
	n_messages	integer default 0,
	delete_on_download_p	char(1) default 'f' check (delete_on_download_p in ('t', 'f')),
	delete_on_local_del_p	char(1) default 'f' check (delete_on_local_del_p in ('t', 'f'))
);


-- PL/SQL bindings for Java procedures
-- create or replace procedure wm_process_queue (queuedir IN VARCHAR)
-- as language java
-- name 'com.arsdigita.mail.MessageParser.processQueue(java.lang.String)';
-- /

-- useful for debugging
-- create or replace procedure wm_parse_message_from_file (filename IN VARCHAR)
-- as language java
-- name 'com.arsdigita.mail.MessageParser.parseMessageFromFile(java.lang.String)';
-- /

-- create or replace function wm_parse_date (datestr IN VARCHAR) return date
-- as language java
-- name 'com.arsdigita.mail.MessageParser.parseDate(java.lang.String)
-- return java.sql.Timestamp';
-- / 

-- create or replace procedure wm_compose_message (outgoing_msg_id IN NUMBER)
-- as language java
-- name 'com.arsdigita.mail.MessageComposer.composeMimeMessage(int)';
-- /


-- Trigger to delete subsidiary rows when a message is deleted.
-- drop function wm_messages_delete_tr();
create function wm_messages_delete_tr() returns opaque as '
begin
  delete from wm_headers where msg_id = old.msg_id;
  delete from wm_recipients where msg_id = old.msg_id;
  delete from wm_message_mailbox_map where msg_id = old.msg_id;
  delete from wm_attachments where msg_id = old.msg_id;
  return old;
end;
' language 'plpgsql';

-- drop trigger wm_messages_delete_trigger on wm_messages;
create trigger wm_messages_delete_trigger
before delete on wm_messages
for each row execute procedure wm_messages_delete_tr();


-- DanW - this functionality should be replicated in aolserver 
--        with a scheduled process

-- Parse the queue every minute. Queue directory is hardcoded.
-- declare
--   job number;
-- begin
--   dbms_job.submit(job, 'wm_process_queue(''/home/nsadmin/qmail/queue/new'');',
-- 		  interval => 'sysdate() + 1/24/60');
-- end;
-- /


-- Utility function to determine email address for a response.
create function wm_response_address (integer) returns VARCHAR as '
declare	
        v_msg_id                alias for $1;
        from_address            varchar(4000);
        reply_to_address        varchar(4000);
begin
    select value into reply_to_address
      from wm_headers
      where msg_id = v_msg_id
        and lower_name = ''reply-to'';

    if found then 
       return reply_to_address;
    else 
      select value into from_address
        from wm_headers
        where msg_id = v_msg_id
          and lower_name = ''from'';
      return from_address;
    end if;
end;
' language 'plpgsql';

create sequence wm_unique_file_id start 1;


-- DanW - no can do intermedia in postgres.

-- interMedia index on body of message
-- create index wm_ctx_index on wm_messages (body)
-- indextype is ctxsys.context parameters ('memory 250M');

-- INSO filtered interMedia index for attachments.
-- create index wm_att_ctx_index on wm_attachments (data)
-- indextype is ctxsys.context parameters ('memory 250M filter ctxsys.inso_filter format column format');

-- Trigger to update format column for INSO index.
-- create or replace trigger wm_att_format_tr before insert on wm_attachments
-- for each row
-- declare
--   content_type	varchar(100);
-- begin
--   content_type := lower(:new.content_type);
--   if content_type like 'text/%' or content_type like 'application/msword%' then
--     :new.format := 'text';
--   else
--     :new.format := 'binary';
--   end if;
-- end;
-- /

	

  


-- Resync the interMedia index every hour.

-- declare
--   job number;
-- begin
--   dbms_job.submit(job, 'ctx_ddl.sync_index(''wm_ctx_index'');',
-- 		  interval => 'sysdate() + 1/24');
--   dbms_job.submit(job, 'ctx_ddl.sync_index(''wm_att_ctx_index'');',
-- 		  interval => 'sysdate() + 1/24');
-- end;
-- /

-- Mailing list data model

create table wm_lists (
	list_name	varchar(100) not null primary key
);