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

# Load a real 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))

total_transactions = df['Invoice'].nunique()
print(f'Total unique retail transactions: {total_transactions}')

df['Revenue'] = df['Quantity'] * df['Price']
total_revenue = df['Revenue'].sum()
print(f'Total revenue for this period: {total_revenue:.2f}')

top_product = df.groupby('Description')['Quantity'].sum().sort_values(ascending=False).head(1)
print('Top selling product (by quantity):')
print(top_product)

order_revenue = df.groupby('Invoice')['Revenue'].sum()
aov = order_revenue.mean()
print(f'Average order value: {aov:.2f}')

country_revenue = df.groupby('Country')['Revenue'].sum().sort_values(ascending=False)
print('Top 5 countries by total revenue:')
print(country_revenue.head(5))

df['Month'] = df['InvoiceDate'].dt.to_period('M')
monthly_sales = df.groupby('Month')['Revenue'].sum()
print('Monthly revenue:')
print(monthly_sales.head())

clv = df.groupby('Customer ID')['Revenue'].sum().sort_values(ascending=False).head(5)
print('Top 5 customers by lifetime revenue:')
print(clv)

np.random.seed(42)
categories = ['Electronics','Clothing','Home','Sports','Beauty']
product_ids = list(df['StockCode'].unique())[:100]
assigned_categories = np.random.choice(categories, len(product_ids))
catalog = pd.DataFrame({'StockCode': product_ids, 'Category': assigned_categories})
df_merged = pd.merge(df, catalog, on='StockCode', how='left')
cat_sales = df_merged.groupby('Category')['Revenue'].sum()
print('Total revenue by category:')
print(cat_sales)

df['FirstPurchaseMonth'] = df.groupby('Customer ID')['InvoiceDate'].transform('min').dt.to_period('M')
cohort_sales = df.groupby('FirstPurchaseMonth')['Revenue'].sum()
print('Cohort-based monthly sales:')
print(cohort_sales.head())

missing_counts = df.isnull().sum()
print('Missing value count per column:')
print(missing_counts)

invalid_revenue = df.groupby('StockCode')['Revenue'].mean().head(3)
print('Incorrect average revenue per product (should use sums, not means!):')
print(invalid_revenue)

correct_revenue = df.groupby('StockCode')['Revenue'].sum().head(3)
print('Correct total revenue per product:')
print(correct_revenue)

# Customer segmentation example: Label high spenders
high_spenders = df.groupby('Customer ID')['Revenue'].sum()
high_spender_flags = high_spenders > high_spenders.quantile(0.9)
print(f'Number of high-value customers: {high_spender_flags.sum()}')

# Product performance: Top category products
top_cat = df_merged.groupby(['Category','Description'])['Revenue'].sum()
top_cat_sorted = top_cat.sort_values(ascending=False).head(5)
print('Top 5 products by revenue within their category:')
print(top_cat_sorted)

# Market basket analysis setup: Simulate market basket transactions
np.random.seed(42)
products = ['Bread','Milk','Butter','Eggs','Apples','Chicken','Rice','Cheese']
transaction_ids = np.repeat(np.arange(1,301),3)
product_choices = np.random.choice(products,len(transaction_ids))
mb_df = pd.DataFrame({'TransactionID':transaction_ids,'Product':product_choices})
print(mb_df.head(3))

# Demand forecasting: Simple time-series sales summarization
daily_sales = df.groupby(df['InvoiceDate'].dt.date)['Revenue'].sum()
print(daily_sales.head())

top5_revenue = df.groupby('Description')['Revenue'].sum().sort_values(ascending=False).head(5)
print('Top 5 products by total revenue:')
print(top5_revenue)

# Business recommendation based on top sellers
if top5_revenue.iloc[0] > top5_revenue.mean() * 2:
    print('Recommendation: Increase inventory and promotion for the best-selling product.')
else:
    print('Recommendation: Diversify assortment or cross-sell to boost more products.')