accounting/server/db.js

const fs = require('fs');
const path = require('path');
const initSqlJs = require('sql.js');
const { createUserNameResolver } = require('./utils/accounting');

const dataDir = path.join(__dirname, 'data');
if (!fs.existsSync(dataDir)) {
  fs.mkdirSync(dataDir, { recursive: true });
}

const dbPath = path.join(dataDir, 'accounting.db');

let db = null;

const DEFAULT_USERS = ['用户1', '用户2'];
const DEFAULT_CATEGORIES = [
  ['工资', 'income', 'Money', 1],
  ['亚马逊转账', 'income', 'CreditCard', 2],
  ['利息', 'income', 'Coin', 3],
  ['其他', 'income', 'Plus', 4],
  ['餐饮', 'expense', 'Bowl', 10],
  ['交通', 'expense', 'Van', 11],
  ['购物', 'expense', 'ShoppingCart', 12],
  ['房租', 'expense', 'House', 13],
  ['水电燃气', 'expense', 'Lightning', 14],
  ['话费', 'expense', 'Phone', 15],
  ['亚马逊物流费', 'expense', 'Box', 16],
  ['亚马逊广告费', 'expense', 'Position', 17],
  ['亚马逊货款', 'expense', 'Goods', 18],
  ['其他', 'expense', 'More', 99],
];

function saveDb() {
  if (!db) {
    return;
  }

  const data = db.export();
  const buffer = Buffer.from(data);
  fs.writeFileSync(dbPath, buffer);
}

function rawAll(sqlDb, sql, params = []) {
  const stmt = sqlDb.prepare(sql);
  if (params.length > 0) {
    stmt.bind(params);
  }

  const rows = [];
  while (stmt.step()) {
    rows.push(stmt.getAsObject());
  }
  stmt.free();
  return rows;
}

function rawGet(sqlDb, sql, params = []) {
  return rawAll(sqlDb, sql, params)[0] || null;
}

function normalizeStoredUsers(sqlDb) {
  const users = rawAll(sqlDb, 'SELECT id, name FROM users ORDER BY sort_order, id');
  const resolveUserName = createUserNameResolver(users);
  const aliases = rawAll(sqlDb, 'SELECT DISTINCT user FROM records WHERE user IS NOT NULL AND user != ""');

  for (const row of aliases) {
    const currentName = row.user;
    const normalizedName = resolveUserName(currentName, '');

    if (!normalizedName || normalizedName === currentName) {
      continue;
    }

    const exists = users.some((user) => user.name === normalizedName);
    if (!exists) {
      continue;
    }

    sqlDb.run('UPDATE records SET user = ? WHERE user = ?', [normalizedName, currentName]);
  }
}

function roundStoredMonetaryValues(sqlDb) {
  sqlDb.run('UPDATE records SET amount = ROUND(amount, 2)');
  sqlDb.run('UPDATE balance_settings SET initial_balance = ROUND(initial_balance, 2)');
  sqlDb.run('UPDATE user_balance_settings SET initial_balance = ROUND(initial_balance, 2)');
}

async function initDb() {
  const SQL = await initSqlJs();

  if (fs.existsSync(dbPath)) {
    db = new SQL.Database(fs.readFileSync(dbPath));
  } else {
    db = new SQL.Database();
  }

  db.run('PRAGMA foreign_keys = ON');

  db.run(`
    CREATE TABLE IF NOT EXISTS categories (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL,
      type TEXT NOT NULL DEFAULT 'expense',
      icon TEXT,
      sort_order INTEGER DEFAULT 0,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      UNIQUE(name, type)
    )
  `);

  db.run(`
    CREATE TABLE IF NOT EXISTS records (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      date TEXT NOT NULL,
      type TEXT NOT NULL,
      category_id INTEGER NOT NULL,
      amount REAL NOT NULL DEFAULT 0,
      note TEXT,
      user TEXT NOT NULL DEFAULT '用户1',
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      FOREIGN KEY (category_id) REFERENCES categories(id)
    )
  `);

  try {
    db.run("ALTER TABLE records ADD COLUMN user TEXT NOT NULL DEFAULT '用户1'");
  } catch (error) {
    // Ignore when the column already exists.
  }

  db.run('CREATE INDEX IF NOT EXISTS idx_records_date ON records(date)');
  db.run('CREATE INDEX IF NOT EXISTS idx_records_type ON records(type)');
  db.run('CREATE INDEX IF NOT EXISTS idx_records_user ON records(user)');

  db.run(`
    CREATE TABLE IF NOT EXISTS balance_settings (
      id INTEGER PRIMARY KEY,
      initial_balance REAL NOT NULL DEFAULT 0,
      updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )
  `);

  db.run(`
    CREATE TABLE IF NOT EXISTS users (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL UNIQUE,
      sort_order INTEGER DEFAULT 0,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )
  `);

  db.run(`
    CREATE TABLE IF NOT EXISTS user_balance_settings (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      user_name TEXT NOT NULL UNIQUE,
      initial_balance REAL NOT NULL DEFAULT 0,
      updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )
  `);

  const userCount = rawGet(db, 'SELECT COUNT(*) AS count FROM users')?.count || 0;
  if (userCount === 0) {
    DEFAULT_USERS.forEach((name, index) => {
      db.run('INSERT INTO users (name, sort_order) VALUES (?, ?)', [name, index + 1]);
      db.run('INSERT INTO user_balance_settings (user_name, initial_balance) VALUES (?, 0)', [name]);
    });
  }

  const categoryStmt = db.prepare(
    'INSERT OR IGNORE INTO categories (name, type, icon, sort_order) VALUES (?, ?, ?, ?)'
  );
  for (const category of DEFAULT_CATEGORIES) {
    categoryStmt.run(category);
  }
  categoryStmt.free();

  const balanceCount = rawGet(db, 'SELECT COUNT(*) AS count FROM balance_settings')?.count || 0;
  if (balanceCount === 0) {
    db.run('INSERT INTO balance_settings (id, initial_balance) VALUES (1, 0)');
  }

  normalizeStoredUsers(db);
  roundStoredMonetaryValues(db);

  saveDb();
  return db;
}

class DbWrapper {
  constructor(sqlDb) {
    this.db = sqlDb;
  }

  all(sql, ...params) {
    return rawAll(this.db, sql, params.flat());
  }

  get(sql, ...params) {
    return rawGet(this.db, sql, params.flat());
  }

  run(sql, ...params) {
    const flatParams = params.flat();
    this.db.run(sql, flatParams);
    const changes = this.db.getRowsModified();
    const lastInsertRowid = this.all('SELECT last_insert_rowid() AS id')[0]?.id;
    saveDb();
    return { changes, lastInsertRowid };
  }
}

module.exports = {
  getDb: () => new DbWrapper(db),
  initDb,
  saveDb,
};