//  File:    page.tsx
//  Path:    apps/web/app/insights/page.tsx
//  Project: AI Risk Index — airiskindex.io
//  Author:  Simon-Pierre Boucher
//  Contact: contact@spboucher.ai
//  Copyright © 2026 Simon-Pierre Boucher. All rights reserved.
//
//  Description: Insights — headline metrics: wage bill under pressure, workers in high-pressure occupations, augmentation counterpoint.

import Link from "next/link";
import { prisma } from "@airiskindex/db";
import { HIGH_EXPOSURE_THRESHOLD } from "@airiskindex/scoring";
import { DecileColumns, ScatterPlot } from "@/components/insights-charts";
import { DistributionChart } from "@/components/score-marks";
import { SOC_MAJOR_GROUPS } from "@/lib/soc-groups";

export const dynamic = "force-dynamic";

export const metadata = {
  title: "Insights — AI Risk Index",
  description:
    "Headline metrics from the index: share of the U.S. wage bill under AI substitution pressure, workers in high-pressure occupations, and the augmentation counterpoint.",
};

interface Aggregates {
  n: number;
  total_emp: number;
  wage_bill: number;
  wage_at_risk: number;
  emp_weighted_sub: number;
  emp_weighted_aug: number;
  emp_high_sub: number;
  emp_high_aug: number;
  avg_ci_width: number;
}

function money(value: number): string {
  if (value >= 1e12) return `$${(value / 1e12).toFixed(2)}T`;
  if (value >= 1e9) return `$${(value / 1e9).toFixed(0)}B`;
  return `$${Math.round(value).toLocaleString("en-US")}`;
}

function millions(value: number): string {
  return value >= 1e6 ? `${(value / 1e6).toFixed(1)}M` : Math.round(value).toLocaleString("en-US");
}

