# Archivo: CotizadorSQL.py
# Ruta: src\Consultas_SQL\Ventas\VentasEUA\CotizadorSQL.py
# Lenguaje: Python con Flask

from Consultas_SQL.conexion import get_connection
from typing import List

def get_fueltype():
    """Obtiene los tipos de combustibles disponibles.
    Retorna una lista de diccionarios con ID y Nombre."""
    query = """
    SELECT DISTINCT
        CZE_FuelType.FuelTypeID,
        CZE_FuelType.Front
    FROM 
        CZE_Genset
    JOIN 
        CZE_EngineModel ON CZE_Genset.EngineModel = CZE_EngineModel.EngineModel
    JOIN 
        CZE_FuelType ON CZE_EngineModel.FuelTypeBack = CZE_FuelType.Back
    JOIN 
        CZE_GensetConfig ON CZE_Genset.GensetModel = CZE_GensetConfig.GensetModel
    JOIN 
        CZE_GensetCost ON CZE_GensetConfig.ConfigID = CZE_GensetCost.ConfigID
    WHERE
        CZE_Genset.Active = 1
        AND CZE_FuelType.Front IS NOT NULL
        AND CZE_GensetCost.Evaluator >= 1
    """
    try:
        conn = get_connection()
        if not conn:
            raise ConnectionError("No se pudo establecer conexión con la base de datos")
        
        cursor = conn.cursor()
        cursor.execute(query)
        results = cursor.fetchall()
        
        return [{"id": row[0], "Fueltype": row[1]} for row in results]
    except Exception as e:
        print(f"[Error inesperado] Error obteniendo tipos de combustible: {e}")
        return []
    finally:
        if conn:
            conn.close()

# V1
def getAll_StopButtonEnclosure():
    query ='select * from StopButtonEnclosureOptions'
    try:
        with get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query)
            return [{"stop_id": row[0], "stop_decription": row[1], "stop_price": row[2], "stop_technicalSheet": row[3]} for row in cursor.fetchall()]
    except Exception as e:
        print(f"Error obteniendo marcas de motor: {e}")
        return []
    

def getAll_louvers_in():
    query ='select * from louvers_in'
    try:
        with get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query)
            return [{"louvers_in_id": row[0], "louvers_in_decription": row[1], "louvers_in_price": row[2], "louvers_in_technicalSheet": row[3]} for row in cursor.fetchall()]
    except Exception as e:
        print(f"Error obteniendo marcas de motor: {e}")
        return []


def getAll_louvers_out():
    query ='select * from louvers_out'
    try:
        with get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query)
            return [{"louvers_out_id": row[0], "louvers_out_decription": row[1], "louvers_out_price": row[2], "louvers_out_technicalSheet": row[3]} for row in cursor.fetchall()]
    except Exception as e:
        print(f"Error obteniendo marcas de motor: {e}")
        return []




def get_kw_by_fuel(fuel_type):
    """Obtiene las capacidades KW disponibles para un tipo de combustible.
    Args:
        fuel_type (str): Tipo de combustible seleccionado
    Returns:
        list: Lista de diccionarios con las capacidades KW disponibles
    """
    query = """
    SELECT DISTINCT
        CZE_Genset.KWOutput
    FROM 
        CZE_Genset
    JOIN 
        CZE_EngineModel ON CZE_Genset.EngineModel = CZE_EngineModel.EngineModel
    JOIN 
        CZE_FuelType ON CZE_EngineModel.FuelTypeBack = CZE_FuelType.Back
    JOIN 
        CZE_GensetConfig ON CZE_Genset.GensetModel = CZE_GensetConfig.GensetModel
    JOIN 
        CZE_GensetCost ON CZE_GensetConfig.ConfigID = CZE_GensetCost.ConfigID
    WHERE
        CZE_Genset.Active = 1
        AND CZE_FuelType.Front = ?
        AND CZE_GensetCost.Evaluator >= 1
    ORDER BY 
        CZE_Genset.KWOutput;
    """
    try:
        with get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query, fuel_type)
            results = cursor.fetchall()
            
            return [{"KW": row[0]} for row in results]
    except Exception as e:
        print(f"Error obteniendo capacidades KW: {e}")
        return []
    
def get_engine_brands(fuel_type, kw_output):
    """Obtiene las marcas de motor disponibles según el tipo de combustible y KW seleccionados.
    Args:
        fuel_type (str): Tipo de combustible seleccionado
        kw_output (int): Capacidad KW seleccionada
    Returns:
        list: Lista de diccionarios con id y marca de los motores disponibles
    """
    query = """
    SELECT DISTINCT
        CZE_EngineBrand.EngineBrandID,
        CZE_EngineBrand.EngineBrand
    FROM 
        CZE_Genset
    JOIN 
        CZE_EngineModel ON CZE_Genset.EngineModel = CZE_EngineModel.EngineModel
    JOIN 
        CZE_FuelType ON CZE_EngineModel.FuelTypeBack = CZE_FuelType.Back
    JOIN 
        CZE_EngineBrand ON CZE_EngineModel.EngineBrandID = CZE_EngineBrand.EngineBrandID
    JOIN 
        CZE_GensetConfig ON CZE_Genset.GensetModel = CZE_GensetConfig.GensetModel
    JOIN 
        CZE_GensetCost ON CZE_GensetConfig.ConfigID = CZE_GensetCost.ConfigID
    WHERE
        CZE_Genset.Active = 1
        AND CZE_EngineBrand.Active = 1
        AND CZE_FuelType.Front = ?
        AND CZE_Genset.KWOutput = ?
        AND CZE_GensetCost.Evaluator >= 1
    ORDER BY 
        CZE_EngineBrand.EngineBrand
    """
    try:
        with get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query, (fuel_type, kw_output))
            return [{"id": row[0], "brand": row[1]} for row in cursor.fetchall()]
    except Exception as e:
        print(f"Error obteniendo marcas de motor: {e}")
        return []
    
def get_alternator_brands(fuel_type, kw_output, engine_brand_id):
    """Obtiene las marcas de alternador disponibles según los parámetros seleccionados.
    Args:
        fuel_type (str): Tipo de combustible seleccionado
        kw_output (int): Capacidad KW seleccionada
        engine_brand_id (str): ID de la marca del motor seleccionado
    Returns:
        list: Lista de diccionarios con id y marca de los alternadores disponibles
    """
    query = """
    SELECT DISTINCT
        CZE_AlternatorBrand.AlternatorBrandID,
        CZE_AlternatorBrand.AlternatorBrand
    FROM 
        CZE_Genset
    JOIN 
        CZE_EngineModel ON CZE_Genset.EngineModel = CZE_EngineModel.EngineModel
    JOIN 
        CZE_FuelType ON CZE_EngineModel.FuelTypeBack = CZE_FuelType.Back
    JOIN 
        CZE_EngineBrand ON CZE_EngineModel.EngineBrandID = CZE_EngineBrand.EngineBrandID
    JOIN 
        CZE_AlternatorBrand ON CZE_Genset.AlternatorBrandID = CZE_AlternatorBrand.AlternatorBrandID
    JOIN 
        CZE_GensetConfig ON CZE_Genset.GensetModel = CZE_GensetConfig.GensetModel
    JOIN 
        CZE_GensetCost ON CZE_GensetConfig.ConfigID = CZE_GensetCost.ConfigID
    WHERE
        CZE_Genset.Active = 1
        AND CZE_EngineBrand.Active = 1
        AND CZE_AlternatorBrand.Active = 1
        AND CZE_FuelType.Front = ?
        AND CZE_Genset.KWOutput = ?
        AND CZE_EngineBrand.EngineBrandID = ?
        AND CZE_GensetCost.Evaluator >= 1
    ORDER BY 
        CZE_AlternatorBrand.AlternatorBrand
    """
    try:
        with get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query, (fuel_type, kw_output, engine_brand_id))
            return [{"id": row[0], "brand": row[1]} for row in cursor.fetchall()]
    except Exception as e:
        print(f"Error obteniendo marcas de alternador: {e}")
        return []
    
def get_voltages(fuel_type, kw_output, engine_brand_id, alternator_brand_id):
    """Obtiene los voltajes disponibles según los parámetros seleccionados.
    Args:
        fuel_type (str): Tipo de combustible seleccionado
        kw_output (int): Capacidad KW seleccionada
        engine_brand_id (str): ID de la marca del motor seleccionado
        alternator_brand_id (str): ID de la marca del alternador seleccionado
    Returns:
        list: Lista de diccionarios con id y voltaje disponibles
    """
    query = """
    SELECT DISTINCT
        CZE_Voltage.VoltageID,
        CZE_Voltage.Voltage_Front
    FROM 
        CZE_Genset
    JOIN 
        CZE_EngineModel ON CZE_Genset.EngineModel = CZE_EngineModel.EngineModel
    JOIN 
        CZE_FuelType ON CZE_EngineModel.FuelTypeBack = CZE_FuelType.Back
    JOIN 
        CZE_EngineBrand ON CZE_EngineModel.EngineBrandID = CZE_EngineBrand.EngineBrandID
    JOIN 
        CZE_AlternatorBrand ON CZE_Genset.AlternatorBrandID = CZE_AlternatorBrand.AlternatorBrandID
    JOIN 
        CZE_GensetConfig ON CZE_Genset.GensetModel = CZE_GensetConfig.GensetModel
    JOIN 
        CZE_Voltage ON CZE_GensetConfig.VoltageID = CZE_Voltage.VoltageID
    JOIN 
        CZE_GensetCost ON CZE_GensetConfig.ConfigID = CZE_GensetCost.ConfigID
    WHERE
        CZE_Genset.Active = 1
        AND CZE_EngineBrand.Active = 1
        AND CZE_AlternatorBrand.Active = 1
        AND CZE_FuelType.Front = ?
        AND CZE_Genset.KWOutput = ?
        AND CZE_EngineBrand.EngineBrandID = ?
        AND CZE_AlternatorBrand.AlternatorBrandID = ?
        AND CZE_GensetCost.Evaluator >= 1
    ORDER BY 
        CZE_Voltage.Voltage_Front
    """
    try:
        with get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query, (fuel_type, kw_output, engine_brand_id, alternator_brand_id))
            return [{"id": row[0], "voltage": row[1]} for row in cursor.fetchall()]
    except Exception as e:
        print(f"Error obteniendo voltajes: {e}")
        return []
    
def get_certifications(fuel_type, kw_output, engine_brand_id, alternator_brand_id, voltage_id):
    """Obtiene las certificaciones disponibles según los parámetros seleccionados.
    Args:
        fuel_type (str): Tipo de combustible seleccionado
        kw_output (int): Capacidad KW seleccionada
        engine_brand_id (str): ID de la marca del motor seleccionado
        alternator_brand_id (str): ID de la marca del alternador seleccionado
        voltage_id (int): ID del voltaje seleccionado
    Returns:
        list: Lista de diccionarios con id y certificación disponibles
    """
    query = """
    SELECT DISTINCT
        CZE_Certification.CertificationID,
        CZE_Certification.Certification
    FROM 
        CZE_Genset
    JOIN 
        CZE_EngineModel ON CZE_Genset.EngineModel = CZE_EngineModel.EngineModel
    JOIN 
        CZE_FuelType ON CZE_EngineModel.FuelTypeBack = CZE_FuelType.Back
    JOIN 
        CZE_EngineBrand ON CZE_EngineModel.EngineBrandID = CZE_EngineBrand.EngineBrandID
    JOIN 
        CZE_AlternatorBrand ON CZE_Genset.AlternatorBrandID = CZE_AlternatorBrand.AlternatorBrandID
    JOIN 
        CZE_GensetConfig ON CZE_Genset.GensetModel = CZE_GensetConfig.GensetModel
    JOIN 
        CZE_Voltage ON CZE_GensetConfig.VoltageID = CZE_Voltage.VoltageID
    JOIN 
        CZE_Certification ON CZE_GensetConfig.CertificationID = CZE_Certification.CertificationID
    JOIN 
        CZE_GensetCost ON CZE_GensetConfig.ConfigID = CZE_GensetCost.ConfigID
    WHERE
        CZE_Genset.Active = 1
        AND CZE_EngineBrand.Active = 1
        AND CZE_AlternatorBrand.Active = 1
        AND CZE_FuelType.Front = ?
        AND CZE_Genset.KWOutput = ?
        AND CZE_EngineBrand.EngineBrandID = ?
        AND CZE_AlternatorBrand.AlternatorBrandID = ?
        AND CZE_Voltage.VoltageID = ?
        AND CZE_GensetCost.Evaluator >= 1
    ORDER BY 
        CZE_Certification.Certification
    """
    try:
        with get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query, (fuel_type, kw_output, engine_brand_id, alternator_brand_id, voltage_id))
            return [{"id": row[0], "certification": row[1]} for row in cursor.fetchall()]
    except Exception as e:
        print(f"Error obteniendo certificaciones: {e}")
        return []
    
def get_breaker_catalog():
    query = """
    SELECT 
        [CZE_BreakersCatalog].[Item_breaker],
        [CZE_BreakersCatalog].[description],
        ISNULL([CZE_BreakersCatalog].[price], 0) AS price,
        [CZE_BreakersCatalog].[type]
    FROM 
        [CZE_BreakersCatalog]
    ORDER BY 
        [CZE_BreakersCatalog].[description]
    """
    with get_connection() as conn:
        cursor = conn.cursor()
        cursor.execute(query)
        return [{"id": row[0], "description": row[1], "price": row[2], "type": row[3]} for row in cursor.fetchall()]

def get_protection_types(breaker_type):
    query = """
    SELECT 
        [CZE_BreakerProtectionType].[Item_breaker_Protection],
        [CZE_BreakerProtectionType].[description],
        ISNULL([CZE_BreakerProtectionType].[price], 0) AS price
    FROM 
        [CZE_BreakerProtectionType]
    WHERE
        [CZE_BreakerProtectionType].[TypeBreaker] = ?
    ORDER BY 
        [CZE_BreakerProtectionType].[Item_breaker_Protection] asc
    """
    with get_connection() as conn:
        cursor = conn.cursor()
        cursor.execute(query, breaker_type)
        return [{"id": row[0], "description": row[1], "price": row[2]} for row in cursor.fetchall()]

def get_control_brands(fuel_type, kw_output, engine_brand_id, alternator_brand_id, voltage_id, certification_id):
    """Obtiene las marcas de controlador disponibles según los parámetros seleccionados.
    Args:
        fuel_type (str): Tipo de combustible seleccionado
        kw_output (int): Capacidad KW seleccionada
        engine_brand_id (str): ID de la marca del motor seleccionado
        alternator_brand_id (str): ID de la marca del alternador seleccionado
        voltage_id (int): ID del voltaje seleccionado
        certification_id (str): ID de la certificación seleccionada
    Returns:
        list: Lista de diccionarios con id y marca de los controladores disponibles
    """
    query = """
    SELECT DISTINCT
        CZE_ControllerBrand.BrandID,
        CZE_ControllerBrand.Brand
    FROM 
        CZE_Genset
    JOIN 
        CZE_EngineModel ON CZE_Genset.EngineModel = CZE_EngineModel.EngineModel
    JOIN 
        CZE_FuelType ON CZE_EngineModel.FuelTypeBack = CZE_FuelType.Back
    JOIN 
        CZE_EngineBrand ON CZE_EngineModel.EngineBrandID = CZE_EngineBrand.EngineBrandID
    JOIN 
        CZE_AlternatorBrand ON CZE_Genset.AlternatorBrandID = CZE_AlternatorBrand.AlternatorBrandID
    JOIN 
        CZE_GensetConfig ON CZE_Genset.GensetModel = CZE_GensetConfig.GensetModel
    JOIN 
        CZE_Voltage ON CZE_GensetConfig.VoltageID = CZE_Voltage.VoltageID
    JOIN 
        CZE_Certification ON CZE_GensetConfig.CertificationID = CZE_Certification.CertificationID
    JOIN 
        CZE_GensetCost ON CZE_GensetConfig.ConfigID = CZE_GensetCost.ConfigID
    JOIN 
        CZE_ControllerBrand ON CZE_GensetCost.ControllerBrandID = CZE_ControllerBrand.BrandID
    WHERE
        CZE_Genset.Active = 1
        AND CZE_EngineBrand.Active = 1
        AND CZE_AlternatorBrand.Active = 1
        AND CZE_FuelType.Front = ?
        AND CZE_Genset.KWOutput = ?
        AND CZE_EngineBrand.EngineBrandID = ?
        AND CZE_AlternatorBrand.AlternatorBrandID = ?
        AND CZE_Voltage.VoltageID = ?
        AND CZE_Certification.CertificationID = ?
        AND CZE_GensetCost.Evaluator >= 1
    ORDER BY 
        CZE_ControllerBrand.Brand
    """
    try:
        with get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query, (fuel_type, kw_output, engine_brand_id, alternator_brand_id, voltage_id, certification_id))
            return [{"id": row[0], "brand": row[1]} for row in cursor.fetchall()]
    except Exception as e:
        print(f"Error obteniendo marcas de controlador: {e}")
        return []
    
