import pandas as pd
import numpy as np
import streamlit as st
import openml
import matplotlib.pyplot as plt
from datetime import datetime, timedelta
import warnings
warnings.filterwarnings('ignore')

# Beginner Example 1: Load Retail Demand Data
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))

# Beginner Example 2: Load Order Fulfillment Data
olist_url = 'https://raw.githubusercontent.com/olist/work-at-olist-data/master/datasets/olist_orders_dataset.csv'
orders_df = pd.read_csv(olist_url)
orders_df['order_purchase_timestamp'] = pd.to_datetime(orders_df['order_purchase_timestamp'])
orders_df['order_delivered_customer_date'] = pd.to_datetime(orders_df['order_delivered_customer_date'])
print(orders_df.shape)
print(orders_df[['order_id','order_purchase_timestamp','order_delivered_customer_date']].head(3))

# Beginner Example 3: Load Supplier Performance Data
dataset = openml.datasets.get_dataset(42125)
supplier_df, _, _, _ = dataset.get_data(dataset_format='dataframe')
print(supplier_df.shape)
print(supplier_df.head(3))

# Intermediate Example 1: Summarize Weekly Retail Demand
retail_df['week'] = retail_df['InvoiceDate'].dt.isocalendar().week
weekly_sales = retail_df.groupby('week')['Quantity'].sum()
print(weekly_sales.head())

# Intermediate Example 2: Calculate Order Delivery Lead Time
orders_df['delivery_lead_time'] = (orders_df['order_delivered_customer_date'] - orders_df['order_purchase_timestamp']).dt.days
print(orders_df[['order_id','delivery_lead_time']].head())

# Intermediate Example 3: Supplier Fill Rate Calculation
if 'Fill Rate' in supplier_df.columns:
    avg_fill = supplier_df['Fill Rate'].mean()
    print(f'Average supplier fill rate: {avg_fill:.2%}')
else:
    print('No Fill Rate column found in the supplier data.')

# Intermediate Example 4: Filter Orders Missing Delivery Dates
missing_delivery = orders_df[orders_df['order_delivered_customer_date'].isnull()]
print(f'Missing delivery dates: {len(missing_delivery)} records')

# Intermediate Example 5: Top 5 Most Popular Products
if 'Description' in retail_df.columns:
    top_products = retail_df.groupby('Description')['Quantity'].sum().nlargest(5)
    print(top_products)
else:
    print('Description column not found in the retail data.')

# Advanced Example 1: Demand Time Series Chart with Matplotlib
plt.figure(figsize=(12,4))
weekly_sales.plot()
plt.title('Weekly Retail Demand')
plt.xlabel('Week number')
plt.ylabel('Total Quantity Sold')
plt.tight_layout()
plt.savefig('weekly_retail_demand.png')
plt.close()

# Advanced Example 2: Build a Minimal Streamlit Dashboard Script
dashboard_script = '''
import streamlit as st
import pandas as pd
import matplotlib.pyplot as plt
retail_df = pd.read_excel('https://archive.ics.uci.edu/ml/machine-learning-databases/00502/online_retail_II.xlsx', sheet_name='Year 2010-2011')
retail_df['InvoiceDate'] = pd.to_datetime(retail_df['InvoiceDate'])
retail_df['week'] = retail_df['InvoiceDate'].dt.isocalendar().week
weekly_sales = retail_df.groupby('week')['Quantity'].sum()
st.title('Weekly Retail Demand Dashboard')
fig, ax = plt.subplots()
weekly_sales.plot(ax=ax)
ax.set_xlabel('Week number')
ax.set_ylabel('Total Quantity Sold')
st.pyplot(fig)
'''
with open('mini_dashboard.py', 'w') as f:
    f.write(dashboard_script)