refunds.js

Go to the documentation of this file.

/**
 * @file Invoice Refunds API Routes
 * @module routes/refunds
 * @description
 * Handles invoice refund operations through Stripe. Supports full invoice refunds and
 * granular line-item refunds with inventory restocking. Restricted to tenant administrators.
 *
 * **Refund Types:**
 * - Full: Refund entire invoice amount via Stripe
 * - Line Item: Refund specific quantities of specific items
 *
 * **Features:**
 * - Stripe Connected Account refund processing
 * - Automatic inventory restocking
 * - Partial refund support (multiple refunds per invoice)
 * - Refund history tracking
 * - Audit trail with user who processed refund
 *
 * **Security:**
 * - Tenant admin role required (admin/msp/root)
 * - Tenant isolation enforced
 * - Payment validation before refund
 * - Prevents over-refunding
 *
 * @requires stripe
 * @requires ../services/db
 * @requires ../middleware/auth
 * @requires ../middleware/tenantAdmin
 */
 
const express = require('express');
const router = express.Router();
const pool = require('../services/db');
 
// Initialize Stripe with correct key based on mode
const stripeMode = process.env.STRIPE_MODE || 'test';
const stripeSecretKey = stripeMode === 'live' 
  ? process.env.STRIPE_LIVE_SECRET_KEY 
  : process.env.STRIPE_TEST_SECRET_KEY;
const stripe = require('stripe')(stripeSecretKey);
 
console.log(`[Stripe] Refunds initialized in ${stripeMode.toUpperCase()} mode`);
const authenticateToken = require('../middleware/auth');
const requireTenantAdmin = require('../middleware/tenantAdmin');
const { getTenantFilter } = require('../middleware/tenant');
 
/**
 * @api {post} /invoices/:id/refund/full Full Invoice Refund
 * @apiName FullRefund
 * @apiGroup Refunds
 * @apiDescription Processes a full refund for a paid invoice through Stripe.
 * Refunds the entire payment amount, restocks all inventory items, and updates
 * invoice status to refunded.
 *
 * **Process:**
 * 1. Validate invoice is paid and belongs to tenant
 * 2. Check invoice hasn't already been fully refunded
 * 3. Get original payment details from Stripe
 * 4. Create refund through Stripe Connected Account
 * 5. Restock all inventory items
 * 6. Update invoice payment_status to 'refunded'
 * 7. Record refund in database with audit trail
 *
 * **Stripe Integration:**
 * - Uses original payment_intent_id
 * - Processes through Connected Account
 * - Handles application fees automatically
 * - Includes metadata for tracking
 *
 * **Inventory:**
 * - Restocks all items with is_stock=true
 * - Updates product stock_quantity
 * - Transaction-safe (rolls back on failure)
 *
 * @apiHeader {string} Authorization Bearer JWT token (tenant admin required)
 * @apiHeader {string} Content-Type application/json
 * @apiParam {number} id Invoice ID (URL parameter)
 * @apiParam {string} [reason] Refund reason (duplicate, fraudulent, requested_by_customer, other)
 * @apiParam {string} [notes] Internal notes about the refund
 *
 * @apiSuccess {boolean} success Always true
 * @apiSuccess {string} message Success message
 * @apiSuccess {number} refundId Database refund record ID
 * @apiSuccess {string} stripeRefundId Stripe refund ID (re_xxx)
 * @apiSuccess {number} amount Refunded amount in cents
 * @apiSuccess {string} currency Currency code
 * @apiSuccess {string} status Refund status (succeeded, pending, failed)
 *
 * @apiError {number} 401 Authentication required
 * @apiError {number} 403 Tenant admin access required
 * @apiError {number} 404 Invoice not found or not in tenant
 * @apiError {number} 400 Invoice not paid / already refunded / invalid state
 * @apiError {number} 500 Stripe error or database failure
 *
 * @apiExample {curl} Example Request:
 *   curl -X POST \
 *        -H "Authorization: Bearer eyJhbGc..." \
 *        -H "Content-Type: application/json" \
 *        -d '{"reason": "requested_by_customer", "notes": "Customer requested full refund due to service issue"}' \
 *        "https://api.everydaytech.au/invoices/123/refund/full"
 *
 * @apiExample {json} Success Response:
 *   HTTP/1.1 200 OK
 *   {
 *     "success": true,
 *     "message": "Full refund processed successfully",
 *     "refundId": 45,
 *     "stripeRefundId": "re_1AbC2DeFgHiJkL3M",
 *     "amount": 29900,
 *     "currency": "AUD",
 *     "status": "succeeded",
 *     "itemsRestocked": 3
 *   }
 */
