"""AI 月度运营洞察 for 服务台分析 (百炼/Qwen).

Feeds a compact, structured digest of the helpdesk's recent tickets
(monthly volumes + category mix + SLA + satisfaction) to the org's
configured Bailian LLM and asks for an IT-service-desk analyst's monthly
read: trend summary, hotspots (by category), and concrete recommendations.

Graceful: when no LLM key is configured the endpoint returns
``available=False`` (the page shows a "配置后启用" hint, never an error).
Results are cached in ``platform_settings`` (key
``feishu_helpdesk.ai_insights.<integration_id>``) for ``_STALE_DAYS`` so a
page load doesn't pay the LLM round-trip every time — a refresh button
forces regeneration.
"""
from __future__ import annotations

import json
import logging
from datetime import UTC, datetime, timedelta
from typing import Any

from sqlalchemy import and_, case, func, select
from sqlalchemy.ext.asyncio import AsyncSession

from app.domains.feishu_helpdesk.models import FeishuHelpdeskTicket
from app.integrations.models import PlatformSetting
from app.integrations.service import get_platform_setting

logger = logging.getLogger(__name__)

_BAILIAN_KEY = "ai_spending.llm.api_key"
_BAILIAN_MODEL = "ai_spending.llm.model"
_BAILIAN_REGION = "ai_spending.llm.region"
_DEFAULT_MODEL = "qwen-max-latest"

_CACHE_PREFIX = "feishu_helpdesk.ai_insights."
_STALE_DAYS = 7
_LOOKBACK_DAYS = 180  # ~6 monthly buckets for trend context


def _cache_key(integration_id: Any) -> str:
    return f"{_CACHE_PREFIX}{integration_id or 'all'}"


async def _resolve_llm(db: AsyncSession) -> tuple[str, str, str | None] | None:
    """(api_key, model, region) for the org's Bailian connector, or None."""
    key = await get_platform_setting(_BAILIAN_KEY, None, session=db)
    key = key.strip() if isinstance(key, str) else ""
    if not key:
        return None
    model = await get_platform_setting(_BAILIAN_MODEL, _DEFAULT_MODEL, session=db)
    region = await get_platform_setting(_BAILIAN_REGION, None, session=db)
    return key, str(model or _DEFAULT_MODEL), region


async def build_digest(
    db: AsyncSession, integration_id: Any | None,
) -> dict[str, Any]:
    """Compact structured stats over the last ``_LOOKBACK_DAYS`` for the LLM."""
    end = datetime.now(UTC)
    start = end - timedelta(days=_LOOKBACK_DAYS)
    conds: list[Any] = [
        FeishuHelpdeskTicket.created_at_feishu >= start,
        FeishuHelpdeskTicket.created_at_feishu <= end,
    ]
    if integration_id is not None:
        conds.append(FeishuHelpdeskTicket.integration_id == integration_id)
    base = and_(*conds)

    month = func.date_format(FeishuHelpdeskTicket.created_at_feishu, "%Y-%m")
    monthly_rows = (
        await db.execute(
            select(
                month.label("m"),
                func.count().label("n"),
                func.sum(case((FeishuHelpdeskTicket.solve == 1, 1), else_=0)).label("solved"),
                # solve_known excludes still-open / unknown tickets so the
                # rate matches the dashboard KPI (service.py); using total
                # count as the denominator would feed the LLM a systematically
                # lower solve-rate than the admin sees on the tiles.
                func.sum(case((FeishuHelpdeskTicket.solve.in_((1, 2)), 1), else_=0)).label("solve_known"),
                func.sum(case((FeishuHelpdeskTicket.score == 3, 1), else_=0)).label("satisfied"),
                func.sum(case((FeishuHelpdeskTicket.score >= 1, 1), else_=0)).label("rated"),
                func.avg(FeishuHelpdeskTicket.first_response_seconds).label("frt"),
            )
            .where(base)
            .group_by(month)
            .order_by(month)
        )
    ).all()
    monthly = [
        {
            "month": m,
            "count": int(n),
            "solve_rate": round(int(sv) / int(sk), 3) if sk else None,
            "satisfaction_rate": (round(int(sat) / int(rt), 3) if rt else None),
            "avg_first_response_min": (round(float(frt) / 60, 1) if frt is not None else None),
        }
        for m, n, sv, sk, sat, rt, frt in monthly_rows
    ]

    async def _top(col, limit: int = 12) -> list[dict[str, Any]]:
        rows = (
            await db.execute(
                select(col.label("k"), func.count().label("n"))
                .where(and_(base, col.is_not(None)))
                .group_by(col)
                .order_by(func.count().desc())
                .limit(limit)
            )
        ).all()
        return [{"name": str(k), "count": int(n)} for k, n in rows]

    by_category = await _top(FeishuHelpdeskTicket.agent_group)
    total = sum(r["count"] for r in monthly)
    return {
        "window": {"start": start.date().isoformat(), "end": end.date().isoformat()},
        "total_tickets": total,
        "monthly": monthly,
        "by_category": by_category,
    }


