invoices.js

Go to the documentation of this file.

/**
 * @file routes/invoices.js
 * @module routes/invoices
 * @description Invoice management API endpoints with comprehensive billing features. Handles
 * invoice CRUD operations, line items with product catalog integration, automatic stock
 * management, payment tracking, and void/delete operations with proper audit trails.
 * 
 * **Core Features:**
 * - Invoice lifecycle management (draft → issued → paid/void)
 * - Line items with product catalog integration
 * - Automatic stock deduction on invoice creation
 * - Stock adjustments on invoice updates
 * - Restocking on void/delete operations
 * - Low stock notifications (min_stock threshold)
 * - Multi-tenant data isolation
 * - Advanced search (invoice number, customer, amount, fuzzy matching)
 * - Date range and payment status filtering
 * - Pagination for large datasets
 * 
 * **Invoice Workflow:**
 * 1. Draft Creation - Create invoice with items (POST /)
 * 2. Stock Deduction - Products automatically decremented
 * 3. Min Stock Check - Notifications if below threshold
 * 4. Issue Invoice - Update status to 'issued'
 * 5. Payment - Update payment_status to 'paid'
 * 6. Void (if needed) - Restock items, mark void with audit trail
 * 
 * **Stock Management:**
 * - Products with is_stock=true trigger stock adjustments
 * - Invoice item quantity reduces stock on creation
 * - Invoice updates compute delta and adjust accordingly
 * - Void/delete operations restore stock levels
 * - Min stock notifications prevent stockouts
 * 
 * **Invoice Number Format:**
 * - Generated format: INV-000123 (zero-padded 6 digits)
 * - Searchable by full format, numeric ID, or fuzzy text
 * - Computed from invoice_id for consistency
 * 
 * **Security:**
 * - All routes require authentication (authenticateToken)
 * - Multi-tenant filtering on all queries
 * - Admin-only for delete operations
 * - Void requires ownership validation
 * - Transaction-based operations prevent data corruption
 * 
 * **Related Models:**
 * - invoices table (invoice_id, customer_id, tenant_id, amount, status, payment_status)
 * - invoice_items table (item_id, invoice_id, product_id, quantity, unit_price, total)
 * - products table (product_id, stock_quantity, min_stock, is_stock)
 * - customers table (customer_id, name, tenant_id)
 * - notifications table (stock alerts)
 * @requires express
 * @requires ../services/db
 * @requires ../middleware/auth
 * @requires ../middleware/adminOnly
 * @requires ../middleware/tenant
 * @author IBG MSP Development Team
 * @date 2024-03-11
 * @since 1.0.0
 * @see {@link module:routes/contracts} for recurring billing
 * @see {@link module:routes/products} for product catalog
 * @see {@link module:routes/customers} for customer management
 */
 
const express = require('express');
const router = express.Router();
const crypto = require('crypto');
const pool = require('../services/db');
const authenticateToken = require('../middleware/auth');
const requireAdmin = require('../middleware/adminOnly');
const { getTenantFilter, setTenantContext } = require('../middleware/tenant');
 
// Apply authentication and tenant context to all routes
router.use(authenticateToken, setTenantContext);
 
/**
 * @api {get} /invoices List Invoices
 * @apiName GetInvoices
 * @apiGroup Invoices
 * @apiDescription Retrieves paginated list of invoices with comprehensive search and filtering
 * capabilities. Supports fuzzy search on invoice numbers (INV-000123), customer names, amounts,
 * and descriptions. Date range filtering and payment status filtering available.
 * 
 * **Search Capabilities:**
 * - Invoice number: "INV-000123", "123", "000123" (all match invoice_id 123)
 * - Customer name: Fuzzy ILIKE search
 * - Amount: Partial numeric match
 * - Currency, status, description: Fuzzy matching
 * - Exact invoice_id match for numeric input
 * 
 * **Tenant Filtering:**
 * Automatically filters invoices by authenticated user's tenant. Root MSP users see all
 * tenant invoices. Regular users see only their tenant's invoices.
 * 
 * **Response Format:**
 * Each invoice includes customer_name and tenant_name from joined tables for display.
 * Total count provided for pagination UI.
 * @apiHeader {string} Authorization Bearer JWT token
 * @apiParam {number} [page=1] Page number for pagination
 * @apiParam {number} [limit=10] Items per page (max recommended: 100)
 * @apiParam {string} [search] Search term (invoice number, customer, amount, description)
 * @apiParam {string} [date_from] Filter invoices issued on or after this date (YYYY-MM-DD)
 * @apiParam {string} [date_to] Filter invoices issued on or before this date (YYYY-MM-DD)
 * @apiParam {string} [payment_status] Filter by payment status: 'paid', 'pending', 'overdue', or 'all'
 * @apiSuccess {Object[]} invoices Array of invoice objects
 * @apiSuccess {number} invoices.invoice_id Unique invoice ID
 * @apiSuccess {number} invoices.customer_id Associated customer ID
 * @apiSuccess {number} invoices.tenant_id Associated tenant ID
 * @apiSuccess {number} invoices.amount Total invoice amount
 * @apiSuccess {string} invoices.currency Currency code (USD, EUR, etc.)
 * @apiSuccess {string} invoices.status Invoice status (draft, issued, void)
 * @apiSuccess {string} invoices.payment_status Payment status (pending, paid, overdue)
 * @apiSuccess {string} invoices.issued_date Date invoice was issued (ISO 8601)
 * @apiSuccess {string} invoices.due_date Payment due date (ISO 8601)
 * @apiSuccess {string} invoices.description Invoice description/notes
 * @apiSuccess {String} invoices.customer_name Customer name (joined)
 * @apiSuccess {String} invoices.tenant_name Tenant name (joined)
 * @apiSuccess {Number} total Total count of invoices matching filters (for pagination)
 * 
 * @apiError {Number} 500 Server error during invoice retrieval
 * 
 * @apiExample {curl} Example Request (Search):
 *   curl -X GET \
 *        -H "Authorization: Bearer eyJhbGc..." \
 *        "https://api.everydaytech.au/invoices?search=INV-000123&page=1&limit=20"
 * 
 * @apiExample {curl} Example Request (Date Range):
 *   curl -X GET \
 *        -H "Authorization: Bearer eyJhbGc..." \
 *        "https://api.everydaytech.au/invoices?date_from=2024-01-01&date_to=2024-12-31&payment_status=paid"
 * 
 * @apiExample {json} Success Response:
 *   HTTP/1.1 200 OK
 *   {
 *     "invoices": [
 *       {
 *         "invoice_id": 123,
 *         "customer_id": 45,
 *         "tenant_id": 5,
 *         "amount": 1299.00,
 *         "currency": "USD",
 *         "status": "issued",
 *         "payment_status": "pending",
 *         "issued_date": "2024-03-01T00:00:00.000Z",
 *         "due_date": "2024-03-31T23:59:59.000Z",
 *         "description": "Monthly managed services",
 *         "customer_name": "Acme Corporation",
 *         "tenant_name": "MSP Alpha"
 *       }
 *     ],
 *     "total": 156
 *   }
 * 
 * @since 1.0.0
 * @see {@link module:routes/invoices.getInvoice} for retrieving single invoice with items
 * @see {@link module:routes/invoices.createInvoice} for creating invoices
 */
