site/WESP_REL/app/services/analytics/reports_detail.py

"""Детальные строки отчётов загрузки для экспорта."""

from __future__ import annotations

from typing import Any, Dict, List, Optional

from sqlalchemy import select

from app import db
from app.models import (
    ComponentLoadingTime,
    LoadingReport,
    LoadingReportComponent,
    UnloadingReport,
    UnloadingReportGroup,
)
from app.services.analytics.date_range import resolve_date_bounds
from app.services.analytics.recipe_filter import parse_recipe_ids
from app.services.analytics.recipe_location import build_recipe_location_maps

UNLOADING_FEED_COMPONENT_LABEL = "Выгрузка полного миксера"
_UNLOADING_NAME_PREFIX = "выгрузка:"


def _parse_unloading_group_name(raw: str) -> str:
    text = (raw or "—").strip()
    if text.lower().startswith(_UNLOADING_NAME_PREFIX):
        parsed = text[len(_UNLOADING_NAME_PREFIX) :].strip()
        return parsed or "—"
    return text or "—"


def _overload_kg(actual: float, target: float) -> float:
    """Перерасход только при факте выше плана; недогруз не дублируем в эту колонку."""
    return round(max(0.0, actual - target), 2)


def _dedupe_detail_rows(rows: List[Dict[str, Any]]) -> List[Dict[str, Any]]:
    """Убрать полные дубликаты строк (одинаковый рейс, время, компонент, план и факт)."""
    seen: set[tuple[Any, ...]] = set()
    out: List[Dict[str, Any]] = []
    for row in rows:
        key = (
            row.get("reportId"),
            row.get("kind"),
            row.get("feedComponent"),
            row.get("animalGroup"),
            row.get("dateTime"),
            row.get("recipeName"),
            row.get("targetKg"),
            row.get("actualKg"),
        )
        if key in seen:
            continue
        seen.add(key)
        out.append(row)
    return out


def build_reports_detail_rows(
    *,
    date_from: Optional[str] = None,
    date_to: Optional[str] = None,
    recipe_id: Optional[str] = None,
    recipe_ids: Optional[str] = None,
) -> List[Dict[str, Any]]:
    start_dt, end_dt = resolve_date_bounds(date_from, date_to)
    q = select(LoadingReport).where(
        LoadingReport.is_deleted.is_(False),
        LoadingReport.start_time >= start_dt,
        LoadingReport.start_time <= end_dt,
    )
    ids = parse_recipe_ids(recipe_id=recipe_id, recipe_ids=recipe_ids)
    if ids:
        q = q.where(LoadingReport.recipe_id.in_(ids) if len(ids) > 1 else LoadingReport.recipe_id == ids[0])
    reports = db.session.execute(q.order_by(LoadingReport.start_time.desc())).scalars().all()
    if not reports:
        return []

    recipe_ids_set = {r.recipe_id for r in reports if r.recipe_id}
    farm_by_recipe, dispenser_by_recipe = build_recipe_location_maps(recipe_ids_set)

    report_ids = [r.id for r in reports]
    components = db.session.execute(
        select(LoadingReportComponent).where(
            LoadingReportComponent.report_id.in_(report_ids),
            LoadingReportComponent.is_deleted.is_(False),
        ).order_by(LoadingReportComponent.loading_order.asc())
    ).scalars().all()
    loading_times = db.session.execute(
        select(ComponentLoadingTime).where(
            ComponentLoadingTime.report_id.in_(report_ids),
            ComponentLoadingTime.is_deleted.is_(False),
        )
    ).scalars().all()
    unloading_rows = db.session.execute(
        select(UnloadingReport).where(
            UnloadingReport.loading_report_id.in_(report_ids),
            UnloadingReport.is_deleted.is_(False),
        )
    ).scalars().all()

    comps_by_report: Dict[str, List[LoadingReportComponent]] = {}
    for c in components:
        comps_by_report.setdefault(c.report_id, []).append(c)

    load_sec_by_report_name: Dict[str, Dict[str, float]] = {}
    for t in loading_times:
        if not t.report_id or not t.component_name:
            continue
        load_sec_by_report_name.setdefault(t.report_id, {})[
            str(t.component_name)
        ] = float(t.loading_duration or 0)

    unloading_by_loading = {u.loading_report_id: u for u in unloading_rows}
    unloading_ids = [u.id for u in unloading_rows]
    unloading_groups: List[UnloadingReportGroup] = []
    if unloading_ids:
        unloading_groups = list(
            db.session.execute(
                select(UnloadingReportGroup).where(
                    UnloadingReportGroup.report_id.in_(unloading_ids),
                    UnloadingReportGroup.is_deleted.is_(False),
                ).order_by(UnloadingReportGroup.order.asc())
            ).scalars().all()
        )
    groups_by_unloading: Dict[str, List[UnloadingReportGroup]] = {}
    for g in unloading_groups:
        groups_by_unloading.setdefault(g.report_id, []).append(g)

    rows: List[Dict[str, Any]] = []
    for report in reports:
        when = report.start_time.strftime("%d.%m.%Y %H:%M") if report.start_time else ""
        date_day = report.start_time.strftime("%d.%m.%Y") if report.start_time else ""
        farm = farm_by_recipe.get(report.recipe_id, "—")
        dispenser = dispenser_by_recipe.get(report.recipe_id, "—")
        mix_plan = int(report.target_mixing_time or 0)
        mix_fact = int(report.actual_mixing_time or 0)
        load_secs = load_sec_by_report_name.get(report.id, {})

        for comp in comps_by_report.get(report.id, []):
            target = float(comp.target_weight or 0)
            actual = float(comp.actual_weight or 0)
            dev = round(actual - target, 2)
            cname = str(comp.component_name or "—")
            rows.append(
                {
                    "kind": "component",
                    "reportId": report.id,
                    "dateDay": date_day,
                    "farm": farm,
                    "dispenserName": dispenser,
                    "recipeName": report.recipe_name,
                    "dateTime": when,
                    "feedComponent": cname,
                    "animalGroup": "",
                    "name": cname,
                    "targetKg": round(target, 2),
                    "actualKg": round(actual, 2),
                    "deviationKg": dev,
                    "overloadKg": _overload_kg(actual, target),
                    "loadingSec": round(load_secs.get(cname, 0), 1),
                    "mixPlanSec": mix_plan,
                    "mixFactSec": mix_fact,
                }
            )

        unloading = unloading_by_loading.get(report.id)
        if unloading:
            for group in groups_by_unloading.get(unloading.id, []):
                target = float(group.target_weight or 0)
                actual = float(group.unloaded_weight or 0)
                group_name = _parse_unloading_group_name(str(group.name or ""))
                rows.append(
                    {
                        "kind": "unloading",
                        "reportId": report.id,
                        "dateDay": date_day,
                        "farm": farm,
                        "dispenserName": dispenser,
                        "recipeName": report.recipe_name,
                        "dateTime": when,
                        "feedComponent": UNLOADING_FEED_COMPONENT_LABEL,
                        "animalGroup": group_name,
                        "name": f"выгрузка: {group_name}",
                        "targetKg": round(target, 2),
                        "actualKg": round(actual, 2),
                        "deviationKg": round(actual - target, 2),
                        "overloadKg": _overload_kg(actual, target),
                        "loadingSec": None,
                        "mixPlanSec": mix_plan,
                        "mixFactSec": mix_fact,
                    }
                )

    rows = _dedupe_detail_rows(rows)
    rows.sort(key=lambda r: (r.get("dateDay") or "", r.get("recipeName") or "", r.get("name") or ""))
    return rows