import { Injectable, ForbiddenException } from '@nestjs/common';
import { PrismaService } from '@core/database/prisma/prisma.service';
import { Prisma } from '@prisma/client';

interface QueryRange {
  from: Date;
  to: Date;
}

interface BaseScope {
  organizationId: string;
  userId?: string; // 限定 own
}

/**
 * Dashboard 聚合查询服务。
 * 关键：所有 groupBy / aggregate 必须显式注入 organizationId，
 * DataScope 拦截器对聚合查询不生效（见 docs/modules/it-operations/ai-usage/06-data-model.md）。
 */
@Injectable()
export class AiUsageDashboardService {
  constructor(private readonly prisma: PrismaService) {}

  resolveRange(period?: string, from?: string, to?: string): QueryRange {
    const now = new Date();
    if (from && to) return { from: new Date(from), to: new Date(to) };
    const start = new Date(now);
    switch (period) {
      case 'today':
        start.setUTCHours(0, 0, 0, 0);
        return { from: start, to: now };
      case 'week': {
        const day = start.getUTCDay() || 7;
        start.setUTCDate(start.getUTCDate() - day + 1);
        start.setUTCHours(0, 0, 0, 0);
        return { from: start, to: now };
      }
      case 'year':
        start.setUTCMonth(0, 1);
        start.setUTCHours(0, 0, 0, 0);
        return { from: start, to: now };
      case 'last7d':
        start.setUTCDate(start.getUTCDate() - 7);
        return { from: start, to: now };
      case 'last30d':
        start.setUTCDate(start.getUTCDate() - 30);
        return { from: start, to: now };
      case 'last60d':
        start.setUTCDate(start.getUTCDate() - 60);
        return { from: start, to: now };
      case 'last90d':
        start.setUTCDate(start.getUTCDate() - 90);
        return { from: start, to: now };
      case 'month':
      default:
        start.setUTCDate(1);
        start.setUTCHours(0, 0, 0, 0);
        return { from: start, to: now };
    }
  }

  private buildWhere(scope: BaseScope, range: QueryRange): Prisma.AiUsageEventWhereInput {
    // 同 orgFilterSql：admin 路径（无 userId）必须有 organizationId，否则拒绝
    if (!scope.organizationId && !scope.userId) {
      throw new ForbiddenException('AI_USAGE_ORG_CONTEXT_REQUIRED');
    }
    return {
      ...(scope.organizationId ? { organizationId: scope.organizationId } : {}),
      ...(scope.userId ? { userId: scope.userId } : {}),
      ts: { gte: range.from, lte: range.to },
    };
  }

  /**
   * 在 $queryRaw 模板里安全注入 organization_id 过滤片段。
   *
   * scope.organizationId 缺失策略：
   * - admin 路径（scope.userId 也缺）→ 抛 ForbiddenException 拒绝跨 org 聚合
   *   （防水平越权：view-all 权限 + currentOrganizationId 未设置 = 不放行）
   * - me 路径（scope.userId 存在）→ 允许 empty，靠 userId 限定到当事人自查
   */
  private orgFilterSql(scope: BaseScope, alias = ''): Prisma.Sql {
    if (!scope.organizationId) {
      if (!scope.userId) {
        throw new ForbiddenException('AI_USAGE_ORG_CONTEXT_REQUIRED');
      }
      return Prisma.empty;
    }
    const col = alias ? `${alias}.organization_id` : 'organization_id';
    return Prisma.sql`${Prisma.raw(col)} = ${scope.organizationId}::uuid AND`;
  }

  /** scope.userId 缺省时返回 empty；存在时返回 `AND user_id = '<uuid>'::uuid`（参数化，防注入） */
  private userFilterSql(scope: BaseScope): Prisma.Sql {
    return scope.userId ? Prisma.sql`AND user_id = ${scope.userId}::uuid` : Prisma.empty;
  }

  /**
   * 公共入口前置 scope 校验（buildWhere / orgFilterSql 同语义）：admin 路径（无 userId）
   * 必须有 organizationId；否则拒绝。直接调 Prisma findMany / findFirst 的方法走这条手工 guard。
   */
  private assertScopeBound(scope: BaseScope): void {
    if (!scope.organizationId && !scope.userId) {
      throw new ForbiddenException('AI_USAGE_ORG_CONTEXT_REQUIRED');
    }
  }

