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

const taskDir = path.dirname(new URL(import.meta.url).pathname);
const inputTemplate = "/Users/jack/同步空间/cpt/05_经营管理与会议/005_日常管理/周例会（事业部层面）/2026年/数字技术中心&交付实施部周报 20260810.xlsx";
const currentDataPath = path.resolve(taskDir, "../product-project-weekly-20260815/weekly-data.json");
const outputDir = path.join(taskDir, "outputs");
const outputPath = path.join(outputDir, "数字技术中心&交付实施部周报_2026-W33_沿用20260810模板_8月任务对标版_20260816.xlsx");
const publicHtmlUrl = "https://img.kxunpt.cn/public/ai-marketing-platform/work/2026-08-06-smart-canteen-marketing-infographics/smart-canteen-marketing-infographics-v2.html";

const current = JSON.parse(await fs.readFile(currentDataPath, "utf8"));
const htmlMeta = JSON.parse(await fs.readFile(path.join(taskDir, "inputs/smart-canteen-5-plus-2-html-fullpage.json"), "utf8"));

const C = {
  navy: "#17365D", blue: "#1F4E78", teal: "#0F766E", green: "#548235",
  orange: "#F28C28", red: "#C65911", text: "#1F2937", gray: "#667085",
  white: "#FFFFFF", border: "#C9D3DD", paleBlue: "#DDEBF7", paleTeal: "#DFF3EF",
  paleGreen: "#E2F0D9", paleOrange: "#FFF2CC", paleRed: "#FCE4D6", paleGray: "#F3F6F8",
};
const FONT = { name: "微软雅黑", size: 12, color: C.text };
const BORDER = { preset: "all", style: "thin", color: C.border };

