import pandas as pd
import openpyxl
from openpyxl.styles import PatternFill
import gradio as gr

def load_excel(file_path):
    """Loads the Excel file into a pandas DataFrame."""
    df = pd.read_excel(file_path, sheet_name="Sheet1")
    return df

def highlight_pairs(df, threshold=2.0):
    """Highlights pairs in columns D and E based on the threshold."""
    colors = ["FF0000", "00FF00", "0000FF", "FFFF00", "FF00FF", "00FFFF"]  # Hex colors for RGB
    color_index = 0
    
    df['High Result'] = ''
    df['Low Result'] = ''
    
    colored_pairs = set()
    wb = openpyxl.load_workbook(file_path)
    ws = wb['Sheet1']
    
    # Function to apply color to cells
    def apply_color(cell, color_hex):
        cell.fill = PatternFill(start_color=color_hex, end_color=color_hex, fill_type="solid")
    
    last_row = len(df)
    
    def process_column(df, col_index, output_col, color_pairs, colors):
        nonlocal color_index
        for i in range(last_row - 1, 1, -1):
            for j in range(i - 1, 1, -1):
                if abs(df.iloc[i, col_index] - df.iloc[j, col_index]) <= threshold:
                    pair_key_i = (i, col_index)
                    pair_key_j = (j, col_index)
                    
                    if pair_key_i not in color_pairs and pair_key_j not in color_pairs:
                        apply_color(ws.cell(row=i+2, column=col_index+1), colors[color_index])
                        apply_color(ws.cell(row=j+2, column=col_index+1), colors[color_index])
                        
                        color_pairs.add(pair_key_i)
                        color_pairs.add(pair_key_j)
                        
                        output_value = (max if col_index == 3 else min)(
                            int(df.iloc[i, col_index]), int(df.iloc[j, col_index])
                        ) + (1 if col_index == 3 else -1)
                        
                        color_output = False
                        for k in range(i - 1, 1, -1):
                            if ((df.iloc[k, col_index] > output_value and col_index == 3) or 
                                (df.iloc[k, col_index] < output_value and col_index == 4)):
                                if ((df.iloc[k, 5] > max(df.iloc[i, col_index], df.iloc[j, col_index]) and col_index == 3) or
                                    (df.iloc[k, 5] < min(df.iloc[i, col_index], df.iloc[j, col_index]) and col_index == 4)):
                                    apply_color(ws.cell(row=j+2, column=output_col+1), colors[color_index])
                                    color_output = True
                                break
                        
                        ws.cell(row=j+2, column=output_col+1).value = output_value
                        if not color_output:
                            ws.cell(row=j+2, column=output_col+1).fill = PatternFill(fill_type=None)
                        
                        color_index = (color_index + 1) % len(colors)
    
    process_column(df, 3, 6, colored_pairs, colors)  # Process Column D (High Price)
    process_column(df, 4, 7, colored_pairs, colors)  # Process Column E (Low Price)
    
    wb.save('output.xlsx')

def gradio_app(file_path):
    """Main function to handle Gradio inputs and outputs."""
    df = load_excel(file_path)
    highlight_pairs(df)
    return 'output.xlsx'

# Gradio Interface
gr_interface = gr.Interface(
    fn=gradio_app,
    inputs=gr.inputs.File(label="Upload Excel File"),
    outputs=gr.outputs.File(label="Download Processed Excel File"),
    title="Excel Highlighter"
)

if __name__ == "__main__":
    gr_interface.launch()