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;
  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);

    /**
     * 口径变更：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.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;
  }

  /**
   * 获取每个账号在指定日期（<= 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;
      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 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 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 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,
        totalIncome: null,
        yesterdayIncome: null,
        totalViews: accountTotalViews,
        yesterdayViews: accountYesterdayViews,
        yesterdayComments: accountYesterdayComments,
        yesterdayLikes: accountYesterdayLikes,
        yesterdayFansIncrease: accountYesterdayFansIncrease,
        updateTime,
        status: account.status,
      });

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

    return {
      accounts: accountList,
      summary: {
        totalAccounts,
        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;

    // 基础查询：当前用户的作品
    const qb = 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,
      })
      .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(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 });

    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 OR pa.accountName LIKE :kw)', { kw });
    }

    qb.groupBy('w.id');

    // 排序：统一按发布时间倒序（最新的在前）
    qb.orderBy('w.publish_time', 'DESC');

    // 统计总数（作品数）
    const countQb = this.workRepository
      .createQueryBuilder('w')
      .innerJoin(PlatformAccount, 'pa', 'pa.id = w.accountId')
      .where('w.userId = :userId', { userId });

    if (platform) {
      countQb.andWhere('w.platform = :platform', { platform });
    }
    if (accountIds && accountIds.length > 0) {
      countQb.andWhere('w.accountId IN (:...accountIds)', { accountIds });
    }
    if (groupId) {
      countQb.andWhere('pa.groupId = :groupId', { groupId });
    }
    if (keyword && keyword.trim()) {
      const kw = `%${keyword.trim()}%`;
      countQb.andWhere('(w.title LIKE :kw OR pa.accountName LIKE :kw)', { kw });
    }

    const total = await countQb.getCount();

    // 分页
    const offset = (page - 1) * pageSize;
    qb.skip(offset).take(pageSize);

    const rows = await qb.getRawMany();

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

    const works = rows.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;

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

      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 nowYear = new 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');

    if (y === nowYear) {
      return `${month}-${day} ${hours}:${minutes}`;
    }
    return `${y}-${month}-${day} ${hours}:${minutes}`;
  }
}