const augustTasks = [
  [1, "项目治理", "统一需求台账", "22个项目全部需求进入共同台账；提出人、原因、优先级、合同范围、计划时间、验收标准齐全。", "未见全量台账、覆盖率或变更确认记录。", "Build", "待补证", "高", "先在三全、滨州、城市副中心试点，再审计22项目覆盖。", "王朔 / 各项目经理", "8/20试点；8/31覆盖审计", "台账链接、覆盖清单、变更确认", "A/B级待补"],
  [2, "项目治理", "项目协同 Agent", "每日识别逾期、无负责人、前置未完成任务；关闭7月25项无状态记录。", "本周材料仅提出项目协作/部署/运维 Agent 真实任务试跑方向，未见22项目验收结果。", "Build", "待补证", "高", "冻结字段模型与日检规则，跑一次全量项目扫描并人工复核。", "王朔 / 产品项目组", "8/23首轮扫描", "日检结果、人工修正、关闭清单", "B/C级"],
  [3, "交付机制", "上线一周现场陪跑→远程运维", "核心流程跑通且连续3天无严重运营问题后转远程；遗留问题均有责任人与时限。", "滨州处试运行与问题闭环，城市副中心处稳定性修复；未见连续3天门禁与正式交接记录。", "Validate", "进行中", "高", "选择滨州作为首个门禁样板，补连续运行、问题分级和远程交接单。", "李潇 / 兰海军", "8/23", "运行日志、问题清单、交接单", "B级局部"],
  [4, "AI赋能", "部署 Agent + 运维 Agent 低风险试点", "自动环境检查、配置生成、部署步骤、结果验证；生产/支付/安全关键动作人工确认。", "运维 Agent V0 已存在；本周口径为进入真实任务试跑计划，未见本月低风险项目完整验收。", "Build→Validate", "进行中", "中", "选1个低风险环境跑完整前后对比，记录人工门禁与节省时间。", "赖清涛 / 王朔", "8/23", "输入、产物、人工修改、耗时对比", "B级能力；A/B级试点待补"],
  [5, "三项资产试点", "城市副中心政务云/网络检查模板", "至少形成部署流程、测试清单、问题处理手册各1套。", "三块大屏点亮，460稳定性完成本轮修复；全流程回归和压测仍待完成。", "Validate", "进行中", "高", "用本轮网络与设备问题反推检查项，完成回归/压测后封版。", "李潇 / 尹旭亮", "8/23", "检查模板、压测记录、问题手册", "B级局部"],
  [6, "三项资产试点", "三全高可用部署模板", "高可用部署流程、测试清单、故障切换与问题手册可复用。", "双应用与MQTT闭环；高可用角色仍未全部通过，8/14原节点未闭环，现转8/23验证。", "Build→Validate", "偏离/延期", "高", "按角色逐项关门，补全流程、故障切换、支付宝和回退证据。", "赖清涛 / 尹旭亮", "8/23", "5角色清单、切换记录、问题清单", "A级任务记录/B级周报"],
  [7, "三项资产试点", "滨州第三方接口/全流程测试模板", "一卡通、SSO、数据中台、IOC等联调流程与全流程测试资产成套。", "现场走访完成，登录、退款、离线消费、绑盘等问题持续闭环；资产包未见封版。", "Validate", "进行中", "高", "按接口—业务流—异常流组织测试，补远程支持和问题手册。", "李潇 / 兰海军", "8/23", "接口清单、全流程用例、问题手册", "B级"],
  [8, "产品主线", "政企供餐端营养模块", "菜单营养计算、餐段营养分析、超标提醒；需求基线、演示环境、测试用例、手册、销售口径各1版。", "政企营养原型完成初评，进入竞品分析与内容调整；其余验收物未见闭环证据。", "Build", "进行中", "高", "冻结三项功能与指标口径，8/23做阶段评审，8/31按五件套收口。", "李潇 / 王朔", "8/23评审；8/31收口", "需求、Demo、用例、手册、话术", "B/C级"],
  [9, "产品主线", "智餐助手 V1.1", "巩固功能/人员/卡/订单/菜单查询，挂接手册视频；具备权限矩阵、问题集、真实数据验证和人工兜底。", "7月V1已上线；本周材料未见V1.1新增验证、有效问答率或人工转接记录。", "Validate待启动", "待补证", "中", "选真实问题集做权限与准确性回归，记录人工兜底和平均处理时长。", "产品 / 客服 / 运维", "8/28", "问题集、回归记录、转人工规则、耗时", "7月B级；本周待补"],
  [10, "产品主线", "场景化标准产品包", "营养、结算、食安、进销存与硬件形成政企版/学校版产品清单及模块、报价、交付边界。", "已有三条产品线、方案包与信息化交付闭环底稿；报价边界、合同交接和第二项目复用指标仍缺。", "Standardize进行中", "进行中", "高", "以2个项目反校模块/报价/交付边界，形成可售、可交接、可验收清单。", "王朔 / 李潇 / 销售", "8/28", "两版产品清单、三类边界、复用记录", "B级底稿"],
  [11, "研发底座", "卡码脸全方法兼容与性能测试", "主/异常流程、性能记录、标准版本包、测试报告；设备型号矩阵和阈值明确。", "硬件标准化进入兼容实测计划；本周明确该项未闭环，已超过8/3—8/7计划窗。", "Validate未闭环", "偏离/延期", "高", "冻结型号矩阵和性能阈值，按卡/码/脸逐项跑主流程与异常流。", "尹旭亮 / 测试", "8/23恢复计划", "型号矩阵、性能记录、版本包、报告", "B级"],
  [12, "研发底座", "AI菜品识别算法与设备闭环", "模型、学习替换、菜谱同步、现场识别、回退流程闭环。", "7月已有初始化、相机、算法激活、学习替换和菜谱同步基础；本周未见现场识别与回退验收。", "Build→Validate", "偏离/延期", "高", "在三全或另一真实点位补现场识别准确性、异常回退与恢复记录。", "赖清涛 / 硬件算法", "8/23恢复计划", "现场记录、异常样例、回退用例", "7月B级；本周待补"],
  [13, "研发底座", "一体机功能帮助与页面优化", "菜谱/人脸页面、模块帮助、标准安装包、操作手册及任务流程验证。", "周会明确保康一体机帮助未闭环；已超过8/3—8/14计划窗。", "Build未闭环", "偏离/延期", "中", "按真实任务路径补帮助入口、安装包和手册，避免只做页面美化。", "尹旭亮 / 产品", "8/23恢复计划", "安装包、操作手册、任务流验证", "B级"],
  [14, "重点项目", "中央美院 8/20 上线门禁", "读卡器、服务器、Windows程序联调、业务验证四类门禁全部有放行证据。", "方案、合同型号和用户流程已确认，现场联调进行中；上线日期仍是条件目标。", "Validate", "进行中", "高", "8/18完成预检，逐门禁给出放行/不放行和遗留项。", "李潇 / 孙海琴 / 赖清涛", "8/18预检；8/20目标", "四类门禁表、联调记录、放行结论", "B级"],
  [15, "重点项目", "人大附中 / 大兴 / 保康三中", "按第二优先级分别形成项目节点、验收证据和可复用资产。", "人大附中有电子菜牌链路与发运记录但仍待连续运行/验收；大兴本周无新增证据；保康一体机帮助未闭环。", "混合阶段", "待补证", "中", "拆成3条独立项目计划，补责任人、时间、验收物，不再合并汇报。", "对应项目经理", "8/20补计划", "三项目独立计划与证据", "B级/缺口并存"],
  [16, "存量收口", "7月未验收项目清零", "7月22项进行中/延迟/未开始/暂停与25项无状态记录全部有责任人、日期和验收结果。", "本周只覆盖8项重点明细，未见7月存量全量关闭审计。", "Control待建立", "待补证", "高", "从7月台账生成未验收清单，逐项补Owner、日期、验收物和状态变更证据。", "王朔 / 项目经理", "8/31", "全量差异清单、关闭证明、遗留升级", "7月A级；本周待补"],
  [17, "新增主线", "网信办纯内网与三级等保", "完成开发、部署、自测、首轮扫描、整改复测与现场检查；每一步单独留证。", "纯内网架构沟通与8张采集表完成；备案材料已提交；扫描和外部检查未发生。", "Build→Validate", "进行中", "高", "8/20前完成环境、自测和首轮扫描准备；外部日期单列跟踪。", "王朔 / 赖清涛", "8/20首轮扫描目标", "环境记录、扫描报告、整改复测、检查结论", "B级"],
];

