"""Template: two-way sensitivity of cost-approach value (unit cost × effective age)."""

from __future__ import annotations

from typing import Any

from app.tools.coerce import num, pct


def _f(p: dict[str, Any], key: str, default: float) -> float:
    """Tolerant numeric param; keys ending in `_pct` / starting with `taux` are ratios."""
    raw = p.get(key)
    v = pct(raw, None) if (key.endswith("_pct") or key.startswith("taux")) else num(raw, None)
    return float(default) if v is None else v


def build(p: dict[str, Any]) -> dict[str, Any]:
    superficie = _f(p, "superficie_m2", 210)
    base_cu = _f(p, "cout_unitaire_m2", 2450)
    dve = _f(p, "duree_vie_economique", 60)
    terrain = _f(p, "valeur_terrain", 185000)
    ages = p.get("ages", [5, 10, 15, 20, 25])
    variations = p.get("variations", [-0.10, -0.05, 0.0, 0.05, 0.10])
    inputs = [
        {"cell": "B4", "label": "Superficie (m²)", "value": superficie, "format": "area"},
        {"cell": "B5", "label": "Coût unitaire de base ($/m²)", "value": base_cu, "format": "currency"},
        {"cell": "B6", "label": "Durée de vie économique (ans)", "value": dve, "format": "number"},
        {"cell": "B7", "label": "Valeur du terrain ($)", "value": terrain, "format": "currency"},
    ]
    header = [{"header": "Variation coût ↓ / Âge effectif →", "type": "text"}] + [
        {"header": f"{a} ans", "type": "currency"} for a in ages]
    rows = []
    for i, v in enumerate(variations):
        r = 11 + i
        row: list[Any] = [float(v)]
        for j, a in enumerate(ages):
            col = chr(ord("B") + j)
            row.append(f"=$B$7+$B$4*$B$5*(1+$A{r})*(1-{a}/$B$6)")
            _ = col
        rows.append(row)
    table = {"anchor": "A10", "columns": header, "rows": rows, "first_col_format": "percent"}
    return {
        "filename": p.get("filename", "analyse_sensibilite.xlsx"),
        "style": "uqo",
        "sheets": [{
            "name": "Sensibilité",
            "title": "Analyse de sensibilité — valeur par la méthode du coût",
            "inputs": inputs,
            "tables": [table],
            "notes": ["Valeur = V_T + S × c_u × (1 + variation) × (1 − âge / DVE).",
                      "Lire l'écart entre les cases extrêmes : il mesure le risque d'estimation."],
        }],
    }