documents.js

Go to the documentation of this file.

/**
 * @file documents.js
 * @module routes/documents
 * @description Knowledge base and document library endpoints with attachment support.
 * Provides CRUD operations for technical documentation with multi-tenant isolation,
 * file attachments (base64-encoded), and category organization.
 * @requires express
 * @requires services/db
 * @requires middleware/auth
 * @requires middleware/adminOnly
 * @requires middleware/tenant
 * @requires middleware/fileValidation
 * @author RMM-PSA Development Team
 * @copyright 2026 RMM-PSA Platform
 * @license Proprietary
 */
 
/**
 * @apiDefine Documents Knowledge Base
 * Document library and technical documentation management
 */
 
const express = require('express');
const router = express.Router();
const pool = require('../services/db');
const authenticateToken = require('../middleware/auth');
const requireAdmin = require('../middleware/adminOnly');
const { getTenantFilter, setTenantContext } = require('../middleware/tenant');
const { validateAttachments } = require('../middleware/fileValidation');
 
// Apply authentication and tenant context to all routes
router.use(authenticateToken, setTenantContext);
 
/**
 * @api {get} /api/documents List documents
 * @apiName ListDocuments
 * @apiGroup Documents
 * @apiDescription Retrieve paginated list of documents with tenant filtering.
 * Returns document metadata with author and tenant info, plus total count for pagination.
 * Attachments not included in list (use GET /:id for full document).
 * @apiParam {number} [page=1] Page number (query parameter)
 * @apiParam {number} [limit=10] Results per page (query parameter, max: 100)
 * @apiSuccess {object[]} documents Array of document summaries
 * @apiSuccess {number} documents.doc_id Document ID
 * @apiSuccess {string} documents.title Document title
 * @apiSuccess {string} documents.category Document category/type
 * @apiSuccess {string} documents.content Document content (full text/markdown)
 * @apiSuccess {DateTime} documents.created_at Creation timestamp
 * @apiSuccess {DateTime} documents.updated_at Last modified timestamp
 * @apiSuccess {number} documents.tenant_id Tenant ID
 * @apiSuccess {string} documents.author_name Author display name
 * @apiSuccess {string} documents.tenant_name Tenant name
 * @apiSuccess {number} total Total document count (for pagination)
 * @apiSuccess {number} page Current page number
 * @apiSuccess {number} limit Results per page
 * @apiSuccess {number} pages Total pages available
 * @apiError (500) {String} error="Server error" Database query failed
 * @apiExample {curl} Example:
 *   curl -X GET "http://localhost:3000/api/documents?page=1&limit=10" \\
 *     -H "Authorization: Bearer YOUR_TOKEN"
 * @apiSuccessExample {json} Success-Response:
 *   HTTP/1.1 200 OK
 *   {
 *     "documents": [
 *       {
 *         "doc_id": 42,
 *         "title": "How to Reset Passwords",
 *         "category": "procedure",
 *         "content": "# Password Reset...\n",
 *         "created_at": "2026-03-10T08:00:00.000Z",
 *         "updated_at": "2026-03-12T10:30:00.000Z",
 *         "tenant_id": 2,
 *         "author_name": "John Admin",
 *         "tenant_name": "Acme Corp"
 *       }
 *     ],
 *     "total": 47,
 *     "page": 1,
 *     "limit": 10,
 *     "pages": 5
 *   }
 */
