{ pkgs, sources, ... }:
let
  versioning = sources.psql-versioning.src;
in {
  config = {
    services.postgresql = {
      enable = true;
      package = pkgs.postgresql_14;
      initialScript = pkgs.writeText "schema.sql" ''
        CREATE DATABASE "matrix-synapse" WITH TEMPLATE "template0" ENCODING "UTF8" LOCALE "C";
        CREATE USER "matrix-synapse";
        GRANT ALL PRIVILEGES ON DATABASE "matrix-synapse" TO "matrix-synapse";
        GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO "matrix-synapse";

        CREATE DATABASE "email" WITH TEMPLATE "template0" ENCODING "UTF8" LOCALE "C";
        CREATE USER "postfix";
        GRANT CONNECT ON DATABASE "email" TO "postfix";
        ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO "postfix";
        CREATE USER "dovecot2";
        GRANT CONNECT ON DATABASE "email" TO "dovecot2";
        ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO "dovecot2";
      '';
    };

    systemd.services.postgresql = {
      postStart = ''
        psql email postgres -eXf ${pkgs.writeText "email.sql" ''
          \i ${versioning + "/install.versioning.sql"}

          BEGIN;
          SELECT _v.register_patch('000-base', null, null);

          CREATE TABLE mailbox (
            id uuid PRIMARY KEY NOT NULL DEFAULT gen_random_uuid(),
            mailbox text NOT NULL CONSTRAINT mailbox_non_empty CHECK (mailbox <> '''),
            quota_bytes bigint CONSTRAINT quota_bytes_positive CHECK (CASE WHEN quota_bytes IS NOT NULL THEN quota_bytes > 0 ELSE true END),
            CONSTRAINT mailbox_unique UNIQUE (mailbox)
          );
          CREATE TABLE mailbox_mapping (
            id uuid PRIMARY KEY NOT NULL DEFAULT gen_random_uuid(),
            local text CONSTRAINT local_non_empty CHECK (local IS DISTINCT FROM '''),
            domain text NOT NULL CONSTRAINT domain_non_empty CHECK (domain <> '''),
            mailbox uuid REFERENCES mailbox(id),
            CONSTRAINT local_domain_unique UNIQUE (local, domain)
          );
          CREATE UNIQUE INDEX domain_unique ON mailbox_mapping (domain) WHERE local IS NULL;

          CREATE VIEW virtual_mailbox_domain (domain) AS SELECT DISTINCT domain FROM mailbox_mapping;
          CREATE VIEW virtual_mailbox_mapping (mailbox, lookup) AS SELECT mailbox.mailbox as mailbox, (CASE WHEN local IS NULL THEN ''' ELSE local END) || '@' || domain AS lookup FROM mailbox_mapping INNER JOIN mailbox on mailbox.id = mailbox_mapping.mailbox;

          CREATE VIEW imap_user ("user", quota_rule) AS SELECT mailbox AS "user", (CASE WHEN quota_bytes IS NULL THEN '*:ignore' ELSE '*:bytes=' || quota_bytes END) AS quota_rule FROM mailbox;
          COMMIT;

          BEGIN;
          SELECT _v.register_patch('001-lmtp-mapping', ARRAY['000-base'], null);

          CREATE VIEW lmtp_mapping ("user", quota_rule, local, domain) AS SELECT mailbox.mailbox AS "user", (CASE WHEN quota_bytes IS NULL THEN '*:ignore' ELSE '*:bytes=' || quota_bytes END) AS quota_rule, local, domain FROM mailbox INNER JOIN mailbox_mapping ON mailbox.id = mailbox_mapping.mailbox;
          COMMIT;

          BEGIN;
          SELECT _v.register_patch('002-citext', ARRAY['000-base'], null);

          CREATE EXTENSION citext;
          ALTER TABLE mailbox ALTER mailbox TYPE citext;
          ALTER TABLE mailbox_mapping ALTER local TYPE citext;
          ALTER TABLE mailbox_mapping ALTER domain TYPE citext;
          COMMIT;
        ''}
      '';
    };
  };
}