BillTracker/tests/seedDemoData.test.js

'use strict';

// Demo seed + surgical removal. seedDemoData() must populate every user-facing
// surface, and clearSeededDemoData() must remove EXACTLY the seeded rows — never a
// user's own bills/categories/planning, and leave no orphans. This is the contract
// the "Seed / Clear Demo Data" buttons depend on.
const test = require('node:test');
const assert = require('node:assert/strict');
const os = require('node:os');
const path = require('node:path');
const fs = require('node:fs');

const dbPath = path.join(os.tmpdir(), `bill-tracker-seed-${process.pid}.sqlite`);
process.env.DB_PATH = dbPath;

const { getDb, closeDb } = require('../db/database.cts');
const { seedDemoData } = require('../scripts/seedDemoData.cts');
const { clearSeededDemoData, eraseUserData } = require('../services/userDataService.cts');

const mkUser = (db, name) =>
  db
    .prepare(
      "INSERT INTO users (username, password_hash, role, active) VALUES (?, 'hash', 'user', 1)",
    )
    .run(name).lastInsertRowid;
const num = (db, sql, ...p) => db.prepare(sql).get(...p).n;

let db, userA, userB, userC;
test.before(() => {
  db = getDb();
  db.pragma('foreign_keys = ON');
  userA = mkUser(db, 'seed-a');
  userB = mkUser(db, 'seed-b');
  userC = mkUser(db, 'seed-c');
  seedDemoData(userA);
  seedDemoData(userB);
  seedDemoData(userC);
});
test.after(() => {
  closeDb();
  for (const s of ['', '-wal', '-shm']) {
    try {
      fs.unlinkSync(dbPath + s);
    } catch {}
  }
});

test('seed populates every user-facing surface', () => {
  assert.equal(num(db, 'SELECT COUNT(*) n FROM bills WHERE user_id=? AND is_seeded=1', userA), 23);
  // 8 of 9 bill categories reuse the user's defaults (not flagged); Healthcare + 5 spending are created.
  assert.equal(
    num(db, 'SELECT COUNT(*) n FROM categories WHERE user_id=? AND is_seeded=1', userA),
    6,
  );
  assert.equal(
    num(
      db,
      'SELECT COUNT(*) n FROM categories WHERE user_id=? AND is_seeded=1 AND spending_enabled=1',
      userA,
    ),
    5,
  );
  assert.ok(
    num(
      db,
      'SELECT COUNT(*) n FROM payments p JOIN bills b ON b.id=p.bill_id WHERE b.user_id=?',
      userA,
    ) > 40,
    'payment history',
  );
  assert.ok(
    num(db, 'SELECT COUNT(*) n FROM transactions WHERE user_id=? AND is_seeded=1', userA) > 10,
    'bank transactions',
  );
  assert.ok(
    num(
      db,
      "SELECT COUNT(*) n FROM transactions WHERE user_id=? AND match_status='matched'",
      userA,
    ) >= 3,
    'matched txns',
  );
  assert.ok(
    num(
      db,
      "SELECT COUNT(*) n FROM transactions WHERE user_id=? AND match_status='unmatched'",
      userA,
    ) >= 5,
    'unmatched txns → suggestions/recommendations',
  );
  assert.ok(
    num(
      db,
      'SELECT COUNT(*) n FROM transactions WHERE user_id=? AND spending_category_id IS NOT NULL',
      userA,
    ) > 5,
    'categorised spending',
  );
  assert.ok(
    num(
      db,
      'SELECT COUNT(*) n FROM monthly_bill_state m JOIN bills b ON b.id=m.bill_id WHERE b.user_id=?',
      userA,
    ) >= 4,
    'skip/override/note/snooze',
  );
  assert.equal(
    num(db, 'SELECT COUNT(*) n FROM category_groups WHERE user_id=? AND is_seeded=1', userA),
    2,
  );
  assert.equal(
    num(db, 'SELECT COUNT(*) n FROM snowball_plans WHERE user_id=? AND is_seeded=1', userA),
    1,
  );
  assert.equal(
    num(db, 'SELECT COUNT(*) n FROM spending_budgets WHERE user_id=? AND is_seeded=1', userA),
    5,
  );
  assert.ok(
    num(
      db,
      'SELECT COUNT(*) n FROM spending_category_rules WHERE user_id=? AND is_seeded=1',
      userA,
    ) >= 3,
  );
  assert.ok(
    num(db, 'SELECT COUNT(*) n FROM monthly_income WHERE user_id=? AND is_seeded=1', userA) >= 1,
  );
  assert.ok(
    num(
      db,
      'SELECT COUNT(*) n FROM monthly_starting_amounts WHERE user_id=? AND is_seeded=1',
      userA,
    ) >= 1,
  );
  assert.ok(
    num(
      db,
      'SELECT COUNT(*) n FROM bill_merchant_rules r JOIN bills b ON b.id=r.bill_id WHERE b.user_id=?',
      userA,
    ) >= 1,
  );
  assert.equal(
    num(db, 'SELECT COUNT(*) n FROM data_sources WHERE user_id=? AND is_seeded=1', userA),
    1,
  );
  assert.equal(
    num(db, 'SELECT COUNT(*) n FROM financial_accounts WHERE user_id=? AND is_seeded=1', userA),
    1,
  );
  assert.equal(
    num(db, 'SELECT COUNT(*) n FROM calendar_tokens WHERE user_id=? AND is_seeded=1', userA),
    1,
  );
  assert.ok(
    db.prepare('SELECT snowball_extra_payment e FROM users WHERE id=?').get(userA).e > 0,
    'snowball extra-payment set',
  );

  // Matched payments link a real seeded transaction; money is integer cents.
  assert.ok(
    num(
      db,
      "SELECT COUNT(*) n FROM payments WHERE payment_source='transaction_match' AND transaction_id IN (SELECT id FROM transactions)",
    ) >= 3,
  );
  assert.equal(
    num(db, 'SELECT COUNT(*) n FROM payments WHERE amount <> CAST(amount AS INT)'),
    0,
    'cents-clean',
  );
});