def get_control_models(control_brand_id):
    """Obtiene los modelos de controlador disponibles para una marca específica.
    Args:
        control_brand_id (str): ID de la marca del controlador
    Returns:
        list: Lista de diccionarios con item y descripción de los modelos
    """
    query = """
    SELECT DISTINCT
        CZE_ControllerCatalog.Item_controller,
        CZE_ControllerCatalog.description
    FROM 
        CZE_ControllerCatalog
    WHERE
        CZE_ControllerCatalog.BrandID = ?
    ORDER BY 
        CZE_ControllerCatalog.description
    """
    try:
        with get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query, control_brand_id)
            return [{"item": row[0], "description": row[1]} for row in cursor.fetchall()]
    except Exception as e:
        print(f"Error obteniendo modelos de controlador: {e}")
        return []

def get_preheater_voltages():
    query = """
    SELECT 
        [CZE_ControllerPreheater].[Item_preheater_volt],
        [CZE_ControllerPreheater].[description],
        ISNULL([CZE_ControllerPreheater].[price], 0) AS price
    FROM 
        [CZE_ControllerPreheater]
    ORDER BY 
        [CZE_ControllerPreheater].[description]
    """
    try:
        with get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query)
            return [{
                "item": row[0],
                "description": row[1],
                "price": float(row[2]) if row[2] else 0
            } for row in cursor.fetchall()]
    except Exception as e:
        print(f"Error obteniendo voltajes de precalentador: {e}")
        return []
    
def get_battery_chargers():
    query = """
    SELECT 
        [CZE_ControllerBattCharger].[Item_batterycharger],
        [CZE_ControllerBattCharger].[description],
        ISNULL([CZE_ControllerBattCharger].[price], 0) AS price
    FROM 
        [CZE_ControllerBattCharger]
    ORDER BY 
        [CZE_ControllerBattCharger].[description]
    """
    try:
        with get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query)
            return [{
                "item": row[0],
                "description": row[1],
                "price": float(row[2]) if row[2] else 0
            } for row in cursor.fetchall()]
    except Exception as e:
        print(f"Error obteniendo cargadores de batería: {e}")
        return []
    
def get_enclosure_types(fuel_type, kw_output, certification_id):
    """Obtiene los tipos de caseta disponibles según los parámetros seleccionados."""
    query = """
    SELECT DISTINCT
        CZE_EnclosureType.Item_Enclosuree_type,
        CZE_EnclosureType.Front
    FROM
        CZE_EnclosureType
    JOIN 
        CZE_EnclosureConfig ON CZE_EnclosureType.Item_Enclosuree_type = CZE_EnclosureConfig.Item_Enclosure_type
    JOIN 
        CZE_FuelType ON CZE_EnclosureConfig.FuelType = CZE_FuelType.Back

    WHERE
        CZE_FuelType.Front = ?
        AND CZE_EnclosureConfig.KWOutput = ?
        AND CZE_EnclosureConfig.CertificationID = ?
        AND CZE_EnclosureConfig.Evaluator >= 1
    """
    try:
        with get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query, (fuel_type, kw_output, certification_id))
            return [{"item": row[0], "description": row[1]} for row in cursor.fetchall()]
    except Exception as e:
        print(f"Error obteniendo tipos de caseta: {e}")
        return []

def get_enclosure_materials(fuel_type, kw_output, certification_id, enclosure_type):
    """Obtiene los materiales de caseta disponibles según los parámetros seleccionados."""
    query = """
    SELECT DISTINCT
        CZE_EnclosureMaterial.Item_Eclousure_Mat,
        CZE_EnclosureMaterial.description
    FROM
        CZE_EnclosureMaterial
    JOIN 
        CZE_EnclosureConfig ON CZE_EnclosureMaterial.Item_Eclousure_Mat = CZE_EnclosureConfig.Item_Enclosure_Mat
    JOIN 
        CZE_FuelType ON CZE_EnclosureConfig.FuelType = CZE_FuelType.Back        
    WHERE
        CZE_FuelType.Front = ?
        AND CZE_EnclosureConfig.KWOutput = ?
        AND CZE_EnclosureConfig.CertificationID = ?
        AND CZE_EnclosureConfig.Item_Enclosure_type = ?
        AND CZE_EnclosureConfig.Evaluator >= 1
    """
    try:
        with get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query, (fuel_type, kw_output, certification_id, enclosure_type))
            return [{"item": row[0], "description": row[1]} for row in cursor.fetchall()]
    except Exception as e:
        print(f"Error obteniendo materiales de caseta: {e}")
        return []
    
def get_enclosure_lights_ac():
    query = """
    SELECT DISTINCT
        [CZE_EnclosureLight].[Item_Enclosure_ligth],
        [CZE_EnclosureLight].[description]
    FROM
        [CZE_EnclosureLight]
    WHERE
        [CZE_EnclosureLight].[type] = 'AC'
    """
    try:
        with get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query)
            return [{
                "item": row[0],
                "description": row[1]
            } for row in cursor.fetchall()]
    except Exception as e:
        print(f"Error obteniendo luces AC de caseta: {e}")
        return []

def get_enclosure_lights_dc():
    query = """
    SELECT DISTINCT
        [CZE_EnclosureLight].[Item_Enclosure_ligth],
        [CZE_EnclosureLight].[description]
    FROM
        [CZE_EnclosureLight]
    WHERE
        [CZE_EnclosureLight].[type] = 'DC'
    """
    try:
        with get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query)
            return [{
                "item": row[0],
                "description": row[1]
            } for row in cursor.fetchall()]
    except Exception as e:
        print(f"Error obteniendo luces DC de caseta: {e}")
        return []
    
def get_enclosure_space_heaters():
    query = """
    SELECT DISTINCT
        [CZE_EnclosureSpaceHeater].[Item_Enclosure_SHeater],
        [CZE_EnclosureSpaceHeater].[description]
    FROM
        [CZE_EnclosureSpaceHeater]
    """
    try:
        with get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query)
            return [{
                "item": row[0],
                "description": row[1]
            } for row in cursor.fetchall()]
    except Exception as e:
        print(f"Error obteniendo calentadores de espacio: {e}")
        return []

def get_enclosure_load_centers():
    query = """
    SELECT DISTINCT
        [CZE_EnclosureLoadCenter].[Item_Enclosure_LCenter],
        [CZE_EnclosureLoadCenter].[description]
    FROM
        [CZE_EnclosureLoadCenter]
    """
    try:
        with get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query)
            return [{
                "item": row[0],
                "description": row[1]
            } for row in cursor.fetchall()]
    except Exception as e:
        print(f"Error obteniendo centros de carga: {e}")
        return []

def get_estop_catalog():
    """Obtiene las opciones de paro de emergencia remoto.
    Retorna una lista de diccionarios con item y descripción."""
    query = """
    SELECT DISTINCT
        [CZE_AddAccesory_Estop].[Item_AddAccesory_Estop],
        [CZE_AddAccesory_Estop].[description]
    FROM
        [CZE_AddAccesory_Estop]
    """
    try:
        with get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query)
            return [{
                "item": row[0],
                "description": row[1]
            } for row in cursor.fetchall()]
    except Exception as e:
        print(f"Error obteniendo opciones de paro de emergencia: {e}")
        return []

def get_spring_catalog():
    """Obtiene las opciones de resorte de aislador.
    Retorna una lista de diccionarios con item y descripción."""
    query = """
    SELECT DISTINCT
        [CZE_AddAccesory_Spring].[Item_AddAccesory_Spring],
        [CZE_AddAccesory_Spring].[description]
    FROM
        [CZE_AddAccesory_Spring]
    """
    try:
        with get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query)
            return [{
                "item": row[0],
                "description": row[1]
            } for row in cursor.fetchall()]
    except Exception as e:
        print(f"Error obteniendo opciones de resorte de aislador: {e}")
        return []
    
def get_tank_capacity_catalog(kw_output):
    """Obtiene las capacidades de tanque disponibles para el KW seleccionado."""
    query = """
    SELECT DISTINCT
        CZE_TankAutonomy.Item_Tank_Autonomy,
        CZE_TankAutonomy.description
    FROM
        CZE_TankAutonomy
    JOIN 
        CZE_TankConfig ON CZE_TankAutonomy.Item_Tank_Autonomy = CZE_TankConfig.Item_Tank_Autonomy
    WHERE
        CZE_TankConfig.KWOutput = ?
        AND CZE_TankConfig.Evaluator >= 1
    """
    try:
        with get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query, (kw_output))
            return [{"item": row[0], "description": row[1]} for row in cursor.fetchall()]
    except Exception as e:
        print(f"Error obteniendo capacidades de tanque: {e}")
        return []

def get_tank_type_catalog(kw_output, tank_autonomy):
    """Obtiene los tipos de tanque disponibles según los parámetros seleccionados."""
    query = """
    SELECT DISTINCT
        CZE_TankType.Item_Tank_type,
        CZE_TankType.description
    FROM
        CZE_TankType
    JOIN 
        CZE_TankConfig ON CZE_TankType.Item_Tank_type = CZE_TankConfig.Item_Tank_type
    WHERE
        CZE_TankConfig.KWOutput = ?
        AND CZE_TankConfig.Item_Tank_Autonomy = ?
        AND CZE_TankConfig.Evaluator >= 1
    """
    try:
        with get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query, (kw_output, tank_autonomy))
            return [{"item": row[0], "description": row[1]} for row in cursor.fetchall()]
    except Exception as e:
        print(f"Error obteniendo tipos de tanque: {e}")
        return []

def get_spill_containment_catalog():
    """Obtiene las opciones de contención de derrames.
    Retorna una lista de diccionarios con item y descripción."""
    query = """
    SELECT DISTINCT
        [CZE_TankSpillCont].[Item_Tank_SpillCont],
        [CZE_TankSpillCont].[description]
    FROM
        [CZE_TankSpillCont]
    """
    try:
        with get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query)
            return [{
                "item": row[0],
                "description": row[1]
            } for row in cursor.fetchall()]
    except Exception as e:
        print(f"Error obteniendo opciones de contención de derrames: {e}")
        return []

#nuevas

def get_countrys():
    """Obtiene las opciones de garantías disponibles.
    Retorna una lista de diccionarios con item y descripción."""
    query = """
    SELECT DISTINCT
        [CZE_PrjWarCountry].[Item_PrjWar_Country],
        [CZE_PrjWarCountry].[description]
    FROM
        [CZE_PrjWarCountry]
    """
    try:
        with get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query)
            return [{
                "item": row[0],
                "description": row[1]
            } for row in cursor.fetchall()]
    except Exception as e:
        print(f"Error obteniendo opciones de garantía: {e}")
        return []






def get_warranty_catalog():
    """Obtiene las opciones de garantías disponibles.
    Retorna una lista de diccionarios con item y descripción."""
    query = """
    SELECT DISTINCT
        [CZE_PrjWarWarranty].[Item_PrjWar_Warranty],
        [CZE_PrjWarWarranty].[description]
    FROM
        [CZE_PrjWarWarranty]
    """
    try:
        with get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query)
            return [{
                "item": row[0],
                "description": row[1]
            } for row in cursor.fetchall()]
    except Exception as e:
        print(f"Error obteniendo opciones de garantía: {e}")
        return []

def get_testing_catalog(kw_output):
    """Obtiene las pruebas disponibles para el KW seleccionado."""
    query = """
    SELECT DISTINCT
        CZE_PrjWarTesting.Item_PrjWar_Testing,
        CZE_PrjWarTesting.description
    FROM
        CZE_PrjWarTesting
    JOIN 
        CZE_PrjWarTestConfig ON CZE_PrjWarTesting.Item_PrjWar_Testing = CZE_PrjWarTestConfig.Item_PrjWar_Testing
    WHERE
        CZE_PrjWarTestConfig.KWOutput = ?
        AND CZE_PrjWarTestConfig.Evaluator >= 1
    """
    try:
        with get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query, (kw_output))
            return [{"item": row[0], "description": row[1]} for row in cursor.fetchall()]
    except Exception as e:
        print(f"Error obteniendo pruebas: {e}")
        return []

def get_profile_info(user_id):
    """Obtiene la información del perfil completo del usuario.
    Retorna un diccionario con la información del perfil."""
    query = """
    SELECT 
        Users.UserID,
        Users.RequestTypeID,
        Profiles.ProfileID,
        Profiles.Discount,
        Profiles.BusinessName,
        Profiles.FiscalAddress,
        Profiles.PrimaryContact,
        Profiles.Email,
        Profiles.ContactPhone,
        Profiles.FirstName,
        Profiles.MiddleName,
        Profiles.LastName,
        Profiles.SecondLastName,
        Profiles.SellerUserID
    FROM 
        Users
    LEFT JOIN 
        Profiles ON Users.UserID = Profiles.UserID
    WHERE 
        Users.UserID = ?
    """
    try:
        conn = get_connection()
        if not conn:
            raise ConnectionError("No se pudo establecer conexión con la base de datos")

        cursor = conn.cursor()
        cursor.execute(query, (user_id,))
        result = cursor.fetchone()

        if result:
            return {
                "UserID": result[0],
                "RequestTypeID": result[1],
                "ProfileID": result[2],
                "Discount": result[3],
                "BusinessName": result[4],
                "FiscalAddress": result[5],
                "PrimaryContact": result[6],
                "Email": result[7],
                "ContactPhone": result[8],
                "FirstName": result[9],
                "MiddleName": result[10],
                "LastName": result[11],
                "SecondLastName": result[12],
                "SellerUserID": result[13]
            }
        return None
    except Exception as e:
        print(f"[Error inesperado] Error obteniendo información del perfil: {e}")
        return None
    finally:
        if conn:
            conn.close()

def get_seller_info(seller_user_id):
    """Obtiene la información del vendedor asignado a un distribuidor.
    Retorna un diccionario con la información del vendedor."""
    query = """
    SELECT 
        Profiles.FirstName,
        Profiles.MiddleName,
        Profiles.LastName,
        Profiles.SecondLastName,
        Profiles.Email,
        Profiles.ContactPhone
    FROM 
        Profiles
    WHERE 
        Profiles.UserID = ?
    """
    try:
        conn = get_connection()
        if not conn:
            raise ConnectionError("No se pudo establecer conexión con la base de datos")

        cursor = conn.cursor()
        cursor.execute(query, (seller_user_id,))
        result = cursor.fetchone()

        if result:
            return {
                "FirstName": result[0],
                "MiddleName": result[1],
                "LastName": result[2],
                "SecondLastName": result[3],
                "Email": result[4],
                "ContactPhone": result[5]
            }
        return None
    except Exception as e:
        print(f"[Error inesperado] Error obteniendo información del vendedor: {e}")
        return None
    finally:
        if conn:
            conn.close()

def get_discount_country_value(quote_id):
    """
    Obtiene el valor del descuento por pais, en función del id de la cotización
    """

    query = """
        SELECT
            CZE_Quotes.QuoteID,
            CZE_Quotes.Destiny_Country,
            CZE_Quotes.Destiny_State,
            CZE_Countries.isDiscount,
            CZE_Countries.percents
        FROM 
            CZE_Quotes  
            JOIN CZE_Countries 
            ON  CZE_Quotes.Destiny_Country = CZE_Countries.CountryID

        WHERE 
            CZE_Quotes.QuoteID = ?

    """

    try:
       with get_connection() as conn:
           cursor = conn.cursor()
           cursor.execute(query, (quote_id, ))
           result = cursor.fetchone()

           if result:
               return  result
               
           else:
                return None
           

    except Exception as e:
        print(f"Error al obtener datos de Country: {e}")
        raise

def update_commercial_country_values(quote_id, country_discount, commercial_offer):
    """
    Actualiza los valores de los precios de CommercialOffer y de CountryDiscount en la 
    tabla de CZE_Quote
    """

    query = """
    
        UPDATE CZE_Quotes 
        SET 
            Country_Discount = ?,
            Commercial_Offer = ?
        WHERE
            QuoteID  = ?

    """

    try:
        with get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query,(
                country_discount, 
                commercial_offer, 
                quote_id
            ))
            conn.commit()
            print("EXITO, VALORES COMERCIALES ACTUALIZADOS CON EXITO")

    except Exception as e:
        print(f"Error actualizado los precios de la cotizaciòn {quote_id}: {e}")
        raise



