const express = require('express');
const router = express.Router();
const { executeQuery } = require('../config/database');
const { authenticateToken, requireManager } = require('../middleware/auth');
const { validateId, handleValidationErrors } = require('../middleware/validation');

// Get users assigned to a project
router.get('/:projectId/users', authenticateToken, validateId('projectId'), handleValidationErrors, async (req, res) => {
  try {
    const projectId = req.params.projectId;
    
    // Verify project exists and user has access
    const projectQuery = `
      SELECT p.id, p.name, p.company_id 
      FROM projects p 
      WHERE p.id = ?
    `;
    const project = await executeQuery(projectQuery, [projectId]);
    
    if (project.length === 0) {
      return res.status(404).json({ success: false, message: 'Project not found' });
    }
    
    // Check if user has access to this project
    if (req.user.role !== 'main_admin') {
      if (req.user.company_id !== project[0].company_id) {
        return res.status(403).json({ success: false, message: 'You do not have access to this project' });
      }
    }
    
    // Get assigned users
    const query = `
      SELECT 
        u.id, 
        u.username,
        u.first_name, 
        u.last_name, 
        u.phone_number,
        u.role,
        u.permission_level,
        pu.role as project_role,
        pu.joined_at as assigned_at
      FROM project_members pu
      JOIN users u ON pu.user_id = u.id
      WHERE pu.project_id = ?
      ORDER BY pu.joined_at DESC
    `;
    
    const users = await executeQuery(query, [projectId]);
    
    res.json({ success: true, users });
  } catch (error) {
    console.error('Error fetching project users:', error);
    res.status(500).json({ success: false, message: 'Failed to fetch project users' });
  }
});

// Assign user to project
router.post('/:projectId/users', authenticateToken, requireManager, validateId('projectId'), handleValidationErrors, async (req, res) => {
  try {
    const projectId = req.params.projectId;
    const { userId, role = 'user' } = req.body;

    const validProjectRoles = new Set(['main_admin', 'admin', 'user']);
    const assignedRole = role === 'member' ? 'user' : role;
    if (!validProjectRoles.has(assignedRole)) {
      return res.status(400).json({ success: false, message: 'Invalid project role specified' });
    }

    if (assignedRole !== 'user' && req.user.role !== 'main_admin') {
      return res.status(403).json({ success: false, message: 'Only main admins can assign admin-level project roles' });
    }
    
    if (!userId) {
      return res.status(400).json({ success: false, message: 'User ID is required' });
    }
    
    // Verify project exists and user has access
    const projectQuery = `
      SELECT p.id, p.name, p.company_id 
      FROM projects p 
      WHERE p.id = ?
    `;
    const project = await executeQuery(projectQuery, [projectId]);
    
    if (project.length === 0) {
      return res.status(404).json({ success: false, message: 'Project not found' });
    }
    
    // Verify user exists and is in the same company
    const userQuery = `
      SELECT id, company_id 
      FROM users 
      WHERE id = ? AND status = 'active'
    `;
    const user = await executeQuery(userQuery, [userId]);
    
    if (user.length === 0) {
      return res.status(404).json({ success: false, message: 'User not found or inactive' });
    }
    
    // Ensure user is in the same company as the project
    if (user[0].company_id !== project[0].company_id) {
      return res.status(400).json({ success: false, message: 'User must be in the same company as the project' });
    }
    
    // Check if user is already assigned to project
    const existingAssignment = await executeQuery(
      'SELECT 1 FROM project_members WHERE project_id = ? AND user_id = ?', 
      [projectId, userId]
    );
    
    if (existingAssignment.length > 0) {
      return res.status(400).json({ success: false, message: 'User is already assigned to this project' });
    }
    
    // Assign user to project
    const insertQuery = `
      INSERT INTO project_members (project_id, user_id, role, joined_at, assigned_by)
      VALUES (?, ?, ?, NOW(), ?)
    `;
    
    await executeQuery(insertQuery, [projectId, userId, assignedRole, req.user.id]);
    
    // Get updated user details
    const updatedUser = await executeQuery(`
      SELECT 
        u.id, 
        u.username,
        u.first_name, 
        u.last_name, 
        u.phone_number,
        u.role,
        u.permission_level,
        pu.role as project_role,
        pu.joined_at as assigned_at
      FROM project_members pu
      JOIN users u ON pu.user_id = u.id
      WHERE pu.project_id = ? AND pu.user_id = ?
    `, [projectId, userId]);
    
    res.status(201).json({
      success: true,
      message: 'User assigned to project successfully',
      user: updatedUser[0]
    });
  } catch (error) {
    console.error('Error assigning user to project:', error);
    res.status(500).json({ success: false, message: 'Failed to assign user to project' });
  }
});