test('seed refuses to double-seed (idempotency guard)', () => {
  assert.throws(
    () => seedDemoData(userA),
    (e) => e.code === 'ALREADY_SEEDED' && e.status === 409,
  );
});

test('clear removes ONLY seeded data — user bills/categories & no orphans', () => {
  // A user's OWN bill in a seed-created category (Healthcare) + one in a default category.
  const healthcareId = db
    .prepare("SELECT id FROM categories WHERE user_id=? AND name='Healthcare'")
    .get(userA).id;
  assert.equal(
    db.prepare('SELECT is_seeded s FROM categories WHERE id=?').get(healthcareId).s,
    1,
    'Healthcare is a seeded category',
  );
  const utilitiesId = db
    .prepare("SELECT id FROM categories WHERE user_id=? AND name='Utilities'")
    .get(userA).id;
  const ownBill1 = db
    .prepare(
      "INSERT INTO bills (user_id, name, category_id, due_day, expected_amount, active, is_seeded) VALUES (?, 'My Doctor', ?, 9, 5000, 1, 0)",
    )
    .run(userA, healthcareId).lastInsertRowid;
  const ownBill2 = db
    .prepare(
      "INSERT INTO bills (user_id, name, category_id, due_day, expected_amount, active, is_seeded) VALUES (?, 'My Power', ?, 9, 6000, 1, 0)",
    )
    .run(userA, utilitiesId).lastInsertRowid;

  const result = clearSeededDemoData(db, userA);
  assert.ok(result.removed > 0);

  // Every seeded surface gone.
  assert.equal(num(db, 'SELECT COUNT(*) n FROM bills WHERE user_id=? AND is_seeded=1', userA), 0);
  assert.equal(
    num(db, 'SELECT COUNT(*) n FROM transactions WHERE user_id=? AND is_seeded=1', userA),
    0,
  );
  assert.equal(
    num(db, 'SELECT COUNT(*) n FROM data_sources WHERE user_id=? AND is_seeded=1', userA),
    0,
  );
  assert.equal(
    num(db, 'SELECT COUNT(*) n FROM financial_accounts WHERE user_id=? AND is_seeded=1', userA),
    0,
  );
  assert.equal(
    num(db, 'SELECT COUNT(*) n FROM snowball_plans WHERE user_id=? AND is_seeded=1', userA),
    0,
  );
  assert.equal(
    num(db, 'SELECT COUNT(*) n FROM spending_budgets WHERE user_id=? AND is_seeded=1', userA),
    0,
  );
  assert.equal(
    num(db, 'SELECT COUNT(*) n FROM monthly_income WHERE user_id=? AND is_seeded=1', userA),
    0,
  );
  assert.equal(
    num(db, 'SELECT COUNT(*) n FROM category_groups WHERE user_id=? AND is_seeded=1', userA),
    0,
  );

  // No orphans: payments/transactions/state for deleted bills are gone.
  assert.equal(
    num(
      db,
      'SELECT COUNT(*) n FROM payments p LEFT JOIN bills b ON b.id=p.bill_id WHERE b.id IS NULL',
    ),
    0,
    'no orphan payments',
  );
  assert.equal(
    num(
      db,
      'SELECT COUNT(*) n FROM monthly_bill_state m LEFT JOIN bills b ON b.id=m.bill_id WHERE b.id IS NULL',
    ),
    0,
    'no orphan monthly_bill_state',
  );

  // User's own data preserved.
  assert.ok(db.prepare('SELECT 1 FROM bills WHERE id=?').get(ownBill1), 'user bill 1 survives');
  assert.ok(db.prepare('SELECT 1 FROM bills WHERE id=?').get(ownBill2), 'user bill 2 survives');
  assert.equal(
    db.prepare('SELECT category_id c FROM bills WHERE id=?').get(ownBill1).c,
    healthcareId,
    'still categorised (not SET NULL)',
  );
  // Healthcare seeded category SURVIVES because a user bill still references it.
  assert.ok(
    db.prepare('SELECT 1 FROM categories WHERE id=?').get(healthcareId),
    'referenced seeded category kept',
  );
  // A seeded spending category with no user bill IS removed.
  assert.equal(
    num(db, "SELECT COUNT(*) n FROM categories WHERE user_id=? AND name='Groceries'", userA),
    0,
    'unreferenced seeded category removed',
  );
  // Default categories untouched.
  assert.ok(
    db.prepare('SELECT 1 FROM categories WHERE id=?').get(utilitiesId),
    'default category kept',
  );
  // Snowball extra-payment reset.
  assert.equal(db.prepare('SELECT snowball_extra_payment e FROM users WHERE id=?').get(userA).e, 0);
});