router.get('/', async (req, res) => {
  const page = parseInt(req.query.page, 10) || 1;
  const limit = parseInt(req.query.limit, 10) || 10;
  const offset = (page - 1) * limit;
  const search = req.query.search;
  const dateFrom = req.query.date_from;
  const dateTo = req.query.date_to;
  const paymentStatus = req.query.payment_status;
 
  try {
    const { clause: tenantClause, params: tenantParams, nextParamIndex } = getTenantFilter(req, 'i');
    
    let query = `
      SELECT i.*, c.name as customer_name, t.name AS tenant_name
      FROM invoices i 
      LEFT JOIN customers c ON i.customer_id = c.customer_id AND i.tenant_id = c.tenant_id
      LEFT JOIN tenants t ON i.tenant_id = t.tenant_id
    `;
    let countQuery = 'SELECT COUNT(*) FROM invoices i LEFT JOIN customers c ON i.customer_id = c.customer_id AND i.tenant_id = c.tenant_id';
    const params = [...tenantParams];
    let where = tenantClause ? `WHERE ${tenantClause}` : '';
    let paramIndex = nextParamIndex;
 
    // Robust search handling: match invoice_number (INV-000123), invoice_id, or generic text
    if (search && String(search).trim().length > 0) {
      const raw = String(search).trim();
      let clauses = [];
 
      // Generic fuzzy param
      params.push(`%${raw}%`);
      const fuzzyIdx = paramIndex++;
      clauses.push(
        `c.name ILIKE $${fuzzyIdx}`,
        `i.description ILIKE $${fuzzyIdx}`,
        `i.currency ILIKE $${fuzzyIdx}`,
        `i.status ILIKE $${fuzzyIdx}`,
        `CONCAT('INV-', LPAD(CAST(i.invoice_id AS TEXT), 6, '0')) ILIKE $${fuzzyIdx}`,
        `CAST(i.amount AS TEXT) LIKE $${fuzzyIdx}`
      );
 
      // If looks like INV-000123, try matching invoice_id exactly too
      const invMatch = raw.match(/^inv[\-\s]*0*(\d+)$/i);
      if (invMatch) {
        const idVal = parseInt(invMatch[1], 10);
        if (!Number.isNaN(idVal)) {
          params.push(idVal);
          const idIdx = paramIndex++;
          clauses.push(`i.invoice_id = $${idIdx}`);
        }
      } else if (/^\d+$/.test(raw)) {
        // Pure numeric -> also try invoice_id exact
        const idVal = parseInt(raw, 10);
        params.push(idVal);
        const idIdx = paramIndex++;
        clauses.push(`i.invoice_id = $${idIdx}`);
      }
 
      const searchClause = `(${clauses.join(' OR ')})`;
      where += where ? ` AND ${searchClause}` : `WHERE ${searchClause}`;
    }
 
    if (dateFrom) {
      params.push(dateFrom);
      where += where ? ` AND i.issued_date >= $${paramIndex}` : `WHERE i.issued_date >= $${paramIndex}`;
      paramIndex++;
    }
 
    if (dateTo) {
      params.push(dateTo);
      where += where ? ` AND i.issued_date <= $${paramIndex}` : `WHERE i.issued_date <= $${paramIndex}`;
      paramIndex++;
    }
 
    if (paymentStatus && paymentStatus !== 'all') {
      params.push(paymentStatus);
      where += where ? ` AND i.payment_status = $${paramIndex}` : `WHERE i.payment_status = $${paramIndex}`;
      paramIndex++;
    }
 
    if (where) {
      query += ` ${where}`;
      countQuery += ` ${where}`;
    }
 
    query += ` ORDER BY i.issued_date DESC LIMIT $${paramIndex} OFFSET $${paramIndex + 1}`;
    params.push(limit, offset);
 
    // For count query, use all params except the trailing LIMIT/OFFSET
    const countParams = params.slice(0, -2);
    // Debug logs for search behavior
    console.log('[Invoices] Query WHERE:', where);
    console.log('[Invoices] Count params:', countParams);
    console.log('[Invoices] Query params:', params);
 
    const [totalRes, result] = await Promise.all([
      pool.query(countQuery, countParams),
      pool.query(query, params)
    ]);
 
    res.json({ invoices: result.rows, total: parseInt(totalRes.rows[0].count, 10) });
  } catch (err) {
    console.error('Error fetching invoices:', err);
    res.status(500).json({ error: 'Server error', details: err.message });
  }
});
 
