Inventory-Manager-CC/web/server/db.js

import Database from 'better-sqlite3';
import { existsSync, mkdirSync, accessSync, constants as fsConstants } from 'fs';
import { dirname } from 'path';

const DB_PATH = process.env.DB_PATH || '/data/inventory.db';

console.log(`[db] Opening database at ${DB_PATH} (uid=${process.getuid()})`);

// Ensure the directory exists
const dbDir = dirname(DB_PATH);
if (!existsSync(dbDir)) {
  mkdirSync(dbDir, { recursive: true });
}

// Verify directory is writable before opening SQLite
try {
  accessSync(dbDir, fsConstants.W_OK);
} catch {
  console.error(`[db] FATAL: directory ${dbDir} is not writable by uid ${process.getuid()}`);
  process.exit(1);
}

let db;
try {
  db = new Database(DB_PATH);
} catch (err) {
  console.error(`[db] FATAL: failed to open database: ${err.message}`);
  process.exit(1);
}

// Performance pragmas
db.pragma('journal_mode = WAL');
db.pragma('synchronous = NORMAL');
db.pragma('foreign_keys = ON');
db.pragma('cache_size = -8000');  // 8MB cache
db.pragma('temp_store = MEMORY');

console.log('[db] Database ready');

// ========== Schema ==========

db.exec(`
  CREATE TABLE IF NOT EXISTS items (
    name        TEXT PRIMARY KEY,
    count       INTEGER NOT NULL DEFAULT 0,
    display_name TEXT NOT NULL DEFAULT '',
    updated_at  INTEGER NOT NULL DEFAULT 0
  );

  CREATE TABLE IF NOT EXISTS furnaces (
    name      TEXT PRIMARY KEY,
    type      TEXT NOT NULL DEFAULT 'minecraft:furnace',
    active    INTEGER NOT NULL DEFAULT 0,
    input     TEXT,
    fuel      TEXT,
    output    TEXT,
    updated_at INTEGER NOT NULL DEFAULT 0
  );

  CREATE TABLE IF NOT EXISTS alerts (
    item      TEXT PRIMARY KEY,
    triggered INTEGER NOT NULL DEFAULT 0,
    current   INTEGER NOT NULL DEFAULT 0,
    threshold INTEGER NOT NULL DEFAULT 0,
    updated_at INTEGER NOT NULL DEFAULT 0
  );

  CREATE TABLE IF NOT EXISTS state (
    key       TEXT PRIMARY KEY,
    value     TEXT NOT NULL DEFAULT '{}',
    updated_at INTEGER NOT NULL DEFAULT 0
  );

  CREATE TABLE IF NOT EXISTS item_history (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    name       TEXT NOT NULL,
    count      INTEGER NOT NULL,
    recorded_at INTEGER NOT NULL
  );

  CREATE INDEX IF NOT EXISTS idx_item_history_name ON item_history(name);
  CREATE INDEX IF NOT EXISTS idx_item_history_time ON item_history(recorded_at);
`);

// ========== Prepared Statements ==========

const upsertItem = db.prepare(`
  INSERT INTO items (name, count, display_name, updated_at)
  VALUES (@name, @count, @displayName, @updatedAt)
  ON CONFLICT(name) DO UPDATE SET
    count = @count,
    display_name = @displayName,
    updated_at = @updatedAt
`);

const upsertFurnace = db.prepare(`
  INSERT INTO furnaces (name, type, active, input, fuel, output, updated_at)
  VALUES (@name, @type, @active, @input, @fuel, @output, @updatedAt)
  ON CONFLICT(name) DO UPDATE SET
    type = @type,
    active = @active,
    input = @input,
    fuel = @fuel,
    output = @output,
    updated_at = @updatedAt
`);

const upsertAlert = db.prepare(`
  INSERT INTO alerts (item, triggered, current, threshold, updated_at)
  VALUES (@item, @triggered, @current, @threshold, @updatedAt)
  ON CONFLICT(item) DO UPDATE SET
    triggered = @triggered,
    current = @current,
    threshold = @threshold,
    updated_at = @updatedAt
`);

