objects.ts

/**
 * The DuckDB object surface (issue #789)
 *
 * The statements, the row shapes and the pure derivations behind `listContainers`,
 * `countObjects`, `listObjects` and `describeObject`. The four methods themselves live on
 * the provider in `index.ts`; nothing here holds a client, so every function below is a
 * pure function of a declaration and a path.
 *
 * DuckDB is the SECOND two-level engine in #789 (SQL Server was the first), and the
 * second level is real rather than nominal: `ATTACH` puts another whole catalog in the
 * same connection, and a three-part name reaches into it, so a DuckDB session genuinely
 * holds databases holding schemas holding objects.
 *
 * Everything below was measured against DuckDB v1.5.5 through `@duckdb/node-api`
 * 1.5.5-r.4 on 2026-09-11. The engine is embedded, so those measurements are taken by
 * `tests/integration/db/duckdb-provider.test.ts` against a REAL engine on every run
 * rather than against a canned recordset.
 *
 * Four facts shape the statements here, each of which a reader would otherwise get wrong:
 *
 * - **`duckdb_schemas().internal` is TRUE for `main` in a USER database.** Filtering a
 *   schema listing on `NOT internal` therefore drops the default schema, which is where
 *   most objects live. The container list filters on the CATALOG instead, where `internal`
 *   means what a reader expects: `system` and `temp` carry it and no attached database
 *   does. Already recorded for `getSchema()` in `docs/providers/duckdb.md` section 3.3.
 * - **Every built-in function lives in the `system` catalog** (`system.main` and
 *   `system.pg_catalog`, 2949 rows on a bare instance), so a `database_name = $1` filter
 *   over a user catalog already excludes all of them and the macro read needs no
 *   `internal` predicate of its own.
 * - **There is no identifier interpolation anywhere in this file.** Every `duckdb_*` table
 *   function publishes its container as a COLUMN, so a catalog and a schema are bound
 *   STRINGS rather than parts of a three-part name. That removes the whole class of
 *   quoting defect SQL Server needs `escapeIdentifier` for here.
 * - **A DuckDB foreign key never crosses a schema**: `Binder Error: Creating foreign keys
 *   across different schemas or catalogs is not supported`, measured. So a referenced
 *   table is always in the reading object's own schema.
 */

import { QueryError } from "../../../errors";
import { assertContainerPathShape, containerDepth, type ContainerPathShapeEngine } from "../../../object-kinds";
import { comparePaths } from "../../../object-path";
import { unquoteLiteral } from "@/lib/sql/values";
import { displayName } from "./introspect";
import type {
  ContainerLevelSpec,
  DatabaseObject,
  KindCount,
  ObjectDetail,
  ObjectKindSpec,
  ObjectSourceForm,
  ObjectSourceOrigin,
  ProviderCapabilities,
} from "../../../types";

/**
 * DuckDB's identity for the shared container-path renderer.
 *
 * Which paths this engine accepts is not a field here: it is `containerPathShapes` in
 * `getCapabilities()` (`./index.ts`), which the object routes read too (#1147).
 */
const DUCKDB_CONTAINER_PATH_ENGINE: ContainerPathShapeEngine = {
  code: "duckdb",
  label: "A DuckDB",
  shapeNames: "label",
};

// ============================================================================
// The macro vocabulary, derived from the ENGINE
// ============================================================================

/**
 * The `duckdb_functions().function_type` spellings that are a MACRO.
 *
 * DuckDB has exactly two macro forms and the documentation names both: `CREATE MACRO
 * f(x) AS <expression>` is a scalar macro and `CREATE MACRO f(x) AS TABLE <select>` is a
 * table macro. They are distinguished only by `function_type`; there is no
 * `duckdb_macros()` and `information_schema` has no `.routines` on this engine, so this
 * one column is the whole discriminator.
 *
 * **Derived from the ENGINE and pinned against it** (standing ruling 5a, #789). Task 10
 * took its vocabulary from a `SELECT DISTINCT` over its own fixture, and a spelling the
 * engine can produce that the fixture lacked fell out of the count AND the listing at
 * once - so ruling 5f still held while the object was invisible in the tree. The guard
 * against that here is `FUNCTION_TYPE_RULES` below, which every `function_type` the live
 * engine publishes has to be accounted for by; the fixture then holds one of each macro
 * form so the two arms cannot rot into one.
 */
const MACRO_FUNCTION_TYPES: readonly string[] = ["macro", "table_macro"];

/**
 * Every `function_type` this provider has a RULE for, and for the excluded ones the
 * reason.
 *
 * This exists because "the vocabulary enumerates the engine" is a claim that decays. A
 * future DuckDB can add a `function_type`, and the failure mode is silence: a spelling no
 * arm names is dropped from the count AND from the listing, so the two still agree, every
 * gate still passes, and the object is simply absent from the tree.
 *
 * The engine is embedded, so unlike MySQL's `CATALOG_TYPE_RULES` this needs no separate
 * live script: `tests/integration/db/duckdb-provider.test.ts` asks the running engine for
 * its own `SELECT DISTINCT function_type` - 2951 built-in rows plus the fixture's two
 * user macros - and fails NAMING anything outside this record. The excluded half is a map
 * rather than a list so an exclusion cannot be added without saying why.
 */
export const FUNCTION_TYPE_RULES: {
  readonly macros: readonly string[];
  readonly excluded: Readonly<Record<string, string>>;
} = {
  macros: MACRO_FUNCTION_TYPES,
  excluded: {
    scalar: "a built-in scalar function, compiled into the engine; nobody wrote it in this database",
    aggregate: "a built-in aggregate, same as scalar",
    table: "a built-in table function, `duckdb_tables()` and `read_csv` among them",
    pragma: "a PRAGMA, invoked as a statement rather than called, and not an object in any schema",
  },
};

