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

const root = "/Users/jack/code/010-cpt/008-zhct/zhctprompt";
const taskRel = "work/2026-07-17-all-region-signal-analysis";
const outputRel = "outputs/2026-07-17-all-region-signal-analysis";
const taskDir = path.join(root, taskRel);
const outputDir = path.join(root, outputRel);
const outputXlsx = path.join(outputDir, "all-region-signal-analysis-20260717.xlsx");
const detailCsv = path.join(taskDir, "all-region-signal-records.csv");
const summaryPath = path.join(taskDir, "summary.md");
const checkedAt = "2026-07-17 00:00 +0800";

const publicAwardFiles = [
  "work/2026-06-17-digital-business-commercial-validation/enterprise-institution-ict-awards.csv",
  "work/2026-06-17-digital-business-commercial-validation/school-ict-awards.csv",
  "work/2026-06-17-digital-business-commercial-validation/school-linked-awards.csv",
];
const internalProjectFile = "work/2026-06-25-smart-canteen-executive-evidence-pack/project_revenue_summary.csv";
const deliveryDetailFile = "work/2026-06-25-smart-canteen-delivery-attribute/smart_canteen_delivery_attribute_detail.csv";
const strategyFiles = [
  "work/2026-06-17-digital-business-commercial-validation/battlefield-market-sizing-from-tenders.csv",
  "work/2026-06-17-digital-business-commercial-validation/buyer-personas-detailed.csv",
  "work/2026-06-17-digital-business-commercial-validation/competitor-alternatives.csv",
  "work/2026-06-17-digital-business-commercial-validation/segment-scoring.csv",
  "work/2026-06-17-digital-business-commercial-validation/source-evidence.csv",
  "work/2026-06-17-digital-business-commercial-validation/wedrive-market-evidence-refresh-20260716.csv",
];

async function readText(rel) {
  return fs.readFile(path.join(root, rel), "utf8").catch(() => "");
}

function splitRows(text, delimiter = ",") {
  text = text.replace(/^\uFEFF/, "");
  const rows = [];
  let row = [];
  let cell = "";
  let inQuotes = false;
  for (let i = 0; i < text.length; i += 1) {
    const ch = text[i];
    const next = text[i + 1];
    if (ch === '"') {
      if (inQuotes && next === '"') {
        cell += '"';
        i += 1;
      } else {
        inQuotes = !inQuotes;
      }
    } else if (ch === delimiter && !inQuotes) {
      row.push(cell);
      cell = "";
    } else if ((ch === "\n" || ch === "\r") && !inQuotes) {
      if (ch === "\r" && next === "\n") i += 1;
      row.push(cell);
      if (row.some((v) => v.trim() !== "")) rows.push(row);
      row = [];
      cell = "";
    } else {
      cell += ch;
    }
  }
  if (cell || row.length) {
    row.push(cell);
    if (row.some((v) => v.trim() !== "")) rows.push(row);
  }
  return rows;
}

async function readCsv(rel) {
  const text = await readText(rel);
  if (!text) return { header: [], rows: [] };
  const raw = splitRows(text, ",");
  return { header: raw[0] || [], rows: raw.slice(1), rel };
}

function value(row, header, names) {
  for (const name of names) {
    const idx = header.indexOf(name);
    if (idx >= 0 && row[idx] != null && row[idx] !== "") return row[idx];
  }
  return "";
}

function compact(v, max = 220) {
  return String(v ?? "").replace(/\s+/g, " ").trim().slice(0, max);
}

function num(v) {
  const n = Number(String(v ?? "").replace(/,/g, ""));
  return Number.isFinite(n) ? n : 0;
}

function monthSortValue(month) {
  return String(month || "").replace(/\D/g, "");
}

function recordKey(parts) {
  return parts.map((p) => compact(p, 160)).join("||");
}

