/**
 * Household Finance Tracker - statement importer
 * Version 1.2 (October 2026): re-asks for any permission box left unticked on first use
 * Version 1.1: QIF files and the Credit Debit Indicator column
 *
 * Runs inside the tracker's own Google Sheet. It can only read and write THIS
 * spreadsheet (see @OnlyCurrentDoc below). It never touches Google Drive,
 * Gmail or the internet. Statement files are read in your browser and only the
 * transaction rows are written into the Transactions tab.
 *
 * @OnlyCurrentDoc
 */

// ---------------------------------------------------------------- settings
var TX_SHEET = 'Transactions';
var ACC_SHEET = 'Accounts';
var RULE_SHEET = 'Rules';
var CAT_SHEET = 'Categories';
var LOG_SHEET = 'Import Log';
var MATCH_DAYS = 5;          // transfer legs must post within this many days
var MAX_FILE_BYTES = 5e6;    // per statement file

// ---------------------------------------------------------------- menu
function onOpen() {
  SpreadsheetApp.getUi().createMenu('Finance Tracker')
    .addItem('Import statements...', 'showImportDialog')
    .addSeparator()
    .addItem('Apply rules to uncategorized rows', 'menuApplyRules')
    .addItem('Match transfers', 'menuMatchTransfers')
    .addItem('Learn rules from my categories', 'menuLearnRules')
    .addSeparator()
    .addItem('Help', 'showHelp')
    .addToUi();
}

/**
 * Google lets people untick individual permission boxes. Without both, the
 * import fails later with a confusing error, so stop here and show Google's
 * permission screen again. Needs no extra permission of its own.
 */
function requirePermissions_() {
  ScriptApp.requireAllScopes(ScriptApp.AuthMode.FULL);
}

function showImportDialog() {
  requirePermissions_();
  var html = HtmlService.createHtmlOutputFromFile('ImportDialog').setWidth(760).setHeight(580);
  SpreadsheetApp.getUi().showModalDialog(html, 'Import statements');
}

function showHelp() {
  SpreadsheetApp.getUi().alert('Finance Tracker importer',
    '1. Download each account\'s statement as QFX/OFX (best) or CSV.\n' +
    '2. Finance Tracker > Import statements, pick all the files at once.\n' +
    '3. Check the account chosen for each file, then Import.\n' +
    '4. Fix rows flagged in column M of Transactions.\n' +
    '5. Run "Learn rules from my categories" so next month needs fewer fixes.\n\n' +
    'Re-importing the same file is safe: duplicates are skipped.',
    SpreadsheetApp.getUi().ButtonSet.OK);
}

// ================================================================ PURE CORE
// Everything in this section is plain JavaScript with no Google services, so
// it can be unit tested outside Sheets.

function parseCsvText(text) {
  text = String(text).replace(/^/, '');
  var rows = [], row = [], cell = '', q = false;
  for (var i = 0; i < text.length; i++) {
    var ch = text[i];
    if (q) {
      if (ch === '"') {
        if (text[i + 1] === '"') { cell += '"'; i++; } else { q = false; }
      } else { cell += ch; }
    } else if (ch === '"') { q = true; }
    else if (ch === ',') { row.push(cell); cell = ''; }
    else if (ch === '\n' || ch === '\r') {
      if (ch === '\r' && text[i + 1] === '\n') i++;
      row.push(cell); rows.push(row); row = []; cell = '';
    } else { cell += ch; }
  }
  if (cell !== '' || row.length) { row.push(cell); rows.push(row); }
  return rows.map(function (r) { return r.map(function (c) { return c.trim(); }); })
             .filter(function (r) { return r.some(function (c) { return c !== ''; }); });
}

function parseMoney(s) {
  if (s === null || s === undefined) return null;
  s = String(s).trim();
  if (s === '' || s === '-' || s === '--') return null;
  var neg = false;
  if (/^\(.*\)$/.test(s)) { neg = true; s = s.slice(1, -1); }
  if (/\s*CR$/i.test(s)) { s = s.replace(/\s*CR$/i, ''); }
  else if (/\s*DR$/i.test(s)) { neg = !neg; s = s.replace(/\s*DR$/i, ''); }
  s = s.replace(/[$,\s]/g, '').replace(/USD$/i, '');
  if (s.charAt(0) === '+') s = s.slice(1);
  if (!/^-?\d*\.?\d+$/.test(s)) return null;
  var v = parseFloat(s);
  return Math.round((neg ? -v : v) * 100) / 100;
}

var MONTHS_ = { jan: 1, feb: 2, mar: 3, apr: 4, may: 5, jun: 6, jul: 7, aug: 8, sep: 9, oct: 10, nov: 11, dec: 12 };

