sql-renderer.ts

import type { JsonValue } from '@prisma-next/contract/types';
import type { CodecLookup } from '@prisma-next/framework-components/codec';
import { runtimeError } from '@prisma-next/framework-components/runtime';
import {
  type AggregateExpr,
  type AnyExpression,
  type AnyFromSource,
  type AnyParamRef,
  type AnyQueryAst,
  type BinaryExpr,
  type ColumnRef,
  collectOrderedParamRefs,
  type DeleteAst,
  type InsertAst,
  type InsertValue,
  type JoinAst,
  type JoinOnExpr,
  type JsonArrayAggExpr,
  type JsonObjectExpr,
  type ListExpression,
  LiteralExpr,
  type LoweredParam,
  type NullCheckExpr,
  type OperationExpr,
  type OrderByItem,
  type ProjectionItem,
  type RawExpr,
  type RawSqlExpr,
  type SelectAst,
  type SubqueryExpr,
  type TableSource,
  type UpdateAst,
  type WindowFuncExpr,
} from '@prisma-next/sql-relational-core/ast';
import { escapeLiteral, quoteIdentifier } from '@prisma-next/target-postgres/sql-utils';
import { ifDefined } from '@prisma-next/utils/defined';
import type { PostgresContract } from './types';

/**
 * Postgres native types whose unknown-OID parameter inference is reliable in arbitrary expression positions. Parameters bound to a codec whose `meta.db.sql.postgres.nativeType` falls in this set are emitted as plain `$N`; everything else (including `json`, `jsonb`, extension types like `vector`, and unknown user types) is emitted as `$N::<nativeType>` so the planner picks an unambiguous overload.
 *
 * `json` / `jsonb` are intentionally excluded despite being Postgres builtins: their operator overloads make context inference unreliable in expression positions (e.g. `$1 -> 'key'` is ambiguous between the two).
 *
 * Spellings match the on-disk `meta.db.sql.postgres.nativeType` values in `@prisma-next/target-postgres`'s codec definitions, not the `udt_name` abbreviations that ADR 205 used as illustrative shorthand. The lookup-based cast policy compares against these strings directly.
 */
const POSTGRES_INFERRABLE_NATIVE_TYPES: ReadonlySet<string> = new Set([
  // Numeric
  'integer',
  'smallint',
  'bigint',
  'real',
  'double precision',
  'numeric',
  // Boolean
  'boolean',
  // Strings
  'text',
  'character',
  'character varying',
  // Temporal
  'timestamp',
  'timestamp without time zone',
  'timestamp with time zone',
  'time',
  'timetz',
  'interval',
  // Bit strings
  'bit',
  'bit varying',
]);

function renderTypedParam(
  index: number,
  codecId: string | undefined,
  codecLookup: CodecLookup,
  many?: boolean,
  typeParams?: JsonValue,
): string {
  if (codecId === undefined) {
    return `$${index}`;
  }
  const meta = codecLookup.metaFor(codecId, typeParams);
  const isRegistered =
    codecLookup.get(codecId) !== undefined ||
    meta !== undefined ||
    codecLookup.targetTypesFor(codecId) !== undefined;
  if (!isRegistered) {
    throw new Error(
      `Postgres lowering: ParamRef carries codecId "${codecId}" but the ` +
        'assembled codec lookup has no entry for it. This usually indicates ' +
        'a missing extension pack in the runtime stack — register the pack ' +
        'that contributes this codec (e.g. `extensionPacks: [pgvectorRuntime]`), ' +
        'or use the codec directly from `@prisma-next/target-postgres/codecs` ' +
        "if it's a builtin.",
    );
  }
  // `typeParams` above already resolved a parameterized codec's per-instance
  // meta (e.g. a native enum's type name) ahead of its static fallback.
  //
  // The framework `CodecLookup.metaFor` returns the family-agnostic
  // `CodecMeta`, whose `db` is `Record<string, unknown>`. The SQL family
  // populates a narrower shape with `db.sql.<dialect>.nativeType: string`, so
  // navigate that path defensively and string-check the leaf.
  const dbRecord = meta?.db;
  const sqlBlock = isRecord(dbRecord) ? dbRecord['sql'] : undefined;
  const dialectBlock = isRecord(sqlBlock) ? sqlBlock['postgres'] : undefined;
  const nativeType = isRecord(dialectBlock) ? dialectBlock['nativeType'] : undefined;
  if (typeof nativeType === 'string') {
    const arraySuffix = many ? '[]' : '';
    if (!POSTGRES_INFERRABLE_NATIVE_TYPES.has(nativeType)) {
      return `$${index}::${nativeType}${arraySuffix}`;
    }
    if (many) {
      return `$${index}::${nativeType}${arraySuffix}`;
    }
  }
  return `$${index}`;
}

function isRecord(value: unknown): value is Record<string, unknown> {
  return typeof value === 'object' && value !== null;
}

