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 dataset
url = 'https://archive.ics.uci.edu/ml/machine-learning-databases/00502/online_retail_II.xlsx'
ori_df = pd.read_excel(url, sheet_name='Year 2010-2011')
ori_df['InvoiceDate'] = pd.to_datetime(ori_df['InvoiceDate'])
print(ori_df.shape)
print(ori_df.head(3))

# Beginner Example 2: Load a simulated Retail Product Catalog
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)
prodcat_df = pd.DataFrame({'ProductID':product_ids,'Category':product_categories,'Price':product_prices})
print(prodcat_df.shape)
print(prodcat_df.head(3))

# Beginner Example 3: Load Simulated Customer Orders
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,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':order_product_ids,'Quantity':quantities,'OrderDate':order_dates})
print(orders_df.shape)
print(orders_df.head(3))

# Beginner Example 4: Calculate total revenue in online retail transactions
ori_df['Revenue'] = ori_df['Quantity'] * ori_df['Price']
total_revenue = ori_df['Revenue'].sum()
print(f'Total Revenue: {total_revenue:,.2f}')

# Beginner Example 5: Find the top 5 selling products by quantity
top_products = ori_df.groupby('Description')['Quantity'].sum().sort_values(ascending=False).head(5)
print(top_products)

# Beginner Example 6: Average order value (AOV) for simulated orders
merged = pd.merge(orders_df, prodcat_df, how='left', left_on='ProductID', right_on='ProductID')
merged['LineRevenue'] = merged['Quantity'] * merged['Price']
aov = merged.groupby('OrderID')['LineRevenue'].sum().mean()
print(f'Average Order Value (AOV): {aov:,.2f}')

# Intermediate Example 1: Revenue by product category
merged['Category'] = merged['Category'].fillna('Unknown')
category_revenue = merged.groupby('Category')['LineRevenue'].sum().sort_values(ascending=False)
print(category_revenue)

# Intermediate Example 2: Identify repeat customers in simulated orders
customer_order_counts = orders_df.groupby('CustomerID')['OrderID'].nunique()
repeat_customers = customer_order_counts[customer_order_counts > 1].count()
total_customers = customer_order_counts.count()
repeat_pct = (repeat_customers / total_customers) * 100
print(f'Repeat customers: {repeat_customers} of {total_customers} ({repeat_pct:.2f}%)')

# Intermediate Example 3: Monthly sales trend over time
ori_df['Month'] = ori_df['InvoiceDate'].dt.to_period('M')
monthly_sales = ori_df.groupby('Month')['Revenue'].sum()
monthly_sales.plot(kind='line', marker='o', figsize=(10,4))
plt.title('Monthly Sales Trend')
plt.xlabel('Month')
plt.ylabel('Revenue')
plt.show()

# Intermediate Example 4: Top customers by total spend
customer_revenue = merged.groupby('CustomerID')['LineRevenue'].sum().sort_values(ascending=False)
print(customer_revenue.head(5))

# Intermediate Example 5: Plot distribution of order sizes (quantity per order)
order_sizes = orders_df.groupby('OrderID')['Quantity'].sum()
sns.histplot(order_sizes, bins=10, kde=True)
plt.title('Distribution of Order Sizes')
plt.xlabel('Items per Order')
plt.ylabel('Number of Orders')
plt.show()

# Advanced Example 1: Basket analysis - Most frequently co-purchased products
basket_df = ori_df[ori_df['Invoice'].duplicated(keep=False)]
invoice_products = basket_df.groupby('Invoice')['Description'].apply(list)
from collections import Counter
pair_counter = Counter()
for prod_list in invoice_products:
    prod_set = set(prod_list)
    for prod1 in prod_set:
        for prod2 in prod_set:
            if prod1 < prod2:
                pair_counter[(prod1, prod2)] += 1
print(pair_counter.most_common(5))

