from sqlalchemy.orm import Session, joinedload
from sqlalchemy import func
from typing import Optional, Dict, Any
from datetime import datetime
from passlib.context import CryptContext

from ..models.user import (
    User, UserProfile, UserSports, UserHealth, UserConsent,
    UserGuardians, UserMarketing, UserFinance
)

# Password hashing context
pwd_context = CryptContext(schemes=["bcrypt"], deprecated="auto")


def get_user_by_email(db: Session, email: str) -> Optional[User]:
    """
    Get user by email with all related data loaded.
    Returns User object with eager-loaded relationships.
    """
    return db.query(User).options(
        joinedload(User.profile),
        joinedload(User.sports),
        joinedload(User.health),
        joinedload(User.consent),
        joinedload(User.guardians),
        joinedload(User.marketing),
        joinedload(User.finance)
    ).filter(func.lower(User.email) == func.lower(email)).first()


def get_all_users(db: Session, roles: Optional[list[str]] = None) -> list[User]:
    """
    Get all users, optionally filtered by roles.
    Eager loads all related data.
    """
    query = db.query(User).options(
        joinedload(User.profile),
        joinedload(User.sports),
        joinedload(User.health),
        joinedload(User.consent),
        joinedload(User.guardians),
        joinedload(User.marketing),
        joinedload(User.finance)
    )
    
    if roles:
        # Case insensitive role filtering
        query = query.filter(func.lower(User.role).in_([r.lower() for r in roles]))
    
    return query.order_by(User.id.desc()).all()


def get_user_by_id(db: Session, user_id: int) -> Optional[User]:
    """
    Get user by ID with all related data loaded.
    Returns User object with eager-loaded relationships.
    """
    return db.query(User).options(
        joinedload(User.profile),
        joinedload(User.sports),
        joinedload(User.health),
        joinedload(User.consent),
        joinedload(User.guardians),
        joinedload(User.marketing),
        joinedload(User.finance)
    ).filter(User.id == user_id).first()


def user_to_dict(user: User) -> Dict[str, Any]:
    """
    Convert User ORM object to dictionary matching the old sqlite3.Row format.
    This ensures backward compatibility with existing code that expects dict-like access.
    """
    if not user:
        return None
    
    result = {
        # Main user fields
        "id": user.id,
        "name": user.name,
        "email": user.email,
        "password_hash": user.password_hash,
        "created_at": user.created_at,
        "role": user.role,
        "is_mvp90": bool(user.is_mvp90),
        "is_temporary": bool(user.is_temporary) if hasattr(user, 'is_temporary') else False,
        "first_name": user.first_name,
        "last_name": user.last_name,
        
        # Profile fields
        "phone": user.profile.phone if user.profile else None,
        "avatar": user.profile.avatar if user.profile else None,
        "city": user.profile.city if user.profile else None,
        "dni": user.profile.dni if user.profile else None,
        "birth_date": user.profile.birth_date if user.profile else None,
        
        # Sports fields
        "team": user.sports.team if user.sports else None,
        "category": user.sports.category if user.sports else None,
        "position": user.sports.position if user.sports else None,
        "dominant_foot": user.sports.dominant_foot if user.sports else None,
        "strengths": user.sports.strengths if user.sports else None,
        "weaknesses": user.sports.weaknesses if user.sports else None,
        "injury_history": user.sports.injury_history if user.sports else None,
        "training_type": user.sports.training_type if user.sports else None,
        
        # Health fields
        "physical_work": user.health.physical_work if user.health else None,
        "physical_work_details": user.health.physical_work_details if user.health else None,
        "smoking": user.health.smoking if user.health else None,
        "alcohol": user.health.alcohol if user.health else None,
        "recovery": user.health.recovery if user.health else None,
        "chest_pain": user.health.chest_pain if user.health else None,
        "discomfort": user.health.discomfort if user.health else None,
        
        # Consent fields
        "whatsapp_content": user.consent.whatsapp_content if user.consent else None,
        "video_permission": user.consent.video_permission if user.consent else None,
        "privacy_acceptance": user.consent.privacy_acceptance if user.consent else None,
        "data_confirmation": user.consent.data_confirmation if user.consent else None,
        "agreement": user.consent.agreement if user.consent else None,
        
        # Guardians fields
        "occupation": user.guardians.occupation if user.guardians else None,
        "study_place": user.guardians.study_place if user.guardians else None,
        "parent_name": user.guardians.parent_name if user.guardians else None,
        "parent_email": user.guardians.parent_email if user.guardians else None,
        
        # Marketing fields
        "found_us": user.marketing.found_us if user.marketing else None,
        "enjoyment": user.marketing.enjoyment if user.marketing else None,
        "nerves_confidence": user.marketing.nerves_confidence if user.marketing else None,
        "additional_comments": user.marketing.additional_comments if user.marketing else None,
        
        # Finance fields
        "matricula_eur": user.finance.matricula_eur if user.finance else 0.0,
        "matricula_cents": int(round((user.finance.matricula_eur if user.finance else 0.0) * 100))
    }
    
    return result

