rentall-app/lambdas/payoutRetryProcessor/queries.js

const { db } = require("../shared");

/**
 * Get all failed payouts with eligible owners (Stripe enabled).
 * Matches the query from backend PayoutService.retryFailedPayouts()
 * @returns {Promise<Array>} Array of failed payouts with owner and item details
 */
async function getFailedPayoutsWithOwners() {
  const result = await db.query(
    `SELECT
      r.id,
      r."itemId",
      r."renterId",
      r."ownerId",
      r."startDateTime",
      r."endDateTime",
      r."totalAmount",
      r."payoutAmount",
      r."platformFee",
      r."paymentStatus",
      r."payoutStatus",
      r.status,
      r."stripeTransferId",
      r."createdAt",
      r."updatedAt",
      -- Owner fields
      owner.id AS "owner_id",
      owner.email AS "owner_email",
      owner."firstName" AS "owner_firstName",
      owner."lastName" AS "owner_lastName",
      owner."stripeConnectedAccountId" AS "owner_stripeConnectedAccountId",
      owner."stripePayoutsEnabled" AS "owner_stripePayoutsEnabled",
      -- Item fields
      item.id AS "item_id",
      item.name AS "item_name"
    FROM "Rentals" r
    INNER JOIN "Users" owner ON r."ownerId" = owner.id
    INNER JOIN "Items" item ON r."itemId" = item.id
    WHERE r.status = 'completed'
      AND r."paymentStatus" = 'paid'
      AND r."payoutStatus" = 'failed'
      AND owner."stripeConnectedAccountId" IS NOT NULL
      AND owner."stripePayoutsEnabled" = true`
  );

  // Transform flat results into nested structure
  return result.rows.map((row) => ({
    id: row.id,
    itemId: row.itemId,
    renterId: row.renterId,
    ownerId: row.ownerId,
    startDateTime: row.startDateTime,
    endDateTime: row.endDateTime,
    totalAmount: parseFloat(row.totalAmount),
    payoutAmount: parseFloat(row.payoutAmount),
    platformFee: parseFloat(row.platformFee),
    paymentStatus: row.paymentStatus,
    payoutStatus: row.payoutStatus,
    status: row.status,
    stripeTransferId: row.stripeTransferId,
    createdAt: row.createdAt,
    updatedAt: row.updatedAt,
    owner: {
      id: row.owner_id,
      email: row.owner_email,
      firstName: row.owner_firstName,
      lastName: row.owner_lastName,
      stripeConnectedAccountId: row.owner_stripeConnectedAccountId,
      stripePayoutsEnabled: row.owner_stripePayoutsEnabled,
    },
    item: {
      id: row.item_id,
      name: row.item_name,
    },
  }));
}

/**
 * Update rental payout status to pending (before retry attempt).
 * @param {string} rentalId - UUID of the rental
 * @returns {Promise<void>}
 */
async function resetPayoutToPending(rentalId) {
  await db.query(
    `UPDATE "Rentals"
     SET "payoutStatus" = 'pending',
         "updatedAt" = NOW()
     WHERE id = $1`,
    [rentalId]
  );
}

/**
 * Update rental with successful payout information.
 * @param {string} rentalId - UUID of the rental
 * @param {string} stripeTransferId - Stripe transfer ID
 * @returns {Promise<void>}
 */
async function updatePayoutSuccess(rentalId, stripeTransferId) {
  await db.query(
    `UPDATE "Rentals"
     SET "payoutStatus" = 'completed',
         "payoutProcessedAt" = NOW(),
         "stripeTransferId" = $2,
         "updatedAt" = NOW()
     WHERE id = $1`,
    [rentalId, stripeTransferId]
  );
}

/**
 * Update rental payout status to failed.
 * @param {string} rentalId - UUID of the rental
 * @returns {Promise<void>}
 */
async function updatePayoutFailed(rentalId) {
  await db.query(
    `UPDATE "Rentals"
     SET "payoutStatus" = 'failed',
         "updatedAt" = NOW()
     WHERE id = $1`,
    [rentalId]
  );
}

/**
 * Clear Stripe connection for a disconnected account.
 * Called when we detect an account is no longer connected.
 * @param {string} stripeConnectedAccountId - The disconnected account ID
 * @returns {Promise<Object|null>} The affected user or null
 */
async function clearDisconnectedAccount(stripeConnectedAccountId) {
  const result = await db.query(
    `UPDATE "Users"
     SET "stripeConnectedAccountId" = NULL,
         "stripePayoutsEnabled" = false,
         "updatedAt" = NOW()
     WHERE "stripeConnectedAccountId" = $1
     RETURNING id, email, "firstName", "lastName"`,
    [stripeConnectedAccountId]
  );

  return result.rows[0] || null;
}

module.exports = {
  getFailedPayoutsWithOwners,
  resetPayoutToPending,
  updatePayoutSuccess,
  updatePayoutFailed,
  clearDisconnectedAccount,
};