multi-platform-media-manage/server/src/services/WorkDayStatisticsService.ts

import { AppDataSource, WorkDayStatistics, Work, PlatformAccount, UserDayStatistics } from '../models/index.js';
import { Between, In } from 'typeorm';
import { logger } from '../utils/logger.js';

interface StatisticsItem {
  workId: number;
  playCount?: number;
  likeCount?: number;
  commentCount?: number;
  shareCount?: number;
  collectCount?: number;
}

interface SaveResult {
  inserted: number;
  updated: number;
}

interface TrendData {
  dates: string[];
  fans: number[];
  views: number[];
  likes: number[];
  comments: number[];
  shares: number[];
  collects: number[];
}

interface PlatformStatItem {
  platform: string;
  fansCount: number;
  fansIncrease: number;
  viewsCount: number;
  likesCount: number;
  commentsCount: number;
  collectsCount: number;
  updateTime?: string;
}

interface WorkStatisticsItem {
  recordDate: string;
  playCount: number;
  likeCount: number;
  commentCount: number;
  shareCount: number;
  collectCount: number;
}

export class WorkDayStatisticsService {
  private statisticsRepository = AppDataSource.getRepository(WorkDayStatistics);
  private workRepository = AppDataSource.getRepository(Work);
  private accountRepository = AppDataSource.getRepository(PlatformAccount);
  private userDayStatisticsRepository = AppDataSource.getRepository(UserDayStatistics);

  private formatDate(d: Date) {
    const yyyy = d.getFullYear();
    const mm = String(d.getMonth() + 1).padStart(2, '0');
    const dd = String(d.getDate()).padStart(2, '0');
    return `${yyyy}-${mm}-${dd}`;
  }

  /**
   * 获取某个账号在指定日期（<= targetDate）时各作品的“最新一条”累计数据总和
   * 口径：对该账号所有作品，每个作品取 record_date <= targetDate 的最大日期那条记录，然后把 play/like/comment/collect 求和
   */
  private async getWorkSumsAtDate(
    workIds: number[],
    targetDate: string
  ): Promise<{ views: number; likes: number; comments: number; collects: number }> {
    if (!workIds.length) {
      return { views: 0, likes: 0, comments: 0, collects: 0 };
    }

    // MySQL: 派生表先取每个作品 <= targetDate 的最新日期，再回连取该日数据求和
    // 注意：workIds 使用 IN (...)，由 TypeORM 负责参数化，避免注入
    const placeholders = workIds.map(() => '?').join(',');
    const sql = `
      SELECT
        COALESCE(SUM(wds.play_count), 0) AS views,
        COALESCE(SUM(wds.like_count), 0) AS likes,
        COALESCE(SUM(wds.comment_count), 0) AS comments,
        COALESCE(SUM(wds.collect_count), 0) AS collects
      FROM work_day_statistics wds
      INNER JOIN (
        SELECT wds2.work_id, MAX(wds2.record_date) AS record_date
        FROM work_day_statistics wds2
        WHERE wds2.work_id IN (${placeholders})
          AND wds2.record_date <= ?
        GROUP BY wds2.work_id
      ) latest
      ON latest.work_id = wds.work_id AND latest.record_date = wds.record_date
    `;

    const rows = await AppDataSource.query(sql, [...workIds, targetDate]);
    const row = rows?.[0] || {};
    return {
      views: Number(row.views) || 0,
      likes: Number(row.likes) || 0,
      comments: Number(row.comments) || 0,
      collects: Number(row.collects) || 0,
    };
  }

  /**
   * 保存作品日统计数据
   * 当天的数据走更新流，日期变化走新增流
   */
  async saveStatistics(statistics: StatisticsItem[]): Promise<SaveResult> {
    const today = new Date();
    today.setHours(0, 0, 0, 0);

    let insertedCount = 0;
    let updatedCount = 0;

    for (const stat of statistics) {
      if (!stat.workId) continue;

      // 检查当天是否已有记录
      const existing = await this.statisticsRepository.findOne({
        where: {
          workId: stat.workId,
          recordDate: today,
        },
      });

      if (existing) {
        // 更新已有记录（不再包含粉丝数，粉丝数从 user_day_statistics 表获取）
        await this.statisticsRepository.update(existing.id, {
          playCount: stat.playCount ?? existing.playCount,
          likeCount: stat.likeCount ?? existing.likeCount,
          commentCount: stat.commentCount ?? existing.commentCount,
          shareCount: stat.shareCount ?? existing.shareCount,
          collectCount: stat.collectCount ?? existing.collectCount,
        });
        updatedCount++;
      } else {
        // 插入新记录（不再包含粉丝数，粉丝数从 user_day_statistics 表获取）
        const newStat = this.statisticsRepository.create({
          workId: stat.workId,
          recordDate: today,
          playCount: stat.playCount ?? 0,
          likeCount: stat.likeCount ?? 0,
          commentCount: stat.commentCount ?? 0,
          shareCount: stat.shareCount ?? 0,
          collectCount: stat.collectCount ?? 0,
        });
        await this.statisticsRepository.save(newStat);
        insertedCount++;
      }
    }

    return { inserted: insertedCount, updated: updatedCount };
  }

