import { useRef, useEffect, useState, useMemo } from "react";
import { useSQLQuery, useDiveState } from "@motherduck/react-sql-query";


export const REQUIRED_DATABASES = [
  { type: 'share', path: 'md:_share/bay_area_isometric/47ae4135-5b64-4d35-87b4-53751b4d4d25', alias: 'bay_area_isometric' }
];
const N = (v) => (v != null ? Number(v) : 0);
const CELL = 250;

const WATER = "#236aa0";
const LAND = [188, 170, 120];
const PARK = [78, 150, 62];
const LOWRISE = [120, 132, 146];
const MIDRISE = [226, 150, 52];
const HIGHRISE = [252, 232, 150];
const UNDER = [226, 72, 96];
const DESAT = [116, 120, 126];
const ROAD = [255, 210, 74];
function densityColor(c) {
  if (c <= 4) return [86, 124, 158];
  if (c <= 12) return [82, 152, 176];
  if (c <= 30) return [130, 190, 184];
  if (c <= 70) return [224, 156, 74];
  if (c <= 140) return [206, 96, 60];
  return [178, 52, 46];
}
function priceColor(p) {
  if (p == null) return LAND;
  if (p <= 700000) return [92, 138, 170];
  if (p <= 1000000) return [120, 182, 168];
  if (p <= 1300000) return [206, 200, 120];
  if (p <= 1700000) return [232, 170, 70];
  if (p <= 2200000) return [222, 112, 56];
  if (p <= 3000000) return [190, 62, 50];
  return [150, 32, 46];
}
const buildupColor = (h) => (h < 15 ? LOWRISE : h < 50 ? MIDRISE : HIGHRISE);
const mul = (c, f) => `rgb(${Math.round(c[0]*f)},${Math.round(c[1]*f)},${Math.round(c[2]*f)})`;

const PEAKS = [
  [-122.4477, 37.7544, 260, 0.85], [-122.4548, 37.7380, 250, 0.80], [-122.4576, 37.7600, 235, 0.70],
  [-122.4360, 37.7920, 95, 0.70],  [-122.4156, 37.7430, 120, 0.50], [-122.4220, 37.7180, 130, 0.90],
  [-122.435, 37.690, 360, 1.1],    [-122.490, 37.550, 560, 1.4],    [-122.420, 37.580, 360, 1.2],
  [-122.340, 37.470, 520, 1.6],    [-122.230, 37.380, 760, 1.8],    [-122.150, 37.315, 820, 1.7],
  [-122.220, 37.890, 500, 1.3],    [-122.160, 37.810, 470, 1.4],    [-122.090, 37.730, 470, 1.5],
  [-121.920, 37.880, 1170, 2.6],   [-121.880, 37.515, 1000, 2.0],   [-121.950, 37.620, 500, 1.6],
  [-122.580, 37.910, 700, 2.2],
];
const TERRAIN_SCALE = 0.3;
function elevAt(lon, lat) {
  let e = 0;
  for (const [clon, clat, pk, sig] of PEAKS) {
    const dx = (lon - clon) * 88.01, dy = (lat - clat) * 110.54;
    e += pk * Math.exp(-(dx*dx + dy*dy) / (2*sig*sig));
  }
  return Math.min(1200, e);
}
const cellLon = (gx) => (gx + 0.5) * CELL / 88010 - 122.355;
const cellLat = (gy) => (gy + 0.5) * CELL / 110540 + 37.775;

const LABELS = [
  ["SAN FRANCISCO", -122.437, 37.760], ["OAKLAND", -122.271, 37.804], ["BERKELEY", -122.273, 37.870],
  ["RICHMOND", -122.350, 37.935], ["SAN RAFAEL", -122.531, 37.974], ["SAN MATEO", -122.310, 37.560],
  ["PALO ALTO", -122.143, 37.444], ["FREMONT", -121.988, 37.548], ["HAYWARD", -122.080, 37.668],
  ["SAN JOSE", -121.890, 37.335], ["MT DIABLO", -121.914, 37.881],
];

