import mysql, { type Pool, type PoolConnection, type ResultSetHeader, type RowDataPacket } from "mysql2/promise";
import { getDbConfig } from "./config";
import { toMysqlDateTime } from "@/lib/db/datetime";

export type SqlParam = string | number | boolean | Date | null | Buffer;

let pool: Pool | null = null;

export function getPool(): Pool {
  if (!pool) {
    pool = mysql.createPool(getDbConfig());
  }
  return pool;
}

/**
 * Safety net: never send ISO-8601 (`2026-07-14T11:24:07.403Z`) into MariaDB DATETIME.
 * Converts those strings; leaves other params untouched.
 */
export function sanitizeSqlParams(params: SqlParam[] = []): SqlParam[] {
  return params.map((param) => {
    if (typeof param !== "string") return param;
    const value = param.trim();
    // Only normalize ISO-8601 date-times — do not touch other strings that end in Z.
    if (!/^\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}/.test(value)) return param;
    return (toMysqlDateTime(value) as SqlParam) ?? null;
  });
}

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

export async function execute(
  sql: string,
  params: SqlParam[] = [],
): Promise<ResultSetHeader> {
  const [result] = await getPool().execute<ResultSetHeader>(sql, sanitizeSqlParams(params));
  return result;
}

export async function withTransaction<T>(
  fn: (conn: PoolConnection) => Promise<T>,
): Promise<T> {
  const conn = await getPool().getConnection();
  const originalExecute = conn.execute.bind(conn);
  const originalQuery = conn.query.bind(conn);

  // Patch connection so ISO datetimes are sanitized inside transactions too.
  conn.execute = ((sql: unknown, values?: unknown) => {
    if (Array.isArray(values)) {
      return originalExecute(sql as never, sanitizeSqlParams(values as SqlParam[]) as never);
    }
    return originalExecute(sql as never, values as never);
  }) as typeof conn.execute;

  conn.query = ((sql: unknown, values?: unknown) => {
    if (Array.isArray(values)) {
      return originalQuery(sql as never, sanitizeSqlParams(values as SqlParam[]) as never);
    }
    return originalQuery(sql as never, values as never);
  }) as typeof conn.query;

  try {
    await conn.beginTransaction();
    const result = await fn(conn);
    await conn.commit();
    return result;
  } catch (err) {
    await conn.rollback();
    throw err;
  } finally {
    conn.release();
  }
}

export function jsonString(value: unknown): string | null {
  if (value === undefined || value === null) return null;
  return JSON.stringify(value);
}

export function parseJson<T>(raw: unknown, fallback: T): T {
  if (raw == null || raw === "") return fallback;
  if (typeof raw === "object") return raw as T;
  try {
    return JSON.parse(String(raw)) as T;
  } catch {
    return fallback;
  }
}