src/Agentic_System/Google_Sheet_Agent.py

"""
Google Sheets Agent – specialized agent with only Sheets MCP tools.

Uses MCPServerStdio (local subprocess) with create_static_tool_filter so the
agent sees *only* spreadsheet-related tools.  Zero network overhead.
"""

import asyncio
import logging
from openai import AsyncOpenAI
from agents import Agent, Runner, OpenAIChatCompletionsModel
from agents.model_settings import ModelSettings

try:
    from .google_mcp_config import (
        LONGCAT_API_KEY, LONGCAT_BASE_URL, MODEL_NAME,
        SHEETS_TOOLS, create_google_mcp_server, USER_GOOGLE_EMAIL,
    )
except ImportError:
    from google_mcp_config import (
        LONGCAT_API_KEY, LONGCAT_BASE_URL, MODEL_NAME,
        SHEETS_TOOLS, create_google_mcp_server, USER_GOOGLE_EMAIL,
    )

logger = logging.getLogger(__name__)

SYSTEM_PROMPT = """\
You are a specialized Google Sheets assistant. You can create, read, modify,
and format Google Sheets spreadsheets using the available tools.

Capabilities:
- **List & search** spreadsheets accessible to the user
- **Read** cell values from any range (A1 notation, e.g. "Sheet1!A1:D10")
- **Write / update** cell values (single or batch)
- **Format** cells – colors (#RRGGBB), bold, italic, font size, alignment,
  number formats, text wrapping
- **Conditional formatting** – add, update, or delete rules
- **Create** new spreadsheets and sheets (tabs)
- **Comments** – read, create, reply to, and resolve comments

Rules:
1. The user's Google email is provided in the query — use it for every
   tool call in the `user_google_email` parameter. NEVER ask the user
   for their email; it is always supplied.
2. Use A1 notation for range references.
3. When modifying values, prefer `value_input_option="USER_ENTERED"` so
   formulas and dates are interpreted correctly.
4. After any write operation, confirm what was changed.
"""


class GoogleSheetsAgent:
    """Thin wrapper around the OpenAI Agent SDK wired to Google Sheets tools."""

    def __init__(self, model: str = MODEL_NAME):
        self.model = model
        self._client = AsyncOpenAI(
            api_key=LONGCAT_API_KEY,
            base_url=LONGCAT_BASE_URL,
            timeout=30.0,
        )

    # ── factory helpers ──────────────────────────────────────────────────
    def _create_mcp_server(self):
        """Spawn a local MCP subprocess with only Sheets tools loaded."""
        return create_google_mcp_server(service="sheets", tool_names=SHEETS_TOOLS)

    def _create_agent(self, mcp_server) -> Agent:
        return Agent(
            name="Google Sheets Agent",
            instructions=SYSTEM_PROMPT,
            mcp_servers=[mcp_server],
            model=OpenAIChatCompletionsModel(
                model=self.model,
                openai_client=self._client,
            ),
            model_settings=ModelSettings(tool_choice="auto"),
        )

    # ── public API ───────────────────────────────────────────────────────
    async def run(self, query: str) -> str:
        """Spawn MCP connection, run a single query, then clean up."""
        mcp_server = self._create_mcp_server()
        async with mcp_server:
            agent = self._create_agent(mcp_server)
            logger.info("Google Sheets MCP connected – agent ready")
            result = await Runner.run(agent, input=query)
            return result.final_output


# ─── CLI entry point ──────────────────────────────────────────────────────────
async def main():
    agent = GoogleSheetsAgent()
    resp = await agent.run(
        "List all my spreadsheets. My email is user@example.com"
    )
    print("Agent Response:\n", resp)


if __name__ == "__main__":
    from dotenv import load_dotenv, find_dotenv
    load_dotenv(find_dotenv())
    asyncio.run(main())