const express = require('express');
const bcrypt = require('bcryptjs');
const { body, validationResult, param } = require('express-validator');
const { getDb, returnToPool } = require('../database/init');
const { authenticateToken, requireAdmin, requireOwnershipOrAdmin } = require('../middleware/auth');
const { uploadUserAvatar, handleUploadError, deleteFile, getFileUrl } = require('../middleware/upload');
const path = require('path');

const router = express.Router();

// Validation rules
const createUserValidation = [
  body('name').trim().isLength({ min: 2 }).withMessage('Name must be at least 2 characters'),
  body('email').isEmail().normalizeEmail().withMessage('Valid email is required'),
  body('password').isLength({ min: 6 }).withMessage('Password must be at least 6 characters'),
  body('phone').trim().isLength({ min: 10 }).withMessage('Valid phone number required'),
  body('role').isIn(['admin', 'client']).withMessage('Role must be admin or client'),
  body('status').optional().isIn(['active', 'inactive']).withMessage('Status must be active or inactive')
];

const updateUserValidation = [
  body('name').optional().trim().isLength({ min: 2 }).withMessage('Name must be at least 2 characters'),
  body('email').optional().isEmail().normalizeEmail().withMessage('Valid email is required'),
  body('phone').optional().trim().isLength({ min: 10 }).withMessage('Valid phone number required'),
  body('role').optional().isIn(['admin', 'client']).withMessage('Role must be admin or client'),
  body('status').optional().isIn(['active', 'inactive']).withMessage('Status must be active or inactive')
];

const updateProfileValidation = [
  body('name').optional().trim().isLength({ min: 2 }).withMessage('Name must be at least 2 characters'),
  body('phone').optional().trim().isLength({ min: 10 }).withMessage('Valid phone number required')
];

const changePasswordValidation = [
  body('currentPassword').notEmpty().withMessage('Current password is required'),
  body('newPassword').isLength({ min: 6 }).withMessage('New password must be at least 6 characters')
];

const resetPasswordValidation = [
  body('newPassword').isLength({ min: 6 }).withMessage('New password must be at least 6 characters')
];

const idValidation = [
  param('id').isInt({ min: 1 }).withMessage('Valid user ID is required')
];

// GET /users (admin only)
router.get('/', authenticateToken, requireAdmin, async (req, res) => {
  let connection;
  try {
    const { page = 1, limit = 10, role, status, search } = req.query;
    connection = await getDb();

    let query = 'SELECT id, name, email, phone, role, status, avatar, created_at, updated_at FROM users';
    const conditions = [];
    const params = [];

    if (role) { conditions.push('role = ?'); params.push(role); }
    if (status) { conditions.push('status = ?'); params.push(status); }
    if (search) {
      conditions.push('(name LIKE ? OR email LIKE ? OR phone LIKE ?)');
      params.push(`%${search}%`, `%${search}%`, `%${search}%`);
    }

    if (conditions.length > 0) {
      query += ' WHERE ' + conditions.join(' AND ');
    }

    query += ' ORDER BY created_at DESC LIMIT ? OFFSET ?';
    params.push(parseInt(limit), (page - 1) * limit);

    const [users] = await connection.query(query, params);

    // Count
    let countQuery = 'SELECT COUNT(*) AS total FROM users';
    const countParams = params.slice(0, -2);
    if (conditions.length > 0) countQuery += ' WHERE ' + conditions.join(' AND ');
    const [[{ total }]] = await connection.query(countQuery, countParams);

    returnToPool(connection);

    res.json({
      users: users.map(u => ({ ...u, avatar: u.avatar ? getFileUrl(req, u.avatar, 'users') : null })),
      pagination: {
        page: +page,
        limit: +limit,
        total,
        pages: Math.ceil(total / limit)
      }
    });
  } catch (err) {
    if (connection) returnToPool(connection);
    console.error('Fetch error:', err);
    res.status(500).json({ error: 'Server error' });
  }
});

