NBA Game Quality Explorer

attach_db.sql

ATTACH 'md:_share/nba_box_scores/c9126ae3-ef30-4b6e-af8e-d2212c1f2797';

create_dive.sql

SELECT * FROM MD_CREATE_DIVE(
  title = 'NBA Game Quality Explorer — 2025-26 v2',
  content = $abcd$
  import { useState, useMemo, useRef, useEffect } from "react";
import { useSQLQuery } from "@motherduck/react-sql-query";

export const REQUIRED_DATABASES = [
  { type: 'share', path: 'md:_share/nba_box_scores/c9126ae3-ef30-4b6e-af8e-d2212c1f2797', alias: 'nba_box_scores' }
];


const N = (v) => (v != null ? Number(v) : 0);
function isoWeek(y, m, d) {
  const dt = new Date(Date.UTC(y, m - 1, d));
  dt.setUTCDate(dt.getUTCDate() + 4 - (dt.getUTCDay() || 7));
  const y1 = new Date(Date.UTC(dt.getUTCFullYear(), 0, 1));
  return Math.ceil(((dt - y1) / 86400000 + 1) / 7);
}
const PAGE_SIZE = 50;
const STATS = ["points", "rebounds", "assists", "steals", "blocks", "turnovers", "fg3_made", "fg_v", "ft_v", "game_quality"];
const STAT_LABELS = { points: "PTS", rebounds: "REB", assists: "AST", steals: "STL", blocks: "BLK", turnovers: "TO", fg3_made: "3PTM", fg_v: "FG_V", ft_v: "FT_V", game_quality: "GQ" };
const HEATMAP_BINS = 20;
const HEATMAP_COLORS = ["#f5f5f5","#e8edf4","#dce4f2","#cfdcf0","#c2d3ee","#b4caec","#a6c0e9","#97b6e6","#88ace3","#79a1df","#6a96db","#5b8bd7","#4d80d3","#4075cf","#356ac8"];

/* ── Filter parsing (Datadog-style key:value) ────────────────────── */

const FILTER_KEYS = {
  // String filters
  team:     { field: 'team_id',         match: 'contains' },
  player:   { field: 'player_name',     match: 'contains' },
  opp:      { field: 'opponent',        match: 'contains' },
  opponent: { field: 'opponent',        match: 'contains' },
  date:     { field: 'game_date_short', match: 'contains' },
  // Numeric filters
  pts:       { field: 'points',       match: 'numeric' },
  points:    { field: 'points',       match: 'numeric' },
  reb:       { field: 'rebounds',     match: 'numeric' },
  rebounds:  { field: 'rebounds',     match: 'numeric' },
  ast:       { field: 'assists',      match: 'numeric' },
  assists:   { field: 'assists',      match: 'numeric' },
  stl:       { field: 'steals',       match: 'numeric' },
  steals:    { field: 'steals',       match: 'numeric' },
  blk:       { field: 'blocks',       match: 'numeric' },
  blocks:    { field: 'blocks',       match: 'numeric' },
  to:        { field: 'turnovers',    match: 'numeric' },
  turnovers: { field: 'turnovers',    match: 'numeric' },
  '3ptm':    { field: 'fg3_made',     match: 'numeric' },
  min:       { field: 'mins',         match: 'numeric' },
  mins:      { field: 'mins',         match: 'numeric' },
  gq:        { field: 'game_quality', match: 'numeric' },
  fg_v:      { field: 'fg_v',         match: 'numeric' },
  ft_v:      { field: 'ft_v',         match: 'numeric' },
  'fg%':     { field: 'fg_pct',      match: 'numeric' },
  fg_pct:    { field: 'fg_pct',      match: 'numeric' },
  'ft%':     { field: 'ft_pct',      match: 'numeric' },
  ft_pct:    { field: 'ft_pct',      match: 'numeric' },
  // Date part filters
  year:      { field: 'year',        match: 'numeric' },
  month:     { field: 'month',       match: 'numeric' },
  day:       { field: 'day',         match: 'numeric' },
  week:      { field: 'week',        match: 'numeric' },
};

function parseNumericValue(value) {
  const m = value.match(/^(>=|<=|>|<|=)?(.+)$/);
  if (!m) return null;
  const op = m[1] || '=';
  const num = parseFloat(m[2]);
  if (isNaN(num)) return null;
  return { op, num };
}

function parseFilters(text) {
  const filters = [];
  const regex = /([\w%]+):(?:"([^"]*)"|([\S]*))/g;
  let match;
  while ((match = regex.exec(text)) !== null) {
    const key = match[1].toLowerCase();
    const value = match[2] !== undefined ? match[2] : match[3];
    if (value) filters.push({ key, value });
  }
  return filters;
}

function parseList(value) {
  if (value.startsWith('[') && value.endsWith(']'))
    return value.slice(1, -1).split(',').map(s => s.trim()).filter(Boolean);
  return null;
}

function applyFilters(data, filters) {
  if (filters.length === 0) return data;
  return data.filter(row =>
    filters.every(({ key, value }) => {
      const config = FILTER_KEYS[key];
      if (!config) return true;
      const list = parseList(value);
      if (config.match === 'numeric') {
        if (list) {
          const nums = list.map(Number).filter(n => !isNaN(n));
          return nums.includes(Number(row[config.field]));
        }
        const parsed = parseNumericValue(value);
        if (!parsed) return true;
        const n = Number(row[config.field]);
        switch (parsed.op) {
          case '>':  return n > parsed.num;
          case '>=': return n >= parsed.num;
          case '<':  return n < parsed.num;
          case '<=': return n <= parsed.num;
          default:   return n === parsed.num;
        }
      }
      const cell = String(row[config.field] ?? '').toLowerCase();
      if (list) {
        const items = list.map(s => s.toLowerCase());
        return config.match === 'contains'
          ? items.some(v => cell.includes(v))
          : items.includes(cell);
      }
      const v = value.toLowerCase();
      return config.match === 'contains' ? cell.includes(v) : cell === v;
    })
  );
}