  /**
   * 获取数据趋势
   */
  async getTrend(
    userId: number,
    options: {
      days?: number;
      startDate?: string;
      endDate?: string;
      accountId?: number;
    }
  ): Promise<TrendData> {
    const { days = 7, startDate, endDate, accountId } = options;

    // 计算日期范围
    let dateStart: Date;
    let dateEnd: Date;

    if (startDate && endDate) {
      dateStart = new Date(startDate);
      dateEnd = new Date(endDate);
    } else {
      dateEnd = new Date();
      dateStart = new Date();
      dateStart.setDate(dateStart.getDate() - Math.min(days, 30) + 1);
    }

    // 构建查询（不再从 work_day_statistics 读取粉丝数，粉丝数从 user_day_statistics 表获取）
    const queryBuilder = this.statisticsRepository
      .createQueryBuilder('wds')
      .innerJoin(Work, 'w', 'wds.work_id = w.id')
      .select('wds.record_date', 'recordDate')
      .addSelect('w.accountId', 'accountId')
      .addSelect('SUM(wds.play_count)', 'accountViews')
      .addSelect('SUM(wds.like_count)', 'accountLikes')
      .addSelect('SUM(wds.comment_count)', 'accountComments')
      .addSelect('SUM(wds.share_count)', 'accountShares')
      .addSelect('SUM(wds.collect_count)', 'accountCollects')
      .where('w.userId = :userId', { userId })
      .andWhere('wds.record_date >= :dateStart', { dateStart })
      .andWhere('wds.record_date <= :dateEnd', { dateEnd })
      .groupBy('wds.record_date')
      .addGroupBy('w.accountId')
      .orderBy('wds.record_date', 'ASC');

    if (accountId) {
      queryBuilder.andWhere('w.accountId = :accountId', { accountId });
    }

    const accountResults = await queryBuilder.getRawMany();

    // 按日期汇总所有账号的数据
    const dateMap = new Map<string, {
      fans: number;
      views: number;
      likes: number;
      comments: number;
      shares: number;
      collects: number;
    }>();

    for (const row of accountResults) {
      const dateKey = row.recordDate instanceof Date 
        ? row.recordDate.toISOString().split('T')[0]
        : String(row.recordDate).split('T')[0];
      
      if (!dateMap.has(dateKey)) {
        dateMap.set(dateKey, {
          fans: 0,
          views: 0,
          likes: 0,
          comments: 0,
          shares: 0,
          collects: 0,
        });
      }

      const current = dateMap.get(dateKey)!;
      current.fans += parseInt(row.accountFans) || 0;
      current.views += parseInt(row.accountViews) || 0;
      current.likes += parseInt(row.accountLikes) || 0;
      current.comments += parseInt(row.accountComments) || 0;
      current.shares += parseInt(row.accountShares) || 0;
      current.collects += parseInt(row.accountCollects) || 0;
    }

    // 构建响应数据
    const dates: string[] = [];
    const fans: number[] = [];
    const views: number[] = [];
    const likes: number[] = [];
    const comments: number[] = [];
    const shares: number[] = [];
    const collects: number[] = [];

    // 按日期排序
    const sortedDates = Array.from(dateMap.keys()).sort();
    for (const dateKey of sortedDates) {
      dates.push(dateKey.slice(5)); // "YYYY-MM-DD" -> "MM-DD"
      const data = dateMap.get(dateKey)!;
      fans.push(data.fans);
      views.push(data.views);
      likes.push(data.likes);
      comments.push(data.comments);
      shares.push(data.shares);
      collects.push(data.collects);
    }

    // 如果没有数据，生成空的日期范围
    if (dates.length === 0) {
      const d = new Date(dateStart);
      while (d <= dateEnd) {
        dates.push(`${String(d.getMonth() + 1).padStart(2, '0')}-${String(d.getDate()).padStart(2, '0')}`);
        fans.push(0);
        views.push(0);
        likes.push(0);
        comments.push(0);
        shares.push(0);
        collects.push(0);
        d.setDate(d.getDate() + 1);
      }
    }

    return { dates, fans, views, likes, comments, shares, collects };
  }

