BlueGit

timfa questward

Files

questward / server / src / persistence / stateDb.js

stateDb.js

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