"""Normalise a free-form `create_excel` spec so that almost anything the model sends renders."""

from __future__ import annotations

import re
from typing import Any

from openpyxl.utils import get_column_letter
from openpyxl.utils.cell import coordinate_from_string

from app.tools.coerce import cell

TYPE_ALIASES = {
    "money": "currency", "cad": "currency", "$": "currency", "dollar": "currency", "dollars": "currency",
    "pct": "percent", "%": "percent", "percentage": "percent", "pourcentage": "percent",
    "int": "integer", "entier": "integer", "float": "number", "decimal": "number", "num": "number",
    "string": "text", "str": "text", "texte": "text", "date": "text", "m2": "area", "m²": "area",
    "superficie": "area", "ratio": "factor", "facteur": "factor",
}


def _type(t: Any) -> str:
    s = str(t or "text").strip().lower()
    return TYPE_ALIASES.get(s, s if s in {"currency", "percent", "number", "integer", "area", "factor", "text"} else "text")


def _guess_type(header: str, values: list[Any]) -> str:
    h = header.lower()
    if any(k in h for k in ("$", "prix", "coût", "cout", "montant", "valeur", "loyer", "revenu", "dépense")):
        return "currency"
    if "%" in h or "taux" in h or "pourcent" in h:
        return "percent"
    if any(k in h for k in ("m²", "m2", "pi²", "superficie")):
        return "area"
    if any(k in h for k in ("année", "annee", "an", "n°", "no", "rang", "période", "periode")) and all(
            isinstance(v, (int, float)) and float(v).is_integer() for v in values if v not in (None, "")):
        return "integer"
    if values and all(isinstance(v, (int, float)) for v in values if v not in (None, "")):
        return "number"
    return "text"


def _columns(raw: Any, rows: list[list[Any]]) -> list[dict[str, Any]]:
    cols: list[dict[str, Any]] = []
    if isinstance(raw, list):
        for c in raw:
            if isinstance(c, dict):
                cols.append({"header": str(c.get("header") or c.get("name") or c.get("label") or ""),
                             "type": _type(c.get("type") or c.get("format"))})
            else:
                cols.append({"header": str(c), "type": None})
    width = max([len(cols)] + [len(r) for r in rows]) if (cols or rows) else 0
    while len(cols) < width:
        cols.append({"header": f"Col {len(cols) + 1}", "type": None})
    for j, c in enumerate(cols):
        if c["type"] is None:
            c["type"] = _guess_type(c["header"], [r[j] for r in rows if j < len(r)])
    return cols


def _rows(raw: Any, columns_raw: Any) -> list[list[Any]]:
    rows: list[list[Any]] = []
    headers = []
    if isinstance(columns_raw, list):
        headers = [str(c.get("header") or c.get("name") or c.get("label") or "") if isinstance(c, dict)
                   else str(c) for c in columns_raw]
    for r in raw or []:
        if isinstance(r, dict):
            if headers:
                keys = {k.lower(): k for k in r}
                row = [cell(r.get(h) if h in r else r.get(keys.get(h.lower(), ""), "")) for h in headers]
                # keep extra keys not in headers
                for k, v in r.items():
                    if k not in headers and k.lower() not in {h.lower() for h in headers}:
                        row.append(cell(v))
            else:
                row = [cell(v) for v in r.values()]
        elif isinstance(r, (list, tuple)):
            row = [cell(v) for v in r]
        else:
            row = [cell(r)]
        rows.append(row)
    return rows


def _valid_anchor(a: Any) -> str | None:
    if not isinstance(a, str) or not re.fullmatch(r"[A-Za-z]{1,3}\d{1,6}", a.strip()):
        return None
    return a.strip().upper()


def normalise_spec(spec: dict[str, Any]) -> dict[str, Any]:
    out: dict[str, Any] = {"filename": spec.get("filename"), "style": "uqo",
                           "objective": spec.get("objective") or spec.get("objectif") or spec.get("title"),
                           "hypotheses": spec.get("hypotheses") or spec.get("hypothèses") or [], "sheets": []}
    sheets = spec.get("sheets") or spec.get("feuilles") or []
    if not sheets and (spec.get("tables") or spec.get("rows") or spec.get("columns")):
        sheets = [spec]
    for si, sh in enumerate(sheets):
        if not isinstance(sh, dict):
            continue
        tables_raw = sh.get("tables") or sh.get("tableaux") or []
        if not tables_raw and (sh.get("rows") or sh.get("columns") or sh.get("data")):
            tables_raw = [{"columns": sh.get("columns"), "rows": sh.get("rows") or sh.get("data"),
                           "totals": sh.get("totals")}]
        inputs = []
        for inp in sh.get("inputs") or sh.get("hypotheses") or []:
            if not isinstance(inp, dict):
                continue
            c = _valid_anchor(inp.get("cell"))
            if not c:
                continue
            v = inp.get("value")
            if isinstance(v, str) and v.startswith("="):
                pass
            else:
                v = cell(v)
            inputs.append({"cell": c, "label": str(inp.get("label", "")), "value": v,
                           "format": _type(inp.get("format") or inp.get("type") or "number"),
                           "name": inp.get("name")})
        next_row = 4
        if inputs:
            next_row = max(coordinate_from_string(i["cell"])[1] for i in inputs) + 2
        tables = []
        for t in tables_raw:
            if not isinstance(t, dict):
                continue
            rows = _rows(t.get("rows") or t.get("data") or t.get("lignes"), t.get("columns") or t.get("colonnes"))
            cols = _columns(t.get("columns") or t.get("colonnes"), rows)
            anchor = _valid_anchor(t.get("anchor")) or f"A{next_row}"
            totals = t.get("totals") or t.get("total")
            if isinstance(totals, dict):
                totals = {"label": str(totals.get("label", "Total")), "formula": totals.get("formula") or totals.get("value"),
                          "format": _type(totals.get("format")) if totals.get("format") else None, "name": totals.get("name")}
                if totals["format"] is None:
                    totals.pop("format")
            else:
                totals = None
            tables.append({"anchor": anchor, "columns": cols, "rows": rows, "totals": totals,
                           "row_formats": t.get("row_formats") or {}, "bold_rows": t.get("bold_rows") or [],
                           "first_col_format": t.get("first_col_format")})
            _, r0 = coordinate_from_string(anchor)
            next_row = max(next_row, r0 + 1 + len(rows) + (1 if totals else 0) + 2)
        charts = []
        for ch in sh.get("charts") or sh.get("graphiques") or []:
            if isinstance(ch, dict):
                charts.append({"type": str(ch.get("type", "bar")).lower(), "title": str(ch.get("title", "")),
                               "categories_range": ch.get("categories_range") or ch.get("categories"),
                               "values_range": ch.get("values_range") or ch.get("data_range") or ch.get("values"),
                               "anchor": _valid_anchor(ch.get("anchor")) or f"{get_column_letter(8)}4"})
        notes = [str(n) for n in (sh.get("notes") or []) if n]
        out["sheets"].append({"name": str(sh.get("name") or sh.get("nom") or f"Feuille {si + 1}"),
                              "title": str(sh.get("title") or sh.get("titre") or sh.get("name") or ""),
                              "inputs_title": sh.get("inputs_title"), "inputs": inputs, "tables": tables,
                              "charts": charts, "notes": notes})
    return out