  /**
   * 按平台分组获取统计数据
   */
  async getStatisticsByPlatform(
    userId: number,
    options: {
      days?: number;
      startDate?: string;
      endDate?: string;
    }
  ): Promise<PlatformStatItem[]> {
    const { days = 30, startDate, endDate } = options;

    // 计算日期范围
    let dateStart: Date;
    let dateEnd: Date;

    if (startDate && endDate) {
      dateStart = new Date(startDate);
      dateEnd = new Date(endDate);
    } else {
      dateEnd = new Date();
      dateStart = new Date();
      dateStart.setDate(dateStart.getDate() - Math.min(days, 30) + 1);
    }

    const endDateStr = endDate ? endDate : this.formatDate(dateEnd);
    const startDateStr = startDate ? startDate : this.formatDate(dateStart);
    const isSingleDay = startDateStr === endDateStr;
    // 单日查询（如“昨天”）按“当日增量”口径：当日粉丝 - 前一日粉丝
    const startBaselineStr = (() => {
      if (!isSingleDay) return startDateStr;
      const d = new Date(endDateStr);
      d.setDate(d.getDate() - 1);
      return this.formatDate(d);
    })();

    // 获取用户的所有账号
    const accounts = await this.accountRepository.find({
      where: { userId },
    });

    // 按平台聚合数据：Map<platform, { fansCount, fansIncrease, viewsCount, likesCount, commentsCount, collectsCount, latestUpdateTime }>
    const platformMap = new Map<string, {
      fansCount: number;
      fansIncrease: number;
      viewsCount: number;
      likesCount: number;
      commentsCount: number;
      collectsCount: number;
      latestUpdateTime: Date | null;
    }>();

    // 遍历每个账号，计算该账号的数据，然后累加到对应平台
    for (const account of accounts) {
      // 获取该账号的作品列表（用于按“所有作品累计值之和”计算）
      const works = await this.workRepository.find({
        where: { accountId: account.id },
        select: ['id'],
      });
      const workIds = works.map(w => w.id);

      // ===== 粉丝口径修正 =====
      // 取 endDate 当天粉丝（若当天没有记录，则取 <= endDate 的最近一条）
      const endUserStat =
        (await this.userDayStatisticsRepository
          .createQueryBuilder('uds')
          .where('uds.account_id = :accountId', { accountId: account.id })
          .andWhere('DATE(uds.record_date) = :d', { d: endDateStr })
          .getOne()) ??
        (await this.userDayStatisticsRepository
          .createQueryBuilder('uds')
          .where('uds.account_id = :accountId', { accountId: account.id })
          .andWhere('DATE(uds.record_date) <= :d', { d: endDateStr })
          .orderBy('uds.record_date', 'DESC')
          .getOne());

      const endFans = endUserStat?.fansCount ?? account.fansCount ?? 0;

      // 取 baseline 当天粉丝（若没有记录，则取 <= baseline 的最近一条）
      const baselineUserStat =
        (await this.userDayStatisticsRepository
          .createQueryBuilder('uds')
          .where('uds.account_id = :accountId', { accountId: account.id })
          .andWhere('DATE(uds.record_date) = :d', { d: startBaselineStr })
          .getOne()) ??
        (await this.userDayStatisticsRepository
          .createQueryBuilder('uds')
          .where('uds.account_id = :accountId', { accountId: account.id })
          .andWhere('DATE(uds.record_date) <= :d', { d: startBaselineStr })
          .orderBy('uds.record_date', 'DESC')
          .getOne());

      const baselineFans = baselineUserStat?.fansCount ?? endFans;
      // 涨粉量允许为负数（掉粉），不做截断
      const accountFansIncrease = endFans - baselineFans;

      // ===== 播放/点赞/评论/收藏口径修正 =====
      // 单日：当日累计 - 前一日累计
      // 区间：end 日累计 - start 日累计
      const [endSums, baseSums] = await Promise.all([
        this.getWorkSumsAtDate(workIds, endDateStr),
        this.getWorkSumsAtDate(workIds, startBaselineStr),
      ]);

      const accountViewsIncrease = endSums.views - baseSums.views;
      const accountLikesIncrease = endSums.likes - baseSums.likes;
      const accountCommentsIncrease = endSums.comments - baseSums.comments;
      const accountCollectsIncrease = endSums.collects - baseSums.collects;

      // 累加到平台聚合数据
      const platformKey = account.platform;
      if (!platformMap.has(platformKey)) {
        platformMap.set(platformKey, {
          fansCount: 0,
          fansIncrease: 0,
          viewsCount: 0,
          likesCount: 0,
          commentsCount: 0,
          collectsCount: 0,
          latestUpdateTime: null,
        });
      }

      const platformStat = platformMap.get(platformKey)!;
      platformStat.fansCount += endFans;
      platformStat.fansIncrease += accountFansIncrease;
      platformStat.viewsCount += accountViewsIncrease;
      platformStat.likesCount += accountLikesIncrease;
      platformStat.commentsCount += accountCommentsIncrease;
      platformStat.collectsCount += accountCollectsIncrease;

      // 查询该账号在当前时间段内的最晚 updated_at
      const latestUserStat = await this.userDayStatisticsRepository
        .createQueryBuilder('uds')
        .where('uds.account_id = :accountId', { accountId: account.id })
        .andWhere('DATE(uds.record_date) >= :startDate', { startDate: startDateStr })
        .andWhere('DATE(uds.record_date) <= :endDate', { endDate: endDateStr })
        .orderBy('uds.updated_at', 'DESC')
        .getOne();

      if (latestUserStat && latestUserStat.updatedAt) {
        // 更新平台的最晚更新时间
        if (!platformStat.latestUpdateTime || latestUserStat.updatedAt > platformStat.latestUpdateTime) {
          platformStat.latestUpdateTime = latestUserStat.updatedAt;
        }
      }
    }

    // 转换为数组格式，按粉丝数降序排序
    const platformData: PlatformStatItem[] = Array.from(platformMap.entries()).map(([platform, stat]) => {
      // 格式化更新时间为 "MM-DD HH:mm" 格式
      let updateTime: string | undefined;
      if (stat.latestUpdateTime) {
        const date = new Date(stat.latestUpdateTime);
        const month = String(date.getMonth() + 1).padStart(2, '0');
        const day = String(date.getDate()).padStart(2, '0');
        const hours = String(date.getHours()).padStart(2, '0');
        const minutes = String(date.getMinutes()).padStart(2, '0');
        updateTime = `${month}-${day} ${hours}:${minutes}`;
      }

      return {
        platform,
        fansCount: stat.fansCount,
        fansIncrease: stat.fansIncrease,
        viewsCount: stat.viewsCount,
        likesCount: stat.likesCount,
        commentsCount: stat.commentsCount,
        collectsCount: stat.collectsCount,
        updateTime,
      };
    });

    platformData.sort((a, b) => b.fansCount - a.fansCount);

    return platformData;
  }

