import "server-only";
import { and, desc, eq, gte, lt, sql, type SQL } from "drizzle-orm";
import { getDb, usageRecords, conversations, projects } from "@/db";
import { isProviderId, type PolyModel, type ProviderId } from "@/lib/ai/core/types";
import { listRegistryModels } from "@/lib/ai/registry";
import { listCustomModels } from "@/lib/endpoints/service";
import { resolveRange, bucketKeys, previousPeriod, observedDays, type ResolvedRange, type UsageRangeKey, type Bucket } from "./time";
import { computeSavings, type SavingsOpportunity } from "./savings";

/** @deprecated kept for older imports — use `UsageRangeKey`. */
export type UsageRange = UsageRangeKey;

export interface UsageQuery {
  range?: string | null;
  from?: string | null;
  to?: string | null;
  tz?: string | null;
  provider?: string | null;
  modelKey?: string | null;
  projectId?: string | null;
}

export interface UsageFilters {
  provider: ProviderId | null;
  modelKey: string | null;
  projectId: string | null;
}

export interface UsageKpis {
  requests: number;
  failures: number;
  stopped: number;
  errorRate: number;
  inputTokens: number;
  outputTokens: number;
  cachedTokens: number;
  reasoningTokens: number;
  totalTokens: number;
  costUsd: number;
  /** Requests whose cost is unknown (no pricing) — shown as a caveat. */
  unpricedRequests: number;
  avgLatencyMs: number;
  avgTtftMs: number | null;
  avgTokensPerSec: number | null;
  /** Average prompt size (input tokens per successful request). */
  avgContextTokens: number | null;
}

export interface UsageSeriesPoint {
  bucket: string;
  requests: number;
  failures: number;
  inputTokens: number;
  outputTokens: number;
  costUsd: number;
}

export interface ProviderAgg {
  provider: ProviderId;
  requests: number;
  failures: number;
  inputTokens: number;
  outputTokens: number;
  costUsd: number;
}

export interface ModelAgg {
  modelKey: string;
  provider: ProviderId;
  requests: number;
  failures: number;
  inputTokens: number;
  outputTokens: number;
  cachedTokens: number;
  reasoningTokens: number;
  costUsd: number;
  avgLatencyMs: number;
  avgTtftMs: number | null;
  tokensPerSec: number | null;
  avgContextTokens: number | null;
}

export interface UsageProjection {
  /** Cost per observed day in the range. */
  dailyAverageCost: number;
  /** dailyAverageCost × 30. */
  estimatedMonthlyCost: number;
  observedDays: number;
  previous: { costUsd: number; requests: number } | null;
  /** Relative change of cost vs the previous period of equal length; null when there is no previous data. */
  costTrendPct: number | null;
  requestsTrendPct: number | null;
}

export interface RecentRecord {
  id: string;
  provider: ProviderId;
  modelKey: string;
  kind: string;
  status: string;
  errorCode: string | null;
  inputTokens: number;
  outputTokens: number;
  cachedTokens: number;
  reasoningTokens: number;
  costUsd: number | null;
  latencyMs: number | null;
  ttftMs: number | null;
  conversationId: string | null;
  conversationTitle: string | null;
  projectId: string | null;
  createdAt: string;
}

export interface UsageSummary {
  range: { key: UsageRangeKey; from: string | null; to: string; bucket: Bucket; tz: string; days: number };
  filters: UsageFilters;
  kpis: UsageKpis;
  series: UsageSeriesPoint[];
  byProvider: ProviderAgg[];
  byModel: ModelAgg[];
  projection: UsageProjection;
  savings: SavingsOpportunity[];
  recent: RecentRecord[];
  /** Filter chips: providers/models seen in the range regardless of the active filter, and the user's projects. */
  facets: { providers: ProviderId[]; models: { modelKey: string; provider: ProviderId; requests: number }[]; projects: { id: string; name: string; icon: string | null; color: string | null }[] };
  generatedAt: string;
}

