BlueGit

timfa questward

Files

questward / server / src / persistence / stateDb.js

stateDb.js

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