app.py

import streamlit as st
import pandas as pd
import numpy as np
from prophet import Prophet
import plotly.graph_objects as go
from datetime import timedelta, datetime
import os
import math
from sqlalchemy import create_engine, text
import urllib.parse
from streamlit_autorefresh import st_autorefresh
import gc 

# =====================================================
# 1. CONFIGURATION & CONNEXION
# =====================================================
st.set_page_config(page_title="Master Planner - Perf & Plan", layout="wide")

DB_USER = "balakibawi"
DB_PASS = "M@tch47om_2026"
DB_HOST = "10.228.11.110"
DB_NAME = "wfm_reporting"

@st.cache_resource
def get_engine():
    try:
        safe_password = urllib.parse.quote_plus(DB_PASS)
        return create_engine(f"mysql+pymysql://{DB_USER}:{safe_password}@{DB_HOST}/{DB_NAME}", connect_args={'connect_timeout': 5})
    except:
        return None

# =====================================================
# 2. FONCTIONS DE CALCUL (ERLANG & PRECISION)
# =====================================================
def apply_business_rules(row, acts):
    h = row['ds'].hour + row['ds'].minute/60
    d = row['ds'].weekday()
    if h < 7 or h >= 21: return 0
    if "PDV" in acts and d == 6: return 0
    return max(0, row['yhat'])

def erlang_c_besoin(calls, aht, interval_sec=1800, service_level=0.8, target_time=20):
    calls = max(0, calls)
    if calls <= 0 or aht <= 0: return 0
    intensity = (calls * aht) / interval_sec
    agents = math.ceil(intensity) + 1
    
    def get_service_level(n, intensity, aht, target_time):
        try:
            rho = intensity / n
            if rho >= 1: return 0
            c_part = math.exp(n * math.log(intensity) - (math.lgamma(n + 1) + math.log(1 - rho)))
            sum_inv = sum([math.exp(i * math.log(intensity) - math.lgamma(i + 1)) for i in range(n)])
            prob_attente = c_part / (sum_inv + c_part)
            return 1 - (prob_attente * math.exp(-(n - intensity) * (target_time / aht)))
        except: return 0

    while agents < 500:
        if get_service_level(agents, intensity, aht, target_time) >= service_level: break
        agents += 1
    return agents

def calculer_precision_performance(reel, prev):
    mask = (reel.notnull()) & (reel > 0)
    if not mask.any(): return 0.0
    erreur = np.sum(np.abs(reel[mask].values - prev[mask].clip(lower=0).values))
    somme = np.sum(reel[mask].values)
    return max(0, min(100, (1 - (erreur / somme)) * 100)) if somme > 0 else 0.0

# =====================================================
# 3. CHARGEMENT DES DONNÉES
# =====================================================
@st.cache_data(ttl=300) 
def load_data_source(uploaded_file=None):
    if uploaded_file is not None:
        df = pd.read_csv(uploaded_file)
    else:
        path = "full_history.csv"
        if os.path.exists(path):
            df = pd.read_csv(path)
        else:
            return pd.DataFrame()
            
    df['ds'] = pd.to_datetime(df['ds'])
    all_times = pd.date_range(start=df['ds'].min(), end=df['ds'].max(), freq='30min')
    df_list = []
    for act in df['activite'].unique():
        temp = df[df['activite'] == act].set_index('ds').reindex(all_times).fillna(0).reset_index()
        temp['activite'] = act
        temp.rename(columns={'index': 'ds'}, inplace=True)
        df_list.append(temp)
    return pd.concat(df_list, ignore_index=True)

# =====================================================
# 4. MOTEUR DE PRÉVISION
# =====================================================
@st.cache_resource(ttl=3600)
def train_and_forecast(_df, activities):
    if _df.empty or not activities: return pd.DataFrame()
    results = []
    for act in activities:
        gc.collect()
        df_act = _df[_df['activite'] == act].tail(8000).copy()
        
        m_vol = Prophet(seasonality_mode='multiplicative', daily_seasonality=True, weekly_seasonality=True, uncertainty_samples=50)
        m_vol.add_country_holidays(country_name='FR')
        m_vol.fit(df_act[['ds', 'y']])
        
        m_aht = Prophet(daily_seasonality=True, weekly_seasonality=True, uncertainty_samples=50)
        m_aht.fit(df_act[['ds', 'aht']].rename(columns={'aht': 'y'}))
        
        future = m_vol.make_future_dataframe(periods=48*14, freq="30min")
        res_vol = m_vol.predict(future)[['ds', 'yhat']]
        res_aht = m_aht.predict(future)[['ds', 'yhat']].rename(columns={'yhat': 'aht_hat'})
        
        res_act = res_vol.merge(res_aht, on='ds')
        res_act['activite'] = act
        results.append(res_act)
    return pd.concat(results, ignore_index=True) if results else pd.DataFrame()

# =====================================================
# 5. UI PRINCIPALE
# =====================================================
st.sidebar.title("📊 Pilotage Perf & Plan")
up_file = st.sidebar.file_uploader("Mettre à jour le CSV", type="csv")

