"""
BI 대시보드 API — 실시간 KPI 위젯 + 트렌드 데이터

기존 analytics.py / sla.py / report.py를 통합·고도화.
Chart.js / D3.js 프론트엔드용 구조화된 데이터 반환.

엔드포인트:
  GET /api/bi/overview          — 전체 현황 요약 (메인 대시보드)
  GET /api/bi/sr-trend          — SR 트렌드 (일별/주별/월별)
  GET /api/bi/sla-heatmap       — SLA 준수율 히트맵
  GET /api/bi/engineer-load     — 엔지니어별 워크로드 분포
  GET /api/bi/category-pie      — SR 카테고리별 분포
  GET /api/bi/resolution-funnel — SR 처리 단계별 퍼널
  GET /api/bi/mttr-trend        — MTTR 트렌드
"""
from __future__ import annotations

from datetime import date, datetime, timedelta
from typing import List, Optional

from fastapi import APIRouter, Depends, Query
from sqlalchemy import select, func, and_, case, desc
from sqlalchemy.ext.asyncio import AsyncSession

from core.auth import get_current_user
from database import get_db
from models import SRRequest, SRStatus, User, WorkLog

router = APIRouter(prefix="/api/bi", tags=["BI Dashboard"])


def _date_series(days: int) -> list[str]:
    return [(date.today() - timedelta(days=i)).isoformat() for i in range(days - 1, -1, -1)]


@router.get("/overview")
async def bi_overview(
    db: AsyncSession = Depends(get_db),
    user: User = Depends(get_current_user),
):
    """메인 대시보드 — 핵심 지표 카드 데이터."""
    today = date.today()
    month_start = today.replace(day=1)
    week_start  = today - timedelta(days=today.weekday())

    async def count_sr(status=None, since=None):
        q = select(func.count(SRRequest.id))
        filters = []
        if status: filters.append(SRRequest.status == status)
        if since:  filters.append(SRRequest.created_at >= since)
        if filters: q = q.where(and_(*filters))
        return (await db.execute(q)).scalar() or 0

    open_sr    = await count_sr(status=SRStatus.OPEN)
    inprog_sr  = await count_sr(status=SRStatus.IN_PROGRESS)
    done_month = await count_sr(status=SRStatus.DONE, since=month_start)
    done_week  = await count_sr(status=SRStatus.DONE, since=week_start)

    # MTTR 이번 달
    mttr_result = await db.execute(
        select(
            func.avg(
                func.extract('epoch', SRRequest.updated_at - SRRequest.created_at) / 3600
            )
        ).where(
            SRRequest.status == SRStatus.DONE,
            SRRequest.updated_at >= month_start,
        )
    )
    mttr = round(mttr_result.scalar() or 0, 1)

    # 전월 대비 증감
    prev_month_start = (month_start - timedelta(days=1)).replace(day=1)
    done_prev = await count_sr(status=SRStatus.DONE, since=prev_month_start)
    done_prev_cnt = done_prev - done_month if done_prev > done_month else done_prev

    return {
        "cards": [
            {"key": "open_sr",     "label": "미처리 SR",       "value": open_sr,    "unit": "건", "color": "red"},
            {"key": "inprog_sr",   "label": "처리 중 SR",      "value": inprog_sr,  "unit": "건", "color": "orange"},
            {"key": "done_month",  "label": "이번 달 완료",     "value": done_month, "unit": "건", "color": "green",
             "change": done_month - done_prev_cnt, "change_label": "전월 대비"},
            {"key": "done_week",   "label": "이번 주 완료",     "value": done_week,  "unit": "건", "color": "blue"},
            {"key": "mttr",        "label": "평균 처리 시간",   "value": mttr,       "unit": "시간", "color": "purple"},
        ],
        "updated_at": datetime.utcnow().isoformat(),
    }


@router.get("/sr-trend")
async def sr_trend(
    period: str = Query("daily", pattern="^(daily|weekly|monthly)$"),
    days: int = Query(30, ge=7, le=365),
    db: AsyncSession = Depends(get_db),
    user: User = Depends(get_current_user),
):
    """SR 생성/완료 트렌드 (Chart.js line chart용)."""
    today = date.today()
    since = today - timedelta(days=days)

    # 날짜별 생성 건수
    created = await db.execute(
        select(
            func.date(SRRequest.created_at).label("d"),
            func.count(SRRequest.id).label("cnt"),
        ).where(SRRequest.created_at >= since)
        .group_by(func.date(SRRequest.created_at))
        .order_by("d")
    )
    created_map = {str(r.d): r.cnt for r in created.all()}

    # 날짜별 완료 건수
    resolved = await db.execute(
        select(
            func.date(SRRequest.updated_at).label("d"),
            func.count(SRRequest.id).label("cnt"),
        ).where(
            SRRequest.status == SRStatus.DONE,
            SRRequest.updated_at >= since,
        ).group_by(func.date(SRRequest.updated_at)).order_by("d")
    )
    resolved_map = {str(r.d): r.cnt for r in resolved.all()}

    labels = _date_series(days)
    return {
        "period": period,
        "labels": labels,
        "datasets": [
            {"label": "신규 SR",  "data": [created_map.get(d, 0)  for d in labels], "color": "#003366"},
            {"label": "완료 SR",  "data": [resolved_map.get(d, 0) for d in labels], "color": "#10B981"},
        ],
    }


