BlueGit

timfa questward

Files

questward / server / src / persistence / stateDb.js

stateDb.js

stateDb.js 21.5 KiB Download Raw
1import { openDatabase } from "./database.js";
2import { emptyEquipment, emptySlots, insertStack, insertStackGrowable, normalizeVault, normalizeInventorySlots, publicSlots, migrateBagCoins, migrateInheritanceCoins } from "../../../shared/items/inventory.js";
3import { STARTER_ITEMS, ItemId, itemDefinition } from "../../../shared/definitions/items.js";
4import { normalizePlayerSkills, defaultPlayerSkills, storedSkillXp } from "../../../shared/skills/state.js";
5
6const STATE_SCHEMA = `
7CREATE TABLE IF NOT EXISTS accounts (
8 account_id INTEGER PRIMARY KEY AUTOINCREMENT,
9 auth_subject TEXT NOT NULL UNIQUE,
10 email TEXT,
11 is_admin INTEGER NOT NULL DEFAULT 0
12);
13
14CREATE TABLE IF NOT EXISTS surname_claims (
15 surname_normalized TEXT PRIMARY KEY,
16 account_id INTEGER NOT NULL
17);
18
19CREATE TABLE IF NOT EXISTS characters (
20 character_id INTEGER PRIMARY KEY AUTOINCREMENT,
21 account_id INTEGER NOT NULL,
22 first_name TEXT NOT NULL,
23 last_name TEXT NOT NULL,
24 first_normalized TEXT NOT NULL,
25 last_normalized TEXT NOT NULL,
26 sex TEXT NOT NULL,
27 tile_x INTEGER NOT NULL DEFAULT 8,
28 tile_y INTEGER NOT NULL DEFAULT 8,
29 gait TEXT NOT NULL DEFAULT 'walk',
30 is_dead INTEGER NOT NULL DEFAULT 0,
31 deletion_scheduled_at INTEGER
32);
33CREATE TABLE IF NOT EXISTS friend_requests (
34 request_id INTEGER PRIMARY KEY AUTOINCREMENT,
35 from_character_id INTEGER NOT NULL,
36 to_character_id INTEGER NOT NULL,
37 created_at INTEGER NOT NULL,
38 UNIQUE (from_character_id, to_character_id)
39);
40
41CREATE TABLE IF NOT EXISTS friendships (
42 character_id_a INTEGER NOT NULL,
43 character_id_b INTEGER NOT NULL,
44 created_at INTEGER NOT NULL,
45 PRIMARY KEY (character_id_a, character_id_b)
46);
47
48CREATE TABLE IF NOT EXISTS persistent_entities (
49 entity_id INTEGER PRIMARY KEY,
50 tile_x INTEGER NOT NULL,
51 tile_y INTEGER NOT NULL,
52 components_blob TEXT NOT NULL
53);
54
55CREATE TABLE IF NOT EXISTS character_bags (
56 character_id INTEGER PRIMARY KEY,
57 inventory_blob TEXT NOT NULL,
58 equipment_blob TEXT NOT NULL,
59 currency INTEGER NOT NULL DEFAULT 0,
60 vault_blob TEXT NOT NULL,
61 vault_currency INTEGER NOT NULL DEFAULT 0,
62 skills_blob TEXT NOT NULL DEFAULT '{}'
63);
64
65CREATE TABLE IF NOT EXISTS account_inheritance (
66 account_id INTEGER PRIMARY KEY,
67 slots_blob TEXT NOT NULL,
68 currency INTEGER NOT NULL DEFAULT 0
69);
70
71CREATE TABLE IF NOT EXISTS ground_items (
72 ground_item_id INTEGER PRIMARY KEY,
73 tile_x INTEGER NOT NULL,
74 tile_y INTEGER NOT NULL,
75 stack_blob TEXT NOT NULL,
76 dropped_at INTEGER NOT NULL,
77 expires_at INTEGER NOT NULL,
78 source TEXT NOT NULL,
79 dropped_by_character_id INTEGER
80);
81
82CREATE TABLE IF NOT EXISTS courier_transits (
83 courier_id INTEGER PRIMARY KEY AUTOINCREMENT,
84 sender_character_id INTEGER NOT NULL,
85 destination_kind TEXT NOT NULL,
86 destination_character_id INTEGER,
87 cargo_blob TEXT NOT NULL,
88 origin_x REAL NOT NULL,
89 origin_y REAL NOT NULL,
90 dest_x REAL NOT NULL,
91 dest_y REAL NOT NULL,
92 traveled REAL NOT NULL DEFAULT 0,
93 status TEXT NOT NULL,
94 entity_id INTEGER,
95 updated_at INTEGER NOT NULL,
96 created_at INTEGER NOT NULL
97);
98`;
99
100export class StateDatabase
101{
102 constructor (filePath)
103 {
104 this.db = openDatabase(filePath);
105 this.db.exec(STATE_SCHEMA);
106 this.#migrateSchema();
107 }
108
109 #migrateSchema ()
110 {
111 const bagColumns = this.db.prepare("PRAGMA table_info(character_bags)").all();
112 if (!bagColumns.some((column) => column.name === "skills_blob"))
113 {
114 this.db.exec("ALTER TABLE character_bags ADD COLUMN skills_blob TEXT NOT NULL DEFAULT '{}'");
115 }
116 const characterColumns = this.db.prepare("PRAGMA table_info(characters)").all();
117 if (!characterColumns.some((column) => column.name === "is_dead"))
118 { this.db.exec("ALTER TABLE characters ADD COLUMN is_dead INTEGER NOT NULL DEFAULT 0"); }
119 this.#grantLanternsToExistingCharacters();
120 }
121
122 #grantLanternsToExistingCharacters ()
123 {
124 const def = itemDefinition(ItemId.lantern);
125 if (!def)
126 { return; }
127 const rows = this.db.prepare("SELECT character_id, inventory_blob, equipment_blob FROM character_bags").all();
128 const update = this.db.prepare(`
129 UPDATE character_bags
130 SET inventory_blob = ?, equipment_blob = ?
131 WHERE character_id = ?
132 `);
133 for (const row of rows)
134 {
135 const inventory = JSON.parse(row.inventory_blob || "[]");
136 const equipment = JSON.parse(row.equipment_blob || "{}");
137 if (bagContainsItem(inventory, equipment, ItemId.lantern))
138 { continue; }
139 if (!equipment.slots?.offhand)
140 {
141 equipment.slots = equipment.slots ?? {};
142 equipment.slots.offhand = { itemId: ItemId.lantern, quantity: 1 };
143 }
144 else
145 {
146 const result = insertStack(inventory, { itemId: ItemId.lantern, quantity: 1 }, def);
147 if (!result.ok)
148 { continue; }
149 inventory.splice(0, inventory.length, ...result.slots);
150 }
151 update.run(JSON.stringify(inventory), JSON.stringify(equipment), row.character_id);
152 }
153 }
154
155 getAccountBySubject (authSubject)
156 {
157 return this.db.prepare("SELECT * FROM accounts WHERE auth_subject = ?").get(authSubject) ?? null;
158 }
159
160 createAccount (authSubject, isAdmin, email = null)
161 {
162 this.db.prepare("INSERT INTO accounts (auth_subject, email, is_admin) VALUES (?, ?, ?)").run(
163 authSubject,
164 email,
165 isAdmin ? 1 : 0
166 );
167 return this.getAccountBySubject(authSubject);
168 }
169
170 ensureAccount (authSubject, isAdmin, email = null)
171 {
172 const existing = this.getAccountBySubject(authSubject);
173 if (existing)
174 { return existing; }
175
176 return this.createAccount(authSubject, isAdmin, email);
177 }
178
179 listCharacters (accountId)
180 {
181 return this.db.prepare(`
182 SELECT * FROM characters
183 WHERE account_id = ?
184 ORDER BY character_id ASC
185 `).all(accountId);
186 }
187
188 getCharacter (characterId)
189 {
190 return this.db.prepare("SELECT * FROM characters WHERE character_id = ?").get(characterId) ?? null;
191 }
192
193 findSurnameClaim (surnameNormalized)
194 {
195 return this.db.prepare("SELECT * FROM surname_claims WHERE surname_normalized = ?").get(surnameNormalized) ?? null;
196 }
197
198 createCharacter (accountId, firstName, lastName, sex, tileX, tileY)
199 {
200 const firstNormalized = normalizeName(firstName);
201 const lastNormalized = normalizeName(lastName);
202 this.db.exec("BEGIN");
203 try
204 {
205 this.db.prepare(`
206 INSERT INTO characters (
207 account_id, first_name, last_name, first_normalized, last_normalized, sex, tile_x, tile_y
208 ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
209 `).run(accountId, firstName, lastName, firstNormalized, lastNormalized, sex, tileX, tileY);
210 const characterId = Number(this.db.prepare("SELECT last_insert_rowid() AS id").get().id);
211
212 const claim = this.findSurnameClaim(lastNormalized);
213 if (!claim)
214 {
215 this.db.prepare("INSERT INTO surname_claims (surname_normalized, account_id) VALUES (?, ?)").run(
216 lastNormalized,
217 accountId
218 );
219 }
220
221 this.db.exec("COMMIT");
222 this.#ensureBag(characterId);
223 return this.getCharacter(characterId);
224 }
225 catch (error)
226 {
227 this.db.exec("ROLLBACK");
228 throw error;
229 }
230 }
231
232 scheduleDeletion (characterId, at)
233 {
234 this.db.prepare("UPDATE characters SET deletion_scheduled_at = ? WHERE character_id = ?").run(at, characterId);
235 }
236
237 cancelDeletion (characterId)
238 {
239 this.db.prepare("UPDATE characters SET deletion_scheduled_at = NULL WHERE character_id = ?").run(characterId);
240 }
241
242 deleteCharacter (characterId)
243 {
244 const character = this.getCharacter(characterId);
245 if (!character)
246 { return; }
247
248 this.db.exec("BEGIN");
249 try
250 {
251 this.#moveGoodsToInheritance(character);
252 this.db.prepare("DELETE FROM character_bags WHERE character_id = ?").run(characterId);
253 this.db.prepare("DELETE FROM characters WHERE character_id = ?").run(characterId);
254 this.db.prepare("DELETE FROM friend_requests WHERE from_character_id = ? OR to_character_id = ?").run(
255 characterId,
256 characterId
257 );
258 this.db.prepare("DELETE FROM friendships WHERE character_id_a = ? OR character_id_b = ?").run(
259 characterId,
260 characterId
261 );
262 const remaining = this.db.prepare(`
263 SELECT COUNT(*) AS count FROM characters
264 WHERE account_id = ? AND last_normalized = ?
265 `).get(character.account_id, character.last_normalized);
266 if (!remaining.count)
267 {
268 this.db.prepare("DELETE FROM surname_claims WHERE surname_normalized = ? AND account_id = ?").run(
269 character.last_normalized,
270 character.account_id
271 );
272 }
273 this.db.exec("COMMIT");
274 }
275 catch (error)
276 {
277 this.db.exec("ROLLBACK");
278 throw error;
279 }
280 }
281
282 saveCharacterPosition (characterId, tileX, tileY, gait)
283 {
284 this.db.prepare("UPDATE characters SET tile_x = ?, tile_y = ?, gait = ? WHERE character_id = ?").run(
285 tileX,
286 tileY,
287 gait,
288 characterId
289 );
290 }
291
292 setCharacterDead (characterId, isDead)
293 {
294 this.db.prepare("UPDATE characters SET is_dead = ? WHERE character_id = ?").run(
295 isDead ? 1 : 0,
296 characterId
297 );
298 }
299
300 listDueDeletions (now)
301 {
302 return this.db.prepare(`
303 SELECT * FROM characters
304 WHERE deletion_scheduled_at IS NOT NULL AND deletion_scheduled_at <= ?
305 `).all(now);
306 }
307
308 getCharacterByName (firstName, lastName)
309 {
310 return this.db.prepare(`
311 SELECT * FROM characters
312 WHERE first_normalized = ? AND last_normalized = ?
313 `).get(normalizeName(firstName), normalizeName(lastName)) ?? null;
314 }
315
316 listFriendRecords (characterId)
317 {
318 return this.db.prepare(`
319 SELECT
320 CASE WHEN character_id_a = ? THEN character_id_b ELSE character_id_a END AS character_id,
321 created_at
322 FROM friendships
323 WHERE character_id_a = ? OR character_id_b = ?
324 ORDER BY created_at ASC
325 `).all(characterId, characterId, characterId);
326 }
327
328 areFriends (characterId, otherId)
329 {
330 const [a, b] = pairIds(characterId, otherId);
331 return !!this.db.prepare(`
332 SELECT 1 FROM friendships WHERE character_id_a = ? AND character_id_b = ?
333 `).get(a, b);
334 }
335
336 insertFriendship (characterId, otherId)
337 {
338 const [a, b] = pairIds(characterId, otherId);
339 this.db.prepare(`
340 INSERT INTO friendships (character_id_a, character_id_b, created_at)
341 VALUES (?, ?, ?)
342 `).run(a, b, Date.now());
343 }
344
345 deleteFriendship (characterId, otherId)
346 {
347 const [a, b] = pairIds(characterId, otherId);
348 this.db.prepare(`
349 DELETE FROM friendships WHERE character_id_a = ? AND character_id_b = ?
350 `).run(a, b);
351 }
352
353 listIncomingRequests (characterId)
354 {
355 return this.db.prepare(`
356 SELECT request_id, from_character_id, to_character_id, created_at
357 FROM friend_requests
358 WHERE to_character_id = ?
359 ORDER BY created_at ASC
360 `).all(characterId);
361 }
362
363 listOutgoingRequests (characterId)
364 {
365 return this.db.prepare(`
366 SELECT request_id, from_character_id, to_character_id, created_at
367 FROM friend_requests
368 WHERE from_character_id = ?
369 ORDER BY created_at ASC
370 `).all(characterId);
371 }
372
373 getFriendRequest (fromId, toId)
374 {
375 return this.db.prepare(`
376 SELECT * FROM friend_requests
377 WHERE from_character_id = ? AND to_character_id = ?
378 `).get(fromId, toId) ?? null;
379 }
380
381 insertFriendRequest (fromId, toId)
382 {
383 this.db.prepare(`
384 INSERT INTO friend_requests (from_character_id, to_character_id, created_at)
385 VALUES (?, ?, ?)
386 `).run(fromId, toId, Date.now());
387 }
388
389 deleteFriendRequest (fromId, toId)
390 {
391 this.db.prepare(`
392 DELETE FROM friend_requests
393 WHERE from_character_id = ? AND to_character_id = ?
394 `).run(fromId, toId);
395 }
396
397 listPersistentInRegion (minX, minY, maxX, maxY)
398 {
399 return this.db.prepare(`
400 SELECT entity_id, tile_x, tile_y, components_blob
401 FROM persistent_entities
402 WHERE tile_x >= ? AND tile_x <= ? AND tile_y >= ? AND tile_y <= ?
403 `).all(minX, maxX, minY, maxY);
404 }
405
406 replacePersistentInRegion (minX, minY, maxX, maxY, entities)
407 {
408 this.db.exec("BEGIN");
409 try
410 {
411 this.db.prepare(`
412 DELETE FROM persistent_entities
413 WHERE tile_x >= ? AND tile_x <= ? AND tile_y >= ? AND tile_y <= ?
414 `).run(minX, maxX, minY, maxY);
415 const insert = this.db.prepare(`
416 INSERT INTO persistent_entities (entity_id, tile_x, tile_y, components_blob)
417 VALUES (?, ?, ?, ?)
418 `);
419 for (const entity of entities)
420 {
421 insert.run(
422 entity.entityId,
423 entity.tileX,
424 entity.tileY,
425 JSON.stringify(entity.payload?.components ?? entity.components)
426 );
427 }
428 this.db.exec("COMMIT");
429 }
430 catch (error)
431 {
432 this.db.exec("ROLLBACK");
433 throw error;
434 }
435 }
436
437 #ensureBag (characterId)
438 {
439 const existing = this.db.prepare("SELECT * FROM character_bags WHERE character_id = ?").get(characterId);
440 if (existing)
441 { return parseBag(existing, characterId); }
442 const slots = emptySlots();
443 for (const starter of STARTER_ITEMS)
444 {
445 const result = insertStack(slots, { ...starter }, itemDefinition(starter.itemId));
446 if (result.ok)
447 { slots.splice(0, slots.length, ...result.slots); }
448 }
449 const equipment = emptyEquipment();
450 equipment.slots.offhand = { itemId: ItemId.lantern, quantity: 1 };
451 this.db.prepare(`
452 INSERT INTO character_bags (
453 character_id, inventory_blob, equipment_blob, currency, vault_blob, vault_currency, skills_blob
454 ) VALUES (?, ?, ?, 0, ?, 0, ?)
455 `).run(
456 characterId,
457 JSON.stringify(slots),
458 JSON.stringify(equipment),
459 JSON.stringify({ slots: [], reserved: [] }),
460 JSON.stringify(storedSkillXp(defaultPlayerSkills()))
461 );
462 return this.getBag(characterId);
463 }
464
465 getBag (characterId)
466 {
467 const row = this.db.prepare("SELECT * FROM character_bags WHERE character_id = ?").get(characterId);
468 if (row)
469 { return parseBag(row, characterId); }
470 if (this.getCharacter(characterId))
471 { return this.#ensureBag(characterId); }
472 return null;
473 }
474
475 saveBag (characterId, bag)
476 {
477 this.#ensureBag(characterId);
478 const migrated = migrateBagCoins(bag);
479 const vault = normalizeVault(
480 migrated.vaultReserved != null
481 ? { slots: migrated.vault, reserved: migrated.vaultReserved }
482 : migrated.vault
483 );
484 this.db.prepare(`
485 UPDATE character_bags
486 SET inventory_blob = ?, equipment_blob = ?, currency = 0, vault_blob = ?, vault_currency = 0
487 WHERE character_id = ?
488 `).run(
489 JSON.stringify(normalizeInventorySlots(migrated.inventory ?? emptySlots())),
490 JSON.stringify(migrated.equipment ?? emptyEquipment()),
491 JSON.stringify({ slots: vault.slots, reserved: vault.reserved }),
492 characterId
493 );
494 }
495
496 getSkills (characterId)
497 {
498 const row = this.db.prepare("SELECT skills_blob FROM character_bags WHERE character_id = ?").get(characterId);
499 if (row)
500 {
501 const parsed = JSON.parse(row.skills_blob || "{}");
502 const skills = normalizePlayerSkills(parsed);
503 const rawHealth = Number(
504 (parsed?.xp && typeof parsed.xp === "object" ? parsed.xp.health : parsed?.health) ?? 0
505 ) || 0;
506 // Persist the Health level-10 floor for legacy `{}` / under-leveled rows.
507 if (rawHealth < skills.xp.health)
508 { this.saveSkills(characterId, skills); }
509 return skills;
510 }
511 if (this.getCharacter(characterId))
512 {
513 this.#ensureBag(characterId);
514 return this.getSkills(characterId);
515 }
516 return null;
517 }
518
519 saveSkills (characterId, skills)
520 {
521 this.#ensureBag(characterId);
522 const xp = storedSkillXp(skills?.xp ? skills : { xp: skills });
523 this.db.prepare("UPDATE character_bags SET skills_blob = ? WHERE character_id = ?").run(
524 JSON.stringify({ xp }),
525 characterId
526 );
527 }
528
529 getInheritance (accountId)
530 {
531 const row = this.db.prepare("SELECT * FROM account_inheritance WHERE account_id = ?").get(accountId);
532 if (!row)
533 { return { accountId, slots: emptySlots() }; }
534 return migrateInheritanceCoins({
535 accountId,
536 slots: JSON.parse(row.slots_blob),
537 currency: row.currency
538 });
539 }
540
541 saveInheritance (accountId, inheritance)
542 {
543 const migrated = migrateInheritanceCoins(inheritance);
544 this.db.prepare(`
545 INSERT INTO account_inheritance (account_id, slots_blob, currency)
546 VALUES (?, ?, 0)
547 ON CONFLICT(account_id) DO UPDATE SET slots_blob = excluded.slots_blob, currency = 0
548 `).run(accountId, JSON.stringify(migrated.slots ?? emptySlots()));
549 }
550
551 #moveGoodsToInheritance (character)
552 {
553 const bag = this.getBag(character.character_id) ?? {
554 inventory: emptySlots(),
555 equipment: emptyEquipment(),
556 vault: [],
557 vaultReserved: []
558 };
559 const inheritance = this.getInheritance(character.account_id);
560 const vault = normalizeVault(
561 bag.vaultReserved != null
562 ? { slots: bag.vault, reserved: bag.vaultReserved }
563 : bag.vault
564 );
565 // Void outstanding cheques issued by this character: reservations return to available vault.
566 for (const reserved of vault.reserved ?? [])
567 {
568 const stack = {
569 itemId: reserved.itemId,
570 quantity: reserved.quantity,
571 origin: reserved.origin ?? null,
572 instance: reserved.instance ?? null
573 };
574 const def = itemDefinition(stack.itemId);
575 if (!def)
576 { continue; }
577 const restored = insertStackGrowable(vault.slots, stack, def);
578 vault.slots = restored.slots;
579 }
580 vault.reserved = [];
581 const incoming = [
582 ...(bag.inventory ?? []),
583 ...Object.values(bag.equipment?.slots ?? {}),
584 ...(vault.slots ?? [])
585 ].filter(Boolean);
586 let slots = inheritance.slots ?? emptySlots();
587 for (const stack of incoming)
588 {
589 const def = itemDefinition(stack.itemId);
590 if (!def)
591 { continue; }
592 const result = insertStack(slots, stack, def);
593 if (result.ok)
594 { slots = result.slots; }
595 else
596 {
597 const extra = result.slots;
598 for (const leftover of extra)
599 {
600 if (leftover)
601 { slots.push(leftover); }
602 }
603 slots.push({ ...stack, quantity: result.remainder });
604 }
605 }
606 this.saveInheritance(character.account_id, {
607 slots: publicSlots(slots)
608 });
609 }
610
611 nextGroundItemId ()
612 {
613 const row = this.db.prepare("SELECT MAX(ground_item_id) AS id FROM ground_items").get();
614 return (row?.id ?? 0) + 1;
615 }
616
617 listGroundItemsInRegion (minX, minY, maxX, maxY)
618 {
619 return this.db.prepare(`
620 SELECT * FROM ground_items
621 WHERE tile_x >= ? AND tile_x <= ? AND tile_y >= ? AND tile_y <= ?
622 `).all(minX, maxX, minY, maxY).map(parseGroundItem);
623 }
624
625 upsertGroundItem (item)
626 {
627 this.db.prepare(`
628 INSERT INTO ground_items (
629 ground_item_id, tile_x, tile_y, stack_blob, dropped_at, expires_at, source, dropped_by_character_id
630 ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
631 ON CONFLICT(ground_item_id) DO UPDATE SET
632 tile_x = excluded.tile_x,
633 tile_y = excluded.tile_y,
634 stack_blob = excluded.stack_blob,
635 dropped_at = excluded.dropped_at,
636 expires_at = excluded.expires_at,
637 source = excluded.source,
638 dropped_by_character_id = excluded.dropped_by_character_id
639 `).run(
640 item.groundItemId,
641 item.tileX,
642 item.tileY,
643 JSON.stringify(item.stack),
644 item.droppedAt,
645 item.expiresAt,
646 item.source,
647 item.droppedByCharacterId ?? null
648 );
649 }
650
651 deleteGroundItem (groundItemId)
652 {
653 this.db.prepare("DELETE FROM ground_items WHERE ground_item_id = ?").run(groundItemId);
654 }
655
656 insertCourierTransit (transit)
657 {
658 const now = transit.updatedAt ?? Date.now();
659 const result = this.db.prepare(`
660 INSERT INTO courier_transits (
661 sender_character_id, destination_kind, destination_character_id, cargo_blob,
662 origin_x, origin_y, dest_x, dest_y, traveled, status, entity_id, updated_at, created_at
663 ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
664 `).run(
665 transit.senderCharacterId,
666 transit.destinationKind,
667 transit.destinationCharacterId ?? null,
668 JSON.stringify(transit.cargo),
669 transit.originX,
670 transit.originY,
671 transit.destX,
672 transit.destY,
673 transit.traveled ?? 0,
674 transit.status ?? "in_transit",
675 transit.entityId ?? null,
676 now,
677 transit.createdAt ?? now
678 );
679 return this.getCourierTransit(Number(result.lastInsertRowid));
680 }
681
682 getCourierTransit (courierId)
683 {
684 const row = this.db.prepare("SELECT * FROM courier_transits WHERE courier_id = ?").get(courierId);
685 return row ? parseCourierTransit(row) : null;
686 }
687
688 listActiveCourierTransits ()
689 {
690 return this.db.prepare(`
691 SELECT * FROM courier_transits WHERE status = 'in_transit' ORDER BY courier_id ASC
692 `).all().map(parseCourierTransit);
693 }
694
695 updateCourierTransit (transit)
696 {
697 this.db.prepare(`
698 UPDATE courier_transits
699 SET traveled = ?, status = ?, entity_id = ?, updated_at = ?,
700 origin_x = ?, origin_y = ?, dest_x = ?, dest_y = ?,
701 destination_kind = ?, destination_character_id = ?
702 WHERE courier_id = ?
703 `).run(
704 transit.traveled ?? 0,
705 transit.status,
706 transit.entityId ?? null,
707 transit.updatedAt ?? Date.now(),
708 transit.originX,
709 transit.originY,
710 transit.destX,
711 transit.destY,
712 transit.destinationKind,
713 transit.destinationCharacterId ?? null,
714 transit.courierId
715 );
716 }
717
718 close ()
719 {
720 this.db.close();
721 }
722}
723
724export function normalizeName (value)
725{
726 return String(value ?? "").trim().toLocaleLowerCase();
727}
728
729function pairIds (left, right)
730{
731 return left < right ? [left, right] : [right, left];
732}
733
734function parseGroundItem (row)
735{
736 return {
737 groundItemId: row.ground_item_id,
738 tileX: row.tile_x,
739 tileY: row.tile_y,
740 stack: JSON.parse(row.stack_blob),
741 droppedAt: row.dropped_at,
742 expiresAt: row.expires_at,
743 source: row.source,
744 droppedByCharacterId: row.dropped_by_character_id
745 };
746}
747
748function bagContainsItem (inventory, equipment, itemId)
749{
750 for (const stack of inventory ?? [])
751 {
752 if (stack?.itemId === itemId)
753 { return true; }
754 }
755 for (const stack of Object.values(equipment?.slots ?? {}))
756 {
757 if (stack?.itemId === itemId)
758 { return true; }
759 }
760 return false;
761}
762
763function parseBag (row, characterId)
764{
765 const vault = normalizeVault(JSON.parse(row.vault_blob));
766 return migrateBagCoins({
767 characterId,
768 inventory: normalizeInventorySlots(JSON.parse(row.inventory_blob)),
769 equipment: JSON.parse(row.equipment_blob),
770 currency: row.currency,
771 vault: vault.slots,
772 vaultReserved: vault.reserved,
773 vaultCurrency: row.vault_currency
774 });
775}
776
777function parseCourierTransit (row)
778{
779 return {
780 courierId: row.courier_id,
781 senderCharacterId: row.sender_character_id,
782 destinationKind: row.destination_kind,
783 destinationCharacterId: row.destination_character_id,
784 cargo: JSON.parse(row.cargo_blob),
785 originX: row.origin_x,
786 originY: row.origin_y,
787 destX: row.dest_x,
788 destY: row.dest_y,
789 traveled: row.traveled,
790 status: row.status,
791 entityId: row.entity_id,
792 updatedAt: row.updated_at,
793 createdAt: row.created_at
794 };
795}