// --- FIX REGISTRY ---
//
// 1) Refactored `getPool` to ensure a true singleton pattern, creating the connection pool only once.
//
// --- END FIX REGISTRY ---
// src/services/db/connection.db.ts
import { Pool, PoolConfig, PoolClient, types } from 'pg';
import { logger } from '../logger.server';

// --- Singleton Pool Instance ---
// This variable will hold the single, shared connection pool for the entire application.
// It is initialized once and then reused, preventing multiple connection pools.
let pool: Pool;

/**
 * Returns a singleton instance of the PostgreSQL connection pool.
 * This function ensures that only one connection pool is created for the application's lifetime.
 * @returns The singleton Pool instance.
 */
export const getPool = (): Pool => {
  if (pool) {
    return pool;
  }

  logger.info('[DB Connection] Creating new PostgreSQL connection pool...');
  const poolConfig: PoolConfig = {
      user: process.env.DB_USER,
      host: process.env.DB_HOST,
      database: process.env.DB_NAME,
      password: process.env.DB_PASSWORD,
      port: parseInt(process.env.DB_PORT || '5432', 10),
      // Recommended pool settings for a typical web application
      max: 20, // Max number of clients in the pool
      idleTimeoutMillis: 30000, // How long a client is allowed to remain idle before being closed
      connectionTimeoutMillis: 2000, // How long to wait for a client to connect
  };

  pool = new Pool(poolConfig);

  // Add a listener for connection errors on the pool.
  pool.on('error', (err, client) => {
    logger.error({ err, client }, 'Unexpected error on idle client in pool');
    // You might want to add logic here to handle the error, e.g., by trying to reconnect.
  });

  // Configure pg to parse numeric types from the database as numbers, not strings.
  // NUMERIC (decimal) and INT8 (bigint) are returned as strings by default.
  types.setTypeParser(types.builtins.NUMERIC, (value: string) => parseFloat(value));
  types.setTypeParser(types.builtins.INT8, (value: string) => parseInt(value, 10));

  return pool;
};

/**
 * Executes a series of database operations within a single atomic transaction.
 * This function implements the Unit of Work pattern as defined in ADR-002.
 * It automatically handles acquiring a client, beginning the transaction,
 * committing on success, rolling back on error, and releasing the client.
 *
 * @param callback A function that receives the transactional `PoolClient` and returns a Promise.
 * @returns A promise that resolves with the return value of the callback.
 */
export async function withTransaction<T>(callback: (client: PoolClient) => Promise<T>): Promise<T> {
  const client = await getPool().connect();
  try {
    await client.query('BEGIN');
    const result = await callback(client);
    await client.query('COMMIT');
    return result;
  } catch (error) {
    await client.query('ROLLBACK');
    logger.error({
      // Safely access error message
      err: error,
    }, 'Transaction failed, rolling back.');
    throw error; // Re-throw the original error to be handled by the caller
  } finally {
    // Always release the client back to the pool
    client.release();
  }
}


/**
 * Checks for the existence of a list of tables in the public schema.
 * @param tableNames An array of table names to check.
 * @returns A promise that resolves to an array of table names that are missing from the database.
 */
// prettier-ignore
export async function checkTablesExist(tableNames: string[]): Promise<string[]> {
  try {
    // This query checks the information_schema to find which of the provided table names exist.
    const query = `
      SELECT table_name
      FROM information_schema.tables
      WHERE table_schema = 'public'
      AND table_name = ANY($1::text[])
    `;
    const res = await getPool().query<{ table_name: string }>(query, [tableNames]);
    
    const existingTables = new Set(res.rows.map(row => row.table_name));
    const missingTables = tableNames.filter(name => !existingTables.has(name));
    
    return missingTables;
  } catch (error) {
    logger.error({ err: error }, 'Database error in checkTablesExist');
    throw new Error('Failed to check for tables in database.');
  }
}

/**
 * Gets the current status of the connection pool.
 * @returns An object with the total, idle, and waiting client counts.
 */
// prettier-ignore
export function getPoolStatus() {
  const pool = getPool();
  // pool.totalCount: The total number of clients in the pool.
  // pool.idleCount: The number of clients that are idle and waiting for a query.
  // pool.waitingCount: The number of queued requests waiting for a client to become available.
  return {
    totalCount: pool.totalCount,
    idleCount: pool.idleCount,
    waitingCount: pool.waitingCount,
  };
}