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

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

from app.models import (
    Service, ServiceType, ServiceOut, ServicesOut, ServiceCreate, ServiceUpdate,
    Fotografia, Musica, Espai, Catering, MobiliariDecoracio, Raider, AvailabilityRule, Address
)


def create_service(*, session: Session, obj_in: ServiceCreate, account_id: UUID) -> ServiceOut:
    """
    Create a new service
    """
    # Convert address to dict if it exists
    address_dict = None
    if obj_in.address:
        address_dict = obj_in.address.model_dump()
    
    # Create the service
    service = Service(
        type=obj_in.type,
        name=obj_in.name,
        description=obj_in.description,
        images=obj_in.images if obj_in.images else [],
        service_include=obj_in.service_include if obj_in.service_include else None,
        address=address_dict,
        account_id=account_id
    )
    session.add(service)
    session.flush()  # To get the service ID
    
    # Create availability rules if provided
    if obj_in.availability_rules:
        for rule_data in obj_in.availability_rules:
            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)
            )
            session.add(rule)
    
    session.commit()
    session.refresh(service)
    
    # Prepare and return the response
    service_dict = _prepare_service_dict(service, session)
    return ServiceOut(**service_dict)


def get_service(*, session: Session, service_id: UUID) -> Optional[Dict[str, Any]]:
    """
    Get service by id
    """
    service = session.get(Service, service_id)
    if not service:
        return None
    
    # Use _prepare_service_dict to ensure consistent response format
    service_dict = _prepare_service_dict(service, session)
    return service_dict


def _prepare_service_dict(service: Service, session: Session) -> Dict[str, Any]:
    """
    Helper function to prepare service dictionary with correct types
    """
    # Get base service data
    service_dict = {
        "id": service.id,
        "type": service.type,
        "name": service.name,
        "description": service.description,
        "images": service.images,
        "service_include": service.service_include,
        "account_id": service.account_id,
        "availability_rules": [rule.model_dump() for rule in service.availability_rules]
    }
    # Handle address if present
    if service.address:
        try:
            address_obj = Address(**service.address)
            service_dict["address"] = address_obj.model_dump()
        except ValueError:
            # Si hay error en la conversión, dejamos el diccionario original
            service_dict["address"] = service.address
    
    # Handle specific service types
    if service.type == ServiceType.FOTOGRAFIA and service.fotografia_id:
        fotografia = session.get(Fotografia, service.fotografia_id)
        if fotografia:
            fotografia_dict = fotografia.model_dump()
            if fotografia.raider_id:
                raider = session.get(Raider, fotografia.raider_id)
                if raider:
                    fotografia_dict["raider"] = raider.model_dump()
            service_dict["fotografia"] = fotografia_dict
            service_dict["fotografia_id"] = service.fotografia_id
    
    if service.type == ServiceType.MUSICA and service.musica_id:
        musica = session.get(Musica, service.musica_id)
        if musica:
            musica_dict = musica.model_dump()
            if musica.raider_id:
                raider = session.get(Raider, musica.raider_id)
                if raider:
                    musica_dict["raider"] = raider.model_dump()
            service_dict["musica"] = musica_dict
            service_dict["musica_id"] = service.musica_id
    
    if service.type == ServiceType.ESPAI and service.espai_id:
        espai = session.get(Espai, service.espai_id)
        if espai:
            service_dict["espai"] = espai.model_dump()
            service_dict["espai_id"] = service.espai_id
    
    if service.type == ServiceType.CATERING and service.catering_id:
        catering = session.get(Catering, service.catering_id)
        if catering:
            catering_dict = catering.model_dump()
            # Solo incluimos menu o articulo, no ambos
            if catering_dict.get('menu'):
                catering_dict = {k: v for k, v in catering_dict.items() if k != 'articulo'}
            elif catering_dict.get('articulo'):
                catering_dict = {k: v for k, v in catering_dict.items() if k != 'menu'}
            service_dict["catering"] = catering_dict
            service_dict["catering_id"] = service.catering_id
    
    if service.type == ServiceType.MOBILIARI_DECORACIO and service.mobiliari_decoracio_id:
        mobiliari = session.get(MobiliariDecoracio, service.mobiliari_decoracio_id)
        if mobiliari:
            service_dict["mobiliari_decoracio"] = mobiliari.model_dump()
            service_dict["mobiliari_decoracio_id"] = service.mobiliari_decoracio_id
    # Remove null values and empty lists
    return {k: v for k, v in service_dict.items() if v is not None and v != []}


def get_services(
    *,
    session: Session,
    skip: int = 0,
    limit: int = 200,
    type: Optional[ServiceType] = None,
    account_id: Optional[UUID] = None
) -> ServicesOut:
    """
    Get all services with optional filtering
    """
    # Base query
    query = select(Service)
    
    # Apply filters
    if type:
        query = query.where(Service.type == type)
    if account_id:
        query = query.where(Service.account_id == account_id)
    
    # Get total count
    count = session.exec(select(func.count()).select_from(query.subquery())).one()
    
    # Get paginated results
    services = session.exec(query.offset(skip).limit(limit)).all()
    
    # Construct the response using _prepare_service_dict
    services_list = [_prepare_service_dict(service, session) for service in services]
    
    return ServicesOut(data=services_list, count=count)


