`test/payouts.test.js`

169 lines
'use strict';
// Integration test for the settlement engine (lib/payouts.createPayoutForBooking)
// against the REAL local dev DB, in SANDBOX (liveMode=false — no creds, no money).
//
// Isolation note: createPayoutForBooking uses the pooled `pool.query` (many
// connections), so a single-client BEGIN/ROLLBACK can NOT wrap its writes.
// Instead we insert sentinel fixtures (committed), assert, and DELETE everything
// we created in a guaranteed FK-safe `finally`. This restores the DB on any
// assertion failure; it does NOT protect against a hard process kill (SIGINT)
// mid-run, which would leave YOLOTEST-* rows behind (harmless, dedupable).
require('dotenv').config(); // load DATABASE_URL before lib/db builds the pool
const { test, after } = require('node:test');
const assert = require('node:assert');
const { pool } = require('../lib/db');
const { createPayoutForBooking } = require('../lib/payouts');

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

const SENT = `YOLOTEST-${Date.now()}`; // unique so we only ever delete our own rows

async function mkUser(name) {
  const { rows: [u] } = await pool.query(
    `INSERT INTO app_users (full_name) VALUES ($1) RETURNING id`, [`${SENT}-${name}`]);
  return u.id;
}
async function mkHost(userId) {
  const { rows: [h] } = await pool.query(
    `INSERT INTO hosts (user_id, legal_name, country, kyc_status) VALUES ($1,$2,'CR','verified') RETURNING id`,
    [userId, `${SENT} Host`]);
  return h.id;
}
async function mkPayoutMethod(hostId, kind, extra = {}) {
  const { rows: [pm] } = await pool.query(
    `INSERT INTO payout_methods (host_id, kind, sinpe_phone, cr_iban, is_default, verified)
     VALUES ($1,$2,$3,$4,true,true) RETURNING id`,
    [hostId, kind, extra.sinpe_phone || null, extra.cr_iban || null]);
  await pool.query(`UPDATE hosts SET default_payout_method_id=$1 WHERE id=$2`, [pm.id, hostId]);
  return pm.id;
}
// Each test booking gets its OWN far-future, non-overlapping date window.
//
// The old version hardcoded CURRENT_DATE + 7 .. + 9 for every booking on place_id 1,
// which made this suite DATE-DEPENDENT: migration 008 adds
//   EXCLUDE ... (place_id WITH =, daterange(check_in, check_out, '[)') WITH &&)
//   WHERE status IN ('confirmed','pending')
// so any booking created with a non-completed status collided with the seeded
// confirmed booking on place 1 (2026-09-10 .. 2026-09-13) whenever today's date put
// the +7/+9 window inside it — passing most days and failing for a few. It also meant
// two pending bookings in one run could collide with each other.
//
// A per-booking offset far past any seeded row removes both collision classes and
// keeps the suite deterministic on every date.
let bookingWindow = 0;
async function mkBooking(hostId, travelerId, { status = 'completed', hostPayout = 36000 } = {}) {
  const startOffset = 1000 + bookingWindow * 3;
  bookingWindow += 1;
  const { rows: [b] } = await pool.query(
    // Satisfy the bookings CHECKs: has-a-date (check_in), stay-order (check_out>check_in),
    // and total_reconciles (total = platform_fee + host_payout, so platform_fee = 40000 - host_payout).
    `INSERT INTO bookings (code, place_id, host_id, traveler_id, currency, subtotal, total, platform_fee, host_payout, status, check_in, check_out)
     VALUES ($1, 1, $2, $3, 'CRC', 36000, 40000, 40000 - $4, $4, $5,
             CURRENT_DATE + $6::int, CURRENT_DATE + ($6::int + 2)) RETURNING id`,
    [`${SENT}-${Math.random().toString(36).slice(2, 8)}`, hostId, travelerId, hostPayout, status, startOffset]);
  return b.id;
}

// migrate_012 completeness backstop (Cody cold audit, cycle 23): a payout method must
// carry the identifier its kind pays to, so an incomplete method can't strand a host
// at $0. The route 400s these; these CHECKs are the DB-level backstop.
test('payout_methods completeness CHECKs: sinpe_movil needs a phone, cr_iban needs an IBAN (23514)', async () => {
  const created = { users: [], hosts: [] };
  try {
    const uid = await mkUser('completeness'); created.users.push(uid);
    const hid = await mkHost(uid); created.hosts.push(hid);
    await assert.rejects(
      () => pool.query(`INSERT INTO payout_methods (host_id, kind) VALUES ($1,'sinpe_movil')`, [hid]),
      (e) => { assert.equal(e.code, '23514'); assert.match(String(e.constraint), /sinpe_phone_reqd/); return true; },
      'a sinpe_movil with no phone must be rejected');
    await assert.rejects(
      () => pool.query(`INSERT INTO payout_methods (host_id, kind) VALUES ($1,'cr_iban')`, [hid]),
      (e) => { assert.equal(e.code, '23514'); assert.match(String(e.constraint), /iban_reqd/); return true; },
      'a cr_iban with no IBAN must be rejected');
    // A complete method AND a plaid_ach (identifier via /host/plaid exchange) are allowed.
    const { rows: [okp] } = await pool.query(`INSERT INTO payout_methods (host_id, kind, sinpe_phone) VALUES ($1,'sinpe_movil','8888-0000') RETURNING id`, [hid]);
    assert.ok(okp.id, 'a complete sinpe_movil is accepted');
    const { rows: [oka] } = await pool.query(`INSERT INTO payout_methods (host_id, kind) VALUES ($1,'plaid_ach') RETURNING id`, [hid]);
    assert.ok(oka.id, 'plaid_ach is not constrained by these CHECKs (its identifier comes from the exchange)');
  } finally {
    if (created.hosts.length) await pool.query(`UPDATE hosts SET default_payout_method_id=NULL WHERE id = ANY($1)`, [created.hosts]).catch(() => {});
    if (created.hosts.length) await pool.query(`DELETE FROM payout_methods WHERE host_id = ANY($1)`, [created.hosts]);
    if (created.hosts.length) await pool.query(`DELETE FROM hosts WHERE id = ANY($1)`, [created.hosts]);
    if (created.users.length) await pool.query(`DELETE FROM app_users WHERE id = ANY($1)`, [created.users]);
  }
});

