import fs from "node:fs/promises";
import path from "node:path";
import { SpreadsheetFile, Workbook } from "@oai/artifact-tool";

const outputDir = path.resolve("outputs/centrum_douyin_audit_2026-06-08");
const generatedAt = "2026-06-08";

const sources = {
  rulePack: "E:/workspace/openclaw-voc-skill/inputs/QW1_Rule_Pack_ExamplesV0.1.docx",
  liveAttempt: "node claude-code/claude-code-voc-intelligence/mcp/src/tools/douyin-trend-run.js --collection-mode live ...",
  douyinPrenatalHub: "https://www.douyin.com/shipin/7354325556445399075",
  douyinMenAuthenticity: "https://www.douyin.com/shipin/7356548156433287195",
  douyinBaikeli: "https://www.douyin.com/user/MS4wLjABAAAAzuNaUb6R1GvlPXhJuVKj5Kzz_8hzB6IOudRSokplS20",
  douyinOlga: "https://www.douyin.com/user/MS4wLjABAAAAWwcXLQaOlIV4k04tSI4xYaYmCzRZt1a9_IDDutj7Wzra_yNzBUDrPQgV8UVJ_dsH",
  douyinGeslook: "https://www.douyin.com/user/MS4wLjABAAAA2NBxS22UFoP99jNw5s5szwbpy1IHK8Ugl2t3LiXZu0GX9yKfoFtwIrqyilASUYxC",
  centrumMen30: "https://centrum.com.hk/products/centrum-specialty-nutritional-blend/men-30-plus/",
  oceanEngineRules: "https://support.oceanengine.com/support/content/130539",
  douyinCrossBorderGovernance: "https://www.chinanews.com/cj/2026/05-26/10628777.shtml",
  douyinQ1Report: "https://pdf.dfcfw.com/pdf/H3_AP202508211731540280_1.pdf?1755772760000.pdf="
};

