import "server-only";
import type { SQL } from "drizzle-orm";
import { alias } from "drizzle-orm/pg-core";
import {
  getDb,
  users,
  sessions,
  organizations,
  organizationMembers,
  projects,
  apiKeys,
  fetchRequests,
  requestAttempts,
  usageEvents,
  subscriptions,
  billingEvents,
  providerConfigs,
  providerHealth,
  domainProfiles,
  routingMetrics,
  auditLogs,
  abuseEvents,
  featureFlags,
  statusIncidents,
  proxySessions,
  webhooks,
  signupAllowlist,
  eq,
  and,
  or,
  desc,
  asc,
  sql,
  gte,
  lte,
  ilike,
  inArray,
  count,
} from "@fetcha/db";
import { PLAN_LIMITS, PLANS, normalizePlan } from "@fetcha/core";

/**
 * Admin-only data access. Everything here may expose upstream provider names,
 * upstream costs and internal error details — never import from customer-facing code.
 */

// ---------------------------------------------------------------------------
// Helpers
// ---------------------------------------------------------------------------
export type Range = "24h" | "7d" | "30d";
export const RANGES: Range[] = ["24h", "7d", "30d"];

export function parseRange(v: string | string[] | undefined): Range {
  return v === "7d" || v === "30d" ? v : "24h";
}
export function rangeStart(r: Range): Date {
  const ms = r === "24h" ? 86_400_000 : r === "7d" ? 7 * 86_400_000 : 30 * 86_400_000;
  return new Date(Date.now() - ms);
}
export function rangeLabel(r: Range): string {
  return r === "24h" ? "Last 24 hours" : r === "7d" ? "Last 7 days" : "Last 30 days";
}

const num = (e: SQL) => sql<number>`coalesce(${e}, 0)`.mapWith(Number);
const sumCase = (cond: SQL) => num(sql`sum(case when ${cond} then 1 else 0 end)`);
const pct = (a: number, b: number) => (b > 0 ? (a / b) * 100 : null);
const toDate = (v: unknown): Date | null => (v ? new Date(v as string) : null);
const first = (v: string | string[] | undefined): string | undefined => (Array.isArray(v) ? v[0] : v);
export const str = (v: string | string[] | undefined): string => (first(v) ?? "").trim();
export function pageNum(v: string | string[] | undefined): number {
  const n = Number.parseInt(first(v) ?? "1", 10);
  return Number.isFinite(n) && n > 0 ? n : 1;
}

export const PROVIDER_IDS = ["oxylabs", "decodo", "soax", "direct"] as const;
export const CONCRETE_NETWORKS = ["datacenter", "residential", "isp", "mobile"] as const;

export const PLAN_OPTIONS = PLANS.map((p) => ({ value: p, label: PLAN_LIMITS[p].label }));

// ---------------------------------------------------------------------------
// Overview
// ---------------------------------------------------------------------------
export interface PlatformKpis {
  requests: number;
  successes: number;
  failed: number;
  successRate: number | null;
  activeOrgs: number;
  revenue: number;
  cost: number;
  margin: number;
  marginPct: number | null;
  costPerSuccess: number | null;
  avgAttempts: number | null;
  avgLatency: number | null;
  bytes: number;
  newUsers: number;
  newOrgs: number;
}

export async function getPlatformKpis(since: Date): Promise<PlatformKpis> {
  const db = getDb();
  const [r] = await db
    .select({
      requests: count(),
      successes: sumCase(sql`${fetchRequests.status} = 'success'`),
      failed: sumCase(sql`${fetchRequests.status} = 'failed'`),
      activeOrgs: num(sql`count(distinct ${fetchRequests.organizationId})`),
      revenue: num(sql`sum(${fetchRequests.priceUsd})`),
      cost: num(sql`sum(${fetchRequests.costUsd})`),
      attempts: num(sql`sum(${fetchRequests.attempts})`),
      avgLatency: num(sql`avg(${fetchRequests.latencyMs})`),
      bytes: num(sql`sum(${fetchRequests.bytesIn} + ${fetchRequests.bytesOut})`),
    })
    .from(fetchRequests)
    .where(gte(fetchRequests.createdAt, since));
  const [u] = await db.select({ n: count() }).from(users).where(gte(users.createdAt, since));
  const [o] = await db.select({ n: count() }).from(organizations).where(gte(organizations.createdAt, since));
  const requests = r?.requests ?? 0;
  const successes = r?.successes ?? 0;
  const revenue = r?.revenue ?? 0;
  const cost = r?.cost ?? 0;
  return {
    requests,
    successes,
    failed: r?.failed ?? 0,
    successRate: pct(successes, requests),
    activeOrgs: r?.activeOrgs ?? 0,
    revenue,
    cost,
    margin: revenue - cost,
    marginPct: revenue > 0 ? ((revenue - cost) / revenue) * 100 : null,
    costPerSuccess: successes > 0 ? cost / successes : null,
    avgAttempts: requests > 0 ? (r?.attempts ?? 0) / requests : null,
    avgLatency: requests > 0 ? r?.avgLatency ?? null : null,
    bytes: r?.bytes ?? 0,
    newUsers: u?.n ?? 0,
    newOrgs: o?.n ?? 0,
  };
}

export interface ProviderStat {
  provider: string;
  requests: number;
  successes: number;
  blocked: number;
  successRate: number | null;
  blockedRate: number | null;
  avgLatency: number | null;
  bytes: number;
  cost: number;
  costPerSuccess: number | null;
}

/** Per-provider attempt statistics from `request_attempts` (admin only). */
export async function getProviderStats(since: Date): Promise<ProviderStat[]> {
  const db = getDb();
  const rows = await db
    .select({
      provider: requestAttempts.provider,
      requests: count(),
      successes: sumCase(sql`${requestAttempts.outcome} = 'success'`),
      blocked: sumCase(sql`${requestAttempts.outcome} = 'blocked'`),
      avgLatency: num(sql`avg(${requestAttempts.durationMs})`),
      bytes: num(sql`sum(${requestAttempts.bytesIn} + ${requestAttempts.bytesOut})`),
      cost: num(sql`sum(${requestAttempts.costUsd})`),
    })
    .from(requestAttempts)
    .where(gte(requestAttempts.createdAt, since))
    .groupBy(requestAttempts.provider)
    .orderBy(desc(count()));
  return rows.map((r) => ({
    ...r,
    successRate: pct(r.successes, r.requests),
    blockedRate: pct(r.blocked, r.requests),
    avgLatency: r.requests ? r.avgLatency : null,
    costPerSuccess: r.successes ? r.cost / r.successes : null,
  }));
}

