import { mkdtemp, rm, writeFile } from "node:fs/promises";
import os from "node:os";
import path from "node:path";

import * as XLSX from "xlsx";

import {
  buildPdfDiagnosticArtifacts as defaultBuildPdfDiagnosticArtifacts,
  type PdfDiagnosticArtifacts,
} from "../procurement-list/pdf-diagnostics.ts";
import { extractProcurementListFromMultimodalInputs as defaultExtractProcurementListFromMultimodalInputs } from "../procurement-list/procurement-extraction-llm.ts";
import {
  mergeProcurementListObjects,
  type MergeProcurementListObjectsResult,
  type ProcurementListExtractionPayloadWithSource,
  type ProcurementListPdfDiagnosticInput,
} from "../procurement-list/procurement-merge.ts";
import type {
  ProcurementListExtractionExcelRow,
  ProcurementListExtractionFileProfile,
  ProcurementListExtractionImageInput,
  ProcurementListExtractionPayload,
  ProcurementListPurchaseObject,
} from "../procurement-list/procurement-extraction-contract.ts";

export interface UploadedProcurementListFile {
  originalname: string;
  mimetype?: string;
  size: number;
  buffer: Buffer;
}

export interface BuildProcurementListExportWorkbookInput {
  files: UploadedProcurementListFile[];
  buildPdfDiagnosticArtifacts?: typeof defaultBuildPdfDiagnosticArtifacts;
  extractProcurementListFromMultimodalInputs?: typeof defaultExtractProcurementListFromMultimodalInputs;
}

export interface BuildProcurementListExportWorkbookResult {
  buffer: Buffer;
  filename: string;
  contentType: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
}

type FileKind = "pdf" | "image" | "excel";

interface ProcessedAttachment {
  sourceFileName: string;
  payload: ProcurementListExtractionPayloadWithSource;
  pdfDiagnostic?: ProcurementListPdfDiagnosticInput;
  fileAudit: Record<string, unknown>;
}

interface ParsedExcelMaterialAnchor {
  material_name: string;
  specification: string;
  quantity: number;
  unit: string;
  technical_standard: string;
  material_requirement: string;
  technical_requirement: string;
  applicable_conditions: string;
  test_requirement: string;
  acceptance_note: string;
  invoice_type: string;
  brand: string;
  remarks: string;
  delivery_address: string;
  row_index: number;
}

const CONTENT_TYPE = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" as const;

const IMPORT_HEADERS = [
  "名称",
  "规格型号",
  "数量",
  "单位",
  "技术标准",
  "材质要求",
  "技术要求",
  "适用条件",
  "试验测试要求",
  "验收说明",
  "质保期",
  "交货期限",
  "发票种类",
  "品牌",
  "备注",
  "收货地址",
] as const;

function normalizeText(value: unknown): string {
  return String(value ?? "").trim();
}

function normalizeUploadedFileName(value: string): string {
  const text = normalizeText(value);
  if (!/[ÃÄÅÂÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖ×ØÙÚÛÜÝÞßà-ÿ]/.test(text)) {
    return text;
  }
  try {
    const decoded = Buffer.from(text, "latin1").toString("utf8");
    return decoded.includes("\uFFFD") ? text : decoded;
  } catch {
    return text;
  }
}

function compactJoin(values: unknown[], separator = "；"): string {
  const seen = new Set<string>();
  const result: string[] = [];
  for (const value of values) {
    const text = normalizeText(value);
    if (!text || seen.has(text)) {
      continue;
    }
    seen.add(text);
    result.push(text);
  }
  return result.join(separator);
}

function inferFileKind(file: UploadedProcurementListFile): FileKind | null {
  const mimeType = normalizeText(file.mimetype).toLowerCase();
  const extension = path.extname(file.originalname).toLowerCase();

  if (mimeType === "application/pdf" || extension === ".pdf") {
    return "pdf";
  }
  if (mimeType.startsWith("image/") || [".png", ".jpg", ".jpeg", ".webp"].includes(extension)) {
    return "image";
  }
  if (
    mimeType.includes("spreadsheet")
    || mimeType.includes("excel")
    || [".xlsx", ".xls", ".csv"].includes(extension)
  ) {
    return "excel";
  }

  return null;
}

function dataUrl(buffer: Buffer, mimeType = "application/octet-stream"): string {
  return `data:${mimeType};base64,${buffer.toString("base64")}`;
}

