"""Template: adjusted comparables grid (sales comparison) with live formulas.

Two ways to feed it (both tolerant to strings like "425 000 $", "+3 %", "Oui", "bon") :

A) Characteristics + unit rates (preferred — adjustments are computed by Excel formulas):
   sujet: {adresse, superficie: 1200, terrain: 6000, garage: "Oui", etat: "bon", ...}
   taux:  {superficie: 120, terrain: 8, garage: 15000, etat: 10000}   # $ per unit / per step
   echelles: {etat: ["mauvais", "moyen", "bon", "très bon"]}            # ordinal scales (optional)
   taux_temps_mensuel: 0.005                                             # market trend (optional)
   comparables: [{adresse, prix, date, mois: 3, superficie: 1150, terrain: 5500, garage: "Oui",
                  etat: "bon"}, ...]
   → ajustement = (sujet − comparable) × taux, in the workbook, cell by cell.

B) Direct dollar adjustments (no `taux`): comparables: [{adresse, prix, temps: "+2 %",
   ajustements: {Superficie: -5000, Garage: -12000}, caracteristiques: {Garage: "Oui"}}]
"""

from __future__ import annotations

import unicodedata
from typing import Any

from openpyxl.utils import get_column_letter as L

from app.tools.coerce import boolish, cell, num, pct

META = {"adresse", "nom", "comparable", "prix", "prix_vente", "date", "date_vente", "temps",
        "ajustement_temps_pct", "date_pct", "ajust_temps", "mois", "ajustements",
        "caracteristiques", "notes", "note", "source", "filename"}
QUALITY_WORDS = ["très mauvais", "mauvais", "passable", "moyen", "bon", "très bon", "excellent", "neuf"]

DEFAULT = {
    "sujet": {"adresse": "Sujet — bungalow fictif, Gatineau", "superficie": 1200, "terrain": 6000,
              "garage": "Oui", "etat": "bon"},
    "taux": {"superficie": 120, "terrain": 8, "garage": 15000, "etat": 10000},
    "taux_temps_mensuel": 0.005,
    "comparables": [
        {"adresse": "45 rue Laurier", "prix": 425000, "date": "2026-03", "mois": 6, "superficie": 1150,
         "terrain": 5500, "garage": "Oui", "etat": "bon"},
        {"adresse": "12 rue Front", "prix": 398000, "date": "2026-01", "mois": 8, "superficie": 1250,
         "terrain": 7000, "garage": "Non", "etat": "moyen"},
        {"adresse": "88 boul. Saint-Joseph", "prix": 449000, "date": "2026-04", "mois": 5,
         "superficie": 1300, "terrain": 6200, "garage": "Oui", "etat": "très bon"},
    ],
}


def _norm(s: str) -> str:
    s = unicodedata.normalize("NFD", str(s)).encode("ascii", "ignore").decode().lower().strip()
    return s.replace(" ", "_").replace("-", "_")


def _label(key: str) -> str:
    return key.replace("_", " ").strip().capitalize()


def _rank(value: Any, scale: list[str] | None) -> float | None:
    """Ordinal text → rank (0-based). Booleans → 1/0. Numbers pass through."""
    n = num(value, None)
    if n is not None and not isinstance(value, bool):
        return n
    b = boolish(value)
    if b is not None and not (isinstance(value, str) and scale and _norm(value) in [_norm(x) for x in scale]):
        return 1.0 if b else 0.0
    if isinstance(value, str):
        words = [_norm(x) for x in (scale or QUALITY_WORDS)]
        v = _norm(value)
        if v in words:
            return float(words.index(v))
        for i, w in enumerate(words):  # "bon état" → "bon"
            if w and w in v:
                return float(i)
    return None


def _time_pct(c: dict[str, Any]) -> float | None:
    for k in ("temps", "ajustement_temps_pct", "date_pct", "ajust_temps"):
        if k in c:
            return pct(c[k], None)
    return None


