xlsx-tools

import { tool, type Tool, type ToolsProviderController } from "@lmstudio/sdk";
import * as fs from "fs";
import * as path from "path";
import { z } from "zod";
import * as XLSX from "xlsx";

// ─── Helpers ──────────────────────────────────────────────────────────────────

function loadWorkbook(filePath: string): XLSX.WorkBook {
  const ext = path.extname(filePath).toLowerCase();
  if (ext === ".csv" || ext === ".tsv") {
    const raw = fs.readFileSync(filePath, "utf-8");
    return XLSX.read(raw, { type: "string" });
  }
  return XLSX.readFile(filePath);
}

function applyBoldHeader(ws: XLSX.WorkSheet): void {
  const range = XLSX.utils.decode_range(ws["!ref"] ?? "A1");
  for (let c = range.s.c; c <= range.e.c; c++) {
    const addr = XLSX.utils.encode_cell({ r: 0, c });
    if (!ws[addr]) continue;
    ws[addr].s = { font: { bold: true } };
  }
}

function autoWidth(ws: XLSX.WorkSheet, data: Record<string, unknown>[]): void {
  if (!data.length) return;
  const headers = Object.keys(data[0]);
  ws["!cols"] = headers.map((h) => {
    const maxLen = Math.max(h.length, ...data.map((r) => String(r[h] ?? "").length));
    return { wch: Math.min(maxLen + 2, 50) };
  });
}

function rowMatchesFilter(
  row: Record<string, unknown>,
  filters: Record<string, unknown>
): boolean {
  return Object.entries(filters).every(([key, val]) => {
    const cell = String(row[key] ?? "").toLowerCase();
    return cell === String(val).toLowerCase();
  });
}

function previewRows(rows: Record<string, unknown>[], max = 5) {
  return rows.slice(0, max);
}

// ─── Tools provider ───────────────────────────────────────────────────────────

