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

# Beginner Example 1: Load the Online Retail Transactions Dataset
url = 'https://archive.ics.uci.edu/ml/machine-learning-databases/00502/online_retail_II.xlsx'
online_retail_df = pd.read_excel(url, sheet_name='Year 2010-2011')
online_retail_df['InvoiceDate'] = pd.to_datetime(online_retail_df['InvoiceDate'])
print(online_retail_df.shape)
print(online_retail_df.head(3))

# Beginner Example 2: Loading Synthetic Retail Customer Orders Data
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')
orders_df = pd.DataFrame({'OrderID': order_ids, 'CustomerID': customer_ids, 'ProductID': product_ids, 'Quantity': quantities, 'OrderDate': order_dates})
print(orders_df.shape)
print(orders_df.head(3))

# Beginner Example 3: Basic Statistics on Retail Orders
print('Number of unique customers:', orders_df['CustomerID'].nunique())
print('Number of unique products ordered:', orders_df['ProductID'].nunique())
print('Average products per order:', orders_df['Quantity'].mean())

# Beginner Example 4: Find the Most Common Product Ordered
top_product_id = orders_df['ProductID'].mode()[0]
top_product_count = (orders_df['ProductID'] == top_product_id).sum()
print(f'The most commonly ordered product ID: {top_product_id}')
print(f'Ordered {top_product_count} times')

# Beginner Example 5: Total Quantity Ordered for Each Product
product_sales = orders_df.groupby('ProductID')['Quantity'].sum().reset_index()
product_sales = product_sales.sort_values('Quantity', ascending=False)
print(product_sales.head())

# Intermediate Example 1: Calculate Orders per Customer
orders_per_customer = orders_df.groupby('CustomerID')['OrderID'].nunique().reset_index()
orders_per_customer = orders_per_customer.rename(columns={'OrderID':'NumOrders'})
print(orders_per_customer.head())
print('Average orders per customer:', orders_per_customer['NumOrders'].mean())

# Intermediate Example 2: Find Repeat vs. One-Time Customers
repeat_customers = orders_per_customer[orders_per_customer['NumOrders'] > 1].shape[0]
one_time_customers = orders_per_customer[orders_per_customer['NumOrders'] == 1].shape[0]
print(f'Repeat customers: {repeat_customers}')
print(f'One-time customers: {one_time_customers}')

# Intermediate Example 3: Add a Product Catalog and Join Price Info
categories = ['Electronics','Clothing','Home','Sports','Beauty']
product_ids_cat = list(range(1001,1101))
product_categories = np.random.choice(categories, 100)
product_prices = np.round(np.random.uniform(5,500,100),2)
catalog_df = pd.DataFrame({'ProductID':product_ids_cat,'Category':product_categories,'Price':product_prices})
orders_merged = orders_df.merge(catalog_df, on='ProductID', how='left')
print(orders_merged.head(3))

# Intermediate Example 4: Calculate Total Revenue per Order
orders_merged['OrderRevenue'] = orders_merged['Quantity'] * orders_merged['Price']
order_revenue_table = orders_merged.groupby('OrderID')['OrderRevenue'].sum().reset_index()
print(order_revenue_table.head(3))

# Intermediate Example 5: Average Order Value by Customer
customer_avg_order_value = orders_merged.groupby('CustomerID')['OrderRevenue'].mean().reset_index()
customer_avg_order_value = customer_avg_order_value.rename(columns={'OrderRevenue':'AvgOrderValue'})
print(customer_avg_order_value.sort_values('AvgOrderValue', ascending=False).head())

# Advanced Example 1: Sales by Product Category
category_sales = orders_merged.groupby('Category')['OrderRevenue'].sum().reset_index()
category_sales = category_sales.sort_values('OrderRevenue', ascending=False)
print(category_sales)

# Advanced Example 2: Time-Based Revenue Trend (Weekly)
orders_merged['Week'] = orders_merged['OrderDate'].dt.isocalendar().week
weekly_sales = orders_merged.groupby('Week')['OrderRevenue'].sum().reset_index()
print(weekly_sales.head())

# Advanced Example 3: Top 5 Customers by Total Revenue
customer_total_revenue = orders_merged.groupby('CustomerID')['OrderRevenue'].sum().reset_index()
top5_customers = customer_total_revenue.sort_values('OrderRevenue', ascending=False).head(5)
print(top5_customers)

# Advanced Example 4: Product Category Performance Over Time
orders_merged['Month'] = orders_merged['OrderDate'].dt.strftime('%Y-%m')
cat_month_sales = orders_merged.groupby(['Category','Month'])['OrderRevenue'].sum().reset_index()
print(cat_month_sales.head(10))

# Advanced Example 5: Calculate Customer Retention Rate
first_purchase = orders_merged.groupby('CustomerID')['OrderDate'].min().reset_index()
latest_purchase = orders_merged.groupby('CustomerID')['OrderDate'].max().reset_index()
retained_customers = (first_purchase['OrderDate'] != latest_purchase['OrderDate']).sum()
total_customers = orders_merged['CustomerID'].nunique()
retention_rate = retained_customers / total_customers
print(f'Customer retention rate: {retention_rate:.2%}')

# Error Example 1: Handling Missing Product Prices
orders_merged['Price'] = orders_merged['Price'].fillna(orders_merged['Price'].mean())
print('Any missing prices after fill:', orders_merged['Price'].isnull().any())

# Error Example 2: Prevent Incorrect Grouping by OrderID and ProductID
mixed_group = orders_merged.groupby(['OrderID', 'ProductID'])['Quantity'].sum().reset_index()
print(mixed_group.head())

# Error Example 3: Demonstrate Incorrect Revenue Calculation
incorrect_revenue = orders_merged.groupby('OrderID')['Price'].sum().reset_index()
print(incorrect_revenue.head())

# Best Practice 1: Identify High-Value Customer Segments
customer_total = orders_merged.groupby('CustomerID')['OrderRevenue'].sum().reset_index()
high_value = customer_total[customer_total['OrderRevenue'] > customer_total['OrderRevenue'].quantile(0.75)]
print('Number of high-value customers:', high_value.shape[0])

# Best Practice 2: Find Top-Performing Products per Category
top_products_per_category = orders_merged.groupby(['Category', 'ProductID'])['OrderRevenue'].sum().reset_index()
top_products_per_category = top_products_per_category.sort_values(['Category','OrderRevenue'], ascending=[True, False]).groupby('Category').head(1)
print(top_products_per_category)

# Best Practice 3: Market BasketMost Common Product Pairings
pair_counts = orders_merged.groupby('OrderID')['ProductID'].apply(list)
from collections import Counter
def all_pairs(product_list):
    return [(min(a,b), max(a,b)) for idx,a in enumerate(product_list) for b in product_list[idx+1:]]
all_pairs_list = pair_counts.apply(all_pairs).explode().dropna()
pair_counter = Counter(all_pairs_list)
common_pairs = pair_counter.most_common(5)
print(common_pairs)

# End-to-End Retail Analytics Problem: Identify Top-Selling Products for Inventory Restock
# Step 1: Sum order quantity for each product
top_products = orders_merged.groupby('ProductID')['Quantity'].sum().reset_index()
# Step 2: Attach product category and price from catalog
top_products = top_products.merge(catalog_df, on='ProductID', how='left')
# Step 3: Get the top 5 products by ordered quantity
top_products = top_products.sort_values('Quantity', ascending=False).head(5)
print('Top 5 products to consider for restocking:')
print(top_products[['ProductID','Category','Price','Quantity']])