"""
Repair import service - handles IMPRE 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 ImportRepairFilter, MovementItem, MovementItemDetailed

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

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 RepairImportService:
    """Service for handling repair import movements (IMPRE)."""
    
    def get_movements(
        self,
        db: Session,
        filters: "ImportRepairFilter"
    ) -> List["MovementItem"]:
        """
        Get repair 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 repair 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(RepairImportQueries.build_aggregated_query(
                filters.database_name, 
                where_clause, 
                filters.discharge_filter.value
            ))
            results = db.execute(sql).fetchall()
            logger.info(f"Found {len(results)} repair import invoices")
            
            movements = []
            
            for row in results:
                factura = row[0]  # C2 - FacturaImpoRep
                estatus = row[3]  # C5 - 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[14]  # C30 - Consecutivo
                
                # 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[24])  # total_me from SUM aggregation
                total_mn = to_float(row[25])  # total_mn from SUM aggregation
                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=to_float(row[17]),  # C40 - TipoCambio
                    fecha_pago=row[6],  # C9 - Fecha_Pago
                    fecha_inicio='',  # Not available in aggregated query
                    tipo_pedimento=row[23],  # C47 - pedimento_code (used as 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
                pedimento_r1 = DatabaseHelper.get_rectification_pedimento(
                    db,
                    row[1],    # C3 - PedimentoImpoRep
                    row[26],   # C48 - 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],  # C3 - PedimentoImpoRep
                    FechaFactura=parse_yyyymmdd_date(row[2]),  # C4 - FechaFactura
                    Estatus=row[3],  # C5 - Estatus
                    ClavePed=row[4],  # C6 - ClavePed
                    TipoMovTemDef='IMPRE',
                    EsCambioRegimen='N',
                    ValorMPTemp=valor_comercial,
                    ValorComercialMN=valor_comercial,
                    TipoCambio=tipo_cambio,
                    ValorAgre=0.0,
                    TipoExpo='',
                    PedimentoR1=pedimento_r1,
                    EDocument=row[15],  # C33 - EDocument
                    NumOperacionVU=row[16],  # C34 - NumOperacionVU
                    BaseDeDatos=filters.database_name,
                    NumGafUni=num_gaf_uni,
                    UsuarioCap=row[19],  # C42 - UsuarioCap
                    UsuarioAcr=row[20],  # C43 - UsuarioAct
                    Fecha_Pago=parse_yyyymmdd_date(row[6]),  # C9 - Fecha_Pago
                    NumCaja=row[22],  # C45 - Transport num
                    Pedimento18='',  # Not in aggregated query
                    AduanaCru=row[13],  # C29 - customs_office
                    Lote=''  # Not in aggregated query
                )
                
                movements.append(movement)
            
            logger.info(f"Successfully processed {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 get_movements_detailed(
        self,
        db: Session,
        filters: "ImportRepairFilter"
    ) -> List["MovementItemDetailed"]:
        """
        Get repair 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 repair import movements with filters: {filters.model_dump()}")
            
            # Get MetTrans configuration
            met_trans = ConfigHelper.get_met_trans_config()
            
            # Build WHERE clause with discharge filter
            where_clause = self._build_where_clause(filters)
            
            # Add discharge filter to WHERE clause
            discharge_clause = ""
            if filters.discharge_filter.value == "SiDes":
                discharge_clause = " AND RepPim.Descarga = 1"
            elif filters.discharge_filter.value == "NoDes":
                discharge_clause = " AND RepPim.Descarga = 0"
            
            where_with_discharge = where_clause + discharge_clause
            
            # Execute main query
            sql = text(RepairImportQueries.build_main_query(filters.database_name, where_with_discharge))
            results = db.execute(sql).fetchall()
            logger.info(f"Found {len(results)} detailed repair import partidas")
            
            movements = []
            
            for row in results:
                # Skip cancelled if not included
                if not filters.include_cancelled and row[4] != 'AC':  # C5 - Estatus
                    continue
                
                # Get all detailed information
                proveedor_info = DatabaseHelper.get_client_info(
                    db, filters.database_name, row[11], is_supplier=True
                )
                vendido_info = DatabaseHelper.get_client_info(
                    db, filters.database_name, row[12], is_supplier=False
                )
                agente_info = DatabaseHelper.get_customs_agent_info(
                    db, filters.database_name, row[13]
                )
                aduana_nombre = DatabaseHelper.get_aduana_seccion_nombre(
                    db, filters.database_name, row[28]
                )
                
                # 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[30],  # 'P' or 'S'
                    valor_me=row[21],
                    valor_mn_direct=row[20],
                    fecha_pago=row[8],
                    fecha_inicio=row[7],
                    clave_ped=row[45],
                    tipo_cambio_partida=row[38],
                    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[31] == 'P':
                    peso_neto = float(row[22]) if row[22] else 0.0
                    peso_bruto = float(row[23]) if row[23] else 0.0
                else:
                    peso_neto = 0.0
                    peso_bruto = 0.0
                
                series_info = DatabaseHelper.get_series_info(
                    db, filters.database_name, row[29], row[0], filters.is_shelter
                )
                
                simbolo_ex = None
                if row[15]:
                    simbolo_ex = DatabaseHelper.get_part_export_symbol(
                        db, filters.database_name, row[15], filters.is_shelter
                    )
                
                pedimento_r1 = DatabaseHelper.get_rectification_pedimento(
                    db, row[2], row[32], filters.is_shelter
                )
                
                num_gaf_uni = DatabaseHelper.get_driver_badge(
                    db, filters.database_name, row[1]
                )
                
                movement = MovementItemDetailed(
                    Linea=row[0],
                    Factura=row[1],
                    Pedimento=row[2],
                    FechaFactura=row[3],
                    Estatus=row[4],
                    ClavePed=row[5],
                    TipoMovTemDef='IMPRE',
                    EsCambioRegimen='N',
                    Regimen=row[6],
                    Fecha_Inicio=parse_yyyymmdd_date(row[7]),
                    Fecha_Fin=parse_yyyymmdd_date(row[8]),
                    Fecha_Pago=parse_yyyymmdd_date(row[9]),
                    Remesa=row[10],
                    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[15],
                    DescripcionE=StringHelper.clean_text(row[16]),
                    DescripcionI=StringHelper.clean_text(row[17]),
                    CantidadIE=float(row[18]) if row[18] else 0.0,
                    UniMed=row[19],
                    ValorComercialMN=valor_comercial,
                    TipoCambio=tipo_cambio,
                    PesoNeto=peso_neto,
                    PesoBruto=peso_bruto,
                    OrdenCompraVenta=row[24],
                    FraccionArancelaria=row[25],
                    Preferencia=row[26],
                    Sector=row[27],
                    PaisOrigen=row[28],
                    Aduana=aduana_nombre,
                    Advalorem='',
                    TipoExpo='',
                    PedimentoR1=pedimento_r1,
                    EDocument=row[33],
                    NumOperacionVU=row[34],
                    Series=series_info,
                    Marca=StringHelper.clean_text(row[35]),
                    Modelo=StringHelper.clean_text(row[36]),
                    FraccionAmericana=row[37],
                    ECCN=row[38],
                    SimboloEx=simbolo_ex,
                    FechaEmision=parse_yyyymmdd_date(row[40]) if row[40] else None,  # C41 - FechaEmision
                    BaseDeDatos=filters.database_name,
                    NumGafUni=num_gaf_uni,
                    UsuarioCap=row[41],
                    UsuarioAcr=row[42],
                    Transportista=row[43],
                    NumCaja=row[44],
                    Pedimento18=row[45],
                    AduanaCru=row[29],
                    Lote=row[54] if len(row) > 54 else ''  # Not in query but mapped safely
                )
                
                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 _build_where_clause(self, filters: "ImportRepairFilter") -> str:
        """Build WHERE clause for repair imports query."""
        where_conditions = []
        
        # STRICT SEPARATION: Only imports for repair
        where_conditions.append("ih.operation_type = 'imp'")
        # Repair imports are identified by cross-references, not invoice_type
        where_conditions.append("COALESCE(cmp.is_regime_change, false) = false")
        
        # 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, discharge_filter: str) -> tuple:
        """Calculate totals for main partidas with discharge filter."""
        # Build discharge clause
        # Note: is_discharged field not yet migrated to PostgreSQL schema
        discharge_clause = ""
        # Temporarily disabled until schema migration:
        # if discharge_filter == "SiDes":
        #     discharge_clause = " AND il.is_discharged = true"
        # elif discharge_filter == "NoDes":
        #     discharge_clause = " AND il.is_discharged = false"
        
        sql = text(f"""
            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 il.is_subitem = false
                {discharge_clause}
        """)
        
        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