const nextActions = [
  ["P0", "中央美院", "完成四类上线门禁预检并出放行结论", "读卡器/服务器/程序/业务验证逐项有证据；未过项明确Owner与恢复时间", "李潇 / 孙海琴 / 赖清涛", "8/18预检；8/20目标", "上线日期依赖联调，不能提前承诺", "四类门禁表 + 联调记录"],
  ["P0", "三全", "完成全流程、高可用、支付宝验证", "5角色状态可追溯；故障切换、回退、支付异常与问题清单齐全", "赖清涛 / 尹旭亮", "8/23", "8/14节点已偏离，必须按门禁恢复", "测试用例 + 切换记录 + 问题清单"],
  ["P0", "三级等保", "完成剩余开发、部署、自测和首轮扫描准备", "环境可复现，自测有记录；外部扫描日期与责任边界明确", "王朔 / 赖清涛", "8/20目标", "备案提交不等于认证通过", "环境记录 + 自测 + 扫描结果"],
  ["P0", "金斯瑞", "完成账户规则测试 Review 与发布判断", "形成通过/不通过、影响范围、回退与发布结论", "孙海琴 / 孙倩倩", "8/18建议", "局部上线不等于项目验收", "评审纪要 + 测试记录"],
  ["P1", "城市副中心", "完成全流程回归、性能与压力测试", "460设备与三块大屏的稳定性、心跳、异常恢复记录齐全", "李潇 / 尹旭亮", "8/23", "单点修复不能替代整体稳定性", "回归报告 + 压测记录 + 检查模板"],
  ["P1", "滨州", "关闭登录/称重/绑盘/离线消费问题并形成测试模板", "核心流程连续运行，问题分级与远程交接完成", "李潇 / 兰海军", "8/23", "试运行不等于验收与回款", "全流程用例 + 问题手册 + 交接单"],
  ["P1", "政企营养模块", "冻结三项功能并完成阶段评审", "需求基线、Demo、测试用例、手册、销售口径五件套有明确完成度", "李潇 / 王朔", "8/23评审；8/31收口", "原型初评不等于产品完成", "五件套清单 + 评审结论"],
  ["P1", "项目治理", "上线三项目需求台账试点并扫描22项目", "试点字段完整；全量扫描能识别逾期、无Owner、前置阻塞和无验收物", "王朔 / 项目经理", "8/20试点；8/23首扫", "没有全量证据不得写100%覆盖", "台账 + Agent日检 + 人工复核"],
  ["P1", "部署/运维 Agent", "选低风险环境跑一次完整试点", "自动检查、配置、执行建议、结果验证与人工门禁全程留痕", "赖清涛 / 王朔", "8/23", "禁止无门禁自动生产上线", "前后耗时 + 人工修改 + 审批记录"],
  ["P1", "标准产品包", "完成政企版/学校版边界评审", "模块、报价、交付边界与2个项目反校结果明确", "王朔 / 李潇 / 销售", "8/28", "产品地图不等于可报价可验收", "产品清单 + 三类边界 + 反校记录"],
  ["P2", "智餐助手 V1.1", "完成真实问题集、权限和人工兜底回归", "有效问答、人工转接、平均处理时长至少形成试点基线", "产品 / 客服 / 运维", "8/28", "V1上线不等于V1.1达标", "问题集 + 指标记录 + 兜底规则"],
  ["P2", "卡码脸测试", "恢复延期计划并完成型号矩阵", "卡/码/脸主流程、异常流、性能阈值和版本包齐全", "尹旭亮 / 测试", "8/23", "计划窗口已过", "型号矩阵 + 测试报告"],
  ["P2", "AI菜品识别", "补现场识别与回退闭环", "真实点位有准确性样例、异常回退与恢复记录", "赖清涛 / 算法硬件", "8/23", "初始化/学习不等于现场闭环", "现场记录 + 回退用例"],
  ["P2", "一体机帮助", "交付标准安装包、帮助入口和操作手册", "按真实任务流完成安装、操作、异常处理验证", "尹旭亮 / 产品", "8/23", "避免只做页面美化", "安装包 + 手册 + 任务流验证"],
  ["P2", "三项标准资产", "把三项目资产封版并做第二项目复用演练", "每项目1流程+1清单+1手册；复用准备时间有基线与实测", "项目组 / 交付组", "8/31", "“时间下降30%”未测量前只能作为目标", "资产包 + 复用计时记录"],
  ["P2", "7月存量项目", "完成未验收与无状态项目全量审计", "每项有Owner、日期、验收物、升级或关闭证据", "王朔 / 项目经理", "8/31", "本周8项重点不能替代全量盘点", "差异清单 + 关闭/升级记录"],
];

function pngDataUrl(bytes) { return `data:image/png;base64,${Buffer.from(bytes).toString("base64")}`; }
async function loadPng(file) { return pngDataUrl(await fs.readFile(file)); }
function cell(sheet, address, value) { sheet.getRange(address).values = [[value]]; }
function mergeValue(sheet, range, value) { sheet.mergeCells(range); cell(sheet, range.split(":")[0], value); }
function title(sheet, range, value) {
  mergeValue(sheet, range, value);
  sheet.getRange(range).format = { fill: C.navy, font: { name: "微软雅黑", size: 18, bold: true, color: C.white }, verticalAlignment: "center", wrapText: true };
}
function subTitle(sheet, range, value, fill = C.paleBlue, color = C.blue) {
  mergeValue(sheet, range, value);
  sheet.getRange(range).format = { fill, font: { name: "微软雅黑", size: 12, bold: true, color }, verticalAlignment: "center", wrapText: true, borders: BORDER };
}
function header(range) {
  range.format = { fill: C.orange, font: { name: "微软雅黑", size: 12, bold: true, color: "#111827" }, horizontalAlignment: "center", verticalAlignment: "center", wrapText: true, borders: BORDER };
}
function body(range) {
  range.format = { font: FONT, verticalAlignment: "center", wrapText: true, borders: BORDER };
}
function statusColor(status) {
  if (/偏离|延期|高风险/.test(status)) return C.paleRed;
  if (/待补证|待确认/.test(status)) return C.paleOrange;
  if (/进行中|Validate|Build/.test(status)) return C.paleBlue;
  return C.paleGreen;
}
function widths(sheet, map, lastRow) {
  for (const [col, width] of map) sheet.getRange(`${col}1:${col}${lastRow}`).format.columnWidth = width;
}
function resetSheet(sheet, range) {
  try { sheet.getRange(range).unmerge(); } catch {}
  sheet.getRange(range).clear({ applyTo: "all" });
  sheet.deleteAllDrawings();
  sheet.showGridLines = false;
}