export async function getRecentSignups(limit = 8) {
  return getDb()
    .select({ id: users.id, email: users.email, name: users.name, emailVerified: users.emailVerified, createdAt: users.createdAt })
    .from(users)
    .orderBy(desc(users.createdAt))
    .limit(limit);
}

export async function getRecentFailures(limit = 10) {
  return getDb()
    .select({
      id: fetchRequests.id,
      domain: fetchRequests.domain,
      errorCode: fetchRequests.errorCode,
      httpStatus: fetchRequests.httpStatus,
      attempts: fetchRequests.attempts,
      createdAt: fetchRequests.createdAt,
      orgName: organizations.name,
      organizationId: fetchRequests.organizationId,
    })
    .from(fetchRequests)
    .leftJoin(organizations, eq(organizations.id, fetchRequests.organizationId))
    .where(eq(fetchRequests.status, "failed"))
    .orderBy(desc(fetchRequests.createdAt))
    .limit(limit);
}

// ---------------------------------------------------------------------------
// Users
// ---------------------------------------------------------------------------
export interface Paged<T> {
  rows: T[];
  total: number;
  page: number;
  pageSize: number;
  pages: number;
}

function paged<T>(rows: T[], total: number, page: number, pageSize: number): Paged<T> {
  return { rows, total, page, pageSize, pages: Math.max(1, Math.ceil(total / pageSize)) };
}

export async function listUsers(opts: { q?: string; page?: number; pageSize?: number }) {
  const db = getDb();
  const page = opts.page ?? 1;
  const pageSize = opts.pageSize ?? 25;
  const where = opts.q ? or(ilike(users.email, `%${opts.q}%`), ilike(users.name, `%${opts.q}%`), eq(users.id, opts.q)) : undefined;
  const rows = await db
    .select({
      id: users.id,
      email: users.email,
      name: users.name,
      emailVerified: users.emailVerified,
      role: users.role,
      banned: users.banned,
      createdAt: users.createdAt,
      orgs: sql<number>`(select count(*) from ${organizationMembers} where ${organizationMembers.userId} = ${users.id})`.mapWith(Number),
      lastLogin: sql<string | null>`(select max(${sessions.createdAt}) from ${sessions} where ${sessions.userId} = ${users.id})`,
    })
    .from(users)
    .where(where)
    .orderBy(desc(users.createdAt))
    .limit(pageSize)
    .offset((page - 1) * pageSize);
  const [t] = await db.select({ total: count() }).from(users).where(where);
  return paged(
    rows.map((r) => ({ ...r, lastLogin: toDate(r.lastLogin) })),
    t?.total ?? 0,
    page,
    pageSize,
  );
}

export async function getUserDetail(id: string) {
  const db = getDb();
  const [user] = await db.select().from(users).where(eq(users.id, id)).limit(1);
  if (!user) return null;
  const memberships = await db
    .select({ organizationId: organizations.id, name: organizations.name, plan: organizations.plan, role: organizationMembers.role, suspended: organizations.suspended, joinedAt: organizationMembers.createdAt })
    .from(organizationMembers)
    .innerJoin(organizations, eq(organizations.id, organizationMembers.organizationId))
    .where(eq(organizationMembers.userId, id));
  const orgIds = memberships.map((m) => m.organizationId);
  const [proj] = orgIds.length ? await db.select({ n: count() }).from(projects).where(inArray(projects.organizationId, orgIds)) : [{ n: 0 }];
  const [keys] = orgIds.length ? await db.select({ n: count() }).from(apiKeys).where(inArray(apiKeys.organizationId, orgIds)) : [{ n: 0 }];
  const [reqs] = orgIds.length
    ? await db.select({ n: count() }).from(fetchRequests).where(and(inArray(fetchRequests.organizationId, orgIds), gte(fetchRequests.createdAt, rangeStart("30d"))))
    : [{ n: 0 }];
  const audit = await db.select().from(auditLogs).where(eq(auditLogs.userId, id)).orderBy(desc(auditLogs.createdAt)).limit(50);
  const userSessions = await db
    .select({ id: sessions.id, createdAt: sessions.createdAt, expiresAt: sessions.expiresAt, ipAddress: sessions.ipAddress, userAgent: sessions.userAgent })
    .from(sessions)
    .where(eq(sessions.userId, id))
    .orderBy(desc(sessions.createdAt))
    .limit(10);
  return { user, memberships, projectsCount: proj?.n ?? 0, keysCount: keys?.n ?? 0, requests30d: reqs?.n ?? 0, audit, sessions: userSessions };
}

// ---------------------------------------------------------------------------
// Organizations
// ---------------------------------------------------------------------------
export async function listOrganizations(opts: { q?: string; plan?: string; page?: number; pageSize?: number }) {
  const db = getDb();
  const page = opts.page ?? 1;
  const pageSize = opts.pageSize ?? 25;
  const since = rangeStart("30d");
  const conds: SQL[] = [];
  if (opts.q) conds.push(or(ilike(organizations.name, `%${opts.q}%`), ilike(organizations.slug, `%${opts.q}%`), eq(organizations.id, opts.q), ilike(users.email, `%${opts.q}%`))!);
  if (opts.plan) conds.push(eq(organizations.plan, opts.plan));
  const where = conds.length ? and(...conds) : undefined;
  const rows = await db
    .select({
      id: organizations.id,
      name: organizations.name,
      slug: organizations.slug,
      plan: organizations.plan,
      ownerEmail: users.email,
      suspended: organizations.suspended,
      providerVisibility: organizations.providerVisibility,
      createdAt: organizations.createdAt,
      projects: sql<number>`(select count(*) from ${projects} where ${projects.organizationId} = ${organizations.id})`.mapWith(Number),
      requests30d: sql<number>`(select count(*) from ${fetchRequests} where ${fetchRequests.organizationId} = ${organizations.id} and ${fetchRequests.createdAt} >= ${since})`.mapWith(Number),
      spend30d: sql<number>`(select coalesce(sum(${fetchRequests.priceUsd}),0) from ${fetchRequests} where ${fetchRequests.organizationId} = ${organizations.id} and ${fetchRequests.createdAt} >= ${since})`.mapWith(Number),
    })
    .from(organizations)
    .leftJoin(users, eq(users.id, organizations.ownerUserId))
    .where(where)
    .orderBy(desc(organizations.createdAt))
    .limit(pageSize)
    .offset((page - 1) * pageSize);
  const [t] = await db.select({ total: count() }).from(organizations).leftJoin(users, eq(users.id, organizations.ownerUserId)).where(where);
  return paged(rows, t?.total ?? 0, page, pageSize);
}

