import "server-only";
import { getDb, apiKeys, projects, fetchRequests, usageEvents, auditLogs, legalAcceptances, eq, and, or, desc, gte, sql, isNull } from "@fetcha/db";
import type { ApiKey, AuditLog } from "@fetcha/db";

/** First instant of the current calendar month (UTC). */
export function monthStart(): Date {
  const d = new Date();
  return new Date(Date.UTC(d.getUTCFullYear(), d.getUTCMonth(), 1));
}

export type ApiKeyStatus = "active" | "revoked" | "expired";

export function apiKeyStatus(k: Pick<ApiKey, "revokedAt" | "expiresAt">): ApiKeyStatus {
  if (k.revokedAt) return "revoked";
  if (k.expiresAt && k.expiresAt.getTime() < Date.now()) return "expired";
  return "active";
}

export interface ApiKeyRow {
  id: string;
  name: string;
  keyPrefix: string;
  last4: string;
  mode: string;
  scopes: string[];
  projectId: string;
  projectName: string;
  createdAt: Date;
  lastUsedAt: Date | null;
  expiresAt: Date | null;
  revokedAt: Date | null;
  status: ApiKeyStatus;
}

/** All keys of the organization (optionally one project), newest first. Never returns the hash. */
export async function listApiKeys(organizationId: string, projectId?: string | null): Promise<ApiKeyRow[]> {
  const db = getDb();
  const rows = await db
    .select({
      id: apiKeys.id,
      name: apiKeys.name,
      keyPrefix: apiKeys.keyPrefix,
      last4: apiKeys.last4,
      mode: apiKeys.mode,
      scopes: apiKeys.scopes,
      projectId: apiKeys.projectId,
      projectName: projects.name,
      createdAt: apiKeys.createdAt,
      lastUsedAt: apiKeys.lastUsedAt,
      expiresAt: apiKeys.expiresAt,
      revokedAt: apiKeys.revokedAt,
    })
    .from(apiKeys)
    .innerJoin(projects, eq(apiKeys.projectId, projects.id))
    .where(projectId ? and(eq(apiKeys.organizationId, organizationId), eq(apiKeys.projectId, projectId)) : eq(apiKeys.organizationId, organizationId))
    .orderBy(desc(apiKeys.createdAt));
  return rows.map((r) => ({ ...r, scopes: r.scopes ?? [], status: apiKeyStatus(r) }));
}

export interface MonthlyUsage {
  requests: number;
  successful: number;
  /** Bytes in + out across all attempts. */
  bytes: number;
  spendUsd: number;
}

/** Requests (fetch_requests), bandwidth and estimated spend (usage_events.cost_usd) for the current month. */
export async function monthlyUsage(organizationId: string, projectId?: string | null): Promise<MonthlyUsage> {
  const db = getDb();
  const since = monthStart();
  const reqWhere = projectId
    ? and(eq(fetchRequests.organizationId, organizationId), eq(fetchRequests.projectId, projectId), gte(fetchRequests.createdAt, since))
    : and(eq(fetchRequests.organizationId, organizationId), gte(fetchRequests.createdAt, since));
  const usageWhere = projectId
    ? and(eq(usageEvents.organizationId, organizationId), eq(usageEvents.projectId, projectId), gte(usageEvents.createdAt, since))
    : and(eq(usageEvents.organizationId, organizationId), gte(usageEvents.createdAt, since));
  const [[req], [usage]] = await Promise.all([
    db
      .select({
        n: sql<number>`count(*)::int`,
        ok: sql<number>`count(*) filter (where ${fetchRequests.status} = 'success')::int`,
        bytes: sql<number>`coalesce(sum(${fetchRequests.bytesIn} + ${fetchRequests.bytesOut}), 0)::float8`,
      })
      .from(fetchRequests)
      .where(reqWhere),
    db.select({ spend: sql<number>`coalesce(sum(${usageEvents.costUsd}), 0)::float8` }).from(usageEvents).where(usageWhere),
  ]);
  return { requests: Number(req?.n ?? 0), successful: Number(req?.ok ?? 0), bytes: Number(req?.bytes ?? 0), spendUsd: Number(usage?.spend ?? 0) };
}

