// S70-T17 / C6. This worksheet stores configuration only. The server owns all aggregation. import { useEffect, useId, useRef, useState } from "react"; import type { ReactNode } from "react"; import { API_V1, CREDENTIALS } from "../apiContract"; import { FieldTypeIcon } from "../customer-grid/icons"; import { limitSummary } from "../customer-grid/counts"; import { AnchoredOverlay, BodyPortal, OverlayProvider, useOverlayLayer } from "../customer-grid/OverlaySurface"; import { choiceVocabulary, isFilterGroup } from "../customer-grid/types"; import type { Field, FilterNode, FilterTree, GridLimit } from "../customer-grid/types"; import { FilterBuilderPanel, FilterFieldStatusContext } from "../filter-kit/FilterBuilderPanel"; import type { FilterValuesState } from "../filter-kit/FilterBuilderPanel"; import { isNumericType } from "../filter-kit/ops"; import { fetchDistinctValues } from "./sheetApi"; import { pivotAxisLabel, readPivotAnswer } from "./types"; import type { PivotAnswer, PivotSpec, SheetTab } from "./types"; const EMPTY_PIVOT: PivotSpec = { database: "", rows: [], columns: [], values: [], filters: [] }; const AGGREGATES = [ ["sum", "Sum"], ["count", "Count"], ["avg", "Average"], ["min", "Min"], ["max", "Max"], ] as const; const numberFormat = new Intl.NumberFormat(undefined, { maximumFractionDigits: 6 }); /** Keep unrecognised limits readable without showing their wire representation. */ function pivotLimits(limits: unknown): GridLimit[] { const entries = Array.isArray(limits) ? limits : limits && (typeof limits !== "object" || Object.keys(limits).length) ? [limits] : []; return entries.map(raw => { const entry = raw && typeof raw === "object" ? raw as Partial : {}; return { subject: typeof entry.subject === "string" ? entry.subject : "", effect: typeof entry.effect === "string" ? entry.effect : "unknown", cause: typeof entry.cause === "string" ? entry.cause : "A limit applies to this pivot.", recommendation: typeof entry.recommendation === "string" ? entry.recommendation : "", }; }); } function filtersAvailable(nodes: FilterNode[], keys: Set): boolean { return nodes.every(node => isFilterGroup(node) ? filtersAvailable(node.children, keys) : keys.has(node.colId) && (!node.rhs || !("colId" in node.rhs) || keys.has(node.rhs.colId))); } /** C6 stores a node list; an OR at the builder root must keep its conjunction in a group. */ export const pivotFilterNodes = (tree: FilterTree): FilterNode[] => tree.conj === "or" && tree.nodes.length ? [{ conj: "or", children: tree.nodes }] : tree.nodes; function PivotFilterDialog({ children, onClose }: { children: ReactNode; onClose: () => void }) { const panelRef = useRef(null); const titleId = useId(); useOverlayLayer({ panelRef, onDismiss: onClose, dismissOnOutside: false, initialFocus: "first", restoreFocus: true, trapFocus: true }); return

Filters