def save_quote(data):
    """Guarda una nueva cotización en la base de datos y retorna el QuoteID."""
    
    # Definir los campos en orden
    fields = [
        # Información General
        'PlateRating', 'FuelTypeIDBack', 'FuelTypeFront', 'KWOutput',
        'EngineBrandID', 'EngineBrand', 'AlternatorBrandID', 'AlternatorBrand',
        'VoltageID', 'Voltage_Front', 'CertificationID', 'Certification',
        'QtyGenset', 'QtyBreakers', 'FrontQtyBreakers',

        # Interruptor 1
        'Item_breaker1', 'Breaker1', 'Item_Sunt_t1', 'Shunt_trip1',
        'Item_Sunt_w1', 'Shunt_trip_wiring1', 'Item_GFI1', 'GFI1',
        'Item_Proyection_t1', 'Protection_type1', 'Item_Auxiliary_c1', 'Auxiliary_contacts1',
        'Item_Breaker_L1', 'Breaker_Lock1', 'Item_Motorized_B1', 'Motorized_Breaker1',

        # Interruptor 2
        'Item_breaker2', 'Breaker2', 'Item_Sunt_t2', 'Shunt_trip2',
        'Item_Sunt_w2', 'Shunt_trip_wiring2', 'Item_GFI2', 'GFI2',
        'Item_Proyection_t2', 'Protection_type2', 'Item_Auxiliary_c2', 'Auxiliary_contacts2',
        'Item_Breaker_L2', 'Breaker_Lock2', 'Item_Motorized_B2', 'Motorized_Breaker2',

        # Interruptor 3
        'Item_breaker3', 'Breaker3', 'Item_Sunt_t3', 'Shunt_trip3',
        'Item_Sunt_w3', 'Shunt_trip_wiring3', 'Item_GFI3', 'GFI3',
        'Item_Proyection_t3', 'Protection_type3', 'Item_Auxiliary_c3', 'Auxiliary_contacts3',
        'Item_Breaker_L3', 'Breaker_Lock3', 'Item_Motorized_B3', 'Motorized_Breaker3',

        # Control
        'ControllerBrandID', 'Controlbrand', 'Item_controller', 'ControlModel',
        'Item_CtrlPanelHeater', 'CtrlPanelHeater', 'Item_RempteDisplMod', 'RemoteDisplayModule',
        'Item_Antena', 'Antena', 'Item_DSE890', 'DSE890',
        'Item_DSE2157', 'DSE2157', 'QtyDSE2157',
        'Item_DSE2548', 'DSE2548', 'QtyDSE2548',
        'Item_CtrlPanelHeaterCMP','CtrlPanelHeaterCMP' , 'Item_RempteDisplModCMP', 'RemoteDisplayModuleCMP',
        'Item_AntenaCMP', 'AntenaCMP',
        'Item_ComapDSE890', 'ComapDSE890',
        'Item_ComapDSE2157', 'ComapDSE2157', 'QtyComapDSE2157',
        'Item_ComapDSE2548', 'ComapDSE2548', 'QtyComapDSE2548',
        'Item_CtrlVotlOper', 'CtrlVotlOper',
        'Item_BateryCharger', 'BateryCharger',

        # Caseta
        'Item_Enclosuree_type', 'Enclosure',
        'Item_Eclousure_Mat', 'EnclosureType',
        'Item_GFCI120V', 'GFCI120V', 'QtyGFCI120V',
        'Item_Receptable120V', 'Receptable120V', 'QtyReceptable120V',
        'Item_Enclosure_ligthAC', 'lightAC', 'QtylightAC',
        'Item_Enclosure_ligthDC', 'lightDC', 'QtylightDC',
        'Item_Enclosure_SHeater', 'SpaceHeater', 'QtySpaceHeater',
        'Item_Enclosure_LCenter', 'LoadCenter',
        'Item_PrewireAccsEncl', 'PrewireAccsEnclousure',
        'Item_autolouvers', 'autolouvers',
        'Item_Receptautolouvers', 'Receptautolouvers',
        
        'Item_OpeningEnclousure', 'OpeningEnclousure',
        'Item_StopButtonEnclosure', 'StopButtonEnclosure', 'StopButtonEnclosure_Qty',

        # Accesorios
        'Item_BandHeater', 'BandHeater',
        'Item_PMGUpdate', 'PMGUpdate',
        'Item_DigitalRegulator', 'DigitalRegulator',
        'Item_VoltageRheostat', 'VoltageRheostat',
        'Item_ThermalWrap', 'ThermalWrap',
        'Item_BaseHeater', 'BaseHeater',
        'Item_BatterySwitch', 'BatterySwitch',
        'Item_BatteryHolder', 'BatteryHolder',
        'Item_OilHeater', 'OilHeater',
        'Item_OilLevelSwitch', 'OilLevelSwitch',
        'Item_CommonAlarmRelay', 'CommonAlarmRelay',
        'Item_FunctionRelay', 'FunctionRelay',
        'Item_Coolant', 'Coolant',
        'Item_Oil', 'Oil',
        'Item_DisconnectSwitches', 'DisconnectSwitches',
        'Item_AddAccesory_Estop', 'EStop', 'QtyEStop',
        'Item_RemoteEStop', 'RemoteEStop',
        'Item_AddAccesory_Spring', 'SpringIsolator',

        # Tanque
        'Item_Tank_Autonomy', 'TankCapacity',
        'Item_Tank_type', 'TankType',
        'Item_BreakTraySwitch', 'BreakTraySwitch',
        'Item_FuelLevelSensor', 'FuelLevelSensor',
        'Item_Tank_SpillCont', 'SpillContainment',
        'Item_OverfillValve', 'OverfillValve',
        'Item_DualFuelSwitch', 'DualFuelSwitches',
        'Item_HighFuelSwitch', 'HighFuelSwitch',
        'Item_RemoteAlarmP', 'RemoteAlarmPanel',
        'Item_VentPipe', 'VentPipe',
        'Item_FlammableL', 'FlammableLiquids',
        'Item_NFPAIdentificat', 'NFPAIdentification',
        'Item_NoSmoking', 'NoSmoking',
        'Item_TankNumber', 'TankNumber',
        'Item_FluidContainment', 'FluidContainment',
        'Item_louvers_in','louvers_in',
        'Item_louvers_out','louvers_out',

        # Proyecto
        'Project',
        'Item_PrjWar_Warranty', 'Garanty',
        'Item_PrjWar_Testing', 'Teasting',
        'ReqCustomer',
        'Discount',
        'CustomerCompany', 'CustomerAddress', 'CustomerContact', 
        'CustomerEmail', 'CustomerTel', 
        'SellerName', 'SellerEmail', 'SellerPhone',
        'Destiny_Country', 'Destiny_State'
        
    ]
    
    # Construir la consulta SQL
    field_list = ', '.join(fields)
    placeholders = ', '.join(['?'] * len(fields))
    
    query = f"""
        INSERT INTO CZE_Quotes ({field_list})
        OUTPUT INSERTED.QuoteID
        VALUES ({placeholders})
    """
    
    # Preparar los valores manteniendo el orden de los campos
    values = [data.get(field) for field in fields]
    
    try:
        with get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query, values)
            quote_id = cursor.fetchone()[0]  # Obtener el ID insertado
            conn.commit()
            return quote_id
            
    except Exception as e:
        print(f"Error guardando cotización: {e}")
        raise


def get_description(stop_id: str) -> List[str]:
    """
    Obtiene la descripción de una cotización por su ID.
    Retorna una lista con la(s) descripción(es).
    """
    with get_connection() as conn:
        with conn.cursor() as cursor:
            cursor.execute("select * from StopButtonEnclosureOptions WHERE stop_id = ?", (stop_id,))
            result = cursor.fetchone()
            if result:
                return result[0]
            return None

# ok discount
def get_quote_data(quote_id):
    """Obtiene los datos necesarios de una cotización específica."""
    query = """
        SELECT 
            FuelTypeIDBack,          -- 0
            KWOutput,                -- 1
            EngineBrandID,           -- 2
            AlternatorBrandID,       -- 3
            VoltageID,               -- 4
            CertificationID,         -- 5
            ControllerBrandID,       -- 6
            QtyGenset,               -- 7

            -- SECCION DE LOS BREAKERS
            -- Breaker gral
            Item_breaker1,           -- 8
            Item_breaker2,           -- 9
            Item_breaker3,           -- 10
            Item_Proyection_t1,      -- 11
            Item_Proyection_t2,      -- 12
            Item_Proyection_t3,      -- 13

            -- Breaker 1 options
            Item_Sunt_t1,            -- 14
            Shunt_trip1,             -- 15
            Item_Sunt_w1,            -- 16
            Shunt_trip_wiring1,      -- 17
            Item_GFI1,               -- 18
            GFI1,                    -- 19
            Item_Auxiliary_c1,       -- 20
            Auxiliary_contacts1,     -- 21
            Item_Breaker_L1,         -- 22
            Breaker_Lock1,           -- 23
            Item_Motorized_B1,       -- 24
            Motorized_Breaker1,      -- 25

            -- Breaker 2 options
            Item_Sunt_t2,            -- 26
            Shunt_trip2,             -- 27
            Item_Sunt_w2,            -- 28
            Shunt_trip_wiring2,      -- 29
            Item_GFI2,               -- 30
            GFI2,                    -- 31
            Item_Auxiliary_c2,       -- 32
            Auxiliary_contacts2,     -- 33
            Item_Breaker_L2,         -- 34
            Breaker_Lock2,           -- 35
            Item_Motorized_B2,       -- 36
            Motorized_Breaker2,      -- 37

            -- Breaker 3 options
            Item_Sunt_t3,            -- 38
            Shunt_trip3,             -- 39
            Item_Sunt_w3,            -- 40
            Shunt_trip_wiring3,      -- 41
            Item_GFI3,               -- 42
            GFI3,                    -- 43
            Item_Auxiliary_c3,       -- 44
            Auxiliary_contacts3,     -- 45
            Item_Breaker_L3,         -- 46
            Breaker_Lock3,           -- 47
            Item_Motorized_B3,       -- 48
            Motorized_Breaker3,      -- 49

            -- SECCION DEL CONTROLLER
            -- Controller gral
            Item_controller,         -- 50
            ControlModel,            -- 51
            Item_CtrlVotlOper,       -- 52
            CtrlVotlOper,            -- 53
            Item_BateryCharger,      -- 54
            BateryCharger,           -- 55

            -- Controller options Si/NO con cantidad
            Item_DSE2157,           -- 56
            DSE2157,                -- 57
            QtyDSE2157,             -- 58
            Item_DSE2548,           -- 59
            DSE2548,                -- 60
            QtyDSE2548,             -- 61
            Item_ComapDSE2157,      -- 62
            ComapDSE2157,           -- 63
            QtyComapDSE2157,        -- 64
            Item_ComapDSE2548,      -- 65
            ComapDSE2548,           -- 66
            QtyComapDSE2548,        -- 67

            -- Controller options Si/NO sin cantidad
            Item_ComapDSE890,       -- 68
            ComapDSE890,            -- 69
            Item_CtrlPanelHeater,   -- 70
            CtrlPanelHeater,        -- 71
            Item_RempteDisplMod,    -- 72
            RemoteDisplayModule,    -- 73
            Item_Antena,            -- 74
            Antena,                 -- 75
            Item_DSE890,            -- 76
            DSE890,                 -- 77

            -- SECCION DEL ENLOSURE
            -- Indices para Enclosure
            Item_Enclosuree_type,   -- 78
            Item_Eclousure_Mat,     -- 79

            Item_Enclosure_ligthAC, -- 80
            lightAC,                -- 81
            QtylightAC,             -- 82

            Item_Enclosure_ligthDC, -- 83
            lightDC,                -- 84
            QtylightDC,             -- 85

            Item_Enclosure_SHeater, -- 86
            SpaceHeater,            -- 87
            QtySpaceHeater,         -- 88

            Item_Enclosure_LCenter, -- 89
            LoadCenter,             -- 90

            Item_GFCI120V,          -- 91
            GFCI120V,               -- 92
            QtyGFCI120V,            -- 93

            Item_Receptable120V,    -- 94
            Receptable120V,         -- 95
            QtyReceptable120V,      -- 96

            Item_PrewireAccsEncl,   -- 97
            PrewireAccsEnclousure,  -- 98

            Item_autolouvers,       -- 99
            autolouvers,            -- 100

            Item_Receptautolouvers, -- 101
            Receptautolouvers,      -- 102
            
            -- SECCION DEL ACCESORIOS
            Item_AddAccesory_Estop, -- 103
            EStop,                  -- 104
            QtyEStop,               -- 105

            Item_AddAccesory_Spring,-- 106
            SpringIsolator,         -- 107

            Item_BandHeater,        -- 108
            BandHeater,             -- 109

            Item_PMGUpdate,         -- 110
            PMGUpdate,              -- 111

            Item_DigitalRegulator,  -- 112
            DigitalRegulator,       -- 113

            Item_VoltageRheostat,   -- 114
            VoltageRheostat,        -- 115

            Item_ThermalWrap,       -- 116
            ThermalWrap,            -- 117

            Item_BaseHeater,        -- 118
            BaseHeater,             -- 119

            Item_BatterySwitch,     -- 120
            BatterySwitch,          -- 121

            Item_BatteryHolder,     -- 122
            BatteryHolder,          -- 123

            Item_OilHeater,         -- 124
            OilHeater,              -- 125

            Item_OilLevelSwitch,    -- 126
            OilLevelSwitch,         -- 127

            Item_CommonAlarmRelay,  -- 128
            CommonAlarmRelay,       -- 129

            Item_FunctionRelay,     -- 130
            FunctionRelay,          -- 131

            Item_Coolant,           -- 132
            Coolant,                -- 133

            Item_Oil,               -- 134
            Oil,                    -- 135

            Item_DisconnectSwitches,-- 136
            DisconnectSwitches,     -- 137

            Item_RemoteEStop,       -- 138
            RemoteEStop,            -- 139
            
            -- SECCION DE TANQUE
            --Tank
            Item_Tank_Autonomy,     -- 140
            TankCapacity,           -- 141
            Item_Tank_type,         -- 142
            TankType,               -- 143

            --TankOptions
            Item_BreakTraySwitch,   -- 144
            BreakTraySwitch,        -- 145

            Item_FuelLevelSensor,   -- 146
            FuelLevelSensor,        -- 147

            Item_Tank_SpillCont,    -- 148
            SpillContainment,       -- 149

            Item_OverfillValve,     -- 150
            OverfillValve,          -- 151

            Item_DualFuelSwitch,    -- 152
            DualFuelSwitches,       -- 153

            Item_HighFuelSwitch,    -- 154
            HighFuelSwitch,         -- 155

            Item_RemoteAlarmP,      -- 156
            RemoteAlarmPanel,       -- 157

            Item_VentPipe,          -- 158
            VentPipe,               -- 159

            Item_FlammableL,        -- 160
            FlammableLiquids,       -- 161

            Item_NFPAIdentificat,   -- 162
            NFPAIdentification,     -- 163

            Item_NoSmoking,         -- 164
            NoSmoking,              -- 165

            Item_TankNumber,        -- 166
            TankNumber,             -- 167

            Item_FluidContainment,  -- 168
            FluidContainment,       -- 169
            
            -- SECCION DE ULTIMOS DETALLES
            --Warranty
            Item_PrjWar_Warranty,   -- 170
            Garanty,                -- 171
            Garanty_price,          -- 172
            Garanty_Percentage,     -- 173

            --Testing
            Item_PrjWar_Testing,    -- 174
            Teasting,               -- 175
            
            Genset_Unit_Price,      -- 176
            Discount,                -- 177

            Item_CtrlPanelHeaterCMP,   -- 178
            CtrlPanelHeaterCMP,        -- 179
            Item_RempteDisplModCMP,    -- 180
            RemoteDisplayModuleCMP,    -- 181
            Item_AntenaCMP,            -- 182
            AntenaCMP,                 -- 183
            
            Item_OpeningEnclousure,    -- 184
            OpeningEnclousure,         -- 185
            
            Item_StopButtonEnclosure,  -- 186
            StopButtonEnclosure,       -- 187
            StopButtonEnclosure_Qty,    -- 188

            Item_louvers_in,          -- 189
            louvers_in,               -- 190

            Item_louvers_out,         -- 191
            louvers_out              -- 192

        FROM CZE_Quotes
        WHERE QuoteID = ?
    """
    
    try:
        with get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query, (quote_id,))
            return cursor.fetchone()
    except Exception as e:
        print(f"Error obteniendo datos de la cotización: {e}")
        raise

