import type {
  Change, EmitResult, ColumnDescriptor, IndexDescriptor, FkDescriptor,
  TableDescriptor, ViewDescriptor, ColumnDefault, FkAction,
} from "../types.js";
import type { SqlType } from "../sql-type.js";
import { DEFAULT_DB_SCHEMA_POSTGRES } from "@metaobjectsdev/metadata";
import { renderFingerprintMarker, viewFingerprint } from "../view-fingerprint.js";
import { viewReplaceIsLegal } from "../view-column-types.js";
import { columnDefaultsEqual } from "../diff/index.js";

// Stages run low → high. drop-view runs BEFORE drop-table so a view that
// depends on a soon-to-be-dropped table is removed first. create-view runs
// AFTER add-fk so the view can reference the new schema in full.
//
// #255: a constraint/index DROP and its ADD counterpart share the same kind
// but need OPPOSITE ordering relative to column mutation — a drop must run
// BEFORE the column change (the constraint/index must be gone before its
// column is dropped, or Postgres refuses `DROP COLUMN` with "other objects
// depend on it" — this applies just as much to an index backing a UNIQUE/FK
// target as it does to the FK/CHECK constraint itself), while an add must run
// AFTER (the column it references must already exist). One stage can't
// satisfy both, so ALL drops — drop-fk/drop-check/drop-index — are hoisted
// ahead of any column mutation; their ADD counterparts (add-fk/add-check/
// add-index) stay at their later stages.
//
// Within that "drops" group, drop-fk/drop-check must ALSO run BEFORE
// drop-index: an FK constraint depends on the unique/PK index backing its
// target column (that's how Postgres enforces the target must be unique), so
// dropping the index first fails with the same "other objects depend on it"
// class of error, one level removed — "constraint … depends on index …".
// drop-index therefore gets its own stage (1.5) strictly between drop-fk/
// drop-check/create-table (1) and column mutation (2).
const STAGE_ORDER: Record<Change["kind"], number> = {
  "drop-view": 0,
  "drop-fk": 1, "drop-check": 1,
  "create-table": 1,
  "drop-index": 1.5,
  "add-column": 2, "drop-column": 2,
  "change-column-type": 2, "change-column-nullable": 2, "change-column-default": 2,
  "rename-column": 3, "rename-table": 3,
  "add-index": 4,
  "add-fk": 5,
  "add-check": 5,
  "drop-table": 6,
  "create-view": 7, "replace-view": 7,
};

export function renderPostgres(changes: Change[]): EmitResult {
  const sorted = [...changes].sort((a, b) => STAGE_ORDER[a.kind] - STAGE_ORDER[b.kind]);
  const upStmts: string[] = [];
  const downStmts: string[] = [];
  for (const c of sorted) {
    upStmts.push(renderUp(c));
    downStmts.push(renderDown(c));
  }
  // Down runs in reverse order (so creates undo correctly w.r.t. FKs).
  const upHelpers = conversionHelpers(sorted, "up");
  const downHelpers = conversionHelpers(sorted, "down");
  return {
    up: [
      ...createSchemaStmts(sorted), ...upHelpers.map(createHelper),
      ...upStmts, ...upHelpers.map(dropHelper),
    ].join("\n\n"),
    down: [
      ...downHelpers.map(createHelper),
      ...[...downStmts].reverse(), ...downHelpers.map(dropHelper),
    ].join("\n\n"),
    recreatedTables: new Set(), // postgres alters in place; no recreate-and-copy
  };
}

/**
 * `CREATE SCHEMA IF NOT EXISTS` for every non-default schema this migration creates
 * an object in, ahead of everything else it emits.
 *
 * A chain must be appliable to a VIRGIN database (#313), and `CREATE TABLE "s"."x"`
 * fails there unless `s` exists — yet `CREATE SCHEMA` was emitted nowhere in either
 * emitter, only by the ledger's own setup. So an `@schema` project's chain could
 * never be replayed, and the first `apply-pending` against a fresh CI database died.
 *
 * VIEWS count, not only tables: a first migration that creates only a view in a
 * non-default schema fails identically. A `create-view` carries the schema in two
 * places and the change's own key wins, matching `renderCreateView(c.view, c.schema)`.
 *
 * `IF NOT EXISTS` because a later migration in the same chain, or an operator, may
 * have created it already. Sorted so output is deterministic — the committed snapshot
 * and the golden tests depend on that. Deliberately NOT dropped in `down`: the schema
 * may hold objects this tool does not own and cannot restore.
 */
