"""
Archivo: CotizCreatedIngSQL.py
Ruta: src\Consultas_SQL\Operaciones\Ingenieria\Cotiz\CotizCreatedIngSQL.py
Descripción: Módulo para crear cotizaciones del area de ingenierìa
Autor: Equipo de Desarrollo IGSA
Fecha: 2025
"""
from datetime import datetime
import pyodbc
import logging
from typing import Dict, List, Optional, Union
from flask import Blueprint, request, jsonify,session

# Configurar logging
logger = logging.getLogger('cotizaciones_especiales_sql')

from config import Productivo, ENVIRONMENT
from Consultas_SQL.conexion import get_connection

from App.Utilities_module.DocsManagement import upload_document_to_existing_docs
from Consultas_SQL.Utilities.DocsManagementSQL import crear_docs_head
from App.Utilities_module.MailManagement import enviar_correo_universal


from flask import jsonify

def data_for_email(task_id):
    """
    🔹 Obtiene y une la información necesaria para el envío de correo
       basada en el TaskID (tabla Q_SpQ_QuotationTasks).
    🔹 Devuelve un diccionario listo para usar como `template_data`.
    """

    query1 = """
        SELECT 
            Q_OpportunityCRM.CRM_OpportunityNumber,
            Q_CostingHead.CostingNum,
            Q_QuotationType.FrontEN,
            Q_QuotationType.FrontES,
            Q_CostingHead.CostingDate
        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_CostingHead
            ON Q_CostingHead.CostingID = Q_SpQ_QuotationTasks.CostingID
        INNER JOIN
            Q_OpportunityCRM
        ON
            Q_SpQ_QuotationTasks.CRM_OpportunityID = Q_OpportunityCRM.CRM_OpportunityID
        WHERE 
            Q_SpQ_QuotationTasks.TaskID = ?;
    """

    query2 = """
        SELECT 
            Users.Email
        FROM Users
        INNER JOIN 
            Q_CostingHead
            ON Q_CostingHead.SellerUserID = Users.UserID
        INNER JOIN 
            Q_SpQ_QuotationTasks
            ON Q_SpQ_QuotationTasks.CostingID = Q_CostingHead.CostingID
        WHERE 
            Q_SpQ_QuotationTasks.TaskID = ?;
    """

    try:
        with get_connection() as conn:
            cursor = conn.cursor()

            # 1️⃣ Consulta principal
            cursor.execute(query1, (task_id,))
            row1 = cursor.fetchone()
            if not row1:
                print(f"⚠️ No se encontró información para TaskID {task_id}")
                return None

            columns1 = [col[0] for col in cursor.description]
            result1 = dict(zip(columns1, row1))

            # 2️⃣ Consulta de correo del usuario
            cursor.execute(query2, (task_id,))
            row2 = cursor.fetchone()
            result1["Email"] = row2[0] if row2 else None

            # 2️⃣ Consulta de número de oportunidad

            # 3️⃣ Formatear fecha (opcional)
            if isinstance(result1.get("CostingDate"), datetime):
                result1["CostingDate"] = result1["CostingDate"].strftime("%d/%m/%Y %H:%M")

            return result1  # dict plano

    except Exception as e:
        print(f"❌ Error en data_for_email({task_id}): {e}")
        return None
    

def consultar_docs_por_oportunidad_y_tarea(oportunity_id, task_id):
    """
    Consulta los documentos asociados a una oportunidad y form (task)
    y regresa las filas crudas del cursor.
    """
    connection = None
    cursor = None
    try:
        connection = get_connection()
        cursor = connection.cursor()
 
        query = """
            SELECT
                Q_SpQ_FormsHead.CRM_OpportunityID,
                Q_SpQ_FormsHead.FormID,
                Q_SpQ_DocsDetail.Title,
                Q_SpQ_DocsDetail.Ruta,
                Q_SpQ_DocsDetail.Type
            FROM
                Q_SpQ_FormsHead
            LEFT JOIN Q_SpQ_DocsHead
                ON Q_SpQ_FormsHead.DocsID = Q_SpQ_DocsHead.DocsID
            LEFT JOIN Q_SpQ_DocsDetail
                ON Q_SpQ_DocsHead.DocsID = Q_SpQ_DocsDetail.DocsID
            WHERE 
                Q_SpQ_FormsHead.CRM_OpportunityID = ?
                AND Q_SpQ_FormsHead.FormID = ?
                AND Q_SpQ_FormsHead.DocsID IS NOT NULL
        """
 
        cursor.execute(query, (oportunity_id, task_id))
        rows = cursor.fetchall()
        return rows
 
    except Exception as e:
        # Aquí puedes loguear el error si quieres
        print(f"Error al consultar documentos: {e}")
        return []
 
    finally:
        if cursor is not None:
            cursor.close()
        if connection is not None:
            connection.close()


