MQTTvue/mqtt-vue-dashboard/server/src/models/systemLog.ts

import { executeQuery } from '../config/database';

export interface SystemLog {
  id?: number;
  level: 'info' | 'warn' | 'error' | 'debug';
  message: string;
  source: string;
  module?: string;
  user_id?: number;
  username?: string;
  ip_address?: string;
  details?: string;
  created_at?: Date;
}

export class SystemLogModel {
  // 获取所有系统日志
  static async getAll(limit?: number, offset?: number): Promise<SystemLog[]> {
    let query = 'SELECT * FROM system_logs ORDER BY created_at DESC';
    const params: (string | number)[] = [];
    
    if (limit !== undefined) {
      query += ' LIMIT ?';
      params.push(limit);
      
      if (offset !== undefined) {
        query += ' OFFSET ?';
        params.push(offset);
      }
    }
    
    return await executeQuery(query, params);
  }

  // 根据ID获取系统日志
  static async getById(id: number): Promise<SystemLog | null> {
    const query = 'SELECT * FROM system_logs WHERE id = ?';
    const logs = await executeQuery(query, [id]);
    return logs.length > 0 ? logs[0] : null;
  }

  // 根据日志级别获取系统日志
  static async getByLevel(level: string, limit?: number, offset?: number): Promise<SystemLog[]> {
    let query = 'SELECT * FROM system_logs WHERE level = ? ORDER BY created_at DESC';
    const params: (string | number)[] = [level];
    
    if (limit !== undefined) {
      query += ' LIMIT ?';
      params.push(limit);
      
      if (offset !== undefined) {
        query += ' OFFSET ?';
        params.push(offset);
      }
    }
    
    return await executeQuery(query, params);
  }

  // 根据来源获取系统日志
  static async getBySource(source: string, limit?: number, offset?: number): Promise<SystemLog[]> {
    let query = 'SELECT * FROM system_logs WHERE source = ? ORDER BY created_at DESC';
    const params: (string | number)[] = [source];
    
    if (limit !== undefined) {
      query += ' LIMIT ?';
      params.push(limit);
      
      if (offset !== undefined) {
        query += ' OFFSET ?';
        params.push(offset);
      }
    }
    
    return await executeQuery(query, params);
  }

  // 根据模块获取系统日志
  static async getByModule(module: string, limit?: number, offset?: number): Promise<SystemLog[]> {
    let query = 'SELECT * FROM system_logs WHERE module = ? ORDER BY created_at DESC';
    const params: (string | number)[] = [module];
    
    if (limit !== undefined) {
      query += ' LIMIT ?';
      params.push(limit);
      
      if (offset !== undefined) {
        query += ' OFFSET ?';
        params.push(offset);
      }
    }
    
    return await executeQuery(query, params);
  }

  // 根据时间范围获取系统日志
  static async getByTimeRange(startTime: Date, endTime: Date, limit?: number, offset?: number): Promise<SystemLog[]> {
    let query = 'SELECT * FROM system_logs WHERE created_at BETWEEN ? AND ? ORDER BY created_at DESC';
    const params: any[] = [startTime, endTime];
    
    if (limit !== undefined) {
      query += ' LIMIT ?';
      params.push(limit);
      
      if (offset !== undefined) {
        query += ' OFFSET ?';
        params.push(offset);
      }
    }
    
    return await executeQuery(query, params);
  }

  // 获取系统日志总数
  static async getCount(): Promise<number> {
    const query = 'SELECT COUNT(*) as count FROM system_logs';
    const result = await executeQuery(query);
    return result[0].count;
  }

  // 获取日志级别统计
  static async getLevelStats(startTime?: Date, endTime?: Date): Promise<any[]> {
    let query = `
      SELECT 
        level,
        COUNT(*) as count
      FROM system_logs
    `;
    
    const params: any[] = [];
    
    if (startTime && endTime) {
      query += ' WHERE created_at BETWEEN ? AND ?';
      params.push(startTime, endTime);
    }
    
    query += ' GROUP BY level';
    
    return await executeQuery(query, params);
  }

  // 获取来源统计
  static async getSourceStats(startTime?: Date, endTime?: Date): Promise<any[]> {
    let query = `
      SELECT 
        source,
        COUNT(*) as count
      FROM system_logs
    `;
    
    const params: any[] = [];
    
    if (startTime && endTime) {
      query += ' WHERE created_at BETWEEN ? AND ?';
      params.push(startTime, endTime);
    }
    
    query += ' GROUP BY source';
    
    return await executeQuery(query, params);
  }

  // 获取每日系统日志统计
  static async getDailyStats(days: number = 7): Promise<any[]> {
    const query = `
      SELECT 
        DATE(created_at) as date,
        COUNT(*) as total,
        SUM(CASE WHEN level = 'info' THEN 1 ELSE 0 END) as info,
        SUM(CASE WHEN level = 'warn' THEN 1 ELSE 0 END) as warn,
        SUM(CASE WHEN level = 'error' THEN 1 ELSE 0 END) as error,
        SUM(CASE WHEN level = 'debug' THEN 1 ELSE 0 END) as debug
      FROM system_logs
      WHERE created_at >= DATE_SUB(CURRENT_DATE(), INTERVAL ? DAY)
      GROUP BY DATE(created_at)
      ORDER BY date DESC
    `;
    
    return await executeQuery(query, [days]);
  }

