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 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 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,
    domainname  varchar(255) NOT NULL,
    domaindir   varchar(40) NOT NULL,
    CONSTRAINT pkey_domains PRIMARY KEY (gid),
    CONSTRAINT ukey_domains UNIQUE (domainname),
    CONSTRAINT fkey_domains_tid_transport FOREIGN KEY (tid)
        REFERENCES transport (tid)
);

CREATE TABLE alias_domains (
    domainname  varchar(255) NOT NULL,
    gid         bigint NOT NULL,
    CONSTRAINT pkey_alias_domains PRIMARY KEY (domainname),
    CONSTRAINT fkey_alias_domains_gid_domains FOREIGN KEY (gid)
        REFERENCES domains (gid)
);

CREATE TABLE users (
    local_part  varchar(64) NOT NULL,
    passwd      varchar(74) NOT NULL,
    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,
    smtp        boolean NOT NULL DEFAULT TRUE,
    pop3        boolean NOT NULL DEFAULT TRUE,
    imap        boolean NOT NULL DEFAULT TRUE,
    managesieve boolean NOT NULL DEFAULT TRUE,
    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 LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION getdomain(name varchar(255)) RETURNS domains AS $$
DECLARE
    dom domains;
BEGIN
    SELECT INTO dom *
      FROM domains
     WHERE domainname = name;
    IF NOT FOUND THEN
        SELECT INTO dom *
          FROM domains
         WHERE gid = (SELECT gid FROM alias_domains WHERE domainname = name);
    END IF;
    RETURN dom;
END;
$$ LANGUAGE plpgsql;

CREATE TYPE dovecot_user AS (
    userid  text,
    uid     bigint,
    gid     bigint,
    home    text,
    mail    text
);

CREATE OR REPLACE FUNCTION get_dovecot_user(
    lpart varchar, dpart varchar) RETURNS dovecot_user AS $$
DECLARE
    du dovecot_user;
BEGIN
    SELECT INTO STRICT du
           local_part || '@' || (getdomain(dpart)).domainname AS userid,
           uid,
           gid,
           (getdomain(dpart)).domaindir || '/' || uid AS home,
           '~/' || maillocation.maillocation AS mail
      FROM users
           LEFT JOIN domains USING (gid)
           LEFT JOIN maillocation USING (mid)
     WHERE local_part = lpart
       AND (getdomain(dpart)).gid = users.gid;
    RETURN du;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            RAISE EXCEPTION 'No such user: %@%', lpart, dpart;
END;
$$ LANGUAGE plpgsql STABLE
EXTERNAL SECURITY INVOKER;

-- ---
-- Example:
-- ---
-- ---
--| SELECT * FROM get_dovecot_user('a.user', 'a-hoernchen.de');
--|            userid            |  uid  |  gid  |          home           |   mail    
--| -----------------------------+-------+-------+-------------------------+-----------
--|  a.user@xn--a-hrnchen-27a.de | 70039 | 70172 | /srv/mail/a/70172/70039 | ~/Maildir
-- ---