from sqlalchemy.orm import Session, joinedload
from sqlalchemy import func, and_, or_
from typing import Optional, List, Dict, Any
from datetime import datetime

from ..models.reservation import Reservation, ReservationStudent, JugadorGasto
from ..models.user import User


def reservation_to_dict(reservation: Reservation) -> Dict[str, Any]:
    """Convert Reservation ORM object to dictionary matching the old format"""
    if not reservation:
        return None
    
    data = {
        "id": reservation.id,
        "created_at": reservation.created_at,
        "date": reservation.date,
        "time": reservation.time,
        "duration_minutes": reservation.duration_minutes,
        "price_cents": reservation.price_cents,
        "name": reservation.name,
        "email": reservation.email,
        "phone": reservation.phone,
        "notes": reservation.notes,
        "user_id": reservation.user_id,
        "status": reservation.status,
        "docente_id": reservation.docente_id,
        "paid": reservation.paid,
        "students": [],
        "student_payments": {},
        "per_student_cents": {}
    }

    # Map expense amounts by user_id
    expense_map = {}
    if reservation.gastos:
        for g in reservation.gastos:
            if g.user_id:
                expense_map[g.user_id] = g.amount_cents

    # Add students with details
    if reservation.students:
        for rs in reservation.students:
            if rs.user:
                s_data = {
                    "id": rs.user.id,
                    "name": rs.user.name,
                    "email": rs.user.email,
                    "first_name": rs.user.first_name,
                    "last_name": rs.user.last_name,
                    "paid": rs.paid,
                    "amount_cents": expense_map.get(rs.user.id, 0)
                }
                data["students"].append(s_data)
                
                # For compatibility with frontend logic expecting student_payments map
                if rs.paid:
                    data["student_payments"][str(rs.user.id)] = True
                
                # For compatibility with frontend logic expecting per_student_cents map
                if rs.user.id in expense_map:
                    data["per_student_cents"][str(rs.user.id)] = expense_map[rs.user.id]

    return data


def get_reservation_by_id(db: Session, reservation_id: int) -> Optional[Reservation]:
    """Get reservation by ID with eager loading"""
    return db.query(Reservation).options(
        joinedload(Reservation.students).joinedload(ReservationStudent.user),
        joinedload(Reservation.gastos)
    ).filter(Reservation.id == reservation_id).first()