// ============================================================================
// The catalog function behind each declared kind
// ============================================================================

interface DuckDBObjectSource {
  /** The `duckdb_*` table function this kind's rows come from. */
  readonly relation: string;
  /** The column in that function's output carrying the object's own name. */
  readonly nameColumn: string;
  /** An extra predicate ANDed onto the container filter, where the kind needs one. */
  readonly predicate?: string;
  /** The column in that function's output carrying the object's definition text (#789). */
  readonly definitionColumn: string;
  /**
   * What the text in `definitionColumn` IS, and on this engine it is not one answer (#789).
   *
   * `complete` for the three kinds whose column is called `sql`: measured on v1.5.5, each
   * answers a statement ending in `;` that runs as given. `partial` for `macro`, which is
   * the only `partial` kind ON THIS ENGINE and is deliberate:
   * `duckdb_functions().macro_definition` is a BODY, `(x + 1)` for a scalar macro and
   * `SELECT * FROM analytics.events LIMIT n` for a table macro, and DuckDB publishes no
   * `CREATE MACRO` statement anywhere. Assembling one out of `parameters` would mean
   * showing the user a statement the engine never published, which they might copy and
   * run; the caption that says "this is the body only, not a complete statement" is
   * cheaper and more honest. Phase 3 assembles, per the Phase 1 rule that the provider
   * builds the statement and core never does.
   *
   * IT IS NOT THE FLEET'S ONLY `partial` PRODUCER, and an earlier revision of this comment
   * said it was. The #789 design names three: PostgreSQL `view` and `materialized_view`,
   * where `pg_get_viewdef` answers a bare SELECT, this engine's `macro`, and Couchbase
   * `function`, whose `definition.text` is a body. `postgres.ts` already answers
   * `form: "partial"` for a relation, so changing what this record says would not leave the
   * arm without a producer.
   */
  readonly definitionForm: ObjectSourceForm;
  /**
   * WHERE the text in `definitionColumn` came from, and on this engine all four agree (#789).
   *
   * `regenerated` for every kind, measured on v1.5.5 rather than assumed: `CREATE TABLE
   * main.customers (id INTEGER PRIMARY KEY, name VARCHAR NOT NULL, note VARCHAR DEFAULT
   * 'none')` reads back as `CREATE TABLE customers(id INTEGER PRIMARY KEY, "name" VARCHAR
   * NOT NULL, note VARCHAR DEFAULT('none'));`, so the schema qualification is gone, `name`
   * is quoted and the default is parenthesised. None of it is the author's bytes.
   *
   * It lives HERE, beside `definitionForm`, rather than as a literal in the read, because
   * it is the same shape of fact: a per-kind statement about the text one catalog column
   * publishes. A fifth kind whose text really were stored bytes would otherwise be added
   * with a `definitionColumn` and a `definitionForm`, because that is where the pattern
   * points, and silently keep the origin the read hardcodes.
   */
  readonly definitionOrigin: ObjectSourceOrigin;
}

/** `'macro', 'table_macro'`: a vocabulary as a SQL literal list. */
function literalList(values: readonly string[]): string {
  return values.map((value) => `'${value}'`).join(", ");
}

/**
 * One entry per declared kind, and the COUNT and the LISTING are both built from it.
 *
 * That is standing ruling 5f (#789) discharged structurally rather than by care: the
 * listing must contain exactly what the count counted, and both Oracle and MySQL failed
 * that on their first pass because a count statement and a listing statement were written
 * separately and drifted. Here `countsSql` and `listObjectsSql` call the same
 * `objectSource()` and the same `kindFilter()`, so the two cannot disagree about which
 * `duckdb_*` function answers for a kind or about which rows of it are in scope.
 *
 * DuckDB has no trigger and no stored procedure, so neither is declared and neither is
 * here. `information_schema` exists on this engine but is partial - it has no `.views`
 * and no `.routines` - so nothing below reaches for it.
 */
const DUCKDB_OBJECT_SOURCES: Record<string, DuckDBObjectSource> = {
  table: {
    relation: "duckdb_tables()",
    nameColumn: "table_name",
    definitionColumn: "sql",
    definitionForm: "complete",
    definitionOrigin: "regenerated",
  },
  view: {
    relation: "duckdb_views()",
    nameColumn: "view_name",
    definitionColumn: "sql",
    definitionForm: "complete",
    definitionOrigin: "regenerated",
  },
  macro: {
    relation: "duckdb_functions()",
    nameColumn: "function_name",
    predicate: `function_type IN (${literalList(MACRO_FUNCTION_TYPES)})`,
    definitionColumn: "macro_definition",
    definitionForm: "partial",
    definitionOrigin: "regenerated",
  },
  sequence: {
    relation: "duckdb_sequences()",
    nameColumn: "sequence_name",
    definitionColumn: "sql",
    definitionForm: "complete",
    definitionOrigin: "regenerated",
  },
};

/**
 * Which catalog function answers for one kind, or a refusal naming it.
 *
 * The lookup is `Object.hasOwn` and not a bare index: a kind id is an OPEN string, so
 * `DUCKDB_OBJECT_SOURCES["toString"]` answers a function off the prototype chain rather
 * than `undefined`, and a kind called `toString` would then reach the statement builder
 * as a kind this engine has (standing ruling 5g, #789).
 */
function objectSource(kind: string): DuckDBObjectSource {
  if (!Object.hasOwn(DUCKDB_OBJECT_SOURCES, kind)) {
    throw new QueryError(
      `DuckDB declares the kind "${kind}" but has no catalog function that answers for it`,
      "duckdb",
    );
  }
  return DUCKDB_OBJECT_SOURCES[kind];
}

