app/api/admin.py

"""
Admin API endpoints (Team/Organization Management).
"""
from uuid import uuid4
from datetime import datetime
from typing import Optional, List
from fastapi import APIRouter, Depends, HTTPException, Query, status, UploadFile, File, BackgroundTasks

from app.dependencies import (
    get_supabase, 
    get_current_user,
    get_current_user_with_db,
    require_team_account, 
    require_organization_admin,
    require_staff_member,
    require_linked_account
)
from app.services.supabase_client import SupabaseService
from app.models.user import User, AccountType
from app.models.player import Player, PlayerCreate, PlayerUpdate
from app.models.schedule import Schedule, ScheduleCreate, ScheduleUpdate
from app.models.match import Match, MatchCreate, MatchUpdate
from app.models.notification import Notification, NotificationCreate, NotificationListResponse
from app.models.team import Organization, OrganizationUpdate
from app.models.stats import MatchStatUploadResponse, StatsConfirmRequest
from app.services.stats_extraction_service import extract_stats_from_file_background
from app.services.player_linking_service import auto_link_players_to_roster

router = APIRouter()

@router.put("/organization", response_model=Organization)
async def update_organization(
    org_data: OrganizationUpdate,
    current_user: dict = Depends(require_organization_admin),
    supabase: SupabaseService = Depends(get_supabase),
):
    """
    Update details of the current user's organization.
    """
    # Find organization owned by current user
    orgs = await supabase.select("organizations", filters={"owner_id": current_user["id"]})
    if not orgs:
        raise HTTPException(status_code=404, detail="Organization not found")
    
    org_id = orgs[0]["id"]
    update_dict = org_data.model_dump(exclude_unset=True)
    
    if not update_dict:
        raise HTTPException(status_code=400, detail="No fields to update")
        
    updated = await supabase.update("organizations", org_id, update_dict)
    return updated

# ============================================
# USERS & PLAYERS MANAGEMENT
# ============================================

@router.put("/users/{user_id}/role")
async def update_user_role(
    user_id: str,
    role_data: dict,
    current_user: dict = Depends(require_staff_member),
    supabase: SupabaseService = Depends(get_supabase),
):
    """
    Update a user's role (e.g. within the team context, or maybe account_type).
    """
    # Verify ownership/permission
    # Assuming this updates metadata or account_type?
    # User roles in this system seem to be 'account_type'.
    # If frontend means 'player role' (Forward, Guard), that's usually on player profile.
    # adminAPI.js calls `updateUserRole`.
    
    # If it's account_type (team/personal), that's critical.
    # If it's a team role (Captain, Starter), that's not in Users table.
    
    # Assuming it updates 'role' metadata for now.
    user_update = {"role": role_data.get("role")}
    updated = await supabase.update("users", user_id, user_update)
    return updated


@router.get("/users")
async def get_users(
    role: Optional[str] = None,
    current_user: dict = Depends(require_staff_member),
    supabase: SupabaseService = Depends(get_supabase),
):
    """
    Get users (players) managed by the admin/team owner.
    """
    # Determine organization_id
    org_id = current_user.get("organization_id")
    if not org_id and current_user.get("account_type") == AccountType.TEAM.value:
        orgs = await supabase.select("organizations", filters={"owner_id": current_user["id"]})
        if not orgs:
            return []
        org_id = orgs[0]["id"]
    
    if not org_id:
        return []

    # Get players in the org
    players = await supabase.select("players", filters={"organization_id": str(org_id)})
    
    # Identify linked users to fetch their personal profiles for data fallback
    linked_user_ids = [str(p["user_id"]) for p in players if p.get("user_id")]
    personal_profiles = {}
    
    if linked_user_ids:
        try:
            # Batch fetch all potential personal profiles for these users
            all_profiles = await supabase.select_in("players", "user_id", linked_user_ids)
            # Filter for true personal profiles (no organization_id)
            for prof in all_profiles:
                if not prof.get("organization_id"):
                    personal_profiles[str(prof["user_id"])] = prof
        except Exception as e:
            print(f"Warning: Failed to fetch personal profiles for roster fallback: {e}")

    # Ensure all fields for the roster are present, falling back to personal data if team data is blank
    for p in players:
        user_id_str = str(p.get("user_id")) if p.get("user_id") else None
        personal = personal_profiles.get(user_id_str) if user_id_str else None
        
        # Helper to get value with fallback
        def get_val(field, default=None):
            v = p.get(field)
            # If team-specific value exists and isn't empty, use it
            if v is not None and v != "":
                return v
            # Otherwise, try to use the player's personal profile value
            if personal and personal.get(field) is not None and personal.get(field) != "":
                return personal[field]
            return default

        p["jersey_number"] = get_val("jersey_number", None)
        p["position"] = get_val("position", "Not set")
        p["status"] = get_val("status", "active")
        # PPG is typically calculated per organization/team context
        p_ppg = p.get("ppg")
        p["ppg"] = float(p_ppg) if p_ppg is not None and p_ppg != "" else 0.0
        
    return players