router.get('/', async (req, res) => {
  const page = parseInt(req.query.page) || 1;
  const limit = parseInt(req.query.limit) || 10;
  const offset = (page - 1) * limit;
 
  try {
    // ✅ enforce tenant filter (now that tenant_id exists)
    const { clause: tenantClause, params: tenantParams, nextParamIndex } = getTenantFilter(req, 'd', true);
    const params = [...tenantParams];
    const whereClause = tenantClause ? `WHERE ${tenantClause}` : '';
 
    params.push(limit, offset);
 
    const [docsResult, countResult] = await Promise.all([
      pool.query(`
        SELECT 
          d.doc_id,
          d.title,
          d.category,
          d.content,
          d.created_at,
          d.updated_at,
          d.tenant_id,
          u.name AS author_name,
          t.name AS tenant_name
        FROM documents d
        LEFT JOIN users u ON d.author_id = u.user_id
        LEFT JOIN tenants t ON d.tenant_id = t.tenant_id
        ${whereClause}
        ORDER BY d.doc_id DESC
        LIMIT $${nextParamIndex} OFFSET $${nextParamIndex + 1}
      `, params),
      // ✅ ensure alias “d” is defined here too
  pool.query(`
        SELECT COUNT(*) 
        FROM documents d 
        ${whereClause}
      `, tenantParams)
    ]);
 
    res.json({
      documents: docsResult.rows,
      total: parseInt(countResult.rows[0].count, 10)
    });
  } catch (err) {
    console.error('Error fetching documents:', err);
    res.status(500).json({ error: 'Server error', details: err.message });
  }
});
 
/**
 * @api {get} /api/documents/:id Get document by ID
 * @apiName GetDocument
 * @apiGroup Documents
 * @apiDescription Retrieve single document with attachment metadata (file data excluded).
 * Includes author, tenant info, and list of attachments with size/filename.
 * Enforces tenant isolation.
 * @apiParam {number} id Document ID
 * @apiSuccess {number} doc_id Document ID
 * @apiSuccess {string} title Document title
 * @apiSuccess {string} category Document category/type
 * @apiSuccess {string} content Full document content
 * @apiSuccess {DateTime} created_at Creation timestamp
 * @apiSuccess {DateTime} updated_at Last modified timestamp
 * @apiSuccess {number} tenant_id Tenant ID
 * @apiSuccess {string} author_name Author display name
 * @apiSuccess {string} tenant_name Tenant name
 * @apiSuccess {Object[]} attachments Attachment metadata (data payload excluded)
 * @apiSuccess {number} attachments.id Attachment ID
 * @apiSuccess {string} attachments.filename Stored filename
 * @apiSuccess {string} attachments.original_filename Original upload filename
 * @apiSuccess {string} attachments.file_size_mb File size (e.g., "2.5 MB")
 * @apiSuccess {DateTime} attachments.created_at Upload timestamp
 * @apiError (404) {String} error="Document not found" Document ID not found or tenant mismatch
 * @apiError (500) {String} error="Server error" Database query failed
 * @apiExample {curl} Example:
 *   curl -X GET http://localhost:3000/api/documents/42 \\
 *     -H "Authorization: Bearer YOUR_TOKEN"
 * @apiSuccessExample {json} Success-Response:
 *   HTTP/1.1 200 OK
 *   {
 *     "doc_id": 42,
 *     "title": "Firewall Configuration Guide",
 *     "category": "network",
 *     "content": "# Firewall Setup\n...markdown content...",
 *     "created_at": "2026-03-10T08:00:00.000Z",
 *     "updated_at": "2026-03-12T10:30:00.000Z",
 *     "tenant_id": 2,
 *     "author_name": "John Admin",
 *     "tenant_name": "Acme Corp",
 *     "attachments": [
 *       {
 *         "id": 15,
 *         "filename": "firewall-diagram.png",
 *         "original_filename": "Firewall Diagram.png",
 *         "file_size_mb": "0.8 MB",
 *         "created_at": "2026-03-10T08:30:00.000Z"
 *       }
 *     ]
 *   }
 */