def _build_prompt(digest: dict[str, Any]) -> tuple[str, str]:
    system = (
        "你是 PingCAP 的 IT 服务台运营分析师。基于给定的服务台工单统计(按月趋势、"
        "按问题类别/客服组分布、解决率、满意度、首响时长),给出一段务实、具体、"
        "可执行的中文月度运营分析。聚焦:量的趋势与异动、最集中的问题类别(可能的"
        "根因方向)、SLA 与满意度的变化、以及能落地的改进建议(例如自助文档、流程、"
        "人力/排班、自动化)。不要泛泛而谈,要引用数据。"
    )
    user = (
        "服务台统计数据(JSON):\n"
        + json.dumps(digest, ensure_ascii=False)
        + "\n\n请只输出一个 JSON 对象,字段:\n"
        '{"summary": "一两句总体结论",'
        ' "trends": ["趋势要点", ...],'
        ' "hotspots": [{"category": "问题类别", "note": "为何值得关注/可能根因"}, ...],'
        ' "recommendations": ["可执行建议", ...]}'
        "\n每个数组 3–6 条,中文,引用具体数字。"
    )
    return system, user


async def _persist(db: AsyncSession, key: str, value: dict[str, Any]) -> None:
    row = await db.get(PlatformSetting, key)
    if row is not None:
        row.value = value
    else:
        db.add(PlatformSetting(
            key=key, value=value, description="飞书服务台 AI 月度洞察缓存",
        ))
    await db.commit()


async def _generate(
    db: AsyncSession, integration_id: Any | None,
    *, key: str, model: str, region: str | None,
) -> dict[str, Any]:
    from app.domains.bailian.llm_client import BailianLLMClient

    digest = await build_digest(db, integration_id)
    if digest["total_tickets"] == 0:
        return {
            "available": True, "generated": False,
            "reason": "窗口内暂无工单数据",
            "generated_at": datetime.now(UTC).isoformat(),
        }
    system, user = _build_prompt(digest)
    client = BailianLLMClient(key, model=model, region=region, purpose="helpdesk.insights")
    out = await client.chat_json(system=system, user=user, max_tokens=2200)
    payload = {
        "available": True,
        "generated": True,
        "summary": out.get("summary"),
        "trends": out.get("trends") or [],
        "hotspots": out.get("hotspots") or [],
        "recommendations": out.get("recommendations") or [],
        "model": (out.get("_meta") or {}).get("model_used") or model,
        "window": digest["window"],
        "generated_at": datetime.now(UTC).isoformat(),
    }
    await _persist(db, _cache_key(integration_id), payload)
    return payload


def _is_stale(payload: dict[str, Any]) -> bool:
    ts = payload.get("generated_at")
    if not isinstance(ts, str):
        return True
    try:
        gen = datetime.fromisoformat(ts)
    except ValueError:
        return True
    if gen.tzinfo is None:
        gen = gen.replace(tzinfo=UTC)
    return datetime.now(UTC) - gen > timedelta(days=_STALE_DAYS)


async def get_insights(
    db: AsyncSession, integration_id: Any | None, *, force: bool = False,
) -> dict[str, Any]:
    """Cached-or-generate. Never raises for missing-key / LLM failure —
    returns an ``available``-flagged dict the frontend renders gracefully."""
    llm = await _resolve_llm(db)
    cached = await get_platform_setting(_cache_key(integration_id), None, session=db)
    cached = cached if isinstance(cached, dict) else None

    if llm is None:
        # No LLM configured — surface stale cache if any, else unavailable.
        if cached:
            return {**cached, "available": False, "llm_unconfigured": True}
        return {
            "available": False,
            "llm_unconfigured": True,
            "reason": "未配置 LLM(百炼)API Key —— 到 管理后台 > 连接器 > AI/LLM 配置后启用。",
        }

    if cached and not force and not _is_stale(cached):
        return {**cached, "cached": True}

    key, model, region = llm
    try:
        return await _generate(db, integration_id, key=key, model=model, region=region)
    except Exception as e:  # noqa: BLE001
        await db.rollback()
        logger.warning("feishu helpdesk AI insights generation failed: %s", e)
        if cached:
            return {**cached, "stale": True, "error": f"{type(e).__name__}: {e}"}
        return {"available": True, "generated": False, "error": f"{type(e).__name__}: {e}"}