function parseExcelRows(buffer: Buffer, sourceFileName: string): ProcurementListExtractionExcelRow[] {
  const workbook = XLSX.read(buffer, { type: "buffer" });
  const rows: ProcurementListExtractionExcelRow[] = [];

  workbook.SheetNames.forEach((sheetName) => {
    const worksheet = workbook.Sheets[sheetName];
    if (!worksheet) {
      return;
    }
    const jsonRows = XLSX.utils.sheet_to_json<Record<string, unknown>>(worksheet, {
      defval: "",
      raw: false,
    });
    jsonRows.forEach((values, index) => {
      rows.push({
        source_file_name: sourceFileName,
        row_index: index + 2,
        values: { sheet: sheetName, ...values },
      });
    });
  });

  return rows;
}

function normalizeHeader(value: unknown): string {
  return normalizeText(value)
    .toLowerCase()
    .replace(/[：:；;，,。.、*]/g, "")
    .replace(/[\s\r\n\t_（）()【】[\]/\\-]/g, "");
}

function parseQuantity(value: unknown): number {
  if (typeof value === "number" && Number.isFinite(value)) {
    return value;
  }
  const match = normalizeText(value).replace(/,/g, "").match(/-?\d+(?:\.\d+)?/);
  if (!match) {
    return 0;
  }
  const parsed = Number(match[0]);
  return Number.isFinite(parsed) ? parsed : 0;
}

function headerMatches(normalizedHeader: string, aliases: string[]): boolean {
  return aliases.some((alias) => {
    const normalizedAlias = normalizeHeader(alias);
    return normalizedHeader === normalizedAlias || (normalizedAlias.length >= 3 && normalizedHeader.includes(normalizedAlias));
  });
}

function pickExcelCell(row: Record<string, unknown>, aliases: string[]): unknown {
  const entries = Object.entries(row).map(([key, value]) => [normalizeHeader(key), value] as const);
  for (const [header, value] of entries) {
    if (normalizeText(value) && headerMatches(header, aliases)) {
      return value;
    }
  }
  return "";
}

function parseExcelMaterialAnchors(buffer: Buffer): ParsedExcelMaterialAnchor[] {
  const workbook = XLSX.read(buffer, { type: "buffer" });
  const materials: ParsedExcelMaterialAnchor[] = [];

  for (const sheetName of workbook.SheetNames) {
    const worksheet = workbook.Sheets[sheetName];
    if (!worksheet) {
      continue;
    }
    const rawRows = XLSX.utils.sheet_to_json<unknown[]>(worksheet, {
      header: 1,
      defval: "",
      blankrows: false,
    });
    const headerRowIndex = rawRows.slice(0, 10).findIndex((row) => {
      const normalizedCells = row.map(normalizeHeader).filter(Boolean);
      const hasName = normalizedCells.some((cell) => headerMatches(cell, ["材料名称", "物料名称", "品名", "名称", "商品名称"]));
      const fieldHits = normalizedCells.filter((cell) => headerMatches(cell, [
        "规格型号",
        "数量",
        "单位",
        "发票种类",
        "收货地址",
      ])).length;
      return hasName && fieldHits >= 1;
    });
    if (headerRowIndex < 0) {
      continue;
    }

    const headers = rawRows[headerRowIndex] ?? [];
    for (let index = headerRowIndex + 1; index < rawRows.length; index += 1) {
      const rowValues = rawRows[index] ?? [];
      const row = Object.fromEntries(headers.map((header, columnIndex) => [
        normalizeText(header) || `column_${columnIndex}`,
        rowValues[columnIndex] ?? "",
      ]));
      const materialName = normalizeText(pickExcelCell(row, ["材料名称", "物料名称", "品名", "名称", "商品名称"]));
      if (!materialName) {
        continue;
      }
      materials.push({
        material_name: materialName,
        specification: normalizeText(pickExcelCell(row, ["规格型号", "规格", "型号", "规格参数"])),
        quantity: parseQuantity(pickExcelCell(row, ["数量", "需求数量", "采购数量"])),
        unit: normalizeText(pickExcelCell(row, ["单位"])),
        technical_standard: normalizeText(pickExcelCell(row, ["技术标准", "执行标准", "标准"])),
        material_requirement: normalizeText(pickExcelCell(row, ["材质要求", "材质"])),
        technical_requirement: normalizeText(pickExcelCell(row, ["技术要求", "要求", "技术参数"])),
        applicable_conditions: normalizeText(pickExcelCell(row, ["适用条件", "适用场景"])),
        test_requirement: normalizeText(pickExcelCell(row, ["试验测试要求", "测试要求", "试验要求"])),
        acceptance_note: normalizeText(pickExcelCell(row, ["验收说明", "验收要求"])),
        invoice_type: normalizeText(pickExcelCell(row, ["发票种类", "发票类型"])),
        brand: normalizeText(pickExcelCell(row, ["品牌", "品牌要求"])),
        remarks: normalizeText(pickExcelCell(row, ["备注/附件", "备注", "备注说明"])),
        delivery_address: normalizeText(pickExcelCell(row, ["收货地址", "交货地址", "送货地址"])),
        row_index: index + 1,
      });
    }
  }

  return materials;
}