# Advanced Example 2: Simple product demand forecasting using rolling sales average
ori_df_sorted = ori_df.sort_values('InvoiceDate')
product_demand = ori_df_sorted.groupby(['Description','InvoiceDate'])['Quantity'].sum().reset_index()
forecast_product = product_demand[product_demand['Description']==product_demand['Description'].iloc[0]].copy()
forecast_product['7D_MA'] = forecast_product['Quantity'].rolling(window=7, min_periods=1).mean()
plt.figure(figsize=(12,5))
plt.plot(forecast_product['InvoiceDate'], forecast_product['Quantity'], label='Daily Sales')
plt.plot(forecast_product['InvoiceDate'], forecast_product['7D_MA'], label='7-Day Moving Avg', linewidth=3)
plt.title(f"Demand Forecast for {forecast_product['Description'].iloc[0]}")
plt.xlabel('Date')
plt.ylabel('Units Sold')
plt.legend()
plt.show()

# Advanced Example 3: Customer segmentation using total spend (K-Means clustering)
from sklearn.cluster import KMeans
spend_df = customer_revenue.reset_index().rename(columns={'LineRevenue':'TotalSpend'})
X = spend_df[['TotalSpend']]
kmeans = KMeans(n_clusters=3, random_state=42)
spend_df['Segment'] = kmeans.fit_predict(X)
sns.boxplot(data=spend_df, x='Segment', y='TotalSpend')
plt.title('Customer Segments by Total Spend')
plt.xlabel('Segment')
plt.ylabel('Total Spend')
plt.show()

# Error Handling Example 1: Check and handle missing prices in product catalog
missing_price = prodcat_df['Price'].isnull().sum()
if missing_price > 0:
    print(f'Missing product prices: {missing_price}')
    prodcat_df['Price'] = prodcat_df['Price'].fillna(prodcat_df['Price'].median())
    print('Filled missing prices with median. New null count:', prodcat_df['Price'].isnull().sum())
else:
    print('No missing product prices.')

# Error Handling Example 2: Preventing double-counting when aggregating quantity
test_sku = ori_df['Description'].iloc[0]
sku_orders = ori_df[ori_df['Description']==test_sku].groupby('Invoice')['Quantity'].sum()
print(sku_orders.head())

# Error Handling Example 3: Checking negative quantities (possible returns or entry errors)
neg_qty = ori_df[ori_df['Quantity'] < 0]
print(f'Negative quantity transactions: {neg_qty.shape[0]}')
print(neg_qty.head(2))

# Error Handling Example 4: Detecting incorrect revenue calculations
invalid_revenue = ori_df[(ori_df['Revenue'] != ori_df['Quantity'] * ori_df['Price'])]
print(f'Rows with incorrect revenue: {invalid_revenue.shape[0]}')

# Pattern Example: Repeat vs. one-time buyers analysis
orders_df['RepeatBuyer'] = orders_df.duplicated(subset=['CustomerID'], keep=False)
repeat_counts = orders_df.groupby('RepeatBuyer')['OrderID'].nunique()
print(repeat_counts)

# Pattern Example: Product performance report by category
cat_perf = merged.groupby('Category')['LineRevenue'].agg(['sum','mean','count'])
cat_perf = cat_perf.rename(columns={'sum':'TotalRevenue','mean':'AvgRevenue','count':'NumLines'})
print(cat_perf.sort_values('TotalRevenue', ascending=False))

# Pattern Example: Simple seasonality detection for top-selling product
top_prod = top_products.index[0]
tp_sales = ori_df[ori_df['Description']==top_prod].copy()
tp_sales.set_index('InvoiceDate', inplace=True)
monthly_tp = tp_sales.resample('M')['Quantity'].sum()
monthly_tp.plot(marker='o')
plt.title(f'Seasonality for Top Product: {top_prod}')
plt.ylabel('Units Sold')
plt.xlabel('Month')
plt.show()

# END-TO-END MINI-PROBLEM: Find high-value and low-value customers
spend_per_customer = merged.groupby('CustomerID')['LineRevenue'].sum().sort_values(ascending=False)
top_customers = spend_per_customer.head(3)
low_customers = spend_per_customer.tail(3)
print('Top 3 Customers by Total Spend:')
print(top_customers)
print('\nLowest 3 Customers by Total Spend:')
print(low_customers)
recommendation = 'Focus loyalty campaigns and special offers on high-value customers. Explore ways to increase engagement from low-value or new customers.'
print('Recommendation:', recommendation)