import io

import pytest
from openpyxl import load_workbook

from app.tools.create_excel import build_workbook
from app.tools.excel_templates import TEMPLATES


@pytest.mark.parametrize("name", list(TEMPLATES))
def test_templates_build_and_reload(name: str) -> None:
    spec = TEMPLATES[name]({})
    data, summaries = build_workbook(spec)
    wb = load_workbook(io.BytesIO(data))
    assert wb.sheetnames[0] == "Lisez-moi"
    assert len(wb.sheetnames) >= 2
    assert summaries and all(s["rows"] > 2 for s in summaries)
    ws = wb.worksheets[1]
    formulas = [c.value for row in ws.iter_rows() for c in row
                if isinstance(c.value, str) and c.value.startswith("=")]
    assert formulas, "templates must contain live formulas"


def test_methode_du_cout_formulas_reference_existing_cells() -> None:
    data, _ = build_workbook(TEMPLATES["methode_du_cout"]({}))
    ws = load_workbook(io.BytesIO(data))["Méthode du coût"]
    assert ws["B17"].value == "=B4*B5"
    assert ws["A28"].value.startswith("VALEUR INDIQUÉE")
    assert ws["B28"].value == "=B25+B26+B27"
    names = set(load_workbook(io.BytesIO(data)).defined_names.keys())
    assert {"Valeur_Terrain", "Valeur_Indiquee", "DVE"} <= names


def test_free_spec() -> None:
    spec = {"filename": "t.xlsx", "sheets": [{"name": "Test", "title": "T",
            "inputs": [{"cell": "B4", "label": "x", "value": 2, "format": "number"}],
            "tables": [{"anchor": "A6", "columns": [{"header": "a", "type": "text"},
                                                    {"header": "b", "type": "currency"}],
                        "rows": [["un", "=B4*10"], ["deux", 5]],
                        "totals": {"label": "T", "formula": "=B7+B8"}}]}]}
    data, _ = build_workbook(spec)
    ws = load_workbook(io.BytesIO(data))["Test"]
    assert ws["B7"].value == "=B4*10" and ws["B9"].value == "=B7+B8"