  async summary(scope: BaseScope, range: QueryRange) {
    const where = this.buildWhere(scope, range);
    const agg = await this.prisma.aiUsageEvent.aggregate({
      where,
      _sum: { totalTokens: true, estimatedCostUsd: true },
    });
    const [deviceCount, projectCount, userCount] = await Promise.all([
      this.prisma.aiUsageEvent.groupBy({ by: ['deviceId'], where, _count: true }).then((r) => r.length),
      this.prisma.aiUsageEvent.groupBy({ by: ['projectBasename'], where, _count: true }).then((r) => r.length),
      scope.userId
        ? Promise.resolve(1)
        : this.prisma.aiUsageEvent.groupBy({ by: ['userId'], where, _count: true }).then((r) => r.length),
    ]);
    return {
      totalTokens: agg._sum.totalTokens ?? 0,
      totalCostUsd: (agg._sum.estimatedCostUsd ?? new Prisma.Decimal(0)).toFixed(4),
      activeDevices: deviceCount,
      activeProjects: projectCount,
      activeUsers: userCount,
    };
  }

  async trend(
    scope: BaseScope,
    range: QueryRange,
    granularity: 'day' | 'week' | 'month' = 'day',
    groupBy: 'none' | 'tool' | 'model' | 'topUser' | 'user' | 'project' = 'none',
  ) {
    const bucketSql =
      granularity === 'month'
        ? `to_char(ts AT TIME ZONE 'UTC', 'YYYY-MM')`
        : granularity === 'week'
          ? `to_char(date_trunc('week', ts AT TIME ZONE 'UTC'), 'YYYY-MM-DD')`
          : `to_char(ts AT TIME ZONE 'UTC', 'YYYY-MM-DD')`;
    const userFilter = this.userFilterSql(scope);

    if (groupBy === 'none') {
      const rows: any[] = await this.prisma.$queryRaw`
        SELECT ${Prisma.raw(bucketSql)} AS bucket,
               SUM(total_tokens)::bigint AS tokens,
               SUM(estimated_cost_usd)::numeric AS cost
        FROM platform_ai_usage.ai_usage_events
        WHERE ${this.orgFilterSql(scope)}
          ts BETWEEN ${range.from} AND ${range.to}
          ${userFilter}
        GROUP BY bucket
        ORDER BY bucket ASC
      `;
      return {
        series: [
          {
            key: 'total',
            points: rows.map((r) => ({ bucket: r.bucket, tokens: Number(r.tokens), costUsd: r.cost?.toString() ?? '0' })),
          },
        ],
      };
    }

    const TREND_DIM_COL: Record<string, string> = {
      tool: 'tool',
      model: 'model',
      project: 'project_basename',
      user: 'user_id',
      topUser: 'user_id',
    };
    const dimCol = TREND_DIM_COL[groupBy] ?? 'user_id';
    const rows: any[] = await this.prisma.$queryRaw`
      SELECT ${Prisma.raw(bucketSql)} AS bucket,
             ${Prisma.raw(dimCol)} AS dim,
             SUM(total_tokens)::bigint AS tokens,
             SUM(estimated_cost_usd)::numeric AS cost
      FROM platform_ai_usage.ai_usage_events
      WHERE ${this.orgFilterSql(scope)}
        ts BETWEEN ${range.from} AND ${range.to}
        ${userFilter}
      GROUP BY bucket, dim
      ORDER BY bucket ASC, tokens DESC
    `;
    const grouped: Record<string, { bucket: string; tokens: number; costUsd: string }[]> = {};
    for (const r of rows) {
      const k = r.dim?.toString() ?? 'unknown';
      (grouped[k] ??= []).push({ bucket: r.bucket, tokens: Number(r.tokens), costUsd: r.cost?.toString() ?? '0' });
    }

    // topUser / user / project：只保留 series 总 tokens 排序的 top 10，避免几十条用户挤爆图
    const TOP_N = 10;
    const needsTopN = groupBy === 'topUser' || groupBy === 'user' || groupBy === 'project';
    const entries = Object.entries(grouped).map(([key, points]) => ({
      key,
      points,
      total: points.reduce((s, p) => s + p.tokens, 0),
    }));
    const sliced = needsTopN
      ? entries.sort((a, b) => b.total - a.total).slice(0, TOP_N)
      : entries;

    // 对 user 维度，把 user_id 解析成 displayName（不 join 避免大表 LEFT JOIN，事后一次 IN 查询）
    if (groupBy === 'user' || groupBy === 'topUser') {
      const ids = sliced.map((e) => e.key).filter((k) => /^[0-9a-f-]{36}$/i.test(k));
      if (ids.length > 0) {
        const users = await this.prisma.user.findMany({
          where: { id: { in: ids } },
          select: { id: true, displayName: true, username: true },
        });
        const map = new Map(users.map((u) => [u.id, u.displayName || u.username || u.id.slice(0, 8)]));
        return {
          series: sliced.map((e) => ({
            key: e.key,
            label: map.get(e.key) ?? e.key.slice(0, 8),
            points: e.points,
          })),
        };
      }
    }

    return { series: sliced.map((e) => ({ key: e.key, points: e.points })) };
  }