/**
 * Per-render carrier threaded through every helper. Bundles the param-index map (for `$N` numbering) and the assembled-stack `codecLookup` (for cast policy at the `renderTypedParam` chokepoint). Carrying both on a single value keeps helper signatures stable.
 */
interface ParamIndexMap {
  readonly indexMap: Map<AnyParamRef, number>;
  readonly codecLookup: CodecLookup;
}

/**
 * Render a SQL query AST to a Postgres-flavored `{ sql, params }` payload.
 *
 * Shared between the runtime (`PostgresAdapterImpl.lower`) and control (`PostgresControlAdapter.lower`) entrypoints so emit-time and run-time paths produce byte-identical output for the same AST.
 */
export function renderLoweredSql(
  ast: AnyQueryAst,
  contract: PostgresContract,
  codecLookup: CodecLookup,
): { readonly sql: string; readonly params: readonly LoweredParam[] } {
  const orderedRefs = collectOrderedParamRefs(ast);
  const indexMap = new Map<AnyParamRef, number>();
  const params: LoweredParam[] = orderedRefs.map((ref, i) => {
    indexMap.set(ref, i + 1);
    return ref.kind === 'prepared-param-ref'
      ? { kind: 'bind', name: ref.name }
      : { kind: 'literal', value: ref.value };
  });
  const pim: ParamIndexMap = { indexMap, codecLookup };

  const node = ast;
  let sql: string;
  switch (node.kind) {
    case 'select':
      sql = renderSelect(node, contract, pim);
      break;
    case 'insert':
      sql = renderInsert(node, contract, pim);
      break;
    case 'update':
      sql = renderUpdate(node, contract, pim);
      break;
    case 'delete':
      sql = renderDelete(node, contract, pim);
      break;
    case 'raw-sql':
      sql = renderRawSql(node, contract, pim);
      break;
    // v8 ignore next 4
    default:
      throw new Error(
        `Unsupported AST node kind: ${(node satisfies never as { kind: string }).kind}`,
      );
  }

  return Object.freeze({ sql, params: Object.freeze(params) });
}

function renderLimitOffset(
  keyword: 'LIMIT' | 'OFFSET',
  value: SelectAst['limit'] | SelectAst['offset'],
  contract: PostgresContract,
  pim: ParamIndexMap,
): string {
  if (value === undefined) return '';
  if (typeof value === 'number') return `${keyword} ${value}`;
  return `${keyword} ${renderExpr(value, contract, pim)}`;
}

function renderSelect(ast: SelectAst, contract: PostgresContract, pim: ParamIndexMap): string {
  const sourcesByRef = collectTableSources(ast);
  const selectClause = `SELECT ${renderDistinctPrefix(ast.distinct, ast.distinctOn, sourcesByRef, contract, pim)}${renderProjection(
    ast.projection,
    contract,
    pim,
  )}`;
  const fromClause = ast.from !== undefined ? `FROM ${renderSource(ast.from, contract, pim)}` : '';

  const joinsClause = ast.joins?.length
    ? ast.joins.map((join) => renderJoin(join, contract, pim)).join(' ')
    : '';

  const whereClause = ast.where ? `WHERE ${renderWhere(ast.where, contract, pim)}` : '';
  const groupByClause = ast.groupBy?.length
    ? `GROUP BY ${ast.groupBy.map((expr) => renderExpr(expr, contract, pim)).join(', ')}`
    : '';
  const havingClause = ast.having ? `HAVING ${renderWhere(ast.having, contract, pim)}` : '';
  const orderClause = ast.orderBy?.length
    ? `ORDER BY ${ast.orderBy
        .map((order) => {
          const expr = renderOrderByExpr(order.expr, sourcesByRef, contract, pim);
          return `${expr} ${order.dir.toUpperCase()}`;
        })
        .join(', ')}`
    : '';
  const limitClause = renderLimitOffset('LIMIT', ast.limit, contract, pim);
  const offsetClause = renderLimitOffset('OFFSET', ast.offset, contract, pim);

  const clauses = [
    selectClause,
    fromClause,
    joinsClause,
    whereClause,
    groupByClause,
    havingClause,
    orderClause,
    limitClause,
    offsetClause,
  ]
    .filter((part) => part.length > 0)
    .join(' ');
  return clauses.trim();
}

/**
 * Storage coordinate a query-level table reference (alias or bare name) resolves to. The ORDER BY enum hook uses this to look a column-ref's storage column up and read its value-set.
 */
interface TableSourceCoordinate {
  readonly name: string;
  readonly namespaceId: string | undefined;
}

/**
 * Map a SELECT's table references (the FROM source and any JOIN sources) to their storage coordinate, keyed by the name a `ColumnRef.table` would carry (the alias when present, otherwise the table name). Derived-table sources are skipped — their columns are projected through a sub-select, not a base storage column, so the enum hook does not apply.
 */