router.get('/:id', async (req, res) => {
  const { id } = req.params;
  
  try {
  const { clause: tenantClause, params: tenantParams } = getTenantFilter(req, 'd', false);
    let query = `
      SELECT d.doc_id, d.title, d.category, d.content, d.created_at, d.updated_at, d.tenant_id,
             u.name as author_name, t.name as tenant_name
      FROM documents d
      LEFT JOIN users u ON d.author_id = u.user_id
      LEFT JOIN tenants t ON d.tenant_id = t.tenant_id
      WHERE d.doc_id = $1
    `;
    const params = [id];
    
    if (tenantClause) {
      // Shift tenant param placeholders to start at $2 because $1 is the doc_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).json({ error: 'Document not found' });
    }
 
    const doc = result.rows[0];
 
    // Fetch attachment metadata (exclude data payload)
    let attachments = [];
    try {
      const aRes = await pool.query(
        `SELECT id, filename, original_filename, file_size_mb, created_at
         FROM document_attachments
         WHERE document_id = $1
         ORDER BY id ASC`,
        [id]
      );
      attachments = aRes.rows;
    } catch (e) {
      // Table might not exist yet; ignore
      console.warn('No attachment metadata available:', e.message);
    }
    
    res.json({ ...doc, attachments });
  } catch (err) {
    console.error('Error fetching document:', err);
    res.status(500).json({ error: 'Server error', details: err.message });
  }
});
 
/**
 * @api {post} /api/documents Create document
 * @apiName CreateDocument
 * @apiGroup Documents
 * @apiDescription Create new document with optional attachments (base64-encoded).
 * Attachments validated by middleware (file type, size). Document assigned to user's tenant.
 * Creates document_attachments table if not exists.
 * @apiParam {string} title Document title
 * @apiParam {string} content Document content (markdown/text)
 * @apiParam {string} [category] Document category (e.g., "procedure", "network", "troubleshooting")
 * @apiParam {string} [type] Legacy category field (use category instead)
 * @apiParam {object[]} [validatedAttachments] Validated attachments from middleware
 * @apiParam {string} validatedAttachments.filename Stored filename
 * @apiParam {string} validatedAttachments.originalFilename Original upload filename
 * @apiParam {string} validatedAttachments.data Base64-encoded file content
 * @apiParam {string} validatedAttachments.size File size (e.g., "1.2 MB")
 * @apiSuccess (201) {Number} doc_id Created document ID
 * @apiSuccess (201) {String} title Document title
 * @apiSuccess (201) {String} content Document content
 * @apiSuccess (201) {String} category Document category
 * @apiSuccess (201) {Number} tenant_id Tenant ID
 * @apiSuccess (201) {DateTime} created_at Creation timestamp
 * @apiSuccess (201) {DateTime} updated_at Last modified timestamp
 * @apiError (403) {String} error="No tenant context" Tenant not set (authentication issue)
 * @apiError (500) {String} error="Failed to create document" Database insert failed
 * @apiExample {curl} Example:
 *   curl -X POST http://localhost:3000/api/documents \\
 *     -H "Authorization: Bearer YOUR_TOKEN" \\
 *     -H "Content-Type: application/json" \\
 *     -d '{
 *       "title": "Windows Update Procedure",
 *       "content": "# Procedure\n1. Open Windows Update...",
 *       "category": "procedure"
 *     }'
 * @apiSuccessExample {json} Success-Response:
 *   HTTP/1.1 201 Created
 *   {
 *     "doc_id": 43,
 *     "title": "Windows Update Procedure",
 *     "content": "# Procedure\n1. Open Windows Update...",
 *     "category": "procedure",
 *     "tenant_id": 2,
 *     "created_at": "2026-03-12T11:00:00.000Z",
 *     "updated_at": "2026-03-12T11:00:00.000Z"
 *   }
 */