/**
 * @api {post} /invoices Create Invoice
 * @apiName CreateInvoice
 * @apiGroup Invoices
 * @apiDescription Creates a new invoice with line items and automatic stock management. Validates
 * all items have positive quantities and non-negative prices. Calculates totals automatically if
 * not provided. Decrements stock for products with is_stock=true. Generates low stock notifications
 * when inventory falls below min_stock threshold.
 * 
 * **Stock Management:**
 * - Products with is_stock=true: Stock reduced by item quantity
 * - Insufficient stock: Stock set to 0 with warning message
 * - Min stock check: Notification created if new stock < min_stock
 * - Non-stock products (services): No stock adjustments
 * 
 * **Validation:**
 * - Must include at least one item
 * - All quantities must be > 0
 * - All unit prices must be >= 0
 * - Product IDs must exist in products table
 * 
 * **Calculation:**
 * - Subtotal = sum(quantity * unit_price) for all items
 * - Total defaults to subtotal (can include tax/discounts in future)
 * - Line totals rounded to 2 decimal places
 * 
 * **Transaction Safety:**
 * All operations in database transaction. If any step fails (invalid product, stock issues),
 * entire invoice creation rolled back.
 * @apiHeader {string} Authorization Bearer JWT token
 * @apiHeader {string} Content-Type application/json
 * @apiParam {number} customer_id Customer ID (required)
 * @apiParam {string} [currency=USD] Currency code
 * @apiParam {string} [status=draft] Invoice status (draft, issued)
 * @apiParam {string} [issued_date] Issue date (ISO 8601, defaults to NULL)
 * @apiParam {string} [due_date] Due date (ISO 8601, defaults to NULL)
 * @apiParam {string} [description] Invoice description/notes
 * @apiParam {number} [subtotal] Subtotal before tax/discounts (calculated if omitted)
 * @apiParam {number} [tax_total=0] Total tax amount
 * @apiParam {number} [total] Final total (defaults to calculated subtotal)
 * @apiParam {Object[]} items Array of line items (required, min 1)
 * @apiParam {number} [items.product_id] Product ID (NULL for custom items)
 * @apiParam {string} items.description Line item description (required)
 * @apiParam {number} items.quantity Quantity (required, must be > 0)
 * @apiParam {number} items.unit_price Unit price (required, must be >= 0)
 * @apiSuccess {Object} invoice Created invoice object
 * @apiSuccess {number} invoice.invoice_id Generated invoice ID
 * @apiSuccess {Object[]} invoice.items Array of created line items
 * @apiSuccess {string[]} warnings Array of warning messages (e.g., insufficient stock)
 * 
 * @apiError {Number} 400 Invoice must include at least one item
 * @apiError {Number} 400 All items must have quantity > 0
 * @apiError {Number} 400 Unit price must be >= 0
 * @apiError {Number} 400 Product id {id} not found
 * @apiError {Number} 409 Duplicate constraint violation
 * @apiError {Number} 500 Failed to create invoice
 * 
 * @apiExample {curl} Example Request:
 *   curl -X POST \
 *        -H "Authorization: Bearer eyJhbGc..." \
 *        -H "Content-Type: application/json" \
 *        -d '{
 *          "customer_id": 45,
 *          "currency": "USD",
 *          "status": "issued",
 *          "issued_date": "2024-03-01",
 *          "due_date": "2024-03-31",
 *          "description": "Monthly managed services",
 *          "items": [
 *            {
 *              "product_id": 15,
 *              "description": "Managed Endpoint Protection",
 *              "quantity": 50,
 *              "unit_price": 5.99
 *            },
 *            {
 *              "product_id": null,
 *              "description": "One-time setup fee",
 *              "quantity": 1,
 *              "unit_price": 499.00
 *            }
 *          ]
 *        }' \
 *        "https://api.everydaytech.au/invoices"
 * 
 * @apiExample {json} Success Response:
 *   HTTP/1.1 201 Created
 *   {
 *     "invoice": {
 *       "invoice_id": 123,
 *       "customer_id": 45,
 *       "tenant_id": 5,
 *       "amount": 798.50,
 *       "currency": "USD",
 *       "status": "issued",
 *       "issued_date": "2024-03-01T00:00:00.000Z",
 *       "due_date": "2024-03-31T23:59:59.000Z",
 *       "items": [
 *         {
 *           "item_id": 301,
 *           "invoice_id": 123,
 *           "product_id": 15,
 *           "description": "Managed Endpoint Protection",
 *           "quantity": 50,
 *           "unit_price": 5.99,
 *           "total": 299.50
 *         },
 *         {
 *           "item_id": 302,
 *           "invoice_id": 123,
 *           "product_id": null,
 *           "description": "One-time setup fee",
 *           "quantity": 1,
 *           "unit_price": 499.00,
 *           "total": 499.00
 *         }
 *       ]
 *     },
 *     "warnings": []
 *   }
 * 
 * @apiExample {json} Success Response (With Stock Warning):
 *   HTTP/1.1 201 Created
 *   {
 *     "invoice": { ... },
 *     "warnings": [
 *       "Product Managed Endpoint Protection had insufficient stock (30), set to 0"
 *     ]
 *   }
 * 
 * @since 1.0.0
 * @see {@link module:routes/products} for product catalog
 * @see {@link module:routes/invoices.updateInvoice} for updating invoices
 */
