import { useState, useRef, useEffect, useCallback, useMemo } from "react"; import { useSQLQuery, useDiveState, useExport } from "@motherduck/react-sql-query"; import * as d3 from "d3"; import { BarChart, Bar, PieChart, Pie, Cell, AreaChart, Area, LineChart, Line, XAxis, YAxis, Tooltip, CartesianGrid, ResponsiveContainer, Legend } from "recharts"; import { ChevronDown, ChevronUp, ChevronRight, ChevronLeft, X, Download, BookOpen } from "lucide-react"; export const REQUIRED_DATABASES = [ { type: 'database', path: 'md:demo-db', alias: 'demo-db' } ]; const N = (v) => (v != null ? Number(v) : 0); const CHART_COLORS = ["#3b82f6","#10b981","#f59e0b","#8b5cf6","#f43f5e","#06b6d4","#84cc16","#f97316","#a78bfa","#34d399","#fb923c","#22d3ee"]; function fmtAxisTick(v, format) { const abs = Math.abs(v); if (format === "percent") return `${v.toFixed(0)}%`; const pfx = format === "currency" ? "$" : ""; if (abs >= 1e6) return `${pfx}${(v / 1e6).toFixed(1)}M`; if (abs >= 1e3) return `${pfx}${(v / 1e3).toFixed(0)}K`; return `${pfx}${Math.round(v)}`; } const OBT = `"demo-db"."mart"."mart_sales_obt"`; const NODE_MIN_W = 260; const NODE_DEFAULT_W = 420; const NODE_MIN_H = 260; const NODE_DEFAULT_H = 680; const NODE_COLLAPSED_W = 220; const NODE_HEADER_H = 42; const PAGE_SIZE = 15; const BOOLEAN_COLS = new Set(["product_is_discontinued"]); const DATE_DIMS = new Set(["order_date", "shipped_date"]); const METRIC_DEFS = { revenue: { expr: "SUM(line_revenue)", label: "Revenue", format: "currency", subtotalAgg: "sum" }, profit: { expr: "SUM(line_profit)", label: "Profit", format: "currency", subtotalAgg: "sum" }, cost: { expr: "SUM(line_cost)", label: "Cost", format: "currency", subtotalAgg: "sum" }, units_sold: { expr: "SUM(quantity_ordered)", label: "Units Sold", format: "integer", subtotalAgg: "sum" }, order_count: { expr: "COUNT(DISTINCT order_id)", label: "Orders", format: "integer", subtotalAgg: "sum" }, avg_margin_pct: { expr: "ROUND(AVG(profit_margin_pct), 1)", label: "Avg Margin %", format: "percent", subtotalAgg: "avg" }, lifetime_orders: { expr: "MAX(customer_rfm_frequency)", label: "Lifetime Orders", format: "integer", subtotalAgg: "max" }, lifetime_value: { expr: "ROUND(MAX(customer_rfm_monetary), 2)", label: "Lifetime Value", format: "currency", subtotalAgg: "max" }, avg_days_ship: { expr: "ROUND(AVG(days_to_ship), 1)", label: "Avg Days", format: "decimal", subtotalAgg: "avg" }, on_time_pct: { expr: "ROUND(AVG(shipped_on_time::INT) * 100, 1)", label: "On-Time %", format: "percent", subtotalAgg: "avg" }, shipments: { expr: "COUNT(DISTINCT order_id)", label: "Shipments", format: "integer", subtotalAgg: "sum" }, }; const NODE_DEFS = [ { id: "customers", label: "Customers", color: "#3b82f6", position: { x: 80, y: 200 }, dimensions: ["customer_name","customer_country","customer_city","customer_rfm_segment"], metrics: ["revenue","order_count","avg_margin_pct","lifetime_orders","lifetime_value"], filterColumns: ["customer_name","customer_country","customer_rfm_segment"] }, { id: "orders", label: "Orders", color: "#f59e0b", position: { x: 460, y: 320 }, dimensions: ["order_id","order_date","line_revenue_band"], metrics: ["revenue","profit","units_sold","order_count"], filterColumns: ["line_revenue_band"] }, { id: "products", label: "Products", color: "#10b981", position: { x: 840, y: 200 }, dimensions: ["product_name","category_name","product_is_discontinued"], metrics: ["revenue","units_sold","profit","avg_margin_pct"], filterColumns: ["product_name","category_name","product_is_discontinued"] }, { id: "sales_reps", label: "Sales Reps", color: "#8b5cf6", position: { x: 80, y: 480 }, dimensions: ["sales_rep_name","sales_rep_title"], metrics: ["revenue","profit","order_count","avg_margin_pct"], filterColumns: ["sales_rep_name","sales_rep_title"] }, { id: "shipping", label: "Shipping & Fulfillment",color: "#06b6d4", position: { x: 560, y: 500 }, dimensions: ["shipper_name","ship_country","ship_city"], metrics: ["avg_days_ship","on_time_pct","shipments"], filterColumns: ["shipper_name","ship_country"] }, { id: "suppliers", label: "Suppliers", color: "#f43f5e", position: { x: 1160, y: 80 }, dimensions: ["supplier_name","supplier_country"], metrics: ["revenue","cost","profit","avg_margin_pct"], filterColumns: ["supplier_name","supplier_country"] }, ]; const EDGE_DEFS = [ { source: "customers", target: "orders", label: "places" }, { source: "orders", target: "products", label: "contains" }, { source: "sales_reps", target: "orders", label: "handles" }, { source: "orders", target: "shipping", label: "fulfilled by" }, { source: "suppliers", target: "products", label: "supplies" }, ]; const INITIAL_FILTERS = { customer_name: null, customer_country: null, customer_rfm_segment: null, product_name: null, category_name: null, product_is_discontinued: null, line_revenue_band: null, sales_rep_name: null, sales_rep_title: null, supplier_name: null, supplier_country: null, shipper_name: null, ship_country: null, }; const DATA_DICT = { metrics: { revenue: "Total gross revenue across all order lines (quantity × unit price).", profit: "Net profit after subtracting cost of goods sold from revenue.", cost: "Total cost of goods sold (COGS) for all items in the period.", units_sold: "Total number of product units ordered.", order_count: "Count of distinct orders placed in the period.", avg_margin_pct: "Average profit margin as a percentage of revenue, across all order lines.", lifetime_orders: "Maximum total number of orders ever placed by the customer (RFM frequency).", lifetime_value: "Maximum total spend by the customer over their entire history (RFM monetary).", avg_days_ship: "Average days elapsed from order date to shipment date.", on_time_pct: "Percentage of orders shipped on or before the required date.", shipments: "Count of distinct orders shipped by this carrier.", }, dimensions: { customer_name: { label: "Customer Name", desc: "Registered business name of the ordering customer." }, customer_country: { label: "Country", desc: "Country where the customer is headquartered." }, customer_city: { label: "City", desc: "City of the customer's primary address." }, customer_rfm_segment: { label: "RFM Segment", desc: "Customer tier from Recency/Frequency/Monetary analysis — e.g. Champions, Loyal, At Risk." }, order_id: { label: "Order ID", desc: "Unique identifier assigned to each sales order." }, order_date: { label: "Order Date", desc: "Date the order was placed by the customer." }, line_revenue_band: { label: "Revenue Band", desc: "Bucket for the order line by revenue size (e.g. $0–$500, $500–$2K, $2K+)." }, product_name: { label: "Product", desc: "Name of the individual SKU sold." }, category_name: { label: "Category", desc: "Product family grouping (e.g. Beverages, Dairy, Seafood)." }, product_is_discontinued: { label: "Discontinued", desc: "Yes = product no longer sold; No = active product." }, sales_rep_name: { label: "Sales Rep", desc: "Full name of the sales representative on the account." }, sales_rep_title: { label: "Title", desc: "Job title of the sales rep (e.g. Sales Manager, Inside Sales)." }, shipper_name: { label: "Shipper", desc: "Logistics carrier used (e.g. FedEx, UPS, Speedy Express)." }, ship_country: { label: "Ship-To Country", desc: "Country of the delivery destination." }, ship_city: { label: "Ship-To City", desc: "City of the delivery destination." }, supplier_name: { label: "Supplier", desc: "Vendor who manufactures or distributes the product." }, supplier_country: { label: "Supplier Country", desc: "Country where the supplier is based." }, } }; function cleanLabel(col) { const prefixes = ["customer_rfm_","customer_","product_","sales_rep_","ship_","supplier_","category_","shipper_","order_","line_"]; let s = col; for (const p of prefixes) { if (s.startsWith(p)) { s = s.slice(p.length); break; } } const specials = { segment: "RFM Segment", frequency: "Lifetime Orders", monetary: "Lifetime Value", recency_days: "Recency Days", is_discontinued: "Discontinued", revenue_band: "Revenue Band" }; if (specials[s]) return specials[s]; return s.replace(/_/g, " ").replace(/\b\w/g, c => c.toUpperCase()); } function fmtValue(v, format) { if (v === null || v === undefined) return "—"; const n = N(v); if (!isFinite(n)) return "—"; switch (format) { case "currency": return "$" + n.toLocaleString("en-US", { minimumFractionDigits: 2, maximumFractionDigits: 2 }); case "integer": return Math.round(n).toLocaleString("en-US"); case "percent": return n.toFixed(1) + "%"; case "decimal": return n.toFixed(1); default: return String(v); } } function fmtDim(v) { if (v === null || v === undefined) return "—"; if (typeof v === "boolean") return v ? "Yes" : "No"; if (typeof v === "bigint") return v.toString(); if (typeof v === "object") { try { if (typeof v.toJson === "function") return JSON.stringify(v.toJson()); } catch {} try { return String(v); } catch {} return "[object]"; } return String(v); } function buildWhereClause(filters) { return Object.entries(filters || {}) .filter(([_, v]) => v !== null && v !== undefined && (!Array.isArray(v) || v.length > 0)) .map(([col, val]) => { const vals = Array.isArray(val) ? val : [val]; if (BOOLEAN_COLS.has(col)) { return vals.length === 1 ? `AND ${col} = ${vals[0]}` : `AND ${col} IN (${vals.join(",")})`; } const esc = vals.map(v => `'${String(v).replace(/'/g, "''")}'`).join(", "); return vals.length === 1 ? `AND ${col} = ${esc}` : `AND ${col} IN (${esc})`; }) .join(" "); } function buildQuery(node, filters) { const dimExprs = node.dimensions.map(d => DATE_DIMS.has(d) ? `strftime(${d}, '%Y-%m-%d') AS ${d}` : d).join(", "); const metrics = node.metrics.map(k => `${METRIC_DEFS[k].expr} AS ${k}`).join(", "); const where = buildWhereClause(filters); return `SELECT ${dimExprs}, ${metrics} FROM ${OBT} WHERE 1=1 ${where} GROUP BY ${node.dimensions.join(", ")} ORDER BY ${node.metrics[0]} DESC NULLS LAST`; } function buildOptionsQuery(col, filters) { const otherFilters = Object.fromEntries(Object.entries(filters || {}).filter(([k]) => k !== col)); const where = buildWhereClause(otherFilters); if (BOOLEAN_COLS.has(col)) return `SELECT DISTINCT CAST(${col} AS VARCHAR) AS val FROM ${OBT} WHERE 1=1 ${where} ORDER BY val`; return `SELECT DISTINCT ${col} AS val FROM ${OBT} WHERE 1=1 ${where} ORDER BY ${col}`; } function computeSubtotals(rows, node) { const result = {}; for (const mk of node.metrics) { const def = METRIC_DEFS[mk]; const vals = rows.map(r => N(r[mk])); if (def.subtotalAgg === "sum") result[mk] = vals.reduce((a, b) => a + b, 0); else if (def.subtotalAgg === "avg") result[mk] = vals.length ? vals.reduce((a, b) => a + b, 0) / vals.length : 0; else if (def.subtotalAgg === "max") result[mk] = vals.length ? Math.max(...vals) : 0; } return result; } // ─── Chart Icons ───────────────────────────────────────────────────────────── function ChartIcon({ type, size = 16 }) { const s = size; switch (type) { case "bar": return ( ); case "barh": return ( ); case "pie": return ( ); case "area": return ( ); case "line": return ( ); case "stacked": return ( ); default: return {type}; } } // ─── Data Dictionary Modal ──────────────────────────────────────────────────── function DataDictModal({ onClose }) { return (

