import type { DatabaseAdapter } from "../db.js";
import { ValidationError, assertAllowedKeys } from "../errors.js";
import { ALL_SOURCES, SESSION_SOURCES, type AnalyticsSource } from "../sources.js";
import { parseOptionalEnumArray } from "../validation.js";

const GROUP_BY_VALUES = ["source", "project", "model", "day", "event_type", "event_name"] as const;

interface SummaryInput {
  sources?: AnalyticsSource[];
  event_type?: "mcp" | "skill" | "hook" | "agent_tool" | "agent_lifecycle" | "hotword";
  date_from?: string;
  date_to?: string;
  group_by?: string[];
}

function parseIso(value: unknown): string | undefined {
  if (value === undefined) return undefined;
  if (typeof value !== "string" || !Number.isFinite(Date.parse(value))) {
    throw new ValidationError("date_from/date_to must be valid ISO strings.");
  }
  return new Date(value).toISOString();
}

function parseInput(args: unknown): SummaryInput {
  const obj = (args && typeof args === "object" && !Array.isArray(args) ? args : {}) as Record<string, unknown>;
  assertAllowedKeys(obj, ["sources", "event_type", "date_from", "date_to", "group_by"]);

  const parsedSources = parseOptionalEnumArray(obj.sources, ALL_SOURCES, "sources");
  const group_by = parseOptionalEnumArray(obj.group_by, GROUP_BY_VALUES, "group_by");
  let event_type: SummaryInput["event_type"];
  if (obj.event_type !== undefined) {
    if (obj.event_type !== "mcp" && obj.event_type !== "skill" && obj.event_type !== "hook" && obj.event_type !== "agent_tool" && obj.event_type !== "agent_lifecycle" && obj.event_type !== "hotword") {
      throw new ValidationError("Invalid event_type.");
    }
    event_type = obj.event_type;
  }

  return {
    sources: parsedSources,
    event_type,
    date_from: parseIso(obj.date_from),
    date_to: parseIso(obj.date_to),
    group_by
  };
}

