import { eq, sql } from "drizzle-orm";
import { getLocale, getTranslations } from "next-intl/server";
import Link from "next/link";

import {
  Table,
  TableBody,
  TableCell,
  TableHead,
  TableHeader,
  TableRow,
} from "@/components/ui/table";
import { requireAdmin } from "@/lib/auth-guards";
import { BILLABLE_PLANS } from "@/lib/billing/plans";
import { db } from "@/lib/db";
import { member, organization, orgSubscription, sites, siteTests } from "@/lib/db/schema";

function planMRR(plan: string, interval: string | null): number {
  if (plan === "free" || plan === "enterprise") return 0;
  const cfg = BILLABLE_PLANS[plan as keyof typeof BILLABLE_PLANS];
  if (!cfg) return 0;
  // Annualized MRR: yearly plans / 12.
  return interval === "year" ? Math.round(cfg.prices.eur.year / 12) : cfg.prices.eur.month;
}

export default async function AdminOrgsPage() {
  await requireAdmin();
  const t = await getTranslations("Admin.orgs");
  const locale = await getLocale();
  const dateLocale = locale === "fr" ? "fr-FR" : locale === "es" ? "es-ES" : "en-US";
  const fmt = new Intl.DateTimeFormat(dateLocale, { dateStyle: "short" });

  const rows = await db
    .select({
      id: organization.id,
      name: organization.name,
      slug: organization.slug,
      createdAt: organization.createdAt,
      plan: orgSubscription.plan,
      interval: orgSubscription.interval,
      status: orgSubscription.status,
      stripeCustomerId: orgSubscription.stripeCustomerId,
      membersCount:
        sql<number>`(SELECT COUNT(*)::int FROM ${member} m WHERE m.organization_id = ${organization.id})`.as(
          "members_count",
        ),
      sitesCount:
        sql<number>`(SELECT COUNT(*)::int FROM ${sites} s WHERE s.organization_id = ${organization.id})`.as(
          "sites_count",
        ),
      testsCount:
        sql<number>`(SELECT COUNT(*)::int FROM ${siteTests} st WHERE st.organization_id = ${organization.id})`.as(
          "tests_count",
        ),
    })
    .from(organization)
    .leftJoin(orgSubscription, eq(orgSubscription.organizationId, organization.id))
    .orderBy(organization.createdAt);

  const totalMRR = rows.reduce((sum, r) => sum + planMRR(r.plan ?? "free", r.interval ?? null), 0);

  return (
    <div className="flex flex-col gap-6">
      <header className="flex items-baseline justify-between gap-4">
        <div>
          <h1 className="text-2xl font-semibold tracking-tight">{t("title")}</h1>
          <p className="text-sm text-muted-foreground">{t("description")}</p>
        </div>
        <div className="text-right text-sm">
          <div className="text-xs tracking-wider text-muted-foreground uppercase">
            {t("totalMRR")}
          </div>
          <div className="text-lg font-semibold">€{totalMRR.toLocaleString()}</div>
        </div>
      </header>
      <Table>
        <TableHeader>
          <TableRow>
            <TableHead>{t("columns.org")}</TableHead>
            <TableHead>{t("columns.plan")}</TableHead>
            <TableHead>{t("columns.status")}</TableHead>
            <TableHead className="text-right">{t("columns.mrr")}</TableHead>
            <TableHead className="text-right">{t("columns.members")}</TableHead>
            <TableHead className="text-right">{t("columns.sites")}</TableHead>
            <TableHead className="text-right">{t("columns.tests")}</TableHead>
            <TableHead>{t("columns.created")}</TableHead>
            <TableHead></TableHead>
          </TableRow>
        </TableHeader>
        <TableBody>
          {rows.map((r) => {
            const mrr = planMRR(r.plan ?? "free", r.interval ?? null);
            return (
              <TableRow key={r.id}>
                <TableCell>
                  <Link href={`/admin/orgs/${r.id}`} className="font-medium hover:underline">
                    {r.name}
                  </Link>
                  <div className="text-xs text-muted-foreground">{r.slug}</div>
                </TableCell>
                <TableCell className="capitalize">
                  {r.plan ?? "free"}
                  {r.interval && (
                    <span className="ml-1 text-xs text-muted-foreground">/{r.interval}</span>
                  )}
                </TableCell>
                <TableCell className="capitalize">{r.status ?? "—"}</TableCell>
                <TableCell className="text-right tabular-nums">
                  {mrr > 0 ? `€${mrr}` : "—"}
                </TableCell>
                <TableCell className="text-right tabular-nums">{r.membersCount}</TableCell>
                <TableCell className="text-right tabular-nums">{r.sitesCount}</TableCell>
                <TableCell className="text-right tabular-nums">{r.testsCount}</TableCell>
                <TableCell>{fmt.format(new Date(r.createdAt))}</TableCell>
                <TableCell>
                  <Link
                    href={`/admin/orgs/${r.id}`}
                    className="text-xs text-muted-foreground hover:text-foreground"
                  >
                    {t("view")}
                  </Link>
                </TableCell>
              </TableRow>
            );
          })}
        </TableBody>
      </Table>
    </div>
  );
}