shinkan-jinkendo/backend/routers/catalogs.py

"""
Catalog Management Endpoints for Shinkan Jinkendo

Admin-verwaltbare Stammdaten für Übungen, Fokusbereiche, Stile, etc.
"""
import re
from typing import Optional

from fastapi import APIRouter, HTTPException, Depends, Query

from db import get_db, get_cursor, r2d
from auth import require_auth

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


def _sql_active_status(column: str, status: Optional[str]) -> tuple[str, list]:
    """
    Filter „active“ schließt Legacy-Zeilen mit status IS NULL ein (sonst leere Dropdowns in der UI).
    """
    if not status:
        return "", []
    if status == "active":
        return f" ({column} = 'active' OR {column} IS NULL)", []
    return f" ({column} = %s)", [status]


def _slugify_skill_label(text: str) -> str:
    t = (text or "").strip().lower()
    t = re.sub(r"[^a-z0-9äöüß]+", "_", t, flags=re.IGNORECASE)
    t = re.sub(r"_+", "_", t).strip("_")
    return (t[:48] or "gruppe")


# ════════════════════════════════════════════════════════════════════════
# SKILL MAIN CATEGORIES (Hauptgruppen, z. B. „KARATE Fähigkeiten“)
# ════════════════════════════════════════════════════════════════════════


@router.get("/skill-main-categories")
def list_skill_main_categories(session=Depends(require_auth)):
    """Alle Hauptkategorien für den Fähigkeitskatalog (sortiert)."""
    with get_db() as conn:
        cur = get_cursor(conn)
        cur.execute(
            "SELECT * FROM skill_main_categories ORDER BY sort_order NULLS LAST, name"
        )
        return [r2d(r) for r in cur.fetchall()]


@router.post("/skill-main-categories")
def create_skill_main_category(data: dict, session=Depends(require_auth)):
    role = session.get("role")
    if role not in ("admin", "superadmin"):
        raise HTTPException(403, "Nur Admins dürfen Hauptkategorien anlegen")
    name = (data.get("name") or "").strip()
    if not name:
        raise HTTPException(400, "Name ist Pflichtfeld")
    slug = (data.get("slug") or "").strip() or _slugify_skill_label(name)
    with get_db() as conn:
        cur = get_cursor(conn)
        try:
            cur.execute(
                """
                INSERT INTO skill_main_categories (name, slug, description, sort_order)
                VALUES (%s, %s, %s, %s)
                RETURNING id
                """,
                (
                    name,
                    slug,
                    data.get("description"),
                    data.get("sort_order", 99),
                ),
            )
            mid = cur.fetchone()["id"]
        except Exception as e:
            if "unique" in str(e).lower() or "duplicate" in str(e).lower():
                raise HTTPException(409, "Name oder Slug bereits vergeben") from e
            raise
        cur.execute("SELECT * FROM skill_main_categories WHERE id = %s", (mid,))
        return r2d(cur.fetchone())


@router.put("/skill-main-categories/{main_id}")
def update_skill_main_category(main_id: int, data: dict, session=Depends(require_auth)):
    role = session.get("role")
    if role not in ("admin", "superadmin"):
        raise HTTPException(403, "Nur Admins dürfen Hauptkategorien bearbeiten")
    with get_db() as conn:
        cur = get_cursor(conn)
        cur.execute("SELECT id FROM skill_main_categories WHERE id = %s", (main_id,))
        if not cur.fetchone():
            raise HTTPException(404, "Hauptkategorie nicht gefunden")
        sets = []
        vals = []
        for key in ("name", "slug", "description", "sort_order"):
            if key in data:
                sets.append(f"{key} = %s")
                vals.append(data[key])
        if not sets:
            cur.execute("SELECT * FROM skill_main_categories WHERE id = %s", (main_id,))
            return r2d(cur.fetchone())
        sets.append("updated_at = NOW()")
        vals.append(main_id)
        try:
            cur.execute(
                f"UPDATE skill_main_categories SET {', '.join(sets)} WHERE id = %s",
                tuple(vals),
            )
        except Exception as e:
            if "unique" in str(e).lower():
                raise HTTPException(409, "Name oder Slug bereits vergeben") from e
            raise
        cur.execute("SELECT * FROM skill_main_categories WHERE id = %s", (main_id,))
        return r2d(cur.fetchone())


@router.delete("/skill-main-categories/{main_id}")
def delete_skill_main_category(main_id: int, session=Depends(require_auth)):
    role = session.get("role")
    if role != "superadmin":
        raise HTTPException(403, "Nur Superadmins dürfen Hauptkategorien löschen")
    with get_db() as conn:
        cur = get_cursor(conn)
        cur.execute(
            "SELECT COUNT(*) AS c FROM skill_categories WHERE main_category_id = %s",
            (main_id,),
        )
        if (cur.fetchone() or {}).get("c", 0) > 0:
            raise HTTPException(
                409,
                "Hauptkategorie hat noch Unterkategorien. Bitte zuerst verschieben oder löschen.",
            )
        cur.execute(
            "SELECT COUNT(*) AS c FROM skills WHERE main_category_id = %s",
            (main_id,),
        )
        if (cur.fetchone() or {}).get("c", 0) > 0:
            raise HTTPException(
                409,
                "Hauptkategorie ist noch Fähigkeiten zugeordnet.",
            )
        cur.execute(
            "DELETE FROM skill_main_categories WHERE id = %s RETURNING id",
            (main_id,),
        )
        if not cur.fetchone():
            raise HTTPException(404, "Hauptkategorie nicht gefunden")
    return {"ok": True}


# ════════════════════════════════════════════════════════════════════════
# FOCUS AREAS
# ════════════════════════════════════════════════════════════════════════

@router.get("/focus-areas")
def list_focus_areas(
    status: Optional[str] = Query(default='active'),
    session=Depends(require_auth)
):
    """List all focus areas (public for authenticated users)."""
    with get_db() as conn:
        cur = get_cursor(conn)

        query = "SELECT * FROM focus_areas"
        params = []

        frag, extra = _sql_active_status("status", status)
        if frag:
            query += " WHERE" + frag
            params.extend(extra)

        query += " ORDER BY sort_order, name"

        cur.execute(query, params)
        rows = cur.fetchall()
        return [r2d(r) for r in rows]