const sampleRows = [
  {
    id: "DY-01",
    sku: "善存孕妇/复合维生素相关竞品语境",
    sourceType: "公开索引 shipin 页面",
    author: "走丢的木头人",
    title: "孕妇复合维生素口碑最好的前十名",
    publishDate: "2026-05-21",
    interaction: "23赞 / 4评 / 11藏 / 11转（页面索引）",
    url: sources.douyinPrenatalHub,
    evidence: "孕妇复维红黑榜、排名、体验型文稿；主页面与相关视频可见 AI 文稿。",
    likelyApprovedEdits: "用红黑榜/成分体验替代直接治疗，但仍出现特殊人群和功效暗示。",
    riskLevel: "高",
    ruleHits: "RP03, RP04, RP07, RP11, RP15",
    commentSignal: "评论全文不可公开索引；页面评论数低，相关视频主要吸引选购/踩坑型互动。",
    improvement: "如善存自有稿件覆盖孕期，必须收回到标签、适用人群、用量和专业咨询，不做胎儿/产检/孕反改善承诺。"
  },
  {
    id: "DY-02",
    sku: "善存孕妇复合维生素（相关视频提及）",
    sourceType: "相关视频直链",
    author: "三傻",
    title: "孕期入手的孕妇复合维生素到底该怎么选",
    publishDate: "2026-05 页面索引",
    interaction: "相关页显示 2 条左右互动入口，需 API 复核",
    url: "https://www.douyin.com/video/7625973759477283170",
    evidence: "把善存描述为基础营养补充，并对比其他品牌的复配成分。",
    likelyApprovedEdits: "将善存定位为基础补充，没有直接写治疗；竞品对比力度较强。",
    riskLevel: "中高",
    ruleHits: "RP03, RP07, RP11, RP14",
    commentSignal: "潜在评论点为基础/全面/性价比/孕吐场景，缺评论正文。",
    improvement: "弱化竞品拉踩；补充版本与适用人群边界。"
  },
  {
    id: "DY-03",
    sku: "善存孕妇复合维生素（相关视频提及）",
    sourceType: "相关视频直链",
    author: "福宝麻麻",
    title: "孕妇复合维生素实测！热门孕妇复合维生素选哪家？",
    publishDate: "2026-05 页面索引",
    interaction: "相关页显示低互动，需 API 复核",
    url: "https://www.douyin.com/video/7641791455262231478",
    evidence: "善存被称为经典老牌、孕早期稳妥，同时出现颗粒大、吞咽费劲等体验反馈。",
    likelyApprovedEdits: "正向点控制在品牌/基础营养；负向点来自用户体验。",
    riskLevel: "中",
    ruleHits: "RP03, RP07, RP11, RP21",
    commentSignal: "体验负反馈集中在颗粒大小、反胃、吞咽压力。",
    improvement: "自有稿件可主动回应颗粒/吞咽/服用时机，并引导客服给 SKU 版本说明。"
  },
  {
    id: "DY-04",
    sku: "跨境营养品/善存海外相关信任语境",
    sourceType: "相关视频直链",
    author: "美好孕育",
    title: "孕妇复合维生素推荐 1 点很关键",
    publishDate: "2026-05 页面索引",
    interaction: "相关页显示 9 条左右互动入口，需 API 复核",
    url: "https://www.douyin.com/video/7638575776710905131",
    evidence: "强调海外营养品安全标准、说明与中国标准差异，核心是信任与风险自担。",
    likelyApprovedEdits: "非善存定向稿，但暴露跨境版本信任风险。",
    riskLevel: "中高",
    ruleHits: "RP05, RP06, RP14, RP21, RP25",
    commentSignal: "用户会追问安全标准、正品渠道、版本区别。",
    improvement: "善存跨境 SKU 应在商品卡/置顶评论解释版本、购买渠道、包装信息，不用模糊海外身份做背书。"
  },
  {
    id: "DY-05",
    sku: "善存复合维生素男士海外版",
    sourceType: "公开索引 shipin 页面",
    author: "在大草原上唱歌",
    title: "善存复合维生素男士海外版怎么看正品",
    publishDate: "2024-04-11",
    interaction: "12赞 / 44评 / 1藏 / 1转（搜索索引）",
    url: sources.douyinMenAuthenticity,
    evidence: "标题直接是正品辨别、天猫国际、喷墨、到期时间等问题。",
    likelyApprovedEdits: "用户自发内容不是品牌稿，但高度提示品牌投放后的评论疑虑。",
    riskLevel: "中",
    ruleHits: "RP05, RP06, RP18, RP21",
    commentSignal: "评论数显著高于赞，说明用户更想问真假/批号/效期/渠道。",
    improvement: "投放稿件下方必须有置顶正品辨别、效期、官方店铺与客服答疑。"
  },
  {
    id: "DY-06",
    sku: "善存PRO营养包 / 善存营养包",
    sourceType: "达人账号页公开索引",
    author: "主持人百克力",
    title: "中年夫妻长续婚姻靠的是什么？",
    publishDate: "2026-03 左右（账号页最新作品区）",
    interaction: "1.3万（账号页作品卡索引）",
    url: sources.douyinBaikeli,
    evidence: "内容以婚姻/中年关系为主，标签含善存PRO营养包、善存营养包。",
    likelyApprovedEdits: "产品从功效话术退到中年生活场景和关系叙事，降低合规风险。",
    riskLevel: "中",
    ruleHits: "RP03, RP13, RP21, RP22",
    commentSignal: "互动可能更多围绕婚姻议题，而非 SKU；商品承接和评论答疑容易不足。",
    improvement: "评论区补一个中性产品说明：适合关注日常营养补充的 30+/40+ 人群，按标签建议。"
  },
  {
    id: "DY-07",
    sku: "银善存脑力瓶 / 银善存大脑维生素",
    sourceType: "达人账号页公开索引",
    author: "Olga姐姐",
    title: "35年外企高管教你精力管理，易累体质必看！",
    publishDate: "2025-11 左右（账号页索引）",
    interaction: "3998（账号页作品卡索引，需 API 复核）",
    url: sources.douyinOlga,
    evidence: "职场、精力管理、年龄增长与竞争力下降的叙事，账号页片段含银善存标签。",
    likelyApprovedEdits: "从直接认知功效转为职场经验访谈，但仍用年龄/精力焦虑做钩子。",
    riskLevel: "中高",
    ruleHits: "RP02, RP03, RP09, RP13, RP19, RP22",
    commentSignal: "评论大概率围绕职场焦虑、年龄、精力，而非成分；需补抓验证。",
    improvement: "把'大脑/竞争力/易累体质'改成'关注日常营养与生活节律'，专家身份不延伸为健康背书。"
  },
  {
    id: "DY-08",
    sku: "银善存脑力瓶 / 银善存大脑维生素",
    sourceType: "达人账号页公开索引",
    author: "歌白说Geslook",
    title: "一定要大量阅读，真的可以改命",
    publishDate: "2026-04 左右（账号页索引）",
    interaction: "3.0万（账号页作品卡索引）",
    url: sources.douyinGeslook,
    evidence: "阅读/知识内容与银善存大脑维生素、脑力瓶、京东活动标签结合。",
    likelyApprovedEdits: "品牌露出放在知识内容标签中，主体不直接宣称产品改变认知。",
    riskLevel: "中",
    ruleHits: "RP01, RP03, RP13, RP16, RP22",
    commentSignal: "高互动来自阅读/改命议题，产品问题可能被淹没。",
    improvement: "保留阅读主题，但避免'改命'和'大脑维生素'相邻造成暗示；置顶评论补充非治疗、非药物边界。"
  }
];