router.post('/:id/refund/full', authenticateToken, requireTenantAdmin, async (req, res) => {
  const invoiceId = req.params.id;
  const { reason = 'requested_by_customer', notes = '' } = req.body;
  const userId = req.user?.user_id;
 
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
 
    // Get invoice with tenant filtering
    const { clause, params } = getTenantFilter(req, 'c');
    const invoiceResult = await client.query(`
      SELECT 
        i.*,
        p.payment_id,
        p.amount as payment_amount,
        p.stripe_payment_intent_id,
        p.stripe_account_id,
        p.currency as payment_currency
      FROM invoices i
      LEFT JOIN customers c ON i.customer_id = c.customer_id
      LEFT JOIN payments p ON i.invoice_id = p.invoice_id AND p.status = 'succeeded'
      WHERE i.invoice_id = $1 ${clause ? 'AND ' + clause : ''}
      FOR UPDATE
    `, [invoiceId, ...params]);
 
    if (invoiceResult.rows.length === 0) {
      await client.query('ROLLBACK');
      return res.status(404).json({ error: 'Invoice not found' });
    }
 
    const invoice = invoiceResult.rows[0];
 
    // Validate invoice can be refunded
    if (invoice.payment_status !== 'paid') {
      await client.query('ROLLBACK');
      return res.status(400).json({ 
        error: 'Invoice must be paid to process refund',
        currentStatus: invoice.payment_status
      });
    }
 
    if (invoice.refund_status === 'full') {
      await client.query('ROLLBACK');
      return res.status(400).json({ error: 'Invoice already fully refunded' });
    }
 
    if (!invoice.stripe_payment_intent_id) {
      await client.query('ROLLBACK');
      return res.status(400).json({ error: 'No Stripe payment found for this invoice' });
    }
 
    // Calculate refund amount (total paid minus any previous refunds)
    const alreadyRefunded = parseInt(invoice.amount_refunded || 0);
    const totalPaid = parseInt(invoice.payment_amount);
    const refundAmount = totalPaid - alreadyRefunded;
 
    if (refundAmount <= 0) {
      await client.query('ROLLBACK');
      return res.status(400).json({ error: 'Invoice already fully refunded' });
    }
 
    // Process refund through Stripe
    console.log(`[Refund] Processing full refund for invoice ${invoiceId}: ${refundAmount} cents`);
    
    const stripeRefund = await stripe.refunds.create({
      payment_intent: invoice.stripe_payment_intent_id,
      amount: refundAmount,
      reason: reason,
      metadata: {
        invoice_id: invoiceId.toString(),
        tenant_id: invoice.tenant_id,
        refund_type: 'full',
        processed_by: userId?.toString() || 'unknown'
      }
    },{ 
      stripeAccount: invoice.stripe_account_id 
    });
 
    console.log(`[Refund] Stripe refund created: ${stripeRefund.id}, status: ${stripeRefund.status}`);
 
    // Restock all items
    const itemsResult = await client.query('SELECT * FROM invoice_items WHERE invoice_id = $1', [invoiceId]);
    let itemsRestocked = 0;
    
    for (const it of itemsResult.rows) {
      if (!it.product_id) continue;
      const productResult = await client.query(
        'SELECT product_id, is_stock FROM products WHERE product_id = $1',
        [it.product_id]
      );
      if (productResult.rows.length && productResult.rows[0].is_stock) {
        await client.query(
          'UPDATE products SET stock_quantity = stock_quantity + $1 WHERE product_id = $2',
          [Number(it.quantity || 0), it.product_id]
        );
        itemsRestocked++;
      }
    }
 
    // Create refund record
    const refundResult = await client.query(`
      INSERT INTO refunds (
        tenant_id,
        invoice_id,
        payment_id,
        stripe_refund_id,
        stripe_account_id,
        refund_type,
        amount,
        currency,
        reason,
        notes,
        status,
        stripe_status,
        created_by,
        processed_at
      ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, CURRENT_TIMESTAMP)
      RETURNING refund_id
    `, [
      invoice.tenant_id,
      invoiceId,
      invoice.payment_id,
      stripeRefund.id,
      invoice.stripe_account_id,
      'full',
      refundAmount,
      invoice.payment_currency || 'AUD',
      reason,
      notes,
      stripeRefund.status,
      stripeRefund.status,
      userId
    ]);
 
    // Update invoice
    await client.query(`
      UPDATE invoices 
      SET 
        payment_status = 'refunded',
        refund_status = 'full',
        amount_refunded = COALESCE(amount_refunded, 0) + $1,
        updated_at = CURRENT_TIMESTAMP
      WHERE invoice_id = $2
    `, [refundAmount, invoiceId]);
 
    // Update payment record
    await client.query(`
      UPDATE payments
      SET
        refunded = true,
        amount_refunded = COALESCE(amount_refunded, 0) + $1,
        refunded_at = CURRENT_TIMESTAMP,
        refund_reason = $2,
        updated_at = CURRENT_TIMESTAMP
      WHERE payment_id = $3
    `, [refundAmount, reason, invoice.payment_id]);
 
    await client.query('COMMIT');
 
    res.json({
      success: true,
      message: 'Full refund processed successfully',
      refundId: refundResult.rows[0].refund_id,
      stripeRefundId: stripeRefund.id,
      amount: refundAmount,
      currency: invoice.payment_currency || 'AUD',
      status: stripeRefund.status,
      itemsRestocked
    });
 
  } catch (err) {
    await client.query('ROLLBACK');
    console.error('[Refund] Error processing full refund:', err);
    
    if (err.type === 'StripeInvalidRequestError') {
      return res.status(400).json({ 
        error: 'Stripe refund failed',
        message: err.message 
      });
    }
    
    res.status(500).json({ 
      error: 'Failed to process refund',
      message: err.message 
    });
  } finally {
    client.release();
  }
});
 