export async function getOrganizationDetail(id: string) {
  const db = getDb();
  const [org] = await db.select().from(organizations).where(eq(organizations.id, id)).limit(1);
  if (!org) return null;
  const [owner] = await db.select({ id: users.id, email: users.email, name: users.name }).from(users).where(eq(users.id, org.ownerUserId)).limit(1);
  const members = await db
    .select({ userId: users.id, email: users.email, name: users.name, role: organizationMembers.role, joinedAt: organizationMembers.createdAt })
    .from(organizationMembers)
    .innerJoin(users, eq(users.id, organizationMembers.userId))
    .where(eq(organizationMembers.organizationId, id));
  const since = rangeStart("30d");
  const projectRows = await db
    .select({
      id: projects.id,
      name: projects.name,
      environment: projects.environment,
      archivedAt: projects.archivedAt,
      createdAt: projects.createdAt,
      keys: sql<number>`(select count(*) from ${apiKeys} where ${apiKeys.projectId} = ${projects.id} and ${apiKeys.revokedAt} is null)`.mapWith(Number),
      requests30d: sql<number>`(select count(*) from ${fetchRequests} where ${fetchRequests.projectId} = ${projects.id} and ${fetchRequests.createdAt} >= ${since})`.mapWith(Number),
    })
    .from(projects)
    .where(eq(projects.organizationId, id))
    .orderBy(asc(projects.createdAt));
  const keys = await db
    .select({
      id: apiKeys.id,
      name: apiKeys.name,
      keyPrefix: apiKeys.keyPrefix,
      last4: apiKeys.last4,
      mode: apiKeys.mode,
      scopes: apiKeys.scopes,
      projectName: projects.name,
      lastUsedAt: apiKeys.lastUsedAt,
      revokedAt: apiKeys.revokedAt,
      expiresAt: apiKeys.expiresAt,
      createdAt: apiKeys.createdAt,
    })
    .from(apiKeys)
    .leftJoin(projects, eq(projects.id, apiKeys.projectId))
    .where(eq(apiKeys.organizationId, id))
    .orderBy(desc(apiKeys.createdAt))
    .limit(100);
  const recentRequests = await db
    .select({
      id: fetchRequests.id,
      domain: fetchRequests.domain,
      status: fetchRequests.status,
      httpStatus: fetchRequests.httpStatus,
      errorCode: fetchRequests.errorCode,
      network: fetchRequests.network,
      country: fetchRequests.country,
      latencyMs: fetchRequests.latencyMs,
      priceUsd: fetchRequests.priceUsd,
      costUsd: fetchRequests.costUsd,
      createdAt: fetchRequests.createdAt,
    })
    .from(fetchRequests)
    .where(eq(fetchRequests.organizationId, id))
    .orderBy(desc(fetchRequests.createdAt))
    .limit(25);
  const [stats] = await db
    .select({
      requests: count(),
      successes: sumCase(sql`${fetchRequests.status} = 'success'`),
      revenue: num(sql`sum(${fetchRequests.priceUsd})`),
      cost: num(sql`sum(${fetchRequests.costUsd})`),
      bytes: num(sql`sum(${fetchRequests.bytesIn} + ${fetchRequests.bytesOut})`),
    })
    .from(fetchRequests)
    .where(and(eq(fetchRequests.organizationId, id), gte(fetchRequests.createdAt, since)));
  const audit = await db.select().from(auditLogs).where(eq(auditLogs.organizationId, id)).orderBy(desc(auditLogs.createdAt)).limit(30);
  return { org, owner: owner ?? null, members, projects: projectRows, keys, recentRequests, stats30d: stats!, audit };
}

// ---------------------------------------------------------------------------
// Projects
// ---------------------------------------------------------------------------
export async function listProjects(opts: { q?: string; page?: number; pageSize?: number; includeArchived?: boolean }) {
  const db = getDb();
  const page = opts.page ?? 1;
  const pageSize = opts.pageSize ?? 25;
  const since = rangeStart("30d");
  const conds: SQL[] = [];
  if (opts.q) conds.push(or(ilike(projects.name, `%${opts.q}%`), ilike(organizations.name, `%${opts.q}%`), eq(projects.id, opts.q))!);
  if (!opts.includeArchived) conds.push(sql`${projects.archivedAt} is null`);
  const where = conds.length ? and(...conds) : undefined;
  const rows = await db
    .select({
      id: projects.id,
      name: projects.name,
      environment: projects.environment,
      organizationId: projects.organizationId,
      orgName: organizations.name,
      orgPlan: organizations.plan,
      archivedAt: projects.archivedAt,
      createdAt: projects.createdAt,
      keys: sql<number>`(select count(*) from ${apiKeys} where ${apiKeys.projectId} = ${projects.id} and ${apiKeys.revokedAt} is null)`.mapWith(Number),
      requests30d: sql<number>`(select count(*) from ${fetchRequests} where ${fetchRequests.projectId} = ${projects.id} and ${fetchRequests.createdAt} >= ${since})`.mapWith(Number),
      spend30d: sql<number>`(select coalesce(sum(${fetchRequests.priceUsd}),0) from ${fetchRequests} where ${fetchRequests.projectId} = ${projects.id} and ${fetchRequests.createdAt} >= ${since})`.mapWith(Number),
    })
    .from(projects)
    .innerJoin(organizations, eq(organizations.id, projects.organizationId))
    .where(where)
    .orderBy(desc(projects.createdAt))
    .limit(pageSize)
    .offset((page - 1) * pageSize);
  const [t] = await db.select({ total: count() }).from(projects).innerJoin(organizations, eq(organizations.id, projects.organizationId)).where(where);
  return paged(rows, t?.total ?? 0, page, pageSize);
}