# ok discount
def get_genset_data(fuel_type, kw_output, engine_brand, alternator_brand, voltage_id, certification_id, controller_brand, QtyGenset, Discount=0):
    """Obtiene los datos del generador según los parámetros seleccionados."""
    
    query = """
    SELECT DISTINCT
        CZE_GensetCost.CostID,
        ISNULL(CZE_GensetCost.Cost, 0) * ((100 - ?) / 100) AS price,
        CZE_GensetCost.TechnicalSheet,
        ((100 - ?) / 100) * ? * ISNULL(CZE_GensetCost.Cost, 0) AS Genset_PriceTot,
        CZE_Genset.EngineModel,
        CZE_EngineModel.TechnicalSheet,
        CZE_Genset.Engine_Tier,
        CZE_Genset.Engine_Rpm,
        CZE_Genset.Engine_Power,
        CZE_Genset.Engine_NumberOfCylinder,
        CZE_Genset.Engine_Aspiration,
        CZE_Genset.Engine_Governor_type,
        CZE_Genset.Engine_Control_voltage,
        CZE_Genset.Engine_Battery_type,
        CZE_Genset.Engine_Hotstart_model,
        CZE_Genset.Engine_RadiatorTemp,
        CZE_GensetConfig.AlternatorModel,
        CZE_AlternatorModel.TechnicalSheet,
        CZE_GensetConfig.Powerfactor,
        CZE_GensetConfig.Insulation_System,
        CZE_GensetConfig.Control_System,
        CZE_GensetConfig.Protection,
        CZE_GensetConfig.AvrModel,
        CZE_GensetConfig.Voltage_Regulation,
        CZE_GensetConfig.Class_temp_rise
    FROM
        CZE_Genset
    JOIN
        CZE_EngineModel ON CZE_Genset.EngineModel = CZE_EngineModel.EngineModel
    JOIN
        CZE_FuelType ON CZE_EngineModel.FuelTypeBack = CZE_FuelType.Back
    JOIN
        CZE_EngineBrand ON CZE_EngineModel.EngineBrandID = CZE_EngineBrand.EngineBrandID
    JOIN
        CZE_AlternatorBrand ON CZE_Genset.AlternatorBrandID = CZE_AlternatorBrand.AlternatorBrandID
    JOIN
        CZE_GensetConfig ON CZE_Genset.GensetModel = CZE_GensetConfig.GensetModel
    JOIN
        CZE_AlternatorModel ON CZE_GensetConfig.AlternatorModel = CZE_AlternatorModel.AlternatorModel
    JOIN
        CZE_Voltage ON CZE_GensetConfig.VoltageID = CZE_Voltage.VoltageID
    JOIN
        CZE_Certification ON CZE_GensetConfig.CertificationID = CZE_Certification.CertificationID
    JOIN
        CZE_GensetCost ON CZE_GensetConfig.ConfigID = CZE_GensetCost.ConfigID
    JOIN
        CZE_ControllerBrand ON CZE_GensetCost.ControllerBrandID = CZE_ControllerBrand.BrandID
    WHERE
        CZE_Genset.Active = 1
        AND CZE_EngineBrand.Active = 1
        AND CZE_AlternatorBrand.Active = 1
        AND CZE_FuelType.Front = ?
        AND CZE_Genset.KWOutput = ?
        AND CZE_EngineBrand.EngineBrandID = ?
        AND CZE_AlternatorBrand.AlternatorBrandID = ?
        AND CZE_Voltage.VoltageID = ?
        AND CZE_Certification.CertificationID = ?
        AND CZE_ControllerBrand.BrandID = ?
        AND CZE_GensetCost.Evaluator >= 1
    """
    
    try:
        with get_connection() as conn:
            cursor = conn.cursor()
            
            cursor.execute(query, (Discount, Discount, QtyGenset, fuel_type, kw_output, engine_brand, alternator_brand,
                                voltage_id, certification_id, controller_brand))  # QtyGenset primero
            result = cursor.fetchone()

            if result:
                return {
                    'cost_id': result[0],
                    'unit_price': result[1],
                    'tech_sheet': result[2],
                    'Genset_PriceTot': result[3],
                    'EngineModel': result[4],
                    'EngineTSheet': result[5],
                    'E_Tier': result[6],
                    'E_Rpm': result[7],
                    'E_Power': result[8],
                    'E_NumberOfCylinder': result[9],
                    'E_Aspiration': result[10],
                    'E_Governor_type': result[11],
                    'E_Control_voltage': result[12],
                    'E_Battery_type': result[13],
                    'E_Hotstart_model': result[14],
                    'E_RadiatorTemp': result[15],
                    'AlternatorModel': result[16],
                    'AlternatorTSheet': result[17],
                    'A_Powerfactor': result[18],
                    'A_Insulation_System': result[19],
                    'A_Control_System': result[20],
                    'A_Protection': result[21],
                    'A_AvrModel': result[22],
                    'A_Voltage_Regulation': result[23],
                    'A_Class_temp_rise': result[24]
                }
            return None
    except Exception as e:
        print(f"Error obteniendo datos del generador: {e}")
        raise

# ok discount
def update_genset_pricing(quote_id, cost_id, unit_price, tech_sheet, Genset_PriceTot, 
                        EngineModel, EngineTSheet, E_Tier, E_Rpm, E_Power, E_NumberOfCylinder, 
                        E_Aspiration, E_Governor_type, E_Control_voltage, E_Battery_type, 
                        E_Hotstart_model, E_RadiatorTemp, AlternatorModel, AlternatorTSheet, 
                        A_Powerfactor, A_Insulation_System, A_Control_System, A_Protection, 
                        A_AvrModel, A_Voltage_Regulation, A_Class_temp_rise):
    """Actualiza los precios y ficha técnica del generador en la cotización."""
    
    query = """
    UPDATE CZE_Quotes
    SET 
        Genset_CostID = ?,
        Genset_Unit_Price = ?,
        Genset_TSheet = ?,
        Genset_PriceTot = ?,
        EngineModel = ?,
        Engine_TSheet = ?,
        Engine_Tier = ?,
        Engine_Rpm = ?,
        Engine_Power = ?,
        Engine_NumberOfCylinder = ?,
        Engine_Aspiration = ?,
        Engine_Governor_type = ?,
        Engine_Control_voltage = ?,
        Engine_Battery_type = ?,
        Engine_Hotstart_model = ?,
        Engine_RadiatorTemp = ?,
        AlternatorModel = ?,
        Altern_TSheet = ?,
        Altern_Powerfactor = ?,
        Altern_Insulation_Sys = ?,
        Altern_Control_System = ?,
        Altern_Protection = ?,
        Altern_AvrModel = ?,
        Altern_VoltageRegulat = ?,
        Altern_Class_temp_rise = ?
    WHERE QuoteID = ?
    """
    
    try:
        with get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query, (cost_id, unit_price, tech_sheet, Genset_PriceTot, EngineModel, 
                                EngineTSheet, E_Tier, E_Rpm, E_Power, E_NumberOfCylinder, E_Aspiration, 
                                E_Governor_type, E_Control_voltage, E_Battery_type, E_Hotstart_model, 
                                E_RadiatorTemp, AlternatorModel, AlternatorTSheet, A_Powerfactor, 
                                A_Insulation_System, A_Control_System, A_Protection, A_AvrModel, 
                                A_Voltage_Regulation, A_Class_temp_rise, quote_id))
            conn.commit()
            
    except Exception as e:
        print(f"Error actualizando precios del generador: {e}")
        raise

# ok discount    
def get_breaker_data(item_breaker, qty_genset, Discount=0):
    """Obtiene los datos del breaker según el item seleccionado."""
    
    query = """
    SELECT 
        CZE_BreakersCatalog.Item_breaker,
        ISNULL(CZE_BreakersCatalog.price, 0) * ? AS price,
        CZE_BreakersCatalog.TechnicalSheet,
        ? * ? * ISNULL(CZE_BreakersCatalog.price, 0) AS price_tot
    FROM 
        CZE_BreakersCatalog
    WHERE
        CZE_BreakersCatalog.Item_breaker = ?
    """
    
    try:
        with get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query, (Discount, Discount, qty_genset, item_breaker))
            result = cursor.fetchone()
            
            if result:
                return {
                    'item': result[0],
                    'price': result[1],
                    'tech_sheet': result[2],
                    'price_tot': result[3]
                }
            return None
            
    except Exception as e:
        print(f"Error obteniendo datos del breaker: {e}")
        raise

# ok discount
def get_protection_data(item_protection, qty_genset, Discount=0):
    """Obtiene los datos de la protección según el item seleccionado."""
    
    query = """
    SELECT 
        CZE_BreakerProtectionType.Item_breaker_Protection,
        ISNULL(CZE_BreakerProtectionType.price, 0) * ? AS price,
        CZE_BreakerProtectionType.TechnicalSheet,
        ? * ? * ISNULL(CZE_BreakerProtectionType.price, 0) AS price_tot
    FROM 
        CZE_BreakerProtectionType
    WHERE
        CZE_BreakerProtectionType.Item_breaker_Protection = ?
    """
    
    try:
        with get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query, (Discount, Discount, qty_genset, item_protection))
            result = cursor.fetchone()
            
            if result:
                return {
                    'item': result[0],
                    'price': result[1],
                    'tech_sheet': result[2],
                    'price_tot': result[3]
                }
            return None
            
    except Exception as e:
        print(f"Error obteniendo datos de la protección: {e}")
        raise

# ok discount
def update_breaker_pricing(quote_id, breaker_num, breaker_data):
    """Actualiza los precios y ficha técnica del breaker en la cotización."""
    
    query = f"""
    UPDATE CZE_Quotes
    SET 
        Breaker{breaker_num}_Price = ?,
        Breaker{breaker_num}_TSheet = ?,
        Breaker{breaker_num}_PriceTot = ?
    WHERE QuoteID = ?
    """
    
    try:
        with get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query, (
                breaker_data['price'], 
                breaker_data['tech_sheet'],
                breaker_data['price_tot'],
                quote_id
            ))
            conn.commit()
            
    except Exception as e:
        print(f"Error actualizando precios del breaker {breaker_num}: {e}")
        raise
    
    
    
# ok discount
def update_protection_pricing(quote_id, protection_num, protection_data):
    """Actualiza los precios y ficha técnica de la protección en la cotización."""
    
    query = f"""
    UPDATE CZE_Quotes
    SET 
        Proyection_t{protection_num}_Price = ?,
        Proyection_t{protection_num}_TSheet = ?,
        Proyection_t{protection_num}_PriceTot = ?
    WHERE QuoteID = ?
    """
    
    try:
        with get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query, (
                protection_data['price'], 
                protection_data['tech_sheet'],
                protection_data['price_tot'],
                quote_id
            ))
            conn.commit()
            
    except Exception as e:
        print(f"Error actualizando precios de la protección {protection_num}: {e}")
        raise

# ok discount
def get_breaker_option_data(item_option, qty_genset, Discount=0):
    """Obtiene los datos de la opción del breaker según el item seleccionado."""
    query = """
    SELECT 
        CZE_BreakersOptions.Item_breaker_options,
        ISNULL(CZE_BreakersOptions.price, 0) * ? AS price,
        CZE_BreakersOptions.TechnicalSheet,
        ? * ? * ISNULL(CZE_BreakersOptions.price, 0) AS price_tot
    FROM 
        CZE_BreakersOptions
    WHERE
        CZE_BreakersOptions.Item_breaker_options = ?
    """
    
    try:
        with get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query, (Discount, Discount, qty_genset, item_option))
            result = cursor.fetchone()
            
            if result:
                return {
                    'item': result[0],
                    'price': result[1],
                    'tech_sheet': result[2],
                    'price_tot': result[3]
                }
            return None
            
    except Exception as e:
        print(f"Error obteniendo datos de opción de breaker: {e}")
        raise

# ok discount
def update_breaker_option_pricing(quote_id, breaker_num, option_type, option_data):
    """Actualiza los precios y ficha técnica de la opción del breaker en la cotización."""
    
    # Mapeo de tipos de opciones a nombres de columnas
    option_mappings = {
        'sunt_t': 'Sunt_t',
        'sunt_w': 'Sunt_w',
        'gfi': 'GFI',
        'aux_c': 'Auxiliary_c',
        'breaker_l': 'Breaker_L',
        'motorized': 'Motorized_B'
    }
    
    prefix = option_mappings.get(option_type)
    if not prefix:
        raise ValueError(f"Tipo de opción no válido: {option_type}")
    
    query = f"""
    UPDATE CZE_Quotes
    SET 
        {prefix}{breaker_num}_Price = ?,
        {prefix}{breaker_num}_TSheet = ?,
        {prefix}{breaker_num}_PriceTot = ?
    WHERE QuoteID = ?
    """
    
    try:
        with get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query, (
                option_data['price'],
                option_data['tech_sheet'],
                option_data['price_tot'],
                quote_id
            ))
            conn.commit()
            
    except Exception as e:
        print(f"Error actualizando precios de opción {option_type} del breaker {breaker_num}: {e}")
        raise

# ok discount
def get_controller_data(item_controller, qty_genset, Discount=0):
    """Obtiene los datos del controlador según el item seleccionado."""
    query = """
    SELECT DISTINCT
        CZE_ControllerCatalog.Item_controller,
        ISNULL(CZE_ControllerCatalog.price, 0) * ? AS price,
        CZE_ControllerCatalog.TechnicalSheet,
        ? * ? * ISNULL(CZE_ControllerCatalog.price, 0) AS priceTot
    FROM 
        CZE_ControllerCatalog
    WHERE
        CZE_ControllerCatalog.Item_controller = ?
    """

    try:
        with get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query, (Discount, Discount, qty_genset, item_controller))
            result = cursor.fetchone()
            
            if result:
                return {
                    'item': result[0],
                    'price': result[1],
                    'tech_sheet': result[2],
                    'price_tot': result[3]
                }
            return None
    except Exception as e:
        print(f"Error obteniendo datos del controlador: {e}")
        raise

# ok discount
def get_preheater_data(item_preheater, qty_genset, Discount=0):
    """Obtiene los datos del precalentador según el item seleccionado."""
    query = """
    SELECT 
        CZE_ControllerPreheater.Item_preheater_volt,
        ISNULL(CZE_ControllerPreheater.price, 0) * ? AS price,
        CZE_ControllerPreheater.TechnicalSheet,
        ? * ? * ISNULL(CZE_ControllerPreheater.price, 0) AS priceTot
    FROM 
        CZE_ControllerPreheater
    WHERE
        CZE_ControllerPreheater.Item_preheater_volt = ?
    """
    
    try:
        with get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query, (Discount, Discount, qty_genset, item_preheater))
            result = cursor.fetchone()
            
            if result:
                return {
                    'item': result[0],
                    'price': result[1],
                    'tech_sheet': result[2],
                    'price_tot': result[3]
                }
            return None
    except Exception as e:
        print(f"Error obteniendo datos del precalentador: {e}")
        raise

# ok discount
def get_battery_charger_data(item_charger, qty_genset, Discount=0):
    """Obtiene los datos del cargador de batería según el item seleccionado."""
    query = """
    SELECT 
        CZE_ControllerBattCharger.Item_batterycharger,
        ISNULL(CZE_ControllerBattCharger.price, 0) * ? AS price,
        CZE_ControllerBattCharger.TechnicalSheet,
        ? * ? * ISNULL(CZE_ControllerBattCharger.price, 0) AS priceTot
    FROM 
        CZE_ControllerBattCharger
    WHERE
        CZE_ControllerBattCharger.Item_batterycharger = ?
    """
    
    try:
        with get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query, (Discount, Discount, qty_genset, item_charger))
            result = cursor.fetchone()
            
            if result:
                return {
                    'item': result[0],
                    'price': result[1],
                    'tech_sheet': result[2],
                    'price_tot': result[3]
                }
            return None
    except Exception as e:
        print(f"Error obteniendo datos del cargador de batería: {e}")
        raise

# ok discount
def update_controller_pricing(quote_id, controller_data):
    """Actualiza los precios del controlador en la cotización."""
    query = """
    UPDATE CZE_Quotes
    SET 
        controller_Price = ?,
        controller_TSheet = ?,
        ControlModel_PriceTot = ?
    WHERE QuoteID = ?
    """
    
    try:
        with get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query, (
                controller_data['price'],
                controller_data['tech_sheet'],
                controller_data['price_tot'],
                quote_id
            ))
            conn.commit()
    except Exception as e:
        print(f"Error actualizando precios del controlador: {e}")
        raise

# ok discount
def update_preheater_pricing(quote_id, preheater_data):
    """Actualiza los precios del precalentador en la cotización."""
    query = """
    UPDATE CZE_Quotes
    SET 
        CtrlVotlOper_Price = ?,
        CtrlVotlOper_TSheet = ?,
        CtrlVotlOper_PriceTot = ?
    WHERE QuoteID = ?
    """
    
    try:
        with get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query, (
                preheater_data['price'],
                preheater_data['tech_sheet'],
                preheater_data['price_tot'],
                quote_id
            ))
            conn.commit()
    except Exception as e:

        print(f"Error actualizando precios del precalentador: {e}")
        raise

# ok discount
def update_battery_charger_pricing(quote_id, charger_data):
    """Actualiza los precios del cargador de batería en la cotización."""
    query = """
    UPDATE CZE_Quotes
    SET 
        BateryCharger_Price = ?,
        BateryCharger_TSheet = ?,
        BateryCharger_PriceTot = ?
    WHERE QuoteID = ?
    """
    
    try:
        with get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query, (
                charger_data['price'],
                charger_data['tech_sheet'],
                charger_data['price_tot'],
                quote_id
            ))
            conn.commit()
    except Exception as e:
        print(f"Error actualizando precios del cargador de batería: {e}")
        raise

# ok discount    
def get_controller_option_qty_data(item_option, qty_genset, qty_item, Discount=0):
    """Obtiene los datos de la opción del controlador que tiene cantidad propia."""
    query = """
    SELECT 
        CZE_ControllerOptions.Item_controller_options,
        ISNULL(CZE_ControllerOptions.price, 0) * ? AS price,
        CZE_ControllerOptions.TechnicalSheet,
        ? * ? * ? * ISNULL(CZE_ControllerOptions.price, 0) AS priceTot
    FROM 
        CZE_ControllerOptions
    WHERE
        CZE_ControllerOptions.Item_controller_options = ?
    """
    
    try:
        
        with get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query, (Discount , Discount, qty_genset, qty_item, item_option))
            result = cursor.fetchone()
            
            if result:
                return {
                    'item': result[0],
                    'price': result[1],
                    'tech_sheet': result[2],
                    'price_tot': result[3]
                }
            return None
    except Exception as e:
        print(f"Error obteniendo datos de opción del controlador: {e}")
        raise

# ok discount 
def update_dse2157_pricing(quote_id, data):
    """Actualiza los precios del DSE2157 en la cotización."""
    query = """
    UPDATE CZE_Quotes
    SET 
        DSE2157_Price = ?,
        DSE2157_TSheet = ?,
        DSE2157_PriceTot = ?
    WHERE QuoteID = ?
    """
    
    try:
        with get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query, (
                data['price'],
                data['tech_sheet'],
                data['price_tot'],
                quote_id
            ))
            conn.commit()
    except Exception as e:
        print(f"Error actualizando precios de DSE2157: {e}")
        raise

