import io

import pytest
from openpyxl import load_workbook

from app.tools import excel_ops
from app.tools.appraisal_calc import FUNCS
from app.tools.create_docx import build as build_docx
from app.tools.create_excel import build_workbook
from app.tools.excel_templates import TEMPLATES
from app.tools.make_chart import render
from app.tools.unit_convert import convert


def _workbook() -> bytes:
    return build_workbook(TEMPLATES["comparables_ajustes"]({}))[0]


def test_inspect_and_preview() -> None:
    data = _workbook()
    info = excel_ops.inspect(data)
    assert any(s["name"] == "Comparables" for s in info["sheets"])
    assert any("=" in line for s in info["sheets"] for line in s["cells"])
    pv = excel_ops.preview(data)
    assert pv["sheet"] == "Comparables" and len(pv["all"]) >= 2


def test_edit_operations_add_column_row_sheet_chart() -> None:
    data = _workbook()
    hr = excel_ops._header_row(load_workbook(io.BytesIO(data))["Comparables"])
    new, log = excel_ops.apply_operations(data, [
        {"op": "add_column", "sheet": "Comparables", "header": "Piscine ($)", "formula": "=B{r}*0.02", "format": "currency"},
        {"op": "set_cell", "sheet": "Comparables", "cell": "B4", "value": "1 250 pi²", "input": True},
        {"op": "add_sheet", "name": "Synthèse", "title": "Synthèse", "table": {"columns": ["Poste", "Montant ($)"],
                                                                                 "rows": [["Terrain", "120 000 $"], ["Bâtiment", 300000]],
                                                                                 "totals": {"label": "Total", "formula": "=B5+B6"}}},
        {"op": "add_chart", "sheet": "Synthèse", "type": "pie", "categories_range": "A5:A6", "values_range": "B5:B6", "anchor": "E4"},
        {"op": "add_note", "sheet": "Comparables", "cell": "A1", "text": "Grille révisée"},
        {"op": "format", "sheet": "Comparables", "range": "B4:B4", "format": "area", "bold": True},
    ])
    wb = load_workbook(io.BytesIO(new))
    ws = wb["Comparables"]
    headers = [c.value for c in ws[hr]]
    assert "Piscine ($)" in headers
    col = headers.index("Piscine ($)") + 1
    assert ws.cell(row=hr + 1, column=col).value == f"=B{hr + 1}*0.02"
    assert ws["B4"].value == 1250 and ws["A1"].comment is not None
    assert "Synthèse" in wb.sheetnames and wb["Synthèse"]["B7"].value == "=B5+B6"
    assert len(log) == 6


def test_edit_unknown_op_raises() -> None:
    with pytest.raises(excel_ops.ExcelOpError):
        excel_ops.apply_operations(_workbook(), [{"op": "explode"}])


def test_appraisal_functions() -> None:
    ca = FUNCS["cost_approach"]({"land_value": "120 000 $", "cost_new": 322000, "effective_age": 15, "economic_life": 60})
    assert round(ca["value"]) == 361500
    bd = FUNCS["breakdown_depreciation"]({
        "cost_new": 500000,
        "curable_physical": [{"item": "peinture", "cost_to_cure": 8000}],
        "short_lived": [{"item": "toiture", "cost_new": 30000, "effective_age": 10, "life": 20}],
        "long_lived": {"effective_age": 15, "economic_life": 60},
        "functional": [{"item": "cuisine", "type": "déficience", "cost_to_cure": 25000, "cost_if_new": 15000}],
        "external": {"rent_loss_annual": 2400, "cap_rate": "8 %", "building_share": 0.8},
    })
    assert bd["components"]["curable_physical"] == 8000 and bd["components"]["short_lived"] == 15000
    assert round(bd["components"]["long_lived"]) == round((500000 - 8000 - 30000) * 15 / 60)
    assert bd["components"]["functional"] == 10000 and round(bd["components"]["external"]) == 24000
    grid = FUNCS["adjust_comparables"]({
        "subject": {"superficie": 1200, "garage": "Oui", "etat": "bon"},
        "rates": {"superficie": 120, "garage": 15000, "etat": 10000},
        "monthly_trend": "0,5 %",
        "comparables": [{"address": "A", "price": 425000, "months": 6, "superficie": 1150, "garage": "Oui", "etat": "bon"},
                        {"address": "B", "price": 398000, "months": 8, "superficie": 1250, "garage": "Non", "etat": "moyen"}],
    })
    a = grid["comparables"][0]
    assert round(a["time_adjusted"]) == round(425000 * 1.03) and a["adjustments"]["superficie"] == 6000
    b = grid["comparables"][1]
    assert b["adjustments"]["garage"] == 15000 and b["adjustments"]["etat"] == 10000
    assert grid["stats"]["least_adjusted"] == "A"
    dc = FUNCS["direct_capitalization"]({"potential_gross_income": 100000, "vacancy_pct": 5, "operating_expenses": 40000, "cap_rate": 7})
    assert round(dc["value"]) == round(55000 / 0.07)


def test_unit_convert() -> None:
    assert abs(convert(100, "m2", "pi2")["result"] - 1076.39) < 0.01
    assert abs(convert(2450, "$/m2", "$/pi2")["result"] - 227.61) < 0.01
    assert abs(convert(1, "arpent", "m2")["result"] - 3418.894) < 0.01
    with pytest.raises(ValueError):
        convert(1, "m2", "pi")


def test_make_chart_and_docx() -> None:
    png = render({"type": "bar", "title": "Prix ajustés", "x": ["A", "B", "C"],
                  "series": [{"name": "Prix", "values": [425000, "398 000 $", 449000]}], "value_format": "currency",
                  "annotate": True})
    assert png[:8] == b"\x89PNG\r\n\x1a\n" and len(png) > 5000
    docx = build_docx({"title": "Fiche — Dépréciation", "course": "IMM1033", "subtitle": "",
                       "markdown": "# Définitions\n\nLa **dépréciation** est $D = \\frac{A_e}{DVE} \\times C_N$.\n\n"
                                   "- physique\n- fonctionnelle\n\n| Type | Exemple |\n|---|---|\n| Physique | toiture |\n\n"
                                   "1. étape un\n2. étape deux\n\n> Rappel : le terrain ne se déprécie pas."})
    assert docx[:2] == b"PK" and len(docx) > 10000