{ db, owner ? db, ... }:
{ pkgs, lib, config, ... }:
let
  sql = pkgs.fetchzip {
    url = "https://www.openconcerto.org/fr/telechargement/1.6/OpenConcerto-1.6.3.sql.zip";
    sha256 = "02h35ni9xknzrjsra56c3zhlhs0ji9qc61kcgi7vgcpylqjw0s6n";
  };
  inherit (config.security) gnupg;
  inherit (config.users) users groups;
  inherit (config) networking;
  # Example of ~/.config/OpenConcerto/main.properties
  # DOC: https://code.openconcerto.org/filedetails.php?repname=OpenConcerto&path=%2Ftrunk%2FOpenConcerto%2Fsrc%2Forg%2Fopenconcerto%2Fsql%2FPropsConfiguration.java
  # DOC: https://jdbc.postgresql.org/documentation/head/connect.html
  "main.properties" = ''
    base.root=Common
    customer=Gestion_Default
    jdbc.connection.ssl=true
    jdbc.connection.sslmode=require
    server.driver=postgresql
    server.ip=openconcerto.${networking.domain}\:5432
    server.login=${owner}
    server.password=TheSecretPassword
    systemRoot=${db}
  '';
  # To be used in postStart when resetting the database
  drop = ''
    $PSQL -d template1 -AqtX --set ON_ERROR_STOP=1 -f - <<EOF
      DROP OWNED BY ${owner};
      DROP DATABASE ${db};
      DROP ROLE     ${owner};
    EOF
  '';
in
{
  services.postgresql = {
    authentication = lib.mkForce ''
      # CONNECTION  DATABASE USER      AUTH  OPTIONS
      # FIXME: using scram-sha-256 instead of md5 requires postfix >= 11
      hostssl       ${db}    ${owner}  all   md5
    '';
    identMap = ''
      # MAPNAME  SYSTEM-USERNAME  PG-USERNAME
      user       root             ${owner}
      user       ${owner}         ${db}
    '';
  };
  security.gnupg.secrets."postgresql/pass/${owner}" = { };
  systemd.services.postgresql = {
    after = [ gnupg.secrets."postgresql/pass/${owner}".service ];
    wants = [ gnupg.secrets."postgresql/pass/${owner}".service ];
    postStart = lib.mkAfter ''
      sed -e 's/ \(TO\|FROM\) \+openconcerto/ \1 ${owner}/g' \
       ${sql}/OpenConcerto-1.6.3.sql |
      connection_limit=64 \
      encoding=UTF8 \
      lc_collate=fr_FR.UTF-8 \
      lc_type=fr_FR.UTF-8 \
      owner=${owner} \
      pass=$(cat ${gnupg.secrets."postgresql/pass/${owner}".path}) \
      pg_createdb ${db} >/dev/null

      $PSQL -d "${db}" -AqtX --set ON_ERROR_STOP=1 -f - <<EOF
        -- Reallow this to avoid the error:
        -- "Couldn't refresh the graph"
        -- when testing the connexion to the database
        -- in OpenConcerto-Configuration.sh
        GRANT SELECT ON pg_catalog.pg_settings TO ${owner};
        -- Reallow this to allow pg_dump
        GRANT SELECT ON pg_catalog.pg_database   TO ${owner};
        GRANT SELECT ON pg_catalog.pg_roles      TO ${owner};
        GRANT SELECT ON pg_catalog.pg_tablespace TO ${owner};
        -- Reallow this to allow pgadmin3
        GRANT SELECT ON pg_catalog.pg_user       TO ${owner};

        -- Enable PL/PGSQL
        CREATE OR REPLACE LANGUAGE plpgsql;
      EOF
    '';
  };
}