# ok discount 
def update_dse2548_pricing(quote_id, data):
    """Actualiza los precios del DSE2548 en la cotización."""
    query = """
    UPDATE CZE_Quotes
    SET 
        DSE2548_Price = ?,
        DSE2548_TSheet = ?,
        DSE2548_PriceTot = ?
    WHERE QuoteID = ?
    """
    
    try:
        with get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query, (
                data['price'],
                data['tech_sheet'],
                data['price_tot'],
                quote_id
            ))
            conn.commit()
    except Exception as e:
        print(f"Error actualizando precios de DSE2548: {e}")
        raise

# ok discount 
def update_comap_dse2157_pricing(quote_id, data):
    """Actualiza los precios del ComapDSE2157 en la cotización."""
    query = """
    UPDATE CZE_Quotes
    SET 
        ComapDSE2157_Price = ?,
        ComapDSE2157_TSheet = ?,
        ComapDSE2157_PriceTot = ?
    WHERE QuoteID = ?
    """
    
    try:
        with get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query, (
                data['price'],
                data['tech_sheet'],
                data['price_tot'],
                quote_id
            ))
            conn.commit()
    except Exception as e:
        print(f"Error actualizando precios de ComapDSE2157: {e}")
        raise

# ok discount 
def update_comap_dse2548_pricing(quote_id, data):
    """Actualiza los precios del ComapDSE2548 en la cotización."""
    query = """
    UPDATE CZE_Quotes
    SET 
        ComapDSE2548_Price = ?,
        ComapDSE2548_TSheet = ?,
        ComapDSE2548_PriceTot = ?
    WHERE QuoteID = ?
    """
    
    try:
        with get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query, (
                data['price'],
                data['tech_sheet'],
                data['price_tot'],
                quote_id
            ))
            conn.commit()
    except Exception as e:
        print(f"Error actualizando precios de ComapDSE2548: {e}")
        raise

# ok discount     
def get_controller_option_data(item_option, qty_genset, Discount=0):
    """Obtiene los datos de la opción del controlador."""
    query = """
    SELECT 
        CZE_ControllerOptions.Item_controller_options,
        ISNULL(CZE_ControllerOptions.price, 0) * ? AS price,
        CZE_ControllerOptions.TechnicalSheet,
        ? * ? * ISNULL(CZE_ControllerOptions.price, 0) AS priceTot
    FROM 
        CZE_ControllerOptions
    WHERE
        CZE_ControllerOptions.Item_controller_options = ?
    """
    
    try:
        with get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query, (Discount, Discount, qty_genset, item_option))
            result = cursor.fetchone()
            
            if result:
                return {
                    'item': result[0],
                    'price': result[1],
                    'tech_sheet': result[2],
                    'price_tot': result[3]
                }
            return None
    except Exception as e:
        print(f"Error obteniendo datos de opción del controlador: {e}")
        raise

# ok discount
def update_controller_option_pricing(quote_id, option_type, data):
    """Actualiza los precios de la opción del controlador en la cotización."""
    
    # Mapeo de tipos de opciones a nombres de columnas
    option_mappings = {
        'comap_dse890': ('ComapDSE890', 'ComapDSE890'),
        'panel_heater': ('CtrlPanelHeater', 'CtrlPanelHeat'),
        'remote_display': ('RempteDisplMod', 'RempteDisplMod'),
        'antena': ('Antena', 'Antena'),
        'dse890': ('DSE890', 'DSE890'),

        'antenaCMP': ('AntenaCMP', 'AntenaCMP'),
        'panel_heaterCMP': ('CtrlPanelHeaterCMP', 'CtrlPanelHeatCMP'),
        'remote_displayCMP': ('RempteDisplModCMP', 'RempteDisplModCMP'),
    }
    
    price_prefix, total_prefix = option_mappings.get(option_type, (None, None))
    if not price_prefix:
        raise ValueError(f"Tipo de opción no válido: {option_type}")
    
    query = f"""
    UPDATE CZE_Quotes
    SET 
        {price_prefix}_Price = ?,
        {price_prefix}_TSheet = ?,
        {total_prefix}_PriceTot = ?
    WHERE QuoteID = ?
    """

    print(query)
    
    try:
        with get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query, (
                data['price'],
                data['tech_sheet'],
                data['price_tot'],
                quote_id
            ))
            conn.commit()
    except Exception as e:
        print(f"Error actualizando precios de opción {option_type}: {e}")
        raise

# ok discount    
def get_enclosure_data(fuel_type, kw_output, certification_id, enclosure_type, enclosure_mat, qty_genset, Discount=0):
    """Obtiene los datos de la caseta según los parámetros seleccionados."""
    query = """
    SELECT DISTINCT
        CZE_EnclosureConfig.ConfigID,
        ISNULL(CZE_EnclosureConfig.Cost, 0) * ? AS price,
        CZE_EnclosureConfig.TechnicalSheet,
        ? * ? * ISNULL(CZE_EnclosureConfig.Cost, 0) AS priceTot
    FROM
        CZE_EnclosureConfig
    WHERE
        CZE_EnclosureConfig.CertificationID = ?
        AND CZE_EnclosureConfig.Fueltype = ?
        AND CZE_EnclosureConfig.Item_Enclosure_type = ?
        AND CZE_EnclosureConfig.KWOutput = ?
        AND CZE_EnclosureConfig.Item_Enclosure_Mat = ?
        AND CZE_EnclosureConfig.Evaluator >= 1
    """
    
    try:
        with get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query, (Discount, Discount, qty_genset, certification_id, fuel_type, enclosure_type, 
                                kw_output, enclosure_mat))
            result = cursor.fetchone()
            
            if result:
                return {
                    'config_id': result[0],
                    'price': result[1],
                    'tech_sheet': result[2],
                    'price_tot': result[3]
                }
            return None
    except Exception as e:
        print(f"Error obteniendo datos de la caseta: {e}")
        raise

# ok discount 
def get_light_data(item_light, qty_genset, qty_light, Discount=0, is_ac=True):
    """Obtiene los datos de la iluminación (AC o DC) según el item seleccionado."""
    query = """
    SELECT 
        CZE_EnclosureLight.Item_Enclosure_ligth,
        ISNULL(CZE_EnclosureLight.price, 0) * ? AS price,
        CZE_EnclosureLight.TechnicalSheet,
        ? * ? * ? * ISNULL(CZE_EnclosureLight.price, 0) AS priceTot
    FROM 
        CZE_EnclosureLight
    WHERE
        CZE_EnclosureLight.Item_Enclosure_ligth = ?
    """
    
    try:
        with get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query, (Discount, Discount, qty_genset, qty_light, item_light))
            result = cursor.fetchone()
            
            if result:
                return {
                    'item': result[0],
                    'price': result[1],
                    'tech_sheet': result[2],
                    'price_tot': result[3]
                }
            return None
    except Exception as e:
        print(f"Error obteniendo datos de iluminación: {e}")
        raise

# ok discount 
def get_space_heater_data(item_heater, qty_genset, qty_heater, Discount=0):
    """Obtiene los datos del calentador de espacio según el item seleccionado."""
    query = """
    SELECT 
        CZE_EnclosureSpaceHeater.Item_Enclosure_SHeater,
        ISNULL(CZE_EnclosureSpaceHeater.price, 0) * ? AS price,
        CZE_EnclosureSpaceHeater.TechnicalSheet,
        ? * ? * ? * ISNULL(CZE_EnclosureSpaceHeater.price, 0) AS priceTot
    FROM 
        CZE_EnclosureSpaceHeater
    WHERE
        CZE_EnclosureSpaceHeater.Item_Enclosure_SHeater = ?
    """
    
    try:
        with get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query, (Discount, Discount, qty_genset, qty_heater, item_heater))
            result = cursor.fetchone()
            
            if result:
                return {
                    'item': result[0],
                    'price': result[1],
                    'tech_sheet': result[2],
                    'price_tot': result[3]
                }
            return None
    except Exception as e:
        print(f"Error obteniendo datos del calentador: {e}")
        raise

# ok discount 
def get_load_center_data(item_center, qty_genset, Discount=0):
    """Obtiene los datos del centro de carga según el item seleccionado."""
    query = """
    SELECT 
        CZE_EnclosureLoadCenter.Item_Enclosure_LCenter,
        ISNULL(CZE_EnclosureLoadCenter.price, 0) * ? AS price,
        CZE_EnclosureLoadCenter.TechnicalSheet,
        ? * ? * ISNULL(CZE_EnclosureLoadCenter.price, 0) AS priceTot
    FROM 
        CZE_EnclosureLoadCenter
    WHERE
        CZE_EnclosureLoadCenter.Item_Enclosure_LCenter = ?
    """
    
    try:
        with get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query, (Discount, Discount, qty_genset, item_center))
            result = cursor.fetchone()
            
            if result:
                return {
                    'item': result[0],
                    'price': result[1],
                    'tech_sheet': result[2],
                    'price_tot': result[3]
                }
            return None
    except Exception as e:
        print(f"Error obteniendo datos del centro de carga: {e}")
        raise

# ok discount 
def update_enclosure_pricing(quote_id, data):
    """Actualiza los precios de la caseta en la cotización."""
    query = """
    UPDATE CZE_Quotes
    SET 
        Enclosure_ConfigID = ?,
        Enclosure_price = ?,
        Enclosure_TSheet = ?,
        Enclosure_PriceTot = ?
    WHERE QuoteID = ?
    """
    
    try:
        with get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query, (
                data['config_id'],
                data['price'],
                data['tech_sheet'],
                data['price_tot'],
                quote_id
            ))
            conn.commit()
    except Exception as e:
        print(f"Error actualizando precios de la caseta: {e}")
        raise

# ok discount 
def update_enclosure_component_pricing(quote_id, component_type, data):
    """Actualiza los precios de los componentes de la caseta."""
    
    # Mapeo de tipos de componentes a nombres de columnas
    component_mappings = {
        'light_ac': 'lightAC',
        'light_dc': 'lightDC',
        'space_heater': 'SpaceHeater',
        'load_center': 'LoadCenter'
    }
    
    prefix = component_mappings.get(component_type)
    if not prefix:
        raise ValueError(f"Tipo de componente no válido: {component_type}")
    
    query = f"""
    UPDATE CZE_Quotes
    SET 
        {prefix}_price = ?,
        {prefix}_TSheet = ?,
        {prefix}_PriceTot = ?
    WHERE QuoteID = ?
    """
    
    try:
        with get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query, (
                data['price'],
                data['tech_sheet'],
                data['price_tot'],
                quote_id
            ))
            conn.commit()
    except Exception as e:
        print(f"Error actualizando precios del componente {component_type}: {e}")
        raise

# ok discount 
def get_enclosure_option_qty_data(item_option, qty_genset, qty_item, Discount=0):
    """Obtiene los datos de opciones de caseta que tienen cantidad propia."""
    query = """
    SELECT 
        CZE_EnclosureOption.Item_Enclosure_options,
        ISNULL(CZE_EnclosureOption.price, 0) * ? AS price,
        CZE_EnclosureOption.TechnicalSheet,
        ? * ? * ? * ISNULL(CZE_EnclosureOption.price, 0) AS priceTot
    FROM 
        CZE_EnclosureOption
    WHERE
        CZE_EnclosureOption.Item_Enclosure_options = ?
    """
    
    try:
        with get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query, (Discount, Discount, qty_genset, qty_item, item_option))
            result = cursor.fetchone()
            
            if result:
                return {
                    'item': result[0],
                    'price': result[1],
                    'tech_sheet': result[2],
                    'price_tot': result[3]
                }
            return None
    except Exception as e:
        print(f"Error obteniendo datos de opción de caseta: {e}")
        raise


# ok discount 
def get_enclosure_option_data(item_option, qty_genset, Discount=0):
    """Obtiene los datos de opciones de caseta sin cantidad propia."""
    query = """
    SELECT 
        CZE_EnclosureOption.Item_Enclosure_options,
        ISNULL(CZE_EnclosureOption.price, 0) * ? AS price,
        CZE_EnclosureOption.TechnicalSheet,
        ? * ? * ISNULL(CZE_EnclosureOption.price, 0) AS priceTot
    FROM 
        CZE_EnclosureOption
    WHERE
        CZE_EnclosureOption.Item_Enclosure_options = ?
    """
    
    try:
        with get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query, (Discount, Discount, qty_genset, item_option))
            result = cursor.fetchone()
            
            if result:
                return {
                    'item': result[0],
                    'price': result[1],
                    'tech_sheet': result[2],
                    'price_tot': result[3]
                }
            return None
    except Exception as e:
        print(f"Error obteniendo datos de opción de caseta: {e}")
        raise

# ok discount
def update_enclosure_option_pricing(quote_id, option_type, data):
    """Actualiza los precios de las opciones de la caseta."""
    
    # Mapeo de tipos de opciones a nombres de columnas
    option_mappings = {
        'gfci': ('GFCI120V', 'GFCI120V'),
        'receptable': ('Receptable120V', 'Receptable120V'),
        'prewire': ('PrewireAccsEnclo', 'PrewireAccsEnc'),
        'autolouvers': ('autolouvers', 'autolouvers'),
        'receptautolouvers': ('Receptautolouver', 'Receptautolouv'),
        'openingEnclousure': ('OpeningEnclousure', 'OpeningEnclousure'),
        'stopButtonEnclosure': ('StopButtonEnclosure', 'StopButtonEnclosure')
    }
    
    price_prefix, total_prefix = option_mappings.get(option_type, (None, None))
    if not price_prefix:
        raise ValueError(f"Tipo de opción no válido: {option_type}")
    
    query = f"""
    UPDATE CZE_Quotes
    SET 
        {price_prefix}_Price = ?,
        {price_prefix}_TSheet = ?,
        {total_prefix}_PriceTot = ?
    WHERE QuoteID = ?
    """
    
    try:
        with get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query, (
                data['price'],
                data['tech_sheet'],
                data['price_tot'],
                quote_id
            ))
            conn.commit()
    except Exception as e:
        print(f"Error actualizando precios de opción {option_type}: {e}")
        raise

# ok discount
def get_estop_data(item_estop, qty_genset, qty_estop, Discount=0):
    """Obtiene los datos del E-Stop según el item seleccionado."""
    query = """
    SELECT 
        CZE_AddAccesory_Estop.Item_AddAccesory_Estop,
        ISNULL(CZE_AddAccesory_Estop.price, 0) * ? AS price,
        CZE_AddAccesory_Estop.TechnicalSheet,
        ? * ? * ? * ISNULL(CZE_AddAccesory_Estop.price, 0) AS priceTot
    FROM 
        CZE_AddAccesory_Estop
    WHERE
        CZE_AddAccesory_Estop.Item_AddAccesory_Estop = ?
    """
    
    try:
        with get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query, (Discount, Discount, qty_genset, qty_estop, item_estop))
            result = cursor.fetchone()
            
            if result:
                return {
                    'item': result[0],
                    'price': result[1],
                    'tech_sheet': result[2],
                    'price_tot': result[3]
                }
            return None
    except Exception as e:
        print(f"Error obteniendo datos del E-Stop: {e}")
        raise

# ok discount
def get_spring_isolator_data(item_spring, qty_genset, Discount=0):
    """Obtiene los datos del aislador de resorte según el item seleccionado."""
    query = """
    SELECT 
        CZE_AddAccesory_Spring.Item_AddAccesory_Spring,
        ISNULL(CZE_AddAccesory_Spring.price, 0) * ? AS price,
        CZE_AddAccesory_Spring.TechnicalSheet,
        ? * ? * ISNULL(CZE_AddAccesory_Spring.price, 0) AS priceTot
    FROM 
        CZE_AddAccesory_Spring
    WHERE
        CZE_AddAccesory_Spring.Item_AddAccesory_Spring = ?
    """
    
    try:
        with get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query, (Discount, Discount, qty_genset, item_spring))
            result = cursor.fetchone()
            
            if result:
                return {
                    'item': result[0],
                    'price': result[1],
                    'tech_sheet': result[2],
                    'price_tot': result[3]
                }
            return None
    except Exception as e:
        print(f"Error obteniendo datos del aislador de resorte: {e}")
        raise

# ok discount
def update_estop_pricing(quote_id, data):
    """Actualiza los precios del E-Stop en la cotización."""
    query = """
    UPDATE CZE_Quotes
    SET 
        EStop_price = ?,
        EStop_TSheet = ?,
        EStop_PriceTot = ?
    WHERE QuoteID = ?
    """
    
    try:
        with get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query, (
                data['price'],
                data['tech_sheet'],
                data['price_tot'],
                quote_id
            ))
            conn.commit()
    except Exception as e:
        print(f"Error actualizando precios del E-Stop: {e}")
        raise

# ok discount
def update_spring_isolator_pricing(quote_id, data):
    """Actualiza los precios del aislador de resorte en la cotización."""
    query = """
    UPDATE CZE_Quotes
    SET 
        SpringIsolator_price = ?,
        SpringIsolator_TSheet = ?,
        SpringIsolator_PriceTot = ?
    WHERE QuoteID = ?
    """
    
    try:
        with get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query, (
                data['price'],
                data['tech_sheet'],
                data['price_tot'],
                quote_id
            ))
            conn.commit()
    except Exception as e:
        print(f"Error actualizando precios del aislador de resorte: {e}")
        raise