  /**
   * 批量获取作品的历史统计数据
   */
  async getWorkStatisticsHistory(
    workIds: number[],
    options: {
      startDate?: string;
      endDate?: string;
    }
  ): Promise<Record<string, WorkStatisticsItem[]>> {
    const { startDate, endDate } = options;

    const queryBuilder = this.statisticsRepository
      .createQueryBuilder('wds')
      .select('wds.work_id', 'workId')
      .addSelect('wds.record_date', 'recordDate')
      .addSelect('wds.play_count', 'playCount')
      .addSelect('wds.like_count', 'likeCount')
      .addSelect('wds.comment_count', 'commentCount')
      .addSelect('wds.share_count', 'shareCount')
      .addSelect('wds.collect_count', 'collectCount')
      .where('wds.work_id IN (:...workIds)', { workIds })
      .orderBy('wds.work_id', 'ASC')
      .addOrderBy('wds.record_date', 'ASC');

    if (startDate) {
      queryBuilder.andWhere('wds.record_date >= :startDate', { startDate });
    }
    if (endDate) {
      queryBuilder.andWhere('wds.record_date <= :endDate', { endDate });
    }

    const results = await queryBuilder.getRawMany();

    // 按 workId 分组
    const groupedData: Record<string, WorkStatisticsItem[]> = {};

    for (const row of results) {
      const workId = String(row.workId);
      if (!groupedData[workId]) {
        groupedData[workId] = [];
      }

      const recordDate = row.recordDate instanceof Date
        ? row.recordDate.toISOString().split('T')[0]
        : String(row.recordDate).split('T')[0];

      groupedData[workId].push({
        recordDate,
        playCount: parseInt(row.playCount) || 0,
        likeCount: parseInt(row.likeCount) || 0,
        commentCount: parseInt(row.commentCount) || 0,
        shareCount: parseInt(row.shareCount) || 0,
        collectCount: parseInt(row.collectCount) || 0,
      });
    }

    return groupedData;
  }

