/**
 * MySQL driver factory — built dynamically by `src/core/db/client.ts` when
 * `DB_DRIVER=mysql`. Kept in a separate file so the mysql2 dependency is
 * only loaded when actually needed.
 *
 * Setup expectations:
 *   - DATABASE_URL=mysql://user:pass@host:3306/dbname
 *   - DB_TABLE_PREFIX=mf_      (optional, useful in shared-DB setups)
 *
 * Schema lives at `./schema/mysql/index.ts` (ported from the SQLite
 * source-of-truth `src/lib/db/schema.ts`).
 */

import mysql from 'mysql2/promise';
import { drizzle, type MySql2Database } from 'drizzle-orm/mysql2';
import * as schema from './schema/mysql';

export type MysqlDb = MySql2Database<typeof schema> & { $client: mysql.Pool };

let cachedClient: MysqlDb | null = null;

export function buildMysqlClient(): MysqlDb {
  if (cachedClient) return cachedClient;

  const url = process.env.DATABASE_URL?.trim();
  if (!url) {
    throw new Error(
      'DATABASE_URL is not set. Required for DB_DRIVER=mysql. Format: mysql://user:pass@host:port/dbname',
    );
  }
  if (!url.startsWith('mysql://')) {
    throw new Error(
      `DATABASE_URL must start with "mysql://" — got ${url.slice(0, 16)}... Check .env.local.`,
    );
  }

  const pool = mysql.createPool({
    uri: url,
    connectionLimit: 10,
    namedPlaceholders: true,
  });

  const db = drizzle(pool, { schema, mode: 'default' }) as MysqlDb;
  // Expose the underlying pool the same way better-sqlite3 driver exposes
  // its database handle as `$client` — useful for raw queries / graceful
  // shutdown / health checks.
  Object.defineProperty(db, '$client', { value: pool, enumerable: false });
  cachedClient = db;
  return db;
}
