`lib/spend-reviews/store.js`

183 lines
'use strict';
/**
 * Spend → Reviews — storage + the common `spend_item` record contract.
 *
 * THE CONTRACT every ingestion adapter normalizes to (makeItem):
 *   {
 *     id,            // stable hash of dedupeKey
 *     source,        // 'amazon' | 'gmail' | 'csv'
 *     merchant,      // best display name of the business/brand
 *     product,       // product/line-item name, or null
 *     order_id,      // vendor order id, or null
 *     asin,          // Amazon ASIN, or null
 *     seller,        // Amazon seller/brand string, or null
 *     date,          // ISO date of the purchase (best-effort)
 *     amount,        // number (spend), 0 if unknown
 *     currency,      // 'USD' default
 *     url,           // source/product url, or null
 *     raw,           // original row/string for audit
 *     norm_merchant, // normalized merchant key (dedupe + cache join)
 *     dedupeKey,
 *     created_at,    // ISO ingestion time (drives the 🕓 card chip)
 *     resolved: { google:{place_id,mapsUrl,status,method}, amazon:{productUrl,seller,status} },
 *     drafts:   { google_review, amazon_review, seller_email },   // {text|subject/body, cost, generatedAt}
 *     targets:  { google_review, amazon_review, seller_email },   // per-target lifecycle
 *   }
 *
 * Per-target lifecycle status: 'none' -> 'draft' -> 'approved' -> 'posted' | 'skipped' | 'error'.
 * Nothing posts/sends without the target reaching 'approved' (a human click) AND a
 * confirm token at execute time — see executors.js.
 */
const fs = require('fs');
const crypto = require('crypto');
const cfg = require('./config');

function ensureDir(p) { try { fs.mkdirSync(p, { recursive: true }); } catch (e) {} }
ensureDir(cfg.DATA_DIR);
ensureDir(cfg.CSV_DROP_DIR);

function readJsonl(file) {
  try {
    return fs.readFileSync(file, 'utf8').split('\n').filter(Boolean).map((l) => {
      try { return JSON.parse(l); } catch (e) { return null; }
    }).filter(Boolean);
  } catch (e) { return []; }
}

