/**
 * User Model
 * Handles user-related database operations
 */

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

class User {
  /**
   * Find user by ID
   */
  static async findById(id) {
    const users = await query(
      `SELECT u.*, e.first_name, e.last_name, e.employee_id as emp_code,
                    e.department_id, d.name as department_name
             FROM users u
             LEFT JOIN employees e ON u.employee_id = e.id
             LEFT JOIN departments d ON e.department_id = d.id
             WHERE u.id = ?`,
      [id],
    );
    return users[0] || null;
  }

  /**
   * Find user by email
   */
  static async findByEmail(email) {
    const users = await query(
      `SELECT u.*, e.first_name, e.last_name, e.employee_id as emp_code,
                    e.department_id, d.name as department_name
             FROM users u
             LEFT JOIN employees e ON u.employee_id = e.id
             LEFT JOIN departments d ON e.department_id = d.id
             WHERE u.email = ?`,
      [email],
    );
    return users[0] || null;
  }

  /**
   * Find user by username
   */
  static async findByUsername(username) {
    const users = await query(
      `SELECT u.*, e.first_name, e.last_name, e.employee_id as emp_code,
                    e.department_id, d.name as department_name
             FROM users u
             LEFT JOIN employees e ON u.employee_id = e.id
             LEFT JOIN departments d ON e.department_id = d.id
             WHERE u.username = ?`,
      [username],
    );
    return users[0] || null;
  }

  /**
   * Create new user
   */
  static async create(userData) {
    const hashedPassword = await bcrypt.hash(
      userData.password,
      config.bcrypt.saltRounds,
    );

    const result = await query(
      `INSERT INTO users (employee_id, username, email, password, role)
             VALUES (?, ?, ?, ?, ?)`,
      [
        userData.employeeId,
        userData.username,
        userData.email,
        hashedPassword,
        userData.role || "Employee",
      ],
    );

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

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

    if (userData.username) {
      fields.push("username = ?");
      values.push(userData.username);
    }
    if (userData.email) {
      fields.push("email = ?");
      values.push(userData.email);
    }
    if (userData.role) {
      fields.push("role = ?");
      values.push(userData.role);
    }
    if (userData.isActive !== undefined) {
      fields.push("is_active = ?");
      values.push(userData.isActive);
    }

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

    values.push(id);

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

    return this.findById(id);
  }

  /**
   * Change password
   */
  static async changePassword(id, newPassword) {
    const hashedPassword = await bcrypt.hash(
      newPassword,
      config.bcrypt.saltRounds,
    );

    await query("UPDATE users SET password = ? WHERE id = ?", [
      hashedPassword,
      id,
    ]);

    return true;
  }

  /**
   * Verify password
   */
  static async verifyPassword(plainPassword, hashedPassword) {
    return bcrypt.compare(plainPassword, hashedPassword);
  }

  /**
   * Update last login
   */
  static async updateLastLogin(id) {
    await query(
      "UPDATE users SET last_login = CURRENT_TIMESTAMP WHERE id = ?",
      [id],
    );
  }

  /**
   * Get all users with pagination
   */
  static async findAll(options = {}) {
    const { page = 1, limit = 10, search = "", role = "" } = options;
    const offset = (page - 1) * limit;

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

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

    if (role) {
      whereClause += " AND u.role = ?";
      params.push(role);
    }

    const countResult = await query(
      `SELECT COUNT(*) as total FROM users u 
             LEFT JOIN employees e ON u.employee_id = e.id 
             WHERE ${whereClause}`,
      params,
    );

    const users = await query(
      `SELECT u.id, u.username, u.email, u.role, u.is_active, u.last_login, u.created_at,
                    e.first_name, e.last_name, e.employee_id as emp_code, d.name as department_name
             FROM users u
             LEFT JOIN employees e ON u.employee_id = e.id
             LEFT JOIN departments d ON e.department_id = d.id
             WHERE ${whereClause}
             ORDER BY u.created_at DESC
             LIMIT ? OFFSET ?`,
      [...params, limit, offset],
    );

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

module.exports = User;