router.post('/', async (req, res) => {
  const { customer_id, currency = 'AUD', status = 'draft', issued_date = null, due_date = null, description = null, subtotal = 0, tax_rate = 10.00, tax_total = 0, total = 0, items = [] } = req.body;
 
  const client = await pool.connect();
  const warnings = [];
  try {
    // basic validation
    if (!Array.isArray(items) || items.length === 0) return res.status(400).json({ error: 'Invoice must include at least one item' });
    for (const it of items) {
      if (Number(it.quantity) <= 0) return res.status(400).json({ error: 'All items must have quantity > 0' });
      if (Number(it.unit_price) < 0) return res.status(400).json({ error: 'Unit price must be >= 0' });
    }
 
    await client.query('BEGIN');
 
    // calculate totals if not provided
    let calculatedSubtotal = 0;
    for (const it of items) {
      const qty = Number(it.quantity || 0);
      const unit = Number(it.unit_price || 0);
      const lineSubtotal = Number((qty * unit).toFixed(2));
      calculatedSubtotal += lineSubtotal;
    }
 
    const finalSubtotal = subtotal || calculatedSubtotal;
    // Calculate GST (10% for AUD)
    const taxRateDecimal = Number(tax_rate) / 100;
    const finalTaxAmount = Number((finalSubtotal * taxRateDecimal).toFixed(2));
    const finalTotal = total || Number((finalSubtotal + finalTaxAmount).toFixed(2));
 
    // Generate payment token for public payment links
    const paymentToken = crypto.randomBytes(32).toString('hex');
 
    // insert invoice and persist tenant_id if available
    const tenantId = req.tenant?.id || null;
    console.log('[Invoices][POST] tenant:', req.tenant, 'customer_id:', customer_id, 'items:', items.length, 'subtotal:', finalSubtotal, 'tax:', finalTaxAmount, 'total:', finalTotal);
    const invRes = await client.query(
      `INSERT INTO invoices (customer_id, tenant_id, amount, currency, status, issued_date, due_date, description, subtotal, tax_rate, tax_amount, total, payment_token)
       VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13) RETURNING *`,
      [customer_id, tenantId, finalTotal, currency, status, issued_date, due_date, description, finalSubtotal, tax_rate, finalTaxAmount, finalTotal, paymentToken]
    );
    const invoice = invRes.rows[0];
 
    // insert items and decrement stock if needed
    for (const it of items) {
      const qty = Number(it.quantity || 0);
      const unit = Number(it.unit_price || 0);
      const lineTotal = Number((qty * unit).toFixed(2));
 
      // validate product if provided
      if (it.product_id) {
        const pRes = await client.query('SELECT product_id, name, is_stock, stock_quantity, min_stock FROM products WHERE product_id = $1', [it.product_id]);
        if (pRes.rows.length === 0) {
          await client.query('ROLLBACK');
          return res.status(400).json({ error: `Product id ${it.product_id} not found` });
        }
        const p = pRes.rows[0];
        // only adjust stock for stock-managed products
        if (p.is_stock) {
          const newStock = Math.max(0, Number(p.stock_quantity) - qty);
          if (Number(p.stock_quantity) < qty) {
            warnings.push(`Product ${p.name} had insufficient stock (${p.stock_quantity}), set to 0`);
          }
          await client.query('UPDATE products SET stock_quantity = $1 WHERE product_id = $2', [newStock, it.product_id]);
          // check min_stock
          if (p.min_stock !== null && newStock < Number(p.min_stock)) {
            await client.query('INSERT INTO notifications (user_id, title, message, type) VALUES ($1,$2,$3,$4)', [null, `Low stock: ${p.name}`, `Stock for product ${p.name} is low (${newStock} < ${p.min_stock})`, 'stock']);
          }
        }
      }
 
      await client.query(
        `INSERT INTO invoice_items (invoice_id, product_id, description, quantity, unit_price, total) VALUES ($1,$2,$3,$4,$5,$6)`,
        [invoice.invoice_id, it.product_id || null, it.description || null, qty, unit, lineTotal]
      );
    }
 
    await client.query('COMMIT');
 
    // fetch items to return
    const itemsRes = await pool.query('SELECT * FROM invoice_items WHERE invoice_id = $1', [invoice.invoice_id]);
    invoice.items = itemsRes.rows;
    const payload = { invoice, warnings };
    res.status(201).json(payload);
  } catch (err) {
    await client.query('ROLLBACK');
    console.error('Error creating invoice with items:', err);
    if (err && err.code === '23505') {
      return res.status(409).json({ error: 'Duplicate constraint', details: err.detail || err.message });
    }
    res.status(500).json({ error: 'Failed to create invoice', details: err.message });
  } finally {
    client.release();
  }
});
 
/**
 * @api {get} /invoices/:id Get Invoice
 * @apiName GetInvoice
 * @apiGroup Invoices
 * @apiDescription Retrieves a single invoice by ID with all line items included. Enforces
 * tenant isolation through customer relationship to support legacy invoices with NULL tenant_id.
 * 
 * **Line Items:**
 * Each invoice includes an items array with product details, quantities, unit prices, and
 * line totals. Products may be stock-managed or non-stock (services, one-time items).
 * 
 * **Tenant Validation:**
 * Invoice must belong to customer within user's tenant scope. Root MSP users can access
 * all invoices. Regular users limited to their tenant.
 * @apiHeader {string} Authorization Bearer JWT token
 * @apiParam {number} id Invoice ID (in URL path)
 * @apiSuccess {number} invoice_id Unique invoice ID
 * @apiSuccess {number} customer_id Associated customer ID
 * @apiSuccess {number} tenant_id Associated tenant ID (may be NULL for legacy invoices)
 * @apiSuccess {number} amount Total invoice amount
 * @apiSuccess {string} currency Currency code (USD, EUR, etc.)
 * @apiSuccess {string} status Invoice status (draft, issued, void)
 * @apiSuccess {string} payment_status Payment status (pending, paid, overdue)
 * @apiSuccess {string} issued_date Date invoice was issued (ISO 8601)
 * @apiSuccess {string} due_date Payment due date (ISO 8601)
 * @apiSuccess {string} description Invoice description/notes
 * @apiSuccess {string} created_at Timestamp when invoice was created
 * @apiSuccess {string} updated_at Last update timestamp
 * @apiSuccess {Object[]} items Array of invoice line items
 * @apiSuccess {number} items.item_id Unique line item ID
 * @apiSuccess {number} items.invoice_id Parent invoice ID
 * @apiSuccess {number} items.product_id Product ID (NULL for custom items)
 * @apiSuccess {String} items.description Line item description
 * @apiSuccess {Number} items.quantity Quantity ordered
 * @apiSuccess {Number} items.unit_price Price per unit
 * @apiSuccess {Number} items.total Line total (quantity * unit_price)
 * 
 * @apiError {Number} 404 Invoice not found (or not in user's tenant)
 * @apiError {Number} 500 Server error during retrieval
 * 
 * @apiExample {curl} Example Request:
 *   curl -X GET \
 *        -H "Authorization: Bearer eyJhbGc..." \
 *        "https://api.everydaytech.au/invoices/123"
 * 
 * @apiExample {json} Success Response:
 *   HTTP/1.1 200 OK
 *   {
 *     "invoice_id": 123,
 *     "customer_id": 45,
 *     "tenant_id": 5,
 *     "amount": 1299.00,
 *     "currency": "USD",
 *     "status": "issued",
 *     "payment_status": "pending",
 *     "issued_date": "2024-03-01T00:00:00.000Z",
 *     "due_date": "2024-03-31T23:59:59.000Z",
 *     "description": "Monthly managed services",
 *     "created_at": "2024-03-01T10:30:00.000Z",
 *     "updated_at": "2024-03-01T10:30:00.000Z",
 *     "items": [
 *       {
 *         "item_id": 301,
 *         "invoice_id": 123,
 *         "product_id": 15,
 *         "description": "Managed Endpoint Protection",
 *         "quantity": 50,
 *         "unit_price": 5.99,
 *         "total": 299.50
 *       },
 *       {
 *         "item_id": 302,
 *         "invoice_id": 123,
 *         "product_id": 22,
 *         "description": "Network Monitoring Service",
 *         "quantity": 1,
 *         "unit_price": 999.50,
 *         "total": 999.50
 *       }
 *     ]
 *   }
 * 
 * @since 1.0.0
 * @see {@link module:routes/invoices.createInvoice} for creating invoices
 * @see {@link module:routes/invoices.updateInvoice} for updating invoices
 */
