📦 Supply Chain Analytics

"""
PROJET 5 : Supply Chain Analytics
Optimisation des stocks et prévisions
"""

import pandas as pd
import numpy as np
import duckdb
from scipy import stats

# Calculer les KPIs Supply Chain
def calculate_supply_chain_kpis(df):
    con = duckdb.connect()
    
    # KPI 1 : Taux de service
    kpis = con.execute("""
        SELECT 
            COUNT(CASE WHEN stockout = 0 THEN 1 END) * 100.0 / COUNT(*) as service_level,
            SUM(CASE WHEN stockout = 1 THEN demand ELSE 0 END) as lost_sales,
            AVG(stock_end) as avg_stock
        FROM df
    """).fetchdf()
    
    print(f"Taux de service : {kpis["service_level"].iloc[0]:.2f}%")
    print(f"Ventes perdues : {kpis["lost_sales"].iloc[0]:,.0f} unités")
    
    return kpis

# Classification ABC/XYZ
def abc_xyz_analysis(df):
    # ABC : Basé sur le CA
    abc = df.groupby("product_id").agg({
        "demand": "sum",
        "unit_cost": "first"
    }).reset_index()
    
    abc["revenue"] = abc["demand"] * abc["unit_cost"]
    abc = abc.sort_values("revenue", ascending=False)
    
    # Cumul
    abc["cumul_pct"] = (abc["revenue"].cumsum() / abc["revenue"].sum()) * 100
    
    # Classification ABC
    abc["ABC"] = "C"
    abc.loc[abc["cumul_pct"] <= 80, "ABC"] = "A"
    abc.loc[(abc["cumul_pct"] > 80) & (abc["cumul_pct"] <= 95), "ABC"] = "B"
    
    return abc

# Calculer le stock de sécurité
def calculate_safety_stock(df, service_level=0.95):
    z_score = stats.norm.ppf(service_level)
    
    safety_stock = df.groupby("product_id").agg({
        "demand": ["mean", "std"],
        "lead_time": "first"
    }).reset_index()
    
    safety_stock.columns = ["product_id", "avg_demand", "std_demand", "lead_time"]
    
    # Formule du stock de sécurité
    safety_stock["safety_stock"] = (
        z_score * safety_stock["std_demand"] * np.sqrt(safety_stock["lead_time"])
    ).round(0)
    
    # Point de commande
    safety_stock["reorder_point"] = (
        safety_stock["avg_demand"] * safety_stock["lead_time"] + 
        safety_stock["safety_stock"]
    ).round(0)
    
    return safety_stock