  async breakdown(
    scope: BaseScope,
    range: QueryRange,
    by: 'user' | 'project' | 'tool' | 'model' | 'device',
    page = 1,
    pageSize = 20,
  ) {
    const where = this.buildWhere(scope, range);
    const BREAKDOWN_COL: Record<string, string> = {
      user: 'userId',
      project: 'projectBasename',
      tool: 'tool',
      model: 'model',
      device: 'deviceId',
    };
    const groupCol = BREAKDOWN_COL[by] ?? 'userId';

    const totalAgg = await this.prisma.aiUsageEvent.aggregate({
      where,
      _sum: { totalTokens: true, estimatedCostUsd: true },
    });
    const grandTokens = totalAgg._sum.totalTokens ?? 0;

    const rawRows: any[] = await (this.prisma.aiUsageEvent.groupBy as any)({
      by: [groupCol],
      where,
      _sum: { totalTokens: true, estimatedCostUsd: true },
      orderBy: { _sum: { estimatedCostUsd: 'desc' } },
      skip: (page - 1) * pageSize,
      take: pageSize,
    });
    const totalRows: any[] = await (this.prisma.aiUsageEvent.groupBy as any)({
      by: [groupCol],
      where,
      _count: true,
    });
    const total = totalRows.length;

    const items = rawRows.map((r) => ({
      key: r[groupCol]?.toString() ?? 'unknown',
      tokens: Number(r._sum.totalTokens ?? 0),
      costUsd: (r._sum.estimatedCostUsd ?? new Prisma.Decimal(0)).toString(),
      share: grandTokens > 0 ? Number(r._sum.totalTokens ?? 0) / Number(grandTokens) : 0,
    }));

    return {
      items,
      pagination: { page, pageSize, total, totalPages: Math.ceil(total / pageSize) },
    };
  }

  /**
   * 5h 滚动窗口 block 视图（参考 ccusage + Claude Pro/Max 配额单位）。
   * Block 切割规则：按 ts 升序遍历，event ts ≥ 当前 block.endsAt 即开新 block。
   * 返回近 N 个 block（最新在前），含 burnRate（cost/小时）便于预警当前消耗速度。
   */
  async listBlocks(scope: BaseScope, hoursPerBlock = 5, limitBlocks = 6) {
    this.assertScopeBound(scope);
    const events = await this.prisma.aiUsageEvent.findMany({
      where: {
        ...(scope.organizationId ? { organizationId: scope.organizationId } : {}),
        ...(scope.userId ? { userId: scope.userId } : {}),
        ts: { gte: new Date(Date.now() - 30 * 24 * 60 * 60 * 1000) },
      },
      orderBy: { ts: 'asc' },
      select: { ts: true, totalTokens: true, estimatedCostUsd: true, model: true },
    });
    if (events.length === 0) return { blocks: [] };

    const windowMs = hoursPerBlock * 60 * 60 * 1000;
    type Block = {
      startedAt: Date;
      endsAt: Date;
      events: number;
      tokens: number;
      costUsd: string;
      models: string[];
    };
    const blocks: Block[] = [];
    let cur: Block | null = null;

    for (const e of events) {
      const ts = e.ts.getTime();
      if (!cur || ts >= cur.endsAt.getTime()) {
        cur = {
          startedAt: new Date(ts),
          endsAt: new Date(ts + windowMs),
          events: 0,
          tokens: 0,
          costUsd: '0',
          models: [],
        };
        blocks.push(cur);
      }
      cur.events += 1;
      cur.tokens += Number(e.totalTokens);
      cur.costUsd = (parseFloat(cur.costUsd) + Number(e.estimatedCostUsd)).toFixed(6);
      if (!cur.models.includes(e.model)) cur.models.push(e.model);
    }

    const now = Date.now();
    return {
      blocks: blocks
        .slice()
        .reverse()
        .slice(0, limitBlocks)
        .map((b) => {
          const isActive = b.endsAt.getTime() > now;
          const elapsedH = (Math.min(now, b.endsAt.getTime()) - b.startedAt.getTime()) / 3600_000;
          return {
            ...b,
            isActive,
            elapsedHours: Number(elapsedH.toFixed(2)),
            remainingHours: isActive
              ? Number(((b.endsAt.getTime() - now) / 3600_000).toFixed(2))
              : 0,
            burnRateUsdPerHour: elapsedH > 0 ? (parseFloat(b.costUsd) / elapsedH).toFixed(4) : '0',
          };
        }),
    };
  }