await fs.mkdir(outputDir, { recursive: true });
const wb = await SpreadsheetFile.importXlsx(await FileBlob.load(inputTemplate));
wb.comments.setSelf({ displayName: "Jack" });

// 模板含内部演示账号/密码；周报保留入口与素材，但不保存明文凭据或旧嵌入图。
const credentials = wb.worksheets.getItem("07-系统链接账号素材");
credentials.deleteAllDrawings();
cell(credentials, "A1", "07-系统链接与演示素材（账号密码已脱敏）");
cell(credentials, "A3", "快速找到系统入口和演示素材；内部账号密码通过授权渠道另行获取。");
cell(credentials, "A4", "按所属产品线筛选；本周报不保存任何账号或密码明文。");
credentials.getRange("E8:F100").clear({ applyTo: "contents" });
for (const r of [8, 9, 14]) {
  cell(credentials, `E${r}`, "内部账号另行获取");
  cell(credentials, `F${r}`, "不在周报中保存");
}

// 1) 项目经营大屏：以8月任务门禁重构，移除上周旧截图与旧日期数据。
const dashboard = wb.worksheets.getItem("项目经营大屏");
resetSheet(dashboard, "A1:J80");
title(dashboard, "A1:J1", "数字技术中心 & 交付实施部周报｜2026-W33 8月任务对标总览");
subTitle(dashboard, "A2:J2", "总体判断：本周处于 Build / Validate 并行阶段；重点项目有实质进展，但8月治理、标准资产与研发底座仍存在未闭环和延期项。", C.paleTeal, C.teal);
mergeValue(dashboard, "A3:J3", "周期：2026-08-10—2026-08-16（材料截至8月15日）｜本表把7月月报中的8月目标作为基线，不以局部上线、发运、材料提交替代验收。");
dashboard.getRange("A3:J3").format = { fill: C.paleGray, font: { ...FONT, color: C.gray }, wrapText: true };

const cards = [
  ["本周项目明细", "8项", C.paleBlue, C.blue],
  ["8月任务对标", `${augustTasks.length}项`, C.paleTeal, C.teal],
  ["偏离/延期", "4项", C.paleRed, C.red],
  ["待补证", "5项", C.paleOrange, "#9A6700"],
];
const cardRanges = ["A5:B8", "C5:E8", "F5:H8", "I5:J8"];
for (let i = 0; i < cards.length; i += 1) {
  const [label, value, fill, color] = cards[i];
  mergeValue(dashboard, cardRanges[i], `${value}\n${label}`);
  dashboard.getRange(cardRanges[i]).format = { fill, font: { name: "微软雅黑", size: 15, bold: true, color }, horizontalAlignment: "center", verticalAlignment: "center", wrapText: true, borders: { preset: "outside", style: "medium", color } };
}

subTitle(dashboard, "A10:J10", "管理层先看这三件事", C.navy, C.white);
const management = [
  ["P0放行", "央美8/20、三全8/23、三级等保8/20均是条件目标；必须以门禁表、测试记录和外部结果逐项放行。", "本周锁定资源、Owner和不放行条件。"],
  ["8月偏差", "三全8/14、卡码脸8/7、AI菜品识别和一体机帮助8/14均未按原窗口闭环。", "8/20前形成恢复计划，8/23提供验证证据。"],
  ["系统建设", "需求台账、项目协同Agent、三项标准资产尚未形成全量闭环证据。", "先做3项目试点，再做22项目覆盖审计和第二项目复用。"],
];
dashboard.getRange("A11:J11").values = [["主题", "判断", "", "", "", "", "", "下一管理动作", "", ""]];
dashboard.mergeCells("B11:G11"); dashboard.mergeCells("H11:J11"); header(dashboard.getRange("A11:J11"));
for (let i = 0; i < management.length; i += 1) {
  const r = 12 + i;
  cell(dashboard, `A${r}`, management[i][0]);
  dashboard.mergeCells(`B${r}:G${r}`); cell(dashboard, `B${r}`, management[i][1]);
  dashboard.mergeCells(`H${r}:J${r}`); cell(dashboard, `H${r}`, management[i][2]);
  body(dashboard.getRange(`A${r}:J${r}`));
  dashboard.getRange(`A${r}`).format.fill = i === 1 ? C.paleRed : C.paleBlue;
  dashboard.getRange(`A${r}:J${r}`).format.rowHeight = 58;
}

