/**
 * Tereka email auto-import (optional)
 * ------------------------------------
 * What it does: every hour, it reads NEW bank debit-alert emails in your Gmail and adds them to the
 * Transactions tab of this Tereka sheet. Your Tereka app picks them up the next time it syncs.
 *
 * Privacy: this script runs only in YOUR Google account. Tereka never sees your email. Nothing is sent
 * anywhere else. It keeps only the amount, the date and a short description. Account numbers and
 * balances are ignored. Each email it reads gets the Gmail label "Tereka/logged", so it's never added twice.
 *
 * How to start: choose "setup" in the menu above, then press Run. Allow access when Google asks.
 * How to stop: choose "stop", then press Run. Or delete this script.
 * Try first: choose "preview" and press Run. It shows what it would add (in the log), without adding.
 *
 * Built from Tereka's own alert reader. Generated file: edit scripts/build-autoimport.mjs, not this.
 */

// Gmail search for alert emails. Change it if your bank's alerts look different.
var QUERY = 'newer_than:3d -label:"Tereka/logged" (debit OR "DR" OR "debit alert" OR "transaction alert")';
var LABEL = 'Tereka/logged';
var CUP_NAMES = {"ops":"Living costs","give":"Giving","safety":"Emergency","invest":"Investment","learn":"Learning","save":"Savings","project":"Project","fun":"Leisure"};

function setup() {
  stop();
  ScriptApp.newTrigger('importAlerts').timeBased().everyHours(1).create();
  importAlerts();
  Logger.log('Done. New debit alerts will be added every hour.');
}

function stop() {
  ScriptApp.getProjectTriggers().forEach(function (t) {
    if (t.getHandlerFunction() === 'importAlerts') ScriptApp.deleteTrigger(t);
  });
}

function preview() { run_(false); }
function importAlerts() { run_(true); }

function run_(write) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Transactions');
  if (!sheet) throw new Error('Open this script from your Tereka sheet (Extensions → Apps Script).');
  var tz = ss.getSpreadsheetTimeZone();
  var label = GmailApp.getUserLabelByName(LABEL) || GmailApp.createLabel(LABEL);
  var last = sheet.getLastRow();
  var seen = {};
  if (last > 1) sheet.getRange(2, 1, last - 1, 1).getValues().forEach(function (r) { seen[String(r[0])] = true; });
  var rows = [];
  var threads = GmailApp.search(QUERY, 0, 50);
  threads.forEach(function (thread) {
    thread.getMessages().forEach(function (msg) {
      var id = 'em' + msg.getId();
      if (seen[id]) return;
      var day = Utilities.formatDate(msg.getDate(), tz, 'yyyy-MM-dd');
      var d = parseEmailAlert(msg.getPlainBody(), day);
      if (!d) return;
      rows.push([id, d.date, CUP_NAMES[d.cup] || CUP_NAMES.ops, d.amount, (d.note || 'Bank alert') + ' (email)', new Date().toISOString()]);
      seen[id] = true;
    });
    if (write) thread.addLabel(label);
  });
  if (!rows.length) { Logger.log('No new debit alerts.'); return; }
  if (write) sheet.getRange(sheet.getLastRow() + 1, 1, rows.length, 6).setValues(rows);
  Logger.log((write ? 'Added ' : 'Would add ') + rows.length + ':\n' + rows.map(function (r) { return r[1] + '  ' + r[2] + '  NGN' + r[3] + '  ' + r[4]; }).join('\n'));
}

// ---- Tereka's alert reader (same as the app) ----
// Read Nigerian bank debit alerts (SMS, email or app notifications) pasted by the user.
// Runs entirely on the device. Keeps only amount, date and a short description —
// account numbers and balances are ignored.
// Self-contained on purpose: scripts/build-autoimport.mjs copies this file into the email auto-import script.

const MONEY = /(?:\b(?:NGN|ngn|N)|₦)\s?(\d[\d,]*(?:\.\d{1,2})?)/;
// "Amt: 5,000.00", "Amount (NGN): 4,500", "DR Amt:5,000.00", "Debit: NGN5,000" — the number needs no currency sign here.
const AMT_LINE = /\b(?:amt|amount|debit|credit)\b\s*(?:\(\s*(?:ngn|₦|n)\s*\))?\s*[:=\-]\s*(?:[+\-]\s?)?(?:ngn|₦|n)?\s?(\d[\d,]*(?:\.\d{1,2})?)/i;
const FEE_LINE = /^\s*(?:fee|fees|charge|charges|vat|stamp\s*duty|commission|sms\s*charge)\b/i;
const MAX = 1e9;
const 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 };

