import { openDatabase } from "./database.js"; import { emptyEquipment, emptySlots, insertStack, insertStackGrowable, normalizeVault, normalizeInventorySlots, publicSlots, migrateBagCoins, migrateInheritanceCoins } from "../../../shared/items/inventory.js"; import { STARTER_ITEMS, itemDefinition } from "../../../shared/definitions/items.js"; import { normalizePlayerSkills, defaultPlayerSkills, storedSkillXp } from "../../../shared/skills/state.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) ); CREATE TABLE IF NOT EXISTS persistent_entities ( entity_id INTEGER PRIMARY KEY, tile_x INTEGER NOT NULL, tile_y INTEGER NOT NULL, components_blob TEXT NOT NULL ); CREATE TABLE IF NOT EXISTS character_bags ( character_id INTEGER PRIMARY KEY, inventory_blob TEXT NOT NULL, equipment_blob TEXT NOT NULL, currency INTEGER NOT NULL DEFAULT 0, vault_blob TEXT NOT NULL, vault_currency INTEGER NOT NULL DEFAULT 0, skills_blob TEXT NOT NULL DEFAULT '{}' ); CREATE TABLE IF NOT EXISTS account_inheritance ( account_id INTEGER PRIMARY KEY, slots_blob TEXT NOT NULL, currency INTEGER NOT NULL DEFAULT 0 ); CREATE TABLE IF NOT EXISTS ground_items ( ground_item_id INTEGER PRIMARY KEY, tile_x INTEGER NOT NULL, tile_y INTEGER NOT NULL, stack_blob TEXT NOT NULL, dropped_at INTEGER NOT NULL, expires_at INTEGER NOT NULL, source TEXT NOT NULL, dropped_by_character_id INTEGER ); CREATE TABLE IF NOT EXISTS courier_transits ( courier_id INTEGER PRIMARY KEY AUTOINCREMENT, sender_character_id INTEGER NOT NULL, destination_kind TEXT NOT NULL, destination_character_id INTEGER, cargo_blob TEXT NOT NULL, origin_x REAL NOT NULL, origin_y REAL NOT NULL, dest_x REAL NOT NULL, dest_y REAL NOT NULL, traveled REAL NOT NULL DEFAULT 0, status TEXT NOT NULL, entity_id INTEGER, updated_at INTEGER NOT NULL, created_at INTEGER NOT NULL ); `; export class StateDatabase { constructor (filePath) { this.db = openDatabase(filePath); this.db.exec(STATE_SCHEMA); this.#migrateSchema(); } #migrateSchema () { const columns = this.db.prepare("PRAGMA table_info(character_bags)").all(); if (!columns.some((column) => column.name === "skills_blob")) { this.db.exec("ALTER TABLE character_bags ADD COLUMN skills_blob TEXT NOT NULL DEFAULT '{}'"); } } 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"); this.#ensureBag(characterId); 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.#moveGoodsToInheritance(character); this.db.prepare("DELETE FROM character_bags WHERE character_id = ?").run(characterId); 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); } listPersistentInRegion (minX, minY, maxX, maxY) { return this.db.prepare(` SELECT entity_id, tile_x, tile_y, components_blob FROM persistent_entities WHERE tile_x >= ? AND tile_x <= ? AND tile_y >= ? AND tile_y <= ? `).all(minX, maxX, minY, maxY); } replacePersistentInRegion (minX, minY, maxX, maxY, entities) { this.db.exec("BEGIN"); try { this.db.prepare(` DELETE FROM persistent_entities WHERE tile_x >= ? AND tile_x <= ? AND tile_y >= ? AND tile_y <= ? `).run(minX, maxX, minY, maxY); const insert = this.db.prepare(` INSERT INTO persistent_entities (entity_id, tile_x, tile_y, components_blob) VALUES (?, ?, ?, ?) `); for (const entity of entities) { insert.run( entity.entityId, entity.tileX, entity.tileY, JSON.stringify(entity.payload?.components ?? entity.components) ); } this.db.exec("COMMIT"); } catch (error) { this.db.exec("ROLLBACK"); throw error; } } #ensureBag (characterId) { const existing = this.db.prepare("SELECT * FROM character_bags WHERE character_id = ?").get(characterId); if (existing) { return parseBag(existing, characterId); } const slots = emptySlots(); for (const starter of STARTER_ITEMS) { const result = insertStack(slots, { ...starter }, itemDefinition(starter.itemId)); if (result.ok) { slots.splice(0, slots.length, ...result.slots); } } this.db.prepare(` INSERT INTO character_bags ( character_id, inventory_blob, equipment_blob, currency, vault_blob, vault_currency, skills_blob ) VALUES (?, ?, ?, 0, ?, 0, ?) `).run( characterId, JSON.stringify(slots), JSON.stringify(emptyEquipment()), JSON.stringify({ slots: [], reserved: [] }), JSON.stringify(storedSkillXp(defaultPlayerSkills())) ); return this.getBag(characterId); } getBag (characterId) { const row = this.db.prepare("SELECT * FROM character_bags WHERE character_id = ?").get(characterId); if (row) { return parseBag(row, characterId); } if (this.getCharacter(characterId)) { return this.#ensureBag(characterId); } return null; } saveBag (characterId, bag) { this.#ensureBag(characterId); const migrated = migrateBagCoins(bag); const vault = normalizeVault( migrated.vaultReserved != null ? { slots: migrated.vault, reserved: migrated.vaultReserved } : migrated.vault ); this.db.prepare(` UPDATE character_bags SET inventory_blob = ?, equipment_blob = ?, currency = 0, vault_blob = ?, vault_currency = 0 WHERE character_id = ? `).run( JSON.stringify(normalizeInventorySlots(migrated.inventory ?? emptySlots())), JSON.stringify(migrated.equipment ?? emptyEquipment()), JSON.stringify({ slots: vault.slots, reserved: vault.reserved }), characterId ); } getSkills (characterId) { const row = this.db.prepare("SELECT skills_blob FROM character_bags WHERE character_id = ?").get(characterId); if (row) { return normalizePlayerSkills(JSON.parse(row.skills_blob || "{}")); } if (this.getCharacter(characterId)) { this.#ensureBag(characterId); return this.getSkills(characterId); } return null; } saveSkills (characterId, skills) { this.#ensureBag(characterId); const xp = storedSkillXp(skills?.xp ? skills : { xp: skills }); this.db.prepare("UPDATE character_bags SET skills_blob = ? WHERE character_id = ?").run( JSON.stringify({ xp }), characterId ); } getInheritance (accountId) { const row = this.db.prepare("SELECT * FROM account_inheritance WHERE account_id = ?").get(accountId); if (!row) { return { accountId, slots: emptySlots() }; } return migrateInheritanceCoins({ accountId, slots: JSON.parse(row.slots_blob), currency: row.currency }); } saveInheritance (accountId, inheritance) { const migrated = migrateInheritanceCoins(inheritance); this.db.prepare(` INSERT INTO account_inheritance (account_id, slots_blob, currency) VALUES (?, ?, 0) ON CONFLICT(account_id) DO UPDATE SET slots_blob = excluded.slots_blob, currency = 0 `).run(accountId, JSON.stringify(migrated.slots ?? emptySlots())); } #moveGoodsToInheritance (character) { const bag = this.getBag(character.character_id) ?? { inventory: emptySlots(), equipment: emptyEquipment(), vault: [], vaultReserved: [] }; const inheritance = this.getInheritance(character.account_id); const vault = normalizeVault( bag.vaultReserved != null ? { slots: bag.vault, reserved: bag.vaultReserved } : bag.vault ); // Void outstanding cheques issued by this character: reservations return to available vault. for (const reserved of vault.reserved ?? []) { const stack = { itemId: reserved.itemId, quantity: reserved.quantity, origin: reserved.origin ?? null, instance: reserved.instance ?? null }; const def = itemDefinition(stack.itemId); if (!def) { continue; } const restored = insertStackGrowable(vault.slots, stack, def); vault.slots = restored.slots; } vault.reserved = []; const incoming = [ ...(bag.inventory ?? []), ...Object.values(bag.equipment?.slots ?? {}), ...(vault.slots ?? []) ].filter(Boolean); let slots = inheritance.slots ?? emptySlots(); for (const stack of incoming) { const def = itemDefinition(stack.itemId); if (!def) { continue; } const result = insertStack(slots, stack, def); if (result.ok) { slots = result.slots; } else { const extra = result.slots; for (const leftover of extra) { if (leftover) { slots.push(leftover); } } slots.push({ ...stack, quantity: result.remainder }); } } this.saveInheritance(character.account_id, { slots: publicSlots(slots) }); } nextGroundItemId () { const row = this.db.prepare("SELECT MAX(ground_item_id) AS id FROM ground_items").get(); return (row?.id ?? 0) + 1; } listGroundItemsInRegion (minX, minY, maxX, maxY) { return this.db.prepare(` SELECT * FROM ground_items WHERE tile_x >= ? AND tile_x <= ? AND tile_y >= ? AND tile_y <= ? `).all(minX, maxX, minY, maxY).map(parseGroundItem); } upsertGroundItem (item) { this.db.prepare(` INSERT INTO ground_items ( ground_item_id, tile_x, tile_y, stack_blob, dropped_at, expires_at, source, dropped_by_character_id ) VALUES (?, ?, ?, ?, ?, ?, ?, ?) ON CONFLICT(ground_item_id) DO UPDATE SET tile_x = excluded.tile_x, tile_y = excluded.tile_y, stack_blob = excluded.stack_blob, dropped_at = excluded.dropped_at, expires_at = excluded.expires_at, source = excluded.source, dropped_by_character_id = excluded.dropped_by_character_id `).run( item.groundItemId, item.tileX, item.tileY, JSON.stringify(item.stack), item.droppedAt, item.expiresAt, item.source, item.droppedByCharacterId ?? null ); } deleteGroundItem (groundItemId) { this.db.prepare("DELETE FROM ground_items WHERE ground_item_id = ?").run(groundItemId); } insertCourierTransit (transit) { const now = transit.updatedAt ?? Date.now(); const result = this.db.prepare(` INSERT INTO courier_transits ( sender_character_id, destination_kind, destination_character_id, cargo_blob, origin_x, origin_y, dest_x, dest_y, traveled, status, entity_id, updated_at, created_at ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) `).run( transit.senderCharacterId, transit.destinationKind, transit.destinationCharacterId ?? null, JSON.stringify(transit.cargo), transit.originX, transit.originY, transit.destX, transit.destY, transit.traveled ?? 0, transit.status ?? "in_transit", transit.entityId ?? null, now, transit.createdAt ?? now ); return this.getCourierTransit(Number(result.lastInsertRowid)); } getCourierTransit (courierId) { const row = this.db.prepare("SELECT * FROM courier_transits WHERE courier_id = ?").get(courierId); return row ? parseCourierTransit(row) : null; } listActiveCourierTransits () { return this.db.prepare(` SELECT * FROM courier_transits WHERE status = 'in_transit' ORDER BY courier_id ASC `).all().map(parseCourierTransit); } updateCourierTransit (transit) { this.db.prepare(` UPDATE courier_transits SET traveled = ?, status = ?, entity_id = ?, updated_at = ?, origin_x = ?, origin_y = ?, dest_x = ?, dest_y = ?, destination_kind = ?, destination_character_id = ? WHERE courier_id = ? `).run( transit.traveled ?? 0, transit.status, transit.entityId ?? null, transit.updatedAt ?? Date.now(), transit.originX, transit.originY, transit.destX, transit.destY, transit.destinationKind, transit.destinationCharacterId ?? null, transit.courierId ); } close () { this.db.close(); } } export function normalizeName (value) { return String(value ?? "").trim().toLocaleLowerCase(); } function pairIds (left, right) { return left < right ? [left, right] : [right, left]; } function parseGroundItem (row) { return { groundItemId: row.ground_item_id, tileX: row.tile_x, tileY: row.tile_y, stack: JSON.parse(row.stack_blob), droppedAt: row.dropped_at, expiresAt: row.expires_at, source: row.source, droppedByCharacterId: row.dropped_by_character_id }; } function parseBag (row, characterId) { const vault = normalizeVault(JSON.parse(row.vault_blob)); return migrateBagCoins({ characterId, inventory: normalizeInventorySlots(JSON.parse(row.inventory_blob)), equipment: JSON.parse(row.equipment_blob), currency: row.currency, vault: vault.slots, vaultReserved: vault.reserved, vaultCurrency: row.vault_currency }); } function parseCourierTransit (row) { return { courierId: row.courier_id, senderCharacterId: row.sender_character_id, destinationKind: row.destination_kind, destinationCharacterId: row.destination_character_id, cargo: JSON.parse(row.cargo_blob), originX: row.origin_x, originY: row.origin_y, destX: row.dest_x, destY: row.dest_y, traveled: row.traveled, status: row.status, entityId: row.entity_id, updatedAt: row.updated_at, createdAt: row.created_at }; }