@router.get("/sla-heatmap")
async def sla_heatmap(
    weeks: int = Query(12, ge=4, le=52),
    db: AsyncSession = Depends(get_db),
    user: User = Depends(get_current_user),
):
    """SLA 준수율 히트맵 (주별 × 카테고리)."""
    since = date.today() - timedelta(weeks=weeks)
    rows = await db.execute(
        select(
            func.date_trunc('week', SRRequest.created_at).label("week"),
            SRRequest.category.label("cat"),
            func.count(SRRequest.id).label("total"),
            func.sum(
                case(
                    (func.extract('epoch', SRRequest.updated_at - SRRequest.created_at) <= 14400, 1),
                    else_=0
                )
            ).label("on_time"),
        ).where(
            SRRequest.status == SRStatus.DONE,
            SRRequest.created_at >= since,
        ).group_by("week", SRRequest.category).order_by("week")
    )
    data = []
    for r in rows.all():
        rate = round(r.on_time / r.total * 100, 1) if r.total else 0
        data.append({
            "week": r.week.date().isoformat() if r.week else None,
            "category": r.cat or "기타",
            "total": r.total,
            "on_time": r.on_time,
            "compliance_pct": rate,
        })
    return {"data": data}


@router.get("/engineer-load")
async def engineer_load(
    days: int = Query(30, ge=7, le=90),
    db: AsyncSession = Depends(get_db),
    user: User = Depends(get_current_user),
):
    """엔지니어별 SR 워크로드 분포 (bar chart용)."""
    since = date.today() - timedelta(days=days)
    rows = await db.execute(
        select(
            User.name.label("engineer"),
            func.count(SRRequest.id).label("total"),
            func.sum(case((SRRequest.status == SRStatus.DONE, 1), else_=0)).label("done"),
            func.sum(case((SRRequest.status.in_([SRStatus.OPEN, SRStatus.IN_PROGRESS]), 1), else_=0)).label("open"),
        ).join(User, SRRequest.assignee_id == User.id, isouter=True)
        .where(SRRequest.created_at >= since)
        .group_by(User.name).order_by(desc("total")).limit(20)
    )
    data = [
        {"engineer": r.engineer or "미배정", "total": r.total, "done": r.done, "open": r.open}
        for r in rows.all()
    ]
    return {
        "period_days": days,
        "labels": [d["engineer"] for d in data],
        "datasets": [
            {"label": "완료", "data": [d["done"] for d in data],  "color": "#10B981"},
            {"label": "진행중", "data": [d["open"] for d in data], "color": "#F59E0B"},
        ],
    }


@router.get("/category-pie")
async def category_pie(
    days: int = Query(30, ge=7, le=365),
    db: AsyncSession = Depends(get_db),
    user: User = Depends(get_current_user),
):
    """SR 카테고리별 분포 (pie chart용)."""
    since = date.today() - timedelta(days=days)
    rows = await db.execute(
        select(
            SRRequest.category.label("cat"),
            func.count(SRRequest.id).label("cnt"),
        ).where(SRRequest.created_at >= since)
        .group_by(SRRequest.category).order_by(desc("cnt"))
    )
    data = [{"category": r.cat or "기타", "count": r.cnt} for r in rows.all()]
    total = sum(d["count"] for d in data)
    for d in data:
        d["pct"] = round(d["count"] / total * 100, 1) if total else 0
    return {"period_days": days, "total": total, "data": data}


@router.get("/mttr-trend")
async def mttr_trend(
    months: int = Query(6, ge=3, le=24),
    db: AsyncSession = Depends(get_db),
    user: User = Depends(get_current_user),
):
    """MTTR 월별 트렌드."""
    since = date.today() - timedelta(days=months * 30)
    rows = await db.execute(
        select(
            func.date_trunc('month', SRRequest.updated_at).label("month"),
            func.avg(
                func.extract('epoch', SRRequest.updated_at - SRRequest.created_at) / 3600
            ).label("mttr_hours"),
            func.count(SRRequest.id).label("cnt"),
        ).where(
            SRRequest.status == SRStatus.DONE,
            SRRequest.updated_at >= since,
        ).group_by("month").order_by("month")
    )
    data = [
        {
            "month": r.month.strftime("%Y-%m") if r.month else None,
            "mttr_hours": round(r.mttr_hours or 0, 1),
            "count": r.cnt,
        }
        for r in rows.all()
    ]
    return {
        "labels": [d["month"] for d in data],
        "datasets": [{"label": "MTTR (시간)", "data": [d["mttr_hours"] for d in data], "color": "#6366F1"}],
        "raw": data,
    }


@router.get("/resolution-funnel")
async def resolution_funnel(
    days: int = Query(30, ge=7, le=90),
    db: AsyncSession = Depends(get_db),
    user: User = Depends(get_current_user),
):
    """SR 처리 단계별 퍼널 (funnel chart용)."""
    since = date.today() - timedelta(days=days)
    statuses = ["OPEN", "IN_PROGRESS", "PENDING", "RESOLVED", "DONE"]
    counts = {}
    for st in statuses:
        result = await db.execute(
            select(func.count(SRRequest.id)).where(
                SRRequest.created_at >= since,
                SRRequest.status == st,
            )
        )
        counts[st] = result.scalar() or 0

    total = sum(counts.values())
    return {
        "period_days": days,
        "total_created": total,
        "funnel": [
            {"stage": st, "count": counts[st],
             "pct": round(counts[st] / total * 100, 1) if total else 0}
            for st in statuses
        ],
    }