const publicRecords = [];
for (const rel of publicAwardFiles) {
  const { header, rows } = await readCsv(rel);
  for (const row of rows) {
    const url = value(row, header, ["官网查看地址"]);
    const title = value(row, header, ["项目名称"]);
    const buyer = value(row, header, ["招标单位"]);
    const amount = num(value(row, header, ["金额数值", "中标金额（元）"]));
    publicRecords.push({
      recordType: "公开采购/中标",
      evidenceLevel: "C",
      sourceFile: rel,
      province: value(row, header, ["发布省份"]),
      city: value(row, header, ["发布市级"]),
      district: value(row, header, ["发布区级"]),
      title,
      buyer,
      customerType: value(row, header, ["客户类型"]),
      supplier: value(row, header, ["中标单位"]),
      demandType: value(row, header, ["需求类型"]),
      stage: value(row, header, ["中标阶段"]),
      amount,
      amountWan: amount ? amount / 10000 : 0,
      month: value(row, header, ["月份"]),
      date: value(row, header, ["发布时间", "信息发布时间"]),
      sourceUrl: url,
      action: "市场/竞品观察：按地区、客户类型、需求类型和中标单位筛选后再决定是否跟进。",
      dedupeKey: url || recordKey([title, buyer, amount, value(row, header, ["发布省份"]), value(row, header, ["发布市级"])]),
    });
  }
}

const seenPublic = new Set();
const publicUnique = publicRecords.filter((r) => {
  if (seenPublic.has(r.dedupeKey)) return false;
  seenPublic.add(r.dedupeKey);
  return true;
});

const internalProjects = [];
{
  const { header, rows } = await readCsv(internalProjectFile);
  for (const row of rows) {
    const amount = num(value(row, header, ["开票/确收金额"]));
    internalProjects.push({
      recordType: "内部项目收入",
      evidenceLevel: "B/D",
      sourceFile: internalProjectFile,
      province: "",
      city: "",
      district: "",
      title: value(row, header, ["项目名称"]),
      buyer: value(row, header, ["客户名称"]),
      customerType: value(row, header, ["客户类型（学校/政企/国企/企业园区/医院养老/其他）"]),
      supplier: value(row, header, ["供应商/硬件类型"]),
      demandType: value(row, header, ["产品类型（前端结算/食安进销存/营养健康/配套服务/硬件）"]),
      stage: value(row, header, ["项目状态"]),
      amount,
      amountWan: amount ? amount / 10000 : 0,
      month: value(row, header, ["月份"]),
      date: "",
      sourceUrl: "",
      action: "经营复核：补项目台账、合同、验收、回款和毛利后再用于经营判断。",
    });
  }
}

const deliveryDetails = [];
{
  const { header, rows } = await readCsv(deliveryDetailFile);
  for (const row of rows) {
    const amount = num(value(row, header, ["金额"]));
    deliveryDetails.push({
      recordType: "内部供应链/交付拆分",
      evidenceLevel: "B/D",
      sourceFile: deliveryDetailFile,
      province: "",
      city: "",
      district: "",
      title: value(row, header, ["客户/项目"]),
      buyer: value(row, header, ["客户/项目"]),
      customerType: "",
      supplier: value(row, header, ["供应商"]),
      demandType: `${value(row, header, ["模块名称"])} / ${value(row, header, ["产品/服务名称"])}`,
      stage: value(row, header, ["交付属性"]),
      amount,
      amountWan: amount ? amount / 10000 : 0,
      month: "",
      date: "",
      sourceUrl: "",
      action: "供应链复核：按供应商统计成本、故障、交付响应和替代方案。",
    });
  }
}

const allDetails = [...publicUnique, ...internalProjects, ...deliveryDetails];

function group(records, keyFn, aggregators = {}) {
  const m = new Map();
  for (const r of records) {
    const key = keyFn(r) || "未填写";
    if (!m.has(key)) m.set(key, { key, count: 0, amount: 0, publicAmount: 0, internalAmount: 0, sample: "" });
    const item = m.get(key);
    item.count += 1;
    item.amount += r.amount || 0;
    if (r.recordType === "公开采购/中标") item.publicAmount += r.amount || 0;
    if (r.recordType !== "公开采购/中标") item.internalAmount += r.amount || 0;
    if (!item.sample) item.sample = r.title || r.buyer || r.supplier || "";
    for (const [name, fn] of Object.entries(aggregators)) item[name] = fn(item[name], r);
  }
  return [...m.values()].sort((a, b) => b.amount - a.amount || b.count - a.count);
}

function top(records, keyFn, n = 20) {
  return group(records, keyFn).slice(0, n);
}

