products.js

Go to the documentation of this file.

/**
 * @file routes/products.js
 * @module routes/products
 * @description Product catalog management API for MSP service offerings and inventory. Handles
 * product CRUD operations, stock tracking, reorder alerts, and pricing management. Products can
 * be physical inventory items (stock-managed) or services/subscriptions (non-stock).
 * 
 * **Product Types:**
 * - **Stock-Managed (is_stock=true):** Physical inventory with quantity tracking, min stock alerts
 * - **Non-Stock (is_stock=false):** Services, subscriptions, one-time fees (no inventory tracking)
 * 
 * **Stock Management:**
 * - Automatic quantity adjustments via invoice operations
 * - Min stock threshold triggers reorder notifications
 * - Real-time stock levels visible in product details
 * - Reorder list endpoint for procurement workflow
 * 
 * **Use Cases:**
 * - Hardware inventory (laptops, network equipment, peripherals)
 * - Software licenses (quantity-based tracking)
 * - Service catalog items (monthly managed services, hourly rates)
 * - Recurring subscriptions (per-user charges, flat fees)
 * - One-time fees (setup, installation, consulting)
 * 
 * **Multi-Tenant Architecture:**
 * - Products scoped to specific tenant (MSP customer organization)
 * - Root MSP tenant can manage products for all tenants
 * - Tenant-level product catalogs for white-label offerings
 * - Shared catalog items across tenant groups
 * 
 * **Pricing:**
 * - price_retail: Standard retail price per unit
 * - Used in invoice line items (can be overridden per invoice)
 * - Future: cost_price, markup percentages, tiered pricing
 * 
 * **Security:**
 * - All routes require authentication (authenticateToken)
 * - Multi-tenant filtering on all queries
 * - Root admin only for delete operations
 * - Referential integrity checks prevent orphaned records
 * 
 * **Related Models:**
 * - products table (product_id, name, is_stock, stock_quantity, min_stock, price_retail, tenant_id)
 * - invoice_items table (stock deductions on invoice creation)
 * - contract_line_items table (recurring product billing)
 * - purchase_order_items table (vendor purchasing for restock)
 * - notifications table (low stock alerts)
 * @requires express
 * @requires ../services/db
 * @requires ../middleware/auth
 * @requires ../middleware/tenant
 * @author IBG MSP Development Team
 * @date 2024-03-11
 * @since 1.0.0
 * @see {@link module:routes/invoices} for invoice line items
 * @see {@link module:routes/contracts} for recurring product billing
 */
 
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} /products/reorder Get Reorder List
 * @apiName GetReorderProducts
 * @apiGroup Products
 * @apiDescription Retrieves all stock-managed products (is_stock=true) that are below their
 * minimum stock threshold (min_stock). Sorted by shortage quantity (most urgent first) and
 * then by product name. Used for procurement workflow and inventory management.
 * 
 * **Reorder Logic:**
 * - Only includes products with is_stock=true
 * - Filters where stock_quantity < min_stock
 * - Calculates needed_qty = max(min_stock - stock_quantity, 0)
 * - Sorted by shortage severity (highest shortage first)
 * 
 * **Use Cases:**
 * - Daily procurement review
 * - Purchase order generation
 * - Inventory restocking workflow
 * - Low stock dashboard alerts
 * 
 * **Tenant Filtering:**
 * Root MSP users see all tenants' products needing reorder. Regular users see only their
 * tenant's products. Each product includes tenant_name for multi-tenant views.
 * @apiHeader {string} Authorization Bearer JWT token
 * @apiSuccess {object[]} products Array of products needing reorder
 * @apiSuccess {number} products.product_id Unique product ID
 * @apiSuccess {string} products.name Product name
 * @apiSuccess {string} products.description Product description
 * @apiSuccess {string} products.supplier Supplier/vendor name
 * @apiSuccess {string} products.sku Product SKU/part number
 * @apiSuccess {boolean} products.is_stock Always true (stock-managed products only)
 * @apiSuccess {number} products.stock_quantity Current stock level
 * @apiSuccess {number} products.min_stock Minimum stock threshold
 * @apiSuccess {number} products.price_retail Retail price per unit
 * @apiSuccess {number} products.tenant_id Associated tenant ID
 * @apiSuccess {string} products.tenant_name Tenant name (joined)
 * @apiSuccess {number} products.needed_qty Calculated quantity needed (min_stock - stock_quantity)
 * @apiError {number} 500 Server error during reorder list retrieval
 * @apiExample {curl} Example Request:
 *   curl -X GET \
 *        -H "Authorization: Bearer eyJhbGc..." \
 *        "https://api.everydaytech.au/products/reorder"
 * @apiExample {json} Success Response:
 *   HTTP/1.1 200 OK
 *   {
 *     "products": [
 *       {
 *         "product_id": 42,
 *         "name": "Dell Latitude 5420 Laptop",
 *         "description": "14" Intel i5, 16GB RAM, 512GB SSD",
 *         "supplier": "Dell Direct",
 *         "sku": "LAT-5420-I5-16-512",
 *         "is_stock": true,
 *         "stock_quantity": 2,
 *         "min_stock": 10,
 *         "price_retail": 1299.00,
 *         "tenant_id": 5,
 *         "tenant_name": "MSP Alpha",
 *         "needed_qty": 8
 *       },
 *       {
 *         "product_id": 15,
 *         "name": "HP EliteDesk 800 G6 Desktop",
 *         "description": "Intel i7, 32GB RAM, 1TB NVMe",
 *         "supplier": "HP Enterprise",
 *         "sku": "ED-800-G6-I7-32-1T",
 *         "is_stock": true,
 *         "stock_quantity": 3,
 *         "min_stock": 8,
 *         "price_retail": 1599.00,
 *         "tenant_id": 5,
 *         "tenant_name": "MSP Alpha",
 *         "needed_qty": 5
 *       }
 *     ]
 *   }
 * 
 * @since 1.0.0
 * @see {@link module:routes/products.getProducts} for full product catalog
 * @see {@link module:routes/invoices.createInvoice} for stock deductions
 */
