BlueGit

timfa questward

Files

questward / server / src / persistence / stateDb.js

stateDb.js

spawner tweaks
timfa Tim Falken · pushed by timfa · 2 months ago
661b81e5 History
stateDb.js 21.9 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 // Persist floors (Health ≥ 10, others ≥ 1) for legacy under-leveled rows.
504 if (this.#skillsBlobNeedsRewrite(parsed, skills))
505 { this.saveSkills(characterId, skills); }
506 return skills;
507 }
508 if (this.getCharacter(characterId))
509 {
510 this.#ensureBag(characterId);
511 return this.getSkills(characterId);
512 }
513 return null;
514 }
515
516 /** True when stored blob is below the default minimum levels and must be rewritten. */
517 #skillsBlobNeedsRewrite (parsed, normalized)
518 {
519 const raw = parsed?.xp && typeof parsed.xp === "object" ? parsed.xp : parsed;
520 if (!raw || typeof raw !== "object")
521 { return true; }
522 for (const skillId of Object.keys(normalized.xp))
523 {
524 const stored = Math.max(0, Math.floor(Number(raw[skillId]) || 0));
525 if (stored < normalized.xp[skillId])
526 { return true; }
527 }
528 return false;
529 }
530
531 saveSkills (characterId, skills)
532 {
533 this.#ensureBag(characterId);
534 const xp = storedSkillXp(skills?.xp ? skills : { xp: skills });
535 this.db.prepare("UPDATE character_bags SET skills_blob = ? WHERE character_id = ?").run(
536 JSON.stringify({ xp }),
537 characterId
538 );
539 }
540
541 getInheritance (accountId)
542 {
543 const row = this.db.prepare("SELECT * FROM account_inheritance WHERE account_id = ?").get(accountId);
544 if (!row)
545 { return { accountId, slots: emptySlots() }; }
546 return migrateInheritanceCoins({
547 accountId,
548 slots: JSON.parse(row.slots_blob),
549 currency: row.currency
550 });
551 }
552
553 saveInheritance (accountId, inheritance)
554 {
555 const migrated = migrateInheritanceCoins(inheritance);
556 this.db.prepare(`
557 INSERT INTO account_inheritance (account_id, slots_blob, currency)
558 VALUES (?, ?, 0)
559 ON CONFLICT(account_id) DO UPDATE SET slots_blob = excluded.slots_blob, currency = 0
560 `).run(accountId, JSON.stringify(migrated.slots ?? emptySlots()));
561 }
562
563 #moveGoodsToInheritance (character)
564 {
565 const bag = this.getBag(character.character_id) ?? {
566 inventory: emptySlots(),
567 equipment: emptyEquipment(),
568 vault: [],
569 vaultReserved: []
570 };
571 const inheritance = this.getInheritance(character.account_id);
572 const vault = normalizeVault(
573 bag.vaultReserved != null
574 ? { slots: bag.vault, reserved: bag.vaultReserved }
575 : bag.vault
576 );
577 // Void outstanding cheques issued by this character: reservations return to available vault.
578 for (const reserved of vault.reserved ?? [])
579 {
580 const stack = {
581 itemId: reserved.itemId,
582 quantity: reserved.quantity,
583 origin: reserved.origin ?? null,
584 instance: reserved.instance ?? null
585 };
586 const def = itemDefinition(stack.itemId);
587 if (!def)
588 { continue; }
589 const restored = insertStackGrowable(vault.slots, stack, def);
590 vault.slots = restored.slots;
591 }
592 vault.reserved = [];
593 const incoming = [
594 ...(bag.inventory ?? []),
595 ...Object.values(bag.equipment?.slots ?? {}),
596 ...(vault.slots ?? [])
597 ].filter(Boolean);
598 let slots = inheritance.slots ?? emptySlots();
599 for (const stack of incoming)
600 {
601 const def = itemDefinition(stack.itemId);
602 if (!def)
603 { continue; }
604 const result = insertStack(slots, stack, def);
605 if (result.ok)
606 { slots = result.slots; }
607 else
608 {
609 const extra = result.slots;
610 for (const leftover of extra)
611 {
612 if (leftover)
613 { slots.push(leftover); }
614 }
615 slots.push({ ...stack, quantity: result.remainder });
616 }
617 }
618 this.saveInheritance(character.account_id, {
619 slots: publicSlots(slots)
620 });
621 }
622
623 nextGroundItemId ()
624 {
625 const row = this.db.prepare("SELECT MAX(ground_item_id) AS id FROM ground_items").get();
626 return (row?.id ?? 0) + 1;
627 }
628
629 listGroundItemsInRegion (minX, minY, maxX, maxY)
630 {
631 return this.db.prepare(`
632 SELECT * FROM ground_items
633 WHERE tile_x >= ? AND tile_x <= ? AND tile_y >= ? AND tile_y <= ?
634 `).all(minX, maxX, minY, maxY).map(parseGroundItem);
635 }
636
637 upsertGroundItem (item)
638 {
639 this.db.prepare(`
640 INSERT INTO ground_items (
641 ground_item_id, tile_x, tile_y, stack_blob, dropped_at, expires_at, source, dropped_by_character_id
642 ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
643 ON CONFLICT(ground_item_id) DO UPDATE SET
644 tile_x = excluded.tile_x,
645 tile_y = excluded.tile_y,
646 stack_blob = excluded.stack_blob,
647 dropped_at = excluded.dropped_at,
648 expires_at = excluded.expires_at,
649 source = excluded.source,
650 dropped_by_character_id = excluded.dropped_by_character_id
651 `).run(
652 item.groundItemId,
653 item.tileX,
654 item.tileY,
655 JSON.stringify(item.stack),
656 item.droppedAt,
657 item.expiresAt,
658 item.source,
659 item.droppedByCharacterId ?? null
660 );
661 }
662
663 deleteGroundItem (groundItemId)
664 {
665 this.db.prepare("DELETE FROM ground_items WHERE ground_item_id = ?").run(groundItemId);
666 }
667
668 insertCourierTransit (transit)
669 {
670 const now = transit.updatedAt ?? Date.now();
671 const result = this.db.prepare(`
672 INSERT INTO courier_transits (
673 sender_character_id, destination_kind, destination_character_id, cargo_blob,
674 origin_x, origin_y, dest_x, dest_y, traveled, status, entity_id, updated_at, created_at
675 ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
676 `).run(
677 transit.senderCharacterId,
678 transit.destinationKind,
679 transit.destinationCharacterId ?? null,
680 JSON.stringify(transit.cargo),
681 transit.originX,
682 transit.originY,
683 transit.destX,
684 transit.destY,
685 transit.traveled ?? 0,
686 transit.status ?? "in_transit",
687 transit.entityId ?? null,
688 now,
689 transit.createdAt ?? now
690 );
691 return this.getCourierTransit(Number(result.lastInsertRowid));
692 }
693
694 getCourierTransit (courierId)
695 {
696 const row = this.db.prepare("SELECT * FROM courier_transits WHERE courier_id = ?").get(courierId);
697 return row ? parseCourierTransit(row) : null;
698 }
699
700 listActiveCourierTransits ()
701 {
702 return this.db.prepare(`
703 SELECT * FROM courier_transits WHERE status = 'in_transit' ORDER BY courier_id ASC
704 `).all().map(parseCourierTransit);
705 }
706
707 updateCourierTransit (transit)
708 {
709 this.db.prepare(`
710 UPDATE courier_transits
711 SET traveled = ?, status = ?, entity_id = ?, updated_at = ?,
712 origin_x = ?, origin_y = ?, dest_x = ?, dest_y = ?,
713 destination_kind = ?, destination_character_id = ?
714 WHERE courier_id = ?
715 `).run(
716 transit.traveled ?? 0,
717 transit.status,
718 transit.entityId ?? null,
719 transit.updatedAt ?? Date.now(),
720 transit.originX,
721 transit.originY,
722 transit.destX,
723 transit.destY,
724 transit.destinationKind,
725 transit.destinationCharacterId ?? null,
726 transit.courierId
727 );
728 }
729
730 close ()
731 {
732 this.db.close();
733 }
734}
735
736export function normalizeName (value)
737{
738 return String(value ?? "").trim().toLocaleLowerCase();
739}
740
741function pairIds (left, right)
742{
743 return left < right ? [left, right] : [right, left];
744}
745
746function parseGroundItem (row)
747{
748 return {
749 groundItemId: row.ground_item_id,
750 tileX: row.tile_x,
751 tileY: row.tile_y,
752 stack: JSON.parse(row.stack_blob),
753 droppedAt: row.dropped_at,
754 expiresAt: row.expires_at,
755 source: row.source,
756 droppedByCharacterId: row.dropped_by_character_id
757 };
758}
759
760function bagContainsItem (inventory, equipment, itemId)
761{
762 for (const stack of inventory ?? [])
763 {
764 if (stack?.itemId === itemId)
765 { return true; }
766 }
767 for (const stack of Object.values(equipment?.slots ?? {}))
768 {
769 if (stack?.itemId === itemId)
770 { return true; }
771 }
772 return false;
773}
774
775function parseBag (row, characterId)
776{
777 const vault = normalizeVault(JSON.parse(row.vault_blob));
778 return migrateBagCoins({
779 characterId,
780 inventory: normalizeInventorySlots(JSON.parse(row.inventory_blob)),
781 equipment: JSON.parse(row.equipment_blob),
782 currency: row.currency,
783 vault: vault.slots,
784 vaultReserved: vault.reserved,
785 vaultCurrency: row.vault_currency
786 });
787}
788
789function parseCourierTransit (row)
790{
791 return {
792 courierId: row.courier_id,
793 senderCharacterId: row.sender_character_id,
794 destinationKind: row.destination_kind,
795 destinationCharacterId: row.destination_character_id,
796 cargo: JSON.parse(row.cargo_blob),
797 originX: row.origin_x,
798 originY: row.origin_y,
799 destX: row.dest_x,
800 destY: row.dest_y,
801 traveled: row.traveled,
802 status: row.status,
803 entityId: row.entity_id,
804 updatedAt: row.updated_at,
805 createdAt: row.created_at
806 };
807}