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


export const REQUIRED_DATABASES = [
  { type: 'share', path: 'md:_share/my_db/1d12c1a9-36a4-454e-9a27-5f8e8cb520bc', alias: 'my_db' }
];







const N = (v) => (v != null ? Number(v) : 0);

const VACCINE_YEARS = {
  "Measles": 1963, "Pertussis": 1948, "Polio": 1955,
  "Mumps": 1967, "Rubella": 1969, "Hepatitis A": 1995, "Smallpox": null,
};
const DISEASES = ["Measles", "Pertussis", "Polio", "Mumps", "Rubella", "Hepatitis A", "Smallpox"];

// sqrt-compressed blue->yellow->red ramp
function colorFor(rate, hasData) {
  if (!hasData || rate == null) return "#e8e8e8";
  const t = Math.min(1, Math.sqrt(rate) / Math.sqrt(2000));
  const stops = [
    [0.0, [247, 251, 255]], [0.15, [171, 217, 233]], [0.4, [254, 224, 144]],
    [0.7, [244, 109, 67]], [1.0, [165, 0, 38]],
  ];
  let lo = stops[0], hi = stops[stops.length - 1];
  for (let i = 0; i < stops.length - 1; i++) {
    if (t >= stops[i][0] && t <= stops[i + 1][0]) { lo = stops[i]; hi = stops[i + 1]; break; }
  }
  const span = (hi[0] - lo[0]) || 1;
  const f = (t - lo[0]) / span;
  const c = lo[1].map((v, i) => Math.round(v + (hi[1][i] - v) * f));
  return `rgb(${c[0]},${c[1]},${c[2]})`;
}

const WIDTH = 620, HEIGHT = 300;

