File size: 27,934 Bytes
f530297
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
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
/**
 * ⭐⭐ WAVE 50 Β· W50-T06 (owner instruction 5, ruling R5) β€” THE FORMULA EDITOR.
 *
 * Owner, verbatim: *"Formula field editor is wider when configuring (it expands) and taller by
 * default; it behaves like Excel: guides the user, autocompletes functions like SUM, offers each
 * formula's form like =SUM([select field]); copy how Excel documents it."*
 *
 * β›”β›” THE ONE PLACE THIS FILE DOES NOT DO WHAT THE OWNER LITERALLY WROTE IS THE FIELD-REFERENCE
 * SPELLING, AND THAT IS DELIBERATE. `[Field]` is REJECTED BY THE RUNTIME ON PURPOSE
 * (`formula_runtime.PARSE_ERROR_FIXTURES["legacy_square_reference"]`). The syntax is `{key}`: the
 * picker INSERTS THE KEY and DISPLAYS THE NAME. Implementing the bracket spelling would produce
 * an editor that autocompletes its user straight into a parse error, which is worse than no
 * autocomplete at all.
 *
 * β›”β›” THE FUNCTION LIST IS NEVER HARD-CODED HERE. It arrives from the C2 catalogue route, which
 * ruling R5 made the ONE source shared by this editor and the Sheet. A typed-out list in a
 * component is stale the day the runtime gains a function, and a stale list is worse than an
 * absent one: it offers names that fail. So with the route ABSENT this editor shows NO LIST AND
 * NO ERROR β€” the catalogue is simply empty, every code path is total, and the textarea keeps
 * working exactly as it did before autocomplete existed. That degradation is the ticket's stated
 * observable, not an accident.
 *
 * β›” THE GRID HIDES THE SHEET-ONLY FUNCTIONS, and the reason is the rule. A grid formula computes
 * ONE ROW at a time: it has no cells to address and no range to fold, so SUMIF, VLOOKUP, INDEX,
 * MATCH, RANK, ROW and the rest of the 24 range functions could only ever fail here. Offering
 * them would be a fake affordance. The catalogue says which world each function belongs to
 * (`contexts`), so the filter is data, not a second list to maintain.
 *
 * ⚠ WHY THIS IS ITS OWN MODULE. `ColumnMenu.tsx` pulls the whole grid and cannot load under a
 * node gate (see its own header). The pure half of this file β€” normalising the catalogue,
 * matching a token, finding the call the caret sits in, stripping a leading `=` β€” is exported so
 * `verify_ui.py` can compile THIS file alone and run it against a frozen C2 fixture.
 */
import { useCallback, useEffect, useId, useMemo, useRef, useState } from "react";
import type { KeyboardEvent as ReactKeyboardEvent } from "react";
import { API_V1, CREDENTIALS } from "../apiContract";
import { AnchoredOverlay } from "./OverlaySurface";
import type { AnchorRect } from "./OverlaySurface";

/* ═══ CONTRACT C2 ══════════════════════════════════════════════════════════════════════════ */

/** The catalogue route. ⚠ It may legitimately 404: it ships in a different lane. */
export const FORMULA_CATALOGUE_ROUTE = `${API_V1}/query/formula-catalogue`;

/** The world this editor authors for. A function whose `contexts` lacks it never appears. */
export const FORMULA_CONTEXT = "grid";

/** The ONLY field-reference syntax the formula runtime parses. See the header. */
export const SUPPORTED_FIELD_REF = "{key}";

export interface CatalogueFunction {
  name: string;
  category: string;
  signature: string;
  description: string;
  minArgs: number;
  maxArgs: number | null;
  contexts: string[];
  aliases: string[];
}

export interface FormulaCatalogue {
  functions: CatalogueFunction[];
  fieldRef: string;
}

