// Shared core for the schema-migration CLI (generate | apply | baseline | status).
//
// Used by BOTH the dev `kumiko schema` command (bin/commands/schema.ts) and the
// shipped `kumiko-schema` bin (dev-server) — so apps run migrations without the
// full dev-CLI registry (which eager-loads ts-morph-heavy dev commands).
//
// NO-MAGIC-ON-DATA: reads only checked-in artifacts (kumiko/schema.ts →
// ENTITY_METAS, kumiko/migrations/*.sql). Never auto-generates at runtime,
// never applies on app-boot — apply/baseline are explicit deploy-steps.

import { existsSync, mkdirSync, readdirSync, writeFileSync } from "node:fs";
import { join, resolve as resolvePath } from "node:path";
import {
  baselineMigrations,
  createDbConnection,
  type DbConnection,
  diffReplayAgainstSnapshot,
  fetchAppliedMigrations,
  generateMigration,
  loadMigrationsFromDir,
  loadSnapshotJson,
  readRebuildMarker,
  rebuildTablesFromDiff,
  type renderTablesDdl,
  replayMigrationsDir,
  runMigrationsFromDir,
  tableExists,
  writeRebuildMarker,
  writeSnapshotJson,
} from "./db";
import { validateBoot } from "./engine/boot-validator";
import { createRegistry } from "./engine/registry";
import type { FeatureDefinition } from "./engine/types/feature";
import { createEventsTable } from "./event-store";
import { buildProjectionTableIndex } from "./migrations";
import {
  createEventConsumerStateTable,
  createProjectionStateTable,
  rebuildProjection,
} from "./pipeline";
import { ensureTemporalPolyfill } from "./time";

export type SchemaCliOut = {
  readonly log: (line: string) => void;
  readonly err: (line: string) => void;
};

const SNAPSHOT_FILENAME = ".snapshot.json";

type SchemaMod = {
  readonly entityMetas: Parameters<typeof renderTablesDdl>[0];
  readonly features?: unknown;
};

// Single load-path for kumiko/schema.ts (512/3) — `generate` and `validate`
// both need ENTITY_METAS (and validate additionally wants FEATURES); this is
// the one place that enforces "ENTITY_METAS must be an array" so the two
// commands can't drift on the check or its wording. Throws — callers that
// want a graceful exit code (validate) catch it themselves.
async function loadSchemaModFromApp(schemaFile: string): Promise<SchemaMod> {
  // bun imports TS directly — no spawn needed.
  const mod = (await import(schemaFile)) as { ENTITY_METAS?: unknown; FEATURES?: unknown };
  if (!Array.isArray(mod.ENTITY_METAS)) {
    throw new Error(
      `Schema file ${schemaFile} muss \`export const ENTITY_METAS: EntityTableMeta[]\` haben.`,
    );
  }
  return {
    entityMetas: mod.ENTITY_METAS as Parameters<typeof renderTablesDdl>[0],
    features: mod.FEATURES,
  };
}

function nextSequenceNumber(migrationsDir: string): number {
  if (!existsSync(migrationsDir)) return 1;
  const files = readdirSync(migrationsDir).filter((f) => f.endsWith(".sql"));
  let max = 0;
  for (const f of files) {
    const m = f.match(/^(\d+)_/);
    if (m) {
      const n = Number(m[1]);
      if (n > max) max = n;
    }
  }
  return max + 1;
}

// Maps changed tables to their projections (via the app registry) and replays
// the events. Tables without a registered projection are skipped.
async function rebuildAffectedProjections(
  db: DbConnection,
  changedTables: readonly string[],
  features: readonly FeatureDefinition[],
  out: SchemaCliOut,
): Promise<void> {
  const registry = createRegistry(features);
  const tableToProjection = buildProjectionTableIndex(registry);

  const projections = new Set<string>();
  for (const table of changedTables) {
    const name = tableToProjection.get(table);
    if (name) projections.add(name);
  }
  // skip: no changed table maps to a registered projection — nothing to rebuild.
  if (projections.size === 0) return;

  out.log(`  Rebuild ${projections.size} Projection(s)…`);
  for (const name of projections) {
    const r = await rebuildProjection(name, { db, registry });
    out.log(`    ↻ ${name} (${r.eventsProcessed} events, ${r.durationMs}ms)`);
  }
  out.log("");
}

