src/module/Data_Exploration.py

"""
MODULE: DATA EXPLORATION (ADVANCED EDA) - VERSION PROFESSIONNELLE
========================================================================
Analyses hospitalières : Démographie, Flux, Occupation, Stocks et Clustering
"""

import streamlit as st
import pandas as pd
import numpy as np
import plotly.express as px
import plotly.graph_objects as go
from datetime import datetime

# Vérification des dépendances statistiques
try:
    from sklearn.cluster import KMeans
    from sklearn.preprocessing import StandardScaler
    from scipy import stats
    STATS_AVAILABLE = True
except ImportError:
    STATS_AVAILABLE = False

# Import de ton moteur ETL (Assure-toi que le chemin est correct)
try:
    from module.Validation_Contract import ETL_Engine
except ImportError:
    st.error("Le module ETL_Engine est introuvable. Vérifiez l'arborescence.")

# =====================================================
# ============= CACHE ETL =============================
# =====================================================

@st.cache_data(show_spinner=False, ttl=3600)
def cached_run_etl(_gspread_client, sheet_name):
    etl = ETL_Engine(_gspread_client, sheet_name)
    return etl.run_etl()

# =====================================================
# ============= ANALYSEUR AVANCÉ ======================
# =====================================================

class HospitalAdvancedAnalyzer:
    def __init__(self, gspread_client, sheet_name):
        self.client = gspread_client
        self.sheet_name = sheet_name
        self.dataframes_raw = {}
        self.dataframes_clean = {}
        
    def load_data(self):
        """Charge et prépare les données une seule fois"""
        success, result = cached_run_etl(self.client, self.sheet_name)
        if success:
            self.dataframes_raw = result.dataframes_raw
            self.dataframes_clean = result.dataframes_clean
            return True
        return False

    def get_df(self, keyword):
        """Récupération intelligente des tables Clean"""
        for k, df in self.dataframes_clean.items():
            if keyword.lower() in k.lower():
                return df.copy()
        return None

    # ========== 1. DÉMOGRAPHIE ==========
    
    def plot_pyramide_ages(self):
        st.markdown("###  Pyramide des Âges")
        df = self.get_df('patients')
        if df is None: return

        df = df[(df['age'] >= 0) & (df['age'] <= 110)].copy()
        bins = [0, 18, 30, 45, 60, 75, 120]
        labels = ['0-18', '19-30', '31-45', '46-60', '61-75', '75+']
        df['age_group'] = pd.cut(df['age'], bins=bins, labels=labels, right=False)
        
        pyramid = df.groupby(['age_group', 'sexe'], observed=False).size().reset_index(name='count')
        
        fig = go.Figure()
        # Hommes (valeurs négatives pour l'orientation à gauche)
        males = pyramid[pyramid['sexe'] == 'M']
        fig.add_trace(go.Bar(y=males['age_group'], x=-males['count'], name='Hommes', 
                             orientation='h', marker_color='steelblue', hoverinfo='x+y'))
        
        # Femmes (à droite)
        females = pyramid[pyramid['sexe'] == 'F']
        fig.add_trace(go.Bar(y=females['age_group'], x=females['count'], name='Femmes', 
                             orientation='h', marker_color='pink', hoverinfo='x+y'))

        fig.update_layout(
            title='Répartition par Sexe et Tranche d\'Âge',
            barmode='overlay',
            xaxis=dict(title='Nombre de Patients', tickmode='array',
                       tickvals=[-100, -50, 0, 50, 100], # Ajuster selon volume
                       ticktext=['100', '50', '0', '50', '100']),
            height=500
        )
        st.plotly_chart(fig, use_container_width=True)

    def plot_geographic_distribution(self):
        st.markdown("###  Répartition Géographique")
        df = self.get_df('patients')
        if df is None: return
        
        col1, col2 = st.columns(2)
        with col1:
            top_villes = df['ville'].value_counts().head(10).reset_index()
            fig = px.bar(top_villes, x='ville', y='count', title="Top 10 Villes", color='count')
            st.plotly_chart(fig, use_container_width=True)
        with col2:
            fig_cp = px.treemap(df, path=[px.Constant("Total"), 'code_postal'], title="Répartition par CP")
            st.plotly_chart(fig_cp, use_container_width=True)

    # ========== 2. PATHOLOGIES ==========

    def plot_pathologies_analysis(self):
        st.markdown("###  Analyse des Pathologies")
        df = self.get_df('sejours')
        if df is None: return

        stats_patho = df.groupby('pathologie_code').agg({
            'stay_id': 'count',
            'gravite_1_5': 'mean',
            'duree_sejour_jours': 'mean'
        }).reset_index().sort_values('stay_id', ascending=False).head(15)

        fig = px.scatter(stats_patho, x='gravite_1_5', y='duree_sejour_jours', size='stay_id', 
                         color='pathologie_code', title="Gravité vs Durée de séjour (Taille = Volume)")
        st.plotly_chart(fig, use_container_width=True)

    # ========== 3. FLUX & SANKEY ==========

    def plot_service_flow(self):
        st.markdown("###  Flux Patients inter-services")
        df = self.get_df('sejours')
        if df is None: return

        df = df.sort_values(['patient_id', 'date_entree'])
        df['service_prev'] = df.groupby('patient_id')['service_code'].shift(1)
        transfers = df[df['service_prev'].notna() & (df['service_code'] != df['service_prev'])]
        
        if transfers.empty:
            st.info("Aucun transfert inter-services détecté.")
            return

        flow = transfers.groupby(['service_prev', 'service_code']).size().reset_index(name='value')
        nodes = list(pd.concat([flow['service_prev'], flow['service_code']]).unique())
        node_map = {name: i for i, name in enumerate(nodes)}

        fig = go.Figure(go.Sankey(
            node=dict(pad=15, thickness=20, label=nodes),
            link=dict(source=flow['service_prev'].map(node_map), 
                      target=flow['service_code'].map(node_map), 
                      value=flow['value'])
        ))
        st.plotly_chart(fig, use_container_width=True)

    # ========== 4. TEMPOREL & SAISONNALITÉ ==========

    def plot_temporal_evolution(self):
        st.markdown("###  Évolution des Admissions")
        df = self.get_df('admissions_jour')
        if df is None: return

        df['date'] = pd.to_datetime(df['date'])
        df['nb_admissions'] = pd.to_numeric(df['nb_admissions'], errors='coerce')
        
        # Séries par mois
        df['mois'] = df['date'].dt.to_period('M').dt.to_timestamp()
        monthly = df.groupby(['mois', 'service_code'])['nb_admissions'].sum().reset_index()
        
        fig = px.line(monthly, x='mois', y='nb_admissions', color='service_code', markers=True)
        st.plotly_chart(fig, use_container_width=True)

    def plot_seasonality_analysis(self):
        st.markdown("###  Saisonnalité Hebdomadaire")
        df = self.get_df('admissions_jour')
        if df is None: return

        df['date'] = pd.to_datetime(df['date'])
        df['day_num'] = df['date'].dt.weekday
        df['day_name'] = df['date'].dt.day_name()
        
        # Tri automatique Lundi -> Dimanche
        day_stats = df.groupby(['day_num', 'day_name'])['nb_admissions'].mean().reset_index().sort_values('day_num')
        
        fig = px.bar(day_stats, x='day_name', y='nb_admissions', color='nb_admissions', 
                     title="Moyenne d'admissions par jour")
        st.plotly_chart(fig, use_container_width=True)

    # ========== 5. RESSOURCES & TENSIONS ==========

    def plot_occupation_analysis(self):
        st.markdown("###  Occupation des Lits")
        df = self.get_df('ressources_jour')
        if df is None: return

        df['taux_pct'] = pd.to_numeric(df['taux_occupation'], errors='coerce') * 100
        avg_occ = df.groupby('service_code')['taux_pct'].mean().reset_index().sort_values('taux_pct', ascending=False)
        
        fig = px.bar(avg_occ, x='service_code', y='taux_pct', text_auto='.1f', 
                     title="Taux d'occupation moyen (%)", color='taux_pct', color_continuous_scale='Reds')
        fig.add_hline(y=100, line_dash="dash", line_color="red")
        st.plotly_chart(fig, use_container_width=True)

    def plot_workload_analysis(self):
        st.markdown("###  Charge de Travail (Tension)")
        df = self.get_df('ressources_jour')
        if df is None: return

        fig = px.box(df, x='service_code', y='tension_personnel', color='service_code', 
                     title="Distribution de la tension du personnel (1.0 = Seuil critique)")
        st.plotly_chart(fig, use_container_width=True)

    # ========== 6. STOCKS ==========

    def plot_stock_analysis(self):
        st.markdown("###  État des Stocks")
        df = self.get_df('stock_materiel_jour')
        if df is None: return

        stock_avg = df.groupby('materiel_code').agg({'stock_disponible': 'mean', 'tension_stock': 'mean'}).reset_index()
        fig = px.bar(stock_avg, x='materiel_code', y='stock_disponible', color='tension_stock', 
                     title="Stock Moyen vs Tension", color_continuous_scale='RdYlGn_r')
        st.plotly_chart(fig, use_container_width=True)

    # ========== 7. CLUSTERING & STATS ==========

    def plot_patient_clustering(self):
        st.markdown("###  Segmentation Patients (K-Means)")
        if not STATS_AVAILABLE:
            st.warning("Bibliothèques scikit-learn manquantes.")
            return

        df_pat = self.get_df('patients')
        df_sej = self.get_df('sejours')
        if df_pat is None or df_sej is None: return

        # Préparation des features
        feat = df_sej.groupby('patient_id').agg({'stay_id': 'count', 'duree_sejour_jours': 'mean', 'gravite_1_5': 'mean'}).reset_index()
        feat = feat.merge(df_pat[['patient_id', 'age']], on='patient_id').dropna()
        
        if len(feat) < 10: return

        # Clustering
        X = feat[['age', 'stay_id', 'duree_sejour_jours', 'gravite_1_5']]
        scaler = StandardScaler()
        X_scaled = scaler.fit_transform(X)
        
        kmeans = KMeans(n_clusters=3, random_state=42, n_init=10)
        feat['cluster'] = kmeans.fit_predict(X_scaled).astype(str)

        fig = px.scatter_3d(feat, x='age', y='stay_id', z='gravite_1_5', color='cluster',
                            title="Clusters de patients", labels={'stay_id': 'Nb Séjours'})
        st.plotly_chart(fig, use_container_width=True)

    def plot_statistical_tests(self):
        st.markdown("###  Analyse de Variance (ANOVA)")
        df = self.get_df('sejours')
        if df is None or not STATS_AVAILABLE: return

        # ANOVA: Durée de séjour entre les 4 plus gros services
        top_services = df['service_code'].value_counts().head(4).index
        groups = [df[df['service_code'] == s]['duree_sejour_jours'].dropna() for s in top_services]
        
        f_stat, p_val = stats.f_oneway(*groups)
        
        c1, c2 = st.columns(2)
        c1.metric("F-Statistic", f"{f_stat:.2f}")
        c2.metric("P-Value", f"{p_val:.4f}")
        
        if p_val < 0.05:
            st.success("Il existe une différence statistiquement significative entre les services.")
        else:
            st.info("Pas de différence significative démontrée.")

    # ========== 8. EXPORT DONNÉES ==========

    def get_all_metrics_dataframes(self):
        """Génère un dictionnaire de tous les tableaux d'analyse pour export"""
        exports = {}
        
        # 1. Démographie
        df_pat = self.get_df('patients')
        if df_pat is not None:
            # Répartition Villes
            exports['demographie_villes'] = df_pat['ville'].value_counts().reset_index(name='nombre_patients')
            # Répartition Ages (Simplifiée)
            exports['demographie_ages'] = df_pat[['age', 'sexe']].describe().reset_index()

        # 2. Pathologies (Stats agrégées)
        df_sej = self.get_df('sejours')
        if df_sej is not None:
            patho_stats = df_sej.groupby('pathologie_code').agg({
                'stay_id': 'count',
                'gravite_1_5': 'mean',
                'duree_sejour_jours': 'mean'
            }).reset_index()
            patho_stats.columns = ['Pathologie', 'Nb Séjours', 'Gravité Moyenne', 'Durée Moyenne']
            exports['analyse_pathologies'] = patho_stats

        # 3. Admissions Mensuelles
        df_adm = self.get_df('admissions_jour')
        if df_adm is not None:
            df_adm['date'] = pd.to_datetime(df_adm['date'])
            df_adm['nb_admissions'] = pd.to_numeric(df_adm['nb_admissions'], errors='coerce')
            monthly = df_adm.set_index('date').resample('M')['nb_admissions'].sum().reset_index()
            exports['admissions_mensuelles'] = monthly

        # 4. Occupation & RH
        df_res = self.get_df('ressources_jour')
        if df_res is not None:
            rh_stats = df_res.groupby('service_code').agg({
                'taux_occupation': 'mean',
                'tension_personnel': 'mean',
                'lits_occupes': 'max'
            }).reset_index()
            exports['analyse_services_rh'] = rh_stats

        # 5. Stocks
        df_stock = self.get_df('stock_materiel_jour')
        if df_stock is not None:
            stock_stats = df_stock.groupby('materiel_code').agg({
                'stock_disponible': 'mean',
                'tension_stock': 'mean',
                'alerte_stock': 'sum'
            }).reset_index()
            exports['analyse_stocks'] = stock_stats

        return exports

