from Consultas_SQL.conexion import get_connection
# Asegúrate de que la ruta de importación a tu modelo Pydantic sea la correcta

class CZE_Quotes_Service:


    def updateQuote_stopButtonEnclosure(self,quote_id) -> bool:
        """
        Actualiza la información del StopButtonEnclosure en la cotización CZE_Quotes

        params:
        - quote_id: ID de la cotización a actualizar (tabla CZE_Quotes).

        return: 
        - True si la actualización fue exitosa, False en caso contrario.
        """
        try:
            with get_connection() as conn:
                with conn.cursor() as cursor:

                    query  = """
                            SELECT
                            StopButtonEnclosure,
                            StopButtonEnclosure_Qty
                            FROM CZE_Quotes
                            WHERE QuoteID = ?;
                            """
                    cursor.execute(query, (quote_id,))
                    stop_id,StopButtonEnclosure_Qty = cursor.fetchone()


                    # Recuperar la informacion ------------------------
                    query = """
                                SELECT 
                                stop_price,
                                stop_technicalSheet
                                FROM StopButtonEnclosureOptions
                            where stop_id = ?
                            """
                    cursor.execute(query, (stop_id,))
                    StopButtonEnclosure_Price,StopButtonEnclosure_TSheet= cursor.fetchone()


                    # Actualizar en Quotes la informacion -----------------
                    StopButtonEnclosure_PriceTot = float(StopButtonEnclosure_Price) * float(StopButtonEnclosure_Qty)
                    query = """
                            UPDATE CZE_Quotes
                            SET
                            StopButtonEnclosure_Price = ?,
                            StopButtonEnclosure_TSheet = ?,
                            StopButtonEnclosure_PriceTot = ?
                            WHERE QuoteID = ?;
                            """
                    cursor.execute(query, (StopButtonEnclosure_Price, StopButtonEnclosure_TSheet, StopButtonEnclosure_PriceTot, quote_id))
                    return True
        except Exception as e:
            print(f"Error actualizando CZE_Quotes 001: {e}")
            return False
        

    def update_quote_louvers_in(self,quote_id) -> bool:
        """
        Actualiza la información de los louvers en la cotización CZE_Quotes

        params:
        - quote_id: ID de la cotización a actualizar (tabla CZE_Quotes).

        return: 
        - True si la actualización fue exitosa, False en caso contrario.
        """
        try:
            with get_connection() as conn:
                with conn.cursor() as cursor:

                    # Obtener el id del objeto --
                    query  = """
                            SELECT
                            louvers_in
                            FROM CZE_Quotes
                            WHERE QuoteID = ?;
                            """
                    cursor.execute(query, (quote_id,))
                    louvers_in = cursor.fetchone()[0]


                    # Recuperar la informacion del objeto ------------------------
                    query = """
                                select 
                                louvers_in_price,
                                louvers_in_technicalSheet
                                from louvers_in
                                where louvers_in_id = ?
                            """

                    cursor.execute(query, (louvers_in))
                    louvers_in_price,louvers_in_technicalSheet = cursor.fetchone()


                    # Actualizar en Quotes la informacion -----------------
                    louvers_in_PriceTot = louvers_in_price
                    query = """
                            UPDATE CZE_Quotes
                            SET
                            louvers_in_price = ?,
                            louvers_in_tsheet = ?,
                            louvers_in_pricetot = ?
                            
                            WHERE QuoteID = ?;
                            """
    
                    cursor.execute(query, (louvers_in_price,louvers_in_technicalSheet,louvers_in_PriceTot ,quote_id))
                    return True
        except Exception as e:
            print(f"Error actualizando CZE_Quotes 002: {e}")
            return False


    def update_quote_louvers_out(self,quote_id) -> bool:
        """
        Actualiza la información de los louvers en la cotización CZE_Quotes

        params:
        - quote_id: ID de la cotización a actualizar (tabla CZE_Quotes).

        return: 
        - True si la actualización fue exitosa, False en caso contrario.
        """
        try:
            with get_connection() as conn:
                with conn.cursor() as cursor:

                    # Obtener el id del objeto --
                    query  = """
                            SELECT
                            louvers_out
                            FROM CZE_Quotes
                            WHERE QuoteID = ?;
                            """
                    cursor.execute(query, (quote_id,))
                    louvers_out = cursor.fetchone()[0]


                    # Recuperar la informacion del objeto ------------------------
                    query = """
                                select 
                                louvers_out_price,
                                louvers_out_technicalSheet
                                from louvers_out
                                where louvers_out_id = ?
                            """

                    cursor.execute(query, (louvers_out,))
                    louvers_out_price,louvers_out_technicalSheet = cursor.fetchone()


                    # Actualizar en Quotes la informacion -----------------
                    louvers_out_PriceTot = louvers_out_price
                    query = """
                            UPDATE CZE_Quotes
                            SET
                            louvers_out_price = ?,
                            louvers_out_tsheet = ?,
                            louvers_out_pricetot = ?
                            
                            WHERE QuoteID = ?;
                            """
    
                    cursor.execute(query, (louvers_out_price,louvers_out_technicalSheet,louvers_out_PriceTot ,quote_id))
                    return True
        except Exception as e:
            print(f"Error actualizando CZE_Quotes 002: {e}")
            return False

    def get_field(self,getFields: list) -> dict:
        """
        Obtiene los valores de los campos especificados en getFields.

        params:
        - getFields: Lista de nombres de campos a obtener.

        return:
        - Diccionario con los valores de los campos solicitados.
        """
        try:
            with get_connection() as conn:
                with conn.cursor() as cursor:
                    query = f"SELECT {', '.join(getFields)} FROM CZE_Quotes WHERE QuoteID = ?;"
                    cursor.execute(query, (self.quote_id,))
                    result = cursor.fetchone()
                    return dict(zip(getFields, result)) if result else {}
        except Exception as e:
            print(f"Error obteniendo campos de CZE_Quotes: {e}")
            return {}