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

import { AppDataSource, Work, PlatformAccount, Comment, WorkDayStatistics } from '../models/index.js';
import { AppError } from '../middleware/error.js';
import { ERROR_CODES, HTTP_STATUS } from '@media-manager/shared';
import type { PlatformType, Work as WorkType, WorkStats, WorksQueryParams } from '@media-manager/shared';
import { logger } from '../utils/logger.js';
import { headlessBrowserService } from './HeadlessBrowserService.js';
import { CookieManager } from '../automation/cookie.js';
import { WorkDayStatisticsService } from './WorkDayStatisticsService.js';
import { taskQueueService } from './TaskQueueService.js';
import { wsManager } from '../websocket/index.js';

export class WorkService {
  private workRepository = AppDataSource.getRepository(Work);
  private accountRepository = AppDataSource.getRepository(PlatformAccount);
  private commentRepository = AppDataSource.getRepository(Comment);
  private workDayStatisticsService = new WorkDayStatisticsService();

  /**
   * 获取作品列表
   */
  async getWorks(userId: number, params: WorksQueryParams): Promise<{ items: WorkType[]; total: number }> {
    const queryBuilder = this.workRepository
      .createQueryBuilder('work')
      .where('work.userId = :userId', { userId });

    if (params.accountId) {
      queryBuilder.andWhere('work.accountId = :accountId', { accountId: params.accountId });
    }
    if (params.platform) {
      queryBuilder.andWhere('work.platform = :platform', { platform: params.platform });
    }
    if (params.status) {
      queryBuilder.andWhere('work.status = :status', { status: params.status });
    }
    if (params.keyword) {
      queryBuilder.andWhere('work.title LIKE :keyword', { keyword: `%${params.keyword}%` });
    }

    const page = params.page || 1;
    const pageSize = params.pageSize || 12;

    const [items, total] = await queryBuilder
      .orderBy('work.publishTime', 'DESC')
      .skip((page - 1) * pageSize)
      .take(pageSize)
      .getManyAndCount();

    return {
      items: items.map(this.formatWork),
      total,
    };
  }

  /**
   * 获取作品统计
   */
  async getStats(userId: number, params?: WorksQueryParams): Promise<WorkStats> {
    const queryBuilder = this.workRepository
      .createQueryBuilder('work')
      .select([
        'COUNT(*) as totalCount',
        'SUM(CASE WHEN status = "published" THEN 1 ELSE 0 END) as publishedCount',
        'CAST(SUM(work.playCount) AS SIGNED) as totalPlayCount',
        'CAST(SUM(work.likeCount) AS SIGNED) as totalLikeCount',
        'CAST(SUM(work.commentCount) AS SIGNED) as totalCommentCount',
      ])
      .where('work.userId = :userId', { userId });

    if (params?.accountId) {
      queryBuilder.andWhere('work.accountId = :accountId', { accountId: params.accountId });
    }
    if (params?.platform) {
      queryBuilder.andWhere('work.platform = :platform', { platform: params.platform });
    }
    if (params?.status) {
      queryBuilder.andWhere('work.status = :status', { status: params.status });
    }
    if (params?.keyword) {
      queryBuilder.andWhere('work.title LIKE :keyword', { keyword: `%${params.keyword}%` });
    }

    const result = await queryBuilder.getRawOne();

    return {
      totalCount: parseInt(result.totalCount) || 0,
      publishedCount: parseInt(result.publishedCount) || 0,
      totalPlayCount: parseInt(result.totalPlayCount) || 0,
      totalLikeCount: parseInt(result.totalLikeCount) || 0,
      totalCommentCount: parseInt(result.totalCommentCount) || 0,
    };
  }

  /**
   * 获取单个作品
   */
  async getWorkById(userId: number, workId: number): Promise<WorkType> {
    const work = await this.workRepository.findOne({
      where: { id: workId, userId },
    });
    if (!work) {
      throw new AppError('作品不存在', HTTP_STATUS.NOT_FOUND, ERROR_CODES.NOT_FOUND);
    }
    return this.formatWork(work);
  }