function mergeText(preferred: string, fallback: unknown): string {
  return preferred || normalizeText(fallback);
}

function buildExcelAnchorObject(
  anchor: ParsedExcelMaterialAnchor,
  sourceFileName: string,
  extracted?: ProcurementListPurchaseObject,
): ProcurementListPurchaseObject {
  const fields = extracted?.fields;
  return {
    object_role: "primary_item",
    original_name: anchor.material_name,
    recommended_name: anchor.material_name,
    fields: {
      specification: mergeText(anchor.specification, fields?.specification),
      material_requirement: mergeText(anchor.material_requirement, fields?.material_requirement),
      technical_requirement: mergeText(anchor.technical_requirement, fields?.technical_requirement),
      test_requirement: mergeText(anchor.test_requirement, fields?.test_requirement),
      technical_standard: mergeText(anchor.technical_standard, fields?.technical_standard),
      applicable_conditions: mergeText(anchor.applicable_conditions, fields?.applicable_conditions),
      main_dimensions: normalizeText(fields?.main_dimensions),
      tolerance: normalizeText(fields?.tolerance),
      performance_indicators: normalizeText(fields?.performance_indicators),
      accessory_materials: normalizeText(fields?.accessory_materials),
      supply_form: normalizeText(fields?.supply_form),
      acceptance_documents: mergeText(anchor.acceptance_note, fields?.acceptance_documents),
      quantity: anchor.quantity > 0 ? anchor.quantity : fields?.quantity ?? 0,
      unit: mergeText(anchor.unit, fields?.unit),
      invoice_type: mergeText(anchor.invoice_type, fields?.invoice_type),
      delivery_address: mergeText(anchor.delivery_address, fields?.delivery_address),
      remarks: compactJoin([anchor.remarks, fields?.remarks]),
      pending_confirmations: fields?.pending_confirmations ?? [],
    },
    evidence: [
      ...(extracted?.evidence ?? []),
      {
        source_file_name: sourceFileName,
        source_type: "excel",
        field: "recommended_name",
        quote: anchor.material_name,
        row_index: anchor.row_index,
        confidence: 1,
      },
    ],
    warnings: extracted?.warnings ?? [],
    confidence: Math.max(extracted?.confidence ?? 0, 0.95),
  };
}

function applyExcelMaterialAnchors(
  payload: ProcurementListExtractionPayload,
  anchors: ParsedExcelMaterialAnchor[],
  sourceFileName: string,
): ProcurementListExtractionPayload {
  if (anchors.length === 0) {
    return payload;
  }
  const extractedObjects = payload.purchase_objects.filter((object) => object.object_role !== "non_procurement");
  return {
    ...payload,
    purchase_objects: anchors.map((anchor, index) => buildExcelAnchorObject(anchor, sourceFileName, extractedObjects[index])),
    warnings: payload.warnings,
  };
}

function excelFileProfile(file: UploadedProcurementListFile, excelRows: ProcurementListExtractionExcelRow[]): ProcurementListExtractionFileProfile {
  return {
    source_file_name: file.originalname,
    file_type: "excel",
    document_title: path.basename(file.originalname),
    drawing_no: "",
    page_count: 0,
    diagnostics: [`Excel/表格文件，读取 ${excelRows.length} 行用于补充数量、单位、发票和地址等字段。`],
  };
}

function imageFileProfile(file: UploadedProcurementListFile): ProcurementListExtractionFileProfile {
  return {
    source_file_name: file.originalname,
    file_type: "image",
    document_title: path.basename(file.originalname),
    drawing_no: "",
    page_count: 1,
    diagnostics: ["图片文件，直接作为多模态视觉输入。"],
  };
}

