"""
Temporary import service - handles IMTEM 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 ImportTemporaryFilter, MovementItem, MovementItemDetailed

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

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 TemporaryImportService:
    """Service for handling temporary import movements (IMTEM)."""
    
    def get_movements(
        self,
        db: Session,
        filters: "ImportTemporaryFilter"
    ) -> List["MovementItem"]:
        """
        Get temporary 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 temporary 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 (GROUP BY with totals)
            sql = text(TemporaryImportQueries.build_aggregated_query(filters.database_name, where_clause))
            results = db.execute(sql).fetchall()
            logger.info(f"Found {len(results)} temporary import invoices")
            
            movements = []
            
            for row in results:
                factura = row[0]  # C1 - FacturaImpo
                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
                
                consecutivo = row[15]  # C39 - Consecutivo
                
                # Helper to convert empty strings to None
                def none_if_empty(val):
                    return None if val == '' else val

                # 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)
                # Correct indices based on TemporaryImportQueries.build_aggregated_query
                total_me = to_float(row[28])  # total_me (index 28)
                total_mn = to_float(row[29])  # total_mn (index 29)
                valor_comercial_mn = to_float(row[30])  # valor_comercial_mn from item_line_financials
                valor_mp_temp_mn = to_float(row[31])   # valor_mp_temp_mn from item_line_financials
                valor_agre_mn = to_float(row[32])       # valor_agre_mn from item_line_financials
                valor_mp_temp_usd = to_float(row[33])   # valor_mp_temp_usd from item_line_financials
                sum_value_usd = to_float(row[34])       # sum_value_usd from item_line_financials (line item sum avoids zero-value header bug)
                
                # Replace zero values with the computed sums
                total_me = sum_value_usd if sum_value_usd > 0 else total_me
                total_mn = valor_comercial_mn if valor_comercial_mn > 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[19]),  # C50 - TipoCambio
                    fecha_pago=row[8],  # C13 - Fecha_Pago
                    fecha_inicio=row[6],  # C11 - Fecha_Inicio
                    tipo_pedimento=row[4],  # C5 - ClavePed (Using correct index)
                    currency_type=filters.currency_type.value,
                    exchange_rate_type=filters.exchange_rate_type.value,
                    is_shelter=filters.is_shelter,
                    use_transport_method=False,  # Not used for temporary imports
                    met_trans=met_trans
                )
                
                # Get pedimento rectification
                pedimento_r1 = DatabaseHelper.get_rectification_pedimento(
                    db,
                    row[1],  # C2 - PedimentoImpo
                    row[16],  # C41 - PedRectifica
                    filters.is_shelter
                )
                
                # Get driver badge
                num_gaf_uni = DatabaseHelper.get_driver_badge(
                    db, filters.database_name, factura
                )
                    
                # Calculate exchange rate for MPTemp explicitly decoupled from Valor Comercial
                valor_mp_temp_raw, _ = ExchangeRateCalculator.calculate_for_aggregated(
                    db=db,
                    db_name=filters.database_name,
                    valor_me=to_float(row[33]),  # sum_value_temp_usd
                    valor_mn=to_float(row[31]),  # sum_value_temp_mxn
                    tipo_cambio_db=to_float(row[19]),
                    fecha_pago=row[8],
                    fecha_inicio=row[6],
                    tipo_pedimento=row[4],
                    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
                )
                valor_mp_temp = float(valor_mp_temp_raw)
                
                # Build movement item
                movement = MovementItem(
                    Factura=factura,
                    Pedimento=row[1],  # C2 - PedimentoImpo
                    FechaFactura=parse_yyyymmdd_date(row[2]),  # C3 - FechaFactura
                    Estatus=row[3],  # C4 - Estatus
                    ClavePed=row[4],  # C5 - ClavePed
                    TipoMovTemDef='IMTEM',
                    EsCambioRegimen='N',
                    ValorMPTemp=valor_mp_temp,
                    ValorComercialMN=valor_comercial,
                    TipoCambio=tipo_cambio,
                    ValorAgre=valor_agre_mn,
                    TipoExpo='',
                    PedimentoR1=pedimento_r1,
                    EDocument=row[17],  # C42 - EDocument
                    NumOperacionVU=row[18],  # C43 - NumOperacionVU
                    BaseDeDatos=filters.database_name,
                    NumGafUni=num_gaf_uni,
                    UsuarioCap=row[21],  # C52 - UsuarioCap
                    UsuarioAcr=row[22],  # C53 - UsuarioAct
                    Fecha_Pago=parse_yyyymmdd_date(none_if_empty(row[8])),  # C13 - Fecha_Pago
                    NumCaja=row[24],  # C55 - transport_num || license_plate (index 24)
                    Pedimento18=row[25],  # C56 - '' empty (index 25)
                    AduanaCru=row[14],  # C38 - Aduana_Cruce (index 14)
                    Lote=row[26]  # C57 - '' empty (index 26)
                )
                
                movements.append(movement)
            
            logger.info(f"Successfully processed {len(movements)} temporary import movements")
            return movements
            
        except Exception as e:
            logger.error(f"Error fetching temporary import movements: {e}", exc_info=True)
            raise
    
    def get_movements_detailed(
        self,
        db: Session,
        filters: "ImportTemporaryFilter"
    ) -> List["MovementItemDetailed"]:
        """
        Get temporary 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 temporary 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(TemporaryImportQueries.build_main_query(filters.database_name, where_clause))
            results = db.execute(sql).fetchall()
            logger.info(f"Found {len(results)} detailed temporary import partidas")
            
            movements = []
            
            for row in results:
                # Skip cancelled if not included
                if not filters.include_cancelled and row[3] != 'AC':  # C4 - Estatus
                    continue
                
                # Provider and client names now come directly from query (C8, C9)
                # No need for additional database lookups
                logger.info(f"Processing invoice {row[0]}: Proveedor='{row[7]}', VendidoA='{row[8]}', CantidadIE={row[22]}, DescripcionE='{row[20][:50] if row[20] else None}'")
                
                # Get customs agent information
                agente_info = DatabaseHelper.get_customs_agent_info(
                    db, filters.database_name, row[17]  # C18 - AAduanal
                )
                
                # Get provider and client details including RFC and TaxID
                provider_info = DatabaseHelper.get_client_info(
                    db, filters.database_name, row[15], is_supplier=True
                ) if row[15] else {}
                
                client_info = DatabaseHelper.get_client_info(
                    db, filters.database_name, row[16], is_supplier=False
                ) if row[16] else {}
                
                # Get customs section name
                aduana_nombre = DatabaseHelper.get_aduana_seccion_nombre(
                    db, filters.database_name, row[37]  # C38 - Aduana_Cruce
                )
                
                # Calculate values (only for main partidas 'P', not subpartidas 'S')
                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],  # C11 - Fecha_Inicio
                    clave_ped=row[57],  # 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
                )
                
                valor_mp_temp_raw, _ = ExchangeRateCalculator.calculate_exchange_rate_and_value(
                    db=db,
                    db_name=filters.database_name,
                    es_subpartida=row[39],  # C40 - EsSubPartida
                    valor_me=float(row[59]) if row[59] else 0.0,
                    valor_mn_direct=float(row[58]) if row[58] else 0.0,
                    fecha_pago=row[12],  # C13 - Fecha_Pago
                    fecha_inicio=row[10],  # C11 - Fecha_Inicio
                    clave_ped=row[57],  # 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
                )
                valor_mp_temp_mn = float(valor_mp_temp_raw)
                
                # Assign the properly converted commercial value directly
                valor_comercial_mn = float(valor_comercial)
                
                # Set peso values based on subpartida flag (allow 'PT', 'MP', etc. but block 'S')
                if row[39] != 'S':  # 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
                
                # Get series information
                series_info = DatabaseHelper.get_series_info(
                    db, filters.database_name, row[38], row[43], filters.is_shelter  # C39, C44
                )
                
                # Get part export symbol
                simbolo_ex = None
                if row[48]:  # C49 - NumParte
                    simbolo_ex = DatabaseHelper.get_part_export_symbol(
                        db, filters.database_name, row[48], filters.is_shelter
                    )
                
                # Get pedimento rectification
                pedimento_r1 = DatabaseHelper.get_rectification_pedimento(
                    db,
                    row[1],  # C2 - PedimentoImpo
                    row[40],  # C41 - PedRectifica
                    filters.is_shelter
                )
                
                # Get driver badge
                num_gaf_uni = DatabaseHelper.get_driver_badge(
                    db, filters.database_name, row[0]  # C1 - FacturaImpo
                )
                
                # Helper to convert empty strings to None for dates
                def none_if_empty(val):
                    if val == '' or val is None:
                        return None
                    return val
                
                # Helper to convert to string (for Remesa, Advalorem)
                def to_str(val):
                    if val is None or val == '':
                        return None
                    if isinstance(val, bool):
                        return 'P' if val else 'S'  # Convert bool to P/S for Advalorem
                    return str(val)
                
                logger.error(f"DEBUG ROW: NumParte(17)='{row[17]}', Sector(30)='{row[30]}', Partida {row[43]}, Original Part ID(46)='{row[46]}'")
                
                # Build detailed movement item
                movement = MovementItemDetailed(
                    Linea=row[43],  # C44 - LineaImpo
                    Factura=row[0],  # C1 - FacturaImpo
                    Pedimento=row[1],  # C2 - PedimentoImpo
                    FechaFactura=parse_yyyymmdd_date(row[2]),  # C3 - FechaFactura (convert to datetime)
                    Estatus=row[3],  # C4 - Estatus
                    ClavePed=row[4],  # C5 - ClavePed
                    TipoMovTemDef='IMTEM',
                    EsCambioRegimen='N',
                    Regimen=row[9],  # C10 - Regimen
                    Fecha_Inicio=parse_yyyymmdd_date(row[10]),  # C11 - Fecha_Inicio
                    Fecha_Fin=parse_yyyymmdd_date(row[11]),  # C12 - Fecha_Fin
                    Fecha_Pago=parse_yyyymmdd_date(row[12]),  # C13 - Fecha_Pago
                    Remesa=to_str(row[13]),  # C14 - Remesa
                    Proveedor=row[7],  # C8 - Provider name (from JOIN)
                    RFCProveedor=provider_info.get('rfc'),
                    ProveedorTaxID=provider_info.get('tax_id'),
                    VendidoA=row[8],  # C9 - Client name (from JOIN)
                    VendidoARFC=client_info.get('rfc'),
                    VendidoATaxID=client_info.get('tax_id'),
                    AgenteAduanal=agente_info.get('name'),
                    Patente=agente_info.get('license'),
                    NumParte=row[48],  # C49 - Part Number (from JOIN)
                    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_mn,
                    ValorMPTemp=valor_mp_temp_mn,
                    TipoCambio=tipo_cambio,
                    PesoNeto=peso_neto,
                    PesoBruto=peso_bruto,
                    OrdenCompraVenta=row[30],  # C31 - OrdenCompra
                    FraccionArancelaria=row[31],  # C32 - Fraccion
                    Preferencia=row[32],  # C33 - TipoFraccion
                    Sector=row[34],  # C35 - Sector (from COALESCE)
                    PaisOrigen=row[36],  # C37 - PaisOrigen
                    Aduana=aduana_nombre,
                    Advalorem=to_str(row[39]),  # C40 - EsSubPartida (convert bool to str)
                    TipoExpo='',
                    PedimentoR1=pedimento_r1,
                    EDocument=row[41],  # C42 - EDocument
                    NumOperacionVU=row[42],  # C43 - NumOperacionVU
                    Series=series_info,
                    Marca=StringHelper.clean_text(row[44]),  # C45
                    Modelo=StringHelper.clean_text(row[45]),  # C46
                    FraccionAmericana=row[46],  # C47 - FraccionAme
                    ECCN=row[47],  # C48 - ECCN
                    SimboloEx=simbolo_ex,
                    FechaEmision=parse_yyyymmdd_date(row[50]),  # C51 - FechaEmision
                    BaseDeDatos=filters.database_name,
                    NumGafUni=num_gaf_uni,
                    UsuarioCap=row[51],  # C52 - UsuarioCap
                    UsuarioAcr=row[52],  # C53 - UsuarioAct
                    Transportista=row[53],  # C54 - Carrier ID
                    NumCaja=row[54],  # C55 - Transport num
                    Pedimento18=row[55],  # C56 - Pedimento18
                    AduanaCru=row[37],  # C38 - Aduana_Cruce
                    Lote=row[56]  # C57 - LOTE
                )
                
                movements.append(movement)
            
            logger.info(f"Successfully processed {len(movements)} detailed temporary import movements")
            return movements
            
        except Exception as e:
            logger.error(f"Error fetching detailed temporary import movements: {e}", exc_info=True)
            raise
    
    def _build_where_clause(self, filters: "ImportTemporaryFilter") -> str:
        """Build WHERE clause for temporary imports query using PostgreSQL tables."""
        where_conditions = []
        
        # STRICT SEPARATION: Only temporary imports
        where_conditions.append("ih.operation_type = 'imp'")
        # ALWAYS filter by specific invoice_type to avoid duplication with Definitive service
        where_conditions.append("ih.invoice_type IN ('TEM', 'MATTEM')")
        
        # 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) if where_conditions else "1=1"
    
    def _calculate_totals(self, db: Session, db_name: str, consecutivo: int) -> tuple:
        """Calculate totals for main partidas only using PostgreSQL.
        
        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
            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
