src/modules/loans_engine.py

import streamlit as st
import pandas as pd
from datetime import datetime, date, timedelta
from Analytics.AnalyseFinance import (
    analyser_capacite, 
    generer_tableau_amortissement,
    calculer_taux_endettement,
    clean_taux_value
)
from DocumentGen.AutoPDFGeneration import generer_contrat_pret, generer_reconnaissance_dette, generer_contrat_caution
import time


# ============================================================================
# GESTION DU CACHE
# ============================================================================

@st.cache_data(ttl=600)
def get_cached_data(_client, sheet_name, worksheet_name):
    try:
        sh = _client.open(sheet_name)
        ws = sh.worksheet(worksheet_name)
        return pd.DataFrame(ws.get_all_records())
    except Exception as e:
        st.error(f"Erreur lors de la lecture de {worksheet_name}: {e}")
        return pd.DataFrame()

def refresh_data():
    st.cache_data.clear()
    st.rerun()


# ============================================================================
# CSS — STYLE GOTHAM / PALANTIR ALIGNÉ SUR streamlit_app.py
# ============================================================================

def apply_loans_engine_styles():
    st.markdown("""
    <style>
    @import url('https://fonts.googleapis.com/css2?family=JetBrains+Mono:wght@400;600;700;800&display=swap');

    #loans-engine-module {
        font-family: 'JetBrains Mono', 'Courier New', monospace;
    }

    /* ── OFFER SELECTION CARDS ── */
    .offer-card {
        background: rgba(10, 14, 18, 0.9);
        border: 1px solid rgba(80, 100, 120, 0.3);
        border-top: 2px solid rgba(80, 100, 120, 0.4);
        padding: 20px 18px;
        cursor: pointer;
        transition: all 0.15s ease;
        font-family: 'JetBrains Mono', monospace;
        height: 100%;
    }
    .offer-card.consumer   { border-top-color: rgba(88, 166, 255, 0.7); }
    .offer-card.decouvert  { border-top-color: rgba(138, 154, 170, 0.7); }
    .offer-card.private    { border-top-color: rgba(243, 156, 18, 0.7); }

    .offer-card.selected {
        background: rgba(15, 22, 32, 0.95);
        border-color: rgba(100, 130, 160, 0.6);
    }
    .offer-card.selected.consumer  { border-top-color: #58a6ff; box-shadow: 0 0 12px rgba(88, 166, 255, 0.15); }
    .offer-card.selected.decouvert { border-top-color: #8a9aaa; box-shadow: 0 0 12px rgba(138, 154, 170, 0.15); }
    .offer-card.selected.private   { border-top-color: #f39c12; box-shadow: 0 0 12px rgba(243, 156, 18, 0.15); }

    .offer-tag {
        font-size: 0.55rem;
        font-weight: 700;
        letter-spacing: 3px;
        text-transform: uppercase;
        margin-bottom: 10px;
        font-family: 'JetBrains Mono', monospace;
    }
    .offer-tag.consumer  { color: rgba(88, 166, 255, 0.7); }
    .offer-tag.decouvert { color: rgba(138, 154, 170, 0.7); }
    .offer-tag.private   { color: rgba(243, 156, 18, 0.7); }

    .offer-name {
        font-size: 0.85rem;
        font-weight: 800;
        letter-spacing: 2px;
        text-transform: uppercase;
        color: #a8b8c8;
        font-family: 'JetBrains Mono', monospace;
        margin-bottom: 10px;
        line-height: 1.3;
    }

    .offer-desc {
        font-size: 0.68rem;
        color: #4a5a6a;
        font-family: 'JetBrains Mono', monospace;
        line-height: 1.7;
        letter-spacing: 0.3px;
    }

    .offer-indicator {
        margin-top: 14px;
        font-size: 0.6rem;
        font-weight: 700;
        letter-spacing: 2px;
        text-transform: uppercase;
        font-family: 'JetBrains Mono', monospace;
        color: #3a4a5a;
    }
    .offer-indicator.active.consumer  { color: #58a6ff; }
    .offer-indicator.active.decouvert { color: #8a9aaa; }
    .offer-indicator.active.private   { color: #f39c12; }

    /* ── OFFER SELECTOR BUTTONS ── */
    #loans-engine-module .offer-btn button {
        background: transparent !important;
        border: 1px solid rgba(60, 80, 100, 0.3) !important;
        color: #3a4a5a !important;
        font-family: 'JetBrains Mono', monospace !important;
        font-size: 0.6rem !important;
        font-weight: 700 !important;
        letter-spacing: 1.5px !important;
        text-transform: uppercase !important;
        border-radius: 0 !important;
        padding: 5px 12px !important;
        width: 100% !important;
        margin-top: 8px !important;
    }
    #loans-engine-module .offer-btn.consumer button {
        border-color: rgba(88, 166, 255, 0.3) !important;
        color: rgba(88, 166, 255, 0.6) !important;
    }
    #loans-engine-module .offer-btn.consumer button:hover {
        border-color: #58a6ff !important;
        color: #58a6ff !important;
        background: rgba(88, 166, 255, 0.06) !important;
    }
    #loans-engine-module .offer-btn.decouvert button {
        border-color: rgba(138, 154, 170, 0.3) !important;
        color: rgba(138, 154, 170, 0.6) !important;
    }
    #loans-engine-module .offer-btn.decouvert button:hover {
        border-color: #8a9aaa !important;
        color: #8a9aaa !important;
        background: rgba(138, 154, 170, 0.06) !important;
    }
    #loans-engine-module .offer-btn.private button {
        border-color: rgba(243, 156, 18, 0.3) !important;
        color: rgba(243, 156, 18, 0.6) !important;
    }
    #loans-engine-module .offer-btn.private button:hover {
        border-color: #f39c12 !important;
        color: #f39c12 !important;
        background: rgba(243, 156, 18, 0.06) !important;
    }

    /* ── SELECTED BADGE ── */
    .offer-selected-badge {
        display: inline-block;
        font-family: 'JetBrains Mono', monospace;
        font-size: 0.6rem;
        font-weight: 700;
        letter-spacing: 2px;
        text-transform: uppercase;
        padding: 4px 10px;
        margin-bottom: 20px;
        margin-top: 4px;
        border: 1px solid;
    }
    .offer-selected-badge.consumer  { color: #58a6ff; border-color: rgba(88, 166, 255, 0.4); background: rgba(88, 166, 255, 0.06); }
    .offer-selected-badge.decouvert { color: #8a9aaa; border-color: rgba(138, 154, 170, 0.4); background: rgba(138, 154, 170, 0.06); }
    .offer-selected-badge.private   { color: #f39c12; border-color: rgba(243, 156, 18, 0.4); background: rgba(243, 156, 18, 0.06); }

    /* ── SECTION TAG ── */
    .le-section-tag {
        display: block;
        font-family: 'JetBrains Mono', monospace;
        font-size: 0.58rem;
        font-weight: 700;
        letter-spacing: 3px;
        text-transform: uppercase;
        color: #3a4a5a;
        border: 1px solid rgba(60, 80, 100, 0.25);
        padding: 3px 10px;
        margin-bottom: 16px;
        margin-top: 24px;
    }

    /* ── METRICS ── */
    #loans-engine-module [data-testid="stMetric"] {
        background: rgba(15, 20, 26, 0.85) !important;
        border: 1px solid rgba(100, 120, 140, 0.25) !important;
        border-top: 2px solid rgba(100, 120, 140, 0.5) !important;
        padding: 14px 16px !important;
        border-radius: 0 !important;
    }
    #loans-engine-module [data-testid="stMetric"] label {
        color: #5a6a7a !important;
        font-size: 0.65rem !important;
        font-weight: 700 !important;
        text-transform: uppercase !important;
        letter-spacing: 1.5px !important;
        font-family: 'JetBrains Mono', monospace !important;
    }
    #loans-engine-module [data-testid="stMetric"] [data-testid="stMetricValue"] {
        color: #a8b8c8 !important;
        font-size: 1.3rem !important;
        font-weight: 700 !important;
        font-family: 'JetBrains Mono', monospace !important;
    }

    /* ── BUTTONS ── */
    #loans-engine-module .stButton > button {
        background: transparent !important;
        border: 1px solid rgba(100, 120, 140, 0.4) !important;
        color: #7a8a9a !important;
        font-family: 'JetBrains Mono', monospace !important;
        font-size: 0.72rem !important;
        font-weight: 700 !important;
        letter-spacing: 1.5px !important;
        text-transform: uppercase !important;
        border-radius: 0 !important;
        padding: 8px 16px !important;
    }
    #loans-engine-module .stButton > button:hover {
        border-color: rgba(168, 184, 200, 0.6) !important;
        color: #a8b8c8 !important;
        background: rgba(100, 120, 140, 0.08) !important;
    }

    /* ── EXPANDERS ── */
    #loans-engine-module [data-testid="stExpander"] {
        border: 1px solid rgba(80, 100, 120, 0.3) !important;
        border-radius: 0 !important;
        background: rgba(15, 20, 26, 0.6) !important;
    }
    #loans-engine-module [data-testid="stExpander"] summary {
        font-family: 'JetBrains Mono', monospace !important;
        font-size: 0.72rem !important;
        letter-spacing: 1.5px !important;
        text-transform: uppercase !important;
        color: #5a6a7a !important;
    }

    /* ── ALERTS ── */
    #loans-engine-module .stAlert {
        border-radius: 0 !important;
        font-family: 'JetBrains Mono', monospace !important;
        font-size: 0.75rem !important;
    }

    /* ── FORM SUBMIT ── */
    #loans-engine-module [data-testid="stFormSubmitButton"] > button {
        background: transparent !important;
        border: 1px solid rgba(100, 120, 140, 0.5) !important;
        color: #7a8a9a !important;
        font-family: 'JetBrains Mono', monospace !important;
        font-size: 0.72rem !important;
        font-weight: 700 !important;
        letter-spacing: 2px !important;
        text-transform: uppercase !important;
        border-radius: 0 !important;
        padding: 10px 24px !important;
        width: 100%;
    }
    #loans-engine-module [data-testid="stFormSubmitButton"] > button:hover {
        border-color: rgba(168, 184, 200, 0.6) !important;
        color: #a8b8c8 !important;
        background: rgba(100, 120, 140, 0.08) !important;
    }
    </style>
    """, unsafe_allow_html=True)