// ============================================================================
// Container statements
// ============================================================================

/**
 * The catalogs this connection can address: the one it opened plus everything `ATTACH`ed.
 *
 * `NOT internal` is the engine's own answer for `system` and `temp`, the two DuckDB
 * attaches to every session. `system` holds the `duckdb_*` and `pg_catalog` views and
 * nothing a person wrote; `temp` holds session-scoped objects, which is a real absence
 * recorded in `docs/providers/duckdb.md` rather than a silent one.
 *
 * `is_session_default` marks the catalog the session is already in, which on this engine
 * is the file the connection names - every other row is something a statement attached.
 */
export const CATALOGS_SQL = `SELECT database_name, database_name = current_database() AS is_session_default
      FROM duckdb_databases()
      WHERE NOT internal
      ORDER BY database_name`;

/**
 * One catalog's schemas, with the session's own schema marked.
 *
 * NO `internal` filter, and that is measured rather than an oversight: `duckdb_schemas()`
 * reports `internal = true` for `main` in a USER database, so `NOT internal` here would
 * drop the default schema and with it most of the objects in the tree. The catalog filter
 * is what keeps `system` and `temp` out, and it is bound rather than interpolated.
 *
 * `is_session_default` is marked HERE as well as on the catalog, and standing ruling 5a2
 * (#789) is why: first paint walks the container chain down to the session default at the
 * DEEPEST declared level, so a two-level engine that marked only its catalogs would leave
 * the tree opening a database and stopping, with no counts read at all.
 *
 * The predicate carries BOTH halves deliberately. `current_schema()` names a schema in
 * `current_database()`, so comparing the schema alone would mark `warehouse.main` as the
 * session default whenever the session sat in `memory.main` - measured: with
 * `USE warehouse.stock`, `current_database()` moves to `warehouse` and `current_schema()`
 * to `stock`, and the two always answer one real pair.
 */
export const SCHEMAS_SQL = `SELECT schema_name,
             database_name = current_database() AND schema_name = current_schema() AS is_session_default
      FROM duckdb_schemas()
      WHERE database_name = $1
      ORDER BY schema_name`;

// ============================================================================
// Count and listing statements
// ============================================================================

/**
 * The container filter both reads share, as a bound predicate.
 *
 * `$1` is the catalog and `$2` the schema, and the numbered form is deliberate: DuckDB
 * reuses a numbered parameter across a `UNION ALL`, so the counts statement binds one
 * catalog once no matter how many kinds are declared. A positional `?` would need one
 * copy per arm and the count would silently shift the moment a kind was added.
 */
function containerFilter(bySchema: boolean): string {
  return bySchema ? "database_name = $1 AND schema_name = $2" : "database_name = $1";
}

/** The container filter plus whatever extra predicate the kind's own catalog needs. */
function kindFilter(source: DuckDBObjectSource, bySchema: boolean): string {
  const filter = containerFilter(bySchema);
  return source.predicate === undefined ? filter : `${filter} AND ${source.predicate}`;
}

/**
 * Every declared kind counted in ONE round trip, one `UNION ALL` arm per kind.
 *
 * Built from the DECLARATION rather than from a constant, so a kind added to
 * `objectKinds` without an entry in `DUCKDB_OBJECT_SOURCES` fails loudly here instead of
 * drawing a folder nothing can fill. The kind id reaches the SQL as a literal, which is
 * safe by construction: `objectSource()` refuses anything outside the four keys above, so
 * no caller-supplied string is ever interpolated.
 */
export function countsSql(kinds: readonly ObjectKindSpec[], bySchema: boolean): string {
  return kinds
    .map((kind) => {
      const source = objectSource(kind.id);
      return `SELECT '${kind.id}' AS kind, COUNT(*) AS n FROM ${source.relation} WHERE ${kindFilter(source, bySchema)}`;
    })
    .join("\n      UNION ALL ");
}

/**
 * One kind's objects in one container.
 *
 * No `ORDER BY`: the ordering is done in `comparePaths` over the produced PATHS, which is
 * one rule for every kind rather than four `ORDER BY` clauses that can disagree.
 *
 * No row count column either, and that is a decision the engine forces. `estimated_size`
 * is the only cardinality DuckDB publishes per table and it is an ESTIMATE - measured in
 * #424, after deleting 19M of a table's 20M rows it answered 1,076,480 against a true
 * 1,000,000 and a `CHECKPOINT` did not move it - so publishing it as `rowCount` would put
 * a wrong number on a folder row. The honest alternative is a `count(*)` per object,
 * which is the N+1 the inventory route already had `includeColumns` removed for.
 */
export function listObjectsSql(kind: string, bySchema: boolean): string {
  const source = objectSource(kind);
  return `SELECT schema_name, ${source.nameColumn} AS name
      FROM ${source.relation}
      WHERE ${kindFilter(source, bySchema)}`;
}

// ============================================================================
// Detail statements
// ============================================================================

/**
 * The target set of one bulk read: every object of one kind in one container, in the
 * engine's own order, optionally cut (#789).
 *
 * The same `objectSource()` and the same `kindFilter()` the count and the listing use, so
 * the three cannot disagree about which rows are in scope. What it adds is an `ORDER BY`,
 * which the listing deliberately does not carry: a bound with no order keeps an arbitrary
 * subset, and two calls could keep different ones.
 *
 * `schema_name` leads the order because a CATALOG-level container spans every schema under
 * it, so `(schema_name, name)` is what is unique there; a schema-level read has one value
 * in the first column and is ordered by name alone in effect.
 *
 * The `LIMIT` placeholder takes the next free number after the container binds, which is
 * why it is derived from `bySchema` rather than written: the container filter is `$1`
 * alone at catalog level and `$1, $2` at schema level.
 */
