import { openDatabase } from "./database.js"; import { emptyEquipment, emptySlots, insertStack, insertStackGrowable, normalizeVault, normalizeInventorySlots, publicSlots, migrateBagCoins, migrateInheritanceCoins } from "../../../shared/items/inventory.js"; import { attachOrigins, godsOrigin } from "../../../shared/items/origin.js"; import { itemDefinition, ItemId } from "../../../shared/definitions/items.js"; import { buildStarterBagContents } from "../../../shared/items/starterLoadout.js"; import { normalizePlayerSkills, defaultPlayerSkills, storedSkillXp } from "../../../shared/skills/state.js"; import { normalizePlayerSpells, defaultPlayerSpells, storedPlayerSpells } from "../../../shared/spells/state.js"; import { decayKillCounter } from "../../../shared/combat/skull.js"; import { applyBountyDailyDecay, bountyDecayDayKey, bountyDecayDaysBetween } from "../../../shared/bounty/pricing.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', is_dead INTEGER NOT NULL DEFAULT 0, deletion_scheduled_at INTEGER, hair_style TEXT, hair_hue REAL NOT NULL DEFAULT 30, hair_saturation REAL NOT NULL DEFAULT 0.55, hair_darkness REAL NOT NULL DEFAULT 0.45 ); 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, dimension_id TEXT NOT NULL DEFAULT 'overworld', 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, dimension_id TEXT NOT NULL DEFAULT 'overworld', 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, origin_dimension_id TEXT NOT NULL DEFAULT 'overworld', dest_dimension_id TEXT NOT NULL DEFAULT 'overworld', legs_blob TEXT, leg_index INTEGER NOT NULL DEFAULT 0, traveled REAL NOT NULL DEFAULT 0, status TEXT NOT NULL, entity_id INTEGER, updated_at INTEGER NOT NULL, created_at INTEGER NOT NULL ); CREATE TABLE IF NOT EXISTS market_orders ( order_id INTEGER PRIMARY KEY AUTOINCREMENT, character_id INTEGER NOT NULL, side TEXT NOT NULL, item_id INTEGER NOT NULL, quantity INTEGER NOT NULL, limit_unit_price INTEGER NOT NULL, escrow_coins INTEGER NOT NULL DEFAULT 0, escrow_stack_blob TEXT, listing_key TEXT, created_at INTEGER NOT NULL, status TEXT NOT NULL ); CREATE TABLE IF NOT EXISTS market_trades ( trade_id INTEGER PRIMARY KEY AUTOINCREMENT, buy_order_id INTEGER NOT NULL, sell_order_id INTEGER NOT NULL, item_id INTEGER NOT NULL, quantity INTEGER NOT NULL, unit_price INTEGER NOT NULL, buyer_character_id INTEGER NOT NULL, seller_character_id INTEGER NOT NULL, created_at INTEGER NOT NULL ); CREATE TABLE IF NOT EXISTS item_last_sales ( item_id INTEGER PRIMARY KEY, last_trade_price INTEGER NOT NULL, last_trade_at INTEGER NOT NULL, price_from INTEGER NOT NULL ); CREATE TABLE IF NOT EXISTS bank_rest_notices ( notice_id INTEGER PRIMARY KEY AUTOINCREMENT, character_id INTEGER NOT NULL, item_id INTEGER NOT NULL, item_name TEXT NOT NULL, quantity INTEGER NOT NULL, source_name TEXT NOT NULL, created_at INTEGER NOT NULL ); CREATE TABLE IF NOT EXISTS held_courier_deliveries ( hold_id INTEGER PRIMARY KEY AUTOINCREMENT, character_id INTEGER NOT NULL, sender_character_id INTEGER NOT NULL, origin_tile_x INTEGER NOT NULL, origin_tile_y INTEGER NOT NULL, origin_dimension_id TEXT NOT NULL DEFAULT 'overworld', destination_kind TEXT NOT NULL, destination_character_id INTEGER, vault_character_id INTEGER, cargo_blob TEXT NOT NULL, created_at INTEGER NOT NULL ); CREATE TABLE IF NOT EXISTS character_bounties ( character_id INTEGER PRIMARY KEY, amount INTEGER NOT NULL DEFAULT 0, last_decay_day TEXT ); CREATE TABLE IF NOT EXISTS character_quest_state ( character_id INTEGER NOT NULL, quest_id TEXT NOT NULL, state_value TEXT NOT NULL, progress_blob TEXT NOT NULL DEFAULT '{}', started_at INTEGER, completed_at INTEGER, PRIMARY KEY (character_id, quest_id) ); CREATE TABLE IF NOT EXISTS character_dialogue_heard ( character_id INTEGER NOT NULL, dialogue_id TEXT NOT NULL, step_id TEXT NOT NULL, heard_at INTEGER NOT NULL, PRIMARY KEY (character_id, dialogue_id, step_id) ); CREATE TABLE IF NOT EXISTS character_quest_journal ( entry_id INTEGER PRIMARY KEY AUTOINCREMENT, character_id INTEGER NOT NULL, quest_id TEXT NOT NULL, step_id TEXT NOT NULL, text TEXT NOT NULL, written_at INTEGER NOT NULL ); CREATE TABLE IF NOT EXISTS character_dialogue_triggers ( character_id INTEGER NOT NULL, dialogue_id TEXT NOT NULL, trigger_id TEXT NOT NULL, latched_at INTEGER NOT NULL, PRIMARY KEY (character_id, dialogue_id, trigger_id) ); CREATE TABLE IF NOT EXISTS character_visited_dimensions ( character_id INTEGER NOT NULL, dimension_id TEXT NOT NULL, first_visited_at INTEGER NOT NULL, PRIMARY KEY (character_id, dimension_id) ); `; export class StateDatabase { constructor(filePath) { this.db = openDatabase(filePath); this.db.exec(STATE_SCHEMA); this.#migrateSchema(); } #migrateSchema () { const bagColumns = this.db.prepare("PRAGMA table_info(character_bags)").all(); if (!bagColumns.some((column) => column.name === "skills_blob")) { this.db.exec("ALTER TABLE character_bags ADD COLUMN skills_blob TEXT NOT NULL DEFAULT '{}'"); } const characterColumns = this.db.prepare("PRAGMA table_info(characters)").all(); if (!characterColumns.some((column) => column.name === "is_dead")) { this.db.exec("ALTER TABLE characters ADD COLUMN is_dead INTEGER NOT NULL DEFAULT 0"); } if (!characterColumns.some((column) => column.name === "hair_style")) { this.db.exec("ALTER TABLE characters ADD COLUMN hair_style TEXT"); this.db.exec("ALTER TABLE characters ADD COLUMN hair_hue REAL NOT NULL DEFAULT 30"); this.db.exec("ALTER TABLE characters ADD COLUMN hair_saturation REAL NOT NULL DEFAULT 0.55"); this.db.exec("ALTER TABLE characters ADD COLUMN hair_darkness REAL NOT NULL DEFAULT 0.45"); } if (!characterColumns.some((column) => column.name === "dimension_id")) { this.db.exec("ALTER TABLE characters ADD COLUMN dimension_id TEXT NOT NULL DEFAULT 'overworld'"); } this.db.exec(` CREATE TABLE IF NOT EXISTS character_visited_dimensions ( character_id INTEGER NOT NULL, dimension_id TEXT NOT NULL, first_visited_at INTEGER NOT NULL, PRIMARY KEY (character_id, dimension_id) ) `); const courierColumns = this.db.prepare("PRAGMA table_info(courier_transits)").all(); if (!courierColumns.some((column) => column.name === "vault_character_id")) { this.db.exec("ALTER TABLE courier_transits ADD COLUMN vault_character_id INTEGER"); } if (!courierColumns.some((column) => column.name === "origin_dimension_id")) { this.db.exec("ALTER TABLE courier_transits ADD COLUMN origin_dimension_id TEXT NOT NULL DEFAULT 'overworld'"); } if (!courierColumns.some((column) => column.name === "dest_dimension_id")) { this.db.exec("ALTER TABLE courier_transits ADD COLUMN dest_dimension_id TEXT NOT NULL DEFAULT 'overworld'"); } if (!courierColumns.some((column) => column.name === "legs_blob")) { this.db.exec("ALTER TABLE courier_transits ADD COLUMN legs_blob TEXT"); } if (!courierColumns.some((column) => column.name === "leg_index")) { this.db.exec("ALTER TABLE courier_transits ADD COLUMN leg_index INTEGER NOT NULL DEFAULT 0"); } const heldCourierColumns = this.db.prepare("PRAGMA table_info(held_courier_deliveries)").all(); if (!heldCourierColumns.some((column) => column.name === "origin_dimension_id")) { this.db.exec("ALTER TABLE held_courier_deliveries ADD COLUMN origin_dimension_id TEXT NOT NULL DEFAULT 'overworld'"); } if (!bagColumns.some((column) => column.name === "spells_blob")) { this.db.exec("ALTER TABLE character_bags ADD COLUMN spells_blob TEXT NOT NULL DEFAULT '{}'"); } if (!characterColumns.some((column) => column.name === "health_current")) { this.db.exec("ALTER TABLE characters ADD COLUMN health_current INTEGER"); } const accountColumns = this.db.prepare("PRAGMA table_info(accounts)").all(); if (!accountColumns.some((column) => column.name === "username")) { this.db.exec("ALTER TABLE accounts ADD COLUMN username TEXT"); } if (!characterColumns.some((column) => column.name === "kill_counter")) { this.db.exec("ALTER TABLE characters ADD COLUMN kill_counter REAL NOT NULL DEFAULT 0"); } if (!characterColumns.some((column) => column.name === "kill_counter_at")) { this.db.exec("ALTER TABLE characters ADD COLUMN kill_counter_at INTEGER"); } if (!characterColumns.some((column) => column.name === "cowardice_forgiven_hour")) { this.db.exec("ALTER TABLE characters ADD COLUMN cowardice_forgiven_hour INTEGER"); } if (!characterColumns.some((column) => column.name === "respawn_altar_x")) { this.db.exec("ALTER TABLE characters ADD COLUMN respawn_altar_x INTEGER"); this.db.exec("ALTER TABLE characters ADD COLUMN respawn_altar_y INTEGER"); this.db.exec("ALTER TABLE characters ADD COLUMN respawn_altar_dimension_id TEXT"); } const groundColumns = this.db.prepare("PRAGMA table_info(ground_items)").all(); if (!groundColumns.some((column) => column.name === "dimension_id")) { this.db.exec("ALTER TABLE ground_items ADD COLUMN dimension_id TEXT NOT NULL DEFAULT 'overworld'"); } const persistentColumns = this.db.prepare("PRAGMA table_info(persistent_entities)").all(); if (!persistentColumns.some((column) => column.name === "dimension_id")) { this.db.exec("ALTER TABLE persistent_entities ADD COLUMN dimension_id TEXT NOT NULL DEFAULT 'overworld'"); } const marketColumns = this.db.prepare("PRAGMA table_info(market_orders)").all(); if (!marketColumns.some((column) => column.name === "listing_key")) { this.db.exec("ALTER TABLE market_orders ADD COLUMN listing_key TEXT"); } const bountyColumns = this.db.prepare("PRAGMA table_info(character_bounties)").all(); if (!bountyColumns.some((column) => column.name === "last_decay_day")) { this.db.exec("ALTER TABLE character_bounties ADD COLUMN last_decay_day TEXT"); } this.db.exec(` CREATE TABLE IF NOT EXISTS account_friend_requests ( request_id INTEGER PRIMARY KEY AUTOINCREMENT, from_account_id INTEGER NOT NULL, to_account_id INTEGER NOT NULL, created_at INTEGER NOT NULL, UNIQUE (from_account_id, to_account_id) ) `); this.db.exec(` CREATE TABLE IF NOT EXISTS account_friendships ( account_id_a INTEGER NOT NULL, account_id_b INTEGER NOT NULL, created_at INTEGER NOT NULL, PRIMARY KEY (account_id_a, account_id_b) ) `); this.#migrateCharacterFriendsToAccounts(); this.db.exec(` CREATE TABLE IF NOT EXISTS auth_sessions ( session_id TEXT PRIMARY KEY, auth_subject TEXT NOT NULL, username TEXT, email TEXT, access_token TEXT, expires_at INTEGER NOT NULL ) `); this.db.exec(` CREATE INDEX IF NOT EXISTS auth_sessions_subject ON auth_sessions(auth_subject) `); this.#grantLanternsToExistingCharacters(); } /** One-time: lift legacy character friendships / requests to account scope. */ #migrateCharacterFriendsToAccounts () { const legacyFriends = this.db.prepare("SELECT character_id_a, character_id_b, created_at FROM friendships").all(); const insertFriend = this.db.prepare(` INSERT OR IGNORE INTO account_friendships (account_id_a, account_id_b, created_at) VALUES (?, ?, ?) `); for (const row of legacyFriends) { const a = this.getCharacter(row.character_id_a); const b = this.getCharacter(row.character_id_b); if (!a || !b || a.account_id === b.account_id) { continue; } const [idA, idB] = pairIds(a.account_id, b.account_id); insertFriend.run(idA, idB, row.created_at | 0 || Date.now()); } const legacyRequests = this.db.prepare(` SELECT from_character_id, to_character_id, created_at FROM friend_requests `).all(); const insertReq = this.db.prepare(` INSERT OR IGNORE INTO account_friend_requests (from_account_id, to_account_id, created_at) VALUES (?, ?, ?) `); for (const row of legacyRequests) { const from = this.getCharacter(row.from_character_id); const to = this.getCharacter(row.to_character_id); if (!from || !to || from.account_id === to.account_id) { continue; } insertReq.run(from.account_id, to.account_id, row.created_at | 0 || Date.now()); } if (legacyFriends.length || legacyRequests.length) { this.db.exec("DELETE FROM friendships"); this.db.exec("DELETE FROM friend_requests"); } } createAuthSession ({ sessionId, authSubject, username = null, email = null, accessToken = null, expiresAtMs }) { this.db.prepare(` INSERT INTO auth_sessions (session_id, auth_subject, username, email, access_token, expires_at) VALUES (?, ?, ?, ?, ?, ?) `).run(sessionId, authSubject, username, email, accessToken, expiresAtMs); } getAuthSession (sessionId) { return this.db.prepare(` SELECT session_id, auth_subject, username, email, access_token, expires_at FROM auth_sessions WHERE session_id = ? `).get(sessionId) ?? null; } deleteAuthSession (sessionId) { if (!sessionId) { return; } this.db.prepare("DELETE FROM auth_sessions WHERE session_id = ?").run(sessionId); } #grantLanternsToExistingCharacters () { const def = itemDefinition(ItemId.lantern); if (!def) { return; } const rows = this.db.prepare("SELECT character_id, inventory_blob, equipment_blob FROM character_bags").all(); const update = this.db.prepare(` UPDATE character_bags SET inventory_blob = ?, equipment_blob = ? WHERE character_id = ? `); for (const row of rows) { const inventory = JSON.parse(row.inventory_blob || "[]"); const equipment = JSON.parse(row.equipment_blob || "{}"); if (bagContainsItem(inventory, equipment, ItemId.lantern)) { continue; } if (!equipment.slots?.offhand) { equipment.slots = equipment.slots ?? {}; equipment.slots.offhand = attachOrigins({ itemId: ItemId.lantern, quantity: 1, origins: [{ origin: godsOrigin(), quantity: 1 }] }); } else { const result = insertStack(inventory, attachOrigins({ itemId: ItemId.lantern, quantity: 1, origins: [{ origin: godsOrigin(), quantity: 1 }] }), def); if (!result.ok) { continue; } inventory.splice(0, inventory.length, ...result.slots); } update.run(JSON.stringify(inventory), JSON.stringify(equipment), row.character_id); } } getAccountBySubject (authSubject) { return this.db.prepare("SELECT * FROM accounts WHERE auth_subject = ?").get(authSubject) ?? null; } countAccounts () { return this.db.prepare("SELECT COUNT(*) AS count FROM accounts").get().count; } countGameAdmins () { return this.db.prepare("SELECT COUNT(*) AS count FROM accounts WHERE is_admin = 1").get().count; } findAccountByUsername (username) { const normalized = String(username ?? "").trim().toLowerCase(); if (!normalized) { return null; } return this.db.prepare(` SELECT * FROM accounts WHERE lower(username) = ? ORDER BY account_id ASC LIMIT 1 `).get(normalized) ?? null; } listGameAdmins () { return this.db.prepare(` SELECT account_id, auth_subject, username, email, is_admin FROM accounts WHERE is_admin = 1 ORDER BY account_id ASC `).all(); } #syncAccountProfile (accountId, { email, username }) { const fields = []; const values = []; // Never blank a stored email when auth stops sending one. if (typeof email === "string" && email.trim() !== "") { fields.push("email = ?"); values.push(email.trim()); } if (username != null) { fields.push("username = ?"); values.push(username); } if (!fields.length) { return; } values.push(accountId); this.db.prepare(`UPDATE accounts SET ${fields.join(", ")} WHERE account_id = ?`).run(...values); } resolveAccountFromAuth ({ authSubject, email = null, username = null }) { const existing = this.getAccountBySubject(authSubject); if (existing) { this.#syncAccountProfile(existing.account_id, { email, username }); return this.getAccountBySubject(authSubject); } const isFirstAccount = this.countAccounts() === 0; return this.createAccount(authSubject, isFirstAccount, email, username); } setGameAdmin (authSubject, isAdmin) { const account = this.getAccountBySubject(authSubject); if (!account) { const error = new Error("Account not found."); error.userFacing = true; throw error; } if (!isAdmin && account.is_admin && this.countGameAdmins() <= 1) { const error = new Error("Cannot remove the last game admin."); error.userFacing = true; throw error; } this.db.prepare("UPDATE accounts SET is_admin = ? WHERE auth_subject = ?").run(isAdmin ? 1 : 0, authSubject); return this.getAccountBySubject(authSubject); } grantGameAdminByUsername (username) { const account = this.findAccountByUsername(username); if (!account) { const error = new Error("No account with that username has signed in yet."); error.userFacing = true; throw error; } return this.setGameAdmin(account.auth_subject, true); } createAccount (authSubject, isAdmin, email = null, username = null) { this.db.prepare(` INSERT INTO accounts (auth_subject, email, username, is_admin) VALUES (?, ?, ?, ?) `).run( authSubject, email, username, isAdmin ? 1 : 0 ); return this.getAccountBySubject(authSubject); } ensureAccount (authSubject, isAdmin, email = null, username = null) { const existing = this.getAccountBySubject(authSubject); if (existing) { this.#syncAccountProfile(existing.account_id, { email, username }); return existing; } return this.createAccount(authSubject, isAdmin, email, username); } 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; } /** All character ids (for world-settings fan-out such as initial quests). */ listAllCharacterIds () { return this.db.prepare("SELECT character_id FROM characters").all() .map((row) => row.character_id | 0) .filter((id) => id > 0); } findSurnameClaim (surnameNormalized) { return this.db.prepare("SELECT * FROM surname_claims WHERE surname_normalized = ?").get(surnameNormalized) ?? null; } createCharacter (accountId, firstName, lastName, sex, tileX, tileY, hair = null, starterSettings = null, creationClothing = null) { const firstNormalized = normalizeName(firstName); const lastNormalized = normalizeName(lastName); const hairStyle = hair?.style ?? null; const hairHue = Number(hair?.hue) || 30; const hairSaturation = Number(hair?.saturation); const hairDarkness = Number(hair?.darkness); 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, dimension_id, hair_style, hair_hue, hair_saturation, hair_darkness ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, 'overworld', ?, ?, ?, ?) `).run( accountId, firstName, lastName, firstNormalized, lastNormalized, sex, tileX, tileY, hairStyle, hairHue, Number.isFinite(hairSaturation) ? hairSaturation : 0.55, Number.isFinite(hairDarkness) ? hairDarkness : 0.45 ); 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, starterSettings, creationClothing); return this.getCharacter(characterId); } catch (error) { this.db.exec("ROLLBACK"); throw error; } } updateCharacterHair (characterId, { style, hue, saturation, darkness }) { this.db.prepare(` UPDATE characters SET hair_style = ?, hair_hue = ?, hair_saturation = ?, hair_darkness = ? WHERE character_id = ? `).run( style ?? null, Number(hue) || 30, Number.isFinite(Number(saturation)) ? Number(saturation) : 0.55, Number.isFinite(Number(darkness)) ? Number(darkness) : 0.45, characterId | 0 ); return this.getCharacter(characterId); } 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 character_bounties WHERE character_id = ?").run(characterId); // Account friendships persist; only clear legacy character rows if any remain. 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, dimensionId = "overworld") { this.db.prepare(` UPDATE characters SET tile_x = ?, tile_y = ?, gait = ?, dimension_id = ? WHERE character_id = ? `).run( tileX, tileY, gait, String(dimensionId ?? "overworld"), characterId ); } getCharacterRespawnAltar (characterId) { const row = this.db.prepare(` SELECT respawn_altar_x, respawn_altar_y, respawn_altar_dimension_id FROM characters WHERE character_id = ? `).get(characterId | 0); if (!row || row.respawn_altar_x == null || row.respawn_altar_y == null) { return null; } return { tileX: row.respawn_altar_x | 0, tileY: row.respawn_altar_y | 0, dimensionId: String(row.respawn_altar_dimension_id ?? "overworld") }; } setCharacterRespawnAltar (characterId, tileX, tileY, dimensionId = "overworld") { this.db.prepare(` UPDATE characters SET respawn_altar_x = ?, respawn_altar_y = ?, respawn_altar_dimension_id = ? WHERE character_id = ? `).run(tileX | 0, tileY | 0, String(dimensionId ?? "overworld"), characterId | 0); } markDimensionVisited (characterId, dimensionId, now = Date.now()) { const dim = String(dimensionId ?? "overworld"); this.db.prepare(` INSERT INTO character_visited_dimensions (character_id, dimension_id, first_visited_at) VALUES (?, ?, ?) ON CONFLICT(character_id, dimension_id) DO NOTHING `).run(characterId | 0, dim, now); } hasVisitedDimension (characterId, dimensionId) { const row = this.db.prepare(` SELECT 1 AS ok FROM character_visited_dimensions WHERE character_id = ? AND dimension_id = ? `).get(characterId | 0, String(dimensionId ?? "overworld")); return !!row; } listVisitedDimensions (characterId) { return this.db.prepare(` SELECT dimension_id, first_visited_at FROM character_visited_dimensions WHERE character_id = ? ORDER BY first_visited_at ASC `).all(characterId | 0).map((row) => ({ dimensionId: row.dimension_id, firstVisitedAt: row.first_visited_at })); } saveCharacterHealth (characterId, current) { this.db.prepare("UPDATE characters SET health_current = ? WHERE character_id = ?").run( Math.max(0, current | 0), characterId ); } setCharacterDead (characterId, isDead) { this.db.prepare("UPDATE characters SET is_dead = ? WHERE character_id = ?").run( isDead ? 1 : 0, 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; } /** * Admin gift search: match first name, last name, "first last", or account username. * @param {string} query * @param {number} [limit] */ searchCharactersForGift (query, limit = 40) { const raw = String(query ?? "").trim(); if (!raw) { return []; } const cap = Math.min(100, Math.max(1, limit | 0 || 40)); const needle = `%${normalizeName(raw).replace(/%/g, "")}%`; const parts = raw.split(/\s+/u).filter(Boolean); const firstPart = parts[0] ? normalizeName(parts[0]) : ""; const lastPart = parts.length >= 2 ? normalizeName(parts.slice(1).join(" ")) : ""; if (parts.length >= 2 && firstPart && lastPart) { return this.db.prepare(` SELECT c.character_id, c.first_name, c.last_name, c.account_id, a.username AS account_username FROM characters c LEFT JOIN accounts a ON a.account_id = c.account_id WHERE (c.first_normalized = ? AND c.last_normalized = ?) OR c.first_normalized LIKE ? OR c.last_normalized LIKE ? OR lower(COALESCE(a.username, '')) LIKE ? OR (c.first_normalized || ' ' || c.last_normalized) LIKE ? ORDER BY c.last_normalized ASC, c.first_normalized ASC, c.character_id ASC LIMIT ? `).all(firstPart, lastPart, needle, needle, needle, needle, cap); } return this.db.prepare(` SELECT c.character_id, c.first_name, c.last_name, c.account_id, a.username AS account_username FROM characters c LEFT JOIN accounts a ON a.account_id = c.account_id WHERE c.first_normalized LIKE ? OR c.last_normalized LIKE ? OR lower(COALESCE(a.username, '')) LIKE ? OR (c.first_normalized || ' ' || c.last_normalized) LIKE ? ORDER BY c.last_normalized ASC, c.first_normalized ASC, c.character_id ASC LIMIT ? `).all(needle, needle, needle, needle, cap); } getCharacterBounty (characterId, now = Date.now()) { return this.applyCharacterBountyDecay(characterId, now); } /** * Daily −1 bounty decay (lazy). First touch after migration stamps today without retroactive decay. * Never moves `last_decay_day` backward (simulation clocks in tests can be epoch-relative). */ applyCharacterBountyDecay (characterId, now = Date.now()) { const id = characterId | 0; const row = this.db.prepare(` SELECT amount, last_decay_day FROM character_bounties WHERE character_id = ? `).get(id); if (!row) { return 0; } const day = bountyDecayDayKey(now); let amount = Math.max(0, row.amount | 0); const last = row.last_decay_day ? String(row.last_decay_day) : ""; if (!last) { this.db.prepare(` UPDATE character_bounties SET last_decay_day = ? WHERE character_id = ? `).run(day, id); return amount; } if (last === day) { return amount; } const days = bountyDecayDaysBetween(last, day); if (days <= 0) { // `now` is on/before the stamped day (clock skew / harness) — leave the stamp alone. return amount; } amount = applyBountyDailyDecay(amount, days); this.db.prepare(` UPDATE character_bounties SET amount = ?, last_decay_day = ? WHERE character_id = ? `).run(amount, day, id); return amount; } /** Add coins to a bounty; returns the new total. */ addCharacterBounty (characterId, amount, now = Date.now()) { const id = characterId | 0; const add = Math.max(0, amount | 0); if (add <= 0) { return this.getCharacterBounty(id, now); } const day = bountyDecayDayKey(now); this.applyCharacterBountyDecay(id, now); this.db.prepare(` INSERT INTO character_bounties (character_id, amount, last_decay_day) VALUES (?, ?, ?) ON CONFLICT(character_id) DO UPDATE SET amount = amount + excluded.amount `).run(id, add, day); return this.getCharacterBounty(id, now); } /** Reduce bounty by up to `amount`; returns the new total. */ reduceCharacterBounty (characterId, amount, now = Date.now()) { const id = characterId | 0; const cut = Math.max(0, amount | 0); const current = this.getCharacterBounty(id, now); if (cut <= 0) { return current; } const next = Math.max(0, current - cut); const day = bountyDecayDayKey(now); this.db.prepare(` INSERT INTO character_bounties (character_id, amount, last_decay_day) VALUES (?, ?, ?) ON CONFLICT(character_id) DO UPDATE SET amount = excluded.amount `).run(id, next, day); return next; } /** Zero out and return the previous bounty amount. */ takeCharacterBounty (characterId, now = Date.now()) { const id = characterId | 0; const amount = this.getCharacterBounty(id, now); if (amount <= 0) { return 0; } const day = bountyDecayDayKey(now); this.db.prepare(` INSERT INTO character_bounties (character_id, amount, last_decay_day) VALUES (?, 0, ?) ON CONFLICT(character_id) DO UPDATE SET amount = 0 `).run(id, day); return amount; } getQuestState (characterId, questId) { const row = this.db.prepare(` SELECT state_value, progress_blob, started_at, completed_at FROM character_quest_state WHERE character_id = ? AND quest_id = ? `).get(characterId | 0, String(questId)); if (!row) { return { state: "not-started", progress: {}, startedAt: null, completedAt: null }; } const progress = JSON.parse(row.progress_blob || "{}"); let state = row.state_value; if (state !== "not-started" && state !== "completed") { state = Number(state) | 0; } return { state, progress, startedAt: row.started_at ?? null, completedAt: row.completed_at ?? null }; } listQuestStates (characterId) { return this.db.prepare(` SELECT quest_id, state_value, progress_blob, started_at, completed_at FROM character_quest_state WHERE character_id = ? `).all(characterId | 0).map((row) => { let state = row.state_value; if (state !== "not-started" && state !== "completed") { state = Number(state) | 0; } return { questId: row.quest_id, state, progress: JSON.parse(row.progress_blob || "{}"), startedAt: row.started_at ?? null, completedAt: row.completed_at ?? null }; }); } saveQuestState (characterId, questId, state, progress = {}, startedAt = null, completedAt = null) { const stateValue = state === "not-started" || state === "completed" ? state : String(state | 0); this.db.prepare(` INSERT INTO character_quest_state (character_id, quest_id, state_value, progress_blob, started_at, completed_at) VALUES (?, ?, ?, ?, ?, ?) ON CONFLICT(character_id, quest_id) DO UPDATE SET state_value = excluded.state_value, progress_blob = excluded.progress_blob, started_at = excluded.started_at, completed_at = excluded.completed_at `).run( characterId | 0, String(questId), stateValue, JSON.stringify(progress ?? {}), startedAt, completedAt ); } isDialogueHeard (characterId, dialogueId, stepId) { const row = this.db.prepare(` SELECT 1 FROM character_dialogue_heard WHERE character_id = ? AND dialogue_id = ? AND step_id = ? `).get(characterId | 0, String(dialogueId), String(stepId)); return !!row; } markDialogueHeard (characterId, dialogueId, stepId) { this.db.prepare(` INSERT OR IGNORE INTO character_dialogue_heard (character_id, dialogue_id, step_id, heard_at) VALUES (?, ?, ?, ?) `).run(characterId | 0, String(dialogueId), String(stepId), Date.now()); } listDialogueHeard (characterId) { return this.db.prepare(` SELECT dialogue_id, step_id, heard_at FROM character_dialogue_heard WHERE character_id = ? `).all(characterId | 0); } isDialogueTriggerLatched (characterId, dialogueId, triggerId) { const row = this.db.prepare(` SELECT 1 FROM character_dialogue_triggers WHERE character_id = ? AND dialogue_id = ? AND trigger_id = ? `).get(characterId | 0, String(dialogueId), String(triggerId)); return !!row; } latchDialogueTrigger (characterId, dialogueId, triggerId) { this.db.prepare(` INSERT OR IGNORE INTO character_dialogue_triggers (character_id, dialogue_id, trigger_id, latched_at) VALUES (?, ?, ?, ?) `).run(characterId | 0, String(dialogueId), String(triggerId), Date.now()); } listDialogueTriggers (characterId) { return this.db.prepare(` SELECT dialogue_id, trigger_id, latched_at FROM character_dialogue_triggers WHERE character_id = ? `).all(characterId | 0); } appendQuestJournal (characterId, questId, stepId, text) { const trimmed = String(text ?? "").trim(); if (!trimmed) { return null; } const writtenAt = Date.now(); const result = this.db.prepare(` INSERT INTO character_quest_journal (character_id, quest_id, step_id, text, written_at) VALUES (?, ?, ?, ?, ?) `).run(characterId | 0, String(questId), String(stepId), trimmed, writtenAt); return { entryId: Number(result.lastInsertRowid), questId: String(questId), stepId: String(stepId), text: trimmed, writtenAt }; } listQuestJournal (characterId, questId = null) { if (questId) { return this.db.prepare(` SELECT entry_id, quest_id, step_id, text, written_at FROM character_quest_journal WHERE character_id = ? AND quest_id = ? ORDER BY written_at ASC, entry_id ASC `).all(characterId | 0, String(questId)); } return this.db.prepare(` SELECT entry_id, quest_id, step_id, text, written_at FROM character_quest_journal WHERE character_id = ? ORDER BY written_at ASC, entry_id ASC `).all(characterId | 0); } listFriendRecords (characterId) { return this.listAccountFriendRecords(this.accountIdOfCharacter(characterId)); } accountIdOfCharacter (characterId) { const row = this.getCharacter(characterId); return row?.account_id | 0; } getAccount (accountId) { return this.db.prepare(` SELECT account_id, auth_subject, username, email, is_admin FROM accounts WHERE account_id = ? `).get(accountId | 0) ?? null; } accountDisplayName (accountId) { const row = this.getAccount(accountId); if (!row) { return "Unknown"; } const name = String(row.username ?? "").trim(); return name || `Account ${row.account_id}`; } listAccountFriendRecords (accountId) { const id = accountId | 0; if (!id) { return []; } return this.db.prepare(` SELECT CASE WHEN account_id_a = ? THEN account_id_b ELSE account_id_a END AS account_id, created_at FROM account_friendships WHERE account_id_a = ? OR account_id_b = ? ORDER BY created_at ASC `).all(id, id, id); } areAccountFriends (accountId, otherAccountId) { const aId = accountId | 0; const bId = otherAccountId | 0; if (!aId || !bId) { return false; } if (aId === bId) { return true; } const [a, b] = pairIds(aId, bId); return !!this.db.prepare(` SELECT 1 FROM account_friendships WHERE account_id_a = ? AND account_id_b = ? `).get(a, b); } areFriends (characterId, otherId) { const left = this.getCharacter(characterId); const right = this.getCharacter(otherId); if (!left || !right) { return false; } return this.areAccountFriends(left.account_id, right.account_id); } insertAccountFriendship (accountId, otherId) { const [a, b] = pairIds(accountId, otherId); this.db.prepare(` INSERT OR IGNORE INTO account_friendships (account_id_a, account_id_b, created_at) VALUES (?, ?, ?) `).run(a, b, Date.now()); } insertFriendship (characterId, otherId) { const left = this.getCharacter(characterId); const right = this.getCharacter(otherId); if (!left || !right) { return; } this.insertAccountFriendship(left.account_id, right.account_id); } deleteAccountFriendship (accountId, otherId) { const [a, b] = pairIds(accountId, otherId); this.db.prepare(` DELETE FROM account_friendships WHERE account_id_a = ? AND account_id_b = ? `).run(a, b); } deleteFriendship (characterId, otherId) { const left = this.getCharacter(characterId); const right = this.getCharacter(otherId); if (!left || !right) { return; } this.deleteAccountFriendship(left.account_id, right.account_id); } listIncomingAccountRequests (accountId) { return this.db.prepare(` SELECT request_id, from_account_id, to_account_id, created_at FROM account_friend_requests WHERE to_account_id = ? ORDER BY created_at ASC `).all(accountId | 0); } listOutgoingAccountRequests (accountId) { return this.db.prepare(` SELECT request_id, from_account_id, to_account_id, created_at FROM account_friend_requests WHERE from_account_id = ? ORDER BY created_at ASC `).all(accountId | 0); } listIncomingRequests (characterId) { return this.listIncomingAccountRequests(this.accountIdOfCharacter(characterId)); } listOutgoingRequests (characterId) { return this.listOutgoingAccountRequests(this.accountIdOfCharacter(characterId)); } getAccountFriendRequest (fromAccountId, toAccountId) { return this.db.prepare(` SELECT * FROM account_friend_requests WHERE from_account_id = ? AND to_account_id = ? `).get(fromAccountId | 0, toAccountId | 0) ?? null; } getFriendRequest (fromId, toId) { const from = this.getCharacter(fromId); const to = this.getCharacter(toId); if (!from || !to) { return null; } return this.getAccountFriendRequest(from.account_id, to.account_id); } insertAccountFriendRequest (fromAccountId, toAccountId) { this.db.prepare(` INSERT INTO account_friend_requests (from_account_id, to_account_id, created_at) VALUES (?, ?, ?) `).run(fromAccountId | 0, toAccountId | 0, Date.now()); } insertFriendRequest (fromId, toId) { const from = this.getCharacter(fromId); const to = this.getCharacter(toId); if (!from || !to) { return; } this.insertAccountFriendRequest(from.account_id, to.account_id); } deleteAccountFriendRequest (fromAccountId, toAccountId) { this.db.prepare(` DELETE FROM account_friend_requests WHERE from_account_id = ? AND to_account_id = ? `).run(fromAccountId | 0, toAccountId | 0); } deleteFriendRequest (fromId, toId) { const from = this.getCharacter(fromId); const to = this.getCharacter(toId); if (!from || !to) { return; } this.deleteAccountFriendRequest(from.account_id, to.account_id); } /** Decayed kill counter at `now` (does not write). */ peekKillCounter (characterId, now = Date.now()) { const row = this.getCharacter(characterId); if (!row) { return 0; } const current = Math.max(0, Number(row.kill_counter) || 0); const at = row.kill_counter_at | 0; if (!(current > 0) || !at || now <= at) { return current; } return decayKillCounter(current, now - at).killCounter; } getKillCounterState (characterId) { const row = this.getCharacter(characterId); if (!row) { return { killCounter: 0, updatedAt: Date.now() }; } return { killCounter: Math.max(0, Number(row.kill_counter) || 0), updatedAt: row.kill_counter_at | 0 || Date.now() }; } setKillCounter (characterId, killCounter, updatedAt = Date.now()) { const value = Math.max(0, Number(killCounter) || 0); this.db.prepare(` UPDATE characters SET kill_counter = ?, kill_counter_at = ? WHERE character_id = ? `).run(value, updatedAt | 0, characterId | 0); return value; } addKillCounter (characterId, amount, now = Date.now()) { const state = this.getKillCounterState(characterId); const decayed = decayKillCounter(state.killCounter, Math.max(0, now - (state.updatedAt | 0))); const next = decayed.killCounter + Math.max(0, Number(amount) || 0); return this.setKillCounter(characterId, next, now); } getCowardiceForgivenHour (characterId) { const row = this.getCharacter(characterId); if (!row || row.cowardice_forgiven_hour == null) { return null; } return row.cowardice_forgiven_hour | 0; } setCowardiceForgivenHour (characterId, hourKey) { this.db.prepare(` UPDATE characters SET cowardice_forgiven_hour = ? WHERE character_id = ? `).run(hourKey | 0, characterId | 0); } listPersistentInRegion (minX, minY, maxX, maxY, dimensionId = "overworld") { const dim = String(dimensionId ?? "overworld"); return this.db.prepare(` SELECT entity_id, tile_x, tile_y, dimension_id, components_blob FROM persistent_entities WHERE tile_x >= ? AND tile_x <= ? AND tile_y >= ? AND tile_y <= ? AND dimension_id = ? `).all(minX, maxX, minY, maxY, dim); } replacePersistentInRegion (minX, minY, maxX, maxY, entities, dimensionId = "overworld") { const dim = String(dimensionId ?? "overworld"); this.db.exec("BEGIN"); try { this.db.prepare(` DELETE FROM persistent_entities WHERE tile_x >= ? AND tile_x <= ? AND tile_y >= ? AND tile_y <= ? AND dimension_id = ? `).run(minX, maxX, minY, maxY, dim); const insert = this.db.prepare(` INSERT INTO persistent_entities (entity_id, tile_x, tile_y, dimension_id, components_blob) VALUES (?, ?, ?, ?, ?) `); for (const entity of entities) { insert.run( entity.entityId, entity.tileX, entity.tileY, String(entity.dimensionId ?? dim), JSON.stringify(entity.payload?.components ?? entity.components) ); } this.db.exec("COMMIT"); } catch (error) { this.db.exec("ROLLBACK"); throw error; } } #ensureBag (characterId, starterSettings = null, creationClothing = null) { const existing = this.db.prepare("SELECT * FROM character_bags WHERE character_id = ?").get(characterId); if (existing) { return parseBag(existing, characterId); } const contents = buildStarterBagContents(starterSettings, creationClothing); 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(contents.slots), JSON.stringify(contents.equipment), 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) { const parsed = JSON.parse(row.skills_blob || "{}"); const skills = normalizePlayerSkills(parsed); // Persist floors (Health ≥ 5, others ≥ 1) for legacy under-leveled rows. if (this.#skillsBlobNeedsRewrite(parsed, skills)) { this.saveSkills(characterId, skills); } return skills; } if (this.getCharacter(characterId)) { this.#ensureBag(characterId); return this.getSkills(characterId); } return null; } /** True when stored blob is below the default minimum levels and must be rewritten. */ #skillsBlobNeedsRewrite (parsed, normalized) { const raw = parsed?.xp && typeof parsed.xp === "object" ? parsed.xp : parsed; if (!raw || typeof raw !== "object") { return true; } for (const skillId of Object.keys(normalized.xp)) { const stored = Math.max(0, Math.floor(Number(raw[skillId]) || 0)); if (stored < normalized.xp[skillId]) { return true; } } return false; } 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 ); } getSpells (characterId) { const row = this.db.prepare("SELECT spells_blob FROM character_bags WHERE character_id = ?").get(characterId); if (row) { return normalizePlayerSpells(JSON.parse(row.spells_blob || "{}")); } if (this.getCharacter(characterId)) { this.#ensureBag(characterId); return this.getSpells(characterId); } return null; } saveSpells (characterId, spells) { this.#ensureBag(characterId); this.db.prepare("UPDATE character_bags SET spells_blob = ? WHERE character_id = ?").run( JSON.stringify(storedPlayerSpells(spells)), 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, dimensionId = "overworld") { const dim = String(dimensionId ?? "overworld"); return this.db.prepare(` SELECT * FROM ground_items WHERE tile_x >= ? AND tile_x <= ? AND tile_y >= ? AND tile_y <= ? AND dimension_id = ? `).all(minX, maxX, minY, maxY, dim).map(parseGroundItem); } upsertGroundItem (item) { this.db.prepare(` INSERT INTO ground_items ( ground_item_id, tile_x, tile_y, dimension_id, 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, dimension_id = excluded.dimension_id, 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, String(item.dimensionId ?? "overworld"), 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, vault_character_id, origin_dimension_id, dest_dimension_id, legs_blob, leg_index ) 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, transit.vaultCharacterId ?? null, transit.originDimensionId ?? "overworld", transit.destDimensionId ?? "overworld", transit.legs != null ? JSON.stringify(transit.legs) : null, transit.legIndex | 0 ); 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 = ?, vault_character_id = ?, origin_dimension_id = ?, dest_dimension_id = ?, legs_blob = ?, leg_index = ? 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.vaultCharacterId ?? null, transit.originDimensionId ?? "overworld", transit.destDimensionId ?? "overworld", transit.legs != null ? JSON.stringify(transit.legs) : null, transit.legIndex | 0, transit.courierId ); } appendBankRestNotice (characterId, entry) { const qty = Math.max(1, entry.quantity | 0); this.db.prepare(` INSERT INTO bank_rest_notices (character_id, item_id, item_name, quantity, source_name, created_at) VALUES (?, ?, ?, ?, ?, ?) `).run( characterId, entry.itemId | 0, String(entry.itemName ?? "Item"), qty, String(entry.sourceName ?? "unknown source"), entry.createdAt ?? Date.now() ); } listBankRestNotices (characterId) { return this.db.prepare(` SELECT * FROM bank_rest_notices WHERE character_id = ? ORDER BY notice_id ASC `).all(characterId).map((row) => ({ noticeId: row.notice_id, characterId: row.character_id, itemId: row.item_id, itemName: row.item_name, quantity: row.quantity, sourceName: row.source_name, createdAt: row.created_at })); } clearBankRestNotices (characterId) { this.db.prepare("DELETE FROM bank_rest_notices WHERE character_id = ?").run(characterId); } insertHeldCourierDelivery (delivery) { const now = delivery.createdAt ?? Date.now(); const result = this.db.prepare(` INSERT INTO held_courier_deliveries ( character_id, sender_character_id, origin_tile_x, origin_tile_y, destination_kind, destination_character_id, vault_character_id, cargo_blob, created_at, origin_dimension_id ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) `).run( delivery.characterId, delivery.senderCharacterId, delivery.originTileX | 0, delivery.originTileY | 0, delivery.destinationKind, delivery.destinationCharacterId ?? null, delivery.vaultCharacterId ?? null, JSON.stringify(delivery.cargo), now, delivery.originDimensionId ?? "overworld" ); return Number(result.lastInsertRowid); } listHeldCourierDeliveries (characterId) { return this.db.prepare(` SELECT * FROM held_courier_deliveries WHERE character_id = ? ORDER BY hold_id ASC `).all(characterId).map(parseHeldCourierDelivery); } deleteHeldCourierDelivery (holdId) { this.db.prepare("DELETE FROM held_courier_deliveries WHERE hold_id = ?").run(holdId); } insertMarketOrder (order) { const result = this.db.prepare(` INSERT INTO market_orders ( character_id, side, item_id, quantity, limit_unit_price, escrow_coins, escrow_stack_blob, listing_key, created_at, status ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) `).run( order.characterId, order.side, order.itemId | 0, order.quantity | 0, order.limitUnitPrice | 0, order.escrowCoins | 0 || 0, order.escrowStack ? JSON.stringify(order.escrowStack) : null, order.listingKey ?? null, order.createdAt ?? Date.now(), order.status ?? "open" ); return this.getMarketOrder(Number(result.lastInsertRowid)); } getMarketOrder (orderId) { const row = this.db.prepare("SELECT * FROM market_orders WHERE order_id = ?").get(orderId); return row ? parseMarketOrder(row) : null; } updateMarketOrder (order) { this.db.prepare(` UPDATE market_orders SET quantity = ?, escrow_coins = ?, escrow_stack_blob = ?, status = ? WHERE order_id = ? `).run( order.quantity | 0, order.escrowCoins | 0 || 0, order.escrowStack ? JSON.stringify(order.escrowStack) : null, order.status, order.orderId ); return this.getMarketOrder(order.orderId); } listOpenMarketOrdersForCharacter (characterId) { return this.db.prepare(` SELECT * FROM market_orders WHERE character_id = ? AND status = 'open' ORDER BY created_at ASC, order_id ASC `).all(characterId).map(parseMarketOrder); } listOpenBuyOrdersForItem (itemId, listingKey = null) { if (listingKey) { return this.db.prepare(` SELECT * FROM market_orders WHERE status = 'open' AND side = 'buy' AND item_id = ? AND listing_key = ? ORDER BY limit_unit_price DESC, created_at ASC, order_id ASC `).all(itemId | 0, listingKey).map(parseMarketOrder); } return this.db.prepare(` SELECT * FROM market_orders WHERE status = 'open' AND side = 'buy' AND item_id = ? AND (listing_key IS NULL OR listing_key = '') ORDER BY limit_unit_price DESC, created_at ASC, order_id ASC `).all(itemId | 0).map(parseMarketOrder); } listOpenSellOrdersForItem (itemId, listingKey = null) { if (listingKey) { return this.db.prepare(` SELECT * FROM market_orders WHERE status = 'open' AND side = 'sell' AND item_id = ? AND listing_key = ? ORDER BY limit_unit_price ASC, created_at ASC, order_id ASC `).all(itemId | 0, listingKey).map(parseMarketOrder); } return this.db.prepare(` SELECT * FROM market_orders WHERE status = 'open' AND side = 'sell' AND item_id = ? AND (listing_key IS NULL OR listing_key = '') ORDER BY limit_unit_price ASC, created_at ASC, order_id ASC `).all(itemId | 0).map(parseMarketOrder); } listExpiredMarketOrders (createdBefore) { return this.db.prepare(` SELECT * FROM market_orders WHERE status = 'open' AND created_at < ? ORDER BY created_at ASC, order_id ASC `).all(createdBefore).map(parseMarketOrder); } insertMarketTrade (trade) { const result = this.db.prepare(` INSERT INTO market_trades ( buy_order_id, sell_order_id, item_id, quantity, unit_price, buyer_character_id, seller_character_id, created_at ) VALUES (?, ?, ?, ?, ?, ?, ?, ?) `).run( trade.buyOrderId, trade.sellOrderId, trade.itemId | 0, trade.quantity | 0, trade.unitPrice | 0, trade.buyerCharacterId, trade.sellerCharacterId, trade.createdAt ?? Date.now() ); return Number(result.lastInsertRowid); } upsertItemLastSale (sale) { this.db.prepare(` INSERT INTO item_last_sales (item_id, last_trade_price, last_trade_at, price_from) VALUES (?, ?, ?, ?) ON CONFLICT(item_id) DO UPDATE SET last_trade_price = excluded.last_trade_price, last_trade_at = excluded.last_trade_at, price_from = excluded.price_from `).run( sale.itemId | 0, sale.lastTradePrice | 0, sale.lastTradeAt, Math.round(Number(sale.priceFrom) || 0) ); } listItemLastSales () { return this.db.prepare("SELECT * FROM item_last_sales").all().map((row) => ({ itemId: row.item_id, lastTradePrice: row.last_trade_price, lastTradeAt: row.last_trade_at, priceFrom: row.price_from })); } 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, dimensionId: String(row.dimension_id ?? "overworld"), stack: JSON.parse(row.stack_blob), droppedAt: row.dropped_at, expiresAt: row.expires_at, source: row.source, droppedByCharacterId: row.dropped_by_character_id }; } function parseMarketOrder (row) { return { orderId: row.order_id, characterId: row.character_id, side: row.side, itemId: row.item_id, quantity: row.quantity, limitUnitPrice: row.limit_unit_price, escrowCoins: row.escrow_coins | 0, escrowStack: row.escrow_stack_blob ? JSON.parse(row.escrow_stack_blob) : null, listingKey: row.listing_key ?? null, createdAt: row.created_at, status: row.status }; } function bagContainsItem (inventory, equipment, itemId) { for (const stack of inventory ?? []) { if (stack?.itemId === itemId) { return true; } } for (const stack of Object.values(equipment?.slots ?? {})) { if (stack?.itemId === itemId) { return true; } } return false; } 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) { let legs = null; if (row.legs_blob) { try { legs = JSON.parse(row.legs_blob); } catch { legs = null; } } return { courierId: row.courier_id, senderCharacterId: row.sender_character_id, destinationKind: row.destination_kind, destinationCharacterId: row.destination_character_id, vaultCharacterId: row.vault_character_id ?? null, cargo: JSON.parse(row.cargo_blob), originX: row.origin_x, originY: row.origin_y, destX: row.dest_x, destY: row.dest_y, originDimensionId: row.origin_dimension_id ?? "overworld", destDimensionId: row.dest_dimension_id ?? "overworld", legs: Array.isArray(legs) ? legs : null, legIndex: row.leg_index | 0, traveled: row.traveled, status: row.status, entityId: row.entity_id, updatedAt: row.updated_at, createdAt: row.created_at }; } function parseHeldCourierDelivery (row) { return { holdId: row.hold_id, characterId: row.character_id, senderCharacterId: row.sender_character_id, originTileX: row.origin_tile_x, originTileY: row.origin_tile_y, originDimensionId: row.origin_dimension_id ?? "overworld", destinationKind: row.destination_kind, destinationCharacterId: row.destination_character_id, vaultCharacterId: row.vault_character_id, cargo: JSON.parse(row.cargo_blob), createdAt: row.created_at }; }