"""AgroStar xlsx (сводка проб) → AgrostarParseResult."""

from __future__ import annotations

import io
from typing import Any

from openpyxl import load_workbook

from app.lab.etl.agrostar_tabular import map_tabular_label, sample_from_agro_fields, tabular_rows_to_agro_fields
from app.lab.etl.agrostar_xml_import import AgrostarParseResult, _parse_float


def parse_agrostar_xlsx(data: bytes) -> AgrostarParseResult:
    result = AgrostarParseResult(lab_name="АгроСтар")
    try:
        wb = load_workbook(io.BytesIO(data), data_only=True, read_only=False)
    except Exception as exc:  # noqa: BLE001
        result.errors.append(f"Не удалось открыть xlsx: {exc}")
        return result

    ws = wb.active
    if ws.max_row < 4 or ws.max_column < 2:
        result.errors.append("Пустой или неузнаваемый xlsx")
        return result

    sample_nos: list[str] = []
    farms: list[str] = []
    labels: list[str] = []
    for col in range(2, ws.max_column + 1):
        no = ws.cell(1, col).value
        if no is None:
            continue
        sample_nos.append(str(no).strip())
        farms.append(str(ws.cell(2, col).value or "").strip())
        labels.append(str(ws.cell(3, col).value or "").strip())

    if not sample_nos:
        result.errors.append("В xlsx нет колонок с пробами")
        return result

    row_values: list[tuple[str, list[str]]] = []
    for row in range(4, ws.max_row + 1):
        label = ws.cell(row, 1).value
        if label is None:
            continue
        label_str = str(label).strip()
        if not label_str or not map_tabular_label(label_str):
            continue
        vals = []
        for col in range(2, 2 + len(sample_nos)):
            raw = ws.cell(row, col).value
            vals.append("" if raw is None else str(raw))
        row_values.append((label_str, vals))

    for idx, sample_no in enumerate(sample_nos):
        pairs = [(label, vals[idx]) for label, vals in row_values if idx < len(vals)]
        agro_fields = tabular_rows_to_agro_fields(pairs)
        if "DM" not in agro_fields:
            result.errors.append(f"Проба {sample_no}: нет СВ")
            continue
        sample = sample_from_agro_fields(
            sample_no=sample_no,
            farm_name=farms[idx] if idx < len(farms) else "",
            desc_1=labels[idx] if idx < len(labels) else sample_no,
            agro_fields=agro_fields,
        )
        result.samples.append(sample)

    if not result.samples and not result.errors:
        result.errors.append("Не удалось разобрать пробы из xlsx")
    return result