BlueGit

timfa questward

Files

questward / server / src / persistence / stateDb.js

stateDb.js

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