"""
Carga de conjuntos FK para validación de import CSV de trailers/cajas.
Clarion: GTipoTrailer, GPaises (Pais_Ame), GEstados (Descripcion / Clave_Ame), relación Estado-País.
"""
from typing import Set, Tuple, Optional, Dict
import logging

from core.database import CoreSessionLocal

logger = logging.getLogger(__name__)


def load_trailers_fk_sets(
    tenant_id: Optional[int] = None,
    company_id: Optional[int] = None,
) -> Tuple[
    Set[str],
    Set[str],
    Set[str],
    Set[Tuple[str, str]],
    Dict[str, str],
]:
    """
    Carga conjuntos para validación CSV de trailers (paridad Clarion).
    Devuelve:
    - valid_trailer_type_keys: códigos de trailer_type (GTipoTrailer)
    - valid_country_ame: claves americana de países (GPaises.Pais_Ame), mayúsculas
    - state_descriptions_upper: descripciones y mex_key en mayúsculas (GEstados)
    - state_country_set: (ame_key_pais, token_upper) para description o mex_key
    - state_ame_to_description: dict clave_ame_upper -> description_upper (opcional; vacío si State no tiene ame_key)
    """
    valid_trailer_type_keys: Set[str] = set()
    valid_country_ame: Set[str] = set()
    state_descriptions_upper: Set[str] = set()
    state_country_set: Set[Tuple[str, str]] = set()
    state_ame_to_description: Dict[str, str] = {}

    try:
        with CoreSessionLocal() as session:
            from api.v1.modules.public.reference_data.trailer_types.models import TrailerType
            from api.v1.modules.public.reference_data.countries.models import Country
            from api.v1.modules.public.reference_data.states.models import State

            for row in session.query(TrailerType.trailer_type_key).all():
                if row[0]:
                    valid_trailer_type_keys.add((row[0] or "").strip().upper())

            for row in session.query(Country.ame_key).all():
                if row[0]:
                    valid_country_ame.add((row[0] or "").strip().upper())

            for state in session.query(State).all():
                country = (
                    session.query(Country)
                    .filter(Country.m3_key == state.m3_key)
                    .first()
                )
                ame_country = None
                if country and (country.ame_key or "").strip():
                    ame_country = (country.ame_key or "").strip().upper()

                desc = (state.description or "").strip()
                if desc:
                    state_descriptions_upper.add(desc.upper())
                    if ame_country:
                        state_country_set.add((ame_country, desc.upper()))

                mex_key = (state.mex_key or "").strip()
                if mex_key:
                    mk = mex_key.upper()
                    state_descriptions_upper.add(mk)
                    if ame_country:
                        state_country_set.add((ame_country, mk))
                    if desc:
                        state_ame_to_description[mk] = desc.upper()

                ame_state = getattr(state, "ame_key", None)
                if ame_state and (ame_state or "").strip():
                    state_ame_to_description[(ame_state or "").strip().upper()] = (
                        desc.upper() if desc else (ame_state or "").strip().upper()
                    )

    except Exception as e:
        logger.warning("Trailers import: could not load FK sets: %s", e)

    return (
        valid_trailer_type_keys,
        valid_country_ame,
        state_descriptions_upper,
        state_country_set,
        state_ame_to_description,
    )