@router.post("/players")
async def create_player(
    player_data: PlayerCreate,
    current_user: dict = Depends(require_staff_member),
    supabase: SupabaseService = Depends(get_supabase),
):
    """
    Add a new player to the team roster.
    """
    org_id = current_user.get("organization_id")
    if not org_id and current_user.get("account_type") == AccountType.TEAM.value:
        orgs = await supabase.select("organizations", filters={"owner_id": current_user["id"]})
        if not orgs:
            raise HTTPException(status_code=400, detail="No organization found for this account")
        org_id = orgs[0]["id"]
    
    if not org_id:
         raise HTTPException(status_code=403, detail="Account not linked to an organization")
    player_dict = player_data.model_dump()
    player_dict["organization_id"] = str(org_id)
    player_dict["id"] = str(uuid4())
    player_dict["created_at"] = datetime.now().isoformat()
    
    # Handle date_of_birth if present
    if player_dict.get("date_of_birth"):
        player_dict["date_of_birth"] = player_dict["date_of_birth"].isoformat()
        
    saved = await supabase.insert("players", player_dict)
    return saved

@router.put("/players/{player_id}")
async def update_player(
    player_id: str,
    player_data: PlayerUpdate,
    current_user: dict = Depends(require_staff_member),
    supabase: SupabaseService = Depends(get_supabase),
):
    """
    Update a player's profile.
    """
    # Verify access
    player = await supabase.select_one("players", player_id)
    if not player:
        raise HTTPException(status_code=404, detail="Player not found")
        
    has_access = False
    if current_user.get("account_type") == AccountType.TEAM.value:
        if player.get("organization_id"):
            org = await supabase.select_one("organizations", str(player["organization_id"]))
            has_access = org and str(org.get("owner_id")) == str(current_user["id"])
    elif current_user.get("account_type") == AccountType.COACH.value:
        coach_org = current_user.get("organization_id")
        if coach_org and str(player.get("organization_id")) == str(coach_org):
            has_access = True
            
    if not has_access:
        raise HTTPException(status_code=403, detail="Access denied")
        
    update_dict = player_data.model_dump(exclude_unset=True)
    if "date_of_birth" in update_dict and update_dict["date_of_birth"]:
        update_dict["date_of_birth"] = update_dict["date_of_birth"].isoformat()
        
    updated = await supabase.update("players", player_id, update_dict)
    return updated

@router.get("/players")
async def get_roster(
    current_user: dict = Depends(require_staff_member),
    supabase: SupabaseService = Depends(get_supabase),
):
    """
    Get team roster (alias for players).
    """
    return await get_users(role="player", current_user=current_user, supabase=supabase)

@router.patch("/players/{player_id}/status")
async def update_player_status(
    player_id: str,
    status_data: dict,
    current_user: dict = Depends(require_staff_member),
    supabase: SupabaseService = Depends(get_supabase),
):
    """
    Update player status (active/injured/etc).
    Note: status field might need to be added to players table or handled via metadata.
    """
    # Check if player belongs to owner's org
    player = await supabase.select_one("players", player_id)
    if not player:
        raise HTTPException(status_code=404, detail="Player not found")
        
    org_id = current_user.get("organization_id")
    if not org_id:
        orgs = await supabase.select("organizations", filters={"owner_id": current_user["id"]})
        if orgs:
            org_id = orgs[0]["id"]
            
    if not org_id or str(player.get("organization_id")) != str(org_id):
        raise HTTPException(status_code=403, detail="Access denied")

    try:
        updated = await supabase.update("players", player_id, {"status": status_data.get("status")})
        return updated
    except Exception:
        return player


@router.delete("/players/{player_id}")
async def delete_player(
    player_id: str,
    current_user: dict = Depends(require_staff_member),
    supabase: SupabaseService = Depends(get_supabase),
):
    """
    Remove a player from the organization's roster.
    This deletes the roster entry and unlinks the user account if linked.
    """
    # 1. Get player
    player = await supabase.select_one("players", player_id)
    if not player:
        raise HTTPException(status_code=404, detail="Player not found")
        
    # 2. Get organization id
    org_id = current_user.get("organization_id")
    if not org_id:
        orgs = await supabase.select("organizations", filters={"owner_id": current_user["id"]})
        if not orgs:
             raise HTTPException(status_code=404, detail="Organization not found")
        org_id = orgs[0]["id"]
        
    # 3. Verify ownership
    if player.get("organization_id") != org_id:
        raise HTTPException(status_code=403, detail="Access denied")

    # 4. Unlink user if associated
    linked_user_id = player.get("user_id")
    if linked_user_id:
        try:
            await supabase.update("users", str(linked_user_id), {"organization_id": None})
        except Exception as e:
            print(f"Warning: Failed to unlink user {linked_user_id}: {e}")

    # 5. Delete roster entry
    await supabase.delete("players", player_id)
    
    return {"message": "Player removed from roster and unlinked successfully"}

