import { Database } from 'bun:sqlite';
import { existsSync, mkdirSync } from 'node:fs';
import { homedir } from 'node:os';
import { dirname, join } from 'node:path';
import { drizzle } from 'drizzle-orm/bun-sqlite';
import { validateDbPath } from '../utils/validation';
import { MIGRATION_ASSETS, migrateEmbedded, migrationsBeforeArticleIdentityConstraints } from './migration-assets';
import {
  articleIdentityConstraintsInstalled,
  articleIdentityRepairAuditStorageInstalled,
  ensureArticleIdentityRepairAuditStorage,
  installArticleIdentityConstraintIndexes,
  repairAndInstallArticleIdentityConstraints,
  repairStaleMigrationHashes,
  seedDrizzleMigrationsForLegacyDb,
} from './migration-repair';
import { setupFts5 } from './migrations';
import * as schema from './schema';

// Default database path
const DEFAULT_DB_PATH = join(homedir(), '.config', 'rss-ai', 'rss.db');
const DB_INIT_LOCK_WAIT_MS = 30_000;
const DB_INIT_LOCK_RETRY_MS = 10;
const ARTICLE_LINK_CANONICALIZATION_MARKER = 'article-link-canonicalization-v1-complete';

let db: ReturnType<typeof drizzle> | null = null;
let sqlite: Database | null = null;
let activeDbPath: string | null = null;

function isSqliteBusyError(error: unknown): boolean {
  return (
    typeof error === 'object' &&
    error !== null &&
    'code' in error &&
    typeof error.code === 'string' &&
    error.code.startsWith('SQLITE_BUSY')
  );
}

function runWithSqliteBusyRetry(sqliteDb: Database, statement: string, dbPath: string): void {
  const deadline = Date.now() + DB_INIT_LOCK_WAIT_MS;

  while (true) {
    try {
      sqliteDb.run(statement);
      return;
    } catch (error) {
      if (!isSqliteBusyError(error)) {
        throw error;
      }
      if (Date.now() >= deadline) {
        throw new Error(`Timed out waiting for another process to initialize database ${dbPath}`);
      }
      Bun.sleepSync(DB_INIT_LOCK_RETRY_MS);
    }
  }
}

function closeFileInitializationConnection(sqliteDb: Database): void {
  // Startup code uses only raw SQLite statements with deterministic ownership,
  // so a strict close is both possible and an invariant: no prepared statement
  // may keep the migration descriptor alive after initialization returns.
  sqliteDb.close(true);
}

function runEmbeddedMigrationPhases(sqliteDb: Database): void {
  migrateEmbedded(sqliteDb, migrationsBeforeArticleIdentityConstraints());
  let canonicalLinksRepaired = false;
  if (!articleIdentityConstraintsInstalled(sqliteDb)) {
    repairAndInstallArticleIdentityConstraints(sqliteDb);
    canonicalLinksRepaired = true;
  } else if (!articleIdentityRepairAuditStorageInstalled(sqliteDb)) {
    ensureArticleIdentityRepairAuditStorage(sqliteDb);
  }
  migrateEmbedded(sqliteDb);
  if (!articleIdentityConstraintsInstalled(sqliteDb)) {
    installArticleIdentityConstraintIndexes(sqliteDb);
  }

  // 0002/0003 were introduced alongside canonical Article identities. Keep a
  // durable effect marker so databases created during development between
  // those migrations and the canonicalization repair receive the same one-time
  // ordered repair without rescanning Articles on every startup.
  const canonicalizationRecorded = sqliteDb
    .query('SELECT 1 FROM maintenance_cursors WHERE key = ?')
    .get(ARTICLE_LINK_CANONICALIZATION_MARKER);
  if (!canonicalizationRecorded) {
    if (!canonicalLinksRepaired) repairAndInstallArticleIdentityConstraints(sqliteDb);
    sqliteDb.run('INSERT INTO maintenance_cursors (key, last_id, updated_at) VALUES (?, 0, ?)', [
      ARTICLE_LINK_CANONICALIZATION_MARKER,
      Math.floor(Date.now() / 1000),
    ]);
  }
}

