// sheet/bindingRefs.ts — WHERE A BINDING KEEPS CELL REFERENCES, AND HOW THEY MOVE (wave 54, // W54-T18, contract C3, ruling R6). // // ⭐⭐ THIS FILE EXISTS SO THERE IS EXACTLY ONE ANSWER TO "WHICH PARTS OF A BINDING ARE CELL // REFERENCES". The registry has fixed object paths for `period.ref` and a separate tree slot // kind for filter leaf `valueRef` records. Every door that copies, fills or pastes a bound cell // already comes through `shiftBindingRefs`. // // ⛔⛔ THE `$` IN THE STORED STRING **IS** THE PER-AXIS ABSOLUTE FLAG, AND NO SECOND // REPRESENTATION OF IT IS ADDED HERE. C3 is worded "stored with explicit absolute/relative flags // per axis", which reads like an invitation to add `colAbs` / `rowAbs` booleans to the stored // binding. It was considered and it is REFUSED. Excel already encodes those two flags, per axis, // in the `$` characters of the text; `a1.ts::CellRef` already parses them into real booleans; // `core/sheets.py` already stores and round-trips the string. Storing the flags a second time // would create two records of one fact, which drift the first time anything writes one without // the other — and the thing most likely to write only one is a hand edit in the binding editor. // So the flags are EXPOSED through the API, on the parsed shape every slot carries, and stored // once, in the character the reader typed. // // ⛔ ANCHORING IS NEVER RE-DERIVED HERE. `a1.ts` owns what a `$` pins, the clamp at row 1 and // column A, and the quoted sheet name. A second opinion about which of `LOG10(`, `'Q1 plan'!A1` // and `$B$5` is a reference is precisely the failure this file is built to make impossible. // // ⛔ NOTHING HERE MAY IMPORT OUTSIDE `src/sheet/`. tsc's emitted ROOT moves with the import graph, // so one import of `../../ui` would drop every emitted module a level down and break every // `require` path the battery's Sheet proofs use. `./a1` only. import { fillFormula, formatRef, parseRef, shiftForStructure, shiftRef } from "./a1"; import type { CellRef, StructuralShift } from "./a1"; import { endingCellDate, endingStepDefault } from "../customer-grid/windows"; import type { BindingFilterValueRef, Workbook } from "./types"; import { bindingOnDrag, BINDING_ENDING_STEP, BINDING_PERIOD_STEP } from "./types"; import type { Field, FilterNode, FilterRule, FilterTree, Row } from "../customer-grid/types"; import { choiceVocabulary, isDateFamilyType, isNumericFieldType, measureColumnIndex, ruleColumnKeys, } from "../customer-grid/types"; export const bindingStepMode = (field: Field): "presence" | "member" | "value" => field.type === "checkbox" || field.type === "json" ? "presence" : field.multi || String(field.type) === "set" || field.type === "multiselect" || field.type === "link" ? "member" : "value"; /** Full permission-scoped rows only. Order is stable across browsers and never capped. */ export const bindingStepValues = ( field: Field, rows: readonly Row[], userOptions: readonly string[] = [], ): string[] => { if (bindingStepMode(field) === "presence") return ["false", "true"]; if (["select", "status", "multiselect"].includes(field.type)) return choiceVocabulary(field, [...rows]); if (field.type === "user" && userOptions.length) return [...new Set(userOptions)]; const values = new Set(); for (const row of rows) { const raw = row[field.key]; if (raw == null || raw === "") continue; const parts = bindingStepMode(field) === "member" ? (Array.isArray(raw) ? raw : String(raw).split(",")) : [raw]; for (const part of parts) { const value = String(part).trim(); if (value) values.add(value); } } const numeric = isNumericFieldType(field.type) || field.type === "link"; const date = isDateFamilyType(field.type); return [...values].sort((a, b) => { const left = date ? Date.parse(a) : numeric ? Number(a) : NaN; const right = date ? Date.parse(b) : numeric ? Number(b) : NaN; if (Number.isFinite(left) && Number.isFinite(right) && left !== right) return left - right; return a < b ? -1 : a > b ? 1 : 0; }); }; /** Selecting Step creates a visible driver and leaves unrelated filter branches intact. */ export const seedBindingStep = (tree: FilterTree, field: Field, value: string): FilterTree => { const mode = bindingStepMode(field); const leaf: FilterRule = mode === "presence" ? { colId: field.key, op: value === "true" ? "isNotEmpty" : "isEmpty", value: "" } : mode === "member" ? { colId: field.key, op: "hasAnyOf", value: "", values: [value] } : { colId: field.key, op: "eq", value }; let found = false; const visit = (node: FilterNode): FilterNode => { if ("children" in node) return { ...node, children: node.children.map(visit) }; if (node.colId !== field.key) return node; found = true; return { ...leaf }; }; const nodes = tree.nodes.map(visit); if (found) return { ...tree, nodes }; // An absent driver narrows an OR filter as a whole, rather than widening it with another arm. return { conj: "and", nodes: tree.conj === "or" && nodes.length ? [{ conj: "or", children: nodes }, leaf] : [...nodes, leaf] }; }; /** * Shift a bare reference or formula slot by a row and column delta, `$` honoured per axis, * by handing the whole question to `a1.ts`. * * ⚠ Text that is not a reference comes back untouched rather than mangled or emptied. A drag is * not the place to discover that a stored pointer was malformed, and blanking it would lose the * only copy of what the reader actually wrote. */ export const shiftRefText = (text: string, dRow: number, dCol: number): string => { const src = String(text ?? "").trim(); if (!src || (!dRow && !dCol)) return src; if (src.startsWith("=")) return fillFormula(src, dRow, dCol); const parsed = parseRef(src); if (!parsed) return src; return formatRef(shiftRef(parsed, dRow, dCol)); }; /** * One place inside a binding where a cell reference is allowed to live. * * ⚠ `pinnedAt` IS A LOCK, NOT A VALIDITY TEST. R9's locked period copies verbatim through every * door, so a pinned slot is enumerated (the reader can still see what it points at) and simply * never shifted during a copy. Structural changes still follow the referenced cells. */ interface PathRefSlotSpec { readonly kind: "path"; /** The dotted name, for anything that has to SAY which slot it means. */ readonly key: string; /** The path from the binding root to the reference's own string. */ readonly path: readonly string[]; /** A path to a truthy value that freezes this slot. Absent means nothing freezes it. */ readonly pinnedAt?: readonly string[]; /** A binding-wide rule that freezes this slot even without a flag at its own path. */ readonly pinnedWhen?: (binding: Record) => boolean; } type RefSlotSpec = PathRefSlotSpec | { readonly kind: "filter"; readonly key: string }; export type CellFilterRule = FilterRule & { valueRef?: BindingFilterValueRef; value2Ref?: BindingFilterValueRef }; /** Both cell slots a filter leaf can carry: the value, and `between`'s upper bound. */ export const FILTER_REF_KEYS = ["valueRef", "value2Ref"] as const; /** Structural compatibility slots, mirrored by sheet_structure.BINDING_REF_KEYS. */ const BINDING_REF_KEYS = new Set(["ref", ...FILTER_REF_KEYS, "cellRef", "endingRef"]); /** Indices follow nodes/children arrays, independently of the fixed object-path slot kind. */ export const filterValueLeaves = (tree: FilterTree): { node: CellFilterRule; indices: number[] }[] => { const out: { node: CellFilterRule; indices: number[] }[] = []; const visit = (nodes: FilterNode[], parent: number[]) => nodes.forEach((node, index) => { const indices = [...parent, index]; if ("children" in node) visit(node.children, indices); else out.push({ node, indices }); }); visit(tree.nodes, []); return out; }; export const updateFilterValueLeaf = ( tree: FilterTree, indices: readonly number[], change: (node: CellFilterRule) => CellFilterRule, ): FilterTree => { const visit = (nodes: FilterNode[], path: readonly number[]): FilterNode[] => nodes.map((node, index) => index !== path[0] ? node : "children" in node ? { ...node, children: visit(node.children, path.slice(1)) } : change(node)); return { ...tree, nodes: visit(tree.nodes, indices) }; }; /** What a referenced cell holds right now: its value as text, and its formula when it has one. */ export type ReadBindingCell = (ref: string) => { value: string; formula: string; problem?: string } | null; const mapFilterLeaves = (tree: FilterTree, change: (node: CellFilterRule) => CellFilterRule): FilterTree => { const visit = (nodes: FilterNode[]): FilterNode[] => nodes.map((node) => "children" in node ? { ...node, children: visit(node.children) } : change(node)); return { ...tree, nodes: visit(tree.nodes) }; }; /** * Hotfix 2026-09-24 (owner: "the edit filter shows in or after a date but the date is blank"). * The shared filter builder only knows typed values, so a condition whose value comes from a cell * painted an empty date. This fills each such leaf's `value` with what the cell reads NOW, for * display only; `keepCellFilterValues` takes it back off before the tree is stored. */ export const showCellFilterValues = (tree: FilterTree, read?: ReadBindingCell): FilterTree => !read ? tree : mapFilterLeaves(tree, (node) => { const got = node.valueRef?.ref ? read(node.valueRef.ref) : null; const got2 = node.value2Ref?.ref ? read(node.value2Ref.ref) : null; const shown = got && got.value !== "" ? { ...node, value: got.value } : node; return got2 && got2.value !== "" ? { ...shown, value2: got2.value } : shown; }); /** * The inverse, run on every change from the builder. A leaf still showing its cell's value keeps * the link and drops the displayed copy, so an untouched binding saves byte for byte as it was * read. A leaf whose value the person typed over becomes a typed value, and the link goes. */ export const keepCellFilterValues = (tree: FilterTree, read?: ReadBindingCell): FilterTree => !read ? tree : mapFilterLeaves(tree, (node) => { let out = node; for (const [refKey, valueKey] of [["valueRef", "value"], ["value2Ref", "value2"]] as const) { const ref = out[refKey]?.ref; if (!ref) continue; const shown = read(ref)?.value ?? ""; const raw = out[valueKey]; const typed = typeof raw === "string" ? raw : raw == null ? "" : String(raw); if (typed === "" || typed === shown) { const { [valueKey]: _shown, ...linked } = out; out = linked as CellFilterRule; } else { const { [refKey]: _link, ...own } = out; out = own as CellFilterRule; } } return out; }); /** * ⭐⭐ THE REGISTRY. Everything this module knows about binding references is these entries. * * Fixed paths and nested filter arrays have separate slot kinds. Both enumerate through the * same door, so copy, fill, Ctrl+D, Ctrl+R and paste use the same anchoring and lock rules. */ const REF_SLOTS: readonly RefSlotSpec[] = [ { kind: "path", key: "period.ref", path: ["period", "ref"], pinnedAt: ["period", "locked"], // R13: turning period stepping off clones the WHOLE period, including a relative source ref. pinnedWhen: (binding) => { const rule = binding.onDrag; if (Array.isArray(asRecord(rule)?.steps)) return !bindingOnDrag(binding).steps?.some( (step) => step.field === BINDING_ENDING_STEP || step.field === BINDING_PERIOD_STEP); return Boolean(rule && typeof rule === "object" && !Array.isArray(rule) && (rule as Record).across === "none"); }, }, { kind: "filter", key: "scope.filter" }, ]; /** What one occupied slot IS, right now, with its anchoring already parsed out per axis. */ export interface BindingRefSlot { readonly kind: "path" | "filter"; readonly indices?: readonly number[]; /** A filter slot's key on its leaf: the value, or `between`'s upper bound. */ readonly refKey?: "valueRef" | "value2Ref"; readonly key: string; readonly path: readonly string[]; /** The stored text, trimmed exactly as `types.ts::bindingPeriod` trims it. */ readonly text: string; /** * The parsed shape, or `null` when the text is not a reference at all. ⭐ THIS IS WHERE THE * PER-AXIS ABSOLUTE FLAGS ARE EXPOSED: `colAbs` and `rowAbs` are real booleans here, read out * of the `$` characters that store them. */ readonly parsed: CellRef | null; /** True when a lock holds this slot still, whatever the delta (R9). */ readonly pinned: boolean; } const asRecord = (value: unknown): Record | null => value && typeof value === "object" && !Array.isArray(value) ? (value as Record) : null; export type CopyAxis = "right" | "down" | "left" | "up"; export interface FilterStep { binding: Record; changed: boolean; /** No leaf in the tree names that field. */ noField: boolean; /** The walk stopped at an option boundary rather than wrapping. */ atEnd: boolean; } const optionIndex = (options: readonly string[], value: string): number => options.findIndex((option) => String(option) === value); /** An unknown value starts before the first option; walking past either end clamps. */ const walkOption = ( options: readonly string[], value: string, steps: number, ): { value: string; atEnd: boolean } => { if (options.length === 0 || steps === 0) return { value, atEnd: false }; const wanted = optionIndex(options, value) + steps; const landed = Math.max(0, Math.min(options.length - 1, wanted)); return { value: String(options[landed]), atEnd: landed !== wanted }; }; /** Copy only changed paths through nested groups; unrelated clauses keep their exact records. */ const walkFilterNodes = ( nodes: readonly unknown[], field: string, next: (value: string) => string, mode?: "value" | "presence" | "member", ): { nodes: readonly unknown[]; found: boolean; changed: boolean } => { let found = false; let changed = false; const out = nodes.map((node) => { const record = asRecord(node); if (!record) return node; if (Array.isArray(record.children)) { const inner = walkFilterNodes(record.children, field, next, mode); found = found || inner.found; if (!inner.changed) return node; changed = true; return { ...record, children: inner.nodes }; } if (String(record.colId ?? "") !== field) return node; // A cell operand moves through the reference slot, never through its last preview value. if ("valueRef" in record || "value2Ref" in record) return node; found = true; const presence = mode ? mode === "presence" : record.op === "isEmpty" || record.op === "isNotEmpty"; const member = mode ? mode === "member" : Array.isArray(record.values) && record.values.length === 1; const value = presence ? String(record.op === "isNotEmpty") : member ? String((record.values as unknown[] | undefined)?.[0] ?? record.value ?? "") : String(record.value ?? ""); const walked = next(value); if (walked === value) return node; changed = true; return presence ? { ...record, op: walked === "true" ? "isNotEmpty" : "isEmpty", value: "" } : member ? { ...record, values: [walked] } : { ...record, value: walked }; }); return { nodes: changed ? out : nodes, found, changed }; }; /** C4 / R8: right and down advance by n; left and up reverse that distance. */ export const stepBindingFilter = ( binding: Record, field: string, options: readonly string[], axis: CopyAxis, n: number, mode?: "value" | "presence" | "member", ): FilterStep => { const steps = n * (axis === "left" || axis === "up" ? -1 : 1); const scope = asRecord(binding.scope); const tree = asRecord(scope?.filter); if (!tree || !Array.isArray(tree.nodes)) { return { binding, changed: false, noField: true, atEnd: false }; } let atEnd = false; const walked = walkFilterNodes(tree.nodes, field, (value) => { const option = walkOption(options, value, steps); atEnd = atEnd || option.atEnd; return option.value; }, mode); return { binding: walked.changed ? { ...binding, scope: { ...scope, filter: { ...tree, nodes: walked.nodes } } } : binding, changed: walked.changed, noField: !walked.found, atEnd, }; }; /** Read the string at a path, trimmed. Empty when any step of the path is missing. */ const readAt = (root: Record, path: readonly string[]): string => { let node: Record | null = root; for (let i = 0; i < path.length - 1; i += 1) { node = node ? asRecord(node[path[i]]) : null; } const leaf = node ? node[path[path.length - 1]] : undefined; return typeof leaf === "string" ? leaf.trim() : ""; }; /** * Whether the flag at a path is set. * * ⛔ IT IS STRICT `=== true`, WHICH IS `types.ts::bindingPeriod`'s OWN TEST AND NOT AN ACCIDENT. * A missing key reads FALSE, the permissive answer: a period stored before the lock toggle * existed must not start refusing to shift with nothing on screen saying why. */ const flagAt = (root: Record, path: readonly string[] | undefined): boolean => { if (!path || path.length === 0) return false; let node: Record | null = root; for (let i = 0; i < path.length - 1; i += 1) { node = node ? asRecord(node[path[i]]) : null; } return (node ? node[path[path.length - 1]] : false) === true; }; /** * Write a value at a path, COPYING every record along the way. * * ⛔ NOTHING IS MUTATED. `dragBoundCell` is a pure function over a workbook and the cell it is * handed is still on the sheet; writing through would edit the SOURCE cell while copying it. */ const writeAt = ( root: Record, path: readonly string[], value: string, ): Record => { if (path.length === 1) return { ...root, [path[0]]: value }; const head = path[0]; const inner = asRecord(root[head]) ?? {}; return { ...root, [head]: writeAt(inner, path.slice(1), value) }; }; /** * Every slot in this binding that actually HOLDS a reference, in registry order. * * Empty period slots are absent. A declared filter slot remains enumerable even when blank, * so clearing its reference produces a refusal rather than dropping a condition. */ export const bindingRefSlots = (binding: unknown): BindingRefSlot[] => { const record = asRecord(binding); if (!record) return []; const out: BindingRefSlot[] = []; for (const spec of REF_SLOTS) { if (spec.kind === "filter") { const tree = asRecord(asRecord(record.scope)?.filter); if (!tree || !Array.isArray(tree.nodes)) continue; for (const { node, indices } of filterValueLeaves(tree as unknown as FilterTree)) { for (const refKey of FILTER_REF_KEYS) { if (!(refKey in node)) continue; const slot = node[refKey]; const text = typeof slot?.ref === "string" ? slot.ref.trim() : ""; out.push({ kind: "filter", key: `${spec.key}.${indices.join(".")}.${refKey}`, refKey, path: [], indices, text, parsed: parseRef(text), pinned: slot?.locked === true }); } } continue; } const text = readAt(record, spec.path); if (!text) continue; out.push({ kind: "path", key: spec.key, path: spec.path, text, parsed: parseRef(text), pinned: flagAt(record, spec.pinnedAt) || Boolean(spec.pinnedWhen?.(record)), }); } return out; }; /** What `shiftBindingRefs` did, so a caller can say whether the binding is the one it copied. */ export interface BindingRefShift { /** The binding, rewritten. The SAME object when nothing moved, so `===` is a valid check. */ binding: Record; /** At least one slot's text is different from the one it was copied from. */ changed: boolean; /** The `key` of each slot that actually moved. */ moved: string[]; } /** * ⭐⭐ THE DOOR. Shift every reference this binding holds by one delta, `$` honoured per axis, * locked slots left verbatim. * * ⚠ `changed` IS LOAD-BEARING FOR THE CALLER AND NOT MERELY INFORMATIONAL. A bound cell whose * binding CHANGED drops its last computed value, because showing the source's number under a * different reference is a wrong number wearing a plausible mark; a verbatim copy keeps it. So a * slot whose shifted text equals its stored text does NOT count as moved, and neither does a * pinned one, an empty one or an unparseable one. */ export const shiftBindingRefs = ( binding: Record, dRow: number, dCol: number, ): BindingRefShift => { let out = binding; const moved: string[] = []; if (!asRecord(binding)) return { binding, changed: false, moved }; for (const slot of bindingRefSlots(binding)) { if (slot.pinned) continue; const steps = slot.kind === "path" && Array.isArray(asRecord(binding.onDrag)?.steps) ? bindingOnDrag(binding).steps?.filter((step) => step.field === BINDING_ENDING_STEP || step.field === BINDING_PERIOD_STEP) : undefined; const next = shiftRefText(slot.text, steps ? (steps.some((step) => step.axis === "down") ? dRow : 0) : dRow, steps ? (steps.some((step) => step.axis === "across") ? dCol : 0) : dCol); if (next === slot.text) continue; if (slot.kind === "filter") { const scope = asRecord(out.scope)!; const tree = scope.filter as FilterTree; const refKey = slot.refKey ?? "valueRef"; out = { ...out, scope: { ...scope, filter: updateFilterValueLeaf(tree, slot.indices!, (node) => ({ ...node, [refKey]: { ...node[refKey], ref: next } })) } }; } else out = writeAt(out, slot.path, next); moved.push(slot.key); } const period = asRecord(out.period); const rules = asRecord(out.onDrag); const endingRule = bindingOnDrag(out).steps?.find((step) => step.field === BINDING_ENDING_STEP); const endingDistance = Array.isArray(rules?.steps) ? (endingRule ? Math.abs(endingRule.axis === "across" ? dCol : dRow) : 0) : rules?.across === "none" ? 0 : Math.abs(dCol || dRow); if (period?.ending && period.locked !== true && endingDistance) { const window = asRecord(period.window); out = { ...out, period: { ...period, endingStep: endingRule?.size ?? period.endingStep ?? endingStepDefault(String(window?.kind ?? "")), endingOffset: Number(period.endingOffset ?? 0) + endingDistance, } }; moved.push("period.endingOffset"); } return { binding: out, changed: moved.length > 0, moved }; }; /** W61-T01 / C1: structure follows each referenced cell's position through the same registry. */ export const structureBindingRefs = ( binding: Record, shift: StructuralShift, ownSheet: string, ): BindingRefShift => mapBindingRefs(binding, (text) => shiftForStructure(text, shift, ownSheet), ownSheet); /** * ⭐ S67-T09: the ONE walk over every reference a binding carries (period, filter leaves with * their `valueRef` / `value2Ref`, related leaves, and the legacy keys in `BINDING_REF_KEYS`), * with the rewrite handed in. A structural insert/delete and a cell MOVE (`a1.ts::shiftForMove`) * both pass through it, so neither can miss a slot the other reaches. */ export const mapBindingRefs = ( binding: Record, rewrite: (text: string) => string, ownSheet?: string, ): BindingRefShift => { let out = binding; const moved: string[] = []; if (!asRecord(binding)) return { binding, changed: false, moved }; const registered = new Set(); for (const slot of bindingRefSlots(binding)) { const path = slot.kind === "path" ? slot.path : ["scope", "filter", "nodes", ...slot.indices!.flatMap((index, i) => i ? ["children", String(index)] : [String(index)]), slot.refKey ?? FILTER_REF_KEYS[0], "ref"]; registered.add(path.join(".")); // R9 locks COPY only; even pinned slots follow cells moved by a structural operation. const next = rewrite(slot.text); if (next === slot.text) continue; if (slot.kind === "filter") { const scope = asRecord(out.scope)!; const tree = scope.filter as FilterTree; const refKey = slot.refKey ?? "valueRef"; out = { ...out, scope: { ...scope, filter: updateFilterValueLeaf(tree, slot.indices!, (node) => ({ ...node, [refKey]: { ...node[refKey], ref: next } })) } }; } else out = writeAt(out, slot.path, next); moved.push(slot.key); } // Imported bindings and view filters may carry legacy slots outside the copy/fill registry. // Walk the original tree so registered slots are never shifted twice. A related: colId is // an ordinary leaf: only the reference keys, never the field name, decide what moves. const walk = (original: unknown, current: unknown, path: string[]): unknown => { if (Array.isArray(original)) { const values = current as unknown[]; const next = original.map((child, index) => walk(child, values[index], [...path, String(index)])); return next.some((child, index) => child !== values[index]) ? next : current; } const record = asRecord(original); if (!record) return current; let next = asRecord(current)!; for (const [key, child] of Object.entries(record)) { if (key === "_deletedRefs") continue; const location = [...path, key]; if (BINDING_REF_KEYS.has(key) && typeof child === "string" && (parseRef(child) || child.trim().startsWith("=")) && !registered.has(location.join("."))) { const value = rewrite(child); if (value !== child) { next = { ...next, [key]: value }; moved.push(location.join(".")); } } else if (child && typeof child === "object") { const value = walk(child, next[key], location); if (value !== next[key]) next = { ...next, [key]: value }; } const currentValue = next[key]; if (ownSheet !== undefined && BINDING_REF_KEYS.has(key) && typeof child === "string" && typeof currentValue === "string" && currentValue.includes("#REF!") && !child.includes("#REF!")) { const parsed = parseRef(child); const originalRef = parsed ? formatRef({ ...parsed, sheet: parsed.sheet || ownSheet }) : child; next = { ...next, _deletedRefs: { ...asRecord(next._deletedRefs), [key]: originalRef } }; } } return next; }; const compatible = walk(binding, out, []) as Record; if (moved.length) out = compatible; // endingOffset counts drag distance. An insert/delete copies nothing, so it stays verbatim. return { binding: out, changed: moved.length > 0, moved }; }; /** Read the current value, never display text or an old refused value. */ export const readBindingCell = ( book: Workbook, text: string, home: string | undefined, fail: (reason: string) => never, emptyFilter = false, ): unknown => { const ref = parseRef(text); if (!ref) return fail("its reference is invalid."); const name = ref.sheet || home; if (!name && book.sheets.length > 1) return fail("the source sheet is not named. Include its name, for example Sheet1!A1."); const tab = name ? book.sheets.find((t) => t.id === name || t.name === name) : book.sheets[0]; const cell = tab?.cells[`${ref.col}${ref.row}`]; if (!tab) return fail("its sheet does not exist."); if (cell?.state === "refused") return fail(cell.refusal?.message || "the cell is refused."); if (cell?.state === "stale") return fail("the cell needs to be refreshed."); if (!cell || cell.kind === "empty") return emptyFilter ? "" : fail("the cell is blank."); return cell.value !== undefined ? cell.value : (cell.kind === "literal" ? cell.text : undefined); }; export const resolveEndingCell = (book: Workbook, text: string, home?: string): string => { const fail = (reason: string): never => { throw new Error(`Ending from cell ${text || "(blank)"} cannot be read because ${reason} ` + "Use a cell containing a date in YYYY-MM-DD format."); }; const value = readBindingCell(book, text, home, fail); const date = endingCellDate(value, book.dateSystem); if (!date) return fail("its value is not a real date."); return date; }; /** * ⭐⭐ S67-T10 (QI4, D-767) — A CELL IS A DATE WHEN ITS NUMBER FORMAT SAYS SO: a Date format set by * hand, the format the engine infers for DATE() / TODAY() / NOW() (written into `style.format`), * or an imported Excel code carrying day or year tokens. Mirrored by * `platform/modules/sheet.py::date_formatted_cell`, so the panel, a refresh and the server read * the same cell the same way. */ export const isDateFormattedCell = (cell: unknown): boolean => { const style = asRecord(asRecord(cell)?.style); const format = asRecord(style?.format); if (!format) return false; if (format.kind === "date") return true; return format.kind === "code" && typeof format.code === "string" && /[dy]/i.test(format.code.replace(/"[^"]*"|\\./g, "")); }; /** The referenced cell itself, for the questions its VALUE alone cannot answer (its format). */ const bindingCellAt = (book: Workbook, text: string, home?: string): unknown => { const ref = parseRef(text); if (!ref) return null; const name = ref.sheet || home; const tab = name ? book.sheets.find((t) => t.id === name || t.name === name) : book.sheets[0]; return tab?.cells[`${ref.col}${ref.row}`] ?? null; }; export const resolveFilterCell = (book: Workbook, text: string, home?: string): string => { const fail = (reason: string): never => { throw new Error(`Filter value from cell ${text || "(blank)"} cannot be read because ${reason} ` + "Choose a readable cell containing one nonblank value, or type the filter value."); }; const value = readBindingCell(book, text, home, fail, true); // Empty text is the existing filter-box no-constraint value. Both preview and panel // consumers accept it without changing their string-valued reader contract. if (value == null || typeof value === "string" && !value.trim()) return ""; if (!["string", "number", "boolean"].includes(typeof value) || String(value).trim() === "" || typeof value === "number" && !Number.isFinite(value)) return fail("its value is blank or is not a single usable value."); // S67-T10: a date-formatted serial (typed, or a formula's result such as 46293) IS that date, // for every operator, so it leaves as the ISO day the filter compares. A plain number with no // date format stays a number and the panel refuses it by name (O9). if (typeof value === "number" && isDateFormattedCell(bindingCellAt(book, text, home))) { const day = endingCellDate(value, book.dateSystem); if (!day) return fail("its value is not a real date."); return day; } return String(value); }; // ── W55-T07, contract C6: fields a cell filter depends on ─────────────────────────────────── // // This stays structural and import-free on purpose. `bindingRefs.ts` is a leaf used by the Sheet // node proofs, and importing the customer-grid filter types here would move tsc's emitted root. // The stored dialect is still the exact FilterTree dialect. // W55-T08 / C5: copied saved Views become local filters before their drivers step. /** One canonical saved-View filter returned by the Sheet server for copying. */ export interface SavedViewShadow { readonly database: string; readonly viewId: string; readonly filter: unknown; } /** What localising one binding did. Missing means the caller must not pretend R5 completed. */ export interface LocalViewBinding { readonly binding: Record; readonly localized: boolean; readonly missing: boolean; } /** * Copy JSON-shaped filter state without sharing any record or array with the saved View snapshot. * The filter language stays structural here so this leaf module does not import UI types. */ const copyJson = (value: unknown): unknown => { if (Array.isArray(value)) return value.map(copyJson); const record = asRecord(value); if (!record) return value; return Object.fromEntries(Object.entries(record).map(([key, child]) => [key, copyJson(child)])); }; /** * R5's materialisation boundary. A saved-View binding becomes a filter-scoped binding before any * driver walks it. The original binding and the server-owned shadow are never mutated. * * A binding that is already local is returned untouched. A missing saved-View shadow is explicit * so the copy door can say that it has not completed instead of silently retaining shared state. */ export const materializeLocalViewBinding = ( binding: Record, shadows: readonly SavedViewShadow[] | undefined, ): LocalViewBinding => { const scope = asRecord(binding.scope); const viewId = typeof scope?.view_id === "string" ? scope.view_id.trim() : ""; if (!viewId) return { binding, localized: false, missing: false }; const database = typeof binding.database === "string" ? binding.database.trim() : ""; const found = (shadows ?? []).find( (shadow) => shadow.database === database && shadow.viewId === viewId, ); if (!found || !asRecord(found.filter)) { return { binding, localized: false, missing: true }; } return { binding: { ...binding, scope: { filter: copyJson(found.filter) } }, localized: true, missing: false, }; }; // W55-T07 / C6 continues with the filter fields a cell View must show. /** Clause order, including nested groups and both operands; Metric windows use grid identity. */ export const filterFieldKeys = (tree: unknown, fields?: readonly Field[]): string[] => { const root = asRecord(tree); const measureCols = fields ? measureColumnIndex([...fields]) : undefined; const seen = new Set(); const add = (value: unknown) => { const key = typeof value === "string" ? value.trim() : ""; // Cohort and View membership leaves are filter vocabulary, not database fields. if (key && !key.startsWith("__")) seen.add(key); }; const walk = (nodes: unknown) => { if (!Array.isArray(nodes)) return; for (const raw of nodes) { const node = asRecord(raw); if (!node) continue; if (Array.isArray(node.children)) { walk(node.children); continue; } // A Metric clause names a semantic key, which can differ from its displayed field key. // Resolve only columns in the permission-scoped catalogue, including their exact window. const rule = node as unknown as FilterRule; for (const key of fields ? ruleColumnKeys(rule, measureCols) : [node.colId]) add(key); const rhs = asRecord(node.rhs); if (rhs?.kind === "measure" && fields) { const operand = { ...rule, colId: rhs.colId, window: rhs.window } as FilterRule; for (const key of ruleColumnKeys(operand, measureCols)) add(key); } else if (rhs?.kind !== "stat") add(rhs?.colId); } }; walk(root?.nodes); return [...seen]; }; /** Fields that explain a cell's rows and value, regardless of the database's default columns. */ export const cellForcedFieldKeys = ( filterTree: unknown, aggregateField?: string, stepField?: string, fields?: readonly Field[], ): string[] => { const keys = new Set(filterFieldKeys(filterTree, fields)); for (const field of [aggregateField, stepField]) { const key = field?.trim(); if (key && !key.startsWith("__")) keys.add(key); } return [...keys]; }; /** * C9: identity, then filter dependencies in clause order, then preferred/default display fields. * Invalid or unavailable keys never become columns, and each key appears only once. */ export const cellViewVisibleKeys = ( defaults: readonly string[], preferred: readonly string[], filterKeys: readonly string[], available: readonly string[], identity = "", ): string[] => { const valid = new Set(available); const out: string[] = []; const add = (key: string) => { if (key && valid.has(key) && !out.includes(key)) out.push(key); }; if (identity) add(identity); for (const key of filterKeys) add(key); for (const key of preferred.length ? preferred : defaults) add(key); return out; };