# ok discount
def get_accessory_option_data(item_option, qty_genset, Discount=0):
    """Obtiene los datos de la opción de accesorio."""
    query = """
    SELECT 
        CZE_AddAccesory_Option.Item_AddAccesory_option,
        ISNULL(CZE_AddAccesory_Option.price, 0) * ? AS price,
        CZE_AddAccesory_Option.TechnicalSheet,
        ? * ? * ISNULL(CZE_AddAccesory_Option.price, 0) AS priceTot
    FROM 
        CZE_AddAccesory_Option
    WHERE
        CZE_AddAccesory_Option.Item_AddAccesory_option = ?
    """
    
    try:
        with get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query, (Discount, Discount, qty_genset, item_option))
            result = cursor.fetchone()
            
            if result:
                return {
                    'item': result[0],
                    'price': result[1],
                    'tech_sheet': result[2],
                    'price_tot': result[3]
                }
            return None
    except Exception as e:
        print(f"Error obteniendo datos de opción de accesorio: {e}")
        raise

# ok discount
def update_accessory_option_pricing(quote_id, option_type, data):
    """Actualiza los precios de las opciones de accesorios."""
    
    # Mapeo de tipos de opciones a nombres de columnas
    option_mappings = {
        'band_heater': ('BandHeater', 'BandHeater'),
        'pmg_update': ('PMGUpdate', 'PMGUpdate'),
        'digital_regulator': ('DigitalRegulator', 'DigitalRegulat'),
        'voltage_rheostat': ('VoltageRheostat', 'VoltageRheosta'),
        'thermal_wrap': ('ThermalWrap', 'ThermalWrap'),
        'base_heater': ('BaseHeater', 'BaseHeater'),
        'battery_switch': ('BatterySwitch', 'BatterySwitch'),
        'battery_holder': ('BatteryHolder', 'BatteryHolder'),
        'oil_heater': ('OilHeater', 'OilHeater'),
        'oil_level_switch': ('OilLevelSwitch', 'OilLevelSwitch'),
        'common_alarm_relay': ('CommonAlarmRelay', 'CommonAlarmRel'),
        'function_relay': ('FunctionRelay', 'FunctionRelay'),
        'coolant': ('Coolant', 'Coolant'),
        'oil': ('Oil', 'Oil'),
        'disconnect_switch': ('DisconnectSwitch', 'DisconnectSwit'),
        'remote_estop': ('RemoteEStop', 'RemoteEStop')
    }
    
    price_prefix, total_prefix = option_mappings.get(option_type, (None, None))
    if not price_prefix:
        raise ValueError(f"Tipo de opción no válido: {option_type}")
    
    query = f"""
    UPDATE CZE_Quotes
    SET 
        {price_prefix}_Price = ?,
        {price_prefix}_TSheet = ?,
        {total_prefix}_PriceTot = ?
    WHERE QuoteID = ?
    """
    
    try:
        with get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query, (
                data['price'],
                data['tech_sheet'],
                data['price_tot'],
                quote_id
            ))
            conn.commit()
    except Exception as e:
        print(f"Error actualizando precios de opción {option_type}: {e}")
        raise

# ok discount
def get_tank_data(kw_output, tank_autonomy, tank_type, qty_genset, Discount=0):
    """Obtiene los datos del tanque según los parámetros seleccionados."""
    query = """
    SELECT DISTINCT
        CZE_TankConfig.ConfigID,
        CZE_TankConfig.CapacityGAL,
        ISNULL(CZE_TankConfig.Cost, 0) * ? AS price,
        CZE_TankConfig.TechnicalSheet,
        ? * ? * ISNULL(CZE_TankConfig.Cost, 0) AS priceTot
    FROM
        CZE_TankConfig
    WHERE
        CZE_TankConfig.KWOutput = ?
        AND CZE_TankConfig.Item_Tank_Autonomy = ?
        AND CZE_TankConfig.Item_Tank_type = ?
        AND CZE_TankConfig.Evaluator >= 1
    """
    
    try:
        with get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query, (Discount, Discount, qty_genset, kw_output, tank_autonomy, tank_type))
            result = cursor.fetchone()
            
            if result:
                return {
                    'config_id': result[0],
                    'capacity_gal': result[1],
                    'price': result[2],
                    'tech_sheet': result[3],
                    'price_tot': result[4]
                }
            return None
    except Exception as e:
        print(f"Error obteniendo datos del tanque: {e}")
        raise

# ok discount
def get_spill_containment_data(item_spill, qty_genset, Discount=0):
    """Obtiene los datos del contenedor de derrames según el item seleccionado."""
    query = """
    SELECT 
        CZE_TankSpillCont.Item_Tank_SpillCont,
        ISNULL(CZE_TankSpillCont.price, 0) * ? AS price,
        CZE_TankSpillCont.TechnicalSheet,
        ? * ? * ISNULL(CZE_TankSpillCont.price, 0) AS priceTot
    FROM 
        CZE_TankSpillCont
    WHERE
        CZE_TankSpillCont.Item_Tank_SpillCont = ?
    """
    
    try:
        with get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query, (Discount, Discount, qty_genset, item_spill))
            result = cursor.fetchone()
            
            if result:
                return {
                    'item': result[0],
                    'price': result[1],
                    'tech_sheet': result[2],
                    'price_tot': result[3]
                }
            return None
    except Exception as e:
        print(f"Error obteniendo datos del contenedor de derrames: {e}")
        raise

# ok discount
def get_tank_option_data(item_option, qty_genset, Discount=0):
    """Obtiene los datos de la opción del tanque."""
    query = """
    SELECT 
        CZE_TankOptions.Item_Tank_option,
        ISNULL(CZE_TankOptions.price, 0) * ? AS price,
        CZE_TankOptions.TechnicalSheet,
        ? * ? * ISNULL(CZE_TankOptions.price, 0) AS priceTot
    FROM 
        CZE_TankOptions
    WHERE
        CZE_TankOptions.Item_Tank_option = ?
    """
    
    try:
        with get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query, (Discount, Discount, qty_genset, item_option))
            result = cursor.fetchone()
            
            if result:
                return {
                    'item': result[0],
                    'price': result[1],
                    'tech_sheet': result[2],
                    'price_tot': result[3]
                }
            return None
    except Exception as e:
        print(f"Error obteniendo datos de opción del tanque: {e}")
        raise

# ok discount
def update_tank_pricing(quote_id, data):
    """Actualiza los precios del tanque en la cotización."""
    query = """
    UPDATE CZE_Quotes
    SET 
        Tank_ConfigID = ?,
        Tank_CapacityGAL = ?,
        Tank_price = ?,
        Tank_TSheet = ?,
        Tank_PriceTot = ?
    WHERE QuoteID = ?
    """
    
    try:
        with get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query, (
                data['config_id'],
                data['capacity_gal'],
                data['price'],
                data['tech_sheet'],
                data['price_tot'],
                quote_id
            ))
            conn.commit()
    except Exception as e:
        print(f"Error actualizando precios del tanque: {e}")
        raise

# ok discount
def update_tank_option_pricing(quote_id, option_type, data):
    """Actualiza los precios de las opciones del tanque."""
    
    # Mapeo de tipos de opciones a nombres de columnas
    option_mappings = {
        'break_tray': ('BreakTraySwitch', 'BreakTraySwitc'),
        'fuel_level': ('FuelLevelSensor', 'FuelLevelSenso'),
        'spill_cont': ('SpillContainment', 'SpillContainme'),
        'overfill': ('OverfillValve', 'OverfillValve'),
        'dual_fuel': ('DualFuelSwitches', 'DualFuelSwitch'),
        'high_fuel': ('HighFuelSwitch', 'HighFuelSwitch'),
        'remote_alarm': ('RemoteAlarmPanel', 'RemoteAlarmPan'),
        'vent_pipe': ('VentPipe', 'VentPipe'),
        'flammable': ('FlammableLiquids', 'FlammableLiqui'),
        'nfpa': ('NFPAIdentificat', 'NFPAIdentifica'),
        'no_smoking': ('NoSmoking', 'NoSmoking'),
        'tank_number': ('TankNumber', 'TankNumber'),
        'fluid_cont': ('FluidContainment', 'FluidContainme')
    }
    
    price_prefix, total_prefix = option_mappings.get(option_type, (None, None))
    if not price_prefix:
        raise ValueError(f"Tipo de opción no válido: {option_type}")
    
    query = f"""
    UPDATE CZE_Quotes
    SET 
        {price_prefix}_Price = ?,
        {price_prefix}_TSheet = ?,
        {total_prefix}_PriceTot = ?
    WHERE QuoteID = ?
    """
    
    try:
        with get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query, (
                data['price'],
                data['tech_sheet'],
                data['price_tot'],
                quote_id
            ))
            conn.commit()
    except Exception as e:
        print(f"Error actualizando precios de opción {option_type}: {e}")
        raise

# ok discount
def get_testing_data(item_testing, kw_output, qty_genset, Discount=0):
    """Obtiene los datos de pruebas según el item seleccionado."""
    query = """
    SELECT DISTINCT
        CZE_PrjWarTestConfig.ConfigID,
        ISNULL(CZE_PrjWarTestConfig.Cost, 0) * ? AS price,
        CZE_PrjWarTestConfig.Document,
        ? * ? * ISNULL(CZE_PrjWarTestConfig.Cost, 0) AS priceTot
    FROM
        CZE_PrjWarTestConfig
    WHERE
        CZE_PrjWarTestConfig.Item_PrjWar_Testing = ?
        AND CZE_PrjWarTestConfig.KWOutput = ?
        AND CZE_PrjWarTestConfig.Evaluator >= 1
    """
    
    try:
        with get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query, (Discount, Discount, qty_genset, item_testing, kw_output))
            result = cursor.fetchone()
            
            if result:
                return {
                    'config_id': result[0],
                    'price': result[1],
                    'document': result[2],
                    'price_tot': result[3]
                }
            return None
    except Exception as e:
        print(f"Error obteniendo datos de pruebas: {e}")
        raise

# ok discount
def get_warranty_data(item_warranty, qty_genset, genset_unit_price, Discount=0):
    """Obtiene los datos de la garantía según el item seleccionado."""
    query = """
    SELECT 
        CZE_PrjWarWarranty.Item_PrjWar_Warranty,
        ISNULL(CZE_PrjWarWarranty.Percentage, 0) AS Percentage,
        ? * ? * ISNULL(CZE_PrjWarWarranty.Percentage, 0) AS warranty_unit_price,
        ? * ? * ? * ISNULL(CZE_PrjWarWarranty.Percentage, 0) AS warranty_price_tot
    FROM 
        CZE_PrjWarWarranty
    WHERE
        CZE_PrjWarWarranty.Item_PrjWar_Warranty = ?
    """
    
    try:
        with get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query, (
                Discount,               # Para warranty_unit_price
                genset_unit_price,      # Para warranty_unit_price
                Discount, 
                qty_genset,             # Para warranty_price_tot
                genset_unit_price,      # Para warranty_price_tot
                item_warranty
            ))
            result = cursor.fetchone()
            if result:
                return {
                    'item': result[0],
                    'percentage': result[1],
                    'price': result[2],
                    'price_tot': result[3]
                }
            return None
    except Exception as e:
        print(f"Error obteniendo datos de garantía: {e}")
        raise

# ok discount
def update_testing_pricing(quote_id, data):
    """Actualiza los precios de pruebas en la cotización."""
    query = """
    UPDATE CZE_Quotes
    SET 
        Teasting_ConfigID = ?,
        Teasting_price = ?,
        Teasting_Document = ?,
        Teasting_PriceTot = ?
    WHERE QuoteID = ?
    """
    
    try:
        with get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query, (
                data['config_id'],
                data['price'],
                data['document'],
                data['price_tot'],
                quote_id
            ))
            conn.commit()
    except Exception as e:
        print(f"Error actualizando precios de pruebas: {e}")
        raise

# ok discount
def update_warranty_pricing(quote_id, data):
    """Actualiza los precios de la garantía en la cotización."""
    query = """
    UPDATE CZE_Quotes
    SET 
        Garanty_Percentage = ?,
        Garanty_price = ?,
        Garanty_PriceTot = ?
    WHERE QuoteID = ?
    """
    
    try:
        with get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query, (
                data['percentage'],
                data['price'],
                data['price_tot'],
                quote_id
            ))
            conn.commit()
    except Exception as e:
        print(f"Error actualizando precios de garantía: {e}")
        raise