  async listMyDevices(userId: string) {
    return this.prisma.aiUsageDevice.findMany({
      where: { userId },
      orderBy: { lastSeenAt: 'desc' },
      select: {
        id: true,
        hostname: true,
        osPlatform: true,
        osUser: true,
        agentVersion: true,
        firstSeenAt: true,
        lastSeenAt: true,
        blockedAt: true,
        blockedReason: true,
      },
    });
  }

  /**
   * 工具使用频次热力图
   * 展开 tool_names JSONB 数组，按 tool 名聚合次数 + 出现的 event 数。
   * 用 jsonb_array_elements_text 展开，零 unique-name 假设。
   */
  async toolFrequency(scope: BaseScope, range: QueryRange) {
    const userFilter = this.userFilterSql(scope);
    const rows: any[] = await this.prisma.$queryRaw`
      SELECT tn.name AS tool_name,
             COUNT(*)::bigint AS event_count,
             SUM(COALESCE(tool_use_count, 1))::bigint AS use_count
      FROM platform_ai_usage.ai_usage_events e,
           LATERAL jsonb_array_elements_text(COALESCE(e.tool_names, '[]'::jsonb)) AS tn(name)
      WHERE ${this.orgFilterSql(scope, 'e')}
        e.ts BETWEEN ${range.from} AND ${range.to}
        ${userFilter}
      GROUP BY tn.name
      ORDER BY use_count DESC
      LIMIT 50
    `;
    const grandUse = rows.reduce((s, r) => s + Number(r.use_count), 0);
    return {
      items: rows.map((r) => ({
        name: r.tool_name,
        eventCount: Number(r.event_count),
        useCount: Number(r.use_count),
        share: grandUse > 0 ? Number(r.use_count) / grandUse : 0,
      })),
    };
  }

