import { createRequire } from "node:module";
import { spawn } from "node:child_process";
import { mkdtemp, readFile, rm, writeFile } from "node:fs/promises";
import { join } from "node:path";
import { tmpdir } from "node:os";

const require = createRequire(import.meta.url);
const ExcelJS = require("../node_modules/exceljs");
const officeCrypto = require("../node_modules/officecrypto-tool");

const stockHeaderAliases = {
  category1: ["类目1", "一级类目", "大类"],
  category2: ["类目2", "二级类目", "小类"],
  barcode: ["商品编号", "商品编码", "商品条码", "产品编号", "产品编码", "产品条码", "国际条码", "条形码", "条码", "编码", "EAN", "EAN码", "EAN13", "GTIN", "Barcode"],
  productName: ["商品名称", "品名", "名称", "产品名称"],
  quantity: ["可销售", "数量", "库存", "QTY"],
  hkd: ["HKD", "港币", "到港价", "现货价", "定价HKD"],
  usd: ["USD", "美金"],
  rmb: ["RMB", "人民币"],
  cartonSize: ["箱规", "装箱数"],
  remark: ["备注"],
  shelfLife: ["剩余保质期", "保质期"],
  productionDate: ["生产日期"]
};

const pricingAliases = {
  brand: ["品牌", "品牌.名称"],
  owner: ["品牌负责人", "品牌负责人.姓名", "负责人"],
  barcode: ["商品编号", "商品编码", "商品条码", "产品编号", "产品编码", "产品条码", "国际条码", "条形码", "条码", "编码", "EAN", "EAN码", "EAN13", "GTIN", "Barcode"],
  productName: ["商品名称", "品名", "名称"],
  costHkd: ["成本HKD", "成本", "成本价"],
  quantity: ["数量", "可销售", "QTY"],
  priceHkd: ["定价HKD", "定价 HKD", "HKD"],
  priceUsd: ["定价USD", "定价 USD", "USD"],
  priceRmb: ["定价RMB", "定价 RMB", "RMB"],
  remark: ["备注"],
  shelfLife: ["剩余保质期", "保质期"]
};

const brandAliases = {
  brand: ["品牌.名称", "品牌", "Brand"],
  owner: ["品牌负责人.姓名", "品牌负责人", "负责人"],
  barcode: ["编码", "商品编号", "商品编码", "商品条码", "产品编号", "产品编码", "产品条码", "国际条码", "条形码", "条码", "EAN", "EAN码", "EAN13", "GTIN"],
  productName: ["名称", "商品名称", "品名"]
};

const costAliases = {
  barcode: ["条码", "商品条码", "商品编码", "产品条码", "产品编码", "物料编码", "国际条码", "条形码", "SKU条码", "69码", "69条码", "商品编号", "产品编号", "编码", "EAN", "EAN码", "EAN13", "GTIN", "Barcode"],
  productName: ["品名", "商品名称", "物料名称", "名称"],
  cost: ["成本", "实际成本", "采购成本", "最新成本", "库存成本", "平均成本", "入库成本", "成本HKD", "实际成本HKD", "成本价", "成本价HKD", "港币成本", "HKD成本", "HKD", "单价", "单价HKD", "采购价", "采购单价", "入库单价", "含税成本", "不含税成本", "总计", "总计最大值", "总计最高值", "总计期初&入库两者取最大值", "总计期初入库两者取最大值", "总计期初入库取最大值", "最大值", "平均值项单价HKD"]
};

const purchaseAliases = {
  barcode: ["商品编号", "商品编码", "商品条码", "产品编号", "产品编码", "产品条码", "物料编码", "国际条码", "条形码", "条码", "编码", "SKU编码", "SKU", "EAN", "EAN码", "EAN13", "GTIN", "Barcode"],
  productName: ["商品名称", "品名", "物料名称", "名称", "产品名称"],
  quantity: ["数量", "采购数量", "进货数量", "入库数量", "QTY"],
  unitPrice: ["单价", "单价HKD", "采购单价", "进货单价", "入库单价", "价格", "成本", "成本HKD", "HKD", "港币"],
  remark: ["备注"],
  shelfLife: ["剩余保质期", "保质期"],
  productionDate: ["生产日期"]
};

function workbookError(message, { fileName = "", fileLabel = "Excel文件", stage = "读取文件", statusCode = 400, details = {} } = {}) {
  const error = new Error(`${fileLabel}${fileName ? `「${fileName}」` : ""}：${message}`);
  error.statusCode = statusCode;
  error.details = {
    fileLabel,
    fileName,
    stage,
    ...details
  };
  return error;
}

