Skip to content

armgit / bento

Public Imported from GitHub
0

Bento, the office suite that fits in a file

51.9 KB
Raw Blame
// SPDX-License-Identifier: MIT
// Copyright (c) 2026 The Bento authors
// Pivot tables — grouped aggregation over the columnar store, and the drill-down
// back to the rows behind a number.
//
// A PIVOT IS A DOCUMENT, NOT A VIEW, and the whole file hangs off that call.
// dash has already drawn this line twice, in opposite directions:
//
//   - SORTING AND FILTERING ARE VIEW STATE (filter.ts:3, store.ts:531). They
//     belong to the reader, they must not dirty the file, and two people looking
//     at one workbook may legitimately have different rows on screen.
//   - HIDING A COLUMN IS DOCUMENT STATE (rowcol.ts:526). It is an EDITORIAL act
//     — the author decided the working column is not part of the story — and two
//     people opening one file have to see the same grid or they are reading
//     different documents.
//
// "Revenue by region by quarter" is the second kind. It is a thing somebody
// BUILT: the fields, the aggregations and the subtotal policy are an argument
// about what the data means, and an argument that evaporated when the reader
// closed the tab would not be worth making. It is also the thing a reader is
// sent — "look at the pivot on sheet 3" has to name something that exists in the
// file. So a pivot is a SHEET (`kind: 'pivot'`), it saves, it travels, and every
// reader of the file sees the same one.
//
// WHAT THE SHEET STORES IS THE SPEC AND NEVER THE NUMBERS. That is chart.ts's
// rule promoted again (chart.ts:5 — "a tile NAMES a sheet and columns"; slides
// learned it the expensive way with `syncLinkedChart`): there is no code path
// here that writes a computed cell into the document. A stored pivot could
// disagree with the sheet beside it, go stale without saying so, and would
// double the file for the privilege. A derived one cannot be wrong about its own
// data — edit a cell and the pivot moves.
//
// The reader's own filters are deliberately NOT applied by default. The pivot
// carries its own `filters` in the spec, because a document that changed shape
// with each reader's grid filter would stop being one thing everybody sees —
// exactly the property that made it a document. `runPivot`'s `rows` option is
// the opt-in for "pivot what I am looking at", and it is the caller's choice.
//
// SEVEN THINGS ARE WRONG IN EVERY NAIVE PIVOT, and every one of them prints a
// number that looks like an answer:
//
//   1. AN EMPTY GROUP THAT READS AS A ZERO. No rows for (South, Q3) is a GAP.
//      Printing 0 there says "South sold nothing in Q3", which is a claim about
//      the world made out of the absence of data. Cells carry `rows`, and a cell
//      with `rows: 0` has `v: null` and renders empty — never `0`, never `—`
//      beside a real zero.
//   2. AN AVERAGE DIVIDED BY THE ROW COUNT. chart.ts:96 already settled it: the
//      divisor is the values PRESENT, not the rows in the group. Twelve rows
//      with four measurements average over four.
//   3. TEXT COERCED TO ZERO. `Number('n/a')` is NaN, `Number('')` is 0, and the
//      convenient `|| 0` turns both into a silent deduction from the sum. This
//      is the bug filter.ts was written around (filter.ts:98) and it is worse in
//      a pivot, because the number is aggregated: nobody can see the row that
//      did it. Non-numeric values are COUNTED (`skipped`) and never summed.
//   4. SUBTOTALS SUMMED FROM CHILDREN. Right for sum and count, WRONG for
//      average (an average of averages weights small groups equally), wrong for
//      median, wrong for count-distinct (distincts do not add — the same
//      customer in two regions counts twice). Every subtotal here is computed
//      from the source rows, not from the cells above it.
//   5. ALPHABETICAL CATEGORIES. The first-seen order is the order the data is
//      in, which is usually the order that means something (a fiscal calendar
//      sorts to Apr, Aug, Dec). chart.ts:79 made this call; this file matches it.
//   6. MATERIALISING THE ROWS. The whole storage argument is 4.8 bytes/cell
//      against 15.9 for array-of-objects (model.ts:17). A pivot that starts by
//      building an array of row objects spends the format's entire advantage in
//      the first ten lines. Nothing here allocates per row: grouping runs on
//      dictionary CODES, and a dictionary-encoded numeric column is parsed once
//      per DISTINCT VALUE rather than once per row.
//   7. A DRILL-DOWN THAT DISAGREES WITH ITS OWN CELL. If the click re-derives
//      the group membership by some other route — re-parsing labels, matching
//      formatted strings — it will eventually return a different set of rows
//      than the ones that made the number. `drillDown` matches on the same
//      integer code planes the aggregation walked, so the two cannot drift.
//
// The engine half is pure and runs in node; the DOM half is at the bottom.

import './pivot.css'
import type { Column, ColumnData, Sheet, StoredPredicate, TableSheet } from './model.ts'
import { readCell } from './store.ts'
import { buildOrder, isBlank, type ColumnFilter, type Get, type Predicate } from './filter.ts'
import { formatValue } from './format.ts'
import { t } from './i18n.ts'

// --- the spec --------------------------------------------------------------

export type AggFn = 'sum' | 'avg' | 'count' | 'min' | 'max' | 'median' | 'countDistinct'

export const AGG_FNS: AggFn[] = ['sum', 'avg', 'count', 'min', 'max', 'median', 'countDistinct']

/** English labels. Localised for DISPLAY only — the stored `fn` stays a model
 *  word, which is the rule `select()` follows across the suite (PLATFORM §8). */
export const AGG_LABEL: Record<AggFn, string> = {
  sum: 'Sum', avg: 'Average', count: 'Count', min: 'Min', max: 'Max',
  median: 'Median', countDistinct: 'Distinct',
}

/**
 * One entry in the pivot's Filters area, in the form that survives JSON. `pred`
 * absent = "(All)". `pred` may also be a live filter.ts `Predicate` (a `Set`
 * checklist) for a caller that built one in memory — `liveFilters` reads both.
 */
export interface PivotFilter {
  col: string
  pred?: StoredPredicate | Predicate
  [extra: string]: unknown
}

/**
 * Stored filters → the live ones `buildOrder` evaluates.
 *
 * Entries with no predicate are the "(All)" case and drop out; a predicate op
 * this build cannot evaluate drops out too — KEPT in the document by the spread
 * in `readPivotSpec`, refused here, the additive rule `toPredicate` in story.ts
 * follows. A copy of that bridge rather than an import of it, because story.ts
 * is a DOM module and this half of the file runs in node.
 */
