import { MessageRecord, IMessageRecord } from "../models/messageRecordModel";
import { MessageStatsDailyUid } from "../models/messageStatsDailyUidModel";
import { redisClient } from "./clients";

export class MessageRecordService {
  // 提取常量便于维护
  private static readonly TIMEZONE = "America/Los_Angeles"; // UTC+8时区
  // private static readonly TIMEZONE = "Asia/Shanghai"; // UTC+8时区
  private static readonly DEFAULT_PAGE = 1;
  private static readonly DEFAULT_LIMIT = 10;
  private static readonly MAX_LIMIT = 100;
  private static readonly DEFAULT_SORT_FIELD = "createdAt";
  private static readonly DEFAULT_SORT_ORDER: "asc" | "desc" = "desc";
  private static readonly ALLOWED_SORT_FIELDS = new Set(["createdAt", "updatedAt", "status", "uid", "strategyName", "templateName", "activityName"]);

  public static readonly DEFAULT_STATS_PAGE = 1;
  public static readonly DEFAULT_STATS_LIMIT = 50;
  public static readonly MAX_STATS_LIMIT = 200;
  private static readonly STATS_CACHE_TTL_SECONDS = 300;
  private static readonly PREAGG_ENABLED = process.env.MESSAGE_STATS_PREAGG_ENABLED === "1";

  private logPerf(endpoint: string, stage: string, durationMs: number, extra: Record<string, unknown> = {}) {
    console.log(
      `[MessageStatsPerf] ${endpoint}:${stage}`,
      JSON.stringify({
        endpoint,
        stage,
        durationMs,
        ...extra,
      }),
    );
  }

  private resolveResultRows(result: unknown): number {
    if (Array.isArray(result)) {
      return result.length;
    }
    if (result === null || result === undefined) {
      return 0;
    }
    return 1;
  }

  private formatDateKeyInTimezone(date: Date): string {
    return new Intl.DateTimeFormat("en-CA", {
      timeZone: MessageRecordService.TIMEZONE,
      year: "numeric",
      month: "2-digit",
      day: "2-digit",
    }).format(date);
  }

  /**
   * 创建一条新的消息推送记录
   * @param recordData 消息记录数据
   * @returns 新创建的消息记录对象
   */
  public async createMessageRecord(recordData: IMessageRecord): Promise<IMessageRecord> {
    const newRecord = new MessageRecord(recordData);
    return await newRecord.save();
  }

  /**
   * 分页获取消息推送记录，支持筛选和排序
   */
  public async getPaginatedRecords(
    page: number = MessageRecordService.DEFAULT_PAGE,
    limit: number = MessageRecordService.DEFAULT_LIMIT,
    filters: { [key: string]: any } = {},
    sortField: string = MessageRecordService.DEFAULT_SORT_FIELD,
    sortOrder: "asc" | "desc" = MessageRecordService.DEFAULT_SORT_ORDER,
  ): Promise<{ records: any[]; total: number; page: number; limit: number; totalPages: number; isTotalEstimated: boolean }> {
    const safePage = Number.isNaN(page) ? MessageRecordService.DEFAULT_PAGE : Math.max(1, page);
    const safeLimit = Number.isNaN(limit) ? MessageRecordService.DEFAULT_LIMIT : Math.min(MessageRecordService.MAX_LIMIT, Math.max(1, limit));

    // 构建查询条件
    const query = this.buildDateFilterQuery(filters);

    // 添加非日期筛选条件
    if (filters.uid) query.uid = filters.uid;
    if (filters.activityName) query.activityName = filters.activityName;
    if (filters.templateName) query.templateName = filters.templateName;
    if (filters.status !== undefined) query.status = filters.status;

    const safeSortField = MessageRecordService.ALLOWED_SORT_FIELDS.has(sortField) ? sortField : MessageRecordService.DEFAULT_SORT_FIELD;

    const sortOption: any = {};
    sortOption[safeSortField] = sortOrder === "asc" ? 1 : -1;

    const isUnfilteredQuery = Object.keys(query).length === 0;
    const total = isUnfilteredQuery ? await MessageRecord.estimatedDocumentCount() : await MessageRecord.countDocuments(query);
    const isTotalEstimated = isUnfilteredQuery;

    const records = await MessageRecord.find(query)
      .sort(sortOption)
      .skip((safePage - 1) * safeLimit)
      .limit(safeLimit)
      .lean();

    const totalPages = Math.ceil(total / safeLimit);

    return { records, total, page: safePage, limit: safeLimit, totalPages, isTotalEstimated };
  }

  /**
   * 根据用户UID获取其所有消息推送记录
   */
  public async getRecordsByUid(uid: string): Promise<IMessageRecord[]> {
    return await MessageRecord.find({ uid }).sort({ createdAt: -1 });
  }

  /**
   * 根据消息活动ID获取所有相关的推送记录
   */
  public async getRecordsByActivityId(activityId: string): Promise<IMessageRecord[]> {
    return await MessageRecord.find({ activityId }).sort({ createdAt: -1 });
  }

  /**
   * 根据ID获取单个消息推送记录
   */
  public async getSingleRecord(recordId: string): Promise<IMessageRecord | null> {
    return await MessageRecord.findById(recordId);
  }

  /**
   * 更新消息推送记录的状态
   */
  public async updateMessageRecord(recordId: string, updateData: Partial<IMessageRecord>): Promise<IMessageRecord | null> {
    return await MessageRecord.findByIdAndUpdate(recordId, updateData, { new: true });
  }

  /**
   * 获取整体消息推送统计数据（含用户点击率）
   */
  public async getOverallStatistics(startDate?: Date, endDate?: Date, strategyName?: string) {
    const cacheKey = this.buildStatsCacheKey("overall", {
      startDate: startDate?.toISOString(),
      endDate: endDate?.toISOString(),
      strategyName: strategyName || null,
    });

    const cached = await this.getCache(cacheKey);
    if (cached) {
      console.log(`[MessageStatsCache] hit key=${cacheKey}`);
      return cached;
    }

    let result: any = null;
    if (MessageRecordService.PREAGG_ENABLED && startDate && endDate) {
      const preAggStartedAt = Date.now();
      result = await this.getOverallFromPreAgg(startDate, endDate, strategyName);
      if (result) {
        this.logPerf("overall", "preagg", Date.now() - preAggStartedAt, {
          startDate: startDate.toISOString(),
          endDate: endDate.toISOString(),
          hasStrategy: Boolean(strategyName),
        });
      }
    }

    if (!result) {
      const matchConditions = this.buildMatchConditions(startDate, endDate, strategyName);
      result = await this.getStatisticsByGroup(matchConditions, []);
    }

    await this.setCache(cacheKey, result);
    console.log(`[MessageStatsCache] miss key=${cacheKey}`);
    return result;
  }

