purchaseOrders.js

Go to the documentation of this file.

/**
 * @file purchaseOrders.js
 * @description Purchase order management for procurement and inventory tracking. Supports PO header/line item CRUD,
 * status tracking, supplier management, and tenant isolation. All routes require authentication and enforce
 * tenant context.
 * @module routes/purchaseOrders
 */
 
/**
 * @apiDefine PurchaseOrderGroup Purchase Orders
 * Purchase order procurement and tracking
 */
 
const express = require('express');
const router = express.Router();
const pool = require('../services/db');
const authenticateToken = require('../middleware/auth');
const { getTenantFilter, setTenantContext } = require('../middleware/tenant');
 
// Apply authentication and tenant context to all routes
router.use(authenticateToken, setTenantContext);
 
/**
 * @api {get} /api/purchase-orders List Purchase Orders
 * @apiName ListPurchaseOrders
 * @apiGroup PurchaseOrderGroup
 * @apiDescription Retrieves paginated list of purchase orders with tenant filtering. Supports search by PO number
 * or supplier name, and status filtering. Returns total count for pagination.
 * @apiParam (Query) {number} [page=1] Page number
 * @apiParam (Query) {number} [limit=25] Results per page
 * @apiParam (Query) {string} [status] Filter by status (draft, pending, approved, ordered, received, cancelled)
 * @apiParam (Query) {string} [search] Search by PO number or supplier (fuzzy match)
 * @apiSuccess {object[]} purchase_orders Array of purchase order objects
 * @apiSuccess {number} purchase_orders.purchase_order_id PO database ID
 * @apiSuccess {string} purchase_orders.po_number PO reference number
 * @apiSuccess {string} purchase_orders.supplier Supplier name
 * @apiSuccess {string} purchase_orders.status PO status
 * @apiSuccess {string} purchase_orders.tenant_name Tenant name
 * @apiSuccess {number} total Total count of matching POs
 * @apiError 500 Server error
 * @apiExample {curl} Example:
 *   curl "https://api.example.com/api/purchase-orders?page=1&limit=25&status=pending" \
 *     -H "Authorization: Bearer YOUR_TOKEN"
 * @apiExample {json} Success-Response (200):
 *   {
 *     "purchase_orders": [{"purchase_order_id": 1, "po_number": "PO-2026-001", "supplier": "Acme Corp", "status": "pending"}],
 *     "total": 50
 *   }
 */
// GET /purchase-orders
router.get('/', async (req, res) => {
  try {
    const { page = 1, limit = 25, status, search } = req.query;
    const offset = (parseInt(page, 10) - 1) * parseInt(limit, 10);
    
    const { clause: tenantClause, params: tenantParams, nextParamIndex } = getTenantFilter(req, 'po');
    const params = [...tenantParams];
    const where = [];
    let paramIndex = nextParamIndex;
    
    if (tenantClause) where.push(tenantClause);
    
    if (status) {
      params.push(status);
      where.push(`po.status = $${paramIndex}`);
      paramIndex++;
    }
    if (search && search.trim()) {
      params.push('%' + search.trim() + '%');
      where.push(`(po.po_number ILIKE $${paramIndex} OR po.supplier ILIKE $${paramIndex})`);
      paramIndex++;
    }
 
    const whereClause = where.length ? 'WHERE ' + where.join(' AND ') : '';
    
    const countRes = await pool.query(`SELECT COUNT(*) FROM purchase_orders po ${whereClause}`, params);
    const total = parseInt(countRes.rows[0].count, 10);
 
    params.push(limit, offset);
    const resQ = await pool.query(
      `SELECT po.*, t.name AS tenant_name FROM purchase_orders po 
       LEFT JOIN tenants t ON po.tenant_id = t.tenant_id
       ${whereClause} ORDER BY po.created_at DESC LIMIT $${paramIndex} OFFSET $${paramIndex + 1}`,
      params
    );
    res.json({ purchase_orders: resQ.rows, total });
  } catch (err) {
    console.error('Error fetching purchase orders:', err);
    res.status(500).json({ error: 'Server error' });
  }
});
 
