"""
Export service - handles export movements (EXPO DEF).
"""

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

from ..schemas import ExportFilter, MovementItem, MovementItemDetailed
from .base import ConfigHelper, StringHelper
from .database_helpers import DatabaseHelper
from .exchange_rate import ExchangeRateCalculator
from .query_builders import ExportQueries

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 ExportService:
    """Service for handling export movements (EXPO DEF)."""
    
    def get_movements(
        self,
        db: Session,
        filters: ExportFilter
    ) -> List[MovementItem]:
        """
        Get export movements (normal mode - grouped by invoice).
        
        Args:
            db: Database session
            filters: Filter criteria
            
        Returns:
            List of movement items grouped by invoice
        """
        try:
            logger.info(f"Fetching export 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
            # Note: discharge_clause not used in aggregated query for exports
            sql = text(ExportQueries.build_aggregated_query(filters.database_name, where_clause))
            results = db.execute(sql).fetchall()
            logger.info(f"Found {len(results)} export invoices")
            
            movements = []
            
            for row in results:
                factura = row[0]  # C1 - FacturaExpo
                tipo_mov = row[14]  # C34 - TipoFactura
                
                # Skip cancelled if not included
                if not filters.include_cancelled and row[3] != 'AC':  # C6 - Estatus
                    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[23])  # total_me from SUM aggregation
                total_mn = to_float(row[24])  # total_mn from SUM aggregation
                sum_value_usd = to_float(row[26])
                sum_value_mxn = to_float(row[27])
                
                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[7],  # C11 - Fecha_Pago
                    fecha_inicio=row[6],  # C10 - Fecha_Inicio (mapped previously to C9)
                    tipo_pedimento='',  # Not in aggregated query
                    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 pedimento rectification
                rectified_pedimento = DatabaseHelper.get_rectification_pedimento(
                    db,
                    row[1],  # C2 - PedimentoExpo
                    row[25],  # C54 - PedRectifica
                    filters.is_shelter
                )
                
                # Get driver badge
                driver_badge = 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,
                    EsCambioRegimen='N',
                    ValorMPTemp=valor_comercial,
                    ValorComercialMN=valor_comercial,
                    TipoCambio=tipo_cambio,
                    ValorAgre=to_float(row[28]),
                    TipoExpo='EXPO DEF',
                    PedimentoR1=rectified_pedimento,
                    EDocument=row[16],  # C40 - EDocument
                    NumOperacionVU=row[17],  # C41 - NumOperacionVU
                    BaseDeDatos=filters.database_name,
                    NumGafUni=driver_badge,
                    UsuarioCap=row[20],  # C50 - UsuarioCap
                    UsuarioAcr=row[21],  # C51 - UsuarioAct
                    Fecha_Pago=parse_yyyymmdd_date(row[7]),  # C11 - Fecha_Pago
                    NumCaja=row[22],  # C53 - Transporte + NumTrasporte
                    Pedimento18='',  # Not in aggregated query
                    AduanaCru=row[13],  # C33 - Aduana_Cruce
                    Lote=''  # Not in aggregated query
                )
                
                movements.append(movement)
            
            logger.info(f"Successfully processed {len(movements)} export movements")
            return movements
            
        except Exception as e:
            logger.error(f"Error fetching export movements: {e}", exc_info=True)
            raise
    
    def get_movements_detailed(
        self,
        db: Session,
        filters: ExportFilter
    ) -> List[MovementItemDetailed]:
        """
        Get export 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 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)
            
            # Build discharge filter for main query
            discharge_clause = ""
            if filters.discharge_filter == "SiDes":
                discharge_clause = " AND EqiPex.Descarga = 1"
            elif filters.discharge_filter == "NoDes":
                discharge_clause = " AND EqiPex.Descarga = 0"
            
            # Modify main query to include discharge filter
            where_with_discharge = where_clause + discharge_clause
            
            # Execute main query
            sql = text(ExportQueries.build_main_query(filters.database_name, where_with_discharge))
            results = db.execute(sql).fetchall()
            logger.info(f"Found {len(results)} detailed export partidas")
            
            movements = []
            
            for row in results:
                # Skip cancelled if not included
                if not filters.include_cancelled and row[5] == 'NA':  # C6 - Estatus
                    continue
                
                # Get client/supplier information
                proveedor_info = DatabaseHelper.get_client_info(
                    db, filters.database_name, row[13], is_supplier=True  # C14 - Proveedor
                )
                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
                customs_name = DatabaseHelper.get_aduana_seccion_nombre(
                    db, filters.database_name, row[32]  # C33 - Aduana_Cruce
                )
                
                # Calculate exchange rate and value for this partida
                valor_mn, tipo_cambio_final = ExchangeRateCalculator.calculate_exchange_rate_and_value(
                    db=db,
                    db_name=filters.database_name,
                    es_subpartida=row[37],  # C38 - EsSubPartida
                    valor_me=row[36],  # C37 - ValorExpoME
                    valor_mn_direct=row[35],  # C36 - ValorExpoMN
                    fecha_pago=row[10],  # C11 - Fecha_Pago
                    fecha_inicio=row[8],  # C9 - Fecha_Inicio
                    clave_ped=row[55],  # C56 - TIPOPEDIMENTOTRANSPORTEE
                    tipo_cambio_partida=row[47],  # C48 - TipoCambio
                    currency_type=filters.currency_type.value,
                    exchange_rate_type=filters.exchange_rate_type.value,
                    met_trans=met_trans
                )
                
                # Set peso values (material_type is typically 'PT' or 'MP', not just 'P')
                peso_neto_final = row[24] if row[37] != 'S' else 0  # C25 - PesoNeto
                peso_bruto_final = row[25] if row[37] != 'S' else 0  # C26 - PesoBruto
                
                # Get series information
                series_info = DatabaseHelper.get_series_info_export(
                    db, filters.database_name, row[34], row[41], filters.is_shelter
                )
                
                # Get pedimento rectification
                rectified_pedimento = DatabaseHelper.get_rectification_pedimento(
                    db,
                    row[1],  # C2 - PedimentoExpo
                    row[38],  # C39 - PedRectifica
                    filters.is_shelter
                )
                
                # Get driver badge
                driver_badge = self._get_driver_badge(db, filters.database_name, row[0])  # C1 - FacturaExpo
                
                # 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[4],                    # C5  - ClavePed
                    TipoMovTemDef=row[31],              # C34 - TipoFactura
                    EsCambioRegimen='N',
                    Regimen=row[5],                     # C6  - Regime (Shared index with Estatus in this query)
                    Fecha_Inicio=parse_yyyymmdd_date(row[6]),                # C7  - Fecha_Inicio
                    Fecha_Fin=parse_yyyymmdd_date(row[7]),                   # C8  - Fecha_Fin
                    Fecha_Pago=parse_yyyymmdd_date(row[8]),                  # C9  - Fecha_Pago
                    Remesa=row[9],                      # C12 - Remesa
                    TipoCambio=tipo_cambio_final,
                    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[17],                   # C18 - NumParte
                    DescripcionE=StringHelper.remove_commas(row[18]),  # C19 - DescripcionE
                    DescripcionI=StringHelper.remove_commas(row[19]),  # C20 - DescripcionI
                    CantidadIE=row[20],                 # C21 - CantExpo
                    UniMed=row[21],                     # C22 - UnidadMedida
                    ValorComercialMN=valor_mn,
                    PesoNeto=peso_neto_final,
                    PesoBruto=peso_bruto_final,
                    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=customs_name,
                    Advalorem=row[29],                  # C30 - Advalorem
                    TipoExpo='EXPO DEF',
                    PedimentoR1=rectified_pedimento,
                    EDocument=row[39],                  # C40 - EDocument
                    NumOperacionVU=row[40],             # C41 - NumOperacionVU
                    Series=series_info,
                    Marca=StringHelper.clean_text(row[42]),   # C43 - Marca
                    Modelo=StringHelper.clean_text(row[43]),  # C44 - Modelo
                    FraccionAmericana=row[44],          # C45 - FraccionAme
                    ECCN=row[45],                       # C46 - ECCN
                    FechaEmision=parse_yyyymmdd_date(row[48]) if row[48] else None,  # C49 - FechaEmision
                    BaseDeDatos=filters.database_name,
                    NumGafUni=driver_badge,
                    UsuarioCap=row[49],                 # C50 - UsuarioCap
                    UsuarioAcr=row[50],                 # C51 - UsuarioAct
                    Transportista=row[51],              # C52 - Carrier ID (derived from log.transport_id)
                    NumCaja=row[52],                    # C53 - log.transport_id || log.transport_num
                    Pedimento18=row[53],                # C54 - empty
                    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 movements")
            return movements
            
        except Exception as e:
            logger.error(f"Error fetching detailed export movements: {e}", exc_info=True)
            raise
    
    def _build_where_clause(self, filters: ExportFilter) -> str:
        """
        Build WHERE clause for export query.
        
        IMPORTANT: Returns conditions WITHOUT the WHERE keyword (already in base query)
        AC (Active) = status = true
        NA (Not Applicable/Deactivated) = status = false
        """
        conditions = []
        
        # STRICT SEPARATION: Only exports
        conditions.append("ih.operation_type = 'exp'")
        
        # Exclude REP (export reports)
        conditions.append("ih.invoice_type NOT IN ('REP')")
        
        # GOLDEN RULE: If movement_type is ALL, only filter by operation_type
        if filters.movement_type.value != "ALL":
            # Filter by invoice type for exports
            conditions.append("ih.invoice_type IN ('EXP', 'EXREP')")
        
        # CRITICAL VALIDATION: AC/NA status filter
        # If include_cancelled is False (checkbox unchecked), only show AC invoices
        # AC (Active) = status = true
        # NA (Not Applicable/Deactivated) = status = false
        if not filters.include_cancelled:
            conditions.append("ih.status = true")
            logger.debug("Filtering only active invoices (status = true)")
        else:
            logger.debug("Including cancelled invoices (include_cancelled = true)")
        
        # Date range
        date_field = "ih.invoice_date" if filters.range_type.value == "FF" else "pd.payment_date"
        conditions.append(f"{date_field} >= TO_DATE('{filters.start_date}', 'YYYYMMDD')")
        conditions.append(f"{date_field} <= TO_DATE('{filters.end_date}', 'YYYYMMDD')")
        
        # Optional filters
        if filters.provider:
            conditions.append(f"cmp.provider_id IN (SELECT id FROM a76.clients_and_providers WHERE name = '{filters.provider}')")
        if filters.buyer:
            conditions.append(f"cmp.sold_to_id IN (SELECT id FROM a76.clients_and_providers WHERE name = '{filters.buyer}')")
        if filters.pedimento_code:
            conditions.append(f"ped.pedimento_code = '{filters.pedimento_code}'")
        
        return " AND ".join(conditions)

    def _build_discharge_clause(self, discharge_filter: str) -> str:
        """Build discharge filter clause for totals query."""
        if discharge_filter == "SiDes":
            return " AND il.is_discharged = true"
        elif discharge_filter == "NoDes":
            return " AND il.is_discharged = false"
        return ""
    
    def _calculate_totals(
        self,
        db: Session,
        db_name: str,
        consecutivo: int,
        discharge_clause: str
    ) -> tuple:
        """Calculate total values for an export invoice."""
        try:
            sql = text(ExportQueries.build_totals_query(db_name, 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 export totals for consecutivo {consecutivo}: {e}")
            return (0, 0)
    
    def _get_driver_badge(self, db: Session, db_name: str, factura: str) -> str:
        """Get driver's unique badge number for an export invoice."""
        if not factura:
            return None
        
        try:
            sql = text(ExportQueries.build_driver_badge_query(db_name))
            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 export invoice {factura}: {e}")
            return None
