import pandas as pd
from datetime import datetime
import locale

file_path = 'bages rooster voor s.xlsx'
sheet_name = 'rooster'

df = pd.read_excel(file_path, sheet_name=sheet_name)

date_ranges = {
    (pd.Timestamp('2025-01-06'), pd.Timestamp('2025-01-12')): 16,
    (pd.Timestamp('2025-01-13'), pd.Timestamp('2025-01-19')): 17,
    (pd.Timestamp('2025-01-20'), pd.Timestamp('2025-01-26')): 18,
    (pd.Timestamp('2025-01-27'), pd.Timestamp('2025-02-02')): 19,

    # add more ranges as needed
}


# Custom date parser function
def parse_custom_date(date_str):
    if pd.isna(date_str):
        return pd.NaT  # Return pandas NaT for missing dates
    if isinstance(date_str, str):
        try:
            # Set locale to Dutch
            locale.setlocale(locale.LC_TIME, 'nl_NL.UTF-8')
            return datetime.strptime(date_str, '%A %d %B %Y')
        except ValueError as e:
            raise ValueError(f"Date conversion error: {e} for date string: {date_str}")
        finally:
            # Reset locale to the default setting
            locale.setlocale(locale.LC_TIME, 'C')
    else:
        raise TypeError(f"Expected string, got {type(date_str).__name__}: {date_str}")


# Ensure the column 'Datum S+' exists and is processed correctly
if 'Datum S+' in df.columns:
    try:
        # Convert 'Datum S+' column to datetime using the custom parser
        df['Datum S+'] = df['Datum S+'].apply(parse_custom_date)
    except (ValueError, TypeError) as e:
        print(f"Error: {e}")
        # Optionally, re-raise the exception if you want to stop execution
        raise


    # Function to update Lesweek based on date ranges
    def update_lesweek(date):
        if pd.isna(date):  # Handle NaT values
            return 0
        for date_range, lesweek_value in date_ranges.items():
            if date_range[0] <= date <= date_range[1]:
                return lesweek_value
        return 0  # Default value if date doesn't fall in any range


    # Apply the function to 'Datum S+' column
    df['Lesweek'] = df['Datum S+'].apply(update_lesweek)

# Check the results
print("\nFirst few rows of the DataFrame to verify date formatting:\n", df.head())

# If needed, you can save the DataFrame to a new Excel file to verify changes
df.to_excel('updated_rooster.xlsx', index=False)