db_scripts/create_db.py

import os
import duckdb
from dotenv import load_dotenv

load_dotenv()

# Connect to (or create) a DuckDB database file
# conn = duckdb.connect('kpai_db.db')
conn = duckdb.connect(f"md:?motherduck_token={os.getenv('MOTHERDUCK_TOKEN')}")

# Create the USERS table
conn.execute('''
    CREATE TABLE IF NOT EXISTS USERS (
        user_id UUID PRIMARY KEY,
        username VARCHAR(100) NOT NULL,
        email VARCHAR(100) NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    );
''')

# Create the SESSIONS table
conn.execute('''
    CREATE TABLE IF NOT EXISTS SESSIONS (
        session_id UUID PRIMARY KEY,
        user_id UUID NOT NULL,
        session_start TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        session_end TIMESTAMP DEFAULT (CURRENT_TIMESTAMP + INTERVAL '2' HOUR),
        FOREIGN KEY(user_id) REFERENCES USERS(user_id)
    );
''')


# Create the RESPONSES table
conn.execute('''
    CREATE TABLE IF NOT EXISTS RESPONSES (
        response_id UUID PRIMARY KEY,
        session_id UUID NOT NULL,
        user_id UUID NOT NULL,
        user_input_text TEXT NOT NULL,
        response_text TEXT NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY(session_id) REFERENCES SESSIONS(session_id),
        FOREIGN KEY(user_id) REFERENCES USERS(user_id)
    );
''')

# Create the AGENT_RESPONSES table
conn.execute('''
    CREATE TABLE IF NOT EXISTS AGENT_RESPONSES (
        agent_response_id UUID PRIMARY KEY,
        response_id UUID NOT NULL,
        agent_name VARCHAR(50) NOT NULL,
        user_input TEXT NOT NULL,
        agent_response_text TEXT NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY(response_id) REFERENCES RESPONSES(response_id)
    );
''')

# Create the FEEDBACK table
conn.execute('''
    CREATE TABLE IF NOT EXISTS FEEDBACK (
        feedback_id UUID PRIMARY KEY,
        response_id UUID NOT NULL,
        user_id UUID NOT NULL,
        feedback_score TEXT,
        feedback_text TEXT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY(response_id) REFERENCES RESPONSES(response_id),
        FOREIGN KEY(user_id) REFERENCES USERS(user_id)
    );
''')

# Close the connection
conn.close()

print("Database and tables created successfully.")