import mysql from 'mysql2/promise';

// CRITICAL: wild_dubai lives on port 3306 (XAMPP MariaDB)
// Must use 127.0.0.1 (TCP) — not 'localhost' (which uses socket and hits wrong MySQL)
const pool = mysql.createPool({
  host: process.env.DB_HOST || '127.0.0.1',
  port: parseInt(process.env.DB_PORT || '3306'),
  user: process.env.DB_USER || 'root',
  password: process.env.DB_PASSWORD || '',
  database: process.env.DB_NAME || 'wild_dubai',
  waitForConnections: true,
  connectionLimit: 3,      // Keep low — XAMPP max_connections = 151, dev has many worker processes
  queueLimit: 10,
  connectTimeout: 30000,
  enableKeepAlive: true,
  keepAliveInitialDelay: 0,
});

export async function query<T = any>(
  sql: string,
  params?: any[]
): Promise<T[]> {
  try {
    const [rows] = await pool.execute(sql, params);
    return rows as T[];
  } catch (error: any) {
    console.error('[DB Error]', error.message, '\nSQL:', sql.substring(0, 120));
    throw error;
  }
}

export { pool };