
import shutil
import base64
import pdfkit
from pathlib import Path
from typing import Tuple, List, Callable, Optional, Dict, Any
from jinja2 import Environment, FileSystemLoader, select_autoescape
from fastapi import HTTPException
from sqlalchemy.orm import Session
from pydantic import BaseModel
from io import BytesIO
import pdf417gen

# --- MODELOS ---
from api.v1.modules.a76.invoices.models import InvoiceHeader, InvoiceComplianceMx, InvoiceFinancials, InvoiceLogistics
from api.v1.modules.a76.general_catalogs.company.models import Company
from api.v1.modules.a76.pedmientos.models.pedimentos import Pedimentos
from api.v1.modules.a76.pedmientos.models.pedimento_validation import PedimentoValidation
from api.v1.modules.a76.customs_brokers.models import CustomsBroker, CustomsBrokerPersonnel
from api.v1.modules.a76.items.models import LineItem
from api.v1.modules.a76.clients_and_providers.models import ClientProvider, ClientProviderAddress, ClientProviderPrograms

# --- SCHEMAS FOR TEMPLATE CONTEXT ---
class EmpresaSchema(BaseModel):
    rfc: str
    razon_social: str
    direccion_completa: str
    tax_id: Optional[str] = None # Extra info just in case

class PersonaSchema(BaseModel):
    nombre: str
    rfc: str
    curp: str

class AvisoSchema(BaseModel):
    pedimento_completo: str
    tipo_operacion: str
    clave_pedimento: str
    acus_valor: str
    aduana_seccion: str
    numero_remesa: str
    peso_bruto: str
    codigo_aceptacion: str
    codigo_barras_b64: Optional[str] = None
    clave_seccion: str
    marcas_numeros_bultos: str
    candados: List[str]
    vehiculo_placas: str
    vehiculo_tipo: str
    observaciones: str
    numero_certificado: str
    tipo_documento: str # NEW: Invoice Type
    firma_electronica: str

class AvisoConsolidadoContext(BaseModel):
    aviso: AvisoSchema
    empresa: EmpresaSchema
    agente: PersonaSchema
    mandatario: PersonaSchema

