75 lines
2.6 KiB
JavaScript
75 lines
2.6 KiB
JavaScript
import Database from 'better-sqlite3';
|
|
import { mkdirSync } from 'node:fs';
|
|
import { dirname, join } from 'node:path';
|
|
import { fileURLToPath } from 'node:url';
|
|
|
|
const __dirname = dirname(fileURLToPath(import.meta.url));
|
|
const DB_PATH = join(__dirname, 'newbury.sqlite');
|
|
|
|
mkdirSync(__dirname, { recursive: true });
|
|
|
|
const db = new Database(DB_PATH);
|
|
db.pragma('journal_mode = WAL');
|
|
db.pragma('foreign_keys = ON');
|
|
|
|
db.exec(`
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
username TEXT UNIQUE NOT NULL,
|
|
password_hash TEXT NOT NULL,
|
|
role TEXT NOT NULL DEFAULT 'admin',
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now'))
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS abilities (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT UNIQUE NOT NULL,
|
|
kind TEXT NOT NULL DEFAULT 'common', -- common | boss
|
|
charges INTEGER,
|
|
cooldown TEXT,
|
|
effect TEXT
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS ghosts (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT NOT NULL,
|
|
display_name TEXT, -- shown on the lock-on label; falls back to name
|
|
type TEXT NOT NULL, -- red | yellow | blue
|
|
rarity INTEGER NOT NULL, -- 1..4 (stars)
|
|
speed INTEGER NOT NULL DEFAULT 0,
|
|
range INTEGER NOT NULL DEFAULT 0,
|
|
charge_shot INTEGER NOT NULL DEFAULT 0,
|
|
health INTEGER NOT NULL,
|
|
damage INTEGER NOT NULL,
|
|
ability TEXT,
|
|
is_boss INTEGER NOT NULL DEFAULT 0,
|
|
set_number TEXT,
|
|
set_name TEXT,
|
|
image_path TEXT, -- uploaded GIF/PNG billboard (nullable)
|
|
enabled INTEGER NOT NULL DEFAULT 1,
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now'))
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_ghosts_type ON ghosts(type);
|
|
CREATE INDEX IF NOT EXISTS idx_ghosts_rarity ON ghosts(rarity);
|
|
CREATE INDEX IF NOT EXISTS idx_ghosts_boss ON ghosts(is_boss);
|
|
|
|
CREATE TABLE IF NOT EXISTS sets (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
code TEXT UNIQUE NOT NULL, -- QR payload / scan code
|
|
set_number TEXT, -- e.g. 70419 (reference only)
|
|
set_name TEXT NOT NULL,
|
|
boss_ghost_id INTEGER REFERENCES ghosts(id) ON DELETE SET NULL,
|
|
enabled INTEGER NOT NULL DEFAULT 1,
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now'))
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS set_ghosts (
|
|
set_id INTEGER NOT NULL REFERENCES sets(id) ON DELETE CASCADE,
|
|
ghost_id INTEGER NOT NULL REFERENCES ghosts(id) ON DELETE CASCADE,
|
|
PRIMARY KEY (set_id, ghost_id)
|
|
);
|
|
`);
|
|
|
|
export default db;
|
|
export { DB_PATH };
|