--
-- Yukatan SQL schema, version 0.6
-- http://hukka.greywolves.org/Yukatan.html
--
-- Copyright (c) 2003, Jukka Zitting <hukka@iki.fi>
-- All rights reserved.
--

CREATE DATABASE yukatan WITH ENCODING 'UNICODE';
\connect yukatan



CREATE TABLE message (
        msgno           SERIAL PRIMARY KEY,
        msgtype         CHARACTER VARYING NOT NULL
                        CHECK (msgtype = 'received' OR
                               msgtype = 'sent' OR
                               msgtype = 'draft' OR
                               msgtype = 'template' OR
                               msgtype = 'saved')
                        DEFAULT 'saved',
        msgtimestamp    TIMESTAMP WITH TIME ZONE NOT NULL
                        DEFAULT CURRENT_TIMESTAMP,
        envsender       CHARACTER VARYING,
        envrecipient    CHARACTER VARYING,
        msgdate         TIMESTAMP WITH TIME ZONE,
        msgsendername   CHARACTER VARYING,
        msgsenderaddress CHARACTER VARYING,
        msgmessageid    CHARACTER VARYING,
        msgsubject      CHARACTER VARYING,
        msgbody         TEXT,
        msgsource       BYTEA
);



CREATE TABLE headerfield (
        msgno           INTEGER NOT NULL
                        REFERENCES message
                        ON UPDATE CASCADE ON DELETE CASCADE,
        fieldno         INTEGER NOT NULL,
        fieldname       CHARACTER VARYING NOT NULL,
        fieldbody       CHARACTER VARYING NOT NULL,
        PRIMARY KEY (msgno, fieldno)
);



CREATE TABLE inreplytofield (
        msgno           INTEGER NOT NULL
                        REFERENCES message
                        ON UPDATE CASCADE ON DELETE CASCADE,
        inreplytono     INTEGER NOT NULL,
        inreplytoid     CHARACTER VARYING NOT NULL,
        PRIMARY KEY (msgno, inreplytono)
);



CREATE TABLE referencesfield (
        msgno           INTEGER NOT NULL
                        REFERENCES message
                        ON UPDATE CASCADE ON DELETE CASCADE,
        referencesno    INTEGER NOT NULL,
        referencesid    CHARACTER VARYING NOT NULL,
        PRIMARY KEY (msgno, referencesno)
);



CREATE TABLE fromfield (
        msgno           INTEGER NOT NULL
                        REFERENCES message
                        ON UPDATE CASCADE ON DELETE CASCADE,
        fromno          INTEGER NOT NULL,
        fromname        CHARACTER VARYING,
        fromaddress     CHARACTER VARYING NOT NULL,
        PRIMARY KEY (msgno, fromno)
);



CREATE TABLE replytofield (
        msgno           INTEGER NOT NULL
                        REFERENCES message
                        ON UPDATE CASCADE ON DELETE CASCADE,
        replytono       INTEGER NOT NULL,
        replytoname     CHARACTER VARYING,
        replytoaddress  CHARACTER VARYING NOT NULL,
        PRIMARY KEY (msgno, replytono)
);



CREATE TABLE tofield (
        msgno           INTEGER NOT NULL
                        REFERENCES message
                        ON UPDATE CASCADE ON DELETE CASCADE,
        tono            INTEGER NOT NULL,
        toname          CHARACTER VARYING,
        toaddress       CHARACTER VARYING NOT NULL,
        PRIMARY KEY (msgno, tono)
);



CREATE TABLE ccfield (
        msgno           INTEGER NOT NULL
                        REFERENCES message
                        ON UPDATE CASCADE ON DELETE CASCADE,
        ccno            INTEGER NOT NULL,
        ccname          CHARACTER VARYING,
        ccaddress       CHARACTER VARYING NOT NULL,
        PRIMARY KEY (msgno, ccno)
);



CREATE TABLE bccfield (
        msgno           INTEGER NOT NULL
                        REFERENCES message
                        ON UPDATE CASCADE ON DELETE CASCADE,
        bccno           INTEGER NOT NULL,
        bccname         CHARACTER VARYING,
        bccaddress      CHARACTER VARYING NOT NULL,
        PRIMARY KEY (msgno, bccno)
);