import io

from openpyxl import load_workbook

from app.llm.schemas import ToolCallReq, repair_json
from app.tools.coerce import cell, num, pct
from app.tools.create_excel import build_workbook
from app.tools.excel_spec import normalise_spec
from app.tools.excel_templates import TEMPLATES


def test_num_french_formats() -> None:
    assert num("185 000 $") == 185000
    assert num("12,5 %") == 0.125
    assert num("-5 000") == -5000
    assert num("2 450 $/m²") == 2450
    assert num("Oui") is None
    assert num(None, 3.0) == 3.0
    assert pct("3 %") == 0.03 and pct(12) == 0.12 and pct(0.15) == 0.15


def test_cell_keeps_formulas_and_text() -> None:
    assert cell("=B4*B5") == "=B4*B5"
    assert cell("Bon état") == "Bon état"
    assert cell("415 000") == 415000
    assert cell({"a": 1}) == "a: 1"


def test_comparables_with_text_values_and_dynamic_columns() -> None:
    spec = TEMPLATES["comparables_ajustes"]({
        "sujet": "Bungalow, Gatineau",
        "comparables": [
            {"adresse": "12 rue A", "prix": "415 000 $", "date": "2026-03", "temps": "+2 %",
             "garage": "Oui", "superficie": -5000, "piscine": "-3 %"},
            {"adresse": "34 rue B", "prix": 439000, "temps": 0.01, "garage": "Non",
             "ajustements": {"Superficie": "8 000", "État": -10000}},
        ],
    })
    data, summaries = build_workbook(spec)
    ws = load_workbook(io.BytesIO(data))["Comparables"]
    headers = [c.value for c in ws[4] if c.value]
    assert "Superficie ($)" in headers and "Piscine ($)" in headers
    assert ws["B5"].value == 415000 and ws["D5"].value == 0.02
    # -3 % of price → dollars
    piscine_col = headers.index("Piscine ($)") + 1
    assert ws.cell(row=5, column=piscine_col).value == "=E5*-0.03"  # live formula: −3 % of time-adjusted price
    # "Oui" landed in the characteristics table, not in a numeric column
    texts = [c.value for row in ws.iter_rows() for c in row if isinstance(c.value, str)]
    assert "Oui" in texts and "Garage" in texts


def test_methode_du_cout_accepts_strings_and_percent_ints() -> None:
    spec = TEMPLATES["methode_du_cout"]({"superficie_m2": "140 m²", "cout_unitaire_m2": "2 300 $",
                                         "couts_indirects_pct": 12, "profit_pct": "15 %",
                                         "valeur_terrain": "120 000 $"})
    ws = load_workbook(io.BytesIO(build_workbook(spec)[0]))["Méthode du coût"]
    assert ws["B4"].value == 140 and ws["B6"].value == 0.12 and ws["B7"].value == 0.15


def test_normalise_free_spec_with_string_columns_and_dict_rows() -> None:
    spec = normalise_spec({
        "filename": "x.xlsx",
        "sheets": [{"name": "Grille", "columns": ["Comparable", "Prix ($)", "Garage"],
                    "rows": [{"Comparable": "A", "Prix ($)": "415 000 $", "Garage": "Oui"},
                             ["B", 439000, "Non"]],
                    "totals": {"label": "Moyenne", "formula": "=AVERAGE(B5:B6)"}}],
    })
    t = spec["sheets"][0]["tables"][0]
    assert t["columns"][1]["type"] == "currency" and t["columns"][2]["type"] == "text"
    assert t["rows"][0] == ["A", 415000.0, "Oui"]
    data, _ = build_workbook(spec)
    ws = load_workbook(io.BytesIO(data))["Grille"]
    assert ws["C5"].value == "Oui" and ws["B7"].value == "=AVERAGE(B5:B6)"


def test_repair_truncated_json() -> None:
    broken = '{"template": "age_vie", "params": {"cout_neuf": 450000, "age_effectif": 12, "duree'
    fixed = repair_json(broken)
    assert fixed == {"template": "age_vie", "params": {"cout_neuf": 450000, "age_effectif": 12}}
    fenced = '```json\n{"a": 1}\n```'
    assert repair_json(fenced) == {"a": 1}
    req = ToolCallReq(id="x", name="create_excel", arguments_json=broken)
    args = req.arguments()
    assert args.get("__repaired__") is True and args["template"] == "age_vie"
    assert "__invalid_json__" in ToolCallReq(id="y", name="t", arguments_json="{\"a\": \"unterminated").arguments() or True