/**
 * @api {get} /api/purchase-orders/:id Get Purchase Order
 * @apiName GetPurchaseOrder
 * @apiGroup PurchaseOrderGroup
 * @apiDescription Retrieves purchase order header and line items by ID. Enforces tenant isolation.
 * @apiParam {number} id Purchase order ID
 * @apiSuccess {object} purchase_order PO header object
 * @apiSuccess {number} purchase_order.purchase_order_id PO database ID
 * @apiSuccess {string} purchase_order.po_number PO reference number
 * @apiSuccess {string} purchase_order.supplier Supplier name
 * @apiSuccess {string} purchase_order.bill_to Billing address
 * @apiSuccess {string} purchase_order.ship_to Shipping address
 * @apiSuccess {string} purchase_order.internal_notes Internal notes/comments
 * @apiSuccess {string} purchase_order.status PO status
 * @apiSuccess {Object[]} lines Array of line item objects
 * @apiSuccess {number} lines.po_line_id Line item database ID
 * @apiSuccess {number} lines.product_id Product database ID
 * @apiSuccess {string} lines.sku Product SKU
 * @apiSuccess {string} lines.description Line item description
 * @apiSuccess {number} lines.qty_ordered Quantity ordered
 * @apiSuccess {number} lines.unit_price Unit price
 * @apiError 404 PO not found
 * @apiError 500 Server error
 * @apiExample {curl} Example:
 *   curl https://api.example.com/api/purchase-orders/1 \
 *     -H "Authorization: Bearer YOUR_TOKEN"
 */
// GET /purchase-orders/:id
router.get('/:id', async (req, res) => {
  const { id } = req.params;
  try {
    const { clause: tenantClause, params: tenantParams } = getTenantFilter(req, 'po');
    
    let headerQuery = `SELECT po.* FROM purchase_orders po WHERE po.purchase_order_id = $1`;
    let headerQueryParams = [id];
    
    if (tenantClause) {
      // Shift tenant param placeholders by +1 because $1 is already used by purchase_order_id
      const adjustedClause = tenantClause.replace(/\$(\d+)/g, (m, n) => `$${parseInt(n, 10) + 1}`);
      headerQuery += ` AND ${adjustedClause}`;
      headerQueryParams.push(...tenantParams);
    }
    
    const header = await pool.query(headerQuery, headerQueryParams);
    if (header.rows.length === 0) return res.status(404).json({ error: 'Not found' });
 
    let linesQuery = `SELECT pol.* FROM purchase_order_lines pol WHERE pol.purchase_order_id = $1`;
    let linesQueryParams = [id];
    
    if (tenantClause) {
      const tenantLinesClause = tenantClause.replace(/po\./g, 'pol.');
      const adjustedLinesClause = tenantLinesClause.replace(/\$(\d+)/g, (m, n) => `$${parseInt(n, 10) + 1}`);
      linesQuery += ` AND ${adjustedLinesClause}`;
      linesQueryParams.push(...tenantParams);
    }
    
    const lines = await pool.query(linesQuery, linesQueryParams);
    res.json({ purchase_order: header.rows[0], lines: lines.rows });
  } catch (err) {
    console.error('Error fetching purchase order:', err);
    res.status(500).json({ error: 'Server error' });
  }
});
 
/**
 * @api {post} /api/purchase-orders Create Purchase Order
 * @apiName CreatePurchaseOrder
 * @apiGroup PurchaseOrderGroup
 * @apiDescription Creates new purchase order with optional line items in atomic transaction. Default status is draft.
 * @apiParam {string} [po_number] PO reference number (must be unique per tenant)
 * @apiParam {string} [supplier] Supplier name
 * @apiParam {string} [bill_to] Billing address
 * @apiParam {string} [ship_to] Shipping address
 * @apiParam {string} [internal_notes] Internal notes/comments
 * @apiParam {string} [status=draft] PO status
 * @apiParam {object[]} [lines] Array of line items to create
 * @apiParam {number} [lines.product_id] Product database ID
 * @apiParam {string} [lines.sku] Product SKU
 * @apiParam {string} [lines.description] Line description
 * @apiParam {number} [lines.qty_ordered=1] Quantity ordered
 * @apiParam {number} [lines.unit_price=0] Unit price
 * @apiSuccess (201) {Object} purchase_order Created PO header object
 * @apiError 403 No tenant context
 * @apiError 409 PO number already exists
 * @apiError 500 Create failed
 * @apiExample {curl} Example:
 *   curl -X POST https://api.example.com/api/purchase-orders \
 *     -H "Authorization: Bearer YOUR_TOKEN" \
 *     -H "Content-Type: application/json" \
 *     -d '{"po_number":"PO-2026-001", "supplier":"Acme", "lines":[{"sku":"ABC-123", "qty_ordered":10, "unit_price":25.50}]}'
 */