// ---------------------------------------------------------------------------
// Requests explorer
// ---------------------------------------------------------------------------
export interface RequestFilters {
  org?: string;
  project?: string;
  domain?: string;
  status?: string;
  provider?: string;
  network?: string;
  country?: string;
  source?: string;
  errorCode?: string;
  from?: string;
  to?: string;
  id?: string;
  page?: number;
  pageSize?: number;
}

export function requestFiltersFromParams(sp: Record<string, string | string[] | undefined>): RequestFilters {
  return {
    org: str(sp.org) || undefined,
    project: str(sp.project) || undefined,
    domain: str(sp.domain) || undefined,
    status: str(sp.status) || undefined,
    provider: str(sp.provider) || undefined,
    network: str(sp.network) || undefined,
    country: str(sp.country).toUpperCase() || undefined,
    source: str(sp.source) || undefined,
    errorCode: str(sp.errorCode) || undefined,
    from: str(sp.from) || undefined,
    to: str(sp.to) || undefined,
    id: str(sp.id) || undefined,
    page: pageNum(sp.page),
  };
}

function requestConditions(f: RequestFilters): SQL | undefined {
  const conds: SQL[] = [];
  if (f.id) conds.push(eq(fetchRequests.id, f.id));
  if (f.org) conds.push(eq(fetchRequests.organizationId, f.org));
  if (f.project) conds.push(eq(fetchRequests.projectId, f.project));
  if (f.domain) conds.push(ilike(fetchRequests.domain, `%${f.domain}%`));
  if (f.status) conds.push(eq(fetchRequests.status, f.status));
  if (f.network) conds.push(eq(fetchRequests.network, f.network));
  if (f.country) conds.push(eq(fetchRequests.country, f.country));
  if (f.source) conds.push(eq(fetchRequests.source, f.source));
  if (f.errorCode) conds.push(eq(fetchRequests.errorCode, f.errorCode));
  if (f.from) {
    const d = new Date(f.from);
    if (!Number.isNaN(d.getTime())) conds.push(gte(fetchRequests.createdAt, d));
  }
  if (f.to) {
    const d = new Date(f.to);
    if (!Number.isNaN(d.getTime())) conds.push(lte(fetchRequests.createdAt, d));
  }
  if (f.provider) {
    conds.push(inArray(fetchRequests.id, getDb().select({ id: requestAttempts.requestId }).from(requestAttempts).where(eq(requestAttempts.provider, f.provider))));
  }
  return conds.length ? and(...conds) : undefined;
}

export async function listRequests(f: RequestFilters) {
  const db = getDb();
  const page = f.page ?? 1;
  const pageSize = f.pageSize ?? 50;
  const where = requestConditions(f);
  const rows = await db
    .select({
      id: fetchRequests.id,
      organizationId: fetchRequests.organizationId,
      orgName: organizations.name,
      projectId: fetchRequests.projectId,
      projectName: projects.name,
      source: fetchRequests.source,
      domain: fetchRequests.domain,
      method: fetchRequests.method,
      status: fetchRequests.status,
      httpStatus: fetchRequests.httpStatus,
      errorCode: fetchRequests.errorCode,
      requestedNetwork: fetchRequests.requestedNetwork,
      network: fetchRequests.network,
      country: fetchRequests.country,
      attempts: fetchRequests.attempts,
      latencyMs: fetchRequests.latencyMs,
      bytesIn: fetchRequests.bytesIn,
      costUsd: fetchRequests.costUsd,
      priceUsd: fetchRequests.priceUsd,
      createdAt: fetchRequests.createdAt,
      providers: sql<string | null>`(select string_agg(${requestAttempts.provider}, ',' order by ${requestAttempts.attemptNo}) from ${requestAttempts} where ${requestAttempts.requestId} = ${fetchRequests.id})`,
    })
    .from(fetchRequests)
    .leftJoin(organizations, eq(organizations.id, fetchRequests.organizationId))
    .leftJoin(projects, eq(projects.id, fetchRequests.projectId))
    .where(where)
    .orderBy(desc(fetchRequests.createdAt))
    .limit(pageSize)
    .offset((page - 1) * pageSize);
  const [t] = await db.select({ total: count() }).from(fetchRequests).where(where);
  return paged(rows, t?.total ?? 0, page, pageSize);
}

export async function getRequestDetail(id: string) {
  const db = getDb();
  const [req] = await db.select().from(fetchRequests).where(eq(fetchRequests.id, id)).limit(1);
  if (!req) return null;
  const [org] = await db.select({ id: organizations.id, name: organizations.name, plan: organizations.plan, providerVisibility: organizations.providerVisibility }).from(organizations).where(eq(organizations.id, req.organizationId)).limit(1);
  const [project] = await db.select({ id: projects.id, name: projects.name, environment: projects.environment }).from(projects).where(eq(projects.id, req.projectId)).limit(1);
  const key = req.apiKeyId
    ? (await db.select({ id: apiKeys.id, name: apiKeys.name, keyPrefix: apiKeys.keyPrefix, last4: apiKeys.last4, mode: apiKeys.mode }).from(apiKeys).where(eq(apiKeys.id, req.apiKeyId)).limit(1))[0] ?? null
    : null;
  const attempts = await db.select().from(requestAttempts).where(eq(requestAttempts.requestId, id)).orderBy(asc(requestAttempts.attemptNo));
  const usage = await db.select().from(usageEvents).where(eq(usageEvents.requestId, id)).orderBy(asc(usageEvents.createdAt));
  const session = req.sessionId ? (await db.select().from(proxySessions).where(eq(proxySessions.id, req.sessionId)).limit(1))[0] ?? null : null;
  return { req, org: org ?? null, project: project ?? null, key, attempts, usage, session };
}

/** Distinct filter values for the explorer selects. */
export async function getRequestFilterOptions() {
  const db = getDb();
  const orgs = await db.select({ id: organizations.id, name: organizations.name }).from(organizations).orderBy(asc(organizations.name)).limit(500);
  const errorCodes = await db
    .select({ code: fetchRequests.errorCode })
    .from(fetchRequests)
    .where(sql`${fetchRequests.errorCode} is not null`)
    .groupBy(fetchRequests.errorCode)
    .orderBy(asc(fetchRequests.errorCode));
  return { orgs, errorCodes: errorCodes.map((e) => e.code!).filter(Boolean) };
}