export interface ProjectStats {
  requests: number;
  spendUsd: number;
  activeKeys: number;
}

/** Per-project month-to-date requests, spend and active key counts for the whole organization. */
export async function projectStatsByProject(organizationId: string): Promise<Map<string, ProjectStats>> {
  const db = getDb();
  const since = monthStart();
  const [reqRows, spendRows, keyRows] = await Promise.all([
    db
      .select({ projectId: fetchRequests.projectId, n: sql<number>`count(*)::int` })
      .from(fetchRequests)
      .where(and(eq(fetchRequests.organizationId, organizationId), gte(fetchRequests.createdAt, since)))
      .groupBy(fetchRequests.projectId),
    db
      .select({ projectId: usageEvents.projectId, spend: sql<number>`coalesce(sum(${usageEvents.costUsd}), 0)::float8` })
      .from(usageEvents)
      .where(and(eq(usageEvents.organizationId, organizationId), gte(usageEvents.createdAt, since)))
      .groupBy(usageEvents.projectId),
    db
      .select({ projectId: apiKeys.projectId, n: sql<number>`count(*)::int` })
      .from(apiKeys)
      .where(and(eq(apiKeys.organizationId, organizationId), isNull(apiKeys.revokedAt)))
      .groupBy(apiKeys.projectId),
  ]);
  const map = new Map<string, ProjectStats>();
  const get = (id: string) => {
    let s = map.get(id);
    if (!s) {
      s = { requests: 0, spendUsd: 0, activeKeys: 0 };
      map.set(id, s);
    }
    return s;
  };
  for (const r of reqRows) get(r.projectId).requests = Number(r.n);
  for (const r of spendRows) if (r.projectId) get(r.projectId).spendUsd = Number(r.spend);
  for (const r of keyRows) get(r.projectId).activeKeys = Number(r.n);
  return map;
}

/** Load one project of the organization (including archived ones), or null. */
export async function getOrgProject(organizationId: string, projectId: string) {
  const [p] = await getDb().select().from(projects).where(and(eq(projects.id, projectId), eq(projects.organizationId, organizationId))).limit(1);
  return p ?? null;
}

export async function listAuditLogs(userId: string, organizationId: string, page: number, pageSize = 25): Promise<{ rows: AuditLog[]; total: number }> {
  const db = getDb();
  const where = or(eq(auditLogs.userId, userId), eq(auditLogs.organizationId, organizationId));
  const [rows, [total]] = await Promise.all([
    db
      .select()
      .from(auditLogs)
      .where(where)
      .orderBy(desc(auditLogs.createdAt))
      .limit(pageSize)
      .offset(Math.max(0, page - 1) * pageSize),
    db.select({ n: sql<number>`count(*)::int` }).from(auditLogs).where(where),
  ]);
  return { rows, total: Number(total?.n ?? 0) };
}

export async function listLegalAcceptances(userId: string) {
  return getDb().select().from(legalAcceptances).where(eq(legalAcceptances.userId, userId)).orderBy(desc(legalAcceptances.acceptedAt));
}

/** Onboarding state: does the org already have an active key / any request? */
export async function onboardingState(organizationId: string): Promise<{ hasKey: boolean; hasRequest: boolean }> {
  const db = getDb();
  const [[k], [r]] = await Promise.all([
    db.select({ id: apiKeys.id }).from(apiKeys).where(and(eq(apiKeys.organizationId, organizationId), isNull(apiKeys.revokedAt))).limit(1),
    db.select({ id: fetchRequests.id }).from(fetchRequests).where(eq(fetchRequests.organizationId, organizationId)).limit(1),
  ]);
  return { hasKey: Boolean(k), hasRequest: Boolean(r) };
}