function filtersToSQL(filters) {
  const dataOnly = filters.filter(f => f.key !== 'heatmap' && f.key !== 'dots' && f.key !== 'top');
  const topFilter = filters.find(f => f.key === 'top');
  const topN = topFilter ? parseInt(topFilter.value, 10) : 0;
  const parts = [];
  if (dataOnly.length > 0) parts.push('WHERE ' + dataOnly.map(({ key, value }) => {
    const config = FILTER_KEYS[key];
    if (!config) return `/* unknown: ${key} */`;
    const list = parseList(value);
    if (list) {
      if (config.match === 'numeric')
        return `${config.field} IN (${list.join(', ')})`;
      if (config.match === 'contains')
        return '(' + list.map(v => `${config.field} ILIKE '%${v}%'`).join(' OR ') + ')';
      return `${config.field} IN (${list.map(v => `'${v}'`).join(', ')})`;
    }
    if (config.match === 'numeric') {
      const parsed = parseNumericValue(value);
      if (!parsed) return `/* invalid: ${key}:${value} */`;
      return `${config.field} ${parsed.op} ${parsed.num}`;
    }
    return config.match === 'contains'
      ? `${config.field} ILIKE '%${value}%'`
      : `${config.field} = '${value}'`;
  }).join('\n  AND '));
  if (topN > 0) parts.push(`LIMIT ${topN}`);
  return parts.length > 0 ? parts.join('\n') : '-- no active filters';
}

/* ── Heatmap helpers ─────────────────────────────────────────────── */

function buildHeatmap(data, xKey, yKey, bounds) {
  const xMin = bounds.xMin, xMax = bounds.xMax, yMin = bounds.yMin, yMax = bounds.yMax;
  const xRange = xMax - xMin || 1, yRange = yMax - yMin || 1;
  const xStep = xRange / HEATMAP_BINS, yStep = yRange / HEATMAP_BINS;
  if (data.length === 0) return { bins: [], xTicks: [], yTicks: [], maxCount: 0, xMin, xMax, yMin, yMax, xStep, yStep };
  const grid = {}; let maxCount = 0;
  for (const d of data) {
    const xi = Math.min(HEATMAP_BINS - 1, Math.max(0, Math.floor((d[xKey] - xMin) / xStep)));
    const yi = Math.min(HEATMAP_BINS - 1, Math.max(0, Math.floor((d[yKey] - yMin) / yStep)));
    const key = `${xi},${yi}`; grid[key] = (grid[key] || 0) + 1;
    if (grid[key] > maxCount) maxCount = grid[key];
  }
  const bins = Object.entries(grid).map(([key, count]) => { const [xi, yi] = key.split(",").map(Number); return { xi, yi, count }; });
  const fmt = (k, v) => k === "game_quality" ? v.toFixed(3) : v.toFixed(1);
  return { bins, xTicks: Array.from({length:5},(_,i)=>fmt(xKey,xMin+(xRange*i)/4)), yTicks: Array.from({length:5},(_,i)=>fmt(yKey,yMin+(yRange*i)/4)), maxCount, xMin, xMax, yMin, yMax, xStep, yStep };
}

function buildPlayerDots(hlData, xKey, yKey, hm, cellW, cellH, padLeft, padTop) {
  if (!hlData || hlData.length === 0 || !hm.xStep) return [];
  const hash = (i) => {
    let x = Math.sin(i * 127.1 + 311.7) * 43758.5453;
    return x - Math.floor(x);
  };
  const r = Math.min(cellW, cellH) * 0.14 + 2;
  const dots = [];
  for (let i = 0; i < hlData.length; i++) {
    const d = hlData[i];
    const xi = Math.min(HEATMAP_BINS-1, Math.floor((d[xKey]-hm.xMin)/hm.xStep));
    const yi = Math.min(HEATMAP_BINS-1, Math.floor((d[yKey]-hm.yMin)/hm.yStep));
    const baseCx = padLeft + xi * cellW + cellW / 2;
    const baseCy = padTop + (HEATMAP_BINS - 1 - yi) * cellH + cellH / 2;
    const jitterX = (hash(i * 2) - 0.5) * (cellW * 0.7);
    const jitterY = (hash(i * 2 + 1) - 0.5) * (cellH * 0.7);
    dots.push({ cx: baseCx + jitterX, cy: baseCy + jitterY, r });
  }
  return dots;
}

/* ── Heatmap (clickable axis labels) ─────────────────────────────── */

