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

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

missing_descriptions = df_retail['Description'].isnull().sum()
print(f'Missing product descriptions: {missing_descriptions}')

np.random.seed(42)
categories = ['Electronics','Clothing','Home','Sports','Beauty']
product_ids = list(range(1001,1101))
product_categories = np.random.choice(categories,100)
product_prices = np.round(np.random.uniform(5,500,100),2)
df_catalog = pd.DataFrame({
    'ProductID': product_ids,
    'Category': product_categories,
    'Price': product_prices
})
print(df_catalog.shape)
print(df_catalog.head(3))

category_counts = df_catalog['Category'].value_counts()
print(category_counts)

np.random.seed(42)
n_orders = 1000
order_ids = list(range(1, n_orders+1))
customer_ids = np.random.randint(1000, 1500, n_orders)
product_ids = np.random.randint(1001, 1100, n_orders)
quantities = np.random.randint(1, 5, n_orders)
order_dates = pd.date_range('2023-01-01', periods=n_orders, freq='h')
df_orders = pd.DataFrame({
    'OrderID': order_ids,
    'CustomerID': customer_ids,
    'ProductID': product_ids,
    'Quantity': quantities,
    'OrderDate': order_dates
})
print(df_orders.shape)
print(df_orders.head(3))

df_orders_merged = df_orders.merge(df_catalog, on='ProductID', how='left')
print(df_orders_merged[['OrderID', 'ProductID', 'Category', 'Quantity']].head(5))

qty_by_product = df_orders_merged.groupby('ProductID')['Quantity'].sum().sort_values(ascending=False)
print(qty_by_product.head(5))

df_orders_merged['Revenue'] = df_orders_merged['Quantity'] * df_orders_merged['Price']
revenue_by_product = df_orders_merged.groupby('ProductID')['Revenue'].sum().sort_values(ascending=False)
print(revenue_by_product.head(5))

order_revenue = df_orders_merged.groupby('OrderID')['Revenue'].sum()
aov = order_revenue.mean()
print(f'Average Order Value (AOV): ${aov:.2f}')

category_revenue = df_orders_merged.groupby('Category')['Revenue'].sum().sort_values(ascending=False)
mean_category_revenue = df_orders_merged.groupby('Category')['Revenue'].mean().sort_values(ascending=False)
print('Total revenue by category:')
print(category_revenue)
print(' ')
print('Mean order revenue by category:')
print(mean_category_revenue)

top_category = category_revenue.idxmax()
top_value = category_revenue.max()
print(f'Top category by total revenue: {top_category} (${top_value:.2f})')

df_orders_merged['Month'] = df_orders_merged['OrderDate'].dt.to_period('M')
monthly_revenue = df_orders_merged.groupby('Month')['Revenue'].sum()
print(monthly_revenue.head(6))

avg_qty_by_cat = df_orders_merged.groupby('Category')['Quantity'].mean().sort_values(ascending=False)
print(avg_qty_by_cat)

customer_revenue = df_orders_merged.groupby('CustomerID')['Revenue'].sum().sort_values(ascending=False)
print(customer_revenue.head(5))

pairs = df_orders_merged.groupby('OrderID')['ProductID'].apply(list)
from collections import Counter
pair_counts = Counter()
for products in pairs:
    for i in range(len(products)-1):
        pair = tuple(sorted((products[i], products[i+1])))
        pair_counts[pair] += 1
print(pair_counts.most_common(5))

missing_prices = df_catalog['Price'].isnull().sum()
print(f'Missing product prices: {missing_prices}')

# Incorrect: using count() instead of sum() for quantity by category
bad_qty_by_cat = df_orders_merged.groupby('Category')['Quantity'].count().sort_values(ascending=False)
print(bad_qty_by_cat)
print('Correct total quantity by category:')
print(df_orders_merged.groupby('Category')['Quantity'].sum().sort_values(ascending=False))

# Wrong: Not multiplying by quantity: shows unit price sum, not true revenue
wrong_revenue_by_product = df_orders_merged.groupby('ProductID')['Price'].sum().sort_values(ascending=False)
correct_revenue_by_product = df_orders_merged.groupby('ProductID')['Revenue'].sum().sort_values(ascending=False)
print('Incorrect (just adding prices):')
print(wrong_revenue_by_product.head(3))
print('Correct (quantity x price):')
print(correct_revenue_by_product.head(3))

top_10_products = revenue_by_product.head(10).index.tolist()
df_orders_merged['PerformanceTier'] = np.where(df_orders_merged['ProductID'].isin(top_10_products), 'Top 10', 'Other')
performance_revenue = df_orders_merged.groupby('PerformanceTier')['Revenue'].sum()
print(performance_revenue)

monthly_cat = df_orders_merged.groupby(['Month','Category'])['Revenue'].sum().unstack().fillna(0)
print(monthly_cat.head(6))

latest_months = df_orders_merged['Month'].sort_values().unique()[-3:]
last3_df = df_orders_merged[df_orders_merged['Month'].isin(latest_months)]
forecast = last3_df.groupby('ProductID')['Quantity'].mean().sort_values(ascending=False)
print(forecast.head(5))

current_month = df_orders_merged['Month'].max()
cur_month_df = df_orders_merged[df_orders_merged['Month'] == current_month]
top_cur_cat = cur_month_df.groupby('Category')['Revenue'].sum().idxmax()
top_cur_val = cur_month_df.groupby('Category')['Revenue'].sum().max()
print(f'In {current_month}, the top-selling category is {top_cur_cat} with ${top_cur_val:.2f} in sales.')
print('Business action: Consider additional promotions for this category or expanding its inventory in the coming month.')