Data Dictionary & User Guide

Sales Analytics Explorer

⚡ Quick Start

{[ ["🧭 Pan & Zoom", "Click and drag the empty canvas to pan. Scroll to zoom in or out."], ["📌 Expand Nodes", "Click any node header to expand or collapse it."], ["↔ Resize", "Drag the right or bottom edge of a node to resize it freely."], ["🔍 Cross-filter", "Filters inside any node apply across all nodes simultaneously."], ["📊 Chart / Table", "Each node has a Chart tab and a Table tab — switch between them."], ["🎯 Reset View", "Click 'Fit to view' in the toolbar to reset zoom and see all nodes."], ].map(([title, desc]) => (

{title}

{desc}

))}

📈 Metrics

Metric Format Description

{Object.entries(METRIC_DEFS).map(([key, def], i) => (

{def.label} {def.format} {DATA_DICT.metrics[key]}

))}

🗂 Nodes & Dimensions

{NODE_DEFS.map(node => (

{node.label} {node.dimensions.length} dimensions · {node.metrics.length} metrics

{node.dimensions.map((dim, i) => { const info = DATA_DICT.dimensions[dim]; return (

{info?.label || cleanLabel(dim)} {info?.desc || "—"}

); })}

))}

); } // ─── Main Component ─────────────────────────────────────────────────────────── export default function BusinessGraphExplorer() { const [savedFilters, setGlobalFilters] = useDiveState("gf", INITIAL_FILTERS); const globalFilters = useMemo(() => ({ ...INITIAL_FILTERS, ...(savedFilters || {}) }), [savedFilters]); const [expanded, setExpanded] = useState(() => Object.fromEntries(NODE_DEFS.map(n => [n.id, false]))); const [positions, setPositions] = useState(() => Object.fromEntries(NODE_DEFS.map(n => [n.id, { ...n.position }]))); const [nodeWidths, setNodeWidths] = useState(() => Object.fromEntries(NODE_DEFS.map(n => [n.id, NODE_DEFAULT_W]))); const [nodeHeights, setNodeHeights] = useState(() => Object.fromEntries(NODE_DEFS.map(n => [n.id, NODE_DEFAULT_H]))); const [transform, setTransform] = useState({ x: 0, y: 0, k: 1 }); const [draggingKey, setDraggingKey] = useState(null); const [showDict, setShowDict] = useState(true); const wrapperRef = useRef(null); const transformRef = useRef(transform); const positionsRef = useRef(positions); const expandedRef = useRef(expanded); const nodeWidthsRef = useRef(nodeWidths); const nodeHeightsRef = useRef(nodeHeights); const zoomRef = useRef(null); useEffect(() => { transformRef.current = transform; }, [transform]); useEffect(() => { positionsRef.current = positions; }, [positions]); useEffect(() => { expandedRef.current = expanded; }, [expanded]); useEffect(() => { nodeWidthsRef.current = nodeWidths; }, [nodeWidths]); useEffect(() => { nodeHeightsRef.current = nodeHeights; }, [nodeHeights]); useEffect(() => { if (!wrapperRef.current) return; const sel = d3.select(wrapperRef.current); const z = d3.zoom() .scaleExtent([0.06, 3]) .filter(ev => { if (ev.target?.closest?.("[data-node-card]")) return false; if (ev.type === "wheel") return true; return ev.button === 0; }) .on("zoom", ev => { const t = ev.transform; setTransform({ x: t.x, y: t.y, k: t.k }); }); zoomRef.current = z; sel.call(z); return () => { try { sel.on(".zoom", null); } catch {} }; }, []); const fitToView = useCallback(() => { if (!wrapperRef.current || !zoomRef.current) return; const w = wrapperRef.current.clientWidth, h = wrapperRef.current.clientHeight; let minX = Infinity, minY = Infinity, maxX = 0, maxY = 0; for (const node of NODE_DEFS) { const p = positionsRef.current[node.id] || node.position; const nw = expandedRef.current[node.id] ? (nodeWidthsRef.current[node.id] || NODE_DEFAULT_W) : NODE_COLLAPSED_W; const nh = expandedRef.current[node.id] ? (nodeHeightsRef.current[node.id] || NODE_DEFAULT_H) : NODE_HEADER_H; minX = Math.min(minX, p.x); minY = Math.min(minY, p.y); maxX = Math.max(maxX, p.x + nw); maxY = Math.max(maxY, p.y + nh); } const pad = 40, cw = maxX - minX + pad * 2, ch = maxY - minY + pad * 2; if (cw <= 0 || ch <= 0) return; const k = Math.min(w / cw, h / ch, 1.0); d3.select(wrapperRef.current).call(zoomRef.current.transform, d3.zoomIdentity.translate((w - cw * k) / 2 - (minX - pad) * k, (h - ch * k) / 2 - (minY - pad) * k).scale(k)); }, []); useEffect(() => { const id = setTimeout(fitToView, 200); return () => clearTimeout(id); }, []); const startDrag = useCallback((e, nodeId) => { if (e.button !== 0) return; e.stopPropagation(); e.preventDefault(); const target = e.currentTarget, startX = e.clientX, startY = e.clientY; const startPos = { ...positionsRef.current[nodeId] }, startK = transformRef.current.k; let isDrag = false; target.setPointerCapture(e.pointerId); const onMove = ev => { const dx = ev.clientX - startX, dy = ev.clientY - startY; if (!isDrag && Math.hypot(dx, dy) >= 4) { isDrag = true; setDraggingKey(nodeId); } if (isDrag) setPositions(prev => ({ ...prev, [nodeId]: { x: startPos.x + dx / startK, y: startPos.y + dy / startK } })); }; const onUp = () => { target.removeEventListener("pointermove", onMove); target.removeEventListener("pointerup", onUp); target.removeEventListener("pointercancel", onUp); try { target.releasePointerCapture(e.pointerId); } catch {} if (!isDrag) setExpanded(prev => ({ ...prev, [nodeId]: !prev[nodeId] })); setDraggingKey(null); }; target.addEventListener("pointermove", onMove); target.addEventListener("pointerup", onUp); target.addEventListener("pointercancel", onUp); }, []); const makeResizer = useCallback((axis, setter, getStart, minVal) => (e, nodeId) => { if (e.button !== 0) return; e.stopPropagation(); e.preventDefault(); const target = e.currentTarget, startPx = axis === "h" ? e.clientX : e.clientY; const startVal = getStart(nodeId), startK = transformRef.current.k; target.setPointerCapture(e.pointerId); const onMove = ev => { const d = ((axis === "h" ? ev.clientX : ev.clientY) - startPx) / startK; setter(prev => ({ ...prev, [nodeId]: Math.max(minVal, Math.round(startVal + d)) })); }; const onUp = () => { target.removeEventListener("pointermove", onMove); target.removeEventListener("pointerup", onUp); target.removeEventListener("pointercancel", onUp); try { target.releasePointerCapture(e.pointerId); } catch {} }; target.addEventListener("pointermove", onMove); target.addEventListener("pointerup", onUp); target.addEventListener("pointercancel", onUp); }, []); const startResizeH = useMemo(() => makeResizer("h", setNodeWidths, id => nodeWidthsRef.current[id] || NODE_DEFAULT_W, NODE_MIN_W), [makeResizer]); const startResizeV = useMemo(() => makeResizer("v", setNodeHeights, id => nodeHeightsRef.current[id] || NODE_DEFAULT_H, NODE_MIN_H), [makeResizer]); const startResizeLeft = useCallback((e, nodeId) => { if (e.button !== 0) return; e.stopPropagation(); e.preventDefault(); const target = e.currentTarget; const startX = e.clientX; const startW = nodeWidthsRef.current[nodeId] || NODE_DEFAULT_W; const startPosX = positionsRef.current[nodeId]?.x || 0; const startK = transformRef.current.k; target.setPointerCapture(e.pointerId); const onMove = ev => { const dx = (ev.clientX - startX) / startK; const newW = Math.max(NODE_MIN_W, Math.round(startW - dx)); setNodeWidths(prev => ({ ...prev, [nodeId]: newW })); setPositions(prev => ({ ...prev, [nodeId]: { ...prev[nodeId], x: startPosX + (startW - newW) } })); }; const onUp = () => { target.removeEventListener("pointermove", onMove); target.removeEventListener("pointerup", onUp); target.removeEventListener("pointercancel", onUp); try { target.releasePointerCapture(e.pointerId); } catch {} }; target.addEventListener("pointermove", onMove); target.addEventListener("pointerup", onUp); target.addEventListener("pointercancel", onUp); }, []); const startResizeTop = useCallback((e, nodeId) => { if (e.button !== 0) return; e.stopPropagation(); e.preventDefault(); const target = e.currentTarget; const startY = e.clientY; const startH = nodeHeightsRef.current[nodeId] || NODE_DEFAULT_H; const startPosY = positionsRef.current[nodeId]?.y || 0; const startK = transformRef.current.k; target.setPointerCapture(e.pointerId); const onMove = ev => { const dy = (ev.clientY - startY) / startK; const newH = Math.max(NODE_MIN_H, Math.round(startH - dy)); setNodeHeights(prev => ({ ...prev, [nodeId]: newH })); setPositions(prev => ({ ...prev, [nodeId]: { ...prev[nodeId], y: startPosY + (startH - newH) } })); }; const onUp = () => { target.removeEventListener("pointermove", onMove); target.removeEventListener("pointerup", onUp); target.removeEventListener("pointercancel", onUp); try { target.releasePointerCapture(e.pointerId); } catch {} }; target.addEventListener("pointermove", onMove); target.addEventListener("pointerup", onUp); target.addEventListener("pointercancel", onUp); }, []) const edgePaths = useMemo(() => EDGE_DEFS.map((edge, i) => { const sp = positions[edge.source] || { x: 0, y: 0 }, tp = positions[edge.target] || { x: 0, y: 0 }; const sw = expanded[edge.source] ? (nodeWidths[edge.source] || NODE_DEFAULT_W) : NODE_COLLAPSED_W; const tw = expanded[edge.target] ? (nodeWidths[edge.target] || NODE_DEFAULT_W) : NODE_COLLAPSED_W; const sy = sp.y + NODE_HEADER_H / 2, ty = tp.y + NODE_HEADER_H / 2; let sx, ex; if (tp.x + tw < sp.x) { sx = sp.x; ex = tp.x + tw; } else if (tp.x > sp.x + sw) { sx = sp.x + sw; ex = tp.x; } else { sx = sp.x + sw; ex = tp.x + tw; } const off = Math.max(60, Math.abs(ex - sx) / 2); return { id: i, d: `M ${sx} ${sy} C ${sx + (ex >= sx ? off : -off)} ${sy}, ${ex + (sx >= ex ? off : -off)} ${ty}, ${ex} ${ty}`, mx: (sx + ex) / 2, my: (sy + ty) / 2 - 8, label: edge.label }; }), [positions, expanded, nodeWidths]); const canvasW = useMemo(() => Math.max(...NODE_DEFS.map(n => (positions[n.id]?.x || 0) + (expanded[n.id] ? (nodeWidths[n.id] || NODE_DEFAULT_W) : NODE_COLLAPSED_W) + 200)), [positions, expanded, nodeWidths]); const canvasH = useMemo(() => Math.max(...NODE_DEFS.map(n => (positions[n.id]?.y || 0) + (expanded[n.id] ? (nodeHeights[n.id] || NODE_DEFAULT_H) : NODE_HEADER_H) + 100)), [positions, expanded, nodeHeights]); const activeFilters = useMemo(() => { const out = []; for (const [col, val] of Object.entries(globalFilters)) { if (val === null || val === undefined) continue; if (Array.isArray(val) && val.length === 0) continue; out.push([col, Array.isArray(val) ? val : [val]]); } return out; }, [globalFilters]); const colToNode = useMemo(() => { const map = {}; for (const node of NODE_DEFS) for (const col of node.filterColumns) map[col] = node; return map; }, []); return (

