from typing import Optional
from google import genai
from google.genai import types
import pandas as pd
import requests
import os
import io
from io import BytesIO

def excel_file_reader(file_url: str, analysis_prompt: Optional[str] = None) -> str:
    """
    Loads Excel files and analyzes the content of the file.
    
    Args:
        file_url (str): Url path to an Excel file to load
        analysis_prompt (Optional[str]): Optional prompt for specific analysis focus
        
    Returns:
        str: Returns the analysis results as a string.
    """
    try:
        # Initialize Google Gen client
        print(f"Load excel file from URL {file_url}")

        gemini_llm =  genai.Client(api_key=os.getenv("GOOGLE_API_KEY"))

        resp = requests.get(file_url)
       
        sheets = pd.read_excel(BytesIO(resp.content), sheet_name=None)
        for _, sheet_data in sheets.items():
            csv_buffer = io.StringIO()
            sheet_data.to_csv(csv_buffer, index=False)
            csv_bytes = csv_buffer.getvalue().encode('utf-8')
            
            text = analysis_prompt or "Provide a detailed description of this CSV file."
            csv_part = types.Part.from_bytes(
                data=csv_bytes,
                mime_type='text/csv'
            )
            
            response = gemini_llm.models.generate_content(
                model="gemini-2.0-flash",
                contents=[text, csv_part]
            )

            return response.text

        return ""
            
    except Exception as e:
        return {"error": f"Error analyzing excel file: {str(e)}"}