""" CRUD operations for raider services """
from typing import List, Optional, Dict, Tuple
from uuid import UUID

from sqlmodel import select, Session, func
from fastapi import HTTPException

from app.models import Raider, RaiderCreate, RaiderUpdate, RaiderFilter, RaidersOut, RaiderOutId, RaidersOutId, Musica, Fotografia, RaiderOut


def create_raider(
    *,
    session: Session,
    raider_in: RaiderCreate,
    account_id: UUID
) -> RaiderOut:
    """Create a new raider"""
    raider = Raider(
        **raider_in.model_dump(),
        account_id=account_id
    )
    session.add(raider)
    session.commit()
    session.refresh(raider)
    return RaiderOut.model_validate(raider)


def get_raider(*, session: Session, raider_id: UUID) -> Optional[Raider]:
    """
    Get raider by id
    """
    return session.get(Raider, raider_id)


def get_raiders_by_account(*, session: Session, account_id: UUID, skip: int = 0, limit: int = 100) -> RaidersOut:
    """
    Get raiders by account id with pagination
    """
    # Count total
    count_statement = select(func.count(Raider.id)).where(Raider.account_id == account_id)
    total = session.exec(count_statement).one()
    
    # Get raiders
    statement = select(Raider).where(
        Raider.account_id == account_id
    ).offset(skip).limit(limit)
    raiders = session.exec(statement).all()
    
    # Convert to RaiderOut
    raiders_out = [RaiderOut.model_validate(raider) for raider in raiders]
    
    return RaidersOut(data=raiders_out, count=total)


def get_all_raiders(*, session: Session, skip: int = 0, limit: int = 100) -> RaidersOut:
    """
    Get all raiders with pagination
    """
    # Count total
    count_statement = select(func.count(Raider.id))
    total = session.exec(count_statement).one()
    
    # Get raiders
    statement = select(Raider).offset(skip).limit(limit)
    raiders = session.exec(statement).all()
    
    # Convert to RaiderOut
    raiders_out = [RaiderOut.model_validate(raider) for raider in raiders]
    
    return RaidersOut(data=raiders_out, count=total)



def get_filtered_raiders(*, session: Session, filters: RaiderFilter, skip: int = 0, limit: int = 100) -> RaidersOut:
    """
    Get filtered raiders
    """
    # Build base query
    statement = select(Raider)
    
    # Apply filters
    if filters.solicitar_cancion_nueva is not None:
        statement = statement.where(Raider.solicitar_cancion_nueva == filters.solicitar_cancion_nueva)
    if filters.equipo_propio is not None:
        statement = statement.where(Raider.equipo_propio == filters.equipo_propio)
    
    # Count total with filters
    count_statement = select(func.count(Raider.id))
    if filters.solicitar_cancion_nueva is not None:
        count_statement = count_statement.where(Raider.solicitar_cancion_nueva == filters.solicitar_cancion_nueva)
    if filters.equipo_propio is not None:
        count_statement = count_statement.where(Raider.equipo_propio == filters.equipo_propio)
    total = session.exec(count_statement).one()
    
    # Apply pagination
    statement = statement.offset(skip).limit(limit)
    raiders = session.exec(statement).all()
    
    # Convert to RaiderOut
    raiders_out = [RaiderOut.model_validate(raider) for raider in raiders]
    
    return RaidersOut(data=raiders_out, count=total)


def update_raider(
    *,
    session: Session,
    raider: Raider,
    raider_in: RaiderUpdate
) -> RaiderOut:
    """Update a raider"""
    raider_data = raider_in.model_dump(exclude_unset=True)
    for field, value in raider_data.items():
        setattr(raider, field, value)
    
    session.add(raider)
    session.commit()
    session.refresh(raider)
    return RaiderOut.model_validate(raider)


def replace_raider(*, session: Session, raider: Raider, raider_in: RaiderCreate) -> Raider:
    """
    Replace raider completely
    """
    raider_data = raider_in.model_dump()
    for key, value in raider_data.items():
        setattr(raider, key, value)
    
    session.add(raider)
    session.commit()
    session.refresh(raider)
    return raider


def delete_raider(*, session: Session, raider: Raider) -> Raider:
    """
    Delete raider if it's not being used by any musica or fotografia
    """
    # Check if raider is being used by any musica
    musica_statement = select(Musica).where(Musica.raider_id == raider.id)
    musica = session.exec(musica_statement).first()
    if musica:
        raise HTTPException(
            status_code=400,
            detail=f"Cannot delete raider because it's being used by musica with ID: {musica.id}"
        )
    
    # Check if raider is being used by any fotografia
    fotografia_statement = select(Fotografia).where(Fotografia.raider_id == raider.id)
    fotografia = session.exec(fotografia_statement).first()
    if fotografia:
        raise HTTPException(
            status_code=400,
            detail=f"Cannot delete raider because it's being used by fotografia with ID: {fotografia.id}"
        )
    
    # If not being used, delete the raider
    session.delete(raider)
    session.commit()
    return raider 