const goldenRows = [
  {
    id: "GD-01",
    observedRisk: "全网第一、唯一有效、改命、最强红榜",
    ruleHits: "RP01, RP04, RP11",
    riskLevel: "红/黄边界",
    rewrite: "可作为日常营养补充选择之一；本内容仅基于成分信息与个人使用场景做分享。",
    why: "去掉绝对化和排名承诺，把判断依据收回到成分/场景。",
    sourceSample: "DY-01/DY-08"
  },
  {
    id: "GD-02",
    observedRisk: "大脑维生素、脑力瓶、年龄增长=职场竞争力下降",
    ruleHits: "RP02, RP03, RP09, RP13",
    riskLevel: "高",
    rewrite: "30+/50+ 人群可以关注日常营养、作息和学习习惯；如需补充维生素矿物质，请按标签建议选择。",
    why: "避免暗示改善认知/疾病/竞争力，把产品放到生活管理的辅助位置。",
    sourceSample: "DY-07/DY-08"
  },
  {
    id: "GD-03",
    observedRisk: "易累体质必看、能量源源不断、精气神马上上来",
    ruleHits: "RP03, RP04, RP13",
    riskLevel: "中高",
    rewrite: "忙碌阶段更容易忽略饮食均衡，可结合三餐、运动、休息和标签建议做日常营养补充。",
    why: "保留场景张力，但不承诺身体状态变化。",
    sourceSample: "DY-06/DY-07"
  },
  {
    id: "GD-04",
    observedRisk: "产检一路绿灯、胎儿发育稳稳的、改善胎儿偏小",
    ruleHits: "RP02, RP03, RP04, RP07, RP15",
    riskLevel: "红",
    rewrite: "孕期/备孕/哺乳期属于特殊阶段，补充产品和用量建议先咨询医生或专业人士，并以包装说明为准。",
    why: "特殊人群不能用结果承诺、胎儿发育或产检结果做产品功效。",
    sourceSample: "DY-01/DY-02/DY-03"
  },
  {
    id: "GD-05",
    observedRisk: "跨境版本等同蓝帽保健功能、海外标准更安全",
    ruleHits: "RP05, RP06, RP14, RP25",
    riskLevel: "高",
    rewrite: "请以当前购买页面、包装标签和官方客服说明为准；不同渠道/版本的包装、配方和适用说明可能不同。",
    why: "版本与法规属性要清晰，避免跨属性套用。",
    sourceSample: "DY-04/DY-05"
  },
  {
    id: "GD-06",
    observedRisk: "普通食品/营养品能治疗、预防、缓解病症",
    ruleHits: "RP02, RP03, RP05",
    riskLevel: "红",
    rewrite: "本品为日常营养补充，不替代药物或治疗；如有疾病或特殊健康状况，请咨询专业人士。",
    why: "与巨量引擎广告规范中虚假宣传/保健医疗混淆风险一致。",
    sourceSample: "规则包/OceanEngine"
  },
  {
    id: "GD-07",
    observedRisk: "智商税、黑榜、别买、竞品不行",
    ruleHits: "RP11, RP13, RP22",
    riskLevel: "中高",
    rewrite: "不同产品在成分组合、剂型、预算和适用阶段上各有侧重，建议按自身需求选择。",
    why: "弱化贬损与不公平比较，保留选购指导。",
    sourceSample: "DY-01/DY-02/DY-03"
  },
  {
    id: "GD-08",
    observedRisk: "营养师/专家/外企高管身份推导产品有效",
    ruleHits: "RP14, RP19",
    riskLevel: "中",
    rewrite: "嘉宾分享仅代表个人经验；产品信息以包装、标签和品牌确认材料为准。",
    why: "身份可服务内容可信度，不能替代 MA/RA/Legal 证据。",
    sourceSample: "DY-01/DY-07"
  },
  {
    id: "GD-09",
    observedRisk: "全家都适合、孕妇儿童老人随便吃",
    ruleHits: "RP07, RP15",
    riskLevel: "红/黄边界",
    rewrite: "适用人群、用法用量请以产品标签或说明书为准；特殊人群建议先咨询专业人士。",
    why: "不泛化适用人群，不自行给用量建议。",
    sourceSample: "规则包"
  },
  {
    id: "GD-10",
    observedRisk: "正品疑问、效期、喷码、渠道不清",
    ruleHits: "RP05, RP06, RP18, RP21",
    riskLevel: "中",
    rewrite: "购买前可核对官方授权渠道、包装版本、喷码/批号和效期；疑问请联系店铺客服。",
    why: "把评论区信任问题前置为购买指引，降低售后和投诉风险。",
    sourceSample: "DY-05"
  }
];