function collectTableSources(ast: SelectAst): ReadonlyMap<string, TableSourceCoordinate> {
  const sources = new Map<string, TableSourceCoordinate>();
  const add = (source: AnyFromSource): void => {
    if (source.kind !== 'table-source') {
      return;
    }
    const ref = source.alias ?? source.name;
    sources.set(ref, { name: source.name, namespaceId: source.namespaceId });
  };
  if (ast.from !== undefined) add(ast.from);
  for (const join of ast.joins ?? []) {
    add(join.source);
  }
  return sources;
}

/**
 * Ordered, codec-encoded values of the value-set a storage column restricts to, or `undefined` when the referenced column carries no value-set (the common, non-enum case). Resolves the column's storage coordinate from the SELECT's table sources, then the column's `valueSet` ref to the value-set's `values`.
 */
function allStrings(values: readonly JsonValue[]): values is readonly string[] {
  return values.every((value) => typeof value === 'string');
}

function resolveEnumOrderValues(
  ref: ColumnRef,
  sourcesByRef: ReadonlyMap<string, TableSourceCoordinate>,
  contract: PostgresContract,
): readonly JsonValue[] | undefined {
  const source = sourcesByRef.get(ref.table);
  if (source === undefined || source.namespaceId === undefined) {
    return undefined;
  }
  const sourceNs = contract.storage.namespaces[source.namespaceId];
  const column =
    sourceNs !== undefined ? sourceNs.entries.table?.[source.name]?.columns[ref.column] : undefined;
  const valueSet = column?.valueSet;
  if (valueSet === undefined) {
    return undefined;
  }
  const valueSetNs = contract.storage.namespaces[valueSet.namespaceId];
  return valueSetNs !== undefined
    ? valueSetNs.entries.valueSet?.[valueSet.entityName]?.values
    : undefined;
}

/**
 * Ordered values for an unqualified ORDER BY column (an `identifier-ref`, the shape the sql-builder emits for `.orderBy('col')`). Scans every FROM/JOIN source for a column of that name. Resolves only when exactly one source has a column of that name and it carries a value-set; if more than one source has such a column the bare identifier is ambiguous (regardless of which are enum-backed), so it falls through to the plain column rendering.
 */
function resolveEnumOrderValuesForIdentifier(
  name: string,
  sourcesByRef: ReadonlyMap<string, TableSourceCoordinate>,
  contract: PostgresContract,
): readonly JsonValue[] | undefined {
  let matchedColumns = 0;
  let resolved: readonly JsonValue[] | undefined;
  for (const source of sourcesByRef.values()) {
    if (source.namespaceId === undefined) {
      continue;
    }
    const identNs = contract.storage.namespaces[source.namespaceId];
    const column =
      identNs !== undefined ? identNs.entries.table?.[source.name]?.columns[name] : undefined;
    if (column === undefined) {
      continue;
    }
    matchedColumns += 1;
    if (matchedColumns > 1) {
      return undefined;
    }
    const valueSet = column.valueSet;
    if (valueSet === undefined) {
      return undefined;
    }
    const valueSetNs = contract.storage.namespaces[valueSet.namespaceId];
    resolved =
      valueSetNs !== undefined
        ? valueSetNs.entries.valueSet?.[valueSet.entityName]?.values
        : undefined;
  }
  return resolved;
}

/**
 * Render an ORDER BY expression. A column reference onto an enum-restricted column sorts by declaration order via `array_position(ARRAY[…]::text[], <col>)` over the value-set's ordered values (NULLs return `NULL` from `array_position`, sorting per the clause's default NULL handling). Both qualified `column-ref`s and the unqualified `identifier-ref`s the sql-builder emits for `.orderBy('col')` are intercepted. Every other expression renders unchanged.
 */
function renderOrderByExpr(
  expr: AnyExpression,
  sourcesByRef: ReadonlyMap<string, TableSourceCoordinate>,
  contract: PostgresContract,
  pim: ParamIndexMap,
): string {
  // Only TEXT enums lower to a value-set whose ORDER BY rewrite ships in this
  // slice. Numeric-enum ORDER BY is future work; until then a non-string
  // value-set falls through to plain column rendering rather than emitting a
  // wrong numeric-as-text ARRAY.
  if (expr.kind === 'column-ref') {
    const orderValues = resolveEnumOrderValues(expr, sourcesByRef, contract);
    if (orderValues !== undefined && allStrings(orderValues)) {
      const array = orderValues.map((value) => `'${escapeLiteral(value)}'`).join(', ');
      return `array_position(ARRAY[${array}]::text[], ${renderColumn(expr)})`;
    }
  }
  if (expr.kind === 'identifier-ref') {
    const orderValues = resolveEnumOrderValuesForIdentifier(expr.name, sourcesByRef, contract);
    if (orderValues !== undefined && allStrings(orderValues)) {
      const array = orderValues.map((value) => `'${escapeLiteral(value)}'`).join(', ');
      return `array_position(ARRAY[${array}]::text[], ${quoteIdentifier(expr.name)})`;
    }
  }
  return renderExpr(expr, contract, pim);
}