export function bulkTargetSql(kind: string, bySchema: boolean, bounded: boolean): string {
  const source = objectSource(kind);
  const limit = bounded ? `\n      LIMIT $${bySchema ? 3 : 2}` : "";
  return `SELECT schema_name, ${source.nameColumn} AS name
      FROM ${source.relation}
      WHERE ${kindFilter(source, bySchema)}
      ORDER BY schema_name, name${limit}`;
}

/**
 * One detail read of a WHOLE FOLDER, as the single read's statement joined to the target.
 *
 * Each of the four detail statements below is its `describeObject()` counterpart with the
 * `schema_name = $2 AND table_name = $3` pair replaced by a join against `described`, and
 * nothing else changed: the same catalog function, the same predicates, the same casts. A
 * rewritten statement would be the one thing this file could get wrong that no fixture
 * shows, so they are built from one function rather than hand-copied four times.
 *
 * The join is on BOTH `schema_name` and the name, never on the name alone. A catalog-level
 * read spans every schema, and this fixture holds `customers` in two of them with different
 * columns: a join on the name would answer one table with the other's columns rather than
 * an error anybody would notice.
 *
 * `database_name = $1` stays on the OUTER read as well as inside the target, because
 * `duckdb_columns()` and the two constraint functions span every attached catalog and the
 * fixture holds `memory.main.customers` and `warehouse.main.customers` at once.
 */
function bulkDetailSql(
  kind: string,
  bySchema: boolean,
  bounded: boolean,
  relation: string,
  projection: string,
  extra: string,
  order: string,
): string {
  return `WITH described AS (${bulkTargetSql(kind, bySchema, bounded)})
      SELECT o.schema_name, o.table_name, ${projection}
      FROM ${relation} o
      JOIN described d ON d.schema_name = o.schema_name AND d.name = o.table_name
      WHERE o.database_name = $1${extra}${order}`;
}

export function bulkColumnsSql(kind: string, bySchema: boolean, bounded: boolean): string {
  return bulkDetailSql(
    kind,
    bySchema,
    bounded,
    "duckdb_columns()",
    "o.column_name, o.data_type, o.is_nullable, o.column_default",
    "",
    "\n      ORDER BY o.schema_name, o.table_name, o.column_index",
  );
}

export function bulkPrimaryKeySql(kind: string, bySchema: boolean, bounded: boolean): string {
  return bulkDetailSql(
    kind,
    bySchema,
    bounded,
    "duckdb_constraints()",
    "o.constraint_column_names",
    "\n        AND o.constraint_type = 'PRIMARY KEY'",
    "",
  );
}

export function bulkForeignKeysSql(kind: string, bySchema: boolean, bounded: boolean): string {
  return bulkDetailSql(
    kind,
    bySchema,
    bounded,
    "duckdb_constraints()",
    "o.constraint_column_names, o.referenced_table, o.referenced_column_names",
    "\n        AND o.constraint_type = 'FOREIGN KEY'",
    "",
  );
}

export function bulkIndexesSql(kind: string, bySchema: boolean, bounded: boolean): string {
  return bulkDetailSql(
    kind,
    bySchema,
    bounded,
    "duckdb_indexes()",
    "o.index_name, o.is_unique, o.expressions::VARCHAR[] AS index_columns",
    "",
    "\n      ORDER BY o.schema_name, o.table_name, o.index_name",
  );
}

/**
 * One relation's columns. `duckdb_columns()` carries a VIEW's columns as well as a
 * table's, so one statement answers for both declared relation kinds.
 */
export const OBJECT_COLUMNS_SQL = `SELECT column_name, data_type, is_nullable, column_default
      FROM duckdb_columns()
      WHERE database_name = $1 AND schema_name = $2 AND table_name = $3
      ORDER BY column_index`;

/**
 * One relation's primary key, as the column names it covers.
 *
 * The type filter is explicit rather than a `NOT internal`, because `duckdb_constraints()`
 * publishes every NOT NULL and UNIQUE constraint as a row of its own: `customers` with one
 * primary key and one NOT NULL column answers three rows there.
 */
export const OBJECT_PRIMARY_KEY_SQL = `SELECT constraint_column_names
      FROM duckdb_constraints()
      WHERE database_name = $1 AND schema_name = $2 AND table_name = $3
        AND constraint_type = 'PRIMARY KEY'`;

/**
 * One relation's foreign keys.
 *
 * Read SEPARATELY from the primary key rather than filtered out of one `IN` list, and the
 * reason is a type rather than a preference: `referenced_table` is NULL on a PRIMARY KEY
 * row and never on a FOREIGN KEY one, so one statement per type is what lets the row shape
 * below say `string` instead of `string | null` and carry no null arm that no test can
 * reach.
 */
export const OBJECT_FOREIGN_KEYS_SQL = `SELECT constraint_column_names, referenced_table, referenced_column_names
      FROM duckdb_constraints()
      WHERE database_name = $1 AND schema_name = $2 AND table_name = $3
        AND constraint_type = 'FOREIGN KEY'`;

/**
 * One relation's indexes.
 *
 * No primary-key exclusion is needed, unlike SQL Server: measured, a DuckDB PRIMARY KEY
 * writes NO `duckdb_indexes()` row at all, so the listing is already exactly the indexes
 * somebody created. `expressions` is declared VARCHAR and prints as `"[a, b]"`, so the
 * cast is what stops a caller parsing it by hand and getting an expression index with a
 * comma in it wrong.
 */
export const OBJECT_INDEXES_SQL = `SELECT index_name, is_unique, expressions::VARCHAR[] AS index_columns
      FROM duckdb_indexes()
      WHERE database_name = $1 AND schema_name = $2 AND table_name = $3
      ORDER BY index_name`;

