tihao-ai/claude-code-tihao-sourcing/scripts/case-template-import.js

#!/usr/bin/env node
const fs = require('fs');
const path = require('path');
const { readXlsxRows } = require('../mcp/src/core/files');

// case_template.xlsx 的字段（中文表头 → 内部 key）
const FIELD_MAP = {
  '案例ID': 'id',
  '品类': 'category',
  '投放目标': 'goal',
  '目标人群': 'audience',
  '品牌调性': 'brandTone',
  '平台': 'platform',
  '选中账号特征': 'selectedAccountProfile',
  '结果标签': 'outcomeLabel',
  '关键原因': 'keyReason',
  '客户反馈': 'customerFeedback',
  '执行数据': 'executionData'
};

// 结果标签枚举（说明行 r3 的值是「爆文 / 达标 / 一般 / 翻车」，含斜杠，会被此白名单排除）
const OUTCOME_LABELS = ['爆文', '达标', '一般', '翻车'];

// 平台中文 → 内部代码
const PLATFORM_CODE = {
  '小红书': 'xiaohongshu',
  'xiaohongshu': 'xiaohongshu',
  'xhs': 'xiaohongshu',
  '抖音': 'douyin',
  'douyin': 'douyin',
  'b站': 'bilibili',
  'bilibili': 'bilibili'
};

// 结果标签 → outcome 方向
const OUTCOME_BY_LABEL = {
  '爆文': 'positive',
  '达标': 'positive',
  '一般': 'neutral',
  '翻车': 'negative'
};

// 结果标签 → 客户选择（用于 history 数据集）
const CUSTOMER_DECISION_BY_LABEL = {
  '爆文': '客户选中',
  '达标': '客户选中',
  '一般': '待客户反馈',
  '翻车': '客户拒绝'
};

// 从关键原因抽取的信号标签（用于给案例打标签，便于后续检索同类案例 / 沉淀规则）
const SIGNAL_DICT = [
  { tag: '互动率', words: ['互动率', '互动', '赞藏比', '赞评藏', '点赞', '收藏'] },
  { tag: '垂直度', words: ['垂直度', '垂直', '垂直度92', '垂直度35'] },
  { tag: '画像匹配', words: ['画像匹配', '画像', '粉丝画像', '匹配'] },
  { tag: '调性匹配', words: ['调性', '成分党', '硬核测评', '测评', '素人', '生活化', '科普'] },
  { tag: '内容质量', words: ['内容', '疲劳', '干货', '深度', '创意'] },
  { tag: '数据虚胖', words: ['虚胖', '水分', '刷', '重复'] },
  { tag: '转化/ROI', words: ['转化', 'ROI', 'roi', '搜索', '占位', 'GMV', '下单'] },
  { tag: '负面反馈', words: ['负面', '差评', '投诉', '无转化', '掉粉', '翻车'] },
  { tag: '报价匹配', words: ['报价', '预算', 'CPM', 'CPE', '性价比', '成本'] },
  { tag: '人群契合', words: ['契合', '对口', '精准', '人群', '年龄'] }
];

function main() {
  const args = parseArgs(process.argv.slice(2));
  const input = path.resolve(
    args.input || args.xlsx || process.env.TIHAO_CASE_TEMPLATE ||
    path.join(__dirname, '..', '..', 'docs', '20260813 - AI提号规则及喂养资料', '历史选号案例', 'case_template.xlsx')
  );
  const outputDir = path.resolve(args.output || path.join(path.dirname(input), 'case-import'));
  if (!fs.existsSync(input)) throw new Error(`case_template.xlsx 不存在：${input}`);

  const cases = readCaseTemplate(input);
  if (cases.length === 0) throw new Error(`未在 ${input} 里解析到任何案例数据行`);

  fs.mkdirSync(outputDir, { recursive: true });
  // 技能包内打包路径：随 npm 包分发（package.json files 含 docs/），运行时 few-shot-cases.js 优先读这里
  const pkgCaseLibraryDir = path.join(__dirname, '..', 'docs', 'case-library');
  fs.mkdirSync(pkgCaseLibraryDir, { recursive: true });
  const files = {
    fewshotCases: path.join(outputDir, 'fewshot-cases.json'),
    insights: path.join(outputDir, 'case-insights.md'),
    historyCsv: path.join(outputDir, 'history-data-template.csv'),
    summary: path.join(outputDir, 'case-import-summary.json'),
    pkgFewshotCases: path.join(pkgCaseLibraryDir, 'fewshot-cases.json')
  };

  const fewshotPayload = buildFewshotCases(cases, input);
  fs.writeFileSync(files.fewshotCases, JSON.stringify(fewshotPayload, null, 2), 'utf8');
  fs.writeFileSync(files.pkgFewshotCases, JSON.stringify(fewshotPayload, null, 2), 'utf8');
  fs.writeFileSync(files.insights, withBom(buildInsights(cases)), 'utf8');
  fs.writeFileSync(files.historyCsv, withBom(buildHistoryCsv(cases)), 'utf8');

  const summary = {
    input,
    outputDir,
    generatedAt: new Date().toISOString(),
    caseCount: cases.length,
    positive: cases.filter(item => item.outcome === 'positive').length,
    neutral: cases.filter(item => item.outcome === 'neutral').length,
    negative: cases.filter(item => item.outcome === 'negative').length,
    files
  };
  fs.writeFileSync(files.summary, JSON.stringify(summary, null, 2), 'utf8');
  console.log(JSON.stringify(summary, null, 2));
}