  // 创建系统日志
  static async create(systemLog: Omit<SystemLog, 'id' | 'created_at'>): Promise<SystemLog> {
    const query = `
      INSERT INTO system_logs (level, message, source, module, user_id, username, ip_address, details)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?)
    `;
    const params = [
      systemLog.level,
      systemLog.message,
      systemLog.source,
      systemLog.module || null,
      systemLog.user_id || null,
      systemLog.username || null,
      systemLog.ip_address || null,
      systemLog.details || null
    ];
    
    const result = await executeQuery(query, params) as any;
    return { ...systemLog, id: result.insertId, created_at: new Date() };
  }

  // 搜索系统日志
  static async search(searchTerm: string, limit?: number, offset?: number): Promise<SystemLog[]> {
    let query = `
      SELECT * FROM system_logs 
      WHERE message LIKE ? OR source LIKE ? OR module LIKE ? OR details LIKE ?
      ORDER BY created_at DESC
    `;
    const params: (string | number)[] = [
      `%${searchTerm}%`, `%${searchTerm}%`, `%${searchTerm}%`, `%${searchTerm}%`
    ];
    
    if (limit !== undefined) {
      query += ' LIMIT ?';
      params.push(limit);
      
      if (offset !== undefined) {
        query += ' OFFSET ?';
        params.push(offset);
      }
    }
    
    return await executeQuery(query, params);
  }

  // 获取搜索结果总数
  static async getSearchCount(searchTerm: string): Promise<number> {
    const query = `
      SELECT COUNT(*) as count 
      FROM system_logs 
      WHERE message LIKE ? OR source LIKE ? OR module LIKE ? OR details LIKE ?
    `;
    const params = [
      `%${searchTerm}%`, `%${searchTerm}%`, `%${searchTerm}%`, `%${searchTerm}%`
    ];
    const result = await executeQuery(query, params);
    return result[0].count;
  }

  // 清理旧的系统日志
  static async cleanup(daysToKeep: number = 30): Promise<number> {
    const query = 'DELETE FROM system_logs WHERE created_at < DATE_SUB(NOW(), INTERVAL ? DAY)';
    const result = await executeQuery(query, [daysToKeep]) as any;
    return result.affectedRows;
  }

  // 获取最近的系统日志
  static async getRecent(limit: number = 10): Promise<SystemLog[]> {
    const query = 'SELECT * FROM system_logs ORDER BY created_at DESC LIMIT ?';
    return await executeQuery(query, [limit]);
  }

  // 根据用户ID获取系统日志
  static async getByUserId(userId: number, limit?: number, offset?: number): Promise<SystemLog[]> {
    let query = 'SELECT * FROM system_logs WHERE user_id = ? ORDER BY created_at DESC';
    const params: (string | number)[] = [userId];
    
    if (limit !== undefined) {
      query += ' LIMIT ?';
      params.push(limit);
      
      if (offset !== undefined) {
        query += ' OFFSET ?';
        params.push(offset);
      }
    }
    
    return await executeQuery(query, params);
  }

  // 根据用户名获取系统日志
  static async getByUsername(username: string, limit?: number, offset?: number): Promise<SystemLog[]> {
    let query = 'SELECT * FROM system_logs WHERE username = ? ORDER BY created_at DESC';
    const params: (string | number)[] = [username];
    
    if (limit !== undefined) {
      query += ' LIMIT ?';
      params.push(limit);
      
      if (offset !== undefined) {
        query += ' OFFSET ?';
        params.push(offset);
      }
    }
    
    return await executeQuery(query, params);
  }

  // 获取完整的系统日志统计信息
  static async getFullStats(): Promise<any> {
    try {
      // 获取总数
      const totalQuery = 'SELECT COUNT(*) as count FROM system_logs';
      const totalResult = await executeQuery(totalQuery);
      const total = totalResult[0].count;

      // 获取各级别数量
      const levelQuery = `
        SELECT 
          level,
          COUNT(*) as count
        FROM system_logs
        GROUP BY level
      `;
      const levelStats = await executeQuery(levelQuery);
      const byLevel: Record<string, number> = {};
      
      levelStats.forEach((stat: any) => {
        byLevel[stat.level] = stat.count;
      });

      // 获取来源统计
      const sourceQuery = `
        SELECT 
          source,
          COUNT(*) as count
        FROM system_logs
        GROUP BY source
      `;
      const sourceStats = await executeQuery(sourceQuery);
      const bySource: Record<string, number> = {};
      
      sourceStats.forEach((stat: any) => {
        bySource[stat.source] = stat.count;
      });

      // 获取时间范围统计
      const todayQuery = `
        SELECT COUNT(*) as count 
        FROM system_logs 
        WHERE DATE(created_at) = CURDATE()
      `;
      const todayResult = await executeQuery(todayQuery);
      const today = todayResult[0].count;

      const weekQuery = `
        SELECT COUNT(*) as count 
        FROM system_logs 
        WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
      `;
      const weekResult = await executeQuery(weekQuery);
      const week = weekResult[0].count;

      const monthQuery = `
        SELECT COUNT(*) as count 
        FROM system_logs 
        WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
      `;
      const monthResult = await executeQuery(monthQuery);
      const month = monthResult[0].count;

      return {
        total,
        byLevel,
        bySource,
        byTimeRange: {
          today,
          week,
          month
        }
      };
    } catch (error) {
      console.error('获取系统日志统计信息失败:', error);
      // 返回默认值
      return {
        total: 0,
        byLevel: {},
        bySource: {},
        byTimeRange: {
          today: 0,
          week: 0,
          month: 0
        }
      };
    }
  }