function passwordCandidates(password) {
  const raw = String(password ?? "");
  const trimmed = raw.trim();
  return [...new Set([raw, trimmed].filter((value) => value.length > 0))];
}

function runPowerShell(script, env = {}) {
  return new Promise((resolve, reject) => {
    const child = spawn("powershell.exe", ["-NoProfile", "-ExecutionPolicy", "Bypass", "-Command", script], {
      windowsHide: true,
      env: { ...process.env, ...env }
    });
    let stdout = "";
    let stderr = "";
    const timer = setTimeout(() => {
      child.kill();
      reject(new Error("WPS 解密超时"));
    }, 180000);
    child.stdout.on("data", (chunk) => { stdout += chunk.toString("utf8"); });
    child.stderr.on("data", (chunk) => { stderr += chunk.toString("utf8"); });
    child.on("error", (error) => {
      clearTimeout(timer);
      reject(error);
    });
    child.on("close", (code) => {
      clearTimeout(timer);
      if (code) return reject(new Error((stderr || stdout || `PowerShell 退出码 ${code}`).trim()));
      resolve(stdout.trim());
    });
  });
}

async function decryptWorkbookWithWps(buffer, password) {
  if (process.platform !== "win32") throw new Error("仅 Windows 环境支持 WPS 兜底解密");
  const tempDir = await mkdtemp(join(tmpdir(), "hanmi-wps-decrypt-"));
  const inputPath = join(tempDir, "input.xlsx");
  const outputPath = join(tempDir, "output.xlsx");
  const script = String.raw`
$ErrorActionPreference = "Stop"
$src = $env:HANMI_WPS_INPUT
$out = $env:HANMI_WPS_OUTPUT
$pwd = $env:HANMI_WPS_PASSWORD
$app = $null
$wb = $null
$new = $null
try {
  $app = New-Object -ComObject 'ket.Application'
  $app.Visible = $false
  $app.DisplayAlerts = $false
  $wb = $app.Workbooks.Open($src, 0, $true, 5, $pwd)
  $new = $app.Workbooks.Add()
  while ($new.Worksheets.Count -gt 1) {
    $new.Worksheets.Item($new.Worksheets.Count).Delete()
  }
  $first = $true
  foreach ($sheet in @($wb.Worksheets)) {
    if ($first) {
      $target = $new.Worksheets.Item(1)
      $target.Name = $sheet.Name
      $first = $false
    } else {
      $target = $new.Worksheets.Add([System.Type]::Missing, $new.Worksheets.Item($new.Worksheets.Count))
      $target.Name = $sheet.Name
    }
    [void]$sheet.UsedRange.Copy()
    [void]$target.Range('A1').PasteSpecial(-4104)
    [void]$target.Range('A1').PasteSpecial(-4122)
  }
  [void]$new.SaveAs($out, 51)
  $new.Close($false)
  $wb.Close($false)
  $app.Quit()
  Write-Output "ok"
} finally {
  if ($new) { try { $new.Close($false) } catch {} }
  if ($wb) { try { $wb.Close($false) } catch {} }
  if ($app) { try { $app.Quit() } catch {} }
  [GC]::Collect()
  [GC]::WaitForPendingFinalizers()
}
`;
  try {
    await writeFile(inputPath, buffer);
    await runPowerShell(script, {
      HANMI_WPS_INPUT: inputPath,
      HANMI_WPS_OUTPUT: outputPath,
      HANMI_WPS_PASSWORD: String(password ?? "")
    });
    const decrypted = await readFile(outputPath);
    if (officeCrypto.isEncrypted(Buffer.from(decrypted))) {
      throw new Error("WPS 已打开文件，但导出的临时文件仍是加密状态");
    }
    return decrypted;
  } finally {
    await rm(tempDir, { recursive: true, force: true, maxRetries: 3, retryDelay: 150 }).catch(() => {});
  }
}

function normalizeHeader(value) {
  return String(value ?? "")
    .normalize("NFKC")
    .replace(/[()（）]/g, "")
    .replace(/[\s_\-\/\\:：.。]+/g, "")
    .toUpperCase()
    .trim();
}

function aliasKeys(aliases) {
  return new Set(aliases.map(normalizeHeader));
}

function scientificIntegerText(text) {
  const match = String(text || "").match(/^([+-]?)(\d+)(?:\.(\d+))?E([+-]?\d+)$/i);
  if (!match) return "";
  const sign = match[1] === "-" ? "-" : "";
  if (sign) return "";
  const integer = match[2];
  const fraction = match[3] || "";
  const exponent = Number(match[4]);
  if (!Number.isInteger(exponent)) return "";
  const digits = `${integer}${fraction}`;
  const decimalIndex = integer.length + exponent;
  if (decimalIndex <= 0) return "";
  if (decimalIndex >= digits.length) return `${digits}${"0".repeat(decimalIndex - digits.length)}`;
  const whole = digits.slice(0, decimalIndex);
  const rest = digits.slice(decimalIndex);
  return /^0*$/.test(rest) ? whole : "";
}