// 读取 xlsx，定位表头行，跳过说明行，返回映射后的案例数组
function readCaseTemplate(file) {
  return parseCasesFromSheets(readXlsxRows(file));
}

function parseCasesFromSheets(sheets) {
  const cases = [];
  for (const sheet of sheets) {
    const headerIndex = sheet.rows.findIndex(row => hasHeader(row));
    if (headerIndex < 0) continue;
    const header = sheet.rows[headerIndex];
    for (let index = headerIndex + 1; index < sheet.rows.length; index += 1) {
      const raw = sheet.rows[index];
      const mapped = mapRow(header, raw);
      if (!mapped || !OUTCOME_LABELS.includes(mapped.outcomeLabel)) continue; // 跳过说明行和空行
      cases.push(mapped);
    }
  }
  return cases;
}

function hasHeader(row) {
  const text = row.map(item => String(item || '')).join('|');
  return text.includes('案例ID') && text.includes('结果标签');
}

function mapRow(header, raw) {
  const record = {};
  header.forEach((label, index) => {
    const key = FIELD_MAP[String(label || '').trim()];
    if (key) record[key] = String(raw[index] || '').trim();
  });
  if (!record.id) return null;
  const platform = normalizePlatform(record.platform);
  const outcome = OUTCOME_BY_LABEL[record.outcomeLabel] || 'neutral';
  return {
    ...record,
    platform,
    platformLabel: labelPlatform(platform),
    outcome,
    signalTags: extractSignalTags(record.keyReason)
  };
}

function buildFewshotCases(cases, source) {
  return {
    version: 1,
    source,
    generatedAt: new Date().toISOString(),
    purpose: '案例喂养 few-shot 库：品类+投放目标+人群+调性+平台 -> 选中账号特征 -> 结果标签。用于下次选号时做正/负样本参照。',
    cases: cases.map(item => ({
      id: item.id,
      category: item.category,
      goal: item.goal,
      audience: item.audience,
      brandTone: item.brandTone,
      platform: item.platform,
      platformLabel: item.platformLabel,
      selectedAccountProfile: item.selectedAccountProfile,
      outcomeLabel: item.outcomeLabel,
      outcome: item.outcome,
      keyReason: item.keyReason,
      customerFeedback: item.customerFeedback,
      executionData: item.executionData,
      signalTags: item.signalTags
    }))
  };
}

function buildInsights(cases) {
  const positive = cases.filter(item => item.outcome === 'positive');
  const neutral = cases.filter(item => item.outcome === 'neutral');
  const negative = cases.filter(item => item.outcome === 'negative');
  const lines = [];
  lines.push('# 案例归因洞察');
  lines.push('');
  lines.push(`> 由 case_template.xlsx 自动生成，共 ${cases.length} 条案例（正 ${positive.length} / 中性 ${neutral.length} / 负 ${negative.length}）。`);
  lines.push('> 用途：把每单执行结果沉淀成可复用信号，人工复核后升级到 experience-rules 或 preference memory。');
  lines.push('');

  lines.push('## 正样本共性（爆文 / 达标）');
  lines.push('');
  if (positive.length) {
    positive.forEach(item => lines.push(`- ${item.id}｜${item.category}｜${item.brandTone}｜「${item.keyReason}」`));
    const tags = countTags(positive);
    lines.push('');
    lines.push('成功信号频次：');
    lines.push('');
    lines.push('| 信号 | 次数 |');
    lines.push('| --- | --- |');
    Object.entries(tags).sort((a, b) => b[1] - a[1]).forEach(([tag, count]) => lines.push(`| ${tag} | ${count} |`));
  } else {
    lines.push('暂无正样本。');
  }
  lines.push('');

  lines.push('## 负样本共性（翻车）');
  lines.push('');
  if (negative.length) {
    negative.forEach(item => lines.push(`- ${item.id}｜${item.category}｜${item.platformLabel}｜「${item.keyReason}」→ ${item.customerFeedback}`));
    const tags = countTags(negative);
    lines.push('');
    lines.push('失败信号频次：');
    lines.push('');
    lines.push('| 信号 | 次数 |');
    lines.push('| --- | --- |');
    Object.entries(tags).sort((a, b) => b[1] - a[1]).forEach(([tag, count]) => lines.push(`| ${tag} | ${count} |`));
  } else {
    lines.push('暂无负样本。');
  }
  lines.push('');

  lines.push('## 建议沉淀的规则');
  lines.push('');
  const suggestions = buildRuleSuggestions(cases);
  if (suggestions.length) {
    suggestions.forEach(item => lines.push(`- ${item}`));
  } else {
    lines.push('- 案例量不足，暂无高频信号可沉淀。建议按模板每天补充案例后重跑本导入器。');
  }
  return lines.join('\n');
}

