"""
Users API — CRUD + profile endpoints for the DWA AI Adoption Tracker.
"""

from datetime import datetime
from uuid import UUID

from fastapi import APIRouter, Depends, HTTPException, Query
from pydantic import BaseModel, EmailStr
from sqlalchemy import func, or_, select, extract
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy.orm import selectinload

from app.auth.demo_auth import get_admin_user, get_current_user
from app.database import get_db
from app.models.activity import Activity, ActivityCompletion
from app.models.gamification import Badge, UserBadge
from app.models.user import Department, User

router = APIRouter(prefix="/users", tags=["Users"])

# ---------------------------------------------------------------------------
# Inline Pydantic schemas (will be moved to app.schemas.user later)
# ---------------------------------------------------------------------------


class UserCreate(BaseModel):
    email: EmailStr
    full_name: str
    employee_id: str | None = None
    department_id: UUID | None = None
    role: str = "employee"
    github_username: str | None = None
    udemy_email: str | None = None


class UserUpdate(BaseModel):
    full_name: str | None = None
    employee_id: str | None = None
    department_id: UUID | None = None
    role: str | None = None
    is_active: bool | None = None
    avatar_url: str | None = None
    github_username: str | None = None
    udemy_email: str | None = None


# ---------------------------------------------------------------------------
# Helpers
# ---------------------------------------------------------------------------


def _user_to_dict(user: User, *, department_name: str | None = None) -> dict:
    return {
        "id": str(user.id),
        "employee_id": user.employee_id,
        "email": user.email,
        "full_name": user.full_name,
        "department_id": str(user.department_id) if user.department_id else None,
        "department_name": department_name or (user.department.name if user.department else None),
        "role": user.role,
        "avatar_url": user.avatar_url,
        "is_active": user.is_active,
        "total_xp": user.total_xp,
        "verified_xp": user.verified_xp,
        "current_level": user.current_level,
        "github_username": user.github_username,
        "udemy_email": user.udemy_email,
        "created_at": user.created_at.isoformat() if user.created_at else None,
        "updated_at": user.updated_at.isoformat() if user.updated_at else None,
    }


# ---------------------------------------------------------------------------
# GET /users — list with optional filters
# ---------------------------------------------------------------------------


@router.get("")
async def list_users(
    department_id: str | None = Query(None),
    is_active: bool | None = Query(None),
    search: str | None = Query(None, description="Search by name or email"),
    skip: int = Query(0, ge=0),
    limit: int = Query(50, ge=1, le=200),
    db: AsyncSession = Depends(get_db),
    _=Depends(get_current_user),
):
    stmt = select(User).options(selectinload(User.department))

    if department_id:
        stmt = stmt.where(User.department_id == UUID(department_id))
    if is_active is not None:
        stmt = stmt.where(User.is_active == is_active)
    if search:
        pattern = f"%{search}%"
        stmt = stmt.where(
            or_(
                User.full_name.ilike(pattern),
                User.email.ilike(pattern),
            )
        )

    # Total count (before pagination)
    count_stmt = select(func.count()).select_from(stmt.subquery())
    total = (await db.execute(count_stmt)).scalar() or 0

    stmt = stmt.order_by(User.full_name).offset(skip).limit(limit)
    result = await db.execute(stmt)
    users = result.scalars().all()

    return {
        "items": [_user_to_dict(u) for u in users],
        "total": total,
        "skip": skip,
        "limit": limit,
    }


# ---------------------------------------------------------------------------
# GET /users/{user_id} — single user with aggregated stats
# ---------------------------------------------------------------------------