def set_user_mvp90(db: Session, user_id: int, value: bool) -> bool:
    """Toggle MVP-90 status for a user"""
    user = db.query(User).filter(User.id == user_id).first()
    if user:
        user.is_mvp90 = 1 if value else 0
        db.commit()
        return True
    return False


def _ensure_user_satellites(db: Session, user_id: int):
    """
    Ensure all satellite tables have records for the user.
    Creates empty records if they don't exist.
    """
    # Check and create profile
    if not db.query(UserProfile).filter(UserProfile.user_id == user_id).first():
        db.add(UserProfile(user_id=user_id))
    
    # Check and create sports
    if not db.query(UserSports).filter(UserSports.user_id == user_id).first():
        db.add(UserSports(user_id=user_id))
    
    # Check and create health
    if not db.query(UserHealth).filter(UserHealth.user_id == user_id).first():
        db.add(UserHealth(user_id=user_id))
    
    # Check and create consent
    if not db.query(UserConsent).filter(UserConsent.user_id == user_id).first():
        db.add(UserConsent(user_id=user_id))
    
    # Check and create guardians
    if not db.query(UserGuardians).filter(UserGuardians.user_id == user_id).first():
        db.add(UserGuardians(user_id=user_id))
    
    # Check and create marketing
    if not db.query(UserMarketing).filter(UserMarketing.user_id == user_id).first():
        db.add(UserMarketing(user_id=user_id))
    
    # Check and create finance
    if not db.query(UserFinance).filter(UserFinance.user_id == user_id).first():
        db.add(UserFinance(user_id=user_id, matricula_eur=0.0))
    
    db.commit()


def create_user(db: Session, name: str, email: str, password: str, role: str = "alumno",
                first_name: Optional[str] = None, last_name: Optional[str] = None) -> int:
    """
    Create a new user with hashed password and initialize satellite tables.
    Returns the new user's ID.
    """
    password_hash = pwd_context.hash(password)
    
    user = User(
        name=name,
        email=email.lower(),
        password_hash=password_hash,
        role=role,
        first_name=first_name,
        last_name=last_name
    )
    
    db.add(user)
    db.commit()
    db.refresh(user)
    
    # Create satellite records
    _ensure_user_satellites(db, user.id)
    
    return user.id


def count_admins(db: Session) -> int:
    """Count number of admin users"""
    return db.query(User).filter(User.role == 'admin').count()


def is_admin(user_dict: Dict[str, Any]) -> bool:
    """Check if user dict has admin role"""
    return str(user_dict.get("role", "")) == "admin"


def set_user_role(db: Session, user_id: int, role: str) -> bool:
    """Set user's role"""
    user = db.query(User).filter(User.id == user_id).first()
    if user:
        user.role = role
        db.commit()
        return True
    return False


def update_user_profile(db: Session, user_id: int, **kwargs) -> bool:
    """Update user profile fields"""
    profile = db.query(UserProfile).filter(UserProfile.user_id == user_id).first()
    if not profile:
        profile = UserProfile(user_id=user_id)
        db.add(profile)
    
    for key, value in kwargs.items():
        if hasattr(profile, key) and value is not None:
            setattr(profile, key, value)
    
    db.commit()
    return True


def update_user_sports(db: Session, user_id: int, **kwargs) -> bool:
    """Update user sports fields"""
    sports = db.query(UserSports).filter(UserSports.user_id == user_id).first()
    if not sports:
        sports = UserSports(user_id=user_id)
        db.add(sports)
    
    for key, value in kwargs.items():
        if hasattr(sports, key) and value is not None:
            setattr(sports, key, value)
    
    db.commit()
    return True


def update_user_health(db: Session, user_id: int, **kwargs) -> bool:
    """Update user health fields"""
    health = db.query(UserHealth).filter(UserHealth.user_id == user_id).first()
    if not health:
        health = UserHealth(user_id=user_id)
        db.add(health)
    
    for key, value in kwargs.items():
        if hasattr(health, key) and value is not None:
            setattr(health, key, value)
    
    db.commit()
    return True


def update_user_consent(db: Session, user_id: int, **kwargs) -> bool:
    """Update user consent fields"""
    consent = db.query(UserConsent).filter(UserConsent.user_id == user_id).first()
    if not consent:
        consent = UserConsent(user_id=user_id)
        db.add(consent)
    
    for key, value in kwargs.items():
        if hasattr(consent, key) and value is not None:
            setattr(consent, key, value)
    
    db.commit()
    return True


def update_user_guardians(db: Session, user_id: int, **kwargs) -> bool:
    """Update user guardians fields"""
    guardians = db.query(UserGuardians).filter(UserGuardians.user_id == user_id).first()
    if not guardians:
        guardians = UserGuardians(user_id=user_id)
        db.add(guardians)
    
    for key, value in kwargs.items():
        if hasattr(guardians, key) and value is not None:
            setattr(guardians, key, value)
    
    db.commit()
    return True