export function liveFilters(filters: readonly PivotFilter[] | undefined): ColumnFilter[] {
  const out: ColumnFilter[] = []
  for (const f of filters ?? []) {
    if (!isObj(f) || typeof f.col !== 'string' || !isObj(f.pred)) continue
    const p = f.pred as Record<string, unknown>
    switch (p.op) {
      case 'isOneOf': {
        const set = p.set instanceof Set ? p.set
          : new Set(Array.isArray(p.values) ? p.values : [])
        out.push({ col: f.col, pred: { op: 'isOneOf', set } })
        break
      }
      case 'equals': case 'notEquals': case 'greater': case 'less':
      case 'greaterOrEqual': case 'lessOrEqual':
        out.push({ col: f.col, pred: { op: p.op, v: p.v } as Predicate })
        break
      case 'contains': case 'notContains': case 'startsWith': case 'endsWith':
        out.push({ col: f.col, pred: { op: p.op, v: String(p.v ?? '') } as Predicate })
        break
      case 'between':
        out.push({ col: f.col, pred: { op: 'between', lo: p.lo, hi: p.hi } })
        break
      case 'isBlank': case 'notBlank':
        out.push({ col: f.col, pred: { op: p.op } })
        break
      case 'topN': case 'bottomN': {
        const n = Number(p.n)
        if (Number.isFinite(n)) out.push({ col: f.col, pred: { op: p.op, n } })
        break
      }
      default:
        break
    }
  }
  return out
}

export interface PivotValue {
  /** source column id */
  col: string
  fn: AggFn
  /** display name override; absent means "Sum of Revenue" is generated */
  as?: string
  [extra: string]: unknown
}

/**
 * What the author built. This is the whole of what a pivot sheet stores.
 *
 * Fields are COLUMN IDS, never names and never positions — the same identity
 * every formula, override and tile keys off (model.ts:61), so renaming a column
 * or dragging it somewhere else never breaks a pivot.
 */
export interface PivotSpec {
  /** id of the source TABLE sheet */
  from: string
  /** row fields, outermost first */
  rows: string[]
  /** column fields, outermost first */
  cols: string[]
  values: PivotValue[]
  /**
   * The pivot's OWN filters — document state, unlike the grid's (filter.ts:3).
   *
   * STORED IN THE JSON SPELLING (`PivotFilter`): a checklist is `values`, an
   * array, because filter.ts's live `Set` serializes as `{}` and would come back
   * from the file matching NOTHING — model.ts's `StoredFilter` rule, and the
   * spelling a story step already uses. A live `ColumnFilter` is accepted too,
   * so a caller holding one need not convert it.
   *
   * An entry with NO `pred` is a field sitting in the editor's Filters area with
   * every value allowed — Excel's "(All)". It filters nothing, and it is kept so
   * the field stays where the author put it.
   */
  filters?: PivotFilter[]
  /** default true */
  subtotals?: boolean
  /** default true */
  grandTotals?: boolean
  /**
   * Include rows whose grouping field is blank, as a "(blank)" group. Default
   * true, and the default matters: switching it off DROPS THE ROW ENTIRELY
   * rather than merely hiding its group, because a grand total that no longer
   * equals the sum of what is on screen is a worse lie than a "(blank)" label.
   */
  blanks?: boolean
  [extra: string]: unknown
}

/**
 * A pivot sheet.
 *
 * NOT YET A MEMBER OF `model.ts`'s `Sheet` UNION, and until it is, `parseDoc`
 * rewrites `kind` to `'table'` for every sheet that is not `'canvas'`
 * (model.ts:529) — so a saved pivot sheet comes back from a round trip
 * kind-coerced, with empty `rids`/`columns`/`data` bolted on. That is why
 * `isPivotSheet` below keys off the PRESENCE OF A WELL-FORMED `pivot` FIELD and
 * not off `kind`: unknown top-level sheet fields survive the parse (the spread
 * at model.ts:530 is the additivity promise of PLATFORM §3), so the spec makes
 * it through intact and the pivot still opens. When model.ts adopts the kind,
 * nothing here changes.
 */
export interface PivotSheet {
  id: string
  name: string
  kind: 'pivot'
  pivot: PivotSpec
  [extra: string]: unknown
}

const isObj = (v: unknown): v is Record<string, unknown> =>
  typeof v === 'object' && v !== null && !Array.isArray(v)

const strings = (v: unknown): string[] =>
  Array.isArray(v) ? v.filter((x): x is string => typeof x === 'string') : []

/**
 * Read a spec out of whatever is actually in the file, defensively — the same
 * posture `readFrozen` takes (rowcol.ts:489). This is an additive field, so an
 * older build, a hand edit or a newer one can put anything there, and anything
 * unreadable has to mean "not a pivot" rather than throwing on open.
 *
 * `from` is required and the field lists are not: a pivot with no rows, no
 * columns and one value is the legitimate degenerate case (one grand total),
 * and it must open.
 */
export function readPivotSpec(sheet: unknown): PivotSpec | null {
  if (!isObj(sheet)) return null
  const p = sheet.pivot
  if (!isObj(p)) return null
  if (typeof p.from !== 'string' || !p.from) return null
  const values = Array.isArray(p.values)
    ? p.values.filter(isObj)
      .filter((v) => typeof v.col === 'string')
      .map((v) => ({
        ...v,
        col: v.col as string,
        // an unrecognised fn is NOT silently a sum: it is preserved for a build
        // that knows it, and `runPivot` reports it rather than answering
        fn: v.fn as AggFn,
      }))
    : []
  return {
    ...p,
    from: p.from,
    rows: strings(p.rows),
    cols: strings(p.cols),
    values,
  }
}

export function isPivotSheet(s: Sheet | PivotSheet | unknown): s is PivotSheet {
  return readPivotSpec(s) !== null
}

// --- planes ----------------------------------------------------------------
//
// A "plane" is one column, prepared for one job, WITHOUT ever building a row
// object. Two jobs, two shapes:
//
//   GROUPING wants an integer per row, because grouping is a tree walk keyed by
//   that integer. For a dictionary column the integer already exists — `idx` IS
//   the code vector — so the whole preparation is an O(distinct-values) pass to
//   fold codes that mean the same thing onto one representative. Zero string
//   work per row, which is the point of the encoding.
//
//   AGGREGATING wants a number per row, and here the dictionary pays twice: a
//   dict column of 100k rows over 40 distinct values is parsed FORTY TIMES, not
//   a hundred thousand. That parse is the expensive part (`asNumber` runs a
//   regex), so this is the difference between a pivot that lands in a frame and
//   one that does not.

/** Blank has no code of its own in the source encoding, so it gets one here. */
const BLANK = -1

interface GroupPlane {
  /** code per row; BLANK for an empty cell */
  code: Int32Array
  /** the value as it appears in the column, for labelling */
  orig: (row: number) => unknown
}

