import pandas as pd
from datetime import datetime
import os
import sys
import re
import matplotlib.pyplot as plt
import matplotlib.colors as mcolors
import math
from collections import deque
import numpy as np

# -------------------------
# Step 1: Load the Data from Excel
# -------------------------
def load_data(file_path, skip_rows=0):
    """
    Attempts to load the Excel file, skipping a specified number of rows.
    Iteratively skips more rows until the required columns are found.

    Parameters:
        file_path (str): The path to the Excel file.
        skip_rows (int): Number of rows to skip at the top of the file.

    Returns:
        df (DataFrame): The loaded DataFrame.
        header_row (int): The row number used as headers.
    """
    max_skip = 10  # Maximum rows to skip to find headers
    for skip in range(skip_rows, skip_rows + max_skip):
        try:
            df = pd.read_excel(file_path, header=skip)
            # Clean column names
            df = clean_column_names(df)
            # Define required columns with cleaned names
            required_columns = [
                'Date',
                'Kick_Off_(local)',
                'Home_Team',
                'Away_Team',
                'Home_Score',
                'Away_Score',
                'Home_Odds',
                'Away_Odds'
            ]
            # Verify required columns are present
            verify_columns(df, required_columns)
            print(f"Successfully loaded data with header row at line {skip + 1}.\n")
            return df, skip
        except FileNotFoundError:
            print(f"Error: The file at path '{file_path}' does not exist.")
            sys.exit(1)
        except KeyError as e:
            print(e)
            print(f"Attempting to skip {skip + 1} rows and try again...\n")
        except Exception as e:
            print(f"An unexpected error occurred: {e}")
            print(f"Attempting to skip {skip + 1} rows and try again...\n")
    raise Exception("Unable to locate the required header row in the Excel file.")

# -------------------------
# Step 2: Clean Column Names
# -------------------------
def clean_column_names(df):
    """
    Cleans the column names by stripping leading/trailing spaces, replacing spaces with underscores,
    removing non-printable characters, and standardizing casing.

    Parameters:
        df (DataFrame): The DataFrame whose columns need to be cleaned.

    Returns:
        df (DataFrame): The DataFrame with cleaned column names.
    """
    # Remove non-printable characters
    df.columns = df.columns.str.replace(r'[^\x00-\x7F]+', '', regex=True)
    # Strip leading/trailing spaces
    df.columns = df.columns.str.strip()
    # Replace spaces with underscores
    df.columns = df.columns.str.replace(' ', '_')
    print("Column names after cleaning:")
    print(df.columns.tolist(), "\n")
    return df

# -------------------------
# Step 3: Verify Required Columns
# -------------------------
def verify_columns(df, required_columns):
    """
    Verifies that all required columns are present in the DataFrame.

    Parameters:
        df (DataFrame): The DataFrame to check.
        required_columns (list): List of required column names.

    Raises:
        KeyError: If any required column is missing.
    """
    missing_columns = [col for col in required_columns if col not in df.columns]
    if missing_columns:
        raise KeyError(f"The following required columns are missing from the DataFrame: {missing_columns}")
    print("All required columns are present.\n")

# -------------------------
# Step 4: Data Preparation
# -------------------------
def prepare_data(df):
    """
    Prepares the data by combining 'Date' and 'Kick_Off_(local)' into a single datetime column,
    assigning game numbers, handling missing values, and sorting the matches chronologically.

    Parameters:
        df (DataFrame): The raw DataFrame.

    Returns:
        df (DataFrame): The cleaned and prepared DataFrame.
    """
    # Convert 'Date' to datetime
    df['Date'] = pd.to_datetime(df['Date'], dayfirst=True, errors='coerce')

    # Convert 'Kick_Off_(local)' to time
    unique_kick_off = df['Kick_Off_(local)'].unique()
    print("Unique 'Kick_Off_(local)' values (first 10):")
    print(unique_kick_off[:10], "\n")

    # Define a function to parse time with multiple formats
    def parse_time(time_str):
        if pd.isnull(time_str):
            return None
        time_str = str(time_str).strip()
        for fmt in ("%H:%M", "%H:%M:%S", "%I:%M %p", "%H.%M"):
            try:
                return datetime.strptime(time_str, fmt).time()
            except ValueError:
                continue
        # Handle cases like '14h30' or '14:30 Uhr'
        cleaned_time = re.sub(r'[^0-9:.\sAPMapm]', '', time_str)
        for fmt in ("%H:%M", "%H:%M:%S", "%I:%M %p", "%H.%M"):
            try:
                return datetime.strptime(cleaned_time, fmt).time()
            except ValueError:
                continue
        return None  # If none of the formats match

    # Apply the parsing function
    df['Kick_Off_(local)_parsed'] = df['Kick_Off_(local)'].apply(parse_time)

    # Debugging: print the first few parsed times
    print("Sample parsed 'Kick_Off_(local)' times:")
    print(df['Kick_Off_(local)_parsed'].head(10), "\n")

    # Combine 'Date' and 'Kick_Off_(local)_parsed' into 'DateTime'
    df['DateTime'] = df.apply(
        lambda row: datetime.combine(row['Date'], row['Kick_Off_(local)_parsed'])
        if pd.notnull(row['Date']) and pd.notnull(row['Kick_Off_(local)_parsed'])
        else pd.NaT, axis=1
    )

    # Debugging: print some 'DateTime' entries
    print("Sample 'DateTime' after combining:")
    print(df['DateTime'].head(10), "\n")

    # Count the number of valid DateTimes
    valid_datetimes = df['DateTime'].notna().sum()
    print(f"Number of valid 'DateTime' entries: {valid_datetimes} out of {df.shape[0]} total rows.\n")

    # Drop rows with invalid DateTime
    initial_row_count = df.shape[0]
    df = df.dropna(subset=['DateTime']).reset_index(drop=True)
    final_row_count = df.shape[0]
    print(f"Dropped {initial_row_count - final_row_count} rows due to invalid 'DateTime'.\n")

    # Sort matches chronologically
    df = df.sort_values('DateTime').reset_index(drop=True)

    # Assign Game_Number based on sorted order
    df['Game_Number'] = df.index + 1
    print("Assigned Game_Number to each match.\n")

    # Drop rows with missing critical information
    critical_columns = ['Home_Team', 'Away_Team', 'Home_Score', 'Away_Score', 'Home_Odds', 'Away_Odds']
    df = df.dropna(subset=critical_columns)

    print(f"Data preparation completed. Total matches: {df.shape[0]}\n")
    return df
. FROM THE ABOVE, WHAT FACTORS COULD BE IMPORTANT IN THE PREDICTION OF THE OUTCOME OF AN AFL GAME. DO THIS BAESD OF WHATS SPORTS BETTING COMPANIES DO, AND LIST ALL THE RELEVANT FACTORS THAT COULD INFLUENCE A GAME AND THAT WE COULD GET FROM THE ABOVE DATA.