// ============================================================================
// Source statements (#789)
// ============================================================================

/**
 * ONE object's definition text, by catalog, schema and name (#789).
 *
 * Built from the SAME `objectSource()` and the SAME `kindFilter()` the count, the listing
 * and the four detail reads use, so the Source tab and the tree cannot disagree about
 * which `duckdb_*` function answers for a kind or about which rows of it are in scope. A
 * source statement written separately is how a macro read would keep answering after
 * `MACRO_FUNCTION_TYPES` changed underneath it.
 *
 * THE NAME IS THE THIRD BIND AND THE KIND IS NOT IN THE STATEMENT AT ALL, which is why
 * `readObjectSource` takes the kind as an argument. Measured on DuckDB v1.5.5 and held by
 * `docker/duckdb-init/01-object-fixture.sql`: a table, a sequence and a macro can all be
 * called `overlap` in one schema, so a read keyed on the name alone answers one of the
 * three at random. The kind picks the catalog function; the three binds pick the row.
 *
 * The column is aliased to `definition` so the row shape below is one type for four kinds
 * rather than a union over `sql` and `macro_definition`. Every bind is a parameter and
 * nothing is interpolated: `duckdb_*` table functions publish the container as a COLUMN,
 * so there is no identifier position here and no escaper is needed.
 */
export function objectSourceSql(kind: string): string {
  const source = objectSource(kind);
  return `SELECT ${source.definitionColumn} AS definition
      FROM ${source.relation}
      WHERE ${kindFilter(source, true)} AND ${source.nameColumn} = $3`;
}

/** What `form` the text one kind publishes has, read off the same record the statement is. */
export function objectSourceForm(kind: string): ObjectSourceForm {
  return objectSource(kind).definitionForm;
}

/** What `origin` the text one kind publishes has, read off that same record. */
export function objectSourceOrigin(kind: string): ObjectSourceOrigin {
  return objectSource(kind).definitionOrigin;
}

/**
 * The catalog COLUMN one kind's definition comes out of, for a refusal to name.
 *
 * Read off the same record the statement is built from, so a sentence cannot send a reader
 * to a column this read never asked for: `sql` for a table, a view and a sequence,
 * `macro_definition` for a macro.
 */
export function objectSourceColumn(kind: string): string {
  return objectSource(kind).definitionColumn;
}

/**
 * Which of FOUR ways a row carried no definition, so the refusal can say which (#789).
 *
 * `absent` is this provider asking for a column the reply does not carry and `nonText` is
 * the driver handing back a value that is not a string, which are both facts about the
 * READ; `null` and `blank` are facts about the ROW. They are kept apart because a refusal
 * stating a cause that is false for the shape in front of it is worse than one stating none.
 *
 * `nonText` was folded into `null` until the #789 fix round, and that fold is exactly the
 * defect this split exists to prevent: `{ definition: 42 }` produced "whose sql is NULL"
 * and then went on to assert that the engine was measured never to publish a NULL, so a
 * reader would conclude the provider was confused rather than that the driver's mapping had
 * moved. It is not a hypothetical mapping either: `@duckdb/node-api` 1.5.5-r.4 hands a
 * BIGINT `COUNT(*)` back as a decimal STRING (see `KindCountRow`), so this driver's JS
 * mapping is per type and not stable across a version.
 */
export type BlankDefinitionShape = "absent" | "null" | "blank" | "nonText";

export function blankDefinitionShape(row: ObjectSourceRow): BlankDefinitionShape {
  if (!Object.hasOwn(row, "definition")) return "absent";
  if (typeof row.definition === "string") return "blank";
  return row.definition === null ? "null" : "nonText";
}

/**
 * The sentence a row carrying no definition is refused with, ONE PER SHAPE, four of them (#789).
 *
 * OURS rather than the engine's, and `docs/providers/duckdb.md` records that as this
 * engine's one exception to the "the engine's own sentence, unprefixed" guarantee: DuckDB
 * supplies no sentence at all here, because from its point of view the read SUCCEEDED and
 * answered a row. There is nothing to carry verbatim.
 *
 * NONE OF THE FOUR WAS OBSERVED ON A LIVE ENGINE, and that is stated in the sentences
 * themselves rather than hidden. Measured on DuckDB v1.5.5: zero of the instance's
 * `duckdb_tables()` rows, zero of its 47 `duckdb_views()` rows, zero `duckdb_sequences()`
 * rows and zero of its 136 macros carry a NULL or whitespace-only text, and even
 * `CREATE MACRO no_body() AS NULL` publishes the four characters `NULL`. The arms exist
 * anyway, because an empty definition must never reach an editor as a definition, and
 * because the `absent` one is the failure mode recipe rule 6 (#789) exists for: a wrong
 * reply COLUMN reads as `undefined`, not as an error.
 *
 * `valueType` is the caller's own `typeof` of the value, and it is read only by the `nonText`
 * arm, which is the one shape where the useful fact is what the driver DID hand back rather
 * than what it did not.
 *
 * The COLUMN is named in the sentence, and it differs by kind - `sql` for a table, a view
 * and a sequence, `macro_definition` for a macro - so a reader is sent to the column this
 * read actually asked for rather than to a spelling that is wrong for their object.
 */