function renderProjection(
  projection: ReadonlyArray<ProjectionItem>,
  contract: PostgresContract,
  pim: ParamIndexMap,
): string {
  return projection
    .map((item) => {
      const alias = quoteIdentifier(item.alias);
      if (item.expr.kind === 'literal') {
        return `${renderLiteral(item.expr)} AS ${alias}`;
      }
      return `${renderExpr(item.expr, contract, pim)} AS ${alias}`;
    })
    .join(', ');
}

function renderReturning(
  items: ReadonlyArray<ProjectionItem>,
  contract: PostgresContract,
  pim: ParamIndexMap,
): string {
  return items
    .map((item) => {
      if (item.expr.kind === 'column-ref') {
        const rendered = renderColumn(item.expr);
        return item.expr.column === item.alias
          ? rendered
          : `${rendered} AS ${quoteIdentifier(item.alias)}`;
      }
      if (item.expr.kind === 'literal') {
        return `${renderLiteral(item.expr)} AS ${quoteIdentifier(item.alias)}`;
      }
      return `${renderExpr(item.expr, contract, pim)} AS ${quoteIdentifier(item.alias)}`;
    })
    .join(', ');
}

function renderDistinctPrefix(
  distinct: true | undefined,
  distinctOn: ReadonlyArray<AnyExpression> | undefined,
  sourcesByRef: ReadonlyMap<string, TableSourceCoordinate>,
  contract: PostgresContract,
  pim: ParamIndexMap,
): string {
  if (distinctOn && distinctOn.length > 0) {
    const rendered = distinctOn
      .map((expr) => renderOrderByExpr(expr, sourcesByRef, contract, pim))
      .join(', ');
    return `DISTINCT ON (${rendered}) `;
  }
  if (distinct) {
    return 'DISTINCT ';
  }
  return '';
}

function hasExplicitSchema(
  table: Pick<TableSource, 'name' | 'namespaceId'>,
): table is Pick<TableSource, 'name' | 'namespaceId'> & { readonly schema: string } {
  return 'schema' in table && typeof table.schema === 'string';
}

function qualifyTableFromNamespaceCoordinate(
  table: Pick<TableSource, 'name' | 'namespaceId'>,
  contract: PostgresContract,
): string {
  if (hasExplicitSchema(table)) {
    return `${quoteIdentifier(table.schema)}.${quoteIdentifier(table.name)}`;
  }
  if (table.namespaceId === undefined) {
    return quoteIdentifier(table.name);
  }
  const namespace = contract.storage.namespaces[table.namespaceId];
  if (namespace === undefined) {
    throw new Error(
      `Table "${table.name}" references namespace "${table.namespaceId}" which is not present as a Postgres schema on the contract`,
    );
  }
  const qualifyTable = namespace.qualifyTable;
  if (qualifyTable === undefined) {
    throw new Error(
      `Table "${table.name}" references namespace "${table.namespaceId}" which is not materialised as a Postgres schema on the contract`,
    );
  }
  return qualifyTable.call(namespace, table.name);
}

function renderTableSource(source: TableSource, contract: PostgresContract): string {
  const qualified = qualifyTableFromNamespaceCoordinate(source, contract);
  if (!source.alias) {
    return qualified;
  }
  return `${qualified} AS ${quoteIdentifier(source.alias)}`;
}

function renderSource(
  source: AnyFromSource,
  contract: PostgresContract,
  pim: ParamIndexMap,
): string {
  const node = source;
  switch (node.kind) {
    case 'table-source':
      return renderTableSource(node, contract);
    case 'derived-table-source':
      return `(${renderSelect(node.query, contract, pim)}) AS ${quoteIdentifier(node.alias)}`;
    case 'function-source': {
      const args = node.args.map((arg) => renderExpr(arg, contract, pim)).join(', ');
      const call = `${node.fn}(${args})`;
      return node.alias !== undefined ? `${call} AS ${quoteIdentifier(node.alias)}` : call;
    }
    // v8 ignore next 4
    default:
      throw new Error(
        `Unsupported source node kind: ${(node satisfies never as { kind: string }).kind}`,
      );
  }
}

function assertScalarSubquery(query: SelectAst): void {
  if (query.projection.length !== 1) {
    throw new Error('Subquery expressions must project exactly one column');
  }
}

function renderSubqueryExpr(
  expr: SubqueryExpr,
  contract: PostgresContract,
  pim: ParamIndexMap,
): string {
  assertScalarSubquery(expr.query);
  return `(${renderSelect(expr.query, contract, pim)})`;
}

function renderWhere(expr: AnyExpression, contract: PostgresContract, pim: ParamIndexMap): string {
  return renderExpr(expr, contract, pim);
}

function renderNullCheck(
  expr: NullCheckExpr,
  contract: PostgresContract,
  pim: ParamIndexMap,
): string {
  const rendered = renderExpr(expr.expr, contract, pim);
  const renderedExpr = isAtomicExpressionKind(expr.expr.kind) ? rendered : `(${rendered})`;
  return expr.isNull ? `${renderedExpr} IS NULL` : `${renderedExpr} IS NOT NULL`;
}

