import gradio as gr
import pandas as pd

def process_files(files):

    all_data = []

    for file in files:

        df = pd.read_excel(file.name, sheet_name="Price Recommendations-Fill This", header=None)

        # remove first 4 rows
        df = df.iloc[4:].reset_index(drop=True)

        # ensure at least 10 columns
        while df.shape[1] < 10:
            df[df.shape[1]] = 0

        # convert columns I and J
        col_i = pd.to_numeric(df.iloc[:,8], errors="coerce").fillna(0)
        col_j = pd.to_numeric(df.iloc[:,9], errors="coerce").fillna(0)

        # H = I - J
        df.iloc[:,7] = col_i - col_j

        # filter H < 59
        df = df[df.iloc[:,7] < 59]

        all_data.append(df)

    # merge all files
    merged = pd.concat(all_data)

    # sort H column low → high
    merged = merged.sort_values(by=7)

    output = "/tmp/merged_output.csv"
    merged.to_csv(output, index=False, header=False)

    return output


demo = gr.Interface(
    fn=process_files,
    inputs=gr.File(file_count="multiple", label="Upload Excel Files"),
    outputs=gr.File(label="Download Merged CSV"),
    title="Bulk Excel Pricing Tool"
)

demo.launch()