# Archivo: SolicitudVueloSQL.py
# Ruta: Consultas_SQL\Global\AdminSolicitudes\Vuelos\SolicitudVueloSQL.py
# Descripción: Consultas SQL para el módulo de Solicitud de Vuelos
# Autor: Equipo de Desarrollo IGSA
# Fecha: 2025

"""
Módulo de consultas SQL para Solicitudes de Vuelo.

Este módulo maneja todas las consultas relacionadas con:
- Consulta de catálogos (centros de costos)
- Validación de centros de costos
- Validación de proyectos en Epicor ERP
- Inserción de solicitudes de vuelo
- Inserción de pasajeros
- Inserción de opciones de vuelo

ESTRUCTURA DE TABLAS (ACTUALIZADA):
====================================

FR_FlightRequest:
- FlightRequestID (int, PK, IDENTITY)
- CentroCostos (nvarchar, NOT NULL)
- Proyecto (nvarchar, NULL)
- VigenciaProyecto (date, NULL)
- DescripcionMotivo (nvarchar, NOT NULL)
- Destino (nvarchar, NOT NULL)
- KilometrajeViaje (numeric, NULL)
- FechaSalida (date, NOT NULL)
- FechaRegreso (date, NULL)
- HoraAproxSalida (time, NOT NULL)
- HoraAproxRegreso (time, NULL)
- Estatus (nvarchar, NOT NULL, DEFAULT 'En Aprobacion')
  * Valores: 'En Aprobacion', 'En Atencion', 'Atendida'
- Resolucion (nvarchar, NULL)
  * Valores: NULL, 'Aprobada', 'Rechazada'
- ComentariosResolucion (nvarchar, NULL)
- CreatedAt (datetime, NOT NULL, DEFAULT GETDATE())
- UpdatedAt (datetime, NULL)
- Active (bit, NOT NULL, DEFAULT 1)

FR_FlightPassenger:
- FlightPassengerID (int, PK, IDENTITY)
- FlightRequestID (int, FK, NOT NULL)
- NombrePasajero (nvarchar, NOT NULL)
- NumeroEmpleado (nvarchar, NULL)
- Empresa (nvarchar, NOT NULL)
- FechaNacimiento (date, NOT NULL)
- TipoIdentificacion (nvarchar, NOT NULL)
- Nacionalidad (nvarchar, NOT NULL)
- CorreoElectronico (nvarchar, NOT NULL)
- Telefono (nvarchar, NOT NULL)
- NumeroPasaporte (nvarchar, NULL)
- VigenciaPasaporte (date, NULL)
- TieneVisaVigente (bit, NOT NULL, DEFAULT 0)
- TipoVisa (nvarchar, NULL)
- VigenciaVisa (date, NULL)
- PaisVisa (nvarchar, NULL)
- OrdenPasajero (int, NOT NULL)
- CreatedAt (datetime, NOT NULL, DEFAULT GETDATE())
- UpdatedAt (datetime, NULL)
- Active (bit, NOT NULL, DEFAULT 1)

FR_Flight:
- FlightID (int, PK, IDENTITY)
- FlightRequestID (int, FK, NOT NULL)
- Aerolinea (nvarchar, NOT NULL)
- NumeroVuelo (nvarchar, NULL)
- FechaVuelo (date, NOT NULL)
- CostoTotal (numeric, NOT NULL)
- RutaCarpetaVuelo (nvarchar, NULL)
- NombreArchivo (nvarchar, NULL)
- OrdenPreferencia (int, NULL)
- EsOpcionSugerida (bit, NOT NULL, DEFAULT 1)
- EsVueloComprado (bit, NOT NULL, DEFAULT 0)
- Estado (nvarchar, NULL)
- CodigoReservacion (nvarchar, NULL)
- FechaCompra (date, NULL)
- ObservacionesCompra (nvarchar, NULL)
- ComentariosVuelo (nvarchar, NULL)
- CreatedAt (datetime, NOT NULL, DEFAULT GETDATE())
- UpdatedAt (datetime, NULL)
- Active (bit, NOT NULL, DEFAULT 1)
"""

from Consultas_SQL.conexion import get_connection, get_connectionERP
import pyodbc
from datetime import datetime

