cve-dashboard/backend/routes/auditLog.js

// Audit Log Routes (Admin only)
const express = require('express');
const pool = require('../db');
const { requireAuth, requireGroup } = require('../middleware/auth');

function createAuditLogRouter() {
    const router = express.Router();

    // All routes require Admin group
    router.use(requireAuth(), requireGroup('Admin'));

    // Get paginated audit logs with filters
    router.get('/', async (req, res) => {
        const {
            page = 1,
            limit = 25,
            user,
            action,
            entityType,
            startDate,
            endDate
        } = req.query;

        const offset = (Math.max(1, parseInt(page)) - 1) * parseInt(limit);
        const pageSize = Math.min(100, Math.max(1, parseInt(limit)));

        let where = [];
        let params = [];
        let paramIndex = 1;

        if (user) {
            where.push(`username ILIKE $${paramIndex++}`);
            params.push(`%${user}%`);
        }
        if (action) {
            where.push(`action = $${paramIndex++}`);
            params.push(action);
        }
        if (entityType) {
            where.push(`entity_type = $${paramIndex++}`);
            params.push(entityType);
        }
        if (startDate) {
            where.push(`created_at >= $${paramIndex++}`);
            params.push(startDate);
        }
        if (endDate) {
            where.push(`created_at <= $${paramIndex++}`);
            params.push(endDate + ' 23:59:59');
        }

        const whereClause = where.length > 0 ? 'WHERE ' + where.join(' AND ') : '';

        try {
            // Get total count
            const countResult = await pool.query(
                `SELECT COUNT(*) as total FROM audit_logs ${whereClause}`,
                params
            );
            const total = parseInt(countResult.rows[0].total);

            // Get paginated results
            const dataResult = await pool.query(
                `SELECT * FROM audit_logs ${whereClause} ORDER BY created_at DESC LIMIT $${paramIndex++} OFFSET $${paramIndex++}`,
                [...params, pageSize, offset]
            );

            res.json({
                logs: dataResult.rows,
                pagination: {
                    page: parseInt(page),
                    limit: pageSize,
                    total: total,
                    totalPages: Math.ceil(total / pageSize)
                }
            });
        } catch (err) {
            console.error('Audit log query error:', err);
            res.status(500).json({ error: 'Failed to fetch audit logs' });
        }
    });

    // Get distinct action types for filter dropdown
    router.get('/actions', async (req, res) => {
        try {
            const { rows } = await pool.query(
                'SELECT DISTINCT action FROM audit_logs ORDER BY action'
            );
            res.json(rows.map(r => r.action));
        } catch (err) {
            console.error('Audit log actions error:', err);
            res.status(500).json({ error: 'Failed to fetch actions' });
        }
    });

    return router;
}

module.exports = createAuditLogRouter;