export function blankDefinitionReason(
  shape: BlankDefinitionShape,
  kind: string,
  column: string,
  name: string,
  valueType: string,
): string {
  const opening = `DuckDB answered a row for the ${kind} "${name}"`;
  if (shape === "nonText") {
    return (
      `${opening} whose ${column} came back as the JavaScript type "${valueType}" rather than a text. ` +
      "That is a fact about this read and not about the object: the column is there and it carries a " +
      "value, so what moved is the driver's mapping of it rather than the definition."
    );
  }
  if (shape === "absent") {
    return (
      `${opening} with no ${column} column at all. That is a fact about this read and not ` +
      "about the object: the statement asks for one column and the reply does not carry it."
    );
  }
  if (shape === "blank") {
    return (
      `${opening} whose ${column} holds no non-whitespace character. An empty definition is ` +
      "not a definition, so it is refused rather than opened in an editor, and the engine " +
      "supplies no sentence of its own for this."
    );
  }
  return (
    `${opening} whose ${column} is NULL. Measured on DuckDB v1.5.5 the engine publishes a ` +
    "text for every row of every catalog function this provider reads, so nothing here was " +
    "observed to produce a NULL, and the engine supplies no sentence of its own for it."
  );
}

// ============================================================================
// Row shapes
// ============================================================================

export interface CatalogRow {
  database_name: string;
  is_session_default: boolean;
}

export interface SchemaNameRow {
  schema_name: string;
  is_session_default: boolean;
}

export interface KindCountRow {
  kind: string;
  /** A DuckDB `COUNT(*)` is BIGINT, which arrives as a decimal STRING. */
  n: unknown;
}

export interface ObjectRow {
  schema_name: string;
  name: string;
}

/**
 * One row of `objectSourceSql()` (#789).
 *
 * `unknown` and not `string | null`, because the whole point of `blankDefinitionShape()` is
 * that this reply is not trusted to have the column at all: a driver that answered a
 * number, or a statement whose alias was changed, must reach the refusal arm rather than a
 * `.trim()` on a non-string.
 */
export interface ObjectSourceRow {
  definition: unknown;
}

export interface ColumnRow {
  column_name: string;
  data_type: string;
  is_nullable: boolean;
  column_default: string | null;
}

export interface PrimaryKeyRow {
  constraint_column_names: string[];
}

/**
 * `referenced_table` is `string` and not `string | null` because the statement that
 * answers this row filters to FOREIGN KEY: measured, only a PRIMARY KEY row carries NULL
 * there. `referenced_column_names` is positionally aligned with `constraint_column_names`,
 * measured on a composite key: `FOREIGN KEY (x, y) REFERENCES parent(a, b)` answers
 * `["x","y"]` against `["a","b"]`.
 */
export interface ForeignKeyRow {
  constraint_column_names: string[];
  referenced_table: string;
  referenced_column_names: string[];
}

export interface IndexRow {
  index_name: string;
  is_unique: boolean;
  index_columns: string[] | null;
}

// ============================================================================
// Derivations over the declaration
// ============================================================================

/**
 * The container levels this engine declares, cut to the depth `containerDepth()` answers.
 *
 * Every derivation below starts here rather than from a length or an index, which is
 * standing ruling 5g (#789) in its general form: NEVER index `path` or `container`
 * positionally, anywhere, for anything. All three spellings the ruling names are wrong on
 * this engine rather than merely potentially wrong, because DuckDB declares two levels:
 * a hardcoded `container.length !== 1` refuses every valid schema path, `path[1]` is the
 * SCHEMA and not the object name, and `path[0]` is the CATALOG and not the schema.
 *
 * The shape is SQL Server's (`src/lib/db/providers/sql/mssql.ts`), which is where the
 * ruling settled it.
 */
function declaredLevels(capabilities: ProviderCapabilities): readonly ContainerLevelSpec[] {
  return (capabilities.containerLevels ?? []).slice(0, containerDepth(capabilities));
}

/**
 * The container segments of a path, keyed by the LEVEL each one belongs to.
 *
 * This is what replaces `container[0]` and `path[1]`: a caller asks for `catalog` or
 * `schema` BY NAME, so a level added, removed or reordered moves every read with it.
 *
 * `Object.fromEntries` loses the key union, so the result is asserted back to it. The
 * assertion is sound by construction: the entries are exactly the declared level ids, and
 * `Partial` is what carries "a catalog-level container names no schema".
 */
function containerSegments(
  capabilities: ProviderCapabilities,
  path: readonly string[],
): Partial<Record<ContainerLevelSpec["id"], string>> {
  const levels = declaredLevels(capabilities);
  return Object.fromEntries(
    path.slice(0, levels.length).map((segment, index) => [levels[index].id, segment]),
  ) as Partial<Record<ContainerLevelSpec["id"], string>>;
}

/**
 * The segment one declared level carries, or a refusal naming the level.
 *
 * Every caller that binds a segment goes through this rather than through a non-null
 * assertion, because `undefined` reaching a bound parameter is not an error on this
 * driver - `Cannot create values of type ANY` is what the binding answers, which names
 * neither the level nor the path. The case is reachable without a bug in this file: a
 * provider that copied it and declared only a `catalog` level would have no schema
 * segment at all.
 */
function requiredSegment(
  segments: Partial<Record<ContainerLevelSpec["id"], string>>,
  level: ContainerLevelSpec["id"],
): string {
  const segment = segments[level];
  if (segment === undefined) {
    throw new QueryError(`DuckDB declares no ${level} level to read this path's segment from`, "duckdb");
  }
  return segment;
}

/**
 * The catalog segment of a listing parent, read as a tree CURSOR and not as an address.
 *
 * `listContainers()` takes a parent to say where in the tree to list, and the route holds a
 * parent to the depth ceiling alone. `containerPathShapes` governs the paths an object read
 * ADDRESSES, so running `assertContainerPathShape` here would refuse a valid `[database]` the
 * moment the declaration said `exact`, while the route had already accepted it.
 */
export function parentCatalog(capabilities: ProviderCapabilities, parent: readonly string[]): string {
  return requiredSegment(containerSegments(capabilities, parent), "catalog");
}

