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

# Load real online retail data (UCI repository)
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(df.shape)
print(df.head(3))

# Calculate total sales revenue
df['Sales'] = df['Quantity'] * df['Price']
total_sales = df['Sales'].sum()
print('Total sales revenue:', total_sales)

# Aggregate sales by product description
product_sales = df.groupby('Description')['Sales'].sum()
top_products = product_sales.sort_values(ascending=False).head(5)
print('Top 5 products by sales:')
print(top_products)

# Calculate monthly sales
df['Month'] = df['InvoiceDate'].dt.to_period('M')
monthly_sales = df.groupby('Month')['Sales'].sum()
print('Monthly sales totals:')
print(monthly_sales.head(6))

# Calculate total sales by customer
customer_sales = df.groupby('Customer ID')['Sales'].sum().sort_values(ascending=False)
print('Top 5 customers by sales:')
print(customer_sales.head(5))

# Simulate a product catalog dataset and merge it with sales data
np.random.seed(42)
categories = ['Electronics','Clothing','Home','Sports','Beauty']
product_ids = df['StockCode'].unique()[:100]
product_categories = np.random.choice(categories, len(product_ids))
product_prices = np.round(np.random.uniform(5, 500, len(product_ids)), 2)
catalog = pd.DataFrame({'StockCode': product_ids, 'Category': product_categories, 'CatalogPrice': product_prices})
df_cat = pd.merge(df, catalog, on='StockCode', how='left')
category_sales = df_cat.groupby('Category')['Sales'].sum().sort_values(ascending=False)
print('Sales by category:')
print(category_sales)

# Calculate average order value (AOV)
order_sales = df.groupby('Invoice')['Sales'].sum()
aov = order_sales.mean()
print('Average order value:', round(aov,2))

# Group sales by day of week
df['Weekday'] = df['InvoiceDate'].dt.day_name()
weekday_sales = df.groupby('Weekday')['Sales'].sum().sort_values(ascending=False)
print('Sales by day of week:')
print(weekday_sales)

# Flag and analyze returned orders
df['IsReturn'] = df['Invoice'].astype(str).str.startswith('C')
returns = df[df['IsReturn']]
return_sales = returns['Sales'].sum()
print('Total value of returned orders:', return_sales)

# Check for missing values in key columns
missing = df[['Quantity','Price','Customer ID']].isnull().sum()
print('Missing values in each column:')
print(missing)

# Example: Incorrect grouping (Demo only)
sales_by_date = df.groupby('InvoiceDate')['Sales'].sum().head()
print('Sales summed by invoice datetime:')
print(sales_by_date)

# Simple RFM-style segmentation
last_date = df['InvoiceDate'].max()
rfm = df.groupby('Customer ID').agg({'InvoiceDate':'max','Sales':'sum','Invoice':'count'})
rfm['Recency'] = (last_date - rfm['InvoiceDate']).dt.days
rfm['Frequency'] = rfm['Invoice']
rfm['Monetary'] = rfm['Sales']
rfm_segment = rfm[['Recency','Frequency','Monetary']]
print(rfm_segment.head(5))

# Measure return rate by product
returns_by_prod = returns.groupby('Description')['Sales'].sum().sort_values()
total_by_prod = df.groupby('Description')['Sales'].sum()
return_rate = (returns_by_prod / total_by_prod).fillna(0).sort_values(ascending=False)
print('Products with highest return rates:')
print(return_rate.head(5))

# Create and analyze a simulated market basket transactions dataset
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))
market_basket = pd.DataFrame({'TransactionID': transaction_ids,'Product': product_choices})
pairs = (market_basket.groupby('TransactionID')['Product'].apply(lambda x: tuple(sorted(x))).value_counts())
print('Most common product triplets:')
print(pairs.head(3))

# Calculate a 3-month moving average of sales
monthly_sales_ma = monthly_sales.rolling(3).mean()
print('3-month moving average of sales:')
print(monthly_sales_ma.tail(6))

# End-to-end example: Find and report top 3 products
final_top = product_sales.sort_values(ascending=False).head(3)
print('Recommendation: Increase stock for these products:')
for product, sales in final_top.items():
    print(f'{product}: $ {sales:,.2f}')