247 lines
7.1 KiB
JavaScript
247 lines
7.1 KiB
JavaScript
const Database = require('better-sqlite3');
|
|
const path = require('path');
|
|
const fs = require('fs');
|
|
|
|
const dbPath = process.env.DB_PATH || path.join(__dirname, 'data', 'jackbox.db');
|
|
const dbDir = path.dirname(dbPath);
|
|
|
|
// Ensure data directory exists with proper permissions
|
|
try {
|
|
if (!fs.existsSync(dbDir)) {
|
|
fs.mkdirSync(dbDir, { recursive: true, mode: 0o777 });
|
|
}
|
|
// Also ensure the directory is writable
|
|
fs.accessSync(dbDir, fs.constants.W_OK);
|
|
} catch (err) {
|
|
console.error(`Error with database directory ${dbDir}:`, err.message);
|
|
console.error('Please ensure the directory exists and is writable');
|
|
process.exit(1);
|
|
}
|
|
|
|
const db = new Database(dbPath);
|
|
|
|
// Enable foreign keys
|
|
db.pragma('foreign_keys = ON');
|
|
|
|
// Create tables
|
|
function initializeDatabase() {
|
|
// Games table
|
|
db.exec(`
|
|
CREATE TABLE IF NOT EXISTS games (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
pack_name TEXT NOT NULL,
|
|
title TEXT NOT NULL,
|
|
min_players INTEGER NOT NULL,
|
|
max_players INTEGER NOT NULL,
|
|
length_minutes INTEGER,
|
|
has_audience INTEGER DEFAULT 0,
|
|
family_friendly INTEGER DEFAULT 0,
|
|
game_type TEXT,
|
|
secondary_type TEXT,
|
|
play_count INTEGER DEFAULT 0,
|
|
popularity_score INTEGER DEFAULT 0,
|
|
enabled INTEGER DEFAULT 1,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
|
|
)
|
|
`);
|
|
|
|
// Sessions table
|
|
db.exec(`
|
|
CREATE TABLE IF NOT EXISTS sessions (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
closed_at DATETIME,
|
|
is_active INTEGER DEFAULT 1,
|
|
notes TEXT
|
|
)
|
|
`);
|
|
|
|
// Session games table
|
|
db.exec(`
|
|
CREATE TABLE IF NOT EXISTS session_games (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
session_id INTEGER NOT NULL,
|
|
game_id INTEGER NOT NULL,
|
|
played_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
manually_added INTEGER DEFAULT 0,
|
|
status TEXT DEFAULT 'played',
|
|
FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (game_id) REFERENCES games(id) ON DELETE CASCADE
|
|
)
|
|
`);
|
|
|
|
// Add status column if it doesn't exist (for existing databases)
|
|
try {
|
|
db.exec(`ALTER TABLE session_games ADD COLUMN status TEXT DEFAULT 'played'`);
|
|
} catch (err) {
|
|
// Column already exists, ignore error
|
|
}
|
|
|
|
// Add room_code column if it doesn't exist (for existing databases)
|
|
try {
|
|
db.exec(`ALTER TABLE session_games ADD COLUMN room_code TEXT`);
|
|
} catch (err) {
|
|
// Column already exists, ignore error
|
|
}
|
|
|
|
// Add player_count column if it doesn't exist (for existing databases)
|
|
try {
|
|
db.exec(`ALTER TABLE session_games ADD COLUMN player_count INTEGER`);
|
|
} catch (err) {
|
|
// Column already exists, ignore error
|
|
}
|
|
|
|
// Add player_count_check_status column if it doesn't exist (for existing databases)
|
|
try {
|
|
db.exec(`ALTER TABLE session_games ADD COLUMN player_count_check_status TEXT DEFAULT 'not_started'`);
|
|
} catch (err) {
|
|
// Column already exists, ignore error
|
|
}
|
|
|
|
// Add favor_bias column to games if it doesn't exist
|
|
try {
|
|
db.exec(`ALTER TABLE games ADD COLUMN favor_bias INTEGER DEFAULT 0`);
|
|
} catch (err) {
|
|
// Column already exists, ignore error
|
|
}
|
|
|
|
// Add upvotes and downvotes columns to games if they don't exist
|
|
try {
|
|
db.exec(`ALTER TABLE games ADD COLUMN upvotes INTEGER DEFAULT 0`);
|
|
} catch (err) {
|
|
// Column already exists, ignore error
|
|
}
|
|
|
|
try {
|
|
db.exec(`ALTER TABLE games ADD COLUMN downvotes INTEGER DEFAULT 0`);
|
|
} catch (err) {
|
|
// Column already exists, ignore error
|
|
}
|
|
|
|
// Migrate existing popularity_score to upvotes/downvotes if needed
|
|
try {
|
|
const gamesWithScore = db.prepare(`
|
|
SELECT id, popularity_score FROM games
|
|
WHERE popularity_score != 0 AND (upvotes = 0 AND downvotes = 0)
|
|
`).all();
|
|
|
|
if (gamesWithScore.length > 0) {
|
|
const updateGame = db.prepare(`
|
|
UPDATE games
|
|
SET upvotes = ?, downvotes = ?
|
|
WHERE id = ?
|
|
`);
|
|
|
|
for (const game of gamesWithScore) {
|
|
if (game.popularity_score > 0) {
|
|
updateGame.run(game.popularity_score, 0, game.id);
|
|
} else {
|
|
updateGame.run(0, Math.abs(game.popularity_score), game.id);
|
|
}
|
|
}
|
|
console.log(`Migrated popularity scores for ${gamesWithScore.length} games`);
|
|
}
|
|
} catch (err) {
|
|
console.error('Error migrating popularity scores:', err);
|
|
}
|
|
|
|
// Packs table for pack-level favoriting
|
|
db.exec(`
|
|
CREATE TABLE IF NOT EXISTS packs (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT NOT NULL UNIQUE,
|
|
favor_bias INTEGER DEFAULT 0,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
|
|
)
|
|
`);
|
|
|
|
// Populate packs table with unique pack names from games
|
|
db.exec(`
|
|
INSERT OR IGNORE INTO packs (name)
|
|
SELECT DISTINCT pack_name FROM games
|
|
`);
|
|
|
|
// Chat logs table
|
|
db.exec(`
|
|
CREATE TABLE IF NOT EXISTS chat_logs (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
session_id INTEGER NOT NULL,
|
|
chatter_name TEXT NOT NULL,
|
|
message TEXT NOT NULL,
|
|
timestamp DATETIME NOT NULL,
|
|
parsed_vote TEXT,
|
|
FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE
|
|
)
|
|
`);
|
|
|
|
// Add message_hash column if it doesn't exist
|
|
try {
|
|
db.exec(`ALTER TABLE chat_logs ADD COLUMN message_hash TEXT`);
|
|
} catch (err) {
|
|
// Column already exists, ignore error
|
|
}
|
|
|
|
// Create index on message_hash for fast duplicate checking
|
|
try {
|
|
db.exec(`CREATE INDEX IF NOT EXISTS idx_chat_logs_hash ON chat_logs(message_hash)`);
|
|
} catch (err) {
|
|
// Index already exists, ignore error
|
|
}
|
|
|
|
// Live votes table for real-time voting
|
|
db.exec(`
|
|
CREATE TABLE IF NOT EXISTS live_votes (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
session_id INTEGER NOT NULL,
|
|
game_id INTEGER NOT NULL,
|
|
username TEXT NOT NULL,
|
|
vote_type INTEGER NOT NULL,
|
|
timestamp DATETIME NOT NULL,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (game_id) REFERENCES games(id) ON DELETE CASCADE
|
|
)
|
|
`);
|
|
|
|
// Create index for duplicate checking (username + timestamp within 1 second)
|
|
try {
|
|
db.exec(`CREATE INDEX IF NOT EXISTS idx_live_votes_dedup ON live_votes(username, timestamp)`);
|
|
} catch (err) {
|
|
// Index already exists, ignore error
|
|
}
|
|
|
|
// Webhooks table for external integrations
|
|
db.exec(`
|
|
CREATE TABLE IF NOT EXISTS webhooks (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT NOT NULL,
|
|
url TEXT NOT NULL,
|
|
secret TEXT NOT NULL,
|
|
events TEXT NOT NULL,
|
|
enabled INTEGER DEFAULT 1,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
|
|
)
|
|
`);
|
|
|
|
// Webhook logs table for debugging
|
|
db.exec(`
|
|
CREATE TABLE IF NOT EXISTS webhook_logs (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
webhook_id INTEGER NOT NULL,
|
|
event_type TEXT NOT NULL,
|
|
payload TEXT NOT NULL,
|
|
response_status INTEGER,
|
|
error_message TEXT,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (webhook_id) REFERENCES webhooks(id) ON DELETE CASCADE
|
|
)
|
|
`);
|
|
|
|
console.log('Database initialized successfully');
|
|
}
|
|
|
|
initializeDatabase();
|
|
|
|
module.exports = db;
|
|
|