function runDatabaseInitialization(sqliteDb: Database, dbPath: string): void {
  // Set the timeout before journal_mode, which may have to wait for another
  // connection's lock while the application is starting up.
  sqliteDb.run('PRAGMA busy_timeout = 5000');
  runWithSqliteBusyRetry(sqliteDb, 'PRAGMA journal_mode = WAL', dbPath);
  sqliteDb.run('PRAGMA foreign_keys = ON');

  // One native write transaction is the startup lock and the atomic migration
  // unit. It is descriptor-backed (so aliases converge), rolls back on crashes,
  // and avoids EXCLUSIVE-mode upgrade deadlocks between multiple waiters.
  runWithSqliteBusyRetry(sqliteDb, 'BEGIN IMMEDIATE', dbPath);
  try {
    // Handle legacy databases that were created by the old migration system, then
    // reconcile stale migration hashes before applying the embedded migrations.
    seedDrizzleMigrationsForLegacyDb(sqliteDb, MIGRATION_ASSETS);
    repairStaleMigrationHashes(sqliteDb, MIGRATION_ASSETS);

    // The public Bun migrator reads a folder at runtime, which would break a
    // standalone executable. The embedded raw migrator preserves Drizzle's journal.
    // 0002 rebuilds Articles before identity repair and 0003's unique indexes.
    runEmbeddedMigrationPhases(sqliteDb);

    // FTS5 virtual table and triggers (Drizzle can't handle these)
    setupFts5(sqliteDb);
    sqliteDb.run('COMMIT');
  } catch (error) {
    try {
      sqliteDb.run('ROLLBACK');
    } catch (rollbackError) {
      throw new AggregateError([error, rollbackError], 'Database initialization failed and could not be rolled back');
    }
    throw error;
  }
}

/** Run file-backed migrations in a scope that releases the native init lock before handoff. */
function initializeFileDatabase(dbPath: string): void {
  const sqliteDb = new Database(dbPath);
  try {
    runDatabaseInitialization(sqliteDb, dbPath);
  } catch (error) {
    try {
      closeFileInitializationConnection(sqliteDb);
    } catch (closeError) {
      throw new AggregateError([error, closeError], 'Database initialization and connection cleanup both failed');
    }
    throw error;
  }
  try {
    closeFileInitializationConnection(sqliteDb);
  } catch (closeError) {
    throw new Error(`Database initialized but its startup connection could not be released: ${dbPath}`, {
      cause: closeError,
    });
  }
}

/** In-memory databases must retain their sole connection after migration. */
function initializeInMemoryDatabase(dbPath: string): void {
  const sqliteDb = new Database(dbPath);
  try {
    runDatabaseInitialization(sqliteDb, dbPath);
    sqlite = sqliteDb;
    db = drizzle(sqliteDb, { schema });
    activeDbPath = dbPath;
  } catch (error) {
    sqliteDb.close();
    throw error;
  }
}

export function getDbPath(customPath?: string): string {
  const dbPath = customPath || process.env.RSS_DB_PATH || DEFAULT_DB_PATH;
  if (dbPath === ':memory:') {
    return dbPath;
  }
  if (customPath || process.env.RSS_DB_PATH) {
    const validation = validateDbPath(dbPath);
    if (!validation.valid) {
      throw new Error(validation.error);
    }
  }
  const dbDir = dirname(dbPath);

  if (!existsSync(dbDir)) {
    mkdirSync(dbDir, { recursive: true });
  }

  return dbPath;
}

export function getDb(customPath?: string): ReturnType<typeof drizzle> {
  // Commands and TUI code initialize their selected database once, then retrieve
  // that active connection without repeating the path. An explicit path is the
  // only request that may replace the singleton.
  if (db && !customPath) {
    return db;
  }

  const dbPath = getDbPath(customPath);
  if (db && activeDbPath === dbPath) return db;

  closeDb();
  sqlite = new Database(dbPath);

  // This is connection-local and must be first: journal_mode can need an exclusive
  // lock, and without a timeout it fails immediately when another process is closing.
  sqlite.run('PRAGMA busy_timeout = 5000');

  // Enable WAL mode for better performance.
  runWithSqliteBusyRetry(sqlite, 'PRAGMA journal_mode = WAL', dbPath);
  sqlite.run('PRAGMA synchronous = NORMAL');
  sqlite.run('PRAGMA cache_size = 10000');
  sqlite.run('PRAGMA temp_store = MEMORY');
  sqlite.run('PRAGMA foreign_keys = ON'); // Ensure referential integrity

  db = drizzle(sqlite, { schema });
  activeDbPath = dbPath;

  return db;
}

export function closeDb(): void {
  const sqliteDb = sqlite;
  sqlite = null;
  db = null;
  activeDbPath = null;
  if (!sqliteDb) return;

  // The Drizzle session must become unreachable before SQLite is closed.
  // Otherwise Bun's close_v2 defers descriptor release until GC, which can
  // leave a same-process reinitialization blocked by its old connection.
  Bun.gc(true);
  sqliteDb.close();
}

// Initialize database with schema (for programmatic use)
export function initDb(customPath?: string): void {
  const dbPath = getDbPath(customPath);
  closeDb();

  if (dbPath === ':memory:') {
    initializeInMemoryDatabase(dbPath);
    return;
  }

  initializeFileDatabase(dbPath);
  getDb(customPath);
}

export { schema };