// ---------------------------------------------------------------------------
// Usage / unit economics
// ---------------------------------------------------------------------------
export interface EconRow {
  key: string;
  requests: number;
  successes: number;
  revenue: number;
  cost: number;
  margin: number;
  marginPct: number | null;
  bytes: number;
}

function econ<T extends { key: string; requests: number; successes: number; revenue: number; cost: number; bytes: number }>(r: T): EconRow {
  const margin = r.revenue - r.cost;
  return { key: r.key, requests: r.requests, successes: r.successes, revenue: r.revenue, cost: r.cost, margin, marginPct: r.revenue > 0 ? (margin / r.revenue) * 100 : null, bytes: r.bytes };
}

const econFields = {
  requests: count(),
  successes: sumCase(sql`${fetchRequests.status} = 'success'`),
  revenue: num(sql`sum(${fetchRequests.priceUsd})`),
  cost: num(sql`sum(${fetchRequests.costUsd})`),
  bytes: num(sql`sum(${fetchRequests.bytesIn} + ${fetchRequests.bytesOut})`),
};

export async function getEconomics(range: Range) {
  const db = getDb();
  const since = rangeStart(range);
  const unit = range === "24h" ? "hour" : "day";
  const bucket = sql<string>`date_trunc(${sql.raw(`'${unit}'`)}, ${fetchRequests.createdAt})`;
  const byBucketRaw = await db
    .select({ key: sql<string>`to_char(${bucket}, 'YYYY-MM-DD"T"HH24:MI:SS')`, ...econFields })
    .from(fetchRequests)
    .where(gte(fetchRequests.createdAt, since))
    .groupBy(bucket)
    .orderBy(bucket);
  const byPlanRaw = await db
    .select({ key: organizations.plan, ...econFields })
    .from(fetchRequests)
    .innerJoin(organizations, eq(organizations.id, fetchRequests.organizationId))
    .where(gte(fetchRequests.createdAt, since))
    .groupBy(organizations.plan);
  const byNetworkRaw = await db
    .select({ key: sql<string>`coalesce(${fetchRequests.network}, 'unresolved')`, ...econFields })
    .from(fetchRequests)
    .where(gte(fetchRequests.createdAt, since))
    .groupBy(sql`coalesce(${fetchRequests.network}, 'unresolved')`);
  const byProviderRaw = await db
    .select({
      key: requestAttempts.provider,
      attempts: count(),
      successes: sumCase(sql`${requestAttempts.outcome} = 'success'`),
      cost: num(sql`sum(${requestAttempts.costUsd})`),
      bytes: num(sql`sum(${requestAttempts.bytesIn} + ${requestAttempts.bytesOut})`),
    })
    .from(requestAttempts)
    .where(gte(requestAttempts.createdAt, since))
    .groupBy(requestAttempts.provider);
  const providerRevenue = await db
    .select({ key: requestAttempts.provider, revenue: num(sql`sum(${fetchRequests.priceUsd})`) })
    .from(requestAttempts)
    .innerJoin(fetchRequests, eq(fetchRequests.id, requestAttempts.requestId))
    .where(and(gte(requestAttempts.createdAt, since), eq(requestAttempts.outcome, "success")))
    .groupBy(requestAttempts.provider);
  const revMap = new Map(providerRevenue.map((r) => [r.key, r.revenue]));
  const topCustomersRaw = await db
    .select({ key: fetchRequests.organizationId, name: organizations.name, plan: organizations.plan, ...econFields })
    .from(fetchRequests)
    .innerJoin(organizations, eq(organizations.id, fetchRequests.organizationId))
    .where(gte(fetchRequests.createdAt, since))
    .groupBy(fetchRequests.organizationId, organizations.name, organizations.plan)
    .orderBy(desc(num(sql`sum(${fetchRequests.priceUsd})`)), desc(count()))
    .limit(20);
  return {
    unit,
    byBucket: byBucketRaw.map(econ),
    byPlan: byPlanRaw.map(econ).sort((a, b) => PLANS.indexOf(normalizePlan(a.key)) - PLANS.indexOf(normalizePlan(b.key)) || a.key.localeCompare(b.key)),
    byNetwork: byNetworkRaw.map(econ),
    byProvider: byProviderRaw.map((r) => ({
      provider: r.key,
      attempts: r.attempts,
      successes: r.successes,
      cost: r.cost,
      bytes: r.bytes,
      attributedRevenue: revMap.get(r.key) ?? 0,
      costPerSuccess: r.successes ? r.cost / r.successes : null,
    })),
    topCustomers: topCustomersRaw.map((r) => ({ ...econ(r), name: r.name, plan: r.plan })),
  };
}

// ---------------------------------------------------------------------------
// Providers
// ---------------------------------------------------------------------------
export async function getProviderConfigs() {
  return getDb().select().from(providerConfigs).orderBy(asc(providerConfigs.id));
}

export async function getProviderHealthHistory(limit = 50) {
  return getDb().select().from(providerHealth).orderBy(desc(providerHealth.checkedAt)).limit(limit);
}

// ---------------------------------------------------------------------------
// Routing analytics
// ---------------------------------------------------------------------------
export interface RouteMatrixRow {
  provider: string;
  network: string;
  requests: number;
  successes: number;
  blocked: number;
  errors: number;
  successRate: number | null;
  avgLatency: number | null;
  bytes: number;
  cost: number;
  costPerSuccess: number | null;
}

