tihao-ai/build_dha_tihao_report_v2.mjs

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

const outDir = "E:/workspace/tihao-ai/output/dha-tihao-poc";
const scored = JSON.parse(await fs.readFile(path.join(outDir, "scored_candidates_enriched.json"), "utf8"));
const refs = JSON.parse(await fs.readFile(path.join(outDir, "reference_notes.json"), "utf8"));
const refAccounts = JSON.parse(await fs.readFile(path.join(outDir, "reference_baseline.json"), "utf8"));
const searches = JSON.parse(await fs.readFile(path.join(outDir, "justone_searches.json"), "utf8"));
const mergedCandidates = JSON.parse(await fs.readFile(path.join(outDir, "candidates_merged_pre_detail.json"), "utf8"));

function pct(value) {
  if (value === null || value === undefined || Number.isNaN(Number(value))) return "";
  return `${Number(value).toFixed(1)}%`;
}

function money(value) {
  if (value === null || value === undefined || Number.isNaN(Number(value))) return "";
  return Number(value);
}

function fixed(value, digits = 2) {
  if (value === null || value === undefined || Number.isNaN(Number(value))) return "";
  return Number(value).toFixed(digits);
}

function join(value) {
  if (!value) return "";
  if (Array.isArray(value)) return value.filter(Boolean).join("；");
  return String(value);
}

function provinceSummary(value) {
  if (!Array.isArray(value)) return "";
  return value
    .slice(0, 5)
    .map((item) => `${item[0]} ${Number(item[1] || 0).toFixed(1)}%`)
    .join("；");
}

function verdict(row) {
  if (row.passCount >= 7 && row.referenceSimilarity >= 55) return "主推";
  if (row.passCount >= 6 || row.referenceSimilarity >= 70) return "备选/复核";
  return "需复核";
}

function fitReason(row) {
  const parts = [];
  if (row.passItems?.includes("预算")) parts.push("预算命中");
  if (row.passItems?.includes("女粉")) parts.push(`女粉${pct(row.femalePct)}`);
  if (row.passItems?.includes("25-44")) parts.push(`25-44岁${pct(row.age25_44Pct)}`);
  if (row.interestHits?.length) parts.push(`兴趣命中${join(row.interestHits)}`);
  if (row.passItems?.includes("阅读")) parts.push(`阅读中位${Math.round(row.readMedian)}`);
  if (row.passItems?.includes("CPE")) parts.push(`CPE ${fixed(row.cpe)}`);
  if (row.referenceFitLevel) parts.push(`参考相似度${row.referenceSimilarity}(${row.referenceFitLevel})`);
  return parts.join("；");
}

function riskReason(row) {
  const risks = [...(row.riskItems || [])];
  if ((row.referenceSimilarity || 0) < 55) risks.push("参考内容相似度偏低");
  if (!risks.length) return "硬指标与参考内容基准均通过，建议进入主推池";
  return `需复核：${join(risks)}`;
}

const mainRows = scored
  .filter((row) => row.passCount >= 7 && row.referenceSimilarity >= 55)
  .slice(0, 8);
const backupRows = scored
  .filter((row) => !mainRows.some((m) => m.userId === row.userId))
  .slice(0, 14);

const wb = Workbook.create();
const summary = wb.worksheets.add("结论");
const main = wb.worksheets.add("主推账号");
const backup = wb.worksheets.add("备选复核");
const refAccountSheet = wb.worksheets.add("参考账号基准");
const refNoteSheet = wb.worksheets.add("参考链接解析");
const method = wb.worksheets.add("Brief与方法");

for (const sheet of [summary, main, backup, refAccountSheet, refNoteSheet, method]) {
  sheet.showGridLines = false;
}