async function loadInsights() {
  const run = await prisma.scoreRun.findFirst({ orderBy: { createdAt: "desc" } });
  if (!run) return null;

  const [agg] = await prisma.$queryRaw<Aggregates[]>`
    SELECT count(*)::int                                                          AS n,
           sum(o.employment)::float8                                              AS total_emp,
           sum(o.employment::float8 * o."medianWageCents"::float8 / 100.0)::float8 AS wage_bill,
           sum(o.employment::float8 * o."medianWageCents"::float8 / 100.0 * s.substitution / 100.0)::float8 AS wage_at_risk,
           (sum(o.employment * s.substitution) / sum(o.employment))::float8       AS emp_weighted_sub,
           (sum(o.employment * s.augmentation) / sum(o.employment))::float8       AS emp_weighted_aug,
           sum(CASE WHEN s.substitution >= 70 THEN o.employment ELSE 0 END)::float8 AS emp_high_sub,
           sum(CASE WHEN s.augmentation >= 70 THEN o.employment ELSE 0 END)::float8 AS emp_high_aug,
           avg(s."substitutionHigh" - s."substitutionLow")::float8                AS avg_ci_width
    FROM "OccupationScore" s
    JOIN "Occupation" o ON o.code = s."occupationCode"
    WHERE s."runId" = ${run.id} AND o.employment IS NOT NULL AND o."medianWageCents" IS NOT NULL
  `;

  const [taskShare] = await prisma.$queryRaw<Array<{ share: number; total: bigint }>>`
    SELECT (count(*) FILTER (WHERE substitution >= ${HIGH_EXPOSURE_THRESHOLD}))::float8 / count(*) AS share,
           count(*) AS total
    FROM "TaskScore" WHERE "runId" = ${run.id}
  `;

  const [scatter, groups, deciles, top20, allScores, mostWorkersAtHigh, widestCI] = await Promise.all([
    prisma.$queryRaw<Array<{ x: number; y: number; size: number | null; label: string }>>`
      SELECT s.substitution::float8 AS x, s.augmentation::float8 AS y,
             o.employment::float8 AS size, o.title AS label
      FROM "OccupationScore" s JOIN "Occupation" o ON o.code = s."occupationCode"
      WHERE s."runId" = ${run.id}
    `,
    prisma.$queryRaw<Array<{ prefix: string; sub: number; n: number; emp: number }>>`
      SELECT left(s."occupationCode", 2) AS prefix,
             (sum(s.substitution * COALESCE(o.employment, 0)) / NULLIF(sum(COALESCE(o.employment, 0)), 0))::float8 AS sub,
             count(*)::int AS n,
             sum(COALESCE(o.employment, 0))::float8 AS emp
      FROM "OccupationScore" s JOIN "Occupation" o ON o.code = s."occupationCode"
      WHERE s."runId" = ${run.id}
      GROUP BY 1 HAVING sum(COALESCE(o.employment, 0)) > 0
      ORDER BY 2 DESC
    `,
    prisma.$queryRaw<Array<{ decile: number; sub: number; lo: number; hi: number }>>`
      WITH d AS (
        SELECT s.substitution, o.employment::float8 AS emp, o."medianWageCents",
               ntile(10) OVER (ORDER BY o."medianWageCents") AS decile
        FROM "OccupationScore" s JOIN "Occupation" o ON o.code = s."occupationCode"
        WHERE s."runId" = ${run.id} AND o."medianWageCents" IS NOT NULL AND o.employment IS NOT NULL
      )
      SELECT decile::int, (sum(substitution * emp) / sum(emp))::float8 AS sub,
             min("medianWageCents")::float8 AS lo, max("medianWageCents")::float8 AS hi
      FROM d GROUP BY decile ORDER BY decile
    `,
    prisma.occupationScore.findMany({
      where: { runId: run.id },
      orderBy: { substitution: "desc" },
      take: 20,
      include: {
        occupation: { select: { code: true, title: true, medianWageCents: true, employment: true } },
      },
    }),
    prisma.occupationScore.findMany({
      where: { runId: run.id },
      select: { substitution: true },
    }),
    prisma.$queryRaw<Array<{ code: string; title: string; employment: number; sub: number }>>`
      SELECT o.code, o.title, o.employment::float8 AS employment, s.substitution::float8 AS sub
      FROM "OccupationScore" s JOIN "Occupation" o ON o.code = s."occupationCode"
      WHERE s."runId" = ${run.id} AND s.substitution >= 60 AND o.employment IS NOT NULL
      ORDER BY o.employment DESC LIMIT 6
    `,
    prisma.$queryRaw<Array<{ code: string; title: string; width: number; sub: number }>>`
      SELECT o.code, o.title, (s."substitutionHigh" - s."substitutionLow")::float8 AS width,
             s.substitution::float8 AS sub
      FROM "OccupationScore" s JOIN "Occupation" o ON o.code = s."occupationCode"
      WHERE s."runId" = ${run.id}
      ORDER BY 3 DESC LIMIT 6
    `,
  ]);

  return { run, agg, taskShare, scatter, groups, deciles, top20, allScores, mostWorkersAtHigh, widestCI };
}

function toBins(values: number[], binCount = 20): number[] {
  const bins = Array.from({ length: binCount }, () => 0);
  for (const value of values) {
    bins[Math.min(binCount - 1, Math.floor((value / 100) * binCount))] += 1;
  }
  return bins;
}

const wageShort = (cents: number): string => `$${Math.round(cents / 100_000)}k`;

function Tile({
  label,
  value,
  detail,
}: {
  label: string;
  value: string;
  detail: string;
}): JSX.Element {
  return (
    <div className="card card-hover p-5">
      <p className="text-sm font-medium text-[var(--ink-2)]">{label}</p>
      <p className="mt-1 text-3xl font-semibold">{value}</p>
      <p className="mt-2 text-xs leading-relaxed text-[var(--muted)]">{detail}</p>
    </div>
  );
}