router.get('/:id', async (req, res) => {
  const id = req.params.id;
  try {
    // Enforce tenant scope using customer tenant (covers older invoices with NULL tenant_id)
    const { clause, params } = getTenantFilter(req, 'c');
    const baseParams = [id, ...params];
    const where = clause ? `AND ${clause}` : '';
 
    const result = await pool.query(`
      SELECT i.*
      FROM invoices i
      LEFT JOIN customers c ON i.customer_id = c.customer_id
      WHERE i.invoice_id = $1 ${where}
    `, baseParams);
  if (result.rows.length === 0) return res.status(404).json({ error: 'Invoice not found' });
    const invoice = result.rows[0];
    const itemsRes = await pool.query('SELECT * FROM invoice_items WHERE invoice_id = $1', [id]);
    invoice.items = itemsRes.rows;
    res.json(invoice);
  } catch (err) {
    console.error('Error fetching invoice:', err);
    res.status(500).json({ error: 'Server error', details: err.message });
  }
});
 
/**
 * @api {put} /invoices/:id Update Invoice
 * @apiName UpdateInvoice
 * @apiGroup Invoices
 * @apiDescription Updates an invoice with optional line items replacement. Supports two update modes:
 * 1. **Full items replacement** (when items array provided): Replaces all line items, calculates
 *    stock deltas, adjusts inventory accordingly, updates totals
 * 2. **Field updates only** (no items array): Updates invoice metadata fields only
 * 
 * **Stock Delta Calculation:**
 * When items provided, system computes per-product changes:
 * - Existing quantity: Sum of all existing items for each product
 * - New quantity: Sum of all new items for each product
 * - Delta = new - existing (positive reduces stock, negative restocks)
 * - Stock-managed products (is_stock=true) updated accordingly
 * 
 * **Min Stock Notifications:**
 * If stock falls below min_stock threshold after update, notification created for low stock alert.
 * 
 * **Tenant Security:**
 * Invoice must belong to user's tenant. Tenant filter applied on both validation and update.
 * 
 * **Transaction Safety:**
 * All operations in transaction. Rollback on any error (invalid product, constraint violations).
 * @apiHeader {string} Authorization Bearer JWT token
 * @apiHeader {string} Content-Type application/json
 * @apiParam {number} id Invoice ID (in URL path)
 * @apiParam {object[]} [items] Array of line items (if provided, replaces all existing items)
 * @apiParam {number} [items.product_id] Product ID (NULL for custom items)
 * @apiParam {string} items.description Line item description
 * @apiParam {number} items.quantity Quantity (must be > 0)
 * @apiParam {number} items.unit_price Unit price (must be >= 0)
 * @apiParam {string} [currency] Currency code
 * @apiParam {string} [status] Invoice status (draft, issued, void)
 * @apiParam {string} [payment_status] Payment status (pending, paid, overdue)
 * @apiParam {string} [issued_date] Issue date (ISO 8601)
 * @apiParam {string} [due_date] Due date (ISO 8601)
 * @apiParam {string} [description] Invoice description/notes
 * @apiParam {number} [amount] Total amount (auto-calculated when items provided)
 * @apiSuccess {Object} invoice Updated invoice object
 * @apiSuccess {Object[]} [invoice.items] Line items array (included when items were updated)
 * @apiSuccess {string[]} [warnings] Array of warning messages (stock issues, etc.)
 * @apiError {Number} 400 Product id {id} not found
 * @apiError {Number} 400 No fields to update (when no items and no field changes)
 * @apiError {Number} 404 Invoice not found (or not in user's tenant)
 * @apiError {Number} 500 Failed to update invoice
 * 
 * @apiExample {curl} Example Request (Update Items):
 *   curl -X PUT \
 *        -H "Authorization: Bearer eyJhbGc..." \
 *        -H "Content-Type: application/json" \
 *        -d '{
 *          "status": "issued",
 *          "items": [
 *            {
 *              "product_id": 15,
 *              "description": "Managed Endpoint Protection",
 *              "quantity": 75,
 *              "unit_price": 5.99
 *            }
 *          ]
 *        }' \
 *        "https://api.everydaytech.au/invoices/123"
 * 
 * @apiExample {curl} Example Request (Update Fields Only):
 *   curl -X PUT \
 *        -H "Authorization: Bearer eyJhbGc..." \
 *        -H "Content-Type: application/json" \
 *        -d '{
 *          "status": "issued",
 *          "payment_status": "paid",
 *          "description": "Monthly services - paid via check"
 *        }' \
 *        "https://api.everydaytech.au/invoices/123"
 * 
 * @apiExample {json} Success Response (With Items):
 *   HTTP/1.1 200 OK
 *   {
 *     "invoice": {
 *       "invoice_id": 123,
 *       "customer_id": 45,
 *       "tenant_id": 5,
 *       "amount": 449.25,
 *       "currency": "USD",
 *       "status": "issued",
 *       "updated_at": "2024-03-11T15:30:00.000Z",
 *       "items": [
 *         {
 *           "item_id": 401,
 *           "invoice_id": 123,
 *           "product_id": 15,
 *           "description": "Managed Endpoint Protection",
 *           "quantity": 75,
 *           "unit_price": 5.99,
 *           "total": 449.25
 *         }
 *       ]
 *     },
 *     "warnings": []
 *   }
 * 
 * @apiExample {json} Success Response (Fields Only):
 *   HTTP/1.1 200 OK
 *   {
 *     "invoice_id": 123,
 *     "status": "issued",
 *     "payment_status": "paid",
 *     "description": "Monthly services - paid via check",
 *     "updated_at": "2024-03-11T15:30:00.000Z"
 *   }
 * 
 * @since 1.0.0
 * @see {@link module:routes/invoices.createInvoice} for creating invoices
 * @see {@link module:routes/invoices.voidInvoice} for voiding invoices
 */
