"""
Configuración de plantillas CSV: columnas que trae cada plantilla y cómo se mapean.
La plantilla se respeta tal cual: solo se leen columnas definidas aquí; el resto se ignora.
Solo se escribe en BD lo que los modelos de facturas aceptan (respetando models).
"""

from typing import Dict, List, Any, Optional

from ..common.cell_value import cell_to_str
from ..common.inject_csv_labels_en import inject_en_labels_into_template_columns
from ..common.template_locale import merge_download_header_into_lookup, merge_labels_into_lookup
from .invoice_csv_column_hints import apply_invoice_csv_hints

# Cada plantilla define sus columnas canónicas y alias (otros nombres que aceptamos en el CSV).
# canonical = nombre estándar con el que trabajamos internamente; debe coincidir con lo que
# espera la lógica de validación e insert (tasks.py).
# aliases = cabeceras alternativas que la plantilla .xls puede traer (ej. "Num Factura" → NUM FACTURA).

TEMPLATE_COLUMNS: Dict[str, List[Dict[str, Any]]] = {
    # --- Encabezado factura: Impo Temp (EstructuraEncFacImpoTemp.xls) - Clarion A-AD ---
    "imp_temp_header": [
        {"canonical": "PEDIMENTO", "aliases": ["NUMERO PEDIMENTO", "PEDIMENTO NUMERO", "NUMERO DE PEDIMENTO", "PED"]},
        {"canonical": "REMESA"},
        {"canonical": "NUMERO FACTURA", "aliases": ["NUM FACTURA", "FACTURA", "ID"]},
        {"canonical": "FECHA FACTURA", "aliases": ["FECHA"]},
        {"canonical": "TIPO DE CAMBIO"},
        {"canonical": "REGIMEN", "aliases": ["CLAVEDOCUMENTO"]},
        {"canonical": "CLAVE PROVEEDOR"},
        {"canonical": "CLAVE VENDIDO A"},
        {"canonical": "CLAVE ENVIADO A"},
        {"canonical": "AGENTE ADUANAL"},
        {"canonical": "CLAVE TRANSPORTISTA"},
        {"canonical": "NOMBRE CONDUCTOR"},
        {"canonical": "TIPO TRANSPORTE"},
        {"canonical": "CLAVE TRANSPORTE"},
        {"canonical": "NUMERO CAJA"},
        {"canonical": "NUMERO TRANSPORTE"},
        {"canonical": "TIPO MONEDA"},
        {"canonical": "CLAVE MONEDA"},
        {"canonical": "FLETES"},
        {"canonical": "VALOR SEGUROS"},
        {"canonical": "SEGUROS"},
        {"canonical": "EMBALAJES"},
        {"canonical": "OTROS INCREMENTABLES"},
        {"canonical": "CLAVE INCOTERM"},
        {"canonical": "PRECINTO"},
        {"canonical": "FECHA EMISION"},
        {"canonical": "TIPO PESO"},
        {"canonical": "E DOCUMENT", "aliases": ["E-DOCUMENT", "EDOCUMENT", "E DOCUMENT"]},
        {"canonical": "NUM OPERACION", "aliases": ["NUM. OPERACION", "NUMOPERACION", "NUM OPERACION"]},
        {"canonical": "ADUANA DE CRUCE"},
        {"canonical": "OBSERVACIONES E"},
        {"canonical": "OBSERVACIONES I"},
        {"canonical": "FACTURA ALTERNA"},
        {"canonical": "NUM PROYECTO", "aliases": ["NUMPROYECTO"]},
        {"canonical": "ORDEN COMPRA", "aliases": ["ORDENCOMPRA"]},
        {"canonical": "FACTURA EXPO REF", "aliases": ["FACTURAEXPOREF"]},
    ],
    # --- Encabezado factura: Impo Def (EstructuraEncFacImpoDef.xls) - 30 columnas Clarion ---
    "imp_def_header": [
        {"canonical": "PEDIMENTO", "aliases": ["NUMERO PEDIMENTO", "PEDIMENTO NUMERO", "NUMERO DE PEDIMENTO", "PED"]},
        {"canonical": "REMESA"},
        {"canonical": "NUMERO FACTURA", "aliases": ["NUM FACTURA", "FACTURA", "ID"]},
        {"canonical": "FECHA FACTURA", "aliases": ["FECHA"]},
        {"canonical": "TIPO DE CAMBIO"},
        {"canonical": "REGIMEN", "aliases": ["CLAVEDOCUMENTO"]},
        {"canonical": "CLAVE PROVEEDOR"},
        {"canonical": "CLAVE VENDIDO A", "aliases": ["CLAVE VENDIDO A:"]},
        {"canonical": "CLAVE ENVIADO A"},
        {"canonical": "AGENTE ADUANAL"},
        {"canonical": "CLAVE TRANSPORTISTA"},
        {"canonical": "NOMBRE CONDUCTOR"},
        {"canonical": "TIPO TRANSPORTE"},
        {"canonical": "CLAVE TRANSPORTE"},
        {"canonical": "NUMERO CAJA"},
        {"canonical": "NUMERO TRANSPORTE"},
        {"canonical": "TIPO MONEDA"},
        {"canonical": "CLAVE MONEDA"},
        {"canonical": "FLETES"},
        {"canonical": "VALOR SEGUROS"},
        {"canonical": "SEGUROS"},
        {"canonical": "EMBALAJES"},
        {"canonical": "OTROS INCREMENTABLES"},
        {"canonical": "CLAVE INCOTERM"},
        {"canonical": "PRECINTO"},
        {"canonical": "FECHA EMISION"},
        {"canonical": "TIPO PESO"},
        {"canonical": "E DOCUMENT", "aliases": ["E-DOCUMENT", "EDOCUMENT", "E DOCUMENT"]},
        {"canonical": "NUM OPERACION", "aliases": ["NUM. OPERACION", "NUMOPERACION", "NUM OPERACION"]},
        {"canonical": "ADUANA DE CRUCE"},
        {"canonical": "OBSERVACIONES E"},
        {"canonical": "OBSERVACIONES I"},
    ],
    # --- Encabezado factura: Expo (EstructuraEncFacExpoCamReg.xls) - Clarion A-AE ---
    # PEDIMENTO, REMESA, NUMERO FACTURA, FECHA FACTURA, TIPO DE CAMBIO, REGIMEN, CLAVE PROVEEDOR,
    # CLAVE VENDIDO A:, CLAVE ENVIADO A, AGENTE ADUANAL, ... MANIFIESTO, E-DOCUMENT, NUM. OPERACION,
    # ENVIADO POR, ADUANA DE CRUCE, OBSERVACIONES E, OBSERVACIONES I, FACTURA ALTERNA
    "exp_def_header": [
        {"canonical": "PEDIMENTO", "aliases": ["NUMERO PEDIMENTO", "PEDIMENTO NUMERO", "NUMERO DE PEDIMENTO", "PED"]},
        {"canonical": "REMESA"},
        {"canonical": "NUMERO FACTURA", "aliases": ["NUM FACTURA", "FACTURA", "ID"]},
        {"canonical": "FECHA FACTURA", "aliases": ["FECHA"]},
        {"canonical": "TIPO DE CAMBIO"},
        {"canonical": "REGIMEN", "aliases": ["CLAVEDOCUMENTO"]},
        {"canonical": "CLAVE PROVEEDOR"},
        {"canonical": "CLAVE VENDIDO A", "aliases": ["CLAVE VENDIDO A:"]},
        {"canonical": "CLAVE ENVIADO A"},
        {"canonical": "AGENTE ADUANAL"},
        {"canonical": "CLAVE TRANSPORTISTA"},
        {"canonical": "NOMBRE CONDUCTOR"},
        {"canonical": "TIPO TRANSPORTE"},
        {"canonical": "CLAVE TRANSPORTE"},
        {"canonical": "NUMERO CAJA"},
        {"canonical": "NUMERO TRANSPORTE"},
        {"canonical": "TIPO MONEDA"},
        {"canonical": "CLAVE MONEDA"},
        {"canonical": "FLETES"},
        {"canonical": "VALOR SEGUROS"},
        {"canonical": "SEGUROS"},
        {"canonical": "EMBALAJES"},
        {"canonical": "OTROS INCREMENTABLES"},
        {"canonical": "CLAVE INCOTERM"},
        {"canonical": "PRECINTO"},
        {"canonical": "TIPO PESO"},
        {"canonical": "MANIFIESTO"},
        {"canonical": "E DOCUMENT", "aliases": ["E-DOCUMENT", "EDOCUMENT", "E DOCUMENT"]},
        {"canonical": "NUM OPERACION", "aliases": ["NUM. OPERACION", "NUMOPERACION", "NUM OPERACION"]},
        {"canonical": "ENVIADO POR"},
        {"canonical": "ADUANA DE CRUCE"},
        {"canonical": "OBSERVACIONES E"},
        {"canonical": "OBSERVACIONES I"},
        {"canonical": "FACTURA ALTERNA"},
    ],
    # --- Encabezado factura: Compras Mexicanas (Clarion VALIDA_TODA_FAC_COM_MEX / VALIDA_PARCIAL) ---
    # Estructura CSV: A,B=CAPTURAR CMEX; C=NUMERO FACTURA; D=FECHA FACTURA; E=TIPO DE CAMBIO; F=CAPTURAR CMEX;
    # G=CLAVE PROVEEDOR; H=CLAVE VENDIDO A; I=CLAVE ENVIADO A; J=CAPTURAR CMEX; K=CLAVE TRANSPORTISTA; ...; Z=OBSERVACIONES E
    "cmex_header": [
        {"canonical": "NUMERO FACTURA", "aliases": ["NUM FACTURA", "FACTURA"]},
        {"canonical": "FECHA FACTURA", "aliases": ["FECHA"]},
        {"canonical": "TIPO DE CAMBIO"},
        {"canonical": "CLAVE PROVEEDOR"},
        {"canonical": "CLAVE VENDIDO A", "aliases": ["CLAVE VENDIDO A:"]},
        {"canonical": "CLAVE ENVIADO A"},
        {"canonical": "CLAVE TRANSPORTISTA"},
        {"canonical": "NOMBRE CONDUCTOR"},
        {"canonical": "TIPO TRANSPORTE"},
        {"canonical": "CLAVE TRANSPORTE"},
        {"canonical": "NUMERO CAJA"},
        {"canonical": "NUMERO TRANSPORTE"},
        {"canonical": "TIPO MONEDA"},
        {"canonical": "CLAVE MONEDA"},
        {"canonical": "FLETES"},
        {"canonical": "VALOR SEGUROS"},
        {"canonical": "SEGUROS"},
        {"canonical": "EMBALAJES"},
        {"canonical": "OTROS INCREMENTABLES"},
        {"canonical": "CLAVE INCOTERM"},
        {"canonical": "PRECINTO"},
        {"canonical": "FECHA EMISION"},
        {"canonical": "TIPO PESO"},
        {"canonical": "OBSERVACIONES E"},
    ],
    # --- Partidas factura: Impo Temp (EstructuraParFacImpoTemp - paridad Clarion A-AG) ---
    "imp_temp_details": [
        {"canonical": "NUMERO FACTURA", "aliases": ["NUM FACTURA", "FACTURA"]},
        {"canonical": "LINEA", "aliases": ["RENGLON", "PARTIDA"]},
        {"canonical": "CLASE"},
        {"canonical": "CANTIDAD IMPORTADA", "aliases": ["CANTIDAD"]},
        {"canonical": "UNIDAD DE MEDIDA", "aliases": ["UNIDAD MEDIDA"]},
        {"canonical": "COSTO UNITARIO", "aliases": ["COSTOUNITARIO", "PRECIO UNITARIO", "PRECIOUNITARIO"]},
        {"canonical": "PESO NETO", "aliases": ["PESONETO"]},
        {"canonical": "PESO BRUTO", "aliases": ["PESOBRUTO"]},
        {"canonical": "CANTIDAD BULTOS", "aliases": ["CANTIDADBULTOS"]},
        {"canonical": "CLAVE BULTOS", "aliases": ["CLAVEBULTOS"]},
        {"canonical": "PAIS ORIGEN", "aliases": ["PAISORIGEN", "PAIS"]},
        {"canonical": "FRACCION ARANCELARIA", "aliases": ["FRACCION", "FRACCIONARANCELARIA"]},
        {"canonical": "PREFERENCIA ARANCELARIA", "aliases": ["PREFERENCIA", "PREFERENCIAARANCELARIA"]},
        {"canonical": "SECTOR"},
        {"canonical": "FRACCION AMERICANA", "aliases": ["FRACCIONAMERICANA"]},
        {"canonical": "ORDEN DE COMPRA", "aliases": ["ORDENCOMPRA", "ORDEN COMPRA"]},
        {"canonical": "DESCRIPCION ESPAÑOL", "aliases": ["DESCRIPCIONE", "DESCRIPCION"]},
        {"canonical": "DESCRIPCION INGLES", "aliases": ["DESCRIPCION INGLES", "DESCRIPCIONI"]},
        {"canonical": "MARCA"},
        {"canonical": "MODELO"},
        {"canonical": "ES PARTIDA O SUBPARTIDA", "aliases": ["ES PARTIDA O SUBPARTIDA", "ESSUBPARTIDA"]},
        {"canonical": "LINEA PRINCIPAL", "aliases": ["LINEAPRINCIPAL", "PARTIDA PRINCIPAL"]},
        {"canonical": "NUM. PARTE", "aliases": ["NUMPARTE", "NUMERO PARTE", "NUM PARTE"]},
        {"canonical": "SE PAGO IMPUESTO", "aliases": ["SE PAGO IMPUESTO", "SEPAGOIMPUESTO"]},
        {"canonical": "FORMA DE PAGO", "aliases": ["FORMADEPAGO", "FORMA PAGO"]},
        {"canonical": "METODO DE VALORACION", "aliases": ["METODODEVALORACION", "METODO VALORACION"]},
        {"canonical": "DESCRIPCION EXTRA", "aliases": ["DESCRIPCIONEXTRA"]},
        {"canonical": "INFORMACION ADICIONAL", "aliases": ["INFORMACIONADICIONAL"]},
        {"canonical": "AGREGAR/SUSTITUIR", "aliases": ["AGREGAR SUSTITUIR", "SUSTITUIR"]},
        {"canonical": "TOTAL"},
        {"canonical": "NUMERO ENTRADA", "aliases": ["NUMEROENTRADA", "NUM ENTRADA"]},
        {"canonical": "LOTE"},
        {"canonical": "ID TYPE", "aliases": ["IDTYPE"]},
    ],
    # --- Partidas: Impo Def y Expo - misma estructura ---
    "imp_def_details": None,
    "exp_def_details": None,
    # --- Partidas exportación definitiva (Clarion EstructuraParExpoCamReg A–V) ---
    "exp_def_partidas": [
        {"canonical": "NUMERO FACTURA EXPO", "aliases": ["NUMERO FACTURA EXPO.", "NUM FACTURA EXPO", "FACTURA EXPO"]},
        {"canonical": "LINEA EXPO", "aliases": ["LINEA EXPO.", "RENGLON EXPO"]},
        {"canonical": "TIPO DE IMPO", "aliases": ["TIPO DE IMPO.", "TIPO IMPO", "PROCEDENCIA"]},
        {"canonical": "FACTURA IMPO", "aliases": ["FACTURA IMPO.", "FACTURA IMPORTACION"]},
        {"canonical": "LINEA IMPO", "aliases": ["LINEA IMPO.", "LINEA IMPORTACION"]},
        {"canonical": "GENERA DESCARGA", "aliases": ["GENERA DESCARGA?", "DESCARGA"]},
        {"canonical": "CANTIDAD EXPORTADA/DESCARGAR", "aliases": ["CANTIDAD EXPORTADA", "CANTIDAD EXPORTADA/DESCARGAR", "CANTIDAD"]},
        {"canonical": "UNIDAD DE MEDIDA", "aliases": ["U.M.", "UNIDAD MEDIDA"]},
        {"canonical": "COSTO UNITARIO", "aliases": ["COSTOUNITARIO"]},
        {"canonical": "PESO NETO", "aliases": ["PESONETO"]},
        {"canonical": "PESO BRUTO", "aliases": ["PESOBRUTO"]},
        {"canonical": "SE PAGO IMPUESTO", "aliases": ["SE PAGO IMPUESTO? (SI o NO)", "SEPAGOIMPUESTO"]},
        {"canonical": "FORMA DE PAGO", "aliases": ["FORMADEPAGO", "FORMA PAGO"]},
        {"canonical": "DESCRIPCION EXTRA", "aliases": ["DESCRIPCION EXTRA", "DESCRIPCIONEXTRA"]},
        {"canonical": "INFORMACION ADICIONAL", "aliases": ["INFORMACION ADICIONAL", "INFORMACIONADICIONAL"]},
        {"canonical": "AGREGAR(A)/SUSTITUIR(S)", "aliases": ["AGREGAR(A)/SUSTITUIR(S)", "AGREGAR/SUSTITUIR", "SUSTITUIR"]},
        {"canonical": "LOTE"},
        {"canonical": "NUMERO ENTRADA", "aliases": ["NUMERO ENTRADA", "NUM ENTRADA"]},
        {"canonical": "ES PARTIDA/SUBPARTIDA", "aliases": ["ES PARTIDA/SUBPARTIDA", "ESSUBPARTIDA", "ES PARTIDA O SUBPARTIDA"]},
        {"canonical": "LINEA PRINCIPAL", "aliases": ["LINEAPRINCIPAL", "PARTIDA PRINCIPAL"]},
        {"canonical": "FRACCION AMERICANA", "aliases": ["FRACCION AMERICANA", "FRACCIONAMERICANA"]},
        {"canonical": "FRACCION ARANCELARIA", "aliases": ["FRACCION ARANCELARIA", "FRACCIONARANCELARIA"]},
        {"canonical": "CANTIDAD BULTOS", "aliases": ["CANTIDADBULTOS"]},
        {"canonical": "CLAVE BULTOS", "aliases": ["CLAVEBULTOS"]},
        {"canonical": "PAIS ORIGEN", "aliases": ["PAISORIGEN", "PAIS"]},
        {"canonical": "NUM. PARTE", "aliases": ["NUMPARTE", "NUMERO PARTE", "NUM PARTE"]},
        {"canonical": "ORDEN DE COMPRA", "aliases": ["ORDENCOMPRA", "ORDEN DE VENTA"]},
    ],
    # --- Series de Importación Temporal (EstructuraSeriesFacImpoTemp.xls) ---
    # Clarion: NUMERO FACTURA, LINEA FACTURA, LINEA SERIE, SERIE, MODELO, NUM PARTE, SUB MODELO, NUMERO ID
    "imp_temp_series": [
        {"canonical": "NUMERO FACTURA", "aliases": ["NUM FACTURA", "FACTURA"]},
        {"canonical": "LINEA FACTURA", "aliases": ["LINEA", "PARTIDA"]},
        {"canonical": "LINEA SERIE", "aliases": ["RENGLON"]},
        {"canonical": "SERIE", "aliases": ["NUMERO SERIE"]},
        {"canonical": "MODELO"},
        {"canonical": "NUM PARTE", "aliases": ["NUMPARTE", "NUMERO PARTE"]},
        {"canonical": "SUB MODELO", "aliases": ["SUBMODELO"]},
        {"canonical": "NUMERO ID", "aliases": ["NUMEROID"]},
        {"canonical": "COL_EXTRA"},  # Optional; if has value → desfase warning (Clarion)
    ],
    # --- Series de Importación Definitiva (misma estructura que TEM; cabeceras imagen: NUMERO/LINEA FAC, LINEA SER, etc.) ---
    "imp_def_series": [
        {"canonical": "NUMERO FACTURA", "aliases": ["NUM FACTURA", "FACTURA", "NUMERO / LINEA FAC"]},
        {"canonical": "LINEA FACTURA", "aliases": ["LINEA", "PARTIDA"]},
        {"canonical": "LINEA SERIE", "aliases": ["RENGLON", "LINEA SER"]},
        {"canonical": "SERIE", "aliases": ["NUMERO SERIE"]},
        {"canonical": "MODELO"},
        {"canonical": "NUM PARTE", "aliases": ["NUMPARTE", "NUMERO PARTE", "NUM PART"]},
        {"canonical": "SUB MODELO", "aliases": ["SUBMODELO", "SUB MODE"]},
        {"canonical": "NUMERO ID", "aliases": ["NUMEROID"]},
        {"canonical": "COL_EXTRA"},
    ],
    # --- Series de Exportación Definitiva (Clarion VALIDA_TODA_SERIES_EXPO / VALIDA_PARCIAL_SERIES_EXPO) ---
    # Misma estructura: NUMERO FACTURA, LINEA FACTURA, LINEA SERIE, SERIE, MODELO, NUM PARTE, SUB MODELO, NUMERO ID
    "exp_def_series": [
        {"canonical": "NUMERO FACTURA", "aliases": ["NUM FACTURA", "FACTURA", "FACTURA EXPO"]},
        {"canonical": "LINEA FACTURA", "aliases": ["LINEA", "PARTIDA"]},
        {"canonical": "LINEA SERIE", "aliases": ["RENGLON", "LINEA SER"]},
        {"canonical": "SERIE", "aliases": ["NUMERO SERIE"]},
        {"canonical": "MODELO"},
        {"canonical": "NUM PARTE", "aliases": ["NUMPARTE", "NUMERO PARTE", "NUM PART"]},
        {"canonical": "SUB MODELO", "aliases": ["SUBMODELO", "SUB MODE"]},
        {"canonical": "NUMERO ID", "aliases": ["NUMEROID"]},
        {"canonical": "COL_EXTRA"},
    ],
}

