""" CRUD operations for musica services """
from typing import List, Optional, Dict, Any, Tuple
from uuid import UUID
from datetime import time

from sqlmodel import select, Session, func, or_
from sqlalchemy import JSON
from fastapi import HTTPException

from app.models import (
    Musica,
    MusicaEstilo, Generos, Canciones, Service, ServiceType,
    ServiceMusicaOut, ServiceMusicasOut, ServiceCreateMusica, ServiceUpdateMusica,
    Raider, AvailabilityRule
)


def create_musica(
    db: Session, 
    musica_in: ServiceCreateMusica, 
    account_id: UUID
) -> ServiceMusicaOut:
    """
    Create a new musica service with its related service data and raider
    """
    # Verificar que el tipo de servicio sea MUSICA
    if musica_in.type != ServiceType.MUSICA:
        musica_in.type = ServiceType.MUSICA
    
    # Convertir el objeto Address a diccionario para que sea serializable
    address_dict = None
    if musica_in.address:
        address_dict = musica_in.address.dict() if hasattr(musica_in.address, 'dict') else musica_in.address.model_dump()
    
    # 1. Crear el Service con la dirección embebida como JSON
    service = Service(
        type=ServiceType.MUSICA,
        name=musica_in.name,
        images=musica_in.images,
        description=musica_in.description,
        service_include=musica_in.service_include,
        account_id=account_id,
        address=address_dict
    )
    db.add(service)
    db.flush()  # Obtener el ID del servicio sin confirmar la transacción
    
    # 2. Crear las reglas de disponibilidad
    if musica_in.availability_rules:
        for rule_data in musica_in.availability_rules:
            # Convertir strings de tiempo a objetos time
            hora_inicio = time.fromisoformat(rule_data.get('hora_inicio')) if rule_data.get('hora_inicio') else None
            hora_fin = time.fromisoformat(rule_data.get('hora_fin')) if rule_data.get('hora_fin') else None
            
            rule = AvailabilityRule(
                service_id=service.id,
                dias_semana=rule_data.get('dias_semana', []),
                hora_inicio=hora_inicio,
                hora_fin=hora_fin,
                price=rule_data.get('price', 0.0),
                min_days_before=rule_data.get('min_days_before', 1)
            )
            db.add(rule)
    
    # 3. Manejar el Raider
    raider_id = None
    raider = None
    if isinstance(musica_in.musica, dict) and musica_in.musica.get('raider'):
        raider_data = musica_in.musica['raider']
        
        # Buscar un raider existente con los mismos datos para esta cuenta
        existing_raider = db.query(Raider).filter(
            Raider.account_id == account_id,
            Raider.num_integrantes == raider_data.get('num_integrantes'),
            Raider.equipo == raider_data.get('equipo'),
            Raider.solicitar_cancion_nueva == raider_data.get('solicitar_cancion_nueva'),
            Raider.equipo_propio == raider_data.get('equipo_propio'),
            Raider.dimensiones_escenario == raider_data.get('dimensiones_escenario'),
            Raider.condiciones == raider_data.get('condiciones'),
        ).first()
        
        if existing_raider:
            raider_id = existing_raider.id
            raider = existing_raider
        else:
            raider = Raider(**raider_data, account_id=account_id)
            db.add(raider)
            db.flush()  # Obtener el ID del raider
            raider_id = raider.id

    elif isinstance(musica_in.musica, dict) and musica_in.musica.get('raider_id'):
        raider_id = musica_in.musica['raider_id']
        raider = db.get(Raider, raider_id)
    
    # 4. Validar y crear la Musica
    musica_data = musica_in.musica.copy()
    if 'raider' in musica_data:
        del musica_data['raider']
    if 'raider_id' in musica_data:
        del musica_data['raider_id']
    
    # Validar musica_type
    if 'musica_type' in musica_data:
        try:
            MusicaEstilo(musica_data['musica_type'])
        except ValueError:
            raise HTTPException(
                status_code=400,
                detail=f"Tipo de música inválido: {musica_data['musica_type']}"
            )
    
    # Validar generos si existen
    if musica_data.get('generos'):
        for genero in musica_data['generos']:
            try:
                Generos(genero)
            except ValueError:
                raise HTTPException(
                    status_code=400,
                    detail=f"Género musical inválido: {genero}"
                )
    
    # Validar canciones_type si existe
    if musica_data.get('canciones'):
        try:
            Canciones(musica_data['canciones'])
        except ValueError:
            raise HTTPException(
                status_code=400,
                detail=f"Tipo de canciones inválido: {musica_data['canciones']}"
            )
        
    musica = Musica(**musica_data, raider_id=raider_id)
    musica.id = service.id  # Usar el mismo ID que el servicio
    db.add(musica)
    
    # 5. Actualizar el servicio con el ID de música
    service.musica_id = musica.id
    db.add(service)
    
    # Confirmar la transacción
    db.commit()
    db.refresh(service)
    db.refresh(musica)
    if raider:
        db.refresh(raider)
    
    # Construir y devolver el objeto de respuesta
    musica_dict = {
        "id": musica.id,
        "musica_type": musica.musica_type,
        "generos": musica.generos,
        "canciones": musica.canciones,
        "duracion": musica.duracion,
        "instrumentos": musica.instrumentos,
        "caracteristicas": musica.caracteristicas,
        "raider_id": musica.raider_id
    }
    
    # Incluir el raider si existe
    if raider:
        musica_dict["raider"] = raider.dict()
    
    # Get availability rules
    availability_rules = [rule.dict() for rule in service.availability_rules]
    
    return ServiceMusicaOut(
        id=service.id,
        type=service.type,
        name=service.name,
        images=service.images,
        description=service.description,
        service_include=service.service_include,
        account_id=service.account_id,
        address=service.address,
        musica_id=musica.id,
        musica=musica_dict,
        availability_rules=availability_rules
    )


