"""
Router FastAPI - Module Analyse MAESTRO
"""
from fastapi import APIRouter, Depends
from sqlalchemy.orm import Session
from sqlalchemy import func, extract
from app.database import get_db
from app.models.projet import Projet, Piece
from app.models.client import Client
from app.models.tache_planning import TachePlanning
from datetime import datetime
from typing import Optional

router = APIRouter(prefix="/api/analyse", tags=["analyse"])

def _yr(annee): return annee or datetime.now().year

@router.get("/annees")
def annees_disponibles(db: Session = Depends(get_db)):
    rows = db.query(extract('year', Projet.date_creation).label("annee")).distinct().order_by("annee").all()
    annees = [int(r[0]) for r in rows if r[0]]
    if not annees: annees = [datetime.now().year]
    return {"annees": annees}

@router.get("/overview")
def overview(annee: Optional[int] = None, db: Session = Depends(get_db)):
    yr = _yr(annee)
    pieces_annee = db.query(Piece).join(Projet).filter(extract('year', Projet.date_creation) == yr).all()
    ca_total = sum((p.prix_total_ht or 0) for p in pieces_annee)
    vol_beton = sum((p.volume_total or 0) for p in pieces_annee)
    nb_projets = db.query(Projet).filter(extract('year', Projet.date_creation) == yr).count()
    nb_pieces_fab = db.query(Piece).filter(Piece.statut_realisation == 'fabrique', extract('year', Piece.date_fabrication_reelle) == yr).count()
    taches = db.query(TachePlanning).filter(TachePlanning.date_debut.like(f"{yr}%")).all()
    heures_prod = len(taches) * 8
    ratio_beton_heure = round(vol_beton / heures_prod, 3) if heures_prod else 0
    clients_annee = db.query(Projet.client_id).filter(extract('year', Projet.date_creation) == yr, Projet.client_id.isnot(None)).distinct().count()
    ca_mensuel = []
    for mois in range(1, 13):
        pieces_m = db.query(Piece).join(Projet).filter(extract('year', Projet.date_creation) == yr, extract('month', Projet.date_creation) == mois).all()
        ca_m = sum((p.prix_total_ht or 0) for p in pieces_m)
        nb_m = sum(p.quantite for p in pieces_m)
        ca_mensuel.append({"mois": mois, "ca": round(ca_m, 2), "nb_pieces": nb_m})
    return {"annee": yr, "ca_total": round(ca_total, 2), "vol_beton_m3": round(vol_beton, 2),
            "nb_projets": nb_projets, "nb_pieces_fabriquees": nb_pieces_fab,
            "heures_production": heures_prod, "ratio_beton_heure": ratio_beton_heure,
            "nb_clients_actifs": clients_annee, "ca_mensuel": ca_mensuel}

