db_setup.py

import os
from dotenv import load_dotenv
import sqlite3

# Load environment variables
load_dotenv()

# Get database path from environment variable
db_path = os.getenv("DB_PATH", "./query_logs.db")  # Default to './query_logs.db' if not set

# Function to initialize SQLite database
def initialize_local_db():
    conn = sqlite3.connect(db_path)  # Use the DB path from .env
    cursor = conn.cursor()

    # Create table if it doesn't exist
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS query_logs (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        natural_language_query TEXT,
        generated_sql TEXT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    );
    ''')

    conn.commit()
    cursor.close()
    conn.close()

# Initialize the database on startup
initialize_local_db()