{showDict && setShowDict(false)} />} {/* Top bar */}

Business Graph Explorer

{activeFilters.length > 0 && ( )}

{activeFilters.length > 0 && (

Filters {activeFilters.map(([col, vals]) => { const node = colToNode[col]; return (

{cleanLabel(col)}: {vals.length === 1 ? vals[0] : `${vals.length} selected`}

); })}

)} {/* Canvas */}

{NODE_DEFS.map(node => ( startDrag(e, node.id)} onResizeHStart={e => startResizeH(e, node.id)} onResizeVStart={e => startResizeV(e, node.id)} onResizeLStart={e => startResizeLeft(e, node.id)} onResizeTStart={e => startResizeTop(e, node.id)} /> ))}

); } // ─── Node Card ──────────────────────────────────────────────────────────────── function NodeCard({ node, position, isExpanded, isDragging, nodeWidth, nodeHeight, globalFilters, setGlobalFilters, onDragStart, onResizeHStart, onResizeVStart, onResizeLStart, onResizeTStart }) { const [sortCol, setSortCol] = useState(null); const [sortDir, setSortDir] = useState("desc"); const [page, setPage] = useState(0); const [view, setView] = useState("table"); const isOrders = node.id === "orders"; // chart types: orders gets bar/barh/line/area; others get bar/barh/pie/area/stacked const chartTypes = isOrders ? ["bar","barh","line","area"] : ["bar","barh","pie","area","stacked"]; const CHART_LABELS = { bar: "Vertical bar", barh: "Horizontal bar", pie: "Pie chart", area: "Area chart", line: "Line chart", stacked: "Stacked bar" }; const [chartType, setChartType] = useState(isOrders ? "area" : "bar"); const [chartDim, setChartDim] = useState(isOrders ? "order_date" : node.dimensions[0]); const [stackDim, setStackDim] = useState(() => node.dimensions.find(d => d !== (isOrders ? "order_date" : node.dimensions[0])) || node.dimensions[0]); const [chartMetric, setChartMetric] = useState(node.metrics[0]); const [timePeriod, setTimePeriod] = useState("month"); const query = useSQLQuery(buildQuery(node, globalFilters)); const allRows = useMemo(() => Array.isArray(query.data) ? query.data : [], [query.data]); useEffect(() => { setPage(0); }, [globalFilters]); const maxVals = useMemo(() => { const r = {}; for (const mk of node.metrics) r[mk] = Math.max(1, ...allRows.map(row => Math.abs(N(row[mk])))); return r; }, [allRows, node]); // Standard chart data (bar/barh/pie/area/line) const chartData = useMemo(() => { if (!allRows.length) return []; if (isOrders) { const g = {}; for (const row of allRows) { const ds = String(row.order_date || ""); const key = timePeriod === "month" ? ds.slice(0, 7) : timePeriod === "year" ? ds.slice(0, 4) : ds; if (!g[key]) { g[key] = { date: key }; for (const mk of node.metrics) g[key][mk] = 0; } for (const mk of node.metrics) g[key][mk] += N(row[mk]); } return Object.values(g).sort((a, b) => a.date.localeCompare(b.date)); } if (!chartDim || !chartMetric) return []; const g = {}; for (const row of allRows) { const key = fmtDim(row[chartDim]); if (key === "—") continue; if (!g[key]) g[key] = { name: key, value: 0 }; g[key].value += N(row[chartMetric]); } const sorted = Object.values(g).sort((a, b) => b.value - a.value); return chartType === "pie" ? sorted.slice(0, 12) : sorted.slice(0, 20); }, [allRows, isOrders, chartDim, chartMetric, chartType, timePeriod, node]); // Stacked chart data const stackedChartData = useMemo(() => { if (chartType !== "stacked" || !allRows.length) return { data: [], stackKeys: [] }; // Find top N stack values by total metric; bucket the rest into "Other" const stackKeyTotals = {}; for (const row of allRows) { const d2 = fmtDim(row[stackDim]); if (d2 === "—") continue; stackKeyTotals[d2] = (stackKeyTotals[d2] || 0) + N(row[chartMetric]); } const TOP_N = 9; const sorted = Object.entries(stackKeyTotals).sort((a, b) => b[1] - a[1]); const topKeys = sorted.slice(0, TOP_N).map(([k]) => k); const topKeySet = new Set(topKeys); const hasOther = sorted.length > TOP_N; const stackKeys = hasOther ? [...topKeys, "Other"] : topKeys; // Group by primary dim — remaining stack values roll into "Other" const groups = {}; for (const row of allRows) { const d1 = fmtDim(row[chartDim]); const d2 = fmtDim(row[stackDim]); if (d1 === "—") continue; const val = N(row[chartMetric]); if (!groups[d1]) groups[d1] = { name: d1 }; if (topKeySet.has(d2)) { groups[d1][d2] = (groups[d1][d2] || 0) + val; } else if (hasOther && d2 !== "—") { groups[d1]["Other"] = (groups[d1]["Other"] || 0) + val; } } const data = Object.values(groups) .map(item => ({ ...item, _total: stackKeys.reduce((s, k) => s + (item[k] || 0), 0) })) .sort((a, b) => b._total - a._total) .slice(0, 15); return { data, stackKeys }; }, [allRows, chartType, chartDim, stackDim, chartMetric]); const sortedRows = useMemo(() => { if (!sortCol) return allRows; const isMet = node.metrics.includes(sortCol); return [...allRows].sort((a, b) => { if (isMet) { const an = isNaN(N(a[sortCol])) ? (sortDir === "desc" ? -Infinity : Infinity) : N(a[sortCol]); const bn = isNaN(N(b[sortCol])) ? (sortDir === "desc" ? -Infinity : Infinity) : N(b[sortCol]); return sortDir === "desc" ? bn - an : an - bn; } const as_ = fmtDim(a[sortCol]), bs_ = fmtDim(b[sortCol]); return sortDir === "desc" ? bs_.localeCompare(as_) : as_.localeCompare(bs_); }); }, [allRows, sortCol, sortDir, node]); const totalPages = Math.max(1, Math.ceil(sortedRows.length / PAGE_SIZE)); const pageRows = sortedRows.slice(page * PAGE_SIZE, (page + 1) * PAGE_SIZE); const subtotals = useMemo(() => computeSubtotals(allRows, node), [allRows, node]); const activeFilterCount = node.filterColumns.filter(col => { const v = globalFilters[col]; return v !== null && v !== undefined && (!Array.isArray(v) || v.length > 0); }).length; const handleSort = col => { if (sortCol === col) { if (sortDir === "desc") setSortDir("asc"); else { setSortCol(null); setSortDir("desc"); } } else { setSortCol(col); setSortDir("desc"); } }; const { exportQuery } = useExport(); const handleExport = async () => { try { await exportQuery({ sql: buildQuery(node, globalFilters), format: "csv", filename: `${node.id}_export` }); } catch {} }; const metFmt = METRIC_DEFS[chartMetric]?.format || "decimal"; const metLabel = METRIC_DEFS[chartMetric]?.label || chartMetric; const dimPct = `${Math.round(45 / node.dimensions.length)}%`; const metPct = `${Math.round(55 / node.metrics.length)}%`; const chartSubtitle = useMemo(() => { if (chartType === "stacked") return `${metLabel} · ${cleanLabel(chartDim)} stacked by ${cleanLabel(stackDim)}`; if (isOrders) return `${metLabel} · by ${timePeriod}`; return `${metLabel} · by ${cleanLabel(chartDim)}`; }, [chartType, metLabel, chartDim, stackDim, isOrders, timePeriod]); const legendFmt = v => (typeof v === "string" && v.length > 20) ? v.slice(0, 20) + "…" : v; const ttStyle = { fontSize: 10, border: "none", boxShadow: "0 2px 10px rgba(0,0,0,0.12)", borderRadius: 6 }; const axisTick = { fontSize: 9, fill: "#9ca3af" }; const yFmt = v => fmtAxisTick(v, metFmt); const ttFmt = (v, name) => [fmtValue(v, metFmt), name]; const xKey = isOrders ? "date" : "name"; const dKey = isOrders ? chartMetric : "value"; const hMargin = { top: 4, right: 16, left: 0, bottom: 8 }; const vMargin = { top: 4, right: 8, left: 0, bottom: 24 }; const leg = ; const renderChart = () => { const noData =