  /**
   * 同步账号的作品
   */
  async syncWorks(
    userId: number,
    accountId?: number,
    platform?: string,
    onProgress?: (progress: number, currentStep: string) => void
  ): Promise<{
    synced: number;
    accounts: number;
    accountSummaries: Array<{
      accountId: number;
      platform: PlatformType;
      worksListLength: number;
      worksCount: number;
      source?: string;
      pythonAvailable?: boolean;
      syncedCount: number;
    }>;
  }> {
    logger.info(`[SyncWorks] Starting sync for userId: ${userId}, accountId: ${accountId || 'all'}, platform: ${platform || 'all'}`);

    // 先查看所有账号（调试用）
    const allAccounts = await this.accountRepository.find({ where: { userId } });
    logger.info(`[SyncWorks] All accounts for user ${userId}: ${allAccounts.map(a => `id=${a.id},status=${a.status},platform=${a.platform}`).join('; ')}`);

    // 同时查询 active 和 expired 状态的账号（expired 的账号 cookie 可能实际上还有效）
    const queryBuilder = this.accountRepository
      .createQueryBuilder('account')
      .where('account.userId = :userId', { userId })
      .andWhere('account.status IN (:...statuses)', { statuses: ['active', 'expired'] });

    if (accountId) {
      queryBuilder.andWhere('account.id = :accountId', { accountId });
    } else if (platform) {
      queryBuilder.andWhere('account.platform = :platform', { platform });
    }

    const accounts = await queryBuilder.getMany();
    logger.info(`[SyncWorks] Found ${accounts.length} accounts (active + expired)`);

    let totalSynced = 0;
    let accountCount = 0;
    const accountSummaries: Array<{
      accountId: number;
      platform: PlatformType;
      worksListLength: number;
      worksCount: number;
      source?: string;
      pythonAvailable?: boolean;
      syncedCount: number;
    }> = [];

    for (let i = 0; i < accounts.length; i++) {
      const account = accounts[i];
      try {
        logger.info(`[SyncWorks] Syncing account ${account.id} (${account.platform}, status: ${account.status})`);
        onProgress?.(
          Math.min(95, 5 + Math.round((i / Math.max(1, accounts.length)) * 90)),
          `同步账号 ${i + 1}/${accounts.length}: ${account.accountName || account.id} (${account.platform})`
        );

        const result = await this.syncAccountWorks(userId, account, (p, step) => {
          const overall = 5 + Math.round(((i + Math.max(0, Math.min(1, p))) / Math.max(1, accounts.length)) * 90);
          onProgress?.(Math.min(95, overall), step);
        });
        totalSynced += result.syncedCount;
        accountCount++;
        accountSummaries.push({
          accountId: account.id,
          platform: account.platform as PlatformType,
          worksListLength: result.worksListLength,
          worksCount: result.worksCount,
          source: result.source,
          pythonAvailable: result.pythonAvailable,
          syncedCount: result.syncedCount,
        });
        logger.info(`[SyncWorks] Account ${account.id} synced ${result.syncedCount} works`);

        // 如果同步成功且账号状态是 expired，则恢复为 active
        if (result.syncedCount > 0 && account.status === 'expired') {
          await this.accountRepository.update(account.id, { status: 'active' });
          logger.info(`[SyncWorks] Account ${account.id} status restored to active`);
        }
      } catch (error) {
        logger.error(`Failed to sync works for account ${account.id}:`, error);
      }
    }

    onProgress?.(100, `同步完成：共同步 ${totalSynced} 条作品（${accountCount} 个账号）`);
    logger.info(`[SyncWorks] Complete: ${totalSynced} works synced from ${accountCount} accounts`);
    return { synced: totalSynced, accounts: accountCount, accountSummaries };
  }