subTitle(dashboard, "A17:J17", "8月主线阶段判断｜目标—当前阶段—下一门禁", C.blue, C.white);
dashboard.getRange("A18:J18").values = [["主线", "目标", "当前阶段", "状态", "", "关键差距", "", "下一门禁", "", "截止"]];
dashboard.mergeCells("D18:E18"); dashboard.mergeCells("F18:G18"); dashboard.mergeCells("H18:I18"); header(dashboard.getRange("A18:J18"));
const overviewRows = [
  ["项目治理", "统一台账+协同Agent", "Build", "待补证", "未见22项目覆盖审计", "三项目试点+全量首扫", "8/23"],
  ["交付机制", "陪跑转运维+三项模板", "Validate", "进行中", "缺连续运行、交接与资产封版", "滨州/三全/副中心分别关门", "8/23"],
  ["产品主线", "营养模块+助手V1.1+标准包", "Build / Standardize", "进行中", "五件套、指标和三类边界不全", "阶段评审+两项目反校", "8/28-31"],
  ["研发底座", "卡码脸+AI识别+一体机", "Build / Validate", "偏离/延期", "原计划窗口已过且验收物不全", "恢复排期+真实点位验证", "8/23"],
  ["重点项目", "央美/三全/存量收口", "Validate / Control", "高风险", "里程碑条件与存量清零未闭环", "门禁放行+全量差异审计", "8/31"],
];
for (let i = 0; i < overviewRows.length; i += 1) {
  const r = 19 + i; const x = overviewRows[i];
  cell(dashboard, `A${r}`, x[0]); cell(dashboard, `B${r}`, x[1]); cell(dashboard, `C${r}`, x[2]);
  dashboard.mergeCells(`D${r}:E${r}`); cell(dashboard, `D${r}`, x[3]);
  dashboard.mergeCells(`F${r}:G${r}`); cell(dashboard, `F${r}`, x[4]);
  dashboard.mergeCells(`H${r}:I${r}`); cell(dashboard, `H${r}`, x[5]); cell(dashboard, `J${r}`, x[6]);
  body(dashboard.getRange(`A${r}:J${r}`)); dashboard.getRange(`D${r}:E${r}`).format.fill = statusColor(x[3]);
  dashboard.getRange(`A${r}:J${r}`).format.rowHeight = 58;
}
mergeValue(dashboard, "A26:J28", "证据边界：7月月报给出的是8月目标和建议验收物；本周周报、项目台账与研发记录只用于判断当前阶段。未见测试、运行、客户确认、验收或财务证据的事项，统一写为“待补证”，不推断完成率。");
dashboard.getRange("A26:J28").format = { fill: C.paleOrange, font: { ...FONT, color: "#9A6700" }, wrapText: true, verticalAlignment: "center", borders: BORDER };
widths(dashboard, [["A", 14], ["B", 22], ["C", 18], ["D", 13], ["E", 13], ["F", 22], ["G", 22], ["H", 22], ["I", 22], ["J", 15]], 80);
for (let r = 1; r <= 28; r += 1) dashboard.getRange(`A${r}:J${r}`).format.font = { name: "微软雅黑", size: r === 1 ? 18 : 12 };
dashboard.getRange("A1:J1").format.rowHeight = 36; dashboard.getRange("A2:J2").format.rowHeight = 42; dashboard.getRange("A3:J3").format.rowHeight = 30;
dashboard.freezePanes.freezeRows(3);

// 2) 交付中项目：替换为本周8项明细。
const projects = wb.worksheets.getItem("交付中项目");
resetSheet(projects, "A1:N40");
title(projects, "A1:N1", "第二部分｜交付中项目组合：本周8项重点明细");
subTitle(projects, "A2:N2", "先看项目阶段和验收门禁，再看局部功能；合同、验收、回款与毛利无来源时保持“待确认”。");
projects.getRange("A4:N4").values = [["序号", "项目类型", "项目", "当前阶段", "本周已验证事实", "", "下一门禁", "", "负责人", "时间", "风险/偏差", "8月任务对标", "验收状态", "来源"]];
projects.mergeCells("E4:F4"); projects.mergeCells("G4:H4"); header(projects.getRange("A4:N4"));
for (let i = 0; i < current.projectRows.length; i += 1) {
  const src = current.projectRows[i]; const r = 5 + i;
  const values = [src[0], src[1], src[3], src[2], src[13], src[14], src[6], src[4], src[15], src[16], src[11], src[17]];
  cell(projects, `A${r}`, values[0]); cell(projects, `B${r}`, values[1]); cell(projects, `C${r}`, values[2]); cell(projects, `D${r}`, values[3]);
  projects.mergeCells(`E${r}:F${r}`); cell(projects, `E${r}`, values[4]);
  projects.mergeCells(`G${r}:H${r}`); cell(projects, `G${r}`, values[5]);
  cell(projects, `I${r}`, values[6]); cell(projects, `J${r}`, values[7]); cell(projects, `K${r}`, values[8]); cell(projects, `L${r}`, values[9]); cell(projects, `M${r}`, values[10]); cell(projects, `N${r}`, values[11]);
  body(projects.getRange(`A${r}:N${r}`)); projects.getRange(`D${r}`).format.fill = statusColor(src[2]); projects.getRange(`K${r}`).format.fill = statusColor(src[15]); projects.getRange(`A${r}:N${r}`).format.rowHeight = 84;
}
subTitle(projects, "A15:N15", "项目组合判断：本周不以“上线数量”作为主结论，以关键流程、连续运行、故障恢复、客户确认和验收证据作为放行条件。", C.paleTeal, C.teal);
projects.getRange("A17:N17").values = [["类型", "项目数", "代表项目", "", "当前主线", "", "", "共性风险", "", "", "下周动作", "", "产品化方向", "证据门禁"]];
projects.mergeCells("C17:D17"); projects.mergeCells("E17:G17"); projects.mergeCells("H17:J17"); projects.mergeCells("K17:L17"); header(projects.getRange("A17:N17"));
const groups = [
  ["校园/康养", 2, "央美、滨州", "一卡通门禁与试运行问题闭环", "接口、离线、绑盘、连续运行与验收证据不足", "央美预检；滨州全流程与远程交接", "校园一卡通+称重餐线配置包", "四类上线门禁/连续运行"],
  ["企业团餐", 3, "金斯瑞、三全、四方达", "账户规则、高可用、补贴核算与部署前置", "测试证据、外部依赖与范围边界不全", "金斯瑞Review；三全验证；四方达锁Owner", "集团高可用+经营大屏+补贴规则", "测试/切换/外部确认"],
  ["政企/安全", 3, "副中心、重庆环卫、等保", "稳定性、设备接入、纯内网与等保", "压测、外部设备、扫描和检查受依赖", "完成压测；锁咖啡机边界；首轮扫描", "政务可信交付+安全合规包", "压测/扫描/整改复测"],
];
for (let i = 0; i < groups.length; i += 1) {
  const r = 18 + i; const x = groups[i];
  cell(projects, `A${r}`, x[0]); cell(projects, `B${r}`, x[1]); projects.mergeCells(`C${r}:D${r}`); cell(projects, `C${r}`, x[2]);
  projects.mergeCells(`E${r}:G${r}`); cell(projects, `E${r}`, x[3]); projects.mergeCells(`H${r}:J${r}`); cell(projects, `H${r}`, x[4]);
  projects.mergeCells(`K${r}:L${r}`); cell(projects, `K${r}`, x[5]); cell(projects, `M${r}`, x[6]); cell(projects, `N${r}`, x[7]);
  body(projects.getRange(`A${r}:N${r}`)); projects.getRange(`A${r}:N${r}`).format.rowHeight = 70;
}
mergeValue(projects, "A23:N25", "证据边界：项目明细来自本周周报、组长计划、硬件记录与等保进度材料。局部功能上线、设备进场、材料提交或单次运行不能替代合同范围、连续稳定、客户确认、验收、回款与毛利证据。");
projects.getRange("A23:N25").format = { fill: C.paleOrange, font: { ...FONT, color: "#9A6700" }, wrapText: true, verticalAlignment: "center", borders: BORDER };
widths(projects, [["A", 7], ["B", 13], ["C", 18], ["D", 18], ["E", 24], ["F", 24], ["G", 24], ["H", 24], ["I", 18], ["J", 16], ["K", 26], ["L", 24], ["M", 15], ["N", 20]], 40);
projects.freezePanes.freezeRows(4);