# ============================================================================
# COMPOSANT : SÉLECTION D'OFFRE TACTIQUE
# ============================================================================

OFFRES = {
    "Offre Consumer Loan": {
        "key": "consumer",
        "tag": "// SEGMENT RETAIL",
        "desc": "Credit a la consommation destine aux particuliers.\nRemboursement hebdomadaire ou mensuel.\nMontants standards, durees courtes a moyennes.",
        "color": "#58a6ff"
    },
    "Offre Découvert Privilège": {
        "key": "decouvert",
        "tag": "// SEGMENT PREMIUM",
        "desc": "Facilite de caisse pour clients a profil etabli.\nSouplesse de remboursement personnalisee.\nAcces prioritaire et conditions negociees.",
        "color": "#8a9aaa"
    },
    "Offre Private Credit": {
        "key": "private",
        "tag": "// SEGMENT PRIVE",
        "desc": "Credit sur mesure pour projets d'envergure.\nMontants eleves, structuration sur-mesure.\nReserve aux profils analyses et valides.",
        "color": "#f39c12"
    }
}


def render_offer_selector():
    """
    Affiche le selecteur d'offre tactique (3 cartes HTML + boutons Streamlit).
    Retourne le nom de l'offre selectionnee ou None.
    """
    if 'selected_offre' not in st.session_state:
        st.session_state.selected_offre = None

    st.markdown('<span class="le-section-tag">// SELECTION DE L\'OFFRE</span>', unsafe_allow_html=True)

    cols = st.columns(3)

    for col, (offre_name, meta) in zip(cols, OFFRES.items()):
        key        = meta["key"]
        is_selected = st.session_state.selected_offre == offre_name
        card_class = f"offer-card {key} {'selected' if is_selected else ''}"
        indicator_class = f"offer-indicator {'active ' + key if is_selected else ''}"
        indicator_text  = "[ SELECTIONNE ]" if is_selected else "[ SELECTIONNER ]"

        with col:
            st.markdown(f"""
            <div class="{card_class}">
                <div class="offer-tag {key}">{meta['tag']}</div>
                <div class="offer-name">{offre_name}</div>
                <div class="offer-desc">{meta['desc'].replace(chr(10), '<br>')}</div>
                <div class="{indicator_class}">{indicator_text}</div>
            </div>
            """, unsafe_allow_html=True)

            st.markdown(f'<div class="offer-btn {key}">', unsafe_allow_html=True)
            if st.button(offre_name, key=f"btn_offre_{key}"):
                st.session_state.selected_offre = offre_name
                st.rerun()
            st.markdown('</div>', unsafe_allow_html=True)

    if st.session_state.selected_offre:
        meta = OFFRES[st.session_state.selected_offre]
        st.markdown(
            f'<span class="offer-selected-badge {meta["key"]}">'
            f'OFFRE ACTIVE : {st.session_state.selected_offre}'
            f'</span>',
            unsafe_allow_html=True
        )

    return st.session_state.selected_offre


