`lib/reviews/model.js`

146 lines
'use strict';
/**
 * Reviews DB model — reads AbramsOS `purchase` rows, writes `review_draft` rows.
 * The content_hash SEAL (set at approval) is what stops stale-approval-replay:
 * the executor refuses to fire if the row's current content no longer matches
 * the sealed hash. Nothing here posts/sends — see executors.js.
 */
const crypto = require('crypto');
const db = require('../db');
const ids = require('../ids');
const drafts = require('./drafts');

// Pull a human product name out of the purchase's parsed-email extract.
function productFromPurchase(p) {
  const raw = p.raw_extract || {};
  let s = raw.item || '';
  if (!s && raw.subject) {
    // 'Ordered: "X..." and 1 more item'  |  'Your Amazon.com order of "X...".'
    const m = String(raw.subject).match(/["“]([^"”]+)["”]/);
    s = m ? m[1] : String(raw.subject).replace(/^(ordered:|your amazon\.com order of)\s*/i, '');
  }
  s = String(s || '').replace(/\s*(?:,?\s*\.{2,})?\s*and \d+ more items?\.?$/i, '').replace(/^["“]|["”]$/g, '').trim();
  return s;
}

function itemShape(p) {
  const isAmazon = /amazon/i.test(p.merchant_name || '');
  return {
    id: p.id, // draft variety is seeded by purchase id — stable across regen
    product: productFromPurchase(p),
    merchant: p.merchant_name || null,
    display_merchant: p.merchant_name || null,
    merchant_domain: p.merchant_domain || null,
    seller: null, // email receipts don't expose the marketplace seller
    order_id: p.order_number || null,
    isAmazon,
  };
}

// The seal: exactly the content a human approved.
function contentHash(row) {
  return crypto.createHash('sha1')
    .update([row.target, row.draft_title || '', row.draft_text || '', row.seller_email_to || ''].join(''))
    .digest('hex');
}

async function listPurchases(userId) {
  const r = await db.query(
    `SELECT id, merchant_name, merchant_domain, order_number, purchase_date, total_amount, currency, raw_extract, created_at
       FROM purchase WHERE user_id = $1 ORDER BY purchase_date DESC LIMIT 500`, [userId]);
  return r.rows;
}

// Generate draft rows for the given purchases (all of the user's if ids omitted).
// Only creates or refreshes rows still in 'draft' — never clobbers approved/posted/skipped.
async function generate(userId, purchaseIds) {
  const purchases = (await listPurchases(userId)).filter((p) => !purchaseIds || purchaseIds.includes(p.id));
  let created = 0, refreshed = 0, skippedLocked = 0;
  for (const p of purchases) {
    const shape = itemShape(p);
    const gen = drafts.generateForItem(shape);
    for (const [target, d] of Object.entries(gen)) {
      const existing = await db.query(
        `SELECT id, status FROM review_draft WHERE purchase_id=$1 AND target=$2`, [p.id, target]);
      if (!existing.rows.length) {
        await db.query(
          `INSERT INTO review_draft (id, user_id, purchase_id, target, product_name, draft_title, draft_text, rating, status)
           VALUES ($1,$2,$3,$4,$5,$6,$7,$8,'draft')`,
          [ids.id('review'), userId, p.id, target, shape.product || null, d.title || null, d.text || null, d.rating || null]);
        created++;
      } else if (existing.rows[0].status === 'draft') {
        await db.query(
          `UPDATE review_draft SET product_name=$2, draft_title=$3, draft_text=$4, rating=$5, updated_at=now()
             WHERE id=$1`,
          [existing.rows[0].id, shape.product || null, d.title || null, d.text || null, d.rating || null]);
        refreshed++;
      } else { skippedLocked++; }
    }
  }
  return { created, refreshed, skippedLocked, purchases: purchases.length };
}

async function listWithDrafts(userId) {
  const purchases = await listPurchases(userId);
  const dr = await db.query(`SELECT * FROM review_draft WHERE user_id=$1`, [userId]);
  const byPurchase = {};
  for (const d of dr.rows) (byPurchase[d.purchase_id] = byPurchase[d.purchase_id] || []).push(d);
  return purchases.map((p) => ({
    id: p.id, merchant_name: p.merchant_name, product: productFromPurchase(p),
    purchase_date: p.purchase_date, total_amount: p.total_amount, currency: p.currency,
    drafts: (byPurchase[p.id] || []).sort((a, b) => a.target.localeCompare(b.target)),
  }));
}

async function getDraft(userId, id) {
  const r = await db.query(`SELECT * FROM review_draft WHERE id=$1 AND user_id=$2`, [id, userId]);
  return r.rows[0] || null;
}

// Batch approve: seal each row's CURRENT content. Reused by the single + batch paths.
async function approveBatch(userId, ids_) {
  let approved = 0;
  for (const id of ids_) {
    const row = await getDraft(userId, id);
    if (!row || row.status === 'posted') continue;
    if (!row.draft_text) continue;
    await db.query(
      `UPDATE review_draft SET status='approved', content_hash=$2, updated_at=now() WHERE id=$1 AND user_id=$3`,
      [id, contentHash(row), userId]);
    approved++;
  }
  return { approved };
}

async function setStatus(userId, id, status) {
  const row = await getDraft(userId, id);
  if (!row) return null;
  // changing away from approved drops the seal
  const hash = status === 'approved' ? contentHash(row) : null;
  await db.query(`UPDATE review_draft SET status=$2, content_hash=$3, updated_at=now() WHERE id=$1 AND user_id=$4`,
    [id, status, hash, userId]);
  return getDraft(userId, id);
}

async function setSellerEmailTo(userId, id, to) {
  const row = await getDraft(userId, id);
  if (!row) return null;
  // changing the destination invalidates a prior approval seal
  const status = row.status === 'approved' ? 'draft' : row.status;
  await db.query(`UPDATE review_draft SET seller_email_to=$2, status=$3, content_hash=NULL, updated_at=now() WHERE id=$1 AND user_id=$4`,
    [id, (to || '').slice(0, 200), status, userId]);
  return getDraft(userId, id);
}

function summarize(items) {
  const s = { purchases: items.length, byTarget: {}, posted: 0 };
  for (const it of items) for (const d of it.drafts) {
    s.byTarget[d.target] = s.byTarget[d.target] || { draft: 0, approved: 0, posted: 0, skipped: 0 };
    if (s.byTarget[d.target][d.status] !== undefined) s.byTarget[d.target][d.status]++;
    if (d.status === 'posted') s.posted++;
  }
  return s;
}

module.exports = { productFromPurchase, itemShape, contentHash, generate, listWithDrafts, getDraft, approveBatch, setStatus, setSellerEmailTo, summarize };