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

const outputDir = "/Users/lingniu/project/ai-coding/lingniu-vehicle-ingest/outputs/019fe9b9-eb60-7d22-8c0a-c70851a48083";
const outputPath = `${outputDir}/氢耗算法120组人工验算底稿.xlsx`;
const previewDir = `${outputDir}/preview`;

const colors = {
  navy: "#0B3A53",
  blue: "#0E7490",
  cyan: "#CDEEF4",
  pale: "#EAF6F8",
  green: "#DCFCE7",
  greenText: "#166534",
  orange: "#FFEDD5",
  orangeText: "#9A3412",
  red: "#FEE2E2",
  redText: "#991B1B",
  gray: "#E5E7EB",
  grayText: "#475569",
  white: "#FFFFFF",
};

const coeffA = [0.05888460, -0.06136111, -0.002650473, 0.002731125, 0.001802374, -0.001150707, 0.00009588528, -0.0000001109040, 0.0000000001264403];
const coeffB = [1.325, 1.87, 2.5, 2.8, 2.938, 3.14, 3.37, 3.75, 4.0];
const coeffC = [1, 1, 2, 2, 2.42, 2.63, 3, 4, 5];

function massKg(pressureMPa, temperatureC, volumeLiter) {
  const temperatureK = temperatureC + 273.15;
  let z = 1;
  for (let i = 0; i < coeffA.length; i++) {
    z += coeffA[i] * Math.pow(100 / temperatureK, coeffB[i]) * Math.pow(pressureMPa, coeffC[i]);
  }
  return pressureMPa * 1000 * 0.00201588 * volumeLiter / (8.314472 * temperatureK * z);
}

function excelCol(index) {
  let result = "";
  let value = index;
  while (value > 0) {
    value--;
    result = String.fromCharCode(65 + (value % 26)) + result;
    value = Math.floor(value / 26);
  }
  return result;
}

function styleTitle(sheet, range, title) {
  range.merge();
  range.values = [[title]];
  range.format = {
    fill: colors.navy,
    font: { bold: true, color: colors.white },
    verticalAlignment: "center",
    horizontalAlignment: "left",
  };
  range.format.rowHeight = 32;
}

function styleHeader(range) {
  range.format = {
    fill: colors.blue,
    font: { bold: true, color: colors.white },
    verticalAlignment: "center",
    horizontalAlignment: "center",
    wrapText: true,
    borders: { preset: "all", style: "thin", color: "#B8CDD5" },
  };
  range.format.rowHeight = 30;
}

function styleBody(range) {
  range.format = {
    verticalAlignment: "center",
    borders: {
      insideHorizontal: { style: "thin", color: "#DDE7EB" },
      bottom: { style: "thin", color: "#B8CDD5" },
    },
  };
}

function setWidths(sheet, widths) {
  widths.forEach((width, index) => {
    sheet.getRange(`${excelCol(index + 1)}:${excelCol(index + 1)}`).format.columnWidth = width;
  });
}

const workbook = Workbook.create();
workbook.comments.setSelf({ displayName: "User" });
const guide = workbook.worksheets.add("验算说明");
const params = workbook.worksheets.add("参数与系数");
const grid = workbook.worksheets.add("120组压力质量验算");
const raw = workbook.worksheets.add("原始报文120条");
const detail = workbook.worksheets.add("样本计算明细");
const summary = workbook.worksheets.add("每日与区间汇总");

for (const sheet of [guide, params, grid, raw, detail, summary]) sheet.showGridLines = false;

// 参数与NIST系数
styleTitle(params, params.getRange("A1:F1"), "氢耗算法参数与NIST实气压缩因子系数");
params.getRange("A3:C3").values = [["参数", "取值", "说明"]];
styleHeader(params.getRange("A3:C3"));
const parameterRows = [
  ["储氢系统总容积(L)", 520, "验算样本使用，可替换为车型确认值"],
  ["动力电池容量(kWh)", 21.04, "4.5T示例；必须使用售后技术部确认值"],
  ["1kg氢气等效电量(kWh/kg)", 16, "当前业务折算参数"],
  ["上电稳定等待(s)", 60, "上电后60秒内样本不作为计算端点"],
  ["下电提前窗口(s)", 60, "下电前60秒样本不作为计算端点"],
  ["加氢压力上升阈值(MPa)", 3, "并需持续达到保持时间"],
  ["加氢保持时间(s)", 300, "5分钟"],
  ["纯电异常压降阈值(MPa)", 5, "30分钟内超过该值判异常"],
  ["纯电异常窗口(s)", 1800, "30分钟"],
  ["端点中位样本数", 5, "区间首尾各5条"],
  ["最少有效样本数", 10, "不足10条不形成有效区间"],
  ["样本间隔(s)", 10, "本验算原始数据间隔"],
];
params.getRange(`A4:C${3 + parameterRows.length}`).values = parameterRows;
styleBody(params.getRange(`A4:C${3 + parameterRows.length}`));
params.getRange(`B4:B${3 + parameterRows.length}`).format.numberFormat = "0.000";