router.put('/:id', async (req, res) => {
  const id = req.params.id;
  const { items, ...fields } = req.body;
 
  // Get tenant filter for security
  const { clause: tenantClause, params: tenantParams } = getTenantFilter(req, 'inv');
 
  // If items provided, handle full items replace and adjust stock accordingly
  if (Array.isArray(items)) {
    const client = await pool.connect();
    const warnings = [];
    try {
      await client.query('BEGIN');
 
      // First verify the invoice belongs to this tenant
      if (tenantClause) {
        const checkQuery = `SELECT invoice_id FROM invoices inv WHERE invoice_id = $1 AND ${tenantClause}`;
        const checkParams = [id, ...tenantParams];
        const checkRes = await client.query(checkQuery, checkParams);
        if (checkRes.rows.length === 0) {
          await client.query('ROLLBACK');
          client.release();
          return res.status(404).json({ error: 'Invoice not found' });
        }
      }
 
      // load existing items grouped by product_id
      const existingRes = await client.query('SELECT product_id, SUM(quantity) as qty FROM invoice_items WHERE invoice_id = $1 GROUP BY product_id', [id]);
      const existingMap = new Map();
      for (const r of existingRes.rows) {
        existingMap.set(r.product_id ? String(r.product_id) : 'null', Number(r.qty));
      }
 
      // compute new totals per product
      const newMap = new Map();
      let totalAmount = 0;
      for (const it of items) {
        const pidKey = it.product_id ? String(it.product_id) : 'null';
        newMap.set(pidKey, (newMap.get(pidKey) || 0) + Number(it.quantity || 0));
        totalAmount += Number((Number(it.quantity || 0) * Number(it.unit_price || 0)).toFixed(2));
      }
 
      // for each product compute delta = new - existing and adjust stock
      for (const [pidKey, newQty] of newMap.entries()) {
        if (pidKey === 'null') continue;
        const pid = Number(pidKey);
        const existingQty = existingMap.get(pidKey) || 0;
        const delta = newQty - existingQty; // positive means reduce stock
        if (delta === 0) continue;
        const pRes = await client.query('SELECT name, is_stock, stock_quantity, min_stock FROM products WHERE product_id = $1', [pid]);
        if (pRes.rows.length === 0) {
          await client.query('ROLLBACK');
          return res.status(400).json({ error: `Product id ${pid} not found` });
        }
        const p = pRes.rows[0];
        if (p.is_stock) {
          if (delta > 0) {
            const newStock = Math.max(0, Number(p.stock_quantity) - delta);
            if (Number(p.stock_quantity) < delta) warnings.push(`Product ${p.name} had insufficient stock (${p.stock_quantity}), set to 0`);
            await client.query('UPDATE products SET stock_quantity = $1 WHERE product_id = $2', [newStock, pid]);
            if (p.min_stock !== null && newStock < Number(p.min_stock)) {
              await client.query('INSERT INTO notifications (user_id, title, message, type) VALUES ($1,$2,$3,$4)', [null, `Low stock: ${p.name}`, `Stock for product ${p.name} is low (${newStock} < ${p.min_stock})`, 'stock']);
            }
          } else {
            // delta < 0 -> increase stock (restock)
            const inc = Math.abs(delta);
            await client.query('UPDATE products SET stock_quantity = stock_quantity + $1 WHERE product_id = $2', [inc, pid]);
          }
        }
      }
 
      // replace items - simple approach: delete existing and insert new
      await client.query('DELETE FROM invoice_items WHERE invoice_id = $1', [id]);
      for (const it of items) {
        const qty = Number(it.quantity || 0);
        const unit = Number(it.unit_price || 0);
        const lineTotal = Number((qty * unit).toFixed(2));
        await client.query('INSERT INTO invoice_items (invoice_id, product_id, description, quantity, unit_price, total) VALUES ($1,$2,$3,$4,$5,$6)', [id, it.product_id || null, it.description || null, qty, unit, lineTotal]);
      }
 
      // update invoice amount and other fields
      const updateFields = { ...fields };
      
      // Calculate GST: subtotal is the sum of line items, then add 10% GST
      const subtotal = totalAmount;
      const taxRate = updateFields.tax_rate !== undefined ? Number(updateFields.tax_rate) : 10.00;
      const taxAmount = Number((subtotal * (taxRate / 100)).toFixed(2));
      const total = Number((subtotal + taxAmount).toFixed(2));
      
      updateFields.subtotal = subtotal;
      updateFields.tax_rate = taxRate;
      updateFields.tax_amount = taxAmount;
      updateFields.amount = total;
      updateFields.total = total;
      
      const cols = [];
      const vals = [];
      let idx = 1;
      for (const k in updateFields) {
        cols.push(`${k} = $${idx}`);
        vals.push(updateFields[k]);
        idx++;
      }
      
      // Build WHERE clause with tenant filtering
      let whereClause = `invoice_id = $${idx}`;
      vals.push(id);
      idx++;
      
      if (tenantClause) {
        const adjustedClause = tenantClause.replace(/\$(\d+)/g, (match, num) => {
          return `$${idx + parseInt(num) - 1}`;
        });
        whereClause += ` AND ${adjustedClause}`;
        vals.push(...tenantParams);
      }
      
      const sql = `UPDATE invoices inv SET ${cols.join(', ')}, updated_at = CURRENT_TIMESTAMP WHERE ${whereClause} RETURNING *`;
      const updRes = await client.query(sql, vals);
 
      await client.query('COMMIT');
 
      const invoice = updRes.rows[0];
      const itemsRes = await pool.query('SELECT * FROM invoice_items WHERE invoice_id = $1', [id]);
      invoice.items = itemsRes.rows;
      res.json({ invoice, warnings });
    } catch (err) {
      await client.query('ROLLBACK');
      console.error('Error updating invoice with items:', err);
      res.status(500).json({ error: 'Failed to update invoice items', details: err.message });
    } finally {
      client.release();
    }
    return;
  }
 
  // fallback: update invoice fields only
  const fieldsToUpdate = fields;
  const cols = [];
  const vals = [];
  let idx = 1;
  for (const key in fieldsToUpdate) {
    cols.push(`${key} = $${idx}`);
    vals.push(fieldsToUpdate[key]);
    idx++;
  }
  if (cols.length === 0) return res.status(400).json({ error: 'No fields to update' });
  
  // Build WHERE clause with tenant filtering
  let whereClause = `invoice_id = $${idx}`;
  vals.push(id);
  idx++;
  
  if (tenantClause) {
    const adjustedClause = tenantClause.replace(/\$(\d+)/g, (match, num) => {
      return `$${idx + parseInt(num) - 1}`;
    });
    whereClause += ` AND ${adjustedClause}`;
    vals.push(...tenantParams);
  }
  
  const sql = `UPDATE invoices inv SET ${cols.join(', ')} , updated_at = CURRENT_TIMESTAMP WHERE ${whereClause} RETURNING *`;
 
  try {
    const result = await pool.query(sql, vals);
    if (result.rows.length === 0) {
      return res.status(404).json({ error: 'Invoice not found' });
    }
    res.json(result.rows[0]);
  } catch (err) {
    console.error('Error updating invoice:', err);
    res.status(500).json({ error: 'Failed to update invoice', details: err.message });
  }
});
 
