spb/uqo-chat

1import io23from openpyxl import load_workbook45from app.llm.schemas import ToolCallReq, repair_json6from app.tools.coerce import cell, num, pct7from app.tools.create_excel import build_workbook8from app.tools.excel_spec import normalise_spec9from app.tools.excel_templates import TEMPLATES101112def test_num_french_formats() -> None:13    assert num("185 000 $") == 18500014    assert num("12,5 %") == 0.12515    assert num("-5 000") == -500016    assert num("2 450 $/m²") == 245017    assert num("Oui") is None18    assert num(None, 3.0) == 3.019    assert pct("3 %") == 0.03 and pct(12) == 0.12 and pct(0.15) == 0.15202122def test_cell_keeps_formulas_and_text() -> None:23    assert cell("=B4*B5") == "=B4*B5"24    assert cell("Bon état") == "Bon état"25    assert cell("415 000") == 41500026    assert cell({"a": 1}) == "a: 1"272829def test_comparables_with_text_values_and_dynamic_columns() -> None:30    spec = TEMPLATES["comparables_ajustes"]({31        "sujet": "Bungalow, Gatineau",32        "comparables": [33            {"adresse": "12 rue A", "prix": "415 000 $", "date": "2026-03", "temps": "+2 %",34             "garage": "Oui", "superficie": -5000, "piscine": "-3 %"},35            {"adresse": "34 rue B", "prix": 439000, "temps": 0.01, "garage": "Non",36             "ajustements": {"Superficie": "8 000", "État": -10000}},37        ],38    })39    data, summaries = build_workbook(spec)40    ws = load_workbook(io.BytesIO(data))["Comparables"]41    headers = [c.value for c in ws[4] if c.value]42    assert "Superficie ($)" in headers and "Piscine ($)" in headers43    assert ws["B5"].value == 415000 and ws["D5"].value == 0.0244    # -3 % of price → dollars45    piscine_col = headers.index("Piscine ($)") + 146    assert ws.cell(row=5, column=piscine_col).value == "=E5*-0.03"  # live formula: −3 % of time-adjusted price47    # "Oui" landed in the characteristics table, not in a numeric column48    texts = [c.value for row in ws.iter_rows() for c in row if isinstance(c.value, str)]49    assert "Oui" in texts and "Garage" in texts505152def test_methode_du_cout_accepts_strings_and_percent_ints() -> None:53    spec = TEMPLATES["methode_du_cout"]({"superficie_m2": "140 m²", "cout_unitaire_m2": "2 300 $",54                                         "couts_indirects_pct": 12, "profit_pct": "15 %",55                                         "valeur_terrain": "120 000 $"})56    ws = load_workbook(io.BytesIO(build_workbook(spec)[0]))["Méthode du coût"]57    assert ws["B4"].value == 140 and ws["B6"].value == 0.12 and ws["B7"].value == 0.15585960def test_normalise_free_spec_with_string_columns_and_dict_rows() -> None:61    spec = normalise_spec({62        "filename": "x.xlsx",63        "sheets": [{"name": "Grille", "columns": ["Comparable", "Prix ($)", "Garage"],64                    "rows": [{"Comparable": "A", "Prix ($)": "415 000 $", "Garage": "Oui"},65                             ["B", 439000, "Non"]],66                    "totals": {"label": "Moyenne", "formula": "=AVERAGE(B5:B6)"}}],67    })68    t = spec["sheets"][0]["tables"][0]69    assert t["columns"][1]["type"] == "currency" and t["columns"][2]["type"] == "text"70    assert t["rows"][0] == ["A", 415000.0, "Oui"]71    data, _ = build_workbook(spec)72    ws = load_workbook(io.BytesIO(data))["Grille"]73    assert ws["C5"].value == "Oui" and ws["B7"].value == "=AVERAGE(B5:B6)"747576def test_repair_truncated_json() -> None:77    broken = '{"template": "age_vie", "params": {"cout_neuf": 450000, "age_effectif": 12, "duree'78    fixed = repair_json(broken)79    assert fixed == {"template": "age_vie", "params": {"cout_neuf": 450000, "age_effectif": 12}}80    fenced = '```json\n{"a": 1}\n```'81    assert repair_json(fenced) == {"a": 1}82    req = ToolCallReq(id="x", name="create_excel", arguments_json=broken)83    args = req.arguments()84    assert args.get("__repaired__") is True and args["template"] == "age_vie"85    assert "__invalid_json__" in ToolCallReq(id="y", name="t", arguments_json="{\"a\": \"unterminated").arguments() or True86