export default function DiseaseMapTimeMachine() {
  const [disease, setDisease] = useDiveState("disease", "Measles");
  const [year, setYear] = useState(null);     // current displayed year (ephemeral)
  const [playing, setPlaying] = useState(false);
  const [hover, setHover] = useState(null);
  const timer = useRef(null);

  const vaxYear = VACCINE_YEARS[disease];

  // Geometry (static across diseases) — load once
  const geo = useSQLQuery(`
    SELECT state, geom_type, coordinates
    FROM "my_db"."main"."us_states_geo"
  `);

  // Year span for the selected disease
  const span = useSQLQuery(`
    SELECT MIN(year) AS min_y, MAX(year) AS max_y
    FROM "my_db"."main"."contagious_diseases"
    WHERE disease = '${disease}'
  `, { enabled: !!disease });

  // ALL state-year rates for this disease, loaded once. Animation indexes into this
  // in-memory map so no query fires per frame (prevents flashing).
  const allRates = useSQLQuery(`
    SELECT year, state, SUM(count) * 100000.0 / NULLIF(SUM(population), 0) AS rate
    FROM "my_db"."main"."contagious_diseases"
    WHERE disease = '${disease}'
    GROUP BY year, state
  `, { enabled: !!disease });

  // National trend for the timeline strip
  const trend = useSQLQuery(`
    SELECT year, SUM(count) * 100000.0 / NULLIF(SUM(population), 0) AS rate
    FROM "my_db"."main"."contagious_diseases"
    WHERE disease = '${disease}'
    GROUP BY year ORDER BY year
  `, { enabled: !!disease });

  const spanRow = Array.isArray(span.data) && span.data[0] ? span.data[0] : null;
  const minY = spanRow ? N(spanRow.min_y) : 1928;
  const maxY = spanRow ? N(spanRow.max_y) : 2011;

  // When disease (and thus span) changes, snap the year to the start
  useEffect(() => {
    if (spanRow) { setPlaying(false); setYear(N(spanRow.min_y)); }
    // eslint-disable-next-line
  }, [disease, spanRow && spanRow.min_y]);

  // Animation loop
  useEffect(() => {
    if (!playing) { if (timer.current) clearInterval(timer.current); return; }
    timer.current = setInterval(() => {
      setYear((y) => {
        const cur = y == null ? minY : y;
        if (cur >= maxY) { setPlaying(false); return maxY; }
        return cur + 1;
      });
    }, 360);
    return () => timer.current && clearInterval(timer.current);
    // eslint-disable-next-line
  }, [playing, minY, maxY]);

  function togglePlay() {
    if (!playing && (year == null || year >= maxY)) setYear(minY);
    setPlaying((p) => !p);
  }

  // Build projected paths
  const geoRows = Array.isArray(geo.data) ? geo.data : [];
  const features = geoRows.map((r) => {
    let coords;
    try { coords = typeof r.coordinates === "string" ? JSON.parse(r.coordinates) : r.coordinates; }
    catch { coords = null; }
    return { state: r.state, geometry: coords ? { type: r.geom_type, coordinates: coords } : null };
  }).filter((f) => f.geometry && !["Alaska", "Hawaii", "Puerto Rico"].includes(f.state));

  // Planar projection (geoIdentity) treats lng/lat as flat X/Y. This sidesteps
  // d3's spherical winding-order rule, which was filling a whole hemisphere when
  // a ring was wound the "wrong" way. reflectY flips lat so north is up.
  const projection = d3.geoIdentity().reflectY(true);
  if (features.length) {
    const fc = { type: "FeatureCollection", features: features.map((f) => ({ type: "Feature", geometry: f.geometry })) };
    projection.fitExtent([[12, 12], [WIDTH - 12, HEIGHT - 12]], fc);
  }
  const pathGen = d3.geoPath(projection);

  // Build { year: { state: rate } } once from the full result set
  const allRows = Array.isArray(allRates.data) ? allRates.data : [];
  const byYear = {};
  for (const r of allRows) {
    const yr = N(r.year);
    (byYear[yr] || (byYear[yr] = {}))[r.state] = N(r.rate);
  }
  const curYear = year ?? minY;
  const rateMap = byYear[curYear] || {};
  const ratesReady = !allRates.isLoading && allRows.length > 0;
  const normName = (s) => (s === "District of Columbia" ? "District Of Columbia" : s);

  const trendRows = Array.isArray(trend.data) ? trend.data : [];
  const maxTrend = trendRows.reduce((m, r) => Math.max(m, N(r.rate)), 1);

  const hoverRate = hover ? rateMap[normName(hover)] : undefined;

  return (
    <div className="p-4" style={{ background: "#f8f8f8", fontFamily: "system-ui, sans-serif", maxWidth: 660, margin: "0 auto" }}>
      <h1 className="text-lg font-semibold" style={{ color: "#231f20", margin: 0 }}>Disease Time Machine</h1>
      <p style={{ fontSize: 12, color: "#6a6a6a", margin: "4px 0 10px" }}>
        US reported cases per 100,000 people, by state. Press play to sweep through the years and watch the country
        cool off after the vaccine arrives.
      </p>

      {/* disease selector */}
      <div style={{ display: "flex", gap: 5, flexWrap: "wrap", marginBottom: 10 }}>
        {DISEASES.map((d) => (
          <button key={d} onClick={() => setDisease(d)} style={{
            padding: "3px 9px", fontSize: 12, borderRadius: 4, cursor: "pointer",
            border: d === disease ? "1px solid #0777b3" : "1px solid #d4d4d4",
            background: d === disease ? "#0777b3" : "#ffffff",
            color: d === disease ? "#ffffff" : "#231f20", fontWeight: d === disease ? 600 : 400,
          }}>{d}</button>
        ))}
      </div>

      {/* play controls + year */}
      <div style={{ display: "flex", alignItems: "center", gap: 12, marginBottom: 4 }}>
        <button onClick={togglePlay} style={{
          width: 70, padding: "5px 0", fontSize: 12, fontWeight: 600, borderRadius: 4,
          border: "1px solid #0777b3", background: playing ? "#ffffff" : "#0777b3",
          color: playing ? "#0777b3" : "#ffffff", cursor: "pointer",
        }}>{playing ? "❚❚ Pause" : "▶ Play"}</button>

        <input type="range" min={minY} max={maxY} value={year ?? minY}
          onChange={(e) => { setPlaying(false); setYear(Number(e.target.value)); }}
          style={{ flex: 1, accentColor: "#0777b3" }} />

        <div style={{ width: 56, textAlign: "right", fontSize: 22, fontWeight: 700, color: "#231f20", fontVariantNumeric: "tabular-nums" }}>
          {year ?? minY}
        </div>
      </div>

      {/* vaccine status line */}
      <div style={{ height: 16, fontSize: 11, marginBottom: 4, color: vaxYear && (year ?? minY) >= vaxYear ? "#2d7a00" : "#6a6a6a" }}>
        {hover
          ? `${hover}: ${hoverRate != null ? hoverRate.toFixed(1) + " per 100k" : "no data"}`
          : vaxYear
            ? ((year ?? minY) >= vaxYear ? `✓ Vaccine introduced ${vaxYear} — ${(year ?? minY) - vaxYear} years on` : `Vaccine arrives in ${vaxYear} (in ${vaxYear - (year ?? minY)} years)`)
            : "No routine vaccine in this period"}
      </div>

      {/* map */}
      {geo.isLoading ? (
        <div className="animate-pulse" style={{ height: HEIGHT, background: "#ececec", borderRadius: 4, maxHeight: 300 }} />
      ) : (
        <svg viewBox={`0 0 ${WIDTH} ${HEIGHT}`} width="100%" style={{ background: "transparent", display: "block", maxHeight: 300 }}>
          {/* pass 1: fills */}
          <g>
            {features.map((f) => {
              const rate = rateMap[normName(f.state)];
              const has = rate !== undefined && ratesReady;
              const d = pathGen(f.geometry);
              if (!d) return null;
              return (
                <path key={"fill-" + f.state} d={d}
                  fill={colorFor(rate, has)} stroke="none"
                  onMouseEnter={() => setHover(f.state)} onMouseLeave={() => setHover(null)}
                  style={{ cursor: "pointer", opacity: hover && hover !== f.state ? 0.85 : 1, transition: "fill 280ms linear" }} />
              );
            })}
          </g>
          {/* pass 2: outlines always drawn on top, so the US map shows by default */}
          <g style={{ pointerEvents: "none" }}>
            {features.map((f) => {
              const d = pathGen(f.geometry);
              if (!d) return null;
              return (
                <path key={"line-" + f.state} d={d} fill="none"
                  stroke={hover === f.state ? "#231f20" : "#6b7280"}
                  strokeWidth={hover === f.state ? 1.4 : 0.7} />
              );
            })}
          </g>
        </svg>
      )}

      {/* national trend timeline */}
      <div style={{ marginTop: 4 }}>
        <svg viewBox={`0 0 ${WIDTH} 56`} width="100%" style={{ display: "block", maxHeight: 56 }}>
          {(() => {
            if (!trendRows.length) return null;
            const x = (yr) => ((yr - minY) / Math.max(1, maxY - minY)) * (WIDTH - 8) + 4;
            const y = (rt) => 48 - (Math.sqrt(rt) / Math.sqrt(maxTrend)) * 40;
            const line = trendRows.map((r, i) => `${i === 0 ? "M" : "L"}${x(N(r.year)).toFixed(1)},${y(N(r.rate)).toFixed(1)}`).join(" ");
            const cy = year ?? minY;
            return (
              <>
                <path d={line} fill="none" stroke="#0777b3" strokeWidth={1.4} />
                {vaxYear && vaxYear >= minY && vaxYear <= maxY && (
                  <line x1={x(vaxYear)} x2={x(vaxYear)} y1={2} y2={52} stroke="#2d7a00" strokeWidth={1.2} strokeDasharray="3 3" />
                )}
                <line x1={x(cy)} x2={x(cy)} y1={0} y2={54} stroke="#231f20" strokeWidth={1.2} />
                <text x={4} y={10} fontSize={8} fill="#9a9a9a">national rate per 100k</text>
                {vaxYear && vaxYear >= minY && vaxYear <= maxY && (
                  <text x={x(vaxYear) + 3} y={10} fontSize={8} fill="#2d7a00">vaccine {vaxYear}</text>
                )}
              </>
            );
          })()}
        </svg>
      </div>

      {/* legend */}
      <div style={{ display: "flex", alignItems: "center", gap: 7, marginTop: 4, fontSize: 10, color: "#6a6a6a" }}>
        <span>Lower</span>
        <div style={{ display: "flex" }}>
          {[0, 5, 20, 80, 200, 600, 1500].map((r) => (
            <div key={r} style={{ width: 20, height: 11, background: colorFor(r, true) }} />
          ))}
        </div>
        <span>Higher (per 100k)</span>
        <div style={{ width: 12, height: 11, background: "#e8e8e8", marginLeft: 8 }} />
        <span>No data</span>
      </div>

      <p style={{ fontSize: 10, color: "#9a9a9a", marginTop: 8 }}>
        Source: Project Tycho (University of Pittsburgh), packaged by Rafael Irizarry. Contiguous 48 states + DC shown.
      </p>
    </div>
  );
}

