import os
import subprocess

# Function to install a package if it is not already installed
def install(package):
    subprocess.check_call([os.sys.executable, "-m", "pip", "install", package])

# Ensure the necessary packages are installed
install("pandas")
install("gradio")
install("openpyxl")

import pandas as pd
import gradio as gr

# Path to the Excel file
file_path = 'cleaned_data.xlsx'

# Function to load the dataset from the provided file path
def load_dataset():
    try:
        df = pd.read_excel(file_path)
        print("File loaded successfully.")
        print(df.head())  # Print first few rows for debugging
        return df
    except Exception as e:
        print(f"Error loading file: {e}")
        return None

# Function to get details based on the name or phone number
def get_details(name, phone_number, df):
    # Clean the 'Name' column for consistent searching
    df['Name'] = df['Name'].astype(str).str.strip().str.lower()
    df['Contact Number (Whatsapp)'] = df['Contact Number (Whatsapp)'].astype(str).str.strip()

    name = name.strip().lower() if name else None
    phone_number = phone_number.strip() if phone_number else None

    print(f"Searching for Name: {name}, Phone Number: {phone_number}")  # Debugging output

    # Filter based on the provided inputs
    if name and phone_number:
        results = df[(df['Name'].str.contains(name, case=False, na=False)) |
                     (df['Contact Number (Whatsapp)'] == phone_number)]
    elif name:
        results = df[df['Name'].str.contains(name, case=False, na=False)]
    elif phone_number:
        results = df[df['Contact Number (Whatsapp)'] == phone_number]
    else:
        return None  # If neither is provided, return None

    if not results.empty:
        print(f"Found Details: {results.to_dict('records')}")  # Debugging output
        return results.to_dict('records')[0]  # Return the first match for simplicity
    else:
        print("No matching records found.")  # Debugging output
        return None

# Function to format the output in HTML
def format_output(details):
    if details is None:
        return "No matching records found."

    # Build HTML table for a cleaner presentation
    html = "<table style='border-collapse: collapse; width: 100%;'>"
    html += "<tr style='background-color: #f2f2f2;'><th style='border: 1px solid #ddd; padding: 8px;'>Ex Cadet</th><th style='border: 1px solid #ddd; padding: 8px;'>Ex Cadet Detail</th></tr>"
    
    for key, value in details.items():
        html += f"<tr><td style='border: 1px solid #ddd; padding: 8px;'>{key}</td><td style='border: 1px solid #ddd; padding: 8px;'>{value if value else 'N/A'}</td></tr>"
    
    html += "</table>"
    return html

# Function to add or update details
def add_or_update_details(name, phone_number, current_occupation, pass_out_year):
    try:
        # Load the existing data
        df = load_dataset()
        if df is None:
            return "Error loading data."

        # Find the entry for the name or phone number
        details = get_details(name, phone_number, df)

        if details:
            # Update the phone number, current occupation, and pass out year
            if phone_number:
                df.loc[df['Name'].str.lower() == name.strip().lower(), 'Contact Number (Whatsapp)'] = phone_number
            if current_occupation:
                df.loc[df['Name'].str.lower() == name.strip().lower(), 'Current Occupation'] = current_occupation
            if pass_out_year:
                df.loc[df['Name'].str.lower() == name.strip().lower(), 'Pass Out Year'] = pass_out_year
            message = "Phone number, occupation, and pass out year updated successfully."
        else:
            # Add a new entry if the name or phone number was not found
            new_entry = {
                'Name': name,
                'Contact Number (Whatsapp)': phone_number,
                'Current Occupation': current_occupation,
                'Pass Out Year': pass_out_year,
                # Add other fields with default values as needed
            }
            df = df.append(new_entry, ignore_index=True)
            message = "New entry added successfully."

        # Save the updated DataFrame back to the Excel file
        df.to_excel(file_path, index=False)
        print("Data saved successfully.")
        return message
    except Exception as e:
        print(f"An error occurred: {e}")
        return f"Error: {e}"

# Combine the functions to create a prediction and update functionality
def predict_and_update(name, phone_number=None, current_occupation=None, pass_out_year=None):
    try:
        # Load the dataset from the provided file path
        df = load_dataset()
        if df is None:
            return "Error loading the file."

        # Get details for the provided name or phone number
        details = get_details(name, phone_number, df)
        details_html = format_output(details)

        # If a phone number, current occupation, or pass out year is provided, update or add the details
        if phone_number or current_occupation or pass_out_year:
            update_message = add_or_update_details(name, phone_number, current_occupation, pass_out_year)
            return f"{details_html}<br><br>{update_message}"
        else:
            return details_html
    except Exception as e:
        print(f"An error occurred: {e}")
        return f"Error: {e}"

# Build the Gradio interface with additional fields for updates
iface = gr.Interface(
    fn=predict_and_update,
    inputs=[
        gr.Textbox(lines=1, label="**Enter Name** (Optional)"),
        gr.Textbox(lines=1, label="**Phone Number** (Optional)"),
        gr.Textbox(lines=1, label="**Current Occupation** (Optional - to add/update)"),
        gr.Textbox(lines=1, label="**Pass Out Year** (Optional - to add/update)")
    ],
    outputs="html",
    title="Name and Phone Number Lookup and Update",
    description="Enter a name or phone number to find details associated with it from the 'cleaned_data.xlsx' file. Optionally, add or update the phone number, current occupation, and pass out year."
)

# Run the Gradio interface
if __name__ == "__main__":
    iface.launch()