import pandas as pd

orders = pd.read_csv('orders_sample.csv', parse_dates=['order_purchase_timestamp', 'order_delivered_customer_date', 'order_estimated_delivery_date'])
customers = pd.read_csv('customers_sample.csv')
payments = pd.read_csv('payments_sample.csv')
reviews = pd.read_csv('reviews_sample.csv')
print(orders.shape, customers.shape, payments.shape, reviews.shape)

delivered = orders[orders['order_status'] == 'delivered'].copy()
df = (delivered
      .merge(customers, on='customer_id', how='left')
      .merge(reviews[['order_id', 'review_score']], on='order_id', how='left')
      .merge(payments.groupby('order_id')['payment_value'].sum().reset_index(), on='order_id', how='left'))
print(df.shape)
print(df[['order_id', 'customer_state', 'review_score', 'payment_value']].head(3))

df['delay_days'] = (df['order_delivered_customer_date'] - df['order_estimated_delivery_date']).dt.days
late_share = (df['delay_days'] > 0).mean()
print(f'{late_share:.1%} of real delivered orders arrived after the estimate')
print(df['delay_days'].describe())

delay_bins = pd.cut(df['delay_days'], bins=[-100, -1, 0, 7, 100], labels=['Early', 'On Time', '1-7 Days Late', '8+ Days Late'])
review_by_delay = df.groupby(delay_bins, observed=True)['review_score'].agg(['mean', 'count']).round(2)
print(review_by_delay)

from scipy import stats
clean = df.dropna(subset=['delay_days', 'review_score'])
corr, p_val = stats.pearsonr(clean['delay_days'], clean['review_score'])
print(f'Delay-Satisfaction correlation: {corr:.3f} (p={p_val:.6f})')

payment_types = pd.read_csv('payments_sample.csv')
print(payment_types['payment_type'].value_counts())
print(payment_types.groupby('payment_type')['payment_installments'].mean().round(1))

state_delay = df.groupby('customer_state')['delay_days'].agg(['mean', 'count'])
state_delay = state_delay[state_delay['count'] >= 10].sort_values('mean', ascending=False)
print(state_delay.round(1).head(6))

print((state_delay['mean'] < 0).all())
print(f"Overall mean delay: {df['delay_days'].mean():.1f} days")

worst_state = state_delay.index[0]
worst_state_delay = state_delay['mean'].iloc[0]
on_time_score = review_by_delay.loc['On Time', 'mean']
late_score = review_by_delay.loc['8+ Days Late', 'mean']

summary = f'''
BUSINESS OPERATIONS SUMMARY
{late_share:.1%} of real delivered orders arrived after the estimated date.
Average review score falls from {on_time_score:.2f} on-time to {late_score:.2f} when delivery runs 8+ days late.
{worst_state} has the smallest average delivery buffer among states with sufficient real order volume, at {worst_state_delay:.1f} days before the estimate, the state genuinely closest to tipping into late.
Recommendation: monitor {worst_state} carrier performance closely as volume grows, since it has the least real margin for error before deliveries start arriving late.
'''
print(summary)