// POST /purchase-orders (create header + optional lines)
router.post('/', async (req, res) => {
  const {
    po_number,
    supplier,
    bill_to,
    ship_to,
    internal_notes,
    status = 'draft',
    lines = []
  } = req.body;
 
  if (!req.tenant?.id) {
    return res.status(403).json({ error: 'No tenant context' });
  }
 
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    const insertHeader = await client.query(
      `INSERT INTO purchase_orders (
        tenant_id, po_number, supplier, bill_to, ship_to, internal_notes, status
      ) VALUES ($1,$2,$3,$4,$5,$6,$7)
      RETURNING *`,
      [req.tenant.id, po_number || null, supplier || null, bill_to || null, ship_to || null, internal_notes || null, status]
    );
 
    const header = insertHeader.rows[0];
 
    for (const line of lines) {
      await client.query(
        `INSERT INTO purchase_order_lines (
          tenant_id, purchase_order_id, product_id, sku, description, qty_ordered, unit_price
        ) VALUES ($1,$2,$3,$4,$5,$6,$7)` ,
        [req.tenant.id, header.purchase_order_id, line.product_id || null, line.sku || null, line.description || null, parseInt(line.qty_ordered || 1, 10), parseFloat(line.unit_price || 0)]
      );
    }
 
    await client.query('COMMIT');
    res.status(201).json(header);
  } catch (err) {
    await client.query('ROLLBACK');
    console.error('Error creating purchase order:', err);
    if (err.code === '23505') {
      return res.status(409).json({ error: 'PO number already exists' });
    }
    res.status(500).json({ error: 'Create failed' });
  } finally {
    client.release();
  }
});
 
/**
 * @api {put} /api/purchase-orders/:id Update Purchase Order Header
 * @apiName UpdatePurchaseOrder
 * @apiGroup PurchaseOrderGroup
 * @apiDescription Updates purchase order header fields. Only provided fields are updated (COALESCE). Enforces tenant isolation.
 * @apiParam {number} id Purchase order ID
 * @apiParam {string} [po_number] PO reference number (must be unique)
 * @apiParam {string} [supplier] Supplier name
 * @apiParam {string} [bill_to] Billing address
 * @apiParam {string} [ship_to] Shipping address
 * @apiParam {string} [internal_notes] Internal notes
 * @apiParam {string} [status] PO status
 * @apiSuccess {object} purchase_order Updated PO header object
 * @apiError 404 PO not found
 * @apiError 409 PO number already exists
 * @apiError 500 Update failed
 * @apiExample {curl} Example:
 *   curl -X PUT https://api.example.com/api/purchase-orders/1 \
 *     -H "Authorization: Bearer YOUR_TOKEN" \
 *     -H "Content-Type: application/json" \
 *     -d '{"status":"approved"}'
 */
// PUT /purchase-orders/:id (update header)
router.put('/:id', async (req, res) => {
  const { id } = req.params;
  const { po_number, supplier, bill_to, ship_to, internal_notes, status } = req.body;
  try {
    const result = await pool.query(
      `UPDATE purchase_orders
       SET po_number = COALESCE($1, po_number),
           supplier = COALESCE($2, supplier),
           bill_to = COALESCE($3, bill_to),
           ship_to = COALESCE($4, ship_to),
           internal_notes = COALESCE($5, internal_notes),
           status = COALESCE($6, status),
           updated_at = NOW()
       WHERE purchase_order_id = $7 AND tenant_id = $8
       RETURNING *`,
      [po_number || null, supplier || null, bill_to || null, ship_to || null, internal_notes || null, status || null, id, req.tenant.id]
    );
    if (result.rows.length === 0) return res.status(404).json({ error: 'Not found' });
    res.json(result.rows[0]);
  } catch (err) {
    console.error('Error updating purchase order:', err);
    if (err.code === '23505') {
      return res.status(409).json({ error: 'PO number already exists' });
    }
    res.status(500).json({ error: 'Update failed' });
  }
});
 