params.getRange("A18:D18").values = [["系数序号", "aᵢ", "bᵢ", "cᵢ"]];
styleHeader(params.getRange("A18:D18"));
params.getRange("A19:D27").values = coeffA.map((a, index) => [index + 1, a, coeffB[index], coeffC[index]]);
styleBody(params.getRange("A19:D27"));
params.getRange("B19:D27").format.numberFormat = "0.0000000000";
params.getRange("A29:F33").values = [
  ["压力—质量公式", null, null, null, null, null],
  ["Z = 1 + Σ[aᵢ × (100/Tₖ)^bᵢ × P^cᵢ]", null, null, null, null, null],
  ["m = P × 1000 × 0.00201588 × V ÷ (8.314472 × Tₖ × Z)", null, null, null, null, null],
  ["SOC平衡公式", null, null, null, null, null],
  ["m_SOC = m_H₂ + C_battery × (SOC_start − SOC_end) ÷ 100 ÷ 16", null, null, null, null, null],
];
for (const row of [29, 30, 31, 32, 33]) params.getRange(`A${row}:F${row}`).merge();
params.getRange("A29:F29").format = { fill: colors.cyan, font: { bold: true, color: colors.navy } };
params.getRange("A32:F32").format = { fill: colors.cyan, font: { bold: true, color: colors.navy } };
params.getRange("A30:F31").format = { fill: colors.pale, font: { color: colors.navy }, wrapText: true };
params.getRange("A33:F33").format = { fill: colors.pale, font: { color: colors.navy }, wrapText: true };
setWidths(params, [28, 18, 52, 16, 16, 16]);
params.freezePanes.freezeRows(3);

// 120组独立压力—质量验算
const gridHeaders = ["序号", "压力P(MPa)", "温度T(℃)", "容积V(L)", "绝对温度Tₖ(K)", ...Array.from({ length: 9 }, (_, i) => `Z分项${i + 1}`), "压缩因子Z", "分子", "分母", "Excel复算质量(kg)", "系统计算质量(kg)", "绝对差值(kg)", "核验结果"];
grid.getRange(`A1:${excelCol(gridHeaders.length)}1`).values = [gridHeaders];
styleHeader(grid.getRange(`A1:${excelCol(gridHeaders.length)}1`));
const pressures = [1, 3, 5, 8, 10, 12, 15, 18, 21, 25, 30, 35];
const temperatures = [-20, -10, 0, 10, 20, 30, 40, 50, 60, 70];
const gridInputs = [];
const gridSystem = [];
let gridIndex = 1;
for (const pressure of pressures) {
  for (const temperature of temperatures) {
    gridInputs.push([gridIndex++, pressure, temperature, 520]);
    gridSystem.push(massKg(pressure, temperature, 520));
  }
}
grid.getRange("A2:D121").values = gridInputs;
for (let row = 2; row <= 121; row++) {
  grid.getRange(`E${row}`).formulas = [[`=C${row}+273.15`]];
  for (let term = 0; term < 9; term++) {
    const col = excelCol(6 + term);
    const coeffRow = 19 + term;
    grid.getRange(`${col}${row}`).formulas = [[`='参数与系数'!$B$${coeffRow}*POWER(100/$E${row},'参数与系数'!$C$${coeffRow})*POWER($B${row},'参数与系数'!$D$${coeffRow})`]];
  }
  grid.getRange(`O${row}`).formulas = [[`=1+SUM(F${row}:N${row})`]];
  grid.getRange(`P${row}`).formulas = [[`=B${row}*1000*0.00201588*D${row}`]];
  grid.getRange(`Q${row}`).formulas = [[`=8.314472*E${row}*O${row}`]];
  grid.getRange(`R${row}`).formulas = [[`=P${row}/Q${row}`]];
  grid.getRange(`S${row}`).values = [[gridSystem[row - 2]]];
  grid.getRange(`T${row}`).formulas = [[`=ABS(R${row}-S${row})`]];
  grid.getRange(`U${row}`).formulas = [[`=IF(T${row}<=0.0000000001,"通过","不通过")`]];
}
styleBody(grid.getRange("A2:U121"));
grid.getRange("B2:D121").format.numberFormat = "0.000";
grid.getRange("E2:Q121").format.numberFormat = "0.0000000000";
grid.getRange("R2:S121").format.numberFormat = "0.000000";
grid.getRange("T2:T121").format.numberFormat = "0.000000000000";
grid.getRange("U2:U121").conditionalFormats.add("containsText", { text: "通过", format: { fill: colors.green, font: { color: colors.greenText, bold: true } } });
grid.getRange("U2:U121").conditionalFormats.add("containsText", { text: "不通过", format: { fill: colors.red, font: { color: colors.redText, bold: true } } });
grid.tables.add("A1:U121", true, "NISTValidation120").style = "TableStyleMedium2";
grid.freezePanes.freezeRows(1);
grid.freezePanes.freezeColumns(5);
setWidths(grid, [8, 12, 12, 12, 15, 14, 14, 14, 14, 14, 14, 14, 14, 14, 14, 16, 16, 18, 18, 18, 12]);