@router.post("/players/{player_id}/link")
async def link_player_account(
    player_id: str,
    link_data: dict,
    current_user: dict = Depends(require_staff_member),
    supabase: SupabaseService = Depends(get_supabase),
):
    """
    Link a roster player to an actual user account by email.
    If player_id is "new", it will search for the user and create a roster entry.
    """
    email = link_data.get("email")
    if not email:
        raise HTTPException(status_code=400, detail="Email is required")

    # 1. Get organization
    org_id = current_user.get("organization_id")
    if not org_id:
        try:
            orgs = await supabase.select("organizations", filters={"owner_id": current_user["id"]})
            if not orgs:
                # Auto-create shell organization for Team accounts to improve onboarding
                if current_user.get("account_type") == AccountType.TEAM.value:
                    print(f"Auto-creating shell organization for team user {current_user['id']}")
                    new_org = await supabase.insert("organizations", {
                        "id": str(uuid4()),
                        "owner_id": current_user["id"],
                        "name": current_user.get("name") or "My Team"
                    })
                    org_id = new_org["id"]
                    # Also link user to this org
                    await supabase.update("users", current_user["id"], {"organization_id": org_id})
                else:
                    raise HTTPException(status_code=404, detail="Organization not found. You must create an organization profile first.")
            else:
                org_id = orgs[0]["id"]
        except HTTPException:
            raise
        except Exception as e:
            print(f"Error fetching/creating organization for linking: {e}")
            raise HTTPException(status_code=500, detail="Error retrieving organization data")

    # 2. Search for user by email
    try:
        users = await supabase.select("users", filters={"email": email})
        if not users:
            raise HTTPException(status_code=404, detail=f"No account found with email {email}. The player must sign up first.")
        target_user = users[0]
    except HTTPException:
        raise
    except Exception as e:
        print(f"Error searching for user by email {email}: {e}")
        raise HTTPException(status_code=500, detail="Error searching for player account")
    
    # Check if already linked to another org
    current_linked_org = target_user.get("organization_id")
    if current_linked_org and str(current_linked_org) != str(org_id):
        raise HTTPException(status_code=400, detail="This user is already linked to another organization")

    # 3. Handle player roster entry
    org_name = "your team"
    if org_id:
        try:
            org_record = await supabase.select_one("organizations", str(org_id))
            if org_record:
                org_name = org_record.get("name", "your team")
        except Exception:
            pass

    try:
        if player_id == "new":
            # Check if already in roster
            existing_roster = await supabase.select("players", filters={
                "organization_id": str(org_id),
                "user_id": str(target_user["id"])
            })
            if existing_roster:
                raise HTTPException(status_code=400, detail="This player is already in your roster")
            
            # Search for an existing personal player profile for this user
            personal_profiles = await supabase.select("players", filters={"user_id": str(target_user["id"])})
            # Filter for the one without an org (personal)
            personal_profile = next((p for p in personal_profiles if not p.get("organization_id")), None)

            # Create new roster entry
            new_player_id = str(uuid4())
            new_player_record = {
                "id": new_player_id,
                "organization_id": str(org_id),
                "user_id": str(target_user["id"]),
                "name": target_user.get("full_name") or target_user.get("email") or "New Player",
                "created_at": datetime.now().isoformat()
            }

            # If they have a personal profile, copy the stats
            if personal_profile:
                fields_to_copy = [
                    "jersey_number", "position", "height_cm", "weight_kg", 
                    "date_of_birth", "avatar_url", "phone", "address", 
                    "experience_years", "bio", "status"
                ]
                for field in fields_to_copy:
                    if personal_profile.get(field) is not None:
                        new_player_record[field] = personal_profile[field]

            await supabase.insert("players", new_player_record)
            player_id = new_player_id
        else:
            # Verify existing player belongs to owner's org
            player = await supabase.select_one("players", player_id)
            if not player:
                raise HTTPException(status_code=404, detail="Player not found")
            if str(player.get("organization_id")) != str(org_id):
                raise HTTPException(status_code=403, detail="Access denied")
            
            # Update player profile with user_id
            await supabase.update("players", player_id, {"user_id": target_user["id"]})
    except HTTPException:
        raise
    except Exception as e:
        print(f"Error updating/creating roster entry: {e}")
        raise HTTPException(status_code=500, detail=f"Failed to update team roster: {e}")
    
    # 4. Update user profile with organization_id
    try:
        await supabase.update("users", target_user["id"], {"organization_id": str(org_id)})
    except Exception as e:
        print(f"Error updating user organization link: {e}")
        # Not a fatal error if roster was linked, but still problematic
    
    # 5. Create a notification
    try:
        await supabase.insert("notifications", {
            "id": str(uuid4()),
            "recipient_id": target_user["id"],
            "title": "Team Link",
            "message": f"You have been added to the team roster of {org_name}.",
            "type": "team_invite",
            "read": False,
            "created_at": datetime.now().isoformat()
        })
    except Exception as e:
        print(f"Failed to create link notification: {e}")

    return {"message": "Account linked successfully", "user": {"id": target_user["id"], "name": target_user.get("full_name") or target_user.get("email")}}

@router.get("/staff")
async def get_staff(
    current_user: dict = Depends(require_staff_member),
    supabase: SupabaseService = Depends(get_supabase),
):
    """
    Get coaching staff linked to the organization.
    """
    org_id = current_user.get("organization_id")
    if not org_id:
        orgs = await supabase.select("organizations", filters={"owner_id": current_user["id"]})
        if not orgs:
             return []
        org_id = orgs[0]["id"]
        
    staff = await supabase.select("users", filters={
        "organization_id": org_id,
        "account_type": "coach"
    })
    return staff