export async function getRoutingAnalytics(range: Range) {
  const db = getDb();
  const since = rangeStart(range);
  const matrixRaw = await db
    .select({
      provider: routingMetrics.provider,
      network: routingMetrics.network,
      requests: num(sql`sum(${routingMetrics.requests})`),
      successes: num(sql`sum(${routingMetrics.successes})`),
      blocked: num(sql`sum(${routingMetrics.blocked})`),
      errors: num(sql`sum(${routingMetrics.errors})`),
      latencySum: num(sql`sum(${routingMetrics.latencySumMs})`),
      bytes: num(sql`sum(${routingMetrics.bytes})`),
      cost: num(sql`sum(${routingMetrics.costUsd})`),
    })
    .from(routingMetrics)
    .where(gte(routingMetrics.bucket, since))
    .groupBy(routingMetrics.provider, routingMetrics.network)
    .orderBy(asc(routingMetrics.provider), asc(routingMetrics.network));
  const matrix: RouteMatrixRow[] = matrixRaw.map((r) => ({
    provider: r.provider,
    network: r.network,
    requests: r.requests,
    successes: r.successes,
    blocked: r.blocked,
    errors: r.errors,
    successRate: pct(r.successes, r.requests),
    avgLatency: r.requests ? r.latencySum / r.requests : null,
    bytes: r.bytes,
    cost: r.cost,
    costPerSuccess: r.successes ? r.cost / r.successes : null,
  }));
  const unit = range === "24h" ? "hour" : "day";
  const bucket = sql<string>`date_trunc(${sql.raw(`'${unit}'`)}, ${routingMetrics.bucket})`;
  const seriesRaw = await db
    .select({
      t: sql<string>`to_char(${bucket}, 'YYYY-MM-DD"T"HH24:MI:SS')`,
      provider: routingMetrics.provider,
      requests: num(sql`sum(${routingMetrics.requests})`),
      successes: num(sql`sum(${routingMetrics.successes})`),
      cost: num(sql`sum(${routingMetrics.costUsd})`),
    })
    .from(routingMetrics)
    .where(gte(routingMetrics.bucket, since))
    .groupBy(bucket, routingMetrics.provider)
    .orderBy(bucket);
  const providers = Array.from(new Set(seriesRaw.map((s) => s.provider))).sort();
  const byT = new Map<string, Record<string, number | string>>();
  for (const s of seriesRaw) {
    const row = byT.get(s.t) ?? { t: s.t };
    row[`${s.provider}:requests`] = s.requests;
    row[`${s.provider}:successes`] = s.successes;
    row[`${s.provider}:cost`] = s.cost;
    byT.set(s.t, row);
  }
  return { matrix, series: Array.from(byT.values()), providers, unit };
}

// ---------------------------------------------------------------------------
// Domains
// ---------------------------------------------------------------------------
export const DOMAIN_SORTS = ["requests", "domain", "successRate", "blockRate", "captchaRate", "avgLatencyMs", "lastSeenAt"] as const;
export type DomainSort = (typeof DOMAIN_SORTS)[number];

export async function listDomains(opts: { q?: string; sort?: string; dir?: string; page?: number; pageSize?: number }) {
  const db = getDb();
  const page = opts.page ?? 1;
  const pageSize = opts.pageSize ?? 50;
  const sort: DomainSort = (DOMAIN_SORTS as readonly string[]).includes(opts.sort ?? "") ? (opts.sort as DomainSort) : "requests";
  const dir = opts.dir === "asc" ? "asc" : "desc";
  const where = opts.q ? ilike(domainProfiles.domain, `%${opts.q}%`) : undefined;
  const successRate = sql<number>`case when ${domainProfiles.requests} > 0 then ${domainProfiles.successes}::float / ${domainProfiles.requests} * 100 else null end`;
  const blockRate = sql<number>`case when ${domainProfiles.requests} > 0 then ${domainProfiles.blocks}::float / ${domainProfiles.requests} * 100 else null end`;
  const captchaRate = sql<number>`case when ${domainProfiles.requests} > 0 then ${domainProfiles.captchas}::float / ${domainProfiles.requests} * 100 else null end`;
  const sortExpr: Record<DomainSort, SQL | typeof domainProfiles.domain> = {
    requests: sql`${domainProfiles.requests}`,
    domain: domainProfiles.domain,
    successRate,
    blockRate,
    captchaRate,
    avgLatencyMs: sql`${domainProfiles.avgLatencyMs}`,
    lastSeenAt: sql`${domainProfiles.lastSeenAt}`,
  };
  const orderBy = dir === "asc" ? sql`${sortExpr[sort]} asc nulls last` : sql`${sortExpr[sort]} desc nulls last`;
  const rows = await db
    .select({
      domain: domainProfiles.domain,
      requests: domainProfiles.requests,
      successes: domainProfiles.successes,
      blocks: domainProfiles.blocks,
      captchas: domainProfiles.captchas,
      browserRequired: domainProfiles.browserRequired,
      avgLatencyMs: domainProfiles.avgLatencyMs,
      preferredNetwork: domainProfiles.preferredNetwork,
      preferredProvider: domainProfiles.preferredProvider,
      policy: domainProfiles.policy,
      lastSeenAt: domainProfiles.lastSeenAt,
      successRate: successRate.mapWith((v) => (v === null ? null : Number(v))),
      blockRate: blockRate.mapWith((v) => (v === null ? null : Number(v))),
      captchaRate: captchaRate.mapWith((v) => (v === null ? null : Number(v))),
    })
    .from(domainProfiles)
    .where(where)
    .orderBy(orderBy)
    .limit(pageSize)
    .offset((page - 1) * pageSize);
  const [t] = await db.select({ total: count() }).from(domainProfiles).where(where);
  return { ...paged(rows, t?.total ?? 0, page, pageSize), sort, dir };
}

export async function getDomainProfile(domain: string) {
  const [row] = await getDb().select().from(domainProfiles).where(eq(domainProfiles.domain, domain)).limit(1);
  if (!row) return null;
  const since = rangeStart("7d");
  const recent = await getDb()
    .select({
      id: fetchRequests.id,
      status: fetchRequests.status,
      httpStatus: fetchRequests.httpStatus,
      errorCode: fetchRequests.errorCode,
      network: fetchRequests.network,
      attempts: fetchRequests.attempts,
      latencyMs: fetchRequests.latencyMs,
      createdAt: fetchRequests.createdAt,
      orgName: organizations.name,
    })
    .from(fetchRequests)
    .leftJoin(organizations, eq(organizations.id, fetchRequests.organizationId))
    .where(and(eq(fetchRequests.domain, domain), gte(fetchRequests.createdAt, since)))
    .orderBy(desc(fetchRequests.createdAt))
    .limit(20);
  return { profile: row, recent };
}

