import pandas as pd
import openml
import warnings
warnings.filterwarnings('ignore')

# Beginner Example 1: Load order transactions (Olist e-commerce dataset)
order_url = 'https://raw.githubusercontent.com/olist/work-at-olist-data/master/datasets/olist_orders_dataset.csv'
orders = pd.read_csv(order_url)
orders['order_purchase_timestamp'] = pd.to_datetime(orders['order_purchase_timestamp'])
orders['order_delivered_customer_date'] = pd.to_datetime(orders['order_delivered_customer_date'])
print(orders.shape)
print(orders.head(2))

# Beginner Example 2: Load real inventory/stock (UCI Online Retail dataset)
retail_url = 'https://archive.ics.uci.edu/ml/machine-learning-databases/00502/online_retail_II.xlsx'
inventory = pd.read_excel(retail_url, sheet_name='Year 2010-2011')
inventory['InvoiceDate'] = pd.to_datetime(inventory['InvoiceDate'])
print(inventory.shape)
print(inventory.head(2))

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

# Intermediate Example 1: Preparation for joins - align key fields
print('Order table columns:', orders.columns.tolist())
print('Inventory table columns:', inventory.columns.tolist())
print('Supplier table columns:', supplier_df.columns.tolist())

# Intermediate Example 2: Harmonize field names for joining product IDs
inventory.rename(columns={'StockCode': 'product_id'}, inplace=True)
if 'product_id' not in orders.columns:
    orders['product_id'] = ''  # For illustration; usually we would map using another lookup
print('Renamed columns ready for joining')

# Intermediate Example 3: Merge orders with inventory data on product_id (outer join)
merged = pd.merge(orders, inventory, on='product_id', how='outer', suffixes=('_order', '_inv'))
print('Joined table shape:', merged.shape)
print(merged[['order_id','product_id','Quantity','order_purchase_timestamp','InvoiceDate']].head(2))

# Intermediate Example 4: Joining inventory with supplier data on department or division
if 'department_name' in supplier_df.columns and 'Description' in inventory.columns:
    inventory['department_name'] = inventory['Description'].str.extract(r'(\w+)', expand=False)
    merged_supplier_inventory = pd.merge(inventory, supplier_df, on='department_name', how='left')
    print('Join shape:', merged_supplier_inventory.shape)
else:
    merged_supplier_inventory = None
    print('Could not join; missing matching fields.')

# Intermediate Example 5: Find stockouts: products with orders but no inventory record
stockouts = merged[merged['Quantity'].isna()]
print('Stockouts detected:', stockouts.shape[0])
print(stockouts[['order_id', 'product_id', 'order_purchase_timestamp']].head(2))

# Intermediate Example 6: Calculate average supplier pay as KPI for department
if merged_supplier_inventory is not None:
    avg_pay = merged_supplier_inventory.groupby('department_name')['current_annual_salary'].mean().reset_index()
    print(avg_pay.head(3))
else:
    print('Could not compute KPI: missing join result.')

# Advanced Example 1: Multi-table join for advanced analysis
multi = pd.merge(orders, inventory, on='product_id', how='left', suffixes=('_order', '_inv'))
if 'department_name' in supplier_df.columns:
    result = pd.merge(multi, supplier_df, on='department_name', how='left')
    print('Joined orders, inventory, and suppliers:', result.shape)
else:
    result = multi
    print('Could not add supplier data (no department_name field)')

# Advanced Example 2: Pivot to count orders per supplier per month
if 'full_name' in supplier_df.columns and 'order_purchase_timestamp' in orders.columns:
    result['month'] = result['order_purchase_timestamp'].dt.to_period('M')
    orders_by_sup = result.groupby(['full_name','month']).size().reset_index(name='num_orders')
    print(orders_by_sup.head(5))
else:
    print('Required supplier or date fields missing.')

# Advanced Example 3: Calculate lead times and flag excessive values
if 'order_purchase_timestamp' in result.columns and 'order_delivered_customer_date' in result.columns:
    result['lead_time'] = (result['order_delivered_customer_date'] - result['order_purchase_timestamp']).dt.days
    slow_orders = result[result['lead_time'] > 15]
    print('Orders with long lead time:', slow_orders.shape[0])
    print(slow_orders[['order_id','full_name','lead_time']].head(2))
else:
    print('Date fields for lead time unavailable.')

# Error Handling Example 1: Detect orders missing delivery dates
missing_delivery = orders[orders['order_delivered_customer_date'].isna()]
print('Orders missing delivery date:', missing_delivery.shape[0])
print(missing_delivery[['order_id','order_purchase_timestamp']].head(2))

# Error Handling Example 2: Attempt join with mismatched product_id and show errors
try:
    bad_join = pd.merge(orders, inventory, on='wrong_field')
except Exception as e:
    print('Join error:', e)

# Error Handling Example 3: Check for negative or unrealistic quantities
bad_quantity = inventory[inventory['Quantity'] < 0]
print('Negative quantity entries:', bad_quantity.shape[0])
if bad_quantity.shape[0] > 0:
    print(bad_quantity[['product_id','Quantity','Description']].head(3))

# Aggregation example: Compute fill rate (orders with matched inventory / total orders)
filled = merged[~merged['Quantity'].isna()]
fill_rate = len(filled) / len(orders)
print(f'Fill rate: {fill_rate:.2%}')

# Time series grouping: Average delivery lead time per month
if 'order_purchase_timestamp' in orders.columns and 'order_delivered_customer_date' in orders.columns:
    orders['lead_time'] = (orders['order_delivered_customer_date'] - orders['order_purchase_timestamp']).dt.days
    orders['month'] = orders['order_purchase_timestamp'].dt.to_period('M')
    avg_lead = orders.groupby('month')['lead_time'].mean().reset_index()
    print(avg_lead.tail(6))
else:
    print('Date columns missing, cannot calculate lead time.')

# Complete end-to-end: Identify top 5 suppliers responsible for most stockouts
if result is not None and 'full_name' in result.columns and 'Quantity' in result.columns:
    stockout_orders = result[result['Quantity'].isna()]
    top_suppliers = stockout_orders['full_name'].value_counts().head(5)
    print('Top 5 suppliers by count of stockouts:')
    print(top_suppliers)
else:
    print('Could not calculate: missing required columns.')