--
-- packages/acs-mail/sql/acs-mail-create.sql
--
-- @author John Prevost <jmp@arsdigita.com>
-- @creation-date 2001-01-08
-- @cvs-id $Id: acs-mail-create.sql,v 1.3 2003/05/17 09:51:08 jeffd Exp $
--

-- Typical usage when creating:

-- For text only
--
-- body_id := acs_mail_body.new ( ... );
-- text_id := acs_mail_object.new ( ... );
-- acs_content.set_parameters ( text_id, ... );
-- update acs_content set content = empty_blob() where object_id = text_id;
-- acs_mail_body.set_content(text_id);
-- bboard_message.new ( ..., body_id );

-- tcl possibilities:
-- set body_id [acs_mail_body_new ...]
-- set text_id [acs_mail_object_new ... -content $text ]
-- acs_mail_body_set_content $body_id $text_id
-- set msg_id [bboard_message_new ... $body_id]
-- *or*
-- set body_id [acs_mail_body_new ... -content $text]
-- set msg_id [bboard_message_new ... $body_id]

-- For attachments (multipart/mixed)
--
-- body_id := acs_mail_body.new ( ... );
-- part_id := acs_mail_multipart.new ( ..., 'multipart/mixed' );
-- text_id := acs_mail_object.new ( ... );
-- { ... content stuff ... }
-- photo_id := acs_mail_object.new ( ... );
-- { ... content stuff ... }
-- acs_mail_multipart.add_content ( part_id, text_id );
-- acs_mail_multipart.add_content ( part_id, photo_id );
-- acs_mail_body.set_content ( part_id );
-- bboard_message.new ( ..., body_id );

-- For alternatives
-- (Same as above, but 'multipart/alternative' instead of 'multipart/mixed')

-- Typical usage when displaying:

