app.py

import os
import time
import pandas as pd
from sqlalchemy import create_engine
from langchain_openai import ChatOpenAI
from langchain_community.agent_toolkits import create_sql_agent
from langchain_community.utilities import SQLDatabase
from huggingface_hub import InferenceClient
import gradio as gr
from dotenv import load_dotenv
import logging

load_dotenv()

UPLOAD_DIR = "uploaded_data"
os.makedirs(UPLOAD_DIR, exist_ok=True)

CSV_FILE_PATH = "anomalia_vendas.csv"
SQL_DB_PATH = "data.db"
HUGGINGFACE_API_KEY = os.getenv("HUGGINGFACE_API_KEY")
OPENAI_API_KEY = os.getenv("OPENAI_API_KEY")

LLAMA_MODELS = {
    "LLaMA 70B": "meta-llama/Llama-3.3-70B-Instruct",
    "LlaMA 8B": "meta-llama/Llama-3.1-8B-Instruct",
    "Qwen 32B": "Qwen/QwQ-32B"
}

MAX_TOKENS_MAP = {
    "meta-llama/Llama-3.3-70B-Instruct": 900,
    "meta-llama/Llama-3.1-8B-Instruct": 600,
    "Qwen/QwQ-32B": 8192
}

hf_client = InferenceClient(
    provider="sambanova",
    api_key=HUGGINGFACE_API_KEY,
)

os.environ["OPENAI_API_KEY"] = OPENAI_API_KEY

query_cache = {}
history_log = []  
recent_history = []  
show_history_flag = False

logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s')

def create_or_load_sql_database(csv_path, sql_db_path):
    if os.path.exists(sql_db_path):
        print("Banco de dados SQL já existe. Carregando...")
        return create_engine(f"sqlite:///{sql_db_path}")
    else:
        print("Banco de dados SQL não encontrado. Criando...")
        engine = create_engine(f"sqlite:///{sql_db_path}")
        df = pd.read_csv(csv_path, sep=";", on_bad_lines="skip")
        print(f"CSV carregado: {len(df)} linhas, {len(df.columns)} colunas")
        df.to_sql("anomalia_vendas", engine, index=False, if_exists="replace")
        print("Banco de dados SQL criado com sucesso!")
        return engine

def load_uploaded_csv_and_create_db(uploaded_file):
    if uploaded_file is None:
        return None

    print(f"[UPLOAD] CSV recebido: {uploaded_file}")

    engine = create_engine(f"sqlite:///{SQL_DB_PATH}")
    df = pd.read_csv(uploaded_file, sep=";", on_bad_lines="skip")
    df.to_sql("anomalia_vendas", engine, index=False, if_exists="replace")
    print("Banco recriado com base no novo CSV.")
    print(f"CSV carregado: {len(df)} linhas, {len(df.columns)} colunas")
    print(f"[DEBUG] Novo engine criado: {engine}")
    return engine

engine = create_or_load_sql_database(CSV_FILE_PATH, SQL_DB_PATH)

def refresh_sql_agent():
    global db, sql_agent
    db = SQLDatabase(engine=engine)
    sql_agent = create_sql_agent(
        ChatOpenAI(model="gpt-4o-mini", temperature=0),
        db=db,
        agent_type="openai-tools",
        verbose=True,
        max_iterations=40,
        return_intermediate_steps=True
    )
    print("[SQL_AGENT] Atualizado com novo banco de dados.")

refresh_sql_agent()


def generate_initial_context(db_sample):
    return (
        f"Você é um assistente que gera queries SQL objetivas e eficientes. Sempre inclua LIMIT 15 nas queries. Aqui está o banco de dados:\n\n"
        f"Exemplos do banco de dados:\n{db_sample.head().to_string(index=False)}\n\n"
        "\n***IMPORTANTE***: Detecte automaticamente o idioma da pergunta do usuário e responda sempre no mesmo idioma.\n"
        "Essa base de dados representa o sellout de 2025, janeiro, fevereiro e março até dia 11, de uma farmácia.\n"
        "Cada linha representa a venda de um SKU em uma determinada data.\n"
        "\nRetorne apenas a pergunta e a query SQL mais eficiente para entregar ao agent SQL do LangChain para gerar uma resposta. O formato deve ser:\n"
        "\nPergunta: <pergunta do usuário>\n"
        "\nOpção de Query SQL:\n<query SQL>"
        "\nIdioma: <idioma>"
    )

def is_greeting(user_query):
    greetings = ["olá", "oi", "bom dia", "boa tarde", "boa noite", "oi, tudo bem?"]
    return user_query.lower().strip() in greetings

