/** * ⭐⭐ 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; 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; 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 | 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 { 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(null); const listId = useId(); const [catalogue, setCatalogue] = useState(EMPTY_CATALOGUE); const [caret, setCaret] = useState(0); const [open, setOpen] = useState(false); const [active, setActive] = useState(0); const [anchor, setAnchor] = useState(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(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) => { 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 (