export default function BayAreaIsometric() {
  const { data, isLoading, isError, error } = useSQLQuery(`
    SELECT gx, gy, kind, h, cnt, price
    FROM "bay_area_isometric"."main"."bay_iso"
    WHERE kind IN ('land','park') OR h > 0
    ORDER BY (gx+gy) ASC, gy ASC
  `);
  const rows = Array.isArray(data) ? data : [];

  const { data: roadData } = useSQLQuery(`SELECT cls, wkt FROM "bay_area_isometric"."main"."bay_roads"`);
  const roads = useMemo(() => {
    const out = [];
    for (const r of (Array.isArray(roadData) ? roadData : [])) {
      const w = r.wkt; if (!w) continue;
      const i = w.indexOf("("), j = w.lastIndexOf(")");
      if (i < 0 || j < 0) continue;
      const pts = w.substring(i + 1, j).split(",").map((s) => {
        const p = s.trim().split(/\s+/); return [Number(p[0]), Number(p[1])];
      });
      if (pts.length > 1) out.push({ cls: r.cls, pts });
    }
    return out;
  }, [roadData]);

  const [mode, setMode] = useDiveState("mode", "buildup");
  const [under, setUnder] = useDiveState("under", false);
  const [zoom, setZoom] = useDiveState("zoom", 5);
  const [exag, setExag] = useDiveState("exag", 1.2);
  const [showLabels, setShowLabels] = useDiveState("labels", true);
  const [showRoads, setShowRoads] = useDiveState("roads", true);
  const [terrain, setTerrain] = useDiveState("terrain", true);

  const canvasRef = useRef(null);
  const wrapRef = useRef(null);
  const layoutRef = useRef(null);
  const centeredRef = useRef(false);
  const [hover, setHover] = useState(null);

  // ---- live insight stats ----
  let built = 0, low = 0, mid = 0, high = 0, totalBuildings = 0;
  const cnts = [], prices = [];
  for (const r of rows) {
    const h = N(r.h), c = N(r.cnt);
    totalBuildings += c;
    if (h > 3) {
      built++; cnts.push(c);
      if (r.price != null) prices.push(Number(r.price));
      if (h < 15) low++; else if (h < 50) mid++; else high++;
    }
  }
  cnts.sort((a, b) => a - b);
  prices.sort((a, b) => a - b);
  const p90 = cnts.length ? cnts[Math.floor(cnts.length * 0.9)] : 1e9;
  const medianPrice = prices.length ? prices[Math.floor(prices.length / 2)] : 0;
  const p66Price = prices.length ? prices[Math.floor(prices.length * 0.66)] : Infinity;
  let dense = 0, denseLow = 0, pricedHi = 0, pricedHiLow = 0;
  for (const r of rows) {
    const h = N(r.h), c = N(r.cnt), p = r.price != null ? Number(r.price) : null;
    if (h > 3 && c >= p90) { dense++; if (h < 15) denseLow++; }
    if (h > 3 && p != null && p >= p66Price) { pricedHi++; if (h < 15) pricedHiLow++; }
  }
  const pctLow = built ? Math.round(low / built * 1000) / 10 : 0;
  const pctMid = built ? Math.round(mid / built * 1000) / 10 : 0;
  const pctHigh = built ? Math.round(high / built * 1000) / 10 : 0;
  const pctDenseLow = dense ? Math.round(denseLow / dense * 1000) / 10 : 0;
  const pctPriceyLow = pricedHi ? Math.round(pricedHiLow / pricedHi * 1000) / 10 : 0;

  const headline = under
    ? { big: `${pctDenseLow}%`, text: "of the Bay's densest blocks are still low-rise. Where it packs buildings in tightest, it still refuses to build up." }
    : mode === "price"
    ? { big: `$${(medianPrice/1e6).toFixed(1)}M`, text: `is the typical built block's home value — yet ${pctPriceyLow}% of the priciest third of blocks stay low-rise. Colour maps ZIP-level values, low (blue) to high (red).` }
    : mode === "buildup"
    ? { big: `${pctLow}%`, text: `of built-up blocks top out under 15 m — about five stories. Only ${pctHigh}% reach high-rise, so the Bay's whole skyline is three downtowns: SF, Oakland, and San Jose.` }
    : { big: totalBuildings.toLocaleString(), text: "buildings fill the core Bay, and the densest 250 m block packs in over 100 — yet almost all of it still stays low." };

  useEffect(() => {
    const cv = canvasRef.current;
    if (!cv || !rows.length) return;
    const ctx = cv.getContext("2d");

    const TW = zoom, hw = TW/2, hh = TW/4;
    const pxPerM = TW * 0.04 * exag;
    const GROUND = Math.max(TW * 0.55, 3);

    let minx = Infinity, miny = Infinity, maxx = -Infinity, maxy = -Infinity;
    for (const r of rows) {
      const gx = N(r.gx), gy = N(r.gy);
      if (gx < minx) minx = gx; if (gx > maxx) maxx = gx;
      if (gy < miny) miny = gy; if (gy > maxy) maxy = gy;
    }
    const proj = (gx, gy) => [((gy-miny)-(gx-minx))*hw, ((gx-minx)+(gy-miny))*hh];
    const elevPx = (gx, gy) => terrain ? elevAt(cellLon(gx), cellLat(gy)) * pxPerM * TERRAIN_SCALE : 0;
    const topH = (gx, gy, h) => GROUND + elevPx(gx, gy) + (h > 3 ? h*pxPerM : 0);

    let minSX = Infinity, maxSX = -Infinity, minSY = Infinity, maxSY = -Infinity;
    for (const r of rows) {
      const gx = N(r.gx), gy = N(r.gy);
      const [sx, sy] = proj(gx, gy);
      const t = sy - topH(gx, gy, N(r.h));
      if (sx-hw < minSX) minSX = sx-hw; if (sx+hw > maxSX) maxSX = sx+hw;
      if (t-hh < minSY) minSY = t-hh;   if (sy+hh > maxSY) maxSY = sy+hh;
    }

    const pad = 28;
    const W = Math.ceil(maxSX-minSX)+pad*2, H = Math.ceil(maxSY-minSY)+pad*2;
    cv.width = W; cv.height = H;
    const ox = -minSX+pad, oy = -minSY+pad;
    const outline = TW >= 8;

    ctx.fillStyle = WATER;
    ctx.fillRect(0, 0, W, H);

    const colorFor = (h, cnt, kind, price) => {
      if (h <= 3) return kind === "park" ? PARK : LAND;
      if (under) return (cnt >= p90 && h < 15) ? UNDER : DESAT;
      return mode === "buildup" ? buildupColor(h) : mode === "price" ? priceColor(price) : densityColor(cnt);
    };

    for (const r of rows) {
      const gx = N(r.gx), gy = N(r.gy), h = N(r.h), cnt = N(r.cnt);
      const price = r.price != null ? Number(r.price) : null;
      const [sx, sy] = proj(gx, gy);
      const X = sx+ox, Y = sy+oy;
      const ty = Y - topH(gx, gy, h);
      const base = colorFor(h, cnt, r.kind, price);

      ctx.fillStyle = mul(base, 0.72);
      ctx.beginPath(); ctx.moveTo(X-hw, ty); ctx.lineTo(X, ty+hh); ctx.lineTo(X, Y+hh); ctx.lineTo(X-hw, Y); ctx.closePath(); ctx.fill();
      ctx.fillStyle = mul(base, 0.52);
      ctx.beginPath(); ctx.moveTo(X+hw, ty); ctx.lineTo(X, ty+hh); ctx.lineTo(X, Y+hh); ctx.lineTo(X+hw, Y); ctx.closePath(); ctx.fill();
      ctx.fillStyle = mul(base, 1.0);
      ctx.beginPath(); ctx.moveTo(X, ty-hh); ctx.lineTo(X+hw, ty); ctx.lineTo(X, ty+hh); ctx.lineTo(X-hw, ty); ctx.closePath(); ctx.fill();

      if (outline) {
        ctx.strokeStyle = "rgba(18,16,24,0.34)"; ctx.lineWidth = 1;
        ctx.beginPath(); ctx.moveTo(X, ty-hh); ctx.lineTo(X+hw, ty); ctx.lineTo(X, ty+hh); ctx.lineTo(X-hw, ty); ctx.closePath(); ctx.stroke();
        ctx.beginPath(); ctx.moveTo(X, ty+hh); ctx.lineTo(X, Y+hh); ctx.stroke();
      }
    }

    // ---- freeway network ----
    if (showRoads && roads.length) {
      const llToScreen = (lon, lat) => {
        const gx = (lon + 122.355) * 88010 / CELL - 0.5;
        const gy = (lat - 37.775) * 110540 / CELL - 0.5;
        const sx = ((gy - miny) - (gx - minx)) * hw, sy = ((gx - minx) + (gy - miny)) * hh;
        const lift = GROUND + (terrain ? elevAt(lon, lat) * pxPerM * TERRAIN_SCALE : 0);
        return [sx + ox, sy + oy - lift];
      };
      const s = Math.max(1, TW / 5);
      ctx.lineJoin = "round"; ctx.lineCap = "round";
      for (let pass = 0; pass < 2; pass++) {
        for (const rd of roads) {
          const mw = rd.cls === "motorway";
          if (pass === 0) { ctx.strokeStyle = "rgba(36,26,12,0.5)"; ctx.lineWidth = (mw ? 3.2 : 2.2) * s; }
          else { ctx.strokeStyle = mw ? "rgb(255,210,74)" : "rgb(245,188,120)"; ctx.lineWidth = (mw ? 1.5 : 1.0) * s; }
          ctx.beginPath();
          const pts = rd.pts;
          for (let k = 0; k < pts.length; k++) {
            const [X, Y] = llToScreen(pts[k][0], pts[k][1]);
            if (k === 0) ctx.moveTo(X, Y); else ctx.lineTo(X, Y);
          }
          ctx.stroke();
        }
      }
    }

    if (showLabels) {
      ctx.font = "bold 11px ui-sans-serif, system-ui, sans-serif";
      ctx.textAlign = "center"; ctx.textBaseline = "middle";
      for (const [name, lon, lat] of LABELS) {
        const lgx = Math.floor((lon+122.355)*88010/CELL), lgy = Math.floor((lat-37.775)*110540/CELL);
        const [sx, sy] = proj(lgx, lgy);
        const X = sx+ox, Y = sy+oy - topH(lgx, lgy, 0) - 2;
        const w = ctx.measureText(name).width;
        ctx.fillStyle = "rgba(14,22,32,0.78)";
        ctx.fillRect(X-w/2-5, Y-8, w+10, 16);
        ctx.fillStyle = "#f2efe6";
        ctx.fillText(name, X, Y+1);
      }
    }

    const m = new Map();
    for (const r of rows) m.set(N(r.gx)+","+N(r.gy), { h: N(r.h), cnt: N(r.cnt), kind: r.kind, price: r.price != null ? Number(r.price) : null });
    layoutRef.current = { ox, oy, hw, hh, minx, miny, map: m };

    if (!centeredRef.current && wrapRef.current) {
      const wrap = wrapRef.current;
      wrap.scrollLeft = (W - wrap.clientWidth)/2;
      wrap.scrollTop = Math.max(0, (H - wrap.clientHeight)/2);
      centeredRef.current = true;
    }
  }, [rows, roads, mode, under, zoom, exag, showLabels, showRoads, terrain, p90]);

  const onMove = (e) => {
    const cv = canvasRef.current, L = layoutRef.current;
    if (!cv || !L) return;
    const rect = cv.getBoundingClientRect();
    const a = (e.clientX-rect.left-L.ox)/L.hw, b = (e.clientY-rect.top-L.oy)/L.hh;
    const col = Math.round((b-a)/2), row = Math.round((a+b)/2);
    const t = L.map && L.map.get((col+L.minx)+","+(row+L.miny));
    if (t) {
      const el = terrain ? Math.round(elevAt(cellLon(col+L.minx), cellLat(row+L.miny))) : null;
      setHover({ x: e.clientX, y: e.clientY, ...t, el });
    } else setHover(null);
  };

  const btn = (active) => ({
    padding: "4px 12px", fontSize: "12px", fontWeight: 600, cursor: "pointer", borderRadius: "4px",
    border: "1px solid " + (active ? "#0777b3" : "#cfcfcf"),
    background: active ? "#0777b3" : "#ffffff", color: active ? "#ffffff" : "#231f20",
  });

  let legend = under
    ? [["dense & low-rise", UNDER], ["other built", DESAT], ["land", LAND], ["park", PARK]]
    : mode === "price"
    ? [["≤$0.7M", priceColor(650000)], ["~$1M", priceColor(950000)], ["~$1.5M", priceColor(1500000)], ["~$2M", priceColor(2100000)], ["$3M+", priceColor(4000000)], ["land", LAND]]
    : mode === "buildup"
    ? [["low-rise < 15 m", LOWRISE], ["mid-rise 15–50", MIDRISE], ["high-rise 50 m+", HIGHRISE], ["land", LAND], ["park", PARK]]
    : [["1–4", densityColor(3)], ["5–30", densityColor(15)], ["31–140", densityColor(80)], ["140 +", densityColor(200)], ["land", LAND]];
  if (showRoads) legend = [...legend, ["freeways", ROAD]];
  const legendCaption = under ? "where the Bay is densest but lowest"
    : mode === "price" ? "home value per block (ZIP-level)"
    : mode === "buildup" ? "how tall each block builds"
    : "buildings / 250 m block";

  const hoverText = (hv) => {
    let s = hv.cnt > 0 ? `tallest ~${Math.round(hv.h)} m · ${hv.cnt} building${hv.cnt===1?"":"s"}`
                       : hv.kind === "park" ? "park / open space" : "open land";
    if (hv.el != null) s += ` · ground ~${hv.el} m`;
    if (hv.price != null) s += ` · ~$${(hv.price/1e6).toFixed(2)}M area`;
    return s;
  };

  return (
    <div className="p-4" style={{ background: "#f8f8f8" }}>
      <h1 className="text-2xl font-semibold" style={{ color: "#231f20" }}>The Bay Builds Out, Not Up</h1>
      <p className="text-sm" style={{ color: "#6a6a6a", marginBottom: "10px" }}>
        Some of the priciest housing in the U.S. — yet almost none of it rises past five stories. Every building from San Francisco to San Jose, mapped by height (Overture Maps, 250 m blocks).
      </p>

      <div style={{ background: "#fff", border: "1px solid #e2e2e2", borderLeft: "4px solid #e0922f", borderRadius: "6px", padding: "10px 14px", marginBottom: "12px" }}>
        <div style={{ display: "flex", alignItems: "baseline", gap: "12px" }}>
          <span style={{ fontSize: "30px", fontWeight: 700, color: "#e0922f", lineHeight: 1 }}>
            {isLoading ? "…" : headline.big}
          </span>
          <span style={{ fontSize: "13px", color: "#3a3a3a" }}>{isLoading ? "Loading the core Bay Area…" : headline.text}</span>
        </div>
        {!isLoading && !under && mode === "buildup" && (
          <div style={{ marginTop: "10px" }}>
            <div style={{ display: "flex", height: "12px", borderRadius: "3px", overflow: "hidden", border: "1px solid #e7e7e7" }}>
              <div style={{ width: `${pctLow}%`, background: mul(LOWRISE, 1) }} title={`low-rise ${pctLow}%`} />
              <div style={{ width: `${pctMid}%`, background: mul(MIDRISE, 1) }} title={`mid-rise ${pctMid}%`} />
              <div style={{ width: `${Math.max(pctHigh, 0.6)}%`, background: mul(HIGHRISE, 1) }} title={`high-rise ${pctHigh}%`} />
            </div>
            <div style={{ display: "flex", gap: "16px", marginTop: "5px", fontSize: "11px", color: "#6a6a6a" }}>
              <span><b style={{ color: "#5a6472" }}>{pctLow}%</b> low-rise (≤5 floors)</span>
              <span><b style={{ color: "#c2832e" }}>{pctMid}%</b> mid-rise</span>
              <span><b style={{ color: "#b8902a" }}>{pctHigh}%</b> high-rise</span>
            </div>
          </div>
        )}
      </div>

      <div style={{ display: "flex", flexWrap: "wrap", gap: "10px", alignItems: "center", marginBottom: "10px" }}>
        <div style={{ display: "flex", gap: "6px" }}>
          <button style={btn(!under && mode === "buildup")} onClick={() => { setUnder(false); setMode("buildup"); }}>Build-up</button>
          <button style={btn(!under && mode === "density")} onClick={() => { setUnder(false); setMode("density"); }}>Density</button>
          <button style={btn(!under && mode === "price")} onClick={() => { setUnder(false); setMode("price"); }}>Price</button>
        </div>
        <button style={btn(under)} onClick={() => setUnder(!under)}>Dense &amp; low</button>
        <button style={btn(showRoads)} onClick={() => setShowRoads(!showRoads)}>Roads</button>
        <button style={btn(terrain)} onClick={() => { centeredRef.current = false; setTerrain(!terrain); }}>Terrain</button>
        <button style={btn(showLabels)} onClick={() => setShowLabels(!showLabels)}>Labels</button>
        <label style={{ fontSize: "12px", color: "#6a6a6a", display: "flex", gap: "6px", alignItems: "center" }}>
          Zoom <input type="range" min={3} max={14} step={1} value={zoom} onChange={(e) => { centeredRef.current = false; setZoom(N(e.target.value)); }} />
        </label>
        <label style={{ fontSize: "12px", color: "#6a6a6a", display: "flex", gap: "6px", alignItems: "center" }}>
          Height × <input type="range" min={0.5} max={3} step={0.1} value={exag} onChange={(e) => setExag(N(e.target.value))} />
        </label>
      </div>

      <div style={{ display: "flex", gap: "10px", alignItems: "center", flexWrap: "wrap", marginBottom: "10px" }}>
        <span style={{ fontSize: "11px", color: "#6a6a6a", fontWeight: 600 }}>{legendCaption}:</span>
        {legend.map(([label, c]) => (
          <span key={label} style={{ display: "flex", gap: "4px", alignItems: "center", fontSize: "11px", color: "#6a6a6a" }}>
            <span style={{ width: "12px", height: "12px", background: mul(c, 1), display: "inline-block", borderRadius: "2px" }} />
            {label}
          </span>
        ))}
      </div>

      <div ref={wrapRef} style={{ overflow: "auto", maxHeight: "540px", border: "1px solid #e2e2e2", borderRadius: "6px", background: WATER, position: "relative" }}>
        {isLoading ? (
          <div className="animate-pulse" style={{ height: "500px", background: "#2b6f9e" }} />
        ) : isError ? (
          <div style={{ padding: "16px", color: "#bc1200", fontSize: "13px" }}>Query error: {String(error)}</div>
        ) : (
          <canvas ref={canvasRef} onMouseMove={onMove} onMouseLeave={() => setHover(null)} style={{ display: "block", imageRendering: "pixelated" }} />
        )}
      </div>

      {hover && (
        <div style={{ position: "fixed", left: hover.x+14, top: hover.y+14, background: "#231f20", color: "#fff", fontSize: "11px", padding: "4px 8px", borderRadius: "4px", pointerEvents: "none", zIndex: 50 }}>
          {hoverText(hover)}
        </div>
      )}
    </div>
  );
}


