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

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

from app.models import (Catering, CateringCreate, CateringUpdate, TipoCatering, TipoEntrega, TipoMenu, Alergenos, Menu, Articulo, TipoArticulo,
                        Service, ServiceCreateCatering, ServiceUpdateCatering, ServiceCateringOut, ServiceType, Address, AvailabilityRule)



def get_catering(*, session: Session, catering_id: UUID) -> Optional[Catering]:
    """
    Get catering by id
    """
    return session.get(Catering, catering_id)



def update_catering(*, session: Session, catering: Catering, catering_in: CateringUpdate) -> Catering:
    """
    Update catering and its service if provided
    """
    # Update catering fields
    catering_data = catering_in.model_dump(exclude={"name", "description", "price"}, exclude_unset=True)
    for key, value in catering_data.items():
        setattr(catering, key, value)
    
    # Update service if provided
    if any(field in catering_in.model_dump() for field in ["name", "description", "price"]):
        service = session.get(Service, catering.service_id)
        if not service:
            raise HTTPException(status_code=404, detail="Service not found")
        
        service_data = {
            k: v for k, v in catering_in.model_dump().items()
            if k in ["name", "description", "price"]
        }
        for key, value in service_data.items():
            setattr(service, key, value)
    
    session.add(catering)
    session.commit()
    session.refresh(catering)
    return catering


def _prepare_catering_dict(catering: Catering) -> Dict[str, Any]:
    """Helper function to prepare catering dictionary with correct types"""
    catering_dict = catering.dict()
    
    # Convert alergenos list to list of Alergenos enums
    if "alergenos" in catering_dict and isinstance(catering_dict["alergenos"], list):
        catering_dict["alergenos"] = [Alergenos(alergeno) for alergeno in catering_dict["alergenos"]]
    
    # Convert menu dict to Menu object if present
    if "menu" in catering_dict and isinstance(catering_dict["menu"], dict):
        try:
            menu_obj = Menu(**catering_dict["menu"])
            catering_dict["menu"] = menu_obj.model_dump()  # Convert back to dict for response
        except ValueError:
            # Si hay error en la conversión, dejamos el diccionario original
            pass
    
    # Convert articulo dict to Articulo object if present
    if "articulo" in catering_dict and isinstance(catering_dict["articulo"], dict):
        try:
            articulo_obj = Articulo(**catering_dict["articulo"])
            catering_dict["articulo"] = articulo_obj.model_dump()  # Convert back to dict for response
        except ValueError:
            # Si hay error en la conversión, dejamos el diccionario original
            pass
    
    # Convert tipo_entrega to enum
    if "tipo_entrega" in catering_dict:
        try:
            catering_dict["tipo_entrega"] = TipoEntrega(catering_dict["tipo_entrega"])
        except ValueError:
            # Si hay error en la conversión, dejamos el valor original
            pass
    
    # Convert tipo_catering to enum if present
    if "tipo_catering" in catering_dict and catering_dict["tipo_catering"]:
        try:
            catering_dict["tipo_catering"] = TipoCatering(catering_dict["tipo_catering"])
        except ValueError:
            # Si hay error en la conversión, dejamos el valor original
            pass
    
    return catering_dict


