import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import warnings
warnings.filterwarnings('ignore')

# Load data: UCI Online Retail II dataset
url = 'https://archive.ics.uci.edu/ml/machine-learning-databases/00502/online_retail_II.xlsx'
df = pd.read_excel(url, sheet_name='Year 2010-2011')
df['InvoiceDate'] = pd.to_datetime(df['InvoiceDate'])
print(df.shape)
print(df.head(3))

# Aggregate by week and item for forecasting example
df['week'] = df['InvoiceDate'].dt.to_period('W').apply(lambda r: r.start_time)
weekly = df.groupby(['week', 'StockCode'])['Quantity'].sum().reset_index()
weekly.rename(columns={'Quantity': 'actual_demand'}, inplace=True)
print(weekly.head())

# Simulate a naive forecast: previous week's sales-> forecast for next week
weekly['forecast'] = weekly.groupby('StockCode')['actual_demand'].shift(1)
print(weekly.head(10))

# Compute forecast error
weekly['error'] = weekly['actual_demand'] - weekly['forecast']
print(weekly[['week','StockCode','actual_demand','forecast','error']].head(10))

# Plot errors for an example item
item = weekly['StockCode'].unique()[0]
item_data = weekly[weekly['StockCode']==item]
plt.figure(figsize=(10,4))
plt.plot(item_data['week'], item_data['error'], marker='o')
plt.title(f'Forecast Error for Item {item}')
plt.xlabel('Week')
plt.ylabel('Error (Actual - Forecast)')
plt.axhline(0, color='gray', linestyle='--')
plt.tight_layout()
plt.show()

# Calculate MAE for each item
mae = weekly.dropna().groupby('StockCode').apply(lambda g: np.mean(np.abs(g['error']))).reset_index(name='MAE')
print(mae.head())

# Calculate MAPE for each item, avoiding division by zero
def mape(y_true, y_pred):
    non_zero = y_true != 0
    return np.mean(np.abs((y_true[non_zero] - y_pred[non_zero]) / y_true[non_zero])) * 100
mapes = weekly.dropna().groupby('StockCode').apply(lambda g: mape(g['actual_demand'].values, g['forecast'].values)).reset_index(name='MAPE')
print(mapes.head())

# Calculate mean forecast bias for each item
bias = weekly.dropna().groupby('StockCode').apply(lambda g: np.mean(g['error'])).reset_index(name='Bias')
print(bias.head())

# Compute rolling 6-week MAE for a specific item
item_eg = weekly['StockCode'].unique()[0]
item_data = weekly[weekly['StockCode']==item_eg].dropna()
item_data = item_data.sort_values('week')
item_data['MAE_6w'] = item_data['error'].abs().rolling(window=6).mean()
plt.figure(figsize=(10,4))
plt.plot(item_data['week'], item_data['MAE_6w'], label='6-week MAE')
plt.title(f'Rolling 6-week MAE for Item {item_eg}')
plt.xlabel('Week')
plt.ylabel('MAE')
plt.legend()
plt.tight_layout()
plt.show()

# Plot rolling MAE for 5 random SKUs
np.random.seed(42)
sample_items = np.random.choice(weekly['StockCode'].unique(), 5, replace=False)
plt.figure(figsize=(12,6))
for code in sample_items:
    itemd = weekly[weekly['StockCode']==code].dropna().sort_values('week')
    itemd['MAE_6w'] = itemd['error'].abs().rolling(window=6).mean()
    plt.plot(itemd['week'], itemd['MAE_6w'], label=f'SKU {code}')
plt.title('Rolling 6-week MAE for 5 Sample SKUs')
plt.xlabel('Week')
plt.ylabel('6-week MAE')
plt.legend()
plt.tight_layout()
plt.show()

# Identify potential error outliers
weekly['abs_error'] = weekly['error'].abs()
threshold = weekly['abs_error'].mean() + 3*weekly['abs_error'].std()
outliers = weekly[weekly['abs_error'] > threshold]
print(f'Total outlier weeks: {outliers.shape[0]}')
print(outliers[['week','StockCode','actual_demand','forecast','error']].head())

# Ensure each item has all weeks (fill missing periods with 0 actuals)
all_weeks = pd.date_range(weekly['week'].min(), weekly['week'].max(), freq='W')
all_items = weekly['StockCode'].unique()
index = pd.MultiIndex.from_product([all_weeks, all_items], names=['week','StockCode'])
full = weekly.set_index(['week','StockCode']).reindex(index, fill_value=0).reset_index()
print(f'Filled data shape: {full.shape}')
print(full.head())

# Example of misaligned forecasts: shifting in the wrong direction
full['misaligned_forecast'] = full.groupby('StockCode')['actual_demand'].shift(-1)
full['misaligned_error'] = full['actual_demand'] - full['misaligned_forecast']
print(full[['week','StockCode','actual_demand','misaligned_forecast','misaligned_error']].head(10))

# Show how dividing by zero gives problems in MAPE
zeros = full[full['actual_demand']==0]
print(f'Sample weeks with zero demand: {len(zeros)}')
if not zeros.empty:
    print(zeros[['week','StockCode','actual_demand','forecast']].head(3))

# Example: Aggregate MAE by country (if available)
if 'Country' in df.columns:
    merged = (weekly.merge(df[['StockCode','Country']].drop_duplicates(), on='StockCode', how='left'))
    country_mae = merged.dropna().groupby('Country')['error'].apply(lambda x: np.mean(np.abs(x))).reset_index(name='MAE')
    print(country_mae.sort_values('MAE', ascending=False).head())

# Example: Group at monthly instead of weekly level
weekly['month'] = pd.to_datetime(weekly['week']).dt.to_period('M').astype(str)
monthly = weekly.groupby(['month','StockCode'])[['actual_demand','forecast']].sum().reset_index()
monthly['error'] = monthly['actual_demand'] - monthly['forecast']
print(monthly.head())

# End-to-end: Flagging SKUs with rising MAE
result = []
for code in weekly['StockCode'].unique()[:100]: # limit to 100 for speed
    sku = weekly[weekly['StockCode']==code].dropna().sort_values('week')
    if len(sku) < 18: continue
    sku['MAE_8w'] = sku['error'].abs().rolling(window=8).mean()
    last10 = sku['MAE_8w'].iloc[-10:]
    if len(last10)==10 and last10.iloc[-1] > 1.5*last10.iloc[0]:
        result.append({'StockCode': code, 'Start_MAE_8w': last10.iloc[0], 'End_MAE_8w': last10.iloc[-1]})
df_flagged = pd.DataFrame(result)
print(df_flagged.head())