import os
import shutil
import subprocess
from pathlib import Path

import pytest
from openpyxl import load_workbook

from finance.exporting import build_formula_workbook
from tests.test_formula_export_reliability import custom_column

pytestmark = pytest.mark.django_db


def test_formula_workbook_recalculates_with_libreoffice(workspace, tmp_path):
    executable = os.environ.get("SOFFICE_BIN") or shutil.which("soffice")
    if not executable:
        pytest.skip("set SOFFICE_BIN to run the LibreOffice recalculation check")

    ds, member, *_ = workspace
    source = tmp_path / "source"
    output = tmp_path / "recalculated"
    source.mkdir()
    output.mkdir()
    workbook_path = source / "formula.xlsx"
    with workbook_path.open("wb") as workbook_file:
        build_formula_workbook(
            ds,
            member,
            {
                "start": "2026-08",
                "end": "2026-08",
                "comparison": "yoy",
                "group": "department",
                "custom_columns": [custom_column()],
            },
            workbook_file,
        )

    profile = tmp_path / "lo-profile"
    result = subprocess.run(
        [
            executable,
            "--headless",
            f"-env:UserInstallation={profile.as_uri()}",
            "--convert-to",
            "xlsx",
            "--outdir",
            str(output),
            str(workbook_path),
        ],
        capture_output=True,
        text=True,
        timeout=120,
        check=False,
    )
    assert result.returncode == 0, result.stderr or result.stdout
    recalculated = output / workbook_path.name
    assert recalculated.is_file(), result.stderr or result.stdout

    formulas = load_workbook(recalculated, data_only=False)
    values = load_workbook(recalculated, data_only=True)
    summary_formulas = formulas["分析汇总"]
    summary_values = values["分析汇总"]
    row = next(
        row
        for row in range(6, summary_formulas.max_row + 1)
        if summary_formulas.cell(row, 1).value == "收入成本差"
    )
    assert summary_formulas.cell(row, 3).value.startswith("=")
    assert summary_values.cell(row, 3).value == pytest.approx(60)
    assert summary_values.cell(row, 4).value == pytest.approx(90)
    assert summary_values.cell(row, 5).value == pytest.approx(-30)
    assert summary_values.cell(row, 6).value == pytest.approx(-1 / 3)
    errors = {
        cell.value
        for sheet in values
        for row_cells in sheet.iter_rows()
        for cell in row_cells
        if cell.data_type == "e"
    }
    assert errors <= {"#N/A"}


def _recalculate(workbook_path, output, profile, executable):
    result = subprocess.run(
        [
            executable,
            "--headless",
            f"-env:UserInstallation={profile.as_uri()}",
            "--convert-to",
            "xlsx",
            "--outdir",
            str(output),
            str(workbook_path),
        ],
        capture_output=True,
        text=True,
        timeout=120,
        check=False,
    )
    assert result.returncode == 0, result.stderr or result.stdout
    recalculated = output / Path(workbook_path).name
    assert recalculated.is_file(), result.stderr or result.stdout
    return load_workbook(recalculated, data_only=True)


def test_custom_column_recalculates_after_detail_amount_edit(workspace, tmp_path):
    executable = os.environ.get("SOFFICE_BIN") or shutil.which("soffice")
    if not executable:
        pytest.skip("set SOFFICE_BIN to run the LibreOffice recalculation check")
    ds, member, *_ = workspace
    source, output = tmp_path / "source", tmp_path / "recalculated"
    source.mkdir()
    output.mkdir()
    workbook_path = source / "edited-input.xlsx"
    with workbook_path.open("wb") as workbook_file:
        build_formula_workbook(
            ds,
            member,
            {
                "start": "2026-08",
                "end": "2026-08",
                "comparison": "yoy",
                "group": "department",
                "custom_columns": [custom_column()],
            },
            workbook_file,
        )
    edited = load_workbook(workbook_path)
    edited["计算明细"]["E4"] = 140
    edited.save(workbook_path)
    values = _recalculate(workbook_path, output, tmp_path / "edit-profile", executable)
    summary = values["分析汇总"]
    row = next(
        row
        for row in range(6, summary.max_row + 1)
        if summary.cell(row, 1).value == "收入成本差" and summary.cell(row, 2).value == "儿科"
    )
    assert summary.cell(row, 3).value == pytest.approx(120)
    assert summary.cell(row, 5).value == pytest.approx(30)


def test_roi_zero_cost_and_missing_baseline_return_excel_na(workspace, tmp_path):
    executable = os.environ.get("SOFFICE_BIN") or shutil.which("soffice")
    if not executable:
        pytest.skip("set SOFFICE_BIN to run the LibreOffice recalculation check")
    ds, member, *_ = workspace
    ds.facts.filter(period="2026-08").update(cost=0)
    source, output = tmp_path / "source", tmp_path / "recalculated"
    source.mkdir()
    output.mkdir()
    workbook_path = source / "roi-edges.xlsx"
    with workbook_path.open("wb") as workbook_file:
        build_formula_workbook(
            ds,
            member,
            {
                "start": "2026-08",
                "end": "2026-08",
                "comparison": "custom",
                "baseline_end": "2024-08",
                "group": "department",
            },
            workbook_file,
        )
    values = _recalculate(workbook_path, output, tmp_path / "roi-profile", executable)
    summary = values["分析汇总"]
    roi_row = next(
        row
        for row in range(6, summary.max_row + 1)
        if summary.cell(row, 1).value == "ROI" and summary.cell(row, 2).value == "儿科"
    )
    assert summary.cell(roi_row, 3).value == "#N/A"
    assert summary.cell(roi_row, 4).value == "#N/A"
    errors = {
        cell.value
        for sheet in values
        for row_cells in sheet.iter_rows()
        for cell in row_cells
        if cell.data_type == "e"
    }
    assert errors <= {"#N/A"}