tihao-ai/claude-code-tihao-sourcing/scripts/export-software-table.js

#!/usr/bin/env node
const fs = require('fs');
const path = require('path');
const { SOFTWARE_TABLE_HEADER } = require('../mcp/src/features/tihao-sourcing/report');

const HEADER = SOFTWARE_TABLE_HEADER;

const PLATFORM_NAMES = {
  xiaohongshu: '小红书',
  xhs: '小红书',
  douyin: '抖音',
  dy: '抖音',
  tiktok: 'TikTok'
};

const DEDUPE_RULE = '软件端交付表按全局博主唯一去重：同一平台 + 规范化主页链接不重复；没有主页链接时使用同一平台 + 规范化博主名称；同分保留证据更完整、综合分更高的记录。';

function main() {
  const outputsRoot = path.resolve(arg('--outputs') || arg('--output-root') || path.join(__dirname, '..', 'outputs'));
  if (process.argv.includes('--scan')) {
    scanManualReviewSamples(outputsRoot);
    return;
  }

  const input = arg('--input') || latestManualReviewSample(outputsRoot);
  const output = arg('--output') || path.join(path.dirname(input), 'software-client-list.dedup.csv');
  const rows = parseCsv(fs.readFileSync(input, 'utf8').replace(/^\uFEFF/, ''));
  if (rows.length < 1) throw new Error(`CSV 为空：${input}`);

  const sourceHeader = rows[0].map(stripBom);
  const sourceRows = rows.slice(1).filter(row => row.some(cell => String(cell || '').trim()));
  const normalized = sourceRows.map(row => normalizeRow(sourceHeader, row));
  const { rows: deduped, duplicateCount, duplicateSamples } = dedupeRows(normalized);
  const ranked = rankWithinBrief(deduped);
  const duplicateAudit = auditDuplicates(ranked);
  const finalDuplicateGroupCount = countDuplicateGroups(duplicateAudit);

  fs.mkdirSync(path.dirname(output), { recursive: true });
  fs.writeFileSync(output, '\uFEFF' + toCsv([HEADER, ...ranked.map(row => HEADER.map(key => row[key] ?? ''))]));

  const summary = {
    generatedAt: new Date().toISOString(),
    input,
    output,
    sourceRows: sourceRows.length,
    outputRows: ranked.length,
    sourceDuplicateRemovedCount: duplicateCount,
    finalDuplicateGroupCount,
    duplicateCountMeaning: 'source_duplicate_removed_count',
    duplicateCount,
    duplicateSamples: duplicateSamples.slice(0, 50),
    duplicateAudit,
    header: HEADER,
    rankContinuous: ranksContinuousWithinBrief(ranked),
    dedupeRule: DEDUPE_RULE
  };
  const summaryPath = output.replace(/\.csv$/i, '.summary.json');
  fs.writeFileSync(summaryPath, JSON.stringify(summary, null, 2));
  console.log(JSON.stringify({ ...summary, summaryPath }, null, 2));
}

function scanManualReviewSamples(root) {
  const files = listFiles(root).filter(file => path.basename(file) === 'manual-review-sample.csv');
  const summaries = files.map(file => {
    const rows = parseCsv(fs.readFileSync(file, 'utf8').replace(/^\uFEFF/, ''));
    const sourceHeader = (rows[0] || []).map(stripBom);
    const sourceRows = rows.slice(1).filter(row => row.some(cell => String(cell || '').trim()));
    const normalized = sourceRows.map(row => normalizeRow(sourceHeader, row));
    const { rows: deduped, duplicateCount, duplicateSamples } = dedupeRows(normalized);
    const ranked = rankWithinBrief(deduped);
    const duplicateAudit = auditDuplicates(ranked);
    const finalDuplicateGroupCount = countDuplicateGroups(duplicateAudit);
    return {
      file,
      sourceRows: sourceRows.length,
      outputRows: ranked.length,
      sourceDuplicateRemovedCount: duplicateCount,
      finalDuplicateGroupCount,
      duplicateCountMeaning: 'source_duplicate_removed_count',
      duplicateCount,
      duplicateSamples: duplicateSamples.slice(0, 10),
      duplicateAudit,
      rankContinuous: ranksContinuousWithinBrief(ranked),
      dedupeRule: DEDUPE_RULE,
      mtime: fs.statSync(file).mtime.toISOString()
    };
  });
  summaries.sort((a, b) => b.duplicateCount - a.duplicateCount || b.sourceRows - a.sourceRows);
  console.log(JSON.stringify(summaries.filter(item => item.duplicateCount > 0).slice(0, 50), null, 2));
}

