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

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

interface StatisticsItem {
  workId: number;
  playCount?: number;
  exposureCount?: number;
  likeCount?: number;
  recommendCount?: number;
  commentCount?: number;
  shareCount?: number;
  collectCount?: number;
  fansIncrease?: number;
  followCount?: number;
  coverClickRate?: string;
  avgWatchDuration?: string;
  totalWatchDuration?: string;
  completionRate?: string;
  twoSecondExitRate?: string;
}

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

// 单个平台的趋势数据
interface PlatformTrendItem {
  platform: string;       // 平台标识
  platformName: string;   // 平台中文名
  fansIncrease: number[]; // 涨粉数
  views: number[];        // 播放数
  likes: number[];        // 点赞数
  comments: number[];     // 评论数
}

// 趋势数据（按平台分组）
interface TrendData {
  dates: string[];                  // 日期数组
  platforms: PlatformTrendItem[];   // 各平台数据
}

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

interface WorkStatisticsItem {
  recordDate: string;
  playCount: number;
  exposureCount?: number;
  likeCount: number;
  commentCount: number;
  shareCount: number;
  collectCount: number;
  fansIncrease?: number;
  followCount?: number; // 视频号：关注数
  totalWatchDuration?: string;
  avgWatchDuration?: string;
  coverClickRate?: string;
  completionRate?: string;
  twoSecondExitRate?: string;
}

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}`;
  }

  /**
   * 按作品 ID 删除该作品的所有每日统计（work 被删除时调用，或用于清理孤儿数据）
   * @returns 被删除的行数
   */
  async deleteByWorkId(workId: number): Promise<number> {
    const result = await this.statisticsRepository.delete({ workId });
    return result.affected ?? 0;
  }

  /**
   * 获取某个账号在指定日期（<= 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 = this.getTodayInChina();

    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) {
        // 更新已有记录
        await this.statisticsRepository.update(existing.id, {
          playCount: stat.playCount ?? existing.playCount,
          exposureCount: stat.exposureCount ?? existing.exposureCount,
          likeCount: stat.likeCount ?? existing.likeCount,
          recommendCount: stat.recommendCount ?? existing.recommendCount,
          commentCount: stat.commentCount ?? existing.commentCount,
          shareCount: stat.shareCount ?? existing.shareCount,
          collectCount: stat.collectCount ?? existing.collectCount,
          fansIncrease: stat.fansIncrease ?? existing.fansIncrease,
          followCount: stat.followCount ?? existing.followCount,
          coverClickRate: stat.coverClickRate ?? existing.coverClickRate ?? '0',
          avgWatchDuration: stat.avgWatchDuration ?? existing.avgWatchDuration ?? '0',
          totalWatchDuration: stat.totalWatchDuration ?? existing.totalWatchDuration ?? '0',
          completionRate: stat.completionRate ?? existing.completionRate ?? '0',
          twoSecondExitRate: stat.twoSecondExitRate ?? existing.twoSecondExitRate ?? '0',
        });
        updatedCount++;
      } else {
        // 插入新记录
        const newStat = this.statisticsRepository.create({
          workId: stat.workId,
          recordDate: today,
          playCount: stat.playCount ?? 0,
          exposureCount: stat.exposureCount ?? 0,
          likeCount: stat.likeCount ?? 0,
          recommendCount: stat.recommendCount ?? 0,
          commentCount: stat.commentCount ?? 0,
          shareCount: stat.shareCount ?? 0,
          collectCount: stat.collectCount ?? 0,
          fansIncrease: stat.fansIncrease ?? 0,
          followCount: stat.followCount ?? 0,
          coverClickRate: stat.coverClickRate ?? '0',
          avgWatchDuration: stat.avgWatchDuration ?? '0',
          totalWatchDuration: stat.totalWatchDuration ?? '0',
          completionRate: stat.completionRate ?? '0',
          twoSecondExitRate: stat.twoSecondExitRate ?? '0',
        });
        await this.statisticsRepository.save(newStat);
        insertedCount++;
      }
    }

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

  /**
   * 获取「中国时区（Asia/Shanghai）当前日历日」的 Date
   * 用于存储 record_date，避免服务器时区与业务日期不一致
   */
  private getTodayInChina(): Date {
    const formatter = new Intl.DateTimeFormat('en-CA', {
      timeZone: 'Asia/Shanghai',
      year: 'numeric',
      month: '2-digit',
      day: '2-digit',
    });
    const parts = formatter.formatToParts(new Date());
    const get = (type: string) => parts.find((p) => p.type === type)?.value ?? '0';
    const y = parseInt(get('year'), 10);
    const m = parseInt(get('month'), 10) - 1;
    const d = parseInt(get('day'), 10);
    return new Date(Date.UTC(y, m, d, 0, 0, 0, 0));
  }

  /**
   * 保存指定日期的作品日统计数据（按 workId + recordDate 维度 upsert）
   * 说明：recordDate 会被归零到当天 00:00:00（本地时间），避免主键冲突
   */
  async saveStatisticsForDate(
    workId: number,
    recordDate: Date,
    patch: Omit<StatisticsItem, 'workId'>
  ): Promise<SaveResult> {
    const d = new Date(recordDate);
    d.setHours(0, 0, 0, 0);

    const existing = await this.statisticsRepository.findOne({
      where: { workId, recordDate: d },
    });

    if (existing) {
      await this.statisticsRepository.update(existing.id, {
        playCount: patch.playCount ?? existing.playCount,
        exposureCount: patch.exposureCount ?? existing.exposureCount,
        likeCount: patch.likeCount ?? existing.likeCount,
        recommendCount: patch.recommendCount ?? existing.recommendCount,
        commentCount: patch.commentCount ?? existing.commentCount,
        shareCount: patch.shareCount ?? existing.shareCount,
        collectCount: patch.collectCount ?? existing.collectCount,
        fansIncrease: patch.fansIncrease ?? existing.fansIncrease,
        followCount: patch.followCount ?? existing.followCount,
        coverClickRate: patch.coverClickRate ?? existing.coverClickRate ?? '0',
        avgWatchDuration: patch.avgWatchDuration ?? existing.avgWatchDuration ?? '0',
        totalWatchDuration: patch.totalWatchDuration ?? existing.totalWatchDuration ?? '0',
        completionRate: patch.completionRate ?? existing.completionRate ?? '0',
        twoSecondExitRate: patch.twoSecondExitRate ?? existing.twoSecondExitRate ?? '0',
      });
      return { inserted: 0, updated: 1 };
    }

    const newStat = this.statisticsRepository.create({
      workId,
      recordDate: d,
      playCount: patch.playCount ?? 0,
      exposureCount: patch.exposureCount ?? 0,
      likeCount: patch.likeCount ?? 0,
      recommendCount: patch.recommendCount ?? 0,
      commentCount: patch.commentCount ?? 0,
      shareCount: patch.shareCount ?? 0,
      collectCount: patch.collectCount ?? 0,
      fansIncrease: patch.fansIncrease ?? 0,
      followCount: patch.followCount ?? 0,
      coverClickRate: patch.coverClickRate ?? '0',
      avgWatchDuration: patch.avgWatchDuration ?? '0',
      totalWatchDuration: patch.totalWatchDuration ?? '0',
      completionRate: patch.completionRate ?? '0',
      twoSecondExitRate: patch.twoSecondExitRate ?? '0',
    });

    await this.statisticsRepository.save(newStat);
    return { inserted: 1, updated: 0 };
  }

  /**
   * 批量保存指定日期范围的作品日统计数据（每条记录自带日期）
   */
  async saveStatisticsForDateBatch(
    items: Array<{ workId: number; recordDate: Date } & Omit<StatisticsItem, 'workId'>>
  ): Promise<SaveResult> {
    let insertedCount = 0;
    let updatedCount = 0;

    for (const it of items) {
      const { workId, recordDate, ...patch } = it;
      const result = await this.saveStatisticsForDate(workId, recordDate, patch);
      insertedCount += result.inserted;
      updatedCount += result.updated;
    }

    logger.info(
      `[WorkDayStatistics] Date-batch save completed: inserted=${insertedCount}, updated=${updatedCount}`
    );
    return { inserted: insertedCount, updated: updatedCount };
  }

  // 平台名称映射
  private platformNameMap: Record<string, string> = {
    xiaohongshu: '小红书',
    douyin: '抖音',
    kuaishou: '快手',
    weixin: '视频号',
    weixin_video: '视频号',
    shipinhao: '视频号',
    baijiahao: '百家号',
  };

  /**
   * 获取数据趋势
   * 从 user_day_statistics 表获取近30天的数据
   * 按平台分组返回，每个平台下所有账号的总和为一条曲线
   */
  async getTrend(
    userId: number,
    options: {
      days?: number;
      startDate?: string;
      endDate?: string;
      accountId?: number;
    }
  ): Promise<TrendData> {
    const { days = 30, 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);
    }

    // 获取该用户所有的账号（包含平台信息）
    const userAccounts = await this.accountRepository.find({
      where: { userId },
      select: ['id', 'platform'],
    });

    if (userAccounts.length === 0) {
      // 用户没有账号，返回空数据
      return this.generateEmptyTrendData(dateStart, dateEnd);
    }

    // 按平台分组账号
    const platformAccountsMap = new Map<string, number[]>();
    for (const account of userAccounts) {
      const platform = account.platform;
      if (!platformAccountsMap.has(platform)) {
        platformAccountsMap.set(platform, []);
      }
      platformAccountsMap.get(platform)!.push(account.id);
    }

    // 如果指定了特定账号，只查询该账号所属平台
    if (accountId) {
      const targetAccount = userAccounts.find(a => a.id === accountId);
      if (targetAccount) {
        platformAccountsMap.clear();
        platformAccountsMap.set(targetAccount.platform, [accountId]);
      }
    }

    // 生成完整的日期数组
    const dates: string[] = [];
    const dateKeys: string[] = [];
    const d = new Date(dateStart);
    while (d <= dateEnd) {
      const dateKey = this.formatDate(d);
      const displayDate = `${String(d.getMonth() + 1).padStart(2, '0')}-${String(d.getDate()).padStart(2, '0')}`;
      dates.push(displayDate);
      dateKeys.push(dateKey);
      d.setDate(d.getDate() + 1);
    }

    // 获取用户拥有的所有平台
    const userPlatforms = Array.from(platformAccountsMap.keys());

    // 查询数据：按平台和日期分组
    const allAccountIds = userAccounts.map(a => a.id);
    const results = await this.userDayStatisticsRepository
      .createQueryBuilder('uds')
      .innerJoin(PlatformAccount, 'pa', 'uds.account_id = pa.id')
      .select('pa.platform', 'platform')
      .addSelect('uds.record_date', 'recordDate')
      .addSelect('SUM(uds.fans_increase)', 'totalFansIncrease')
      .addSelect('SUM(uds.play_count)', 'totalViews')
      .addSelect('SUM(uds.like_count)', 'totalLikes')
      .addSelect('SUM(uds.comment_count)', 'totalComments')
      .where('uds.account_id IN (:...accountIds)', { accountIds: allAccountIds })
      .andWhere('uds.record_date >= :dateStart', { dateStart })
      .andWhere('uds.record_date <= :dateEnd', { dateEnd })
      .groupBy('pa.platform')
      .addGroupBy('uds.record_date')
      .orderBy('uds.record_date', 'ASC')
      .getRawMany();

    // 构建 平台 -> 日期 -> 数据 的映射
    const platformDateMap = new Map<string, Map<string, {
      fansIncrease: number;
      views: number;
      likes: number;
      comments: number;
    }>>();

    for (const row of results) {
      const platform = row.platform;
      const dateKey = row.recordDate instanceof Date 
        ? row.recordDate.toISOString().split('T')[0]
        : String(row.recordDate).split('T')[0];

      if (!platformDateMap.has(platform)) {
        platformDateMap.set(platform, new Map());
      }

      platformDateMap.get(platform)!.set(dateKey, {
        fansIncrease: parseInt(row.totalFansIncrease) || 0,
        views: parseInt(row.totalViews) || 0,
        likes: parseInt(row.totalLikes) || 0,
        comments: parseInt(row.totalComments) || 0,
      });
    }

    // 构建各平台的数据数组
    const platforms: PlatformTrendItem[] = [];

    for (const platform of userPlatforms) {
      const dateDataMap = platformDateMap.get(platform) || new Map();
      
      const fansIncrease: number[] = [];
      const views: number[] = [];
      const likes: number[] = [];
      const comments: number[] = [];

      for (const dateKey of dateKeys) {
        const data = dateDataMap.get(dateKey);
        if (data) {
          fansIncrease.push(data.fansIncrease);
          views.push(data.views);
          likes.push(data.likes);
          comments.push(data.comments);
        } else {
          fansIncrease.push(0);
          views.push(0);
          likes.push(0);
          comments.push(0);
        }
      }

      platforms.push({
        platform,
        platformName: this.platformNameMap[platform] || platform,
        fansIncrease,
        views,
        likes,
        comments,
      });
    }

    return { dates, platforms };
  }

  /**
   * 生成空的趋势数据
   */
  private generateEmptyTrendData(dateStart: Date, dateEnd: Date): TrendData {
    const dates: string[] = [];

    const d = new Date(dateStart);
    while (d <= dateEnd) {
      dates.push(`${String(d.getMonth() + 1).padStart(2, '0')}-${String(d.getDate()).padStart(2, '0')}`);
      d.setDate(d.getDate() + 1);
    }

    return { dates, platforms: [] };
  }

  /**
   * 按平台分组获取统计数据
   */
  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);

    /**
     * 口径变更：user_day_statistics 的 play/comment/like/collect/fans_increase 等字段为“每日单独值”
     * 因此：
     * - 区间统计：直接按日期范围 SUM
     * - 单日统计：startDate=endDate 时，也同样按该日 SUM（无需再做“累计差”）
     * 粉丝数：使用 platform_accounts.fans_count（当前值）
     */
    const [fansRows, udsRows] = await Promise.all([
      this.accountRepository
        .createQueryBuilder('pa')
        .select('pa.platform', 'platform')
        .addSelect('COALESCE(SUM(pa.fansCount), 0)', 'fansCount')
        .where('pa.userId = :userId', { userId })
        .groupBy('pa.platform')
        .getRawMany(),
      this.userDayStatisticsRepository
        .createQueryBuilder('uds')
        .innerJoin(PlatformAccount, 'pa', 'pa.id = uds.account_id')
        .select('pa.platform', 'platform')
        .addSelect('COALESCE(SUM(uds.play_count), 0)', 'viewsCount')
        .addSelect('COALESCE(SUM(uds.comment_count), 0)', 'commentsCount')
        .addSelect('COALESCE(SUM(uds.like_count), 0)', 'likesCount')
        .addSelect('COALESCE(SUM(uds.collect_count), 0)', 'collectsCount')
        .addSelect('COALESCE(SUM(uds.fans_increase), 0)', 'fansIncrease')
        .addSelect('MAX(uds.updated_at)', 'latestUpdateTime')
        .where('pa.user_id = :userId', { userId })
        .andWhere('DATE(uds.record_date) >= :startDate', { startDate: startDateStr })
        .andWhere('DATE(uds.record_date) <= :endDate', { endDate: endDateStr })
        .groupBy('pa.platform')
        .getRawMany(),
    ]);

    const fansMap = new Map<string, number>();
    for (const row of fansRows || []) {
      const platform = String(row.platform || '');
      if (!platform) continue;
      fansMap.set(platform, Number(row.fansCount) || 0);
    }

    const statMap = new Map<string, {
      viewsCount: number;
      commentsCount: number;
      likesCount: number;
      collectsCount: number;
      fansIncrease: number;
      latestUpdateTime?: string | Date | null;
    }>();
    for (const row of udsRows || []) {
      const platform = String(row.platform || '');
      if (!platform) continue;
      statMap.set(platform, {
        viewsCount: Number(row.viewsCount) || 0,
        commentsCount: Number(row.commentsCount) || 0,
        likesCount: Number(row.likesCount) || 0,
        collectsCount: Number(row.collectsCount) || 0,
        fansIncrease: Number(row.fansIncrease) || 0,
        latestUpdateTime: row.latestUpdateTime ?? null,
      });
    }

    const platforms = new Set<string>([...fansMap.keys(), ...statMap.keys()]);

    const platformData: PlatformStatItem[] = Array.from(platforms).map((platform) => {
      const stat = statMap.get(platform);
      const fansCount = fansMap.get(platform) ?? 0;
      const latestUpdate = stat?.latestUpdateTime ? new Date(stat.latestUpdateTime as any) : null;

      return {
        platform,
        fansCount,
        fansIncrease: stat?.fansIncrease ?? 0,
        viewsCount: stat?.viewsCount ?? 0,
        likesCount: stat?.likesCount ?? 0,
        commentsCount: stat?.commentsCount ?? 0,
        collectsCount: stat?.collectsCount ?? 0,
        updateTime: latestUpdate ? latestUpdate.toISOString() : undefined,
      };
    });

    platformData.sort((a, b) => (b.fansCount || 0) - (a.fansCount || 0));
    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.exposure_count', 'exposureCount')
      .addSelect('wds.like_count', 'likeCount')
      .addSelect('wds.comment_count', 'commentCount')
      .addSelect('wds.share_count', 'shareCount')
      .addSelect('wds.collect_count', 'collectCount')
      .addSelect('wds.fans_increase', 'fansIncrease')
      .addSelect('wds.follow_count', 'followCount')
      .addSelect('wds.total_watch_duration', 'totalWatchDuration')
      .addSelect('wds.avg_watch_duration', 'avgWatchDuration')
      .addSelect('wds.cover_click_rate', 'coverClickRate')
      .addSelect('wds.completion_rate', 'completionRate')
      .addSelect('wds.two_second_exit_rate', 'twoSecondExitRate')
      .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,
        exposureCount: parseInt(row.exposureCount) || 0,
        likeCount: parseInt(row.likeCount) || 0,
        commentCount: parseInt(row.commentCount) || 0,
        shareCount: parseInt(row.shareCount) || 0,
        collectCount: parseInt(row.collectCount) || 0,
        fansIncrease: parseInt(row.fansIncrease) || 0,
        followCount: parseInt(row.followCount) || 0,
        totalWatchDuration: row.totalWatchDuration || '0',
        avgWatchDuration: row.avgWatchDuration || '0',
        coverClickRate: row.coverClickRate || '0',
        completionRate: row.completionRate || '0',
        twoSecondExitRate: row.twoSecondExitRate || '0',
      });
    }

    return groupedData;
  }

  /**
   * 获取每个账号在指定日期（<= targetDate）时，user_day_statistics 的“最新一条”
   * 主要用于：总粉丝、更新时间等需要“最新状态”的字段。
   */
  private async getLatestUserDayStatsAtDate(
    accountIds: number[],
    targetDate: string
  ): Promise<Map<number, { fansCount: number; updatedAt: Date | null; recordDate: Date | null }>> {
    const map = new Map<number, { fansCount: number; updatedAt: Date | null; recordDate: Date | null }>();
    if (!accountIds.length) return map;

    // MySQL: 派生表先取每个账号 <= targetDate 的最新日期，再回连取该日数据
    const placeholders = accountIds.map(() => '?').join(',');
    const sql = `
      SELECT
        uds.account_id AS accountId,
        uds.fans_count AS fansCount,
        uds.record_date AS recordDate,
        uds.updated_at AS updatedAt
      FROM user_day_statistics uds
      INNER JOIN (
        SELECT account_id, MAX(record_date) AS record_date
        FROM user_day_statistics
        WHERE account_id IN (${placeholders})
          AND record_date <= ?
        GROUP BY account_id
      ) latest
      ON latest.account_id = uds.account_id AND latest.record_date = uds.record_date
    `;

    const rows: any[] = await AppDataSource.query(sql, [...accountIds, targetDate]);
    for (const row of rows || []) {
      const accountId = Number(row.accountId) || 0;
      if (!accountId) continue;
      const fansCount = Number(row.fansCount) || 0;
      const recordDate = row.recordDate ? new Date(row.recordDate) : null;
      const updatedAt = row.updatedAt ? new Date(row.updatedAt) : null;
      map.set(accountId, { fansCount, updatedAt, recordDate });
    }

    return map;
  }

  /**
   * 获取指定日期（= dateStr）每个账号在 user_day_statistics 的数据（用于“昨日”口径的一一对应）。
   */
  private async getUserDayStatsByExactDate(
    accountIds: number[],
    dateStr: string
  ): Promise<Map<number, { playCount: number; commentCount: number; likeCount: number; fansIncrease: number; updatedAt: Date | null }>> {
    const map = new Map<number, { playCount: number; commentCount: number; likeCount: number; fansIncrease: number; updatedAt: Date | null }>();
    if (!accountIds.length) return map;

    const rows = await this.userDayStatisticsRepository
      .createQueryBuilder('uds')
      .select('uds.account_id', 'accountId')
      .addSelect('uds.play_count', 'playCount')
      .addSelect('uds.comment_count', 'commentCount')
      .addSelect('uds.like_count', 'likeCount')
      .addSelect('uds.fans_increase', 'fansIncrease')
      .addSelect('uds.updated_at', 'updatedAt')
      .where('uds.account_id IN (:...accountIds)', { accountIds })
      .andWhere('DATE(uds.record_date) = :d', { d: dateStr })
      .getRawMany();

    for (const row of rows || []) {
      const accountId = Number(row.accountId) || 0;
      if (!accountId) continue;
      map.set(accountId, {
        playCount: Number(row.playCount) || 0,
        commentCount: Number(row.commentCount) || 0,
        likeCount: Number(row.likeCount) || 0,
        fansIncrease: Number(row.fansIncrease) || 0,
        updatedAt: row.updatedAt ? new Date(row.updatedAt) : null,
      });
    }

    return map;
  }

  /**
   * 获取数据总览
   * 返回账号列表和汇总统计数据
   */
  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;
      worksCount: number | null;
      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;
      totalWorks: 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 accountIds = accounts.map(a => a.id);

    // 账号总数：platform_accounts 中 user_id 对应数量（等价于 accounts.length）
    const totalAccounts = accounts.length;

    // 列表“总播放/汇总总播放”：统一从 works.play_count 聚合（累计）
    const worksPlayRows = accountIds.length
      ? await this.workRepository
          .createQueryBuilder('w')
          .select('w.accountId', 'accountId')
          .addSelect('COALESCE(SUM(w.playCount), 0)', 'playCount')
          .where('w.userId = :userId', { userId })
          .andWhere('w.accountId IN (:...accountIds)', { accountIds })
          .groupBy('w.accountId')
          .getRawMany()
      : [];
    const totalPlayMap = new Map<number, number>();
    for (const row of worksPlayRows || []) {
      totalPlayMap.set(Number(row.accountId) || 0, Number(row.playCount) || 0);
    }

    // “昨日”口径：只取 user_day_statistics 指定日期那一行，一一对应
    const yesterdayUdsMap = await this.getUserDayStatsByExactDate(accountIds, yesterdayStr);

    // 粉丝数口径：直接取 platform_accounts.fans_count（不跟随 user_day_statistics）

    const accountList: 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;
    }> = [];

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

    for (const account of accounts) {
      const accountTotalViews = totalPlayMap.get(account.id) ?? 0;
      const yesterdayUds = yesterdayUdsMap.get(account.id);

      const accountFansCount = account.fansCount || 0;
      const accountWorksCount = account.worksCount || 0;
      const accountYesterdayViews = yesterdayUds?.playCount ?? 0;
      const accountYesterdayComments = yesterdayUds?.commentCount ?? 0;
      const accountYesterdayLikes = yesterdayUds?.likeCount ?? 0;
      const accountYesterdayFansIncrease = yesterdayUds?.fansIncrease ?? 0;

      const updateTime = (yesterdayUds?.updatedAt ?? account.updatedAt).toISOString();

      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,
        worksCount: accountWorksCount,
        totalIncome: null,
        yesterdayIncome: null,
        totalViews: accountTotalViews,
        yesterdayViews: accountYesterdayViews,
        yesterdayComments: accountYesterdayComments,
        yesterdayLikes: accountYesterdayLikes,
        yesterdayFansIncrease: accountYesterdayFansIncrease,
        updateTime,
        status: account.status,
      });

      totalWorks += accountWorksCount;
      totalViews += accountTotalViews;
      totalFans += accountFansCount;
      yesterdayViews += accountYesterdayViews;
      yesterdayComments += accountYesterdayComments;
      yesterdayLikes += accountYesterdayLikes;
      yesterdayFansIncrease += accountYesterdayFansIncrease;
    }

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

  /**
   * 获取账号维度的区间统计数据
   * 用于「账号数据」页，支持按日期区间、平台、分组筛选
   */
  async getAccountsAnalytics(
    userId: number,
    options: {
      startDate: string;
      endDate: string;
      platform?: string;
      groupId?: number;
    }
  ): Promise<{
    accounts: Array<{
      id: number;
      nickname: string;
      username: string;
      avatarUrl: string | null;
      platform: string;
      groupId: number | null;
      income: number | null;
      recommendationCount: number | null;
      viewsCount: number;
      commentsCount: number;
      likesCount: number;
      fansIncrease: number;
      fansCount: number;
      updateTime: string;
      status: string;
    }>;
    summary: {
      totalAccounts: number;
      totalIncome: number;
      recommendationCount: number | null;
      viewsCount: number;
      commentsCount: number;
      likesCount: number;
      fansIncrease: number;
      totalFans: number;
    };
  }> {
    const { startDate, endDate, platform, groupId } = options;

    // 只查询支持的平台：抖音、百家号、视频号、小红书
    const allowedPlatforms = ['douyin', 'baijiahao', 'weixin_video', 'xiaohongshu'];

    const accountQuery = this.accountRepository
      .createQueryBuilder('pa')
      .where('pa.userId = :userId', { userId })
      .andWhere('pa.platform IN (:...allowedPlatforms)', { allowedPlatforms });

    if (platform) {
      accountQuery.andWhere('pa.platform = :platform', { platform });
    }
    if (groupId) {
      accountQuery.andWhere('pa.groupId = :groupId', { groupId });
    }

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

    const accountIds = accounts.map(a => a.id);

    // 使用 user_day_statistics 统计区间内的播放/评论/点赞/涨粉等
    const statsRows = await this.userDayStatisticsRepository
      .createQueryBuilder('uds')
      .select('uds.account_id', 'accountId')
      .addSelect('COALESCE(SUM(uds.play_count), 0)', 'viewsCount')
      .addSelect('COALESCE(SUM(uds.comment_count), 0)', 'commentsCount')
      .addSelect('COALESCE(SUM(uds.like_count), 0)', 'likesCount')
      .addSelect('COALESCE(SUM(uds.fans_increase), 0)', 'fansIncrease')
      .addSelect('MAX(uds.updated_at)', 'latestUpdateTime')
      .where('uds.account_id IN (:...accountIds)', { accountIds })
      .andWhere('DATE(uds.record_date) >= :startDate', { startDate })
      .andWhere('DATE(uds.record_date) <= :endDate', { endDate })
      .groupBy('uds.account_id')
      .getRawMany();

    const statMap = new Map<
      number,
      {
        viewsCount: number;
        commentsCount: number;
        likesCount: number;
        fansIncrease: number;
        latestUpdateTime: Date | null;
      }
    >();

    for (const row of statsRows || []) {
      const accountId = Number(row.accountId) || 0;
      if (!accountId) continue;
      statMap.set(accountId, {
        viewsCount: Number(row.viewsCount) || 0,
        commentsCount: Number(row.commentsCount) || 0,
        likesCount: Number(row.likesCount) || 0,
        fansIncrease: Number(row.fansIncrease) || 0,
        latestUpdateTime: row.latestUpdateTime ? new Date(row.latestUpdateTime) : null,
      });
    }

    const resultAccounts: Array<{
      id: number;
      nickname: string;
      username: string;
      avatarUrl: string | null;
      platform: string;
      groupId: number | null;
      income: number | null;
      recommendationCount: number | null;
      viewsCount: number;
      commentsCount: number;
      likesCount: number;
      fansIncrease: number;
      fansCount: number;
      updateTime: string;
      status: string;
    }> = [];

    let totalViews = 0;
    let totalComments = 0;
    let totalLikes = 0;
    let totalFansIncrease = 0;
    let totalFans = 0;

    for (const account of accounts) {
      const stat =
        statMap.get(account.id) ?? {
          viewsCount: 0,
          commentsCount: 0,
          likesCount: 0,
          fansIncrease: 0,
          latestUpdateTime: account.updatedAt ?? null,
        };

      const fansCount = account.fansCount || 0;
      const updateTime = stat.latestUpdateTime
        ? this.formatUpdateTime(stat.latestUpdateTime)
        : this.formatUpdateTime(account.updatedAt ?? new Date());

      resultAccounts.push({
        id: account.id,
        nickname: account.accountName || '',
        username: account.accountId || '',
        avatarUrl: account.avatarUrl,
        platform: account.platform,
        groupId: account.groupId ?? null,
        income: null,
        recommendationCount: null,
        viewsCount: stat.viewsCount,
        commentsCount: stat.commentsCount,
        likesCount: stat.likesCount,
        fansIncrease: stat.fansIncrease,
        fansCount,
        updateTime,
        status: account.status,
      });

      totalViews += stat.viewsCount;
      totalComments += stat.commentsCount;
      totalLikes += stat.likesCount;
      totalFansIncrease += stat.fansIncrease;
      totalFans += fansCount;
    }

    return {
      accounts: resultAccounts,
      summary: {
        totalAccounts: accounts.length,
        totalIncome: 0,
        recommendationCount: null,
        viewsCount: totalViews,
        commentsCount: totalComments,
        likesCount: totalLikes,
        fansIncrease: totalFansIncrease,
        totalFans,
      },
    };
  }

  /**
   * 获取单个账号的详情数据
   * 包括：汇总统计、每日数据、作品列表
   * 说明：
   * - 收益、推荐量目前数据库尚未接入，统一返回 0
   */
  async getAccountDetail(
    userId: number,
    accountId: number,
    options: {
      startDate: string;
      endDate: string;
    }
  ): Promise<{
    summary: {
      income: number;
      recommendationCount: number;
      viewsCount: number;
      commentsCount: number;
      likesCount: number;
      fansIncrease: number;
    };
    dailyData: Array<{
      date: string;
      income: number;
      recommendationCount: number;
      viewsCount: number;
      commentsCount: number;
      likesCount: number;
      fansIncrease: number;
    }>;
    works: Array<{
      id: number;
      title: string;
      coverUrl: string;
      platform: string;
      publishTime: string | null;
      recommendCount: number;
      viewsCount: number;
      commentsCount: number;
      sharesCount: number;
      collectsCount: number;
      likesCount: number;
    }>;
  }> {
    const { startDate, endDate } = options;
    const account = await this.accountRepository.findOne({
      where: { id: accountId, userId },
    });

    if (!account) {
      // 账号不存在或不属于该用户，返回空数据
      return {
        summary: {
          income: 0,
          recommendationCount: 0,
          viewsCount: 0,
          commentsCount: 0,
          likesCount: 0,
          fansIncrease: 0,
        },
        dailyData: [],
        works: [],
      };
    }

    const startDateStr = startDate;
    const endDateStr = endDate;

    // 1. 每日数据：直接从 user_day_statistics 获取指定账号的每日记录
    const udsRows = await this.userDayStatisticsRepository
      .createQueryBuilder('uds')
      .select('uds.record_date', 'recordDate')
      .addSelect('uds.play_count', 'viewsCount')
      .addSelect('uds.comment_count', 'commentsCount')
      .addSelect('uds.like_count', 'likesCount')
      .addSelect('uds.fans_increase', 'fansIncrease')
      .where('uds.account_id = :accountId', { accountId })
      .andWhere('DATE(uds.record_date) >= :startDate', { startDate: startDateStr })
      .andWhere('DATE(uds.record_date) <= :endDate', { endDate: endDateStr })
      .orderBy('uds.record_date', 'ASC')
      .getRawMany();

    const dailyMap = new Map<string, { views: number; comments: number; likes: number; fansIncrease: number }>();
    for (const row of udsRows || []) {
      if (!row.recordDate) continue;
      let dateKey: string;
      if (row.recordDate instanceof Date) {
        const y = row.recordDate.getFullYear();
        const m = String(row.recordDate.getMonth() + 1).padStart(2, '0');
        const d = String(row.recordDate.getDate()).padStart(2, '0');
        dateKey = `${y}-${m}-${d}`;
      } else {
        dateKey = String(row.recordDate).slice(0, 10);
      }

      dailyMap.set(dateKey, {
        views: Number(row.viewsCount) || 0,
        comments: Number(row.commentsCount) || 0,
        likes: Number(row.likesCount) || 0,
        fansIncrease: Number(row.fansIncrease) || 0,
      });
    }

    const dStart = new Date(startDateStr);
    const dEnd = new Date(endDateStr);
    const cursor = new Date(dStart);

    const dailyData: Array<{
      date: string;
      income: number;
      recommendationCount: number;
      viewsCount: number;
      commentsCount: number;
      likesCount: number;
      fansIncrease: number;
    }> = [];

    let totalViews = 0;
    let totalComments = 0;
    let totalLikes = 0;
    let totalFansIncrease = 0;

    while (cursor <= dEnd) {
      const dateKey = this.formatDate(cursor);
      const value = dailyMap.get(dateKey) ?? {
        views: 0,
        comments: 0,
        likes: 0,
        fansIncrease: 0,
      };

      dailyData.push({
        date: dateKey,
        income: 0,
        recommendationCount: 0,
        viewsCount: value.views,
        commentsCount: value.comments,
        likesCount: value.likes,
        fansIncrease: value.fansIncrease,
      });

      totalViews += value.views;
      totalComments += value.comments;
      totalLikes += value.likes;
      totalFansIncrease += value.fansIncrease;

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

    // 2. 作品列表：按作品聚合 work_day_statistics 区间内的数据
    const worksRows = await this.workRepository
      .createQueryBuilder('w')
      .leftJoin(WorkDayStatistics, 'wds', 'wds.work_id = w.id AND wds.record_date >= :wStart AND wds.record_date <= :wEnd', {
        wStart: startDateStr,
        wEnd: endDateStr,
      })
      .select('w.id', 'id')
      .addSelect('w.title', 'title')
      .addSelect('w.cover_url', 'coverUrl')
      .addSelect('w.platform', 'platform')
      .addSelect('w.publish_time', 'publishTime')
      .addSelect('COALESCE(SUM(wds.play_count), 0)', 'viewsCount')
      .addSelect('COALESCE(SUM(wds.comment_count), 0)', 'commentsCount')
      .addSelect('COALESCE(SUM(wds.share_count), 0)', 'sharesCount')
      .addSelect('COALESCE(SUM(wds.collect_count), 0)', 'collectsCount')
      .addSelect('COALESCE(SUM(wds.like_count), 0)', 'likesCount')
      .where('w.userId = :userId', { userId })
      .andWhere('w.accountId = :accountId', { accountId })
      .groupBy('w.id')
      .orderBy('w.publish_time', 'DESC')
      .getRawMany();

    const works = (worksRows || []).map((row) => {
      const publishTime =
        row.publishTime instanceof Date
          ? row.publishTime.toISOString()
          : row.publishTime
          ? String(row.publishTime)
          : null;

      return {
        id: Number(row.id),
        title: row.title || '',
        coverUrl: row.coverUrl || '',
        platform: row.platform || '',
        publishTime,
        // 推荐量目前没有独立字段，统一返回 0
        recommendCount: 0,
        viewsCount: Number(row.viewsCount) || 0,
        commentsCount: Number(row.commentsCount) || 0,
        sharesCount: Number(row.sharesCount) || 0,
        collectsCount: Number(row.collectsCount) || 0,
        likesCount: Number(row.likesCount) || 0,
      };
    });

    return {
      summary: {
        income: 0,
        recommendationCount: 0,
        viewsCount: totalViews,
        commentsCount: totalComments,
        likesCount: totalLikes,
        fansIncrease: totalFansIncrease,
      },
      dailyData,
      works,
    };
  }

  /**
   * 获取作品数据列表（用于「作品数据」页）
   * 依据 work_day_statistics 进行区间汇总统计
   */
  async getWorksAnalytics(
    userId: number,
    options: {
      startDate: string;
      endDate: string;
      platform?: string;
      accountIds?: number[];
      groupId?: number;
      keyword?: string;
      sortBy?: 'publish_desc' | 'publish_asc' | 'views_desc' | 'likes_desc' | 'comments_desc';
      page?: number;
      pageSize?: number;
    }
  ): Promise<{
    summary: {
      totalWorks: number;
      recommendCount: number;
      viewsCount: number;
      commentsCount: number;
      sharesCount: number;
      collectsCount: number;
      likesCount: number;
    };
    total: number;
    works: Array<{
      id: number;
      title: string;
      coverUrl: string;
      platform: string;
      accountId: number;
      accountName: string;
      accountAvatar: string | null;
      workType: string;
      publishTime: string | null;
      recommendCount: number;
      viewsCount: number;
      commentsCount: number;
      sharesCount: number;
      collectsCount: number;
      likesCount: number;
    }>;
  }> {
    const {
      startDate,
      endDate,
      platform,
      accountIds,
      groupId,
      keyword,
      sortBy = 'publish_desc',
      page = 1,
      pageSize = 20,
    } = options;

    const startDateStr = startDate;
    const endDateStr = endDate;

    // 作品列表：指标直接取 works 表 yesterday_* 快照；时间范围仅用于筛选「发布时间」落在范围内的作品
    // 说明：不再按日期范围聚合 work_day_statistics（避免口径随筛选范围变化）
    const qb = this.workRepository
      .createQueryBuilder('w')
      .innerJoin(PlatformAccount, 'pa', 'pa.id = w.accountId')
      .select('w.id', 'id')
      .addSelect('w.title', 'title')
      .addSelect('w.cover_url', 'coverUrl')
      .addSelect('w.platform', 'platform')
      .addSelect('w.accountId', 'accountId')
      .addSelect('pa.accountName', 'accountName')
      .addSelect('pa.avatarUrl', 'accountAvatar')
      .addSelect('w.status', 'workType')
      .addSelect('w.publish_time', 'publishTime')
      .addSelect('COALESCE(w.yesterday_play_count, 0)', 'viewsCount')
      .addSelect('COALESCE(w.yesterday_comment_count, 0)', 'commentsCount')
      .addSelect('COALESCE(w.yesterday_share_count, 0)', 'sharesCount')
      .addSelect('COALESCE(w.yesterday_collect_count, 0)', 'collectsCount')
      .addSelect('COALESCE(w.yesterday_like_count, 0)', 'likesCount')
      .where('w.userId = :userId', { userId })
      .andWhere('w.publish_time IS NOT NULL')
      .andWhere('DATE(w.publish_time) >= :startDate AND DATE(w.publish_time) <= :endDate', {
        startDate: startDateStr,
        endDate: endDateStr,
      });

    if (platform) {
      qb.andWhere('w.platform = :platform', { platform });
    }
    if (accountIds && accountIds.length > 0) {
      qb.andWhere('w.accountId IN (:...accountIds)', { accountIds });
    }
    if (groupId) {
      qb.andWhere('pa.groupId = :groupId', { groupId });
    }
    if (keyword && keyword.trim()) {
      const kw = `%${keyword.trim()}%`;
      qb.andWhere('w.title LIKE :kw', { kw });
    }
    // SQL 里只按发布时间倒序，具体排序口径在内存中根据 sortBy 再处理
    qb.orderBy('w.publish_time', 'DESC');

    // 先获取全部满足条件的作品聚合行，再在内存中做分页和汇总
    const allRows = await qb.getRawMany();

    // 根据 sortBy 对结果进行排序
    const sortedRows = [...allRows];
    sortedRows.sort((a, b) => {
      const getNum = (v: unknown) => Number(v) || 0;
      if (sortBy === 'views_desc') {
        return getNum(b.viewsCount) - getNum(a.viewsCount);
      }
      if (sortBy === 'likes_desc') {
        return getNum(b.likesCount) - getNum(a.likesCount);
      }
      if (sortBy === 'comments_desc') {
        return getNum(b.commentsCount) - getNum(a.commentsCount);
      }

      // 发布时间排序（默认 publish_desc）
      const toTime = (v: unknown) => {
        if (!v) return 0;
        if (v instanceof Date) return v.getTime();
        const t = new Date(String(v)).getTime();
        return Number.isNaN(t) ? 0 : t;
      };

      const ta = toTime(a.publishTime);
      const tb = toTime(b.publishTime);

      if (sortBy === 'publish_asc') {
        return ta - tb;
      }
      // publish_desc
      return tb - ta;
    });

    const total = sortedRows.length;
    const offset = (page - 1) * pageSize;
    const pagedRows = sortedRows.slice(offset, offset + pageSize);

    let totalViews = 0;
    let totalComments = 0;
    let totalShares = 0;
    let totalCollects = 0;
    let totalLikes = 0;

    // 汇总统计使用所有作品（而不是当前页），确保顶部统计口径统一
    for (const row of sortedRows) {
      const views = Number(row.viewsCount) || 0;
      const comments = Number(row.commentsCount) || 0;
      const shares = Number(row.sharesCount) || 0;
      const collects = Number(row.collectsCount) || 0;
      const likes = Number(row.likesCount) || 0;

      totalViews += views;
      totalComments += comments;
      totalShares += shares;
      totalCollects += collects;
      totalLikes += likes;
    }

    // 当前页作品列表只返回分页后的数据
    const works = pagedRows.map((row) => {
      const views = Number(row.viewsCount) || 0;
      const comments = Number(row.commentsCount) || 0;
      const shares = Number(row.sharesCount) || 0;
      const collects = Number(row.collectsCount) || 0;
      const likes = Number(row.likesCount) || 0;

      const publishTime =
        row.publishTime instanceof Date
          ? row.publishTime.toISOString()
          : row.publishTime
          ? String(row.publishTime)
          : null;

      return {
        id: Number(row.id),
        title: row.title || '',
        coverUrl: row.coverUrl || '',
        platform: row.platform || '',
        accountId: Number(row.accountId) || 0,
        accountName: row.accountName || '',
        accountAvatar: row.accountAvatar || null,
        workType: row.workType || '动态',
        publishTime,
        recommendCount: 0,
        viewsCount: views,
        commentsCount: comments,
        sharesCount: shares,
        collectsCount: collects,
        likesCount: likes,
      };
    });

    return {
      summary: {
        totalWorks: total,
        recommendCount: 0,
        viewsCount: totalViews,
        commentsCount: totalComments,
        sharesCount: totalShares,
        collectsCount: totalCollects,
        likesCount: totalLikes,
      },
      total,
      works,
    };
  }

  /**
   * 获取平台详情数据
   * 包括汇总统计、每日汇总数据和账号列表
   */
  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: [],
      };
    }

    /**
     * 口径变更：user_day_statistics 的各项数据为“每日单独值”，不再是累计值
     * 因此平台详情：
     * - 区间汇总：直接 SUM(user_day_statistics.*)（按账号、按天）
     * - 每日汇总：按 record_date 分组 SUM
     */
    const accountIds = accounts.map(a => a.id);
    const totalAccounts = accounts.length;

    const [dailyRows, perAccountRows] = await Promise.all([
      // 按日期维度汇总（每天所有账号数据之和）
      // 这里直接使用原生 SQL，以保证和文档/手动验证时看到的 SQL 完全一致：
      //
      // SELECT
      //   uds.record_date AS recordDate,
      //   COALESCE(SUM(uds.play_count), 0)    AS viewsCount,
      //   COALESCE(SUM(uds.comment_count), 0) AS commentsCount,
      //   COALESCE(SUM(uds.like_count), 0)    AS likesCount,
      //   COALESCE(SUM(uds.fans_increase), 0) AS fansIncrease
      // FROM user_day_statistics uds
      // WHERE uds.account_id IN (...)
      //   AND uds.record_date >= ?
      //   AND uds.record_date <= ?
      // GROUP BY uds.record_date
      // ORDER BY uds.record_date ASC;
      (async () => {
        if (!accountIds.length) return [];

        const inPlaceholders = accountIds.map(() => '?').join(',');
        const sql = `
          SELECT
            uds.record_date AS recordDate,
            COALESCE(SUM(uds.play_count), 0)      AS viewsCount,
            COALESCE(SUM(uds.comment_count), 0)   AS commentsCount,
            COALESCE(SUM(uds.like_count), 0)      AS likesCount,
            COALESCE(SUM(uds.fans_increase), 0)   AS fansIncrease
          FROM user_day_statistics uds
          WHERE uds.account_id IN (${inPlaceholders})
            AND uds.record_date >= ?
            AND uds.record_date <= ?
          GROUP BY uds.record_date
          ORDER BY uds.record_date ASC
        `;

        const params = [...accountIds, startDateStr, endDateStr];
        return await AppDataSource.query(sql, params);
      })(),
      // 按账号维度汇总（区间内所有天的和）
      this.userDayStatisticsRepository
        .createQueryBuilder('uds')
        .select('uds.account_id', 'accountId')
        .addSelect('COUNT(1)', 'rowCount')
        .addSelect('COALESCE(SUM(uds.play_count), 0)', 'viewsCount')
        .addSelect('COALESCE(SUM(uds.comment_count), 0)', 'commentsCount')
        .addSelect('COALESCE(SUM(uds.like_count), 0)', 'likesCount')
        .addSelect('COALESCE(SUM(uds.fans_increase), 0)', 'fansIncrease')
        .addSelect('MAX(uds.updated_at)', 'latestUpdateTime')
        .where('uds.account_id IN (:...accountIds)', { accountIds })
        .andWhere('DATE(uds.record_date) >= :startDate', { startDate: startDateStr })
        .andWhere('DATE(uds.record_date) <= :endDate', { endDate: endDateStr })
        .groupBy('uds.account_id')
        .getRawMany(),
    ]);

    // ===== 按日期汇总：每日汇总数据 =====
    const dailyMap = new Map<string, { views: number; comments: number; likes: number; fansIncrease: number }>();
    for (const row of dailyRows || []) {
      if (!row.recordDate) continue;

      /**
       * 注意：record_date 在实体里是 DATE 类型，TypeORM 读出来通常是 Date 对象。
       * 之前用 String(row.recordDate).slice(0, 10) 会得到类似 "Wed Jan 28" 这样的字符串前 10 位，
       * 导致 key 和下面 this.formatDate(cursor) 生成的 "YYYY-MM-DD" 不一致，从而 dailyMap 命中失败，全部变成 0。
       *
       * 这里改成显式按本地时间拼出 "YYYY-MM-DD"，确保与 startDate/endDate 的格式一致。
       */
      let dateKey: string;
      if (row.recordDate instanceof Date) {
        const y = row.recordDate.getFullYear();
        const m = String(row.recordDate.getMonth() + 1).padStart(2, '0');
        const d = String(row.recordDate.getDate()).padStart(2, '0');
        dateKey = `${y}-${m}-${d}`;
      } else {
        // 数据库如果已经返回字符串，例如 "2026-01-28"，直接截前 10 位即可
        dateKey = String(row.recordDate).slice(0, 10);
      }

      const prev = dailyMap.get(dateKey) ?? { views: 0, comments: 0, likes: 0, fansIncrease: 0 };

      dailyMap.set(dateKey, {
        views: prev.views + (Number(row.viewsCount) || 0),
        comments: prev.comments + (Number(row.commentsCount) || 0),
        likes: prev.likes + (Number(row.likesCount) || 0),
        fansIncrease: prev.fansIncrease + (Number(row.fansIncrease) || 0),
      });
    }

    // 补齐日期区间（没有数据也返回 0）
    const dailyData: Array<{
      date: string;
      income: number;
      recommendationCount: number | null;
      viewsCount: number;
      commentsCount: number;
      likesCount: number;
      fansIncrease: number;
    }> = [];

    const dStart = new Date(startDateStr);
    const dEnd = new Date(endDateStr);
    const cursor = new Date(dStart);
    while (cursor <= dEnd) {
      const dateKey = this.formatDate(cursor);
      const v = dailyMap.get(dateKey) ?? { views: 0, comments: 0, likes: 0, fansIncrease: 0 };
      dailyData.push({
        date: dateKey,
        income: 0,
        recommendationCount: null,
        viewsCount: v.views,
        commentsCount: v.comments,
        likesCount: v.likes,
        fansIncrease: v.fansIncrease,
      });
      cursor.setDate(cursor.getDate() + 1);
    }

    // ===== 按账号汇总：账号列表 & 顶部汇总 =====
    const perAccountMap = new Map<
      number,
      { rowCount: number; views: number; comments: number; likes: number; fansIncrease: number; latestUpdateTime: Date | null }
    >();
    for (const row of perAccountRows || []) {
      const accountId = Number(row.accountId) || 0;
      if (!accountId) continue;
      perAccountMap.set(accountId, {
        rowCount: Number(row.rowCount) || 0,
        views: Number(row.viewsCount) || 0,
        comments: Number(row.commentsCount) || 0,
        likes: Number(row.likesCount) || 0,
        fansIncrease: Number(row.fansIncrease) || 0,
        latestUpdateTime: row.latestUpdateTime ? new Date(row.latestUpdateTime) : null,
      });
    }

    // 顶部汇总：直接用账号维度汇总，确保和“账号详细数据”一致
    let totalViews = 0;
    let totalComments = 0;
    let totalLikes = 0;
    let totalFansIncrease = 0;
    for (const agg of perAccountMap.values()) {
      totalViews += agg.views;
      totalComments += agg.comments;
      totalLikes += agg.likes;
      totalFansIncrease += agg.fansIncrease;
    }

    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;
    }> = accounts.map((account) => {
      const agg =
        perAccountMap.get(account.id) ?? { rowCount: 0, views: 0, comments: 0, likes: 0, fansIncrease: 0, latestUpdateTime: null };
      const updateTime = agg.latestUpdateTime ? this.formatUpdateTime(agg.latestUpdateTime) : '';
      return {
        id: account.id,
        nickname: account.accountName || '',
        username: account.accountId || '',
        avatarUrl: account.avatarUrl,
        platform: account.platform,
        income: null,
        recommendationCount: null,
        // 没有任何记录时，前端展示“获取失败”，避免把“无数据”误显示成 0
        viewsCount: agg.rowCount > 0 ? agg.views : null,
        commentsCount: agg.comments,
        likesCount: agg.likes,
        fansIncrease: agg.fansIncrease,
        updateTime,
      };
    });

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

  /**
   * 格式化更新时间为统一的人类可读格式：
   * - 如果是今年：MM-DD HH:mm（例如：01-22 10:22）
   * - 如果是往年：YYYY-MM-DD HH:mm（例如：2025-12-22 10:22）
   */
  private formatUpdateTime(date: Date): string {
    const y = date.getFullYear();
    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');

    // 始终返回完整的 YYYY-MM-DD HH:mm 格式，避免前端 dayjs 解析省略年份时被误解析为 2001 年
    return `${y}-${month}-${day} ${hours}:${minutes}`;
  }
}