fix: temporary adaptation for calculating LLM usage for diarization in seconds based on tokens

backend/app/services/report_service.py

@@ -18,8 +18,7 @@ import json
 class TranscriptionReportService:
     def __init__(self, company_id: str, start_date: str, end_date: str):
         self.company_id = str(company_id)
-        self.start_date = start_date
-        self.end_date = end_date
+        self.start_date = start_date 
         self.end_date = end_date
         self.mongo_client = current_app.mongo_client 
         self.mongo_results = []
@@ -129,6 +128,7 @@ class TranscriptionReportService:
         # Executa agregação principal
         self.mongo_results = list(collection.aggregate(pipeline))
         self.unique_ids = [doc["_id"] for doc in self.mongo_results] 
+         
         
         #  TEST
         # print("======> self.mongo_results: ", self.mongo_results)
@@ -138,7 +138,35 @@ class TranscriptionReportService:
             
         #     stt = rowMongo.get('usageByType', {}).get('stt', {}) 
                     
-        #     if not stt:            
+        #     if not stt:
+                
+        #         # print("==========> rowMongo: ", rowMongo)
+                
+        #         # input_text = rowMongo.get('usageByType', {}).get('input-text-gemini-2.5-flash', {})
+        #         # input_audio = rowMongo.get('usageByType', {}).get('input-audio-gemini-2.5-flash', {})
+        #         # output = rowMongo.get('usageByType', {}).get('output-text-gemini-2.5-flash', {})
+                
+        #         # input_text_usage_cost = input_text.get("usageCost", 0)
+        #         # input_audio_usage_cost = input_audio.get("usageCost", 0)
+        #         # output_usage_cost = output.get("usageCost", 0)
+                
+        #         # token_by_second = 1920 / 60                    
+        #         # usageSeconds = round(input_audio.get('usage', 0) / token_by_second)     
+                
+        #         # total_cost_stt = (input_text_usage_cost + input_audio_usage_cost + output_usage_cost)
+                
+        #         # print("============> total_cost_stt: ", total_cost_stt)
+        #         # print("============> usageSeconds: ", usageSeconds)
+        #         # print("")
+                
+        #         # print("======> stt_model: ", input_audio.get('product', 'unknown'))
+        #         # print("======> stt_provider: ", input_audio.get('provider', 'unknown'))  
+        #         # print("======> stt_cost: ", total_cost_stt)    
+        #         # print("======> stt_usageSeconds: ", f"{usageSeconds}") 
+                
+        #         # exit()
+                
+                         
         #         stt = rowMongo.get('usageByType', {}).get('input-audio', {})   
                 
         #         # minutes
@@ -178,83 +206,107 @@ class TranscriptionReportService:
         }
   
 
-    def _fetch_mysql_data(self,  hit_report: Optional[bool] = False)-> List[Dict[str, Any]]:
+    def _fetch_mysql_data(self, hit_report: Optional[bool] = False) -> List[Dict[str, Any]]:
+        # Carrega tabela de produtos (usada depois em client_price_row)
+        collection = self.mongo_client["billing-api"]["api_products"]
+        products = list(collection.find({})) 
 
-        collection = self.mongo_client["billing-api"]["api_products"] 
+        # IDs vindos do Mongo (sessionId). Se vazio, nada a consultar.
+        ids = [str(uid) for uid in self.unique_ids]
+        if not ids:
+            return []
 
-        products = list(collection.find({}))  
+        # Monta IN(...) seguro contra quote simples e compatível com 1 ou N itens
+        def _sql_quote(v: str) -> str:
+            return "'" + v.replace("'", "''") + "'"
 
-        sql = f"""SELECT 
-                    uniqueid,
-                    src,
-                    dst,
-                    MIN(calldate) AS start_call,
-                    MAX(calldate) AS end_call,
-                    SUM(CASE 
-                            WHEN dstchannel LIKE 'PJSIP/%' AND lastapp = 'Queue' 
-                            THEN billsec 
-                            ELSE 0 
-                        END) AS total_billsec
-                FROM 
-                    tab_cdr
-                WHERE 
-                    uniqueid IN {tuple(self.unique_ids)}
-                GROUP BY 
-                    uniqueid, src, dst;""" 
-        rows = execute_query(self.company_id, sql) 
-           
+        in_clause = "(" + ",".join(_sql_quote(v) for v in ids) + ")"
 
-        if hit_report: 
-            for row in rows:  
-                row["companyId"] = self.company_id 
+        sql = f"""
+            SELECT 
+                uniqueid,
+                src,
+                dst,
+                MIN(calldate) AS start_call,
+                MAX(calldate) AS end_call,
+                SUM(
+                    CASE 
+                        WHEN dstchannel LIKE 'PJSIP/%' AND lastapp = 'Queue' THEN billsec 
+                        ELSE 0 
+                    END
+                ) AS total_billsec
+            FROM tab_cdr
+            WHERE uniqueid IN {in_clause}
+            GROUP BY uniqueid, src, dst;
+        """
 