/**
 * @api {post} /invoices/:id/refund/line-items Line Item Refund
 * @apiName LineItemRefund
 * @apiGroup Refunds
 * @apiDescription Processes a partial refund for specific line items with quantities.
 * Allows refunding individual items or partial quantities while leaving other items intact.
 *
 * **Process:**
 * 1. Validate invoice is paid and belongs to tenant
 * 2. Calculate total refund amount from line items
 * 3. Check refund doesn't exceed remaining refundable amount
 * 4. Create refund through Stripe
 * 5. Restock specified quantities
 * 6. Record line item details
 * 7. Update invoice refund status
 *
 * @apiHeader {string} Authorization Bearer JWT token (tenant admin required)
 * @apiHeader {string} Content-Type application/json
 * @apiParam {number} id Invoice ID (URL parameter)
 * @apiParam {Object[]} items Line items to refund
 * @apiParam {number} items.item_id Invoice item ID
 * @apiParam {number} items.quantity Quantity to refund (can be partial)
 * @apiParam {string} [reason] Refund reason
 * @apiParam {string} [notes] Internal notes
 *
 * @apiSuccess {boolean} success Always true
 * @apiSuccess {string} message Success message
 * @apiSuccess {number} refundId Database refund record ID
 * @apiSuccess {string} stripeRefundId Stripe refund ID
 * @apiSuccess {number} amount Total refunded amount in cents
 * @apiSuccess {number} itemsRefunded Number of line items refunded
 * @apiSuccess {number} itemsRestocked Number of items restocked
 *
 * @apiError {number} 400 Invalid line items / over-refunding
 * @apiError {number} 404 Invoice not found
 *
 * @apiExample {curl} Example Request:
 *   curl -X POST \
 *        -H "Authorization: Bearer eyJhbGc..." \
 *        -H "Content-Type: application/json" \
 *        -d '{"items": [{"item_id": 45, "quantity": 2}, {"item_id": 46, "quantity": 1}], "reason": "requested_by_customer"}' \
 *        "https://api.everydaytech.au/invoices/123/refund/line-items"
 */