  /**
   * 获取数据总览
   * 返回账号列表和汇总统计数据
   */
  async getOverview(userId: number): Promise<{
    accounts: Array<{
      id: number;
      nickname: string;
      username: string;
      avatarUrl: string | null;
      platform: string;
      groupId: number | null;
      groupName?: string | null;
      fansCount: number;
      totalIncome: number | null;
      yesterdayIncome: number | null;
      totalViews: number | null;
      yesterdayViews: number | null;
      yesterdayComments: number;
      yesterdayLikes: number;
      yesterdayFansIncrease: number;
      updateTime: string;
      status: string;
    }>;
    summary: {
      totalAccounts: number;
      totalIncome: number;
      yesterdayIncome: number;
      totalViews: number;
      yesterdayViews: number;
      totalFans: number;
      yesterdayComments: number;
      yesterdayLikes: number;
      yesterdayFansIncrease: number;
    };
  }> {
    // 只查询支持的平台：抖音、百家号、视频号、小红书
    const allowedPlatforms = ['douyin', 'baijiahao', 'weixin_video', 'xiaohongshu'];
    
    // 获取用户的所有账号（只包含支持的平台）
    const accounts = await this.accountRepository.find({
      where: {
        userId,
        platform: In(allowedPlatforms),
      },
      relations: ['group'],
    });

    // 使用中国时区（UTC+8）计算“今天/昨天”的业务日期
    // 思路：在当前 UTC 时间基础上 +8 小时，再取 ISO 日期部分，即为中国日历日期
    const now = new Date();
    const chinaNow = new Date(now.getTime() + 8 * 60 * 60 * 1000);
    const chinaYesterday = new Date(chinaNow.getTime() - 24 * 60 * 60 * 1000);

    // 格式化为 YYYY-MM-DD，与 MySQL DATE 字段匹配
    const todayStr = chinaNow.toISOString().split('T')[0];
    const yesterdayStr = chinaYesterday.toISOString().split('T')[0];
    
    logger.info(`[WorkDayStatistics] getOverview - userId: ${userId}, today: ${todayStr}, yesterday: ${yesterdayStr}`);

    const accountList: Array<{
      id: number;
      nickname: string;
      username: string;
      avatarUrl: string | null;
      platform: string;
      groupId: number | null;
      fansCount: number;
      totalIncome: number | null;
      yesterdayIncome: number | null;
      totalViews: number | null;
      yesterdayViews: number | null;
      yesterdayComments: number;
      yesterdayLikes: number;
      yesterdayFansIncrease: number;
      updateTime: string;
      status: string;
    }> = [];

    // 汇总统计数据
    let totalAccounts = 0;
    let totalIncome = 0;
    let yesterdayIncome = 0;
    let totalViews = 0;
    let yesterdayViews = 0;
    let totalFans = 0;
    let yesterdayComments = 0;
    let yesterdayLikes = 0;
    let yesterdayFansIncrease = 0;

    for (const account of accounts) {
      // 获取该账号的所有作品ID
      const works = await this.workRepository.find({
        where: { accountId: account.id },
        select: ['id'],
      });

      if (works.length === 0) {
        // 如果没有作品，只返回账号基本信息
        // 从 user_day_statistics 表获取账号的最新粉丝数
        // 使用日期字符串直接查询（更可靠，避免时区问题）
        const todayUserStat = await this.userDayStatisticsRepository
          .createQueryBuilder('uds')
          .where('uds.account_id = :accountId', { accountId: account.id })
          .andWhere('DATE(uds.record_date) = :today', { today: todayStr })
          .getOne();
        
        const yesterdayUserStat = await this.userDayStatisticsRepository
          .createQueryBuilder('uds')
          .where('uds.account_id = :accountId', { accountId: account.id })
          .andWhere('DATE(uds.record_date) = :yesterday', { yesterday: yesterdayStr })
          .getOne();
        
        // 获取今天的粉丝数：优先使用统计数据，如果没有则使用账号表的当前值
        let accountFansCount: number;
        if (todayUserStat) {
          accountFansCount = todayUserStat.fansCount || 0;
        } else {
          accountFansCount = account.fansCount || 0;
        }
        
        // 计算昨日涨粉（今天粉丝数 - 昨天粉丝数）
        let accountYesterdayFansIncrease: number;
        if (yesterdayUserStat) {
          // 昨天有数据，直接使用
          const yesterdayFans = yesterdayUserStat.fansCount || 0;
          accountYesterdayFansIncrease = Math.max(0, accountFansCount - yesterdayFans);
          logger.debug(`[WorkDayStatistics] Account ${account.id} (no works) - yesterday: ${yesterdayFans}, today: ${accountFansCount}, increase: ${accountYesterdayFansIncrease}`);
        } else {
          // 昨天没有数据，查找最近一天（早于今天）的数据作为基准
          const recentUserStat = await this.userDayStatisticsRepository
            .createQueryBuilder('uds')
            .where('uds.account_id = :accountId', { accountId: account.id })
            .andWhere('DATE(uds.record_date) < :today', { today: todayStr })
            .orderBy('uds.record_date', 'DESC')
            .getOne();
          
          if (recentUserStat) {
            const recentFans = recentUserStat.fansCount || 0;
            accountYesterdayFansIncrease = Math.max(0, accountFansCount - recentFans);
            logger.debug(`[WorkDayStatistics] Account ${account.id} (no works) - using recent: ${recentFans}, today: ${accountFansCount}, increase: ${accountYesterdayFansIncrease}`);
          } else {
            // 完全没有历史数据，涨粉为 0
            accountYesterdayFansIncrease = 0;
            logger.debug(`[WorkDayStatistics] Account ${account.id} (no works) - no history data, increase: 0`);
          }
        }
        
        accountList.push({
          id: account.id,
          nickname: account.accountName || '',
          username: account.accountId || '',
          avatarUrl: account.avatarUrl,
          platform: account.platform,
          groupId: account.groupId,
        groupName: account.group?.name ?? null,
          fansCount: accountFansCount,
          totalIncome: null,
          yesterdayIncome: null,
          totalViews: null,
          yesterdayViews: null,
          yesterdayComments: 0,
          yesterdayLikes: 0,
          yesterdayFansIncrease: accountYesterdayFansIncrease,
          updateTime: account.updatedAt.toISOString(),
          status: account.status,
        });
        
        // 即使没有作品，也要累加账号的粉丝数到总粉丝数
        totalAccounts++;
        totalFans += accountFansCount;
        yesterdayFansIncrease += accountYesterdayFansIncrease;
        continue;
      }

      const workIds = works.map(w => w.id);

      // 获取每个作品的最新日期统计数据（总播放量等，不再包含粉丝数）
      const latestStatsQuery = this.statisticsRepository
        .createQueryBuilder('wds')
        .select('wds.work_id', 'workId')
        .addSelect('MAX(wds.record_date)', 'latestDate')
        .addSelect('MAX(wds.play_count)', 'playCount')
        .addSelect('MAX(wds.like_count)', 'likeCount')
        .addSelect('MAX(wds.comment_count)', 'commentCount')
        .where('wds.work_id IN (:...workIds)', { workIds })
        .groupBy('wds.work_id');

      const latestStats = await latestStatsQuery.getRawMany();

      // 计算总播放量（所有作品最新日期的play_count总和）
      let accountTotalViews = 0;
      const latestDateMap = new Map<number, string>();
      for (const stat of latestStats) {
        accountTotalViews += parseInt(stat.playCount) || 0;
        latestDateMap.set(stat.workId, stat.latestDate);
      }

      // 获取昨天和今天的数据来计算增量（不再包含粉丝数，粉丝数从 user_day_statistics 表获取）
      const yesterdayStatsQuery = this.statisticsRepository
        .createQueryBuilder('wds')
        .select('wds.work_id', 'workId')
        .addSelect('SUM(wds.play_count)', 'playCount')
        .addSelect('SUM(wds.like_count)', 'likeCount')
        .addSelect('SUM(wds.comment_count)', 'commentCount')
        .where('wds.work_id IN (:...workIds)', { workIds })
        .andWhere('wds.record_date = :yesterday', { yesterday: yesterdayStr })
        .groupBy('wds.work_id');

      const todayStatsQuery = this.statisticsRepository
        .createQueryBuilder('wds')
        .select('wds.work_id', 'workId')
        .addSelect('SUM(wds.play_count)', 'playCount')
        .addSelect('SUM(wds.like_count)', 'likeCount')
        .addSelect('SUM(wds.comment_count)', 'commentCount')
        .where('wds.work_id IN (:...workIds)', { workIds })
        .andWhere('wds.record_date = :today', { today: todayStr })
        .groupBy('wds.work_id');

      const [yesterdayStats, todayStats] = await Promise.all([
        yesterdayStatsQuery.getRawMany(),
        todayStatsQuery.getRawMany(),
      ]);
      
      logger.info(`[WorkDayStatistics] Account ${account.id} (${account.accountName}) - workIds: ${workIds.length}, yesterdayStats: ${yesterdayStats.length}, todayStats: ${todayStats.length}`);
      
      if (yesterdayStats.length > 0 || todayStats.length > 0) {
        logger.debug(`[WorkDayStatistics] yesterdayStats:`, JSON.stringify(yesterdayStats.slice(0, 3)));
        logger.debug(`[WorkDayStatistics] todayStats:`, JSON.stringify(todayStats.slice(0, 3)));
      }

      // 计算昨日增量
      let accountYesterdayViews = 0;
      let accountYesterdayComments = 0;
      let accountYesterdayLikes = 0;
      let accountYesterdayFansIncrease = 0;

      // 按作品ID汇总（不再包含粉丝数）
      const yesterdayMap = new Map<number, { play: number; like: number; comment: number }>();
      const todayMap = new Map<number, { play: number; like: number; comment: number }>();

      for (const stat of yesterdayStats) {
        const workId = parseInt(String(stat.workId)) || 0;
        yesterdayMap.set(workId, {
          play: Number(stat.playCount) || 0,
          like: Number(stat.likeCount) || 0,
          comment: Number(stat.commentCount) || 0,
        });
      }

      for (const stat of todayStats) {
        const workId = parseInt(String(stat.workId)) || 0;
        todayMap.set(workId, {
          play: Number(stat.playCount) || 0,
          like: Number(stat.likeCount) || 0,
          comment: Number(stat.commentCount) || 0,
        });
      }
      
      logger.debug(`[WorkDayStatistics] Account ${account.id} - yesterdayMap size: ${yesterdayMap.size}, todayMap size: ${todayMap.size}`);

      // 计算增量（今天 - 昨天，不再包含粉丝数）
      for (const workId of workIds) {
        const todayData = todayMap.get(workId) || { play: 0, like: 0, comment: 0 };
        const yesterdayData = yesterdayMap.get(workId) || { play: 0, like: 0, comment: 0 };
        
        const viewsDiff = todayData.play - yesterdayData.play;
        const commentsDiff = todayData.comment - yesterdayData.comment;
        const likesDiff = todayData.like - yesterdayData.like;
        
        accountYesterdayViews += Math.max(0, viewsDiff);
        accountYesterdayComments += Math.max(0, commentsDiff);
        accountYesterdayLikes += Math.max(0, likesDiff);
      }
      
      logger.info(`[WorkDayStatistics] Account ${account.id} - Calculated: views=${accountYesterdayViews}, comments=${accountYesterdayComments}, likes=${accountYesterdayLikes}`);

      // 从 user_day_statistics 表获取账号的最新粉丝数和作品数
      // 优先使用统计数据，如果没有则使用账号表的当前值
      
      // 使用日期字符串直接查询（更可靠，避免时区问题）
      // todayStr 和 yesterdayStr 是中国时区的日期字符串（如 "2026-01-26"）
      // 直接使用字符串查询，TypeORM 会自动转换为 DATE 类型进行比较
      const todayUserStat = await this.userDayStatisticsRepository
        .createQueryBuilder('uds')
        .where('uds.account_id = :accountId', { accountId: account.id })
        .andWhere('DATE(uds.record_date) = :today', { today: todayStr })
        .getOne();
      
      const yesterdayUserStat = await this.userDayStatisticsRepository
        .createQueryBuilder('uds')
        .where('uds.account_id = :accountId', { accountId: account.id })
        .andWhere('DATE(uds.record_date) = :yesterday', { yesterday: yesterdayStr })
        .getOne();
      
      // 获取今天的粉丝数：优先使用统计数据，如果没有则使用账号表的当前值
      let accountFansCount: number;
      let accountWorksCount: number;
      if (todayUserStat) {
        accountFansCount = todayUserStat.fansCount || 0;
        accountWorksCount = todayUserStat.worksCount || 0;
      } else {
        // 今天没有统计数据，使用账号表的当前值
        accountFansCount = account.fansCount || 0;
        accountWorksCount = account.worksCount || 0;
      }
      
      // 计算昨日涨粉（今天粉丝数 - 昨天粉丝数）
      let yesterdayFans: number;
      if (yesterdayUserStat) {
        // 昨天有数据，直接使用
        yesterdayFans = yesterdayUserStat.fansCount || 0;
        logger.debug(`[WorkDayStatistics] Account ${account.id} - yesterday has data: ${yesterdayFans}, today: ${accountFansCount}`);
      } else {
        // 昨天没有数据，查找最近一天（早于今天）的数据作为基准
        const recentUserStat = await this.userDayStatisticsRepository
          .createQueryBuilder('uds')
          .where('uds.account_id = :accountId', { accountId: account.id })
          .andWhere('DATE(uds.record_date) < :today', { today: todayStr })
          .orderBy('uds.record_date', 'DESC')
          .getOne();
        
        if (recentUserStat) {
          yesterdayFans = recentUserStat.fansCount || 0;
          logger.debug(`[WorkDayStatistics] Account ${account.id} - using recent data: ${yesterdayFans}, today: ${accountFansCount}`);
        } else {
          // 完全没有历史数据，用账号表的当前粉丝数作为基准（涨粉为 0）
          yesterdayFans = accountFansCount;
          logger.debug(`[WorkDayStatistics] Account ${account.id} - no history data, using current: ${accountFansCount}`);
        }
      }
      
      // 计算涨粉数（今天 - 昨天），确保不为负数
      accountYesterdayFansIncrease = Math.max(0, accountFansCount - yesterdayFans);
      logger.info(`[WorkDayStatistics] Account ${account.id} - fans increase: ${accountFansCount} - ${yesterdayFans} = ${accountYesterdayFansIncrease}`);

      accountList.push({
        id: account.id,
        nickname: account.accountName || '',
        username: account.accountId || '',
        avatarUrl: account.avatarUrl,
        platform: account.platform,
        groupId: account.groupId,
        groupName: account.group?.name ?? null,
        fansCount: accountFansCount,
        totalIncome: null, // 收益数据需要从其他表获取，暂时为null
        yesterdayIncome: null,
        totalViews: accountTotalViews > 0 ? accountTotalViews : null,
        yesterdayViews: accountYesterdayViews > 0 ? accountYesterdayViews : null,
        yesterdayComments: accountYesterdayComments,
        yesterdayLikes: accountYesterdayLikes,
        yesterdayFansIncrease: accountYesterdayFansIncrease,
        updateTime: account.updatedAt.toISOString(),
        status: account.status,
      });

      // 累加汇总数据
      totalAccounts++;
      totalViews += accountTotalViews;
      yesterdayViews += accountYesterdayViews;
      totalFans += accountFansCount;
      yesterdayComments += accountYesterdayComments;
      yesterdayLikes += accountYesterdayLikes;
      yesterdayFansIncrease += accountYesterdayFansIncrease;
    }

    return {
      accounts: accountList,
      summary: {
        totalAccounts,
        totalIncome,
        yesterdayIncome,
        totalViews,
        yesterdayViews,
        totalFans,
        yesterdayComments,
        yesterdayLikes,
        yesterdayFansIncrease,
      },
    };
  }