@router.post("/focus-areas")
def create_focus_area(data: dict, session=Depends(require_auth)):
    """Create new focus area (admin only)."""
    role = session.get('role')
    if role not in ['admin', 'superadmin']:
        raise HTTPException(403, "Nur Admins dürfen Fokusbereiche erstellen")

    name = data.get('name')
    if not name:
        raise HTTPException(400, "Name ist Pflichtfeld")

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

        cur.execute("""
            INSERT INTO focus_areas (name, abbreviation, description, color, icon, sort_order, status)
            VALUES (%s, %s, %s, %s, %s, %s, %s)
            RETURNING id
        """, (
            name,
            data.get('abbreviation'),
            data.get('description'),
            data.get('color'),
            data.get('icon'),
            data.get('sort_order', 99),
            data.get('status', 'active')
        ))

        focus_area_id = cur.fetchone()['id']
        conn.commit()

        cur.execute("SELECT * FROM focus_areas WHERE id = %s", (focus_area_id,))
        return r2d(cur.fetchone())


@router.put("/focus-areas/{focus_area_id}")
def update_focus_area(focus_area_id: int, data: dict, session=Depends(require_auth)):
    """Update focus area (admin only)."""
    role = session.get('role')
    if role not in ['admin', 'superadmin']:
        raise HTTPException(403, "Nur Admins dürfen Fokusbereiche bearbeiten")

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

        cur.execute("""
            UPDATE focus_areas SET
                name = %s,
                abbreviation = %s,
                description = %s,
                color = %s,
                icon = %s,
                sort_order = %s,
                status = %s,
                updated_at = NOW()
            WHERE id = %s
        """, (
            data.get('name'),
            data.get('abbreviation'),
            data.get('description'),
            data.get('color'),
            data.get('icon'),
            data.get('sort_order'),
            data.get('status'),
            focus_area_id
        ))

        conn.commit()

        cur.execute("SELECT * FROM focus_areas WHERE id = %s", (focus_area_id,))
        return r2d(cur.fetchone())


@router.delete("/focus-areas/{focus_area_id}")
def delete_focus_area(focus_area_id: int, session=Depends(require_auth)):
    """Delete focus area (superadmin only)."""
    role = session.get('role')
    if role != 'superadmin':
        raise HTTPException(403, "Nur Superadmins dürfen Fokusbereiche löschen")

    with get_db() as conn:
        cur = get_cursor(conn)
        cur.execute("DELETE FROM focus_areas WHERE id = %s", (focus_area_id,))
        conn.commit()

    return {"ok": True}


# ════════════════════════════════════════════════════════════════════════
# TRAINING STYLES
# ════════════════════════════════════════════════════════════════════════

@router.get("/training-styles")
def list_training_styles(
    status: Optional[str] = Query(default='active'),
    session=Depends(require_auth)
):
    """List all training styles."""
    with get_db() as conn:
        cur = get_cursor(conn)

        query = """
            SELECT ts.*, ps.name as parent_style_name, fa.name as focus_area_name, fa.icon as focus_area_icon
            FROM style_directions ts
            LEFT JOIN style_directions ps ON ts.parent_style_id = ps.id
            LEFT JOIN focus_areas fa ON ts.focus_area_id = fa.id
        """
        params = []

        frag, extra = _sql_active_status("ts.status", status)
        if frag:
            query += " WHERE" + frag
            params.extend(extra)

        query += " ORDER BY ts.sort_order, ts.name"

        cur.execute(query, params)
        rows = cur.fetchall()
        return [r2d(r) for r in rows]


@router.post("/training-styles")
def create_training_style(data: dict, session=Depends(require_auth)):
    """Create new training style (admin only)."""
    role = session.get('role')
    if role not in ['admin', 'superadmin']:
        raise HTTPException(403, "Nur Admins dürfen Trainingsstile erstellen")

    name = data.get('name')
    if not name:
        raise HTTPException(400, "Name ist Pflichtfeld")

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

        cur.execute("""
            INSERT INTO style_directions (name, abbreviation, description, focus_area_id, parent_style_id, sort_order, status)
            VALUES (%s, %s, %s, %s, %s, %s, %s)
            RETURNING id
        """, (
            name,
            data.get('abbreviation'),
            data.get('description'),
            data.get('focus_area_id'),
            data.get('parent_style_id'),
            data.get('sort_order', 99),
            data.get('status', 'active')
        ))

        style_id = cur.fetchone()['id']
        conn.commit()

        cur.execute("""
            SELECT ts.*, ps.name as parent_style_name, fa.name as focus_area_name, fa.icon as focus_area_icon
            FROM style_directions ts
            LEFT JOIN style_directions ps ON ts.parent_style_id = ps.id
            LEFT JOIN focus_areas fa ON ts.focus_area_id = fa.id
            WHERE ts.id = %s
        """, (style_id,))
        return r2d(cur.fetchone())


@router.put("/training-styles/{style_id}")
def update_training_style(style_id: int, data: dict, session=Depends(require_auth)):
    """Update training style (admin only)."""
    role = session.get('role')
    if role not in ['admin', 'superadmin']:
        raise HTTPException(403, "Nur Admins dürfen Trainingsstile bearbeiten")

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

        cur.execute("""
            UPDATE style_directions SET
                name = %s,
                abbreviation = %s,
                description = %s,
                focus_area_id = %s,
                parent_style_id = %s,
                sort_order = %s,
                status = %s,
                updated_at = NOW()
            WHERE id = %s
        """, (
            data.get('name'),
            data.get('abbreviation'),
            data.get('description'),
            data.get('focus_area_id'),
            data.get('parent_style_id'),
            data.get('sort_order'),
            data.get('status'),
            style_id
        ))

        conn.commit()

        cur.execute("""
            SELECT ts.*, ps.name as parent_style_name, fa.name as focus_area_name, fa.icon as focus_area_icon
            FROM style_directions ts
            LEFT JOIN style_directions ps ON ts.parent_style_id = ps.id
            LEFT JOIN focus_areas fa ON ts.focus_area_id = fa.id
            WHERE ts.id = %s
        """, (style_id,))
        return r2d(cur.fetchone())