router.post('/', validateAttachments, async (req, res) => {
  const { title, content, category: categoryFromBody, type, validatedAttachments } = req.body;
 
  // Prefer explicit category; fall back to legacy 'type'
  const category = categoryFromBody ?? type ?? null;
 
  if (!req.tenant?.id) {
    return res.status(403).json({ error: 'No tenant context' });
  }
 
  try {
    console.log('POST /documents - body:', { title, hasContent: !!content, category, attachmentsCount: Array.isArray(validatedAttachments) ? validatedAttachments.length : 0 });
    console.log('POST /documents - tenant:', req.tenant);
    const result = await pool.query(
      `INSERT INTO documents (title, content, category, tenant_id)
       VALUES ($1, $2, $3, $4) RETURNING *`,
      [title, content, category, req.tenant.id]
    );
 
    const doc = result.rows[0];
 
    // If attachments are provided and validated, store them
    if (Array.isArray(validatedAttachments) && validatedAttachments.length > 0) {
      // ensure attachments table exists
      await pool.query(`
        CREATE TABLE IF NOT EXISTS document_attachments (
          id SERIAL PRIMARY KEY,
          document_id INTEGER REFERENCES documents(doc_id) ON DELETE CASCADE,
          filename VARCHAR(255),
          original_filename VARCHAR(255),
          data_base64 TEXT,
          file_size_mb VARCHAR(10),
          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
      `);
 
      for (const a of validatedAttachments) {
        try {
          await pool.query(
            `INSERT INTO document_attachments (document_id, filename, original_filename, data_base64, file_size_mb) 
             VALUES ($1, $2, $3, $4, $5)`,
            [doc.doc_id, a.filename, a.originalFilename, a.data, a.size]
          );
        } catch (innerErr) {
          console.error('Failed to save attachment', innerErr);
        }
      }
    }
 
    res.status(201).json(doc);
  } catch (err) {
    console.error('Error creating document:', err);
    res.status(500).json({ error: 'Failed to create document', details: err.message });
  }
});
 
/**
 * @api {put} /api/documents/:id Update document
 * @apiName UpdateDocument
 * @apiGroup Documents
 * @apiDescription Update existing document (title, content, category).
 * Enforces tenant isolation - user can only update documents in their tenant.
 * Does not affect attachments (use POST /:id/attachments for that).
 * @apiParam {number} id Document ID
 * @apiParam {string} title Updated document title
 * @apiParam {string} content Updated document content
 * @apiParam {string} [category] Updated document category
 * @apiSuccess {number} doc_id Document ID
 * @apiSuccess {string} title Updated title
 * @apiSuccess {string} content Updated content
 * @apiSuccess {string} category Updated category
 * @apiSuccess {number} tenant_id Tenant ID
 * @apiSuccess {DateTime} created_at Original creation timestamp
 * @apiSuccess {DateTime} updated_at New update timestamp
 * @apiError (404) {String} error="Document not found" Document ID not found or tenant mismatch
 * @apiError (500) {String} error="Failed to update document" Database update failed
 * @apiExample {curl} Example:
 *   curl -X PUT http://localhost:3000/api/documents/42 \\
 *     -H "Authorization: Bearer YOUR_TOKEN" \\
 *     -H "Content-Type: application/json" \\
 *     -d '{
 *       "title": "Firewall Configuration Guide (Updated)",
 *       "content": "# Updated Firewall Setup\n...",
 *       "category": "network"
 *     }'
 * @apiSuccessExample {json} Success-Response:
 *   HTTP/1.1 200 OK
 *   {
 *     "doc_id": 42,
 *     "title": "Firewall Configuration Guide (Updated)",
 *     "content": "# Updated Firewall Setup\n...",
 *     "category": "network",
 *     "tenant_id": 2,
 *     "created_at": "2026-03-10T08:00:00.000Z",
 *     "updated_at": "2026-03-12T11:15:00.000Z"
 *   }
 */