export default async function InsightsPage(): Promise<JSX.Element> {
  const data = await loadInsights();
  if (!data) {
    return (
      <main className="mx-auto max-w-4xl px-4 py-14 sm:px-6">
        <h1 className="text-3xl font-bold tracking-tight">Insights</h1>
        <p className="mt-4 text-[var(--ink-2)]">No score run published yet.</p>
      </main>
    );
  }
  const { run, agg, taskShare, scatter, groups, deciles, top20, allScores, mostWorkersAtHigh, widestCI } = data;
  const atRiskShare = (agg.wage_at_risk / agg.wage_bill) * 100;

  return (
    <main className="hero-wash mx-auto max-w-4xl px-4 py-10 sm:px-6 sm:py-14">
      <p className="text-xs font-semibold uppercase tracking-[0.14em] text-[var(--muted)]">
        Insights · run {run.indexVersion} · {run.createdAt.toISOString().slice(0, 10)}
      </p>
      <h1 className="mt-2 text-3xl font-bold tracking-tight sm:text-4xl">
        What the index says, in aggregate
      </h1>
      <p className="mt-3 max-w-2xl text-[var(--ink-2)]">
        Employment- and wage-weighted headline metrics across {agg.n.toLocaleString("en-US")}{" "}
        scored occupations covering {millions(agg.total_emp)} U.S. workers (BLS OEWS wages and
        employment). Pressure, not prophecy — see how to read this below.
      </p>

      {/* Hero figure */}
      <div className="card mt-8 p-6 sm:p-8">
        <p className="text-sm font-medium text-[var(--ink-2)]">
          Share of the U.S. wage bill under substitution pressure
        </p>
        <p className="mt-2 text-6xl font-bold tracking-tight sm:text-7xl">
          {atRiskShare.toFixed(0)}
          <span className="text-3xl font-semibold text-[var(--ink-2)]">%</span>
        </p>
        <p className="mt-2 text-sm text-[var(--muted)]">
          ≈ {money(agg.wage_at_risk)} of {money(agg.wage_bill)} in annual median wages,
          substitution-score-weighted across occupations. This measures how much paid work sits in
          exposed tasks — not wages that will disappear.
        </p>
      </div>

      <div className="mt-4 grid grid-cols-2 gap-4 lg:grid-cols-3">
        <Tile
          label="Employment-weighted substitution"
          value={agg.emp_weighted_sub.toFixed(0)}
          detail="Average substitution score experienced by the typical U.S. worker (0–100)."
        />
        <Tile
          label="Employment-weighted augmentation"
          value={agg.emp_weighted_aug.toFixed(0)}
          detail="The counterpoint: how much AI assists the typical worker's tasks without replacing them."
        />
        <Tile
          label="Workers in high-pressure occupations"
          value={millions(agg.emp_high_sub)}
          detail={`Employed in occupations with substitution ≥ ${HIGH_EXPOSURE_THRESHOLD} — ${((agg.emp_high_sub / agg.total_emp) * 100).toFixed(1)}% of covered employment.`}
        />
        <Tile
          label="Workers in high-augmentation occupations"
          value={millions(agg.emp_high_aug)}
          detail={`Employed in occupations with augmentation ≥ ${HIGH_EXPOSURE_THRESHOLD} — ${((agg.emp_high_aug / agg.total_emp) * 100).toFixed(1)}% of covered employment.`}
        />
        <Tile
          label="Highly exposed tasks"
          value={`${(taskShare.share * 100).toFixed(1)}%`}
          detail={`Of ${Number(taskShare.total).toLocaleString("en-US")} rated O*NET tasks, the share scoring ≥ ${HIGH_EXPOSURE_THRESHOLD} on substitution.`}
        />
        <Tile
          label="Average confidence-interval width"
          value={`±${(agg.avg_ci_width / 2).toFixed(1)}`}
          detail="Mean half-width of the substitution CI from rater disagreement — the index's own uncertainty, stated plainly."
        />
      </div>

      {/* The signature chart: substitution × augmentation */}
      <section className="mt-12">
        <h2 className="text-xl font-semibold tracking-tight">
          Substitution × augmentation — every occupation
        </h2>
        <p className="mt-1 max-w-2xl text-sm text-[var(--ink-2)]">
          The core claim of the methodology in one picture: replacement and assistance are
          different axes. Each dot is one of {scatter.length.toLocaleString("en-US")} occupations;
          dot area reflects U.S. employment. Hover a dot for its name.
        </p>
        <div className="card mt-4 p-4 sm:p-6">
          <ScatterPlot points={scatter} />
        </div>
      </section>

      <section className="mt-10 grid gap-4 lg:grid-cols-2">
        <div className="card min-w-0 p-5 sm:p-6">
          <h2 className="text-lg font-semibold tracking-tight">The shape of the index</h2>
          <p className="mt-1 text-sm text-[var(--ink-2)]">
            Distribution of all {allScores.length.toLocaleString("en-US")} occupation substitution
            scores; the marker is the employment-weighted mean.
          </p>
          <div className="mt-4">
            <DistributionChart
              bins={toBins(allScores.map((s) => s.substitution))}
              marker={agg.emp_weighted_sub}
              markerLabel={`workers' mean · ${agg.emp_weighted_sub.toFixed(0)}`}
            />
          </div>
        </div>
        <div className="card min-w-0 p-5 sm:p-6">
          <h2 className="text-lg font-semibold tracking-tight">Pressure by wage decile</h2>
          <p className="mt-1 text-sm text-[var(--ink-2)]">
            Employment-weighted mean substitution per median-wage decile — where on the pay scale
            the pressure sits.
          </p>
          <div className="mt-4">
            <DecileColumns
              columns={deciles.map((d) => ({
                label: `${wageShort(d.lo)}–${wageShort(d.hi)}`,
                value: d.sub,
              }))}
            />
          </div>
        </div>
      </section>

      <section className="mt-10">
        <h2 className="text-xl font-semibold tracking-tight">By occupation group</h2>
        <p className="mt-1 text-sm text-[var(--ink-2)]">
          Employment-weighted mean substitution per SOC major group.
        </p>
        <div className="card mt-4 overflow-hidden">
          {groups.map((group) => (
            <Link
              key={group.prefix}
              href={`/occupations#g${group.prefix}`}
              className="block border-b border-[var(--grid)] px-4 py-3 last:border-b-0 hover:bg-[var(--wash)] sm:px-5"
            >
              <div className="flex items-baseline gap-3">
                <span className="min-w-0 truncate text-sm">
                  {SOC_MAJOR_GROUPS[group.prefix] ?? group.prefix}
                </span>
                <span className="ml-auto shrink-0 text-xs text-[var(--muted)]">
                  {group.n} occ. · {millions(group.emp)} workers
                </span>
                <span className="w-8 shrink-0 text-right text-sm font-semibold tabular-nums">
                  {group.sub.toFixed(0)}
                </span>
              </div>
              <div aria-hidden="true" className="mt-1.5 h-[6px] rounded-r-[3px] bg-[var(--seq-track)]">
                <div
                  className="h-full rounded-r-[3px] bg-[var(--seq)]"
                  style={{ width: `${Math.min(100, group.sub)}%` }}
                />
              </div>
            </Link>
          ))}
        </div>
      </section>

      <section className="mt-10">
        <div className="flex flex-wrap items-baseline justify-between gap-2">
          <h2 className="text-xl font-semibold tracking-tight">Top 20 by substitution</h2>
          <Link href="/ranking" className="text-xs text-[var(--muted)] underline hover:text-[var(--ink)]">
            full index →
          </Link>
        </div>
        <div className="card mt-4 overflow-x-auto">
          <table className="w-full min-w-[640px] text-left text-sm">
            <caption className="sr-only">Top 20 occupations by substitution score</caption>
            <thead className="border-b border-[var(--grid)] text-xs uppercase tracking-wide text-[var(--muted)]">
              <tr>
                <th className="px-4 py-3 font-medium">#</th>
                <th className="px-4 py-3 font-medium">Occupation</th>
                <th className="px-4 py-3 font-medium">Substitution (CI)</th>
                <th className="px-4 py-3 font-medium">Exposure</th>
                <th className="px-4 py-3 font-medium">Augment.</th>
                <th className="px-4 py-3 font-medium">Median wage</th>
                <th className="px-4 py-3 font-medium">Employed</th>
              </tr>
            </thead>
            <tbody className="tabular-nums">
              {top20.map((row, index) => (
                <tr key={row.occupationCode} className="border-b border-[var(--grid)] last:border-b-0 hover:bg-[var(--wash)]">
                  <td className="px-4 py-2.5 text-[var(--muted)]">{index + 1}</td>
                  <td className="max-w-xs px-4 py-2.5">
                    <Link href={`/occupations/${row.occupationCode}`} className="hover:underline">
                      {row.occupation.title}
                    </Link>
                  </td>
                  <td className="px-4 py-2.5 font-semibold">
                    {row.substitution.toFixed(1)}
                    <span className="ml-1 font-normal text-[var(--muted)]">
                      ({row.substitutionLow.toFixed(0)}–{row.substitutionHigh.toFixed(0)})
                    </span>
                  </td>
                  <td className="px-4 py-2.5">{row.exposure.toFixed(0)}</td>
                  <td className="px-4 py-2.5">{row.augmentation.toFixed(0)}</td>
                  <td className="px-4 py-2.5">
                    {row.occupation.medianWageCents != null
                      ? `$${Math.round(row.occupation.medianWageCents / 100).toLocaleString("en-US")}`
                      : "—"}
                  </td>
                  <td className="px-4 py-2.5">
                    {row.occupation.employment != null
                      ? row.occupation.employment.toLocaleString("en-US")
                      : "—"}
                  </td>
                </tr>
              ))}
            </tbody>
          </table>
        </div>
      </section>

      <section className="mt-12 grid gap-6 lg:grid-cols-2">
        <div className="min-w-0">
          <h2 className="text-lg font-semibold tracking-tight">
            Most workers in high-pressure occupations
          </h2>
          <p className="mt-1 text-sm text-[var(--ink-2)]">
            Substitution ≥ 60, ordered by U.S. employment.
          </p>
          <ul className="card mt-3 overflow-hidden">
            {mostWorkersAtHigh.map((row) => (
              <li key={row.code} className="border-b border-[var(--grid)] last:border-b-0">
                <Link
                  href={`/occupations/${row.code}`}
                  className="flex items-baseline gap-3 px-4 py-3 hover:bg-[var(--wash)]"
                >
                  <span className="min-w-0 truncate text-sm">{row.title}</span>
                  <span className="ml-auto shrink-0 text-xs text-[var(--muted)]">
                    {millions(row.employment)} workers
                  </span>
                  <span className="shrink-0 text-sm font-semibold tabular-nums">
                    {row.sub.toFixed(0)}
                  </span>
                </Link>
              </li>
            ))}
          </ul>
        </div>
        <div className="min-w-0">
          <h2 className="text-lg font-semibold tracking-tight">Where the panel disagrees most</h2>
          <p className="mt-1 text-sm text-[var(--ink-2)]">
            Widest confidence intervals — scores to hold loosely, and first in line for expert
            review.
          </p>
          <ul className="card mt-3 overflow-hidden">
            {widestCI.map((row) => (
              <li key={row.code} className="border-b border-[var(--grid)] last:border-b-0">
                <Link
                  href={`/occupations/${row.code}`}
                  className="flex items-baseline gap-3 px-4 py-3 hover:bg-[var(--wash)]"
                >
                  <span className="min-w-0 truncate text-sm">{row.title}</span>
                  <span className="ml-auto shrink-0 text-xs text-[var(--muted)]">
                    ±{(row.width / 2).toFixed(0)}
                  </span>
                  <span className="shrink-0 text-sm font-semibold tabular-nums">
                    {row.sub.toFixed(0)}
                  </span>
                </Link>
              </li>
            ))}
          </ul>
        </div>
      </section>

      <section className="card mt-12 p-6">
        <h2 className="font-semibold">How to read these numbers</h2>
        <p className="mt-2 text-sm leading-relaxed text-[var(--ink-2)]">
          "Wage bill under pressure" weights every occupation's wages by its substitution score —
          it measures where paid work overlaps with what AI can plausibly take over, not a payroll
          forecast. Realized effects so far are concentrated and cohort-specific, and
          augmentation-weighted usage still dominates. Metrics cover the{" "}
          {agg.n.toLocaleString("en-US")} scored occupations with BLS wage and employment data;
          methodology and formulas are public on the{" "}
          <Link href="/methodology" className="underline">
            methodology page
          </Link>
          .
        </p>
      </section>
    </main>
  );
}