// ---------------------------------------------------------------------------
// Billing
// ---------------------------------------------------------------------------
export async function getBillingOverview() {
  const db = getDb();
  const subs = await db
    .select({
      id: subscriptions.id,
      organizationId: subscriptions.organizationId,
      orgName: organizations.name,
      plan: subscriptions.plan,
      status: subscriptions.status,
      stripeSubscriptionId: subscriptions.stripeSubscriptionId,
      currentPeriodStart: subscriptions.currentPeriodStart,
      currentPeriodEnd: subscriptions.currentPeriodEnd,
      cancelAtPeriodEnd: subscriptions.cancelAtPeriodEnd,
      createdAt: subscriptions.createdAt,
    })
    .from(subscriptions)
    .leftJoin(organizations, eq(organizations.id, subscriptions.organizationId))
    .orderBy(desc(subscriptions.createdAt))
    .limit(100);
  const events = await db
    .select({ id: billingEvents.id, organizationId: billingEvents.organizationId, orgName: organizations.name, type: billingEvents.type, stripeEventId: billingEvents.stripeEventId, createdAt: billingEvents.createdAt })
    .from(billingEvents)
    .leftJoin(organizations, eq(organizations.id, billingEvents.organizationId))
    .orderBy(desc(billingEvents.createdAt))
    .limit(100);
  const dist = await db.select({ plan: organizations.plan, n: count() }).from(organizations).groupBy(organizations.plan);
  // Single plan: every stored value (including legacy ones) normalizes to it, so nothing is "unknown".
  const distribution = PLANS.map((p) => {
    const n = dist.filter((d) => normalizePlan(d.plan) === p).reduce((s, d) => s + Number(d.n ?? 0), 0);
    const price = PLAN_LIMITS[p].price_usd_month;
    return { plan: p, label: PLAN_LIMITS[p].label, orgs: n, price, mrr: n * price };
  });
  /** Legacy plan values still present in `organizations.plan` (should be empty after migration 0001). */
  const unknown = dist.filter((d) => !(PLANS as readonly string[]).includes(d.plan)).map((d) => ({ plan: d.plan, n: Number(d.n ?? 0) }));
  const mrr = distribution.reduce((s, d) => s + d.mrr, 0);
  const [stripe] = await db.select({ n: count() }).from(organizations).where(sql`${organizations.stripeCustomerId} is not null`);
  const since = rangeStart("30d");
  const [usage30] = await db
    .select({ revenue: num(sql`sum(${fetchRequests.priceUsd})`), cost: num(sql`sum(${fetchRequests.costUsd})`) })
    .from(fetchRequests)
    .where(gte(fetchRequests.createdAt, since));
  return { subs, events, distribution, unknown, mrr, stripeCustomers: stripe?.n ?? 0, usageRevenue30d: usage30?.revenue ?? 0, usageCost30d: usage30?.cost ?? 0, stripeConfigured: Boolean(process.env.STRIPE_SECRET_KEY) };
}

// ---------------------------------------------------------------------------
// Access (signup allowlist)
// ---------------------------------------------------------------------------
export type AllowlistStatus = "account" | "invited" | "pending";

export interface AllowlistRow {
  email: string;
  note: string | null;
  invitedByUserId: string | null;
  invitedByEmail: string | null;
  invitedAt: Date | null;
  usedAt: Date | null;
  userId: string | null;
  /** Email of the account that consumed the invitation (join on users), when it exists. */
  accountEmail: string | null;
  accountBanned: boolean | null;
  createdAt: Date;
  status: AllowlistStatus;
}

/** Whole allowlist (it is small by construction), newest first, with inviter and account information. */
export async function listAllowlist(opts: { q?: string } = {}): Promise<AllowlistRow[]> {
  const db = getDb();
  const inviter = alias(users, "inviter");
  const account = alias(users, "account");
  const where = opts.q ? or(ilike(signupAllowlist.email, `%${opts.q}%`), ilike(signupAllowlist.note, `%${opts.q}%`)) : undefined;
  const rows = await db
    .select({
      email: signupAllowlist.email,
      note: signupAllowlist.note,
      invitedByUserId: signupAllowlist.invitedByUserId,
      invitedByEmail: inviter.email,
      invitedAt: signupAllowlist.invitedAt,
      usedAt: signupAllowlist.usedAt,
      userId: signupAllowlist.userId,
      accountEmail: account.email,
      accountBanned: account.banned,
      createdAt: signupAllowlist.createdAt,
    })
    .from(signupAllowlist)
    .leftJoin(inviter, eq(inviter.id, signupAllowlist.invitedByUserId))
    .leftJoin(account, eq(account.id, signupAllowlist.userId))
    .where(where)
    .orderBy(desc(signupAllowlist.createdAt))
    .limit(2000);
  return rows.map((r) => ({
    ...r,
    status: r.userId || r.usedAt ? "account" : r.invitedAt ? "invited" : "pending",
  }));
}

export async function getAllowlistCounts(): Promise<{ total: number; accounts: number; invited: number; pending: number }> {
  const [r] = await getDb()
    .select({
      total: count(),
      accounts: sumCase(sql`${signupAllowlist.userId} is not null or ${signupAllowlist.usedAt} is not null`),
      invited: sumCase(sql`${signupAllowlist.userId} is null and ${signupAllowlist.usedAt} is null and ${signupAllowlist.invitedAt} is not null`),
    })
    .from(signupAllowlist);
  const total = r?.total ?? 0;
  const accounts = r?.accounts ?? 0;
  const invited = r?.invited ?? 0;
  return { total, accounts, invited, pending: Math.max(0, total - accounts - invited) };
}

// ---------------------------------------------------------------------------
// Abuse
// ---------------------------------------------------------------------------
export async function listAbuseEvents(opts: { resolved?: "all" | "open" | "resolved"; limit?: number }) {
  const db = getDb();
  const where = opts.resolved === "open" ? eq(abuseEvents.resolved, false) : opts.resolved === "resolved" ? eq(abuseEvents.resolved, true) : undefined;
  return db
    .select({
      id: abuseEvents.id,
      organizationId: abuseEvents.organizationId,
      orgName: organizations.name,
      projectId: abuseEvents.projectId,
      requestId: abuseEvents.requestId,
      kind: abuseEvents.kind,
      severity: abuseEvents.severity,
      detail: abuseEvents.detail,
      resolved: abuseEvents.resolved,
      createdAt: abuseEvents.createdAt,
    })
    .from(abuseEvents)
    .leftJoin(organizations, eq(organizations.id, abuseEvents.organizationId))
    .where(where)
    .orderBy(asc(abuseEvents.resolved), desc(abuseEvents.createdAt))
    .limit(opts.limit ?? 200);
}

