import logging
from sqlalchemy import text
from sqlalchemy.orm import Session
import configparser
import os
from typing import List, Optional
from datetime import datetime
from decimal import Decimal

from .schemas import (
    ImportTemporaryFilter, 
    ImportDefinitiveFilter, 
    ImportRepairFilter,
    MovementItem, 
    MovementItemDetailed,
    RangeType
)

logger = logging.getLogger(__name__)


class MovementService:
    """
    Service for handling movement operations, particularly temporary import movements.
    Integrates with legacy SQL Server databases for data extraction.
    """
    
    def _get_met_trans_config(self) -> int:
        """
        Read MetTrans configuration from Scaii.ini file.
        
        Returns:
            MetTrans value (0 or 1)
        """
        try:
            config = configparser.ConfigParser()
            config.read('Scaii.ini') 
            met_trans = config.getint('METTRANS', 'TipoCambio', fallback=0)
            logger.debug(f"INI met_trans value: {met_trans}")
            return met_trans
        except Exception as e:
            logger.warning(f"Could not read Scaii.ini, using default met_trans=0: {e}")
            return 0
    
    def _build_where_clause_temporary(self, filters: ImportTemporaryFilter) -> tuple:
        """
        Build WHERE clause and parameters for temporary imports query.
        
        Returns:
            Tuple of (where_string, params_dict)
        """
        where_clauses = []
        params = {}
        
        # Date range filter
        if filters.range_type.value == 'FF':
            where_clauses.append("EqiFim.FechaFactura >= :start_date AND EqiFim.FechaFactura <= :end_date")
        else:
            where_clauses.append("EqiPed.Fecha_Pago >= :start_date AND EqiPed.Fecha_Pago <= :end_date")
        
        params['start_date'] = filters.start_date
        params['end_date'] = filters.end_date

        # Status filter
        if not filters.include_cancelled:
            where_clauses.append("EqiFim.Estatus = 'AC'")
        
        # Provider filter
        if filters.provider:
            where_clauses.append("EqiFim.Proveedor = :provider")
            params['provider'] = filters.provider
            
        # Buyer filter
        if filters.buyer:
            where_clauses.append("EqiFim.VendidoA = :buyer")
            params['buyer'] = filters.buyer

        # Pedimento code filter
        if filters.pedimento_code:
            where_clauses.append("EqiPed.ClavePed = :pedimento_code")
            params['pedimento_code'] = filters.pedimento_code

        return " AND ".join(where_clauses), params
    
    def _build_where_clause_definitive(self, filters: ImportDefinitiveFilter) -> str:
        """
        Build WHERE clause for definitive imports query.
        
        Returns:
            WHERE clause string
        """
        where_conditions = []
        
        # Date range filter
        if filters.range_type.value == "FF":
            where_conditions.append(f"EqiFid.FechaFactura >= '{filters.start_date}' AND EqiFid.FechaFactura <= '{filters.end_date}'")
        else:
            where_conditions.append(f"EqiPed.Fecha_Pago >= '{filters.start_date}' AND EqiPed.Fecha_Pago <= '{filters.end_date}'")
        
        # Status filter
        if not filters.include_cancelled:
            where_conditions.append("EqiFid.Estatus = 'AC'")
        
        # Provider filter
        if filters.provider:
            where_conditions.append(f"EqiFid.Proveedor = '{filters.provider}'")
        
        # Buyer filter
        if filters.buyer:
            where_conditions.append(f"EqiFid.VendidoA = '{filters.buyer}'")
        
        # Pedimento code filter
        if filters.pedimento_code:
            where_conditions.append(f"EqiPed.ClavePed = '{filters.pedimento_code}'")
        
        # Movement type filter
        if filters.movement_type.value == "COMEX":
            where_conditions.append("EqiFid.ProvImpoDefCR = 'P'")
        elif filters.movement_type.value == "IMPDF":
            where_conditions.append("EqiFid.ProvImpoDefCR != 'P'")
        
        return " AND ".join(where_conditions)
    
    def _calculate_exchange_rate_and_value(
        self,
        db: Session,
        db_name: str,
        valor_me: float,
        valor_mn: float,
        tipo_cambio_db: float,
        fecha_pago,
        fecha_inicio,
        tipo_pedimento: str,
        currency_type: str,
        exchange_rate_type: str,
        is_shelter: bool,
        use_transport_method: bool,
        met_trans: int
    ) -> tuple:
        """
        Unified method to calculate exchange rate and commercial value.
        Eliminates duplicated logic across temporary and definitive imports.
        
        Args:
            db: Database session
            db_name: Database name
            valor_me: Value in foreign currency
            valor_mn: Value in local currency
            tipo_cambio_db: Exchange rate from database
            fecha_pago: Payment date
            fecha_inicio: Start date
            tipo_pedimento: Pedimento type
            currency_type: "ME" or "MN"
            exchange_rate_type: "FP" or "FF"
            is_shelter: Shelter company flag
            use_transport_method: Use transport method flag
            met_trans: MetTrans value from config
            
        Returns:
            Tuple of (valor_comercial_mn, tipo_cambio)
        """
        # Foreign currency case - simpler
        if currency_type == "ME":
            valor_comercial = valor_me
            tipo_cambio = tipo_cambio_db
            
            # Try to get exchange rate if using payment date
            if exchange_rate_type == "FP" and fecha_pago:
                fecha_tc = self._get_fecha_tipo_cambio(
                    fecha_pago, fecha_inicio, tipo_pedimento, use_transport_method, met_trans
                )
                tc_value = self._obtener_tipo_cambio(db, db_name, fecha_tc, is_shelter)
                if tc_value:
                    tipo_cambio = tc_value
            
            return valor_comercial, tipo_cambio
        
        # Local currency case - more complex
        if exchange_rate_type == "FP" and fecha_pago:
            fecha_tc = self._get_fecha_tipo_cambio(
                fecha_pago, fecha_inicio, tipo_pedimento, use_transport_method, met_trans
            )
            tc_value = self._obtener_tipo_cambio(db, db_name, fecha_tc, is_shelter)
            
            if tc_value:
                return valor_me * tc_value, tc_value
            else:
                logger.warning(f"Exchange rate not found for date {fecha_tc}, using DB values")
                return valor_mn, tipo_cambio_db
        else:
            return valor_mn, tipo_cambio_db
    
    def _get_fecha_tipo_cambio(
        self,
        fecha_pago,
        fecha_inicio,
        tipo_pedimento: str,
        use_transport_method: bool,
        met_trans: int
    ):
        """
        Determine which date to use for exchange rate lookup.
        
        Returns:
            Date to use for exchange rate
        """
        fecha = fecha_pago
        if use_transport_method and met_trans == 1:
            if tipo_pedimento in ('1', '4', '98E'):
                fecha = fecha_inicio
        return fecha
    
    def get_temporary_import_movements(
        self, 
        db: Session, 
        filters: ImportTemporaryFilter
    ) -> List[MovementItem]:
        """
        Retrieve temporary import movements from legacy database.
        
        Args:
            db: Database session
            filters: Filter criteria for the query
            
        Returns:
            List of movement items matching the criteria
            
        Raises:
            Exception: If database query fails
        """
        logger.info(f"Fetching temporary import movements with filters: {filters.model_dump()}")
        
        # Read INI configuration for exchange rate logic
        met_trans = 0
        try:
            config = configparser.ConfigParser()
            config.read('Scaii.ini') 
            met_trans = config.getint('METTRANS', 'TipoCambio', fallback=0)
            logger.debug(f"INI met_trans value: {met_trans}")
        except Exception as e:
            logger.warning(f"Could not read Scaii.ini, using default met_trans=0: {e}")

        # Build WHERE clause dynamically based on filters
        where_clauses = []
        params = {}
        
        # Date range filter
        if filters.range_type == 'FF':
            where_clauses.append(
                "EqiFim.FechaFactura >= :start_date AND EqiFim.FechaFactura <= :end_date"
            )
        else:
            where_clauses.append(
                "EqiPed.Fecha_Pago >= :start_date AND EqiPed.Fecha_Pago <= :end_date"
            )
        
        params['start_date'] = filters.start_date
        params['end_date'] = filters.end_date

        # Status filter
        if not filters.include_cancelled:
            where_clauses.append("EqiFim.Estatus = 'AC'")
        
        # Provider filter
        if filters.provider:
            where_clauses.append("EqiFim.Proveedor = :provider")
            params['provider'] = filters.provider
            
        # Buyer filter
        if filters.buyer:
            where_clauses.append("EqiFim.VendidoA = :buyer")
            params['buyer'] = filters.buyer

        # Pedimento code filter
        if filters.pedimento_code:
            where_clauses.append("EqiPed.ClavePed = :pedimento_code")
            params['pedimento_code'] = filters.pedimento_code

        where_str = " AND ".join(where_clauses)
        db_name = filters.database_name
        
        logger.debug(f"WHERE clause: {where_str}")
        logger.debug(f"Query params: {params}")
        
        # Main Query (using shared query builder)
        sql_query = text(self._build_main_query(db_name, where_str))

        try:
            result = db.execute(sql_query, params)
            rows = result.fetchall()
            logger.info(f"Query returned {len(rows)} rows")
        except Exception as e:
            logger.error(f"Error executing main query: {e}")
            raise Exception(f"Database query failed: {str(e)}")
        
        movements = []
        processed_facturas = set()

        for row in rows:
            if filters.report_type == 'Normal':
                C1_Factura = row[0]
                
                if C1_Factura in processed_facturas:
                    continue
                
                qcsv_ie = {}
                processed_facturas.add(C1_Factura)
                
                C39_Consecutivo = row[38]

                # Totalize Items (sum values for main partidas only)
                sql_total = text(f"""
                    SELECT 
                        COALESCE(SUM(EqiPim.ValorImpoME), 0), 
                        COALESCE(SUM(EqiPim.ValorImpoMN), 0) 
                    FROM [{db_name}].dbo.QEqiMaq EqiPim
                    WHERE EqiPim.Consecutivo = :consecutivo
                        AND EqiPim.EsSubpartida = 'P'
                """)
                
                try:
                    res_total = db.execute(sql_total, {"consecutivo": C39_Consecutivo}).fetchone()
                    val_total_me = float(res_total[0]) if res_total and res_total[0] is not None else 0.0
                    val_total_mn = float(res_total[1]) if res_total and res_total[1] is not None else 0.0
                except Exception as e:
                    logger.warning(f"Error calculating totals for consecutivo {C39_Consecutivo}: {e}")
                    val_total_me = 0.0
                    val_total_mn = 0.0

                qcsv_ie['Factura'] = row[0]
                qcsv_ie['Pedimento'] = row[1]
                qcsv_ie['FechaFactura'] = row[2]
                qcsv_ie['Estatus'] = row[3]
                qcsv_ie['ClavePed'] = row[4]
                qcsv_ie['TipoMovTemDef'] = 'IMTEM'
                qcsv_ie['EsCambioRegimen'] = 'N'
                
                row_c13 = row[12] # Fecha_Pago
                row_c11 = row[10] # Fecha_Inicio
                row_c58 = row[57] # TIPOPEDIMENTOTRANSPORTEE
                row_c50 = row[49] # TipoCambio
                row_c41 = row[40] # PedRectifica
                row_c2 = row[1]   # PedimentoImpo

                calculated_tc = row_c50
                
                # Calculate values based on currency type
                if filters.currency_type == 'ME':
                    qcsv_ie['ValorMPTemp'] = val_total_me
                    qcsv_ie['ValorComercialMN'] = val_total_me
                    
                    if filters.exchange_rate_type == 'FP' and row_c13:
                        calculated_tc = self._obtener_tipo_cambio(
                            db, db_name, row_c13, row_c11, row_c58, met_trans
                        )
                        qcsv_ie['TipoCambio'] = calculated_tc if calculated_tc > 0 else row_c50
                    else:
                        qcsv_ie['TipoCambio'] = row_c50
                else:
                    if filters.is_shelter:
                        if filters.exchange_rate_type == 'FP' and row_c13:
                            calculated_tc = self._obtener_tipo_cambio_mn(db, db_name, row_c13, row_c11, row_c58, met_trans)
                            qcsv_ie['ValorMPTemp'] = val_total_me * calculated_tc
                            qcsv_ie['ValorComercialMN'] = val_total_me * calculated_tc
                            qcsv_ie['TipoCambio'] = calculated_tc
                        else:
                            qcsv_ie['ValorMPTemp'] = val_total_mn
                            qcsv_ie['ValorComercialMN'] = val_total_mn
                            qcsv_ie['TipoCambio'] = row_c50
                    else:
                        if filters.exchange_rate_type == 'FP' and row_c13:
                            calculated_tc = self._obtener_tipo_cambio(db, db_name, row_c13, row_c11, row_c58, met_trans)
                            qcsv_ie['ValorMPTemp'] = val_total_me * calculated_tc
                            qcsv_ie['ValorComercialMN'] = val_total_me * calculated_tc
                            qcsv_ie['TipoCambio'] = calculated_tc
                        else:
                            qcsv_ie['ValorMPTemp'] = val_total_mn
                            qcsv_ie['ValorComercialMN'] = val_total_mn
                            qcsv_ie['TipoCambio'] = row_c50
                            
                qcsv_ie['ValorAgre'] = 0.0
                qcsv_ie['TipoExpo'] = ''

                if filters.is_shelter:
                    qcsv_ie['PedimentoR1'] = row_c41
                else:
                    qcsv_ie['PedimentoR1'] = self._buscar_rectificacion(db, db_name, row_c2, row_c41)
                
                qcsv_ie['EDocument'] = row[41]
                qcsv_ie['NumOperacionVU'] = row[42]
                qcsv_ie['BaseDeDatos'] = db_name
                
                # Get driver badge number (gafete)
                sql_gafete = text(f"""
                    SELECT TOP 1 NUMGAFETEUNICO 
                    FROM [{db_name}].dbo.GConductor 
                    LEFT JOIN [{db_name}].dbo.QFacImp 
                        ON QFacImp.CONDUCTOR = GConductor.CONDUCTOR 
                    WHERE FacturaImpo = :factura
                """)
                try:
                    res_gafete = db.execute(sql_gafete, {"factura": row[0]}).fetchone()
                    qcsv_ie['NumGafUni'] = res_gafete[0] if res_gafete and res_gafete[0] else None
                except Exception as e:
                    logger.debug(f"Could not retrieve badge for invoice {row[0]}: {e}")
                    qcsv_ie['NumGafUni'] = None
                
                qcsv_ie['UsuarioCap'] = row[51]
                qcsv_ie['UsuarioAcr'] = row[52]
                qcsv_ie['Fecha_Pago'] = row[12]
                qcsv_ie['NumCaja'] = row[54]
                qcsv_ie['Pedimento18'] = row[55]
                qcsv_ie['AduanaCru'] = row[37]
                qcsv_ie['Lote'] = row[56]

                movements.append(MovementItem(**qcsv_ie))

        return movements
    
    def _obtener_tipo_cambio(
        self, 
        db: Session, 
        db_name: str, 
        fecha,
        is_shelter: bool
    ) -> Optional[float]:
        """
        Get exchange rate for the given date.
        Simplified version that works with both shelter and non-shelter logic.
        
        Args:
            db: Database session
            db_name: Legacy database name
            fecha: Date for exchange rate lookup
            is_shelter: Shelter company flag (currently not used but kept for compatibility)
            
        Returns:
            Exchange rate as float, or None if not found
        """
        if not fecha:
            return None
        
        try:
            sql_tc = text(f"""
                SELECT TOP 1 Valor 
                FROM [{db_name}].dbo.GTipoCambio 
                WHERE Fecha = :fecha 
                ORDER BY Fecha DESC
            """)
            res = db.execute(sql_tc, {"fecha": fecha}).fetchone()
            if res and res[0]:
                return float(res[0])
            else:
                logger.warning(f"Exchange rate not found for date {fecha}")
                return None
        except Exception as e:
            logger.error(f"Error fetching exchange rate for date {fecha}: {e}")
            return None

    def _buscar_rectificacion(
        self, 
        pedimento: str, 
        ped_rectifica: Optional[str]
    ) -> Optional[str]:
        """
        Search for pedimento rectification.
        Simplified version - returns the rectification value from database.
        
        Args:
            pedimento: Original pedimento number
            ped_rectifica: Rectification pedimento from query
            
        Returns:
            Rectification pedimento number or None
        """
        # TODO: Implement full rectification search logic if needed
        # For now, returning the value from the query
        return ped_rectifica

    def get_temporary_import_movements_detailed(
        self, 
        db: Session, 
        filters: ImportTemporaryFilter
    ) -> List[MovementItemDetailed]:
        """
        Retrieve detailed temporary import movements (line by line) from legacy database.
        
        Args:
            db: Database session
            filters: Filter criteria for the query
            
        Returns:
            List of detailed movement items (one per line/partida)
        """
        logger.info(f"Fetching DETAILED temporary import movements with filters: {filters.model_dump()}")
        
        # Read INI configuration
        met_trans = 0
        try:
            config = configparser.ConfigParser()
            config.read('Scaii.ini') 
            met_trans = config.getint('METTRANS', 'TipoCambio', fallback=0)
            logger.debug(f"INI met_trans value: {met_trans}")
        except Exception as e:
            logger.warning(f"Could not read Scaii.ini, using default met_trans=0: {e}")

        # Build WHERE clause (same as normal report)
        where_clauses = []
        params = {}
        
        if filters.range_type == 'FF':
            where_clauses.append(
                "EqiFim.FechaFactura >= :start_date AND EqiFim.FechaFactura <= :end_date"
            )
        else:
            where_clauses.append(
                "EqiPed.Fecha_Pago >= :start_date AND EqiPed.Fecha_Pago <= :end_date"
            )
        
        params['start_date'] = filters.start_date
        params['end_date'] = filters.end_date

        if not filters.include_cancelled:
            where_clauses.append("EqiFim.Estatus = 'AC'")
        
        if filters.provider:
            where_clauses.append("EqiFim.Proveedor = :provider")
            params['provider'] = filters.provider
            
        if filters.buyer:
            where_clauses.append("EqiFim.VendidoA = :buyer")
            params['buyer'] = filters.buyer

        if filters.pedimento_code:
            where_clauses.append("EqiPed.ClavePed = :pedimento_code")
            params['pedimento_code'] = filters.pedimento_code

        where_str = " AND ".join(where_clauses)
        db_name = filters.database_name
        
        logger.debug(f"WHERE clause: {where_str}")
        logger.debug(f"Query params: {params}")
        
        # Same main query as normal report
        sql_query = text(self._build_main_query(db_name, where_str))

        try:
            result = db.execute(sql_query, params)
            rows = result.fetchall()
            logger.info(f"Query returned {len(rows)} rows for detailed processing")
        except Exception as e:
            logger.error(f"Error executing main query: {e}")
            raise Exception(f"Database query failed: {str(e)}")
        
        movements = []

        # Process each row individually (detailed mode)
        for row in rows:
            item = {}
            
            # Basic invoice info
            item['Linea'] = row[43]  # C44 - LineaImpo
            item['Factura'] = row[0]  # C1
            item['Pedimento'] = row[1]  # C2
            item['FechaFactura'] = row[2]  # C3
            item['Estatus'] = row[3]  # C4
            item['ClavePed'] = row[4]  # C5
            item['TipoMovTemDef'] = 'IMTEM'
            item['EsCambioRegimen'] = 'N'
            item['Regimen'] = row[9]  # C10
            item['Fecha_Inicio'] = row[10]  # C11
            item['Fecha_Fin'] = row[11]  # C12
            item['Fecha_Pago'] = row[12]  # C13
            item['Remesa'] = row[13]  # C14
            
            # Get provider information
            provider_code = row[15]  # C16 - Proveedor
            provider_info = self._get_client_provider_info(db, db_name, provider_code, filters.is_shelter)
            item['Proveedor'] = provider_info.get('nombre')
            item['RFCProveedor'] = provider_info.get('rfc')
            item['ProveedorTaxID'] = provider_info.get('tax_id')
            
            # Get buyer information
            buyer_code = row[16]  # C17 - VendidoA
            buyer_info = self._get_client_buyer_info(db, db_name, buyer_code, filters.is_shelter)
            item['VendidoA'] = buyer_info.get('nombre')
            item['VendidoARFC'] = buyer_info.get('rfc')
            item['VendidoATaxID'] = buyer_info.get('tax_id')
            
            # Get customs broker info
            customs_broker_code = row[17]  # C18 - AAduanal
            broker_info = self._get_customs_broker_info(db, db_name, customs_broker_code)
            item['AgenteAduanal'] = broker_info.get('nombre')
            item['Patente'] = broker_info.get('patente')
            
            # Item details
            item['NumParte'] = row[19]  # C20 - Clase (NumParte)
            item['DescripcionE'] = self._clean_text(row[20])  # C21 - Already cleaned in query
            item['DescripcionI'] = self._clean_text(row[21])  # C22 - Already cleaned in query
            item['CantidadIE'] = float(row[22]) if row[22] else 0.0  # C23
            item['UniMed'] = row[23]  # C24
            
            # Values and exchange rate (only for main partidas, not subpartidas)
            if row[39] == 'P':  # C40 - EsSubPartida == 'P'
                # Calculate value based on currency type
                if filters.currency_type == 'MN':
                    if filters.is_shelter:
                        if filters.exchange_rate_type == 'FP' and row[12]:  # C13 - Fecha_Pago
                            tc = self._obtener_tipo_cambio(db, db_name, row[12], row[10], row[57], met_trans)
                            item['ValorComercialMN'] = float(row[26]) * tc if row[26] else 0.0  # C27 * TC
                            item['TipoCambio'] = tc
                        else:
                            item['ValorComercialMN'] = float(row[24]) if row[24] else 0.0  # C25
                            item['TipoCambio'] = float(row[49]) if row[49] else 0.0  # C50
                    else:
                        if filters.exchange_rate_type == 'FP' and row[12]:
                            tc = self._obtener_tipo_cambio(db, db_name, row[12], row[10], row[57], met_trans)
                            item['ValorComercialMN'] = float(row[26]) * tc if row[26] else 0.0  # C27 * TC
                            item['TipoCambio'] = tc
                        else:
                            item['ValorComercialMN'] = float(row[24]) if row[24] else 0.0  # C25
                            item['TipoCambio'] = float(row[49]) if row[49] else 0.0  # C50
                elif filters.currency_type == 'ME':
                    item['ValorComercialMN'] = float(row[26]) if row[26] else 0.0  # C27
                    if filters.exchange_rate_type == 'FP' and row[12]:
                        tc = self._obtener_tipo_cambio(db, db_name, row[12], row[10], row[57], met_trans)
                        item['TipoCambio'] = tc if tc > 0 else float(row[49]) if row[49] else 0.0
                    else:
                        item['TipoCambio'] = float(row[49]) if row[49] else 0.0  # C50
                
                item['PesoNeto'] = float(row[28]) if row[28] else 0.0  # C29
                item['PesoBruto'] = float(row[29]) if row[29] else 0.0  # C30
            elif row[39] == 'S':  # Subpartida
                item['ValorComercialMN'] = 0.0
                item['PesoNeto'] = 0.0
                item['PesoBruto'] = 0.0
                item['TipoCambio'] = 0.0
            
            # Additional fields
            item['OrdenCompraVenta'] = row[30]  # C31
            item['FraccionArancelaria'] = row[31]  # C32
            item['Preferencia'] = row[32]  # C33
            item['Sector'] = row[34]  # C35
            item['PaisOrigen'] = row[36]  # C37
            
            # Get customs office name
            aduana_code = row[37]  # C38 - Aduana_Cruce
            aduana_name = self._get_customs_office_name(db, db_name, aduana_code)
            item['Aduana'] = aduana_name
            
            item['Advalorem'] = row[39]  # C40
            item['TipoExpo'] = ''
            
            # Rectification
            if filters.is_shelter:
                item['PedimentoR1'] = row[40]  # C41
            else:
                item['PedimentoR1'] = self._buscar_rectificacion(db, db_name, row[1], row[40])
            
            item['EDocument'] = row[41]  # C42
            item['NumOperacionVU'] = row[42]  # C43
            
            # Get series information
            consecutivo = row[38]  # C39
            linea_impo = row[43]  # C44
            series_info = self._get_series_info(db, db_name, consecutivo, linea_impo, filters.is_shelter)
            item['Series'] = series_info
            
            item['Marca'] = row[44]  # C45
            item['Modelo'] = row[45]  # C46
            item['FraccionAmericana'] = row[46]  # C47
            item['ECCN'] = row[47]  # C48
            
            # Get export symbol from parts
            num_parte = row[48]  # C49
            if num_parte:
                simbolo_ex = self._get_part_export_symbol(db, db_name, num_parte, filters.is_shelter)
                item['SimboloEx'] = simbolo_ex
            else:
                item['SimboloEx'] = None
            
            item['FechaEmision'] = row[50]  # C51
            item['BaseDeDatos'] = db_name
            
            # Get driver badge
            factura = row[0]  # C1
            try:
                sql_gafete = text(f"""
                    SELECT TOP 1 NUMGAFETEUNICO 
                    FROM [{db_name}].dbo.GConductor 
                    LEFT JOIN [{db_name}].dbo.QFacImp 
                        ON QFacImp.CONDUCTOR = GConductor.CONDUCTOR 
                    WHERE FacturaImpo = :factura
                """)
                res_gafete = db.execute(sql_gafete, {"factura": factura}).fetchone()
                item['NumGafUni'] = res_gafete[0] if res_gafete and res_gafete[0] else None
            except Exception as e:
                logger.debug(f"Could not retrieve badge for invoice {factura}: {e}")
                item['NumGafUni'] = None
            
            item['UsuarioCap'] = row[51]  # C52
            item['UsuarioAcr'] = row[52]  # C53
            item['Transportista'] = row[53]  # C54
            item['NumCaja'] = row[54]  # C55
            item['Pedimento18'] = row[55]  # C56
            item['AduanaCru'] = row[37]  # C38
            item['Lote'] = row[56]  # C57
            
            movements.append(MovementItemDetailed(**item))
        
        logger.info(f"Processed {len(movements)} detailed movement items")
        return movements
    
    def _build_main_query(self, db_name: str, where_str: str) -> str:
        """Build the main SQL query for fetching invoice data"""
        return f"""
        SELECT 
            EqiFim.FacturaImpo AS C1, 
            EqiFim.PedimentoImpo AS C2, 
            EqiFim.FechaFactura AS C3,
            EqiFim.Estatus AS C4, 
            EqiPed.ClavePed AS C5, 
            EqiFim.ValorImpoME AS C6, 
            EqiFim.ValorImpoMN AS C7,
            EqiFim.Proveedor AS C8, 
            EqiFim.VendidoA AS C9, 
            EqiPed.Regimen AS C10, 
            EqiPed.Fecha_Inicio AS C11,
            EqiPed.Fecha_Fin AS C12, 
            EqiPed.Fecha_Pago AS C13, 
            EqiFim.Remesa AS C14, 
            EqiFim.TipoCambio AS C15,
            EqiFim.Proveedor AS C16, 
            EqiFim.VendidoA AS C17, 
            EqiFim.AAduanal AS C18, 
            '' AS C19,
            EqiPim.Clase AS C20,
            REPLACE(REPLACE(REPLACE(REPLACE(EqiPim.DescripcionE, CHAR(44), ' '), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' ') AS C21,
            REPLACE(REPLACE(REPLACE(REPLACE(ClaAct.DescripcionI, CHAR(44), ' '), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' ') AS C22, 
            EqiPim.CantImpo AS C23,
            EqiPim.UnidadMedida AS C24, 
            EqiPim.ValorImpoMN AS C25, 
            EqiPim.ValorAduanasMN AS C26,
            EqiPim.ValorImpoME AS C27, 
            EqiPim.ValorAduanasME AS C28, 
            EqiPim.PesoNeto AS C29, 
            EqiPim.PesoBruto AS C30,
            EqiPim.OrdenCompra AS C31, 
            EqiPim.Fraccion AS C32, 
            EqiPim.TipoFraccion AS C33, 
            EqiPim.AdvImpo AS C34,
            EqiPim.Sector AS C35, 
            EqiPim.MontoIgi AS C36, 
            EqiPim.PaisOrigen AS C37, 
            EqiPed.Aduana_Cruce AS C38,
            EqiFim.Consecutivo AS C39, 
            EqiPim.EsSubPartida AS C40, 
            EqiPed.PedRectifica AS C41, 
            EqiFim.EDocument AS C42,
            EqiFim.NumOperacionVU AS C43, 
            EqiPim.LineaImpo AS C44,
            REPLACE(REPLACE(REPLACE(REPLACE(EqiPim.Marca, CHAR(44), ' '), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' ') AS C45,
            REPLACE(REPLACE(REPLACE(REPLACE(EqiPim.Modelo, CHAR(44), ' '), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' ') AS C46, 
            ClaAct.FraccionAme AS C47,
            ClaAct.ECCN AS C48, 
            EqiPim.NumParte AS C49, 
            EqiFim.TipoCambio AS C50, 
            EqiFim.FechaEmision AS C51, 
            EqiFim.UsuarioCap AS C52, 
            EqiFim.UsuarioAct AS C53,
            EqiFim.Transportista AS C54, 
            EqiFim.Transporte + ' ' + EqiFim.NumTrasporte AS C55, 
            EqiPed.Pedimento18 AS C56, 
            EqiPim.LOTE AS C57, 
            EqiPed.TIPOPEDIMENTOTRANSPORTEE AS C58
        FROM [{db_name}].dbo.QFacImp EqiFim
        LEFT JOIN [{db_name}].dbo.QPedimentos EqiPed 
            ON EqiPed.Pedimento = EqiFim.PedimentoImpo
        LEFT JOIN [{db_name}].dbo.QEqiMaq EqiPim 
            ON EqiPim.Consecutivo = EqiFim.Consecutivo
        LEFT JOIN [{db_name}].dbo.QClaAct ClaAct 
            ON ClaAct.Clase = EqiPim.Clase
        WHERE {where_str}
        """
    
    def _clean_text(self, text: Optional[str]) -> Optional[str]:
        """Clean text by removing special characters"""
        if not text:
            return None
        return text.strip()
    
    def _get_client_provider_info(self, db: Session, db_name: str, client_code: str, is_shelter: bool) -> dict:
        """Get provider/client information"""
        if not client_code:
            return {"nombre": None, "rfc": None, "tax_id": None}
        
        try:
            sql = text(f"""
                SELECT TOP 1 Nombre, RFC, TaxID 
                FROM [{db_name}].dbo.GClientesPro 
                WHERE Cliente = :cliente
            """)
            result = db.execute(sql, {"cliente": client_code}).fetchone()
            
            if result:
                return {
                    "nombre": self._clean_text(result[0]),
                    "rfc": result[1],
                    "tax_id": result[2]
                }
        except Exception as e:
            logger.warning(f"Error fetching provider info for {client_code}: {e}")
        
        return {"nombre": None, "rfc": None, "tax_id": None}
    
    def _get_client_buyer_info(self, db: Session, db_name: str, client_code: str, is_shelter: bool) -> dict:
        """Get buyer information (same structure as provider)"""
        return self._get_client_provider_info(db, db_name, client_code, is_shelter)
    
    def _get_customs_broker_info(self, db: Session, db_name: str, broker_code: str) -> dict:
        """Get customs broker information"""
        if not broker_code:
            return {"nombre": None, "patente": None}
        
        try:
            sql = text(f"""
                SELECT TOP 1 Nombre, Patente 
                FROM [{db_name}].dbo.GAAduanal 
                WHERE ClaveAA = :clave
            """)
            result = db.execute(sql, {"clave": broker_code}).fetchone()
            
            if result:
                return {
                    "nombre": result[0],
                    "patente": result[1]
                }
        except Exception as e:
            logger.warning(f"Error fetching customs broker info for {broker_code}: {e}")
        
        return {"nombre": None, "patente": None}
    
    def _get_customs_office_name(self, db: Session, db_name: str, aduana_code: str) -> Optional[str]:
        """Get customs office name"""
        if not aduana_code:
            return None
        
        try:
            sql = text(f"""
                SELECT TOP 1 REPLACE(REPLACE(REPLACE(REPLACE(Nombre, CHAR(44), ' '), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' ') 
                FROM [{db_name}].dbo.GAduanaSec 
                WHERE AduanaSeccion = :aduana
            """)
            result = db.execute(sql, {"aduana": aduana_code}).fetchone()
            return result[0] if result else None
        except Exception as e:
            logger.warning(f"Error fetching customs office name for {aduana_code}: {e}")
            return None
    
    def _get_series_info(self, db: Session, db_name: str, consecutivo: str, linea_impo: str, is_shelter: bool) -> Optional[str]:
        """Get series information for an item"""
        if not consecutivo or not linea_impo:
            return None
        
        try:
            sql = text(f"""
                SELECT SerieImpo, ModeloImpo, ParteImpo 
                FROM [{db_name}].dbo.QSeriesImpo 
                WHERE Consecutivo = :consecutivo 
                    AND LineaImpo = :linea
                ORDER BY RenImpo
            """)
            result = db.execute(sql, {"consecutivo": consecutivo, "linea": linea_impo}).fetchall()
            
            if not result:
                return None
            
            # Build series string
            series_parts = []
            for idx, row in enumerate(result, 1):
                serie = row[0]
                modelo = row[1]
                parte = row[2]
                
                serie_str = f"{idx}) {serie}"
                if modelo:
                    serie_str += f". Modelo: {modelo}"
                if parte:
                    serie_str += f". Parte: {parte}"
                
                series_parts.append(serie_str)
            
            return " | ".join(series_parts) if series_parts else None
            
        except Exception as e:
            logger.warning(f"Error fetching series for {consecutivo}/{linea_impo}: {e}")
            return None
    
    def _get_part_export_symbol(self, db: Session, db_name: str, num_parte: str, is_shelter: bool) -> Optional[str]:
        """Get export symbol/license for a part number"""
        if not num_parte:
            return None
        
        try:
            sql = text(f"""
                SELECT TOP 1 SimboloExcLic 
                FROM [{db_name}].dbo.QPartes 
                WHERE NumParte = :num_parte
            """)
            result = db.execute(sql, {"num_parte": num_parte}).fetchone()
            return result[0] if result else None
        except Exception as e:
            logger.debug(f"Error fetching export symbol for part {num_parte}: {e}")
            return None

    def get_definitive_import_movements(
        self, 
        db: Session, 
        filters: ImportDefinitiveFilter
    ) -> List[MovementItem]:
        """
        Retrieve definitive import movements from legacy database (LLENADODEFINITIVO - NORMAL).
        Aggregates movements by invoice number.
        
        Args:
            db: Database session
            filters: Filter criteria for the query
            
        Returns:
            List of movement items matching the criteria
            
        Raises:
            Exception: If database query fails
        """
        logger.info(f"Fetching definitive import movements with filters: {filters.model_dump()}")
        
        # Read INI configuration for exchange rate logic
        met_trans = 0
        try:
            config = configparser.ConfigParser()
            config.read('Scaii.ini') 
            met_trans = config.getint('METTRANS', 'TipoCambio', fallback=0)
            logger.debug(f"INI met_trans value: {met_trans}")
        except Exception as e:
            logger.warning(f"Could not read Scaii.ini, using default met_trans=0: {e}")

        # Build WHERE clause
        where_conditions = []
        
        # Date range filter
        if filters.range_type.value == "FF":
            where_conditions.append(f"EqiFid.FechaFactura >= '{filters.start_date}' AND EqiFid.FechaFactura <= '{filters.end_date}'")
        else:  # FP
            where_conditions.append(f"EqiPed.Fecha_Pago >= '{filters.start_date}' AND EqiPed.Fecha_Pago <= '{filters.end_date}'")
        
        # Status filter
        if not filters.include_cancelled:
            where_conditions.append("EqiFid.Estatus = 'AC'")
        
        # Provider filter
        if filters.provider:
            where_conditions.append(f"EqiFid.Proveedor = '{filters.provider}'")
        
        # Buyer filter
        if filters.buyer:
            where_conditions.append(f"EqiFid.VendidoA = '{filters.buyer}'")
        
        # Pedimento code filter
        if filters.pedimento_code:
            where_conditions.append(f"EqiPed.ClavePed = '{filters.pedimento_code}'")
        
        # Movement type filter
        if filters.movement_type.value == "COMEX":
            where_conditions.append("EqiFid.ProvImpoDefCR = 'P'")
        elif filters.movement_type.value == "IMPDF":
            where_conditions.append("EqiFid.ProvImpoDefCR != 'P'")
        # If ALL, no filter added
        
        where_clause = " AND ".join(where_conditions)
        
        # Build main SQL query
        sql_query = text(f"""
            SELECT 
                EqiFid.FacturaImpoDef AS C1,
                EqiFid.PedimentoImpoDef AS C2,
                EqiFid.FechaFactura AS C3,
                EqiFid.Estatus AS C4,
                EqiPed.ClavePed AS C5,
                EqiFid.ValorImpoME AS C6,
                EqiFid.ValorImpoMN AS C7,
                EqiFid.Proveedor AS C8,
                EqiFid.VendidoA AS C9,
                EqiPed.Regimen AS C10,
                EqiPed.Fecha_Inicio AS C11,
                EqiPed.Fecha_Fin AS C12,
                EqiPed.Fecha_Pago AS C13,
                EqiFid.Remesa AS C14,
                EqiFid.TipoCambio AS C15,
                EqiFid.AAduanal AS C18,
                EqiFid.Consecutivo AS C40,
                EqiPed.PedRectifica AS C42,
                EqiFid.EDocument AS C43,
                EqiFid.NumOperacionVU AS C44,
                EqiFid.TipoCambio AS C51,
                EqiFid.FechaEmision AS C52,
                EqiFid.UsuarioCap AS C53,
                EqiFid.UsuarioAct AS C54,
                EqiFid.Transportista AS C55,
                EqiFid.Transporte + ' ' + EqiFid.NumTrasporte AS C56,
                EqiPed.Pedimento18 AS C57,
                EqiPed.Aduana_Cruce AS C38,
                EqiFid.ProvImpoDefCR AS C39,
                EqiPed.TIPOPEDIMENTOTRANSPORTEE AS C59
            FROM [{filters.database_name}].dbo.QFacImpDef EqiFid
            LEFT JOIN [{filters.database_name}].dbo.QPedimentos EqiPed 
                ON EqiPed.Pedimento = EqiFid.PedimentoImpoDef
            WHERE {where_clause}
        """)
        
        try:
            result = db.execute(sql_query)
            movements_dict = {}
            
            for row in result:
                factura = row[0]  # C1
                prov_impo_def_cr = row[28]  # C39
                
                # Determine movement type
                tipo_mov = "COMEX" if prov_impo_def_cr == 'P' else "IMPDF"
                
                # Use factura + tipo_mov as key
                key = (factura, tipo_mov)
                
                # If already exists, skip (we only want one entry per invoice in Normal mode)
                if key not in movements_dict:
                    consecutivo = row[16]  # C40
                    fecha_pago = row[12]  # C13
                    tipo_pedimento = row[29]  # C59
                    fecha_inicio = row[10]  # C11
                    
                    # Calculate total values for this invoice
                    valor_me, valor_mn = self._calculate_definitive_totals(
                        db, filters.database_name, consecutivo
                    )
                    
                    # Calculate exchange rate and values
                    tipo_cambio = row[20]  # C51
                    valor_mp_temp = 0.0
                    valor_comercial_mn = 0.0
                    
                    if filters.currency_type.value == "ME":
                        valor_mp_temp = float(valor_me or 0)
                        valor_comercial_mn = float(valor_me or 0)
                        tipo_cambio = float(tipo_cambio or 1.0)
                    else:  # MN
                        if filters.is_shelter:
                            # Shelter logic with exchange rate calculation
                            if filters.exchange_rate_type.value == "FP" and fecha_pago:
                                # Check if special transport method logic applies
                                fecha_tc = fecha_pago
                                if filters.use_transport_method and met_trans == 1:
                                    if tipo_pedimento in ('1', '4', '98E'):
                                        fecha_tc = fecha_inicio
                                
                                tc_value = self._obtener_tipo_cambio(
                                    db, filters.database_name, fecha_tc, filters.is_shelter
                                )
                                if tc_value:
                                    valor_mp_temp = float(valor_me or 0) * tc_value
                                    valor_comercial_mn = float(valor_me or 0) * tc_value
                                    tipo_cambio = tc_value
                                else:
                                    logger.warning(f"Exchange rate not found for date {fecha_tc}, using values from DB")
                                    valor_mp_temp = float(valor_mn or 0)
                                    valor_comercial_mn = float(valor_mn or 0)
                            else:
                                valor_mp_temp = float(valor_mn or 0)
                                valor_comercial_mn = float(valor_mn or 0)
                        else:
                            # Non-shelter logic
                            if filters.exchange_rate_type.value == "FP" and fecha_pago:
                                fecha_tc = fecha_pago
                                if filters.use_transport_method and met_trans == 1:
                                    if tipo_pedimento in ('1', '4', '98E'):
                                        fecha_tc = fecha_inicio
                                
                                tc_value = self._obtener_tipo_cambio(
                                    db, filters.database_name, fecha_tc, filters.is_shelter
                                )
                                if tc_value:
                                    valor_mp_temp = float(valor_me or 0) * tc_value
                                    valor_comercial_mn = float(valor_me or 0) * tc_value
                                    tipo_cambio = tc_value
                                else:
                                    valor_mp_temp = float(valor_mn or 0)
                                    valor_comercial_mn = float(valor_mn or 0)
                            else:
                                valor_mp_temp = float(valor_mn or 0)
                                valor_comercial_mn = float(valor_mn or 0)
                    
                    # Get driver badge number
                    num_gaf_uni = self._get_driver_badge(db, filters.database_name, factura)
                    
                    # Get rectification pedimento
                    pedimento = row[1]  # C2
                    ped_rectifica = row[17]  # C42
                    pedimento_r1 = self._buscar_rectificacion(pedimento, ped_rectifica)
                    
                    # Create movement item
                    movement = MovementItem(
                        Factura=factura,
                        Pedimento=row[1],  # C2
                        FechaFactura=row[2],  # C3
                        Estatus=row[3],  # C4
                        ClavePed=row[4],  # C5
                        TipoMovTemDef=tipo_mov,
                        EsCambioRegimen='N',
                        ValorMPTemp=valor_mp_temp,
                        ValorComercialMN=valor_comercial_mn,
                        TipoCambio=tipo_cambio,
                        ValorAgre=0.0,
                        TipoExpo='',
                        PedimentoR1=pedimento_r1,
                        EDocument=row[18],  # C43
                        NumOperacionVU=row[19],  # C44
                        BaseDeDatos=filters.database_name,
                        NumGafUni=num_gaf_uni,
                        UsuarioCap=row[22],  # C53
                        UsuarioAcr=row[23],  # C54
                        Fecha_Pago=row[12],  # C13
                        NumCaja=row[25],  # C56
                        Pedimento18=row[26],  # C57
                        AduanaCru=row[27],  # C38
                        Lote=None  # Lote comes from EpiDef table, not available in main query
                    )
                    
                    movements_dict[key] = movement
            
            movements = list(movements_dict.values())
            logger.info(f"Successfully retrieved {len(movements)} definitive import movements")
            return movements
            
        except Exception as e:
            logger.error(f"Error fetching definitive import movements: {e}", exc_info=True)
            raise

    def _calculate_definitive_totals(self, db: Session, db_name: str, consecutivo: int) -> tuple:
        """
        Calculate total values for a definitive import invoice.
        Sums up all partidas (items) excluding sub-partidas.
        
        Returns:
            tuple: (total_valor_me, total_valor_mn)
        """
        try:
            sql = text(f"""
                SELECT SUM(EqiPdf.ValorME), SUM(EqiPdf.ValorMN)
                FROM [{db_name}].dbo.QEqiDef EqiPdf
                WHERE EqiPdf.Consecutivo = :consecutivo
                    AND EqiPdf.EsSubpartida = 'P'
            """)
            result = db.execute(sql, {"consecutivo": consecutivo}).fetchone()
            
            if result:
                return (result[0] or 0, result[1] or 0)
            return (0, 0)
        except Exception as e:
            logger.error(f"Error calculating totals for consecutivo {consecutivo}: {e}")
            return (0, 0)

    def _get_driver_badge(self, db: Session, db_name: str, factura: str) -> Optional[str]:
        """Get driver's unique badge number (NUMGAFETEUNICO) for a definitive import invoice"""
        if not factura:
            return None
        
        try:
            sql = text(f"""
                SELECT NUMGAFETEUNICO 
                FROM [{db_name}].dbo.GConductor 
                LEFT JOIN [{db_name}].dbo.QFacImpDef 
                    ON QFacImpDef.CONDUCTOR = GConductor.CONDUCTOR 
                WHERE FacturaImpoDef = :factura
            """)
            result = db.execute(sql, {"factura": factura}).fetchone()
            return result[0] if result else None
        except Exception as e:
            logger.debug(f"Error fetching driver badge for invoice {factura}: {e}")
            return None

    def get_definitive_import_movements_detailed(
        self, 
        db: Session, 
        filters: ImportDefinitiveFilter
    ) -> List[MovementItemDetailed]:
        """
        Retrieve detailed definitive import movements from legacy database (LLENADODEFINITIVO - DETALLADO).
        Returns each line/partida as a separate record with full details.
        
        Args:
            db: Database session
            filters: Filter criteria for the query
            
        Returns:
            List of detailed movement items (one per partida/line)
            
        Raises:
            Exception: If database query fails
        """
        logger.info(f"Fetching DETAILED definitive import movements with filters: {filters.model_dump()}")
        
        # Read INI configuration for exchange rate logic
        met_trans = 0
        try:
            config = configparser.ConfigParser()
            config.read('Scaii.ini') 
            met_trans = config.getint('METTRANS', 'TipoCambio', fallback=0)
            logger.debug(f"INI met_trans value: {met_trans}")
        except Exception as e:
            logger.warning(f"Could not read Scaii.ini, using default met_trans=0: {e}")

        # Build WHERE clause (same as normal mode)
        where_conditions = []
        
        if filters.range_type.value == "FF":
            where_conditions.append(f"EqiFid.FechaFactura >= '{filters.start_date}' AND EqiFid.FechaFactura <= '{filters.end_date}'")
        else:
            where_conditions.append(f"EqiPed.Fecha_Pago >= '{filters.start_date}' AND EqiPed.Fecha_Pago <= '{filters.end_date}'")
        
        if not filters.include_cancelled:
            where_conditions.append("EqiFid.Estatus = 'AC'")
        
        if filters.provider:
            where_conditions.append(f"EqiFid.Proveedor = '{filters.provider}'")
        
        if filters.buyer:
            where_conditions.append(f"EqiFid.VendidoA = '{filters.buyer}'")
        
        if filters.pedimento_code:
            where_conditions.append(f"EqiPed.ClavePed = '{filters.pedimento_code}'")
        
        if filters.movement_type.value == "COMEX":
            where_conditions.append("EqiFid.ProvImpoDefCR = 'P'")
        elif filters.movement_type.value == "IMPDF":
            where_conditions.append("EqiFid.ProvImpoDefCR != 'P'")
        
        where_clause = " AND ".join(where_conditions)
        
        # Build detailed SQL query (includes partida/line details)
        sql_query = text(f"""
            SELECT 
                EqiFid.FacturaImpoDef AS C1,
                EqiFid.PedimentoImpoDef AS C2,
                EqiFid.FechaFactura AS C3,
                EqiFid.Estatus AS C4,
                EqiPed.ClavePed AS C5,
                EqiPed.Regimen AS C10,
                EqiPed.Fecha_Inicio AS C11,
                EqiPed.Fecha_Fin AS C12,
                EqiPed.Fecha_Pago AS C13,
                EqiFid.Remesa AS C14,
                EqiFid.TipoCambio AS C15,
                EqiFid.Proveedor AS C16,
                EqiFid.VendidoA AS C17,
                EqiFid.AAduanal AS C18,
                EpiDef.Clase AS C20,
                REPLACE(REPLACE(REPLACE(REPLACE(EpiDef.DescripcionE, CHAR(44), ' '), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' ') AS C21,
                REPLACE(REPLACE(REPLACE(REPLACE(EqiCla.DescripcionI, CHAR(44), ' '), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' ') AS C22,
                EpiDef.CantImpoDef AS C23,
                EpiDef.UnidadMedida AS C24,
                EpiDef.ValorMN AS C25,
                EpiDef.ValorME AS C27,
                EpiDef.PesoNeto AS C29,
                EpiDef.PesoBruto AS C30,
                EpiDef.OrdenCompra AS C31,
                EpiDef.Fraccion AS C32,
                EpiDef.TipoFraccion AS C33,
                EpiDef.Sector AS C35,
                EpiDef.PaisOrigen AS C37,
                EqiPed.Aduana_Cruce AS C38,
                EqiFid.ProvImpoDefCR AS C39,
                EqiFid.Consecutivo AS C40,
                EpiDef.EsSubPartida AS C41,
                EqiPed.PedRectifica AS C42,
                EqiFid.EDocument AS C43,
                EqiFid.NumOperacionVU AS C44,
                EpiDef.LineaImpoDef AS C45,
                REPLACE(REPLACE(REPLACE(REPLACE(EpiDef.Marca, CHAR(44), ' '), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' ') AS C46,
                REPLACE(REPLACE(REPLACE(REPLACE(EpiDef.Modelo, CHAR(44), ' '), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' ') AS C47,
                EqiCla.FraccionAme AS C48,
                EqiCla.ECCN AS C49,
                EpiDef.NumParte AS C50,
                EqiFid.TipoCambio AS C51,
                EqiFid.FechaEmision AS C52,
                EqiFid.UsuarioCap AS C53,
                EqiFid.UsuarioAct AS C54,
                EqiFid.Transportista AS C55,
                EqiFid.Transporte + ' ' + EqiFid.NumTrasporte AS C56,
                EqiPed.Pedimento18 AS C57,
                EpiDef.Lote AS C58,
                EqiPed.TIPOPEDIMENTOTRANSPORTEE AS C59
            FROM [{filters.database_name}].dbo.QFacImpDef EqiFid
            LEFT JOIN [{filters.database_name}].dbo.QPedimentos EqiPed 
                ON EqiPed.Pedimento = EqiFid.PedimentoImpoDef
            LEFT JOIN [{filters.database_name}].dbo.QEqiDef EpiDef 
                ON EpiDef.Consecutivo = EqiFid.Consecutivo
            LEFT JOIN [{filters.database_name}].dbo.QClaAct EqiCla 
                ON EqiCla.Clase = EpiDef.Clase
            WHERE {where_clause}
        """)
        
        try:
            result = db.execute(sql_query)
            movements = []
            
            for row in result:
                linea = row[31]  # C45
                factura = row[0]  # C1
                pedimento = row[1]  # C2
                prov_impo_def_cr = row[25]  # C39
                es_subpartida = row[27]  # C41
                fecha_pago = row[8]  # C13
                tipo_pedimento = row[44]  # C59
                fecha_inicio = row[6]  # C11
                consecutivo = row[26]  # C40
                
                # Determine movement type
                tipo_mov = "COMEX" if prov_impo_def_cr == 'P' else "IMPDF"
                
                # Get provider info
                proveedor_info = self._get_client_provider_info(
                    db, filters.database_name, row[11], filters.is_shelter  # C16
                )
                
                # Get buyer info
                buyer_info = self._get_client_buyer_info(
                    db, filters.database_name, row[12], filters.is_shelter  # C17
                )
                
                # Get customs broker info
                broker_info = self._get_customs_broker_info(
                    db, filters.database_name, row[13]  # C18
                )
                
                # Calculate commercial value and exchange rate
                tipo_cambio = float(row[10] or 1.0)  # C15
                valor_comercial_mn = 0.0
                peso_neto = 0.0
                peso_bruto = 0.0
                
                # Only process values if it's a Partida (not Subpartida)
                if es_subpartida == 'P':
                    if filters.currency_type.value == "MN":
                        if filters.is_shelter:
                            if filters.exchange_rate_type.value == "FP" and fecha_pago:
                                fecha_tc = fecha_pago
                                if filters.use_transport_method and met_trans == 1:
                                    if tipo_pedimento in ('1', '4', '98E'):
                                        fecha_tc = fecha_inicio
                                
                                tc_value = self._obtener_tipo_cambio(
                                    db, filters.database_name, fecha_tc, filters.is_shelter
                                )
                                if tc_value:
                                    valor_comercial_mn = float(row[20] or 0) * tc_value  # C27 * TC
                                    tipo_cambio = tc_value
                                else:
                                    valor_comercial_mn = float(row[19] or 0)  # C25
                                    tipo_cambio = float(row[37] or 1.0)  # C51
                            else:
                                valor_comercial_mn = float(row[19] or 0)  # C25
                                tipo_cambio = float(row[37] or 1.0)  # C51
                        else:
                            if filters.exchange_rate_type.value == "FP" and fecha_pago:
                                fecha_tc = fecha_pago
                                if filters.use_transport_method and met_trans == 1:
                                    if tipo_pedimento in ('1', '4', '98E'):
                                        fecha_tc = fecha_inicio
                                
                                tc_value = self._obtener_tipo_cambio(
                                    db, filters.database_name, fecha_tc, filters.is_shelter
                                )
                                if tc_value:
                                    valor_comercial_mn = float(row[20] or 0) * tc_value  # C27 * TC
                                    tipo_cambio = tc_value
                                else:
                                    valor_comercial_mn = float(row[19] or 0)  # C25
                                    tipo_cambio = float(row[37] or 1.0)  # C51
                            else:
                                valor_comercial_mn = float(row[19] or 0)  # C25
                                tipo_cambio = float(row[37] or 1.0)  # C51
                    else:  # ME
                        valor_comercial_mn = float(row[20] or 0)  # C27
                        if filters.exchange_rate_type.value == "FP" and fecha_pago:
                            fecha_tc = fecha_pago
                            if filters.use_transport_method and met_trans == 1:
                                if tipo_pedimento in ('1', '4', '98E'):
                                    fecha_tc = fecha_inicio
                            
                            tc_value = self._obtener_tipo_cambio(
                                db, filters.database_name, fecha_tc, filters.is_shelter
                            )
                            if tc_value:
                                tipo_cambio = tc_value
                            else:
                                tipo_cambio = float(row[37] or 1.0)  # C51
                        else:
                            tipo_cambio = float(row[37] or 1.0)  # C51
                    
                    peso_neto = float(row[21] or 0)  # C29
                    peso_bruto = float(row[22] or 0)  # C30
                # If es_subpartida == 'S', values remain 0
                
                # Get customs office name
                aduana_nombre = self._get_customs_office_name(
                    db, filters.database_name, row[24]  # C38
                )
                
                # Get series information
                series_info = self._get_definitive_series_info(
                    db, filters.database_name, consecutivo, linea, filters.is_shelter
                )
                
                # Get export symbol
                symbolo_ex = self._get_part_export_symbol(
                    db, filters.database_name, row[36], filters.is_shelter  # C50
                )
                
                # Get rectification pedimento
                pedimento_r1 = self._buscar_rectificacion(pedimento, row[28])  # C42
                
                # Get driver badge
                num_gaf_uni = self._get_driver_badge(db, filters.database_name, factura)
                
                # Create detailed movement item
                movement = MovementItemDetailed(
                    Linea=linea,
                    Factura=factura,
                    Pedimento=pedimento,
                    FechaFactura=row[2],  # C3
                    Estatus=row[3],  # C4
                    ClavePed=row[4],  # C5
                    TipoMovTemDef=tipo_mov,
                    EsCambioRegimen='N',
                    Regimen=row[5],  # C10
                    Fecha_Inicio=row[6],  # C11
                    Fecha_Fin=row[7],  # C12
                    Fecha_Pago=row[8],  # C13
                    Remesa=row[9],  # C14,
                    Proveedor=proveedor_info.get("nombre"),
                    RFCProveedor=proveedor_info.get("rfc"),
                    ProveedorTaxID=proveedor_info.get("tax_id"),
                    VendidoA=buyer_info.get("nombre"),
                    VendidoARFC=buyer_info.get("rfc"),
                    VendidoATaxID=buyer_info.get("tax_id"),
                    AgenteAduanal=broker_info.get("nombre"),
                    Patente=broker_info.get("patente"),
                    NumParte=row[14],  # C20
                    DescripcionE=row[15],  # C21
                    DescripcionI=row[16],  # C22
                    CantidadIE=float(row[17] or 0),  # C23
                    UniMed=row[18],  # C24
                    ValorComercialMN=valor_comercial_mn,
                    TipoCambio=tipo_cambio,
                    PesoNeto=peso_neto,
                    PesoBruto=peso_bruto,
                    OrdenCompraVenta=row[23],  # C31
                    FraccionArancelaria=row[29],  # C32
                    Preferencia=row[30],  # C33
                    Sector=row[32],  # C35
                    PaisOrigen=row[33],  # C37
                    Aduana=aduana_nombre,
                    Advalorem=row[27],  # C41
                    TipoExpo='',
                    PedimentoR1=pedimento_r1,
                    EDocument=row[34],  # C43
                    NumOperacionVU=row[35],  # C44
                    Series=series_info,
                    Marca=row[40],  # C46
                    Modelo=row[41],  # C47
                    FraccionAmericana=row[42],  # C48
                    ECCN=row[43],  # C49
                    SimboloEx=symbolo_ex,
                    FechaEmision=row[38],  # C52
                    BaseDeDatos=filters.database_name,
                    NumGafUni=num_gaf_uni,
                    UsuarioCap=row[39],  # C53
                    UsuarioAcr=row[40],  # C54
                    Transportista=row[41],  # C55
                    NumCaja=row[42],  # C56
                    Pedimento18=row[43],  # C57
                    AduanaCru=row[24],  # C38
                    Lote=row[44]  # C58
                )
                
                movements.append(movement)
            
            logger.info(f"Successfully retrieved {len(movements)} detailed definitive movements")
            return movements
            
        except Exception as e:
            logger.error(f"Error fetching detailed definitive movements: {e}", exc_info=True)
            raise

    def _get_definitive_series_info(
        self, 
        db: Session, 
        db_name: str, 
        consecutivo: int, 
        linea: int,
        is_shelter: bool
    ) -> Optional[str]:
        """
        Get series information for a definitive import partida from QSeriesDef table.
        Returns formatted string with series, model, and part info.
        """
        if not consecutivo or not linea:
            return None
        
        try:
            sql = text(f"""
                SELECT SerieImpo, ModeloImpo, ParteImpo
                FROM [{db_name}].dbo.QSeriesDef
                WHERE Consecutivo = :consecutivo
                    AND LineaImpoDef = :linea
            """)
            result = db.execute(sql, {"consecutivo": consecutivo, "linea": linea}).fetchall()
            
            if not result:
                return None
            
            series_list = []
            for idx, row in enumerate(result, 1):
                serie = row[0]
                modelo = row[1]
                parte = row[2]
                
                serie_str = f"{idx}) {serie}"
                if modelo:
                    serie_str += f". Modelo: {modelo}"
                if parte:
                    serie_str += f". Parte: {parte}"
                
                series_list.append(serie_str)
            
            return " | ".join(series_list) if series_list else None
            
        except Exception as e:
            logger.debug(f"Error fetching definitive series info for consecutivo {consecutivo}, linea {linea}: {e}")
            return None

    def get_repair_import_movements(
        self, 
        db: Session, 
        filters: ImportRepairFilter
    ) -> List[MovementItem]:
        """
        Retrieve repair import movements from legacy database (LLENADOIMP_REPARACION - NORMAL).
        Aggregates movements by invoice number.
        
        Args:
            db: Database session
            filters: Filter criteria for the query
            
        Returns:
            List of movement items matching the criteria
            
        Raises:
            Exception: If database query fails
        """
        logger.info(f"Fetching repair import movements with filters: {filters.model_dump()}")
        
        # Get configuration
        met_trans = self._get_met_trans_config()

        # Build WHERE clause
        where_conditions = []
        
        # Date range filter
        if filters.range_type.value == "FF":
            where_conditions.append(f"FimRep.FechaFactura >= '{filters.start_date}' AND FimRep.FechaFactura <= '{filters.end_date}'")
        else:
            where_conditions.append(f"EqiPed.Fecha_Pago >= '{filters.start_date}' AND EqiPed.Fecha_Pago <= '{filters.end_date}'")
        
        # Status filter
        if not filters.include_cancelled:
            where_conditions.append("FimRep.Estatus = 'AC'")
        
        # Provider filter
        if filters.provider:
            where_conditions.append(f"FimRep.Proveedor = '{filters.provider}'")
        
        # Buyer filter
        if filters.buyer:
            where_conditions.append(f"FimRep.VendidoA = '{filters.buyer}'")
        
        # Pedimento code filter
        if filters.pedimento_code:
            where_conditions.append(f"EqiPed.ClavePed = '{filters.pedimento_code}'")
        
        # Discharge filter for repair imports
        if filters.discharge_filter.value == "SiDes":
            where_conditions.append("RepPim.Descarga = 1")
        elif filters.discharge_filter.value == "NoDes":
            where_conditions.append("RepPim.Descarga = 0")
        # If ALL, no filter added
        
        # Exclude regime changes
        where_conditions.append("FimRep.EsCambioRegimen <> 'S'")
        
        where_clause = " AND ".join(where_conditions)
        
        # Build main SQL query for repair imports
        sql_query = text(f"""
            SELECT 
                FimRep.FacturaImpo AS C1,
                FimRep.PedimentoImpo AS C2,
                FimRep.FechaFactura AS C3,
                FimRep.Estatus AS C4,
                EqiPed.ClavePed AS C5,
                FimRep.ValorImpoME AS C6,
                FimRep.ValorImpoMN AS C7,
                FimRep.Proveedor AS C8,
                FimRep.VendidoA AS C9,
                EqiPed.Regimen AS C10,
                EqiPed.Fecha_Inicio AS C11,
                EqiPed.Fecha_Fin AS C12,
                EqiPed.Fecha_Pago AS C13,
                FimRep.Remesa AS C14,
                FimRep.TipoCambio AS C15,
                FimRep.AAduanal AS C18,
                FimRep.Consecutivo AS C39,
                EqiPed.PedRectifica AS C41,
                FimRep.EDocument AS C42,
                FimRep.NumOperacionVU AS C43,
                FimRep.TipoCambio AS C49,
                FimRep.FechaEmision AS C50,
                FimRep.UsuarioCap AS C51,
                FimRep.UsuarioAct AS C52,
                FimRep.Transportista AS C53,
                FimRep.Transporte + ' ' + FimRep.NumTrasporte AS C54,
                EqiPed.Pedimento18 AS C55,
                EqiPed.Aduana_Cruce AS C38,
                EqiPed.TIPOPEDIMENTOTRANSPORTEE AS C56
            FROM [{filters.database_name}].dbo.QFacImpRep FimRep
            LEFT JOIN [{filters.database_name}].dbo.QPedimentos EqiPed 
                ON EqiPed.Pedimento = FimRep.PedimentoImpo
            LEFT JOIN [{filters.database_name}].dbo.QEqiMaqRep RepPim 
                ON RepPim.Consecutivo = FimRep.Consecutivo
            LEFT JOIN [{filters.database_name}].dbo.QClaAct ClaAct 
                ON ClaAct.Clase = RepPim.Clase
            WHERE {where_clause}
        """)
        
        try:
            result = db.execute(sql_query)
            movements_dict = {}
            
            for row in result:
                factura = row[0]  # C1
                
                # Use factura + IMPRE as key
                tipo_mov = "IMPRE"
                key = (factura, tipo_mov)
                
                # If already exists, skip (we only want one entry per invoice in Normal mode)
                if key not in movements_dict:
                    consecutivo = row[16]  # C39
                    fecha_pago = row[12]  # C13
                    tipo_pedimento = row[28]  # C56
                    fecha_inicio = row[10]  # C11
                    
                    # Calculate total values for this invoice (with discharge filter)
                    valor_me, valor_mn = self._calculate_repair_totals(
                        db, filters.database_name, consecutivo, filters.discharge_filter.value
                    )
                    
                    # Calculate exchange rate and values using unified method
                    valor_comercial, tipo_cambio = self._calculate_exchange_rate_and_value(
                        db=db,
                        db_name=filters.database_name,
                        valor_me=valor_me,
                        valor_mn=valor_mn,
                        tipo_cambio_db=float(row[20] or 1.0),  # C49
                        fecha_pago=fecha_pago,
                        fecha_inicio=fecha_inicio,
                        tipo_pedimento=tipo_pedimento,
                        currency_type=filters.currency_type.value,
                        exchange_rate_type=filters.exchange_rate_type.value,
                        is_shelter=filters.is_shelter,
                        use_transport_method=filters.use_transport_method,
                        met_trans=met_trans
                    )
                    
                    # Get driver badge number
                    num_gaf_uni = self._get_driver_badge_repair(db, filters.database_name, factura)
                    
                    # Get rectification pedimento
                    pedimento = row[1]  # C2
                    ped_rectifica = row[17]  # C41
                    pedimento_r1 = self._buscar_rectificacion(pedimento, ped_rectifica)
                    
                    # Create movement item
                    movement = MovementItem(
                        Factura=factura,
                        Pedimento=row[1],  # C2
                        FechaFactura=row[2],  # C3
                        Estatus=row[3],  # C4
                        ClavePed=row[4],  # C5
                        TipoMovTemDef=tipo_mov,
                        EsCambioRegimen='N',
                        ValorMPTemp=valor_comercial,
                        ValorComercialMN=valor_comercial,
                        TipoCambio=tipo_cambio,
                        ValorAgre=0.0,
                        TipoExpo='',
                        PedimentoR1=pedimento_r1,
                        EDocument=row[18],  # C42
                        NumOperacionVU=row[19],  # C43
                        BaseDeDatos=filters.database_name,
                        NumGafUni=num_gaf_uni,
                        UsuarioCap=row[22],  # C51
                        UsuarioAcr=row[23],  # C52
                        Fecha_Pago=row[12],  # C13
                        NumCaja=row[25],  # C54
                        Pedimento18=row[26],  # C55
                        AduanaCru=row[27],  # C38
                        Lote=None  # Repair imports don't have Lote in main query
                    )
                    
                    movements_dict[key] = movement
            
            movements = list(movements_dict.values())
            logger.info(f"Successfully retrieved {len(movements)} repair import movements")
            return movements
            
        except Exception as e:
            logger.error(f"Error fetching repair import movements: {e}", exc_info=True)
            raise

    def _calculate_repair_totals(
        self, 
        db: Session, 
        db_name: str, 
        consecutivo: int,
        discharge_filter: str
    ) -> tuple:
        """
        Calculate total values for a repair import invoice.
        Sums up all partidas (items) excluding sub-partidas, with optional discharge filter.
        
        Args:
            db: Database session
            db_name: Database name
            consecutivo: Invoice consecutive number
            discharge_filter: "SiDes", "NoDes", or "ALL"
            
        Returns:
            tuple: (total_valor_me, total_valor_mn)
        """
        try:
            # Build discharge filter clause
            discharge_clause = ""
            if discharge_filter == "SiDes":
                discharge_clause = " AND RepPim.Descarga = 1"
            elif discharge_filter == "NoDes":
                discharge_clause = " AND RepPim.Descarga = 0"
            
            sql = text(f"""
                SELECT SUM(RepPim.ValorImpoME), SUM(RepPim.ValorImpoMN)
                FROM [{db_name}].dbo.QEqiMaqRep RepPim
                WHERE RepPim.Consecutivo = :consecutivo
                    AND RepPim.EsSubpartida = 'P'
                    {discharge_clause}
            """)
            result = db.execute(sql, {"consecutivo": consecutivo}).fetchone()
            
            if result:
                return (result[0] or 0, result[1] or 0)
            return (0, 0)
        except Exception as e:
            logger.error(f"Error calculating repair totals for consecutivo {consecutivo}: {e}")
            return (0, 0)

    def _get_driver_badge_repair(self, db: Session, db_name: str, factura: str) -> Optional[str]:
        """Get driver's unique badge number for a repair import invoice"""
        if not factura:
            return None
        
        try:
            sql = text(f"""
                SELECT NUMGAFETEUNICO 
                FROM [{db_name}].dbo.GConductor 
                LEFT JOIN [{db_name}].dbo.QFacImpRep 
                    ON QFacImpRep.CONDUCTOR = GConductor.CONDUCTOR 
                WHERE FacturaImpo = :factura
            """)
            result = db.execute(sql, {"factura": factura}).fetchone()
            return result[0] if result else None
        except Exception as e:
            logger.debug(f"Error fetching driver badge for repair invoice {factura}: {e}")
            return None

    def get_repair_import_movements_detailed(
        self, 
        db: Session, 
        filters: ImportRepairFilter
    ) -> List[MovementItemDetailed]:
        """
        Retrieve detailed repair import movements from legacy database (LLENADOIMP_REPARACION - DETALLADO).
        Returns individual partida lines with full detail.
        
        Args:
            db: Database session
            filters: Filter parameters including date range, discharge filter, etc.
            
        Returns:
            List of detailed movement items
        """
        try:
            logger.info(f"Fetching detailed repair import movements with filters: {filters}")
            
            # Get database name
            db_name = self._get_database_name(db)
            if not db_name:
                logger.error("Could not determine database name")
                return []
            
            # Get MetTrans configuration
            met_trans = self._get_met_trans_config()
            
            # Build WHERE clause
            where_clause = self._build_where_clause_repair(filters)
            
            # Build discharge filter for main query
            discharge_clause = ""
            if filters.discharge_filter == "SiDes":
                discharge_clause = " AND RepPim.Descarga = 1"
            elif filters.discharge_filter == "NoDes":
                discharge_clause = " AND RepPim.Descarga = 0"
            
            # Build main SQL query with all required fields for detailed mode
            sql = text(f"""
                SELECT 
                    RepPim.LineaImpo,                      -- C44: Linea
                    Rep.FacturaImpo,                       -- C1: Factura
                    Ped.PedNumero,                         -- C2: Pedimento
                    Rep.FechaFacImpo,                      -- C3: FechaFactura
                    Rep.Estatus,                           -- C4: Estatus
                    Ped.ClavePedImpo,                      -- C5: ClavePed
                    Ped.Regimen,                           -- C10: Regimen
                    Ped.FechaEntrada,                      -- C11: Fecha_Inicio
                    Ped.FechaPago,                         -- C13: Fecha_Pago
                    Rep.Remesa,                            -- C14: Remesa
                    Rep.TipoCambio,                        -- C15: TipoCambio (from header)
                    Rep.Cliente,                           -- C16: Cliente/Proveedor
                    Rep.VendidoA,                          -- C17: VendidoA
                    Rep.AgenteAduanal,                     -- C18: AgenteAduanal clave
                    RepPim.NumParteImpo,                   -- C20: NumParte
                    RepPim.DescripcionE,                   -- C21: DescripcionE
                    RepPim.DescripcionI,                   -- C22: DescripcionI
                    RepPim.CantidadImpo,                   -- C23: CantidadIE
                    RepPim.UnidadMedImpo,                  -- C24: UniMed
                    RepPim.ValorImpoMN,                    -- C25: ValorComercialMN (direct)
                    RepPim.ValorImpoME,                    -- C27: ValorComercialME
                    RepPim.PesoNetoImpo,                   -- C29: PesoNeto
                    RepPim.PesoBrutoImpo,                  -- C30: PesoBruto
                    RepPim.OrdenCompraVta,                 -- C31: OrdenCompraVenta
                    RepPim.FraccionImpo,                   -- C32: FraccionArancelaria
                    RepPim.Preferencia,                    -- C33: Preferencia
                    RepPim.Sector,                         -- C35: Sector
                    RepPim.PaisOrigenImpo,                 -- C37: PaisOrigen
                    RepPim.Aduana,                         -- C38: Aduana seccion
                    Rep.Consecutivo,                       -- C39: Consecutivo
                    RepPim.EsSubpartida,                   -- C40: Advalorem/EsSubpartida
                    Ped.PedRectifica,                      -- C41: PedRectifica
                    Rep.eDocument,                         -- C42: EDocument
                    Rep.NumOperacionVU,                    -- C43: NumOperacionVU
                    RepPim.Marca,                          -- C45: Marca
                    RepPim.Modelo,                         -- C46: Modelo
                    RepPim.FraccionAmericana,              -- C47: FraccionAmericana
                    RepPim.ECCN,                           -- C48: ECCN
                    RepPim.TipoCambio AS TipoCambioPartida, -- C49: TipoCambio (from partida)
                    Rep.FechaEmbarque,                     -- C50: FechaEmision
                    Rep.UsuarioCap,                        -- C51: UsuarioCap
                    Rep.UsuarioAct,                        -- C52: UsuarioAcr
                    Rep.Transportista,                     -- C53: Transportista
                    Rep.NumCaja,                           -- C54: NumCaja
                    Ped.Pedimento18,                       -- C55: Pedimento18
                    Ped.ClavePedImpo                       -- C56: ClavePedImpo (for MetTrans check)
                FROM [{db_name}].dbo.QFacImpRep Rep
                LEFT JOIN [{db_name}].dbo.QPedimentos Ped ON Rep.Pedimento = Ped.PedNumero
                LEFT JOIN [{db_name}].dbo.QEqiMaqRep RepPim ON Rep.Consecutivo = RepPim.Consecutivo
                WHERE Rep.EsCambioRegimen <> 'S'
                    {where_clause}
                    {discharge_clause}
                ORDER BY Rep.FacturaImpo, RepPim.LineaImpo
            """)
            
            results = db.execute(sql).fetchall()
            logger.info(f"Found {len(results)} detailed repair import partidas")
            
            movements = []
            for row in results:
                # Extract all fields from query
                linea = row[0]
                factura = row[1]
                pedimento = row[2]
                fecha_factura = row[3]
                estatus = row[4]
                clave_ped = row[5]
                regimen = row[6]
                fecha_inicio = row[7]
                fecha_pago = row[8]
                remesa = row[9]
                tipo_cambio_header = row[10]
                cliente = row[11]
                vendido_a = row[12]
                agente_aduanal_clave = row[13]
                num_parte = row[14]
                descripcion_e = row[15]
                descripcion_i = row[16]
                cantidad = row[17]
                uni_med = row[18]
                valor_mn_direct = row[19]
                valor_me = row[20]
                peso_neto = row[21]
                peso_bruto = row[22]
                orden_compra = row[23]
                fraccion = row[24]
                preferencia = row[25]
                sector = row[26]
                pais_origen = row[27]
                aduana_seccion = row[28]
                consecutivo = row[29]
                es_subpartida = row[30]
                ped_rectifica = row[31]
                e_document = row[32]
                num_operacion_vu = row[33]
                marca = row[34]
                modelo = row[35]
                fraccion_americana = row[36]
                eccn = row[37]
                tipo_cambio_partida = row[38]
                fecha_emision = row[39]
                usuario_cap = row[40]
                usuario_acr = row[41]
                transportista = row[42]
                num_caja = row[43]
                pedimento_18 = row[44]
                clave_ped_mettrans = row[45]
                
                # Get client/supplier information (Proveedor)
                proveedor_info = self._get_client_info(db, db_name, cliente, is_supplier=True)
                
                # Get sold-to client information (VendidoA)
                vendido_info = self._get_client_info(db, db_name, vendido_a, is_supplier=False)
                
                # Get customs agent information
                agente_info = self._get_customs_agent_info(db, db_name, agente_aduanal_clave)
                
                # Get customs section name
                aduana_nombre = self._get_aduana_seccion_nombre(db, db_name, aduana_seccion)
                
                # Calculate exchange rate and commercial value
                valor_mn, tipo_cambio_final = self._calculate_exchange_rate_and_value(
                    db=db,
                    db_name=db_name,
                    es_subpartida=es_subpartida,
                    valor_me=valor_me,
                    valor_mn_direct=valor_mn_direct,
                    fecha_pago=fecha_pago,
                    fecha_inicio=fecha_inicio,
                    clave_ped=clave_ped_mettrans,
                    tipo_cambio_partida=tipo_cambio_partida,
                    currency_type=filters.currency_type,
                    exchange_rate_type=filters.exchange_rate_type,
                    met_trans=met_trans
                )
                
                # Set peso values based on subpartida flag
                peso_neto_final = peso_neto if es_subpartida == 'P' else 0
                peso_bruto_final = peso_bruto if es_subpartida == 'P' else 0
                
                # Get series information for this partida
                series = self._get_series_info_repair(db, db_name, consecutivo, linea)
                
                # Get rectification pedimento
                pedimento_r1 = self._buscar_rectificacion(db, db_name, pedimento, ped_rectifica)
                
                # Get driver badge unique number
                num_gaf_uni = self._get_driver_badge_repair(db, db_name, factura)
                
                # Build movement item
                movement = MovementItemDetailed(
                    linea=linea,
                    factura=factura,
                    pedimento=pedimento,
                    fecha_factura=fecha_factura,
                    estatus=estatus,
                    clave_ped=clave_ped,
                    tipo_mov_tem_def="IMPRE",
                    es_cambio_regimen="N",
                    regimen=regimen,
                    fecha_inicio=fecha_inicio,
                    fecha_fin=None,  # Not available for repair imports
                    fecha_pago=fecha_pago,
                    remesa=remesa,
                    tipo_cambio=tipo_cambio_final,
                    proveedor=proveedor_info.get("nombre"),
                    rfc_proveedor=proveedor_info.get("rfc"),
                    proveedor_tax_id=proveedor_info.get("tax_id"),
                    vendido_a=vendido_info.get("nombre"),
                    vendido_a_rfc=vendido_info.get("rfc"),
                    vendido_a_tax_id=vendido_info.get("tax_id"),
                    agente_aduanal=agente_info.get("nombre"),
                    patente=agente_info.get("patente"),
                    num_parte=num_parte,
                    descripcion_e=self._remove_commas(descripcion_e),
                    descripcion_i=self._remove_commas(descripcion_i),
                    cantidad_ie=cantidad,
                    uni_med=uni_med,
                    valor_comercial_mn=valor_mn,
                    peso_neto=peso_neto_final,
                    peso_bruto=peso_bruto_final,
                    orden_compra_venta=orden_compra,
                    fraccion_arancelaria=fraccion,
                    preferencia=preferencia,
                    sector=sector,
                    pais_origen=pais_origen,
                    aduana=aduana_nombre,
                    advalorem=es_subpartida,
                    tipo_expo=None,
                    pedimento_r1=pedimento_r1,
                    e_document=e_document,
                    num_operacion_vu=num_operacion_vu,
                    series=series,
                    marca=marca,
                    modelo=modelo,
                    fraccion_americana=fraccion_americana,
                    eccn=eccn,
                    fecha_emision=fecha_emision,
                    base_de_datos=db_name,
                    num_gaf_uni=num_gaf_uni,
                    usuario_cap=usuario_cap,
                    usuario_acr=usuario_acr,
                    transportista=transportista,
                    num_caja=num_caja,
                    pedimento_18=pedimento_18,
                    aduana_cru=aduana_seccion
                )
                
                movements.append(movement)
            
            logger.info(f"Successfully processed {len(movements)} detailed repair import movements")
            return movements
            
        except Exception as e:
            logger.error(f"Error fetching detailed repair import movements: {e}", exc_info=True)
            raise

    def _get_series_info_repair(self, db: Session, db_name: str, consecutivo: int, linea: int) -> Optional[str]:
        """Get series information for repair import partida"""
        if not consecutivo or not linea:
            return None
        
        try:
            sql = text(f"""
                SELECT SerieImpo, ModeloImpo, ParteImpo 
                FROM [{db_name}].dbo.QSeriesImpoRep 
                WHERE Consecutivo = :consecutivo 
                    AND LineaImpo = :linea
                ORDER BY Renglon
            """)
            results = db.execute(sql, {"consecutivo": consecutivo, "linea": linea}).fetchall()
            
            if not results:
                return None
            
            series_list = []
            for idx, row in enumerate(results, 1):
                serie = row[0]
                modelo = row[1]
                parte = row[2]
                
                serie_str = f"{idx}) {serie}"
                if modelo:
                    serie_str += f". Modelo: {modelo}"
                if parte:
                    serie_str += f". Parte: {parte}"
                
                series_list.append(serie_str)
            
            return " | ".join(series_list) if series_list else None
            
        except Exception as e:
            logger.debug(f"Error fetching repair series info for consecutivo {consecutivo}, linea {linea}: {e}")
            return None

    def _build_where_clause_repair(self, filters: ImportRepairFilter) -> str:
        """Build WHERE clause for repair imports query"""
        conditions = []
        
        # Date range filter
        if filters.range_type == RangeType.INVOICE_DATE:
            conditions.append(f"Rep.FechaFacImpo BETWEEN '{filters.date_from}' AND '{filters.date_to}'")
        elif filters.range_type == RangeType.PAYMENT_DATE:
            conditions.append(f"Ped.FechaPago BETWEEN '{filters.date_from}' AND '{filters.date_to}'")
        elif filters.range_type == RangeType.ENTRY_DATE:
            conditions.append(f"Ped.FechaEntrada BETWEEN '{filters.date_from}' AND '{filters.date_to}'")
        
        return " AND " + " AND ".join(conditions) if conditions else ""


# Singleton instance
movement_service = MovementService()