function pdfFileProfile(artifacts: PdfDiagnosticArtifacts): ProcurementListExtractionFileProfile {
  return {
    source_file_name: artifacts.profile.source_file_name,
    file_type: artifacts.profile.file_type,
    document_title: "",
    drawing_no: "",
    page_count: artifacts.profile.page_count,
    diagnostics: artifacts.profile.diagnostics,
  };
}

function assertExtractionPayload(payload: ProcurementListExtractionPayload | undefined, sourceFileName: string): asserts payload is ProcurementListExtractionPayload {
  if (!payload || !Array.isArray(payload.purchase_objects) || !payload.file_profile) {
    throw new Error(`采购清单多模态模型未返回可用结构化结果: ${sourceFileName}`);
  }
}

async function processPdfAttachment(
  file: UploadedProcurementListFile,
  buildPdfDiagnosticArtifacts: typeof defaultBuildPdfDiagnosticArtifacts,
  extractProcurementListFromMultimodalInputs: typeof defaultExtractProcurementListFromMultimodalInputs,
): Promise<ProcessedAttachment> {
  const tmpDir = await mkdtemp(path.join(os.tmpdir(), "procurement-list-export-"));
  try {
    const pdfPath = path.join(tmpDir, file.originalname);
    await writeFile(pdfPath, file.buffer);
    const artifacts = await buildPdfDiagnosticArtifacts({
      pdfPath,
      sourceFileName: file.originalname,
      fileSizeBytes: file.size,
      outputDir: tmpDir,
    });
    const fileProfile = pdfFileProfile(artifacts);
    const imageInputs: ProcurementListExtractionImageInput[] = [
      ...artifacts.renders.map((render) => ({
        source_file_name: file.originalname,
        source_type: "pdf_render" as const,
        label: `page-${render.pageNumber}-${render.renderer}`,
        dataUrl: render.dataUrl,
      })),
      {
        source_file_name: file.originalname,
        source_type: "pdf_render" as const,
        label: "trimmed-high-res",
        dataUrl: artifacts.trimmed_high_res.dataUrl,
      },
    ];
    const extraction = await extractProcurementListFromMultimodalInputs({
      fileProfiles: [fileProfile],
      imageInputs,
      excelRows: [],
    });
    assertExtractionPayload(extraction.payload, file.originalname);

    return {
      sourceFileName: file.originalname,
      payload: { ...extraction.payload, source_file_name: file.originalname },
      pdfDiagnostic: {
        source_file_name: file.originalname,
        file_type: artifacts.profile.file_type,
        drawing_no: fileProfile.drawing_no,
        diagnostics: artifacts.profile.diagnostics,
      },
      fileAudit: {
        源文件: file.originalname,
        类型: artifacts.profile.file_type,
        大小字节: file.size,
        处理方式: "PDF 诊断 + 页面渲染 + 裁剪高分图 + 多模态提取",
        模型: extraction.model,
        供应商: extraction.provider,
        提取对象数: extraction.payload.purchase_objects.length,
        诊断: artifacts.profile.diagnostics.join("；"),
      },
    };
  } finally {
    await rm(tmpDir, { recursive: true, force: true });
  }
}

async function processImageAttachment(
  file: UploadedProcurementListFile,
  extractProcurementListFromMultimodalInputs: typeof defaultExtractProcurementListFromMultimodalInputs,
): Promise<ProcessedAttachment> {
  const fileProfile = imageFileProfile(file);
  const extraction = await extractProcurementListFromMultimodalInputs({
    fileProfiles: [fileProfile],
    imageInputs: [{
      source_file_name: file.originalname,
      source_type: "image",
      label: "uploaded-image",
      dataUrl: dataUrl(file.buffer, file.mimetype || "image/png"),
    }],
    excelRows: [],
  });
  assertExtractionPayload(extraction.payload, file.originalname);

  return {
    sourceFileName: file.originalname,
    payload: { ...extraction.payload, source_file_name: file.originalname },
    fileAudit: {
      源文件: file.originalname,
      类型: "image",
      大小字节: file.size,
      处理方式: "图片多模态提取",
      模型: extraction.model,
      供应商: extraction.provider,
      提取对象数: extraction.payload.purchase_objects.length,
      诊断: fileProfile.diagnostics.join("；"),
    },
  };
}

