/**
 * Database client — driver-aware façade over Drizzle ORM.
 *
 * Reads `DB_DRIVER` (env, default `sqlite`) to choose between:
 *   - `sqlite` — better-sqlite3, single file, default for dev/CI
 *   - `mysql`  — mysql2, server-based, default for Laravel-CRM integration
 *
 * For SQLite, uses `SQLITE_DB_PATH` (default `./data/mailforge.db`) and sets
 * production-friendly pragmas (WAL, foreign_keys, busy_timeout).
 *
 * For MySQL, uses `DATABASE_URL` (mysql://user:pass@host:port/db). The Drizzle
 * schema is fully ported (`./schema/mysql/index.ts`) and the connection
 * factory is implemented (`./client.mysql.ts`), so `drizzle-kit generate:mysql`,
 * `db:export-ddl`, and `buildMysqlClient()` all work today.
 *
 * The repository layer (`src/lib/db/repositories.ts`, `campaign-repo.ts`, …),
 * however, was lifted directly from the SQLite codebase and uses
 * SQLite-specific raw SQL in a handful of places (`unixepoch()`, JSON-extract
 * syntax). Until that layer is refactored to be driver-agnostic — tracked as
 * the "Repo-Layer dual-driver" follow-up — `getDb()` returns the SQLite
 * client. Use `buildMysqlClient()` directly if you only need the MySQL
 * Drizzle handle (DDL generation, schema diffs, ad-hoc tooling).
 */

import Database from 'better-sqlite3';
import { drizzle, type BetterSQLite3Database } from 'drizzle-orm/better-sqlite3';
import { existsSync, mkdirSync } from 'node:fs';
import { dirname } from 'node:path';
import * as schema from '@/lib/db/schema';

export type DbDriver = 'sqlite' | 'mysql';
export type Db = BetterSQLite3Database<typeof schema> & { $client: Database.Database };

export function getDriver(): DbDriver {
  const v = (process.env.DB_DRIVER ?? 'sqlite').toLowerCase();
  if (v !== 'sqlite' && v !== 'mysql') {
    throw new Error(
      `Invalid DB_DRIVER="${v}". Must be "sqlite" or "mysql". Check .env.local.`,
    );
  }
  return v;
}

export function getTablePrefix(): string {
  return process.env.DB_TABLE_PREFIX ?? '';
}

function buildSqliteClient(): Db {
  const dbPath =
    process.env.SQLITE_DB_PATH ?? process.env.MAILFORGE_DB_PATH ?? './data/mailforge.db';

  const dir = dirname(dbPath);
  if (!existsSync(dir)) mkdirSync(dir, { recursive: true });

  const sqlite = new Database(dbPath);
  sqlite.pragma('journal_mode = WAL');
  sqlite.pragma('synchronous = NORMAL');
  sqlite.pragma('busy_timeout = 5000');
  sqlite.pragma('foreign_keys = ON');

  return drizzle(sqlite, { schema });
}

/**
 * Build the database client appropriate for the configured driver.
 *
 * MySQL note: schema and connection factory are both ready (see file header),
 * but the SQLite-only repo-layer prevents a runtime switch via `getDb()`. We
 * still validate the env at boot — failing fast is better than a broken-repo
 * surprise at first query.
 */
function buildClient(): Db {
  const driver = getDriver();

  if (driver === 'mysql') {
    throw new Error(
      'DB_DRIVER=mysql is not yet supported by `getDb()`. The MySQL schema and ' +
        'driver factory are ready (use `buildMysqlClient()` directly for DDL/tooling), ' +
        'but the repository layer still uses SQLite-specific SQL. Switch to ' +
        'DB_DRIVER=sqlite for now — repo-layer dual-driver support is the next phase.',
    );
  }

  return buildSqliteClient();
}

let cachedClient: Db | null = null;

export function getDb(): Db {
  if (!cachedClient) cachedClient = buildClient();
  return cachedClient;
}

/**
 * Diagnostic info — used by the Settings UI and CLI to show which driver
 * is active. Doesn't open a connection.
 */
export function getDriverInfo(): {
  driver: DbDriver;
  prefix: string;
  details: string;
} {
  const driver = getDriver();
  if (driver === 'sqlite') {
    const path =
      process.env.SQLITE_DB_PATH ?? process.env.MAILFORGE_DB_PATH ?? './data/mailforge.db';
    return { driver, prefix: getTablePrefix(), details: `file: ${path}` };
  }
  // MySQL — redact password from URL for display
  const raw = process.env.DATABASE_URL ?? '';
  const redacted = raw.replace(/(mysql:\/\/[^:]+:)([^@]+)(@.+)/, '$1***$3');
  return { driver, prefix: getTablePrefix(), details: redacted || '(DATABASE_URL not set)' };
}
