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

// Get all sprints
router.get('/', authenticateToken, validatePagination(), handleValidationErrors, async (req, res) => {
  try {
    const page = parseInt(req.query.page) || 1;
    const limit = parseInt(req.query.limit) || 10;
    const offset = (page - 1) * limit;
    const search = req.query.search || '';
    const status = req.query.status || '';
    const project_id = req.query.project_id || '';
    const userId = req.user.id;
    const userRole = req.user.role;

    let whereClause = 'WHERE 1=1';
    let queryParams = [];

    // Non-admin users can only see sprints from projects they're involved in
    if (userRole !== 'admin' && userRole !== 'main_admin') {
      whereClause += ' AND (s.project_id IN (SELECT id FROM projects WHERE owner_id = ?) OR s.project_id IN (SELECT project_id FROM project_members WHERE user_id = ?))';
      queryParams.push(userId, userId);
    }

    if (search) {
      whereClause += ' AND (s.name LIKE ? OR s.description LIKE ?)';
      const searchTerm = `%${search}%`;
      queryParams.push(searchTerm, searchTerm);
    }

    if (status) {
      whereClause += ' AND s.status = ?';
      queryParams.push(status);
    }

    if (project_id) {
      whereClause += ' AND s.project_id = ?';
      queryParams.push(project_id);
    }

    // Get total count
    const countQuery = `
      SELECT COUNT(*) as total 
      FROM sprints s 
      ${whereClause}
    `;
    const countResult = await executeQuery(countQuery, queryParams);
    const total = countResult[0].total;

    // Get sprints with pagination
    const sprintsQuery = `
      SELECT 
        s.id, s.name, s.description, s.status, s.start_date, s.end_date,
        s.created_at, s.updated_at,
        p.name as project_name,
        (SELECT COUNT(*) FROM tasks WHERE sprint_id = s.id) as task_count,
        (SELECT COUNT(*) FROM tasks WHERE sprint_id = s.id AND status = 'done') as completed_tasks,
        (SELECT SUM(story_points) FROM tasks WHERE sprint_id = s.id) as total_story_points,
        (SELECT SUM(story_points) FROM tasks WHERE sprint_id = s.id AND status = 'done') as completed_story_points
      FROM sprints s
      LEFT JOIN projects p ON s.project_id = p.id
      ${whereClause}
      ORDER BY s.created_at DESC
      LIMIT ${limit} OFFSET ${offset}
    `;
    
    const sprints = await executeQuery(sprintsQuery, queryParams);

    res.status(200).json({
      success: true,
      message: 'Sprints retrieved successfully',
      data: {
        sprints,
        pagination: {
          current_page: page,
          per_page: limit,
          total,
          total_pages: Math.ceil(total / limit)
        }
      }
    });
  } catch (error) {
    console.error('Get sprints error:', error);
    res.status(500).json({
      success: false,
      message: 'Failed to retrieve sprints'
    });
  }
});

