import { useSQLQuery, useDiveState } from "@motherduck/react-sql-query";
import { AreaChart, Area, XAxis, YAxis, CartesianGrid, Tooltip, ResponsiveContainer } 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",
};
const PLATFORM_COLOR = { TikTok: "#6FB89A", Instagram: "#C9A24A", X: "#9DB0A6" };
const fmtK = (v) => (v >= 1000000 ? (v / 1000000).toFixed(1) + "M" : v >= 1000 ? Math.round(v / 1000) + "K" : String(v));

export default function CulturalSentimentHeatmap() {
  const heat = useSQLQuery(`
    SELECT region_id, region_label, region_group, mentions_24h, sentiment_score,
           velocity_pct, top_platform, diaspora_pins
    FROM "prdp26"."main"."v_sentiment_heat"
  `);

  const pulse = useSQLQuery(`
    SELECT strftime(hour_ts, '%a %H:00') AS hour_label, hour_ts, platform, mentions
    FROM "prdp26"."main"."social_pulse"
    ORDER BY hour_ts
  `);

  const topics = useSQLQuery(`
    SELECT topic, platform, mentions_24h, sentiment_score, velocity_pct
    FROM "prdp26"."main"."social_topics"
    ORDER BY velocity_pct DESC
    LIMIT 8
  `);

  const pipeline = useSQLQuery(`
    SELECT component, status, last_run, records_processed, note
    FROM "prdp26"."main"."pipeline_status"
    ORDER BY sort_order
  `);

  const heatRows = Array.isArray(heat.data) ? heat.data : [];
  const pulseRows = Array.isArray(pulse.data) ? pulse.data : [];
  const topicRows = Array.isArray(topics.data) ? topics.data : [];
  const pipeRows = Array.isArray(pipeline.data) ? pipeline.data : [];

  const [region, setRegion] = useDiveState("region", "PR-ISLAND");

  // Pulse pivot: one row per hour, one key per platform
  const hours = [...new Map(pulseRows.map((r) => [r.hour_label, r.hour_label])).values()];
  const pulseData = hours.map((h) => {
    const row = { hour: h };
    pulseRows.filter((r) => r.hour_label === h).forEach((r) => { row[r.platform] = N(r.mentions); });
    return row;
  });

  const maxMentions = Math.max(1, ...heatRows.map((r) => N(r.mentions_24h)));
  const totalMentions = heatRows.reduce((s, r) => s + N(r.mentions_24h), 0);
  const avgSentiment = heatRows.length
    ? Math.round(heatRows.reduce((s, r) => s + N(r.sentiment_score) * N(r.mentions_24h), 0) / totalMentions)
    : 0;
  const hottest = heatRows[0] || {};
  const fastestTopic = [...topicRows].sort((a, b) => N(b.velocity_pct) - N(a.velocity_pct))[0] || {};
  const sel = heatRows.find((r) => r.region_id === region);
  const groups = ["New York City", "Northeast", "Florida", "Midwest", "Puerto Rico", "Global"];

  const heatTile = (r) => {
    const ratio = N(r.mentions_24h) / maxMentions;
    const active = r.region_id === region;
    return (
      <div key={r.region_id} onClick={() => setRegion(r.region_id)}
        className="p-2 rounded"
        style={{
          background: `rgba(201,162,74,${(0.07 + 0.6 * ratio).toFixed(2)})`,
          border: `1px solid ${active ? C.cream : "rgba(201,162,74,0.35)"}`,
          cursor: "pointer", minWidth: 118, flex: "1 1 118px",
        }}>
        <p className="text-xs font-semibold" style={{ color: ratio > 0.45 ? C.bg : C.cream }}>{r.region_label}</p>
        <p className="text-lg font-bold" style={{ color: ratio > 0.45 ? C.bg : C.gold }}>{fmtK(N(r.mentions_24h))}</p>
        <p className="text-xs" style={{ color: ratio > 0.45 ? "#3a3322" : C.muted }}>
          ☺ {N(r.sentiment_score)} · ▲{N(r.velocity_pct)}% · 📍{N(r.diaspora_pins)}
        </p>
      </div>
    );
  };

  return (
    <div className="p-6" style={{ background: C.bg, color: C.cream }}>
      {/* Header */}
      <div className="flex justify-between items-start flex-wrap gap-2">
        <div>
          <p className="text-xs uppercase" style={{ color: C.gold, letterSpacing: "0.25em" }}>
            § 10 fast-follow · The Real-Time Culture & Commerce Engine
          </p>
          <h1 className="text-2xl font-semibold mt-1" style={{ color: C.cream }}>Cultural Sentiment Heatmap</h1>
          <p className="text-sm" style={{ color: C.muted }}>
            Bright Data → Databricks NLP → MotherDuck · parade, artists & Lila Nikole · public, aggregated, anonymized
          </p>
        </div>
        <span className="text-xs px-3 py-2 rounded font-semibold"
          style={{ background: C.coral, color: C.bg }}>
          ● SIMULATED FEED — live pipeline lands as § 10 fast-follow
        </span>
      </div>

      {/* Pipeline status strip */}
      <div className="flex gap-2 mt-4 mb-5 flex-wrap">
        {pipeline.isLoading ? (
          <div className="animate-pulse rounded w-full" style={{ height: 50, background: C.panel }} />
        ) : pipeRows.map((p) => (
          <div key={p.component} className="px-3 py-2 rounded" style={{ background: C.panel, border: `1px solid ${C.line}`, flex: "1 1 200px" }}>
            <p className="text-xs font-semibold" style={{ color: C.cream }}>
              <span style={{ color: p.status === "healthy" ? C.teal : C.coral }}>●</span> {p.component}
            </p>
            <p className="text-xs" style={{ color: C.muted }}>
              {fmtK(N(p.records_processed))} recs · last {String(p.last_run).slice(11, 16)} UTC · {p.note}
            </p>
          </div>
        ))}
      </div>

      {/* KPIs */}
      <div className="grid grid-cols-4 gap-8 mb-5">
        <div>
          <p className="text-4xl font-bold" style={{ color: C.gold }}>{heat.isLoading ? "…" : fmtK(totalMentions)}</p>
          <p className="text-xs mt-1 uppercase" style={{ color: C.muted, letterSpacing: "0.1em" }}>mentions · 24h</p>
        </div>
        <div>
          <p className="text-4xl font-bold" style={{ color: C.teal }}>{heat.isLoading ? "…" : avgSentiment}</p>
          <p className="text-xs mt-1 uppercase" style={{ color: C.muted, letterSpacing: "0.1em" }}>weighted sentiment / 100</p>
        </div>
        <div>
          <p className="text-4xl font-bold" style={{ color: C.cream }}>{heat.isLoading ? "…" : (hottest.region_label || "—")}</p>
          <p className="text-xs mt-1 uppercase" style={{ color: C.muted, letterSpacing: "0.1em" }}>hottest region</p>
        </div>
        <div>
          <p className="text-4xl font-bold" style={{ color: C.coral }}>{topics.isLoading ? "…" : "+" + N(fastestTopic.velocity_pct) + "%"}</p>
          <p className="text-xs mt-1 uppercase" style={{ color: C.muted, letterSpacing: "0.1em" }}>fastest: {fastestTopic.topic || ""}</p>
        </div>
      </div>

      {/* 48h pulse */}
      <p className="text-xs uppercase mb-1" style={{ color: C.muted, letterSpacing: "0.15em" }}>
        48-hour pulse — mentions per hour by platform (4-min batch refresh)
      </p>
      {pulse.isLoading ? (
        <div className="animate-pulse rounded" style={{ height: 190, background: C.panel }} />
      ) : (
        <ResponsiveContainer width="100%" height={190}>
          <AreaChart data={pulseData} margin={{ top: 4, right: 8, bottom: 0, left: 0 }}>
            <CartesianGrid strokeDasharray="3 3" stroke={C.line} />
            <XAxis dataKey="hour" tick={{ fill: C.muted, fontSize: 10 }} axisLine={{ stroke: C.line }} tickLine={false} interval={7} />
            <YAxis tickFormatter={fmtK} tick={{ fill: C.muted, fontSize: 11 }} axisLine={false} tickLine={false} width={40} />
            <Tooltip contentStyle={{ background: C.bg, border: `1px solid ${C.line}`, fontSize: 12 }}
              labelStyle={{ color: C.gold }} formatter={(v, name) => [fmtK(N(v)), name]} />
            {["X", "Instagram", "TikTok"].map((p) => (
              <Area key={p} type="linear" dataKey={p} stackId="1"
                stroke={PLATFORM_COLOR[p]} fill={PLATFORM_COLOR[p]} fillOpacity={0.45} strokeWidth={1.5} />
            ))}
          </AreaChart>
        </ResponsiveContainer>
      )}

      {/* Heat grid */}
      <p className="text-xs uppercase mt-4 mb-1" style={{ color: C.muted, letterSpacing: "0.15em" }}>
        Engagement heat — tile intensity = 24h mention volume · ☺ sentiment · ▲ velocity · 📍 diaspora pins (§ 01)
      </p>
      {heat.isLoading ? (
        <div className="animate-pulse rounded" style={{ height: 180, background: C.panel }} />
      ) : (
        groups.map((g) => {
          const rows = heatRows.filter((r) => r.region_group === g);
          if (!rows.length) return null;
          return (
            <div key={g} className="mb-2">
              <p className="text-xs mb-1" style={{ color: C.muted }}>{g}</p>
              <div className="flex gap-2 flex-wrap">{rows.map(heatTile)}</div>
            </div>
          );
        })
      )}
      {sel && (
        <p className="text-xs mt-1 p-2 rounded" style={{ background: C.panel, border: `1px solid ${C.line}`, color: C.cream }}>
          <span style={{ color: C.gold, fontWeight: 600 }}>{sel.region_label}</span> — {fmtK(N(sel.mentions_24h))} mentions,
          sentiment {N(sel.sentiment_score)}/100, +{N(sel.velocity_pct)}% velocity, top platform {sel.top_platform},{" "}
          <span style={{ color: C.teal }}>{N(sel.diaspora_pins)} diaspora pins dropped</span> — heat here is your cue to push the § 01 pin QR and Rep Your Barrio inventory.
        </p>
      )}

      {/* Trending topics */}
      <p className="text-xs uppercase mt-4 mb-1" style={{ color: C.gold, letterSpacing: "0.2em" }}>
        Trend extraction — fastest-moving topics (Databricks NLP)
      </p>
      {topics.isLoading ? (
        <div className="animate-pulse rounded" style={{ height: 110, background: C.panel }} />
      ) : (
        <table className="w-full text-sm">
          <thead>
            <tr className="text-left text-xs uppercase" style={{ color: C.muted }}>
              <th className="pb-1">Topic</th><th className="pb-1">Platform</th>
              <th className="pb-1 text-right">Mentions 24h</th><th className="pb-1 text-right">Sentiment</th>
              <th className="pb-1 text-right">Velocity</th>
            </tr>
          </thead>
          <tbody>
            {topicRows.map((t) => (
              <tr key={t.topic} style={{ borderTop: `1px solid ${C.line}` }}>
                <td className="py-1" style={{ color: C.cream }}>{t.topic}</td>
                <td className="py-1 text-xs" style={{ color: PLATFORM_COLOR[t.platform] || C.muted }}>{t.platform}</td>
                <td className="py-1 text-right" style={{ color: C.cream }}>{fmtK(N(t.mentions_24h))}</td>
                <td className="py-1 text-right" style={{ color: N(t.sentiment_score) >= 85 ? C.teal : N(t.sentiment_score) >= 75 ? C.cream : C.coral }}>
                  {N(t.sentiment_score)}
                </td>
                <td className="py-1 text-right font-semibold" style={{ color: N(t.velocity_pct) >= 80 ? C.coral : C.gold }}>
                  ▲ {N(t.velocity_pct)}%
                </td>
              </tr>
            ))}
          </tbody>
        </table>
      )}

      <p className="text-xs mt-4" style={{ color: C.muted }}>
        WRTH · prdp26 · Guardrails: public posts only, aggregated & anonymized before storage, no PII retained, platform ToS and rate limits respected.
        Near-real-time (4-min batches) by design; true streaming is a later upgrade. Feed shown is simulated until the § 10 pipeline ships. Stamped 2026-06-10.
      </p>
    </div>
  );
}

WRTH × 69th NPRDP (June 14, 2026). The Real-Time Culture & Commerce Engine, Idea 1: a live-control-room sentiment heatmap. Bright Data → Databricks NLP → MotherDuck pipeline status strip, 48-hour mention pulse by platform (TikTok/Instagram/X), borough-to-global engagement heat tiles paired with § 01 diaspora pins, and a fastest-moving-topics trend table. Public/aggregated/anonymized guardrails throughout; simulated feed until the § 10 fast-follow pipeline ships. Powered by prdp26.

WRTH x LILA Nikole - PRDP26 · Cultural Sentiment Heatmap — Live Control Room

Comments