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

# Load a real retail demand dataset
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))

# Load an order fulfillment and delivery dataset
url = 'https://raw.githubusercontent.com/olist/work-at-olist-data/master/datasets/olist_orders_dataset.csv'
orders_df = pd.read_csv(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.head(3))

# Beginner: Use if-else to flag late deliveries
orders_df['is_late'] = None
for idx, row in orders_df.iterrows():
    if pd.isnull(row['order_delivered_customer_date']):
        orders_df.at[idx, 'is_late'] = 'Unknown'
    elif row['order_delivered_customer_date'] > row['order_purchase_timestamp'] + pd.Timedelta(days=7):
        orders_df.at[idx, 'is_late'] = 'Yes'
    else:
        orders_df.at[idx, 'is_late'] = 'No'
print(orders_df[['order_purchase_timestamp', 'order_delivered_customer_date', 'is_late']].head(6))

# Beginner: Count unknown, late, and on-time cases
counts = orders_df['is_late'].value_counts(dropna=False)
print(counts)

# Beginner: Use list comprehension for simple demand flagging
df['high_demand'] = ['Yes' if qty > 50 else 'No' for qty in df['Quantity']]
print(df[['Quantity', 'high_demand']].head(6))

# Beginner: Filter only high demand transactions using boolean logic
high_demand_df = df[df['high_demand'] == 'Yes']
print(high_demand_df[['StockCode', 'Quantity', 'high_demand']].head(5))

# Intermediate: Classify orders as delivered, not delivered, or unknown
def classify_status(row):
    if pd.isnull(row['order_delivered_customer_date']):
        return 'Unknown'
    elif row['order_delivered_customer_date'] <= pd.Timestamp.now():
        return 'Delivered'
    else:
        return 'Not delivered'
orders_df['delivery_status'] = orders_df.apply(classify_status, axis=1)
print(orders_df[['order_delivered_customer_date', 'delivery_status']].head(6))

# Intermediate: Flag orders with abnormal delivery time using nested ifs
def abnormal_delivery(row):
    if pd.isnull(row['order_delivered_customer_date']):
        return 'Unknown'
    else:
        days = (row['order_delivered_customer_date'] - row['order_purchase_timestamp']).days
        if days < 0:
            return 'Error'
        elif days > 30:
            return 'Extremely late'
        elif days > 7:
            return 'Late'
        else:
            return 'On time'
orders_df['delivery_flag'] = orders_df.apply(abnormal_delivery, axis=1)
print(orders_df[['order_purchase_timestamp', 'order_delivered_customer_date', 'delivery_flag']].head(8))

# Intermediate: Using for-loops to summarize per-week demand in real retail data
df['week'] = df['InvoiceDate'].dt.strftime('%Y-%U')
week_demand = {}
for week in df['week'].unique():
    week_qty = df[df['week']==week]['Quantity'].sum()
    if week_qty > 5000:
        week_demand[week] = 'High Demand Week'
    else:
        week_demand[week] = 'Normal'
print(list(week_demand.items())[:5])

# Intermediate: Find the first date of stockout using control flow
stockout_item = df['StockCode'].value_counts().idxmax()
item_df = df[df['StockCode']==stockout_item]
item_df = item_df.sort_values('InvoiceDate')
cumulative_qty = 0
stockout_date = None
for idx, row in item_df.iterrows():
    cumulative_qty += row['Quantity']
    if cumulative_qty <= 0 and stockout_date is None:
        stockout_date = row['InvoiceDate']
        break
print('Stockout item:', stockout_item)
print('Stockout date:', stockout_date)

# Advanced: Fetch and analyze a supplier performance dataset
dataset = openml.datasets.get_dataset(42125)
supply_df, _, _, _ = dataset.get_data(dataset_format='dataframe')
print(supply_df.shape)
print(supply_df.head(3))

# Advanced: Use decision logic to classify supplier risk
def supplier_risk(row):
    if row['OnTimeDeliveryRate'] < 0.85 or row['DefectRate'] > 0.05:
        return 'High Risk'
    elif row['OnTimeDeliveryRate'] < 0.95 or row['DefectRate'] > 0.02:
        return 'Medium Risk'
    else:
        return 'Low Risk'
if 'OnTimeDeliveryRate' in supply_df.columns and 'DefectRate' in supply_df.columns:
    supply_df['SupplierRisk'] = supply_df.apply(supplier_risk, axis=1)
    print(supply_df[['OnTimeDeliveryRate', 'DefectRate', 'SupplierRisk']].head(6))
else:
    print('Risk columns missing: cannot compute supplier risk.')

# Advanced: Create flexible business rules using lambda and np.select
import numpy as np
conditions = [
    (orders_df['delivery_flag'] == 'On time') & (orders_df['is_late'] == 'No'),
    (orders_df['delivery_flag'] == 'Late'),
    (orders_df['delivery_flag'] == 'Extremely late')
]
choices = ['Reward', 'Investigate', 'Escalate']
orders_df['action_decision'] = np.select(conditions, choices, default='Check')
print(orders_df[['delivery_flag', 'is_late', 'action_decision']].head(10))

# Advanced: Automatically generate an alert report when high-risk suppliers are found
high_risk_suppliers = None
if 'SupplierRisk' in supply_df.columns:
    high_risk_suppliers = supply_df[supply_df['SupplierRisk']=='High Risk']
    high_risk_suppliers[['SupplierName','OnTimeDeliveryRate','DefectRate']].to_csv('high_risk_suppliers.csv', index=False)
    print('Alert: Saved high-risk supplier report!')
else:
    print('SupplierRisk not available, report not generated.')

ffghfh