"""프롬프트 관리 API — CRUD + 버전 관리."""

import json
import uuid
from datetime import datetime, timezone
from typing import Optional

from fastapi import APIRouter, Depends, Query
from pydantic import BaseModel
from sqlalchemy import text
from sqlalchemy.orm import Session as OrmSession

from app.api.deps import get_db, get_current_user, require_admin
from app.models.catalog import UserAccount

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


class PromptCreate(BaseModel):
    module: str
    name: str
    version: str
    content: str
    schema_json: Optional[str] = None


class PromptUpdate(BaseModel):
    content: str
    schema_json: Optional[str] = None


@router.get("/")
def list_prompts(
    module: Optional[str] = None,
    active_only: bool = False,
    db: OrmSession = Depends(get_db),
    current_user: UserAccount = Depends(get_current_user),
):
    """프롬프트 목록 조회. module 필터 + active_only 옵션."""
    query = "SELECT id, module, name, version, is_active, file_path, created_at, created_by, LENGTH(content) as content_length FROM prompt_template"
    params = {}
    conditions = []

    if module:
        conditions.append("module = :module")
        params["module"] = module
    if active_only:
        conditions.append("is_active = true")

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

    # SQL ORDER BY version 은 lexical (`9.x` > `10.x` 버그). module/name 만 SQL
    # 정렬, version 은 Python ``_version_sort_key`` numeric-aware 로 후처리.
    query += " ORDER BY module, name"

    rows = db.execute(text(query), params).fetchall()
    from app.modules.prompt_loader import _version_sort_key
    sorted_rows = sorted(
        rows,
        key=lambda r: (r[1], r[2], _version_sort_key(r[3] or "")),
        reverse=False,
    )
    # version DESC 적용 — 같은 (module, name) 묶음 안에서 reverse.
    from itertools import groupby
    final: list = []
    for (mod, nm), grp in groupby(sorted_rows, key=lambda r: (r[1], r[2])):
        final.extend(reversed(list(grp)))
    return [{
        "id": r[0], "module": r[1], "name": r[2], "version": r[3],
        "is_active": r[4], "file_path": r[5], "created_at": r[6],
        "created_by": r[7], "content_length": r[8],
    } for r in final]


@router.get("/modules")
def list_modules(
    db: OrmSession = Depends(get_db),
    current_user: UserAccount = Depends(get_current_user),
):
    """모듈 목록 + 각 모듈의 프롬프트/버전 수.

    latest_version 은 SQL ``MAX(version)`` 이 lexical (``9.x`` > ``10.x`` 버그)
    이므로 module 별 row 를 모두 fetch 한 뒤 Python ``_version_sort_key`` 로
    numeric-aware 최대값 계산.
    """
    rows = db.execute(text(
        "SELECT module, version, is_active FROM prompt_template ORDER BY module"
    )).fetchall()
    from collections import defaultdict
    from app.modules.prompt_loader import _version_sort_key

    by_module: dict = defaultdict(list)
    for r in rows:
        by_module[r[0]].append((r[1], r[2]))

    out = []
    for module in sorted(by_module.keys()):
        entries = by_module[module]
        total = len(entries)
        active_count = sum(1 for _, is_active in entries if is_active)
        versions = [v for v, _ in entries if v]
        versions.sort(key=_version_sort_key, reverse=True)
        latest = versions[0] if versions else None
        out.append({
            "module": module, "total": total, "active_count": active_count,
            "latest_version": latest,
        })
    return out


@router.get("/effective/{module}/{name}")
def get_effective_prompt(
    module: str,
    name: str,
    db: OrmSession = Depends(get_db),
    current_user: UserAccount = Depends(get_current_user),
):
    """현재 ``load_prompt`` 가 실제 사용할 source 표시 — DB row + file fallback 모두 검사.

    admin UI 가 prompt_template DB row 만 보고 active prompt 판단하던 문제 해소
    (problems.md #8). winner / fallback_reason / candidate 정보 표시.
    """
    from app.modules.prompt_loader import get_effective_source
    return get_effective_source(module, name, db)