function Heatmap({ data, xKey, yKey, hlData, selectedBin, onBinClick, onXLabelClick, onYLabelClick, bounds, width=620, height=280 }) {
  const pad={top:16,right:16,bottom:48,left:46};
  const w=width-pad.left-pad.right, h=height-pad.top-pad.bottom;
  const hm=useMemo(()=>buildHeatmap(data,xKey,yKey,bounds),[data,xKey,yKey,bounds]);
  const cellW=w/HEATMAP_BINS, cellH=h/HEATMAP_BINS;
  const [hover,setHover]=useState(null);
  const playerDots=useMemo(()=>buildPlayerDots(hlData,xKey,yKey,hm,cellW,cellH,pad.left,pad.top),[hlData,xKey,yKey,hm,cellW,cellH]);

  // Contour: draw edges where filled cells border empty cells
  const contourPath=useMemo(()=>{
    if(hm.bins.length===0) return '';
    const filled=new Set(hm.bins.map(b=>`${b.xi},${b.yi}`));
    const segs=[];
    for(const b of hm.bins){
      const sx=pad.left+b.xi*cellW;
      const sy=pad.top+(HEATMAP_BINS-1-b.yi)*cellH;
      if(!filled.has(`${b.xi-1},${b.yi}`))  segs.push(`M${sx},${sy}V${sy+cellH}`);
      if(!filled.has(`${b.xi+1},${b.yi}`))  segs.push(`M${sx+cellW},${sy}V${sy+cellH}`);
      if(!filled.has(`${b.xi},${b.yi+1}`))  segs.push(`M${sx},${sy}H${sx+cellW}`);
      if(!filled.has(`${b.xi},${b.yi-1}`))  segs.push(`M${sx},${sy+cellH}H${sx+cellW}`);
    }
    return segs.join('');
  },[hm.bins,cellW,cellH,pad.left,pad.top]);

  if(data.length===0) return null;
  return (
    <svg width={width} height={height} style={{display:"block",maxWidth:"100%"}}>
      {hm.bins.map(b=>{
        const ci=Math.min(HEATMAP_COLORS.length-1,Math.floor((b.count/hm.maxCount)*(HEATMAP_COLORS.length-1)));
        const isSel=selectedBin&&selectedBin.xi===b.xi&&selectedBin.yi===b.yi;
        return <rect key={`${b.xi}-${b.yi}`} x={pad.left+b.xi*cellW} y={pad.top+(HEATMAP_BINS-1-b.yi)*cellH}
          width={cellW+0.5} height={cellH+0.5} fill={HEATMAP_COLORS[ci]}
          stroke={isSel?"#231f20":"none"} strokeWidth={isSel?2:0} style={{cursor:"pointer"}}
          onClick={()=>onBinClick(b.xi,b.yi,hm)}
          onMouseEnter={()=>setHover({count:b.count,px:pad.left+b.xi*cellW+cellW/2,py:pad.top+(HEATMAP_BINS-1-b.yi)*cellH})}
          onMouseLeave={()=>setHover(null)} />;
      })}
      {contourPath && <path d={contourPath} stroke="#bbb" strokeWidth={0.75} fill="none" style={{pointerEvents:"none"}} />}
      {playerDots.map((dot,i)=>(
        <circle key={`pd-${i}`} cx={dot.cx} cy={dot.cy} r={dot.r} fill="#e67300" fillOpacity={0.85} stroke="#fff" strokeWidth={0.5} style={{pointerEvents:"none"}} />
      ))}
      {hm.xTicks.map((t,i)=><text key={`x${i}`} x={pad.left+(i/4)*w} y={height-20} fontSize={10} fill="#6a6a6a" textAnchor="middle">{t}</text>)}
      <text x={pad.left+w/2} y={height-4} fontSize={11} fill="#0777b3" textAnchor="middle" fontWeight={600}
        style={{cursor:"pointer"}} textDecoration="underline"
        onClick={onXLabelClick}>{STAT_LABELS[xKey]} ▾</text>
      {hm.yTicks.map((t,i)=><text key={`y${i}`} x={pad.left-5} y={pad.top+h-(i/4)*h+3} fontSize={10} fill="#6a6a6a" textAnchor="end">{t}</text>)}
      <text x={8} y={pad.top+h/2} fontSize={11} fill="#0777b3" textAnchor="middle" fontWeight={600}
        style={{cursor:"pointer"}} textDecoration="underline"
        transform={`rotate(-90, 8, ${pad.top+h/2})`}
        onClick={onYLabelClick}>{STAT_LABELS[yKey]} ▾</text>
      {hover&&<g>
        <rect x={Math.min(hover.px,width-100)} y={Math.max(0,hover.py-26)} width={80} height={20} rx={3} fill="#231f20" fillOpacity={0.9}/>
        <text x={Math.min(hover.px,width-100)+6} y={Math.max(0,hover.py-26)+14} fontSize={10} fill="#fff">{hover.count} game{hover.count>1?"s":""}</text>
      </g>}
    </svg>
  );
}

/* ── GQ Box Plot ─────────────────────────────────────────────────── */

