import mysql from 'mysql2/promise';

// A single pooled connection reused across all API routes. mysql2's pool
// handles reconnects/idle connections itself — no need to manually manage
// connect/disconnect per request.
let pool: mysql.Pool | null = null;

export function getPool(): mysql.Pool {
  if (!pool) {
    pool = mysql.createPool({
      host: process.env.DB_HOST,
      port: Number(process.env.DB_PORT ?? 3306),
      user: process.env.DB_USER,
      password: process.env.DB_PASSWORD,
      database: process.env.DB_NAME ?? 'unloze_playtimestats',
      waitForConnections: true,
      connectionLimit: 10,
      queueLimit: 0,
      dateStrings: true, // return DATETIME columns as 'YYYY-MM-DD HH:MM:SS' strings, not JS Date objects with TZ surprises
    });
  }
  return pool;
}

export async function query<T = any>(sql: string, params: any[] = []): Promise<T[]> {
  const [rows] = await getPool().query(sql, params);
  return rows as T[];
}