// 构造120条可人工核验的原始GB/T 32960解析字段样本。
const rawHeaders = ["序号", "报文ID", "上报时间", "数据源", "车辆状态", "充电状态", "运行模式", "燃料电池工作", "氢压(MPa)", "氢温(℃)", "储氢容积(L)", "电SOC(%)", "仪表总里程(km)", "原始解析字段JSON"];
raw.getRange("A1:N1").values = [rawHeaders];
styleHeader(raw.getRange("A1:N1"));
const rawRows = [];
const systemMasses = [];
const baseTime = new Date(Date.UTC(2026, 7, 26, 0, 0, 0));
for (let index = 0; index < 120; index++) {
  const eventId = `audit-32960-${String(index + 1).padStart(3, "0")}`;
  const observedAt = new Date(baseTime.getTime() + index * 10_000);
  let vehicleState = 1;
  let chargeState = 3;
  let runningMode = 2;
  let fuelCellActive = 1;
  let pressure;
  let soc;
  let mileage;
  if (index < 40) {
    pressure = 30 - index * 0.04;
    soc = 80 - index * 0.04;
    mileage = 1000 + index * 0.08;
  } else if (index < 60) {
    vehicleState = 2;
    chargeState = 1;
    runningMode = 0;
    fuelCellActive = 0;
    pressure = 28.4 - (index - 40) * 0.002;
    soc = 78.4 + (index - 40) * (11.6 / 19);
    mileage = 1003.2;
  } else if (index < 80) {
    chargeState = index === 60 ? 4 : 3;
    runningMode = 1;
    fuelCellActive = 0;
    pressure = 28.36 - (index - 60) * 0.005;
    soc = 90 - (index - 60) * 0.15;
    mileage = 1003.2 + (index - 60) * 0.1;
  } else {
    pressure = 28.26 - (index - 80) * 0.06;
    soc = 87 - (index - 80) * 0.04;
    mileage = 1005.2 + (index - 80) * 0.12;
    if (index === 119) vehicleState = 2;
  }
  const temperature = 30 + Math.sin(index / 10);
  const mass = massKg(pressure, temperature, 520);
  systemMasses.push(mass);
  const rawJson = JSON.stringify({
    event_id: eventId,
    vehicle_state: vehicleState,
    charge_status: chargeState,
    running_mode: runningMode,
    fuel_cell_active: fuelCellActive,
    hydrogen_max_pressure_mpa: Number(pressure.toFixed(6)),
    hydrogen_max_temperature_c: Number(temperature.toFixed(6)),
    soc_percent: Number(soc.toFixed(6)),
    total_mileage_km: Number(mileage.toFixed(6)),
  });
  rawRows.push([index + 1, eventId, observedAt, "GB32960-audit-sample", vehicleState, chargeState, runningMode, fuelCellActive, pressure, temperature, 520, soc, mileage, rawJson]);
}
raw.getRange("A2:N121").values = rawRows;
styleBody(raw.getRange("A2:N121"));
raw.getRange("C2:C121").format.numberFormat = "yyyy-mm-dd hh:mm:ss";
raw.getRange("I2:M121").format.numberFormat = "0.000000";
raw.getRange("N2:N121").format.wrapText = false;
raw.getRange("A123:N126").values = [
  ["人工核验说明", null, null, null, null, null, null, null, null, null, null, null, null, null],
  ["本页为验算构造的原始解析字段样本，不是生产车辆真实报文。生产复核时应按相同字段结构替换为中台导出的真实32960数据。", null, null, null, null, null, null, null, null, null, null, null, null, null],
  ["车辆状态：1=启动/运行，2=熄火；充电状态：1=停车充电，3=未充电，4=充电完成；运行模式：1=纯电，2=混动。", null, null, null, null, null, null, null, null, null, null, null, null, null],
  ["停车充电需由车辆状态与充电状态联合判断；充电区间不计算氢耗，充电完成重新建立SOC、压力、温度和里程基线。", null, null, null, null, null, null, null, null, null, null, null, null, null],
];
for (let row = 123; row <= 126; row++) raw.getRange(`A${row}:N${row}`).merge();
raw.getRange("A123:N123").format = { fill: colors.cyan, font: { bold: true, color: colors.navy } };
raw.getRange("A124:N126").format = { fill: colors.pale, font: { color: colors.grayText }, wrapText: true };
raw.tables.add("A1:N121", true, "RawTelemetry120").style = "TableStyleMedium2";
raw.freezePanes.freezeRows(1);
raw.freezePanes.freezeColumns(3);
setWidths(raw, [8, 24, 20, 24, 10, 10, 10, 14, 12, 12, 14, 12, 18, 90]);