router.put('/:id', async (req, res) => {
  const { id } = req.params;
  const { title, content, category } = req.body;
  
  try {
  const { clause: tenantClause, params: tenantParams } = getTenantFilter(req, 'd', false);
    
    // First check if document exists and user has access
    let checkQuery = 'SELECT doc_id FROM documents d WHERE doc_id = $1';
    const checkParams = [id];
    
    if (tenantClause) {
      const adjustedClause = tenantClause.replace(/\$(\d+)/g, (m, n) => `$${parseInt(n, 10) + 1}`);
      checkQuery += ` AND ${adjustedClause}`;
      checkParams.push(...tenantParams);
    }
    
    const checkResult = await pool.query(checkQuery, checkParams);
    
    if (checkResult.rows.length === 0) {
      return res.status(404).json({ error: 'Document not found' });
    }
    
    // Update the document
    const result = await pool.query(
      `UPDATE documents 
       SET title = $1, content = $2, category = $3, updated_at = NOW()
       WHERE doc_id = $4
       RETURNING *`,
      [title, content, category, id]
    );
    
    res.json(result.rows[0]);
  } catch (err) {
    console.error('Error updating document:', err);
    res.status(500).json({ error: 'Failed to update document', details: err.message });
  }
});
 
/**
 * @api {post} /api/documents/:id/attachments Upload attachments
 * @apiName UploadDocumentAttachments
 * @apiGroup Documents
 * @apiDescription Add attachments to existing document (base64-encoded files).
 * Attachments validated by middleware for file type and size. Creates attachments table if not exists.
 * Enforces tenant isolation.
 * @apiParam {number} id Document ID
 * @apiParam {object[]} validatedAttachments Validated attachments from middleware
 * @apiParam {string} validatedAttachments.filename Stored filename
 * @apiParam {string} validatedAttachments.originalFilename Original upload filename
 * @apiParam {string} validatedAttachments.data Base64-encoded file content
 * @apiParam {string} validatedAttachments.size File size (e.g., "1.2 MB")
 * @apiSuccess (201) {String} message="Attachments uploaded successfully"
 * @apiSuccess (201) {Number} count Number of attachments uploaded
 * @apiError (400) {String} error="No valid attachments provided" No attachments in request
 * @apiError (404) {String} error="Document not found" Document ID not found or tenant mismatch
 * @apiError (500) {String} error="Failed to upload attachments" Database insert failed
 * @apiExample {curl} Example:
 *   curl -X POST http://localhost:3000/api/documents/42/attachments \\
 *     -H "Authorization: Bearer YOUR_TOKEN" \\
 *     -H "Content-Type: application/json" \\
 *     -d '{
 *       "validatedAttachments": [
 *         {
 *           "filename": "network-diagram.png",
 *           "originalFilename": "Network Diagram.png",
 *           "data": "iVBORw0KGgoAAAANS...",
 *           "size": "1.2 MB"
 *         }
 *       ]
 *     }'
 * @apiSuccessExample {json} Success-Response:
 *   HTTP/1.1 201 Created
 *   {
 *     "message": "Attachments uploaded successfully",
 *     "count": 1
 *   }
 */
router.post('/:id/attachments', validateAttachments, async (req, res) => {
  const { id } = req.params;
  const { validatedAttachments } = req.body;
 
  if (!validatedAttachments || validatedAttachments.length === 0) {
    return res.status(400).json({ error: 'No valid attachments provided' });
  }
 
  try {
    // Verify document exists and user has tenant access
  const { clause: tenantClause, params: tenantParams } = getTenantFilter(req, 'd', false);
    let checkQuery = 'SELECT doc_id FROM documents d WHERE doc_id = $1';
    const checkParams = [id];
    if (tenantClause) {
      const adjusted = tenantClause.replace(/\$(\d+)/g, (m, n) => `$${parseInt(n, 10) + 1}`);
      checkQuery += ` AND ${adjusted}`;
      checkParams.push(...tenantParams);
    }
    const check = await pool.query(checkQuery, checkParams);
    if (check.rows.length === 0) {
      return res.status(404).json({ error: 'Document not found' });
    }
 
    // Ensure attachments table exists
    await pool.query(`
      CREATE TABLE IF NOT EXISTS document_attachments (
        id SERIAL PRIMARY KEY,
        document_id INTEGER REFERENCES documents(doc_id) ON DELETE CASCADE,
        filename VARCHAR(255),
        original_filename VARCHAR(255),
        data_base64 TEXT,
        file_size_mb VARCHAR(10),
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      )
    `);
 
    // Insert attachments
    for (const a of validatedAttachments) {
      try {
        await pool.query(
          `INSERT INTO document_attachments (document_id, filename, original_filename, data_base64, file_size_mb) 
           VALUES ($1, $2, $3, $4, $5)`,
          [id, a.filename, a.originalFilename, a.data, a.size]
        );
      } catch (innerErr) {
        console.error('Failed to save attachment', innerErr);
      }
    }
 
    res.status(201).json({ message: 'Attachments uploaded successfully', count: validatedAttachments.length });
  } catch (err) {
    console.error('Error uploading attachments:', err);
    res.status(500).json({ error: 'Failed to upload attachments', details: err.message });
  }
});
 