/**
 * Atomic expression kinds whose rendered SQL is already self-delimited (a column reference, parameter, literal, function call, aggregate, etc.) and therefore does not need surrounding parentheses when used as the left operand of a postfix predicate like `IS NULL` or `IS NOT NULL`, or as either operand of a binary infix operator.
 *
 * Anything not in this set is treated as composite (binary, AND/OR/NOT, EXISTS, nested IS NULL, subqueries, operation templates) and gets wrapped to preserve grouping.
 */
function isAtomicExpressionKind(kind: AnyExpression['kind']): boolean {
  switch (kind) {
    case 'column-ref':
    case 'identifier-ref':
    case 'param-ref':
    case 'prepared-param-ref':
    case 'literal':
    case 'aggregate':
    case 'window-func':
    case 'json-object':
    case 'json-array-agg':
    case 'list':
      return true;
    case 'subquery':
    case 'operation':
    case 'binary':
    case 'and':
    case 'or':
    case 'exists':
    case 'null-check':
    case 'not':
    case 'raw-expr':
      return false;
  }
}

function renderBinary(expr: BinaryExpr, contract: PostgresContract, pim: ParamIndexMap): string {
  if (expr.right.kind === 'list' && expr.right.values.length === 0) {
    if (expr.op === 'in') {
      return 'FALSE';
    }
    if (expr.op === 'notIn') {
      return 'TRUE';
    }
  }

  const leftExpr = expr.left;
  const left = renderExpr(leftExpr, contract, pim);
  const leftRendered =
    leftExpr.kind === 'operation' || leftExpr.kind === 'subquery' ? `(${left})` : left;

  const rightNode = expr.right;
  let right: string;
  switch (rightNode.kind) {
    case 'list':
      right = renderListLiteral(rightNode, contract, pim);
      break;
    case 'literal':
      right = renderLiteral(rightNode);
      break;
    case 'column-ref':
      right = renderColumn(rightNode);
      break;
    case 'param-ref':
    case 'prepared-param-ref':
      right = renderParamRef(rightNode, pim);
      break;
    default:
      right = renderExpr(rightNode, contract, pim);
      break;
  }

  const operatorMap: Record<BinaryExpr['op'], string> = {
    eq: '=',
    neq: '!=',
    gt: '>',
    lt: '<',
    gte: '>=',
    lte: '<=',
    like: 'LIKE',
    in: 'IN',
    notIn: 'NOT IN',
  };

  return `${leftRendered} ${operatorMap[expr.op]} ${right}`;
}

function renderListLiteral(
  expr: ListExpression,
  contract: PostgresContract,
  pim: ParamIndexMap,
): string {
  if (expr.values.length === 0) {
    return '(NULL)';
  }
  const values = expr.values
    .map((v) => {
      if (v.kind === 'param-ref' || v.kind === 'prepared-param-ref') {
        return renderParamRef(v, pim);
      }
      if (v.kind === 'literal') return renderLiteral(v);
      return renderExpr(v, contract, pim);
    })
    .join(', ');
  return `(${values})`;
}

function renderColumn(ref: ColumnRef): string {
  if (ref.table === 'excluded') {
    return `excluded.${quoteIdentifier(ref.column)}`;
  }
  return `${quoteIdentifier(ref.table)}.${quoteIdentifier(ref.column)}`;
}

function renderAggregateExpr(
  expr: AggregateExpr,
  contract: PostgresContract,
  pim: ParamIndexMap,
): string {
  const fn = expr.fn.toUpperCase();
  if (!expr.expr) {
    return `${fn}(*)`;
  }
  return `${fn}(${renderExpr(expr.expr, contract, pim)})`;
}

function renderWindowFuncExpr(
  expr: WindowFuncExpr,
  contract: PostgresContract,
  pim: ParamIndexMap,
): string {
  const fn = expr.fn.toUpperCase();
  const args = expr.args.map((arg) => renderExpr(arg, contract, pim)).join(', ');
  const partitionClause =
    expr.partitionBy && expr.partitionBy.length > 0
      ? `PARTITION BY ${expr.partitionBy.map((e) => renderExpr(e, contract, pim)).join(', ')}`
      : '';
  const orderClause =
    expr.orderBy && expr.orderBy.length > 0
      ? `ORDER BY ${renderOrderByItems(expr.orderBy, contract, pim)}`
      : '';
  const over = [partitionClause, orderClause].filter((part) => part.length > 0).join(' ');
  return `${fn}(${args}) OVER (${over})`;
}

/**
 * Maximum key/value pairs emitted in a single `jsonb_build_object` call. Postgres caps function
 * arguments at 100, so 50 key/value pairs (100 args) is the largest safe batch; wider objects are
 * split across multiple `jsonb_build_object(...)` calls and merged with the JSONB `||`
 * concatenation operator (which plain `json` does not support).
 */