/** Read the dashboard query from a request URL (`range`, `from`, `to`, `tz`, `provider`, `modelKey`, `projectId`). */
export function usageQueryFromUrl(url: string): UsageQuery {
  const sp = new URL(url).searchParams;
  return { range: sp.get("range"), from: sp.get("from"), to: sp.get("to"), tz: sp.get("tz"), provider: sp.get("provider"), modelKey: sp.get("modelKey"), projectId: sp.get("projectId") };
}

const num = (v: unknown) => Number(v ?? 0);
const nul = (v: unknown) => (v === null || v === undefined ? null : Number(v));

export function parseFilters(q: UsageQuery): UsageFilters {
  return {
    provider: q.provider && isProviderId(q.provider) ? q.provider : null,
    modelKey: q.modelKey && q.modelKey.length <= 160 ? q.modelKey : null,
    projectId: q.projectId && q.projectId.length <= 64 ? q.projectId : null,
  };
}

function whereFor(userId: string, from: Date | null, to: Date, f: UsageFilters, opts: { ignoreProvider?: boolean; ignoreModel?: boolean } = {}): SQL {
  const conds: SQL[] = [eq(usageRecords.userId, userId), lt(usageRecords.createdAt, to)];
  if (from) conds.push(gte(usageRecords.createdAt, from));
  if (f.provider && !opts.ignoreProvider) conds.push(eq(usageRecords.provider, f.provider));
  if (f.modelKey && !opts.ignoreModel) conds.push(eq(usageRecords.modelKey, f.modelKey));
  if (f.projectId) {
    conds.push(sql`${usageRecords.conversationId} in (select ${conversations.id} from ${conversations} where ${conversations.userId} = ${userId} and ${conversations.projectId} = ${f.projectId})`);
  }
  return and(...conds)!;
}

const TPS = sql<number>`avg(case when ${usageRecords.latencyMs} > 0 and ${usageRecords.outputTokens} > 0 and ${usageRecords.status} = 'ok' then ${usageRecords.outputTokens}::float8 / (greatest(${usageRecords.latencyMs} - coalesce(${usageRecords.ttftMs}, 0), 1)::float8 / 1000) end)`;

async function totalsFor(where: SQL) {
  const [t] = await getDb()
    .select({
      requests: sql<number>`count(*)::int`,
      failures: sql<number>`sum(case when ${usageRecords.status} = 'error' then 1 else 0 end)::int`,
      stopped: sql<number>`sum(case when ${usageRecords.status} = 'stopped' then 1 else 0 end)::int`,
      inputTokens: sql<number>`coalesce(sum(${usageRecords.inputTokens}),0)::bigint`,
      outputTokens: sql<number>`coalesce(sum(${usageRecords.outputTokens}),0)::bigint`,
      cachedTokens: sql<number>`coalesce(sum(${usageRecords.cachedTokens}),0)::bigint`,
      reasoningTokens: sql<number>`coalesce(sum(${usageRecords.reasoningTokens}),0)::bigint`,
      costUsd: sql<number>`coalesce(sum(${usageRecords.costUsd}),0)::float8`,
      unpriced: sql<number>`sum(case when ${usageRecords.costUsd} is null and ${usageRecords.status} = 'ok' then 1 else 0 end)::int`,
      avgLatencyMs: sql<number>`coalesce(avg(${usageRecords.latencyMs}) filter (where ${usageRecords.status} = 'ok'),0)::float8`,
      avgTtftMs: sql<number | null>`avg(${usageRecords.ttftMs}) filter (where ${usageRecords.ttftMs} is not null and ${usageRecords.status} = 'ok')`,
      tps: sql<number | null>`${TPS}`,
      avgContext: sql<number | null>`avg(${usageRecords.inputTokens}) filter (where ${usageRecords.status} = 'ok')`,
      firstAt: sql<string | null>`min(${usageRecords.createdAt})`,
    })
    .from(usageRecords)
    .where(where);
  return t;
}