router.get('/reorder', async (req, res) => {
  try {
  const { clause: tenantClause, params: tenantParams } = getTenantFilter(req, 'p', true);
  const whereClause = tenantClause ? `WHERE ${tenantClause} AND` : 'WHERE';
    
    const result = await pool.query(
      `SELECT 
         p.*, 
         t.name AS tenant_name,
         GREATEST(p.min_stock - p.stock_quantity, 0) AS needed_qty
       FROM products p
       LEFT JOIN tenants t ON p.tenant_id = t.tenant_id
       ${whereClause} p.is_stock = true AND p.stock_quantity < p.min_stock
       ORDER BY (p.min_stock - p.stock_quantity) DESC, p.name ASC`,
      tenantParams
    );
    res.json({ products: result.rows });
  } catch (err) {
    console.error('Error fetching products for reorder', err);
    res.status(500).send('Server error');
  }
});
 
/**
 * @api {get} /products List Products
 * @apiName GetProducts
 * @apiGroup Products
 * @apiDescription Retrieves paginated product catalog with search capabilities. Searches across
 * product name, description, supplier, and SKU fields. Returns both stock-managed inventory
 * items and non-stock service catalog items.
 * 
 * **Search:**
 * Fuzzy ILIKE search across:
 * - Product name
 * - Description
 * - Supplier/vendor name
 * - SKU/part number
 * 
 * **Pagination:**
 * - Default: 25 items per page
 * - Returns total count for pagination UI
 * - Sorted alphabetically by product name
 * 
 * **Tenant Filtering:**
 * Products filtered by authenticated user's tenant. Root MSP users see all tenants' products
 * with tenant_name included. Regular users see only their tenant's products.
 * 
 * **Response Format:**
 * Each product includes complete details (stock levels, pricing, supplier info) plus joined
 * tenant_name for multi-tenant views.
 * @apiHeader {string} Authorization Bearer JWT token
 * @apiParam {number} [page=1] Page number for pagination
 * @apiParam {number} [limit=25] Items per page (default: 25)
 * @apiParam {string} [search] Search term (name, description, supplier, SKU)
 * @apiSuccess {object[]} products Array of product objects
 * @apiSuccess {number} products.product_id Unique product ID
 * @apiSuccess {string} products.name Product name
 * @apiSuccess {string} products.description Product description
 * @apiSuccess {string} products.supplier Supplier/vendor name (may be NULL)
 * @apiSuccess {string} products.sku Product SKU/part number (may be NULL)
 * @apiSuccess {boolean} products.is_stock Whether product is stock-managed (true) or service (false)
 * @apiSuccess {number} products.stock_quantity Current stock level (0 for non-stock products)
 * @apiSuccess {number} products.min_stock Minimum stock threshold (0 for non-stock products)
 * @apiSuccess {number} products.price_retail Retail price per unit
 * @apiSuccess {number} products.tenant_id Associated tenant ID
 * @apiSuccess {string} products.tenant_name Tenant name (joined)
 * @apiSuccess {string} products.created_at Timestamp when product was created
 * @apiSuccess {Number} total Total count of products matching filters (for pagination)
 * 
 * @apiError {Number} 500 Server error during product retrieval
 * 
 * @apiExample {curl} Example Request:
 *   curl -X GET \
 *        -H "Authorization: Bearer eyJhbGc..." \
 *        "https://api.everydaytech.au/products?search=laptop&page=1&limit=25"
 * 
 * @apiExample {json} Success Response:
 *   HTTP/1.1 200 OK
 *   {
 *     "products": [
 *       {
 *         "product_id": 42,
 *         "name": "Dell Latitude 5420 Laptop",
 *         "description": "14" Intel i5, 16GB RAM, 512GB SSD",
 *         "supplier": "Dell Direct",
 *         "sku": "LAT-5420-I5-16-512",
 *         "is_stock": true,
 *         "stock_quantity": 12,
 *         "min_stock": 10,
 *         "price_retail": 1299.00,
 *         "tenant_id": 5,
 *         "tenant_name": "MSP Alpha",
 *         "created_at": "2024-01-15T10:00:00.000Z"
 *       },
 *       {
 *         "product_id": 18,
 *         "name": "Managed Services - Per User",
 *         "description": "Monthly managed IT services per user",
 *         "supplier": null,
 *         "sku": "SVC-MANAGED-USER",
 *         "is_stock": false,
 *         "stock_quantity": 0,
 *         "min_stock": 0,
 *         "price_retail": 125.00,
 *         "tenant_id": 5,
 *         "tenant_name": "MSP Alpha",
 *         "created_at": "2024-01-10T09:00:00.000Z"
 *       }
 *     ],
 *     "total": 47
 *   }
 * 
 * @since 1.0.0
 * @see {@link module:routes/products.getReorderProducts} for reorder list
 * @see {@link module:routes/products.getProduct} for single product details
 */