// 逐条计算明细：全部中间状态和公式均保留。
const detailHeaders = ["序号", "报文ID", "上报时间", "压力P", "温度T", "容积V", "Tₖ", ...Array.from({ length: 9 }, (_, i) => `Z分项${i + 1}`), "Z", "分子", "分母", "Excel复算质量", "系统质量", "差值", "车辆状态", "充电状态", "运行模式", "燃料电池", "SOC", "仪表里程", "阶段", "上电60s通过", "下电60s通过", "停车充电", "状态组合有效", "参与计算", "排除原因", "区间类型", "系统区间号", "端点角色"];
detail.getRange(`A1:${excelCol(detailHeaders.length)}1`).values = [detailHeaders];
styleHeader(detail.getRange(`A1:${excelCol(detailHeaders.length)}1`));
for (let row = 2; row <= 121; row++) {
  const rawRow = row;
  const index = row - 2;
  detail.getRange(`A${row}:F${row}`).formulas = [[
    `='原始报文120条'!A${rawRow}`,
    `='原始报文120条'!B${rawRow}`,
    `='原始报文120条'!C${rawRow}`,
    `='原始报文120条'!I${rawRow}`,
    `='原始报文120条'!J${rawRow}`,
    `='原始报文120条'!K${rawRow}`,
  ]];
  detail.getRange(`G${row}`).formulas = [[`=E${row}+273.15`]];
  for (let term = 0; term < 9; term++) {
    const col = excelCol(8 + term);
    const coeffRow = 19 + term;
    detail.getRange(`${col}${row}`).formulas = [[`='参数与系数'!$B$${coeffRow}*POWER(100/$G${row},'参数与系数'!$C$${coeffRow})*POWER($D${row},'参数与系数'!$D$${coeffRow})`]];
  }
  detail.getRange(`Q${row}`).formulas = [[`=1+SUM(H${row}:P${row})`]];
  detail.getRange(`R${row}`).formulas = [[`=D${row}*1000*0.00201588*F${row}`]];
  detail.getRange(`S${row}`).formulas = [[`=8.314472*G${row}*Q${row}`]];
  detail.getRange(`T${row}`).formulas = [[`=R${row}/S${row}`]];
  detail.getRange(`U${row}`).values = [[systemMasses[index]]];
  detail.getRange(`V${row}`).formulas = [[`=ABS(T${row}-U${row})`]];
  detail.getRange(`W${row}:AB${row}`).formulas = [[
    `='原始报文120条'!E${rawRow}`,
    `='原始报文120条'!F${rawRow}`,
    `='原始报文120条'!G${rawRow}`,
    `='原始报文120条'!H${rawRow}`,
    `='原始报文120条'!L${rawRow}`,
    `='原始报文120条'!M${rawRow}`,
  ]];
  const phase = index < 40 ? "充电前运行" : index < 60 ? "停车充电" : index < 80 ? "充电后纯电" : "充电后混动";
  detail.getRange(`AC${row}`).values = [[phase]];
  detail.getRange(`AD${row}`).formulas = [[`=IF(OR(C${row}<'原始报文120条'!$C$2+TIME(0,0,'参数与系数'!$B$7),AND(C${row}>='原始报文120条'!$C$62,C${row}<'原始报文120条'!$C$62+TIME(0,0,'参数与系数'!$B$7))),"否","是")`]];
  detail.getRange(`AE${row}`).formulas = [[`=IF(OR(AND(C${row}>'原始报文120条'!$C$42-TIME(0,0,'参数与系数'!$B$8),C${row}<='原始报文120条'!$C$42),AND(C${row}>'原始报文120条'!$C$121-TIME(0,0,'参数与系数'!$B$8),C${row}<='原始报文120条'!$C$121)),"否","是")`]];
  detail.getRange(`AF${row}`).formulas = [[`=IF(AND(W${row}=2,X${row}=1),"是","否")`]];
  detail.getRange(`AG${row}`).formulas = [[`=IF(OR(AND(Y${row}=1,Z${row}=0),AND(Y${row}=2,Z${row}=1)),"是","否")`]];
  detail.getRange(`AH${row}`).formulas = [[`=IF(AND(AD${row}="是",AE${row}="是",AF${row}="否",AG${row}="是",W${row}=1),"是","否")`]];
  detail.getRange(`AI${row}`).formulas = [[`=IF(AH${row}="是","",IF(AF${row}="是","停车充电区间",IF(AD${row}="否","上电后60秒稳定窗口",IF(AE${row}="否","下电前60秒稳定窗口",IF(W${row}<>1,"车辆非运行状态","运行模式与燃料电池状态不匹配")))))`]];
  detail.getRange(`AJ${row}`).formulas = [[`=IF(AH${row}<>"是","",IF(AND(Y${row}=1,Z${row}=0),"PURE_ELECTRIC",IF(AND(Y${row}=2,Z${row}=1),"MIXED","")))`]];
  const segment = index >= 6 && index <= 34 ? 1 : index >= 66 && index <= 79 ? 2 : index >= 80 && index <= 113 ? 3 : null;
  detail.getRange(`AK${row}`).values = [[segment]];
  let endpointRole = "";
  if (index >= 6 && index <= 10) endpointRole = `区间1起始候选${index - 5}`;
  else if (index >= 30 && index <= 34) endpointRole = `区间1结束候选${index - 29}`;
  else if (index >= 66 && index <= 70) endpointRole = `区间2起始候选${index - 65}`;
  else if (index >= 75 && index <= 79) endpointRole = `区间2结束候选${index - 74}`;
  else if (index >= 80 && index <= 84) endpointRole = `区间3起始候选${index - 79}`;
  else if (index >= 109 && index <= 113) endpointRole = `区间3结束候选${index - 108}`;
  detail.getRange(`AL${row}`).values = [[endpointRole]];
}
styleBody(detail.getRange(`A2:AL121`));
detail.getRange("C2:C121").format.numberFormat = "yyyy-mm-dd hh:mm:ss";
detail.getRange("D2:V121").format.numberFormat = "0.000000";
detail.getRange("AA2:AB121").format.numberFormat = "0.000000";
detail.getRange("AH2:AH121").conditionalFormats.add("containsText", { text: "是", format: { fill: colors.green, font: { color: colors.greenText, bold: true } } });
detail.getRange("AH2:AH121").conditionalFormats.add("containsText", { text: "否", format: { fill: colors.gray, font: { color: colors.grayText } } });
detail.getRange("AF2:AF121").conditionalFormats.add("containsText", { text: "是", format: { fill: colors.orange, font: { color: colors.orangeText, bold: true } } });
detail.getRange("AL2:AL121").conditionalFormats.add("notContainsBlanks", { format: { fill: colors.cyan, font: { color: colors.navy, bold: true } } });
detail.tables.add("A1:AL121", true, "TelemetryCalculationDetail").style = "TableStyleMedium2";
detail.freezePanes.freezeRows(1);
detail.freezePanes.freezeColumns(3);
setWidths(detail, [8, 24, 20, 11, 11, 11, 12, ...Array(9).fill(13), 12, 14, 14, 16, 16, 16, 10, 10, 10, 12, 11, 16, 16, 13, 13, 12, 14, 12, 28, 16, 12, 18]);