  /**
   * 按活动获取消息统计数据（含用户点击率）
   */
  public async getStatisticsByActivity(startDate?: Date, endDate?: Date, page: number = MessageRecordService.DEFAULT_STATS_PAGE, limit: number = MessageRecordService.DEFAULT_STATS_LIMIT) {
    const matchConditions = this.buildMatchConditions(startDate, endDate);
    const groupFields = ["activityId", "activityName"];
    return this.getStatisticsByGroup(matchConditions, groupFields, { deliveredRate: -1 }, page, limit);
  }

  /**
   * 按策略获取消息统计数据（含用户点击率）
   */
  public async getStatisticsByStrategy(
    startDate?: Date,
    endDate?: Date,
    strategyName?: string,
    page: number = MessageRecordService.DEFAULT_STATS_PAGE,
    limit: number = MessageRecordService.DEFAULT_STATS_LIMIT,
  ) {
    const cacheKey = this.buildStatsCacheKey("by-strategy", {
      startDate: startDate?.toISOString(),
      endDate: endDate?.toISOString(),
      strategyName: strategyName || null,
      page,
      limit,
    });

    const cached = await this.getCache(cacheKey);
    if (cached) {
      console.log(`[MessageStatsCache] hit key=${cacheKey}`);
      return cached;
    }

    let result: any[] = [];
    if (MessageRecordService.PREAGG_ENABLED && startDate && endDate) {
      const preAggStartedAt = Date.now();
      result = await this.getByStrategyFromPreAgg(startDate, endDate, strategyName, page, limit);
      if (Array.isArray(result) && result.length > 0) {
        this.logPerf("by-strategy", "preagg", Date.now() - preAggStartedAt, {
          startDate: startDate.toISOString(),
          endDate: endDate.toISOString(),
          hasStrategy: Boolean(strategyName),
          page,
          limit,
          resultRows: result.length,
        });
      }
    }

    if (!Array.isArray(result) || result.length === 0) {
      const matchConditions = this.buildMatchConditions(startDate, endDate, strategyName);
      const groupFields = ["strategyId", "strategyName"];
      result = await this.getStatisticsByGroup(matchConditions, groupFields, { clickThroughRate: -1 }, page, limit);
    }

    await this.setCache(cacheKey, result);
    console.log(`[MessageStatsCache] miss key=${cacheKey}`);
    return result;
  }

  /**
   * 按模板获取消息统计数据（含用户点击率）
   */
  public async getStatisticsByTemplate(
    startDate?: Date,
    endDate?: Date,
    strategyName?: string,
    page: number = MessageRecordService.DEFAULT_STATS_PAGE,
    limit: number = MessageRecordService.DEFAULT_STATS_LIMIT,
  ) {
    const cacheKey = this.buildStatsCacheKey("by-template", {
      startDate: startDate?.toISOString(),
      endDate: endDate?.toISOString(),
      strategyName: strategyName || null,
      page,
      limit,
    });
    const cached = await this.getCache(cacheKey);
    if (cached) {
      console.log(`[MessageStatsCache] hit key=${cacheKey}`);
      return cached;
    }
    let result: any[];
    if (MessageRecordService.PREAGG_ENABLED && startDate && endDate) {
      const t0 = Date.now();
      result = await this.getByTemplateFromPreAgg(startDate, endDate, strategyName, page, limit);
      if (result.length > 0) {
        console.log(`[MessageStats] by-template preagg rows=${result.length} ms=${Date.now() - t0}`);
        await this.setCache(cacheKey, result);
        console.log(`[MessageStatsCache] miss key=${cacheKey}`);
        return result;
      }
    }
    const matchConditions = this.buildMatchConditions(startDate, endDate, strategyName);
    // templateId 是 ObjectId，分组时增加基数但对展示无意义，移除后减少第一阶段 group key 大小
    const groupFields = ["templateName"];
    result = await this.getStatisticsByGroup(matchConditions, groupFields, { clickThroughRate: -1 }, page, limit);
    await this.setCache(cacheKey, result);
    console.log(`[MessageStatsCache] miss key=${cacheKey}`);
    return result;
  }

  /**
   * 按国家代码获取消息统计数据（含用户点击率）
   */
  public async getStatisticsByCc(
    startDate?: Date,
    endDate?: Date,
    strategyName?: string,
    page: number = MessageRecordService.DEFAULT_STATS_PAGE,
    limit: number = MessageRecordService.DEFAULT_STATS_LIMIT,
  ) {
    const cacheKey = this.buildStatsCacheKey("by-cc", {
      startDate: startDate?.toISOString(),
      endDate: endDate?.toISOString(),
      strategyName: strategyName || null,
      page,
      limit,
    });
    const cached = await this.getCache(cacheKey);
    if (cached) {
      console.log(`[MessageStatsCache] hit key=${cacheKey}`);
      return cached;
    }
    let result: any[];
    if (MessageRecordService.PREAGG_ENABLED && startDate && endDate) {
      const t0 = Date.now();
      result = await this.getByCcFromPreAgg(startDate, endDate, strategyName, page, limit);
      if (result.length > 0) {
        console.log(`[MessageStats] by-cc preagg rows=${result.length} ms=${Date.now() - t0}`);
        await this.setCache(cacheKey, result);
        console.log(`[MessageStatsCache] miss key=${cacheKey}`);
        return result;
      }
    }
    const matchConditions = this.buildMatchConditions(startDate, endDate, strategyName);
    result = await this.getStatisticsByGroup(matchConditions, ["cc"], { totalRecords: -1 }, page, limit);
    await this.setCache(cacheKey, result);
    console.log(`[MessageStatsCache] miss key=${cacheKey}`);
    return result;
  }

  /**
   * 按图片 URL 获取消息统计数据（含用户点击率）
   */
  public async getStatisticsByImage(
    startDate?: Date,
    endDate?: Date,
    strategyName?: string,
    page: number = MessageRecordService.DEFAULT_STATS_PAGE,
    limit: number = MessageRecordService.DEFAULT_STATS_LIMIT,
  ) {
    const cacheKey = this.buildStatsCacheKey("by-image", {
      startDate: startDate?.toISOString(),
      endDate: endDate?.toISOString(),
      strategyName: strategyName || null,
      page,
      limit,
    });
    const cached = await this.getCache(cacheKey);
    if (cached) {
      console.log(`[MessageStatsCache] hit key=${cacheKey}`);
      return cached;
    }
    let result: any[];
    if (MessageRecordService.PREAGG_ENABLED && startDate && endDate) {
      const t0 = Date.now();
      result = await this.getByImageFromPreAgg(startDate, endDate, strategyName, page, limit);
      if (result.length > 0) {
        console.log(`[MessageStats] by-image preagg rows=${result.length} ms=${Date.now() - t0}`);
        await this.setCache(cacheKey, result);
        console.log(`[MessageStatsCache] miss key=${cacheKey}`);
        return result;
      }
    }
    const matchConditions = this.buildMatchConditions(startDate, endDate, strategyName);
    result = await this.getStatisticsByGroup(matchConditions, ["image"], { clickThroughRate: -1 }, page, limit);
    await this.setCache(cacheKey, result);
    console.log(`[MessageStatsCache] miss key=${cacheKey}`);
    return result;
  }