// Get sprint by ID
router.get('/:id', authenticateToken, validateId(), handleValidationErrors, async (req, res) => {
  try {
    const sprintId = req.params.id;
    const userId = req.user.id;
    const userRole = req.user.role;

    let whereClause = 'WHERE s.id = ?';
    let queryParams = [sprintId];

    // Non-admin users can only see sprints from projects they're involved in
    if (userRole !== 'admin' && userRole !== 'main_admin') {
      whereClause += ' AND (s.project_id IN (SELECT id FROM projects WHERE owner_id = ?) OR s.project_id IN (SELECT project_id FROM project_members WHERE user_id = ?))';
      queryParams.push(userId, userId);
    }

    const sprints = await executeQuery(`
      SELECT 
        s.id, s.name, s.description, s.status, s.start_date, s.end_date,
        s.created_at, s.updated_at,
        p.id as project_id, p.name as project_name
      FROM sprints s
      LEFT JOIN projects p ON s.project_id = p.id
      ${whereClause}
    `, queryParams);

    if (sprints.length === 0) {
      return res.status(404).json({
        success: false,
        message: 'Sprint not found or access denied'
      });
    }

    // Get sprint tasks
    const tasks = await executeQuery(`
      SELECT 
        t.id, t.title, t.status, t.priority, t.type, t.story_points,
        t.estimated_hours, t.actual_hours, t.due_date,
        assignee.first_name as assignee_first_name, assignee.last_name as assignee_last_name
      FROM tasks t
      LEFT JOIN users assignee ON t.assigned_to = assignee.id
      WHERE t.sprint_id = ?
      ORDER BY t.created_at ASC
    `, [sprintId]);

    // Calculate sprint statistics
    const stats = {
      total_tasks: tasks.length,
      completed_tasks: tasks.filter(t => t.status === 'done').length,
      in_progress_tasks: tasks.filter(t => t.status === 'in_progress').length,
      todo_tasks: tasks.filter(t => t.status === 'todo').length,
      total_story_points: tasks.reduce((sum, t) => sum + (t.story_points || 0), 0),
      completed_story_points: tasks.filter(t => t.status === 'done').reduce((sum, t) => sum + (t.story_points || 0), 0),
      total_estimated_hours: tasks.reduce((sum, t) => sum + (t.estimated_hours || 0), 0),
      total_actual_hours: tasks.reduce((sum, t) => sum + (t.actual_hours || 0), 0)
    };

    res.status(200).json({
      success: true,
      message: 'Sprint retrieved successfully',
      data: {
        sprint: {
          ...sprints[0],
          tasks,
          statistics: stats
        }
      }
    });
  } catch (error) {
    console.error('Get sprint error:', error);
    res.status(500).json({
      success: false,
      message: 'Failed to retrieve sprint'
    });
  }
});

// Create new sprint
router.post('/', authenticateToken, validateSprint(), handleValidationErrors, async (req, res) => {
  const connection = await getConnection();
  
  try {
    await connection.beginTransaction();

    const { project_id, name, description, start_date, end_date } = req.body;
    const createdBy = req.user.id;
    const userRole = req.user.role;

    // Verify project exists and user has access
    let projectQuery = 'SELECT id FROM projects WHERE id = ?';
    let projectParams = [project_id];

    if (userRole !== 'admin' && userRole !== 'main_admin') {
      projectQuery += ' AND (owner_id = ? OR id IN (SELECT project_id FROM project_members WHERE user_id = ?))';
      projectParams.push(createdBy, createdBy);
    }

    const [projectExists] = await connection.execute(projectQuery, projectParams);

    if (projectExists.length === 0) {
      await connection.rollback();
      return res.status(400).json({
        success: false,
        message: 'Project not found or access denied'
      });
    }

    // Check for overlapping sprints in the same project
    const [overlappingSprints] = await connection.execute(`
      SELECT id FROM sprints 
      WHERE project_id = ? 
      AND status IN ('planning', 'active') 
      AND (
        (start_date <= ? AND end_date >= ?) OR
        (start_date <= ? AND end_date >= ?) OR
        (start_date >= ? AND end_date <= ?)
      )
    `, [project_id, start_date, start_date, end_date, end_date, start_date, end_date]);

    if (overlappingSprints.length > 0) {
      await connection.rollback();
      return res.status(400).json({
        success: false,
        message: 'Sprint dates overlap with existing active or planned sprints'
      });
    }

    // Create sprint
    const [result] = await connection.execute(
      'INSERT INTO sprints (project_id, name, description, start_date, end_date, status, created_by) VALUES (?, ?, ?, ?, ?, ?, ?)',
      [project_id, name, description, start_date, end_date, 'planning', createdBy]
    );

    const newSprintId = result.insertId;

    // Log activity
    await connection.execute(
      'INSERT INTO activity_logs (user_id, action, entity_type, entity_id, details) VALUES (?, ?, ?, ?, ?)',
      [createdBy, 'create_sprint', 'sprint', newSprintId, JSON.stringify({ name, project_id, start_date, end_date })]
    );

    await connection.commit();

    // Get created sprint details
    const [newSprint] = await connection.execute(`
      SELECT 
        s.id, s.name, s.description, s.status, s.start_date, s.end_date, s.created_at,
        p.name as project_name
      FROM sprints s
      LEFT JOIN projects p ON s.project_id = p.id
      WHERE s.id = ?
    `, [newSprintId]);

    res.status(201).json({
      success: true,
      message: 'Sprint created successfully',
      data: {
        sprint: newSprint[0]
      }
    });
  } catch (error) {
    await connection.rollback();
    console.error('Create sprint error:', error);
    res.status(500).json({
      success: false,
      message: 'Failed to create sprint'
    });
  } finally {
    connection.release();
  }
});

