BlueGit

timfa questward

Files

questward / server / src / persistence / stateDb.js

stateDb.js

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