def get_musica(db: Session, musica_id: UUID) -> Optional[ServiceMusicaOut]:
    """
    Get a musica service by ID with all related data including raider
    """
    # Obtener el servicio
    service = db.get(Service, musica_id)
    if not service or service.type != ServiceType.MUSICA:
        return None
    
    # Obtener la música relacionada
    musica = db.get(Musica, musica_id)
    if not musica:
        return None
    
    # Obtener el raider si existe
    raider = None
    if musica.raider_id:
        raider = db.get(Raider, musica.raider_id)
    
    # Construir el diccionario de música
    musica_dict = {
        "id": musica.id,
        "musica_type": musica.musica_type,
        "generos": musica.generos,
        "canciones": musica.canciones,
        "duracion": musica.duracion,
        "instrumentos": musica.instrumentos,
        "caracteristicas": musica.caracteristicas,
        "raider_id": musica.raider_id
    }
    
    # Incluir el raider si existe
    if raider:
        musica_dict["raider"] = raider.dict()
    
    # Get availability rules
    availability_rules = [rule.dict() for rule in service.availability_rules]
    
    # Construir y devolver el objeto de respuesta
    return ServiceMusicaOut(
        id=service.id,
        type=service.type,
        name=service.name,
        images=service.images,
        description=service.description,
        service_include=service.service_include,
        account_id=service.account_id,
        address=service.address,
        musica_id=musica.id,
        musica=musica_dict,
        availability_rules=availability_rules
    )


def get_musicas(
    db: Session, 
    skip: int = 0, 
    limit: int = 100
) -> Tuple[List[ServiceMusicaOut], int]:
    """
    Get all musica services with pagination
    """
    # Base query para servicios de tipo MUSICA
    query = select(Service).where(Service.type == ServiceType.MUSICA)
    
    # Obtener el recuento total para pagination
    count_query = select(func.count()).select_from(query.subquery())
    total = db.scalar(count_query)
    
    # Aplicar pagination
    services = db.exec(query.offset(skip).limit(limit)).all()
    
    # Construir objetos de respuesta
    result = []
    for service in services:
        musica = db.get(Musica, service.id)
        if not musica:
            continue
        
        # Obtener el raider si existe
        raider = None
        if musica.raider_id:
            raider = db.get(Raider, musica.raider_id)
        
        # Construir el diccionario de música
        musica_dict = {
            "id": musica.id,
            "musica_type": musica.musica_type,
            "generos": musica.generos,
            "canciones": musica.canciones,
            "duracion": musica.duracion,
            "instrumentos": musica.instrumentos,
            "caracteristicas": musica.caracteristicas,
            "raider_id": musica.raider_id
        }
        
        # Incluir el raider si existe
        if raider:
            musica_dict["raider"] = raider.dict()
        
        # Get availability rules
        availability_rules = [rule.dict() for rule in service.availability_rules]
        
        result.append(ServiceMusicaOut(
            id=service.id,
            type=service.type,
            name=service.name,
            images=service.images,
            description=service.description,
            service_include=service.service_include,
            account_id=service.account_id,
            address=service.address,
            musica_id=musica.id,
            musica=musica_dict,
            availability_rules=availability_rules
        ))
    
    return result, total


