"""Normalize lab runtime storage: drop JSON blobs, use relational tables.

Revision ID: lab_normalize
Revises: lab_component_nutrient_value
Create Date: 2026-06-08
"""

from __future__ import annotations

import json
import uuid

import sqlalchemy as sa
from alembic import op

from app.lab.models import (
    LabAnimalProfile,
    LabProfileNorm,
    LabRationCalcIndicator,
    LabRationCalcTotal,
    LabRationCompoundLine,
    LabRecipeRation,
)


revision = "lab_normalize"
down_revision = "lab_component_nutrient_value"
branch_labels = None
depends_on = None


def _parse_num(value) -> float | None:
    if value is None or value == "":
        return None
    try:
        n = float(value)
    except (TypeError, ValueError):
        return None
    return None if n != n else n


def _norms_from_payload(raw: str | None) -> tuple[float | None, int | None, dict]:
    if not raw or not str(raw).strip() or str(raw).strip() == "{}":
        return None, None, {}
    try:
        data = json.loads(raw)
    except (json.JSONDecodeError, TypeError):
        return None, None, {}
    if not isinstance(data, dict):
        return None, None, {}
    mass = _parse_num(data.get("massKg", data.get("mass_kg")))
    ext = data.get("externalNo", data.get("external_no"))
    external_no = int(ext) if ext is not None and str(ext).strip() != "" else None
    indicators = data.get("indicators")
    out: dict = {}
    if isinstance(indicators, dict):
        for key, bounds in indicators.items():
            if isinstance(bounds, dict):
                out[str(key)] = {
                    "min": _parse_num(bounds.get("min")),
                    "max": _parse_num(bounds.get("max")),
                }
    alias_map = {
        "dry_matter": ["Сухое Вещество", "Сухое вещество"],
        "crude_protein": ["Сыр. Протеин"],
        "usp": ["уСП"],
        "oe": ["ОЭ-КРС", "    ОЭ-КРС"],
        "nel": ["ЧЭЛ- КРС", "    ЧЭЛ- КРС"],
    }
    for key, aliases in alias_map.items():
        if key in out:
            continue
        for alias in aliases:
            entry = data.get(alias)
            if isinstance(entry, dict):
                out[key] = {
                    "min": _parse_num(entry.get("min")),
                    "max": _parse_num(entry.get("max")),
                }
                break
    return mass, external_no, out


def _indicator_key(label: str) -> str | None:
    mapping = {
        "Сухое вещество": "dry_matter",
        "СВ — основной корм": "dm_main",
        "ОЭ — КРС / Дойн": "oe",
        "ЧЭЛ — КРС / Дойн": "nel",
        "ЧЭЛ/кг СВ": "nel_per_kg_dm",
        "Сырой протеин": "crude_protein",
        "Нер. СП / кг СВ": "rup_per_kg_dm",
        "Нерастворимый протеин": "insoluble_protein",
        "уСП": "usp",
        "% уСП/кг СВ": "usp_pct_dm",
    }
    return mapping.get(label)


def _migrate_profile_norms(bind) -> None:
    insp = sa.inspect(bind)
    if "lab_animal_profile" not in insp.get_table_names():
        return
    cols = {c["name"] for c in insp.get_columns("lab_animal_profile")}
    if "norms_data" not in cols:
        return
    rows = bind.execute(sa.text("SELECT id, norms_data FROM lab_animal_profile")).fetchall()
    for profile_id, raw in rows:
        mass, external_no, norms = _norms_from_payload(raw)
        bind.execute(
            sa.text(
                "UPDATE lab_animal_profile SET mass_kg = :mass, external_no = :external_no WHERE id = :id"
            ),
            {"id": profile_id, "mass": mass, "external_no": external_no},
        )
        for indicator_key, bounds in norms.items():
            min_v = bounds.get("min")
            max_v = bounds.get("max")
            if min_v is None and max_v is None:
                continue
            bind.execute(
                sa.text(
                    """
                    INSERT INTO lab_profile_norm
                        (id, profile_id, indicator_key, min_value, max_value)
                    VALUES (:id, :profile_id, :indicator_key, :min_value, :max_value)
                    """
                ),
                {
                    "id": str(uuid.uuid4()),
                    "profile_id": profile_id,
                    "indicator_key": indicator_key,
                    "min_value": min_v,
                    "max_value": max_v,
                },
            )