  /**
   * 同步单个账号的作品
   */
  async syncAccountWorks(
    userId: number,
    account: PlatformAccount,
    onProgress?: (progress: number, currentStep: string) => void
  ): Promise<{
    syncedCount: number;
    worksListLength: number;
    worksCount: number;
    source?: string;
    pythonAvailable?: boolean;
  }> {
    logger.info(`[SyncAccountWorks] Starting for account ${account.id} (${account.platform})`);

    if (!account.cookieData) {
      logger.warn(`Account ${account.id} has no cookie data`);
      return { syncedCount: 0, worksListLength: 0, worksCount: 0 };
    }

    // 解密 Cookie
    let decryptedCookies: string;
    try {
      decryptedCookies = CookieManager.decrypt(account.cookieData);
      logger.info(`[SyncAccountWorks] Cookie decrypted successfully`);
    } catch {
      decryptedCookies = account.cookieData;
      logger.info(`[SyncAccountWorks] Using raw cookie data`);
    }

    // 解析 Cookie - 支持两种格式
    const platform = account.platform as PlatformType;
    let cookieList: { name: string; value: string; domain: string; path: string }[];
    try {
      // 先尝试 JSON 格式
      cookieList = JSON.parse(decryptedCookies);
      logger.info(`[SyncAccountWorks] Parsed ${cookieList.length} cookies from JSON format`);
    } catch {
      // 如果 JSON 解析失败，尝试解析 "name=value; name2=value2" 格式
      cookieList = this.parseCookieString(decryptedCookies, platform);
      logger.info(`[SyncAccountWorks] Parsed ${cookieList.length} cookies from string format`);
      if (cookieList.length === 0) {
        logger.error(`Invalid cookie format for account ${account.id}`);
        return { syncedCount: 0, worksListLength: 0, worksCount: 0 };
      }
    }

    // 获取作品列表
    logger.info(`[SyncAccountWorks] Fetching account info from ${platform}...`);
    onProgress?.(0.1, `获取作品列表中：${account.accountName || account.id} (${platform})`);
    const accountInfo = await headlessBrowserService.fetchAccountInfo(platform, cookieList, {
      onWorksFetchProgress: (info) => {
        const declaredTotal = typeof info.declaredTotal === 'number' ? info.declaredTotal : 0;
        const ratio = declaredTotal > 0 ? Math.min(1, info.totalSoFar / declaredTotal) : 0;
        onProgress?.(
          0.1 + ratio * 0.2,
          `拉取作品中：${account.accountName || account.id} (${platform}) ${info.totalSoFar}/${declaredTotal || '?'}`
        );
      },
    });
    logger.info(`[SyncAccountWorks] Got ${accountInfo.worksList?.length || 0} works from API`);
    onProgress?.(
      0.3,
      `拉取完成：${account.accountName || account.id} (${platform}) python=${accountInfo.pythonAvailable ? 'ok' : 'off'} source=${accountInfo.source || 'unknown'} list=${accountInfo.worksList?.length || 0} total=${accountInfo.worksCount || 0}`
    );

    // #6075: 同步作品时同步更新账号的头像和昵称
    if (accountInfo.accountName || accountInfo.avatarUrl) {
      const accountUpdate: Partial<PlatformAccount> = {};
      const defaultNames = [`${platform}账号`, '未知账号', '抖音账号', '小红书账号', '快手账号', '视频号账号', 'B站账号', '头条账号', '百家号账号'];

      if (accountInfo.accountName && !defaultNames.includes(accountInfo.accountName) && accountInfo.accountName !== account.accountName) {
        accountUpdate.accountName = accountInfo.accountName;
      }
      if (accountInfo.avatarUrl && accountInfo.avatarUrl !== account.avatarUrl) {
        accountUpdate.avatarUrl = accountInfo.avatarUrl;
      }
      if (Object.keys(accountUpdate).length > 0) {
        await this.accountRepository.update(account.id, accountUpdate as any);
        logger.info(`[SyncAccountWorks] Updated account info: ${Object.keys(accountUpdate).join(', ')}`);
        wsManager.sendToUser(userId, 'account:info_updated', {
          accountId: account.id,
          ...accountUpdate,
        });
      }
    }

    let syncedCount = 0;

    // 收集远程作品的 platformVideoId
    const remotePlatformVideoIds = new Set<string>();

    if (accountInfo.worksList && accountInfo.worksList.length > 0) {
      const legacyToCanonicalInRun = new Map<string, string>();
      const processedPlatformVideoIds = new Set<string>();
      const total = accountInfo.worksList.length;
      for (const workItem of accountInfo.worksList) {
        const titleForId = (workItem.title || '').trim();
        const publishTimeForId = (workItem.publishTime || '').trim();
        const legacyFallbackId = `${platform}_${titleForId}_${publishTimeForId}`.substring(0, 100);
        let canonicalVideoId = (workItem.videoId || '').trim() || legacyFallbackId;

        // 小红书每日同步只同步三个月内的作品（原为一年内，现改为三个月）
        if (platform === 'xiaohongshu') {
          const publishedAt = this.parsePublishTime(workItem.publishTime);
          if (publishedAt) {
            const threeMonthsAgo = new Date();
            threeMonthsAgo.setMonth(threeMonthsAgo.getMonth() - 3);
            if (publishedAt < threeMonthsAgo) continue;
          }
        }

        if (platform === 'weixin_video') {
          const rawVideoId = (workItem.videoId || '').trim();
          if (rawVideoId) {
            legacyToCanonicalInRun.set(legacyFallbackId, rawVideoId);
            canonicalVideoId = rawVideoId;
          } else {
            const mapped = legacyToCanonicalInRun.get(legacyFallbackId);
            if (mapped) {
              canonicalVideoId = mapped;
            }
          }
        }

        if (processedPlatformVideoIds.has(canonicalVideoId)) {
          continue;
        }
        processedPlatformVideoIds.add(canonicalVideoId);

        remotePlatformVideoIds.add(canonicalVideoId);
        if (legacyFallbackId !== canonicalVideoId) {
          remotePlatformVideoIds.add(legacyFallbackId);
        }

        let work = await this.workRepository.findOne({
          where: { accountId: account.id, platformVideoId: canonicalVideoId },
        });

        if (platform === 'weixin_video' && work && legacyFallbackId !== canonicalVideoId) {
          const legacyWork = await this.workRepository.findOne({
            where: { accountId: account.id, platformVideoId: legacyFallbackId },
          });
          if (legacyWork && legacyWork.id !== work.id) {
            await AppDataSource.getRepository(Comment).update(
              { workId: legacyWork.id },
              { workId: work.id }
            );
            await this.workDayStatisticsService.deleteByWorkId(legacyWork.id);
            await this.workRepository.delete(legacyWork.id);
          }
        }

        if (!work && legacyFallbackId !== canonicalVideoId) {
          const legacyWork = await this.workRepository.findOne({
            where: { accountId: account.id, platformVideoId: legacyFallbackId },
          });

          if (legacyWork) {
            const canonicalWork = await this.workRepository.findOne({
              where: { accountId: account.id, platformVideoId: canonicalVideoId },
            });

            if (canonicalWork) {
              await AppDataSource.getRepository(Comment).update(
                { workId: legacyWork.id },
                { workId: canonicalWork.id }
              );
              await this.workDayStatisticsService.deleteByWorkId(legacyWork.id);
              await this.workRepository.delete(legacyWork.id);
              work = canonicalWork;
            } else {
              await this.workRepository.update(legacyWork.id, {
                platformVideoId: canonicalVideoId,
              });
              work = { ...legacyWork, platformVideoId: canonicalVideoId };
            }
          }
        }

        if (work) {
          await this.workRepository.update(work.id, {
            title: workItem.title || work.title,
            coverUrl: workItem.coverUrl || work.coverUrl,
            videoUrl: workItem.videoUrl !== undefined ? workItem.videoUrl || null : work.videoUrl,
            duration: workItem.duration || work.duration,
            status: workItem.status || work.status,
            playCount: workItem.playCount ?? work.playCount,
            likeCount: workItem.likeCount ?? work.likeCount,
            commentCount: workItem.commentCount ?? work.commentCount,
            shareCount: workItem.shareCount ?? work.shareCount,
            collectCount: workItem.collectCount ?? work.collectCount,
          });
        } else {
          // 创建新作品
          const work = this.workRepository.create({
            userId,
            accountId: account.id,
            platform,
            platformVideoId: canonicalVideoId,
            title: workItem.title || '',
            coverUrl: workItem.coverUrl || '',
            videoUrl: workItem.videoUrl || null,
            duration: workItem.duration || '00:00',
            status: this.normalizeStatus(workItem.status),
            publishTime: this.parsePublishTime(workItem.publishTime),
            playCount: workItem.playCount || 0,
            likeCount: workItem.likeCount || 0,
            commentCount: workItem.commentCount || 0,
            shareCount: workItem.shareCount || 0,
            collectCount: workItem.collectCount || 0,
          });

          await this.workRepository.save(work);
        }
        syncedCount++;
        if (syncedCount === 1 || syncedCount === total || syncedCount % 10 === 0) {
          onProgress?.(0.3 + (syncedCount / total) * 0.65, `写入作品：${account.accountName || account.id} ${syncedCount}/${total}`);
        }
      }

      logger.info(`Synced ${syncedCount} works for account ${account.id}`);
    }

    if (platform === 'weixin_video') {
      await this.dedupeWeixinVideoWorks(account.id);
    }

    // 删除本地存在但远程已删除的作品
    const remoteListLength = accountInfo.worksList?.length || 0;
    const expectedRemoteCount = accountInfo.worksCount || 0;
    const remoteComplete =
      typeof accountInfo.worksListComplete === 'boolean'
        ? accountInfo.worksListComplete
        : expectedRemoteCount > 0
          ? remoteListLength >= expectedRemoteCount
          : remoteListLength > 0;

    let skipLocalDeletions = false;
    if (!remoteComplete) {
      logger.warn(
        `[SyncAccountWorks] Skipping local deletions for account ${account.id} because remote works list seems incomplete (remote=${remoteListLength}, expected=${expectedRemoteCount})`
      );
      skipLocalDeletions = true;
    } else if (remotePlatformVideoIds.size === 0) {
      logger.warn(`[SyncAccountWorks] Skipping local deletions for account ${account.id} because no remote IDs were collected`);
      skipLocalDeletions = true;
    } else {
      const localWorks = await this.workRepository.find({
        where: { accountId: account.id },
      });

      if (platform === 'weixin_video') {
        logger.info(`[SyncAccountWorks] Skipping local deletions for ${platform} account ${account.id} to avoid false deletions`);
        skipLocalDeletions = true;
      }
      if (platform === 'xiaohongshu') {
        logger.info(`[SyncAccountWorks] Skipping local deletions for ${platform} account ${account.id} (only syncing works within 3 months)`);
        skipLocalDeletions = true;
      }

      const matchedCount = localWorks.reduce(
        (sum, w) => sum + (remotePlatformVideoIds.has(w.platformVideoId) ? 1 : 0),
        0
      );
      const matchRatio = localWorks.length > 0 ? matchedCount / localWorks.length : 1;
      if (!skipLocalDeletions && localWorks.length >= 10 && matchRatio < 0.2) {
        logger.warn(
          `[SyncAccountWorks] Skipping local deletions for account ${account.id} because remote/local ID match ratio is too low (matched=${matchedCount}/${localWorks.length})`
        );
        skipLocalDeletions = true;
      }

      if (!skipLocalDeletions) {
        let deletedCount = 0;
        for (const localWork of localWorks) {
          if (!remotePlatformVideoIds.has(localWork.platformVideoId)) {
            await AppDataSource.getRepository(Comment).delete({ workId: localWork.id });
            await this.workDayStatisticsService.deleteByWorkId(localWork.id);
            await this.workRepository.delete(localWork.id);
            deletedCount++;
            logger.info(`Deleted work ${localWork.id} (${localWork.title}) - no longer exists on platform`);
          }
        }

        if (deletedCount > 0) {
          logger.info(`Deleted ${deletedCount} works that no longer exist on platform for account ${account.id}`);
        }
      }
    }

    // 同步作品后：不再基于 works 累计值写 work_day_statistics，当天快照全部交给各平台专门的“每日数据”任务
    // try {
    //   await this.saveWorkDayStatistics(account);
    // } catch (error) {
    //   logger.error(`[SyncAccountWorks] Failed to save day statistics for account ${account.id}:`, error);
    // }

    // 小红书：如果是新作品且 work_day_statistics 中尚无任何记录，则补首批日统计 & works.yesterday_*（不受14天限制）
    if (platform === 'xiaohongshu') {
      try {
        const works = await this.workRepository.find({
          where: { accountId: account.id, platform },
          select: ['id'],
        });
        const workIds = works.map((w) => w.id);
        if (workIds.length > 0) {
          const rows = await AppDataSource.getRepository(WorkDayStatistics)
            .createQueryBuilder('wds')
            .select('DISTINCT wds.work_id', 'workId')
            .where('wds.work_id IN (:...ids)', { ids: workIds })
            .getRawMany();
          const hasStats = new Set<number>(rows.map((r: any) => Number(r.workId)));
          const needInitIds = workIds.filter((id) => !hasStats.has(id));

          if (needInitIds.length > 0) {
            logger.info(
              `[SyncAccountWorks] XHS account ${account.id} has ${needInitIds.length} works without statistics, running initial note/base import.`
            );
            // 放入任务队列异步执行，避免阻塞同步作品流程
            taskQueueService.createTask(account.userId, {
              type: 'xhs_work_stats_backfill',
              title: `小红书作品补数（${needInitIds.length}）`,
              accountId: account.id,
              platform: 'xiaohongshu',
              data: {
                workIds: needInitIds,
              },
            });
          }
        }
      } catch (err) {
        logger.error(
          `[SyncAccountWorks] Failed to backfill XHS work_day_statistics for account ${account.id}:`,
          err
        );
      }
    }

    // 抖音：如果是新作品且 work_day_statistics 中尚无任何记录，则异步补齐历史日统计 & works.yesterday_*（使用 DouyinWorkStatisticsImportService）
    if (platform === 'douyin') {
      try {
        const works = await this.workRepository.find({
          where: { accountId: account.id, platform },
          select: ['id'],
        });
        const workIds = works.map((w) => w.id);
        if (workIds.length > 0) {
          const rows = await AppDataSource.getRepository(WorkDayStatistics)
            .createQueryBuilder('wds')
            .select('DISTINCT wds.work_id', 'workId')
            .where('wds.work_id IN (:...ids)', { ids: workIds })
            .getRawMany();
          const hasStats = new Set<number>(rows.map((r: any) => Number(r.workId)));
          const needInitIds = workIds.filter((id) => !hasStats.has(id));

          if (needInitIds.length > 0) {
            logger.info(
              `[SyncAccountWorks] DY account ${account.id} has ${needInitIds.length} works without statistics, enqueue dy_work_stats_backfill task.`
            );
            taskQueueService.createTask(account.userId, {
              type: 'dy_work_stats_backfill',
              title: `抖音作品补数（${needInitIds.length}）`,
              accountId: account.id,
              platform: 'douyin',
              data: {
                workIds: needInitIds,
              },
            });
          }
        }
      } catch (err) {
        logger.error(
          `[SyncAccountWorks] Failed to enqueue DY work_day_statistics backfill for account ${account.id}:`,
          err
        );
      }
    }

    // 百家号：如果是新作品且 work_day_statistics 中尚无任何记录，则异步补齐历史日统计 & works.yesterday_*（使用 BaijiahaoWorkDailyStatisticsImportService）
    if (platform === 'baijiahao') {
      try {
        const works = await this.workRepository.find({
          where: { accountId: account.id, platform },
          select: ['id'],
        });
        const workIds = works.map((w) => w.id);
        if (workIds.length > 0) {
          const rows = await AppDataSource.getRepository(WorkDayStatistics)
            .createQueryBuilder('wds')
            .select('DISTINCT wds.work_id', 'workId')
            .where('wds.work_id IN (:...ids)', { ids: workIds })
            .getRawMany();
          const hasStats = new Set<number>(rows.map((r: any) => Number(r.workId)));
          const needInitIds = workIds.filter((id) => !hasStats.has(id));

          if (needInitIds.length > 0) {
            logger.info(
              `[SyncAccountWorks] BJ account ${account.id} has ${needInitIds.length} works without statistics, enqueue bj_work_stats_backfill task.`
            );
            taskQueueService.createTask(account.userId, {
              type: 'bj_work_stats_backfill',
              title: `百家号作品补数（${needInitIds.length}）`,
              accountId: account.id,
              platform: 'baijiahao',
              data: {
                workIds: needInitIds,
              },
            });
          }
        }
      } catch (err) {
        logger.error(
          `[SyncAccountWorks] Failed to enqueue BJ work_day_statistics backfill for account ${account.id}:`,
          err
        );
      }
    }

    // 视频号：如果是新作品且 work_day_statistics 中尚无任何记录，则异步补齐日统计 & works.yesterday_*（使用 WeixinVideoWorkStatisticsImportService）
    if (platform === 'weixin_video') {
      try {
        const works = await this.workRepository.find({
          where: { accountId: account.id, platform },
          select: ['id'],
        });
        const workIds = works.map((w) => w.id);
        if (workIds.length > 0) {
          const rows = await AppDataSource.getRepository(WorkDayStatistics)
            .createQueryBuilder('wds')
            .select('DISTINCT wds.work_id', 'workId')
            .where('wds.work_id IN (:...ids)', { ids: workIds })
            .getRawMany();
          const hasStats = new Set<number>(rows.map((r: any) => Number(r.workId)));
          const needInitIds = workIds.filter((id) => !hasStats.has(id));

          if (needInitIds.length > 0) {
            logger.info(
              `[SyncAccountWorks] WX account ${account.id} has ${needInitIds.length} works without statistics, enqueue wx_work_stats_backfill task.`
            );
            taskQueueService.createTask(account.userId, {
              type: 'wx_work_stats_backfill',
              title: `视频号作品补数（${needInitIds.length}）`,
              accountId: account.id,
              platform: 'weixin_video',
              data: {
                workIds: needInitIds,
              },
            });
          }
        }
      } catch (err) {
        logger.error(
          `[SyncAccountWorks] Failed to enqueue WX work_day_statistics backfill for account ${account.id}:`,
          err
        );
      }
    }

    return {
      syncedCount,
      worksListLength: accountInfo.worksList?.length || 0,
      worksCount: accountInfo.worksCount || 0,
      source: accountInfo.source,
      pythonAvailable: accountInfo.pythonAvailable,
    };
  }