// ===== VOID AND DELETE ROUTES =====
 
/**
 * @api {post} /invoices/:id/void Void Invoice
 * @apiName VoidInvoice
 * @apiGroup Invoices
 * @apiDescription Marks invoice as void with audit trail and restocks all line items. Prevents
 * voiding fully paid invoices (use refund flow instead). Idempotent operation - calling on
 * already-void invoice returns success.
 * 
 * **Voiding Process:**
 * 1. Validate invoice exists and belongs to user's tenant
 * 2. Check invoice not fully paid (payment_status != 'paid')
 * 3. Restock all stock-managed products (is_stock=true)
 * 4. Mark invoice status = 'void'
 * 5. Record void_reason, voided_at timestamp, voided_by user
 * 6. Return updated invoice with items
 * 
 * **Stock Restoration:**
 * For each line item with product_id where product.is_stock=true:
 * - Stock quantity increased by item quantity
 * - Reverses stock deduction from invoice creation
 * 
 * **Audit Trail:**
 * System automatically adds audit columns on first void:
 * - void_reason (TEXT): Reason provided or NULL
 * - voided_at (TIMESTAMP): When invoice was voided
 * - voided_by (INTEGER): User ID who performed void
 * 
 * **Idempotency:**
 * If invoice already void, returns current state without error or changes.
 * 
 * **Security:**
 * - Requires authentication
 * - Tenant isolation enforced through customer relationship
 * - Cannot void fully paid invoices (payment_status='paid')
 * @apiHeader {string} Authorization Bearer JWT token
 * @apiHeader {string} Content-Type application/json
 * @apiParam {number} id Invoice ID (in URL path)
 * @apiParam {string} [reason] Reason for voiding (max 1000 chars, optional)
 * @apiSuccess {number} invoice_id Invoice ID
 * @apiSuccess {string} status "void"
 * @apiSuccess {string} void_reason Reason provided or NULL
 * @apiSuccess {string} voided_at Timestamp when voided
 * @apiSuccess {number} voided_by User ID who performed void
 * @apiSuccess {Object[]} items Array of invoice line items
 * @apiError {number} 400 Cannot void a fully paid invoice
 * @apiError {number} 404 Invoice not found (or not in user's tenant)
 * @apiError {number} 500 Failed to void invoice
 * @apiExample {curl} Example Request:
 *   curl -X POST \
 *        -H "Authorization: Bearer eyJhbGc..." \
 *        -H "Content-Type: application/json" \
 *        -d '{"reason": "Customer dispute - incorrect pricing"}' \
 *        "https://api.everydaytech.au/invoices/123/void"
 * @apiExample {json} Success Response:
 *   HTTP/1.1 200 OK
 *   {
 *     "invoice_id": 123,
 *     "customer_id": 45,
 *     "tenant_id": 5,
 *     "amount": 1299.00,
 *     "currency": "USD",
 *     "status": "void",
 *     "payment_status": "pending",
 *     "void_reason": "Customer dispute - incorrect pricing",
 *     "voided_at": "2024-03-11T15:45:00.000Z",
 *     "voided_by": 12,
 *     "items": [
 *       {
 *         "item_id": 301,
 *         "product_id": 15,
 *         "quantity": 50,
 *         "unit_price": 5.99,
 *         "total": 299.50
 *       }
 *     ]
 *   }
 * @apiExample {json} Error Response (Paid Invoice):
 *   HTTP/1.1 400 Bad Request
 *   {
 *     "error": "Cannot void a fully paid invoice"
 *   }
 * @since 1.0.0
 * @see {@link module:routes/invoices.deleteInvoice} for deleting draft/void invoices
 * @see {@link module:routes/invoices.updateInvoice} for updating invoices
 */
