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.json"), "utf8"));
const refs = JSON.parse(await fs.readFile(path.join(outDir, "reference_notes.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) return "主推";
  if (row.passCount >= 6) 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)}`);
  return parts.join("；");
}

function riskReason(row) {
  if (!row.riskItems?.length) return "硬指标均通过，建议进入主推池";
  return `需复核：${join(row.riskItems)}；${row.supportTagHits?.length ? `账号标签补充命中${join(row.supportTagHits)}` : ""}`;
}

const mainRows = scored
  .filter((row) => row.passCount >= 7)
  .slice(0, 8);
const backupRows = scored
  .filter((row) => row.passCount < 7)
  .slice(0, 12);

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

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

summary.getRange("A1:H1").merge();
summary.getRange("A1").values = [["AI提号 - DHA 小红书需求 PoC"]];
summary.getRange("A1").format = {
  fill: "#144D43",
  font: { bold: true, color: "#FFFFFF", size: 16 },
};
summary.getRange("A3:B8").values = [
  ["Brief", "小红书 / DHA / 干货科普 + 618合集 / 预算1000-5000"],
  ["硬性画像", "女性为主，25-44岁占比70%以上；兴趣前三需命中母婴/萌娃/生活记录中的2个"],
  ["数据门槛", "CPE≤3，阅读中位数≥4000，预估阅读单价≤1.0"],
  ["外部接口", "Just One 小红书蒲公英创作者搜索 + 详情/粉丝画像/近30天表现；TikHub 解析参考笔记"],
  ["采集规模", `${searches.length} 个搜索入口，去重候选 ${mergedCandidates.length} 个，深查 ${scored.length} 个`],
  ["结论", `主推 ${mainRows.length} 个；备选/复核 ${backupRows.length} 个。建议先人工复核主推前5个的内容调性和档期。`],
];
summary.getRange("A3:A8").format = { fill: "#E7F3EF", font: { bold: true } };
summary.getRange("A10:H10").values = [["推荐结论", "账号", "推荐等级", "预算", "粉丝画像", "内容/数据", "推荐理由", "风险提示"]];
summary.getRange("A10:H10").format = { fill: "#1F7A68", font: { bold: true, color: "#FFFFFF" } };
const summaryRows = [...mainRows.slice(0, 5), ...backupRows.slice(0, 3)].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)} / CPE ${fixed(row.cpe)}`,
  fitReason(row),
  riskReason(row),
]);
summary.getRangeByIndexes(10, 0, summaryRows.length, 8).values = summaryRows;
summary.getRange("A10:H18").format = { wrapText: true };
summary.freezePanes.freezeRows(10);

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),
    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:V${values.length + 1}`, true, `${sheet.name.replace(/\W/g, "")}Table`);
  sheet.freezePanes.freezeRows(1);
}

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

refSheet.getRange("A1:J1").values = [[
  "参考类型",
  "短链",
  "解析笔记ID",
  "作者昵称",
  "作者userId",
  "红书号",
  "标题",
  "类型",
  "状态",
  "最终链接",
]];
refSheet.getRange("A1:J1").format = { fill: "#144D43", font: { bold: true, color: "#FFFFFF" }, wrapText: true };
refSheet.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,
]);
refSheet.getRange("A1:J5").format = { wrapText: true };

method.getRange("A1:B14").values = [
  ["字段", "内容"],
  ["平台", "小红书"],
  ["产品", "DHA"],
  ["账号类型", "干货科普（营养师/育婴师/妈妈为主），618合集，6个月-7岁孩子妈妈博主"],
  ["预算", "1000-5000"],
  ["粉丝画像", "女性为主，25-44岁占比70%以上；广东/山东/河南占比较多属正常"],
  ["兴趣要求", "Top兴趣中母婴/萌娃/生活记录至少命中2个"],
  ["数据要求", "CPE≤3，阅读中位数≥4000，预估阅读单价≤1.0"],
  ["召回策略", "Just One 蒲公英创作者搜索：DHA母婴、营养师科普、育婴师妈妈、DHA 618合集、宝宝DHA测评等关键词"],
  ["参考解析", "TikHub 解析短链笔记作者；参考作者用于昵称反查和风格参照"],
  ["评分逻辑", "预算、女粉、25-44、兴趣、阅读中位、阅读单价、CPE为硬指标；育婴师/合集/目标省份加少量业务分"],
  ["使用限制", "本版未接入本地资源库历史合作与真实档期；CPE为空的账号需二次确认"],
  ["下一步", "对主推前5个做近10条内容人工复核、供应商档期确认、客户版名单润色"],
  ["生成时间", new Date().toLocaleString("zh-CN", { hour12: false })],
];
method.getRange("A1:A14").format = { fill: "#E7F3EF", font: { bold: true } };
method.getRange("A1:B14").format = { wrapText: true };

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

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

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

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

const output = await SpreadsheetFile.exportXlsx(wb);
await output.save(path.join(outDir, "AI提号-DHA小红书推荐名单-PoC.xlsx"));