// GET /users/:id
router.get('/:id', authenticateToken, requireOwnershipOrAdmin('id'), idValidation, async (req, res) => {
  let connection;
  try {
    connection = await getDb();
    const [rows] = await connection.query(
      'SELECT id, name, email, phone, role, status, avatar, created_at, updated_at FROM users WHERE id = ?',
      [req.params.id]
    );
    returnToPool(connection);

    if (!rows.length) return res.status(404).json({ error: 'User not found' });
    const user = rows[0];
    res.json({ ...user, avatar: user.avatar ? getFileUrl(req, user.avatar, 'users') : null });
  } catch (err) {
    if (connection) returnToPool(connection);
    res.status(500).json({ error: 'Server error' });
  }
});

// POST /users
router.post('/', authenticateToken, requireAdmin, createUserValidation, async (req, res) => {
  let connection;
  try {
    const errors = validationResult(req);
    if (!errors.isEmpty()) return res.status(400).json({ errors: errors.array() });

    const { name, email, password, phone, role, status = 'active' } = req.body;
    const hashedPassword = await bcrypt.hash(password, parseInt(process.env.BCRYPT_ROUNDS) || 12);

    connection = await getDb();
    const [[existing]] = await connection.query('SELECT id FROM users WHERE email = ?', [email]);
    if (existing) return res.status(409).json({ error: 'Email already exists' });

    const [result] = await connection.query(
      'INSERT INTO users (name, email, password, phone, role, status, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, NOW(), NOW())',
      [name, email, hashedPassword, phone, role, status]
    );

    const [[newUser]] = await connection.query(
      'SELECT id, name, email, phone, role, status, avatar, created_at, updated_at FROM users WHERE id = ?',
      [result.insertId]
    );

    returnToPool(connection);

    res.status(201).json({
      message: 'User created successfully',
      user: {
        ...newUser,
        avatar: newUser.avatar ? getFileUrl(req, newUser.avatar, 'users') : null
      }
    });
  } catch (err) {
    if (connection) returnToPool(connection);
    res.status(500).json({ error: 'Server error' });
  }
});

// PUT /users/:id
router.put('/:id', authenticateToken, uploadUserAvatar, handleUploadError, [...idValidation, ...updateUserValidation], async (req, res) => {
  let connection;
  try {
    const errors = validationResult(req);
    if (!errors.isEmpty()) {
      if (req.file) deleteFile(req.file.path);
      return res.status(400).json({ errors: errors.array() });
    }

    const { id } = req.params;
    const { name, email, phone, role, status } = req.body;
    const isAdmin = req.user.role === 'admin';
    const isOwner = req.user.id === parseInt(id);

    if (!isAdmin && !isOwner) {
      if (req.file) deleteFile(req.file.path);
      return res.status(403).json({ error: 'Access denied' });
    }

    if (!isAdmin && (role || status)) {
      if (req.file) deleteFile(req.file.path);
      return res.status(403).json({ error: 'Cannot modify role or status' });
    }

    connection = await getDb();
    const [[existingUser]] = await connection.query('SELECT * FROM users WHERE id = ?', [id]);
    if (!existingUser) {
      if (req.file) deleteFile(req.file.path);
      return res.status(404).json({ error: 'User not found' });
    }

    if (email && email !== existingUser.email) {
      const [[emailExists]] = await connection.query('SELECT id FROM users WHERE email = ? AND id != ?', [email, id]);
      if (emailExists) {
        if (req.file) deleteFile(req.file.path);
        return res.status(409).json({ error: 'Email already exists' });
      }
    }

    let avatar = existingUser.avatar;
    if (req.file) {
      if (avatar) deleteFile(path.join(__dirname, '..', 'uploads', 'users', avatar));
      avatar = req.file.filename;
    }

    const updates = [];
    const values = [];
    if (name !== undefined) { updates.push('name = ?'); values.push(name); }
    if (email !== undefined) { updates.push('email = ?'); values.push(email); }
    if (phone !== undefined) { updates.push('phone = ?'); values.push(phone); }
    if (isAdmin && role !== undefined) { updates.push('role = ?'); values.push(role); }
    if (isAdmin && status !== undefined) { updates.push('status = ?'); values.push(status); }
    if (avatar !== existingUser.avatar) { updates.push('avatar = ?'); values.push(avatar); }
    updates.push('updated_at = NOW()');
    values.push(id);

    await connection.query(`UPDATE users SET ${updates.join(', ')} WHERE id = ?`, values);
    const [[updated]] = await connection.query('SELECT id, name, email, phone, role, status, avatar, created_at, updated_at FROM users WHERE id = ?', [id]);
    returnToPool(connection);

    res.json({
      message: 'User updated',
      user: { ...updated, avatar: updated.avatar ? getFileUrl(req, updated.avatar, 'users') : null }
    });
  } catch (err) {
    if (connection) returnToPool(connection);
    if (req.file) deleteFile(req.file.path);
    res.status(500).json({ error: 'Server error' });
  }
});

