--
-- The Intrasite-Message Package
--
-- @author Richard Hamilton (richard.hamilton@webteamworks.com)
-- @version $Id:
--
--
-- GNU GPL v2
--


create table im_folder (
    folder_id             integer
                          not null
                          primary key,
    folder_name           varchar(50)
                          not null,
    user_id               integer,
    created_timestampz    timestamptz not null,
    immutable_p           boolean default 'f'
);

comment on column im_folder.immutable_p is '
Users will be able to create and delete folders without restriction. There is a need for
a means of distinguishing between user folders and default application folders that must
not be deleted. The immutable flag marks a folder as not available for deletion or
modification by any user.
';

create sequence im_folder_id_seq start 5;

insert into im_folder (folder_id, folder_name, created_timestampz, immutable_p)
	values (1, 'In-box', now(), 't');
insert into im_folder (folder_id, folder_name, created_timestampz, immutable_p)
	values (2, 'Sent-box', now(), 't');
insert into im_folder (folder_id, folder_name, created_timestampz, immutable_p)
	values (3, 'Drafts', now(), 't');
insert into im_folder (folder_id, folder_name, created_timestampz, immutable_p)
	values (4, 'Deleted', now(), 't');


create table im_address_mode (
    mode_id               integer
                          not null
                          primary key,
    mode_desc             varchar(8)
                          not null
);

insert into im_address_mode (mode_id, mode_desc) values (0,'Sent by');
insert into im_address_mode (mode_id, mode_desc) values (1,'To');
insert into im_address_mode (mode_id, mode_desc) values (2,'Cc');
insert into im_address_mode (mode_id, mode_desc) values (3,'Bcc');


create sequence im_message_id_seq;

create table im_message (
        message_id              integer
                                primary key,
        sender_user_id          integer
                                not null
                                constraint im_sender_user_id_fk
                                references users,
        subject                 varchar(500),
        body                    text,
        sent_p                  boolean default 'f',
        sent_timestamptz        timestamptz,
        priority_p              boolean default 'f',
        created_timestampz      timestamptz not null,
        deleted_p               boolean default 'f',
	deleted_timestampz      timestamptz
);

comment on table im_message is '
This is the main table for the package and is to hold the messages themselves.
';

comment on column im_message.message_id is '
Primary key.
';

comment on column im_message.sender_user_id is '
Constrained to reference the user_id of the openacs users table. Only registered users will
be able to send and receive messages.
';

comment on column im_folder.folder_id is '
Constrained to reference the folder_id of the im_folder table. A message can only be in one
folder at a time.
';

comment on column im_message.priority_p is '
We must provide the facility for a message to be entered but not be sent, and then permit
editing of the message right up until it has been sent. The default "Drafts" folder will
be used to store messages that have not been sent, however the sent_p flag is required to
prevent a sent message being edited irrespective of the folder in which it resides. The
folders are for user convenience and should not be used to store message meta-data. It will
probably make sense to prevent a sent message being moved into the "Drafts" folder to
avoid confusion.
';

comment on column im_message.priority_p is '
To mark a message has a higher priotity.
';


-- im_addressees
-- this table will store the user_ids of those users to whom a message is addressed.

create table im_addressees (
    message_id            integer
                          not null
                          constraint im_message_id_fk
                          references im_message ON DELETE CASCADE,
    addressee_id          integer
                          not null
                          constraint user_id_fk
                          references users,
    address_mode_id       integer
                          not null
                          constraint address_mode_id_fk
                          references im_address_mode,
    read_p                boolean default 'f',
    folder_id             integer default 1
                          not null
                          constraint im_folder_id_fk
                          references im_folder,
    deleted_p             boolean default 'f',
    deleted_timestampz    timestamptz,
    PRIMARY KEY (message_id, addressee_id, address_mode_id)
);

comment on table im_addressees is '
This table holds the addressee information and folder mapping information for each user. The
primary key is to create a unique constraint to ensure that each addressee can only be 
addressed once by each address mode. The folder_id column
';

comment on column im_addressees.folder_id is '
Constrained to reference the folder_id of the im_folder table. A message can only be in one
folder at a time. The folder_id column is to allow each user to have a received message in a 
folder of their choice. The folder information for the sender is stored here also and there
is a special addressee mode "0" that signifies "Sent-by".
';

comment on column im_addressees.deleted_p is '
When a message is deleted by the sender, the information is stored in the im_messages table.
However a user that has received that message must still be able to read it after the sender 
has deleted it and so must have independent control of the message status. The intention is
that the message display query will not respect the im_message delete status but rather will
refer to the status info held here for each addressee.
';
comment on column im_addressees.read_p is '
Whether an addressee has read the message. This attribute is not really relevant for the sender
and so this special case will be handled by setting read_p to true for the sender during the 
message send process.
';

-- Need to add a table to hold attachments. Table should be able to hold images and other
-- files in blob fields.