// Update sprint
router.put('/:id', authenticateToken, validateId(), handleValidationErrors, async (req, res) => {
  const connection = await getConnection();
  
  try {
    await connection.beginTransaction();

    const sprintId = req.params.id;
    const { name, description, start_date, end_date, status } = req.body;
    const updatedBy = req.user.id;
    const userRole = req.user.role;

    // Check if sprint exists and user has access
    let sprintQuery = `
      SELECT s.*, p.owner_id as project_owner_id
      FROM sprints s
      JOIN projects p ON s.project_id = p.id
      WHERE s.id = ?
    `;
    let sprintParams = [sprintId];

    if (userRole !== 'admin' && userRole !== 'main_admin') {
      sprintQuery += ' AND (p.owner_id = ? OR p.id IN (SELECT project_id FROM project_members WHERE user_id = ?))';
      sprintParams.push(updatedBy, updatedBy);
    }

    const [existingSprints] = await connection.execute(sprintQuery, sprintParams);

    if (existingSprints.length === 0) {
      await connection.rollback();
      return res.status(404).json({
        success: false,
        message: 'Sprint not found or access denied'
      });
    }

    const existingSprint = existingSprints[0];

    // Check for overlapping sprints if dates are being updated
    if ((start_date && start_date !== existingSprint.start_date) || 
        (end_date && end_date !== existingSprint.end_date)) {
      
      const newStartDate = start_date || existingSprint.start_date;
      const newEndDate = end_date || existingSprint.end_date;

      const [overlappingSprints] = await connection.execute(`
        SELECT id FROM sprints 
        WHERE project_id = ? 
        AND id != ?
        AND status IN ('planning', 'active') 
        AND (
          (start_date <= ? AND end_date >= ?) OR
          (start_date <= ? AND end_date >= ?) OR
          (start_date >= ? AND end_date <= ?)
        )
      `, [existingSprint.project_id, sprintId, newStartDate, newStartDate, newEndDate, newEndDate, newStartDate, newEndDate]);

      if (overlappingSprints.length > 0) {
        await connection.rollback();
        return res.status(400).json({
          success: false,
          message: 'Sprint dates overlap with existing active or planned sprints'
        });
      }
    }

    // Build update query
    let updateFields = [];
    let updateValues = [];

    if (name !== undefined) {
      updateFields.push('name = ?');
      updateValues.push(name);
    }
    if (description !== undefined) {
      updateFields.push('description = ?');
      updateValues.push(description);
    }
    if (start_date !== undefined) {
      updateFields.push('start_date = ?');
      updateValues.push(start_date);
    }
    if (end_date !== undefined) {
      updateFields.push('end_date = ?');
      updateValues.push(end_date);
    }
    if (status !== undefined) {
      updateFields.push('status = ?');
      updateValues.push(status);
    }

    if (updateFields.length === 0) {
      await connection.rollback();
      return res.status(400).json({
        success: false,
        message: 'No fields to update'
      });
    }

    updateValues.push(sprintId);

    // Update sprint
    await connection.execute(
      `UPDATE sprints SET ${updateFields.join(', ')}, updated_at = CURRENT_TIMESTAMP WHERE id = ?`,
      updateValues
    );

    // Log activity
    await connection.execute(
      'INSERT INTO activity_logs (user_id, action, entity_type, entity_id, details) VALUES (?, ?, ?, ?, ?)',
      [updatedBy, 'update_sprint', 'sprint', sprintId, JSON.stringify(req.body)]
    );

    await connection.commit();

    // Get updated sprint details
    const [updatedSprint] = await connection.execute(`
      SELECT 
        s.id, s.name, s.description, s.status, s.start_date, s.end_date, s.updated_at,
        p.name as project_name
      FROM sprints s
      LEFT JOIN projects p ON s.project_id = p.id
      WHERE s.id = ?
    `, [sprintId]);

    res.status(200).json({
      success: true,
      message: 'Sprint updated successfully',
      data: {
        sprint: updatedSprint[0]
      }
    });
  } catch (error) {
    await connection.rollback();
    console.error('Update sprint error:', error);
    res.status(500).json({
      success: false,
      message: 'Failed to update sprint'
    });
  } finally {
    connection.release();
  }
});

