BlueGit

timfa questward

Files

questward / server / src / persistence / stateDb.js

stateDb.js

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