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

# Load order fulfillment data from Olist
url = 'https://raw.githubusercontent.com/olist/work-at-olist-data/master/datasets/olist_orders_dataset.csv'
olist_df = pd.read_csv(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'])
print(olist_df[['order_id', 'order_purchase_timestamp', 'order_delivered_customer_date']].head(3))

# Calculate delivery duration in days
olist_df['delivery_days'] = (olist_df['order_delivered_customer_date'] - olist_df['order_purchase_timestamp']).dt.days
print(olist_df[['order_id', 'delivery_days']].head(3))

# Count missing values in the delivery date
missing_delivery = olist_df['order_delivered_customer_date'].isna().sum()
print(f'Missing delivered dates: {missing_delivery}')

# Load OpenML inventory operations data
inv_dataset = openml.datasets.get_dataset(43900)
X_inv, y_inv, _, _ = inv_dataset.get_data(dataset_format='dataframe')
inv_df = X_inv.copy() if y_inv is None else pd.concat([X_inv, y_inv], axis=1)
print(inv_df.shape)
print(inv_df.head(3))

# Example quantity aggregation
if 'quantity' in inv_df.columns:
    total_inventory = inv_df['quantity'].sum()
    print(f'Total inventory quantity: {total_inventory}')
else:
    print('No inventory quantity column found.')

# Load supplier performance data
sup_dataset = openml.datasets.get_dataset(42125)
sup_df, _, _, _ = sup_dataset.get_data(dataset_format='dataframe')
if 'InvoiceDate' in sup_df.columns:
    sup_df['InvoiceDate'] = pd.to_datetime(sup_df['InvoiceDate'], errors='coerce')
print(sup_df.head(3))

# Check for late deliveries if possible
if 'ActualDeliveryDate' in sup_df.columns and 'ExpectedDeliveryDate' in sup_df.columns:
    sup_df['ActualDeliveryDate'] = pd.to_datetime(sup_df['ActualDeliveryDate'], errors='coerce')
    sup_df['ExpectedDeliveryDate'] = pd.to_datetime(sup_df['ExpectedDeliveryDate'], errors='coerce')
    sup_df['delivery_delay_days'] = (sup_df['ActualDeliveryDate'] - sup_df['ExpectedDeliveryDate']).dt.days
    print(sup_df[['ActualDeliveryDate', 'ExpectedDeliveryDate', 'delivery_delay_days']].head(3))
else:
    print('Delivery date columns not found in this dataset.')

# Aggregate orders by purchase date
olist_counts = olist_df.groupby(olist_df['order_purchase_timestamp'].dt.date)['order_id'].count()
print(olist_counts.head())

# Load OpenML sales forecasting data
sales_dataset = openml.datasets.get_dataset(4549)
X_sales, y_sales, _, _ = sales_dataset.get_data(dataset_format='dataframe')
sales_df = pd.concat([X_sales, y_sales], axis=1)
print(sales_df.head(3))

# Check for date or time columns in sales data
date_columns = [col for col in sales_df.columns if 'date' in col.lower() or 'time' in col.lower()]
print('Date/time columns:', date_columns)
if date_columns:
    sales_df[date_columns[0]] = pd.to_datetime(sales_df[date_columns[0]], errors='coerce')
    print(sales_df[date_columns[0]].head(3))

# Plot numeric quantities if matplotlib is installed
import matplotlib.pyplot as plt
num_cols = sales_df.select_dtypes(include='number').columns
if not num_cols.empty:
    sales_df[num_cols[0]].hist(bins=30)
    plt.title(f'Distribution of {num_cols[0]}')
    plt.xlabel(num_cols[0])
    plt.ylabel('Frequency')
    plt.show()
else:
    print('No numeric columns available for visualization.')

# Rolling average of daily delivery durations (Olist)
olist_df_sorted = olist_df.dropna(subset=['delivery_days']).sort_values('order_purchase_timestamp')
olist_df_sorted['rolling_avg_delivery'] = olist_df_sorted['delivery_days'].rolling(window=7).mean()
print(olist_df_sorted[['order_purchase_timestamp', 'delivery_days', 'rolling_avg_delivery']].head(10))

# Count of orders per month
olist_df['month'] = olist_df['order_purchase_timestamp'].dt.to_period('M')
monthly_counts = olist_df.groupby('month')['order_id'].count()
print(monthly_counts.head())

# Weekly order volume seasonality
olist_df['week'] = olist_df['order_purchase_timestamp'].dt.isocalendar().week
weekly_volume = olist_df.groupby('week')['order_id'].count()
print(weekly_volume.head())

# On-time delivery KPI (percentage within X days)
on_time_threshold = 5
total_delivered = olist_df['delivery_days'].notna().sum()
on_time = (olist_df['delivery_days'] <= on_time_threshold).sum()
on_time_pct = 100 * on_time / total_delivered if total_delivered > 0 else float('nan')
print(f'On-time delivery rate (<= {on_time_threshold} days): {on_time_pct:.2f}%')

# Handle missing data in delivery days
missing_delivery_count = olist_df['delivery_days'].isna().sum()
if missing_delivery_count > 0:
    print(f'Warning: {missing_delivery_count} orders are missing delivery days.')
    olist_df_filled = olist_df.copy()
    olist_df_filled['delivery_days'] = olist_df['delivery_days'].fillna(-1)
else:
    olist_df_filled = olist_df.copy()
    print('No missing delivery day values.')

# Find deliveries before purchase dates (should never happen)
invalid_dates = olist_df[olist_df['order_delivered_customer_date'] < olist_df['order_purchase_timestamp']]
print(f'Deliveries before purchase: {invalid_dates.shape[0]}')
if not invalid_dates.empty:
    print(invalid_dates[['order_id', 'order_purchase_timestamp', 'order_delivered_customer_date']].head())

# Demonstrate a possible incorrect join: drop mismatched IDs
test_left = olist_df[['order_id', 'order_purchase_timestamp']].iloc[:5]
test_right = olist_df[['order_id', 'order_delivered_customer_date']].iloc[2:7]
merge_result = pd.merge(test_left, test_right, on='order_id', how='outer', indicator=True)
print(merge_result)

# Example: Aggregate orders by item_id (if available)
if 'product_id' in olist_df.columns:
    order_counts = olist_df.groupby('product_id')['order_id'].count().sort_values(ascending=False)
    print(order_counts.head())
else:
    print('No product_id available in this dataset.')

# Rolling on-time KPI visualization
on_time_threshold = 7
olist_df_sorted = olist_df.sort_values('order_purchase_timestamp').copy()
olist_df_sorted['on_time'] = (olist_df_sorted['delivery_days'] <= on_time_threshold).astype(int)
olist_df_sorted['rolling_kpi'] = olist_df_sorted['on_time'].rolling(window=30, min_periods=1).mean() * 100
plt.plot(olist_df_sorted['order_purchase_timestamp'], olist_df_sorted['rolling_kpi'])
plt.title('30-Order Rolling On-Time Delivery Rate')
plt.xlabel('Order Purchase Date')
plt.ylabel('On-time Delivery %')
plt.show()

# Summing order counts by week for reporting
weekly_order_counts = olist_df.groupby('week')['order_id'].count()
print(weekly_order_counts.head())

# End-to-end: Identify week with fastest deliveries
min_week = olist_df.loc[olist_df['delivery_days'] > 0].groupby('week')['delivery_days'].mean().idxmin()
fastest_mean = olist_df.loc[olist_df['week'] == min_week, 'delivery_days'].mean()
print(f'The fastest average delivery was week {min_week} with {fastest_mean:.2f} days.')