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

# Beginner Example 1: Load retail demand dataset
url = 'https://archive.ics.uci.edu/ml/machine-learning-databases/00502/online_retail_II.xlsx'
retail_df = pd.read_excel(url, sheet_name='Year 2010-2011')
retail_df['InvoiceDate'] = pd.to_datetime(retail_df['InvoiceDate'])
print(retail_df.shape)
print(retail_df.head(3))

# Beginner Example 2: Load supplier performance dataset
supplier_dataset = openml.datasets.get_dataset(42125)
supplier_df, _, _, _ = supplier_dataset.get_data(dataset_format='dataframe')
print(supplier_df.shape)
print(supplier_df.head(3))

# Beginner Example 3: Load order fulfillment data
order_url = 'https://raw.githubusercontent.com/olist/work-at-olist-data/master/datasets/olist_orders_dataset.csv'
orders_df = pd.read_csv(order_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'])
print(orders_df.shape)
print(orders_df.head(3))

# Intermediate Example 1: Detect missing deliveries
missing_deliveries = orders_df['order_delivered_customer_date'].isnull().sum()
print(f'Missing delivered customer dates: {missing_deliveries}')

# Intermediate Example 2: Summarize supplier on-time delivery
if 'on_time_delivery' in supplier_df.columns:
    performance = supplier_df['on_time_delivery'].value_counts(normalize=True)
    print(performance)
else:
    print('on_time_delivery column not present in supplier data.')

# Intermediate Example 3: Aggregate retail sales by day
retail_daily = retail_df.groupby(retail_df['InvoiceDate'].dt.date)['Quantity'].sum()
print(retail_daily.head())

# Intermediate Example 4: Plot order lead time distribution
orders_df['order_lead_time'] = (orders_df['order_delivered_customer_date'] - orders_df['order_purchase_timestamp']).dt.days
plt.figure(figsize=(8,4))
sns.histplot(orders_df['order_lead_time'].dropna(), bins=30, kde=True)
plt.title('Distribution of Order Lead Times (Days)')
plt.xlabel('Lead Time (days)')
plt.ylabel('Order Count')
plt.show()

# Intermediate Example 5: Simulate join between orders and suppliers
if 'SupplierID' in orders_df.columns and 'SupplierID' in supplier_df.columns:
    merged_df = orders_df.merge(supplier_df, on='SupplierID', how='left')
    print(merged_df[['order_id', 'SupplierID']].head())
else:
    print('SupplierID not present in both dataframes - join cannot be performed.')

# Advanced Example 1: Calculate moving average demand (7-day window)
retail_daily = retail_daily.sort_index()
ma_7d = retail_daily.rolling(window=7, min_periods=1).mean()
plt.figure(figsize=(10,4))
plt.plot(retail_daily, label='Daily Demand')
plt.plot(ma_7d, label='7-Day Moving Average', linewidth=2)
plt.legend()
plt.xlabel('Date')
plt.ylabel('Units Sold')
plt.title('Retail Demand and 7-Day Moving Average')
plt.show()

# Advanced Example 2: Supplier performance by region
if 'Region' in supplier_df.columns and 'on_time_delivery' in supplier_df.columns:
    regional = supplier_df.groupby('Region')['on_time_delivery'].mean()
    print(regional)
else:
    print('Required columns not available for region-based supplier performance.')

# Advanced Example 3: Identify possible outliers in lead time
lead_time_mean = orders_df['order_lead_time'].mean()
lead_time_std = orders_df['order_lead_time'].std()
outlier_mask = (orders_df['order_lead_time'] > lead_time_mean + 3 * lead_time_std)
outliers = orders_df[outlier_mask]
print(f'Number of lead time outliers: {outliers.shape[0]}')
print(outliers[['order_id', 'order_lead_time']].head())

# Error Handling: Check for missing InvoiceDate in retail data
missing_dates = retail_df['InvoiceDate'].isnull().sum()
print(f'Missing InvoiceDate entries: {missing_dates}')

# Error Handling: Test for duplicate Order IDs before join
duplicate_orders = orders_df['order_id'].duplicated().sum()
print(f'Duplicate order IDs in orders_df: {duplicate_orders}')

# Error Handling: Negative quantities in retail data
neg_qty = retail_df[retail_df['Quantity'] < 0]
print(f'Negative quantity records: {neg_qty.shape[0]}')

# KPI: Calculate average order lead time
avg_lead_time = orders_df['order_lead_time'].mean()
print(f'Average order lead time (days): {avg_lead_time:.2f}')

# KPI: Rolling weekly order count
orders_daily_count = orders_df.groupby(orders_df['order_purchase_timestamp'].dt.date)['order_id'].count()
orders_rolling = orders_daily_count.rolling(window=7, min_periods=1).sum()
plt.figure(figsize=(10, 4))
plt.plot(orders_daily_count, label='Daily Orders')
plt.plot(orders_rolling, label='7-Day Rolling Orders', linewidth=2)
plt.title('Order Volume (Daily and 7-Day Rolling)')
plt.xlabel('Date')
plt.ylabel('Order Count')
plt.legend()
plt.show()

# Best Practice: Supplier groups by delivery performance
if 'on_time_delivery' in supplier_df.columns:
    counts = supplier_df['on_time_delivery'].value_counts()
    print(counts)
else:
    print('on_time_delivery field not present in supplier_df.')

# Time series pattern: Weekday vs weekend sales
retail_df['weekday'] = retail_df['InvoiceDate'].dt.dayofweek
weekday_sales = retail_df.groupby('weekday')['Quantity'].sum()
print(weekday_sales)

# End-to-end: Aggregate daily retail demand, join with supplier, calculate KPI (if possible)
proj_results = None
if 'SupplierID' in retail_df.columns and 'SupplierID' in supplier_df.columns:
    merged_proj = retail_df.merge(supplier_df, on='SupplierID', how='left')
    merged_proj['InvoiceDate'] = pd.to_datetime(merged_proj['InvoiceDate'])
    daily_kpi = merged_proj.groupby(merged_proj['InvoiceDate'].dt.date).agg({'Quantity': 'sum', 'on_time_delivery': 'mean'})
    proj_results = daily_kpi
    print(daily_kpi.head(7))
else:
    print('No SupplierID key for join in retail or supplier dataset - cannot calculate combined KPI.')