BlueGit

timfa questward

Files

questward / server / src / persistence / stateDb.js

stateDb.js

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