router.post('/:id/refund/line-items', authenticateToken, requireTenantAdmin, async (req, res) => {
  const invoiceId = req.params.id;
  const { items = [], reason = 'requested_by_customer', notes = '' } = req.body;
  const userId = req.user?.user_id;
 
  if (!items || items.length === 0) {
    return res.status(400).json({ error: 'At least one line item required for refund' });
  }
 
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
 
    // Get invoice with tenant filtering
    const { clause, params } = getTenantFilter(req, 'c');
    const invoiceResult = await client.query(`
      SELECT 
        i.*,
        p.payment_id,
        p.amount as payment_amount,
        p.stripe_payment_intent_id,
        p.stripe_account_id,
        p.currency as payment_currency
      FROM invoices i
      LEFT JOIN customers c ON i.customer_id = c.customer_id
      LEFT JOIN payments p ON i.invoice_id = p.invoice_id AND p.status = 'succeeded'
      WHERE i.invoice_id = $1 ${clause ? 'AND ' + clause : ''}
      FOR UPDATE
    `, [invoiceId, ...params]);
 
    if (invoiceResult.rows.length === 0) {
      await client.query('ROLLBACK');
      return res.status(404).json({ error: 'Invoice not found' });
    }
 
    const invoice = invoiceResult.rows[0];
 
    if (invoice.payment_status !== 'paid' && invoice.payment_status !== 'refunded') {
      await client.query('ROLLBACK');
      return res.status(400).json({ 
        error: 'Invoice must be paid to process refund',
        currentStatus: invoice.payment_status
      });
    }
 
    if (!invoice.stripe_payment_intent_id) {
      await client.query('ROLLBACK');
      return res.status(400).json({ error: 'No Stripe payment found for this invoice' });
    }
 
    // Get all invoice items
    const invoiceItemsResult = await client.query(
      'SELECT * FROM invoice_items WHERE invoice_id = $1',
      [invoiceId]
    );
    const invoiceItemsMap = new Map(invoiceItemsResult.rows.map(it => [it.item_id, it]));
 
    // Calculate refund amount and validate items
    let refundAmountCents = 0;
    const refundLineItems = [];
 
    for (const refundItem of items) {
      const invoiceItem = invoiceItemsMap.get(refundItem.item_id);
      if (!invoiceItem) {
        await client.query('ROLLBACK');
        return res.status(400).json({ 
          error: `Invoice item ${refundItem.item_id} not found`
        });
      }
 
      const quantityToRefund = parseFloat(refundItem.quantity);
      const originalQuantity = parseFloat(invoiceItem.quantity);
 
      if (quantityToRefund <= 0 || quantityToRefund > originalQuantity) {
        await client.query('ROLLBACK');
        return res.status(400).json({ 
          error: `Invalid quantity for item ${refundItem.item_id}. Must be between 0 and ${originalQuantity}`
        });
      }
 
      // Calculate refund amount for this line (unit_price * quantity + 10% GST)
      const unitPrice = parseFloat(invoiceItem.unit_price);
      const lineSubtotal = unitPrice * quantityToRefund;
      const lineGST = lineSubtotal * 0.10;
      const lineTotal = lineSubtotal + lineGST;
      const lineTotalCents = Math.round(lineTotal * 100);
 
      refundAmountCents += lineTotalCents;
      refundLineItems.push({
        item_id: refundItem.item_id,
        quantity: quantityToRefund,
        unit_price: unitPrice,
        amount: lineTotalCents,
        product_id: invoiceItem.product_id,
        description: invoiceItem.description
      });
    }
 
    // Validate refund amount doesn't exceed remaining refundable amount
    const alreadyRefunded = parseInt(invoice.amount_refunded || 0);
    const totalPaid = parseInt(invoice.payment_amount);
    const remainingRefundable = totalPaid - alreadyRefunded;
 
    if (refundAmountCents > remainingRefundable) {
      await client.query('ROLLBACK');
      return res.status(400).json({ 
        error: 'Refund amount exceeds remaining refundable amount',
        requested: refundAmountCents,
        available: remainingRefundable
      });
    }
 
    // Process refund through Stripe
    console.log(`[Refund] Processing line item refund for invoice ${invoiceId}: ${refundAmountCents} cents (${items.length} items)`);
    
    const stripeRefund = await stripe.refunds.create({
      payment_intent: invoice.stripe_payment_intent_id,
      amount: refundAmountCents,
      reason: reason,
      metadata: {
        invoice_id:invoiceId.toString(),
        tenant_id: invoice.tenant_id,
        refund_type: 'line_item',
        items_count: items.length.toString(),
        processed_by: userId?.toString() || 'unknown'
      }
    }, {
      stripeAccount: invoice.stripe_account_id
    });
 
    console.log(`[Refund] Stripe refund created: ${stripeRefund.id}, status: ${stripeRefund.status}`);
 
    // Create refund record
    const refundResult = await client.query(`
      INSERT INTO refunds (
        tenant_id,
        invoice_id,
        payment_id,
        stripe_refund_id,
        stripe_account_id,
        refund_type,
        amount,
        currency,
        reason,
        notes,
        status,
        stripe_status,
        created_by,
        processed_at
      ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, CURRENT_TIMESTAMP)
      RETURNING refund_id
    `, [
      invoice.tenant_id,
      invoiceId,
      invoice.payment_id,
      stripeRefund.id,
      invoice.stripe_account_id,
      'line_item',
      refundAmountCents,
      invoice.payment_currency || 'AUD',
      reason,
      notes,
      stripeRefund.status,
      stripeRefund.status,
      userId
    ]);
 
    const refundId = refundResult.rows[0].refund_id;
 
    // Insert refund line items and restock
    let itemsRestocked = 0;
    for (const lineItem of refundLineItems) {
      // Insert refund line item record
      await client.query(`
        INSERT INTO refund_line_items (
          refund_id,
          invoice_item_id,
          product_id,
          description,
          quantity_refunded,
          unit_price,
          refund_amount
        ) VALUES ($1, $2, $3, $4, $5, $6, $7)
      `, [
        refundId,
        lineItem.item_id,
        lineItem.product_id,
        lineItem.description,
        lineItem.quantity,
        lineItem.unit_price,
        lineItem.amount
      ]);
 
      // Restock if product has inventory tracking
      if (lineItem.product_id) {
        const productResult = await client.query(
          'SELECT product_id, is_stock FROM products WHERE product_id = $1',
          [lineItem.product_id]
        );
        if (productResult.rows.length && productResult.rows[0].is_stock) {
          await client.query(
            'UPDATE products SET stock_quantity = stock_quantity + $1 WHERE product_id = $2',
            [lineItem.quantity, lineItem.product_id]
          );
          itemsRestocked++;
        }
      }
    }
 
    // Update invoice refund tracking
    const newAmountRefunded = alreadyRefunded + refundAmountCents;
    const fullyRefunded = newAmountRefunded >= totalPaid;
    
    await client.query(`
      UPDATE invoices 
      SET 
        payment_status = CASE WHEN $1 THEN 'refunded' ELSE 'partial' END,
        refund_status = CASE WHEN $1 THEN 'full' ELSE 'partial' END,
        amount_refunded = $2,
        updated_at = CURRENT_TIMESTAMP
      WHERE invoice_id = $3
    `, [fullyRefunded, newAmountRefunded, invoiceId]);
 
    // Update payment record
    await client.query(`
      UPDATE payments
      SET
        refunded = $1,
        amount_refunded = $2,
        refunded_at = CURRENT_TIMESTAMP,
        refund_reason = $3,
        updated_at = CURRENT_TIMESTAMP
      WHERE payment_id = $4
    `, [fullyRefunded, newAmountRefunded, reason, invoice.payment_id]);
 
    await client.query('COMMIT');
 
    res.json({
      success: true,
      message: 'Line item refund processed successfully',
      refundId,
      stripeRefundId: stripeRefund.id,
      amount: refundAmountCents,
      currency: invoice.payment_currency || 'AUD',
      status: stripeRefund.status,
      itemsRefunded: items.length,
      itemsRestocked
    });
 
  } catch (err) {
    await client.query('ROLLBACK');
    console.error('[Refund] Error processing line item refund:', err);
    
    if (err.type === 'StripeInvalidRequestError') {
      return res.status(400).json({ 
        error: 'Stripe refund failed',
        message: err.message 
      });
    }
    
    res.status(500).json({ 
      error: 'Failed to process refund',
      message: err.message 
    });
  } finally {
    client.release();
  }
});
 
