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

# Load sample online retail transaction dataset from UCI
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))

# Load product catalog (synthetic, reproducible)
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))

# Load retail customer orders (synthetic orders, reproducible)
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))

# Beginner Example 1: Find total quantity sold per product
product_sales = df_orders.groupby('ProductID')['Quantity'].sum().reset_index()
product_sales = product_sales.sort_values('Quantity', ascending=False)
print(product_sales.head(10))

# Beginner Example 2: Find total revenue per product
merged_orders = pd.merge(df_orders, df_catalog, left_on='ProductID', right_on='ProductID', how='left')
merged_orders['Revenue'] = merged_orders['Quantity'] * merged_orders['Price']
product_revenue = merged_orders.groupby('ProductID')['Revenue'].sum().reset_index()
product_revenue = product_revenue.sort_values('Revenue', ascending=False)
print(product_revenue.head(10))

# Beginner Example 3: Locate low-performing products (lowest sales quantity)
low_sales = product_sales.tail(10).reset_index(drop=True)
print(low_sales)

# Intermediate Example 1: Calculate total sales by product category
category_sales = pd.merge(product_sales, df_catalog, left_on='ProductID', right_on='ProductID', how='left')
category_sales = category_sales.groupby('Category')['Quantity'].sum().reset_index().sort_values('Quantity', ascending=False)
print(category_sales)

# Intermediate Example 2: Revenue distribution by category
category_revenue = pd.merge(product_revenue, df_catalog, on='ProductID', how='left')
category_revenue = category_revenue.groupby('Category')['Revenue'].sum().reset_index().sort_values('Revenue', ascending=False)
print(category_revenue)

# Intermediate Example 3: Identify products with zero orders (not purchased)
ordered_products = set(df_orders['ProductID'])
all_products = set(df_catalog['ProductID'])
unordered_products = all_products - ordered_products
print('Products never ordered:', unordered_products)

# Intermediate Example 4: Top products for a specific customer
sample_cust = df_orders['CustomerID'].iloc[0]
cust_products = df_orders[df_orders['CustomerID'] == sample_cust].groupby('ProductID')['Quantity'].sum().sort_values(ascending=False).reset_index()
print(f'Top products for Customer {sample_cust}:')
print(cust_products.head(5))

# Advanced Example 1: Monthly sales trend for top 3 products
top3 = product_sales.head(3)['ProductID'].tolist()
merged_orders['Month'] = merged_orders['OrderDate'].dt.to_period('M')
trend = merged_orders[merged_orders['ProductID'].isin(top3)].groupby(['Month', 'ProductID'])['Quantity'].sum().unstack(fill_value=0)
print(trend)

# Advanced Example 2: Identify products with declining sales over time
monthly_trend = merged_orders.groupby(['ProductID', merged_orders['OrderDate'].dt.to_period('M')])['Quantity'].sum().reset_index()
monthly_trend = monthly_trend.sort_values(['ProductID', 'OrderDate'])
declining_products = []
for pid in merged_orders['ProductID'].unique():
    sales = monthly_trend[monthly_trend['ProductID'] == pid]['Quantity'].values
    if len(sales) > 1 and all(earlier >= later for earlier, later in zip(sales, sales[1:])):
        declining_products.append(pid)
print('Products with strictly declining sales:', declining_products)

# Advanced Example 3: Calculate average order value for each product
order_values = merged_orders.groupby('OrderID')['Revenue'].sum()
avg_order_value = merged_orders.groupby('ProductID')['Revenue'].sum() / merged_orders.groupby('ProductID')['OrderID'].nunique()
avg_order_value = avg_order_value.reset_index().rename(columns={0: 'AvgOrderValue'})
print(avg_order_value.head(10))

# Error Example 1: Aggregating revenue without multiplying by quantity (wrong!)
product_wrong = pd.merge(df_orders, df_catalog, left_on='ProductID', right_on='ProductID', how='left')
# Total revenue error: just summing price, not considering quantities
product_wrong_revenue = product_wrong.groupby('ProductID')['Price'].sum().reset_index()
print(product_wrong_revenue.head(3))

# Error Example 2: Missing value handling in product sales
df_orders_with_nan = df_orders.copy()
df_orders_with_nan.loc[0, 'Quantity'] = np.nan
total_quant_nan = df_orders_with_nan['Quantity'].sum()
print(f'Total quantity with missing value: {total_quant_nan}')

# Error Example 3: Grouping by the wrong field (by CustomerID instead of ProductID)
wrong_group = df_orders.groupby('CustomerID')['Quantity'].sum().head()
print(wrong_group)

# End-to-End Analysis: Identify and recommend top and low-performing products
performance = pd.merge(product_sales, product_revenue, on='ProductID')
performance = pd.merge(performance, df_catalog, on='ProductID')
performance = performance.sort_values('Revenue', ascending=False)
top_n = 5
top_products = performance.head(top_n)[['ProductID', 'Category', 'Quantity', 'Revenue']]
low_products = performance.tail(top_n)[['ProductID', 'Category', 'Quantity', 'Revenue']]
print('Top 5 products to promote:')
print(top_products)
print('\nLow-performing products to review or optimize:')
print(low_products)

# Save product performance to CSV for further sharing or visualization
performance.to_csv('product_performance.csv', index=False)
print('File saved: product_performance.csv')