// PATCH /users/profile
router.patch('/profile', authenticateToken, uploadUserAvatar, handleUploadError, updateProfileValidation, async (req, res) => {
  let connection;
  try {
    const errors = validationResult(req);
    if (!errors.isEmpty()) {
      if (req.file) deleteFile(req.file.path);
      return res.status(400).json({ errors: errors.array() });
    }

    const { name, phone } = req.body;
    const userId = req.user.id;
    connection = await getDb();
    const [[existingUser]] = await connection.query('SELECT * FROM users WHERE id = ?', [userId]);
    if (!existingUser) {
      if (req.file) deleteFile(req.file.path);
      return res.status(404).json({ error: 'User not found' });
    }

    let avatar = existingUser.avatar;
    if (req.file) {
      if (avatar) deleteFile(path.join(__dirname, '..', 'uploads', 'users', avatar));
      avatar = req.file.filename;
    }

    await connection.query(
      'UPDATE users SET name = ?, phone = ?, avatar = ?, updated_at = NOW() WHERE id = ?',
      [name || existingUser.name, phone || existingUser.phone, avatar, userId]
    );

    const [[updatedUser]] = await connection.query('SELECT id, name, email, phone, role, status, avatar, created_at, updated_at FROM users WHERE id = ?', [userId]);
    returnToPool(connection);

    res.json({
      message: 'Profile updated',
      user: { ...updatedUser, avatar: updatedUser.avatar ? getFileUrl(req, updatedUser.avatar, 'users') : null }
    });
  } catch (err) {
    if (connection) returnToPool(connection);
    if (req.file) deleteFile(req.file.path);
    res.status(500).json({ error: 'Server error' });
  }
});

// PATCH /users/:id/password
router.patch('/:id/password', authenticateToken, requireOwnershipOrAdmin('id'), [...idValidation, ...changePasswordValidation], async (req, res) => {
  let connection;
  try {
    const { id } = req.params;
    const { currentPassword, newPassword } = req.body;
    connection = await getDb();
    const [[user]] = await connection.query('SELECT * FROM users WHERE id = ?', [id]);
    if (!user) return res.status(404).json({ error: 'User not found' });

    if (req.user.role !== 'admin' || req.user.id === parseInt(id)) {
      const valid = await bcrypt.compare(currentPassword, user.password);
      if (!valid) return res.status(400).json({ error: 'Current password is incorrect' });
    }

    const hashed = await bcrypt.hash(newPassword, parseInt(process.env.BCRYPT_ROUNDS) || 12);
    await connection.query('UPDATE users SET password = ?, updated_at = NOW() WHERE id = ?', [hashed, id]);
    returnToPool(connection);
    res.json({ message: 'Password updated' });
  } catch (err) {
    if (connection) returnToPool(connection);
    res.status(500).json({ error: 'Server error' });
  }
});