test("clear leaves other users' seeded data untouched", () => {
  assert.equal(
    num(db, 'SELECT COUNT(*) n FROM bills WHERE user_id=? AND is_seeded=1', userB),
    23,
    'user B intact after clearing A',
  );
  assert.ok(
    num(db, 'SELECT COUNT(*) n FROM transactions WHERE user_id=? AND is_seeded=1', userB) > 0,
  );
});

test('clear is audited to import_history', () => {
  const row = db
    .prepare(
      "SELECT file_type, source_filename FROM import_history WHERE user_id=? AND file_type='clear-demo' ORDER BY id DESC LIMIT 1",
    )
    .get(userA);
  assert.equal(row.file_type, 'clear-demo');
  assert.equal(row.source_filename, 'clear-demo-data');
});

test('eraseUserData still wipes seeded demo data wholesale', () => {
  assert.ok(num(db, 'SELECT COUNT(*) n FROM bills WHERE user_id=?', userC) > 0);
  eraseUserData(db, userC);
  assert.equal(num(db, 'SELECT COUNT(*) n FROM bills WHERE user_id=?', userC), 0);
  assert.equal(num(db, 'SELECT COUNT(*) n FROM transactions WHERE user_id=?', userC), 0);
  assert.equal(num(db, 'SELECT COUNT(*) n FROM snowball_plans WHERE user_id=?', userC), 0);
  assert.equal(
    num(
      db,
      'SELECT COUNT(*) n FROM payments p LEFT JOIN bills b ON b.id=p.bill_id WHERE b.id IS NULL',
    ),
    0,
    'no orphans after erase',
  );
});