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

# Beginner Example 1: 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('Shape:', df.shape)
print(df.head(3))

# Beginner Example 2: Look at unique customers
unique_customers = df['Customer ID'].nunique()
print('Number of unique customers:', unique_customers)

# Beginner Example 3: Calculate total revenue per transaction
df['Revenue'] = df['Quantity'] * df['Price']
print(df[['Invoice', 'Customer ID', 'Revenue']].head(3))

# Intermediate Example 1: Calculate each customer's total revenue
customer_revenue = df.groupby('Customer ID')['Revenue'].sum().reset_index()
customer_revenue = customer_revenue.rename(columns={'Revenue': 'TotalRevenue'})
print(customer_revenue.head(3))

# Intermediate Example 2: Calculate the number of purchases per customer
customer_freq = df.groupby('Customer ID')['Invoice'].nunique().reset_index()
customer_freq = customer_freq.rename(columns={'Invoice': 'NumPurchases'})
print(customer_freq.head(3))

# Intermediate Example 3: Find each customer's last purchase date
last_dates = df.groupby('Customer ID')['InvoiceDate'].max().reset_index()
last_dates = last_dates.rename(columns={'InvoiceDate': 'LastPurchaseDate'})
print(last_dates.head(3))

# Advanced Example 1: Calculate Recency, Frequency, and Monetary (RFM) features
REFERENCE_DATE = df['InvoiceDate'].max() + pd.Timedelta(days=1)
rfm = df.groupby('Customer ID').agg({
    'InvoiceDate': lambda x: (REFERENCE_DATE - x.max()).days,
    'Invoice': 'nunique',
    'Revenue': 'sum'
}).reset_index()
rfm.columns = ['CustomerID', 'Recency', 'Frequency', 'Monetary']
print(rfm.head(3))

# Advanced Example 2: Visualize RFM segments
plt.figure(figsize=(8,6))
sns.scatterplot(data=rfm, x='Recency', y='Monetary', size='Frequency', alpha=0.6, legend=False)
plt.xlabel('Recency (days since last purchase)')
plt.ylabel('Total Monetary Value')
plt.title('Customer Segments: Recency vs Monetary')
plt.show()

# Advanced Example 3: Create a simple churn flag
inactive_threshold = 90
rfm['ChurnFlag'] = (rfm['Recency'] > inactive_threshold).astype(int)
print(rfm[['CustomerID', 'Recency', 'ChurnFlag']].head(5))

# Error Example: What if Revenue has missing values?
missing_revenue = df['Revenue'].isnull().sum()
print('Transactions with missing revenue:', missing_revenue)

# Error Example: Handle missing or negative quantities
n_invalid_qty = (df['Quantity'] <= 0).sum()
print('Transactions with invalid (zero or negative) quantity:', n_invalid_qty)
df_clean = df[df['Quantity'] > 0]

# Error Example: Mistakenly grouping by product, not customer
product_freq = df.groupby('StockCode')['Invoice'].nunique().reset_index()
print(product_freq.head(3))
# This is not helpful for churn!

# Best Practice: Identify high-risk (churn) segment for targeted offers
at_risk_customers = rfm[(rfm['Recency'] > 90) & (rfm['Frequency'] <= 2)]
print('Number of at-risk customers:', at_risk_customers.shape[0])
print(at_risk_customers[['CustomerID', 'Recency', 'Frequency', 'Monetary']].head(5))

# End-to-End Example: Generate a churn prediction report
report = at_risk_customers[['CustomerID', 'Recency', 'Frequency', 'Monetary']].copy()
report['RecommendedAction'] = 'Send Re-engagement Offer'
report.to_csv('churn_report.csv', index=False)
print('Churn prediction report saved as churn_report.csv')