1  #!/usr/bin/env node
  2  
  3  /**
  4   * PayPal Webhook Handler
  5   * Processes PayPal payment notifications and triggers report generation
  6   * Can be run as Express server or deployed to Cloudflare Workers
  7   */
  8  
  9  import express from 'express';
 10  import Logger from '../utils/logger.js';
 11  import { run, getOne } from '../utils/db.js';
 12  import { verifyPayment } from './paypal.js';
 13  import { getPrice } from '../utils/country-pricing.js';
 14  import { generateAuditReportForPurchase } from '../reports/report-orchestrator.js';
 15  import { deliverReport } from '../reports/report-delivery.js';
 16  import '../utils/load-env.js';
 17  
 18  const logger = new Logger('PayPalWebhook');
 19  
 20  /**
 21   * Atomic idempotency check using INSERT ... ON CONFLICT DO NOTHING.
 22   * Returns true if this is a NEW webhook (just inserted).
 23   * Returns false if the order_id was already processed (row existed).
 24   *
 25   * Table m333.processed_webhooks is defined in db/pg-schema.sql.
 26   *
 27   * @param {string} orderId
 28   * @param {number} amount
 29   * @param {string} currency
 30   * @returns {Promise<boolean>} true if newly inserted (first time), false if duplicate
 31   */
 32  async function claimWebhook(orderId, amount, currency) {
 33    const result = await run(
 34      `INSERT INTO processed_webhooks (order_id, amount, currency)
 35       VALUES ($1, $2, $3)
 36       ON CONFLICT (order_id) DO NOTHING`,
 37      [orderId, amount, currency]
 38    );
 39    return result.changes === 1;
 40  }
 41  
 42  /**
 43   * Amount tolerance for float comparison (0.01 = 1 cent).
 44   * PayPal may round differently, so we allow a small margin.
 45   */
 46  const AMOUNT_TOLERANCE = 0.02;
 47  
 48  /**
 49   * Verify that the payment amount and currency match expected pricing
 50   * for the given country. Protects against amount manipulation attacks
 51   * where an attacker creates an order with a lower price.
 52   *
 53   * @param {number} paidAmount - Amount actually paid (from PayPal verification)
 54   * @param {string} paidCurrency - Currency code from PayPal (e.g. 'AUD', 'USD')
 55   * @param {string} countryCode - Country code from the site record
 56   * @returns {{ valid: boolean, reason?: string }}
 57   */
 58  function verifyPaymentAmount(paidAmount, paidCurrency, countryCode) {
 59    // Skip verification in test mode only — never in production
 60    if (process.env.SKIP_AMOUNT_VERIFICATION === 'true' && process.env.NODE_ENV === 'test') {
 61      return { valid: true };
 62    }
 63  
 64    if (!countryCode) {
 65      // No country to verify against — allow but log
 66      logger.warn('Amount verification skipped: no country_code on site');
 67      return { valid: true };
 68    }
 69  
 70    // Check if this was a test-price order (< $5 in any currency)
 71    // Test orders use testPrice override which bypasses normal pricing
 72    if (paidAmount < 5) {
 73      logger.warn(`Low amount detected (${paidCurrency} ${paidAmount}) — likely test order, allowing`);
 74      return { valid: true };
 75    }
 76  
 77    const pricing = getPrice(countryCode);
 78    if (!pricing) {
 79      // Country not in pricing table — allow but log
 80      logger.warn(`Amount verification skipped: no pricing for country ${countryCode}`);
 81      return { valid: true };
 82    }
 83  
 84    // Verify currency matches expected
 85    if (paidCurrency !== pricing.currency) {
 86      return {
 87        valid: false,
 88        reason: `Currency mismatch: paid ${paidCurrency}, expected ${pricing.currency} for ${countryCode}`,
 89      };
 90    }
 91  
 92    // Verify amount matches expected (within tolerance)
 93    const expectedAmount = pricing.priceLocal;
 94    if (Math.abs(paidAmount - expectedAmount) > AMOUNT_TOLERANCE) {
 95      return {
 96        valid: false,
 97        reason: `Amount mismatch: paid ${paidCurrency} ${paidAmount}, expected ${paidCurrency} ${expectedAmount} for ${countryCode}`,
 98      };
 99    }
100  
101    return { valid: true };
102  }
103  
104  /**
105   * Process PayPal payment completion
106   * @param {string} orderId - PayPal order ID
107   * @returns {Promise<Object>} Processing result
108   */
109  export async function processPaymentComplete(orderId) {
110    logger.info(`Processing payment completion for order: ${orderId}`);
111  
112    try {
113      // Verify payment with PayPal API
114      const paymentDetails = await verifyPayment(orderId);
115  
116      if (!paymentDetails.isPaid) {
117        logger.warn(`Payment ${orderId} not completed yet: ${paymentDetails.status}`);
118        return {
119          success: false,
120          message: `Payment status: ${paymentDetails.status}`,
121        };
122      }
123  
124      // Extract site_id and message_id from reference_id
125      // Format: "site_123_conv_456"
126      const match = paymentDetails.referenceId?.match(/site_(\d+)_conv_(\d+)/);
127      if (!match) {
128        throw new Error(`Invalid reference ID format: ${paymentDetails.referenceId}`);
129      }
130  
131      const siteId = parseInt(match[1]);
132      const messageId = parseInt(match[2]);
133  
134      logger.info(
135        `Payment verified: Site ${siteId}, Message ${messageId}, Amount: ${paymentDetails.currency} $${paymentDetails.amount}`
136      );
137  
138      // ── Idempotency gate (atomic) ─────────────────────────────────────────
139      // INSERT ... ON CONFLICT DO NOTHING into processed_webhooks. If the row
140      // already exists, changes === 0 and we know this is a duplicate delivery.
141      const isNew = await claimWebhook(orderId, paymentDetails.amount, paymentDetails.currency);
142      if (!isNew) {
143        logger.info(`Payment ${orderId} already processed (idempotency gate)`);
144        return {
145          success: true,
146          message: 'Payment already processed',
147          orderId,
148        };
149      }
150  
151      // ── Amount & currency verification ────────────────────────────────────
152      // Look up the site's country to verify expected pricing
153      const siteForPricing = await getOne(
154        'SELECT country_code FROM sites WHERE id = $1',
155        [siteId]
156      );
157  
158      const amountCheck = verifyPaymentAmount(
159        paymentDetails.amount,
160        paymentDetails.currency,
161        siteForPricing?.country_code
162      );
163  
164      if (!amountCheck.valid) {
165        logger.error(
166          `PAYMENT AMOUNT REJECTED for order ${orderId}: ${amountCheck.reason}`
167        );
168        // Remove from processed_webhooks so it can be re-evaluated if needed
169        await run('DELETE FROM processed_webhooks WHERE order_id = $1', [orderId]);
170        return {
171          success: false,
172          message: `Amount verification failed: ${amountCheck.reason}`,
173          orderId,
174        };
175      }
176  
177      // Legacy idempotency check (belt-and-suspenders with processed_webhooks)
178      const existing = await getOne(
179        `SELECT id, payment_id FROM messages
180         WHERE id = $1 AND payment_id = $2`,
181        [messageId, orderId]
182      );
183  
184      if (existing?.payment_id) {
185        logger.info(`Payment ${orderId} already processed for message ${messageId}`);
186        return {
187          success: true,
188          message: 'Payment already processed',
189          messageId,
190        };
191      }
192  
193      // Update message with payment details
194      await run(
195        `UPDATE messages
196         SET payment_id = $1,
197             payment_amount = $2
198         WHERE id = $3`,
199        [orderId, paymentDetails.amount, messageId]
200      );
201  
202      // Update site resulted_in_sale
203      const message = await getOne(
204        'SELECT site_id FROM messages WHERE id = $1',
205        [messageId]
206      );
207  
208      if (message?.site_id) {
209        await run(
210          `UPDATE sites
211           SET resulted_in_sale = 1,
212               sale_amount = $1
213           WHERE id = $2`,
214          [paymentDetails.amount, message.site_id]
215        );
216      }
217  
218      logger.success(`Updated message ${messageId} to 'paid', marked site as sale`);
219  
220      // Find or create purchase record for report generation
221      let purchaseId = null;
222      const existingPurchase = await getOne(
223        'SELECT id FROM purchases WHERE paypal_order_id = $1',
224        [orderId]
225      );
226  
227      if (existingPurchase) {
228        purchaseId = existingPurchase.id;
229      } else {
230        // Create purchase record from conversation payment data
231        const site = await getOne(
232          'SELECT landing_page_url, country_code FROM sites WHERE id = $1',
233          [siteId]
234        );
235  
236        if (site) {
237          const result = await run(
238            `INSERT INTO purchases
239             (email, landing_page_url, paypal_order_id, amount, currency, amount_usd,
240              country_code, site_id, message_id, status)
241             VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, 'paid')
242             RETURNING id`,
243            [
244              paymentDetails.payerEmail || '',
245              site.landing_page_url,
246              orderId,
247              Math.round(paymentDetails.amount * 100),
248              paymentDetails.currency || 'USD',
249              Math.round(paymentDetails.amount * 100),
250              site.country_code,
251              siteId,
252              messageId,
253            ]
254          );
255          purchaseId = result.lastInsertRowid;
256          logger.success(`Created purchase #${purchaseId} for report generation`);
257        }
258      }
259  
260      // Trigger fresh assessment + report generation asynchronously
261      // Always re-scrapes, re-screenshots, and re-scores with Opus + extended thinking + vision
262      if (purchaseId) {
263        triggerFreshAssessment(purchaseId, siteId).catch(err => {
264          logger.error(`Fresh assessment failed for purchase #${purchaseId}:`, err);
265        });
266      }
267  
268      return {
269        success: true,
270        message: 'Payment processed successfully',
271        messageId,
272        siteId,
273        orderId,
274        purchaseId,
275        amount: paymentDetails.amount,
276      };
277    } catch (error) {
278      logger.error(`Failed to process payment ${orderId}`, error);
279      throw error;
280    }
281  }
282  
283  /**
284   * Trigger a full fresh assessment for a purchase.
285   * Always re-scrapes assets (including screenshots), re-scores with Opus + extended thinking
286   * using the vision scoring prompt — regardless of ENABLE_VISION setting or score recency.
287   * Customers always get the most accurate report possible.
288   *
289   * @param {number} purchaseId - Purchase ID
290   * @param {number} siteId - Site ID
291   */
292  async function triggerFreshAssessment(purchaseId, siteId) {
293    logger.info(
294      `Triggering fresh assessment for purchase #${purchaseId}, site #${siteId} ` +
295        `(Opus + extended thinking + vision, ignoring ENABLE_VISION setting)`
296    );
297  
298    try {
299      // Generate full audit report (captures screenshots, scores with Opus, generates PDF)
300      const result = await generateAuditReportForPurchase(purchaseId);
301      logger.success(
302        `Report generated for purchase #${purchaseId}: ${result.score} (${result.grade})`
303      );
304  
305      // Deliver report via email
306      await deliverReport(purchaseId);
307      logger.success(`Report delivered for purchase #${purchaseId}`);
308  
309      // Update site conversation status
310      await run(
311        `UPDATE sites
312         SET conversation_status = 'report_delivered'
313         WHERE id = (SELECT site_id FROM messages WHERE id = (SELECT message_id FROM purchases WHERE id = $1))`,
314        [purchaseId]
315      );
316    } catch (error) {
317      logger.error(`Fresh assessment failed for purchase #${purchaseId}:`, error);
318  
319      // Mark purchase for retry by process-purchases cron
320      await run(
321        `UPDATE purchases SET error_message = $1, updated_at = NOW() WHERE id = $2`,
322        [`triggerFreshAssessment failed: ${error.message}`, purchaseId]
323      );
324  
325      throw error;
326    }
327  }
328  
329  /**
330   * Verify PayPal webhook signature using PayPal's verification API.
331   * This is the server-side equivalent of the Cloudflare Worker's verifyPayPalSignature().
332   *
333   * Required env vars:
334   *   PAYPAL_CLIENT_ID, PAYPAL_CLIENT_SECRET, PAYPAL_WEBHOOK_ID
335   *   PAYPAL_MODE ('live' or 'sandbox')
336   *
337   * @param {import('express').Request} req - Express request with headers and rawBody
338   * @returns {Promise<{ verified: boolean, error?: string }>}
339   */
340  export async function verifyWebhookSignature(req) {
341    const webhookId = process.env.PAYPAL_WEBHOOK_ID;
342  
343    if (!webhookId) {
344      logger.error('PAYPAL_WEBHOOK_ID not configured — rejecting webhook');
345      return { verified: false, error: 'Webhook verification not configured' };
346    }
347  
348    const transmissionId = req.headers['paypal-transmission-id'];
349    const transmissionTime = req.headers['paypal-transmission-time'];
350    const transmissionSig = req.headers['paypal-transmission-sig'];
351    const certUrl = req.headers['paypal-cert-url'];
352    const authAlgo = req.headers['paypal-auth-algo'];
353  
354    if (!transmissionId || !transmissionTime || !transmissionSig || !certUrl || !authAlgo) {
355      return { verified: false, error: 'Missing PayPal signature headers' };
356    }
357  
358    // Determine PayPal API base URL
359    const apiBase =
360      process.env.PAYPAL_MODE === 'live'
361        ? 'https://api-m.paypal.com'
362        : 'https://api-m.sandbox.paypal.com';
363  
364    try {
365      const clientId = process.env.PAYPAL_CLIENT_ID;
366      const clientSecret = process.env.PAYPAL_CLIENT_SECRET;
367  
368      if (!clientId || !clientSecret) {
369        return { verified: false, error: 'PayPal credentials not configured' };
370      }
371  
372      // Get OAuth token
373      const auth = Buffer.from(`${clientId}:${clientSecret}`).toString('base64');
374      const tokenRes = await fetch(`${apiBase}/v1/oauth2/token`, {
375        method: 'POST',
376        headers: {
377          'Content-Type': 'application/x-www-form-urlencoded',
378          Authorization: `Basic ${auth}`,
379        },
380        body: 'grant_type=client_credentials',
381      });
382  
383      if (!tokenRes.ok) {
384        return { verified: false, error: 'Failed to obtain PayPal access token' };
385      }
386  
387      const tokenData = await tokenRes.json();
388  
389      // Verify webhook signature via PayPal API
390      const rawBody = req.rawBody || JSON.stringify(req.body);
391      const verifyRes = await fetch(`${apiBase}/v1/notifications/verify-webhook-signature`, {
392        method: 'POST',
393        headers: {
394          'Content-Type': 'application/json',
395          Authorization: `Bearer ${tokenData.access_token}`,
396        },
397        body: JSON.stringify({
398          auth_algo: authAlgo,
399          cert_url: certUrl,
400          transmission_id: transmissionId,
401          transmission_sig: transmissionSig,
402          transmission_time: transmissionTime,
403          webhook_id: webhookId,
404          webhook_event: JSON.parse(rawBody),
405        }),
406      });
407  
408      if (!verifyRes.ok) {
409        const errText = await verifyRes.text();
410        logger.error(`PayPal signature verification request failed: ${verifyRes.status} ${errText}`);
411        return { verified: false, error: 'Signature verification request failed' };
412      }
413  
414      const verifyData = await verifyRes.json();
415      const verified = verifyData.verification_status === 'SUCCESS';
416  
417      if (!verified) {
418        logger.warn(`PayPal signature verification failed: ${verifyData.verification_status}`);
419      }
420  
421      return { verified };
422    } catch (err) {
423      logger.error('PayPal signature verification error:', err);
424      return { verified: false, error: err.message };
425    }
426  }
427  
428  /**
429   * Create Express webhook server
430   */
431  export function createWebhookServer(port = 3001) {
432    const app = express();
433  
434    // Capture raw body for signature verification, then parse JSON.
435    // PayPal's verification API needs the exact raw bytes of the request body.
436    app.use(
437      express.json({
438        verify: (req, _res, buf) => {
439          req.rawBody = buf.toString('utf8');
440        },
441      })
442    );
443  
444    // Health check endpoint
445    app.get('/health', (_req, res) => {
446      res.json({ status: 'ok', service: 'paypal-webhook' });
447    });
448  
449    // PayPal webhook endpoint
450    app.post('/webhook/paypal', async (req, res) => {
451      try {
452        // ── Signature verification ────────────────────────────────────────
453        // Verify the webhook actually came from PayPal before processing.
454        // This prevents forged payment events from triggering report generation.
455        const { verified, error: verifyError } = await verifyWebhookSignature(req);
456        if (!verified) {
457          logger.warn(`Webhook signature verification failed: ${verifyError}`);
458          return res.status(401).json({ error: 'Webhook signature verification failed' });
459        }
460  
461        const event = req.body;
462        logger.info(`Received PayPal webhook (verified): ${event.event_type}`);
463  
464        // Handle different event types
465        switch (event.event_type) {
466          case 'CHECKOUT.ORDER.APPROVED':
467          case 'PAYMENT.CAPTURE.COMPLETED': {
468            // Extract order ID from webhook event
469            const orderId =
470              event.resource?.id || event.resource?.supplementary_data?.related_ids?.order_id;
471  
472            if (!orderId) {
473              logger.warn('No order ID in webhook payload');
474              return res.status(400).json({ error: 'Missing order ID' });
475            }
476  
477            // Process payment asynchronously (don't block webhook response)
478            processPaymentComplete(orderId)
479              .then(result => {
480                logger.success(`Webhook processing complete: ${JSON.stringify(result)}`);
481              })
482              .catch(error => {
483                logger.error('Webhook processing error', error);
484              });
485  
486            // Respond immediately to PayPal
487            res.status(200).json({ received: true });
488            break;
489          }
490  
491          case 'PAYMENT.CAPTURE.DENIED':
492          case 'PAYMENT.CAPTURE.REFUNDED': {
493            logger.warn(`Payment issue: ${event.event_type}`);
494            res.status(200).json({ received: true });
495            break;
496          }
497  
498          default:
499            logger.info(`Unhandled event type: ${event.event_type}`);
500            res.status(200).json({ received: true });
501        }
502      } catch (error) {
503        logger.error('Webhook error', error);
504        res.status(500).json({ error: 'Internal server error' });
505      }
506    });
507  
508    return app;
509  }
510  
511  /**
512   * Start webhook server
513   */
514  export function startWebhookServer(port = 3001) {
515    const app = createWebhookServer(port);
516  
517    app.listen(port, () => {
518      logger.success(`PayPal webhook server listening on port ${port}`);
519      console.log(`\nPayPal Webhook Server`);
520      console.log(`  Health: http://localhost:${port}/health`);
521      console.log(`  Webhook: http://localhost:${port}/webhook/paypal`);
522      console.log(`\nConfigure this URL in your PayPal Developer Dashboard:`);
523      console.log(`  ${process.env.BASE_URL || 'https://yourdomain.com'}/webhook/paypal\n`);
524    });
525  
526    return app;
527  }
528  
529  // CLI functionality
530  if (import.meta.url === `file://${process.argv[1]}`) {
531    const command = process.argv[2];
532  
533    if (command === 'server') {
534      const port = parseInt(process.argv[3]) || 3001;
535      startWebhookServer(port);
536    } else if (command === 'process') {
537      const orderId = process.argv[3];
538  
539      if (!orderId) {
540        console.error('Usage: node src/payment/webhook-handler.js process <order_id>');
541        process.exit(1);
542      }
543  
544      processPaymentComplete(orderId)
545        .then(result => {
546          console.log('\n✅ Payment Processing Complete\n');
547          console.log(JSON.stringify(result, null, 2));
548          console.log('');
549          process.exit(0);
550        })
551        .catch(error => {
552          logger.error('Processing failed', error);
553          process.exit(1);
554        });
555    } else {
556      console.log('PayPal Webhook Handler');
557      console.log('');
558      console.log('Usage:');
559      console.log('  server [port]       - Start webhook server (default: 3001)');
560      console.log('  process <order_id>  - Manually process a payment');
561      console.log('');
562      console.log('Examples:');
563      console.log('  node src/payment/webhook-handler.js server 3001');
564      console.log('  node src/payment/webhook-handler.js process 8AB12345CD678901E');
565      console.log('');
566      console.log('Production Deployment:');
567      console.log('  1. Deploy as Cloudflare Worker (recommended) or Express server');
568      console.log('  2. Configure webhook URL in PayPal Developer Dashboard');
569      console.log('  3. Subscribe to: CHECKOUT.ORDER.APPROVED, PAYMENT.CAPTURE.COMPLETED\n');
570      process.exit(1);
571    }
572  }
573  
574  // Exported for testing only
575  export { claimWebhook, verifyPaymentAmount, AMOUNT_TOLERANCE };
576  
577  export default {
578    processPaymentComplete,
579    createWebhookServer,
580    startWebhookServer,
581  };