"""
Leaderboard Service — Ranking and department aggregation queries.

Optimized for fast reads since leaderboard is the most viewed page.
Uses denormalized total_xp on the users table for ranking.
"""

from uuid import UUID

from sqlalchemy import func, select
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy.orm import selectinload

from app.models.activity import ActivityCompletion
from app.models.gamification import Level, UserBadge
from app.models.user import Department, User


async def get_leaderboard(
    db: AsyncSession,
    limit: int = 50,
    offset: int = 0,
    department_id: UUID | None = None,
) -> list[dict]:
    """
    Get ranked leaderboard. Returns users sorted by total_xp descending
    with their level, completion count, and badge count.
    """
    query = (
        select(User)
        .where(User.is_active == True)  # noqa: E712
        .options(selectinload(User.department))
        .order_by(User.total_xp.desc())
        .limit(limit)
        .offset(offset)
    )

    if department_id:
        query = query.where(User.department_id == department_id)

    result = await db.execute(query)
    users = result.scalars().all()

    # Get all levels for mapping
    levels_result = await db.execute(select(Level).order_by(Level.min_xp.desc()))
    levels = levels_result.scalars().all()

    leaderboard = []
    for rank, user in enumerate(users, start=offset + 1):
        # Find level
        user_level = None
        for level in levels:
            if user.total_xp >= level.min_xp:
                user_level = level
                break

        # Count completions
        comp_result = await db.execute(
            select(func.count(ActivityCompletion.id)).where(
                ActivityCompletion.user_id == user.id,
                ActivityCompletion.status == "completed",
            )
        )
        completed_count = comp_result.scalar() or 0

        # Count badges
        badge_result = await db.execute(
            select(func.count(UserBadge.id)).where(UserBadge.user_id == user.id)
        )
        badge_count = badge_result.scalar() or 0

        leaderboard.append({
            "rank": rank,
            "user": user,
            "total_xp": user.total_xp,
            "level": user_level,
            "completed_count": completed_count,
            "badge_count": badge_count,
        })

    return leaderboard


async def get_department_summary(db: AsyncSession) -> list[dict]:
    """
    Aggregate stats per department for the department comparison view.
    """
    result = await db.execute(select(Department))
    departments = result.scalars().all()

    summaries = []
    for dept in departments:
        # Get members
        members_result = await db.execute(
            select(User).where(User.department_id == dept.id, User.is_active == True)  # noqa: E712
            .order_by(User.total_xp.desc())
        )
        members = members_result.scalars().all()

        if not members:
            continue

        total_xp = sum(m.total_xp for m in members)

        # Total completions for department
        comp_result = await db.execute(
            select(func.count(ActivityCompletion.id))
            .join(User, ActivityCompletion.user_id == User.id)
            .where(
                User.department_id == dept.id,
                ActivityCompletion.status == "completed",
            )
        )
        total_completions = comp_result.scalar() or 0

        summaries.append({
            "department": dept,
            "member_count": len(members),
            "total_xp": total_xp,
            "avg_xp": round(total_xp / len(members)) if members else 0,
            "total_completions": total_completions,
            "top_performer": members[0] if members else None,
        })

    return sorted(summaries, key=lambda s: s["total_xp"], reverse=True)