// PATCH /users/:id/reset-password
router.patch('/:id/reset-password', authenticateToken, requireAdmin, [...idValidation, ...resetPasswordValidation], async (req, res) => {
  let connection;
  try {
    const { id } = req.params;
    const { newPassword } = req.body;
    const hashed = await bcrypt.hash(newPassword, parseInt(process.env.BCRYPT_ROUNDS) || 12);
    connection = await getDb();
    await connection.query('UPDATE users SET password = ?, updated_at = NOW() WHERE id = ?', [hashed, id]);
    returnToPool(connection);
    res.json({ message: 'Password reset successfully' });
  } catch (err) {
    if (connection) returnToPool(connection);
    res.status(500).json({ error: 'Server error' });
  }
});

// PATCH /users/:id/status
router.patch('/:id/status', authenticateToken, requireAdmin, idValidation, async (req, res) => {
  let connection;
  try {
    const { id } = req.params;
    const { status } = req.body;
    if (!['active', 'inactive'].includes(status)) return res.status(400).json({ error: 'Invalid status' });

    if (req.user.id === parseInt(id) && status === 'inactive') {
      return res.status(400).json({ error: 'Cannot deactivate your own account' });
    }

    connection = await getDb();
    await connection.query('UPDATE users SET status = ?, updated_at = NOW() WHERE id = ?', [status, id]);
    returnToPool(connection);
    res.json({ message: 'Status updated' });
  } catch (err) {
    if (connection) returnToPool(connection);
    res.status(500).json({ error: 'Server error' });
  }
});

// DELETE /users/:id
router.delete('/:id', authenticateToken, requireAdmin, idValidation, async (req, res) => {
  let connection;
  try {
    const { id } = req.params;
    if (req.user.id === parseInt(id)) return res.status(400).json({ error: 'Cannot delete own account' });

    connection = await getDb();
    const [[user]] = await connection.query('SELECT * FROM users WHERE id = ?', [id]);
    if (!user) return res.status(404).json({ error: 'User not found' });

    const [[{ count }]] = await connection.query('SELECT COUNT(*) as count FROM orders WHERE user_id = ?', [id]);
    if (count > 0) return res.status(400).json({ error: 'User has orders. Deactivate instead.' });

    await connection.query('DELETE FROM users WHERE id = ?', [id]);
    returnToPool(connection);

    if (user.avatar) deleteFile(path.join(__dirname, '..', 'uploads', 'users', user.avatar));
    res.json({ message: 'User deleted successfully' });
  } catch (err) {
    if (connection) returnToPool(connection);
    res.status(500).json({ error: 'Server error' });
  }
});

// GET /users/stats/overview
router.get('/stats/overview', authenticateToken, requireAdmin, async (req, res) => {
  let connection;
  try {
    connection = await getDb();
    const [[{ totalUsers }]] = await connection.query('SELECT COUNT(*) as totalUsers FROM users');
    const [byRole] = await connection.query('SELECT role, COUNT(*) as count FROM users GROUP BY role');
    const [byStatus] = await connection.query('SELECT status, COUNT(*) as count FROM users GROUP BY status');
    const [[{ recentRegistrations }]] = await connection.query('SELECT COUNT(*) as recentRegistrations FROM users WHERE created_at >= NOW() - INTERVAL 7 DAY');
    returnToPool(connection);

    res.json({ totalUsers, usersByRole: byRole, usersByStatus: byStatus, recentRegistrations });
  } catch (err) {
    if (connection) returnToPool(connection);
    res.status(500).json({ error: 'Server error' });
  }
});

// GET /users/profile
router.get('/profile', authenticateToken, async (req, res) => {
  let connection;
  try {
    connection = await getDb();
    const [[user]] = await connection.query(
      'SELECT id, name, email, phone, role, status, avatar, created_at, updated_at FROM users WHERE id = ?',
      [req.user.id]
    );
    returnToPool(connection);
    if (!user) return res.status(404).json({ error: 'User not found' });
    res.json({ ...user, avatar_url: user.avatar ? getFileUrl(req, user.avatar, 'users') : null });
  } catch (err) {
    if (connection) returnToPool(connection);
    res.status(500).json({ error: 'Server error' });
  }
});

module.exports = router;