/** The one shape a DuckDB object path takes, spelled for the message in `objectRead()`. */
function shapeList(shapes: readonly string[][]): string {
  return shapes.map((shape) => `[${shape.join(", ")}]`).join(" or ");
}

/**
 * The segments a container path addresses, keyed by level, or a refusal naming the shapes.
 *
 * It raises rather than reading what it can and carrying on. `undefined` bound as a
 * parameter is a driver-level failure here, and a container one segment too long would
 * otherwise bind the object's own name as a schema and answer an empty folder that looks
 * exactly like a schema holding nothing.
 */
function containerTarget(
  capabilities: ProviderCapabilities,
  container: readonly string[],
): Partial<Record<ContainerLevelSpec["id"], string>> {
  assertContainerPathShape(capabilities, container, DUCKDB_CONTAINER_PATH_ENGINE);
  return containerSegments(capabilities, container);
}

/**
 * One container path resolved into everything the three reads need.
 *
 * ONE function rather than three, because `bySchema` decides both which statement is built
 * and which parameters are bound, and two derivations of the same fact are two chances to
 * disagree: a statement carrying `$2` with a one-element bind array is a driver-level
 * failure (`Values were not provided for the following prepared statement parameters`),
 * and the reverse silently counts the whole catalog under a schema's badge.
 *
 * Both fields are read BY LEVEL through `requiredSegment` and `target.schema`, never by
 * position: standing ruling 5g (#789) is that `container[0]` is the third spelling of the
 * same defect, and on a two-level engine it is wrong rather than merely fragile.
 */
export interface ContainerRead {
  readonly catalog: string;
  readonly bySchema: boolean;
  readonly binds: string[];
}

export function containerRead(capabilities: ProviderCapabilities, container: readonly string[]): ContainerRead {
  const target = containerTarget(capabilities, container);
  const catalog = requiredSegment(target, "catalog");
  const schema = target.schema;
  return {
    catalog,
    bySchema: schema !== undefined,
    binds: schema === undefined ? [catalog] : [catalog, schema],
  };
}

/**
 * The path shapes one KIND's objects are addressed by, derived from the declaration.
 *
 * ONE shape per kind, `[...levels, name]`. Every DuckDB object of every declared kind sits
 * in a schema - `CREATE TABLE t` with no schema resolves into the session's, never into no
 * schema - and nothing on this engine hangs off another object: there are no triggers at
 * all, which is the kind that gave SQL Server and Oracle a second shape, and an index is
 * not declared as a kind here.
 *
 * A kind declaring `attachedTo` is REFUSED rather than given the extra segment, because
 * the four statements above address a schema and a name and there is no read here that
 * could fill a base-object segment. Producing the shape anyway would draw a folder whose
 * every path resolves to nothing; saying so names what is missing.
 */
function objectShapes(capabilities: ProviderCapabilities, spec: ObjectKindSpec): readonly string[][] {
  if (spec.attachedTo !== undefined) {
    throw new QueryError(
      `DuckDB holds no object attached to another, so the kind "${spec.id}" cannot declare attachedTo "${spec.attachedTo}"`,
      "duckdb",
    );
  }
  return [[...declaredLevels(capabilities).map((level) => level.label.toLowerCase()), "name"]];
}

/**
 * One object path resolved into the three things its detail reads need, checked first.
 *
 * The name is `path[path.length - 1]` and NEVER `path[2]`, and the container segments come
 * from `containerSegments()` and never from `path[0]` and `path[1]`. Standing ruling 5g
 * (#789) asks every provider task to pin this; DuckDB can pin it harder than SQL Server
 * could, because a `spyOn` on `getCapabilities` can hand this provider the SAME two levels
 * in the OPPOSITE order and the test still reaches a bound value against a real engine -
 * the SQL Server task recorded that case as unreachable there (#789).
 *
 * The SCHEMA is carried as a named field as well as inside `binds`, because
 * `ForeignKeySchema.referencedTable` is spelled relative to it and reading it back out of
 * the bind array by position would reintroduce exactly the defect above.
 */
export interface ObjectRead {
  /**
   * Named as well as bound, for the reason the schema is (#789): a message or a second read
   * that wanted the catalog back would otherwise take `binds[0]`, which is the positional
   * read this whole interface exists to remove.
   */
  readonly catalog: string;
  readonly schema: string;
  readonly name: string;
  readonly binds: [string, string, string];
}

export function objectRead(
  capabilities: ProviderCapabilities,
  spec: ObjectKindSpec,
  path: readonly string[],
): ObjectRead {
  const shapes = objectShapes(capabilities, spec);
  if (!shapes.some((shape) => shape.length === path.length)) {
    throw new QueryError(
      `A DuckDB "${spec.id}" path is ${shapeList(shapes)}, received ${JSON.stringify(path)}`,
      "duckdb",
    );
  }
  const segments = containerSegments(capabilities, path);
  const catalog = requiredSegment(segments, "catalog");
  const schema = requiredSegment(segments, "schema");
  const name = path[path.length - 1];
  return { catalog, schema, name, binds: [catalog, schema, name] };
}

// ============================================================================
// Count assembly
// ============================================================================

/**
 * Every declared kind seeded at zero, before any row is read.
 *
 * Seeding is what makes "this engine has this kind and this container holds none" render
 * as a 0 badge. Building the record from the answered rows alone would leave the kind out
 * entirely, and an absent kind already means something else and stronger: the engine has
 * no such concept, so the tree draws no folder at all.
 */
export function seedZeroCounts(kinds: readonly ObjectKindSpec[]): Record<string, KindCount> {
  return Object.fromEntries(kinds.map((kind) => [kind.id, { count: 0 } as KindCount]));
}