class SolicitudVueloSQL:
    """
    Clase que contiene todas las consultas SQL para el módulo de Solicitud de Vuelos.
    Todos los métodos son estáticos para facilitar su uso sin necesidad de instanciar la clase.
    """
    
    # ========================================
    # CONSULTAS DE CATÁLOGOS
    # ========================================
    
    @staticmethod
    def obtener_centros_costos():
        """
        Obtiene todos los centros de costos activos de la base de datos.
        
        Returns:
            dict: {
                'success': bool,
                'centros': list de diccionarios con ceco y nombre,
                'error': str con mensaje de error (si falla)
            }
        """
        try:
            conn = get_connection()
            cursor = conn.cursor()
            
            query = """
                SELECT 
                    CeCo,
                    NombreCeCo
                FROM Cost_Centers
                WHERE Active = 1
                ORDER BY CeCo ASC
            """
            
            cursor.execute(query)
            resultados = cursor.fetchall()
            
            cursor.close()
            conn.close()
            
            centros = []
            for resultado in resultados:
                centros.append({
                    'ceco': resultado[0],
                    'nombre': resultado[1]
                })
            
            return {
                'success': True,
                'centros': centros,
                'error': None
            }
                
        except pyodbc.Error as e:
            return {
                'success': False,
                'centros': [],
                'error': f'Error al obtener centros de costos: {str(e)}'
            }
        except Exception as e:
            return {
                'success': False,
                'centros': [],
                'error': f'Error inesperado: {str(e)}'
            }
    
    # ========================================
    # VALIDACIONES
    # ========================================
    
    @staticmethod
    def validar_centro_costos(centro_costos):
        """
        Valida que el centro de costos exista y esté activo en la base de datos.
        
        Args:
            centro_costos (str): Código del centro de costos a validar
            
        Returns:
            dict: {
                'existe': bool,
                'datos': dict con información del centro de costos (si existe),
                'error': str con mensaje de error (si falla)
            }
        """
        try:
            conn = get_connection()
            cursor = conn.cursor()
            
            query = """
                SELECT 
                    CeCo,
                    NombreCeCo,
                    Active
                FROM Cost_Centers
                WHERE CeCo = ?
                  AND Active = 1
            """
            
            cursor.execute(query, (centro_costos,))
            resultado = cursor.fetchone()
            
            cursor.close()
            conn.close()
            
            if resultado:
                return {
                    'existe': True,
                    'datos': {
                        'CeCo': resultado[0],
                        'NombreCeCo': resultado[1],
                        'Active': resultado[2]
                    },
                    'error': None
                }
            else:
                return {
                    'existe': False,
                    'datos': None,
                    'error': f'Centro de costos "{centro_costos}" no encontrado o inactivo'
                }
                
        except pyodbc.Error as e:
            return {
                'existe': False,
                'datos': None,
                'error': f'Error al validar centro de costos: {str(e)}'
            }
        except Exception as e:
            return {
                'existe': False,
                'datos': None,
                'error': f'Error inesperado: {str(e)}'
            }
    
    @staticmethod
    def validar_proyecto_erp(proyecto_id):
        """
        Valida que el proyecto exista en Epicor ERP y esté vigente.
        
        Args:
            proyecto_id (str): ID del proyecto en Epicor
            
        Returns:
            dict: {
                'existe': bool,
                'vigente': bool,
                'datos': dict con información del proyecto (si existe),
                'error': str con mensaje de error (si falla)
            }
        """
        try:
            conn = get_connectionERP()
            cursor = conn.cursor()
            
            # Consulta para validar proyecto con órdenes vigentes
            query = """
                SELECT 
                    Project.ProjectID,
                    Project.Description AS NombreProyecto,
                    OrderDtl.OrderNum,
                    OrderHed.CustNum,
                    OrderHed.OrderDate,
                    OrderHed.OrderAmt,
                    OrderHed.OpenOrder,
                    OrderHed.VoidOrder,
                    CASE 
                        WHEN OrderHed.OpenOrder = 1 AND OrderHed.VoidOrder = 0 
                            THEN 'VIGENTE'
                        ELSE 'NO VIGENTE'
                    END AS Estado
                FROM Erp.Project AS Project
                INNER JOIN Erp.OrderDtl AS OrderDtl
                    ON Project.Company = OrderDtl.Company
                    AND Project.ProjectID = OrderDtl.ProjectID
                INNER JOIN Erp.OrderHed AS OrderHed
                    ON OrderDtl.Company = OrderHed.Company
                    AND OrderDtl.OrderNum = OrderHed.OrderNum
                WHERE 
                    Project.Company = 'IGSA'
                    AND Project.ProjectID = ?
                    AND OrderHed.OpenOrder = 1
                    AND OrderHed.VoidOrder = 0
            """
            
            cursor.execute(query, (proyecto_id,))
            resultado = cursor.fetchone()
            
            cursor.close()
            conn.close()
            
            if resultado:
                return {
                    'existe': True,
                    'vigente': resultado[8] == 'VIGENTE',
                    'datos': {
                        'ProjectID': resultado[0],
                        'NombreProyecto': resultado[1],
                        'OrderNum': resultado[2],
                        'CustNum': resultado[3],
                        'OrderDate': resultado[4],
                        'OrderAmt': resultado[5],
                        'OpenOrder': resultado[6],
                        'VoidOrder': resultado[7],
                        'Estado': resultado[8]
                    },
                    'error': None
                }
            else:
                return {
                    'existe': False,
                    'vigente': False,
                    'datos': None,
                    'error': f'Proyecto "{proyecto_id}" no encontrado o no tiene órdenes vigentes'
                }
                
        except pyodbc.Error as e:
            return {
                'existe': False,
                'vigente': False,
                'datos': None,
                'error': f'Error al validar proyecto en ERP: {str(e)}'
            }
        except Exception as e:
            return {
                'existe': False,
                'vigente': False,
                'datos': None,
                'error': f'Error inesperado: {str(e)}'
            }
    
    # ========================================
    # INSERCIÓN DE DATOS
    # ========================================
    
    @staticmethod
    def insertar_solicitud(datos_solicitud):
        """
        Inserta una nueva solicitud de vuelo en la tabla FR_FlightRequest.
        
        Args:
            datos_solicitud (dict): Diccionario con los datos de la solicitud
                
        Returns:
            dict: {
                'success': bool,
                'flight_request_id': int (ID generado),
                'folio': str (folio generado),
                'error': str (si falla)
            }
        """
        conn = None
        try:
            conn = get_connection()
            cursor = conn.cursor()
            
            # ====================================================================
            # Query de inserción basada en la estructura real de la tabla
            # Estatus inicial: 'En Aprobacion' (valor por defecto del constraint)
            # CreatedAt: Se auto-completa con DEFAULT
            # ====================================================================
            query = """
                INSERT INTO FR_FlightRequest (
                    CentroCostos,
                    Proyecto,
                    VigenciaProyecto,
                    DescripcionMotivo,
                    Destino,
                    KilometrajeViaje,
                    FechaSalida,
                    FechaRegreso,
                    HoraAproxSalida,
                    HoraAproxRegreso,
                    Estatus,
                    Active
                ) VALUES (
                    ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
                    'En Aprobacion',
                    1
                )
            """
            
            cursor.execute(query, (
                datos_solicitud.get('CentroCostos'),
                datos_solicitud.get('Proyecto'),
                datos_solicitud.get('VigenciaProyecto'),
                datos_solicitud.get('DescripcionMotivo'),
                datos_solicitud.get('Destino'),
                datos_solicitud.get('KilometrajeViaje'),
                datos_solicitud.get('FechaSalida'),
                datos_solicitud.get('FechaRegreso'),
                datos_solicitud.get('HoraAproxSalida'),
                datos_solicitud.get('HoraAproxRegreso')
            ))
            
            # Obtener el ID generado
            cursor.execute("SELECT @@IDENTITY")
            flight_request_id = int(cursor.fetchone()[0])
            
            # Generar folio
            folio = f"FLT-{flight_request_id:06d}"
            
            # Confirmar transacción
            conn.commit()
            
            cursor.close()
            conn.close()
            
            return {
                'success': True,
                'flight_request_id': flight_request_id,
                'folio': folio,
                'error': None
            }
            
        except pyodbc.Error as e:
            if conn:
                conn.rollback()
            return {
                'success': False,
                'flight_request_id': None,
                'folio': None,
                'error': f'Error al insertar solicitud: {str(e)}'
            }
        except Exception as e:
            if conn:
                conn.rollback()
            return {
                'success': False,
                'flight_request_id': None,
                'folio': None,
                'error': f'Error inesperado: {str(e)}'
            }
    
    @staticmethod
    def insertar_pasajeros(flight_request_id, lista_pasajeros):
        """
        Inserta múltiples pasajeros asociados a una solicitud de vuelo.
        
        Args:
            flight_request_id (int): ID de la solicitud de vuelo
            lista_pasajeros (list): Lista de diccionarios con datos de pasajeros
                
        Returns:
            dict: {
                'success': bool,
                'insertados': int (cantidad de pasajeros insertados),
                'error': str (si falla)
            }
        """
        conn = None
        try:
            conn = get_connection()
            cursor = conn.cursor()
            
            # ====================================================================
            # Query de inserción basada en la estructura real de la tabla
            # CreatedAt y UpdatedAt: Se auto-completan con DEFAULT
            # ====================================================================
            query = """
                INSERT INTO FR_FlightPassenger (
                    FlightRequestID,
                    NombrePasajero,
                    NumeroEmpleado,
                    Empresa,
                    FechaNacimiento,
                    TipoIdentificacion,
                    Nacionalidad,
                    CorreoElectronico,
                    Telefono,
                    OrdenPasajero,
                    Active
                ) VALUES (
                    ?, ?, ?, ?, ?, ?, ?, ?, ?, ? 
                    , 1
                )
            """
            
            insertados = 0
            
            for pasajero in lista_pasajeros:
                cursor.execute(query, (
                    flight_request_id,
                    pasajero.get('NombrePasajero'),
                    pasajero.get('NumeroEmpleado'),
                    pasajero.get('Empresa'),
                    pasajero.get('FechaNacimiento'),
                    pasajero.get('TipoIdentificacion'),
                    pasajero.get('Nacionalidad'),
                    pasajero.get('CorreoElectronico'),
                    pasajero.get('Telefono'),
                    pasajero.get('OrdenPasajero')
                ))
                insertados += 1
            
            # Confirmar transacción
            conn.commit()
            
            cursor.close()
            conn.close()
            
            return {
                'success': True,
                'insertados': insertados,
                'error': None
            }
            
        except pyodbc.Error as e:
            if conn:
                conn.rollback()
            return {
                'success': False,
                'insertados': 0,
                'error': f'Error al insertar pasajeros: {str(e)}'
            }
        except Exception as e:
            if conn:
                conn.rollback()
            return {
                'success': False,
                'insertados': 0,
                'error': f'Error inesperado: {str(e)}'
            }
    
    @staticmethod
    def insertar_vuelos(flight_request_id, lista_vuelos):
        """
        Inserta múltiples opciones de vuelo asociadas a una solicitud.
        
        Args:
            flight_request_id (int): ID de la solicitud de vuelo
            lista_vuelos (list): Lista de diccionarios con datos de vuelos
                
        Returns:
            dict: {
                'success': bool,
                'insertados': int (cantidad de vuelos insertados),
                'error': str (si falla)
            }
        """
        conn = None
        try:
            conn = get_connection()
            cursor = conn.cursor()
            
            # ====================================================================
            # Query de inserción basada en la estructura real de la tabla
            # FechaVuelo es NOT NULL según el script CREATE TABLE
            # CreatedAt y UpdatedAt: Se auto-completan con DEFAULT
            # ====================================================================
            query = """
                INSERT INTO FR_Flight (
                    FlightRequestID,
                    Aerolinea,
                    NumeroVuelo,
                    FechaVuelo,
                    CostoTotal,
                    OrdenPreferencia,
                    EsOpcionSugerida,
                    EsVueloComprado,
                    Active
                ) VALUES (
                    ?, ?, ?, ?, ?, ?, 1, 0, 1
                )
            """
            
            insertados = 0
            
            for vuelo in lista_vuelos:
                # FechaVuelo es NOT NULL, usar fecha de salida de la solicitud como default
                fecha_vuelo = vuelo.get('FechaVuelo')
                if not fecha_vuelo:
                    # Obtener la fecha de salida de la solicitud
                    cursor.execute("""
                        SELECT FechaSalida 
                        FROM FR_FlightRequest 
                        WHERE FlightRequestID = ?
                    """, (flight_request_id,))
                    fecha_vuelo = cursor.fetchone()[0]
                
                cursor.execute(query, (
                    flight_request_id,
                    vuelo.get('Aerolinea'),
                    vuelo.get('NumeroVuelo'),
                    fecha_vuelo,  # NOT NULL
                    vuelo.get('CostoTotal'),
                    vuelo.get('OrdenPreferencia')
                ))
                insertados += 1
            
            # Confirmar transacción
            conn.commit()
            
            cursor.close()
            conn.close()
            
            return {
                'success': True,
                'insertados': insertados,
                'error': None
            }
            
        except pyodbc.Error as e:
            if conn:
                conn.rollback()
            return {
                'success': False,
                'insertados': 0,
                'error': f'Error al insertar opciones de vuelo: {str(e)}'
            }
        except Exception as e:
            if conn:
                conn.rollback()
            return {
                'success': False,
                'insertados': 0,
                'error': f'Error inesperado: {str(e)}'
            }