{ pkgs, ... }:

{
  services.postgresql = {
    enable = true;
    package = pkgs.postgresql_15;
    enableTCPIP = true;

    initialScript = pkgs.writeText "backend-init-script" ''
      CREATE USER postgres WITH SUPERUSER PASSWORD 'postgres';
    '';

    # x86_64 server optimized settings (8GB+ RAM assumed)
    settings = {
      port = 5432;

      # Memory settings
      shared_buffers = "2GB";
      effective_cache_size = "6GB";
      maintenance_work_mem = "512MB";
      work_mem = "16MB";
      wal_buffers = "16MB";

      # Connection settings
      max_connections = 100;

      # Performance tuning for x86_64 SSD
      random_page_cost = 1.1;
      effective_io_concurrency = 200;

      # WAL settings
      wal_level = "replica";
      checkpoint_timeout = "15min";
      checkpoint_completion_target = 0.9;
      min_wal_size = "1GB";
      max_wal_size = "4GB";

      # Query planning
      default_statistics_target = 100;

      # Logging
      log_min_duration_statement = 1000;
      log_duration = false;
    };

    authentication = ''
      local   all             all                                     trust
      host    all             all             127.0.0.1/32            md5
      host    all             all             ::1/128                 md5
      host    all             all             192.168.2.0/24          md5
    '';
  };

  systemd.services.postgresql.wantedBy = [ "multi-user.target" ];

  networking.firewall.allowedTCPPorts = [ 5432 ];
}