import type { PoolConfig } from 'pg';

function parseOptionalBoolean(value: string | undefined): boolean | undefined {
  if (typeof value !== 'string') return undefined;
  const normalized = value.trim().toLowerCase();
  if (['1', 'true', 'yes', 'on', 'require'].includes(normalized)) return true;
  if (['0', 'false', 'no', 'off', 'disable'].includes(normalized)) return false;
  return undefined;
}

function getSslModeFromDatabaseUrl(): string | null {
  const rawUrl = process.env.DATABASE_URL;
  if (!rawUrl) return null;

  try {
    return new URL(rawUrl).searchParams.get('sslmode');
  } catch {
    return null;
  }
}

function shouldUseDatabaseSsl(): boolean {
  const explicit = parseOptionalBoolean(process.env.DATABASE_SSL);
  if (explicit !== undefined) return explicit;

  const sslMode = (process.env.PGSSLMODE || getSslModeFromDatabaseUrl() || '').trim().toLowerCase();
  return sslMode !== '' && sslMode !== 'disable';
}

function buildDatabaseSslConfig(): PoolConfig['ssl'] | undefined {
  if (!shouldUseDatabaseSsl()) return undefined;

  const rejectUnauthorized = parseOptionalBoolean(process.env.DATABASE_SSL_REJECT_UNAUTHORIZED);
  return {
    rejectUnauthorized: rejectUnauthorized !== false,
  };
}

function buildConnectionConfig(): Pick<PoolConfig, 'connectionString' | 'host' | 'port' | 'database' | 'user' | 'password'> {
  if (process.env.DATABASE_URL) {
    return { connectionString: process.env.DATABASE_URL };
  }

  return {
    host: process.env.PGHOST || '127.0.0.1',
    port: parseInt(process.env.PGPORT || '5433', 10),
    database: process.env.PGDATABASE || 'eventheodds_sports',
    user: process.env.PGUSER || 'eventheodds',
    password: process.env.PGPASSWORD || 'eventheodds_dev_password',
  };
}

export function buildDatabasePoolConfig(overrides: PoolConfig = {}): PoolConfig {
  const ssl = buildDatabaseSslConfig();

  return {
    ...buildConnectionConfig(),
    max: 20,
    idleTimeoutMillis: 30_000,
    connectionTimeoutMillis: 5_000,
    maxLifetimeSeconds: 300,
    ...(ssl ? { ssl } : {}),
    ...overrides,
  };
}