from dotenv import load_dotenv

load_dotenv()

import streamlit as st

import os
import sqlite3
import google.generativeai as genai

genai.configure(api_key = os.getenv("GOOGLE_API_KEY"))

def get_gemini_reponse(question,prompt):
    model = genai.GenerativeModel("gemini-pro")
    response = model.generate_content([prompt[0],question])
    return response.text

## functio to retreive query

def read_sql_query(sql,db):
    conn = sqlite3.connect(db)
    cur = conn.cursor()
    cur.execute(sql)
    rows = cur.fetchall()
    for row in rows:
        print(row)
    return rows

## dedfine prompt

prompt = [
"""
you are expert in converting english questions to SQL query!
The SQL database has the name STUDENT and has the following columns - NAME,CLASS
,SECTION \n\n For example , \nExample 1 - how many entries of records are present ?,
the sql command will be something like this SELECT COUNT(*)from student;
\nExample 2 - tell me all the name of  students study in data sciense class?,
the sql command will be something like this SELECT * from student where class="data science"  from student;
also the sql code should not have ```in begining or end and sql word in output

"""
]


st.set_page_config(page_title = "I can retrieve any sql query")
st.header(" gemini app to retrieve sql data")

question = st.text_input("input: ",key = "input")

submit= st.button("ask the question")

if submit :
    response = get_gemini_reponse(question,prompt)
    print(response)
    response = read_sql_query(response,"test.db")
    st.subheader(" the response is")
    for row in response:
        print(row)
        st.header(row)