function arg(name) {
  const index = process.argv.indexOf(name);
  return index >= 0 ? process.argv[index + 1] : '';
}

function latestManualReviewSample(root) {
  const files = listFiles(root).filter(file => path.basename(file) === 'manual-review-sample.csv');
  const candidates = files
    .filter(isRefreshCandidateSample)
    .map(file => ({
      file,
      sourceRows: manualReviewSampleRowCount(file),
      mtimeMs: fs.statSync(file).mtimeMs
    }))
    .filter(item => item.sourceRows > 0)
    .sort((a, b) => b.sourceRows - a.sourceRows || b.mtimeMs - a.mtimeMs);
  if (!candidates.length) throw new Error(`未在 ${root} 下找到非空的 overnight-quality manual-review-sample.csv`);
  return candidates[0].file;
}

function isRefreshCandidateSample(file) {
  const normalized = file.replace(/\\/g, '/').toLowerCase();
  const parent = path.basename(path.dirname(file));
  return /^overnight-quality-\d+$/.test(parent) && !normalized.includes('smoke');
}

function manualReviewSampleRowCount(file) {
  const rows = parseCsv(fs.readFileSync(file, 'utf8').replace(/^\uFEFF/, ''));
  return rows.slice(1).filter(row => row.some(cell => String(cell || '').trim())).length;
}

function listFiles(dir) {
  if (!fs.existsSync(dir)) return [];
  return fs.readdirSync(dir, { withFileTypes: true }).flatMap(entry => {
    const full = path.join(dir, entry.name);
    return entry.isDirectory() ? listFiles(full) : [full];
  });
}

function normalizeRow(header, row) {
  const source = Object.fromEntries(header.map((key, index) => [key, row[index] || '']));
  const rank = number(pick(source, ['序号', '排名', 'rank']));
  const name = pick(source, ['账号名称', '博主名称', 'displayName', 'name']);
  return {
    'brief编号': pick(source, ['brief编号', 'briefId']),
    '策略': pick(source, ['策略', 'variant']),
    '序号': rank,
    '排名': rank,
    '目标城市': pick(source, ['目标城市', 'targetCity', 'city']),
    '预算规则': pick(source, ['预算规则', 'budgetRule']),
    '命中价格类型': pick(source, ['命中价格类型', 'matchedPriceType']),
    '命中价格': pick(source, ['命中价格', 'matchedPrice']),
    '平台': platformName(pick(source, ['平台', 'platform'])),
    '账号名称': name,
    '博主名称': name,
    '账号ID': pick(source, ['账号ID', 'platformUserId', 'userId']),
    '主页链接': pick(source, ['主页链接', 'profileUrl']),
    '粉丝数': number(pick(source, ['粉丝数', 'fansCount'])),
    '图文报价': number(pick(source, ['图文报价', 'imagePrice', 'picturePrice'])),
    '视频报价': number(pick(source, ['视频报价', 'videoPrice'])),
    '最低报价': number(pick(source, ['最低报价', 'minPrice'])),
    '报价状态': pick(source, ['报价状态', 'quoteStatus']),
    '地区/定位': pick(source, ['地区/定位', 'location', 'city']),
    '内容标签': pick(source, ['内容标签', 'contentTags']),
    '人设标签': pick(source, ['人设标签', 'personaTags']),
    '推荐等级': pick(source, ['推荐等级', 'recommendStatus']),
    '综合分': number(pick(source, ['综合分', 'score'])),
    'brief匹配分': number(pick(source, ['brief匹配分', 'briefFitScore'])),
    '参考风格分': number(pick(source, ['参考风格分', 'referenceStyleFitScore'])),
    '主页证据分': number(pick(source, ['主页证据分', 'recentContentFitScore', 'homepageEvidenceScore'])),
    '视觉质感分': number(pick(source, ['视觉质感分', 'visualQualityScore'])),
    '调性一致分': number(pick(source, ['调性一致分', 'toneConsistencyScore'])),
    '证据加分': number(pick(source, ['证据加分', 'evidenceBoost', 'evidenceScoreBoost'])),
    '证据风险扣分': number(pick(source, ['证据风险扣分', 'evidenceRiskPenalty'])),
    '推荐理由': cleanBusinessText(pick(source, ['推荐理由', 'reason'])),
    '风险提示': cleanBusinessText(pick(source, ['风险提示', 'riskNote'])),
    '蒲公英判断': pick(source, ['蒲公英判断', 'pugongyingJudgement']),
    '图文CPM': number(pick(source, ['图文CPM', 'imageCpm', 'pictureCpm'])),
    '视频CPM': number(pick(source, ['视频CPM', 'videoCpm'])),
    '图文CPE': number(pick(source, ['图文CPE', 'imageCpe', 'pictureCpe'])),
    '视频CPE': number(pick(source, ['视频CPE', 'videoCpe'])),
    '商单数': number(pick(source, ['商单数', 'commercialOrderCount'])),
    '近30天商单数': number(pick(source, ['近30天商单数', 'commercialOrderCount30d'])),
    '商单阅读中位数': number(pick(source, ['商单阅读中位数', 'commercialReadMedian'])),
    '商单互动中位数': number(pick(source, ['商单互动中位数', 'commercialInteractionMedian'])),
    '近30天商单曝光中位数': number(pick(source, ['近30天商单曝光中位数', 'commercialExposureMedian30d'])),
    '粉丝30日增长率': pick(source, ['粉丝30日增长率', 'fanGrowthRate30d']),
    '48h邀约回复率': pick(source, ['48h邀约回复率', 'inviteReplyRate48h']),
    '是否低活跃': pick(source, ['是否低活跃', 'lowActivity']),
    '数据来源': pick(source, ['数据来源', 'sourceProvider']),
    '人工复核标签': pick(source, ['人工复核标签', 'manualLabel']),
    '人工复核项': pick(source, ['人工复核项', 'manualReviewFields'])
  };
}

