"""
Database query helpers for invoice movements.
"""

import logging
from sqlalchemy import text
from sqlalchemy.orm import Session
from typing import Optional, Dict

logger = logging.getLogger(__name__)


class DatabaseHelper:
    """Helper for common database operations."""
    
    @staticmethod
    def get_database_name(db: Session) -> Optional[str]:
        """
        Get the current database name from the session.
        
        Returns:
            Database name or None if not found
        """
        try:
            result = db.execute(text("SELECT current_database()")).fetchone()
            return result[0] if result else None
        except Exception as e:
            logger.error(f"Error getting database name: {e}")
            return None
    
    @staticmethod
    def get_exchange_rate(
        db: Session, 
        db_name: str, 
        fecha,
        is_shelter: bool = False,
        raise_on_missing: bool = False,
        pedimento_number: Optional[str] = None
    ) -> Optional[float]:
        """
        Get exchange rate for the given date from exchange_rate table.
        
        Args:
            db: Database session
            db_name: Legacy database name (kept for compatibility, not used)
            fecha: Date for exchange rate lookup
            is_shelter: Shelter company flag (when True and rate not found, raises detailed error)
            raise_on_missing: If True, raises ValueError when rate not found
            pedimento_number: Pedimento number for error messages
            
        Returns:
            Exchange rate as float, or None if not found
            
        Raises:
            ValueError: When is_shelter=True and exchange rate not found
        """
        if not fecha:
            return None
        
        try:
            # TODO: Verify exchange_rate table structure and column names
            sql_tc = text("""
                SELECT rate 
                FROM a76.exchange_rate 
                WHERE rate_date = :fecha 
                ORDER BY rate_date DESC
                LIMIT 1
            """)
            res = db.execute(sql_tc, {"fecha": fecha}).fetchone()
            if res and res[0]:
                return float(res[0])
            else:
                # Clarion logic: For Shelter operations with FP, missing exchange rate is an error
                if is_shelter and raise_on_missing:
                    fecha_str = fecha.strftime('%d/%m/%Y') if hasattr(fecha, 'strftime') else str(fecha)
                    ped_info = f" del Pedimento: {pedimento_number}" if pedimento_number else ""
                    raise ValueError(
                        f"Falta el tipo de cambio del día {fecha_str}. "
                        f"Por favor regístralo en el catálogo de Tipos de Cambio."
                    )
                logger.warning(f"Exchange rate not found for date {fecha}")
                return None
        except ValueError:
            raise  # Re-raise validation errors
        except Exception as e:
            logger.error(f"Error fetching exchange rate for date {fecha}: {e}")
            return None
    
    @staticmethod
    def get_client_info(
        db: Session, 
        db_name: str, 
        client_code: str, 
        is_supplier: bool = True
    ) -> Dict[str, Optional[str]]:
        """
        Get client or supplier information (name, RFC, TaxID).
        
        Args:
            db: Database session
            db_name: Database name (kept for compatibility, not used)
            client_code: Client/supplier code
            is_supplier: True for suppliers, False for clients
            
        Returns:
            Dict with 'name', 'rfc', 'tax_id' keys
        """
        if not client_code:
            return {"name": None, "rfc": None, "tax_id": None}
        
        # In our schema, enum values are lowercase: 'client', 'provider', 'both'
        client_type = 'provider' if is_supplier else 'client'
        
        try:
            sql = text("""
                SELECT cp.name, cp.rfc
                FROM a76.clients_and_providers cp
                WHERE cp.id = :client_code
                  AND (cp.client_or_provider = :client_type OR cp.client_or_provider = 'both')
            """)
            result = db.execute(sql, {"client_code": client_code, "client_type": client_type}).fetchone()
            
            if result:
                # Unified identifier: cp.rfc contains either RFC (national) or TAX-ID (foreign)
                return {
                    "name": result[0],
                    "rfc": result[1],
                    "tax_id": result[1],
                }
            else:
                logger.debug(f"Client {client_code} not found as {client_type}")
                return {"name": None, "rfc": None, "tax_id": None}
        except Exception as e:
            logger.error(f"Error fetching client info for {client_code}: {e}")
            raise
    
    @staticmethod
    def get_customs_agent_info(
        db: Session, 
        db_name: str, 
        agent_code: str
    ) -> Dict[str, Optional[str]]:
        """
        Get customs agent information (name, license).
        
        Args:
            db: Database session
            db_name: Database name (kept for compatibility, not used)
            agent_code: Customs agent code
            
        Returns:
            Dict with 'name', 'license' keys
        """
        if not agent_code:
            return {"name": None, "license": None}
        
        try:
            sql = text("""
                SELECT name, license 
                FROM a76.customs_brokers 
                WHERE id = :agent_code
                LIMIT 1
            """)
            result = db.execute(sql, {"agent_code": agent_code}).fetchone()
            
            if result:
                return {
                    "name": result[0],
                    "license": result[1]
                }
            else:
                logger.debug(f"Customs agent {agent_code} not found")
                return {"name": None, "license": None}
        except Exception as e:
            logger.error(f"Error fetching customs agent info for {agent_code}: {e}")
            raise
    
    @staticmethod
    def get_aduana_seccion_nombre(
        db: Session, 
        db_name: str, 
        aduana_seccion: str
    ) -> Optional[str]:
        """
        Get customs section name.
        
        Args:
            db: Database session
            db_name: Database name
            aduana_seccion: Customs section code
            
        Returns:
            Customs section name or None
        """
        if not aduana_seccion:
            return None
            
        try:
            query = text("""
                SELECT section_name 
                FROM public.customs_sections 
                WHERE customs_code = :code
            """)
            result = db.execute(query, {"code": aduana_seccion}).fetchone()
            return result[0] if result else None
        except Exception as e:
            logger.error(f"Error fetching customs section name: {e}")
            raise
    
    @staticmethod
    def get_series_info(
        db: Session, 
        db_name: str, 
        invoice_id: int, 
        linea: str, 
        is_shelter: bool
    ) -> Optional[str]:
        """
        Get series information for import items.
        
        Args:
            db: Database session
            db_name: Legacy database name (not used in PostgreSQL)
            invoice_id: Invoice header ID
            linea: Line number
            is_shelter: Shelter flag (not used)
            
        Returns:
            Formatted series string or None
        """
        if not invoice_id or not linea:
            return None
            
        try:
            query = text("""
                SELECT serial_numbers, model, brand
                FROM a76.item_line_series ils
                INNER JOIN a76.item_lines il ON ils.line_item_id = il.id
                WHERE il.invoice_id = :invoice_id 
                AND il.line_number = :linea
                ORDER BY ils.id
                LIMIT 1
            """)
            result = db.execute(query, {
                "invoice_id": invoice_id,
                "linea": linea
            }).fetchone()
            
            if result:
                serial_numbers, model, brand = result
                parts = []
                if serial_numbers:
                    parts.append(serial_numbers)
                if model:
                    parts.append(model)
                if brand:
                    parts.append(brand)
                return " / ".join(parts) if parts else None
            return None
        except Exception as e:
            logger.error(f"Error fetching series info: {e}")
            raise
    
    @staticmethod
    def get_series_info_export(
        db: Session, 
        db_name: str, 
        invoice_id: int, 
        linea: str, 
        is_shelter: bool
    ) -> Optional[str]:
        """
        Get series information for export items.
        
        Args:
            db: Database session
            db_name: Legacy database name
            invoice_id: Invoice header ID
            linea: LineaExpo value
            is_shelter: Shelter flag
            
        Returns:
            Formatted series string or None
        """
        if not invoice_id or not linea:
            return None
            
        try:
            # Note: Postgres items table calls it expo_brad (typo in DB schema)
            # ItemLineSeries FK is line_item_id, not item_line_id
            query = text("""
                SELECT serial_numbers, model, expo_brad
                FROM a76.item_line_series ils
                INNER JOIN a76.item_lines il ON ils.line_item_id = il.id
                WHERE il.invoice_id = :invoice_id 
                AND il.line_number = :linea
                ORDER BY ils.id
                LIMIT 1
            """)
            result = db.execute(query, {
                "invoice_id": invoice_id,
                "linea": linea
            }).fetchone()
            
            if result:
                serial_numbers, model, expo_brand = result
                parts = []
                if serial_numbers:
                    parts.append(serial_numbers)
                if model:
                    parts.append(model)
                if expo_brand:
                    parts.append(expo_brand)
                return " | ".join(parts) if parts else None
            return None
        except Exception as e:
            logger.error(f"Error fetching export series info: {e}")
            raise
    
    @staticmethod
    def get_rectification_pedimento(
        db: Session,
        pedimento: str, 
        ped_rectifica: Optional[str],
        is_shelter: bool = False
    ) -> Optional[str]:
        """
        Get final pedimento rectification number following the chain recursively.
        
        Clarion logic:
        - IF Loc:OpcionShelter = 1 THEN: use direct field value (PedRectifica)
        - ELSE: call BuscarRectificacion() - follows rectification chain recursively
        
        BuscarRectificacion follows the chain:
        Example: A1 -> A2 -> A3 -> A4 (returns A4, the final rectification)
        
        Args:
            db: Database session
            pedimento: Original pedimento number
            ped_rectifica: Initial rectification pedimento from database field
                           (already resolved from pedimento_rectification_origin JOIN in query)
            is_shelter: Shelter company flag
            
        Returns:
            Final rectification pedimento number in the chain, or None/empty if no rectification
        """
        if is_shelter:
            # Shelter: use direct value from PedRectifica field
            result = ped_rectifica
        else:
            # Non-Shelter: implement BuscarRectificacion logic
            result = DatabaseHelper._buscar_rectificacion(db, pedimento, ped_rectifica)
        return result
    
    @staticmethod
    def _buscar_rectificacion(
        db: Session,
        pedimento_orig: str,
        ped_rec: Optional[str]
    ) -> Optional[str]:
        """
        BUSCA ULTIMO PEDIMENTO DE RECTIFICACION
        Returns the rectification origin pedimento string already resolved by the query
        builder's JOIN on pedimento_rectification_origin.
        
        Args:
            db: Database session
            pedimento_orig: Original pedimento number (e.g. "1234567")
            ped_rec: Rectification pedimento origin string already computed by the SQL JOIN
                     (e.g. "25-470-8000-1234567")
            
        Returns:
            The rectification origin string, or empty string if none.
        """
        if not ped_rec:
            return ''
        
        # The ped_rec value already comes from the JOIN on pedimento_rectification_origin
        # in the query builder, so it is the directly stored origin pedimento.
        # Return it directly without any further recursive DB lookup.
        return ped_rec
    
    @staticmethod
    def _busca_pedimento_r1(
        db: Session,
        pedimento: str,
        visited: set
    ) -> Optional[str]:
        """
        BUSCA_PEDIMENTO_R1 ROUTINE - Recursive search for final rectification pedimento
        using pedimento_rectification_origin table.
        
        Args:
            db: Database session
            pedimento: Current pedimento number to check
            visited: Set of already visited pedimentos (prevents infinite loops)
            
        Returns:
            Final pedimento in chain, or None if circular reference detected
        """
        if pedimento in visited:
            # Circular reference detected (ERRORCODE = 30 equivalent)
            logger.warning(f"Circular reference detected in rectification chain: {pedimento}")
            return None
        
        try:
            # Query pedimento_rectification_origin for the next pedimento in the chain.
            # NOTE: The a76.pedimentos table does NOT have a ped_rectifica column.
            # Rectification data lives in pedimento_rectification_origin.
            sql = text("""
                SELECT 
                    pro.original_pedimento_year || '-' || pro.original_customs_office || 
                    '-' || pro.original_license || '-' || pro.original_pedimento_number AS ped_origen
                FROM a76.pedimento_rectification_origin pro
                INNER JOIN a76.pedimentos ped ON ped.id = pro.pedimento_id
                WHERE ped.pedimento_number = :pedimento
                  AND pro.deleted_at IS NULL
                LIMIT 1
            """)
            result = db.execute(sql, {"pedimento": pedimento}).fetchone()
            
            if result and result[0] and result[0].replace('-', '').strip():
                ped_rectifica_next = result[0]
                
                # Add current pedimento to visited set
                visited.add(pedimento)
                
                # Recurse with next rectification origin
                final_ped = DatabaseHelper._busca_pedimento_r1(
                    db, ped_rectifica_next, visited
                )
                
                return final_ped if final_ped else pedimento
            else:
                # No more rectifications, this is the final pedimento
                return pedimento
                
        except Exception as e:
            logger.error(f"Error fetching rectification origin for pedimento {pedimento}: {e}")
            return None
    
    @staticmethod
    def get_driver_badge(
        db: Session,
        db_name: str,
        factura: str
    ) -> Optional[str]:
        """
        Get driver unique badge number (NUMGAFETEUNICO) for invoice.
        
        Clarion query:
        SELECT NUMGAFETEUNICO FROM GConductor 
        LEFT JOIN QFacImp ON QFacImp.CONDUCTOR = GConductor.CONDUCTOR 
        WHERE FacturaImpo = '<factura>'
        
        Modern schema:
        - invoice_header has invoice_number
        - invoice_logistics links to invoice via invoice_id and has driver_name
        - driver table has unique_badge_number and driver_name
        
        Args:
            db: Database session
            db_name: Database name (not used in modern schema)
            factura: Invoice number
            
        Returns:
            Driver unique badge number or None
        """
        if not factura:
            return None
            
        try:
            # Join invoice_header -> invoice_logistics -> driver via driver_name
            query = text("""
                SELECT d.unique_badge_number
                FROM a76.invoice_header ih
                LEFT JOIN a76.invoice_logistics log ON log.invoice_id = ih.id
                LEFT JOIN a76.driver d ON d.driver_name = log.driver_name
                WHERE ih.invoice_number = :factura
                  AND d.unique_badge_number IS NOT NULL
                LIMIT 1
            """)
            result = db.execute(query, {"factura": factura}).fetchone()
            return result[0] if result else None
        except Exception as e:
            logger.error(f"Error fetching driver badge for invoice {factura}: {e}")
            raise
    
    @staticmethod
    def get_part_export_symbol(
        db: Session,
        db_name: str,
        num_parte: str,
        is_shelter: bool
    ) -> Optional[str]:
        """
        Get export symbol/license for a part number.
        
        Args:
            db: Database session
            db_name: Database name (not used in PostgreSQL, kept for compatibility)
            num_parte: Part number
            is_shelter: Shelter flag (not used, kept for compatibility)
            
        Returns:
            Export symbol/license or None
        """
        if not num_parte:
            return None
        
        try:
            query = text("""
                SELECT exclusion_symbol
                FROM a76.parts
                WHERE part_number = :num_parte
                LIMIT 1
            """)
            result = db.execute(query, {"num_parte": num_parte}).fetchone()
            return result[0] if result else None
        except Exception as e:
            logger.error(f"Error fetching export symbol for part {num_parte}: {e}")
            raise
