import type { AdminOverviewResponse } from "@xtablo/shared-types";
import { Hono } from "hono";
import type { BaseEnv } from "../types/app.types.js";

function startOfRecentWindow(days: number) {
  const date = new Date();
  date.setUTCDate(date.getUTCDate() - days);
  return date.toISOString();
}

async function countRows(
  query: PromiseLike<{ count: number | null; error: { message: string } | null }>
) {
  const { count, error } = await query;

  if (error) {
    throw new Error(error.message);
  }

  return count ?? 0;
}

export const getAdminOverviewRouter = () => {
  const adminOverviewRouter = new Hono<BaseEnv>();

  adminOverviewRouter.get("/", async (c) => {
    const supabase = c.get("supabase");
    const sevenDaysAgo = startOfRecentWindow(7);

    try {
      const [
        totalUsers,
        recentUsers,
        totalTablos,
        recentTablos,
        activeAccess,
        adminAccess,
        temporaryUsers,
        inactiveAccess,
      ] = await Promise.all([
        countRows(supabase.from("profiles").select("*", { count: "exact", head: true })),
        countRows(
          supabase
            .from("profiles")
            .select("*", { count: "exact", head: true })
            .gte("created_at", sevenDaysAgo)
        ),
        countRows(
          supabase.from("tablos").select("*", { count: "exact", head: true }).is("deleted_at", null)
        ),
        countRows(
          supabase
            .from("tablos")
            .select("*", { count: "exact", head: true })
            .is("deleted_at", null)
            .gte("created_at", sevenDaysAgo)
        ),
        countRows(
          supabase
            .from("tablo_access")
            .select("*", { count: "exact", head: true })
            .eq("is_active", true)
        ),
        countRows(
          supabase
            .from("tablo_access")
            .select("*", { count: "exact", head: true })
            .eq("is_active", true)
            .eq("is_admin", true)
        ),
        countRows(
          supabase
            .from("profiles")
            .select("*", { count: "exact", head: true })
            .eq("is_temporary", true)
        ),
        countRows(
          supabase
            .from("tablo_access")
            .select("*", { count: "exact", head: true })
            .eq("is_active", false)
        ),
      ]);

      const response: AdminOverviewResponse = {
        alerts: [
          {
            description: `${temporaryUsers} temporary users still exist in production.`,
            id: "temporary-users",
            severity: temporaryUsers > 0 ? "warning" : "info",
            title: "Temporary Accounts",
          },
          {
            description: `${inactiveAccess} tablo access rows are inactive and may need review.`,
            id: "inactive-access",
            severity: inactiveAccess > 10 ? "critical" : "warning",
            title: "Inactive Access Drift",
          },
        ],
        metrics: [
          {
            changeLabel: `+${recentUsers} last 7d`,
            id: "total-users",
            label: "Total Users",
            value: totalUsers.toLocaleString(),
          },
          {
            changeLabel: `+${recentTablos} last 7d`,
            id: "total-tablos",
            label: "Active Tablos",
            value: totalTablos.toLocaleString(),
          },
          {
            changeLabel: `${adminAccess} admin grants`,
            id: "active-access",
            label: "Active Access",
            value: activeAccess.toLocaleString(),
          },
          {
            changeLabel: `${temporaryUsers} temporary`,
            id: "admin-access",
            label: "Admin Grants",
            value: adminAccess.toLocaleString(),
          },
        ],
        shortcuts: [
          { href: "/explorer", id: "profiles", label: "Inspect Users" },
          { href: "/explorer", id: "access", label: "Review Tablo Access" },
          { href: "/analytics", id: "growth", label: "Open Growth Analytics" },
          { href: "/actions", id: "actions", label: "Run Admin Actions" },
        ],
      };

      return c.json(response, 200);
    } catch {
      return c.json({ error: "Failed to load admin overview" }, 500);
    }
  });

  return adminOverviewRouter;
};