function dedupeRows(rows) {
  const best = new Map();
  const duplicateSamples = [];
  for (const row of rows) {
    const key = dedupeKey(row);
    const previous = best.get(key);
    if (previous) {
      const kept = priority(row) > priority(previous) ? row : previous;
      const dropped = kept === row ? previous : row;
      duplicateSamples.push({
        key,
        keptStrategy: kept['策略'],
        droppedStrategy: dropped['策略'],
        keptName: kept['博主名称'],
        droppedName: dropped['博主名称'],
        profileUrl: kept['主页链接'] || dropped['主页链接']
      });
    }
    if (!previous || priority(row) > priority(previous)) best.set(key, row);
  }

  const outputRows = [...best.values()].sort((a, b) =>
    String(a['brief编号']).localeCompare(String(b['brief编号']), 'zh-Hans-CN') ||
    Number(b['综合分']) - Number(a['综合分']) ||
    Number(b['brief匹配分']) - Number(a['brief匹配分']) ||
    String(a['博主名称']).localeCompare(String(b['博主名称']), 'zh-Hans-CN')
  );
  return { rows: outputRows, duplicateCount: duplicateSamples.length, duplicateSamples };
}

function rankWithinBrief(rows) {
  const counters = new Map();
  return rows.map(row => {
    const briefId = String(row['brief编号'] || '未命名brief');
    const next = (counters.get(briefId) || 0) + 1;
    counters.set(briefId, next);
    return { ...row, '序号': next, '排名': next };
  });
}

function ranksContinuousWithinBrief(rows) {
  const counters = new Map();
  for (const row of rows) {
    const briefId = String(row['brief编号'] || '未命名brief');
    const expected = (counters.get(briefId) || 0) + 1;
    if (Number(row['排名']) !== expected) return false;
    counters.set(briefId, expected);
  }
  return true;
}

function auditDuplicates(rows) {
  return {
    sameBriefUrl: duplicateGroups(rows, row => {
      const url = normalizeUrl(row['主页链接']);
      return url ? `${normalizeKey(row['brief编号'])}|${normalizeKey(row['平台'])}|${url}` : '';
    }),
    sameBriefName: duplicateGroups(rows, row => `${normalizeKey(row['brief编号'])}|${normalizeKey(row['平台'])}|${normalizeName(row['博主名称'])}`),
    globalUrl: duplicateGroups(rows, row => normalizeUrl(row['主页链接'])),
    globalNamePlatform: duplicateGroups(rows, row => `${normalizeKey(row['平台'])}|${normalizeName(row['博主名称'])}`)
  };
}

function countDuplicateGroups(duplicateAudit) {
  if (!duplicateAudit) return null;
  return Object.values(duplicateAudit).reduce((sum, groups) => sum + (Array.isArray(groups) ? groups.length : 0), 0);
}

function duplicateGroups(rows, keyFn) {
  const groups = new Map();
  for (const row of rows) {
    const key = keyFn(row);
    if (!key) continue;
    if (!groups.has(key)) groups.set(key, []);
    groups.get(key).push({
      brief编号: row['brief编号'],
      平台: row['平台'],
      排名: row['排名'],
      博主名称: row['博主名称'],
      主页链接: row['主页链接']
    });
  }
  return [...groups.entries()]
    .filter(([, values]) => values.length > 1)
    .map(([key, values]) => ({ key, count: values.length, rows: values }));
}

