import type { Pool, PoolConnection, ResultSetHeader, RowDataPacket } from "mysql2/promise";
import mysql from "mysql2/promise";

let pool: Pool | null = null;

export function getMysqlConfig() {
  return {
    host: process.env.MYSQL_HOST || "127.0.0.1",
    port: Number(process.env.MYSQL_PORT || 3306),
    user: process.env.MYSQL_USER || "root",
    password: process.env.MYSQL_PASSWORD ?? "",
    database: process.env.MYSQL_DATABASE || "safar_libya",
  };
}

export async function connectMysql() {
  if (pool) return pool;
  const cfg = getMysqlConfig();
  pool = mysql.createPool({
    ...cfg,
    waitForConnections: true,
    connectionLimit: 10,
    timezone: "Z",
    charset: "utf8mb4",
    multipleStatements: false,
  });
  const conn = await pool.getConnection();
  await conn.ping();
  conn.release();
  return pool;
}

export async function disconnectMysql() {
  if (pool) {
    await pool.end();
    pool = null;
  }
}

export function mysqlPool(): Pool {
  if (!pool) throw new Error("MySQL pool not connected. Call connectMysql() first.");
  return pool;
}

export async function sqlQuery<T extends RowDataPacket[]>(
  sql: string,
  params: unknown[] = [],
): Promise<T> {
  const [rows] = await mysqlPool().query<T>(sql, params);
  return rows;
}

export async function sqlExecute(
  sql: string,
  params: unknown[] = [],
): Promise<ResultSetHeader> {
  const [res] = await mysqlPool().execute<ResultSetHeader>(sql, params as never[]);
  return res;
}

export async function withMysqlTxn<T>(
  fn: (conn: PoolConnection) => Promise<T>,
): Promise<T> {
  const conn = await mysqlPool().getConnection();
  try {
    await conn.beginTransaction();
    const out = await fn(conn);
    await conn.commit();
    return out;
  } catch (e) {
    await conn.rollback();
    throw e;
  } finally {
    conn.release();
  }
}

/** Serialize multi-step operations that concern the same logical resource. */
export async function withMysqlNamedLock<T>(
  name: string,
  fn: () => Promise<T>,
  timeoutSeconds = 10,
): Promise<T> {
  const conn = await mysqlPool().getConnection();
  try {
    const [rows] = await conn.query<RowDataPacket[]>(`SELECT GET_LOCK(?, ?) AS acquired`, [
      name,
      timeoutSeconds,
    ]);
    if (Number(rows[0]?.acquired) !== 1) {
      throw new Error(`Could not acquire MySQL lock: ${name}`);
    }
    return await fn();
  } finally {
    try {
      await conn.query(`SELECT RELEASE_LOCK(?)`, [name]);
    } finally {
      conn.release();
    }
  }
}