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

# Example 1: Loading a retail transaction 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')
print(f"Retail dataset shape: {df_retail.shape}")
df_retail.head(3)

# Example 2: Parse dates for time-based analysis
df_retail['InvoiceDate'] = pd.to_datetime(df_retail['InvoiceDate'])
print(df_retail['InvoiceDate'].dtype)
print(df_retail['InvoiceDate'].min(), '-', df_retail['InvoiceDate'].max())

# Example 3: Counting unique products
unique_products = df_retail['StockCode'].nunique()
print(f"Number of unique products: {unique_products}")

# Example 4: Loading a supplier performance dataset
dataset = openml.datasets.get_dataset(42125)
df_supplier, _, _, _ = dataset.get_data(dataset_format='dataframe')
print(f"Supplier performance dataset: {df_supplier.shape}")
df_supplier.head(3)

# Example 5: Checking for missing values (critical in operations)
missing_columns = df_supplier.columns[df_supplier.isnull().any()]
print(f"Columns with missing values: {list(missing_columns)}")

# Example 6: Load order fulfillment data (E-commerce delivery)
url_order = 'https://raw.githubusercontent.com/olist/work-at-olist-data/master/datasets/olist_orders_dataset.csv'
df_order = pd.read_csv(url_order)
print(f"Order dataset shape: {df_order.shape}")
df_order.head(3)

# Example 7: Calculating late deliveries
df_order['order_purchase_timestamp'] = pd.to_datetime(df_order['order_purchase_timestamp'])
df_order['order_delivered_customer_date'] = pd.to_datetime(df_order['order_delivered_customer_date'])
df_order['delivery_delay'] = (df_order['order_delivered_customer_date'] - df_order['order_purchase_timestamp']).dt.days
late_orders = df_order[df_order['delivery_delay'] > 7]
print(f"Orders delivered after more than one week: {late_orders.shape[0]}")

# Example 8: Load a sales forecasting dataset
dataset = openml.datasets.get_dataset(4549)
X, y, _, _ = dataset.get_data(dataset_format='dataframe')
df_sales = pd.concat([X, y], axis=1)
print(f"Sales forecasting data: {df_sales.shape}")
df_sales.head(3)

# Example 9: Aggregate daily sales totals
if 'date' in df_sales.columns:
    df_sales['date'] = pd.to_datetime(df_sales['date'])
    df_daily = df_sales.groupby('date')['target'].sum().reset_index()
    print(df_daily.head())
else:
    print('Dataset has no date field.')

# Example 10: Investigate outliers in sales data
if 'target' in df_sales.columns:
    sales_q1 = df_sales['target'].quantile(0.25)
    sales_q3 = df_sales['target'].quantile(0.75)
    iqr = sales_q3 - sales_q1
    outliers = df_sales[(df_sales['target'] < sales_q1 - 1.5 * iqr) | (df_sales['target'] > sales_q3 + 1.5 * iqr)]
    print(f"Number of sales outliers: {outliers.shape[0]}")
else:
    print('No target column found in sales dataset.')

# Example 11: Load manufacturing operations dataset
dataset = openml.datasets.get_dataset(43900)
X_mfg, y_mfg, _, _ = dataset.get_data(dataset_format='dataframe')
df_mfg = X_mfg.copy() if y_mfg is None else pd.concat([X_mfg, y_mfg], axis=1)
print(f"Manufacturing operation data shape: {df_mfg.shape}")
df_mfg.head(3)

# Example 12: Compute average cycle time for a process step
process_cols = [col for col in df_mfg.columns if 'cycle' in col.lower() or 'time' in col.lower()]
if process_cols:
    mean_cycle = df_mfg[process_cols[0]].mean()
    print(f"Average cycle time: {mean_cycle:.2f}")
else:
    print('No cycle time columns found.')

# Example 13: Checking for duplicate order records
order_id_col = [col for col in df_mfg.columns if 'order' in col.lower()]
if order_id_col:
    dupes = df_mfg.duplicated(order_id_col[0]).sum()
    print(f"Number of duplicate order IDs: {dupes}")
else:
    print('No order id column found.')

