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

# Example 1: Load an online retail transactions dataset
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))

# Example 2: Check for missing values in price and quantity
missing_prices = df['Price'].isnull().sum()
missing_quantities = df['Quantity'].isnull().sum()
print('Missing price entries:', missing_prices)
print('Missing quantity entries:', missing_quantities)

# Example 3: Remove transactions with non-positive quantities or prices
clean_df = df[(df['Quantity'] > 0) & (df['Price'] > 0)].copy()
print('Shape after removing negative values:', clean_df.shape)

# Example 4: Calculate basic revenue field for each line item
clean_df['Revenue'] = clean_df['Quantity'] * clean_df['Price']
print(clean_df[['Invoice', 'StockCode', 'Quantity', 'Price', 'Revenue']].head(5))

# Example 5: Simulate a discount field (10% for demonstration)
discount_rate = 0.10
clean_df['Discount'] = clean_df['Revenue'] * discount_rate
clean_df['Revenue_after_discount'] = clean_df['Revenue'] - clean_df['Discount']
print(clean_df[['Revenue', 'Discount', 'Revenue_after_discount']].head(5))

# Example 6: Compute total gross and net revenue for the whole dataset
gross_revenue = clean_df['Revenue'].sum()
net_revenue = clean_df['Revenue_after_discount'].sum()
print('Total Gross Revenue:', round(gross_revenue, 2))
print('Total Net Revenue after Discounts:', round(net_revenue, 2))

# Example 7: Aggregate revenue by product code
product_revenue = clean_df.groupby('StockCode').agg({'Revenue':'sum', 'Revenue_after_discount':'sum'}).reset_index()
print(product_revenue.head(5))

# Example 8: Sort and display top 5 products by net revenue
top_products = product_revenue.sort_values(by='Revenue_after_discount', ascending=False).head(5)
print(top_products)

# Example 9: Simulate an order table with multiple product lines
np.random.seed(42)
orders = clean_df[['Invoice', 'Customer ID', 'InvoiceDate']].drop_duplicates().reset_index(drop=True)
orders['OrderID'] = range(1, len(orders)+1)
clean_df = pd.merge(clean_df, orders[['Invoice', 'OrderID']], on='Invoice', how='left')
print(clean_df[['OrderID', 'Invoice', 'StockCode', 'Revenue_after_discount']].head(5))

# Example 10: Compute order value (sum revenue lines per order)
order_values = clean_df.groupby('OrderID')['Revenue_after_discount'].sum().reset_index(name='OrderValue')
print(order_values.head(5))

# Example 11: Calculate average order value (AOV)
avg_order_value = order_values['OrderValue'].mean()
print('Average Order Value:', round(avg_order_value, 2))

# Example 12: Aggregate revenue by customer
customer_revenue = clean_df.groupby('Customer ID')['Revenue_after_discount'].sum().reset_index().rename(columns={'Revenue_after_discount':'TotalRevenue'})
print(customer_revenue.head(5))

# Example 13: Identify top 5 customers by net revenue
top_customers = customer_revenue.sort_values(by='TotalRevenue', ascending=False).head(5)
print(top_customers)

# Example 14: Detect and handle missing or obviously invalid entries again
missing_customer = clean_df['Customer ID'].isnull().sum()
print('Transactions without customer ID:', missing_customer)
clean_df_valid = clean_df[clean_df['Customer ID'].notnull()]

# Example 15: Debug common revenue grouping mistake (grouping by wrong field)
incorrect_group = clean_df.groupby('Invoice')['Revenue_after_discount'].mean()
correct_group = clean_df.groupby('Invoice')['Revenue_after_discount'].sum()
print('Example incorrect (mean):', incorrect_group.head(3).values)
print('Example correct (sum):', correct_group.head(3).values)

# Example 16: Aggregate revenue by country to spot regional sales patterns
country_revenue = clean_df.groupby('Country')['Revenue_after_discount'].sum().sort_values(ascending=False).reset_index()
print(country_revenue.head(5))

# Example 17: Monthly revenue trend analysis
clean_df['Month'] = clean_df['InvoiceDate'].dt.month
monthly_revenue = clean_df.groupby('Month')['Revenue_after_discount'].sum().reset_index()
print(monthly_revenue)

# Example 18: End-to-end problem: Identify top-selling products in a specific country after discounts
target_country = 'United Kingdom'
country_df = clean_df[clean_df['Country'] == target_country]
prod_sales = country_df.groupby('StockCode')['Revenue_after_discount'].sum().sort_values(ascending=False).head(5)
print(f'Top products in {target_country}:')
print(prod_sales)

# Example 19: Segment customers by total spend after discounts (basic RFM segmentation step)
customer_spend = customer_revenue.copy()
customer_spend['Segment'] = pd.qcut(customer_spend['TotalRevenue'], 3, labels=['Low', 'Medium', 'High'])
print(customer_spend.groupby('Segment').size())

# Example 20: Calculate the impact of a category-specific discount (simulate 20% on 'Discount Product')
sample_product = clean_df['StockCode'].value_counts().index[0]
clean_df['CategoryDiscount'] = np.where(clean_df['StockCode'] == sample_product, clean_df['Revenue'] * 0.2, 0)
clean_df['Revenue_after_category_discount'] = clean_df['Revenue_after_discount'] - clean_df['CategoryDiscount']
cat_disc_revenue = clean_df.loc[clean_df['StockCode'] == sample_product, 'Revenue_after_category_discount'].sum()
print(f'Total revenue for top product after new category discount: {cat_disc_revenue:.2f}')

# Example 21: Challenge - Compute median quantity per product and discuss implications
median_qty = clean_df.groupby('StockCode')['Quantity'].median().reset_index()
print(median_qty.sort_values('Quantity', ascending=False).head(5))