const commentRows = [
  ["正品/渠道信任", "DY-05", "44评高于12赞，标题即为真假辨别", "高", "置顶正品辨别、官方渠道、效期/喷码说明；客服关键词：真假、喷码、效期、天猫国际"],
  ["颗粒/吞咽/反胃体验", "DY-03", "孕期复维相关视频多次提到颗粒大、吞咽费劲、反胃", "中", "发布“怎么吃更舒服/何时吃/能否掰开”答疑，注意必须以标签为准"],
  ["基础补充 vs 全面复配", "DY-02/DY-03", "善存被放在基础营养补充语境，竞品常强调复配更多", "中高", "把善存定位为分龄/日常营养管理，补清每个 SKU 的成分和适用场景"],
  ["特殊人群安全", "DY-01/DY-04", "孕期视频容易出现胎儿/产检/孕反改善类诉求", "高", "避免主动投放孕期效果；如涉及特殊人群，固定专业咨询与标签边界"],
  ["故事高互动但商品弱承接", "DY-06/DY-08", "婚姻/阅读/职场议题有高互动，产品只在标签或软植入", "中", "在不增加功效风险的前提下，用置顶评论承接 SKU、版本和购买疑问"],
  ["年龄/精力焦虑", "DY-07", "年龄增长、精力管理、职场竞争力等钩子有效但有恐吓/功效暗示风险", "中高", "改为生活节律与日常营养支持，避免‘不补就下降’表达"]
];

const scriptRows = [
  ["生活关系钩子", "DY-06", "中年夫妻/长续婚姻", "先讲人群故事，再用标签/轻口播承接产品", "商品弱承接，评论容易只聊关系", "结尾增加中性 SKU 答疑：适用人群、版本、标签建议。"],
  ["职场精力钩子", "DY-07", "外企高管/精力管理/易累体质", "用经验访谈代替直接功效承诺", "大脑、竞争力、疲劳体质易触发 RP02/RP03/RP09", "改成作息、饮食、运动、日常营养支持，不暗示不补就下降。"],
  ["知识成长钩子", "DY-08", "阅读/改命/自我提升", "产品退到标签或活动承接，主体讲知识内容", "改命与大脑维生素相邻会形成认知改善暗示", "保留阅读主题，产品句只说日常营养补充和标签建议。"],
  ["选购评测钩子", "DY-01/DY-03", "红黑榜/实测/热门产品怎么选", "用对比和体验引发互动", "竞品贬损、特殊人群、效果排名风险高", "改为成分、剂型、预算、吞咽体验等中性维度。"],
  ["信任答疑钩子", "DY-05", "怎么看正品/喷码/效期", "围绕用户问题做购买前确认", "版本和渠道不清会放大投诉风险", "置顶评论提供官方渠道、效期、包装版本和客服路径。"]
];