No data to display

; if (chartType === "stacked") { const { data, stackKeys } = stackedChartData; if (!data.length) return noData; return ( {stackKeys.map((key, i) => ( ))} ); } if (chartType === "barh") { if (!chartData.length) return noData; return ( {leg} ); } if (chartType === "pie") { if (!chartData.length) return noData; return ( percent > 0.04 ? `${(percent * 100).toFixed(0)}%` : ""} labelLine={false} fontSize={9}> {chartData.map((_, i) => )} [fmtValue(v, metFmt), name]} contentStyle={ttStyle} /> ); } if (chartType === "line") { if (!chartData.length) return noData; return ( {leg} ); } if (chartType === "area") { if (!chartData.length) return noData; return ( {leg} ); } // Default: vertical bar if (!chartData.length) return noData; return ( {leg} ); }; return (

{isExpanded && (

e.currentTarget.style.background = `${node.color}30`} onMouseLeave={e => e.currentTarget.style.background = "transparent"} /> )} {isExpanded && (

e.currentTarget.style.background = `${node.color}30`} onMouseLeave={e => e.currentTarget.style.background = "transparent"} /> )} {/* Header */}

{node.label} {!query.isLoading && allRows.length > 0 && {allRows.length.toLocaleString()}} {activeFilterCount > 0 &&

{activeFilterCount}

} {query.isLoading &&

} {isExpanded ? : }

