import os
import sqlite3
import google.generativeai as genai
import gradio as gr
from dotenv import load_dotenv
load_dotenv()

# Configure the API Key
genai.configure(api_key = "AIzaSyChjOLER-nWxh6tcB7vG3hW43o21VPGuu0")

# Load Google Gemini model and retireve the sql query as response
def get_gemini_response(question, prompt):
    model = genai.GenerativeModel('gemini-2.5-flash-preview-04-17')
    response = model.generate_content([prompt, question])
    return response.text

# Retrieve query from SQL database
def read_sql_query(sql, db):
    connection = sqlite3.connect(db)    # Connect to the SQLite database
    cursor = connection.cursor()    # Create a cursor object to execute SQL commands like SELECT, INSERT, UPDATE, DELETE
    cursor.execute(sql) # Execute the SQL command to create the table
    rows = cursor.fetchall()    # Fetch all rows from the executed query
    connection.close()  # Close the connection to the database
    for row in rows:
        print(row)  # Print each row
    return rows

# Prompt for the Gemini model
prompt = """
You are an expert in converting English questions to SQL query!
The SQL database has the name STUDENT and has the following columns - NAME, CLASS, 
SECTION

For example,
Example 1 - How many entries of records are present?, 
the SQL command will be something like this SELECT COUNT(*) FROM STUDENT ;

Example 2 - Tell me all the students studying in Data Science class?, 
the SQL command will be something like this SELECT * FROM STUDENT 
where CLASS="Data Science"; 
also the sql code should not have ``` in beginning or end and sql word in output
"""

def process_question(question):
    response = get_gemini_response(question, prompt)
    data = read_sql_query(response, "student.db")
    result = f"Generated SQL Query:\n{response}\n\nQuery Result:\n"
    for row in data:
        result += f"{str(row)}\n"
    return result

# Create Gradio interface
demo = gr.Interface(
    fn=process_question,
    inputs=gr.Textbox(label="Enter your question"),
    outputs=gr.Textbox(label="Result"),
    title="Azaan's SQL Query Generator",
    description="This app generates SQL queries based on user input questions."
)

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