An isometric pixel-art map of every building in the core Bay Area (San Francisco to San Jose), built from Overture Maps footprints and heights in 250 m blocks. The thesis: one of the priciest housing markets in the U.S. is almost entirely low-rise — 94% of built-up blocks top out under 15 m (~5 stories) and only 0.4% reach high-rise, so the region's whole skyline is three downtowns (SF, Oakland, San Jose). Three views: Build-up (height typology with a distribution bar), Density (buildings per block), and Price (ZIP-level Zillow home values joined via Overture address ZIPs, with the proof that 96% of the priciest third of blocks stay low-rise). Plus a "Dense & low" highlight, the real freeway network, modeled terrain, place labels, and per-block hover with height, building count, ground elevation, and area home value.

The Bay Builds Out, Not Up

Build me a MotherDuck Dive: an isometric, SimCity-style pixel map of every building in the core Bay Area (San Francisco down to San Jose), rendered to an HTML canvas from real Overture Maps data. Aggregate building footprints and heights into 250 m grid cells, classify each cell as land / park / water via spatial joins against Overture's land-use and water polygons, and draw each cell as a 2:1 isometric tile extruded to its tallest building. Lead with a clear thesis as the title — the Bay barely builds up — and prove it with a live-computed headline stat (share of built blocks under 15 m ≈ 5 stories) plus a small height-distribution bar. Give it color modes for height typology, building density, and ZIP-level home value (join Zillow ZHVI, read directly via read_csv, to ZIPs derived from Overture address points), a "dense but low" highlight, the real freeway network as cased lines, modeled hill terrain, place labels, and a hover tooltip. Watch the isometric projection handedness so the geography isn't mirrored — SF must end up west of Oakland.

The Bay Builds Out, Not Up

AI Prompts Used

Comments