from smolagents.tools import Tool
import pandas as pd
from tabulate import tabulate
from typing import Union, Optional
from huggingface_hub import hf_hub_download

class ExcelAnalysisTool(Tool):
    """Render an Excel worksheet as Markdown text."""

    name = "excel_to_text"
    description = (
        "Read an Excel file and return a Markdown table of the requested sheet. "
        "Accepts either the sheet name or the zero-based index."
    )

    inputs = {
        "excel_path": {
            "type": "string",
            "description": "Path to the Excel file (.xlsx / .xls).",
        },
        "sheet_name": {
            "type": "string",
            "description": (
                "Worksheet name or zero‑based index *as a string* (optional; default first sheet)."
            ),
            "nullable": True,
        },
    }

    output_type = "string"

    def forward(
            self,
            excel_path: str,
            sheet_name: Optional[str] = None,
    ) -> str:
        """Load *excel_path* and return the sheet as a Markdown table."""

        path = pathlib.Path(excel_path).expanduser().resolve()
        if not path.exists():
            return f"Error: Excel file not found at {path}"

        try:
            # Interpret sheet identifier -----------------------------------
            sheet: Union[str, int]
            if sheet_name is None or sheet_name == "":
                sheet = 0  # first sheet
            else:
                # If the user passed a numeric string (e.g. "1"), cast to int
                sheet = int(sheet_name) if sheet_name.isdigit() else sheet_name

            # Load worksheet ----------------------------------------------
            df = pd.read_excel(path, sheet_name=sheet)

            # Render to Markdown; fall back to tabulate if needed ---------
            if hasattr(pd.DataFrame, "to_markdown"):
                return df.to_markdown(index=False)
            from tabulate import tabulate  # pragma: no cover – fallback path

            return tabulate(df, headers="keys", tablefmt="github", showindex=False)

        except Exception as exc:  # broad catch keeps the agent chat‑friendly
            return f"Error reading Excel file: {exc}"