def get_musicas_by_account(
    db: Session, 
    account_id: UUID,
    skip: int = 0, 
    limit: int = 100
) -> Tuple[List[ServiceMusicaOut], int]:
    """
    Get musica services for a specific account with pagination
    """
    # Construir la query base
    query = (
        select(Service, Musica)
        .join(Musica, Service.id == Musica.id)
        .where(
            Service.type == ServiceType.MUSICA,
            Service.account_id == account_id
        )
    )
    
    # Obtener el recuento total
    count_query = select(func.count()).select_from(query.subquery())
    total = db.scalar(count_query)
    
    # Aplicar pagination
    results = db.exec(query.offset(skip).limit(limit)).all()
    
    # Construir la respuesta
    musicas = []
    for service, musica in results:
        # Obtener el raider si existe
        raider = None
        if musica.raider_id:
            raider = db.get(Raider, musica.raider_id)
        
        # Construir el diccionario de música
        musica_dict = {
            "id": musica.id,
            "musica_type": musica.musica_type,
            "generos": musica.generos,
            "canciones": musica.canciones,
            "duracion": musica.duracion,
            "instrumentos": musica.instrumentos,
            "caracteristicas": musica.caracteristicas,
            "raider_id": musica.raider_id
        }
        
        # Incluir el raider si existe
        if raider:
            musica_dict["raider"] = raider.dict()
        
        # Get availability rules
        availability_rules = [rule.dict() for rule in service.availability_rules]
        
        # Añadir a la lista de resultados
        musicas.append(
            ServiceMusicaOut(
                id=service.id,
                type=service.type,
                name=service.name,
                images=service.images,
                description=service.description,
                service_include=service.service_include,
                account_id=service.account_id,
                address=service.address,
                musica_id=musica.id,
                musica=musica_dict,
                availability_rules=availability_rules
            )
        )
    
    return musicas, total