  /**
   * 按时间维度的趋势分析，每日统计（含用户点击率）
   */
  public async getDailySentTrends(startDate?: Date, endDate?: Date, strategyName?: string) {
    const cacheKey = this.buildStatsCacheKey("daily-trends", {
      startDate: startDate?.toISOString(),
      endDate: endDate?.toISOString(),
      strategyName: strategyName || null,
    });

    const cached = await this.getCache(cacheKey);
    if (cached) {
      console.log(`[MessageStatsCache] hit key=${cacheKey}`);
      return cached;
    }

    let result: any[] = [];
    if (MessageRecordService.PREAGG_ENABLED && startDate && endDate) {
      const preAggStartedAt = Date.now();
      result = await this.getDailyTrendsFromPreAgg(startDate, endDate, strategyName);
      if (Array.isArray(result) && result.length > 0) {
        this.logPerf("daily-trends", "preagg", Date.now() - preAggStartedAt, {
          startDate: startDate.toISOString(),
          endDate: endDate.toISOString(),
          hasStrategy: Boolean(strategyName),
          resultRows: result.length,
        });
      }
    }

    if (!Array.isArray(result) || result.length === 0) {
      result = await this.getDailyTrendsByDimensions(startDate, endDate, strategyName, []);
    }

    await this.setCache(cacheKey, result);
    console.log(`[MessageStatsCache] miss key=${cacheKey}`);
    return result;
  }

  /**
   * 获取消息统计首屏汇总数据。
   * 用于首屏减少多次并行重查询。
   */
  public async getStatisticsSummary(
    startDate?: Date,
    endDate?: Date,
    strategyName?: string,
    page: number = MessageRecordService.DEFAULT_STATS_PAGE,
    limit: number = MessageRecordService.DEFAULT_STATS_LIMIT,
  ) {
    const summaryStartedAt = Date.now();

    const safePage = Math.max(1, Math.floor(page || MessageRecordService.DEFAULT_STATS_PAGE));
    const safeLimit = Math.min(MessageRecordService.MAX_STATS_LIMIT, Math.max(1, Math.floor(limit || MessageRecordService.DEFAULT_STATS_LIMIT)));

    // 预先构建 3 个子查询的 cache key，与各自方法保持完全一致
    const overallCacheKey = this.buildStatsCacheKey("overall", {
      startDate: startDate?.toISOString(),
      endDate: endDate?.toISOString(),
      strategyName: strategyName || null,
    });
    const dailyTrendsCacheKey = this.buildStatsCacheKey("daily-trends", {
      startDate: startDate?.toISOString(),
      endDate: endDate?.toISOString(),
      strategyName: strategyName || null,
    });
    const strategyCacheKey = this.buildStatsCacheKey("by-strategy", {
      startDate: startDate?.toISOString(),
      endDate: endDate?.toISOString(),
      strategyName: strategyName || null,
      page: safePage,
      limit: safeLimit,
    });

    // 并行检查全部缓存
    const [cachedOverall, cachedDailyTrends, cachedStrategy] = await Promise.all([this.getCache(overallCacheKey), this.getCache(dailyTrendsCacheKey), this.getCache(strategyCacheKey)]);

    if (cachedOverall && cachedDailyTrends && cachedStrategy) {
      // 全部命中，直接返回
      console.log(`[MessageStatsCache] hit key=summary:all-3`);
      this.logPerf("summary", "total", Date.now() - summaryStartedAt, {
        cacheHit: true,
        dailyTrendRows: this.resolveResultRows(cachedDailyTrends),
        strategyRows: this.resolveResultRows(cachedStrategy),
      });
      return {
        overall: cachedOverall,
        dailyTrends: cachedDailyTrends,
        strategyStats: cachedStrategy,
        pagination: { page: safePage, limit: safeLimit },
      };
    }

    let overall: any = null;
    let strategyStats: any[] = [];
    let dailyTrends: any[] = [];

    // 任意 cache miss：优先读取预聚合，预聚合不可用时回退实时 facet
    if (MessageRecordService.PREAGG_ENABLED && startDate && endDate) {
      const preAggResult = await this.runSummaryFromPreAgg(startDate, endDate, strategyName, safePage, safeLimit);
      overall = preAggResult.overall;
      strategyStats = preAggResult.strategyStats;
      dailyTrends = preAggResult.dailyTrends;
      this.logPerf("summary", "preagg-query", Date.now() - summaryStartedAt, {
        overallRows: this.resolveResultRows(overall),
        strategyRows: this.resolveResultRows(strategyStats),
        dailyTrendRows: this.resolveResultRows(dailyTrends),
      });
    }

    if (!overall || strategyStats.length === 0 || dailyTrends.length === 0) {
      const matchConditions = this.buildMatchConditions(startDate, endDate, strategyName);
      const facetResult = await this.runSummaryFacetQuery(matchConditions, safePage, safeLimit);
      if (!overall) {
        overall = facetResult.overall;
      }
      if (strategyStats.length === 0) {
        strategyStats = facetResult.strategyStats;
      }
      if (dailyTrends.length === 0) {
        dailyTrends = facetResult.dailyTrends;
      }
    }

    // 回填各子查询缓存（未命中的才写入）
    await Promise.all([
      cachedOverall ? Promise.resolve() : this.setCache(overallCacheKey, overall),
      cachedDailyTrends ? Promise.resolve() : this.setCache(dailyTrendsCacheKey, dailyTrends),
      cachedStrategy ? Promise.resolve() : this.setCache(strategyCacheKey, strategyStats),
    ]);

    const missKeys = [!cachedOverall ? "overall" : null, !cachedDailyTrends ? "daily-trends" : null, !cachedStrategy ? "by-strategy" : null].filter(Boolean).join(",");
    console.log(`[MessageStatsCache] miss key=summary:facet misses=${missKeys}`);

    this.logPerf("summary", "total", Date.now() - summaryStartedAt, {
      dailyTrendRows: this.resolveResultRows(dailyTrends),
      strategyRows: this.resolveResultRows(strategyStats),
    });

    return {
      overall: (cachedOverall as any) || overall,
      dailyTrends: (cachedDailyTrends as any) || dailyTrends,
      strategyStats: (cachedStrategy as any) || strategyStats,
      pagination: { page: safePage, limit: safeLimit },
    };
  }

