--
-- A simple mail queue
--
-- @author <a href="mailto:eric@openforce.net">eric@openforce.net</a>
-- @version $Id: acs-mail-lite-create.sql,v 1.22 2024/09/03 15:37:33 gustafn Exp $
--

create sequence acs_mail_lite_id_seq;

CREATE TABLE acs_mail_lite_queue (
    message_id                integer
                              constraint acs_mail_lite_queue_pk
                              primary key,
    creation_date             text,
    locking_server            text,
    to_addr                   text,
    cc_addr                   text,
    bcc_addr                  text,
    from_addr                 text,
    reply_to                  text,
    subject                   text,
    body                      text,
    package_id                integer
                              constraint amlq_package_id_fk
                              references apm_packages,
    file_ids                  text,
    filesystem_files          text,
    delete_filesystem_files_p boolean,
    mime_type                 text,
    object_id                 integer,
    no_callback_p             boolean,
    extraheaders              text,
    use_sender_p              boolean
);

create index acs_mail_lite_queue_package_id_idx on acs_mail_lite_queue(package_id);

create table acs_mail_lite_mail_log (
    party_id                  integer
                              constraint amlml_party_id_fk
                              references parties (party_id)
                              on delete cascade
                              constraint acs_mail_lite_log_pk
                              primary key,
    last_mail_date            timestamptz default current_timestamp
);


create table acs_mail_lite_bounce (
    party_id                  integer
                              constraint amlb_party_id_fk
                              references parties (party_id)
                              on delete cascade
                              constraint acs_mail_lite_bou_pk
                              primary key,
    bounce_count              integer default 1
);


create table acs_mail_lite_bounce_notif (
    party_id                  integer
                              constraint amlbn_party_id_fk
                              references parties (party_id)
                              on delete cascade
                              constraint acs_mail_lite_bounce_notif_pk
                              primary key,
    notification_time         timestamptz default current_timestamp,
    notification_count        integer default 0
);

--
-- Add Incoming Mail Processing
--
create sequence acs_mail_lite_in_id_seq;

-- New tables

-- table tracking incoming email
create table acs_mail_lite_from_external (
       aml_email_id         integer primary key
                            not null
                            DEFAULT nextval ('acs_mail_lite_id_seq'),
       -- Priority for processing incoming email in queue.
       -- Lower number processed first.
       priority             integer,
       -- using varchar instead of text for indexing
       -- to and from email are defined according to headers.
       -- See table acs_mail_lite_ie_headers
       to_email_addrs       varchar(1000),
       from_email_addrs     text,
       subject              text,
       -- see acs_mail_lite_send_msg_id_map.msg_id
       msg_id               bigint,
       -- used by prioritization calculations
       -- For IMAP4 this is size defined by rfc822
       size_chars           numeric,
       -- time email received from server in seconds since tcl epoch
       received_cs          bigint,
       -- Answers question:
       -- Has all ACS Mail Lite processes finished for this email?
       -- Processes like parsing email, bounced email, input validation
       processed_p      boolean,
       -- Answers question:
       -- Have all callbacks related to this email finished processing?
       -- Upon release, delete  all components of aml_email_id also from
       -- tables acs_mail_lite_ie_headers, acs_mail_lite_ie_body_parts, and
       -- acs_mail_lite_ie_files.
       -- Release essentially means its available to be deleted.
       release_p boolean
);

create index acs_mail_lite_from_external_processed_p_idx
       on acs_mail_lite_from_external (processed_p);
create index acs_mail_lite_from_external_release_p_idx
       on acs_mail_lite_from_external (release_p);



-- Some services are offered between sessions of importing incoming email.
-- A unique ID provided by
-- acs_mail_lite_email_uid_id_map.uid_ext
-- is designed to
-- support UIDs for each email that are consistent between import sessions
-- from external source, such as specified by IMAP4 rfc3501
-- https://tools.ietf.org/html/rfc3501
-- It is also expected that each mailbox.host, mailbox and user are
-- consistent for duration of the service.
-- And yet, experience knows that sometimes email servers change
-- and UIDs for an email change with it.
-- Users switching email servers of an email account using a IMAP4 client
-- might hassle with moving email, but
-- in the process they generally know what is happening. They don't re-read
-- all the email.
-- We want to avoid this server re-reading and processing email
-- that has already been processed, when the UID of emails change.
-- The Questions become:

-- What scenarios might we run into?
-- Another  user resetting flags.
-- A server migration or restore with some conflicting UIDs.

-- Can we recognize a change in server?
-- If so, can we signal ACS Mail Lite to ignore existing email
-- in a new environment?
-- Also, we should have a manual override to not ignore or ignore
-- in case of false positive and false negative triggers.