Animated US choropleth map of contagious-disease incidence (per 100k) by state, 1928–2011. Play button sweeps through the years; a national trend strip marks the vaccine year. Switch diseases to watch the map cool after each vaccine. Data: Project Tycho (R. Irizarry).

Disease Time Machine — Animated Map

I have the MotherDuck MCP connected. Build me an animated "Disease Time Machine" Dive that shows how US contagious diseases collapsed after their vaccines were introduced.
Data: Load https://raw.githubusercontent.com/firasm/bits/master/us_contagious_diseases.csv into a MotherDuck table with read_csv_auto. It has columns disease, state, year, weeks_reporting, count, population — 7 diseases (Measles, Pertussis, Polio, Mumps, Rubella, Hepatitis A, Smallpox), 51 states, 1928–2011. Also load a US states GeoJSON (https://raw.githubusercontent.com/PublicaMundi/MappingAPI/master/data/geojson/us-states.json) into a geometry table, storing each state's name, geometry type, and coordinates as JSON.
Visualization: An animated US choropleth map. Each state is shaded by reported cases per 100,000 people (count / population * 100000) for the selected year. Include:

A disease selector (buttons), defaulting to Measles
A Play button + a year scrubber slider, with a big year readout
A line under the map showing the national trend over time, with a dashed marker at the vaccine year and a playhead at the current year
A status line that says how many years before/after the vaccine the current year is
Hover on a state to show its exact rate

Disease Time Machine — Animated Map

AI Prompts Used

Comments