function createSchemaStmts(sorted: readonly Change[]): string[] {
  const schemas = new Set<string>();
  for (const c of sorted) {
    const s =
      c.kind === "create-table" ? c.table.schema
      : c.kind === "create-view" ? (c.schema ?? c.view.schema)
      : undefined;
    if (s !== undefined && s !== DEFAULT_DB_SCHEMA_POSTGRES) schemas.add(s);
  }
  return [...schemas].sort().map((s) => `CREATE SCHEMA IF NOT EXISTS ${quote(s)};`);
}

function renderUp(c: Change): string {
  switch (c.kind) {
    case "create-table":           return renderCreateTable(c.table);
    // #313 — every FORWARD drop is `IF EXISTS`. A committed chain must apply to a
    // VIRGIN database, and the diff legitimately proposes dropping an object that
    // exists in the live DB but was never created by any migration in the chain (a
    // table another tool owns, say). Bare, that statement kills the replay with
    // `table "x" does not exist`. The DOWN renderer below is deliberately NOT
    // guarded: `rollbackTo` runs down.sql and the ledger delete in ONE transaction,
    // so a no-op down would still record the rollback as done.
    case "drop-table":             return `DROP TABLE IF EXISTS ${quoteQualified(c.table, c.schema)};`;
    case "rename-table":           return `ALTER TABLE ${quoteQualified(c.from, c.schema)} RENAME TO ${quote(c.to)};`;
    case "add-column": {
      const base = `ALTER TABLE ${quoteQualified(c.table, c.schema)} ADD COLUMN ${renderColumn(c.column)};`;
      if (!c.column.description) return base;
      return `${base}\n${columnCommentSql(c.table, c.schema, c.column.name, c.column.description)}`;
    }
    case "drop-column":            return `ALTER TABLE ${quoteQualified(c.table, c.schema)} DROP COLUMN ${quote(c.column)};`;
    case "rename-column":          return `ALTER TABLE ${quoteQualified(c.table, c.schema)} RENAME COLUMN ${quote(c.from)} TO ${quote(c.to)};`;
    case "change-column-type":     return renderAlterColumnType(c, c.from, c.to, c.fromDefault, c.toDefault);
    case "change-column-nullable":
      return c.to
        ? `ALTER TABLE ${quoteQualified(c.table, c.schema)} ALTER COLUMN ${quote(c.column)} DROP NOT NULL;`
        : `ALTER TABLE ${quoteQualified(c.table, c.schema)} ALTER COLUMN ${quote(c.column)} SET NOT NULL;`;
    case "change-column-default":
      return c.to !== undefined
        ? `ALTER TABLE ${quoteQualified(c.table, c.schema)} ALTER COLUMN ${quote(c.column)} SET DEFAULT ${renderDefault(c.to)};`
        : `ALTER TABLE ${quoteQualified(c.table, c.schema)} ALTER COLUMN ${quote(c.column)} DROP DEFAULT;`;
    case "add-index":              return renderCreateIndex(c.table, c.schema, c.index);
    // #285: an index OWNED by a UNIQUE / PRIMARY KEY / EXCLUDE constraint cannot be
    // dropped with DROP INDEX — Postgres refuses with "cannot drop index X because
    // constraint X on table Y requires it", failing the whole (transactional) apply and
    // so blocking every other pending change in the same migration. Drop the constraint
    // instead; it takes its backing index with it. `restore` is the ACTUAL introspected
    // descriptor (both diff producers populate it), which is where the marker lives.
    // Matters broadly, not marginally: Drizzle's `unique()` emits constraints, so every
    // schema adopted from Drizzle has constraint-backed unique indexes.
    // Both arms carry the #313 `IF EXISTS`: they are two renderings of the SAME
    // `drop-index` change, and guarding one would leave the change kind half-covered.
    // The constraint-backed arm ALSO guards the enclosing `ALTER TABLE` (not just
    // the constraint name) — see the `drop-fk`/`drop-check` comment below for why.
    case "drop-index":
      return c.restore?.constraint !== undefined
        ? `ALTER TABLE IF EXISTS ${quoteQualified(c.table, c.schema)} DROP CONSTRAINT IF EXISTS ${quote(c.index)};`
        : `DROP INDEX IF EXISTS ${quoteIndexQualified(c.index, c.schema)};`;
    case "add-fk":                 return renderAddFk(c.table, c.schema, c.fk);
    // #313 (constraint-level): `DROP CONSTRAINT IF EXISTS` alone only guards the
    // constraint NAME — Postgres still requires the TABLE to exist to parse an
    // `ALTER TABLE` at all, so a table another tool owns (never created by any
    // migration in this chain) still killed the replay with `relation "x" does
    // not exist`. Postgres supports `ALTER TABLE IF EXISTS` directly; using it
    // closes the gap the same way `DROP TABLE IF EXISTS` above already does.
    case "drop-fk":                return `ALTER TABLE IF EXISTS ${quoteQualified(c.table, c.schema)} DROP CONSTRAINT IF EXISTS ${quote(c.fk)};`;
    // `drop-check` IS produced by the diff — diff/index.ts:579 and :592 both push it,
    // and an evolved `field.enum @values` is a live producer. (A comment here used to
    // claim these arms were unreachable "declared, not yet produced" stubs; that was
    // false, and two tests already asserted the emitted statement.) `add-check` is the
    // paired ADD and rides the same passes.
    //
    // `drop-fk`/`drop-check` are guarded on Postgres ONLY, and that is not a dialect
    // split: SQLite emits no standalone statement for either kind — `renderUpNative`
    // throws, because SQLite constraints are create-time-only and inline, so the change
    // folds into a table recreate that rebuilds from the EXPECTED descriptor and never
    // references the dropped constraint. SQLite is already replay-safe by construction;
    // guarding Postgres makes the two dialects agree.
    case "add-check":              return `ALTER TABLE ${quoteQualified(c.table, c.schema)} ADD CONSTRAINT ${quote(c.check.name)} CHECK (${c.check.expression});`;
    // Same `ALTER TABLE IF EXISTS` gap as `drop-fk` above.
    case "drop-check":             return `ALTER TABLE IF EXISTS ${quoteQualified(c.table, c.schema)} DROP CONSTRAINT IF EXISTS ${quote(c.check)};`;
    case "create-view":            return renderCreateView(c.view, c.schema, /* orReplace */ false);
    case "drop-view":              return renderDropView(c);
    case "replace-view":           return renderCreateView(c.view, c.schema, /* orReplace */ true);
  }
}

