import requests
import pandas as pd
from io import BytesIO
from bs4 import BeautifulSoup
import streamlit as st

# Streamlit app title
st.title("Visa Application Status Checker")

# URL of the website to scrape
url = "https://www.ireland.ie/en/india/newdelhi/services/visas/processing-times-and-decisions/"

# Headers to mimic a browser request
headers = {
    "User-Agent": (
        "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 "
        "(KHTML, like Gecko) Chrome/114.0.0.0 Safari/537.36"
    )
}

# Step 1: Function to fetch and cache the .ods file
@st.cache_data(ttl=3600, max_entries=1)
def fetch_ods_file():
    response = requests.get(url, headers=headers)
    if response.status_code == 200:
        soup = BeautifulSoup(response.content, 'html.parser')

        # Find all anchor tags
        links = soup.find_all('a')

        # Search for the link containing the specific text
        file_url = None
        for link in links:
            link_text = link.get_text(strip=True)
            if "Visa decisions made from 1 January 2024 to" in link_text:
                file_url = link.get('href')
                file_name = link_text
                break
        
        if file_url:
            # Make the link absolute if it is relative
            if not file_url.startswith('http'):
                file_url = requests.compat.urljoin(url, file_url)
            
            file_response = requests.get(file_url, headers=headers)
            
            if file_response.status_code == 200:
                return BytesIO(file_response.content), file_name
            else:
                st.error(f"Failed to download the file. Status code: {file_response.status_code}")
        else:
            st.error("The specified link was not found.")
    else:
        st.error(f"Failed to retrieve the webpage. Status code: {response.status_code}")
    return None, None

# Step 2: Fetch the cached .ods file
ods_file, cached_file_name = fetch_ods_file()

if ods_file:
    try:
        # Step 3: Read the .ods file into a DataFrame
        df = pd.read_excel(ods_file, engine='odf')
        
        # Clean up the DataFrame by dropping unnecessary columns
        df.drop(columns=["Unnamed: 0", "Unnamed: 1"], inplace=True, errors='ignore')
        
        # Drop empty rows and reset index
        df.dropna(how='all', inplace=True)
        df.reset_index(drop=True, inplace=True)
        
        # Identify the header row and reformat DataFrame
        for idx, row in df.iterrows():
            if row['Unnamed: 2'] == 'Application Number' and row['Unnamed: 3'] == 'Decision':
                df.columns = ['Application Number', 'Decision']
                df = df.iloc[idx + 1:]  # Skip the header row
                break
        
        # Reset index after cleaning
        df.reset_index(drop=True, inplace=True)
        
        # Convert "Application Number" to string for consistency
        df['Application Number'] = df['Application Number'].astype(str)
        
        # Step 4: Get user input for application number using Streamlit
        user_application_number = st.text_input("Enter your Application Number")
        
        # Step 5: Check if the application number exists in the DataFrame
        if user_application_number:
            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:
                st.warning(f"No record found for Application Number: {user_application_number}.")
                
                # Convert Application Numbers to integers for comparison
                df['Application Number'] = df['Application Number'].astype(int)
                try:
                    user_application_number_int = int(user_application_number)
                    
                    # Step 6: Find the nearest pre and post application numbers
                    df_sorted = df.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]
                    })

                    # Step 7: 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.")
    
    except Exception as e:
        st.error(f"Error reading the .ods file: {e}")
else:
    st.error("No file data available.")