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

# Load the Online Retail Transactions Dataset from UCI
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))

# Beginner Example 1: Counting Total Unique Customers
num_customers = df['Customer ID'].nunique()
print('Total unique customers:', num_customers)

# Beginner Example 2: What is the average purchase quantity per invoice?
avg_qty_per_invoice = df.groupby('Invoice')['Quantity'].sum().mean()
print('Average quantity per invoice:', round(avg_qty_per_invoice,2))

# Beginner Example 3: Top 5 best-selling products (by quantity sold)
top_products = df.groupby('Description')['Quantity'].sum().sort_values(ascending=False).head(5)
print('Top 5 products by total quantity sold:')
print(top_products)

# Beginner Example 4: What is the total sales revenue generated?
df['TotalPrice'] = df['Quantity'] * df['Price']
total_revenue = df['TotalPrice'].sum()
print('Total sales revenue:', round(total_revenue,2))

# Beginner Example 5: Distribution of purchases by country
country_counts = df['Country'].value_counts().head(5)
print('Top 5 countries by number of purchases:')
print(country_counts)

# Beginner Example 6: How many repeat customers do we have?
repeat_customers = df.groupby('Customer ID').size().loc[lambda x: x > 1].count()
print('Number of customers with multiple purchases:', repeat_customers)

# Beginner Example 7: Which day has the highest number of purchases?
most_active_day = df['InvoiceDate'].dt.date.value_counts().idxmax()
print('Day with most purchases:', most_active_day)

# Intermediate Example 1: Revenue by country
country_revenue = df.groupby('Country')['TotalPrice'].sum().sort_values(ascending=False).head(5)
print('Top 5 countries by revenue:')
print(country_revenue)

# Intermediate Example 2: Who are the top 10 customers by lifetime value (LTV)?
top_customers = df.groupby('Customer ID')['TotalPrice'].sum().sort_values(ascending=False).head(10)
print('Top 10 customers by total spend:')
print(top_customers)

# Intermediate Example 3: Orders over time (monthly revenue trend)
df['Month'] = df['InvoiceDate'].dt.to_period('M')
monthly_revenue = df.groupby('Month')['TotalPrice'].sum()
print('Monthly revenue breakdown:')
print(monthly_revenue.tail(6))

# Intermediate Example 4: What is the average order value (AOV)?
invoice_totals = df.groupby('Invoice')['TotalPrice'].sum()
aov = invoice_totals.mean()
print('Average order value (AOV):', round(aov, 2))

# Intermediate Example 5: Which products generate the most revenue?
product_revenue = df.groupby('Description')['TotalPrice'].sum().sort_values(ascending=False).head(5)
print('Top 5 products by revenue:')
print(product_revenue)

# Intermediate Example 6: Customer frequency - how often do customers buy?
customer_freq = df.groupby('Customer ID')['InvoiceDate'].nunique().sort_values(ascending=False).head(5)
print('Top 5 customers by the number of distinct purchase days:')
print(customer_freq)

# Intermediate Example 7: Which customer segment spends the most?
df['OrderSize'] = pd.cut(df['TotalPrice'], bins=[-1,20,100,500,np.inf], labels=['Small','Medium','Large','ExtraLarge'])
segment_counts = df.groupby('OrderSize')['TotalPrice'].mean().sort_values(ascending=False)
print('Average spend by order size segment:')
print(segment_counts)

# Advanced Example 1: Identify customers with high recency, frequency, and monetary value (RFM)
snapshot_date = df['InvoiceDate'].max() + pd.Timedelta(days=1)
rfm = df.groupby('Customer ID').agg({
    'InvoiceDate': lambda x: (snapshot_date - x.max()).days,
    'Invoice': 'nunique',
    'TotalPrice': 'sum'
})
rfm.columns = ['Recency', 'Frequency', 'Monetary']
print('Sample RFM table:')
print(rfm.head())