def _save_calc(bind, recipe_id: str, result: dict, calculated_at) -> None:
    engine = str(result.get("engine") or "native")
    bind.execute(
        sa.text(
            "UPDATE lab_recipe_ration SET calc_engine = :engine, calculated_at = :calculated_at WHERE recipe_id = :recipe_id"
        ),
        {"engine": engine, "calculated_at": calculated_at, "recipe_id": recipe_id},
    )
    for scope, block_key in (("ration", None), ("compound", "compound")):
        block = result if block_key is None else result.get(block_key) or {}
        if not isinstance(block, dict):
            continue
        for idx, row in enumerate(block.get("totals") or []):
            bind.execute(
                sa.text(
                    """
                    INSERT INTO lab_ration_calc_total
                        (id, recipe_id, scope, metric_key, label, value, sort_order)
                    VALUES (:id, :recipe_id, :scope, :metric_key, :label, :value, :sort_order)
                    """
                ),
                {
                    "id": str(uuid.uuid4()),
                    "recipe_id": recipe_id,
                    "scope": scope,
                    "metric_key": str(row.get("key") or f"metric_{idx}"),
                    "label": str(row.get("label") or ""),
                    "value": _parse_num(row.get("value")),
                    "sort_order": idx,
                },
            )
        for idx, row in enumerate(block.get("indicators") or []):
            bind.execute(
                sa.text(
                    """
                    INSERT INTO lab_ration_calc_indicator
                        (id, recipe_id, scope, indicator_key, label, unit,
                         min_value, max_value, content, diff, sort_order)
                    VALUES (:id, :recipe_id, :scope, :indicator_key, :label, :unit,
                            :min_value, :max_value, :content, :diff, :sort_order)
                    """
                ),
                {
                    "id": str(uuid.uuid4()),
                    "recipe_id": recipe_id,
                    "scope": scope,
                    "indicator_key": _indicator_key(str(row.get("label") or "")),
                    "label": str(row.get("label") or ""),
                    "unit": str(row.get("unit") or ""),
                    "min_value": _parse_num(row.get("min")),
                    "max_value": _parse_num(row.get("max")),
                    "content": _parse_num(row.get("content")),
                    "diff": _parse_num(row.get("diff")),
                    "sort_order": idx,
                },
            )
        if scope == "compound":
            for idx, row in enumerate(block.get("lines") or []):
                bind.execute(
                    sa.text(
                        """
                        INSERT INTO lab_ration_compound_line
                            (id, recipe_id, row_index, ingredient_name, daily_kg, share_pct)
                        VALUES (:id, :recipe_id, :row_index, :ingredient_name, :daily_kg, :share_pct)
                        """
                    ),
                    {
                        "id": str(uuid.uuid4()),
                        "recipe_id": recipe_id,
                        "row_index": idx,
                        "ingredient_name": row.get("ingredient_name"),
                        "daily_kg": _parse_num(row.get("daily_kg")),
                        "share_pct": _parse_num(row.get("share_pct")),
                    },
                )
    for idx, message in enumerate(result.get("errors") or []):
        text = str(message or "").strip()
        if not text:
            continue
        bind.execute(
            sa.text(
                """
                INSERT INTO lab_ration_calc_error (id, recipe_id, sort_order, message)
                VALUES (:id, :recipe_id, :sort_order, :message)
                """
            ),
            {
                "id": str(uuid.uuid4()),
                "recipe_id": recipe_id,
                "sort_order": idx,
                "message": text,
            },
        )


def _migrate_calc_results(bind) -> None:
    insp = sa.inspect(bind)
    if "lab_recipe_ration" not in insp.get_table_names():
        return
    cols = {c["name"] for c in insp.get_columns("lab_recipe_ration")}
    if "ration_results" not in cols:
        return
    rows = bind.execute(
        sa.text(
            "SELECT recipe_id, params_json, ration_results, compound_results, calculated_at FROM lab_recipe_ration"
        )
    ).fetchall()
    for recipe_id, params_raw, ration_raw, compound_raw, calculated_at in rows:
        if params_raw:
            try:
                params = json.loads(params_raw or "{}")
            except (json.JSONDecodeError, TypeError):
                params = {}
            if isinstance(params, dict):
                seeded = params.get("seeded_from") or params.get("synced_from")
                if seeded:
                    bind.execute(
                        sa.text(
                            "UPDATE lab_recipe_ration SET seed_source = :seed WHERE recipe_id = :recipe_id"
                        ),
                        {"seed": str(seeded), "recipe_id": recipe_id},
                    )
        result: dict = {}
        if ration_raw:
            try:
                parsed = json.loads(ration_raw or "{}")
                if isinstance(parsed, dict):
                    result = parsed
            except (json.JSONDecodeError, TypeError):
                result = {}
        if compound_raw:
            try:
                compound = json.loads(compound_raw or "{}")
                if isinstance(compound, dict) and compound:
                    result["compound"] = compound
            except (json.JSONDecodeError, TypeError):
                pass
        if result:
            _save_calc(bind, recipe_id, result, calculated_at)