def update_user_marketing(db: Session, user_id: int, **kwargs) -> bool:
    """Update user marketing fields"""
    marketing = db.query(UserMarketing).filter(UserMarketing.user_id == user_id).first()
    if not marketing:
        marketing = UserMarketing(user_id=user_id)
        db.add(marketing)
    
    for key, value in kwargs.items():
        if hasattr(marketing, key) and value is not None:
            setattr(marketing, key, value)
    
    db.commit()
    return True


def update_user_finance(db: Session, user_id: int, **kwargs) -> bool:
    """Update user finance fields"""
    finance = db.query(UserFinance).filter(UserFinance.user_id == user_id).first()
    if not finance:
        finance = UserFinance(user_id=user_id, matricula_eur=0.0)
        db.add(finance)
    
    for key, value in kwargs.items():
        if hasattr(finance, key) and value is not None:
            setattr(finance, key, value)
    
    db.commit()
    return True

# ==========================================
# New methods for app/main.py migration
# ==========================================

def get_all_students(db: Session) -> list[User]:
    """Get all students ordered by name"""
    return db.query(User).filter(User.role == 'alumno').order_by(User.name).all()

def get_teachers(db: Session) -> list[Dict[str, Any]]:
    """Get all teachers as dicts"""
    teachers = db.query(User).filter(User.role == 'docente').order_by(User.name).all()
    return [
        {"id": t.id, "name": t.name, "email": t.email, "created_at": t.created_at, "role": t.role}
        for t in teachers
    ]

def delete_user(db: Session, user_id: int) -> bool:
    """Delete a user along with cascading deletes not handled by DB constraints"""
    user = db.query(User).filter(User.id == user_id).first()
    if not user:
        return False
    
    # SQLAlchemy's cascade="all, delete-orphan" on relationships should handle most
    # but let's be explicit if needed. 
    # For now, standard delete.
    db.delete(user)
    db.commit()
    return True

def get_user_with_satellites(db: Session, user_id: int) -> Optional[Dict[str, Any]]:
    """
    Get full user details including all satellite tables for admin view.
    Replaces the large raw SQL query in api_user_detail.
    """
    user = get_user_by_id(db, user_id)
    if not user:
        return None
    
    return user_to_dict(user)

def get_users_for_management(db: Session, page: int = 1, per_page: int = 20, search: Optional[str] = None) -> Dict[str, Any]:
    """
    Get users for management view with pagination and search support.
    """
    # Build base query
    query = db.query(User, UserSports.category).outerjoin(
        UserSports, User.id == UserSports.user_id
    )
    
    # Apply search filter if provided
    if search and search.strip():
        search_term = f"%{search.strip().lower()}%"
        query = query.filter(
            (func.lower(User.name).like(search_term)) |
            (func.lower(User.email).like(search_term))
        )
    
    # Count total users for pagination
    total_query = query.statement.with_only_columns(func.count()).order_by(None)
    total_count = db.execute(total_query).scalar() or 0
    
    # Apply pagination
    offset = (page - 1) * per_page
    results = query.order_by(User.created_at.desc(), User.id.desc()).offset(offset).limit(per_page).all()
    
    # Convert results to dict format
    users = []
    for user, category in results:
        u_dict = {
            "id": user.id,
            "name": user.name,
            "email": user.email,
            "role": user.role,
            "is_mvp90": bool(user.is_mvp90),
            "created_at": user.created_at,
            "category": (category or "").lower()
        }
        users.append(u_dict)
    
    # Calculate pagination info
    total_pages = (total_count + per_page - 1) // per_page
    has_prev = page > 1
    has_next = page < total_pages
    
    return {
        "users": users,
        "pagination": {
            "page": page,
            "per_page": per_page,
            "total": total_count,
            "total_pages": total_pages,
            "has_prev": has_prev,
            "has_next": has_next,
            "prev_page": page - 1 if has_prev else None,
            "next_page": page + 1 if has_next else None
        }
    }

def update_user_core(db: Session, user_id: int, name: str, email: str) -> bool:
    """Update core user fields (name, email)"""
    user = db.query(User).filter(User.id == user_id).first()
    if not user:
        return False
    user.name = name
    user.email = email
    db.commit()
    return True

def update_password(db: Session, user_id: int, password_hash: str) -> bool:
    """Update user password"""
    user = db.query(User).filter(User.id == user_id).first()
    if not user:
        return False
    user.password_hash = password_hash
    db.commit()
    return True


def set_user_password(db: Session, user_id: int, plain_password: str) -> bool:
    """Set user's password (hashing it first)"""
    user = db.query(User).filter(User.id == user_id).first()
    if user:
        user.password_hash = pwd_context.hash(plain_password)
        db.commit()
        return True
    return False