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

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))

print('Unique product codes:', df['StockCode'].nunique())
print('Number of countries:', df['Country'].nunique())

df.info()

df = df[df['Quantity'] > 0]
df = df[df['Invoice'].apply(lambda x: not str(x).startswith('C'))]
print('Shape after removing returns:', df.shape)

# Compute total sales per product
df['TotalSales'] = df['Quantity'] * df['Price']
sales_per_item = df.groupby('StockCode')['TotalSales'].sum().sort_values(ascending=False)
print(sales_per_item.head(10))

# Calculate cumulative sales percentage
total_sales = sales_per_item.sum()
cum_sales = sales_per_item.cumsum()
cum_perc = cum_sales / total_sales
sales_abc = pd.DataFrame({'TotalSales': sales_per_item, 'CumPerc': cum_perc})
print(sales_abc.head(10))

# Assign ABC category
def get_abc_class(cum_perc):
    if cum_perc <= 0.8:
        return 'A'
    elif cum_perc <= 0.95:
        return 'B'
    else:
        return 'C'
sales_abc['ABC_Class'] = sales_abc['CumPerc'].apply(get_abc_class)
print(sales_abc['ABC_Class'].value_counts())

# Add ABC back to original cleaned product master
abc_master = sales_abc.reset_index()
abc_master = abc_master[['StockCode', 'ABC_Class']]
df = df.merge(abc_master, on='StockCode', how='left')
print(df[['StockCode', 'ABC_Class']].drop_duplicates().head(10))

# Example: Analyze sales by ABC class for a selected country
selected_country = 'United Kingdom'
country_sales = df[df['Country'] == selected_country].groupby('ABC_Class')['TotalSales'].sum()
print(country_sales)

# Beginner: Count distinct products per ABC class
distinct_counts = df[['StockCode', 'ABC_Class']].drop_duplicates().groupby('ABC_Class').count()
print(distinct_counts.rename(columns={'StockCode':'ProductCount'}))

# Beginner: Average order size for each ABC class
avg_order_size = df.groupby('ABC_Class')['Quantity'].mean()
print(avg_order_size)

# Intermediate: Monthly sales by ABC class
df['YearMonth'] = df['InvoiceDate'].dt.to_period('M')
monthly_sales = df.groupby(['YearMonth', 'ABC_Class'])['TotalSales'].sum().unstack()
print(monthly_sales.tail(6))

# Intermediate: Plot cumulative distribution for ABC analysis
import matplotlib.pyplot as plt
plt.figure(figsize=(8,5))
plt.plot(sales_abc['CumPerc'].values, marker='o')
plt.xlabel('Ranked Products')
plt.ylabel('Cumulative Sales %')
plt.title('ABC Distribution Curve')
plt.grid(True)
plt.show()

# Intermediate: Which items changed ABC class between periods?
month1 = df[df['YearMonth']=='2010-12']
month2 = df[df['YearMonth']=='2011-01']
def abc_by_period(subdf):
    tp = subdf.groupby('StockCode')['TotalSales'].sum().sort_values(ascending=False)
    total = tp.sum()
    cum = tp.cumsum()/total
    return pd.DataFrame({'StockCode': tp.index, 'ABC': cum.apply(get_abc_class)})
abc1 = abc_by_period(month1).set_index('StockCode')
abc2 = abc_by_period(month2).set_index('StockCode')
abc_compare = abc1.join(abc2, lsuffix='_2010_12', rsuffix='_2011_01', how='inner')
changed = abc_compare[abc_compare['ABC_2010_12'] != abc_compare['ABC_2011_01']]
print('Number of items with class change:', len(changed))
print(changed.head())

# Advanced: Link ABC class to fulfillment time using Olist order delivery data
del_url = 'https://raw.githubusercontent.com/olist/work-at-olist-data/master/datasets/olist_orders_dataset.csv'
orders_df = pd.read_csv(del_url)
orders_df['order_purchase_timestamp'] = pd.to_datetime(orders_df['order_purchase_timestamp'])
orders_df['order_delivered_customer_date'] = pd.to_datetime(orders_df['order_delivered_customer_date'])
orders_df['delivery_leadtime'] = (orders_df['order_delivered_customer_date'] - orders_df['order_purchase_timestamp']).dt.days
print(orders_df[['order_id','delivery_leadtime']].head())

# Advanced: Export ABC classified product list for further operational planning
export_cols = ['StockCode', 'ABC_Class', 'Country']
abc_master_wide = df[export_cols].drop_duplicates()
abc_master_wide.to_csv('abc_classified_items.csv', index=False)
print('Exported to abc_classified_items.csv')

# Error example: Missing dates in InvoiceDate
df_with_missing = df.copy()
df_with_missing.loc[df_with_missing.sample(frac=0.01, random_state=42).index, 'InvoiceDate'] = pd.NaT
missing_count = df_with_missing['InvoiceDate'].isna().sum()
print(f'Rows with missing InvoiceDate: {missing_count}')
try:
    df_with_missing['YearMonth'] = df_with_missing['InvoiceDate'].dt.to_period('M')
except Exception as e:
    print('Error:', e)

# Error example: Incorrect join (merge) on product codes with spaces
df_err = df.copy()
df_err['StockCode'] = df_err['StockCode'].astype(str) + ' '
try:
    df_err = df_err.merge(abc_master, on='StockCode', how='left', suffixes=('', '_abc'))
    print('Merge successful:', True)
except Exception as e:
    print('Merge failed:', e)
missing_after_merge = df_err['ABC_Class'].isna().sum()
print('Rows with missing ABC class after incorrect join:', missing_after_merge)

# Error example: Misinterpreting lead time or item quantities
false_df = df.copy()
false_df['Quantity'] = -false_df['Quantity']
false_df['TotalSales'] = false_df['Quantity'] * false_df['Price']
negative_sales = false_df[false_df['TotalSales'] < 0].shape[0]
print(f'Negative total sales rows: {negative_sales}')

# Operational pattern: Time-based ABC class count as a simple KPI
abc_time_kpi = df.groupby(['YearMonth', 'ABC_Class'])['StockCode'].nunique().unstack()
print(abc_time_kpi.tail())

# Complete end-to-end supply chain example: Alert when A-class items are out of stock
stock_levels = df.groupby('StockCode')['Quantity'].sum()
a_class_codes = abc_master[abc_master['ABC_Class']=='A']['StockCode']
a_stock = stock_levels[a_class_codes]
low_stock = a_stock[a_stock <= 10]
print('Number of A-class items below threshold:', low_stock.shape[0])
print('A-class items at risk of stock-out:')
print(low_stock)