// // Update user (admin only for all fields, users can only update their own profile)
// router.put('/:id', authenticateToken, uploadUserAvatar, handleUploadError, [...idValidation, ...updateUserValidation], async (req, res) => {
//   let db;
//   try {
//     const errors = validationResult(req);
//     if (!errors.isEmpty()) {
//       if (req.file) {
//         deleteFile(req.file.path);
//       }
//       return res.status(400).json({ errors: errors.array() });
//     }

//     const { id } = req.params;
//     const { name, email, phone, role, status } = req.body;
//     const isAdmin = req.user.role === 'admin';
//     const isOwner = req.user.id === parseInt(id);

//     // Check permissions
//     if (!isAdmin && !isOwner) {
//       if (req.file) {
//         deleteFile(req.file.path);
//       }
//       return res.status(403).json({ error: 'Access denied' });
//     }

//     // Non-admin users can only update their own basic profile
//     if (!isAdmin && (role || status)) {
//       if (req.file) {
//         deleteFile(req.file.path);
//       }
//       return res.status(403).json({ error: 'Cannot modify role or status' });
//     }

//     db = await getDb();

//     // Check if user exists
//     const existingUser = await new Promise((resolve, reject) => {
//       db.get('SELECT * FROM users WHERE id = ?', [id], (err, row) => {
//         if (err) reject(err);
//         else resolve(row);
//       });
//     });

//     if (!existingUser) {
//       if (req.file) {
//         deleteFile(req.file.path);
//       }
//       returnToPool(db);
//       return res.status(404).json({ error: 'User not found' });
//     }

//     // Check email uniqueness if updating email
//     if (email && email !== existingUser.email) {
//       const emailExists = await new Promise((resolve, reject) => {
//         db.get('SELECT id FROM users WHERE email = ? AND id != ?', [email, id], (err, row) => {
//           if (err) reject(err);
//           else resolve(row);
//         });
//       });

//       if (emailExists) {
//         if (req.file) {
//           deleteFile(req.file.path);
//         }
//         returnToPool(db);
//         return res.status(409).json({ error: 'Email already exists' });
//       }
//     }

//     // Handle avatar update
//     let avatarToUpdate = existingUser.avatar;
//     if (req.file) {
//       // Delete old avatar if exists
//       if (existingUser.avatar) {
//         deleteFile(path.join(__dirname, '..', 'uploads', 'users', existingUser.avatar));
//       }
//       avatarToUpdate = req.file.filename;
//     }

//     // Prepare update fields
//     const updateFields = [];
//     const updateValues = [];

//     if (name !== undefined) {
//       updateFields.push('name = ?');
//       updateValues.push(name);
//     }

//     if (email !== undefined) {
//       updateFields.push('email = ?');
//       updateValues.push(email);
//     }

//     if (phone !== undefined) {
//       updateFields.push('phone = ?');
//       updateValues.push(phone);
//     }

//     if (isAdmin && role !== undefined) {
//       updateFields.push('role = ?');
//       updateValues.push(role);
//     }

//     if (isAdmin && status !== undefined) {
//       updateFields.push('status = ?');
//       updateValues.push(status);
//     }

//     if (avatarToUpdate !== existingUser.avatar) {
//       updateFields.push('avatar = ?');
//       updateValues.push(avatarToUpdate);
//     }

//     // Always update the updated_at timestamp
//     updateFields.push('updated_at = CURRENT_TIMESTAMP');

//     if (updateFields.length === 1) { // Only updated_at
//       returnToPool(db);
//       if (req.file) {
//         deleteFile(req.file.path);
//       }
//       return res.status(400).json({ error: 'No fields to update' });
//     }

//     // Build and execute update query
//     const updateQuery = `UPDATE users SET ${updateFields.join(', ')} WHERE id = ?`;
//     updateValues.push(id);

//     await new Promise((resolve, reject) => {
//       db.run(updateQuery, updateValues, function(err) {
//         if (err) reject(err);
//         else resolve({ changes: this.changes });
//       });
//     });

