import { openDatabase } from "./database.js"; import { emptyEquipment, emptySlots, insertStack, insertStackGrowable, normalizeVault, normalizeInventorySlots, publicSlots, migrateBagCoins, migrateInheritanceCoins } from "../../../shared/items/inventory.js"; import { STARTER_ITEMS, ItemId, 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', is_dead INTEGER NOT NULL DEFAULT 0, 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 ); 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, 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 ); `; 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"); } this.#grantLanternsToExistingCharacters(); } #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 = { itemId: ItemId.lantern, quantity: 1 }; } else { const result = insertStack(inventory, { itemId: ItemId.lantern, 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; } 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 ); } 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; } 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); } } const equipment = emptyEquipment(); equipment.slots.offhand = { itemId: ItemId.lantern, quantity: 1 }; 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(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 ≥ 10, 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 ); } 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 ); } 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, 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.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) { return this.db.prepare(` SELECT * FROM market_orders WHERE status = 'open' AND side = 'buy' AND item_id = ? ORDER BY limit_unit_price DESC, created_at ASC, order_id ASC `).all(itemId | 0).map(parseMarketOrder); } listOpenSellOrdersForItem (itemId) { return this.db.prepare(` SELECT * FROM market_orders WHERE status = 'open' AND side = 'sell' AND item_id = ? 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, 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, 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) { 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 }; }