utils/db.py

import sqlite3
import os
from datetime import datetime
from config.config import Config

def get_db_connection():
    conn = sqlite3.connect(Config.DB_PATH)
    conn.row_factory = sqlite3.Row
    return conn

def init_db():
    conn = get_db_connection()
    c = conn.cursor()
    c.execute('''CREATE TABLE IF NOT EXISTS logs (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_query TEXT NOT NULL,
        ai_response TEXT NOT NULL,
        timestamp TEXT NOT NULL
    )''')
    c.execute('''CREATE TABLE IF NOT EXISTS feedback (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        log_id INTEGER,
        rating TEXT,
        timestamp TEXT NOT NULL,
        FOREIGN KEY (log_id) REFERENCES logs(id)
    )''')
    conn.commit()
    conn.close()

def insert_log(user_query, ai_response):
    conn = get_db_connection()
    c = conn.cursor()
    timestamp = datetime.utcnow().isoformat()
    c.execute('INSERT INTO logs (user_query, ai_response, timestamp) VALUES (?, ?, ?)',
              (user_query, ai_response, timestamp))
    log_id = c.lastrowid
    conn.commit()
    conn.close()
    return log_id

def insert_feedback(log_id, rating):
    conn = get_db_connection()
    c = conn.cursor()
    timestamp = datetime.utcnow().isoformat()
    c.execute('INSERT INTO feedback (log_id, rating, timestamp) VALUES (?, ?, ?)',
              (log_id, rating, timestamp))
    conn.commit()
    conn.close()