src/tools/sheets/readCellFormat.ts

import type { FastMCP } from 'fastmcp';
import { UserError } from 'fastmcp';
import { z } from 'zod';
import { getSheetsClient } from '../../clients.js';
import { rowColToA1 } from '../../googleSheetsApiHelpers.js';

/**
 * Converts a Google Sheets RGBA color object (0-1 range) to a hex string.
 * Returns null if the color is undefined or has no meaningful channels.
 */
function rgbaToHex(
  color: { red?: number | null; green?: number | null; blue?: number | null } | null | undefined
): string | null {
  if (!color) return null;
  const r = Math.round((color.red ?? 0) * 255);
  const g = Math.round((color.green ?? 0) * 255);
  const b = Math.round((color.blue ?? 0) * 255);
  return `#${r.toString(16).padStart(2, '0').toUpperCase()}${g.toString(16).padStart(2, '0').toUpperCase()}${b.toString(16).padStart(2, '0').toUpperCase()}`;
}

/**
 * Extracts a simplified formatting summary from a Google Sheets CellFormat object.
 * Only includes properties that are explicitly set (non-default).
 */
function simplifyFormat(fmt: any): Record<string, any> | null {
  if (!fmt) return null;
  const result: Record<string, any> = {};

  // Text formatting
  if (fmt.textFormat) {
    const tf: Record<string, any> = {};
    if (fmt.textFormat.bold) tf.bold = true;
    if (fmt.textFormat.italic) tf.italic = true;
    if (fmt.textFormat.strikethrough) tf.strikethrough = true;
    if (fmt.textFormat.underline) tf.underline = true;
    if (fmt.textFormat.fontSize != null) tf.fontSize = fmt.textFormat.fontSize;
    if (fmt.textFormat.fontFamily) tf.fontFamily = fmt.textFormat.fontFamily;
    if (fmt.textFormat.foregroundColorStyle?.rgbColor) {
      tf.foregroundColor = rgbaToHex(fmt.textFormat.foregroundColorStyle.rgbColor);
    } else if (fmt.textFormat.foregroundColor) {
      tf.foregroundColor = rgbaToHex(fmt.textFormat.foregroundColor);
    }
    if (Object.keys(tf).length > 0) result.textFormat = tf;
  }

  // Background color
  if (fmt.backgroundColorStyle?.rgbColor) {
    result.backgroundColor = rgbaToHex(fmt.backgroundColorStyle.rgbColor);
  } else if (fmt.backgroundColor) {
    result.backgroundColor = rgbaToHex(fmt.backgroundColor);
  }

  // Alignment
  if (fmt.horizontalAlignment) result.horizontalAlignment = fmt.horizontalAlignment;
  if (fmt.verticalAlignment) result.verticalAlignment = fmt.verticalAlignment;

  // Number format
  if (fmt.numberFormat) {
    result.numberFormat = {
      type: fmt.numberFormat.type,
      pattern: fmt.numberFormat.pattern,
    };
  }

  // Borders
  if (fmt.borders) {
    const borders: Record<string, any> = {};
    for (const side of ['top', 'bottom', 'left', 'right'] as const) {
      if (fmt.borders[side]) {
        borders[side] = {
          style: fmt.borders[side].style,
          ...(fmt.borders[side].colorStyle?.rgbColor
            ? { color: rgbaToHex(fmt.borders[side].colorStyle.rgbColor) }
            : fmt.borders[side].color
              ? { color: rgbaToHex(fmt.borders[side].color) }
              : {}),
        };
      }
    }
    if (Object.keys(borders).length > 0) result.borders = borders;
  }

  // Wrap strategy
  if (fmt.wrapStrategy) result.wrapStrategy = fmt.wrapStrategy;

  return Object.keys(result).length > 0 ? result : null;
}

export function register(server: FastMCP) {
  server.addTool({
    name: 'readCellFormat',
    description:
      'Reads the formatting/style of cells in a given range. Returns formatting details like bold, italic, fontSize, fontFamily, colors, alignment, borders, and number format per cell.',
    parameters: z.strictObject({
      spreadsheetId: z
        .string()
        .describe(
          'The spreadsheet ID — the long string between /d/ and /edit in a Google Sheets URL.'
        ),
      range: z
        .string()
        .describe('A1 notation range to read formatting from (e.g., "Sheet1!A1:D5" or "A1:B2").'),
    }),
    execute: async (args, { log }) => {
      const sheets = await getSheetsClient();
      log.info(
        `Reading cell format for range "${args.range}" in spreadsheet ${args.spreadsheetId}`
      );

      try {
        const response = await sheets.spreadsheets.get({
          spreadsheetId: args.spreadsheetId,
          ranges: [args.range],
          includeGridData: true,
          fields:
            'sheets.data.rowData.values.userEnteredFormat,sheets.data.startRow,sheets.data.startColumn',
        });

        const sheetData = response.data.sheets?.[0]?.data?.[0];
        if (!sheetData?.rowData) {
          return JSON.stringify({ range: args.range, cells: [] }, null, 2);
        }

        const startRow = sheetData.startRow ?? 0;
        const startCol = sheetData.startColumn ?? 0;

        const cells: Array<{ cell: string; format: Record<string, any> }> = [];

        for (let rowIdx = 0; rowIdx < sheetData.rowData.length; rowIdx++) {
          const row = sheetData.rowData[rowIdx];
          if (!row.values) continue;

          for (let colIdx = 0; colIdx < row.values.length; colIdx++) {
            const cellData = row.values[colIdx];
            const fmt = simplifyFormat(cellData?.userEnteredFormat);
            if (fmt) {
              const cellRef = rowColToA1(startRow + rowIdx, startCol + colIdx);
              cells.push({ cell: cellRef, format: fmt });
            }
          }
        }

        return JSON.stringify({ range: args.range, cells }, null, 2);
      } catch (error: any) {
        log.error(
          `Error reading cell format for spreadsheet ${args.spreadsheetId}: ${error.message || error}`
        );
        if (error instanceof UserError) throw error;
        throw new UserError(`Failed to read cell format: ${error.message || 'Unknown error'}`);
      }
    },
  });
}