// Delete sprint
router.delete('/:id', authenticateToken, requireManager, validateId(), handleValidationErrors, async (req, res) => {
  const connection = await getConnection();
  
  try {
    await connection.beginTransaction();

    const sprintId = req.params.id;
    const deletedBy = req.user.id;

    // Check if sprint exists
    const [existingSprints] = await connection.execute(
      'SELECT id, project_id, name FROM sprints WHERE id = ?',
      [sprintId]
    );

    if (existingSprints.length === 0) {
      await connection.rollback();
      return res.status(404).json({
        success: false,
        message: 'Sprint not found'
      });
    }

    const sprint = existingSprints[0];

    // Check if sprint has tasks
    const [sprintTasks] = await connection.execute(
      'SELECT COUNT(*) as task_count FROM tasks WHERE sprint_id = ?',
      [sprintId]
    );

    if (sprintTasks[0].task_count > 0) {
      await connection.rollback();
      return res.status(400).json({
        success: false,
        message: 'Cannot delete sprint with existing tasks. Please move or delete tasks first.'
      });
    }

    // Delete sprint
    await connection.execute(
      'DELETE FROM sprints WHERE id = ?',
      [sprintId]
    );

    // Log activity
    await connection.execute(
      'INSERT INTO activity_logs (user_id, action, entity_type, entity_id, details) VALUES (?, ?, ?, ?, ?)',
      [deletedBy, 'delete_sprint', 'sprint', sprintId, JSON.stringify({ sprint_name: sprint.name, project_id: sprint.project_id })]
    );

    await connection.commit();

    res.status(200).json({
      success: true,
      message: 'Sprint deleted successfully'
    });
  } catch (error) {
    await connection.rollback();
    console.error('Delete sprint error:', error);
    res.status(500).json({
      success: false,
      message: 'Failed to delete sprint'
    });
  } finally {
    connection.release();
  }
});

