"""
Export repair service - handles EXPO REP movements (repair exports).
"""

import logging
from datetime import datetime
from sqlalchemy import text
from sqlalchemy.orm import Session
from typing import List, Optional

from ..schemas import ExportRepairFilter, MovementItem, MovementItemDetailed
from .base import ConfigHelper, StringHelper
from .database_helpers import DatabaseHelper
from .exchange_rate import ExchangeRateCalculator
from .query_builders import ExportRepairQueries

logger = logging.getLogger(__name__)


def parse_yyyymmdd_date(date_str: str) -> Optional[datetime]:
    """Parse date string in YYYYMMDD format to datetime."""
    if not date_str or date_str == '':
        return None
    try:
        return datetime.strptime(date_str, '%Y%m%d')
    except (ValueError, TypeError):
        return None


class ExportRepairService:
    """Service for handling export repair movements (EXPO REP)."""
    
    def get_movements(
        self,
        db: Session,
        filters: ExportRepairFilter
    ) -> List[MovementItem]:
        """
        Get export repair movements (normal mode - grouped by invoice).

        Aggregated query column order (ExportRepairQueries.build_aggregated_query):
            [0]  C1  - invoice_number
            [1]  C2  - pedimento_number
            [2]  C3  - invoice_date
            [3]  C6  - estatus (AC/NA)
            [4]  C7  - pedimento_code
            [5]  C8  - regime
            [6]  C11 - payment_date
            [7]  C12 - remesa
            [8]  C13 - exchange_rate (fecha_pago context)
            [9]  C14 - provider_id
            [10] C15 - sold_to_id
            [11] C16 - customs_broker_id
            [12] C27 - purchase_order
            [13] C33 - customs_office   ← AduanaCru
            [14] C34 - document_type    ← TipoFactura / tipo_mov
            [15] C35 - id               ← consecutivo
            [16] C40 - edocument        ← EDocument
            [17] C41 - vucem_op_num     ← NumOperacionVU
            [18] C48 - exchange_rate    ← TipoCambio
            [19] C49 - emission_date
            [20] C50 - capture_user     ← UsuarioCap
            [21] C51 - who_processed      ← UsuarioAcr
            [22] C52 - carrier_id       ← Transportista
            [23] C53 - transport        ← NumCaja
            [24] total_me
            [25] total_mn
            [26] C54 - ped_r1           ← PedimentoR1
        
        Args:
            db: Database session
            filters: Filter criteria
            
        Returns:
            List of movement items grouped by invoice
        """
        try:
            logger.info(f"Fetching export repair movements with filters: {filters.model_dump()}")
            
            # Get MetTrans configuration
            met_trans = ConfigHelper.get_met_trans_config()
            
            # Build WHERE clause
            where_clause = self._build_where_clause(filters)
            
            # Execute optimized aggregated query for NORMAL mode
            sql = text(ExportRepairQueries.build_aggregated_query(filters.database_name, where_clause))
            results = db.execute(sql).fetchall()
            logger.info(f"Found {len(results)} export repair invoices")
            
            movements = []
            
            for row in results:
                factura = row[0]   # C1  - FacturaExpo
                tipo_mov = row[14] # C34 - TipoFactura
                estatus = row[3]   # C6  - Estatus (AC o NA)
                
                # Filtrar facturas según include_cancelled
                if not filters.include_cancelled and estatus != 'AC':
                    continue
                
                consecutivo = row[15]  # C35 - Consecutivo
                
                # Totals come directly from GROUP BY query (no N+1 problem)
                def to_float(val):
                    if val is None or val == '': return 0.0
                    try: return float(val)
                    except (ValueError, TypeError): return 0.0

                total_me = to_float(row[24])  # total_me
                total_mn = to_float(row[25])  # total_mn
                sum_value_usd = to_float(row[27])
                sum_value_mxn = to_float(row[28])
                
                total_me = sum_value_usd if sum_value_usd > 0 else total_me
                total_mn = sum_value_mxn if sum_value_mxn > 0 else total_mn
                
                # Calculate exchange rate and value
                valor_comercial, tipo_cambio = ExchangeRateCalculator.calculate_for_aggregated(
                    db=db,
                    db_name=filters.database_name,
                    valor_me=total_me,
                    valor_mn=total_mn,
                    tipo_cambio_db=row[18],  # C48 - TipoCambio
                    fecha_pago=row[6],        # C11 - Fecha_Pago
                    fecha_inicio='',
                    tipo_pedimento='',
                    currency_type=filters.currency_type.value,
                    exchange_rate_type=filters.exchange_rate_type.value,
                    is_shelter=filters.is_shelter,
                    use_transport_method=False,
                    met_trans=met_trans
                )
                
                # Get pedimento rectification (already resolved by SQL COALESCE)
                pedimento_r1 = DatabaseHelper.get_rectification_pedimento(
                    db,
                    row[1],   # C2  - PedimentoExpo
                    row[26],  # C54 - PedRectifica (pre-built by SQL)
                    filters.is_shelter
                )
                
                # Get driver badge
                num_gaf_uni = self._get_driver_badge(db, filters.database_name, factura)
                
                # Build movement item
                movement = MovementItem(
                    Factura=factura,
                    Pedimento=row[1],                           # C2  - PedimentoExpo
                    FechaFactura=parse_yyyymmdd_date(row[2]),   # C3  - FechaFactura
                    Estatus=row[3],                             # C6  - Estatus
                    ClavePed=row[4],                            # C7  - ClavePed
                    TipoMovTemDef=tipo_mov,                     # C34 - TipoFactura
                    EsCambioRegimen='N',
                    ValorMPTemp=valor_comercial,
                    ValorComercialMN=valor_comercial,
                    TipoCambio=tipo_cambio,
                    ValorAgre=to_float(row[29]),
                    TipoExpo='EXPO REP',
                    PedimentoR1=pedimento_r1,
                    EDocument=row[16],                          # C40 - EDocument      ← FIXED (was 17)
                    NumOperacionVU=row[17],                     # C41 - NumOperacionVU ← FIXED (was 18)
                    BaseDeDatos=filters.database_name,
                    NumGafUni=num_gaf_uni,
                    UsuarioCap=row[20],                         # C50 - UsuarioCap     ← FIXED (was 21)
                    UsuarioAcr=row[21],                         # C51 - UsuarioAct     ← FIXED (was 22)
                    Fecha_Pago=parse_yyyymmdd_date(row[6]),     # C11 - Fecha_Pago     ← FIXED (was 8)
                    NumCaja=row[23],                            # C53 - NumCaja
                    Pedimento18='',
                    AduanaCru=row[13],                          # C33 - customs_office ← FIXED (was 14)
                    Lote=''
                )
                
                movements.append(movement)
            
            logger.info(f"Successfully processed {len(movements)} export repair movements")
            return movements
            
        except Exception as e:
            logger.error(f"Error fetching export repair movements: {e}", exc_info=True)
            raise
    
    def get_movements_detailed(
        self,
        db: Session,
        filters: ExportRepairFilter
    ) -> List[MovementItemDetailed]:
        """
        Get export repair movements (detailed mode - line by line).
        
        Args:
            db: Database session
            filters: Filter criteria
            
        Returns:
            List of detailed movement items (one per partida)
        """
        try:
            logger.info(f"Fetching detailed export repair movements with filters: {filters.model_dump()}")
            
            # Get MetTrans configuration
            met_trans = ConfigHelper.get_met_trans_config()
            
            # Build WHERE clause
            where_clause = self._build_where_clause(filters)
            
            # Add discharge filter to WHERE clause
            discharge_clause = ""
            if filters.discharge_filter.value == "SiDes":
                discharge_clause = " AND RepPex.Descarga = 1"
            elif filters.discharge_filter.value == "NoDes":
                discharge_clause = " AND RepPex.Descarga = 0"
            
            where_with_discharge = where_clause + discharge_clause
            
            # Execute main query
            sql = text(ExportRepairQueries.build_main_query(filters.database_name, where_with_discharge))
            results = db.execute(sql).fetchall()
            logger.info(f"Found {len(results)} detailed export repair partidas")
            
            movements = []
            
            for row in results:
                # Skip cancelled if not included
                if not filters.include_cancelled and row[5] == 'NA':  # C6 - Estatus
                    continue
                
                # Get provider information
                proveedor_info = DatabaseHelper.get_client_info(
                    db, filters.database_name, row[13], is_supplier=True  # C14 - Proveedor
                )
                
                # Get buyer information
                vendido_info = DatabaseHelper.get_client_info(
                    db, filters.database_name, row[14], is_supplier=False  # C15 - VendidoA
                )
                
                # Get customs agent information
                agente_info = DatabaseHelper.get_customs_agent_info(
                    db, filters.database_name, row[15]  # C16 - AAduanal
                )
                
                # Get customs section name
                aduana_nombre = DatabaseHelper.get_aduana_seccion_nombre(
                    db, filters.database_name, row[32]  # C33 - Aduana_Cruce
                )
                
                # Set peso values based on subpartida flag (allow 'PT', 'MP', etc. but block 'S')
                if row[37] != 'S':  # C38 - EsSubPartida
                    valor_comercial, tipo_cambio = ExchangeRateCalculator.calculate_for_partida(
                        db=db,
                        db_name=filters.database_name,
                        valor_me=row[36],  # C37 - ValorExpoME
                        valor_mn=row[35],  # C36 - ValorExpoMN
                        tipo_cambio_db=row[47],  # C48 - TipoCambio
                        fecha_pago=row[10],  # C11 - Fecha_Pago
                        fecha_inicio=row[8],  # C9 - Fecha_Inicio
                        tipo_pedimento=row[55],  # C56 - TIPOPEDIMENTOTRANSPORTEE
                        currency_type=filters.currency_type.value,
                        exchange_rate_type=filters.exchange_rate_type.value,
                        is_shelter=filters.is_shelter,
                        use_transport_method=False,
                        met_trans=met_trans
                    )
                    peso_neto = float(row[24]) if row[24] else 0.0  # C25
                    peso_bruto = float(row[25]) if row[25] else 0.0  # C26
                else:  # Subpartida
                    valor_comercial = 0.0
                    tipo_cambio = 0.0
                    peso_neto = 0.0
                    peso_bruto = 0.0
                
                # Get series information
                series_info = DatabaseHelper.get_series_info_export(
                    db, filters.database_name, row[34], row[41], filters.is_shelter  # C35, C42
                )
                
                # Get part export symbol
                simbolo_ex = None
                if row[46]:  # C47 - NumParte
                    simbolo_ex = DatabaseHelper.get_part_export_symbol(
                        db, filters.database_name, row[46], filters.is_shelter
                    )
                
                # Get pedimento rectification
                pedimento_r1 = DatabaseHelper.get_rectification_pedimento(
                    db,
                    row[1],   # C2 - PedimentoExpo
                    row[38],  # C39 - PedRectifica
                    filters.is_shelter
                )
                
                # Get driver badge
                num_gaf_uni = self._get_driver_badge(db, filters.database_name, row[0])
                
                # Build detailed movement item
                movement = MovementItemDetailed(
                    Linea=row[41],                      # C42 - LineaExpo
                    Factura=row[0],                     # C1  - FacturaExpo
                    Pedimento=row[1],                   # C2  - PedimentoExpo
                    FechaFactura=row[2],                # C3  - FechaFactura
                    Estatus=row[5],                     # C6  - Estatus
                    ClavePed=row[6],                    # C7  - ClavePed
                    TipoMovTemDef=row[33],              # C34 - TipoFactura
                    EsCambioRegimen='N',
                    Regimen=row[7],                     # C8  - Regimen
                    Fecha_Inicio=parse_yyyymmdd_date(row[8]),                # C9  - Fecha_Inicio
                    Fecha_Fin=parse_yyyymmdd_date(row[9]),                   # C10 - Fecha_Fin
                    Fecha_Pago=parse_yyyymmdd_date(row[10]),                 # C11 - Fecha_Pago
                    Remesa=row[11],                     # C12 - Remesa
                    Proveedor=proveedor_info.get('name'),
                    RFCProveedor=proveedor_info.get('rfc'),
                    ProveedorTaxID=proveedor_info.get('tax_id'),
                    VendidoA=vendido_info.get('name'),
                    VendidoARFC=vendido_info.get('rfc'),
                    VendidoATaxID=vendido_info.get('tax_id'),
                    AgenteAduanal=agente_info.get('name'),
                    Patente=agente_info.get('license'),
                    NumParte=row[46],                   # C47 - NumParte
                    DescripcionE=StringHelper.clean_text(row[18]),  # C19
                    DescripcionI=StringHelper.clean_text(row[19]),  # C20
                    CantidadIE=float(row[20]) if row[20] else 0.0,  # C21
                    UniMed=row[21],                     # C22
                    ValorComercialMN=valor_comercial,
                    TipoCambio=tipo_cambio,
                    PesoNeto=peso_neto,
                    PesoBruto=peso_bruto,
                    OrdenCompraVenta=row[26],           # C27 - OrdenCompra
                    FraccionArancelaria=row[27],        # C28 - FraccionExpo
                    Preferencia=row[28],                # C29 - TipoFraccion
                    Sector=row[30],                     # C31 - Sector
                    PaisOrigen=row[31],                 # C32 - PaisOrigen
                    Aduana=aduana_nombre,
                    Advalorem=row[37],                  # C38 - EsSubPartida
                    TipoExpo='EXPO REP',
                    PedimentoR1=pedimento_r1,
                    EDocument=row[39],                  # C40 - EDocument
                    NumOperacionVU=row[40],             # C41 - NumOperacionVU
                    Series=series_info,
                    Marca=StringHelper.clean_text(row[42]),   # C43
                    Modelo=StringHelper.clean_text(row[43]),  # C44
                    FraccionAmericana=row[44],          # C45 - FraccionAme
                    ECCN=row[45],                       # C46 - ECCN
                    SimboloEx=simbolo_ex,
                    FechaEmision=parse_yyyymmdd_date(row[48]) if row[48] else None,  # C49 - FechaFactura
                    BaseDeDatos=filters.database_name,
                    NumGafUni=num_gaf_uni,
                    UsuarioCap=row[49],                 # C50 - UsuarioCap
                    UsuarioAcr=row[50],                 # C51 - UsuarioAct
                    Transportista=row[51],              # C52 - Transportista
                    NumCaja=row[52],                    # C53 - Transporte + NumTrasporte
                    Pedimento18=row[53],                # C54 - Pedimento18
                    AduanaCru=row[32],                  # C33 - Aduana_Cruce
                    Lote=row[54] if len(row) > 54 else ''  # C55 - Lote
                )
                
                movements.append(movement)
            
            logger.info(f"Successfully processed {len(movements)} detailed export repair movements")
            return movements
            
        except Exception as e:
            logger.error(f"Error fetching detailed export repair movements: {e}", exc_info=True)
            raise
    
    def _build_where_clause(self, filters: ExportRepairFilter) -> str:
        """Build WHERE clause for export repair query."""
        where_conditions = []
        
        # STRICT SEPARATION: Only exports for repair
        where_conditions.append("ih.operation_type = 'exp'")
        # GOLDEN RULE: If movement_type is ALL, only filter by operation_type
        if filters.movement_type.value == "ALL":
            pass
        else:
            where_conditions.append("ih.invoice_type = 'REPAR'")
        
        # Date range filter
        if filters.range_type.value == "FF":
            where_conditions.append(f"ih.invoice_date >= TO_DATE('{filters.start_date}', 'YYYYMMDD') AND ih.invoice_date <= TO_DATE('{filters.end_date}', 'YYYYMMDD')")
        else:
            where_conditions.append(f"pd.payment_date >= TO_DATE('{filters.start_date}', 'YYYYMMDD') AND pd.payment_date <= TO_DATE('{filters.end_date}', 'YYYYMMDD')")
        
        # Provider filter
        if filters.provider:
            where_conditions.append(f"cmp.provider_id IN (SELECT id FROM a76.clients_and_providers WHERE name = '{filters.provider}')")
        
        # Buyer filter
        if filters.buyer:
            where_conditions.append(f"cmp.sold_to_id IN (SELECT id FROM a76.clients_and_providers WHERE name = '{filters.buyer}')")
        
        # Pedimento code filter
        if filters.pedimento_code:
            where_conditions.append(f"ped.pedimento_code = '{filters.pedimento_code}'")
        
        # Movement type filter
        if filters.movement_type.value == "AFIJO":
            where_conditions.append("ih.document_type = 'AFIJO'")
        elif filters.movement_type.value == "NODES":
            where_conditions.append("ih.document_type = 'NODES'")
        
        return " AND ".join(where_conditions)
    
        return total_me, total_mn
    
    def _get_driver_badge(self, db: Session, db_name: str, factura: str) -> str:
        """Get driver badge number for invoice."""
        # TODO: GConductor table not migrated to PostgreSQL yet
        return None