  /**
   * 按国家代码和时间维度获取每日统计数据（含用户点击率）
   */
  public async getDailyTrendsByCc(cc: string, startDate?: Date, endDate?: Date, strategyName?: string) {
    return this.getDailyTrendsByDimensions(startDate, endDate, strategyName, [{ cc }]);
  }

  /**
   * 按策略名称和时间维度获取每日统计数据（含用户点击率）
   */
  public async getDailyTrendsByStrategy(strategyName: string, startDate?: Date, endDate?: Date) {
    return this.getDailyTrendsByDimensions(startDate, endDate, strategyName, [{ strategyName }]);
  }

  /**
   * 按模板ID和时间维度获取每日统计数据（含用户点击率）
   */
  public async getDailyTrendsByTemplate(templateName: string, startDate?: Date, endDate?: Date, strategyName?: string) {
    return this.getDailyTrendsByDimensions(startDate, endDate, strategyName, [{ templateName }]);
  }

  /**
   * 按图片URL和时间维度获取每日统计数据（含用户点击率）
   */
  public async getDailyTrendsByImage(image: string, startDate?: Date, endDate?: Date, strategyName?: string) {
    return this.getDailyTrendsByDimensions(startDate, endDate, strategyName, [{ image }]);
  }

  /**
   * 按多个维度获取消息推送统计数据（含用户点击率）
   */
  public async getMultiDimensionalStatistics(filters: { startDate?: Date; endDate?: Date; templateName?: string; strategyName?: string; cc?: string; image?: string }) {
    try {
      const startedAt = Date.now();
      const pipeline: any[] = [];

      // 构建匹配阶段
      const matchStage = this.buildMatchConditions(filters.startDate, filters.endDate, filters.strategyName);

      // 添加额外筛选条件
      if (filters.templateName) matchStage.templateName = filters.templateName;
      if (filters.cc) matchStage.cc = filters.cc;
      if (filters.image) matchStage.image = filters.image;

      if (Object.keys(matchStage).length > 0) {
        pipeline.push({ $match: matchStage });
      }

      // 构建分组阶段 - 收集唯一用户ID（按uid去重）
      pipeline.push({
        $group: {
          _id: {
            date: { $dateTrunc: { date: "$createdAt", unit: "day", timezone: MessageRecordService.TIMEZONE } },
            templateName: "$templateName",
            strategyName: "$strategyName",
            cc: "$cc",
            image: "$image",
            status: "$status",
            inforeground: "$inforeground",
          },
          count: { $sum: 1 }, // 人次统计（原逻辑保留）
          uniqueUids: { $addToSet: "$uid" }, // 收集该分组下的唯一用户ID（去重）
        },
      });

      // 汇总阶段 - 计算人数统计
      pipeline.push({
        $group: {
          _id: {
            date: "$_id.date",
            templateName: "$_id.templateName",
            strategyName: "$_id.strategyName",
            cc: "$_id.cc",
            image: "$_id.image",
          },
          totalRecords: { $sum: "$count" },
          ...this.getStatusAggregationFields(), // 包含人次+人数统计
        },
      });

      // 计算比率并格式化输出（含用户点击率）
      pipeline.push({
        $project: this.getStatisticsProjectFields(["date", "templateName", "strategyName", "cc", "image"]),
      });

      // 排序
      pipeline.push({
        $sort: { date: 1, templateName: 1, cc: 1, image: 1 },
      });

      const result = await MessageRecord.aggregate(pipeline).allowDiskUse(true);
      this.logPerf("multi-dimensional", "aggregate", Date.now() - startedAt, {
        resultRows: this.resolveResultRows(result),
        hasDateRange: Boolean(filters.startDate || filters.endDate),
        hasStrategy: Boolean(filters.strategyName),
        hasTemplate: Boolean(filters.templateName),
        hasCc: Boolean(filters.cc),
        hasImage: Boolean(filters.image),
      });
      return result;
    } catch (error) {
      console.error("Error fetching multi-dimensional statistics:", error);
      return [];
    }
  }

  // ------------------------------
  // 私有工具方法 - 提取公共逻辑
  // ------------------------------

  /**
   * 构建日期筛选查询条件
   */
  private buildDateFilterQuery(filters: { [key: string]: any }): any {
    const query: any = {};

    // 处理所有日期字段的筛选
    ["createdAt", "sentAt", "displayedAt", "openedAt"].forEach((field) => {
      if (filters[field]) {
        const dates = (filters[field] as string).split(",");
        const startDate = new Date(dates[0]);

        if (dates.length > 1) {
          const endDate = new Date(dates[1]);
          query[field] = { $gte: startDate, $lte: endDate };
        } else {
          query[field] = { $gte: startDate };
        }
      }
    });

    return query;
  }

  /**
   * 构建匹配条件
   */
  private buildMatchConditions(startDate?: Date, endDate?: Date, strategyName?: string): any {
    const conditions: any = {};

    // 处理日期范围
    if (startDate || endDate) {
      conditions.createdAt = {};

      if (startDate) {
        conditions.createdAt.$gte = startDate;
      }

      if (endDate) {
        // 容错处理：如果 endDate <= startDate，将 endDate 设为 startDate 的后一天
        if (startDate && endDate.getTime() <= startDate.getTime()) {
          endDate = new Date(startDate.getTime() + 24 * 60 * 60 * 1000);
        }
        conditions.createdAt.$lte = endDate;
      }
    }

    // 处理策略名称筛选
    if (strategyName) {
      conditions.strategyName = strategyName;
    }

    return conditions;
  }

  /**
   * 获取状态聚合字段（含人次和人数统计）
   */
  private getStatusAggregationFields() {
    return {
      // 1. 原有人次统计（保留）
      sent: { $sum: { $cond: [{ $gte: ["$_id.status", 1] }, "$count", 0] } },
      delivered: { $sum: { $cond: [{ $gte: ["$_id.status", 2] }, "$count", 0] } },
      opened: { $sum: { $cond: [{ $eq: ["$_id.status", 3] }, "$count", 0] } },
      failed: { $sum: { $cond: [{ $eq: ["$_id.status", -1] }, "$count", 0] } },
      displayCount: {
        $sum: {
          $cond: [{ $and: [{ $gte: ["$_id.status", 2] }, { $eq: ["$_id.inforeground", false] }] }, "$count", 0],
        },
      },

      // 2. 新增：按uid去重的人数统计
      displayedUsers: {
        // 展示人数：至少被展示1次的独立用户数
        $sum: {
          $cond: [
            { $and: [{ $gte: ["$_id.status", 2] }, { $eq: ["$_id.inforeground", false] }] },
            "$uniqueUsers", // 由上游分层聚合提前去重计数
            0,
          ],
        },
      },
      openedUsers: {
        // 点击人数：至少点击1次的独立用户数
        $sum: {
          $cond: [{ $eq: ["$_id.status", 3] }, "$uniqueUsers", 0],
        },
      },
    };
  }