// 每日与区间汇总，公式均追溯至逐条明细。
styleTitle(summary, summary.getRange("A1:AB1"), "120条原始报文：每日氢耗与区间计算汇总");
summary.getRange("A3:H3").values = [["统计日期", "原始样本数", "参与计算样本", "停车充电样本", "有效区间", "混动区间", "纯电区间", "质量状态"]];
styleHeader(summary.getRange("A3:H3"));
summary.getRange("A4").values = [["2026-08-26"]];
summary.getRange("B4:H4").formulas = [[
  `=COUNTA('原始报文120条'!$B$2:$B$121)`,
  `=COUNTIF('样本计算明细'!$AH$2:$AH$121,"是")`,
  `=COUNTIF('样本计算明细'!$AF$2:$AF$121,"是")`,
  `=COUNTA($A$9:$A$11)`,
  `=COUNTIF($B$9:$B$11,"MIXED")`,
  `=COUNTIF($B$9:$B$11,"PURE_ELECTRIC")`,
  `=IF(AND(B4=120,C4>=10,F4>=1),"OK","SUSPECT")`,
]];
styleBody(summary.getRange("A4:H4"));
summary.getRange("H4").conditionalFormats.add("containsText", { text: "OK", format: { fill: colors.green, font: { color: colors.greenText, bold: true } } });