def get_Definitive_quote_data(quote_id):
    """Obtiene los datos necesarios de una cotización específica."""
    query = """
        SELECT 
            CZE_Quotes.QuoteID,-- 1
            CZE_Quotes.PlateRating,-- 2
            CZE_Quotes.FuelTypeIDBack,-- 3
            CZE_Quotes.FuelTypeFront,-- 4
            CZE_Quotes.KWOutput,-- 5
            CZE_Quotes.EngineBrandID,-- 6
            CZE_Quotes.EngineBrand,-- 7
            CZE_Quotes.AlternatorBrandID,-- 8
            CZE_Quotes.AlternatorBrand,-- 9
            CZE_Quotes.VoltageID,-- 10
            CZE_Quotes.Voltage_Front,-- 11
            CZE_Quotes.CertificationID,-- 12
            CZE_Quotes.Certification,-- 13
            CZE_Quotes.QtyGenset,-- 14
            CZE_Quotes.ControllerBrandID,-- 15
            CZE_Quotes.Controlbrand,-- 16
            CZE_Quotes.Genset_CostID,-- 17
            CZE_Quotes.Genset_Unit_Price,-- 18
            CZE_Quotes.Genset_TSheet,-- 19
            CZE_Quotes.Genset_PriceTot,-- 20
            CZE_Quotes.QtyBreakers,-- 21
            CZE_Quotes.FrontQtyBreakers,-- 22
            CZE_Quotes.Item_breaker1,-- 23
            CZE_Quotes.Breaker1,-- 24
            CZE_Quotes.Breaker1_Price,-- 25
            CZE_Quotes.Breaker1_TSheet,-- 26
            CZE_Quotes.Breaker1_PriceTot,-- 27
            CZE_Quotes.Item_Sunt_t1,-- 28
            CZE_Quotes.Shunt_trip1,-- 29
            CZE_Quotes.Sunt_t1_Price,-- 30
            CZE_Quotes.Sunt_t1_TSheet,-- 31
            CZE_Quotes.Sunt_t1_PriceTot,-- 32
            CZE_Quotes.Item_Sunt_w1,-- 33
            CZE_Quotes.Shunt_trip_wiring1,-- 34
            CZE_Quotes.Sunt_w1_Price,-- 35
            CZE_Quotes.Sunt_w1_TSheet,-- 36
            CZE_Quotes.Sunt_w1_PriceTot,-- 37
            CZE_Quotes.Item_GFI1,-- 38
            CZE_Quotes.GFI1,-- 39
            CZE_Quotes.GFI1_Price,-- 40
            CZE_Quotes.GFI1_TSheet,-- 41
            CZE_Quotes.GFI1_PriceTot,-- 42
            CZE_Quotes.Item_Proyection_t1,-- 43
            CZE_Quotes.Protection_type1,-- 44
            CZE_Quotes.Proyection_t1_Price,-- 45
            CZE_Quotes.Proyection_t1_TSheet,-- 46
            CZE_Quotes.Proyection_t1_PriceTot,-- 47
            CZE_Quotes.Item_Auxiliary_c1,-- 48
            CZE_Quotes.Auxiliary_contacts1,-- 49
            CZE_Quotes.Auxiliary_c1_Price,-- 50
            CZE_Quotes.Auxiliary_c1_TSheet,-- 51
            CZE_Quotes.Auxiliary_c1_PriceTot,-- 52
            CZE_Quotes.Item_Breaker_L1,-- 53
            CZE_Quotes.Breaker_Lock1,-- 54
            CZE_Quotes.Breaker_L1_Price,-- 55
            CZE_Quotes.Breaker_L1_TSheet,-- 56
            CZE_Quotes.Breaker_L1_PriceTot,-- 57
            CZE_Quotes.Item_Motorized_B1,-- 58
            CZE_Quotes.Motorized_Breaker1,-- 59
            CZE_Quotes.Motorized_B1_Price,-- 60
            CZE_Quotes.Motorized_B1_TSheet,-- 61
            CZE_Quotes.Motorized_B1_PriceTot,-- 62
            CZE_Quotes.Item_breaker2,-- 63
            CZE_Quotes.Breaker2,-- 64
            CZE_Quotes.Breaker2_Price,-- 65
            CZE_Quotes.Breaker2_TSheet,-- 66
            CZE_Quotes.Breaker2_PriceTot,-- 67
            CZE_Quotes.Item_Sunt_t2,-- 68
            CZE_Quotes.Shunt_trip2,-- 69
            CZE_Quotes.Sunt_t2_Price,-- 70
            CZE_Quotes.Sunt_t2_TSheet,-- 71
            CZE_Quotes.Sunt_t2_PriceTot,-- 72
            CZE_Quotes.Item_Sunt_w2,-- 73
            CZE_Quotes.Shunt_trip_wiring2,-- 74
            CZE_Quotes.Sunt_w2_Price,-- 75
            CZE_Quotes.Sunt_w2_TSheet,-- 76
            CZE_Quotes.Sunt_w2_PriceTot,-- 77
            CZE_Quotes.Item_GFI2,-- 78
            CZE_Quotes.GFI2,-- 79
            CZE_Quotes.GFI2_Price,-- 80
            CZE_Quotes.GFI2_TSheet,-- 81
            CZE_Quotes.GFI2_PriceTot,-- 82
            CZE_Quotes.Item_Proyection_t2,-- 83
            CZE_Quotes.Protection_type2,-- 84
            CZE_Quotes.Proyection_t2_Price,-- 85
            CZE_Quotes.Proyection_t2_TSheet,-- 86
            CZE_Quotes.Proyection_t2_PriceTot,-- 87
            CZE_Quotes.Item_Auxiliary_c2,-- 88
            CZE_Quotes.Auxiliary_contacts2,-- 89
            CZE_Quotes.Auxiliary_c2_Price,-- 90
            CZE_Quotes.Auxiliary_c2_TSheet,-- 91
            CZE_Quotes.Auxiliary_c2_PriceTot,-- 92
            CZE_Quotes.Item_Breaker_L2,-- 93
            CZE_Quotes.Breaker_Lock2,-- 94
            CZE_Quotes.Breaker_L2_Price,-- 95
            CZE_Quotes.Breaker_L2_TSheet,-- 96
            CZE_Quotes.Breaker_L2_PriceTot,-- 97
            CZE_Quotes.Item_Motorized_B2,-- 98
            CZE_Quotes.Motorized_Breaker2,-- 99
            CZE_Quotes.Motorized_B2_Price,-- 100
            CZE_Quotes.Motorized_B2_TSheet,-- 101
            CZE_Quotes.Motorized_B2_PriceTot,-- 102
            CZE_Quotes.Item_breaker3,-- 103
            CZE_Quotes.Breaker3,-- 104
            CZE_Quotes.Breaker3_Price,-- 105
            CZE_Quotes.Breaker3_TSheet,-- 106
            CZE_Quotes.Breaker3_PriceTot,-- 107
            CZE_Quotes.Item_Sunt_t3,-- 108
            CZE_Quotes.Shunt_trip3,-- 109
            CZE_Quotes.Sunt_t3_Price,-- 110
            CZE_Quotes.Sunt_t3_TSheet,-- 111
            CZE_Quotes.Sunt_t3_PriceTot,-- 112
            CZE_Quotes.Item_Sunt_w3,-- 113
            CZE_Quotes.Shunt_trip_wiring3,-- 114
            CZE_Quotes.Sunt_w3_Price,-- 115
            CZE_Quotes.Sunt_w3_TSheet,-- 116
            CZE_Quotes.Sunt_w3_PriceTot,-- 117
            CZE_Quotes.Item_GFI3,-- 118
            CZE_Quotes.GFI3,-- 119
            CZE_Quotes.GFI3_Price,-- 120
            CZE_Quotes.GFI3_TSheet,-- 121
            CZE_Quotes.GFI3_PriceTot,-- 122
            CZE_Quotes.Item_Proyection_t3,-- 123
            CZE_Quotes.Protection_type3,-- 124
            CZE_Quotes.Proyection_t3_Price,-- 125
            CZE_Quotes.Proyection_t3_TSheet,-- 126
            CZE_Quotes.Proyection_t3_PriceTot,-- 127
            CZE_Quotes.Item_Auxiliary_c3,-- 128
            CZE_Quotes.Auxiliary_contacts3,-- 129
            CZE_Quotes.Auxiliary_c3_Price,-- 130
            CZE_Quotes.Auxiliary_c3_TSheet,-- 131
            CZE_Quotes.Auxiliary_c3_PriceTot,-- 132
            CZE_Quotes.Item_Breaker_L3,-- 133
            CZE_Quotes.Breaker_Lock3,-- 134
            CZE_Quotes.Breaker_L3_Price,-- 135
            CZE_Quotes.Breaker_L3_TSheet,-- 136
            CZE_Quotes.Breaker_L3_PriceTot,-- 137
            CZE_Quotes.Item_Motorized_B3,-- 138
            CZE_Quotes.Motorized_Breaker3,-- 139
            CZE_Quotes.Motorized_B3_Price,-- 140
            CZE_Quotes.Motorized_B3_TSheet,-- 141
            CZE_Quotes.Motorized_B3_PriceTot,-- 142
            CZE_Quotes.Item_controller,-- 143
            CZE_Quotes.ControlModel,-- 144
            CZE_Quotes.controller_Price,-- 145
            CZE_Quotes.controller_TSheet,-- 146
            CZE_Quotes.ControlModel_PriceTot,-- 147
            CZE_Quotes.Item_CtrlPanelHeater,-- 148
            CZE_Quotes.CtrlPanelHeater,-- 149
            CZE_Quotes.CtrlPanelHeater_Price,-- 150
            CZE_Quotes.CtrlPanelHeater_TSheet,-- 151
            CZE_Quotes.CtrlPanelHeat_PriceTot,-- 152
            CZE_Quotes.Item_RempteDisplMod,-- 153
            CZE_Quotes.RemoteDisplayModule,-- 154
            CZE_Quotes.RempteDisplMod_Price,-- 155
            CZE_Quotes.RempteDisplMod_TSheet,-- 156
            CZE_Quotes.RempteDisplMod_PriceTot,-- 157
            CZE_Quotes.Item_Antena,-- 158
            CZE_Quotes.Antena,-- 159
            CZE_Quotes.Antena_Price,-- 160
            CZE_Quotes.Antena_TSheet,-- 161
            CZE_Quotes.Antena_PriceTot,-- 162
            CZE_Quotes.Item_DSE890,-- 163
            CZE_Quotes.DSE890,-- 164
            CZE_Quotes.DSE890_Price,-- 165
            CZE_Quotes.DSE890_TSheet,-- 166
            CZE_Quotes.DSE890_PriceTot,-- 167
            CZE_Quotes.Item_DSE2157,-- 168
            CZE_Quotes.DSE2157,-- 169
            CZE_Quotes.QtyDSE2157,-- 170
            CZE_Quotes.DSE2157_Price,-- 171
            CZE_Quotes.DSE2157_TSheet,-- 172
            CZE_Quotes.DSE2157_PriceTot,-- 173
            CZE_Quotes.Item_DSE2548,-- 174
            CZE_Quotes.DSE2548,-- 175
            CZE_Quotes.QtyDSE2548,-- 176
            CZE_Quotes.DSE2548_Price,-- 177
            CZE_Quotes.DSE2548_TSheet,-- 178
            CZE_Quotes.DSE2548_PriceTot,-- 179
            
            CZE_Quotes.Item_ComapDSE890,-- 180
            CZE_Quotes.ComapDSE890,-- 181
            CZE_Quotes.ComapDSE890_Price,-- 182
            CZE_Quotes.ComapDSE890_TSheet,-- 183
            CZE_Quotes.ComapDSE890_PriceTot,-- 184

            CZE_Quotes.Item_ComapDSE2157,-- 185
            CZE_Quotes.ComapDSE2157,-- 186
            CZE_Quotes.QtyComapDSE2157,-- 187
            CZE_Quotes.ComapDSE2157_Price,-- 188
            CZE_Quotes.ComapDSE2157_TSheet,-- 189
            CZE_Quotes.ComapDSE2157_PriceTot,-- 190
            CZE_Quotes.Item_ComapDSE2548,-- 191
            CZE_Quotes.ComapDSE2548,-- 192
            CZE_Quotes.QtyComapDSE2548,-- 193
            CZE_Quotes.ComapDSE2548_Price,-- 194
            CZE_Quotes.ComapDSE2548_TSheet,-- 195
            CZE_Quotes.ComapDSE2548_PriceTot,-- 196
            CZE_Quotes.Item_CtrlVotlOper,-- 197
            CZE_Quotes.CtrlVotlOper,-- 198
            CZE_Quotes.CtrlVotlOper_Price,-- 199
            CZE_Quotes.CtrlVotlOper_TSheet,-- 200
            CZE_Quotes.CtrlVotlOper_PriceTot,-- 201
            CZE_Quotes.Item_BateryCharger,-- 202
            CZE_Quotes.BateryCharger,-- 203
            CZE_Quotes.BateryCharger_Price,-- 204
            CZE_Quotes.BateryCharger_TSheet,-- 205
            CZE_Quotes.BateryCharger_PriceTot,-- 206
            CZE_Quotes.Item_Enclosuree_type,-- 207
            CZE_Quotes.Enclosure,-- 208
            CZE_Quotes.Item_Eclousure_Mat,-- 209
            CZE_Quotes.EnclosureType,-- 210
            CZE_Quotes.Enclosure_ConfigID,-- 211
            CZE_Quotes.Enclosure_price,-- 212
            CZE_Quotes.Enclosure_TSheet,-- 213
            CZE_Quotes.Enclosure_PriceTot,-- 214
            CZE_Quotes.Item_GFCI120V,-- 215
            CZE_Quotes.GFCI120V,-- 216
            CZE_Quotes.QtyGFCI120V,-- 217
            CZE_Quotes.GFCI120V_Price,-- 218
            CZE_Quotes.GFCI120V_TSheet,-- 219
            CZE_Quotes.GFCI120V_PriceTot,-- 220
            CZE_Quotes.Item_Receptable120V,-- 221
            CZE_Quotes.Receptable120V,-- 222
            CZE_Quotes.QtyReceptable120V,-- 223
            CZE_Quotes.Receptable120V_Price,-- 224
            CZE_Quotes.Receptable120V_TSheet,-- 225
            CZE_Quotes.Receptable120V_PriceTot,-- 226
            CZE_Quotes.Item_Enclosure_ligthAC,-- 227
            CZE_Quotes.lightAC,-- 228
            CZE_Quotes.QtylightAC,-- 229
            CZE_Quotes.lightAC_price,-- 230
            CZE_Quotes.lightAC_TSheet,-- 231
            CZE_Quotes.lightAC_PriceTot,-- 232
            CZE_Quotes.Item_Enclosure_ligthDC,-- 233
            CZE_Quotes.lightDC,-- 234
            CZE_Quotes.QtylightDC,-- 235
            CZE_Quotes.lightDC_price,-- 236
            CZE_Quotes.lightDC_TSheet,-- 237
            CZE_Quotes.lightDC_PriceTot,-- 238
            CZE_Quotes.Item_Enclosure_SHeater,-- 239
            CZE_Quotes.SpaceHeater,-- 240
            CZE_Quotes.QtySpaceHeater,-- 241
            CZE_Quotes.SpaceHeater_price,-- 242
            CZE_Quotes.SpaceHeater_TSheet,-- 243
            CZE_Quotes.SpaceHeater_PriceTot,-- 244
            CZE_Quotes.Item_Enclosure_LCenter,-- 245
            CZE_Quotes.LoadCenter,-- 246
            CZE_Quotes.LoadCenter_price,-- 247
            CZE_Quotes.LoadCenter_TSheet,-- 248
            CZE_Quotes.LoadCenter_PriceTot,-- 249
            CZE_Quotes.Item_PrewireAccsEncl,-- 250
            CZE_Quotes.PrewireAccsEnclousure,-- 251
            CZE_Quotes.PrewireAccsEnclo_Price,-- 252
            CZE_Quotes.PrewireAccsEnclo_TSheet,-- 253
            CZE_Quotes.PrewireAccsEnc_PriceTot,-- 254
            CZE_Quotes.Item_autolouvers,-- 255
            CZE_Quotes.autolouvers,-- 256
            CZE_Quotes.autolouvers_Price,-- 257
            CZE_Quotes.autolouvers_TSheet,-- 258
            CZE_Quotes.autolouvers_PriceTot,-- 259
            CZE_Quotes.Item_Receptautolouvers,-- 260
            CZE_Quotes.Receptautolouvers,-- 261
            CZE_Quotes.Receptautolouver_Price,-- 262
            CZE_Quotes.Receptautolouver_TSheet,-- 263
            CZE_Quotes.Receptautolouv_PriceTot,-- 264
            CZE_Quotes.Item_BandHeater,-- 265
            CZE_Quotes.BandHeater,-- 266
            CZE_Quotes.BandHeater_Price,-- 267
            CZE_Quotes.BandHeater_TSheet,-- 268
            CZE_Quotes.BandHeater_PriceTot,-- 269
            CZE_Quotes.Item_PMGUpdate,-- 270
            CZE_Quotes.PMGUpdate,-- 271
            CZE_Quotes.PMGUpdate_Price,-- 272
            CZE_Quotes.PMGUpdate_TSheet,-- 273
            CZE_Quotes.PMGUpdate_PriceTot,-- 274
            CZE_Quotes.Item_DigitalRegulator,-- 275
            CZE_Quotes.DigitalRegulator,-- 276
            CZE_Quotes.DigitalRegulator_Price,-- 277
            CZE_Quotes.DigitalRegulator_TSheet,-- 278
            CZE_Quotes.DigitalRegulat_PriceTot,-- 279
            CZE_Quotes.Item_VoltageRheostat,-- 280
            CZE_Quotes.VoltageRheostat,-- 281
            CZE_Quotes.VoltageRheostat_Price,-- 282
            CZE_Quotes.VoltageRheostat_TSheet,-- 283
            CZE_Quotes.VoltageRheosta_PriceTot,-- 284
            CZE_Quotes.Item_ThermalWrap,-- 285
            CZE_Quotes.ThermalWrap,-- 286
            CZE_Quotes.ThermalWrap_Price,-- 287
            CZE_Quotes.ThermalWrap_TSheet,-- 288
            CZE_Quotes.ThermalWrap_PriceTot,-- 289
            CZE_Quotes.Item_BaseHeater,-- 290
            CZE_Quotes.BaseHeater,-- 291
            CZE_Quotes.BaseHeater_Price,-- 292
            CZE_Quotes.BaseHeater_TSheet,-- 293
            CZE_Quotes.BaseHeater_PriceTot,-- 294
            CZE_Quotes.Item_BatterySwitch,-- 295
            CZE_Quotes.BatterySwitch,-- 296
            CZE_Quotes.BatterySwitch_Price,-- 297
            CZE_Quotes.BatterySwitch_TSheet,-- 298
            CZE_Quotes.BatterySwitch_PriceTot,-- 299
            CZE_Quotes.Item_BatteryHolder,-- 300
            CZE_Quotes.BatteryHolder,-- 301
            CZE_Quotes.BatteryHolder_Price,-- 302
            CZE_Quotes.BatteryHolder_TSheet,-- 303
            CZE_Quotes.BatteryHolder_PriceTot,-- 304
            CZE_Quotes.Item_OilHeater,-- 305
            CZE_Quotes.OilHeater,-- 306
            CZE_Quotes.OilHeater_Price,-- 307
            CZE_Quotes.OilHeater_TSheet,-- 308
            CZE_Quotes.OilHeater_PriceTot,-- 309
            CZE_Quotes.Item_OilLevelSwitch,-- 310
            CZE_Quotes.OilLevelSwitch,-- 311
            CZE_Quotes.OilLevelSwitch_Price,-- 312
            CZE_Quotes.OilLevelSwitch_TSheet,-- 313
            CZE_Quotes.OilLevelSwitch_PriceTot,-- 314
            CZE_Quotes.Item_CommonAlarmRelay,-- 315
            CZE_Quotes.CommonAlarmRelay,-- 316
            CZE_Quotes.CommonAlarmRelay_Price,-- 317
            CZE_Quotes.CommonAlarmRelay_TSheet,-- 318
            CZE_Quotes.CommonAlarmRel_PriceTot,-- 319
            CZE_Quotes.Item_FunctionRelay,-- 320
            CZE_Quotes.FunctionRelay,-- 321
            CZE_Quotes.FunctionRelay_Price,-- 322
            CZE_Quotes.FunctionRelay_TSheet,-- 323
            CZE_Quotes.FunctionRelay_PriceTot,-- 324
            CZE_Quotes.Item_Coolant,-- 325
            CZE_Quotes.Coolant,-- 326
            CZE_Quotes.Coolant_Price,-- 327
            CZE_Quotes.Coolant_TSheet,-- 328
            CZE_Quotes.Coolant_PriceTot,-- 329
            CZE_Quotes.Item_Oil,-- 330
            CZE_Quotes.Oil,-- 331
            CZE_Quotes.Oil_Price,-- 332
            CZE_Quotes.Oil_TSheet,-- 333
            CZE_Quotes.Oil_PriceTot,-- 334
            CZE_Quotes.Item_DisconnectSwitches,-- 335
            CZE_Quotes.DisconnectSwitches,-- 336
            CZE_Quotes.DisconnectSwitch_Price,-- 337
            CZE_Quotes.DisconnectSwitch_TSheet,-- 338
            CZE_Quotes.DisconnectSwit_PriceTot,-- 339
            CZE_Quotes.Item_AddAccesory_Estop,-- 340
            CZE_Quotes.EStop,-- 341
            CZE_Quotes.QtyEStop,-- 342
            CZE_Quotes.EStop_price,-- 343
            CZE_Quotes.EStop_TSheet,-- 344
            CZE_Quotes.EStop_PriceTot,-- 345
            CZE_Quotes.Item_RemoteEStop,-- 346
            CZE_Quotes.RemoteEStop,-- 347
            CZE_Quotes.RemoteEStop_Price,-- 348
            CZE_Quotes.RemoteEStop_TSheet,-- 349
            CZE_Quotes.RemoteEStop_PriceTot,-- 350
            CZE_Quotes.Item_AddAccesory_Spring,-- 351
            CZE_Quotes.SpringIsolator,-- 352
            CZE_Quotes.SpringIsolator_price,-- 353
            CZE_Quotes.SpringIsolator_TSheet,-- 354
            CZE_Quotes.SpringIsolator_PriceTot,-- 355
            CZE_Quotes.Item_Tank_Autonomy,-- 356
            CZE_Quotes.TankCapacity,-- 357
            CZE_Quotes.Item_Tank_type,-- 358
            CZE_Quotes.TankType,-- 359
            CZE_Quotes.Tank_ConfigID,-- 360
            CZE_Quotes.Tank_CapacityGAL,-- 361
            CZE_Quotes.Tank_price,-- 362
            CZE_Quotes.Tank_TSheet,-- 363
            CZE_Quotes.Tank_PriceTot,-- 364
            CZE_Quotes.Item_BreakTraySwitch,-- 365
            CZE_Quotes.BreakTraySwitch,-- 366
            CZE_Quotes.BreakTraySwitch_Price,-- 367
            CZE_Quotes.BreakTraySwitch_TSheet,-- 368
            CZE_Quotes.BreakTraySwitc_PriceTot,-- 369
            CZE_Quotes.Item_FuelLevelSensor,-- 370
            CZE_Quotes.FuelLevelSensor,-- 371
            CZE_Quotes.FuelLevelSensor_Price,-- 372
            CZE_Quotes.FuelLevelSensor_TSheet,-- 373
            CZE_Quotes.FuelLevelSenso_PriceTot,-- 374
            CZE_Quotes.Item_Tank_SpillCont,-- 375
            CZE_Quotes.SpillContainment,-- 376
            CZE_Quotes.SpillContainment_Price,-- 377
            CZE_Quotes.SpillContainment_TSheet,-- 378
            CZE_Quotes.SpillContainme_PriceTot,-- 379
            CZE_Quotes.Item_OverfillValve,-- 380
            CZE_Quotes.OverfillValve,-- 381
            CZE_Quotes.OverfillValve_Price,-- 382
            CZE_Quotes.OverfillValve_TSheet,-- 383
            CZE_Quotes.OverfillValve_PriceTot,-- 384
            CZE_Quotes.Item_DualFuelSwitch,-- 385
            CZE_Quotes.DualFuelSwitches,-- 386
            CZE_Quotes.DualFuelSwitches_Price,-- 387
            CZE_Quotes.DualFuelSwitches_TSheet,-- 388
            CZE_Quotes.DualFuelSwitch_PriceTot,-- 389
            CZE_Quotes.Item_HighFuelSwitch,-- 390
            CZE_Quotes.HighFuelSwitch,-- 391
            CZE_Quotes.HighFuelSwitch_Price,-- 392
            CZE_Quotes.HighFuelSwitch_TSheet,-- 393
            CZE_Quotes.HighFuelSwitch_PriceTot,-- 394
            CZE_Quotes.Item_RemoteAlarmP,-- 395
            CZE_Quotes.RemoteAlarmPanel,-- 396
            CZE_Quotes.RemoteAlarmPanel_Price,-- 397
            CZE_Quotes.RemoteAlarmPanel_TSheet,-- 398
            CZE_Quotes.RemoteAlarmPan_PriceTot,-- 399
            CZE_Quotes.Item_VentPipe,-- 400
            CZE_Quotes.VentPipe,-- 401
            CZE_Quotes.VentPipe_Price,-- 402
            CZE_Quotes.VentPipe_TSheet,-- 403
            CZE_Quotes.VentPipe_PriceTot,-- 404
            CZE_Quotes.Item_FlammableL,-- 405
            CZE_Quotes.FlammableLiquids,-- 406
            CZE_Quotes.FlammableLiquids_Price,-- 407
            CZE_Quotes.FlammableLiquids_TSheet,-- 408
            CZE_Quotes.FlammableLiqui_PriceTot,-- 409
            CZE_Quotes.Item_NFPAIdentificat,-- 410
            CZE_Quotes.NFPAIdentification,-- 411
            CZE_Quotes.NFPAIdentificat_Price,-- 412
            CZE_Quotes.NFPAIdentificat_TSheet,-- 413
            CZE_Quotes.NFPAIdentifica_PriceTot,-- 414
            CZE_Quotes.Item_NoSmoking,-- 415
            CZE_Quotes.NoSmoking,-- 416
            CZE_Quotes.NoSmoking_Price,-- 417
            CZE_Quotes.NoSmoking_TSheet,-- 418
            CZE_Quotes.NoSmoking_PriceTot,-- 419
            CZE_Quotes.Item_TankNumber,-- 420
            CZE_Quotes.TankNumber,-- 421
            CZE_Quotes.TankNumber_Price,-- 422
            CZE_Quotes.TankNumber_TSheet,-- 423
            CZE_Quotes.TankNumber_PriceTot,-- 424
            CZE_Quotes.Item_FluidContainment,-- 425
            CZE_Quotes.FluidContainment,-- 426
            CZE_Quotes.FluidContainment_Price,-- 427
            CZE_Quotes.FluidContainment_TSheet,-- 428
            CZE_Quotes.FluidContainme_PriceTot,-- 429
            CZE_Quotes.Project,-- 430
            CZE_Quotes.Item_PrjWar_Warranty,-- 431
            CZE_Quotes.Garanty,-- 432
            CZE_Quotes.Garanty_Percentage,-- 433
            CZE_Quotes.Garanty_price,-- 434
            CZE_Quotes.Garanty_PriceTot,-- 435
            CZE_Quotes.Item_PrjWar_Testing,-- 436
            CZE_Quotes.Teasting,-- 437
            CZE_Quotes.Teasting_ConfigID,-- 438
            CZE_Quotes.Teasting_price,-- 439
            CZE_Quotes.Teasting_Document,-- 440
            CZE_Quotes.Teasting_PriceTot,-- 441
            CZE_Quotes.ReqCustomer,-- 442
            CZE_Quotes.EngineModel,-- 443
            CZE_Quotes.Engine_TSheet,-- 444
            CZE_Quotes.Engine_Tier,-- 445
            CZE_Quotes.Engine_Rpm,-- 446
            CZE_Quotes.Engine_Power,-- 447
            CZE_Quotes.Engine_NumberOfCylinder,-- 448
            CZE_Quotes.Engine_Aspiration,-- 449
            CZE_Quotes.Engine_Governor_type,-- 450
            CZE_Quotes.Engine_Control_voltage,-- 451
            CZE_Quotes.Engine_Battery_type,-- 452
            CZE_Quotes.Engine_Hotstart_model,-- 453
            CZE_Quotes.Engine_RadiatorTemp,-- 454
            CZE_Quotes.AlternatorModel,-- 455
            CZE_Quotes.Altern_TSheet,-- 456
            CZE_Quotes.Altern_Powerfactor,-- 457
            CZE_Quotes.Altern_Insulation_Sys,-- 458
            CZE_Quotes.Altern_Control_System,-- 459
            CZE_Quotes.Altern_Protection,-- 460
            CZE_Quotes.Altern_AvrModel,-- 461
            CZE_Quotes.Altern_VoltageRegulat,-- 462
            CZE_Quotes.Altern_Class_temp_rise,-- 463
			(
			SELECT
				CASE
					WHEN CZE_Voltage.Phases = 'Single' THEN 1
					WHEN CZE_Voltage.Phases = 2 THEN 2
					WHEN CZE_Voltage.Phases = 3 THEN 3
				END
			FROM
				CZE_Voltage
			WHERE 
				CZE_Voltage.VoltageID = CZE_Quotes.VoltageID
			) AS Phases,-- 464
			(
			SELECT
				CONCAT( UPPER (CZE_TankType.description),' - ', CZE_TankConfig.CapacityGAL, ' GAL',' - ', CZE_TankAutonomy.description)
				/*
				CZE_TankType.description,
				CZE_TankConfig.CapacityGAL,
				CZE_TankAutonomy.description
				*/
			FROM
				CZE_TankConfig
			LEFT JOIN
				CZE_TankType ON CZE_TankConfig.Item_Tank_type = CZE_TankType.Item_Tank_type
			LEFT JOIN
				CZE_TankAutonomy ON CZE_TankConfig.Item_Tank_Autonomy = CZE_TankAutonomy.Item_Tank_Autonomy
			WHERE 
				CZE_TankConfig.ConfigID = CZE_Quotes.Tank_ConfigID
			) AS BaseTank,-- 465
			(
			SELECT
				CONCAT( 'AUTONOMY ', CZE_TankAutonomy.description)
			FROM
				CZE_TankConfig
			LEFT JOIN
				CZE_TankType ON CZE_TankConfig.Item_Tank_type = CZE_TankType.Item_Tank_type
			LEFT JOIN
				CZE_TankAutonomy ON CZE_TankConfig.Item_Tank_Autonomy = CZE_TankAutonomy.Item_Tank_Autonomy
			WHERE 
				CZE_TankConfig.ConfigID = CZE_Quotes.Tank_ConfigID
			) AS TankAutonomy,-- 466
			(
			SELECT
				CONCAT (CZE_EnclosureMaterial.description, ' - ', CZE_EnclosureType.Front)
			FROM
				CZE_EnclosureConfig
			LEFT JOIN
				CZE_EnclosureMaterial ON CZE_EnclosureConfig.Item_Enclosure_Mat = CZE_EnclosureMaterial.Item_Eclousure_Mat
			LEFT JOIN
				CZE_EnclosureType ON CZE_EnclosureConfig.Item_Enclosure_type = CZE_EnclosureType.Item_Enclosuree_type
			WHERE 
				CZE_EnclosureConfig.ConfigID = CZE_Quotes.Enclosure_ConfigID
			) AS AcousticEnclosure,-- 467
            CZE_Quotes.CustomerCompany,-- 468
            CZE_Quotes.CustomerAddress,-- 469
            CZE_Quotes.CustomerContact,-- 470
            CZE_Quotes.CustomerEmail,-- 471
            CZE_Quotes.CustomerTel,-- 472
            CZE_Quotes.SellerName,-- 473
            CZE_Quotes.SellerEmail,-- 474
            CZE_Quotes.SellerPhone, -- 475
            CZE_Quotes.Destiny_Country, -- 476
            CZE_Quotes.Destiny_State, -- 477
            CZE_Quotes.Country_Discount, --478
            CZE_Quotes.Commercial_Offer, --479


            CZE_Quotes.Item_CtrlPanelHeaterCMP, --480
            CZE_Quotes.CtrlPanelHeaterCMP, --481
            CZE_Quotes.CtrlPanelHeaterCMP_Price, --482
            CZE_Quotes.CtrlPanelHeaterCMP_TSheet, --483
            CZE_Quotes.CtrlPanelHeatCMP_PriceTot, --484,

            -- Módulo de Pantalla Remota
            CZE_Quotes.Item_RempteDisplModCMP, --485
            CZE_Quotes.RemoteDisplayModuleCMP, --486
            CZE_Quotes.RempteDisplModCMP_Price, --487
            CZE_Quotes.RempteDisplModCMP_TSheet, --488
            CZE_Quotes.RempteDisplModCMP_PriceTot, --489

            -- Antena CMP GSM / GPS
            CZE_Quotes.Item_AntenaCMP, --490
            CZE_Quotes.AntenaCMP, --491
            CZE_Quotes.AntenaCMP_Price, --492
            CZE_Quotes.AntenaCMP_TSheet, --493
            CZE_Quotes.AntenaCMP_PriceTot, --494

            -- CM3 Ethernet / 4G gateway device
            CZE_Quotes.Item_ComapDSE890, --495
            CZE_Quotes.ComapDSE890, --496
            CZE_Quotes.ComapDSE890_Price, --497
            CZE_Quotes.ComapDSE890_TSheet, --498
            CZE_Quotes.ComapDSE890_PriceTot, --499

            -- CMP-IRB8 Output module
            CZE_Quotes.Item_ComapDSE2157, --500
            CZE_Quotes.ComapDSE2157, --501
            CZE_Quotes.ComapDSE2157_Price, --502
            CZE_Quotes.ComapDSE2157_TSheet, --503
            CZE_Quotes.ComapDSE2157_PriceTot, --504

            -- IGLRA15 Remote annunciator
            CZE_Quotes.Item_ComapDSE2548, --505
            CZE_Quotes.ComapDSE2548, --506
            CZE_Quotes.ComapDSE2548_Price, --507
            CZE_Quotes.ComapDSE2548_TSheet, --508
            CZE_Quotes.ComapDSE2548_PriceTot, --509

            CZE_Quotes.Item_OpeningEnclousure, --510
            CZE_Quotes.OpeningEnclousure, --511
            CZE_Quotes.OpeningEnclousure_Price, --512
            CZE_Quotes.OpeningEnclousure_TSheet, --513
            CZE_Quotes.OpeningEnclousure_PriceTot, --514

            CZE_Quotes.Item_StopButtonEnclosure, --515
            CZE_Quotes.StopButtonEnclosure, --516
            CZE_Quotes.StopButtonEnclosure_Price, --517
            CZE_Quotes.StopButtonEnclosure_TSheet, --518
            CZE_Quotes.StopButtonEnclosure_PriceTot, --519
            CZE_Quotes.StopButtonEnclosure_Qty, --520


            CZE_Quotes.Item_louvers_in, --521
            CZE_Quotes.louvers_in, --522
            CZE_Quotes.louvers_in_price, --523
            CZE_Quotes.louvers_in_tsheet, --524
            CZE_Quotes.louvers_in_pricetot, --525

            CZE_Quotes.Item_louvers_out, --526
            CZE_Quotes.louvers_out, --527
            CZE_Quotes.louvers_out_price, --528
            CZE_Quotes.louvers_out_tsheet,  --529
            CZE_Quotes.louvers_out_pricetot --530


        FROM CZE_Quotes
        WHERE QuoteID = ?
    """
    
    try:
        with get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query, (quote_id,))
            
            # Obtener los nombres de las columnas
            columns = [column[0] for column in cursor.description]
            
            # Obtener la tupla de resultados
            row = cursor.fetchone()
            
            if row:
                # Convertir la tupla en diccionario
                return dict(zip(columns, row))
            return None
            
            #return cursor.fetchone()
    except Exception as e:
        print(f"Error obteniendo datos de la cotización: {e}")
        raise