export async function usageSummary(userId: string, query: UsageQuery, now = new Date()): Promise<UsageSummary> {
  const db = getDb();
  const range = resolveRange(query, now);
  const filters = parseFilters(query);
  const where = whereFor(userId, range.from, range.to, filters);
  const bucketExpr = range.bucket === "hour" ? sql<string>`to_char(${usageRecords.createdAt} at time zone ${range.tz}, 'YYYY-MM-DD"T"HH24:00')` : sql<string>`to_char(${usageRecords.createdAt} at time zone ${range.tz}, 'YYYY-MM-DD')`;

  const [totals, seriesRows, byProviderRows, byModelRows, recentRows, projectRows, facetProviders, facetModels, registry, customModels] = await Promise.all([
    totalsFor(where),
    db
      .select({
        bucket: bucketExpr.as("bucket"),
        requests: sql<number>`count(*)::int`,
        failures: sql<number>`sum(case when ${usageRecords.status} = 'error' then 1 else 0 end)::int`,
        inputTokens: sql<number>`coalesce(sum(${usageRecords.inputTokens}),0)::bigint`,
        outputTokens: sql<number>`coalesce(sum(${usageRecords.outputTokens}),0)::bigint`,
        costUsd: sql<number>`coalesce(sum(${usageRecords.costUsd}),0)::float8`,
      })
      .from(usageRecords)
      .where(where)
      .groupBy(sql`1`)
      .orderBy(sql`1`),
    db
      .select({
        provider: usageRecords.provider,
        requests: sql<number>`count(*)::int`,
        failures: sql<number>`sum(case when ${usageRecords.status} = 'error' then 1 else 0 end)::int`,
        inputTokens: sql<number>`coalesce(sum(${usageRecords.inputTokens}),0)::bigint`,
        outputTokens: sql<number>`coalesce(sum(${usageRecords.outputTokens}),0)::bigint`,
        costUsd: sql<number>`coalesce(sum(${usageRecords.costUsd}),0)::float8`,
      })
      .from(usageRecords)
      .where(where)
      .groupBy(usageRecords.provider),
    db
      .select({
        modelKey: usageRecords.modelKey,
        provider: usageRecords.provider,
        requests: sql<number>`count(*)::int`,
        failures: sql<number>`sum(case when ${usageRecords.status} = 'error' then 1 else 0 end)::int`,
        inputTokens: sql<number>`coalesce(sum(${usageRecords.inputTokens}),0)::bigint`,
        outputTokens: sql<number>`coalesce(sum(${usageRecords.outputTokens}),0)::bigint`,
        cachedTokens: sql<number>`coalesce(sum(${usageRecords.cachedTokens}),0)::bigint`,
        reasoningTokens: sql<number>`coalesce(sum(${usageRecords.reasoningTokens}),0)::bigint`,
        costUsd: sql<number>`coalesce(sum(${usageRecords.costUsd}),0)::float8`,
        avgLatencyMs: sql<number>`coalesce(avg(${usageRecords.latencyMs}) filter (where ${usageRecords.status} = 'ok'),0)::float8`,
        avgTtftMs: sql<number | null>`avg(${usageRecords.ttftMs}) filter (where ${usageRecords.ttftMs} is not null and ${usageRecords.status} = 'ok')`,
        tokensPerSec: sql<number | null>`${TPS}`,
        avgContext: sql<number | null>`avg(${usageRecords.inputTokens}) filter (where ${usageRecords.status} = 'ok')`,
      })
      .from(usageRecords)
      .where(where)
      .groupBy(usageRecords.modelKey, usageRecords.provider)
      .orderBy(desc(sql`count(*)`)),
    db
      .select({
        id: usageRecords.id,
        provider: usageRecords.provider,
        modelKey: usageRecords.modelKey,
        kind: usageRecords.kind,
        status: usageRecords.status,
        errorCode: usageRecords.errorCode,
        inputTokens: usageRecords.inputTokens,
        outputTokens: usageRecords.outputTokens,
        cachedTokens: usageRecords.cachedTokens,
        reasoningTokens: usageRecords.reasoningTokens,
        costUsd: usageRecords.costUsd,
        latencyMs: usageRecords.latencyMs,
        ttftMs: usageRecords.ttftMs,
        conversationId: usageRecords.conversationId,
        conversationTitle: conversations.title,
        projectId: conversations.projectId,
        createdAt: usageRecords.createdAt,
      })
      .from(usageRecords)
      .leftJoin(conversations, eq(conversations.id, usageRecords.conversationId))
      .where(where)
      .orderBy(desc(usageRecords.createdAt))
      .limit(60),
    db.select({ id: projects.id, name: projects.name, icon: projects.icon, color: projects.color }).from(projects).where(and(eq(projects.userId, userId), eq(projects.archived, false))).orderBy(projects.sortOrder, projects.name),
    db
      .select({ provider: usageRecords.provider })
      .from(usageRecords)
      .where(whereFor(userId, range.from, range.to, filters, { ignoreProvider: true, ignoreModel: true }))
      .groupBy(usageRecords.provider),
    db
      .select({ modelKey: usageRecords.modelKey, provider: usageRecords.provider, requests: sql<number>`count(*)::int` })
      .from(usageRecords)
      .where(whereFor(userId, range.from, range.to, filters, { ignoreModel: true }))
      .groupBy(usageRecords.modelKey, usageRecords.provider)
      .orderBy(desc(sql`count(*)`))
      .limit(24),
    listRegistryModels({ includeDeprecated: true, includeHidden: true }),
    listCustomModels(userId).catch(() => [] as PolyModel[]),
  ]);

  // Gap-filled series in the caller's time zone.
  const byBucket = new Map(seriesRows.map((s) => [s.bucket, s]));
  const keys = range.from ? bucketKeys(range.from, range.to, range.bucket, range.tz) : seriesRows.map((s) => s.bucket);
  const series: UsageSeriesPoint[] = keys.map((k) => {
    const s = byBucket.get(k);
    return { bucket: k, requests: num(s?.requests), failures: num(s?.failures), inputTokens: num(s?.inputTokens), outputTokens: num(s?.outputTokens), costUsd: num(s?.costUsd) };
  });

  const requests = num(totals.requests);
  const failures = num(totals.failures);
  const kpis: UsageKpis = {
    requests,
    failures,
    stopped: num(totals.stopped),
    errorRate: requests ? failures / requests : 0,
    inputTokens: num(totals.inputTokens),
    outputTokens: num(totals.outputTokens),
    cachedTokens: num(totals.cachedTokens),
    reasoningTokens: num(totals.reasoningTokens),
    totalTokens: num(totals.inputTokens) + num(totals.outputTokens),
    costUsd: num(totals.costUsd),
    unpricedRequests: num(totals.unpriced),
    avgLatencyMs: num(totals.avgLatencyMs),
    avgTtftMs: nul(totals.avgTtftMs),
    avgTokensPerSec: nul(totals.tps),
    avgContextTokens: nul(totals.avgContext),
  };

  // Projection + trend vs the previous period of equal length.
  const prev = previousPeriod(range);
  const prevTotals = prev ? await totalsFor(whereFor(userId, prev.from, prev.to, filters)) : null;
  const firstAt = totals.firstAt ? new Date(totals.firstAt) : null;
  const days = observedDays(range, now, firstAt);
  const dailyAverageCost = kpis.costUsd / days;
  const prevCost = prevTotals ? num(prevTotals.costUsd) : 0;
  const prevReq = prevTotals ? num(prevTotals.requests) : 0;
  const projection: UsageProjection = {
    dailyAverageCost,
    estimatedMonthlyCost: dailyAverageCost * 30,
    observedDays: Math.round(days * 100) / 100,
    previous: prevTotals ? { costUsd: prevCost, requests: prevReq } : null,
    costTrendPct: prevTotals && prevCost > 0 ? ((kpis.costUsd - prevCost) / prevCost) * 100 : null,
    requestsTrendPct: prevTotals && prevReq > 0 ? ((requests - prevReq) / prevReq) * 100 : null,
  };

  const byModel: ModelAgg[] = byModelRows.map((m) => ({
    modelKey: m.modelKey,
    provider: m.provider,
    requests: num(m.requests),
    failures: num(m.failures),
    inputTokens: num(m.inputTokens),
    outputTokens: num(m.outputTokens),
    cachedTokens: num(m.cachedTokens),
    reasoningTokens: num(m.reasoningTokens),
    costUsd: num(m.costUsd),
    avgLatencyMs: num(m.avgLatencyMs),
    avgTtftMs: nul(m.avgTtftMs),
    tokensPerSec: nul(m.tokensPerSec),
    avgContextTokens: nul(m.avgContext),
  }));

  const registryMap = new Map<string, PolyModel>([...registry, ...customModels].map((m) => [m.key, m]));
  const savings = computeSavings(byModel, registryMap);

  return {
    range: { key: range.key, from: range.from?.toISOString() ?? null, to: range.to.toISOString(), bucket: range.bucket, tz: range.tz, days: range.days },
    filters,
    kpis,
    series,
    byProvider: byProviderRows.map((p) => ({ provider: p.provider, requests: num(p.requests), failures: num(p.failures), inputTokens: num(p.inputTokens), outputTokens: num(p.outputTokens), costUsd: num(p.costUsd) })),
    byModel,
    projection,
    savings,
    recent: recentRows.map((r) => ({ ...r, costUsd: r.costUsd ?? null, createdAt: r.createdAt.toISOString() })),
    facets: {
      providers: facetProviders.map((p) => p.provider),
      models: facetModels.map((m) => ({ modelKey: m.modelKey, provider: m.provider, requests: num(m.requests) })),
      projects: projectRows,
    },
    generatedAt: now.toISOString(),
  };
}