/** β›” The shape returned for EVERY failure: no route, a 404, a non-JSON body, a garbage payload.
 *  `fieldRef` is the supported syntax rather than the empty string, because the `{` picker is
 *  local knowledge and must keep working when the catalogue does not. */
export const EMPTY_CATALOGUE: FormulaCatalogue = {
  functions: [],
  fieldRef: SUPPORTED_FIELD_REF,
};

function asText(value: unknown): string {
  return typeof value === "string" ? value : "";
}

function asTextList(value: unknown): string[] {
  return Array.isArray(value)
    ? value.filter((item): item is string => typeof item === "string")
    : [];
}

function asCount(value: unknown, fallback: number): number {
  return typeof value === "number" && Number.isFinite(value) ? value : fallback;
}

/**
 * β›” TOTAL BY CONSTRUCTION: any input at all, including `undefined`, a string, a number or a
 * half-shaped object, returns a usable catalogue and never throws. An editor that can throw
 * while reading a response turns a missing route into a red console and a blank pane, which is
 * precisely the "no list and no error" clause failing.
 *
 * β›” `signature` and `description` PASS THROUGH VERBATIM. They are the catalogue's words, written
 * once at the source R5 named, and this editor is a display of them. Rewriting or re-deriving
 * either here would put two authorities on the same sentence.
 */
export function catalogueFromResponse(raw: unknown): FormulaCatalogue {
  if (!raw || typeof raw !== "object" || Array.isArray(raw)) return EMPTY_CATALOGUE;
  const body = raw as Record<string, unknown>;
  const rows = Array.isArray(body.functions) ? body.functions : [];
  const functions: CatalogueFunction[] = [];
  for (const row of rows) {
    if (!row || typeof row !== "object" || Array.isArray(row)) continue;
    const item = row as Record<string, unknown>;
    const name = asText(item.name).trim();
    if (!name) continue;
    const contexts = asTextList(item.contexts);
    // β›” THE GRID FILTER. See the header: a range function has nothing to range over on a row.
    if (!contexts.includes(FORMULA_CONTEXT)) continue;
    functions.push({
      name,
      category: asText(item.category),
      signature: asText(item.signature),
      description: asText(item.description),
      minArgs: asCount(item.minArgs, 0),
      maxArgs:
        typeof item.maxArgs === "number" && Number.isFinite(item.maxArgs)
          ? item.maxArgs
          : null,
      contexts,
      aliases: asTextList(item.aliases),
    });
  }
  return {
    functions,
    fieldRef: asText(body.fieldRef) || SUPPORTED_FIELD_REF,
  };
}

/**
 * The reference text for one field key, or `null` when the catalogue declares a field-reference
 * syntax this editor cannot author.
 *
 * β›” NULL IS THE HONEST ANSWER, NOT A FALLBACK TO OUR OWN GUESS. If the server one day says the
 * syntax is `[Name]`, inserting `{key}` anyway would write formulas that the server itself
 * refuses; inserting `[Name]` would write formulas the CURRENT runtime refuses. Declining to
 * insert is the only branch that cannot author a broken formula, and the textarea still accepts
 * anything typed by hand.
 */
export function fieldReferenceFor(catalogue: FormulaCatalogue, key: string): string | null {
  if (catalogue.fieldRef !== SUPPORTED_FIELD_REF) return null;
  return "{" + key + "}";
}

/* ═══ THE PURE EDITOR MODEL ════════════════════════════════════════════════════════════════ */

/** Six rows by default, and it grows with the content. Owner instruction 5's "taller". */
export const FORMULA_MIN_ROWS = 6;
/** ⚠ A CEILING, because the pane scrolls and a 40-line formula must not push Save off it. */
export const FORMULA_MAX_ROWS = 18;
export const SUGGESTION_LIMIT = 8;

export function editorRows(text: string): number {
  const lines = (typeof text === "string" ? text : "").split(String.fromCharCode(10)).length;
  return Math.min(FORMULA_MAX_ROWS, Math.max(FORMULA_MIN_ROWS, lines + 1));
}

