1001 lines
55 KiB
JavaScript
1001 lines
55 KiB
JavaScript
#!/usr/bin/env node
|
|
'use strict';
|
|
|
|
// Standalone test for the spreadsheet import parsing logic.
|
|
// Run: node scripts/test-import.js
|
|
// Does NOT require a running server or DB. Tests parsing functions directly.
|
|
|
|
const xlsx = require('xlsx');
|
|
const assert = require('assert');
|
|
const path = require('path');
|
|
const fs = require('fs');
|
|
const { execFileSync } = require('child_process');
|
|
|
|
// Import the two exported pure helpers directly from the service.
|
|
// They don't touch the DB, so this is safe without a DB connection.
|
|
const {
|
|
parseSheetName,
|
|
resolveYearMonth,
|
|
findBillMatches: serviceFindBillMatches,
|
|
buildRecommendation: serviceBuildRecommendation,
|
|
} = require('../services/spreadsheetImportService');
|
|
|
|
// ─── Re-implement helpers for row-level tests (avoids DB init) ────────────────
|
|
|
|
function parseAmount(raw) {
|
|
if (raw == null) return null;
|
|
const str = String(raw).trim();
|
|
if (!str) return null;
|
|
const cleaned = str.replace(/[$,\s]/g, '').replace(/^\((.+)\)$/, '-$1');
|
|
const val = parseFloat(cleaned);
|
|
return Number.isFinite(val) ? val : null;
|
|
}
|
|
|
|
function parseDate(raw) {
|
|
if (raw == null) return null;
|
|
const str = String(raw).trim();
|
|
if (!str) return null;
|
|
let m = str.match(/^(\d{1,2})\/(\d{1,2})\/(\d{4})$/);
|
|
if (m) {
|
|
const [, mon, day, yr] = m;
|
|
return { year: parseInt(yr, 10), month: parseInt(mon, 10), day: parseInt(day, 10), iso: `${yr}-${mon.padStart(2,'0')}-${day.padStart(2,'0')}` };
|
|
}
|
|
m = str.match(/^(\d{4})-(\d{2})-(\d{2})$/);
|
|
if (m) return { year: parseInt(m[1],10), month: parseInt(m[2],10), day: parseInt(m[3],10), iso: str };
|
|
m = str.match(/^(\d{1,2})\/(\d{1,2})$/);
|
|
if (m) return { year: null, month: parseInt(m[1],10), day: parseInt(m[2],10), iso: null };
|
|
return null;
|
|
}
|
|
|
|
const LABEL_PATTERNS = {
|
|
autopay: /\bauto(?:pay)?\b/i,
|
|
past_due: /\bpast\s*due\b/i,
|
|
double_pay: /\bdouble\s*pay\b/i,
|
|
skipped: /\b(?:skip(?:ped)?|n\/?a|not\s*applicable)\b/i,
|
|
};
|
|
function detectLabels(text) {
|
|
if (!text) return [];
|
|
return Object.entries(LABEL_PATTERNS).filter(([,p])=>p.test(text)).map(([l])=>l);
|
|
}
|
|
|
|
function normalizeName(name) {
|
|
return String(name).trim().toLowerCase().replace(/\s+/g,' ').replace(/[.,!?;:'"()]/g,'').trim();
|
|
}
|
|
|
|
function isBlankRow(cells) { return cells.every(c=>c==null||String(c).trim()===''); }
|
|
function isLikelyTotalRow(c) { return c.some(v=>v!=null&&/^(?:total|subtotal|sum|grand\s*total)$/i.test(String(v).trim())); }
|
|
function isXlsxBuffer(buf) {
|
|
return Buffer.isBuffer(buf) && buf.length >= 4
|
|
&& buf[0]===0x50 && buf[1]===0x4b && buf[2]===0x03 && buf[3]===0x04;
|
|
}
|
|
|
|
// ─── Fixtures ─────────────────────────────────────────────────────────────────
|
|
|
|
function createSingleSheetXlsx() {
|
|
const wb = xlsx.utils.book_new();
|
|
const data = [
|
|
['Bill', 'Amount', 'Date', 'Notes'],
|
|
['Netflix', '$15.99', '5/1/2026', 'autopay'],
|
|
['Roadrunner ATV', '$1,460.62', '5/15/2026', ''],
|
|
['Discover (Austin)', '$39.60', '5/10/2026', 'past due'],
|
|
['', '', '', ''],
|
|
['Discover (Tilyn)', '$75.00', '5/10/2026', 'double pay'],
|
|
['RoadRunner', '$1,460.62', '5/15/2026', ''],
|
|
['TOTAL', '$3,051.21', '', ''],
|
|
['Unknown New Bill', '$250.00', '5/20/2026', 'auto'],
|
|
['Ghost Bill', '', '5/25/2026', ''],
|
|
['Partial Date Bill', '$100.00', '5/28', ''],
|
|
];
|
|
const ws = xlsx.utils.aoa_to_sheet(data);
|
|
xlsx.utils.book_append_sheet(wb, ws, 'May 2026');
|
|
const ws2 = xlsx.utils.aoa_to_sheet([['Other', '$1.00']]);
|
|
xlsx.utils.book_append_sheet(wb, ws2, 'Summary');
|
|
return xlsx.write(wb, { type: 'buffer', bookType: 'xlsx' });
|
|
}
|
|
|
|
function createMultiSheetXlsx() {
|
|
const wb = xlsx.utils.book_new();
|
|
|
|
// Tab 1: Jan 2026 — full month/year in name
|
|
const jan = [
|
|
['Bill', 'Amount', 'Notes'],
|
|
['Netflix', '$15.99', 'autopay'],
|
|
['Electric', '$120.00', ''],
|
|
['', '', ''],
|
|
['TOTAL', '$135.99', ''],
|
|
];
|
|
xlsx.utils.book_append_sheet(wb, xlsx.utils.aoa_to_sheet(jan), 'Jan 2026');
|
|
|
|
// Tab 2: Feb 2026 — full month/year, different format
|
|
const feb = [
|
|
['Bill', 'Amount', 'Notes'],
|
|
['Netflix', '$15.99', ''],
|
|
['Internet', '$59.99', 'auto'],
|
|
];
|
|
xlsx.utils.book_append_sheet(wb, xlsx.utils.aoa_to_sheet(feb), 'Feb 2026');
|
|
|
|
// Tab 3: Summary — should be skipped (non-month)
|
|
const summary = [
|
|
['Month', 'Total'],
|
|
['Jan 2026', '$135.99'],
|
|
['Feb 2026', '$75.98'],
|
|
];
|
|
xlsx.utils.book_append_sheet(wb, xlsx.utils.aoa_to_sheet(summary), 'Summary');
|
|
|
|
// Tab 4: May — month only, needs default_year
|
|
const may = [
|
|
['Bill', 'Amount', 'Notes'],
|
|
['Water', '$45.00', ''],
|
|
['Gas', '$30.00', 'past due'],
|
|
];
|
|
xlsx.utils.book_append_sheet(wb, xlsx.utils.aoa_to_sheet(may), 'May');
|
|
|
|
// Tab 5: Ambiguous — no month or year
|
|
const ambiguous = [
|
|
['Bill', 'Amount'],
|
|
['Mystery Bill', '$99.00'],
|
|
];
|
|
xlsx.utils.book_append_sheet(wb, xlsx.utils.aoa_to_sheet(ambiguous), 'Misc Data');
|
|
|
|
return xlsx.write(wb, { type: 'buffer', bookType: 'xlsx' });
|
|
}
|
|
|
|
// ─── Test runner ──────────────────────────────────────────────────────────────
|
|
|
|
let passed = 0;
|
|
let failed = 0;
|
|
|
|
function test(name, fn) {
|
|
try { fn(); console.log(` ✓ ${name}`); passed++; }
|
|
catch (err) { console.error(` ✗ ${name}\n ${err.message}`); failed++; }
|
|
}
|
|
|
|
// ─── parseSheetName ───────────────────────────────────────────────────────────
|
|
console.log('\n── parseSheetName — month+year patterns ─────────────────────────');
|
|
test('"Jan 2026"', ()=>{ const r=parseSheetName('Jan 2026'); assert.deepStrictEqual({year:r.year,month:r.month},{year:2026,month:1}); });
|
|
test('"January 2026"', ()=>{ const r=parseSheetName('January 2026'); assert.deepStrictEqual({year:r.year,month:r.month},{year:2026,month:1}); });
|
|
test('"Feb 2025"', ()=>{ const r=parseSheetName('Feb 2025'); assert.deepStrictEqual({year:r.year,month:r.month},{year:2025,month:2}); });
|
|
test('"March 2026"', ()=>{ const r=parseSheetName('March 2026'); assert.deepStrictEqual({year:r.year,month:r.month},{year:2026,month:3}); });
|
|
test('"2026-01"', ()=>{ const r=parseSheetName('2026-01'); assert.deepStrictEqual({year:r.year,month:r.month},{year:2026,month:1}); });
|
|
test('"2026-5"', ()=>{ const r=parseSheetName('2026-5'); assert.deepStrictEqual({year:r.year,month:r.month},{year:2026,month:5}); });
|
|
test('"01-2026"', ()=>{ const r=parseSheetName('01-2026'); assert.deepStrictEqual({year:r.year,month:r.month},{year:2026,month:1}); });
|
|
test('"02-2026"', ()=>{ const r=parseSheetName('02-2026'); assert.deepStrictEqual({year:r.year,month:r.month},{year:2026,month:2}); });
|
|
test('"Bills May 2026"', ()=>{ const r=parseSheetName('Bills May 2026'); assert.deepStrictEqual({year:r.year,month:r.month},{year:2026,month:5}); });
|
|
test('"2026 May"', ()=>{ const r=parseSheetName('2026 May'); assert.deepStrictEqual({year:r.year,month:r.month},{year:2026,month:5}); });
|
|
test('"September 2026"', ()=>{ const r=parseSheetName('September 2026'); assert.deepStrictEqual({year:r.year,month:r.month},{year:2026,month:9}); });
|
|
test('"Sept 2026"', ()=>{ const r=parseSheetName('Sept 2026'); assert.deepStrictEqual({year:r.year,month:r.month},{year:2026,month:9}); });
|
|
test('"Dec 2025"', ()=>{ const r=parseSheetName('Dec 2025'); assert.deepStrictEqual({year:r.year,month:r.month},{year:2025,month:12}); });
|
|
test('"July2017"', ()=>{ const r=parseSheetName('July2017'); assert.deepStrictEqual({year:r.year,month:r.month},{year:2017,month:7}); });
|
|
test('"August2017"', ()=>{ const r=parseSheetName('August2017'); assert.deepStrictEqual({year:r.year,month:r.month},{year:2017,month:8}); });
|
|
test('"September2017"', ()=>{ const r=parseSheetName('September2017'); assert.deepStrictEqual({year:r.year,month:r.month},{year:2017,month:9}); });
|
|
test('"Januaru 2021"', ()=>{ const r=parseSheetName('Januaru 2021'); assert.deepStrictEqual({year:r.year,month:r.month},{year:2021,month:1}); });
|
|
test('"Febuary 2023"', ()=>{ const r=parseSheetName('Febuary 2023'); assert.deepStrictEqual({year:r.year,month:r.month},{year:2023,month:2}); });
|
|
test('"Novevmber 2019"', ()=>{ const r=parseSheetName('Novevmber 2019'); assert.deepStrictEqual({year:r.year,month:r.month},{year:2019,month:11}); });
|
|
|
|
console.log('\n── parseSheetName — month only (no year) ────────────────────────');
|
|
test('"May"', ()=>{ const r=parseSheetName('May'); assert.strictEqual(r.month,5); assert.strictEqual(r.year,null); assert.strictEqual(r.is_non_month_sheet,false); });
|
|
test('"May Bills"', ()=>{ const r=parseSheetName('May Bills'); assert.strictEqual(r.month,5); assert.strictEqual(r.year,null); });
|
|
test('"January"', ()=>{ const r=parseSheetName('January'); assert.strictEqual(r.month,1); assert.strictEqual(r.year,null); });
|
|
test('"Oct"', ()=>{ const r=parseSheetName('Oct'); assert.strictEqual(r.month,10); });
|
|
|
|
console.log('\n── parseSheetName — known non-month sheets ──────────────────────');
|
|
test('"Summary"', ()=>assert.strictEqual(parseSheetName('Summary').is_non_month_sheet, true));
|
|
test('"Totals"', ()=>assert.strictEqual(parseSheetName('Totals').is_non_month_sheet, true));
|
|
test('"Total"', ()=>assert.strictEqual(parseSheetName('Total').is_non_month_sheet, true));
|
|
test('"Dashboard"', ()=>assert.strictEqual(parseSheetName('Dashboard').is_non_month_sheet, true));
|
|
test('"Notes"', ()=>assert.strictEqual(parseSheetName('Notes').is_non_month_sheet, true));
|
|
test('"Categories"', ()=>assert.strictEqual(parseSheetName('Categories').is_non_month_sheet, true));
|
|
test('"Settings"', ()=>assert.strictEqual(parseSheetName('Settings').is_non_month_sheet, true));
|
|
test('"Overview"', ()=>assert.strictEqual(parseSheetName('Overview').is_non_month_sheet, true));
|
|
test('"Template"', ()=>assert.strictEqual(parseSheetName('Template').is_non_month_sheet, true));
|
|
test('"Info"', ()=>assert.strictEqual(parseSheetName('Info').is_non_month_sheet, true));
|
|
test('"2018 taxes"', ()=>assert.strictEqual(parseSheetName('2018 taxes').is_non_month_sheet, true));
|
|
test('"debt totoals"', ()=>assert.strictEqual(parseSheetName('debt totoals').is_non_month_sheet, true));
|
|
test('"Sheet13"', ()=>assert.strictEqual(parseSheetName('Sheet13').is_non_month_sheet, true));
|
|
|
|
console.log('\n── parseSheetName — ambiguous (no month, not non-month) ─────────');
|
|
test('"Misc Data"', ()=>{ const r=parseSheetName('Misc Data'); assert.strictEqual(r.month,null); assert.strictEqual(r.is_non_month_sheet,false); });
|
|
test('"Q1"', ()=>{ const r=parseSheetName('Q1'); assert.strictEqual(r.month,null); assert.strictEqual(r.is_non_month_sheet,false); });
|
|
test('"1st - 14th"', ()=>{ const r=parseSheetName('1st - 14th'); assert.strictEqual(r.month,null); assert.strictEqual(r.is_non_month_sheet,false); });
|
|
test('empty string', ()=>{ const r=parseSheetName(''); assert.strictEqual(r.month,null); assert.strictEqual(r.is_non_month_sheet,false); });
|
|
|
|
// ─── resolveYearMonth ─────────────────────────────────────────────────────────
|
|
console.log('\n── resolveYearMonth — source tracking ───────────────────────────');
|
|
test('full date in row → row_date', ()=>{
|
|
const r = resolveYearMonth({year:2026,month:5,day:1,iso:'2026-05-01'}, null, null, null, null);
|
|
assert.strictEqual(r.source,'row_date'); assert.strictEqual(r.year,2026); assert.strictEqual(r.month,5);
|
|
});
|
|
test('partial date + sheetYear → sheet_name', ()=>{
|
|
const r = resolveYearMonth({year:null,month:5,day:10,iso:null}, 2026, null, null, null);
|
|
assert.strictEqual(r.source,'sheet_name'); assert.strictEqual(r.year,2026); assert.strictEqual(r.month,5);
|
|
});
|
|
test('partial date + defaultYear → default', ()=>{
|
|
const r = resolveYearMonth({year:null,month:3,day:5,iso:null}, null, null, 2025, null);
|
|
assert.strictEqual(r.source,'default'); assert.strictEqual(r.year,2025); assert.strictEqual(r.month,3);
|
|
});
|
|
test('partial date, no year anywhere → ambiguous', ()=>{
|
|
const r = resolveYearMonth({year:null,month:5,day:10,iso:null}, null, null, null, null);
|
|
assert.strictEqual(r.source,'ambiguous'); assert.strictEqual(r.year,null);
|
|
assert.ok(r.warnings.length > 0, 'should have warning');
|
|
});
|
|
test('no row date, sheetMonth+sheetYear → sheet_name', ()=>{
|
|
const r = resolveYearMonth(null, 2026, 1, null, null);
|
|
assert.strictEqual(r.source,'sheet_name'); assert.strictEqual(r.year,2026); assert.strictEqual(r.month,1);
|
|
});
|
|
test('no row date, sheetMonth no year, defaultYear → sheet_name', ()=>{
|
|
const r = resolveYearMonth(null, null, 5, 2026, null);
|
|
assert.strictEqual(r.source,'sheet_name'); assert.strictEqual(r.year,2026); assert.strictEqual(r.month,5);
|
|
});
|
|
test('no row date, sheetMonth no year, no defaultYear → ambiguous', ()=>{
|
|
const r = resolveYearMonth(null, null, 5, null, null);
|
|
assert.strictEqual(r.source,'ambiguous'); assert.strictEqual(r.year,null); assert.strictEqual(r.month,5);
|
|
assert.ok(r.warnings.some(w=>w.includes('Year unknown')));
|
|
});
|
|
test('no row date, no sheet, defaults present → default', ()=>{
|
|
const r = resolveYearMonth(null, null, null, 2026, 4);
|
|
assert.strictEqual(r.source,'default'); assert.strictEqual(r.year,2026); assert.strictEqual(r.month,4);
|
|
});
|
|
test('nothing available → ambiguous', ()=>{
|
|
const r = resolveYearMonth(null, null, null, null, null);
|
|
assert.strictEqual(r.source,'ambiguous'); assert.strictEqual(r.year,null); assert.strictEqual(r.month,null);
|
|
});
|
|
|
|
// ─── parseAmount ──────────────────────────────────────────────────────────────
|
|
console.log('\n── parseAmount ──────────────────────────────────────────────────');
|
|
test('plain number', ()=>assert.strictEqual(parseAmount('15.99'), 15.99));
|
|
test('dollar + commas', ()=>assert.strictEqual(parseAmount('$1,460.62'),1460.62));
|
|
test('bare integer', ()=>assert.strictEqual(parseAmount('39'), 39));
|
|
test('zero', ()=>assert.strictEqual(parseAmount('0'), 0));
|
|
test('null for empty', ()=>assert.strictEqual(parseAmount(''), null));
|
|
test('null for text', ()=>assert.strictEqual(parseAmount('autopay'), null));
|
|
test('null for null', ()=>assert.strictEqual(parseAmount(null), null));
|
|
test('accounting negative', ()=>assert.strictEqual(parseAmount('(50.00)'), -50.00));
|
|
|
|
// ─── parseDate ────────────────────────────────────────────────────────────────
|
|
console.log('\n── parseDate ────────────────────────────────────────────────────');
|
|
test('MM/DD/YYYY', ()=>assert.deepStrictEqual(parseDate('5/1/2026'), {year:2026,month:5,day:1,iso:'2026-05-01'}));
|
|
test('YYYY-MM-DD', ()=>assert.deepStrictEqual(parseDate('2026-05-15'),{year:2026,month:5,day:15,iso:'2026-05-15'}));
|
|
test('MM/DD no yr', ()=>assert.deepStrictEqual(parseDate('5/28'), {year:null,month:5,day:28,iso:null}));
|
|
test('null empty', ()=>assert.strictEqual(parseDate(''), null));
|
|
test('null text', ()=>assert.strictEqual(parseDate('autopay'), null));
|
|
|
|
// ─── detectLabels ─────────────────────────────────────────────────────────────
|
|
console.log('\n── detectLabels ─────────────────────────────────────────────────');
|
|
test('autopay', ()=>assert.deepStrictEqual(detectLabels('autopay'), ['autopay']));
|
|
test('auto', ()=>assert.deepStrictEqual(detectLabels('auto'), ['autopay']));
|
|
test('past_due', ()=>assert.deepStrictEqual(detectLabels('past due'), ['past_due']));
|
|
test('double_pay', ()=>assert.deepStrictEqual(detectLabels('double pay'), ['double_pay']));
|
|
test('N/A', ()=>assert.deepStrictEqual(detectLabels('N/A'), ['skipped']));
|
|
test('multiple', ()=>{ const l=detectLabels('past due double pay'); assert.ok(l.includes('past_due')&&l.includes('double_pay')); });
|
|
test('none', ()=>assert.deepStrictEqual(detectLabels('Netflix'), []));
|
|
|
|
// ─── normalizeName ────────────────────────────────────────────────────────────
|
|
console.log('\n── normalizeName ────────────────────────────────────────────────');
|
|
test('lowercases + trims', ()=>assert.strictEqual(normalizeName(' Netflix '), 'netflix'));
|
|
test('collapses spaces', ()=>assert.strictEqual(normalizeName('Road Runner'), 'road runner'));
|
|
test('strips punctuation', ()=>assert.strictEqual(normalizeName('Discover.'), 'discover'));
|
|
test('strips parens around text', ()=>assert.strictEqual(normalizeName('Discover (Austin)'), 'discover austin'));
|
|
|
|
// ─── recommendation logic ─────────────────────────────────────────────────────
|
|
console.log('\n── recommendation logic ─────────────────────────────────────────');
|
|
const recBills = [
|
|
{ id: 1, name: 'Capital One', category_id: 4, category_name: 'Credit Cards', due_day: 10, expected_amount: 85.23 },
|
|
{ id: 2, name: 'Austin Discover', category_id: 4, category_name: 'Credit Cards', due_day: 12, expected_amount: 39.60 },
|
|
{ id: 3, name: 'Roadrunner ATV', category_id: 5, category_name: 'Loans', due_day: 15, expected_amount: 1460.62 },
|
|
{ id: 4, name: 'Roadrunner Internet', category_id: 2, category_name: 'Utilities', due_day: 8, expected_amount: 79.99 },
|
|
{ id: 5, name: 'Netflix', category_id: 3, category_name: 'Subscriptions', due_day: 1, expected_amount: 15.99 },
|
|
];
|
|
const recCategories = [
|
|
{ id: 2, name: 'Utilities' },
|
|
{ id: 3, name: 'Subscriptions' },
|
|
{ id: 4, name: 'Credit Cards' },
|
|
{ id: 5, name: 'Loans' },
|
|
];
|
|
const baseRecInput = {
|
|
detectedAmount: 85.23,
|
|
parsedDate: { year: 2026, month: 5, day: 12, iso: '2026-05-12' },
|
|
dateHeader: 'Due Date',
|
|
detectedCategory: null,
|
|
notesText: null,
|
|
categories: recCategories,
|
|
warnings: [],
|
|
errors: [],
|
|
};
|
|
test('"Capital" matches existing "Capital One"', ()=>{
|
|
const matches = serviceFindBillMatches('Capital', recBills);
|
|
assert.strictEqual(matches[0].bill_id, 1);
|
|
assert.ok(['medium','high'].includes(matches[0].match_confidence));
|
|
});
|
|
test('"Cap One" matches existing "Capital One"', ()=>{
|
|
const matches = serviceFindBillMatches('Cap One', recBills);
|
|
assert.strictEqual(matches[0].bill_id, 1);
|
|
assert.ok(['medium','high'].includes(matches[0].match_confidence));
|
|
});
|
|
test('existing bill exact match recommends match_existing_bill', ()=>{
|
|
const possibleMatches = serviceFindBillMatches('Netflix', recBills);
|
|
const rec = serviceBuildRecommendation({ ...baseRecInput, billName: 'Netflix', detectedAmount: 15.99, possibleMatches });
|
|
assert.strictEqual(rec.action, 'match_existing_bill');
|
|
assert.strictEqual(rec.bill_id, 5);
|
|
assert.strictEqual(rec.confidence, 'high');
|
|
});
|
|
test('weak/ambiguous match with multiple candidates requires decision', ()=>{
|
|
const possibleMatches = serviceFindBillMatches('Roadrunner', recBills);
|
|
const rec = serviceBuildRecommendation({ ...baseRecInput, billName: 'Roadrunner', possibleMatches });
|
|
assert.strictEqual(rec.action, 'ambiguous');
|
|
assert.ok(possibleMatches.length >= 2);
|
|
});
|
|
test('no match recommends create_new_bill', ()=>{
|
|
const possibleMatches = serviceFindBillMatches('New Gym', recBills);
|
|
const rec = serviceBuildRecommendation({ ...baseRecInput, billName: 'New Gym', possibleMatches });
|
|
assert.strictEqual(rec.action, 'create_new_bill');
|
|
assert.strictEqual(rec.bill_name, 'New Gym');
|
|
});
|
|
test('category recommendation uses existing category', ()=>{
|
|
const rec = serviceBuildRecommendation({
|
|
...baseRecInput,
|
|
billName: 'Electric',
|
|
detectedAmount: 120,
|
|
possibleMatches: [],
|
|
});
|
|
assert.strictEqual(rec.action, 'create_new_bill');
|
|
assert.strictEqual(rec.category_id, 2);
|
|
assert.strictEqual(rec.category_name, 'Utilities');
|
|
});
|
|
test('due date parsed into due_day', ()=>{
|
|
const possibleMatches = serviceFindBillMatches('Capital One', recBills);
|
|
const rec = serviceBuildRecommendation({ ...baseRecInput, billName: 'Capital One', possibleMatches });
|
|
assert.strictEqual(rec.due_day, 12);
|
|
});
|
|
test('due day mismatch warning', ()=>{
|
|
const possibleMatches = serviceFindBillMatches('Capital One', recBills);
|
|
const rec = serviceBuildRecommendation({ ...baseRecInput, billName: 'Capital One', possibleMatches });
|
|
assert.ok(rec.warnings.includes('Spreadsheet due day differs from current bill due day'));
|
|
});
|
|
test('amount mismatch warning', ()=>{
|
|
const possibleMatches = serviceFindBillMatches('Capital One', recBills);
|
|
const rec = serviceBuildRecommendation({ ...baseRecInput, billName: 'Capital One', detectedAmount: 99, possibleMatches });
|
|
assert.ok(rec.warnings.includes('Spreadsheet amount differs from current bill expected amount'));
|
|
});
|
|
test('summary/blank rows still skipped', ()=>{
|
|
const rec = serviceBuildRecommendation({
|
|
...baseRecInput,
|
|
billName: null,
|
|
detectedAmount: null,
|
|
possibleMatches: [],
|
|
warnings: ['No amount detected'],
|
|
errors: ['No bill name detected'],
|
|
});
|
|
assert.strictEqual(rec.action, 'skip_row');
|
|
});
|
|
|
|
// ─── row classification ───────────────────────────────────────────────────────
|
|
console.log('\n── row classification ───────────────────────────────────────────');
|
|
test('blank row', ()=>assert.ok(isBlankRow(['','',null,' '])));
|
|
test('non-blank row', ()=>assert.ok(!isBlankRow(['Netflix','$15','',''])));
|
|
test('total row', ()=>assert.ok(isLikelyTotalRow(['TOTAL','$3,051','',''])));
|
|
test('non-total row', ()=>assert.ok(!isLikelyTotalRow(['Netflix','$15','',''])));
|
|
|
|
// ─── XLSX magic bytes ─────────────────────────────────────────────────────────
|
|
console.log('\n── XLSX magic bytes ─────────────────────────────────────────────');
|
|
test('valid XLSX accepted', ()=>assert.ok(isXlsxBuffer(createSingleSheetXlsx())));
|
|
test('random buffer rejected',()=>assert.ok(!isXlsxBuffer(Buffer.from([0,1,2,3]))));
|
|
test('empty buffer rejected', ()=>assert.ok(!isXlsxBuffer(Buffer.alloc(0))));
|
|
|
|
// ─── Single-sheet XLSX round-trip ─────────────────────────────────────────────
|
|
console.log('\n── Single-sheet XLSX round-trip ─────────────────────────────────');
|
|
test('valid buffer', ()=>{ const buf=createSingleSheetXlsx(); assert.ok(isXlsxBuffer(buf)); assert.ok(buf.length>100); });
|
|
test('rows correct', ()=>{
|
|
const buf=createSingleSheetXlsx();
|
|
const wb=xlsx.read(buf,{type:'buffer',cellFormula:false});
|
|
const rows=xlsx.utils.sheet_to_json(wb.Sheets['May 2026'],{header:1,defval:null,raw:false});
|
|
assert.strictEqual(rows[0][0],'Bill');
|
|
assert.strictEqual(rows[1][0],'Netflix');
|
|
assert.strictEqual(rows[1][1],'$15.99');
|
|
assert.strictEqual(rows[1][3],'autopay');
|
|
assert.ok(isBlankRow(rows[4]||[]),'row 4 blank');
|
|
assert.strictEqual(rows[7][0],'TOTAL');
|
|
assert.strictEqual(wb.SheetNames[1],'Summary');
|
|
});
|
|
test('amounts parse',()=>{
|
|
const buf=createSingleSheetXlsx();
|
|
const rows=xlsx.utils.sheet_to_json(xlsx.read(buf,{type:'buffer',cellFormula:false}).Sheets['May 2026'],{header:1,defval:null,raw:false});
|
|
assert.strictEqual(parseAmount(rows[1][1]),15.99);
|
|
assert.strictEqual(parseAmount(rows[2][1]),1460.62);
|
|
assert.strictEqual(parseAmount(rows[9][1]),null,'Ghost Bill no amount');
|
|
});
|
|
test('labels in rows',()=>{
|
|
const buf=createSingleSheetXlsx();
|
|
const rows=xlsx.utils.sheet_to_json(xlsx.read(buf,{type:'buffer',cellFormula:false}).Sheets['May 2026'],{header:1,defval:null,raw:false});
|
|
assert.ok(detectLabels(String(rows[1][3]||'')).includes('autopay'));
|
|
assert.ok(detectLabels(String(rows[3][3]||'')).includes('past_due'));
|
|
assert.ok(detectLabels(String(rows[5][3]||'')).includes('double_pay'));
|
|
});
|
|
test('partial date parsed',()=>{
|
|
const buf=createSingleSheetXlsx();
|
|
const rows=xlsx.utils.sheet_to_json(xlsx.read(buf,{type:'buffer',cellFormula:false}).Sheets['May 2026'],{header:1,defval:null,raw:false});
|
|
const d=parseDate(rows[10][2]);
|
|
assert.strictEqual(d.month,5); assert.strictEqual(d.day,28); assert.strictEqual(d.year,null);
|
|
});
|
|
|
|
// ─── Multi-sheet XLSX round-trip ──────────────────────────────────────────────
|
|
console.log('\n── Multi-sheet XLSX round-trip ──────────────────────────────────');
|
|
test('creates valid XLSX',()=>{ const b=createMultiSheetXlsx(); assert.ok(isXlsxBuffer(b)); });
|
|
test('has 5 sheets',()=>{
|
|
const wb=xlsx.read(createMultiSheetXlsx(),{type:'buffer',cellFormula:false});
|
|
assert.strictEqual(wb.SheetNames.length,5);
|
|
assert.deepStrictEqual(wb.SheetNames,['Jan 2026','Feb 2026','Summary','May','Misc Data']);
|
|
});
|
|
test('Jan 2026 sheet → year=2026 month=1',()=>{
|
|
const r=parseSheetName('Jan 2026');
|
|
assert.strictEqual(r.year,2026); assert.strictEqual(r.month,1); assert.strictEqual(r.is_non_month_sheet,false);
|
|
});
|
|
test('Feb 2026 sheet → year=2026 month=2',()=>{
|
|
const r=parseSheetName('Feb 2026');
|
|
assert.strictEqual(r.year,2026); assert.strictEqual(r.month,2);
|
|
});
|
|
test('Summary sheet → non-month',()=>{
|
|
assert.strictEqual(parseSheetName('Summary').is_non_month_sheet,true);
|
|
});
|
|
test('May sheet → month=5, no year',()=>{
|
|
const r=parseSheetName('May');
|
|
assert.strictEqual(r.month,5); assert.strictEqual(r.year,null); assert.strictEqual(r.is_non_month_sheet,false);
|
|
});
|
|
test('May + default_year=2026 → sheet_name source',()=>{
|
|
const r=resolveYearMonth(null, null, 5, 2026, null);
|
|
assert.strictEqual(r.month,5); assert.strictEqual(r.year,2026); assert.strictEqual(r.source,'sheet_name');
|
|
});
|
|
test('Misc Data → ambiguous, no month',()=>{
|
|
const r=parseSheetName('Misc Data');
|
|
assert.strictEqual(r.month,null); assert.strictEqual(r.is_non_month_sheet,false);
|
|
});
|
|
test('Jan rows have no dates → sheet provides year/month',()=>{
|
|
const wb = xlsx.read(createMultiSheetXlsx(),{type:'buffer',cellFormula:false});
|
|
const rows = xlsx.utils.sheet_to_json(wb.Sheets['Jan 2026'],{header:1,defval:null,raw:false});
|
|
// Row 1 (Netflix) has no date cell — year/month should come from sheet name
|
|
const { year: sy, month: sm } = parseSheetName('Jan 2026');
|
|
const resolved = resolveYearMonth(null, sy, sm, null, null);
|
|
assert.strictEqual(resolved.year,2026); assert.strictEqual(resolved.month,1);
|
|
assert.strictEqual(resolved.source,'sheet_name');
|
|
});
|
|
test('blank/total rows in Jan sheet',()=>{
|
|
const wb = xlsx.read(createMultiSheetXlsx(),{type:'buffer',cellFormula:false});
|
|
const rows = xlsx.utils.sheet_to_json(wb.Sheets['Jan 2026'],{header:1,defval:null,raw:false});
|
|
assert.ok(isBlankRow(rows[3]||[]),'blank row in Jan');
|
|
assert.ok(isLikelyTotalRow(rows[4]||[]),'total row in Jan');
|
|
});
|
|
test('May amounts parse correctly',()=>{
|
|
const wb = xlsx.read(createMultiSheetXlsx(),{type:'buffer',cellFormula:false});
|
|
const rows = xlsx.utils.sheet_to_json(wb.Sheets['May'],{header:1,defval:null,raw:false});
|
|
assert.strictEqual(parseAmount(rows[1][1]),45.00,'Water $45');
|
|
assert.strictEqual(parseAmount(rows[2][1]),30.00,'Gas $30');
|
|
});
|
|
test('past_due label in May Gas row',()=>{
|
|
const wb = xlsx.read(createMultiSheetXlsx(),{type:'buffer',cellFormula:false});
|
|
const rows = xlsx.utils.sheet_to_json(wb.Sheets['May'],{header:1,defval:null,raw:false});
|
|
assert.ok(detectLabels(String(rows[2][2]||'')).includes('past_due'));
|
|
});
|
|
|
|
// ─── Apply preserves tab-derived year/month ───────────────────────────────────
|
|
console.log('\n── apply: tab-derived year/month preserved in resolveYear/Month ─');
|
|
test('sheet-derived year flows through resolveYear', ()=>{
|
|
// Simulate previewRow as stored in session (detected_year came from sheet name)
|
|
const previewRow = { detected_year: 2026, detected_month: 1 };
|
|
const sessionData = { default_year: null, default_month: null };
|
|
const decision = { row_id: 's0_r1', action: 'match_existing_bill', bill_id: 1 };
|
|
// resolveYear reads previewRow.detected_year which was populated from sheet name during preview
|
|
const yr = decision.year ?? previewRow.detected_year ?? sessionData.default_year ?? null;
|
|
const mo = decision.month ?? previewRow.detected_month ?? sessionData.default_month ?? null;
|
|
assert.strictEqual(yr, 2026, 'year from sheet name preserved');
|
|
assert.strictEqual(mo, 1, 'month from sheet name preserved');
|
|
});
|
|
test('decision year overrides sheet-derived year', ()=>{
|
|
const previewRow = { detected_year: 2026, detected_month: 1 };
|
|
const sessionData = { default_year: null };
|
|
const decision = { year: 2025 }; // explicit override
|
|
const yr = decision.year ?? previewRow.detected_year ?? sessionData.default_year ?? null;
|
|
assert.strictEqual(yr, 2025, 'explicit decision year wins');
|
|
});
|
|
|
|
// ─── Apply regression: match existing bill ───────────────────────────────────
|
|
console.log('\n── apply: match_existing_bill regression ────────────────────────');
|
|
|
|
function runApplyRegression(name, body) {
|
|
test(name, () => {
|
|
const dbPath = path.join('/tmp', `bill-tracker-import-${process.pid}-${name.replace(/[^a-z0-9]+/gi, '-')}.sqlite`);
|
|
try { fs.unlinkSync(dbPath); } catch {}
|
|
execFileSync(process.execPath, ['-e', body], {
|
|
cwd: path.join(__dirname, '..'),
|
|
env: { ...process.env, DB_PATH: dbPath },
|
|
stdio: ['ignore', 'pipe', 'pipe'],
|
|
});
|
|
try { fs.unlinkSync(dbPath); } catch {}
|
|
try { fs.unlinkSync(`${dbPath}-wal`); } catch {}
|
|
try { fs.unlinkSync(`${dbPath}-shm`); } catch {}
|
|
});
|
|
}
|
|
|
|
const APPLY_SETUP = `
|
|
const assert = require('assert');
|
|
const xlsx = require('xlsx');
|
|
const { getDb, ensureUserDefaultCategories } = require('./db/database');
|
|
const { previewSpreadsheet, applyImportDecisions } = require('./services/spreadsheetImportService');
|
|
async function setup() {
|
|
const db = getDb();
|
|
db.prepare('INSERT INTO users (username,password_hash) VALUES (?,?)').run('importer','x');
|
|
const userId = db.prepare('SELECT id FROM users WHERE username=?').get('importer').id;
|
|
ensureUserDefaultCategories(userId);
|
|
const categoryId = db.prepare('SELECT id FROM categories WHERE name=? AND user_id=?').get('Other', userId).id;
|
|
const billId = db.prepare('INSERT INTO bills (user_id,name,category_id,due_day,expected_amount,active) VALUES (?,?,?,?,?,1)')
|
|
.run(userId, 'Capital One', categoryId, 12, 80).lastInsertRowid;
|
|
const wb = xlsx.utils.book_new();
|
|
xlsx.utils.book_append_sheet(wb, xlsx.utils.aoa_to_sheet([
|
|
['Bill','Amount','Due Date','Paid Date','Notes'],
|
|
['Capital One','85.23','5/12/2026','5/13/2026','monthly note'],
|
|
['New Gym','25.00','5/20/2026','5/21/2026',''],
|
|
]), 'May 2026');
|
|
const preview = await previewSpreadsheet(userId, xlsx.write(wb, { type: 'buffer', bookType: 'xlsx' }), { default_year: 2026 });
|
|
return { db, userId, billId, preview };
|
|
}
|
|
`;
|
|
|
|
runApplyRegression('match_existing_bill succeeds with numeric bill_id', `
|
|
${APPLY_SETUP}
|
|
(async () => {
|
|
const { db, userId, billId, preview } = await setup();
|
|
const row = preview.rows.find(r => r.detected_bill_name === 'Capital One');
|
|
const result = await applyImportDecisions(userId, preview.import_session_id, [{
|
|
row_id: row.row_id, action: 'match_existing_bill', bill_id: billId, year: 2026, month: 5,
|
|
actual_amount: 85.23, notes: 'monthly note',
|
|
}], {});
|
|
assert.strictEqual(result.rows_created, 1);
|
|
assert.strictEqual(result.rows_errored, 0);
|
|
const state = db.prepare('SELECT * FROM monthly_bill_state WHERE bill_id=? AND year=2026 AND month=5').get(billId);
|
|
assert.strictEqual(state.actual_amount, 85.23);
|
|
assert.strictEqual(state.notes, 'monthly note');
|
|
const bill = db.prepare('SELECT expected_amount, due_day FROM bills WHERE id=?').get(billId);
|
|
assert.strictEqual(bill.expected_amount, 80);
|
|
assert.strictEqual(bill.due_day, 12);
|
|
})().catch(err => { console.error(err.stack || err); process.exit(1); });
|
|
`);
|
|
|
|
runApplyRegression('paid date column creates payment for matched bill', `
|
|
${APPLY_SETUP}
|
|
(async () => {
|
|
const { db, userId, billId, preview } = await setup();
|
|
const row = preview.rows.find(r => r.detected_bill_name === 'Capital One');
|
|
assert.strictEqual(row.detected_paid_date, '2026-05-13');
|
|
assert.strictEqual(row.detected_payment_amount, 85.23);
|
|
const result = await applyImportDecisions(userId, preview.import_session_id, [{
|
|
row_id: row.row_id,
|
|
action: 'match_existing_bill',
|
|
bill_id: billId,
|
|
year: 2026,
|
|
month: 5,
|
|
actual_amount: 85.23,
|
|
payment_amount: row.detected_payment_amount,
|
|
payment_date: row.detected_paid_date,
|
|
}], {});
|
|
assert.strictEqual(result.rows_created, 1);
|
|
assert.strictEqual(result.rows_errored, 0);
|
|
assert.strictEqual(result.details[0].payment, 'created');
|
|
const payment = db.prepare('SELECT amount, paid_date FROM payments WHERE bill_id=?').get(billId);
|
|
assert.strictEqual(payment.amount, 85.23);
|
|
assert.strictEqual(payment.paid_date, '2026-05-13');
|
|
})().catch(err => { console.error(err.stack || err); process.exit(1); });
|
|
`);
|
|
|
|
runApplyRegression('paid date column creates payment for new bill', `
|
|
${APPLY_SETUP}
|
|
(async () => {
|
|
const { db, userId, preview } = await setup();
|
|
const row = preview.rows.find(r => r.detected_bill_name === 'New Gym');
|
|
assert.strictEqual(row.detected_paid_date, '2026-05-21');
|
|
const result = await applyImportDecisions(userId, preview.import_session_id, [{
|
|
row_id: row.row_id,
|
|
action: 'create_new_bill',
|
|
bill_name: 'New Gym',
|
|
due_day: 20,
|
|
expected_amount: 25,
|
|
actual_amount: 25,
|
|
year: 2026,
|
|
month: 5,
|
|
payment_amount: row.detected_payment_amount,
|
|
payment_date: row.detected_paid_date,
|
|
}], {});
|
|
assert.strictEqual(result.rows_created, 1);
|
|
assert.strictEqual(result.rows_errored, 0);
|
|
assert.strictEqual(result.details[0].payment, 'created');
|
|
const bill = db.prepare('SELECT id FROM bills WHERE name=?').get('New Gym');
|
|
const payment = db.prepare('SELECT amount, paid_date FROM payments WHERE bill_id=?').get(bill.id);
|
|
assert.strictEqual(payment.amount, 25);
|
|
assert.strictEqual(payment.paid_date, '2026-05-21');
|
|
})().catch(err => { console.error(err.stack || err); process.exit(1); });
|
|
`);
|
|
|
|
runApplyRegression('create_new_bill imports related paid months for same detected bill', `
|
|
const assert = require('assert');
|
|
const xlsx = require('xlsx');
|
|
const { getDb } = require('./db/database');
|
|
const { previewSpreadsheet, applyImportDecisions } = require('./services/spreadsheetImportService');
|
|
(async () => {
|
|
const db = getDb();
|
|
db.prepare('INSERT INTO users (username,password_hash) VALUES (?,?)').run('importer','x');
|
|
const userId = db.prepare('SELECT id FROM users WHERE username=?').get('importer').id;
|
|
const wb = xlsx.utils.book_new();
|
|
xlsx.utils.book_append_sheet(wb, xlsx.utils.aoa_to_sheet([
|
|
['Due Date','Bill','Amount','Paid Date'],
|
|
['4/1','Electric','156','4/2'],
|
|
]), 'April 2017');
|
|
xlsx.utils.book_append_sheet(wb, xlsx.utils.aoa_to_sheet([
|
|
['Due Date','Bill','Amount','Paid Date'],
|
|
['5/1','Electric','183','4/28'],
|
|
]), 'May 2017');
|
|
xlsx.utils.book_append_sheet(wb, xlsx.utils.aoa_to_sheet([
|
|
['Due Date','Bill','Amount','Paid Date'],
|
|
['5/1','Water','50','5/3'],
|
|
]), 'May 2017 Other');
|
|
const preview = await previewSpreadsheet(userId, xlsx.write(wb, { type: 'buffer', bookType: 'xlsx' }), { parse_all_sheets: true });
|
|
const row = preview.rows.find(r => r.sheet_name === 'April 2017' && r.detected_bill_name === 'Electric');
|
|
const result = await applyImportDecisions(userId, preview.import_session_id, [{
|
|
row_id: row.row_id,
|
|
action: 'create_new_bill',
|
|
bill_name: 'Electric',
|
|
due_day: 1,
|
|
expected_amount: 156,
|
|
actual_amount: 156,
|
|
year: 2017,
|
|
month: 4,
|
|
payment_amount: row.detected_payment_amount,
|
|
payment_date: row.detected_paid_date,
|
|
}], { reviewed_skipped_count: preview.rows.length - 1 });
|
|
assert.strictEqual(result.rows_created, 2);
|
|
assert.strictEqual(result.rows_skipped, 1);
|
|
const createdDetail = result.details.find(d => d.action === 'create_new_bill');
|
|
assert.strictEqual(createdDetail.related_months_imported, 1);
|
|
const bill = db.prepare('SELECT id FROM bills WHERE name=?').get('Electric');
|
|
const states = db.prepare('SELECT year, month, actual_amount FROM monthly_bill_state WHERE bill_id=? ORDER BY year, month').all(bill.id);
|
|
assert.deepStrictEqual(states.map(s => [s.year, s.month, s.actual_amount]), [[2017, 4, 156], [2017, 5, 183]]);
|
|
const payments = db.prepare('SELECT amount, paid_date FROM payments WHERE bill_id=? ORDER BY paid_date').all(bill.id);
|
|
assert.deepStrictEqual(payments.map(p => [p.amount, p.paid_date]), [[156, '2017-04-02'], [183, '2017-04-28']]);
|
|
})().catch(err => { console.error(err.stack || err); process.exit(1); });
|
|
`);
|
|
|
|
runApplyRegression('match_existing_bill succeeds with string bill_id', `
|
|
${APPLY_SETUP}
|
|
(async () => {
|
|
const { db, userId, billId, preview } = await setup();
|
|
const row = preview.rows.find(r => r.detected_bill_name === 'Capital One');
|
|
const result = await applyImportDecisions(userId, preview.import_session_id, [{
|
|
row_id: row.row_id, action: 'match_existing_bill', bill_id: String(billId), year: '2026', month: '5',
|
|
actual_amount: 85.23,
|
|
}], {});
|
|
assert.strictEqual(result.rows_created, 1);
|
|
assert.strictEqual(result.rows_errored, 0);
|
|
const state = db.prepare('SELECT actual_amount FROM monthly_bill_state WHERE bill_id=?').get(billId);
|
|
assert.strictEqual(state.actual_amount, 85.23);
|
|
})().catch(err => { console.error(err.stack || err); process.exit(1); });
|
|
`);
|
|
|
|
runApplyRegression('match_existing_bill missing bill_id returns status 400', `
|
|
${APPLY_SETUP}
|
|
(async () => {
|
|
const { userId, preview } = await setup();
|
|
const row = preview.rows.find(r => r.detected_bill_name === 'Capital One');
|
|
let err = null;
|
|
try {
|
|
await applyImportDecisions(userId, preview.import_session_id, [{ row_id: row.row_id, action: 'match_existing_bill', year: 2026, month: 5 }], {});
|
|
} catch (e) { err = e; }
|
|
assert.ok(err);
|
|
assert.strictEqual(err.status, 400);
|
|
assert.ok(err.message.includes('bill_id is required'));
|
|
})().catch(err => { console.error(err.stack || err); process.exit(1); });
|
|
`);
|
|
|
|
runApplyRegression('match_existing_bill invalid bill_id returns clear error not crash', `
|
|
${APPLY_SETUP}
|
|
(async () => {
|
|
const { userId, preview } = await setup();
|
|
const row = preview.rows.find(r => r.detected_bill_name === 'Capital One');
|
|
let err = null;
|
|
try {
|
|
await applyImportDecisions(userId, preview.import_session_id, [{ row_id: row.row_id, action: 'match_existing_bill', bill_id: 'abc', year: 2026, month: 5 }], {});
|
|
} catch (e) { err = e; }
|
|
assert.ok(err);
|
|
assert.strictEqual(err.status, 400);
|
|
assert.ok(err.message.includes('integer'));
|
|
})().catch(err => { console.error(err.stack || err); process.exit(1); });
|
|
`);
|
|
|
|
runApplyRegression('unsupported action returns clear 400', `
|
|
${APPLY_SETUP}
|
|
(async () => {
|
|
const { userId, preview } = await setup();
|
|
const row = preview.rows.find(r => r.detected_bill_name === 'Capital One');
|
|
let err = null;
|
|
try {
|
|
await applyImportDecisions(userId, preview.import_session_id, [{ row_id: row.row_id, action: 'teleport_bill' }], {});
|
|
} catch (e) { err = e; }
|
|
assert.ok(err);
|
|
assert.strictEqual(err.status, 400);
|
|
assert.strictEqual(err.code, 'IMPORT_VALIDATION_ERROR');
|
|
assert.strictEqual(err.details[0].field, 'action');
|
|
})().catch(err => { console.error(err.stack || err); process.exit(1); });
|
|
`);
|
|
|
|
runApplyRegression('unknown row_id returns clear 400', `
|
|
${APPLY_SETUP}
|
|
(async () => {
|
|
const { userId, preview, billId } = await setup();
|
|
let err = null;
|
|
try {
|
|
await applyImportDecisions(userId, preview.import_session_id, [{ row_id: 'row_9999', action: 'match_existing_bill', bill_id: billId, year: 2026, month: 5 }], {});
|
|
} catch (e) { err = e; }
|
|
assert.ok(err);
|
|
assert.strictEqual(err.status, 400);
|
|
assert.strictEqual(err.details[0].field, 'row_id');
|
|
})().catch(err => { console.error(err.stack || err); process.exit(1); });
|
|
`);
|
|
|
|
runApplyRegression('create_new_bill without bill_name returns clear 400', `
|
|
${APPLY_SETUP}
|
|
(async () => {
|
|
const { userId, preview } = await setup();
|
|
const row = preview.rows.find(r => r.detected_bill_name === 'New Gym');
|
|
let err = null;
|
|
try {
|
|
await applyImportDecisions(userId, preview.import_session_id, [{ row_id: row.row_id, action: 'create_new_bill', year: 2026, month: 5 }], {});
|
|
} catch (e) { err = e; }
|
|
assert.ok(err);
|
|
assert.strictEqual(err.status, 400);
|
|
assert.strictEqual(err.details[0].field, 'bill_name');
|
|
})().catch(err => { console.error(err.stack || err); process.exit(1); });
|
|
`);
|
|
|
|
runApplyRegression('invalid year and month return clear 400', `
|
|
${APPLY_SETUP}
|
|
(async () => {
|
|
const { userId, preview, billId } = await setup();
|
|
const row = preview.rows.find(r => r.detected_bill_name === 'Capital One');
|
|
let err = null;
|
|
try {
|
|
await applyImportDecisions(userId, preview.import_session_id, [{ row_id: row.row_id, action: 'match_existing_bill', bill_id: billId, year: 3026, month: 99 }], {});
|
|
} catch (e) { err = e; }
|
|
assert.ok(err);
|
|
assert.strictEqual(err.status, 400);
|
|
assert.ok(['year','month'].includes(err.details[0].field));
|
|
})().catch(err => { console.error(err.stack || err); process.exit(1); });
|
|
`);
|
|
|
|
runApplyRegression('bulk-style create_new_bill without category_id does not crash', `
|
|
${APPLY_SETUP}
|
|
(async () => {
|
|
const { db, userId, preview } = await setup();
|
|
const row = preview.rows.find(r => r.detected_bill_name === 'New Gym');
|
|
const result = await applyImportDecisions(userId, preview.import_session_id, [{
|
|
row_id: row.row_id,
|
|
action: 'create_new_bill',
|
|
bill_name: 'New Gym',
|
|
due_day: 20,
|
|
expected_amount: 25,
|
|
actual_amount: 25,
|
|
year: 2026,
|
|
month: 5,
|
|
notes: null,
|
|
}], {});
|
|
assert.strictEqual(result.rows_created, 1);
|
|
assert.strictEqual(result.rows_errored, 0);
|
|
assert.ok(db.prepare('SELECT id FROM bills WHERE name=?').get('New Gym'));
|
|
})().catch(err => { console.error(err.stack || err); process.exit(1); });
|
|
`);
|
|
|
|
runApplyRegression('skip_row requires no bill_id year or month', `
|
|
${APPLY_SETUP}
|
|
(async () => {
|
|
const { userId, preview } = await setup();
|
|
const row = preview.rows.find(r => r.detected_bill_name === 'Capital One');
|
|
const result = await applyImportDecisions(userId, preview.import_session_id, [{ row_id: row.row_id, action: 'skip_row' }], {});
|
|
assert.strictEqual(result.rows_skipped, 1);
|
|
assert.strictEqual(result.rows_errored, 0);
|
|
})().catch(err => { console.error(err.stack || err); process.exit(1); });
|
|
`);
|
|
|
|
runApplyRegression('omitted reviewed skip rows are recorded in summary and history', `
|
|
${APPLY_SETUP}
|
|
(async () => {
|
|
const { db, userId, preview } = await setup();
|
|
const row = preview.rows.find(r => r.detected_bill_name === 'New Gym');
|
|
const result = await applyImportDecisions(userId, preview.import_session_id, [{
|
|
row_id: row.row_id,
|
|
action: 'create_new_bill',
|
|
bill_name: 'New Gym',
|
|
due_day: 20,
|
|
expected_amount: 25,
|
|
actual_amount: 25,
|
|
year: 2026,
|
|
month: 5,
|
|
}], { reviewed_skipped_count: 1 });
|
|
assert.strictEqual(result.rows_created, 1);
|
|
assert.strictEqual(result.rows_skipped, 1);
|
|
const hist = db.prepare('SELECT rows_skipped FROM import_history ORDER BY id DESC LIMIT 1').get();
|
|
assert.strictEqual(hist.rows_skipped, 1);
|
|
})().catch(err => { console.error(err.stack || err); process.exit(1); });
|
|
`);
|
|
|
|
runApplyRegression('match_existing_bill nonexistent bill_id is row error not 500', `
|
|
${APPLY_SETUP}
|
|
(async () => {
|
|
const { userId, preview } = await setup();
|
|
const row = preview.rows.find(r => r.detected_bill_name === 'Capital One');
|
|
const result = await applyImportDecisions(userId, preview.import_session_id, [{
|
|
row_id: row.row_id, action: 'match_existing_bill', bill_id: 99999, year: 2026, month: 5,
|
|
}], {});
|
|
assert.strictEqual(result.rows_errored, 1);
|
|
assert.ok(result.details[0].error.includes('not found or inactive'));
|
|
})().catch(err => { console.error(err.stack || err); process.exit(1); });
|
|
`);
|
|
|
|
runApplyRegression('create_new_bill and skip_row still work', `
|
|
${APPLY_SETUP}
|
|
(async () => {
|
|
const { db, userId, preview } = await setup();
|
|
const newRow = preview.rows.find(r => r.detected_bill_name === 'New Gym');
|
|
const matchRow = preview.rows.find(r => r.detected_bill_name === 'Capital One');
|
|
const result = await applyImportDecisions(userId, preview.import_session_id, [
|
|
{ row_id: newRow.row_id, action: 'create_new_bill', bill_name: 'New Gym', due_day: 20, expected_amount: 25, actual_amount: 25, year: 2026, month: 5 },
|
|
{ row_id: matchRow.row_id, action: 'skip_row' },
|
|
], {});
|
|
assert.strictEqual(result.rows_created, 1);
|
|
assert.strictEqual(result.rows_skipped, 1);
|
|
assert.ok(db.prepare('SELECT id FROM bills WHERE name=?').get('New Gym'));
|
|
})().catch(err => { console.error(err.stack || err); process.exit(1); });
|
|
`);
|
|
|
|
console.log('\n── user SQLite import regression ────────────────────────────────');
|
|
|
|
const USER_DB_SETUP = `
|
|
const assert = require('assert');
|
|
const fs = require('fs');
|
|
const path = require('path');
|
|
const Database = require('better-sqlite3');
|
|
const { getDb, ensureUserDefaultCategories } = require('./db/database');
|
|
const { previewUserDbImport, applyUserDbImport } = require('./services/userDbImportService');
|
|
|
|
function createExportBuffer(nameSuffix = '') {
|
|
const file = path.join('/tmp', 'bill-tracker-user-export-test-' + process.pid + '-' + Date.now() + nameSuffix + '.sqlite');
|
|
const out = new Database(file);
|
|
out.exec(\`
|
|
CREATE TABLE export_metadata (key TEXT PRIMARY KEY, value TEXT);
|
|
CREATE TABLE categories (id INTEGER PRIMARY KEY, name TEXT, created_at TEXT, updated_at TEXT);
|
|
CREATE TABLE bills (id INTEGER PRIMARY KEY, name TEXT, category_id INTEGER, due_day INTEGER, override_due_date TEXT, bucket TEXT, expected_amount REAL, interest_rate REAL, billing_cycle TEXT, autopay_enabled INTEGER, autodraft_status TEXT, website TEXT, username TEXT, account_info TEXT, has_2fa INTEGER, active INTEGER, notes TEXT, created_at TEXT, updated_at TEXT);
|
|
CREATE TABLE payments (id INTEGER PRIMARY KEY, bill_id INTEGER, amount REAL, paid_date TEXT, method TEXT, notes TEXT, created_at TEXT, updated_at TEXT);
|
|
CREATE TABLE monthly_bill_state (id INTEGER PRIMARY KEY, bill_id INTEGER, year INTEGER, month INTEGER, actual_amount REAL, notes TEXT, is_skipped INTEGER, created_at TEXT, updated_at TEXT);
|
|
CREATE TABLE notes (type TEXT, bill_id INTEGER, payment_id INTEGER, monthly_state_id INTEGER, year INTEGER, month INTEGER, notes TEXT);
|
|
\`);
|
|
out.prepare('INSERT INTO export_metadata (key, value) VALUES (?, ?)').run('metadata_json', JSON.stringify({ export_type: 'user_data', exported_at: '2026-05-03T00:00:00.000Z', includes: ['Bills','Payments','Categories','Monthly bill state','Notes','Export metadata'] }));
|
|
out.prepare('INSERT INTO categories (id, name) VALUES (?, ?)').run(10, 'Imported Utilities');
|
|
out.prepare('INSERT INTO bills (id, name, category_id, due_day, expected_amount, interest_rate, billing_cycle, active, notes) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)').run(20, 'Electric', 10, 15, 156, 29.99, 'monthly', 1, 'bill note');
|
|
out.prepare('INSERT INTO payments (id, bill_id, amount, paid_date, method, notes) VALUES (?, ?, ?, ?, ?, ?)').run(30, 20, 156, '2026-05-15', 'card', 'paid');
|
|
out.prepare('INSERT INTO monthly_bill_state (id, bill_id, year, month, actual_amount, notes, is_skipped) VALUES (?, ?, ?, ?, ?, ?, ?)').run(40, 20, 2026, 5, 156, 'monthly', 0);
|
|
out.close();
|
|
const buffer = fs.readFileSync(file);
|
|
fs.unlinkSync(file);
|
|
return buffer;
|
|
}
|
|
|
|
function setupUser() {
|
|
const db = getDb();
|
|
db.prepare('INSERT INTO users (username,password_hash) VALUES (?,?)').run('sqlite-importer','x');
|
|
const userId = db.prepare('SELECT id FROM users WHERE username=?').get('sqlite-importer').id;
|
|
ensureUserDefaultCategories(userId);
|
|
return { db, userId };
|
|
}
|
|
`;
|
|
|
|
runApplyRegression('user SQLite preview reads app user export and writes no live data', `
|
|
${USER_DB_SETUP}
|
|
(async () => {
|
|
const { db, userId } = setupUser();
|
|
const beforeBills = db.prepare('SELECT COUNT(*) AS n FROM bills WHERE user_id=?').get(userId).n;
|
|
const preview = await previewUserDbImport(userId, createExportBuffer(), { original_filename: 'user-export.sqlite' });
|
|
assert.strictEqual(preview.import_type, 'user_db');
|
|
assert.strictEqual(preview.counts.bills, 1);
|
|
assert.strictEqual(preview.counts.payments, 1);
|
|
assert.strictEqual(preview.summary.bills.create, 1);
|
|
const afterBills = db.prepare('SELECT COUNT(*) AS n FROM bills WHERE user_id=?').get(userId).n;
|
|
assert.strictEqual(afterBills, beforeBills);
|
|
})().catch(err => { console.error(err.stack || err); process.exit(1); });
|
|
`);
|
|
|
|
runApplyRegression('user SQLite apply imports signed-in user data and records history', `
|
|
${USER_DB_SETUP}
|
|
(async () => {
|
|
const { db, userId } = setupUser();
|
|
const preview = await previewUserDbImport(userId, createExportBuffer('-apply'), { original_filename: 'user-export.sqlite' });
|
|
const result = await applyUserDbImport(userId, preview.import_session_id, {});
|
|
assert.strictEqual(result.rows_errored, 0);
|
|
assert.ok(result.rows_created >= 4);
|
|
const bill = db.prepare('SELECT * FROM bills WHERE user_id=? AND name=?').get(userId, 'Electric');
|
|
assert.ok(bill);
|
|
assert.strictEqual(bill.interest_rate, 29.99);
|
|
assert.ok(db.prepare('SELECT id FROM payments WHERE bill_id=? AND amount=?').get(bill.id, 156));
|
|
assert.ok(db.prepare('SELECT id FROM monthly_bill_state WHERE bill_id=? AND year=2026 AND month=5').get(bill.id));
|
|
const hist = db.prepare("SELECT file_type, rows_created FROM import_history WHERE user_id=? ORDER BY id DESC LIMIT 1").get(userId);
|
|
assert.strictEqual(hist.file_type, 'sqlite');
|
|
assert.ok(hist.rows_created >= 4);
|
|
})().catch(err => { console.error(err.stack || err); process.exit(1); });
|
|
`);
|
|
|
|
runApplyRegression('user SQLite conflicts are skipped by default', `
|
|
${USER_DB_SETUP}
|
|
(async () => {
|
|
const { db, userId } = setupUser();
|
|
const preview1 = await previewUserDbImport(userId, createExportBuffer('-one'), {});
|
|
await applyUserDbImport(userId, preview1.import_session_id, {});
|
|
const preview2 = await previewUserDbImport(userId, createExportBuffer('-two'), {});
|
|
const result = await applyUserDbImport(userId, preview2.import_session_id, {});
|
|
assert.strictEqual(result.rows_errored, 0);
|
|
assert.ok(result.rows_conflicted > 0);
|
|
assert.strictEqual(db.prepare('SELECT COUNT(*) AS n FROM bills WHERE user_id=? AND name=?').get(userId, 'Electric').n, 1);
|
|
})().catch(err => { console.error(err.stack || err); process.exit(1); });
|
|
`);
|
|
|
|
runApplyRegression('user SQLite invalid file is rejected safely', `
|
|
${USER_DB_SETUP}
|
|
(async () => {
|
|
const { userId } = setupUser();
|
|
try {
|
|
await previewUserDbImport(userId, Buffer.from('not sqlite'), {});
|
|
assert.fail('expected invalid sqlite rejection');
|
|
} catch (err) {
|
|
assert.strictEqual(err.status, 400);
|
|
assert.strictEqual(err.code, 'USER_DB_IMPORT_INVALID_SQLITE');
|
|
}
|
|
})().catch(err => { console.error(err.stack || err); process.exit(1); });
|
|
`);
|
|
|
|
runApplyRegression('user SQLite full app backup shape is rejected safely', `
|
|
${USER_DB_SETUP}
|
|
(async () => {
|
|
const { userId } = setupUser();
|
|
const file = '/tmp/bill-tracker-full-backup-shape-' + process.pid + '.sqlite';
|
|
try { fs.unlinkSync(file); } catch {}
|
|
const out = new Database(file);
|
|
out.exec('CREATE TABLE users (id INTEGER PRIMARY KEY, username TEXT, password_hash TEXT); CREATE TABLE sessions (id TEXT PRIMARY KEY, user_id INTEGER);');
|
|
out.close();
|
|
const buffer = fs.readFileSync(file);
|
|
fs.unlinkSync(file);
|
|
try {
|
|
await previewUserDbImport(userId, buffer, {});
|
|
assert.fail('expected full app backup shape rejection');
|
|
} catch (err) {
|
|
assert.strictEqual(err.status, 400);
|
|
assert.ok(['USER_DB_IMPORT_UNSUPPORTED_SCHEMA', 'USER_DB_IMPORT_NOT_USER_EXPORT'].includes(err.code));
|
|
}
|
|
})().catch(err => { console.error(err.stack || err); process.exit(1); });
|
|
`);
|
|
|
|
// ─── Save fixtures ────────────────────────────────────────────────────────────
|
|
const singlePath = path.join(__dirname, 'test-import-fixture.xlsx');
|
|
const multiPath = path.join(__dirname, 'test-import-multi-fixture.xlsx');
|
|
fs.writeFileSync(singlePath, createSingleSheetXlsx());
|
|
fs.writeFileSync(multiPath, createMultiSheetXlsx());
|
|
|
|
console.log(`\n── Fixture files ────────────────────────────────────────────────`);
|
|
console.log(` Single-sheet: ${singlePath}`);
|
|
console.log(` Multi-sheet: ${multiPath}`);
|
|
console.log(`\n Test with curl (requires running server + valid session cookie):`);
|
|
console.log(` # Single sheet:`);
|
|
console.log(` curl -b bt_session=<session> -X POST \\`);
|
|
console.log(` http://localhost:3000/api/import/spreadsheet/preview \\`);
|
|
console.log(` -H 'Content-Type: application/octet-stream' \\`);
|
|
console.log(` --data-binary @${singlePath}`);
|
|
console.log(` # All sheets:`);
|
|
console.log(` curl -b bt_session=<session> -X POST \\`);
|
|
console.log(` 'http://localhost:3000/api/import/spreadsheet/preview?parse_all_sheets=true&year=2026' \\`);
|
|
console.log(` -H 'Content-Type: application/octet-stream' \\`);
|
|
console.log(` --data-binary @${multiPath}`);
|
|
|
|
console.log(`\n─────────────────────────────────────────────────────────────────`);
|
|
console.log(` ${passed} passed, ${failed} failed\n`);
|
|
if (failed > 0) process.exit(1);
|