summary.getRange("A1:I1").merge();
summary.getRange("A1").values = [["AI提号 - DHA 小红书需求 PoC（补 TikHub 参考账号分析）"]];
summary.getRange("A1").format = {
  fill: "#144D43",
  font: { bold: true, color: "#FFFFFF", size: 16 },
};
summary.getRange("A3:B9").values = [
  ["Brief", "小红书 / DHA / 干货科普 + 618合集 / 预算1000-5000"],
  ["硬性画像", "女性为主，25-44岁占比70%以上；兴趣前三需命中母婴/萌娃/生活记录中的2个"],
  ["数据门槛", "CPE≤3，阅读中位数≥4000，预估阅读单价≤1.0"],
  ["参考抓取", "TikHub 已抓 3 个有效参考账号主页画像与近期作品：17/20/22条；1个短链当前不可浏览"],
  ["候选召回", `Just One 15个搜索入口，去重候选${mergedCandidates.length}个，深查${scored.length}个`],
  ["参考基准", "妈妈真实使用 + 选品/避坑 + DHA/营养成分 + 节点合集/测评；不是只看账号标签"],
  ["结论", `主推${mainRows.length}个；备选/复核${backupRows.length}个。建议先复核主推前5个的最新档期与内容调性。`],
];
summary.getRange("A3:A9").format = { fill: "#E7F3EF", font: { bold: true } };

summary.getRange("A11:I11").values = [[
  "推荐结论",
  "账号",
  "等级",
  "预算",
  "粉丝画像",
  "数据",
  "参考相似度",
  "推荐理由",
  "风险提示",
]];
summary.getRange("A11:I11").format = { fill: "#1F7A68", font: { bold: true, color: "#FFFFFF" } };
const summaryRows = [...mainRows.slice(0, 5), ...backupRows.slice(0, 4)].map((row, idx) => [
  idx < 5 ? "优先沟通" : "备选观察",
  row.name,
  verdict(row),
  `${row.chosenFormat} ${row.chosenPrice}`,
  `女粉${pct(row.femalePct)} / 25-44 ${pct(row.age25_44Pct)} / ${join(row.interestHits)}`,
  `阅读${Math.round(row.readMedian)} / 单价${fixed(row.readUnit, 3)} / CPE ${fixed(row.cpe)}`,
  `${row.referenceSimilarity} / ${row.referenceHitPoints}`,
  fitReason(row),
  riskReason(row),
]);
summary.getRangeByIndexes(11, 0, summaryRows.length, 9).values = summaryRows;
summary.getRange("A11:I20").format = { wrapText: true };
summary.freezePanes.freezeRows(11);

const headers = [
  "推荐等级",
  "账号",
  "红书号",
  "地区",
  "粉丝数",
  "图文报价",
  "视频报价",
  "建议形式",
  "建议报价",
  "阅读中位",
  "互动中位",
  "阅读单价",
  "CPE",
  "女粉占比",
  "25-44占比",
  "兴趣命中",
  "Top兴趣",
  "标签",
  "省份分布",
  "参考相似度",
  "参考命中点",
  "内容采样证据",
  "通过项",
  "风险项",
  "推荐理由",
];

function writeCandidateSheet(sheet, rows) {
  sheet.getRangeByIndexes(0, 0, 1, headers.length).values = [headers];
  sheet.getRangeByIndexes(0, 0, 1, headers.length).format = {
    fill: "#144D43",
    font: { bold: true, color: "#FFFFFF" },
    wrapText: true,
  };
  const values = rows.map((row) => [
    verdict(row),
    row.name,
    row.redId,
    row.location,
    row.fansNum,
    money(row.picturePrice),
    money(row.videoPrice),
    row.chosenFormat,
    money(row.chosenPrice),
    Math.round(row.readMedian || 0),
    Math.round(row.interactionMedian || 0),
    fixed(row.readUnit, 3),
    fixed(row.cpe, 2),
    pct(row.femalePct),
    pct(row.age25_44Pct),
    join(row.interestHits),
    join(row.interests),
    join(row.tags),
    provinceSummary(row.provinces),
    row.referenceSimilarity,
    row.referenceHitPoints,
    row.contentEvidence,
    join(row.passItems),
    join(row.riskItems),
    `${fitReason(row)}。${riskReason(row)}`,
  ]);
  sheet.getRangeByIndexes(1, 0, values.length, headers.length).values = values;
  sheet.getRangeByIndexes(0, 0, values.length + 1, headers.length).format = { wrapText: true };
  sheet.tables.add(`A1:Y${values.length + 1}`, true, `${sheet.name.replace(/\W/g, "")}Table`);
  sheet.freezePanes.freezeRows(1);
}

writeCandidateSheet(main, mainRows);
writeCandidateSheet(backup, backupRows);