@router.post("/staff-link")
@router.post("/staff-link/")
async def link_staff_member(
    link_data: dict,
    current_user: dict = Depends(require_team_account),
    supabase: SupabaseService = Depends(get_supabase),
):
    """
    Link a coach account to the organization and assign a role.
    """
    email = link_data.get("email")
    role = link_data.get("role", "Coach")
    
    print(f"DEBUG: Staff linking attempt for {email} by admin {current_user.get('email')}")
    
    if not email:
        raise HTTPException(status_code=400, detail="Email is required")

    # 1. Get organization
    org_id = current_user.get("organization_id")
    if not org_id:
        try:
            orgs = await supabase.select("organizations", filters={"owner_id": current_user["id"]})
            if not orgs:
                # Auto-create shell organization for Team accounts
                if current_user.get("account_type") == AccountType.TEAM.value:
                    print(f"Auto-creating shell organization for team user {current_user['id']} (staff link)")
                    new_org = await supabase.insert("organizations", {
                        "id": str(uuid4()),
                        "owner_id": current_user["id"],
                        "name": current_user.get("name") or "My Team"
                    })
                    org_id = new_org["id"]
                    # Also link user to this org
                    await supabase.update("users", current_user["id"], {"organization_id": org_id})
                else:
                    raise HTTPException(status_code=404, detail="Organization not found")
            else:
                org_id = orgs[0]["id"]
        except HTTPException:
            raise
        except Exception as e:
            print(f"Error fetching/creating organization for staff linking: {e}")
            raise HTTPException(status_code=500, detail="Error retrieving organization data")
    
    # 2. Search for user by email (case-insensitive handling)
    search_email = email.strip().lower()
    users = await supabase.select("users", filters={"email": search_email})
    
    if not users:
        # Try finding with exact casing just in case
        users = await supabase.select("users", filters={"email": email.strip()})
        
    if not users:
        raise HTTPException(status_code=404, detail=f"No account found with email {email}. The coach must sign up first.")
    
    target_user = users[0]
    
    if target_user["account_type"] not in ["coach", "personal"]:
         raise HTTPException(status_code=400, detail=f"This account is a {target_user['account_type']} account. Staff members must have a Coach or Personal account.")

    if target_user.get("organization_id"):
        if str(target_user.get("organization_id")) == str(org_id):
            raise HTTPException(status_code=400, detail="This coach is already linked to your organization.")
        else:
            raise HTTPException(status_code=400, detail="This coach is already linked to another organization.")

    # 3. Update user profile
    await supabase.update("users", target_user["id"], {
        "organization_id": org_id,
        "staff_role": role
    })
    
    # 4. Create notification
    try:
        await supabase.insert("notifications", {
            "id": str(uuid4()),
            "recipient_id": target_user["id"],
            "title": "Staff Invitation",
            "message": f"You have been added as a {role} to the organization.",
            "type": "team_invite",
            "read": False,
            "created_at": datetime.now().isoformat()
        })
    except Exception:
        pass

    return {"message": "Staff member linked successfully", "user": {"id": target_user["id"], "role": role}}

@router.delete("/staff/{user_id}")
async def remove_staff_member(
    user_id: str,
    current_user: dict = Depends(require_organization_admin),
    supabase: SupabaseService = Depends(get_supabase),
):
    """
    Remove a staff member from the organization.
    """
    org_id = current_user.get("organization_id")
    if not org_id:
        orgs = await supabase.select("organizations", filters={"owner_id": current_user["id"]})
        if not orgs:
             raise HTTPException(status_code=404, detail="Organization not found")
        org_id = orgs[0]["id"]

    # Verify user belongs to org
    target_user = await supabase.select_one("users", user_id)
    if not target_user or target_user.get("organization_id") != org_id:
        raise HTTPException(status_code=403, detail="Access denied or user not found")

    # Unlink
    await supabase.update("users", user_id, {
        "organization_id": None,
        "staff_role": None
    })
    
    return {"message": "Staff member removed successfully"}

# ============================================
# SCHEDULE MANAGEMENT
# ============================================

@router.get("/schedule")
async def get_schedule(
    current_user: dict = Depends(require_staff_member),
    supabase: SupabaseService = Depends(get_supabase),
):
    """Get team schedule."""
    org_id = current_user.get("organization_id")
    if not org_id:
        orgs = await supabase.select("organizations", filters={"owner_id": current_user["id"]})
        if not orgs:
            return []
        org_id = orgs[0]["id"]
        
    schedules = await supabase.select("schedules", filters={"organization_id": org_id})
    return schedules

@router.post("/schedule")
async def create_schedule_event(
    event_data: ScheduleCreate,
    current_user: dict = Depends(require_staff_member),
    supabase: SupabaseService = Depends(get_supabase),
):
    """Create a schedule event (Coach only)."""
    org_id = current_user.get("organization_id")
    if not org_id:
        raise HTTPException(status_code=403, detail="You must be linked to an organization")
        
    event_dict = event_data.model_dump()
    event_dict["organization_id"] = str(org_id)
    event_dict["created_by"] = current_user["id"]
    event_dict["id"] = str(uuid4())
    
    event_dict["start_time"] = event_dict["start_time"].isoformat()
    event_dict["end_time"] = event_dict["end_time"].isoformat()
    
    saved = await supabase.insert("schedules", event_dict)
    return saved

@router.put("/schedule/{event_id}")
async def update_schedule_event(
    event_id: str,
    event_data: ScheduleUpdate,
    current_user: dict = Depends(require_staff_member),
    supabase: SupabaseService = Depends(get_supabase),
):
    """Update a schedule event (Coach only)."""
    # Verify owner's org match
    event = await supabase.select_one("schedules", event_id)
    if not event or event.get("organization_id") != current_user.get("organization_id"):
        raise HTTPException(status_code=403, detail="Access denied")

    update_dict = event_data.model_dump(exclude_unset=True)
    if not update_dict:
        raise HTTPException(status_code=400, detail="No fields to update")
        
    if "start_time" in update_dict:
        update_dict["start_time"] = update_dict["start_time"].isoformat()
    if "end_time" in update_dict:
        update_dict["end_time"] = update_dict["end_time"].isoformat()
        
    updated = await supabase.update("schedules", event_id, update_dict)
    return updated

