import gradio as gr
import openai
import sqlite3
import pandas as pd

def gpt4():
    openai.api_type = "azure"  
    openai.api_key = "ebbe2c995cb8911"
    openai.api_base = "https://sanofit2dvcopenaigpt4.openai.azure.com/"  
    openai.api_version = "2023-07-01-preview"
    return "SanofiT2DGPT4Model"

convlogs=[{"role":"system","content":"Assistant is an AI chatbot that helps users turn a natural language query into MySQL query. After users input a query, they want to generate a MySQL Query, if the user has not provided any, then ask the user to ask their query.\n\n\"Given the following schema for a \"food_supply_chain\" dataset:\n\nSupplier: The company that provides the specific food item.\nItem_Name: The specific name or type of the food product.\nMarket: The geographical region or country where the product is supplied.\nProduct_Category: The broad category to which the food item belongs, such as Bread/Bakery, Meat, Poultry, or Seafood.\nFacility: The location or organization where the food items are being delivered or distributed.\n\nPlease generate SQL queries for this given schema.\n\nNote: Do not provide explanation of the generated code.\nNote: Provide only valid MySQL query.\nNote: do not change the entity name provided in the query."}]
responsegen_convlogs=[{"role":"system","content":"You are a Procurement specialist in Supply chain and McDonald employee. You will be asked a question and data delimited by $$$ to answer the question.  your task is the analyze the question and generate the answer from the data. "}]
nosql_convlogs=[{"role":"system","content":"You are an AI assistant who works as a procurement specialist in supply chain and McDonald employee. I am responsible for sourcing the ingredients and supply chain risk management.\n\nYou will be asked a query, your task is to reply as truthfully as possible."}]
with gr.Blocks() as demo:
    chatbot = gr.Chatbot()
    msg = gr.Textbox()
    clear = gr.ClearButton([msg, chatbot])
    def respond(message, chat_history):
        convlogs.append({"role":"user","content":message})
        nosql_convlogs.append({"role":"user","content":message})
        openai_response = openai.ChatCompletion.create(
            engine=gpt4(),
            messages = convlogs,
            temperature=0.3,
            max_tokens=800,
            top_p=0.95,
            frequency_penalty=0,
            presence_penalty=0,
            stop=None)
        reply = openai_response["choices"][0]["message"]["content"]
        if(not(reply.lower().startswith('select'))):
            #bot_message = reply
            openai_response = openai.ChatCompletion.create(
            engine=gpt4(),
            messages = nosql_convlogs,
            temperature=0.3,
            max_tokens=800,
            top_p=0.95,
            frequency_penalty=0,
            presence_penalty=0,
            stop=None)
            bot_message = openai_response["choices"][0]["message"]["content"]
            reply=bot_message
        else:
            conn = sqlite3.connect('Database.db')
            df = pd.read_sql_query(reply.replace("\n",""),conn )
            conn.close()
            
            responsegen_convlogs.append({"role":"user","content":"query: {} $$${}$$$".format(message,str(df))})
            
            openai_final_response = openai.ChatCompletion.create(
            engine=gpt4(),
            messages = responsegen_convlogs,
            temperature=0.3,
            max_tokens=800,
            top_p=0.95,
            frequency_penalty=0,
            presence_penalty=0,
            stop=None)
            final_reply = openai_final_response["choices"][0]["message"]["content"]
            
            bot_message= final_reply
        chat_history.append((message, bot_message))
        convlogs.append({"role":"assistant","content":reply})
        nosql_convlogs.append({"role":"assistant","content":bot_message})
        responsegen_convlogs.append({"role":"assistant","content":bot_message})
        return "", chat_history
    msg.submit(respond, [msg, chatbot], [msg, chatbot])
demo.launch(inline=False,width="70%") # debug=True