interface ValuePlane {
  /** the number in this cell, or null when there is not one */
  num: (row: number) => number | null
  /** is there anything here at all? (`count` counts these) */
  present: (row: number) => boolean
  /** canonical identity for `countDistinct`; null when blank */
  key: (row: number) => string | null
}

/**
 * The digit shape a string must have to be READ as a number. Copied verbatim
 * from filter.ts:112 rather than imported, because filter.ts does not export it
 * — and copied rather than loosened, which matters: `Number('')` is 0 (a blank
 * becomes a zero), `Number('2026-01-15')` is NaN (an ISO date stays text, which
 * is what makes it sort chronologically), and "50%" is refused because a percent
 * column stores the FRACTION (import.ts:281), so reading it as 50 would be wrong
 * by a factor of a hundred in every cell.
 *
 * ONE DELIBERATE DIVERGENCE: a boolean reads as 1/0 here and as null there. A
 * bool column IS a measure in a pivot — "how many are flagged" is the obvious
 * question to ask of one — and `count` cannot answer it, because `false` is a
 * value that is present. filter.ts has no such use (it compares, it does not
 * total) and its `rankable` already treats booleans as rankable, so the two are
 * consistent in spirit. Every OTHER rule below is filter.ts's, unchanged.
 *
 * IF filter.ts EVER EXPORTS `asNumber`, TAKE IT AND KEEP ONLY THE BOOL CASE.
 * Two copies of one rule is how a status bar and a pivot come to disagree about
 * what a number is.
 */
const NUMERIC = /^[+-]?(?:(?:\d{1,3}(?:,\d{3})+|\d+)(?:\.\d+)?|\.\d+)$/

function asNumber(v: unknown): number | null {
  if (typeof v === 'number') return Number.isFinite(v) ? v : null
  if (typeof v === 'boolean') return v ? 1 : 0
  if (typeof v !== 'string') return null
  const s = v.trim()
  if (s === '' || !NUMERIC.test(s)) return null
  const n = Number(s.replace(/,/g, ''))
  return Number.isFinite(n) ? n : null
}

/**
 * The one canonical key, matching filter.ts:146. Two values collapse into one
 * pivot group exactly when they would collapse into one checkbox in the filter
 * menu — so a reader who ticks "North" in the filter and sees "North" in the
 * pivot is looking at the same set of rows both times. Numbers canonicalise
 * through their value (10, "10" and " 10.0 " are one group); text
 * canonicalises case- and whitespace-insensitively.
 */
function matchKey(v: unknown): string {
  if (isBlank(v)) return ' blank'
  const n = asNumber(v)
  return n === null ? `t:${String(v).trim().toLowerCase()}` : `n:${n}`
}

/**
 * `pack` is an opaque archive encoding and `readCell` answers null for every row
 * of one (store.ts:196). Pivoting through that would silently produce ONE
 * "(blank)" group holding the whole sheet and a grand total of nothing — a
 * wrong answer with no symptom. rowcol.ts refuses packed columns for structural
 * edits for the same reason; this refuses them for reads.
 */
function refusePacked(d: ColumnData | undefined, col: string): void {
  if (d && d.enc === 'pack') {
    throw new Error(`column "${col}" is packed and cannot be pivoted — promote it to raw first`)
  }
}

function groupPlane(d: ColumnData | undefined, computed: unknown[] | undefined, n: number, col: string): GroupPlane {
  const code = new Int32Array(n)
  if (!computed && d && d.enc === 'dict') {
    refusePacked(d, col)
    // O(distinct), not O(rows): fold every dictionary entry onto the first entry
    // sharing its canonical key, so the row loop below is an array index and a
    // null test. This is the entire reason the format is dictionary-encoded.
    const rep = new Int32Array(d.dict.length)
    const seen = new Map<string, number>()
    for (let k = 0; k < d.dict.length; k++) {
      const s = d.dict[k]
      if (isBlank(s)) { rep[k] = BLANK; continue }
      const mk = matchKey(s)
      const first = seen.get(mk)
      if (first === undefined) { seen.set(mk, k); rep[k] = k } else rep[k] = first
    }
    for (let i = 0; i < n; i++) {
      const k = d.idx[i]
      code[i] = k == null ? BLANK : (rep[k] ?? BLANK)
    }
    return { code, orig: (row) => readCell(d, row) }
  }

  refusePacked(d, col)
  // `raw` and computed columns have no dictionary to lean on, so this interns
  // one — the same cost `buildOrder`'s column cache already pays per sort.
  const read = computed
    ? (row: number) => computed[row] ?? null
    : (row: number) => readCell(d, row)
  const seen = new Map<string, number>()
  for (let i = 0; i < n; i++) {
    const v = read(i)
    if (isBlank(v)) { code[i] = BLANK; continue }
    const mk = matchKey(v)
    let c = seen.get(mk)
    if (c === undefined) { c = seen.size; seen.set(mk, c) }
    code[i] = c
  }
  return { code, orig: read }
}

function valuePlane(d: ColumnData | undefined, computed: unknown[] | undefined, col: string): ValuePlane {
  if (!computed && d && d.enc === 'dict') {
    refusePacked(d, col)
    const nums = d.dict.map(asNumber)
    const pres = d.dict.map((s) => !isBlank(s))
    const keys = d.dict.map((s) => (isBlank(s) ? null : matchKey(s)))
    const at = (row: number): number | null => {
      const k = d.idx[row]
      return k == null ? null : k
    }
    return {
      num: (row) => { const k = at(row); return k == null ? null : (nums[k] ?? null) },
      present: (row) => { const k = at(row); return k == null ? false : (pres[k] ?? false) },
      key: (row) => { const k = at(row); return k == null ? null : (keys[k] ?? null) },
    }
  }
  refusePacked(d, col)
  const read = computed
    ? (row: number) => computed[row] ?? null
    : (row: number) => readCell(d, row)
  return {
    num: (row) => asNumber(read(row)),
    present: (row) => !isBlank(read(row)),
    key: (row) => { const v = read(row); return isBlank(v) ? null : matchKey(v) },
  }
}

// --- accumulators ----------------------------------------------------------

interface Acc {
  /** non-blank values seen — what `count` reports */
  present: number
  /** how many of those were numbers — the DIVISOR of `avg` (chart.ts:96) */
  numeric: number
  /** present values that are not numbers. Recorded, never coerced to zero. */
  skipped: number
  sum: number
  min: number | null
  max: number | null
  /** median is not a streaming aggregate; allocated only when one is asked for */
  vals: number[] | null
  /** distinct is not a streaming aggregate either */
  keys: Set<string> | null
}