def uploadFileAuxiliar(DocsID, CostingID, resultado):
    # 1. Asegurar clave message
    if "message" not in resultado:
        resultado["message"] = ""

    # 2. CORRECCIÓN DE EMERGENCIA: 
    # Si DocsID llegó como None, intentalo sacar del diccionario aquí mismo
    if DocsID is None:
        DocsID = resultado.get("docs_id")

    # 3. Ahora el IF ya funcionará porque DocsID valdrá 1008
    if DocsID and CostingID:
        query = """
        UPDATE Q_CostingHead
        SET DocsID = ?
        WHERE CostingID = ?
        """
        conn = None
        try:
            conn = get_connection()
            with conn:
                cursor = conn.cursor()
                cursor.execute(query, (DocsID, CostingID))
                conn.commit()
                resultado["message"] += f" | Costing actualizado con DocsID {DocsID}"
                return resultado
        except Exception as e:
            resultado["success"] = False
            resultado["message"] = f"Error DB: {str(e)}"
            return resultado
        finally:
            if conn:
                conn.close()
    
    return resultado

def enviarCorreoCostingCreado(task_id,destinatarioTest:str=""):
    """
    🔹 Usa los datos de data_for_email(task_id)
    🔹 Envía el correo con el template correspondiente
    """
    data = data_for_email(task_id)
    logger.info(f"Datos para correo: {data}")

    if not data:
        print(f"❌ No se pudieron obtener los datos para TaskID {task_id}")
        return {"success": False, "mensaje": f"No se encontraron datos para TaskID {task_id}"}

    try:
        destinatario= ""
        if destinatarioTest != "":
            destinatario = destinatarioTest
        else:
            destinatario = data["Email"]
        resultado = enviar_correo_universal(
            template_path='Emails/Ingenieria/Cotiz/CotizCreatedIngMail.html',
            asunto='✅ Costeo creado correctamente - IGSA Elephant',
            destinatarios_adicionales={
                'TO': [destinatario ],  # fallback si no hay correo
                'CC': ['victor.barrera.@igsa.com.mx','victor.cervantes@igsa.com.mx','alexis.moreno@igsa.com.mx']
            },
            template_data=data
        )

        if resultado.get("success"):
            print(f"✅ Correo enviado exitosamente a {data['Email']}")
        else:
            print(f"⚠️ Error al enviar correo: {resultado.get('mensaje')}")

        return resultado

    except Exception as e:
        print(f"❌ Error al enviar correo para TaskID {task_id}: {e}")
        return {"success": False, "mensaje": str(e)}


def get_currencies() -> List[Dict[str, str]]:
    """
    Consulta la tabla Q_Currency para obtener el código, el nombre y el símbolo de las monedas activas.
    """
    query = "SELECT CurrencyCode, FrontES, CurrSymbol FROM Q_Currency WHERE Active = 1;"
    currencies = []
    try:
        with get_connection() as conn:
            with conn.cursor() as cursor:
                cursor.execute(query)
                rows = cursor.fetchall()
                for row in rows:
                    currencies.append(
                        {"CurrencyCode": row[0], "FrontES": row[1], "CurrSymbol": row[2]})
        return currencies
    except Exception as e:
        print(f"Error al obtener las monedas: {e}")
        return []


def get_taxes(CurrencyCode) -> List[Dict[str, Union[str, float]]]:
        """
        Consulta la tabla Q_TaxRate para obtener el código y la descripción de los impuestos.
        """
        query = "SELECT TaxCode, FrontES, TaxAmount, CurrencyCode FROM Q_TaxRate WHERE Active >= 1 AND CurrencyCode = ?;"
        taxes = []
        try:
            with get_connection() as conn:
                with conn.cursor() as cursor:
                    cursor.execute(query, (CurrencyCode,))
                    rows = cursor.fetchall()
                    for row in rows:
                        taxes.append({"TaxCode": row[0], "FrontES": row[1], "TaxAmount": float(
                            row[2]), "CurrencyCode": row[3]})
            return taxes
        except Exception as e:
            print(f"Error al obtener los impuestos: {e}")
            return []


# =============================================================
# 🔍 Subconsulta 1 - Obtener datos del FormID
# =============================================================
def obtener_datos_formulario(form_id):
    query = """
        SELECT TOP 1 CRM_OpportunityID, QuotationTypeID
        FROM Q_SpQ_FormsHead
        WHERE FormID = ?
    """

    conn = None
    try:
        conn = get_connection()
        with conn:
            cursor = conn.cursor()
            cursor.execute(query, (form_id,))
            columns = [col[0] for col in cursor.description]
            row = cursor.fetchone()

            if row:
                result = dict(zip(columns, row))
                logger.info(f"Formulario {form_id} encontrado correctamente.")
                return result
            else:
                logger.warning(f"No se encontró FormID {form_id}.")
                return None

    except pyodbc.Error as e:
        msg = f"Error SQL en obtener_datos_formulario({form_id}): {e}"
        logger.error(msg)
        raise Exception(msg)

    finally:
        if conn:
            try:
                conn.close()
            except:
                pass


