import os
import pandas as pd
import numpy as np
import gradio as gr
from openpyxl import Workbook, load_workbook
from openpyxl.styles import PatternFill
import random

def generate_random_light_color():
    min_brightness = 0.7
    while True:
        r, g, b = [random.randint(128, 255) for _ in range(3)]
        brightness = (r * 0.299 + g * 0.587 + b * 0.114) / 255
        if brightness >= min_brightness:
            return f'{r:02X}{g:02X}{b:02X}'

def calculate_threshold(value, is_high_price=True):
    """
    Determine the threshold based on the provided ranges.
    The function checks if it's for High Price or Low Price and returns the respective threshold.
    """
    if 0 <= value <= 200:
        return 0.20
    elif 201 <= value <= 500:
        return 0.70
    elif 501 <= value <= 1000:
        return 1.0
    elif 1001 <= value <= 2000:
        return 2.0
    elif 2001 <= value <= 3000:
        return 3.0
    elif 3001 <= value <= 4000:
        return 4.0
    elif 4001 <= value <= 5000:
        return 5.0
    else:
        return 5.0

def process_section(ws, start_row, end_row, col_index1, col_index2, output_col_index1, output_col_index2, high_threshold, low_threshold):
    colored_pairs = set()

    # Process first column
    for i in range(end_row, start_row - 1, -1):
        for j in range(i - 1, start_row - 1, -1):
            cell_value_i = ws.cell(i, col_index1).value
            cell_value_j = ws.cell(j, col_index1).value
            if not (isinstance(cell_value_i, (int, float)) and isinstance(cell_value_j, (int, float))):
                continue
            if abs(cell_value_i - cell_value_j) <= high_threshold:
                if (i, col_index1) not in colored_pairs and (j, col_index1) not in colored_pairs:
                    color = generate_random_light_color()
                    fill = PatternFill(start_color=color, end_color=color, fill_type="solid")
                    ws.cell(i, col_index1).fill = fill
                    ws.cell(j, col_index1).fill = fill
                    colored_pairs.add((i, col_index1))
                    colored_pairs.add((j, col_index1))

                    output_value1 = max(int(cell_value_i), int(cell_value_j)) + 1
                    ws.cell(j, output_col_index1).value = output_value1

                    for k in range(j - 1, start_row - 1, -1):
                        cell_value_k = ws.cell(k, col_index1).value
                        if isinstance(cell_value_k, (int, float)) and cell_value_k > output_value1:
                            if ws.cell(k, 6).value > max(cell_value_i, cell_value_j):
                                ws.cell(j, output_col_index1).fill = fill
                            break

    # Process second column
    for i in range(end_row, start_row - 1, -1):
        for j in range(i - 1, start_row - 1, -1):
            cell_value_i = ws.cell(i, col_index2).value
            cell_value_j = ws.cell(j, col_index2).value
            if not (isinstance(cell_value_i, (int, float)) and isinstance(cell_value_j, (int, float))):
                continue
            if abs(cell_value_i - cell_value_j) <= low_threshold:
                if (i, col_index2) not in colored_pairs and (j, col_index2) not in colored_pairs:
                    color = generate_random_light_color()
                    fill = PatternFill(start_color=color, end_color=color, fill_type="solid")
                    ws.cell(i, col_index2).fill = fill
                    ws.cell(j, col_index2).fill = fill
                    colored_pairs.add((i, col_index2))
                    colored_pairs.add((j, col_index2))

                    output_value2 = min(int(cell_value_i), int(cell_value_j)) - 1
                    ws.cell(j, output_col_index2).value = output_value2

                    for k in range(j - 1, start_row - 1, -1):
                        cell_value_k = ws.cell(k, col_index2).value
                        if isinstance(cell_value_k, (int, float)) and cell_value_k < output_value2:
                            if ws.cell(k, 6).value < min(cell_value_i, cell_value_j):
                                ws.cell(j, output_col_index2).fill = fill
                            break

def highlight_pairs_with_rgb(input_file):
    df = pd.read_csv(input_file)
    df.columns = df.columns.str.strip()

    # Convert to Excel and add three empty columns
    excel_filename = input_file.replace(".csv", ".xlsx")
    with pd.ExcelWriter(excel_filename, engine='openpyxl') as writer:
        # Reorder and create Excel file with empty columns
        df_new = pd.concat([df.iloc[:, :8], pd.DataFrame(columns=['', '', '']), df.iloc[:, 8:]], axis=1)
        df_new.to_excel(writer, index=False, sheet_name="Sheet1")
    
    wb = load_workbook(excel_filename)
    ws = wb.active

    # Determine thresholds for High Price (Column 6) and Low Price (Column 7)
    high_price_col = df["HIGH PRICE"].dropna()
    low_price_col = df["LOW PRICE"].dropna()

    high_threshold = calculate_threshold(high_price_col.max(), is_high_price=True)
    low_threshold = calculate_threshold(low_price_col.min(), is_high_price=False)

    last_row = ws.max_row
    col_index1, col_index2 = 6, 7  # HIGH PRICE and LOW PRICE columns in Excel
    output_col_index1, output_col_index2 = 9, 10  # Empty columns added earlier

    ws.cell(1, output_col_index1).value = "High Result"
    ws.cell(1, output_col_index2).value = "Low Result"

    start_row = 2
    while start_row <= last_row:
        end_row = start_row
        while end_row <= last_row and ws.cell(end_row, 1).value is not None:
            end_row += 1
        end_row -= 1

        process_section(ws, start_row, end_row, col_index1, col_index2, output_col_index1, output_col_index2, high_threshold, low_threshold)
        start_row = end_row + 2

    wb.save(excel_filename)
    return excel_filename

def gradio_interface(input_file):
    output_file = highlight_pairs_with_rgb(input_file.name)
    return output_file

# Gradio app interface
iface = gr.Interface(
    fn=gradio_interface,
    inputs=gr.File(label="Upload CSV File (.csv)", file_count="single"),
    outputs=gr.File(label="Download Processed Excel File"),
    title="CSV to Excel Processor with Cell Highlighting",
    description="Upload a CSV file with stock data, and download a processed Excel file with highlighted cells."
)

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