import "server-only";
import mysql, { type Pool } from "mysql2/promise";
import type { RowDataPacket } from "mysql2";

let pool: Pool | undefined;

export function getDb(): Pool {
  if (pool) return pool;

  const host = process.env.DB_HOST?.trim();
  const database = process.env.DB_NAME?.trim();
  const user = process.env.DB_USER?.trim();

  // An empty password is allowed for local development.
  // Production should use a strong password.
  const password = process.env.DB_PASSWORD ?? "";

  if (!host || !database || !user) {
    throw new Error("Database configuration is incomplete.");
  }

  const port = Number(process.env.DB_PORT || 3306);

  if (!Number.isInteger(port) || port < 1 || port > 65535) {
    throw new Error("Invalid database port configuration.");
  }

  const sslEnabled = process.env.DB_SSL === "true";

  pool = mysql.createPool({
    host,
    port,
    database,
    user,
    password,

    waitForConnections: true,
    connectionLimit: 3,
    maxIdle: 2,
    idleTimeout: 30_000,
    queueLimit: 0,
    connectTimeout: 5_000,
    enableKeepAlive: true,

    ssl: sslEnabled
      ? { minVersion: "TLSv1.2" }
      : undefined,
  });

  return pool;
}


interface ConnectionTestRow extends RowDataPacket {
  connection_test: number;
  server_time: Date | string;
}

export async function testDatabaseConnection(): Promise<
  | { ok: true; message: string; serverTime: string }
  | { ok: false; message: string }
> {
  try {
    const db = getDb();

    const [rows] = await db.query<ConnectionTestRow[]>(
      "SELECT 1 AS connection_test, NOW() AS server_time"
    );

    const row = rows[0];

    if (row?.connection_test !== 1) {
      return {
        ok: false,
        message: "Unexpected SQL response.",
      };
    }

    const serverTime =
      row.server_time instanceof Date
        ? row.server_time.toISOString()
        : new Date(row.server_time).toISOString();

    return {
      ok: true,
      message: "SQL connection successful.",
      serverTime,
    };
  } catch (error) {
    console.error(
      "[Database] Connection test failed:",
      error instanceof Error ? error.message : "Unknown error"
    );

    return {
      ok: false,
      message:
        "Connection unavailable. Check the server configuration and network access.",
    };
  }
}

export function isDatabaseConfigured(): boolean {
  return Boolean(
    process.env.DB_HOST?.trim() &&
    process.env.DB_NAME?.trim() &&
    process.env.DB_USER?.trim()
  );
}