import { useSQLQuery, useDiveState } from "@motherduck/react-sql-query";
import {
  LineChart, Line, BarChart, Bar, XAxis, YAxis, CartesianGrid,
  Tooltip, ResponsiveContainer, Legend, Cell, ReferenceDot,
} from "recharts";


export const REQUIRED_DATABASES = [
  { type: 'database', path: 'md:climate_demo', alias: 'climate_demo' }
];
const N = (v: unknown): number => (v != null ? Number(v) : 0);

const PALETTE = ["#326fa8", "#e15759", "#59a14f", "#edc949", "#af7aa1"];
const INK = "#121212";
const MUTED = "#666";
const GRID = "#ececec";
const PAPER = "#fff";
const SERIF = "Georgia, 'Times New Roman', serif";

const MONTHS = ["", "Jan", "Feb", "Mar", "Apr", "May", "Jun",
  "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"];

const METRICS: Record<string, { col: string; label: string }> = {
  avg: { col: "avg_tmax_c", label: "Average daily high" },
  max: { col: "max_tmax_c", label: "Hottest day" },
  min: { col: "min_tmax_c", label: "Coldest daily high" },
};

function Toggle({ options, value, onChange }: {
  options: { v: string; label: string }[];
  value: string;
  onChange: (v: string) => void;
}) {
  return (
    <div style={{ display: "inline-flex", border: `1px solid ${INK}` }}>
      {options.map((o, i) => {
        const active = o.v === value;
        return (
          <button
            key={o.v}
            onClick={() => onChange(o.v)}
            style={{
              fontFamily: SERIF,
              fontSize: 13,
              padding: "4px 12px",
              cursor: "pointer",
              background: active ? INK : PAPER,
              color: active ? PAPER : INK,
              borderLeft: i === 0 ? "none" : `1px solid ${INK}`,
            }}
          >
            {o.label}
          </button>
        );
      })}
    </div>
  );
}