@router.delete("/schedule/{event_id}")
async def delete_schedule_event(
    event_id: str,
    current_user: dict = Depends(require_staff_member),
    supabase: SupabaseService = Depends(get_supabase),
):
    """Delete a schedule event (Coach only)."""
    event = await supabase.select_one("schedules", event_id)
    if not event or event.get("organization_id") != current_user.get("organization_id"):
        raise HTTPException(status_code=403, detail="Access denied")

    await supabase.delete("schedules", event_id)
    return {"message": "Event deleted"}
# ============================================
# MATCH MANAGEMENT
# ============================================

@router.get("/matches")
async def get_matches(
    current_user: dict = Depends(require_staff_member),
    supabase: SupabaseService = Depends(get_supabase),
):
    """Get matches for the current user's organization (Owner or Staff)."""
    org_id = current_user.get("organization_id")
    if not org_id:
        orgs = await supabase.select("organizations", filters={"owner_id": current_user["id"]})
        if not orgs:
            return []
        org_id = orgs[0]["id"]
        
    matches = await supabase.select("matches", filters={"organization_id": org_id})
    return matches

@router.post("/matches")
async def create_match(
    match_data: MatchCreate,
    current_user: dict = Depends(require_staff_member),
    supabase: SupabaseService = Depends(get_supabase),
):
    """Create a match (Coach only)."""
    org_id = current_user.get("organization_id")
    if not org_id:
        raise HTTPException(status_code=403, detail="You must be linked to an organization")
        
    match_dict = match_data.model_dump()
    match_dict["id"] = str(uuid4())
    match_dict["organization_id"] = str(org_id)
    match_dict["date"] = match_dict["date"].isoformat()
    
    saved = await supabase.insert("matches", match_dict)
    return saved

@router.put("/matches/{match_id}")
async def update_match(
    match_id: str,
    match_data: MatchUpdate,
    current_user: dict = Depends(require_staff_member),
    supabase: SupabaseService = Depends(get_supabase),
):
    """Update a match (Coach only)."""
    match = await supabase.select_one("matches", match_id)
    if not match or match.get("organization_id") != current_user.get("organization_id"):
        raise HTTPException(status_code=403, detail="Access denied")

    update_dict = match_data.model_dump(exclude_unset=True)
    if "date" in update_dict:
        update_dict["date"] = update_dict["date"].isoformat()
        
    updated = await supabase.update("matches", match_id, update_dict)
    return updated

@router.delete("/matches/{match_id}")
async def delete_match(
    match_id: str,
    current_user: dict = Depends(require_staff_member),
    supabase: SupabaseService = Depends(get_supabase),
):
    """Delete a match (Coach only)."""
    match = await supabase.select_one("matches", match_id)
    if not match or match.get("organization_id") != current_user.get("organization_id"):
        raise HTTPException(status_code=403, detail="Access denied")

    await supabase.delete("matches", match_id)
    return {"message": "Match deleted"}


@router.get("/matches/{match_id}/player-stats")
async def get_match_player_stats(
    match_id: str,
    current_user: dict = Depends(require_staff_member),
    supabase: SupabaseService = Depends(get_supabase),
):
    """Get all player stats for a specific match, enriched with player name/jersey info."""
    # Verify org access
    org_id = current_user.get("organization_id")
    if not org_id:
        orgs = await supabase.select("organizations", filters={"owner_id": current_user["id"]})
        if not orgs:
            return []
        org_id = orgs[0]["id"]

    stats = await supabase.select("match_player_stats", filters={
        "match_id": match_id,
        "organization_id": org_id
    })

    # Enrich each stat row with player name/jersey from the players table
    player_ids = list({s["player_profile_id"] for s in stats if s.get("player_profile_id")})
    players = []
    if player_ids:
        players = await supabase.select_in("players", "id", player_ids)
    player_map = {p["id"]: p for p in players}

    for s in stats:
        pid = s.get("player_profile_id")
        p = player_map.get(pid, {})
        s["player_name"] = p.get("name", "Unknown")
        s["player_jersey"] = p.get("jersey_number")
        s["player_position"] = p.get("position")

    match = await supabase.select_one("matches", match_id)
    return {"match": match, "stats": stats}

# ============================================
# NOTIFICATIONS & STATS
# ============================================

@router.get("/notifications")
async def get_notifications(
    current_user: dict = Depends(get_current_user_with_db),
    supabase: SupabaseService = Depends(get_supabase),
):
    try:
        user_id = current_user["id"]
        # Basic validation for UUID if using Postgres
        import uuid
        try:
            uuid.UUID(str(user_id))
        except ValueError:
            # If not a UUID (like dev-id-...), return empty list instead of crashing Postgres
            return []

        notifs = await supabase.select("notifications", filters={"recipient_id": str(user_id)}, order_by="created_at")
        return notifs
    except Exception as e:
        print(f"Error in get_notifications: {e}")
        return [] # Return empty list on error to keep UI stable