function GQBoxPlot({ data, hlData, height=280 }) {
  const w=60, pad={top:16,bottom:48,left:8,right:8};
  const plotH=height-pad.top-pad.bottom;
  const boxX=pad.left, boxW=w-pad.left-pad.right;
  const stats=useMemo(()=>{
    if(data.length===0) return null;
    const vals=data.map(d=>d.game_quality).sort((a,b)=>a-b);
    const n=vals.length;
    const pct=(p)=>{const idx=p*(n-1),lo=Math.floor(idx),hi=Math.ceil(idx);return lo===hi?vals[lo]:vals[lo]+(vals[hi]-vals[lo])*(idx-lo);};
    return {min:vals[0],p25:pct(0.25),p50:pct(0.5),p75:pct(0.75),max:vals[n-1]};
  },[data]);
  const playerTicks=useMemo(()=>hlData&&hlData.length>0?hlData.map(d=>d.game_quality):[],[hlData]);
  if(!stats) return null;
  const yScale=(v)=>pad.top+plotH-((v-stats.min)/(stats.max-stats.min||1))*plotH;
  const cx=boxX+boxW/2, tw=8;
  return (
    <svg width={w} height={height} style={{display:"block",flexShrink:0}}>
      <line x1={cx} y1={yScale(stats.min)} x2={cx} y2={yScale(stats.max)} stroke="#bbb" strokeWidth={1}/>
      <line x1={cx-tw/2} y1={yScale(stats.min)} x2={cx+tw/2} y2={yScale(stats.min)} stroke="#bbb" strokeWidth={1}/>
      <line x1={cx-tw/2} y1={yScale(stats.max)} x2={cx+tw/2} y2={yScale(stats.max)} stroke="#bbb" strokeWidth={1}/>
      <rect x={boxX+4} y={yScale(stats.p75)} width={boxW-8} height={yScale(stats.p25)-yScale(stats.p75)} fill="#e8edf4" stroke="#9bb" strokeWidth={1}/>
      <line x1={boxX+4} y1={yScale(stats.p50)} x2={boxX+boxW-4} y2={yScale(stats.p50)} stroke="#231f20" strokeWidth={2}/>
      {playerTicks.map((gq,i)=><line key={i} x1={boxX+2} y1={yScale(gq)} x2={boxX+boxW-2} y2={yScale(gq)} stroke="#e67300" strokeWidth={1.5} strokeOpacity={0.8}/>)}
      <text x={cx} y={height-4} fontSize={11} fill="#231f20" textAnchor="middle" fontWeight={600}>GQ</text>
    </svg>
  );
}

/* ── Axis Dropdown ───────────────────────────────────────────────── */

function AxisDropdown({ stats, labels, current, onSelect, position }) {
  return (
    <div style={{
      position:"absolute", ...position,
      background:"#fff", border:"1px solid #ccc", borderRadius:4,
      boxShadow:"0 2px 8px rgba(0,0,0,0.15)", zIndex:20, padding:4,
    }}>
      {stats.map(s => (
        <div key={s} onClick={() => onSelect(s)}
          style={{
            padding:"4px 10px", fontSize:11, cursor:"pointer", borderRadius:3,
            whiteSpace:"nowrap",
            background: current === s ? "#0777b3" : "transparent",
            color: current === s ? "#fff" : "#231f20",
            fontWeight: current === s ? 600 : 400,
          }}>
          {labels[s]}
        </div>
      ))}
    </div>
  );
}

/* ── Table columns ───────────────────────────────────────────────── */

const TABLE_COLS = [
  { key: "team_id", label: "Team", sortKey: "team_id" },
  { key: "player_name", label: "Player", sortKey: "player_name" },
  { key: "opponent", label: "OPP", sortKey: "opponent" },
  { key: "game_date_short", label: "Date", sortKey: "game_date_short" },
  { key: "mins", label: "MIN", sortKey: "mins" },
  { key: "points", label: "PTS", sortKey: "points" },
  { key: "fg3_made", label: "3PTM", sortKey: "fg3_made" },
  { key: "fg_display", label: "FG%", sortKey: "fg_v" },
  { key: "ft_display", label: "FT%", sortKey: "ft_v" },
  { key: "rebounds", label: "REB", sortKey: "rebounds" },
  { key: "assists", label: "AST", sortKey: "assists" },
  { key: "steals", label: "STL", sortKey: "steals" },
  { key: "blocks", label: "BLK", sortKey: "blocks" },
  { key: "turnovers", label: "TO", sortKey: "turnovers" },
  { key: "game_quality", label: "GQ", sortKey: "game_quality" },
];

function fmtPctVal(pct, val) {
  const sign = val >= 0 ? "+" : "";
  return `${pct.toFixed(3)} (${sign}${val.toFixed(1)})`;
}

/* ── Main component ──────────────────────────────────────────────── */

