/**
 * DB schema drift check.
 *
 * Compares the drizzle migrations folder against the `__drizzle_migrations`
 * table. If the folder has more entries than the table records, a
 * `blocked` finding is produced. In the typical celilo runtime, missing
 * migrations are auto-applied at DB connect time (see
 * `apps/celilo/src/db/client.ts:createDbClient`), so this check is a
 * sanity net for cases where the auto-migrate path was skipped or
 * failed silently.
 *
 * The `journalReader` and `appliedReader` deps are injectable so tests
 * don't need a real drizzle journal on disk or a real DB.
 */

import { existsSync, readFileSync } from 'node:fs';
import { join } from 'node:path';
import type { DbClient } from '../../db/client';
import type { DriftFinding } from './types';

interface DrizzleJournalEntry {
  idx: number;
  tag: string;
  when: number;
}

interface DrizzleJournal {
  version: string;
  dialect: string;
  entries: DrizzleJournalEntry[];
}

export type JournalReader = () => DrizzleJournal | null;
export type AppliedReader = (db: DbClient) => string[];

/**
 * Default journal reader: parses `<migrationsFolder>/meta/_journal.json`.
 * Returns null if the journal isn't present (callers treat as "nothing to compare").
 */
export function makeJournalReader(migrationsFolder: string): JournalReader {
  return () => {
    const journalPath = join(migrationsFolder, 'meta', '_journal.json');
    if (!existsSync(journalPath)) return null;
    try {
      return JSON.parse(readFileSync(journalPath, 'utf-8')) as DrizzleJournal;
    } catch {
      return null;
    }
  };
}

/**
 * Default applied-migrations reader: queries `__drizzle_migrations`.
 * Returns an empty array if the table doesn't exist (fresh DB).
 */
export const readAppliedMigrations: AppliedReader = (db) => {
  // The drizzle migrations table tracks `hash` (which is the migration tag).
  // We use a raw query because the table isn't in our schema definitions.
  try {
    const sqlite = (
      db as unknown as { $client?: { query: (sql: string) => { all: () => unknown[] } } }
    ).$client;
    if (!sqlite) return [];
    const rows = sqlite.query('SELECT hash FROM __drizzle_migrations').all() as Array<{
      hash: string;
    }>;
    return rows.map((r) => r.hash);
  } catch {
    return [];
  }
};

export interface SchemaAuditDeps {
  journal: JournalReader;
  applied: AppliedReader;
  db: DbClient;
}

export async function auditSchema(deps: SchemaAuditDeps): Promise<DriftFinding[]> {
  const journal = deps.journal();
  if (!journal) {
    // Pending migrations are the difference between a `.deb` that installed and
    // a `.deb` that works — `apt upgrade` does not run them (celilo#169). With
    // no journal there is nothing to compare against, and silence used to
    // render that as READY.
    return [
      {
        category: 'schema',
        severity: 'unmeasured',
        code: 'schema_journal_unreadable',
        message:
          'No drizzle migration journal could be read, so pending schema migrations are unknown',
        remediation:
          'The installed @celilo/cli is missing its `drizzle/meta/_journal.json`. Reinstall it, then re-audit. This records that the comparison did not happen, not that the schema is current.',
        actionable: false,
        subject: 'system',
      },
    ];
  }

  // drizzle stores SHA-256 hashes (not tag names) in __drizzle_migrations,
  // so we can't intersect tags. The next-best signal is "did all the
  // journal entries get applied?" — compare counts. The applied count
  // can also exceed the journal count for old DBs with stale tracking
  // rows; only flag when applied < journal.
  const applied = deps.applied(deps.db);
  const pendingCount = journal.entries.length - applied.length;
  if (pendingCount <= 0) return [];

  // We can identify *which* migrations are pending by treating the
  // journal as ordered: the last `pendingCount` entries are the ones
  // not yet tracked. Drizzle applies them in idx order, so the tail
  // is the unapplied set.
  const sortedJournal = [...journal.entries].sort((a, b) => a.idx - b.idx);
  const pendingEntries = sortedJournal.slice(-pendingCount);

  return [
    {
      category: 'schema',
      severity: 'blocked',
      code: 'schema_pending_migrations',
      message: `${pendingCount} pending DB migration${pendingCount === 1 ? '' : 's'}`,
      details: pendingEntries.map((e) => `  • ${e.tag}`).join('\n'),
      remediation: [
        'Migrations apply automatically when celilo starts.',
        'Restart any long-running celilo process, or run',
        'bun run apps/celilo/src/db/migrate.ts directly.',
      ].join('\n'),
      actionable: false,
      subject: 'system',
    },
  ];
}