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

# Beginner Example 1: Load a real ecommerce order dataset
url = 'https://raw.githubusercontent.com/olist/work-at-olist-data/master/datasets/olist_orders_dataset.csv'
orders = pd.read_csv(url)
print(orders.shape)
print(orders.head(3))

# Beginner Example 2: Convert date columns to datetime type
orders['order_purchase_timestamp'] = pd.to_datetime(orders['order_purchase_timestamp'])
orders['order_delivered_customer_date'] = pd.to_datetime(orders['order_delivered_customer_date'])
orders['order_estimated_delivery_date'] = pd.to_datetime(orders['order_estimated_delivery_date'])

# Beginner Example 3: Count total delivered orders
delivered_mask = orders['order_status'] == 'delivered'
print('Delivered orders:', delivered_mask.sum())

# Intermediate Example 1: Calculate delivery time lag (days) for each order
orders['delivery_lag'] = (orders['order_delivered_customer_date'] - orders['order_purchase_timestamp']).dt.days

# Intermediate Example 2: Compare actual vs estimated delivery
orders['on_time'] = orders['order_delivered_customer_date'] <= orders['order_estimated_delivery_date']

# Intermediate Example 3: Calculate on-time delivery rate
total_delivered = delivered_mask.sum()
on_time_delivered = orders.loc[delivered_mask, 'on_time'].sum()
on_time_rate = on_time_delivered / total_delivered
print(f'On-time delivery rate: {on_time_rate:.2%}')

# Intermediate Example 4: Monthly on-time delivery trend
monthly = orders[delivered_mask].set_index('order_purchase_timestamp').resample('M')['on_time'].mean()
print(monthly.tail())

# Intermediate Example 5: Identify late deliveries
late_orders = orders[delivered_mask & (orders['on_time'] == False)]
print(late_orders[['order_id', 'delivery_lag', 'order_estimated_delivery_date']].head())

# Advanced Example 1: Calculating fulfillment rate (all orders, not just delivered)
fulfillment_rate = (orders['order_status'] == 'delivered').mean()
print(f'Overall fulfillment rate: {fulfillment_rate:.2%}')

# Advanced Example 2: Average lateness when orders are late
average_lateness = late_orders['delivery_lag'] - (late_orders['order_estimated_delivery_date'] - late_orders['order_purchase_timestamp']).dt.days
print(f'Average days late: {average_lateness.mean():.2f}')

# Advanced Example 3: Save late order analysis to CSV for team sharing
late_orders[['order_id', 'delivery_lag', 'order_estimated_delivery_date']].to_csv('late_orders_summary.csv', index=False)
print('Exported late orders summary to late_orders_summary.csv')

# Error Handling: Detect and count missing delivery dates
missing_delivery = orders['order_delivered_customer_date'].isna()
print(f'Orders missing delivery date: {missing_delivery.sum()}')

# Error Handling: Catch impossible delivery lags (negative or extreme values)
impossible_lags = orders['delivery_lag'] < 0
print('Negative delivery lags:', impossible_lags.sum())

# Error Handling: Check for mismatches in date ranges
future_dates = orders['order_delivered_customer_date'] > orders['order_estimated_delivery_date'] + pd.Timedelta(days=180)
print('Deliveries more than 6 months late:', future_dates.sum())

# Operational Pattern: Weekly fulfillment and on-time rates
orders['week'] = orders['order_purchase_timestamp'].dt.isocalendar().week
weekly_kpis = orders[delivered_mask].groupby('week').agg({'on_time': 'mean', 'order_id': 'count'})
weekly_kpis.rename(columns={'on_time':'on_time_rate','order_id':'delivered_orders'}, inplace=True)
print(weekly_kpis.tail())

# Operational Pattern: KPI calculation function
def calc_kpis(df):
    dmask = df['order_status']=='delivered'
    on_time = df.loc[dmask, 'on_time'].mean()
    fulfillment = dmask.mean()
    return on_time, fulfillment
sample_on_time, sample_fulfillment = calc_kpis(orders)
print(f'On-time: {sample_on_time:.2%} | Fulfillment: {sample_fulfillment:.2%}')

# End-to-End Problem: From raw orders to operational insight
# 1. Collect delivered orders
df_delivered = orders[orders['order_status']=='delivered'].copy()
# 2. Calculate delivery lag and on-time status
df_delivered['delivery_lag'] = (df_delivered['order_delivered_customer_date'] - df_delivered['order_purchase_timestamp']).dt.days
df_delivered['on_time'] = df_delivered['order_delivered_customer_date'] <= df_delivered['order_estimated_delivery_date']
# 3. Compute monthly fulfillment and on-time rates
df_delivered['month'] = df_delivered['order_purchase_timestamp'].dt.to_period('M')
monthly_metrics = df_delivered.groupby('month').agg(on_time_rate=('on_time','mean'), avg_lag=('delivery_lag','mean'), delivered_orders=('order_id','count'))
print(monthly_metrics.tail())