//     // Get updated user
//     const updatedUser = await new Promise((resolve, reject) => {
//       db.get(
//         'SELECT id, name, email, phone, role, status, avatar, created_at, updated_at FROM users WHERE id = ?',
//         [id],
//         (err, row) => {
//           if (err) reject(err);
//           else resolve(row);
//         }
//       );
//     });

//     returnToPool(db);

//     res.json({
//       message: 'User updated successfully',
//       user: {
//         ...updatedUser,
//         avatar: updatedUser.avatar ? getFileUrl(req, updatedUser.avatar, 'users') : null
//       }
//     });

//   } catch (error) {
//     console.error('Error updating user:', error);
//     if (db) returnToPool(db);
//     if (req.file) {
//       deleteFile(req.file.path);
//     }
//     res.status(500).json({ error: 'Server error' });
//   }
// });

// // Update own profile (authenticated users)
// router.patch('/profile', authenticateToken, uploadUserAvatar, handleUploadError, updateProfileValidation, async (req, res) => {
//   try {
//     const errors = validationResult(req);
//     if (!errors.isEmpty()) {
//       if (req.file) {
//         deleteFile(req.file.path);
//       }
//       return res.status(400).json({ errors: errors.array() });
//     }

//     const { name, phone } = req.body;
//     const userId = req.user.id;
//     const db = getDb();

//     // Get current user
//     const existingUser = await new Promise((resolve, reject) => {
//       db.get('SELECT * FROM users WHERE id = ?', [userId], (err, row) => {
//         if (err) reject(err);
//         else resolve(row);
//       });
//     });

//     if (!existingUser) {
//       if (req.file) {
//         deleteFile(req.file.path);
//       }
//       db.close();
//       return res.status(404).json({ error: 'User not found' });
//     }

//     // Handle avatar update
//     let avatarToUpdate = existingUser.avatar;
//     if (req.file) {
//       // Delete old avatar if exists
//       if (existingUser.avatar) {
//         deleteFile(path.join(__dirname, '..', 'uploads', 'users', existingUser.avatar));
//       }
//       avatarToUpdate = req.file.filename;
//     }

//     // Update user
//     await new Promise((resolve, reject) => {
//       db.run(
//         'UPDATE users SET name = ?, phone = ?, avatar = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?',
//         [
//           name || existingUser.name,
//           phone || existingUser.phone,
//           avatarToUpdate,
//           userId
//         ],
//         function(err) {
//           if (err) reject(err);
//           else resolve();
//         }
//       );
//     });

//     // Get updated user
//     const updatedUser = await new Promise((resolve, reject) => {
//       db.get(
//         'SELECT id, name, email, phone, role, status, avatar, created_at, updated_at FROM users WHERE id = ?',
//         [userId],
//         (err, row) => {
//           if (err) reject(err);
//           else resolve(row);
//         }
//       );
//     });

//     db.close();

//     // Process user data
//     const processedUser = {
//       ...updatedUser,
//       avatar: updatedUser.avatar ? getFileUrl(req, updatedUser.avatar, 'users') : null
//     };

//     res.json({
//       message: 'Profile updated successfully',
//       user: processedUser
//     });

//   } catch (error) {
//     console.error('Error updating profile:', error);
//     if (req.file) {
//       deleteFile(req.file.path);
//     }
//     res.status(500).json({ error: 'Server error' });
//   }
// });

// // Change password
// router.patch('/:id/password', authenticateToken, requireOwnershipOrAdmin('id'), [...idValidation, ...changePasswordValidation], async (req, res) => {
//   try {
//     const errors = validationResult(req);
//     if (!errors.isEmpty()) {
//       return res.status(400).json({ errors: errors.array() });
//     }

//     const { id } = req.params;
//     const { currentPassword, newPassword } = req.body;
//     const db = getDb();

//     // Get user with password
//     const user = await new Promise((resolve, reject) => {
//       db.get('SELECT * FROM users WHERE id = ?', [id], (err, row) => {
//         if (err) reject(err);
//         else resolve(row);
//       });
//     });

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