// Update user role in project
router.put('/:projectId/users/:userId', authenticateToken, requireManager, validateId('projectId'), validateId('userId'), handleValidationErrors, async (req, res) => {
  try {
    const projectId = req.params.projectId;
    const userId = req.params.userId;
    const { role } = req.body;
    
    if (!role) {
      return res.status(400).json({ success: false, message: 'Role is required' });
    }
    
    // Verify project exists
    const project = await executeQuery('SELECT id, company_id FROM projects WHERE id = ?', [projectId]);
    
    if (project.length === 0) {
      return res.status(404).json({ success: false, message: 'Project not found' });
    }
    
    // Check if user is assigned to project
    const existingAssignment = await executeQuery(
      'SELECT 1 FROM project_members WHERE project_id = ? AND user_id = ?', 
      [projectId, userId]
    );
    
    if (existingAssignment.length === 0) {
      return res.status(404).json({ success: false, message: 'User is not assigned to this project' });
    }
    
    // Update user role
    const updateQuery = `
      UPDATE project_members
      SET role = ?, updated_at = NOW()
      WHERE project_id = ? AND user_id = ?
    `;
    
    const validProjectRoles = new Set(['main_admin', 'admin', 'user']);
    const updatedRole = role === 'member' ? 'user' : role;
    if (!validProjectRoles.has(updatedRole)) {
      return res.status(400).json({ success: false, message: 'Invalid project role specified' });
    }

    if (updatedRole !== 'user' && req.user.role !== 'main_admin') {
      return res.status(403).json({ success: false, message: 'Only main admins can assign admin-level project roles' });
    }

    await executeQuery(updateQuery, [updatedRole, projectId, userId]);
    
    // Get updated user details
    const updatedUser = await executeQuery(`
      SELECT 
        u.id, 
        u.username,
        u.first_name, 
        u.last_name, 
        u.phone_number,
        u.role,
        u.permission_level,
        pu.role as project_role,
        pu.joined_at as assigned_at
      FROM project_members pu
      JOIN users u ON pu.user_id = u.id
      WHERE pu.project_id = ? AND pu.user_id = ?
    `, [projectId, userId]);
    
    res.json({
      success: true,
      message: 'User role updated successfully',
      user: updatedUser[0]
    });
  } catch (error) {
    console.error('Error updating user role:', error);
    res.status(500).json({ success: false, message: 'Failed to update user role' });
  }
});

