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

# Example 1: Load retail demand data
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))

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

# Example 3: Load order fulfillment data
olist_url = 'https://raw.githubusercontent.com/olist/work-at-olist-data/master/datasets/olist_orders_dataset.csv'
df_orders = pd.read_csv(olist_url)
df_orders['order_purchase_timestamp'] = pd.to_datetime(df_orders['order_purchase_timestamp'])
df_orders['order_delivered_customer_date'] = pd.to_datetime(df_orders['order_delivered_customer_date'])
print(df_orders.shape)
print(df_orders.head(3))

# Beginner Example 1: Write a function to calculate total demand for an item
def total_demand(df, stockcode):
    total = df[df['StockCode'] == stockcode]['Quantity'].sum()
    return total
 
sample_item = df['StockCode'].iloc[0]
print('Total demand for item', sample_item, ':', total_demand(df, sample_item))

# Beginner Example 2: Function for average daily demand
def average_daily_demand(df, stockcode):
    sub = df[df['StockCode'] == stockcode]
    daily = sub.groupby(sub['InvoiceDate'].dt.date)['Quantity'].sum()
    return daily.mean()
 
print('Average daily demand for item', sample_item, ':', average_daily_demand(df, sample_item))

# Beginner Example 3: Find number of unique customers for a product
def unique_customers(df, stockcode):
    sub = df[df['StockCode'] == stockcode]
    return sub['Customer ID'].nunique()
 
print('Unique customers for item', sample_item, ':', unique_customers(df, sample_item))

# Intermediate Example 1: Function to calculate inventory turnover rate
def inventory_turnover(df, stockcode, inventory_level):
    sales = total_demand(df, stockcode)
    if inventory_level == 0:
        return np.nan
    return sales / inventory_level
 
# Assume starting stock for this item is 100
print('Inventory turnover rate:', inventory_turnover(df, sample_item, 100))

# Intermediate Example 2: Function for supplier on-time delivery rate
def on_time_delivery_rate(df):
    if 'order_purchase_timestamp' not in df.columns or 'order_delivered_customer_date' not in df.columns:
        return np.nan
    delivered = df.dropna(subset=['order_delivered_customer_date'])
    estimated = df.dropna(subset=['order_estimated_delivery_date'])
    if delivered.empty or estimated.empty:
        return np.nan
    on_time = (delivered['order_delivered_customer_date'] <= delivered['order_estimated_delivery_date']).mean()
    return on_time
 
rate = on_time_delivery_rate(df_orders)
print(f'On-time delivery rate: {rate:.2%}')

# Intermediate Example 3: Calculate supplier fill rate using order and delivery quantity
def fill_rate(df, ordered_col, delivered_col):
    if df[ordered_col].sum() == 0:
        return np.nan
    return df[delivered_col].sum() / df[ordered_col].sum()
 
# This is a demo with retail demand as delivered and using a hypothetical ordered column
df['OrderedQty'] = df['Quantity'].abs() + np.random.randint(0, 5, len(df))
print('Fill rate:', fill_rate(df, 'OrderedQty', 'Quantity'))

# Advanced Example 1: Create a function for moving average demand (time series)
def moving_average_demand(df, stockcode, window=7):
    sub = df[df['StockCode'] == stockcode].copy()
    sub['Date'] = sub['InvoiceDate'].dt.date
    daily = sub.groupby('Date')['Quantity'].sum().sort_index()
    ma = daily.rolling(window=window, min_periods=1).mean()
    return ma
 
ma = moving_average_demand(df, sample_item, window=7)
print(ma.tail(7))

# Advanced Example 2: Calculate lead time for orders using order purchase and delivery date
def lead_time_days(df):
    df = df.dropna(subset=['order_purchase_timestamp', 'order_delivered_customer_date'])
    if df.empty:
        return np.nan
    return (df['order_delivered_customer_date'] - df['order_purchase_timestamp']).dt.days.mean()
 
avg_lead = lead_time_days(df_orders)
print(f'Average lead time (days): {avg_lead:.2f}')

# Advanced Example 3: Write a multi-metric order analytics function
def order_kpis(df):
    valid = df.dropna(subset=['order_purchase_timestamp', 'order_delivered_customer_date'])
    total_orders = len(valid)
    if total_orders == 0:
        return {}
    on_time = (valid['order_delivered_customer_date'] <= valid['order_estimated_delivery_date']).sum()
    mean_lead_time = (valid['order_delivered_customer_date'] - valid['order_purchase_timestamp']).dt.days.mean()
    return {
        'total_orders': total_orders,
        'on_time_rate': on_time / total_orders,
        'average_lead_time': mean_lead_time
    }
 
kpis = order_kpis(df_orders)
print('Order KPIs:', kpis)

# Error Example 1: Handling missing invoice dates in demand calculations
df_missing_date = df.copy()
df_missing_date.loc[0:4, 'InvoiceDate'] = pd.NaT
try:
    result = average_daily_demand(df_missing_date, sample_item)
except Exception as e:
    print('Error:', str(e))

# Error Example 2: Common pitfall - misinterpreting order join keys
wrong_merge = pd.merge(df, df_orders, left_on='Invoice', right_on='order_id', how='inner')
print('Merged shape:', wrong_merge.shape)

# Error Example 3: Wrong interpretation of negative or zero quantities
df_neg = df.copy()
df_neg.loc[0:2, 'Quantity'] = 0
print('Total demand for item with 0 quantities:', total_demand(df_neg, sample_item))

# KPI Example: Function for group-by month item sales
def monthly_item_sales(df, stockcode):
    mask = df['StockCode'] == stockcode
    by_month = df[mask].groupby(df['InvoiceDate'].dt.to_period('M'))['Quantity'].sum()
    return by_month
 
print(monthly_item_sales(df, sample_item).head(6))

# KPI Example: Top-N best-selling items function
def top_n_products(df, n=5):
    grouped = df.groupby('StockCode')['Quantity'].sum()
    return grouped.sort_values(ascending=False).head(n)
 
print('Top 5 best-selling items:')
print(top_n_products(df, n=5))

# End-to-end: Track monthly demand trend and identify reorder points for a key item
def reorder_signal(daily_qty, reorder_level):
    below = daily_qty[daily_qty < reorder_level]
    return below.index.astype(str).tolist()
 
item_monthly = monthly_item_sales(df, sample_item)
reorder_level = 10
reorder_dates = reorder_signal(item_monthly, reorder_level)
print('Reorder signals for item', sample_item, 'on months:', reorder_dates)