// Migration-ledger helper for the vendored Hasna storage kit.
//
// A `schema_migrations` ledger with per-migration sha256 checksums, modeled on
// loops' storage ledger. Guarantees:
//   - each migration runs at most once (idempotent by id),
//   - a migration whose SQL changed after being applied is detected as a
//     checksum mismatch and refuses to proceed (no silent drift),
//   - an applied migration unknown to this binary is detected (downgrade
//     guard), unless the app EXPLICITLY acknowledges it as non-reproducible
//     legacy history via `acknowledgedLegacyIds` — the one deliberate,
//     opt-in widening, documented on the option itself,
//   - `dryRun` reports the plan without mutating anything.
//
// PURE REMOTE (Amendment A1): migrations run against the cloud Postgres. There
// is no local schema and no sync of ledger rows between machines.

import { createHash } from "node:crypto";
import type { TypedQueryClient } from "./query.js";
import { ownProp, ownString } from "./own.js";

/** Default ledger table name. Override per app if a legacy name exists. */
export const DEFAULT_MIGRATION_LEDGER_TABLE = "schema_migrations";

export interface Migration {
  readonly id: string;
  readonly sql: string;
  readonly checksum: string;
}

export type MigrationState = "already_applied" | "pending";

export interface MigrationPlanItem {
  readonly migration: Migration;
  readonly state: MigrationState;
}

export interface AppliedMigration {
  readonly id: string;
  readonly checksum: string;
  readonly appliedAt: string;
}

export interface MigrationResult {
  readonly dryRun: boolean;
  readonly applied: AppliedMigration[];
  readonly plan: MigrationPlanItem[];
}

/** Stable sha256 checksum for a migration's SQL text. */
export function checksumSql(sql: string): string {
  const normalized = sql.trim().replace(/\r\n/g, "\n");
  return `sha256:${createHash("sha256").update(normalized).digest("hex")}`;
}

/** Freeze a migration definition, computing its checksum from the SQL. */
export function defineMigration(id: string, sql: string): Migration {
  return Object.freeze({ id, sql: sql.trim(), checksum: checksumSql(sql) });
}

interface LedgerRow {
  id: string;
  checksum: string;
  applied_at: string | Date;
}

export interface MigrationRunnerOptions {
  ledgerTable?: string;
  /**
   * Applied-ledger rows whose ids the build ACKNOWLEDGES as non-reproducible
   * history: a migration that was applied to the ledger by an out-of-band
   * operation or by a build whose id scheme no longer exists, so no current
   * source can reproduce its id or its SQL.
   *
   * An acknowledged id:
   *   - passes the downgrade guard (it IS recognized — as history),
   *   - is never checksum-compared (its SQL is gone, so no checksum can be
   *     computed for it; storing an arbitrary placeholder in `checksum` is
   *     what the prod ledger already holds for such rows),
   *   - is never re-applied and never re-inserted (it is already in the
   *     ledger; the plan covers declared migrations only).
   *
   * The list is EXPLICIT and OPT-IN: an acknowledged id may not also be a
   * declared migration (enforced at construction), and any OTHER applied row
   * unknown to the build still fails the downgrade guard. Every declared
   * migration keeps its checksum bind unchanged.
   */
  acknowledgedLegacyIds?: readonly string[];
}

export class MigrationLedger {
  private readonly ledgerTable: string;
  private readonly acknowledgedLegacyIds: ReadonlySet<string>;

