import sqlite3
import re
import openai

def gpt3(texts):
    openai.api_key ="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"

    response = openai.Completion.create(
      engine="code-davinci-002",
      prompt= texts,
          temperature=0,
          max_tokens=100,
          top_p=1,
          frequency_penalty=0.0,
          presence_penalty=0.0,
          suffix='")',
          stop = ("conn.close()", "</code>", "#")
    )
    x = response.choices[0].text 
    #extract the text inside of the cur.execute() function. for example 'cur.execute("SELECT * FROM gptsum")' would turn into 'SELECT * FROM gptsum'
    x = re.sub(r'(cur.execute\(\"|\"\))', '', x)
    # cut everything off after the first "\n"
    x = x.split("\n")[0]
    return x



#using the name of the database, return the table names and the column names

def print_attributes(database, table):
    conn = sqlite3.connect(database)
    c = conn.cursor()
    c.execute("PRAGMA table_info(" + table + ")")
    print(c.fetchall())
    conn.close()

# if tabes returns ([], []), re do the function and the base input
def get_tables():
    # make the base variable global
    global base
    base = input("Name the database to connect to:")
    if len(base) <5:
        print("The name is too short")
        get_tables()
    #exit clause
    if base == "exit":
        return
    conn = sqlite3.connect(base)
    c = conn.cursor()
    c.execute("SELECT name FROM sqlite_master WHERE type='table';")
    tables = c.fetchall()


    conn.close()
    if tables == [] and columns == []:
        get_tables()
    return tables




#tables = get_tables()

#if len(base)==0:
#    base= "gmaildb.sqlite"

# turn this: "request=input("What would you like to do to the database?")" into a callable function that repeats itself if too short

def request():
    requests=input("What would you like to do to the database?")
    if len(requests) < 5:
        print("The request is too short")
        request()
    return requests

import gradio as gr


# create a UI using gradio that takes in a text name and a text prompt, which then is displayed back to the user

def greet( prompt):
    txt= ("# Python 3 \n# SQLite \n# "+prompt+ "\nimport sqlite3"+'\nconn = sqlite3.connect("database")\ncur= conn.cursor()\ncur.execute("')
    sql = gpt3(txt)
    return sql

    #the scrip variable is a string of python code with a sql query in it. execute the code keep the result in a variable




iface = gr.Interface(greet, inputs = ["text"], outputs = "text")
iface.launch(share=True  )


#breakpoint()
#req = request()

#txt= ("# Python 3 \n# SQLite \n# "+req+ "\nimport sqlite3"+'\nconn = sqlite3.connect("'+ base +'")\ncur = conn.cursor()')
#cod = gpt3(txt)


#scrip= (txt+str(cod))
#print(scrip)
#exec(scrip)