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


export const REQUIRED_DATABASES = [
  { type: 'share', path: 'md:_share/snotel_wa/f41b9aba-72e7-4904-a652-178b3f581608', alias: 'snotel_wa' }
];
const N = (v) => (v != null ? Number(v) : 0);

const DOY_LABELS = { 0:"Oct 1", 31:"Nov 1", 61:"Dec 1", 92:"Jan 1", 122:"Feb 1", 153:"Mar 1", 182:"Apr 1", 213:"May 1", 243:"Jun 1", 274:"Jul 1", 304:"Aug 1", 335:"Sep 1" };

const WY_COLORS = {
  2026: "#0777b3",
  2025: "#6a6a6a",
  2024: "#adadad",
  2023: "#c8c8c8",
  2022: "#e0e0e0",
};

const CustomTooltip = ({ active, payload, label }) => {
  if (!active || !payload?.length) return null;
  const month = Object.entries(DOY_LABELS).reverse().find(([d]) => label >= Number(d));
  const dayInMonth = label - Number(month[0]) + 1;
  return (
    <div style={{background:"#231f20",padding:"8px 12px",borderRadius:4,fontSize:11,color:"#fff",lineHeight:1.6}}>
      <div style={{fontWeight:600,marginBottom:2}}>{month[1].replace(" 1","")}{" "}{dayInMonth}</div>
      {payload.filter(p=>p.value>0).map((p,i)=>(
        <div key={i} style={{display:"flex",gap:12,justifyContent:"space-between"}}>
          <span style={{color:p.color}}>{p.dataKey==="median"?"Median":p.dataKey.replace("wy","WY")}</span>
          <span style={{fontWeight:600}}>{N(p.value).toFixed(1)}"</span>
        </div>
      ))}
    </div>
  );
};