@router.post("/notifications")
async def create_notification(
    notif_data: NotificationCreate,
    current_user: dict = Depends(require_staff_member),
    supabase: SupabaseService = Depends(get_supabase),
):
    notif_dict = notif_data.model_dump()
    notif_dict["id"] = str(uuid4())
    saved = await supabase.insert("notifications", notif_dict)
    return saved

@router.put("/notifications/{notification_id}/mark-as-read")
@router.put("/notifications/{notification_id}/read")
async def mark_notification_read(
    notification_id: str,
    current_user: dict = Depends(get_current_user_with_db),
    supabase: SupabaseService = Depends(get_supabase),
):
    updated = await supabase.update("notifications", notification_id, {"read": True})
    return updated

@router.put("/notifications/read-all")
async def mark_all_notifications_read(
    current_user: dict = Depends(get_current_user_with_db),
    supabase: SupabaseService = Depends(get_supabase),
):
    # Mark all unread notifications for the current user as read
    notifs = await supabase.select("notifications", filters={"recipient_id": current_user["id"], "read": False})
    for n in notifs:
         await supabase.update("notifications", n["id"], {"read": True})
    return {"message": "All notifications marked as read"}

@router.put("/notifications/{notification_id}")
async def update_notification(
    notification_id: str,
    notification_data: NotificationCreate,
    current_user: dict = Depends(require_staff_member),
    supabase: SupabaseService = Depends(get_supabase),
):
    update_dict = notification_data.model_dump(exclude_unset=True)
    updated = await supabase.update("notifications", notification_id, update_dict)
    return updated

@router.delete("/notifications/{notification_id}")
async def delete_notification(
    notification_id: str,
    current_user: dict = Depends(get_current_user_with_db),
    supabase: SupabaseService = Depends(get_supabase),
):
    await supabase.delete("notifications", notification_id)
    return {"message": "Notification deleted"}


@router.get("/stats")
async def get_stats(
    current_user: dict = Depends(require_staff_member),
    supabase: SupabaseService = Depends(get_supabase),
):
    # Mock aggregation or real counts
    org_id = current_user.get("organization_id")
    if not org_id:
        # Fallback for owners who might not have org_id in token yet
        orgs = await supabase.select("organizations", filters={"owner_id": current_user["id"]})
        if not orgs:
            return {"total_players": 0, "total_matches": 0, "videos_analyzed": 0, "win_rate": 0, "organization_id": None}
        org_id = orgs[0]["id"]
    players = await supabase.select("players", filters={"organization_id": org_id})
    matches = await supabase.select("matches", filters={"organization_id": org_id})
    videos = await supabase.select("videos", filters={"organization_id": org_id})
    
    # Calculate win rate
    played_matches = [m for m in matches if m.get("score_us") is not None and m.get("score_them") is not None]
    wins = len([m for m in played_matches if (m.get("score_us") or 0) > (m.get("score_them") or 0)])
    win_rate = round((wins / len(played_matches) * 100), 1) if played_matches else 0
    
    # Generate some performance trend data for the frontend chart
    performance_data = []
    # Just a simple trend for the last 7 days or match entries
    for i in range(7):
        performance_data.append({
            "date": f"Day {i+1}",
            "players": len(players),
            "performance": win_rate or (70 + i), # Fallback to a nice looking trend if no matches
            "games": len(played_matches)
        })

    return {
        "total_players": len(players),
        "total_matches": len(matches),
        "videos_analyzed": len(videos),
        "upcoming_matches": len([m for m in matches if datetime.fromisoformat(m["date"].replace("Z", "+00:00")).replace(tzinfo=None) > datetime.utcnow()]),
        "win_rate": win_rate,
        "performance_data": performance_data,
        "organization_id": org_id
    }

# ============================================
# SECURITY & PROFILE 
# ============================================

@router.get("/profile")
async def get_profile(
    current_user: dict = Depends(require_staff_member),
    supabase: SupabaseService = Depends(get_supabase),
):
    # Get user info and associated organization
    user_info = current_user
    org_id = current_user.get("organization_id")
    
    # Try fetching as owner first
    orgs = await supabase.select("organizations", filters={"owner_id": current_user["id"]})
    
    # If not owner but has org_id (like a coach), fetch by ID
    if not orgs and org_id:
        org_record = await supabase.select_one("organizations", str(org_id))
        if org_record:
            orgs = [org_record]
            
    return {"user": user_info, "organization": orgs[0] if orgs else None}

