import React, { useMemo, useState } from "react";
import { useSQLQuery } from "@motherduck/react-sql-query";
import {
  Area, AreaChart, Bar, BarChart, CartesianGrid, Cell, ComposedChart,
  Legend, Line, ResponsiveContainer, Scatter, ScatterChart, Tooltip, XAxis, YAxis
} from "recharts";

const COLORS = {
  bg: "#070707",
  panel: "#111318",
  panel2: "#171a21",
  duck: "#FFD84D",
  blue: "#4DA3FF",
  green: "#47E6A0",
  red: "#FF5C5C",
  orange: "#FF9F43",
  purple: "#A78BFA",
  text: "#F7F3DF",
  muted: "#9CA3AF",
  grid: "#2A2F3A"
};

function sqlSite(site) {
  return site === "Site B" ? "Site B" : "Site A";
}

const SHARE_URL = "md:_share/gsc_anonymous_dive_public/4154ec92-abe0-400c-86e1-6d1c1645e1a7";

function useRows(sql) {
  // The Gallery sandbox does not automatically attach required_resources yet, so
  // attach the unrestricted public share inside each query before reading it.
  const { data, loading, error } = useSQLQuery(`ATTACH IF NOT EXISTS '${SHARE_URL}' AS gsc_anonymous_dive; ${sql}`);
  return { rows: data || [], loading, error };
}

function Card({ label, value, hint, color = COLORS.duck }) {
  return <div style={{ background: COLORS.panel, border: `1px solid ${COLORS.grid}`, borderRadius: 18, padding: 18 }}>
    <div style={{ color: COLORS.muted, fontSize: 12, letterSpacing: 1, textTransform: "uppercase" }}>{label}</div>
    <div style={{ color, fontSize: 34, fontWeight: 900, marginTop: 6 }}>{value}</div>
    <div style={{ color: COLORS.muted, fontSize: 12, marginTop: 6 }}>{hint}</div>
  </div>;
}

function Panel({ title, subtitle, children }) {
  return <section style={{ background: COLORS.panel, border: `1px solid ${COLORS.grid}`, borderRadius: 22, padding: 20, marginTop: 18 }}>
    <div style={{ display: "flex", justifyContent: "space-between", gap: 12, marginBottom: 14 }}>
      <div>
        <h2 style={{ color: COLORS.text, fontSize: 22, margin: 0 }}>{title}</h2>
        {subtitle && <p style={{ color: COLORS.muted, margin: "6px 0 0", fontSize: 13 }}>{subtitle}</p>}
      </div>
    </div>
    {children}
  </section>;
}

function MiniBadge({ children, color = COLORS.duck }) {
  return <span style={{ border: `1px solid ${color}`, color, padding: "4px 9px", borderRadius: 999, fontSize: 12, fontWeight: 700 }}>{children}</span>;
}

