import { mkdir, writeFile } from "node:fs/promises";
import path from "node:path";
import * as XLSX from "xlsx";
import type { AssistantResultTable, TableExportRequest, TableExportResult } from "../../shared/types";

export async function exportTablesWorkbook(request: TableExportRequest, outputDir: string): Promise<TableExportResult> {
  const tables = request.tables.map(normalizeTable).filter((table) => table.columns.length > 0);
  if (tables.length === 0) {
    throw new Error("没有可导出的表格数据");
  }

  await mkdir(outputDir, { recursive: true });
  const createdAt = new Date();
  const fileName = `${sanitizeFileName(request.title || tables[0].title || "智能体查询结果")}_${formatTimestamp(createdAt)}.xlsx`;
  const filePath = path.join(outputDir, fileName);

  const workbook = XLSX.utils.book_new();
  let rowCount = 0;
  for (const [index, table] of tables.entries()) {
    const data = [
      table.columns.map(safeExcelText),
      ...table.rows.map((row) => table.columns.map((column) => safeExcelText(formatCell(row[column]))))
    ];
    rowCount += table.rows.length;
    const sheet = XLSX.utils.aoa_to_sheet(data);
    sheet["!cols"] = table.columns.map((column) => ({ wch: Math.min(Math.max(column.length + 8, 14), 42) }));
    if (data.length > 0) {
      sheet["!autofilter"] = { ref: XLSX.utils.encode_range({ s: { r: 0, c: 0 }, e: { r: data.length - 1, c: table.columns.length - 1 } }) };
    }
    XLSX.utils.book_append_sheet(workbook, sheet, sanitizeSheetName(table.title || `表格${index + 1}`, index));
  }

  const metaSheet = XLSX.utils.aoa_to_sheet([
    ["导出标题", request.title || tables[0].title || "智能体查询结果"],
    ["导出时间", createdAt.toLocaleString("zh-CN", { hour12: false })],
    ["表格数量", tables.length],
    ["数据行数", rowCount],
    ["说明", "本文件由市民请集合智能助手根据 MCP 工具结果生成；涉及 SQL、接口或外部页面的依据请以对话中的依据列为准。"]
  ]);
  metaSheet["!cols"] = [{ wch: 16 }, { wch: 96 }];
  XLSX.utils.book_append_sheet(workbook, metaSheet, "说明");

  const buffer = XLSX.write(workbook, { type: "buffer", bookType: "xlsx" }) as Buffer;
  await writeFile(filePath, buffer);

  return {
    filePath,
    fileName,
    tableCount: tables.length,
    rowCount,
    createdAt: createdAt.toISOString()
  };
}

function normalizeTable(table: AssistantResultTable): AssistantResultTable {
  const rows = Array.isArray(table.rows) ? table.rows : [];
  const inferredColumns = rows.length > 0 ? Array.from(new Set(rows.flatMap((row) => Object.keys(row)))) : [];
  return {
    title: String(table.title || "智能体查询结果"),
    columns: table.columns?.length ? table.columns.map(String) : inferredColumns,
    rows
  };
}

function formatCell(value: unknown): string {
  if (value === null || value === undefined) {
    return "";
  }
  if (value instanceof Date) {
    return value.toLocaleString("zh-CN", { hour12: false });
  }
  if (typeof value === "object") {
    return JSON.stringify(value);
  }
  return String(value);
}

function sanitizeFileName(value: string): string {
  const cleaned = value
    .replace(/[<>:"/\\|?*\x00-\x1F]/g, " ")
    .replace(/\s+/g, " ")
    .trim()
    .slice(0, 80);
  return cleaned || "智能体查询结果";
}

function sanitizeSheetName(value: string, index: number): string {
  const cleaned = value.replace(/[\\/?*\[\]:]/g, " ").replace(/\s+/g, " ").trim().slice(0, 25);
  return cleaned || `表格${index + 1}`;
}

function formatTimestamp(value: Date): string {
  const pad = (item: number) => String(item).padStart(2, "0");
  return [
    value.getFullYear(),
    pad(value.getMonth() + 1),
    pad(value.getDate()),
    "_",
    pad(value.getHours()),
    pad(value.getMinutes()),
    pad(value.getSeconds())
  ].join("");
}

function safeExcelText(value: string): string {
  const text = String(value ?? "");
  return /^[=+\-@]/.test(text) ? `'${text}` : text;
}