export async function toolsProvider(_ctl: ToolsProviderController) {
  const tools: Tool[] = [];

  // ── 1. xlsx_read ──────────────────────────────────────────────────────────
  tools.push(tool({
    name: "xlsx_read",
    description: "Read rows from an Excel (.xlsx), CSV, or TSV file. Returns JSON with column names and row data. Supports row range selection.",
    parameters: {
      filePath: z.string().describe("Absolute path to the file"),
      sheet: z.string().optional().describe("Sheet name (default: first sheet)"),
      startRow: z.number().optional().describe("First row to return, 0-indexed (default: 0)"),
      endRow: z.number().optional().describe("Last row to return, exclusive (default: all)"),
      maxRows: z.number().optional().describe("Max rows to return (default: 100)"),
    },
    implementation: async ({ filePath, sheet, startRow, endRow, maxRows }) => {
      try {
        if (!fs.existsSync(filePath)) return `Error: File not found at "${filePath}"`;
        const wb = loadWorkbook(filePath);
        const sheetName = sheet ?? wb.SheetNames[0];
        if (!wb.SheetNames.includes(sheetName))
          return `Error: Sheet "${sheetName}" not found. Available: ${wb.SheetNames.join(", ")}`;
        const ws = wb.Sheets[sheetName];
        const allRows = XLSX.utils.sheet_to_json<Record<string, unknown>>(ws, { defval: null });
        const start = startRow ?? 0;
        const end = endRow ?? allRows.length;
        const sliced = allRows.slice(start, end).slice(0, maxRows ?? 100);
        return JSON.stringify({
          file: filePath, sheet: sheetName,
          total_rows: allRows.length, returned_rows: sliced.length,
          range: `rows ${start}–${Math.min(end, allRows.length) - 1}`,
          columns: sliced.length > 0 ? Object.keys(sliced[0]) : [],
          data: sliced,
        }, null, 2);
      } catch (e) { return `Error: ${String(e)}`; }
    },
  }));

  // ── 2. xlsx_write ─────────────────────────────────────────────────────────
  tools.push(tool({
    name: "xlsx_write",
    description: "Create a new Excel (.xlsx) file from an array of row objects. Supports Excel formulas in cell values (prefix with '='). Applies bold headers and auto-sized columns.",
    parameters: {
      filePath: z.string().describe("Absolute output path for the .xlsx file"),
      data: z.array(z.record(z.union([z.string(), z.number(), z.null()]))).describe("Array of row objects — keys become column headers. String values starting with '=' are treated as Excel formulas."),
      sheetName: z.string().optional().describe("Sheet name (default: Sheet1)"),
      boldHeaders: z.boolean().optional().describe("Bold headers (default: true)"),
      autoWidthCols: z.boolean().optional().describe("Auto-size columns (default: true)"),
    },
    implementation: async ({ filePath, data, sheetName, boldHeaders, autoWidthCols }) => {
      try {
        const name = sheetName ?? "Sheet1";
        const wb = XLSX.utils.book_new();
        const ws = XLSX.utils.json_to_sheet(data);
        if (boldHeaders !== false) applyBoldHeader(ws);
        if (autoWidthCols !== false) autoWidth(ws, data);
        XLSX.utils.book_append_sheet(wb, ws, name);
        const dir = path.dirname(filePath);
        if (!fs.existsSync(dir)) fs.mkdirSync(dir, { recursive: true });
        XLSX.writeFile(wb, filePath);
        return JSON.stringify({
          success: true, file: filePath, sheet: name,
          rows_written: data.length,
          columns: data.length > 0 ? Object.keys(data[0]) : [],
          preview: previewRows(data),
        });
      } catch (e) { return `Error: ${String(e)}`; }
    },
  }));

  // ── 3. xlsx_append_rows ───────────────────────────────────────────────────
  tools.push(tool({
    name: "xlsx_append_rows",
    description: "Append rows to an existing Excel sheet. Validates that row keys match existing headers before writing.",
    parameters: {
      filePath: z.string().describe("Path to existing .xlsx file"),
      rows: z.array(z.record(z.union([z.string(), z.number(), z.null()]))).describe("Rows to append"),
      sheet: z.string().optional().describe("Sheet to append to (default: first sheet)"),
    },
    implementation: async ({ filePath, rows, sheet }) => {
      try {
        if (!fs.existsSync(filePath)) return `Error: File not found at "${filePath}"`;
        const wb = XLSX.readFile(filePath);
        const sheetName = sheet ?? wb.SheetNames[0];
        const ws = wb.Sheets[sheetName];
        const existing = XLSX.utils.sheet_to_json<Record<string, unknown>>(ws);

        // Validate column names
        if (existing.length > 0 && rows.length > 0) {
          const existingHeaders = new Set(Object.keys(existing[0]));
          const newHeaders = Object.keys(rows[0]);
          const unknown = newHeaders.filter((h) => !existingHeaders.has(h));
          if (unknown.length > 0)
            return `Error: Unknown columns: ${unknown.join(", ")}. Expected: ${[...existingHeaders].join(", ")}`;
        }

        const updated = [...existing, ...rows];
        const newWs = XLSX.utils.json_to_sheet(updated);
        autoWidth(newWs, updated);
        wb.Sheets[sheetName] = newWs;
        XLSX.writeFile(wb, filePath);
        return JSON.stringify({
          success: true, file: filePath, sheet: sheetName,
          rows_before: existing.length, rows_appended: rows.length, total_rows: updated.length,
          preview_appended: previewRows(rows),
        });
      } catch (e) { return `Error: ${String(e)}`; }
    },
  }));

  // ── 4. xlsx_list_sheets ───────────────────────────────────────────────────
  tools.push(tool({
    name: "xlsx_list_sheets",
    description: "List all sheet names in an Excel workbook along with row counts and cell ranges.",
    parameters: {
      filePath: z.string().describe("Path to an .xlsx file"),
    },
    implementation: async ({ filePath }) => {
      try {
        if (!fs.existsSync(filePath)) return `Error: File not found at "${filePath}"`;
        const wb = loadWorkbook(filePath);
        const info = wb.SheetNames.map((name) => {
          const ws = wb.Sheets[name];
          return { name, rows: XLSX.utils.sheet_to_json(ws).length, range: ws["!ref"] ?? "empty" };
        });
        return JSON.stringify({ file: filePath, sheets: info }, null, 2);
      } catch (e) { return `Error: ${String(e)}`; }
    },
  }));

  // ── 5. xlsx_summarize ─────────────────────────────────────────────────────
  tools.push(tool({
    name: "xlsx_summarize",
    description: "Statistical summary per column: min, max, avg, sum, unique count, null count, duplicate rows, empty columns, outlier detection.",
    parameters: {
      filePath: z.string().describe("Path to the .xlsx / .csv file"),
      sheet: z.string().optional().describe("Sheet name (default: first sheet)"),
    },
    implementation: async ({ filePath, sheet }) => {
      try {
        if (!fs.existsSync(filePath)) return `Error: File not found at "${filePath}"`;
        const wb = loadWorkbook(filePath);
        const sheetName = sheet ?? wb.SheetNames[0];
        const ws = wb.Sheets[sheetName];
        const rows = XLSX.utils.sheet_to_json<Record<string, unknown>>(ws, { defval: null });
        if (!rows.length) return JSON.stringify({ file: filePath, empty: true });

        // Duplicate detection
        const rowStrings = rows.map((r) => JSON.stringify(r));
        const duplicateCount = rowStrings.length - new Set(rowStrings).size;

        const headers = Object.keys(rows[0]);
        const stats: Record<string, unknown> = {};
        const emptyColumns: string[] = [];

        for (const col of headers) {
          const values = rows.map((r) => r[col]).filter((v) => v !== null && v !== "");
          if (values.length === 0) { emptyColumns.push(col); continue; }
          const nums = values.filter((v) => typeof v === "number") as number[];
          if (nums.length > 0) {
            const sum = nums.reduce((a, b) => a + b, 0);
            const avg = sum / nums.length;
            const stddev = Math.sqrt(nums.reduce((a, b) => a + Math.pow(b - avg, 2), 0) / nums.length);
            const outliers = nums.filter((n) => Math.abs(n - avg) > 3 * stddev);
            stats[col] = {
              type: "numeric", count: nums.length, nulls: rows.length - nums.length,
              min: Math.min(...nums), max: Math.max(...nums),
              avg: +avg.toFixed(4), sum: +sum.toFixed(4), stddev: +stddev.toFixed(4),
              outliers: outliers.length > 0 ? outliers.slice(0, 5) : "none",
            };
          } else {
            const unique = new Set(values.map(String));
            stats[col] = {
              type: "text", count: values.length, nulls: rows.length - values.length,
              unique_values: unique.size, sample: [...unique].slice(0, 5),
            };
          }
        }
        return JSON.stringify({
          file: filePath, sheet: sheetName,
          total_rows: rows.length, total_columns: headers.length,
          duplicate_rows: duplicateCount,
          empty_columns: emptyColumns.length > 0 ? emptyColumns : "none",
          column_stats: stats,
        }, null, 2);
      } catch (e) { return `Error: ${String(e)}`; }
    },
  }));

  // ── 6. xlsx_filter ────────────────────────────────────────────────────────
  tools.push(tool({
    name: "xlsx_filter",
    description: "Return only rows matching given column conditions (case-insensitive equality). Like a SQL WHERE clause.",
    parameters: {
      filePath: z.string().describe("Path to the .xlsx / .csv file"),
      filters: z.record(z.union([z.string(), z.number()])).describe("Column/value pairs to filter on, e.g. { \"Ville\": \"Paris\", \"Statut\": \"actif\" }"),
      sheet: z.string().optional().describe("Sheet name (default: first sheet)"),
    },
    implementation: async ({ filePath, filters, sheet }) => {
      try {
        if (!fs.existsSync(filePath)) return `Error: File not found at "${filePath}"`;
        const wb = loadWorkbook(filePath);
        const sheetName = sheet ?? wb.SheetNames[0];
        const ws = wb.Sheets[sheetName];
        const rows = XLSX.utils.sheet_to_json<Record<string, unknown>>(ws, { defval: null });
        const matched = rows.filter((r) => rowMatchesFilter(r, filters));
        return JSON.stringify({
          file: filePath, sheet: sheetName,
          filters, total_rows: rows.length, matched_rows: matched.length,
          data: matched,
        }, null, 2);
      } catch (e) { return `Error: ${String(e)}`; }
    },
  }));

  // ── 7. xlsx_sort ──────────────────────────────────────────────────────────
  tools.push(tool({
    name: "xlsx_sort",
    description: "Sort a sheet by one or more columns and save the result.",
    parameters: {
      filePath: z.string().describe("Path to the .xlsx file"),
      sortBy: z.array(z.object({
        column: z.string().describe("Column name to sort by"),
        direction: z.enum(["asc", "desc"]).optional().describe("Sort direction (default: asc)"),
      })).describe("Sort keys in priority order"),
      sheet: z.string().optional().describe("Sheet name (default: first sheet)"),
    },
    implementation: async ({ filePath, sortBy, sheet }) => {
      try {
        if (!fs.existsSync(filePath)) return `Error: File not found at "${filePath}"`;
        const wb = XLSX.readFile(filePath);
        const sheetName = sheet ?? wb.SheetNames[0];
        const ws = wb.Sheets[sheetName];
        const rows = XLSX.utils.sheet_to_json<Record<string, unknown>>(ws, { defval: null });
        const sorted = [...rows].sort((a, b) => {
          for (const { column, direction } of sortBy) {
            const dir = direction === "desc" ? -1 : 1;
            const av = a[column]; const bv = b[column];
            if (av === bv) continue;
            if (typeof av === "number" && typeof bv === "number") return (av - bv) * dir;
            return String(av ?? "").localeCompare(String(bv ?? "")) * dir;
          }
          return 0;
        });
        const newWs = XLSX.utils.json_to_sheet(sorted);
        autoWidth(newWs, sorted);
        wb.Sheets[sheetName] = newWs;
        XLSX.writeFile(wb, filePath);
        return JSON.stringify({
          success: true, file: filePath, sheet: sheetName,
          sorted_by: sortBy, total_rows: sorted.length,
          preview: previewRows(sorted),
        });
      } catch (e) { return `Error: ${String(e)}`; }
    },
  }));

  // ── 8. xlsx_delete_rows ───────────────────────────────────────────────────
  tools.push(tool({
    name: "xlsx_delete_rows",
    description: "Delete rows matching given column conditions. Returns count of deleted rows and a preview of what remains.",
    parameters: {
      filePath: z.string().describe("Path to the .xlsx file"),
      filters: z.record(z.union([z.string(), z.number()])).describe("Column/value pairs — rows matching ALL conditions will be deleted"),
      sheet: z.string().optional().describe("Sheet name (default: first sheet)"),
    },
    implementation: async ({ filePath, filters, sheet }) => {
      try {
        if (!fs.existsSync(filePath)) return `Error: File not found at "${filePath}"`;
        const wb = XLSX.readFile(filePath);
        const sheetName = sheet ?? wb.SheetNames[0];
        const ws = wb.Sheets[sheetName];
        const rows = XLSX.utils.sheet_to_json<Record<string, unknown>>(ws, { defval: null });
        const kept = rows.filter((r) => !rowMatchesFilter(r, filters));
        const deleted = rows.length - kept.length;
        const newWs = XLSX.utils.json_to_sheet(kept);
        autoWidth(newWs, kept);
        wb.Sheets[sheetName] = newWs;
        XLSX.writeFile(wb, filePath);
        return JSON.stringify({
          success: true, file: filePath, sheet: sheetName,
          filters, rows_before: rows.length, rows_deleted: deleted, rows_after: kept.length,
          preview_remaining: previewRows(kept),
        });
      } catch (e) { return `Error: ${String(e)}`; }
    },
  }));

  // ── 9. xlsx_update_rows ───────────────────────────────────────────────────
  tools.push(tool({
    name: "xlsx_update_rows",
    description: "Update cell values in rows matching given filter conditions. Like a SQL UPDATE ... WHERE.",
    parameters: {
      filePath: z.string().describe("Path to the .xlsx file"),
      filters: z.record(z.union([z.string(), z.number()])).describe("Conditions to identify rows to update"),
      updates: z.record(z.union([z.string(), z.number(), z.null()])).describe("Column/value pairs to set on matched rows"),
      sheet: z.string().optional().describe("Sheet name (default: first sheet)"),
    },
    implementation: async ({ filePath, filters, updates, sheet }) => {
      try {
        if (!fs.existsSync(filePath)) return `Error: File not found at "${filePath}"`;
        const wb = XLSX.readFile(filePath);
        const sheetName = sheet ?? wb.SheetNames[0];
        const ws = wb.Sheets[sheetName];
        const rows = XLSX.utils.sheet_to_json<Record<string, unknown>>(ws, { defval: null });
        let updatedCount = 0;
        const newRows = rows.map((r) => {
          if (!rowMatchesFilter(r, filters)) return r;
          updatedCount++;
          return { ...r, ...updates };
        });
        const newWs = XLSX.utils.json_to_sheet(newRows);
        autoWidth(newWs, newRows);
        wb.Sheets[sheetName] = newWs;
        XLSX.writeFile(wb, filePath);
        return JSON.stringify({
          success: true, file: filePath, sheet: sheetName,
          filters, updates, rows_updated: updatedCount,
          preview: previewRows(newRows.filter((r) => rowMatchesFilter(r, updates as Record<string, unknown>))),
        });
      } catch (e) { return `Error: ${String(e)}`; }
    },
  }));

  // ── 10. xlsx_merge_files ──────────────────────────────────────────────────
  tools.push(tool({
    name: "xlsx_merge_files",
    description: "Merge multiple Excel/CSV files into a single workbook. Each source file becomes a sheet.",
    parameters: {
      inputFiles: z.array(z.string()).describe("List of absolute paths to .xlsx or .csv files to merge"),
      outputFile: z.string().describe("Absolute path for the output .xlsx file"),
    },
    implementation: async ({ inputFiles, outputFile }) => {
      try {
        const missing = inputFiles.filter((f) => !fs.existsSync(f));
        if (missing.length > 0) return `Error: Files not found: ${missing.join(", ")}`;
        const outWb = XLSX.utils.book_new();
        const summary: { file: string; sheet: string; rows: number }[] = [];
        for (const filePath of inputFiles) {
          const wb = loadWorkbook(filePath);
          for (const sheetName of wb.SheetNames) {
            const ws = wb.Sheets[sheetName];
            const rows = XLSX.utils.sheet_to_json(ws);
            // Avoid sheet name collisions
            let name = path.basename(filePath, path.extname(filePath));
            if (wb.SheetNames.length > 1) name = `${name}_${sheetName}`;
            name = name.slice(0, 31); // Excel sheet name limit
            let safeName = name; let i = 2;
            while (outWb.SheetNames.includes(safeName)) safeName = `${name}_${i++}`;
            XLSX.utils.book_append_sheet(outWb, ws, safeName);
            summary.push({ file: filePath, sheet: safeName, rows: rows.length });
          }
        }
        const dir = path.dirname(outputFile);
        if (!fs.existsSync(dir)) fs.mkdirSync(dir, { recursive: true });
        XLSX.writeFile(outWb, outputFile);
        return JSON.stringify({ success: true, output: outputFile, sheets_created: summary });
      } catch (e) { return `Error: ${String(e)}`; }
    },
  }));

  // ── 11. xlsx_pivot ────────────────────────────────────────────────────────
  tools.push(tool({
    name: "xlsx_pivot",
    description: "Create a pivot table: group rows by a column and aggregate a numeric column (sum, avg, count, min, max). Saves result to a new sheet or file.",
    parameters: {
      filePath: z.string().describe("Path to the source .xlsx / .csv file"),
      groupBy: z.string().describe("Column to group by"),
      valueColumn: z.string().describe("Numeric column to aggregate"),
      aggregation: z.enum(["sum", "avg", "count", "min", "max"]).describe("Aggregation function"),
      outputFile: z.string().optional().describe("Output .xlsx path (default: overwrites source)"),
      outputSheet: z.string().optional().describe("Sheet name for pivot result (default: Pivot)"),
      sheet: z.string().optional().describe("Source sheet name (default: first sheet)"),
    },
    implementation: async ({ filePath, groupBy, valueColumn, aggregation, outputFile, outputSheet, sheet }) => {
      try {
        if (!fs.existsSync(filePath)) return `Error: File not found at "${filePath}"`;
        const wb = loadWorkbook(filePath);
        const sheetName = sheet ?? wb.SheetNames[0];
        const ws = wb.Sheets[sheetName];
        const rows = XLSX.utils.sheet_to_json<Record<string, unknown>>(ws, { defval: null });

        // Group
        const groups = new Map<string, number[]>();
        for (const row of rows) {
          const key = String(row[groupBy] ?? "(blank)");
          const val = row[valueColumn];
          if (!groups.has(key)) groups.set(key, []);
          if (typeof val === "number") groups.get(key)!.push(val);
        }

        // Aggregate
        const pivotRows = [...groups.entries()].map(([key, vals]) => {
          let result: number;
          switch (aggregation) {
            case "sum":   result = vals.reduce((a, b) => a + b, 0); break;
            case "avg":   result = vals.length ? vals.reduce((a, b) => a + b, 0) / vals.length : 0; break;
            case "count": result = vals.length; break;
            case "min":   result = Math.min(...vals); break;
            case "max":   result = Math.max(...vals); break;
          }
          return { [groupBy]: key, [`${aggregation}(${valueColumn})`]: +result.toFixed(4), count: vals.length };
        }).sort((a, b) => (b[`${aggregation}(${valueColumn})`] as number) - (a[`${aggregation}(${valueColumn})`] as number));

        // Write
        const outPath = outputFile ?? filePath;
        const outWb = fs.existsSync(outPath) && outPath === filePath ? XLSX.readFile(outPath) : XLSX.utils.book_new();
        const pivotWs = XLSX.utils.json_to_sheet(pivotRows);
        applyBoldHeader(pivotWs);
        autoWidth(pivotWs, pivotRows);
        const pivotSheetName = outputSheet ?? "Pivot";
        if (outWb.SheetNames.includes(pivotSheetName)) {
          outWb.Sheets[pivotSheetName] = pivotWs;
        } else {
          XLSX.utils.book_append_sheet(outWb, pivotWs, pivotSheetName);
        }
        XLSX.writeFile(outWb, outPath);
        return JSON.stringify({
          success: true, file: outPath, sheet: pivotSheetName,
          group_by: groupBy, value_column: valueColumn, aggregation,
          groups: groups.size, preview: previewRows(pivotRows, 10),
        }, null, 2);
      } catch (e) { return `Error: ${String(e)}`; }
    },
  }));

  // ── 12. xlsx_diff ─────────────────────────────────────────────────────────
  tools.push(tool({
    name: "xlsx_diff",
    description: "Compare two Excel/CSV files and return added rows, removed rows, and modified rows.",
    parameters: {
      fileA: z.string().describe("Path to the original file"),
      fileB: z.string().describe("Path to the new/modified file"),
      keyColumn: z.string().describe("Column that uniquely identifies each row (like a primary key)"),
      sheetA: z.string().optional().describe("Sheet name in fileA (default: first sheet)"),
      sheetB: z.string().optional().describe("Sheet name in fileB (default: first sheet)"),
    },
    implementation: async ({ fileA, fileB, keyColumn, sheetA, sheetB }) => {
      try {
        if (!fs.existsSync(fileA)) return `Error: File not found: "${fileA}"`;
        if (!fs.existsSync(fileB)) return `Error: File not found: "${fileB}"`;

        const wbA = loadWorkbook(fileA);
        const wbB = loadWorkbook(fileB);
        const wsA = wbA.Sheets[sheetA ?? wbA.SheetNames[0]];
        const wsB = wbB.Sheets[sheetB ?? wbB.SheetNames[0]];
        const rowsA = XLSX.utils.sheet_to_json<Record<string, unknown>>(wsA, { defval: null });
        const rowsB = XLSX.utils.sheet_to_json<Record<string, unknown>>(wsB, { defval: null });

        const mapA = new Map(rowsA.map((r) => [String(r[keyColumn]), r]));
        const mapB = new Map(rowsB.map((r) => [String(r[keyColumn]), r]));

        const added = rowsB.filter((r) => !mapA.has(String(r[keyColumn])));
        const removed = rowsA.filter((r) => !mapB.has(String(r[keyColumn])));
        const modified: { key: string; changes: Record<string, { from: unknown; to: unknown }> }[] = [];

        for (const [key, rowB] of mapB.entries()) {
          const rowA = mapA.get(key);
          if (!rowA) continue;
          const changes: Record<string, { from: unknown; to: unknown }> = {};
          for (const col of new Set([...Object.keys(rowA), ...Object.keys(rowB)])) {
            if (String(rowA[col] ?? "") !== String(rowB[col] ?? ""))
              changes[col] = { from: rowA[col], to: rowB[col] };
          }
          if (Object.keys(changes).length > 0) modified.push({ key, changes });
        }

        return JSON.stringify({
          fileA, fileB, key_column: keyColumn,
          summary: { added: added.length, removed: removed.length, modified: modified.length },
          added: added.slice(0, 20),
          removed: removed.slice(0, 20),
          modified: modified.slice(0, 20),
        }, null, 2);
      } catch (e) { return `Error: ${String(e)}`; }
    },
  }));

  // ── 13. xlsx_create_sheet ────────────────────────────────────────────────
  tools.push(tool({
    name: "xlsx_create_sheet",
    description: "Add a new empty sheet to an existing workbook. Optionally populate it with initial data.",
    parameters: {
      filePath: z.string().describe("Path to the .xlsx file"),
      sheetName: z.string().describe("Name of the new sheet"),
      data: z.array(z.record(z.union([z.string(), z.number(), z.null()]))).optional().describe("Optional initial row data"),
      position: z.number().optional().describe("Position index (0 = first). Default: append at end"),
    },
    implementation: async ({ filePath, sheetName, data, position }) => {
      try {
        if (!fs.existsSync(filePath)) return `Error: File not found at "${filePath}"`;
        const wb = XLSX.readFile(filePath);
        if (wb.SheetNames.includes(sheetName))
          return `Error: Sheet "${sheetName}" already exists`;
        const rows = data ?? [];
        const ws = XLSX.utils.json_to_sheet(rows);
        if (rows.length > 0) { applyBoldHeader(ws); autoWidth(ws, rows); }
        XLSX.utils.book_append_sheet(wb, ws, sheetName);
        // Reorder if position specified
        if (position !== undefined) {
          const names = wb.SheetNames.filter((n) => n !== sheetName);
          names.splice(position, 0, sheetName);
          wb.SheetNames = names;
        }
        XLSX.writeFile(wb, filePath);
        return JSON.stringify({
          success: true, file: filePath, sheet_created: sheetName,
          position: wb.SheetNames.indexOf(sheetName),
          all_sheets: wb.SheetNames,
          rows_written: rows.length,
        });
      } catch (e) { return `Error: ${String(e)}`; }
    },
  }));

  // ── 14. xlsx_delete_sheet ────────────────────────────────────────────────
  tools.push(tool({
    name: "xlsx_delete_sheet",
    description: "Delete a sheet from a workbook by name.",
    parameters: {
      filePath: z.string().describe("Path to the .xlsx file"),
      sheetName: z.string().describe("Name of the sheet to delete"),
    },
    implementation: async ({ filePath, sheetName }) => {
      try {
        if (!fs.existsSync(filePath)) return `Error: File not found at "${filePath}"`;
        const wb = XLSX.readFile(filePath);
        if (!wb.SheetNames.includes(sheetName))
          return `Error: Sheet "${sheetName}" not found. Available: ${wb.SheetNames.join(", ")}`;
        if (wb.SheetNames.length === 1)
          return `Error: Cannot delete the only sheet in a workbook`;
        wb.SheetNames = wb.SheetNames.filter((n) => n !== sheetName);
        delete wb.Sheets[sheetName];
        XLSX.writeFile(wb, filePath);
        return JSON.stringify({
          success: true, file: filePath, sheet_deleted: sheetName,
          remaining_sheets: wb.SheetNames,
        });
      } catch (e) { return `Error: ${String(e)}`; }
    },
  }));

  // ── 15. xlsx_rename_sheet ────────────────────────────────────────────────
  tools.push(tool({
    name: "xlsx_rename_sheet",
    description: "Rename a sheet in a workbook.",
    parameters: {
      filePath: z.string().describe("Path to the .xlsx file"),
      oldName: z.string().describe("Current sheet name"),
      newName: z.string().describe("New sheet name"),
    },
    implementation: async ({ filePath, oldName, newName }) => {
      try {
        if (!fs.existsSync(filePath)) return `Error: File not found at "${filePath}"`;
        const wb = XLSX.readFile(filePath);
        if (!wb.SheetNames.includes(oldName))
          return `Error: Sheet "${oldName}" not found. Available: ${wb.SheetNames.join(", ")}`;
        if (wb.SheetNames.includes(newName))
          return `Error: Sheet "${newName}" already exists`;
        if (newName.length > 31)
          return `Error: Sheet name must be 31 characters or fewer`;
        const idx = wb.SheetNames.indexOf(oldName);
        wb.SheetNames[idx] = newName;
        wb.Sheets[newName] = wb.Sheets[oldName];
        delete wb.Sheets[oldName];
        XLSX.writeFile(wb, filePath);
        return JSON.stringify({
          success: true, file: filePath,
          renamed: { from: oldName, to: newName },
          all_sheets: wb.SheetNames,
        });
      } catch (e) { return `Error: ${String(e)}`; }
    },
  }));

  // ── 16. xlsx_copy_sheet ───────────────────────────────────────────────────
  tools.push(tool({
    name: "xlsx_copy_sheet",
    description: "Duplicate a sheet within the same workbook or copy it to another workbook.",
    parameters: {
      filePath: z.string().describe("Path to the source .xlsx file"),
      sheetName: z.string().describe("Name of the sheet to copy"),
      newSheetName: z.string().describe("Name for the copy"),
      targetFile: z.string().optional().describe("Target .xlsx file path (default: same file)"),
    },
    implementation: async ({ filePath, sheetName, newSheetName, targetFile }) => {
      try {
        if (!fs.existsSync(filePath)) return `Error: File not found at "${filePath}"`;
        const srcWb = XLSX.readFile(filePath);
        if (!srcWb.SheetNames.includes(sheetName))
          return `Error: Sheet "${sheetName}" not found. Available: ${srcWb.SheetNames.join(", ")}`;

        const destPath = targetFile ?? filePath;
        const destWb = targetFile && fs.existsSync(targetFile)
          ? XLSX.readFile(targetFile)
          : targetFile
            ? XLSX.utils.book_new()
            : srcWb;

        if (destWb.SheetNames.includes(newSheetName))
          return `Error: Sheet "${newSheetName}" already exists in destination`;

        // Deep-copy the worksheet via JSON round-trip to avoid reference sharing
        const wsJson = JSON.stringify(srcWb.Sheets[sheetName]);
        const wsCopy: XLSX.WorkSheet = JSON.parse(wsJson);
        XLSX.utils.book_append_sheet(destWb, wsCopy, newSheetName);

        XLSX.writeFile(destWb, destPath);
        return JSON.stringify({
          success: true,
          source: { file: filePath, sheet: sheetName },
          destination: { file: destPath, sheet: newSheetName },
          all_sheets: destWb.SheetNames,
        });
      } catch (e) { return `Error: ${String(e)}`; }
    },
  }));

  // ── 17. xlsx_move_sheet ───────────────────────────────────────────────────
  tools.push(tool({
    name: "xlsx_move_sheet",
    description: "Change the position (tab order) of a sheet in a workbook.",
    parameters: {
      filePath: z.string().describe("Path to the .xlsx file"),
      sheetName: z.string().describe("Name of the sheet to move"),
      position: z.number().describe("Target position index (0 = first tab)"),
    },
    implementation: async ({ filePath, sheetName, position }) => {
      try {
        if (!fs.existsSync(filePath)) return `Error: File not found at "${filePath}"`;
        const wb = XLSX.readFile(filePath);
        if (!wb.SheetNames.includes(sheetName))
          return `Error: Sheet "${sheetName}" not found. Available: ${wb.SheetNames.join(", ")}`;
        const clampedPos = Math.max(0, Math.min(position, wb.SheetNames.length - 1));
        const names = wb.SheetNames.filter((n) => n !== sheetName);
        names.splice(clampedPos, 0, sheetName);
        wb.SheetNames = names;
        XLSX.writeFile(wb, filePath);
        return JSON.stringify({
          success: true, file: filePath,
          sheet: sheetName, new_position: clampedPos,
          all_sheets: wb.SheetNames,
        });
      } catch (e) { return `Error: ${String(e)}`; }
    },
  }));

  // ── 18. xlsx_copy_rows_between_sheets ────────────────────────────────────
  tools.push(tool({
    name: "xlsx_copy_rows_between_sheets",
    description: "Copy or move rows from one sheet to another within the same workbook. Can filter which rows to copy.",
    parameters: {
      filePath: z.string().describe("Path to the .xlsx file"),
      sourceSheet: z.string().describe("Sheet to copy rows from"),
      targetSheet: z.string().describe("Sheet to copy rows to (must already exist)"),
      filters: z.record(z.union([z.string(), z.number()])).optional().describe("Only copy rows matching these conditions. Omit to copy all rows."),
      move: z.boolean().optional().describe("If true, delete copied rows from source (default: false = copy only)"),
    },
    implementation: async ({ filePath, sourceSheet, targetSheet, filters, move }) => {
      try {
        if (!fs.existsSync(filePath)) return `Error: File not found at "${filePath}"`;
        const wb = XLSX.readFile(filePath);
        if (!wb.SheetNames.includes(sourceSheet))
          return `Error: Source sheet "${sourceSheet}" not found`;
        if (!wb.SheetNames.includes(targetSheet))
          return `Error: Target sheet "${targetSheet}" not found. Create it first with xlsx_create_sheet`;

        const srcWs = wb.Sheets[sourceSheet];
        const srcRows = XLSX.utils.sheet_to_json<Record<string, unknown>>(srcWs, { defval: null });
        const rowsToCopy = filters ? srcRows.filter((r) => rowMatchesFilter(r, filters)) : srcRows;

        if (rowsToCopy.length === 0)
          return JSON.stringify({ success: true, rows_copied: 0, message: "No rows matched the filter" });

        // Append to target
        const tgtWs = wb.Sheets[targetSheet];
        const tgtRows = XLSX.utils.sheet_to_json<Record<string, unknown>>(tgtWs, { defval: null });
        const mergedRows = [...tgtRows, ...rowsToCopy];
        const newTgtWs = XLSX.utils.json_to_sheet(mergedRows);
        autoWidth(newTgtWs, mergedRows);
        wb.Sheets[targetSheet] = newTgtWs;

        // Optionally remove from source
        if (move) {
          const remaining = filters ? srcRows.filter((r) => !rowMatchesFilter(r, filters)) : [];
          const newSrcWs = XLSX.utils.json_to_sheet(remaining);
          autoWidth(newSrcWs, remaining);
          wb.Sheets[sourceSheet] = newSrcWs;
        }

        XLSX.writeFile(wb, filePath);
        return JSON.stringify({
          success: true, file: filePath,
          operation: move ? "move" : "copy",
          source: { sheet: sourceSheet, rows_before: srcRows.length, rows_after: move ? srcRows.length - rowsToCopy.length : srcRows.length },
          target: { sheet: targetSheet, rows_before: tgtRows.length, rows_after: mergedRows.length },
          rows_transferred: rowsToCopy.length,
          preview: previewRows(rowsToCopy),
        });
      } catch (e) { return `Error: ${String(e)}`; }
    },
  }));

  return tools;
}