def build(p: dict[str, Any]) -> dict[str, Any]:
    if not p.get("comparables"):
        p = {**DEFAULT, **{k: v for k, v in p.items() if k != "comparables"}} if p.get("taux") is None else {**DEFAULT, **p}
    comps_raw = [c if isinstance(c, dict) else {"adresse": str(c)} for c in (p.get("comparables") or [])]
    taux_raw = {_norm(k): v for k, v in (p.get("taux") or p.get("unit_rates") or p.get("taux_unitaires") or {}).items()}
    scales = {_norm(k): list(v) for k, v in (p.get("echelles") or p.get("scales") or {}).items() if isinstance(v, list)}
    sujet_raw = p.get("sujet") or {}
    sujet: dict[str, Any] = {_norm(k): v for k, v in sujet_raw.items()} if isinstance(sujet_raw, dict) else {}
    sujet_label = (sujet_raw.get("adresse") or sujet_raw.get("nom") if isinstance(sujet_raw, dict)
                   else str(sujet_raw or "Sujet — immeuble fictif, Gatineau")) or "Sujet"
    monthly = pct(p.get("taux_temps_mensuel", p.get("taux_temps_pct_mensuel")), None)
    mode_rates = bool(taux_raw)

    # ---- characteristics (mode A) or adjustments (mode B)
    keys: list[str] = []
    for c in comps_raw:
        for k, v in c.items():
            nk = _norm(k)
            if nk in META or isinstance(v, (dict, list)):
                continue
            if nk not in keys:
                keys.append(nk)
        for k in (c.get("ajustements") or {}) if isinstance(c.get("ajustements"), dict) else {}:
            if _norm(k) not in keys:
                keys.append(_norm(k))
    for k in taux_raw:
        if k not in keys and k in sujet:
            keys.append(k)
    if mode_rates:
        keys = [k for k in keys if k in taux_raw]  # only characteristics with a rate are adjusted
    text_keys: list[str] = []  # qualitative columns kept as text (mode B or no rank)

    n = len(comps_raw)
    sheet: dict[str, Any] = {"name": "Comparables", "title": f"Grille de comparables ajustés — {sujet_label}",
                             "inputs": [], "tables": [], "charts": [], "notes": []}
    row = 4
    inputs_map: dict[str, str] = {}  # key → cell of subject value / rate

    if mode_rates:
        sheet["inputs_title"] = "Hypothèses (cellules bleues modifiables)"
        # subject values in column B, rates in column D (labels in A and C)
        r = row
        for k in keys:
            sv = _rank(sujet.get(k), scales.get(k))
            if sv is None:
                sv = 0.0
            sheet["inputs"].append({"cell": f"B{r}", "label": f"Sujet — {_label(k)}"
                                    + (f" ({'/'.join(scales[k])})" if k in scales else ""),
                                    "value": sv, "format": "number"})
            sheet["inputs"].append({"cell": f"D{r}", "label": f"Taux — {_label(k)} ($/unité ou $/cran)",
                                    "value": num(taux_raw.get(k), 0.0) or 0.0, "format": "currency"})
            inputs_map[k] = f"$B${r}"
            inputs_map[k + "__taux"] = f"$D${r}"
            r += 1
        if monthly is not None:
            sheet["inputs"].append({"cell": f"D{r}", "label": "Tendance du marché (%/mois)", "value": monthly,
                                    "format": "percent"})
            inputs_map["__monthly"] = f"$D${r}"
            r += 1
        row = r + 2

    # ---- main grid
    header_row = row
    first = header_row + 1
    last = first + n - 1
    columns: list[dict[str, Any]] = [{"header": "Comparable", "type": "text"}, {"header": "Prix de vente ($)", "type": "currency"},
                                     {"header": "Date de vente", "type": "text"}]
    col = 4  # D
    if mode_rates and monthly is not None:
        columns += [{"header": "Mois écoulés", "type": "number"}, {"header": "Ajust. temps (%)", "type": "percent"}]
        c_mois, c_temps = col, col + 1
        col += 2
    else:
        columns += [{"header": "Ajust. temps (%)", "type": "percent"}]
        c_mois, c_temps = None, col
        col += 1
    columns.append({"header": "Prix ajusté temps ($)", "type": "currency"})
    c_pat = col
    col += 1
    c_char: dict[str, int] = {}
    c_adj: dict[str, int] = {}
    if mode_rates:
        for k in keys:
            columns.append({"header": f"{_label(k)} (comp.)", "type": "number"})
            c_char[k] = col
            col += 1
        for k in keys:
            columns.append({"header": f"Ajust. {_label(k)} ($)", "type": "currency"})
            c_adj[k] = col
            col += 1
    else:
        for k in keys:
            columns.append({"header": f"{_label(k)} ($)", "type": "currency"})
            c_adj[k] = col
            col += 1
    c_total, c_final, c_gross, c_gross_pct = col, col + 1, col + 2, col + 3
    columns += [{"header": "Total ajustements ($)", "type": "currency"}, {"header": "Prix ajusté ($)", "type": "currency"},
                {"header": "Ajust. bruts ($)", "type": "currency"}, {"header": "Ajust. bruts (%)", "type": "percent"}]

    rows: list[list[Any]] = []
    chars_text: list[list[Any]] = []
    for i, c in enumerate(comps_raw):
        cn = {_norm(k): v for k, v in c.items()}
        r = first + i
        name = str(cn.get("adresse") or cn.get("nom") or cn.get("comparable") or f"Comparable {i + 1}")
        line: list[Any] = [name, num(cn.get("prix", cn.get("prix_vente")), 0.0) or 0.0,
                           str(cn.get("date") or cn.get("date_vente") or "—")]
        tp = _time_pct(cn)
        if c_mois is not None:
            line.append(num(cn.get("mois"), 0.0) or 0.0)
            line.append(f"={L(c_mois)}{r}*{inputs_map['__monthly']}" if tp is None else tp)
        else:
            line.append(tp if tp is not None else 0.0)
        line.append(f"=B{r}*(1+{L(c_temps)}{r})")
        adj_vals = {_norm(k): v for k, v in (cn.get("ajustements") or {}).items()} if isinstance(cn.get("ajustements"), dict) else {}
        text_row: list[Any] = [name]
        if mode_rates:
            for k in keys:
                rank = _rank(cn.get(k), scales.get(k))
                line.append(rank if rank is not None else 0.0)
            for k in keys:
                line.append(f"=({inputs_map[k]}-{L(c_char[k])}{r})*{inputs_map[k + '__taux']}")
            for k, v in cn.items():
                if k not in META and k not in keys and not isinstance(v, (dict, list)):
                    if k not in text_keys:
                        text_keys.append(k)
        else:
            for k in keys:
                v = adj_vals.get(k, cn.get(k))
                nv = num(v, None)
                if nv is None:
                    line.append(0.0)
                    if v not in (None, ""):
                        if k not in text_keys:
                            text_keys.append(k)
                elif isinstance(v, str) and v.strip().endswith("%"):
                    line.append(f"={L(c_pat)}{r}*{nv}")
                else:
                    line.append(nv)
        chars_text.append(text_row)
        adj_range = f"{L(min(c_adj.values()))}{r}:{L(max(c_adj.values()))}{r}" if c_adj else None
        line.append(f"=SUM({adj_range})" if adj_range else 0)
        line.append(f"={L(c_pat)}{r}+{L(c_total)}{r}")
        line.append(f"=SUMPRODUCT(ABS({adj_range}))" if adj_range else 0)
        line.append(f"=IF({L(c_pat)}{r}=0,0,{L(c_gross)}{r}/{L(c_pat)}{r})")
        rows.append(line)
    sheet["tables"].append({"anchor": f"A{header_row}", "columns": columns, "rows": rows})

    # ---- statistics
    F, G = L(c_final), L(c_gross_pct)
    s0 = last + 3
    sheet["tables"].append({
        "anchor": f"A{s0}",
        "columns": [{"header": "Statistique", "type": "text"}, {"header": "Valeur", "type": "currency"}],
        "rows": [
            ["Minimum des prix ajustés", f"=MIN({F}{first}:{F}{last})"],
            ["Maximum des prix ajustés", f"=MAX({F}{first}:{F}{last})"],
            ["Moyenne simple (indicatif)", f"=AVERAGE({F}{first}:{F}{last})"],
            ["Médiane", f"=MEDIAN({F}{first}:{F}{last})"],
            ["Comparable le moins ajusté (rang)", f"=MATCH(MIN({G}{first}:{G}{last}),{G}{first}:{G}{last},0)"],
            ["Prix ajusté du comparable le moins ajusté", f"=INDEX({F}{first}:{F}{last},B{s0 + 5})"],
            ["Pondération réconciliée (poids inverses des ajust. bruts)",
             f"=SUMPRODUCT({F}{first}:{F}{last},1/(1+{G}{first}:{G}{last}))/SUMPRODUCT(1/(1+{G}{first}:{G}{last}))"],
        ],
        "row_formats": {4: "integer"},
    })
    next_row = s0 + 10

    # ---- qualitative characteristics kept as text
    if text_keys:
        sheet["tables"].append({
            "anchor": f"A{next_row}",
            "columns": [{"header": "Comparable", "type": "text"}] + [{"header": _label(k), "type": "text"} for k in text_keys],
            "rows": [[str(c.get("adresse") or c.get("nom") or f"Comparable {i + 1}")]
                     + [cell({_norm(k2): v for k2, v in c.items()}.get(k, "—")) for k in text_keys]
                     for i, c in enumerate(comps_raw)],
        })
    if mode_rates and scales:
        sheet["notes"].append("Échelles ordinales : " + " ; ".join(f"{_label(k)} = {' < '.join(v)} (rang 0, 1, 2…)" for k, v in scales.items()))
    sheet["notes"] += [
        "Ajustement = (caractéristique du sujet − celle du comparable) × taux : le comparable est meilleur → ajustement négatif."
        if mode_rates else "On ajuste le comparable vers le sujet : le comparable est meilleur → ajustement négatif.",
        "Ordre des ajustements : conditions de vente, financement, marché (temps), puis caractéristiques physiques.",
        "La réconciliation pondère les indications (plus de poids au comparable le moins ajusté) ; la moyenne simple n'est qu'indicative.",
        "Repères du cours : ajustements bruts ≤ 25 % et nets ≤ 15 % pour un comparable fiable.",
    ]
    return {"filename": p.get("filename", "comparables_ajustes.xlsx"), "style": "uqo",
            "objective": f"Grille de comparables ajustés — {sujet_label}", "sheets": [sheet]}