excel-tools

import { tool, type Tool } from "@lmstudio/sdk";
import { z } from "zod";
import ExcelJS from "exceljs";
import { loadWorkbook, saveWorkbook, getSheet, worksheetToJson, jsonToWorksheet, matchesFilters, preview, type FilterCondition } from "../utils/workbook";
import { checkReadable, backupFile, resolvePath } from "../utils/config";

export function getSheetTools(workingDir: string): Tool[] {
  return [

    // ── 13. excel_list_sheets ────────────────────────────────────────────────
    tool({
      name: "excel_list_sheets",
      description: "List all sheets in a workbook with row counts, column counts, and cell ranges.",
      parameters: { filePath: z.string() },
      implementation: async ({ filePath }) => {
        filePath = resolvePath(filePath, workingDir);
          const err = checkReadable(filePath);
          if (err) return `Error: ${err}`;
        try {
          const wb = await loadWorkbook(filePath);
          const info = wb.worksheets.map(ws => {
            const rows = worksheetToJson(ws);
            return { name: ws.name, rows: rows.length,
              columns: rows[0] ? Object.keys(rows[0]).length : 0,
              column_names: rows[0] ? Object.keys(rows[0]) : [],
            };
          });
          return JSON.stringify({ file: filePath, sheet_count: info.length, sheets: info }, null, 2);
        } catch (e) { return `Error: ${e}`; }
      },
    }),

    // ── 14. excel_create_sheet ───────────────────────────────────────────────
    tool({
      name: "excel_create_sheet",
      description: "Add a new sheet to an existing workbook. Optionally populate with initial data and set its tab position.",
      parameters: {
        filePath: z.string(),
        sheetName: z.string(),
        data: z.array(z.record(z.union([z.string(), z.number(), z.null()]))).optional(),
        position: z.number().optional().describe("Tab position index, 0-based (default: append at end)"),
      },
      implementation: async ({ filePath, sheetName, data, position }) => {
        filePath = resolvePath(filePath, workingDir);
          const err = checkReadable(filePath);
          if (err) return `Error: ${err}`;
        try {
          const wb = await loadWorkbook(filePath);
          if (wb.getWorksheet(sheetName)) return `Error: Sheet "${sheetName}" already exists`;
          const ws = wb.addWorksheet(sheetName);
          if (data?.length) jsonToWorksheet(ws, data);
          // Reorder if position given
          if (position !== undefined) {
            const names = wb.worksheets.map(s => s.name).filter(n => n !== sheetName);
            names.splice(Math.max(0, position), 0, sheetName);
            (wb as any)._worksheets = names.map(n => wb.getWorksheet(n));
          }
          await saveWorkbook(wb, filePath);
          return JSON.stringify({ success: true, file: filePath, sheet_created: sheetName,
            position: wb.worksheets.findIndex(s => s.name === sheetName),
            rows_written: data?.length ?? 0, all_sheets: wb.worksheets.map(s => s.name) });
        } catch (e) { return `Error: ${e}`; }
      },
    }),

    // ── 15. excel_delete_sheet ───────────────────────────────────────────────
    tool({
      name: "excel_delete_sheet",
      description: "Delete a sheet from a workbook by name. Cannot delete the last remaining sheet.",
      parameters: {
        filePath: z.string(),
        sheetName: z.string(),
      },
      implementation: async ({ filePath, sheetName }) => {
        filePath = resolvePath(filePath, workingDir);
          const err = checkReadable(filePath);
          if (err) return `Error: ${err}`;
        try {
          backupFile(filePath);
          const wb = await loadWorkbook(filePath);
          if (!wb.getWorksheet(sheetName))
            return `Error: Sheet "${sheetName}" not found. Available: ${wb.worksheets.map(s => s.name).join(", ")}`;
          if (wb.worksheets.length === 1) return "Error: Cannot delete the only sheet in a workbook";
          wb.removeWorksheet(wb.getWorksheet(sheetName)!.id);
          await saveWorkbook(wb, filePath);
          return JSON.stringify({ success: true, file: filePath, sheet_deleted: sheetName,
            remaining_sheets: wb.worksheets.map(s => s.name) });
        } catch (e) { return `Error: ${e}`; }
      },
    }),

    // ── 16. excel_rename_sheet ───────────────────────────────────────────────
    tool({
      name: "excel_rename_sheet",
      description: "Rename a sheet. Sheet names must be ≤31 characters and unique within the workbook.",
      parameters: {
        filePath: z.string(),
        oldName: z.string(),
        newName: z.string(),
      },
      implementation: async ({ filePath, oldName, newName }) => {
        filePath = resolvePath(filePath, workingDir);
          const err = checkReadable(filePath);
          if (err) return `Error: ${err}`;
        if (newName.length > 31) return "Error: Sheet name must be ≤31 characters";
        try {
          const wb = await loadWorkbook(filePath);
          const ws = wb.getWorksheet(oldName);
          if (!ws) return `Error: Sheet "${oldName}" not found. Available: ${wb.worksheets.map(s => s.name).join(", ")}`;
          if (wb.getWorksheet(newName)) return `Error: Sheet "${newName}" already exists`;
          ws.name = newName;
          await saveWorkbook(wb, filePath);
          return JSON.stringify({ success: true, file: filePath,
            renamed: { from: oldName, to: newName }, all_sheets: wb.worksheets.map(s => s.name) });
        } catch (e) { return `Error: ${e}`; }
      },
    }),

    // ── 17. excel_copy_sheet ─────────────────────────────────────────────────
    tool({
      name: "excel_copy_sheet",
      description: "Duplicate a sheet within the same workbook or copy it to another file.",
      parameters: {
        filePath: z.string(),
        sheetName: z.string(),
        newSheetName: z.string(),
        targetFile: z.string().optional().describe("Target .xlsx path (default: same file)"),
      },
      implementation: async ({ filePath, sheetName, newSheetName, targetFile }) => {
        filePath = resolvePath(filePath, workingDir);
          const err = checkReadable(filePath);
          if (err) return `Error: ${err}`;
        try {
          const srcWb = await loadWorkbook(filePath);
          const srcWs = srcWb.getWorksheet(sheetName);
          if (!srcWs) return `Error: Sheet "${sheetName}" not found`;
          const rows = worksheetToJson(srcWs);

          const destPath = targetFile ?? filePath;
          const destWb = targetFile ? new ExcelJS.Workbook() : srcWb;
          if (destWb.getWorksheet(newSheetName)) return `Error: Sheet "${newSheetName}" already exists in destination`;
          const newWs = destWb.addWorksheet(newSheetName);
          jsonToWorksheet(newWs, rows);
          await saveWorkbook(destWb, destPath);
          return JSON.stringify({ success: true,
            source: { file: filePath, sheet: sheetName },
            destination: { file: destPath, sheet: newSheetName },
            rows_copied: rows.length, all_sheets: destWb.worksheets.map(s => s.name) });
        } catch (e) { return `Error: ${e}`; }
      },
    }),

    // ── 18. excel_move_sheet ─────────────────────────────────────────────────
    tool({
      name: "excel_move_sheet",
      description: "Change the tab position of a sheet (reorder tabs).",
      parameters: {
        filePath: z.string(),
        sheetName: z.string(),
        position: z.number().describe("Target position, 0-based"),
      },
      implementation: async ({ filePath, sheetName, position }) => {
        filePath = resolvePath(filePath, workingDir);
          const err = checkReadable(filePath);
          if (err) return `Error: ${err}`;
        try {
          const wb = await loadWorkbook(filePath);
          if (!wb.getWorksheet(sheetName))
            return `Error: Sheet "${sheetName}" not found. Available: ${wb.worksheets.map(s => s.name).join(", ")}`;
          const sheets = wb.worksheets.map(s => ({ name: s.name, data: worksheetToJson(s) }));
          const idx = sheets.findIndex(s => s.name === sheetName);
          const [moved] = sheets.splice(idx, 1);
          sheets.splice(Math.max(0, Math.min(position, sheets.length)), 0, moved);
          // Rebuild workbook in new order
          const newWb = new ExcelJS.Workbook();
          for (const { name, data } of sheets) {
            const ws = newWb.addWorksheet(name);
            if (data.length) jsonToWorksheet(ws, data);
          }
          await saveWorkbook(newWb, filePath);
          return JSON.stringify({ success: true, file: filePath, sheet: sheetName,
            new_position: position, all_sheets: newWb.worksheets.map(s => s.name) });
        } catch (e) { return `Error: ${e}`; }
      },
    }),

    // ── 19. excel_copy_rows_between_sheets ───────────────────────────────────
    tool({
      name: "excel_copy_rows_between_sheets",
      description: "Copy or move rows from one sheet to another in the same workbook. Optional filter to select which rows to transfer.",
      parameters: {
        filePath: z.string(),
        sourceSheet: z.string(),
        targetSheet: z.string(),
        filters: z.array(z.object({
          column: z.string(),
          operator: z.enum(["=","!=",">","<",">=","<=","contains","startsWith","endsWith","regex","isNull","isNotNull"]),
          value: z.union([z.string(), z.number()]).optional(),
        })).optional().describe("Filter conditions — omit to transfer all rows"),
        move: z.boolean().optional().describe("If true, delete transferred rows from source (default: false)"),
      },
      implementation: async ({ filePath, sourceSheet, targetSheet, filters, move }) => {
        filePath = resolvePath(filePath, workingDir);
          const err = checkReadable(filePath);
          if (err) return `Error: ${err}`;
        try {
          backupFile(filePath);
          const wb = await loadWorkbook(filePath);
          const srcWs = getSheet(wb, sourceSheet);
          if (typeof srcWs === "string") return `Error: ${srcWs}`;
          const tgtWs = getSheet(wb, targetSheet);
          if (typeof tgtWs === "string") return `Error: ${tgtWs}. Create it first with excel_create_sheet`;
          const srcRows = worksheetToJson(srcWs);
          const transfer = filters?.length
            ? srcRows.filter(r => matchesFilters(r, filters as FilterCondition[]))
            : srcRows;
          if (!transfer.length) return JSON.stringify({ success: true, rows_transferred: 0, message: "No rows matched" });
          const tgtRows = worksheetToJson(tgtWs);
          const merged = [...tgtRows, ...transfer];
          tgtWs.spliceRows(1, tgtWs.rowCount);
          jsonToWorksheet(tgtWs, merged);
          if (move) {
            const remaining = filters?.length ? srcRows.filter(r => !matchesFilters(r, filters as FilterCondition[])) : [];
            srcWs.spliceRows(1, srcWs.rowCount);
            if (remaining.length) jsonToWorksheet(srcWs, remaining);
          }
          await saveWorkbook(wb, filePath);
          return JSON.stringify({ success: true, file: filePath,
            operation: move ? "move" : "copy",
            source: { sheet: sourceSheet, rows_after: move ? srcRows.length - transfer.length : srcRows.length },
            target: { sheet: targetSheet, rows_after: merged.length },
            rows_transferred: transfer.length, preview: preview(transfer) });
        } catch (e) { return `Error: ${e}`; }
      },
    }),

  ];
}