@router.get("/rentabilite")
def rentabilite(annee: Optional[int] = None, db: Session = Depends(get_db)):
    from app.models.transport import TarifTransport, ParametreCout
    yr = _yr(annee)
    params = {p.cle: p.valeur for p in db.query(ParametreCout).all()}

    projets = db.query(Projet).filter(
        extract('year', Projet.date_creation) == yr
    ).all()

    result_projets = []
    ca_par_client: dict = {}

    for p in projets:
        pieces = db.query(Piece).filter(Piece.projet_id == p.id).all()

        # Calculs pièces
        ca = sum((pc.prix_total_ht or 0) for pc in pieces)
        vol_budget = sum((pc.volume_total or 0) for pc in pieces)
        vol_reel = sum((pc.vol_beton_reel or pc.volume_total or 0) for pc in pieces)
        vol = vol_reel
        nb_pieces = sum(pc.quantite for pc in pieces)
        h_budget = sum((pc.heures_budgetees or 0) for pc in pieces)
        h_reelles = sum((pc.heures_reelles or 0) for pc in pieces if pc.heures_reelles_verrouillee == 1)

        # Coût transport
        cp = p.code_postal_chantier or ""
        dept = cp[:2].zfill(2) if cp else None
        tarif = db.query(TarifTransport).filter(TarifTransport.departement == dept).first() if dept else None
        nb_convoi = sum(1 for pc in pieces if pc.convoi_exceptionnel)
        nb_normal = max(0, (p.nb_transports or 0) - nb_convoi)
        cout_transport = 0
        if tarif:
            cout_transport = round(
                nb_normal * tarif.prix_rotation +
                nb_convoi * tarif.prix_rotation * tarif.majoration_convoi, 2
            )

        # Coût acier budget
        prix_cfa_kg = params.get("prix_cfa", 1.20)
        cout_acier = 0.0
        for pc in pieces:
            # CFA
            if pc.poids_cfa and pc.poids_cfa > 0:
                cout_acier += pc.poids_cfa * prix_cfa_kg
            # Treillis : nb_panneaux * prix_panneau * (1 + taux_perte)
            if pc.config_treillis:
                try:
                    import json as _json
                    ct = _json.loads(pc.config_treillis) if isinstance(pc.config_treillis, str) else pc.config_treillis
                    for nappe in (ct.get("nappes") or []):
                        art_id = nappe.get("article_id")
                        nb = nappe.get("nb_panneaux", 0) or 0
                        if art_id and nb > 0:
                            from app.models.article import Article
                            art = db.query(Article).filter(Article.id == int(art_id)).first()
                            if art and art.prix_achat_ht and art.prix_achat_ht > 0:
                                perte = art.taux_perte or 0
                                cout_acier += nb * art.prix_achat_ht * (1 + perte / 100)
                except Exception:
                    pass
            # Abouts de voile (config_inserts)
            if pc.config_inserts:
                try:
                    ci = _json.loads(pc.config_inserts) if isinstance(pc.config_inserts, str) else pc.config_inserts
                    art_id = ci.get("article_id")
                    qml = ci.get("quantite_ml", 0) or 0
                    if art_id and qml > 0:
                        from app.models.article import Article as _Article
                        art = db.query(_Article).filter(_Article.id == int(art_id)).first()
                        if art and art.prix_achat_ht and art.prix_achat_ht > 0:
                            lu = art.longueur_unitaire or 100
                            import math as _math
                            nb = _math.ceil(qml * 100 / lu)
                            cout_acier += nb * art.prix_achat_ht * pc.quantite
                except Exception:
                    pass

        # Coût accessoires budget (becquets + boîtes d'attentes)
        cout_accessoires = 0.0
        for pc in pieces:
            import math as _math2
            import json as _json2
            # Becquets
            if pc.becquet_type:
                try:
                    from app.models.article import Article as _ArtB
                    art = db.query(_ArtB).filter(_ArtB.id == int(pc.becquet_type)).first()
                    if art and art.prix_achat_ht and art.prix_achat_ht > 0:
                        lu = art.longueur_unitaire or 1
                        bl = pc.becquet_longueur or 0
                        nb = _math2.ceil(bl / lu) if lu > 0 else 0
                        cout_accessoires += nb * art.prix_achat_ht * pc.quantite
                except Exception:
                    pass
            # Boîtes d'attentes (config_start)
            if pc.config_start:
                try:
                    cs = _json2.loads(pc.config_start) if isinstance(pc.config_start, str) else pc.config_start
                    art_id = cs.get("article_id")
                    nb_boites = cs.get("nb_boites", 0) or 0
                    if art_id and nb_boites > 0:
                        from app.models.article import Article as _ArtS
                        art = db.query(_ArtS).filter(_ArtS.id == int(art_id)).first()
                        if art and art.prix_achat_ht and art.prix_achat_ht > 0:
                            cout_accessoires += nb_boites * art.prix_achat_ht * pc.quantite
                except Exception:
                    pass

        # Client
        client = db.query(Client).filter(Client.id == p.client_id).first() if p.client_id else None
        client_nom = client.raison_sociale if client else "Sans client"
        ca_par_client[client_nom] = ca_par_client.get(client_nom, 0) + ca

        result_projets.append({
            "id": p.id,
            "numero_affaire": p.numero_affaire,
            "nom": p.nom,
            "client": client_nom,
            "couleur": p.couleur,
            "statut": p.statut,
            "ca": round(ca, 2),
            "vol_beton": round(vol_reel, 2),
            "vol_beton_budget": round(vol_budget, 2),
            "nb_pieces": nb_pieces,
            "h_budget": round(h_budget, 2),
            "h_reelles": round(h_reelles, 2),
            "cout_transport": cout_transport,
            "nb_transports": p.nb_transports or 0,
            "departement": dept,
            "cout_acier": round(cout_acier, 2),
            "cout_accessoires": round(cout_accessoires, 2),
        })

    clients_ca = [
        {"client": k, "ca": round(v, 2)}
        for k, v in sorted(ca_par_client.items(), key=lambda x: x[1], reverse=True)
    ]

    return {"annee": yr, "projets": result_projets, "clients_ca": clients_ca}