-- select ... from ... (tree query)
-- 0   RFC822 Header (ignored)
-- 1   multipart/mixed (attachments!)
-- 1.1 text/plain (spit it out)
-- 1.2 image/gif (inline it)
-- 1.3 text/plain (more text to spit out
-- 1.4 message/rfc822
-- 1.4.0 header of submessage

-- 0000 (not sure about the numbering stuff yet) is always the RFC822
-- header, autogenerated or from incoming email.
-- 0001 will always be the content of the message
-- within 0001 may be more items, depending on the structure of the message.

-- Common headers are also available decomposed into useful forms.

-- Exactly how incoming messages get transformed into this structure
-- and how outgoing messages get transformed from this structure is to
-- be implemented soon.

set feedback off

-- Object System Metadata ----------------------------------------------

-- A messaging object, which is subject to garbage collection by the
-- messaging system.  If any object in this table is not found in

--     select body_id as o_id from acs_mail_links
--       union
--     select object_id as o_id from acs_mail_multipart_parts
--       union
--     select body_content as o_id from acs_mail_bodies

-- then it is removed.  It is assumed that an object cannot satisfy
-- the above predicate if it ever stops satisfying it (outside of a
-- transaction.)

begin
    acs_object_type.create_type (
        supertype => 'acs_object',
        object_type => 'acs_mail_gc_object',
        pretty_name => 'ACS Mail Object',
        pretty_plural => 'ACS Mail Objects',
        table_name => 'ACS_MAIL_GC_OBJECTS',
        id_column => 'GC_OBJECT_ID',
        package_name => 'ACS_MAIL_GC_OBJECT',
        name_method => 'ACS_OBJECT.DEFAULT_NAME'
    );
end;
/
show errors

-- Mail bodies are automatically GC'd.  These contain the data
-- relevant to a single message.  These are shared by being pointed to
-- by many acs_mail_links.  This should not be subtyped.

begin
    acs_object_type.create_type (
        supertype => 'acs_mail_gc_object',
        object_type => 'acs_mail_body',
        pretty_name => 'ACS Mail Body',
        pretty_plural => 'ACS Mail Bodies',
        table_name => 'ACS_MAIL_BODIES',
        id_column => 'BODY_ID',
        package_name => 'ACS_MAIL_BODY',
        name_method => 'ACS_OBJECT.DEFAULT_NAME'
    );
end;
/
show errors

-- multipart mime parts are automatically GC'd.  These contain
-- multiple parts to make up alternatives or mixed content
-- (attachments).  These may be shared by belonging to multiple
-- mail_links.

begin
    acs_object_type.create_type (
        supertype => 'acs_mail_gc_object',
        object_type => 'acs_mail_multipart',
        pretty_name => 'ACS Mail Multipart Object',
        pretty_plural => 'ACS Mail Multipart Objects',
        table_name => 'ACS_MAIL_MULTIPARTS',
        id_column => 'MULTIPART_ID',
        package_name => 'ACS_MAIL_MULTIPART',
        name_method => 'ACS_OBJECT.DEFAULT_NAME'
    );
end;
/
show errors

-- A mail_link, subtypable, and used by applications to track messages.
-- Permissions should be set at this level.  These should not be
-- shared between applications: rather, an application should create a
-- new mail_link and use it as it wills.  When it's done, it should
-- delete this, which will cause the other objects to be garbage
-- collected.

begin
    acs_object_type.create_type (
        supertype => 'acs_object',
        object_type => 'acs_mail_link',
        pretty_name => 'ACS Mail Message',
        pretty_plural => 'ACS Mail Messages',
        table_name => 'ACS_MAIL_LINKS',
        id_column => 'MAIL_LINK_ID',
        package_name => 'ACS_MAIL_LINK',
        name_method => 'ACS_OBJECT.DEFAULT_NAME'
    );

end;
/
show errors

-- Raw Tables and Comments ---------------------------------------------

  -- All garbage collectable objects are in this table

create table acs_mail_gc_objects (
    gc_object_id integer
        constraint acs_mail_gc_objs_object_id_pk
            primary key
        constraint acs_mail_gc_objs_object_id_fk
            references acs_objects
);

  -- Mail bodies

create table acs_mail_bodies (
    body_id integer
        constraint acs_mail_bodies_body_id_pk primary key
        constraint acs_mail_bodies_body_id_fk
            references acs_mail_gc_objects on delete cascade,
    body_reply_to integer
        constraint acs_mail_bodies_reply_to_fk
            references acs_mail_bodies on delete set null,
    body_from integer
        constraint acs_mail_bodies_body_from_fk
            references parties on delete set null,
    body_date date,
    header_message_id varchar2(1000)
        constraint acs_mail_bodies_h_m_id_un unique
        constraint acs_mail_bodies_h_m_id_nn not null,
    header_reply_to varchar2(1000),
    header_subject varchar2(4000),
    header_from varchar2(4000),
    header_to varchar2(4000),
    content_item_id integer
        constraint acs_mail_bodies_content_oid_fk
            references acs_objects on delete cascade
);

-- RI Indexes 
create index acs_mail_bodies_item_id_idx ON acs_mail_bodies(content_item_id);
create index acs_mail_bodies_body_from_idx ON acs_mail_bodies(body_from);
create index acs_mail_bodies_body_reply_idx ON acs_mail_bodies(body_reply_to);

create table acs_mail_body_headers (
    body_id integer
        constraint acs_mail_body_heads_body_id_fk
            references acs_mail_bodies on delete cascade,
    header_name varchar2(1000),
    header_content varchar2(4000)
);

create index acs_mail_body_hdrs_body_id_idx
    on acs_mail_body_headers (body_id);

  -- MIME Multiparts

create table acs_mail_multiparts (
    multipart_id integer
        constraint acs_mail_multiparts_mp_id_pk primary key
        constraint acs_mail_multiparts_mp_id_fk
            references acs_mail_gc_objects on delete cascade,
    multipart_kind varchar2(120)
        constraint acs_mail_multiparts_mp_kind_nn not null
);

create table acs_mail_multipart_parts (
    multipart_id integer
        constraint acs_mail_mp_parts_mp_id_fk
            references acs_mail_multiparts
                on delete cascade,
    mime_filename varchar2(1000),
    mime_disposition varchar2(1000),
    sequence_number integer,
    content_item_id integer
        constraint acs_mail_mp_parts_c_obj_id_fk references cr_items,
    constraint acs_mail_multipart_parts_pk
        primary key (multipart_id, sequence_number)
);
--RI Index
create index acs_mail_mpp_cr_item_id_idx ON acs_mail_multipart_parts(content_item_id);

-- Mail Links

create table acs_mail_links (
    mail_link_id integer
        constraint acs_mail_links_ml_id_pk primary key
        constraint acs_mail_links_ml_id_fk references acs_objects 
        on delete cascade,
    body_id integer
        constraint acs_mail_links_body_id_nn not null
        constraint acs_mail_links_body_id_fk references acs_mail_bodies
);

--RI Index
create index acs_mail_links_body_id_idx ON acs_mail_links(body_id);


-- API -----------------------------------------------------------------

-- APIs for the datamodel in this file, separated out for future upgrades
@@ acs-mail-packages-create

-- Supporting Datamodels -----------------------------------------------

-- The mail queue datamodel
@@ acs-mail-queue-create

-- The notification package
@@ acs-mail-nt-create