# Advanced Example 2: Segmenting customers by RFM scores
rfm['R'] = pd.qcut(rfm['Recency'], 4, labels=[4,3,2,1])
rfm['F'] = pd.qcut(rfm['Frequency'].rank(method='first'), 4, labels=[1,2,3,4])
rfm['M'] = pd.qcut(rfm['Monetary'], 4, labels=[1,2,3,4])
rfm['RFM_Score'] = rfm[['R', 'F', 'M']].astype(int).sum(axis=1)
print('Customer segmentation based on RFM score:')
print(rfm[['Recency','Frequency','Monetary','RFM_Score']].head(5))

# Advanced Example 3: Detecting returns and cancelled transactions
cancelled = df[df['Invoice'].astype(str).str.contains('C')]
print('Number of cancelled/returned transactions:', cancelled.shape[0])
print('Sample cancelled transactions:')
print(cancelled[['Invoice', 'Customer ID', 'TotalPrice']].head())

# Advanced Example 4: Visualizing sales trend over time (requires matplotlib)
import matplotlib.pyplot as plt
plt.figure(figsize=(12,5))
monthly_revenue.plot(kind='bar')
plt.title('Monthly Sales Revenue Trend')
plt.xlabel('Month')
plt.ylabel('Revenue')
plt.tight_layout()
plt.show()

# Error Handling 1: Checking for missing values
missing_counts = df.isnull().sum()
print('Missing value counts per column:')
print(missing_counts)

# Error Handling 2: Handling negative quantities (returns)
negative_quantities = df[df['Quantity'] < 0]
print('Number of transactions with negative quantity (returns):', negative_quantities.shape[0])

# Error Handling 3: What happens if you try to group by a column with missing values?
try:
    problem_group = df.groupby('Customer ID')['TotalPrice'].sum()
    print('Grouping succeeded!')
except Exception as e:
    print('Error during grouping:', e)

# Error Handling 4: Double counting errors - grouping by the wrong column
wrong_count = df.groupby('Invoice')['Description'].count().sum()
right_count = df.shape[0]
print('Wrong count if grouping then summing:', wrong_count)
print('Actual transaction count:', right_count)

# Best Practice 1: Removing outliers for better purchase analysis
q_low = df['TotalPrice'].quantile(0.01)
q_high = df['TotalPrice'].quantile(0.99)
filtered = df[(df['TotalPrice'] >= q_low) & (df['TotalPrice'] <= q_high)]
print('Shape after removing outliers:', filtered.shape)

# Best Practice 2: Segmenting by product category using product codes
category_counts = df.groupby('StockCode')['TotalPrice'].sum().sort_values(ascending=False).head(5)
print('StockCodes with highest revenue:')
print(category_counts)

# Best Practice 3: Checking for seasonality
df['Weekday'] = df['InvoiceDate'].dt.day_name()
weekday_sales = df.groupby('Weekday')['TotalPrice'].sum()
weekday_sales = weekday_sales.reindex(['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday'])
print('Total revenue by weekday:')
print(weekday_sales)

# Best Practice 4: Market basket analysis - Products purchased together
basket = df.groupby(['Invoice', 'Description'])['Quantity'].sum().unstack().fillna(0)
top_pairs = (basket > 0).sum(axis=0).sort_values(ascending=False).head(3)
print('Most frequently purchased products in baskets:')
print(top_pairs.index.tolist())

# End-to-End Example: Find high-value customers for a holiday email campaign
valid_sales = df[(~df['Invoice'].astype(str).str.contains('C')) & (df['TotalPrice'] > 0)]
customer_spend = valid_sales.groupby('Customer ID')['TotalPrice'].sum()
customer_email_targets = customer_spend[customer_spend > customer_spend.quantile(0.95)].index.tolist()
print('Number of top-spending customers to target:', len(customer_email_targets))
print('Sample Customer IDs:', customer_email_targets[:5])