BlueGit

timfa questward

Files

questward / server / src / persistence / stateDb.js

stateDb.js

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