5why-analyzer/models/AuditLog.js

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

/**
 * AuditLog Model
 * 處理稽核日誌相關的資料庫操作
 */
class AuditLog {
  /**
   * 建立稽核日誌
   */
  static async create(logData) {
    const {
      user_id = null,
      action,
      entity_type = null,
      entity_id = null,
      old_value = null,
      new_value = null,
      ip_address = null,
      user_agent = null,
      status = 'success',
      error_message = null
    } = logData;

    try {
      await pool.execute(
        `INSERT INTO 5Why_audit_logs
         (user_id, action, entity_type, entity_id, old_value, new_value,
          ip_address, user_agent, status, error_message)
         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
        [
          user_id,
          action,
          entity_type,
          entity_id,
          old_value ? JSON.stringify(old_value) : null,
          new_value ? JSON.stringify(new_value) : null,
          ip_address,
          user_agent,
          status,
          error_message
        ]
      );
    } catch (error) {
      console.error('Error creating audit log:', error);
      // 不拋出錯誤，以免影響主要業務邏輯
    }
  }

  /**
   * 記錄登入
   */
  static async logLogin(userId, ipAddress, userAgent, success = true) {
    await this.create({
      user_id: userId,
      action: 'login',
      ip_address: ipAddress,
      user_agent: userAgent,
      status: success ? 'success' : 'failed'
    });
  }

  /**
   * 記錄登出
   */
  static async logLogout(userId, ipAddress, userAgent) {
    await this.create({
      user_id: userId,
      action: 'logout',
      ip_address: ipAddress,
      user_agent: userAgent,
      status: 'success'
    });
  }

  /**
   * 記錄建立操作
   */
  static async logCreate(userId, entityType, entityId, newValue, ipAddress, userAgent) {
    await this.create({
      user_id: userId,
      action: `create_${entityType}`,
      entity_type: entityType,
      entity_id: entityId,
      new_value: newValue,
      ip_address: ipAddress,
      user_agent: userAgent
    });
  }

  /**
   * 記錄更新操作
   */
  static async logUpdate(userId, entityType, entityId, oldValue, newValue, ipAddress, userAgent) {
    await this.create({
      user_id: userId,
      action: `update_${entityType}`,
      entity_type: entityType,
      entity_id: entityId,
      old_value: oldValue,
      new_value: newValue,
      ip_address: ipAddress,
      user_agent: userAgent
    });
  }

  /**
   * 記錄刪除操作
   */
  static async logDelete(userId, entityType, entityId, oldValue, ipAddress, userAgent) {
    await this.create({
      user_id: userId,
      action: `delete_${entityType}`,
      entity_type: entityType,
      entity_id: entityId,
      old_value: oldValue,
      ip_address: ipAddress,
      user_agent: userAgent
    });
  }

  /**
   * 取得稽核日誌（分頁）
   */
  static async getAll(page = 1, limit = 50, filters = {}) {
    const offset = (page - 1) * limit;
    let query = `
      SELECT al.*, u.username, u.employee_id
      FROM 5Why_audit_logs al
      LEFT JOIN 5Why_users u ON al.user_id = u.id
    `;
    let countQuery = 'SELECT COUNT(*) as total FROM 5Why_audit_logs al';
    const params = [];
    const whereClauses = [];

    // 篩選條件
    if (filters.user_id) {
      whereClauses.push('al.user_id = ?');
      params.push(filters.user_id);
    }
    if (filters.action) {
      whereClauses.push('al.action = ?');
      params.push(filters.action);
    }
    if (filters.entity_type) {
      whereClauses.push('al.entity_type = ?');
      params.push(filters.entity_type);
    }
    if (filters.status) {
      whereClauses.push('al.status = ?');
      params.push(filters.status);
    }
    if (filters.date_from) {
      whereClauses.push('al.created_at >= ?');
      params.push(filters.date_from);
    }
    if (filters.date_to) {
      whereClauses.push('al.created_at <= ?');
      params.push(filters.date_to);
    }

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

    query += ' ORDER BY al.created_at DESC LIMIT ? OFFSET ?';
    params.push(parseInt(limit), parseInt(offset));

    try {
      // 使用 pool.query 而非 pool.execute，因為 LIMIT/OFFSET 需要數字類型
      const [rows] = await pool.query(query, params);
      const [countResult] = await pool.query(countQuery, params.slice(0, -2));

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

  /**
   * 取得使用者的操作日誌
   */
  static async getByUserId(userId, page = 1, limit = 50) {
    return await this.getAll(page, limit, { user_id: userId });
  }

  /**
   * 清理舊日誌（保留 N 天）
   */
  static async cleanup(daysToKeep = 90) {
    try {
      const [result] = await pool.execute(
        'DELETE FROM 5Why_audit_logs WHERE created_at < DATE_SUB(NOW(), INTERVAL ? DAY)',
        [daysToKeep]
      );
      return result.affectedRows;
    } catch (error) {
      throw new Error(`Error cleaning up audit logs: ${error.message}`);
    }
  }
}

export default AuditLog;