-- Can we recognize if another user accesses the same email account
-- and arbitrarily selects some prior messages to unread?
-- Yes. The purpose of acs_mail_lite_email_uid_id_map is to act as a log
-- of prior processed messages.
-- If total new messages is a significant percentage of all messages
-- and service has been working for a week or more,
-- use statistics to generate a reasonable trigger.
-- Weekly produce a revised distribution curve of weekly counts of incoming.
-- If percentile is over 200%.. ie twice the weekly maximum..
-- Also, test, for example, if new message count is less than
-- prior total, there's more of a chance that they are new messages;
-- Maybe check for one or two duplicates.
-- If new message count is over the total prior message count, flag a problem.
-- rfc3501 2.3.1.1.  ..A client can only assume.. at the time
--        that it obtains the next unique identifier value.. that
--        messages arriving after that time will have a UID greater
--        than or equal to that value...


-- Can we recognize a change in server?
-- rfc3501 does not specify a unique server id
-- It specifies a unique id for a mailbox: UIDVALIDITY
-- UIDVALIDITY is optional, quite useful.
-- Rfc3501 specifies a unique id for each email: UID.
-- We can assign each email a more unique reference:
-- mailbox.host + mailbox.name + UIDVALIDITY (of mailbox) + UID.
-- We are more specific so that we detect more subtle cases of
-- server change, where checks by UID and UIDVALIDITY may not.


-- For example, when migrating email service and
-- and the new system initially restores the UIVALIDITY and message UID,
-- but references a different state of each email. The cause
-- of such cases are reasonable. For example, restoring
-- from backup to a new email host or restoring
-- before some batch event changed a bunch of things. So,
-- src_ext = mailbox.host + (user?) + mailbox.name + UIDVALIDITY
-- Leave user out for now..
-- Priority is to have a robust way to ignore
-- prior messages recognized as 'new' messages.

create table acs_mail_lite_email_uid_id_map (
       -- unqique internal inbound email id
       -- src_ext_id identifies source, but is redundant
       -- for identifying a unique email.
       aml_email_id  integer not null,
       --uisng varchar instead of text for indexing purposes
       -- Each UID externally defined such as from imap4 server
       uid_ext varchar(3000) not null,
       -- Each external source may apply a different uid.
       -- This is essentially an arbitrary constant frame reference between
       -- connecting sessions with external server in most scenarios.
       -- For IMAP4v1 rfc3501  2.3.1.1. item 4 ..combination of
       -- mailbox.name, UIDVALIDITY, and UID must refer to a single
       -- immutable message on that server forever.
       -- default is:
       -- ExternalSource parameter mailbox.name
       -- and UIDVALIDITY with dash as delimiter
       -- where ExternalSource parameter is
       -- either blank or maybe mailbox.host for example.
       -- external source reference id
       -- see acs_mail_lite_email_src_ext_id_map.aml_src_id
       src_ext_id integer
);

create index acs_mail_lite_email_uid_id_map_uid_ext_idx
    on acs_mail_lite_email_uid_id_map (uid_ext);
create index acs_mail_lite_email_uid_id_map_src_ext_id_idx
    on acs_mail_lite_email_uid_id_map (src_ext_id);

create table acs_mail_lite_email_src_ext_id_map (
       aml_src_id integer not null,
       src_ext varchar(1000) not null
);



-- Packages that are services, such as ACS Mail Lite, do not have a web UI.
-- Scheduled procs cannot read changes in values of package parameters
-- or get updates via web UI connections, or changes in tcl via apm.
-- Choices are updates via nsv variables and database value updates.
-- Choices via database have persistence across server restarts.
-- Defaults are set in acs_mail_lite::sched_parameters
-- These all are used in context of processing incoming email
-- unless stated otherwise.
-- Most specific flag takes precedence.
-- If an email is flagged high priority by package_id and
-- low priority by subject glob. It is assigned low priority.
-- Order of specificity.
-- medium default package_id party_id subject_id object_id
-- party_id can be group_id or user_id
-- If fast and low flag the same specificity for an email, low is chosen.
create table acs_mail_lite_ui (
       -- scan_replies_est_dur_per_cycle_s_override see www/doc/analysis-notes
       sredpcs_override integer,
       -- Answers question: Reprocess old email?
       reprocess_old_p boolean,
       -- Max number of concurrent threads for high priority processing
       max_concurrent integer,
       -- Any incoming email body part over this size is stored in file
       -- instead of database.
       max_blob_chars integer,
       -- Minimum threshold for default medium (standard) priority
       mpri_min integer,
       -- Maximum value for default medium (standard) priority
       mpri_max integer,
       --space delimited list of package_ids to process at fast/high priority
       hpri_package_ids text,
       --space delimited list of package_ids to process at low priority
       lpri_package_ids text,
       --space delimited list of party_ids to process at fast/high priority
       hpri_party_ids text,
       --space delimited list of party_ids to process at low priority
       lpri_party_ids text,
       -- a glob for searching subjects to flag for fast/high priority
       hpri_subject_glob text,
       -- a glob for searching subjects to flag for low priority
       lpri_subject_glob text,
       --space delimited list of object_ids to process at fast/high priority
       hpri_object_ids text,
       --space delimited list of object_ids to process at low priority
       lpri_object_ids text,
       --filters to reject input as early as possible in processing inbound
       --Each filter is a name value in standard tcl list format
       --where name is a header name
       reject_on_hit text,
       reject_on_miss text
);