  /**
   * 保存作品每日统计数据
   */
  private async saveWorkDayStatistics(account: PlatformAccount): Promise<void> {
    // 已废弃：同步作品时不再基于 works 表当前累计值，往 work_day_statistics 写“今天快照”。
    // 保留空实现仅为兼容旧调用点，避免影响同步主流程。
    logger.info(
      `[SaveWorkDayStatistics] Skip snapshot-based work_day_statistics when syncing works for account ${account.id} (disabled by design).`
    );
  }

  /**
   * 标准化状态
   */
  private normalizeStatus(status: string): string {
    const statusMap: Record<string, string> = {
      '已发布': 'published',
      '审核中': 'reviewing',
      '未通过': 'rejected',
      '草稿': 'draft',
      'published': 'published',
      'reviewing': 'reviewing',
      'rejected': 'rejected',
      'draft': 'draft',
    };
    return statusMap[status] || 'published';
  }

  /**
   * 解析发布时间
   */
  private parsePublishTime(timeStr: string): Date | null {
    if (!timeStr) return null;

    // 尝试解析各种格式
    // 格式: "2025年12月19日 06:33"
    const match = timeStr.match(/(\d{4})年(\d{1,2})月(\d{1,2})日\s*(\d{1,2}):(\d{2})/);
    if (match) {
      const [, year, month, day, hour, minute] = match;
      return new Date(parseInt(year), parseInt(month) - 1, parseInt(day), parseInt(hour), parseInt(minute));
    }

    // 尝试直接解析
    const date = new Date(timeStr);
    if (!isNaN(date.getTime())) {
      return date;
    }

    return null;
  }