summary.getRange("J3:Q3").values = [["物理耗氢(kg)", "SOC差值(百分点)", "电池净放电(kWh)", "电量折氢(kg)", "SOC平衡氢耗(kg)", "混动里程(km)", "物理百公里氢耗", "SOC平衡百公里氢耗"]];
styleHeader(summary.getRange("J3:Q3"));
summary.getRange("J4:Q4").formulas = [[
  `=SUMIF($B$9:$B$11,"MIXED",$P$9:$P$11)`,
  `=SUMIF($B$9:$B$11,"MIXED",$R$9:$R$11)-SUMIF($B$9:$B$11,"MIXED",$Q$9:$Q$11)`,
  `=SUMIF($B$9:$B$11,"MIXED",$T$9:$T$11)`,
  `=SUMIF($B$9:$B$11,"MIXED",$U$9:$U$11)`,
  `=SUMIF($B$9:$B$11,"MIXED",$V$9:$V$11)`,
  `=SUMIF($B$9:$B$11,"MIXED",$Y$9:$Y$11)`,
  `=IF(O4>0,J4/O4*100,"")`,
  `=IF(O4>0,N4/O4*100,"")`,
]];
styleBody(summary.getRange("J4:Q4"));
summary.getRange("J4:Q4").format.numberFormat = "0.000000";

summary.getRange("A7:AB7").values = [["区间", "类型", "有效样本", "起始候选行", "结束候选行", "起始报文ID", "结束报文ID", "起始时间", "结束时间", "起始压力", "结束压力", "起始温度", "结束温度", "起始质量", "结束质量", "物理耗氢", "起始SOC", "结束SOC", "SOC差值", "电池净放电", "电量折氢", "SOC平衡氢耗", "起始里程", "结束里程", "区间里程", "物理百公里", "SOC平衡百公里", "是否计入日氢耗"]];
styleHeader(summary.getRange("A7:AB7"));
const intervalSpecs = [
  { row: 9, index: 1, type: "MIXED", sampleCount: 29, startRows: [8, 12], endRows: [32, 36] },
  { row: 10, index: 2, type: "PURE_ELECTRIC", sampleCount: 14, startRows: [68, 72], endRows: [77, 81] },
  { row: 11, index: 3, type: "MIXED", sampleCount: 34, startRows: [82, 86], endRows: [111, 115] },
];
for (const spec of intervalSpecs) {
  const r = spec.row;
  const [ss, se] = spec.startRows;
  const [es, ee] = spec.endRows;
  summary.getRange(`A${r}:E${r}`).values = [[spec.index, spec.type, spec.sampleCount, `${ss}:${se}`, `${es}:${ee}`]];
  summary.getRange(`F${r}`).formulas = [[`=INDEX('样本计算明细'!$B$${ss}:$B$${se},MATCH(MEDIAN('样本计算明细'!$T$${ss}:$T$${se}),'样本计算明细'!$T$${ss}:$T$${se},0))`]];
  summary.getRange(`G${r}`).formulas = [[`=INDEX('样本计算明细'!$B$${es}:$B$${ee},MATCH(MEDIAN('样本计算明细'!$T$${es}:$T$${ee}),'样本计算明细'!$T$${es}:$T$${ee},0))`]];
  summary.getRange(`H${r}`).formulas = [[`=INDEX('样本计算明细'!$C$${ss}:$C$${se},MATCH(MEDIAN('样本计算明细'!$T$${ss}:$T$${se}),'样本计算明细'!$T$${ss}:$T$${se},0))`]];
  summary.getRange(`I${r}`).formulas = [[`=INDEX('样本计算明细'!$C$${es}:$C$${ee},MATCH(MEDIAN('样本计算明细'!$T$${es}:$T$${ee}),'样本计算明细'!$T$${es}:$T$${ee},0))`]];
  const sourceCols = { J: "D", K: "D", L: "E", M: "E", N: "T", O: "T", Q: "AA", R: "AA", W: "AB", X: "AB" };
  for (const [targetCol, sourceCol] of Object.entries(sourceCols)) {
    const isStart = ["J", "L", "N", "Q", "W"].includes(targetCol);
    const rs = isStart ? ss : es;
    const re = isStart ? se : ee;
    summary.getRange(`${targetCol}${r}`).formulas = [[`=INDEX('样本计算明细'!$${sourceCol}$${rs}:$${sourceCol}$${re},MATCH(MEDIAN('样本计算明细'!$T$${rs}:$T$${re}),'样本计算明细'!$T$${rs}:$T$${re},0))`]];
  }
  summary.getRange(`P${r}`).formulas = [[spec.type === "MIXED" ? `=MAX(0,N${r}-O${r})` : `=0`]];
  summary.getRange(`S${r}`).formulas = [[`=R${r}-Q${r}`]];
  summary.getRange(`T${r}`).formulas = [[spec.type === "MIXED" ? `='参数与系数'!$B$5*(Q${r}-R${r})/100` : `=""`]];
  summary.getRange(`U${r}`).formulas = [[spec.type === "MIXED" ? `=T${r}/'参数与系数'!$B$6` : `=""`]];
  summary.getRange(`V${r}`).formulas = [[spec.type === "MIXED" ? `=P${r}+U${r}` : `=""`]];
  summary.getRange(`Y${r}`).formulas = [[`=MAX(0,X${r}-W${r})`]];
  summary.getRange(`Z${r}`).formulas = [[spec.type === "MIXED" ? `=IF(Y${r}>0,P${r}/Y${r}*100,"")` : `=""`]];
  summary.getRange(`AA${r}`).formulas = [[spec.type === "MIXED" ? `=IF(Y${r}>0,V${r}/Y${r}*100,"")` : `=""`]];
  summary.getRange(`AB${r}`).values = [[spec.type === "MIXED" ? "是" : "否（仅计纯电里程）"]];
}
styleBody(summary.getRange("A9:AB11"));
summary.getRange("H9:I11").format.numberFormat = "yyyy-mm-dd hh:mm:ss";
summary.getRange("J9:AA11").format.numberFormat = "0.000000";
summary.getRange("B9:B11").conditionalFormats.add("containsText", { text: "MIXED", format: { fill: colors.green, font: { color: colors.greenText, bold: true } } });
summary.getRange("B9:B11").conditionalFormats.add("containsText", { text: "PURE_ELECTRIC", format: { fill: colors.cyan, font: { color: colors.navy, bold: true } } });

