mitai-jinkendo/backend/routers/admin_bls.py

"""Admin BLS catalog import and attribute/food maintenance."""
from __future__ import annotations

from typing import Optional

from fastapi import APIRouter, Depends, File, HTTPException, UploadFile
from fastapi.concurrency import run_in_threadpool
from pydantic import BaseModel

from auth import require_admin
from bls.import_service import upsert_attributes, upsert_foods
from bls.jobs import create_and_check, get_job, start_apply
from bls.parser import parse_components_xlsx, parse_foods_xlsx
from db import get_cursor, get_db, r2d

MAX_IMPORT_BYTES = 50 * 1024 * 1024

router = APIRouter(prefix="/api/admin/bls", tags=["admin", "bls"])


class AttributeCreate(BaseModel):
    attr_key: str
    name_de: str
    name_en: Optional[str] = None
    unit: Optional[str] = None
    category: Optional[str] = None
    data_type: str = "num_per_100g"
    enum_values: Optional[list] = None


class AttributeUpdate(BaseModel):
    name_de: Optional[str] = None
    name_en: Optional[str] = None
    unit: Optional[str] = None
    category: Optional[str] = None
    sort_order: Optional[int] = None


class ManualFoodCreate(BaseModel):
    name_de: str
    name_en: Optional[str] = None
    macros_per_100g: Optional[dict] = None
    attributes: Optional[dict] = None
    serving_g: Optional[float] = None


@router.get("/status")
def bls_status(session: dict = Depends(require_admin)):
    with get_db() as conn:
        cur = get_cursor(conn)
        cur.execute("SELECT COUNT(*) AS n FROM food_catalog WHERE catalog_kind = 'official_bls'")
        foods = cur.fetchone()["n"]
        cur.execute("SELECT COUNT(*) AS n FROM food_attributes WHERE origin = 'official_bls'")
        attrs = cur.fetchone()["n"]
        cur.execute("SELECT COUNT(*) AS n FROM food_catalog WHERE catalog_kind <> 'official_bls'")
        manual = cur.fetchone()["n"]
        cur.execute("SELECT MAX(updated_at) AS last_updated FROM food_catalog WHERE catalog_kind = 'official_bls'")
        last = cur.fetchone()["last_updated"]
    return {
        "official_foods": foods,
        "official_attributes": attrs,
        "manual_foods": manual,
        "last_updated": last,
        "source": "Max Rubner-Institut, BLS 4.0 (frei verfügbar)",
    }


@router.post("/import/components")
async def import_components(
    file: UploadFile = File(...),
    dry_run: bool = True,
    session: dict = Depends(require_admin),
):
    raw = await file.read()
    if not raw:
        raise HTTPException(400, "Leere Datei")
    try:
        attrs = await run_in_threadpool(parse_components_xlsx, raw)
    except Exception as e:
        raise HTTPException(400, f"Components-Datei unlesbar: {e}") from e
    if dry_run:
        return {"dry_run": True, "attributes": len(attrs), "sample": attrs[:8]}

    def apply():
        with get_db() as conn:
            cur = get_cursor(conn)
            return upsert_attributes(cur, attrs)

    stats = await run_in_threadpool(apply)
    return {"dry_run": False, **stats}


@router.post("/import/foods")
async def import_foods(
    file: UploadFile = File(...),
    dry_run: bool = True,
    session: dict = Depends(require_admin),
):
    raw = await file.read()
    if not raw:
        raise HTTPException(400, "Leere Datei")
    try:
        parsed = await run_in_threadpool(parse_foods_xlsx, raw)
    except Exception as e:
        raise HTTPException(400, f"Datendatei unlesbar: {e}") from e
    foods = parsed["foods"]
    if dry_run:
        return {
            "dry_run": True,
            "foods": len(foods),
            "attribute_columns": len(parsed["attribute_headers"]),
            "sample": [
                {"bls_code": f["bls_code"], "name_de": f["name_de"]}
                for f in foods[:8]
            ],
        }

    def apply():
        with get_db() as conn:
            cur = get_cursor(conn)
            return upsert_foods(cur, foods)

    stats = await run_in_threadpool(apply)
    return {"dry_run": False, **stats}


@router.post("/import/jobs")
async def start_import_job(
    kind: str,
    file: UploadFile = File(...),
    session: dict = Depends(require_admin),
):
    if kind not in ("components", "foods"):
        raise HTTPException(400, "kind muss components oder foods sein")
    raw = await file.read()
    if not raw:
        raise HTTPException(400, "Leere Datei")
    if len(raw) > MAX_IMPORT_BYTES:
        raise HTTPException(400, "Datei größer als 50 MB")
    job_id = create_and_check(kind, raw)
    return {"id": job_id, "status": "checking"}


