import pandas as pd
import re
import gradio as gr

# Function to check if a cell contains a number with matching last 4 digits
def contains_matching_number(row, numbers_list):
    for item in row:
        item_str = str(item)  # Convert cell value to string
        for number in numbers_list:
            if re.search(fr"\b{number}\b", item_str[-4:]):  # Match last 4 digits
                return True
    return False

# Function to process the Excel file
def process_excel(file, numbers):
    # Read the Excel file
    df = pd.read_excel(file, sheet_name=0)  # Read the first sheet

    # Convert input text to a list of numbers
    numbers_list = numbers.split(",")  # Example: "10857, 2140" → ['10857', '2140']
    numbers_list = [num.strip()[-4:] for num in numbers_list]  # Extract last 4 digits

    # Filter rows where any cell contains a number matching last 4 digits
    matched_rows = df[df.apply(lambda row: contains_matching_number(row, numbers_list), axis=1)]

    # Save results to a new Excel file
    output_file = "processed_data.xlsx"
    with pd.ExcelWriter(output_file, engine="openpyxl") as writer:
        df.to_excel(writer, sheet_name="OriginalData", index=False)
        matched_rows.to_excel(writer, sheet_name="MatchedRows", index=False)

    return output_file

# Gradio Interface
def gradio_interface(file, numbers):
    if file is None:
        return None
    processed_file = process_excel(file.name, numbers)
    return processed_file

# Create Gradio UI
iface = gr.Interface(
    fn=gradio_interface,
    inputs=[
        gr.File(label="Upload Excel File"),
        gr.Textbox(label="Enter numbers to match (comma-separated)")
    ],
    outputs=gr.File(label="Download Processed Excel File"),
    title="Excel Row Matcher (Last 4 Digits)",
    description="Upload an Excel file and enter numbers. The script will find rows where any cell contains the last 4 digits matching the entered numbers and save them to a new sheet.",
)

# Launch the Gradio app
iface.launch(share=True)