@router.get("/beton")
def beton(annee: Optional[int] = None, db: Session = Depends(get_db)):
    from app.models.transport import ParametreCout
    yr = _yr(annee)
    # Charger les prix béton depuis parametres_cout
    params = {p.cle: p.valeur for p in db.query(ParametreCout).all()}
    def prix_beton(type_beton, accelerateur=None):
        if not type_beton: base = params.get("prix_beton_XF1", 90)
        else:
            cle = "prix_beton_" + type_beton.upper().replace(" ", "_").replace("/", "_")
            base = params.get(cle, params.get("prix_beton_XF1", 90))
        if accelerateur:
            cle_acc = "prix_beton_" + accelerateur.upper().replace(" ", "_").replace("%", "").replace(".", "_")
            base += params.get(cle_acc, 0)
        return base

    mensuel = []
    for mois in range(1, 13):
        pieces = db.query(Piece).join(Projet).filter(extract('year', Projet.date_creation) == yr, extract('month', Projet.date_creation) == mois).all()
        vol = sum((p.vol_beton_reel or p.volume_total or 0) for p in pieces)
        cout = sum((p.vol_beton_reel or p.volume_total or 0) * prix_beton(p.type_beton, getattr(p, "accelerateur_beton", None)) for p in pieces)
        taches = db.query(TachePlanning).filter(TachePlanning.date_debut.like(f"{yr}-{str(mois).zfill(2)}%")).all()
        heures = len(taches) * 8
        mensuel.append({"mois": mois, "vol_m3": round(vol, 2), "cout_eur": round(cout, 2), "heures": heures, "ratio_m3_h": round(vol / heures, 3) if heures else 0})
    types_beton = db.query(Piece.type_beton, func.sum(Piece.volume_total).label("vol")).join(Projet).filter(extract('year', Projet.date_creation) == yr).group_by(Piece.type_beton).all()
    return {"annee": yr, "mensuel": mensuel, "types_beton": [{"type": t[0] or "Non défini", "vol": round(float(t[1] or 0), 2)} for t in types_beton]}

@router.get("/volets")
def volets(annee: Optional[int] = None, client_id: Optional[int] = None, db: Session = Depends(get_db)):
    from app.models.transport import ParametreCout
    yr = _yr(annee)
    params = {p.cle: p.valeur for p in db.query(ParametreCout).all()}
    def prix_beton(type_beton, accelerateur=None):
        if not type_beton: base = params.get("prix_beton_XF1", 90)
        else:
            cle = "prix_beton_" + type_beton.upper().replace(" ", "_").replace("/", "_")
            base = params.get(cle, params.get("prix_beton_XF1", 90))
        if accelerateur:
            cle_acc = "prix_beton_" + accelerateur.upper().replace(" ", "_").replace("%", "").replace(".", "_")
            base += params.get(cle_acc, 0)
        return base
    query = db.query(Piece).join(Projet).filter(extract('year', Projet.date_creation) == yr)
    if client_id: query = query.filter(Projet.client_id == client_id)
    pieces = query.all()
    types_data: dict = {}
    for p in pieces:
        tp = p.type_piece or "Autre"
        if tp not in types_data:
            types_data[tp] = {"type": tp, "quantite": 0, "m2": 0.0, "ml": 0.0, "vol_m3": 0.0, "ca": 0.0, "cout_beton": 0.0, "_delais": []}
        d = types_data[tp]
        d["quantite"] += p.quantite
        d["m2"] += (p.longueur * p.largeur / 10000) * p.quantite
        d["ml"] += (p.longueur / 100) * p.quantite
        d["vol_m3"] += (p.volume_total or 0)
        d["ca"] += (p.prix_total_ht or 0)
        d["cout_beton"] += (p.vol_beton_reel or p.volume_total or 0) * prix_beton(p.type_beton, getattr(p, "accelerateur_beton", None))
        if p.date_fabrication_reelle and p.date_creation:
            d["_delais"].append((p.date_fabrication_reelle - p.date_creation).days)
    result_types = []
    for tp, d in types_data.items():
        delais = d.pop("_delais")
        marge = d["ca"] - d["cout_beton"]
        d["delai_moy_jours"] = round(sum(delais) / len(delais), 1) if delais else None
        d["marge"] = round(marge, 2)
        d["taux_marge"] = round((marge / d["ca"] * 100) if d["ca"] else 0, 1)
        d["ca"] = round(d["ca"], 2); d["cout_beton"] = round(d["cout_beton"], 2)
        d["m2"] = round(d["m2"], 1); d["ml"] = round(d["ml"], 1); d["vol_m3"] = round(d["vol_m3"], 2)
        result_types.append(d)
    result_types.sort(key=lambda x: x["ca"], reverse=True)
    clients = db.query(Client).filter(Client.actif == 1).order_by(Client.raison_sociale).all()
    return {"annee": yr, "types_pieces": result_types, "clients": [{"id": c.id, "nom": c.raison_sociale} for c in clients]}

@router.get("/saisonnalite")
def saisonnalite(db: Session = Depends(get_db)):
    yr = datetime.now().year
    annees = [yr - 2, yr - 1, yr]
    data = {}
    for y in annees:
        mensuel = []
        for mois in range(1, 13):
            pieces = db.query(Piece).join(Projet).filter(extract('year', Projet.date_creation) == y, extract('month', Projet.date_creation) == mois).all()
            mensuel.append({"mois": mois, "vol_m3": round(sum((p.volume_total or 0) for p in pieces), 2), "nb_pieces": sum(p.quantite for p in pieces)})
        data[str(y)] = mensuel
    return {"annees": annees, "data": data}