File size: 36,321 Bytes
82cdb59
 
 
 
c1fc133
 
 
82cdb59
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
ce9586e
c1fc133
 
 
 
82cdb59
 
 
 
 
 
 
c1fc133
82cdb59
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
ce9586e
 
82cdb59
 
 
 
 
 
 
 
ce9586e
82cdb59
 
 
 
 
 
 
 
 
 
c1fc133
82cdb59
c1fc133
 
82cdb59
 
 
 
 
 
 
 
 
 
c1fc133
 
7b892a5
 
 
2eae9f0
 
c1fc133
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
9fdbff5
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
7b892a5
 
 
9fdbff5
 
 
 
 
 
 
 
 
7b892a5
 
 
 
 
 
 
 
 
 
 
 
 
 
9fdbff5
7b892a5
9fdbff5
 
82cdb59
 
 
c1fc133
 
82cdb59
 
 
c1fc133
82cdb59
 
 
 
 
 
c1fc133
 
82cdb59
 
 
 
c1fc133
82cdb59
 
 
 
c1fc133
 
7b892a5
 
82cdb59
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
c1fc133
82cdb59
 
 
 
 
 
 
c1fc133
82cdb59
 
 
 
 
 
c1fc133
7b892a5
82cdb59
c1fc133
 
82cdb59
c1fc133
82cdb59
 
 
 
 
 
 
 
 
 
 
 
 
c1fc133
82cdb59
 
 
 
 
 
 
 
 
 
 
 
c1fc133
82cdb59
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
c1fc133
 
82cdb59
 
 
 
 
 
c1fc133
 
 
 
7b892a5
 
 
 
 
 
 
c1fc133
 
 
82cdb59
 
 
c1fc133
82cdb59
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
c1fc133
 
 
 
 
 
 
 
 
 
7b892a5
c1fc133
7b892a5
c1fc133
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
816fa8a
2eae9f0
 
 
 
 
 
 
 
816fa8a
c1fc133
 
 
 
2eae9f0
c1fc133
2eae9f0
 
 
 
c1fc133
2eae9f0
82cdb59
c1fc133
 
 
7b892a5
c1fc133
7b892a5
c1fc133
82cdb59
 
2eae9f0
 
 
 
 
 
 
 
 
 
 
 
 
816fa8a
2eae9f0
ce9586e
 
2eae9f0
 
 
 
 
 
 
 
 
 
816fa8a
 
 
 
 
 
 
2eae9f0
 
 
 
 
c1fc133
82cdb59
 
 
c1fc133
 
 
ce9586e
c1fc133
 
 
 
 
 
 
 
 
ce9586e
 
 
c1fc133
 
 
 
 
 
 
 
 
 
 
 
 
 
2eae9f0
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
c1fc133
 
 
 
 
ce9586e
 
 
 
c1fc133
 
 
2eae9f0
 
 
 
 
 
 
 
c1fc133
 
 
82cdb59
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
// 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;
};