@router.delete("/training-styles/{style_id}")
def delete_training_style(style_id: int, session=Depends(require_auth)):
    """Delete training style (superadmin only)."""
    role = session.get('role')
    if role != 'superadmin':
        raise HTTPException(403, "Nur Superadmins dürfen Trainingsstile löschen")

    with get_db() as conn:
        cur = get_cursor(conn)
        cur.execute("DELETE FROM style_directions WHERE id = %s", (style_id,))
        conn.commit()

    return {"ok": True}


# ════════════════════════════════════════════════════════════════════════
# TRAINING CHARACTERS
# ════════════════════════════════════════════════════════════════════════

@router.get("/training-characters")
def list_training_characters(
    status: Optional[str] = Query(default='active'),
    session=Depends(require_auth)
):
    """List all training characters."""
    with get_db() as conn:
        cur = get_cursor(conn)

        query = "SELECT * FROM training_characters"
        params = []

        if status:
            query += " WHERE status = %s"
            params.append(status)

        query += " ORDER BY sort_order, name"

        cur.execute(query, params)
        rows = cur.fetchall()
        return [r2d(r) for r in rows]


@router.post("/training-characters")
def create_training_character(data: dict, session=Depends(require_auth)):
    """Create new training character (admin only)."""
    role = session.get('role')
    if role not in ['admin', 'superadmin']:
        raise HTTPException(403, "Nur Admins dürfen Trainingscharaktere erstellen")

    name = data.get('name')
    if not name:
        raise HTTPException(400, "Name ist Pflichtfeld")

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

        cur.execute("""
            INSERT INTO training_characters (name, description, sort_order, status)
            VALUES (%s, %s, %s, %s)
            RETURNING id
        """, (
            name,
            data.get('description'),
            data.get('sort_order', 99),
            data.get('status', 'active')
        ))

        char_id = cur.fetchone()['id']
        conn.commit()

        cur.execute("SELECT * FROM training_characters WHERE id = %s", (char_id,))
        return r2d(cur.fetchone())


@router.put("/training-characters/{char_id}")
def update_training_character(char_id: int, data: dict, session=Depends(require_auth)):
    """Update training character (admin only)."""
    role = session.get('role')
    if role not in ['admin', 'superadmin']:
        raise HTTPException(403, "Nur Admins dürfen Trainingscharaktere bearbeiten")

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

        cur.execute("""
            UPDATE training_characters SET
                name = %s,
                description = %s,
                sort_order = %s,
                status = %s,
                updated_at = NOW()
            WHERE id = %s
        """, (
            data.get('name'),
            data.get('description'),
            data.get('sort_order'),
            data.get('status'),
            char_id
        ))

        conn.commit()

        cur.execute("SELECT * FROM training_characters WHERE id = %s", (char_id,))
        return r2d(cur.fetchone())


@router.delete("/training-characters/{char_id}")
def delete_training_character(char_id: int, session=Depends(require_auth)):
    """Delete training character (superadmin only)."""
    role = session.get('role')
    if role != 'superadmin':
        raise HTTPException(403, "Nur Superadmins dürfen Trainingscharaktere löschen")

    with get_db() as conn:
        cur = get_cursor(conn)
        cur.execute("DELETE FROM training_characters WHERE id = %s", (char_id,))
        conn.commit()

    return {"ok": True}


# ════════════════════════════════════════════════════════════════════════
# TRAINING TYPES (Breitensport, Leistungssport, etc.)
# ════════════════════════════════════════════════════════════════════════

@router.get("/training-types")
def list_training_types(
    status: Optional[str] = Query(default='active'),
    focus_area_id: Optional[int] = Query(default=None),
    session=Depends(require_auth)
):
    """List all training types.

    Optional filter by focus_area_id for context-specific types.
    """
    with get_db() as conn:
        cur = get_cursor(conn)

        query = """
            SELECT tt.*, fa.name as focus_area_name, fa.icon as focus_area_icon
            FROM training_types tt
            LEFT JOIN focus_areas fa ON tt.focus_area_id = fa.id
        """
        params = []
        where = []

        frag, extra = _sql_active_status("tt.status", status)
        if frag:
            where.append(frag.strip())
            params.extend(extra)

        if focus_area_id is not None:
            where.append("tt.focus_area_id = %s")
            params.append(focus_area_id)

        if where:
            query += " WHERE " + " AND ".join(where)

        query += " ORDER BY fa.sort_order, tt.sort_order, tt.name"

        cur.execute(query, params)
        rows = cur.fetchall()
        return [r2d(r) for r in rows]


@router.post("/training-types")
def create_training_type(data: dict, session=Depends(require_auth)):
    """Create new training type (admin only)."""
    role = session.get('role')
    if role not in ['admin', 'superadmin']:
        raise HTTPException(403, "Nur Admins dürfen Trainingsstile erstellen")

    name = data.get('name')
    if not name:
        raise HTTPException(400, "Name ist Pflichtfeld")

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

        cur.execute("""
            INSERT INTO training_types (name, abbreviation, description, focus_area_id, sort_order, status)
            VALUES (%s, %s, %s, %s, %s, %s)
            RETURNING id
        """, (
            name,
            data.get('abbreviation'),
            data.get('description'),
            data.get('focus_area_id'),
            data.get('sort_order', 99),
            data.get('status', 'active')
        ))

        type_id = cur.fetchone()['id']
        conn.commit()

        cur.execute("""
            SELECT tt.*, fa.name as focus_area_name, fa.icon as focus_area_icon
            FROM training_types tt
            LEFT JOIN focus_areas fa ON tt.focus_area_id = fa.id
            WHERE tt.id = %s
        """, (type_id,))
        return r2d(cur.fetchone())


