loopable / web /src /sheet /bindingRefs.ts
fsanyoto's picture
Deploy AIOS web (React glide grid + FastAPI slice)
98c8696 verified
Raw History Blame
36.3 kB
// 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<string>();
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<string, unknown>) => 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<string>(["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<string, unknown>).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<string, unknown> | null =>
value && typeof value === "object" && !Array.isArray(value)
? (value as Record<string, unknown>)
: null;
export type CopyAxis = "right" | "down" | "left" | "up";
export interface FilterStep {
binding: Record<string, unknown>;
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<string, unknown>, 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<string, unknown>, path: readonly string[]): string => {
let node: Record<string, unknown> | 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<string, unknown>, path: readonly string[] | undefined): boolean => {
if (!path || path.length === 0) return false;
let node: Record<string, unknown> | 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<string, unknown>, path: readonly string[], value: string,
): Record<string, unknown> => {
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<string, unknown>;
/** 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<string, unknown>, 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<string, unknown>, 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<string, unknown>, 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<string>();
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<string, unknown>;
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<string, unknown>;
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<string, unknown>, 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<string>();
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;
};