/** Raw records for CSV export (same range/filters as the dashboard), newest first, capped. */
export async function usageRecordsForExport(userId: string, query: UsageQuery, limit = 50_000, now = new Date()) {
  const range = resolveRange(query, now);
  const filters = parseFilters(query);
  const rows = await getDb()
    .select({
      id: usageRecords.id,
      createdAt: usageRecords.createdAt,
      provider: usageRecords.provider,
      modelKey: usageRecords.modelKey,
      kind: usageRecords.kind,
      status: usageRecords.status,
      errorCode: usageRecords.errorCode,
      inputTokens: usageRecords.inputTokens,
      outputTokens: usageRecords.outputTokens,
      cachedTokens: usageRecords.cachedTokens,
      reasoningTokens: usageRecords.reasoningTokens,
      costUsd: usageRecords.costUsd,
      latencyMs: usageRecords.latencyMs,
      ttftMs: usageRecords.ttftMs,
      conversationId: usageRecords.conversationId,
      conversationTitle: conversations.title,
      projectId: conversations.projectId,
    })
    .from(usageRecords)
    .leftJoin(conversations, eq(conversations.id, usageRecords.conversationId))
    .where(whereFor(userId, range.from, range.to, filters))
    .orderBy(desc(usageRecords.createdAt))
    .limit(limit);
  return { range, filters, rows };
}

