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

# Load REAL retail demand data from UCI Online Retail II
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))

# Calculate daily item sales volume
sales_volume = df.groupby(['InvoiceDate', 'StockCode'])['Quantity'].sum().reset_index()
print(sales_volume.head())

# Plot inventory activity for a single item
import matplotlib.pyplot as plt
item_code = sales_volume['StockCode'].iloc[0]
item_sales = sales_volume[sales_volume['StockCode'] == item_code]
plt.figure(figsize=(10,4))
plt.plot(item_sales['InvoiceDate'], item_sales['Quantity'], marker='o')
plt.title(f'Daily Quantity for Item {item_code}')
plt.xlabel('Date')
plt.ylabel('Net Quantity Sold')
plt.grid(True)
plt.show()

# Compute cumulative inventory change for an example item
item_sales = item_sales.sort_values('InvoiceDate')
item_sales['cum_qty'] = item_sales['Quantity'].cumsum()
plt.figure(figsize=(10,4))
plt.step(item_sales['InvoiceDate'], item_sales['cum_qty'], where='mid')
plt.title(f'Cumulative Inventory Change: Item {item_code}')
plt.xlabel('Date')
plt.ylabel('Cumulative Net Units')
plt.grid(True)
plt.show()

# Beginner example: Identify stockouts (quantity reaches zero or negative)
item_sales['stockout'] = item_sales['cum_qty'] <= 0
print(item_sales[item_sales['stockout']][['InvoiceDate', 'cum_qty']])

# Beginner: What is the most commonly sold item by transaction count?
top_item = df['StockCode'].value_counts().idxmax()
print(f'Most common StockCode: {top_item}')

# Beginner: Identify returns (negative Quantity)
returns = df[df['Quantity'] < 0]
print(returns[['InvoiceDate', 'StockCode', 'Quantity']].head())

# Intermediate: Track inventory changes for multiple SKUs over time
sku_subset = df['StockCode'].unique()[:3]
multi_items = df[df['StockCode'].isin(sku_subset)]
multi_sales = multi_items.groupby(['InvoiceDate', 'StockCode'])['Quantity'].sum().reset_index()
multi_sales['cum_qty'] = multi_sales.groupby('StockCode')['Quantity'].cumsum()
for code in sku_subset:
    plt.plot(multi_sales[multi_sales['StockCode'] == code]['InvoiceDate'],
             multi_sales[multi_sales['StockCode'] == code]['cum_qty'], label=f'StockCode {code}')
plt.legend()
plt.title('Multi-SKU Cumulative Inventory Change')
plt.xlabel('Date')
plt.ylabel('Cumulative Net Units')
plt.show()

# Intermediate: Aggregate net inventory change by week
df['Week'] = df['InvoiceDate'].dt.to_period('W').apply(lambda r: r.start_time)
weekly_inv = df.groupby(['Week', 'StockCode'])['Quantity'].sum().reset_index()
weekly_inv['cum_qty'] = weekly_inv.groupby('StockCode')['Quantity'].cumsum()
print(weekly_inv.head())

# Intermediate: Check for data gaps (missing transaction dates)
all_dates = pd.date_range(df['InvoiceDate'].min(), df['InvoiceDate'].max())
dates_present = pd.to_datetime(df['InvoiceDate'].dt.date.unique())
missing_dates = set(all_dates.date) - set(dates_present)
print(f'Missing transaction dates: {sorted(list(missing_dates))[:5]} ...')

# Advanced: Use OpenML operational dataset for additional inventory patterns
import openml
dataset = openml.datasets.get_dataset(43900)
X, y, _, _ = dataset.get_data(dataset_format='dataframe')
manufacturing_df = X.copy() if y is None else pd.concat([X, y], axis=1)
print(manufacturing_df.shape)
print(manufacturing_df.head(3))

# Advanced: Detect negative inventory in OpenML manufacturing data (if present)
if 'inventory' in manufacturing_df.columns:
    negatives = manufacturing_df[manufacturing_df['inventory'] < 0]
    print(f'Negative inventory events: {len(negatives)}')
    print(negatives.head())
else:
    print('No inventory column available in this manufacturing dataset.')

# Advanced: Merge retail transactions with product information (simulated lookup)
if 'Description' in df.columns:
    items_info = df[['StockCode', 'Description']].drop_duplicates()
    merged = pd.merge(sales_volume, items_info, on='StockCode', how='left')
    print(merged.head())
else:
    print('No Description column found.')

# Error handling: Missing quantities
missing_qty = df[df['Quantity'].isnull()]
print(f'Rows with missing Quantity: {len(missing_qty)}')

# Error handling: Incorrect join keys (simulate a mismatch)
bad_lookup = items_info.rename(columns={'StockCode': 'SKU'})
merged_bad = pd.merge(sales_volume, bad_lookup, left_on='StockCode', right_on='SKU', how='left')
nulls = merged_bad['Description'].isnull().sum()
print(f'Rows with missing Description after join: {nulls}')

# Error handling: Misinterpreting negative quantities as positive movements
mistake = df.copy()
mistake['fixed_qty'] = mistake['Quantity'].abs()
pos_sum = mistake[mistake['Quantity'] > 0]['fixed_qty'].sum()
neg_sum = mistake[mistake['Quantity'] < 0]['fixed_qty'].sum()
print(f'Wrongly treating all movement as positive: {pos_sum + neg_sum}')

# Best practice: Use time series aggregation to spot seasonal trends
monthly_qty = df.groupby(df['InvoiceDate'].dt.to_period('M'))['Quantity'].sum()
monthly_qty.plot(kind='bar', figsize=(12,6), title='Monthly Net Inventory Change')
plt.xlabel('Month')
plt.ylabel('Net Units Sold (or Returned)')
plt.tight_layout()
plt.show()

# Best practice: Calculate inventory turnover KPI
total_units_sold = df[df['Quantity'] > 0]['Quantity'].sum()
avg_inventory = sales_volume['Quantity'].mean()
turnover = total_units_sold / avg_inventory if avg_inventory != 0 else np.nan
print(f'Inventory Turnover Ratio: {turnover:.2f}')

# End-to-end: Build daily inventory position for one SKU
item = top_item
sku_hist = df[df['StockCode'] == item]
date_range = pd.date_range(sku_hist['InvoiceDate'].min(), sku_hist['InvoiceDate'].max())
pos = sku_hist.groupby(sku_hist['InvoiceDate'].dt.date)['Quantity'].sum().reindex(date_range.date, fill_value=0).cumsum()
inventory_df = pd.DataFrame({'Date': date_range.date, 'InventoryPosition': pos.values})
print(inventory_df.head())

# Write the SKU inventory position to a CSV so it can be visualized or shared
csvfile = 'sku_inventory_position.csv'
inventory_df.to_csv(csvfile, index=False)
print(f'File saved: {csvfile}')