/**
 * @api {get} /invoices/:id/refunds List Invoice Refunds
 * @apiName ListRefunds
 * @apiGroup Refunds
 * @apiDescription Lists all refunds for a specific invoice, including full and partial refunds.
 *
 * @apiHeader {string} Authorization Bearer JWT token
 * @apiParam {number} id Invoice ID (URL parameter)
 *
 * @apiSuccess {Object[]} refunds Array of refund records
 * @apiSuccess {number} refunds.refund_id Refund ID
 * @apiSuccess {string} refunds.refund_type Type (full, partial, line_item)
 * @apiSuccess {number} refunds.amount Amount in cents
 * @apiSuccess {string} refunds.currency Currency code
 * @apiSuccess {string} refunds.status Status (pending, succeeded, failed, canceled)
 * @apiSuccess {string} refunds.reason Refund reason
 * @apiSuccess {string} refunds.notes Internal notes
 * @apiSuccess {string} refunds.created_at Timestamp
 * @apiSuccess {string} refunds.processed_by_name User who processed refund
 *
 * @apiError {number} 404 Invoice not found
 */
router.get('/:id/refunds', authenticateToken, async (req, res) => {
  const invoiceId = req.params.id;
  
  try {
    // Get invoice with tenant filtering
    const { clause, params } = getTenantFilter(req, 'c');
    const invoiceCheck = await pool.query(`
      SELECT i.invoice_id
      FROM invoices i
      LEFT JOIN customers c ONi.customer_id = c.customer_id
      WHERE i.invoice_id = $1 ${clause ? 'AND ' + clause : ''}
    `, [invoiceId, ...params]);
 
    if (invoiceCheck.rows.length === 0) {
      return res.status(404).json({ error: 'Invoice not found' });
    }
 
    // Get all refunds for this invoice
    const refundsResult = await pool.query(`
      SELECT 
        r.*,
        u.name as processed_by_name,
        u.email as processed_by_email
      FROM refunds r
      LEFT JOIN users u ON r.created_by = u.user_id
      WHERE r.invoice_id = $1
      ORDER BY r.created_at DESC
    `, [invoiceId]);
 
    res.json({ refunds: refundsResult.rows });
    
  } catch (err) {
    console.error('[Refund] Error listing refunds:', err);
    res.status(500).json({ error: 'Failed to retrieve refunds' });
  }
});
 
module.exports = router;