BlueGit

timfa questward

Files

questward / server / src / persistence / stateDb.js

stateDb.js

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