@router.get("/compare")
def compare_versions(
    id1: str = Query(...),
    id2: str = Query(...),
    db: OrmSession = Depends(get_db),
    current_user: UserAccount = Depends(get_current_user),
):
    """두 프롬프트 버전 비교."""
    r1 = db.execute(text(
        "SELECT module, name, version, content, schema_json FROM prompt_template WHERE id = :id"
    ), {"id": id1}).fetchone()
    r2 = db.execute(text(
        "SELECT module, name, version, content, schema_json FROM prompt_template WHERE id = :id"
    ), {"id": id2}).fetchone()
    if not r1 or not r2:
        from app.core.errors import AppError
        raise AppError(code="prompt.not_found", message="비교 대상을 찾을 수 없습니다", status_code=404)

    return {
        "left": {"module": r1[0], "name": r1[1], "version": r1[2], "content": r1[3], "schema_json": r1[4]},
        "right": {"module": r2[0], "name": r2[1], "version": r2[2], "content": r2[3], "schema_json": r2[4]},
    }


@router.get("/{prompt_id}")
def get_prompt(
    prompt_id: str,
    db: OrmSession = Depends(get_db),
    current_user: UserAccount = Depends(get_current_user),
):
    """프롬프트 상세 (content 포함)."""
    row = db.execute(text(
        "SELECT id, module, name, version, content, schema_json, is_active, file_path, created_at, created_by "
        "FROM prompt_template WHERE id = :id"
    ), {"id": prompt_id}).fetchone()
    if not row:
        from app.core.errors import AppError
        raise AppError(code="prompt.not_found", message="프롬프트를 찾을 수 없습니다", status_code=404)
    return {
        "id": row[0], "module": row[1], "name": row[2], "version": row[3],
        "content": row[4], "schema_json": row[5], "is_active": row[6],
        "file_path": row[7], "created_at": row[8], "created_by": row[9],
    }


@router.post("/")
def create_prompt(
    body: PromptCreate,
    db: OrmSession = Depends(get_db),
    current_user: UserAccount = Depends(require_admin),
):
    """새 프롬프트 버전 생성."""
    now = datetime.now(timezone.utc).isoformat()
    pid = str(uuid.uuid4())

    db.execute(text("""
        INSERT INTO prompt_template (id, module, name, version, content, schema_json, is_active, created_at, created_by)
        VALUES (:id, :module, :name, :version, :content, :schema_json, false, :created_at, :created_by)
    """), {
        "id": pid, "module": body.module, "name": body.name,
        "version": body.version, "content": body.content,
        "schema_json": body.schema_json, "created_at": now,
        "created_by": current_user.id,
    })
    db.commit()
    return {"id": pid, "message": "프롬프트가 생성되었습니다"}


@router.put("/{prompt_id}")
def update_prompt(
    prompt_id: str,
    body: PromptUpdate,
    db: OrmSession = Depends(get_db),
    current_user: UserAccount = Depends(require_admin),
):
    """프롬프트 내용 수정 (같은 버전 내에서)."""
    result = db.execute(text(
        "UPDATE prompt_template SET content = :content, schema_json = :schema_json WHERE id = :id"
    ), {"content": body.content, "schema_json": body.schema_json, "id": prompt_id})
    if result.rowcount == 0:
        from app.core.errors import AppError
        raise AppError(code="prompt.not_found", message="프롬프트를 찾을 수 없습니다", status_code=404)
    db.commit()
    return {"ok": True}


@router.post("/{prompt_id}/activate")
def activate_prompt(
    prompt_id: str,
    db: OrmSession = Depends(get_db),
    current_user: UserAccount = Depends(require_admin),
):
    """이 프롬프트를 활성 버전으로 설정 (같은 module+name의 다른 버전은 비활성)."""
    row = db.execute(text(
        "SELECT module, name FROM prompt_template WHERE id = :id"
    ), {"id": prompt_id}).fetchone()
    if not row:
        from app.core.errors import AppError
        raise AppError(code="prompt.not_found", message="프롬프트를 찾을 수 없습니다", status_code=404)

    module, name = row[0], row[1]
    db.execute(text(
        "UPDATE prompt_template SET is_active = false WHERE module = :m AND name = :n"
    ), {"m": module, "n": name})
    db.execute(text(
        "UPDATE prompt_template SET is_active = true WHERE id = :id"
    ), {"id": prompt_id})
    db.commit()
    return {"ok": True, "message": f"{module}/{name} 활성 버전이 변경되었습니다"}