"""
Carga de conjuntos FK para validación de import CSV de transportistas.
Clarion: GTransportista (ClaveTrans), GPaises (Pais_Ame), GEstados (Descripcion), relación Estado-País.
"""
from typing import Set, Tuple, Optional
import logging

from core.database import CoreSessionLocal

logger = logging.getLogger(__name__)


def load_transportistas_fk_sets(
    tenant_id: Optional[int] = None,
    company_id: Optional[int] = None,
) -> Tuple[
    Set[str],
    Set[str],
    Set[str],
    Set[Tuple[str, str]],
]:
    """
    Carga conjuntos para validación CSV de transportistas (paridad Clarion).
    Devuelve:
    - existing_transporter_keys: claves de transportistas existentes (tenant/company) en mayúsculas
    - valid_country_ame: claves americana de países (GPaises.Pais_Ame), mayúsculas
    - state_descriptions_upper: descripciones y claves cortas (mex_key) de estados en mayúsculas (GEstados)
    - state_country_set: set de (ame_key_pais, token_upper) para description o mex_key
    """
    existing_transporter_keys: Set[str] = set()
    valid_country_ame: Set[str] = set()
    state_descriptions_upper: Set[str] = set()
    state_country_set: Set[Tuple[str, str]] = set()

    try:
        with CoreSessionLocal() as session:
            from api.v1.modules.a76.transportation.transporters.models import Transporter
            from api.v1.modules.public.reference_data.countries.models import Country
            from api.v1.modules.public.reference_data.states.models import State

            if tenant_id is not None and company_id is not None:
                for row in (
                    session.query(Transporter.transporter_key)
                    .filter(
                        Transporter.tenant_id == tenant_id,
                        Transporter.company_id == company_id,
                    )
                    .all()
                ):
                    if row[0] and (row[0] or "").strip():
                        existing_transporter_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 = None
                if country and (country.ame_key or "").strip():
                    ame = (country.ame_key or "").strip().upper()

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

                mex_key = (state.mex_key or "").strip()
                if mex_key:
                    mk = mex_key.upper()
                    state_descriptions_upper.add(mk)
                    if ame:
                        state_country_set.add((ame, mk))

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

    return (
        existing_transporter_keys,
        valid_country_ame,
        state_descriptions_upper,
        state_country_set,
    )