@router.put("/profile")
async def update_profile(
    profile_data: dict,
    current_user: dict = Depends(require_staff_member),
    supabase: SupabaseService = Depends(get_supabase),
):
    # Update user or org
    if "user" in profile_data:
        user_payload = {}
        for k, v in profile_data["user"].items():
            if k in ["id", "created_at", "updated_at", "email", "account_type"]:
                continue
            user_payload[k] = v
        if user_payload:
            await supabase.update("users", current_user["id"], user_payload)
    if "organization" in profile_data:
        org_id = current_user.get("organization_id")
        orgs = []
        if org_id:
            org_record = await supabase.select_one("organizations", str(org_id))
            if org_record:
                orgs = [org_record]
        else:
            orgs = await supabase.select("organizations", filters={"owner_id": current_user["id"]})
            
        if orgs:
            # Avoid sending nested objects (which might not map to DB columns)
            org_payload = {}
            for k, v in (profile_data["organization"] or {}).items():
                # Do not try to update primary key or generated fields
                if k in ["id", "owner_id", "created_at", "updated_at"]:
                    continue
                org_payload[k] = v

            if org_payload:
                print(f"DEBUG update_profile: updating org {orgs[0]['id']} with payload: {org_payload}")
                try:
                    await supabase.update("organizations", orgs[0]["id"], org_payload)
                except Exception as e:
                    print(f"ERROR updating organization: {e}")
                    raise HTTPException(status_code=500, detail=f"Failed to update organization: {str(e)}")
        else:
            # Create new organization
            org_payload = {
                "owner_id": current_user["id"],
                "name": profile_data["organization"].get("name", "New Team")
            }
            for k, v in (profile_data["organization"] or {}).items():
                if k in ["id", "owner_id", "created_at", "updated_at"]:
                    continue
                org_payload[k] = v
            
            print(f"DEBUG update_profile: creating new org for user {current_user['id']} with payload: {org_payload}")
            try:
                new_org = await supabase.insert("organizations", org_payload)
                # Also link user to this org
                await supabase.update("users", current_user["id"], {"organization_id": new_org["id"]})
            except Exception as e:
                print(f"ERROR creating organization: {e}")
                raise HTTPException(status_code=500, detail=f"Failed to create organization: {str(e)}")
    return {"message": "Profile updated"}

@router.get("/security/settings")
async def get_security_settings(current_user: dict = Depends(get_current_user_with_db)):
    # Mock settings
    return {"two_factor_enabled": False, "login_alerts": True}

@router.put("/security/settings")
async def update_security_settings(settings: dict, current_user: dict = Depends(get_current_user_with_db)):
    # Mock update
    return settings

@router.get("/security/logs")
async def get_security_logs(current_user: dict = Depends(get_current_user_with_db)):
    # Mock logs
    return [
        {"id": 1, "action": "Login", "ip": "127.0.0.1", "timestamp": datetime.now().isoformat()},
    ]

# ============================================
# OFFICIAL STATS IMPORT
# ============================================

@router.post("/matches/{match_id}/stats-upload")
async def upload_match_stats(
    match_id: str,
    background_tasks: BackgroundTasks,
    file: UploadFile = File(...),
    current_user: dict = Depends(require_staff_member),
    supabase: SupabaseService = Depends(get_supabase),
):
    """Upload a box score PDF or image, save to storage, schedule extraction."""
    # 1. Verify match ownership
    match = await supabase.select_one("matches", match_id)
    org_id = current_user.get("organization_id")
    if not org_id:
        orgs = await supabase.select("organizations", filters={"owner_id": current_user["id"]})
        if orgs:
            org_id = orgs[0]["id"]
            
    if not match or match.get("organization_id") != org_id:
        raise HTTPException(status_code=403, detail="Access denied")
    
    org_id = match["organization_id"]
    upload_id = str(uuid4())
    
    # 2. Upload file to storage
    file_bytes = await file.read()
    is_pdf = file.content_type == "application/pdf"
    file_ext = "pdf" if is_pdf else "jpg"
    storage_path = f"org_{org_id}/match_{match_id}/{upload_id}.{file_ext}"
    
    try:
        url = await supabase.upload_file("match-stats", storage_path, file_bytes, file.content_type)
    except Exception as e:
        print(f"Warning: file upload skip or error: {e}")
        url = f"/local/{storage_path}"
        
    # 3. Create upload record
    upload_data = {
        "id": upload_id,
        "match_id": match_id,
        "organization_id": org_id,
        "uploaded_by": current_user["id"],
        "storage_path": storage_path,
        "file_type": "pdf" if is_pdf else "image",
        "extract_status": "queued",
        "created_at": datetime.now().isoformat(),
        "updated_at": datetime.now().isoformat()
    }
    await supabase.insert("match_stat_uploads", upload_data)
    
    # 4. Trigger extraction job
    background_tasks.add_task(extract_stats_from_file_background, upload_id, org_id)
    
    return {"upload_id": upload_id, "status": "queued"}

@router.get("/stats-upload/{upload_id}", response_model=MatchStatUploadResponse)
async def get_stats_upload(
    upload_id: str,
    current_user: dict = Depends(require_staff_member),
    supabase: SupabaseService = Depends(get_supabase),
):
    """Poll for extraction status and JSON preview."""
    org_id = current_user.get("organization_id")
    if not org_id:
        orgs = await supabase.select("organizations", filters={"owner_id": current_user["id"]})
        if orgs:
            org_id = orgs[0]["id"]

    upload = await supabase.select_one("match_stat_uploads", upload_id)
    if not upload or upload.get("organization_id") != org_id:
        raise HTTPException(status_code=404, detail="Upload not found")
        
    return upload