@router.put("/training-types/{type_id}")
def update_training_type(type_id: int, data: dict, session=Depends(require_auth)):
    """Update training type (admin only)."""
    role = session.get('role')
    if role not in ['admin', 'superadmin']:
        raise HTTPException(403, "Nur Admins dürfen Trainingsstile bearbeiten")

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

        cur.execute("""
            UPDATE training_types SET
                name = %s,
                abbreviation = %s,
                description = %s,
                focus_area_id = %s,
                sort_order = %s,
                status = %s,
                updated_at = NOW()
            WHERE id = %s
        """, (
            data.get('name'),
            data.get('abbreviation'),
            data.get('description'),
            data.get('focus_area_id'),
            data.get('sort_order'),
            data.get('status'),
            type_id
        ))

        conn.commit()

        cur.execute("""
            SELECT tt.*, fa.name as focus_area_name, fa.icon as focus_area_icon
            FROM training_types tt
            LEFT JOIN focus_areas fa ON tt.focus_area_id = fa.id
            WHERE tt.id = %s
        """, (type_id,))
        return r2d(cur.fetchone())


@router.delete("/training-types/{type_id}")
def delete_training_type(type_id: int, session=Depends(require_auth)):
    """Delete training type (superadmin only)."""
    role = session.get('role')
    if role != 'superadmin':
        raise HTTPException(403, "Nur Superadmins dürfen Trainingsstile löschen")

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

        # Check if assigned to exercises
        cur.execute("""
            SELECT COUNT(*) as count
            FROM exercise_training_types
            WHERE training_type_id = %s
        """, (type_id,))
        ex_count = cur.fetchone()['count']

        if ex_count > 0:
            raise HTTPException(
                409,
                f"Trainingsstil kann nicht gelöscht werden: {ex_count} Übung(en) zugeordnet. "
                "Bitte zuerst alle Zuordnungen entfernen."
            )

        cur.execute("DELETE FROM training_types WHERE id = %s", (type_id,))
        conn.commit()

    return {"ok": True}


# ════════════════════════════════════════════════════════════════════════
# SKILL CATEGORIES
# ════════════════════════════════════════════════════════════════════════

@router.get("/skill-categories")
def list_skill_categories(
    status: Optional[str] = Query(default='active'),
    main_category_id: Optional[int] = Query(default=None),
    session=Depends(require_auth),
):
    """List all skill categories (mit Hauptkategorie)."""
    with get_db() as conn:
        cur = get_cursor(conn)

        query = """
            SELECT sc.*, pc.name as parent_category_name,
                   mc.id as main_category_ref_id, mc.name as main_category_name,
                   mc.slug as main_category_slug, mc.sort_order as main_category_sort
            FROM skill_categories sc
            LEFT JOIN skill_categories pc ON sc.parent_category_id = pc.id
            LEFT JOIN skill_main_categories mc ON sc.main_category_id = mc.id
        """
        params = []
        where = []

        if status:
            where.append("sc.status = %s")
            params.append(status)
        if main_category_id is not None:
            where.append("sc.main_category_id = %s")
            params.append(main_category_id)

        if where:
            query += " WHERE " + " AND ".join(where)

        query += " ORDER BY mc.sort_order NULLS LAST, sc.sort_order NULLS LAST, sc.name"

        cur.execute(query, params)
        rows = cur.fetchall()
        return [r2d(r) for r in rows]


@router.post("/skill-categories")
def create_skill_category(data: dict, session=Depends(require_auth)):
    """Create new skill category (admin only)."""
    role = session.get('role')
    if role not in ['admin', 'superadmin']:
        raise HTTPException(403, "Nur Admins dürfen Fähigkeitsbereiche erstellen")

    name = (data.get("name") or "").strip()
    if not name:
        raise HTTPException(400, "Name ist Pflichtfeld")

    main_category_id = data.get("main_category_id")
    if main_category_id is not None:
        main_category_id = int(main_category_id)

    slug = (data.get("slug") or "").strip() or _slugify_skill_label(name)

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

        try:
            cur.execute(
                """
                INSERT INTO skill_categories (
                    name, slug, description, parent_category_id, main_category_id,
                    sort_order, status
                )
                VALUES (%s, %s, %s, %s, %s, %s, %s)
                RETURNING id
                """,
                (
                    name,
                    slug,
                    data.get("description"),
                    data.get("parent_category_id"),
                    main_category_id,
                    data.get("sort_order", 99),
                    data.get("status", "active"),
                ),
            )
            cat_id = cur.fetchone()["id"]
        except Exception as e:
            if "unique" in str(e).lower() or "duplicate" in str(e).lower():
                raise HTTPException(409, "Name oder Slug schon vergeben") from e
            raise

        cur.execute(
            """
            SELECT sc.*, pc.name as parent_category_name,
                   mc.name as main_category_name, mc.slug as main_category_slug
            FROM skill_categories sc
            LEFT JOIN skill_categories pc ON sc.parent_category_id = pc.id
            LEFT JOIN skill_main_categories mc ON sc.main_category_id = mc.id
            WHERE sc.id = %s
            """,
            (cat_id,),
        )
        return r2d(cur.fetchone())


@router.put("/skill-categories/{cat_id}")
def update_skill_category(cat_id: int, data: dict, session=Depends(require_auth)):
    """Update skill category (admin only)."""
    role = session.get('role')
    if role not in ['admin', 'superadmin']:
        raise HTTPException(403, "Nur Admins dürfen Fähigkeitsbereiche bearbeiten")

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

        cur.execute("SELECT id FROM skill_categories WHERE id = %s", (cat_id,))
        if not cur.fetchone():
            raise HTTPException(404, "Kategorie nicht gefunden")

        sets: list = []
        vals: list = []
        for key in (
            "name",
            "slug",
            "description",
            "parent_category_id",
            "main_category_id",
            "sort_order",
            "status",
        ):
            if key in data:
                sets.append(f"{key} = %s")
                vals.append(data[key])
        if sets:
            sets.append("updated_at = NOW()")
            vals.append(cat_id)
            try:
                cur.execute(
                    f"UPDATE skill_categories SET {', '.join(sets)} WHERE id = %s",
                    tuple(vals),
                )
            except Exception as e:
                if "unique" in str(e).lower():
                    raise HTTPException(409, "Name oder Slug schon vergeben") from e
                raise

        cur.execute(
            "SELECT main_category_id FROM skill_categories WHERE id = %s",
            (cat_id,),
        )
        effective_main = cur.fetchone()["main_category_id"]
        if effective_main is not None:
            cur.execute(
                "UPDATE skills SET main_category_id = %s WHERE category_id = %s",
                (effective_main, cat_id),
            )

        cur.execute(
            """
            SELECT sc.*, pc.name as parent_category_name,
                   mc.name as main_category_name, mc.slug as main_category_slug
            FROM skill_categories sc
            LEFT JOIN skill_categories pc ON sc.parent_category_id = pc.id
            LEFT JOIN skill_main_categories mc ON sc.main_category_id = mc.id
            WHERE sc.id = %s
            """,
            (cat_id,),
        )
        return r2d(cur.fetchone())