  /**
   * 获取统计结果投影字段（含用户点击率）
   */
  private getStatisticsProjectFields(idFields: string[]) {
    const project: any = { _id: 0 };

    // 显式处理date字段
    if (idFields.includes("date")) {
      project.date = "$_id.date";
    }

    // 添加其他ID字段映射
    idFields.forEach((field) => {
      if (field !== "date") {
        project[field] = `$_id.${field}`;
      }
    });

    // 统计字段：包含原有人次指标 + 新增人数指标 + 用户点击率
    return {
      ...project,
      // 原有人次指标
      totalRecords: "$totalRecords",
      sent: "$sent",
      delivered: "$delivered",
      opened: "$opened",
      failed: "$failed",
      displayCount: "$displayCount",

      // 新增：人数指标
      displayedUsers: "$displayedUsers",
      openedUsers: "$openedUsers",

      // 原有比率指标
      sentSuccessRate: {
        $cond: [{ $eq: ["$totalRecords", 0] }, 0, { $divide: ["$sent", "$totalRecords"] }],
      },
      deliveredRate: {
        $cond: [{ $eq: ["$sent", 0] }, 0, { $divide: ["$delivered", "$sent"] }],
      },
      displayRate: {
        $cond: [{ $eq: ["$delivered", 0] }, 0, { $divide: ["$displayCount", "$delivered"] }],
      },
      clickThroughRate: {
        // 原点击率（人次维度）
        $cond: [{ $eq: ["$displayCount", 0] }, 0, { $divide: ["$opened", "$displayCount"] }],
      },
      tokenInvalidationRate: {
        $cond: [{ $eq: ["$totalRecords", 0] }, 0, { $divide: ["$failed", "$totalRecords"] }],
      },

      // 新增：用户点击率（人数维度）
      actualClickThroughRate: {
        $cond: [
          { $eq: ["$displayedUsers", 0] }, // 避免除以0
          0,
          { $divide: ["$openedUsers", "$displayedUsers"] }, // 点击人数 / 展示人数
        ],
      },
    };
  }

  private buildPreAggMatch(startDate: Date, endDate: Date, strategyName?: string): any {
    const startKey = this.formatDateKeyInTimezone(startDate);
    const endKey = this.formatDateKeyInTimezone(endDate);
    const match: any = {
      dateKey: {
        $gte: startKey,
        $lte: endKey,
      },
    };
    if (strategyName) {
      match.strategyName = strategyName;
    }
    return match;
  }

  private async getOverallFromPreAgg(startDate: Date, endDate: Date, strategyName?: string) {
    const pipeline: any[] = [
      { $match: this.buildPreAggMatch(startDate, endDate, strategyName) },
      {
        $group: {
          _id: { status: "$status", inforeground: "$inforeground", uid: "$uid" },
          count: { $sum: "$msgCount" },
        },
      },
      {
        $group: {
          _id: { status: "$_id.status", inforeground: "$_id.inforeground" },
          count: { $sum: "$count" },
          uniqueUsers: { $sum: 1 },
        },
      },
      {
        $group: {
          _id: null,
          totalRecords: { $sum: "$count" },
          ...this.getStatusAggregationFields(),
        },
      },
      { $project: this.getStatisticsProjectFields([]) },
    ];

    const [result] = await MessageStatsDailyUid.aggregate(pipeline).allowDiskUse(true);
    return result || null;
  }

  private async getByStrategyFromPreAgg(startDate: Date, endDate: Date, strategyName: string | undefined, page: number, limit: number) {
    const safePage = Math.max(1, Math.floor(page || MessageRecordService.DEFAULT_STATS_PAGE));
    const safeLimit = Math.min(MessageRecordService.MAX_STATS_LIMIT, Math.max(1, Math.floor(limit || MessageRecordService.DEFAULT_STATS_LIMIT)));

    const pipeline: any[] = [
      { $match: this.buildPreAggMatch(startDate, endDate, strategyName) },
      {
        $group: {
          _id: {
            strategyId: "$strategyId",
            strategyName: "$strategyName",
            status: "$status",
            inforeground: "$inforeground",
            uid: "$uid",
          },
          count: { $sum: "$msgCount" },
        },
      },
      {
        $group: {
          _id: {
            strategyId: "$_id.strategyId",
            strategyName: "$_id.strategyName",
            status: "$_id.status",
            inforeground: "$_id.inforeground",
          },
          count: { $sum: "$count" },
          uniqueUsers: { $sum: 1 },
        },
      },
      {
        $group: {
          _id: { strategyId: "$_id.strategyId", strategyName: "$_id.strategyName" },
          strategyId: { $first: "$_id.strategyId" },
          strategyName: { $first: "$_id.strategyName" },
          totalRecords: { $sum: "$count" },
          ...this.getStatusAggregationFields(),
        },
      },
      { $project: this.getStatisticsProjectFields(["strategyId", "strategyName"]) },
      { $sort: { clickThroughRate: -1 } },
      { $skip: (safePage - 1) * safeLimit },
      { $limit: safeLimit },
    ];

    return MessageStatsDailyUid.aggregate(pipeline).allowDiskUse(true);
  }

  private async getByTemplateFromPreAgg(startDate: Date, endDate: Date, strategyName: string | undefined, page: number, limit: number) {
    const safePage = Math.max(1, Math.floor(page || MessageRecordService.DEFAULT_STATS_PAGE));
    const safeLimit = Math.min(MessageRecordService.MAX_STATS_LIMIT, Math.max(1, Math.floor(limit || MessageRecordService.DEFAULT_STATS_LIMIT)));

    const pipeline: any[] = [
      { $match: this.buildPreAggMatch(startDate, endDate, strategyName) },
      {
        $group: {
          _id: {
            templateName: "$templateName",
            status: "$status",
            inforeground: "$inforeground",
            uid: "$uid",
          },
          count: { $sum: "$msgCount" },
        },
      },
      {
        $group: {
          _id: {
            templateName: "$_id.templateName",
            status: "$_id.status",
            inforeground: "$_id.inforeground",
          },
          count: { $sum: "$count" },
          uniqueUsers: { $sum: 1 },
        },
      },
      {
        $group: {
          _id: { templateName: "$_id.templateName" },
          templateName: { $first: "$_id.templateName" },
          totalRecords: { $sum: "$count" },
          ...this.getStatusAggregationFields(),
        },
      },
      { $project: this.getStatisticsProjectFields(["templateName"]) },
      { $sort: { clickThroughRate: -1 } },
      { $skip: (safePage - 1) * safeLimit },
      { $limit: safeLimit },
    ];

    return MessageStatsDailyUid.aggregate(pipeline).allowDiskUse(true);
  }