@router.post("/stats-upload/{upload_id}/confirm")
async def confirm_stats_upload(
    upload_id: str,
    confirm_data: StatsConfirmRequest,
    current_user: dict = Depends(require_staff_member),
    supabase: SupabaseService = Depends(get_supabase),
):
    """Confirm mapping and persist stats."""
    org_id = current_user.get("organization_id")
    if not org_id:
        orgs = await supabase.select("organizations", filters={"owner_id": current_user["id"]})
        if orgs:
            org_id = orgs[0]["id"]

    upload = await supabase.select_one("match_stat_uploads", upload_id)
    if not upload or upload.get("organization_id") != org_id:
        raise HTTPException(status_code=404, detail="Upload not found")
        
    org_id = upload["organization_id"]
    match_id = upload["match_id"]
    
    confirmed_json = confirm_data.extracted_json
    
    # Update match score
    if confirmed_json.final_score_for is not None:
        await supabase.update("matches", match_id, {
            "score_us": confirmed_json.final_score_for,
            "score_them": confirmed_json.final_score_against
        })
    
    # Persist the rows
    stats_to_insert = []
    
    import re
    from app.services.stats_utils import parse_made_attempted
    
    # 1. Collect all players (starters + bench)
    all_players_to_save = []
    if confirmed_json.starters:
        for p in confirmed_json.starters:
            p.is_starter = True
            all_players_to_save.append(p)
    if confirmed_json.bench:
        for p in confirmed_json.bench:
            p.is_starter = False
            all_players_to_save.append(p)
    
    # Fallback if starters/bench were not populated but players was
    if not all_players_to_save and confirmed_json.players:
        all_players_to_save = confirmed_json.players

    # 2. Prepare stat rows for database
    stats_to_insert = []
    for player_row in all_players_to_save:
        if not player_row.linked_player_profile_id:
            continue
            
        # Use already parsed values if available, otherwise try to parse from the ratio string
        fg_m, fg_a = (player_row.fg_m, player_row.fg_a) if (player_row.fg_m or player_row.fg_a) else parse_made_attempted(player_row.fg)
        tp_m, tp_a = (player_row.tp_m, player_row.tp_a) if (player_row.tp_m or player_row.tp_a) else parse_made_attempted(player_row.tp)
        thp_m, thp_a = (player_row.thp_m, player_row.thp_a) if (player_row.thp_m or player_row.thp_a) else parse_made_attempted(player_row.thp)
        ft_m, ft_a = (player_row.ft_m, player_row.ft_a) if (player_row.ft_m or player_row.ft_a) else parse_made_attempted(player_row.ft)
        
        stats_to_insert.append({
            "id": str(uuid4()),
            "match_id": match_id,
            "organization_id": org_id,
            "player_profile_id": str(player_row.linked_player_profile_id),
            "source": "official_upload",
            "mins": player_row.mins,
            "pts": player_row.pts,
            "fgm": fg_m,
            "fga": fg_a,
            "tp_m": tp_m,
            "tp_a": tp_a,
            "thp_m": thp_m,
            "thp_a": thp_a,
            "ft_m": ft_m,
            "ft_a": ft_a,
            "off_reb": player_row.off,
            "def_reb": player_row.def_reb,
            "reb": player_row.reb,
            "ast": player_row.ast,
            "to_cnt": player_row.to_cnt,
            "stl": player_row.stl,
            "blk": player_row.blk,
            "blkr": player_row.blkr,
            "pf": player_row.pf,
            "fls_on": player_row.fls_on,
            "plus_minus": player_row.plus_minus or 0,
            "index_rating": player_row.index or 0,
            "row_confidence": player_row.row_confidence or 1.0,
            "is_starter": player_row.is_starter,
            "created_at": datetime.now().isoformat()
        })
        
    # 3. Bulk insert/update player stats
    for stat in stats_to_insert:
        try:
            # Check for existing stat row for this player in this match
            existing = await supabase.select("match_player_stats", filters={
                "match_id": stat["match_id"],
                "player_profile_id": stat["player_profile_id"]
            })
            if existing:
                await supabase.update("match_player_stats", existing[0]["id"], stat)
            else:
                await supabase.insert("match_player_stats", stat)
        except Exception as e:
            print(f"Error persisting player stat row for {stat['player_profile_id']}: {e}")

    # 4. Persist team statistics
    if confirmed_json.team_statistics:
        try:
            team_stats = confirmed_json.team_statistics.model_dump()
            team_stats["match_id"] = match_id
            team_stats["organization_id"] = org_id
            
            existing_team = await supabase.select("match_team_stats", filters={"match_id": match_id})
            if existing_team:
                await supabase.update("match_team_stats", existing_team[0]["id"], team_stats)
            else:
                await supabase.insert("match_team_stats", team_stats)
        except Exception as e:
            print(f"Error persisting team stats: {e}")

    # 5. Mark upload as confirmed
    await supabase.update("match_stat_uploads", upload_id, {
        "extract_status": "confirmed",
        "extracted_json": confirmed_json.model_dump(by_alias=True)
    })
    
    return {"message": "Stats confirmed and saved"}

@router.post("/stats-upload/{upload_id}/retry")
async def retry_stats_upload(
    upload_id: str,
    background_tasks: BackgroundTasks,
    current_user: dict = Depends(require_staff_member),
    supabase: SupabaseService = Depends(get_supabase),
):
    """Re-run extraction."""
    org_id = current_user.get("organization_id")
    if not org_id:
        orgs = await supabase.select("organizations", filters={"owner_id": current_user["id"]})
        if orgs:
            org_id = orgs[0]["id"]

    upload = await supabase.select_one("match_stat_uploads", upload_id)
    if not upload or upload.get("organization_id") != org_id:
        raise HTTPException(status_code=404, detail="Upload not found")
        
    await supabase.update("match_stat_uploads", upload_id, {"extract_status": "queued"})
    background_tasks.add_task(extract_stats_from_file_background, upload_id, upload["organization_id"])
    
    return {"message": "Retry queued"}