/** A (row group × column group) intersection. ABSENT means the pair never
 *  occurred, which is a gap — the reason the map is sparse rather than a dense
 *  matrix filled with zeroes. */
interface Bucket {
  rows: number
  accs: Acc[]
}

const newAcc = (fn: AggFn): Acc => ({
  present: 0, numeric: 0, skipped: 0, sum: 0, min: null, max: null,
  vals: fn === 'median' ? [] : null,
  keys: fn === 'countDistinct' ? new Set<string>() : null,
})

interface Node {
  id: number
  depth: number
  code: number
  label: unknown
  kids: Map<number, Node>
  /** first-seen order, which is the order the data is in (chart.ts:79) */
  order: Node[]
  parent: Node | null
  /** column-node id → bucket. Only ROW nodes use this. */
  cells: Map<number, Bucket>
}

// --- the result ------------------------------------------------------------

export interface PivotCell {
  /**
   * NULL IS NOT ZERO. `rows === 0` means the combination does not occur — a gap.
   * `rows > 0` with `v === null` means the rows are there and hold nothing this
   * function can answer (a text column asked for a sum). Both render empty; a
   * real zero renders "0".
   */
  v: number | null
  /** source rows behind this cell — the number the drill-down will show */
  rows: number
  /** non-blank values among them */
  present: number
  /** how many of those were numbers */
  numeric: number
  /** present values that could not be read as numbers, and were NOT summed */
  skipped: number
}

export type GroupKind = 'leaf' | 'subtotal' | 'grand'

export interface PivotGroup {
  /** one code per level pinned, root-relative. Shorter than the field list for
   *  a subtotal; empty for the grand total. */
  codes: number[]
  /** first-seen ORIGINAL values, one per pinned level — what the reader sees */
  path: unknown[]
  kind: GroupKind
}

export interface PivotColumn extends PivotGroup {
  value: PivotValue
  /** index into `spec.values` */
  vi: number
}

export interface PivotRow extends PivotGroup {
  /** one per entry in `columns`, same order */
  cells: PivotCell[]
}

export interface PivotHeaderCell {
  label: unknown
  span: number
  kind: GroupKind
  /** true for the generated "… total" caption above a subtotal column */
  total: boolean
}

/**
 * Something the reader has to be told, rather than silently absorbed. The model
 * takes this line everywhere (a mistyped value is `Column.failed`, an unknown
 * step marks its descendants unresolved) and the reason is always the same: a
 * pivot that quietly dropped a field still prints a full set of numbers.
 */
export interface PivotFinding {
  kind: 'missing-column' | 'unknown-agg' | 'no-values' | 'empty-source'
  detail: string
}

export interface PivotResult {
  spec: PivotSpec
  /** the fields that SURVIVED — a field naming a deleted column is dropped and
   *  reported, never grouped as one big blank */
  rowFields: string[]
  colFields: string[]
  values: PivotValue[]
  /** one row per column FIELD, plus a value-name row when it is needed */
  header: PivotHeaderCell[][]
  columns: PivotColumn[]
  rows: PivotRow[]
  /**
   * Source rows that actually reached an accumulator — the denominator of
   * everything on screen, and NOT simply the count the filters admitted. With
   * `blanks: false` those two differ, and reporting the larger one would put a
   * footer under the table that disagrees with the grand total directly above
   * it. Invariant: with any row field, this equals the grand-total cell's `rows`.
   */
  scanned: number
  findings: PivotFinding[]
  /** retained so a drill-down cannot disagree with the number it explains */
  source: PivotScan
}

/** The prepared source, kept on the result so `drillDown` re-uses the very code
 *  planes the aggregation walked instead of re-deriving membership some other
 *  way (see failure 7 in the header). */
export interface PivotScan {
  sheetId: string
  /** the source row indices the filters admitted, in sheet order */
  rows: number[]
  rowPlanes: GroupPlane[]
  colPlanes: GroupPlane[]
}

// --- the engine ------------------------------------------------------------

export interface PivotOpts {
  /** formula columns the grid just recomputed, as chart.ts takes them: a pivot
   *  over a derived column must show the numbers on screen, not the empty
   *  storage underneath them */
  computed?: Map<string, unknown[]>
  /**
   * Restrict to these source row indices — the grid's order vector, if the
   * caller wants "pivot what I am looking at". OFF by default and deliberately:
   * a document whose numbers changed with each reader's grid filter would stop
   * being one thing everyone sees, which is the property that made it a
   * document in the first place (see the header).
   */
  rows?: number[]
}

const rowsOf = (sheet: TableSheet): number => sheet.rids.reduce((n, [, c]) => n + c, 0)

const KNOWN_AGG = new Set<string>(AGG_FNS)

/**
 * Run a pivot.
 *
 * Cost is one pass over the admitted rows, touching (rowDepth + 1) ×
 * (colDepth + 1) buckets each — nine map operations per row for the common
 * two-by-two pivot. No row object is ever built.
 *
 * Measured: 200,000 rows, two row fields × one column field × two value fields
 * (dictionary-encoded groups, raw measure with 2% text in it) — 74 ms to build,
 * 18 MB heap, 2 ms to drill 2,062 rows out of a grand-total cell. A click, not
 * a frame, which is the trade filter.ts:352 already made for sorting.
 */