function renderDown(c: Change): string {
  switch (c.kind) {
    case "create-table":           return `DROP TABLE ${quoteQualified(c.table.name, c.table.schema)};`;
    case "drop-table": {
      if (!c.restore) {
        return `-- WARNING: down migration cannot restore data\n-- TODO: restore table "${c.table}" structure manually`;
      }
      // renderCreateTable emits only columns + PK + checks. Indexes and FKs ride
      // as separate add-index/add-fk changes on the up side, so re-create them
      // explicitly here or the down silently loses them.
      const parts = [renderCreateTable(c.restore)];
      for (const index of c.restore.indexes) {
        parts.push(renderCreateIndex(c.restore.name, c.restore.schema, index));
      }
      for (const fk of c.restore.foreignKeys) {
        parts.push(renderAddFk(c.restore.name, c.restore.schema, fk));
      }
      parts.push("-- NOTE: table data is not restored by this down migration.");
      return parts.join("\n");
    }
    case "rename-table":           return `ALTER TABLE ${quoteQualified(c.to, c.schema)} RENAME TO ${quote(c.from)};`;
    case "add-column":             return `ALTER TABLE ${quoteQualified(c.table, c.schema)} DROP COLUMN ${quote(c.column.name)};`;
    case "drop-column":
      return c.restore
        ? `ALTER TABLE ${quoteQualified(c.table, c.schema)} ADD COLUMN ${renderColumn(c.restore)};\n-- NOTE: column data is not restored by this down migration.`
        : `-- WARNING: down migration cannot restore data\n-- TODO: re-add dropped column "${c.column}" manually with original type/nullable/default`;
    case "rename-column":          return `ALTER TABLE ${quoteQualified(c.table, c.schema)} RENAME COLUMN ${quote(c.to)} TO ${quote(c.from)};`;
    case "change-column-type":     return renderAlterColumnType(c, c.to, c.from, c.toDefault, c.fromDefault);
    case "change-column-nullable":
      return c.from
        ? `ALTER TABLE ${quoteQualified(c.table, c.schema)} ALTER COLUMN ${quote(c.column)} DROP NOT NULL;`
        : `ALTER TABLE ${quoteQualified(c.table, c.schema)} ALTER COLUMN ${quote(c.column)} SET NOT NULL;`;
    case "change-column-default":
      return c.from !== undefined
        ? `ALTER TABLE ${quoteQualified(c.table, c.schema)} ALTER COLUMN ${quote(c.column)} SET DEFAULT ${renderDefault(c.from)};`
        : `ALTER TABLE ${quoteQualified(c.table, c.schema)} ALTER COLUMN ${quote(c.column)} DROP DEFAULT;`;
    case "add-index":              return `DROP INDEX ${quoteIndexQualified(c.index.name, c.schema)};`;
    case "drop-index":
      // #285: the up dropped a CONSTRAINT, so the down must add one back — recreating a
      // bare unique index would leave the database in a different shape than it started
      // (uniqueness still enforced, but by an index rather than the constraint), and the
      // next down of THIS same migration would then fail to find the constraint.
      if (c.restore?.constraint === "unique") {
        return `ALTER TABLE ${quoteQualified(c.table, c.schema)} ADD CONSTRAINT ${quote(c.restore.name)} UNIQUE (${c.restore.columns.map(quote).join(", ")});`;
      }
      if (c.restore?.constraint === "exclude") {
        // An EXCLUDE constraint's operator list is not recoverable from an
        // IndexDescriptor; emit an honest warning rather than wrong SQL.
        return `-- WARNING: down migration cannot restore EXCLUDE constraint ${c.restore.name}`;
      }
      return c.restore
        ? renderCreateIndex(c.table, c.schema, c.restore)
        : `-- WARNING: down migration cannot restore the original index definition`;
    case "add-fk":                 return `ALTER TABLE ${quoteQualified(c.table, c.schema)} DROP CONSTRAINT ${quote(c.fk.name)};`;
    case "drop-fk":
      return c.restore
        ? renderAddFk(c.table, c.schema, c.restore)
        : `-- WARNING: down migration cannot restore the original FK definition`;
    // add-check / drop-check down arms: declared but not yet produced by the diff
    // (checks are create-time-only; see renderUp note).
    case "add-check":              return `ALTER TABLE ${quoteQualified(c.table, c.schema)} DROP CONSTRAINT ${quote(c.check.name)};`;
    case "drop-check":
      return c.restore
        ? `ALTER TABLE ${quoteQualified(c.table, c.schema)} ADD CONSTRAINT ${quote(c.restore.name)} CHECK (${c.restore.expression});`
        : `-- WARNING: down migration cannot restore the original CHECK definition`;
    case "create-view":            return `DROP VIEW ${quoteQualifiedView(c.view.name, c.schema)};`;
    // The deparsed body introspection captured IS a valid restore payload.
    case "drop-view":              return renderRestoreView(c.restore, c.view, c.schema);
    case "replace-view":           return renderRestoreView(c.restore, c.view.name, c.schema, c.view);
  }
}

