// Aggregates that power /api/thomas. After we started persisting
// outcomes at ingest (see store/ingest-outcomes.ts), all of the heavy
// lifting moves to SQL — payload BLOBs are never read at query time.
// Cold-path cost dropped from ~30s to sub-100ms on a 1+ GB store.

import { type AgentType, THOMAS_VERSION, defaultAgentType } from '../outcomes/registry.ts'
import { getRawDb } from './db.ts'

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

export type ThomasSubAgent = {
  name: string
  outcomeCount: number
  outcomeCountVerified: number
  outcomeValueUsd: number
  outcomeValueUsdVerified: number
}

/** One running instance of an agent (e.g. a Claude Code window, an OpenClaw
 *  channel) within the period: its spend and the verified value it produced. */
export type ThomasInstance = {
  instanceId: string
  costUsd: number
  outcomeValueUsd: number
  outcomeCount: number
  taskCount: number
}

export type ThomasPerAgent = {
  agent: string
  agentType: AgentType
  thomas: number
  thomasVerified: number
  costUsd: number
  outcomeValueUsd: number
  outcomeValueUsdVerified: number
  outcomeCount: number
  outcomeCountVerified: number
  taskCount: number
  durationMs: number
  subAgents: ThomasSubAgent[]
  instances: ThomasInstance[]
}

export type ThomasPerModel = {
  model: string
  costUsd: number
  outcomeCount: number
  outcomeValueUsd: number
}

export type ThomasTopTask = {
  threadId: string
  title: string | null
  agent: string
  costUsd: number
  runCount: number
  actionCount: number
  errorCount: number
}

export type ThomasResult = {
  version: string
  period: ThomasPeriod
  since: number
  until: number
  thomas: number
  thomasVerified: number
  costUsd: number
  outcomeValueUsd: number
  outcomeValueUsdVerified: number
  totalActions: number
  totalOutcomes: number
  totalOutcomesVerified: number
  totalTokens: number
  /** Cache-hit input tokens — cheapest bucket. */
  totalCachedIn: number
  /** Non-cache-hit input tokens (fresh input + cache_write) — full input price or premium. */
  totalFreshIn: number
  /** Output tokens. */
  totalOut: number
  actionRate: number
  outcomeRate: number
  outcomeRateVerified: number
  outcomeCounts: Record<string, number>
  outcomeCountsVerified: Record<string, number>
  perAgent: ThomasPerAgent[]
  perModel: ThomasPerModel[]
  topTasks: ThomasTopTask[]
}

