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

# Load the Online Retail Transactions Dataset
url = 'https://archive.ics.uci.edu/ml/machine-learning-databases/00502/online_retail_II.xlsx'
df = pd.read_excel(url, sheet_name='Year 2010-2011')
df['InvoiceDate'] = pd.to_datetime(df['InvoiceDate'])
print(df.shape)
print(df.head(3))

# Check missing values in customer IDs
missing_customers = df['Customer ID'].isnull().sum()
print('Number of missing customer IDs:', missing_customers)

# Remove transactions that do not have a customer ID
df = df.dropna(subset=['Customer ID'])
print('Transactions remaining after cleaning:', df.shape[0])

# Quick sales aggregation by customer
df['Revenue'] = df['Price'] * df['Quantity']
customer_sales = df.groupby('Customer ID')['Revenue'].sum().reset_index()
customer_sales = customer_sales.sort_values(by='Revenue', ascending=False)
print(customer_sales.head(5))

# Find the number of unique customers
n_customers = df['Customer ID'].nunique()
print('Unique customers:', n_customers)

# Count purchases per customer
customer_freq = df.groupby('Customer ID')['Invoice'].nunique().reset_index()
customer_freq = customer_freq.rename(columns={'Invoice':'Num_Purchases'})
print(customer_freq.sort_values(by='Num_Purchases',ascending=False).head(5))

# Compute average order value (AOV) per customer
aov_per_customer = customer_sales.merge(customer_freq, on='Customer ID')
aov_per_customer['AOV'] = aov_per_customer['Revenue'] / aov_per_customer['Num_Purchases']
print(aov_per_customer[['Customer ID','AOV']].sort_values('AOV',ascending=False).head())

# Segment customers by total revenue into quantiles
customer_sales['Segment'] = pd.qcut(customer_sales['Revenue'], q=4, labels=['Bronze','Silver','Gold','Platinum'])
print(customer_sales[['Customer ID','Revenue','Segment']].head(10))

# Calculate recency for each customer (in days)
latest_date = df['InvoiceDate'].max()
recency = df.groupby('Customer ID')['InvoiceDate'].max().reset_index()
recency['RecencyDays'] = (latest_date - recency['InvoiceDate']).dt.days
print(recency[['Customer ID','RecencyDays']].sort_values('RecencyDays').head())

# Merge RFM metrics for one-table customer view
rfm = customer_sales.merge(customer_freq, on='Customer ID').merge(recency[['Customer ID','RecencyDays']], on='Customer ID')
print(rfm.head())

# Advanced: k-means clustering for customer segmentation
from sklearn.preprocessing import StandardScaler
from sklearn.cluster import KMeans
X = rfm[['Revenue','Num_Purchases','RecencyDays']]
X_scaled = StandardScaler().fit_transform(X)
kmeans = KMeans(n_clusters=4, random_state=42)
rfm['Cluster'] = kmeans.fit_predict(X_scaled)
print(rfm[['Customer ID','Revenue','Num_Purchases','RecencyDays','Cluster']].head())

# Advanced: Profile each customer cluster
cluster_profile = rfm.groupby('Cluster').agg({'Revenue':'mean','Num_Purchases':'mean','RecencyDays':'mean','Customer ID':'count'}).rename(columns={'Customer ID':'Num_Customers'})
print(cluster_profile)

# Error handling: What if a groupby fails due to a missing column?
try:
    rfm.groupby('MissingColumn').mean()
except KeyError as e:
    print('Error:', e)

# Debugging: Make sure revenue is always non-negative
bad_revenue = df[df['Revenue'] < 0]
print('Transactions with negative revenue:', bad_revenue.shape[0])

# Remove transactions with negative revenue for clean analysis
df = df[df['Revenue'] >= 0]
print('Transactions after removing negatives:', df.shape[0])

# Tiny End-to-End Problem: Identify VIP Customers
vip = customer_sales[customer_sales['Segment']=='Platinum']
print('Number of Platinum (VIP) customers:', vip.shape[0])
print(vip[['Customer ID','Revenue']].head(10))

# Bonus: Export VIP list for the marketing team
vip[['Customer ID','Revenue']].to_csv('vip_customers.csv', index=False)
print('VIP customer list exported to vip_customers.csv')