39 lines
1.2 KiB
JavaScript
39 lines
1.2 KiB
JavaScript
const path = require('path');
|
|
const fs = require('fs');
|
|
const Database = require('better-sqlite3');
|
|
|
|
const dataDir = process.env.DATA_DIR || path.join(__dirname, 'data');
|
|
if (!fs.existsSync(dataDir)) {
|
|
fs.mkdirSync(dataDir, { recursive: true });
|
|
}
|
|
|
|
const db = new Database(path.join(dataDir, 'sudoku.db'));
|
|
db.pragma('journal_mode = WAL');
|
|
db.pragma('foreign_keys = ON');
|
|
|
|
db.exec(`
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
pseudo TEXT NOT NULL UNIQUE,
|
|
email TEXT NOT NULL UNIQUE,
|
|
password_hash TEXT NOT NULL,
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now'))
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS games (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
difficulty TEXT NOT NULL,
|
|
time_seconds INTEGER NOT NULL,
|
|
mistakes INTEGER NOT NULL DEFAULT 0,
|
|
won INTEGER NOT NULL DEFAULT 0,
|
|
points INTEGER NOT NULL DEFAULT 0,
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now'))
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_games_user ON games(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_games_diff ON games(difficulty, won, time_seconds);
|
|
`);
|
|
|
|
module.exports = db;
|