def update_musica(
    db: Session, 
    musica_id: UUID, 
    musica_in: ServiceUpdateMusica
) -> Optional[ServiceMusicaOut]:
    """
    Update a musica service and its related data
    """
    # Obtener el servicio
    service = db.get(Service, musica_id)
    if not service or service.type != ServiceType.MUSICA:
        return None
    
    # Obtener la música relacionada
    musica = db.get(Musica, musica_id)
    if not musica:
        return None
    
    # Actualizar los campos del servicio si se proporcionan
    if musica_in.name is not None:
        service.name = musica_in.name
    if musica_in.images is not None:
        service.images = musica_in.images
    if musica_in.description is not None:
        service.description = musica_in.description
    if musica_in.service_include is not None:
        service.service_include = musica_in.service_include
    if musica_in.address is not None:
        service.address = musica_in.address
    
    # Actualizar reglas de disponibilidad si se proporcionan
    if musica_in.availability_rules is not None:
        # Eliminar reglas existentes
        existing_rules = db.exec(
            select(AvailabilityRule).where(AvailabilityRule.service_id == service.id)
        ).all()
        for rule in existing_rules:
            db.delete(rule)
        
        # Crear nuevas reglas
        for rule_data in musica_in.availability_rules:
            # Convertir strings de tiempo a objetos time
            hora_inicio = time.fromisoformat(rule_data.get('hora_inicio')) if rule_data.get('hora_inicio') else None
            hora_fin = time.fromisoformat(rule_data.get('hora_fin')) if rule_data.get('hora_fin') else None
            
            rule = AvailabilityRule(
                service_id=service.id,
                dias_semana=rule_data.get('dias_semana', []),
                hora_inicio=hora_inicio,
                hora_fin=hora_fin,
                price=rule_data.get('price', 0.0),
                min_days_before=rule_data.get('min_days_before', 1)
            )
            db.add(rule)
    
    # Actualizar los campos de música si se proporcionan
    if musica_in.musica and isinstance(musica_in.musica, dict):
        musica_data = musica_in.musica.copy()
        
        # Manejar el raider si se proporciona
        raider_data = musica_data.pop('raider', None)
        if raider_data:
            # Buscar un raider existente con los mismos datos
            existing_raider = db.query(Raider).filter(
                Raider.account_id == service.account_id,
                Raider.num_integrantes == raider_data.get('num_integrantes'),
                Raider.equipo == raider_data.get('equipo'),
                Raider.solicitar_cancion_nueva == raider_data.get('solicitar_cancion_nueva'),
                Raider.equipo_propio == raider_data.get('equipo_propio'),
                Raider.dimensiones_escenario == raider_data.get('dimensiones_escenario'),
                Raider.condiciones == raider_data.get('condiciones'),
            ).first()
            
            if existing_raider:
                musica.raider_id = existing_raider.id
            else:
                raider = Raider(**raider_data, account_id=service.account_id)
                db.add(raider)
                db.flush()
                musica.raider_id = raider.id
        
        # Validar musica_type si se proporciona
        if 'musica_type' in musica_data:
            try:
                MusicaEstilo(musica_data['musica_type'])
            except ValueError:
                raise HTTPException(
                    status_code=400,
                    detail=f"Tipo de música inválido: {musica_data['musica_type']}"
                )
            musica.musica_type = musica_data['musica_type']
        
        # Validar y actualizar géneros si se proporcionan
        if 'generos' in musica_data:
            for genero in musica_data['generos']:
                try:
                    Generos(genero)
                except ValueError:
                    raise HTTPException(
                        status_code=400,
                        detail=f"Género musical inválido: {genero}"
                    )
            musica.generos = musica_data['generos']
        
        # Validar y actualizar canciones_type si se proporciona
        if 'canciones' in musica_data:
            try:
                Canciones(musica_data['canciones'])
            except ValueError:
                raise HTTPException(
                    status_code=400,
                    detail=f"Tipo de canciones inválido: {musica_data['canciones']}"
                )
            musica.canciones = musica_data['canciones']
        
        # Actualizar otros campos
        if 'duracion' in musica_data:
            musica.duracion = musica_data['duracion']
        if 'instrumentos' in musica_data:
            musica.instrumentos = musica_data['instrumentos']
        if 'caracteristicas' in musica_data:
            musica.caracteristicas = musica_data['caracteristicas']
    
    # Guardar los cambios
    db.add(service)
    db.add(musica)
    db.commit()
    db.refresh(service)
    db.refresh(musica)
    
    # Obtener el raider actualizado si existe
    raider = None
    if musica.raider_id:
        raider = db.get(Raider, musica.raider_id)
    
    # Construir el diccionario de música
    musica_dict = {
        "id": musica.id,
        "musica_type": musica.musica_type,
        "generos": musica.generos,
        "canciones": musica.canciones,
        "duracion": musica.duracion,
        "instrumentos": musica.instrumentos,
        "caracteristicas": musica.caracteristicas,
        "raider_id": musica.raider_id
    }
    
    # Incluir el raider si existe
    if raider:
        musica_dict["raider"] = raider.dict()
    
    # Obtener las reglas de disponibilidad
    availability_rules = db.exec(
        select(AvailabilityRule)
        .where(AvailabilityRule.service_id == service.id)
    ).all()
    availability_rules_dict = [rule.dict() for rule in availability_rules]
    
    # Construir y devolver el objeto de respuesta
    return ServiceMusicaOut(
        id=service.id,
        type=service.type,
        name=service.name,
        images=service.images,
        description=service.description,
        service_include=service.service_include,
        account_id=service.account_id,
        address=service.address,
        musica_id=musica.id,
        musica=musica_dict,
        availability_rules=availability_rules_dict
    )


