BlueGit

timfa questward

Files

questward / server / src / persistence / stateDb.js

stateDb.js

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