from __future__ import annotations

from pathlib import Path
from typing import Any, Iterable

from .export import export_calculation_book_xlsx
from .project_model import DEFAULT_FREQUENCIES, ProjectData, get_frequency


DEFAULT_FREQ_P = DEFAULT_FREQUENCIES


# @hook B6 20260804
# Manages multi-frequency case comparison tables and Excel export from the shared project model.
def ensure_case_frequencies(
    project: ProjectData,
    frequencies: Iterable[float] = DEFAULT_FREQ_P,
    case_ids: Iterable[str] = ("existing", "proposed"),
) -> None:
    for case_id in case_ids:
        for frequency in frequencies:
            get_frequency(project, case_id, frequency, create=True)


def build_case_compare(
    project: ProjectData,
    program_id: str,
    case_pair: tuple[str, str] = ("existing", "proposed"),
    frequencies: Iterable[float] = DEFAULT_FREQ_P,
) -> dict[str, Any]:
    ensure_case_frequencies(project, frequencies, case_pair)
    rows: list[dict[str, Any]] = []
    for frequency in frequencies:
        left = _result_outputs(project, case_pair[0], frequency, program_id)
        right = _result_outputs(project, case_pair[1], frequency, program_id)
        row = {
            "freqP": float(frequency),
            "existingBlockRatio": _pick_number(left, ("blockRatio", "obstruction_area_ratio_percent")),
            "proposedBlockRatio": _pick_number(right, ("blockRatio", "obstruction_area_ratio_percent")),
            "existingBackwater": _pick_number(left, ("backwater", "max_backwater_height_m")),
            "proposedBackwater": _pick_number(right, ("backwater", "max_backwater_height_m")),
            "existingWaterLevel": _pick_number(left, ("desWL", "design_water_level_m", "water_level")),
            "proposedWaterLevel": _pick_number(right, ("desWL", "design_water_level_m", "water_level")),
        }
        row["blockRatioDelta"] = _delta(row["proposedBlockRatio"], row["existingBlockRatio"])
        row["backwaterDelta"] = _delta(row["proposedBackwater"], row["existingBackwater"])
        row["waterLevelDelta"] = _delta(row["proposedWaterLevel"], row["existingWaterLevel"])
        rows.append(row)
    return {
        "programId": program_id,
        "casePair": list(case_pair),
        "frequencies": [float(item) for item in frequencies],
        "rows": rows,
        "summary": {
            "rowCount": len(rows),
            "hasExisting": any(_result_outputs(project, case_pair[0], freq, program_id) for freq in frequencies),
            "hasProposed": any(_result_outputs(project, case_pair[1], freq, program_id) for freq in frequencies),
        },
    }


def export_case_compare_xlsx(compare: dict[str, Any], path: str | Path) -> Path:
    target = Path(path)
    target.parent.mkdir(parents=True, exist_ok=True)
    lines = [
        "工况与频率对比表",
        f"程序ID\t{compare.get('programId', '')}",
        "",
        (
            "频率P(%)\t现状阻水比(%)\t工程后阻水比(%)\t阻水比增量(%)\t"
            "现状壅水(m)\t工程后壅水(m)\t壅水增量(m)\t"
            "现状水位(m)\t工程后水位(m)\t水位增量(m)"
        ),
    ]
    for row in compare.get("rows", []):
        lines.append(
            "\t".join(
                [
                    _format_cell(row.get("freqP")),
                    _format_cell(row.get("existingBlockRatio")),
                    _format_cell(row.get("proposedBlockRatio")),
                    _format_cell(row.get("blockRatioDelta")),
                    _format_cell(row.get("existingBackwater")),
                    _format_cell(row.get("proposedBackwater")),
                    _format_cell(row.get("backwaterDelta")),
                    _format_cell(row.get("existingWaterLevel")),
                    _format_cell(row.get("proposedWaterLevel")),
                    _format_cell(row.get("waterLevelDelta")),
                ]
            )
        )
    export_calculation_book_xlsx("\n".join(lines), target, title="工况与频率对比表")
    return target


def run_case_compare(
    project: ProjectData,
    program_id: str,
    case_pair: tuple[str, str] = ("existing", "proposed"),
    *,
    export_path: str | Path | None = None,
    frequencies: Iterable[float] = DEFAULT_FREQ_P,
) -> dict[str, Any]:
    compare = build_case_compare(project, program_id, case_pair, frequencies)
    if export_path:
        compare["exportPath"] = str(export_case_compare_xlsx(compare, export_path))
    return compare


def _result_outputs(project: ProjectData, case_id: str, frequency: float | str, program_id: str) -> dict[str, Any]:
    try:
        record = get_frequency(project, case_id, frequency).results.get(program_id)
    except KeyError:
        return {}
    return dict(record.outputs) if record is not None else {}


def _pick_number(values: dict[str, Any], keys: tuple[str, ...]) -> float | None:
    for key in keys:
        value = values.get(key)
        if isinstance(value, (int, float)):
            return float(value)
        if isinstance(value, str):
            try:
                return float(value)
            except ValueError:
                continue
    return None


def _delta(new_value: float | None, old_value: float | None) -> float | None:
    if new_value is None or old_value is None:
        return None
    return round(new_value - old_value, 6)


def _format_cell(value: Any) -> str:
    if value is None:
        return "待计算"
    if isinstance(value, float):
        return f"{value:.3f}".rstrip("0").rstrip(".")
    return str(value)