export async function getSuspiciousActivity() {
  const db = getDb();
  const since = rangeStart("24h");
  const ssrf = await db
    .select({
      organizationId: fetchRequests.organizationId,
      orgName: organizations.name,
      plan: organizations.plan,
      total: count(),
      notAllowed: sumCase(sql`${fetchRequests.errorCode} = 'URL_NOT_ALLOWED'`),
    })
    .from(fetchRequests)
    .innerJoin(organizations, eq(organizations.id, fetchRequests.organizationId))
    .where(gte(fetchRequests.createdAt, since))
    .groupBy(fetchRequests.organizationId, organizations.name, organizations.plan)
    .having(sql`count(*) >= 5 and sum(case when ${fetchRequests.errorCode} = 'URL_NOT_ALLOWED' then 1 else 0 end)::float / count(*) > 0.3`)
    .orderBy(desc(count()))
    .limit(20);
  const limited = await db
    .select({
      organizationId: fetchRequests.organizationId,
      orgName: organizations.name,
      plan: organizations.plan,
      rateLimited: sumCase(sql`${fetchRequests.errorCode} = 'RATE_LIMITED'`),
      concurrency: sumCase(sql`${fetchRequests.errorCode} = 'CONCURRENCY_LIMIT'`),
      total: count(),
    })
    .from(fetchRequests)
    .innerJoin(organizations, eq(organizations.id, fetchRequests.organizationId))
    .where(and(gte(fetchRequests.createdAt, since), inArray(fetchRequests.errorCode, ["RATE_LIMITED", "CONCURRENCY_LIMIT"])))
    .groupBy(fetchRequests.organizationId, organizations.name, organizations.plan)
    .orderBy(desc(count()))
    .limit(20);
  const unverified = await db
    .select({
      userId: users.id,
      email: users.email,
      organizationId: organizations.id,
      orgName: organizations.name,
      playground: count(),
      createdAt: users.createdAt,
    })
    .from(fetchRequests)
    .innerJoin(organizations, eq(organizations.id, fetchRequests.organizationId))
    .innerJoin(users, eq(users.id, organizations.ownerUserId))
    .where(and(gte(fetchRequests.createdAt, since), eq(fetchRequests.source, "playground"), eq(users.emailVerified, false)))
    .groupBy(users.id, users.email, organizations.id, organizations.name, users.createdAt)
    .having(sql`count(*) >= 20`)
    .orderBy(desc(count()))
    .limit(20);
  return {
    ssrf: ssrf.map((r) => ({ ...r, ratio: pct(r.notAllowed, r.total) })),
    limited,
    unverified,
  };
}

// ---------------------------------------------------------------------------
// System
// ---------------------------------------------------------------------------
export async function getTableCounts() {
  const db = getDb();
  const [r] = await db.select({
    users: sql<number>`(select count(*) from ${users})`.mapWith(Number),
    sessions: sql<number>`(select count(*) from ${sessions})`.mapWith(Number),
    organizations: sql<number>`(select count(*) from ${organizations})`.mapWith(Number),
    projects: sql<number>`(select count(*) from ${projects})`.mapWith(Number),
    api_keys: sql<number>`(select count(*) from ${apiKeys})`.mapWith(Number),
    fetch_requests: sql<number>`(select count(*) from ${fetchRequests})`.mapWith(Number),
    request_attempts: sql<number>`(select count(*) from ${requestAttempts})`.mapWith(Number),
    proxy_sessions: sql<number>`(select count(*) from ${proxySessions})`.mapWith(Number),
    usage_events: sql<number>`(select count(*) from ${usageEvents})`.mapWith(Number),
    domain_profiles: sql<number>`(select count(*) from ${domainProfiles})`.mapWith(Number),
    routing_metrics: sql<number>`(select count(*) from ${routingMetrics})`.mapWith(Number),
    provider_health: sql<number>`(select count(*) from ${providerHealth})`.mapWith(Number),
    audit_logs: sql<number>`(select count(*) from ${auditLogs})`.mapWith(Number),
    abuse_events: sql<number>`(select count(*) from ${abuseEvents})`.mapWith(Number),
    webhooks: sql<number>`(select count(*) from ${webhooks})`.mapWith(Number),
    subscriptions: sql<number>`(select count(*) from ${subscriptions})`.mapWith(Number),
    billing_events: sql<number>`(select count(*) from ${billingEvents})`.mapWith(Number),
    feature_flags: sql<number>`(select count(*) from ${featureFlags})`.mapWith(Number),
    status_incidents: sql<number>`(select count(*) from ${statusIncidents})`.mapWith(Number),
  }).from(sql`(select 1) as one`);
  const [size] = await db.select({ size: sql<string>`pg_size_pretty(pg_database_size(current_database()))`, version: sql<string>`version()` }).from(sql`(select 1) as one`);
  return { counts: r!, dbSize: size?.size ?? "—", pgVersion: size?.version?.split(" on ")[0] ?? "—" };
}

export async function getFeatureFlags() {
  return getDb().select().from(featureFlags).orderBy(asc(featureFlags.key));
}

export async function getIncidents(limit = 50) {
  return getDb().select().from(statusIncidents).orderBy(sql`${statusIncidents.resolvedAt} is not null`, desc(statusIncidents.startedAt)).limit(limit);
}

/** Boolean presence of env vars in the web process. Never returns values. */
export function getEnvPresence(): Array<{ group: string; key: string; present: boolean }> {
  const spec: Array<[string, string[]]> = [
    ["Upstream providers", ["OXYLABS_USERNAME", "OXYLABS_PASSWORD", "DECODO_USERNAME", "DECODO_PASSWORD", "SOAX_USERNAME", "SOAX_PASSWORD"]],
    ["Core", ["DATABASE_URL", "REDIS_URL", "AUTH_SECRET", "INTERNAL_SERVICE_TOKEN", "API_URL", "NEXT_PUBLIC_SITE_URL", "ADMIN_EMAILS"]],
    ["Email", ["RESEND_API_KEY", "EMAIL_FROM", "EMAIL_FROM_TRANSACTIONAL"]],
    ["Anti-bot", ["TWOCAPTCHA_API_KEY", "FETCHA_BROWSER_ENABLED", "FETCHA_BROWSER_CHANNEL", "FETCHA_BROWSER_HEADLESS"]],
    ["Billing", ["STRIPE_SECRET_KEY", "STRIPE_WEBHOOK_SECRET"]],
  ];
  return spec.flatMap(([group, keys]) => keys.map((key) => ({ group, key, present: Boolean(process.env[key]?.trim()) })));
}