const JSONB_BUILD_OBJECT_MAX_PAIRS = 50;

function renderJsonObjectExpr(
  expr: JsonObjectExpr,
  contract: PostgresContract,
  pim: ParamIndexMap,
): string {
  const renderedPairs = expr.entries.map((entry): [string, string] => {
    const key = `'${escapeLiteral(entry.key)}'`;
    if (entry.value.kind === 'literal') {
      return [key, renderLiteral(entry.value)];
    }
    return [key, renderExpr(entry.value, contract, pim)];
  });
  if (renderedPairs.length === 0) {
    return 'jsonb_build_object()';
  }
  const chunks: string[] = [];
  for (let i = 0; i < renderedPairs.length; i += JSONB_BUILD_OBJECT_MAX_PAIRS) {
    const slice = renderedPairs.slice(i, i + JSONB_BUILD_OBJECT_MAX_PAIRS);
    chunks.push(`jsonb_build_object(${slice.flat().join(', ')})`);
  }
  return chunks.join(' || ');
}

function renderOrderByItems(
  items: ReadonlyArray<OrderByItem>,
  contract: PostgresContract,
  pim: ParamIndexMap,
): string {
  return items
    .map((item) => `${renderExpr(item.expr, contract, pim)} ${item.dir.toUpperCase()}`)
    .join(', ');
}

function renderJsonArrayAggExpr(
  expr: JsonArrayAggExpr,
  contract: PostgresContract,
  pim: ParamIndexMap,
): string {
  const aggregateOrderBy =
    expr.orderBy && expr.orderBy.length > 0
      ? ` ORDER BY ${renderOrderByItems(expr.orderBy, contract, pim)}`
      : '';
  const aggregated = `json_agg(${renderExpr(expr.expr, contract, pim)}${aggregateOrderBy})`;
  if (expr.onEmpty === 'emptyArray') {
    return `coalesce(${aggregated}, json_build_array())`;
  }
  return aggregated;
}

function renderExpr(expr: AnyExpression, contract: PostgresContract, pim: ParamIndexMap): string {
  const node = expr;
  switch (node.kind) {
    case 'column-ref':
      return renderColumn(node);
    case 'identifier-ref':
      return quoteIdentifier(node.name);
    case 'operation':
      return renderOperation(node, contract, pim);
    case 'subquery':
      return renderSubqueryExpr(node, contract, pim);
    case 'aggregate':
      return renderAggregateExpr(node, contract, pim);
    case 'window-func':
      return renderWindowFuncExpr(node, contract, pim);
    case 'json-object':
      return renderJsonObjectExpr(node, contract, pim);
    case 'json-array-agg':
      return renderJsonArrayAggExpr(node, contract, pim);
    case 'binary':
      return renderBinary(node, contract, pim);
    case 'and':
      if (node.exprs.length === 0) {
        return 'TRUE';
      }
      return `(${node.exprs.map((part) => renderExpr(part, contract, pim)).join(' AND ')})`;
    case 'or':
      if (node.exprs.length === 0) {
        return 'FALSE';
      }
      return `(${node.exprs.map((part) => renderExpr(part, contract, pim)).join(' OR ')})`;
    case 'exists': {
      const notKeyword = node.notExists ? 'NOT ' : '';
      const subquery = renderSelect(node.subquery, contract, pim);
      return `${notKeyword}EXISTS (${subquery})`;
    }
    case 'null-check':
      return renderNullCheck(node, contract, pim);
    case 'not':
      return `NOT (${renderExpr(node.expr, contract, pim)})`;
    case 'param-ref':
    case 'prepared-param-ref':
      return renderParamRef(node, pim);
    case 'literal':
      return renderLiteral(node);
    case 'list':
      return renderListLiteral(node, contract, pim);
    case 'raw-expr':
      return renderRawExpr(node, contract, pim);
    // v8 ignore next 4
    default:
      throw new Error(
        `Unsupported expression node kind: ${(node satisfies never as { kind: string }).kind}`,
      );
  }
}

function renderParamRef(ref: AnyParamRef, pim: ParamIndexMap): string {
  const index = pim.indexMap.get(ref);
  if (index === undefined) {
    throw new Error('ParamRef not found in index map');
  }
  if (ref.kind === 'prepared-param-ref') {
    return renderTypedParam(
      index,
      ref.codec.codecId,
      pim.codecLookup,
      ref.codec.many,
      ref.codec.typeParams,
    );
  }
  if (ref.codec === undefined) {
    throw runtimeError(
      'RUNTIME.PARAM_REF_MISSING_CODEC',
      'Postgres renderer: ParamRef reached lowering without a bound CodecRef. ' +
        'Every column-bound ParamRef must carry a codec under the AST-bound codec contract. ' +
        'This usually indicates a builder path that constructed a ParamRef without threading the column codec.',
      { paramIndex: index, ...ifDefined('name', ref.name) },
    );
  }
  return renderTypedParam(
    index,
    ref.codec.codecId,
    pim.codecLookup,
    ref.codec.many,
    ref.codec.typeParams,
  );
}

