import pandas as pd
from langchain.tools import tool
from langchain_experimental.agents import create_pandas_dataframe_agent
from langchain_openai import ChatOpenAI

from core.messages import attachmentHandler

llm = ChatOpenAI(model="gpt-4.1")


@tool
def query_excel_file(question: str, file_reference: str) -> str:
    """
    Analyze the incoming excel file (xls/xlsx) and answer the question based on this analysis
    :param question: the question concerning the data in the given excel file
    :param file_reference: the content of the excel file encoded base64
    :return: the answer to the question
    """
    # Load Excel file
    content_bytes = attachmentHandler.fetch_file_from_reference(file_reference)
    df = pd.read_excel(content_bytes)
    # Create agent
    pandas_agent = create_pandas_dataframe_agent(llm, df, verbose=True, allow_dangerous_code=True)
    response = pandas_agent.run(question)
    return response