/**
 * Report Controller
 * Handles report generation
 */

const { query } = require("../config/database");

/**
 * Get employee task report
 * GET /api/reports/employee-tasks
 */
const getEmployeeTaskReport = async (req, res, next) => {
  try {
    const { employeeId, startDate, endDate } = req.query;

    let whereClause = "t.is_deleted = FALSE";
    const params = [];

    if (employeeId) {
      whereClause += " AND t.assigned_to = ?";
      params.push(employeeId);
    }

    if (startDate) {
      whereClause += " AND t.created_at >= ?";
      params.push(startDate);
    }

    if (endDate) {
      whereClause += " AND t.created_at <= ?";
      params.push(endDate);
    }

    const report = await query(
      `
            SELECT 
                e.employee_id,
                CONCAT(e.first_name, ' ', e.last_name) as employee_name,
                d.name as department,
                COUNT(t.id) as total_tasks,
                SUM(CASE WHEN t.status = 'Pending' THEN 1 ELSE 0 END) as pending,
                SUM(CASE WHEN t.status = 'In Progress' THEN 1 ELSE 0 END) as in_progress,
                SUM(CASE WHEN t.status = 'Completed' THEN 1 ELSE 0 END) as completed,
                SUM(CASE WHEN t.status = 'On Hold' THEN 1 ELSE 0 END) as on_hold,
                SUM(CASE WHEN t.due_date < CURDATE() AND t.status != 'Completed' THEN 1 ELSE 0 END) as overdue,
                ROUND(AVG(t.actual_hours), 2) as avg_hours_per_task,
                ROUND(
                    SUM(CASE WHEN t.status = 'Completed' THEN 1 ELSE 0 END) * 100.0 / 
                    NULLIF(COUNT(t.id), 0), 2
                ) as completion_rate
            FROM employees e
            LEFT JOIN departments d ON e.department_id = d.id
            LEFT JOIN tasks t ON e.id = t.assigned_to AND ${whereClause}
            WHERE e.is_deleted = FALSE
            GROUP BY e.id, e.employee_id, e.first_name, e.last_name, d.name
            ORDER BY total_tasks DESC
        `,
      params,
    );

    res.json({
      success: true,
      data: report,
    });
  } catch (error) {
    next(error);
  }
};

/**
 * Get completed tasks report
 * GET /api/reports/completed-tasks
 */
const getCompletedTasksReport = async (req, res, next) => {
  try {
    const { startDate, endDate, departmentId } = req.query;

    let whereClause = "t.status = 'Completed' AND t.is_deleted = FALSE";
    const params = [];

    if (startDate) {
      whereClause += " AND t.completed_date >= ?";
      params.push(startDate);
    }

    if (endDate) {
      whereClause += " AND t.completed_date <= ?";
      params.push(endDate);
    }

    if (departmentId) {
      whereClause += " AND t.department_id = ?";
      params.push(departmentId);
    }

    const report = await query(
      `
            SELECT 
                t.task_id,
                t.title,
                CONCAT(e.first_name, ' ', e.last_name) as assigned_to,
                d.name as department,
                t.priority,
                t.start_date,
                t.due_date,
                t.completed_date,
                DATEDIFF(t.completed_date, t.start_date) as days_to_complete,
                CASE 
                    WHEN t.completed_date <= t.due_date THEN 'On Time'
                    ELSE 'Late'
                END as completion_status,
                t.estimated_hours,
                t.actual_hours
            FROM tasks t
            LEFT JOIN employees e ON t.assigned_to = e.id
            LEFT JOIN departments d ON t.department_id = d.id
            WHERE ${whereClause}
            ORDER BY t.completed_date DESC
        `,
      params,
    );

    res.json({
      success: true,
      data: report,
    });
  } catch (error) {
    next(error);
  }
};

/**
 * Get pending tasks report
 * GET /api/reports/pending-tasks
 */