@router.get("/{user_id}")
async def get_user(
    user_id: UUID,
    db: AsyncSession = Depends(get_db),
    _=Depends(get_current_user),
):
    result = await db.execute(
        select(User).options(selectinload(User.department)).where(User.id == user_id)
    )
    user = result.scalar_one_or_none()
    if not user:
        raise HTTPException(status_code=404, detail="User not found")

    # Completion count
    completion_count = (
        await db.execute(
            select(func.count(ActivityCompletion.id)).where(
                ActivityCompletion.user_id == user_id,
                ActivityCompletion.status.in_(["completed", "verified"]),
            )
        )
    ).scalar() or 0

    # Badge count
    badge_count = (
        await db.execute(
            select(func.count(UserBadge.id)).where(UserBadge.user_id == user_id)
        )
    ).scalar() or 0

    data = _user_to_dict(user)
    data["completion_count"] = completion_count
    data["badge_count"] = badge_count
    return data


# ---------------------------------------------------------------------------
# GET /users/{user_id}/completions — user's completions with activity titles
# ---------------------------------------------------------------------------


@router.get("/{user_id}/completions")
async def get_user_completions(
    user_id: UUID,
    status: str | None = Query(None),
    skip: int = Query(0, ge=0),
    limit: int = Query(50, ge=1, le=200),
    db: AsyncSession = Depends(get_db),
    _=Depends(get_current_user),
):
    # Verify user exists
    user = (await db.execute(select(User).where(User.id == user_id))).scalar_one_or_none()
    if not user:
        raise HTTPException(status_code=404, detail="User not found")

    stmt = (
        select(ActivityCompletion)
        .options(selectinload(ActivityCompletion.activity))
        .where(ActivityCompletion.user_id == user_id)
    )
    if status:
        stmt = stmt.where(ActivityCompletion.status == status)

    count_stmt = select(func.count()).select_from(stmt.subquery())
    total = (await db.execute(count_stmt)).scalar() or 0

    stmt = stmt.order_by(ActivityCompletion.created_at.desc()).offset(skip).limit(limit)
    result = await db.execute(stmt)
    completions = result.scalars().all()

    items = []
    for c in completions:
        items.append(
            {
                "id": str(c.id),
                "activity_id": str(c.activity_id),
                "activity_title": c.activity.title if c.activity else None,
                "activity_category": c.activity.category if c.activity else None,
                "activity_platform": c.activity.platform if c.activity else None,
                "status": c.status,
                "progress_pct": c.progress_pct,
                "xp_awarded": c.xp_awarded,
                "trust_tier": c.trust_tier,
                "source": c.source,
                "evidence_url": c.evidence_url,
                "started_at": c.started_at.isoformat() if c.started_at else None,
                "completed_at": c.completed_at.isoformat() if c.completed_at else None,
                "created_at": c.created_at.isoformat() if c.created_at else None,
            }
        )

    return {"items": items, "total": total, "skip": skip, "limit": limit}


# ---------------------------------------------------------------------------
# GET /users/{user_id}/badges — user's earned badges
# ---------------------------------------------------------------------------


@router.get("/{user_id}/badges")
async def get_user_badges(
    user_id: UUID,
    db: AsyncSession = Depends(get_db),
    _=Depends(get_current_user),
):
    user = (await db.execute(select(User).where(User.id == user_id))).scalar_one_or_none()
    if not user:
        raise HTTPException(status_code=404, detail="User not found")

    result = await db.execute(
        select(UserBadge)
        .options(selectinload(UserBadge.badge))
        .where(UserBadge.user_id == user_id)
        .order_by(UserBadge.awarded_at.desc())
    )
    user_badges = result.scalars().all()

    return [
        {
            "id": str(ub.id),
            "badge_id": str(ub.badge_id),
            "badge_name": ub.badge.name if ub.badge else None,
            "badge_description": ub.badge.description if ub.badge else None,
            "badge_icon": ub.badge.icon if ub.badge else None,
            "badge_category": ub.badge.category if ub.badge else None,
            "awarded_at": ub.awarded_at.isoformat() if ub.awarded_at else None,
        }
        for ub in user_badges
    ]


# ---------------------------------------------------------------------------
# POST /users — create user (admin only)
# ---------------------------------------------------------------------------