def get_reservations_paginated(
    db: Session,
    page: int = 1,
    size: int = 10,
    user_id: Optional[int] = None,
    docente_id: Optional[int] = None,
    status: Optional[str] = None,
    date_from: Optional[str] = None,
    date_to: Optional[str] = None
) -> Dict[str, Any]:
    """Get paginated list of reservations with filters"""
    query = db.query(Reservation)
    
    # Apply filters
    if user_id is not None:
        query = query.filter(Reservation.user_id == user_id)
    if docente_id is not None:
        query = query.filter(Reservation.docente_id == docente_id)
    if status is not None:
        query = query.filter(Reservation.status == status)
    if date_from:
        query = query.filter(Reservation.date >= date_from)
    if date_to:
        query = query.filter(Reservation.date <= date_to)
    
    # Get total count
    total = query.count()
    
    # Calculate pagination
    offset = (page - 1) * size
    pages = max(1, (total + size - 1) // size) if total else 1
    
    # Get paginated items
    items = query.order_by(Reservation.created_at.desc(), Reservation.id.desc())\
                 .options(
                    joinedload(Reservation.students).joinedload(ReservationStudent.user),
                    joinedload(Reservation.gastos)
                 )\
                 .offset(offset)\
                 .limit(size)\
                 .all()
    
    return {
        "items": [reservation_to_dict(r) for r in items],
        "meta": {
            "page": page,
            "size": size,
            "total": total,
            "pages": pages
        }
    }


def get_reservations(
    db: Session,
    user_id: Optional[int] = None,
    docente_id: Optional[int] = None,
    status: Optional[str] = None,
    date_from: Optional[str] = None,
    date_to: Optional[str] = None
) -> List[Dict[str, Any]]:
    """Get list of reservations with filters (non-paginated)"""
    query = db.query(Reservation)
    
    # Apply filters
    if user_id is not None:
        query = query.filter(Reservation.user_id == user_id)
    if docente_id is not None:
        query = query.filter(Reservation.docente_id == docente_id)
    if status is not None:
        query = query.filter(Reservation.status == status)
    if date_from:
        query = query.filter(Reservation.date >= date_from)
    if date_to:
        query = query.filter(Reservation.date <= date_to)
    
    # Eager load for performance and data consistency
    query = query.options(
        joinedload(Reservation.students).joinedload(ReservationStudent.user),
        joinedload(Reservation.gastos)
    )
    
    items = query.order_by(Reservation.created_at.desc(), Reservation.id.desc()).all()
    
    return [reservation_to_dict(r) for r in items]


def get_reservations_by_user(db: Session, user_id: int, email: Optional[str] = None) -> List[Reservation]:
    """
    Get all reservations for a user.
    Checks user_id, reservation_students table, and email.
    """
    # Query for direct user_id match or email match
    query = db.query(Reservation).filter(
        or_(
            Reservation.user_id == user_id,
            func.lower(Reservation.email) == func.lower(email) if email else False
        )
    )
    
    direct_reservations = query.all()
    
    # Also get reservations through reservation_students
    student_reservations = db.query(Reservation).join(
        ReservationStudent,
        ReservationStudent.reservation_id == Reservation.id
    ).filter(ReservationStudent.user_id == user_id).all()
    
    # Merge and deduplicate
    all_reservations = {}
    for r in direct_reservations + student_reservations:
        all_reservations[r.id] = r
    
    return list(all_reservations.values())


def count_user_reservations(db: Session, user_id: int, email: Optional[str] = None) -> int:
    """Count total reservations for a user"""
    # Count direct reservations
    direct_count = db.query(Reservation).filter(
        or_(
            Reservation.user_id == user_id,
            func.lower(Reservation.email) == func.lower(email) if email else False
        )
    ).count()
    
    # Count through reservation_students
    student_count = db.query(Reservation).join(
        ReservationStudent,
        ReservationStudent.reservation_id == Reservation.id
    ).filter(ReservationStudent.user_id == user_id).count()
    
    # Use set logic to avoid double counting
    direct_ids = set(r.id for r in db.query(Reservation.id).filter(
        or_(
            Reservation.user_id == user_id,
            func.lower(Reservation.email) == func.lower(email) if email else False
        )
    ).all())
    
    student_ids = set(r.id for r in db.query(Reservation.id).join(
        ReservationStudent,
        ReservationStudent.reservation_id == Reservation.id
    ).filter(ReservationStudent.user_id == user_id).all())
    
    return len(direct_ids | student_ids)


def create_reservation(
    db: Session,
    date: str,
    docente_id: int,
    time: Optional[str] = None,
    duration_minutes: Optional[int] = None,
    price_cents: int = 0,
    name: Optional[str] = None,
    email: Optional[str] = None,
    phone: Optional[str] = None,
    notes: Optional[str] = None,
    user_id: Optional[int] = None,
    status: str = 'confirmada',
    paid: int = 0
) -> int:
    """Create a new reservation and return its ID"""
    reservation = Reservation(
        date=date,
        time=time,
        duration_minutes=duration_minutes,
        price_cents=price_cents,
        name=name,
        email=email,
        phone=phone,
        notes=notes,
        user_id=user_id,
        status=status,
        docente_id=docente_id,
        paid=paid
    )
    
    db.add(reservation)
    db.commit()
    db.refresh(reservation)
    
    return reservation.id


def update_reservation(db: Session, reservation_id: int, **kwargs) -> bool:
    """Update reservation fields"""
    reservation = db.query(Reservation).filter(Reservation.id == reservation_id).first()
    if not reservation:
        return False
    
    for key, value in kwargs.items():
        if hasattr(reservation, key):
            setattr(reservation, key, value)
    
    db.commit()
    return True


def delete_reservation(db: Session, reservation_id: int) -> bool:
    """Delete a reservation"""
    reservation = db.query(Reservation).filter(Reservation.id == reservation_id).first()
    if not reservation:
        return False
    
    db.delete(reservation)
    db.commit()
    return True


def delete_reservations_by_user(db: Session, user_id: int):
    """Delete all reservations for a user"""
    db.query(Reservation).filter(Reservation.user_id == user_id).delete()
    db.commit()


# ===== Reservation Students =====

def get_reservation_students(db: Session, reservation_id: int) -> List[Dict[str, Any]]:
    """Get all students for a reservation with payment info"""
    # Join ReservationStudent to get paid status
    results = db.query(User, ReservationStudent.paid).join(
        ReservationStudent,
        ReservationStudent.user_id == User.id
    ).filter(ReservationStudent.reservation_id == reservation_id)\
     .order_by(func.coalesce(User.name, User.email)).all()
    
    students = []
    for user, paid in results:
        # Try to find expense for this reservation and user
        expense = db.query(JugadorGasto).filter(
            and_(
                JugadorGasto.reservation_id == reservation_id,
                JugadorGasto.user_id == user.id
            )
        ).first()
        
        amount_cents = expense.amount_cents if expense else 0
        
        students.append({
            "id": user.id,
            "name": user.name,
            "email": user.email,
            "paid": paid,
            "amount_cents": amount_cents
        })
        
    return students


def add_student_to_reservation(db: Session, reservation_id: int, user_id: int, paid: int = 0) -> bool:
    """Add a student to a reservation"""
    # Check if already exists
    existing = db.query(ReservationStudent).filter(
        and_(
            ReservationStudent.reservation_id == reservation_id,
            ReservationStudent.user_id == user_id
        )
    ).first()
    
    if existing:
        return False
    
    rs = ReservationStudent(
        reservation_id=reservation_id,
        user_id=user_id,
        paid=paid
    )
    db.add(rs)
    db.commit()
    return True


def remove_student_from_reservation(db: Session, reservation_id: int, user_id: int) -> bool:
    """Remove a student from a reservation"""
    deleted = db.query(ReservationStudent).filter(
        and_(
            ReservationStudent.reservation_id == reservation_id,
            ReservationStudent.user_id == user_id
        )
    ).delete()
    db.commit()
    return deleted > 0


def update_student_payment_status(db: Session, reservation_id: int, user_id: int, paid: int) -> bool:
    """Update payment status for a student in a reservation"""
    rs = db.query(ReservationStudent).filter(
        and_(
            ReservationStudent.reservation_id == reservation_id,
            ReservationStudent.user_id == user_id
        )
    ).first()
    
    if not rs:
        return False
    
    rs.paid = paid
    db.commit()
    return True


def sync_reservation_students(db: Session, reservation_id: int, new_student_ids: List[int]) -> Dict[str, List[int]]:
    """
    Sync students for a reservation: add new ones, remove missing ones.
    Returns dict with 'added' and 'removed' lists of user_ids.
    """
    # Get current students
    current_students = db.query(ReservationStudent).filter(ReservationStudent.reservation_id == reservation_id).all()
    current_ids = set(rs.user_id for rs in current_students)
    new_ids = set(new_student_ids)
    
    # Calculate differences
    to_add = new_ids - current_ids
    to_remove = current_ids - new_ids
    
    # Remove
    if to_remove:
        db.query(ReservationStudent).filter(
            and_(
                ReservationStudent.reservation_id == reservation_id,
                ReservationStudent.user_id.in_(to_remove)
            )
        ).delete(synchronize_session=False)
        
        # Also cleanup finance? 
        # Logic says if we remove a student, maybe we should not auto-refund but let admin handle it?
        # The legacy code didn't seem to remove students at all in update loop.
        # But if we are syncing, we should remove them from the list.
        # Expenses (JugadorGasto) might exist. We usually keep expenses as history or delete them?
        # Cascading delete on ReservationStudent won't delete Expenses.
        # Let's just remove the link for now.
    
    # Add
    for uid in to_add:
        rs = ReservationStudent(
            reservation_id=reservation_id,
            user_id=uid,
            paid=0 # Default to unpaid when adding
        )
        db.add(rs)
        
    db.commit()
    
    return {"added": list(to_add), "removed": list(to_remove)}


# ===== Player Expenses (Jugadores Gastos) =====


def get_expenses_by_reservation(db: Session, reservation_id: int) -> List[JugadorGasto]:
    """Get all expenses linked to a reservation"""
    return db.query(JugadorGasto).filter(JugadorGasto.reservation_id == reservation_id).all()


def get_expenses(
    db: Session,
    date_from: Optional[str] = None,
    date_to: Optional[str] = None,
    user_id: Optional[int] = None
) -> List[Dict[str, Any]]:
    """Get expenses with optional filters"""
    query = db.query(JugadorGasto, User.name.label('user_name'), User.email.label('user_email'))\
              .outerjoin(User, User.id == JugadorGasto.user_id)
    
    if date_from:
        query = query.filter(JugadorGasto.date >= date_from)
    if date_to:
        query = query.filter(JugadorGasto.date <= date_to)
    if user_id is not None:
        query = query.filter(JugadorGasto.user_id == user_id)
    
    results = query.order_by(JugadorGasto.date.asc(), JugadorGasto.id.asc()).all()
    
    expenses = []
    for gasto, user_name, user_email in results:
        expenses.append({
            "id": gasto.id,
            "created_at": gasto.created_at,
            "date": gasto.date,
            "user_id": gasto.user_id,
            "user_name": user_name,
            "user_email": user_email,
            "reservation_id": gasto.reservation_id,
            "amount_cents": gasto.amount_cents,
            "category": gasto.category,
            "concept": gasto.concept,
            "notes": gasto.notes
        })
    
    return expenses


def create_expense(
    db: Session,
    date: str,
    amount_cents: int,
    category: Optional[str] = None,
    concept: Optional[str] = None,
    notes: Optional[str] = None,
    user_id: Optional[int] = None,
    reservation_id: Optional[int] = None
) -> int:
    """Create a new expense and return its ID"""
    expense = JugadorGasto(
        date=date,
        amount_cents=amount_cents,
        category=category,
        concept=concept,
        notes=notes,
        user_id=user_id,
        reservation_id=reservation_id
    )
    
    db.add(expense)
    db.commit()
    db.refresh(expense)
    
    return expense.id


def delete_expense(db: Session, expense_id: int) -> bool:
    """Delete an expense"""
    expense = db.query(JugadorGasto).filter(JugadorGasto.id == expense_id).first()
    if not expense:
        return False
    
    db.delete(expense)
    db.commit()
    return True


def check_duplicate_reservation(db: Session, date: str, time: Optional[str], docente_id: Optional[int]) -> Optional[int]:
    """Check if a reservation already exists for the same date, time, and teacher"""
    existing = db.query(Reservation.id).filter(
        and_(
            Reservation.date == date,
            Reservation.time == time if time else Reservation.time.is_(None),
            func.coalesce(Reservation.docente_id, 0) == (docente_id if docente_id else 0)
        )
    ).first()
    
    return existing[0] if existing else None


def delete_student_entries_by_user(db: Session, user_id: int):
    """Delete all reservation_student entries for a user"""
    db.query(ReservationStudent).filter(ReservationStudent.user_id == user_id).delete()
    db.commit()