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 ); CREATE TABLE IF NOT EXISTS friend_requests ( request_id INTEGER PRIMARY KEY AUTOINCREMENT, from_character_id INTEGER NOT NULL, to_character_id INTEGER NOT NULL, created_at INTEGER NOT NULL, UNIQUE (from_character_id, to_character_id) ); CREATE TABLE IF NOT EXISTS friendships ( character_id_a INTEGER NOT NULL, character_id_b INTEGER NOT NULL, created_at INTEGER NOT NULL, PRIMARY KEY (character_id_a, character_id_b) ); `; 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); this.db.prepare("DELETE FROM friend_requests WHERE from_character_id = ? OR to_character_id = ?").run( characterId, characterId ); this.db.prepare("DELETE FROM friendships WHERE character_id_a = ? OR character_id_b = ?").run( characterId, 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); } getCharacterByName (firstName, lastName) { return this.db.prepare(` SELECT * FROM characters WHERE first_normalized = ? AND last_normalized = ? `).get(normalizeName(firstName), normalizeName(lastName)) ?? null; } listFriendRecords (characterId) { return this.db.prepare(` SELECT CASE WHEN character_id_a = ? THEN character_id_b ELSE character_id_a END AS character_id, created_at FROM friendships WHERE character_id_a = ? OR character_id_b = ? ORDER BY created_at ASC `).all(characterId, characterId, characterId); } areFriends (characterId, otherId) { const [a, b] = pairIds(characterId, otherId); return !!this.db.prepare(` SELECT 1 FROM friendships WHERE character_id_a = ? AND character_id_b = ? `).get(a, b); } insertFriendship (characterId, otherId) { const [a, b] = pairIds(characterId, otherId); this.db.prepare(` INSERT INTO friendships (character_id_a, character_id_b, created_at) VALUES (?, ?, ?) `).run(a, b, Date.now()); } deleteFriendship (characterId, otherId) { const [a, b] = pairIds(characterId, otherId); this.db.prepare(` DELETE FROM friendships WHERE character_id_a = ? AND character_id_b = ? `).run(a, b); } listIncomingRequests (characterId) { return this.db.prepare(` SELECT request_id, from_character_id, to_character_id, created_at FROM friend_requests WHERE to_character_id = ? ORDER BY created_at ASC `).all(characterId); } listOutgoingRequests (characterId) { return this.db.prepare(` SELECT request_id, from_character_id, to_character_id, created_at FROM friend_requests WHERE from_character_id = ? ORDER BY created_at ASC `).all(characterId); } getFriendRequest (fromId, toId) { return this.db.prepare(` SELECT * FROM friend_requests WHERE from_character_id = ? AND to_character_id = ? `).get(fromId, toId) ?? null; } insertFriendRequest (fromId, toId) { this.db.prepare(` INSERT INTO friend_requests (from_character_id, to_character_id, created_at) VALUES (?, ?, ?) `).run(fromId, toId, Date.now()); } deleteFriendRequest (fromId, toId) { this.db.prepare(` DELETE FROM friend_requests WHERE from_character_id = ? AND to_character_id = ? `).run(fromId, toId); } close () { this.db.close(); } } export function normalizeName (value) { return String(value ?? "").trim().toLocaleLowerCase(); } function pairIds (left, right) { return left < right ? [left, right] : [right, left]; }