# =============================================================
# 🔍 Subconsulta 2 - Obtener contexto del usuario
# =============================================================
def obtener_contexto_usuario(user_id):
    query = """
        SELECT TOP 1
            UserRoles.RoleID,
            Roles.CompanyID,
            Roles.DivisionID,
            Roles.DepartamentID,
            Profiles.CompanyID AS ProfileCompanyID,
            Profiles.DivisionID AS ProfileDivisionID,
            Profiles.DepartamentID AS ProfileDepartamentID
        FROM Users
        INNER JOIN Profiles ON Users.UserID = Profiles.UserID
        INNER JOIN UserRoles ON Users.UserID = UserRoles.UserID
        INNER JOIN Roles ON UserRoles.RoleID = Roles.RoleID
        WHERE Users.UserID = ?
    """

    conn = None
    try:
        conn = get_connection()
        with conn:
            cursor = conn.cursor()
            cursor.execute(query, (user_id,))
            columns = [col[0] for col in cursor.description]
            row = cursor.fetchone()

            if row:
                result = dict(zip(columns, row))
                logger.info(f"Contexto de usuario {user_id} recuperado correctamente.")
                return result
            else:
                logger.warning(f"No se encontró contexto para UserID {user_id}.")
                return None

    except pyodbc.Error as e:
        msg = f"Error SQL en obtener_contexto_usuario({user_id}): {e}"
        logger.error(msg)
        raise Exception(msg)

    finally:
        if conn:
            try:
                conn.close()
            except:
                pass



def generar_costing_num():
    """Genera el siguiente número de costing (IDENTITY 1000,1)"""
    try:
        conn = get_connection()
        cursor = conn.cursor()
        
        # Obtener el último CostingNum
        query = """
        SELECT ISNULL(MAX(CostingNum), 999) + 1 AS NextCostingNum
        FROM Q_CostingHead
        """
        cursor.execute(query)
        row = cursor.fetchone()
        
        return row.NextCostingNum if row else 1000
    except Exception as e:
        logger.error(f"Error al generar CostingNum: {str(e)}")
        return 1000
    finally:
        cursor.close()
        conn.close()


