BlueGit

timfa questward

Files

questward / server / src / persistence / stateDb.js

stateDb.js

stateDb.js 26.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 { 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
99CREATE TABLE IF NOT EXISTS market_orders (
100 order_id INTEGER PRIMARY KEY AUTOINCREMENT,
101 character_id INTEGER NOT NULL,
102 side TEXT NOT NULL,
103 item_id INTEGER NOT NULL,
104 quantity INTEGER NOT NULL,
105 limit_unit_price INTEGER NOT NULL,
106 escrow_coins INTEGER NOT NULL DEFAULT 0,
107 escrow_stack_blob TEXT,
108 created_at INTEGER NOT NULL,
109 status TEXT NOT NULL
110);
111
112CREATE TABLE IF NOT EXISTS market_trades (
113 trade_id INTEGER PRIMARY KEY AUTOINCREMENT,
114 buy_order_id INTEGER NOT NULL,
115 sell_order_id INTEGER NOT NULL,
116 item_id INTEGER NOT NULL,
117 quantity INTEGER NOT NULL,
118 unit_price INTEGER NOT NULL,
119 buyer_character_id INTEGER NOT NULL,
120 seller_character_id INTEGER NOT NULL,
121 created_at INTEGER NOT NULL
122);
123
124CREATE TABLE IF NOT EXISTS item_last_sales (
125 item_id INTEGER PRIMARY KEY,
126 last_trade_price INTEGER NOT NULL,
127 last_trade_at INTEGER NOT NULL,
128 price_from INTEGER NOT NULL
129);
130`;
131
132export class StateDatabase
133{
134 constructor (filePath)
135 {
136 this.db = openDatabase(filePath);
137 this.db.exec(STATE_SCHEMA);
138 this.#migrateSchema();
139 }
140
141 #migrateSchema ()
142 {
143 const bagColumns = this.db.prepare("PRAGMA table_info(character_bags)").all();
144 if (!bagColumns.some((column) => column.name === "skills_blob"))
145 {
146 this.db.exec("ALTER TABLE character_bags ADD COLUMN skills_blob TEXT NOT NULL DEFAULT '{}'");
147 }
148 const characterColumns = this.db.prepare("PRAGMA table_info(characters)").all();
149 if (!characterColumns.some((column) => column.name === "is_dead"))
150 { this.db.exec("ALTER TABLE characters ADD COLUMN is_dead INTEGER NOT NULL DEFAULT 0"); }
151 this.#grantLanternsToExistingCharacters();
152 }
153
154 #grantLanternsToExistingCharacters ()
155 {
156 const def = itemDefinition(ItemId.lantern);
157 if (!def)
158 { return; }
159 const rows = this.db.prepare("SELECT character_id, inventory_blob, equipment_blob FROM character_bags").all();
160 const update = this.db.prepare(`
161 UPDATE character_bags
162 SET inventory_blob = ?, equipment_blob = ?
163 WHERE character_id = ?
164 `);
165 for (const row of rows)
166 {
167 const inventory = JSON.parse(row.inventory_blob || "[]");
168 const equipment = JSON.parse(row.equipment_blob || "{}");
169 if (bagContainsItem(inventory, equipment, ItemId.lantern))
170 { continue; }
171 if (!equipment.slots?.offhand)
172 {
173 equipment.slots = equipment.slots ?? {};
174 equipment.slots.offhand = { itemId: ItemId.lantern, quantity: 1 };
175 }
176 else
177 {
178 const result = insertStack(inventory, { itemId: ItemId.lantern, quantity: 1 }, def);
179 if (!result.ok)
180 { continue; }
181 inventory.splice(0, inventory.length, ...result.slots);
182 }
183 update.run(JSON.stringify(inventory), JSON.stringify(equipment), row.character_id);
184 }
185 }
186
187 getAccountBySubject (authSubject)
188 {
189 return this.db.prepare("SELECT * FROM accounts WHERE auth_subject = ?").get(authSubject) ?? null;
190 }
191
192 createAccount (authSubject, isAdmin, email = null)
193 {
194 this.db.prepare("INSERT INTO accounts (auth_subject, email, is_admin) VALUES (?, ?, ?)").run(
195 authSubject,
196 email,
197 isAdmin ? 1 : 0
198 );
199 return this.getAccountBySubject(authSubject);
200 }
201
202 ensureAccount (authSubject, isAdmin, email = null)
203 {
204 const existing = this.getAccountBySubject(authSubject);
205 if (existing)
206 { return existing; }
207
208 return this.createAccount(authSubject, isAdmin, email);
209 }
210
211 listCharacters (accountId)
212 {
213 return this.db.prepare(`
214 SELECT * FROM characters
215 WHERE account_id = ?
216 ORDER BY character_id ASC
217 `).all(accountId);
218 }
219
220 getCharacter (characterId)
221 {
222 return this.db.prepare("SELECT * FROM characters WHERE character_id = ?").get(characterId) ?? null;
223 }
224
225 findSurnameClaim (surnameNormalized)
226 {
227 return this.db.prepare("SELECT * FROM surname_claims WHERE surname_normalized = ?").get(surnameNormalized) ?? null;
228 }
229
230 createCharacter (accountId, firstName, lastName, sex, tileX, tileY)
231 {
232 const firstNormalized = normalizeName(firstName);
233 const lastNormalized = normalizeName(lastName);
234 this.db.exec("BEGIN");
235 try
236 {
237 this.db.prepare(`
238 INSERT INTO characters (
239 account_id, first_name, last_name, first_normalized, last_normalized, sex, tile_x, tile_y
240 ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
241 `).run(accountId, firstName, lastName, firstNormalized, lastNormalized, sex, tileX, tileY);
242 const characterId = Number(this.db.prepare("SELECT last_insert_rowid() AS id").get().id);
243
244 const claim = this.findSurnameClaim(lastNormalized);
245 if (!claim)
246 {
247 this.db.prepare("INSERT INTO surname_claims (surname_normalized, account_id) VALUES (?, ?)").run(
248 lastNormalized,
249 accountId
250 );
251 }
252
253 this.db.exec("COMMIT");
254 this.#ensureBag(characterId);
255 return this.getCharacter(characterId);
256 }
257 catch (error)
258 {
259 this.db.exec("ROLLBACK");
260 throw error;
261 }
262 }
263
264 scheduleDeletion (characterId, at)
265 {
266 this.db.prepare("UPDATE characters SET deletion_scheduled_at = ? WHERE character_id = ?").run(at, characterId);
267 }
268
269 cancelDeletion (characterId)
270 {
271 this.db.prepare("UPDATE characters SET deletion_scheduled_at = NULL WHERE character_id = ?").run(characterId);
272 }
273
274 deleteCharacter (characterId)
275 {
276 const character = this.getCharacter(characterId);
277 if (!character)
278 { return; }
279
280 this.db.exec("BEGIN");
281 try
282 {
283 this.#moveGoodsToInheritance(character);
284 this.db.prepare("DELETE FROM character_bags WHERE character_id = ?").run(characterId);
285 this.db.prepare("DELETE FROM characters WHERE character_id = ?").run(characterId);
286 this.db.prepare("DELETE FROM friend_requests WHERE from_character_id = ? OR to_character_id = ?").run(
287 characterId,
288 characterId
289 );
290 this.db.prepare("DELETE FROM friendships WHERE character_id_a = ? OR character_id_b = ?").run(
291 characterId,
292 characterId
293 );
294 const remaining = this.db.prepare(`
295 SELECT COUNT(*) AS count FROM characters
296 WHERE account_id = ? AND last_normalized = ?
297 `).get(character.account_id, character.last_normalized);
298 if (!remaining.count)
299 {
300 this.db.prepare("DELETE FROM surname_claims WHERE surname_normalized = ? AND account_id = ?").run(
301 character.last_normalized,
302 character.account_id
303 );
304 }
305 this.db.exec("COMMIT");
306 }
307 catch (error)
308 {
309 this.db.exec("ROLLBACK");
310 throw error;
311 }
312 }
313
314 saveCharacterPosition (characterId, tileX, tileY, gait)
315 {
316 this.db.prepare("UPDATE characters SET tile_x = ?, tile_y = ?, gait = ? WHERE character_id = ?").run(
317 tileX,
318 tileY,
319 gait,
320 characterId
321 );
322 }
323
324 setCharacterDead (characterId, isDead)
325 {
326 this.db.prepare("UPDATE characters SET is_dead = ? WHERE character_id = ?").run(
327 isDead ? 1 : 0,
328 characterId
329 );
330 }
331
332 listDueDeletions (now)
333 {
334 return this.db.prepare(`
335 SELECT * FROM characters
336 WHERE deletion_scheduled_at IS NOT NULL AND deletion_scheduled_at <= ?
337 `).all(now);
338 }
339
340 getCharacterByName (firstName, lastName)
341 {
342 return this.db.prepare(`
343 SELECT * FROM characters
344 WHERE first_normalized = ? AND last_normalized = ?
345 `).get(normalizeName(firstName), normalizeName(lastName)) ?? null;
346 }
347
348 listFriendRecords (characterId)
349 {
350 return this.db.prepare(`
351 SELECT
352 CASE WHEN character_id_a = ? THEN character_id_b ELSE character_id_a END AS character_id,
353 created_at
354 FROM friendships
355 WHERE character_id_a = ? OR character_id_b = ?
356 ORDER BY created_at ASC
357 `).all(characterId, characterId, characterId);
358 }
359
360 areFriends (characterId, otherId)
361 {
362 const [a, b] = pairIds(characterId, otherId);
363 return !!this.db.prepare(`
364 SELECT 1 FROM friendships WHERE character_id_a = ? AND character_id_b = ?
365 `).get(a, b);
366 }
367
368 insertFriendship (characterId, otherId)
369 {
370 const [a, b] = pairIds(characterId, otherId);
371 this.db.prepare(`
372 INSERT INTO friendships (character_id_a, character_id_b, created_at)
373 VALUES (?, ?, ?)
374 `).run(a, b, Date.now());
375 }
376
377 deleteFriendship (characterId, otherId)
378 {
379 const [a, b] = pairIds(characterId, otherId);
380 this.db.prepare(`
381 DELETE FROM friendships WHERE character_id_a = ? AND character_id_b = ?
382 `).run(a, b);
383 }
384
385 listIncomingRequests (characterId)
386 {
387 return this.db.prepare(`
388 SELECT request_id, from_character_id, to_character_id, created_at
389 FROM friend_requests
390 WHERE to_character_id = ?
391 ORDER BY created_at ASC
392 `).all(characterId);
393 }
394
395 listOutgoingRequests (characterId)
396 {
397 return this.db.prepare(`
398 SELECT request_id, from_character_id, to_character_id, created_at
399 FROM friend_requests
400 WHERE from_character_id = ?
401 ORDER BY created_at ASC
402 `).all(characterId);
403 }
404
405 getFriendRequest (fromId, toId)
406 {
407 return this.db.prepare(`
408 SELECT * FROM friend_requests
409 WHERE from_character_id = ? AND to_character_id = ?
410 `).get(fromId, toId) ?? null;
411 }
412
413 insertFriendRequest (fromId, toId)
414 {
415 this.db.prepare(`
416 INSERT INTO friend_requests (from_character_id, to_character_id, created_at)
417 VALUES (?, ?, ?)
418 `).run(fromId, toId, Date.now());
419 }
420
421 deleteFriendRequest (fromId, toId)
422 {
423 this.db.prepare(`
424 DELETE FROM friend_requests
425 WHERE from_character_id = ? AND to_character_id = ?
426 `).run(fromId, toId);
427 }
428
429 listPersistentInRegion (minX, minY, maxX, maxY)
430 {
431 return this.db.prepare(`
432 SELECT entity_id, tile_x, tile_y, components_blob
433 FROM persistent_entities
434 WHERE tile_x >= ? AND tile_x <= ? AND tile_y >= ? AND tile_y <= ?
435 `).all(minX, maxX, minY, maxY);
436 }
437
438 replacePersistentInRegion (minX, minY, maxX, maxY, entities)
439 {
440 this.db.exec("BEGIN");
441 try
442 {
443 this.db.prepare(`
444 DELETE FROM persistent_entities
445 WHERE tile_x >= ? AND tile_x <= ? AND tile_y >= ? AND tile_y <= ?
446 `).run(minX, maxX, minY, maxY);
447 const insert = this.db.prepare(`
448 INSERT INTO persistent_entities (entity_id, tile_x, tile_y, components_blob)
449 VALUES (?, ?, ?, ?)
450 `);
451 for (const entity of entities)
452 {
453 insert.run(
454 entity.entityId,
455 entity.tileX,
456 entity.tileY,
457 JSON.stringify(entity.payload?.components ?? entity.components)
458 );
459 }
460 this.db.exec("COMMIT");
461 }
462 catch (error)
463 {
464 this.db.exec("ROLLBACK");
465 throw error;
466 }
467 }
468
469 #ensureBag (characterId)
470 {
471 const existing = this.db.prepare("SELECT * FROM character_bags WHERE character_id = ?").get(characterId);
472 if (existing)
473 { return parseBag(existing, characterId); }
474 const slots = emptySlots();
475 for (const starter of STARTER_ITEMS)
476 {
477 const result = insertStack(slots, { ...starter }, itemDefinition(starter.itemId));
478 if (result.ok)
479 { slots.splice(0, slots.length, ...result.slots); }
480 }
481 const equipment = emptyEquipment();
482 equipment.slots.offhand = { itemId: ItemId.lantern, quantity: 1 };
483 this.db.prepare(`
484 INSERT INTO character_bags (
485 character_id, inventory_blob, equipment_blob, currency, vault_blob, vault_currency, skills_blob
486 ) VALUES (?, ?, ?, 0, ?, 0, ?)
487 `).run(
488 characterId,
489 JSON.stringify(slots),
490 JSON.stringify(equipment),
491 JSON.stringify({ slots: [], reserved: [] }),
492 JSON.stringify(storedSkillXp(defaultPlayerSkills()))
493 );
494 return this.getBag(characterId);
495 }
496
497 getBag (characterId)
498 {
499 const row = this.db.prepare("SELECT * FROM character_bags WHERE character_id = ?").get(characterId);
500 if (row)
501 { return parseBag(row, characterId); }
502 if (this.getCharacter(characterId))
503 { return this.#ensureBag(characterId); }
504 return null;
505 }
506
507 saveBag (characterId, bag)
508 {
509 this.#ensureBag(characterId);
510 const migrated = migrateBagCoins(bag);
511 const vault = normalizeVault(
512 migrated.vaultReserved != null
513 ? { slots: migrated.vault, reserved: migrated.vaultReserved }
514 : migrated.vault
515 );
516 this.db.prepare(`
517 UPDATE character_bags
518 SET inventory_blob = ?, equipment_blob = ?, currency = 0, vault_blob = ?, vault_currency = 0
519 WHERE character_id = ?
520 `).run(
521 JSON.stringify(normalizeInventorySlots(migrated.inventory ?? emptySlots())),
522 JSON.stringify(migrated.equipment ?? emptyEquipment()),
523 JSON.stringify({ slots: vault.slots, reserved: vault.reserved }),
524 characterId
525 );
526 }
527
528 getSkills (characterId)
529 {
530 const row = this.db.prepare("SELECT skills_blob FROM character_bags WHERE character_id = ?").get(characterId);
531 if (row)
532 {
533 const parsed = JSON.parse(row.skills_blob || "{}");
534 const skills = normalizePlayerSkills(parsed);
535 // Persist floors (Health ≥ 10, others ≥ 1) for legacy under-leveled rows.
536 if (this.#skillsBlobNeedsRewrite(parsed, skills))
537 { this.saveSkills(characterId, skills); }
538 return skills;
539 }
540 if (this.getCharacter(characterId))
541 {
542 this.#ensureBag(characterId);
543 return this.getSkills(characterId);
544 }
545 return null;
546 }
547
548 /** True when stored blob is below the default minimum levels and must be rewritten. */
549 #skillsBlobNeedsRewrite (parsed, normalized)
550 {
551 const raw = parsed?.xp && typeof parsed.xp === "object" ? parsed.xp : parsed;
552 if (!raw || typeof raw !== "object")
553 { return true; }
554 for (const skillId of Object.keys(normalized.xp))
555 {
556 const stored = Math.max(0, Math.floor(Number(raw[skillId]) || 0));
557 if (stored < normalized.xp[skillId])
558 { return true; }
559 }
560 return false;
561 }
562
563 saveSkills (characterId, skills)
564 {
565 this.#ensureBag(characterId);
566 const xp = storedSkillXp(skills?.xp ? skills : { xp: skills });
567 this.db.prepare("UPDATE character_bags SET skills_blob = ? WHERE character_id = ?").run(
568 JSON.stringify({ xp }),
569 characterId
570 );
571 }
572
573 getInheritance (accountId)
574 {
575 const row = this.db.prepare("SELECT * FROM account_inheritance WHERE account_id = ?").get(accountId);
576 if (!row)
577 { return { accountId, slots: emptySlots() }; }
578 return migrateInheritanceCoins({
579 accountId,
580 slots: JSON.parse(row.slots_blob),
581 currency: row.currency
582 });
583 }
584
585 saveInheritance (accountId, inheritance)
586 {
587 const migrated = migrateInheritanceCoins(inheritance);
588 this.db.prepare(`
589 INSERT INTO account_inheritance (account_id, slots_blob, currency)
590 VALUES (?, ?, 0)
591 ON CONFLICT(account_id) DO UPDATE SET slots_blob = excluded.slots_blob, currency = 0
592 `).run(accountId, JSON.stringify(migrated.slots ?? emptySlots()));
593 }
594
595 #moveGoodsToInheritance (character)
596 {
597 const bag = this.getBag(character.character_id) ?? {
598 inventory: emptySlots(),
599 equipment: emptyEquipment(),
600 vault: [],
601 vaultReserved: []
602 };
603 const inheritance = this.getInheritance(character.account_id);
604 const vault = normalizeVault(
605 bag.vaultReserved != null
606 ? { slots: bag.vault, reserved: bag.vaultReserved }
607 : bag.vault
608 );
609 // Void outstanding cheques issued by this character: reservations return to available vault.
610 for (const reserved of vault.reserved ?? [])
611 {
612 const stack = {
613 itemId: reserved.itemId,
614 quantity: reserved.quantity,
615 origin: reserved.origin ?? null,
616 instance: reserved.instance ?? null
617 };
618 const def = itemDefinition(stack.itemId);
619 if (!def)
620 { continue; }
621 const restored = insertStackGrowable(vault.slots, stack, def);
622 vault.slots = restored.slots;
623 }
624 vault.reserved = [];
625 const incoming = [
626 ...(bag.inventory ?? []),
627 ...Object.values(bag.equipment?.slots ?? {}),
628 ...(vault.slots ?? [])
629 ].filter(Boolean);
630 let slots = inheritance.slots ?? emptySlots();
631 for (const stack of incoming)
632 {
633 const def = itemDefinition(stack.itemId);
634 if (!def)
635 { continue; }
636 const result = insertStack(slots, stack, def);
637 if (result.ok)
638 { slots = result.slots; }
639 else
640 {
641 const extra = result.slots;
642 for (const leftover of extra)
643 {
644 if (leftover)
645 { slots.push(leftover); }
646 }
647 slots.push({ ...stack, quantity: result.remainder });
648 }
649 }
650 this.saveInheritance(character.account_id, {
651 slots: publicSlots(slots)
652 });
653 }
654
655 nextGroundItemId ()
656 {
657 const row = this.db.prepare("SELECT MAX(ground_item_id) AS id FROM ground_items").get();
658 return (row?.id ?? 0) + 1;
659 }
660
661 listGroundItemsInRegion (minX, minY, maxX, maxY)
662 {
663 return this.db.prepare(`
664 SELECT * FROM ground_items
665 WHERE tile_x >= ? AND tile_x <= ? AND tile_y >= ? AND tile_y <= ?
666 `).all(minX, maxX, minY, maxY).map(parseGroundItem);
667 }
668
669 upsertGroundItem (item)
670 {
671 this.db.prepare(`
672 INSERT INTO ground_items (
673 ground_item_id, tile_x, tile_y, stack_blob, dropped_at, expires_at, source, dropped_by_character_id
674 ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
675 ON CONFLICT(ground_item_id) DO UPDATE SET
676 tile_x = excluded.tile_x,
677 tile_y = excluded.tile_y,
678 stack_blob = excluded.stack_blob,
679 dropped_at = excluded.dropped_at,
680 expires_at = excluded.expires_at,
681 source = excluded.source,
682 dropped_by_character_id = excluded.dropped_by_character_id
683 `).run(
684 item.groundItemId,
685 item.tileX,
686 item.tileY,
687 JSON.stringify(item.stack),
688 item.droppedAt,
689 item.expiresAt,
690 item.source,
691 item.droppedByCharacterId ?? null
692 );
693 }
694
695 deleteGroundItem (groundItemId)
696 {
697 this.db.prepare("DELETE FROM ground_items WHERE ground_item_id = ?").run(groundItemId);
698 }
699
700 insertCourierTransit (transit)
701 {
702 const now = transit.updatedAt ?? Date.now();
703 const result = this.db.prepare(`
704 INSERT INTO courier_transits (
705 sender_character_id, destination_kind, destination_character_id, cargo_blob,
706 origin_x, origin_y, dest_x, dest_y, traveled, status, entity_id, updated_at, created_at
707 ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
708 `).run(
709 transit.senderCharacterId,
710 transit.destinationKind,
711 transit.destinationCharacterId ?? null,
712 JSON.stringify(transit.cargo),
713 transit.originX,
714 transit.originY,
715 transit.destX,
716 transit.destY,
717 transit.traveled ?? 0,
718 transit.status ?? "in_transit",
719 transit.entityId ?? null,
720 now,
721 transit.createdAt ?? now
722 );
723 return this.getCourierTransit(Number(result.lastInsertRowid));
724 }
725
726 getCourierTransit (courierId)
727 {
728 const row = this.db.prepare("SELECT * FROM courier_transits WHERE courier_id = ?").get(courierId);
729 return row ? parseCourierTransit(row) : null;
730 }
731
732 listActiveCourierTransits ()
733 {
734 return this.db.prepare(`
735 SELECT * FROM courier_transits WHERE status = 'in_transit' ORDER BY courier_id ASC
736 `).all().map(parseCourierTransit);
737 }
738
739 updateCourierTransit (transit)
740 {
741 this.db.prepare(`
742 UPDATE courier_transits
743 SET traveled = ?, status = ?, entity_id = ?, updated_at = ?,
744 origin_x = ?, origin_y = ?, dest_x = ?, dest_y = ?,
745 destination_kind = ?, destination_character_id = ?
746 WHERE courier_id = ?
747 `).run(
748 transit.traveled ?? 0,
749 transit.status,
750 transit.entityId ?? null,
751 transit.updatedAt ?? Date.now(),
752 transit.originX,
753 transit.originY,
754 transit.destX,
755 transit.destY,
756 transit.destinationKind,
757 transit.destinationCharacterId ?? null,
758 transit.courierId
759 );
760 }
761
762 insertMarketOrder (order)
763 {
764 const result = this.db.prepare(`
765 INSERT INTO market_orders (
766 character_id, side, item_id, quantity, limit_unit_price,
767 escrow_coins, escrow_stack_blob, created_at, status
768 ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
769 `).run(
770 order.characterId,
771 order.side,
772 order.itemId | 0,
773 order.quantity | 0,
774 order.limitUnitPrice | 0,
775 order.escrowCoins | 0 || 0,
776 order.escrowStack ? JSON.stringify(order.escrowStack) : null,
777 order.createdAt ?? Date.now(),
778 order.status ?? "open"
779 );
780 return this.getMarketOrder(Number(result.lastInsertRowid));
781 }
782
783 getMarketOrder (orderId)
784 {
785 const row = this.db.prepare("SELECT * FROM market_orders WHERE order_id = ?").get(orderId);
786 return row ? parseMarketOrder(row) : null;
787 }
788
789 updateMarketOrder (order)
790 {
791 this.db.prepare(`
792 UPDATE market_orders
793 SET quantity = ?, escrow_coins = ?, escrow_stack_blob = ?, status = ?
794 WHERE order_id = ?
795 `).run(
796 order.quantity | 0,
797 order.escrowCoins | 0 || 0,
798 order.escrowStack ? JSON.stringify(order.escrowStack) : null,
799 order.status,
800 order.orderId
801 );
802 return this.getMarketOrder(order.orderId);
803 }
804
805 listOpenMarketOrdersForCharacter (characterId)
806 {
807 return this.db.prepare(`
808 SELECT * FROM market_orders
809 WHERE character_id = ? AND status = 'open'
810 ORDER BY created_at ASC, order_id ASC
811 `).all(characterId).map(parseMarketOrder);
812 }
813
814 listOpenBuyOrdersForItem (itemId)
815 {
816 return this.db.prepare(`
817 SELECT * FROM market_orders
818 WHERE status = 'open' AND side = 'buy' AND item_id = ?
819 ORDER BY limit_unit_price DESC, created_at ASC, order_id ASC
820 `).all(itemId | 0).map(parseMarketOrder);
821 }
822
823 listOpenSellOrdersForItem (itemId)
824 {
825 return this.db.prepare(`
826 SELECT * FROM market_orders
827 WHERE status = 'open' AND side = 'sell' AND item_id = ?
828 ORDER BY limit_unit_price ASC, created_at ASC, order_id ASC
829 `).all(itemId | 0).map(parseMarketOrder);
830 }
831
832 listExpiredMarketOrders (createdBefore)
833 {
834 return this.db.prepare(`
835 SELECT * FROM market_orders
836 WHERE status = 'open' AND created_at < ?
837 ORDER BY created_at ASC, order_id ASC
838 `).all(createdBefore).map(parseMarketOrder);
839 }
840
841 insertMarketTrade (trade)
842 {
843 const result = this.db.prepare(`
844 INSERT INTO market_trades (
845 buy_order_id, sell_order_id, item_id, quantity, unit_price,
846 buyer_character_id, seller_character_id, created_at
847 ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
848 `).run(
849 trade.buyOrderId,
850 trade.sellOrderId,
851 trade.itemId | 0,
852 trade.quantity | 0,
853 trade.unitPrice | 0,
854 trade.buyerCharacterId,
855 trade.sellerCharacterId,
856 trade.createdAt ?? Date.now()
857 );
858 return Number(result.lastInsertRowid);
859 }
860
861 upsertItemLastSale (sale)
862 {
863 this.db.prepare(`
864 INSERT INTO item_last_sales (item_id, last_trade_price, last_trade_at, price_from)
865 VALUES (?, ?, ?, ?)
866 ON CONFLICT(item_id) DO UPDATE SET
867 last_trade_price = excluded.last_trade_price,
868 last_trade_at = excluded.last_trade_at,
869 price_from = excluded.price_from
870 `).run(
871 sale.itemId | 0,
872 sale.lastTradePrice | 0,
873 sale.lastTradeAt,
874 Math.round(Number(sale.priceFrom) || 0)
875 );
876 }
877
878 listItemLastSales ()
879 {
880 return this.db.prepare("SELECT * FROM item_last_sales").all().map((row) => ({
881 itemId: row.item_id,
882 lastTradePrice: row.last_trade_price,
883 lastTradeAt: row.last_trade_at,
884 priceFrom: row.price_from
885 }));
886 }
887
888 close ()
889 {
890 this.db.close();
891 }
892}
893
894export function normalizeName (value)
895{
896 return String(value ?? "").trim().toLocaleLowerCase();
897}
898
899function pairIds (left, right)
900{
901 return left < right ? [left, right] : [right, left];
902}
903
904function parseGroundItem (row)
905{
906 return {
907 groundItemId: row.ground_item_id,
908 tileX: row.tile_x,
909 tileY: row.tile_y,
910 stack: JSON.parse(row.stack_blob),
911 droppedAt: row.dropped_at,
912 expiresAt: row.expires_at,
913 source: row.source,
914 droppedByCharacterId: row.dropped_by_character_id
915 };
916}
917
918function parseMarketOrder (row)
919{
920 return {
921 orderId: row.order_id,
922 characterId: row.character_id,
923 side: row.side,
924 itemId: row.item_id,
925 quantity: row.quantity,
926 limitUnitPrice: row.limit_unit_price,
927 escrowCoins: row.escrow_coins | 0,
928 escrowStack: row.escrow_stack_blob ? JSON.parse(row.escrow_stack_blob) : null,
929 createdAt: row.created_at,
930 status: row.status
931 };
932}
933
934function bagContainsItem (inventory, equipment, itemId)
935{
936 for (const stack of inventory ?? [])
937 {
938 if (stack?.itemId === itemId)
939 { return true; }
940 }
941 for (const stack of Object.values(equipment?.slots ?? {}))
942 {
943 if (stack?.itemId === itemId)
944 { return true; }
945 }
946 return false;
947}
948
949function parseBag (row, characterId)
950{
951 const vault = normalizeVault(JSON.parse(row.vault_blob));
952 return migrateBagCoins({
953 characterId,
954 inventory: normalizeInventorySlots(JSON.parse(row.inventory_blob)),
955 equipment: JSON.parse(row.equipment_blob),
956 currency: row.currency,
957 vault: vault.slots,
958 vaultReserved: vault.reserved,
959 vaultCurrency: row.vault_currency
960 });
961}
962
963function parseCourierTransit (row)
964{
965 return {
966 courierId: row.courier_id,
967 senderCharacterId: row.sender_character_id,
968 destinationKind: row.destination_kind,
969 destinationCharacterId: row.destination_character_id,
970 cargo: JSON.parse(row.cargo_blob),
971 originX: row.origin_x,
972 originY: row.origin_y,
973 destX: row.dest_x,
974 destY: row.dest_y,
975 traveled: row.traveled,
976 status: row.status,
977 entityId: row.entity_id,
978 updatedAt: row.updated_at,
979 createdAt: row.created_at
980 };
981}