5why-analyzer/models/User.js

import { pool } from '../config.js';
import bcrypt from 'bcryptjs';

/**
 * User Model
 * 處理使用者相關的資料庫操作
 */
class User {
  /**
   * 根據 ID 取得使用者
   */
  static async findById(id) {
    try {
      const [rows] = await pool.execute(
        'SELECT id, employee_id, username, email, role, department, position, is_active, created_at, last_login_at FROM 5Why_users WHERE id = ?',
        [id]
      );
      return rows[0] || null;
    } catch (error) {
      throw new Error(`Error finding user by ID: ${error.message}`);
    }
  }

  /**
   * 根據 Email 取得使用者（含密碼，用於登入驗證）
   */
  static async findByEmail(email) {
    try {
      const [rows] = await pool.execute(
        'SELECT * FROM 5Why_users WHERE email = ? AND is_active = 1',
        [email]
      );
      return rows[0] || null;
    } catch (error) {
      throw new Error(`Error finding user by email: ${error.message}`);
    }
  }

  /**
   * 根據工號取得使用者
   */
  static async findByEmployeeId(employeeId) {
    try {
      const [rows] = await pool.execute(
        'SELECT * FROM 5Why_users WHERE employee_id = ? AND is_active = 1',
        [employeeId]
      );
      return rows[0] || null;
    } catch (error) {
      throw new Error(`Error finding user by employee ID: ${error.message}`);
    }
  }

  /**
   * 驗證密碼
   */
  static async verifyPassword(plainPassword, hashedPassword) {
    return await bcrypt.compare(plainPassword, hashedPassword);
  }

  /**
   * 建立新使用者
   */
  static async create(userData) {
    const { employee_id, username, email, password, role = 'user', department, position } = userData;

    try {
      // 加密密碼
      const passwordHash = await bcrypt.hash(password, 10);

      const [result] = await pool.execute(
        `INSERT INTO 5Why_users (employee_id, username, email, password_hash, role, department, position)
         VALUES (?, ?, ?, ?, ?, ?, ?)`,
        [employee_id, username, email, passwordHash, role, department, position]
      );

      return await this.findById(result.insertId);
    } catch (error) {
      if (error.code === 'ER_DUP_ENTRY') {
        throw new Error('工號或 Email 已存在');
      }
      throw new Error(`Error creating user: ${error.message}`);
    }
  }

  /**
   * 更新使用者資料
   */
  static async update(id, userData) {
    const { username, email, role, department, position, is_active } = userData;

    try {
      await pool.execute(
        `UPDATE 5Why_users
         SET username = ?, email = ?, role = ?, department = ?, position = ?, is_active = ?
         WHERE id = ?`,
        [username, email, role, department, position, is_active, id]
      );

      return await this.findById(id);
    } catch (error) {
      throw new Error(`Error updating user: ${error.message}`);
    }
  }

  /**
   * 更新密碼
   */
  static async updatePassword(id, newPassword) {
    try {
      const passwordHash = await bcrypt.hash(newPassword, 10);
      await pool.execute(
        'UPDATE 5Why_users SET password_hash = ? WHERE id = ?',
        [passwordHash, id]
      );
      return true;
    } catch (error) {
      throw new Error(`Error updating password: ${error.message}`);
    }
  }

  /**
   * 更新最後登入時間
   */
  static async updateLastLogin(id) {
    try {
      await pool.execute(
        'UPDATE 5Why_users SET last_login_at = CURRENT_TIMESTAMP WHERE id = ?',
        [id]
      );
    } catch (error) {
      console.error('Error updating last login:', error);
    }
  }

  /**
   * 取得所有使用者（分頁）
   */
  static async getAll(page = 1, limit = 10, filters = {}) {
    const offset = (page - 1) * limit;
    let query = 'SELECT id, employee_id, username, email, role, department, position, is_active, created_at FROM 5Why_users';
    let countQuery = 'SELECT COUNT(*) as total FROM 5Why_users';
    const whereParams = [];
    const whereClauses = [];

    // 篩選條件
    if (filters.role) {
      whereClauses.push('role = ?');
      whereParams.push(filters.role);
    }
    if (filters.is_active !== undefined) {
      whereClauses.push('is_active = ?');
      whereParams.push(filters.is_active);
    }
    if (filters.search) {
      whereClauses.push('(username LIKE ? OR email LIKE ? OR employee_id LIKE ?)');
      const searchTerm = `%${filters.search}%`;
      whereParams.push(searchTerm, searchTerm, searchTerm);
    }

    if (whereClauses.length > 0) {
      const whereClause = ' WHERE ' + whereClauses.join(' AND ');
      query += whereClause;
      countQuery += whereClause;
    }

    query += ' ORDER BY created_at DESC LIMIT ? OFFSET ?';

    try {
      // 使用 query 而非 execute，因為 LIMIT 和 OFFSET 參數需要數字類型
      const [rows] = await pool.query(query, [...whereParams, parseInt(limit), parseInt(offset)]);
      const [countResult] = await pool.query(countQuery, whereParams);

      return {
        data: rows,
        pagination: {
          page,
          limit,
          total: countResult[0].total,
          totalPages: Math.ceil(countResult[0].total / limit)
        }
      };
    } catch (error) {
      throw new Error(`Error getting users: ${error.message}`);
    }
  }

  /**
   * 刪除使用者（軟刪除）
   */
  static async delete(id) {
    try {
      await pool.execute(
        'UPDATE 5Why_users SET is_active = 0 WHERE id = ?',
        [id]
      );
      return true;
    } catch (error) {
      throw new Error(`Error deleting user: ${error.message}`);
    }
  }

  /**
   * 硬刪除使用者（謹慎使用）
   */
  static async hardDelete(id) {
    try {
      await pool.execute('DELETE FROM 5Why_users WHERE id = ?', [id]);
      return true;
    } catch (error) {
      throw new Error(`Error hard deleting user: ${error.message}`);
    }
  }

  /**
   * 取得使用者統計
   */
  static async getStats(userId) {
    try {
      const [rows] = await pool.execute(
        'SELECT * FROM 5Why_user_analysis_stats WHERE user_id = ?',
        [userId]
      );
      return rows[0] || null;
    } catch (error) {
      throw new Error(`Error getting user stats: ${error.message}`);
    }
  }
}

export default User;