def generar_costing_headSQL(data):
    """
    Endpoint que crea Q_CostingHead y Q_CostingDetail en una transacción.
    Obtiene el último CostingNum, incrementa +1 y lo usa para insertar.
    """
    conn = None
    cursor = None
    
    try:
        # ============================
        # 1️⃣ VALIDAR DATOS OBLIGATORIOS
        # ============================
        form_id = data.get("FormID")
        user_id = data.get("UserID")
        seller_user_id = data.get("SellerUserID")


        logger.info(f"costingId {data.get('CostingID')}")

        if not form_id or not user_id:
            return jsonify({"error": "Faltan parámetros: FormID y UserID son obligatorios"}), 400

        logger.info(f"📋 Iniciando creación de Costing - FormID: {form_id}, UserID: {user_id}")

        # ============================
        # 2️⃣ EJECUTAR SUBCONSULTAS
        # ============================
        sub1 = obtener_datos_formulario(form_id)
        if not sub1:
            return jsonify({"error": f"No se encontró información para FormID {form_id}"}), 404

        sub2 = obtener_contexto_usuario(seller_user_id)
        if not sub2:
            return jsonify({"error": f"No se encontró contexto para UserID {seller_user_id}"}), 404

        # ============================
        # 3️⃣ INICIAR CONEXIÓN
        # ============================
        conn = get_connection()
        cursor = conn.cursor()

        version = data.get("Version", 1)

        # ============================
        # 4️⃣ OBTENER EL ÚLTIMO CostingNum
        # ============================
        cursor.execute("SELECT ISNULL(MAX(CostingNum), 999) FROM Q_CostingHead;")
        last_num = cursor.fetchone()[0]
        costing_num = last_num + 1
        costing_id = f"{costing_num}-{version}"

        logger.info(f"🧮 Generando nuevo CostingNum: {costing_num} (último fue {last_num})")

        # ============================
        # 5️⃣ INSERTAR Q_CostingHead
        # ============================
        insert_head_query = """
        INSERT INTO Q_CostingHead (
            CostingNum, Version, QuotationTypeID, UserID, RoleID, SellerUserID,
            CompanyID, DivisionID, DepartamentID, CaseCost,
            DirectCost, IndirectPercent, IndirectAmount,
            FinancePercent, FinanceAmount, UtilityPercent, UtilityAmount,
            OperationPercent, OperationAmount, SalePriceMin,
            DiscountMaxPercent, DiscountMaxAmount, SalePriceList, OvercostFactor,
            CRM_OpportunityID, ApprovalProcessID, PreviusDocsID, DocsID,
            TechnicalTermsAndConditions, RunTimeNumber, RunTimeType, Active,
            CurrencyCode, TaxCode
        )
        VALUES (
            ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
            ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
            ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
        )
        """
        print("Inserting into Q_CostingHead with values:")

        cursor.execute(insert_head_query, (
            costing_num,
            version,
            sub1.get("QuotationTypeID"),
            user_id,
            sub2.get("RoleID"),
            seller_user_id,
            sub2.get("CompanyID"),
            sub2.get("DivisionID"),
            sub2.get("DepartamentID"),
            data.get("CaseCost"),
            data.get("DirectCost"),
            data.get("IndirectPercent"),
            data.get("IndirectAmount"),
            data.get("FinancePercent"),
            data.get("FinanceAmount"),
            data.get("UtilityPercent"),
            data.get("UtilityAmount"),
            data.get("OperationPercent"),
            data.get("OperationAmount"),
            data.get("SalePriceMin"),
            data.get("DiscountMaxPercent"),
            data.get("DiscountMaxAmount"),
            data.get("SalePriceList"),
            data.get("OvercostFactor"),
            sub1.get("CRM_OpportunityID"),
            data.get("ApprovalProcessID"),
            data.get("PreviusDocsID"),
            data.get("DocsID"),
            data.get("TechnicalTermsAndConditions"),
            data.get("RunTimeNumber"),
            data.get("RunTimeType"),
            data.get("Active", True),
            data.get("CurrencyCode"),
            data.get("TaxCode")
        ))


        logger.info(f"✅ Q_CostingHead insertado correctamente con CostingNum={costing_num}")
        logger.info(f"----case cost: {data.get('CaseCost')}----")

        # ============================
        # 6️⃣ INSERTAR Q_CostingDetail
        # ============================
        detail_items = data.get("Q_CostingDetail", [])
        items_insertados = 0

        caso = data.get("CaseCost")  # 👈 sin coma
        
        if caso == 'A':
            factorOverCost = 1
        
        elif caso in ('B', 'C'):
            factorOverCost = (
                (1 + (data.get("IndirectPercent", 0) / 100)) *
                (1 + (data.get("FinancePercent", 0) / 100)) *
                (1 + (data.get("UtilityPercent", 0) / 100)) *
                (1 + (data.get("OperationPercent", 0) / 100))
            )

        logger.info(f"OvercostFactor para detalles: {factorOverCost:.6f}")
        

        if detail_items:
            insert_detail_query = """
            INSERT INTO Q_CostingDetail (
                CostingID, CostingLine, PartNum, PartDescription,
                Qty, UOMCode, UnitPrice, Amount
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
            """

            for item in detail_items:
                cursor.execute(insert_detail_query, (
                    costing_id,
                    item.get("CostingLine"),
                    item.get("PartNum"),
                    item.get("PartDescription"),
                    item.get("Qty"),
                    item.get("UOMCode"),
                    (item.get("UnitPrice")  * factorOverCost) ,
                    (item.get("UnitPrice")   * factorOverCost * item.get("Qty"))
                ))
                items_insertados += 1

            logger.info(f"✅ {items_insertados} items insertados en Q_CostingDetail")
        else:
            logger.warning("⚠️ No hay items en Q_CostingDetail para insertar")

        TaskID =data.get("TaskID")
        query3= """
        UPDATE Q_SpQ_QuotationTasks
        SET 
            Status = 'COMPLETADO',
            StatusDate = CAST(SYSDATETIMEOFFSET() AT TIME ZONE 'Central America Standard Time' AS DATETIME)
        WHERE TaskID =?;
        """
        cursor.execute(query3, (TaskID))


        # consultar si todas las tareas estan completadas
        query4= """
        SELECT
            COUNT(TaskID) AS NumeroDeTareasPorAsignarNoCompletadas
        FROM
            Q_SpQ_QuotationTasks
        WHERE
            CRM_OpportunityID = ? AND
            Status <> 'COMPLETADO';
        """
        cursor.execute(query4, (sub1.get("CRM_OpportunityID"),))

        resultQuery4 = cursor.fetchone()
        numero_de_tareas = resultQuery4[0]


        # Si todas las tareas están completadas, actualizar el estado de la oportunidad CRM
        if numero_de_tareas == 0:
            query5 = """
            UPDATE Q_OpportunityCRM
            SET
                Status = 'ATENDIDO',
                UpdatedAt = CAST(SYSDATETIMEOFFSET() AT TIME ZONE 'Central America Standard Time' AS DATETIME) -- Actualiza la fecha de modificación
                --UpdatedBy = 'NOMBRE_USUARIO_QUE_ACTUALIZA' -- Actualiza quién realizó la modificación
            WHERE
                CRM_OpportunityID = ?;
            """

            cursor.execute(query5, (sub1.get("CRM_OpportunityID"),))
            logger.info(f"✅ Oportunidad CRM {sub1.get('CRM_OpportunityID')} actualizada a 'ATENDIDO'")

        print("costing id generado:",costing_id)

        query6 = """
            select CostingDate
            from Q_CostingHead
            where CostingID = ?;
        """
        cursor.execute(query6, (costing_id,))
        resultQuery6 = cursor.fetchone()
        costing_date = resultQuery6[0] if resultQuery6 else None

        logger.info(f"✅ CostingDate obtenido: {costing_date} para CostingID {costing_id}")

        logger.info(f"task id para actualizar en costing tasks: {TaskID}")
        query7 = """
        UPDATE dbo.q_spq_quotationtasks
        SET 
            CostingID = ?,
            CostingDate = ?
        WHERE 
            TaskID = ?
        """
        cursor.execute(query7, (costing_id, costing_date, TaskID))

        # ============================
        # 7️⃣ CONFIRMAR TRANSACCIÓN
        # ============================
        conn.commit()
        logger.info(f"✅ Transacción confirmada - CostingID: {costing_id}")

        # ============================
        # 8️⃣ RESPUESTA EXITOSA
        # ============================
        return jsonify({
            "success": True,
            "message": "Costing creado exitosamente",
            "data": {
                "CostingID": costing_id,
                "CostingNum": costing_num,
                "Version": version,
                "QuotationTypeID": sub1.get("QuotationTypeID"),
                "CRM_OpportunityID": sub1.get("CRM_OpportunityID"),
                "ItemsInsertados": items_insertados,
                "DocsID": data.get("DocsID")
            }
        }), 201

    # ============================
    # ERRORES
    # ============================
    except pyodbc.IntegrityError as db_error:
        if conn: conn.rollback()
        logger.error(f"❌ Error de integridad: {db_error}")
        return jsonify({"error": "Error de integridad de datos", "details": str(db_error)}), 409

    except pyodbc.Error as db_error:
        if conn: conn.rollback()
        logger.error(f"❌ Error de base de datos: {db_error}")
        return jsonify({"error": "Error en base de datos", "details": str(db_error)}), 500

    except Exception as e:
        if conn: conn.rollback()
        logger.error(f"❌ Error inesperado: {e}")
        return jsonify({"error": "Error al crear costing", "details": str(e)}), 500

    finally:
        if cursor:
            try: cursor.close()
            except: pass
        if conn:
            try:
                conn.close()
                logger.info("🔒 Conexión cerrada")
            except:
                pass