class AvisoConsolidadoExportacionService:
    def __init__(self):
        self.template_dir = Path(__file__).parent / "templates"
        self.jinja_env = Environment(
            loader=FileSystemLoader(self.template_dir),
            autoescape=select_autoescape(['html', 'xml'])
        )
        self.template = self.jinja_env.get_template('avcon_exp.html')

    def _get_wkhtmltopdf_config(self):
        path = shutil.which("wkhtmltopdf") or "/usr/local/bin/wkhtmltopdf"
        if not Path(path).exists():
            raise RuntimeError("wkhtmltopdf no encontrado.")
        return pdfkit.configuration(wkhtmltopdf=path)

    def obtener_datos(self, db: Session, invoice_id: int, company_id: int, progress_callback: Optional[Callable] = None) -> AvisoConsolidadoContext:
        try:
            with open("/tmp/barcode_debug.log", "a") as f: f.write(f"ENTER obtener_datos ID={invoice_id}\n")
            if progress_callback: progress_callback(10, "Buscando factura...")
            
            # Fetch minimal real data if possible, or use placeholders as requested
            header = db.query(InvoiceHeader).filter(InvoiceHeader.id == invoice_id, InvoiceHeader.company_id == company_id).first()
            if not header:
                # We can't strictly raise 404 if we want to support testing with non-existent IDs for pure UI check,
                # but valid workflow requires a real invoice. Raising 404 is better practice.
                raise HTTPException(status_code=404, detail="Factura no encontrada")

            company = db.query(Company).filter(Company.id == company_id).first()
            
            if progress_callback: progress_callback(30, "Preparando datos...")

            # --- FETCHING REAL DATA ---
            
            # 1. Compliance & Pedimento
            compliance = header.compliance_mx
            pedimento = None
            if compliance and compliance.pedimento_id:
                pedimento = db.query(Pedimentos).filter(Pedimentos.id == compliance.pedimento_id).first()
            
            # Pedimento Completo Construction
            pedimento_txt = "S/P"
            clave_ped = ""
            if pedimento:
                # Format: YY  OFF  LIC  NUMBER
                year = pedimento.year or ""
                office = pedimento.customs_office or ""
                lic = pedimento.license or ""
                num = pedimento.pedimento_number or ""
                pedimento_txt = f"{year}  {office}  {lic}  {num}"
                clave_ped = pedimento.pedimento_code or ""
            
            # 2. Importer/Exporter Data (Clarion 100% Match)
            # Logic:
            # IF EqiFex:EsCambioRegimen = 'S' THEN
            #     CliPro:Cliente = EqiFex:VendidoA
            # ELSE
            #     CliPro:Cliente = EqiFex:Proveedor
            # END
            
            target_entity_data = {
                "rfc": "",
                "razon_social": "",
                "direccion_completa": "DOMICILIO NO REGISTRADO"
            }
            
            target_client_id = None
            
            if compliance:
                 if compliance.is_regime_change:
                     target_client_id = compliance.sold_to_id
                 else:
                     target_client_id = compliance.provider_id
            
            if target_client_id:
                client_obj = db.query(ClientProvider).filter(ClientProvider.id == target_client_id).first()
                if client_obj:
                    # Fetch Address
                    c_addr = db.query(ClientProviderAddress).filter(ClientProviderAddress.client_id == target_client_id).first()
                    # Identificador fiscal único en ClientProvider.rfc (RFC o TAX-ID)
                    c_rfc = getattr(client_obj, "rfc", "") or ""
                    
                    c_dir_str = "DOMICILIO NO REGISTRADO"
                    if c_addr:
                        parts_c = []
                        if c_addr.streets: parts_c.append(c_addr.streets)
                        if c_addr.exterior_number: parts_c.append(f"No. {c_addr.exterior_number}")
                        if c_addr.interior_number: parts_c.append(f"Int. {c_addr.interior_number}")
                        if c_addr.neighborhood: parts_c.append(f"Col. {c_addr.neighborhood}")
                        if c_addr.postal_code: parts_c.append(f"CP {c_addr.postal_code}")
                        if c_addr.city: parts_c.append(c_addr.city)
                        if c_addr.state: parts_c.append(c_addr.state)
                        if c_addr.country: parts_c.append(c_addr.country)
                        
                        if parts_c:
                            c_dir_str = ", ".join(parts_c).upper()

                    target_entity_data = {
                        "rfc": c_rfc or "",
                        "razon_social": client_obj.name or client_obj.short_name or "",
                        "direccion_completa": c_dir_str
                    }

            empresa = EmpresaSchema(
                rfc=target_entity_data["rfc"],
                razon_social=target_entity_data["razon_social"],
                direccion_completa=target_entity_data["direccion_completa"]
            )
            
            # Destino/Origen (Clarion: Loc:DestinoOrigen = 'Destino/Origen:    '&EqiFex:DestinoOrigenCOVE)
            destino_origen_str = ""
            if compliance and compliance.origin_destination_cove:
                # Assuming enum value or string is what we want. 
                # If it's an Enum object, accessing .value is safer.
                val = compliance.origin_destination_cove
                if hasattr(val, 'value'): val = val.value
                destino_origen_str = f"Destino/Origen:    {val}"



            # 3. Datos Aviso (Invoice/Compliance/Logistics/Financials)
            financials = header.financials
            logistics = header.logistics
            
            # Fetch Items associated with this invoice (MOVED UP FOR WEIGHT CALCULATION)
            items = db.query(LineItem).filter(LineItem.invoice_id == invoice_id).all()
            
            # Peso Bruto
            peso_bruto_val = "0.0"
            calculated_gross_weight = 0.0
            
            # Calculate sum from items first
            if items:
                 for item in items:                                             
                        if item.quantity and item.quantity.gross_weight:
                            try:
                                calculated_gross_weight += float(item.quantity.gross_weight)
                            except (ValueError, TypeError):
                                pass

            if financials and financials.gross_weight and float(financials.gross_weight) > 0:
                 peso_bruto_val = f"{financials.gross_weight:,.2f}"
            elif calculated_gross_weight > 0:
                 peso_bruto_val = f"{calculated_gross_weight:,.2f}"
            elif pedimento and pedimento.gross_weight:
                 peso_bruto_val = f"{pedimento.gross_weight:,.2f}"

            # Candados (Seals)
            candados_list = []
            if logistics and logistics.seal_number:
                # Split by comma or space if multiple
                candados_list = [s.strip() for s in logistics.seal_number.replace(',', ' ').split() if s.strip()]

            # Vehiculo / Contenedor Logic (Replicating Clarion)
            # Clarion Logic:
            # 1. Check for explicit `DatosVehiculo`.
            # 2. Check for `EsFerrocarril`.
            # 3. Build string from Trailer + Transport.
            
            # Since we don't have a direct "DatosVehiculo" text field in Logistics (usually), we construct it.
            # However, we'll check if `license_plate` is being used as a catch-all or if we should build it.
            
            vehiculo_str = ""
            tipo_display = ""
            
            # Basic Logistics Data
            l_trailer = logistics.trailer_num.strip() if (logistics and logistics.trailer_num) else ""
            l_placa = logistics.license_plate.strip() if (logistics and logistics.license_plate) else ""
            l_trans_type = logistics.transport_type.strip() if (logistics and logistics.transport_type) else ""
            l_vehicle_num = logistics.vehicle_num.strip() if (logistics and logistics.vehicle_num) else ""
            l_container_types = logistics.container_types.strip() if (logistics and logistics.container_types) else ""

            # Check for Ferrocarril explicitly
            is_rail = False
            if "FERRO" in l_trans_type.upper() or "RAIL" in l_trans_type.upper():
                is_rail = True
            
            # --- LOGIC NUMERO / TIPO ---
            final_numero = ""
            final_tipo = ""

            # 1. Container Logic (Clarion: ContenedoresTipo parsing)
            # Format expected: "CONTENEDOR|TIPO,CONTENEDOR2|TIPO2..."
            if l_container_types:
                 # Take first container
                 first_cont_group = l_container_types.split(',')[0] # Split by comma
                 if '|' in first_cont_group:
                     parts = first_cont_group.split('|')
                     final_numero = parts[0].strip()
                     final_tipo = parts[1].strip()
                 else:
                     # Fallback if no pipe
                     final_numero = first_cont_group.strip()
                     final_tipo = "CONT" # Default?
            
            # 2. Transport = Container Logic
            elif l_trans_type.upper() == "CONTENEDOR":
                 # Use trailer num as container num
                 if l_trailer:
                     final_numero = l_trailer
                     # Try to find Type? In Clarion it does a DB lookup into GTrailers.ClaveContenedor
                     # We assume 'CONTENEDOR' or a default if not found in simplified logic
                     final_tipo = "CONT" 
            
            # 3. Trailer/General Logic (Fallback)
            if not final_numero:
                 # Construct valid string
                 parts_veh = []
                 if l_trailer:
                    parts_veh.append(f"TRAILER: {l_trailer}")
                    if not tipo_display: tipo_display = "TRAILER"
                 
                 if l_trans_type and l_trans_type.upper() != "NINGUNO":
                    if is_rail:
                        if l_vehicle_num:
                             parts_veh.append(f"CONTENEDOR: {l_vehicle_num}")
                             tipo_display = "FERROCARRIL"
                    else:
                        segment = l_trans_type
                        if l_vehicle_num: segment += f": {l_vehicle_num}"
                        parts_veh.append(segment)
                        if not tipo_display: tipo_display = l_trans_type
                 
                 if not parts_veh and l_placa:
                     parts_veh.append(f"PLACAS: {l_placa}")
                 
                 final_numero = ", ".join(parts_veh).upper()
                 final_tipo = tipo_display.upper()

            # Codigo de Aceptacion aka Acuse de Validacion
            codigo_aceptacion_val = ""
            if pedimento and pedimento.pedimento_validation:
                # Assuming relationship "pedimento_validation" exists on Pedimentos model (lazy loaded)
                # Or we can query it if relationship is scalar 'uselist=False'
                 if pedimento.pedimento_validation.validation_ack:
                     codigo_aceptacion_val = pedimento.pedimento_validation.validation_ack

            aviso = AvisoSchema(
                pedimento_completo=pedimento_txt, 
                tipo_operacion=header.operation_type.upper() if header.operation_type else "EXP",
                clave_pedimento=clave_ped,
                acus_valor=compliance.edocument.upper() if (compliance and compliance.edocument) else "",
                aduana_seccion=compliance.aduana if (compliance and compliance.aduana) else "",
                numero_remesa=str(compliance.remesa) if (compliance and compliance.remesa) else "",
                peso_bruto=peso_bruto_val,
                codigo_aceptacion=codigo_aceptacion_val,
                codigo_barras_b64=None, 
                clave_seccion=compliance.aduana if (compliance and compliance.aduana) else "", # Using Aduana as Section Key
                marcas_numeros_bultos=f"{financials.bundle_count} BULTOS" if (financials and financials.bundle_count) else "1 BULTOS",
                candados=candados_list,
                vehiculo_placas=final_numero,
                vehiculo_tipo=final_tipo,
                observaciones=(header.observation_es or "") + ("\n" + destino_origen_str if destino_origen_str else ""),
                numero_certificado=compliance.certificate_number if (compliance and compliance.certificate_number) else "",
                tipo_documento=header.document_type or "FACTURA", # Default
                firma_electronica=compliance.electronic_signature if (compliance and compliance.electronic_signature) else ""
            )
            
            # --- BARCODE GENERATION (PDF417) ---
            # Replicating Clarion "LLENADOCODIGODEBARRAS" logic
            try:
                # Debug Log
                debug_log = []
                debug_log.append(f"Processing Invoice {invoice_id}")
                
                # 1. Patente (4 Digits) - From Pedimento or Compliance
                patente_txt = pedimento.license if pedimento and pedimento.license else ""
                if not patente_txt and pedimento_txt:
                     # Fallback parsing "YY  OFF  LIC  NUMBER" -> LIC is index 2 (0, 1, 2)
                     try:
                         parts = pedimento_txt.split()
                         if len(parts) >= 3: patente_txt = parts[2]
                     except: pass
                
                # 2. Pedimento Number (7 Digits)
                pedimento_num = pedimento.pedimento_number if pedimento and pedimento.pedimento_number else ""
                if not pedimento_num and pedimento_txt:
                    try:
                         parts = pedimento_txt.split()
                         if len(parts) >= 4: pedimento_num = parts[3]
                    except: pass

                # 3. Recinto (3 chars) - Default to 000 if invalid/missing as per Clarion 'ELSE LINEPRINT('000'...'
                # Clarion: Loc:Recinto = EqiFex:Recinto
                recinto_txt = "000"
                if compliance and compliance.enclosure:
                     recinto_txt = compliance.enclosure[:3]
                if not recinto_txt: recinto_txt = "000" 
                
                # 4. E-Document
                edoc_txt = aviso.acus_valor # Already uppercased

                # 5. Num Contenedor (Rail) or 000...
                # Clarion: IF Loc:EsFerrocarril = 'SI' ... LINEPRINT(CLIP(Loc:NumContenedor)) ELSE LINEPRINT('0000000000000')
                # We reused logic for 'vehiculo_placas' and 'vehiculo_tipo' earlier.
                # Let's re-evaluate "EsFerrocarril" logic safely
                is_rail_bar = False
                if "FERRO" in final_tipo.upper() or "RAIL" in final_tipo.upper():
                    is_rail_bar = True
                
                field_5 = "0000000000000"
                if is_rail_bar:
                     # We extracted container into 'parts_veh' earlier but let's grab from raw if possible or from aviso?
                     # In our logic above: "CONTENEDOR: {l_vehicle_num}" was added to textual description.
                     # Let's use compliance.container_ids or logistics.vehicle_num
                     c_num = logistics.vehicle_num if logistics and logistics.vehicle_num else ""
                     if c_num: field_5 = c_num
                
                # 6. Firma Electronica
                firma_txt = aviso.firma_electronica

                # 7. Cantidad Comercial (Format @n015.3 -> 15 chars total, 3 decimals?)
                # We need to sum quantities.
                cant_total = 0.0
                
                if items:
                    for item in items:                        
                        for line in item:
                            # Priority: Quantity (UMA or Standard)
                            q = 0.0
                            if line.quantity:
                                    try:
                                        if line.quantity.quantity_uma is not None:
                                            q = float(line.quantity.quantity_uma)
                                        elif line.quantity.quantity is not None:
                                            q = float(line.quantity.quantity)
                                    except (ValueError, TypeError):
                                        q = 0.0
                            cant_total += q
                
                # Format: 15 chars, 3 decimals? Actually Clarion LINEPRINT usually just prints the text.
                # Clarion 'CLIP(FORMAT(Loc:CantTotal,@n015.3))' removes spaces.
                cant_total_str = f"{cant_total:.3f}"



                # 8. Valor Total Dlls
                # Clarion: LINEPRINT(FORMAT(Loc:ValorTotalDlls,@n012)) -> Integer? Or just standard?
                # Clarion @n012 usually means right justified or just specific length? 
                # Code says: Loc:ValorTotalDlls = GSQLFile3.SQL3:C1 + (rounding logic).
                val_usd = 0.0
                if financials:
                    try:
                        # Use value_me (Foreign Currency) as primary source for USD amount
                        if financials.value_me is not None:
                            val_usd = float(financials.value_me)
                        elif financials.value_mn is not None:
                             # Fallback to MN if ME is missing (though technically incorrect for USD field, avoids crash)
                            val_usd = float(financials.value_mn)
                    except (ValueError, TypeError):
                        val_usd = 0.0
                
                # Clarion logic:
                # IF GSQLFile3.SQL3:C1 > 0 AND GSQLFile3.SQL3:C1 < 1 THEN
                #      Loc:ValorTotalDlls = GSQLFile3.SQL3:C1 + (1 - GSQLFile3.SQL3:C1) (Result is 1.0)
                # ELSE ... ROUND(...,1) or Raw.
                
                final_val_usd = val_usd
                if 0.0 < val_usd < 1.0:
                    final_val_usd = 1.0
                elif (val_usd - int(val_usd)) > 0 and (val_usd - int(val_usd)) < 0.5:
                     # Clarion: IF Loc:Decimal > 0 AND Loc:Decimal < 0.5 THEN Loc:ValorTotalDlls = ROUND(GSQLFile3.SQL3:C1,1)
                     # Round to 1 decimal place? Or standard round? Python round matches generally.
                     final_val_usd = round(val_usd, 1)
                
                val_usd_str = f"{final_val_usd:.2f}" 

                # 9. Cant Embarques (Rail)
                field_9 = "000000000000"
                if is_rail_bar:
                    # Logic for Cant Embarques?
                    # Clarion: EqiFex:CantGuiasEmbarque
                    # usage unknown in current DB. Defaulting to 0.
                    pass

                # 10. NIU / DTA (Rail)
                field_10 = "0000000000000"
                if is_rail_bar:
                    # Clarion: EqiFex:NumeroNIU
                    if compliance and compliance.niu:
                        field_10 = compliance.niu
                
                # 11. Remesa (4 chars)
                remesa_txt = str(compliance.remesa) if (compliance and compliance.remesa) else "0"
                
                # 12. Filler
                field_12 = "00000000.000"

                # Construct Line Prints (Text content for barcode)
                # Clarion LINEPRINT separates by NewLine? Or is it one long string?
                # "Glo:GeneraTXT" is a file. LINEPRINT appends a line.
                # So the Barcode Content is a multi-line string or specific format.
                # PDF417 normally encodes the full text block.
                
                # 1. Patente (4 Digits)
                # Formatted: @P####P -> 4 digits.
                # Assuming simple string slice or pad.
                patente_formatted = f"{patente_txt}".strip()[:4]
                
                # 2. Pedimento (7 Digits)
                pedimento_formatted = f"{pedimento_num}".strip()[:7]
                
                # 3. Recinto (3 Digits Zero Padded @n03)
                # Ensure it's numeric-like for zero padding or just string pad?
                # Clarion FORMAT(Loc:Recinto,@n03) implies numeric.
                try:
                    recinto_val = int(recinto_txt)
                    recinto_formatted = f"{recinto_val:03d}"
                except:
                    recinto_formatted = "000"

                # 4. E-Document (Left aligned, clipped)
                edoc_formatted = edoc_txt.strip()

                # 5. Container (13 chars?) or Rail Logic
                # Clarion: IF Rail -> CLIP(Loc:NumContenedor) ELSE '0000000000000'
                if is_rail_bar and field_5 and len(field_5) > 0:
                     field_5_formatted = field_5.strip()
                else:
                     field_5_formatted = "0000000000000"
                
                # 6. Firma (Clipped)
                firma_formatted = firma_txt.strip()

                # 7. Cantidad (FORMAT(Loc:CantTotal,@n015.3)) -> 15 chars, 3 decimals, Zero Padded?
                # Python f"{val:015.3f}" produces 15 chars total (including dot) with zero padding.
                cant_total_formatted = f"{cant_total:015.3f}"

                # 8. Valor USD (FORMAT(Loc:ValorTotalDlls,@n012)) -> 12 chars, Integer?, Zero Padded?
                # If Clarion @n012 means Integer:
                # But previously we calculated rounding. If it is integer, we cast to int.
                # Clarion default doubles formatted with @n012 usually rounds to integer.
                # Let's assume Integer Zero Padded for now based on @n012 (no decimal part).
                val_usd_formatted = f"{int(final_val_usd):012d}"

                # 9. Cant Embarques (Rail) (FORMAT(...,@n012))
                field_9_formatted = "000000000000"
                if is_rail_bar:
                    # If we had a value... assuming 0 generally.
                    pass

                # 10. NIU (Rail) (@s13 -> String 13 chars?) or DTA
                # Clarion: LINEPRINT(CLIP(FORMAT(Loc:NumeroNIU,@s13)),Glo:GeneraTXT)
                # CLIP removes spaces, FORMAT @s13 makes it string 13?
                # Actually CLIP(FORMAT(...,@s13)) might just mean "The string value".
                # The ELSE is '0000000000000' (13 chars).
                field_10_formatted = "0000000000000"
                if is_rail_bar and field_10 != "0000000000000":
                    field_10_formatted = field_10.strip()
                
                # 11. Remesa (FORMAT(...,@n04) -> 4 digits zero padded)
                try:
                    remesa_val = int(remesa_txt)
                    remesa_formatted = f"{remesa_val:04d}"
                except:
                    remesa_formatted = "0000"

                # 12. Filler / Appendix 17
                # Clarion Logic:
                # IF TipoFactura = 'IMPOTEMP'/'EXPO'/'IMPODEF' ... IF Apendice17=1 -> '000000000003' ELSE '00000000.000'
                # Default '00000000.000'
                field_12_formatted = "00000000.000"
                if compliance and compliance.appendix_17 == 1:
                     # Check Doc Type? Assuming broadly for now based on flag.
                     field_12_formatted = "000000000003"

                barcode_lines = [
                    patente_formatted,
                    pedimento_formatted,
                    recinto_formatted,
                    edoc_formatted,
                    field_5_formatted,
                    firma_formatted,
                    cant_total_formatted,
                    val_usd_formatted,
                    field_9_formatted,
                    field_10_formatted,
                    remesa_formatted,
                    field_12_formatted
                ]
                
                # Join with appropriate separator. Clarion LINEPRINT adds CR/LF (Windows).
                barcode_content = "\r\n".join(barcode_lines)
                
                # Generate Image
                codes = pdf417gen.encode(barcode_content, columns=14)
                image = pdf417gen.render_image(codes, scale=5, padding=5)
                
                # Convert to B64
                buffered = BytesIO()
                image.save(buffered, format="PNG")
                img_str = base64.b64encode(buffered.getvalue()).decode("utf-8")
                
                aviso.codigo_barras_b64 = f"data:image/png;base64,{img_str}"
                debug_log.append("SUCCESS: Barcode generated.")

            except Exception as e:
                import traceback
                error_msg = f"Error generando codigo de barras InvID={invoice_id}: {str(e)}\n{traceback.format_exc()}"
                print(error_msg)
                debug_log.append(f"ERROR: {error_msg}")
                aviso.codigo_barras_b64 = None
            
            # Write Debug Log
            try:
                with open("/tmp/barcode_debug.log", "a") as f:
                    f.write("\n".join(debug_log) + "\n--------------------------------\n")
            except Exception as e_log:
                print(f"FAILED TO WRITE LOG: {e_log}")

            # 4. Agente Aduanal
            nombre_agente = ""
            rfc_agente = ""
            curp_agente = ""
            
            if compliance and compliance.customs_broker_id:
                broker = db.query(CustomsBroker).filter(CustomsBroker.id == compliance.customs_broker_id).first()
                if broker:
                    nombre_agente = broker.name or ""
                    rfc_agente = broker.tax_id or ""
                    curp_agente = broker.personal_id or ""

            agente = PersonaSchema(
                nombre=nombre_agente,
                rfc=rfc_agente,
                curp=curp_agente
            )

            # 5. Mandatario (CustomsBrokerPersonnel)
            mandatario = PersonaSchema(nombre="", rfc="", curp="")
            
            if broker:
                # Try to find personnel associated with this broker
                # Using direct query to ensure specific order if needed, typically just the first valid one
                personnel = db.query(CustomsBrokerPersonnel).filter(
                    CustomsBrokerPersonnel.customs_broker_id == broker.id
                ).first()
                
                if personnel:
                    # Construct name if main field is empty
                    full_name = personnel.name
                    if not full_name:
                        parts = []
                        if personnel.first_name: parts.append(personnel.first_name)
                        if personnel.last_name: parts.append(personnel.last_name)
                        if personnel.middle_name: parts.append(personnel.middle_name)
                        full_name = " ".join(parts)

                    mandatario = PersonaSchema(
                        nombre=full_name or "",
                        rfc=personnel.tax_id or "",
                        curp=personnel.personal_id or ""
                    )

            return AvisoConsolidadoContext(
                aviso=aviso,
                empresa=empresa,
                agente=agente,
                mandatario=mandatario
            )

        except Exception as e:
            print(f"Error Service A76 Export Aviso Consolidado: {e}")
            raise HTTPException(status_code=500, detail=f"Error: {str(e)}")

    def generar_pdf(self, db: Session, invoice_id: int, company_id: int, progress_callback: Optional[Callable] = None) -> Tuple[bytes, str, str]:
        if progress_callback: progress_callback(5, "Iniciando servicio de reporte...")
        
        datos = self.obtener_datos(db, invoice_id, company_id, progress_callback)
        
        if progress_callback: progress_callback(80, "Renderizando plantilla...")
        
        context = datos.model_dump()
        html_content = self.template.render(**context)
        nombre = f"AvisoConsolidado_Exp_{invoice_id}.pdf"
        
        if progress_callback: progress_callback(90, "Generando PDF final...")
        
        options = {
            'page-size': 'Letter', 
            'margin-top': '0.5in', 
            'margin-right': '0.5in', 
            'margin-bottom': '0.5in', 
            'margin-left': '0.5in', 
            'encoding': "UTF-8", 
            'enable-local-file-access': None
        }
        
        pdf = pdfkit.from_string(html_content, False, options=options, configuration=self._get_wkhtmltopdf_config())
        
        if progress_callback: progress_callback(100, "Completado")
        return pdf, nombre, "application/pdf"
