import { Pool } from 'pg';
import * as fs from 'fs';
import * as path from 'path';
import dotenv from 'dotenv';

let envLoaded = false;

function ensureEnvLoaded() {
  if (envLoaded) return;

  const cwd = process.cwd();
  const candidateFiles = [
    path.join(cwd, '.env.local'),
    path.join(cwd, '.env'),
  ];

  for (const filePath of candidateFiles) {
    if (fs.existsSync(filePath)) {
      dotenv.config({ path: filePath, override: false });
    }
  }

  envLoaded = true;
}

const globalForPg = globalThis as unknown as {
  pgPool?: Pool;
};

function assertDatabaseUrl(): string {
  ensureEnvLoaded();
  const value = process.env.DATABASE_URL;
  if (!value) {
    throw new Error('DATABASE_URL is not set. Configure PostgreSQL connection string first.');
  }
  return value;
}

export function getPgPool(): Pool {
  if (!globalForPg.pgPool) {
    globalForPg.pgPool = new Pool({
      connectionString: assertDatabaseUrl(),
      max: Number(process.env.PG_POOL_MAX || 20),
      idleTimeoutMillis: Number(process.env.PG_IDLE_TIMEOUT_MS || 30000),
      connectionTimeoutMillis: Number(process.env.PG_CONNECT_TIMEOUT_MS || 5000),
      application_name: 'edoo-next-app',
    });
  }
  return globalForPg.pgPool;
}

export async function pgQuery<T = any>(text: string, params: any[] = []) {
  return getPgPool().query<T>(text, params);
}

/** Tests whether a database connection can be established. */
export async function canConnect(): Promise<boolean> {
  try {
    const pool = getPgPool();
    const client = await pool.connect();
    client.release();
    return true;
  } catch {
    return false;
  }
}
