BillTracker/services/backupService.js

const crypto = require('crypto');
const fs = require('fs');
const path = require('path');
const Database = require('better-sqlite3');
const { closeDb, getDb, getDbPath } = require('../db/database');

const BACKUP_DIR = path.resolve(
  process.env.BACKUP_PATH || path.join(path.dirname(getDbPath()), '..', 'backups')
);
const BACKUP_ID_RE = /^(?:bill-tracker-backup|pre-restore|imported-backup|scheduled-backup)-\d{8}-\d{6}-\d{3}Z-[a-f0-9]{8}\.sqlite$/;

function ensureBackupDir() {
  fs.mkdirSync(BACKUP_DIR, { recursive: true, mode: 0o700 });
}

function timestampPart(date = new Date()) {
  return date.toISOString()
    .replace(/[-:]/g, '')
    .replace('T', '-')
    .replace(/\.\d{3}Z$/, `-${String(date.getMilliseconds()).padStart(3, '0')}Z`);
}

function makeBackupId(prefix = 'bill-tracker-backup') {
  return `${prefix}-${timestampPart()}-${crypto.randomBytes(4).toString('hex')}.sqlite`;
}

function assertValidBackupId(id) {
  if (
    typeof id !== 'string' ||
    id.includes('/') ||
    id.includes('\\') ||
    path.isAbsolute(id) ||
    !BACKUP_ID_RE.test(id)
  ) {
    const err = new Error('Invalid backup id');
    err.status = 400;
    throw err;
  }
  return id;
}

function backupPathForId(id) {
  assertValidBackupId(id);
  ensureBackupDir();
  const resolved = path.resolve(BACKUP_DIR, id);
  const relative = path.relative(BACKUP_DIR, resolved);
  if (relative.startsWith('..') || path.isAbsolute(relative)) {
    const err = new Error('Invalid backup id');
    err.status = 400;
    throw err;
  }
  return resolved;
}

function checksumFile(filePath) {
  const hash = crypto.createHash('sha256');
  hash.update(fs.readFileSync(filePath));
  return hash.digest('hex');
}

function cleanupSqliteSidecars(filePath) {
  for (const suffix of ['-wal', '-shm']) {
    try {
      const sidecar = `${filePath}${suffix}`;
      if (fs.existsSync(sidecar)) fs.unlinkSync(sidecar);
    } catch {}
  }
}

function metadataForFile(filePath) {
  const id = path.basename(filePath);
  assertValidBackupId(id);
  const stat = fs.statSync(filePath);
  const type = id.startsWith('pre-restore-')
    ? 'pre-restore'
    : id.startsWith('imported-backup-')
      ? 'imported'
      : id.startsWith('scheduled-backup-')
        ? 'scheduled'
        : 'manual';
  return {
    id,
    filename: id,
    type,
    created_at: stat.birthtime.toISOString(),
    modified_at: stat.mtime.toISOString(),
    size_bytes: stat.size,
    checksum: checksumFile(filePath),
  };
}

function listBackups() {
  ensureBackupDir();
  return fs.readdirSync(BACKUP_DIR)
    .filter(name => BACKUP_ID_RE.test(name))
    .map(name => {
      const filePath = backupPathForId(name);
      if (!fs.statSync(filePath).isFile()) return null;
      return metadataForFile(filePath);
    })
    .filter(Boolean)
    .sort((a, b) => b.modified_at.localeCompare(a.modified_at));
}

function validateSqliteDatabase(filePath) {
  let db;
  try {
    db = new Database(filePath, { readonly: true, fileMustExist: true });
    const result = db.pragma('integrity_check', { simple: true });
    if (result !== 'ok') {
      const err = new Error('Backup failed SQLite integrity check');
      err.status = 400;
      throw err;
    }
    db.prepare('SELECT name FROM sqlite_master LIMIT 1').get();
  } catch (err) {
    if (err.status) throw err;
    const safeErr = new Error('Backup is not a valid SQLite database');
    safeErr.status = 400;
    throw safeErr;
  } finally {
    if (db) db.close();
    cleanupSqliteSidecars(filePath);
  }
}