export function runPivot(sheet: TableSheet, spec: PivotSpec, opts: PivotOpts = {}): PivotResult {
  const n = rowsOf(sheet)
  const findings: PivotFinding[] = []
  const has = (id: string): boolean =>
    sheet.columns.some((c) => c.id === id) || opts.computed?.has(id) === true
  const nameOf = (id: string): string => sheet.columns.find((c) => c.id === id)?.name ?? id

  // A field naming a column that is gone is DROPPED and reported. Keeping it
  // would group every row under one "(blank)", which looks like a pivot and is
  // a pivot of nothing; grouping silently by the remaining fields would change
  // every number on screen without saying so.
  const keep = (ids: string[], where: string): string[] => ids.filter((id) => {
    if (has(id)) return true
    findings.push({
      kind: 'missing-column',
      detail: `the ${where} field "${id}" names a column this sheet no longer has, so it is not being grouped by`,
    })
    return false
  })

  const rowFields = keep(spec.rows, 'row')
  const colFields = keep(spec.cols, 'column')
  const values = spec.values.filter((v) => {
    if (!has(v.col)) {
      findings.push({
        kind: 'missing-column',
        detail: `the value "${v.col}" names a column this sheet no longer has`,
      })
      return false
    }
    if (!KNOWN_AGG.has(v.fn)) {
      // Preserved in the document (readPivotSpec spreads it through) and refused
      // HERE, because inventing an answer for a function this build cannot
      // perform is the "unresolved" failure model.ts:174 exists to prevent.
      findings.push({
        kind: 'unknown-agg',
        detail: `"${String(v.fn)}" is not an aggregation this build knows, so ${nameOf(v.col)} is not summarised`,
      })
      return false
    }
    return true
  })
  if (!values.length) {
    findings.push({ kind: 'no-values', detail: 'this pivot has nothing to summarise yet' })
  }
  if (!n) findings.push({ kind: 'empty-source', detail: 'the source sheet has no rows' })

  const col = (id: string): ColumnData | undefined => sheet.data[id]
  const comp = (id: string): unknown[] | undefined => opts.computed?.get(id)

  const rowPlanes = rowFields.map((id) => groupPlane(col(id), comp(id), n, id))
  const colPlanes = colFields.map((id) => groupPlane(col(id), comp(id), n, id))
  const valPlanes = values.map((v) => valuePlane(col(v.col), comp(v.col), v.col))

  // The pivot's OWN filters, through filter.ts — one implementation of "less
  // than 10", not two. `topN` works here too, and ranks the whole source.
  let rows: number[]
  // Filters on a column that is gone are dropped and REPORTED, like a row or a
  // column field: filtering on nothing would admit no rows at all, and a pivot
  // of nothing beside a sentence that does not say why is the empty-axis lie.
  const filters = liveFilters(spec.filters).filter((f) => {
    if (has(f.col)) return true
    findings.push({
      kind: 'missing-column',
      detail: `the filter field "${f.col}" names a column this sheet no longer has, so it is not filtering`,
    })
    return false
  })
  if (filters.length) {
    const get: Get = (c, row) => {
      const cv = comp(c)
      return cv ? cv[row] ?? null : readCell(col(c), row)
    }
    rows = buildOrder(n, get, filters, [])
  } else {
    rows = Array.from({ length: n }, (_, i) => i)
  }
  if (opts.rows) {
    const allowed = new Set(opts.rows)
    rows = rows.filter((r) => allowed.has(r))
  }

  let nextId = 0
  const mkNode = (depth: number, code: number, label: unknown, parent: Node | null): Node => ({
    id: nextId++, depth, code, label, kids: new Map(), order: [], parent, cells: new Map(),
  })
  const rowRoot = mkNode(0, BLANK, null, null)
  const colRoot = mkNode(0, BLANK, null, null)

  const descend = (node: Node, code: number, label: unknown): Node => {
    let k = node.kids.get(code)
    if (!k) {
      // The label is captured HERE, at first sight, so it is the original as it
      // appears in the first row of the group — matching the order the groups
      // come out in. Reading it off the dictionary instead would show whichever
      // spelling happened to be interned first, which is not always the same.
      k = mkNode(node.depth + 1, code, label, node)
      node.kids.set(code, k)
      node.order.push(k)
    }
    return k
  }

  const includeBlanks = spec.blanks !== false
  const rowChain: Node[] = new Array(rowFields.length + 1)
  const colChain: Node[] = new Array(colFields.length + 1)
  let scanned = 0

  scan: for (const r of rows) {
    // EXCLUDING BLANKS EXCLUDES THE ROW, not merely its group. Hiding the group
    // while still feeding its rows into the totals leaves a grand total that
    // does not equal the sum of what is on screen, and a reader who adds the
    // column up by hand is right and the pivot is wrong.
    if (!includeBlanks) {
      for (const p of rowPlanes) if (p.code[r] === BLANK) continue scan
      for (const p of colPlanes) if (p.code[r] === BLANK) continue scan
    }
    scanned++
    rowChain[0] = rowRoot
    for (let l = 0; l < rowPlanes.length; l++) {
      const c = rowPlanes[l].code[r]
      rowChain[l + 1] = descend(rowChain[l], c, c === BLANK ? null : rowPlanes[l].orig(r))
    }
    colChain[0] = colRoot
    for (let l = 0; l < colPlanes.length; l++) {
      const c = colPlanes[l].code[r]
      colChain[l + 1] = descend(colChain[l], c, c === BLANK ? null : colPlanes[l].orig(r))
    }

    // EVERY ancestor pair, not just the leaf pair. This is what makes subtotals
    // computed rather than summed (failure 4): the (region, ·) bucket sees the
    // same rows the leaves do, so its average divides by its own values and its
    // distinct count is a distinct count.
    for (const rn of rowChain) {
      for (const cn of colChain) {
        let b = rn.cells.get(cn.id)
        if (!b) { b = { rows: 0, accs: values.map((v) => newAcc(v.fn)) }; rn.cells.set(cn.id, b) }
        b.rows++
        for (let vi = 0; vi < valPlanes.length; vi++) {
          const p = valPlanes[vi]
          if (!p.present(r)) continue
          const a = b.accs[vi]
          a.present++
          if (a.keys) { const k = p.key(r); if (k !== null) a.keys.add(k) }
          const num = p.num(r)
          if (num === null) { a.skipped++; continue }
          a.numeric++
          a.sum += num
          if (a.min === null || num < a.min) a.min = num
          if (a.max === null || num > a.max) a.max = num
          if (a.vals) a.vals.push(num)
        }
      }
    }
  }

  const subtotals = spec.subtotals !== false
  const grand = spec.grandTotals !== false
  const rowGroups = flatten(rowRoot, rowFields.length, subtotals, grand)
  const colGroups = flatten(colRoot, colFields.length, subtotals, grand)

  const columns: PivotColumn[] = []
  const colNodes: Node[] = []
  for (const g of colGroups) {
    for (let vi = 0; vi < values.length; vi++) {
      columns.push({ codes: g.codes, path: g.path, kind: g.kind, value: values[vi], vi })
      colNodes.push(g.node)
    }
  }

  const out: PivotRow[] = rowGroups.map((g) => ({
    codes: g.codes,
    path: g.path,
    kind: g.kind,
    cells: columns.map((c, i) => finish(g.node.cells.get(colNodes[i].id), c.value.fn, c.vi)),
  }))

  return {
    spec,
    rowFields,
    colFields,
    values,
    header: buildHeader(columns, colFields.length, values, nameOf),
    columns,
    rows: out,
    scanned,
    findings,
    source: { sheetId: sheet.id, rows, rowPlanes, colPlanes },
  }
}

interface FlatGroup extends PivotGroup { node: Node }

/**
 * Depth-first, children before their own subtotal, grand total last — Excel's
 * outline order, and the only one where a subtotal reads as summarising the
 * lines above it.
 *
 * A node at full depth is a LEAF even if `depth` is 0: a pivot with no column
 * fields has exactly one column group, which is the root, and it is a leaf
 * rather than a grand total. Emitting a grand total there as well would print
 * the same numbers twice under two different captions.
 */