router.get('/', async (req, res) => {
  // Debug: log request context
  console.log(`[API][products] GET /products | Tenant:`, req.tenant, '| User:', req.user ? req.user.id : null, '| Query:', req.query);
  try {
    const { page = 1, limit = 25, search } = req.query;
    const offset = (parseInt(page, 10) - 1) * parseInt(limit, 10);
 
    const { clause: tenantClause, params: tenantParams, nextParamIndex } = getTenantFilter(req, 'p', true);
    let where = tenantClause ? `WHERE ${tenantClause}` : '';
    const params = [...tenantParams];
    let paramIndex = nextParamIndex;
    
    if (search && search.trim() !== '') {
      params.push(`%${search.trim()}%`);
      const searchWhere = `(
        p.name ILIKE $${paramIndex} OR 
        p.description ILIKE $${paramIndex} OR
        p.supplier ILIKE $${paramIndex} OR
        p.sku ILIKE $${paramIndex})`;
      where += where ? ` AND ${searchWhere}` : `WHERE ${searchWhere}`;
      paramIndex++;
    }
 
    const totalQ = `SELECT COUNT(*) FROM products p ${where}`;
    // Debug: log final query and params
    console.log(`[API][products] Executing totalQ:`, totalQ, '| Params:', params);
 
    const totalRes = await pool.query(totalQ, params);
    const total = parseInt(totalRes.rows[0].count, 10);
 
    params.push(limit);
    params.push(offset);
    const q = `SELECT p.*, t.name AS tenant_name FROM products p LEFT JOIN tenants t ON p.tenant_id = t.tenant_id ${where} ORDER BY p.name ASC LIMIT $${paramIndex} OFFSET $${paramIndex + 1}`;
    console.log(`[API][products] Executing q:`, q, '| Params:', params);
    const result = await pool.query(q, params);
    res.json({ products: result.rows, total });
  } catch (err) {
    console.error('[API][products] Error fetching products:', err, '| Tenant:', req.tenant, '| Query:', req.query);
    res.status(500).json({ error: 'Server error', details: err.message });
  }
});
 