function buildRuleSuggestions(cases) {
  const positiveTags = countTags(cases.filter(item => item.outcome === 'positive'));
  const negativeTags = countTags(cases.filter(item => item.outcome === 'negative'));
  const suggestions = [];
  const negativeEntries = Object.entries(negativeTags).sort((a, b) => b[1] - a[1]);
  for (const [tag, count] of negativeEntries) {
    if (count >= 2) suggestions.push(`负样本信号「${tag}」出现 ${count} 次，建议在 ranker 风险扣分或 experience-rules 里加入对应剔除/降级规则。`);
  }
  const positiveEntries = Object.entries(positiveTags).sort((a, b) => b[1] - a[1]);
  for (const [tag, count] of positiveEntries) {
    if (count >= 2) suggestions.push(`正样本信号「${tag}」出现 ${count} 次，建议在 ranker 评分或 brief-parser 风格词表里加入对应加分项。`);
  }
  return suggestions;
}

// 对齐 HISTORY_HEADER 的 CSV（博主名称为「选中账号特征」摘要占位，需回填真实博主/主页才能进客户效果审计）
function buildHistoryCsv(cases) {
  const header = [
    'brief编号', '项目名称', '类目', '客户原始Brief', '历史人工补号量基线',
    '参考账号或视频', '平台', '博主名称', '主页链接', '人工复核标签', '客户选择', '拒绝原因'
  ];
  const rows = [header];
  for (const item of cases) {
    const isNegative = item.outcome === 'negative';
    rows.push([
      item.id,
      `${item.category}-${item.goal}`,
      item.category,
      `【${item.brandTone}调性】【${item.audience}人群】【${item.goal}目标】`,
      '',
      '',
      item.platformLabel,
      item.selectedAccountProfile,
      '',
      '',
      CUSTOMER_DECISION_BY_LABEL[item.outcomeLabel] || '待客户反馈',
      isNegative ? item.keyReason : ''
    ]);
  }
  return rows.map(row => row.map(csvCell).join(',')).join('\n');
}

function extractSignalTags(reason) {
  const text = String(reason || '');
  return SIGNAL_DICT.filter(item => item.words.some(word => text.includes(word))).map(item => item.tag);
}

function countTags(cases) {
  const counter = {};
  for (const item of cases) {
    for (const tag of item.signalTags || []) counter[tag] = (counter[tag] || 0) + 1;
  }
  return counter;
}

function normalizePlatform(value) {
  return PLATFORM_CODE[String(value || '').trim().toLowerCase()] || String(value || '').trim();
}

function labelPlatform(platform) {
  return { xiaohongshu: '小红书', douyin: '抖音', bilibili: 'B站' }[platform] || platform;
}

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

function withBom(text) {
  return `${text}`;
}

function parseArgs(argv) {
  const args = {};
  for (let index = 0; index < argv.length; index += 1) {
    const raw = argv[index];
    if (!raw.startsWith('--')) continue;
    const key = raw.slice(2).replace(/-([a-z])/g, (_, char) => char.toUpperCase());
    const next = argv[index + 1];
    if (!next || next.startsWith('--')) args[key] = true;
    else {
      args[key] = next;
      index += 1;
    }
  }
  return args;
}

if (require.main === module) {
  try {
    main();
  } catch (error) {
    console.error(error && error.stack ? error.stack : String(error));
    process.exit(1);
  }
}

module.exports = {
  readCaseTemplate,
  parseCasesFromSheets,
  buildFewshotCases,
  buildInsights,
  buildHistoryCsv,
  buildRuleSuggestions,
  extractSignalTags
};