  // 根据多个条件获取系统日志数量
  static async getCountByMultipleConditions(
    conditions: { [key: string]: any },
    startTime?: Date,
    endTime?: Date,
    fuzzyFields?: string[]
  ): Promise<number> {
    let query = 'SELECT COUNT(*) as count FROM system_logs WHERE 1=1';
    const params: any[] = [];
    
    for (const [key, value] of Object.entries(conditions)) {
      if (value !== undefined && value !== null) {
        if (fuzzyFields && fuzzyFields.includes(key)) {
          query += ` AND ${key} LIKE ?`;
          params.push(`%${value}%`);
        } else if (Array.isArray(value)) {
          const placeholders = value.map(() => '?').join(', ');
          query += ` AND ${key} IN (${placeholders})`;
          params.push(...value);
        } else {
          query += ` AND ${key} = ?`;
          params.push(value);
        }
      }
    }
    
    if (startTime && endTime) {
      query += ' AND created_at BETWEEN ? AND ?';
      params.push(startTime, endTime);
    }
    
    const result = await executeQuery(query, params) as any;
    return result[0].count;
  }

  // 根据多个条件获取系统日志
  static async getByMultipleConditions(
    conditions: { [key: string]: any },
    startTime?: Date,
    endTime?: Date,
    limit?: number,
    offset?: number,
    fuzzyFields?: string[]
  ): Promise<SystemLog[]> {
    let query = 'SELECT * FROM system_logs WHERE 1=1';
    const params: any[] = [];
    
    for (const [key, value] of Object.entries(conditions)) {
      if (value !== undefined && value !== null) {
        if (fuzzyFields && fuzzyFields.includes(key)) {
          query += ` AND ${key} LIKE ?`;
          params.push(`%${value}%`);
        } else if (Array.isArray(value)) {
          const placeholders = value.map(() => '?').join(', ');
          query += ` AND ${key} IN (${placeholders})`;
          params.push(...value);
        } else {
          query += ` AND ${key} = ?`;
          params.push(value);
        }
      }
    }
    
    if (startTime && endTime) {
      query += ' AND created_at BETWEEN ? AND ?';
      params.push(startTime, endTime);
    }
    
    query += ' ORDER BY created_at DESC';
    
    if (limit !== undefined && offset !== undefined) {
      query += ' LIMIT ? OFFSET ?';
      params.push(limit, offset);
    }
    
    return await executeQuery(query, params);
  }

  // 根据时间范围获取系统日志数量
  static async getCountByTimeRange(startTime: Date, endTime: Date): Promise<number> {
    const query = 'SELECT COUNT(*) as count FROM system_logs WHERE created_at BETWEEN ? AND ?';
    const result = await executeQuery(query, [startTime, endTime]) as any;
    return result[0].count;
  }

  // 根据日志级别获取系统日志数量
  static async getCountByLevel(level: string): Promise<number> {
    const query = 'SELECT COUNT(*) as count FROM system_logs WHERE level = ?';
    const result = await executeQuery(query, [level]) as any;
    return result[0].count;
  }

  // 根据来源获取系统日志数量
  static async getCountBySource(source: string): Promise<number> {
    const query = 'SELECT COUNT(*) as count FROM system_logs WHERE source = ?';
    const result = await executeQuery(query, [source]) as any;
    return result[0].count;
  }

  // 根据模块获取系统日志数量
  static async getCountByModule(module: string): Promise<number> {
    const query = 'SELECT COUNT(*) as count FROM system_logs WHERE module = ?';
    const result = await executeQuery(query, [module]) as any;
    return result[0].count;
  }

  // 根据用户ID获取系统日志数量
  static async getCountByUserId(userId: number): Promise<number> {
    const query = 'SELECT COUNT(*) as count FROM system_logs WHERE user_id = ?';
    const result = await executeQuery(query, [userId]) as any;
    return result[0].count;
  }

  // 根据用户名获取系统日志数量
  static async getCountByUsername(username: string): Promise<number> {
    const query = 'SELECT COUNT(*) as count FROM system_logs WHERE username = ?';
    const result = await executeQuery(query, [username]) as any;
    return result[0].count;
  }
}