async function processExcelAttachment(
  file: UploadedProcurementListFile,
  extractProcurementListFromMultimodalInputs: typeof defaultExtractProcurementListFromMultimodalInputs,
): Promise<ProcessedAttachment> {
  const excelRows = parseExcelRows(file.buffer, file.originalname);
  const excelAnchors = parseExcelMaterialAnchors(file.buffer);
  const fileProfile = excelFileProfile(file, excelRows);
  const extraction = await extractProcurementListFromMultimodalInputs({
    fileProfiles: [fileProfile],
    imageInputs: [],
    excelRows,
  });
  assertExtractionPayload(extraction.payload, file.originalname);
  const payload = applyExcelMaterialAnchors(extraction.payload, excelAnchors, file.originalname);

  return {
    sourceFileName: file.originalname,
    payload: { ...payload, source_file_name: file.originalname },
    fileAudit: {
      源文件: file.originalname,
      类型: "excel",
      大小字节: file.size,
      处理方式: "表格行解析 + 结构化提取",
      模型: extraction.model,
      供应商: extraction.provider,
      提取对象数: payload.purchase_objects.length,
      读取行数: excelRows.length,
      确定性采购项数: excelAnchors.length,
      诊断: fileProfile.diagnostics.join("；"),
    },
  };
}

async function processAttachment(
  file: UploadedProcurementListFile,
  deps: Required<Pick<BuildProcurementListExportWorkbookInput, "buildPdfDiagnosticArtifacts" | "extractProcurementListFromMultimodalInputs">>,
): Promise<ProcessedAttachment> {
  const kind = inferFileKind(file);
  if (!kind) {
    throw new Error(`暂不支持的清单梳理附件类型: ${file.originalname}`);
  }
  if (kind === "pdf") {
    return processPdfAttachment(file, deps.buildPdfDiagnosticArtifacts, deps.extractProcurementListFromMultimodalInputs);
  }
  if (kind === "image") {
    return processImageAttachment(file, deps.extractProcurementListFromMultimodalInputs);
  }
  return processExcelAttachment(file, deps.extractProcurementListFromMultimodalInputs);
}

function buildMainSheetRows(merged: MergeProcurementListObjectsResult): Array<Record<string, unknown>> {
  return merged.canonical_items.map((item) => {
    const fields = item.fields;
    const remarks = compactJoin([
      fields.remarks,
      fields.performance_indicators ? `性能指标：${fields.performance_indicators}` : "",
      fields.accessory_materials ? `配套材料：${fields.accessory_materials}` : "",
      item.missing_fields.length > 0 ? `待补充：${item.missing_fields.join("、")}` : "",
      item.human_fix_hint,
    ]);

    return {
      名称: item.recommended_name,
      规格型号: item.specification,
      数量: item.quantity ?? "",
      单位: item.unit,
      技术标准: fields.technical_standard ?? "",
      材质要求: fields.material_requirement ?? "",
      技术要求: fields.technical_requirement ?? "",
      适用条件: fields.applicable_conditions ?? "",
      试验测试要求: fields.test_requirement ?? fields.acceptance_documents ?? "",
      验收说明: fields.acceptance_documents ?? "",
      质保期: "",
      交货期限: "",
      发票种类: fields.invoice_type ?? "",
      品牌: "",
      备注: remarks,
      收货地址: fields.delivery_address ?? "",
    };
  });
}

function buildPurchaseCardRows(merged: MergeProcurementListObjectsResult): Array<Record<string, unknown>> {
  return merged.purchase_cards.map((card, index) => ({
    序号: index + 1,
    推荐采购品名: card.recommended_name,
    原始名称: card.original_names.join("；"),
    来源文件: card.source_files.join("；"),
    规格型号: card.fields.specification ?? "",
    数量: card.fields.quantity ?? "",
    单位: card.fields.unit ?? "",
    发票种类: card.fields.invoice_type ?? "",
    收货地址: card.fields.delivery_address ?? "",
    性能指标: card.fields.performance_indicators ?? "",
    配套材料: card.fields.accessory_materials ?? "",
    待确认: card.pending_confirmations.join("；"),
    字段来源: JSON.stringify(card.field_sources),
  }));
}

function buildPdfDiagnosticRows(processed: ProcessedAttachment[]): Array<Record<string, unknown>> {
  return processed
    .map((item) => item.pdfDiagnostic)
    .filter((item): item is ProcurementListPdfDiagnosticInput => Boolean(item))
    .map((diagnostic) => ({
      源文件: diagnostic.source_file_name,
      PDF类型: diagnostic.file_type ?? "",
      图号: diagnostic.drawing_no ?? "",
      诊断: (diagnostic.diagnostics ?? []).join("；"),
    }));
}

