BlueGit

timfa questward

Files

questward / server / src / persistence / stateDb.js

stateDb.js

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