/**
 * ============================================================================
 * CENTRAL DATABASE CONNECTION — the single source of truth for DB access.
 * ============================================================================
 * Every part of the app (website + any shared code) connects to MySQL through
 * THIS file only. No other file should read DB credentials directly.
 *
 * SECURITY MODEL (as required):
 *  - Credentials are NEVER hard-coded here. They are read from environment
 *    variables loaded from a protected `.env` file (see .env.example).
 *  - The `.env` file must live OUTSIDE the public web root and be blocked from
 *    web access (see deploy/security notes in DEPLOY.md). Only host-level admins
 *    (you) can read it.
 *  - This keeps the "one central file with the DB user/password" model your IT
 *    team wants, while ensuring a web attacker can't read the credentials.
 *
 * The SAME database is shared with the CRM. The website reads published content;
 * the CRM writes content. Both point their own copy of this connection at the
 * one MySQL database.
 * ============================================================================
 */
import { drizzle } from "drizzle-orm/mysql2";
import mysql from "mysql2/promise";
import * as schema from "../../drizzle/schema";

export interface DbCredentials {
  host: string;
  port: number;
  user: string;
  password: string;
  database: string;
  ssl: boolean;
}

/** Reads and validates DB credentials from the environment (.env). */
export function loadDbCredentials(): DbCredentials | null {
  const {
    DB_HOST,
    DB_PORT,
    DB_USER,
    DB_PASSWORD,
    DB_NAME,
    DB_SSL,
    DATABASE_URL,
  } = process.env;

  // Preferred: discrete vars (clearest for phpMyAdmin/host setups).
  if (DB_HOST && DB_USER && DB_NAME) {
    return {
      host: DB_HOST,
      port: DB_PORT ? Number(DB_PORT) : 3306,
      user: DB_USER,
      password: DB_PASSWORD ?? "",
      database: DB_NAME,
      ssl: DB_SSL === "true",
    };
  }

  // Fallback: a single DATABASE_URL (mysql://user:pass@host:port/db).
  if (DATABASE_URL) {
    try {
      const u = new URL(DATABASE_URL);
      return {
        host: u.hostname,
        port: u.port ? Number(u.port) : 3306,
        user: decodeURIComponent(u.username),
        password: decodeURIComponent(u.password),
        database: u.pathname.replace(/^\//, ""),
        ssl: u.searchParams.get("ssl") === "true",
      };
    } catch {
      return null;
    }
  }

  return null;
}

let _pool: mysql.Pool | null = null;
let _db: ReturnType<typeof createDb> | null = null;

function createDb(pool: mysql.Pool) {
  return drizzle(pool, { schema, mode: "default" });
}

/** Returns a shared Drizzle instance backed by a MySQL connection pool. */
export function getDb() {
  if (_db) return _db;

  const creds = loadDbCredentials();
  if (!creds) {
    console.warn(
      "[Database] No DB credentials found. Set DB_HOST/DB_USER/DB_NAME (and DB_PASSWORD) in .env"
    );
    return null;
  }

  try {
    _pool = mysql.createPool({
      host: creds.host,
      port: creds.port,
      user: creds.user,
      password: creds.password,
      database: creds.database,
      ssl: creds.ssl ? {} : undefined,
      waitForConnections: true,
      connectionLimit: 10,
      queueLimit: 0,
    });
    _db = createDb(_pool);
    return _db;
  } catch (error) {
    console.error("[Database] Failed to create MySQL pool:", error);
    return null;
  }
}

export { schema };