// Start sprint
router.post('/:id/start', authenticateToken, validateId(), handleValidationErrors, async (req, res) => {
  const connection = await getConnection();
  
  try {
    await connection.beginTransaction();

    const sprintId = req.params.id;
    const userId = req.user.id;
    const userRole = req.user.role;

    // Check if sprint exists and user has access
    let sprintQuery = `
      SELECT s.*, p.owner_id as project_owner_id
      FROM sprints s
      JOIN projects p ON s.project_id = p.id
      WHERE s.id = ? AND s.status = 'planning'
    `;
    let sprintParams = [sprintId];

    if (userRole !== 'admin' && userRole !== 'main_admin') {
      sprintQuery += ' AND (p.owner_id = ? OR p.id IN (SELECT project_id FROM project_members WHERE user_id = ?))';
      sprintParams.push(userId, userId);
    }

    const [existingSprints] = await connection.execute(sprintQuery, sprintParams);

    if (existingSprints.length === 0) {
      await connection.rollback();
      return res.status(404).json({
        success: false,
        message: 'Sprint not found, access denied, or sprint is not in planning status'
      });
    }

    const sprint = existingSprints[0];

    // Check if there's already an active sprint in the project
    const [activeSprints] = await connection.execute(
      'SELECT id FROM sprints WHERE project_id = ? AND status = "active"',
      [sprint.project_id]
    );

    if (activeSprints.length > 0) {
      await connection.rollback();
      return res.status(400).json({
        success: false,
        message: 'There is already an active sprint in this project'
      });
    }

    // Start sprint
    await connection.execute(
      'UPDATE sprints SET status = "active", updated_at = CURRENT_TIMESTAMP WHERE id = ?',
      [sprintId]
    );

    // Log activity
    await connection.execute(
      'INSERT INTO activity_logs (user_id, action, entity_type, entity_id, details) VALUES (?, ?, ?, ?, ?)',
      [userId, 'start_sprint', 'sprint', sprintId, JSON.stringify({ sprint_name: sprint.name })]
    );

    await connection.commit();

    res.status(200).json({
      success: true,
      message: 'Sprint started successfully'
    });
  } catch (error) {
    await connection.rollback();
    console.error('Start sprint error:', error);
    res.status(500).json({
      success: false,
      message: 'Failed to start sprint'
    });
  } finally {
    connection.release();
  }
});

// Complete sprint
router.post('/:id/complete', authenticateToken, validateId(), handleValidationErrors, async (req, res) => {
  const connection = await getConnection();
  
  try {
    await connection.beginTransaction();

    const sprintId = req.params.id;
    const userId = req.user.id;
    const userRole = req.user.role;

    // Check if sprint exists and user has access
    let sprintQuery = `
      SELECT s.*, p.owner_id as project_owner_id
      FROM sprints s
      JOIN projects p ON s.project_id = p.id
      WHERE s.id = ? AND s.status = 'active'
    `;
    let sprintParams = [sprintId];

    if (userRole !== 'admin' && userRole !== 'main_admin') {
      sprintQuery += ' AND (p.owner_id = ? OR p.id IN (SELECT project_id FROM project_members WHERE user_id = ?))';
      sprintParams.push(userId, userId);
    }

    const [existingSprints] = await connection.execute(sprintQuery, sprintParams);

    if (existingSprints.length === 0) {
      await connection.rollback();
      return res.status(404).json({
        success: false,
        message: 'Sprint not found, access denied, or sprint is not active'
      });
    }

    const sprint = existingSprints[0];

    // Complete sprint
    await connection.execute(
      'UPDATE sprints SET status = "completed", updated_at = CURRENT_TIMESTAMP WHERE id = ?',
      [sprintId]
    );

    // Move incomplete tasks to backlog (remove sprint assignment)
    await connection.execute(
      'UPDATE tasks SET sprint_id = NULL WHERE sprint_id = ? AND status != "done"',
      [sprintId]
    );

    // Log activity
    await connection.execute(
      'INSERT INTO activity_logs (user_id, action, entity_type, entity_id, details) VALUES (?, ?, ?, ?, ?)',
      [userId, 'complete_sprint', 'sprint', sprintId, JSON.stringify({ sprint_name: sprint.name })]
    );

    await connection.commit();

    res.status(200).json({
      success: true,
      message: 'Sprint completed successfully. Incomplete tasks moved to backlog.'
    });
  } catch (error) {
    await connection.rollback();
    console.error('Complete sprint error:', error);
    res.status(500).json({
      success: false,
      message: 'Failed to complete sprint'
    });
  } finally {
    connection.release();
  }
});

module.exports = router;
