/**
 * Department Model
 * Handles department-related database operations
 */

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

class Department {
  /**
   * Find department by ID
   */
  static async findById(id) {
    const departments = await query(
      `SELECT d.*, 
                    CONCAT(e.first_name, ' ', e.last_name) as manager_name,
                    (SELECT COUNT(*) FROM employees WHERE department_id = d.id AND is_deleted = FALSE) as employee_count
             FROM departments d
             LEFT JOIN employees e ON d.manager_id = e.id
             WHERE d.id = ?`,
      [id],
    );
    return departments[0] || null;
  }

  /**
   * Find department by name
   */
  static async findByName(name) {
    const departments = await query(
      "SELECT * FROM departments WHERE name = ?",
      [name],
    );
    return departments[0] || null;
  }

  /**
   * Create new department
   */
  static async create(departmentData) {
    const result = await query(
      `INSERT INTO departments (name, description, manager_id, is_active)
             VALUES (?, ?, ?, ?)`,
      [
        departmentData.name,
        departmentData.description || null,
        departmentData.managerId || null,
        departmentData.isActive !== undefined ? departmentData.isActive : true,
      ],
    );

    return { id: result.insertId, ...departmentData };
  }

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

    if (departmentData.name !== undefined) {
      fields.push("name = ?");
      values.push(departmentData.name);
    }
    if (departmentData.description !== undefined) {
      fields.push("description = ?");
      values.push(departmentData.description);
    }
    if (departmentData.managerId !== undefined) {
      fields.push("manager_id = ?");
      values.push(departmentData.managerId);
    }
    if (departmentData.isActive !== undefined) {
      fields.push("is_active = ?");
      values.push(departmentData.isActive);
    }

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

    values.push(id);

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

    return this.findById(id);
  }

  /**
   * Delete department
   */
  static async delete(id) {
    // Check if department has employees
    const employees = await query(
      "SELECT COUNT(*) as count FROM employees WHERE department_id = ? AND is_deleted = FALSE",
      [id],
    );

    if (employees[0].count > 0) {
      throw new Error("Cannot delete department with active employees");
    }

    await query("DELETE FROM departments WHERE id = ?", [id]);
    return true;
  }

  /**
   * Get all departments
   */
  static async findAll(options = {}) {
    const { includeInactive = false, search = "" } = options;

    let whereClause = "1=1";
    const params = [];

    if (!includeInactive) {
      whereClause += " AND d.is_active = TRUE";
    }

    if (search) {
      whereClause += " AND (d.name LIKE ? OR d.description LIKE ?)";
      params.push(`%${search}%`, `%${search}%`);
    }

    return query(
      `SELECT d.*, 
                    CONCAT(e.first_name, ' ', e.last_name) as manager_name,
                    (SELECT COUNT(*) FROM employees WHERE department_id = d.id AND is_deleted = FALSE) as employee_count
             FROM departments d
             LEFT JOIN employees e ON d.manager_id = e.id
             WHERE ${whereClause}
             ORDER BY d.name ASC`,
      params,
    );
  }

  /**
   * Get department with employees
   */
  static async findWithEmployees(id) {
    const department = await this.findById(id);

    if (!department) return null;

    const employees = await query(
      `SELECT id, employee_id, first_name, last_name, email, job_title, status
             FROM employees
             WHERE department_id = ? AND is_deleted = FALSE
             ORDER BY first_name ASC`,
      [id],
    );

    return { ...department, employees };
  }
}

module.exports = Department;
