-- $Id$ 

CREATE SEQUENCE transport_id;
CREATE TABLE transport (
    tid         bigint NOT NULL DEFAULT nextval('transport_id'),
    transport   varchar(270) NOT NULL, -- smtps:[255-char.host.name:50025]
    CONSTRAINT pkey_transport PRIMARY KEY (tid),
    CONSTRAINT ukey_transport UNIQUE (transport)
);
-- Insert default transport
INSERT INTO transport(transport) VALUES ('dovecot:');

CREATE SEQUENCE maillocation_id;
CREATE TABLE maillocation(
    mid     bigint NOT NULL DEFAULT nextval('maillocation_id'),
    maillocation varchar(20) NOT NULL,
    CONSTRAINT pkey_maillocation PRIMARY KEY (mid),
    CONSTRAINT ukey_maillocation UNIQUE (maillocation)
);
-- Insert default Maildir-folder name
INSERT INTO maillocation(maillocation) VALUES ('Maildir');

CREATE SEQUENCE domains_gid
    START WITH 70000
    INCREMENT BY 1
    MINVALUE 70000
    MAXVALUE 4294967294
    NO CYCLE;

CREATE SEQUENCE users_uid
    START WITH 70000
    INCREMENT BY 1
    MINVALUE 70000
    MAXVALUE 4294967294
    NO CYCLE;

CREATE TABLE domains (
    gid         bigint NOT NULL DEFAULT nextval('domains_gid'),
    tid         bigint NOT NULL DEFAULT 1, -- defualt transport
    domainname  varchar(255) NOT NULL,
    domaindir   varchar(40) NOT NULL, --/srv/mail/$RAND/4294967294
    CONSTRAINT pkey_domains PRIMARY KEY (gid),
    CONSTRAINT ukey_domains UNIQUE (domainname),
    CONSTRAINT fkey_domains_tid_transport FOREIGN KEY (tid)
        REFERENCES transport (tid)
);

CREATE TABLE users (
    local_part  varchar(64) NOT NULL,-- only localpart w/o '@'
    passwd      varchar(74) NOT NULL,-- {CRAM-MD5}+64hex numbers
    name        varchar(128) NULL,
    uid         bigint NOT NULL DEFAULT nextval('users_uid'),
    gid         bigint NOT NULL,
    mid         bigint NOT NULL DEFAULT 1,
    tid         bigint NOT NULL DEFAULT 1,
    disabled    boolean NOT NULL DEFAULT FALSE,
    CONSTRAINT pkye_users PRIMARY KEY (local_part, gid),
    CONSTRAINT ukey_users_uid UNIQUE (uid),
    CONSTRAINT fkey_users_gid_domains FOREIGN KEY (gid)
        REFERENCES domains (gid),
    CONSTRAINT fkey_users_mid_maillocation FOREIGN KEY (mid)
        REFERENCES maillocation (mid),
    CONSTRAINT fkey_users_tid_transport FOREIGN KEY (tid)
        REFERENCES transport (tid)
);

CREATE TABLE alias (
    gid         bigint NOT NULL,
    address     varchar(256) NOT NULL,
    destination varchar(320) NOT NULL,
    CONSTRAINT pkey_alias PRIMARY KEY (gid, address, destination),
    CONSTRAINT fkey_alias_gid_domains FOREIGN KEY (gid)
        REFERENCES domains (gid)
);

CREATE TABLE relocated (
    gid         bigint NOT NULL,
    address     varchar(64) NOT NULL,
    destination varchar(320) NOT NULL,
    CONSTRAINT pkey_relocated PRIMARY KEY (gid, address),
    CONSTRAINT fkey_relocated_gid_domains FOREIGN KEY (gid)
        REFERENCES domains (gid)
);

CREATE OR REPLACE VIEW dovecot_password AS
    SELECT local_part || '@' || domains.domainname AS "user",
           passwd AS "password"
      FROM users
           LEFT JOIN domains USING (gid);

CREATE OR REPLACE VIEW dovecot_user AS
    SELECT local_part || '@' || domains.domainname AS userid,
           domains.domaindir || '/' || uid AS home, uid, gid
      FROM users
           LEFT JOIN domains USING (gid);

CREATE OR REPLACE VIEW postfix_gid AS
    SELECT gid, domainname
      FROM domains;

CREATE OR REPLACE VIEW postfix_uid AS
    SELECT local_part || '@' || domains.domainname AS address, uid
      FROM users
           LEFT JOIN domains USING (gid);

CREATE OR REPLACE VIEW postfix_maildir AS
    SELECT local_part || '@' || domains.domainname AS address,
           domains.domaindir||'/'||uid||'/'||maillocation.maillocation||'/' AS maildir
      FROM users
           LEFT JOIN domains USING (gid)
           LEFT JOIN maillocation USING (mid);

CREATE OR REPLACE VIEW postfix_relocated AS
    SELECT address || '@' || domains.domainname AS address, destination
      FROM relocated
           LEFT JOIN domains USING (gid);

CREATE OR REPLACE VIEW postfix_alias AS
    SELECT address || '@' || domains.domainname AS address, destination, gid
      FROM alias
           LEFT JOIN domains USING (gid);

CREATE OR REPLACE VIEW postfix_transport AS
    SELECT local_part || '@' || domains.domainname AS address,
           transport.transport
      FROM users
           LEFT JOIN transport USING (tid)
           LEFT JOIN domains USING (gid);

CREATE OR REPLACE VIEW vmm_alias_count AS
    SELECT count(DISTINCT address) AS aliases, gid
      FROM alias 
  GROUP BY gid;

CREATE OR REPLACE VIEW vmm_domain_info AS
    SELECT gid, domainname, transport, domaindir, count(uid) AS accounts,
           aliases
      FROM domains
           LEFT JOIN transport USING (tid)
           LEFT JOIN users USING (gid)
           LEFT JOIN vmm_alias_count USING (gid)
  GROUP BY gid, domainname, transport, domaindir, aliases;