const upsertState = db.prepare(`
  INSERT INTO state (key, value, updated_at)
  VALUES (@key, @value, @updatedAt)
  ON CONFLICT(key) DO UPDATE SET
    value = @value,
    updated_at = @updatedAt
`);

const insertHistory = db.prepare(`
  INSERT INTO item_history (name, count, recorded_at)
  VALUES (@name, @count, @recordedAt)
`);

const getState = db.prepare(`SELECT value FROM state WHERE key = ?`);
const getAllItems = db.prepare(`SELECT name, count, display_name as displayName FROM items ORDER BY count DESC`);
const getAllFurnaces = db.prepare(`SELECT * FROM furnaces`);
const getAllAlerts = db.prepare(`SELECT item, triggered, current, threshold FROM alerts WHERE triggered = 1`);
const clearAlertTriggered = db.prepare(`UPDATE alerts SET triggered = 0`);

const getItemHistory = db.prepare(`
  SELECT count, recorded_at as recordedAt
  FROM item_history
  WHERE name = ?
  ORDER BY recorded_at DESC
  LIMIT ?
`);

const getHistorySummaryStmt = db.prepare(`
  SELECT recorded_at as recordedAt, SUM(count) as total
  FROM item_history
  GROUP BY recorded_at
  ORDER BY recorded_at ASC
`);

// Cleanup old history (keep last 7 days)
const cleanupHistory = db.prepare(`
  DELETE FROM item_history WHERE recorded_at < ?
`);

// ========== Batch Operations ==========

const saveItemsBatch = db.transaction((items, now) => {
  for (const item of items) {
    upsertItem.run({
      name: item.name || '',
      count: item.count || 0,
      displayName: item.displayName || item.name || '',
      updatedAt: now,
    });
  }
});

const saveHistoryBatch = db.transaction((items, now) => {
  for (const item of items) {
    insertHistory.run({
      name: item.name || '',
      count: item.count || 0,
      recordedAt: now,
    });
  }
});

const saveFurnacesBatch = db.transaction((furnaces, now) => {
  for (const [name, f] of Object.entries(furnaces)) {
    upsertFurnace.run({
      name,
      type: f.type || 'minecraft:furnace',
      active: f.active ? 1 : 0,
      input: f.input ? JSON.stringify(f.input) : null,
      fuel: f.fuel ? JSON.stringify(f.fuel) : null,
      output: f.output ? JSON.stringify(f.output) : null,
      updatedAt: now,
    });
  }
});

const saveAlertsBatch = db.transaction((alerts, now) => {
  // Clear old triggered status
  clearAlertTriggered.run();
  for (const alert of alerts) {
    upsertAlert.run({
      item: alert.item || '',
      triggered: alert.triggered ? 1 : 0,
      current: alert.current || 0,
      threshold: alert.threshold || 0,
      updatedAt: now,
    });
  }
});

// ========== Public API ==========

/**
 * Save inventory items to the database
 */
export function saveItems(items) {
  const now = Date.now();
  saveItemsBatch(items, now);
}

/**
 * Record a snapshot of item counts for history tracking
 */
let lastHistoryRecord = 0;
const HISTORY_INTERVAL = 5 * 60 * 1000; // Record history every 5 minutes

export function recordItemHistory(items) {
  const now = Date.now();
  if (now - lastHistoryRecord < HISTORY_INTERVAL) return;
  lastHistoryRecord = now;
  saveHistoryBatch(items, now);

  // Cleanup entries older than 7 days
  cleanupHistory.run(now - 7 * 24 * 60 * 60 * 1000);
}

/**
 * Save furnace status to the database
 */
export function saveFurnaces(furnaceStatus) {
  const now = Date.now();
  saveFurnacesBatch(furnaceStatus, now);
}

/**
 * Save alerts to the database
 */
