import type { Database } from 'bun:sqlite';
import { migrate } from 'drizzle-orm/bun-sqlite/migrator';
import { readMigrationFiles } from 'drizzle-orm/migrator';
import { type DbClient, createDbClient, findMigrationsFolder } from './client';
import { findSchemaDrift } from './schema-introspection';

/**
 * Record every migration past the ledger's watermark as applied, running none
 * of them. Only safe when the caller has already established that the schema
 * the code declares is entirely present.
 *
 * Replaying the statements instead would be wrong, and quietly so. Migrations
 * are not idempotent: `0021_dns_registration_consumers` rebuilds a table by
 * copying it aside, `DROP TABLE`-ing the original and renaming the copy over
 * it. Run that against a schema that is already current and it drops a live
 * table. Skipping the statements that fail with "already exists" does not save
 * you either, because `DROP` and `INSERT ... SELECT` do not fail that way.
 *
 * So the ledger is corrected and the schema is left alone.
 */
function stampLedgerAsApplied(sqlite: Database, migrationsFolder: string): void {
  const newest = sqlite
    .query<{ created_at: number }, []>(
      'SELECT created_at FROM `__drizzle_migrations` ORDER BY created_at DESC LIMIT 1',
    )
    .get();
  const watermark = Number(newest?.created_at ?? 0);

  sqlite.run('BEGIN');
  try {
    for (const migration of readMigrationFiles({ migrationsFolder })) {
      if (migration.folderMillis <= watermark) continue;
      sqlite.run('INSERT INTO `__drizzle_migrations` ("hash", "created_at") VALUES (?, ?)', [
        migration.hash,
        migration.folderMillis,
      ]);
    }
    sqlite.run('COMMIT');
  } catch (error) {
    sqlite.run('ROLLBACK');
    throw error;
  }
}

/**
 * Apply pending drizzle migrations to an open DB. Idempotent — drizzle applies
 * only migrations newer than the latest recorded in `__drizzle_migrations`.
 * The single migration mechanism (ISS-0100); createDbClient also calls this
 * shape on open (auto-migrate).
 *
 * The stock migrator runs first and handles every database celilo has written
 * since ISS-0100, so a healthy box takes exactly the path it took before.
 *
 * It fails on one database celilo did not write: one from the imperative
 * hand-list era, where schema changes were applied directly and
 * `__drizzle_migrations` never recorded them. Its ledger remembers an old
 * migration while the objects of every later one are already there, and
 * drizzle — being watermark-only — re-runs them and dies on the first
 * `ALTER TABLE ... ADD`. The throw happens inside `createDbClient`, so every
 * celilo command on that box fails at database open. celilo-mgr, the one box
 * in that state, was remediated by hand (celilo#169); this is so the next one
 * is not.
 *
 * The repair is only attempted when the schema the code declares is ALREADY
 * COMPLETE, because that is the one case with an unambiguous answer: every
 * migration has plainly run, so the ledger is what is wrong. A database missing
 * some of it is the genuinely hard case — celilo-mgr had 0011's column and not
 * 0010's table — and there is no safe automatic answer, so it keeps failing
 * with drizzle's own error and a human decides.
 */
export function runMigrationsOn(db: DbClient): void {
  const migrationsFolder = findMigrationsFolder();
  try {
    migrate(db, { migrationsFolder });
  } catch (error) {
    const drift = findSchemaDrift(db.$client);
    if (drift.missingTables.length > 0 || drift.missingColumns.length > 0) throw error;
    stampLedgerAsApplied(db.$client, migrationsFolder);
  }
}

/**
 * Open a database at `dbPath` with migrations applied, then close it.
 * Standalone entrypoint (`bun run src/db/migrate.ts`); tests also use it to
 * prepare a fresh scratch database.
 *
 * createDbClient migrates synchronously on open (its own runMigrationsOn
 * call), so this function is open + close. It deliberately does NOT call
 * runMigrationsOn again: a second pass is always a ledger no-op when the
 * first succeeded, and keeping it invited a double-migrate everywhere this
 * entrypoint is used (celilo#1315).
 */
export async function runMigrations(dbPath?: string) {
  console.log('Running database migrations...');
  const db = createDbClient(dbPath ? { path: dbPath } : undefined);
  console.log('Migrations completed successfully');
  // Close the connection THIS call created. It is not the singleton —
  // createDbClient does not register one — so an old closeDb() here closed
  // whatever else was open (usually nothing) and leaked this connection,
  // which then held the file in WAL and blocked every later journal-mode
  // switch on it (celilo#1269).
  db.$client.close();
}

// Run migrations if executed directly
if (import.meta.main) {
  runMigrations().catch((error) => {
    console.error('Failed to run migrations:', error);
    process.exit(1);
  });
}