async function createBackup(prefix = 'bill-tracker-backup') {
  ensureBackupDir();
  const db = getDb();
  db.pragma('wal_checkpoint(TRUNCATE)');

  const id = makeBackupId(prefix);
  const finalPath = backupPathForId(id);
  const tempPath = `${finalPath}.partial`;

  if (fs.existsSync(finalPath) || fs.existsSync(tempPath)) {
    const err = new Error('Backup file already exists');
    err.status = 409;
    throw err;
  }

  try {
    await db.backup(tempPath);
    validateSqliteDatabase(tempPath);
    fs.renameSync(tempPath, finalPath);
    fs.chmodSync(finalPath, 0o600);
    return metadataForFile(finalPath);
  } catch (err) {
    try { if (fs.existsSync(tempPath)) fs.unlinkSync(tempPath); } catch {}
    cleanupSqliteSidecars(tempPath);
    throw err;
  }
}

async function importBackupBuffer(buffer) {
  ensureBackupDir();

  if (!Buffer.isBuffer(buffer) || buffer.length === 0) {
    const err = new Error('Backup upload is required');
    err.status = 400;
    throw err;
  }

  const id = makeBackupId('imported-backup');
  const finalPath = backupPathForId(id);
  const tempPath = `${finalPath}.upload`;

  if (fs.existsSync(finalPath) || fs.existsSync(tempPath)) {
    const err = new Error('Backup file already exists');
    err.status = 409;
    throw err;
  }

  try {
    fs.writeFileSync(tempPath, buffer, { flag: 'wx', mode: 0o600 });
    validateSqliteDatabase(tempPath);
    fs.renameSync(tempPath, finalPath);
    fs.chmodSync(finalPath, 0o600);
    return metadataForFile(finalPath);
  } catch (err) {
    try { if (fs.existsSync(tempPath)) fs.unlinkSync(tempPath); } catch {}
    cleanupSqliteSidecars(tempPath);
    throw err;
  }
}

function getBackupFile(id) {
  const filePath = backupPathForId(id);
  if (!fs.existsSync(filePath) || !fs.statSync(filePath).isFile()) {
    const err = new Error('Backup not found');
    err.status = 404;
    throw err;
  }
  return {
    path: filePath,
    metadata: metadataForFile(filePath),
  };
}

function deleteBackup(id) {
  const backup = getBackupFile(id);
  fs.unlinkSync(backup.path);
  cleanupSqliteSidecars(backup.path);
  return { deleted: true, id: backup.metadata.id, deleted_at: new Date().toISOString() };
}

function applyScheduledRetention(retentionCount) {
  const keep = parseInt(retentionCount, 10);
  if (!Number.isInteger(keep) || keep < 1) return { deleted: [] };

  const scheduled = listBackups().filter(backup => backup.type === 'scheduled');
  const toDelete = scheduled.slice(keep);
  const deleted = [];

  for (const backup of toDelete) {
    try {
      deleted.push(deleteBackup(backup.id).id);
    } catch {
      // Retention should never make a scheduled backup fail.
    }
  }

  return { deleted };
}

async function restoreBackup(id) {
  const source = getBackupFile(id);
  validateSqliteDatabase(source.path);

  const preRestoreBackup = await createBackup('pre-restore');
  const livePath = path.resolve(getDbPath());
  const liveDir = path.dirname(livePath);
  const tempRestorePath = path.join(liveDir, `.restore-${Date.now()}-${crypto.randomBytes(4).toString('hex')}.sqlite`);

  try {
    fs.copyFileSync(source.path, tempRestorePath, fs.constants.COPYFILE_EXCL);
    validateSqliteDatabase(tempRestorePath);

    closeDb();
    fs.renameSync(tempRestorePath, livePath);

    for (const suffix of ['-wal', '-shm']) {
      try {
        const sidecar = `${livePath}${suffix}`;
        if (fs.existsSync(sidecar)) fs.unlinkSync(sidecar);
      } catch {}
    }

    getDb();

    return {
      restored_from: source.metadata.id,
      pre_restore_backup: preRestoreBackup.id,
      restored_at: new Date().toISOString(),
      restart_required: false,
    };
  } catch (err) {
    try { if (fs.existsSync(tempRestorePath)) fs.unlinkSync(tempRestorePath); } catch {}
    cleanupSqliteSidecars(tempRestorePath);
    throw err;
  }
}

module.exports = {
  BACKUP_DIR,
  assertValidBackupId,
  applyScheduledRetention,
  createBackup,
  deleteBackup,
  getBackupFile,
  importBackupBuffer,
  listBackups,
  restoreBackup,
};