function renderLiteral(expr: LiteralExpr): string {
  if (typeof expr.value === 'string') {
    return `'${escapeLiteral(expr.value)}'`;
  }
  if (typeof expr.value === 'number' || typeof expr.value === 'boolean') {
    return String(expr.value);
  }
  if (typeof expr.value === 'bigint') {
    return String(expr.value);
  }
  if (expr.value === null) {
    return 'NULL';
  }
  if (expr.value === undefined) {
    return 'NULL';
  }
  if (expr.value instanceof Date) {
    return `'${escapeLiteral(expr.value.toISOString())}'`;
  }
  if (Array.isArray(expr.value)) {
    return `ARRAY[${expr.value.map((v: unknown) => renderLiteral(new LiteralExpr(v))).join(', ')}]`;
  }
  const json = JSON.stringify(expr.value);
  if (json === undefined) {
    return 'NULL';
  }
  return `'${escapeLiteral(json)}'`;
}

function renderOperation(
  expr: OperationExpr,
  contract: PostgresContract,
  pim: ParamIndexMap,
): string {
  const self = renderExpr(expr.self, contract, pim);
  const args = expr.args.map((arg) => {
    return renderExpr(arg, contract, pim);
  });

  // Resolve `{{self}}` and `{{argN}}` from the original template in a single pass. Doing this with sequential `String.prototype.replace` calls is unsafe: a substituted fragment can itself contain text that matches a later token (e.g. an arg literal containing the substring `{{arg1}}`), and the next iteration would corrupt it. A single regex callback never re-scans already-substituted output.
  return expr.lowering.template.replace(
    /\{\{self\}\}|\{\{arg(\d+)\}\}/g,
    (token, argIndex: string | undefined) => {
      if (token === '{{self}}') {
        return self;
      }
      const arg = args[Number(argIndex)];
      if (arg === undefined) {
        throw new Error(
          `Operation lowering template for "${expr.method}" referenced missing argument {{arg${argIndex}}}; template has ${args.length} arg(s)`,
        );
      }
      return arg;
    },
  );
}

function renderJoin(join: JoinAst, contract: PostgresContract, pim: ParamIndexMap): string {
  const joinType = join.joinType.toUpperCase();
  const lateral = join.lateral ? 'LATERAL ' : '';
  const source = renderSource(join.source, contract, pim);
  const onClause = renderJoinOn(join.on, contract, pim);
  return `${joinType} JOIN ${lateral}${source} ON ${onClause}`;
}

function renderJoinOn(on: JoinOnExpr, contract: PostgresContract, pim: ParamIndexMap): string {
  if (on.kind === 'eq-col-join-on') {
    const left = renderColumn(on.left);
    const right = renderColumn(on.right);
    return `${left} = ${right}`;
  }
  return renderWhere(on, contract, pim);
}

function getInsertColumnOrder(
  rows: ReadonlyArray<Record<string, InsertValue>>,
  contract: PostgresContract,
  tableRef: Pick<TableSource, 'name' | 'namespaceId'>,
): string[] {
  const tableName = tableRef.name;
  const orderedColumns: string[] = [];
  const seenColumns = new Set<string>();

  for (const row of rows) {
    for (const column of Object.keys(row)) {
      if (seenColumns.has(column)) {
        continue;
      }
      seenColumns.add(column);
      orderedColumns.push(column);
    }
  }

  if (orderedColumns.length > 0) {
    return orderedColumns;
  }

  let table: { columns: Readonly<Record<string, unknown>> } | undefined;
  if (tableRef.namespaceId !== undefined) {
    const ns = contract.storage.namespaces[tableRef.namespaceId];
    table = ns !== undefined ? ns.entries.table?.[tableName] : undefined;
  }
  if (table === undefined) {
    for (const ns of Object.values(contract.storage.namespaces)) {
      const found = ns.entries.table?.[tableName];
      if (found !== undefined) {
        table = found;
        break;
      }
    }
  }
  if (!table) {
    throw new Error(`INSERT target table not found in contract storage: ${tableName}`);
  }
  return Object.keys(table.columns);
}

function renderInsertValue(
  value: InsertValue | undefined,
  contract: PostgresContract,
  pim: ParamIndexMap,
): string {
  if (!value || value.kind === 'default-value') {
    return 'DEFAULT';
  }

  switch (value.kind) {
    case 'param-ref':
    case 'prepared-param-ref':
      return renderParamRef(value, pim);
    case 'column-ref':
      return renderColumn(value);
    case 'raw-expr':
      return renderExpr(value, contract, pim);
    // v8 ignore next 4
    default:
      throw new Error(
        `Unsupported value node in INSERT: ${(value satisfies never as { kind: string }).kind}`,
      );
  }
}

