import { TrafficCharts } from "@/components/TrafficCharts";
import { db } from "@/lib/db";
import { courses } from "@/lib/courses";
import { COURSE_PRICE_GHS } from "@/lib/pricing";

export const metadata = { title: "Admin analytics" };

export default async function AdminHome() {
  const users = await db
    .prepare("SELECT id, created_at, trial_ends_at, status, role, agent_until, has_access FROM users")
    .all() as {
    id: string;
    created_at: string;
    trial_ends_at: string;
    status: string;
    role: string;
    agent_until: string | null;
    has_access: number;
  }[];
  const now = Date.now();
  const weekAgo = now - 7 * 24 * 60 * 60 * 1000;
  const unlocks = await db.prepare("SELECT course_slug, COUNT(*) AS n FROM course_unlocks GROUP BY course_slug").all() as {
    course_slug: string;
    n: number;
  }[];
  const pay = await db
    .prepare("SELECT amount, status, created_at, kind FROM payments")
    .all() as { amount: number; status: string; created_at: string; kind: string }[];
  const success = pay.filter((p) => p.status === "success");
  const revenuePesewas = success.reduce((s, p) => s + p.amount, 0);
  const unpaid = users.filter((u) => u.status === "active" && !u.has_access).length;
  const onTrial = users.filter(
    (u) => u.status === "active" && u.has_access && new Date(u.trial_ends_at).getTime() > now
  ).length;
  const expired = users.filter(
    (u) => u.status === "active" && u.has_access && new Date(u.trial_ends_at).getTime() <= now
  ).length;
  const newWeek = users.filter((u) => new Date(u.created_at).getTime() >= weekAgo).length;
  const paidWeek = success.filter((p) => new Date(p.created_at).getTime() >= weekAgo).length;
  const live = users.filter((u) => u.agent_until && new Date(u.agent_until).getTime() > now).length;
  const progressWeek = (
    await db.prepare("SELECT COUNT(*) AS n FROM progress WHERE updated_at >= ?").get(new Date(weekAgo).toISOString()) as {
      n: number;
    }
  ).n;

  const cards = [
    ["Learners", String(users.length)],
    ["Unpaid ₵50", String(unpaid)],
    ["In 3-day window", String(onTrial)],
    ["Window ended", String(expired)],
    ["New (7 days)", String(newWeek)],
    ["Course unlocks", String(unlocks.reduce((s, u) => s + u.n, 0))],
    ["Revenue", `₵${(revenuePesewas / 100).toFixed(0)}`],
    ["Paid (7 days)", String(paidWeek)],
    ["Live coach active", String(live)],
    ["Steps marked (7 days)", String(progressWeek)],
  ];

  return (
    <div className="mx-auto max-w-6xl px-4 py-10">
      <p className="text-xs uppercase tracking-[0.28em] text-terra">Admin</p>
      <h1 className="font-display mt-2 text-4xl text-forest">Analytics</h1>
      <TrafficCharts />
      <h2 className="font-display mt-12 text-2xl text-forest">Academy</h2>
      <div className="mt-6 grid gap-4 sm:grid-cols-2 lg:grid-cols-3">
        {cards.map(([k, v]) => (
          <div key={k} className="rounded-3xl bg-cream p-5">
            <p className="text-xs uppercase tracking-widest text-forest/50">{k}</p>
            <p className="font-display mt-2 text-3xl text-forest">{v}</p>
          </div>
        ))}
      </div>
      <h2 className="font-display mt-12 text-2xl text-forest">Unlocks by course</h2>
      <p className="text-sm text-forest/60">₵{COURSE_PRICE_GHS} each after the 3-day window</p>
      <ul className="mt-4 space-y-2">
        {courses.map((c) => {
          const n = unlocks.find((u) => u.course_slug === c.slug)?.n || 0;
          return (
            <li key={c.slug} className="flex items-center justify-between rounded-2xl bg-cream px-4 py-3 text-sm">
              <span>{c.title}</span>
              <strong>{n}</strong>
            </li>
          );
        })}
      </ul>
    </div>
  );
}