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

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

# Example 2: Load supplier performance data (OpenML Supply Chain)
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 e-commerce orders (Olist Order Fulfillment Dataset)
url = 'https://raw.githubusercontent.com/olist/work-at-olist-data/master/datasets/olist_orders_dataset.csv'
df_orders = pd.read_csv(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))

# Example 4: Count unique customers who made purchases
unique_customers = df_retail['Customer ID'].nunique()
print(f'Total unique customers: {unique_customers}')

# Example 5: Calculate basic supplier salary summary
supplier_salary_mean = df_supplier['current_annual_salary'].mean()
supplier_salary_max = df_supplier['current_annual_salary'].max()
print(f'Average supplier salary: {supplier_salary_mean:.2f}')
print(f'Highest supplier salary: {supplier_salary_max:.2f}')

# Example 6: Compute order delivery time for all fulfilled orders
mask_delivered = df_orders['order_delivered_customer_date'].notnull()
df_delivered = df_orders[mask_delivered].copy()
df_delivered['delivery_days'] = (df_delivered['order_delivered_customer_date'] - df_delivered['order_purchase_timestamp']).dt.days
print(df_delivered[['order_id', 'order_status', 'delivery_days']].head(5))

# Example 7: Analyze order status distribution
order_status_counts = df_orders['order_status'].value_counts()
print(order_status_counts)

# Example 8: Time series - group retail sales by month
monthly_sales = df_retail.set_index('InvoiceDate').resample('M')['Quantity'].sum()
print(monthly_sales.head(6))

# Example 9: Filter top supplier divisions by staff count
top_divisions = df_supplier['division'].value_counts().head(5)
print(top_divisions)

# Example 10: Detect potential duplicate retail invoices
dupes = df_retail['Invoice'].duplicated().sum()
print(f'Duplicate invoice count: {dupes}')

# Example 11: Calculate late deliveries in Olist orders
late_orders = df_delivered[df_delivered['order_delivered_customer_date'] > df_delivered['order_estimated_delivery_date']]
late_pct = len(late_orders) / len(df_delivered) * 100
print(f'Late deliveries: {len(late_orders)} ({late_pct:.2f}%)')

# Example 12: Remove canceled orders for efficiency analysis
active_orders = df_delivered[df_delivered['order_status'] != 'canceled'].copy()
print(f'Active orders (not canceled): {active_orders.shape[0]}')

# Example 13: Export delivery performance KPI to CSV for reporting
delivery_kpi = df_delivered.groupby('order_status').agg(avg_days=('delivery_days', 'mean'), count=('order_id', 'size')).reset_index()
delivery_kpi.to_csv('delivery_performance.csv', index=False)
print(delivery_kpi.head())

# Example 14: Robust missing data handling for operations pipeline
missing_invoices = df_retail['Invoice'].isnull().sum()
missing_dates = df_retail['InvoiceDate'].isnull().sum()
print(f'Missing invoice IDs: {missing_invoices}')
print(f'Missing invoice dates: {missing_dates}')

# Example 15: Saving supplier division summary for downstream automation
div_summary = df_supplier.groupby('division').agg(staff_count=('full_name', 'nunique')).reset_index()
div_summary.to_csv('supplier_division_summary.csv', index=False)
print(div_summary.head())

# Example 16: Parameterize time window for demand analysis (for automation)
def sales_in_window(df, start, end):
    mask = (df['InvoiceDate'] >= start) & (df['InvoiceDate'] < end)
    return df.loc[mask, 'Quantity'].sum()
window_total = sales_in_window(df_retail, pd.Timestamp('2010-12-01'), pd.Timestamp('2010-12-31'))
print(f'Sales units Dec 2010: {window_total}')

# Example 17: Handling missing delivery dates in orders
missing_delivery = df_orders['order_delivered_customer_date'].isnull().sum()
print(f'Orders with missing delivery date: {missing_delivery}')

# Example 18: Debugging illogical delivery times
negative_delivery = (df_delivered['delivery_days'] < 0).sum()
print(f'Orders with negative delivery days: {negative_delivery}')

# Example 19: Catching incorrect data merges (join on wrong key)
df_tmp = df_orders.merge(df_retail, left_on='order_id', right_on='Invoice', how='inner')
print('Joined shape:', df_tmp.shape)

# Example 20: Handling misinterpreted lead time (numeric vs. string)
test_leadtime = pd.Series(['5', '10', '3', '8'])
numeric_leadtime = pd.to_numeric(test_leadtime, errors='coerce')
print(numeric_leadtime)

# End-to-end: Find monthly late delivery percentage for Olist
monthly_late = df_delivered.copy()
monthly_late['late'] = monthly_late['order_delivered_customer_date'] > monthly_late['order_estimated_delivery_date']
monthly_group = monthly_late.set_index('order_purchase_timestamp').resample('M')['late'].mean().reset_index()
monthly_group['late_pct'] = monthly_group['late'] * 100
print(monthly_group[['order_purchase_timestamp', 'late_pct']].head(6))