/** Returns 'YYYY-MM-DD' or null. Assumes US month-first order. */
function parseDateStr(s) {
  s = String(s || '').trim();
  var m, y, mo, d;
  if ((m = s.match(/^(\d{4})[-\/.](\d{1,2})[-\/.](\d{1,2})/))) { y = +m[1]; mo = +m[2]; d = +m[3]; }
  else if ((m = s.match(/^(\d{8})/))) { y = +s.slice(0, 4); mo = +s.slice(4, 6); d = +s.slice(6, 8); }
  else if ((m = s.match(/^(\d{1,2})[-\/.](\d{1,2})[-\/.](\d{2,4})$/))) {
    mo = +m[1]; d = +m[2]; y = +m[3]; if (y < 100) y += 2000;
  } else if ((m = s.match(/^([A-Za-z]{3})[a-z]*\.?\s+(\d{1,2}),?\s+(\d{4})$/))) {
    mo = MONTHS_[m[1].toLowerCase()]; d = +m[2]; y = +m[3];
  } else if ((m = s.match(/^(\d{1,2})\s+([A-Za-z]{3})[a-z]*\.?,?\s+(\d{4})$/))) {
    d = +m[1]; mo = MONTHS_[m[2].toLowerCase()]; y = +m[3];
  } else { return null; }
  if (!mo || mo < 1 || mo > 12 || d < 1 || d > 31 || y < 1990 || y > 2100) return null;
  return y + '-' + (mo < 10 ? '0' : '') + mo + '-' + (d < 10 ? '0' : '') + d;
}

function dayNum_(ymd) {
  var p = ymd.split('-');
  return Date.UTC(+p[0], +p[1] - 1, +p[2]) / 86400000;
}