const CUP_HINTS = [
  ['give', /tithe|offering|church|mosque|zakat|charity|donat|gift|seed|mum|mom|dad|mama|papa|sister|brother/i],
  ['learn', /udemy|coursera|course|school|tuition|book|training|exam|jamb|waec|certif|bootcamp/i],
  ['fun', /netflix|showmax|spotify|apple\.com|cinema|filmhouse|genesis|bar\b|lounge|club|restaurant|kfc|dominos|pizza|chicken republic|cafe|game|bet9ja|sportybet|party|hangout/i],
  ['invest', /cowrywise|piggyvest|risevest|bamboo|chaka|trove|stanbic.*fund|mutual fund|treasury|t-?bill|stock|invest/i],
  ['save', /save|savings|target|vault|kolo/i],
  ['ops', /shoprite|spar|market|grocer|food|bolt|uber|indrive|fuel|petrol|filling|total|mobil|nnpc|airtime|data|mtn|glo|airtel|9mobile|dstv|gotv|startimes|electric|ikedc|ekedc|nepa|phcn|water|rent|transport|pos|pharm|hospital|clinic/i],
];

function guessCup(text) {
  for (const [cup, re] of CUP_HINTS) if (re.test(text)) return cup;
  return 'ops';
}

function parseDate(text, today) {
  // 01-10-2026, 01/10/26, 2026-10-01, 01-Oct-2026, 1 Oct 2026, 5th Oct 2026, Oct 5, 2026
  let m = text.match(/\b(20\d{2})-(\d{1,2})-(\d{1,2})\b/);
  if (m) return iso(+m[1], +m[2], +m[3]);
  m = text.match(/\b(\d{1,2})(?:st|nd|rd|th)?[\s\-/]([A-Za-z]{3})[a-z]*\.?[\s\-/,]+(\d{2,4})\b/);
  if (m && MONTHS[m[2].toLowerCase()]) return iso(year(+m[3]), MONTHS[m[2].toLowerCase()], +m[1]);
  m = text.match(/\b([A-Za-z]{3})[a-z]*\.?\s+(\d{1,2})(?:st|nd|rd|th)?,?\s+(\d{4})\b/);
  if (m && MONTHS[m[1].toLowerCase()]) return iso(+m[3], MONTHS[m[1].toLowerCase()], +m[2]);
  m = text.match(/\b(\d{1,2})[-/](\d{1,2})[-/](\d{2,4})\b/);
  if (m) return iso(year(+m[3]), +m[2], +m[1]); // Nigerian banks write day first
  return today;
}
const year = (y) => (y < 100 ? 2000 + y : y);
function iso(y, m, d) {
  if (m < 1 || m > 12 || d < 1 || d > 31) return null;
  return `${y}-${String(m).padStart(2, '0')}-${String(d).padStart(2, '0')}`;
}

// Balances (and a CR/DR right after them: "Bal: NGN120,000.00CR") are never the amount or the direction.
const stripBal = (l) => l.replace(/(avail(able)?\s*)?(ledger\s*|book\s*|clr\s*|cleared\s*|current\s*)?bal(ance)?\s*[:\-]?\s*(NGN|N|₦)?\s?[\d,.]+(\s*(CR|DR)\b)?/gi, '');

/** The alert's amount: an Amt/Amount line first, then a line that names the debit, then any naira amount. */
function amountOf(block) {
  const lines = block.split('\n').map(stripBal).filter((l) => !FEE_LINE.test(l));
  const num = (s) => parseFloat(s.replace(/,/g, ''));
  for (const l of lines) {
    const m = l.match(AMT_LINE);
    if (m && num(m[1]) > 0) return num(m[1]);
  }
  const pick = lines.find((l) => /\b(debit(ed)?|dr|sent|paid|purchase|withdraw|transfer)/i.test(l) && MONEY.test(l))
    || lines.find((l) => MONEY.test(l));
  const m = pick && pick.match(MONEY);
  return m ? num(m[1]) : 0;
}

