drop table part;
drop table encoding;
drop table header;
drop table recipient;
drop table message;

drop sequence message_id_generator;
drop sequence recipient_id_generator;
drop sequence header_id_generator;
drop sequence attachment_id_generator;
drop sequence part_id_generator;


--------------------------------------------------------------------------------
-- Sequences for primary keys
--
create sequence MESSAGE_ID_GENERATOR start 1;
create sequence RECIPIENT_ID_GENERATOR start 1;
create sequence HEADER_ID_GENERATOR start 1;
create sequence ATTACHMENT_ID_GENERATOR start 1;
create sequence PART_ID_GENERATOR start 1;


--------------------------------------------------------------------------------
-- Table MESSAGE
--
-- Store an RFC822-compliant message.
--
create table MESSAGE
( -- unique message ID, automatically generated
  message_id  integer  default nextval('MESSAGE_ID_GENERATOR'),

  -- subject of the message (arbitrary text)
  subject  text  not null,

  -- the email address of the message's sender
  sender_name  varchar(150)  not null,

  -- the real name of the message's sender (if any)
  sender_address  varchar(150)  not null,

  -- the time that the message was sent
  time_sent  timestamp with time zone  not null,

  -- message priority
  priority  char(7)
    default 'normal'
    not null
    check (priority in ('Lowest', 'Low', 'Normal', 'High', 'Highest')),

  -- raw headers of the message (arbitrary text)
  -- as per RFC822, everything up to the first blank line
  raw_headers  text  not null,

  -- raw body of the message (arbitrary text)
  -- as per RFC822, everything after the first blank line
  raw_content  text  not null,

  -- MD5 hash of the message body, for quick comparison purposes and
  -- indexing
  content_hash  char(32)  not null,

  -- raw source of the message (arbitrary text)
  -- = headers + blank line + body
  raw_source  text  not null,
  
  -- is the message body written in HTML?
  is_html  boolean
    default false
    not null,
  
  -- is the message a multipart MIME message?
  is_multipart  boolean
    default false
    not null,
  
  -- primary key
  primary key (message_id)
);


--------------------------------------------------------------------------------
-- Table RECIPIENT
--
-- Store recipients for an RFC822-compliant message.
--
create table RECIPIENT
( -- unique recipient ID, automatically generated
  recipient_id  integer  default nextval('RECIPIENT_ID_GENERATOR'),
  
  -- recipient's display name (if any)
  recipient_name  varchar(150)  not null,
  
  -- recipient's email address
  address  varchar(150)  not null,
  
  -- type of recipient (To, Cc or Bcc)
  recipient_type  char(3)
    default 'to'
    not null
    check (recipient_type in ('To', 'Cc', 'Bcc')),

  -- ID of message that this recipient belongs to
  message_id  integer  not null,
  
  -- primary key
  primary key (recipient_id),
  
  -- foreign key to MESSAGE
  foreign key (message_id) references MESSAGE
);


--------------------------------------------------------------------------------
-- Table ENCODING
--
-- Lookup table of various encoding types.
--
create table ENCODING
( -- unqiue encoding ID
  encoding_id  integer,
  
  -- name of encoding
  encoding_name  varchar(20)  not null,
  
  -- primary key
  primary key (encoding_id)
);

--------------------------------------------------------------------------------
-- Defined encoding types
--

-- Unspecified encodings should be dealt with at the application level.
insert into encoding (encoding_id, encoding_name) values (1, 'unspecified');

-- The most common encodings appear to be 7bit, 8bit, base64 and quoted-printable.
insert into encoding (encoding_id, encoding_name) values (2, '7bit');
insert into encoding (encoding_id, encoding_name) values (3, '8bit');
insert into encoding (encoding_id, encoding_name) values (4, 'binhex');
insert into encoding (encoding_id, encoding_name) values (5, 'base64');
insert into encoding (encoding_id, encoding_name) values (6, 'uuencode');
insert into encoding (encoding_id, encoding_name) values (7, 'applesingle');
insert into encoding (encoding_id, encoding_name) values (8, 'appledouble');
insert into encoding (encoding_id, encoding_name) values (9, 'quoted-printable');

-- Any other encodings encountered should be added to this table as required.


--------------------------------------------------------------------------------
-- Table PART
--
-- Store parts of a multi-part MIME message. This includes attachments.
--
create table PART
( -- unique part ID, automatically generated
  part_id  integer  default nextval('PART_ID_GENERATOR'),
  
  -- MIME type of the part data
  mime_type  varchar(30)  not null,
  
  -- MIME subtype of the part data
  mime_subtype  varchar(50)  not null,
  
  -- type of encoding (reference to ENCODING lookup table)
  encoding_id  integer
    default 1
    not null,

  -- raw headers of the part (arbitrary text)
  part_raw_headers  text  not null,

  -- raw body of the part (arbitrary text)
  part_raw_content  text  not null,
  
  -- is this part an attachment? (either implied by giving the part a name
  -- or explicitly specified using a Content-disposition header)
  is_attachment  boolean  not null,
  
  -- if this part is an attachment, store its name here
  attachment_name  varchar(150),
  
  -- ID of message that this part belongs to
  message_id  integer  not null,

  -- ID of part that this part belongs to (if any)
  parent_part  integer,

  -- primary key
  primary key (part_id),
  
  -- foreign key to ENCODING
  foreign key (encoding_id) references ENCODING,

  -- foreign key to MESSAGE
  foreign key (message_id) references MESSAGE,

  -- foreign key to PART
  foreign key (parent_part) references PART
);


--------------------------------------------------------------------------------
-- Table HEADER
--
-- Store additional headers of an RFC822-compliant message.
--
create table HEADER
( -- unique header ID, automatically generated
  header_id  integer  default nextval('HEADER_ID_GENERATOR'),
  
  -- name of the header (e.g., "X-Mailer" or "Return-Path")
  header_name  varchar(50)  not null,
  
  -- value of the header (arbitrary text)
  header_value  text  not null,
  
  -- ID of message that this header belongs to
  message_id  integer  not null,
  
  -- ID of message part that this header belongs to (if applicable)
  part_id  integer  not null,
  
  -- primary key
  primary key (header_id),
  
  -- foreign key to MESSAGE
  foreign key (message_id) references MESSAGE,
  
  -- foreign key to PART
  foreign key (message_id) references MESSAGE
);


--------------------------------------------------------------------------------
-- Table TEST_DATE
--
-- A temporary table for quickly testing whether we can insert a particular
-- date value into the .
--
create table TEST_DATE
( -- timestamp column
  a_date  timestamp with time zone
);