//     // Verify current password (skip for admin changing other user's password)
//     if (req.user.role !== 'admin' || req.user.id === parseInt(id)) {
//       const isValidPassword = await bcrypt.compare(currentPassword, user.password);
//       if (!isValidPassword) {
//         db.close();
//         return res.status(400).json({ error: 'Current password is incorrect' });
//       }
//     }

//     // Hash new password
//     const saltRounds = parseInt(process.env.BCRYPT_ROUNDS) || 12;
//     const hashedNewPassword = await bcrypt.hash(newPassword, saltRounds);

//     // Update password
//     await new Promise((resolve, reject) => {
//       db.run(
//         'UPDATE users SET password = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?',
//         [hashedNewPassword, id],
//         function(err) {
//           if (err) reject(err);
//           else resolve();
//         }
//       );
//     });

//     db.close();

//     res.json({ message: 'Password updated successfully' });

//   } catch (error) {
//     console.error('Error changing password:', error);
//     res.status(500).json({ error: 'Server error' });
//   }
// });

// // Reset password (admin only)
// router.patch('/:id/reset-password', authenticateToken, requireAdmin, [...idValidation, ...resetPasswordValidation], async (req, res) => {
//   try {
//     const errors = validationResult(req);
//     if (!errors.isEmpty()) {
//       return res.status(400).json({ errors: errors.array() });
//     }

//     const { id } = req.params;
//     const { newPassword } = req.body;
//     const db = getDb();

//     // Check if user exists
//     const user = await new Promise((resolve, reject) => {
//       db.get('SELECT id FROM users WHERE id = ?', [id], (err, row) => {
//         if (err) reject(err);
//         else resolve(row);
//       });
//     });

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

//     // Hash new password
//     const saltRounds = parseInt(process.env.BCRYPT_ROUNDS) || 12;
//     const hashedNewPassword = await bcrypt.hash(newPassword, saltRounds);

//     // Update password
//     await new Promise((resolve, reject) => {
//       db.run(
//         'UPDATE users SET password = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?',
//         [hashedNewPassword, id],
//         function(err) {
//           if (err) reject(err);
//           else resolve();
//         }
//       );
//     });

//     db.close();

//     res.json({ message: 'Password reset successfully' });

//   } catch (error) {
//     console.error('Error resetting password:', error);
//     res.status(500).json({ error: 'Server error' });
//   }
// });

// // Update user status (admin only)
// router.patch('/:id/status', authenticateToken, requireAdmin, idValidation, async (req, res) => {
//   try {
//     const errors = validationResult(req);
//     if (!errors.isEmpty()) {
//       return res.status(400).json({ errors: errors.array() });
//     }

//     const { id } = req.params;
//     const { status } = req.body;

//     if (!['active', 'inactive'].includes(status)) {
//       return res.status(400).json({ error: 'Invalid status. Must be "active" or "inactive"' });
//     }

//     const db = getDb();

//     // Check if user exists
//     const user = await new Promise((resolve, reject) => {
//       db.get('SELECT id FROM users WHERE id = ?', [id], (err, row) => {
//         if (err) reject(err);
//         else resolve(row);
//       });
//     });

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

//     // Prevent admin from deactivating themselves
//     if (req.user.id === parseInt(id) && status === 'inactive') {
//       db.close();
//       return res.status(400).json({ error: 'Cannot deactivate your own account' });
//     }

//     // Update status
//     await new Promise((resolve, reject) => {
//       db.run(
//         'UPDATE users SET status = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?',
//         [status, id],
//         function(err) {
//           if (err) reject(err);
//           else resolve();
//         }
//       );
//     });

//     db.close();

//     res.json({ message: 'User status updated successfully' });

//   } catch (error) {
//     console.error('Error updating user status:', error);
//     res.status(500).json({ error: 'Server error' });
//   }
// });

// // Delete user (admin only, cannot delete self)
// router.delete('/:id', authenticateToken, requireAdmin, idValidation, async (req, res) => {
//   let db;
//   try {
//     const errors = validationResult(req);
//     if (!errors.isEmpty()) {
//       return res.status(400).json({ errors: errors.array() });
//     }