@router.delete("/skill-categories/{cat_id}")
def delete_skill_category(cat_id: int, session=Depends(require_auth)):
    """Delete skill category (superadmin only)."""
    role = session.get('role')
    if role != 'superadmin':
        raise HTTPException(403, "Nur Superadmins dürfen Fähigkeitsbereiche löschen")

    with get_db() as conn:
        cur = get_cursor(conn)
        cur.execute("SELECT COUNT(*) AS c FROM skills WHERE category_id = %s", (cat_id,))
        if (cur.fetchone() or {}).get("c", 0) > 0:
            raise HTTPException(
                409,
                "Kategorie noch Fähigkeiten zugeordnet – bitte zuerst verschieben oder löschen.",
            )
        cur.execute(
            "SELECT COUNT(*) AS c FROM skill_categories WHERE parent_category_id = %s",
            (cat_id,),
        )
        if (cur.fetchone() or {}).get("c", 0) > 0:
            raise HTTPException(409, "Kategorie hat Unterkategorien.")
        cur.execute("DELETE FROM skill_categories WHERE id = %s RETURNING id", (cat_id,))
        if not cur.fetchone():
            raise HTTPException(404, "Kategorie nicht gefunden")

    return {"ok": True}


# ════════════════════════════════════════════════════════════════════════
# TRAINER FOCUS AREAS (Welcher Trainer arbeitet in welchen Fokusbereichen?)
# ════════════════════════════════════════════════════════════════════════

@router.get("/trainer-focus-areas")
def list_trainer_focus_areas(
    profile_id: Optional[int] = Query(default=None),
    session=Depends(require_auth)
):
    """List trainer focus area assignments."""
    with get_db() as conn:
        cur = get_cursor(conn)

        query = """
            SELECT tfa.*, fa.name as focus_area_name, fa.abbreviation as focus_area_abbr,
                   p.name as trainer_name
            FROM trainer_focus_areas tfa
            LEFT JOIN focus_areas fa ON tfa.focus_area_id = fa.id
            LEFT JOIN profiles p ON tfa.profile_id = p.id
        """
        params = []

        # If not admin, only show own focus areas
        role = session.get('role')
        current_profile_id = session['profile_id']

        if role not in ['admin', 'superadmin']:
            query += " WHERE tfa.profile_id = %s"
            params.append(current_profile_id)
        elif profile_id:
            query += " WHERE tfa.profile_id = %s"
            params.append(profile_id)

        query += " ORDER BY fa.name"

        cur.execute(query, params)
        rows = cur.fetchall()
        return [r2d(r) for r in rows]


@router.post("/trainer-focus-areas")
def assign_trainer_focus_area(data: dict, session=Depends(require_auth)):
    """Assign focus area to trainer (admin only)."""
    role = session.get('role')
    if role not in ['admin', 'superadmin']:
        raise HTTPException(403, "Nur Admins dürfen Fokusbereiche zuweisen")

    profile_id = data.get('profile_id')
    focus_area_id = data.get('focus_area_id')

    if not profile_id or not focus_area_id:
        raise HTTPException(400, "profile_id und focus_area_id sind Pflichtfelder")

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

        cur.execute("""
            INSERT INTO trainer_focus_areas (profile_id, focus_area_id, is_primary)
            VALUES (%s, %s, %s)
            ON CONFLICT (profile_id, focus_area_id) DO UPDATE
            SET is_primary = EXCLUDED.is_primary
            RETURNING id
        """, (
            profile_id,
            focus_area_id,
            data.get('is_primary', False)
        ))

        tfa_id = cur.fetchone()['id']
        conn.commit()

        cur.execute("""
            SELECT tfa.*, fa.name as focus_area_name, p.name as trainer_name
            FROM trainer_focus_areas tfa
            LEFT JOIN focus_areas fa ON tfa.focus_area_id = fa.id
            LEFT JOIN profiles p ON tfa.profile_id = p.id
            WHERE tfa.id = %s
        """, (tfa_id,))
        return r2d(cur.fetchone())


@router.delete("/trainer-focus-areas/{tfa_id}")
def delete_trainer_focus_area(tfa_id: int, session=Depends(require_auth)):
    """Remove focus area assignment (admin only)."""
    role = session.get('role')
    if role not in ['admin', 'superadmin']:
        raise HTTPException(403, "Nur Admins dürfen Zuweisungen entfernen")

    with get_db() as conn:
        cur = get_cursor(conn)
        cur.execute("DELETE FROM trainer_focus_areas WHERE id = %s", (tfa_id,))
        conn.commit()

    return {"ok": True}


# ════════════════════════════════════════════════════════════════════════
# TARGET GROUPS (Zielgruppen)
# ════════════════════════════════════════════════════════════════════════

@router.get("/target-groups")
def list_target_groups(
    status: Optional[str] = Query(default='active'),
    session=Depends(require_auth)
):
    """List all target groups (global catalog - independent of styles)."""
    with get_db() as conn:
        cur = get_cursor(conn)

        query = "SELECT * FROM target_groups"
        params = []
        where = []

        frag, extra = _sql_active_status("status", status)
        if frag:
            where.append(frag.strip())
            params.extend(extra)

        if where:
            query += " WHERE " + " AND ".join(where)

        query += " ORDER BY sort_order, name"

        cur.execute(query, params)
        rows = cur.fetchall()
        return [r2d(r) for r in rows]