def get_genset_model(genset_cost_id):
    """Obtiene el GensetModel desde la tabla CZE_GensetConfig usando ConfigID."""
    query = """
    SELECT 
        CZE_GensetConfig.GensetModel
    FROM 
        CZE_GensetCost
    LEFT JOIN
        CZE_GensetConfig ON CZE_GensetCost.ConfigID = CZE_GensetConfig.ConfigID
    WHERE 
        CostID = ?
    """
    
    try:
        with get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query, (genset_cost_id,))
            result = cursor.fetchone()  # Obtiene una sola fila
            
            if result:
                return result[0]  # Devuelve solo el GensetModel
            else:
                return None  # Si no hay resultados, devuelve None
                
    except Exception as e:
        print(f"Error al obtener GensetModel: {e}")
        raise
    
def get_frecuency(Voltage_ID):
    """Obtiene la frecuencia y las fases de acuerdo al voltaje seleccionado."""
    query = """
    SELECT
        Frecuency,
        Phases
    FROM CZE_Voltage
    WHERE 
        VoltageID = ?
    """
    
    try:
        with get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query, (Voltage_ID,))
            result = cursor.fetchone()  # Obtiene una sola fila
            
            if result:
                return {
                    "Frecuency": result[0],
                    "Phases": result[1]
                }
            else:
                return None  # Si no hay resultados, devuelve None
                
    except Exception as e:
        print(f"Error al obtener los datos de voltaje: {e}")
        raise
    
    
def get_RoutePDF(quote_id):
        query = """
            SELECT 
                CZE_Quotes.RoutePDF
            FROM CZE_Quotes
            WHERE QuoteID = ?
        """
        try:
            with get_connection() as conn:
                
                cursor = conn.cursor()
                cursor.execute(query, (quote_id,))
                
                # Obtener los nombres de las columnas
                columns = [column[0] for column in cursor.description]
                
                # Obtener la tupla de resultados
                row = cursor.fetchone()
                
                if row:
                    # Convertir la tupla en diccionario
                    return dict(zip(columns, row))
                return None
                
                #return cursor.fetchone()
        except Exception as e:
            print(f"Error obteniendo datos de la cotización: {e}")
            raise
        
def update_RoutePDF(RoutePDF,quote_id):
    query = f"""
    UPDATE CZE_Quotes
    SET 
        RoutePDF = ?
    WHERE QuoteID = ?
    """
    try:
        with get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query, (
                RoutePDF, 
                quote_id
            ))
            conn.commit()
            
    except Exception as e:
        print(f"Error actualizando RoutePDF: {e}")
        raise