/**
 * ⭐ Excel accepts a leading `=` because every Excel user types one. The runtime does not, so it
 * is STRIPPED ON SAVE rather than refused on entry: refusing it would mean the first thing a
 * spreadsheet user does in this box produces an error message.
 *
 * ⚠ It strips ONE leading `=` and the whitespace around it. `==` is not a formula in any
 * dialect, so the second character is left for the validator to complain about honestly.
 */
export function stripLeadingEquals(text: string): string {
  return typeof text === "string" ? text.replace(/^\s*=\s*/, "") : "";
}

export type CaretTokenKind = "function" | "field" | null;

export interface CaretToken {
  kind: CaretTokenKind;
  /** What has been typed so far, without the opening `{` for a field. */
  token: string;
  /** Where the replaceable run STARTS, so accepting a suggestion overwrites it exactly. */
  start: number;
}

const NAME_CHAR = /[A-Za-z0-9_]/;
const NAME_HEAD = /^[A-Za-z]/;

/**
 * What is being typed at the caret: a function name, a field reference, or nothing.
 *
 * ⚠ THE FIELD BRANCH WINS, and it has to. Inside `{rev` the run back from the caret is also a
 * legal function-name run, so testing for a function first would offer REVENUE-shaped function
 * names inside a field reference.
 */
export function caretToken(text: string, caret: number): CaretToken {
  const source = typeof text === "string" ? text : "";
  const at = Math.max(0, Math.min(caret, source.length));
  const head = source.slice(0, at);
  const brace = head.lastIndexOf("{");
  if (brace >= 0 && !head.slice(brace).includes("}"))
    return { kind: "field", token: head.slice(brace + 1), start: brace };
  let i = at;
  while (i > 0 && NAME_CHAR.test(head[i - 1])) i -= 1;
  const word = head.slice(i, at);
  if (!word || !NAME_HEAD.test(word)) return { kind: null, token: "", start: at };
  return { kind: "function", token: word, start: i };
}

export interface FormulaSuggestion {
  fn: CatalogueFunction;
  /** The alias that matched, when the typed text is not a prefix of the real name. */
  alias: string | null;
}

/**
 * ⭐ `aliases` is why CONCAT finds CONCATENATE. A name match sorts above an alias match so the
 * canonical spelling is never buried under a synonym of itself.
 */
export function matchFunctions(
  catalogue: FormulaCatalogue,
  token: string,
  limit: number = SUGGESTION_LIMIT
): FormulaSuggestion[] {
  const needle = (typeof token === "string" ? token : "").trim().toUpperCase();
  if (!needle) return [];
  const found: FormulaSuggestion[] = [];
  for (const fn of catalogue.functions) {
    if (fn.name.toUpperCase().startsWith(needle)) {
      found.push({ fn, alias: null });
      continue;
    }
    const alias = fn.aliases.find((name) => name.toUpperCase().startsWith(needle));
    if (alias) found.push({ fn, alias });
  }
  found.sort(
    (a, b) =>
      (a.alias ? 1 : 0) - (b.alias ? 1 : 0) || a.fn.name.localeCompare(b.fn.name)
  );
  return found.slice(0, limit);
}

export interface FormulaField {
  key: string;
  label: string;
  type?: string;
}

export function matchFields(
  fields: FormulaField[],
  token: string,
  limit: number = SUGGESTION_LIMIT
): FormulaField[] {
  const needle = (typeof token === "string" ? token : "").trim().toLowerCase();
  const rows = Array.isArray(fields) ? fields : [];
  const hit = rows.filter(
    (f) =>
      !needle ||
      f.key.toLowerCase().includes(needle) ||
      (f.label || "").toLowerCase().includes(needle)
  );
  return hit.slice(0, limit);
}

