"""lab_component_nutrients: zootech-показатели в колонках (не JSON на component).

Revision ID: lab_component_nutrients
Revises: lab_module
Create Date: 2026-06-08
"""

from __future__ import annotations

import json

import sqlalchemy as sa
from alembic import op


revision = "lab_component_nutrients"
down_revision = "lab_module"
branch_labels = None
depends_on = None


def _normalize_key(value: str) -> str:
    return " ".join((value or "").split()).strip().lower()


def _read_num(data: dict, keys: tuple[str, ...]) -> float | None:
    for search in keys:
        target = _normalize_key(search)
        for k, v in data.items():
            nk = _normalize_key(str(k))
            if nk == target or target in nk or nk in target:
                try:
                    n = float(v)
                    if n == n:
                        return n
                except (TypeError, ValueError):
                    pass
    return None


_FIELD_KEYS = {
    "crude_protein": ("Сыр. Протеин",),
    "usp": ("уСП",),
    "bra": ("БРА", "    БРА "),
    "nel_cattle": ("ЧЭЛ- КРС", "    ЧЭЛ- КРС"),
    "oe_cattle": ("ОЭ-КРС", "    ОЭ-КРС"),
    "ndf": ("Сырая клетчатка", "Сырая клетч"),
    "structural_fiber": ("Структур. клетчатка", "Структур клетч"),
    "crude_fat": ("Сырой жир",),
}


def _migrate_json_nutrients(bind) -> None:
    insp = sa.inspect(bind)
    if "component" not in insp.get_table_names():
        return
    cols = {c["name"] for c in insp.get_columns("component")}
    if "nutrients" not in cols:
        return
    rows = bind.execute(sa.text("SELECT id, nutrients FROM component WHERE is_deleted = 0")).fetchall()
    for comp_id, raw in rows:
        try:
            data = json.loads(raw or "{}")
        except (json.JSONDecodeError, TypeError):
            data = {}
        if not isinstance(data, dict) or not data:
            continue
        payload = {col: _read_num(data, keys) for col, keys in _FIELD_KEYS.items()}
        if not any(v is not None for v in payload.values()):
            continue
        bind.execute(
            sa.text(
                """
                INSERT INTO lab_component_nutrients (
                    component_id, crude_protein, usp, bra, nel_cattle,
                    oe_cattle, ndf, structural_fiber, crude_fat
                ) VALUES (
                    :component_id, :crude_protein, :usp, :bra, :nel_cattle,
                    :oe_cattle, :ndf, :structural_fiber, :crude_fat
                )
                ON CONFLICT(component_id) DO UPDATE SET
                    crude_protein = excluded.crude_protein,
                    usp = excluded.usp,
                    bra = excluded.bra,
                    nel_cattle = excluded.nel_cattle,
                    oe_cattle = excluded.oe_cattle,
                    ndf = excluded.ndf,
                    structural_fiber = excluded.structural_fiber,
                    crude_fat = excluded.crude_fat
                """
            ),
            {"component_id": comp_id, **payload},
        )


def upgrade(**kw) -> None:
    if kw.get("tag") == "reports":
        return
    bind = op.get_bind()
    insp = sa.inspect(bind)
    if "lab_component_nutrients" not in insp.get_table_names():
        op.create_table(
            "lab_component_nutrients",
            sa.Column("component_id", sa.String(length=36), primary_key=True),
            sa.Column("crude_protein", sa.Float(), nullable=True),
            sa.Column("usp", sa.Float(), nullable=True),
            sa.Column("bra", sa.Float(), nullable=True),
            sa.Column("nel_cattle", sa.Float(), nullable=True),
            sa.Column("oe_cattle", sa.Float(), nullable=True),
            sa.Column("ndf", sa.Float(), nullable=True),
            sa.Column("structural_fiber", sa.Float(), nullable=True),
            sa.Column("crude_fat", sa.Float(), nullable=True),
        )
    _migrate_json_nutrients(bind)

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


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

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

    if "lab_component_nutrients" in insp.get_table_names():
        op.drop_table("lab_component_nutrients")