@router.post("", status_code=201)
async def create_user(
    body: UserCreate,
    db: AsyncSession = Depends(get_db),
    admin: User = Depends(get_admin_user),
):
    # Check duplicate email
    existing = await db.execute(select(User).where(User.email == body.email.lower()))
    if existing.scalar_one_or_none():
        raise HTTPException(status_code=400, detail="Email already registered")

    # Validate department if provided
    if body.department_id:
        dept = (await db.execute(select(Department).where(Department.id == body.department_id))).scalar_one_or_none()
        if not dept:
            raise HTTPException(status_code=400, detail="Department not found")

    user = User(
        email=body.email.lower(),
        full_name=body.full_name,
        employee_id=body.employee_id,
        department_id=body.department_id,
        role=body.role,
        github_username=body.github_username,
        udemy_email=body.udemy_email,
    )
    db.add(user)
    await db.flush()
    await db.refresh(user, attribute_names=["department"])

    return _user_to_dict(user)


# ---------------------------------------------------------------------------
# PUT /users/{user_id} — update user (admin only)
# ---------------------------------------------------------------------------


@router.put("/{user_id}")
async def update_user(
    user_id: UUID,
    body: UserUpdate,
    db: AsyncSession = Depends(get_db),
    admin: User = Depends(get_admin_user),
):
    result = await db.execute(
        select(User).options(selectinload(User.department)).where(User.id == user_id)
    )
    user = result.scalar_one_or_none()
    if not user:
        raise HTTPException(status_code=404, detail="User not found")

    # Validate department if changing
    if body.department_id is not None:
        dept = (await db.execute(select(Department).where(Department.id == body.department_id))).scalar_one_or_none()
        if not dept:
            raise HTTPException(status_code=400, detail="Department not found")

    update_data = body.model_dump(exclude_unset=True)
    for field, value in update_data.items():
        setattr(user, field, value)

    await db.flush()
    await db.refresh(user, attribute_names=["department"])

    return _user_to_dict(user)


# ---------------------------------------------------------------------------
# GET /users/{user_id}/tool-usage — monthly tool usage summary
# ---------------------------------------------------------------------------


@router.get("/{user_id}/tool-usage")
async def get_user_tool_usage(
    user_id: UUID,
    months: int = Query(6, ge=1, le=24, description="Number of recent months to return"),
    db: AsyncSession = Depends(get_db),
    _=Depends(get_current_user),
):
    user = (await db.execute(select(User).where(User.id == user_id))).scalar_one_or_none()
    if not user:
        raise HTTPException(status_code=404, detail="User not found")

    # Tool usage activities are category='tool_usage' with period_key like '2025-03'
    result = await db.execute(
        select(
            ActivityCompletion.period_key,
            Activity.platform,
            Activity.title,
            ActivityCompletion.xp_awarded,
            ActivityCompletion.status,
            ActivityCompletion.trust_tier,
        )
        .join(Activity, ActivityCompletion.activity_id == Activity.id)
        .where(
            ActivityCompletion.user_id == user_id,
            Activity.category == "tool_usage",
            ActivityCompletion.period_key.isnot(None),
        )
        .order_by(ActivityCompletion.period_key.desc())
    )
    rows = result.all()

    # Group by period_key
    usage_by_month: dict[str, list[dict]] = {}
    for period_key, platform, title, xp, status, trust_tier in rows:
        if period_key not in usage_by_month:
            usage_by_month[period_key] = []
        usage_by_month[period_key].append(
            {
                "platform": platform,
                "title": title,
                "xp_awarded": xp,
                "status": status,
                "trust_tier": trust_tier,
            }
        )

    # Return most recent N months
    sorted_months = sorted(usage_by_month.keys(), reverse=True)[:months]
    return [
        {"period": m, "tools": usage_by_month[m]}
        for m in sorted_months
    ]