"""
Superadmin API: Pflege von ai_skill_retrieval_profiles (KI Skill-Katalog / exercise_ai).

Kein Vereinsbezug — require_auth + is_superadmin; kein TenantContext.
Siehe ACCESS_LAYER_ENDPOINT_AUDIT.md.
"""
from __future__ import annotations

import json
from typing import Any, Dict, Optional

from fastapi import APIRouter, Depends, HTTPException
from pydantic import BaseModel, Field, model_validator
from psycopg2.extras import Json

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

router = APIRouter(tags=["admin_ai_skill_retrieval"])


def _require_superadmin(session: dict) -> dict:
    role = (session.get("role") or "").strip().lower()
    if not is_superadmin(role):
        raise HTTPException(status_code=403, detail="Nur Superadmins")
    return session


def _table_ready(cur) -> bool:
    cur.execute("SELECT to_regclass(%s)::text AS t", ("public.ai_skill_retrieval_profiles",))
    row = cur.fetchone()
    if not row:
        return False
    val = row.get("t") if isinstance(row, dict) else row[0]
    return val is not None and str(val).strip() != ""


def _assert_table(cur) -> None:
    if not _table_ready(cur):
        raise HTTPException(
            status_code=503,
            detail="Tabelle ai_skill_retrieval_profiles fehlt — Migration 068 ausführen.",
        )


def _normalize_config(raw: Any) -> Dict[str, Any]:
    if raw is None:
        return {}
    if isinstance(raw, dict):
        return raw
    if isinstance(raw, str):
        try:
            parsed = json.loads(raw)
        except json.JSONDecodeError as e:
            raise HTTPException(status_code=400, detail=f"config: ungültiges JSON — {e}") from e
        if not isinstance(parsed, dict):
            raise HTTPException(status_code=400, detail="config muss ein JSON-Objekt sein.")
        return parsed
    raise HTTPException(status_code=400, detail="config muss ein Objekt sein.")


class AiRetrievalProfileCreate(BaseModel):
    name: str = Field(..., min_length=2, max_length=200)
    description: Optional[str] = Field("", max_length=4000)
    active: bool = True
    is_default: bool = False
    focus_area_id: Optional[int] = Field(None, ge=1)
    config: Dict[str, Any] = Field(default_factory=dict)

    @model_validator(mode="after")
    def default_vs_focus(self):
        if self.is_default and self.focus_area_id is not None:
            raise ValueError("Standardprofil darf keinen Fokusbereich (focus_area_id) haben.")
        if not self.is_default and self.focus_area_id is None:
            raise ValueError("Profil ohne Standard-Flag benötigt eine focus_area_id.")
        return self


class AiRetrievalProfileUpdate(BaseModel):
    name: Optional[str] = Field(None, min_length=2, max_length=200)
    description: Optional[str] = Field(None, max_length=4000)
    active: Optional[bool] = None
    is_default: Optional[bool] = None
    focus_area_id: Optional[int] = Field(None, ge=1)
    config: Optional[Dict[str, Any]] = None


def _row_to_out(row: dict) -> dict:
    cfg = row.get("config")
    if isinstance(cfg, str):
        try:
            cfg = json.loads(cfg)
        except json.JSONDecodeError:
            cfg = {}
    if not isinstance(cfg, dict):
        cfg = {}
    out = {
        "id": row["id"],
        "focus_area_id": row.get("focus_area_id"),
        "focus_area_name": row.get("focus_area_name"),
        "is_default": bool(row.get("is_default")),
        "name": row.get("name") or "",
        "description": row.get("description") or "",
        "active": bool(row.get("active", True)),
        "config": cfg,
        "updated_at": row.get("updated_at"),
    }
    return out


def _active_focus_conflict(cur, focus_area_id: int, exclude_id: Optional[int] = None) -> bool:
    if exclude_id is not None:
        cur.execute(
            """
            SELECT 1 FROM ai_skill_retrieval_profiles
            WHERE active = true AND focus_area_id = %s AND id != %s
            LIMIT 1
            """,
            (focus_area_id, exclude_id),
        )
    else:
        cur.execute(
            """
            SELECT 1 FROM ai_skill_retrieval_profiles
            WHERE active = true AND focus_area_id = %s
            LIMIT 1
            """,
            (focus_area_id,),
        )
    return cur.fetchone() is not None


