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

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; every query is a single indexed aggregate over D1. */
export async function adminOverview() {
  const now = Date.now();
  const week = now - 7 * DAY_MS;
  const month = now - 30 * DAY_MS;
  const [
    users,
    verifiedUsers,
    newUsersWeek,
    newUsersMonth,
    admins,
    twoFactorUsers,
    orgs,
    newOrgsMonth,
    repos,
    publicRepos,
    sites,
    activeSites,
    pausedSites,
    disabledSites,
    scratchSites,
    openReports,
    reportsWeek,
    autoPausedWeek,
  ] = 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 sites"),
    count("SELECT COUNT(*) AS count FROM sites WHERE disabled_reason IS NULL AND active_deployment_id IS NOT NULL"),
    count("SELECT COUNT(*) AS count FROM sites WHERE paused_at IS NOT NULL"),
    count("SELECT COUNT(*) AS count FROM sites WHERE disabled_reason IS NOT NULL AND paused_at IS NULL"),
    count("SELECT COUNT(*) AS count FROM sites WHERE scratch = 1 AND (expires_at IS NULL OR expires_at > ?)", now),
    count("SELECT COUNT(*) AS count FROM abuse_reports WHERE status = 'open'"),
    count("SELECT COUNT(*) AS count FROM abuse_reports WHERE created_at > ?", week),
    count("SELECT COUNT(*) AS count FROM sites WHERE paused_at > ?", week),
  ]);
  const [topOrgs, recentUsers, reportsByCategory] = await Promise.all([
    all<AdminOrgRow>(
      `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,
              (SELECT COUNT(*) FROM sites s WHERE s.owner_id = a.id) AS sites,
              (SELECT COUNT(*) FROM abuse_reports ar JOIN sites s ON s.id = ar.site_id
                WHERE s.owner_id = a.id AND ar.status = 'open') AS reports
       FROM accounts a WHERE a.kind = 'org'
       ORDER BY reports DESC, members DESC, repos DESC LIMIT 25`,
    ),
    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,
              (SELECT COUNT(*) FROM abuse_reports ar JOIN sites s ON s.id = ar.site_id
                WHERE s.owner_id = a.id AND ar.status = 'open') 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,
    ),
  ]);
  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: row.reports,
      createdAt: row.created_at,
    })),
  };
}