function dedupeKey(row) {
  const platform = normalizeKey(row['平台']);
  const url = normalizeUrl(row['主页链接']);
  const name = normalizeName(row['博主名称']);
  return url ? `${platform}|url:${url}` : `${platform}|name:${name}`;
}

function priority(row) {
  const evidenceCompleteness = [
    row['主页链接'],
    row['推荐理由'],
    row['风险提示'],
    Number(row['主页证据分']) > 0 ? 'homepage' : '',
    Number(row['视觉质感分']) > 0 ? 'visual' : '',
    Number(row['调性一致分']) > 0 ? 'tone' : ''
  ].filter(Boolean).length;
  return evidenceCompleteness * 1000000 +
    Number(row['综合分'] || 0) * 10000 +
    Number(row['brief匹配分'] || 0) * 100 +
    Number(row['参考风格分'] || 0);
}

function pick(source, keys) {
  for (const key of keys) {
    if (source[key] !== undefined && source[key] !== '') return source[key];
  }
  return '';
}

function number(value) {
  const numeric = Number(value);
  return Number.isFinite(numeric) ? numeric : 0;
}

function platformName(value) {
  const text = String(value || '').trim();
  const key = text.toLowerCase();
  return PLATFORM_NAMES[key] || text;
}

function cleanBusinessText(value) {
  return dedupeAdjacentText(
    String(value || '')
      .replace(/标签覆盖\s*([^，。；;]+)/g, (_, terms) => `标签覆盖 ${dedupeTerms(terms)}`)
      .trim()
  );
}

function dedupeTerms(value) {
  const seen = new Set();
  return String(value || '')
    .split(/[、,，/]/)
    .map(term => term.trim())
    .filter(Boolean)
    .filter(term => {
      const key = normalizeName(term);
      if (seen.has(key)) return false;
      seen.add(key);
      return true;
    })
    .join('、');
}

function dedupeAdjacentText(text) {
  const parts = String(text || '').split(/([，。；;])/);
  const output = [];
  let previousPhrase = '';
  for (let index = 0; index < parts.length; index += 2) {
    const phrase = (parts[index] || '').trim();
    const delimiter = parts[index + 1] || '';
    if (!phrase) continue;
    const key = normalizeName(phrase);
    if (key !== previousPhrase) {
      output.push(phrase + delimiter);
      previousPhrase = key;
    }
  }
  return output.join('').trim();
}

function stripBom(value) {
  return String(value || '').replace(/^\uFEFF/, '');
}

function normalizeKey(value) {
  return String(value || '').trim().toLowerCase();
}

function normalizeName(value) {
  return String(value || '')
    .trim()
    .toLowerCase()
    .replace(/\s+/g, '')
    .replace(/[（(].*?[)）]/g, '')
    .replace(/[^\p{L}\p{N}\u4e00-\u9fa5]/gu, '');
}

function normalizeUrl(value) {
  return String(value || '')
    .trim()
    .toLowerCase()
    .replace(/^http:\/\//, 'https://')
    .replace(/^https:\/\/m\.xiaohongshu\.com\//, 'https://www.xiaohongshu.com/')
    .replace(/^https:\/\/www\.iesdouyin\.com\//, 'https://www.douyin.com/')
    .replace(/[?#].*$/, '')
    .replace(/\/$/, '');
}

function parseCsv(text) {
  const rows = [];
  let row = [];
  let cell = '';
  let quoted = false;
  for (let index = 0; index < text.length; index += 1) {
    const char = text[index];
    if (char === '\r') continue;
    if (char === '"' && quoted && text[index + 1] === '"') {
      cell += '"';
      index += 1;
    } else if (char === '"') {
      quoted = !quoted;
    } else if (char === ',' && !quoted) {
      row.push(cell);
      cell = '';
    } else if (char === '\n' && !quoted) {
      row.push(cell);
      rows.push(row);
      row = [];
      cell = '';
    } else {
      cell += char;
    }
  }
  if (cell || row.length) {
    row.push(cell);
    rows.push(row);
  }
  return rows;
}

function toCsv(rows) {
  return rows.map(row => row.map(csvCell).join(',')).join('\n');
}

function csvCell(value) {
  const text = String(value ?? '');
  return /[",\n]/.test(text) ? `"${text.replace(/"/g, '""')}"` : text;
}

main();