import sqlite3

conn = sqlite3.connect("memory.db", check_same_thread=False)
cursor = conn.cursor()

cursor.execute("""
CREATE TABLE IF NOT EXISTS conversations (
    user_id TEXT,
    role TEXT,
    content TEXT
)
""")
conn.commit()


def save_message(user_id, role, content):
    cursor.execute(
        "INSERT INTO conversations VALUES (?, ?, ?)",
        (user_id, role, content)
    )
    conn.commit()


def load_memory(user_id, limit=6):
    cursor.execute("""
    SELECT role, content FROM conversations
    WHERE user_id=?
    ORDER BY rowid DESC
    LIMIT ?
    """, (user_id, limit))

    rows = cursor.fetchall()

    # Convert into LLM message format
    formatted = [
        {"role": role, "content": content}
        for role, content in reversed(rows)
    ]

    return formatted