export function saveAlerts(alerts) {
  const now = Date.now();
  saveAlertsBatch(alerts, now);
}

/**
 * Save a key-value state entry (JSON serialized)
 */
export function saveState(key, value) {
  upsertState.run({
    key,
    value: JSON.stringify(value),
    updatedAt: Date.now(),
  });
}

/**
 * Load a key-value state entry
 */
export function loadState(key, defaultValue = null) {
  const row = getState.get(key);
  if (!row) return defaultValue;
  try {
    return JSON.parse(row.value);
  } catch {
    return defaultValue;
  }
}

/**
 * Load all persisted items
 */
export function loadItems() {
  return getAllItems.all();
}

/**
 * Load all persisted furnace statuses
 */
export function loadFurnaces() {
  const rows = getAllFurnaces.all();
  const result = {};
  for (const row of rows) {
    let input = null, fuel = null, output = null;
    try { input = row.input ? JSON.parse(row.input) : null; } catch { input = row.input; }
    try { fuel = row.fuel ? JSON.parse(row.fuel) : null; } catch { fuel = row.fuel; }
    try { output = row.output ? JSON.parse(row.output) : null; } catch { output = row.output; }
    result[row.name] = {
      active: !!row.active,
      type: row.type,
      input,
      fuel,
      output,
    };
  }
  return result;
}

/**
 * Load all triggered alerts
 */
export function loadAlerts() {
  return getAllAlerts.all().map(row => ({
    ...row,
    triggered: !!row.triggered,
  }));
}

/**
 * Get item count history for a specific item
 */
export function getHistory(itemName, limit = 100) {
  return getItemHistory.all(itemName, limit);
}

/**
 * Get aggregate total item count over time
 */
export function getHistorySummary() {
  return getHistorySummaryStmt.all();
}

/**
 * Load the full last-known inventory state from DB
 */
export function loadFullState() {
  const items = loadItems();
  const furnaces = loadFurnaces();
  const alerts = loadAlerts();
  const smeltingPaused = loadState('smeltingPaused', false);
  const disabledRecipes = loadState('disabledRecipes', {});
  const smeltableRecipes = loadState('smeltableRecipes', {});
  const craftableRecipes = loadState('craftableRecipes', []);
  const craftTurtleOk = loadState('craftTurtleOk', false);
  const activity = loadState('activity', {});
  const lastUpdate = loadState('lastUpdate', 0);

  // Reconstruct inventoryState
  const inventoryMeta = loadState('inventoryMeta', {});
  const inventoryState = {
    itemList: items,
    grandTotal: inventoryMeta.grandTotal || 0,
    chestCount: inventoryMeta.chestCount || 0,
    totalSlots: inventoryMeta.totalSlots || 0,
    usedSlots: inventoryMeta.usedSlots || 0,
    freeSlots: inventoryMeta.freeSlots || 0,
    usedRatio: inventoryMeta.usedRatio || 0,
    dropperOk: inventoryMeta.dropperOk || false,
    barrelOk: inventoryMeta.barrelOk || false,
    furnaceCount: inventoryMeta.furnaceCount || 0,
    furnaceStatus: furnaces,
    droppers: Array.isArray(inventoryMeta.droppers) ? inventoryMeta.droppers : [],
  };

  return {
    inventoryState,
    activityState: activity,
    alertsState: alerts,
    smeltingPaused,
    disabledRecipes,
    smeltableRecipes,
    craftableRecipes,
    craftTurtleOk,
    lastUpdate,
  };
}

/**
 * Persist the full state to DB in one transaction.
 * Uses a single flat transaction (no nested sub-transactions).
 */