function sinceForPeriod(period: ThomasPeriod, 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 computeThomas(period: ThomasPeriod): Promise<ThomasResult> {
  const raw = await getRawDb()
  const now = Date.now()
  const since = sinceForPeriod(period, now)

  // ── 1. action / cost / token aggregates from the actions table ──
  // These are all indexed columns; the kind+ts index covers the filter.
  const actionAggRow = raw
    .prepare(
      `SELECT
         t.agent_id AS agent,
         COALESCE(SUM(a.cost_usd), 0) AS cost_micro,
         COALESCE(SUM(a.tokens_in), 0) AS tokens_in,
         COALESCE(SUM(a.tokens_out), 0) AS tokens_out,
         COALESCE(SUM(a.cache_read_tokens), 0) AS cache_read,
         COALESCE(SUM(a.cache_write_tokens), 0) AS cache_write,
         COALESCE(SUM(a.dur_ms), 0) AS dur_ms,
         COUNT(DISTINCT a.thread_id) AS task_count
       FROM actions a
       INNER JOIN threads t ON t.id = a.thread_id
       WHERE a.ts >= ?
       GROUP BY t.agent_id`,
    )
    .all(since) as Array<{
    agent: string
    cost_micro: number
    tokens_in: number
    tokens_out: number
    cache_read: number
    cache_write: number
    dur_ms: number
    task_count: number
  }>

  // Action counter approximates the legacy "tool_use + non-idle" rule
  // by using tool_uses where it exists (better signal than counting
  // rows) and falling back to model_call row count where there are no
  // tool_uses for that row.
  const actionCountByAgent = new Map<string, number>()
  const toolUseRows = raw
    .prepare(
      `SELECT agent, COUNT(*) AS n
       FROM tool_uses
       WHERE ts >= ?
       GROUP BY agent`,
    )
    .all(since) as Array<{ agent: string; n: number }>
  for (const r of toolUseRows) actionCountByAgent.set(r.agent, r.n)

  // Add raw model_call counts as a baseline (so agents with no
  // tool_use rows still have action_rate). This avoids double-counting
  // because tool_use rows already include their parent's contribution.
  const modelCallNoToolRows = raw
    .prepare(
      `SELECT t.agent_id AS agent, COUNT(*) AS n
       FROM actions a
       INNER JOIN threads t ON t.id = a.thread_id
       WHERE a.kind = 'model_call' AND a.ts >= ?
         AND NOT EXISTS (SELECT 1 FROM tool_uses tu WHERE tu.action_id = a.id)
       GROUP BY t.agent_id`,
    )
    .all(since) as Array<{ agent: string; n: number }>
  for (const r of modelCallNoToolRows) {
    actionCountByAgent.set(r.agent, (actionCountByAgent.get(r.agent) ?? 0) + r.n)
  }

  // ── 2. outcome aggregates from the outcomes table ─────────────
  const outcomeAggRow = raw
    .prepare(
      `SELECT
         agent,
         COUNT(*) AS outcomes,
         COALESCE(SUM(value_usd_micro), 0) AS value_micro,
         COALESCE(SUM(CASE WHEN verified THEN 1 ELSE 0 END), 0) AS outcomes_verified,
         COALESCE(SUM(CASE WHEN verified THEN value_usd_micro ELSE 0 END), 0) AS value_micro_verified
       FROM outcomes
       WHERE ts >= ?
       GROUP BY agent`,
    )
    .all(since) as Array<{
    agent: string
    outcomes: number
    value_micro: number
    outcomes_verified: number
    value_micro_verified: number
  }>

  // Outcome counts by kind (overall + verified).
  const outcomeCountsRows = raw
    .prepare(
      `SELECT kind, COUNT(*) AS n,
              SUM(CASE WHEN verified THEN 1 ELSE 0 END) AS n_verified
       FROM outcomes
       WHERE ts >= ?
       GROUP BY kind`,
    )
    .all(since) as Array<{ kind: string; n: number; n_verified: number }>

  const outcomeCounts: Record<string, number> = {}
  const outcomeCountsVerified: Record<string, number> = {}
  for (const r of outcomeCountsRows) {
    outcomeCounts[r.kind] = r.n
    outcomeCountsVerified[r.kind] = r.n_verified
  }

  // Per-agent sub-agent breakdown.
  const subAgentRows = raw
    .prepare(
      `SELECT agent, sub_agent AS name,
              COUNT(*) AS outcomes,
              COALESCE(SUM(value_usd_micro), 0) AS value_micro,
              COALESCE(SUM(CASE WHEN verified THEN 1 ELSE 0 END), 0) AS outcomes_verified,
              COALESCE(SUM(CASE WHEN verified THEN value_usd_micro ELSE 0 END), 0) AS value_micro_verified
       FROM outcomes
       WHERE ts >= ? AND sub_agent IS NOT NULL
       GROUP BY agent, sub_agent
       ORDER BY value_micro DESC`,
    )
    .all(since) as Array<{
    agent: string
    name: string
    outcomes: number
    value_micro: number
    outcomes_verified: number
    value_micro_verified: number
  }>
  const subAgentsByAgent = new Map<string, ThomasSubAgent[]>()
  for (const r of subAgentRows) {
    const arr = subAgentsByAgent.get(r.agent) ?? []
    arr.push({
      name: r.name,
      outcomeCount: r.outcomes,
      outcomeCountVerified: r.outcomes_verified,
      outcomeValueUsd: r.value_micro / 1_000_000,
      outcomeValueUsdVerified: r.value_micro_verified / 1_000_000,
    })
    subAgentsByAgent.set(r.agent, arr)
  }

  // ── per-instance breakdown (which window/channel/session) ─────
  // Spend + tasks come from actions (instance_id); value + outcome count
  // from outcomes (instance_id). Merge by (agent, instance).
  const instancesByAgent = new Map<string, Map<string, ThomasInstance>>()
  const getInstance = (agent: string, instanceId: string): ThomasInstance => {
    let byInst = instancesByAgent.get(agent)
    if (!byInst) {
      byInst = new Map()
      instancesByAgent.set(agent, byInst)
    }
    let inst = byInst.get(instanceId)
    if (!inst) {
      inst = { instanceId, costUsd: 0, outcomeValueUsd: 0, outcomeCount: 0, taskCount: 0 }
      byInst.set(instanceId, inst)
    }
    return inst
  }
  const instCostRows = raw
    .prepare(
      `SELECT source_agent AS agent, instance_id AS instance,
              COALESCE(SUM(cost_usd), 0) AS cost_micro,
              COUNT(DISTINCT thread_id) AS tasks
       FROM actions
       WHERE ts >= ? AND instance_id IS NOT NULL
       GROUP BY source_agent, instance_id`,
    )
    .all(since) as Array<{ agent: string; instance: string; cost_micro: number; tasks: number }>
  for (const r of instCostRows) {
    const inst = getInstance(r.agent, r.instance)
    inst.costUsd = r.cost_micro / 1_000_000
    inst.taskCount = r.tasks
  }
  const instValueRows = raw
    .prepare(
      `SELECT agent, instance_id AS instance,
              COALESCE(SUM(value_usd_micro), 0) AS value_micro,
              COUNT(*) AS outcomes
       FROM outcomes
       WHERE ts >= ? AND instance_id IS NOT NULL
       GROUP BY agent, instance_id`,
    )
    .all(since) as Array<{ agent: string; instance: string; value_micro: number; outcomes: number }>
  for (const r of instValueRows) {
    const inst = getInstance(r.agent, r.instance)
    inst.outcomeValueUsd = r.value_micro / 1_000_000
    inst.outcomeCount = r.outcomes
  }

  // ── per-model spend + verified outcomes ───────────────────────
  // Spend comes from model_call actions; outcomes are attributed to the
  // model of the action they were detected from. The kind+model+ts index
  // covers the cost query.
  const modelCostRows = raw
    .prepare(
      `SELECT model, COALESCE(SUM(cost_usd), 0) AS cost_micro
       FROM actions
       WHERE kind = 'model_call' AND ts >= ?
         AND model IS NOT NULL AND model != ''
       GROUP BY model`,
    )
    .all(since) as Array<{ model: string; cost_micro: number }>

  const modelOutcomeRows = raw
    .prepare(
      `SELECT a.model AS model,
              COUNT(*) AS outcomes,
              COALESCE(SUM(o.value_usd_micro), 0) AS value_micro
       FROM outcomes o
       INNER JOIN actions a ON a.id = o.action_id
       WHERE o.ts >= ? AND o.verified = 1
         AND a.model IS NOT NULL AND a.model != ''
       GROUP BY a.model`,
    )
    .all(since) as Array<{ model: string; outcomes: number; value_micro: number }>

  const perModelMap = new Map<string, ThomasPerModel>()
  for (const r of modelCostRows) {
    perModelMap.set(r.model, {
      model: r.model,
      costUsd: r.cost_micro / 1_000_000,
      outcomeCount: 0,
      outcomeValueUsd: 0,
    })
  }
  for (const r of modelOutcomeRows) {
    const entry = perModelMap.get(r.model) ?? {
      model: r.model,
      costUsd: 0,
      outcomeCount: 0,
      outcomeValueUsd: 0,
    }
    entry.outcomeCount = r.outcomes
    entry.outcomeValueUsd = r.value_micro / 1_000_000
    perModelMap.set(r.model, entry)
  }
  // Drop models with neither spend nor outcomes — these are failed calls
  // (e.g. a 404 from probing a non-existent model id like
  // "claude-opus-4-7[1m]"), not real usage worth a row.
  const perModel = [...perModelMap.values()]
    .filter((m) => m.costUsd > 0 || m.outcomeCount > 0)
    .sort((a, b) => b.costUsd - a.costUsd)

  // ── costliest tasks (the spend ranking the Home page shows) ───
  // Per-task (thread) spend, with run/action counts and the error count —
  // a costly task with errored model calls is the signature of a retry
  // storm. cost_usd / http_status are columns, so no payload is read.
  const topTaskRows = raw
    .prepare(
      `SELECT a.thread_id AS thread_id,
              t.agent_id AS agent,
              t.title AS title,
              COALESCE(SUM(a.cost_usd), 0) AS cost_micro,
              COUNT(DISTINCT a.run_id) AS run_count,
              COUNT(*) AS action_count,
              COALESCE(SUM(CASE WHEN a.http_status >= 400 THEN 1 ELSE 0 END), 0) AS error_count
       FROM actions a
       INNER JOIN threads t ON t.id = a.thread_id
       WHERE a.ts >= ?
       GROUP BY a.thread_id
       ORDER BY cost_micro DESC
       LIMIT 12`,
    )
    .all(since) as Array<{
    thread_id: string
    agent: string
    title: string | null
    cost_micro: number
    run_count: number
    action_count: number
    error_count: number
  }>
  const topTasks: ThomasTopTask[] = topTaskRows
    .filter((r) => r.cost_micro > 0)
    .map((r) => ({
      threadId: r.thread_id,
      title: r.title,
      agent: r.agent,
      costUsd: r.cost_micro / 1_000_000,
      runCount: r.run_count,
      actionCount: r.action_count,
      errorCount: r.error_count,
    }))

  // ── 3. merge per-agent rows ───────────────────────────────────
  const outcomeByAgent = new Map<string, (typeof outcomeAggRow)[number]>()
  for (const r of outcomeAggRow) outcomeByAgent.set(r.agent, r)

  const perAgentResult: ThomasPerAgent[] = []
  let totalCostMicro = 0
  let totalTokens = 0
  let totalCachedIn = 0
  let totalFreshIn = 0
  let totalOut = 0
  let totalOutcomes = 0
  let totalOutcomesVerified = 0
  let totalOutcomeValueUsd = 0
  let totalOutcomeValueUsdVerified = 0
  let totalActions = 0

  for (const a of actionAggRow) {
    totalCostMicro += a.cost_micro
    totalTokens += a.tokens_in + a.tokens_out + a.cache_read + a.cache_write
    totalCachedIn += a.cache_read
    // why: cache_write tokens are non-cache-hit input — they were freshly
    // computed (and additionally written to cache at ~1.25x), so they
    // belong in the "non-cache-hit" bucket alongside tokens_in.
    totalFreshIn += a.tokens_in + a.cache_write
    totalOut += a.tokens_out
    totalActions += actionCountByAgent.get(a.agent) ?? 0
    const o = outcomeByAgent.get(a.agent)
    const outcomes = o?.outcomes ?? 0
    const outcomesVerified = o?.outcomes_verified ?? 0
    const valueUsd = (o?.value_micro ?? 0) / 1_000_000
    const valueUsdVerified = (o?.value_micro_verified ?? 0) / 1_000_000
    totalOutcomes += outcomes
    totalOutcomesVerified += outcomesVerified
    totalOutcomeValueUsd += valueUsd
    totalOutcomeValueUsdVerified += valueUsdVerified
    const agentCost = a.cost_micro / 1_000_000
    perAgentResult.push({
      agent: a.agent,
      agentType: defaultAgentType(a.agent),
      thomas: agentCost > 0 ? valueUsd / agentCost : 0,
      thomasVerified: agentCost > 0 ? valueUsdVerified / agentCost : 0,
      costUsd: agentCost,
      outcomeValueUsd: valueUsd,
      outcomeValueUsdVerified: valueUsdVerified,
      outcomeCount: outcomes,
      outcomeCountVerified: outcomesVerified,
      taskCount: a.task_count,
      durationMs: a.dur_ms,
      subAgents: subAgentsByAgent.get(a.agent) ?? [],
      instances: [...(instancesByAgent.get(a.agent)?.values() ?? [])].sort(
        (x, y) => y.costUsd - x.costUsd,
      ),
    })
  }
  perAgentResult.sort((a, b) => b.outcomeValueUsd - a.outcomeValueUsd)

  const costUsd = totalCostMicro / 1_000_000
  const thomas = costUsd > 0 ? totalOutcomeValueUsd / costUsd : 0
  const thomasVerified = costUsd > 0 ? totalOutcomeValueUsdVerified / costUsd : 0
  const actionRate = totalTokens > 0 ? (totalActions / totalTokens) * 1_000_000 : 0
  const outcomeRate = totalActions > 0 ? totalOutcomes / totalActions : 0
  const outcomeRateVerified = totalActions > 0 ? totalOutcomesVerified / totalActions : 0

  return {
    version: THOMAS_VERSION,
    period,
    since,
    until: now,
    thomas,
    thomasVerified,
    costUsd,
    outcomeValueUsd: totalOutcomeValueUsd,
    outcomeValueUsdVerified: totalOutcomeValueUsdVerified,
    totalActions,
    totalOutcomes,
    totalOutcomesVerified,
    totalTokens,
    totalCachedIn,
    totalFreshIn,
    totalOut,
    actionRate,
    outcomeRate,
    outcomeRateVerified,
    outcomeCounts,
    outcomeCountsVerified,
    perAgent: perAgentResult,
    perModel,
    topTasks,
  }
}

/** Exposed for the dashboard tooltip — outcome label + value lookup. */
import { OUTCOME_REGISTRY } from '../outcomes/registry.ts'
export function outcomeMetadata(kind: string): { label: string; valueUsd: number } | null {
  const def = OUTCOME_REGISTRY[kind]
  if (!def) return null
  return {
    label: def.label,
    valueUsd: (def.humanMinutes * 180) / 60, // $180/h wage benchmark
  }
}