import mysql from "mysql2/promise";

let pool;

export function getDbPool() {
  if (pool) return pool;

  const required = ["DB_HOST", "DB_NAME", "DB_USER", "DB_PASSWORD"];
  const missing = required.filter((key) => !process.env[key]);
  if (missing.length) {
    throw new Error(`Missing database environment variables: ${missing.join(", ")}`);
  }

  pool = mysql.createPool({
    host: process.env.DB_HOST,
    port: Number(process.env.DB_PORT || 3306),
    database: process.env.DB_NAME,
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
    waitForConnections: true,
    connectionLimit: 10,
    queueLimit: 0,
    namedPlaceholders: true,
    charset: "utf8mb4"
  });

  return pool;
}

export async function query(sql, params = {}) {
  const [rows] = await getDbPool().execute(sql, params);
  return rows;
}

export async function withTransaction(callback) {
  const conn = await getDbPool().getConnection();
  try {
    await conn.beginTransaction();
    const result = await callback(conn);
    await conn.commit();
    return result;
  } catch (error) {
    await conn.rollback();
    throw error;
  } finally {
    conn.release();
  }
}

export async function healthcheck() {
  const rows = await query("SELECT 1 AS ok");
  return rows?.[0]?.ok === 1;
}