def query_with_llama(user_query, db_sample, selected_model_name):
    model_id = LLAMA_MODELS[selected_model_name]
    max_tokens = MAX_TOKENS_MAP.get(model_id, 512)
    
    initial_context = generate_initial_context(db_sample)
    formatted_history = "\n".join(
        [f"{msg['role'].capitalize()}: {msg['content']}" for msg in recent_history[-2:]]
    )
    
    full_prompt = f"{initial_context}\n\nHistórico recente:\n{formatted_history}\n\nPergunta do usuário:\n{user_query}"
    
    logging.info(f"[DEBUG] Contexto enviado ao ({selected_model_name}):\n{full_prompt}\n")
    
    start_time = time.time()
    
    try:
        response = hf_client.chat.completions.create(
            model=model_id,
            messages=[{"role": "system", "content": full_prompt}],
            max_tokens=max_tokens,
            stream=False
        )
        
        llama_response = response["choices"][0]["message"]["content"]
        end_time = time.time()
        logging.info(f"[DEBUG] Resposta do {selected_model_name} para o Agent SQL:\n{llama_response.strip()}\n[Tempo de execução: {end_time - start_time:.2f}s]\n")
        return llama_response.strip(), model_id
        
    except Exception as e:
        logging.error(f"[ERRO] Falha ao interagir com o modelo {selected_model_name}: {e}")
        return None, model_id

def query_sql_agent(user_query, selected_model_name):
    try:
        if user_query in query_cache:
            print(f"[CACHE] Retornando resposta do cache para a consulta: {user_query}")
            return query_cache[user_query]

        if is_greeting(user_query):
            greeting_response = "Olá! Estou aqui para ajudar com suas consultas. Pergunte algo relacionado aos dados carregados no agente!"
            query_cache[user_query] = greeting_response 
            return greeting_response

        column_data = pd.read_sql_query("SELECT * FROM anomalia_vendas LIMIT 10", engine)
        llama_instruction = query_with_llama(user_query, column_data, selected_model_name)
        
        if not llama_instruction:
            return "Erro: O modelo Llama não conseguiu gerar uma instrução válida."

        print("------- Agent SQL: Executando query -------")
        response = sql_agent.invoke({"input": llama_instruction})
        sql_response = response.get("output", "Erro ao obter a resposta do agente.")

        query_cache[user_query] = sql_response
        return sql_response
        
    except Exception as e:
        return f"Erro ao consultar o agente SQL: {e}"

def chatbot_response(user_input, selected_model_name):
    start_time = time.time()
    response = query_sql_agent(user_input, selected_model_name)
    end_time = time.time()

    model_id = LLAMA_MODELS[selected_model_name]

    history_log.append({
        "Modelo LLM": model_id,
        "Pergunta": user_input,
        "Resposta": response,
        "Tempo de Resposta (s)": round(end_time - start_time, 2)
    })

    recent_history.append({"role": "user", "content": user_input})
    recent_history.append({"role": "assistant", "content": response})

    if len(recent_history) > 4:
        recent_history.pop(0)
        recent_history.pop(0)

    return response

def toggle_history():
    global show_history_flag
    show_history_flag = not show_history_flag
    return history_log if show_history_flag else {}

with gr.Blocks(theme=gr.themes.Soft()) as demo:
    gr.Markdown("# 🧠 Anomalia Agent")

    with gr.Row():
        with gr.Column(scale=1):
            gr.Markdown("## ⚙️ Configurações")
            model_selector = gr.Dropdown(
                choices=list(LLAMA_MODELS.keys()),
                label="Escolha o Modelo LLM para gerar a query SQL",
                value="LLaMA 70B"
            )
            csv_file = gr.File(label="📂 Enviar novo CSV", file_types=[".csv"])

        with gr.Column(scale=4):
            chatbot = gr.Chatbot(height=600)
            msg = gr.Textbox(placeholder="Digite sua pergunta aqui...", label=" ", lines=1)
            btn = gr.Button("Enviar", variant="primary")
            history_btn = gr.Button("Histórico", variant="secondary")

            def respond(message, chat_history, selected_model_name):
                response = chatbot_response(message, selected_model_name)
                chat_history.append((message, response))
                return "", chat_history

            msg.submit(respond, [msg, chatbot, model_selector], [msg, chatbot])
            btn.click(respond, [msg, chatbot, model_selector], [msg, chatbot])

            history_output = gr.JSON()
            history_btn.click(toggle_history, inputs=[], outputs=history_output)

        def handle_csv_upload(file):
            global engine
            try:
                engine = load_uploaded_csv_and_create_db(file)
                if engine is not None:
                    refresh_sql_agent()
            except Exception as e:
                print(f"[ERRO] Falha ao processar novo CSV: {e}
    
        csv_file.change(handle_csv_upload, inputs=csv_file, outputs=csv_file)

if __name__ == "__main__":
    demo.launch(share=False)