/** Normalize a messy merchant/descriptor string into a stable join/dedupe key. */
function normalizeMerchant(s) {
  if (!s) return '';
  let x = String(s).toLowerCase();
  // strip common processor/POS prefixes and noise
  x = x.replace(/\b(sq|tst|pos|paypal|pp|ppd|amzn mktp us|amzn|amazon mktpl|amazon\.com|ach|dbt|crd|pmt|purchase|debit|pos debit|visa|mastercard)\b\*?/g, ' ');
  x = x.replace(/[*#]+/g, ' ');
  x = x.replace(/\bstore\s*#?\d+\b/g, ' ');            // store numbers
  x = x.replace(/\b\d{2}\/\d{2}(\/\d{2,4})?\b/g, ' ');  // dates
  x = x.replace(/\b\d{3}[-. ]?\d{3}[-. ]?\d{4}\b/g, ' '); // phone (BEFORE card-tail so 6464 isn't eaten)
  x = x.replace(/\bx?\d{4,}\b/g, ' ');                   // card tails / long digit runs
  x = x.replace(/\.(com|net|org|co)\b/g, ' ');           // tld
  x = x.replace(/\bwww\b/g, ' ');
  x = x.replace(/\b[a-z]{2}\b\s*$/g, ' ');               // trailing state
  x = x.replace(/[^a-z0-9&' ]+/g, ' ');
  x = x.replace(/\s+/g, ' ').trim();
  return x;
}

/** Human-friendly display name from the normalized key (title-case). */
function displayMerchant(merchant, norm) {
  const base = (norm && norm.length >= 3) ? norm : String(merchant || '').toLowerCase();
  const t = base.replace(/\b[a-z]/g, (c) => c.toUpperCase()).trim();
  return t || String(merchant || '').trim();
}

function makeItem(input) {
  const source = input.source;
  const merchant = (input.merchant || '').toString().trim();
  const norm = normalizeMerchant(merchant);
  const date = input.date || null;
  const amount = Number(input.amount) || 0;
  // dedupe: prefer order_id; else normalized merchant + date + amount
  const dedupeKey = input.order_id
    ? `${source}:${input.order_id}${input.asin ? ':' + input.asin : ''}`
    : `${source}:${norm}|${date || ''}|${amount}`;
  const id = crypto.createHash('sha1').update(dedupeKey).digest('hex').slice(0, 12);
  return {
    id,
    source,
    merchant,
    display_merchant: displayMerchant(merchant, norm),
    product: input.product || null,
    order_id: input.order_id || null,
    asin: input.asin || null,
    seller: input.seller || null,
    date,
    amount,
    currency: input.currency || 'USD',
    url: input.url || null,
    raw: input.raw != null ? input.raw : null,
    norm_merchant: norm,
    dedupeKey,
    created_at: new Date().toISOString(),
    resolved: { google: null, amazon: null },
    drafts: { google_review: null, amazon_review: null, seller_email: null },
    targets: { google_review: 'none', amazon_review: 'none', seller_email: 'none' },
    // per-target approval binding: {contentHash, approvedAt} snapshot of EXACTLY
    // what a human approved, so a later content drift can't be silently executed.
    approvals: { google_review: null, amazon_review: null, seller_email: null },
    seller_email_to: null,
  };
}

/** Hash the EXACT executable payload for a target — the anti-drift approval seal. */
function targetContentHash(item, target) {
  const d = (item.drafts && item.drafts[target]) || {};
  let payload;
  if (target === 'seller_email') {
    payload = [item.seller_email_to || '', d.subject || '', d.body || ''].join('');
  } else {
    const g = (item.resolved && item.resolved.google) || {};
    const a = (item.resolved && item.resolved.amazon) || {};
    const tgtUrl = target === 'google_review' ? (g.writeUrl || g.mapsUrl || '') : (a.reviewUrl || a.productUrl || '');
    payload = [d.title || '', d.text || '', String(d.rating || ''), tgtUrl].join('');
  }
  return crypto.createHash('sha1').update(target + '' + payload).digest('hex');
}

function readItems() { return readJsonl(cfg.ITEMS_FILE); }
function getItem(id) { return readItems().find((i) => i.id === id) || null; }

function writeItems(items) {
  fs.writeFileSync(cfg.ITEMS_FILE, items.map((i) => JSON.stringify(i)).join('\n') + (items.length ? '\n' : ''));
}

/** Insert only genuinely-new items (by dedupeKey). Returns {added, skipped, total}. */
function upsertItems(newItems) {
  const existing = readItems();
  const seen = new Set(existing.map((i) => i.dedupeKey));
  let added = 0;
  for (const it of newItems) {
    if (!it || !it.dedupeKey) continue;
    if (seen.has(it.dedupeKey)) continue;
    existing.push(it); seen.add(it.dedupeKey); added++;
  }
  writeItems(existing);
  return { added, skipped: newItems.length - added, total: existing.length };
}

/** Patch one item in place (shallow-merge top-level; caller passes nested objects whole). */
function updateItem(id, patch) {
  const items = readItems();
  const idx = items.findIndex((i) => i.id === id);
  if (idx < 0) return null;
  items[idx] = Object.assign({}, items[idx], patch);
  writeItems(items);
  return items[idx];
}

// ---- merchant → place_id cache (DTD Option B) --------------------------------
function getPlace(norm) {
  if (!norm) return null;
  const rows = readJsonl(cfg.PLACE_CACHE);
  // last write wins
  let hit = null;
  for (const r of rows) if (r && r.norm === norm) hit = r;
  return hit;
}
function setPlace(norm, place_id, extra) {
  const row = Object.assign({ norm, place_id: place_id || null, at: new Date().toISOString() }, extra || {});
  fs.appendFileSync(cfg.PLACE_CACHE, JSON.stringify(row) + '\n');
  return row;
}

// ---- audit log of every approve / execute action ----------------------------
function logAction(row) {
  const r = Object.assign({ at: new Date().toISOString() }, row);
  try { fs.appendFileSync(cfg.ACTIONS_LOG, JSON.stringify(r) + '\n'); } catch (e) {}
  return r;
}

module.exports = {
  normalizeMerchant, displayMerchant, makeItem, targetContentHash, readItems, getItem, upsertItems, updateItem,
  getPlace, setPlace, logAction, readJsonl,
};