import { all, first } from "./db";
import { allSites } from "./sites/store";

const DAY_MS = 24 * 60 * 60 * 1000;

type Count = { count: number };

async function count(sql: string, ...params: (string | number)[]): Promise<number> {
  return (await first<Count>(sql, ...params))?.count ?? 0;
}

export type AdminOrgRow = { id: string; name: string; members: number; repos: number; sites: number; reports: number };
export type AdminUserRow = {
  id: string;
  name: string;
  email: string;
  verified: boolean;
  isAdmin: boolean;
  repos: number;
  reports: number;
  createdAt: number;
};

/**
 * Site-wide counters for the admin dashboard: indexed aggregates over the D1 directory, plus the sites, which
 * live in their repositories' objects and are gathered with one fan-out (the list is small).
 */
export async function adminOverview() {
  const now = Date.now();
  const week = now - 7 * DAY_MS;
  const month = now - 30 * DAY_MS;
  const allSiteRows = await allSites();
  const siteCount = (keep: (site: (typeof allSiteRows)[number]) => boolean) => allSiteRows.filter(keep).length;
  const sites = allSiteRows.length;
  const activeSites = siteCount((site) => site.disabled_reason === null && site.active_deployment_id !== null);
  const pausedSites = siteCount((site) => site.paused_at !== null);
  const disabledSites = siteCount((site) => site.disabled_reason !== null && site.paused_at === null);
  const scratchSites = siteCount((site) => site.scratch === 1 && (site.expires_at === null || site.expires_at > now));
  const autoPausedWeek = siteCount((site) => site.paused_at !== null && site.paused_at > week);
  const siteOwner = new Map(allSiteRows.map((site) => [site.id, site.owner_id]));
  const sitesByOwner = new Map<string, number>();
  for (const site of allSiteRows) {
    if (site.owner_id) sitesByOwner.set(site.owner_id, (sitesByOwner.get(site.owner_id) ?? 0) + 1);
  }
  const openSiteReports = await all<{ site_id: string }>(
    "SELECT site_id FROM abuse_reports WHERE status = 'open' AND site_id IS NOT NULL",
  );
  const reportsByOwner = new Map<string, number>();
  for (const report of openSiteReports) {
    const owner = siteOwner.get(report.site_id);
    if (owner) reportsByOwner.set(owner, (reportsByOwner.get(owner) ?? 0) + 1);
  }
  const [
    users,
    verifiedUsers,
    newUsersWeek,
    newUsersMonth,
    admins,
    twoFactorUsers,
    orgs,
    newOrgsMonth,
    repos,
    publicRepos,
    openReports,
    reportsWeek,
  ] = await Promise.all([
    count("SELECT COUNT(*) AS count FROM users"),
    count("SELECT COUNT(*) AS count FROM users WHERE email_verified_at IS NOT NULL"),
    count("SELECT COUNT(*) AS count FROM accounts WHERE kind = 'user' AND created_at > ?", week),
    count("SELECT COUNT(*) AS count FROM accounts WHERE kind = 'user' AND created_at > ?", month),
    count("SELECT COUNT(*) AS count FROM users WHERE is_admin = 1"),
    count("SELECT COUNT(*) AS count FROM users WHERE totp_enabled_at IS NOT NULL"),
    count("SELECT COUNT(*) AS count FROM accounts WHERE kind = 'org'"),
    count("SELECT COUNT(*) AS count FROM accounts WHERE kind = 'org' AND created_at > ?", month),
    count("SELECT COUNT(*) AS count FROM repos"),
    count("SELECT COUNT(*) AS count FROM repos WHERE visibility = 'public'"),
    count("SELECT COUNT(*) AS count FROM abuse_reports WHERE status = 'open'"),
    count("SELECT COUNT(*) AS count FROM abuse_reports WHERE created_at > ?", week),
  ]);
  const [orgRows, recentUsers, reportsByCategory] = await Promise.all([
    all<Omit<AdminOrgRow, "sites" | "reports">>(
      `SELECT a.id, a.name,
              (SELECT COUNT(*) FROM org_members m WHERE m.org_id = a.id) AS members,
              (SELECT COUNT(*) FROM repos r WHERE r.owner_id = a.id) AS repos
       FROM accounts a WHERE a.kind = 'org'`,
    ),
    all<{
      id: string;
      name: string;
      email: string;
      email_verified_at: number | null;
      is_admin: number;
      repos: number;
      reports: number;
      created_at: number;
    }>(
      `SELECT a.id, a.name, u.email, u.email_verified_at, u.is_admin, a.created_at,
              (SELECT COUNT(*) FROM repos r WHERE r.owner_id = a.id) AS repos, 0 AS reports
       FROM accounts a JOIN users u ON u.account_id = a.id
       ORDER BY a.created_at DESC LIMIT 25`,
    ),
    all<{ category: string; count: number }>(
      "SELECT category, COUNT(*) AS count FROM abuse_reports WHERE created_at > ? GROUP BY category ORDER BY count DESC",
      month,
    ),
  ]);
  const topOrgs: AdminOrgRow[] = orgRows
    .map((org) => ({ ...org, sites: sitesByOwner.get(org.id) ?? 0, reports: reportsByOwner.get(org.id) ?? 0 }))
    .sort((left, right) => right.reports - left.reports || right.members - left.members || right.repos - left.repos)
    .slice(0, 25);
  return {
    users: { total: users, verified: verifiedUsers, newWeek: newUsersWeek, newMonth: newUsersMonth, admins },
    security: { twoFactorUsers },
    orgs: { total: orgs, newMonth: newOrgsMonth },
    repos: { total: repos, public: publicRepos, private: repos - publicRepos },
    sites: { total: sites, active: activeSites, paused: pausedSites, disabled: disabledSites, scratch: scratchSites },
    abuse: { open: openReports, week: reportsWeek, autoPausedWeek, byCategory: reportsByCategory },
    topOrgs,
    recentUsers: recentUsers.map((row): AdminUserRow => ({
      id: row.id,
      name: row.name,
      email: row.email,
      verified: row.email_verified_at !== null,
      isAdmin: row.is_admin === 1,
      repos: row.repos,
      reports: reportsByOwner.get(row.id) ?? 0,
      createdAt: row.created_at,
    })),
  };
}