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


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

const C = {
  bg: "#0E1B1B", panel: "#122222", gold: "#C9A24A", teal: "#6FB89A",
  coral: "#D17765", cream: "#F7EAD1", muted: "#9DB0A6", line: "#24403A",
};

export default function PRByTheNumbers() {
  const metrics = useSQLQuery(`
    SELECT metric_id, metric_label, value, unit, period, source, confidence, category, sort_order
    FROM "prdp26"."main"."v_thesis"
  `);

  const trends = useSQLQuery(`
    SELECT series, year, value, confidence
    FROM "prdp26"."main"."thesis_trends"
    ORDER BY year
  `);

  const lift = useSQLQuery(`
    SELECT lift_label, lift_pct, period, source, lift_type
    FROM "prdp26"."main"."culture_lift"
    ORDER BY sort_order
  `);

  const mRows = Array.isArray(metrics.data) ? metrics.data : [];
  const tRows = Array.isArray(trends.data) ? trends.data : [];
  const lRows = Array.isArray(lift.data) ? lift.data : [];

  const [showSources, setShowSources] = useDiveState("sources", false);

  const m = (id) => mRows.find((r) => r.metric_id === id) || {};
  const trendData = [...new Set(tRows.map((r) => N(r.year)))].map((y) => ({
    year: String(y),
    gdp: N((tRows.find((r) => r.series === "GDP" && N(r.year) === y) || {}).value),
    visitor: N((tRows.find((r) => r.series === "VISITOR" && N(r.year) === y) || {}).value),
  }));
  const liftData = lRows.map((r) => ({ ...r, lift_pct: N(r.lift_pct) }));

  const kpi = (id, fmt, accent) => {
    const r = m(id);
    return (
      <div>
        {metrics.isLoading ? (
          <div className="animate-pulse rounded" style={{ height: 44, width: 110, background: C.line }} />
        ) : (
          <p className="text-4xl font-bold" style={{ color: accent }}>{fmt(r)}</p>
        )}
        <p className="text-xs mt-1 uppercase" style={{ color: C.muted, letterSpacing: "0.08em" }}>
          {r.metric_label || ""} <span style={{ color: C.line }}>·</span> {r.period || ""}
        </p>
      </div>
    );
  };

  return (
    <div className="p-6" style={{ background: C.bg, color: C.cream }}>
      <p className="text-xs uppercase" style={{ color: C.gold, letterSpacing: "0.25em" }}>
        § 09 · WRTH × 69th NPRDP · The Thesis
      </p>
      <h1 className="text-2xl font-semibold mt-1" style={{ color: C.cream }}>P.R. by the Numbers — Culture Drives Commerce</h1>
      <p className="text-sm mb-5" style={{ color: C.muted }}>
        The market is real, growing, and culturally ignited. Cleanest proof: the 2025 residency — and this parade is dedicated to its hometown, Vega Baja.
      </p>

      {/* Hero KPIs */}
      <div className="grid grid-cols-4 gap-8 mb-2">
        {kpi("M-GDP", (r) => `$${N(r.value)}B`, C.cream)}
        {kpi("M-VISITOR", (r) => `$${N(r.value).toFixed(0)}B+`, C.teal)}
        {kpi("M-MUSIC7", (r) => `#${N(r.value)}`, C.coral)}
        {kpi("M-RES-IMPACT", (r) => `$${N(r.value)}M+`, C.gold)}
      </div>
      <div className="grid grid-cols-4 gap-8 mb-6">
        {kpi("M-ARTS", (r) => `+${N(r.value)}%`, C.teal)}
        {kpi("M-RETAIL", (r) => `+${N(r.value)}%`, C.cream)}
        {kpi("M-RES-ATTEND", (r) => `${N(r.value)}K`, C.gold)}
        {kpi("M-CREATIVE", (r) => `$${N(r.value)}B`, C.coral)}
      </div>

      <div className="grid grid-cols-2 gap-6">
        {/* Trend lines */}
        <div>
          <p className="text-xs uppercase mb-1" style={{ color: C.muted, letterSpacing: "0.15em" }}>
            GDP ($B, left) & visitor economy ($B, right) — 2020 → 2026e
          </p>
          {trends.isLoading ? (
            <div className="animate-pulse rounded" style={{ height: 230, background: C.panel }} />
          ) : (
            <ResponsiveContainer width="100%" height={230}>
              <ComposedChart data={trendData} margin={{ top: 8, right: 8, bottom: 0, left: 0 }}>
                <CartesianGrid strokeDasharray="3 3" stroke={C.line} />
                <XAxis dataKey="year" tick={{ fill: C.muted, fontSize: 11 }} axisLine={{ stroke: C.line }} tickLine={false} />
                <YAxis yAxisId="gdp" domain={[95, 135]} tick={{ fill: C.muted, fontSize: 11 }} axisLine={false} tickLine={false} width={36} />
                <YAxis yAxisId="vis" orientation="right" domain={[0, 20]} tick={{ fill: C.teal, fontSize: 11 }} axisLine={false} tickLine={false} width={30} />
                <Tooltip contentStyle={{ background: C.bg, border: `1px solid ${C.line}`, fontSize: 12 }}
                  labelStyle={{ color: C.gold }} itemStyle={{ color: C.cream }}
                  formatter={(v, name) => [`$${N(v)}B`, name === "gdp" ? "GDP" : "Visitor economy"]} />
                <Line yAxisId="gdp" type="linear" dataKey="gdp" stroke={C.cream} strokeWidth={2} dot={{ r: 2, fill: C.cream }} />
                <Line yAxisId="vis" type="linear" dataKey="visitor" stroke={C.teal} strokeWidth={2} dot={{ r: 2, fill: C.teal }} />
              </ComposedChart>
            </ResponsiveContainer>
          )}
          <p className="text-xs" style={{ color: C.muted }}>2024 endpoints sourced; earlier years interpolated, 2025–26 estimates.</p>
        </div>

        {/* Culture → spending lift */}
        <div>
          <p className="text-xs uppercase mb-1" style={{ color: C.muted, letterSpacing: "0.15em" }}>
            Culture event → spending lift (% YoY) — residency vs island baseline
          </p>
          {lift.isLoading ? (
            <div className="animate-pulse rounded" style={{ height: 230, background: C.panel }} />
          ) : (
            <ResponsiveContainer width="100%" height={230}>
              <BarChart data={liftData} layout="vertical" margin={{ top: 4, right: 40, bottom: 0, left: 8 }}>
                <CartesianGrid strokeDasharray="3 3" stroke={C.line} horizontal={false} />
                <XAxis type="number" domain={[0, 80]} tick={{ fill: C.muted, fontSize: 11 }} axisLine={{ stroke: C.line }} tickLine={false} tickFormatter={(v) => v + "%"} />
                <YAxis type="category" dataKey="lift_label" width={158} tick={{ fill: C.cream, fontSize: 10 }} axisLine={false} tickLine={false} />
                <Tooltip contentStyle={{ background: C.bg, border: `1px solid ${C.line}`, fontSize: 12 }}
                  labelStyle={{ color: C.gold }} itemStyle={{ color: C.cream }}
                  formatter={(v, _n, p) => [`+${N(v)}% · ${p.payload.period} · ${p.payload.source}`, ""]} />
                <Bar dataKey="lift_pct" radius={[0, 3, 3, 0]} barSize={16}>
                  <LabelList dataKey="lift_pct" position="right" formatter={(v) => "+" + N(v) + "%"} style={{ fill: C.cream, fontSize: 10 }} />
                  {liftData.map((d, i) => (
                    <Cell key={i} fill={d.lift_type === "residency" ? C.gold : C.muted} fillOpacity={d.lift_type === "residency" ? 0.9 : 0.55} />
                  ))}
                </Bar>
              </BarChart>
            </ResponsiveContainer>
          )}
          <p className="text-xs" style={{ color: C.muted }}>
            Gold = 2025 residency window · gray = island baseline. One event moved every gold bar.
          </p>
        </div>
      </div>

      {/* The 100×35 thesis strip */}
      <div className="mt-5 p-4 rounded" style={{ background: C.panel, border: `1px solid ${C.gold}` }}>
        <p className="text-xs uppercase mb-1" style={{ color: C.gold, letterSpacing: "0.2em" }}>The 100×35 Thesis — twin engines</p>
        <p className="text-sm" style={{ color: C.cream, lineHeight: 1.6 }}>
          An island of <span style={{ color: C.teal, fontWeight: 600 }}>3.2M</span> with a diaspora of{" "}
          <span style={{ color: C.teal, fontWeight: 600 }}>5.8M+</span> behaves like a market of 9 million — and culture is its demand engine.
          One artist's 30-night residency produced <span style={{ color: C.gold, fontWeight: 600 }}>$200M–$733M</span> of measured economic impact
          and double-digit lifts in every adjacent spending category; this Sunday, <span style={{ color: C.gold, fontWeight: 600 }}>1M+</span> people
          line Fifth Avenue for the parade dedicated to that artist's hometown. When culture moves, commerce follows — WRTH is the operating
          system that captures the move.
        </p>
      </div>

      {/* Sources table toggle */}
      <button onClick={() => setShowSources(!showSources)}
        className="text-xs px-3 py-2 rounded font-semibold mt-4"
        style={{ background: showSources ? C.gold : "transparent", color: showSources ? C.bg : C.gold, border: `1px solid ${C.gold}`, cursor: "pointer" }}>
        {showSources ? "Hide" : "Show"} the tidy table — metric · value · period · source
      </button>
      {showSources && (
        <table className="w-full text-xs mt-2">
          <thead>
            <tr className="text-left uppercase" style={{ color: C.muted }}>
              <th className="pb-1">Metric</th><th className="pb-1 text-right">Value</th>
              <th className="pb-1">Period</th><th className="pb-1">Source</th><th className="pb-1 text-right">Confidence</th>
            </tr>
          </thead>
          <tbody>
            {mRows.map((r) => (
              <tr key={r.metric_id} style={{ borderTop: `1px solid ${C.line}` }}>
                <td className="py-1" style={{ color: C.cream }}>{r.metric_label}</td>
                <td className="py-1 text-right font-semibold" style={{ color: C.gold }}>{N(r.value)} {r.unit}</td>
                <td className="py-1" style={{ color: C.muted }}>{r.period}</td>
                <td className="py-1" style={{ color: C.muted }}>{r.source}</td>
                <td className="py-1 text-right" style={{ color: r.confidence === "sourced" ? C.teal : C.coral }}>{r.confidence}</td>
              </tr>
            ))}
          </tbody>
        </table>
      )}

      <p className="text-xs mt-4" style={{ color: C.muted }}>
        WRTH · prdp26 · Credibility asset for partners, press & investors. Exports-to-U.S. figure (76.5%) is a 2016 estimate; clothing-spend lift is a WRTH intelligence estimate — refresh both before external decks. Stamped 2026-06-10.
      </p>
    </div>
  );
}

WRTH × 69th NPRDP (June 14, 2026). The 100×35 thesis dashboard: culture drives commerce. KPI grid (GDP $125.8B FY2024, $15B+ visitor economy, #7 world music exporter, $8.7B creative industries), GDP + visitor-economy trend lines 2020–2026e, and the culture-event → spending-lift proof bar anchored on the 2025 Bad Bunny residency ($200M–$733M impact, 600K attendees, +70% hotel occupancy) vs the island retail baseline. Full metric · value · period · source table with confidence flags. Credibility asset for partners, press, and investors. Powered by prdp26.

WRTH x LIla Nikole - PRDP26 · P.R. by the Numbers — Culture Drives Commerce

Comments