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

# Beginner Example 1: Load product catalog (synthetic)
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)
catalog = pd.DataFrame({'ProductID':product_ids,'Category':product_categories,'Price':product_prices})
print(catalog.shape)
print(catalog.head(3))

# Beginner Example 2: Simulate orders linked to products
np.random.seed(42)
n_orders = 1000
order_ids = list(range(1, n_orders+1))
customer_ids = np.random.randint(1000,1500,n_orders)
order_product_ids = np.random.randint(1001,1101,n_orders)
quantities = np.random.randint(1,5,n_orders)
order_dates = pd.date_range('2023-01-01', periods=n_orders, freq='h')
orders = pd.DataFrame({'OrderID':order_ids,'CustomerID':customer_ids,'ProductID':order_product_ids,'Quantity':quantities,'OrderDate':order_dates})
print(orders.shape)
print(orders.head(3))

# Beginner Example 3: Merge orders and products
merged = pd.merge(orders, catalog, on='ProductID', how='left')
print(merged.shape)
print(merged[['OrderID','ProductID','Category','Quantity','Price']].head(3))

# Beginner Example 4: Calculate revenue per order item
merged['Revenue'] = merged['Quantity'] * merged['Price']
print(merged[['ProductID','Category','Quantity','Price','Revenue']].head())

# Beginner Example 5: Sum total sales revenue
total_revenue = merged['Revenue'].sum()
print('Total Revenue across all sales: $', round(total_revenue,2))

# Intermediate Example 1: Aggregate sales by product
sales_by_product = merged.groupby('ProductID').agg({'Revenue':'sum', 'Quantity':'sum'}).reset_index()
sales_by_product = sales_by_product.merge(catalog[['ProductID','Category']], on='ProductID', how='left')
print(sales_by_product.head(5))

# Intermediate Example 2: Aggregate sales by category
sales_by_category = merged.groupby('Category').agg({'Revenue':'sum', 'Quantity':'sum'}).reset_index()
print(sales_by_category.sort_values('Revenue', ascending=False))

# Intermediate Example 3: Top 5 products by revenue
top_products = sales_by_product.sort_values('Revenue', ascending=False).head(5)
print(top_products[['ProductID','Category','Revenue','Quantity']])

# Intermediate Example 4: Average order value (AOV)
aov = merged.groupby('OrderID')['Revenue'].sum().mean()
print('Average Order Value: $', round(aov,2))

# Intermediate Example 5: Sales over time by category
merged['OrderDate'] = pd.to_datetime(merged['OrderDate'])
time_cat = merged.groupby([merged['OrderDate'].dt.date, 'Category'])['Revenue'].sum().unstack('Category', fill_value=0)
print(time_cat.tail())

# Advanced Example 1: Identify top customers by revenue
customer_sales = merged.groupby('CustomerID')['Revenue'].sum().reset_index().sort_values('Revenue', ascending=False)
print(customer_sales.head(5))

# Advanced Example 2: Cross-tab products vs categories by revenue
product_cat_sales = pd.pivot_table(merged, values='Revenue', index='ProductID', columns='Category', aggfunc='sum', fill_value=0)
print(product_cat_sales.head(8))

# Advanced Example 3: Calculate category revenue share
total_rev = sales_by_category['Revenue'].sum()
sales_by_category['RevenueShare'] = round(100 * sales_by_category['Revenue'] / total_rev, 2)
print(sales_by_category[['Category','RevenueShare']])

# Advanced Example 4: Detect products with sales variance across categories
variance = sales_by_product.groupby('Category')['Revenue'].var().reset_index()
print('Sales Revenue Variance by Category:')
print(variance)

# Error Handling Example 1: Handling missing values
merged_missing = merged.copy()
merged_missing.loc[merged_missing.sample(frac=0.02, random_state=42).index, 'Revenue'] = np.nan
missing_count = merged_missing['Revenue'].isna().sum()
print('Rows with missing Revenue:', missing_count)
merged_missing['Revenue'].fillna(0, inplace=True)
print('Missing revenues set to 0. Check:', merged_missing['Revenue'].isna().sum())

# Error Handling Example 2: Check for negative quantities or prices
neg_qty = (merged['Quantity'] < 0).sum()
neg_price = (merged['Price'] < 0).sum()
print('Negative Quantities:', neg_qty)
print('Negative Prices:', neg_price)

# Error Handling Example 3: Trap incorrect groupings
incorrect_group = merged.groupby('Price').agg({'Revenue':'sum'})
print('Sales grouped only by Price (incorrect):')
print(incorrect_group.head(3))
correct_group = merged.groupby('ProductID').agg({'Revenue':'sum'})
print('Sales grouped by ProductID (correct):')
print(correct_group.head(3))

# End-to-End Problem: Identify top category and product with actionable insight
top_cat = sales_by_category.sort_values('Revenue', ascending=False).iloc[0]
top_prod = sales_by_product.sort_values('Revenue', ascending=False).iloc[0]
print('TOP-PERFORMING CATEGORY: {} (${:.2f} revenue)'.format(top_cat['Category'], top_cat['Revenue']))
print('TOP-PERFORMING PRODUCT: {} (${:.2f} revenue)'.format(top_prod['ProductID'], top_prod['Revenue']))
print('Business recommendation: Focus your next promotion on {} products and feature ProductID {} prominently.'.format(top_cat['Category'], top_prod['ProductID']))

# Bonus: Plot category revenue with Matplotlib
import matplotlib.pyplot as plt
plt.figure(figsize=(7,5))
plt.bar(sales_by_category['Category'], sales_by_category['Revenue'], color='skyblue')
plt.title('Total Revenue by Category')
plt.ylabel('Revenue ($)')
plt.xlabel('Category')
plt.show()