"""
Admin Management API Endpoints
GET /api/v1/admin/roster - Full roster with parent emails
GET /api/v1/admin/coaches - All coaches with case counts
GET /api/v1/admin/stats - Extended stats by sport and grade
"""
from typing import List, Optional, Dict
from datetime import datetime, timedelta
from fastapi import APIRouter, Depends, HTTPException, Query, status, Request
from sqlalchemy.orm import Session
from sqlalchemy import and_
from pydantic import BaseModel

from app.database import get_db
from app.models import Athlete, Case, Event, School, User, Milestone
from app.auth import get_current_user, require_role


router = APIRouter(tags=["admin"])


# ============== SCHEMAS ==============
class AdminRosterAthlete(BaseModel):
    """Athlete row in admin roster"""
    id: str
    first_name: str
    last_name: str
    grade: int
    sports: List[str]
    team: Optional[str] = None
    current_status: str
    parent_emails: List[str] = []
    active_case_count: int = 0


class AdminRosterResponse(BaseModel):
    """Admin roster response"""
    athletes: List[AdminRosterAthlete]
    total: int


class AdminCoachSummary(BaseModel):
    """Coach summary in admin view"""
    id: str
    first_name: str
    last_name: str
    email: str
    assigned_sports: List[str]
    assigned_teams: List[str]
    active_cases_count: int = 0


class AdminCoachesResponse(BaseModel):
    """Admin coaches response"""
    coaches: List[AdminCoachSummary]


class SportStats(BaseModel):
    """Stats by sport"""
    name: str
    athletes: int
    active_cases: int
    coaches: int
    ats: int


class GradeStats(BaseModel):
    """Stats by grade"""
    grade: int
    athletes: int
    cases: int


class AdminStatsResponse(BaseModel):
    """Admin extended stats"""
    total_athletes: int
    active_cases: int
    out_today: int
    modified: int
    by_sport: List[SportStats]
    by_grade: List[GradeStats]


# ============== ADMIN ROSTER ENDPOINT ==============
@router.get("/roster", response_model=AdminRosterResponse)
async def get_admin_roster(
    request: Request,
    school_id: str = Query(..., description="School identifier"),
    current_user: User = Depends(require_role(["admin", "at", "ad"])),
    db: Session = Depends(get_db),
):
    """
    Get full roster with management data for admin/AT
    
    Returns athletes with parent emails resolved from users table
    """
    if current_user.school_id != school_id:
        raise HTTPException(
            status_code=status.HTTP_403_FORBIDDEN,
            detail="Access denied for this school"
        )
    
    school = db.query(School).filter(School.id == school_id).first()
    if not school:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail=f"School '{school_id}' not found"
        )
    
    # Get all athletes
    athletes = db.query(Athlete).filter(
        Athlete.school_id == school_id
    ).order_by(Athlete.last_name, Athlete.first_name).all()
    
    # Get all users for email resolution
    all_users = db.query(User).filter(User.school_id == school_id).all()
    user_map = {u.id: u.email for u in all_users}
    
    # Build active case counts
    active_case_counts = {}
    active_cases = db.query(Case).filter(
        and_(Case.school_id == school_id, Case.status == "active")
    ).all()
    for c in active_cases:
        active_case_counts[c.athlete_id] = active_case_counts.get(c.athlete_id, 0) + 1
    
    roster_athletes = []
    for a in athletes:
        # Resolve parent emails
        parent_emails = []
        for pid in (a.parent_ids or []):
            if pid in user_map:
                parent_emails.append(user_map[pid])
        
        roster_athletes.append(AdminRosterAthlete(
            id=a.id,
            first_name=a.first_name,
            last_name=a.last_name,
            grade=a.grade,
            sports=a.sports or [],
            team=a.team,
            current_status=a.current_status,
            parent_emails=parent_emails,
            active_case_count=active_case_counts.get(a.id, 0)
        ))
    
    return AdminRosterResponse(
        athletes=roster_athletes,
        total=len(roster_athletes)
    )