function flatten(root: Node, depth: number, subtotals: boolean, grand: boolean): FlatGroup[] {
  const out: FlatGroup[] = []
  const codes: number[] = []
  const path: unknown[] = []
  const walk = (node: Node): void => {
    if (node.depth === depth) {
      out.push({ codes: codes.slice(), path: path.slice(), kind: 'leaf', node })
      return
    }
    for (const k of node.order) {
      codes.push(k.code)
      path.push(k.label)
      walk(k)
      codes.pop()
      path.pop()
    }
    // A node with one child gets a subtotal identical to that child's row. Excel
    // prints it anyway and so does this: suppressing it makes the outline depth
    // depend on the data, so two regions of the same report indent differently.
    if (node.depth > 0 && subtotals) {
      out.push({ codes: codes.slice(), path: path.slice(), kind: 'subtotal', node })
    }
  }
  walk(root)
  if (depth > 0 && grand) out.push({ codes: [], path: [], kind: 'grand', node: root })
  return out
}

/** Shared and FROZEN: it is returned by reference for every gap in the table,
 *  so a caller that decorated it in place would decorate all of them. */
const EMPTY_CELL: PivotCell = Object.freeze({ v: null, rows: 0, present: 0, numeric: 0, skipped: 0 })

/**
 * One accumulator into one cell.
 *
 * `count` and `countDistinct` return 0 when the group HAS rows and none of them
 * carry a value — that zero is an answer ("nothing was recorded here"), and it
 * is distinguishable from the gap because the gap has `rows: 0` and no bucket at
 * all. Every other function returns null instead of 0, for filter.ts's reason
 * (filter.ts:498): a sum of nothing printed as `0` reads as "these add up to
 * nothing", `Math.min()` of an empty list is Infinity, and 0/0 is NaN.
 */
function finish(b: Bucket | undefined, fn: AggFn, vi: number): PivotCell {
  if (!b) return EMPTY_CELL
  const a = b.accs[vi]
  const base = { rows: b.rows, present: a.present, numeric: a.numeric, skipped: a.skipped }
  switch (fn) {
    case 'count': return { ...base, v: a.present }
    case 'countDistinct': return { ...base, v: a.keys ? a.keys.size : 0 }
    case 'sum': return { ...base, v: a.numeric ? a.sum : null }
    case 'avg': return { ...base, v: a.numeric ? a.sum / a.numeric : null }
    case 'min': return { ...base, v: a.min }
    case 'max': return { ...base, v: a.max }
    case 'median': return { ...base, v: a.numeric && a.vals ? median(a.vals) : null }
  }
}

/** Numeric sort, then the middle — `Array.sort`'s default is LEXICAL, which
 *  would put 100 before 9 and hand back a median that is not one. */
function median(vals: number[]): number {
  const s = vals.slice().sort((x, y) => x - y)
  const m = s.length >> 1
  return s.length % 2 ? s[m] : (s[m - 1] + s[m]) / 2
}

/**
 * The column header, one row per column field plus a value row.
 *
 * The value row exists when there is more than one value field — or when there
 * are no column fields at all, because otherwise the table would have a header
 * with nothing in it and the reader could not tell a sum from a count.
 */
function buildHeader(
  columns: PivotColumn[], depth: number, values: PivotValue[], nameOf: (id: string) => string,
): PivotHeaderCell[][] {
  const out: PivotHeaderCell[][] = []
  for (let l = 0; l < depth; l++) {
    const row: PivotHeaderCell[] = []
    let key: string | null = null
    for (const c of columns) {
      // A subtotal column pins fewer levels than the header is deep, so past
      // its own depth it carries the "… total" caption. Its identity at level
      // `l` is just its pinned prefix — a subtotal DOES belong under its
      // parent's caption at the levels above it, and it can never merge with a
      // sibling leaf at the level where the two differ, because one prefix is
      // then a strict prefix of the other. An earlier draft also appended a
      // total marker here; it turned out to change no output for any shape,
      // and dead defence that nothing can distinguish is how a file rots.
      //
      // SEPARATED, not concatenated: codes are integers, so `[1,2]` and `[12]`
      // join to the same string without a separator, and two unrelated groups
      // sharing a header key would merge into one cell whose colspan then lies
      // about what is under it. Codes are handed out in first-seen order, so
      // adjacent groups get adjacent numbers and the collision is probably not
      // reachable today — but this is a comma, and the alternative is re-running
      // that reachability argument every time the coding changes.
      const k = c.codes.slice(0, Math.min(l + 1, c.codes.length)).join(',')
      if (k === key && row.length) { row[row.length - 1].span++; continue }
      key = k
      const total = l >= c.codes.length
      row.push({
        label: total
          ? (c.kind === 'grand' ? t('Grand total') : totalCaption(c.path))
          : c.path[l],
        span: 1,
        kind: c.kind,
        total,
      })
    }
    out.push(row)
  }
  if (values.length > 1 || depth === 0) {
    out.push(columns.map((c) => ({
      label: valueName(c.value, nameOf),
      span: 1,
      kind: c.kind,
      total: false,
    })))
  }
  return out
}

const totalCaption = (path: unknown[]): string =>
  t('{name} total').replace('{name}', label(path[path.length - 1]))

/** The caption for a value field. Generated rather than stored, so renaming the
 *  source column renames the pivot's header with it. */
export const valueName = (v: PivotValue, nameOf: (id: string) => string): string =>
  v.as ?? `${t(AGG_LABEL[v.fn] ?? String(v.fn))} ${t('of')} ${nameOf(v.col)}`

/** A group label for display. A blank group is "(blank)" and NOT an empty
 *  string: an unlabelled row in the middle of an outline reads as a rendering
 *  bug, and the reader cannot tell it from the subtotal above it. */
export const label = (v: unknown): string =>
  isBlank(v) ? t('(blank)') : String(v)

// --- drill-down ------------------------------------------------------------

export interface Drill {
  /** source row indices, in sheet order */
  rows: number[]
  /** how many there are in total, when `rows` was capped */
  total: number
  truncated: boolean
}

/**
 * The rows behind one number.
 *
 * This is the thing dash can do that Excel cannot do honestly: the source rows
 * are in the same file, so "why is this 4,812?" is a click rather than an export
 * and a VLOOKUP. Excel's own drill-down materialises a COPY of the rows onto a
 * new sheet — a snapshot that is wrong the moment anything upstream changes, and
 * that quietly doubles the workbook. This returns indices into the live sheet.
 *
 * MATCHED ON THE CODE PLANES, not on labels and not on formatted text. Anything
 * that re-derives group membership by a second route will eventually disagree
 * with the aggregation that produced the number — the rig checks that summing a
 * drill-down reproduces its own cell, which is the check that would catch it.
 *
 * O(admitted rows) per click, with no state kept per cell. At 100k rows that is
 * a few milliseconds: a click, not a frame — the same trade filter.ts:352 makes.
 */