/**
 * @api {get} /api/documents/:id/attachments/:attachmentId/download Download attachment
 * @apiName DownloadDocumentAttachment
 * @apiGroup Documents
 * @apiDescription Download attachment file from document. Returns binary file with original filename.
 * Decodes base64-encoded attachment data and streams to client. Enforces tenant isolation.
 * @apiParam {number} id Document ID
 * @apiParam {number} attachmentId Attachment ID
 * @apiSuccess {File} file Binary file download with original filename
 * @apiError (404) {String} error="Document not found" Document ID not found or tenant mismatch
 * @apiError (404) {String} error="Attachment not found" Attachment ID not found for document
 * @apiError (500) {String} error="Failed to download attachment" Database query or decoding failed
 * @apiExample {curl} Example:
 *   curl -X GET http://localhost:3000/api/documents/42/attachments/15/download \\
 *     -H "Authorization: Bearer YOUR_TOKEN" \\
 *     -o firewall-diagram.png
 * @apiSuccessExample {binary} Success-Response:
 *   HTTP/1.1 200 OK
 *   Content-Type: application/octet-stream
 *   Content-Disposition: attachment; filename="Firewall Diagram.png"
 *   
 *   [binary file content]
 */
router.get('/:id/attachments/:attachmentId/download', async (req, res) => {
  const { id, attachmentId } = req.params;
  
  console.log(`[Documents] Download request - doc_id: ${id}, attachment_id: ${attachmentId}, user: ${req.user?.user_id}, tenant: ${req.tenant?.id}`);
  
  try {
    // Ensure document belongs to tenant via join
  const { clause: tenantClause, params: tenantParams } = getTenantFilter(req, 'd', false);
    let checkQuery = 'SELECT 1 FROM documents d WHERE d.doc_id = $1';
    const checkParams = [id];
    if (tenantClause) {
      const adjusted = tenantClause.replace(/\$(\d+)/g, (m, n) => `$${parseInt(n, 10) + 1}`);
      checkQuery += ` AND ${adjusted}`;
      checkParams.push(...tenantParams);
    }
    
    console.log('[Documents] Download check query:', checkQuery, checkParams);
    
    const check = await pool.query(checkQuery, checkParams);
    if (check.rows.length === 0) {
      console.log('[Documents] Document not found or access denied for tenant');
      return res.status(404).json({ error: 'Document not found' });
    }
 
    const aRes = await pool.query(
      `SELECT original_filename, filename, data_base64
       FROM document_attachments
       WHERE id = $1 AND document_id = $2`,
      [attachmentId, id]
    );
    
    if (aRes.rows.length === 0) {
      console.log('[Documents] Attachment not found');
      return res.status(404).json({ error: 'Attachment not found' });
    }
    
    const att = aRes.rows[0];
    console.log('[Documents] Attachment found:', att.original_filename || att.filename);
 
    // Decode base64 and send as file
    let base64 = att.data_base64;
    if (base64.includes(',')) base64 = base64.split(',')[1];
    const buf = Buffer.from(base64, 'base64');
 
    res.setHeader('Content-Type', 'application/octet-stream');
    res.setHeader('Content-Disposition', `attachment; filename="${att.original_filename || att.filename}"`);
    res.send(buf);
  } catch (err) {
    console.error('Error downloading attachment:', err);
    res.status(500).json({ error: 'Failed to download attachment', details: err.message });
  }
});
 
