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

const N = (v) => (v != null ? Number(v) : 0);
const WHO = 5;
const CSV = `read_csv_auto('https://us.data.motherduck.com/who_ambient_air_quality/csv/who_ambient_air_quality_database_version_2024.csv')`;

const CITIES_LIST = [
  "Addis Ababa/ETH","Aix En Provence/FRA","Ankara/TUR","Annecy/FRA","Antwerpen/BEL",
  "Athina/GRC","Bangkok/THA","Barcelona/ESP","Basel/CHE","Beijing/CHN",
  "Belgrade/SRB","Bergamo/ITA","Berlin/DEU","Bern/CHE","Bogota/COL",
  "Bologna/ITA","Bordeaux/FRA","Boston Cambridge Newton MA NH/USA","Bratislava/SVK",
  "Brescia/ITA","Brno/CZE","Brussels/BEL","Budapest/HUN",
  "Chicago Naperville Elgin IL IN WI/USA","Clermont Ferrand/FRA","Colombo/LKA",
  "Delhi/IND","Denver Aurora Lakewood CO/USA","Dhaka/BGD","Dijon/FRA","Dubai/ARE",
  "Dublin/IRL","Gent/BEL","Grenoble/FRA","Hamburg/DEU","Hanoi/VNM",
  "Helsinki Helsingfors/FIN","Istanbul/TUR","Jakarta/IDN","Johannesburg/ZAF",
  "Kathmandu/NPL","Krakow/POL","Lausanne/CHE","Liege/BEL","Lille/FRA","Lima/PER",
  "Ljubljana/SVN","London/GBR","Los Angeles Long Beach Anaheim CA/USA","Lyon/FRA",
  "Madrid/ESP","Marseille/FRA","Montpellier/FRA","Montreal/CAN","Mumbai/IND",
  "Nairobi/KEN","Namur/BEL","Nantes/FRA","Nice/FRA","Oslo/NOR","Paris/FRA",
  "Rennes/FRA","Riga/LVA","San Francisco Oakland Hayward CA/USA","Santiago/CHL",
  "Sao Paulo/BRA","Seattle Tacoma Bellevue WA/USA","Seoul/KOR","Shanghai/CHN",
  "Singapore/SGP","Stockholm/SWE","Strasbourg/FRA","Sydney/AUS","Tallinn/EST",
  "Tokyo/JPN","Toronto/CAN","Toulouse/FRA","Vancouver/CAN","Vilnius/LTU",
  "Zagreb/HRV","Zurich/CHE"
];

const RL = {"1_Afr":"Africa","2_Amr":"Americas","3_Sear":"South-East Asia","4_Eur":"Europe","5_Emr":"Eastern Mediterranean","6_Wpr":"Western Pacific"};

function tier(v) {
  if (v <= 5) return { l: "Good", c: "#2d7a00" };
  if (v <= 15) return { l: "Moderate", c: "#e18727" };
  if (v <= 35) return { l: "High", c: "#bd4e35" };
  return { l: "Hazardous", c: "#bc1200" };
}

const CT = ({ active, payload }) => {
  if (!active || !payload?.[0]) return null;
  const d = payload[0].payload;
  return (
    <div style={{ background: "#231f20", color: "#fff", padding: "6px 10px", borderRadius: 4, fontSize: 11, fontFamily: "monospace" }}>
      <div style={{ fontWeight: 600 }}>{d.city || d.year}</div>
      <div>PM2.5: {N(d.pm25).toFixed(1)} μg/m³</div>
    </div>
  );
};