// 3) 四个项目：替换上周四张图，使用本周新四张概念图。
const four = wb.worksheets.getItem("4个项目");
resetSheet(four, "A1:P100");
title(four, "A1:P1", "我的项目｜本周4个重点场景（不重复上周图片）");
subTitle(four, "A2:P2", "金斯瑞、三全、中央美院、四方达；四张图均为用户提供的D级场景概念图，用于解释业务，不作为现场复原、BOM、上线或验收证据。", C.paleOrange, "#9A6700");
const projectCards = current.imageCards;
const startRows = [4, 27, 50, 73];
for (let i = 0; i < projectCards.length; i += 1) {
  const card = projectCards[i]; const sr = startRows[i];
  mergeValue(four, `A${sr}:P${sr}`, card.project);
  four.getRange(`A${sr}:P${sr}`).format = { fill: i % 2 === 0 ? C.blue : C.teal, font: { name: "微软雅黑", size: 15, bold: true, color: C.white }, verticalAlignment: "center" };
  const imagePath = path.resolve(taskDir, `../product-project-weekly-20260815/${card.path}`);
  four.images.add({ dataUrl: await loadPng(imagePath), anchor: { from: { row: sr, col: 0 }, extent: { widthPx: 720, heightPx: 405 } } });
  const labels = [["本周事实", card.fact], ["证据边界", card.boundary], ["下一门禁", card.next]];
  const rr = [sr + 2, sr + 8, sr + 14];
  for (let j = 0; j < labels.length; j += 1) {
    four.mergeCells(`J${rr[j]}:K${rr[j] + 4}`); cell(four, `J${rr[j]}`, labels[j][0]);
    four.mergeCells(`L${rr[j]}:P${rr[j] + 4}`); cell(four, `L${rr[j]}`, labels[j][1]);
    four.getRange(`J${rr[j]}:K${rr[j] + 4}`).format = { fill: j === 1 ? C.paleOrange : C.paleBlue, font: { ...FONT, bold: true, color: j === 1 ? "#9A6700" : C.blue }, horizontalAlignment: "center", verticalAlignment: "center", wrapText: true, borders: BORDER };
    four.getRange(`L${rr[j]}:P${rr[j] + 4}`).format = { fill: C.white, font: FONT, verticalAlignment: "center", wrapText: true, borders: BORDER };
  }
}
for (let r = 1; r <= 96; r += 1) four.getRange(`A${r}:P${r}`).format.rowHeight = r === 1 ? 36 : 20;
widths(four, Array.from("ABCDEFGHIJKLMNOP").map((col) => [col, 11]), 100);
four.freezePanes.freezeRows(2);

