scripts/db_setup.py

"""
Project Jarvis — Database Initialization Script.
Creates the SQLite database for basic memory and history storage.
"""

import sqlite3
from pathlib import Path

def setup_db():
    project_dir = Path(__file__).parent.parent
    data_dir = project_dir / "data"
    data_dir.mkdir(parents=True, exist_ok=True)
    db_path = data_dir / "jarvis.db"
    
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()
    
    # Create interactions table
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS interactions (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        session_id TEXT,
        timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
        role TEXT NOT NULL,
        content TEXT NOT NULL,
        intent TEXT
    )
    """)
    
    # Create memory key-value store
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS memory (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        key TEXT UNIQUE NOT NULL,
        value TEXT NOT NULL,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )
    """)
    
    # Create expenses table (for Phase 1 roadmap)
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS expenses (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        amount REAL NOT NULL,
        category TEXT NOT NULL,
        description TEXT,
        date DATETIME DEFAULT CURRENT_TIMESTAMP
    )
    """)
    
    conn.commit()
    conn.close()
    print(f"✓ SQLite database successfully initialized at {db_path}")

if __name__ == "__main__":
    print("Initializing Jarvis Database...")
    setup_db()