Generate Credit Card Transaction Alerts

CREATE INDEX `adv_type_rg` ON `bulk`.`data`.`register`(`type`);
CREATE INDEX `adv_type_rv` ON `bulk`.`data`.`review`(`type`);
CREATE PRIMARY INDEX `adv_prim_rv` ON `bulk`.`data`.`review`;

function OnUpdate(doc, meta) {
  if (doc.type != "transaction") return;
  try {
    var verbose = 0; // logging - 0: minimal, 1: moderate, 2: massive
    if (verbose > 0) log(meta.id + ' Process transaction for doc.card: ' +
      doc.card + ', doc.amount: ' + nformat(doc.amount, 0, 2));

    // Loads the associated card info of this transaction
    var card = register['card:' + doc.card];
    if (!card) {
      log(meta.id + ' warn card does not exist: ' + doc.card);
      return;
    }

    // Loads the exchange rate table for the day of the transaction
    var erid = 'exchange_rates:er-' + (doc.date).substr(0, 10);
    var exchange_rates = register[erid];
    if (!exchange_rates) {
      log(meta.id + ' WARNING exchange_rates does not exist: ' + erid);
      return;
    }
    var to_USD = exchange_rates['to_USD'];
    var trxn_2_USD = to_USD[doc.currency];
    var card_2_USD = to_USD[card['currency']];
    if (!trxn_2_USD || !card_2_USD) {
      log(meta.id + ' WARNING exchange_rates for either ' + card['currency'] +
        ' or ' + doc.currency + ' does exist');
      return;
    }

    // Converts transaction charge and credit card limit into USD
    var trxn_amount_USD = doc.amount / trxn_2_USD;
    var card_thresh_USD = card['threshold'] / card_2_USD;

    if (verbose > 1) {
      log(meta.id + ' doc   ', doc);
      log(meta.id + ' card  ', card);
      log(meta.id + ' rates ', exchange_rates)
    }
    if (verbose > 0) {
      log(meta.id + ' 1 doc.amount       ' + nformat(doc.amount, 8, 2) +
        ', card_limit       ' + nformat(card['threshold'], 8, 2));
      log(meta.id + ' 2 trxn_currency    ' + sformat(doc.currency, 8) +
        ', card_currency    ' + sformat(card['currency'], 8));
      log(meta.id + ' 3 trxn_2_USD       ' + nformat(trxn_2_USD, 8, 6) +
        ', card_2_USD       ' + nformat(card_2_USD, 8, 6));
      log(meta.id + ' 4 trxn_amount_USD  ' + nformat(trxn_amount_USD, 8, 2) +
        ', card_thresh_USD  ' + nformat(card_thresh_USD, 8, 2));
    }

    // Checks if transaction is high risk due to being over threshold limit
    if (card_thresh_USD < trxn_amount_USD) {
      var msg = 'High Risk Txn: amount: ' + nformat(doc.amount, 8, 2) + ' ' +
        doc.currency + ' exceeds purchase threshold: ' +
        nformat(card['threshold'], 8, 2) + ' ' + card['currency'];
      log(meta.id + ' *** ' + msg);
      doc["comments"] = msg; // Appends description to the document
      doc["reason_code"] = 'X-CREDIT'; // Appends the code to the document
      delete doc["city"]; // Removes city sub document
      review[meta.id] = doc; // Saves the modified document for review
      return;
    }

    // Checks if transacton is high risk due to being in a foreign currency
    if (doc.currency != card['currency']) {
      var msg = 'High Risk Txn: currency mismatch card: ' +
        card['currency'] + ' != txn: ' + doc.currency;
      log(meta.id + ' *** ' + msg);
      doc["comments"] = msg; // Appends description to the document
      doc["reason_code"] = 'X-MISMATCH'; // Appends the code to the document
      delete doc["city"]; // Removes city sub document
      review[meta.id] = doc; // Saves the modified document for review
      return;
    }
    if (verbose > 0) log(meta.id + ' Charge by ' + card["firstname"] + ' ' +
      card["lastname"] + ' appears normal in the amount of ' +
      nformat(doc.amount, 0, 2) + ' ' + doc.currency);
  } catch (e) {
    // Notifies the user if there is a processing error or exception
    log(meta.id + 'ERROR in OnUpdate:', e);
  }
}

// Right justify string with given width
function sformat(s, width) {
  var str = s;
  while (width > str.length) str = ' ' + str;
  return str;
}

// Right justify number with given width with given precision
function nformat(n, width, prec) {
  return sformat(n.toFixed(prec), width, prec);
}

{
  "type": "transaction",
  "txnid": "tx-1505315650-403",
  "amount": 5383.35,
  "product": "Computer, iMac 64GB 4TB Nvme",
  "card": "4273-6623-8686-4599",
  "merchant": "Apple Regent Street",
  "date": "2018-09-14T20:46:10+05:30",
  "currency": "GBP",
  "comments": "High Risk Txn: currency mismatch card: USD != txn: GBP",
  "reason_code": "X-MISMATCH"
}

DELETE FROM `bulk`.`data`.`review`;

function OnUpdate(doc, meta) {
  if (doc.type != "transaction") return;
  try {
    var verbose = 3; // logging - 0: minimal, 1: moderate, 2: massive
    // ...
  }
}