-                if rowMongo := next((m for m in self.mongo_results if m["_id"] == row["uniqueid"] ), None): 
-                    row["custo_hit"] = f"{float(rowMongo["totalCost"])}" 
+        rows = execute_query(self.company_id, sql)
 
-                    token_output = rowMongo.get('usageByType', {}).get('output', {})
-                    token_input = rowMongo.get('usageByType', {}).get('input',{})
-                    
-                    row["qtd_token_input"] = token_input.get('usage', 0)
-                    row["qtd_token_output"] = token_output.get('usage', 0)                    
-                    row["total_cost_token"] = float(token_input.get('usageCost',0) + token_output.get('usageCost', 0)) 
-                    row["llm_provider"] = token_output.get('provider','unknown')    
-                    
-                    stt = rowMongo.get('usageByType', {}).get('stt',{})
-                    
-                    if not stt:                        
-                        stt = rowMongo.get('usageByType', {}).get('input-audio',{})
+        if hit_report:
+            for row in rows:
+                row["companyId"] = self.company_id
+
+                rowMongo = next((m for m in self.mongo_results if m["_id"] == row["uniqueid"]), None)
+                if rowMongo:
+                    row["custo_hit"] = str(float(rowMongo["totalCost"]))
+
+                    usage_by_type = rowMongo.get("usageByType", {})
+                    token_output = usage_by_type.get("output", {})
+                    token_input  = usage_by_type.get("input",  {})
+
+                    row["qtd_token_input"]  = token_input.get("usage", 0)
+                    row["qtd_token_output"] = token_output.get("usage", 0)
+                    row["total_cost_token"] = float(token_input.get("usageCost", 0) + token_output.get("usageCost", 0))
+                    row["llm_provider"]     = token_output.get("provider", "unknown")
+
+                    stt = usage_by_type.get("stt", {})
+                    if not stt:
                         
-                        # seconds 
-                        token_by_second = 1920 / 60 # Cobrança de token por minuto do modelo gemini flash 2.5 input de audio
-                        usageSeconds = round(stt.get('usage', 0) / token_by_second)
-                                              
-                        row["stt_model"] = stt.get('product', 'unknown')
-                        row["stt_provider"] = stt.get('provider', 'unknown')  
-                        row["stt_cost"] = rowMongo["totalCost"]
-                        row["stt_usage"] = usageSeconds
+                        # AJUSTE PROVISORIO. REFATORAR PARA NÃO USAR CODIGO STATICO COM NOME DO MODELO 
+                        input_text = rowMongo.get('usageByType', {}).get('input-text-gemini-2.5-flash', {})
+                        input_audio = rowMongo.get('usageByType', {}).get('input-audio-gemini-2.5-flash', {})
+                        output = rowMongo.get('usageByType', {}).get('output-text-gemini-2.5-flash', {})
+
+                        input_text_usage_cost = input_text.get("usageCost", 0)
+                        input_audio_usage_cost = input_audio.get("usageCost", 0)
+                        output_usage_cost = output.get("usageCost", 0)
+
+                        token_by_second = 1920 / 60                    
+                        usageSeconds = round(input_audio.get('usage', 0) / token_by_second)     
+
+                        total_cost_stt = (input_text_usage_cost + input_audio_usage_cost + output_usage_cost)
+                                                
+                        model_name = input_audio.get("product", "unknown")
                         
-                    else:    
-                        row["stt_model"] = stt.get('product', 'unknown')
-                        row["stt_provider"] = stt.get('provider', 'unknown')
-                        row["stt_cost"] = stt.get('usageCost', 0)
-                        row["stt_usage"] = stt.get('usage', 0)
+                        if "-input-audio" in model_name:
+                            # Ajuste para situações como gemini-2.5-flash-input-audio para modificar para gemini-2.5-flash
+                            model_name = model_name.replace("-input-audio", "")
+
+                        row["stt_model"]  = model_name                        
+                        row["stt_provider"] = input_audio.get("provider", "unknown")
+                        row["stt_cost"]   = total_cost_stt
+                        row["stt_usage"]  = usageSeconds  
+                        
+                    else:
+                        row["stt_model"]    = stt.get("product", "unknown")
+                        row["stt_provider"] = stt.get("provider", "unknown")
+                        row["stt_cost"]     = stt.get("usageCost", 0)
+                        row["stt_usage"]    = stt.get("usage", 0)
 
                     row["total_min"] = f"{(int(row['total_billsec']) / 60):.2f}"
-                
-                self.client_price_row(products, row)                    
-                self.formate_properties(row) 
-                 
+
+                self.client_price_row(products, row)
+                self.formate_properties(row)
         else:
             for row in rows:
                 row["total_min"] = f"{(int(row['total_billsec']) / 60):.2f}"
-
                 self.client_price_row(products, row)
-                self.formate_properties(row) 
-        
-        
+                self.formate_properties(row)
 
         return rows
 
+   
+
     def formate_properties(self, row):
         for key in row:
             if isinstance(row[key], datetime):