/**
 * @api {get} /products/:id Get Product
 * @apiName GetProduct
 * @apiGroup Products
 * @apiDescription Retrieves a single product by ID with all details. Enforces tenant isolation.
 * 
 * **Tenant Validation:**
 * Product must belong to user's tenant scope. Root MSP users can access all products.
 * Regular users limited to their tenant's products.
 * @apiHeader {string} Authorization Bearer JWT token
 * @apiParam {number} id Product ID (in URL path)
 * @apiSuccess {number} product_id Unique product ID
 * @apiSuccess {string} name Product name
 * @apiSuccess {string} description Product description
 * @apiSuccess {string} supplier Supplier/vendor name (may be NULL)
 * @apiSuccess {string} sku Product SKU/part number (may be NULL)
 * @apiSuccess {boolean} is_stock Whether product is stock-managed
 * @apiSuccess {number} stock_quantity Current stock level
 * @apiSuccess {number} min_stock Minimum stock threshold
 * @apiSuccess {number} price_retail Retail price per unit
 * @apiSuccess {number} tenant_id Associated tenant ID
 * @apiSuccess {string} created_at Creation timestamp
 * @apiError {number} 404 Product 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/products/42"
 * 
 * @apiExample {json} Success Response:
 *   HTTP/1.1 200 OK
 *   {
 *     "product_id": 42,
 *     "name": "Dell Latitude 5420 Laptop",
 *     "description": "14" Intel i5, 16GB RAM, 512GB SSD",
 *     "supplier": "Dell Direct",
 *     "sku": "LAT-5420-I5-16-512",
 *     "is_stock": true,
 *     "stock_quantity": 12,
 *     "min_stock": 10,
 *     "price_retail": 1299.00,
 *     "tenant_id": 5,
 *     "created_at": "2024-01-15T10:00:00.000Z"
 *   }
 * 
 * @since 1.0.0
 * @see {@link module:routes/products.updateProduct} for updating products
 */
router.get('/:id', async (req, res) => {
  try {
    const { id } = req.params;
    // Enforce tenant filter for single product fetch
    const { clause: tenantClause, params: tenantParams, nextParamIndex } = getTenantFilter(req, 'p', true);
    let query = 'SELECT * FROM products p WHERE product_id = $1';
    const params = [id];
    if (tenantClause) {
      // Shift tenant param placeholders to start at $2 because $1 is the product_id
      const adjustedClause = tenantClause.replace(/\$(\d+)/g, (m, n) => `$${parseInt(n, 10) + 1}`);
      query += ` AND ${adjustedClause}`;
      params.push(...tenantParams);
    }
    const result = await pool.query(query, params);
    if (result.rows.length === 0) return res.status(404).send('Not found');
    res.json(result.rows[0]);
  } catch (err) {
    console.error('Error fetching product', err);
    res.status(500).send('Server error');
  }
});
 
