/**
 * `celilo system migrate` — apply pending DB migrations (ISS-0100).
 *
 * Drizzle's migrator is the single migration mechanism; createDbClient already
 * auto-migrates on open, so this command is the explicit, operator-visible
 * entrypoint the .deb postinst and celilo-mgmt deploy call. Idempotent: a
 * current DB reports "up to date".
 */

import type { Database } from 'bun:sqlite';
import { defineEvents, openBus } from '@celilo/event-bus';
import { getEventBusPath } from '../../config/paths';
import { createDbClient, findMigrationsFolder, getDb } from '../../db/client';
import { runMigrationsOn } from '../../db/migrate';
import { getMigrationStatus } from '../../db/migration-status';
import { findSchemaDrift } from '../../db/schema-introspection';
import { migrateMonitorCadences } from '../../services/alerting/cadence-migration';
import { ensureBackupSweepSubscriber } from '../../services/backup-sweep';
import { ensureOperationsSweepSubscriber } from '../../services/module-operations';
import type { CommandResult } from '../types';

/**
 * Arm celilo's own housekeeping subscribers.
 *
 * Here because this command is what the .deb postinst runs on every apt
 * upgrade — the one moment guaranteed to happen after new CLI code lands.
 * A subscriber registered only from module install/update would not appear
 * until some module happened to be touched next, which can be weeks and
 * looks exactly like a feature that shipped and silently does nothing.
 *
 * Best-effort: a bus that can't be opened must not fail a schema migration.
 */
function ensureCoreSubscribers(): void {
  try {
    const bus = openBus({ dbPath: getEventBusPath(), events: defineEvents({}) });
    try {
      ensureOperationsSweepSubscriber(bus);
      // The backup sweep is armed here for the same reason, plus a sharper one:
      // its row already exists on every deployed fleet, carrying the 60s bus
      // default that made scheduled backups impossible. Correcting the default
      // in code does nothing until something re-registers, and module
      // install/update can be weeks away. This is the upgrade path.
      //
      // This also re-arms a sweep an operator paused by hand. Deliberate: the
      // pause exists only because staging leaked, and the reaper that stops it
      // leaking ships in this same binary.
      ensureBackupSweepSubscriber(bus);
    } finally {
      bus.close();
    }
  } catch {
    // Nothing to do — the next module install/update arms it.
  }
}

function countApplied(sqlite: Database): number {
  try {
    const row = sqlite
      .query<{ c: number }, []>('SELECT COUNT(*) AS c FROM `__drizzle_migrations`')
      .get();
    return row?.c ?? 0;
  } catch {
    return 0;
  }
}

/**
 * `celilo system migrate --status` — read-only interrogation (celilo#604).
 *
 * A rollout runbook that says "assert applied 19 → 20 and `backups.pid`
 * present" needs a product surface to assert against; the table count that used
 * to be the only answer cannot see a column migration at all.
 */
export function migrationStatusResult(sqlite: Database): CommandResult {
  const status = getMigrationStatus(sqlite, findMigrationsFolder());
  const missing = [...status.missingTables, ...status.missingColumns];
  const lines = [
    `Applied migrations: ${status.appliedCount}`,
    `Latest applied:     ${status.latestApplied ?? '(none)'}`,
    status.pending.length > 0
      ? `Pending:            ${status.pending.join(', ')}`
      : 'Pending:            none',
    `Schema present:     ${status.tableCount} tables, ${status.columnCount} columns`,
    ...(missing.length > 0 ? [`Missing:            ${missing.join(', ')}`] : []),
  ];
  if (status.pending.length > 0 || missing.length > 0) {
    return {
      success: false,
      error: `${lines.join('\n')}\n\nRun \`celilo system migrate\` to apply pending migrations on this box.`,
    };
  }
  return { success: true, message: lines.join('\n'), data: status };
}

export async function handleSystemMigrate(
  _args: string[] = [],
  flags: Record<string, string | boolean> = {},
): Promise<CommandResult> {
  // --status must NOT migrate. getDb() auto-migrates on open, so a status that
  // went through it would repair the very state it claims to be reporting and
  // could never say "pending" — the placebo shape this command exists to end.
  if (flags.status) {
    const ro = createDbClient({ readonly: true });
    try {
      return migrationStatusResult(ro.$client);
    } finally {
      ro.$client.close();
    }
  }

  // getDb() auto-migrates on open, and repairs a frozen `__drizzle_migrations`
  // watermark itself when the declared schema is already complete. What reaches
  // this catch is the case it will not guess at: schema that is only PARTLY
  // there, where stamping would record migrations that never ran. Caught so it
  // says what to do instead of surfacing a raw migrator error.
  let db: ReturnType<typeof getDb>;
  try {
    db = getDb();
  } catch (error) {
    const msg = error instanceof Error ? error.message : String(error);
    return {
      success: false,
      error: `Migration failed: ${msg}\n\nThis DB's \`__drizzle_migrations\` watermark disagrees with a schema that is only partly applied, which celilo will not resolve on its own. It needs a one-time remediation by hand (create the genuinely missing objects from their migration .sql, then stamp the watermark to the latest migration) — runbook in celilo#169.`,
    };
  }

  const sqlite = db.$client;
  const before = countApplied(sqlite);
  try {
    runMigrationsOn(db); // idempotent re-assert
  } catch (error) {
    return { success: false, error: error instanceof Error ? error.message : String(error) };
  }
  const applied = countApplied(sqlite) - before;

  const drift = findSchemaDrift(sqlite);
  if (drift.missingTables.length > 0 || drift.missingColumns.length > 0) {
    const missing = [...drift.missingTables, ...drift.missingColumns].join(', ');
    return {
      success: false,
      error: `Schema still behind after migrate — missing: ${missing}. This DB likely needs one-time remediation — see ISS-0100.`,
    };
  }

  ensureCoreSubscribers();

  // A health-check cadence used to live on the monitor row and now resolves
  // from `module_configs`. Without carrying the existing rows over, this very
  // upgrade would silently revert every operator's cadence to the manifest's
  // suggestion and resume watching modules they deliberately stopped watching.
  // Idempotent: writes only where no override exists.
  const cadences = migrateMonitorCadences(db);

  // Name the latest migration, not just a table count: "35 tables" reads the
  // same whether a column migration applied or silently did nothing (celilo#604).
  const status = getMigrationStatus(sqlite, findMigrationsFolder());
  const lines = [
    applied > 0 ? `Applied ${applied} migration(s).` : 'Schema already up to date.',
    `Applied migrations: ${status.appliedCount} (latest: ${status.latestApplied ?? 'none'})`,
    `Schema current: ${drift.tableCount} tables, ${drift.columnCount} columns.`,
    ...(cadences.written.size > 0
      ? [
          `Carried ${cadences.written.size} health-check cadence(s) into module config:`,
          ...[...cadences.written].map(([moduleId, value]) => `  ${moduleId}: ${value}`),
        ]
      : []),
  ];
  return { success: true, message: lines.join('\n'), data: status };
}