-- This table has similar requirements to acs_mail_lite_ui
-- proc acs_mail_lite_imap_conn_* needs to be able to update values
-- within scheduled procs without restarting server.
-- Port is ignored. Added because it is a common requirement of connections
-- that might one day be useful here, too.
create table acs_mail_lite_imap_conn (
       -- mailbox.host
       ho text,
       -- you guessed it
       pa text,
       -- port
       po integer,
       --timeout
       ti integer,
       -- user
       us text,
       -- mailbox.name See nsimap documentation for definition
       na text,
       -- space separated list of flags for imap related modifications
       -- ssl means connect via ssl.
       -- novalidatecert means accept a self-signed certificate
       fl text
);


-- Following tables store parsed incoming email for processing by callbacks
-- defined in the rest of OpenACS

-- incoming email headers
-- There should be a size limit per unit time from each source
-- to prevent DDOS attacks and such (at least to the imap system).
--
create table acs_mail_lite_ie_headers (
       -- incoming email
       -- only includes headers useful in processing the queue
       -- Such as
       -- size
       -- from
       aml_email_id integer,
       -- header name, one header per row
       -- For all headers together, see acs_mail_lite_ie_parts.c_type=headers
       -- Special case: h_name = struct means
       -- h_value contains entire value returned from ns_imap struct
       -- as a tcl list
       h_name text,
       h_value text
);

create index acs_mail_lite_ie_headers_aml_email_id_idx
    on acs_mail_lite_ie_headers (aml_email_id);

-- incoming email body parts
-- including email file attachments and file content
-- A part may be a filename. The filename data model is added
-- to the parts table to reduce code complexity.
-- An attached or inline file is a kind of part.
create table acs_mail_lite_ie_parts (
       aml_email_id integer,
       section_id integer,

       -- In addition to content_type, there is a special case:
       -- headers, which contains all headers for email
       -- content_type = c_type
       c_type text,
       -- If type has a filename, this is original filename.
       filename text,
       -- If c_type is multipart, content is blank. part_id is branched.
       content text,
       -- An alternate filepathname for large blob, or
       -- A local absolute filepath location
       c_filepathname text
);

create index acs_mail_lite_ie_parts_aml_email_id_idx
    on acs_mail_lite_ie_parts (aml_email_id);


-- incoming email parts, name value pairs of
create table acs_mail_lite_ie_part_nv_pairs (
       aml_email_id integer,
       -- Usage is same as acs_mail_lite_ie_parts.section_id
       section_id integer,
       -- name value pair
       p_name text,
       p_value text
);

create index acs_mail_lite_ie_part_nv_pairs_aml_email_id_idx
    on acs_mail_lite_ie_part_nv_pairs (aml_email_id);

create table acs_mail_lite_ie_section_ref_map (
       -- 'Section' refers to usage with 'part' reference in 'ns_imap body'
       -- Email parts can contain multiple parts.
       -- Each multiple part can contain multiple parts.

       -- Section_ref is an absolute reference of a part
       -- including the parts it is contained in, and
       -- delimited by period.
       -- It is defined by:
       --  ns_imap body #s msgno part
       -- And yet, this reference system holds for any email
       -- storage, so is adopted for generic use as well.

       -- Default reference is value of 1.
       -- A two part message has values 1 and 2.
       -- Part 2 of a 3 part email (2/3) has reference '2'
       -- If part 2 is also multiple parts, then
       -- part 1 of part 2 of email has reference '2.1' and so on.

       -- Mapping is constant for each case.
       -- For example, '1.2.2.1' will always point to the same integer.
       -- So do not alter values as they are likely used by
       -- multiple emails.


       section_ref varchar(300),
       section_id integer
);

create index acs_mail_lite_ie_section_ref_map_section_ref_idx
    on acs_mail_lite_ie_section_ref_map (section_ref);
create index acs_mail_lite_ie_section_ref_map_section_id_idx
    on acs_mail_lite_ie_section_ref_map (section_id);

--
-- TODO: this table does not exists for Oracle.... Not needed there?
--
create table acs_mail_lite_send_msg_id_map (
       -- a randomized number unique to this table
       -- unique not null
       msg_id        text primary key,
       package_id    integer
                     constraint aml_package_id_fk
                     references apm_packages
                     on delete cascade,
       party_id      integer
                     constraint aml_from_external_party_id_fk
                     references parties (party_id)
                     on delete cascade,
       object_id     integer
                     constraint aml_from_external_obect_id_fk
                     references acs_objects (object_id)
                     on delete cascade,
       -- Indicate approximate time when this email is created
       datetime_cs   integer,
       -- other data or parameters to associate with email
       other         text
);

--
-- Speed up referential integrity
--
CREATE INDEX acs_mail_lite_send_msg_id_map_package_id_idx ON acs_mail_lite_send_msg_id_map(package_id);
CREATE INDEX acs_mail_lite_send_msg_id_map_party_id_idx   ON acs_mail_lite_send_msg_id_map(party_id);
CREATE INDEX acs_mail_lite_send_msg_id_map_object_id_idx  ON acs_mail_lite_send_msg_id_map(object_id);