def _focus_area_exists(cur, focus_area_id: int) -> bool:
    cur.execute(
        "SELECT 1 FROM focus_areas WHERE id = %s AND (status IS NULL OR status = 'active') LIMIT 1",
        (focus_area_id,),
    )
    return cur.fetchone() is not None


@router.get("/api/admin/ai-skill-retrieval-profiles")
def list_ai_skill_retrieval_profiles(session: dict = Depends(require_auth)):
    _require_superadmin(session)
    with get_db() as conn:
        cur = get_cursor(conn)
        _assert_table(cur)
        cur.execute(
            """
            SELECT p.id, p.focus_area_id, p.is_default, p.name, p.description, p.active, p.config, p.updated_at,
                   fa.name AS focus_area_name
            FROM ai_skill_retrieval_profiles p
            LEFT JOIN focus_areas fa ON fa.id = p.focus_area_id
            ORDER BY p.is_default DESC NULLS LAST, fa.name NULLS LAST, p.name
            """
        )
        rows = [r2d(r) for r in cur.fetchall()]
    return [_row_to_out(r) for r in rows]


@router.get("/api/admin/ai-skill-retrieval-profiles/{profile_id}")
def get_ai_skill_retrieval_profile(profile_id: int, session: dict = Depends(require_auth)):
    _require_superadmin(session)
    with get_db() as conn:
        cur = get_cursor(conn)
        _assert_table(cur)
        cur.execute(
            """
            SELECT p.id, p.focus_area_id, p.is_default, p.name, p.description, p.active, p.config, p.updated_at,
                   fa.name AS focus_area_name
            FROM ai_skill_retrieval_profiles p
            LEFT JOIN focus_areas fa ON fa.id = p.focus_area_id
            WHERE p.id = %s
            """,
            (profile_id,),
        )
        row = cur.fetchone()
    if not row:
        raise HTTPException(status_code=404, detail="Profil nicht gefunden")
    return _row_to_out(r2d(row))


@router.post("/api/admin/ai-skill-retrieval-profiles", status_code=201)
def create_ai_skill_retrieval_profile(
    body: AiRetrievalProfileCreate,
    session: dict = Depends(require_auth),
):
    _require_superadmin(session)
    cfg = _normalize_config(body.config)
    with get_db() as conn:
        cur = get_cursor(conn)
        _assert_table(cur)

        if body.is_default:
            cur.execute(
                "UPDATE ai_skill_retrieval_profiles SET is_default = false, updated_at = NOW() WHERE is_default = true"
            )
        else:
            if not _focus_area_exists(cur, int(body.focus_area_id)):
                raise HTTPException(status_code=400, detail="Unbekannter oder inaktiver Fokusbereich.")
            if body.active and _active_focus_conflict(cur, int(body.focus_area_id)):
                raise HTTPException(
                    status_code=409,
                    detail="Für diesen Fokusbereich existiert bereits ein aktives Profil.",
                )

        cur.execute(
            """
            INSERT INTO ai_skill_retrieval_profiles
                (focus_area_id, is_default, name, description, active, config, updated_at)
            VALUES (%s, %s, %s, %s, %s, %s, NOW())
            RETURNING id
            """,
            (
                None if body.is_default else body.focus_area_id,
                body.is_default,
                body.name.strip(),
                (body.description or "").strip(),
                body.active,
                Json(cfg),
            ),
        )
        new_id_row = cur.fetchone()
        new_id = int(new_id_row["id"] if isinstance(new_id_row, dict) else new_id_row[0])

        cur.execute(
            """
            SELECT p.id, p.focus_area_id, p.is_default, p.name, p.description, p.active, p.config, p.updated_at,
                   fa.name AS focus_area_name
            FROM ai_skill_retrieval_profiles p
            LEFT JOIN focus_areas fa ON fa.id = p.focus_area_id
            WHERE p.id = %s
            """,
            (new_id,),
        )
        row = r2d(cur.fetchone())
    return _row_to_out(row)


