/**
 * Task Model
 * Handles task-related database operations
 */

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

class Task {
  /**
   * Generate unique task ID
   */
  static async generateTaskId() {
    const result = await query(
      `SELECT task_id FROM tasks 
             ORDER BY id DESC LIMIT 1`,
    );

    if (result.length === 0) {
      return "TSK001";
    }

    const lastId = result[0].task_id;
    const numPart = parseInt(lastId.replace("TSK", ""), 10);
    const newNum = numPart + 1;

    return `TSK${String(newNum).padStart(3, "0")}`;
  }

  /**
   * Find task by ID
   */
  static async findById(id) {
    const tasks = await query(
      `SELECT t.*,
                    CONCAT(ae.first_name, ' ', ae.last_name) as assigned_to_name,
                    ae.email as assigned_to_email,
                    CONCAT(ab.first_name, ' ', ab.last_name) as assigned_by_name,
                    d.name as department_name
             FROM tasks t
             LEFT JOIN employees ae ON t.assigned_to = ae.id
             LEFT JOIN employees ab ON t.assigned_by = ab.id
             LEFT JOIN departments d ON t.department_id = d.id
             WHERE t.id = ? AND t.is_deleted = FALSE`,
      [id],
    );
    return tasks[0] || null;
  }

  /**
   * Create new task
   */
  static async create(taskData) {
    const taskId = await this.generateTaskId();

    const result = await query(
      `INSERT INTO tasks (
                task_id, title, description, assigned_to, assigned_by,
                department_id, priority, status, start_date, due_date, estimated_hours
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        taskId,
        taskData.title,
        taskData.description || null,
        taskData.assignedTo || null,
        taskData.assignedBy || null,
        taskData.departmentId || null,
        taskData.priority || "Medium",
        taskData.status || "Pending",
        taskData.startDate || null,
        taskData.dueDate || null,
        taskData.estimatedHours || null,
      ],
    );

    return { id: result.insertId, taskId, ...taskData };
  }

  /**
   * Update task
   */
  static async update(id, taskData) {
    const fields = [];
    const values = [];

    const fieldMap = {
      title: "title",
      description: "description",
      assignedTo: "assigned_to",
      assignedBy: "assigned_by",
      departmentId: "department_id",
      priority: "priority",
      status: "status",
      startDate: "start_date",
      dueDate: "due_date",
      estimatedHours: "estimated_hours",
      actualHours: "actual_hours",
    };

    for (const [key, column] of Object.entries(fieldMap)) {
      if (taskData[key] !== undefined) {
        fields.push(`${column} = ?`);
        values.push(taskData[key]);
      }
    }

    // Handle completion date
    if (taskData.status === "Completed") {
      fields.push("completed_date = CURRENT_TIMESTAMP");
    }

    if (fields.length === 0) return null;

    values.push(id);

    await query(`UPDATE tasks SET ${fields.join(", ")} WHERE id = ?`, values);

    return this.findById(id);
  }

  /**
   * Update task status
   */
  static async updateStatus(id, status) {
    const updateFields = ["status = ?"];
    const values = [status];

    if (status === "Completed") {
      updateFields.push("completed_date = CURRENT_TIMESTAMP");
    }

    values.push(id);

    await query(
      `UPDATE tasks SET ${updateFields.join(", ")} WHERE id = ?`,
      values,
    );

    return this.findById(id);
  }

  /**
   * Soft delete task
   */
  static async softDelete(id) {
    await query("UPDATE tasks SET is_deleted = TRUE WHERE id = ?", [id]);
    return true;
  }

  /**
   * Get all tasks with pagination and filters
   */
  static async findAll(options = {}) {
    const {
      page = 1,
      limit = 10,
      search = "",
      status = "",
      priority = "",
      assignedTo = "",
      departmentId = "",
      startDate = "",
      endDate = "",
      sortBy = "created_at",
      sortOrder = "DESC",
    } = options;

    const offset = (page - 1) * limit;
    let whereClause = "t.is_deleted = FALSE";
    const params = [];

    if (search) {
      whereClause += ` AND (
                t.task_id LIKE ? OR 
                t.title LIKE ? OR 
                t.description LIKE ?
            )`;
      const searchTerm = `%${search}%`;
      params.push(searchTerm, searchTerm, searchTerm);
    }

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

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

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

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

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

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

    // Validate sort column
    const allowedSortColumns = [
      "task_id",
      "title",
      "priority",
      "status",
      "due_date",
      "created_at",
    ];
    const sortColumn = allowedSortColumns.includes(sortBy)
      ? sortBy
      : "created_at";
    const order = sortOrder.toUpperCase() === "ASC" ? "ASC" : "DESC";

    // Get total count
    const countResult = await query(
      `SELECT COUNT(*) as total FROM tasks t WHERE ${whereClause}`,
      params,
    );

    // Get tasks
    const tasks = await query(
      `SELECT t.*,
                    CONCAT(ae.first_name, ' ', ae.last_name) as assigned_to_name,
                    CONCAT(ab.first_name, ' ', ab.last_name) as assigned_by_name,
                    d.name as department_name,
                    DATEDIFF(t.due_date, CURDATE()) as days_until_due
             FROM tasks t
             LEFT JOIN employees ae ON t.assigned_to = ae.id
             LEFT JOIN employees ab ON t.assigned_by = ab.id
             LEFT JOIN departments d ON t.department_id = d.id
             WHERE ${whereClause}
             ORDER BY t.${sortColumn} ${order}
             LIMIT ? OFFSET ?`,
      [...params, limit, offset],
    );

    return {
      tasks,
      pagination: {
        total: countResult[0].total,
        page,
        limit,
        totalPages: Math.ceil(countResult[0].total / limit),
      },
    };
  }

  /**
   * Get tasks by employee
   */
  static async findByEmployee(employeeId, options = {}) {
    const { status = "", limit = 50 } = options;

    let whereClause = "t.assigned_to = ? AND t.is_deleted = FALSE";
    const params = [employeeId];

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

    return query(
      `SELECT t.*,
                    CONCAT(ab.first_name, ' ', ab.last_name) as assigned_by_name,
                    d.name as department_name,
                    DATEDIFF(t.due_date, CURDATE()) as days_until_due
             FROM tasks t
             LEFT JOIN employees ab ON t.assigned_by = ab.id
             LEFT JOIN departments d ON t.department_id = d.id
             WHERE ${whereClause}
             ORDER BY 
                CASE t.priority WHEN 'High' THEN 1 WHEN 'Medium' THEN 2 WHEN 'Low' THEN 3 END,
                t.due_date ASC
             LIMIT ?`,
      [...params, limit],
    );
  }

  /**
   * Get task statistics
   */
  static async getStatistics(employeeId = null) {
    let whereClause = "is_deleted = FALSE";
    const params = [];

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

    const result = await query(
      `
            SELECT 
                COUNT(*) as total,
                SUM(CASE WHEN status = 'Pending' THEN 1 ELSE 0 END) as pending,
                SUM(CASE WHEN status = 'In Progress' THEN 1 ELSE 0 END) as in_progress,
                SUM(CASE WHEN status = 'Completed' THEN 1 ELSE 0 END) as completed,
                SUM(CASE WHEN status = 'On Hold' THEN 1 ELSE 0 END) as on_hold,
                SUM(CASE WHEN due_date < CURDATE() AND status != 'Completed' THEN 1 ELSE 0 END) as overdue,
                SUM(CASE WHEN due_date = CURDATE() AND status != 'Completed' THEN 1 ELSE 0 END) as due_today,
                SUM(CASE WHEN due_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY) AND status != 'Completed' THEN 1 ELSE 0 END) as due_this_week
            FROM tasks 
            WHERE ${whereClause}
        `,
      params,
    );

    return result[0];
  }

  /**
   * Get overdue tasks
   */
  static async getOverdueTasks(limit = 10) {
    return query(
      `SELECT t.*,
                    CONCAT(ae.first_name, ' ', ae.last_name) as assigned_to_name,
                    ae.email as assigned_to_email,
                    d.name as department_name,
                    DATEDIFF(CURDATE(), t.due_date) as days_overdue
             FROM tasks t
             LEFT JOIN employees ae ON t.assigned_to = ae.id
             LEFT JOIN departments d ON t.department_id = d.id
             WHERE t.due_date < CURDATE() 
                   AND t.status != 'Completed' 
                   AND t.is_deleted = FALSE
             ORDER BY t.due_date ASC
             LIMIT ?`,
      [limit],
    );
  }

  /**
   * Get upcoming deadlines
   */
  static async getUpcomingDeadlines(days = 7, employeeId = null) {
    let whereClause = `
            t.due_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL ? DAY)
            AND t.status != 'Completed'
            AND t.is_deleted = FALSE
        `;
    const params = [days];

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

    return query(
      `SELECT t.*,
                    CONCAT(ae.first_name, ' ', ae.last_name) as assigned_to_name,
                    d.name as department_name,
                    DATEDIFF(t.due_date, CURDATE()) as days_until_due
             FROM tasks t
             LEFT JOIN employees ae ON t.assigned_to = ae.id
             LEFT JOIN departments d ON t.department_id = d.id
             WHERE ${whereClause}
             ORDER BY t.due_date ASC`,
      params,
    );
  }
}

module.exports = Task;