// Remove user from project
router.delete('/:projectId/users/:userId', authenticateToken, requireManager, validateId('projectId'), validateId('userId'), handleValidationErrors, async (req, res) => {
  try {
    const projectId = req.params.projectId;
    const userId = req.params.userId;
    
    // Verify project exists
    const project = await executeQuery('SELECT id, company_id FROM projects WHERE id = ?', [projectId]);
    
    if (project.length === 0) {
      return res.status(404).json({ success: false, message: 'Project not found' });
    }
    
    // Check if user is assigned to project
    const existingAssignment = await executeQuery(
      'SELECT role FROM project_members WHERE project_id = ? AND user_id = ?', 
      [projectId, userId]
    );
    
    if (existingAssignment.length === 0) {
      return res.status(404).json({ success: false, message: 'User is not assigned to this project' });
    }
    
    // Prevent removing the last admin-level member
    if (existingAssignment[0].role === 'admin' || existingAssignment[0].role === 'main_admin') {
      const adminCountRows = await executeQuery(
        'SELECT COUNT(*) as count FROM project_members WHERE project_id = ? AND role IN (?, ?)',
        [projectId, 'admin', 'main_admin']
      );
      
      if (adminCountRows[0].count <= 1) {
        return res.status(400).json({ success: false, message: 'Cannot remove the last admin from the project' });
      }
    }
    
    // Remove user from project
    await executeQuery(
      'DELETE FROM project_members WHERE project_id = ? AND user_id = ?',
      [projectId, userId]
    );
    
    res.json({ success: true, message: 'User removed from project successfully' });
  } catch (error) {
    console.error('Error removing user from project:', error);
    res.status(500).json({ success: false, message: 'Failed to remove user from project' });
  }
});

// Get projects assigned to a specific user
router.get('/user/:userId/projects', authenticateToken, async (req, res) => {
  try {
    let userId = req.params.userId;
    
    // Handle "me" parameter
    if (userId === 'me') {
      userId = req.user.id.toString();
    } else {
      // Validate userId is a positive integer
      const userIdNum = parseInt(userId);
      if (isNaN(userIdNum) || userIdNum <= 0) {
        return res.status(400).json({
          success: false,
          message: 'Validation failed',
          errors: [{
            type: 'field',
            value: userId,
            msg: 'userId must be a positive integer or "me"',
            path: 'userId',
            location: 'params'
          }]
        });
      }
      userId = userIdNum.toString();
    }
    
    // Users can only view their own projects unless they are admins
    if (req.user.id !== parseInt(userId) && 
        req.user.role !== 'main_admin' && 
        req.user.role !== 'admin') {
      return res.status(403).json({ message: 'You do not have permission to view this user\'s projects' });
    }
    
    // Admins can only view projects for users in their company
    if (req.user.role === 'admin') {
      const userCompany = await executeQuery('SELECT company_id FROM users WHERE id = ?', [userId]);
      if (userCompany.length === 0) {
        return res.status(404).json({ message: 'User not found' });
      }
      
      if (userCompany[0].company_id !== req.user.company_id) {
        return res.status(403).json({ message: 'You do not have permission to view this user\'s projects' });
      }
    }

    // Get projects assigned to the user
    const query = `
      SELECT 
        p.id,
        p.name,
        p.description,
        p.status,
        p.priority,
        p.start_date,
        p.end_date,
        p.budget,
        p.owner_id,
        p.created_by,
        p.company_id,
        p.created_at,
        p.updated_at,
        pu.role as user_role,
        pu.joined_at as assigned_at,
        owner.first_name as owner_first_name,
        owner.last_name as owner_last_name
      FROM project_members pu
      JOIN projects p ON pu.project_id = p.id
      LEFT JOIN users owner ON p.owner_id = owner.id
      WHERE pu.user_id = ?
      ORDER BY pu.joined_at DESC
    `;
    
    const projects = await executeQuery(query, [userId]);
    
    res.json({
      success: true,
      projects: projects
    });
  } catch (error) {
    console.error('Error fetching user projects:', error);
    res.status(500).json({ message: 'Failed to fetch user projects' });
  }
});

