import { DEFAULT_WORLD_SETTINGS } from "../../../shared/protocol/constants.js"; import { normalizeStarterLoadout } from "../../../shared/items/starterLoadout.js"; import { openDatabase, withTransaction } from "./database.js"; const WORLD_SCHEMA = ` CREATE TABLE IF NOT EXISTS world_revisions ( revision_id INTEGER PRIMARY KEY AUTOINCREMENT, created_at INTEGER NOT NULL, created_by TEXT NOT NULL, status TEXT NOT NULL ); CREATE TABLE IF NOT EXISTS world_chunk_versions ( chunk_x INTEGER NOT NULL, chunk_y INTEGER NOT NULL, revision_id INTEGER NOT NULL, open INTEGER NOT NULL, data_blob TEXT NOT NULL, PRIMARY KEY (chunk_x, chunk_y, revision_id) ); CREATE TABLE IF NOT EXISTS world_meta ( key TEXT PRIMARY KEY, value INTEGER ); CREATE TABLE IF NOT EXISTS world_settings ( revision_id INTEGER PRIMARY KEY, settings_blob TEXT NOT NULL ); CREATE TABLE IF NOT EXISTS world_chunk_edit_log ( log_id INTEGER PRIMARY KEY AUTOINCREMENT, chunk_x INTEGER NOT NULL, chunk_y INTEGER NOT NULL, revision_id INTEGER NOT NULL, editor_admin_id TEXT NOT NULL, edited_at INTEGER NOT NULL, diff_entries TEXT NOT NULL ); CREATE TABLE IF NOT EXISTS world_live_settings ( id INTEGER PRIMARY KEY CHECK (id = 1), settings_blob TEXT NOT NULL, updated_at INTEGER NOT NULL, updated_by TEXT NOT NULL ); CREATE TABLE IF NOT EXISTS world_settings_edit_log ( log_id INTEGER PRIMARY KEY AUTOINCREMENT, editor_admin_id TEXT NOT NULL, edited_at INTEGER NOT NULL, diff_entries TEXT NOT NULL ); `; export class WorldDatabase { constructor (filePath) { this.db = openDatabase(filePath); this.db.exec(WORLD_SCHEMA); this.#ensureInitialRevision(); this.#ensureLiveSettings(); } #ensureInitialRevision () { const meta = this.db.prepare("SELECT key, value FROM world_meta").all(); if (meta.length > 0) { return; } withTransaction(this.db, () => { this.db.prepare( "INSERT INTO world_revisions (created_at, created_by, status) VALUES (?, ?, ?)" ).run(Date.now(), "system", "active"); const revisionId = Number(this.db.prepare("SELECT last_insert_rowid() AS id").get().id); this.db.prepare("INSERT INTO world_meta (key, value) VALUES ('active_revision', ?)").run(revisionId); this.db.exec("INSERT INTO world_meta (key, value) VALUES ('pending_revision', NULL)"); this.db.prepare("INSERT INTO world_settings (revision_id, settings_blob) VALUES (?, ?)").run( revisionId, JSON.stringify(DEFAULT_WORLD_SETTINGS) ); }); } #ensureLiveSettings () { const existing = this.db.prepare("SELECT id FROM world_live_settings WHERE id = 1").get(); if (existing) { return; } const settings = this.getSettings(this.getActiveRevisionId()); this.db.prepare( "INSERT INTO world_live_settings (id, settings_blob, updated_at, updated_by) VALUES (1, ?, ?, ?)" ).run(JSON.stringify(settings), Date.now(), "system"); } getLiveSettings () { this.#ensureLiveSettings(); const row = this.db.prepare("SELECT settings_blob FROM world_live_settings WHERE id = 1").get(); const merged = row ? { ...DEFAULT_WORLD_SETTINGS, ...JSON.parse(row.settings_blob) } : { ...DEFAULT_WORLD_SETTINGS }; const loadout = normalizeStarterLoadout(merged); merged.starterEquipment = loadout.equipment; merged.starterInventory = loadout.inventory; return merged; } saveLiveSettings (settings, updatedBy) { this.db.prepare(` INSERT INTO world_live_settings (id, settings_blob, updated_at, updated_by) VALUES (1, ?, ?, ?) ON CONFLICT(id) DO UPDATE SET settings_blob = excluded.settings_blob, updated_at = excluded.updated_at, updated_by = excluded.updated_by `).run(JSON.stringify(settings), Date.now(), updatedBy); } appendSettingsChangelog (editorAdminId, diffEntries) { this.db.prepare(` INSERT INTO world_settings_edit_log (editor_admin_id, edited_at, diff_entries) VALUES (?, ?, ?) `).run(editorAdminId, Date.now(), JSON.stringify(diffEntries)); } listSettingsChangelog () { return this.db.prepare(` SELECT log_id, editor_admin_id, edited_at, diff_entries FROM world_settings_edit_log ORDER BY edited_at ASC, log_id ASC `).all().map((row) => ({ ...row, diffEntries: JSON.parse(row.diff_entries) })); } getActiveRevisionId () { return this.db.prepare("SELECT value FROM world_meta WHERE key = 'active_revision'").get()?.value ?? null; } getPendingRevisionId () { return this.db.prepare("SELECT value FROM world_meta WHERE key = 'pending_revision'").get()?.value ?? null; } getEditorRevisionId () { return this.getPendingRevisionId() ?? this.getActiveRevisionId(); } getSettings (revisionId) { const row = this.db.prepare("SELECT settings_blob FROM world_settings WHERE revision_id = ?").get(revisionId); if (row) { return { ...DEFAULT_WORLD_SETTINGS, ...JSON.parse(row.settings_blob) }; } const fallback = this.db.prepare( "SELECT settings_blob FROM world_settings WHERE revision_id <= ? ORDER BY revision_id DESC LIMIT 1" ).get(revisionId); return fallback ? { ...DEFAULT_WORLD_SETTINGS, ...JSON.parse(fallback.settings_blob) } : { ...DEFAULT_WORLD_SETTINGS }; } getChunkVersion (chunkX, chunkY, revisionId) { return this.db.prepare(` SELECT chunk_x, chunk_y, revision_id, open, data_blob FROM world_chunk_versions WHERE chunk_x = ? AND chunk_y = ? AND revision_id <= ? ORDER BY revision_id DESC LIMIT 1 `).get(chunkX, chunkY, revisionId) ?? null; } listChunkCoords (revisionId) { return this.db.prepare(` SELECT chunk_x, chunk_y, MAX(revision_id) AS revision_id FROM world_chunk_versions WHERE revision_id <= ? GROUP BY chunk_x, chunk_y `).all(revisionId); } ensurePendingRevision (createdBy) { const existing = this.getPendingRevisionId(); if (existing) { return existing; } return withTransaction(this.db, () => { const activeId = this.getActiveRevisionId(); this.db.prepare( "INSERT INTO world_revisions (created_at, created_by, status) VALUES (?, ?, ?)" ).run(Date.now(), createdBy, "pending"); const revisionId = Number(this.db.prepare("SELECT last_insert_rowid() AS id").get().id); this.db.prepare("UPDATE world_meta SET value = ? WHERE key = 'pending_revision'").run(revisionId); const settings = this.getSettings(activeId); this.db.prepare("INSERT INTO world_settings (revision_id, settings_blob) VALUES (?, ?)").run( revisionId, JSON.stringify(settings) ); return revisionId; }); } saveChunkVersion (chunkX, chunkY, revisionId, open, dataBlob) { this.db.prepare(` INSERT INTO world_chunk_versions (chunk_x, chunk_y, revision_id, open, data_blob) VALUES (?, ?, ?, ?, ?) ON CONFLICT(chunk_x, chunk_y, revision_id) DO UPDATE SET open = excluded.open, data_blob = excluded.data_blob `).run(chunkX, chunkY, revisionId, open ? 1 : 0, dataBlob); } saveSettings (revisionId, settings) { this.db.prepare(` INSERT INTO world_settings (revision_id, settings_blob) VALUES (?, ?) ON CONFLICT(revision_id) DO UPDATE SET settings_blob = excluded.settings_blob `).run(revisionId, JSON.stringify(settings)); } appendChangelog (chunkX, chunkY, revisionId, editorAdminId, diffEntries) { this.db.prepare(` INSERT INTO world_chunk_edit_log (chunk_x, chunk_y, revision_id, editor_admin_id, edited_at, diff_entries) VALUES (?, ?, ?, ?, ?, ?) `).run(chunkX, chunkY, revisionId, editorAdminId, Date.now(), JSON.stringify(diffEntries)); } listChangelog (chunkX, chunkY) { return this.db.prepare(` SELECT log_id, chunk_x, chunk_y, revision_id, editor_admin_id, edited_at, diff_entries FROM world_chunk_edit_log WHERE chunk_x = ? AND chunk_y = ? ORDER BY edited_at ASC, log_id ASC `).all(chunkX, chunkY).map((row) => ({ ...row, diffEntries: JSON.parse(row.diff_entries) })); } promotePendingRevision () { const pendingId = this.getPendingRevisionId(); if (!pendingId) { return this.getActiveRevisionId(); } return withTransaction(this.db, () => { const activeId = this.getActiveRevisionId(); this.db.prepare("UPDATE world_revisions SET status = 'superseded' WHERE revision_id = ?").run(activeId); this.db.prepare("UPDATE world_revisions SET status = 'active' WHERE revision_id = ?").run(pendingId); this.db.prepare("UPDATE world_meta SET value = ? WHERE key = 'active_revision'").run(pendingId); this.db.exec("UPDATE world_meta SET value = NULL WHERE key = 'pending_revision'"); return pendingId; }); } close () { this.db.close(); } }