inject_en_labels_into_template_columns(TEMPLATE_COLUMNS)

apply_invoice_csv_hints(TEMPLATE_COLUMNS)


def _resolve_template_columns(template_id: str) -> Optional[List[Dict[str, Any]]]:
    cols = TEMPLATE_COLUMNS.get(template_id)
    if cols is not None:
        return cols
    if template_id == "imp_def_header":
        return TEMPLATE_COLUMNS.get("imp_temp_header")
    if template_id in ("imp_def_details", "exp_def_details"):
        return TEMPLATE_COLUMNS.get("imp_temp_details")
    if template_id == "exp_def_partidas":
        return TEMPLATE_COLUMNS.get("exp_def_partidas")
    if template_id == "cmex_details":
        return TEMPLATE_COLUMNS.get("imp_temp_details")
    if template_id == "cmex_series":
        return TEMPLATE_COLUMNS.get("imp_def_series")
    if template_id == "exp_def_series":
        return TEMPLATE_COLUMNS.get("exp_def_series")
    return None


def build_normalized_lookup(template_id: str, normalize_header_fn) -> Dict[str, str]:
    """
    Construye un diccionario: normalized_header -> canonical_name.
    normalize_header_fn(str) -> str debe ser la función que normaliza cabeceras (ej. mayúsculas, sin acentos).
    """
    cols = _resolve_template_columns(template_id)
    if not cols:
        return {}
    lookup: Dict[str, str] = {}
    for item in cols:
        canonical = item["canonical"]
        lookup[normalize_header_fn(canonical)] = canonical
        for alias in item.get("aliases") or []:
            lookup[normalize_header_fn(alias)] = canonical
        merge_labels_into_lookup(item, canonical, normalize_header_fn, lookup)
        merge_download_header_into_lookup(item, canonical, normalize_header_fn, lookup)
    return lookup


def row_from_template(row: Dict[str, Any], template_id: str, normalize_header_fn) -> Dict[str, Any]:
    """
    A partir de una fila CSV (dict header->value) y un template_id, devuelve un dict
    solo con las columnas de la plantilla, usando nombres canónicos.
    Así la plantilla se respeta: solo entran columnas definidas en la plantilla.
    """
    lookup = build_normalized_lookup(template_id, normalize_header_fn)
    if not lookup:
        return {normalize_header_fn(k): cell_to_str(v) for k, v in row.items()}
    out: Dict[str, Any] = {}
    for csv_header, value in row.items():
        key_norm = normalize_header_fn(csv_header)
        if key_norm in lookup:
            out[lookup[key_norm]] = cell_to_str(value)
    return out