function csvEscape(v) {
  const s = String(v ?? "").trimEnd();
  return /[",\n\r]/.test(s) ? `"${s.replace(/"/g, '""')}"` : s;
}

function writeCsv(filePath, header, rows) {
  return fs.writeFile(filePath, [header, ...rows].map((r) => r.map(csvEscape).join(",")).join("\n"), "utf8");
}

function colName(n) {
  let s = "";
  while (n > 0) {
    const m = (n - 1) % 26;
    s = String.fromCharCode(65 + m) + s;
    n = Math.floor((n - 1) / 26);
  }
  return s;
}

function rowIndex(cell) {
  return Number(cell.match(/\d+/)[0]);
}

function colIndex(cell) {
  const letters = cell.match(/[A-Z]+/i)[0].toUpperCase();
  let n = 0;
  for (const ch of letters) n = n * 26 + ch.charCodeAt(0) - 64;
  return n;
}

function writeSheet(sheet, startCell, values) {
  if (!values.length) return;
  const rows = values.length;
  const cols = Math.max(...values.map((r) => r.length));
  const normalized = values.map((r) => [...r, ...Array(cols - r.length).fill("")]);
  const startCol = colIndex(startCell);
  const startRow = rowIndex(startCell);
  sheet.getRange(`${startCell}:${colName(startCol + cols - 1)}${startRow + rows - 1}`).values = normalized;
}

function header(sheet, range) {
  const r = sheet.getRange(range);
  r.format.fill = "#17365D";
  r.format.font = { color: "#FFFFFF", bold: true };
  r.format.wrapText = true;
  r.format.horizontalAlignment = "center";
}

function table(sheet, range) {
  const r = sheet.getRange(range);
  r.format.borders = { preset: "all", style: "thin", color: "#D9E1EA" };
  r.format.wrapText = true;
}

function amountWan(n) {
  return Number(((n || 0) / 10000).toFixed(2));
}

const publicAmount = publicUnique.reduce((s, r) => s + r.amount, 0);
const internalAmount = internalProjects.reduce((s, r) => s + r.amount, 0);
const deliveryAmount = deliveryDetails.reduce((s, r) => s + r.amount, 0);
const provinceSummary = group(publicUnique, (r) => r.province);
const citySummary = group(publicUnique, (r) => `${r.province || "未填写"}-${r.city || "未填写"}`);
const districtSummary = group(publicUnique, (r) => `${r.province || "未填写"}-${r.city || "未填写"}-${r.district || "未填写"}`);
const customerTypeSummary = top(publicUnique, (r) => r.customerType, 30);
const demandTypeSummary = top(publicUnique, (r) => r.demandType, 30);
const supplierSummary = top([...publicUnique, ...deliveryDetails], (r) => r.supplier, 40);
const internalCustomerSummary = top(internalProjects, (r) => r.customerType, 20);

const detailHeader = [
  "记录类型",
  "证据等级",
  "省份",
  "城市",
  "区县",
  "项目/标题",
  "客户/招标单位",
  "客户类型",
  "供应商/中标单位",
  "需求/产品类型",
  "阶段/交付属性",
  "金额_元",
  "金额_万元",
  "月份",
  "日期",
  "建议动作",
  "来源文件",
  "来源URL",
];
const detailRows = allDetails.map((r) => [
  r.recordType,
  r.evidenceLevel,
  r.province,
  r.city,
  r.district,
  compact(r.title, 180),
  compact(r.buyer, 160),
  r.customerType,
  compact(r.supplier, 180),
  compact(r.demandType, 160),
  compact(r.stage, 120),
  r.amount || "",
  r.amountWan ? Number(r.amountWan.toFixed(2)) : "",
  r.month,
  r.date,
  r.action,
  r.sourceFile,
  r.sourceUrl,
]);

await fs.mkdir(taskDir, { recursive: true });
await fs.mkdir(outputDir, { recursive: true });
await writeCsv(detailCsv, detailHeader, detailRows);

const workbook = Workbook.create();
const dashboard = workbook.worksheets.add("Dashboard");
const regionSheet = workbook.worksheets.add("地区汇总");
const typeSheet = workbook.worksheets.add("客户需求");
const supplierSheet = workbook.worksheets.add("供应商");
const publicSheet = workbook.worksheets.add("公开采购明细");
const internalSheet = workbook.worksheets.add("内部项目");
const deliverySheet = workbook.worksheets.add("供应链明细");
const sourceSheet = workbook.worksheets.add("来源边界");
const actionSheet = workbook.worksheets.add("行动清单");

writeSheet(dashboard, "A1", [
  ["全量地区与项目线索分析", "", "", "", "", ""],
  ["生成时间", checkedAt, "范围", "公开采购/中标 + 内部项目收入 + 内部供应链拆分", "", ""],
  ["公开采购去重记录", publicUnique.length, "公开采购金额万元", amountWan(publicAmount), "覆盖省份", provinceSummary.length],
  ["内部项目数", internalProjects.length, "内部收入金额万元", amountWan(internalAmount), "供应链拆分行", deliveryDetails.length],
  ["供应链拆分金额万元", amountWan(deliveryAmount), "全量明细行", allDetails.length, "来源文件数", publicAwardFiles.length + 2 + strategyFiles.length],
]);
dashboard.getRange("A1:F1").merge();
dashboard.getRange("A1:F1").format.fill = "#17365D";
dashboard.getRange("A1:F1").format.font = { color: "#FFFFFF", bold: true, size: 18 };
table(dashboard, "A2:F5");
writeSheet(dashboard, "A8", [["Top 省份", "记录数", "金额万元"], ...provinceSummary.slice(0, 12).map((r) => [r.key, r.count, amountWan(r.amount)])]);
writeSheet(dashboard, "E8", [["Top 城市", "记录数", "金额万元"], ...citySummary.slice(0, 12).map((r) => [r.key, r.count, amountWan(r.amount)])]);
writeSheet(dashboard, "I8", [["Top 供应商/中标单位", "记录数", "金额万元"], ...supplierSummary.slice(0, 12).map((r) => [r.key, r.count, amountWan(r.amount)])]);
header(dashboard, "A8:C8");
header(dashboard, "E8:G8");
header(dashboard, "I8:K8");
table(dashboard, "A8:C20");
table(dashboard, "E8:G20");
table(dashboard, "I8:K20");

writeSheet(regionSheet, "A1", [["省份", "记录数", "金额万元", "样例项目"], ...provinceSummary.map((r) => [r.key, r.count, amountWan(r.amount), r.sample])]);
writeSheet(regionSheet, "F1", [["省市", "记录数", "金额万元", "样例项目"], ...citySummary.map((r) => [r.key, r.count, amountWan(r.amount), r.sample])]);
writeSheet(regionSheet, "K1", [["省市区县", "记录数", "金额万元", "样例项目"], ...districtSummary.slice(0, 120).map((r) => [r.key, r.count, amountWan(r.amount), r.sample])]);
header(regionSheet, "A1:D1");
header(regionSheet, "F1:I1");
header(regionSheet, "K1:N1");
table(regionSheet, `A1:D${provinceSummary.length + 1}`);
table(regionSheet, `F1:I${citySummary.length + 1}`);
table(regionSheet, `K1:N${Math.min(districtSummary.length, 120) + 1}`);

writeSheet(typeSheet, "A1", [["客户类型", "记录数", "金额万元", "样例"], ...customerTypeSummary.map((r) => [r.key, r.count, amountWan(r.amount), r.sample])]);
writeSheet(typeSheet, "F1", [["需求类型", "记录数", "金额万元", "样例"], ...demandTypeSummary.map((r) => [r.key, r.count, amountWan(r.amount), r.sample])]);
writeSheet(typeSheet, "K1", [["内部客户类型", "项目数", "金额万元", "样例"], ...internalCustomerSummary.map((r) => [r.key, r.count, amountWan(r.amount), r.sample])]);
header(typeSheet, "A1:D1");
header(typeSheet, "F1:I1");
header(typeSheet, "K1:N1");
table(typeSheet, `A1:D${customerTypeSummary.length + 1}`);
table(typeSheet, `F1:I${demandTypeSummary.length + 1}`);
table(typeSheet, `K1:N${internalCustomerSummary.length + 1}`);

writeSheet(supplierSheet, "A1", [["供应商/中标单位", "记录数", "金额万元", "样例"], ...supplierSummary.map((r) => [r.key, r.count, amountWan(r.amount), r.sample])]);
header(supplierSheet, "A1:D1");
table(supplierSheet, `A1:D${supplierSummary.length + 1}`);

writeSheet(publicSheet, "A1", [detailHeader, ...publicUnique.map((r) => [
  r.recordType, r.evidenceLevel, r.province, r.city, r.district, compact(r.title, 180), compact(r.buyer, 160), r.customerType,
  compact(r.supplier, 180), compact(r.demandType, 160), compact(r.stage, 120), r.amount || "", r.amountWan ? Number(r.amountWan.toFixed(2)) : "",
  r.month, r.date, r.action, r.sourceFile, r.sourceUrl,
])]);
header(publicSheet, "A1:R1");
table(publicSheet, `A1:R${publicUnique.length + 1}`);

writeSheet(internalSheet, "A1", [detailHeader, ...internalProjects.map((r) => [
  r.recordType, r.evidenceLevel, r.province, r.city, r.district, compact(r.title, 180), compact(r.buyer, 160), r.customerType,
  compact(r.supplier, 180), compact(r.demandType, 160), compact(r.stage, 120), r.amount || "", r.amountWan ? Number(r.amountWan.toFixed(2)) : "",
  r.month, r.date, r.action, r.sourceFile, r.sourceUrl,
])]);
header(internalSheet, "A1:R1");
table(internalSheet, `A1:R${internalProjects.length + 1}`);

writeSheet(deliverySheet, "A1", [detailHeader, ...deliveryDetails.map((r) => [
  r.recordType, r.evidenceLevel, r.province, r.city, r.district, compact(r.title, 180), compact(r.buyer, 160), r.customerType,
  compact(r.supplier, 180), compact(r.demandType, 160), compact(r.stage, 120), r.amount || "", r.amountWan ? Number(r.amountWan.toFixed(2)) : "",
  r.month, r.date, r.action, r.sourceFile, r.sourceUrl,
])]);
header(deliverySheet, "A1:R1");
table(deliverySheet, `A1:R${deliveryDetails.length + 1}`);

writeSheet(sourceSheet, "A1", [
  ["来源", "类型", "处理方式", "边界"],
  ...publicAwardFiles.map((f) => [f, "公开采购/中标 CSV", "合并后按 URL 或项目+单位+金额去重", "C 级公开线索，不能直接代表商机确定性"]),
  [internalProjectFile, "内部收入项目汇总", "保留项目、客户类型、产品类型、金额和状态", "B/D 级内部样本，需财务/合同/验收补证"],
  [deliveryDetailFile, "内部供应链拆分", "保留项目、模块、产品/服务、供应商、金额和交付属性", "B/D 级拆分，需补成本、故障、售后和替代方案"],
  ...strategyFiles.map((f) => [f, "策略/证据辅助 CSV", "仅登记覆盖，不进入金额汇总", "用于解释和后续补证，不做金额累计"]),
]);
header(sourceSheet, "A1:D1");
table(sourceSheet, `A1:D${publicAwardFiles.length + strategyFiles.length + 3}`);

writeSheet(actionSheet, "A1", [
  ["优先级", "动作", "原因", "产出"],
  ["P0", "按地区筛 Top 省市和高金额项目", "公开采购金额集中度能指向优先市场", "省市优先级和重点项目回访表"],
  ["P0", "把中标单位和内部供应商合并看", "同一公司可能既是竞品又是硬件伙伴", "竞品/伙伴双重身份清单"],
  ["P1", "把学校、银行、国企三类客户分开", "采购动机、付款方式、产品包和销售路径不同", "三类 ICP 和话术差异"],
  ["P1", "内部项目补毛利、回款、交付工时", "当前金额不是经营质量，不能直接判断好项目", "经营复核表"],
  ["P2", "从策略辅助 CSV 补证", "市场分析文件是方法和证据入口，不适合直接累计", "下一轮市场研究补证队列"],
]);
header(actionSheet, "A1:D1");
table(actionSheet, "A1:D6");

for (const sh of [dashboard, regionSheet, typeSheet, supplierSheet, publicSheet, internalSheet, deliverySheet, sourceSheet, actionSheet]) {
  sh.showGridLines = false;
  sh.getRange("A:Z").format.font = { name: "Microsoft YaHei", size: 12 };
}
for (const sh of [publicSheet, internalSheet, deliverySheet]) {
  sh.freezePanes.freezeRows(1);
  sh.getRange("A:E").format.columnWidthPx = 95;
  sh.getRange("F:F").format.columnWidthPx = 360;
  sh.getRange("G:G").format.columnWidthPx = 260;
  sh.getRange("I:J").format.columnWidthPx = 250;
  sh.getRange("P:P").format.columnWidthPx = 330;
  sh.getRange("Q:R").format.columnWidthPx = 300;
  sh.getRange("L:M").format.numberFormat = [["#,##0", "#,##0.00"]];
}
dashboard.getRange("A:K").format.columnWidthPx = 170;
regionSheet.getRange("A:N").format.columnWidthPx = 180;
typeSheet.getRange("A:N").format.columnWidthPx = 180;
supplierSheet.getRange("A:A").format.columnWidthPx = 320;
supplierSheet.getRange("D:D").format.columnWidthPx = 360;
sourceSheet.getRange("A:A").format.columnWidthPx = 460;
sourceSheet.getRange("B:D").format.columnWidthPx = 260;
actionSheet.getRange("B:D").format.columnWidthPx = 360;

await fs.mkdir(outputDir, { recursive: true });
const inspect = await workbook.inspect({ kind: "table", range: "Dashboard!A1:K22", include: "values", tableMaxRows: 22, tableMaxCols: 11 });
console.log(inspect.ndjson);
const errors = await workbook.inspect({ kind: "match", searchTerm: "#REF!|#DIV/0!|#VALUE!|#NAME\\?|#N/A", options: { useRegex: true, maxResults: 300 }, summary: "final formula error scan" });
console.log(errors.ndjson);

const output = await SpreadsheetFile.exportXlsx(workbook);
await output.save(outputXlsx);

const summary = `# 全量地区与项目线索分析

- 生成时间：${checkedAt}
- 用户可见 Excel：\`${outputRel}/all-region-signal-analysis-20260717.xlsx\`
- 明细 CSV：\`${taskRel}/all-region-signal-records.csv\`

## 覆盖范围

- 公开采购/中标：原始 ${publicRecords.length} 条，去重后 ${publicUnique.length} 条。
- 内部项目收入：${internalProjects.length} 条。
- 内部供应链/交付拆分：${deliveryDetails.length} 条。
- 覆盖省份：${provinceSummary.length} 个。

## 核心数字

- 公开采购/中标金额口径：${amountWan(publicAmount)} 万元。
- 内部项目收入金额口径：${amountWan(internalAmount)} 万元。
- 内部供应链拆分金额口径：${amountWan(deliveryAmount)} 万元。

## 怎么用

1. 先看 Dashboard：判断总体规模、Top 省份、Top 城市和 Top 供应商。
2. 看「地区汇总」：选择重点市场和高金额区域。
3. 看「客户需求」：拆学校、银行、国企等客户类型，以及硬件、软件、维保等需求类型。
4. 看「供应商」：识别竞品、硬件伙伴和潜在替代供应商。
5. 看三张明细表：回到具体项目、金额、来源 URL 或内部来源文件。

## 边界

- 公开采购为 C 级市场线索，不等于商机确定性。
- 内部项目收入和供应链拆分为 B/D 级内部样本，仍需财务、合同、验收、回款、毛利和交付工时补证。
- 本轮不包含联系人电话、token、cookie、客户敏感原文。
- 策略辅助 CSV 仅作为来源覆盖和后续补证入口，不做金额累计。
`;
await fs.writeFile(summaryPath, summary, "utf8");
await fs.rm(`${outputXlsx}.inspect.ndjson`, { force: true });

console.log(JSON.stringify({
  outputXlsx,
  detailCsv,
  summaryPath,
  publicRaw: publicRecords.length,
  publicUnique: publicUnique.length,
  internalProjects: internalProjects.length,
  deliveryRows: deliveryDetails.length,
  provinceCount: provinceSummary.length,
  publicAmount,
  internalAmount,
  deliveryAmount,
}));