BlueGit

timfa questward

Files

questward / server / src / persistence / stateDb.js

stateDb.js

feat: branch quest steps and remember which steps were finished
timfa project_85689633_bot_760517ee9267c86ff1d6c3a0237abf7d · pushed by timfa · 4 weeks ago
25915c58 History
stateDb.js 61.4 KiB Download Raw
1import { openDatabase } from "./database.js";
2import { emptyEquipment, emptySlots, insertStack, insertStackGrowable, normalizeVault, normalizeInventorySlots, publicSlots, migrateBagCoins, migrateInheritanceCoins } from "../../../shared/items/inventory.js";
3import { attachOrigins, godsOrigin } from "../../../shared/items/origin.js";
4import { itemDefinition, ItemId } from "../../../shared/definitions/items.js";
5import { buildStarterBagContents } from "../../../shared/items/starterLoadout.js";
6import { normalizePlayerSkills, defaultPlayerSkills, storedSkillXp } from "../../../shared/skills/state.js";
7import { normalizePlayerSpells, defaultPlayerSpells, storedPlayerSpells } from "../../../shared/spells/state.js";
8import { decayKillCounter } from "../../../shared/combat/skull.js";
9import {
10 applyBountyDailyDecay,
11 bountyDecayDayKey,
12 bountyDecayDaysBetween
13} from "../../../shared/bounty/pricing.js";
14import { parseStoredQuestState, serializeQuestState } from "../../../shared/quests/questProgress.js";
15
16const STATE_SCHEMA = `
17CREATE TABLE IF NOT EXISTS accounts (
18 account_id INTEGER PRIMARY KEY AUTOINCREMENT,
19 auth_subject TEXT NOT NULL UNIQUE,
20 email TEXT,
21 is_admin INTEGER NOT NULL DEFAULT 0
22);
23
24CREATE TABLE IF NOT EXISTS surname_claims (
25 surname_normalized TEXT PRIMARY KEY,
26 account_id INTEGER NOT NULL
27);
28
29CREATE TABLE IF NOT EXISTS characters (
30 character_id INTEGER PRIMARY KEY AUTOINCREMENT,
31 account_id INTEGER NOT NULL,
32 first_name TEXT NOT NULL,
33 last_name TEXT NOT NULL,
34 first_normalized TEXT NOT NULL,
35 last_normalized TEXT NOT NULL,
36 sex TEXT NOT NULL,
37 tile_x INTEGER NOT NULL DEFAULT 8,
38 tile_y INTEGER NOT NULL DEFAULT 8,
39 gait TEXT NOT NULL DEFAULT 'walk',
40 is_dead INTEGER NOT NULL DEFAULT 0,
41 deletion_scheduled_at INTEGER,
42 hair_style TEXT,
43 hair_hue REAL NOT NULL DEFAULT 30,
44 hair_saturation REAL NOT NULL DEFAULT 0.55,
45 hair_darkness REAL NOT NULL DEFAULT 0.45
46);
47CREATE TABLE IF NOT EXISTS friend_requests (
48 request_id INTEGER PRIMARY KEY AUTOINCREMENT,
49 from_character_id INTEGER NOT NULL,
50 to_character_id INTEGER NOT NULL,
51 created_at INTEGER NOT NULL,
52 UNIQUE (from_character_id, to_character_id)
53);
54
55CREATE TABLE IF NOT EXISTS friendships (
56 character_id_a INTEGER NOT NULL,
57 character_id_b INTEGER NOT NULL,
58 created_at INTEGER NOT NULL,
59 PRIMARY KEY (character_id_a, character_id_b)
60);
61
62CREATE TABLE IF NOT EXISTS persistent_entities (
63 entity_id INTEGER PRIMARY KEY,
64 tile_x INTEGER NOT NULL,
65 tile_y INTEGER NOT NULL,
66 dimension_id TEXT NOT NULL DEFAULT 'overworld',
67 components_blob TEXT NOT NULL
68);
69
70CREATE TABLE IF NOT EXISTS character_bags (
71 character_id INTEGER PRIMARY KEY,
72 inventory_blob TEXT NOT NULL,
73 equipment_blob TEXT NOT NULL,
74 currency INTEGER NOT NULL DEFAULT 0,
75 vault_blob TEXT NOT NULL,
76 vault_currency INTEGER NOT NULL DEFAULT 0,
77 skills_blob TEXT NOT NULL DEFAULT '{}'
78);
79
80CREATE TABLE IF NOT EXISTS account_inheritance (
81 account_id INTEGER PRIMARY KEY,
82 slots_blob TEXT NOT NULL,
83 currency INTEGER NOT NULL DEFAULT 0
84);
85
86CREATE TABLE IF NOT EXISTS ground_items (
87 ground_item_id INTEGER PRIMARY KEY,
88 tile_x INTEGER NOT NULL,
89 tile_y INTEGER NOT NULL,
90 dimension_id TEXT NOT NULL DEFAULT 'overworld',
91 stack_blob TEXT NOT NULL,
92 dropped_at INTEGER NOT NULL,
93 expires_at INTEGER NOT NULL,
94 source TEXT NOT NULL,
95 dropped_by_character_id INTEGER
96);
97
98CREATE TABLE IF NOT EXISTS courier_transits (
99 courier_id INTEGER PRIMARY KEY AUTOINCREMENT,
100 sender_character_id INTEGER NOT NULL,
101 destination_kind TEXT NOT NULL,
102 destination_character_id INTEGER,
103 cargo_blob TEXT NOT NULL,
104 origin_x REAL NOT NULL,
105 origin_y REAL NOT NULL,
106 dest_x REAL NOT NULL,
107 dest_y REAL NOT NULL,
108 origin_dimension_id TEXT NOT NULL DEFAULT 'overworld',
109 dest_dimension_id TEXT NOT NULL DEFAULT 'overworld',
110 legs_blob TEXT,
111 leg_index INTEGER NOT NULL DEFAULT 0,
112 traveled REAL NOT NULL DEFAULT 0,
113 status TEXT NOT NULL,
114 entity_id INTEGER,
115 updated_at INTEGER NOT NULL,
116 created_at INTEGER NOT NULL
117);
118
119CREATE TABLE IF NOT EXISTS market_orders (
120 order_id INTEGER PRIMARY KEY AUTOINCREMENT,
121 character_id INTEGER NOT NULL,
122 side TEXT NOT NULL,
123 item_id INTEGER NOT NULL,
124 quantity INTEGER NOT NULL,
125 limit_unit_price INTEGER NOT NULL,
126 escrow_coins INTEGER NOT NULL DEFAULT 0,
127 escrow_stack_blob TEXT,
128 listing_key TEXT,
129 created_at INTEGER NOT NULL,
130 status TEXT NOT NULL
131);
132
133CREATE TABLE IF NOT EXISTS market_trades (
134 trade_id INTEGER PRIMARY KEY AUTOINCREMENT,
135 buy_order_id INTEGER NOT NULL,
136 sell_order_id INTEGER NOT NULL,
137 item_id INTEGER NOT NULL,
138 quantity INTEGER NOT NULL,
139 unit_price INTEGER NOT NULL,
140 buyer_character_id INTEGER NOT NULL,
141 seller_character_id INTEGER NOT NULL,
142 created_at INTEGER NOT NULL
143);
144
145CREATE TABLE IF NOT EXISTS item_last_sales (
146 item_id INTEGER PRIMARY KEY,
147 last_trade_price INTEGER NOT NULL,
148 last_trade_at INTEGER NOT NULL,
149 price_from INTEGER NOT NULL
150);
151
152CREATE TABLE IF NOT EXISTS bank_rest_notices (
153 notice_id INTEGER PRIMARY KEY AUTOINCREMENT,
154 character_id INTEGER NOT NULL,
155 item_id INTEGER NOT NULL,
156 item_name TEXT NOT NULL,
157 quantity INTEGER NOT NULL,
158 source_name TEXT NOT NULL,
159 created_at INTEGER NOT NULL
160);
161
162CREATE TABLE IF NOT EXISTS held_courier_deliveries (
163 hold_id INTEGER PRIMARY KEY AUTOINCREMENT,
164 character_id INTEGER NOT NULL,
165 sender_character_id INTEGER NOT NULL,
166 origin_tile_x INTEGER NOT NULL,
167 origin_tile_y INTEGER NOT NULL,
168 origin_dimension_id TEXT NOT NULL DEFAULT 'overworld',
169 destination_kind TEXT NOT NULL,
170 destination_character_id INTEGER,
171 vault_character_id INTEGER,
172 cargo_blob TEXT NOT NULL,
173 created_at INTEGER NOT NULL
174);
175
176CREATE TABLE IF NOT EXISTS character_bounties (
177 character_id INTEGER PRIMARY KEY,
178 amount INTEGER NOT NULL DEFAULT 0,
179 last_decay_day TEXT
180);
181
182CREATE TABLE IF NOT EXISTS character_quest_state (
183 character_id INTEGER NOT NULL,
184 quest_id TEXT NOT NULL,
185 state_value TEXT NOT NULL,
186 progress_blob TEXT NOT NULL DEFAULT '{}',
187 started_at INTEGER,
188 completed_at INTEGER,
189 PRIMARY KEY (character_id, quest_id)
190);
191
192CREATE TABLE IF NOT EXISTS character_dialogue_heard (
193 character_id INTEGER NOT NULL,
194 dialogue_id TEXT NOT NULL,
195 step_id TEXT NOT NULL,
196 heard_at INTEGER NOT NULL,
197 PRIMARY KEY (character_id, dialogue_id, step_id)
198);
199
200CREATE TABLE IF NOT EXISTS character_quest_journal (
201 entry_id INTEGER PRIMARY KEY AUTOINCREMENT,
202 character_id INTEGER NOT NULL,
203 quest_id TEXT NOT NULL,
204 step_id TEXT NOT NULL,
205 text TEXT NOT NULL,
206 written_at INTEGER NOT NULL
207);
208
209CREATE TABLE IF NOT EXISTS character_dialogue_triggers (
210 character_id INTEGER NOT NULL,
211 dialogue_id TEXT NOT NULL,
212 trigger_id TEXT NOT NULL,
213 latched_at INTEGER NOT NULL,
214 PRIMARY KEY (character_id, dialogue_id, trigger_id)
215);
216
217CREATE TABLE IF NOT EXISTS character_visited_dimensions (
218 character_id INTEGER NOT NULL,
219 dimension_id TEXT NOT NULL,
220 first_visited_at INTEGER NOT NULL,
221 PRIMARY KEY (character_id, dimension_id)
222);
223`;
224
225export class StateDatabase
226{
227 constructor(filePath)
228 {
229 this.db = openDatabase(filePath);
230 this.db.exec(STATE_SCHEMA);
231 this.#migrateSchema();
232 }
233
234 #migrateSchema ()
235 {
236 const bagColumns = this.db.prepare("PRAGMA table_info(character_bags)").all();
237 if (!bagColumns.some((column) => column.name === "skills_blob"))
238 {
239 this.db.exec("ALTER TABLE character_bags ADD COLUMN skills_blob TEXT NOT NULL DEFAULT '{}'");
240 }
241 const characterColumns = this.db.prepare("PRAGMA table_info(characters)").all();
242 if (!characterColumns.some((column) => column.name === "is_dead"))
243 { this.db.exec("ALTER TABLE characters ADD COLUMN is_dead INTEGER NOT NULL DEFAULT 0"); }
244 if (!characterColumns.some((column) => column.name === "hair_style"))
245 {
246 this.db.exec("ALTER TABLE characters ADD COLUMN hair_style TEXT");
247 this.db.exec("ALTER TABLE characters ADD COLUMN hair_hue REAL NOT NULL DEFAULT 30");
248 this.db.exec("ALTER TABLE characters ADD COLUMN hair_saturation REAL NOT NULL DEFAULT 0.55");
249 this.db.exec("ALTER TABLE characters ADD COLUMN hair_darkness REAL NOT NULL DEFAULT 0.45");
250 }
251 if (!characterColumns.some((column) => column.name === "dimension_id"))
252 { this.db.exec("ALTER TABLE characters ADD COLUMN dimension_id TEXT NOT NULL DEFAULT 'overworld'"); }
253 this.db.exec(`
254 CREATE TABLE IF NOT EXISTS character_visited_dimensions (
255 character_id INTEGER NOT NULL,
256 dimension_id TEXT NOT NULL,
257 first_visited_at INTEGER NOT NULL,
258 PRIMARY KEY (character_id, dimension_id)
259 )
260 `);
261 const courierColumns = this.db.prepare("PRAGMA table_info(courier_transits)").all();
262 if (!courierColumns.some((column) => column.name === "vault_character_id"))
263 { this.db.exec("ALTER TABLE courier_transits ADD COLUMN vault_character_id INTEGER"); }
264 if (!courierColumns.some((column) => column.name === "origin_dimension_id"))
265 { this.db.exec("ALTER TABLE courier_transits ADD COLUMN origin_dimension_id TEXT NOT NULL DEFAULT 'overworld'"); }
266 if (!courierColumns.some((column) => column.name === "dest_dimension_id"))
267 { this.db.exec("ALTER TABLE courier_transits ADD COLUMN dest_dimension_id TEXT NOT NULL DEFAULT 'overworld'"); }
268 if (!courierColumns.some((column) => column.name === "legs_blob"))
269 { this.db.exec("ALTER TABLE courier_transits ADD COLUMN legs_blob TEXT"); }
270 if (!courierColumns.some((column) => column.name === "leg_index"))
271 { this.db.exec("ALTER TABLE courier_transits ADD COLUMN leg_index INTEGER NOT NULL DEFAULT 0"); }
272 const heldCourierColumns = this.db.prepare("PRAGMA table_info(held_courier_deliveries)").all();
273 if (!heldCourierColumns.some((column) => column.name === "origin_dimension_id"))
274 { this.db.exec("ALTER TABLE held_courier_deliveries ADD COLUMN origin_dimension_id TEXT NOT NULL DEFAULT 'overworld'"); }
275 if (!bagColumns.some((column) => column.name === "spells_blob"))
276 {
277 this.db.exec("ALTER TABLE character_bags ADD COLUMN spells_blob TEXT NOT NULL DEFAULT '{}'");
278 }
279 if (!characterColumns.some((column) => column.name === "health_current"))
280 { this.db.exec("ALTER TABLE characters ADD COLUMN health_current INTEGER"); }
281 const accountColumns = this.db.prepare("PRAGMA table_info(accounts)").all();
282 if (!accountColumns.some((column) => column.name === "username"))
283 { this.db.exec("ALTER TABLE accounts ADD COLUMN username TEXT"); }
284 if (!characterColumns.some((column) => column.name === "kill_counter"))
285 { this.db.exec("ALTER TABLE characters ADD COLUMN kill_counter REAL NOT NULL DEFAULT 0"); }
286 if (!characterColumns.some((column) => column.name === "kill_counter_at"))
287 { this.db.exec("ALTER TABLE characters ADD COLUMN kill_counter_at INTEGER"); }
288 if (!characterColumns.some((column) => column.name === "cowardice_forgiven_hour"))
289 { this.db.exec("ALTER TABLE characters ADD COLUMN cowardice_forgiven_hour INTEGER"); }
290 if (!characterColumns.some((column) => column.name === "respawn_altar_x"))
291 {
292 this.db.exec("ALTER TABLE characters ADD COLUMN respawn_altar_x INTEGER");
293 this.db.exec("ALTER TABLE characters ADD COLUMN respawn_altar_y INTEGER");
294 this.db.exec("ALTER TABLE characters ADD COLUMN respawn_altar_dimension_id TEXT");
295 }
296 const groundColumns = this.db.prepare("PRAGMA table_info(ground_items)").all();
297 if (!groundColumns.some((column) => column.name === "dimension_id"))
298 { this.db.exec("ALTER TABLE ground_items ADD COLUMN dimension_id TEXT NOT NULL DEFAULT 'overworld'"); }
299 const persistentColumns = this.db.prepare("PRAGMA table_info(persistent_entities)").all();
300 if (!persistentColumns.some((column) => column.name === "dimension_id"))
301 { this.db.exec("ALTER TABLE persistent_entities ADD COLUMN dimension_id TEXT NOT NULL DEFAULT 'overworld'"); }
302 const marketColumns = this.db.prepare("PRAGMA table_info(market_orders)").all();
303 if (!marketColumns.some((column) => column.name === "listing_key"))
304 { this.db.exec("ALTER TABLE market_orders ADD COLUMN listing_key TEXT"); }
305 const bountyColumns = this.db.prepare("PRAGMA table_info(character_bounties)").all();
306 if (!bountyColumns.some((column) => column.name === "last_decay_day"))
307 { this.db.exec("ALTER TABLE character_bounties ADD COLUMN last_decay_day TEXT"); }
308 this.db.exec(`
309 CREATE TABLE IF NOT EXISTS account_friend_requests (
310 request_id INTEGER PRIMARY KEY AUTOINCREMENT,
311 from_account_id INTEGER NOT NULL,
312 to_account_id INTEGER NOT NULL,
313 created_at INTEGER NOT NULL,
314 UNIQUE (from_account_id, to_account_id)
315 )
316 `);
317 this.db.exec(`
318 CREATE TABLE IF NOT EXISTS account_friendships (
319 account_id_a INTEGER NOT NULL,
320 account_id_b INTEGER NOT NULL,
321 created_at INTEGER NOT NULL,
322 PRIMARY KEY (account_id_a, account_id_b)
323 )
324 `);
325 this.#migrateCharacterFriendsToAccounts();
326 this.db.exec(`
327 CREATE TABLE IF NOT EXISTS auth_sessions (
328 session_id TEXT PRIMARY KEY,
329 auth_subject TEXT NOT NULL,
330 username TEXT,
331 email TEXT,
332 access_token TEXT,
333 expires_at INTEGER NOT NULL
334 )
335 `);
336 this.db.exec(`
337 CREATE INDEX IF NOT EXISTS auth_sessions_subject ON auth_sessions(auth_subject)
338 `);
339 this.#grantLanternsToExistingCharacters();
340 }
341
342 /** One-time: lift legacy character friendships / requests to account scope. */
343 #migrateCharacterFriendsToAccounts ()
344 {
345 const legacyFriends = this.db.prepare("SELECT character_id_a, character_id_b, created_at FROM friendships").all();
346 const insertFriend = this.db.prepare(`
347 INSERT OR IGNORE INTO account_friendships (account_id_a, account_id_b, created_at)
348 VALUES (?, ?, ?)
349 `);
350 for (const row of legacyFriends)
351 {
352 const a = this.getCharacter(row.character_id_a);
353 const b = this.getCharacter(row.character_id_b);
354 if (!a || !b || a.account_id === b.account_id)
355 { continue; }
356 const [idA, idB] = pairIds(a.account_id, b.account_id);
357 insertFriend.run(idA, idB, row.created_at | 0 || Date.now());
358 }
359 const legacyRequests = this.db.prepare(`
360 SELECT from_character_id, to_character_id, created_at FROM friend_requests
361 `).all();
362 const insertReq = this.db.prepare(`
363 INSERT OR IGNORE INTO account_friend_requests (from_account_id, to_account_id, created_at)
364 VALUES (?, ?, ?)
365 `);
366 for (const row of legacyRequests)
367 {
368 const from = this.getCharacter(row.from_character_id);
369 const to = this.getCharacter(row.to_character_id);
370 if (!from || !to || from.account_id === to.account_id)
371 { continue; }
372 insertReq.run(from.account_id, to.account_id, row.created_at | 0 || Date.now());
373 }
374 if (legacyFriends.length || legacyRequests.length)
375 {
376 this.db.exec("DELETE FROM friendships");
377 this.db.exec("DELETE FROM friend_requests");
378 }
379 }
380
381 createAuthSession ({ sessionId, authSubject, username = null, email = null, accessToken = null, expiresAtMs })
382 {
383 this.db.prepare(`
384 INSERT INTO auth_sessions (session_id, auth_subject, username, email, access_token, expires_at)
385 VALUES (?, ?, ?, ?, ?, ?)
386 `).run(sessionId, authSubject, username, email, accessToken, expiresAtMs);
387 }
388
389 getAuthSession (sessionId)
390 {
391 return this.db.prepare(`
392 SELECT session_id, auth_subject, username, email, access_token, expires_at
393 FROM auth_sessions
394 WHERE session_id = ?
395 `).get(sessionId) ?? null;
396 }
397
398 deleteAuthSession (sessionId)
399 {
400 if (!sessionId)
401 { return; }
402 this.db.prepare("DELETE FROM auth_sessions WHERE session_id = ?").run(sessionId);
403 }
404
405 #grantLanternsToExistingCharacters ()
406 {
407 const def = itemDefinition(ItemId.lantern);
408 if (!def)
409 { return; }
410 const rows = this.db.prepare("SELECT character_id, inventory_blob, equipment_blob FROM character_bags").all();
411 const update = this.db.prepare(`
412 UPDATE character_bags
413 SET inventory_blob = ?, equipment_blob = ?
414 WHERE character_id = ?
415 `);
416 for (const row of rows)
417 {
418 const inventory = JSON.parse(row.inventory_blob || "[]");
419 const equipment = JSON.parse(row.equipment_blob || "{}");
420 if (bagContainsItem(inventory, equipment, ItemId.lantern))
421 { continue; }
422 if (!equipment.slots?.offhand)
423 {
424 equipment.slots = equipment.slots ?? {};
425 equipment.slots.offhand = attachOrigins({
426 itemId: ItemId.lantern,
427 quantity: 1,
428 origins: [{ origin: godsOrigin(), quantity: 1 }]
429 });
430 }
431 else
432 {
433 const result = insertStack(inventory, attachOrigins({
434 itemId: ItemId.lantern,
435 quantity: 1,
436 origins: [{ origin: godsOrigin(), quantity: 1 }]
437 }), def);
438 if (!result.ok)
439 { continue; }
440 inventory.splice(0, inventory.length, ...result.slots);
441 }
442 update.run(JSON.stringify(inventory), JSON.stringify(equipment), row.character_id);
443 }
444 }
445
446 getAccountBySubject (authSubject)
447 {
448 return this.db.prepare("SELECT * FROM accounts WHERE auth_subject = ?").get(authSubject) ?? null;
449 }
450
451 countAccounts ()
452 {
453 return this.db.prepare("SELECT COUNT(*) AS count FROM accounts").get().count;
454 }
455
456 countGameAdmins ()
457 {
458 return this.db.prepare("SELECT COUNT(*) AS count FROM accounts WHERE is_admin = 1").get().count;
459 }
460
461 findAccountByUsername (username)
462 {
463 const normalized = String(username ?? "").trim().toLowerCase();
464 if (!normalized)
465 { return null; }
466 return this.db.prepare(`
467 SELECT * FROM accounts
468 WHERE lower(username) = ?
469 ORDER BY account_id ASC
470 LIMIT 1
471 `).get(normalized) ?? null;
472 }
473
474 listGameAdmins ()
475 {
476 return this.db.prepare(`
477 SELECT account_id, auth_subject, username, email, is_admin
478 FROM accounts
479 WHERE is_admin = 1
480 ORDER BY account_id ASC
481 `).all();
482 }
483
484 #syncAccountProfile (accountId, { email, username })
485 {
486 const fields = [];
487 const values = [];
488 // Never blank a stored email when auth stops sending one.
489 if (typeof email === "string" && email.trim() !== "")
490 {
491 fields.push("email = ?");
492 values.push(email.trim());
493 }
494 if (username != null)
495 {
496 fields.push("username = ?");
497 values.push(username);
498 }
499 if (!fields.length)
500 { return; }
501 values.push(accountId);
502 this.db.prepare(`UPDATE accounts SET ${fields.join(", ")} WHERE account_id = ?`).run(...values);
503 }
504
505 resolveAccountFromAuth ({ authSubject, email = null, username = null })
506 {
507 const existing = this.getAccountBySubject(authSubject);
508 if (existing)
509 {
510 this.#syncAccountProfile(existing.account_id, { email, username });
511 return this.getAccountBySubject(authSubject);
512 }
513 const isFirstAccount = this.countAccounts() === 0;
514 return this.createAccount(authSubject, isFirstAccount, email, username);
515 }
516
517 setGameAdmin (authSubject, isAdmin)
518 {
519 const account = this.getAccountBySubject(authSubject);
520 if (!account)
521 {
522 const error = new Error("Account not found.");
523 error.userFacing = true;
524 throw error;
525 }
526 if (!isAdmin && account.is_admin && this.countGameAdmins() <= 1)
527 {
528 const error = new Error("Cannot remove the last game admin.");
529 error.userFacing = true;
530 throw error;
531 }
532 this.db.prepare("UPDATE accounts SET is_admin = ? WHERE auth_subject = ?").run(isAdmin ? 1 : 0, authSubject);
533 return this.getAccountBySubject(authSubject);
534 }
535
536 grantGameAdminByUsername (username)
537 {
538 const account = this.findAccountByUsername(username);
539 if (!account)
540 {
541 const error = new Error("No account with that username has signed in yet.");
542 error.userFacing = true;
543 throw error;
544 }
545 return this.setGameAdmin(account.auth_subject, true);
546 }
547
548 createAccount (authSubject, isAdmin, email = null, username = null)
549 {
550 this.db.prepare(`
551 INSERT INTO accounts (auth_subject, email, username, is_admin)
552 VALUES (?, ?, ?, ?)
553 `).run(
554 authSubject,
555 email,
556 username,
557 isAdmin ? 1 : 0
558 );
559 return this.getAccountBySubject(authSubject);
560 }
561
562 ensureAccount (authSubject, isAdmin, email = null, username = null)
563 {
564 const existing = this.getAccountBySubject(authSubject);
565 if (existing)
566 {
567 this.#syncAccountProfile(existing.account_id, { email, username });
568 return existing;
569 }
570
571 return this.createAccount(authSubject, isAdmin, email, username);
572 }
573
574 listCharacters (accountId)
575 {
576 return this.db.prepare(`
577 SELECT * FROM characters
578 WHERE account_id = ?
579 ORDER BY character_id ASC
580 `).all(accountId);
581 }
582
583 getCharacter (characterId)
584 {
585 return this.db.prepare("SELECT * FROM characters WHERE character_id = ?").get(characterId) ?? null;
586 }
587
588 /** All character ids (for world-settings fan-out such as initial quests). */
589 listAllCharacterIds ()
590 {
591 return this.db.prepare("SELECT character_id FROM characters").all()
592 .map((row) => row.character_id | 0)
593 .filter((id) => id > 0);
594 }
595
596 findSurnameClaim (surnameNormalized)
597 {
598 return this.db.prepare("SELECT * FROM surname_claims WHERE surname_normalized = ?").get(surnameNormalized) ?? null;
599 }
600
601 createCharacter (accountId, firstName, lastName, sex, tileX, tileY, hair = null, starterSettings = null, creationClothing = null)
602 {
603 const firstNormalized = normalizeName(firstName);
604 const lastNormalized = normalizeName(lastName);
605 const hairStyle = hair?.style ?? null;
606 const hairHue = Number(hair?.hue) || 30;
607 const hairSaturation = Number(hair?.saturation);
608 const hairDarkness = Number(hair?.darkness);
609 this.db.exec("BEGIN");
610 try
611 {
612 this.db.prepare(`
613 INSERT INTO characters (
614 account_id, first_name, last_name, first_normalized, last_normalized, sex, tile_x, tile_y,
615 dimension_id, hair_style, hair_hue, hair_saturation, hair_darkness
616 ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, 'overworld', ?, ?, ?, ?)
617 `).run(
618 accountId,
619 firstName,
620 lastName,
621 firstNormalized,
622 lastNormalized,
623 sex,
624 tileX,
625 tileY,
626 hairStyle,
627 hairHue,
628 Number.isFinite(hairSaturation) ? hairSaturation : 0.55,
629 Number.isFinite(hairDarkness) ? hairDarkness : 0.45
630 );
631 const characterId = Number(this.db.prepare("SELECT last_insert_rowid() AS id").get().id);
632
633 const claim = this.findSurnameClaim(lastNormalized);
634 if (!claim)
635 {
636 this.db.prepare("INSERT INTO surname_claims (surname_normalized, account_id) VALUES (?, ?)").run(
637 lastNormalized,
638 accountId
639 );
640 }
641
642 this.db.exec("COMMIT");
643 this.#ensureBag(characterId, starterSettings, creationClothing);
644 return this.getCharacter(characterId);
645 }
646 catch (error)
647 {
648 this.db.exec("ROLLBACK");
649 throw error;
650 }
651 }
652
653 updateCharacterHair (characterId, { style, hue, saturation, darkness })
654 {
655 this.db.prepare(`
656 UPDATE characters
657 SET hair_style = ?, hair_hue = ?, hair_saturation = ?, hair_darkness = ?
658 WHERE character_id = ?
659 `).run(
660 style ?? null,
661 Number(hue) || 30,
662 Number.isFinite(Number(saturation)) ? Number(saturation) : 0.55,
663 Number.isFinite(Number(darkness)) ? Number(darkness) : 0.45,
664 characterId | 0
665 );
666 return this.getCharacter(characterId);
667 }
668
669 scheduleDeletion (characterId, at)
670 {
671 this.db.prepare("UPDATE characters SET deletion_scheduled_at = ? WHERE character_id = ?").run(at, characterId);
672 }
673
674 cancelDeletion (characterId)
675 {
676 this.db.prepare("UPDATE characters SET deletion_scheduled_at = NULL WHERE character_id = ?").run(characterId);
677 }
678
679 deleteCharacter (characterId)
680 {
681 const character = this.getCharacter(characterId);
682 if (!character)
683 { return; }
684
685 this.db.exec("BEGIN");
686 try
687 {
688 this.#moveGoodsToInheritance(character);
689 this.db.prepare("DELETE FROM character_bags WHERE character_id = ?").run(characterId);
690 this.db.prepare("DELETE FROM characters WHERE character_id = ?").run(characterId);
691 this.db.prepare("DELETE FROM character_bounties WHERE character_id = ?").run(characterId);
692 // Account friendships persist; only clear legacy character rows if any remain.
693 this.db.prepare("DELETE FROM friend_requests WHERE from_character_id = ? OR to_character_id = ?").run(
694 characterId,
695 characterId
696 );
697 this.db.prepare("DELETE FROM friendships WHERE character_id_a = ? OR character_id_b = ?").run(
698 characterId,
699 characterId
700 );
701 const remaining = this.db.prepare(`
702 SELECT COUNT(*) AS count FROM characters
703 WHERE account_id = ? AND last_normalized = ?
704 `).get(character.account_id, character.last_normalized);
705 if (!remaining.count)
706 {
707 this.db.prepare("DELETE FROM surname_claims WHERE surname_normalized = ? AND account_id = ?").run(
708 character.last_normalized,
709 character.account_id
710 );
711 }
712 this.db.exec("COMMIT");
713 }
714 catch (error)
715 {
716 this.db.exec("ROLLBACK");
717 throw error;
718 }
719 }
720
721 saveCharacterPosition (characterId, tileX, tileY, gait, dimensionId = "overworld")
722 {
723 this.db.prepare(`
724 UPDATE characters SET tile_x = ?, tile_y = ?, gait = ?, dimension_id = ?
725 WHERE character_id = ?
726 `).run(
727 tileX,
728 tileY,
729 gait,
730 String(dimensionId ?? "overworld"),
731 characterId
732 );
733 }
734
735 getCharacterRespawnAltar (characterId)
736 {
737 const row = this.db.prepare(`
738 SELECT respawn_altar_x, respawn_altar_y, respawn_altar_dimension_id
739 FROM characters WHERE character_id = ?
740 `).get(characterId | 0);
741 if (!row || row.respawn_altar_x == null || row.respawn_altar_y == null)
742 { return null; }
743 return {
744 tileX: row.respawn_altar_x | 0,
745 tileY: row.respawn_altar_y | 0,
746 dimensionId: String(row.respawn_altar_dimension_id ?? "overworld")
747 };
748 }
749
750 setCharacterRespawnAltar (characterId, tileX, tileY, dimensionId = "overworld")
751 {
752 this.db.prepare(`
753 UPDATE characters
754 SET respawn_altar_x = ?, respawn_altar_y = ?, respawn_altar_dimension_id = ?
755 WHERE character_id = ?
756 `).run(tileX | 0, tileY | 0, String(dimensionId ?? "overworld"), characterId | 0);
757 }
758
759 markDimensionVisited (characterId, dimensionId, now = Date.now())
760 {
761 const dim = String(dimensionId ?? "overworld");
762 this.db.prepare(`
763 INSERT INTO character_visited_dimensions (character_id, dimension_id, first_visited_at)
764 VALUES (?, ?, ?)
765 ON CONFLICT(character_id, dimension_id) DO NOTHING
766 `).run(characterId | 0, dim, now);
767 }
768
769 hasVisitedDimension (characterId, dimensionId)
770 {
771 const row = this.db.prepare(`
772 SELECT 1 AS ok FROM character_visited_dimensions
773 WHERE character_id = ? AND dimension_id = ?
774 `).get(characterId | 0, String(dimensionId ?? "overworld"));
775 return !!row;
776 }
777
778 listVisitedDimensions (characterId)
779 {
780 return this.db.prepare(`
781 SELECT dimension_id, first_visited_at
782 FROM character_visited_dimensions
783 WHERE character_id = ?
784 ORDER BY first_visited_at ASC
785 `).all(characterId | 0).map((row) => ({
786 dimensionId: row.dimension_id,
787 firstVisitedAt: row.first_visited_at
788 }));
789 }
790
791 saveCharacterHealth (characterId, current)
792 {
793 this.db.prepare("UPDATE characters SET health_current = ? WHERE character_id = ?").run(
794 Math.max(0, current | 0),
795 characterId
796 );
797 }
798
799 setCharacterDead (characterId, isDead)
800 {
801 this.db.prepare("UPDATE characters SET is_dead = ? WHERE character_id = ?").run(
802 isDead ? 1 : 0,
803 characterId
804 );
805 }
806
807 listDueDeletions (now)
808 {
809 return this.db.prepare(`
810 SELECT * FROM characters
811 WHERE deletion_scheduled_at IS NOT NULL AND deletion_scheduled_at <= ?
812 `).all(now);
813 }
814
815 getCharacterByName (firstName, lastName)
816 {
817 return this.db.prepare(`
818 SELECT * FROM characters
819 WHERE first_normalized = ? AND last_normalized = ?
820 `).get(normalizeName(firstName), normalizeName(lastName)) ?? null;
821 }
822
823 /**
824 * Admin gift search: match first name, last name, "first last", or account username.
825 * @param {string} query
826 * @param {number} [limit]
827 */
828 searchCharactersForGift (query, limit = 40)
829 {
830 const raw = String(query ?? "").trim();
831 if (!raw)
832 { return []; }
833 const cap = Math.min(100, Math.max(1, limit | 0 || 40));
834 const needle = `%${normalizeName(raw).replace(/%/g, "")}%`;
835 const parts = raw.split(/\s+/u).filter(Boolean);
836 const firstPart = parts[0] ? normalizeName(parts[0]) : "";
837 const lastPart = parts.length >= 2 ? normalizeName(parts.slice(1).join(" ")) : "";
838
839 if (parts.length >= 2 && firstPart && lastPart)
840 {
841 return this.db.prepare(`
842 SELECT
843 c.character_id,
844 c.first_name,
845 c.last_name,
846 c.account_id,
847 a.username AS account_username
848 FROM characters c
849 LEFT JOIN accounts a ON a.account_id = c.account_id
850 WHERE (c.first_normalized = ? AND c.last_normalized = ?)
851 OR c.first_normalized LIKE ?
852 OR c.last_normalized LIKE ?
853 OR lower(COALESCE(a.username, '')) LIKE ?
854 OR (c.first_normalized || ' ' || c.last_normalized) LIKE ?
855 ORDER BY c.last_normalized ASC, c.first_normalized ASC, c.character_id ASC
856 LIMIT ?
857 `).all(firstPart, lastPart, needle, needle, needle, needle, cap);
858 }
859
860 return this.db.prepare(`
861 SELECT
862 c.character_id,
863 c.first_name,
864 c.last_name,
865 c.account_id,
866 a.username AS account_username
867 FROM characters c
868 LEFT JOIN accounts a ON a.account_id = c.account_id
869 WHERE c.first_normalized LIKE ?
870 OR c.last_normalized LIKE ?
871 OR lower(COALESCE(a.username, '')) LIKE ?
872 OR (c.first_normalized || ' ' || c.last_normalized) LIKE ?
873 ORDER BY c.last_normalized ASC, c.first_normalized ASC, c.character_id ASC
874 LIMIT ?
875 `).all(needle, needle, needle, needle, cap);
876 }
877
878 getCharacterBounty (characterId, now = Date.now())
879 {
880 return this.applyCharacterBountyDecay(characterId, now);
881 }
882
883 /**
884 * Daily −1 bounty decay (lazy). First touch after migration stamps today without retroactive decay.
885 * Never moves `last_decay_day` backward (simulation clocks in tests can be epoch-relative).
886 */
887 applyCharacterBountyDecay (characterId, now = Date.now())
888 {
889 const id = characterId | 0;
890 const row = this.db.prepare(`
891 SELECT amount, last_decay_day FROM character_bounties WHERE character_id = ?
892 `).get(id);
893 if (!row)
894 { return 0; }
895 const day = bountyDecayDayKey(now);
896 let amount = Math.max(0, row.amount | 0);
897 const last = row.last_decay_day ? String(row.last_decay_day) : "";
898 if (!last)
899 {
900 this.db.prepare(`
901 UPDATE character_bounties SET last_decay_day = ? WHERE character_id = ?
902 `).run(day, id);
903 return amount;
904 }
905 if (last === day)
906 { return amount; }
907 const days = bountyDecayDaysBetween(last, day);
908 if (days <= 0)
909 {
910 // `now` is on/before the stamped day (clock skew / harness) — leave the stamp alone.
911 return amount;
912 }
913 amount = applyBountyDailyDecay(amount, days);
914 this.db.prepare(`
915 UPDATE character_bounties SET amount = ?, last_decay_day = ? WHERE character_id = ?
916 `).run(amount, day, id);
917 return amount;
918 }
919
920 /** Add coins to a bounty; returns the new total. */
921 addCharacterBounty (characterId, amount, now = Date.now())
922 {
923 const id = characterId | 0;
924 const add = Math.max(0, amount | 0);
925 if (add <= 0)
926 { return this.getCharacterBounty(id, now); }
927 const day = bountyDecayDayKey(now);
928 this.applyCharacterBountyDecay(id, now);
929 this.db.prepare(`
930 INSERT INTO character_bounties (character_id, amount, last_decay_day) VALUES (?, ?, ?)
931 ON CONFLICT(character_id) DO UPDATE SET amount = amount + excluded.amount
932 `).run(id, add, day);
933 return this.getCharacterBounty(id, now);
934 }
935
936 /** Reduce bounty by up to `amount`; returns the new total. */
937 reduceCharacterBounty (characterId, amount, now = Date.now())
938 {
939 const id = characterId | 0;
940 const cut = Math.max(0, amount | 0);
941 const current = this.getCharacterBounty(id, now);
942 if (cut <= 0)
943 { return current; }
944 const next = Math.max(0, current - cut);
945 const day = bountyDecayDayKey(now);
946 this.db.prepare(`
947 INSERT INTO character_bounties (character_id, amount, last_decay_day) VALUES (?, ?, ?)
948 ON CONFLICT(character_id) DO UPDATE SET amount = excluded.amount
949 `).run(id, next, day);
950 return next;
951 }
952
953 /** Zero out and return the previous bounty amount. */
954 takeCharacterBounty (characterId, now = Date.now())
955 {
956 const id = characterId | 0;
957 const amount = this.getCharacterBounty(id, now);
958 if (amount <= 0)
959 { return 0; }
960 const day = bountyDecayDayKey(now);
961 this.db.prepare(`
962 INSERT INTO character_bounties (character_id, amount, last_decay_day) VALUES (?, 0, ?)
963 ON CONFLICT(character_id) DO UPDATE SET amount = 0
964 `).run(id, day);
965 return amount;
966 }
967
968 getQuestState (characterId, questId)
969 {
970 const row = this.db.prepare(`
971 SELECT state_value, progress_blob, started_at, completed_at
972 FROM character_quest_state
973 WHERE character_id = ? AND quest_id = ?
974 `).get(characterId | 0, String(questId));
975 if (!row)
976 { return { state: "not-started", progress: {}, startedAt: null, completedAt: null }; }
977 const progress = JSON.parse(row.progress_blob || "{}");
978 return {
979 state: parseStoredQuestState(row.state_value),
980 progress,
981 startedAt: row.started_at ?? null,
982 completedAt: row.completed_at ?? null
983 };
984 }
985
986 listQuestStates (characterId)
987 {
988 return this.db.prepare(`
989 SELECT quest_id, state_value, progress_blob, started_at, completed_at
990 FROM character_quest_state
991 WHERE character_id = ?
992 `).all(characterId | 0).map((row) =>
993 {
994 return {
995 questId: row.quest_id,
996 state: parseStoredQuestState(row.state_value),
997 progress: JSON.parse(row.progress_blob || "{}"),
998 startedAt: row.started_at ?? null,
999 completedAt: row.completed_at ?? null
1000 };
1001 });
1002 }
1003
1004 saveQuestState (characterId, questId, state, progress = {}, startedAt = null, completedAt = null)
1005 {
1006 const stateValue = serializeQuestState(state);
1007 this.db.prepare(`
1008 INSERT INTO character_quest_state
1009 (character_id, quest_id, state_value, progress_blob, started_at, completed_at)
1010 VALUES (?, ?, ?, ?, ?, ?)
1011 ON CONFLICT(character_id, quest_id) DO UPDATE SET
1012 state_value = excluded.state_value,
1013 progress_blob = excluded.progress_blob,
1014 started_at = excluded.started_at,
1015 completed_at = excluded.completed_at
1016 `).run(
1017 characterId | 0,
1018 String(questId),
1019 stateValue,
1020 JSON.stringify(progress ?? {}),
1021 startedAt,
1022 completedAt
1023 );
1024 }
1025
1026 isDialogueHeard (characterId, dialogueId, stepId)
1027 {
1028 const row = this.db.prepare(`
1029 SELECT 1 FROM character_dialogue_heard
1030 WHERE character_id = ? AND dialogue_id = ? AND step_id = ?
1031 `).get(characterId | 0, String(dialogueId), String(stepId));
1032 return !!row;
1033 }
1034
1035 markDialogueHeard (characterId, dialogueId, stepId)
1036 {
1037 this.db.prepare(`
1038 INSERT OR IGNORE INTO character_dialogue_heard
1039 (character_id, dialogue_id, step_id, heard_at)
1040 VALUES (?, ?, ?, ?)
1041 `).run(characterId | 0, String(dialogueId), String(stepId), Date.now());
1042 }
1043
1044 listDialogueHeard (characterId)
1045 {
1046 return this.db.prepare(`
1047 SELECT dialogue_id, step_id, heard_at
1048 FROM character_dialogue_heard
1049 WHERE character_id = ?
1050 `).all(characterId | 0);
1051 }
1052
1053 isDialogueTriggerLatched (characterId, dialogueId, triggerId)
1054 {
1055 const row = this.db.prepare(`
1056 SELECT 1 FROM character_dialogue_triggers
1057 WHERE character_id = ? AND dialogue_id = ? AND trigger_id = ?
1058 `).get(characterId | 0, String(dialogueId), String(triggerId));
1059 return !!row;
1060 }
1061
1062 latchDialogueTrigger (characterId, dialogueId, triggerId)
1063 {
1064 this.db.prepare(`
1065 INSERT OR IGNORE INTO character_dialogue_triggers
1066 (character_id, dialogue_id, trigger_id, latched_at)
1067 VALUES (?, ?, ?, ?)
1068 `).run(characterId | 0, String(dialogueId), String(triggerId), Date.now());
1069 }
1070
1071 listDialogueTriggers (characterId)
1072 {
1073 return this.db.prepare(`
1074 SELECT dialogue_id, trigger_id, latched_at
1075 FROM character_dialogue_triggers
1076 WHERE character_id = ?
1077 `).all(characterId | 0);
1078 }
1079
1080 appendQuestJournal (characterId, questId, stepId, text)
1081 {
1082 const trimmed = String(text ?? "").trim();
1083 if (!trimmed)
1084 { return null; }
1085 const writtenAt = Date.now();
1086 const result = this.db.prepare(`
1087 INSERT INTO character_quest_journal
1088 (character_id, quest_id, step_id, text, written_at)
1089 VALUES (?, ?, ?, ?, ?)
1090 `).run(characterId | 0, String(questId), String(stepId), trimmed, writtenAt);
1091 return {
1092 entryId: Number(result.lastInsertRowid),
1093 questId: String(questId),
1094 stepId: String(stepId),
1095 text: trimmed,
1096 writtenAt
1097 };
1098 }
1099
1100 listQuestJournal (characterId, questId = null)
1101 {
1102 if (questId)
1103 {
1104 return this.db.prepare(`
1105 SELECT entry_id, quest_id, step_id, text, written_at
1106 FROM character_quest_journal
1107 WHERE character_id = ? AND quest_id = ?
1108 ORDER BY written_at ASC, entry_id ASC
1109 `).all(characterId | 0, String(questId));
1110 }
1111 return this.db.prepare(`
1112 SELECT entry_id, quest_id, step_id, text, written_at
1113 FROM character_quest_journal
1114 WHERE character_id = ?
1115 ORDER BY written_at ASC, entry_id ASC
1116 `).all(characterId | 0);
1117 }
1118
1119 listFriendRecords (characterId)
1120 {
1121 return this.listAccountFriendRecords(this.accountIdOfCharacter(characterId));
1122 }
1123
1124 accountIdOfCharacter (characterId)
1125 {
1126 const row = this.getCharacter(characterId);
1127 return row?.account_id | 0;
1128 }
1129
1130 getAccount (accountId)
1131 {
1132 return this.db.prepare(`
1133 SELECT account_id, auth_subject, username, email, is_admin
1134 FROM accounts WHERE account_id = ?
1135 `).get(accountId | 0) ?? null;
1136 }
1137
1138 accountDisplayName (accountId)
1139 {
1140 const row = this.getAccount(accountId);
1141 if (!row)
1142 { return "Unknown"; }
1143 const name = String(row.username ?? "").trim();
1144 return name || `Account ${row.account_id}`;
1145 }
1146
1147 listAccountFriendRecords (accountId)
1148 {
1149 const id = accountId | 0;
1150 if (!id)
1151 { return []; }
1152 return this.db.prepare(`
1153 SELECT
1154 CASE WHEN account_id_a = ? THEN account_id_b ELSE account_id_a END AS account_id,
1155 created_at
1156 FROM account_friendships
1157 WHERE account_id_a = ? OR account_id_b = ?
1158 ORDER BY created_at ASC
1159 `).all(id, id, id);
1160 }
1161
1162 areAccountFriends (accountId, otherAccountId)
1163 {
1164 const aId = accountId | 0;
1165 const bId = otherAccountId | 0;
1166 if (!aId || !bId)
1167 { return false; }
1168 if (aId === bId)
1169 { return true; }
1170 const [a, b] = pairIds(aId, bId);
1171 return !!this.db.prepare(`
1172 SELECT 1 FROM account_friendships WHERE account_id_a = ? AND account_id_b = ?
1173 `).get(a, b);
1174 }
1175
1176 areFriends (characterId, otherId)
1177 {
1178 const left = this.getCharacter(characterId);
1179 const right = this.getCharacter(otherId);
1180 if (!left || !right)
1181 { return false; }
1182 return this.areAccountFriends(left.account_id, right.account_id);
1183 }
1184
1185 insertAccountFriendship (accountId, otherId)
1186 {
1187 const [a, b] = pairIds(accountId, otherId);
1188 this.db.prepare(`
1189 INSERT OR IGNORE INTO account_friendships (account_id_a, account_id_b, created_at)
1190 VALUES (?, ?, ?)
1191 `).run(a, b, Date.now());
1192 }
1193
1194 insertFriendship (characterId, otherId)
1195 {
1196 const left = this.getCharacter(characterId);
1197 const right = this.getCharacter(otherId);
1198 if (!left || !right)
1199 { return; }
1200 this.insertAccountFriendship(left.account_id, right.account_id);
1201 }
1202
1203 deleteAccountFriendship (accountId, otherId)
1204 {
1205 const [a, b] = pairIds(accountId, otherId);
1206 this.db.prepare(`
1207 DELETE FROM account_friendships WHERE account_id_a = ? AND account_id_b = ?
1208 `).run(a, b);
1209 }
1210
1211 deleteFriendship (characterId, otherId)
1212 {
1213 const left = this.getCharacter(characterId);
1214 const right = this.getCharacter(otherId);
1215 if (!left || !right)
1216 { return; }
1217 this.deleteAccountFriendship(left.account_id, right.account_id);
1218 }
1219
1220 listIncomingAccountRequests (accountId)
1221 {
1222 return this.db.prepare(`
1223 SELECT request_id, from_account_id, to_account_id, created_at
1224 FROM account_friend_requests
1225 WHERE to_account_id = ?
1226 ORDER BY created_at ASC
1227 `).all(accountId | 0);
1228 }
1229
1230 listOutgoingAccountRequests (accountId)
1231 {
1232 return this.db.prepare(`
1233 SELECT request_id, from_account_id, to_account_id, created_at
1234 FROM account_friend_requests
1235 WHERE from_account_id = ?
1236 ORDER BY created_at ASC
1237 `).all(accountId | 0);
1238 }
1239
1240 listIncomingRequests (characterId)
1241 {
1242 return this.listIncomingAccountRequests(this.accountIdOfCharacter(characterId));
1243 }
1244
1245 listOutgoingRequests (characterId)
1246 {
1247 return this.listOutgoingAccountRequests(this.accountIdOfCharacter(characterId));
1248 }
1249
1250 getAccountFriendRequest (fromAccountId, toAccountId)
1251 {
1252 return this.db.prepare(`
1253 SELECT * FROM account_friend_requests
1254 WHERE from_account_id = ? AND to_account_id = ?
1255 `).get(fromAccountId | 0, toAccountId | 0) ?? null;
1256 }
1257
1258 getFriendRequest (fromId, toId)
1259 {
1260 const from = this.getCharacter(fromId);
1261 const to = this.getCharacter(toId);
1262 if (!from || !to)
1263 { return null; }
1264 return this.getAccountFriendRequest(from.account_id, to.account_id);
1265 }
1266
1267 insertAccountFriendRequest (fromAccountId, toAccountId)
1268 {
1269 this.db.prepare(`
1270 INSERT INTO account_friend_requests (from_account_id, to_account_id, created_at)
1271 VALUES (?, ?, ?)
1272 `).run(fromAccountId | 0, toAccountId | 0, Date.now());
1273 }
1274
1275 insertFriendRequest (fromId, toId)
1276 {
1277 const from = this.getCharacter(fromId);
1278 const to = this.getCharacter(toId);
1279 if (!from || !to)
1280 { return; }
1281 this.insertAccountFriendRequest(from.account_id, to.account_id);
1282 }
1283
1284 deleteAccountFriendRequest (fromAccountId, toAccountId)
1285 {
1286 this.db.prepare(`
1287 DELETE FROM account_friend_requests
1288 WHERE from_account_id = ? AND to_account_id = ?
1289 `).run(fromAccountId | 0, toAccountId | 0);
1290 }
1291
1292 deleteFriendRequest (fromId, toId)
1293 {
1294 const from = this.getCharacter(fromId);
1295 const to = this.getCharacter(toId);
1296 if (!from || !to)
1297 { return; }
1298 this.deleteAccountFriendRequest(from.account_id, to.account_id);
1299 }
1300
1301 /** Decayed kill counter at `now` (does not write). */
1302 peekKillCounter (characterId, now = Date.now())
1303 {
1304 const row = this.getCharacter(characterId);
1305 if (!row)
1306 { return 0; }
1307 const current = Math.max(0, Number(row.kill_counter) || 0);
1308 const at = row.kill_counter_at | 0;
1309 if (!(current > 0) || !at || now <= at)
1310 { return current; }
1311 return decayKillCounter(current, now - at).killCounter;
1312 }
1313
1314 getKillCounterState (characterId)
1315 {
1316 const row = this.getCharacter(characterId);
1317 if (!row)
1318 { return { killCounter: 0, updatedAt: Date.now() }; }
1319 return {
1320 killCounter: Math.max(0, Number(row.kill_counter) || 0),
1321 updatedAt: row.kill_counter_at | 0 || Date.now()
1322 };
1323 }
1324
1325 setKillCounter (characterId, killCounter, updatedAt = Date.now())
1326 {
1327 const value = Math.max(0, Number(killCounter) || 0);
1328 this.db.prepare(`
1329 UPDATE characters SET kill_counter = ?, kill_counter_at = ? WHERE character_id = ?
1330 `).run(value, updatedAt | 0, characterId | 0);
1331 return value;
1332 }
1333
1334 addKillCounter (characterId, amount, now = Date.now())
1335 {
1336 const state = this.getKillCounterState(characterId);
1337 const decayed = decayKillCounter(state.killCounter, Math.max(0, now - (state.updatedAt | 0)));
1338 const next = decayed.killCounter + Math.max(0, Number(amount) || 0);
1339 return this.setKillCounter(characterId, next, now);
1340 }
1341
1342 getCowardiceForgivenHour (characterId)
1343 {
1344 const row = this.getCharacter(characterId);
1345 if (!row || row.cowardice_forgiven_hour == null)
1346 { return null; }
1347 return row.cowardice_forgiven_hour | 0;
1348 }
1349
1350 setCowardiceForgivenHour (characterId, hourKey)
1351 {
1352 this.db.prepare(`
1353 UPDATE characters SET cowardice_forgiven_hour = ? WHERE character_id = ?
1354 `).run(hourKey | 0, characterId | 0);
1355 }
1356
1357 listPersistentInRegion (minX, minY, maxX, maxY, dimensionId = "overworld")
1358 {
1359 const dim = String(dimensionId ?? "overworld");
1360 return this.db.prepare(`
1361 SELECT entity_id, tile_x, tile_y, dimension_id, components_blob
1362 FROM persistent_entities
1363 WHERE tile_x >= ? AND tile_x <= ? AND tile_y >= ? AND tile_y <= ?
1364 AND dimension_id = ?
1365 `).all(minX, maxX, minY, maxY, dim);
1366 }
1367
1368 replacePersistentInRegion (minX, minY, maxX, maxY, entities, dimensionId = "overworld")
1369 {
1370 const dim = String(dimensionId ?? "overworld");
1371 this.db.exec("BEGIN");
1372 try
1373 {
1374 this.db.prepare(`
1375 DELETE FROM persistent_entities
1376 WHERE tile_x >= ? AND tile_x <= ? AND tile_y >= ? AND tile_y <= ?
1377 AND dimension_id = ?
1378 `).run(minX, maxX, minY, maxY, dim);
1379 const insert = this.db.prepare(`
1380 INSERT INTO persistent_entities (entity_id, tile_x, tile_y, dimension_id, components_blob)
1381 VALUES (?, ?, ?, ?, ?)
1382 `);
1383 for (const entity of entities)
1384 {
1385 insert.run(
1386 entity.entityId,
1387 entity.tileX,
1388 entity.tileY,
1389 String(entity.dimensionId ?? dim),
1390 JSON.stringify(entity.payload?.components ?? entity.components)
1391 );
1392 }
1393 this.db.exec("COMMIT");
1394 }
1395 catch (error)
1396 {
1397 this.db.exec("ROLLBACK");
1398 throw error;
1399 }
1400 }
1401
1402 #ensureBag (characterId, starterSettings = null, creationClothing = null)
1403 {
1404 const existing = this.db.prepare("SELECT * FROM character_bags WHERE character_id = ?").get(characterId);
1405 if (existing)
1406 { return parseBag(existing, characterId); }
1407 const contents = buildStarterBagContents(starterSettings, creationClothing);
1408 this.db.prepare(`
1409 INSERT INTO character_bags (
1410 character_id, inventory_blob, equipment_blob, currency, vault_blob, vault_currency, skills_blob
1411 ) VALUES (?, ?, ?, 0, ?, 0, ?)
1412 `).run(
1413 characterId,
1414 JSON.stringify(contents.slots),
1415 JSON.stringify(contents.equipment),
1416 JSON.stringify({ slots: [], reserved: [] }),
1417 JSON.stringify(storedSkillXp(defaultPlayerSkills()))
1418 );
1419 return this.getBag(characterId);
1420 }
1421
1422 getBag (characterId)
1423 {
1424 const row = this.db.prepare("SELECT * FROM character_bags WHERE character_id = ?").get(characterId);
1425 if (row)
1426 { return parseBag(row, characterId); }
1427 if (this.getCharacter(characterId))
1428 { return this.#ensureBag(characterId); }
1429 return null;
1430 }
1431
1432 saveBag (characterId, bag)
1433 {
1434 this.#ensureBag(characterId);
1435 const migrated = migrateBagCoins(bag);
1436 const vault = normalizeVault(
1437 migrated.vaultReserved != null
1438 ? { slots: migrated.vault, reserved: migrated.vaultReserved }
1439 : migrated.vault
1440 );
1441 this.db.prepare(`
1442 UPDATE character_bags
1443 SET inventory_blob = ?, equipment_blob = ?, currency = 0, vault_blob = ?, vault_currency = 0
1444 WHERE character_id = ?
1445 `).run(
1446 JSON.stringify(normalizeInventorySlots(migrated.inventory ?? emptySlots())),
1447 JSON.stringify(migrated.equipment ?? emptyEquipment()),
1448 JSON.stringify({ slots: vault.slots, reserved: vault.reserved }),
1449 characterId
1450 );
1451 }
1452
1453 getSkills (characterId)
1454 {
1455 const row = this.db.prepare("SELECT skills_blob FROM character_bags WHERE character_id = ?").get(characterId);
1456 if (row)
1457 {
1458 const parsed = JSON.parse(row.skills_blob || "{}");
1459 const skills = normalizePlayerSkills(parsed);
1460 // Persist floors (Health ≥ 5, others ≥ 1) for legacy under-leveled rows.
1461 if (this.#skillsBlobNeedsRewrite(parsed, skills))
1462 { this.saveSkills(characterId, skills); }
1463 return skills;
1464 }
1465 if (this.getCharacter(characterId))
1466 {
1467 this.#ensureBag(characterId);
1468 return this.getSkills(characterId);
1469 }
1470 return null;
1471 }
1472
1473 /** True when stored blob is below the default minimum levels and must be rewritten. */
1474 #skillsBlobNeedsRewrite (parsed, normalized)
1475 {
1476 const raw = parsed?.xp && typeof parsed.xp === "object" ? parsed.xp : parsed;
1477 if (!raw || typeof raw !== "object")
1478 { return true; }
1479 for (const skillId of Object.keys(normalized.xp))
1480 {
1481 const stored = Math.max(0, Math.floor(Number(raw[skillId]) || 0));
1482 if (stored < normalized.xp[skillId])
1483 { return true; }
1484 }
1485 return false;
1486 }
1487
1488 saveSkills (characterId, skills)
1489 {
1490 this.#ensureBag(characterId);
1491 const xp = storedSkillXp(skills?.xp ? skills : { xp: skills });
1492 this.db.prepare("UPDATE character_bags SET skills_blob = ? WHERE character_id = ?").run(
1493 JSON.stringify({ xp }),
1494 characterId
1495 );
1496 }
1497
1498 getSpells (characterId)
1499 {
1500 const row = this.db.prepare("SELECT spells_blob FROM character_bags WHERE character_id = ?").get(characterId);
1501 if (row)
1502 {
1503 return normalizePlayerSpells(JSON.parse(row.spells_blob || "{}"));
1504 }
1505 if (this.getCharacter(characterId))
1506 {
1507 this.#ensureBag(characterId);
1508 return this.getSpells(characterId);
1509 }
1510 return null;
1511 }
1512
1513 saveSpells (characterId, spells)
1514 {
1515 this.#ensureBag(characterId);
1516 this.db.prepare("UPDATE character_bags SET spells_blob = ? WHERE character_id = ?").run(
1517 JSON.stringify(storedPlayerSpells(spells)),
1518 characterId
1519 );
1520 }
1521 getInheritance (accountId)
1522 {
1523 const row = this.db.prepare("SELECT * FROM account_inheritance WHERE account_id = ?").get(accountId);
1524 if (!row)
1525 { return { accountId, slots: emptySlots() }; }
1526 return migrateInheritanceCoins({
1527 accountId,
1528 slots: JSON.parse(row.slots_blob),
1529 currency: row.currency
1530 });
1531 }
1532
1533 saveInheritance (accountId, inheritance)
1534 {
1535 const migrated = migrateInheritanceCoins(inheritance);
1536 this.db.prepare(`
1537 INSERT INTO account_inheritance (account_id, slots_blob, currency)
1538 VALUES (?, ?, 0)
1539 ON CONFLICT(account_id) DO UPDATE SET slots_blob = excluded.slots_blob, currency = 0
1540 `).run(accountId, JSON.stringify(migrated.slots ?? emptySlots()));
1541 }
1542
1543 #moveGoodsToInheritance (character)
1544 {
1545 const bag = this.getBag(character.character_id) ?? {
1546 inventory: emptySlots(),
1547 equipment: emptyEquipment(),
1548 vault: [],
1549 vaultReserved: []
1550 };
1551 const inheritance = this.getInheritance(character.account_id);
1552 const vault = normalizeVault(
1553 bag.vaultReserved != null
1554 ? { slots: bag.vault, reserved: bag.vaultReserved }
1555 : bag.vault
1556 );
1557 // Void outstanding cheques issued by this character: reservations return to available vault.
1558 for (const reserved of vault.reserved ?? [])
1559 {
1560 const stack = {
1561 itemId: reserved.itemId,
1562 quantity: reserved.quantity,
1563 origin: reserved.origin ?? null,
1564 instance: reserved.instance ?? null
1565 };
1566 const def = itemDefinition(stack.itemId);
1567 if (!def)
1568 { continue; }
1569 const restored = insertStackGrowable(vault.slots, stack, def);
1570 vault.slots = restored.slots;
1571 }
1572 vault.reserved = [];
1573 const incoming = [
1574 ...(bag.inventory ?? []),
1575 ...Object.values(bag.equipment?.slots ?? {}),
1576 ...(vault.slots ?? [])
1577 ].filter(Boolean);
1578 let slots = inheritance.slots ?? emptySlots();
1579 for (const stack of incoming)
1580 {
1581 const def = itemDefinition(stack.itemId);
1582 if (!def)
1583 { continue; }
1584 const result = insertStack(slots, stack, def);
1585 if (result.ok)
1586 { slots = result.slots; }
1587 else
1588 {
1589 const extra = result.slots;
1590 for (const leftover of extra)
1591 {
1592 if (leftover)
1593 { slots.push(leftover); }
1594 }
1595 slots.push({ ...stack, quantity: result.remainder });
1596 }
1597 }
1598 this.saveInheritance(character.account_id, {
1599 slots: publicSlots(slots)
1600 });
1601 }
1602
1603 nextGroundItemId ()
1604 {
1605 const row = this.db.prepare("SELECT MAX(ground_item_id) AS id FROM ground_items").get();
1606 return (row?.id ?? 0) + 1;
1607 }
1608
1609 listGroundItemsInRegion (minX, minY, maxX, maxY, dimensionId = "overworld")
1610 {
1611 const dim = String(dimensionId ?? "overworld");
1612 return this.db.prepare(`
1613 SELECT * FROM ground_items
1614 WHERE tile_x >= ? AND tile_x <= ? AND tile_y >= ? AND tile_y <= ?
1615 AND dimension_id = ?
1616 `).all(minX, maxX, minY, maxY, dim).map(parseGroundItem);
1617 }
1618
1619 upsertGroundItem (item)
1620 {
1621 this.db.prepare(`
1622 INSERT INTO ground_items (
1623 ground_item_id, tile_x, tile_y, dimension_id, stack_blob, dropped_at, expires_at, source, dropped_by_character_id
1624 ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
1625 ON CONFLICT(ground_item_id) DO UPDATE SET
1626 tile_x = excluded.tile_x,
1627 tile_y = excluded.tile_y,
1628 dimension_id = excluded.dimension_id,
1629 stack_blob = excluded.stack_blob,
1630 dropped_at = excluded.dropped_at,
1631 expires_at = excluded.expires_at,
1632 source = excluded.source,
1633 dropped_by_character_id = excluded.dropped_by_character_id
1634 `).run(
1635 item.groundItemId,
1636 item.tileX,
1637 item.tileY,
1638 String(item.dimensionId ?? "overworld"),
1639 JSON.stringify(item.stack),
1640 item.droppedAt,
1641 item.expiresAt,
1642 item.source,
1643 item.droppedByCharacterId ?? null
1644 );
1645 }
1646
1647 deleteGroundItem (groundItemId)
1648 {
1649 this.db.prepare("DELETE FROM ground_items WHERE ground_item_id = ?").run(groundItemId);
1650 }
1651
1652 insertCourierTransit (transit)
1653 {
1654 const now = transit.updatedAt ?? Date.now();
1655 const result = this.db.prepare(`
1656 INSERT INTO courier_transits (
1657 sender_character_id, destination_kind, destination_character_id, cargo_blob,
1658 origin_x, origin_y, dest_x, dest_y, traveled, status, entity_id, updated_at, created_at,
1659 vault_character_id, origin_dimension_id, dest_dimension_id, legs_blob, leg_index
1660 ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
1661 `).run(
1662 transit.senderCharacterId,
1663 transit.destinationKind,
1664 transit.destinationCharacterId ?? null,
1665 JSON.stringify(transit.cargo),
1666 transit.originX,
1667 transit.originY,
1668 transit.destX,
1669 transit.destY,
1670 transit.traveled ?? 0,
1671 transit.status ?? "in_transit",
1672 transit.entityId ?? null,
1673 now,
1674 transit.createdAt ?? now,
1675 transit.vaultCharacterId ?? null,
1676 transit.originDimensionId ?? "overworld",
1677 transit.destDimensionId ?? "overworld",
1678 transit.legs != null ? JSON.stringify(transit.legs) : null,
1679 transit.legIndex | 0
1680 );
1681 return this.getCourierTransit(Number(result.lastInsertRowid));
1682 }
1683
1684 getCourierTransit (courierId)
1685 {
1686 const row = this.db.prepare("SELECT * FROM courier_transits WHERE courier_id = ?").get(courierId);
1687 return row ? parseCourierTransit(row) : null;
1688 }
1689
1690 listActiveCourierTransits ()
1691 {
1692 return this.db.prepare(`
1693 SELECT * FROM courier_transits WHERE status = 'in_transit' ORDER BY courier_id ASC
1694 `).all().map(parseCourierTransit);
1695 }
1696
1697 updateCourierTransit (transit)
1698 {
1699 this.db.prepare(`
1700 UPDATE courier_transits
1701 SET traveled = ?, status = ?, entity_id = ?, updated_at = ?,
1702 origin_x = ?, origin_y = ?, dest_x = ?, dest_y = ?,
1703 destination_kind = ?, destination_character_id = ?, vault_character_id = ?,
1704 origin_dimension_id = ?, dest_dimension_id = ?, legs_blob = ?, leg_index = ?
1705 WHERE courier_id = ?
1706 `).run(
1707 transit.traveled ?? 0,
1708 transit.status,
1709 transit.entityId ?? null,
1710 transit.updatedAt ?? Date.now(),
1711 transit.originX,
1712 transit.originY,
1713 transit.destX,
1714 transit.destY,
1715 transit.destinationKind,
1716 transit.destinationCharacterId ?? null,
1717 transit.vaultCharacterId ?? null,
1718 transit.originDimensionId ?? "overworld",
1719 transit.destDimensionId ?? "overworld",
1720 transit.legs != null ? JSON.stringify(transit.legs) : null,
1721 transit.legIndex | 0,
1722 transit.courierId
1723 );
1724 }
1725
1726 appendBankRestNotice (characterId, entry)
1727 {
1728 const qty = Math.max(1, entry.quantity | 0);
1729 this.db.prepare(`
1730 INSERT INTO bank_rest_notices (character_id, item_id, item_name, quantity, source_name, created_at)
1731 VALUES (?, ?, ?, ?, ?, ?)
1732 `).run(
1733 characterId,
1734 entry.itemId | 0,
1735 String(entry.itemName ?? "Item"),
1736 qty,
1737 String(entry.sourceName ?? "unknown source"),
1738 entry.createdAt ?? Date.now()
1739 );
1740 }
1741
1742 listBankRestNotices (characterId)
1743 {
1744 return this.db.prepare(`
1745 SELECT * FROM bank_rest_notices WHERE character_id = ? ORDER BY notice_id ASC
1746 `).all(characterId).map((row) => ({
1747 noticeId: row.notice_id,
1748 characterId: row.character_id,
1749 itemId: row.item_id,
1750 itemName: row.item_name,
1751 quantity: row.quantity,
1752 sourceName: row.source_name,
1753 createdAt: row.created_at
1754 }));
1755 }
1756
1757 clearBankRestNotices (characterId)
1758 {
1759 this.db.prepare("DELETE FROM bank_rest_notices WHERE character_id = ?").run(characterId);
1760 }
1761
1762 insertHeldCourierDelivery (delivery)
1763 {
1764 const now = delivery.createdAt ?? Date.now();
1765 const result = this.db.prepare(`
1766 INSERT INTO held_courier_deliveries (
1767 character_id, sender_character_id, origin_tile_x, origin_tile_y,
1768 destination_kind, destination_character_id, vault_character_id, cargo_blob, created_at,
1769 origin_dimension_id
1770 ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
1771 `).run(
1772 delivery.characterId,
1773 delivery.senderCharacterId,
1774 delivery.originTileX | 0,
1775 delivery.originTileY | 0,
1776 delivery.destinationKind,
1777 delivery.destinationCharacterId ?? null,
1778 delivery.vaultCharacterId ?? null,
1779 JSON.stringify(delivery.cargo),
1780 now,
1781 delivery.originDimensionId ?? "overworld"
1782 );
1783 return Number(result.lastInsertRowid);
1784 }
1785
1786 listHeldCourierDeliveries (characterId)
1787 {
1788 return this.db.prepare(`
1789 SELECT * FROM held_courier_deliveries WHERE character_id = ? ORDER BY hold_id ASC
1790 `).all(characterId).map(parseHeldCourierDelivery);
1791 }
1792
1793 deleteHeldCourierDelivery (holdId)
1794 {
1795 this.db.prepare("DELETE FROM held_courier_deliveries WHERE hold_id = ?").run(holdId);
1796 }
1797
1798 insertMarketOrder (order)
1799 {
1800 const result = this.db.prepare(`
1801 INSERT INTO market_orders (
1802 character_id, side, item_id, quantity, limit_unit_price,
1803 escrow_coins, escrow_stack_blob, listing_key, created_at, status
1804 ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
1805 `).run(
1806 order.characterId,
1807 order.side,
1808 order.itemId | 0,
1809 order.quantity | 0,
1810 order.limitUnitPrice | 0,
1811 order.escrowCoins | 0 || 0,
1812 order.escrowStack ? JSON.stringify(order.escrowStack) : null,
1813 order.listingKey ?? null,
1814 order.createdAt ?? Date.now(),
1815 order.status ?? "open"
1816 );
1817 return this.getMarketOrder(Number(result.lastInsertRowid));
1818 }
1819
1820 getMarketOrder (orderId)
1821 {
1822 const row = this.db.prepare("SELECT * FROM market_orders WHERE order_id = ?").get(orderId);
1823 return row ? parseMarketOrder(row) : null;
1824 }
1825
1826 updateMarketOrder (order)
1827 {
1828 this.db.prepare(`
1829 UPDATE market_orders
1830 SET quantity = ?, escrow_coins = ?, escrow_stack_blob = ?, status = ?
1831 WHERE order_id = ?
1832 `).run(
1833 order.quantity | 0,
1834 order.escrowCoins | 0 || 0,
1835 order.escrowStack ? JSON.stringify(order.escrowStack) : null,
1836 order.status,
1837 order.orderId
1838 );
1839 return this.getMarketOrder(order.orderId);
1840 }
1841
1842 listOpenMarketOrdersForCharacter (characterId)
1843 {
1844 return this.db.prepare(`
1845 SELECT * FROM market_orders
1846 WHERE character_id = ? AND status = 'open'
1847 ORDER BY created_at ASC, order_id ASC
1848 `).all(characterId).map(parseMarketOrder);
1849 }
1850
1851 listOpenBuyOrdersForItem (itemId, listingKey = null)
1852 {
1853 if (listingKey)
1854 {
1855 return this.db.prepare(`
1856 SELECT * FROM market_orders
1857 WHERE status = 'open' AND side = 'buy' AND item_id = ? AND listing_key = ?
1858 ORDER BY limit_unit_price DESC, created_at ASC, order_id ASC
1859 `).all(itemId | 0, listingKey).map(parseMarketOrder);
1860 }
1861 return this.db.prepare(`
1862 SELECT * FROM market_orders
1863 WHERE status = 'open' AND side = 'buy' AND item_id = ?
1864 AND (listing_key IS NULL OR listing_key = '')
1865 ORDER BY limit_unit_price DESC, created_at ASC, order_id ASC
1866 `).all(itemId | 0).map(parseMarketOrder);
1867 }
1868
1869 listOpenSellOrdersForItem (itemId, listingKey = null)
1870 {
1871 if (listingKey)
1872 {
1873 return this.db.prepare(`
1874 SELECT * FROM market_orders
1875 WHERE status = 'open' AND side = 'sell' AND item_id = ? AND listing_key = ?
1876 ORDER BY limit_unit_price ASC, created_at ASC, order_id ASC
1877 `).all(itemId | 0, listingKey).map(parseMarketOrder);
1878 }
1879 return this.db.prepare(`
1880 SELECT * FROM market_orders
1881 WHERE status = 'open' AND side = 'sell' AND item_id = ?
1882 AND (listing_key IS NULL OR listing_key = '')
1883 ORDER BY limit_unit_price ASC, created_at ASC, order_id ASC
1884 `).all(itemId | 0).map(parseMarketOrder);
1885 }
1886
1887 listExpiredMarketOrders (createdBefore)
1888 {
1889 return this.db.prepare(`
1890 SELECT * FROM market_orders
1891 WHERE status = 'open' AND created_at < ?
1892 ORDER BY created_at ASC, order_id ASC
1893 `).all(createdBefore).map(parseMarketOrder);
1894 }
1895
1896 insertMarketTrade (trade)
1897 {
1898 const result = this.db.prepare(`
1899 INSERT INTO market_trades (
1900 buy_order_id, sell_order_id, item_id, quantity, unit_price,
1901 buyer_character_id, seller_character_id, created_at
1902 ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
1903 `).run(
1904 trade.buyOrderId,
1905 trade.sellOrderId,
1906 trade.itemId | 0,
1907 trade.quantity | 0,
1908 trade.unitPrice | 0,
1909 trade.buyerCharacterId,
1910 trade.sellerCharacterId,
1911 trade.createdAt ?? Date.now()
1912 );
1913 return Number(result.lastInsertRowid);
1914 }
1915
1916 upsertItemLastSale (sale)
1917 {
1918 this.db.prepare(`
1919 INSERT INTO item_last_sales (item_id, last_trade_price, last_trade_at, price_from)
1920 VALUES (?, ?, ?, ?)
1921 ON CONFLICT(item_id) DO UPDATE SET
1922 last_trade_price = excluded.last_trade_price,
1923 last_trade_at = excluded.last_trade_at,
1924 price_from = excluded.price_from
1925 `).run(
1926 sale.itemId | 0,
1927 sale.lastTradePrice | 0,
1928 sale.lastTradeAt,
1929 Math.round(Number(sale.priceFrom) || 0)
1930 );
1931 }
1932
1933 listItemLastSales ()
1934 {
1935 return this.db.prepare("SELECT * FROM item_last_sales").all().map((row) => ({
1936 itemId: row.item_id,
1937 lastTradePrice: row.last_trade_price,
1938 lastTradeAt: row.last_trade_at,
1939 priceFrom: row.price_from
1940 }));
1941 }
1942
1943 close ()
1944 {
1945 this.db.close();
1946 }
1947}
1948
1949export function normalizeName (value)
1950{
1951 return String(value ?? "").trim().toLocaleLowerCase();
1952}
1953
1954function pairIds (left, right)
1955{
1956 return left < right ? [left, right] : [right, left];
1957}
1958
1959function parseGroundItem (row)
1960{
1961 return {
1962 groundItemId: row.ground_item_id,
1963 tileX: row.tile_x,
1964 tileY: row.tile_y,
1965 dimensionId: String(row.dimension_id ?? "overworld"),
1966 stack: JSON.parse(row.stack_blob),
1967 droppedAt: row.dropped_at,
1968 expiresAt: row.expires_at,
1969 source: row.source,
1970 droppedByCharacterId: row.dropped_by_character_id
1971 };
1972}
1973
1974function parseMarketOrder (row)
1975{
1976 return {
1977 orderId: row.order_id,
1978 characterId: row.character_id,
1979 side: row.side,
1980 itemId: row.item_id,
1981 quantity: row.quantity,
1982 limitUnitPrice: row.limit_unit_price,
1983 escrowCoins: row.escrow_coins | 0,
1984 escrowStack: row.escrow_stack_blob ? JSON.parse(row.escrow_stack_blob) : null,
1985 listingKey: row.listing_key ?? null,
1986 createdAt: row.created_at,
1987 status: row.status
1988 };
1989}
1990
1991function bagContainsItem (inventory, equipment, itemId)
1992{
1993 for (const stack of inventory ?? [])
1994 {
1995 if (stack?.itemId === itemId)
1996 { return true; }
1997 }
1998 for (const stack of Object.values(equipment?.slots ?? {}))
1999 {
2000 if (stack?.itemId === itemId)
2001 { return true; }
2002 }
2003 return false;
2004}
2005
2006function parseBag (row, characterId)
2007{
2008 const vault = normalizeVault(JSON.parse(row.vault_blob));
2009 return migrateBagCoins({
2010 characterId,
2011 inventory: normalizeInventorySlots(JSON.parse(row.inventory_blob)),
2012 equipment: JSON.parse(row.equipment_blob),
2013 currency: row.currency,
2014 vault: vault.slots,
2015 vaultReserved: vault.reserved,
2016 vaultCurrency: row.vault_currency
2017 });
2018}
2019
2020function parseCourierTransit (row)
2021{
2022 let legs = null;
2023 if (row.legs_blob)
2024 {
2025 try
2026 { legs = JSON.parse(row.legs_blob); }
2027 catch
2028 { legs = null; }
2029 }
2030 return {
2031 courierId: row.courier_id,
2032 senderCharacterId: row.sender_character_id,
2033 destinationKind: row.destination_kind,
2034 destinationCharacterId: row.destination_character_id,
2035 vaultCharacterId: row.vault_character_id ?? null,
2036 cargo: JSON.parse(row.cargo_blob),
2037 originX: row.origin_x,
2038 originY: row.origin_y,
2039 destX: row.dest_x,
2040 destY: row.dest_y,
2041 originDimensionId: row.origin_dimension_id ?? "overworld",
2042 destDimensionId: row.dest_dimension_id ?? "overworld",
2043 legs: Array.isArray(legs) ? legs : null,
2044 legIndex: row.leg_index | 0,
2045 traveled: row.traveled,
2046 status: row.status,
2047 entityId: row.entity_id,
2048 updatedAt: row.updated_at,
2049 createdAt: row.created_at
2050 };
2051}
2052
2053function parseHeldCourierDelivery (row)
2054{
2055 return {
2056 holdId: row.hold_id,
2057 characterId: row.character_id,
2058 senderCharacterId: row.sender_character_id,
2059 originTileX: row.origin_tile_x,
2060 originTileY: row.origin_tile_y,
2061 originDimensionId: row.origin_dimension_id ?? "overworld",
2062 destinationKind: row.destination_kind,
2063 destinationCharacterId: row.destination_character_id,
2064 vaultCharacterId: row.vault_character_id,
2065 cargo: JSON.parse(row.cargo_blob),
2066 createdAt: row.created_at
2067 };
2068}