refAccountSheet.getRange("A1:K1").values = [[
  "参考类型",
  "账号",
  "红书号",
  "userId",
  "粉丝数",
  "IP/地区",
  "主页简介",
  "采样作品数",
  "作品类型结构",
  "核心内容信号",
  "采样标题",
]];
refAccountSheet.getRange("A1:K1").format = { fill: "#144D43", font: { bold: true, color: "#FFFFFF" }, wrapText: true };
refAccountSheet.getRangeByIndexes(1, 0, refAccounts.length, 11).values = refAccounts.map((row) => [
  row.bucket,
  row.name,
  row.redId,
  row.userId,
  row.fans,
  row.location,
  row.profileDesc,
  row.sampleCount,
  row.typeMix,
  join(row.topSignals),
  join(row.sampleTitles),
]);
refAccountSheet.getRange("A1:K5").format = { wrapText: true };
refAccountSheet.freezePanes.freezeRows(1);

refNoteSheet.getRange("A1:J1").values = [[
  "参考类型",
  "短链",
  "解析笔记ID",
  "作者昵称",
  "作者userId",
  "红书号",
  "标题",
  "类型",
  "状态",
  "最终链接",
]];
refNoteSheet.getRange("A1:J1").format = { fill: "#144D43", font: { bold: true, color: "#FFFFFF" }, wrapText: true };
refNoteSheet.getRangeByIndexes(1, 0, refs.length, 10).values = refs.map((row) => [
  row.bucket,
  row.url,
  row.note_id,
  row.author_nickname,
  row.author_userid,
  row.author_red_id,
  row.title,
  row.type,
  row.note_id ? "已解析" : "短链当前不可浏览",
  row.final_url,
]);
refNoteSheet.getRange("A1:J5").format = { wrapText: true };

method.getRange("A1:B15").values = [
  ["字段", "内容"],
  ["平台", "小红书"],
  ["产品", "DHA"],
  ["账号类型", "干货科普（营养师/育婴师/妈妈为主），618合集（6个月-7岁孩子妈妈博主）"],
  ["预算", "1000-5000"],
  ["粉丝画像", "女性为主，25-44岁占比70%以上；广东/山东/河南占比多属正常"],
  ["兴趣要求", "Top兴趣中母婴/萌娃/生活记录至少命中2个"],
  ["数据要求", "CPE≤3，阅读中位数≥4000，预估阅读单价≤1.0"],
  ["参考账号抓取", "TikHub get_user_info + app_v2/get_user_posted_notes，抓取参考账号主页画像和近期作品"],
  ["候选召回", "Just One 小红书蒲公英创作者搜索 + 详情/粉丝画像/近30天表现"],
  ["重排逻辑", "硬指标通过数优先；同等情况下用参考相似度重排；高相似但硬指标不足进入备选复核"],
  ["参考相似度", "基于DHA/营养成分、育婴师/专业背书、妈妈真实使用、选品避坑、合集节点、母婴萌娃、测评开箱等信号"],
  ["使用限制", "未接入本地历史合作与真实档期；Just One 部分账号 CPE/笔记样本为空需供应商二次确认"],
  ["费用提示", "TikHub 用户信息/用户作品接口调用会计费；本轮参考抓取 3个用户信息 + 3个用户作品接口"],
  ["生成时间", new Date().toLocaleString("zh-CN", { hour12: false })],
];
method.getRange("A1:A15").format = { fill: "#E7F3EF", font: { bold: true } };
method.getRange("A1:B15").format = { wrapText: true };

for (const sheet of [summary, main, backup, refAccountSheet, refNoteSheet, method]) {
  const used = sheet.getUsedRange();
  used.format.autofitColumns();
  used.format.autofitRows();
}

const inspect = await wb.inspect({
  kind: "table",
  range: "结论!A1:I20",
  include: "values",
  tableMaxRows: 20,
  tableMaxCols: 9,
});
console.log(inspect.ndjson.split("\n").slice(0, 4).join("\n"));

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

const preview = await wb.render({ sheetName: "结论", autoCrop: "all", scale: 1, format: "png" });
await fs.writeFile(
  path.join(outDir, "dha-tihao-tikhub-summary-preview.png"),
  new Uint8Array(await preview.arrayBuffer()),
);

const outputPath = path.join(outDir, "AI提号-DHA小红书推荐名单-TikHub参考分析版.xlsx");
const output = await SpreadsheetFile.exportXlsx(wb);
await output.save(outputPath);
console.log(outputPath);