import pandas as pd
import numpy as np

df = pd.read_csv('superstore_full.csv')
df['Order Date'] = pd.to_datetime(df['Order Date'])
df['Ship Date'] = pd.to_datetime(df['Ship Date'])
print(df.shape)
print(df.isna().sum().sum(), 'real missing values')
print(df.duplicated().sum(), 'real duplicate rows')

total_sales = df['Sales'].sum()
total_profit = df['Profit'].sum()
margin = total_profit / total_sales
print(f'Total sales: ${total_sales:,.0f}')
print(f'Total profit: ${total_profit:,.0f}')
print(f'Overall margin: {margin:.1%}')

monthly_sales = df.set_index('Order Date')['Sales'].resample('QE').sum()
print(monthly_sales)

snapshot_date = df['Order Date'].max() + pd.Timedelta(days=1)
rfm = df.groupby('Customer ID').agg(
    recency=('Order Date', lambda x: (snapshot_date - x.max()).days),
    frequency=('Order ID', 'nunique'),
    monetary=('Sales', 'sum')
)
print(rfm.head())
print(rfm.shape)

rfm['r_score'] = pd.qcut(rfm['recency'], 4, labels=[4, 3, 2, 1])
rfm['f_score'] = pd.qcut(rfm['frequency'].rank(method='first'), 4, labels=[1, 2, 3, 4])
rfm['m_score'] = pd.qcut(rfm['monetary'], 4, labels=[1, 2, 3, 4])
rfm['rfm_score'] = rfm['r_score'].astype(int) + rfm['f_score'].astype(int) + rfm['m_score'].astype(int)
print(rfm.sort_values('rfm_score', ascending=False).head())

def segment_label(score):
    if score >= 10:
        return 'Champions'
    elif score >= 7:
        return 'Loyal'
    elif score >= 5:
        return 'At Risk'
    else:
        return 'Lost'

rfm['segment'] = rfm['rfm_score'].apply(segment_label)
print(rfm['segment'].value_counts())

from scipy import stats
corr, p_val = stats.pearsonr(df['Discount'], df['Profit'])
print(f'Discount-Profit correlation: {corr:.3f} (p={p_val:.6f})')
discount_bins = pd.cut(df['Discount'], bins=[-0.01, 0, 0.2, 0.4, 1.0], labels=['0%', '1-20%', '21-40%', '40%+'])
print(df.groupby(discount_bins, observed=True)['Profit'].mean().round(2))

df['ship_days'] = (df['Ship Date'] - df['Order Date']).dt.days
print(df.groupby('Ship Mode')['ship_days'].agg(['mean', 'min', 'max']).round(1))

champions = (rfm['segment'] == 'Champions').sum()
champions_share = rfm.loc[rfm['segment'] == 'Champions', 'monetary'].sum() / rfm['monetary'].sum()

summary = f'''
RETAIL ANALYTICS SUMMARY
Total sales: ${total_sales:,.0f} across {len(df):,} orders, {margin:.1%} overall margin.
{champions} of {len(rfm)} customers are Champions, driving {champions_share:.1%} of total revenue.
Discount and profit show a real, statistically significant negative relationship (r={corr:.2f}); orders discounted above 40% average negative profit.
Recommendation: protect margin by capping discounts near 20%, and prioritize retention offers for Champions and At Risk segments.
'''
print(summary)