@router.post("/target-groups")
def create_target_group(data: dict, session=Depends(require_auth)):
    """Create new target group (admin only, global catalog)."""
    role = session.get('role')
    if role not in ['admin', 'superadmin']:
        raise HTTPException(403, "Nur Admins dürfen Zielgruppen erstellen")

    name = data.get('name')
    if not name:
        raise HTTPException(400, "Name ist Pflichtfeld")

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

        cur.execute("""
            INSERT INTO target_groups (
                name, description, min_age, max_age, sort_order, status
            )
            VALUES (%s, %s, %s, %s, %s, %s)
            RETURNING id
        """, (
            name,
            data.get('description'),
            data.get('min_age'),
            data.get('max_age'),
            data.get('sort_order', 99),
            data.get('status', 'active')
        ))

        target_group_id = cur.fetchone()['id']
        conn.commit()

        cur.execute("SELECT * FROM target_groups WHERE id = %s", (target_group_id,))
        return r2d(cur.fetchone())


@router.put("/target-groups/{target_group_id}")
def update_target_group(target_group_id: int, data: dict, session=Depends(require_auth)):
    """Update target group (admin only)."""
    role = session.get('role')
    if role not in ['admin', 'superadmin']:
        raise HTTPException(403, "Nur Admins dürfen Zielgruppen bearbeiten")

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

        cur.execute("""
            UPDATE target_groups SET
                name = %s,
                description = %s,
                min_age = %s,
                max_age = %s,
                sort_order = %s,
                status = %s,
                updated_at = NOW()
            WHERE id = %s
        """, (
            data.get('name'),
            data.get('description'),
            data.get('min_age'),
            data.get('max_age'),
            data.get('sort_order'),
            data.get('status'),
            target_group_id
        ))

        conn.commit()

        cur.execute("SELECT * FROM target_groups WHERE id = %s", (target_group_id,))
        return r2d(cur.fetchone())


@router.delete("/target-groups/{target_group_id}")
def delete_target_group(target_group_id: int, session=Depends(require_auth)):
    """Delete target group (superadmin only).

    Fails if target group is assigned to any exercises or training styles.
    """
    role = session.get('role')
    if role != 'superadmin':
        raise HTTPException(403, "Nur Superadmins dürfen Zielgruppen löschen")

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

        # Check if assigned to exercises
        cur.execute("""
            SELECT COUNT(*) as count
            FROM exercise_target_groups
            WHERE target_group_id = %s
        """, (target_group_id,))
        ex_count = cur.fetchone()['count']

        # Check if assigned to training styles (M:N)
        cur.execute("""
            SELECT COUNT(*) as count
            FROM style_direction_target_groups
            WHERE target_group_id = %s
        """, (target_group_id,))
        style_count = cur.fetchone()['count']

        if ex_count > 0 or style_count > 0:
            raise HTTPException(
                409,
                f"Zielgruppe kann nicht gelöscht werden: {ex_count} Übung(en), {style_count} Stil(e) zugeordnet. "
                "Bitte zuerst alle Zuordnungen entfernen."
            )

        cur.execute("DELETE FROM target_groups WHERE id = %s", (target_group_id,))
        conn.commit()

    return {"ok": True}


# ════════════════════════════════════════════════════════════════════════
# TRAINING STYLE → TARGET GROUPS (M:N Assignments)
# ════════════════════════════════════════════════════════════════════════

@router.get("/training-style-target-groups")
def list_training_style_target_groups(
    style_direction_id: Optional[int] = Query(default=None),
    target_group_id: Optional[int] = Query(default=None),
    is_primary: Optional[bool] = Query(default=None),
    session=Depends(require_auth)
):
    """List M:N assignments between style directions and target groups.

    Returns enriched data with style_direction_name, target_group_name,
    focus_area_name for easy display in Matrix UI.
    """
    with get_db() as conn:
        cur = get_cursor(conn)

        query = """
            SELECT
                sdtg.id,
                sdtg.style_direction_id,
                sdtg.target_group_id,
                sdtg.is_primary,
                sdtg.created_at,
                sd.name as style_direction_name,
                sd.focus_area_id,
                fa.name as focus_area_name,
                tg.name as target_group_name,
                tg.min_age,
                tg.max_age
            FROM style_direction_target_groups sdtg
            LEFT JOIN style_directions sd ON sdtg.style_direction_id = sd.id
            LEFT JOIN focus_areas fa ON sd.focus_area_id = fa.id
            LEFT JOIN target_groups tg ON sdtg.target_group_id = tg.id
        """
        params = []
        where = []

        if style_direction_id is not None:
            where.append("sdtg.style_direction_id = %s")
            params.append(style_direction_id)

        if target_group_id is not None:
            where.append("sdtg.target_group_id = %s")
            params.append(target_group_id)

        if is_primary is not None:
            where.append("sdtg.is_primary = %s")
            params.append(is_primary)

        if where:
            query += " WHERE " + " AND ".join(where)

        query += " ORDER BY fa.sort_order, sd.sort_order, tg.sort_order"

        cur.execute(query, params)
        rows = cur.fetchall()
        return [r2d(r) for r in rows]


@router.post("/training-style-target-groups")
def create_training_style_target_group(data: dict, session=Depends(require_auth)):
    """Assign target group to style direction (admin only).

    Uses UPSERT logic - if assignment exists, updates is_primary flag.
    """
    role = session.get('role')
    if role not in ['admin', 'superadmin']:
        raise HTTPException(403, "Nur Admins dürfen Zuordnungen erstellen")

    style_direction_id = data.get('style_direction_id')
    target_group_id = data.get('target_group_id')

    if not style_direction_id or not target_group_id:
        raise HTTPException(400, "style_direction_id und target_group_id sind Pflichtfelder")

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

        # Upsert logic
        cur.execute("""
            INSERT INTO style_direction_target_groups
                (style_direction_id, target_group_id, is_primary)
            VALUES (%s, %s, %s)
            ON CONFLICT (style_direction_id, target_group_id)
            DO UPDATE SET is_primary = EXCLUDED.is_primary
            RETURNING id
        """, (
            style_direction_id,
            target_group_id,
            data.get('is_primary', False)
        ))

        assignment_id = cur.fetchone()['id']
        conn.commit()

        # Return enriched record
        cur.execute("""
            SELECT
                sdtg.id,
                sdtg.style_direction_id,
                sdtg.target_group_id,
                sdtg.is_primary,
                sdtg.created_at,
                sd.name as style_direction_name,
                sd.focus_area_id,
                fa.name as focus_area_name,
                tg.name as target_group_name
            FROM style_direction_target_groups sdtg
            LEFT JOIN style_directions sd ON sdtg.style_direction_id = sd.id
            LEFT JOIN focus_areas fa ON sd.focus_area_id = fa.id
            LEFT JOIN target_groups tg ON sdtg.target_group_id = tg.id
            WHERE sdtg.id = %s
        """, (assignment_id,))

        return r2d(cur.fetchone())