router.post('/:id/void', async (req, res) => {
  const id = req.params.id;
  const reason = (req.body && req.body.reason ? String(req.body.reason) : '').slice(0, 1000);
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
 
  // Ensure audit columns exist (Postgres supports ADD COLUMN IF NOT EXISTS)
  await client.query("ALTER TABLE invoices ADD COLUMN IF NOT EXISTS void_reason TEXT");
  await client.query("ALTER TABLE invoices ADD COLUMN IF NOT EXISTS voided_at TIMESTAMP");
  await client.query("ALTER TABLE invoices ADD COLUMN IF NOT EXISTS voided_by INTEGER");
 
    // Enforce tenant scope when loading invoice (via customers to support legacy NULL tenant_id)
    const { clause, params } = getTenantFilter(req, 'c');
    const invSql = `
      SELECT i.*
      FROM invoices i
      LEFT JOIN customers c ON i.customer_id = c.customer_id
      WHERE i.invoice_id = $1 ${clause ? 'AND ' + clause : ''}`;
    const invRes = await client.query(invSql, [id, ...params]);
    if (invRes.rows.length === 0) {
      await client.query('ROLLBACK');
      return res.status(404).json({ error: 'Invoice not found' });
    }
    const invoice = invRes.rows[0];
 
    if (invoice.status === 'void') {
      // Idempotent: already void, just return
      const itemsRes = await client.query('SELECT * FROM invoice_items WHERE invoice_id = $1', [id]);
      invoice.items = itemsRes.rows;
      await client.query('COMMIT');
      return res.json(invoice);
    }
 
    if (invoice.payment_status === 'paid') {
      await client.query('ROLLBACK');
      return res.status(400).json({ error: 'Cannot void a fully paid invoice' });
    }
 
    // Restock items for stock-managed products
    const itemsRes = await client.query('SELECT * FROM invoice_items WHERE invoice_id = $1', [id]);
    for (const it of itemsRes.rows) {
      if (!it.product_id) continue;
      const pRes = await client.query('SELECT product_id, is_stock FROM products WHERE product_id = $1', [it.product_id]);
      if (pRes.rows.length && pRes.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]);
      }
    }
 
    // Mark invoice as void with reason and audit
    const upd = await client.query(
      `UPDATE invoices 
       SET status = 'void', void_reason = $2, voided_at = CURRENT_TIMESTAMP, voided_by = $3, updated_at = CURRENT_TIMESTAMP 
       WHERE invoice_id = $1 RETURNING *`,
      [id, reason || null, req.user?.user_id || null]
    );
    const updated = upd.rows[0];
    updated.items = itemsRes.rows;
 
    await client.query('COMMIT');
    res.json(updated);
  } catch (err) {
    await client.query('ROLLBACK');
    console.error('Error voiding invoice:', err);
    res.status(500).json({ error: 'Failed to void invoice', details: err.message });
  } finally {
    client.release();
  }
});
 
/**
 * @api {delete} /invoices/:id Delete Invoice
 * @apiName DeleteInvoice
 * @apiGroup Invoices
 * @apiDescription Permanently deletes an invoice and all associated line items. Admin only.
 * Only draft or void invoices can be deleted. Paid invoices cannot be deleted (prevents
 * accounting manipulation). Restocks all line items before deletion.
 * 
 * **Deletion Rules:**
 * - Status must be 'draft' or 'void'
 * - Payment status cannot be 'paid'
 * - Admin role required (requireAdmin middleware)
 * - Invoice must belong to user's tenant
 * 
 * **Deletion Process:**
 * 1. Validate invoice exists and belongs to tenant
 * 2. Check payment_status != 'paid'
 * 3. Check status in ['draft', 'void']
 * 4. Restock all stock-managed products
 * 5. Delete all invoice_items records
 * 6. Delete invoice record
 * 7. Return success confirmation
 * 
 * **Stock Restoration:**
 * For each line item with product_id where product.is_stock=true:
 * - Stock quantity increased by item quantity
 * - Ensures no inventory loss from deleted invoices
 * 
 * **Security:**
 * - Admin only (requireAdmin middleware)
 * - Tenant isolation enforced
 * - Transaction-based for data integrity
 * - Cannot delete paid invoices
 * 
 * **Use Cases:**
 * - Remove duplicate draft invoices
 * - Clean up test invoices
 * - Remove voided invoices after accounting period
 * @apiHeader {string} Authorization Bearer JWT token (admin role required)
 * @apiParam {number} id Invoice ID (in URL path)
 * @apiSuccess {boolean} success Always true when successful
 * @apiError {number} 400 Cannot delete a paid invoice
 * @apiError {number} 400 Only 'draft' or 'void' invoices can be deleted
 * @apiError {number} 404 Invoice not found (or not in user's tenant)
 * @apiError {number} 500 Failed to delete invoice
 * @apiExample {curl} Example Request:
 *   curl -X DELETE \
 *        -H "Authorization: Bearer eyJhbGc..." \
 *        "https://api.everydaytech.au/invoices/123"
 * @apiExample {json} Success Response:
 *   HTTP/1.1 200 OK
 *   {
 *     "success": true
 *   }
 * @apiExample {json} Error Response (Paid Invoice):
 *   HTTP/1.1 400 Bad Request
 *   {
 *     "error": "Cannot delete a paid invoice"
 *   }
 * @apiExample {json} Error Response (Wrong Status):
 *   HTTP/1.1 400 Bad Request
 *   {
 *     "error": "Only 'draft' or 'void' invoices can be deleted"
 *   }
 * @since 1.0.0
 * @see {@link module:routes/invoices.voidInvoice} for voiding invoices
 * @see {@link module:routes/invoices.createInvoice} for creating invoices
 */
router.delete('/:id', requireAdmin, async (req, res) => {
  const id = req.params.id;
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
 
    // Enforce tenant scope when loading invoice
    const { clause, params } = getTenantFilter(req, 'c');
    const invSql = `
      SELECT i.*
      FROM invoices i
      LEFT JOIN customers c ON i.customer_id = c.customer_id
      WHERE i.invoice_id = $1 ${clause ? 'AND ' + clause : ''}`;
    const invRes = await client.query(invSql, [id, ...params]);
    if (invRes.rows.length === 0) {
      await client.query('ROLLBACK');
      return res.status(404).json({ error: 'Invoice not found' });
    }
    const invoice = invRes.rows[0];
 
    if (invoice.payment_status === 'paid') {
      await client.query('ROLLBACK');
      return res.status(400).json({ error: 'Cannot delete a paid invoice' });
    }
 
    if (!['draft', 'void'].includes(invoice.status)) {
      await client.query('ROLLBACK');
      return res.status(400).json({ error: "Only 'draft' or 'void' invoices can be deleted" });
    }
 
    // Restock items before delete (in case status was 'draft')
    const itemsRes = await client.query('SELECT * FROM invoice_items WHERE invoice_id = $1', [id]);
    for (const it of itemsRes.rows) {
      if (!it.product_id) continue;
      const pRes = await client.query('SELECT product_id, is_stock FROM products WHERE product_id = $1', [it.product_id]);
      if (pRes.rows.length && pRes.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]);
      }
    }
 
    await client.query('DELETE FROM invoice_items WHERE invoice_id = $1', [id]);
    await client.query('DELETE FROM invoices WHERE invoice_id = $1', [id]);
 
    await client.query('COMMIT');
    res.json({ success: true });
  } catch (err) {
    await client.query('ROLLBACK');
    console.error('Error deleting invoice:', err);
    res.status(500).json({ error: 'Failed to delete invoice', details: err.message });
  } finally {
    client.release();
  }
});
 
module.exports = router;