function renderCreateTable(t: TableDescriptor): string {
  const colDefs = t.columns.map((c) => `  ${renderColumn(c)}`);
  if (t.primaryKey.length > 0) {
    colDefs.push(`  CONSTRAINT ${quote(t.name + "_pkey")} PRIMARY KEY (${t.primaryKey.map(quote).join(", ")})`);
  }
  for (const chk of t.checks ?? []) {
    colDefs.push(`  CONSTRAINT ${quote(chk.name)} CHECK (${chk.expression})`);
  }
  const create = `CREATE TABLE ${quoteQualified(t.name, t.schema)} (\n${colDefs.join(",\n")}\n);`;
  const comments = renderTableComments(t);
  return comments.length === 0 ? create : `${create}\n${comments.join("\n")}`;
}

function renderTableComments(t: TableDescriptor): string[] {
  const out: string[] = [];
  if (t.description) {
    out.push(`COMMENT ON TABLE ${quoteQualified(t.name, t.schema)} IS '${pgEscape(t.description)}';`);
  }
  for (const col of t.columns) {
    if (col.description) {
      out.push(columnCommentSql(t.name, t.schema, col.name, col.description));
    }
  }
  return out;
}

function columnCommentSql(
  table: string,
  schema: string | undefined,
  column: string,
  description: string,
): string {
  return `COMMENT ON COLUMN ${quoteQualified(table, schema)}.${quote(column)} IS '${pgEscape(description)}';`;
}

