import openpyxl
from PIL import Image, ImageDraw, ImageFont
import base64
import requests
import gradio as gr
import os

# OpenAI API Key
api_key = os.environ.get("api_key")

# Function to encode the image
def encode_image(image_path):
    with open(image_path, "rb") as image_file:
        return base64.b64encode(image_file.read()).decode('utf-8')

def take_screenshot_of_cells(file_path, sheet_name, start_cell, end_cell, query):
    # Load the workbook and the specified sheet
    workbook = openpyxl.load_workbook(file_path)
    sheet = workbook[sheet_name]
    
    # Get the coordinates of the start and end cells
    start_col, start_row = openpyxl.utils.cell.coordinate_to_tuple(start_cell)
    end_col, end_row = openpyxl.utils.cell.coordinate_to_tuple(end_cell)
    
    # Calculate the number of rows and columns
    num_cols = end_col - start_col + 1
    num_rows = end_row - start_row + 1
    
    # Set the width and height of each cell (you can adjust these values as needed)
    cell_width = 100
    cell_height = 30
    
    # Create a blank image with the calculated size
    image_width = cell_width * num_cols
    image_height = cell_height * num_rows
    image = Image.new('RGB', (image_width, image_height), 'white')
    draw = ImageDraw.Draw(image)
    
    # Load a font (you may need to specify the path to a font file on your system)
    font = ImageFont.load_default()
    
    # Draw the cells and their content
    for row in range(num_rows):
        for col in range(num_cols):
            cell_value = sheet.cell(row=start_row + row, column=start_col + col).value
            x0 = col * cell_width
            y0 = row * cell_height
            x1 = x0 + cell_width
            y1 = y0 + cell_height
            
            # Draw the cell border
            draw.rectangle([x0, y0, x1, y1], outline='black')
            
            # Draw the cell value
            if cell_value is not None:
                draw.text((x0 + 5, y0 + 5), str(cell_value), fill='black', font=font)
    
    # Save the image
    output_image_path = 'screenshot.png'
    image.save(output_image_path)

    # Encode the image to base64
    base64_image = encode_image(output_image_path)
    
    # Send the image to the OpenAI API
    headers = {
        "Content-Type": "application/json",
        "Authorization": f"Bearer {api_key}"
    }

    payload = {
        "model": "gpt-4o",  # Assuming the model name is 'gpt-4-vision'
        "messages": [
            {
                "role": "user",
                "content": [
                    {
                        "type": "text",
                        "text": query
                    },
                    {
                        "type": "image_url",
                        "image_url": {
                            "url": f"data:image/jpeg;base64,{base64_image}"
                        }
                    }
                ]
            }
        ],
        "max_tokens": 300
    }

    response = requests.post("https://api.openai.com/v1/chat/completions", headers=headers, json=payload)
    response=response.json()
    return response['choices'][0]['message']['content']

def gradio_interface(excel_file, sheet_name, start_cell, end_cell, query):
    return take_screenshot_of_cells(excel_file.name, sheet_name, start_cell, end_cell, query)

# Create Gradio Interface
inputs = [
    gr.File(label="Upload Excel File"),
    gr.Textbox(label="Sheet Name"),
    gr.Textbox(label="Start Cell"),
    gr.Textbox(label="End Cell"),
    gr.Textbox(label="Query")
]

outputs = gr.Textbox(label="Output")

gr.Interface(fn=gradio_interface, inputs=inputs, outputs=outputs, title="Excel Screenshot Analyzer").launch()