function normalizeBarcode(value) {
  const text = textOf(value)
    .normalize("NFKC")
    .replace(/[\u200B-\u200D\uFEFF\u00A0]/g, "")
    .trim()
    .replace(/^'+/, "")
    .replace(/\s+/g, "")
    .toUpperCase();
  return (scientificIntegerText(text) || text).replace(/\.0+$/g, "");
}

function barcodeDigits(value) {
  return normalizeBarcode(value).replace(/\D+/g, "");
}

function isLikelyBarcode(value) {
  return barcodeDigits(value).length >= 8;
}

function normalizeNumber(value) {
  const text = textOf(value).normalize("NFKC").replace(/,/g, "").trim();
  if (!text || text === "/" || text.toUpperCase() === "A/N" || text.toUpperCase() === "#N/A") return null;
  const match = text.match(/-?\d+(?:\.\d+)?/);
  if (!match) return null;
  const number = Number(match[0]);
  return Number.isFinite(number) ? number : null;
}

function textOf(value) {
  if (value === null || value === undefined) return "";
  if (value instanceof Date) return value.toISOString().slice(0, 10);
  if (typeof value === "object") {
    if (Array.isArray(value.richText)) return value.richText.map((item) => item.text || "").join("").trim();
    if (value.text !== undefined) return String(value.text ?? "").trim();
    if (value.result !== undefined) {
      if (value.result && typeof value.result === "object") return textOf(value.result);
      return String(value.result ?? "").trim();
    }
    if (value.error) return String(value.error).trim();
    if (value.formula) return String(value.result ?? "").trim();
  }
  return String(value).trim();
}

function cellText(cell) {
  if (!cell) return "";
  if (typeof cell.text === "string" && cell.text.trim()) return cell.text.trim();
  return textOf(cell.value);
}

function formatDateOnly(value) {
  if (!(value instanceof Date)) return "";
  const time = value.getTime();
  if (!Number.isFinite(time)) return "";
  return value.toISOString().slice(0, 10);
}

function dateCellText(cell) {
  if (!cell) return "";
  const value = cell.value;
  const directDate = formatDateOnly(value);
  if (directDate) return directDate;
  if (value && typeof value === "object") {
    const resultDate = formatDateOnly(value.result);
    if (resultDate) return resultDate;
  }
  return cellText(cell);
}

function combineShelfLife(shelfLife, productionDate) {
  const shelfText = String(shelfLife || "").trim();
  const productionText = String(productionDate || "").trim();
  if (!productionText) return shelfText;
  if (!shelfText || shelfText === productionText) return productionText;
  return `${productionText}；原剩余保质期：${shelfText}`;
}

function rowValues(row, maxColumn = row.cellCount || 0) {
  const values = [];
  for (let column = 1; column <= maxColumn; column += 1) {
    values[column] = cellText(row.getCell(column));
  }
  return values;
}

function isRedCell(cell) {
  const fontColor = String(cell?.font?.color?.argb || "").toUpperCase();
  const fillColor = String(cell?.fill?.fgColor?.argb || cell?.fill?.bgColor?.argb || "").toUpperCase();
  return fontColor === "FFFF0000"
    || fontColor === "FFC00000"
    || fontColor.endsWith("FF0000")
    || fillColor === "FFFF0000"
    || fillColor === "FFC00000"
    || fillColor === "FFFFC7CE";
}

function rowHasRedMarker(row, maxColumn) {
  for (let column = 1; column <= maxColumn; column += 1) {
    if (isRedCell(row.getCell(column))) return true;
  }
  return false;
}

function headerMapFromRow(row, aliasesByField, maxColumn) {
  const result = {};
  const aliasMap = Object.fromEntries(
    Object.entries(aliasesByField).map(([field, aliases]) => [field, aliasKeys(aliases)])
  );
  for (let column = 1; column <= maxColumn; column += 1) {
    const key = normalizeHeader(cellText(row.getCell(column)));
    if (!key) continue;
    for (const [field, keys] of Object.entries(aliasMap)) {
      if (!result[field] && keys.has(key)) result[field] = column;
    }
  }
  return result;
}

function findHeaderRow(worksheet, aliasesByField, requiredFields, maxRows = 40) {
  const maxColumn = Math.max(worksheet.columnCount || 0, 1);
  for (let rowNumber = 1; rowNumber <= Math.min(worksheet.rowCount, maxRows); rowNumber += 1) {
    const row = worksheet.getRow(rowNumber);
    const header = headerMapFromRow(row, aliasesByField, maxColumn);
    if (requiredFields.every((field) => header[field])) {
      return { rowNumber, header, maxColumn };
    }
  }
  return null;
}

function parseDateFromFileName(fileName, fallbackYear = new Date().getFullYear()) {
  const text = String(fileName || "").replace(/\.(xlsx|xlsm|xls)$/i, "");
  const dateFromParts = (year, month, day) => {
    const fullYear = Number(year) < 100 ? 2000 + Number(year) : Number(year);
    const monthNo = Number(month);
    const dayNo = Number(day);
    const lastDay = new Date(Date.UTC(fullYear, monthNo, 0)).getUTCDate();
    if (!Number.isInteger(fullYear) || fullYear < 2000 || fullYear > 2099) return "";
    if (!Number.isInteger(monthNo) || monthNo < 1 || monthNo > 12) return "";
    if (!Number.isInteger(dayNo) || dayNo < 1 || dayNo > lastDay) return "";
    return `${fullYear}-${String(monthNo).padStart(2, "0")}-${String(dayNo).padStart(2, "0")}`;
  };
  let match = text.match(/(?:^|[^\d])((?:19|20)\d{2})[.\-_/年](\d{1,2})[.\-_/月](\d{1,2})日?(?=$|[^\d])/);
  if (match) return dateFromParts(match[1], match[2], match[3]);
  match = text.match(/(?:^|[^\d])((?:19|20)\d{2})(\d{2})(\d{2})(?=$|[^\d])/);
  if (match) return dateFromParts(match[1], match[2], match[3]);
  match = text.match(/(?:^|[^\d])(\d{1,2})[.\-_/月](\d{1,2})日?(?=$|[^\d])/);
  if (match) return dateFromParts(fallbackYear, match[1], match[2]);
  match = text.match(/(?:^|[^\d])(\d{2})(\d{2})(?=$|[^\d])/);
  if (match) return dateFromParts(fallbackYear, match[1], match[2]);
  return "";
}

async function loadWorkbook(buffer, { password = "", fileName = "", fileLabel = "Excel文件" } = {}) {
  let body = buffer;
  let encrypted = false;
  if (officeCrypto.isEncrypted(Buffer.from(buffer))) {
    encrypted = true;
    const candidates = passwordCandidates(password);
    if (!candidates.length) {
      throw workbookError("文件已加密，请先输入该文件的打开密码", {
        fileName,
        fileLabel,
        stage: "解密",
        details: { encrypted: true, passwordProvided: false }
      });
    }
    let decrypted = null;
    let lastError = null;
    let wpsFallbackError = null;
    for (const candidate of candidates) {
      try {
        decrypted = await officeCrypto.decrypt(Buffer.from(buffer), { password: candidate });
        break;
      } catch (error) {
        lastError = error;
      }
    }
    if (!decrypted) {
      for (const candidate of candidates) {
        try {
          decrypted = await decryptWorkbookWithWps(Buffer.from(buffer), candidate);
          break;
        } catch (error) {
          wpsFallbackError = error;
        }
      }
    }
    if (!decrypted) {
      throw workbookError("加密表解密失败，请检查该文件密码；如果 WPS 能打开但这里失败，请确认业务电脑已安装 WPS 表格且允许本地自动化调用", {
        fileName,
        fileLabel,
        stage: "解密",
        details: {
          encrypted: true,
          passwordProvided: true,
          retriedTrimmedPassword: candidates.length > 1,
          sourceError: lastError?.message || "",
          wpsFallbackAttempted: process.platform === "win32",
          wpsFallbackError: wpsFallbackError?.message || ""
        }
      });
    }
    body = decrypted;
  }
  const workbook = new ExcelJS.Workbook();
  try {
    await workbook.xlsx.load(body);
  } catch (error) {
    throw workbookError("文件无法按 xlsx/xlsm 解析，请确认不是旧版 .xls、损坏文件或受特殊保护的工作簿", {
      fileName,
      fileLabel,
      stage: "解析工作簿",
      details: { encrypted, sourceError: error?.message || "" }
    });
  }
  return { workbook, encrypted };
}

async function parseWorkbookWithContext(buffer, fileName, fileLabel, options = {}) {
  try {
    return await loadWorkbook(buffer, { ...options, fileName, fileLabel });
  } catch (error) {
    if (error.details?.fileLabel) throw error;
    throw workbookError(error.message || "文件读取失败", {
      fileName,
      fileLabel,
      stage: "读取文件",
      statusCode: error.statusCode || 400,
      details: error.details || {}
    });
  }
}

export function parseQuoteDateFromFileName(fileName) {
  return parseDateFromFileName(fileName);
}

export async function parseStockWorkbook(buffer, fileName, options = {}) {
  const { workbook, encrypted } = await parseWorkbookWithContext(buffer, fileName, "现货表");
  const quoteDate = parseDateFromFileName(fileName);
  const rows = [];
  const allRows = [];
  const sheetSummaries = [];
  const errors = [];

  for (const worksheet of workbook.worksheets) {
    const headerInfo = findHeaderRow(worksheet, stockHeaderAliases, ["barcode", "productName"]);
    if (!headerInfo) {
      sheetSummaries.push({ sheetName: worksheet.name, status: "skipped", reason: "未找到条码/商品名称表头" });
      continue;
    }
    const { rowNumber, header, maxColumn } = headerInfo;
    let redRows = 0;
    let importedRows = 0;
    for (let rowNumberIndex = rowNumber + 1; rowNumberIndex <= worksheet.rowCount; rowNumberIndex += 1) {
      const row = worksheet.getRow(rowNumberIndex);
      const barcode = normalizeBarcode(cellText(row.getCell(header.barcode)));
      const productName = cellText(row.getCell(header.productName));
      const baseRow = {
        sourceSheet: worksheet.name,
        sourceRow: rowNumberIndex,
        category1: header.category1 ? cellText(row.getCell(header.category1)) : "",
        category2: header.category2 ? cellText(row.getCell(header.category2)) : "",
        barcode,
        barcodeKey: barcode,
        productName,
        quantity: header.quantity ? normalizeNumber(cellText(row.getCell(header.quantity))) : null,
        stockHkd: header.hkd ? cellText(row.getCell(header.hkd)) : "",
        stockUsd: header.usd ? cellText(row.getCell(header.usd)) : "",
        stockRmb: header.rmb ? cellText(row.getCell(header.rmb)) : "",
        cartonSize: header.cartonSize ? cellText(row.getCell(header.cartonSize)) : "",
        remark: header.remark ? cellText(row.getCell(header.remark)) : "",
        shelfLife: combineShelfLife(
          header.shelfLife ? cellText(row.getCell(header.shelfLife)) : "",
          header.productionDate ? dateCellText(row.getCell(header.productionDate)) : ""
        )
      };
      if (barcode && productName) allRows.push(baseRow);
      if (!rowHasRedMarker(row, maxColumn)) continue;
      redRows += 1;
      if (!barcode || !productName) {
        if (errors.length < 200) {
          errors.push({ sheetName: worksheet.name, rowNumber: rowNumberIndex, message: "标红行缺少条码或商品名称" });
        }
        continue;
      }
      importedRows += 1;
      const shelfLifeRaw = header.shelfLife ? cellText(row.getCell(header.shelfLife)) : "";
      const productionDate = header.productionDate ? dateCellText(row.getCell(header.productionDate)) : "";
      rows.push({
        ...baseRow,
        shelfLife: combineShelfLife(shelfLifeRaw, productionDate),
        shelfLifeRaw,
        productionDate
      });
    }
    sheetSummaries.push({
      sheetName: worksheet.name,
      status: "parsed",
      headerRowNumber: rowNumber,
      redRows,
      importedRows
    });
  }

  if (!rows.length && !(options.allowNoRedRows && allRows.length)) {
    throw Object.assign(new Error("现货表未识别到可处理的标红数据行"), {
      statusCode: 400,
      details: { sheetSummaries, errors }
    });
  }
  return { kind: "stock", fileName, quoteDate, encrypted, rows, allRows, sheetSummaries, errors };
}

export async function parsePurchaseWorkbook(buffer, fileName, password = "") {
  const { workbook, encrypted } = await parseWorkbookWithContext(buffer, fileName, "进货明细", { password });
  const rawRows = [];
  const errors = [];
  const sheetSummaries = [];

  for (const worksheet of workbook.worksheets) {
    const headerInfo = findHeaderRow(worksheet, purchaseAliases, ["barcode"], 30);
    if (!headerInfo) {
      sheetSummaries.push({ sheetName: worksheet.name, status: "skipped", reason: "未找到编码/条码表头" });
      continue;
    }
    let importedRows = 0;
    for (let rowNumber = headerInfo.rowNumber + 1; rowNumber <= worksheet.rowCount; rowNumber += 1) {
      const row = worksheet.getRow(rowNumber);
      const barcode = normalizeBarcode(cellText(row.getCell(headerInfo.header.barcode)));
      if (!barcode || !isLikelyBarcode(barcode)) continue;
      const unitPrice = headerInfo.header.unitPrice ? normalizeNumber(cellText(row.getCell(headerInfo.header.unitPrice))) : null;
      const quantity = headerInfo.header.quantity ? normalizeNumber(cellText(row.getCell(headerInfo.header.quantity))) : null;
      if (unitPrice === null && errors.length < 200) {
        errors.push({ sheetName: worksheet.name, rowNumber, message: "进货明细单价为空或非数字" });
      }
      const shelfLifeRaw = headerInfo.header.shelfLife ? cellText(row.getCell(headerInfo.header.shelfLife)) : "";
      const productionDate = headerInfo.header.productionDate ? dateCellText(row.getCell(headerInfo.header.productionDate)) : "";
      importedRows += 1;
      rawRows.push({
        sourceSheet: worksheet.name,
        sourceRow: rowNumber,
        barcode,
        barcodeKey: barcode,
        productName: headerInfo.header.productName ? cellText(row.getCell(headerInfo.header.productName)) : "",
        quantity,
        costHkd: unitPrice,
        remark: headerInfo.header.remark ? cellText(row.getCell(headerInfo.header.remark)) : "",
        shelfLife: combineShelfLife(shelfLifeRaw, productionDate),
        shelfLifeRaw,
        productionDate
      });
    }
    sheetSummaries.push({
      sheetName: worksheet.name,
      status: "parsed",
      headerRowNumber: headerInfo.rowNumber,
      importedRows
    });
  }

  if (!rawRows.length) {
    throw Object.assign(new Error("进货明细未识别到可用数据"), {
      statusCode: 400,
      details: { sheetSummaries, errors }
    });
  }

  const deduped = new Map();
  for (const row of rawRows) {
    const key = normalizeBarcode(row.barcode);
    const existing = deduped.get(key);
    if (!existing) {
      deduped.set(key, { ...row, mergedRows: 1, sourceRows: [row.sourceRow] });
      continue;
    }
    existing.mergedRows += 1;
    existing.sourceRows.push(row.sourceRow);
    existing.quantity = Number.isFinite(existing.quantity) || Number.isFinite(row.quantity)
      ? Number(existing.quantity || 0) + Number(row.quantity || 0)
      : null;
    if (!existing.productName && row.productName) existing.productName = row.productName;
    if (!existing.remark && row.remark) existing.remark = row.remark;
    if (!existing.shelfLife && row.shelfLife) existing.shelfLife = row.shelfLife;
    if (Number.isFinite(row.costHkd) && (!Number.isFinite(existing.costHkd) || row.costHkd > existing.costHkd)) {
      existing.costHkd = row.costHkd;
      existing.sourceSheet = row.sourceSheet;
      existing.sourceRow = row.sourceRow;
    }
  }

  const rows = [...deduped.values()];
  return {
    kind: "purchase",
    fileName,
    quoteDate: parseDateFromFileName(fileName),
    encrypted,
    rows,
    rawRows,
    sheetSummaries,
    errors
  };
}

export async function parsePricingWorkbook(buffer, fileName, password = "") {
  const { workbook, encrypted } = await parseWorkbookWithContext(buffer, fileName, "定价表", { password });
  const priceSheet = workbook.worksheets[0];
  const brandSheet = workbook.worksheets[2];
  if (!priceSheet) throw Object.assign(new Error("定价表缺少第一张工作表"), { statusCode: 400 });
  if (!brandSheet) throw Object.assign(new Error("定价表缺少第三张品牌&品名工作表"), { statusCode: 400 });

  const priceHeaderInfo = findHeaderRow(priceSheet, pricingAliases, ["barcode", "productName"], 20);
  if (!priceHeaderInfo) {
    throw Object.assign(new Error("定价表第一张表未找到商品编号/商品名称表头"), { statusCode: 400 });
  }
  const pricedRows = [];
  const pricedKeys = new Set();
  for (let rowNumber = priceHeaderInfo.rowNumber + 1; rowNumber <= priceSheet.rowCount; rowNumber += 1) {
    const row = priceSheet.getRow(rowNumber);
    const barcode = normalizeBarcode(cellText(row.getCell(priceHeaderInfo.header.barcode)));
    if (!barcode) continue;
    pricedKeys.add(barcode);
    pricedRows.push({
      rowNumber,
      barcode,
      productName: priceHeaderInfo.header.productName ? cellText(row.getCell(priceHeaderInfo.header.productName)) : "",
      priceHkd: priceHeaderInfo.header.priceHkd ? cellText(row.getCell(priceHeaderInfo.header.priceHkd)) : "",
      remark: priceHeaderInfo.header.remark ? cellText(row.getCell(priceHeaderInfo.header.remark)) : "",
      shelfLife: priceHeaderInfo.header.shelfLife ? cellText(row.getCell(priceHeaderInfo.header.shelfLife)) : ""
    });
  }

  const brandHeaderInfo = findHeaderRow(brandSheet, brandAliases, ["barcode", "brand"], 20);
  if (!brandHeaderInfo) {
    throw Object.assign(new Error("定价表第三张表未找到编码/品牌表头"), { statusCode: 400 });
  }
  const brandRows = [];
  const brandByBarcode = new Map();
  for (let rowNumber = brandHeaderInfo.rowNumber + 1; rowNumber <= brandSheet.rowCount; rowNumber += 1) {
    const row = brandSheet.getRow(rowNumber);
    const barcode = normalizeBarcode(cellText(row.getCell(brandHeaderInfo.header.barcode)));
    if (!barcode || brandByBarcode.has(barcode)) continue;
    const item = {
      barcode,
      brand: cellText(row.getCell(brandHeaderInfo.header.brand)),
      owner: brandHeaderInfo.header.owner ? cellText(row.getCell(brandHeaderInfo.header.owner)) : "",
      productName: brandHeaderInfo.header.productName ? cellText(row.getCell(brandHeaderInfo.header.productName)) : ""
    };
    brandRows.push(item);
    brandByBarcode.set(barcode, item);
  }

  return {
    kind: "pricing",
    fileName,
    quoteDate: parseDateFromFileName(fileName),
    encrypted,
    priceSheetName: priceSheet.name,
    brandSheetName: brandSheet.name,
    pricedRows,
    pricedKeys: [...pricedKeys],
    brandRows
  };
}

export async function parseCostWorkbook(buffer, fileName, password = "") {
  const { workbook, encrypted } = await parseWorkbookWithContext(buffer, fileName, "成本表", { password });
  const rows = [];
  const errors = [];
  const sheetSummaries = [];
  let inheritedHeader = null;
  const noDateSheet = workbook.worksheets.find((worksheet) => normalizeHeader(worksheet.name) === normalizeHeader("无日期"));
  const worksheetsToParse = noDateSheet ? [noDateSheet] : workbook.worksheets;
  if (noDateSheet) {
    for (const worksheet of workbook.worksheets) {
      if (worksheet !== noDateSheet) {
        sheetSummaries.push({ sheetName: worksheet.name, status: "skipped", reason: "已按规则优先读取无日期 Sheet" });
      }
    }
  }

  for (const worksheet of worksheetsToParse) {
    let headerInfo = findHeaderRow(worksheet, costAliases, ["barcode", "cost"], 30);
    let inherited = false;
    if (!headerInfo) {
      if (!inheritedHeader) {
        sheetSummaries.push({ sheetName: worksheet.name, status: "skipped", reason: "未找到条码/成本表头" });
        continue;
      }
      headerInfo = { rowNumber: 0, ...inheritedHeader };
      inherited = true;
    } else {
      inheritedHeader = { header: headerInfo.header, maxColumn: headerInfo.maxColumn };
    }
    let importedRows = 0;
    worksheet.eachRow({ includeEmpty: false }, (row, rowNumber) => {
      if (!inherited && rowNumber <= headerInfo.rowNumber) return;
      const barcode = normalizeBarcode(cellText(row.getCell(headerInfo.header.barcode)));
      if (!barcode || !isLikelyBarcode(barcode)) return;
      const costText = cellText(row.getCell(headerInfo.header.cost));
      const cost = normalizeNumber(costText);
      if (cost === null) {
        if (errors.length < 200) {
          errors.push({ sheetName: worksheet.name, rowNumber, message: "成本为空或非数字" });
        }
        return;
      }
      importedRows += 1;
      rows.push({
        sourceSheet: worksheet.name,
        sourceRow: rowNumber,
        barcode,
        barcodeKey: barcode,
        productName: headerInfo.header.productName ? cellText(row.getCell(headerInfo.header.productName)) : "",
        costHkd: cost
      });
    });
    sheetSummaries.push({
      sheetName: worksheet.name,
      status: inherited ? "parsed_with_inherited_header" : "parsed",
      headerRowNumber: headerInfo.rowNumber,
      importedRows
    });
  }

  if (!rows.length) {
    throw Object.assign(new Error("成本表未识别到可用成本数据"), {
      statusCode: 400,
      details: { sheetSummaries, errors }
    });
  }
  return { kind: "cost", fileName, quoteDate: parseDateFromFileName(fileName), encrypted, rows, sheetSummaries, errors };
}

function stylePricingSheet(sheet) {
  sheet.views = [{ state: "frozen", ySplit: 1 }];
  sheet.autoFilter = "A1:O1";
  sheet.columns = [
    { width: 24 },
    { width: 14 },
    { width: 20 },
    { width: 56 },
    { width: 12 },
    { width: 12 },
    { width: 12 },
    { width: 12 },
    { width: 12 },
    { width: 18 },
    { width: 16 },
    { width: 18 },
    { width: 18 },
    { width: 10 },
    { width: 14 }
  ];
  const header = sheet.getRow(1);
  header.font = { bold: true, color: { argb: "FFFFFFFF" } };
  header.fill = { type: "pattern", pattern: "solid", fgColor: { argb: "FF1F5FBF" } };
  header.alignment = { vertical: "middle", horizontal: "center" };
  header.height = 24;
  for (let rowNumber = 2; rowNumber <= sheet.rowCount; rowNumber += 1) {
    const row = sheet.getRow(rowNumber);
    row.getCell(5).numFmt = "0.00";
    if (row.getCell(5).value === "A/N") {
      row.getCell(5).font = { color: { argb: "FFC2413A" }, bold: true };
      if (row.getCell(11).text === "未匹配成本") row.getCell(11).font = { color: { argb: "FFC2413A" } };
    }
  }
}

function addPricingRowsSheet(workbook, sheetName, rows, { quoteDate, defaultSource }) {
  const sheet = workbook.addWorksheet(sheetName);
  sheet.addRow([
    "品牌",
    "品牌负责人",
    "商品编号",
    "商品名称",
    "成本HKD",
    "数量",
    "定价 HKD",
    "定价 USD",
    "定价 RMB",
    "剩余保质期",
    "备注",
    "来源",
    "源Sheet",
    "源行号",
    "处理日期"
  ]);
  for (const row of rows) {
    const remark = String(row.remark || "").trim() || (row.costStatus === "matched" ? "" : "未匹配成本");
    sheet.addRow([
      row.brand || "",
      row.owner || "",
      row.barcode || "",
      row.productName || "",
      Number.isFinite(Number(row.costHkd)) ? Number(row.costHkd) : (row.costStatus === "matched" ? row.costHkd : "A/N"),
      row.quantity ?? "",
      row.priceHkd || "",
      row.priceUsd || "",
      row.priceRmb || "",
      row.shelfLife || "",
      remark,
      row.sourceLabel || defaultSource || "",
      row.sourceSheet || "",
      Array.isArray(row.sourceRows) ? row.sourceRows.join(",") : (row.sourceRow || ""),
      quoteDate || row.quoteDate || ""
    ]);
  }
  stylePricingSheet(sheet);
  return sheet;
}

export async function buildPricingWorkbook({ quoteDate, rows, purchaseRows = [] }) {
  const workbook = new ExcelJS.Workbook();
  workbook.creator = "Hanmi Local Quote Tool";
  workbook.created = new Date();
  addPricingRowsSheet(workbook, "现货表", rows.map((row) => ({
    ...row,
    sourceLabel: row.sourceType === "carryover" ? "上一批未定价结转" : "本次现货标红"
  })), { quoteDate, defaultSource: "本次现货标红" });
  addPricingRowsSheet(workbook, "进货明细", purchaseRows.map((row) => ({
    ...row,
    costStatus: Number.isFinite(Number(row.costHkd)) ? "matched" : "missing",
    sourceLabel: row.priceHkd ? "进货明细-已同步现货定价" : "进货明细-待定价"
  })), { quoteDate, defaultSource: "进货明细" });
  const summary = workbook.addWorksheet("处理摘要");
  const matched = rows.filter((row) => row.costStatus === "matched").length;
  const missing = rows.length - matched;
  const purchasePriced = purchaseRows.filter((row) => String(row.priceHkd || "").trim()).length;
  summary.addRows([
    ["处理日期", quoteDate || ""],
    ["待定价总行数", rows.length],
    ["成本匹配", matched],
    ["成本缺失 A/N", missing],
    ["进货明细合并后行数", purchaseRows.length],
    ["进货明细已同步现货定价", purchasePriced],
    ["说明", "第一张表供业务填写现货待定价；第二张进货明细已按编码去重，同编码单价取最高，能从当天现货同步定价的已自动填写。"]
  ]);
  summary.columns = [{ width: 20 }, { width: 80 }];
  summary.getColumn(1).font = { bold: true };
  return workbook.xlsx.writeBuffer();
}
