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

# Load a real online retail dataset from UCI
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 daily sales quantity
daily_qty = df.groupby(df['InvoiceDate'].dt.date)['Quantity'].sum()
print(daily_qty.head())

# Calculate daily sales revenue
df['Revenue'] = df['Quantity'] * df['Price']
daily_revenue = df.groupby(df['InvoiceDate'].dt.date)['Revenue'].sum()
print(daily_revenue.head())

# Visualize daily sales quantity over time
daily_qty.plot(figsize=(12,5), title='Daily Products Sold')
plt.ylabel('Quantity Sold')
plt.xlabel('Date')
plt.show()

# Extract month and weekday for seasonal analysis
df['Month'] = df['InvoiceDate'].dt.month
df['Weekday'] = df['InvoiceDate'].dt.day_name()
print(df[['InvoiceDate','Month','Weekday']].head(3))

# Aggregate revenue by month
monthly_revenue = df.groupby('Month')['Revenue'].sum()
print(monthly_revenue)

# Plot monthly sales revenue
monthly_revenue.plot(kind='bar', title='Monthly Sales Revenue', color='skyblue')
plt.ylabel('Revenue')
plt.xlabel('Month')
plt.show()

# Aggregate sales by weekday
weekday_sales = df.groupby('Weekday')['Revenue'].sum().reindex(['Monday','Tuesday','Wednesday','Thursday','Friday','Saturday','Sunday'])
print(weekday_sales)

# Plot weekday sales revenue
weekday_sales.plot(kind='bar', color='salmon', title='Weekly Revenue by Day')
plt.ylabel('Revenue')
plt.xlabel('Weekday')
plt.show()

# Use rolling mean to smooth daily sales (reduces noise)
rolling_qty = daily_qty.rolling(window=7, min_periods=1).mean()
plt.figure(figsize=(12, 5))
plt.plot(daily_qty.index, daily_qty.values, alpha=0.4, label='Daily Sales')
plt.plot(rolling_qty.index, rolling_qty.values, color='red', label='7-Day Rolling Mean')
plt.title('Smoothed Daily Product Sales')
plt.xlabel('Date')
plt.ylabel('Quantity Sold')
plt.legend()
plt.show()

# Simulate more recent retail order data for time series practice
np.random.seed(42)
n_orders = 1000
order_dates = pd.date_range('2023-01-01', periods=n_orders, freq='h')
order_qty = np.random.choice([1,2,3,4], n_orders, p=[0.5,0.3,0.15,0.05])
orders_df = pd.DataFrame({'OrderDate': order_dates, 'Quantity': order_qty})
orders_df['Date'] = orders_df['OrderDate'].dt.date
daily_sim_qty = orders_df.groupby('Date')['Quantity'].sum()
print(daily_sim_qty.head())

# Detect potential seasonality in simulated data
plt.figure(figsize=(12,5))
plt.plot(daily_sim_qty.index, daily_sim_qty.values, label='Simulated Daily Sales')
plt.title('Simulated Daily Retail Orders')
plt.xlabel('Date')
plt.ylabel('Quantity Sold')
plt.legend()
plt.show()

# Resample orders to monthly totals in the simulated dataset
orders_df['OrderDate'] = pd.to_datetime(orders_df['OrderDate'])
monthly_sim_qty = orders_df.resample('M', on='OrderDate')['Quantity'].sum()
print(monthly_sim_qty)

# Plot simulated monthly demand
monthly_sim_qty.plot(kind='bar', color='orange', title='Monthly Simulated Orders')
plt.ylabel('Quantity Sold')
plt.xlabel('Month')
plt.show()

# Advanced: Calculate 30-day moving average on real retail sales
moving_avg_qty = daily_qty.rolling(window=30, min_periods=7).mean()
plt.figure(figsize=(12, 5))
plt.plot(moving_avg_qty.index, moving_avg_qty.values, color='navy')
plt.title('30-Day Moving Average of Daily Products Sold')
plt.ylabel('Quantity Sold')
plt.xlabel('Date')
plt.show()

# Advanced: Decompose timeseries to visualize seasonality
from statsmodels.tsa.seasonal import seasonal_decompose
result = seasonal_decompose(daily_qty, model='additive', period=30)
result.plot()
plt.suptitle('Seasonal Decomposition of Sales: Trend, Seasonality, Residuals')
plt.show()

# Error Handling: Check for missing dates in sales
all_dates = pd.date_range(df['InvoiceDate'].min().date(), df['InvoiceDate'].max().date())
missing_dates = set(all_dates.date) - set(daily_qty.index)
print(f'Missing daily sales records: {len(missing_dates)}')

# Error Handling: Detect and fix negative sales quantities
neg_qty = df[df['Quantity'] < 0]
print('Rows with negative quantities:', neg_qty.shape[0])
# Fix: Remove negative quantities (often returns/refunds)
df_clean = df[df['Quantity'] >= 0]

# Debugging: MistakeGrouping by invoice number instead of date
wrong_group = df.groupby('Invoice')['Revenue'].sum()
print(wrong_group.head())

# Pattern: Create a seasonality index (average sales by month)
seasonality_idx = monthly_revenue / monthly_revenue.mean()
print(seasonality_idx.round(2))

# Pattern: Compare product-level demand trends over time
top_products = df_clean.groupby('Description')['Quantity'].sum().sort_values(ascending=False).head(3).index
for prod in top_products:
    prod_daily = df_clean[df_clean['Description'] == prod].groupby(df_clean['InvoiceDate'].dt.date)['Quantity'].sum()
    plt.plot(prod_daily.index, prod_daily.values, label=prod)
plt.title('Daily Demand for Top 3 Products')
plt.xlabel('Date')
plt.ylabel('Quantity Sold')
plt.legend()
plt.show()

# End-to-End: Identify month with the highest demand surge and recommend action
peak_month = monthly_revenue.idxmax()
surge = monthly_revenue.max() - monthly_revenue.median()
print(f'Peak demand is in month {peak_month}. Surges by {surge:,.0f} units above typical demand.')
recommendation = f'Recommendation: Ensure stock and extra staff for month {peak_month}, as demand surges well above typical levels!'
print(recommendation)