export default function Dive() {
  const [site, setSite] = useState("Site A");
  const s = sqlSite(site);

  const daily = useRows(`
    SELECT date, clicks_index, impressions_index, ctr_pct, avg_position, opportunity_index
    FROM gsc_anonymous_dive.main.daily_performance
    WHERE site_id = '${s}'
    ORDER BY date
  `);

  const opportunities = useRows(`
    SELECT query_id, intent_class, query_length_bucket, clicks_index, impressions_index,
           ctr_pct, avg_position, opportunity_score, rank_zone
    FROM gsc_anonymous_dive.main.query_opportunities
    WHERE site_id = '${s}' AND rank_zone = 'striking distance'
    ORDER BY opportunity_score DESC
    LIMIT 12
  `);

  const intents = useRows(`
    SELECT intent_class, round(sum(impressions_index), 2) AS demand_index,
           round(avg(ctr_pct), 2) AS ctr_pct,
           round(avg(avg_position), 2) AS avg_position
    FROM gsc_anonymous_dive.main.query_opportunities
    WHERE site_id = '${s}'
    GROUP BY intent_class
    ORDER BY demand_index DESC
  `);

  const templates = useRows(`
    SELECT page_template, count(*) AS pages,
           round(avg(opportunity_index), 2) AS opportunity_index,
           round(avg(ctr_pct), 2) AS ctr_pct,
           round(avg(avg_position), 2) AS avg_position
    FROM gsc_anonymous_dive.main.page_performance
    WHERE site_id = '${s}'
    GROUP BY page_template
    ORDER BY opportunity_index DESC
  `);

  const device = useRows(`
    SELECT intent_class, device, impressions_index, avg_position
    FROM gsc_anonymous_dive.main.device_intent_performance
    WHERE site_id = '${s}'
    ORDER BY impressions_index DESC
  `);

  const regions = useRows(`
    SELECT region, impressions_index, clicks_index
    FROM gsc_anonymous_dive.main.region_performance
    WHERE site_id = '${s}'
    ORDER BY impressions_index DESC
  `);

  const kpis = useMemo(() => {
    const rows = daily.rows;
    const last = rows[rows.length - 1] || {};
    const avg = (key) => rows.length ? rows.reduce((a, r) => a + Number(r[key] || 0), 0) / rows.length : 0;
    const opp = avg("opportunity_index");
    const ctr = avg("ctr_pct");
    const pos = avg("avg_position");
    return {
      demand: Math.round(last.impressions_index || 0),
      clicks: Math.round(last.clicks_index || 0),
      ctr: ctr.toFixed(1) + "%",
      pos: pos.toFixed(1),
      pain: Math.min(100, Math.round(opp))
    };
  }, [daily.rows]);

  const anyLoading = daily.loading || opportunities.loading || intents.loading || templates.loading;
  const anyError = daily.error || opportunities.error || intents.error || templates.error;

  if (anyError) {
    return <div style={{ padding: 24, color: COLORS.red, background: COLORS.bg }}>Query error: {String(anyError)}</div>;
  }

  return <main style={{ background: COLORS.bg, color: COLORS.text, minHeight: "100vh", padding: 28, fontFamily: "Inter, ui-sans-serif, system-ui" }}>
    <div style={{ display: "flex", justifyContent: "space-between", gap: 20, alignItems: "flex-start" }}>
      <div>
        <div style={{ display: "flex", gap: 8, marginBottom: 12 }}>
          <MiniBadge>public-safe GSC</MiniBadge><MiniBadge color={COLORS.blue}>no queries or URLs exposed</MiniBadge><MiniBadge color={COLORS.green}>normalized indexes</MiniBadge>
        </div>
        <h1 style={{ fontSize: 48, lineHeight: 1, margin: 0, letterSpacing: -1.5 }}>
          Anonymous Search Console<br/><span style={{ color: COLORS.duck }}>Opportunity Map</span>
        </h1>
        <p style={{ maxWidth: 780, color: COLORS.muted, fontSize: 16, lineHeight: 1.5 }}>
          A privacy-preserving SEO Dive: real Google Search Console performance, transformed so the public can see the story without seeing the domains, URLs, raw search queries, or traffic counts.
        </p>
      </div>
      <div style={{ display: "flex", gap: 8, background: COLORS.panel, border: `1px solid ${COLORS.grid}`, padding: 8, borderRadius: 999 }}>
        {["Site A", "Site B"].map(x => <button key={x} onClick={() => setSite(x)} style={{ cursor: "pointer", border: 0, borderRadius: 999, padding: "10px 16px", fontWeight: 800, background: site === x ? COLORS.duck : "transparent", color: site === x ? "#111" : COLORS.text }}>{x}</button>)}
      </div>
    </div>

    {anyLoading && <div style={{ color: COLORS.muted, marginTop: 12 }}>Loading live MotherDuck queries…</div>}

    <div style={{ display: "grid", gridTemplateColumns: "repeat(5, minmax(0, 1fr))", gap: 14, marginTop: 22 }}>
      <Card label="Demand now" value={kpis.demand} hint="latest normalized impression index" />
      <Card label="Clicks now" value={kpis.clicks} hint="latest normalized click index" color={COLORS.green} />
      <Card label="CTR drag" value={kpis.ctr} hint="average CTR across the window" color={COLORS.orange} />
      <Card label="Avg position" value={kpis.pos} hint="lower is better" color={COLORS.blue} />
      <Card label="Opportunity heat" value={kpis.pain} hint="100 = biggest unrealized demand" color={kpis.pain > 50 ? COLORS.red : COLORS.duck} />
    </div>

    <Panel title="The 90-day search pulse" subtitle="Demand and clicks are indexed, so the shape is real but business volume stays private.">
      <ResponsiveContainer width="100%" height={320}>
        <ComposedChart data={daily.rows}>
          <CartesianGrid stroke={COLORS.grid} />
          <XAxis dataKey="date" stroke={COLORS.muted} tick={{ fontSize: 11 }} />
          <YAxis stroke={COLORS.muted} />
          <Tooltip contentStyle={{ background: COLORS.panel2, border: `1px solid ${COLORS.grid}`, color: COLORS.text }} />
          <Legend />
          <Area type="monotone" dataKey="impressions_index" name="Demand index" fill={COLORS.blue} stroke={COLORS.blue} fillOpacity={0.18} />
          <Line type="monotone" dataKey="clicks_index" name="Click index" stroke={COLORS.green} strokeWidth={3} dot={false} />
          <Line type="monotone" dataKey="opportunity_index" name="Opportunity heat" stroke={COLORS.orange} strokeWidth={2} dot={false} />
        </ComposedChart>
      </ResponsiveContainer>
    </Panel>

    <div style={{ display: "grid", gridTemplateColumns: "1.1fr .9fr", gap: 18 }}>
      <Panel title="Demand by intent" subtitle="Raw queries are classified, then removed. The public sees intent, not keywords.">
        <ResponsiveContainer width="100%" height={300}>
          <BarChart data={intents.rows} layout="vertical" margin={{ left: 24 }}>
            <CartesianGrid stroke={COLORS.grid} />
            <XAxis type="number" stroke={COLORS.muted} />
            <YAxis type="category" dataKey="intent_class" stroke={COLORS.muted} width={135} tick={{ fontSize: 12 }} />
            <Tooltip contentStyle={{ background: COLORS.panel2, border: `1px solid ${COLORS.grid}`, color: COLORS.text }} />
            <Bar dataKey="demand_index" name="Demand index" radius={[0, 8, 8, 0]}>
              {intents.rows.map((_, i) => <Cell key={i} fill={[COLORS.duck, COLORS.blue, COLORS.green, COLORS.orange, COLORS.purple][i % 5]} />)}
            </Bar>
          </BarChart>
        </ResponsiveContainer>
      </Panel>

      <Panel title="Page-template drag" subtitle="Where the site leaks demand: not page names, just page archetypes.">
        <ResponsiveContainer width="100%" height={300}>
          <ScatterChart>
            <CartesianGrid stroke={COLORS.grid} />
            <XAxis dataKey="avg_position" name="Avg position" stroke={COLORS.muted} />
            <YAxis dataKey="opportunity_index" name="Opportunity" stroke={COLORS.muted} />
            <Tooltip cursor={{ strokeDasharray: "3 3" }} contentStyle={{ background: COLORS.panel2, border: `1px solid ${COLORS.grid}`, color: COLORS.text }} />
            <Scatter data={templates.rows} fill={COLORS.duck} name="Templates" />
          </ScatterChart>
        </ResponsiveContainer>
        <div style={{ display: "grid", gridTemplateColumns: "repeat(2, 1fr)", gap: 8 }}>
          {templates.rows.map(t => <div key={t.page_template} style={{ background: COLORS.panel2, borderRadius: 12, padding: 10, color: COLORS.muted }}><b style={{ color: COLORS.text }}>{t.page_template}</b><br/>Opp: {t.opportunity_index} · CTR: {t.ctr_pct}%</div>)}
        </div>
      </Panel>
    </div>

    <Panel title="Top anonymous refresh candidates" subtitle="The highest-upside striking-distance queries. Query IDs are stable inside the dataset, but not reversible to the original search terms.">
      <div style={{ overflowX: "auto" }}>
        <table style={{ width: "100%", borderCollapse: "collapse", fontSize: 13 }}>
          <thead><tr style={{ color: COLORS.muted, textAlign: "left" }}>
            {['Query ID','Intent','Length','Demand','CTR','Position','Recommendation'].map(h => <th key={h} style={{ padding: 10, borderBottom: `1px solid ${COLORS.grid}` }}>{h}</th>)}
          </tr></thead>
          <tbody>
            {opportunities.rows.map((r, i) => <tr key={r.query_id} style={{ background: i % 2 ? COLORS.panel2 : "transparent" }}>
              <td style={{ padding: 10, color: COLORS.duck, fontWeight: 800 }}>{r.query_id}</td>
              <td style={{ padding: 10 }}>{r.intent_class}</td>
              <td style={{ padding: 10, color: COLORS.muted }}>{r.query_length_bucket}</td>
              <td style={{ padding: 10 }}>{r.impressions_index}</td>
              <td style={{ padding: 10 }}>{r.ctr_pct}%</td>
              <td style={{ padding: 10 }}>{r.avg_position}</td>
              <td style={{ padding: 10, color: COLORS.green }}>Refresh title, improve snippet, add internal links</td>
            </tr>)}
          </tbody>
        </table>
      </div>
    </Panel>

    <div style={{ display: "grid", gridTemplateColumns: "1fr 1fr", gap: 18 }}>
      <Panel title="Device x intent mismatch" subtitle="Where mobile/desktop behavior changes the SEO bet.">
        <ResponsiveContainer width="100%" height={280}>
          <BarChart data={device.rows.slice(0, 18)}>
            <CartesianGrid stroke={COLORS.grid} />
            <XAxis dataKey="device" stroke={COLORS.muted} />
            <YAxis stroke={COLORS.muted} />
            <Tooltip contentStyle={{ background: COLORS.panel2, border: `1px solid ${COLORS.grid}`, color: COLORS.text }} />
            <Bar dataKey="impressions_index" fill={COLORS.purple} name="Impressions index" />
          </BarChart>
        </ResponsiveContainer>
      </Panel>
      <Panel title="Region mix" subtitle="Countries are collapsed to coarse regions for privacy.">
        <ResponsiveContainer width="100%" height={280}>
          <BarChart data={regions.rows}>
            <CartesianGrid stroke={COLORS.grid} />
            <XAxis dataKey="region" stroke={COLORS.muted} tick={{ fontSize: 11 }} />
            <YAxis stroke={COLORS.muted} />
            <Tooltip contentStyle={{ background: COLORS.panel2, border: `1px solid ${COLORS.grid}`, color: COLORS.text }} />
            <Bar dataKey="impressions_index" fill={COLORS.blue} name="Demand index" />
            <Bar dataKey="clicks_index" fill={COLORS.green} name="Click index" />
          </BarChart>
        </ResponsiveContainer>
      </Panel>
    </div>

    <Panel title="Why this is public-safe" subtitle="The data story stays useful while private SEO assets stay private.">
      <div style={{ display: "grid", gridTemplateColumns: "repeat(4, 1fr)", gap: 12 }}>
        {["Domains removed", "URLs replaced with page IDs", "Queries replaced with intent + IDs", "Counts normalized to indexes"].map((x, i) => <div key={x} style={{ background: COLORS.panel2, borderRadius: 16, padding: 16, border: `1px solid ${COLORS.grid}` }}><div style={{ color: COLORS.duck, fontSize: 28, fontWeight: 900 }}>0{i + 1}</div><div style={{ fontWeight: 800 }}>{x}</div></div>)}
      </div>
    </Panel>
  </main>;
}


A privacy-preserving SEO Dive built from real Google Search Console data: domains, URLs, queries, and raw traffic counts are removed while intent, opportunity, and performance shape remain explorable.

Anonymous Search Console Opportunity Map

Comments