
import { useState } from "react";
import { useSQLQuery } from "@motherduck/react-sql-query";
import {
  AreaChart, Area, XAxis, YAxis, Tooltip, ResponsiveContainer, CartesianGrid
} from "recharts";

export const REQUIRED_DATABASES = [
  { type: 'share', path: 'md:_share/duckdb_stats/1eb684bf-faff-4860-8e7d-92af4ff9a410', alias: 'duckdb_stats' }
];


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

const C = {
  bg:        "#0f1117",
  surface:   "#1a1d27",
  border:    "#2a2d3a",
  text:      "#e8e8f0",
  muted:     "#6b6e82",
  primary:   "#0777b3",
  primaryDim:"#0a3d5c",
  amber:     "#f59e0b",
  green:     "#22c55e",
  purple:    "#a78bfa",
  positive:  "#22c55e",
  negative:  "#ef4444",
};

const fmt = (n) => {
  const v = N(n);
  if (v >= 1e9) return `${(v/1e9).toFixed(1)}B`;
  if (v >= 1e6) return `${(v/1e6).toFixed(1)}M`;
  if (v >= 1e3) return `${(v/1e3).toFixed(0)}K`;
  return v.toLocaleString();
};

const Tip = ({ active, payload, label }) => {
  if (!active || !payload?.length) return null;
  return (
    <div style={{background:C.surface,border:`1px solid ${C.border}`,padding:"8px 12px",borderRadius:6,fontSize:12}}>
      <div style={{color:C.muted,fontSize:10,marginBottom:3}}>{label}</div>
      <div style={{fontWeight:700,color:C.text,fontSize:14}}>{fmt(payload[0].value)}</div>
    </div>
  );
};

const HBar = ({ data, barColor }) => {
  const max = Math.max(...data.map(r => N(r.d)));
  return (
    <div style={{display:"flex",flexDirection:"column",gap:8}}>
      {data.map((row, i) => (
        <div key={row.k} style={{display:"flex",alignItems:"center",gap:8}}>
          <span style={{fontSize:11,color:i===0?C.text:C.muted,width:40,textAlign:"right",fontVariantNumeric:"tabular-nums",flexShrink:0}}>{row.k}</span>
          <div style={{flex:1,background:C.border,borderRadius:2,height:5,overflow:"hidden"}}>
            <div style={{background:i===0?barColor:C.primaryDim,width:`${(N(row.d)/max)*100}%`,height:"100%",borderRadius:2,opacity:i===0?1:0.7}}/>
          </div>
          <span style={{fontSize:10,color:C.muted,width:36,fontVariantNumeric:"tabular-nums",flexShrink:0}}>{fmt(row.d)}</span>
        </div>
      ))}
    </div>
  );
};

const Skeleton = ({ h = 16, w = "100%" }) => (
  <div style={{height:h,width:w,background:C.border,borderRadius:4,animation:"pulse 1.5s ease-in-out infinite"}}/>
);

const WEEK_ZOOMS = [
  {key:"all",  label:"All",   from:"2019-01-01"},
  {key:"2022", label:"2022+", from:"2022-01-01"},
  {key:"2024", label:"2024+", from:"2024-01-01"},
  {key:"6m",   label:"6M",    from:null},
];
const PERIODS = [
  {key:"all", label:"All time"},
  {key:"7",   label:"Last 7d"},
  {key:"30",  label:"Last 30d"},
  {key:"90",  label:"Last 90d"},
];