function decodeEntities_(s) {
  return String(s).replace(/&amp;/gi, '&').replace(/&lt;/gi, '<').replace(/&gt;/gi, '>')
                  .replace(/&quot;/gi, '"').replace(/&apos;|&#39;/gi, "'").replace(/&nbsp;/gi, ' ');
}

function ofxTag_(block, tag) {
  var m = block.match(new RegExp('<' + tag + '>([^<\\r\\n]*)', 'i'));
  return m ? decodeEntities_(m[1]).trim() : '';
}

/** OFX / QFX (SGML 1.x or XML 2.x). One file can hold several accounts. */
function parseOfx(text) {
  var out = [];
  var parts = String(text).split(/<(?:STMTRS|CCSTMTRS)>/i);
  var kinds = String(text).match(/<(?:STMTRS|CCSTMTRS)>/gi) || [];
  for (var p = 1; p < parts.length; p++) {
    var body = parts[p];
    var isCard = /CCSTMTRS/i.test(kinds[p - 1]);
    var acct = ofxTag_(body, 'ACCTID');
    var txns = [];
    var blocks = body.split(/<STMTTRN>/i).slice(1);
    blocks.forEach(function (b) {
      b = b.split(/<\/STMTTRN>|<\/BANKTRANLIST>/i)[0];
      var date = parseDateStr(ofxTag_(b, 'DTPOSTED').slice(0, 8));
      var amt = parseMoney(ofxTag_(b, 'TRNAMT'));
      if (!date || amt === null) return;
      var name = ofxTag_(b, 'NAME'), memo = ofxTag_(b, 'MEMO');
      var desc = name || memo;
      if (name && memo && memo.toUpperCase().indexOf(name.toUpperCase()) === 0) desc = memo;
      txns.push({ date: date, amount: amt, desc: desc.replace(/\s+/g, ' '), id: ofxTag_(b, 'FITID') });
    });
    out.push({ format: 'OFX', last4: (acct.replace(/\D/g, '').slice(-4)), isCard: isCard,
               txns: txns, signKnown: true, warnings: [] });
  }
  return out;
}

var COLS_ = {
  date: ['transaction date', 'trans. date', 'trans date', 'date', 'posting date', 'posted date', 'post date', 'date posted'],
  desc: ['description', 'transaction description', 'original description', 'merchant', 'payee', 'name', 'memo'],
  amount: ['amount', 'amount (usd)', 'transaction amount', 'amt'],
  debit: ['debit', 'debits', 'withdrawal', 'withdrawals', 'debit amount', 'money out', 'withdrawal amount'],
  credit: ['credit', 'credits', 'deposit', 'deposits', 'credit amount', 'money in', 'deposit amount'],
  type: ['credit debit indicator', 'debit credit indicator', 'credit/debit', 'debit/credit', 'transaction type', 'type', 'details'],
  status: ['status', 'state', 'transaction status'],
  acct: ['account number', 'card no.', 'card no', 'card number', 'account', 'card']
};

function findCol_(header, names) {
  var h = header.map(function (x) { return x.toLowerCase().replace(/\s+/g, ' ').trim(); });
  for (var i = 0; i < names.length; i++) {
    var j = h.indexOf(names[i]);
    if (j >= 0) return j;
  }
  return -1;
}

/** Generic CSV statement parser: finds the header row and maps columns by name. */
function parseCsvStatement(text) {
  var rows = parseCsvText(text);
  var warnings = [];
  var hi = -1;
  for (var i = 0; i < Math.min(rows.length, 40); i++) {
    var low = rows[i].map(function (c) { return c.toLowerCase(); });
    var hasDate = low.some(function (c) { return /date/.test(c) && c.length < 25; });
    var hasMoney = low.some(function (c) { return /^(amount|amount \(usd\)|transaction amount|debit|credit|withdrawals?|deposits?)$/.test(c); });
    if (hasDate && hasMoney) { hi = i; break; }
  }
  var C, data;
  if (hi < 0) {
    // Header-less layout (e.g. Wells Fargo): date, amount, *, check#, description
    if (rows.length && parseDateStr(rows[0][0]) && parseMoney(rows[0][1]) !== null) {
      C = { date: 0, amount: 1, desc: rows[0].length - 1, debit: -1, credit: -1, type: -1, status: -1, acct: -1, desc2: -1 };
      data = rows;
    } else {
      return [{ format: 'CSV', last4: '', isCard: false, txns: [], signKnown: false,
                warnings: ['Could not find a header row with Date and Amount columns.'] }];
    }
  } else {
    var h = rows[hi];
    C = {};
    Object.keys(COLS_).forEach(function (k) { C[k] = findCol_(h, COLS_[k]); });
    if (C.date < 0) C.date = h.findIndex(function (x) { return /date/i.test(x); });
    if (C.desc < 0) C.desc = h.findIndex(function (x) { return /desc|merchant|payee|name/i.test(x); });
    C.desc2 = findCol_(h, ['merchant', 'payee', 'name', 'memo']);
    if (C.desc2 === C.desc) C.desc2 = -1;
    data = rows.slice(hi + 1);
  }
  var txns = [], last4 = '', signKnown = false, typeVals = {}, skipped = 0;
  var useDebitCredit = C.amount < 0 && (C.debit >= 0 || C.credit >= 0);
  if (useDebitCredit) signKnown = true;
  data.forEach(function (r) {
    if (C.status >= 0 && /pending|declined|failed|cancel/i.test(r[C.status] || '')) { skipped++; return; }
    var date = parseDateStr(r[C.date]);
    if (!date) return;
    var amt;
    if (useDebitCredit) {
      var dr = parseMoney(r[C.debit]), cr = parseMoney(r[C.credit]);
      if (dr === null && cr === null) return;
      amt = Math.round(((cr ? Math.abs(cr) : 0) - (dr ? Math.abs(dr) : 0)) * 100) / 100;
    } else {
      amt = parseMoney(r[C.amount]);
      if (amt === null) return;
    }
    var t = C.type >= 0 ? String(r[C.type] || '').trim().toLowerCase() : '';
    if (t) typeVals[t] = true;
    if (!last4 && C.acct >= 0) last4 = String(r[C.acct] || '').replace(/\D/g, '').slice(-4);
    var desc = String(r[C.desc] || '').replace(/\s+/g, ' ').trim();
    if (!desc && C.desc2 >= 0) desc = String(r[C.desc2] || '').replace(/\s+/g, ' ').trim();
    txns.push({ date: date, amount: amt, desc: desc, type: t });
  });
  // Unsigned amount + Credit/Debit type column (e.g. Capital One 360)
  var tv = Object.keys(typeVals);
  var creditDebitOnly = tv.length > 0 && tv.every(function (v) { return v === 'credit' || v === 'debit'; });
  var allPositive = txns.length > 0 && txns.every(function (t) { return t.amount >= 0; });
  if (!useDebitCredit && creditDebitOnly && allPositive) {
    txns.forEach(function (t) { if (t.type === 'debit') t.amount = -t.amount; });
    signKnown = true;
  } else if (!useDebitCredit && creditDebitOnly) {
    signKnown = true; // already signed, type column agrees (e.g. Chase checking)
  }
  if (skipped) warnings.push(skipped + ' pending/declined rows skipped.');
  txns.forEach(function (t) { delete t.type; });
  return [{ format: 'CSV', last4: last4, isCard: false, txns: txns, signKnown: signKnown, warnings: warnings }];
}

/** QIF (Quicken Interchange Format): D=date, T/U=amount, P=payee, M=memo, ^ ends a record. Amounts are signed. */
function parseQif(text) {
  var txns = [], cur = {}, isCard = /^!Type:CCard/im.test(text);
  String(text).split(/\r?\n/).forEach(function (line) {
    var c = line.charAt(0), v = line.slice(1).trim();
    if (c === '^') {
      if (cur.date && cur.amount !== null && cur.amount !== undefined) {
        txns.push({ date: cur.date, amount: cur.amount, desc: (cur.payee || cur.memo || '').replace(/\s+/g, ' ') });
      }
      cur = {};
    } else if (c === 'D') {
      cur.date = parseDateStr(v.replace(/'\s*/, '/').replace(/\s+/g, ''));
    } else if (c === 'T' || (c === 'U' && cur.amount === undefined)) {
      cur.amount = parseMoney(v);
    } else if (c === 'P') { cur.payee = v; }
    else if (c === 'M') { cur.memo = v; }
  });
  return [{ format: 'QIF', last4: '', isCard: isCard, txns: txns, signKnown: true, warnings: [] }];
}

function parseStatementFile(name, text) {
  var head = String(text).slice(0, 2000);
  if (/\.qif$/i.test(name) || /^!Type:/im.test(head)) return parseQif(text);
  var isOfx = /\.(qfx|ofx|qbo)$/i.test(name) || /<OFX>|OFXHEADER/i.test(head);
  return isOfx ? parseOfx(text) : parseCsvStatement(text);
}

/**
 * Applies the account's sign setting. Returns {txns, flipped}.
 * sign: 'auto' (default), 'as-is', 'flip'.
 * Auto flips a credit card file only when the format did not already fix the
 * sign and most rows are positive (purchases shown as positive, e.g. Amex,
 * Discover, Apple Card CSV).
 */
function normalizeSign(st, account) {
  var mode = String((account && account.sign) || 'auto').toLowerCase();
  var isCard = st.isCard || /card/i.test((account && account.type) || '');
  var flip = false;
  if (mode === 'flip') flip = true;
  else if (mode === 'auto' || mode === '') {
    if (!st.signKnown && isCard && st.txns.length) {
      var pos = st.txns.filter(function (t) { return t.amount > 0; }).length;
      flip = pos > st.txns.length / 2;
    }
  }
  var txns = st.txns.map(function (t) {
    return { date: t.date, amount: flip ? -t.amount : t.amount, desc: t.desc };
  });
  return { txns: txns, flipped: flip };
}

function guessAccount(fileName, st, accounts) {
  var fn = String(fileName).toLowerCase();
  if (st.last4) {
    var byLast4 = accounts.filter(function (a) { return a.last4 && a.last4 === st.last4; });
    if (byLast4.length === 1) return byLast4[0].label;
  }
  for (var i = 0; i < accounts.length; i++) {
    var keys = String(accounts[i].fileKey || '').split(/[,;]/).map(function (k) { return k.trim().toLowerCase(); })
                 .filter(String);
    if (keys.some(function (k) { return fn.indexOf(k) >= 0; })) return accounts[i].label;
  }
  var squash = function (s) { return String(s).toLowerCase().replace(/[^a-z0-9]/g, ''); };
  var fs = squash(fileName);
  var hits = accounts.filter(function (a) { return a.label && fs.indexOf(squash(a.label)) >= 0; });
  return hits.length === 1 ? hits[0].label : '';
}

function descKey_(desc) {
  return String(desc || '').toUpperCase().replace(/[^A-Z0-9]/g, '').slice(0, 20);
}

function txKey_(account, date, amount, desc) {
  return account + '|' + date + '|' + Math.round(amount * 100) + '|' + descKey_(desc);
}

/**
 * Count-based de-duplication: a key already present N times in the sheet lets
 * through only incoming copies beyond N. Two real $5 coffees on the same day
 * survive; re-importing the same file adds nothing.
 */
function dedupe(incoming, existing) {
  var have = {};
  existing.forEach(function (r) { var k = txKey_(r.account, r.date, r.amount, r.desc); have[k] = (have[k] || 0) + 1; });
  var seen = {}, keep = [], dupes = 0;
  incoming.forEach(function (r) {
    var k = txKey_(r.account, r.date, r.amount, r.desc);
    seen[k] = (seen[k] || 0) + 1;
    if (seen[k] <= (have[k] || 0)) { dupes++; } else { keep.push(r); }
  });
  return { rows: keep, dupes: dupes };
}

/** Rule keyword: plain text = "contains" (case-insensitive); /text/ = regex. Longest keyword wins. */
function prepareRules(rules) {
  return rules.filter(function (r) { return r.keyword && r.category; }).map(function (r) {
    var k = String(r.keyword).trim(), re = null;
    if (k.length > 2 && k.charAt(0) === '/' && k.lastIndexOf('/') > 0) {
      try { re = new RegExp(k.slice(1, k.lastIndexOf('/')), 'i'); } catch (e) { re = null; }
    }
    return { keyword: k, up: k.toUpperCase().replace(/\s+/g, ' '), re: re, category: r.category };
  }).sort(function (a, b) { return b.up.length - a.up.length; });
}

function categorize(desc, prepared) {
  var d = String(desc || '').toUpperCase().replace(/\s+/g, ' ');
  for (var i = 0; i < prepared.length; i++) {
    var r = prepared[i];
    if (r.re ? r.re.test(desc) : d.indexOf(r.up) >= 0) return r.category;
  }
  return '';
}

var TRANSFER_HINT_ = /TRANSFER|XFER|PAYMENT|PYMT|AUTOPAY|AUTO PAY|ACH|ONLINE BANKING|DEPOSIT FROM|WITHDRAWAL TO|FROM (CHK|SAV|CHECKING|SAVINGS)|TO (CHK|SAV|CHECKING|SAVINGS)|THANK YOU|CD CLOS|INTERNAL/;

/**
 * Pairs equal and opposite amounts across two different accounts within
 * MATCH_DAYS. Only rows that are uncategorized or already in the Transfer
 * bucket, with no Transfer ID, are considered, and at least one leg must look
 * like a transfer. Returns [{out, in, id, category}] using row indexes.
 */
function matchTransfers(rows, accountTypes, catBucket, transferCats) {
  var used = {}, pairs = [];
  var nextNum = {};
  rows.forEach(function (r) {
    var m = String(r.tid || '').match(/^T-(\d{4})-(\d+)$/);
    if (m) nextNum[m[1]] = Math.max(nextNum[m[1]] || 0, +m[2]);
  });
  var cand = rows.filter(function (r) {
    if (!r.date || r.tid || !r.amount) return false;
    return !r.category || catBucket[r.category] === 'Transfer';
  });
  var hint = function (r) { return catBucket[r.category] === 'Transfer' || TRANSFER_HINT_.test(String(r.desc).toUpperCase()); };
  var outs = cand.filter(function (r) { return r.amount < 0; }).sort(function (a, b) { return a.date < b.date ? -1 : 1; });
  var ins = cand.filter(function (r) { return r.amount > 0; });
  outs.forEach(function (o) {
    var best = null, bestGap = 1e9;
    ins.forEach(function (i) {
      if (used[i.idx] || i.account === o.account) return;
      if (Math.round(i.amount * 100) !== Math.round(-o.amount * 100)) return;
      if (!hint(o) && !hint(i)) return;
      var gap = Math.abs(dayNum_(i.date) - dayNum_(o.date));
      if (gap <= MATCH_DAYS && gap < bestGap) { best = i; bestGap = gap; }
    });
    if (!best) return;
    used[best.idx] = true;
    var yymm = o.date.slice(2, 4) + o.date.slice(5, 7);
    nextNum[yymm] = (nextNum[yymm] || 0) + 1;
    var n = nextNum[yymm];
    var id = 'T-' + yymm + '-' + (n < 10 ? '00' : n < 100 ? '0' : '') + n;
    var isCard = /card/i.test(accountTypes[o.account] || '') || /card/i.test(accountTypes[best.account] || '');
    var cat = isCard ? transferCats.card : transferCats.own;
    pairs.push({ out: o.idx, in: best.idx, id: id, category: cat });
  });
  return pairs;
}

var PROCESSOR_PREFIX_ = /^(SQ \*|SQ\*|TST\* ?|SP \*?|PP\*|PAYPAL \*|PY \*|DD \*|IC\* |CKO\*|BT\*|AMZN MKTP US\*?)/;

/** Merchant keyword for a description: leading words up to the first token with a digit, max 3 words. */
function merchantKey(desc) {
  var d = String(desc || '').toUpperCase().replace(/\s+/g, ' ').trim();
  var m = d.match(PROCESSOR_PREFIX_);
  if (m) d = d.slice(m[0].length).trim();
  var words = d.split(' '), keep = [];
  for (var i = 0; i < words.length && keep.length < 3; i++) {
    var w = words[i];
    if (/\d/.test(w) || /^[#*]/.test(w)) break;
    if (/^(VA|NC|MD|DC|CA|NY|TX|FL|GA|USA|US|WWW|HTTPS?)$/.test(w) && keep.length) break;
    keep.push(w);
  }
  var k = keep.join(' ').replace(/[*#,.\-]+$/, '').trim();
  if (k.replace(/[^A-Z]/g, '').length < 4) return '';
  if (/^(ACH|POS|DEBIT|CREDIT|PURCHASE|PAYMENT|CHECK|ONLINE|TRANSFER|ZELLE|VENMO|DEPOSIT|WITHDRAWAL|RECURRING)( |$)/.test(k)) return '';
  return k;
}

/**
 * Suggests rules from rows the user categorized by hand. A merchant needs one
 * consistent row, or 80% agreement once it has 3+ rows. Transfers are skipped.
 */
function learnRules(rows, rules, catBucket) {
  var prepared = prepareRules(rules);
  var groups = {};
  rows.forEach(function (r) {
    if (!r.category || !r.desc || catBucket[r.category] === 'Transfer' || catBucket[r.category] === undefined) return;
    if (categorize(r.desc, prepared)) return;
    var k = merchantKey(r.desc);
    if (!k || String(r.desc).toUpperCase().replace(/\s+/g, ' ').indexOf(k) < 0) return;
    (groups[k] = groups[k] || []).push(r.category);
  });
  var out = [];
  Object.keys(groups).sort().forEach(function (k) {
    var cats = groups[k], tally = {};
    cats.forEach(function (c) { tally[c] = (tally[c] || 0) + 1; });
    var top = Object.keys(tally).sort(function (a, b) { return tally[b] - tally[a]; })[0];
    var share = tally[top] / cats.length;
    if ((cats.length < 3 && share === 1) || (cats.length >= 3 && share >= 0.8)) {
      out.push({ keyword: k, category: top, rows: cats.length });
    }
  });
  return out;
}

// ================================================================ SHEET I/O

function ss_() { return SpreadsheetApp.getActiveSpreadsheet(); }
function tz_() { return ss_().getSpreadsheetTimeZone(); }

function sheet_(name) {
  var sh = ss_().getSheetByName(name);
  if (!sh) throw new Error('Missing tab "' + name + '". This menu needs the tracker template.');
  return sh;
}

function ymdOf_(v) {
  if (v instanceof Date) return Utilities.formatDate(v, tz_(), 'yyyy-MM-dd');
  return parseDateStr(v);
}

function toDate_(ymd) { return Utilities.parseDate(ymd + ' 12:00', tz_(), 'yyyy-MM-dd HH:mm'); }

function readAccounts_() {
  var sh = sheet_(ACC_SHEET);
  var n = Math.max(sh.getLastRow() - 1, 1);
  var v = sh.getRange(2, 1, n, 10).getValues();
  return v.filter(function (r) { return String(r[0]).trim(); }).map(function (r) {
    return { label: String(r[0]).trim(), type: String(r[2]), property: String(r[3]),
             last4: String(r[4]).replace(/\D/g, '').slice(-4), fileKey: String(r[8] || ''),
             sign: String(r[9] || 'auto') };
  });
}

function readRules_() {
  var sh = sheet_(RULE_SHEET);
  if (sh.getLastRow() < 2) return [];
  return sh.getRange(2, 1, sh.getLastRow() - 1, 2).getValues().map(function (r) {
    return { keyword: String(r[0]).trim(), category: String(r[1]).trim() };
  }).filter(function (r) { return r.keyword && r.category; });
}

function readCatBuckets_() {
  var v = sheet_(CAT_SHEET).getRange('A2:B300').getValues(), m = {};
  v.forEach(function (r) { if (r[1]) m[String(r[1]).trim()] = String(r[0]).trim(); });
  return m;
}

/** Reads Transactions A:H. Returns {rows, lastRow}. Rows carry idx = sheet row. */
function readTx_() {
  var sh = sheet_(TX_SHEET);
  var max = sh.getMaxRows() - 1;
  var v = sh.getRange(2, 1, max, 8).getValues();
  var rows = [], last = 1;
  for (var i = 0; i < v.length; i++) {
    var r = v[i];
    if (r[0] === '' && r[1] === '' && r[3] === '') continue;
    last = i + 2;
    rows.push({ idx: i + 2, date: ymdOf_(r[0]), account: String(r[1]), desc: String(r[2]),
                amount: typeof r[3] === 'number' ? r[3] : parseMoney(r[3]) || 0,
                category: String(r[4]).trim(), tid: String(r[5]).trim() });
  }
  return { rows: rows, lastRow: last };
}

function transferCats_(catBucket) {
  var t = Object.keys(catBucket).filter(function (c) { return catBucket[c] === 'Transfer'; });
  var pick = function (re) { return t.filter(function (c) { return re.test(c); })[0] || t[0] || ''; };
  return { card: pick(/card/i), own: pick(/own account|between/i) };
}

/** Category tweak: a home-mortgage rule hit on a rental account books as the rental mortgage. */
function adjustForProperty_(cat, account, catBucket) {
  if (!account || !account.property || account.property === 'Household') return cat;
  if (/^Mortgage Payment/i.test(cat)) {
    var r = Object.keys(catBucket).filter(function (c) { return /^Rental - Mortgage/i.test(c); })[0];
    return r || cat;
  }
  return cat;
}

// ---------------------------------------------------------------- dialog API

/** files: [{name, text}] read by the browser. Returns one preview item per statement. */
function previewFiles(files) {
  var accounts = readAccounts_();
  var logged = importedNames_();
  var items = [];
  files.forEach(function (f, fi) {
    if (f.text.length > MAX_FILE_BYTES) {
      items.push({ key: fi + ':0', name: f.name, error: 'File is larger than 5 MB.' });
      return;
    }
    var sts;
    try { sts = parseStatementFile(f.name, f.text); }
    catch (e) { items.push({ key: fi + ':0', name: f.name, error: 'Could not read: ' + e.message }); return; }
    sts.forEach(function (st, si) {
      var dates = st.txns.map(function (t) { return t.date; }).sort();
      items.push({
        key: fi + ':' + si,
        name: f.name + (sts.length > 1 ? ' (account ' + (si + 1) + ')' : ''),
        format: st.format, last4: st.last4, count: st.txns.length,
        from: dates[0] || '', to: dates[dates.length - 1] || '',
        guess: guessAccount(f.name, st, accounts),
        warnings: st.warnings.concat(logged[f.name] ? ['This file name was imported before; duplicates will be skipped.'] : []),
        error: st.txns.length ? '' : (st.warnings[0] || 'No transactions found.')
      });
    });
  });
  return { items: items, accounts: accounts.map(function (a) { return a.label; }) };
}

/** choices: {key: accountLabel}. Writes rows, applies rules, matches transfers. */
function importFiles(files, choices) {
  var lock = LockService.getDocumentLock();
  lock.waitLock(20000);
  try {
    var accounts = readAccounts_(), accByLabel = {};
    accounts.forEach(function (a) { accByLabel[a.label] = a; });
    var catBucket = readCatBuckets_();
    var rules = prepareRules(readRules_());
    var tx = readTx_();
    var incoming = [], perFile = [];
    files.forEach(function (f, fi) {
      var sts = parseStatementFile(f.name, f.text);
      sts.forEach(function (st, si) {
        var label = choices[fi + ':' + si];
        if (!label || !accByLabel[label]) return;
        var norm = normalizeSign(st, accByLabel[label]);
        var rows = norm.txns.map(function (t) { return { date: t.date, account: label, amount: t.amount, desc: t.desc }; });
        perFile.push({ name: f.name, account: label, rows: rows, flipped: norm.flipped });
        incoming = incoming.concat(rows);
      });
    });
    var d = dedupe(incoming, tx.rows);
    var keep = d.rows.sort(function (a, b) { return a.date < b.date ? -1 : a.date > b.date ? 1 : 0; });
    var auto = 0;
    var values = keep.map(function (r) {
      var cat = adjustForProperty_(categorize(r.desc, rules), accByLabel[r.account], catBucket);
      if (cat) auto++;
      return [toDate_(r.date), r.account, r.desc, r.amount, cat, '', '', ''];
    });
    var sh = sheet_(TX_SHEET);
    if (values.length) {
      var start = tx.lastRow + 1, end = start + values.length - 1;
      ensureFormulaRows_(sh, end);
      sh.getRange(start, 1, values.length, 8).setValues(values);
    }
    SpreadsheetApp.flush();
    var matched = runTransferMatch_();
    logImports_(perFile, d.dupes);
    var review = readTx_().rows.filter(function (r) { return !r.category; }).length;
    return { added: values.length, dupes: d.dupes, auto: auto, transfers: matched, uncategorized: review,
             flipped: perFile.filter(function (p) { return p.flipped; }).map(function (p) { return p.name; }) };
  } finally { lock.releaseLock(); }
}

/** Copies the I:M formulas further down when imports run past the prefilled rows. */
function ensureFormulaRows_(sh, lastNeeded) {
  var max = sh.getMaxRows();
  if (lastNeeded > max) sh.insertRowsAfter(max, lastNeeded - max + 200);
  var col = sh.getRange(2, 9, sh.getMaxRows() - 1, 1).getFormulas();
  var lastFormula = 1;
  for (var i = col.length - 1; i >= 0; i--) { if (col[i][0]) { lastFormula = i + 2; break; } }
  if (lastFormula < lastNeeded && lastFormula >= 2) {
    sh.getRange(lastFormula, 9, 1, 5).copyTo(sh.getRange(lastFormula + 1, 9, lastNeeded + 200 - lastFormula, 5));
  }
}

function runTransferMatch_() {
  var accounts = readAccounts_(), types = {};
  accounts.forEach(function (a) { types[a.label] = a.type; });
  var catBucket = readCatBuckets_();
  var tx = readTx_();
  var pairs = matchTransfers(tx.rows, types, catBucket, transferCats_(catBucket));
  if (!pairs.length) return 0;
  var sh = sheet_(TX_SHEET);
  var rng = sh.getRange(2, 5, tx.lastRow - 1, 2);
  var ef = rng.getValues();
  var byIdx = {};
  tx.rows.forEach(function (r) { byIdx[r.idx] = r; });
  pairs.forEach(function (p) {
    [p.out, p.in].forEach(function (idx) {
      var row = ef[idx - 2];
      row[1] = p.id;
      if (!row[0] && p.category) row[0] = p.category;
    });
  });
  rng.setValues(ef);
  return pairs.length;
}

function importedNames_() {
  var sh = ss_().getSheetByName(LOG_SHEET), m = {};
  if (!sh || sh.getLastRow() < 2) return m;
  sh.getRange(2, 2, sh.getLastRow() - 1, 1).getValues().forEach(function (r) { m[r[0]] = true; });
  return m;
}

function logImports_(perFile, dupes) {
  var sh = ss_().getSheetByName(LOG_SHEET);
  if (!sh) {
    sh = ss_().insertSheet(LOG_SHEET);
    sh.getRange(1, 1, 1, 6).setValues([['Imported at', 'File name', 'Account', 'Rows in file', 'Date range', 'Sign flipped']])
      .setFontWeight('bold');
  }
  var now = new Date();
  var rows = perFile.map(function (p) {
    var ds = p.rows.map(function (r) { return r.date; }).sort();
    return [now, p.name, p.account, p.rows.length, (ds[0] || '') + ' to ' + (ds[ds.length - 1] || ''), p.flipped ? 'yes' : ''];
  });
  if (rows.length) sh.getRange(sh.getLastRow() + 1, 1, rows.length, 6).setValues(rows);
}

// ---------------------------------------------------------------- menu actions

function menuApplyRules() {
  requirePermissions_();
  var accounts = readAccounts_(), accByLabel = {};
  accounts.forEach(function (a) { accByLabel[a.label] = a; });
  var catBucket = readCatBuckets_();
  var rules = prepareRules(readRules_());
  var tx = readTx_();
  if (tx.lastRow < 2) return toast_('No transactions yet.');
  var rng = sheet_(TX_SHEET).getRange(2, 5, tx.lastRow - 1, 1);
  var col = rng.getValues(), n = 0;
  tx.rows.forEach(function (r) {
    if (r.category) return;
    var c = adjustForProperty_(categorize(r.desc, rules), accByLabel[r.account], catBucket);
    if (c) { col[r.idx - 2][0] = c; n++; }
  });
  rng.setValues(col);
  var m = runTransferMatch_();
  toast_(n + ' rows categorized, ' + m + ' transfer pairs matched.');
}

function menuMatchTransfers() {
  requirePermissions_();
  toast_(runTransferMatch_() + ' transfer pairs matched.');
}

function menuLearnRules() {
  requirePermissions_();
  var ui = SpreadsheetApp.getUi();
  var sugg = learnRules(readTx_().rows, readRules_(), readCatBuckets_());
  if (!sugg.length) { ui.alert('No new rules to learn. Categorize a few rows by hand first.'); return; }
  var preview = sugg.slice(0, 15).map(function (s) { return s.keyword + '  ->  ' + s.category; }).join('\n');
  var ans = ui.alert('Add ' + sugg.length + ' new rules?',
    preview + (sugg.length > 15 ? '\n...and ' + (sugg.length - 15) + ' more' : '') +
    '\n\nThey are added to the Rules tab in yellow so you can edit or delete any.', ui.ButtonSet.YES_NO);
  if (ans !== ui.Button.YES) return;
  var sh = sheet_(RULE_SHEET);
  var today = Utilities.formatDate(new Date(), tz_(), 'yyyy-MM-dd');
  var rows = sugg.map(function (s) { return [s.keyword, s.category, 'Learned ' + today + ' from ' + s.rows + ' row(s)']; });
  var start = sh.getLastRow() + 1;
  sh.getRange(start, 1, rows.length, 3).setValues(rows).setBackground('#FFF2CC');
  toast_(rows.length + ' rules added. Run "Apply rules to uncategorized rows" to use them now.');
}

function toast_(msg) { ss_().toast(msg, 'Finance Tracker', 6); }

// Node test hook (ignored by Apps Script)
if (typeof module !== 'undefined') {
  module.exports = { parseCsvText: parseCsvText, parseMoney: parseMoney, parseDateStr: parseDateStr,
    parseOfx: parseOfx, parseCsvStatement: parseCsvStatement, parseStatementFile: parseStatementFile,
    normalizeSign: normalizeSign, guessAccount: guessAccount, dedupe: dedupe, prepareRules: prepareRules,
    categorize: categorize, matchTransfers: matchTransfers, merchantKey: merchantKey, learnRules: learnRules };
}