function pgEscape(s: string): string {
  return s.replace(/'/g, "''");
}

function renderColumn(c: ColumnDescriptor): string {
  let s = `${quote(c.name)} ${pgType(c.sqlType)}`;
  if (c.identity === "increment") s += " GENERATED BY DEFAULT AS IDENTITY";
  if (c.identity === "uuid")      s += " DEFAULT gen_random_uuid()";
  s += c.nullable ? "" : " NOT NULL";
  if (c.default !== undefined && c.identity !== "uuid") {
    // For uuid identity we already set DEFAULT gen_random_uuid(); don't duplicate.
    s += ` DEFAULT ${renderDefault(c.default)}`;
  }
  return s;
}

export function pgType(t: SqlType): string {
  switch (t.kind) {
    case "text":      return t.maxLength !== undefined ? `VARCHAR(${t.maxLength})` : "TEXT";
    case "integer":   return t.bits === 64 ? "BIGINT" : "INTEGER";
    case "real":      return "DOUBLE PRECISION";
    case "real4":     return "REAL";
    case "numeric":   {
      if (t.precision !== undefined && t.scale !== undefined) return `NUMERIC(${t.precision},${t.scale})`;
      if (t.precision !== undefined) return `NUMERIC(${t.precision})`;
      return "NUMERIC";
    }
    case "boolean":   return "BOOLEAN";
    case "timestamp": return t.withTimezone ? "TIMESTAMPTZ" : "TIMESTAMP";
    case "date":      return "DATE";
    case "time":      return "TIME";
    case "json":      return "JSONB";
    case "blob":      return "BYTEA";
    case "uuid":      return "UUID";
    case "inet":      return "INET";
    case "array":     return `${pgType(t.element)}[]`;
  }
}

function renderDefault(d: ColumnDefault): string {
  if (d.kind === "expr") return d.value;
  // Literal: quote string-form values.
  return `'${pgEscape(d.value)}'`;
}

function renderCreateIndex(table: string, schema: string | undefined, ix: IndexDescriptor): string {
  const u = ix.unique ? "UNIQUE " : "";
  // Key list: a raw expression (functional/expression index) takes precedence over
  // the per-column list. The expression is emitted verbatim (already valid SQL).
  const keys = ix.expr
    ? ix.expr
    : ix.columns
        .map((c, i) => (ix.orders?.[i] === "desc" ? `${quote(c)} DESC` : quote(c)))
        .join(", ");
  // Access method: btree is the default and intentionally not rendered (matches PG's
  // canonical def); anything else (gin/gist/hash/…) is emitted as `USING <method>`.
  const using = ix.using && ix.using !== "btree" ? ` USING ${ix.using}` : "";
  // Partial-index predicate, when present.
  const where = ix.where ? ` WHERE (${ix.where})` : "";
  // Index name itself is unqualified in CREATE INDEX (Postgres places the index
  // in the same schema as the table being indexed). Only the ON clause needs qualification.
  return `CREATE ${u}INDEX ${quote(ix.name)} ON ${quoteQualified(table, schema)}${using} (${keys})${where};`;
}

function renderAddFk(table: string, schema: string | undefined, fk: FkDescriptor): string {
  let s = `ALTER TABLE ${quoteQualified(table, schema)} ADD CONSTRAINT ${quote(fk.name)} `;
  s += `FOREIGN KEY (${fk.columns.map(quote).join(", ")}) `;
  // v1 limitation: FkDescriptor does not carry the ref-table's schema today.
  // Assume the referenced table lives in the same schema as the FK-owner.
  // For cross-schema FKs, add `refSchema?` to FkDescriptor in a follow-up.
  s += `REFERENCES ${quoteQualified(fk.refTable, schema)} (${fk.refColumns.map(quote).join(", ")})`;
  if (fk.onDelete) s += ` ON DELETE ${fkActionSql(fk.onDelete)}`;
  if (fk.onUpdate) s += ` ON UPDATE ${fkActionSql(fk.onUpdate)}`;
  return s + ";";
}

function fkActionSql(a: FkAction): string {
  switch (a) {
    case "cascade":   return "CASCADE";
    case "set-null":  return "SET NULL";
    case "restrict":  return "RESTRICT";
    case "no-action": return "NO ACTION";
  }
}

type ColumnTypeChange = Extract<Change, { kind: "change-column-type" }>;

/**
 * `ALTER COLUMN … TYPE`, with the `USING` clause the conversion needs.
 *
 * Postgres changes a column's type in place only along an ASSIGNMENT cast. Many cross-kind
 * changes have none (jsonb → text[], text[] → jsonb, text → text[], boolean → integer), and the
 * bare ALTER is refused ("cannot be cast automatically"), so a migration `meta migrate` wrote
 * could not be applied by anything. {@link conversion} names each one.
 *
 * A DEFAULT is converted by an assignment cast too, and never through USING, so a default in
 * the way of a converting change is dropped first and set again after. The change carries both
 * defaults for exactly this (types.ts).
 */
function renderAlterColumnType(
  c: ColumnTypeChange, from: SqlType, to: SqlType,
  fromDefault: ColumnDefault | undefined, toDefault: ColumnDefault | undefined,
): string {
  const col = quote(c.column);
  const alterColumn = `ALTER TABLE ${quoteQualified(c.table, c.schema)} ALTER COLUMN ${col}`;
  const { using } = conversion(col, from, to, c.schema);
  const alterType = `${alterColumn} TYPE ${pgType(to)}${using};`;
  if (using === "" && columnDefaultsEqual(fromDefault, toDefault)) return alterType;
  return [
    ...(fromDefault !== undefined ? [`${alterColumn} DROP DEFAULT;`] : []),
    alterType,
    ...(toDefault !== undefined ? [`${alterColumn} SET DEFAULT ${renderDefault(toDefault)};`] : []),
  ].join("\n");
}

/**
 * The `USING` clause for a change (`""` when the assignment cast a bare ALTER uses is right),
 * and the helper function it calls, if any.
 *
 * - jsonb → T[]: a subquery is not allowed in USING, so the rows are unpacked by a helper. It
 *   keeps element order, maps a JSON `null` to NULL, and RAISES on a scalar or an object.
 * - T[] → jsonb: `to_jsonb`, since no cast from an array to jsonb exists.
 * - scalar → T[]: the value becomes the array's one element. An explicit `::T[]` would PARSE the
 *   text as an array literal instead ('{a,b}' → two elements, 'hello' → an error).
 * - T[] → scalar: the one element a row holds, through a helper that RAISES on a row holding
 *   more. Neither alternative is safe: `c[1]` silently keeps the first element, and the bare
 *   assignment cast to text stores the array literal '{a,b}' as the value.
 * - anything else cross-kind: an explicit cast, which is the assignment cast where one exists
 *   and the only cast where it does not.
 */
function conversion(
  col: string, from: SqlType, to: SqlType, schema: string | undefined,
): { using: string; helper?: ConversionHelper } {
  if (from.kind === "json" && to.kind === "array") {
    const helper = JSONB_ARRAY_TO_TEXT_ARRAY;
    return { using: ` USING ${helperCall(helper, col, schema)}${castIfNeeded(TEXT_ARRAY, to)}`, helper };
  }
  if (from.kind === "array" && to.kind === "json") return { using: ` USING to_jsonb(${col})` };
  if (from.kind !== "array" && to.kind === "array") {
    return {
      using: ` USING (CASE WHEN ${col} IS NULL THEN NULL ELSE ARRAY[${col}] END)`
        + castIfNeeded({ kind: "array", element: from }, to),
    };
  }
  if (from.kind === "array" && to.kind !== "array") {
    const helper = ARRAY_TO_SCALAR;
    return { using: ` USING ${helperCall(helper, col, schema)}${castIfNeeded(from.element, to)}`, helper };
  }
  const cast = castIfNeeded(from, to);
  return { using: cast === "" ? "" : ` USING ${col}${cast}` };
}

/**
 * `::T` when a value of `from` has no assignment cast to `to`. A change TO text never needs one,
 * since every type has an assignment cast to text, and must not get one: an explicit cast to
 * VARCHAR(n) TRUNCATES an over-length value that the assignment cast refuses.
 */
function castIfNeeded(from: SqlType, to: SqlType): string {
  return needsExplicitCast(from, to) ? `::${pgType(to)}` : "";
}

function needsExplicitCast(from: SqlType, to: SqlType): boolean {
  if (to.kind === "text") return false;
  if (from.kind !== to.kind) return true;
  if (from.kind === "array" && to.kind === "array") return needsExplicitCast(from.element, to.element);
  return false;
}

const TEXT_ARRAY: SqlType = { kind: "array", element: { kind: "text" } };

/**
 * A function a conversion calls. It is an ordinary function created in the table's schema
 * before the ALTERs and dropped at the end of the same file, not a `pg_temp` one: a database
 * that revokes TEMP from PUBLIC (a common hardening step) refuses pg_temp, while CREATE on the
 * schema is a privilege the migration already needs for its tables.
 */
interface ConversionHelper {
  readonly name: string;
  /** Parameter list, return type and language, as written after the function name. */
  readonly signature: string;
  /** Argument types, as `DROP FUNCTION` needs them. */
  readonly argTypes: string;
  readonly body: string;
}

const JSONB_ARRAY_TO_TEXT_ARRAY: ConversionHelper = {
  name: "metaobjects_jsonb_array_to_text_array",
  signature: "(j jsonb) RETURNS text[] LANGUAGE sql IMMUTABLE",
  argTypes: "jsonb",
  body: "SELECT CASE WHEN j IS NULL OR jsonb_typeof(j) = 'null' THEN NULL "
    + "ELSE ARRAY(SELECT e FROM jsonb_array_elements_text(j) WITH ORDINALITY AS x(e, i) ORDER BY i) END",
};

const ARRAY_TO_SCALAR: ConversionHelper = {
  name: "metaobjects_array_to_scalar",
  signature: "(a anyarray) RETURNS anyelement LANGUAGE plpgsql IMMUTABLE",
  argTypes: "anyarray",
  body: "BEGIN IF cardinality(a) > 1 THEN RAISE EXCEPTION "
    + "'metaobjects: cannot narrow an array of % elements to one value', cardinality(a); "
    + "END IF; RETURN a[1]; END",
};

function helperCall(helper: ConversionHelper, col: string, schema: string | undefined): string {
  return `${quoteQualified(helper.name, schema)}(${col})`;
}

/** The helpers one direction of the migration calls: each once per schema, in a stable order. */
function conversionHelpers(
  changes: readonly Change[], direction: "up" | "down",
): { helper: ConversionHelper; schema: string | undefined }[] {
  const needed = new Map<string, { helper: ConversionHelper; schema: string | undefined }>();
  for (const c of changes) {
    if (c.kind !== "change-column-type") continue;
    const [from, to] = direction === "up" ? [c.from, c.to] : [c.to, c.from];
    const { helper } = conversion("", from, to, c.schema);
    if (helper === undefined) continue;
    const schema = c.schema === DEFAULT_DB_SCHEMA_POSTGRES ? undefined : c.schema;
    needed.set(`${schema ?? ""}.${helper.name}`, { helper, schema });
  }
  return [...needed.keys()].sort().flatMap((k) => needed.get(k) ?? []);
}

function createHelper({ helper, schema }: { helper: ConversionHelper; schema: string | undefined }): string {
  return `CREATE OR REPLACE FUNCTION ${quoteQualified(helper.name, schema)}${helper.signature} AS $$ ${helper.body} $$;`;
}

function dropHelper({ helper, schema }: { helper: ConversionHelper; schema: string | undefined }): string {
  return `DROP FUNCTION IF EXISTS ${quoteQualified(helper.name, schema)}(${helper.argTypes});`;
}

function quote(ident: string): string {
  // Conservative double-quoting; reject embedded quotes (defense).
  if (ident.includes('"')) throw new Error(`unsafe identifier: ${ident}`);
  return `"${ident}"`;
}

/**
 * Quote a table identifier, prefixing the schema when non-default. The Postgres
 * default schema is `public`; undefined and "public" both mean "no prefix needed."
 */
function quoteQualified(table: string, schema: string | undefined): string {
  if (!schema || schema === DEFAULT_DB_SCHEMA_POSTGRES) return quote(table);
  return quote(schema) + "." + quote(table);
}

/**
 * Quote an index identifier for DROP INDEX, prefixing the schema when non-default.
 * In Postgres, indexes live in the same schema as their owning table; DROP INDEX
 * accepts the qualified form `"schema"."index"`.
 */
function quoteIndexQualified(index: string, schema: string | undefined): string {
  if (!schema || schema === DEFAULT_DB_SCHEMA_POSTGRES) return quote(index);
  return quote(schema) + "." + quote(index);
}

/** Same shape as quoteQualified, just for view identifiers (kept separate for readability). */
function quoteQualifiedView(view: string, schema: string | undefined): string {
  return quoteQualified(view, schema);
}

/**
 * Emit the view AND stamp it with the fingerprint of the body we just wrote.
 *
 * The stamp is not decoration — it is the ONLY way a later migrate can tell whether
 * this view is up to date. Postgres does not store view SQL (it deparses it from the
 * parse tree), so the text can never be read back and compared; the fingerprint in the
 * view's COMMENT can. Drop the stamp and every migrate re-proposes every view forever.
 *
 * `CREATE OR REPLACE VIEW` does NOT clear an existing comment, so re-stamping on every
 * replace is both required (the body changed → the hash changed) and sufficient.
 */
function renderCreateView(v: ViewDescriptor, schema: string | undefined, orReplace: boolean): string {
  if (v.sql === undefined || v.sql.trim().length === 0) {
    throw new Error(`view "${v.name}" has no sql body — buildExpectedSchema must populate it before emit`);
  }
  const prefix = orReplace ? "CREATE OR REPLACE VIEW" : "CREATE VIEW";
  const qualified = quoteQualifiedView(v.name, schema);
  const create = `${prefix} ${qualified} AS\n${v.sql};`;
  const fingerprint = v.fingerprint ?? viewFingerprint(v.sql);
  return `${create}\n${renderViewComment(qualified, renderFingerprintMarker(fingerprint))}`;
}

function renderViewComment(qualifiedView: string, comment: string | null): string {
  if (comment === null) return `COMMENT ON VIEW ${qualifiedView} IS NULL;`;
  return `COMMENT ON VIEW ${qualifiedView} IS '${comment.replace(/'/g, "''")}';`;
}

/**
 * DROP VIEW, plus — when the drop would cascade into relations we do not manage — a
 * banner naming every one of them.
 *
 * The banner lives in the emitted SQL rather than only in CLI output on purpose: the
 * migration file is committed and code-reviewed, and "this statement destroys three
 * objects belonging to another application" is exactly the thing a reviewer must see.
 *
 * CASCADE is emitted ONLY when explicitly allowed. Otherwise a plain DROP VIEW is
 * emitted even if dependents are known — so if a dependent appeared between introspect
 * and apply, Postgres itself refuses the drop rather than silently destroying it.
 */
function renderDropView(c: Extract<Change, { kind: "drop-view" }>): string {
  const qualified = quoteQualifiedView(c.view, c.schema);
  const dependents = c.dependents ?? [];
  // #313 `IF EXISTS` on both forms — this is the FORWARD renderer. `renderRestoreView`
  // below stays bare: it is reached only from `renderDown`.
  if (dependents.length === 0) return `DROP VIEW IF EXISTS ${qualified};`;

  const listed = dependents
    .map((d) => `--   ${d.schema}.${d.name} (${d.relkind === "m" ? "materialized view" : "view"})`)
    .join("\n");
  const rule = "-- " + "=".repeat(74);
  return [
    rule,
    "-- WARNING: CASCADE DROP. The following dependent objects are DESTROYED by this",
    "-- statement. MetaObjects does not manage them and the down migration does NOT",
    "-- restore them:",
    listed,
    rule,
    `DROP VIEW IF EXISTS ${qualified} CASCADE;`,
  ].join("\n");
}

/**
 * Restore a view the up migration dropped or replaced.
 *
 * The payload is Postgres's own deparsed body (`pg_get_viewdef`) — useless for
 * COMPARISON, but perfectly valid SQL that reproduces the view. So the thing that made
 * the bug (the deparser) is what makes down migrations restorable. The prior stamp is
 * replayed verbatim: the restored parse tree IS the pre-migration view, so its old
 * fingerprint is still the truthful one.
 */
function renderRestoreView(
  restore: ViewDescriptor | undefined,
  name: string,
  schema: string | undefined,
  /** The view the UP migration left in place — i.e. what the down is replacing. */
  current?: ViewDescriptor,
): string {
  if (restore?.sql === undefined || restore.sql.trim().length === 0) {
    return `-- WARNING: down migration cannot restore the original view definition`;
  }
  const qualified = quoteQualifiedView(name, schema);
  const body = restore.sql.trim().replace(/;\s*$/, "");

  // Postgres's OR-REPLACE prefix rule applies to the down migration too, and usually
  // REFUSES: undoing an appended field means REMOVING a view column, and
  // `CREATE OR REPLACE VIEW` cannot drop columns ("cannot drop columns from view").
  // So ask the same question the forward path asks, with the arguments swapped — and
  // fall back to DROP + CREATE when the answer is no.
  //
  // The fallback DROP is deliberately NOT `CASCADE`: if a dependent has since been built
  // on the newer shape, Postgres refuses the down migration rather than silently
  // destroying that dependent. Loud beats convenient.
  const stamp = restore.fingerprint !== undefined
    ? renderViewComment(qualified, renderFingerprintMarker(restore.fingerprint))
    // The view being restored carried NO fingerprint (hand-written, or pre-stamping).
    // Clear ours — otherwise the restored view would advertise a stamp for a body it
    // does not have, and the next migrate would believe the stamp and skip it.
    : renderViewComment(qualified, null);

  if (current !== undefined && !viewReplaceIsLegal(restore.columns, current.columns)) {
    return `DROP VIEW ${qualified};\nCREATE VIEW ${qualified} AS\n${body};\n${stamp}`;
  }
  return `CREATE OR REPLACE VIEW ${qualified} AS\n${body};\n${stamp}`;
}