  /**
   * 获取平台详情数据
   * 包括汇总统计、每日汇总数据和账号列表
   */
  async getPlatformDetail(
    userId: number,
    platform: string,
    options: {
      startDate: string;
      endDate: string;
    }
  ): Promise<{
    summary: {
      totalAccounts: number;
      totalIncome: number;
      viewsCount: number;
      commentsCount: number;
      likesCount: number;
      fansIncrease: number;
      recommendationCount: number | null; // 推荐量（部分平台支持）
    };
    dailyData: Array<{
      date: string;
      income: number;
      recommendationCount: number | null;
      viewsCount: number;
      commentsCount: number;
      likesCount: number;
      fansIncrease: number;
    }>;
    accounts: Array<{
      id: number;
      nickname: string;
      username: string;
      avatarUrl: string | null;
      platform: string;
      income: number | null;
      recommendationCount: number | null;
      viewsCount: number | null;
      commentsCount: number;
      likesCount: number;
      fansIncrease: number;
      updateTime: string;
    }>;
  }> {
    const { startDate, endDate } = options;
    const startDateStr = startDate;
    const endDateStr = endDate;

    // 获取该平台的所有账号
    const accounts = await this.accountRepository.find({
      where: {
        userId,
        platform: platform as any,
      },
      relations: ['group'],
    });

    if (accounts.length === 0) {
      return {
        summary: {
          totalAccounts: 0,
          totalIncome: 0,
          viewsCount: 0,
          commentsCount: 0,
          likesCount: 0,
          fansIncrease: 0,
          recommendationCount: null,
        },
        dailyData: [],
        accounts: [],
      };
    }

    // 计算汇总统计
    let totalAccounts = 0;
    let totalViews = 0;
    let totalComments = 0;
    let totalLikes = 0;
    let totalFansIncrease = 0;

    // 按日期汇总数据
    const dailyMap = new Map<string, {
      views: number;
      comments: number;
      likes: number;
      fansIncrease: number;
    }>();

    // 账号详细列表
    const accountList: Array<{
      id: number;
      nickname: string;
      username: string;
      avatarUrl: string | null;
      platform: string;
      income: number | null;
      recommendationCount: number | null;
      viewsCount: number | null;
      commentsCount: number;
      likesCount: number;
      fansIncrease: number;
      updateTime: string;
    }> = [];

    for (const account of accounts) {
      const works = await this.workRepository.find({
        where: { accountId: account.id },
        select: ['id'],
      });
      const workIds = works.map(w => w.id);

      // 获取该账号在日期范围内的每日数据
      const dateStart = new Date(startDateStr);
      const dateEnd = new Date(endDateStr);
      const currentDate = new Date(dateStart);

      while (currentDate <= dateEnd) {
        const dateStr = this.formatDate(currentDate);
        
        // 获取该日期的数据
        const [daySums, prevDaySums] = await Promise.all([
          this.getWorkSumsAtDate(workIds, dateStr),
          this.getWorkSumsAtDate(workIds, this.formatDate(new Date(currentDate.getTime() - 24 * 60 * 60 * 1000))),
        ]);

        const dayViews = daySums.views - prevDaySums.views;
        const dayComments = daySums.comments - prevDaySums.comments;
        const dayLikes = daySums.likes - prevDaySums.likes;

        // 获取粉丝数据
        const dayUserStat = await this.userDayStatisticsRepository
          .createQueryBuilder('uds')
          .where('uds.account_id = :accountId', { accountId: account.id })
          .andWhere('DATE(uds.record_date) = :d', { d: dateStr })
          .getOne();

        const prevDayUserStat = await this.userDayStatisticsRepository
          .createQueryBuilder('uds')
          .where('uds.account_id = :accountId', { accountId: account.id })
          .andWhere('DATE(uds.record_date) = :d', { d: this.formatDate(new Date(currentDate.getTime() - 24 * 60 * 60 * 1000)) })
          .getOne();

        const dayFans = (dayUserStat?.fansCount || 0) - (prevDayUserStat?.fansCount || 0);

        if (!dailyMap.has(dateStr)) {
          dailyMap.set(dateStr, {
            views: 0,
            comments: 0,
            likes: 0,
            fansIncrease: 0,
          });
        }

        const daily = dailyMap.get(dateStr)!;
        daily.views += Math.max(0, dayViews);
        daily.comments += Math.max(0, dayComments);
        daily.likes += Math.max(0, dayLikes);
        daily.fansIncrease += Math.max(0, dayFans);

        currentDate.setDate(currentDate.getDate() + 1);
      }

      // 计算账号的总数据（使用 endDate 的数据）
      const [endSums, startSums] = await Promise.all([
        this.getWorkSumsAtDate(workIds, endDateStr),
        this.getWorkSumsAtDate(workIds, startDateStr),
      ]);

      const accountViews = endSums.views - startSums.views;
      const accountComments = endSums.comments - startSums.comments;
      const accountLikes = endSums.likes - startSums.likes;

      // 获取粉丝数据
      const endUserStat = await this.userDayStatisticsRepository
        .createQueryBuilder('uds')
        .where('uds.account_id = :accountId', { accountId: account.id })
        .andWhere('DATE(uds.record_date) <= :d', { d: endDateStr })
        .orderBy('uds.record_date', 'DESC')
        .getOne();

      const startUserStat = await this.userDayStatisticsRepository
        .createQueryBuilder('uds')
        .where('uds.account_id = :accountId', { accountId: account.id })
        .andWhere('DATE(uds.record_date) <= :d', { d: startDateStr })
        .orderBy('uds.record_date', 'DESC')
        .getOne();

      const accountFansIncrease = (endUserStat?.fansCount || 0) - (startUserStat?.fansCount || 0);

      // 获取更新时间
      const latestUserStat = await this.userDayStatisticsRepository
        .createQueryBuilder('uds')
        .where('uds.account_id = :accountId', { accountId: account.id })
        .andWhere('DATE(uds.record_date) >= :startDate', { startDate: startDateStr })
        .andWhere('DATE(uds.record_date) <= :endDate', { endDate: endDateStr })
        .orderBy('uds.updated_at', 'DESC')
        .getOne();

      const updateTime = latestUserStat?.updatedAt 
        ? this.formatUpdateTime(latestUserStat.updatedAt)
        : '';

      accountList.push({
        id: account.id,
        nickname: account.accountName || '',
        username: account.accountId || '',
        avatarUrl: account.avatarUrl,
        platform: account.platform,
        income: null, // 收益数据需要从其他表获取
        recommendationCount: null, // 推荐量（部分平台支持）
        viewsCount: accountViews > 0 ? accountViews : null,
        commentsCount: Math.max(0, accountComments),
        likesCount: Math.max(0, accountLikes),
        fansIncrease: Math.max(0, accountFansIncrease),
        updateTime,
      });

      totalAccounts++;
      totalViews += Math.max(0, accountViews);
      totalComments += Math.max(0, accountComments);
      totalLikes += Math.max(0, accountLikes);
      totalFansIncrease += Math.max(0, accountFansIncrease);
    }

    // 转换为每日数据数组
    const dailyData = Array.from(dailyMap.entries())
      .map(([date, data]) => ({
        date,
        income: 0, // 收益数据需要从其他表获取
        recommendationCount: null, // 推荐量（部分平台支持）
        viewsCount: data.views,
        commentsCount: data.comments,
        likesCount: data.likes,
        fansIncrease: data.fansIncrease,
      }))
      .sort((a, b) => a.date.localeCompare(b.date));

    return {
      summary: {
        totalAccounts,
        totalIncome: 0, // 收益数据需要从其他表获取
        viewsCount: totalViews,
        commentsCount: totalComments,
        likesCount: totalLikes,
        fansIncrease: totalFansIncrease,
        recommendationCount: null, // 推荐量（部分平台支持）
      },
      dailyData,
      accounts: accountList,
    };
  }

  /**
   * 格式化更新时间为 "MM-DD HH:mm" 格式
   */
  private formatUpdateTime(date: Date): string {
    const month = String(date.getMonth() + 1).padStart(2, '0');
    const day = String(date.getDate()).padStart(2, '0');
    const hours = String(date.getHours()).padStart(2, '0');
    const minutes = String(date.getMinutes()).padStart(2, '0');
    return `${month}-${day} ${hours}:${minutes}`;
  }
}