import { Pool, QueryResult, QueryResultRow } from 'pg';

let _pool: Pool | null = null;

const getDbConfig = () => {
  const dbUrl = process.env.DATABASE_URL || '';
  if (!dbUrl) {
    throw new Error('DATABASE_URL environment variable is not set');
  }
  try {
    const url = new URL(dbUrl);
    return {
      user: url.username,
      password: decodeURIComponent(url.password),
      host: url.hostname,
      port: parseInt(url.port || '5432', 10),
      database: url.pathname.slice(1),
    };
  } catch (e) {
    return { connectionString: dbUrl };
  }
};

export function getPool(): Pool {
  if (!_pool) {
    _pool = new Pool({
      ...getDbConfig(),
      ssl: process.env.NODE_ENV === 'production' ? { rejectUnauthorized: process.env.DB_SSL_REJECT_UNAUTHORIZED === 'true' } : false,
      max: 30,
      idleTimeoutMillis: 30000,
      connectionTimeoutMillis: 10000,
    });
  }
  return _pool;
}

export async function query<T extends QueryResultRow = QueryResultRow>(text: string, params?: any[]): Promise<QueryResult<T>> {
  return getPool().query<T>(text, params);
}

export async function getOne<T extends QueryResultRow = QueryResultRow>(text: string, params?: any[]): Promise<T | null> {
  const result = await getPool().query<T>(text, params);
  return result.rows[0] ?? null;
}

export async function getMany<T extends QueryResultRow = QueryResultRow>(text: string, params?: any[]): Promise<T[]> {
  const result = await getPool().query<T>(text, params);
  return result.rows;
}

export async function insert<T extends QueryResultRow = QueryResultRow>(table: string, data: Record<string, any>): Promise<T | null> {
  const keys = Object.keys(data);
  const values = Object.values(data);
  const placeholders = keys.map((_, i) => `$${i + 1}`);
  const sql = `INSERT INTO ${table} (${keys.join(', ')}) VALUES (${placeholders.join(', ')}) RETURNING *`;
  const result = await getPool().query<T>(sql, values);
  return result.rows[0] ?? null;
}

export async function update<T extends QueryResultRow = QueryResultRow>(
  table: string,
  data: Record<string, any>,
  where: string,
  whereParams: any[] = []
): Promise<T | null> {
  const keys = Object.keys(data);
  const values = Object.values(data);
  const setClauses = keys.map((k, i) => `${k} = $${i + 1}`);
  const offset = keys.length;
  const adjustedWhere = where.replace(/\$(\d+)/g, (_, n) => `$${parseInt(n, 10) + offset}`);
  const sql = `UPDATE ${table} SET ${setClauses.join(', ')} WHERE ${adjustedWhere} RETURNING *`;
  const result = await getPool().query<T>(sql, [...values, ...whereParams]);
  return result.rows[0] ?? null;
}

export async function remove(table: string, where: string, params: any[] = []): Promise<number> {
  const sql = `DELETE FROM ${table} WHERE ${where}`;
  const result = await getPool().query(sql, params);
  return result.rowCount ?? 0;
}

export async function withTransaction<T>(fn: (client: import('pg').PoolClient) => Promise<T>): Promise<T> {
  const client = await getPool().connect();
  try {
    await client.query('BEGIN');
    const result = await fn(client);
    await client.query('COMMIT');
    return result;
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  } finally {
    client.release();
  }
}

export const pool = {
  query: (text: string, params?: any[]) => getPool().query(text, params),
  connect: () => getPool().connect(),
  end: () => _pool?.end(),
};