/**
 * @api {post} /products Create Product
 * @apiName CreateProduct
 * @apiGroup Products
 * @apiDescription Creates a new product in the catalog. Automatically associated with authenticated
 * user's tenant. Can create stock-managed inventory items or non-stock service catalog items.
 * 
 * **Stock-Managed Products:**
 * Set is_stock=true and provide stock_quantity and min_stock values. Stock will be tracked
 * automatically via invoice operations.
 * 
 * **Service Catalog Items:**
 * Set is_stock=false. Stock fields ignored (set to 0). Used for services, subscriptions,
 * labor hours, one-time fees, etc.
 * 
 * **Tenant Association:**
 * Product automatically associated with req.tenant.id from authenticated user's context.
 * Tenant context required (returns 403 if missing).
 * @apiHeader {string} Authorization Bearer JWT token
 * @apiHeader {string} Content-Type application/json
 * @apiParam {string} name Product name (required)
 * @apiParam {string} [description] Product description (default: empty string)
 * @apiParam {string} [supplier] Supplier/vendor name (default: NULL)
 * @apiParam {boolean} [is_stock=false] Whether product is stock-managed (default: false)
 * @apiParam {number} [stock_quantity=0] Initial stock quantity (default: 0)
 * @apiParam {number} [min_stock=0] Minimum stock threshold for reorder alerts (default: 0)
 * @apiParam {number} [price_retail=0] Retail price per unit (default: 0)
 * @apiSuccess {number} product_id Generated product ID
 * @apiSuccess {string} name Product name
 * @apiSuccess {string} description Product description
 * @apiSuccess {boolean} is_stock Stock-managed flag
 * @apiSuccess {number} stock_quantity Current stock level
 * @apiSuccess {number} tenant_id Associated tenant ID
 * @apiSuccess {string} created_at Creation timestamp
 * @apiError {Number} 403 No tenant context (authentication issue)
 * @apiError {Number} 500 Create failed
 * 
 * @apiExample {curl} Example Request (Stock Item):
 *   curl -X POST \
 *        -H "Authorization: Bearer eyJhbGc..." \
 *        -H "Content-Type: application/json" \
 *        -d '{
 *          "name": "Dell Latitude 5420 Laptop",
 *          "description": "14" Intel i5, 16GB RAM, 512GB SSD",
 *          "supplier": "Dell Direct",
 *          "sku": "LAT-5420-I5-16-512",
 *          "is_stock": true,
 *          "stock_quantity": 15,
 *          "min_stock": 10,
 *          "price_retail": 1299.00
 *        }' \
 *        "https://api.everydaytech.au/products"
 * 
 * @apiExample {curl} Example Request (Service Item):
 *   curl -X POST \
 *        -H "Authorization: Bearer eyJhbGc..." \
 *        -H "Content-Type: application/json" \
 *        -d '{
 *          "name": "Managed Services - Per User",
 *          "description": "Monthly managed IT services per user",
 *          "sku": "SVC-MANAGED-USER",
 *          "is_stock": false,
 *          "price_retail": 125.00
 *        }' \
 *        "https://api.everydaytech.au/products"
 * 
 * @apiExample {json} Success Response:
 *   HTTP/1.1 201 Created
 *   {
 *     "product_id": 42,
 *     "name": "Dell Latitude 5420 Laptop",
 *     "description": "14" Intel i5, 16GB RAM, 512GB SSD",
 *     "supplier": "Dell Direct",
 *     "sku": "LAT-5420-I5-16-512",
 *     "is_stock": true,
 *     "stock_quantity": 15,
 *     "min_stock": 10,
 *     "price_retail": 1299.00,
 *     "tenant_id": 5,
 *     "created_at": "2024-03-11T16:00:00.000Z"
 *   }
 * 
 * @since 1.0.0
 * @see {@link module:routes/products.updateProduct} for updating products
 * @see {@link module:routes/invoices.createInvoice} for using products in invoices
 */
router.post('/', async (req, res) => {
  try {
    const p = req.body;
    const tenantId = req.tenant?.id;
    if (!tenantId) return res.status(403).json({ error: 'No tenant context' });
    const result = await pool.query(
      `INSERT INTO products (name, description, supplier, is_stock, stock_quantity, min_stock, price_retail, tenant_id)
       VALUES ($1,$2,$3,$4,$5,$6,$7,$8) RETURNING *`,
      [p.name, p.description || '', p.supplier || null, p.is_stock || false, p.stock_quantity || 0, p.min_stock || 0, p.price_retail || 0, tenantId]
    );
    res.status(201).json(result.rows[0]);
  } catch (err) {
    console.error('Error creating product', err);
    res.status(500).send('Create failed');
  }
});
 
