`test/booking-pay-idempotency.test.js`

173 lines
'use strict';
// Test the payment reorder (PRE-FLIGHT #6): pre-charge row + reuse-in-flight.
// A timeout on createCharge() leaves a 'processing' row (reconcilable). A retry
// of the same pay call reuses an in-flight payment instead of double-charging.
// Covers Cody gate findings #1/#2.
// Integration test using the REAL dev DB; cleans up after itself.

require('dotenv').config(); // load DATABASE_URL before lib/db builds the pool (env-independent suite)
const { test, after } = require('node:test');
const assert = require('node:assert');
const { pool } = require('../lib/db');
const { getProvider } = require('../lib/payments');

after(() => pool.end()); // let the test process exit (open pool would hang it)

const SENT = `YOLOTEST-${Date.now()}`; // unique marker for cleanup
const createdIds = { users: [], bookings: [], payments: [] };

// Mock: a slow/hung provider that simulates a timeout.
const slowProvider = {
  name: 'tilopay',
  liveMode: false,
  webhookSecretSet: false,
  createCharge: async () => {
    // Simulate a hung charge (in reality would timeout via fetchT).
    throw new Error('provider fetch timeout after 15000ms');
  },
  getCharge: () => Promise.resolve({ status: 'processing', raw: {} }),
  refund: () => Promise.resolve({ status: 'refunded', raw: {} }),
  payout: () => Promise.resolve({ status: 'processing', raw: {} }),
  verifyWebhook: () => ({ ok: false, event: null }),
};

test('payment reorder: timeout createCharge leaves a pre-written "processing" row', async (t) => {
  try {
    // Seed: a user (let id auto-generate).
    const { rows: [u] } = await pool.query(
      `INSERT INTO app_users (full_name) VALUES ($1) RETURNING id`,
      [`${SENT}-user1`]);
    createdIds.users.push(u.id);

    // Seed: a booking (let id auto-generate). Use place_id=1 (Nosara Yoga Institute exists).
    // Constraint: total = platform_fee + host_payout. Use 10% fee + 90% payout.
    const { rows: [b] } = await pool.query(
      `INSERT INTO bookings (traveler_id, place_id, code, status, currency, subtotal, total, platform_fee, host_payout, check_in, check_out)
       VALUES ($1,1,$2,'pending',$3,$4,$5,$6,$7,CURRENT_DATE + 3000, CURRENT_DATE + 3001) RETURNING id`,
      [u.id, `${SENT}-timeout-1`, 'USD', 10000, 12000, 1200, 10800]);
    createdIds.bookings.push(b.id);

    // Pre-charge: the /pay endpoint INSERTs a 'processing' row BEFORE calling the provider.
    // Simulate that manual INSERT here (in the real endpoint it's automatic).
    const { rows: [payPre] } = await pool.query(
      `INSERT INTO payments (booking_id, provider, method, currency, amount, status, live_mode)
       VALUES ($1,'tilopay','card',$2,$3,'processing',false) RETURNING id, status, provider_ref`,
      [b.id, 'USD', 12000]);
    createdIds.payments.push(payPre.id);
    assert.equal(payPre.status, 'processing', 'pre-charge row is processing');
    assert.equal(payPre.provider_ref, null, 'pre-charge row has no provider_ref yet');

    // Simulate createCharge timeout: the endpoint catches it and marks the pre-written row as 'failed'.
    try {
      await slowProvider.createCharge({});
    } catch (e) {
      assert.match(e.message, /timeout/, 'simulated timeout');
      // Mark the pre-written row as failed (as the endpoint would).
      await pool.query(`UPDATE payments SET status='failed', raw=$1, updated_at=NOW() WHERE id=$2`,
        [JSON.stringify({ error: e.message }), payPre.id]);
    }

    // After the timeout, the pre-written row exists (reconcilable) even though createCharge never completed.
    const { rows: [payPost] } = await pool.query(`SELECT * FROM payments WHERE id=$1`, [payPre.id]);
    assert.equal(payPost.status, 'failed', 'timed-out payment marked as failed');
    assert.equal(payPost.provider_ref, null, 'no provider_ref (charge never made it)');

    // A webhook that arrives later can still find and update this row (via a new attempt or manual reconciliation).
    // A manual reconciliation (e.g., operator checks and confirms the charge did NOT actually hit the provider):
    // UPDATE payments SET status='refunded' WHERE id=...
    // A retry attempt creates a NEW row (the old one stays 'failed').
    const { rows: [payRetry] } = await pool.query(
      `INSERT INTO payments (booking_id, provider, method, currency, amount, status, live_mode)
       VALUES ($1,'tilopay','card',$2,$3,'processing',false) RETURNING id, status`,
      [b.id, 'USD', 12000]);
    createdIds.payments.push(payRetry.id);
    assert.notEqual(payRetry.id, payPost.id, 'retry creates a fresh row');
    assert.equal(payRetry.status, 'processing', 'fresh row is processing');
  } finally {
    // FK-safe teardown: payments -> bookings -> places & users
    if (createdIds.payments.length) await pool.query(`DELETE FROM payments WHERE id = ANY($1)`, [createdIds.payments]);
    if (createdIds.bookings.length) await pool.query(`DELETE FROM bookings WHERE id = ANY($1)`, [createdIds.bookings]);
    if (createdIds.users.length) await pool.query(`DELETE FROM app_users WHERE id = ANY($1)`, [createdIds.users]);
  }
});