export default function PyPIDuckDBStats() {
  const [period, setPeriod] = useState("all");
  const [weekZoom, setWeekZoom] = useState("all");

  // Exclude current incomplete month — MoM/YoY should compare complete months only
  const monthlyQ = useSQLQuery(`
    SELECT strftime(date_trunc('month', download_date),'%Y-%m') as month,
           SUM(daily_download_sum) as downloads
    FROM "duckdb_stats"."main"."pypi_daily_stats"
    WHERE date_trunc('month', download_date) < date_trunc('month', current_date)
      AND download_date >= (current_date - INTERVAL 14 MONTHS)
    GROUP BY 1 ORDER BY 1
  `);
  const monthlyRows = Array.isArray(monthlyQ.data) ? monthlyQ.data : [];
  const tableMonths = [...monthlyRows].reverse().slice(0, 6);
  const latestM  = monthlyRows[monthlyRows.length - 1];
  const prevM    = monthlyRows[monthlyRows.length - 2];
  const yoyM     = monthlyRows[0];
  const momPct   = latestM && prevM ? (N(latestM.downloads) - N(prevM.downloads)) / N(prevM.downloads) * 100 : null;
  const yoyPct   = latestM && yoyM  ? (N(latestM.downloads) - N(yoyM.downloads))  / N(yoyM.downloads)  * 100 : null;

  const kpiQ = useSQLQuery(`
    SELECT SUM(daily_download_sum) as total,
           strftime(MAX(download_date), '%b %d, %Y') as freshness
    FROM "duckdb_stats"."main"."pypi_daily_stats"
  `);
  const kpiRow = Array.isArray(kpiQ.data) && kpiQ.data.length > 0 ? kpiQ.data[0] : null;

  // Exclude current incomplete week
  const weeklyQ = useSQLQuery(`
    SELECT strftime(date_trunc('week', download_date),'%Y-%m-%d') as week,
           SUM(daily_download_sum) as downloads
    FROM "duckdb_stats"."main"."pypi_daily_stats"
    WHERE date_trunc('week', download_date) < date_trunc('week', current_date)
    GROUP BY 1 ORDER BY 1
  `);
  const allWeekly = Array.isArray(weeklyQ.data) ? weeklyQ.data : [];

  // Coerce downloads to Number — Recharts cannot render BigInt values for chart heights
  const weekData = (() => {
    let raw;
    if (weekZoom === "6m") raw = allWeekly.slice(-26);
    else {
      const z = WEEK_ZOOMS.find(z => z.key === weekZoom);
      raw = z?.from ? allWeekly.filter(d => d.week >= z.from) : allWeekly;
    }
    return raw.map(d => ({ week: d.week, downloads: N(d.downloads) }));
  })();

  const weekTickFmt = (v) => {
    const d = new Date(v);
    if (weekZoom === "6m") return d.getDate() <= 7 ? d.toLocaleString("en",{month:"short"}) : "";
    return d.getMonth() === 0 && d.getDate() <= 7 ? String(d.getFullYear()) : "";
  };
  const weekTickInterval = weekZoom === "6m" ? 3 : weekZoom === "2024" ? 3 : weekZoom === "2022" ? 8 : 12;

  const periodFilter = period === "all" ? "" : `AND download_date >= current_date - INTERVAL ${period} DAYS`;

  const versionsQ = useSQLQuery(`
    SELECT regexp_extract(version, '^\\d+\\.\\d+') as k, SUM(daily_download_sum) as d
    FROM "duckdb_stats"."main"."pypi_daily_stats"
    WHERE k != '' ${periodFilter}
    GROUP BY 1 ORDER BY 2 DESC LIMIT 8
  `);
  const pythonQ = useSQLQuery(`
    SELECT regexp_extract(python_version, '^\\d+\\.\\d+') as k, SUM(daily_download_sum) as d
    FROM "duckdb_stats"."main"."pypi_daily_stats"
    WHERE k != '' ${periodFilter}
    GROUP BY 1 ORDER BY 2 DESC LIMIT 8
  `);
  const countryQ = useSQLQuery(`
    SELECT country_code as k, SUM(daily_download_sum) as d
    FROM "duckdb_stats"."main"."pypi_daily_stats"
    WHERE k IS NOT NULL ${periodFilter}
    GROUP BY 1 ORDER BY 2 DESC LIMIT 8
  `);
  const osQ = useSQLQuery(`
    SELECT system_name as k, SUM(daily_download_sum) as d
    FROM "duckdb_stats"."main"."pypi_daily_stats"
    WHERE k IS NOT NULL ${periodFilter}
    GROUP BY 1 ORDER BY 2 DESC LIMIT 6
  `);

  const versions  = Array.isArray(versionsQ.data) ? versionsQ.data : [];
  const python    = Array.isArray(pythonQ.data)   ? pythonQ.data   : [];
  const countries = Array.isArray(countryQ.data)  ? countryQ.data  : [];
  const os        = Array.isArray(osQ.data)        ? osQ.data       : [];
  const breakdownLoading = versionsQ.isLoading || pythonQ.isLoading || countryQ.isLoading || osQ.isLoading;

  return (
    <div style={{background:C.bg,minHeight:"100vh",color:C.text,fontFamily:"-apple-system,BlinkMacSystemFont,'Segoe UI',sans-serif"}}>
      <style>{`@keyframes pulse{0%,100%{opacity:1}50%{opacity:0.4}}`}</style>

      <div style={{padding:"22px 28px",maxWidth:1100,margin:"0 auto"}}>

        <div style={{marginBottom:20,display:"flex",alignItems:"center",gap:10}}>
          <div style={{width:8,height:8,borderRadius:"50%",background:C.primary,flexShrink:0}}/>
          <div>
            <span style={{fontSize:18,fontWeight:700}}>DuckDB</span>
            <span style={{fontSize:18,fontWeight:300,color:C.muted,marginLeft:6}}>· PyPI Download Stats</span>
          </div>
          <span style={{fontSize:11,color:C.muted,marginLeft:"auto"}}>2019 – present</span>
        </div>

        <div style={{display:"grid",gridTemplateColumns:"repeat(5,1fr)",gap:32,marginBottom:24}}>
          <div>
            {kpiQ.isLoading ? <Skeleton h={36} w="80%"/> : <div style={{fontSize:28,fontWeight:800,color:C.text,lineHeight:1.1,letterSpacing:"-0.5px",fontVariantNumeric:"tabular-nums"}}>{kpiRow ? fmt(kpiRow.total) : "—"}</div>}
            <div style={{fontSize:11,color:C.muted,marginTop:5}}>all-time downloads</div>
          </div>
          <div>
            {monthlyQ.isLoading ? <Skeleton h={36} w="80%"/> : <div style={{fontSize:28,fontWeight:800,color:C.text,lineHeight:1.1,letterSpacing:"-0.5px",fontVariantNumeric:"tabular-nums"}}>{latestM ? fmt(latestM.downloads) : "—"}</div>}
            <div style={{fontSize:11,color:C.muted,marginTop:5}}>{latestM ? `${latestM.month} downloads` : "latest month"}</div>
          </div>
          <div>
            {monthlyQ.isLoading ? <Skeleton h={36} w="60%"/> : <div style={{fontSize:28,fontWeight:800,lineHeight:1.1,letterSpacing:"-0.5px",fontVariantNumeric:"tabular-nums",color:momPct!=null?(momPct>=0?C.positive:C.negative):C.muted}}>{momPct!=null ? `${momPct>0?"+":""}${momPct.toFixed(1)}%` : "—"}</div>}
            <div style={{fontSize:11,color:C.muted,marginTop:5}}>vs prior month</div>
          </div>
          <div>
            {monthlyQ.isLoading ? <Skeleton h={36} w="60%"/> : <div style={{fontSize:28,fontWeight:800,lineHeight:1.1,letterSpacing:"-0.5px",fontVariantNumeric:"tabular-nums",color:yoyPct!=null?(yoyPct>=0?C.positive:C.negative):C.muted}}>{yoyPct!=null ? `${yoyPct>0?"+":""}${yoyPct.toFixed(0)}%` : "—"}</div>}
            <div style={{fontSize:11,color:C.muted,marginTop:5}}>year-over-year</div>
          </div>
          <div>
            {kpiQ.isLoading ? <Skeleton h={36} w="90%"/> : <div style={{fontSize:28,fontWeight:800,color:C.text,lineHeight:1.1,letterSpacing:"-0.5px"}}>{kpiRow?.freshness ?? "—"}</div>}
            <div style={{fontSize:11,color:C.muted,marginTop:5}}>data freshness</div>
          </div>
        </div>

        <div style={{display:"grid",gridTemplateColumns:"240px 1fr",gap:20,marginBottom:20}}>
          <div style={{background:C.surface,borderRadius:10,padding:"14px 16px",border:`1px solid ${C.border}`,overflow:"hidden"}}>
            <div style={{fontSize:12,fontWeight:600,color:C.text,marginBottom:2}}>Monthly downloads</div>
            <div style={{fontSize:10,color:C.muted,marginBottom:12}}>last 6 complete months</div>
            {monthlyQ.isLoading
              ? <div style={{display:"flex",flexDirection:"column",gap:10}}>{[...Array(6)].map((_,i)=><Skeleton key={i} h={28}/>)}</div>
              : tableMonths.map((row, i) => {
                  const prev_ = tableMonths[i + 1];
                  const pct = prev_ ? ((N(row.downloads) - N(prev_.downloads)) / N(prev_.downloads) * 100) : null;
                  const isLatest = i === 0;
                  return (
                    <div key={row.month} style={{display:"flex",alignItems:"center",justifyContent:"space-between",padding:"5px 0",borderBottom:i<tableMonths.length-1?`1px solid ${C.border}`:"none"}}>
                      <span style={{fontSize:11,color:isLatest?C.text:C.muted,fontWeight:isLatest?600:400}}>{row.month}</span>
                      <div style={{textAlign:"right"}}>
                        <div style={{fontSize:isLatest?20:13,fontWeight:isLatest?800:500,color:isLatest?C.text:C.muted,lineHeight:1.2,fontVariantNumeric:"tabular-nums"}}>{fmt(row.downloads)}</div>
                        {pct!=null&&<div style={{fontSize:9,color:pct>=0?C.positive:C.negative,lineHeight:1.3}}>{pct>0?"▲":"▼"} {Math.abs(pct).toFixed(1)}%</div>}
                      </div>
                    </div>
                  );
                })
            }
          </div>

          <div style={{background:C.surface,borderRadius:10,padding:"14px 16px",border:`1px solid ${C.border}`}}>
            <div style={{display:"flex",alignItems:"center",justifyContent:"space-between",marginBottom:10}}>
              <div>
                <div style={{fontSize:12,fontWeight:600,color:C.text}}>Weekly downloads</div>
                <div style={{fontSize:10,color:C.muted,marginTop:2}}>May 2019 – present · complete weeks only</div>
              </div>
              <div style={{display:"flex",gap:3}}>
                {WEEK_ZOOMS.map(z=>(
                  <button key={z.key} onClick={()=>setWeekZoom(z.key)} style={{
                    padding:"3px 9px",fontSize:10,fontWeight:500,borderRadius:5,cursor:"pointer",
                    border:`1px solid ${weekZoom===z.key?C.primary:C.border}`,
                    background:weekZoom===z.key?C.primaryDim:"transparent",
                    color:weekZoom===z.key?C.primary:C.muted,
                  }}>{z.label}</button>
                ))}
              </div>
            </div>
            {weeklyQ.isLoading
              ? <div style={{background:C.border,borderRadius:6,height:305,animation:"pulse 1.5s ease-in-out infinite"}}/>
              : <ResponsiveContainer width="100%" height={305}>
                  <AreaChart data={weekData} margin={{top:4,right:4,left:0,bottom:0}}>
                    <defs>
                      <linearGradient id="g" x1="0" y1="0" x2="0" y2="1">
                        <stop offset="5%" stopColor={C.primary} stopOpacity={0.3}/>
                        <stop offset="95%" stopColor={C.primary} stopOpacity={0}/>
                      </linearGradient>
                    </defs>
                    <CartesianGrid strokeDasharray="3 3" stroke={C.border} vertical={false}/>
                    <XAxis dataKey="week" tick={{fontSize:10,fill:C.muted}} tickLine={false} axisLine={{stroke:C.border}}
                      tickFormatter={weekTickFmt} interval={weekTickInterval}/>
                    <YAxis tick={{fontSize:10,fill:C.muted}} tickLine={false} axisLine={false} tickFormatter={v=>fmt(v)} width={42}/>
                    <Tooltip content={<Tip/>}/>
                    <Area type="linear" dataKey="downloads" stroke={C.primary} strokeWidth={2} fill="url(#g)" dot={false} isAnimationActive={false}/>
                  </AreaChart>
                </ResponsiveContainer>
            }
          </div>
        </div>

        <div style={{background:C.surface,borderRadius:10,padding:"14px 16px",border:`1px solid ${C.border}`}}>
          <div style={{display:"flex",alignItems:"center",justifyContent:"space-between",marginBottom:16}}>
            <div>
              <div style={{fontSize:12,fontWeight:600,color:C.text}}>Download breakdown</div>
              <div style={{fontSize:10,color:C.muted,marginTop:2}}>by DuckDB version, Python version, country & OS</div>
            </div>
            <div style={{display:"flex",gap:4}}>
              {PERIODS.map(p=>(
                <button key={p.key} onClick={()=>setPeriod(p.key)} style={{
                  padding:"4px 11px",fontSize:11,fontWeight:500,borderRadius:6,cursor:"pointer",
                  border:`1px solid ${period===p.key?C.primary:C.border}`,
                  background:period===p.key?C.primaryDim:"transparent",
                  color:period===p.key?C.primary:C.muted,
                }}>{p.label}</button>
              ))}
            </div>
          </div>
          {breakdownLoading
            ? <div style={{display:"grid",gridTemplateColumns:"1fr 1fr 1fr 1fr",gap:24}}>
                {[...Array(4)].map((_,i)=>(
                  <div key={i} style={{display:"flex",flexDirection:"column",gap:8}}>
                    {[...Array(6)].map((__,j)=><Skeleton key={j} h={12}/>)}
                  </div>
                ))}
              </div>
            : <div style={{display:"grid",gridTemplateColumns:"1fr 1fr 1fr 1fr",gap:24}}>
                {[
                  {title:"DuckDB version", data:versions,  color:C.primary},
                  {title:"Python version", data:python,    color:C.amber},
                  {title:"Country",        data:countries, color:C.green},
                  {title:"OS",             data:os,        color:C.purple},
                ].map(({title,data,color})=>(
                  <div key={title}>
                    <div style={{fontSize:10,color:C.muted,marginBottom:10,fontWeight:600,textTransform:"uppercase",letterSpacing:"0.06em"}}>{title}</div>
                    {data.length > 0 ? <HBar data={data} barColor={color}/> : <div style={{fontSize:11,color:C.muted}}>No data</div>}
                  </div>
                ))}
              </div>
          }
        </div>

        <div style={{marginTop:14,fontSize:10,color:C.border,textAlign:"right"}}>
          duckdb_stats.main.pypi_daily_stats · updated daily
        </div>
      </div>
    </div>
  );
}


Weekly and monthly DuckDB download trends on PyPI since 2019, with version, Python, country and OS breakdowns. Sourced from duckdb_stats.main.pypi_daily_stats.