def create_catering_service(*, session: Session, obj_in: ServiceCreateCatering, account_id: UUID) -> ServiceCateringOut:
    """Create a new catering 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=ServiceType.CATERING,
        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 the catering with relation to service
    catering_data = obj_in.catering.copy() if isinstance(obj_in.catering, dict) else {}
    
    # Convert alergenos to list of Alergenos enums
    if "alergenos" in catering_data and isinstance(catering_data["alergenos"], list):
        catering_data["alergenos"] = [Alergenos(alergeno) for alergeno in catering_data["alergenos"]]
    
    # Convert tipo_entrega to enum
    if "tipo_entrega" in catering_data:
        catering_data["tipo_entrega"] = TipoEntrega(catering_data["tipo_entrega"])
    
    # Convert tipo_catering to enum if present
    if "tipo_catering" in catering_data and catering_data["tipo_catering"]:
        catering_data["tipo_catering"] = TipoCatering(catering_data["tipo_catering"])
    
    # Convert menu dict to Menu object if present and then back to dict for storage
    if "menu" in catering_data and isinstance(catering_data["menu"], dict):
        try:
            menu_obj = Menu(**catering_data["menu"])
            catering_data["menu"] = menu_obj.model_dump()  # Convert back to dict for storage
        except ValueError as e:
            raise HTTPException(
                status_code=400,
                detail=f"Error en el menú: {str(e)}"
            )
    
    # Convert articulo dict to Articulo object if present and then back to dict for storage
    if "articulo" in catering_data and isinstance(catering_data["articulo"], dict):
        try:
            articulo_obj = Articulo(**catering_data["articulo"])
            catering_data["articulo"] = articulo_obj.model_dump()  # Convert back to dict for storage
        except ValueError as e:
            raise HTTPException(
                status_code=400,
                detail=f"Error en el artículo: {str(e)}"
            )
    
    catering = Catering(**catering_data, id=service.id)
    session.add(catering)
    service.catering_id = catering.id
    session.add(service)
    
    # 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)
    session.refresh(catering)
    
    # Get availability rules
    rules = session.exec(
        select(AvailabilityRule).where(AvailabilityRule.service_id == service.id)
    ).all()
    availability_rules_dict = [rule.dict() for rule in rules]
    
    # Construct the response
    catering_dict = _prepare_catering_dict(catering)
    return ServiceCateringOut(
        id=service.id,
        type=service.type,
        name=service.name,
        images=service.images,
        description=service.description,
        service_include=service.service_include,
        address=service.address,
        account_id=service.account_id,
        availability_rules=availability_rules_dict,
        catering_id=catering.id,
        catering=catering_dict
    )


def get_catering_service(*, session: Session, service_id: UUID) -> Optional[ServiceCateringOut]:
    """Get a catering service by ID"""
    service = session.get(Service, service_id)
    if not service or service.type != ServiceType.CATERING:
        return None
    
    catering = session.get(Catering, service.catering_id)
    if not catering:
        return None
    
    # Get availability rules
    rules = session.exec(
        select(AvailabilityRule).where(AvailabilityRule.service_id == service.id)
    ).all()
    availability_rules_dict = [rule.dict() for rule in rules]
    
    # Construct the response
    catering_dict = _prepare_catering_dict(catering)
    return ServiceCateringOut(
        id=service.id,
        type=service.type,
        name=service.name,
        images=service.images,
        description=service.description,
        service_include=service.service_include,
        address=service.address,
        account_id=service.account_id,
        availability_rules=availability_rules_dict,
        catering_id=catering.id,
        catering=catering_dict
    )


def get_all_caterings(*, session: Session, skip: int = 0, limit: int = 100) -> Tuple[List[ServiceCateringOut], int]:
    """Get all catering services"""
    # Count query
    count_statement = select(func.count(Service.id)).where(Service.type == ServiceType.CATERING)
    count = session.exec(count_statement).one()
    
    # Results query
    results = session.exec(
        select(Service, Catering)
        .join(Catering, Service.id == Catering.id)
        .where(Service.type == ServiceType.CATERING)
        .offset(skip)
        .limit(limit)
    ).all()
    
    # Construct the response
    caterings = []
    for service, catering in results:
        # Get availability rules
        rules = session.exec(
            select(AvailabilityRule).where(AvailabilityRule.service_id == service.id)
        ).all()
        availability_rules_dict = [rule.dict() for rule in rules]
        
        catering_dict = _prepare_catering_dict(catering)
        caterings.append(ServiceCateringOut(
            id=service.id,
            type=service.type,
            name=service.name,
            images=service.images,
            description=service.description,
            service_include=service.service_include,
            address=service.address,
            account_id=service.account_id,
            availability_rules=availability_rules_dict,
            catering_id=catering.id,
            catering=catering_dict
        ))
    
    return caterings, count


def get_caterings_by_account(*, session: Session, account_id: UUID, skip: int = 0, limit: int = 100) -> Tuple[List[ServiceCateringOut], int]:
    """Get all catering services for an account"""
    # Count query
    count_statement = select(func.count(Service.id)).where(
        Service.account_id == account_id,
        Service.type == ServiceType.CATERING
    )
    count = session.exec(count_statement).one()
    
    # Results query
    results = session.exec(
        select(Service, Catering)
        .join(Catering, Service.id == Catering.id)
        .where(
            Service.account_id == account_id,
            Service.type == ServiceType.CATERING
        )
        .offset(skip)
        .limit(limit)
    ).all()
    
    # Construct the response
    caterings = []
    for service, catering in results:
        # Get availability rules
        rules = session.exec(
            select(AvailabilityRule).where(AvailabilityRule.service_id == service.id)
        ).all()
        availability_rules_dict = [rule.dict() for rule in rules]
        
        catering_dict = _prepare_catering_dict(catering)
        caterings.append(ServiceCateringOut(
            id=service.id,
            type=service.type,
            name=service.name,
            images=service.images,
            description=service.description,
            service_include=service.service_include,
            address=service.address,
            account_id=service.account_id,
            availability_rules=availability_rules_dict,
            catering_id=catering.id,
            catering=catering_dict
        ))
    
    return caterings, count


def get_caterings_filtered(
    *,
    session: Session,
    tipo_entrega: Optional[TipoEntrega] = None,
    tipo_catering: Optional[TipoCatering] = None,
    num_comensales: Optional[int] = None,
    duracion: Optional[float] = None,
    menu_or_articulo: Optional[bool] = None,
    tipo_menu: Optional[TipoMenu] = None,
    tipo_articulo: Optional[TipoArticulo] = None,
    skip: int = 0,
    limit: int = 100
) -> Tuple[List[ServiceCateringOut], int]:
    """Get filtered catering services"""
    # Base query
    query = (
        select(Service, Catering)
        .join(Catering, Service.id == Catering.id)
        .where(Service.type == ServiceType.CATERING)
    )
    
    # Apply filters
    if tipo_entrega:
        query = query.where(Catering.tipo_entrega == tipo_entrega)
    if tipo_catering:
        query = query.where(Catering.tipo_catering == tipo_catering)
    if num_comensales:
        query = query.where(
            Catering.min_comensales <= num_comensales,
            Catering.max_comensales >= num_comensales
        )
    if duracion:
        query = query.where(Catering.duracion == duracion)
    
    # Filter by menu or article
    if menu_or_articulo is not None:
        if menu_or_articulo:
            query = query.where(
                Catering.menu.is_not(None),
                Catering.articulo.is_(None)
            )
        else:
            query = query.where(
                Catering.articulo.is_not(None),
                Catering.menu.is_(None)
            )
    
    # Menu or Article type filter
    if tipo_menu:
        query = query.where(Catering.menu.is_not(None))
        results = session.exec(query.offset(skip).limit(limit)).all()
        filtered_results = []
        for service, catering in results:
            if catering.menu and isinstance(catering.menu, dict) and catering.menu.get("type_menu") == tipo_menu:
                filtered_results.append((service, catering))
        results = filtered_results
        count = len(filtered_results)
    elif tipo_articulo:
        query = query.where(Catering.articulo.is_not(None))
        results = session.exec(query.offset(skip).limit(limit)).all()
        filtered_results = []
        for service, catering in results:
            if catering.articulo and isinstance(catering.articulo, dict) and catering.articulo.get("type_articulo") == tipo_articulo:
                filtered_results.append((service, catering))
        results = filtered_results
        count = len(filtered_results)
    else:
        results = session.exec(query.offset(skip).limit(limit)).all()
        count_query = select(func.count()).select_from(query.subquery())
        count = session.exec(count_query).one()
    
    # Construct the response
    services = []
    for service, catering in results:
        # Get availability rules
        rules = session.exec(
            select(AvailabilityRule).where(AvailabilityRule.service_id == service.id)
        ).all()
        availability_rules_dict = [rule.dict() for rule in rules]
        
        catering_dict = _prepare_catering_dict(catering)
        services.append(ServiceCateringOut(
            id=service.id,
            type=service.type,
            name=service.name,
            images=service.images,
            description=service.description,
            service_include=service.service_include,
            address=service.address,
            account_id=service.account_id,
            availability_rules=availability_rules_dict,
            catering_id=catering.id,
            catering=catering_dict
        ))
    
    return services, count


def get_caterings_filtered_me(
    *,
    session: Session,
    account_id: UUID,
    tipo_entrega: Optional[TipoEntrega] = None,
    tipo_catering: Optional[TipoCatering] = None,
    num_comensales: Optional[int] = None,
    duracion: Optional[float] = None,
    tipo_menu: Optional[TipoMenu] = None,
    tipo_articulo: Optional[TipoArticulo] = None,
    skip: int = 0,
    limit: int = 100
) -> Tuple[List[ServiceCateringOut], int]:
    """Get filtered catering services for an account"""
    # Base query
    query = (
        select(Service, Catering)
        .join(Catering, Service.id == Catering.id)
        .where(
            Service.type == ServiceType.CATERING,
            Service.account_id == account_id
        )
    )
    
    # Apply filters
    if tipo_entrega:
        query = query.where(Catering.tipo_entrega == tipo_entrega)
    if tipo_catering:
        query = query.where(Catering.tipo_catering == tipo_catering)
    if num_comensales:
        query = query.where(
            Catering.min_comensales <= num_comensales,
            Catering.max_comensales >= num_comensales
        )
    if duracion:
        query = query.where(Catering.duracion == duracion)
    
    # Menu or Article type filter
    if tipo_menu:
        query = query.where(Catering.menu.is_not(None))
        # Filter by menu type (stored in JSON)
        results = session.exec(query.offset(skip).limit(limit)).all()
        filtered_results = []
        for service, catering in results:
            if catering.menu and catering.menu.get("type_menu") == tipo_menu:
                filtered_results.append((service, catering))
        results = filtered_results
    elif tipo_articulo:
        query = query.where(Catering.articulo.is_not(None))
        # Filter by article type (stored in JSON)
        results = session.exec(query.offset(skip).limit(limit)).all()
        filtered_results = []
        for service, catering in results:
            if catering.articulo and catering.articulo.get("type_articulo") == tipo_articulo:
                filtered_results.append((service, catering))
        results = filtered_results
    else:
        results = session.exec(query.offset(skip).limit(limit)).all()
    
    # Count total (including menu/article type filter)
    if tipo_menu or tipo_articulo:
        count = len(results)  # Usar el número de resultados filtrados
    else:
        count_query = select(func.count()).select_from(query.subquery())
        count = session.exec(count_query).one()
    
    # Construct the response
    services = []
    for service, catering in results:
        # Convert address dict to Address object if present
        address = None
        if service.address and isinstance(service.address, dict):
            address = Address(**service.address)
        
        # Convert menu dict to Menu object if present
        menu = None
        if catering.menu and isinstance(catering.menu, dict):
            menu = Menu(**catering.menu)
        
        # Convert articulo dict to Articulo object if present
        articulo = None
        if catering.articulo and isinstance(catering.articulo, dict):
            articulo = Articulo(**catering.articulo)
        
        # Convert alergenos list to list of Alergenos enums
        alergenos = None
        if catering.alergenos and isinstance(catering.alergenos, list):
            alergenos = [Alergenos(alergeno) for alergeno in catering.alergenos]
        
        # Create catering dict with proper types
        catering_dict = _prepare_catering_dict(catering)
        if menu:
            catering_dict["menu"] = menu
        if articulo:
            catering_dict["articulo"] = articulo
        if alergenos:
            catering_dict["alergenos"] = alergenos
        
        services.append(
            ServiceCateringOut(
                id=service.id,
                type=service.type,
                name=service.name,
                images=service.images,
                description=service.description,
                service_include=service.service_include,
                address=address,
                account_id=service.account_id,
                catering=catering_dict,
                catering_id=catering.id
            )
        )
    
    return services, count


def update_catering_service(*, session: Session, service: Service, catering: Catering, obj_in: ServiceUpdateCatering) -> ServiceCateringOut:
    """Update a catering service"""
    # Update service fields
    service_data = obj_in.dict(exclude={"catering", "availability_rules"}, exclude_unset=True)
    address_dict = None
    if service_data.get("address"):
        address = service_data["address"]
        address_dict = address.model_dump() if hasattr(address, 'model_dump') else address
        service_data["address"] = address_dict
    for key, value in service_data.items():
        setattr(service, key, value)
    
    # Actualizar reglas de disponibilidad si se proporcionan
    if obj_in.availability_rules is not None:
        # Eliminar reglas existentes
        existing_rules = session.exec(
            select(AvailabilityRule).where(AvailabilityRule.service_id == service.id)
        ).all()
        for rule in existing_rules:
            session.delete(rule)
        
        # Crear nuevas reglas
        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)
    
    # Update catering fields if provided
    if obj_in.catering and isinstance(obj_in.catering, dict):
        catering_data = obj_in.catering.copy()
        
        # Convert menu dict to Menu object if present and then back to dict for storage
        if "menu" in catering_data and isinstance(catering_data["menu"], dict):
            try:
                menu_obj = Menu(**catering_data["menu"])
                catering_data["menu"] = menu_obj.model_dump()  # Convert back to dict for storage
            except ValueError as e:
                raise HTTPException(
                    status_code=400,
                    detail=f"Error en el menú: {str(e)}"
                )
        
        # Convert articulo dict to Articulo object if present and then back to dict for storage
        if "articulo" in catering_data and isinstance(catering_data["articulo"], dict):
            try:
                articulo_obj = Articulo(**catering_data["articulo"])
                catering_data["articulo"] = articulo_obj.model_dump()  # Convert back to dict for storage
            except ValueError as e:
                raise HTTPException(
                    status_code=400,
                    detail=f"Error en el artículo: {str(e)}"
                )
        
        for key, value in catering_data.items():
            setattr(catering, key, value)
    
    session.add(service)
    session.add(catering)
    session.commit()
    session.refresh(service)
    session.refresh(catering)
    
    # Get availability rules
    rules = session.exec(
        select(AvailabilityRule).where(AvailabilityRule.service_id == service.id)
    ).all()
    availability_rules_dict = [rule.dict() for rule in rules]
    
    # Construct the response
    catering_dict = _prepare_catering_dict(catering)
    return ServiceCateringOut(
        id=service.id,
        type=service.type,
        name=service.name,
        images=service.images,
        description=service.description,
        service_include=service.service_include,
        address=service.address,
        account_id=service.account_id,
        availability_rules=availability_rules_dict,
        catering_id=catering.id,
        catering=catering_dict
    )


def delete_catering_service(*, session: Session, service: Service, catering: Catering) -> None:
    """Delete a catering service"""
    # Delete availability rules first
    rules = session.exec(
        select(AvailabilityRule).where(AvailabilityRule.service_id == service.id)
    ).all()
    for rule in rules:
        session.delete(rule)
    
    # Delete the catering and service
    session.delete(catering)
    session.delete(service)
    session.commit()