# ============================================================================
# MAIN FUNCTION
# ============================================================================

def show_loans_engine(client, sheet_name):
    apply_loans_engine_styles()
    st.markdown('<div id="loans-engine-module">', unsafe_allow_html=True)

    st.markdown("## MOTEUR FINANCIER : OCTROI DE PRET")

    # ── SELECTION OFFRE ────────────────────────────────────────────────────
    offre_selectionnee = render_offer_selector()

    if not offre_selectionnee:
        st.markdown("""
        <div style="background:rgba(10,14,18,0.9); border:1px solid rgba(80,100,120,0.2);
                    border-left:4px solid rgba(80,100,120,0.4); padding:14px 18px; margin-top:8px;
                    font-family:'JetBrains Mono',monospace; font-size:0.75rem; color:#4a5a6a;
                    letter-spacing:0.5px;">
            SELECTIONNER UNE OFFRE CI-DESSUS POUR ACCEDER A LA CONFIGURATION DU PRET.
        </div>
        """, unsafe_allow_html=True)
        st.markdown('</div>', unsafe_allow_html=True)
        return

    # ── CHARGEMENT DONNÉES ─────────────────────────────────────────────────
    try:
        sh = client.open(sheet_name)
        df_clients = get_cached_data(client, sheet_name, "Clients_KYC")
        try:
            df_garants = get_cached_data(client, sheet_name, "Garants_KYC")
            if not df_garants.empty:
                df_garants['search_label'] = df_garants['ID_Garant'].astype(str) + " - " + df_garants['Nom_Complet'].astype(str)
        except Exception as e:
            st.error(f"Erreur sur la feuille Garants : {e}")
            df_garants = pd.DataFrame()
    except Exception as e:
        st.error(f"Erreur connexion : {e}")
        return

    # ── SÉLECTION CLIENT ───────────────────────────────────────────────────
    if df_clients.empty:
        st.error("Aucun client trouve dans la base KYC.")
        st.markdown('</div>', unsafe_allow_html=True)
        return

    st.markdown('<span class="le-section-tag">// IDENTIFICATION CIBLE</span>', unsafe_allow_html=True)

    df_clients['search_label'] = df_clients['ID_Client'].astype(str) + " - " + df_clients['Nom_Complet'].astype(str)
    selected_client_label = st.selectbox("Rechercher une cible", [""] + df_clients['search_label'].tolist())

    if not selected_client_label:
        st.markdown('</div>', unsafe_allow_html=True)
        return

    client_info = df_clients[df_clients['search_label'] == selected_client_label].iloc[0]
    client_id   = client_info['ID_Client']

    try:
        pers_charge = int(client_info.get('Pers_Charge', 0))
    except (ValueError, TypeError):
        pers_charge = 0

    st.info(f"CIBLE : {client_info['Nom_Complet']}")

    with st.expander(f"SOLVABILITE // {client_info['Nom_Complet']}", expanded=True):
        def clean_val(val):
            try:
                return float(str(val).replace("XOF","").replace(" ","").replace(",",""))
            except:
                return 0.0

        rev   = clean_val(client_info['Revenus_Mensuels'])
        chg   = clean_val(client_info['Charges_Estimees'])
        reste = rev - chg

        c1, c2, c3 = st.columns(3)
        c1.metric("Revenus Mensuels",   f"{int(rev):,} XOF".replace(",", " "))
        c2.metric("Charges Estimees",   f"{int(chg):,} XOF".replace(",", " "))
        delta_color = "normal" if reste > 20000 else "inverse"
        c3.metric("Reste a Vivre (Net)", f"{int(reste):,} XOF".replace(",", " "),
                  delta=f"{int(reste):,}", delta_color=delta_color)
        st.markdown(f"**Profession :** {client_info['Statut_Pro']} | **Ville :** {client_info['Ville']}")

        if pers_charge >= 1:
            ipf = rev / (1 + pers_charge)
            st.info(f"IPF (Indice de Pression Familiale) : {int(ipf):,} XOF — base de calcul ajustee ({pers_charge} pers. a charge)".replace(",", " "))

    # ── SÉLECTION GARANT ───────────────────────────────────────────────────
    selected_garant = None
    garant_id       = ""

    st.markdown('<span class="le-section-tag">// GARANT (OPTIONNEL)</span>', unsafe_allow_html=True)

    if df_garants.empty:
        st.info("Aucun garant enregistre dans la base.")
    else:
        selected_garant_label = st.selectbox("Rechercher un garant (Optionnel)", [""] + df_garants['search_label'].tolist())
        if selected_garant_label:
            garant_info     = df_garants[df_garants['search_label'] == selected_garant_label].iloc[0]
            selected_garant = garant_info
            garant_id       = garant_info['ID_Garant']
            st.info(f"GARANT : {garant_info['Nom_Complet']}")
            with st.expander(f"ANALYSE CAUTION // {garant_info['Nom_Complet']}", expanded=True):
                rev_g = clean_val(garant_info['Revenus_Mensuels'])
                chg_g = clean_val(garant_info['Charges_Estimees'])
                g1, g2, g3 = st.columns(3)
                g1.metric("Revenus Garant", f"{int(rev_g):,} XOF".replace(",", " "))
                g2.metric("Charges Garant", f"{int(chg_g):,} XOF".replace(",", " "))
                g3.metric("Reste a Vivre",  f"{int(rev_g - chg_g):,} XOF".replace(",", " "))
                st.warning("ENGAGEMENT SOLIDAIRE : Le garant renonce aux benefices de discussion et de division.")

    # ── CONFIGURATION PRÊT ────────────────────────────────────────────────
    st.markdown('<span class="le-section-tag">// CONFIGURATION DU PRET</span>', unsafe_allow_html=True)

    col_motif, col_type, col_moyen = st.columns(3)
    with col_motif:
        motif = st.selectbox("Motif du pret", [
            "Commerce / Achat de stock", "Investissement", "Tresorerie professionnelle",
            "Lancement d'activite", "Developpement d'activite", "Agriculture / Elevage",
            "Transport / Logistique", "Urgence medicale", "Scolarite / Formation",
            "Logement / Habitat", "Reparations", "Evenements familiaux",
            "Voyage / Deplacement", "Consommation", "Achat d'equipement personnel",
            "Projet personnel", "Autre"
        ])
    with col_type:
        type_pret = st.selectbox("Type de remboursement", [
            "In Fine", "Mensuel - Interets", "Mensuel - Constant", "Hebdomadaire", "Personnalise"
        ])
    with col_moyen:
        moyen_transfert = st.selectbox("Moyen de transfert", ["Wave", "Orange Money", "Virement"])

    type_code_map = {
        "In Fine": "IN_FINE", "Mensuel - Interets": "MENSUEL_INTERETS",
        "Mensuel - Constant": "MENSUEL_CONSTANT", "Hebdomadaire": "HEBDOMADAIRE",
        "Personnalise": "PERSONNALISE"
    }
    type_code = type_code_map[type_pret]

    col1, col2, col3 = st.columns(3)
    montant    = col1.number_input("Montant (XOF)", 10000, value=100000, step=10000)
    taux_hebdo = col2.number_input("Taux Hebdo (%)", 0.1, value=2.0, step=0.1)

    # ── VARIABLES INIT ────────────────────────────────────────────────────
    montant_versement = 0
    montant_total     = 0
    cout_credit       = 0
    nb_versements     = 0
    duree_semaines    = 0
    dates_versements  = []
    date_debut        = date.today()
    date_fin          = date_debut

    st.markdown('<span class="le-section-tag">// SIMULATION</span>', unsafe_allow_html=True)

    # ── IN FINE ───────────────────────────────────────────────────────────
    if type_code == "IN_FINE":
        duree_semaines  = col3.number_input("Duree (en semaines)", min_value=1, max_value=104, value=8)
        date_fin        = date_debut + timedelta(weeks=duree_semaines)
        dates_versements = [date_fin]
        montant_total   = montant * (1 + (taux_hebdo / 100) * duree_semaines)
        cout_credit     = montant_total - montant
        montant_versement = montant_total
        nb_versements   = 1
        r1, r2, r3 = st.columns(3)
        r1.metric("Versement unique",  f"{int(montant_versement):,} XOF".replace(",", " "))
        r2.metric("Cout du credit",    f"{int(cout_credit):,} XOF".replace(",", " "))
        r3.metric("Montant Total",     f"{int(montant_total):,} XOF".replace(",", " "), delta=f"+{int(cout_credit):,}")

    # ── MENSUEL INTÉRÊTS ──────────────────────────────────────────────────
    elif type_code == "MENSUEL_INTERETS":
        duree_mois       = col3.number_input("Duree (en mois)", min_value=1, max_value=60, value=12)
        date_fin         = date_debut + timedelta(days=duree_mois * 30)
        duree_semaines   = duree_mois * 4.33
        taux_mensuel     = (taux_hebdo / 100) * 4.33
        interet_mensuel  = montant * taux_mensuel
        montant_versement = interet_mensuel
        montant_final_mois = montant + interet_mensuel
        montant_total    = (interet_mensuel * duree_mois) + montant
        cout_credit      = montant_total - montant
        nb_versements    = int(duree_mois)
        # ✅ Génération des dates mensuelles
        dates_versements = [date_debut + timedelta(days=30 * i) for i in range(1, nb_versements + 1)]
        r1, r2 = st.columns(2)
        r1.metric("Interets mensuels",  f"{int(interet_mensuel):,} XOF".replace(",", " "))
        r2.metric("Dernier versement",  f"{int(montant_final_mois):,} XOF".replace(",", " "))
        r3, r4 = st.columns(2)
        r3.metric("Cout du credit",     f"{int(cout_credit):,} XOF".replace(",", " "))
        r4.metric("Montant Total",      f"{int(montant_total):,} XOF".replace(",", " "), delta=f"+{int(cout_credit):,}")

    # ── MENSUEL CONSTANT ──────────────────────────────────────────────────
    elif type_code == "MENSUEL_CONSTANT":
        duree_mois       = col3.number_input("Duree (en mois)", min_value=1, max_value=60, value=12)
        date_fin         = date_debut + timedelta(days=duree_mois * 30)
        duree_semaines   = duree_mois * 4.33
        taux_mensuel     = (taux_hebdo / 100) * 4.33
        if taux_mensuel > 0:
            mensualite = (montant * taux_mensuel) / (1 - (1 + taux_mensuel)**(-duree_mois))
        else:
            mensualite = montant / duree_mois
        montant_versement = mensualite
        montant_total    = mensualite * duree_mois
        cout_credit      = montant_total - montant
        nb_versements    = int(duree_mois)
        # ✅ Génération des dates mensuelles
        dates_versements = [date_debut + timedelta(days=30 * i) for i in range(1, nb_versements + 1)]
        r1, r2, r3 = st.columns(3)
        r1.metric("Mensualite constante", f"{int(mensualite):,} XOF".replace(",", " "))
        r2.metric("Cout du credit",       f"{int(cout_credit):,} XOF".replace(",", " "))
        r3.metric("Montant Total",        f"{int(montant_total):,} XOF".replace(",", " "), delta=f"+{int(cout_credit):,}")

    # ── HEBDOMADAIRE ──────────────────────────────────────────────────────
    elif type_code == "HEBDOMADAIRE":
        duree_semaines   = col3.number_input("Duree (en semaines)", min_value=1, max_value=104, value=12)
        date_fin         = date_debut + timedelta(weeks=duree_semaines)
        taux_hebdo_dec   = taux_hebdo / 100
        if taux_hebdo_dec > 0:
            hebdomadalite = (montant * taux_hebdo_dec) / (1 - (1 + taux_hebdo_dec)**(-duree_semaines))
        else:
            hebdomadalite = montant / duree_semaines
        montant_versement = hebdomadalite
        montant_total    = hebdomadalite * duree_semaines
        cout_credit      = montant_total - montant
        nb_versements    = int(duree_semaines)
        # ✅ Génération des dates hebdomadaires
        dates_versements = [date_debut + timedelta(weeks=i) for i in range(1, nb_versements + 1)]
        r1, r2, r3 = st.columns(3)
        r1.metric("Versement Hebdo",  f"{int(hebdomadalite):,} XOF".replace(",", " "))
        r2.metric("Cout du credit",   f"{int(cout_credit):,} XOF".replace(",", " "))
        r3.metric("Montant Total",    f"{int(montant_total):,} XOF".replace(",", " "), delta=f"+{int(cout_credit):,}")

    # ── PERSONNALISÉ ──────────────────────────────────────────────────────
    else:
        st.info("Configurez les dates de versement ci-dessous")
        if 'dates_perso' not in st.session_state:
            st.session_state.dates_perso = [date.today() + timedelta(weeks=2)]
        col_add, _ = st.columns([1, 4])
        if col_add.button("[ + AJOUTER ECHEANCE ]"):
            st.session_state.dates_perso.append(st.session_state.dates_perso[-1] + timedelta(weeks=1))
            st.rerun()
        dates_versements = []
        for idx, dt in enumerate(st.session_state.dates_perso):
            col_d, col_x = st.columns([4, 1])
            new_date = col_d.date_input(f"Echeance {idx+1}", value=dt, key=f"d_{idx}", min_value=date.today())
            dates_versements.append(new_date)
            if col_x.button("[ X ]", key=f"del_{idx}") and len(st.session_state.dates_perso) > 1:
                st.session_state.dates_perso.pop(idx)
                st.rerun()
        st.session_state.dates_perso = dates_versements
        if dates_versements:
            dates_versements.sort()
            date_fin       = dates_versements[-1]
            delta_days     = (date_fin - date_debut).days
            duree_semaines = max(1, delta_days // 7)
            montant_total  = montant * (1 + (taux_hebdo / 100) * duree_semaines)
            cout_credit    = montant_total - montant
            nb_versements  = len(dates_versements)
            montant_versement = montant_total / nb_versements
            r1, r2 = st.columns(2)
            r1.metric("Moyenne/Versement", f"{int(montant_versement):,} XOF".replace(",", " "))
            r2.metric("Duree estimee",     f"{duree_semaines} sem")
            r3, r4 = st.columns(2)
            r3.metric("Cout du credit",  f"{int(cout_credit):,} XOF".replace(",", " "))
            r4.metric("Montant Total",   f"{int(montant_total):,} XOF".replace(",", " "), delta=f"+{int(cout_credit):,}")

    # ── ANALYSE CAPACITÉ ──────────────────────────────────────────────────
    st.markdown('<span class="le-section-tag">// ANALYSE DE CAPACITE</span>', unsafe_allow_html=True)

    analyse = analyser_capacite(
        type_code, montant, taux_hebdo, duree_semaines, montant_versement, nb_versements,
        client_info['Revenus_Mensuels'], client_info.get('Charges_Estimees', 0),
        montant_total, pers_charge=pers_charge
    )
    _couleur = analyse['couleur']
    _statut  = analyse['statut']
    st.markdown(
        f"<span style='font-family:JetBrains Mono,monospace;font-size:0.8rem;"
        f"font-weight:800;color:{_couleur};letter-spacing:2px;text-transform:uppercase;'>"
        f"STATUT : {_statut}</span>",
        unsafe_allow_html=True
    )
    st.info(analyse['message'])
    with st.expander("DETAILS FINANCIERS"):
        st.markdown(analyse['details'])

    # ── TABLEAU D'AMORTISSEMENT ───────────────────────────────────────────
    st.markdown('<span class="le-section-tag">// TABLEAU D\'ECHEANCES</span>', unsafe_allow_html=True)

    date_debut = date.today()
    if type_code == "PERSONNALISE":
        df_amort = generer_tableau_amortissement(
            type_code, montant, taux_hebdo, duree_semaines,
            montant_versement, nb_versements, date_debut,
            dates_versements=dates_versements
        )
    else:
        df_amort = generer_tableau_amortissement(
            type_code, montant, taux_hebdo, duree_semaines,
            montant_versement, nb_versements, date_debut
        )
    if not df_amort.empty:
        df_amort.insert(0, "Type", type_pret)
    st.dataframe(df_amort, hide_index=True, use_container_width=True)

    # ── TAUX D'ENDETTEMENT ────────────────────────────────────────────────
    from Analytics.AnalyseFinance import calculer_taux_endettement
    taux_endettement = calculer_taux_endettement(
        type_code, montant_versement, nb_versements, duree_semaines,
        client_info['Revenus_Mensuels'], pers_charge=pers_charge
    )
    st.info(f"TAUX D'ENDETTEMENT CALCULE : {taux_endettement:.2f}%")

    # ── VALIDATION ────────────────────────────────────────────────────────
    st.markdown('<span class="le-section-tag">// VALIDATION & OCTROI</span>', unsafe_allow_html=True)

    with st.form("valid_pret"):
        submit = st.form_submit_button("[ OCTROYER & GENERER DOCS ]")

    if submit:
        ws_prets     = sh.worksheet("Prets_Master")
        current_year = datetime.now().year
        all_rows     = ws_prets.get_all_values()

        if len(all_rows) <= 1:
            next_number = 1
        else:
            existing_numbers = []
            for row in all_rows[1:]:
                if row[0].startswith(f"PRT-{current_year}-"):
                    try:
                        existing_numbers.append(int(row[0].split('-')[-1]))
                    except:
                        continue
            next_number = max(existing_numbers) + 1 if existing_numbers else 1

        new_id = f"PRT-{current_year}-{next_number:04d}"

        # Ordre strict colonnes Prets_Master (avec Offre en position 4)
        row_data = [
            new_id,                                              # ID_Pret
            client_id,                                           # ID_Client
            client_info['Nom_Complet'],                          # Nom_Complet
            offre_selectionnee,                                  # Offre ✅
            type_code,                                           # Type_Pret
            motif,                                               # Motif
            montant,                                             # Montant_Capital
            taux_hebdo,                                          # Taux_Hebdo
            round(taux_endettement, 2),                          # Taux_Endettement
            duree_semaines,                                      # Duree_Semaines
            round(montant_versement),                            # Montant_Versement
            round(montant_total),                                # Montant_Total
            round(cout_credit),                                  # Cout_Credit
            nb_versements,                                       # Nb_Versements
            ";".join([d.strftime("%d/%m/%Y") for d in dates_versements]) if dates_versements else "",  # Dates_Versements
            date_debut.strftime("%d/%m/%Y"),                     # Date_Deblocage
            date_fin.strftime("%d/%m/%Y"),                       # Date_Fin
            moyen_transfert,                                     # Moyen_Transfert
            "ACTIF",                                             # Statut
            garant_id,                                           # ID_Garant
            datetime.now().strftime("%d-%m-%Y %H:%M:%S"),        # Date_Creation
            datetime.now().strftime("%d-%m-%Y %H:%M:%S"),        # Date_Update
        ]
        ws_prets.append_row(row_data)
        time.sleep(1)

        st.session_state.loan_validated = True
        st.session_state.loan_id        = new_id
        st.session_state.loan_data      = {
            "ID_Pret":          new_id,
            "Offre":            offre_selectionnee,
            "Montant_Capital":  montant,
            "Montant_Total":    montant_total,
            "Taux_Hebdo":       taux_hebdo,
            "Taux_Endettement": taux_endettement,
            "Duree_Semaines":   duree_semaines,
            "Motif":            motif,
            "Date_Deblocage":   date_debut.strftime("%d/%m/%Y"),
            "Date_Fin":         date_fin.strftime("%d/%m/%Y"),
        }
        st.session_state.client_data = client_info.to_dict()
        st.session_state.garant_data = selected_garant.to_dict() if selected_garant is not None else None
        st.session_state.df_amort    = df_amort.copy()

        st.success(f"PRET {new_id} ENREGISTRE AVEC SUCCES — OFFRE : {offre_selectionnee}")

    # ── DOCUMENTS PDF ─────────────────────────────────────────────────────
    if st.session_state.get('loan_validated', False):
        st.markdown('<span class="le-section-tag">// DOCUMENTS</span>', unsafe_allow_html=True)
        st.markdown(f"PRET **{st.session_state.loan_id}** — OFFRE : **{st.session_state.loan_data.get('Offre', '')}**")

        loan_data       = st.session_state.loan_data
        client_data     = st.session_state.client_data
        garant_data     = st.session_state.garant_data
        df_amort_saved  = st.session_state.df_amort

        col_pdf1, col_pdf2, col_pdf3, col_reset = st.columns(4)

        with col_pdf1:
            pdf_contrat = generer_contrat_pret(loan_data, client_data, df_amort_saved)
            st.download_button(
                "Contrat de Pret", pdf_contrat,
                f"Contrat_{st.session_state.loan_id}.pdf", "application/pdf",
                use_container_width=True
            )
        with col_pdf2:
            pdf_dette = generer_reconnaissance_dette(loan_data, client_data)
            st.download_button(
                "Reconnaissance de Dette", pdf_dette,
                f"Dette_{st.session_state.loan_id}.pdf", "application/pdf",
                use_container_width=True
            )
        with col_pdf3:
            if garant_data is not None:
                pdf_caution = generer_contrat_caution(loan_data, garant_data)
                st.download_button(
                    "Contrat de Caution", pdf_caution,
                    f"Caution_{st.session_state.loan_id}.pdf", "application/pdf",
                    use_container_width=True
                )
            else:
                st.info("Pas de garant")
        with col_reset:
            if st.button("[ NOUVEAU PRET ]", use_container_width=True):
                st.session_state.loan_validated = False
                st.session_state.selected_offre = None
                for k in ['loan_id', 'loan_data', 'client_data', 'garant_data', 'df_amort']:
                    st.session_state.pop(k, None)
                st.rerun()

    st.markdown('</div>', unsafe_allow_html=True)