/**
 * @api {put} /products/:id Update Product
 * @apiName UpdateProduct
 * @apiGroup Products
 * @apiDescription Updates an existing product. Validates tenant ownership before updating.
 * All fields provided in request body will be updated.
 * 
 * **Tenant Validation:**
 * Product must belong to user's tenant. Returns 404 if product not found or not in tenant scope.
 * 
 * **Stock Adjustments:**
 * Can manually adjust stock_quantity to correct inventory levels. Use with caution as this
 * bypasses normal invoice-based stock tracking.
 * 
 * **Field Updates:**
 * All fields from request body applied. Provide complete values for fields being updated.
 * Missing optional fields default to empty/zero values.
 * @apiHeader {string} Authorization Bearer JWT token
 * @apiHeader {string} Content-Type application/json
 * @apiParam {number} id Product ID (in URL path)
 * @apiParam {string} name Product name (required)
 * @apiParam {string} [description] Product description (defaults to empty string if omitted)
 * @apiParam {string} [supplier] Supplier/vendor name (defaults to NULL if omitted)
 * @apiParam {boolean} [is_stock=false] Whether product is stock-managed
 * @apiParam {number} [stock_quantity=0] Current stock quantity
 * @apiParam {number} [min_stock=0] Minimum stock threshold
 * @apiParam {number} [price_retail=0] Retail price per unit
 * @apiSuccess {number} product_id Product ID
 * @apiSuccess {string} name Updated product name
 * @apiSuccess {string} description Updated description
 * @apiSuccess {boolean} is_stock Stock-managed flag
 * @apiSuccess {number} stock_quantity Updated stock level
 * @apiSuccess {number} price_retail Updated retail price
 * @apiError {Number} 404 Product not found (or not in user's tenant)
 * @apiError {Number} 500 Update failed
 * 
 * @apiExample {curl} Example Request:
 *   curl -X PUT \
 *        -H "Authorization: Bearer eyJhbGc..." \
 *        -H "Content-Type: application/json" \
 *        -d '{
 *          "name": "Dell Latitude 5420 Laptop",
 *          "description": "14" Intel i5, 16GB RAM, 512GB SSD - Updated specs",
 *          "supplier": "Dell Direct",
 *          "is_stock": true,
 *          "stock_quantity": 18,
 *          "min_stock": 12,
 *          "price_retail": 1349.00
 *        }' \
 *        "https://api.everydaytech.au/products/42"
 * 
 * @apiExample {json} Success Response:
 *   HTTP/1.1 200 OK
 *   {
 *     "product_id": 42,
 *     "name": "Dell Latitude 5420 Laptop",
 *     "description": "14" Intel i5, 16GB RAM, 512GB SSD - Updated specs",
 *     "supplier": "Dell Direct",
 *     "is_stock": true,
 *     "stock_quantity": 18,
 *     "min_stock": 12,
 *     "price_retail": 1349.00,
 *     "tenant_id": 5
 *   }
 * 
 * @since 1.0.0
 * @see {@link module:routes/products.createProduct} for creating products
 */
router.put('/:id', async (req, res) => {
  try {
    const { id } = req.params;
    const p = req.body;
    
    // Check tenant access
  const { clause: tenantClause, params: tenantParams, nextParamIndex } = getTenantFilter(req, 'p', true);
    let checkQuery = `SELECT product_id FROM products p WHERE product_id = $${nextParamIndex}`;
    const checkParams = [...tenantParams, id];
    if (tenantClause) {
      checkQuery = `SELECT product_id FROM products p WHERE ${tenantClause} AND product_id = $${nextParamIndex}`;
    }
    const check = await pool.query(checkQuery, checkParams);
    if (check.rows.length === 0) return res.status(404).send('Not found');
    
    const result = await pool.query(
      `UPDATE products SET name=$1, description=$2, supplier=$3, is_stock=$4, stock_quantity=$5, min_stock=$6, price_retail=$7 WHERE product_id=$8 RETURNING *`,
      [p.name, p.description || '', p.supplier || null, p.is_stock || false, p.stock_quantity || 0, p.min_stock || 0, p.price_retail || 0, id]
    );
    res.json(result.rows[0]);
  } catch (err) {
    console.error('Error updating product:', err);
    console.error('Error details:', err.message);
    res.status(500).json({ error: 'Update failed', details: err.message });
  }
});
 