/**
 * @api {delete} /api/documents/:id Delete document
 * @apiName DeleteDocument
 * @apiGroup Documents
 * @apiDescription Delete document and associated attachments. Admin only.
 * Enforces tenant isolation - admin can only delete documents in their tenant.
 * Cascades to delete attachments first due to foreign key constraints.
 * @apiParam {number} id Document ID
 * @apiSuccess {string} message="Document deleted successfully"
 * @apiSuccess {object} deleted_document Deleted document object
 * @apiSuccess {number} deleted_document.doc_id Deleted document ID
 * @apiSuccess {string} deleted_document.title Deleted document title
 * @apiSuccess {number} deleted_document.tenant_id Tenant ID
 * @apiError (403) {String} error="Forbidden" User not admin
 * @apiError (404) {String} error="Document not found" Document ID not found or tenant mismatch
 * @apiError (500) {String} error="Failed to delete document" Database delete failed
 * @apiExample {curl} Example:
 *   curl -X DELETE http://localhost:3000/api/documents/42 \\
 *     -H "Authorization: Bearer YOUR_TOKEN"
 * @apiSuccessExample {json} Success-Response:
 *   HTTP/1.1 200 OK
 *   {
 *     "message": "Document deleted successfully",
 *     "deleted_document": {
 *       "doc_id": 42,
 *       "title": "Firewall Configuration Guide",
 *       "category": "network",
 *       "tenant_id": 2,
 *       "created_at": "2026-03-10T08:00:00.000Z",
 *       "updated_at": "2026-03-12T10:30:00.000Z"
 *     }
 *   }
 */
router.delete('/:id', requireAdmin, async (req, res) => {
  const { id } = req.params;
  
  try {
    const { clause: tenantClause, params: tenantParams } = getTenantFilter(req, 'd', false);
    
    // First check if document exists and user has access
    let checkQuery = 'SELECT doc_id, title FROM documents d WHERE doc_id = $1';
    const checkParams = [id];
    
    if (tenantClause) {
      const adjustedClause = tenantClause.replace(/\$(\d+)/g, (m, n) => `$${parseInt(n, 10) + 1}`);
      checkQuery += ` AND ${adjustedClause}`;
      checkParams.push(...tenantParams);
    }
    
    const checkResult = await pool.query(checkQuery, checkParams);
    
    if (checkResult.rows.length === 0) {
      return res.status(404).json({ error: 'Document not found' });
    }
    
    const document = checkResult.rows[0];
    
    // Delete associated attachments first (due to foreign key constraints)
    try {
      await pool.query('DELETE FROM document_attachments WHERE document_id = $1', [id]);
    } catch (attachErr) {
      // Table might not exist, that's ok
      console.log('No attachments to delete or attachment table does not exist');
    }
    
    // Delete the document
    const result = await pool.query(
      'DELETE FROM documents WHERE doc_id = $1 RETURNING *',
      [id]
    );
    
    console.log(`[Documents] Document deleted: ID ${id}, Title: "${document.title}", User: ${req.user?.user_id}, Tenant: ${req.tenant?.id}`);
    
    res.json({ 
      message: 'Document deleted successfully', 
      deleted_document: result.rows[0] 
    });
  } catch (err) {
    console.error('Error deleting document:', err);
    res.status(500).json({ error: 'Failed to delete document', details: err.message });
  }
});
 
module.exports = router;