"""Reports router — topic weakness, leaderboard, readiness, completion."""
from uuid import UUID

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

from app.api.deps import get_current_user, require_roles
from app.core.rbac import Role
from app.db.base import get_db
from app.models import (
    LeaderboardSnapshot,
    LearnerProgress,
    ManagerSignoff,
    ModuleModel,
    UserModel,
    WeakTopicSnapshot,
)
from app.schemas import (
    CompletionRow,
    LeaderboardRow,
    ReadinessRow,
    WeakTopicRow,
)

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


@router.get("/topic-weakness", response_model=list[WeakTopicRow])
async def topic_weakness_report(
    module_id: UUID | None = Query(None),
    learner_id: UUID | None = Query(None),
    db: AsyncSession = Depends(get_db),
    current_user: UserModel = Depends(
        require_roles(Role.LD_ADMIN, Role.SALES_MANAGER, Role.DEPT_HEAD)
    ),
) -> list[WeakTopicRow]:
    """Topic weakness by learner, batch, module, and cohort. HR accesses via LD_ADMIN role."""
    query = (
        select(
            WeakTopicSnapshot,
            UserModel,
            ModuleModel,
        )
        .join(UserModel, WeakTopicSnapshot.learner_id == UserModel.id)
        .join(ModuleModel, WeakTopicSnapshot.module_id == ModuleModel.id)
        .where(WeakTopicSnapshot.weakness_ratio > 0.3)  # only show weak topics
    )
    if module_id:
        query = query.where(WeakTopicSnapshot.module_id == module_id)
    if learner_id:
        query = query.where(WeakTopicSnapshot.learner_id == learner_id)

    query = query.order_by(WeakTopicSnapshot.weakness_ratio.desc())
    result = await db.execute(query)
    rows = result.all()

    return [
        WeakTopicRow(
            learner_id=snap.learner_id,
            learner_name=user.full_name,
            module_id=snap.module_id,
            module_name=module.name,
            topic_tag=snap.topic_tag,
            total_questions=snap.total_questions,
            correct_questions=snap.correct_questions,
            weakness_ratio=snap.weakness_ratio,
        )
        for snap, user, module in rows
    ]


@router.get("/leaderboard", response_model=list[LeaderboardRow])
async def leaderboard_report(
    week: str | None = Query(None, description="ISO week key e.g. 2026-W32"),
    db: AsyncSession = Depends(get_db),
    current_user: UserModel = Depends(get_current_user),
) -> list[LeaderboardRow]:
    """Company-wide leaderboard. Shows top learners based on live progress and scores."""
    result = await db.execute(
        select(
            UserModel,
            func.coalesce(func.max(LearnerProgress.best_score), 0.0).label("score"),
            func.coalesce(func.sum(LearnerProgress.attempt_count), 0).label("streak"),
        )
        .outerjoin(LearnerProgress, UserModel.id == LearnerProgress.learner_id)
        .where(UserModel.is_active == True)
        .group_by(UserModel.id)
        .order_by(func.coalesce(func.max(LearnerProgress.best_score), 0.0).desc(), UserModel.full_name)
    )
    rows = result.all()

    leaderboard = []
    for idx, (user, score, streak) in enumerate(rows, start=1):
        leaderboard.append(
            LeaderboardRow(
                rank=idx,
                user_id=user.id,
                user_name=user.display_name or user.full_name,
                score=float(score),
                streak_count=int(streak),
                mastery_points=float(score * 10),
                improvement_points=0.0,
                consistency_points=float(streak * 5),
            )
        )
    return leaderboard


@router.get("/readiness", response_model=list[ReadinessRow])
async def readiness_report(
    db: AsyncSession = Depends(get_db),
    current_user: UserModel = Depends(
        require_roles(Role.LD_ADMIN, Role.SALES_MANAGER, Role.DEPT_HEAD)
    ),
) -> list[ReadinessRow]:
    """Certification readiness queue — learners who passed but may need sign-off."""
    result = await db.execute(
        select(LearnerProgress, UserModel, ModuleModel, ManagerSignoff)
        .join(UserModel, LearnerProgress.learner_id == UserModel.id)
        .join(ModuleModel, LearnerProgress.module_id == ModuleModel.id)
        .outerjoin(
            ManagerSignoff,
            and_(
                ManagerSignoff.learner_id == LearnerProgress.learner_id,
                ManagerSignoff.module_id == LearnerProgress.module_id,
            )
        )
        .where(LearnerProgress.is_passed == True)
        .order_by(LearnerProgress.best_score.desc())
    )
    rows = result.all()
    return [
        ReadinessRow(
            learner_id=progress.learner_id,
            learner_name=user.full_name,
            module_id=progress.module_id,
            module_name=module.name,
            best_score=progress.best_score,
            signed_off=progress.signed_off,
            signoff_status=signoff.status.value if signoff else None,
        )
        for progress, user, module, signoff in rows
    ]


@router.get("/completion", response_model=list[CompletionRow])
async def completion_report(
    module_id: UUID | None = Query(None),
    db: AsyncSession = Depends(get_db),
    current_user: UserModel = Depends(
        require_roles(Role.LD_ADMIN, Role.SALES_MANAGER, Role.DEPT_HEAD)
    ),
) -> list[CompletionRow]:
    """Completion status — best score, latest score, replay delta, sign-off status."""
    query = (
        select(LearnerProgress, UserModel, ModuleModel)
        .join(UserModel, LearnerProgress.learner_id == UserModel.id)
        .join(ModuleModel, LearnerProgress.module_id == ModuleModel.id)
    )
    if module_id:
        query = query.where(LearnerProgress.module_id == module_id)
    query = query.order_by(UserModel.full_name)
    result = await db.execute(query)
    rows = result.all()
    return [
        CompletionRow(
            learner_id=progress.learner_id,
            learner_name=user.full_name,
            module_id=progress.module_id,
            module_name=module.name,
            best_score=progress.best_score,
            latest_score=progress.latest_score,
            attempt_count=progress.attempt_count,
            is_passed=progress.is_passed,
            signed_off=progress.signed_off,
        )
        for progress, user, module in rows
    ]