#!/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);