const ruleRows = [
  ["RP01", "绝对化/保证性/最高级", "Active-Blocking", "去掉唯一、第一、100%、保证、立刻见效等表达"],
  ["RP02", "疾病治疗/治愈/诊断暗示", "Active-Blocking", "删除病症、治疗、预防、医生推荐式药效表达"],
  ["RP03", "未经确认功效/健康利益宣称", "Active-Blocking", "回到已确认 Claim、包装、标签、备案或说明书"],
  ["RP04", "数字/实验/前后对比造成明确功效承诺", "Active-Blocking", "不使用人体结果数值、前后对比、产检/指标变化承诺"],
  ["RP05", "法规属性误用/三品一械边界错误", "Active-Blocking", "不同渠道/跨境/保健食品/普通食品版本不能套用宣称"],
  ["RP06", "包装/标签/备案/说明书不一致", "Active-Blocking", "画面、字幕、口播、商品卡必须对齐同一 SKU/版本"],
  ["RP07", "用量/用法/禁忌/适用人群误导", "Active-Blocking", "用量、适用人群按标签；特殊人群咨询专业人士"],
  ["RP11", "竞品贬损/虚假比较/不公平竞争", "Active-Blocking", "红黑榜、智商税、别买等表达要弱化为场景差异"],
  ["RP14", "证据引用不足或上下文不清", "Advisory", "研究、专家、数据必须匹配 SKU、剂量、对象、场景"],
  ["RP16", "平台敏感词/卡审词疑似命中", "Advisory", "记录候选，不因一次卡审直接升级正式规则"],
  ["RP19", "达人/KOL/KOC/专家身份边界", "Advisory", "真实专家也不能超出资质和产品证据发言"],
  ["RP21", "CTA/购买引导/场景导向不清", "Advisory", "补商品版本、购买路径、客服答疑，不诱导治疗性购买"]
];

const limitationRows = [
  ["VOC live 采集", "已尝试", "needs_token", "当前工作区未检测到 VOC/Douyin token，无法批量抓取评论全文和更多视频。"],
  ["公开网页索引", "已使用", "partial", "可看到部分 shipin 页面 AI 文稿、互动数、账号页作品卡；评论正文覆盖不足。"],
  ["逐字稿拆解", "已使用/可复跑", "manual transcript", "使用公开 AI 文稿/人工摘录进入脚本拆解；正式报告建议用视频 ASR 或平台 AI 文稿补齐。"],
  ["审核前后版本对比", "缺源", "needs_material", "目前没有品牌审核前原稿、驳回原因、通过版 Audit ID；本报告为已发布稿件的合规推断与黄金改写样本。"],
  ["覆盖范围", "受限", "web-indexed", "公开索引覆盖 8 个样本/账号线索；配置 token 后可扩展到 10 关键词 x 10视频 x 5页评论。"]
];

const riskSummary = [
  ["高", sampleRows.filter(row => row.riskLevel === "高").length],
  ["中高", sampleRows.filter(row => row.riskLevel === "中高").length],
  ["中", sampleRows.filter(row => row.riskLevel === "中").length]
];

function toMatrix(rows, headers) {
  return [headers, ...rows.map(row => headers.map(header => row[header] ?? ""))];
}

function applyHeader(range) {
  range.format = {
    fill: "#1F4E79",
    font: { bold: true, color: "#FFFFFF" },
    wrapText: true
  };
}

function styleSheet(sheet, usedRangeAddress, widths = []) {
  sheet.showGridLines = false;
  if (usedRangeAddress) {
    const range = sheet.getRange(usedRangeAddress);
    range.format = {
      font: { name: "Microsoft YaHei", size: 10 },
      wrapText: true,
      verticalAlignment: "top",
      borders: { preset: "all", style: "thin", color: "#D9E2EC" }
    };
  }
  widths.forEach(([col, px]) => {
    sheet.getRange(`${col}:${col}`).format.columnWidthPx = px;
  });
}

function writeRows(sheet, startRow, startCol, rows) {
  sheet.getRangeByIndexes(startRow, startCol, rows.length, rows[0].length).values = rows;
}