export async function querySummary(db: DatabaseAdapter, args: unknown): Promise<Record<string, unknown>> {
  const input = parseInput(args);
  const sources = input.sources ?? [...ALL_SOURCES];
  const sessionSources = sources.filter((s) => s !== "hook_log");
  const eventSources = sources;

  const sessionWhere: string[] = [];
  const sessionParams: unknown[] = [];
  if (sessionSources.length > 0) {
    sessionWhere.push(`source IN (${sessionSources.map(() => "?").join(",")})`);
    sessionParams.push(...sessionSources);
  } else {
    sessionWhere.push("1=0");
  }
  if (input.date_from) {
    sessionWhere.push("updated_at >= ?");
    sessionParams.push(input.date_from);
  }
  if (input.date_to) {
    sessionWhere.push("updated_at <= ?");
    sessionParams.push(input.date_to);
  }
  sessionWhere.push("COALESCE(session_kind, 'main') = 'main'");
  sessionWhere.push("user_message_count > 0");
  const sessionTotals = await db.get<{ sessions: number; messages: number }>(
    `SELECT COUNT(*) as sessions, COALESCE(SUM(user_message_count),0) as messages FROM sessions WHERE ${sessionWhere.join(" AND ")}`,
    sessionParams
  );
  const tokenTotals = await db.get<{ input_tokens: number; output_tokens: number; reasoning_tokens: number; cache_read_tokens: number; cache_write_tokens: number; available: number; missing: number; partial: number }>(
    `SELECT COALESCE(SUM(COALESCE(mm.input_tokens,0)),0) input_tokens, COALESCE(SUM(COALESCE(mm.output_tokens,0)),0) output_tokens,
      COALESCE(SUM(mm.reasoning_tokens),0) reasoning_tokens, COALESCE(SUM(mm.cache_read_tokens),0) cache_read_tokens,
      COALESCE(SUM(mm.cache_write_tokens),0) cache_write_tokens,
      COALESCE(SUM(CASE WHEN mm.token_available=1 THEN 1 ELSE 0 END),0) available,
      COALESCE(SUM(CASE WHEN mm.token_available=0 THEN 1 ELSE 0 END),0) missing,
      COALESCE(SUM(CASE WHEN mm.partial_token_data=1 THEN 1 ELSE 0 END),0) partial
     FROM sessions s JOIN message_metrics mm ON mm.session_id=s.id WHERE ${sessionWhere.join(" AND ")}`,
    sessionParams
  );
  const preferred = await db.get<{ model: string; observed_tokens: number; known_token_count: number }>(
    `SELECT COALESCE(NULLIF(mm.model,''),NULLIF(s.model_primary,'')) model,
      COALESCE(SUM(COALESCE(mm.input_tokens,0)+COALESCE(mm.output_tokens,0)),0) observed_tokens,
      COUNT(*) known_token_count
     FROM sessions s JOIN message_metrics mm ON mm.session_id=s.id
     WHERE ${sessionWhere.join(" AND ")} AND COALESCE(NULLIF(mm.model,''),NULLIF(s.model_primary,'')) IS NOT NULL
     GROUP BY COALESCE(NULLIF(mm.model,''),NULLIF(s.model_primary,''))
     HAVING observed_tokens > 0 ORDER BY observed_tokens DESC, model ASC LIMIT 1`, sessionParams
  );
  const modelCoverage = await db.get<{ known: number; unknown: number }>(
    `SELECT COALESCE(SUM(CASE WHEN COALESCE(NULLIF(mm.model,''),NULLIF(s.model_primary,'')) IS NOT NULL THEN COALESCE(mm.input_tokens,0)+COALESCE(mm.output_tokens,0) ELSE 0 END),0) known, COALESCE(SUM(CASE WHEN COALESCE(NULLIF(mm.model,''),NULLIF(s.model_primary,'')) IS NULL THEN COALESCE(mm.input_tokens,0)+COALESCE(mm.output_tokens,0) ELSE 0 END),0) unknown FROM sessions s JOIN message_metrics mm ON mm.session_id=s.id WHERE ${sessionWhere.join(" AND ")}`,
    sessionParams
  );

  const buildRuntimeEventFilter = (source?: AnalyticsSource, includeEventType = true): { where: string[]; params: unknown[] } => {
    const where: string[] = [];
    const params: unknown[] = [];
    if (source) {
      where.push("source = ?");
      params.push(source);
    } else if (eventSources.length > 0) {
      where.push(`source IN (${eventSources.map(() => "?").join(",")})`);
      params.push(...eventSources);
    }
    if (input.date_from) {
      where.push("occurred_at >= ?");
      params.push(input.date_from);
    }
    if (input.date_to) {
      where.push("occurred_at <= ?");
      params.push(input.date_to);
    }
    where.push("is_self_event = 0");
    if (includeEventType && input.event_type) {
      where.push("event_type = ?");
      params.push(input.event_type);
    }
    return { where, params };
  };
  const { where: eventWhere, params: eventParams } = buildRuntimeEventFilter();
  const { where: eventBaseWhere, params: eventBaseParams } = buildRuntimeEventFilter(undefined, false);

  const eventTotals = await db.get<{ runtime_events: number; mcp_calls: number; hook_events: number; skill_hints: number; skill_declarations: number; skill_signals: number; agent_tools: number; agent_lifecycle: number }>(
    `SELECT
       COUNT(*) as runtime_events,
       SUM(CASE WHEN event_type='mcp' THEN 1 ELSE 0 END) as mcp_calls,
       SUM(CASE WHEN event_type='hook' THEN 1 ELSE 0 END) as hook_events,
       SUM(CASE WHEN event_type='skill' AND event_origin='hook_hint' THEN 1 ELSE 0 END) as skill_hints,
       SUM(CASE WHEN event_type='skill' AND event_origin='observed_structured' AND hook_event='SkillDeclared' THEN 1 ELSE 0 END) as skill_declarations,
       SUM(CASE WHEN event_type='skill' THEN 1 ELSE 0 END) as skill_signals,
       SUM(CASE WHEN event_type='agent_tool' THEN 1 ELSE 0 END) as agent_tools,
       SUM(CASE WHEN event_type='agent_lifecycle' THEN 1 ELSE 0 END) as agent_lifecycle
     FROM runtime_events WHERE ${eventWhere.join(" AND ")}`,
    eventParams
  );

  const hookMetrics = await db.get<{
    invocations: number;
    outcome_evaluations: number;
    none: number;
    mcp: number;
    skill: number;
    mcp_and_skill: number;
    suggested_invocations: number;
    lifecycle: number;
    prompt: number;
    pretool: number;
    mcp_suggestion_count: number;
    skill_suggestion_count: number;
  }>(
    `SELECT
       COUNT(*) AS invocations,
       COALESCE(SUM(CASE WHEN suggestion_outcome IS NOT NULL THEN 1 ELSE 0 END), 0) AS outcome_evaluations,
       COALESCE(SUM(CASE WHEN suggestion_outcome = 'none' THEN 1 ELSE 0 END), 0) AS none,
       COALESCE(SUM(CASE WHEN suggestion_outcome = 'mcp' THEN 1 ELSE 0 END), 0) AS mcp,
       COALESCE(SUM(CASE WHEN suggestion_outcome = 'skill' THEN 1 ELSE 0 END), 0) AS skill,
       COALESCE(SUM(CASE WHEN suggestion_outcome = 'mcp_and_skill' THEN 1 ELSE 0 END), 0) AS mcp_and_skill,
       COALESCE(SUM(CASE WHEN suggestion_outcome IN ('mcp', 'skill', 'mcp_and_skill') THEN 1 ELSE 0 END), 0) AS suggested_invocations,
       COALESCE(SUM(CASE WHEN hook_phase = 'lifecycle' THEN 1 ELSE 0 END), 0) AS lifecycle,
       COALESCE(SUM(CASE WHEN hook_phase = 'prompt' THEN 1 ELSE 0 END), 0) AS prompt,
       COALESCE(SUM(CASE WHEN hook_phase = 'pretool' THEN 1 ELSE 0 END), 0) AS pretool,
       COALESCE(SUM(COALESCE(mcp_suggestion_count, 0)), 0) AS mcp_suggestion_count,
       COALESCE(SUM(COALESCE(skill_suggestion_count, 0)), 0) AS skill_suggestion_count
     FROM runtime_events
     WHERE ${eventWhere.join(' AND ')}
       AND event_type = 'hook'
       AND hook_phase IS NOT NULL`,
    eventParams
  );
  const hooksByName = await db.all<{
    hook_name: string | null;
    hook_event: string | null;
    hook_phase: string;
    invocations: number;
    outcome_evaluations: number;
    none: number;
    mcp: number;
    skill: number;
    mcp_and_skill: number;
    suggested_invocations: number;
    suggestion_rate: number;
  }>(
    `SELECT
       hook_name,
       hook_event,
       hook_phase,
       COUNT(*) AS invocations,
       COALESCE(SUM(CASE WHEN suggestion_outcome IS NOT NULL THEN 1 ELSE 0 END), 0) AS outcome_evaluations,
       COALESCE(SUM(CASE WHEN suggestion_outcome = 'none' THEN 1 ELSE 0 END), 0) AS none,
       COALESCE(SUM(CASE WHEN suggestion_outcome = 'mcp' THEN 1 ELSE 0 END), 0) AS mcp,
       COALESCE(SUM(CASE WHEN suggestion_outcome = 'skill' THEN 1 ELSE 0 END), 0) AS skill,
       COALESCE(SUM(CASE WHEN suggestion_outcome = 'mcp_and_skill' THEN 1 ELSE 0 END), 0) AS mcp_and_skill,
       COALESCE(SUM(CASE WHEN suggestion_outcome IN ('mcp', 'skill', 'mcp_and_skill') THEN 1 ELSE 0 END), 0) AS suggested_invocations,
       CASE WHEN SUM(CASE WHEN suggestion_outcome IS NOT NULL THEN 1 ELSE 0 END) > 0
         THEN SUM(CASE WHEN suggestion_outcome IN ('mcp', 'skill', 'mcp_and_skill') THEN 1 ELSE 0 END) * 100.0 /
           SUM(CASE WHEN suggestion_outcome IS NOT NULL THEN 1 ELSE 0 END)
         ELSE 0 END AS suggestion_rate
     FROM runtime_events
     WHERE ${eventWhere.join(' AND ')}
       AND event_type = 'hook'
       AND hook_phase IS NOT NULL
     GROUP BY hook_name, hook_event, hook_phase
     ORDER BY invocations DESC, hook_name ASC, hook_event ASC, hook_phase ASC`,
    eventParams
  );
  const hooksBySource = await db.all<{
    source: AnalyticsSource;
    hook_name: string | null;
    hook_event: string | null;
    hook_phase: string;
    invocations: number;
    outcome_evaluations: number;
    none: number;
    mcp: number;
    skill: number;
    mcp_and_skill: number;
    suggested_invocations: number;
    suggestion_rate: number;
  }>(
    `SELECT
       source,
       hook_name,
       hook_event,
       hook_phase,
       COUNT(*) AS invocations,
       COALESCE(SUM(CASE WHEN suggestion_outcome IS NOT NULL THEN 1 ELSE 0 END), 0) AS outcome_evaluations,
       COALESCE(SUM(CASE WHEN suggestion_outcome = 'none' THEN 1 ELSE 0 END), 0) AS none,
       COALESCE(SUM(CASE WHEN suggestion_outcome = 'mcp' THEN 1 ELSE 0 END), 0) AS mcp,
       COALESCE(SUM(CASE WHEN suggestion_outcome = 'skill' THEN 1 ELSE 0 END), 0) AS skill,
       COALESCE(SUM(CASE WHEN suggestion_outcome = 'mcp_and_skill' THEN 1 ELSE 0 END), 0) AS mcp_and_skill,
       COALESCE(SUM(CASE WHEN suggestion_outcome IN ('mcp', 'skill', 'mcp_and_skill') THEN 1 ELSE 0 END), 0) AS suggested_invocations,
       CASE WHEN SUM(CASE WHEN suggestion_outcome IS NOT NULL THEN 1 ELSE 0 END) > 0
         THEN SUM(CASE WHEN suggestion_outcome IN ('mcp', 'skill', 'mcp_and_skill') THEN 1 ELSE 0 END) * 100.0 /
           SUM(CASE WHEN suggestion_outcome IS NOT NULL THEN 1 ELSE 0 END)
         ELSE 0 END AS suggestion_rate
     FROM runtime_events
     WHERE ${eventWhere.join(' AND ')}
       AND event_type = 'hook'
       AND hook_phase IS NOT NULL
     GROUP BY source, hook_name, hook_event, hook_phase
     ORDER BY invocations DESC, source ASC, hook_name ASC, hook_event ASC, hook_phase ASC`,
    eventParams
  );

  const promptMetrics = await db.get<{ prompts_total: number }>(
    `SELECT COALESCE(SUM(CASE WHEN event_type = 'hook' AND hook_phase = 'prompt' AND hook_event = 'UserPromptSubmit' THEN 1 ELSE 0 END), 0) AS prompts_total
     FROM runtime_events
     WHERE ${eventBaseWhere.join(' AND ')}`,
    eventBaseParams
  );

  const hotwordMetrics = await db.get<{
    prompts_with_hotword: number;
    quick: number;
    agents: number;
    quick_agents: number;
    quick_total: number;
    agents_total: number;
    quick_light: number;
    quick_overridden: number;
    orchestration_required: number;
  }>(
    `SELECT
       COALESCE(SUM(CASE WHEN event_type = 'hotword' THEN 1 ELSE 0 END), 0) AS prompts_with_hotword,
       COALESCE(SUM(CASE WHEN hotword_mode = 'quick' THEN 1 ELSE 0 END), 0) AS quick,
       COALESCE(SUM(CASE WHEN hotword_mode = 'agents' THEN 1 ELSE 0 END), 0) AS agents,
       COALESCE(SUM(CASE WHEN hotword_mode = 'quick_agents' THEN 1 ELSE 0 END), 0) AS quick_agents,
       COALESCE(SUM(CASE WHEN hotword_mode IN ('quick', 'quick_agents') THEN 1 ELSE 0 END), 0) AS quick_total,
       COALESCE(SUM(CASE WHEN hotword_mode IN ('agents', 'quick_agents') THEN 1 ELSE 0 END), 0) AS agents_total,
       COALESCE(SUM(CASE WHEN hotword_mode IN ('quick', 'quick_agents') AND context_mode = 'light' THEN 1 ELSE 0 END), 0) AS quick_light,
       COALESCE(SUM(CASE WHEN quick_overridden = 1 THEN 1 ELSE 0 END), 0) AS quick_overridden,
       COALESCE(SUM(CASE WHEN event_type = 'hotword' AND subagent_mode = 'required' THEN 1 ELSE 0 END), 0) AS orchestration_required
     FROM runtime_events
     WHERE ${eventWhere.join(' AND ')}
       AND event_type = 'hotword'`,
    eventParams
  );

  const skillMetrics = await db.get<{
    hints: number;
    declarations: number;
    signals: number;
  }>(
    `SELECT
       COALESCE(SUM(CASE WHEN event_origin = 'hook_hint' AND hook_event = 'SkillHint' THEN 1 ELSE 0 END), 0) AS hints,
       COALESCE(SUM(CASE WHEN event_origin = 'observed_structured' AND hook_event = 'SkillDeclared' THEN 1 ELSE 0 END), 0) AS declarations,
       COUNT(*) AS signals
     FROM runtime_events
     WHERE ${eventWhere.join(' AND ')}
       AND event_type = 'skill'`,
    eventParams
  );
  const normalizedSkillName = `COALESCE(
    NULLIF(TRIM(skill_name), ''),
    NULLIF(TRIM(CASE WHEN event_name LIKE 'skill.%' THEN SUBSTR(event_name, 7) ELSE event_name END), ''),
    'unknown-skill'
  )`;
  const skillsByName = await db.all<{
    skill_name: string;
    hints: number;
    declarations: number;
    signals: number;
  }>(
    `SELECT
       ${normalizedSkillName} AS skill_name,
       COALESCE(SUM(CASE WHEN event_origin = 'hook_hint' AND hook_event = 'SkillHint' THEN 1 ELSE 0 END), 0) AS hints,
       COALESCE(SUM(CASE WHEN event_origin = 'observed_structured' AND hook_event = 'SkillDeclared' THEN 1 ELSE 0 END), 0) AS declarations,
       COUNT(*) AS signals
     FROM runtime_events
     WHERE ${eventWhere.join(' AND ')}
       AND event_type = 'skill'
     GROUP BY ${normalizedSkillName}
     ORDER BY signals DESC, skill_name ASC`,
    eventParams
  );
  const skillsBySource = await db.all<{
    source: AnalyticsSource;
    skill_name: string;
    hints: number;
    declarations: number;
    signals: number;
  }>(
    `SELECT
       source, ${normalizedSkillName} AS skill_name,
       COALESCE(SUM(CASE WHEN event_origin = 'hook_hint' AND hook_event = 'SkillHint' THEN 1 ELSE 0 END), 0) AS hints,
       COALESCE(SUM(CASE WHEN event_origin = 'observed_structured' AND hook_event = 'SkillDeclared' THEN 1 ELSE 0 END), 0) AS declarations,
       COUNT(*) AS signals
     FROM runtime_events
     WHERE ${eventWhere.join(' AND ')}
       AND event_type = 'skill'
     GROUP BY source, ${normalizedSkillName}
     ORDER BY signals DESC, source ASC, skill_name ASC`,
    eventParams
  );

  const mcpMetrics = await db.get<{
    real_calls: number;
    server_hints: number;
    tool_hints: number;
  }>(
    `SELECT
       COALESCE(SUM(CASE WHEN event_type = 'mcp' THEN 1 ELSE 0 END), 0) AS real_calls,
       COALESCE(SUM(CASE WHEN event_type = 'hook' AND event_origin = 'hook_hint' AND hook_event = 'McpHint' AND NULLIF(TRIM(tool_name), '') IS NULL THEN 1 ELSE 0 END), 0) AS server_hints,
       COALESCE(SUM(CASE WHEN event_type = 'hook' AND event_origin = 'hook_hint' AND hook_event = 'McpHint' AND NULLIF(TRIM(tool_name), '') IS NOT NULL THEN 1 ELSE 0 END), 0) AS tool_hints
     FROM runtime_events
     WHERE ${eventWhere.join(' AND ')}`,
    eventParams
  );
  const normalizedMcpServerName = `COALESCE(
    NULLIF(TRIM(mcp_server_name), ''),
    CASE
      WHEN event_type = 'mcp' AND INSTR(COALESCE(event_name, ''), '.') > 1
        THEN SUBSTR(event_name, 1, INSTR(event_name, '.') - 1)
    END,
    'unknown-server'
  )`;
  const normalizedMcpToolName = `COALESCE(
    NULLIF(TRIM(tool_name), ''),
    CASE
      WHEN event_type = 'mcp' AND INSTR(COALESCE(event_name, ''), '.') > 0
        THEN NULLIF(TRIM(SUBSTR(event_name, INSTR(event_name, '.') + 1)), '')
    END,
    'unknown-tool'
  )`;
  const mcpByServer = await db.all<{
    mcp_server_name: string;
    real_calls: number;
    server_hints: number;
    tool_hints: number;
  }>(
    `SELECT
       ${normalizedMcpServerName} AS mcp_server_name,
       COALESCE(SUM(CASE WHEN event_type = 'mcp' THEN 1 ELSE 0 END), 0) AS real_calls,
       COALESCE(SUM(CASE WHEN event_type = 'hook' AND event_origin = 'hook_hint' AND hook_event = 'McpHint' AND NULLIF(TRIM(tool_name), '') IS NULL THEN 1 ELSE 0 END), 0) AS server_hints,
       COALESCE(SUM(CASE WHEN event_type = 'hook' AND event_origin = 'hook_hint' AND hook_event = 'McpHint' AND NULLIF(TRIM(tool_name), '') IS NOT NULL THEN 1 ELSE 0 END), 0) AS tool_hints
     FROM runtime_events
     WHERE ${eventWhere.join(' AND ')}
       AND (event_type = 'mcp' OR (event_type = 'hook' AND event_origin = 'hook_hint' AND hook_event = 'McpHint'))
     GROUP BY ${normalizedMcpServerName}
     ORDER BY (real_calls + server_hints + tool_hints) DESC, mcp_server_name ASC`,
    eventParams
  );
  const mcpByTool = await db.all<{
    mcp_server_name: string;
    tool_name: string;
    real_calls: number;
    tool_hints: number;
  }>(
    `SELECT
       ${normalizedMcpServerName} AS mcp_server_name,
       ${normalizedMcpToolName} AS tool_name,
       COALESCE(SUM(CASE WHEN event_type = 'mcp' THEN 1 ELSE 0 END), 0) AS real_calls,
       COALESCE(SUM(CASE WHEN event_type = 'hook' AND event_origin = 'hook_hint' AND hook_event = 'McpHint' AND NULLIF(TRIM(tool_name), '') IS NOT NULL THEN 1 ELSE 0 END), 0) AS tool_hints
     FROM runtime_events
     WHERE ${eventWhere.join(' AND ')}
       AND (event_type = 'mcp' OR (event_type = 'hook' AND event_origin = 'hook_hint' AND hook_event = 'McpHint' AND NULLIF(TRIM(tool_name), '') IS NOT NULL))
     GROUP BY ${normalizedMcpServerName}, ${normalizedMcpToolName}
     ORDER BY (real_calls + tool_hints) DESC, mcp_server_name ASC, tool_name ASC`,
    eventParams
  );
  const mcpBySourceServer = await db.all<{
    source: AnalyticsSource;
    mcp_server_name: string;
    real_calls: number;
    server_hints: number;
    tool_hints: number;
  }>(
    `SELECT
       source, ${normalizedMcpServerName} AS mcp_server_name,
       COALESCE(SUM(CASE WHEN event_type = 'mcp' THEN 1 ELSE 0 END), 0) AS real_calls,
       COALESCE(SUM(CASE WHEN event_type = 'hook' AND event_origin = 'hook_hint' AND hook_event = 'McpHint' AND NULLIF(TRIM(tool_name), '') IS NULL THEN 1 ELSE 0 END), 0) AS server_hints,
       COALESCE(SUM(CASE WHEN event_type = 'hook' AND event_origin = 'hook_hint' AND hook_event = 'McpHint' AND NULLIF(TRIM(tool_name), '') IS NOT NULL THEN 1 ELSE 0 END), 0) AS tool_hints
     FROM runtime_events
     WHERE ${eventWhere.join(' AND ')}
       AND (event_type = 'mcp' OR (event_type = 'hook' AND event_origin = 'hook_hint' AND hook_event = 'McpHint'))
     GROUP BY source, ${normalizedMcpServerName}
     ORDER BY (real_calls + server_hints + tool_hints) DESC, source ASC, mcp_server_name ASC`,
    eventParams
  );
  const mcpBySourceTool = await db.all<{
    source: AnalyticsSource;
    mcp_server_name: string;
    tool_name: string;
    real_calls: number;
    tool_hints: number;
  }>(
    `SELECT
       source, ${normalizedMcpServerName} AS mcp_server_name, ${normalizedMcpToolName} AS tool_name,
       COALESCE(SUM(CASE WHEN event_type = 'mcp' THEN 1 ELSE 0 END), 0) AS real_calls,
       COALESCE(SUM(CASE WHEN event_type = 'hook' AND event_origin = 'hook_hint' AND hook_event = 'McpHint' AND NULLIF(TRIM(tool_name), '') IS NOT NULL THEN 1 ELSE 0 END), 0) AS tool_hints
     FROM runtime_events
     WHERE ${eventWhere.join(' AND ')}
       AND (event_type = 'mcp' OR (event_type = 'hook' AND event_origin = 'hook_hint' AND hook_event = 'McpHint' AND NULLIF(TRIM(tool_name), '') IS NOT NULL))
     GROUP BY source, ${normalizedMcpServerName}, ${normalizedMcpToolName}
     ORDER BY (real_calls + tool_hints) DESC, source ASC, mcp_server_name ASC, tool_name ASC`,
    eventParams
  );

  // Per-source breakdown follows the selected source filter. hook_log has no rows in `sessions`
  // by design (it only produces runtime_events), so it correctly shows 0/0 for sessions/messages.
  const bySource: Array<{
    source: AnalyticsSource;
    sessions: number;
    messages: number;
    events: number;
    last_activity: string | null;
    token_metrics: { applicable: boolean; observed_tokens: number; status: "ok" | "not_applicable" };
  }> = [];
  for (const source of sources) {
    const sourceSessionWhere: string[] = ["source = ?", "COALESCE(session_kind, 'main') = 'main'", "user_message_count > 0"];
    const sourceSessionParams: unknown[] = [source];
    if (input.date_from) {
      sourceSessionWhere.push("updated_at >= ?");
      sourceSessionParams.push(input.date_from);
    }
    if (input.date_to) {
      sourceSessionWhere.push("updated_at <= ?");
      sourceSessionParams.push(input.date_to);
    }
    const sourceSessionTotals = await db.get<{ sessions: number; messages: number; last_activity: string | null }>(
      `SELECT COUNT(*) as sessions, COALESCE(SUM(user_message_count),0) as messages, MAX(updated_at) as last_activity
       FROM sessions WHERE ${sourceSessionWhere.join(" AND ")}`,
      sourceSessionParams
    );
    const tokenApplicable = (SESSION_SOURCES as readonly AnalyticsSource[]).includes(source);
    const sourceTokenTotals = tokenApplicable
      ? await db.get<{ observed_tokens: number }>(`SELECT COALESCE(SUM(COALESCE(mm.input_tokens,0) + COALESCE(mm.output_tokens,0)),0) observed_tokens FROM sessions s LEFT JOIN message_metrics mm ON mm.session_id=s.id WHERE ${sourceSessionWhere.join(" AND ")}`, sourceSessionParams)
      : null;

    const { where: sourceEventWhere, params: sourceEventParams } = buildRuntimeEventFilter(source);
    const sourceEventTotals = await db.get<{ events: number; last_activity: string | null }>(
      `SELECT COUNT(*) as events, MAX(occurred_at) as last_activity
       FROM runtime_events WHERE ${sourceEventWhere.join(" AND ")}`,
      sourceEventParams
    );

    const eventsLastActivity = sourceEventTotals?.last_activity ?? null;
    let lastActivity: string | null;
    if (input.event_type) {
      lastActivity = eventsLastActivity;
    } else {
      const sessionsLastActivity = sourceSessionTotals?.last_activity ?? null;
      if (sessionsLastActivity && eventsLastActivity) {
        lastActivity = sessionsLastActivity > eventsLastActivity ? sessionsLastActivity : eventsLastActivity;
      } else {
        lastActivity = sessionsLastActivity ?? eventsLastActivity ?? null;
      }
    }

    bySource.push({
      source,
      sessions: sourceSessionTotals?.sessions ?? 0,
      messages: sourceSessionTotals?.messages ?? 0,
      events: sourceEventTotals?.events ?? 0,
      last_activity: lastActivity,
      token_metrics: tokenApplicable
        ? { applicable: true, observed_tokens: sourceTokenTotals?.observed_tokens ?? 0, status: "ok" }
        : { applicable: false, observed_tokens: 0, status: "not_applicable" }
    });
  }

  const groupByRows: Array<Record<string, unknown>> = [];
  if (input.group_by && input.group_by.length > 0) {
    const allowedExpr: Record<string, string> = {
      source: "source",
      event_type: "event_type",
      event_name: "event_name",
      day: "substr(occurred_at,1,10)",
      project: "COALESCE((SELECT project_id FROM sessions s WHERE s.id = runtime_events.session_id),'')",
      model: "COALESCE((SELECT model_primary FROM sessions s WHERE s.id = runtime_events.session_id),'')"
    };
    const selects = input.group_by.map((g) => `${allowedExpr[g]} as ${g}`);
    const groupBySql = input.group_by.join(", ");
    const grouped = await db.all<Record<string, unknown>>(
      `SELECT ${selects.join(", ")}, COUNT(*) as count
       FROM runtime_events
       WHERE ${eventWhere.join(" AND ")}
       GROUP BY ${groupBySql}
       ORDER BY count DESC`,
      eventParams
    );
    groupByRows.push(...grouped);
  }

  return {
    ok: true,
    session_totals: { sessions: sessionTotals?.sessions ?? 0, messages: sessionTotals?.messages ?? 0 },
    token_totals: {
      input_tokens: tokenTotals?.input_tokens ?? 0, output_tokens: tokenTotals?.output_tokens ?? 0,
      observed_tokens: (tokenTotals?.input_tokens ?? 0) + (tokenTotals?.output_tokens ?? 0),
      reasoning_tokens: tokenTotals?.reasoning_tokens ?? 0, cache_read_tokens: tokenTotals?.cache_read_tokens ?? 0,
      cache_write_tokens: tokenTotals?.cache_write_tokens ?? 0,
      coverage: { available: tokenTotals?.available ?? 0, missing: tokenTotals?.missing ?? 0, partial: tokenTotals?.partial ?? 0 }
    },
    model_coverage: { known_observed_tokens: modelCoverage?.known ?? 0, unknown_observed_tokens: modelCoverage?.unknown ?? 0, total_observed_tokens: (tokenTotals?.input_tokens ?? 0) + (tokenTotals?.output_tokens ?? 0), known_percentage: ((tokenTotals?.input_tokens ?? 0) + (tokenTotals?.output_tokens ?? 0)) > 0 ? (modelCoverage?.known ?? 0) * 100 / ((tokenTotals?.input_tokens ?? 0) + (tokenTotals?.output_tokens ?? 0)) : 0 },
    preferred_model: preferred ? { model: preferred.model, observed_tokens: preferred.observed_tokens } : null,
    event_totals: {
      runtime_events: eventTotals?.runtime_events ?? 0,
      mcp_calls: eventTotals?.mcp_calls ?? 0,
      hook_events: eventTotals?.hook_events ?? 0,
      skill_hints: eventTotals?.skill_hints ?? 0,
      skill_declarations: eventTotals?.skill_declarations ?? 0,
      skill_signals: eventTotals?.skill_signals ?? 0,
      agent_tools: eventTotals?.agent_tools ?? 0,
      agent_lifecycle: eventTotals?.agent_lifecycle ?? 0
    },
    hook_metrics: {
      invocations: hookMetrics?.invocations ?? 0,
      outcome_evaluations: hookMetrics?.outcome_evaluations ?? 0,
      none: hookMetrics?.none ?? 0,
      mcp: hookMetrics?.mcp ?? 0,
      skill: hookMetrics?.skill ?? 0,
      mcp_and_skill: hookMetrics?.mcp_and_skill ?? 0,
      suggested_invocations: hookMetrics?.suggested_invocations ?? 0,
      suggestion_rate: (hookMetrics?.outcome_evaluations ?? 0) > 0
        ? ((hookMetrics?.suggested_invocations ?? 0) * 100) / (hookMetrics?.outcome_evaluations ?? 0)
        : 0,
      lifecycle: hookMetrics?.lifecycle ?? 0,
      prompt: hookMetrics?.prompt ?? 0,
      pretool: hookMetrics?.pretool ?? 0,
      mcp_suggestion_count: hookMetrics?.mcp_suggestion_count ?? 0,
      skill_suggestion_count: hookMetrics?.skill_suggestion_count ?? 0,
      by_hook: hooksByName,
      by_source_hook: hooksBySource
    },
    hotword_metrics: {
      prompts_total: promptMetrics?.prompts_total ?? 0,
      prompts_with_hotword: hotwordMetrics?.prompts_with_hotword ?? 0,
      quick: hotwordMetrics?.quick ?? 0,
      agents: hotwordMetrics?.agents ?? 0,
      quick_agents: hotwordMetrics?.quick_agents ?? 0,
      quick_total: hotwordMetrics?.quick_total ?? 0,
      agents_total: hotwordMetrics?.agents_total ?? 0,
      quick_light: hotwordMetrics?.quick_light ?? 0,
      quick_overridden: hotwordMetrics?.quick_overridden ?? 0,
      orchestration_required: hotwordMetrics?.orchestration_required ?? 0
    },
    skill_metrics: {
      hints: skillMetrics?.hints ?? 0,
      declarations: skillMetrics?.declarations ?? 0,
      signals: skillMetrics?.signals ?? 0,
      by_skill: skillsByName,
      by_source_skill: skillsBySource
    },
    mcp_metrics: {
      real_calls: mcpMetrics?.real_calls ?? 0,
      server_hints: mcpMetrics?.server_hints ?? 0,
      tool_hints: mcpMetrics?.tool_hints ?? 0,
      by_server: mcpByServer,
      by_tool: mcpByTool,
      by_source_server: mcpBySourceServer,
      by_source_tool: mcpBySourceTool
    },
    notes: [
      "hook_log does not contribute to session, token, or model metrics",
      "hook_log contributes to metrics derived from runtime events",
      "self events are excluded by default"
    ],
    by_source: bySource,
    ...(input.group_by ? { group_by: input.group_by, groups: groupByRows } : {})
  };
}