spb/uqo-chat

1"""Normalise a free-form `create_excel` spec so that almost anything the model sends renders."""23from __future__ import annotations45import re6from typing import Any78from openpyxl.utils import get_column_letter9from openpyxl.utils.cell import coordinate_from_string1011from app.tools.coerce import cell1213TYPE_ALIASES = {14    "money": "currency", "cad": "currency", "$": "currency", "dollar": "currency", "dollars": "currency",15    "pct": "percent", "%": "percent", "percentage": "percent", "pourcentage": "percent",16    "int": "integer", "entier": "integer", "float": "number", "decimal": "number", "num": "number",17    "string": "text", "str": "text", "texte": "text", "date": "text", "m2": "area", "m²": "area",18    "superficie": "area", "ratio": "factor", "facteur": "factor",19}202122def _type(t: Any) -> str:23    s = str(t or "text").strip().lower()24    return TYPE_ALIASES.get(s, s if s in {"currency", "percent", "number", "integer", "area", "factor", "text"} else "text")252627def _guess_type(header: str, values: list[Any]) -> str:28    h = header.lower()29    if any(k in h for k in ("$", "prix", "coût", "cout", "montant", "valeur", "loyer", "revenu", "dépense")):30        return "currency"31    if "%" in h or "taux" in h or "pourcent" in h:32        return "percent"33    if any(k in h for k in ("m²", "m2", "pi²", "superficie")):34        return "area"35    if any(k in h for k in ("année", "annee", "an", "n°", "no", "rang", "période", "periode")) and all(36            isinstance(v, (int, float)) and float(v).is_integer() for v in values if v not in (None, "")):37        return "integer"38    if values and all(isinstance(v, (int, float)) for v in values if v not in (None, "")):39        return "number"40    return "text"414243def _columns(raw: Any, rows: list[list[Any]]) -> list[dict[str, Any]]:44    cols: list[dict[str, Any]] = []45    if isinstance(raw, list):46        for c in raw:47            if isinstance(c, dict):48                cols.append({"header": str(c.get("header") or c.get("name") or c.get("label") or ""),49                             "type": _type(c.get("type") or c.get("format"))})50            else:51                cols.append({"header": str(c), "type": None})52    width = max([len(cols)] + [len(r) for r in rows]) if (cols or rows) else 053    while len(cols) < width:54        cols.append({"header": f"Col {len(cols) + 1}", "type": None})55    for j, c in enumerate(cols):56        if c["type"] is None:57            c["type"] = _guess_type(c["header"], [r[j] for r in rows if j < len(r)])58    return cols596061def _rows(raw: Any, columns_raw: Any) -> list[list[Any]]:62    rows: list[list[Any]] = []63    headers = []64    if isinstance(columns_raw, list):65        headers = [str(c.get("header") or c.get("name") or c.get("label") or "") if isinstance(c, dict)66                   else str(c) for c in columns_raw]67    for r in raw or []:68        if isinstance(r, dict):69            if headers:70                keys = {k.lower(): k for k in r}71                row = [cell(r.get(h) if h in r else r.get(keys.get(h.lower(), ""), "")) for h in headers]72                # keep extra keys not in headers73                for k, v in r.items():74                    if k not in headers and k.lower() not in {h.lower() for h in headers}:75                        row.append(cell(v))76            else:77                row = [cell(v) for v in r.values()]78        elif isinstance(r, (list, tuple)):79            row = [cell(v) for v in r]80        else:81            row = [cell(r)]82        rows.append(row)83    return rows848586def _valid_anchor(a: Any) -> str | None:87    if not isinstance(a, str) or not re.fullmatch(r"[A-Za-z]{1,3}\d{1,6}", a.strip()):88        return None89    return a.strip().upper()909192def normalise_spec(spec: dict[str, Any]) -> dict[str, Any]:93    out: dict[str, Any] = {"filename": spec.get("filename"), "style": "uqo",94                           "objective": spec.get("objective") or spec.get("objectif") or spec.get("title"),95                           "hypotheses": spec.get("hypotheses") or spec.get("hypothèses") or [], "sheets": []}96    sheets = spec.get("sheets") or spec.get("feuilles") or []97    if not sheets and (spec.get("tables") or spec.get("rows") or spec.get("columns")):98        sheets = [spec]99    for si, sh in enumerate(sheets):100        if not isinstance(sh, dict):101            continue102        tables_raw = sh.get("tables") or sh.get("tableaux") or []103        if not tables_raw and (sh.get("rows") or sh.get("columns") or sh.get("data")):104            tables_raw = [{"columns": sh.get("columns"), "rows": sh.get("rows") or sh.get("data"),105                           "totals": sh.get("totals")}]106        inputs = []107        for inp in sh.get("inputs") or sh.get("hypotheses") or []:108            if not isinstance(inp, dict):109                continue110            c = _valid_anchor(inp.get("cell"))111            if not c:112                continue113            v = inp.get("value")114            if isinstance(v, str) and v.startswith("="):115                pass116            else:117                v = cell(v)118            inputs.append({"cell": c, "label": str(inp.get("label", "")), "value": v,119                           "format": _type(inp.get("format") or inp.get("type") or "number"),120                           "name": inp.get("name")})121        next_row = 4122        if inputs:123            next_row = max(coordinate_from_string(i["cell"])[1] for i in inputs) + 2124        tables = []125        for t in tables_raw:126            if not isinstance(t, dict):127                continue128            rows = _rows(t.get("rows") or t.get("data") or t.get("lignes"), t.get("columns") or t.get("colonnes"))129            cols = _columns(t.get("columns") or t.get("colonnes"), rows)130            anchor = _valid_anchor(t.get("anchor")) or f"A{next_row}"131            totals = t.get("totals") or t.get("total")132            if isinstance(totals, dict):133                totals = {"label": str(totals.get("label", "Total")), "formula": totals.get("formula") or totals.get("value"),134                          "format": _type(totals.get("format")) if totals.get("format") else None, "name": totals.get("name")}135                if totals["format"] is None:136                    totals.pop("format")137            else:138                totals = None139            tables.append({"anchor": anchor, "columns": cols, "rows": rows, "totals": totals,140                           "row_formats": t.get("row_formats") or {}, "bold_rows": t.get("bold_rows") or [],141                           "first_col_format": t.get("first_col_format")})142            _, r0 = coordinate_from_string(anchor)143            next_row = max(next_row, r0 + 1 + len(rows) + (1 if totals else 0) + 2)144        charts = []145        for ch in sh.get("charts") or sh.get("graphiques") or []:146            if isinstance(ch, dict):147                charts.append({"type": str(ch.get("type", "bar")).lower(), "title": str(ch.get("title", "")),148                               "categories_range": ch.get("categories_range") or ch.get("categories"),149                               "values_range": ch.get("values_range") or ch.get("data_range") or ch.get("values"),150                               "anchor": _valid_anchor(ch.get("anchor")) or f"{get_column_letter(8)}4"})151        notes = [str(n) for n in (sh.get("notes") or []) if n]152        out["sheets"].append({"name": str(sh.get("name") or sh.get("nom") or f"Feuille {si + 1}"),153                              "title": str(sh.get("title") or sh.get("titre") or sh.get("name") or ""),154                              "inputs_title": sh.get("inputs_title"), "inputs": inputs, "tables": tables,155                              "charts": charts, "notes": notes})156    return out157