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

import { Badge } from "@/components/ui/badge";
import {
  Table,
  TableBody,
  TableCell,
  TableHead,
  TableHeader,
  TableRow,
} from "@/components/ui/table";
import { db } from "@/lib/db";
import { member, siteTests, sites, user } from "@/lib/db/schema";

export default async function AdminUsersPage() {
  const t = await getTranslations("Admin.users");
  const locale = await getLocale();
  const dateLocale = locale === "fr" ? "fr-FR" : locale === "es" ? "es-ES" : "en-US";
  const fmt = new Intl.DateTimeFormat(dateLocale, { dateStyle: "short", timeStyle: "short" });
  const rows = await db
    .select({
      id: user.id,
      email: user.email,
      name: user.name,
      role: user.role,
      banned: user.banned,
      createdAt: user.createdAt,
      // Postgres "ambiguous column" (42702):
      //   1. drizzle interpolates ${sites.id} as bare "id" inside a raw sql
      //      template, which collides with member.id once we JOIN member.
      //      Fix: alias sites as `s` / siteTests as `st` and qualify the
      //      COUNT(DISTINCT) call.
      //   2. ${user.id} (the outer-scope correlated ref) ALSO renders as
      //      bare "id", colliding with s.id / m.id in the subquery scope.
      //      Fix: use sql.raw to force-qualify as "user"."id" (matches the
      //      outer FROM "user" clause).
      sitesCount: sql<number>`(
        SELECT COUNT(DISTINCT s.id)::int
        FROM ${sites} s
        INNER JOIN ${member} m ON m.organization_id = s.organization_id
        WHERE m.user_id = ${sql.raw(`"user"."id"`)}
      )`,
      testsCount: sql<number>`(
        SELECT COUNT(DISTINCT st.id)::int
        FROM ${siteTests} st
        INNER JOIN ${member} m ON m.organization_id = st.organization_id
        WHERE m.user_id = ${sql.raw(`"user"."id"`)} AND st.organization_id IS NOT NULL
      )`,
    })
    .from(user)
    .orderBy(desc(user.createdAt));

  return (
    <div className="space-y-6">
      <h1 className="text-2xl font-semibold tracking-tight">{t("title")}</h1>
      <Table>
        <TableHeader>
          <TableRow>
            <TableHead>{t("columns.email")}</TableHead>
            <TableHead>{t("columns.name")}</TableHead>
            <TableHead>{t("columns.role")}</TableHead>
            <TableHead>{t("columns.status")}</TableHead>
            <TableHead className="text-right">{t("columns.sites")}</TableHead>
            <TableHead className="text-right">{t("columns.tests")}</TableHead>
            <TableHead>{t("columns.created")}</TableHead>
          </TableRow>
        </TableHeader>
        <TableBody>
          {rows.map((r) => (
            <TableRow key={r.id}>
              <TableCell className="font-medium">{r.email}</TableCell>
              <TableCell>{r.name}</TableCell>
              <TableCell>
                <Badge variant={r.role === "admin" ? "default" : "secondary"}>
                  {t(`roles.${(r.role ?? "user") === "admin" ? "admin" : "user"}`)}
                </Badge>
              </TableCell>
              <TableCell>
                {r.banned ? <Badge variant="destructive">{t("banned")}</Badge> : "—"}
              </TableCell>
              <TableCell className="text-right tabular-nums">{r.sitesCount}</TableCell>
              <TableCell className="text-right tabular-nums">{r.testsCount}</TableCell>
              <TableCell className="text-muted-foreground">{fmt.format(r.createdAt)}</TableCell>
            </TableRow>
          ))}
        </TableBody>
      </Table>
    </div>
  );
}