@router.get("/import/jobs/{job_id}")
def import_job_status(job_id: str, session: dict = Depends(require_admin)):
    job = get_job(job_id)
    if not job:
        raise HTTPException(404, "Import-Job nicht gefunden")
    return job


@router.post("/import/jobs/{job_id}/apply")
def apply_import_job(job_id: str, session: dict = Depends(require_admin)):
    try:
        start_apply(job_id)
    except KeyError:
        raise HTTPException(404, "Import-Job nicht gefunden") from None
    except ValueError as e:
        raise HTTPException(409, str(e)) from e
    return get_job(job_id)


@router.get("/foods")
def admin_list_foods(
    q: Optional[str] = None,
    kind: Optional[str] = None,
    limit: int = 50,
    session: dict = Depends(require_admin),
):
    limit = min(max(limit, 1), 200)
    with get_db() as conn:
        cur = get_cursor(conn)
        conds = ["is_active = true"]
        params: list = []
        if kind == "manual":
            conds.append("catalog_kind IN ('manual_user', 'manual_admin')")
        elif kind:
            conds.append("catalog_kind = %s")
            params.append(kind)
        if q:
            conds.append("(name_de ILIKE %s OR COALESCE(name_en,'') ILIKE %s OR COALESCE(bls_code,'') ILIKE %s)")
            like = f"%{q}%"
            params.extend([like, like, like])
        params.append(limit)
        cur.execute(
            f"""
            SELECT id, bls_code, name_de, name_en, catalog_kind, food_group, bls_version, owner_profile_id
            FROM food_catalog
            WHERE {' AND '.join(conds)}
            ORDER BY name_de
            LIMIT %s
            """,
            params,
        )
        return [r2d(r) for r in cur.fetchall()]


@router.get("/foods/{food_id}")
def admin_food_detail(food_id: str, editor: bool = False, session: dict = Depends(require_admin)):
    from data_layer.nutrition_items import resolve_food_attributes

    with get_db() as conn:
        cur = get_cursor(conn)
        cur.execute("SELECT * FROM food_catalog WHERE id = %s", (food_id,))
        row = cur.fetchone()
        if not row:
            raise HTTPException(404, "Lebensmittel nicht gefunden")
        food = dict(row)
        if editor:
            from data_layer.food_attributes import editor_payload_for_food
            from data_layer.food_visibility import attach_visibility

            editable = food.get("catalog_kind") in ("manual_admin", "manual_user")
            return attach_visibility(editor_payload_for_food(cur, food, editable=editable))
        attrs = resolve_food_attributes(cur, food_id)
    return {**r2d(row), "attributes": attrs}


@router.post("/foods/manual")
def admin_create_manual_food(body: ManualFoodCreate, session: dict = Depends(require_admin)):
    from data_layer.food_attributes import macros_and_attributes_to_values, write_numeric_attributes
    from data_layer.food_mapping import normalize_food_name
    from data_layer.food_visibility import attach_visibility

    name = (body.name_de or "").strip()
    if not name:
        raise HTTPException(400, "Name fehlt")
    serving = body.serving_g if body.serving_g and body.serving_g > 0 else None
    values = macros_and_attributes_to_values(body.macros_per_100g, body.attributes, serving)
    with get_db() as conn:
        cur = get_cursor(conn)
        cur.execute(
            """
            INSERT INTO food_catalog (name_de, name_en, catalog_kind, source, external_key)
            VALUES (%s, %s, 'manual_admin', 'manual', %s)
            RETURNING *
            """,
            (name, body.name_en, f"man-admin-{normalize_food_name(name)[:40]}"),
        )
        food = r2d(cur.fetchone())
        write_numeric_attributes(cur, food["id"], values)
    from data_layer.food_suggest import invalidate_suggest_index
    invalidate_suggest_index()
    return attach_visibility(food)


