/**
 * Employee Model
 * Handles employee-related database operations
 */

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

class Employee {
  /**
   * Generate unique employee ID
   */
  static async generateEmployeeId() {
    const result = await query(
      `SELECT employee_id FROM employees 
             ORDER BY id DESC LIMIT 1`,
    );

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

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

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

  /**
   * Find employee by ID
   */
  static async findById(id) {
    const employees = await query(
      `SELECT e.*, d.name as department_name,
                    u.id as user_id, u.username, u.role, u.is_active as user_active
             FROM employees e
             LEFT JOIN departments d ON e.department_id = d.id
             LEFT JOIN users u ON u.employee_id = e.id
             WHERE e.id = ? AND e.is_deleted = FALSE`,
      [id],
    );
    return employees[0] || null;
  }

  /**
   * Find employee by employee ID
   */
  static async findByEmployeeId(employeeId) {
    const employees = await query(
      `SELECT e.*, d.name as department_name
             FROM employees e
             LEFT JOIN departments d ON e.department_id = d.id
             WHERE e.employee_id = ? AND e.is_deleted = FALSE`,
      [employeeId],
    );
    return employees[0] || null;
  }

  /**
   * Create new employee
   */
  static async create(employeeData) {
    const employeeId = await this.generateEmployeeId();

    const result = await query(
      `INSERT INTO employees (
                employee_id, first_name, last_name, nic, email, mobile,
                address, job_title, department_id, join_date, status
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        employeeId,
        employeeData.firstName,
        employeeData.lastName,
        employeeData.nic,
        employeeData.email,
        employeeData.mobile,
        employeeData.address || null,
        employeeData.jobTitle || null,
        employeeData.departmentId || null,
        employeeData.joinDate,
        employeeData.status || "Active",
      ],
    );

    return { id: result.insertId, employeeId, ...employeeData };
  }

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

    const fieldMap = {
      firstName: "first_name",
      lastName: "last_name",
      nic: "nic",
      email: "email",
      mobile: "mobile",
      address: "address",
      jobTitle: "job_title",
      departmentId: "department_id",
      joinDate: "join_date",
      status: "status",
      profileImage: "profile_image",
    };

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

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

    values.push(id);

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

    return this.findById(id);
  }

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

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

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

    if (search) {
      whereClause += ` AND (
                e.employee_id LIKE ? OR 
                e.first_name LIKE ? OR 
                e.last_name LIKE ? OR 
                e.email LIKE ? OR
                e.mobile LIKE ?
            )`;
      const searchTerm = `%${search}%`;
      params.push(searchTerm, searchTerm, searchTerm, searchTerm, searchTerm);
    }

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

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

    // Validate sort column
    const allowedSortColumns = [
      "employee_id",
      "first_name",
      "last_name",
      "email",
      "join_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 employees e WHERE ${whereClause}`,
      params,
    );

    // Get employees
    const employees = await query(
      `SELECT e.*, d.name as department_name
             FROM employees e
             LEFT JOIN departments d ON e.department_id = d.id
             WHERE ${whereClause}
             ORDER BY e.${sortColumn} ${order}
             LIMIT ? OFFSET ?`,
      [...params, limit, offset],
    );

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

  /**
   * Get employees by department
   */
  static async findByDepartment(departmentId) {
    return query(
      `SELECT e.*, d.name as department_name
             FROM employees e
             LEFT JOIN departments d ON e.department_id = d.id
             WHERE e.department_id = ? AND e.is_deleted = FALSE AND e.status = 'Active'
             ORDER BY e.first_name ASC`,
      [departmentId],
    );
  }

  /**
   * Get employee statistics
   */
  static async getStatistics() {
    const result = await query(`
            SELECT 
                COUNT(*) as total,
                SUM(CASE WHEN status = 'Active' THEN 1 ELSE 0 END) as active,
                SUM(CASE WHEN status = 'Inactive' THEN 1 ELSE 0 END) as inactive,
                COUNT(DISTINCT department_id) as departments_with_employees
            FROM employees 
            WHERE is_deleted = FALSE
        `);

    return result[0];
  }
}

module.exports = Employee;
