BlueGit

timfa questward

Files

questward / server / src / persistence / stateDb.js

stateDb.js

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