BlueGit

timfa questward

Files

questward / server / src / persistence / stateDb.js

stateDb.js

stateDb.js 32.4 KiB Download Raw
1import { openDatabase } from "./database.js";
2import { emptyEquipment, emptySlots, insertStack, insertStackGrowable, normalizeVault, normalizeInventorySlots, publicSlots, migrateBagCoins, migrateInheritanceCoins } from "../../../shared/items/inventory.js";
3import { itemDefinition, ItemId } from "../../../shared/definitions/items.js";
4import { buildStarterBagContents } from "../../../shared/items/starterLoadout.js";
5import { normalizePlayerSkills, defaultPlayerSkills, storedSkillXp } from "../../../shared/skills/state.js";
6
7const STATE_SCHEMA = `
8CREATE TABLE IF NOT EXISTS accounts (
9 account_id INTEGER PRIMARY KEY AUTOINCREMENT,
10 auth_subject TEXT NOT NULL UNIQUE,
11 email TEXT,
12 is_admin INTEGER NOT NULL DEFAULT 0
13);
14
15CREATE TABLE IF NOT EXISTS surname_claims (
16 surname_normalized TEXT PRIMARY KEY,
17 account_id INTEGER NOT NULL
18);
19
20CREATE TABLE IF NOT EXISTS characters (
21 character_id INTEGER PRIMARY KEY AUTOINCREMENT,
22 account_id INTEGER NOT NULL,
23 first_name TEXT NOT NULL,
24 last_name TEXT NOT NULL,
25 first_normalized TEXT NOT NULL,
26 last_normalized TEXT NOT NULL,
27 sex TEXT NOT NULL,
28 tile_x INTEGER NOT NULL DEFAULT 8,
29 tile_y INTEGER NOT NULL DEFAULT 8,
30 gait TEXT NOT NULL DEFAULT 'walk',
31 is_dead INTEGER NOT NULL DEFAULT 0,
32 deletion_scheduled_at INTEGER,
33 hair_style TEXT,
34 hair_hue REAL NOT NULL DEFAULT 30,
35 hair_saturation REAL NOT NULL DEFAULT 0.55,
36 hair_darkness REAL NOT NULL DEFAULT 0.45
37);
38CREATE TABLE IF NOT EXISTS friend_requests (
39 request_id INTEGER PRIMARY KEY AUTOINCREMENT,
40 from_character_id INTEGER NOT NULL,
41 to_character_id INTEGER NOT NULL,
42 created_at INTEGER NOT NULL,
43 UNIQUE (from_character_id, to_character_id)
44);
45
46CREATE TABLE IF NOT EXISTS friendships (
47 character_id_a INTEGER NOT NULL,
48 character_id_b INTEGER NOT NULL,
49 created_at INTEGER NOT NULL,
50 PRIMARY KEY (character_id_a, character_id_b)
51);
52
53CREATE TABLE IF NOT EXISTS persistent_entities (
54 entity_id INTEGER PRIMARY KEY,
55 tile_x INTEGER NOT NULL,
56 tile_y INTEGER NOT NULL,
57 components_blob TEXT NOT NULL
58);
59
60CREATE TABLE IF NOT EXISTS character_bags (
61 character_id INTEGER PRIMARY KEY,
62 inventory_blob TEXT NOT NULL,
63 equipment_blob TEXT NOT NULL,
64 currency INTEGER NOT NULL DEFAULT 0,
65 vault_blob TEXT NOT NULL,
66 vault_currency INTEGER NOT NULL DEFAULT 0,
67 skills_blob TEXT NOT NULL DEFAULT '{}'
68);
69
70CREATE TABLE IF NOT EXISTS account_inheritance (
71 account_id INTEGER PRIMARY KEY,
72 slots_blob TEXT NOT NULL,
73 currency INTEGER NOT NULL DEFAULT 0
74);
75
76CREATE TABLE IF NOT EXISTS ground_items (
77 ground_item_id INTEGER PRIMARY KEY,
78 tile_x INTEGER NOT NULL,
79 tile_y INTEGER NOT NULL,
80 stack_blob TEXT NOT NULL,
81 dropped_at INTEGER NOT NULL,
82 expires_at INTEGER NOT NULL,
83 source TEXT NOT NULL,
84 dropped_by_character_id INTEGER
85);
86
87CREATE TABLE IF NOT EXISTS courier_transits (
88 courier_id INTEGER PRIMARY KEY AUTOINCREMENT,
89 sender_character_id INTEGER NOT NULL,
90 destination_kind TEXT NOT NULL,
91 destination_character_id INTEGER,
92 cargo_blob TEXT NOT NULL,
93 origin_x REAL NOT NULL,
94 origin_y REAL NOT NULL,
95 dest_x REAL NOT NULL,
96 dest_y REAL NOT NULL,
97 traveled REAL NOT NULL DEFAULT 0,
98 status TEXT NOT NULL,
99 entity_id INTEGER,
100 updated_at INTEGER NOT NULL,
101 created_at INTEGER NOT NULL
102);
103
104CREATE TABLE IF NOT EXISTS market_orders (
105 order_id INTEGER PRIMARY KEY AUTOINCREMENT,
106 character_id INTEGER NOT NULL,
107 side TEXT NOT NULL,
108 item_id INTEGER NOT NULL,
109 quantity INTEGER NOT NULL,
110 limit_unit_price INTEGER NOT NULL,
111 escrow_coins INTEGER NOT NULL DEFAULT 0,
112 escrow_stack_blob TEXT,
113 created_at INTEGER NOT NULL,
114 status TEXT NOT NULL
115);
116
117CREATE TABLE IF NOT EXISTS market_trades (
118 trade_id INTEGER PRIMARY KEY AUTOINCREMENT,
119 buy_order_id INTEGER NOT NULL,
120 sell_order_id INTEGER NOT NULL,
121 item_id INTEGER NOT NULL,
122 quantity INTEGER NOT NULL,
123 unit_price INTEGER NOT NULL,
124 buyer_character_id INTEGER NOT NULL,
125 seller_character_id INTEGER NOT NULL,
126 created_at INTEGER NOT NULL
127);
128
129CREATE TABLE IF NOT EXISTS item_last_sales (
130 item_id INTEGER PRIMARY KEY,
131 last_trade_price INTEGER NOT NULL,
132 last_trade_at INTEGER NOT NULL,
133 price_from INTEGER NOT NULL
134);
135
136CREATE TABLE IF NOT EXISTS bank_rest_notices (
137 notice_id INTEGER PRIMARY KEY AUTOINCREMENT,
138 character_id INTEGER NOT NULL,
139 item_id INTEGER NOT NULL,
140 item_name TEXT NOT NULL,
141 quantity INTEGER NOT NULL,
142 source_name TEXT NOT NULL,
143 created_at INTEGER NOT NULL
144);
145
146CREATE TABLE IF NOT EXISTS held_courier_deliveries (
147 hold_id INTEGER PRIMARY KEY AUTOINCREMENT,
148 character_id INTEGER NOT NULL,
149 sender_character_id INTEGER NOT NULL,
150 origin_tile_x INTEGER NOT NULL,
151 origin_tile_y INTEGER NOT NULL,
152 destination_kind TEXT NOT NULL,
153 destination_character_id INTEGER,
154 vault_character_id INTEGER,
155 cargo_blob TEXT NOT NULL,
156 created_at INTEGER NOT NULL
157);
158
159CREATE TABLE IF NOT EXISTS character_bounties (
160 character_id INTEGER PRIMARY KEY,
161 amount INTEGER NOT NULL DEFAULT 0
162);
163`;
164
165export class StateDatabase
166{
167 constructor (filePath)
168 {
169 this.db = openDatabase(filePath);
170 this.db.exec(STATE_SCHEMA);
171 this.#migrateSchema();
172 }
173
174 #migrateSchema ()
175 {
176 const bagColumns = this.db.prepare("PRAGMA table_info(character_bags)").all();
177 if (!bagColumns.some((column) => column.name === "skills_blob"))
178 {
179 this.db.exec("ALTER TABLE character_bags ADD COLUMN skills_blob TEXT NOT NULL DEFAULT '{}'");
180 }
181 const characterColumns = this.db.prepare("PRAGMA table_info(characters)").all();
182 if (!characterColumns.some((column) => column.name === "is_dead"))
183 { this.db.exec("ALTER TABLE characters ADD COLUMN is_dead INTEGER NOT NULL DEFAULT 0"); }
184 if (!characterColumns.some((column) => column.name === "hair_style"))
185 {
186 this.db.exec("ALTER TABLE characters ADD COLUMN hair_style TEXT");
187 this.db.exec("ALTER TABLE characters ADD COLUMN hair_hue REAL NOT NULL DEFAULT 30");
188 this.db.exec("ALTER TABLE characters ADD COLUMN hair_saturation REAL NOT NULL DEFAULT 0.55");
189 this.db.exec("ALTER TABLE characters ADD COLUMN hair_darkness REAL NOT NULL DEFAULT 0.45");
190 }
191 const courierColumns = this.db.prepare("PRAGMA table_info(courier_transits)").all();
192 if (!courierColumns.some((column) => column.name === "vault_character_id"))
193 { this.db.exec("ALTER TABLE courier_transits ADD COLUMN vault_character_id INTEGER"); }
194 this.#grantLanternsToExistingCharacters();
195 }
196
197 #grantLanternsToExistingCharacters ()
198 {
199 const def = itemDefinition(ItemId.lantern);
200 if (!def)
201 { return; }
202 const rows = this.db.prepare("SELECT character_id, inventory_blob, equipment_blob FROM character_bags").all();
203 const update = this.db.prepare(`
204 UPDATE character_bags
205 SET inventory_blob = ?, equipment_blob = ?
206 WHERE character_id = ?
207 `);
208 for (const row of rows)
209 {
210 const inventory = JSON.parse(row.inventory_blob || "[]");
211 const equipment = JSON.parse(row.equipment_blob || "{}");
212 if (bagContainsItem(inventory, equipment, ItemId.lantern))
213 { continue; }
214 if (!equipment.slots?.offhand)
215 {
216 equipment.slots = equipment.slots ?? {};
217 equipment.slots.offhand = { itemId: ItemId.lantern, quantity: 1 };
218 }
219 else
220 {
221 const result = insertStack(inventory, { itemId: ItemId.lantern, quantity: 1 }, def);
222 if (!result.ok)
223 { continue; }
224 inventory.splice(0, inventory.length, ...result.slots);
225 }
226 update.run(JSON.stringify(inventory), JSON.stringify(equipment), row.character_id);
227 }
228 }
229
230 getAccountBySubject (authSubject)
231 {
232 return this.db.prepare("SELECT * FROM accounts WHERE auth_subject = ?").get(authSubject) ?? null;
233 }
234
235 createAccount (authSubject, isAdmin, email = null)
236 {
237 this.db.prepare("INSERT INTO accounts (auth_subject, email, is_admin) VALUES (?, ?, ?)").run(
238 authSubject,
239 email,
240 isAdmin ? 1 : 0
241 );
242 return this.getAccountBySubject(authSubject);
243 }
244
245 ensureAccount (authSubject, isAdmin, email = null)
246 {
247 const existing = this.getAccountBySubject(authSubject);
248 if (existing)
249 { return existing; }
250
251 return this.createAccount(authSubject, isAdmin, email);
252 }
253
254 listCharacters (accountId)
255 {
256 return this.db.prepare(`
257 SELECT * FROM characters
258 WHERE account_id = ?
259 ORDER BY character_id ASC
260 `).all(accountId);
261 }
262
263 getCharacter (characterId)
264 {
265 return this.db.prepare("SELECT * FROM characters WHERE character_id = ?").get(characterId) ?? null;
266 }
267
268 findSurnameClaim (surnameNormalized)
269 {
270 return this.db.prepare("SELECT * FROM surname_claims WHERE surname_normalized = ?").get(surnameNormalized) ?? null;
271 }
272
273 createCharacter (accountId, firstName, lastName, sex, tileX, tileY, hair = null, starterSettings = null)
274 {
275 const firstNormalized = normalizeName(firstName);
276 const lastNormalized = normalizeName(lastName);
277 const hairStyle = hair?.style ?? null;
278 const hairHue = Number(hair?.hue) || 30;
279 const hairSaturation = Number(hair?.saturation);
280 const hairDarkness = Number(hair?.darkness);
281 this.db.exec("BEGIN");
282 try
283 {
284 this.db.prepare(`
285 INSERT INTO characters (
286 account_id, first_name, last_name, first_normalized, last_normalized, sex, tile_x, tile_y,
287 hair_style, hair_hue, hair_saturation, hair_darkness
288 ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
289 `).run(
290 accountId,
291 firstName,
292 lastName,
293 firstNormalized,
294 lastNormalized,
295 sex,
296 tileX,
297 tileY,
298 hairStyle,
299 hairHue,
300 Number.isFinite(hairSaturation) ? hairSaturation : 0.55,
301 Number.isFinite(hairDarkness) ? hairDarkness : 0.45
302 );
303 const characterId = Number(this.db.prepare("SELECT last_insert_rowid() AS id").get().id);
304
305 const claim = this.findSurnameClaim(lastNormalized);
306 if (!claim)
307 {
308 this.db.prepare("INSERT INTO surname_claims (surname_normalized, account_id) VALUES (?, ?)").run(
309 lastNormalized,
310 accountId
311 );
312 }
313
314 this.db.exec("COMMIT");
315 this.#ensureBag(characterId, starterSettings);
316 return this.getCharacter(characterId);
317 }
318 catch (error)
319 {
320 this.db.exec("ROLLBACK");
321 throw error;
322 }
323 }
324
325 updateCharacterHair (characterId, { style, hue, saturation, darkness })
326 {
327 this.db.prepare(`
328 UPDATE characters
329 SET hair_style = ?, hair_hue = ?, hair_saturation = ?, hair_darkness = ?
330 WHERE character_id = ?
331 `).run(
332 style ?? null,
333 Number(hue) || 30,
334 Number.isFinite(Number(saturation)) ? Number(saturation) : 0.55,
335 Number.isFinite(Number(darkness)) ? Number(darkness) : 0.45,
336 characterId | 0
337 );
338 return this.getCharacter(characterId);
339 }
340
341 scheduleDeletion (characterId, at)
342 {
343 this.db.prepare("UPDATE characters SET deletion_scheduled_at = ? WHERE character_id = ?").run(at, characterId);
344 }
345
346 cancelDeletion (characterId)
347 {
348 this.db.prepare("UPDATE characters SET deletion_scheduled_at = NULL WHERE character_id = ?").run(characterId);
349 }
350
351 deleteCharacter (characterId)
352 {
353 const character = this.getCharacter(characterId);
354 if (!character)
355 { return; }
356
357 this.db.exec("BEGIN");
358 try
359 {
360 this.#moveGoodsToInheritance(character);
361 this.db.prepare("DELETE FROM character_bags WHERE character_id = ?").run(characterId);
362 this.db.prepare("DELETE FROM characters WHERE character_id = ?").run(characterId);
363 this.db.prepare("DELETE FROM friend_requests WHERE from_character_id = ? OR to_character_id = ?").run(
364 characterId,
365 characterId
366 );
367 this.db.prepare("DELETE FROM friendships WHERE character_id_a = ? OR character_id_b = ?").run(
368 characterId,
369 characterId
370 );
371 const remaining = this.db.prepare(`
372 SELECT COUNT(*) AS count FROM characters
373 WHERE account_id = ? AND last_normalized = ?
374 `).get(character.account_id, character.last_normalized);
375 if (!remaining.count)
376 {
377 this.db.prepare("DELETE FROM surname_claims WHERE surname_normalized = ? AND account_id = ?").run(
378 character.last_normalized,
379 character.account_id
380 );
381 }
382 this.db.exec("COMMIT");
383 }
384 catch (error)
385 {
386 this.db.exec("ROLLBACK");
387 throw error;
388 }
389 }
390
391 saveCharacterPosition (characterId, tileX, tileY, gait)
392 {
393 this.db.prepare("UPDATE characters SET tile_x = ?, tile_y = ?, gait = ? WHERE character_id = ?").run(
394 tileX,
395 tileY,
396 gait,
397 characterId
398 );
399 }
400
401 setCharacterDead (characterId, isDead)
402 {
403 this.db.prepare("UPDATE characters SET is_dead = ? WHERE character_id = ?").run(
404 isDead ? 1 : 0,
405 characterId
406 );
407 }
408
409 listDueDeletions (now)
410 {
411 return this.db.prepare(`
412 SELECT * FROM characters
413 WHERE deletion_scheduled_at IS NOT NULL AND deletion_scheduled_at <= ?
414 `).all(now);
415 }
416
417 getCharacterByName (firstName, lastName)
418 {
419 return this.db.prepare(`
420 SELECT * FROM characters
421 WHERE first_normalized = ? AND last_normalized = ?
422 `).get(normalizeName(firstName), normalizeName(lastName)) ?? null;
423 }
424
425 getCharacterBounty (characterId)
426 {
427 const row = this.db.prepare(`
428 SELECT amount FROM character_bounties WHERE character_id = ?
429 `).get(characterId | 0);
430 return Math.max(0, row?.amount | 0);
431 }
432
433 /** Add coins to a bounty; returns the new total. */
434 addCharacterBounty (characterId, amount)
435 {
436 const id = characterId | 0;
437 const add = Math.max(0, amount | 0);
438 if (add <= 0)
439 { return this.getCharacterBounty(id); }
440 this.db.prepare(`
441 INSERT INTO character_bounties (character_id, amount) VALUES (?, ?)
442 ON CONFLICT(character_id) DO UPDATE SET amount = amount + excluded.amount
443 `).run(id, add);
444 return this.getCharacterBounty(id);
445 }
446
447 /** Zero out and return the previous bounty amount. */
448 takeCharacterBounty (characterId)
449 {
450 const id = characterId | 0;
451 const amount = this.getCharacterBounty(id);
452 if (amount <= 0)
453 { return 0; }
454 this.db.prepare(`
455 INSERT INTO character_bounties (character_id, amount) VALUES (?, 0)
456 ON CONFLICT(character_id) DO UPDATE SET amount = 0
457 `).run(id);
458 return amount;
459 }
460
461 listFriendRecords (characterId)
462 {
463 return this.db.prepare(`
464 SELECT
465 CASE WHEN character_id_a = ? THEN character_id_b ELSE character_id_a END AS character_id,
466 created_at
467 FROM friendships
468 WHERE character_id_a = ? OR character_id_b = ?
469 ORDER BY created_at ASC
470 `).all(characterId, characterId, characterId);
471 }
472
473 areFriends (characterId, otherId)
474 {
475 const [a, b] = pairIds(characterId, otherId);
476 return !!this.db.prepare(`
477 SELECT 1 FROM friendships WHERE character_id_a = ? AND character_id_b = ?
478 `).get(a, b);
479 }
480
481 insertFriendship (characterId, otherId)
482 {
483 const [a, b] = pairIds(characterId, otherId);
484 this.db.prepare(`
485 INSERT INTO friendships (character_id_a, character_id_b, created_at)
486 VALUES (?, ?, ?)
487 `).run(a, b, Date.now());
488 }
489
490 deleteFriendship (characterId, otherId)
491 {
492 const [a, b] = pairIds(characterId, otherId);
493 this.db.prepare(`
494 DELETE FROM friendships WHERE character_id_a = ? AND character_id_b = ?
495 `).run(a, b);
496 }
497
498 listIncomingRequests (characterId)
499 {
500 return this.db.prepare(`
501 SELECT request_id, from_character_id, to_character_id, created_at
502 FROM friend_requests
503 WHERE to_character_id = ?
504 ORDER BY created_at ASC
505 `).all(characterId);
506 }
507
508 listOutgoingRequests (characterId)
509 {
510 return this.db.prepare(`
511 SELECT request_id, from_character_id, to_character_id, created_at
512 FROM friend_requests
513 WHERE from_character_id = ?
514 ORDER BY created_at ASC
515 `).all(characterId);
516 }
517
518 getFriendRequest (fromId, toId)
519 {
520 return this.db.prepare(`
521 SELECT * FROM friend_requests
522 WHERE from_character_id = ? AND to_character_id = ?
523 `).get(fromId, toId) ?? null;
524 }
525
526 insertFriendRequest (fromId, toId)
527 {
528 this.db.prepare(`
529 INSERT INTO friend_requests (from_character_id, to_character_id, created_at)
530 VALUES (?, ?, ?)
531 `).run(fromId, toId, Date.now());
532 }
533
534 deleteFriendRequest (fromId, toId)
535 {
536 this.db.prepare(`
537 DELETE FROM friend_requests
538 WHERE from_character_id = ? AND to_character_id = ?
539 `).run(fromId, toId);
540 }
541
542 listPersistentInRegion (minX, minY, maxX, maxY)
543 {
544 return this.db.prepare(`
545 SELECT entity_id, tile_x, tile_y, components_blob
546 FROM persistent_entities
547 WHERE tile_x >= ? AND tile_x <= ? AND tile_y >= ? AND tile_y <= ?
548 `).all(minX, maxX, minY, maxY);
549 }
550
551 replacePersistentInRegion (minX, minY, maxX, maxY, entities)
552 {
553 this.db.exec("BEGIN");
554 try
555 {
556 this.db.prepare(`
557 DELETE FROM persistent_entities
558 WHERE tile_x >= ? AND tile_x <= ? AND tile_y >= ? AND tile_y <= ?
559 `).run(minX, maxX, minY, maxY);
560 const insert = this.db.prepare(`
561 INSERT INTO persistent_entities (entity_id, tile_x, tile_y, components_blob)
562 VALUES (?, ?, ?, ?)
563 `);
564 for (const entity of entities)
565 {
566 insert.run(
567 entity.entityId,
568 entity.tileX,
569 entity.tileY,
570 JSON.stringify(entity.payload?.components ?? entity.components)
571 );
572 }
573 this.db.exec("COMMIT");
574 }
575 catch (error)
576 {
577 this.db.exec("ROLLBACK");
578 throw error;
579 }
580 }
581
582 #ensureBag (characterId, starterSettings = null)
583 {
584 const existing = this.db.prepare("SELECT * FROM character_bags WHERE character_id = ?").get(characterId);
585 if (existing)
586 { return parseBag(existing, characterId); }
587 const contents = buildStarterBagContents(starterSettings);
588 this.db.prepare(`
589 INSERT INTO character_bags (
590 character_id, inventory_blob, equipment_blob, currency, vault_blob, vault_currency, skills_blob
591 ) VALUES (?, ?, ?, 0, ?, 0, ?)
592 `).run(
593 characterId,
594 JSON.stringify(contents.slots),
595 JSON.stringify(contents.equipment),
596 JSON.stringify({ slots: [], reserved: [] }),
597 JSON.stringify(storedSkillXp(defaultPlayerSkills()))
598 );
599 return this.getBag(characterId);
600 }
601
602 getBag (characterId)
603 {
604 const row = this.db.prepare("SELECT * FROM character_bags WHERE character_id = ?").get(characterId);
605 if (row)
606 { return parseBag(row, characterId); }
607 if (this.getCharacter(characterId))
608 { return this.#ensureBag(characterId); }
609 return null;
610 }
611
612 saveBag (characterId, bag)
613 {
614 this.#ensureBag(characterId);
615 const migrated = migrateBagCoins(bag);
616 const vault = normalizeVault(
617 migrated.vaultReserved != null
618 ? { slots: migrated.vault, reserved: migrated.vaultReserved }
619 : migrated.vault
620 );
621 this.db.prepare(`
622 UPDATE character_bags
623 SET inventory_blob = ?, equipment_blob = ?, currency = 0, vault_blob = ?, vault_currency = 0
624 WHERE character_id = ?
625 `).run(
626 JSON.stringify(normalizeInventorySlots(migrated.inventory ?? emptySlots())),
627 JSON.stringify(migrated.equipment ?? emptyEquipment()),
628 JSON.stringify({ slots: vault.slots, reserved: vault.reserved }),
629 characterId
630 );
631 }
632
633 getSkills (characterId)
634 {
635 const row = this.db.prepare("SELECT skills_blob FROM character_bags WHERE character_id = ?").get(characterId);
636 if (row)
637 {
638 const parsed = JSON.parse(row.skills_blob || "{}");
639 const skills = normalizePlayerSkills(parsed);
640 // Persist floors (Health ≥ 10, others ≥ 1) for legacy under-leveled rows.
641 if (this.#skillsBlobNeedsRewrite(parsed, skills))
642 { this.saveSkills(characterId, skills); }
643 return skills;
644 }
645 if (this.getCharacter(characterId))
646 {
647 this.#ensureBag(characterId);
648 return this.getSkills(characterId);
649 }
650 return null;
651 }
652
653 /** True when stored blob is below the default minimum levels and must be rewritten. */
654 #skillsBlobNeedsRewrite (parsed, normalized)
655 {
656 const raw = parsed?.xp && typeof parsed.xp === "object" ? parsed.xp : parsed;
657 if (!raw || typeof raw !== "object")
658 { return true; }
659 for (const skillId of Object.keys(normalized.xp))
660 {
661 const stored = Math.max(0, Math.floor(Number(raw[skillId]) || 0));
662 if (stored < normalized.xp[skillId])
663 { return true; }
664 }
665 return false;
666 }
667
668 saveSkills (characterId, skills)
669 {
670 this.#ensureBag(characterId);
671 const xp = storedSkillXp(skills?.xp ? skills : { xp: skills });
672 this.db.prepare("UPDATE character_bags SET skills_blob = ? WHERE character_id = ?").run(
673 JSON.stringify({ xp }),
674 characterId
675 );
676 }
677
678 getInheritance (accountId)
679 {
680 const row = this.db.prepare("SELECT * FROM account_inheritance WHERE account_id = ?").get(accountId);
681 if (!row)
682 { return { accountId, slots: emptySlots() }; }
683 return migrateInheritanceCoins({
684 accountId,
685 slots: JSON.parse(row.slots_blob),
686 currency: row.currency
687 });
688 }
689
690 saveInheritance (accountId, inheritance)
691 {
692 const migrated = migrateInheritanceCoins(inheritance);
693 this.db.prepare(`
694 INSERT INTO account_inheritance (account_id, slots_blob, currency)
695 VALUES (?, ?, 0)
696 ON CONFLICT(account_id) DO UPDATE SET slots_blob = excluded.slots_blob, currency = 0
697 `).run(accountId, JSON.stringify(migrated.slots ?? emptySlots()));
698 }
699
700 #moveGoodsToInheritance (character)
701 {
702 const bag = this.getBag(character.character_id) ?? {
703 inventory: emptySlots(),
704 equipment: emptyEquipment(),
705 vault: [],
706 vaultReserved: []
707 };
708 const inheritance = this.getInheritance(character.account_id);
709 const vault = normalizeVault(
710 bag.vaultReserved != null
711 ? { slots: bag.vault, reserved: bag.vaultReserved }
712 : bag.vault
713 );
714 // Void outstanding cheques issued by this character: reservations return to available vault.
715 for (const reserved of vault.reserved ?? [])
716 {
717 const stack = {
718 itemId: reserved.itemId,
719 quantity: reserved.quantity,
720 origin: reserved.origin ?? null,
721 instance: reserved.instance ?? null
722 };
723 const def = itemDefinition(stack.itemId);
724 if (!def)
725 { continue; }
726 const restored = insertStackGrowable(vault.slots, stack, def);
727 vault.slots = restored.slots;
728 }
729 vault.reserved = [];
730 const incoming = [
731 ...(bag.inventory ?? []),
732 ...Object.values(bag.equipment?.slots ?? {}),
733 ...(vault.slots ?? [])
734 ].filter(Boolean);
735 let slots = inheritance.slots ?? emptySlots();
736 for (const stack of incoming)
737 {
738 const def = itemDefinition(stack.itemId);
739 if (!def)
740 { continue; }
741 const result = insertStack(slots, stack, def);
742 if (result.ok)
743 { slots = result.slots; }
744 else
745 {
746 const extra = result.slots;
747 for (const leftover of extra)
748 {
749 if (leftover)
750 { slots.push(leftover); }
751 }
752 slots.push({ ...stack, quantity: result.remainder });
753 }
754 }
755 this.saveInheritance(character.account_id, {
756 slots: publicSlots(slots)
757 });
758 }
759
760 nextGroundItemId ()
761 {
762 const row = this.db.prepare("SELECT MAX(ground_item_id) AS id FROM ground_items").get();
763 return (row?.id ?? 0) + 1;
764 }
765
766 listGroundItemsInRegion (minX, minY, maxX, maxY)
767 {
768 return this.db.prepare(`
769 SELECT * FROM ground_items
770 WHERE tile_x >= ? AND tile_x <= ? AND tile_y >= ? AND tile_y <= ?
771 `).all(minX, maxX, minY, maxY).map(parseGroundItem);
772 }
773
774 upsertGroundItem (item)
775 {
776 this.db.prepare(`
777 INSERT INTO ground_items (
778 ground_item_id, tile_x, tile_y, stack_blob, dropped_at, expires_at, source, dropped_by_character_id
779 ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
780 ON CONFLICT(ground_item_id) DO UPDATE SET
781 tile_x = excluded.tile_x,
782 tile_y = excluded.tile_y,
783 stack_blob = excluded.stack_blob,
784 dropped_at = excluded.dropped_at,
785 expires_at = excluded.expires_at,
786 source = excluded.source,
787 dropped_by_character_id = excluded.dropped_by_character_id
788 `).run(
789 item.groundItemId,
790 item.tileX,
791 item.tileY,
792 JSON.stringify(item.stack),
793 item.droppedAt,
794 item.expiresAt,
795 item.source,
796 item.droppedByCharacterId ?? null
797 );
798 }
799
800 deleteGroundItem (groundItemId)
801 {
802 this.db.prepare("DELETE FROM ground_items WHERE ground_item_id = ?").run(groundItemId);
803 }
804
805 insertCourierTransit (transit)
806 {
807 const now = transit.updatedAt ?? Date.now();
808 const result = this.db.prepare(`
809 INSERT INTO courier_transits (
810 sender_character_id, destination_kind, destination_character_id, cargo_blob,
811 origin_x, origin_y, dest_x, dest_y, traveled, status, entity_id, updated_at, created_at,
812 vault_character_id
813 ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
814 `).run(
815 transit.senderCharacterId,
816 transit.destinationKind,
817 transit.destinationCharacterId ?? null,
818 JSON.stringify(transit.cargo),
819 transit.originX,
820 transit.originY,
821 transit.destX,
822 transit.destY,
823 transit.traveled ?? 0,
824 transit.status ?? "in_transit",
825 transit.entityId ?? null,
826 now,
827 transit.createdAt ?? now,
828 transit.vaultCharacterId ?? null
829 );
830 return this.getCourierTransit(Number(result.lastInsertRowid));
831 }
832
833 getCourierTransit (courierId)
834 {
835 const row = this.db.prepare("SELECT * FROM courier_transits WHERE courier_id = ?").get(courierId);
836 return row ? parseCourierTransit(row) : null;
837 }
838
839 listActiveCourierTransits ()
840 {
841 return this.db.prepare(`
842 SELECT * FROM courier_transits WHERE status = 'in_transit' ORDER BY courier_id ASC
843 `).all().map(parseCourierTransit);
844 }
845
846 updateCourierTransit (transit)
847 {
848 this.db.prepare(`
849 UPDATE courier_transits
850 SET traveled = ?, status = ?, entity_id = ?, updated_at = ?,
851 origin_x = ?, origin_y = ?, dest_x = ?, dest_y = ?,
852 destination_kind = ?, destination_character_id = ?, vault_character_id = ?
853 WHERE courier_id = ?
854 `).run(
855 transit.traveled ?? 0,
856 transit.status,
857 transit.entityId ?? null,
858 transit.updatedAt ?? Date.now(),
859 transit.originX,
860 transit.originY,
861 transit.destX,
862 transit.destY,
863 transit.destinationKind,
864 transit.destinationCharacterId ?? null,
865 transit.vaultCharacterId ?? null,
866 transit.courierId
867 );
868 }
869
870 appendBankRestNotice (characterId, entry)
871 {
872 const qty = Math.max(1, entry.quantity | 0);
873 this.db.prepare(`
874 INSERT INTO bank_rest_notices (character_id, item_id, item_name, quantity, source_name, created_at)
875 VALUES (?, ?, ?, ?, ?, ?)
876 `).run(
877 characterId,
878 entry.itemId | 0,
879 String(entry.itemName ?? "Item"),
880 qty,
881 String(entry.sourceName ?? "unknown source"),
882 entry.createdAt ?? Date.now()
883 );
884 }
885
886 listBankRestNotices (characterId)
887 {
888 return this.db.prepare(`
889 SELECT * FROM bank_rest_notices WHERE character_id = ? ORDER BY notice_id ASC
890 `).all(characterId).map((row) => ({
891 noticeId: row.notice_id,
892 characterId: row.character_id,
893 itemId: row.item_id,
894 itemName: row.item_name,
895 quantity: row.quantity,
896 sourceName: row.source_name,
897 createdAt: row.created_at
898 }));
899 }
900
901 clearBankRestNotices (characterId)
902 {
903 this.db.prepare("DELETE FROM bank_rest_notices WHERE character_id = ?").run(characterId);
904 }
905
906 insertHeldCourierDelivery (delivery)
907 {
908 const now = delivery.createdAt ?? Date.now();
909 const result = this.db.prepare(`
910 INSERT INTO held_courier_deliveries (
911 character_id, sender_character_id, origin_tile_x, origin_tile_y,
912 destination_kind, destination_character_id, vault_character_id, cargo_blob, created_at
913 ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
914 `).run(
915 delivery.characterId,
916 delivery.senderCharacterId,
917 delivery.originTileX | 0,
918 delivery.originTileY | 0,
919 delivery.destinationKind,
920 delivery.destinationCharacterId ?? null,
921 delivery.vaultCharacterId ?? null,
922 JSON.stringify(delivery.cargo),
923 now
924 );
925 return Number(result.lastInsertRowid);
926 }
927
928 listHeldCourierDeliveries (characterId)
929 {
930 return this.db.prepare(`
931 SELECT * FROM held_courier_deliveries WHERE character_id = ? ORDER BY hold_id ASC
932 `).all(characterId).map(parseHeldCourierDelivery);
933 }
934
935 deleteHeldCourierDelivery (holdId)
936 {
937 this.db.prepare("DELETE FROM held_courier_deliveries WHERE hold_id = ?").run(holdId);
938 }
939
940 insertMarketOrder (order)
941 {
942 const result = this.db.prepare(`
943 INSERT INTO market_orders (
944 character_id, side, item_id, quantity, limit_unit_price,
945 escrow_coins, escrow_stack_blob, created_at, status
946 ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
947 `).run(
948 order.characterId,
949 order.side,
950 order.itemId | 0,
951 order.quantity | 0,
952 order.limitUnitPrice | 0,
953 order.escrowCoins | 0 || 0,
954 order.escrowStack ? JSON.stringify(order.escrowStack) : null,
955 order.createdAt ?? Date.now(),
956 order.status ?? "open"
957 );
958 return this.getMarketOrder(Number(result.lastInsertRowid));
959 }
960
961 getMarketOrder (orderId)
962 {
963 const row = this.db.prepare("SELECT * FROM market_orders WHERE order_id = ?").get(orderId);
964 return row ? parseMarketOrder(row) : null;
965 }
966
967 updateMarketOrder (order)
968 {
969 this.db.prepare(`
970 UPDATE market_orders
971 SET quantity = ?, escrow_coins = ?, escrow_stack_blob = ?, status = ?
972 WHERE order_id = ?
973 `).run(
974 order.quantity | 0,
975 order.escrowCoins | 0 || 0,
976 order.escrowStack ? JSON.stringify(order.escrowStack) : null,
977 order.status,
978 order.orderId
979 );
980 return this.getMarketOrder(order.orderId);
981 }
982
983 listOpenMarketOrdersForCharacter (characterId)
984 {
985 return this.db.prepare(`
986 SELECT * FROM market_orders
987 WHERE character_id = ? AND status = 'open'
988 ORDER BY created_at ASC, order_id ASC
989 `).all(characterId).map(parseMarketOrder);
990 }
991
992 listOpenBuyOrdersForItem (itemId)
993 {
994 return this.db.prepare(`
995 SELECT * FROM market_orders
996 WHERE status = 'open' AND side = 'buy' AND item_id = ?
997 ORDER BY limit_unit_price DESC, created_at ASC, order_id ASC
998 `).all(itemId | 0).map(parseMarketOrder);
999 }
1000
1001 listOpenSellOrdersForItem (itemId)
1002 {
1003 return this.db.prepare(`
1004 SELECT * FROM market_orders
1005 WHERE status = 'open' AND side = 'sell' AND item_id = ?
1006 ORDER BY limit_unit_price ASC, created_at ASC, order_id ASC
1007 `).all(itemId | 0).map(parseMarketOrder);
1008 }
1009
1010 listExpiredMarketOrders (createdBefore)
1011 {
1012 return this.db.prepare(`
1013 SELECT * FROM market_orders
1014 WHERE status = 'open' AND created_at < ?
1015 ORDER BY created_at ASC, order_id ASC
1016 `).all(createdBefore).map(parseMarketOrder);
1017 }
1018
1019 insertMarketTrade (trade)
1020 {
1021 const result = this.db.prepare(`
1022 INSERT INTO market_trades (
1023 buy_order_id, sell_order_id, item_id, quantity, unit_price,
1024 buyer_character_id, seller_character_id, created_at
1025 ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
1026 `).run(
1027 trade.buyOrderId,
1028 trade.sellOrderId,
1029 trade.itemId | 0,
1030 trade.quantity | 0,
1031 trade.unitPrice | 0,
1032 trade.buyerCharacterId,
1033 trade.sellerCharacterId,
1034 trade.createdAt ?? Date.now()
1035 );
1036 return Number(result.lastInsertRowid);
1037 }
1038
1039 upsertItemLastSale (sale)
1040 {
1041 this.db.prepare(`
1042 INSERT INTO item_last_sales (item_id, last_trade_price, last_trade_at, price_from)
1043 VALUES (?, ?, ?, ?)
1044 ON CONFLICT(item_id) DO UPDATE SET
1045 last_trade_price = excluded.last_trade_price,
1046 last_trade_at = excluded.last_trade_at,
1047 price_from = excluded.price_from
1048 `).run(
1049 sale.itemId | 0,
1050 sale.lastTradePrice | 0,
1051 sale.lastTradeAt,
1052 Math.round(Number(sale.priceFrom) || 0)
1053 );
1054 }
1055
1056 listItemLastSales ()
1057 {
1058 return this.db.prepare("SELECT * FROM item_last_sales").all().map((row) => ({
1059 itemId: row.item_id,
1060 lastTradePrice: row.last_trade_price,
1061 lastTradeAt: row.last_trade_at,
1062 priceFrom: row.price_from
1063 }));
1064 }
1065
1066 close ()
1067 {
1068 this.db.close();
1069 }
1070}
1071
1072export function normalizeName (value)
1073{
1074 return String(value ?? "").trim().toLocaleLowerCase();
1075}
1076
1077function pairIds (left, right)
1078{
1079 return left < right ? [left, right] : [right, left];
1080}
1081
1082function parseGroundItem (row)
1083{
1084 return {
1085 groundItemId: row.ground_item_id,
1086 tileX: row.tile_x,
1087 tileY: row.tile_y,
1088 stack: JSON.parse(row.stack_blob),
1089 droppedAt: row.dropped_at,
1090 expiresAt: row.expires_at,
1091 source: row.source,
1092 droppedByCharacterId: row.dropped_by_character_id
1093 };
1094}
1095
1096function parseMarketOrder (row)
1097{
1098 return {
1099 orderId: row.order_id,
1100 characterId: row.character_id,
1101 side: row.side,
1102 itemId: row.item_id,
1103 quantity: row.quantity,
1104 limitUnitPrice: row.limit_unit_price,
1105 escrowCoins: row.escrow_coins | 0,
1106 escrowStack: row.escrow_stack_blob ? JSON.parse(row.escrow_stack_blob) : null,
1107 createdAt: row.created_at,
1108 status: row.status
1109 };
1110}
1111
1112function bagContainsItem (inventory, equipment, itemId)
1113{
1114 for (const stack of inventory ?? [])
1115 {
1116 if (stack?.itemId === itemId)
1117 { return true; }
1118 }
1119 for (const stack of Object.values(equipment?.slots ?? {}))
1120 {
1121 if (stack?.itemId === itemId)
1122 { return true; }
1123 }
1124 return false;
1125}
1126
1127function parseBag (row, characterId)
1128{
1129 const vault = normalizeVault(JSON.parse(row.vault_blob));
1130 return migrateBagCoins({
1131 characterId,
1132 inventory: normalizeInventorySlots(JSON.parse(row.inventory_blob)),
1133 equipment: JSON.parse(row.equipment_blob),
1134 currency: row.currency,
1135 vault: vault.slots,
1136 vaultReserved: vault.reserved,
1137 vaultCurrency: row.vault_currency
1138 });
1139}
1140
1141function parseCourierTransit (row)
1142{
1143 return {
1144 courierId: row.courier_id,
1145 senderCharacterId: row.sender_character_id,
1146 destinationKind: row.destination_kind,
1147 destinationCharacterId: row.destination_character_id,
1148 vaultCharacterId: row.vault_character_id ?? null,
1149 cargo: JSON.parse(row.cargo_blob),
1150 originX: row.origin_x,
1151 originY: row.origin_y,
1152 destX: row.dest_x,
1153 destY: row.dest_y,
1154 traveled: row.traveled,
1155 status: row.status,
1156 entityId: row.entity_id,
1157 updatedAt: row.updated_at,
1158 createdAt: row.created_at
1159 };
1160}
1161
1162function parseHeldCourierDelivery (row)
1163{
1164 return {
1165 holdId: row.hold_id,
1166 characterId: row.character_id,
1167 senderCharacterId: row.sender_character_id,
1168 originTileX: row.origin_tile_x,
1169 originTileY: row.origin_tile_y,
1170 destinationKind: row.destination_kind,
1171 destinationCharacterId: row.destination_character_id,
1172 vaultCharacterId: row.vault_character_id,
1173 cargo: JSON.parse(row.cargo_blob),
1174 createdAt: row.created_at
1175 };
1176}