import { openDatabase } from "./database.js"; const STATE_SCHEMA = ` CREATE TABLE IF NOT EXISTS accounts ( account_id INTEGER PRIMARY KEY AUTOINCREMENT, auth_subject TEXT NOT NULL UNIQUE, email TEXT, is_admin INTEGER NOT NULL DEFAULT 0 ); CREATE TABLE IF NOT EXISTS surname_claims ( surname_normalized TEXT PRIMARY KEY, account_id INTEGER NOT NULL ); CREATE TABLE IF NOT EXISTS characters ( character_id INTEGER PRIMARY KEY AUTOINCREMENT, account_id INTEGER NOT NULL, first_name TEXT NOT NULL, last_name TEXT NOT NULL, first_normalized TEXT NOT NULL, last_normalized TEXT NOT NULL, sex TEXT NOT NULL, tile_x INTEGER NOT NULL DEFAULT 8, tile_y INTEGER NOT NULL DEFAULT 8, gait TEXT NOT NULL DEFAULT 'walk', deletion_scheduled_at INTEGER ); `; export class StateDatabase { constructor (filePath) { this.db = openDatabase(filePath); this.db.exec(STATE_SCHEMA); } getAccountBySubject (authSubject) { return this.db.prepare("SELECT * FROM accounts WHERE auth_subject = ?").get(authSubject) ?? null; } createAccount (authSubject, isAdmin, email = null) { this.db.prepare("INSERT INTO accounts (auth_subject, email, is_admin) VALUES (?, ?, ?)").run( authSubject, email, isAdmin ? 1 : 0 ); return this.getAccountBySubject(authSubject); } ensureAccount (authSubject, isAdmin, email = null) { const existing = this.getAccountBySubject(authSubject); if (existing) { return existing; } return this.createAccount(authSubject, isAdmin, email); } listCharacters (accountId) { return this.db.prepare(` SELECT * FROM characters WHERE account_id = ? ORDER BY character_id ASC `).all(accountId); } getCharacter (characterId) { return this.db.prepare("SELECT * FROM characters WHERE character_id = ?").get(characterId) ?? null; } findSurnameClaim (surnameNormalized) { return this.db.prepare("SELECT * FROM surname_claims WHERE surname_normalized = ?").get(surnameNormalized) ?? null; } createCharacter (accountId, firstName, lastName, sex, tileX, tileY) { const firstNormalized = normalizeName(firstName); const lastNormalized = normalizeName(lastName); this.db.exec("BEGIN"); try { this.db.prepare(` INSERT INTO characters ( account_id, first_name, last_name, first_normalized, last_normalized, sex, tile_x, tile_y ) VALUES (?, ?, ?, ?, ?, ?, ?, ?) `).run(accountId, firstName, lastName, firstNormalized, lastNormalized, sex, tileX, tileY); const characterId = Number(this.db.prepare("SELECT last_insert_rowid() AS id").get().id); const claim = this.findSurnameClaim(lastNormalized); if (!claim) { this.db.prepare("INSERT INTO surname_claims (surname_normalized, account_id) VALUES (?, ?)").run( lastNormalized, accountId ); } this.db.exec("COMMIT"); return this.getCharacter(characterId); } catch (error) { this.db.exec("ROLLBACK"); throw error; } } scheduleDeletion (characterId, at) { this.db.prepare("UPDATE characters SET deletion_scheduled_at = ? WHERE character_id = ?").run(at, characterId); } cancelDeletion (characterId) { this.db.prepare("UPDATE characters SET deletion_scheduled_at = NULL WHERE character_id = ?").run(characterId); } deleteCharacter (characterId) { const character = this.getCharacter(characterId); if (!character) { return; } this.db.exec("BEGIN"); try { this.db.prepare("DELETE FROM characters WHERE character_id = ?").run(characterId); const remaining = this.db.prepare(` SELECT COUNT(*) AS count FROM characters WHERE account_id = ? AND last_normalized = ? `).get(character.account_id, character.last_normalized); if (!remaining.count) { this.db.prepare("DELETE FROM surname_claims WHERE surname_normalized = ? AND account_id = ?").run( character.last_normalized, character.account_id ); } this.db.exec("COMMIT"); } catch (error) { this.db.exec("ROLLBACK"); throw error; } } saveCharacterPosition (characterId, tileX, tileY, gait) { this.db.prepare("UPDATE characters SET tile_x = ?, tile_y = ?, gait = ? WHERE character_id = ?").run( tileX, tileY, gait, characterId ); } listDueDeletions (now) { return this.db.prepare(` SELECT * FROM characters WHERE deletion_scheduled_at IS NOT NULL AND deletion_scheduled_at <= ? `).all(now); } close () { this.db.close(); } } export function normalizeName (value) { return String(value ?? "").trim().toLocaleLowerCase(); }