export default function WHOAirQuality() {
  const [sel, setSel] = useState("Paris/FRA");
  const [yr, setYr] = useState(null);

  const safeSel = sel.replace(/'/g, "''");

  const trend = useSQLQuery(`
    SELECT TRY_CAST(year AS INT) as year,
           ROUND(TRY_CAST(pm25_concentration AS DOUBLE), 1) as pm25,
           who_region, country_name
    FROM ${CSV}
    WHERE city = '${safeSel}'
      AND TRY_CAST(pm25_concentration AS DOUBLE) IS NOT NULL
      AND TRY_CAST(year AS INT) IS NOT NULL
    ORDER BY TRY_CAST(year AS INT)
  `);

  const trendRows = Array.isArray(trend.data) ? trend.data : [];
  const years = trendRows.map(r => N(r.year));
  const maxYear = years.length > 0 ? Math.max(...years) : null;
  const effectiveYr = yr !== null && years.includes(yr) ? yr : maxYear;
  const region = trendRows[0]?.who_region || "";
  const country = trendRows[0]?.country_name || "";

  const curRow = trendRows.find(r => N(r.year) === effectiveYr);
  const pm = curRow ? N(curRow.pm25) : 0;
  const t = tier(pm);

  const peers = useSQLQuery(`
    SELECT city, ROUND(TRY_CAST(pm25_concentration AS DOUBLE), 1) as pm25
    FROM ${CSV}
    WHERE who_region = '${region}'
      AND TRY_CAST(year AS INT) = ${effectiveYr}
      AND TRY_CAST(pm25_concentration AS DOUBLE) IS NOT NULL
    ORDER BY TRY_CAST(pm25_concentration AS DOUBLE) ASC
    LIMIT 150
  `, { enabled: !!region && effectiveYr !== null });

  const peerRows = Array.isArray(peers.data) ? peers.data : [];
  const rankIdx = peerRows.findIndex(r => r.city === sel);
  const rank = rankIdx >= 0 ? rankIdx + 1 : null;

  const barData = useMemo(() => {
    if (!peerRows.length) return [];
    const idx = peerRows.findIndex(r => r.city === sel);
    const center = idx >= 0 ? idx : 0;
    const s = Math.max(0, center - 8);
    return peerRows.slice(s, s + 18).map(r => ({ city: String(r.city), pm25: N(r.pm25) }));
  }, [peerRows, sel]);

  const trendData = trendRows.map(r => ({ year: N(r.year), pm25: N(r.pm25) }));
  const cityName = sel.split("/")[0];

  return (
    <div className="p-4" style={{ background: "#f8f8f8", color: "#231f20" }}>
      <h1 className="text-2xl font-semibold">WHO Air Quality Index</h1>
      <p className="text-sm mb-4" style={{ color: "#6a6a6a" }}>PM2.5 Annual Mean · WHO Database v2024</p>

      <div className="flex items-end gap-4 mb-5 flex-wrap">
        <div>
          <p className="text-xs mb-1 font-medium" style={{ color: "#6a6a6a", textTransform: "uppercase", letterSpacing: "1px" }}>City</p>
          <select value={sel} onChange={e => { setSel(e.target.value); setYr(null); }}
            className="text-sm p-2 rounded"
            style={{ border: "1px solid #ddd", background: "#fff", minWidth: 220 }}>
            {CITIES_LIST.map(c => <option key={c} value={c}>{c}</option>)}
          </select>
        </div>
        <div>
          <p className="text-xs mb-1 font-medium" style={{ color: "#6a6a6a", textTransform: "uppercase", letterSpacing: "1px" }}>Year</p>
          <div className="flex gap-1 flex-wrap">
            {trend.isLoading ? (
              <div className="h-7 w-32 bg-gray-200 animate-pulse rounded" />
            ) : years.map(y => (
              <button key={y} onClick={() => setYr(y)}
                className="text-xs px-2 py-1 rounded"
                style={{
                  background: y === effectiveYr ? "#231f20" : "#fff",
                  color: y === effectiveYr ? "#fff" : "#231f20",
                  border: "1px solid #ddd", fontFamily: "monospace", cursor: "pointer"
                }}>
                {y}
              </button>
            ))}
          </div>
        </div>
      </div>

      <div className="grid grid-cols-3 gap-8 mb-5">
        <div>
          {trend.isLoading ? <div className="h-12 w-24 bg-gray-200 animate-pulse rounded" /> : (
            <p className="text-5xl font-bold" style={{ color: t.c, fontFamily: "monospace" }}>{pm.toFixed(1)}</p>
          )}
          <p className="text-sm mt-1" style={{ color: "#6a6a6a" }}>PM2.5 μg/m³ · {trend.isLoading ? "…" : t.l}</p>
        </div>
        <div>
          {trend.isLoading ? <div className="h-12 w-24 bg-gray-200 animate-pulse rounded" /> : (
            <p className="text-5xl font-bold" style={{ color: pm <= 5 ? "#2d7a00" : "#bc1200", fontFamily: "monospace" }}>
              {pm <= 5 ? "✓" : `${(pm / WHO).toFixed(1)}×`}
            </p>
          )}
          <p className="text-sm mt-1" style={{ color: "#6a6a6a" }}>vs WHO guideline (5 μg/m³)</p>
        </div>
        <div>
          {peers.isLoading ? <div className="h-12 w-24 bg-gray-200 animate-pulse rounded" /> : (
            <p className="text-5xl font-bold" style={{ color: "#231f20", fontFamily: "monospace" }}>
              {rank ? `#${rank}` : "—"}
            </p>
          )}
          <p className="text-sm mt-1" style={{ color: "#6a6a6a" }}>
            {rank ? `of ${peerRows.length} in ${RL[region] || "region"}` : RL[region] || ""}
          </p>
        </div>
      </div>

      <p className="text-xs font-medium mb-2" style={{ color: "#6a6a6a", textTransform: "uppercase", letterSpacing: "1px" }}>
        Regional Peers · {RL[region] || ""} · {effectiveYr || "—"}
      </p>
      {peers.isLoading ? (
        <div className="bg-gray-100 animate-pulse rounded" style={{ height: 200 }} />
      ) : barData.length > 0 ? (
        <ResponsiveContainer width="100%" height={200}>
          <BarChart data={barData} margin={{ top: 4, right: 8, bottom: 44, left: 4 }}>
            <CartesianGrid strokeDasharray="3 3" stroke="#eee" vertical={false} />
            <XAxis dataKey="city" tick={{ fontSize: 8, fill: "#6a6a6a" }} angle={-45} textAnchor="end" height={60} interval={0}
              tickFormatter={v => v.length > 12 ? v.slice(0, 10) + "…" : v} />
            <YAxis tick={{ fontSize: 10, fill: "#6a6a6a" }} />
            <Tooltip content={<CT />} />
            <ReferenceLine y={WHO} stroke="#2d7a00" strokeDasharray="4 3" strokeWidth={1.5} />
            <Bar dataKey="pm25" radius={[2, 2, 0, 0]} maxBarSize={18}>
              {barData.map((d, i) => (
                <Cell key={i} fill={d.city === sel ? "#0777b3" : "#adadad"} opacity={d.city === sel ? 1 : 0.45} />
              ))}
            </Bar>
          </BarChart>
        </ResponsiveContainer>
      ) : (
        <div style={{ height: 200, display: "flex", alignItems: "center", justifyContent: "center", color: "#6a6a6a", fontSize: 13 }}>
          No peer data available
        </div>
      )}

      <p className="text-xs font-medium mt-4 mb-2" style={{ color: "#6a6a6a", textTransform: "uppercase", letterSpacing: "1px" }}>
        PM2.5 Trend · {cityName}, {country}
      </p>
      {trend.isLoading ? (
        <div className="bg-gray-100 animate-pulse rounded" style={{ height: 200 }} />
      ) : trendData.length > 1 ? (
        <ResponsiveContainer width="100%" height={200}>
          <LineChart data={trendData} margin={{ top: 8, right: 24, bottom: 4, left: 4 }}>
            <CartesianGrid strokeDasharray="3 3" stroke="#eee" vertical={false} />
            <XAxis dataKey="year" tick={{ fontSize: 11, fill: "#6a6a6a" }} />
            <YAxis tick={{ fontSize: 11, fill: "#6a6a6a" }} domain={[0, "auto"]} />
            <Tooltip content={<CT />} />
            <ReferenceLine y={WHO} stroke="#2d7a00" strokeDasharray="4 3" strokeWidth={1.5} />
            <Line type="linear" dataKey="pm25" stroke="#0777b3" strokeWidth={2} dot={{ r: 3, fill: "#0777b3" }} />
          </LineChart>
        </ResponsiveContainer>
      ) : (
        <div style={{ height: 200, display: "flex", alignItems: "center", justifyContent: "center", color: "#6a6a6a", fontSize: 13 }}>
          {trendData.length === 1 ? "Single data point — trend requires multiple years" : "No data available"}
        </div>
      )}

      <p className="text-xs mt-3 text-center" style={{ color: "#6a6a6a" }}>
        WHO Ambient Air Quality Database v2024 · Queried live via DuckDB on MotherDuck
      </p>
    </div>
  );
}


Interactive PM2.5 explorer — compare cities, track trends, and benchmark against WHO guidelines. Data from WHO Ambient Air Quality Database v2024, queried live via DuckDB.

WHO Air Quality Index

Comments