/** Overwrites the seeded zeros with what the UNION ALL actually answered. */
export function applyKindCounts(
  counts: Record<string, KindCount>,
  rows: readonly KindCountRow[],
  toNumber: (value: unknown) => number | undefined,
): void {
  for (const row of rows) {
    const measured = toNumber(row.n);
    // An unreadable count is left at whatever it was seeded with rather than coerced to
    // 0 through `Number(undefined)`, which is NaN and renders as a blank badge.
    if (measured !== undefined) counts[row.kind] = { count: measured };
  }
}

// ============================================================================
// Row mapping and ordering
// ============================================================================

/**
 * One catalog row as the object it addresses.
 *
 * The SCHEMA comes from the row rather than from the container, which is load-bearing at
 * the catalog level where one listing spans every schema in the database.
 *
 * The path is CONSTRUCTED here, which standing ruling 5g (#789) names as the one place a
 * position is legitimately written rather than derived - and it still has to be written in
 * the DECLARED order rather than in this engine's happens-to-be order, because
 * `objectRead()` reads the same path back by level. A listing that wrote
 * `[catalog, schema, name]` while the declaration said schema then catalog would hand the
 * conformance helper a path its own `describeObject` resolves backwards, so the two sides
 * of the round trip are driven from one array.
 */
export function listedObject(
  capabilities: ProviderCapabilities,
  catalog: string,
  kind: string,
  row: ObjectRow,
): DatabaseObject {
  const segments: Record<ContainerLevelSpec["id"], string> = { catalog, schema: row.schema_name };
  const path = [...declaredLevels(capabilities).map((level) => segments[level.id]), row.name];
  return { path, name: row.name, kind };
}

/**
 * The rows of one bulk read, grouped by the object they describe.
 *
 * The key is built by the CALLER rather than here, because a schema and a name joined by
 * any printable separator is a name collision waiting to happen: two objects really can be
 * called `a` and `b.c` in schemas `a.b` and `b`. The caller uses a NUL, which no DuckDB
 * identifier can contain.
 */
export function groupByObject<T>(rows: readonly T[], key: (row: T) => string): Map<string, T[]> {
  const grouped = new Map<string, T[]>();
  for (const row of rows) {
    const id = key(row);
    const existing = grouped.get(id);
    if (existing === undefined) grouped.set(id, [row]);
    else existing.push(row);
  }
  return grouped;
}

/** One bulk detail row, tagged with the object it belongs to. */
export type OfObject<T> = T & { schema_name: string; table_name: string };

/** One object's four row sets, whichever read produced them. */
export interface ObjectDetailRows {
  readonly columns: readonly ColumnRow[];
  readonly primaryKey: readonly PrimaryKeyRow[];
  readonly foreignKeys: readonly ForeignKeyRow[];
  readonly indexes: readonly IndexRow[];
}

/**
 * The VALUE a column defaults to, read out of the catalog text (#1029). DuckDB reports a
 * default as the expression AS WRITTEN, so a string default arrives as the quoted literal
 * `'abc'`. `unquoteLiteral` decodes exactly one complete literal with this dialect's
 * escaping and answers `undefined` for anything else, which is what lets a number such as
 * `42` or an expression such as `CURRENT_TIMESTAMP` through unchanged. The text itself is
 * kept alongside as `defaultExpression`, because once decoded this is no longer something
 * that can be pasted after the word DEFAULT.
 */
function readCatalogDefault(raw: string | null | undefined): string | undefined {
  return raw === null || raw === undefined ? undefined : (unquoteLiteral(raw, "duckdb") ?? raw);
}

/**
 * ONE object's detail, from rows, for BOTH the single read and the bulk read (#789).
 *
 * One mapper and not two, because two are two chances for `describeObjects` to spell a
 * column, an index or a foreign key differently from `describeObject` over the same table,
 * and nothing downstream compares the two answers.
 *
 * `schema` is passed rather than read off the path, because the bulk read takes it from the
 * ROW: a catalog-level read spans every schema under the catalog, so the object's schema is
 * something the answer carries and not something the container said.
 */
export function objectDetailFromRows(path: readonly string[], schema: string, rows: ObjectDetailRows): ObjectDetail {
  const primaryKey = new Set(rows.primaryKey.flatMap((row) => row.constraint_column_names));

  return {
    path: [...path],
    columns: rows.columns.map((row) => ({
      name: row.column_name,
      type: row.data_type,
      nullable: row.is_nullable,
      isPrimary: primaryKey.has(row.column_name),
      // `?? undefined` rather than a conditional spread: `ColumnSchema.defaultValue` is
      // optional and an absent key and an undefined one are the same fact to every
      // consumer, so the explicit form keeps the object shape constant across rows.
      defaultValue: readCatalogDefault(row.column_default),
      defaultExpression: row.column_default ?? undefined,
    })),
    // A composite foreign key is ONE constraint over several columns and `ForeignKeySchema`
    // is per column, so the two aligned arrays are zipped out.
    foreignKeys: rows.foreignKeys.flatMap((row) =>
      row.constraint_column_names.map((columnName, index) => ({
        columnName,
        // Spelled the way `getSchema()` spells it on this engine, because
        // `ForeignKeySchema` carries one string and both surfaces are live through Phase 1:
        // bare in `main` and qualified anywhere else. DuckDB refuses a foreign key across
        // schemas outright, so the target is always in the reading object's own schema and
        // the catalog never needs naming.
        referencedTable: displayName(schema, row.referenced_table),
        referencedColumn: row.referenced_column_names[index],
      })),
    ),
    indexes: rows.indexes.map((row) => ({
      name: row.index_name,
      columns: row.index_columns ?? [],
      unique: row.is_unique,
    })),
  };
}