const _saveFullStateTransaction = db.transaction((state, now) => {
  // Items — inline loop instead of calling saveItemsBatch to avoid nested transaction
  if (state.inventoryState?.itemList?.length) {
    for (const item of state.inventoryState.itemList) {
      upsertItem.run({
        name: item.name || '',
        count: item.count || 0,
        displayName: item.displayName || item.name || '',
        updatedAt: now,
      });
    }
  }

  // Inventory metadata (totals, slot info, etc.)
  if (state.inventoryState) {
    const { itemList, furnaceStatus, ...meta } = state.inventoryState;
    upsertState.run({ key: 'inventoryMeta', value: JSON.stringify(meta), updatedAt: now });
  }

  // Furnaces — inline loop
  if (state.inventoryState?.furnaceStatus && typeof state.inventoryState.furnaceStatus === 'object') {
    for (const [name, f] of Object.entries(state.inventoryState.furnaceStatus)) {
      upsertFurnace.run({
        name,
        type: f.type || 'minecraft:furnace',
        active: f.active ? 1 : 0,
        input: f.input ? JSON.stringify(f.input) : null,
        fuel: f.fuel ? JSON.stringify(f.fuel) : null,
        output: f.output ? JSON.stringify(f.output) : null,
        updatedAt: now,
      });
    }
  }

  // Alerts — inline loop
  if (Array.isArray(state.alertsState)) {
    clearAlertTriggered.run();
    for (const alert of state.alertsState) {
      upsertAlert.run({
        item: alert.item || '',
        triggered: alert.triggered ? 1 : 0,
        current: alert.current || 0,
        threshold: alert.threshold || 0,
        updatedAt: now,
      });
    }
  }

  // Key-value state
  if (state.activityState !== undefined) {
    upsertState.run({ key: 'activity', value: JSON.stringify(state.activityState), updatedAt: now });
  }
  if (state.smeltingPaused !== undefined) {
    upsertState.run({ key: 'smeltingPaused', value: JSON.stringify(state.smeltingPaused), updatedAt: now });
  }
  if (state.disabledRecipes !== undefined) {
    upsertState.run({ key: 'disabledRecipes', value: JSON.stringify(state.disabledRecipes), updatedAt: now });
  }
  if (state.smeltableRecipes !== undefined) {
    upsertState.run({ key: 'smeltableRecipes', value: JSON.stringify(state.smeltableRecipes), updatedAt: now });
  }
  if (state.craftableRecipes !== undefined) {
    upsertState.run({ key: 'craftableRecipes', value: JSON.stringify(state.craftableRecipes), updatedAt: now });
  }
  if (state.craftTurtleOk !== undefined) {
    upsertState.run({ key: 'craftTurtleOk', value: JSON.stringify(state.craftTurtleOk), updatedAt: now });
  }
  upsertState.run({ key: 'lastUpdate', value: JSON.stringify(now), updatedAt: now });
});

/**
 * Debounced save — buffers rapid updates and writes to DB at most every SAVE_INTERVAL ms.
 * This prevents DB writes from blocking WebSocket broadcasts on every bridge update.
 */
const SAVE_INTERVAL = 2000; // Write at most every 2 seconds
let pendingSaveState = null;
let saveTimer = null;

export function saveFullState(state) {
  pendingSaveState = state;
  if (!saveTimer) {
    saveTimer = setTimeout(() => {
      saveTimer = null;
      if (pendingSaveState) {
        const toSave = pendingSaveState;
        pendingSaveState = null;
        try {
          _saveFullStateTransaction(toSave, Date.now());
        } catch (err) {
          console.error('❌ DB save error:', err.message);
        }
      }
    }, SAVE_INTERVAL);
  }
}

/**
 * Flush any pending save immediately (used during shutdown)
 */
export function flushPendingSave() {
  if (saveTimer) {
    clearTimeout(saveTimer);
    saveTimer = null;
  }
  if (pendingSaveState) {
    const toSave = pendingSaveState;
    pendingSaveState = null;
    try {
      _saveFullStateTransaction(toSave, Date.now());
    } catch (err) {
      console.error('❌ DB flush error:', err.message);
    }
  }
}

/**
 * Close the database connection gracefully
 */
export function closeDb() {
  flushPendingSave();
  if (db.open) {
    db.close();
  }
}

export default db;