{isExpanded && (

{/* Filters */}

{node.filterColumns.map(col => ( ))}

{/* View toggle */}

{[["chart","📊 Chart"],["table","📋 Table"]].map(([v, label]) => ( ))}

{/* CHART VIEW */} {view === "chart" && (

{/* Toolbar */}

{/* Icon-based chart type buttons */}

{chartTypes.map(ct => ( ))}

{/* Dimension selector(s) */} {chartType === "stacked" ? (

stacked by

) : !isOrders ? ( ) : null} {/* Metric selector */} {/* Time period (orders) */} {isOrders && (

{["day","month","year"].map(tp => ( ))}

)}

{/* Chart subtitle */}

{chartSubtitle}

{/* Chart */}

{renderChart()}

)} {/* TABLE VIEW */} {view === "table" && (

{query.isError &&

Error loading data

} {!query.isError && ( {node.dimensions.map((_, i) => )} {node.metrics.map((_, i) => )} {node.dimensions.map(dim => ( ))} {node.metrics.map(mk => ( ))} {pageRows.length === 0 && !query.isLoading && ( )} {pageRows.map((row, i) => ( {node.dimensions.map(dim => ( ))} {node.metrics.map(mk => { const val = N(row[mk]); const pct = maxVals[mk] > 0 ? Math.min(100, (Math.abs(val) / maxVals[mk]) * 100) : 0; return ( ); })} ))}

handleSort(dim)} style={{ padding: "5px 8px", textAlign: "left", fontSize: 10, fontWeight: 600, color: "#6b7280", textTransform: "uppercase", letterSpacing: "0.05em", cursor: "pointer", whiteSpace: "nowrap", borderBottom: "2px solid #f3f4f6", background: "#fff", overflow: "hidden", textOverflow: "ellipsis" }}> {cleanLabel(dim)}{sortCol === dim ? (sortDir === "desc" ? " ↓" : " ↑") : ""}	handleSort(mk)} style={{ padding: "5px 8px", textAlign: "right", fontSize: 10, fontWeight: 600, color: "#6b7280", textTransform: "uppercase", letterSpacing: "0.05em", cursor: "pointer", whiteSpace: "nowrap", borderBottom: "2px solid #f3f4f6", background: "#fff", overflow: "hidden", textOverflow: "ellipsis" }}> {METRIC_DEFS[mk].label}{sortCol === mk ? (sortDir === "desc" ? " ↓" : " ↑") : ""}
No results
{fmtDim(row[dim])}	{fmtValue(row[mk], METRIC_DEFS[mk].format)}

)}

{allRows.length > 0 && (

{node.dimensions.map((_, i) => )} {node.metrics.map((_, i) => )} {node.dimensions.slice(1).map((_, i) => ))}

Σ Total

)} {node.metrics.map(mk => (

{fmtValue(subtotals[mk], METRIC_DEFS[mk].format)}

)}

{allRows.length === 0 ? "0 rows" : `${page * PAGE_SIZE + 1}–${Math.min((page + 1) * PAGE_SIZE, sortedRows.length)} of ${sortedRows.length}`} {totalPages > 1 && (

{page + 1} / {totalPages}

)}

); } // ─── Multi-Select Pill ──────────────────────────────────────────────────────── function MultiSelectPill({ col, node, globalFilters, setGlobalFilters }) { const [open, setOpen] = useState(false); const pillRef = useRef(null); const currentVals = globalFilters[col]; const selected = useMemo(() => { if (!currentVals) return []; return Array.isArray(currentVals) ? currentVals : [currentVals]; }, [currentVals]); const isActive = selected.length > 0; const optionsQ = useSQLQuery(buildOptionsQuery(col, globalFilters)); const options = useMemo(() => { if (BOOLEAN_COLS.has(col)) return [{ val: "true", label: "Yes" }, { val: "false", label: "No" }]; const rows = Array.isArray(optionsQ.data) ? optionsQ.data : []; return rows.map(r => ({ val: r.val != null ? String(r.val) : null, label: r.val != null ? String(r.val) : null })).filter(r => r.val !== null); }, [optionsQ.data, col]); useEffect(() => { if (!open) return; const handle = e => { if (pillRef.current && !pillRef.current.contains(e.target)) setOpen(false); }; document.addEventListener("mousedown", handle); return () => document.removeEventListener("mousedown", handle); }, [open]); const toggle = val => { if (selected.includes(val)) { const next = selected.filter(v => v !== val); setGlobalFilters(prev => ({ ...(prev || INITIAL_FILTERS), [col]: next.length > 0 ? next : null })); } else { setGlobalFilters(prev => ({ ...(prev || INITIAL_FILTERS), [col]: [...selected, val] })); } }; const label = selected.length === 0 ? "All" : selected.length === 1 ? (selected[0].length > 14 ? selected[0].slice(0, 14) + "…" : selected[0]) : `${selected.length} selected`; return (

setOpen(o => !o)} style={{ display: "inline-flex", alignItems: "center", gap: 3, padding: "3px 7px 3px 9px", borderRadius: 12, border: `1px solid ${isActive ? node.color + "60" : "#e5e7eb"}`, background: isActive ? node.color + "12" : "#f9fafb", fontSize: 11, cursor: "pointer", userSelect: "none", }}> {cleanLabel(col)}: {label} {isActive ? : }

{open && (

{options.length === 0 &&

Loading…

} {options.map(opt => { const checked = selected.includes(opt.val); return (

toggle(opt.val)} style={{ padding: "5px 11px", cursor: "pointer", display: "flex", alignItems: "center", gap: 8, fontSize: 11, background: checked ? node.color + "09" : "transparent" }} onMouseEnter={e => { if (!checked) e.currentTarget.style.background = "#f5f5f5"; }} onMouseLeave={e => { e.currentTarget.style.background = checked ? node.color + "09" : "transparent"; }} >

{checked && ✓}

{opt.label}

); })}

)}

); }

Business Graph Explorer

Comments