"""Template: loan amortisation schedule."""

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]:
    principal = _f(p, "capital", 350000)
    rate = _f(p, "taux_annuel", 0.055)
    years = int(_f(p, "amortissement_ans", 25))
    ppy = int(_f(p, "versements_par_an", 12))
    n_show = min(years * ppy, 360)
    inputs = [
        {"cell": "B4", "label": "Capital emprunté ($)", "value": principal, "format": "currency",
         "name": "Capital"},
        {"cell": "B5", "label": "Taux nominal annuel", "value": rate, "format": "percent",
         "name": "Taux"},
        {"cell": "B6", "label": "Amortissement (ans)", "value": years, "format": "integer"},
        {"cell": "B7", "label": "Versements par an", "value": ppy, "format": "integer"},
        {"cell": "B8", "label": "Taux périodique", "value": "=B5/B7", "format": "percent"},
        {"cell": "B9", "label": "Nombre de versements", "value": "=B6*B7", "format": "integer"},
        {"cell": "B10", "label": "Versement périodique ($)", "value": "=-PMT(B8,B9,B4)",
         "format": "currency", "name": "Versement"},
    ]
    rows = []
    for k in range(1, n_show + 1):
        r = 13 + k
        prev = "B4" if k == 1 else f"F{r - 1}"
        rows.append([k, f"={prev}", "=$B$10", f"=B{r}*$B$8", f"=C{r}-D{r}", f"=B{r}-E{r}"])
    table = {
        "anchor": "A13",
        "columns": [{"header": "Période", "type": "integer"}, {"header": "Solde début ($)", "type": "currency"},
                    {"header": "Versement ($)", "type": "currency"}, {"header": "Intérêts ($)", "type": "currency"},
                    {"header": "Capital ($)", "type": "currency"}, {"header": "Solde fin ($)", "type": "currency"}],
        "rows": rows,
    }
    return {
        "filename": p.get("filename", "tableau_amortissement.xlsx"),
        "style": "uqo",
        "sheets": [{
            "name": "Amortissement",
            "title": "Tableau d'amortissement d'un prêt hypothécaire",
            "inputs": inputs,
            "tables": [table],
            "charts": [{"type": "line", "title": "Solde du prêt", "categories_range": f"A14:A{13 + n_show}",
                        "values_range": f"F14:F{13 + n_show}", "anchor": "H13"}],
            "notes": ["Versement = Capital × FRC = Capital × i / (1 − (1+i)^-n).",
                      "Le taux périodique est le taux nominal divisé par le nombre de versements par an (capitalisation simple)."],
        }],
    }