export type RunSchemaCliOptions = {
  /** Composed app features. When given, `apply` rebuilds the projections whose
   *  tables a freshly applied migration changed (via its `.rebuild.json`
   *  marker). Omitted (dev `kumiko schema`) → no rebuild, migrations only. */
  readonly features?: readonly FeatureDefinition[];
};

/**
 * Runs a schema-CLI subcommand. `appCwd` is the app workspace root (where
 * `kumiko/schema.ts` + `kumiko/migrations/` live). Returns a process exit code.
 */
export async function runSchemaCli(
  argv: readonly string[],
  appCwd: string,
  out: SchemaCliOut,
  options: RunSchemaCliOptions = {},
): Promise<number> {
  // runProdApp/runDevApp install this at boot; the standalone CLI (the
  // migrate-db initContainer, `bun kumiko.js schema apply`) never goes through
  // that boot path, so a projection rebuild's tz/timestamp coercion throws
  // "Temporal is not defined" — deterministically, on every rebuild-marker
  // migration, since the crashed process still records the migration as
  // applied and the rebuild is never retried.
  await ensureTemporalPolyfill();
  const sub = argv[0];
  const schemaFile = resolvePath(appCwd, "kumiko/schema.ts");
  const migrationsDir = resolvePath(appCwd, "kumiko/migrations");

  switch (sub) {
    case "generate": {
      const name = argv[1];
      if (name === "--help" || name === "-h") {
        out.log("  Usage: schema generate <name>");
        return 0;
      }
      if (!name) {
        out.err("  Usage: schema generate <name>");
        return 1;
      }
      // name lands unescaped in `${seq}_${name}.sql` (generateMigration) — this
      // allowlist blocks flag-like names and path traversal (`../../x`).
      if (name.startsWith("-") || !/^[A-Za-z0-9_-]+$/.test(name)) {
        out.err(`  Invalid migration name "${name}" — use letters, digits, "-", "_" only.`);
        out.err("  Usage: schema generate <name>");
        return 1;
      }
      if (!existsSync(schemaFile)) {
        out.err(`  ${schemaFile} fehlt.`);
        out.err("  App-Convention: kumiko/schema.ts mit");
        out.err("    export const ENTITY_METAS: EntityTableMeta[] = [...]");
        return 1;
      }
      const { entityMetas: metas } = await loadSchemaModFromApp(schemaFile);
      const snapshotPath = join(migrationsDir, SNAPSHOT_FILENAME);
      const prevSnapshot = existsSync(snapshotPath) ? loadSnapshotJson(snapshotPath) : null;
      const result = generateMigration({
        metas,
        prevSnapshot,
        name,
        sequenceNumber: nextSequenceNumber(migrationsDir),
      });

      const isEmpty =
        result.diff.newTables.length === 0 &&
        result.diff.changedTables.length === 0 &&
        result.diff.droppedTables.length === 0;
      if (isEmpty) {
        out.log("  No schema changes detected — kein neues Migration-File geschrieben.");
        return 0;
      }

      if (!existsSync(migrationsDir)) mkdirSync(migrationsDir, { recursive: true });
      writeFileSync(join(migrationsDir, result.filename), result.sqlContent);
      writeSnapshotJson(snapshotPath, result.snapshot);

      // Rebuild-Marker nur für inkrementelle Migrationen — die Init-Migration
      // (prevSnapshot===null) legt nur Tabellen an, es gibt keine historischen
      // Events zum Replayen.
      const rebuildTables = prevSnapshot === null ? [] : rebuildTablesFromDiff(result.diff);
      writeRebuildMarker(migrationsDir, result.filename, rebuildTables);

      out.log("");
      out.log(`  ✓ ${result.filename}`);
      out.log(
        `    new tables: ${result.diff.newTables.length}, changed: ${result.diff.changedTables.length}, dropped: ${result.diff.droppedTables.length}`,
      );
      if (rebuildTables.length > 0) {
        out.log(
          `    rebuild-marker: ${result.filename.replace(/\.sql$/, ".rebuild.json")} (${rebuildTables.length} table(s))`,
        );
      }
      out.log("");
      out.log("  Review + ggf. hand-edit + git add + commit. Apply via: schema apply");
      out.log("");
      return 0;
    }

    case "validate": {
      // Static, DB-free boot-blocking checks for CI — catches "this won't boot"
      // before deploy. Three layers, no database:
      //   1. schema drift: would `generate` write a migration? (= an entity was
      //      added/changed but never generated → missing table → prod 500)
      //   2. boot validity: validateBoot over the composed FEATURES (QN/screen/
      //      nav/role refs). Runs only if kumiko/schema.ts exports FEATURES.
      //   3. migration-content drift: replay every committed *.sql file and
      //      diff the result against .snapshot.json — catches a migration
      //      file whose SQL body doesn't match what its own snapshot entry
      //      claims (e.g. an accidental copy-paste from an earlier file).
      //      Layer 1 alone misses this: it only compares ENTITY_METAS to the
      //      snapshot, never the snapshot to the SQL that's supposed to have
      //      produced it.
      // The DB-level gate (assertKumikoSchemaCurrent) stays at boot/deploy.
      if (!existsSync(schemaFile)) {
        out.err(`  ${schemaFile} fehlt.`);
        out.err("  App-Convention: kumiko/schema.ts mit");
        out.err("    export const ENTITY_METAS: EntityTableMeta[] = [...]");
        return 1;
      }
      let schemaMod: SchemaMod;
      try {
        schemaMod = await loadSchemaModFromApp(schemaFile);
      } catch (e) {
        out.err(`  ${e instanceof Error ? e.message : String(e)}`);
        return 1;
      }
      const mod = { FEATURES: schemaMod.features };
      let ok = true;

      // 1. Schema drift — compute the diff, never write.
      const metas = schemaMod.entityMetas;
      const snapshotPath = join(migrationsDir, SNAPSHOT_FILENAME);
      const prevSnapshot = existsSync(snapshotPath) ? loadSnapshotJson(snapshotPath) : null;
      const drift = generateMigration({
        metas,
        prevSnapshot,
        name: "validate",
        sequenceNumber: nextSequenceNumber(migrationsDir),
      });
      const pendingTables = [
        ...drift.diff.newTables.map((t) => t.tableName),
        ...drift.diff.changedTables.map((t) => t.tableName),
        ...drift.diff.droppedTables,
      ];
      if (pendingTables.length === 0) {
        out.log("  ✓ schema: migrations match the entity definitions");
      } else {
        ok = false;
        out.err("  ✗ schema drift: entity definitions are ahead of kumiko/migrations.");
        out.err(
          `    pending — new: ${drift.diff.newTables.length}, changed: ${drift.diff.changedTables.length}, dropped: ${drift.diff.droppedTables.length}`,
        );
        out.err(`    tables: ${pendingTables.join(", ")}`);
        out.err("    Fix: `kumiko-schema generate <name>`, then commit the migration.");
      }

      // 2. Boot validity — needs the composed feature set.
      if (Array.isArray(mod.FEATURES)) {
        try {
          validateBoot(mod.FEATURES as readonly FeatureDefinition[]);
          out.log("  ✓ boot: feature configuration is valid");
        } catch (e) {
          ok = false;
          out.err(`  ✗ boot: ${e instanceof Error ? e.message : String(e)}`);
        }
      } else {
        out.log(
          "  · boot: skipped — add `export const FEATURES = composeFeatures(APP_FEATURES, { includeBundled: true })` to kumiko/schema.ts to enable validateBoot.",
        );
      }

      // 3. Migration-content drift — replay the committed *.sql files and
      // diff the reconstructed schema against .snapshot.json.
      if (existsSync(migrationsDir) && prevSnapshot !== null) {
        try {
          const replayed = replayMigrationsDir(migrationsDir);
          const mismatches = diffReplayAgainstSnapshot(replayed, prevSnapshot);
          if (mismatches.length === 0) {
            out.log("  ✓ migrations: table/column names match .snapshot.json");
          } else {
            ok = false;
            out.err(
              "  ✗ migration-content drift: committed *.sql files don't produce .snapshot.json.",
            );
            for (const m of mismatches) {
              out.err(`    ${m.tableName} (${m.kind}): ${m.detail}`);
            }
            if (mismatches.some((m) => m.kind === "unexpected-table")) {
              out.err(
                "    Fix (unexpected-table): build a meta via `defineUnmanagedTable()` " +
                  "from `@cosmicdrift/kumiko-framework/db`, then `r.storeTable(meta, { reason: ... })` " +
                  "inside a feature — this adds it to ENTITY_METAS immediately; the .snapshot.json " +
                  "only picks it up on the NEXT `kumiko-schema generate` run, which you still need " +
                  "to run and commit. `table()` returns a query handle, not a storeTable()-compatible " +
                  "meta — don't pass its result to storeTable(). See the bundled `jobs` feature's " +
                  "job-run-log store table for the pattern.",
              );
            }
            if (mismatches.some((m) => m.kind !== "unexpected-table")) {
              out.err(
                "    Fix (missing-table/column-drift): a migration file's body doesn't match " +
                  "what it (or the snapshot) claims — hand-fix the file, or ship a corrective " +
                  "migration if it's already applied in prod.",
              );
            }
          }
        } catch (e) {
          // replayMigrationsDir fail-loud's on a table-DDL statement it can't
          // parse — that must surface as a normal ✗ line like every other
          // check here, not a raw stack trace that kills the process before
          // any later check (or the Fix: help) ever runs (framework#1535).
          ok = false;
          out.err(`  ✗ migration-content: ${e instanceof Error ? e.message : String(e)}`);
          out.err(
            "    Fix: a statement in a migration file isn't recognizable to the replay parser — extend the parser's recognized patterns, or reword the migration to one it understands.",
          );
        }
      }

      return ok ? 0 : 1;
    }

    case "apply": {
      const dbUrl = process.env["DATABASE_URL"];
      if (!dbUrl) {
        out.err("  DATABASE_URL not set.");
        return 1;
      }
      if (!existsSync(migrationsDir)) {
        out.err(`  ${migrationsDir} fehlt — erst schema generate <name>.`);
        return 1;
      }
      const { db, close } = createDbConnection(dbUrl);
      try {
        const result = await runMigrationsFromDir(db, migrationsDir);
        // Framework-Infra-Tabellen (event-store + pipeline-state) — die erfasst
        // `generate` nicht (nur Entity-read-Tabellen). Bestehende DBs haben sie
        // aus dem legacy-drizzle-Fundament; eine Greenfield-DB (erste App ohne
        // Cutover) hätte sonst kein kumiko_events → runProdApp-Boot scheitert.
        // Alle drei sind idempotent (tableExists-Gate), also no-op für Bestands-DBs.
        await createEventsTable(db);
        await createEventConsumerStateTable(db);
        await createProjectionStateTable(db);
        out.log("");
        if (result.applied.length === 0) {
          out.log(`  ✓ All ${result.skipped.length} migrations already applied.`);
        } else {
          out.log(`  ✓ Applied ${result.applied.length}:`);
          for (const id of result.applied) out.log(`    + ${id}`);
          if (result.skipped.length > 0) out.log(`  (${result.skipped.length} already applied)`);
        }
        out.log("");

        // Projection-rebuild for tables a freshly applied migration changed
        // (marker NNNN_<name>.rebuild.json from `generate`). Without it read_*
        // projections stay stale after a schema change. Needs the composed
        // feature set → only when the caller passed `features` (the app bin);
        // the dev CLI omits it and applies migrations only.
        if (options.features && result.applied.length > 0) {
          const changedTables = new Set<string>();
          for (const id of result.applied) {
            for (const table of readRebuildMarker(migrationsDir, id)) changedTables.add(table);
          }
          if (changedTables.size > 0) {
            await rebuildAffectedProjections(db, [...changedTables], options.features, out);
          }
        }
        return 0;
      } catch (e) {
        out.err("");
        out.err(`  ✗ ${e instanceof Error ? (e.stack ?? e.message) : String(e)}`);
        out.err("");
        return 1;
      } finally {
        await close();
      }
    }

    case "baseline": {
      // Adopt an existing DB: mark all checked-in migrations as applied WITHOUT
      // running their SQL (prod tables already exist — cutover from the legacy
      // drizzle system). Afterwards the boot-gate is drift-free.
      const dbUrl = process.env["DATABASE_URL"];
      if (!dbUrl) {
        out.err("  DATABASE_URL not set.");
        return 1;
      }
      if (!existsSync(migrationsDir)) {
        out.err(`  ${migrationsDir} fehlt — erst schema generate <name>.`);
        return 1;
      }
      const { db, close } = createDbConnection(dbUrl);
      try {
        const result = await baselineMigrations(db, loadMigrationsFromDir(migrationsDir));
        out.log("");
        out.log(`  ✓ Marked ${result.marked.length} migration(s) as applied (no SQL run):`);
        for (const id of result.marked) out.log(`    + ${id}`);
        if (result.alreadyTracked.length > 0) {
          out.log(`  (${result.alreadyTracked.length} already tracked)`);
        }
        out.log("");
        return 0;
      } catch (e) {
        out.err(`  ✗ ${e instanceof Error ? e.message : String(e)}`);
        return 1;
      } finally {
        await close();
      }
    }

    case "status": {
      const dbUrl = process.env["DATABASE_URL"];
      if (!dbUrl) {
        out.err("  DATABASE_URL not set.");
        return 1;
      }
      if (!existsSync(migrationsDir)) {
        out.log("  Kein kumiko/migrations/ — App ist noch auf dem alten drizzle-Pfad.");
        return 0;
      }
      const local = loadMigrationsFromDir(migrationsDir);
      const { db, close } = createDbConnection(dbUrl);
      try {
        // Frische DB ohne je gelaufenes `kumiko schema apply` → tracking-table
        // fehlt = "nichts applied". Connection-/Permission-Fehler dagegen
        // sollen NICHT geschluckt werden (False-pending verschleiert das Problem) —
        // tableExists prüft gezielt nur Existenz, alles andere propagiert.
        const trackingExists = await tableExists(db, "_kumiko_migrations");
        const applied = trackingExists
          ? new Set((await fetchAppliedMigrations(db)).map((a) => a.id))
          : new Set<string>();
        out.log("");
        out.log(`  ${local.length} migrations in ${migrationsDir}:`);
        for (const m of local) out.log(`    ${applied.has(m.id) ? "✓" : " "} ${m.id}`);
        const pending = local.filter((m) => !applied.has(m.id)).length;
        out.log("");
        out.log(`  ${applied.size} applied, ${pending} pending.`);
        out.log("");
        return pending === 0 ? 0 : 1;
      } finally {
        await close();
      }
    }

    default: {
      out.log("");
      out.log("  Subcommands:");
      out.log("    generate <name>   Schreibe neue Migration aus EntityTableMeta-Diff");
      out.log("    validate          Static CI-Gate (kein DB): schema-drift + validateBoot");
      out.log("    apply             Applied pending checked-in SQL-Files");
      out.log("    baseline          Markiere checked-in Migrations als applied (kein SQL-Run)");
      out.log("    status            Liste applied vs pending");
      out.log("");
      return 0;
    }
  }
}