summary.getRange("A14:AB18").values = [
  ["人工核验顺序", null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null],
  ["1. 在“原始报文120条”核对每条车辆状态、充电状态、运行模式、压力、温度、SOC和仪表里程。", null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null],
  ["2. 在“样本计算明细”逐项复核Tₖ、9个Z分项、压缩因子Z、分子、分母、质量及参与计算原因。", null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null],
  ["3. 在本页核对每个区间首尾5条候选样本的中位质量、对应原始报文ID、物理耗氢、SOC修正和里程。", null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null],
  ["4. 停车充电区间不计氢耗；充电结束后重新建立基线。纯电区间只累计纯电里程，不计入混动氢耗分母。", null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null],
];
for (let row = 14; row <= 18; row++) summary.getRange(`A${row}:AB${row}`).merge();
summary.getRange("A14:AB14").format = { fill: colors.cyan, font: { bold: true, color: colors.navy } };
summary.getRange("A15:AB18").format = { fill: colors.pale, font: { color: colors.grayText }, wrapText: true };
summary.freezePanes.freezeRows(7);
summary.freezePanes.freezeColumns(2);
setWidths(summary, [8, 18, 12, 14, 14, 24, 24, 20, 20, 13, 13, 13, 13, 15, 15, 15, 13, 13, 13, 16, 14, 17, 15, 15, 14, 15, 17, 20]);

// 说明首页，关键结果均跨表引用。
styleTitle(guide, guide.getRange("A1:H1"), "氢耗算法120组人工验算底稿");
guide.getRange("A3:H4").merge();
guide.getRange("A3").values = [["用途：供人工使用原始字段逐步复核压力—质量换算、充电剔除、充电后重建基线、纯电/混动分段、SOC能量修正及最终日氢耗。样本为验算构造数据，不代表生产车辆真实报文。"]];
guide.getRange("A3:H4").format = { fill: colors.pale, font: { color: colors.navy }, wrapText: true, verticalAlignment: "center" };
guide.getRange("A6:H6").values = [["核验项目", "数量/结果", "口径", "核验项目", "数量/结果", "口径", "状态", "备注"]];
styleHeader(guide.getRange("A6:H6"));
guide.getRange("A7:H10").values = [
  ["NIST压力质量样本", null, "12压力×10温度", "原始报文样本", null, "含停车充电和充电后重基线", null, ""],
  ["NIST通过数", null, "系统值与Excel公式差≤1e-10", "参与计算样本", null, "上/下电窗口及充电剔除后", null, ""],
  ["物理耗氢", null, "仅有效混动区间", "SOC平衡氢耗", null, "物理耗氢+电池净放电折氢", null, ""],
  ["混动里程", null, "仅混动区间", "纯电里程", null, "只统计，不进入混动氢耗分母", null, ""],
];
guide.getRange("B7").formulas = [[`=COUNTA('120组压力质量验算'!$A$2:$A$121)`]];
guide.getRange("E7").formulas = [[`=COUNTA('原始报文120条'!$A$2:$A$121)`]];
guide.getRange("G7").formulas = [[`=IF(AND(B7=120,E7=120),"完整","不完整")`]];
guide.getRange("B8").formulas = [[`=COUNTIF('120组压力质量验算'!$U$2:$U$121,"通过")`]];
guide.getRange("E8").formulas = [[`='每日与区间汇总'!$C$4`]];
guide.getRange("G8").formulas = [[`=IF(B8=120,"通过","不通过")`]];
guide.getRange("B9").formulas = [[`='每日与区间汇总'!$J$4`]];
guide.getRange("E9").formulas = [[`='每日与区间汇总'!$N$4`]];
guide.getRange("G9").formulas = [[`='每日与区间汇总'!$H$4`]];
guide.getRange("B10").formulas = [[`='每日与区间汇总'!$O$4`]];
guide.getRange("E10").formulas = [[`=SUMIF('每日与区间汇总'!$B$9:$B$11,"PURE_ELECTRIC",'每日与区间汇总'!$Y$9:$Y$11)`]];
guide.getRange("G10").values = [["可追溯"]];
styleBody(guide.getRange("A7:H10"));
guide.getRange("B9:B10").format.numberFormat = "0.000000";
guide.getRange("E9:E10").format.numberFormat = "0.000000";
guide.getRange("G7:G10").conditionalFormats.add("containsText", { text: "通过", format: { fill: colors.green, font: { color: colors.greenText, bold: true } } });
guide.getRange("G7:G10").conditionalFormats.add("containsText", { text: "完整", format: { fill: colors.green, font: { color: colors.greenText, bold: true } } });
guide.getRange("G7:G10").conditionalFormats.add("containsText", { text: "可追溯", format: { fill: colors.cyan, font: { color: colors.navy, bold: true } } });