function buildMarkdown() {
  const evidenceCards = sampleRows.map((row, index) => [
    `### ${index + 1}. ${row.title}`,
    `- SKU/方向：${row.sku}`,
    `- 作者/来源：${row.author}｜${row.sourceType}`,
    `- 互动：${row.interaction}`,
    `- 链接：${row.url}`,
    `- 值得看：${row.evidence}`,
    `- 审核润色推断：${row.likelyApprovedEdits}`,
    `- 评论/VOC信号：${row.commentSignal}`,
    `- 改进空间：${row.improvement}`,
    ""
  ].join("\n")).join("\n");

  const goldenTable = goldenRows.map(row =>
    `| ${row.id} | ${row.observedRisk} | ${row.ruleHits} | ${row.rewrite} | ${row.why} |`
  ).join("\n");

  const commentTable = commentRows.map(row => `| ${row.join(" | ")} |`).join("\n");
  const scriptTable = scriptRows.map(row => `| ${row.join(" | ")} |`).join("\n");

  return `# 善存抖音稿件审核润色与评论 VOC 初步报告

生成日期：${generatedAt}

## 一句话结论

本轮可公开索引样本显示：善存相关抖音内容已经明显采用“生活场景/访谈/知识内容 + 产品标签或轻口播”的方式降低硬功效风险；但银善存脑力瓶、善存 PRO 营养包与孕妇复维相关语境里，仍容易滑向“精力/大脑/产检/胎儿/年龄焦虑/竞品红黑榜”等高风险表达。评论层面，公开网页无法抓到完整评论正文；能看到的强信号是正品渠道、版本效期、颗粒吞咽、基础补充是否足够、特殊人群安全边界。

## 采集与限制

- 已调用本地 \`claude-code-voc-intelligence\` 抖音 live 采集入口，状态为 \`needs_token\`：当前工作区没有 VOC/Douyin token，无法批量抓取更多视频和评论全文。
- 已使用公开网页索引补样：抖音 \`shipin\` 页面、账号页作品卡、AI 文稿片段、互动数。
- 已提取资料包：\`${sources.rulePack}\`，得到 28 个 Rule Pack、红黄绿样例、推荐改法与供应商配置字段。
- 因缺少品牌审核前原稿、驳回记录、通过版 Audit ID，本报告里的“做了哪些润色和修改”是基于已发布稿件 + QW1 规则包 + 平台规则的合规推断，不等同于逐条审核日志复盘。

## 已发布稿件的润色路径

1. 从功效承诺改成生活场景：PRO 营养包样本借中年夫妻、40岁生活、关系经营切入，产品多在标签或轻植入出现。
2. 从医疗/改善结果改成日常营养：较合规的表达会说基础营养、标签建议、日常补充，不直接说治疗或指标改善。
3. 从产品硬广改成达人内容：银善存样本用阅读、职场、精力管理内容带出品牌，但要警惕“大脑维生素”“竞争力下降”等暗示。
4. 从单向卖点改成评论答疑：正品、渠道、效期、版本差异应前置到置顶评论和商品卡，否则用户会在评论区追问。
5. 从竞品拉踩改成场景差异：孕妇复维类红黑榜最容易违规，适合改成不同阶段、预算、剂型、吞咽体验的中性比较。

## 黄金改写数据集

| ID | 风险原型 | 规则命中 | 建议通过版 | 为什么这样改 |
|---|---|---|---|---|
${goldenTable}

## 评论/VOC 改进空间

| 主题 | 样本 | 证据 | 优先级 | 建议动作 |
|---|---|---|---|---|
${commentTable}

## 逐字稿/脚本结构拆解

| 结构类型 | 样本 | 开头钩子 | 当前承接方式 | 主要风险 | 可复用改法 |
|---|---|---|---|---|---|
${scriptTable}

## 样本案例

${evidenceCards}

## 建议补采命令

\`\`\`powershell
node claude-code/claude-code-voc-intelligence/mcp/src/tools/douyin-trend-run.js \\
  --collection-mode live \\
  --project "善存抖音稿件审核与评论VOC" \\
  --industry "营养健康/膳食补充剂" \\
  --keywords "善存营养包,善存PRO营养包,银善存脑力瓶,善存复合维生素,善存孕妇复合维生素,善存多维元素片,善存佳维片,善存小佳维,Centrum善存,善存海外营养包" \\
  --keyword-limit 10 --videos-per-keyword 10 --max-comment-pages 5 \\
  --output outputs/centrum_douyin_audit_2026-06-08/live-full
\`\`\`

## 外部规则与产品边界来源

- Centrum HK 男士30+产品页：${sources.centrumMen30}
- 巨量引擎广告投放管理规范：${sources.oceanEngineRules}
- 抖音跨境品牌资质与带货合规倡议报道：${sources.douyinCrossBorderGovernance}
- 抖音 2025Q1 高增行业与爆款复盘 PDF：${sources.douyinQ1Report}
`;
}