/**
 * The function whose argument list the caret currently sits inside, so the signature can be
 * shown while it is being filled in. This is Excel's parameter tooltip.
 *
 * ⚠ It scans backwards counting depth, so `ROUND(SUM(1, 2), ` correctly reports ROUND. It does
 * NOT understand string literals: a `(` inside `"a(b"` would be counted. That costs a wrong
 * hint on an unusual literal and nothing else, and the alternative is a second parser beside
 * `formulaEngine.ts`, which is another lane's file and the wrong place for two of them.
 */
export function activeCall(
  text: string,
  caret: number,
  catalogue: FormulaCatalogue
): CatalogueFunction | null {
  const source = typeof text === "string" ? text : "";
  const head = source.slice(0, Math.max(0, Math.min(caret, source.length)));
  let depth = 0;
  for (let i = head.length - 1; i >= 0; i -= 1) {
    const ch = head[i];
    if (ch === ")") {
      depth += 1;
    } else if (ch === "(") {
      if (depth > 0) {
        depth -= 1;
        continue;
      }
      let j = i;
      while (j > 0 && NAME_CHAR.test(head[j - 1])) j -= 1;
      const name = head.slice(j, i).toUpperCase();
      if (!name) return null;
      return (
        catalogue.functions.find(
          (fn) =>
            fn.name.toUpperCase() === name ||
            fn.aliases.some((alias) => alias.toUpperCase() === name)
        ) ?? null
      );
    }
  }
  return null;
}

export interface Splice {
  text: string;
  caret: number;
}

/** Accepting a function writes `NAME(` and leaves the caret inside the call, which is what makes
 *  the signature appear on the very next render. */
export function insertFunction(
  text: string,
  start: number,
  caret: number,
  name: string
): Splice {
  const source = typeof text === "string" ? text : "";
  const head = source.slice(0, Math.max(0, start));
  const tail = source.slice(Math.max(0, Math.min(caret, source.length)));
  const written = name + "(";
  return { text: head + written + tail, caret: head.length + written.length };
}

/** Accepting a field overwrites the partial `{typed` with the WHOLE `{key}`, closing brace and
 *  all, so there is never a half-open reference left behind. */
export function insertFieldRef(
  text: string,
  start: number,
  caret: number,
  reference: string
): Splice {
  const source = typeof text === "string" ? text : "";
  const head = source.slice(0, Math.max(0, start));
  const tail = source.slice(Math.max(0, Math.min(caret, source.length)));
  return { text: head + reference + tail, caret: head.length + reference.length };
}

/* ═══ THE CATALOGUE FETCH ══════════════════════════════════════════════════════════════════ */

let cachedCatalogue: FormulaCatalogue | null = null;
let inflight: Promise<FormulaCatalogue> | null = null;

/**
 * Fetched once and cached for the session: the catalogue is a constant of the build, and the
 * editor opens every time a formula column is created or edited.
 *
 * β›” A FAILURE IS NEVER CACHED. Caching the empty catalogue would mean that one 404 during
 * startup disables autocomplete for the rest of the session, including after the route starts
 * answering. Only a catalogue that actually carries functions is kept.
 */
export async function loadFormulaCatalogue(): Promise<FormulaCatalogue> {
  if (cachedCatalogue) return cachedCatalogue;
  if (!inflight) {
    inflight = (async () => {
      try {
        const response = await fetch(FORMULA_CATALOGUE_ROUTE, { credentials: CREDENTIALS });
        if (!response.ok) return EMPTY_CATALOGUE;
        return catalogueFromResponse(await response.json());
      } catch {
        return EMPTY_CATALOGUE;
      }
    })().then((catalogue) => {
      inflight = null;
      if (catalogue.functions.length) cachedCatalogue = catalogue;
      return catalogue;
    });
  }
  return inflight;
}

/* ═══ THE CARET RECTANGLE ══════════════════════════════════════════════════════════════════ */