df_full = load_data_source(up_file)

if df_full.empty:
    st.info("Veuillez uploader un fichier 'full_history.csv' pour commencer.")
    st.stop()

mode = st.sidebar.selectbox("Vue", ["Rétrospective", "Planification Futur"])
all_acts = sorted(df_full['activite'].unique().tolist())
sel_act = st.sidebar.multiselect("Activités", options=all_acts, default=all_acts[:2])

# --- NOUVEAU : SIMULATEUR DE SCÉNARIO ---
st.sidebar.markdown("---")
st.sidebar.header("🧪 Simulateur d'Impact")
var_vol = st.sidebar.slider("Variation Volume (%)", -30, 50, 0)
var_aht = st.sidebar.slider("Variation DMT (%)", -20, 30, 0)

if not sel_act:
    st.warning("Sélectionnez une activité.")
    st.stop()

with st.spinner("Calcul des prévisions..."):
    fc_all = train_and_forecast(df_full[df_full['activite'].isin(sel_act)], sel_act)

if not fc_all.empty:
    fc_all['yhat'] = fc_all.apply(lambda r: apply_business_rules(r, [r['activite']]), axis=1)
    fc_all['work_load_pred'] = fc_all['yhat'] * fc_all['aht_hat']
    
    df_filtered = df_full[df_full['activite'].isin(sel_act)][['ds', 'activite', 'y']]
    hist = fc_all.merge(df_filtered, on=['ds', 'activite'], how='left')
    
    hist_agg = hist.groupby('ds').agg({'y': 'sum', 'yhat': 'sum', 'work_load_pred': 'sum'}).reset_index()
    hist_agg['aht_hat'] = (hist_agg['work_load_pred'] / hist_agg['yhat']).fillna(180)
    
    d_min, d_max = hist_agg['ds'].min().date(), hist_agg['ds'].max().date()
    sel_range = st.sidebar.date_input("Période", value=(d_min, d_max))
    
    if len(sel_range) == 2:
        mask = (hist_agg['ds'].dt.date >= sel_range[0]) & (hist_agg['ds'].dt.date <= sel_range[1])
        view = hist_agg[mask].copy()
        
        # --- APPLICATION SIMULATION ---
        view['yhat_sim'] = view['yhat'] * (1 + var_vol/100)
        view['aht_sim'] = view['aht_hat'] * (1 + var_aht/100)
        
        # Calcul besoins (Base vs Simulé)
        view['besoin_agents'] = view.apply(lambda r: erlang_c_besoin(r['yhat'], r['aht_hat']), axis=1)
        view['besoin_sim'] = view.apply(lambda r: erlang_c_besoin(r['yhat_sim'], r['aht_sim']), axis=1)
        
        st.title(f"🚀 Master Planner - {mode}")
        
        # Métriques
        c1, c2, c3, c4 = st.columns(4)
        c1.metric("🎯 Précision", f"{calculer_precision_performance(view['y'], view['yhat']):.1f}%")
        c2.metric("📈 Volume Prévu", f"{int(view['yhat_sim'].sum()):,}", delta=f"{var_vol}%" if var_vol!=0 else None)
        
        staff_base = math.ceil(view[view['besoin_agents']>0]['besoin_agents'].mean())
        staff_sim = math.ceil(view[view['besoin_sim']>0]['besoin_sim'].mean())
        c3.metric("👥 Staff Requis", f"{staff_sim} agents", delta=f"{staff_sim - staff_base} agents" if staff_sim != staff_base else None)
        
        dmt_moy = int(view['aht_sim'].mean())
        c4.metric("⏱️ DMT (moy)", f"{dmt_moy}s", delta=f"{var_aht}%" if var_aht!=0 else None)

        # Graphique
        fig = go.Figure()
        if mode == "Rétrospective":
            fig.add_trace(go.Scatter(x=view['ds'], y=view['y'], name="RÉEL", line=dict(color="#1f77b4", width=3)))
        
        fig.add_trace(go.Scatter(x=view['ds'], y=view['yhat_sim'], name="PRÉVISION (Simulée)", line=dict(color="#ff7f0e", dash='dot')))
        
        fig.update_layout(title="Courbe de Charge (Workload)", hovermode="x unified")
        st.plotly_chart(fig, use_container_width=True)
        
        # Tableau de bord Staffing
        with st.expander("📅 Détails de la planification"):
            st.dataframe(view[['ds', 'y', 'yhat_sim', 'aht_sim', 'besoin_sim']].rename(columns={
                'ds': 'Intervalle', 'y': 'Réel', 'yhat_sim': 'Prévu', 'aht_sim': 'DMT', 'besoin_sim': 'Agents Requis'
            }), use_container_width=True)

        st.download_button("📥 Exporter le plan de charge", view.to_csv(index=False), "planification.csv", "text/csv")

# =====================================================
# 9. FOOTER
# =====================================================
st.sidebar.markdown("---")
st.sidebar.caption(f"Propulsé par Prophet & Erlang-C | MatchAtom 2026")