import { createFileRoute } from "@tanstack/react-router";
import { AppShell, PageHeader } from "@/components/app-shell";
import { Badge } from "@/components/ui/badge";
import { Card, CardContent } from "@/components/ui/card";
import { SCHEMA_GROUPS, TABLE_PURPOSE } from "@/lib/schema-contract";
import { getSqlSchema } from "@/lib/server/api-academy";
import { getShellMeta } from "@/lib/server/api-core";

export const Route = createFileRoute("/admin/schema")({
  loader: async () => {
    const [meta, schema] = await Promise.all([getShellMeta(), getSqlSchema()]);
    return { meta, schema };
  },
  component: SchemaPage,
});

function SchemaPage() {
  const { meta, schema } = Route.useLoaderData();
  const grouped = new Map<string, typeof schema.columns>();
  for (const col of schema.columns) {
    const list = grouped.get(col.table_name) ?? [];
    list.push(col);
    grouped.set(col.table_name, list);
  }
  const known = new Set<string>(SCHEMA_GROUPS.flatMap((g) => [...g.tables]));
  const leftover = schema.tables.filter((t) => !known.has(t));

  return (
    <AppShell hold={meta.hold} unread={meta.unread} noticeCount={meta.noticeCount} inboxUnread={meta.inboxUnread}>
      <PageHeader
        kicker="Super Admin"
        title="Database"
        description="The contract between this app and Postgres, then the live columns. Academy tables sit beside Grant Scout. Better Auth session tables stay hidden."
      />
      <div className="mb-8 grid gap-3 sm:grid-cols-2 lg:grid-cols-3">
        {SCHEMA_GROUPS.map((group) => (
          <Card key={group.id}>
            <CardContent className="p-4">
              <p className="text-[11px] font-medium tracking-[0.16em] text-primary uppercase">{group.title}</p>
              <p className="mt-2 text-sm text-muted-foreground">{group.blurb}</p>
              <p className="mt-2 text-xs text-muted-foreground">{group.tables.length} tables · {group.owner}</p>
            </CardContent>
          </Card>
        ))}
      </div>
      <p className="mb-6 text-sm text-muted-foreground">
        {schema.tables.length} tables · {schema.columns.length} columns · grouped by the database contract
      </p>
      <div className="space-y-8">
        {SCHEMA_GROUPS.map((group) => {
          const tables = group.tables.filter((t) => grouped.has(t));
          if (tables.length === 0) return null;
          return (
            <section key={group.id}>
              <div className="mb-3 flex flex-wrap items-end justify-between gap-2">
                <div>
                  <h2 className="font-display text-2xl">{group.title}</h2>
                  <p className="mt-1 max-w-2xl text-sm text-muted-foreground">{group.blurb}</p>
                </div>
                <Badge variant="outline">{tables.length} tables</Badge>
              </div>
              <div className="grid gap-4 md:grid-cols-2">
                {tables.map((table) => {
                  const cols = grouped.get(table) ?? [];
                  return (
                    <Card key={table}>
                      <CardContent className="pt-5">
                        <div className="flex items-baseline justify-between gap-3">
                          <h3 className="font-mono text-sm font-semibold text-primary">{table}</h3>
                          <span className="tabular text-[11px] text-muted-foreground">{cols.length} cols</span>
                        </div>
                        <p className="mt-1 text-xs text-muted-foreground">{TABLE_PURPOSE[table] ?? "Application table."}</p>
                        <ul className="mt-3 space-y-1">
                          {cols.map((c) => (
                            <li key={c.column_name} className="flex justify-between gap-3 font-mono text-xs">
                              <span>{c.column_name}</span>
                              <span className="text-muted-foreground">
                                {c.data_type}
                                {c.is_nullable === "YES" ? "?" : ""}
                              </span>
                            </li>
                          ))}
                        </ul>
                      </CardContent>
                    </Card>
                  );
                })}
              </div>
            </section>
          );
        })}
        {leftover.length > 0 ? (
          <section>
            <h2 className="mb-3 font-display text-2xl">Other</h2>
            <div className="grid gap-4 md:grid-cols-2">
              {leftover.map((table) => {
                const cols = grouped.get(table) ?? [];
                return (
                  <Card key={table}>
                    <CardContent className="pt-5">
                      <h3 className="font-mono text-sm font-semibold text-primary">{table}</h3>
                      <ul className="mt-3 space-y-1">
                        {cols.map((c) => (
                          <li key={c.column_name} className="flex justify-between gap-3 font-mono text-xs">
                            <span>{c.column_name}</span>
                            <span className="text-muted-foreground">
                              {c.data_type}
                              {c.is_nullable === "YES" ? "?" : ""}
                            </span>
                          </li>
                        ))}
                      </ul>
                    </CardContent>
                  </Card>
                );
              })}
            </div>
          </section>
        ) : null}
      </div>
    </AppShell>
  );
}