  private async getByCcFromPreAgg(startDate: Date, endDate: Date, strategyName: string | undefined, page: number, limit: number) {
    const safePage = Math.max(1, Math.floor(page || MessageRecordService.DEFAULT_STATS_PAGE));
    const safeLimit = Math.min(MessageRecordService.MAX_STATS_LIMIT, Math.max(1, Math.floor(limit || MessageRecordService.DEFAULT_STATS_LIMIT)));

    const pipeline: any[] = [
      { $match: this.buildPreAggMatch(startDate, endDate, strategyName) },
      {
        $group: {
          _id: {
            cc: "$cc",
            status: "$status",
            inforeground: "$inforeground",
            uid: "$uid",
          },
          count: { $sum: "$msgCount" },
        },
      },
      {
        $group: {
          _id: {
            cc: "$_id.cc",
            status: "$_id.status",
            inforeground: "$_id.inforeground",
          },
          count: { $sum: "$count" },
          uniqueUsers: { $sum: 1 },
        },
      },
      {
        $group: {
          _id: { cc: "$_id.cc" },
          cc: { $first: "$_id.cc" },
          totalRecords: { $sum: "$count" },
          ...this.getStatusAggregationFields(),
        },
      },
      { $project: this.getStatisticsProjectFields(["cc"]) },
      { $sort: { totalRecords: -1 } },
      { $skip: (safePage - 1) * safeLimit },
      { $limit: safeLimit },
    ];

    return MessageStatsDailyUid.aggregate(pipeline).allowDiskUse(true);
  }

  private async getByImageFromPreAgg(startDate: Date, endDate: Date, strategyName: string | undefined, page: number, limit: number) {
    const safePage = Math.max(1, Math.floor(page || MessageRecordService.DEFAULT_STATS_PAGE));
    const safeLimit = Math.min(MessageRecordService.MAX_STATS_LIMIT, Math.max(1, Math.floor(limit || MessageRecordService.DEFAULT_STATS_LIMIT)));

    const pipeline: any[] = [
      { $match: this.buildPreAggMatch(startDate, endDate, strategyName) },
      {
        $group: {
          _id: {
            image: "$image",
            status: "$status",
            inforeground: "$inforeground",
            uid: "$uid",
          },
          count: { $sum: "$msgCount" },
        },
      },
      {
        $group: {
          _id: {
            image: "$_id.image",
            status: "$_id.status",
            inforeground: "$_id.inforeground",
          },
          count: { $sum: "$count" },
          uniqueUsers: { $sum: 1 },
        },
      },
      {
        $group: {
          _id: { image: "$_id.image" },
          image: { $first: "$_id.image" },
          totalRecords: { $sum: "$count" },
          ...this.getStatusAggregationFields(),
        },
      },
      { $project: this.getStatisticsProjectFields(["image"]) },
      { $sort: { clickThroughRate: -1 } },
      { $skip: (safePage - 1) * safeLimit },
      { $limit: safeLimit },
    ];

    return MessageStatsDailyUid.aggregate(pipeline).allowDiskUse(true);
  }

  private async getDailyTrendsFromPreAgg(startDate: Date, endDate: Date, strategyName?: string) {
    const pipeline: any[] = [
      { $match: this.buildPreAggMatch(startDate, endDate, strategyName) },
      {
        $group: {
          _id: {
            date: "$date",
            status: "$status",
            inforeground: "$inforeground",
            uid: "$uid",
          },
          count: { $sum: "$msgCount" },
        },
      },
      {
        $group: {
          _id: { date: "$_id.date", status: "$_id.status", inforeground: "$_id.inforeground" },
          count: { $sum: "$count" },
          uniqueUsers: { $sum: 1 },
        },
      },
      {
        $group: {
          _id: "$_id.date",
          date: { $first: "$_id.date" },
          totalRecords: { $sum: "$count" },
          ...this.getStatusAggregationFields(),
        },
      },
      {
        $project: {
          _id: 0,
          date: "$date",
          totalRecords: "$totalRecords",
          sent: "$sent",
          delivered: "$delivered",
          opened: "$opened",
          failed: "$failed",
          displayCount: "$displayCount",
          displayedUsers: "$displayedUsers",
          openedUsers: "$openedUsers",
          sentSuccessRate: {
            $cond: [{ $eq: ["$totalRecords", 0] }, 0, { $divide: ["$sent", "$totalRecords"] }],
          },
          deliveredRate: {
            $cond: [{ $eq: ["$sent", 0] }, 0, { $divide: ["$delivered", "$sent"] }],
          },
          displayRate: {
            $cond: [{ $eq: ["$delivered", 0] }, 0, { $divide: ["$displayCount", "$delivered"] }],
          },
          clickThroughRate: {
            $cond: [{ $eq: ["$displayCount", 0] }, 0, { $divide: ["$opened", "$displayCount"] }],
          },
          tokenInvalidationRate: {
            $cond: [{ $eq: ["$totalRecords", 0] }, 0, { $divide: ["$failed", "$totalRecords"] }],
          },
          actualClickThroughRate: {
            $cond: [{ $eq: ["$displayedUsers", 0] }, 0, { $divide: ["$openedUsers", "$displayedUsers"] }],
          },
        },
      },
      { $sort: { date: -1 } },
    ];

    return MessageStatsDailyUid.aggregate(pipeline).allowDiskUse(true);
  }

  private async runSummaryFromPreAgg(
    startDate: Date,
    endDate: Date,
    strategyName: string | undefined,
    page: number,
    limit: number,
  ): Promise<{ overall: any; strategyStats: any[]; dailyTrends: any[] }> {
    const [overall, strategyStats, dailyTrends] = await Promise.all([
      this.getOverallFromPreAgg(startDate, endDate, strategyName),
      this.getByStrategyFromPreAgg(startDate, endDate, strategyName, page, limit),
      this.getDailyTrendsFromPreAgg(startDate, endDate, strategyName),
    ]);

    return {
      overall,
      strategyStats: strategyStats || [],
      dailyTrends: dailyTrends || [],
    };
  }