// 4) 产品总览：保留上周产品知识底稿，在末尾增加本周8月产品对标。
const productOverview = wb.worksheets.getItem("01-产品线总览");
subTitle(productOverview, "A111:H111", "2026-W33产品进展｜对标8月三条产品主线", C.navy, C.white);
productOverview.getRange("A112:H112").values = [["产品主线", "8月目标", "当前阶段", "本周证据", "", "关键差距", "下一门禁", "证据边界"]];
productOverview.mergeCells("D112:E112"); header(productOverview.getRange("A112:H112"));
const productRows = [
  ["政企供餐端营养模块", "三项营养功能+五件套", "Build", "政企营养原型完成初评", "演示环境、用例、手册、销售口径未闭环", "8/23阶段评审；8/31收口", "原型不等于产品完成"],
  ["智餐助手 V1.1", "查询/指引+手册视频+真实数据验证", "Validate待启动", "7月V1上线；本周无V1.1新证据", "权限矩阵、问题集、指标、人工兜底待补", "8/28真实问题集回归", "V1事实不能替代V1.1验收"],
  ["场景化标准产品包", "政企/学校清单+模块/报价/交付边界", "Standardize进行中", "已有产品线、方案包与交付闭环底稿", "报价边界、合同交接、第二项目复用指标待补", "8/28两项目反校", "资料底稿不等于可售可验收"],
];
for (let i = 0; i < productRows.length; i += 1) {
  const r = 113 + i; const x = productRows[i];
  cell(productOverview, `A${r}`, x[0]); cell(productOverview, `B${r}`, x[1]); cell(productOverview, `C${r}`, x[2]);
  productOverview.mergeCells(`D${r}:E${r}`); cell(productOverview, `D${r}`, x[3]); cell(productOverview, `F${r}`, x[4]); cell(productOverview, `G${r}`, x[5]); cell(productOverview, `H${r}`, x[6]);
  body(productOverview.getRange(`A${r}:H${r}`)); productOverview.getRange(`C${r}`).format.fill = statusColor(x[2]); productOverview.getRange(`A${r}:H${r}`).format.rowHeight = 68;
}

// 5) 八月任务对标矩阵。
const align = wb.worksheets.getOrAdd("08-八月任务对标");
resetSheet(align, "A1:M30");
title(align, "A1:M1", "08｜8月任务对标：目标—当前阶段—差距—下一门禁");
subTitle(align, "A2:M2", "目标基线来自7月月报PART 03；当前阶段来自W33周报、组长计划、项目台账、硬件记录和仓库任务证据。没有闭环证据的事项不填完成率。", C.paleTeal, C.teal);
mergeValue(align, "A3:M3", "阶段口径：Build=建设；Validate=真实任务/现场验证；Standardize=形成可复用包；Scale=跨项目稳定复制。本周整体仍处Build/Validate并行，标准化只在局部底稿层形成。");
align.getRange("A3:M3").format = { fill: C.paleGray, font: { ...FONT, color: C.gray }, wrapText: true };
align.getRange("A5:M5").values = [["序号", "8月主线", "8月任务", "目标/完成定义", "当前证据", "当前阶段", "状态", "优先级", "下一门禁", "责任人/角色", "时间", "验收物", "证据等级"]];
header(align.getRange("A5:M5"));
align.getRange(`A6:M${5 + augustTasks.length}`).values = augustTasks;
body(align.getRange(`A6:M${5 + augustTasks.length}`));
for (let i = 0; i < augustTasks.length; i += 1) {
  const r = 6 + i; align.getRange(`G${r}`).format.fill = statusColor(augustTasks[i][6]);
  align.getRange(`H${r}`).format.fill = augustTasks[i][7] === "高" ? C.paleRed : C.paleOrange;
  align.getRange(`A${r}:M${r}`).format.rowHeight = 92;
}
widths(align, [["A", 7], ["B", 14], ["C", 24], ["D", 38], ["E", 42], ["F", 18], ["G", 15], ["H", 10], ["I", 38], ["J", 24], ["K", 22], ["L", 32], ["M", 16]], 30);
align.freezePanes.freezeRows(5);

// 6) 下步行动拆解。
const actions = wb.worksheets.getOrAdd("09-下步行动拆解");
resetSheet(actions, "A1:H25");
title(actions, "A1:H1", "09｜8月下半月行动拆解：先关门，再封版，再复制");
subTitle(actions, "A2:H2", "执行顺序：P0项目放行与安全门禁 → P1产品/治理/Agent试点 → P2研发底座与资产复用。每项都以可回读验收物结束。", C.paleBlue, C.blue);
actions.getRange("A4:H4").values = [["优先级", "对象", "下一动作", "通过标准", "责任人/角色", "时间", "风险/停止规则", "验收物"]];
header(actions.getRange("A4:H4"));
actions.getRange(`A5:H${4 + nextActions.length}`).values = nextActions;
body(actions.getRange(`A5:H${4 + nextActions.length}`));
for (let i = 0; i < nextActions.length; i += 1) {
  const r = 5 + i; const p = nextActions[i][0];
  actions.getRange(`A${r}`).format.fill = p === "P0" ? C.paleRed : p === "P1" ? C.paleOrange : C.paleBlue;
  actions.getRange(`A${r}:H${r}`).format.rowHeight = 82;
}
mergeValue(actions, `A${6 + nextActions.length}:H${8 + nextActions.length}`, "月底收口判定：只有当目标验收物可回读、关键风险有关闭或升级记录、责任人和时间均明确，才把任务从Build/Validate提升为Standardize；“材料已提交”“功能已上线”“设备已到场”均不是整项完成定义。");
actions.getRange(`A${6 + nextActions.length}:H${8 + nextActions.length}`).format = { fill: C.paleOrange, font: { ...FONT, color: "#9A6700" }, wrapText: true, verticalAlignment: "center", borders: BORDER };
widths(actions, [["A", 10], ["B", 22], ["C", 38], ["D", 42], ["E", 24], ["F", 22], ["G", 34], ["H", 34]], 25);
actions.freezePanes.freezeRows(4);

