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

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

# Basic check for missing values
print(df_retail.isnull().sum())

# Beginner: Aggregate total quantity and revenue per product
df_retail['Revenue'] = df_retail['Quantity'] * df_retail['Price']
prod_summary = df_retail.groupby('Description').agg({'Quantity': 'sum', 'Revenue': 'sum'}).reset_index()
print(prod_summary.head(5))

# Beginner: Sum total revenue across the entire dataset
total_revenue = df_retail['Revenue'].sum()
print(f'Total revenue: GBP {total_revenue:,.2f}')

# Beginner: Show monthly revenue trend (time-based analysis for reports)
df_retail['YearMonth'] = df_retail['InvoiceDate'].dt.to_period('M')
monthly_revenue = df_retail.groupby('YearMonth')['Revenue'].sum().reset_index()
print(monthly_revenue.head(6))

# Beginner: Export the product summary to a CSV file for reporting
prod_summary.to_csv('product_sales_summary.csv', index=False)
print('Product summary exported to product_sales_summary.csv')

# Beginner: Export the monthly revenue report as Excel (XLSX)
monthly_revenue.to_excel('monthly_revenue_report.xlsx', index=False)
print('Monthly revenue report exported as monthly_revenue_report.xlsx')

# Intermediate: Aggregate revenue by country for region-based reporting
country_rev = df_retail.groupby('Country')['Revenue'].sum().reset_index().sort_values('Revenue', ascending=False)
print(country_rev.head(5))

# Intermediate: Calculate average order value (AOV) for business insight
df_retail['OrderRevenue'] = df_retail.groupby('Invoice')['Revenue'].transform('sum')
avg_order_value = df_retail[['Invoice', 'OrderRevenue']].drop_duplicates()['OrderRevenue'].mean()
print(f'Average order value: GBP {avg_order_value:,.2f}')

# Intermediate: Export country revenue report for external sharing
country_rev.to_csv('country_revenue_summary.csv', index=False)
print('Country-wise revenue exported to country_revenue_summary.csv')

# Intermediate: Create a simple Excel report with multiple sheets
with pd.ExcelWriter('retail_comprehensive_report.xlsx') as writer:
    prod_summary.to_excel(writer, sheet_name='Product_Summary', index=False)
    monthly_revenue.to_excel(writer, sheet_name='Monthly_Revenue', index=False)
    country_rev.to_excel(writer, sheet_name='Country_Revenue', index=False)
print('Retail comprehensive report exported as retail_comprehensive_report.xlsx')

# Intermediate: Identify and export top 10 high-value customers
cust_rev = df_retail.groupby('Customer ID')['Revenue'].sum().reset_index()
top_customers = cust_rev.sort_values('Revenue', ascending=False).head(10)
top_customers.to_csv('top_10_customers.csv', index=False)
print(top_customers)

# Advanced: Export filtered product report (only electronics category example)
electronics = prod_summary[prod_summary['Description'].str.contains('electronic', case=False, na=False)]
electronics.to_csv('electronics_sales_summary.csv', index=False)
print(electronics.head(3))

# Advanced: Export summary with formatted Excel columns for professional look
with pd.ExcelWriter('formatted_product_report.xlsx', engine='xlsxwriter') as writer:
    prod_summary.to_excel(writer, sheet_name='Summary', index=False)
    workbook  = writer.book
    worksheet = writer.sheets['Summary']
    money_fmt = workbook.add_format({'num_format': 'GBP #,##0.00'})
    worksheet.set_column('B:B', 10)
    worksheet.set_column('C:C', 18, money_fmt)
print('Formatted Excel export complete')

# Advanced: Export top products to PDF using pandas and reportlab
from pandas.plotting import table
import matplotlib.pyplot as plt
top_products = prod_summary.sort_values('Revenue', ascending=False).head(10)
fig, ax = plt.subplots(figsize=(8, 4))
ax.xaxis.set_visible(False)  # Hide axes
ax.yaxis.set_visible(False)
ax.set_frame_on(False)
tbl = table(ax, top_products, loc='center', colWidths=[0.20]*len(top_products.columns))
tbl.auto_set_font_size(False)
tbl.set_fontsize(9)
plt.savefig('top_products_report.pdf', bbox_inches='tight')
plt.close()

# Error handling: Detect missing Customer IDs when exporting reports
missing_cust = df_retail['Customer ID'].isnull().sum()
if missing_cust > 0:
    print(f'Warning: {missing_cust} sales records are missing Customer ID and will be excluded from customer reporting.')
else:
    print('No missing Customer IDs detected.')

# Error handling: Prevent wrong grouping in product export
bad_group_test = df_retail.agg({'Quantity':'sum', 'Revenue':'sum'})
print('Total Quantity (no product breakdown):', bad_group_test['Quantity'])
print('Total Revenue (no product breakdown): GBP', bad_group_test['Revenue'])
# This does not give a product report! Always group by product for per-product insights.

# Error handling: Catch and report missing Price or Quantity when calculating revenue
missing_price = df_retail['Price'].isnull().sum()
missing_qty = df_retail['Quantity'].isnull().sum()
if missing_price > 0 or missing_qty > 0:
    print(f'Warning: {missing_price} records missing Price, {missing_qty} missing Quantity. These are excluded from revenue calculations.')
else:
    print('No missing Price or Quantity. Revenue calculation is complete and reliable.')

# End-to-end problem: Identify top 5 products this quarter and export report
qtr = df_retail[df_retail['InvoiceDate'].dt.quarter == 1]
qtr_prod = qtr.groupby('Description').agg({'Quantity':'sum','Revenue':'sum'}).reset_index()
top5_qtr = qtr_prod.sort_values('Revenue', ascending=False).head(5)
top5_qtr.to_csv('Q1_top5_product_report.csv', index=False)
print(top5_qtr)