import pandas as pd
import streamlit as st

def search_application(user_application_number, df):
    # Ensure the Application Number column is treated as string for consistent comparison
    df['Application Number'] = df['Application Number'].astype(str)
    
    # Search for the application number in the dataframe
    result = df[df['Application Number'] == user_application_number]
    
    if not result.empty:
        decision = result.iloc[0]['Decision']
        if decision.lower() == 'approved':
            st.success(f"Congratulations! Your visa application ({user_application_number}) has been Approved.")
        elif decision.lower() == 'rejected':
            st.error(f"Sorry, your visa application ({user_application_number}) has been Rejected.")
        else:
            st.warning(f"Your visa application ({user_application_number}) has a status of '{decision}'.")
    else:
        # When no record is found, show a warning
        st.warning(f"No record found for Application Number: {user_application_number}.")
        
        # Remove non-numeric rows for the nearest application number calculation
        df_cleaned = df[pd.to_numeric(df['Application Number'], errors='coerce').notnull()]
        
        # Convert application number to integer for the nearest calculation
        df_cleaned['Application Number'] = df_cleaned['Application Number'].astype(int)
        
        try:
            user_application_number_int = int(user_application_number)
            
            # Find the nearest pre and post application numbers
            df_sorted = df_cleaned.sort_values(by='Application Number')
            pre_number = df_sorted[df_sorted['Application Number'] < user_application_number_int].tail(1)
            post_number = df_sorted[df_sorted['Application Number'] > user_application_number_int].head(1)
            
            # Prepare the results
            pre_diff = user_application_number_int - pre_number['Application Number'].values[0] if not pre_number.empty else None
            post_diff = post_number['Application Number'].values[0] - user_application_number_int if not post_number.empty else None
            
            result_table = pd.DataFrame({
                "Nearest Application": ['Before', 'After'],
                "Application Number": [pre_number['Application Number'].values[0] if not pre_number.empty else None,
                                      post_number['Application Number'].values[0] if not post_number.empty else None],
                "Decision": [pre_number['Decision'].values[0] if not pre_number.empty else None,
                             post_number['Decision'].values[0] if not post_number.empty else None],
                "Difference": [pre_diff, post_diff]
            })

            # Display the nearest application numbers in tabular form
            st.subheader("Nearest Application Numbers")
            st.table(result_table)
        except ValueError:
            st.error("Invalid Application Number format. Please enter a numeric value.")