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_transactions = pd.read_excel(url, sheet_name='Year 2010-2011')
df_transactions['InvoiceDate'] = pd.to_datetime(df_transactions['InvoiceDate'])
print(df_transactions.shape)
print(df_transactions.head(3))

print('Available columns:', list(df_transactions.columns))

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

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))

sales_summary = df_transactions.groupby('Country').agg({'Invoice':'nunique'}).rename(columns={'Invoice':'NumInvoices'})
sales_summary = sales_summary.sort_values('NumInvoices', ascending=False)

print(sales_summary.head(5))

revenue = (df_transactions['Quantity'] * df_transactions['Price']).sum()
print(f'Total revenue in dataset: {revenue:.2f} GBP')

category_sales = df_orders.merge(df_products, left_on='ProductID', right_on='ProductID')
cat_rev = (category_sales['Quantity'] * category_sales['Price']).groupby(category_sales['Category']).sum().sort_values(ascending=False)

print(cat_rev)

customer_sales = df_orders.groupby('CustomerID')['Quantity'].sum().sort_values(ascending=False).head(5)
print(customer_sales)

orders_per_day = df_orders.groupby(df_orders['OrderDate'].dt.date)['OrderID'].nunique()
print(orders_per_day.head(7))

# Advanced: Find top revenue-generating customers
order_revenue = df_orders.merge(df_products, left_on='ProductID', right_on='ProductID')
order_revenue['Revenue'] = order_revenue['Quantity'] * order_revenue['Price']
top_customers = order_revenue.groupby('CustomerID')['Revenue'].sum().sort_values(ascending=False).head(5)
print(top_customers)

# Advanced: Calculate average order value (AOV)
order_totals = order_revenue.groupby('OrderID')['Revenue'].sum()
aov = order_totals.mean()
print(f'Average order value: {aov:.2f} GBP')

# Advanced: Weekly revenue trends
order_revenue['Week'] = order_revenue['OrderDate'].dt.isocalendar().week
weekly_rev = order_revenue.groupby('Week')['Revenue'].sum()
print(weekly_rev.head(3))

# Error handling: Check for missing prices
missing_prices = df_products['Price'].isnull().sum()
print(f'Products missing price: {missing_prices}')

# Error handling: What if orders try to use products with no catalog entry?
invalid_products = df_orders[~df_orders['ProductID'].isin(df_products['ProductID'])]
print('Orders with invalid ProductIDs:', len(invalid_products))

# Debugging: Revenue by category using wrong grouping (mistake example)
wrong_group = order_revenue.groupby('ProductID')['Revenue'].sum()
print('Number of grouped rows:', len(wrong_group))

# Best practice: Segment customers by quantity purchased
df_orders['Customer_Segment'] = pd.qcut(df_orders['Quantity'].cumsum(), 3, labels=['Low', 'Medium', 'High'])
print(df_orders[['CustomerID', 'Quantity', 'Customer_Segment']].head(7))

# Best practice: Find top performing products for inventory planning
top_products = order_revenue.groupby('ProductID')['Quantity'].sum().sort_values(ascending=False).head(10)
print(top_products)

# Best practice: Simple market basket analysis - count most common product pairs
from itertools import combinations
basket = df_orders.groupby('OrderID')['ProductID'].apply(list)
pair_counts = {}
for products in basket:
    for pair in combinations(sorted(products), 2):
        pair_counts[pair] = pair_counts.get(pair, 0) + 1
sorted_pairs = sorted(pair_counts.items(), key=lambda x: x[1], reverse=True)[:5]
print(sorted_pairs)

# Best practice: Basic demand forecasting using moving average
weekly = order_revenue.groupby('Week')['Quantity'].sum()
weekly_ma = weekly.rolling(4).mean()
print(weekly_ma.dropna().head(5))

# Best practice: Find monthly sales seasonality
order_revenue['Month'] = order_revenue['OrderDate'].dt.month
monthly_sales = order_revenue.groupby('Month')['Revenue'].sum()
print(monthly_sales)

# End-to-end example: Identify top-selling products and recommend stocking
top3 = top_products.head(3).index.tolist()
products_to_stock = df_products[df_products['ProductID'].isin(top3)]
print('Top 3 products to prioritize for stock:')
print(products_to_stock)