  /**
   * 删除本地作品记录
   */
  async deleteWork(userId: number, workId: number): Promise<void> {
    const work = await this.workRepository.findOne({
      where: { id: workId, userId },
    });

    if (!work) {
      throw new AppError('作品不存在', HTTP_STATUS.NOT_FOUND, ERROR_CODES.NOT_FOUND);
    }

    // 先删除关联的评论和作品每日统计
    await AppDataSource.getRepository(Comment).delete({ workId });
    await this.workDayStatisticsService.deleteByWorkId(workId);

    // 删除作品
    await this.workRepository.delete(workId);

    logger.info(`Deleted work ${workId} for user ${userId}`);
  }

  /**
   * 删除平台上的作品
   * @returns 包含 accountId 用于后续刷新作品列表
   */
  async deletePlatformWork(
    userId: number,
    workId: number,
    onCaptchaRequired?: (captchaInfo: { taskId: string }) => Promise<string>
  ): Promise<{ success: boolean; errorMessage?: string; accountId?: number }> {
    const work = await this.workRepository.findOne({
      where: { id: workId, userId },
      relations: ['account'],
    });

    if (!work) {
      throw new AppError('作品不存在', HTTP_STATUS.NOT_FOUND, ERROR_CODES.NOT_FOUND);
    }

    const account = await this.accountRepository.findOne({
      where: { id: work.accountId },
    });

    if (!account || !account.cookieData) {
      throw new AppError('账号不存在或未登录', HTTP_STATUS.BAD_REQUEST, ERROR_CODES.ACCOUNT_NOT_FOUND);
    }

    // 解密 Cookie
    let decryptedCookies: string;
    try {
      decryptedCookies = CookieManager.decrypt(account.cookieData);
    } catch {
      decryptedCookies = account.cookieData;
    }

    // 根据平台调用对应的删除方法
    if (account.platform === 'douyin') {
      const { DouyinAdapter } = await import('../automation/platforms/douyin.js');
      const adapter = new DouyinAdapter();

      const result = await adapter.deleteWork(decryptedCookies, work.platformVideoId, onCaptchaRequired);

      if (result.success) {
        // 更新作品状态为已删除
        await this.workRepository.update(workId, { status: 'deleted' });
        logger.info(`Platform work ${workId} deleted successfully`);
      }

      return { ...result, accountId: account.id };
    }

    if (account.platform === 'xiaohongshu') {
      const { XiaohongshuAdapter } = await import('../automation/platforms/xiaohongshu.js');
      const adapter = new XiaohongshuAdapter();

      const result = await adapter.deleteWork(decryptedCookies, work.platformVideoId, onCaptchaRequired);

      if (result.success) {
        // 更新作品状态为已删除
        await this.workRepository.update(workId, { status: 'deleted' });
        logger.info(`Platform work ${workId} (xiaohongshu) deleted successfully`);
      }

      return { ...result, accountId: account.id };
    }

    return { success: false, errorMessage: '暂不支持该平台删除功能' };
  }

