CREATE TABLE addresses (
    username text NOT NULL,
    localpart text NOT NULL,
    DOMAIN text NOT NULL,
    created TIMESTAMP WITH TIME zone DEFAULT now()
);
 
CREATE TABLE user_accounts (
    username text,
    password text,
    created TIMESTAMP WITH TIME zone
);
 
CREATE TABLE user_addresses (
    username text,
    localpart text,
    DOMAIN text,
    created TIMESTAMP WITH TIME zone
);
 
CREATE VIEW dovecot_password_query AS
 SELECT user_accounts.username AS "user",
    user_accounts.password
   FROM user_accounts
UNION
 SELECT concat(user_addresses.localpart, '@', user_addresses.domain) AS "user",
    user_accounts.password
   FROM user_addresses,
    user_accounts
  WHERE (user_addresses.username = user_accounts.username);
 
CREATE VIEW dovecot_user_query AS
 SELECT user_accounts.username AS KEY,
    user_accounts.username AS "user",
    concat('mdbox:/var/vmail/', user_accounts.username, '/mdbox') AS mail,
    concat('/var/vmail/', user_accounts.username) AS home,
    5000 AS uid,
    5000 AS gid
   FROM user_accounts
UNION
 SELECT concat(user_addresses.localpart, '@', user_addresses.domain) AS KEY,
    user_accounts.username AS "user",
    concat('mdbox:/var/vmail/', user_accounts.username, '/mdbox') AS mail,
    concat('/var/vmail/', user_accounts.username) AS home,
    5000 AS uid,
    5000 AS gid
   FROM (user_addresses
     JOIN user_accounts ON ((user_addresses.username = user_accounts.username)));
 
CREATE TABLE external_accounts (
    username text NOT NULL,
    address text NOT NULL,
    smtpuser text NOT NULL,
    getuser text NOT NULL,
    smtppassword text NOT NULL,
    getpassword text NOT NULL,
    smtpssl INTEGER DEFAULT 1 NOT NULL,
    getssl INTEGER DEFAULT 1 NOT NULL
);
 
CREATE TABLE mail_forwards (
    from_address text,
    to_address text
);
 
CREATE VIEW postfix_smtpd_sender_login_maps AS
 SELECT user_accounts.username,
    concat(user_addresses.localpart, '@', user_addresses.domain) AS address
   FROM (user_accounts
     LEFT JOIN user_addresses ON ((user_accounts.username = user_addresses.username)))
UNION
 SELECT concat(a.localpart, '@', a.domain) AS username,
    concat(b.localpart, '@', b.domain) AS address
   FROM (user_addresses a
     LEFT JOIN user_addresses b ON ((a.username = b.username)));
 
CREATE TABLE standard_addresses (
    alias text
);
 
CREATE VIEW postfix_virtual_alias_maps AS
 SELECT concat(standard_addresses.alias, '@', user_addresses.domain) AS address,
    standard_addresses.alias
   FROM standard_addresses,
    user_addresses
  GROUP BY standard_addresses.alias, user_addresses.domain
UNION
 SELECT mail_forwards.from_address AS address,
    string_agg(mail_forwards.to_address, ', '::text) AS alias
   FROM mail_forwards
  GROUP BY mail_forwards.from_address;
 
CREATE VIEW postfix_virtual_mailbox_domains AS
 SELECT user_addresses.domain
   FROM user_addresses
  GROUP BY user_addresses.domain;
 
CREATE VIEW postfix_virtual_mailbox_maps AS
 SELECT concat(user_addresses.localpart, '@', user_addresses.domain) AS email
   FROM user_addresses;
 
CREATE TABLE rfc2142_addresses (
    alias text NOT NULL
);
 
CREATE UNIQUE INDEX addresses_localpart_domain_idx ON addresses USING btree (localpart, DOMAIN);