# Install dependencies:
# pip install ollama
# Install Ollama locally and run: ollama pull llama3.2
#
# Creates a SQLite student database, reads records with SQL, and asks
# an Ollama model to reflect on the retrieved data.

import sqlite3
import ollama

students = [
    (1, "Asha", 82),
    (2, "Rahul", 68),
    (3, "Kiran", 91),
]


def main():
    conn = sqlite3.connect("student.db")
    cursor = conn.cursor()
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS students (
            id INTEGER PRIMARY KEY,
            name TEXT,
            marks INTEGER
        )
    """)
    cursor.execute("DELETE FROM students")
    cursor.executemany(
        "INSERT INTO students (id, name, marks) VALUES (?, ?, ?)",
        students,
    )
    conn.commit()

    cursor.execute("SELECT id, name, marks FROM students ORDER BY id")
    rows = cursor.fetchall()

    print("DATABASE INFORMATION")
    for row in rows:
        print(row)

    data = "\n".join(
        f"ID: {row[0]}, Name: {row[1]}, Marks: {row[2]}"
        for row in rows
    )
    prompt = f"""
You are a reflection agent. Review these records retrieved from SQLite:
{data}

Provide:
1. Observations
2. Strengths
3. Weaknesses
4. Suggestions for improvement
5. A short final reflection

Base your response only on the supplied records. Do not infer personal traits.
"""
    print("\nREFLECTION AGENT OUTPUT")
    try:
        response = ollama.chat(
            model="llama3.2",
            messages=[{"role": "user", "content": prompt}],
        )
        print(response["message"]["content"].strip())
    finally:
        conn.close()


if __name__ == "__main__":
    main()