multi-platform-media-manage/server/src/scripts/check-trend-data.ts

#!/usr/bin/env tsx
/**
 * 排查数据趋势无数据问题
 * 检查 user_day_statistics 表、platform_accounts 表的数据情况
 *
 * 运行: cd server && pnpm exec tsx src/scripts/check-trend-data.ts
 */
import { initDatabase, AppDataSource } from '../models/index.js';

async function main() {
  console.log('========== 数据趋势排查 ==========\n');

  try {
    await initDatabase();
    console.log('✓ 数据库连接成功\n');

    // 1. 检查 user_day_statistics 表
    const udsCount = await AppDataSource.query(
      'SELECT COUNT(*) as cnt FROM user_day_statistics'
    );
    console.log('1. user_day_statistics 表:');
    console.log(`   总记录数: ${udsCount[0].cnt}`);

    if (Number(udsCount[0].cnt) === 0) {
      console.log('\n   ⚠ 表为空！数据来自定时任务导入，需要执行以下操作之一：');
      console.log('   - 等待每天 12:00 定时任务自动导入');
      console.log('   - 在「发布」页面为小红书/抖音等账号执行「导入近30天数据」');
      console.log('   - 或运行: pnpm xhs:import (小红书)');
    } else {
      const byAccount = await AppDataSource.query(`
        SELECT account_id, COUNT(*) as cnt, 
               MIN(record_date) as min_date, MAX(record_date) as max_date
        FROM user_day_statistics 
        GROUP BY account_id
      `);
      console.log(`   按账号分布: ${JSON.stringify(byAccount, null, 2)}`);
    }

    // 2. 检查 platform_accounts 表
    const accounts = await AppDataSource.query(`
      SELECT pa.id, pa.user_id, pa.platform, pa.account_name 
      FROM platform_accounts pa
      ORDER BY pa.user_id, pa.platform
    `);
    console.log('\n2. platform_accounts 表:');
    console.log(`   总账号数: ${accounts.length}`);
    if (accounts.length > 0) {
      accounts.forEach((a: any) => {
        console.log(`   - id=${a.id} userId=${a.user_id} platform=${a.platform} name=${a.account_name}`);
      });
    } else {
      console.log('   ⚠ 没有账号！请先在「账号管理」添加平台账号');
    }

    // 3. 检查账号是否有对应的 user_day_statistics
    if (accounts.length > 0 && Number(udsCount[0].cnt) > 0) {
      const accountIds = accounts.map((a: any) => a.id);
      const hasData = await AppDataSource.query(
        `SELECT account_id FROM user_day_statistics WHERE account_id IN (?) GROUP BY account_id`,
        [accountIds]
      );
      const accountIdsWithData = new Set(hasData.map((r: any) => r.account_id));
      const missing = accounts.filter((a: any) => !accountIdsWithData.has(a.id));
      if (missing.length > 0) {
        console.log('\n3. 以下账号没有 user_day_statistics 数据:');
        missing.forEach((a: any) => {
          console.log(`   - id=${a.id} platform=${a.platform} ${a.account_name}`);
        });
      }
    }

    console.log('\n========== 排查完成 ==========');
  } catch (err) {
    console.error('错误:', err);
    process.exit(1);
  } finally {
    await AppDataSource.destroy();
  }
}

main();