{ pkgs, lib, config, ... }:
let
  db = "openconcerto1";
  owner = db;
  sql = pkgs.fetchzip {
    url = "https://www.openconcerto.org/fr/telechargement/1.6/OpenConcerto-1.6.3.sql.zip";
    sha256 = "02h35ni9xknzrjsra56c3zhlhs0ji9qc61kcgi7vgcpylqjw0s6n";
  };
  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
{
systemd.services.postgresql = {
  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 /run/keys/postgresql_pass_${owner}) \
    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};
      
      -- Enable PL/PGSQL
      CREATE OR REPLACE LANGUAGE plpgsql;
    EOF
  '';
};
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}
  '';
};
install.shellHook = ''
  pass "servers/losurdo/postgresql/pass/${owner}" |
  ssh "$target" install -D -m 0400 -o root -g root /dev/stdin \
   /run/keys/postgresql_pass_${owner}
'';
}