export function usageCsv(rows: Awaited<ReturnType<typeof usageRecordsForExport>>["rows"]): string {
  const header = ["id", "created_at", "provider", "model_key", "kind", "status", "error_code", "input_tokens", "output_tokens", "cached_tokens", "reasoning_tokens", "cost_usd", "latency_ms", "ttft_ms", "conversation_id", "conversation_title", "project_id"];
  const esc = (v: unknown): string => {
    if (v === null || v === undefined) return "";
    const s = v instanceof Date ? v.toISOString() : String(v);
    // Neutralise spreadsheet formula injection and quote when needed.
    const safe = /^[=+\-@\t\r]/.test(s) ? `'${s}` : s;
    return /[",\n\r]/.test(safe) ? `"${safe.replace(/"/g, '""')}"` : safe;
  };
  const lines = [header.join(",")];
  for (const r of rows) {
    lines.push([r.id, r.createdAt, r.provider, r.modelKey, r.kind, r.status, r.errorCode, r.inputTokens, r.outputTokens, r.cachedTokens, r.reasoningTokens, r.costUsd, r.latencyMs, r.ttftMs, r.conversationId, r.conversationTitle, r.projectId].map(esc).join(","));
  }
  return `${lines.join("\r\n")}\r\n`;
}

export type { ResolvedRange };