/**
 * Schema introspection — compare the DB's actual tables/columns against the
 * tables the running code's drizzle schema declares. The single source for
 * "what does the code expect, and is it present?" shared by:
 *   - the migration baseline (db/migrate.ts) — to decide which migrations are
 *     already applied on an existing DB before running migrate() (ISS-0100), and
 *   - the doctor's schema-drift check (services/fleet-checks.ts) — to report
 *     missing tables/columns to the operator (ISS-0113).
 */

import type { Database } from 'bun:sqlite';
import { is } from 'drizzle-orm';
import { SQLiteTable, getTableConfig } from 'drizzle-orm/sqlite-core';
import * as dbSchema from './schema';

export interface SchemaDrift {
  /** Tables the code's schema declares that the DB lacks. */
  missingTables: string[];
  /** `table.column` the code declares that the DB's table lacks. */
  missingColumns: string[];
  /** Total number of tables the code's schema declares. */
  tableCount: number;
  /**
   * Total number of columns the code's schema declares. Reported alongside
   * tableCount so the doctor can say what it actually checked — a table count
   * alone reads as "columns unverified" even when they were (celilo#604).
   */
  columnCount: number;
}

/** Every table name + column names the drizzle schema declares. */
export function getSchemaTables(): Array<{ name: string; columns: string[] }> {
  const out: Array<{ name: string; columns: string[] }> = [];
  for (const value of Object.values(dbSchema)) {
    if (!is(value, SQLiteTable)) continue;
    const cfg = getTableConfig(value);
    out.push({ name: cfg.name, columns: cfg.columns.map((c) => c.name) });
  }
  return out;
}

/** Names of all tables that physically exist in the DB. */
export function getExistingTables(sqlite: Database): Set<string> {
  return new Set(
    sqlite
      .query<{ name: string }, []>("SELECT name FROM sqlite_master WHERE type='table'")
      .all()
      .map((r) => r.name),
  );
}

/** Names of all indexes that physically exist in the DB. */
export function getExistingIndexes(sqlite: Database): Set<string> {
  return new Set(
    sqlite
      .query<{ name: string }, []>("SELECT name FROM sqlite_master WHERE type='index'")
      .all()
      .map((r) => r.name),
  );
}

/** Column names physically present on a table (empty if the table is absent). */
export function getExistingColumns(sqlite: Database, table: string): Set<string> {
  // `table` is a schema/migration identifier (never user input) — safe to inline.
  return new Set(
    sqlite
      .query<{ name: string }, []>(`SELECT name FROM pragma_table_info('${table}')`)
      .all()
      .map((r) => r.name),
  );
}

/**
 * Compare the running code's drizzle schema to the DB and report what's missing.
 * Track-agnostic: it reads actual table/column presence, so it's honest whether
 * the DB was migrated, baselined, or hand-patched.
 */
export function findSchemaDrift(sqlite: Database): SchemaDrift {
  const present = getExistingTables(sqlite);
  const missingTables: string[] = [];
  const missingColumns: string[] = [];
  const tables = getSchemaTables();
  for (const t of tables) {
    if (!present.has(t.name)) {
      missingTables.push(t.name);
      continue;
    }
    const cols = getExistingColumns(sqlite, t.name);
    for (const c of t.columns) {
      if (!cols.has(c)) missingColumns.push(`${t.name}.${c}`);
    }
  }
  const columnCount = tables.reduce((n, t) => n + t.columns.length, 0);
  return { missingTables, missingColumns, tableCount: tables.length, columnCount };
}