// /api/tools aggregates. Backed by the persistent tool_uses table —
// each row inserted at ingest with the tool name and a cost share
// of its enclosing model_call. Query becomes a pure index lookup.

import { getRawDb } from './db.ts'

export type ToolsPeriod = '24h' | '7d' | '30d' | 'lifetime'

export type ToolStat = {
  name: string
  callCount: number
  errorCount: number
  costUsd: number
  byAgent: Array<{ agent: string; calls: number }>
}

/** One row per (tool name × provider) that Thomas executed locally —
 *  emitted from captured tool_call actions. Mirrors ToolStat but
 *  carries provider attribution since the same tool name can be served
 *  by multiple configured providers. */
export type ThomasExecutionStat = {
  toolName: string
  providerId: string
  backend: string
  callCount: number
  errorCount: number
  costUsd: number
}

export type ToolsResult = {
  period: ToolsPeriod
  since: number
  until: number
  totalToolCalls: number
  totalToolErrors: number
  tools: ToolStat[]
  /** Tools that Thomas actually ran on the agent's behalf (web_search
   *  via DDG/Brave/Baidu, web_fetch, …). Distinct from `tools` above,
   *  which is the model's *intent* (tool_use blocks the model emitted).
   *  Per-provider rollup so users can compare "DDG vs Baidu" cost +
   *  call counts. */
  thomasExecutions: ThomasExecutionStat[]
  totalThomasExecutions: number
  totalThomasExecutionCostUsd: number
}

const MAX_TOOLS = 30

function sinceForPeriod(period: ToolsPeriod, now: number): number {
  switch (period) {
    case '24h':
      return now - 24 * 3_600_000
    case '7d':
      return now - 7 * 86_400_000
    case '30d':
      return now - 30 * 86_400_000
    case 'lifetime':
      return 0
  }
}

export async function computeToolStats(period: ToolsPeriod): Promise<ToolsResult> {
  const raw = await getRawDb()
  const now = Date.now()
  const since = sinceForPeriod(period, now)

  const totals = raw
    .prepare(
      `SELECT COUNT(*) AS total, SUM(is_error) AS errors
       FROM tool_uses WHERE ts >= ?`,
    )
    .get(since) as { total: number; errors: number | null }
  const totalToolCalls = totals?.total ?? 0
  const totalToolErrors = totals?.errors ?? 0

  // Top tools by call count.
  const byTool = raw
    .prepare(
      `SELECT name, COUNT(*) AS calls,
              SUM(is_error) AS errors,
              COALESCE(SUM(cost_micro_share), 0) AS cost_micro
       FROM tool_uses
       WHERE ts >= ?
       GROUP BY name
       ORDER BY calls DESC
       LIMIT ?`,
    )
    .all(since, MAX_TOOLS) as Array<{
    name: string
    calls: number
    errors: number | null
    cost_micro: number
  }>

  // Per-tool: top calling agents (max 4 each).
  const byToolAgent = raw
    .prepare(
      `SELECT name, agent, COUNT(*) AS calls
       FROM tool_uses
       WHERE ts >= ? AND name IN (${byTool.map(() => '?').join(',') || "''"})
       GROUP BY name, agent
       ORDER BY calls DESC`,
    )
    .all(since, ...byTool.map((t) => t.name)) as Array<{
    name: string
    agent: string
    calls: number
  }>
  const agentsByTool = new Map<string, Array<{ agent: string; calls: number }>>()
  for (const row of byToolAgent) {
    const arr = agentsByTool.get(row.name) ?? []
    if (arr.length < 4) arr.push({ agent: row.agent, calls: row.calls })
    agentsByTool.set(row.name, arr)
  }

  const tools: ToolStat[] = byTool.map((t) => ({
    name: t.name,
    callCount: t.calls,
    errorCount: t.errors ?? 0,
    costUsd: t.cost_micro / 1_000_000,
    byAgent: agentsByTool.get(t.name) ?? [],
  }))

  const thomasExecutions = computeThomasExecutions(raw, since)
  const totalThomasExecutions = thomasExecutions.reduce((s, t) => s + t.callCount, 0)
  const totalThomasExecutionCostUsd = thomasExecutions.reduce(
    (s, t) => s + t.costUsd,
    0,
  )

  return {
    period,
    since,
    until: now,
    totalToolCalls,
    totalToolErrors,
    tools,
    thomasExecutions,
    totalThomasExecutions,
    totalThomasExecutionCostUsd,
  }
}

/** Aggregate captured tool_call actions by (toolName × providerId) in
 *  the period. Reads payload JSON via json_extract — the tool_call
 *  payload shape (toolName / providerId / backend / status) is fully
 *  known so we can index into it directly instead of fetching all
 *  payload rows. */
type RawExecRow = {
  tool_name: string | null
  provider_id: string | null
  backend: string | null
  status: string | null
  calls: number
  cost_micro: number | null
}

function computeThomasExecutions(
  raw: ReturnType<typeof getRawDb> extends Promise<infer R> ? R : never,
  since: number,
): ThomasExecutionStat[] {
  const rows = raw
    .prepare(
      `SELECT json_extract(ap.payload, '$.toolName')    AS tool_name,
              json_extract(ap.payload, '$.providerId')  AS provider_id,
              json_extract(ap.payload, '$.backend')     AS backend,
              json_extract(ap.payload, '$.status')      AS status,
              COUNT(*)                                  AS calls,
              COALESCE(SUM(a.cost_usd), 0)              AS cost_micro
       FROM actions a
       JOIN action_payloads ap ON ap.action_id = a.id
       WHERE a.kind = 'tool_call' AND a.ts >= ?
       GROUP BY tool_name, provider_id, backend, status
       ORDER BY calls DESC`,
    )
    .all(since) as RawExecRow[]

  // Fold (tool, provider, backend) across status rows so the UI sees
  // one card per (tool × provider) with separate call/error counters.
  type Key = string
  const merged = new Map<Key, ThomasExecutionStat>()
  for (const r of rows) {
    const toolName = r.tool_name ?? '(unknown)'
    const providerId = r.provider_id ?? '(unknown)'
    const backend = r.backend ?? ''
    const key: Key = `${toolName}|${providerId}|${backend}`
    const isError = r.status === 'error'
    const acc = merged.get(key) ?? {
      toolName,
      providerId,
      backend,
      callCount: 0,
      errorCount: 0,
      costUsd: 0,
    }
    acc.callCount += r.calls
    if (isError) acc.errorCount += r.calls
    acc.costUsd += (r.cost_micro ?? 0) / 1_000_000
    merged.set(key, acc)
  }
  return [...merged.values()].sort((a, b) => b.callCount - a.callCount)
}