const MIRROR_PROPS = [
  "font-family",
  "font-size",
  "font-weight",
  "font-style",
  "letter-spacing",
  "line-height",
  "text-transform",
  "text-indent",
  "padding-top",
  "padding-right",
  "padding-bottom",
  "padding-left",
  "border-top-width",
  "border-right-width",
  "border-bottom-width",
  "border-left-width",
  "box-sizing",
  "tab-size",
];

/**
 * Where the caret is on screen, so the listbox can sit UNDER IT rather than under the whole box.
 *
 * ⚠ A textarea exposes no caret geometry, so the standard technique is the only one: mirror the
 * box into a hidden div with the same metrics, put a marker at the caret offset, and measure the
 * marker. It runs only while the list is open.
 *
 * β›” IT DEGRADES TO THE TEXTAREA'S OWN BOTTOM EDGE and never throws. A mis-measured popover is a
 * cosmetic problem; an exception thrown from a render path unmounts the application.
 */
export function caretAnchorRect(area: HTMLTextAreaElement): AnchorRect {
  const box = area.getBoundingClientRect();
  const fallback: AnchorRect = {
    left: box.left,
    top: box.bottom,
    right: box.left,
    bottom: box.bottom,
    width: 0,
    height: 0,
  };
  try {
    const style = window.getComputedStyle(area);
    const mirror = document.createElement("div");
    for (const prop of MIRROR_PROPS)
      mirror.style.setProperty(prop, style.getPropertyValue(prop));
    mirror.style.position = "absolute";
    mirror.style.left = "0";
    mirror.style.top = "0";
    mirror.style.visibility = "hidden";
    mirror.style.whiteSpace = "pre-wrap";
    mirror.style.overflowWrap = "break-word";
    mirror.style.width = box.width + "px";
    mirror.style.height = "auto";
    const at = area.selectionStart ?? area.value.length;
    mirror.appendChild(document.createTextNode(area.value.slice(0, at)));
    const marker = document.createElement("span");
    marker.textContent = area.value.slice(at) || ".";
    mirror.appendChild(marker);
    document.body.appendChild(mirror);
    const markerBox = marker.getBoundingClientRect();
    const mirrorBox = mirror.getBoundingClientRect();
    document.body.removeChild(mirror);
    const left = box.left + (markerBox.left - mirrorBox.left) - area.scrollLeft;
    const lineTop = box.top + (markerBox.top - mirrorBox.top) - area.scrollTop;
    // Clamped to the visible box: a caret scrolled out of view must not drag the list with it.
    const top = Math.max(box.top, Math.min(lineTop, box.bottom));
    const height = Math.max(1, Math.min(markerBox.height || 16, box.bottom - top));
    return {
      left: Math.max(box.left, Math.min(left, box.right)),
      top,
      right: Math.max(box.left, Math.min(left, box.right)),
      bottom: top + height,
      width: 0,
      height,
    };
  } catch {
    return fallback;
  }
}

/* ═══ THE COMPONENT ════════════════════════════════════════════════════════════════════════ */

export interface FormulaEditorProps {
  value: string;
  onChange: (next: string) => void;
  /** The columns this formula may reference, offered by the `{` picker. */
  fields: FormulaField[];
  placeholder?: string;
  ariaLabel?: string;
}