@router.put("/training-style-target-groups/{assignment_id}")
def update_training_style_target_group(
    assignment_id: int,
    data: dict,
    session=Depends(require_auth)
):
    """Update M:N assignment (admin only).

    Currently only supports updating is_primary flag.
    """
    role = session.get('role')
    if role not in ['admin', 'superadmin']:
        raise HTTPException(403, "Nur Admins dürfen Zuordnungen bearbeiten")

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

        cur.execute("""
            UPDATE style_direction_target_groups
            SET is_primary = %s
            WHERE id = %s
        """, (
            data.get('is_primary', False),
            assignment_id
        ))

        conn.commit()

        # Return enriched record
        cur.execute("""
            SELECT
                sdtg.id,
                sdtg.style_direction_id,
                sdtg.target_group_id,
                sdtg.is_primary,
                sdtg.created_at,
                sd.name as style_direction_name,
                sd.focus_area_id,
                fa.name as focus_area_name,
                tg.name as target_group_name
            FROM style_direction_target_groups sdtg
            LEFT JOIN style_directions sd ON sdtg.style_direction_id = sd.id
            LEFT JOIN focus_areas fa ON sd.focus_area_id = fa.id
            LEFT JOIN target_groups tg ON sdtg.target_group_id = tg.id
            WHERE sdtg.id = %s
        """, (assignment_id,))

        return r2d(cur.fetchone())


@router.delete("/training-style-target-groups/{assignment_id}")
def delete_training_style_target_group(assignment_id: int, session=Depends(require_auth)):
    """Remove M:N assignment (admin only)."""
    role = session.get('role')
    if role not in ['admin', 'superadmin']:
        raise HTTPException(403, "Nur Admins dürfen Zuordnungen löschen")

    with get_db() as conn:
        cur = get_cursor(conn)
        cur.execute("DELETE FROM style_direction_target_groups WHERE id = %s", (assignment_id,))
        conn.commit()

    return {"ok": True}


@router.get("/training-styles/hierarchy")
def get_training_styles_hierarchy(
    status: Optional[str] = Query(default='active'),
    session=Depends(require_auth)
):
    """Get hierarchical structure: Focus Areas → Style Directions → Target Groups.

    Returns nested structure for Tree-View rendering in Admin UI.
    """
    with get_db() as conn:
        cur = get_cursor(conn)

        # Get all focus areas
        fa_query = "SELECT * FROM focus_areas"
        fa_params = []

        if status:
            fa_query += " WHERE status = %s"
            fa_params.append(status)

        fa_query += " ORDER BY sort_order, name"

        cur.execute(fa_query, fa_params)
        focus_areas = [r2d(r) for r in cur.fetchall()]

        # For each focus area, get style directions with their target groups
        for fa in focus_areas:
            sd_query = """
                SELECT * FROM style_directions
                WHERE focus_area_id = %s
            """
            sd_params = [fa['id']]

            if status:
                sd_query += " AND status = %s"
                sd_params.append(status)

            sd_query += " ORDER BY sort_order, name"

            cur.execute(sd_query, sd_params)
            style_directions = [r2d(r) for r in cur.fetchall()]

            # For each style direction, get assigned target groups
            for sd in style_directions:
                cur.execute("""
                    SELECT
                        tg.id,
                        tg.name,
                        tg.description,
                        tg.min_age,
                        tg.max_age,
                        sdtg.is_primary,
                        sdtg.id as assignment_id
                    FROM style_direction_target_groups sdtg
                    LEFT JOIN target_groups tg ON sdtg.target_group_id = tg.id
                    WHERE sdtg.style_direction_id = %s
                    ORDER BY tg.sort_order, tg.name
                """, (sd['id'],))

                sd['target_groups'] = [r2d(r) for r in cur.fetchall()]

            fa['style_directions'] = style_directions

        return focus_areas


# ════════════════════════════════════════════════════════════════════════
# TRAINER CONTEXTS (Fokussierte Trainer-Ansichten)
# ════════════════════════════════════════════════════════════════════════

@router.get("/trainer-contexts")
def list_trainer_contexts(session=Depends(require_auth)):
    """List all trainer contexts for the current user.

    Returns enriched data with focus_area_name, style_direction_name, training_type_name.
    """
    profile_id = session['profile_id']

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

        cur.execute("""
            SELECT
                tc.*,
                fa.name as focus_area_name,
                fa.icon as focus_area_icon,
                sd.name as style_direction_name,
                tt.name as training_type_name
            FROM trainer_contexts tc
            LEFT JOIN focus_areas fa ON tc.focus_area_id = fa.id
            LEFT JOIN style_directions sd ON tc.style_direction_id = sd.id
            LEFT JOIN training_types tt ON tc.training_type_id = tt.id
            WHERE tc.profile_id = %s
            ORDER BY tc.sort_order, tc.name
        """, (profile_id,))

        rows = cur.fetchall()
        return [r2d(r) for r in rows]


