--
-- 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.5 2006/09/25 20:47:36 byronl 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.)

select acs_object_type__create_type (
    'acs_mail_gc_object',
    'ACS Mail Object',
    'ACS Mail Objects',
    'acs_object',
    'acs_mail_gc_objects',
    'gc_object_id',
    'acs_mail_gc_object',
    'f',
    null,
    'acs_object.default_name'
);

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

select acs_object_type__create_type (
    'acs_mail_body',
    'ACS Mail Body',
    'ACS Mail Bodies',
    'acs_mail_gc_object',
    'acs_mail_bodies',
    'body_id',
    'acs_mail_body',
    'f',
    null,
    'acs_object.default_name'
);

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

select acs_object_type__create_type (
    'acs_mail_multipart',
    'ACS Mail Multipart Object',
    'ACS Mail Multipart Objects',
    'acs_mail_gc_object',
    'acs_mail_multiparts',
    'multipart_id',
    'acs_mail_multipart',
    'f',
    null,
    'acs_object.default_name'
);

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

select acs_object_type__create_type (
    'acs_mail_link',
    'ACS Mail Message',
    'ACS Mail Messages',
    'acs_object',
    'acs_mail_links',
    'mail_link_id',
    'acs_mail_link',
    'f',
    null,
    'acs_object.default_name'
);

-- 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 on delete cascade
);

  -- 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			timestamptz,
    header_message_id	varchar(1000)
						constraint acs_mail_bodies_h_m_id_un 
						unique
						constraint acs_mail_bodies_h_m_id_nn 
						not null,
    header_reply_to		varchar(1000),
    header_subject		text,
    header_from			text,
    header_to			text,
	-- content_item_id is a reference to acs_objects
	-- if you are creating a simple message,
	--   we expect you to create your CR item first before
	--   calling acs_mail_bodies__new, so content_item_id will
	--   refer to a cr_item
	-- if you are creating a multipart message,
	--   then create a acs_mail_multipart first and then supply
	--   the multipart_id as the content_item_id
    content_item_id		integer
						constraint acs_mail_bodies_content_iid_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			varchar(1000),
    header_content		text
);

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		varchar(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		varchar(1000),
    mime_disposition	varchar(1000),
    sequence_number		integer,
    content_item_id		integer
						constraint acs_mail_mp_parts_c_itm_id_fk 
						references cr_items on delete cascade,
    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_mail_link_id_pk 
				 primary key
				 constraint acs_mail_links_mail_link_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 on delete cascade
);

-- 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
\i acs-mail-packages-create.sql

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

-- The mail queue datamodel
\i acs-mail-queue-create.sql

-- The notification package
\i acs-mail-nt-create.sql