def upgrade(**kw) -> None:
    if kw.get("tag") == "reports":
        return
    bind = op.get_bind()
    insp = sa.inspect(bind)

    if "lab_animal_profile" in insp.get_table_names():
        cols = {c["name"] for c in insp.get_columns("lab_animal_profile")}
        if "mass_kg" not in cols:
            with op.batch_alter_table("lab_animal_profile") as batch:
                batch.add_column(sa.Column("mass_kg", sa.Float(), nullable=True))
                batch.add_column(sa.Column("external_no", sa.Integer(), nullable=True))

    if "lab_recipe_ration" in insp.get_table_names():
        cols = {c["name"] for c in insp.get_columns("lab_recipe_ration")}
        if "calc_engine" not in cols:
            with op.batch_alter_table("lab_recipe_ration") as batch:
                batch.add_column(sa.Column("calc_engine", sa.String(length=20), nullable=True))
                batch.add_column(sa.Column("seed_source", sa.String(length=32), nullable=True))

    for model in (
        LabProfileNorm,
        LabRationCalcTotal,
        LabRationCalcIndicator,
        LabRationCompoundLine,
    ):
        model.__table__.create(bind, checkfirst=True)

    _migrate_profile_norms(bind)
    _migrate_calc_results(bind)

    insp = sa.inspect(bind)
    if "lab_animal_profile" in insp.get_table_names():
        cols = {c["name"] for c in insp.get_columns("lab_animal_profile")}
        if "norms_data" in cols:
            with op.batch_alter_table("lab_animal_profile") as batch:
                batch.drop_column("norms_data")

    if "lab_recipe_ration" in insp.get_table_names():
        cols = {c["name"] for c in insp.get_columns("lab_recipe_ration")}
        with op.batch_alter_table("lab_recipe_ration") as batch:
            for col in ("params_json", "ration_results", "compound_results"):
                if col in cols:
                    batch.drop_column(col)

    if "lab_tab_ration_project" in insp.get_table_names():
        cols = {c["name"] for c in insp.get_columns("lab_tab_ration_project")}
        with op.batch_alter_table("lab_tab_ration_project") as batch:
            for col in ("settings_json", "results_json", "compound_json"):
                if col in cols:
                    batch.drop_column(col)

    if "lab_tab_ration_line" in insp.get_table_names():
        cols = {c["name"] for c in insp.get_columns("lab_tab_ration_line")}
        if "raw_data_json" in cols:
            with op.batch_alter_table("lab_tab_ration_line") as batch:
                batch.drop_column("raw_data_json")


def downgrade(**kw) -> None:
    if kw.get("tag") == "reports":
        return
    bind = op.get_bind()
    insp = sa.inspect(bind)

    if "lab_animal_profile" in insp.get_table_names():
        cols = {c["name"] for c in insp.get_columns("lab_animal_profile")}
        if "norms_data" not in cols:
            with op.batch_alter_table("lab_animal_profile") as batch:
                batch.add_column(sa.Column("norms_data", sa.Text(), nullable=False, server_default="{}"))
        for col in ("mass_kg", "external_no"):
            if col in cols:
                with op.batch_alter_table("lab_animal_profile") as batch:
                    batch.drop_column(col)

    if "lab_recipe_ration" in insp.get_table_names():
        cols = {c["name"] for c in insp.get_columns("lab_recipe_ration")}
        with op.batch_alter_table("lab_recipe_ration") as batch:
            for col, default in (
                ("params_json", "{}"),
                ("ration_results", "{}"),
                ("compound_results", "{}"),
            ):
                if col not in cols:
                    batch.add_column(sa.Column(col, sa.Text(), nullable=False, server_default=default))
            for col in ("calc_engine", "seed_source"):
                if col in cols:
                    batch.drop_column(col)

    for table in (
        "lab_ration_calc_error",
        "lab_ration_compound_line",
        "lab_ration_calc_indicator",
        "lab_ration_calc_total",
        "lab_profile_norm",
    ):
        if table in insp.get_table_names():
            op.drop_table(table)