  constructor(
    private readonly client: TypedQueryClient,
    private readonly migrations: readonly Migration[],
    options: MigrationRunnerOptions = {},
  ) {
    // OWN-property read: `ledgerTable` is interpolated directly into DDL below,
    // so a prototype-supplied value is SQL injection rather than mere config
    // drift.
    this.ledgerTable = ownString(options, "ledgerTable") ?? DEFAULT_MIGRATION_LEDGER_TABLE;
    const seen = new Set<string>();
    for (const migration of migrations) {
      if (seen.has(migration.id)) throw new Error(`Duplicate migration id: ${migration.id}`);
      seen.add(migration.id);
    }
    const rawAcknowledged = ownProp<unknown>(options, "acknowledgedLegacyIds");
    if (rawAcknowledged !== undefined) {
      if (!Array.isArray(rawAcknowledged) || rawAcknowledged.some((id) => typeof id !== "string")) {
        throw new Error("acknowledgedLegacyIds must be an array of migration id strings");
      }
      this.acknowledgedLegacyIds = new Set(rawAcknowledged);
    } else {
      this.acknowledgedLegacyIds = new Set();
    }
    for (const id of this.acknowledgedLegacyIds) {
      if (seen.has(id)) {
        throw new Error(`Acknowledged legacy migration id '${id}' is also declared as a migration.`);
      }
    }
  }

  async ensureLedger(): Promise<void> {
    await this.client.execute(
      `CREATE TABLE IF NOT EXISTS ${this.ledgerTable} (
         id TEXT PRIMARY KEY,
         checksum TEXT NOT NULL,
         applied_at TIMESTAMPTZ NOT NULL DEFAULT now()
       )`,
    );
  }

  async listApplied(): Promise<AppliedMigration[]> {
    await this.ensureLedger();
    return this.readApplied();
  }

  private async readApplied(): Promise<AppliedMigration[]> {
    const rows = await this.client.many<LedgerRow>(
      `SELECT id, checksum, applied_at FROM ${this.ledgerTable} ORDER BY id ASC`,
    );
    return rows.map((row) => ({
      id: row.id,
      checksum: row.checksum,
      appliedAt: row.applied_at instanceof Date ? row.applied_at.toISOString() : String(row.applied_at),
    }));
  }

  /** Compute the migration plan and guard against drift/downgrade. */
  private buildPlan(applied: AppliedMigration[]): MigrationPlanItem[] {
    const known = new Set(this.migrations.map((m) => m.id));
    for (const row of applied) {
      // An acknowledged legacy row is recognized as history: its id is
      // accepted and its checksum is not comparable (the SQL that produced it
      // is gone). Everything else keeps the strict downgrade guard.
      if (known.has(row.id) || this.acknowledgedLegacyIds.has(row.id)) {
        continue;
      }
      throw new Error(`Applied migration '${row.id}' is not recognized by this build (downgrade?).`);
    }
    const appliedById = new Map(applied.map((row) => [row.id, row]));
    for (const migration of this.migrations) {
      const existing = appliedById.get(migration.id);
      if (existing && existing.checksum !== migration.checksum) {
        throw new Error(
          `Migration checksum mismatch for '${migration.id}': the SQL changed after it was applied.`,
        );
      }
    }
    return this.migrations.map((migration) => ({
      migration,
      state: appliedById.has(migration.id) ? "already_applied" : "pending",
    }));
  }

  /** Apply all pending migrations. With `dryRun`, report the plan only. */
  async migrate(opts: { dryRun?: boolean } = {}): Promise<MigrationResult> {
    // OWN-property read: a prototype-supplied `dryRun` would turn every
    // `migrate()` call into a no-op that still reports a plan.
    const dryRun = ownProp<unknown>(opts, "dryRun") === true;
    await this.ensureLedger();
    const applied = await this.readApplied();
    const plan = this.buildPlan(applied);
    if (dryRun) return { dryRun, applied, plan };

    for (const item of plan) {
      if (item.state === "already_applied") continue;
      await this.client.execute(item.migration.sql);
      await this.client.execute(
        `INSERT INTO ${this.ledgerTable} (id, checksum, applied_at) VALUES ($1, $2, now())`,
        [item.migration.id, item.migration.checksum],
      );
    }
    return { dryRun, applied: await this.readApplied(), plan };
  }
}

/** Convenience: build a ledger and run all pending migrations. */
export function createMigrationLedger(
  client: TypedQueryClient,
  migrations: readonly Migration[],
  options: MigrationRunnerOptions = {},
): MigrationLedger {
  return new MigrationLedger(client, migrations, options);
}