test('payouts settlement — sandbox, real DB, self-cleaning', async (t) => {
  const created = { payouts: [], bookings: [], pms: [], hosts: [], users: [] };
  const track = (bucket, id) => { created[bucket].push(id); return id; };
  try {
    const hostUser = track('users', await mkUser('hostuser'));
    const traveler = track('users', await mkUser('traveler'));

    // --- SINPE host ---
    const hostA = track('hosts', await mkHost(hostUser));
    track('pms', await mkPayoutMethod(hostA, 'sinpe_movil', { sinpe_phone: '8888-0000' }));
    const bkSinpe = track('bookings', await mkBooking(hostA, traveler));

    await t.test('sinpe: routes rail=sinpe and records a processing payout', async () => {
      const res = await createPayoutForBooking(bkSinpe);
      assert.equal(res.rail, 'sinpe');
      assert.equal(res.amount, 36000, 'amount = booking.host_payout');
      const { rows: [row] } = await pool.query(`SELECT id, host_id, booking_id, rail, currency, amount, status, provider_ref, live_mode FROM payouts WHERE booking_id=$1`, [bkSinpe]);
      track('payouts', row.id);
      assert.equal(row.rail, 'sinpe');
      assert.equal(row.status, 'processing');
      assert.equal(row.live_mode, false, 'sandbox — never live');
      assert.match(String(row.provider_ref), /_sbx_/, 'sandbox provider ref');
    });

    // --- Plaid ACH host (sandbox branch must NOT throw; liveMode=false) ---
    const hostB = track('hosts', await mkHost(track('users', await mkUser('achuser'))));
    track('pms', await mkPayoutMethod(hostB, 'plaid_ach'));
    const bkAch = track('bookings', await mkBooking(hostB, traveler));

    await t.test('plaid_ach: sandbox records rail=plaid_ach without throwing', async () => {
      const res = await createPayoutForBooking(bkAch);
      assert.equal(res.rail, 'plaid_ach');
      const { rows: [row] } = await pool.query(`SELECT id, host_id, booking_id, rail, currency, amount, status, provider_ref, live_mode FROM payouts WHERE booking_id=$1`, [bkAch]);
      track('payouts', row.id);
      assert.equal(row.status, 'processing');
      assert.match(String(row.provider_ref), /ach_sbx_/);
    });

    // --- Guards ---
    await t.test('rejects a booking that is not completed', async () => {
      const bkPending = track('bookings', await mkBooking(hostA, traveler, { status: 'pending' }));
      await assert.rejects(() => createPayoutForBooking(bkPending), /not completed/);
    });

    await t.test('rejects a host with no payout method', async () => {
      const lonelyHost = track('hosts', await mkHost(track('users', await mkUser('nopm'))));
      const bk = track('bookings', await mkBooking(lonelyHost, traveler));
      await assert.rejects(() => createPayoutForBooking(bk), /no payout method/);
    });

    // Guard the money-misdirection bug Cody found: the partial-unique index
    // idx_payout_methods_one_default_per_host must forbid a second default so the
    // rail-selection query can never tie. hostA already has one default (sinpe).
    await t.test('DB forbids a second default payout method per host', async () => {
      await assert.rejects(
        // Complete method (has an IBAN) so it passes migrate_012's completeness CHECK
        // and actually reaches the one-default unique index this test targets.
        () => pool.query(`INSERT INTO payout_methods (host_id, kind, cr_iban, is_default) VALUES ($1,'cr_iban','CR05000000000000000001',true)`, [hostA]),
        /duplicate key|one_default_per_host/,
        'a second is_default=true for the same host must be rejected');
    });
  } finally {
    // FK-safe teardown: payouts -> bookings -> null host default -> payout_methods -> hosts -> users
    const del = async (sql, ids) => { if (ids.length) await pool.query(sql, [ids]); };
    await del(`DELETE FROM payouts WHERE id = ANY($1)`, created.payouts);
    await del(`DELETE FROM payouts WHERE booking_id = ANY($1)`, created.bookings); // any we didn't track
    await del(`DELETE FROM bookings WHERE id = ANY($1)`, created.bookings);
    if (created.hosts.length) await pool.query(`UPDATE hosts SET default_payout_method_id=NULL WHERE id = ANY($1)`, [created.hosts]);
    await del(`DELETE FROM payout_methods WHERE id = ANY($1)`, created.pms);
    await del(`DELETE FROM hosts WHERE id = ANY($1)`, created.hosts);
    await del(`DELETE FROM app_users WHERE id = ANY($1)`, created.users);
  }
});