BlueGit

timfa questward

Files

questward / server / src / persistence / stateDb.js

stateDb.js

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