"""
Definitive import service - handles COMEX/IMPDF movements.
"""

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

if TYPE_CHECKING:
    from ..schemas import ImportDefinitiveFilter, MovementItem, MovementItemDetailed

from .base import ConfigHelper, StringHelper
from .database_helpers import DatabaseHelper
from .exchange_rate import ExchangeRateCalculator
from .query_builders import DefinitiveImportQueries

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 DefinitiveImportService:
    """Service for handling definitive import movements (COMEX/IMPDF)."""
    
    def get_movements(
        self,
        db: Session,
        filters: "ImportDefinitiveFilter"
    ) -> List["MovementItem"]:
        """
        Get definitive import movements (normal mode - grouped by invoice).
        
        Args:
            db: Database session
            filters: Filter criteria
            
        Returns:
            List of movement items grouped by invoice
        """
        from ..schemas import MovementItem
        
        try:
            logger.info(f"Fetching definitive import 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(DefinitiveImportQueries.build_aggregated_query(filters.database_name, where_clause))
            results = db.execute(sql).fetchall()
            logger.info(f"Found {len(results)} definitive import invoices")
            
            movements = []
            
            for row in results:
                factura = row[0]  # C1 - FacturaImpoDef
                estatus = row[3]  # C4 - Estatus (AC o NA)
                
                # Filtrar facturas según include_cancelled
                # Si include_cancelled=False, solo mostrar AC (status=true)
                # Si include_cancelled=True, mostrar todas (AC y NA)
                if not filters.include_cancelled and estatus != 'AC':
                    continue
                
                invoice_id = row[16]  # C35 - invoice ID
                
                # Helper to safely convert to float
                def to_float(val):
                    if val is None or val == '':
                        return 0.0
                    try:
                        return float(val)
                    except (ValueError, TypeError):
                        return 0.0

                # Totals come directly from GROUP BY query (no N+1 problem)
                total_me = to_float(row[28])  # total_me from SUM aggregation
                total_mn = to_float(row[29])  # total_mn from SUM aggregation
                sum_value_usd = to_float(row[30])
                sum_value_mxn = to_float(row[31])
                
                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=to_float(row[20]),  # C51 - TipoCambio
                    fecha_pago=row[8],  # C13 - Fecha_Pago
                    fecha_inicio=row[6],  # C11 - Fecha_Inicio
                    tipo_pedimento=row[4],  # C5 - ClavePed (Fix: using C5 instead of empty C59)
                    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
                pedimento_r1 = DatabaseHelper.get_rectification_pedimento(
                    db,
                    row[1],  # C2 - PedimentoImpoDef
                    row[17],  # C42 - PedRectifica
                    filters.is_shelter
                )
                
                # Get driver badge
                num_gaf_uni = DatabaseHelper.get_driver_badge(
                    db, filters.database_name, factura
                )
                
                # Build movement item
                movement = MovementItem(
                    Factura=factura,
                    Pedimento=row[1],  # C2 - PedimentoImpoDef
                    FechaFactura=parse_yyyymmdd_date(row[2]),  # C3 - FechaFactura
                    Estatus=row[3],  # C4 - Estatus
                    ClavePed=row[4],  # C5 - ClavePed
                    TipoMovTemDef='IMPDF',
                    EsCambioRegimen='N',
                    ValorMPTemp=valor_comercial,
                    ValorComercialMN=valor_comercial,
                    TipoCambio=tipo_cambio,
                    ValorAgre=0.0,
                    TipoExpo='',
                    PedimentoR1=pedimento_r1,
                    EDocument=row[18],  # C43 - EDocument
                    NumOperacionVU=row[19],  # C44 - NumOperacionVU
                    BaseDeDatos=filters.database_name,
                    NumGafUni=num_gaf_uni,
                    UsuarioCap=row[22],  # C53 - UsuarioCap
                    UsuarioAcr=row[23],  # C54 - UsuarioAct
                    Fecha_Pago=parse_yyyymmdd_date(row[8]),  # C13 - Fecha_Pago
                    NumCaja=row[24],  # C56 - Transporte + NumTrasporte
                    Pedimento18=row[25],  # C57 - empty (index 25)
                    AduanaCru=row[15],  # C39 - Aduana_Cruce
                    Lote=row[26]  # C58 - empty (index 26)
                )
                
                movements.append(movement)
            
            logger.info(f"Successfully processed {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 get_movements_detailed(
        self,
        db: Session,
        filters: "ImportDefinitiveFilter"
    ) -> List["MovementItemDetailed"]:
        """
        Get definitive import movements (detailed mode - line by line).
        
        Args:
            db: Database session
            filters: Filter criteria
            
        Returns:
            List of detailed movement items (one per partida)
        """
        from ..schemas import MovementItemDetailed
        
        try:
            logger.info(f"Fetching detailed definitive import 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 main query
            sql = text(DefinitiveImportQueries.build_main_query(filters.database_name, where_clause))
            results = db.execute(sql).fetchall()
            logger.info(f"Found {len(results)} detailed definitive import partidas")
            
            movements = []
            
            for row in results:
                # Skip cancelled if not included
                if not filters.include_cancelled and row[3] != 'AC':  # C4 - Estatus
                    continue
                
                # Get additional detailed information
                # Provider and client names now come directly from query (row[7], row[8])
                # But we still need RFC and TaxID from the helper
                proveedor_info = DatabaseHelper.get_client_info(
                    db, filters.database_name, row[15], is_supplier=True
                )
                vendido_info = DatabaseHelper.get_client_info(
                    db, filters.database_name, row[16], is_supplier=False
                )
                agente_info = DatabaseHelper.get_customs_agent_info(
                    db, filters.database_name, row[17]
                )
                aduana_nombre = DatabaseHelper.get_aduana_seccion_nombre(
                    db, filters.database_name, row[38]
                )
                
                # Calculate values using unified method
                valor_comercial, tipo_cambio = ExchangeRateCalculator.calculate_exchange_rate_and_value(
                    db=db,
                    db_name=filters.database_name,
                    es_subpartida=row[39],  # C40 - EsSubPartida
                    valor_me=row[26],       # C27 - ValorImpoME
                    valor_mn_direct=row[24], # C25 - ValorImpoMN
                    fecha_pago=row[12],      # C13 - Fecha_Pago
                    fecha_inicio=row[10],    # C1 entry_date
                    clave_ped=row[57] if len(row) > 57 else '', # C58 - TIPOPEDIMENTOTRANSPORTEE
                    tipo_cambio_partida=row[49], # C50 - TipoCambio
                    currency_type=filters.currency_type.value,
                    exchange_rate_type=filters.exchange_rate_type.value,
                    met_trans=met_trans
                )
                
                # Set peso values based on subpartida flag
                if row[39] == 'P': # C40 - EsSubPartida
                    peso_neto = float(row[28]) if row[28] else 0.0  # C29
                    peso_bruto = float(row[29]) if row[29] else 0.0  # C30
                else:
                    peso_neto = 0.0
                    peso_bruto = 0.0
                
                series_info = DatabaseHelper.get_series_info(
                    db, filters.database_name, row[38], row[43], filters.is_shelter # C39, C44
                )
                
                simbolo_ex = None
                if row[48]: # C49 - Part Number
                    simbolo_ex = DatabaseHelper.get_part_export_symbol(
                        db, filters.database_name, row[48], filters.is_shelter
                    )
                
                pedimento_r1 = DatabaseHelper.get_rectification_pedimento(
                    db, row[1], row[40], filters.is_shelter # C2, C41
                )
                
                num_gaf_uni = DatabaseHelper.get_driver_badge(
                    db, filters.database_name, row[0] # C1
                )
                
                movement = MovementItemDetailed(
                    Linea=row[43],  # C44
                    Factura=row[0],  # C1
                    Pedimento=row[1], # C2
                    FechaFactura=parse_yyyymmdd_date(row[2]), # C3
                    Estatus=row[3], # C4
                    ClavePed=row[4], # C5
                    TipoMovTemDef='IMPDF',
                    EsCambioRegimen='N',
                    Regimen=row[9], # C10
                    Fecha_Inicio=parse_yyyymmdd_date(row[10]), # C11
                    Fecha_Fin=parse_yyyymmdd_date(row[11]), # C12
                    Fecha_Pago=parse_yyyymmdd_date(row[12]), # C13
                    Remesa=str(row[13]) if row[13] is not None else None, # C14
                    Proveedor=row[7],  # C8 - Provider name (from JOIN)
                    RFCProveedor=proveedor_info.get('rfc'),
                    ProveedorTaxID=proveedor_info.get('tax_id'),
                    VendidoA=row[8],  # C9 - Client name (from JOIN)
                    VendidoARFC=vendido_info.get('rfc'),
                    VendidoATaxID=vendido_info.get('tax_id'),
                    AgenteAduanal=agente_info.get('name'),
                    Patente=agente_info.get('license'),
                    NumParte=row[48], # C49
                    DescripcionE=StringHelper.clean_text(row[20]), # C21
                    DescripcionI=StringHelper.clean_text(row[21]), # C22
                    CantidadIE=float(row[22]) if row[22] else 0.0, # C23
                    UniMed=row[23], # C24
                    ValorComercialMN=valor_comercial,
                    TipoCambio=tipo_cambio,
                    PesoNeto=peso_neto,
                    PesoBruto=peso_bruto,
                    OrdenCompraVenta=row[30], # C31
                    FraccionArancelaria=row[31], # C32
                    Preferencia=row[32], # C33
                    Sector=row[34],  # C35
                    PaisOrigen=row[36], # C37
                    Aduana=aduana_nombre,
                    Advalorem='P' if row[39] == 'P' else 'S', # C40
                    TipoExpo='',
                    PedimentoR1=pedimento_r1,
                    EDocument=row[41], # C42
                    NumOperacionVU=row[42], # C43
                    Series=series_info,
                    Marca=StringHelper.clean_text(row[44]), # C45
                    Modelo=StringHelper.clean_text(row[45]), # C46
                    FraccionAmericana=row[46],  # C47
                    ECCN=row[47],  # C48
                    SimboloEx=simbolo_ex,
                    FechaEmision=parse_yyyymmdd_date(row[50]) if row[50] else None,  # C51
                    BaseDeDatos=filters.database_name,
                    NumGafUni=num_gaf_uni,
                    UsuarioCap=row[51],  # C52
                    UsuarioAcr=row[52],  # C53
                    Transportista=row[53],  # C54
                    NumCaja=row[54],  # C55
                    Pedimento18=row[55] if len(row) > 55 else '',  # C56
                    AduanaCru=row[37],  # C38
                    Lote=row[56] if len(row) > 56 else ''  # C57
                )
                
                movements.append(movement)
            
            logger.info(f"Successfully processed {len(movements)} detailed definitive import movements")
            return movements
            
        except Exception as e:
            logger.error(f"Error fetching detailed definitive import movements: {e}", exc_info=True)
            raise
    
    def _build_where_clause(self, filters: "ImportDefinitiveFilter") -> str:
        """Build WHERE clause for definitive imports query."""
        where_conditions = []
        
        # STRICT SEPARATION: Only imports
        where_conditions.append("ih.operation_type = 'imp'")
        
        # GOLDEN RULE: If movement_type is ALL, only filter by operation_type
        # ALWAYS filter by specific invoice_type to avoid duplication with Temporary service
        where_conditions.append("ih.invoice_type IN ('DEF', 'MATDE', 'EXDEF')")
        
        # 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')")
        
        # Note: Status filter applied at Python level after CASE WHEN in SELECT
        # because status doesn't directly represent AC/NA status
        
        # 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}'")
        
        return " AND ".join(where_conditions)
    
    def _calculate_totals(self, db: Session, db_name: str, consecutivo: int) -> tuple:
        """Calculate totals for main partidas only.
        
        Only sums partidas where is_subpartida is false (equivalent to EsSubpartida = 'P' in Clarion).
        """
        sql = text("""
            SELECT 
                COALESCE(SUM(lf.value_usd), 0), 
                COALESCE(SUM(lf.value_mxn), 0) 
            FROM a76.item_line_financials lf
            INNER JOIN a76.item_lines il ON il.id = lf.item_line_id
            WHERE il.invoice_id = :consecutivo
                AND COALESCE(il.is_subpartida, false) = false
        """)
        
        result = db.execute(sql, {"consecutivo": consecutivo}).fetchone()
        total_me = float(result[0]) if result and result[0] is not None else 0.0
        total_mn = float(result[1]) if result and result[1] is not None else 0.0
        
        return total_me, total_mn