@router.post("/trainer-contexts")
def create_trainer_context(data: dict, session=Depends(require_auth)):
    """Create new trainer context for the current user."""
    profile_id = session['profile_id']

    name = data.get('name')
    if not name:
        raise HTTPException(400, "Name ist Pflichtfeld")

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

        cur.execute("""
            INSERT INTO trainer_contexts (
                profile_id, name, focus_area_id, style_direction_id,
                training_type_id, is_style_independent, description, sort_order, is_active
            )
            VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)
            RETURNING id
        """, (
            profile_id,
            name,
            data.get('focus_area_id'),
            data.get('style_direction_id'),
            data.get('training_type_id'),
            data.get('is_style_independent', False),
            data.get('description'),
            data.get('sort_order', 99),
            data.get('is_active', True)
        ))

        context_id = cur.fetchone()['id']
        conn.commit()

        # Return enriched record
        cur.execute("""
            SELECT
                tc.*,
                fa.name as focus_area_name,
                sd.name as style_direction_name,
                tt.name as training_type_name
            FROM trainer_contexts tc
            LEFT JOIN focus_areas fa ON tc.focus_area_id = fa.id
            LEFT JOIN style_directions sd ON tc.style_direction_id = sd.id
            LEFT JOIN training_types tt ON tc.training_type_id = tt.id
            WHERE tc.id = %s
        """, (context_id,))

        return r2d(cur.fetchone())


@router.put("/trainer-contexts/{context_id}")
def update_trainer_context(context_id: int, data: dict, session=Depends(require_auth)):
    """Update trainer context (own contexts only)."""
    profile_id = session['profile_id']

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

        # Verify ownership
        cur.execute("SELECT profile_id FROM trainer_contexts WHERE id = %s", (context_id,))
        row = cur.fetchone()
        if not row:
            raise HTTPException(404, "Kontext nicht gefunden")
        if row['profile_id'] != profile_id:
            raise HTTPException(403, "Zugriff verweigert")

        cur.execute("""
            UPDATE trainer_contexts SET
                name = %s,
                focus_area_id = %s,
                style_direction_id = %s,
                training_type_id = %s,
                is_style_independent = %s,
                description = %s,
                sort_order = %s,
                is_active = %s,
                updated_at = NOW()
            WHERE id = %s
        """, (
            data.get('name'),
            data.get('focus_area_id'),
            data.get('style_direction_id'),
            data.get('training_type_id'),
            data.get('is_style_independent', False),
            data.get('description'),
            data.get('sort_order'),
            data.get('is_active', True),
            context_id
        ))

        conn.commit()

        # Return enriched record
        cur.execute("""
            SELECT
                tc.*,
                fa.name as focus_area_name,
                sd.name as style_direction_name,
                tt.name as training_type_name
            FROM trainer_contexts tc
            LEFT JOIN focus_areas fa ON tc.focus_area_id = fa.id
            LEFT JOIN style_directions sd ON tc.style_direction_id = sd.id
            LEFT JOIN training_types tt ON tc.training_type_id = tt.id
            WHERE tc.id = %s
        """, (context_id,))

        return r2d(cur.fetchone())


@router.delete("/trainer-contexts/{context_id}")
def delete_trainer_context(context_id: int, session=Depends(require_auth)):
    """Delete trainer context (own contexts only)."""
    profile_id = session['profile_id']

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

        # Verify ownership
        cur.execute("SELECT profile_id FROM trainer_contexts WHERE id = %s", (context_id,))
        row = cur.fetchone()
        if not row:
            raise HTTPException(404, "Kontext nicht gefunden")
        if row['profile_id'] != profile_id:
            raise HTTPException(403, "Zugriff verweigert")

        cur.execute("DELETE FROM trainer_contexts WHERE id = %s", (context_id,))
        conn.commit()

    return {"ok": True}


# ════════════════════════════════════════════════════════════════════════
# HIERARCHICAL CATALOG VIEW (Admin UI Tree)
# ════════════════════════════════════════════════════════════════════════

@router.get("/admin/hierarchy")
def get_admin_hierarchy(session=Depends(require_auth)):
    """
    Get complete hierarchical catalog structure for admin tree view.

    Returns:
    [
      {
        "id": 1,
        "name": "Karate",
        "icon": "🥋",
        "description": "...",
        "style_directions": [
          {
            "id": 10,
            "name": "Shotokan",
            "abbreviation": "SKA",
            "description": "...",
            "target_groups": [
              {"id": 100, "name": "Kinder", "is_primary": true}
            ]
          }
        ],
        "training_types": [
          {
            "id": 20,
            "name": "Breitensport",
            "abbreviation": "BS",
            "description": "..."
          }
        ]
      }
    ]
    """
    role = session.get('role')
    if role not in ['admin', 'superadmin']:
        raise HTTPException(403, "Nur Admins dürfen die Hierarchie abrufen")

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

        # 1. Get all focus areas
        cur.execute("""
            SELECT id, name, icon, description, sort_order, status
            FROM focus_areas
            WHERE status = 'active'
            ORDER BY sort_order, name
        """)
        focus_areas = [r2d(r) for r in cur.fetchall()]

        # 2. Get all style directions with their target group assignments
        cur.execute("""
            SELECT
                sd.id,
                sd.name,
                sd.abbreviation,
                sd.description,
                sd.focus_area_id,
                sd.sort_order,
                json_agg(
                    json_build_object(
                        'id', tg.id,
                        'name', tg.name,
                        'is_primary', sdtg.is_primary
                    ) ORDER BY tg.name
                ) FILTER (WHERE tg.id IS NOT NULL) as target_groups
            FROM style_directions sd
            LEFT JOIN style_direction_target_groups sdtg ON sd.id = sdtg.style_direction_id
            LEFT JOIN target_groups tg ON sdtg.target_group_id = tg.id
            WHERE sd.status = 'active'
            GROUP BY sd.id, sd.name, sd.abbreviation, sd.description, sd.focus_area_id, sd.sort_order
            ORDER BY sd.sort_order, sd.name
        """)
        style_directions = [r2d(r) for r in cur.fetchall()]

        # 3. Get all training types
        cur.execute("""
            SELECT id, name, abbreviation, description, focus_area_id, sort_order
            FROM training_types
            WHERE status = 'active'
            ORDER BY sort_order, name
        """)
        training_types = [r2d(r) for r in cur.fetchall()]

        # 4. Build hierarchy
        for fa in focus_areas:
            fa_id = fa['id']

            # Attach style directions
            fa['style_directions'] = [
                sd for sd in style_directions
                if sd['focus_area_id'] == fa_id
            ]

            # Attach training types
            fa['training_types'] = [
                tt for tt in training_types
                if tt['focus_area_id'] == fa_id
            ]

        return focus_areas