cve-dashboard/backend/routes/users.js

// User Management Routes (Admin only)
const express = require('express');
const bcrypt = require('bcryptjs');

function createUsersRouter(db, requireAuth, requireGroup, logAudit) {
    const router = express.Router();

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

    // Get all users
    router.get('/', async (req, res) => {
        try {
            const users = await new Promise((resolve, reject) => {
                db.all(
                    `SELECT id, username, email, user_group AS 'group', is_active, created_at, last_login
                     FROM users ORDER BY created_at DESC`,
                    (err, rows) => {
                        if (err) reject(err);
                        else resolve(rows);
                    }
                );
            });
            res.json(users);
        } catch (err) {
            console.error('Get users error:', err);
            res.status(500).json({ error: 'Failed to fetch users' });
        }
    });

    // Get single user
    router.get('/:id', async (req, res) => {
        try {
            const user = await new Promise((resolve, reject) => {
                db.get(
                    `SELECT id, username, email, user_group AS 'group', is_active, created_at, last_login
                     FROM users WHERE id = ?`,
                    [req.params.id],
                    (err, row) => {
                        if (err) reject(err);
                        else resolve(row);
                    }
                );
            });

            if (!user) {
                return res.status(404).json({ error: 'User not found' });
            }

            res.json(user);
        } catch (err) {
            console.error('Get user error:', err);
            res.status(500).json({ error: 'Failed to fetch user' });
        }
    });

    // Create new user
    router.post('/', async (req, res) => {
        const { username, email, password, group } = req.body;
        const VALID_GROUPS = ['Admin', 'Standard_User', 'Leadership', 'Read_Only'];

        if (!username || !email || !password) {
            return res.status(400).json({ error: 'Username, email, and password are required' });
        }

        const userGroup = group || 'Read_Only';

        if (!VALID_GROUPS.includes(userGroup)) {
            return res.status(400).json({ error: 'Invalid group. Must be one of: Admin, Standard_User, Leadership, Read_Only' });
        }

        try {
            const passwordHash = await bcrypt.hash(password, 10);

            const result = await new Promise((resolve, reject) => {
                db.run(
                    `INSERT INTO users (username, email, password_hash, user_group)
                     VALUES (?, ?, ?, ?)`,
                    [username, email, passwordHash, userGroup],
                    function(err) {
                        if (err) reject(err);
                        else resolve({ id: this.lastID });
                    }
                );
            });

            logAudit(db, {
                userId: req.user.id,
                username: req.user.username,
                action: 'user_create',
                entityType: 'user',
                entityId: String(result.id),
                details: { created_username: username, group: userGroup },
                ipAddress: req.ip
            });

            res.status(201).json({
                message: 'User created successfully',
                user: {
                    id: result.id,
                    username,
                    email,
                    group: userGroup
                }
            });
        } catch (err) {
            console.error('Create user error:', err);
            if (err.message.includes('UNIQUE constraint failed')) {
                return res.status(409).json({ error: 'Username or email already exists' });
            }
            res.status(500).json({ error: 'Failed to create user' });
        }
    });

    // Update user
    router.patch('/:id', async (req, res) => {
        const { username, email, password, group, is_active } = req.body;
        const VALID_GROUPS = ['Admin', 'Standard_User', 'Leadership', 'Read_Only'];
        const userId = req.params.id;

        // Validate group if provided
        if (group && !VALID_GROUPS.includes(group)) {
            return res.status(400).json({ error: 'Invalid group. Must be one of: Admin, Standard_User, Leadership, Read_Only' });
        }

        // Prevent admin self-demotion
        if (String(userId) === String(req.user.id) && group && group !== 'Admin') {
            return res.status(400).json({ error: 'Cannot remove your own admin group' });
        }

        // Prevent self-deactivation
        if (String(userId) === String(req.user.id) && is_active === false) {
            return res.status(400).json({ error: 'Cannot deactivate your own account' });
        }

        try {
            // Fetch current user record before update (needed for group change audit)
            const currentUser = await new Promise((resolve, reject) => {
                db.get(
                    'SELECT user_group FROM users WHERE id = ?',
                    [userId],
                    (err, row) => {
                        if (err) reject(err);
                        else resolve(row);
                    }
                );
            });

            if (!currentUser) {
                return res.status(404).json({ error: 'User not found' });
            }

            const updates = [];
            const values = [];

            if (username) {
                updates.push('username = ?');
                values.push(username);
            }
            if (email) {
                updates.push('email = ?');
                values.push(email);
            }
            if (password) {
                const passwordHash = await bcrypt.hash(password, 10);
                updates.push('password_hash = ?');
                values.push(passwordHash);
            }
            if (group) {
                updates.push('user_group = ?');
                values.push(group);
            }
            if (typeof is_active === 'boolean') {
                updates.push('is_active = ?');
                values.push(is_active ? 1 : 0);
            }

            if (updates.length === 0) {
                return res.status(400).json({ error: 'No fields to update' });
            }

            values.push(userId);

            await new Promise((resolve, reject) => {
                db.run(
                    `UPDATE users SET ${updates.join(', ')} WHERE id = ?`,
                    values,
                    function(err) {
                        if (err) reject(err);
                        else resolve({ changes: this.changes });
                    }
                );
            });

            const updatedFields = {};
            if (username) updatedFields.username = username;
            if (email) updatedFields.email = email;
            if (group) updatedFields.group = group;
            if (typeof is_active === 'boolean') updatedFields.is_active = is_active;
            if (password) updatedFields.password_changed = true;

            logAudit(db, {
                userId: req.user.id,
                username: req.user.username,
                action: 'user_update',
                entityType: 'user',
                entityId: String(userId),
                details: updatedFields,
                ipAddress: req.ip
            });

            // Log specific audit entry for group changes
            if (group && group !== currentUser.user_group) {
                logAudit(db, {
                    userId: req.user.id,
                    username: req.user.username,
                    action: 'user_group_change',
                    entityType: 'user',
                    entityId: String(userId),
                    details: {
                        previous_group: currentUser.user_group,
                        new_group: group
                    },
                    ipAddress: req.ip
                });
            }

            // If user was deactivated, delete their sessions
            if (is_active === false) {
                await new Promise((resolve) => {
                    db.run('DELETE FROM sessions WHERE user_id = ?', [userId], () => resolve());
                });
            }

            res.json({ message: 'User updated successfully' });
        } catch (err) {
            console.error('Update user error:', err);
            if (err.message.includes('UNIQUE constraint failed')) {
                return res.status(409).json({ error: 'Username or email already exists' });
            }
            res.status(500).json({ error: 'Failed to update user' });
        }
    });

    // Delete user
    router.delete('/:id', async (req, res) => {
        const userId = req.params.id;

        // Prevent self-deletion
        if (String(userId) === String(req.user.id)) {
            return res.status(400).json({ error: 'Cannot delete your own account' });
        }

        try {
            // Look up the user before deleting
            const targetUser = await new Promise((resolve, reject) => {
                db.get('SELECT username FROM users WHERE id = ?', [userId], (err, row) => {
                    if (err) reject(err);
                    else resolve(row);
                });
            });

            // Delete sessions first (foreign key)
            await new Promise((resolve) => {
                db.run('DELETE FROM sessions WHERE user_id = ?', [userId], () => resolve());
            });

            // Delete user
            const result = await new Promise((resolve, reject) => {
                db.run('DELETE FROM users WHERE id = ?', [userId], function(err) {
                    if (err) reject(err);
                    else resolve({ changes: this.changes });
                });
            });

            if (result.changes === 0) {
                return res.status(404).json({ error: 'User not found' });
            }

            logAudit(db, {
                userId: req.user.id,
                username: req.user.username,
                action: 'user_delete',
                entityType: 'user',
                entityId: String(userId),
                details: { deleted_username: targetUser ? targetUser.username : 'unknown' },
                ipAddress: req.ip
            });

            res.json({ message: 'User deleted successfully' });
        } catch (err) {
            console.error('Delete user error:', err);
            res.status(500).json({ error: 'Failed to delete user' });
        }
    });

    return router;
}

module.exports = createUsersRouter;