/**
 * Database Test Utilities
 *
 * Provides functions for creating and managing isolated test databases.
 * Each test gets its own database to prevent state leakage.
 */

import { Database } from 'bun:sqlite';
import { mkdtemp, rm } from 'node:fs/promises';
import { tmpdir } from 'node:os';
import { join } from 'node:path';
import { drizzle } from 'drizzle-orm/bun-sqlite';
import { migrate } from 'drizzle-orm/bun-sqlite/migrator';
import { type DbClient, createDbClient, findMigrationsFolder } from '../db/client';
import * as schema from '../db/schema';

/**
 * Create isolated test database (in-memory)
 *
 * Use for fast tests that don't need CLI interaction.
 * Database is destroyed when closed.
 *
 * @returns In-memory SQLite database with migrations applied
 *
 * @example
 * ```typescript
 * const db = await setupTestDatabase();
 * // Use db...
 * await cleanupTestDatabase(db);
 * ```
 */
export async function setupTestDatabase(): Promise<DbClient> {
  const sqlite = new Database(':memory:');
  // Matches `createDbClient` (db/client.ts). SQLite defaults this OFF per
  // connection, so without it every `onDelete: 'cascade'` in the schema is
  // enforced in production and inert in the suite (celilo#1074).
  sqlite.run('PRAGMA foreign_keys = ON');
  const db = drizzle(sqlite, { schema });
  const migrationsFolder = findMigrationsFolder();
  await migrate(db, { migrationsFolder });
  return db;
}

/**
 * Create isolated test database (temporary file)
 *
 * Use for tests that need CLI to access database.
 * Database is in a temporary directory that's cleaned up.
 *
 * @returns Object with database, path, and cleanup function
 *
 * @example
 * ```typescript
 * const { db, path: dbPath, cleanup } = await setupTestDatabaseFile();
 * // Use db and dbPath...
 * await cleanup();
 * ```
 */
export async function setupTestDatabaseFile(): Promise<{
  db: DbClient;
  path: string;
  cleanup: () => Promise<void>;
}> {
  const tempDir = await mkdtemp(join(tmpdir(), 'celilo-test-'));
  const dbPath = join(tempDir, 'test.db');
  const sqlite = new Database(dbPath);
  // See setupTestDatabase — celilo#1074.
  sqlite.run('PRAGMA foreign_keys = ON');
  const db = drizzle(sqlite, { schema });
  const migrationsFolder = findMigrationsFolder();
  await migrate(db, { migrationsFolder });

  const cleanup = async () => {
    sqlite.close();
    await rm(tempDir, { recursive: true, force: true });
  };

  return { db, path: dbPath, cleanup };
}

/**
 * Create a test database at a path the CALLER chooses.
 *
 * The other two helpers pick the location — memory, or a temp directory they
 * own. This one exists for tests that must hand the same path to something
 * else, typically a spawned CLI through `CELILO_DB_PATH`.
 *
 * It was a second module, `test-utils/setup-test-db.ts`, exporting a function
 * ALSO called `setupTestDatabase` and another also called
 * `cleanupTestDatabase`, with different arities and — until celilo#1074 —
 * opposite foreign-key semantics. Which contract a test was under depended
 * entirely on which file its import line named, and nothing at the call site
 * showed it. One caller had already aliased the import to `migrateDbFile` to
 * make it readable. The two colliding names are gone rather than renamed: there
 * is no longer a wrong one to pick.
 *
 * @param testDbPath - Where to create the database file
 */
export async function setupTestDatabaseAt(testDbPath: string): Promise<DbClient> {
  const db = createDbClient({ path: testDbPath });
  await migrate(db, { migrationsFolder: findMigrationsFolder() });
  return db;
}

/**
 * Clean up test database
 *
 * Closes the database connection.
 * For in-memory databases, this destroys all data.
 *
 * @param db - Database to close
 *
 * @example
 * ```typescript
 * const db = await setupTestDatabase();
 * // ... tests ...
 * await cleanupTestDatabase(db);
 * ```
 */
export async function cleanupTestDatabase(db: DbClient): Promise<void> {
  db.$client.close();
}

/**
 * Create multiple isolated databases
 *
 * Useful for tests that need multiple independent databases.
 *
 * @param count - Number of databases to create
 * @returns Array of in-memory databases
 *
 * @example
 * ```typescript
 * const [db1, db2] = await setupMultipleTestDatabases(2);
 * // Use db1 and db2 independently...
 * await cleanupMultipleTestDatabases([db1, db2]);
 * ```
 */
export async function setupMultipleTestDatabases(count: number): Promise<DbClient[]> {
  const databases: DbClient[] = [];
  for (let i = 0; i < count; i++) {
    databases.push(await setupTestDatabase());
  }
  return databases;
}

/**
 * Clean up multiple test databases
 *
 * @param databases - Array of databases to close
 */
export async function cleanupMultipleTestDatabases(databases: DbClient[]): Promise<void> {
  for (const db of databases) {
    await cleanupTestDatabase(db);
  }
}