function buildEvidenceRows(merged: MergeProcurementListObjectsResult): Array<Record<string, unknown>> {
  return merged.field_evidence.map((evidence) => ({
    采购项序号: evidence.canonical_item_index + 1,
    字段: evidence.field_name,
    字段值: evidence.field_value,
    来源文件: evidence.source_file_name,
    证据类型: evidence.evidence_type,
    定位: JSON.stringify(evidence.locator),
    置信度: evidence.confidence ?? "",
    证据: evidence.note,
  }));
}

function buildConfirmationRows(merged: MergeProcurementListObjectsResult): Array<Record<string, unknown>> {
  return merged.confirmations.map((confirmation) => ({
    采购项序号: confirmation.canonical_item_index + 1,
    字段: confirmation.field_name,
    类型: confirmation.issue_type,
    状态: confirmation.status,
    说明: confirmation.message,
  }));
}

function appendSheet(workbook: XLSX.WorkBook, sheetName: string, rows: Array<Record<string, unknown>>, headers?: readonly string[]) {
  const worksheet = rows.length > 0
    ? XLSX.utils.json_to_sheet(rows, headers ? { header: [...headers] } : undefined)
    : XLSX.utils.aoa_to_sheet(headers ? [[...headers]] : [["说明"], ["无"]]);
  worksheet["!cols"] = (headers ?? Object.keys(rows[0] ?? { 说明: "" })).map(() => ({ wch: 22 }));
  XLSX.utils.book_append_sheet(workbook, worksheet, sheetName);
}

function sanitizeFilenamePart(value: string): string {
  return value
    .split("")
    .filter((char) => char.charCodeAt(0) >= 32)
    .join("")
    .replace(/[<>:"/\\|?*]/g, "")
    .replace(/\s+/g, "")
    .replace(/[. ]+$/g, "")
    .slice(0, 48);
}

function buildExportFilename(merged: MergeProcurementListObjectsResult): string {
  const names = compactJoin(
    merged.canonical_items.map((item) => item.recommended_name || item.original_name),
    "-",
  );
  const title = sanitizeFilenamePart(names) || "采购物料清单";
  const date = new Date().toISOString().slice(0, 10);
  return title === "采购物料清单"
    ? `采购物料清单-${date}.xlsx`
    : `${title}-采购物料清单-${date}.xlsx`;
}

export async function buildProcurementListExportWorkbook(
  input: BuildProcurementListExportWorkbookInput,
): Promise<BuildProcurementListExportWorkbookResult> {
  const files = input.files
    .filter((file) => file.buffer?.length > 0)
    .map((file) => ({
      ...file,
      originalname: normalizeUploadedFileName(file.originalname),
    }));
  if (files.length === 0) {
    throw new Error("请至少上传一个清单梳理附件");
  }

  const deps = {
    buildPdfDiagnosticArtifacts: input.buildPdfDiagnosticArtifacts ?? defaultBuildPdfDiagnosticArtifacts,
    extractProcurementListFromMultimodalInputs: input.extractProcurementListFromMultimodalInputs ?? defaultExtractProcurementListFromMultimodalInputs,
  };
  const processed = await Promise.all(files.map((file) => processAttachment(file, deps)));
  const merged = mergeProcurementListObjects({
    extractionPayloads: processed.map((item) => item.payload),
    pdfDiagnostics: processed
      .map((item) => item.pdfDiagnostic)
      .filter((item): item is ProcurementListPdfDiagnosticInput => Boolean(item)),
  });

  const workbook = XLSX.utils.book_new();
  appendSheet(workbook, "采购清单", buildMainSheetRows(merged), IMPORT_HEADERS);
  appendSheet(workbook, "采购信息卡", buildPurchaseCardRows(merged));
  appendSheet(workbook, "PDF诊断与区域", buildPdfDiagnosticRows(processed));
  appendSheet(workbook, "字段证据", buildEvidenceRows(merged));
  appendSheet(workbook, "待确认事项", buildConfirmationRows(merged));
  appendSheet(workbook, "源文件审计", processed.map((item) => item.fileAudit));

  return {
    buffer: XLSX.write(workbook, { bookType: "xlsx", type: "buffer" }) as Buffer,
    filename: buildExportFilename(merged),
    contentType: CONTENT_TYPE,
  };
}
