Updated the SQL query generation and made the Answers of the chatbot a bit more Robust

app.py

@@ -46,37 +46,56 @@ def generate_sql_from_nl(prompt):
     client = OpenAI(api_key=os.getenv("OPENAI_API_KEY"))
 
     table_struct = """
-        id INTEGER PRIMARY KEY AUTOINCREMENT,
-        date TEXT,
-        subject TEXT,
-        chapter_name TEXT,
-        task_type TEXT,
-        time TEXT,
-        subtopic TEXT
+        CREATE TABLE IF NOT EXISTS roadmap (
+            id INTEGER PRIMARY KEY AUTOINCREMENT,
+            day_num INTEGER,
+            date TEXT,
+            subject TEXT,
+            chapter_name TEXT,
+            task_type TEXT,
+            time TEXT,
+            subtopic TEXT
+        )
     """
 
     response = client.chat.completions.create(
         model="gpt-4o-mini",
         messages=[
-            {"role": "system", "content": "You are an expert at converting natural language to SQL."},
-            {"role": "user", "content": f"""Convert this to an SQL query for the 'roadmap' table: {prompt}
-            Keep the table structure in mind: {table_struct}, 
-            Also dont include new line statements in the output of the query and just give me the query in the output.
-            Do not include ```sql and any other text."""}
+            {"role": "system", "content": f""""You are an helper who runs in the background of an AI agent, 
+             which helps students for their JEE Preparation. Now your Job is to analyze the users prompt and 
+             create an SQL query to extract the related Information from an sqlite3 database with the table 
+             structure: {table_struct}. 
+
+             Note: For the time column, the data is formatted like '0.5 hour', '1 hour', '2 hours' and 
+             so on. So make sure create queries that compare just the numbers within the text.
+
+             You will also make sure multiple times that you give an SQL
+             Query that adheres to the given table structure, and you Output just the SQL query.
+             Do not include anyting else like new line statements, ```sql or any other text. Your output
+             is going to be directly fed into a Python script to extract the required information. So,
+             please follow all the given Instructions."""},
+            {"role": "user", "content": f"""Keeping the table structure in mind: {table_struct}, 
+             Convert this prompt to an SQL query for the given table: {prompt}. Make sure your
+             output is just the SQL query, which can directly be used to extract required content"""}
         ]
     )
     return response.choices[0].message.content.strip()
 
 # Function to convert SQL output to natural language
-def generate_nl_from_sql_output(prompt):
+def generate_nl_from_sql_output(prompt, data):
     client = OpenAI(api_key=os.getenv("OPENAI_API_KEY"))
 
     response = client.chat.completions.create(
         model="gpt-4o-mini",
         messages=[
-            {"role": "system", "content": """You are a helpful chatbot for JEE roadmap analysis.
-            You will be given SQL-fetched roadmap data and must give a natural and interactive explanation to the user."""},
-            {"role": "user", "content": f"""Convert this data into interactive output: {prompt}"""}
+            {"role": "system", "content": f"""You are an helpful AI chatbot working under the roadmap 
+             section of an AI Agent, whose role is to aid students in their preparation for the JEE examination. 
+             You are going to play a very crucial role of a Roadmap Assistant, who helps the student out with whatever query
+             they have related to their roadmap, the data required to answer the users query is already extracted 
+             from the Roadmap table of a SQLite3 database and given to you here {data}. Analyse the users query deeply and 
+             reply to it with the relevant information from the given data in a supportive manner."""},
+            {"role": "user", "content": f"""Answer to this users query using the data given to you, while keeping 
+             your role in mind: {prompt}"""}
         ]
     )
     return response.choices[0].message.content.strip()
@@ -94,8 +113,10 @@ def fetch_data_from_sql(sql_query):
 def answer_user_query(prompt):
     initialize_roadmap_db()
     sql = generate_sql_from_nl(prompt)
+    st.write(sql)
     rows = fetch_data_from_sql(sql)
-    return generate_nl_from_sql_output(rows)
+    st.write(rows)
+    return generate_nl_from_sql_output(prompt, rows)
     
 def initialize_roadmap_db():
     if not os.path.exists("jee_roadmap.db"):
@@ -109,6 +130,7 @@ def initialize_roadmap_db():
             cursor.execute("""
             CREATE TABLE IF NOT EXISTS roadmap (
                 id INTEGER PRIMARY KEY AUTOINCREMENT,
+                day_num INTEGER,
                 date TEXT,
                 subject TEXT,
                 chapter_name TEXT,
@@ -120,13 +142,15 @@ def initialize_roadmap_db():
 
             for day in roadmap_data["schedule"]:
                 date = day["date"]
+                day_num = day["dayNumber"]
                 for subj in day["subjects"]:
                     subject = subj["name"]
                     for task in subj["tasks"]:
                         cursor.execute("""
-                            INSERT INTO roadmap (date, subject, chapter_name, task_type, time, subtopic)
-                            VALUES (?, ?, ?, ?, ?, ?)
+                            INSERT INTO roadmap (day_num, date, subject, chapter_name, task_type, time, subtopic)
+                            VALUES (?, ?, ?, ?, ?, ?, ?)
                         """, (
+                            day_num,
                             date,
                             subject,
                             task["ChapterName"],