import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
import warnings
warnings.filterwarnings('ignore')

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

# Beginner Example 2: Explore Basic Column Types
print(df.dtypes)
print('Unique countries:', df['Country'].nunique())

# Beginner Example 3: Calculate Total Sales Revenue (Retail Transaction Dataset)
df['Revenue'] = df['Quantity'] * df['Price']
print('Revenue range:', df['Revenue'].min(), 'to', df['Revenue'].max())
print(df[['Quantity', 'Price', 'Revenue']].head(3))

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

# Beginner Example 5: Summarize Customer Order Behavior
cust_order_counts = orders['CustomerID'].value_counts().reset_index()
cust_order_counts.columns = ['CustomerID', 'OrderCount']
print(cust_order_counts.head(3))
print('Max orders by a customer:', cust_order_counts['OrderCount'].max())

# Beginner Example 6: Group Orders by Product
prod_qty = orders.groupby('ProductID')['Quantity'].sum().reset_index()
prod_qty = prod_qty.sort_values('Quantity', ascending=False)
print(prod_qty.head(3))

# Intermediate Example 1: Generate Time-Based Sales Trends
orders['OrderDate'] = pd.to_datetime(orders['OrderDate'])
orders['OrderDay'] = orders['OrderDate'].dt.date
daily_sales = orders.groupby('OrderDay')['Quantity'].sum().reset_index()
plt.figure(figsize=(10,5))
sns.lineplot(data=daily_sales, x='OrderDay', y='Quantity')
plt.title('Total Quantity Sold Per Day')
plt.xlabel('Order Day')
plt.ylabel('Quantity Sold')
plt.xticks(rotation=45)
plt.tight_layout()
plt.show()

# Intermediate Example 2: Plot Orders by Hour to Find Peak Times
orders['OrderHour'] = orders['OrderDate'].dt.hour
hourly_counts = orders.groupby('OrderHour').size().reset_index(name='OrderCount')
plt.figure(figsize=(8,4))
sns.barplot(data=hourly_counts, x='OrderHour', y='OrderCount', color='royalblue')
plt.title('Order Count by Hour of Day')
plt.xlabel('Hour of Day')
plt.ylabel('Number of Orders')
plt.show()

# Intermediate Example 3: Average Order Quantity per Customer
customer_mean = orders.groupby('CustomerID')['Quantity'].mean().reset_index()
customer_mean = customer_mean.sort_values('Quantity', ascending=False)
print(customer_mean.head(5))

# Intermediate Example 4: Connect Orders to Product Catalog
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 = pd.DataFrame({'ProductID': product_ids_cat, 'Category': product_categories, 'Price': product_prices})
orders_merged = orders.merge(catalog, on='ProductID', how='left')
print(orders_merged.head(3))

# Intermediate Example 5: Compute Revenue per Product Category
orders_merged['Revenue'] = orders_merged['Quantity'] * orders_merged['Price']
cat_revenue = orders_merged.groupby('Category')['Revenue'].sum().reset_index()
cat_revenue = cat_revenue.sort_values('Revenue', ascending=False)
print(cat_revenue)
plt.figure(figsize=(7,4))
sns.barplot(data=cat_revenue, x='Category', y='Revenue', palette='viridis')
plt.title('Revenue by Product Category')
plt.ylabel('Revenue')
plt.xlabel('Product Category')
plt.show()

# Intermediate Example 6: Identify Repeat Customers (Simple RFM)
order_counts = orders.groupby('CustomerID').size().reset_index(name='Frequency')
recent_order = orders.groupby('CustomerID')['OrderDate'].max().reset_index()
rfm = order_counts.merge(recent_order, on='CustomerID', how='left')
rfm = rfm.sort_values(['Frequency', 'OrderDate'], ascending=[False, False])
print(rfm.head(5))

# Advanced Example 1: Finding Top 5 High-Value Customers
high_value = orders_merged.groupby('CustomerID')['Revenue'].sum().reset_index()
high_value = high_value.sort_values('Revenue', ascending=False).head(5)
print('Top 5 high-value customers:')
print(high_value)

# Advanced Example 2: Monthly Sales Heatmap
orders_merged['YearMonth'] = orders_merged['OrderDate'].dt.to_period('M')
month_cat_sales = orders_merged.groupby(['YearMonth','Category'])['Revenue'].sum().reset_index()
pivot_tab = month_cat_sales.pivot(index='YearMonth', columns='Category', values='Revenue').fillna(0)
plt.figure(figsize=(10,6))
sns.heatmap(pivot_tab, annot=True, fmt='.0f', cmap='YlGnBu', cbar_kws={'label': 'Revenue'})
plt.title('Monthly Revenue Heatmap by Category')
plt.ylabel('Year-Month')
plt.xlabel('Product Category')
plt.tight_layout()
plt.show()

# Advanced Example 3: Market Basket Summary
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))
baskets = pd.DataFrame({'TransactionID': transaction_ids, 'Product': product_choices})
transaction_sets = baskets.groupby('TransactionID')['Product'].apply(list)
print(transaction_sets.head(3))

# Advanced Example 4: Error Handling - Missing Values
missing_counts = df.isnull().sum()
print('Missing values in retail transaction data:')
print(missing_counts)

# Advanced Example 5: Debug Aggregation Mistakes - Incorrect Grouping
revenue_by_date = df.groupby('InvoiceDate')['Revenue'].sum()
print('Revenue grouped correctly by InvoiceDate.')

# Incorrect: group by Description only (could be misleading)
wrong_agg = df.groupby('Description')['Revenue'].sum().sort_values(ascending=False)
print('Revenue by product (top 3):')
print(wrong_agg.head(3))

# Advanced Example 6: Debug Revenue Calculation - Negative Quantities?
neg_qty = df[df['Quantity'] < 0]
print('Transactions with negative quantity:')
print(neg_qty[['Invoice', 'Quantity', 'Description', 'Revenue']].head())

# End-to-End Example: Build a Simple Retail Sales Dashboard
category_sales = orders_merged.groupby('Category')['Revenue'].sum().reset_index()
date_sales = orders_merged.groupby('OrderDay')['Revenue'].sum().reset_index()
fig, axs = plt.subplots(1,2, figsize=(13,5))
sns.barplot(data=category_sales, x='Category', y='Revenue', ax=axs[0], palette='viridis')
axs[0].set_title('Total Revenue by Category')
axs[0].set_ylabel('Revenue')
axs[0].set_xlabel('Product Category')
sns.lineplot(data=date_sales, x='OrderDay', y='Revenue', ax=axs[1], color='teal')
axs[1].set_title('Revenue Trend Over Time')
axs[1].set_ylabel('Revenue')
axs[1].set_xlabel('Date')
plt.tight_layout()
plt.show()