@router.put("/api/admin/ai-skill-retrieval-profiles/{profile_id}")
def update_ai_skill_retrieval_profile(
    profile_id: int,
    body: AiRetrievalProfileUpdate,
    session: dict = Depends(require_auth),
):
    _require_superadmin(session)
    with get_db() as conn:
        cur = get_cursor(conn)
        _assert_table(cur)
        cur.execute(
            """
            SELECT id, focus_area_id, is_default, name, description, active, config, updated_at
            FROM ai_skill_retrieval_profiles WHERE id = %s
            """,
            (profile_id,),
        )
        old = cur.fetchone()
        if not old:
            raise HTTPException(status_code=404, detail="Profil nicht gefunden")
        old_d = dict(old)

        if body.is_default is True and body.focus_area_id is not None:
            raise HTTPException(
                status_code=400,
                detail="Standardprofil und Fokusbereich schließen sich aus.",
            )

        nm = body.name.strip() if body.name is not None else (old_d["name"] or "")
        nm = nm.strip()
        desc = (
            body.description.strip() if body.description is not None else (old_d.get("description") or "")
        ).strip()
        active = body.active if body.active is not None else bool(old_d.get("active", True))
        next_is_default = body.is_default if body.is_default is not None else bool(old_d.get("is_default"))
        next_focus_raw = (
            body.focus_area_id if body.focus_area_id is not None else old_d.get("focus_area_id")
        )
        cfg = (
            _normalize_config(body.config) if body.config is not None else _normalize_config(old_d.get("config"))
        )

        if next_is_default:
            focus_id_sql: Optional[int] = None
        else:
            if next_focus_raw is None:
                raise HTTPException(status_code=400, detail="Nicht-Standard-Profil benötigt focus_area_id.")
            focus_id_sql = int(next_focus_raw)
            if not _focus_area_exists(cur, focus_id_sql):
                raise HTTPException(status_code=400, detail="Unbekannter oder inaktiver Fokusbereich.")
            if active and _active_focus_conflict(cur, focus_id_sql, exclude_id=profile_id):
                raise HTTPException(
                    status_code=409,
                    detail="Für diesen Fokusbereich existiert bereits ein anderes aktives Profil.",
                )

        old_default = bool(old_d.get("is_default"))

        if old_default and not next_is_default:
            raise HTTPException(
                status_code=400,
                detail="Das Standardprofil kann hier nicht zum Fokusbereichsprofil geändert werden — zuerst ein anderes Profil als Standard aktivieren.",
            )

        if old_default and active is False:
            raise HTTPException(status_code=400, detail="Standardprofil kann nicht deaktiviert werden.")

        if next_is_default:
            cur.execute(
                """
                UPDATE ai_skill_retrieval_profiles
                SET is_default = false, updated_at = NOW()
                WHERE is_default = true AND id != %s
                """,
                (profile_id,),
            )

        cur.execute(
            """
            UPDATE ai_skill_retrieval_profiles
            SET focus_area_id = %s,
                is_default = %s,
                name = %s,
                description = %s,
                active = %s,
                config = %s,
                updated_at = NOW()
            WHERE id = %s
            """,
            (
                focus_id_sql,
                next_is_default,
                nm,
                desc,
                active,
                Json(cfg),
                profile_id,
            ),
        )

        cur.execute(
            """
            SELECT p.id, p.focus_area_id, p.is_default, p.name, p.description, p.active, p.config, p.updated_at,
                   fa.name AS focus_area_name
            FROM ai_skill_retrieval_profiles p
            LEFT JOIN focus_areas fa ON fa.id = p.focus_area_id
            WHERE p.id = %s
            """,
            (profile_id,),
        )
        row = r2d(cur.fetchone())
    return _row_to_out(row)


@router.delete("/api/admin/ai-skill-retrieval-profiles/{profile_id}")
def delete_ai_skill_retrieval_profile(profile_id: int, session: dict = Depends(require_auth)):
    _require_superadmin(session)
    with get_db() as conn:
        cur = get_cursor(conn)
        _assert_table(cur)
        cur.execute(
            "SELECT id, is_default FROM ai_skill_retrieval_profiles WHERE id = %s",
            (profile_id,),
        )
        row = cur.fetchone()
        if not row:
            raise HTTPException(status_code=404, detail="Profil nicht gefunden")
        if bool(row.get("is_default") if isinstance(row, dict) else row[1]):
            raise HTTPException(status_code=400, detail="Standardprofil kann nicht gelöscht werden.")
        cur.execute("DELETE FROM ai_skill_retrieval_profiles WHERE id = %s", (profile_id,))
    return {"deleted": True, "id": profile_id}