function renderInsert(ast: InsertAst, contract: PostgresContract, pim: ParamIndexMap): string {
  const table = qualifyTableFromNamespaceCoordinate(ast.table, contract);
  const rows = ast.rows;
  if (rows.length === 0) {
    throw new Error('INSERT requires at least one row');
  }
  const hasExplicitValues = rows.some((row) => Object.keys(row).length > 0);
  const insertClause = (() => {
    if (!hasExplicitValues) {
      if (rows.length === 1) {
        return `INSERT INTO ${table} DEFAULT VALUES`;
      }

      const defaultColumns = getInsertColumnOrder(rows, contract, ast.table);
      if (defaultColumns.length === 0) {
        return `INSERT INTO ${table} VALUES ${rows.map(() => '()').join(', ')}`;
      }

      const quotedColumns = defaultColumns.map((column) => quoteIdentifier(column));
      const defaultRow = `(${defaultColumns.map(() => 'DEFAULT').join(', ')})`;
      return `INSERT INTO ${table} (${quotedColumns.join(', ')}) VALUES ${rows
        .map(() => defaultRow)
        .join(', ')}`;
    }

    const columnOrder = getInsertColumnOrder(rows, contract, ast.table);
    const columns = columnOrder.map((column) => quoteIdentifier(column));
    const values = rows
      .map((row) => {
        const renderedRow = columnOrder.map((column) =>
          renderInsertValue(row[column], contract, pim),
        );
        return `(${renderedRow.join(', ')})`;
      })
      .join(', ');

    return `INSERT INTO ${table} (${columns.join(', ')}) VALUES ${values}`;
  })();
  const onConflictClause = ast.onConflict
    ? (() => {
        const conflictColumns = ast.onConflict.columns.map((col) => quoteIdentifier(col.column));
        if (conflictColumns.length === 0) {
          throw new Error('INSERT onConflict requires at least one conflict column');
        }

        const action = ast.onConflict.action;
        switch (action.kind) {
          case 'do-nothing':
            return ` ON CONFLICT (${conflictColumns.join(', ')}) DO NOTHING`;
          case 'do-update-set': {
            const updateEntries = Object.entries(action.set);
            if (updateEntries.length === 0) {
              throw new Error('INSERT onConflict do-update-set requires at least one assignment');
            }
            const updates = updateEntries.map(([colName, value]) => {
              return `${quoteIdentifier(colName)} = ${renderExpr(value, contract, pim)}`;
            });
            return ` ON CONFLICT (${conflictColumns.join(', ')}) DO UPDATE SET ${updates.join(', ')}`;
          }
          // v8 ignore next 4
          default:
            throw new Error(
              `Unsupported onConflict action: ${(action satisfies never as { kind: string }).kind}`,
            );
        }
      })()
    : '';
  const returningClause = ast.returning?.length
    ? ` RETURNING ${renderReturning(ast.returning, contract, pim)}`
    : '';

  return `${insertClause}${onConflictClause}${returningClause}`;
}

function renderUpdate(ast: UpdateAst, contract: PostgresContract, pim: ParamIndexMap): string {
  const table = qualifyTableFromNamespaceCoordinate(ast.table, contract);
  const setEntries = Object.entries(ast.set);
  if (setEntries.length === 0) {
    throw new Error('UPDATE requires at least one SET assignment');
  }
  const setClauses = setEntries.map(([col, val]) => {
    const column = quoteIdentifier(col);
    return `${column} = ${renderExpr(val, contract, pim)}`;
  });

  const whereClause = ast.where ? ` WHERE ${renderWhere(ast.where, contract, pim)}` : '';
  const returningClause = ast.returning?.length
    ? ` RETURNING ${renderReturning(ast.returning, contract, pim)}`
    : '';

  return `UPDATE ${table} SET ${setClauses.join(', ')}${whereClause}${returningClause}`;
}

function renderDelete(ast: DeleteAst, contract: PostgresContract, pim: ParamIndexMap): string {
  const table = qualifyTableFromNamespaceCoordinate(ast.table, contract);
  const whereClause = ast.where ? ` WHERE ${renderWhere(ast.where, contract, pim)}` : '';
  const returningClause = ast.returning?.length
    ? ` RETURNING ${renderReturning(ast.returning, contract, pim)}`
    : '';

  return `DELETE FROM ${table}${whereClause}${returningClause}`;
}

function renderRawSql(ast: RawSqlExpr, contract: PostgresContract, pim: ParamIndexMap): string {
  const out: string[] = [];
  for (let i = 0; i < ast.fragments.length; i++) {
    out.push(ast.fragments[i] ?? '');
    if (i < ast.args.length) {
      const arg = ast.args[i];
      if (arg !== undefined) {
        out.push(renderExpr(arg, contract, pim));
      }
    }
  }
  return out.join('');
}

function renderRawExpr(node: RawExpr, contract: PostgresContract, pim: ParamIndexMap): string {
  return node.parts
    .map((part) => (typeof part === 'string' ? part : renderExpr(part, contract, pim)))
    .join('');
}