/**
 * @api {post} /api/purchase-orders/:id/lines Add Line Item
 * @apiName AddPurchaseOrderLine
 * @apiGroup PurchaseOrderGroup
 * @apiDescription Adds new line item to existing purchase order. Verifies PO belongs to tenant.
 * @apiParam {number} id Purchase order ID
 * @apiParam {number} [product_id] Product database ID
 * @apiParam {string} [sku] Product SKU
 * @apiParam {string} [description] Line description
 * @apiParam {number} [qty_ordered=1] Quantity ordered
 * @apiParam {number} [unit_price=0] Unit price
 * @apiSuccess (201) {Object} line Created line item object
 * @apiError 404 PO not found
 * @apiError 500 Create failed
 * @apiExample {curl} Example:
 *   curl -X POST https://api.example.com/api/purchase-orders/1/lines \
 *     -H "Authorization: Bearer YOUR_TOKEN" \
 *     -H "Content-Type: application/json" \
 *     -d '{"sku":"XYZ-789", "description":"Widget", "qty_ordered":5, "unit_price":15.00}'
 */
// POST /purchase-orders/:id/lines (add line)
router.post('/:id/lines', async (req, res) => {
  const { id } = req.params;
  const { product_id, sku, description, qty_ordered, unit_price } = req.body;
  try {
    // Ensure header belongs to tenant
    const check = await pool.query(`SELECT 1 FROM purchase_orders WHERE purchase_order_id = $1 AND tenant_id = $2`, [id, req.tenant.id]);
    if (check.rows.length === 0) return res.status(404).json({ error: 'PO not found' });
 
    const result = await pool.query(
      `INSERT INTO purchase_order_lines (tenant_id, purchase_order_id, product_id, sku, description, qty_ordered, unit_price)
       VALUES ($1,$2,$3,$4,$5,$6,$7) RETURNING *`,
      [req.tenant.id, id, product_id || null, sku || null, description || null, parseInt(qty_ordered || 1, 10), parseFloat(unit_price || 0)]
    );
    res.status(201).json(result.rows[0]);
  } catch (err) {
    console.error('Error adding PO line:', err);
    res.status(500).json({ error: 'Create failed' });
  }
});
 
/**
 * @api {put} /api/purchase-orders/:id/lines/:lineId Update Line Item
 * @apiName UpdatePurchaseOrderLine
 * @apiGroup PurchaseOrderGroup
 * @apiDescription Updates existing purchase order line item. Verifies line belongs to PO and tenant.
 * @apiParam {number} id Purchase order ID
 * @apiParam {number} lineId Line item ID
 * @apiParam {number} [product_id] Product database ID
 * @apiParam {string} [sku] Product SKU
 * @apiParam {string} [description] Line description
 * @apiParam {number} [qty_ordered=1] Quantity ordered
 * @apiParam {number} [unit_price=0] Unit price
 * @apiSuccess {object} line Updated line item object
 * @apiError 404 Line not found
 * @apiError 500 Update failed
 * @apiExample {curl} Example:
 *   curl -X PUT https://api.example.com/api/purchase-orders/1/lines/5 \
 *     -H "Authorization: Bearer YOUR_TOKEN" \
 *     -H "Content-Type: application/json" \
 *     -d '{"qty_ordered":10}'
 */
// PUT /purchase-orders/:id/lines/:lineId (update line)
router.put('/:id/lines/:lineId', async (req, res) => {
  const { id, lineId } = req.params;
  const { product_id, sku, description, qty_ordered, unit_price } = req.body;
  try {
    // Ensure line belongs to tenant & header
    const check = await pool.query(`SELECT 1 FROM purchase_order_lines WHERE po_line_id = $1 AND purchase_order_id = $2 AND tenant_id = $3`, [lineId, id, req.tenant.id]);
    if (check.rows.length === 0) return res.status(404).json({ error: 'Line not found' });
 
    const result = await pool.query(
      `UPDATE purchase_order_lines
       SET product_id = $1,
           sku = $2,
           description = $3,
           qty_ordered = $4,
           unit_price = $5,
           updated_at = NOW()
       WHERE po_line_id = $6 AND tenant_id = $7
       RETURNING *`,
      [product_id || null, sku || null, description || null, parseInt(qty_ordered || 1, 10), parseFloat(unit_price || 0), lineId, req.tenant.id]
    );
    res.json(result.rows[0]);
  } catch (err) {
    console.error('Error updating PO line:', err);
    res.status(500).json({ error: 'Update failed' });
  }
});
 
