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

url = 'https://raw.githubusercontent.com/olist/work-at-olist-data/master/datasets/olist_orders_dataset.csv'
df = pd.read_csv(url)
print(df.shape)
print(df.head(3))

df['order_purchase_timestamp'] = pd.to_datetime(df['order_purchase_timestamp'])
df['order_delivered_customer_date'] = pd.to_datetime(df['order_delivered_customer_date'])
print(df[['order_purchase_timestamp', 'order_delivered_customer_date']].head())

df['lead_time_days'] = (df['order_delivered_customer_date'] - df['order_purchase_timestamp']).dt.days
print(df[['order_purchase_timestamp', 'order_delivered_customer_date', 'lead_time_days']].head())

lead_times = df['lead_time_days'].dropna()
print('Number of orders with valid lead times:', lead_times.shape[0])
print('Typical lead time (days):', lead_times.median())

plt.figure(figsize=(8,4))
plt.hist(lead_times, bins=30, color='skyblue', edgecolor='black')
plt.title('Distribution of Delivery Lead Times (days)')
plt.xlabel('Lead Time (days)')
plt.ylabel('Number of Orders')
plt.show()

valid = df[df['lead_time_days'].notnull() & (df['lead_time_days'] >= 0)]
average_lead_time = valid['lead_time_days'].mean()
print('Average delivery lead time (days):', round(average_lead_time, 2))

q_low = valid['lead_time_days'].quantile(0.01)
q_high = valid['lead_time_days'].quantile(0.99)
outliers = valid[(valid['lead_time_days'] < q_low) | (valid['lead_time_days'] > q_high)]
print('Orders with lead times outside the 1st and 99th percentiles:', outliers.shape[0])

status_group = valid.groupby('order_status')['lead_time_days'].mean()
print(status_group)

status_counts = valid['order_status'].value_counts()
plt.figure(figsize=(8,3))
plt.bar(status_counts.index, status_counts.values, color='orange')
plt.title('Count of Completed Orders by Status')
plt.xlabel('Order Status')
plt.ylabel('Number of Orders')
plt.show()

customers_url = 'https://raw.githubusercontent.com/olist/work-at-olist-data/master/datasets/olist_customers_dataset.csv'
customers = pd.read_csv(customers_url)
df_state = pd.merge(valid, customers[['customer_id','customer_state']], on='customer_id', how='left')
state_stats = df_state.groupby('customer_state')['lead_time_days'].agg(['mean','std','count']).sort_values(by='mean')
print(state_stats.head())

plt.figure(figsize=(12,5))
plt.bar(state_stats.index, state_stats['mean'], yerr=state_stats['std'], color='seagreen')
plt.title('Mean Lead Time by State with Variability')
plt.xlabel('State')
plt.ylabel('Mean Lead Time (days)')
plt.xticks(rotation=90)
plt.show()

valid['order_month'] = valid['order_purchase_timestamp'].dt.to_period('M')
month_stats = valid.groupby('order_month')['lead_time_days'].agg(['mean','std','count'])
print(month_stats.tail(12))

plt.figure(figsize=(10,4))
plt.errorbar(month_stats.index.astype(str), month_stats['mean'], yerr=month_stats['std'], fmt='-o', color='navy')
plt.title('Monthly Mean Lead Time with Variability')
plt.xlabel('Month')
plt.ylabel('Mean Lead Time (days)')
plt.xticks(rotation=45)
plt.show()

sellers_url = 'https://raw.githubusercontent.com/olist/work-at-olist-data/master/datasets/olist_order_items_dataset.csv'
items = pd.read_csv(sellers_url)
seller_lead = pd.merge(valid, items[['order_id','seller_id']], on='order_id', how='left')
seller_stats = seller_lead.groupby('seller_id')['lead_time_days'].agg(['mean','std','count'])
top_sellers = seller_stats.sort_values('count', ascending=False).head(5)
print(top_sellers)

missing_lt = df['lead_time_days'].isnull().sum()
negative_lt = (df['lead_time_days'] < 0).sum()
print(f'Orders missing lead time: {missing_lt}')
print(f'Orders with negative lead time: {negative_lt}')

problem_orders = df[(df['lead_time_days'].isnull()) | (df['lead_time_days'] < 0)]
print(problem_orders[['order_id', 'order_status', 'lead_time_days']].head())

mean_lt = lead_times.mean()
std_lt = lead_times.std()
cv_lt = std_lt / mean_lt if mean_lt != 0 else np.nan
print(f'Coefficient of Variation (lead time): {cv_lt:.2f}')

avg_daily_sales = 50
z = 1.65  # for about 95 percent service level
safety_stock = z * avg_daily_sales * std_lt ** 0.5
print(f'Estimated safety stock units (given observed lead time variability): {int(safety_stock)}')

null_rows = df[df['order_purchase_timestamp'].isnull() | df['order_delivered_customer_date'].isnull()]
print(f'Orders with missing critical dates: {null_rows.shape[0]}')

# Example: Bad join scenario
pre_join = valid.shape[0]
joined = pd.merge(valid, customers, on='customer_id', how='left')
post_join = joined.shape[0]
print(f'Rows before join: {pre_join}, after join: {post_join}')

sla_days = 7
service_level = (valid['lead_time_days'] <= sla_days).mean()
print(f'Percent of orders delivered within {sla_days} days: {service_level:.2%}')

monthly_sla = valid.groupby('order_month').apply(lambda g: (g['lead_time_days'] <= sla_days).mean())
monthly_sla.plot(kind='bar', figsize=(10,4), color='teal')
plt.title('Monthly SLA Service Level')
plt.xlabel('Month')
plt.ylabel('Percent Orders < 7 days')
plt.show()

worst_month = month_stats['mean'].idxmax()
print('Month with highest mean delivery lead time:', worst_month)

bad_month_orders = valid[valid['order_month'] == worst_month]
late_sellers = pd.merge(bad_month_orders, items[['order_id','seller_id']], on='order_id', how='left')
seller_risk = late_sellers.groupby('seller_id')['lead_time_days'].mean().sort_values(ascending=False).head(3)
print('Sellers with highest mean lead time during worst month:')
print(seller_risk)