/**
 * Read-only migration interrogation (celilo#604).
 *
 * `system migrate` used to report "Schema current: 35 tables" — a table COUNT,
 * which cannot distinguish "the column migration applied" from "nothing
 * happened". 0019_backup_pid adds a column; a rollout runbook asserting
 * "applied 19 → 20, backups.pid present" had no product surface to check it
 * against and had to reach for `sqlite3` over SSH.
 *
 * This names migrations. Drizzle's `__drizzle_migrations.created_at` is the
 * journal entry's `when`, so the join back to a human tag is exact.
 */

import type { Database } from 'bun:sqlite';
import { existsSync, readFileSync } from 'node:fs';
import { join } from 'node:path';
import { findSchemaDrift } from './schema-introspection';

export interface MigrationStatus {
  /** Rows in `__drizzle_migrations` — what the runbook calls "applied count". */
  appliedCount: number;
  /** Tag of the newest applied migration, e.g. `0019_backup_pid`. */
  latestApplied: string | null;
  /** Tags present on disk that this DB has not applied, oldest first. */
  pending: string[];
  /** Tables the running code declares that the DB lacks. */
  missingTables: string[];
  /** `table.column` the running code declares that the DB lacks. */
  missingColumns: string[];
  tableCount: number;
  columnCount: number;
}

interface JournalEntry {
  when: number;
  tag: string;
}

/** Journal entries (oldest first), or [] when the folder has no journal. */
export function readMigrationJournal(migrationsFolder: string): JournalEntry[] {
  const path = join(migrationsFolder, 'meta', '_journal.json');
  if (!existsSync(path)) return [];
  const parsed = JSON.parse(readFileSync(path, 'utf-8')) as { entries?: JournalEntry[] };
  return [...(parsed.entries ?? [])].sort((a, b) => a.when - b.when);
}

/** `created_at` timestamps recorded in `__drizzle_migrations` (empty if untracked). */
function appliedTimestamps(sqlite: Database): number[] {
  try {
    return sqlite
      .query<{ created_at: number }, []>(
        'SELECT created_at FROM `__drizzle_migrations` ORDER BY created_at',
      )
      .all()
      .map((r) => r.created_at);
  } catch {
    // No migrations table — a DB that has never been through the migrator.
    return [];
  }
}

export function getMigrationStatus(sqlite: Database, migrationsFolder: string): MigrationStatus {
  const journal = readMigrationJournal(migrationsFolder);
  const applied = new Set(appliedTimestamps(sqlite));
  const appliedTags = journal.filter((e) => applied.has(e.when)).map((e) => e.tag);
  const pending = journal.filter((e) => !applied.has(e.when)).map((e) => e.tag);
  const drift = findSchemaDrift(sqlite);

  return {
    appliedCount: applied.size,
    latestApplied: appliedTags.at(-1) ?? null,
    pending,
    missingTables: drift.missingTables,
    missingColumns: drift.missingColumns,
    tableCount: drift.tableCount,
    columnCount: drift.columnCount,
  };
}