/**
 * @api {delete} /products/:id Delete Product
 * @apiName DeleteProduct
 * @apiGroup Products
 * @apiDescription Permanently deletes a product from the catalog. Root tenant admin only.
 * Validates referential integrity - cannot delete products referenced in invoices, contracts,
 * or purchase orders.
 * 
 * **Authorization:**
 * Only root tenant administrators (MSP level) can delete products. Regular tenant users
 * receive 403 Forbidden.
 * 
 * **Authorization Checks:**
 * - req.tenant.subdomain === 'admin' OR
 * - req.tenant.isMsp === true OR
 * - req.user.is_msp === true
 * 
 * **Referential Integrity:**
 * System checks for product references in:
 * - invoice_line_items table
 * - contract_line_items table
 * - purchase_order_items table
 * 
 * If ANY references exist, deletion blocked with detailed error message showing counts.
 * 
 * **Constraint Handling:**
 * Additional database-level foreign key constraint protection. If constraint violation occurs,
 * user-friendly error returned instead of raw database error.
 * 
 * **Audit Logging:**
 * Successful deletions logged to console with product ID, name, and user email.
 * @apiHeader {string} Authorization Bearer JWT token (root admin required)
 * @apiParam {number} id Product ID (in URL path)
 * @apiSuccess {number} 204 No Content - Product deleted successfully (empty response body)
 * @apiError {number} 400 Cannot delete product (has references in invoices/contracts/POs)
 * @apiError {number} 403 Access denied (not root tenant administrator)
 * @apiError {number} 404 Product not found
 * @apiError {number} 500 Delete failed (database error)
 * @apiExample {curl} Example Request:
 *   curl -X DELETE \
 *        -H "Authorization: Bearer eyJhbGc..." \
 *        "https://api.everydaytech.au/products/42"
 * @apiExample {json} Success Response:
 *   HTTP/1.1 204 No Content
 * @apiExample {json} Error Response (Has References):
 *   HTTP/1.1 400 Bad Request
 *   {
 *     "error": "Cannot delete product",
 *     "message": "This product is referenced in 3 invoice(s), 2 contract(s). Please remove these references first or contact support."
 *   }
 * @apiExample {json} Error Response (Not Admin):
 *   HTTP/1.1 403 Forbidden
 *   {
 *     "error": "Access denied",
 *     "message": "Only root tenant administrators can delete products"
 *   }
 * @since 1.0.0
 * @see {@link module:routes/products.updateProduct} for updating products
 */
router.delete('/:id', async (req, res) => {
  try {
    const { id } = req.params;
    
    // Check if user is root tenant admin
    const isRootAdmin = req.tenant?.subdomain === 'admin' || req.tenant?.isMsp === true || req.user?.is_msp === true;
    
    if (!isRootAdmin) {
      return res.status(403).json({ 
        error: 'Access denied', 
        message: 'Only root tenant administrators can delete products' 
      });
    }
    
    // Check if product exists
    const checkResult = await pool.query(
      'SELECT product_id, name FROM products WHERE product_id = $1',
      [id]
    );
    
    if (checkResult.rows.length === 0) {
      return res.status(404).json({ error: 'Product not found' });
    }
 
    const productName = checkResult.rows[0].name;
 
    // Check for references in other tables
    const references = await pool.query(`
      SELECT 
        (SELECT COUNT(*) FROM invoice_line_items WHERE product_id = $1) as invoice_items,
        (SELECT COUNT(*) FROM contract_line_items WHERE product_id = $1) as contract_items,
        (SELECT COUNT(*) FROM purchase_order_items WHERE product_id = $1) as po_items
    `, [id]);
 
    const refs = references.rows[0];
    const totalRefs = parseInt(refs.invoice_items) + parseInt(refs.contract_items) + parseInt(refs.po_items);
 
    if (totalRefs > 0) {
      const details = [];
      if (parseInt(refs.invoice_items) > 0) details.push(`${refs.invoice_items} invoice(s)`);
      if (parseInt(refs.contract_items) > 0) details.push(`${refs.contract_items} contract(s)`);
      if (parseInt(refs.po_items) > 0) details.push(`${refs.po_items} purchase order(s)`);
 
      return res.status(400).json({ 
        error: 'Cannot delete product',
        message: `This product is referenced in ${details.join(', ')}. Please remove these references first or contact support.`
      });
    }
    
    // Delete the product
    await pool.query('DELETE FROM products WHERE product_id = $1', [id]);
    
    console.log(`[Products] Deleted product ${id} (${productName}) by user ${req.user.email}`);
    res.sendStatus(204);
  } catch (err) {
    console.error('Error deleting product', err);
    
    // Handle foreign key constraint errors
    if (err.code === '23503') {
      return res.status(400).json({ 
        error: 'Cannot delete product',
        message: 'This product is still referenced in other records. Please remove all references first.'
      });
    }
    
    res.status(500).json({ error: 'Delete failed', details: err.message });
  }
});
 
module.exports = router;