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

url = 'https://archive.ics.uci.edu/ml/machine-learning-databases/00502/online_retail_II.xlsx'
df_transactions = pd.read_excel(url, sheet_name='Year 2010-2011')
df_transactions['InvoiceDate'] = pd.to_datetime(df_transactions['InvoiceDate'])
print(df_transactions.shape)
print(df_transactions.head(3))

# Simulate and save a simple product catalog to CSV
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)
df_catalog = pd.DataFrame({'ProductID':product_ids,'Category':product_categories,'Price':product_prices})
df_catalog.to_csv('product_catalog.csv', index=False)
print(df_catalog.shape)

# Now read the product catalog CSV into pandas
df_products = pd.read_csv('product_catalog.csv')
print(df_products.head(3))

# Simulate and save simple retail orders to CSV
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')
df_orders = pd.DataFrame({'OrderID':order_ids,'CustomerID':customer_ids,'ProductID':product_ids,'Quantity':quantities,'OrderDate':order_dates})
df_orders.to_csv('customer_orders.csv', index=False)
print(df_orders.shape)

# Read customer orders CSV to DataFrame
df_orders_loaded = pd.read_csv('customer_orders.csv', parse_dates=['OrderDate'])
print(df_orders_loaded.head(3))

# Merge orders with product catalog
orders_products = pd.merge(df_orders_loaded, df_products, on='ProductID', how='left')
# Calculate revenue for each order line
orders_products['Revenue'] = orders_products['Quantity'] * orders_products['Price']
print(orders_products.head(3))

# Group by ProductID and summarize sales
product_sales_summary = orders_products.groupby('ProductID').agg({'Quantity':'sum','Revenue':'sum'}).reset_index()
product_sales_summary = pd.merge(product_sales_summary, df_products[['ProductID','Category']], on='ProductID', how='left')
print(product_sales_summary.sort_values('Revenue', ascending=False).head())

# Extract month and summarize sales
orders_products['Month'] = orders_products['OrderDate'].dt.to_period('M')
monthly_sales = orders_products.groupby('Month')['Revenue'].sum().reset_index()
print(monthly_sales.head())

# Aggregate revenue by customer
customer_lifetime = orders_products.groupby('CustomerID')['Revenue'].sum().reset_index()
customer_lifetime = customer_lifetime.sort_values('Revenue', ascending=False)
print(customer_lifetime.head(5))

# Calculate total revenue and unique order count
total_revenue = orders_products['Revenue'].sum()
unique_orders = orders_products['OrderID'].nunique()
aov = total_revenue / unique_orders
print('Average Order Value: {:.2f}'.format(aov))

# Aggregate revenue by category
category_sales = orders_products.groupby('Category')['Revenue'].sum().reset_index()
total_category_revenue = category_sales['Revenue'].sum()
category_sales['PercentOfTotal'] = 100 * category_sales['Revenue'] / total_category_revenue
print(category_sales.sort_values('Revenue', ascending=False))

# Introduce missing values into revenue
orders_products_with_missing = orders_products.copy()
orders_products_with_missing.loc[orders_products_with_missing.sample(frac=0.01, random_state=42).index, 'Revenue'] = np.nan
# Count missing revenue values
num_missing = orders_products_with_missing['Revenue'].isna().sum()
print(f'Missing revenue entries: {num_missing}')

# Aggregate by category only (incorrect example)
wrong_group = orders_products.groupby('Category')['Revenue'].sum().reset_index()
top_category = wrong_group.sort_values('Revenue', ascending=False).head(1)
print(top_category)

# Find product with highest quantity, not revenue
most_sold = product_sales_summary.sort_values('Quantity', ascending=False).head(1)
print('Most sold product by quantity:')
print(most_sold[['ProductID','Quantity','Revenue','Category']])

# Use quantiles to segment customers
spend_quantiles = customer_lifetime['Revenue'].quantile([0.33, 0.66]).values
def segment_customer(spend):
    if spend < spend_quantiles[0]:
        return 'Low'
    elif spend < spend_quantiles[1]:
        return 'Medium'
    else:
        return 'High'
customer_lifetime['Segment'] = customer_lifetime['Revenue'].apply(segment_customer)
print(customer_lifetime['Segment'].value_counts())

# Report bottom 5 products by revenue
bottom_products = product_sales_summary.sort_values('Revenue').head(5)
print(bottom_products[['ProductID','Revenue','Category']])

# Find top 3 products by revenue and give advice
top3 = product_sales_summary.sort_values('Revenue', ascending=False).head(3)
print('Top 3 Products by Revenue:')
print(top3[['ProductID','Revenue','Category']])