# ============== ADMIN COACHES ENDPOINT ==============
@router.get("/coaches", response_model=AdminCoachesResponse)
async def get_admin_coaches(
    request: Request,
    school_id: str = Query(..., description="School identifier"),
    current_user: User = Depends(require_role(["admin", "at", "ad"])),
    db: Session = Depends(get_db),
):
    """
    Get all coaches for this school with case counts
    """
    if current_user.school_id != school_id:
        raise HTTPException(
            status_code=status.HTTP_403_FORBIDDEN,
            detail="Access denied for this school"
        )
    
    school = db.query(School).filter(School.id == school_id).first()
    if not school:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail=f"School '{school_id}' not found"
        )
    
    # Get all coaches for this school
    coaches = db.query(User).filter(
        and_(User.school_id == school_id, User.role == "coach")
    ).order_by(User.last_name, User.first_name).all()
    
    # Get active case count per sport
    active_cases = db.query(Case).filter(
        and_(Case.school_id == school_id, Case.status == "active")
    ).all()
    
    # Get athletes to resolve sport from case
    all_athletes = db.query(Athlete).filter(Athlete.school_id == school_id).all()
    athlete_map = {a.id: a for a in all_athletes}
    
    # Count active cases per sport
    sport_case_count = {}
    for c in active_cases:
        athlete = athlete_map.get(c.athlete_id)
        if athlete and athlete.sports:
            for s in athlete.sports:
                sport_case_count[s] = sport_case_count.get(s, 0) + 1
    
    coach_list = []
    for coach in coaches:
        assigned_sports = coach.assigned_sports or []
        coach_active_count = sum(sport_case_count.get(s, 0) for s in assigned_sports)
        
        coach_list.append(AdminCoachSummary(
            id=coach.id,
            first_name=coach.first_name,
            last_name=coach.last_name,
            email=coach.email,
            assigned_sports=assigned_sports,
            assigned_teams=coach.assigned_teams or [],
            active_cases_count=coach_active_count
        ))
    
    return AdminCoachesResponse(coaches=coach_list)


# ============== ADMIN STATS ENDPOINT ==============
@router.get("/stats", response_model=AdminStatsResponse)
async def get_admin_stats(
    request: Request,
    school_id: str = Query(..., description="School identifier"),
    current_user: User = Depends(require_role(["admin", "at", "ad"])),
    db: Session = Depends(get_db),
):
    """
    Get extended admin stats broken down by sport and grade
    """
    if current_user.school_id != school_id:
        raise HTTPException(
            status_code=status.HTTP_403_FORBIDDEN,
            detail="Access denied for this school"
        )
    
    school = db.query(School).filter(School.id == school_id).first()
    if not school:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail=f"School '{school_id}' not found"
        )
    
    # Get all athletes
    athletes = db.query(Athlete).filter(Athlete.school_id == school_id).all()
    total_athletes = len(athletes)
    
    # Stats
    out_count = sum(1 for a in athletes if a.current_status == "out")
    modified_count = sum(1 for a in athletes if a.current_status == "restricted")
    
    # Active cases
    active_cases = db.query(Case).filter(
        and_(Case.school_id == school_id, Case.status == "active")
    ).all()
    active_case_count = len(active_cases)
    
    # Get coaches per sport
    coaches = db.query(User).filter(
        and_(User.school_id == school_id, User.role == "coach")
    ).all()
    
    # Get ATs
    ats = db.query(User).filter(
        and_(User.school_id == school_id, User.role == "at")
    ).all()
    
    # Map athlete_id to case for sport resolution
    athlete_case_map = {c.athlete_id: c for c in active_cases}
    
    # Build sport stats
    all_sports = set()
    for a in athletes:
        for s in (a.sports or []):
            all_sports.add(s)
    
    by_sport = []
    for sport in sorted(all_sports):
        sport_athletes = [a for a in athletes if sport in (a.sports or [])]
        sport_active_cases = sum(1 for a in sport_athletes if a.id in athlete_case_map)
        sport_coaches = sum(1 for c in coaches if sport in (c.assigned_sports or []))
        sport_ats = len(ats) if ats else 0
        
        by_sport.append(SportStats(
            name=sport,
            athletes=len(sport_athletes),
            active_cases=sport_active_cases,
            coaches=sport_coaches,
            ats=sport_ats
        ))
    
    by_sport.sort(key=lambda x: x.athletes, reverse=True)
    
    # Build grade stats
    grade_counts = {}
    athlete_map = {a.id: a for a in athletes}
    for a in athletes:
        g = a.grade
        if g not in grade_counts:
            grade_counts[g] = {"athletes": 0, "cases": 0}
        grade_counts[g]["athletes"] += 1
    
    for c in active_cases:
        athlete = athlete_map.get(c.athlete_id)
        if athlete and athlete.grade in grade_counts:
            grade_counts[athlete.grade]["cases"] += 1
    
    by_grade = []
    for grade in sorted(grade_counts.keys()):
        by_grade.append(GradeStats(
            grade=grade,
            athletes=grade_counts[grade]["athletes"],
            cases=grade_counts[grade]["cases"]
        ))
    
    return AdminStatsResponse(
        total_athletes=total_athletes,
        active_cases=active_case_count,
        out_today=out_count,
        modified=modified_count,
        by_sport=by_sport,
        by_grade=by_grade
    )