# Archivo: CotizFormQueryIngSQL.py
# Ruta: src\Consultas_SQL\Operaciones\Ingenieria\Cotiz\CotizFormQueryIngSQL.py
# Descripción: Módulo de consultas SQL  para consultar formularios de cotización en el módulo de Ingeniería.
# Autor: Equipo de Desarrollo IGSA
# Fecha: 2025

# Importamos la función de conexión de tu proyecto
from Consultas_SQL.conexion import get_connection
import pyodbc # Es buena práctica importarlo para manejar errores específicos

def get_tasks_list():
    """
    Obtiene la lista completa de tareas de cotización desde la BD.
    Esta función es llamada por el endpoint en CotizFormQueryIng.py
    """
    
    # 📌 Este es tu script SQL, modificado con alias compatibles con el JS
    query = """
    SELECT 
        -- 🟦 Identificadores principales
        Q_SpQ_QuotationTasks.TaskID                               AS [NroTarea],
        Q_SpQ_QuotationTasks.Status                               AS [Estatus],
        Q_OpportunityCRM.CRM_OpportunityNumber                   AS [Oportunidad],
        Q_QuotationType.FrontES                                   AS [TipoFormulario],

        -- 🟩 Fechas clave (Con FORMATO)
        Q_SpQ_FormsHead.FormID                                 AS [NroFormulario],
        FORMAT(Q_SpQ_FormsHead.CreatedAt, 'dd-MM-yyyy HH:mm')      AS [FechaCreacion],

        -- 🟨 Información del cliente (CRM)
        Q_OpportunityCRM.CRM_ContactName                          AS [Contacto],

        -- 🟧 Información del vendedor (desde Profiles)
        Profiles.FirstName + ' ' + 
        ISNULL(Profiles.MiddleName, '') + ' ' + 
        Profiles.LastName + ' ' + 
        ISNULL(Profiles.SecondLastName, '')                       AS [Vendedor],
        Profiles.Email                                            AS [CorreoVendedor],
        Profiles.ContactPhone                                     AS [TelefonoVendedor],

        -- 🟥 Información de costeo (puede estar vacía si no se ha generado)
        Q_CostingHead.CostingNum                                  AS [NroCosteo],
        Q_CostingHead.CaseCost                                    AS [CasoCosteo],
        Q_CostingHead.DirectCost                                  AS [CostoDirecto],
        Q_CostingHead.IndirectPercent                             AS [PctIndirecto],
        Q_CostingHead.IndirectAmount                              AS [MontoIndirecto],
        Q_CostingHead.FinancePercent                              AS [PctFinanciamiento],
        Q_CostingHead.FinanceAmount                               AS [MontoFinanciamiento],
        Q_CostingHead.UtilityPercent                              AS [PctUtilidad],
        Q_CostingHead.UtilityAmount                               AS [MontoUtilidad],
        Q_CostingHead.OperationPercent                            AS [PctGtosOp],
        Q_CostingHead.OperationAmount                             AS [MontoGtosOp],
        Q_CostingHead.OvercostFactor                              AS [FactorSobrecosto],
        Q_CostingHead.SalePriceMin                                AS [PrecioVentaMin],
        Q_CostingHead.DiscountMaxPercent                          AS [PctDescMax],
        Q_CostingHead.DiscountMaxAmount                           AS [MontoDescuento],
        Q_CostingHead.SalePriceList                               AS [PrecioLista],

        -- 🟪 Tiempo de ejecución formateado
        LTRIM(RTRIM(CONCAT(
            ISNULL(CAST(Q_CostingHead.RunTimeNumber AS NVARCHAR(10)), ''),
            ' ',
            ISNULL(Q_CostingHead.RunTimeType, '')
        ))) AS [TiempoEjecucion]

    FROM Q_SpQ_QuotationTasks
    INNER JOIN Q_SpQ_FormsHead
        ON Q_SpQ_QuotationTasks.FormID = Q_SpQ_FormsHead.FormID
    INNER JOIN Q_QuotationType
        ON Q_SpQ_FormsHead.QuotationTypeID = Q_QuotationType.QuotationTypeID
    INNER JOIN Q_OpportunityCRM
        ON Q_SpQ_QuotationTasks.CRM_OpportunityID = Q_OpportunityCRM.CRM_OpportunityID
    LEFT JOIN Q_CostingHead
        ON Q_SpQ_QuotationTasks.CostingID = Q_CostingHead.CostingID
    LEFT JOIN Profiles
        ON Q_SpQ_QuotationTasks.SellerUserID = Profiles.UserID
    WHERE 
        Q_SpQ_QuotationTasks.Active = 1
    ORDER BY 
        Q_SpQ_QuotationTasks.Status,
        Q_SpQ_FormsHead.CreatedAt DESC;
    """
    
    conn = None
    try:
        conn = get_connection() # Obtenemos conexión de tu archivo 'conexion.py'
        if not conn:
            print("Error SQL: No se pudo establecer conexión con la base de datos.")
            return []
        
        cursor = conn.cursor()
        cursor.execute(query)
        
        # --- Método robusto para convertir la consulta en JSON ---
        # Obtenemos los nombres de las columnas (NroTarea, Estatus, etc.)
        columns = [column[0] for column in cursor.description]
        
        # Creamos una lista de diccionarios (lista de objetos JSON)
        results = [dict(zip(columns, row)) for row in cursor.fetchall()]
        
        return results
        
    except pyodbc.Error as e:
        # Captura errores específicos de la base de datos (ej. tabla no encontrada)
        print(f"[Error SQL pyodbc] Error obteniendo la lista de tareas: {e}")
        return []
    except Exception as e:
        # Captura cualquier otro error
        print(f"[Error inesperado] Error en get_tasks_list: {e}")
        return []
    finally:
        if conn:
            conn.close() # Siempre cerramos la conexión