  /**
   * summary 首屏接口专用：单次 $facet 查询同时计算 overall / byStrategy / dailyTrends，
   * 替代 3 次独立聚合，减少对同一时间窗口数据的重复扫描。
   */
  private async runSummaryFacetQuery(matchConditions: any, page: number, limit: number): Promise<{ overall: any; strategyStats: any[]; dailyTrends: any[] }> {
    const startedAt = Date.now();

    // overall 子管道（等价于 getStatisticsByGroup(matchConditions, [])）
    const overallFacet: any[] = [
      {
        $group: {
          _id: { status: "$status", inforeground: "$inforeground", uid: "$uid" },
          msgCount: { $sum: 1 },
        },
      },
      {
        $group: {
          _id: { status: "$_id.status", inforeground: "$_id.inforeground" },
          count: { $sum: "$msgCount" },
          uniqueUsers: { $sum: 1 },
        },
      },
      {
        $group: {
          _id: null,
          totalRecords: { $sum: "$count" },
          ...this.getStatusAggregationFields(),
        },
      },
      { $project: this.getStatisticsProjectFields([]) },
    ];

    // byStrategy 子管道（等价于 getStatisticsByGroup(matchConditions, ["strategyId","strategyName"], {clickThroughRate:-1}, page, limit)）
    const strategyFacet: any[] = [
      {
        $group: {
          _id: {
            strategyId: "$strategyId",
            strategyName: "$strategyName",
            status: "$status",
            inforeground: "$inforeground",
            uid: "$uid",
          },
          msgCount: { $sum: 1 },
        },
      },
      {
        $group: {
          _id: {
            strategyId: "$_id.strategyId",
            strategyName: "$_id.strategyName",
            status: "$_id.status",
            inforeground: "$_id.inforeground",
          },
          count: { $sum: "$msgCount" },
          uniqueUsers: { $sum: 1 },
        },
      },
      {
        $group: {
          _id: { strategyId: "$_id.strategyId", strategyName: "$_id.strategyName" },
          strategyId: { $first: "$_id.strategyId" },
          strategyName: { $first: "$_id.strategyName" },
          totalRecords: { $sum: "$count" },
          ...this.getStatusAggregationFields(),
        },
      },
      { $project: this.getStatisticsProjectFields(["strategyId", "strategyName"]) },
      { $sort: { clickThroughRate: -1 } },
      { $skip: (page - 1) * limit },
      { $limit: limit },
    ];

    // dailyTrends 子管道（等价于 getDailyTrendsByDimensions，不含 $match 因已在外层处理）
    const dailyTrendsFacet: any[] = [
      {
        $group: {
          _id: {
            date: { $dateTrunc: { date: "$createdAt", unit: "day", timezone: MessageRecordService.TIMEZONE } },
            status: "$status",
            inforeground: "$inforeground",
            uid: "$uid",
          },
          msgCount: { $sum: 1 },
        },
      },
      {
        $group: {
          _id: { date: "$_id.date", status: "$_id.status", inforeground: "$_id.inforeground" },
          count: { $sum: "$msgCount" },
          uniqueUsers: { $sum: 1 },
        },
      },
      {
        $group: {
          _id: "$_id.date",
          date: { $first: "$_id.date" },
          totalRecords: { $sum: "$count" },
          ...this.getStatusAggregationFields(),
        },
      },
      {
        $project: {
          _id: 0,
          date: "$date",
          totalRecords: "$totalRecords",
          sent: "$sent",
          delivered: "$delivered",
          opened: "$opened",
          failed: "$failed",
          displayCount: "$displayCount",
          displayedUsers: "$displayedUsers",
          openedUsers: "$openedUsers",
          sentSuccessRate: {
            $cond: [{ $eq: ["$totalRecords", 0] }, 0, { $divide: ["$sent", "$totalRecords"] }],
          },
          deliveredRate: {
            $cond: [{ $eq: ["$sent", 0] }, 0, { $divide: ["$delivered", "$sent"] }],
          },
          displayRate: {
            $cond: [{ $eq: ["$delivered", 0] }, 0, { $divide: ["$displayCount", "$delivered"] }],
          },
          clickThroughRate: {
            $cond: [{ $eq: ["$displayCount", 0] }, 0, { $divide: ["$opened", "$displayCount"] }],
          },
          tokenInvalidationRate: {
            $cond: [{ $eq: ["$totalRecords", 0] }, 0, { $divide: ["$failed", "$totalRecords"] }],
          },
          actualClickThroughRate: {
            $cond: [{ $eq: ["$displayedUsers", 0] }, 0, { $divide: ["$openedUsers", "$displayedUsers"] }],
          },
        },
      },
      { $sort: { date: -1 } },
    ];

    const pipeline: any[] = [
      { $match: matchConditions },
      {
        $facet: {
          overall: overallFacet,
          strategyStats: strategyFacet,
          dailyTrends: dailyTrendsFacet,
        },
      },
    ];

    const [facetResult] = await MessageRecord.aggregate(pipeline).allowDiskUse(true);

    this.logPerf("summary", "facet-query", Date.now() - startedAt, {
      overallRows: this.resolveResultRows(facetResult?.overall?.[0]),
      strategyRows: facetResult?.strategyStats?.length ?? 0,
      dailyTrendRows: facetResult?.dailyTrends?.length ?? 0,
    });

    return {
      overall: facetResult?.overall?.[0] ?? null,
      strategyStats: facetResult?.strategyStats ?? [],
      dailyTrends: facetResult?.dailyTrends ?? [],
    };
  }

