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

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))

dataset = openml.datasets.get_dataset(43900)
X, y, _, _ = dataset.get_data(dataset_format='dataframe')
df_ops = X.copy() if y is None else pd.concat([X, y], axis=1)
print(df_ops.shape)
print(df_ops.head(3))

unique_orders = df['Invoice'].nunique()
print('Number of unique orders:', unique_orders)

daily_demand = df.groupby(df['InvoiceDate'].dt.date)['Quantity'].sum()
print(daily_demand.head())

top_product = df.groupby('Description')['Quantity'].sum().sort_values(ascending=False).head(1)
print('Most demanded product:')
print(top_product)

orders_country = df.groupby('Country')['Invoice'].nunique().sort_values(ascending=False)
print(orders_country.head())

olist_url = 'https://raw.githubusercontent.com/olist/work-at-olist-data/master/datasets/olist_orders_dataset.csv'
olist_df = pd.read_csv(olist_url)
olist_df['order_purchase_timestamp'] = pd.to_datetime(olist_df['order_purchase_timestamp'])
olist_df['order_delivered_customer_date'] = pd.to_datetime(olist_df['order_delivered_customer_date'])
olist_df['lead_time_days'] = (olist_df['order_delivered_customer_date'] - olist_df['order_purchase_timestamp']).dt.days
mean_lead_time = olist_df['lead_time_days'].mean()
print('Average lead time (days):', mean_lead_time)

Q1 = daily_demand.quantile(0.25)
Q3 = daily_demand.quantile(0.75)
IQR = Q3 - Q1
outlier_days = daily_demand[daily_demand > Q3 + 1.5 * IQR]
print('Unusually high demand days:')
print(outlier_days)

prod_daily = df.groupby(['Description', df['InvoiceDate'].dt.date])['Quantity'].sum().reset_index()
var_by_prod = prod_daily.groupby('Description')['Quantity'].std().sort_values(ascending=False)
print('Top 5 most variable products by daily demand:')
print(var_by_prod.head())

all_days = pd.date_range(start=daily_demand.index.min(), end=daily_demand.index.max())
missing_days = all_days.difference(pd.to_datetime(daily_demand.index))
print('Number of missing sales days:', len(missing_days))
print('Missing dates:', missing_days[:5].strftime('%Y-%m-%d').tolist())

# Example: matching orders from two sources by invoice - simulate mismatch
order_keys = set(df['Invoice'].astype(str).unique())
olist_keys = set(olist_df['order_id'].astype(str).unique())
join_overlap = order_keys & olist_keys
print('Number of matching order keys between datasets:', len(join_overlap))

neg_qty = df[df['Quantity'] < 0]
neg_lead = olist_df[olist_df['lead_time_days'] < 0]
print('Negative quantity rows:', neg_qty.shape[0])
print('Negative lead time rows:', neg_lead.shape[0])

df['month'] = df['InvoiceDate'].dt.to_period('M')
monthly_demand = df.groupby('month')['Quantity'].sum()
print(monthly_demand.head())

on_time_pct = (olist_df['lead_time_days'] <= 7).mean() * 100
print(f'Percent of orders delivered within 7 days: {on_time_pct:.2f}%')

df['week'] = df['InvoiceDate'].dt.to_period('W')
weekly_demand = df.groupby('week')['Quantity'].sum()
weekly_growth = weekly_demand.pct_change()
spike_weeks = weekly_growth[weekly_growth > 0.5].index.astype(str).tolist()
print('Weeks with demand spike alert:', spike_weeks)