def delete_musica(db: Session, musica_id: UUID) -> bool:
    """
    Delete a musica service and its related data
    """
    try:
        # Obtener el servicio y la música
        service = db.get(Service, musica_id)
        if not service or service.type != ServiceType.MUSICA:
            raise HTTPException(status_code=404, detail="Servicio de música no encontrado")
        
        musica = db.get(Musica, musica_id)
        if not musica:
            raise HTTPException(status_code=404, detail="Música no encontrada")
        
        # Primero eliminamos las reglas de disponibilidad
        availability_rules = db.exec(
            select(AvailabilityRule).where(AvailabilityRule.service_id == service.id)
        ).all()
        for rule in availability_rules:
            db.delete(rule)
        db.flush()
        
        # Luego eliminamos la música
        db.delete(musica)
        db.flush()
        
        # Finalmente eliminamos el servicio
        db.delete(service)
        db.flush()
        
        # Confirmar la transacción
        db.commit()
        return True
        
    except Exception as e:
        db.rollback()
        raise HTTPException(
            status_code=500,
            detail=f"Error al eliminar el servicio: {str(e)}"
        )


def get_musicas_filtered(
    db: Session,
    musica_type: Optional[MusicaEstilo] = None,
    genero: Optional[List[Generos]] = None,
    canciones: Optional[Canciones] = None,
    min_duracion: Optional[float] = None,
    max_duracion: Optional[float] = None,
    instrumento: Optional[List[str]] = None,
    account_id: Optional[UUID] = None,
    skip: int = 0,
    limit: int = 100
) -> ServiceMusicasOut:
    """
    Get filtered musica services with pagination and optional filters
    """
    # Construir la consulta base
    query = db.query(Service).join(Musica)
    
    # Aplicar filtros
    if musica_type:
        query = query.filter(Musica.musica_type == musica_type)
    if genero:
        # Buscar si alguno de los géneros está en la lista de géneros
        genero_filters = [Musica.generos.contains(g) for g in genero]
        query = query.filter(or_(*genero_filters))
    if canciones:
        query = query.filter(Musica.canciones == canciones)
    if min_duracion is not None:
        query = query.filter(Musica.duracion >= min_duracion)
    if max_duracion is not None:
        query = query.filter(Musica.duracion <= max_duracion)
    if instrumento:
        # Buscar si alguno de los instrumentos está en la lista de instrumentos
        instrumento_filters = [Musica.instrumentos.contains(i) for i in instrumento]
        query = query.filter(or_(*instrumento_filters))
    if account_id:
        query = query.filter(Service.account_id == account_id)
    
    # Obtener el total de resultados
    total = query.count()
    
    # Aplicar paginación
    query = query.offset(skip).limit(limit)
    
    # Ejecutar la consulta
    services = query.all()
    
    # Construir la respuesta
    result = []
    for service in services:
        musica = db.get(Musica, service.id)
        if not musica:
            continue
        
        # Obtener el raider si existe
        raider = None
        if musica.raider_id:
            raider = db.get(Raider, musica.raider_id)
        
        # Construir el diccionario de música
        musica_dict = {
            "id": musica.id,
            "musica_type": musica.musica_type,
            "generos": musica.generos,
            "canciones": musica.canciones,
            "duracion": musica.duracion,
            "instrumentos": musica.instrumentos,
            "caracteristicas": musica.caracteristicas,
            "raider_id": musica.raider_id
        }
        
        # Incluir el raider si existe
        if raider:
            musica_dict["raider"] = raider.dict()
        
        # Get availability rules
        availability_rules = [rule.dict() for rule in service.availability_rules]
        
        result.append(ServiceMusicaOut(
            id=service.id,
            type=service.type,
            name=service.name,
            images=service.images,
            description=service.description,
            service_include=service.service_include,
            account_id=service.account_id,
            address=service.address,
            musica_id=musica.id,
            musica=musica_dict,
            availability_rules=availability_rules
        ))
    
    return ServiceMusicasOut(
        data=result,
        count=total
    ) 