  /**
   * Session 维度统计：
   *  - 每个 session 的起止时间、turn 数、模型、tokens、cost
   *  - 持续时长分布桶（< 10 min / 10-60 min / 1-4 h / > 4 h）
   *  - turn 数分布桶（1-10 / 11-50 / 51-200 / > 200）
   *
   * filter: 可按 userId / projectBasename 收缩；recentSessions 分页（page/pageSize）
   * 上限：聚合阶段最多扫描 5000 个 session（防止超大时间窗炸）
   */
  async sessionStats(
    scope: BaseScope,
    range: QueryRange,
    filter: { userId?: string; projectBasename?: string } = {},
    page = 1,
    pageSize = 50,
  ) {
    const userId = filter.userId ?? scope.userId;
    const userFilter = userId ? Prisma.sql`AND user_id = ${userId}::uuid` : Prisma.empty;
    const projectFilter = filter.projectBasename
      ? Prisma.sql`AND project_basename = ${filter.projectBasename}`
      : Prisma.empty;
    const rows: any[] = await this.prisma.$queryRaw`
      SELECT session_id,
             MIN(user_id::text)            AS user_id,
             MIN(project_basename)         AS project_basename,
             MIN(ts)                       AS started_at,
             MAX(ts)                       AS ended_at,
             COUNT(*)::int                 AS turn_count,
             SUM(total_tokens)::bigint     AS tokens,
             SUM(estimated_cost_usd)::numeric AS cost,
             EXTRACT(EPOCH FROM (MAX(ts) - MIN(ts)))::int AS duration_sec,
             MIN(model)                    AS model
      FROM platform_ai_usage.ai_usage_events
      WHERE ${this.orgFilterSql(scope)}
        ts BETWEEN ${range.from} AND ${range.to}
        ${userFilter}
        ${projectFilter}
      GROUP BY session_id
      ORDER BY started_at DESC
      LIMIT 5000
    `;
    const durationBuckets = { '<10min': 0, '10-60min': 0, '1-4h': 0, '>4h': 0 } as Record<string, number>;
    const turnBuckets = { '1-10': 0, '11-50': 0, '51-200': 0, '>200': 0 } as Record<string, number>;
    let totalDuration = 0;
    let totalTurns = 0;
    for (const r of rows) {
      const d = Number(r.duration_sec);
      if (d < 600) durationBuckets['<10min']++;
      else if (d < 3600) durationBuckets['10-60min']++;
      else if (d < 14400) durationBuckets['1-4h']++;
      else durationBuckets['>4h']++;
      const t = Number(r.turn_count);
      if (t <= 10) turnBuckets['1-10']++;
      else if (t <= 50) turnBuckets['11-50']++;
      else if (t <= 200) turnBuckets['51-200']++;
      else turnBuckets['>200']++;
      totalDuration += d;
      totalTurns += t;
    }
    const n = rows.length;
    const totalPages = Math.max(1, Math.ceil(n / pageSize));
    const safePage = Math.max(1, Math.min(page, totalPages));
    const paged = rows.slice((safePage - 1) * pageSize, safePage * pageSize);

    // 反查 user displayName（最多一次 IN 查询，paged 长度 ≤ pageSize）
    const userIds = Array.from(new Set(paged.map((r) => r.user_id).filter(Boolean)));
    let userMap = new Map<string, string>();
    if (userIds.length > 0) {
      const users = await this.prisma.user.findMany({
        where: { id: { in: userIds } },
        select: { id: true, displayName: true, username: true },
      });
      userMap = new Map(users.map((u) => [u.id, u.displayName || u.username || u.id.slice(0, 8)]));
    }

    return {
      sessionCount: n,
      avgDurationSec: n > 0 ? Math.round(totalDuration / n) : 0,
      avgTurns: n > 0 ? Math.round((totalTurns / n) * 10) / 10 : 0,
      durationBuckets,
      turnBuckets,
      recentSessions: paged.map((r) => ({
        sessionId: r.session_id,
        userId: r.user_id,
        userLabel: userMap.get(r.user_id) ?? r.user_id?.slice(0, 8) ?? '',
        projectBasename: r.project_basename,
        model: r.model,
        startedAt: r.started_at,
        endedAt: r.ended_at,
        durationSec: Number(r.duration_sec),
        turnCount: Number(r.turn_count),
        tokens: Number(r.tokens),
        costUsd: r.cost?.toString() ?? '0',
      })),
      pagination: { page: safePage, pageSize, total: n, totalPages },
    };
  }