def update_costing_head_docsidSQL(data):
    """
    Endpoint para actualizar el DocsID en Q_CostingHead.
    """
    logger.info("🔄 Iniciando actualización de DocsID en Q_CostingHead")
    conn = None
    cursor = None

    try:
        logger.info(f"Datos recibidos para actualización: {data}")

        # ============================
        # 1️⃣ VALIDAR DATOS OBLIGATORIOS
        # ============================
        costing_id = data.get("CostingID")
        docs_id = data.get("DocsID")
        logger.info(f"costingId recibido: {costing_id}")
        logger.info(f"docsId recibido: {docs_id}")

        if not costing_id or not docs_id:
            return jsonify({"error": "Faltan parámetros: CostingID y DocsID son obligatorios"}), 400

        logger.info(f"📋 Iniciando actualización de DocsID para CostingID: {costing_id}")

        # ============================
        # 2️⃣ INICIAR CONEXIÓN Y TRANSACCIÓN
        # ============================
        conn = get_connection()
        cursor = conn.cursor()

        # ============================
        # 3️⃣ EJECUTAR ACTUALIZACIÓN
        # ============================
        update_query = """
        UPDATE Q_CostingHead
        SET DocsID = ?
        WHERE CostingID = ?
        """
        cursor.execute(update_query, (docs_id, costing_id))

        # ============================
        # 4️⃣ VERIFICAR SI LA ACTUALIZACIÓN FUE EXITOSA
        # ============================
        if cursor.rowcount == 0:
            # Si no se actualizó ninguna fila, es probable que el CostingID no exista
            logger.warning(f"⚠️ No se encontró el CostingID {costing_id} para actualizar.")
            return jsonify({"error": f"No se encontró un registro con CostingID {costing_id}"}), 404

        # ============================
        # 5️⃣ CONFIRMAR TRANSACCIÓN
        # ============================
        conn.commit()
        logger.info(f"✅ DocsID actualizado correctamente a '{docs_id}' para CostingID {costing_id}")

        # ============================
        # 6️⃣ RESPUESTA EXITOSA
        # ============================
        return jsonify({
            "success": True,
            "message": f"DocsID actualizado correctamente en CostingID {costing_id}"
        }), 200

    # ============================
    # MANEJO DE ERRORES
    # ============================
    except pyodbc.Error as db_error:
        if conn: conn.rollback()
        logger.error(f"❌ Error de base de datos al actualizar DocsID: {db_error}")
        return jsonify({"error": "Error en la base de datos", "details": str(db_error)}), 500

    except Exception as e:
        if conn: conn.rollback()
        logger.error(f"❌ Error inesperado al actualizar DocsID: {e}")
        return jsonify({"error": "Ocurrió un error inesperado", "details": str(e)}), 500

    # ============================
    # 7️⃣ CIERRE DE CONEXIÓN
    # ============================
    finally:
        if cursor:
            try: cursor.close()
            except: pass
        if conn:
            try:
                conn.close()
                logger.info("🔒 Conexión a la base de datos cerrada.")
            except:
                pass
    """
    Endpoint para actualizar el DocsID en Q_CostingHead.
    """
    conn = None
    cursor = None

    try:
        data = request.get_json()

        # ============================
        # 1️⃣ VALIDAR DATOS OBLIGATORIOS
        # ============================
        costing_id = data.get("CostingID")
        docs_id = data.get("DocsID")

        if not costing_id or not docs_id:
            return jsonify({"error": "Faltan parámetros: CostingID y DocsID son obligatorios"}), 400

        logger.info(f"📋 Iniciando actualización de DocsID para CostingID: {costing_id}")

        # ============================
        # 2️⃣ INICIAR CONEXIÓN Y TRANSACCIÓN
        # ============================
        conn = get_connection()
        cursor = conn.cursor()

        # ============================
        # 3️⃣ EJECUTAR ACTUALIZACIÓN
        # ============================
        update_query = """
        UPDATE Q_CostingHead
        SET DocsID = ?
        WHERE CostingID = ?
        """
        cursor.execute(update_query, (docs_id, costing_id))

        # ============================
        # 4️⃣ VERIFICAR SI LA ACTUALIZACIÓN FUE EXITOSA
        # ============================
        if cursor.rowcount == 0:
            # Si no se actualizó ninguna fila, es probable que el CostingID no exista
            logger.warning(f"⚠️ No se encontró el CostingID {costing_id} para actualizar.")
            return jsonify({"error": f"No se encontró un registro con CostingID {costing_id}"}), 404

        # ============================
        # 5️⃣ CONFIRMAR TRANSACCIÓN
        # ============================
        conn.commit()
        logger.info(f"✅ DocsID actualizado correctamente a '{docs_id}' para CostingID {costing_id}")

        # ============================
        # 6️⃣ RESPUESTA EXITOSA
        # ============================
        return jsonify({
            "success": True,
            "message": f"DocsID actualizado correctamente en CostingID {costing_id}"
        }), 200

    # ============================
    # MANEJO DE ERRORES
    # ============================
    except pyodbc.Error as db_error:
        if conn: conn.rollback()
        logger.error(f"❌ Error de base de datos al actualizar DocsID: {db_error}")
        return jsonify({"error": "Error en la base de datos", "details": str(db_error)}), 500

    except Exception as e:
        if conn: conn.rollback()
        logger.error(f"❌ Error inesperado al actualizar DocsID: {e}")
        return jsonify({"error": "Ocurrió un error inesperado", "details": str(e)}), 500

    # ============================
    # 7️⃣ CIERRE DE CONEXIÓN
    # ============================
    finally:
        if cursor:
            try: cursor.close()
            except: pass
        if conn:
            try:
                conn.close()
                logger.info("🔒 Conexión a la base de datos cerrada.")
            except:
                pass


