loopable / web /src /sheet /PivotTab.tsx
fsanyoto's picture
Deploy AIOS web (React glide grid + FastAPI slice)
9e15897 verified
Raw History Blame Contribute Delete
19.7 kB
// 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<GridLimit> : {};
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<string>): 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<HTMLDivElement>(null);
const titleId = useId();
useOverlayLayer({ panelRef, onDismiss: onClose, dismissOnOutside: false,
initialFocus: "first", restoreFocus: true, trapFocus: true });
return <BodyPortal><div className="lp-dialog-scrim">
<div ref={panelRef} className="lp-dialog" role="dialog" aria-modal="true" aria-labelledby={titleId}
>
<div className="lp-dialog-head">
<h2 id={titleId} className="lp-dialog-title">Filters</h2>
<button type="button" className="lp-dialog-x" aria-label="Close filters" onClick={onClose}>×</button>
</div>
<div className="lp-dialog-body">{children}</div>
<div className="lp-dialog-actions">
<button type="button" className="lp-dialog-btn lp-dialog-btn-primary" onClick={onClose}>Done</button>
</div>
</div>
</div></BodyPortal>;
}
function PivotFilters({ database, fields, userOptions, nodes, onChange }: {
database: string; fields: Field[]; userOptions: string[]; nodes: FilterNode[];
onChange: (nodes: FilterNode[]) => void;
}) {
const [valuesState, setValuesState] = useState<Record<string, FilterValuesState>>({});
const values = useRef<Record<string, FilterValuesState>>({});
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 <FilterFieldStatusContext.Provider value={{ relatedPending: false, unavailable: [],
valuesState, wantValues: (key, search) => { void loadValues(key, search); },
moreValues: key => { void loadValues(key, values.current[key]?.search, true); } }}>
<FilterBuilderPanel database={database} fields={fields} filters={filters} statusValues={statusValues}
userOptions={userOptions} onChange={tree => onChange(pivotFilterNodes(tree))} />
</FilterFieldStatusContext.Provider>;
}
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<string | null>(null);
const limitTrigger = useRef<HTMLButtonElement>(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 <section className="sheet-grid-wrap" aria-label="PivotTable" data-sheet-kind="pivot">
<div className="sheet-grid-head">
<label className="sheet-field" style={{ flex: "1 1 auto" }}>
<span>Database</span>
<select aria-label="Pivot database" value={allowed ? spec.database : ""} disabled={readOnly}
onChange={event => { setSearch(""); setFiltersOpen(false); change({ database: event.target.value,
rows: [], columns: [], values: [], filters: [] }); }}>
<option value="">Choose a database</option>
{databases.map(database => <option key={database.key} value={database.key}>{database.label}</option>)}
</select>
</label>
<button type="button" className="lp-btn lp-btn-quiet" disabled={!spec.database || !allowed}
onClick={() => { setFiltersOpen(false); setSchema(null); setAggregate(null); setRefresh(value => value + 1); }}>
Refresh
</button>
</div>
<div style={{ display: "flex", flex: "1 1 auto", minHeight: 0, overflow: "auto", flexWrap: "wrap" }}>
<aside aria-label="Pivot fields" className="cg-detail-section" style={{ flex: "0 1 var(--lp-rail-w)", minWidth: 0,
fontSize: "var(--lp-fs-xs)",
borderRight: "1px solid var(--lp-line)", overflow: "auto" }}>
<h2 className="sheet-binding-title">Fields</h2>
<label className="sheet-field">
<span>Find a field</span>
<input aria-label="Find a pivot field" value={search} onChange={event => setSearch(event.target.value)} />
</label>
<div style={{ maxHeight: "12rem", overflow: "auto" }}>
{fields.filter(field => field.label.toLowerCase().includes(search.toLowerCase())).map(field =>
<div key={field.key} className="pg-list-item" style={{ alignItems: "center", flexWrap: "wrap" }}>
<FieldTypeIcon type={field.type} />
<span style={{ flex: "1 1 auto", fontSize: "var(--lp-fs-xs)", color: "var(--lp-ink)" }}>{field.label}</span>
{!readOnly && <span style={{ display: "flex", flexWrap: "wrap" }}>
{(["rows", "columns", "values"] as const).map(area => <button key={area} type="button"
className="lp-btn lp-btn-quiet" aria-label={`Add ${field.label} to ${area}`}
disabled={area !== "values" && spec[area].includes(field.key)} onClick={() => addField(field, area)}>
{area === "rows" ? "Rows" : area === "columns" ? "Columns" : "Values"}
</button>)}
</span>}
</div>)}
</div>
{(["rows", "columns"] as const).map(area => <fieldset key={area} className="sheet-scope" disabled={readOnly || !schemaReady}>
<legend>{area === "rows" ? "Rows" : "Columns"}</legend>
{spec[area].map((key, index) => <div key={`${key}:${index}`} className="sheet-field-row" style={{ alignItems: "center" }}>
<span style={{ flex: "1 1 auto" }}>{label(key)}</span>
{!readOnly && <button type="button" className="lp-btn lp-btn-quiet" aria-label={`Remove ${area} field ${index + 1}`}
onClick={() => change({ ...spec, [area]: spec[area].filter((_, i) => i !== index) })}>Remove</button>}
</div>)}
</fieldset>)}
<fieldset className="sheet-scope" disabled={readOnly || !schemaReady}>
<legend>Values</legend>
{spec.values.map((value, index) => <div key={`${value.field}:${index}`}>
<label className="sheet-field">
<span>{label(value.field)}</span>
<select aria-label={`Aggregate for value ${index + 1}`} value={value.agg}
onChange={event => change({ ...spec, values: spec.values.map((held, i) => i === index
? { ...held, agg: event.target.value as PivotSpec["values"][number]["agg"] } : held) })}>
{AGGREGATES.map(([key, name]) => <option key={key} value={key}>{name}</option>)}
</select>
</label>
{!readOnly && <button type="button" className="lp-btn lp-btn-quiet" aria-label={`Remove value ${index + 1}`}
onClick={() => change({ ...spec, values: spec.values.filter((_, i) => i !== index) })}>Remove</button>}
</div>)}
</fieldset>
<fieldset className="sheet-scope" disabled={readOnly || !schemaReady}>
<legend>Filters</legend>
{scoped ? <button type="button" className="lp-btn lp-btn-quiet" aria-label="Edit pivot filters"
onClick={() => setFiltersOpen(true)}>Filters</button> : schemaReady && <>
<span role="status">Unavailable</span>
{!readOnly && <button type="button" className="lp-btn lp-btn-quiet"
onClick={() => change({ ...spec, filters: [] })}>Clear filters</button>}
</>}
</fieldset>
</aside>
<div aria-label="Pivot results" aria-busy={status === "Loading"}
style={{ flex: "1 1 var(--lp-rail-w)", minWidth: 0, overflow: "auto", color: "var(--lp-ink)", fontSize: "var(--lp-fs-xs)" }}>
{status && <p className="sheet-placeholder-note" role={current?.error ? "alert" : "status"}>{status}</p>}
{answer && <>
<div className="sheet-grid-head" role="status" style={{ fontVariantNumeric: "tabular-nums" }}>
<span>{`${numberFormat.format(answer.total_rows)} records`}</span>
{limitNote && <>
<button ref={limitTrigger} type="button" className="lp-btn lp-btn-quiet"
title={limitText} aria-describedby={limitOpenKey === resultKey ? limitTipId : undefined}
onMouseEnter={() => setLimitOpenKey(resultKey)}
onMouseLeave={event => {
if (document.activeElement !== event.currentTarget) setLimitOpenKey(null);
}}
onFocus={() => setLimitOpenKey(resultKey)} onBlur={() => setLimitOpenKey(null)}>Limited</button>
{limitOpenKey === resultKey && <AnchoredOverlay anchor={limitTrigger.current}
id={limitTipId} className="cg-pop sheet-pivot-limit-tip" role="tooltip"
initialFocus="none" restoreFocus={false} onDismiss={() => setLimitOpenKey(null)}>
{limitText}
</AnchoredOverlay>}
</>}
</div>
<table aria-label="PivotTable results" className="pg-table" style={{ fontVariantNumeric: "tabular-nums" }}>
<thead><tr>
<th scope="col">
{spec.rows.map(label).join(" / ") || "Total"}
</th>
{answer.columns.map((column, index) => <th key={index} scope="col"
className="is-right">{pivotAxisLabel(column)}</th>)}
</tr></thead>
<tbody>{answer.rows.map((row, rowIndex) => <tr key={rowIndex}>
<th scope="row" style={{ fontWeight: 500 }}>{pivotAxisLabel(row)}</th>
{answer.cells[rowIndex].map((cell, colIndex) => <td key={colIndex}
className="is-right" title={cell === null ? undefined : String(cell)}>
{cell === null ? "" : numberFormat.format(cell)}
</td>)}
</tr>)}</tbody>
</table>
</>}
</div>
</div>
{filtersOpen && scoped && !readOnly && <OverlayProvider>
<PivotFilterDialog onClose={() => setFiltersOpen(false)}>
<PivotFilters key={schemaKey} database={spec.database} fields={fields}
userOptions={schema?.userOptions ?? []} nodes={spec.filters}
onChange={filters => change({ ...spec, filters })} />
</PivotFilterDialog>
</OverlayProvider>}
</section>;
}