import { Database } from 'bun:sqlite';
import { existsSync, mkdirSync } from 'node:fs';
import { dirname, join } from 'node:path';
import { fileURLToPath } from 'node:url';
import { BUSY_TIMEOUT_MS, ensureWalMode } from '@celilo/event-bus/wal';
import { drizzle } from 'drizzle-orm/bun-sqlite';
import { getDbPath } from '../config/paths';
import { runMigrationsOn } from './migrate';
import * as schema from './schema';

/**
 * Database configuration
 */
interface DatabaseConfig {
  path: string;
  readonly?: boolean;
}

/**
 * Find migrations folder relative to current working directory
 */
export function findMigrationsFolder(): string {
  // Get directory of current file
  const currentDir = dirname(fileURLToPath(import.meta.url));

  // Try common locations
  const candidates = [
    './drizzle', // Running from backend directory
    './backend/drizzle', // Running from celilo directory
    join(process.cwd(), 'drizzle'), // Absolute from backend
    join(process.cwd(), 'backend', 'drizzle'), // Absolute from celilo
    join(currentDir, '../../drizzle'), // Relative to this file
  ];

  for (const candidate of candidates) {
    const metaPath = join(candidate, 'meta', '_journal.json');
    if (existsSync(metaPath)) {
      return candidate;
    }
  }

  throw new Error(`Could not find drizzle migrations folder. Tried: ${candidates.join(', ')}`);
}

/**
 * Create database client and run migrations if needed
 */
export function createDbClient(config?: Partial<DatabaseConfig>) {
  const dbPath = config?.path ?? getDbPath();
  const readonly = config?.readonly ?? false;

  // Tripwire against opening the operator's real database from a test
  // (celilo#1343). The scratch-DB preload in apps/celilo/test-preload.ts only
  // arms when bun runs from apps/celilo/ — bun reads the bunfig at the working
  // directory — so the same suite run from the repo root reaches this function
  // with every override unset and would open, and migrate, the operator's
  // ~/Library/.../celilo.db. The condition below is exactly that resolution:
  // under bun test, with every redirect unset, getDbPath() falls through to
  // the platform data directory. A run that is properly scoped (preload armed,
  // CELILO_DATA_DIR redirected, or an explicit config.path) never satisfies
  // it and pays one string comparison.
  if (
    !config?.path &&
    process.env.NODE_ENV === 'test' &&
    !process.env.CELILO_DB_PATH &&
    !process.env.CELILO_DATA_DIR &&
    process.env.ENVIRONMENT !== 'dev'
  ) {
    throw new Error(
      'Refusing to open the celilo database under bun test with no database path set ' +
        '(celilo#1343): the scratch-DB preload in apps/celilo/test-preload.ts only arms ' +
        "when bun runs from apps/celilo/, so this default path is the operator's real " +
        'celilo.db. Run tests from apps/celilo/, or set CELILO_DB_PATH (or ' +
        'CELILO_DATA_DIR) to a test-scoped path.',
    );
  }

  // bun:sqlite's `create: true` makes the file but not the parent directory.
  // First-run after a fresh install hits this — without recursive mkdir we
  // get SQLITE_CANTOPEN before init can write its config.
  if (!readonly) {
    mkdirSync(dirname(dbPath), { recursive: true });
  }

  const sqlite = new Database(dbPath, {
    readonly,
    create: true,
  });

  // Set FIRST so it covers every statement below, including the migrations. A
  // command that opens the db while a deploy still holds it used to die on
  // SQLITE_BUSY immediately instead of waiting (#798).
  sqlite.run(`PRAGMA busy_timeout = ${BUSY_TIMEOUT_MS}`);

  // Enable foreign keys
  sqlite.run('PRAGMA foreign_keys = ON');

  // Enable WAL mode for better concurrency. busy_timeout does NOT cover this
  // transition, so the switch needs its own retry — see ensureWalMode.
  if (!readonly) {
    ensureWalMode(sqlite);
  }

  const db = drizzle(sqlite, { schema });

  // Apply migrations on open (ISS-0100). Drizzle's migrator is the single
  // migration mechanism for ALL DBs — fresh and existing — and the only place
  // schema changes live (the old imperative hand-list is gone). migrate() is
  // idempotent: it applies every migration newer than the latest recorded in
  // `__drizzle_migrations` and no-ops once current.
  //
  // A DB from the hand-list era has a frozen `__drizzle_migrations` watermark,
  // so drizzle re-runs already-applied migrations and throws (celilo#169).
  // runMigrationsOn repairs that itself where the schema is already complete.
  // It has to happen HERE and not only in `celilo system migrate`, because
  // that command reaches its own repair through getDb() — this line — and so
  // would die before getting there. A PARTIALLY applied schema still throws
  // and still needs a human. `celilo system doctor` (checkSchemaDrift) detects
  // the drift.
  if (!readonly) {
    try {
      runMigrationsOn(db);
    } catch (error) {
      console.error('Failed to run migrations:', error);
      // Release the file before rethrowing: the caller never receives this
      // connection, so leaving it open holds the WAL lock on a db it cannot
      // use (celilo#1269).
      sqlite.close();
      throw error;
    }
  }

  // One-time upgrade backfill for the target_ip → module_systems refactor
  // (openspec/specs/module-systems-addressing/spec.md). migrate() above has ensured the
  // module_systems table exists (migration 0007). A deployment created before
  // the refactor has its host data only in module_configs /
  // ip_allocations / module_infrastructure and an EMPTY module_systems, so its
  // modules resolve to no system and the migrated hooks throw "No deployed
  // system found". This lifts that state across. Idempotent (skips modules
  // already recorded), so a no-op on a fresh DB and on every steady-state open.
  // Logged so the upgrade is operator-visible, not silent magic.
  if (!readonly) {
    try {
      const { backfillModuleSystems } = require('../services/deployed-systems');
      const filled = backfillModuleSystems(db) as string[];
      if (filled.length > 0) {
        console.log(
          `Backfilled module_systems for ${filled.length} module(s): ${filled.join(', ')}`,
        );
      }
    } catch (error) {
      console.error('Failed to backfill module_systems:', error);
      sqlite.close();
      throw error;
    }
  }

  return db;
}

/**
 * Celilo database client type
 * Convenience type to avoid repeating ReturnType<typeof createDbClient>
 */
export type DbClient = ReturnType<typeof createDbClient>;

/**
 * Global database instance (singleton)
 */
let dbInstance: DbClient | null = null;
let dbPath: string | null = null;

/**
 * Get or create database instance
 *
 * Checks environment variable for path changes to support testing
 */
export function getDb() {
  const currentPath = getDbPath();

  // If path changed, close existing instance and create new one
  if (dbInstance && dbPath !== currentPath) {
    dbInstance.$client.close();
    dbInstance = null;
    dbPath = null;
  }

  if (!dbInstance) {
    dbInstance = createDbClient();
    dbPath = currentPath;
  }

  return dbInstance;
}

/**
 * Close database connection and reset singleton
 */
export function closeDb() {
  if (dbInstance) {
    dbInstance.$client.close();
    dbInstance = null;
    dbPath = null;
  }
}