export default function ClimateInsights() {
  const [year, setYear] = useDiveState<string>("year", "2024");
  const [metric, setMetric] = useDiveState<string>("metric", "avg");
  const [country, setCountry] = useDiveState<string>("country", "");
  const yr = N(year) || 2024;
  const mcol = (METRICS[metric] ?? METRICS.avg).col;

  const years = useSQLQuery(`
    SELECT DISTINCT year FROM "climate_demo"."main"."insight_monthly" ORDER BY year
  `);

  const kpi = useSQLQuery(`
    SELECT
      (SELECT COUNT(*) FROM "climate_demo"."main"."daily_tmax" WHERE year = ${yr})            AS obs,
      (SELECT COUNT(DISTINCT station) FROM "climate_demo"."main"."daily_tmax" WHERE year = ${yr}) AS stations,
      (SELECT MAX(tmax_c) FROM "climate_demo"."main"."insight_extremes" WHERE year = ${yr})    AS hottest,
      (SELECT MAX(pct_of_obs) FROM "climate_demo"."main"."insight_country"
         WHERE year = ${yr} AND country = 'US')                                                AS us_pct,
      (SELECT SUM(rejected) FROM "climate_demo"."main"."insight_qc")                           AS rejected
  `);

  const seasonal = useSQLQuery(`
    SELECT month,
      MAX(${mcol}) FILTER (WHERE hemisphere = 'Northern') AS north,
      MAX(${mcol}) FILTER (WHERE hemisphere = 'Southern') AS south
    FROM "climate_demo"."main"."insight_monthly"
    WHERE year = ${yr}
    GROUP BY month ORDER BY month
  `);

  const countries = useSQLQuery(`
    SELECT country_name, obs, pct_of_obs
    FROM "climate_demo"."main"."insight_country"
    WHERE year = ${yr}
    ORDER BY obs DESC LIMIT 12
  `);

  const extremes = useSQLQuery(`
    SELECT strftime(obs_date, '%b %d') AS day, name, country_name AS country,
           tmax_c, elev_m
    FROM "climate_demo"."main"."insight_extremes"
    WHERE year = ${yr} ${country ? `AND country_name = '${country.replace(/'/g, "''")}'` : ""}
    ORDER BY tmax_c DESC LIMIT 10
  `);

  const yearOpts = (Array.isArray(years.data) ? years.data : [])
    .map((r) => ({ v: String(N(r.year)), label: String(N(r.year)) }));
  const k = (Array.isArray(kpi.data) ? kpi.data : [])[0] ?? {};
  const seasonalData = (Array.isArray(seasonal.data) ? seasonal.data : []).map((r) => ({
    month: MONTHS[N(r.month)],
    Northern: N(r.north),
    Southern: N(r.south),
  }));
  const countryData = (Array.isArray(countries.data) ? countries.data : []).map((r) => ({
    country: r.country_name as string,
    obs: N(r.obs),
    pct: N(r.pct_of_obs),
  }));
  const extremeRows = Array.isArray(extremes.data) ? extremes.data : [];

  // Peak annotations for the line chart.
  const peak = (key: "Northern" | "Southern") =>
    seasonalData.reduce(
      (best, d) => (d[key] > best.v ? { m: d.month, v: d[key] } : best),
      { m: "", v: -Infinity }
    );
  const nPeak = peak("Northern");
  const sPeak = peak("Southern");

  return (
    <div className="p-8" style={{ background: PAPER, fontFamily: SERIF, color: INK }}>
      {/* Masthead */}
      <p style={{ fontSize: 11, letterSpacing: 2, textTransform: "uppercase", color: MUTED, marginBottom: 6 }}>
        Climate &middot; NOAA GHCN-Daily
      </p>
      <h1 style={{ fontSize: 30, fontWeight: 700, lineHeight: 1.15, marginBottom: 8, maxWidth: 640 }}>
        A year of the world&rsquo;s daily high temperatures
      </h1>
      <p style={{ fontSize: 15, color: MUTED, maxWidth: 640, marginBottom: 20 }}>
        Every quality-checked maximum-temperature reading from {N(k.stations).toLocaleString()} weather
        stations in {yr}, converted from tenths of a degree to whole degrees Celsius.
      </p>

      {/* Controls */}
      <div style={{ display: "flex", gap: 24, alignItems: "center", flexWrap: "wrap", marginBottom: 28, borderTop: `1px solid ${INK}`, borderBottom: `1px solid ${GRID}`, padding: "12px 0" }}>
        <div>
          <span style={{ fontSize: 11, color: MUTED, marginRight: 8, textTransform: "uppercase", letterSpacing: 1 }}>Year</span>
          <Toggle options={yearOpts.length ? yearOpts : [{ v: "2024", label: "2024" }]} value={year} onChange={setYear} />
        </div>
        <div>
          <span style={{ fontSize: 11, color: MUTED, marginRight: 8, textTransform: "uppercase", letterSpacing: 1 }}>Metric</span>
          <Toggle
            options={[
              { v: "avg", label: "Average" },
              { v: "max", label: "Hottest" },
              { v: "min", label: "Coldest high" },
            ]}
            value={metric}
            onChange={setMetric}
          />
        </div>
      </div>

      {/* KPIs */}
      <div className="grid grid-cols-4 gap-8 mb-12">
        {[
          { v: kpi.isLoading ? null : `${(N(k.obs) / 1e6).toFixed(1)}M`, l: "Readings" },
          { v: kpi.isLoading ? null : N(k.stations).toLocaleString(), l: "Stations" },
          { v: kpi.isLoading ? null : `${N(k.hottest).toFixed(1)}°`, l: "Hottest reading (C)", accent: true },
          { v: kpi.isLoading ? null : `${N(k.us_pct).toFixed(0)}%`, l: "Readings from US stations" },
        ].map((kp, i) => (
          <div key={i}>
            {kp.v == null ? (
              <div className="h-12 w-24 bg-gray-200 animate-pulse rounded" />
            ) : (
              <p style={{ fontSize: 42, fontWeight: 700, lineHeight: 1, color: kp.accent ? PALETTE[1] : INK }}>{kp.v}</p>
            )}
            <p style={{ fontSize: 13, color: MUTED, marginTop: 8 }}>{kp.l}</p>
          </div>
        ))}
      </div>

      {/* Seasonal line chart */}
      <h2 style={{ fontSize: 20, fontWeight: 700, marginBottom: 4 }}>The hemispheres are mirror images</h2>
      <p style={{ fontSize: 14, color: MUTED, marginBottom: 14, maxWidth: 640 }}>
        {(METRICS[metric] ?? METRICS.avg).label} by month. The Northern line peaks in {nPeak.m}; the
        Southern line peaks in {sPeak.m}, half a year apart.
      </p>
      {seasonal.isLoading ? (
        <div className="animate-pulse rounded mb-12" style={{ height: 260, background: "#f3f3f3" }} />
      ) : (
        <div className="mb-12">
          <ResponsiveContainer width="100%" height={260}>
            <LineChart data={seasonalData} margin={{ top: 16, right: 24, left: -8, bottom: 0 }}>
              <CartesianGrid strokeDasharray="2 4" stroke={GRID} vertical={false} />
              <XAxis dataKey="month" fontSize={12} stroke={MUTED} tick={{ fontFamily: SERIF }} />
              <YAxis tickFormatter={(v) => `${v}°`} fontSize={12} stroke={MUTED} tick={{ fontFamily: SERIF }} />
              <Tooltip
                formatter={(v: number) => [`${v}°C`]}
                contentStyle={{ fontFamily: SERIF, border: `1px solid ${INK}`, borderRadius: 0 }}
              />
              <Legend wrapperStyle={{ fontFamily: SERIF, fontSize: 13 }} />
              <Line type="linear" dataKey="Northern" stroke={PALETTE[0]} strokeWidth={2} dot={false} />
              <Line type="linear" dataKey="Southern" stroke={PALETTE[1]} strokeWidth={2} dot={false} />
              {isFinite(nPeak.v) && (
                <ReferenceDot x={nPeak.m} y={nPeak.v} r={3} fill={PALETTE[0]} stroke={PAPER}
                  label={{ value: `${nPeak.v}°`, position: "top", fontFamily: SERIF, fontSize: 12, fill: PALETTE[0] }} />
              )}
              {isFinite(sPeak.v) && (
                <ReferenceDot x={sPeak.m} y={sPeak.v} r={3} fill={PALETTE[1]} stroke={PAPER}
                  label={{ value: `${sPeak.v}°`, position: "top", fontFamily: SERIF, fontSize: 12, fill: PALETTE[1] }} />
              )}
            </LineChart>
          </ResponsiveContainer>
        </div>
      )}

      {/* Country horizontal bar — click to cross-filter */}
      <h2 style={{ fontSize: 20, fontWeight: 700, marginBottom: 4 }}>It is mostly a United States dataset</h2>
      <p style={{ fontSize: 14, color: MUTED, marginBottom: 14, maxWidth: 640 }}>
        Readings by country, top 12. The US alone supplies {N(k.us_pct).toFixed(0)}% of them.
        Click a bar to filter the table below; click it again to clear.
      </p>
      {countries.isLoading ? (
        <div className="animate-pulse rounded mb-12" style={{ height: 300, background: "#f3f3f3" }} />
      ) : (
        <div className="mb-12">
          <ResponsiveContainer width="100%" height={300}>
            <BarChart data={countryData} layout="vertical" margin={{ top: 0, right: 28, left: 70, bottom: 0 }}>
              <CartesianGrid strokeDasharray="2 4" stroke={GRID} horizontal={false} />
              <XAxis type="number" tickFormatter={(v) => (v >= 1e6 ? `${(v / 1e6).toFixed(1)}M` : `${Math.round(v / 1e3)}k`)} fontSize={12} stroke={MUTED} tick={{ fontFamily: SERIF }} />
              <YAxis type="category" dataKey="country" width={100} fontSize={12} stroke={MUTED} tick={{ fontFamily: SERIF }} />
              <Tooltip
                cursor={{ fill: "#f5f5f5" }}
                formatter={(v: number) => [`${v.toLocaleString()} readings`]}
                contentStyle={{ fontFamily: SERIF, border: `1px solid ${INK}`, borderRadius: 0 }}
              />
              <Bar dataKey="obs" onClick={(d: any) => setCountry(d && d.country === country ? "" : (d ? d.country : ""))} cursor="pointer">
                {countryData.map((d, i) => {
                  const isUS = d.country === "United States";
                  const base = isUS ? PALETTE[1] : PALETTE[0];
                  const dim = country && d.country !== country ? 0.3 : 1;
                  return <Cell key={i} fill={base} fillOpacity={dim} />;
                })}
              </Bar>
            </BarChart>
          </ResponsiveContainer>
        </div>
      )}

      {/* Extremes table */}
      <div style={{ display: "flex", alignItems: "baseline", gap: 12, marginBottom: 4, flexWrap: "wrap" }}>
        <h2 style={{ fontSize: 20, fontWeight: 700 }}>Hottest readings of {yr}</h2>
        {country && (
          <button onClick={() => setCountry("")} style={{ fontFamily: SERIF, fontSize: 12, color: PALETTE[1], cursor: "pointer", background: "none", border: "none", textDecoration: "underline" }}>
            filtered to {country} &times; clear
          </button>
        )}
      </div>
      <p style={{ fontSize: 14, color: MUTED, marginBottom: 14, maxWidth: 640 }}>
        {country ? `The ten hottest readings recorded in ${country}.` : "Death Valley dominates, much of it recorded below sea level."}
      </p>
      {extremes.isLoading ? (
        <div className="animate-pulse space-y-2">
          <div className="h-4 bg-gray-200 rounded w-3/4" />
          <div className="h-4 bg-gray-200 rounded w-2/3" />
          <div className="h-4 bg-gray-200 rounded w-1/2" />
        </div>
      ) : (
        <table style={{ width: "100%", fontFamily: SERIF, fontSize: 14, borderCollapse: "collapse" }}>
          <thead>
            <tr style={{ color: MUTED, textAlign: "left", borderBottom: `1.5px solid ${INK}` }}>
              <th style={{ padding: "6px 8px 6px 0", fontWeight: 700 }}>Date</th>
              <th style={{ padding: "6px 8px", fontWeight: 700 }}>Station</th>
              {!country && <th style={{ padding: "6px 8px", fontWeight: 700 }}>Country</th>}
              <th style={{ padding: "6px 8px", fontWeight: 700, textAlign: "right" }}>Elev (m)</th>
              <th style={{ padding: "6px 0 6px 8px", fontWeight: 700, textAlign: "right" }}>TMAX (&deg;C)</th>
            </tr>
          </thead>
          <tbody>
            {extremeRows.map((r, i) => (
              <tr key={i} style={{ borderBottom: `1px solid ${GRID}` }}>
                <td style={{ padding: "6px 8px 6px 0", color: MUTED }}>{r.day as string}</td>
                <td style={{ padding: "6px 8px" }}>{r.name as string}</td>
                {!country && <td style={{ padding: "6px 8px", color: MUTED }}>{r.country as string}</td>}
                <td style={{ padding: "6px 8px", textAlign: "right", color: MUTED }}>{N(r.elev_m).toFixed(0)}</td>
                <td style={{ padding: "6px 0 6px 8px", textAlign: "right", fontWeight: 700, color: PALETTE[1] }}>{N(r.tmax_c).toFixed(1)}</td>
              </tr>
            ))}
          </tbody>
        </table>
      )}

      <p style={{ fontSize: 12, color: MUTED, marginTop: 28, borderTop: `1px solid ${GRID}`, paddingTop: 12, maxWidth: 640 }}>
        Source: NOAA Global Historical Climatology Network &ndash; Daily.
        {kpi.isLoading ? "" : ` ${N(k.rejected).toLocaleString()} readings that failed NOAA quality checks were excluded; the raw feed still contains impossible sentinel values such as -99.9°C and +807°C.`}
      </p>
    </div>
  );
}


Ink & Paper / NYT-style climate dive over NOAA GHCN-Daily TMAX. Interactive: year toggle, metric toggle (average / hottest / coldest high), and click-to-cross-filter on the country bar (filters the hottest-readings table). Georgia serif, #fff paper, ink #121212, palette #326fa8/#e15759/#59a14f/#edc949/#af7aa1. Annotated hemisphere line chart, horizontal country bar, extremes table. Reads the insight_* marts from the climate-insights flight.

A Year of the World's Daily High Temperatures (NOAA GHCN)

Comments