def upload_archivos_simple(archivos, modulo_id, user_id,CostingID:str, categoria="General") :
    """
    Patrón simplificado para casos con una sola categoría
    """

    # Crear grupo único
    docs_result = crear_docs_head(user_id, "Q_CostingHead", obtener_nombre_usuario(user_id))
    docs_id = docs_result['docs_id']

    # Subir archivos
    urls_generadas = []
    for archivo in archivos:
        resultado = upload_document_to_existing_docs(
            file_obj=archivo,
            docs_id=docs_id,
            title=archivo.filename,
            description=f"Archivo de {modulo_id}",
            ruta_completa=f"Ventas/Costeo/{CostingID}"
        )

        if resultado['success']:
            urls_generadas.append(resultado['data']['download_url'])

    return {
        'success': len(urls_generadas) > 0,
        'docs_id': docs_id,
        'archivos_subidos': len(urls_generadas),
        'urls': urls_generadas
    }


def obtener_nombre_usuario(user_id: int) -> str:
    """
    Obtiene el nombre completo del usuario desde la tabla Profiles
    
    Args:
        user_id (int): ID del usuario
    
    Returns:
        str: Nombre completo del usuario
    """
    
    query = """
        SELECT 
            CONCAT(
                COALESCE(Profiles.FirstName, ''), ' ',
                COALESCE(Profiles.LastName, ''), ' ',
                COALESCE(Profiles.SecondLastName, '')
            ) AS FullName
        FROM Profiles
        WHERE Profiles.UserID = ?
            AND Profiles.UserID IN (
                SELECT Users.UserID 
                FROM Users 
                WHERE Users.Status = 'ACTIVO'
            )
    """

    conn = None
    try:
        conn = get_connection()
        if not conn:
            return f"Usuario_{user_id}"  # Fallback
        
        with conn:
            cursor = conn.cursor()
            cursor.execute(query, (user_id,))
            
            row = cursor.fetchone()
            if row:
                # Limpiar espacios extra del nombre concatenado
                full_name = ' '.join(row[0].split())
                return full_name if full_name.strip() else f"Usuario_{user_id}"
            else:
                return f"Usuario_{user_id}"
            
    except Exception as e:
        logger.warning(f"Error al obtener nombre de usuario {user_id}: {str(e)}")
        return f"Usuario_{user_id}"
        
    finally:
        if conn:
            try:
                conn.close()
            except:
                pass



def buscar_score() -> Optional[List[Dict]]:
    """
    Busca todos los datos de score en la tabla Score de la base de datos

    Returns:
        List[Dict]: Lista de diccionarios con los datos de los scores encontrados
        None: Si no se encuentra ningún registro

    Raises:
        ConnectionError: Si no se puede establecer conexión con la BD
        Exception: Para otros errores durante la consulta
    """
    query = """
       SELECT * FROM Score;
       
    """

    conn = None

    try:
        # Obtener la conexión
        conn = get_connection()
        if not conn:
            raise ConnectionError("No se pudo establecer conexión con la base de datos")
        
        with conn:
            cursor = conn.cursor()
            cursor.execute(query)

            # Obtener los nombres de las columnas
            columns = [column[0] for column in cursor.description]

            # Obtener todos los resultados
            rows = cursor.fetchall()

            if rows:
                # Convertir cada tupla en diccionario
                results = []
                for row in rows:
                    result = dict(zip(columns, row))
                    
                    # Convertir datetime a string para JSON serialization
                    for date_field in ['CreatedAt', 'UpdatedAt']:
                        if date_field in result and result[date_field]:
                            result[date_field] = result[date_field].isoformat()
                    
                    results.append(result)

                # Log de la búsqueda exitosa
                logger.info(f"Se encontraron {len(results)} registros de score")
                return results
            else:
                # Log cuando no se encuentra el score
                logger.warning("No se encontraron registros en la tabla Score")
                return None

    except pyodbc.Error as e:
        error_msg = f"Error de base de datos al buscar los scores: {str(e)}"
        logger.error(error_msg)
        raise Exception(error_msg)
    
    except ConnectionError as e:
        error_msg = f"Error de conexión al buscar los scores: {str(e)}"
        logger.error(error_msg)
        raise Exception(error_msg)
    
    except Exception as e:
        error_msg = f"Error inesperado al buscar los scores: {str(e)}"
        logger.error(error_msg)
        raise Exception(error_msg)
    
    finally:
        # Asegurar que la conexión se cierre
        if conn:
            try:
                conn.close()
            except Exception:
                logger.warning("Error al cerrar la conexión a la BD")


