import { db } from "@/lib/db";
import { users, tips, payments } from "@/lib/db/schema";
import { desc } from "drizzle-orm";
import { RevenueBarChart, BreakdownPieChart } from "@/components/admin/DashboardCharts";

// realAmount is only ever set once Stripe actually confirms the charge —
// falls back to the pledged amount only for old rows created before this
// field existed (where realAmount is null but the tip did genuinely go
// through at the time).
function tipAmount(t: { amount: number; realAmount: number | null }) {
  return t.realAmount ?? t.amount;
}

export default async function AdminFundingPage() {
  const allUsers = await db.select().from(users);
  const allTips = await db.select().from(tips).orderBy(desc(tips.createdAt));
  const allPayments = await db.select().from(payments).orderBy(desc(payments.createdAt));

  const totalMembers = allUsers.length;
  const paidMembers = allUsers.filter((u) => u.tier !== "free").length;

  // ----- Separate: registered members vs guest/anonymous tips -----
  const memberTips = allTips.filter((t) => !!t.userId);
  const guestTips = allTips.filter((t) => !t.userId);

  const confirmedMemberTips = memberTips.filter((t) => t.status === "paid" || t.status === "active");
  const confirmedGuestTips = guestTips.filter((t) => t.status === "paid" || t.status === "active");

  // ----- Separate: first-time subscriptions vs recurring renewals -----
  const firstSubscriptions = allPayments.filter((p) => p.type === "subscription" && !p.isRenewal && p.status === "paid");
  const renewalSubscriptions = allPayments.filter((p) => p.type === "subscription" && p.isRenewal && p.status === "paid");
  const purchases = allPayments.filter((p) => p.type === "purchase" && p.status === "paid");

  // ----- Failed transactions (from webhook-tracked failures) -----
  const failedPayments = allPayments.filter((p) => p.status === "failed");
  const failedTips = allTips.filter((t) => t.status === "failed");
  const allFailed = [
    ...failedPayments.map((p) => ({ id: p.id, name: "-", description: p.description, amount: p.amount, reason: p.failureReason, date: p.createdAt })),
    ...failedTips.map((t) => ({ id: t.id, name: t.name, description: "Support the Creators", amount: t.amount, reason: t.failureReason, date: t.createdAt })),
  ].sort((a, b) => new Date(b.date || 0).getTime() - new Date(a.date || 0).getTime());

  const confirmedTipsRaised = [...confirmedMemberTips, ...confirmedGuestTips].reduce((s, t) => s + tipAmount(t), 0);
  const confirmedSubRevenue = [...firstSubscriptions, ...renewalSubscriptions].reduce((s, p) => s + p.amount, 0);
  const confirmedPurchaseRevenue = purchases.reduce((s, p) => s + p.amount, 0);
  const totalConfirmed = confirmedTipsRaised + confirmedSubRevenue + confirmedPurchaseRevenue;

  const confirmedPaymentsAll = allPayments.filter((r) => r.status === "paid");
  const confirmedTipsAll = allTips.filter((r) => r.status === "paid" || r.status === "active");
  const lowAccountTotal =
    confirmedPaymentsAll.filter((r) => r.stripeAccount !== "high").reduce((s, r) => s + r.amount, 0) +
    confirmedTipsAll.filter((r) => r.stripeAccount !== "high").reduce((s, r) => s + tipAmount(r), 0);
  const highAccountTotal =
    confirmedPaymentsAll.filter((r) => r.stripeAccount === "high").reduce((s, r) => s + r.amount, 0) +
    confirmedTipsAll.filter((r) => r.stripeAccount === "high").reduce((s, r) => s + tipAmount(r), 0);

  const firstMember = [...allUsers].sort((a, b) => new Date(a.createdAt || 0).getTime() - new Date(b.createdAt || 0).getTime())[0];

  const stats = [
    { label: "Total Members", value: totalMembers, color: "text-white" },
    { label: "Paid/Upgraded Members", value: paidMembers, color: "text-white" },
    { label: "First-Time Subscriptions", value: firstSubscriptions.length, color: "text-blue-400" },
    { label: "Recurring Renewals", value: renewalSubscriptions.length, color: "text-purple-400" },
    { label: "Shop/PPV Purchases", value: purchases.length, color: "text-emerald-400" },
    { label: "Registered Member Tips", value: confirmedMemberTips.length, color: "text-amber-400" },
    { label: "Guest/Anonymous Tips", value: confirmedGuestTips.length, color: "text-neutral-300" },
    { label: "Failed Transactions", value: allFailed.length, color: "text-red-400" },
  ];

  const breakdownData = [
    { name: "Subscriptions", value: confirmedSubRevenue },
    { name: "Tips", value: confirmedTipsRaised },
    { name: "Shop/PPV", value: confirmedPurchaseRevenue },
  ];

  const barData = [
    { name: "First Subs", amount: firstSubscriptions.reduce((s, p) => s + p.amount, 0) },
    { name: "Renewals", amount: renewalSubscriptions.reduce((s, p) => s + p.amount, 0) },
    { name: "Member Tips", amount: confirmedMemberTips.reduce((s, t) => s + tipAmount(t), 0) },
    { name: "Guest Tips", amount: confirmedGuestTips.reduce((s, t) => s + tipAmount(t), 0) },
    { name: "Shop/PPV", amount: confirmedPurchaseRevenue },
  ];

  return (
    <div>
      <h2 className="text-lg font-semibold mb-1">Funding Dashboard</h2>
      <p className="text-xs text-neutral-500 mb-4">
        "Confirmed" totals only count Stripe-verified paid transactions. If the Stripe webhook is
        configured (Admin Docs), failed/expired payments and recurring renewals are tracked automatically.
      </p>

      <div className="grid grid-cols-2 md:grid-cols-4 gap-4 mb-6">
        {stats.map((s) => (
          <div key={s.label} className="card p-4">
            <p className="text-neutral-500 text-xs mb-1">{s.label}</p>
            <p className={`text-xl font-bold ${s.color}`}>{s.value}</p>
          </div>
        ))}
      </div>

      <div className="card p-4 mb-6 bg-gradient-to-br from-red-950/30 to-transparent border-red-900/40">
        <p className="text-neutral-400 text-xs mb-1">Total Confirmed Revenue</p>
        <p className="text-3xl font-bold text-white">${totalConfirmed.toFixed(2)}</p>
      </div>

      {/* Charts */}
      <div className="grid md:grid-cols-2 gap-4 mb-8">
        <div className="card p-5">
          <h3 className="font-semibold mb-3 text-sm">Revenue by Source</h3>
          <RevenueBarChart data={barData} />
        </div>
        <div className="card p-5">
          <h3 className="font-semibold mb-3 text-sm">Revenue Breakdown</h3>
          <BreakdownPieChart data={breakdownData} />
        </div>
      </div>

      <div className="grid md:grid-cols-2 gap-4 mb-8">
        <div className="card p-4">
          <p className="text-neutral-500 text-xs mb-2">Revenue by Stripe Account</p>
          <div className="flex justify-between text-sm mb-1">
            <span>Low account (below threshold)</span>
            <span className="font-bold">${lowAccountTotal.toFixed(2)}</span>
          </div>
          <div className="flex justify-between text-sm">
            <span>High account (above threshold)</span>
            <span className="font-bold">${highAccountTotal.toFixed(2)}</span>
          </div>
        </div>
        <div className="card p-4">
          <p className="text-neutral-500 text-xs mb-2">First Member Registered</p>
          {firstMember ? (
            <>
              <p className="font-semibold">{firstMember.name} ({firstMember.email})</p>
              <p className="text-xs text-neutral-500 mt-1">
                {firstMember.createdAt ? new Date(firstMember.createdAt).toLocaleDateString() : "—"}
              </p>
            </>
          ) : (
            <p className="text-neutral-500 text-sm">No members yet.</p>
          )}
        </div>
      </div>

      {/* Registered vs Guest tips, side by side */}
      <div className="grid md:grid-cols-2 gap-4 mb-8">
        <div className="card overflow-hidden">
          <div className="p-4 border-b border-neutral-800 bg-amber-950/20">
            <h3 className="font-semibold text-amber-400">Registered Member Supporters ({memberTips.length})</h3>
          </div>
          <div className="max-h-64 overflow-y-auto">
            {memberTips.length === 0 ? (
              <p className="p-4 text-neutral-500 text-sm">None yet.</p>
            ) : (
              memberTips.map((t) => (
                <div key={t.id} className="p-3 border-b border-neutral-900 flex justify-between text-sm">
                  <span>{t.name}</span>
                  <span className="font-semibold">${tipAmount(t).toFixed(2)}</span>
                </div>
              ))
            )}
          </div>
        </div>
        <div className="card overflow-hidden">
          <div className="p-4 border-b border-neutral-800 bg-neutral-900">
            <h3 className="font-semibold text-neutral-300">Guest / Anonymous Supporters ({guestTips.length})</h3>
          </div>
          <div className="max-h-64 overflow-y-auto">
            {guestTips.length === 0 ? (
              <p className="p-4 text-neutral-500 text-sm">None yet.</p>
            ) : (
              guestTips.map((t) => (
                <div key={t.id} className="p-3 border-b border-neutral-900 flex justify-between text-sm">
                  <span>{t.name}</span>
                  <span className="font-semibold">${tipAmount(t).toFixed(2)}</span>
                </div>
              ))
            )}
          </div>
        </div>
      </div>

      {/* Failed transactions */}
      <div className="card overflow-hidden mb-8">
        <div className="p-4 border-b border-neutral-800 bg-red-950/20">
          <h3 className="font-semibold text-red-400">Failed Transactions ({allFailed.length})</h3>
          <p className="text-xs text-neutral-500 mt-1">
            Requires the Stripe webhook to be configured — see Admin Docs. Shows the filled-in data
            even though the payment didn't complete, so you can follow up.
          </p>
        </div>
        <table className="w-full text-sm">
          <thead className="bg-neutral-900 text-neutral-500 text-left">
            <tr>
              <th className="p-3">Name</th>
              <th className="p-3">Description</th>
              <th className="p-3">Amount</th>
              <th className="p-3">Reason</th>
              <th className="p-3">Date</th>
            </tr>
          </thead>
          <tbody>
            {allFailed.map((f) => (
              <tr key={f.id} className="border-t border-neutral-800">
                <td className="p-3">{f.name}</td>
                <td className="p-3">{f.description}</td>
                <td className="p-3">${f.amount.toFixed(2)}</td>
                <td className="p-3 text-neutral-400 max-w-xs truncate">{f.reason || "—"}</td>
                <td className="p-3 text-neutral-400">{f.date ? new Date(f.date).toLocaleDateString() : "—"}</td>
              </tr>
            ))}
            {allFailed.length === 0 && (
              <tr><td colSpan={5} className="p-6 text-center text-neutral-500">No failed transactions recorded.</td></tr>
            )}
          </tbody>
        </table>
      </div>
    </div>
  );
}