{children}
; } function PivotFilters({ database, fields, userOptions, nodes, onChange }: { database: string; fields: Field[]; userOptions: string[]; nodes: FilterNode[]; onChange: (nodes: FilterNode[]) => void; }) { const [valuesState, setValuesState] = useState>({}); const values = useRef>({}); const live = useRef(true); useEffect(() => { live.current = true; return () => { live.current = false; }; }, []); const loadValues = async (key: string, search = "", more = false) => { if (!fields.some(field => field.key === key)) return; const held = values.current[key]; if ((held?.pending && held.search === search) || (more && !held?.hasMore)) return; const offset = more && held ? held.offset : 0; const pending: FilterValuesState = { values: more && held ? held.values : [], search, offset, pending: true, loaded: false, hasMore: more && held ? held.hasMore : false }; values.current = { ...values.current, [key]: pending }; setValuesState(values.current); const got = await fetchDistinctValues(database, key, { offset, search, limit: 200 }); if (!live.current || values.current[key] !== pending) return; const next: FilterValuesState = got.ok ? { ...pending, values: [...new Set([...pending.values, ...got.value.values])], pending: false, loaded: true, hasMore: got.value.hasMore, offset: got.value.offset + got.value.limit, } : { ...pending, pending: false, error: "Unavailable" }; values.current = { ...values.current, [key]: next }; setValuesState(values.current); }; const statusValues = Object.fromEntries(fields.map(field => { const declared = choiceVocabulary(field, []); return [field.key, field.type === "user" ? [...new Set([...userOptions, ...(valuesState[field.key]?.values ?? [])])] : declared.length ? declared : valuesState[field.key]?.values ?? []]; })); // Unwrap only the single root group produced above. The builder can keep editing OR faithfully. const filters: FilterTree = nodes.length === 1 && isFilterGroup(nodes[0]) ? { conj: nodes[0].conj, nodes: nodes[0].children } : { conj: "and", nodes }; return { void loadValues(key, search); }, moreValues: key => { void loadValues(key, values.current[key]?.search, true); } }}> onChange(pivotFilterNodes(tree))} /> ; } export default function PivotTab({ tab, databases, readOnly, onChange }: { tab: SheetTab; databases: { key: string; label: string }[]; readOnly: boolean; onChange: (pivot: PivotSpec) => void; }) { const spec = tab.pivot ?? EMPTY_PIVOT; const [refresh, setRefresh] = useState(0); const [search, setSearch] = useState(""); const [filtersOpen, setFiltersOpen] = useState(false); const [limitOpenKey, setLimitOpenKey] = useState(null); const limitTrigger = useRef(null); const limitTipId = useId(); const [schema, setSchema] = useState<{ key: string; fields: Field[]; userOptions: string[]; error?: string; } | null>(null); const [aggregate, setAggregate] = useState<{ key: string; answer?: PivotAnswer; error?: string; } | null>(null); const allowed = databases.some(database => database.key === spec.database); // Refresh rechecks permission as well as data. No memo may outrun a changed field wall. const schemaKey = JSON.stringify([tab.id, spec.database, databases, refresh]); const resultKey = JSON.stringify([schemaKey, spec]); const schemaReady = allowed && schema?.key === schemaKey && !schema.error; const fields = schemaReady ? schema.fields : []; const keys = new Set(fields.map(field => field.key)); const scoped = schemaReady && spec.rows.every(key => keys.has(key)) && spec.columns.every(key => keys.has(key)) && spec.values.every(value => keys.has(value.field)) && filtersAvailable(spec.filters, keys); const ready = scoped && spec.values.length > 0; const current = ready && aggregate?.key === resultKey ? aggregate : null; useEffect(() => { const controller = new AbortController(); setSchema(null); setAggregate(null); if (!allowed) return () => controller.abort(); // Same schema-only door as BindingEditor. It declares fields/options without reading records. const scope = spec.database === "customer_data" ? "customer" : spec.database === "product_data" ? "product" : spec.database; void (async () => { try { const response = await fetch(`${API_V1}/workspace?scope=${encodeURIComponent(scope)}&fields_only=1`, { credentials: CREDENTIALS, signal: controller.signal }); const body = await response.json(); if (controller.signal.aborted) return; if (!response.ok || body?.workspace?.fieldsOnly !== true || !Array.isArray(body.workspace.fields)) { setSchema({ key: schemaKey, fields: [], userOptions: [], error: response.status === 403 ? "No access" : "Unavailable" }); return; } setSchema({ key: schemaKey, fields: body.workspace.fields, userOptions: Array.isArray(body.workspace.userOptions) ? body.workspace.userOptions : [] }); } catch { if (!controller.signal.aborted) setSchema({ key: schemaKey, fields: [], userOptions: [], error: "Unavailable" }); } })(); return () => controller.abort(); }, [schemaKey, allowed]); // All inputs to the read are in schemaKey. useEffect(() => { const controller = new AbortController(); setAggregate(null); if (!ready) return () => controller.abort(); void (async () => { try { const response = await fetch(`${API_V1}/sheets/pivot`, { method: "POST", credentials: CREDENTIALS, signal: controller.signal, headers: { "Content-Type": "application/json" }, body: JSON.stringify(spec), }); const raw = await response.json(); if (controller.signal.aborted) return; if (response.status === 403) { // A fresh permission refusal invalidates the earlier field offer as well as the matrix. setSchema({ key: schemaKey, fields: [], userOptions: [], error: "No access" }); } const answer = response.ok ? readPivotAnswer(raw) : null; // A named field limit keeps the offered fields; only a 403 means the access changed. const limit = (raw?.error?.code === "pivot_field_unsupported" || raw?.error?.code === "pivot_too_large") && typeof raw.error.message === "string" ? raw.error.message : null; setAggregate({ key: resultKey, ...(answer ? { answer } : { error: response.status === 403 ? "No access" : response.ok && raw?.ok === true ? "Invalid response" : limit ?? "Refused", }) }); } catch { if (!controller.signal.aborted) setAggregate({ key: resultKey, error: "Unavailable" }); } })(); return () => controller.abort(); }, [resultKey, ready]); const change = (next: PivotSpec) => { if (!readOnly) { setAggregate(null); onChange(next); } }; const label = (key: string) => fields.find(field => field.key === key)?.label ?? "Unavailable"; const addField = (field: Field, area: "rows" | "columns" | "values") => { if (area === "values") change({ ...spec, values: [...spec.values, { field: field.key, agg: isNumericType(field.type) ? "sum" : "count" }] }); else if (!spec[area].includes(field.key)) change({ ...spec, [area]: [...spec[area], field.key] }); }; const answer = current?.answer; const limits = answer?.limits; const limitNote = limitSummary(pivotLimits(limits), undefined, key => keys.has(key) ? label(key) : fields.find(field => field.label === key)?.label); const limitText = limitNote ? `${limitNote.short}\n\n${limitNote.full}` : ""; const status = !spec.database ? "Choose a database" : !allowed ? "No access" : schema?.key === schemaKey && schema.error ? schema.error : !schemaReady ? "Loading" : !scoped ? "Unavailable fields" : !spec.values.length ? "Choose a value" : current?.error ?? (!answer ? "Loading" : !answer.rows.length ? "No results" : ""); return
{status &&

{status}

} {answer && <>
{`${numberFormat.format(answer.total_rows)} records`} {limitNote && <> {limitOpenKey === resultKey && setLimitOpenKey(null)}> {limitText} } }
{answer.columns.map((column, index) => )} {answer.rows.map((row, rowIndex) => {answer.cells[rowIndex].map((cell, colIndex) => )} )}
{spec.rows.map(label).join(" / ") || "Total"} {pivotAxisLabel(column)}
{pivotAxisLabel(row)} {cell === null ? "" : numberFormat.format(cell)}
}
{filtersOpen && scoped && !readOnly && setFiltersOpen(false)}> change({ ...spec, filters })} /> }
; }