async function buildWorkbook() {
  const workbook = Workbook.create();

  const summary = workbook.worksheets.add("执行摘要");
  summary.getRange("A1:H1").merge();
  summary.getRange("A1").values = [["善存抖音稿件审核润色与评论 VOC 初步汇总"]];
  summary.getRange("A1").format = {
    fill: "#0B1F33",
    font: { bold: true, color: "#FFFFFF", size: 16 },
    horizontalAlignment: "center"
  };
  const kpiRows = [
    ["生成日期", generatedAt, "样本数", sampleRows.length],
    ["黄金改写样本", goldenRows.length, "评论/VOC主题", commentRows.length],
    ["live采集状态", "needs_token", "结论等级", "初步判断，待补采校准"],
    ["主要高风险", "孕期/胎儿/大脑/精力/正品", "建议", "补抓评论全文 + 审核前后稿"]
  ];
  writeRows(summary, 2, 0, kpiRows);
  const riskTable = [["风险等级", "样本数"], ...riskSummary];
  writeRows(summary, 8, 0, riskTable);
  applyHeader(summary.getRange("A9:B9"));
  const chart = summary.charts.add("bar", summary.getRange("A9:B12"));
  chart.title = "样本风险等级分布";
  chart.hasLegend = false;
  chart.setPosition("D9", "H24");
  styleSheet(summary, "A3:H24", [["A", 150], ["B", 190], ["C", 110], ["D", 120], ["E", 160], ["F", 190], ["G", 110], ["H", 140]]);

  const samples = workbook.worksheets.add("样本_抖音稿件");
  const sampleHeaders = ["id", "sku", "sourceType", "author", "title", "publishDate", "interaction", "url", "evidence", "likelyApprovedEdits", "riskLevel", "ruleHits", "commentSignal", "improvement"];
  const sampleMatrix = toMatrix(sampleRows, sampleHeaders);
  writeRows(samples, 0, 0, sampleMatrix);
  applyHeader(samples.getRangeByIndexes(0, 0, 1, sampleHeaders.length));
  styleSheet(samples, `A1:N${sampleMatrix.length}`, [["A", 70], ["B", 180], ["C", 130], ["D", 110], ["E", 240], ["F", 120], ["G", 160], ["H", 260], ["I", 260], ["J", 240], ["K", 80], ["L", 140], ["M", 240], ["N", 300]]);
  samples.freezePanes.freezeRows(1);

  const golden = workbook.worksheets.add("黄金改写集");
  const goldenHeaders = ["id", "observedRisk", "ruleHits", "riskLevel", "rewrite", "why", "sourceSample"];
  const goldenMatrix = toMatrix(goldenRows, goldenHeaders);
  writeRows(golden, 0, 0, goldenMatrix);
  applyHeader(golden.getRangeByIndexes(0, 0, 1, goldenHeaders.length));
  styleSheet(golden, `A1:G${goldenMatrix.length}`, [["A", 75], ["B", 260], ["C", 140], ["D", 100], ["E", 330], ["F", 280], ["G", 120]]);
  golden.freezePanes.freezeRows(1);

  const comments = workbook.worksheets.add("评论VOC");
  const commentMatrix = [["主题", "样本", "证据", "优先级", "建议动作"], ...commentRows];
  writeRows(comments, 0, 0, commentMatrix);
  applyHeader(comments.getRange("A1:E1"));
  styleSheet(comments, `A1:E${commentMatrix.length}`, [["A", 170], ["B", 120], ["C", 300], ["D", 90], ["E", 360]]);
  comments.freezePanes.freezeRows(1);

  const scripts = workbook.worksheets.add("脚本拆解");
  const scriptMatrix = [["结构类型", "样本", "开头钩子", "当前承接方式", "主要风险", "可复用改法"], ...scriptRows];
  writeRows(scripts, 0, 0, scriptMatrix);
  applyHeader(scripts.getRange("A1:F1"));
  styleSheet(scripts, `A1:F${scriptMatrix.length}`, [["A", 150], ["B", 120], ["C", 210], ["D", 260], ["E", 300], ["F", 360]]);
  scripts.freezePanes.freezeRows(1);

  const rules = workbook.worksheets.add("规则映射");
  const ruleMatrix = [["Rule ID", "规则包", "状态", "本项目用法"], ...ruleRows];
  writeRows(rules, 0, 0, ruleMatrix);
  applyHeader(rules.getRange("A1:D1"));
  styleSheet(rules, `A1:D${ruleMatrix.length}`, [["A", 90], ["B", 240], ["C", 130], ["D", 420]]);
  rules.freezePanes.freezeRows(1);

  const limits = workbook.worksheets.add("采集限制");
  const limitationMatrix = [["环节", "动作", "状态", "说明"], ...limitationRows];
  writeRows(limits, 0, 0, limitationMatrix);
  applyHeader(limits.getRange("A1:D1"));
  styleSheet(limits, `A1:D${limitationMatrix.length}`, [["A", 160], ["B", 110], ["C", 130], ["D", 520]]);
  limits.freezePanes.freezeRows(1);

  const inspect = await workbook.inspect({
    kind: "match",
    searchTerm: "#REF!|#DIV/0!|#VALUE!|#NAME\\?|#N/A",
    options: { useRegex: true, maxResults: 50 },
    summary: "formula error scan"
  });

  for (const sheetName of ["执行摘要", "样本_抖音稿件", "黄金改写集", "评论VOC", "脚本拆解", "规则映射", "采集限制"]) {
    const preview = await workbook.render({ sheetName, autoCrop: "all", scale: 1, format: "png" });
    await fs.writeFile(
      path.join(outputDir, `${sheetName}.png`),
      new Uint8Array(await preview.arrayBuffer())
    );
  }

  const xlsx = await SpreadsheetFile.exportXlsx(workbook);
  const xlsxPath = path.join(outputDir, "善存抖音稿件审核润色与评论VOC.xlsx");
  await xlsx.save(xlsxPath);
  return { xlsxPath, inspect: inspect.ndjson };
}