def buscar_tarea_crm(task_id: float) -> Optional[Dict]:
    """
    Busca datos de la oportunidad en la tabla Q_CRM

    Args:

    Returns:

    Raises:


    """

    query = """
        SELECT TOP 1
            Q_CRM.CRM_OpportunityNumber,
            Q_CRM.CRM_Version,
            Q_CRM.CRM_ContactID,
            Q_CRM.CRM_ContactName,
            Q_CRM.CRM_ContactType,
            Q_CRM.CRM_AssignedSalesperson,
            Q_CRM.CRM_ContactAdress,
            Q_CRM.CRM_ContactColonia,
            Q_CRM.CRM_ContactCity,
            Q_CRM.CRM_ContactNumber,
            Q_CRM.CRM_ContactCountry,
            Q_CRM.CRM_ContactLegalIdentifier,
            Q_CRM.CRM_ContactZip,
            Q_CRM.CRM_ContactState,
            Q_CRM.CRM_ContactEmail,
            Q_CRM.Status,
            Q_CRM.CreatedAt,
            Q_CRM.CreatedBy,
            Q_CRM.Active
        FROM 
            Q_SpQ_QuotationTasks  
            JOIN Q_SpQ_FormsHead   
            ON  Q_SpQ_QuotationTasks.FormID = Q_SpQ_FormsHead.FormID
            JOIN Q_OpportunityCRM  
            ON Q_SpQ_FormsHead.CRM_OpportunityID = Q_OpportunityCRM.CRM_OpportunityID
            JOIN Q_CRM  
            ON Q_OpportunityCRM.CRM_OpportunityNumber = Q_CRM.CRM_OpportunityNumber
        WHERE
            Q_SpQ_QuotationTasks.taskID = ?
            AND Q_CRM.Active = 1
        ORDER BY Q_CRM.CRM_Version DESC;
    """

    conn = None

    try:
        # Obtener la conexión
        conn = get_connection()
        if not conn:
            raise ConnectionError("No se pudo establecer conexión con la base de datos")
        
        with conn:
            cursor = conn.cursor()
            cursor.execute(query, (task_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
                result = dict(zip(columns, row))

                #Log de la búsqueda exitosa
                logger.info(f"Oportunidad {task_id} encontrada exitosamente")

                # Convertir datetime a string para JSON serialization
                if 'CreatedAt' in result and result['CreatedAt']:
                    result['CreatedAt'] = result['CreatedAt'].isoformat()

                return result
            else:
                #Log cuando no se encuentra la oportunidad
                logger.warning(f"Oportunidad {task_id} no encontrada en la base de datos")
                return None

    except pyodbc.Error as e:
        #Error específico en la base de datos
        error_msg = f"Error de base de datos al buscar la oportunidad { task_id }: { str(e) }"
        logger.error(error_msg)
        raise Exception(error_msg)
    
    except ConnectionError as e:
        #Error de conexión
        error_msg = f"Error de conexión al buscar la oprtunidad { task_id }: { str(e) }"
        logger.error(error_msg)
        raise Exception(error_msg)
    
    except Exception as e:
        # Cualquier otro error
        error_msg = f"Error inesperado al buscar la oportunidad { task_id }: { str(e) }"
        logger.error(error_msg)
        raise Exception(error_msg)
    
    finally:
        # Asegurar que la conexión se cierre
        if conn:
            try:
                conn.close()
            except:
                pass


def buscar_oportunidad_crm(opportunity_number: float) -> Optional[Dict]:
    """
    Busca una oportunidad en la tabla Q_OpportunityCRM
    
    Args:
        opportunity_number (float): Número de oportunidad del CRM
    
    Returns:
        Dict: Diccionario con los datos de la oportunidad si se encuentra, None si no existe
        
    Raises:
        ConnectionError: Si no se puede establecer conexión con la base de datos
        Exception: Para otros errores de base de datos
    """
    
    query = """
        SELECT TOP 1
            Q_CRM.CRM_OpportunityNumber,
            Q_CRM.CRM_Version,
            Q_CRM.CRM_ContactID,
            Q_CRM.CRM_ContactName,
            Q_CRM.CRM_ContactType,
            Q_CRM.CRM_AssignedSalesperson,
            Q_CRM.CRM_ContactAdress,
            Q_CRM.CRM_ContactColonia,
            Q_CRM.CRM_ContactCity,
            Q_CRM.CRM_ContactNumber,
            Q_CRM.CRM_ContactCountry,
            Q_CRM.CRM_ContactLegalIdentifier,
            Q_CRM.CRM_ContactZip,
            Q_CRM.CRM_ContactState,
            Q_CRM.CRM_ContactEmail,
            Q_CRM.Status,
            Q_CRM.CreatedAt,
            Q_CRM.CreatedBy,
            Q_CRM.Active
        FROM Q_CRM
        WHERE Q_CRM.CRM_OpportunityNumber = ?
            AND Q_CRM.Active = 1
        ORDER BY Q_CRM.CRM_Version DESC
    """

    conn = None
    try:
        # Obtener conexión
        conn = get_connection()
        if not conn:
            raise ConnectionError("No se pudo establecer conexión con la base de datos")
        
        with conn:
            cursor = conn.cursor()
            cursor.execute(query, (opportunity_number,))
            
            # 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
                result = dict(zip(columns, row))
                
                # Log de la búsqueda exitosa
                logger.info(f"Oportunidad {opportunity_number} encontrada exitosamente")
                
                # Convertir datetime a string para JSON serialization
                if 'CreatedAt' in result and result['CreatedAt']:
                    result['CreatedAt'] = result['CreatedAt'].isoformat()
                
                return result
            else:
                # Log cuando no se encuentra la oportunidad
                logger.warning(f"Oportunidad {opportunity_number} no encontrada en la base de datos")
                return None
            
    except pyodbc.Error as e:
        # Error específico de base de datos
        error_msg = f"Error de base de datos al buscar oportunidad {opportunity_number}: {str(e)}"
        logger.error(error_msg)
        raise Exception(error_msg)
        
    except ConnectionError as e:
        # Error de conexión
        error_msg = f"Error de conexión al buscar oportunidad {opportunity_number}: {str(e)}"
        logger.error(error_msg)
        raise ConnectionError(error_msg)
        
    except Exception as e:
        # Cualquier otro error
        error_msg = f"Error inesperado al buscar oportunidad {opportunity_number}: {str(e)}"
        logger.error(error_msg)
        raise Exception(error_msg)
        
    finally:
        # Asegurar que la conexión se cierre
        if conn:
            try:
                conn.close()
            except:
                pass


def verificar_oportunidad_existente(opportunity_number: str) -> Optional[Dict]:
    """
    Verifica si ya existe una oportunidad y retorna la versión más alta
    También valida que no esté cerrada/vendida o en proceso activo
    
    Args:
        opportunity_number (str): Número de oportunidad del CRM
    
    Returns:
        Dict: Datos de la oportunidad existente con versión más alta, None si no existe
        
    Raises:
        Exception: Si la oportunidad está cerrada/vendida o en proceso activo
    """
    
    query = """
        SELECT TOP 1
            Q_OpportunityCRM.CRM_OpportunityNumber,
            Q_OpportunityCRM.Version,
            Q_OpportunityCRM.CRM_OpportunityID,
            Q_OpportunityCRM.Status,
            Q_OpportunityCRM.Active
        FROM Q_OpportunityCRM
        WHERE Q_OpportunityCRM.CRM_OpportunityNumber = ?
            AND Q_OpportunityCRM.Active = 1
        ORDER BY Q_OpportunityCRM.Version DESC
    """

    conn = None
    try:
        conn = get_connection()
        if not conn:
            raise ConnectionError("No se pudo establecer conexión con la base de datos")
        
        with conn:
            cursor = conn.cursor()
            cursor.execute(query, (opportunity_number,))
            
            columns = [column[0] for column in cursor.description]
            row = cursor.fetchone()
            
            if row:
                result = dict(zip(columns, row))
                
                # 🚨 VALIDACIONES DE STATUS
                status = result.get('Status', '').upper()
                
                # 1. Validar si está cerrada o vendida
                if status in ['VENDIDO', 'VENDIDA', 'ATENDIDO']:
                    logger.warning(f"Oportunidad {opportunity_number} tiene status {status} - proceso terminado")
                    raise ValueError(f"OPPORTUNITY_CLOSED:{status}")
                
                # 2. Validar si está en proceso activo (NUEVA VALIDACIÓN)
                if status in ['PENDIENTE', 'EN DESARROLLO', 'EN APROBACION', 'EN_DESARROLLO', 'EN_APROBACION']:
                    logger.warning(f"Oportunidad {opportunity_number} tiene status {status} - proceso activo")
                    raise ValueError(f"OPPORTUNITY_IN_PROCESS:{status}")
                
                logger.info(f"Oportunidad {opportunity_number} encontrada con versión {result['Version']} y status {status}")
                return result
            else:
                logger.info(f"Oportunidad {opportunity_number} no encontrada")
                return None
            
    except ValueError as e:
        # Re-lanzar errores de validación para que los maneje el proceso principal
        raise e
        
    except pyodbc.Error as e:
        error_msg = f"Error de base de datos al verificar oportunidad {opportunity_number}: {str(e)}"
        logger.error(error_msg)
        raise Exception(error_msg)
        
    except Exception as e:
        error_msg = f"Error inesperado al verificar oportunidad {opportunity_number}: {str(e)}"
        logger.error(error_msg)
        raise Exception(error_msg)
        
    finally:
        if conn:
            try:
                conn.close()
            except:
                pass