# =====================================================
# ============= INTERFACE STREAMLIT ===================
# =====================================================

def show_terminal(client, sheet_name):
    st.markdown('##  DATA LAB : EXPLORATION AVANCÉE')
    
    if 'hospital_analyzer' not in st.session_state:
        st.session_state.hospital_analyzer = HospitalAdvancedAnalyzer(client, sheet_name)
    
    analyzer = st.session_state.hospital_analyzer

    if not analyzer.dataframes_clean:
        with st.spinner('Chargement du Lab...'):
            if not analyzer.load_data():
                st.error("Erreur de connexion aux données.")
                return
            st.rerun()

    # --- AJOUT DE L'ONGLET "📥 Export" ---
    tabs = st.tabs([
        " Démographie", " Pathos", " Flux", " Temps", 
        " Lits", " RH", " Stocks", " Clusters", " Stats", "📥 Export"
    ])

    with tabs[0]: 
        analyzer.plot_pyramide_ages()
        analyzer.plot_geographic_distribution()
    with tabs[1]: analyzer.plot_pathologies_analysis()
    with tabs[2]: analyzer.plot_service_flow()
    with tabs[3]: 
        analyzer.plot_temporal_evolution()
        analyzer.plot_seasonality_analysis()
    with tabs[4]: analyzer.plot_occupation_analysis()
    with tabs[5]: analyzer.plot_workload_analysis()
    with tabs[6]: analyzer.plot_stock_analysis()
    with tabs[7]: analyzer.plot_patient_clustering()
    with tabs[8]: analyzer.plot_statistical_tests()
    
    # --- LOGIQUE DE L'ONGLET EXPORT ---
    with tabs[9]:
        st.markdown("###  Téléchargement des Rapports d'Analyse")
        st.caption("Téléchargez les résultats agrégés calculés par le Data Lab au format CSV.")
        
        # Récupération des données
        metrics_data = analyzer.get_all_metrics_dataframes()
        
        if metrics_data:
            col1, col2 = st.columns(2)
            
            # Affichage en deux colonnes pour être propre
            for i, (name, df) in enumerate(metrics_data.items()):
                # Alternance des colonnes
                col = col1 if i % 2 == 0 else col2
                
                with col:
                    # Conversion en CSV
                    csv = df.to_csv(index=False).encode('utf-8-sig') # utf-8-sig pour compatibilité Excel
                    
                    st.download_button(
                        label=f" {name.replace('_', ' ').title()} (.csv)",
                        data=csv,
                        file_name=f"datalab_{name}_{datetime.now().strftime('%Y%m%d')}.csv",
                        mime='text/csv',
                        use_container_width=True
                    )
                    # Petit aperçu (optionnel)
                    with st.expander(f" Aperçu : {name}"):
                        st.dataframe(df.head(5), use_container_width=True)
        else:
            st.warning("Aucune donnée d'analyse disponible pour l'export.")