  /**
   * 按分组获取统计数据的通用方法（支持用户点击率）
   */
  private async getStatisticsByGroup(
    matchConditions: any,
    groupFields: string[] = [],
    sortOrder: any = { _id: 1 },
    page: number = MessageRecordService.DEFAULT_STATS_PAGE,
    limit: number = MessageRecordService.DEFAULT_STATS_LIMIT,
  ) {
    try {
      const startedAt = Date.now();
      const pipeline: any[] = [];

      // 添加匹配阶段
      if (Object.keys(matchConditions).length > 0) {
        pipeline.push({ $match: matchConditions });
      }

      // 构建细粒度分组键（包含uid，先做去重基底）
      const groupId: any = {};
      groupFields.forEach((field) => {
        groupId[field] = `$${field}`;
      });
      groupId.status = "$status";
      groupId.inforeground = "$inforeground";
      groupId.uid = "$uid";

      // 第一阶段分组 - 维度+状态+uid 聚合，避免使用 $addToSet 大数组
      pipeline.push({
        $group: {
          _id: groupId,
          msgCount: { $sum: 1 },
        },
      });

      // 第二阶段分组 - 收敛到维度+状态，保留人次统计和去重人数统计
      const statusLevelGroupId: any = {};
      groupFields.forEach((field) => {
        statusLevelGroupId[field] = `$_id.${field}`;
      });
      statusLevelGroupId.status = "$_id.status";
      statusLevelGroupId.inforeground = "$_id.inforeground";

      pipeline.push({
        $group: {
          _id: statusLevelGroupId,
          count: { $sum: "$msgCount" },
          uniqueUsers: { $sum: 1 },
        },
      });

      // 第三阶段分组 - 按主要维度汇总
      const mainGroupId: any = {};
      groupFields.forEach((field) => {
        mainGroupId[field] = `$_id.${field}`;
      });

      pipeline.push({
        $group: {
          _id: mainGroupId,
          ...groupFields.reduce((acc, field) => {
            acc[field] = { $first: `$_id.${field}` };
            return acc;
          }, {} as any),
          totalRecords: { $sum: "$count" },
          ...this.getStatusAggregationFields(), // 包含人次+人数统计
        },
      });

      // 计算比率并格式化输出（含用户点击率）
      pipeline.push({
        $project: this.getStatisticsProjectFields(groupFields),
      });

      // 添加排序
      pipeline.push({ $sort: sortOrder });

      if (groupFields.length > 0) {
        const safePage = Math.max(1, Math.floor(page || MessageRecordService.DEFAULT_STATS_PAGE));
        const safeLimit = Math.min(MessageRecordService.MAX_STATS_LIMIT, Math.max(1, Math.floor(limit || MessageRecordService.DEFAULT_STATS_LIMIT)));
        pipeline.push({ $skip: (safePage - 1) * safeLimit });
        pipeline.push({ $limit: safeLimit });
      }

      const results = await MessageRecord.aggregate(pipeline).allowDiskUse(true);
      const output = groupFields.length === 0 ? results[0] : results;
      this.logPerf("statistics-by-group", "aggregate", Date.now() - startedAt, {
        groupFields: groupFields.join(",") || "overall",
        matchKeyCount: Object.keys(matchConditions || {}).length,
        resultRows: this.resolveResultRows(output),
        page,
        limit,
      });
      return output;
    } catch (error) {
      console.error("Error fetching statistics by group:", error);
      return groupFields.length === 0 ? null : [];
    }
  }

  /**
   * 按维度获取每日趋势的通用方法（支持用户点击率）
   */
  private async getDailyTrendsByDimensions(startDate?: Date, endDate?: Date, strategyName?: string, extraConditions: any[] = []) {
    try {
      const startedAt = Date.now();
      const pipeline: any[] = [];

      // 构建匹配条件
      let matchConditions = this.buildMatchConditions(startDate, endDate, strategyName);

      // 添加额外条件
      extraConditions.forEach((cond) => {
        matchConditions = { ...matchConditions, ...cond };
      });

      pipeline.push({ $match: matchConditions });

      // 先按日期、状态、前景、uid 聚合，内联 $dateTrunc 到 $group._id，省去一次文档物化
      pipeline.push({
        $group: {
          _id: {
            date: { $dateTrunc: { date: "$createdAt", unit: "day", timezone: MessageRecordService.TIMEZONE } },
            status: "$status",
            inforeground: "$inforeground",
            uid: "$uid",
          },
          msgCount: { $sum: 1 },
        },
      });

      // 再收敛到日期+状态+前景，得到人次和去重人数
      pipeline.push({
        $group: {
          _id: { date: "$_id.date", status: "$_id.status", inforeground: "$_id.inforeground" },
          count: { $sum: "$msgCount" },
          uniqueUsers: { $sum: 1 },
        },
      });

      // 按日期汇总 - 计算人数统计
      pipeline.push({
        $group: {
          _id: "$_id.date",
          date: { $first: "$_id.date" }, // 显式保留date字段
          totalRecords: { $sum: "$count" },
          ...this.getStatusAggregationFields(), // 包含人次+人数统计
        },
      });

      // 计算比率并格式化输出（含用户点击率）
      pipeline.push({
        $project: {
          _id: 0,
          date: "$date",
          totalRecords: "$totalRecords",
          sent: "$sent",
          delivered: "$delivered",
          opened: "$opened",
          failed: "$failed",
          displayCount: "$displayCount",
          displayedUsers: "$displayedUsers",
          openedUsers: "$openedUsers",
          sentSuccessRate: {
            $cond: [{ $eq: ["$totalRecords", 0] }, 0, { $divide: ["$sent", "$totalRecords"] }],
          },
          deliveredRate: {
            $cond: [{ $eq: ["$sent", 0] }, 0, { $divide: ["$delivered", "$sent"] }],
          },
          displayRate: {
            $cond: [{ $eq: ["$delivered", 0] }, 0, { $divide: ["$displayCount", "$delivered"] }],
          },
          clickThroughRate: {
            $cond: [{ $eq: ["$displayCount", 0] }, 0, { $divide: ["$opened", "$displayCount"] }],
          },
          tokenInvalidationRate: {
            $cond: [{ $eq: ["$totalRecords", 0] }, 0, { $divide: ["$failed", "$totalRecords"] }],
          },
          actualClickThroughRate: {
            $cond: [{ $eq: ["$displayedUsers", 0] }, 0, { $divide: ["$openedUsers", "$displayedUsers"] }],
          },
        },
      });

      // 按日期排序
      pipeline.push({
        $sort: { date: -1 },
      });

      const result = await MessageRecord.aggregate(pipeline).allowDiskUse(true);
      this.logPerf("daily-trends", "aggregate", Date.now() - startedAt, {
        hasDateRange: Boolean(startDate || endDate),
        hasStrategy: Boolean(strategyName),
        extraConditionCount: extraConditions.length,
        resultRows: this.resolveResultRows(result),
      });
      return result;
    } catch (error) {
      console.error("Error fetching daily trends:", error);
      return [];
    }
  }

  private buildStatsCacheKey(endpoint: string, params: Record<string, unknown>): string {
    const normalized: Record<string, unknown> = {};
    Object.keys(params)
      .sort()
      .forEach((key) => {
        normalized[key] = params[key] === undefined ? null : params[key];
      });

    return `message_stats:${endpoint}:${JSON.stringify(normalized)}`;
  }

  private async getCache<T>(key: string): Promise<T | null> {
    try {
      if (!redisClient.isOpen) {
        return null;
      }
      const cached = await redisClient.get(key);
      if (!cached) {
        return null;
      }
      return JSON.parse(cached) as T;
    } catch (error) {
      console.warn(`[MessageStatsCache] get failed key=${key}`, error);
      return null;
    }
  }

  private async setCache(key: string, value: unknown): Promise<void> {
    try {
      if (!redisClient.isOpen) {
        return;
      }
      await redisClient.setEx(key, MessageRecordService.STATS_CACHE_TTL_SECONDS, JSON.stringify(value));
    } catch (error) {
      console.warn(`[MessageStatsCache] set failed key=${key}`, error);
    }
  }
}