export function drillDown(
  res: PivotResult, rowIndex: number, colIndex: number, cap = 500,
): Drill {
  const r = res.rows[rowIndex]
  const c = res.columns[colIndex]
  if (!r || !c) return { rows: [], total: 0, truncated: false }
  const { rows, rowPlanes, colPlanes } = res.source
  const includeBlanks = res.spec.blanks !== false
  const out: number[] = []
  let total = 0
  scan: for (const i of rows) {
    if (!includeBlanks) {
      for (const p of rowPlanes) if (p.code[i] === BLANK) continue scan
      for (const p of colPlanes) if (p.code[i] === BLANK) continue scan
    }
    for (let l = 0; l < r.codes.length; l++) if (rowPlanes[l].code[i] !== r.codes[l]) continue scan
    for (let l = 0; l < c.codes.length; l++) if (colPlanes[l].code[i] !== c.codes[l]) continue scan
    total++
    if (out.length < cap) out.push(i)
  }
  return { rows: out, total, truncated: total > out.length }
}

// --- authoring -------------------------------------------------------------

/**
 * A first pivot that takes no configuration, the way `defaultBinding`
 * (chart.ts:154) gives the first chart one. The first low-cardinality text
 * column becomes the rows, the second becomes the columns, and the first
 * magnitude column is summed.
 *
 * Returns null rather than an empty pivot when the sheet cannot support one:
 * an empty pivot is indistinguishable from a broken one.
 */
export function defaultPivot(sheet: TableSheet): PivotSpec | null {
  const n = rowsOf(sheet)
  const cat = sheet.columns.filter((c) => c.type === 'text' || c.type === 'enum' || c.type === 'bool')
  // A near-unique column (an id, a note) makes one group per row, which is the
  // source table with extra lines. Cardinality is measured off the DICTIONARY
  // where there is one — free, and the reason the encoding is worth having.
  const wide = (c: Column): boolean => {
    const d = sheet.data[c.id]
    const distinct = d && d.enc === 'dict' ? d.dict.length : n
    // The floor keeps a small sheet groupable at all — at eight rows, eight
    // groups is still a table you can read, and refusing it would leave a new
    // workbook with no default pivot for no reason a user could see.
    return n >= 8 && distinct > Math.max(8, n / 2)
  }
  const usable = cat.filter((c) => !wide(c))
  const nums = sheet.columns.filter((c) => c.type === 'number' || c.type === 'money' || c.type === 'percent')
  if (!usable.length || !nums.length) return null
  return {
    from: sheet.id,
    rows: [usable[0].id],
    cols: usable[1] ? [usable[1].id] : [],
    values: [{ col: nums[0].id, fn: 'sum' }],
  }
}

/**
 * Mint a pivot sheet.
 *
 * NOT A PATCH FACTORY, unlike every other authoring helper in this codebase
 * (rowcol.ts:5), and not by choice: `Patch` reaches cells, columns, sheet props
 * and `doc.title` — never the sheet LIST (store.ts:55). panels.ts hit the same
 * wall and routes `addSheet` through `replaceDoc`, which clears the undo stack.
 * This returns the object; the call site pushes it and calls `replaceDoc`, and
 * adding a pivot is therefore not undoable. See the report — an `addSheet` patch
 * op is the hook this file wants.
 */
export function newPivotSheet(id: string, name: string, spec: PivotSpec): PivotSheet {
  return { id, name, kind: 'pivot', pivot: spec }
}

// --- rendering -------------------------------------------------------------
//
// One table, drawn whole. There is no virtualisation here and that is a
// deliberate limit rather than an oversight: a pivot with more rows than fit on
// screen has failed as a pivot — it is a summary, and a summary you have to
// scroll for a minute is the source table wearing a hat. The renderer caps and
// SAYS SO instead of quietly drawing the first thousand.

/** Past this a pivot has stopped summarising. Rendered, then capped with a note. */
export const PIVOT_ROW_CAP = 2000

export interface PivotViewOpts {
  /** the source sheet, for column names, number formats and the drill-down table */
  sheet: TableSheet
  /** formula columns, as `runPivot` takes them */
  computed?: Map<string, unknown[]>
  /** clicked a cell — the host may take over instead of the built-in drawer */
  onDrill?: (drill: Drill, row: PivotRow, col: PivotColumn) => void
}

const el = <K extends keyof HTMLElementTagNameMap>(
  tag: K, cls?: string, text?: string,
): HTMLElementTagNameMap[K] => {
  const e = document.createElement(tag)
  if (cls) e.className = cls
  if (text !== undefined) e.textContent = text
  return e
}

/**
 * Format an aggregated number.
 *
 * A COUNT IS NOT MONEY. Counting a currency column and printing "$14.00" for
 * fourteen rows is the kind of wrong that a reader believes, so counts take a
 * plain integer format and everything else inherits the source column's — an
 * average of a money column is money, a sum of a percent column is a percent.
 */
function formatAgg(v: number | null, fn: AggFn, source: Column | undefined): string {
  if (v === null) return ''
  if (fn === 'count' || fn === 'countDistinct') {
    return new Intl.NumberFormat(undefined, { maximumFractionDigits: 0 }).format(v)
  }
  return formatValue(v, source ?? { type: 'number' })
}

/**
 * Draw a pivot into a host element. Returns a teardown, as `renderChart` does.
 *
 * Re-renders whole. The tables here are small by construction (see the cap
 * above), and a diffing renderer would be a lot of machinery guarding a repaint
 * that costs under a millisecond.
 */