export function FormulaEditor({
  value,
  onChange,
  fields,
  placeholder,
  ariaLabel = "Formula",
}: FormulaEditorProps) {
  const areaRef = useRef<HTMLTextAreaElement>(null);
  const listId = useId();
  const [catalogue, setCatalogue] = useState<FormulaCatalogue>(EMPTY_CATALOGUE);
  const [caret, setCaret] = useState(0);
  const [open, setOpen] = useState(false);
  const [active, setActive] = useState(0);
  const [anchor, setAnchor] = useState<AnchorRect | null>(null);
  // The caret splice a suggestion asks for, applied after React has written the new value:
  // setting `selectionStart` on a textarea React is about to re-render is undone by the render.
  const pendingCaret = useRef<number | null>(null);

  useEffect(() => {
    let alive = true;
    void loadFormulaCatalogue().then((next) => {
      if (alive) setCatalogue(next);
    });
    return () => {
      alive = false;
    };
  }, []);

  const token = useMemo(() => caretToken(value, caret), [value, caret]);
  const functionHits = useMemo(
    () => (token.kind === "function" ? matchFunctions(catalogue, token.token) : []),
    [catalogue, token]
  );
  const fieldHits = useMemo(
    () => (token.kind === "field" ? matchFields(fields, token.token) : []),
    [fields, token]
  );
  const rows = token.kind === "field" ? fieldHits.length : functionHits.length;
  // β›” THE WHOLE "NO LIST AND NO ERROR" CLAUSE IS THIS ONE EXPRESSION. With the route absent
  // `catalogue.functions` is empty, so `functionHits` is empty, so `showList` is false: the
  // listbox never mounts and nothing anywhere renders a message about it.
  const showList = open && rows > 0;
  const signature = useMemo(
    () => activeCall(value, caret, catalogue),
    [value, caret, catalogue]
  );

  const syncCaret = useCallback((area: HTMLTextAreaElement | null) => {
    if (!area) return;
    setCaret(area.selectionStart ?? 0);
    setAnchor(caretAnchorRect(area));
  }, []);

  useEffect(() => {
    const area = areaRef.current;
    if (!area || pendingCaret.current === null) return;
    const at = pendingCaret.current;
    pendingCaret.current = null;
    area.setSelectionRange(at, at);
    setCaret(at);
    setAnchor(caretAnchorRect(area));
  }, [value]);

  useEffect(() => {
    if (active >= rows) setActive(0);
  }, [active, rows]);

  const accept = useCallback(
    (index: number) => {
      const area = areaRef.current;
      if (!area) return;
      const current = caretToken(value, area.selectionStart ?? caret);
      const at = area.selectionStart ?? caret;
      let splice: Splice | null = null;
      if (current.kind === "field") {
        const field = fieldHits[index];
        if (!field) return;
        const reference = fieldReferenceFor(catalogue, field.key);
        if (!reference) return;
        splice = insertFieldRef(value, current.start, at, reference);
      } else if (current.kind === "function") {
        const hit = functionHits[index];
        if (!hit) return;
        splice = insertFunction(value, current.start, at, hit.fn.name);
      }
      if (!splice) return;
      pendingCaret.current = splice.caret;
      onChange(splice.text);
      setOpen(false);
      setActive(0);
      area.focus();
    },
    [caret, catalogue, fieldHits, functionHits, onChange, value]
  );

  const onKeyDown = (event: ReactKeyboardEvent<HTMLTextAreaElement>) => {
    if (!showList) {
      if (event.key === "Escape" && open) setOpen(false);
      return;
    }
    if (event.key === "ArrowDown") {
      event.preventDefault();
      setActive((n) => (n + 1) % rows);
      return;
    }
    if (event.key === "ArrowUp") {
      event.preventDefault();
      setActive((n) => (n - 1 + rows) % rows);
      return;
    }
    // The PRD's interaction layer, verbatim: "Enter/Tab accept, Escape closes."
    if (event.key === "Enter" || event.key === "Tab") {
      event.preventDefault();
      accept(active);
      return;
    }
    if (event.key === "Escape") {
      event.preventDefault();
      setOpen(false);
    }
  };

  const optionId = (index: number) => `${listId}-opt-${index}`;

  return (
    <div className="cg-formula-wrap">
      <textarea
        ref={areaRef}
        className="cg-input cg-formula-editor"
        rows={editorRows(value)}
        value={value}
        placeholder={placeholder}
        aria-label={ariaLabel}
        // β›” NOT `role="combobox"`. The element is a real multi-line text field and stays one;
        // the relationship to the list is stated with the three properties that describe it,
        // which is what lets the caret keep working normally while the list is up.
        aria-expanded={showList}
        aria-controls={showList ? listId : undefined}
        aria-activedescendant={showList ? optionId(active) : undefined}
        aria-autocomplete="list"
        onChange={(event) => {
          onChange(event.target.value);
          setOpen(true);
          setActive(0);
          setCaret(event.target.selectionStart ?? 0);
          setAnchor(caretAnchorRect(event.target));
        }}
        onKeyUp={(event) => syncCaret(event.currentTarget)}
        onClick={(event) => syncCaret(event.currentTarget)}
        onFocus={(event) => syncCaret(event.currentTarget)}
        onKeyDown={onKeyDown}
        onBlur={() => setOpen(false)}
      />
      {/* ⭐ THE SIGNATURE, WHICH IS "COPY HOW EXCEL DOCUMENTS IT". The form and the sentence are
          the catalogue's own words (R5's shared source) shown verbatim, so the editor and the
          Sheet document a function identically and neither owns a second copy of the prose.

          ⚠ It floats over the row beneath rather than pushing it down: a hint that reflows the
          pane every time the caret crosses a bracket is worse than no hint. `pointer-events` is
          off in the stylesheet so the control it covers stays clickable. */}
      {signature ? (
        <div className="cg-formula-sig" role="status" aria-live="polite">
          <span className="cg-formula-sig-form">{signature.signature || signature.name}</span>
          {signature.description ? (
            <span className="cg-formula-sig-desc">{signature.description}</span>
          ) : null}
        </div>
      ) : null}
      {showList ? (
        <AnchoredOverlay
          anchor={anchor}
          className="cg-formula-ac"
          onDismiss={() => setOpen(false)}
          role="listbox"
          ariaLabel={token.kind === "field" ? "Matching fields" : "Matching functions"}
          id={listId}
          // β›” THE CARET MUST NOT MOVE. `useOverlayLayer` focuses the panel's first focusable
          // element a frame after it mounts and restores focus on unmount; both would take the
          // caret out of the formula mid-word. The textarea keeps focus, so the list is driven
          // entirely from its own key handler.
          initialFocus="none"
          restoreFocus={false}
        >
          {token.kind === "field"
            ? fieldHits.map((field, index) => (
                <div
                  key={field.key}
                  id={optionId(index)}
                  role="option"
                  aria-selected={index === active}
                  className={
                    "cg-formula-ac-opt" + (index === active ? " is-active" : "")
                  }
                  // ⚠ `mousedown`, not `click`: the textarea's blur closes the list, and blur
                  // fires before click. `preventDefault` stops the focus moving at all.
                  onMouseDown={(event) => {
                    event.preventDefault();
                    accept(index);
                  }}
                  onMouseEnter={() => setActive(index)}
                >
                  {/* The NAME is what a person recognises; the KEY is what gets inserted, and it
                      is shown beside it so the reference in the box is never a mystery. */}
                  <span className="cg-formula-ac-name">{field.label || field.key}</span>
                  <span className="cg-formula-ac-sig">{"{" + field.key + "}"}</span>
                </div>
              ))
            : functionHits.map((hit, index) => (
                <div
                  key={hit.fn.name}
                  id={optionId(index)}
                  role="option"
                  aria-selected={index === active}
                  className={
                    "cg-formula-ac-opt" + (index === active ? " is-active" : "")
                  }
                  onMouseDown={(event) => {
                    event.preventDefault();
                    accept(index);
                  }}
                  onMouseEnter={() => setActive(index)}
                >
                  <span className="cg-formula-ac-name">{hit.fn.name}</span>
                  <span className="cg-formula-ac-sig">
                    {hit.fn.signature || hit.fn.name}
                  </span>
                  {hit.alias ? (
                    <span className="cg-formula-ac-alias">
                      {"also " + hit.alias}
                    </span>
                  ) : null}
                </div>
              ))}
        </AnchoredOverlay>
      ) : null}
    </div>
  );
}