<?php
$dbFile = 'chat_history.sqlite';
$db = new SQLite3($dbFile);

// Enable foreign key support
$db->exec('PRAGMA foreign_keys = ON;');

// Create users table
$db->exec('
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT UNIQUE NOT NULL,
    password TEXT NOT NULL,
    profile_id INTEGER,
    FOREIGN KEY (profile_id) REFERENCES profiles(id)
);
');

// Create profiles table
$db->exec('
CREATE TABLE IF NOT EXISTS profiles (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT UNIQUE NOT NULL,
    region TEXT NOT NULL,
    max_tokens INTEGER NOT NULL,
    stop_sequences TEXT NOT NULL,
    temperature REAL NOT NULL,
    top_p REAL NOT NULL,
    knowledge_base_id TEXT NOT NULL,
    model_arn TEXT NOT NULL,
    number_of_results INTEGER NOT NULL,
    override_search_type TEXT NOT NULL,
    instructions TEXT NOT NULL
);
');

// Create prompt_templates table
$db->exec('
CREATE TABLE IF NOT EXISTS prompt_templates (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    profile_id INTEGER NOT NULL,
    template TEXT NOT NULL,
    FOREIGN KEY (profile_id) REFERENCES profiles(id)
);
');

// Create messages table (if it doesn't exist)
$db->exec('
CREATE TABLE IF NOT EXISTS messages (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    session_id TEXT NOT NULL,
    username TEXT NOT NULL,
    sender TEXT NOT NULL,
    message TEXT NOT NULL,
    timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
);
');

// Create sessions table
$db->exec('
CREATE TABLE IF NOT EXISTS sessions (
    id TEXT PRIMARY KEY,
    username TEXT NOT NULL,
    start_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    last_use_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (username) REFERENCES users(username)
);
');

echo "Database schema updated successfully.";