// 7) 独立5+2 Sheet：嵌入用户指定HTML整页高清截图，并保留可点击链接。
const cap = wb.worksheets.getOrAdd("10-5+2产品能力");
resetSheet(cap, "A1:N520");
title(cap, "A1:N1", "10｜5大产品能力 + 2大差异能力（用户指定HTML原貌）");
mergeValue(cap, "A2:N2", publicHtmlUrl);
cap.getRange("A2").formulas = [[`=HYPERLINK("${publicHtmlUrl}","打开线上HTML：5大产品能力 + 2大差异能力")`]];
cap.getRange("A2:N2").format = { fill: C.paleBlue, font: { name: "微软雅黑", size: 12, bold: true, color: C.blue, underline: true }, wrapText: true, verticalAlignment: "center" };
mergeValue(cap, "A3:N3", "说明：下方为指定HTML的整页高清渲染；用于呈现产品能力故事，不代表所有能力已在所有项目上线、验收或形成规模化收入。");
cap.getRange("A3:N3").format = { fill: C.paleOrange, font: { ...FONT, color: "#9A6700" }, wrapText: true, verticalAlignment: "center", borders: BORDER };
const htmlPng = await loadPng(htmlMeta.screenshot);
const imageWidth = 1220;
const imageHeight = Math.round(imageWidth * htmlMeta.height / htmlMeta.width);
cap.images.add({ dataUrl: htmlPng, anchor: { from: { row: 4, col: 0 }, extent: { widthPx: imageWidth, heightPx: imageHeight } } });
for (let r = 1; r <= 520; r += 1) cap.getRange(`A${r}:N${r}`).format.rowHeight = r === 1 ? 36 : r <= 3 ? 30 : 20;
widths(cap, Array.from("ABCDEFGHIJKLMN").map((col) => [col, 12]), 520);
cap.freezePanes.freezeRows(3);

// 8) 导航增加本次三页。
const nav = wb.worksheets.getItem("00-角色导航");
cell(nav, "B23", "链接与演示素材（凭据已脱敏）");
subTitle(nav, "A27:D27", "本周新增：8月任务与5+2产品能力", C.navy, C.white);
nav.getRange("A28:D30").values = [
  ["08-八月任务对标", "8月目标、当前阶段、差距和下一门禁", "管理层、项目经理、产品", "先看状态和证据缺口"],
  ["09-下步行动拆解", "8月下半月P0/P1/P2动作", "负责人、执行团队", "按通过标准交付验收物"],
  ["10-5+2产品能力", "用户指定HTML整页原貌", "销售、售前、客户领导", "看产品能力与差异价值"],
];
body(nav.getRange("A28:D30"));

// 所有可见单元格统一微软雅黑12号，保留各Sheet既有填充/边框/粗体层次。
for (const sheet of wb.worksheets.items) {
  const used = sheet.getUsedRange();
  if (used) used.format.font = { name: "微软雅黑", size: 12 };
}

const inspectDashboard = await wb.inspect({ kind: "table", sheetId: "项目经营大屏", range: "A1:J28", include: "values,formulas", tableMaxRows: 35, tableMaxCols: 12, maxChars: 18000 });
const inspectAlign = await wb.inspect({ kind: "table", sheetId: "08-八月任务对标", range: `A1:M${5 + augustTasks.length}`, include: "values,formulas", tableMaxRows: 30, tableMaxCols: 15, maxChars: 26000 });
const formulaErrors = await wb.inspect({ kind: "match", searchTerm: "#REF!|#DIV/0!|#VALUE!|#NAME\\?|#N/A", options: { useRegex: true, maxResults: 300 }, summary: "final formula error scan" });
await fs.writeFile(path.join(outputDir, "inspect-dashboard.ndjson"), inspectDashboard.ndjson, "utf8");
await fs.writeFile(path.join(outputDir, "inspect-august-alignment.ndjson"), inspectAlign.ndjson, "utf8");
await fs.writeFile(path.join(outputDir, "formula-errors.ndjson"), formulaErrors.ndjson, "utf8");

for (const [sheetName, range, fileName, scale] of [
  ["项目经营大屏", "A1:J28", "preview-dashboard.png", 0.9],
  ["交付中项目", "A1:N25", "preview-projects.png", 0.7],
  ["4个项目", "A1:P96", "preview-four-projects.png", 0.65],
  ["08-八月任务对标", `A1:M${5 + augustTasks.length}`, "preview-august-alignment.png", 0.55],
  ["09-下步行动拆解", `A1:H${8 + nextActions.length}`, "preview-next-actions.png", 0.7],
  ["10-5+2产品能力", "A1:N80", "preview-5-plus-2-top.png", 0.55],
]) {
  const preview = await wb.render({ sheetName, range, scale, format: "png" });
  await fs.writeFile(path.join(outputDir, fileName), new Uint8Array(await preview.arrayBuffer()));
}

const xlsx = await SpreadsheetFile.exportXlsx(wb);
await xlsx.save(outputPath);
console.log(JSON.stringify({
  outputPath,
  sheetCount: wb.worksheets.getSheetCount(),
  sheets: wb.worksheets.items.map((sheet) => sheet.name),
  augustTasks: augustTasks.length,
  nextActions: nextActions.length,
  embeddedHtmlScreenshot: { width: htmlMeta.width, height: htmlMeta.height, imageWidth, imageHeight },
}, null, 2));