excel-tools

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

/** Parse "A1" or "A1:C10" into individual cell addresses */
function* rangeAddresses(ws: ExcelJS.Worksheet, ref: string): Generator<ExcelJS.Cell> {
  const match = ref.match(/^([A-Z]+)(\d+)(?::([A-Z]+)(\d+))?$/i);
  if (!match) return;
  const colLetter = (s: string) => s.toUpperCase().split("").reduce((a, c) => a * 26 + c.charCodeAt(0) - 64, 0);
  const c1 = colLetter(match[1]); const r1 = parseInt(match[2]);
  const c2 = match[3] ? colLetter(match[3]) : c1;
  const r2 = match[4] ? parseInt(match[4]) : r1;
  for (let r = r1; r <= r2; r++)
    for (let c = c1; c <= c2; c++)
      yield ws.getCell(r, c);
}

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

    // ── 20. excel_format_cells ───────────────────────────────────────────────
    tool({
      name: "excel_format_cells",
      description: "Apply rich formatting to a cell range: font (bold/italic/size/color), background fill, borders, alignment.",
      parameters: {
        filePath: z.string(),
        range: z.string().describe("Cell or range, e.g. 'A1' or 'A1:D10'"),
        sheet: z.string().optional(),
        font: z.object({
          bold: z.boolean().optional(),
          italic: z.boolean().optional(),
          size: z.number().optional(),
          color: z.string().optional().describe("ARGB hex, e.g. 'FFFF0000' for red"),
          underline: z.boolean().optional(),
        }).optional(),
        fill: z.object({
          color: z.string().describe("ARGB hex background color, e.g. 'FFFFFF00' for yellow"),
        }).optional(),
        border: z.enum(["thin","medium","thick","none"]).optional().describe("Apply border style on all 4 sides"),
        alignment: z.object({
          horizontal: z.enum(["left","center","right","fill","justify"]).optional(),
          vertical: z.enum(["top","middle","bottom"]).optional(),
          wrapText: z.boolean().optional(),
        }).optional(),
        numberFormat: z.string().optional().describe("Excel number format string, e.g. '#,##0.00' or '0%'"),
      },
      implementation: async ({ filePath, range, sheet, font, fill, border, alignment, numberFormat }) => {
        filePath = resolvePath(filePath, workingDir);
          const err = checkReadable(filePath);
          if (err) return `Error: ${err}`;
        try {
          const wb = await loadWorkbook(filePath);
          const ws = getSheet(wb, sheet);
          if (typeof ws === "string") return `Error: ${ws}`;
          let count = 0;
          for (const cell of rangeAddresses(ws, range)) {
            if (font) cell.font = { ...cell.font, ...font, color: font.color ? { argb: font.color } : cell.font?.color } as ExcelJS.Font;
            if (fill) cell.fill = { type: "pattern", pattern: "solid", fgColor: { argb: fill.color } };
            if (border && border !== "none") {
              const s = { style: border as ExcelJS.BorderStyle };
              cell.border = { top: s, bottom: s, left: s, right: s };
            }
            if (border === "none") cell.border = {};
            if (alignment) cell.alignment = { ...cell.alignment, ...alignment };
            if (numberFormat) cell.numFmt = numberFormat;
            count++;
          }
          await saveWorkbook(wb, filePath);
          return JSON.stringify({ success: true, file: filePath, sheet: typeof ws === "string" ? ws : ws.name,
            range, cells_formatted: count });
        } catch (e) { return `Error: ${e}`; }
      },
    }),

    // ── 21. excel_conditional_format ─────────────────────────────────────────
    tool({
      name: "excel_conditional_format",
      description: "Add conditional formatting rules to a range. Supports cellIs (>, <, =, between) and colorScale (gradient).",
      parameters: {
        filePath: z.string(),
        range: z.string().describe("Cell range, e.g. 'B2:B100'"),
        type: z.enum(["greaterThan","lessThan","equal","between","top10","colorScale"]),
        value: z.number().optional().describe("Threshold value (for greaterThan/lessThan/equal)"),
        value2: z.number().optional().describe("Second value (for between)"),
        fillColor: z.string().optional().describe("ARGB fill color for matched cells, e.g. 'FFC6EFCE'"),
        fontColor: z.string().optional().describe("ARGB font color for matched cells"),
        sheet: z.string().optional(),
      },
      implementation: async ({ filePath, range, type, value, value2, fillColor, fontColor, sheet }) => {
        filePath = resolvePath(filePath, workingDir);
          const err = checkReadable(filePath);
          if (err) return `Error: ${err}`;
        try {
          const wb = await loadWorkbook(filePath);
          const ws = getSheet(wb, sheet);
          if (typeof ws === "string") return `Error: ${ws}`;
          const style: Partial<ExcelJS.Style> = {};
          if (fillColor) style.fill = { type: "pattern", pattern: "solid", bgColor: { argb: fillColor } };
          if (fontColor) style.font = { color: { argb: fontColor } };

          if (type === "colorScale") {
            ws.addConditionalFormatting({ ref: range, rules: [{
              type: "colorScale", priority: 1,
              cfvo: [{ type: "min" }, { type: "max" }],
              color: [{ argb: "FFF8696B" }, { argb: "FF63BE7B" }],
            } as any] });
          } else {
            const opMap: Record<string, string> = {
              greaterThan: "greaterThan", lessThan: "lessThan",
              equal: "equal", between: "between", top10: "greaterThan",
            };
            ws.addConditionalFormatting({ ref: range, rules: [{
              type: "cellIs", priority: 1,
              operator: opMap[type] as any,
              formulae: type === "between" ? [value ?? 0, value2 ?? 0] : [value ?? 0],
              style,
            }] });
          }
          await saveWorkbook(wb, filePath);
          return JSON.stringify({ success: true, file: filePath, range, type, rule_added: true });
        } catch (e) { return `Error: ${e}`; }
      },
    }),

    // ── 22. excel_freeze_panes ───────────────────────────────────────────────
    tool({
      name: "excel_freeze_panes",
      description: "Freeze rows and/or columns so they stay visible while scrolling.",
      parameters: {
        filePath: z.string(),
        rows: z.number().optional().describe("Number of rows to freeze from the top (e.g. 1 to freeze header)"),
        columns: z.number().optional().describe("Number of columns to freeze from the left"),
        sheet: z.string().optional(),
      },
      implementation: async ({ filePath, rows, columns, sheet }) => {
        filePath = resolvePath(filePath, workingDir);
          const err = checkReadable(filePath);
          if (err) return `Error: ${err}`;
        try {
          const wb = await loadWorkbook(filePath);
          const ws = getSheet(wb, sheet);
          if (typeof ws === "string") return `Error: ${ws}`;
          const r = rows ?? 0; const c = columns ?? 0;
          ws.views = [{
            state: "frozen", xSplit: c, ySplit: r,
            topLeftCell: `${c > 0 ? String.fromCharCode(65 + c) : "A"}${r + 1}`,
            activeCell: "A1",
          }];
          await saveWorkbook(wb, filePath);
          return JSON.stringify({ success: true, file: filePath, sheet: ws.name,
            frozen_rows: r, frozen_columns: c });
        } catch (e) { return `Error: ${e}`; }
      },
    }),

    // ── 23. excel_set_dimensions ─────────────────────────────────────────────
    tool({
      name: "excel_set_dimensions",
      description: "Set precise column widths and row heights.",
      parameters: {
        filePath: z.string(),
        columnWidths: z.array(z.object({ column: z.string().describe("Column letter, e.g. 'A'"), width: z.number() })).optional(),
        rowHeights: z.array(z.object({ row: z.number().describe("1-based row number"), height: z.number() })).optional(),
        sheet: z.string().optional(),
      },
      implementation: async ({ filePath, columnWidths, rowHeights, sheet }) => {
        filePath = resolvePath(filePath, workingDir);
          const err = checkReadable(filePath);
          if (err) return `Error: ${err}`;
        try {
          const wb = await loadWorkbook(filePath);
          const ws = getSheet(wb, sheet);
          if (typeof ws === "string") return `Error: ${ws}`;
          columnWidths?.forEach(({ column, width }) => { ws.getColumn(column).width = width; });
          rowHeights?.forEach(({ row, height }) => { ws.getRow(row).height = height; });
          await saveWorkbook(wb, filePath);
          return JSON.stringify({ success: true, file: filePath, sheet: ws.name,
            columns_resized: columnWidths?.length ?? 0, rows_resized: rowHeights?.length ?? 0 });
        } catch (e) { return `Error: ${e}`; }
      },
    }),

    // ── 24. excel_add_data_validation ────────────────────────────────────────
    tool({
      name: "excel_add_data_validation",
      description: "Add data validation to a cell range: dropdown lists, numeric ranges, date constraints, or custom formulas.",
      parameters: {
        filePath: z.string(),
        range: z.string().describe("Cell or range, e.g. 'C2:C100'"),
        type: z.enum(["list","whole","decimal","date","textLength","custom"]),
        options: z.object({
          list: z.array(z.string()).optional().describe("Dropdown options (for type=list)"),
          formula: z.string().optional().describe("Excel formula for custom validation"),
          operator: z.enum(["between","notBetween","equal","notEqual","greaterThan","lessThan","greaterThanOrEqual","lessThanOrEqual"]).optional(),
          value1: z.union([z.string(),z.number()]).optional(),
          value2: z.union([z.string(),z.number()]).optional(),
          prompt: z.string().optional().describe("Input message shown when cell is selected"),
          error: z.string().optional().describe("Error message shown on invalid input"),
        }).optional(),
        sheet: z.string().optional(),
      },
      implementation: async ({ filePath, range, type, options, sheet }) => {
        filePath = resolvePath(filePath, workingDir);
          const err = checkReadable(filePath);
          if (err) return `Error: ${err}`;
        try {
          const wb = await loadWorkbook(filePath);
          const ws = getSheet(wb, sheet);
          if (typeof ws === "string") return `Error: ${ws}`;
          const validation: ExcelJS.DataValidation = { type: type as any, allowBlank: true,
            formulae: [],
            showInputMessage: !!options?.prompt, showErrorMessage: !!options?.error };
          if (type === "list" && options?.list) {
            validation.formulae = [`"${options.list.join(",")}"`];
          } else if (type === "custom" && options?.formula) {
            validation.formulae = [options.formula];
          } else if (options?.value1 !== undefined) {
            validation.operator = options.operator as any ?? "between";
            validation.formulae = options.value2 !== undefined
              ? [options.value1, options.value2]
              : [options.value1];
          }
          if (options?.prompt) { (validation as any).promptTitle = "Input"; validation.prompt = options.prompt; }
          if (options?.error) { (validation as any).errorTitle = "Invalid"; validation.error = options.error; }
          for (const cell of rangeAddresses(ws, range)) cell.dataValidation = validation;
          await saveWorkbook(wb, filePath);
          return JSON.stringify({ success: true, file: filePath, range, validation_type: type });
        } catch (e) { return `Error: ${e}`; }
      },
    }),

    // ── 25. excel_add_comment ────────────────────────────────────────────────
    tool({
      name: "excel_add_comment",
      description: "Add or replace a comment/note on a specific cell.",
      parameters: {
        filePath: z.string(),
        cell: z.string().describe("Cell address, e.g. 'B5'"),
        comment: z.string(),
        author: z.string().optional().describe("Author name shown in the comment (default: Excel Tools)"),
        sheet: z.string().optional(),
      },
      implementation: async ({ filePath, cell, comment, author, sheet }) => {
        filePath = resolvePath(filePath, workingDir);
          const err = checkReadable(filePath);
          if (err) return `Error: ${err}`;
        try {
          const wb = await loadWorkbook(filePath);
          const ws = getSheet(wb, sheet);
          if (typeof ws === "string") return `Error: ${ws}`;
          ws.getCell(cell).note = {
            texts: [{ font: { bold: false, size: 10 }, text: comment }],
            editAs: "oneCells",
          } as any;
          await saveWorkbook(wb, filePath);
          return JSON.stringify({ success: true, file: filePath, cell, comment, author: author ?? "Excel Tools" });
        } catch (e) { return `Error: ${e}`; }
      },
    }),

    // ── 26. excel_add_hyperlink ──────────────────────────────────────────────
    tool({
      name: "excel_add_hyperlink",
      description: "Add a clickable hyperlink to a cell. Supports URLs and internal sheet references.",
      parameters: {
        filePath: z.string(),
        cell: z.string().describe("Cell address, e.g. 'A1'"),
        url: z.string().describe("URL (https://...) or internal ref (e.g. 'Sheet2!A1')"),
        displayText: z.string().optional().describe("Text shown in cell (default: the URL)"),
        tooltip: z.string().optional(),
        sheet: z.string().optional(),
      },
      implementation: async ({ filePath, cell, url, displayText, tooltip, sheet }) => {
        filePath = resolvePath(filePath, workingDir);
          const err = checkReadable(filePath);
          if (err) return `Error: ${err}`;
        try {
          const wb = await loadWorkbook(filePath);
          const ws = getSheet(wb, sheet);
          if (typeof ws === "string") return `Error: ${ws}`;
          const c = ws.getCell(cell);
          c.value = { text: displayText ?? url, hyperlink: url, tooltip } as ExcelJS.CellHyperlinkValue;
          c.font = { color: { argb: "FF0563C1" }, underline: true };
          await saveWorkbook(wb, filePath);
          return JSON.stringify({ success: true, file: filePath, cell, url, display: displayText ?? url });
        } catch (e) { return `Error: ${e}`; }
      },
    }),

  ];
}