# Example 14: Load SECOM manufacturing quality dataset
dataset = openml.datasets.get_dataset(1500)
X_qual, y_qual, _, _ = dataset.get_data(dataset_format='dataframe')
df_secom = pd.concat([X_qual, y_qual], axis=1)
print(f"Quality records: {df_secom.shape}")
df_secom.head(3)

# Example 15: Detect missing sensor values
missing_sensor = df_secom.isnull().any(axis=1).sum()
print(f"Production records with missing sensor data: {missing_sensor}")

# Example 16: Identify defective unit share
if y_qual is not None and y_qual.name in df_secom.columns:
    defect_share = (df_secom[y_qual.name] != 1).mean()
    print(f"Fraction of defective units: {defect_share:.2%}")
else:
    print('Defect label not present.')

# Advanced Example 2: Calculate supplier on-time rate
if 'OnTimeDelivery' in df_supplier.columns:
    on_time_rate = (df_supplier['OnTimeDelivery'] == 1).mean()
    print(f"Supplier on-time delivery rate: {on_time_rate:.2%}")
else:
    print('No on-time delivery field in supplier data.')

# Example 17: Handling missing delivery dates
missing_date = df_order['order_delivered_customer_date'].isnull().sum()
print(f"Orders with missing delivery date: {missing_date}")

# Example 18: Joining with mismatched keys
fake_left = pd.DataFrame({'OrderID': [1, 2, 3], 'Location': ['A', 'B', 'C']})
fake_right = pd.DataFrame({'OrderRef': [2, 3, 4], 'Value': [50, 60, 70]})
merged = pd.merge(fake_left, fake_right, left_on='OrderID', right_on='OrderRef', how='left')
print(merged)

# Example 19: Misinterpreting inventory quantities
data_qty = pd.DataFrame({'Product': ['X', 'Y'], 'Stock': ['12', 'eight']})
data_qty['Stock'] = pd.to_numeric(data_qty['Stock'], errors='coerce')
print(data_qty)

# Example 20: Aggregate orders per customer
if 'customer_id' in df_order.columns:
    customer_orders = df_order.groupby('customer_id').size().reset_index(name='num_orders')
    print(customer_orders.head())
else:
    print('Customer id not found in data.')

# Example 21: Group sales by product for top-sellers
if 'StockCode' in df_retail.columns and 'Quantity' in df_retail.columns:
    product_sales = df_retail.groupby('StockCode')['Quantity'].sum().reset_index()
    product_sales = product_sales.sort_values('Quantity', ascending=False).head(5)
    print(product_sales)
else:
    print('Sales columns not present in retail data.')

# Example 22: Calculate on-time order fulfillment rate
if 'delivery_delay' in df_order.columns:
    on_time_fulfillment = (df_order['delivery_delay'] <= 7).mean()
    print(f"On-time fulfillment rate: {on_time_fulfillment:.2%}")
else:
    print('Delivery delay field missing in order data.')

# Step 1: Load supplier performance data (repeat from earlier)
dataset = openml.datasets.get_dataset(42125)
df_supplier, _, _, _ = dataset.get_data(dataset_format='dataframe')
print('Supplier data loaded:', df_supplier.shape)

# Step 2: Clean late delivery values (handling missing and outliers)
if 'LateDeliveries' in df_supplier.columns:
    late_counts = df_supplier['LateDeliveries'].copy()
    late_counts = pd.to_numeric(late_counts, errors='coerce').fillna(0)
    df_supplier['LateDeliveriesClean'] = late_counts
    print('Missing values filled. Outliers and non-numbers handled.')
else:
    print('LateDeliveries field not present.')

# Step 3: Extract the supplier with the most late deliveries
if 'SupplierName' in df_supplier.columns and 'LateDeliveriesClean' in df_supplier.columns:
    top_late = df_supplier.groupby('SupplierName')['LateDeliveriesClean'].sum().reset_index()
    worst = top_late.sort_values('LateDeliveriesClean', ascending=False).head(1)
    print('Supplier with the most late deliveries:')
    print(worst)
else:
    print('Fields needed for this KPi are missing.')