  /**
   * 单 session 的 turn timeline：按 ts 升序拉所有 event。
   * 不直接 JSON.stringify 内容 — DB 里本就没有 content，仅含 metadata（v1.1 字段）。
   * scope.userId 存在时强制 cross-check（防 admin 误访其他 user session，view-own 路径自动收缩）。
   */
  async sessionTurns(scope: BaseScope, sessionId: string) {
    this.assertScopeBound(scope);
    const events = await this.prisma.aiUsageEvent.findMany({
      where: {
        ...(scope.organizationId ? { organizationId: scope.organizationId } : {}),
        sessionId,
        ...(scope.userId ? { userId: scope.userId } : {}),
      },
      orderBy: { ts: 'asc' },
      select: {
        id: true,
        ts: true,
        model: true,
        inputTokens: true,
        outputTokens: true,
        cacheCreationTokens: true,
        cacheReadTokens: true,
        totalTokens: true,
        estimatedCostUsd: true,
        turnIndex: true,
        toolUseCount: true,
        toolNames: true,
        stopReason: true,
        serviceTier: true,
        gitBranch: true,
        agentVersionEvent: true,
        worktreeLabel: true,
        cwdBasename: true,
        userId: true,
        projectBasename: true,
        projectPath: true,
        tool: true,
      },
    });
    if (events.length === 0) {
      return { sessionId, turns: [], summary: null };
    }
    const head = events[0];
    const tail = events[events.length - 1];
    // user displayName
    const user = await this.prisma.user.findUnique({
      where: { id: head.userId },
      select: { id: true, displayName: true, username: true },
    });
    // 工具用次数聚合
    const toolAgg = new Map<string, number>();
    for (const e of events) {
      const tn = Array.isArray(e.toolNames) ? (e.toolNames as unknown as string[]) : [];
      for (const n of tn) toolAgg.set(n, (toolAgg.get(n) ?? 0) + 1);
    }

    return {
      sessionId,
      summary: {
        userId: head.userId,
        userLabel: user?.displayName || user?.username || head.userId.slice(0, 8),
        projectBasename: head.projectBasename,
        projectPath: head.projectPath,
        tool: head.tool,
        startedAt: head.ts,
        endedAt: tail.ts,
        durationSec: Math.max(0, Math.round((tail.ts.getTime() - head.ts.getTime()) / 1000)),
        turnCount: events.length,
        totalTokens: events.reduce((s, e) => s + Number(e.totalTokens), 0),
        totalCostUsd: events.reduce((s, e) => s + Number(e.estimatedCostUsd), 0).toFixed(6),
        toolUseAgg: Array.from(toolAgg.entries())
          .map(([name, count]) => ({ name, count }))
          .sort((a, b) => b.count - a.count),
      },
      turns: events.map((e) => ({
        id: e.id,
        ts: e.ts,
        turnIndex: e.turnIndex,
        model: e.model,
        inputTokens: e.inputTokens,
        outputTokens: e.outputTokens,
        cacheCreationTokens: e.cacheCreationTokens,
        cacheReadTokens: e.cacheReadTokens,
        totalTokens: e.totalTokens,
        costUsd: e.estimatedCostUsd.toString(),
        toolUseCount: e.toolUseCount,
        toolNames: e.toolNames,
        stopReason: e.stopReason,
        serviceTier: e.serviceTier,
        gitBranch: e.gitBranch,
        agentVersionEvent: e.agentVersionEvent,
        worktreeLabel: e.worktreeLabel,
        cwdBasename: e.cwdBasename,
      })),
    };
  }

  /**
   * 按日 × user 矩阵热力图（admin only），用于 calendar 视图
   * 返回每 user 每天的 tokens 与 cost，前端转矩阵。
   */
  async dailyUserMatrix(scope: BaseScope, range: QueryRange, topUsers = 10) {
    // 先取 top N user
    const topRows: any[] = await this.prisma.$queryRaw`
      SELECT user_id::text AS user_id, SUM(total_tokens)::bigint AS tokens
      FROM platform_ai_usage.ai_usage_events
      WHERE ${this.orgFilterSql(scope)}
        ts BETWEEN ${range.from} AND ${range.to}
      GROUP BY user_id
      ORDER BY tokens DESC
      LIMIT ${Prisma.raw(String(Math.max(1, Math.min(50, topUsers))))}
    `;
    if (topRows.length === 0) return { users: [], dates: [], cells: [] };
    const userIds = topRows.map((r) => r.user_id);
    const users = await this.prisma.user.findMany({
      where: { id: { in: userIds } },
      select: { id: true, displayName: true, username: true },
    });
    const labelMap = new Map(users.map((u) => [u.id, u.displayName || u.username || u.id.slice(0, 8)]));

    const cells: any[] = await this.prisma.$queryRaw`
      SELECT to_char(ts AT TIME ZONE 'UTC', 'YYYY-MM-DD') AS bucket,
             user_id::text AS user_id,
             SUM(total_tokens)::bigint AS tokens,
             SUM(estimated_cost_usd)::numeric AS cost
      FROM platform_ai_usage.ai_usage_events
      WHERE ${this.orgFilterSql(scope)}
        ts BETWEEN ${range.from} AND ${range.to}
        AND user_id::text = ANY(${userIds})
      GROUP BY bucket, user_id
    `;
    // dates: 排序去重
    const dateSet = new Set<string>(cells.map((c) => c.bucket));
    const dates = Array.from(dateSet).sort();
    return {
      users: userIds.map((id) => ({ id, label: labelMap.get(id) ?? id.slice(0, 8) })),
      dates,
      cells: cells.map((c) => ({
        date: c.bucket,
        userId: c.user_id,
        tokens: Number(c.tokens),
        costUsd: c.cost?.toString() ?? '0',
      })),
    };
  }

