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

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

export default async function AdminSitesPage() {
  const t = await getTranslations("Admin.sites");
  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: sites.id,
      hostname: sites.hostname,
      label: sites.label,
      createdAt: sites.createdAt,
      orgName: organization.name,
      latestScore: sql<number | null>`(
        SELECT ${siteTests.performanceScore} FROM ${siteTests}
        WHERE ${siteTests.siteId} = ${sites.id}
        ORDER BY ${siteTests.createdAt} DESC
        LIMIT 1
      )`,
      testsCount: sql<number>`(
        SELECT COUNT(*)::int FROM ${siteTests} WHERE ${siteTests.siteId} = ${sites.id}
      )`,
    })
    .from(sites)
    .leftJoin(organization, eq(sites.organizationId, organization.id))
    .orderBy(desc(sites.createdAt));

  return (
    <div className="space-y-6">
      <h1 className="text-2xl font-semibold tracking-tight">{t("title")}</h1>
      <Table>
        <TableHeader>
          <TableRow>
            <TableHead>{t("columns.hostname")}</TableHead>
            <TableHead>{t("columns.owner")}</TableHead>
            <TableHead>{t("columns.label")}</TableHead>
            <TableHead className="text-right">{t("columns.latestScore")}</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.hostname}</TableCell>
              <TableCell>{r.orgName ?? "—"}</TableCell>
              <TableCell className="text-muted-foreground">{r.label ?? "—"}</TableCell>
              <TableCell className="text-right tabular-nums">{r.latestScore ?? "—"}</TableCell>
              <TableCell className="text-right tabular-nums">{r.testsCount}</TableCell>
              <TableCell className="text-muted-foreground">
                {fmt.format(new Date(r.createdAt))}
              </TableCell>
            </TableRow>
          ))}
        </TableBody>
      </Table>
    </div>
  );
}