guide.getRange("A13:H13").values = [["工作表", "核验内容", "人工操作", "", "", "", "", ""]];
styleHeader(guide.getRange("A13:H13"));
const guideRows = [
  ["参数与系数", "所有常量和9组NIST系数", "先确认车型参数，再复核公式引用"],
  ["120组压力质量验算", "120组压力/温度的完整中间值和最终差异", "逐行查看Z分项→Z→分子/分母→质量→通过结果"],
  ["原始报文120条", "车辆状态、充电状态、运行模式、压力、温度、SOC、里程及JSON", "核对原始字段，不修改计算列"],
  ["样本计算明细", "每条报文换算质量、边界过滤、充电识别和区间归属", "按报文ID追踪排除原因及端点候选"],
  ["每日与区间汇总", "端点中位数、分段耗氢、SOC修正、里程和百公里氢耗", "从最终结果反查起止报文ID"],
];
guide.getRange("A14:C18").values = guideRows;
for (let row = 14; row <= 18; row++) guide.getRange(`C${row}:H${row}`).merge();
styleBody(guide.getRange("A14:H18"));
guide.getRange("A21:H21").merge();
guide.getRange("A21").values = [["关键规则：停车充电 = 车辆状态为熄火(2) 且充电状态为停车充电(1)。充电期间不计算氢耗；充电结束后重新建立SOC、压力、温度和里程基线，仅对运行区间SOC变化进行氢气等效修正。"]];
guide.getRange("A21:H21").format = { fill: colors.orange, font: { bold: true, color: colors.orangeText }, wrapText: true };
guide.getRange("A21:H21").format.rowHeight = 48;
setWidths(guide, [24, 18, 34, 24, 18, 34, 14, 28]);

await fs.mkdir(previewDir, { recursive: true });

const checks = {};
for (const [name, range] of [
  ["guide", "验算说明!A1:H21"],
  ["parameters", "参数与系数!A1:F33"],
  ["grid", "120组压力质量验算!A1:U12"],
  ["raw", "原始报文120条!A1:N15"],
  ["detail", "样本计算明细!A1:AL15"],
  ["summary", "每日与区间汇总!A1:AB18"],
]) {
  const [sheetName, a1] = range.split("!");
  const inspection = await workbook.inspect({ kind: "table", sheetId: sheetName, range: a1, include: "values,formulas", tableMaxRows: 20, tableMaxCols: 40, maxChars: 12000 });
  checks[name] = inspection.ndjson;
}

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

for (const sheetName of ["验算说明", "参数与系数", "120组压力质量验算", "原始报文120条", "样本计算明细", "每日与区间汇总"]) {
  const preview = await workbook.render({ sheetName, autoCrop: "all", scale: 0.8, format: "png" });
  await fs.writeFile(`${previewDir}/${sheetName}.png`, new Uint8Array(await preview.arrayBuffer()));
}

const xlsx = await SpreadsheetFile.exportXlsx(workbook);
await xlsx.save(outputPath);
await fs.writeFile(`${outputDir}/verification.json`, JSON.stringify({ outputPath, checks, formulaErrors: formulaErrors.ndjson }, null, 2));
console.log(JSON.stringify({ outputPath, previewDir, formulaErrors: formulaErrors.ndjson }));