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

df_transactions['TotalSales'] = df_transactions['Quantity'] * df_transactions['Price']
total_sales = df_transactions['TotalSales'].sum()
print(f'Total sales revenue: {total_sales:,.2f}')

n_customers = df_transactions['Customer ID'].nunique()
print('Number of unique customers:', n_customers)

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_products = pd.DataFrame({'ProductID':product_ids,'Category':product_categories,'Price':product_prices})
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))

daily_sales = df_transactions.groupby(df_transactions['InvoiceDate'].dt.date)['TotalSales'].sum()
print(daily_sales.head(7))

top_products = df_transactions.groupby('Description')['TotalSales'].sum().sort_values(ascending=False).head(5)
print('Top 5 products by sales:')
print(top_products)

df_merged = pd.merge(df_orders, df_products, left_on='ProductID', right_on='ProductID', how='left')
df_merged['OrderValue'] = df_merged['Quantity'] * df_merged['Price']
category_sales = df_merged.groupby('Category')['OrderValue'].sum().sort_values(ascending=False)
print('Sales revenue per category:')
print(category_sales)

customer_order_value = df_merged.groupby('CustomerID')['OrderValue'].sum()
avg_order_value = customer_order_value.mean()
print(f'Average total spend per customer: {avg_order_value:.2f}')

df_merged['Month'] = df_merged['OrderDate'].dt.month
monthly_sales = df_merged.groupby('Month')['OrderValue'].sum()
print('Month-by-month sales revenue:')
print(monthly_sales)

top_customers = customer_order_value.sort_values(ascending=False).head(3)
print('Top 3 high-value customers and their total spend:')
print(top_customers)

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))
df_basket = pd.DataFrame({'TransactionID':transaction_ids,'Product':product_choices})
print(df_basket.shape)
print(df_basket.head(3))

item_counts = df_basket.groupby('Product').size().sort_values(ascending=False)
print('Most frequently purchased products:')
print(item_counts)

from itertools import combinations
basket_sets = df_basket.groupby('TransactionID')['Product'].apply(set)
pair_counts = {}
for basket in basket_sets:
    for pair in combinations(sorted(basket), 2):
        pair_counts[pair] = pair_counts.get(pair,0)+1
pair_counts_sorted = dict(sorted(pair_counts.items(), key=lambda x: x[1], reverse=True))
print('Most common product pairs:')
for pair, count in list(pair_counts_sorted.items())[:5]:
    print(pair, ':', count)

missing_customers = df_transactions['Customer ID'].isnull().sum()
print('Number of transactions with missing customer ID:', missing_customers)

# Wrong: summing price without multiplying by quantity
wrong_revenue = df_transactions['Price'].sum()
print('Incorrect total revenue (should not use):', wrong_revenue)

# Incorrect: Counting orders without grouping by category
wrong_counts = df_merged.groupby('ProductID').size()
print('Orders per product (not category):', wrong_counts.head(3).to_dict())

segments = pd.qcut(customer_order_value, 4, labels=['Low','Mid-Low','Mid-High','High'])
segment_counts = segments.value_counts()
print('Customer spend segments:')
print(segment_counts.sort_index())

import matplotlib.pyplot as plt
plt.plot(monthly_sales.index, monthly_sales.values, marker='o')
plt.xlabel('Month')
plt.ylabel('Sales Revenue')
plt.title('Monthly Sales Revenue Trend')
plt.xticks(monthly_sales.index)
plt.grid(True)
plt.show()

category_share = category_sales / category_sales.sum()
print('Product category revenue share:')
print((category_share * 100).round(2).astype(str) + '%')

daily_sales_sorted = daily_sales.sort_index()
rolling_avg = daily_sales_sorted.rolling(window=7).mean()
print('7-day moving average sales:')
print(rolling_avg.head(10))

category_revenue = df_merged.groupby('Category')['OrderValue'].sum().sort_values(ascending=False)
top_category = category_revenue.index[0]
top_revenue = category_revenue.iloc[0]
print(f'The top-selling category is {top_category} with revenue of {top_revenue:,.2f}.')