function describe(block) {
  let m = block.match(/\b(?:desc(?:ription)?|des|narration|narrative|remarks?|details?|memo)\s*[:\-]\s*([^\n]+)/i);
  if (m && tidy(m[1])) return tidy(m[1]);
  m = block.match(/\b(?:[Tt]o|TO|[Aa]t|AT|[Ff]or|FOR|[Ff]rom|FROM)\s+((?:[Tt]ransfer\s+to\s+)?[A-Z][\w&'.\- ]{2,40})/);
  if (m) return tidy(m[1].replace(/^transfer\s+to\s+/i, ''));
  return '';
}
const tidy = (s) => s.replace(/\b(date|time|bal|balance|avail|available|amt|acct?|ref|session)\b.*$/i, '').replace(/\s+on\s+\d.*$/i, '')
  .replace(/[*#]{2,}\w*/g, '').replace(/\.(\s.*)?$/, '').replace(/\s+/g, ' ').trim().slice(0, 60);

/**
 * 'debit', 'credit', or null when the block doesn't say. Reads the WHOLE alert, strongest marker first:
 *   1. a DR/CR or sign right next to the amount or account ("NGN5,000.00 DR", "DR Amt:", "Acct: 0123**45 DR", "-NGN500")
 *   2. a stated type ("Txn: Credit", "Transaction Type: Debit", "Debit Alert")
 *   3. the words debit/debited or credit/credited anywhere
 * A "DR" before a name in the narration ("TRF FROM DR ADEYEMI") is not a marker.
 */
function direction(block) {
  const b = block.split('\n').map(stripBal).join('\n').replace(/credit\s*card|debit\s*card/gi, ' ');
  // 1. next to an amount or account
  const dr1 = DR_NEAR.test(b);
  const cr1 = CR_NEAR.test(b);
  if (dr1 !== cr1) return dr1 ? 'debit' : 'credit';
  // 2. a stated type
  const dr2 = /\b(?:txn|trans(?:action)?)\s*(?:type)?\s*[:\-]?\s*(?:debit|dr)\b|\bdebit\s+(?:alert|transaction)\b/i.test(b);
  const cr2 = /\b(?:txn|trans(?:action)?)\s*(?:type)?\s*[:\-]?\s*(?:credit|cr)\b|\bcredit\s+(?:alert|transaction)\b/i.test(b);
  if (dr2 !== cr2) return dr2 ? 'debit' : 'credit';
  // 3. the words
  const dr3 = /\b(?:debit|debited)\b/i.test(b);
  const cr3 = /\bCR\b/.test(b) || /\b(?:credit|credited)\b/i.test(b);
  if (dr3 !== cr3) return dr3 ? 'debit' : 'credit';
  return null;
}
function near(m, sign) {
  return new RegExp(
    `(?:\\bNGN|\\bN|₦)\\s?\\d[\\d,]*(?:\\.\\d+)?\\s*${m}\\b` + // NGN5,000.00 DR
    `|\\b(?:amt|amount)\\b\\s*[:\\-]?\\s*(?:NGN|N|₦)?\\s?\\d[\\d,]*(?:\\.\\d+)?\\s*${m}\\b` + // Amt: 5,000.00 DR
    `|\\b${m}\\b\\s*(?:amt|amount)\\b|\\b${m}\\b\\s*[:\\-]?\\s*(?:NGN|N|₦)\\s?\\d` + // DR Amt:, DR NGN5,000
    `|\\b(?:acct|acc|account|a\\/c)\\b[^\\n\\d*]{0,12}[\\d*xX\\-]{3,}\\s*${m}\\b` + // Acct: 012****345 DR
    `|${sign}\\s?(?:NGN|₦)\\s?\\d|(?:NGN|₦)\\s?${sign}\\s?\\d`, 'i'); // -NGN500, NGN-500
}
const DR_NEAR = near('DR', '-');
const CR_NEAR = near('CR', '\\+');

function isDebit(block) {
  const d = direction(block);
  if (d) return d === 'debit';
  if (/\b(received|deposit(ed)?|inflow|reversal|refund(ed)?)\b/i.test(block)) return false;
  return true; // "You sent…", "Payment of…", POS receipts
}

// Line keys: a line repeating a key the current alert already has starts the next alert.
const KEYS = [
  ['acct', /\b(?:acct|acc|account|a\/c)\b\s*(?:no|number|num)?\.?\s*[:#]/i],
  ['amt', /\b(?:amt|amount)\b\s*(?:\([^)]*\))?\s*[:\-]/i],
  ['txn', /\b(?:txn|transaction)\s*(?:type)?\s*:|^\W*(?:debit|credit)\W*$/i],
  ['alert', /\b(?:debit|credit|transaction|txn)\s+alert\b/i],
  ['desc', /\b(?:desc(?:ription)?|narration|remarks?|details?)\s*[:\-]/i],
  ['date', /\bdate\s*[:\-]/i],
];
const keysOf = (line) => KEYS.filter(([, re]) => re.test(line)).map(([k]) => k);
const hasAmount = (s) => amountOf(s) > 0;

/** Split pasted text into separate alerts, keeping each alert's header lines (Txn: Credit…) with it. */
function split(text) {
  const lines = String(text || '').replace(/\r/g, '').split('\n');
  const blocks = [];
  let cur = [], keys = new Set(), amt = false, gap = false;
  const flush = () => { if (cur.length) blocks.push(cur.join('\n')); cur = []; keys = new Set(); amt = false; };
  for (const raw of lines) {
    const line = raw.trim();
    if (!line) { gap = true; continue; }
    const k = keysOf(line);
    const lineAmt = !FEE_LINE.test(line) && hasAmount(line);
    const repeat = k.some((x) => keys.has(x));
    // A new alert starts once this one has its amount and the line is: a header ("Txn: Debit", "Credit Alert"),
    // after a blank line, or a second amount line that isn't a narration. A repeated key always starts one.
    const header = k.includes('txn') || k.includes('alert');
    if (cur.length && (repeat || (amt && (gap || header || (lineAmt && !k.includes('desc')))))) flush();
    gap = false;
    cur.push(line);
    k.forEach((x) => keys.add(x));
    if (lineAmt) amt = true;
  }
  flush();
  return blocks;
}

const dupKey = (d) => `${d.amount}|${d.date}|${(d.note || '').toLowerCase()}`;

/**
 * @returns {{ debits: Array<{amount,date,note,cup,duplicate?}>, skipped: number, credits: number, duplicates: number }}
 *   skipped = credits plus alerts whose amount line couldn't be read (or was over ₦1bn). Header-only or
 *   footer-only pieces with no amount line are dropped quietly. credits = how many of the skipped were credits.
 *   A debit with the same amount, date and description as an earlier one in the same paste gets
 *   duplicate: true (it is still listed, so the person decides); duplicates counts them.
 */
function parseAlerts(text, today) {
  const debits = [];
  let skipped = 0, credits = 0, duplicates = 0;
  const seen = new Set();
  for (const block of split(text)) {
    const amount = Math.round(amountOf(block));
    if (!amount) { if (/\b(?:amt|amount)\b/i.test(block)) skipped++; continue; }
    if (amount > MAX) { skipped++; continue; }
    if (!isDebit(block)) { skipped++; credits++; continue; }
    const note = describe(block);
    const d = { amount, date: parseDate(block, today) || today, note, cup: guessCup(note || block) };
    const key = dupKey(d);
    if (seen.has(key)) { d.duplicate = true; duplicates++; }
    seen.add(key);
    debits.push(d);
  }
  return { debits, skipped, credits, duplicates };
}

/**
 * One bank alert email (its plain text) → one debit, or null. The whole email is one alert, so footers and
 * line breaks don't split it. Credits and emails with no clear amount return null.
 * Used by the optional email auto-import script that runs in the person's own Google account.
 */
function parseEmailAlert(text, today) {
  const block = String(text || '').replace(/\r/g, '').split('\n').map((l) => l.trim()).filter(Boolean).slice(0, 60).join('\n');
  if (!block) return null;
  if (direction(block) !== 'debit') return null; // only clear debits; never guess on promos or credits
  const amount = Math.round(amountOf(block));
  if (!amount || amount > MAX) return null;
  const note = describe(block);
  return { amount, date: parseDate(block, today) || today, note, cup: guessCup(note || block) };
}