def get_services_by_account(
    *,
    session: Session,
    account_id: UUID,
    skip: int = 0,
    limit: int = 200
) -> ServicesOut:
    """
    Get all services for an account
    """
    # Base query
    query = select(Service).where(Service.account_id == account_id)
    
    # Get total count
    count = session.exec(select(func.count()).select_from(query.subquery())).one()
    
    # Get paginated results
    services = session.exec(query.offset(skip).limit(limit)).all()
    
    # Construct the response using _prepare_service_dict
    services_list = [_prepare_service_dict(service, session) for service in services]
    
    return ServicesOut(data=services_list, count=count)


def get_all_services(*, session: Session, skip: int = 0, limit: int = 200) -> ServicesOut:
    """Get all services"""
    # Count query
    count_statement = select(func.count(Service.id))
    count = session.exec(count_statement).one()
    
    # Results query
    services = session.exec(
        select(Service)
        .offset(skip)
        .limit(limit)
    ).all()
    
    # Construct the response using _prepare_service_dict
    services_list = [_prepare_service_dict(service, session) for service in services]
    
    return ServicesOut(data=services_list, count=count)


def get_filtered_services(*, session: Session, filters: Dict[str, Any], skip: int = 0, limit: int = 100) -> ServicesOut:
    """Get services filtered by criteria"""
    try:
        # Build base query
        query = select(Service)
        
        # Apply basic filters
        if filters.get("type"):
            query = query.where(Service.type == filters["type"])
        if filters.get("account_id"):
            query = query.where(Service.account_id == filters["account_id"])
        if filters.get("name"):
            query = query.where(Service.name.ilike(f"%{filters['name']}%"))
        if filters.get("provincia"):
            query = query.where(Service.address.ilike(f"%{filters['provincia']}%"))
        if filters.get("ciudad"):
            query = query.where(Service.address.ilike(f"%{filters['ciudad']}%"))
        
        # Filtros de AvailabilityRule usando subconsultas
        if any(key in filters for key in ["dia_semana", "precio_min", "precio_max"]):
            availability_subquery = select(AvailabilityRule.service_id).where(
                AvailabilityRule.service_id == Service.id
            )
            
            if filters.get("dia_semana"):
                # Convertir el día de la semana a string para la comparación
                dia_semana = str(filters["dia_semana"])
                availability_subquery = availability_subquery.where(
                    AvailabilityRule.dias_semana.cast(String).contains(dia_semana)
                )
            if filters.get("precio_min"):
                availability_subquery = availability_subquery.where(
                    AvailabilityRule.price >= filters["precio_min"]
                )
            if filters.get("precio_max"):
                availability_subquery = availability_subquery.where(
                    AvailabilityRule.price <= filters["precio_max"]
                )
            
            # Añadir la subconsulta a la consulta principal
            query = query.where(Service.id.in_(availability_subquery))
        
        # Count total
        count_statement = select(func.count(Service.id)).where(query.whereclause)
        count = session.exec(count_statement).one()
        
        # Get services
        services = session.exec(
            query.offset(skip).limit(limit)
        ).all()
        
        # Construct the response using _prepare_service_dict
        services_list = [_prepare_service_dict(service, session) for service in services]
        return ServicesOut(data=services_list, count=count)
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"Error getting filtered services: {str(e)}")


def update_service(*, session: Session, service: Service, obj_in: ServiceUpdate) -> ServiceOut:
    """
    Update a service
    """
    # Update service fields if provided
    if obj_in.name is not None:
        service.name = obj_in.name
    if obj_in.description is not None:
        service.description = obj_in.description
    if obj_in.images is not None:
        service.images = obj_in.images
    if obj_in.service_include is not None:
        service.service_include = obj_in.service_include
    if obj_in.address is not None:
        service.address = obj_in.address.model_dump()
    
    # Update availability rules if provided
    if obj_in.availability_rules is not None:
        # Delete existing rules
        session.exec(
            select(AvailabilityRule).where(AvailabilityRule.service_id == service.id)
        ).delete()
        
        # Create new rules
        for rule_data in obj_in.availability_rules:
            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)
            )
            session.add(rule)
    
    session.add(service)
    session.commit()
    session.refresh(service)
    
    # Prepare and return the response
    service_dict = _prepare_service_dict(service, session)
    return ServiceOut(**service_dict)


def get_services_by_raider(
    *,
    session: Session,
    raider_id: UUID
) -> ServicesOut:
    """
    Get all services (fotografía o música) vinculados a un raider
    """
    # Buscar servicios de fotografía vinculados a este raider
    query_foto = select(Service).where(
        Service.type == ServiceType.FOTOGRAFIA,
        Service.fotografia_id.isnot(None)
    )
    # Buscar servicios de música vinculados a este raider
    query_musica = select(Service).where(
        Service.type == ServiceType.MUSICA,
        Service.musica_id.isnot(None)
    )

    # Filtrar por raider_id en la tabla correspondiente
    servicios_foto = []
    for service in session.exec(query_foto).all():
        fotografia = session.get(Fotografia, service.fotografia_id)
        if fotografia and fotografia.raider_id == raider_id:
            servicios_foto.append(service)

    servicios_musica = []
    for service in session.exec(query_musica).all():
        musica = session.get(Musica, service.musica_id)
        if musica and musica.raider_id == raider_id:
            servicios_musica.append(service)

    servicios = servicios_foto + servicios_musica

    # Construir la respuesta usando _prepare_service_dict
    services_list = [_prepare_service_dict(service, session) for service in servicios]
    return ServicesOut(data=services_list, count=len(services_list))