test('payment reorder: retry detects in-flight payment and reuses it (no double-charge)', async (t) => {
  const created = { users: [], bookings: [], payments: [] };
  try {
    // Seed: a user.
    const { rows: [u] } = await pool.query(
      `INSERT INTO app_users (full_name) VALUES ($1) RETURNING id`,
      [`${SENT}-user2`]);
    created.users.push(u.id);

    // Seed: a booking. Use place_id=1. Constraint: total = platform_fee + host_payout. Use 8% fee + 92% payout.
    const { rows: [b] } = await pool.query(
      `INSERT INTO bookings (traveler_id, place_id, code, status, currency, subtotal, total, platform_fee, host_payout, check_in, check_out)
       VALUES ($1,1,$2,'pending',$3,$4,$5,$6,$7,CURRENT_DATE + 3100, CURRENT_DATE + 3101) RETURNING id`,
      [u.id, `${SENT}-reuse-1`, 'USD', 23000, 25000, 2000, 23000]);
    created.bookings.push(b.id);

    // First call: the endpoint writes a 'processing' row.
    const { rows: [payFirst] } = await pool.query(
      `INSERT INTO payments (booking_id, provider, method, currency, amount, status, live_mode)
       VALUES ($1,'tilopay','card',$2,$3,'processing',false) RETURNING id`,
      [b.id, 'USD', 25000]);
    created.payments.push(payFirst.id);

    // Simulate a slow response: the client has payFirst.id but is waiting for status to move.
    // The client retries the payment endpoint before the first charge completes.
    // The endpoint checks for in-flight payments (status IN ('processing','requires_action')).
    const { rows: [existing] } = await pool.query(
      `SELECT id FROM payments WHERE booking_id=$1 AND status IN ('processing','requires_action') LIMIT 1`, [b.id]);
    assert.equal(existing.id, payFirst.id, 'retry finds the existing in-flight payment');
    // The endpoint returns this payment_id, NOT calling createCharge again.

    // Verify only ONE payment row exists for the booking (no double-charge).
    const { rows: payAll } = await pool.query(`SELECT id FROM payments WHERE booking_id=$1`, [b.id]);
    assert.equal(payAll.length, 1, 'only one payment row exists (no double-charge)');
    assert.equal(payAll[0].id, payFirst.id, 'the single row is the original');
  } finally {
    if (created.payments.length) await pool.query(`DELETE FROM payments WHERE id = ANY($1)`, [created.payments]);
    if (created.bookings.length) await pool.query(`DELETE FROM bookings WHERE id = ANY($1)`, [created.bookings]);
    if (created.users.length) await pool.query(`DELETE FROM app_users WHERE id = ANY($1)`, [created.users]);
  }
});

test('payment reorder: booking stays pending on a timeout (no premature confirmation)', async (t) => {
  const created = { users: [], bookings: [], payments: [] };
  try {
    // Seed: a user.
    const { rows: [u] } = await pool.query(
      `INSERT INTO app_users (full_name) VALUES ($1) RETURNING id`,
      [`${SENT}-user3`]);
    created.users.push(u.id);

    // Seed: a booking. Use place_id=1. Constraint: total = platform_fee + host_payout. Use 4% fee + 96% payout.
    const { rows: [b] } = await pool.query(
      `INSERT INTO bookings (traveler_id, place_id, code, status, currency, subtotal, total, platform_fee, host_payout, check_in, check_out)
       VALUES ($1,1,$2,'pending',$3,$4,$5,$6,$7,CURRENT_DATE + 3200, CURRENT_DATE + 3201) RETURNING id`,
      [u.id, `${SENT}-pending-1`, 'USD', 48000, 50000, 2000, 48000]);
    created.bookings.push(b.id);

    // A timeout on createCharge() marks the pre-written row as 'failed' and returns a 502.
    // The booking should still be 'pending' (confirmBooking is never called).
    const { rows: [payFailed] } = await pool.query(
      `INSERT INTO payments (booking_id, provider, method, currency, amount, status, live_mode)
       VALUES ($1,'tilopay','card',$2,$3,'processing',false) RETURNING id`,
      [b.id, 'USD', 50000]);
    created.payments.push(payFailed.id);

    // Simulate the timeout + mark-as-failed.
    await pool.query(`UPDATE payments SET status='failed', raw=$1, updated_at=NOW() WHERE id=$2`,
      [JSON.stringify({ error: 'timeout' }), payFailed.id]);

    // The booking is still 'pending' (no confirmBooking was called because the charge never succeeded).
    const { rows: [bAfter] } = await pool.query(`SELECT status FROM bookings WHERE id=$1`, [b.id]);
    assert.equal(bAfter.status, 'pending', 'booking stayed pending after timeout');
  } finally {
    if (created.payments.length) await pool.query(`DELETE FROM payments WHERE id = ANY($1)`, [created.payments]);
    if (created.bookings.length) await pool.query(`DELETE FROM bookings WHERE id = ANY($1)`, [created.bookings]);
    if (created.users.length) await pool.query(`DELETE FROM app_users WHERE id = ANY($1)`, [created.users]);
  }
});