export function mountPivot(host: HTMLElement, res: PivotResult, opts: PivotViewOpts): () => void {
  const { sheet } = opts
  const colOf = (id: string): Column | undefined => sheet.columns.find((c) => c.id === id)
  const nameOf = (id: string): string => colOf(id)?.name ?? id

  host.textContent = ''
  const wrap = el('div', 'dxp')

  if (res.findings.length) {
    const box = el('div', 'dxp-findings')
    for (const f of res.findings) box.append(el('div', 'dxp-finding', f.detail))
    wrap.append(box)
  }

  if (!res.values.length) {
    wrap.append(el('p', 'dxp-empty', t('Add a value field to summarise.')))
    host.append(wrap)
    return () => { host.textContent = '' }
  }

  const scroll = el('div', 'dxp-scroll')
  const table = el('table', 'dxp-table')
  const thead = el('thead')
  const rowSpan = res.header.length || 1

  // The row-field captions sit in the top-left corner block, spanning every
  // header row — the one place in the table where the ROW axis gets named at
  // all. Without it a two-level pivot is two columns of words with no clue
  // which is which.
  const corner = el('th', 'dxp-corner')
  corner.rowSpan = rowSpan
  corner.colSpan = Math.max(1, res.rowFields.length)
  corner.textContent = res.rowFields.map(nameOf).join(' · ')

  res.header.forEach((hrow, i) => {
    const tr = el('tr')
    if (i === 0) tr.append(corner)
    for (const cell of hrow) {
      const th = el('th', `dxp-h dxp-${cell.kind}${cell.total ? ' dxp-total' : ''}`)
      th.colSpan = cell.span
      th.textContent = cell.total ? String(cell.label) : label(cell.label)
      tr.append(th)
    }
    thead.append(tr)
  })
  if (!res.header.length) {
    const tr = el('tr')
    tr.append(corner)
    thead.append(tr)
  }
  table.append(thead)

  const tbody = el('tbody')
  const shown = Math.min(res.rows.length, PIVOT_ROW_CAP)
  for (let ri = 0; ri < shown; ri++) {
    const row = res.rows[ri]
    const tr = el('tr', `dxp-r dxp-${row.kind}`)

    // Row headers: one cell per row field, with the ancestors that repeat from
    // the previous line left blank. Repeating "North" down forty lines is how a
    // pivot stops looking like an outline and starts looking like a export.
    const prev = ri > 0 ? res.rows[ri - 1] : null
    if (row.kind === 'grand') {
      const th = el('th', 'dxp-rh dxp-total')
      th.colSpan = Math.max(1, res.rowFields.length)
      th.textContent = t('Grand total')
      tr.append(th)
    } else if (row.kind === 'subtotal') {
      const th = el('th', 'dxp-rh dxp-total')
      th.colSpan = Math.max(1, res.rowFields.length)
      th.textContent = totalCaption(row.path)
      tr.append(th)
    } else {
      for (let l = 0; l < Math.max(1, res.rowFields.length); l++) {
        const same = prev && prev.kind === 'leaf' && prev.codes[l] === row.codes[l]
          && prev.codes.slice(0, l).join(',') === row.codes.slice(0, l).join(',')
        tr.append(el('th', 'dxp-rh', same ? '' : label(row.path[l])))
      }
    }

    row.cells.forEach((cell, ci) => {
      const c = res.columns[ci]
      const td = el('td', `dxp-c dxp-${c.kind}${row.kind !== 'leaf' ? ' dxp-total' : ''}`)
      td.textContent = formatAgg(cell.v, c.value.fn, colOf(c.value.col))
      if (cell.rows === 0) {
        td.classList.add('dxp-gap')
        // A gap is titled, because an empty cell and a cell holding an empty
        // string look identical and mean opposite things.
        td.title = t('no rows')
      } else {
        td.title = t('{n} source rows').replace('{n}', String(cell.rows))
        // A COUNT reads every value, number or not — "Count of Owner" counts
        // names, and marking each of its cells "values are not numbers and were
        // not counted" says the opposite of what happened. Only the functions
        // that do arithmetic can be short.
        if (cell.skipped && c.value.fn !== 'count' && c.value.fn !== 'countDistinct') {
          // Never folded into the number and never hidden: this is `Column.failed`
          // (model.ts:87) at the aggregate level.
          td.classList.add('dxp-skipped')
          td.title += ` · ${t('{n} value(s) are not numbers and were not counted').replace('{n}', String(cell.skipped))}`
        }
        td.tabIndex = 0
        td.addEventListener('click', () => openDrill(ri, ci))
        td.addEventListener('keydown', (e) => {
          if (e.key === 'Enter' || e.key === ' ') { e.preventDefault(); openDrill(ri, ci) }
        })
      }
      tr.append(td)
    })
    tbody.append(tr)
  }
  table.append(tbody)
  scroll.append(table)
  wrap.append(scroll)

  if (res.rows.length > shown) {
    wrap.append(el('p', 'dxp-note',
      t('{n} more rows are not shown — this pivot has stopped summarising.')
        .replace('{n}', String(res.rows.length - shown))))
  }
  wrap.append(el('p', 'dxp-note',
    t('{n} source rows').replace('{n}', String(res.scanned))))

  const drawer = el('div', 'dxp-drawer')
  drawer.hidden = true
  wrap.append(drawer)
  host.append(wrap)

  function openDrill(ri: number, ci: number): void {
    const d = drillDown(res, ri, ci)
    const row = res.rows[ri]
    const c = res.columns[ci]
    if (opts.onDrill) { opts.onDrill(d, row, c); return }
    drawer.textContent = ''
    drawer.hidden = false

    const head = el('div', 'dxp-drawer-head')
    const parts = [
      ...row.path.map(label),
      ...c.path.map(label),
      valueName(c.value, nameOf),
    ]
    head.append(el('span', 'dxp-drawer-title', parts.join(' · ')))
    head.append(el('span', 'dxp-drawer-count',
      t('{n} rows').replace('{n}', String(d.total))))
    const close = el('button', 'dxp-drawer-close', '✕')
    close.title = t('Close')
    close.addEventListener('click', () => { drawer.hidden = true })
    head.append(close)
    drawer.append(head)

    const dt = el('table', 'dxp-drill')
    const htr = el('tr')
    // The source ROW NUMBER is shown, because the whole point is to be able to
    // go and look at it. Positions are 1-based here to match the grid gutter.
    htr.append(el('th', 'dxp-drill-n', '#'))
    for (const sc of sheet.columns) htr.append(el('th', '', sc.name))
    const dhead = el('thead')
    dhead.append(htr)
    dt.append(dhead)
    const body = el('tbody')
    for (const r of d.rows) {
      const tr = el('tr')
      tr.append(el('td', 'dxp-drill-n', String(r + 1)))
      for (const sc of sheet.columns) {
        const v = opts.computed?.get(sc.id)?.[r] ?? readCell(sheet.data[sc.id], r)
        tr.append(el('td', '', formatValue(v, sc)))
      }
      body.append(tr)
    }
    dt.append(body)
    const dscroll = el('div', 'dxp-drill-scroll')
    dscroll.append(dt)
    drawer.append(dscroll)
    if (d.truncated) {
      drawer.append(el('p', 'dxp-note',
        t('Showing the first {n} of {total}.')
          .replace('{n}', String(d.rows.length))
          .replace('{total}', String(d.total))))
    }
    drawer.scrollIntoView({ block: 'nearest' })
  }

  return () => { host.textContent = '' }
}

/** Exposed for the rig, as store.ts and rowcol.ts do — not part of the app's surface. */
export const _internals = { asNumber, matchKey, median, groupPlane, valuePlane, flatten, BLANK }