// Get projects assigned to the current user (convenience endpoint)
router.get('/user/me/projects', authenticateToken, async (req, res) => {
  try {
    const userId = req.user.id;

    // Get projects assigned to the user
    const query = `
      SELECT 
        p.id,
        p.name,
        p.description,
        p.status,
        p.priority,
        p.start_date,
        p.end_date,
        p.budget,
        p.owner_id,
        p.created_by,
        p.company_id,
        p.created_at,
        p.updated_at,
        pu.role as user_role,
        pu.joined_at as assigned_at,
        owner.first_name as owner_first_name,
        owner.last_name as owner_last_name
      FROM project_members pu
      JOIN projects p ON pu.project_id = p.id
      LEFT JOIN users owner ON p.owner_id = owner.id
      WHERE pu.user_id = ?
      ORDER BY pu.joined_at DESC
    `;
    
    const projects = await executeQuery(query, [userId]);
    
    res.json({
      success: true,
      projects: projects
    });
  } catch (error) {
    console.error('Error fetching user projects:', error);
    res.status(500).json({ message: 'Failed to fetch user projects' });
  }
});

// Bulk assign users to project
router.post('/:projectId/users/bulk', authenticateToken, requireManager, validateId('projectId'), handleValidationErrors, async (req, res) => {
  try {
    const projectId = req.params.projectId;
    const { userIds, role = 'member' } = req.body;
    
    if (!userIds || !Array.isArray(userIds) || userIds.length === 0) {
      return res.status(400).json({ message: 'User IDs array is required' });
    }
    
    // Verify project exists and user has access
    const projectQuery = `
      SELECT p.id, p.name, p.company_id 
      FROM projects p 
      WHERE p.id = ?
    `;
    const project = await executeQuery(projectQuery, [projectId]);
    
    if (project.length === 0) {
      return res.status(404).json({ message: 'Project not found' });
    }
    
    // Check if user has access to this project
    if (req.user.role !== 'main_admin' && req.user.role !== 'admin') {
      return res.status(403).json({ message: 'You do not have permission to assign users to this project' });
    }
    
    const results = [];
    const errors = [];
    
    for (const userId of userIds) {
      try {
        // Verify user exists and is in the same company
        const userQuery = `
          SELECT id, company_id 
          FROM users 
          WHERE id = ? AND status = 'active'
        `;
        const user = await executeQuery(userQuery, [userId]);
        
        if (user.length === 0) {
          errors.push({ userId, error: 'User not found or inactive' });
          continue;
        }
        
        // Check if user belongs to the same company (unless main_admin)
        if (req.user.role !== 'main_admin' && user[0].company_id !== req.user.company_id) {
          errors.push({ userId, error: 'User must belong to the same company' });
          continue;
        }
        
        // Check if user is already assigned to project
        const existingAssignment = await executeQuery(
          'SELECT 1 FROM project_members WHERE project_id = ? AND user_id = ?', 
          [projectId, userId]
        );
        
        if (existingAssignment.length > 0) {
          errors.push({ userId, error: 'User is already assigned to this project' });
          continue;
        }
        
        // Assign user to project
        const insertQuery = `
          INSERT INTO project_members (project_id, user_id, role, joined_at, assigned_by)
          VALUES (?, ?, ?, NOW(), ?)
        `;
        
        // Map frontend roles to backend roles
        const bulkRole = role === 'member' ? 'user' : role;
        if (!['main_admin', 'admin', 'user'].includes(bulkRole)) {
          errors.push({ userId, error: 'Invalid project role specified' });
          continue;
        }
        if (bulkRole !== 'user' && req.user.role !== 'main_admin') {
          errors.push({ userId, error: 'Only main admins can assign admin-level project roles' });
          continue;
        }
        await executeQuery(insertQuery, [projectId, userId, bulkRole, req.user.id]);
        results.push({ userId, success: true });
        
      } catch (error) {
        console.error(`Error assigning user ${userId} to project ${projectId}:`, error);
        errors.push({ userId, error: 'Failed to assign user to project' });
      }
    }
    
    res.status(201).json({
      success: true,
      message: `Successfully assigned ${results.length} users to project`,
      results: results,
      errors: errors
    });
  } catch (error) {
    console.error('Error bulk assigning users to project:', error);
    res.status(500).json({ message: 'Failed to bulk assign users to project' });
  }
});

module.exports = router;