export default function SNOTELDashboard() {
  const [view, setView] = useState("swe");

  const sweQuery = useSQLQuery(`
    WITH wy AS (
      SELECT *,
        CASE WHEN month(Date) >= 10 THEN year(Date)+1 ELSE year(Date) END as water_year,
        (Date - CASE WHEN month(Date) >= 10 THEN make_date(year(Date),10,1) ELSE make_date(year(Date)-1,10,1) END)::int as day_of_wy,
        "Snow Water Equivalent (in) Start of Day Values" as swe
      FROM "snotel_wa"."main"."snotel_clean"
    ),
    median_baseline AS (
      SELECT day_of_wy, median(swe) as median_swe
      FROM wy WHERE water_year BETWEEN 1991 AND 2020
      GROUP BY day_of_wy
    ),
    recent AS (
      SELECT water_year, day_of_wy, swe
      FROM wy
      WHERE water_year BETWEEN 2022 AND 2026
        AND day_of_wy % 3 = 0
    )
    SELECT r.water_year, r.day_of_wy, r.swe, m.median_swe
    FROM recent r
    LEFT JOIN median_baseline m ON r.day_of_wy = m.day_of_wy
    ORDER BY r.water_year, r.day_of_wy
  `);

  const summaryQuery = useSQLQuery(`
    WITH wy AS (
      SELECT *,
        CASE WHEN month(Date) >= 10 THEN year(Date)+1 ELSE year(Date) END as water_year,
        (Date - CASE WHEN month(Date) >= 10 THEN make_date(year(Date),10,1) ELSE make_date(year(Date)-1,10,1) END)::int as day_of_wy,
        "Snow Water Equivalent (in) Start of Day Values" as swe,
        "Precipitation Accumulation (in) Start of Day Values" as precip_accum
      FROM "snotel_wa"."main"."snotel_clean"
    ),
    median_baseline AS (
      SELECT day_of_wy, median(swe) as median_swe
      FROM wy WHERE water_year BETWEEN 1991 AND 2020
      GROUP BY day_of_wy
    ),
    latest AS (
      SELECT * FROM wy WHERE water_year = (SELECT max(water_year) FROM wy) ORDER BY Date DESC LIMIT 1
    ),
    peak_years AS (
      SELECT water_year, max(swe) as peak_swe
      FROM wy WHERE water_year BETWEEN 2022 AND 2025
      GROUP BY water_year
    )
    SELECT
      strftime(l.Date, '%Y-%m-%d') as latest_date,
      l.swe as current_swe,
      l.day_of_wy as current_day_of_wy,
      m.median_swe as median_swe_today,
      CASE WHEN m.median_swe > 0 THEN round(l.swe / m.median_swe * 100, 1) ELSE 0 END as pct_of_median,
      l.precip_accum as precip_ytd,
      (SELECT avg(peak_swe) FROM peak_years) as avg_recent_peak
    FROM latest l
    LEFT JOIN median_baseline m ON l.day_of_wy = m.day_of_wy
  `);

  const recentQuery = useSQLQuery(`
    SELECT
      strftime(Date, '%b %d') as l,
      "Air Temperature Maximum (degF)" as hi,
      "Air Temperature Minimum (degF)" as lo,
      "Snow Water Equivalent (in) Start of Day Values" as swe,
      "Precipitation Increment (in)" as p
    FROM "snotel_wa"."main"."snotel_clean"
    WHERE Date BETWEEN (SELECT max(Date) - INTERVAL 30 DAY FROM "snotel_wa"."main"."snotel_clean") AND (SELECT max(Date) FROM "snotel_wa"."main"."snotel_clean")
    ORDER BY Date
  `);

  const peakQuery = useSQLQuery(`
    WITH wy AS (
      SELECT
        CASE WHEN month(Date) >= 10 THEN year(Date)+1 ELSE year(Date) END as water_year,
        "Snow Water Equivalent (in) Start of Day Values" as swe
      FROM "snotel_wa"."main"."snotel_clean"
    )
    SELECT water_year as wy, max(swe) as peak
    FROM wy
    WHERE water_year BETWEEN 2022 AND (SELECT max(CASE WHEN month(Date)>=10 THEN year(Date)+1 ELSE year(Date) END) FROM "snotel_wa"."main"."snotel_clean")
    GROUP BY water_year
    ORDER BY water_year
  `);

  const sweRows = Array.isArray(sweQuery.data) ? sweQuery.data : [];
  const summaryRows = Array.isArray(summaryQuery.data) ? summaryQuery.data : [];
  const recentRows = Array.isArray(recentQuery.data) ? recentQuery.data : [];
  const peakRows = Array.isArray(peakQuery.data) ? peakQuery.data : [];

  const summary = summaryRows[0] || {};

  const chartData = (() => {
    const byDay = {};
    sweRows.forEach(r => {
      const doy = N(r.day_of_wy);
      if (!byDay[doy]) byDay[doy] = { doy, median: N(r.median_swe) };
      byDay[doy][`wy${N(r.water_year)}`] = N(r.swe);
    });
    return Object.values(byDay).sort((a,b) => a.doy - b.doy);
  })();

  const Skeleton = ({h=48,w="6rem"}) => <div className="animate-pulse rounded" style={{height:h,width:w,background:"#e0e0e0"}} />;

  return (
    <div style={{background:"#f8f8f8",minHeight:"100%",fontFamily:"-apple-system,BlinkMacSystemFont,'Segoe UI',sans-serif"}}>
      <div style={{padding:"20px 24px 8px"}}>
        <h1 className="text-2xl font-semibold" style={{color:"#231f20",margin:0}}>SNOTEL WA — Snowpack</h1>
        <p className="text-sm" style={{color:"#6a6a6a",margin:"4px 0 0"}}>
          {summaryQuery.isLoading ? "Loading..." : `Water Year ${new Date().getMonth()>=9?new Date().getFullYear()+1:new Date().getFullYear()} · Data through ${summary.latest_date || "..."} · 1991–2020 baseline`}
        </p>
      </div>

      <div className="grid grid-cols-4 gap-6" style={{padding:"16px 24px"}}>
        <div>
          {summaryQuery.isLoading ? <Skeleton /> : <p className="text-4xl font-bold" style={{color:"#bc1200",margin:0}}>{N(summary.current_swe).toFixed(1)}"</p>}
          <p className="text-sm" style={{color:"#6a6a6a",margin:"4px 0 0"}}>Current SWE</p>
        </div>
        <div>
          {summaryQuery.isLoading ? <Skeleton /> : <p className="text-4xl font-bold" style={{color:"#bc1200",margin:0}}>{N(summary.pct_of_median).toFixed(0)}%</p>}
          <p className="text-sm" style={{color:"#6a6a6a",margin:"4px 0 0"}}>of Median ({N(summary.median_swe_today)}")</p>
        </div>
        <div>
          {summaryQuery.isLoading ? <Skeleton /> : <p className="text-4xl font-bold" style={{color:"#231f20",margin:0}}>{N(summary.precip_ytd)}"</p>}
          <p className="text-sm" style={{color:"#6a6a6a",margin:"4px 0 0"}}>Precip YTD</p>
        </div>
        <div>
          {summaryQuery.isLoading ? <Skeleton /> : <p className="text-4xl font-bold" style={{color:"#231f20",margin:0}}>{N(summary.avg_recent_peak).toFixed(0)}"</p>}
          <p className="text-sm" style={{color:"#6a6a6a",margin:"4px 0 0"}}>Avg Peak (4yr)</p>
        </div>
      </div>

      <div style={{display:"flex",gap:4,padding:"0 24px",borderBottom:"1px solid #e0e0e0"}}>
        {[["swe","SWE by Water Year"],["recent","30-Day Detail"],["peaks","Peak SWE History"]].map(([key,label])=>(
          <button key={key} onClick={()=>setView(key)} style={{
            padding:"8px 16px",fontSize:13,fontWeight:view===key?600:400,
            color:view===key?"#231f20":"#6a6a6a",background:"transparent",border:"none",cursor:"pointer",
            borderBottom:view===key?"2px solid #0777b3":"2px solid transparent",marginBottom:-1
          }}>{label}</button>
        ))}
      </div>

      <div style={{padding:"16px 24px 24px"}}>
        {view === "swe" && (
          <>
            <div style={{display:"flex",gap:16,alignItems:"center",marginBottom:12,flexWrap:"wrap"}}>
              {Object.entries(WY_COLORS).map(([wy,color])=>(
                <div key={wy} style={{display:"flex",alignItems:"center",gap:4,fontSize:11,color:"#6a6a6a"}}>
                  <div style={{width:16,height:2,background:color,borderRadius:1}} />
                  WY{wy}
                </div>
              ))}
              <div style={{display:"flex",alignItems:"center",gap:4,fontSize:11,color:"#6a6a6a"}}>
                <div style={{width:16,height:0,borderTop:"2px dashed #0777b3",opacity:0.4}} />
                Median
              </div>
            </div>
            {sweQuery.isLoading ? <div className="animate-pulse rounded" style={{height:280,background:"#e8e8e8"}} /> : (
              <ResponsiveContainer width="100%" height={280}>
                <LineChart data={chartData} margin={{top:5,right:10,bottom:5,left:0}}>
                  <CartesianGrid strokeDasharray="3 3" stroke="#eee" />
                  <XAxis dataKey="doy" fontSize={10} tickFormatter={v=>DOY_LABELS[v]||""} ticks={Object.keys(DOY_LABELS).map(Number).filter(d=>d<=300)} stroke="#ccc" />
                  <YAxis fontSize={10} tickFormatter={v=>`${v}"`} stroke="#ccc" />
                  <Tooltip content={<CustomTooltip/>} />
                  <Line type="linear" dataKey="median" stroke="#0777b3" strokeWidth={1.5} strokeDasharray="4 3" opacity={0.4} dot={false} />
                  <Line type="linear" dataKey="wy2022" stroke={WY_COLORS[2022]} strokeWidth={1} dot={false} />
                  <Line type="linear" dataKey="wy2023" stroke={WY_COLORS[2023]} strokeWidth={1} dot={false} />
                  <Line type="linear" dataKey="wy2024" stroke={WY_COLORS[2024]} strokeWidth={1} dot={false} />
                  <Line type="linear" dataKey="wy2025" stroke={WY_COLORS[2025]} strokeWidth={1.5} dot={false} />
                  <Line type="linear" dataKey="wy2026" stroke={WY_COLORS[2026]} strokeWidth={2.5} dot={false} />
                </LineChart>
              </ResponsiveContainer>
            )}
          </>
        )}

        {view === "recent" && (
          recentQuery.isLoading ? <div className="animate-pulse rounded" style={{height:280,background:"#e8e8e8"}} /> : (
            <ResponsiveContainer width="100%" height={280}>
              <ComposedChart data={recentRows.map(r=>({l:r.l,swe:N(r.swe),p:N(r.p)}))} margin={{top:5,right:10,bottom:5,left:0}}>
                <CartesianGrid strokeDasharray="3 3" stroke="#eee" />
                <XAxis dataKey="l" fontSize={9} interval={4} stroke="#ccc" />
                <YAxis yAxisId="swe" fontSize={10} tickFormatter={v=>`${v}"`} stroke="#ccc" />
                <YAxis yAxisId="precip" orientation="right" fontSize={10} tickFormatter={v=>`${v}"`} stroke="#ccc" />
                <Tooltip contentStyle={{background:"#231f20",border:"none",borderRadius:4,fontSize:11,color:"#fff"}} />
                <Bar yAxisId="precip" dataKey="p" fill="#0777b3" opacity={0.3} name="Daily Precip" />
                <Line yAxisId="swe" type="linear" dataKey="swe" stroke="#0777b3" strokeWidth={2} dot={false} name="SWE" />
              </ComposedChart>
            </ResponsiveContainer>
          )
        )}

        {view === "peaks" && (
          peakQuery.isLoading ? <div className="animate-pulse rounded" style={{height:280,background:"#e8e8e8"}} /> : (
            <ResponsiveContainer width="100%" height={280}>
              <BarChart data={peakRows.map(r=>({wy:N(r.wy),peak:N(r.peak)}))} margin={{top:5,right:10,bottom:5,left:0}}>
                <CartesianGrid strokeDasharray="3 3" stroke="#eee" />
                <XAxis dataKey="wy" fontSize={11} tickFormatter={v=>`WY${v}`} stroke="#ccc" />
                <YAxis fontSize={10} tickFormatter={v=>`${v}"`} stroke="#ccc" />
                <Tooltip contentStyle={{background:"#231f20",border:"none",borderRadius:4,fontSize:11,color:"#fff"}} formatter={(v)=>[`${N(v).toFixed(1)}"`, "Peak SWE"]} />
                <Bar dataKey="peak" fill="#0777b3" radius={[2,2,0,0]} />
              </BarChart>
            </ResponsiveContainer>
          )
        )}
      </div>
    </div>
  );
}

Washington state SNOTEL snowpack monitoring with multi-year SWE comparison (WY2022–2026), 30-year median baseline, 30-day detail view, and peak SWE history. Live queries against snotel_wa database.

SNOTEL WA Snowpack WY2026

AI Prompts Used

Comments