--
-- Sequence "user_ids"
-- Name: user_ids; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE user_ids
    INCREMENT BY 1
    NOMAXVALUE
    NOMINVALUE
    NOCACHE;

--
-- Table "users"
-- Name: users; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE users (
    user_id integer PRIMARY KEY,
    username varchar2(128) NOT NULL,
    mail_host varchar2(128) DEFAULT '',
    alias varchar2(128) DEFAULT '',
    created timestamp DEFAULT SYSDATE NOT NULL,
    last_login timestamp DEFAULT SYSDATE NOT NULL,
    "language" varchar2(5) DEFAULT 'en' NOT NULL,
    preferences varchar2(4000) DEFAULT ''
);

CREATE TRIGGER users_user_id
BEFORE INSERT ON users REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
	SELECT user_ids.nextval into :NEW.user_id FROM DUAL;
END;
/
  
--
-- Table "session"
-- Name: session; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE "session" (
    sess_id varchar2(40) NOT NULL PRIMARY KEY,
    created timestamp DEFAULT SYSDATE NOT NULL,
    changed timestamp DEFAULT SYSDATE NOT NULL,
    ip varchar2(16) NOT NULL,
    vars varchar2(4000) NOT NULL
);



--
-- Sequence "identity_ids"
-- Name: identity_ids; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE identity_ids
    START WITH 1
    INCREMENT BY 1
    NOMAXVALUE
    NOMINVALUE
    NOCACHE;

--
-- Table "identities"
-- Name: identities; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE identities (
    identity_id integer NOT NULL PRIMARY KEY,
    user_id integer NOT NULL REFERENCES users (user_id),
    del integer DEFAULT 0 NOT NULL,
    standard integer DEFAULT 0 NOT NULL,
    name varchar2(128) NOT NULL,
    organization varchar2(128),
    email varchar2(128) NOT NULL,
    "reply-to" varchar2(128),
    bcc varchar2(128),
    signature varchar2(4000)
);

CREATE TRIGGER identities_identity_ids
BEFORE INSERT ON identities REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
        SELECT identity_ids.nextval into :NEW.identity_id FROM DUAL;
END;
/

--
-- Sequence "contact_ids"
-- Name: contact_ids; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE contact_ids
    START WITH 1
    INCREMENT BY 1
    NOMAXVALUE
    NOMINVALUE
    NOCACHE;

--
-- Table "contacts"
-- Name: contacts; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE contacts (
    contact_id integer NOT NULL PRIMARY KEY,
    user_id integer NOT NULL REFERENCES users (user_id),
    changed timestamp DEFAULT SYSDATE NOT NULL,
    del integer DEFAULT 0 NOT NULL,
    name varchar2(128),
    email varchar2(128),
    firstname varchar2(128),
    surname varchar2(128),
    vcard varchar2(4000)
);

CREATE TRIGGER contacts_contact_ids
BEFORE INSERT ON contacts REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
        SELECT contact_ids.nextval into :NEW.contact_id FROM DUAL;
END;
/


--
-- Sequence "cache_ids"
-- Name: cache_ids; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE cache_ids
    INCREMENT BY 1
    NOMAXVALUE
    NOMINVALUE
    NOCACHE;

--
-- Table "cache"
-- Name: cache; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE cache (
    cache_id integer NOT NULL PRIMARY KEY,
    user_id integer NOT NULL REFERENCES users (user_id),
    session_id varchar2(40) REFERENCES "session" (sess_id),
    cache_key varchar2(128),
    created timestamp DEFAULT SYSDATE NOT NULL,
    data varchar(4000)
);

CREATE TRIGGER cache_cache_ids
BEFORE INSERT ON cache REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
        SELECT cache_ids.nextval into :NEW.cache_id FROM DUAL;
END;
/


--
-- Sequence "message_ids"
-- Name: message_ids; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE message_ids
    INCREMENT BY 1
    NOMAXVALUE
    NOMINVALUE
    NOCACHE;

--
-- Table "messages"
-- Name: messages; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE messages (
    message_id integer NOT NULL PRIMARY KEY,
    user_id integer NOT NULL REFERENCES users (user_id),
    del integer DEFAULT 0 NOT NULL,
    cache_key varchar2(128),
    created timestamp DEFAULT SYSDATE NOT NULL,
    idx integer DEFAULT 0 NOT NULL,
    "uid" integer DEFAULT 0 NOT NULL,
    subject varchar2(128),
    "from" varchar2(128),
    "to" varchar2(128),
    cc varchar2(128),
    "date" timestamp NOT NULL,
    "size" integer DEFAULT 0 NOT NULL,
    headers varchar2(4000) NOT NULL,
    body varchar2(4000)
);

CREATE TRIGGER messages_message_ids
BEFORE INSERT ON messages REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
        SELECT message_ids.nextval into :NEW.message_id FROM DUAL;
END;
/

-- Emulate MySQL's UNIX_TIMESTAMP function
CREATE OR REPLACE
    FUNCTION UNIX_TIMESTAMP(oracle_date IN DATE) RETURN PLS_INTEGER IS
        /**
         * Converts an Oracle DATE to a UNIX timestamp
         */
        unix_epoch DATE := TO_DATE('19700101000000','YYYYMMDDHH24MISS');
        max_date DATE := TO_DATE('20380101000000','YYYYMMDDHH24MISS');
        min_date DATE := TO_DATE('19030101000000','YYYYMMDDHH24MISS');
        unix_ts PLS_INTEGER;
        
        BEGIN
            
            IF oracle_date > max_date THEN
                RAISE_APPLICATION_ERROR( -20902,'Date too large for 32bit UNIX timestamp' );
            ELSIF oracle_date < min_date THEN
                RAISE_APPLICATION_ERROR( -20902,'Date too small for 32bit UNIX timestamp' );
            ELSE
                unix_ts := (oracle_date - unix_epoch) / (1/86400);
            END IF;
            
            RETURN (unix_ts);
        
        END;
/

-- Emulate MySQL's NOW function
CREATE OR REPLACE
    FUNCTION NOW RETURN TIMESTAMP IS
        /**
         * Converts an Oracle DATE to a UNIX timestamp
         */
        BEGIN
            RETURN (SYSDATE);
        END;
/