const getPendingTasksReport = async (req, res, next) => {
  try {
    const { departmentId, priority } = req.query;

    let whereClause =
      "t.status IN ('Pending', 'In Progress') AND t.is_deleted = FALSE";
    const params = [];

    if (departmentId) {
      whereClause += " AND t.department_id = ?";
      params.push(departmentId);
    }

    if (priority) {
      whereClause += " AND t.priority = ?";
      params.push(priority);
    }

    const report = await query(
      `
            SELECT 
                t.task_id,
                t.title,
                t.description,
                CONCAT(e.first_name, ' ', e.last_name) as assigned_to,
                e.email as assigned_to_email,
                d.name as department,
                t.priority,
                t.status,
                t.start_date,
                t.due_date,
                DATEDIFF(t.due_date, CURDATE()) as days_until_due,
                CASE 
                    WHEN t.due_date < CURDATE() THEN 'Overdue'
                    WHEN t.due_date = CURDATE() THEN 'Due Today'
                    WHEN t.due_date <= DATE_ADD(CURDATE(), INTERVAL 3 DAY) THEN 'Due Soon'
                    ELSE 'On Track'
                END as urgency
            FROM tasks t
            LEFT JOIN employees e ON t.assigned_to = e.id
            LEFT JOIN departments d ON t.department_id = d.id
            WHERE ${whereClause}
            ORDER BY 
                CASE 
                    WHEN t.due_date < CURDATE() THEN 1
                    WHEN t.due_date = CURDATE() THEN 2
                    ELSE 3
                END,
                t.due_date ASC
        `,
      params,
    );

    res.json({
      success: true,
      data: report,
    });
  } catch (error) {
    next(error);
  }
};

/**
 * Get overdue tasks report
 * GET /api/reports/overdue-tasks
 */
const getOverdueTasksReport = async (req, res, next) => {
  try {
    const { departmentId } = req.query;

    let whereClause =
      "t.due_date < CURDATE() AND t.status != 'Completed' AND t.is_deleted = FALSE";
    const params = [];

    if (departmentId) {
      whereClause += " AND t.department_id = ?";
      params.push(departmentId);
    }

    const report = await query(
      `
            SELECT 
                t.task_id,
                t.title,
                CONCAT(e.first_name, ' ', e.last_name) as assigned_to,
                e.email as assigned_to_email,
                d.name as department,
                t.priority,
                t.status,
                t.due_date,
                DATEDIFF(CURDATE(), t.due_date) as days_overdue
            FROM tasks t
            LEFT JOIN employees e ON t.assigned_to = e.id
            LEFT JOIN departments d ON t.department_id = d.id
            WHERE ${whereClause}
            ORDER BY days_overdue DESC, t.priority DESC
        `,
      params,
    );

    // Summary statistics
    const summary = await query(
      `
            SELECT 
                COUNT(*) as total_overdue,
                SUM(CASE WHEN t.priority = 'High' THEN 1 ELSE 0 END) as high_priority,
                SUM(CASE WHEN t.priority = 'Medium' THEN 1 ELSE 0 END) as medium_priority,
                SUM(CASE WHEN t.priority = 'Low' THEN 1 ELSE 0 END) as low_priority,
                AVG(DATEDIFF(CURDATE(), t.due_date)) as avg_days_overdue
            FROM tasks t
            WHERE ${whereClause}
        `,
      params,
    );

    res.json({
      success: true,
      data: {
        tasks: report,
        summary: summary[0],
      },
    });
  } catch (error) {
    next(error);
  }
};

/**
 * Get department performance report
 * GET /api/reports/department-performance
 */
const getDepartmentPerformanceReport = async (req, res, next) => {
  try {
    const report = await query(`
            SELECT 
                d.id,
                d.name as department,
                COUNT(DISTINCT e.id) as employee_count,
                COUNT(t.id) as total_tasks,
                SUM(CASE WHEN t.status = 'Completed' THEN 1 ELSE 0 END) as completed_tasks,
                SUM(CASE WHEN t.status = 'Pending' THEN 1 ELSE 0 END) as pending_tasks,
                SUM(CASE WHEN t.status = 'In Progress' THEN 1 ELSE 0 END) as in_progress_tasks,
                SUM(CASE WHEN t.due_date < CURDATE() AND t.status != 'Completed' THEN 1 ELSE 0 END) as overdue_tasks,
                ROUND(
                    SUM(CASE WHEN t.status = 'Completed' THEN 1 ELSE 0 END) * 100.0 / 
                    NULLIF(COUNT(t.id), 0), 2
                ) as completion_rate,
                ROUND(
                    SUM(CASE WHEN t.status = 'Completed' AND t.completed_date <= t.due_date THEN 1 ELSE 0 END) * 100.0 / 
                    NULLIF(SUM(CASE WHEN t.status = 'Completed' THEN 1 ELSE 0 END), 0), 2
                ) as on_time_completion_rate
            FROM departments d
            LEFT JOIN employees e ON d.id = e.department_id AND e.is_deleted = FALSE
            LEFT JOIN tasks t ON d.id = t.department_id AND t.is_deleted = FALSE
            WHERE d.is_active = TRUE
            GROUP BY d.id, d.name
            ORDER BY completion_rate DESC
        `);

    res.json({
      success: true,
      data: report,
    });
  } catch (error) {
    next(error);
  }
};

module.exports = {
  getEmployeeTaskReport,
  getCompletedTasksReport,
  getPendingTasksReport,
  getOverdueTasksReport,
  getDepartmentPerformanceReport,
};