  /**
   * Turn 间隔分布（衡量思考密度）：
   * 用 LAG 取同 session 内前一 event 的 ts，diff 得到秒级间隔。
   * 桶：< 5s / 5-30s / 30s-2min / > 2min
   */
  async turnGapDistribution(scope: BaseScope, range: QueryRange) {
    const userFilter = this.userFilterSql(scope);
    const rows: any[] = await this.prisma.$queryRaw`
      WITH gaps AS (
        SELECT EXTRACT(EPOCH FROM (ts - LAG(ts) OVER (PARTITION BY session_id ORDER BY ts)))::numeric AS gap_sec
        FROM platform_ai_usage.ai_usage_events
        WHERE ${this.orgFilterSql(scope)}
          ts BETWEEN ${range.from} AND ${range.to}
          ${userFilter}
      )
      SELECT
        SUM(CASE WHEN gap_sec < 5 THEN 1 ELSE 0 END)::bigint                    AS lt5s,
        SUM(CASE WHEN gap_sec >= 5 AND gap_sec < 30 THEN 1 ELSE 0 END)::bigint  AS s5to30,
        SUM(CASE WHEN gap_sec >= 30 AND gap_sec < 120 THEN 1 ELSE 0 END)::bigint AS s30to120,
        SUM(CASE WHEN gap_sec >= 120 THEN 1 ELSE 0 END)::bigint                 AS gt2min,
        AVG(gap_sec)::numeric                                                    AS avg_gap_sec,
        PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY gap_sec)::numeric            AS median_gap_sec
      FROM gaps WHERE gap_sec IS NOT NULL
    `;
    const r = rows[0] ?? {};
    return {
      buckets: {
        '<5s': Number(r.lt5s ?? 0),
        '5-30s': Number(r.s5to30 ?? 0),
        '30s-2min': Number(r.s30to120 ?? 0),
        '>2min': Number(r.gt2min ?? 0),
      },
      avgGapSec: Number(r.avg_gap_sec ?? 0),
      medianGapSec: Number(r.median_gap_sec ?? 0),
    };
  }

  /**
   * 服务等级配比（standard / priority / batch）— 关心成本占比。
   */
  async serviceTierMix(scope: BaseScope, range: QueryRange) {
    const userFilter = this.userFilterSql(scope);
    const rows: any[] = await this.prisma.$queryRaw`
      SELECT COALESCE(service_tier, 'unknown') AS tier,
             COUNT(*)::bigint                 AS event_count,
             SUM(total_tokens)::bigint        AS tokens,
             SUM(estimated_cost_usd)::numeric AS cost
      FROM platform_ai_usage.ai_usage_events
      WHERE ${this.orgFilterSql(scope)}
        ts BETWEEN ${range.from} AND ${range.to}
        ${userFilter}
      GROUP BY tier
      ORDER BY cost DESC
    `;
    const grand = rows.reduce((s, r) => s + Number(r.cost ?? 0), 0);
    return {
      items: rows.map((r) => ({
        tier: r.tier,
        eventCount: Number(r.event_count),
        tokens: Number(r.tokens),
        costUsd: r.cost?.toString() ?? '0',
        costShare: grand > 0 ? Number(r.cost) / grand : 0,
      })),
    };
  }

  /**
   * Stop reason 分布（end_turn / tool_use / max_tokens / stop_sequence）
   * max_tokens 高占比 = 工程上需要拆 turn / 扩 max_tokens 的信号
   */
  async stopReasonMix(scope: BaseScope, range: QueryRange) {
    const userFilter = this.userFilterSql(scope);
    const rows: any[] = await this.prisma.$queryRaw`
      SELECT COALESCE(stop_reason, 'unknown') AS reason,
             COUNT(*)::bigint AS event_count,
             SUM(total_tokens)::bigint AS tokens
      FROM platform_ai_usage.ai_usage_events
      WHERE ${this.orgFilterSql(scope)}
        ts BETWEEN ${range.from} AND ${range.to}
        ${userFilter}
      GROUP BY reason
      ORDER BY event_count DESC
    `;
    const grandEvents = rows.reduce((s, r) => s + Number(r.event_count), 0);
    return {
      items: rows.map((r) => ({
        reason: r.reason,
        eventCount: Number(r.event_count),
        tokens: Number(r.tokens),
        share: grandEvents > 0 ? Number(r.event_count) / grandEvents : 0,
      })),
    };
  }

