BlueGit

timfa questward

Files

questward / server / src / persistence / stateDb.js

stateDb.js

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