  /**
   * 将 cookie 字符串解析为 cookie 列表
   */
  private parseCookieString(cookieString: string, platform: PlatformType): {
    name: string;
    value: string;
    domain: string;
    path: string;
  }[] {
    // 获取平台对应的域名
    const domainMap: Record<string, string> = {
      douyin: '.douyin.com',
      kuaishou: '.kuaishou.com',
      xiaohongshu: '.xiaohongshu.com',
      weixin_video: '.qq.com',
      bilibili: '.bilibili.com',
      toutiao: '.toutiao.com',
      baijiahao: '.baidu.com',
      qie: '.qq.com',
      dayuhao: '.alibaba.com',
    };

    const domain = domainMap[platform] || `.${platform}.com`;

    // 解析 "name=value; name2=value2" 格式的 cookie 字符串
    const cookies: { name: string; value: string; domain: string; path: string }[] = [];

    const pairs = cookieString.split(';');
    for (const pair of pairs) {
      const trimmed = pair.trim();
      if (!trimmed) continue;

      const eqIndex = trimmed.indexOf('=');
      if (eqIndex === -1) continue;

      const name = trimmed.substring(0, eqIndex).trim();
      const value = trimmed.substring(eqIndex + 1).trim();

      if (name && value) {
        cookies.push({
          name,
          value,
          domain,
          path: '/',
        });
      }
    }

    return cookies;
  }

