120 lines
3.6 KiB
JavaScript
120 lines
3.6 KiB
JavaScript
import Database from 'better-sqlite3';
|
|
import path from 'path';
|
|
import { fileURLToPath } from 'url';
|
|
import bcrypt from 'bcrypt';
|
|
|
|
const __dirname = path.dirname(fileURLToPath(import.meta.url));
|
|
const db = new Database(path.join(__dirname, 'sandbox.db'));
|
|
|
|
db.pragma('journal_mode = WAL');
|
|
db.pragma('foreign_keys = ON');
|
|
|
|
db.exec(`
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id TEXT PRIMARY KEY,
|
|
username TEXT UNIQUE NOT NULL,
|
|
email TEXT UNIQUE,
|
|
password_hash TEXT,
|
|
discord_id TEXT UNIQUE,
|
|
global_name TEXT,
|
|
avatar TEXT,
|
|
created_at TEXT DEFAULT (datetime('now')),
|
|
updated_at TEXT DEFAULT (datetime('now'))
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS characters (
|
|
id TEXT PRIMARY KEY,
|
|
user_id TEXT NOT NULL,
|
|
name TEXT NOT NULL,
|
|
description TEXT DEFAULT '',
|
|
personality_traits TEXT DEFAULT '[]',
|
|
backstory TEXT DEFAULT '',
|
|
avatar_url TEXT DEFAULT '',
|
|
created_at TEXT DEFAULT (datetime('now')),
|
|
updated_at TEXT DEFAULT (datetime('now')),
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS character_needs (
|
|
id TEXT PRIMARY KEY,
|
|
character_id TEXT NOT NULL,
|
|
name TEXT NOT NULL,
|
|
enabled INTEGER DEFAULT 1,
|
|
initial_value REAL DEFAULT 50,
|
|
min_value REAL DEFAULT 0,
|
|
max_value REAL DEFAULT 100,
|
|
decay_rate REAL DEFAULT 1,
|
|
priority INTEGER DEFAULT 0,
|
|
created_at TEXT DEFAULT (datetime('now')),
|
|
FOREIGN KEY (character_id) REFERENCES characters(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS character_ui_elements (
|
|
id TEXT PRIMARY KEY,
|
|
character_id TEXT NOT NULL,
|
|
need_id TEXT,
|
|
element_type TEXT NOT NULL,
|
|
config TEXT DEFAULT '{}',
|
|
created_at TEXT DEFAULT (datetime('now')),
|
|
FOREIGN KEY (character_id) REFERENCES characters(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (need_id) REFERENCES character_needs(id) ON DELETE SET NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS character_brain_rules (
|
|
id TEXT PRIMARY KEY,
|
|
character_id TEXT NOT NULL,
|
|
condition TEXT NOT NULL,
|
|
action TEXT NOT NULL,
|
|
priority INTEGER DEFAULT 0,
|
|
enabled INTEGER DEFAULT 1,
|
|
created_at TEXT DEFAULT (datetime('now')),
|
|
FOREIGN KEY (character_id) REFERENCES characters(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS lorebooks (
|
|
id TEXT PRIMARY KEY,
|
|
user_id TEXT NOT NULL,
|
|
name TEXT NOT NULL,
|
|
description TEXT DEFAULT '',
|
|
created_at TEXT DEFAULT (datetime('now')),
|
|
updated_at TEXT DEFAULT (datetime('now')),
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS fragments (
|
|
id TEXT PRIMARY KEY,
|
|
lorebook_id TEXT NOT NULL,
|
|
title TEXT NOT NULL,
|
|
content TEXT DEFAULT '',
|
|
tags TEXT DEFAULT '[]',
|
|
linked_characters TEXT DEFAULT '[]',
|
|
created_at TEXT DEFAULT (datetime('now')),
|
|
updated_at TEXT DEFAULT (datetime('now')),
|
|
FOREIGN KEY (lorebook_id) REFERENCES lorebooks(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS ai_training_data (
|
|
id TEXT PRIMARY KEY,
|
|
user_id TEXT NOT NULL,
|
|
prompt TEXT NOT NULL,
|
|
response TEXT NOT NULL,
|
|
category TEXT DEFAULT 'general',
|
|
created_at TEXT DEFAULT (datetime('now')),
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
|
|
);
|
|
`);
|
|
|
|
// Migrations: add columns that may not exist yet
|
|
const migrations = [
|
|
"ALTER TABLE users ADD COLUMN email TEXT",
|
|
"ALTER TABLE users ADD COLUMN discord_id TEXT UNIQUE",
|
|
"ALTER TABLE users ADD COLUMN global_name TEXT",
|
|
"ALTER TABLE users ADD COLUMN avatar TEXT",
|
|
"ALTER TABLE users ADD COLUMN updated_at TEXT DEFAULT (datetime('now'))",
|
|
];
|
|
for (const sql of migrations) {
|
|
try { db.exec(sql); } catch {}
|
|
}
|
|
|
|
export default db;
|