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

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

retail_df['Revenue'] = retail_df['Quantity'] * retail_df['Price']
total_revenue = retail_df['Revenue'].sum()
print('Total Revenue: {:,.2f}'.format(total_revenue))

daily_sales = retail_df.groupby(retail_df['InvoiceDate'].dt.date)['Revenue'].sum()
plt.figure(figsize=(12,6))
plt.plot(daily_sales.index, daily_sales.values, marker='o', linestyle='-')
plt.title('Daily Retail Revenue Over Time')
plt.xlabel('Date')
plt.ylabel('Daily Revenue ()')
plt.grid(True)
plt.tight_layout()
plt.show()

# Create a simulated product catalog with categories
np.random.seed(42)
categories = ['Electronics','Clothing','Home','Sports','Beauty']
prod_ids = list(range(1001, 1101))
prod_cats = np.random.choice(categories, 100)
prod_prices = np.round(np.random.uniform(5, 500, 100),2)
product_catalog = pd.DataFrame({'StockCode':prod_ids, 'Category':prod_cats, 'Price':prod_prices})
 
# Map StockCode in retail_df to Category
retail_df = pd.merge(retail_df, product_catalog[['StockCode','Category']], on='StockCode', how='left')

category_sales = retail_df.groupby('Category')['Revenue'].sum().sort_values(ascending=False)
print('Revenue by Product Category:')
print(category_sales)

plt.figure(figsize=(8,6))
sns.barplot(x=category_sales.values, y=category_sales.index, palette='Blues_d')
plt.title('Revenue by Product Category')
plt.xlabel('Total Revenue ()')
plt.ylabel('Product Category')
plt.tight_layout()
plt.show()

order_revenue = retail_df.groupby(['Invoice', 'Country'])['Revenue'].sum().reset_index()
country_avg_order = order_revenue.groupby('Country')['Revenue'].mean().sort_values(ascending=False)
print('Average Order Value by Country:')
print(country_avg_order.head(10))

plt.figure(figsize=(10,5))
sns.barplot(x=country_avg_order.values[:10], y=country_avg_order.index[:10], palette='viridis')
plt.title('Top 10 Countries by Average Order Value')
plt.xlabel('Average Order Value ()')
plt.ylabel('Country')
plt.tight_layout()
plt.show()

retail_df['DayOfWeek'] = retail_df['InvoiceDate'].dt.day_name()
retail_df['Hour'] = retail_df['InvoiceDate'].dt.hour
sales_pivot = retail_df.pivot_table(index='DayOfWeek', columns='Hour', values='Revenue', aggfunc='sum').fillna(0)
sales_pivot = sales_pivot.reindex(['Monday','Tuesday','Wednesday','Thursday','Friday','Saturday','Sunday'])
plt.figure(figsize=(15,5))
sns.heatmap(sales_pivot, cmap='YlGnBu', linewidths=0.3)
plt.title('Retail Revenue by Day of Week and Hour')
plt.xlabel('Hour of Day')
plt.ylabel('Day of Week')
plt.show()

product_sales = retail_df.groupby('Description')['Revenue'].sum().sort_values(ascending=False)
plt.figure(figsize=(9,6))
sns.histplot(product_sales.values, bins=30, kde=True)
plt.title('Distribution of Product Sales Revenue')
plt.xlabel('Product Revenue ()')
plt.ylabel('Number of Products')
plt.tight_layout()
plt.show()

plt.figure(figsize=(10,5))
sns.barplot(x=product_sales.values[:10], y=product_sales.index[:10], palette='rocket')
plt.title('Top 10 Products by Revenue')
plt.xlabel('Total Revenue ()')
plt.ylabel('Product')
plt.tight_layout()
plt.show()

rolling_sales = daily_sales.rolling(window=7, min_periods=1).mean()
plt.figure(figsize=(12,6))
plt.plot(daily_sales.index, daily_sales.values, label='Actual Daily Sales', alpha=0.6)
plt.plot(daily_sales.index, rolling_sales, label='7-Day Moving Average', linewidth=3, color='red')
plt.title('Daily Retail Sales with Trend Smoothed (7-day MA)')
plt.xlabel('Date')
plt.ylabel('Revenue ()')
plt.legend()
plt.tight_layout()
plt.show()

missing = retail_df[(retail_df['Quantity'] <= 0) | (retail_df['Price'] <= 0) | (retail_df['Quantity'].isnull()) | (retail_df['Price'].isnull())]
print('Number of problematic transactions:', missing.shape[0])
print(missing.head(2))

# INCORRECT: Aggregating by Invoice only loses product detail
invoice_sales = retail_df.groupby('Invoice')['Revenue'].sum().head(5)
print(invoice_sales)

# CORRECT: Group by both Invoice and Product Description
invoice_product_sales = retail_df.groupby(['Invoice','Description'])['Revenue'].sum().reset_index().head(5)
print(invoice_product_sales)

retail_df['Month'] = retail_df['InvoiceDate'].dt.to_period('M')
latest_month = retail_df['Month'].max()
monthly_top_products = retail_df[retail_df['Month'] == latest_month].groupby('Description')['Revenue'].sum().sort_values(ascending=False).head(5)
print(f'Top 5 Selling Products in {latest_month}:')
print(monthly_top_products)