  /**
   * 格式化作品
   */
  private formatWork(work: Work): WorkType {
    return {
      id: work.id,
      accountId: work.accountId,
      platform: work.platform as PlatformType,
      platformVideoId: work.platformVideoId,
      title: work.title,
      description: work.description || undefined,
      coverUrl: work.coverUrl,
      videoUrl: work.videoUrl || undefined,
      duration: work.duration,
      status: work.status as 'published' | 'reviewing' | 'rejected' | 'draft' | 'deleted',
      publishTime: work.publishTime?.toISOString() || '',
      playCount: work.playCount,
      likeCount: work.likeCount,
      commentCount: work.commentCount,
      shareCount: work.shareCount,
      collectCount: work.collectCount,
      yesterdayPlayCount: work.yesterdayPlayCount,
      yesterdayLikeCount: work.yesterdayLikeCount,
      yesterdayCommentCount: work.yesterdayCommentCount,
      yesterdayShareCount: work.yesterdayShareCount,
      yesterdayCollectCount: work.yesterdayCollectCount,
      yesterdayRecommendCount: (work as any).yesterdayRecommendCount,
      yesterdayFansIncrease: work.yesterdayFansIncrease,
      yesterdayCoverClickRate: work.yesterdayCoverClickRate,
      yesterdayAvgWatchDuration: work.yesterdayAvgWatchDuration,
      yesterdayTotalWatchDuration: work.yesterdayTotalWatchDuration,
      yesterdayCompletionRate: work.yesterdayCompletionRate,
      yesterdayTwoSecondExitRate: work.yesterdayTwoSecondExitRate,
      yesterdayCompletionRate5s: work.yesterdayCompletionRate5s,
      yesterdayExposureCount: work.yesterdayExposureCount,
      createdAt: work.createdAt.toISOString(),
      updatedAt: work.updatedAt.toISOString(),
    };
  }

  private async dedupeWeixinVideoWorks(accountId: number): Promise<void> {
    const works = await this.workRepository.find({ where: { accountId } });
    const groups = new Map<string, Work[]>();

    for (const w of works) {
      if (!w.title || !w.publishTime) continue;
      const key = `${w.title}__${w.publishTime.toISOString()}`;
      const list = groups.get(key);
      if (list) list.push(w);
      else groups.set(key, [w]);
    }

    for (const list of groups.values()) {
      if (list.length <= 1) continue;

      list.sort((a, b) => {
        const qa = a.platformVideoId.startsWith('weixin_video_') ? 0 : 1;
        const qb = b.platformVideoId.startsWith('weixin_video_') ? 0 : 1;
        if (qa !== qb) return qb - qa;
        return (b.updatedAt?.getTime?.() || 0) - (a.updatedAt?.getTime?.() || 0);
      });

      const keep = list[0];
      for (const dup of list.slice(1)) {
        if (dup.id === keep.id) continue;
        await this.commentRepository.update({ workId: dup.id }, { workId: keep.id });
        await this.workRepository.delete(dup.id);
      }
    }
  }
}

export const workService = new WorkService();