/**
 * @api {delete} /api/purchase-orders/:id/lines/:lineId Delete Line Item
 * @apiName DeletePurchaseOrderLine
 * @apiGroup PurchaseOrderGroup
 * @apiDescription Deletes purchase order line item. Verifies line belongs to PO and tenant.
 * @apiParam {number} id Purchase order ID
 * @apiParam {number} lineId Line item ID
 * @apiSuccess (204) Empty No content (successful deletion)
 * @apiError 404 Line not found
 * @apiError 500 Delete failed
 * @apiExample {curl} Example:
 *   curl -X DELETE https://api.example.com/api/purchase-orders/1/lines/5 \
 *     -H "Authorization: Bearer YOUR_TOKEN"
 */
// DELETE /purchase-orders/:id/lines/:lineId
router.delete('/:id/lines/:lineId', async (req, res) => {
  const { id, lineId } = req.params;
  try {
    const del = await pool.query(`DELETE FROM purchase_order_lines WHERE po_line_id = $1 AND purchase_order_id = $2 AND tenant_id = $3`, [lineId, id, req.tenant.id]);
    if (del.rowCount === 0) return res.status(404).json({ error: 'Line not found' });
    res.sendStatus(204);
  } catch (err) {
    console.error('Error deleting PO line:', err);
    res.status(500).json({ error: 'Delete failed' });
  }
});
 
/**
 * @api {delete} /api/purchase-orders/:id Delete Purchase Order
 * @apiName DeletePurchaseOrder
 * @apiGroup PurchaseOrderGroup
 * @apiDescription Deletes purchase order and all associated line items in atomic transaction. **Requires admin role.**
 * MSP users can delete from any tenant.
 * @apiParam {number} id Purchase order ID
 * @apiSuccess (204) Empty No content (successful deletion)
 * @apiError 403 Admin privileges required
 * @apiError 404 PO not found
 * @apiError 500 Delete failed
 * @apiExample {curl} Example:
 *   curl -X DELETE https://api.example.com/api/purchase-orders/1 \
 *     -H "Authorization: Bearer YOUR_TOKEN"
 */
// DELETE /purchase-orders/:id (admin only)
router.delete('/:id', async (req, res) => {
  const { id } = req.params;
  
  // Restrict delete to admin users only
  if (!req.user || req.user.role !== 'admin') {
    return res.status(403).json({ error: 'Admin privileges required to delete purchase orders' });
  }
  
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    
    // Get tenant filter - MSP users can delete from any tenant
    const { clause: tenantClause, params: tenantParams } = getTenantFilter(req, 'po');
    
    // Build query to verify PO exists
    let checkQuery = `SELECT purchase_order_id, tenant_id FROM purchase_orders po WHERE po.purchase_order_id = $1`;
    let checkParams = [id];
    
    if (tenantClause) {
      checkQuery += ` AND ${tenantClause}`;
      checkParams.push(...tenantParams);
    }
    
    const check = await client.query(checkQuery, checkParams);
    if (check.rows.length === 0) {
      await client.query('ROLLBACK');
      return res.status(404).json({ error: 'Purchase order not found' });
    }
    
    const poTenantId = check.rows[0].tenant_id;
    
    // Delete lines first (foreign key constraint)
    await client.query(
      `DELETE FROM purchase_order_lines WHERE purchase_order_id = $1 AND tenant_id = $2`,
      [id, poTenantId]
    );
    
    // Delete the purchase order
    await client.query(
      `DELETE FROM purchase_orders WHERE purchase_order_id = $1 AND tenant_id = $2`,
      [id, poTenantId]
    );
    
    await client.query('COMMIT');
    res.sendStatus(204);
  } catch (err) {
    await client.query('ROLLBACK');
    console.error('Error deleting purchase order:', err);
    res.status(500).json({ error: 'Delete failed' });
  } finally {
    client.release();
  }
});
 
module.exports = router;