function buildAnalyzerInput() {
  return {
    metadata: {
      project: "善存抖音稿件审核润色与评论VOC",
      generatedAt,
      source: "public-indexed-douyin-plus-rule-pack"
    },
    videos: sampleRows.map(row => ({
      aweme_id: row.id,
      keyword: row.sku,
      title: row.title,
      desc: `${row.title}。${row.evidence}。${row.likelyApprovedEdits}`,
      author: { nickname: row.author },
      likeCount: 0,
      commentCount: 0,
      shareCount: 0,
      playCount: 0,
      url: row.url
    })),
    comments: [],
    transcripts: sampleRows.map(row => ({
      awemeId: row.id,
      provider: "manual-public-indexed",
      text: `${row.title}。${row.evidence}。${row.likelyApprovedEdits}。${row.improvement}`,
      segments: []
    }))
  };
}

async function main() {
  await fs.mkdir(outputDir, { recursive: true });
  const mdPath = path.join(outputDir, "善存抖音稿件审核润色与评论VOC.md");
  const dataPath = path.join(outputDir, "centrum_douyin_samples.json");
  const analyzerInputPath = path.join(outputDir, "douyin_analyzer_input.json");
  await fs.writeFile(mdPath, buildMarkdown(), "utf8");
  await fs.writeFile(dataPath, JSON.stringify({ generatedAt, sources, sampleRows, goldenRows, commentRows, scriptRows, ruleRows, limitationRows }, null, 2), "utf8");
  await fs.writeFile(analyzerInputPath, JSON.stringify(buildAnalyzerInput(), null, 2), "utf8");
  const workbookResult = await buildWorkbook();
  console.log(JSON.stringify({ mdPath, dataPath, analyzerInputPath, xlsxPath: workbookResult.xlsxPath, inspect: workbookResult.inspect }, null, 2));
}

main().catch(error => {
  console.error(error);
  process.exit(1);
});