`lib/services/marketplace.js`

104 lines
// Marketplace totals for admin dashboards. Pure read functions over
// `bookings` + `payment_events`. Two scopes:
//   - per-installer (what one studio sees in its admin)
//   - platform-wide (what NPH staff sees in /admin/billing)
// Defensive against empty tables / NULL sums; always returns numeric zeros.

const db = require('../db');

function num(v) { return v == null ? 0 : Number(v); }

async function getInstallerMarketplaceTotals(installerId) {
  // Deposits paid: bookings where deposit_status='paid' for this installer.
  // Platform fee: sum of application_fee_cents from payment_events for this
  //   installer's PI succeeded events.
  // Pending payouts (platform_hold): bookings where deposit_status='paid' AND
  //   the installer had no Connect account at charge-time → metadata flagged
  //   the payout for manual transfer. We approximate by counting paid bookings
  //   where the installer's stripe_account_id is NULL right now.
  const summary = await db.one(
    `SELECT
       COALESCE(SUM(b.deposit_amount_cents) FILTER (WHERE b.deposit_status='paid'), 0)::bigint AS deposits_paid_cents,
       COUNT(*) FILTER (WHERE b.deposit_status='paid')::int  AS count_paid,
       COUNT(*) FILTER (WHERE b.deposit_status='failed')::int AS count_failed,
       COUNT(*) FILTER (WHERE b.deposit_status='requires_payment')::int AS count_pending
     FROM bookings b
     WHERE b.installer_id = $1`,
    [installerId]
  ) || {};

  const fees = await db.one(
    `SELECT COALESCE(SUM(pe.application_fee_cents), 0)::bigint AS platform_fee_cents
       FROM payment_events pe
      WHERE pe.installer_id = $1
        AND pe.event_type = 'payment_intent.succeeded'`,
    [installerId]
  ) || {};

  const installer = await db.one(
    `SELECT stripe_account_id, stripe_account_charges_enabled FROM installers WHERE id = $1`,
    [installerId]
  ) || {};

  // If no Connect account or charges not enabled, all paid deposits are still
  // sitting in the platform balance pending manual transfer at onboarding.
  const onConnect = !!installer.stripe_account_id && !!installer.stripe_account_charges_enabled;
  const payoutsPending = onConnect ? 0 : (num(summary.deposits_paid_cents) - num(fees.platform_fee_cents));

  return {
    deposits_paid_cents: num(summary.deposits_paid_cents),
    platform_fee_cents:  num(fees.platform_fee_cents),
    payouts_pending_cents: Math.max(0, payoutsPending),
    count_paid:    num(summary.count_paid),
    count_failed:  num(summary.count_failed),
    count_pending: num(summary.count_pending),
    on_connect:    onConnect
  };
}

async function getPlatformMarketplaceTotals() {
  const summary = await db.one(
    `SELECT
       COALESCE(SUM(deposit_amount_cents) FILTER (WHERE deposit_status='paid'), 0)::bigint AS deposits_paid_cents,
       COUNT(*) FILTER (WHERE deposit_status='paid')::int   AS count_paid,
       COUNT(*) FILTER (WHERE deposit_status='failed')::int AS count_failed,
       COUNT(*) FILTER (WHERE deposit_status='requires_payment')::int AS count_pending,
       COUNT(DISTINCT installer_id) FILTER (WHERE deposit_status='paid')::int AS active_installers
     FROM bookings`
  ) || {};

  const fees = await db.one(
    `SELECT COALESCE(SUM(application_fee_cents), 0)::bigint AS platform_fee_cents
       FROM payment_events
      WHERE event_type = 'payment_intent.succeeded'`
  ) || {};

  // Platform-hold sum: paid bookings where the installer is NOT on Connect.
  const hold = await db.one(
    `SELECT COALESCE(SUM(b.deposit_amount_cents), 0)::bigint AS held_cents
       FROM bookings b
       JOIN installers i ON i.id = b.installer_id
      WHERE b.deposit_status = 'paid'
        AND (i.stripe_account_id IS NULL OR i.stripe_account_charges_enabled = false)`
  ) || {};

  // Subtract platform fees retained from the held total (rough approx).
  const heldNet = Math.max(0, num(hold.held_cents) - num(fees.platform_fee_cents));

  return {
    deposits_paid_cents:  num(summary.deposits_paid_cents),
    platform_fee_cents:   num(fees.platform_fee_cents),
    transferred_cents:    Math.max(0, num(summary.deposits_paid_cents) - num(fees.platform_fee_cents) - heldNet),
    payouts_pending_cents: heldNet,
    count_paid:    num(summary.count_paid),
    count_failed:  num(summary.count_failed),
    count_pending: num(summary.count_pending),
    active_installers: num(summary.active_installers)
  };
}

module.exports = {
  getInstallerMarketplaceTotals,
  getPlatformMarketplaceTotals
};