export default function NBAGameQuality() {
  const [filterText, setFilterText] = useState("");
  const [filterMode, setFilterMode] = useState("edit");
  const [xAxis, setXAxis] = useState("points");
  const [yAxis, setYAxis] = useState("game_quality");
  const [axisDropdown, setAxisDropdown] = useState(null);
  const [sortCol, setSortCol] = useState("game_quality");
  const [sortDir, setSortDir] = useState("desc");
  const [page, setPage] = useState(0);
  const [selectedBin, setSelectedBin] = useState(null);
  const chartRef = useRef(null);
  const inputRef = useRef(null);
  const pendingCursor = useRef(null);

  // Restore cursor position after controlled input updates
  useEffect(() => {
    if (pendingCursor.current !== null && inputRef.current) {
      inputRef.current.setSelectionRange(pendingCursor.current, pendingCursor.current);
      pendingCursor.current = null;
    }
  });

  const SORTED_KEYS = useMemo(() => [...Object.keys(FILTER_KEYS), 'heatmap', 'dots', 'top'].sort(), []);
  const AUTO_VALUES = { heatmap: 'true', dots: 'true' };

  const handleFilterKeyDown = (e) => {
    const input = e.target;
    const pos = input.selectionStart;
    const selEnd = input.selectionEnd;

    if (e.key === 'Tab') {
      e.preventDefault();
      const before = filterText.slice(0, pos);
      const lastSpace = before.lastIndexOf(' ');
      const token = before.slice(lastSpace + 1).toLowerCase();
      if (token && !token.includes(':')) {
        const match = SORTED_KEYS.find(k => k.startsWith(token));
        if (match) {
          const autoVal = AUTO_VALUES[match];
          const completion = autoVal ? match + ':' + autoVal : match + ':';
          const newText = filterText.slice(0, lastSpace + 1) + completion + filterText.slice(pos);
          setFilterText(newText);
          pendingCursor.current = lastSpace + 1 + completion.length;
        }
      }
      return;
    }

    if (e.key === '"') {
      e.preventDefault();
      // If cursor is right before a closing quote, just skip past it
      if (pos === selEnd && filterText[pos] === '"') {
        pendingCursor.current = pos + 1;
        setFilterText(filterText); // trigger re-render to apply cursor
        return;
      }
      // If text is selected, wrap selection in quotes
      if (pos !== selEnd) {
        const newText = filterText.slice(0, pos) + '"' + filterText.slice(pos, selEnd) + '"' + filterText.slice(selEnd);
        setFilterText(newText);
        pendingCursor.current = selEnd + 2;
        return;
      }
      // Otherwise insert pair and place cursor between
      const newText = filterText.slice(0, pos) + '""' + filterText.slice(pos);
      setFilterText(newText);
      pendingCursor.current = pos + 1;
      return;
    }
  };

  const parsedFilters = useMemo(() => parseFilters(filterText), [filterText]);

  // Close axis dropdown on outside click
  useEffect(() => {
    if (!axisDropdown) return;
    const handler = (e) => {
      if (chartRef.current && !chartRef.current.contains(e.target)) setAxisDropdown(null);
    };
    document.addEventListener('mousedown', handler);
    return () => document.removeEventListener('mousedown', handler);
  }, [axisDropdown]);

  const { data, isLoading, isError } = useSQLQuery(`
    SELECT bg.player_name, bs.team_id,
           CASE WHEN bs.team_id = s.home_team_abbreviation THEN s.away_team_abbreviation ELSE s.home_team_abbreviation END as opponent,
           strftime(CAST(timezone('America/New_York', timezone('UTC', s.game_date)) AS DATE), '%m/%d/%y') as game_date_short,
           CAST(substring(bs.minutes, 1, instr(bs.minutes, ':') - 1) AS INTEGER) as mins,
           bg.points, bg.rebounds, bg.assists, bg.steals, bg.blocks, bg.turnovers, bg.fg3_made,
           round(bg.fg_pct, 3) as fg_pct, round(bg.ft_pct, 3) as ft_pct,
           round(bg.fg_v, 2) as fg_v, round(bg.ft_v, 2) as ft_v,
           round(bg.game_quality, 4) as game_quality,
           bg.game_id, bg.week_id
    FROM "nba_box_scores"."main"."box_scores_gq" bg
    JOIN "nba_box_scores"."main"."box_scores" bs ON bg.game_id = bs.game_id AND bg.entity_id = bs.entity_id AND bs.period = 'FullGame'
    JOIN "nba_box_scores"."main"."schedule" s ON bg.game_id = s.game_id
    WHERE bg.game_quality > 0 AND s.game_date >= '2025-10-20'
    ORDER BY bg.game_quality DESC
  `);

  const cachedRef = useRef(null);
  const cachedData = useMemo(() => {
    const raw = Array.isArray(data) ? data : [];
    if (raw.length === 0) return cachedRef.current || [];
    if (cachedRef.current && cachedRef.current.length === raw.length) return cachedRef.current;
    const processed = raw.map(d => {
      const dp = String(d.game_date_short || '').split('/');
      const gm = parseInt(dp[0], 10) || 0, gd = parseInt(dp[1], 10) || 0, gy = 2000 + (parseInt(dp[2], 10) || 0);
      return {
        player_name: d.player_name, team_id: d.team_id, opponent: d.opponent,
        game_date_short: d.game_date_short, mins: N(d.mins),
        points: N(d.points), rebounds: N(d.rebounds), assists: N(d.assists),
        steals: N(d.steals), blocks: N(d.blocks), turnovers: N(d.turnovers),
        fg3_made: N(d.fg3_made), fg_pct: N(d.fg_pct), ft_pct: N(d.ft_pct),
        fg_v: N(d.fg_v), ft_v: N(d.ft_v), game_quality: N(d.game_quality),
        game_id: d.game_id, week_id: N(d.week_id),
        year: gy, month: gm, day: gd, week: gm && gd ? isoWeek(gy, gm, gd) : 0,
      };
    });
    cachedRef.current = processed;
    return processed;
  }, [data]);

  // Global axis bounds from full dataset (anchored regardless of filters)
  const statBounds = useMemo(() => {
    if (cachedData.length === 0) return null;
    const b = {};
    for (const s of STATS) {
      const vals = cachedData.map(d => d[s]);
      b[s] = { min: Math.min(...vals), max: Math.max(...vals) };
    }
    return b;
  }, [cachedData]);

  // Separate special directives from data filters
  const heatmapFiltered = parsedFilters.some(f => f.key === 'heatmap' && f.value.toLowerCase() === 'true');
  const dataFilters = useMemo(() => parsedFilters.filter(f => f.key !== 'heatmap' && f.key !== 'dots' && f.key !== 'top'), [parsedFilters]);

  // Apply Datadog-style filters
  const filteredDataRaw = useMemo(() => applyFilters(cachedData, dataFilters), [cachedData, dataFilters]);

  // top:N directive — sort by current sort col/dir, then slice
  const topN = useMemo(() => {
    const tf = parsedFilters.find(f => f.key === 'top');
    if (!tf) return 0;
    const n = parseInt(tf.value, 10);
    return isNaN(n) || n <= 0 ? 0 : n;
  }, [parsedFilters]);

  const filteredData = useMemo(() => {
    if (!topN) return filteredDataRaw;
    const s = sortCol;
    const sorted = [...filteredDataRaw].sort((a, b) => {
      if (s === "player_name" || s === "team_id" || s === "opponent" || s === "game_date_short") {
        const cmp = (a[s]||"").localeCompare(b[s]||"");
        return sortDir === "desc" ? -cmp : cmp;
      }
      return sortDir === "desc" ? b[s]-a[s] : a[s]-b[s];
    });
    return sorted.slice(0, topN);
  }, [filteredDataRaw, topN, sortCol, sortDir]);

  // Heatmap sees full dataset unless heatmap:true is set
  const heatmapData = heatmapFiltered ? filteredData : cachedData;

  // Orange highlight: show dots when a player filter is active or dots:true
  const dotsEnabled = parsedFilters.some(f => f.key === 'dots' && f.value.toLowerCase() === 'true');
  const playerFilter = dataFilters.find(f => f.key === 'player');
  const hlData = useMemo(() => (playerFilter || dotsEnabled) ? filteredData : [], [filteredData, playerFilter, dotsEnabled]);

  // Heatmap bin cross-filter (from heatmap's dataset, not table's)
  const binFilteredGames = useMemo(() => {
    if (!selectedBin) return null;
    const { xi, yi, xMin, xStep, yMin, yStep, xKey, yKey } = selectedBin;
    return filteredData.filter(d => {
      const dxi = Math.min(HEATMAP_BINS-1, Math.floor((d[xKey]-xMin)/xStep));
      const dyi = Math.min(HEATMAP_BINS-1, Math.floor((d[yKey]-yMin)/yStep));
      return dxi === xi && dyi === yi;
    });
  }, [filteredData, selectedBin]);

  const handleBinClick = (xi, yi, hm) => {
    if (selectedBin && selectedBin.xi === xi && selectedBin.yi === yi) setSelectedBin(null);
    else setSelectedBin({ xi, yi, xMin:hm.xMin, xStep:hm.xStep, yMin:hm.yMin, yStep:hm.yStep, xKey:xAxis, yKey:yAxis });
    setPage(0);
  };
  const clearBin = () => { setSelectedBin(null); setPage(0); };

  // Table data
  const tableData = useMemo(() => {
    const base = binFilteredGames || filteredData;
    const s = sortCol;
    if (s === "player_name" || s === "team_id" || s === "opponent" || s === "game_date_short") {
      return [...base].sort((a, b) => { const cmp = (a[s]||"").localeCompare(b[s]||""); return sortDir === "desc" ? -cmp : cmp; });
    }
    return [...base].sort((a, b) => sortDir === "desc" ? b[s]-a[s] : a[s]-b[s]);
  }, [filteredData, binFilteredGames, sortCol, sortDir]);

  const totalPages = Math.max(1, Math.ceil(tableData.length / PAGE_SIZE));
  const safeePage = Math.min(page, totalPages - 1);
  const pageData = tableData.slice(safeePage * PAGE_SIZE, (safeePage + 1) * PAGE_SIZE);
  const handleSort = (sortKey) => {
    if (sortCol === sortKey) setSortDir(d => d === "desc" ? "asc" : "desc");
    else { setSortCol(sortKey); setSortDir("desc"); }
    setPage(0);
  };

  // Click a table row to toggle player filter in the text box
  const handleRowClick = (playerName) => {
    const escaped = playerName.includes(' ') ? `player:"${playerName}"` : `player:${playerName}`;
    const isAlready = parsedFilters.some(f => f.key === 'player' && f.value.toLowerCase() === playerName.toLowerCase());
    if (isAlready) {
      setFilterText(prev =>
        prev
          .replace(new RegExp(`player:"${playerName.replace(/[.*+?^${}()|[\]\\]/g, '\\$&')}"`, 'gi'), '')
          .replace(new RegExp(`player:${playerName.replace(/[.*+?^${}()|[\]\\]/g, '\\$&')}(?=[\\s]|$)`, 'gi'), '')
          .replace(/\s+/g, ' ').trim()
      );
    } else {
      setFilterText(prev => (prev.trim() ? prev.trim() + ' ' : '') + escaped);
    }
    setPage(0);
  };

  const btnStyle = (dis) => ({fontSize:11,padding:"4px 8px",borderRadius:4,border:"1px solid #ccc",background:"#fff",cursor:dis?"not-allowed":"pointer",opacity:dis?0.3:1});

  const filterCount = dataFilters.length;

  return (
    <div style={{background:"#f8f8f8",color:"#231f20",maxWidth:900,fontFamily:"system-ui, sans-serif"}}>
      <div style={{padding:20}}>
        <h1 style={{fontSize:20,fontWeight:700,marginBottom:4}}>NBA Game Quality Explorer — 2025-26</h1>
        <p style={{fontSize:14,color:"#6a6a6a",marginBottom:16}}>Individual game performances ranked by Game Quality — a head-to-head dominance score across 9 stat categories within each week.</p>

        {/* ── Filter bar ──────────────────────────────────────────── */}
        <div style={{display:"flex",gap:8,alignItems:"stretch",marginBottom:16}}>
          <div style={{flex:1,position:"relative"}}>
            {filterMode === 'edit' ? (
              <input ref={inputRef} type="text" value={filterText}
                onChange={e => { setFilterText(e.target.value); setPage(0); setSelectedBin(null); }}
                onKeyDown={handleFilterKeyDown}
                placeholder='filter on key:"value"  e.g. team:LAL player:"LeBron James" opp:BOS'
                style={{fontSize:13,border:"1px solid #ccc",borderRadius:4,padding:"7px 10px",background:"#fff",width:"100%",fontFamily:"'SF Mono',SFMono-Regular,Consolas,monospace",boxSizing:"border-box"}} />
            ) : (
              <div style={{fontSize:13,border:"1px solid #ccc",borderRadius:4,padding:"7px 10px",background:"#f0f0f0",fontFamily:"'SF Mono',SFMono-Regular,Consolas,monospace",color:"#231f20",minHeight:32,whiteSpace:"pre-wrap",boxSizing:"border-box"}}>
                {filtersToSQL(parsedFilters)}
              </div>
            )}
            {filterText && filterMode === 'edit' && (
              <span style={{position:"absolute",right:8,top:"50%",transform:"translateY(-50%)",cursor:"pointer",fontSize:13,color:"#999",lineHeight:1}}
                onClick={() => { setFilterText(""); setPage(0); setSelectedBin(null); }}>✕</span>
            )}
          </div>
          <div style={{display:"flex",gap:8,flexShrink:0,alignItems:"center"}}>
            <span onClick={() => setFilterMode('edit')}
              style={{fontSize:11,cursor:"pointer",color:"#6a6a6a",
                textDecoration:filterMode==='edit'?'underline':'none',
                textUnderlineOffset:3}}>
              edit
            </span>
            <span onClick={() => setFilterMode('sql')}
              style={{fontSize:11,cursor:"pointer",color:"#6a6a6a",
                textDecoration:filterMode==='sql'?'underline':'none',
                textUnderlineOffset:3}}>
              SQL
            </span>
          </div>
        </div>

        {isLoading ? (
          <div style={{display:"flex",alignItems:"center",justifyContent:"center",padding:"64px 0",fontSize:14,color:"#6a6a6a"}}>Loading game data...</div>
        ) : isError ? (
          <div style={{display:"flex",alignItems:"center",justifyContent:"center",padding:"64px 0",fontSize:14,color:"#bc1200"}}>Error loading data. Please try refreshing.</div>
        ) : (
          <>
            <p style={{fontSize:11,color:"#6a6a6a",marginBottom:4}}>
              {STAT_LABELS[xAxis]} vs {STAT_LABELS[yAxis]} — {heatmapData.length} games{heatmapFiltered ? ' (heatmap filtered)' : ''}.
              {' '}Darker = more games. Click a cell to filter the table.
              {(playerFilter || dotsEnabled) && <span> Orange dots: filtered games.</span>}
              {selectedBin && <span style={{fontWeight:600}}> Cell selected — <span style={{cursor:"pointer",textDecoration:"underline"}} onClick={clearBin}>clear</span></span>}
            </p>

            {/* ── Chart area with axis dropdowns ────────────────── */}
            <div ref={chartRef} style={{position:"relative"}}>
              <div style={{display:"flex",alignItems:"flex-start",justifyContent:"space-between"}}>
                <Heatmap data={heatmapData} xKey={xAxis} yKey={yAxis} hlData={hlData}
                  selectedBin={selectedBin} onBinClick={handleBinClick}
                  onXLabelClick={() => setAxisDropdown(axisDropdown === 'x' ? null : 'x')}
                  onYLabelClick={() => setAxisDropdown(axisDropdown === 'y' ? null : 'y')}
                  bounds={statBounds ? { xMin: statBounds[xAxis].min, xMax: statBounds[xAxis].max, yMin: statBounds[yAxis].min, yMax: statBounds[yAxis].max } : { xMin:0, xMax:1, yMin:0, yMax:1 }}
                  width={800} height={280} />
                <GQBoxPlot data={heatmapData} hlData={hlData} height={280} />
              </div>

              {axisDropdown === 'x' && (
                <AxisDropdown stats={STATS} labels={STAT_LABELS} current={xAxis}
                  onSelect={s => { setXAxis(s); setAxisDropdown(null); clearBin(); }}
                  position={{bottom:-4,left:370}} />
              )}
              {axisDropdown === 'y' && (
                <AxisDropdown stats={STATS} labels={STAT_LABELS} current={yAxis}
                  onSelect={s => { setYAxis(s); setAxisDropdown(null); clearBin(); }}
                  position={{top:60,left:0}} />
              )}
            </div>

            <div style={{display:"flex",alignItems:"center",gap:4,marginTop:4,marginBottom:16}}>
              <span style={{fontSize:11,color:"#6a6a6a"}}>Fewer</span>
              {HEATMAP_COLORS.filter((_,i)=>i%3===0).map((c,i)=><div key={i} style={{width:14,height:8,background:c,borderRadius:1}} />)}
              <span style={{fontSize:11,color:"#6a6a6a"}}>More games</span>
            </div>

            {/* ── Table ──────────────────────────────────────────── */}
            <div style={{overflowX:"auto"}}>
              <div style={{display:"flex",alignItems:"center",justifyContent:"space-between",marginBottom:8}}>
                <p style={{fontSize:11,color:"#6a6a6a"}}>
                  {tableData.length} games{selectedBin?" — cell filter":""}
                  {totalPages>1?` — Page ${safeePage+1}/${totalPages}`:""}
                </p>
                <div style={{display:"flex",gap:8}}>
                  <button onClick={()=>setPage(p=>Math.max(0,p-1))} disabled={safeePage===0||totalPages<=1} style={btnStyle(safeePage===0||totalPages<=1)}>← Prev</button>
                  <button onClick={()=>setPage(p=>Math.min(totalPages-1,p+1))} disabled={safeePage>=totalPages-1||totalPages<=1} style={btnStyle(safeePage>=totalPages-1||totalPages<=1)}>Next →</button>
                </div>
              </div>
              <table style={{width:"100%",fontSize:11,borderCollapse:"collapse"}}>
                <thead>
                  <tr style={{borderBottom:"2px solid #231f20"}}>
                    <th style={{textAlign:"left",padding:"8px 4px",fontWeight:600,width:24}}>#</th>
                    {TABLE_COLS.map(col=>(
                      <th key={col.key} style={{textAlign:["player_name","team_id","opponent","game_date_short"].includes(col.key)?"left":"right",padding:"8px 4px",fontWeight:600,cursor:"pointer",whiteSpace:"nowrap",userSelect:"none"}}
                        onClick={()=>handleSort(col.sortKey)}>
                        {col.label}{sortCol===col.sortKey?(sortDir==="desc"?" ↓":" ↑"):""}
                      </th>
                    ))}
                  </tr>
                </thead>
                <tbody>
                  {pageData.map((g,i)=>{
                    const idx=safeePage*PAGE_SIZE+i;
                    const isHl = dotsEnabled || (playerFilter && g.player_name.toLowerCase().includes(playerFilter.value.toLowerCase()));
                    return (
                      <tr key={g.game_id+i} style={{borderBottom:"1px solid #e5e5e5",cursor:"pointer",background:isHl?"#fef3e2":idx%2===0?"transparent":"#f0f0f0"}}
                        onClick={()=>handleRowClick(g.player_name)}>
                        <td style={{padding:"4px",lineHeight:"20px",color:"#6a6a6a"}}>{idx+1}</td>
                        <td style={{padding:"4px",lineHeight:"20px",color:"#6a6a6a"}}>{g.team_id}</td>
                        <td style={{padding:"4px",lineHeight:"20px",fontWeight:500,minWidth:120}}>{g.player_name}</td>
                        <td style={{padding:"4px",lineHeight:"20px",color:"#6a6a6a"}}>{g.opponent}</td>
                        <td style={{padding:"4px",lineHeight:"20px",color:"#6a6a6a"}}>{g.game_date_short}</td>
                        <td style={{padding:"4px",lineHeight:"20px",textAlign:"right"}}>{g.mins}</td>
                        <td style={{padding:"4px",lineHeight:"20px",textAlign:"right",fontWeight:700}}>{g.points}</td>
                        <td style={{padding:"4px",lineHeight:"20px",textAlign:"right"}}>{g.fg3_made}</td>
                        <td style={{padding:"4px",lineHeight:"20px",textAlign:"right",whiteSpace:"nowrap"}}>{fmtPctVal(g.fg_pct,g.fg_v)}</td>
                        <td style={{padding:"4px",lineHeight:"20px",textAlign:"right",whiteSpace:"nowrap"}}>{fmtPctVal(g.ft_pct,g.ft_v)}</td>
                        <td style={{padding:"4px",lineHeight:"20px",textAlign:"right"}}>{g.rebounds}</td>
                        <td style={{padding:"4px",lineHeight:"20px",textAlign:"right"}}>{g.assists}</td>
                        <td style={{padding:"4px",lineHeight:"20px",textAlign:"right"}}>{g.steals}</td>
                        <td style={{padding:"4px",lineHeight:"20px",textAlign:"right"}}>{g.blocks}</td>
                        <td style={{padding:"4px",lineHeight:"20px",textAlign:"right"}}>{g.turnovers}</td>
                        <td style={{padding:"4px",lineHeight:"20px",textAlign:"right",fontWeight:700}}>{g.game_quality.toFixed(4)}</td>
                      </tr>
                    );
                  })}
                </tbody>
              </table>
              <div style={{display:"flex",justifyContent:"flex-end",marginTop:8,gap:8}}>
                <button onClick={()=>setPage(p=>Math.max(0,p-1))} disabled={safeePage===0||totalPages<=1} style={btnStyle(safeePage===0||totalPages<=1)}>← Prev</button>
                <button onClick={()=>setPage(p=>Math.min(totalPages-1,p+1))} disabled={safeePage>=totalPages-1||totalPages<=1} style={btnStyle(safeePage>=totalPages-1||totalPages<=1)}>Next →</button>
              </div>
            </div>
            <p style={{fontSize:11,color:"#6a6a6a",marginTop:16}}>
              <strong>Keys:</strong> team, player, opp, date, pts, reb, ast, stl, blk, to, 3ptm, min, gq, fg%, ft%, year, month, day, week, top — e.g. <code style={{background:"#e8e8e8",padding:"1px 4px",borderRadius:2}}>team:LA month:&gt;=12 top:10</code>. <code style={{background:"#e8e8e8",padding:"1px 4px",borderRadius:2}}>top:N</code> limits to top N by current sort. Click axis labels to change stats. Click rows to toggle player filter. Add <code style={{background:"#e8e8e8",padding:"1px 4px",borderRadius:2}}>heatmap:true</code> to apply filters to heatmap.
            </p>
          </>
        )}
      </div>
    </div>
  );
}
$abcd$,
description = 'Interactive NBA Game Quality explorer with Datadog-style key:value filter bar (Tab autocomplete, auto-close quotes, IN lists with bracket syntax, numeric comparisons, date part filters), edit/SQL toggle, 20x20 heatmap with contour edges and anchored axes, clickable axis label dropdowns, GQ box plot with player tick marks, orange dot overlay via player filter or dots:true directive, heatmap:true to apply filters to heatmap, sortable paginated table with click-to-filter rows.',
  api_version = 1
);