//     const { id } = req.params;

//     // Prevent admin from deleting themselves
//     if (req.user.id === parseInt(id)) {
//       return res.status(400).json({ error: 'Cannot delete your own account' });
//     }

//     db = await getDb();

//     // Get user to delete associated files
//     const user = await new Promise((resolve, reject) => {
//       db.get('SELECT * FROM users WHERE id = ?', [id], (err, row) => {
//         if (err) reject(err);
//         else resolve(row);
//       });
//     });

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

//     // Check for existing orders
//     const userOrders = await new Promise((resolve, reject) => {
//       db.get('SELECT COUNT(*) as count FROM orders WHERE user_id = ?', [id], (err, row) => {
//         if (err) reject(err);
//         else resolve(row.count);
//       });
//     });

//     if (userOrders > 0) {
//       db.close();
//       return res.status(400).json({ 
//         error: 'Cannot delete user with existing orders. Consider deactivating instead.' 
//       });
//     }

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

//     returnToPool(db);

//     // Delete associated avatar file
//     if (user.avatar) {
//       deleteFile(path.join(__dirname, '..', 'uploads', 'users', user.avatar));
//     }

//     res.json({ message: 'User deleted successfully' });

//   } catch (error) {
//     console.error('Error deleting user:', error);
//     res.status(500).json({ error: 'Server error' });
//   }
// });

// // Get user statistics (admin only)
// router.get('/stats/overview', authenticateToken, requireAdmin, async (req, res) => {
//   let db;
//   try {
//     db = await getDb();

//     // Get total users count
//     const totalUsers = await new Promise((resolve, reject) => {
//       db.get('SELECT COUNT(*) as count FROM users', (err, row) => {
//         if (err) reject(err);
//         else resolve(row.count);
//       });
//     });

//     // Get users by role
//     const usersByRole = await new Promise((resolve, reject) => {
//       db.all('SELECT role, COUNT(*) as count FROM users GROUP BY role', (err, rows) => {
//         if (err) reject(err);
//         else resolve(rows);
//       });
//     });

//     // Get users by status
//     const usersByStatus = await new Promise((resolve, reject) => {
//       db.all('SELECT status, COUNT(*) as count FROM users GROUP BY status', (err, rows) => {
//         if (err) reject(err);
//         else resolve(rows);
//       });
//     });

//     // Get recent registrations (last 7 days)
//     const recentRegistrations = await new Promise((resolve, reject) => {
//       db.get(
//         'SELECT COUNT(*) as count FROM users WHERE created_at >= datetime("now", "-7 days")',
//         (err, row) => {
//           if (err) reject(err);
//           else resolve(row.count);
//         }
//       );
//     });

//     returnToPool(db);

//     res.json({
//       totalUsers,
//       usersByRole,
//       usersByStatus,
//       recentRegistrations
//     });

//   } catch (error) {
//     console.error('Error fetching user statistics:', error);
//     if (db) returnToPool(db);
//     res.status(500).json({ error: 'Server error' });
//   }
// });

// // Get current user's profile
// router.get('/profile', authenticateToken, async (req, res) => {
//   try {
//     const userId = req.user.id;
//     const db = getDb();

//     const user = await new Promise((resolve, reject) => {
//       db.get(
//         'SELECT id, name, email, phone, role, status, avatar, created_at, updated_at FROM users WHERE id = ?',
//         [userId],
//         (err, row) => {
//           if (err) reject(err);
//           else resolve(row);
//         }
//       );
//     });

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

//     // Add full avatar URL if avatar exists
//     const responseUser = {
//       ...user,
//       avatar_url: user.avatar ? getFileUrl(req, user.avatar, 'users') : null
//     };

//     db.close();
//     res.json(responseUser);

//   } catch (error) {
//     console.error('Error fetching user profile:', error);
//     res.status(500).json({ error: 'Server error' });
//   }
// });

// module.exports = router;
