cve-dashboard/backend/routes/auditLog.js

// Audit Log Routes (Admin only)
const express = require('express');

function createAuditLogRouter(db, requireAuth, requireRole) {
    const router = express.Router();

    // All routes require admin role
    router.use(requireAuth(db), requireRole('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 = [];

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

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

        try {
            // Get total count
            const countRow = await new Promise((resolve, reject) => {
                db.get(
                    `SELECT COUNT(*) as total FROM audit_logs ${whereClause}`,
                    params,
                    (err, row) => {
                        if (err) reject(err);
                        else resolve(row);
                    }
                );
            });

            // Get paginated results
            const rows = await new Promise((resolve, reject) => {
                db.all(
                    `SELECT * FROM audit_logs ${whereClause} ORDER BY created_at DESC LIMIT ? OFFSET ?`,
                    [...params, pageSize, offset],
                    (err, rows) => {
                        if (err) reject(err);
                        else resolve(rows);
                    }
                );
            });

            res.json({
                logs: rows,
                pagination: {
                    page: parseInt(page),
                    limit: pageSize,
                    total: countRow.total,
                    totalPages: Math.ceil(countRow.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 new Promise((resolve, reject) => {
                db.all(
                    'SELECT DISTINCT action FROM audit_logs ORDER BY action',
                    (err, rows) => {
                        if (err) reject(err);
                        else resolve(rows);
                    }
                );
            });

            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;