@router.put("/foods/manual/{food_id}")
def admin_update_manual_food(food_id: str, body: ManualFoodCreate, session: dict = Depends(require_admin)):
    from collections import defaultdict

    from data_layer.food_attributes import (
        editor_payload_for_food,
        macros_and_attributes_to_values,
        replace_numeric_attributes,
    )
    from data_layer.food_visibility import attach_visibility
    from data_layer.nutrition_items import profiles_dates_using_food, schedule_daily_nutrient_rebuild

    name = (body.name_de or "").strip()
    if not name:
        raise HTTPException(400, "Name fehlt")
    serving = body.serving_g if body.serving_g and body.serving_g > 0 else None
    values = macros_and_attributes_to_values(body.macros_per_100g, body.attributes, serving)
    with get_db() as conn:
        cur = get_cursor(conn)
        cur.execute(
            """
            SELECT id, name_de, name_en, catalog_kind, owner_profile_id
            FROM food_catalog
            WHERE id = %s AND catalog_kind IN ('manual_admin', 'manual_user') AND is_active = true
            """,
            (food_id,),
        )
        food = cur.fetchone()
        if not food:
            raise HTTPException(404, "Manuelles Lebensmittel nicht gefunden")
        cur.execute(
            """
            UPDATE food_catalog
            SET name_de = %s, name_en = %s, updated_at = NOW()
            WHERE id = %s
            RETURNING id, name_de, name_en, catalog_kind, owner_profile_id
            """,
            (name, body.name_en, food_id),
        )
        updated = dict(cur.fetchone())
        replace_numeric_attributes(cur, food_id, values)
        pairs = profiles_dates_using_food(cur, food_id)
        payload = attach_visibility(editor_payload_for_food(cur, updated, editable=True))
    from data_layer.food_suggest import invalidate_suggest_index
    invalidate_suggest_index()
    by_profile = defaultdict(list)
    for pid, day in pairs:
        by_profile[pid].append(day)
    for pid, dates in by_profile.items():
        schedule_daily_nutrient_rebuild(pid, dates, f"Zentrales Lebensmittel {name}")
    payload["rebuild_pending"] = len(pairs)
    return payload


@router.get("/attributes")
def admin_list_attributes(session: dict = Depends(require_admin)):
    from bls.attribute_labels import annotate_attribute, sort_attributes

    with get_db() as conn:
        cur = get_cursor(conn)
        cur.execute(
            """
            SELECT id, attr_key, name_de, name_en, unit, category, data_type, origin, sort_order
            FROM food_attributes
            WHERE is_active = true
            """
        )
        return sort_attributes([annotate_attribute(r2d(r)) for r in cur.fetchall()])


@router.post("/attributes/fill-labels")
def admin_fill_attribute_labels(session: dict = Depends(require_admin)):
    from bls.attribute_labels import fill_placeholder_labels

    with get_db() as conn:
        cur = get_cursor(conn)
        return fill_placeholder_labels(cur)


@router.post("/attributes")
def admin_create_attribute(body: AttributeCreate, session: dict = Depends(require_admin)):
    key = body.attr_key.strip().upper().replace(" ", "_")
    if not key:
        raise HTTPException(400, "attr_key fehlt")
    if body.data_type not in ("num_per_100g", "boolean", "text", "enum"):
        raise HTTPException(400, "Ungültiger data_type")
    with get_db() as conn:
        cur = get_cursor(conn)
        try:
            cur.execute(
                """
                INSERT INTO food_attributes
                    (attr_key, name_de, name_en, unit, category, data_type, enum_values, origin, sort_order)
                VALUES (%s, %s, %s, %s, %s, %s, %s, 'extension', 9000)
                RETURNING *
                """,
                (
                    key, body.name_de, body.name_en, body.unit, body.category,
                    body.data_type, None if not body.enum_values else body.enum_values,
                ),
            )
        except Exception as e:
            raise HTTPException(409, f"Attribut existiert bereits oder ist ungültig: {e}") from e
        return r2d(cur.fetchone())


@router.put("/attributes/{attr_id}")
def admin_update_attribute(attr_id: int, body: AttributeUpdate, session: dict = Depends(require_admin)):
    fields = []
    params: list = []
    if body.name_de is not None:
        name = body.name_de.strip()
        if not name:
            raise HTTPException(400, "Name fehlt")
        fields.append("name_de = %s")
        params.append(name)
    if body.name_en is not None:
        fields.append("name_en = %s")
        params.append(body.name_en.strip() or None)
    if body.unit is not None:
        fields.append("unit = %s")
        params.append(body.unit.strip() or None)
    if body.category is not None:
        fields.append("category = %s")
        params.append(body.category.strip() or None)
    if body.sort_order is not None:
        fields.append("sort_order = %s")
        params.append(body.sort_order)
    if not fields:
        raise HTTPException(400, "Keine Änderung")
    fields.append("updated_at = NOW()")
    params.append(attr_id)
    with get_db() as conn:
        cur = get_cursor(conn)
        cur.execute(
            f"""
            UPDATE food_attributes
            SET {', '.join(fields)}
            WHERE id = %s AND is_active = true
            RETURNING id, attr_key, name_de, name_en, unit, category, data_type, origin, sort_order
            """,
            params,
        )
        row = cur.fetchone()
        if not row:
            raise HTTPException(404, "Stoff nicht gefunden")
        from bls.attribute_labels import annotate_attribute
        return annotate_attribute(r2d(row))


def _write_manual_macros(cur, food_id: str, macros: dict | None) -> None:
    from data_layer.food_attributes import macros_and_attributes_to_values, write_numeric_attributes
    write_numeric_attributes(cur, food_id, macros_and_attributes_to_values(macros, None))