  /**
   * 导出富 metadata 详细 event（CSV 用），admin only。
   * 上限 10000 行（admin 一次需求场景：30 天小团队全量）。
   * 隐私：不输出 raw_message_id / project_path（含 PII），只给 sessionId / projectBasename。
   */
  async exportRichEvents(scope: BaseScope, range: QueryRange, limit = 10000) {
    this.assertScopeBound(scope);
    const events = await this.prisma.aiUsageEvent.findMany({
      where: {
        ...(scope.organizationId ? { organizationId: scope.organizationId } : {}),
        ts: { gte: range.from, lte: range.to },
        ...(scope.userId ? { userId: scope.userId } : {}),
      },
      orderBy: { ts: 'desc' },
      take: Math.max(1, Math.min(50000, limit)),
      select: {
        ts: true,
        userId: true,
        projectBasename: true,
        sessionId: true,
        turnIndex: true,
        tool: true,
        model: true,
        inputTokens: true,
        outputTokens: true,
        cacheCreationTokens: true,
        cacheReadTokens: true,
        totalTokens: true,
        estimatedCostUsd: true,
        toolUseCount: true,
        toolNames: true,
        stopReason: true,
        serviceTier: true,
        gitBranch: true,
        worktreeLabel: true,
        agentVersionEvent: true,
      },
    });
    if (events.length === 0) return [];
    const userIds = Array.from(new Set(events.map((e) => e.userId)));
    const users = await this.prisma.user.findMany({
      where: { id: { in: userIds } },
      select: { id: true, displayName: true, username: true },
    });
    const map = new Map(users.map((u) => [u.id, u.displayName || u.username || u.id.slice(0, 8)]));
    return events.map((e) => ({
      ts: e.ts.toISOString(),
      userLabel: map.get(e.userId) ?? e.userId.slice(0, 8),
      projectBasename: e.projectBasename,
      sessionId: e.sessionId,
      turnIndex: e.turnIndex,
      tool: e.tool,
      model: e.model,
      inputTokens: e.inputTokens,
      outputTokens: e.outputTokens,
      cacheCreationTokens: e.cacheCreationTokens,
      cacheReadTokens: e.cacheReadTokens,
      totalTokens: e.totalTokens,
      costUsd: e.estimatedCostUsd.toString(),
      toolUseCount: e.toolUseCount,
      toolNames: e.toolNames,
      stopReason: e.stopReason,
      serviceTier: e.serviceTier,
      gitBranch: e.gitBranch,
      worktreeLabel: e.worktreeLabel,
      agentVersionEvent: e.agentVersionEvent,
    }));
  }

  /**
   * 按 git_branch 聚合 token 与 cost（top N 分支热力图）
   */
  async gitBranchHeatmap(scope: BaseScope, range: QueryRange, limit = 20) {
    const userFilter = this.userFilterSql(scope);
    const rows: any[] = await this.prisma.$queryRaw`
      SELECT COALESCE(git_branch, '(unknown)') AS branch,
             COUNT(*)::bigint AS event_count,
             SUM(total_tokens)::bigint AS tokens,
             SUM(estimated_cost_usd)::numeric AS cost
      FROM platform_ai_usage.ai_usage_events
      WHERE ${this.orgFilterSql(scope)}
        ts BETWEEN ${range.from} AND ${range.to}
        ${userFilter}
      GROUP BY branch
      ORDER BY cost DESC NULLS LAST
      LIMIT ${Prisma.raw(String(Math.max(1, Math.min(100, limit))))}
    `;
    return {
      items: rows.map((r) => ({
        branch: r.branch,
        eventCount: Number(r.event_count),
        tokens: Number(r.tokens),
        costUsd: r.cost?.toString() ?? '0',
      })),
    };
  }
}