using GSModbus.Config;
using SqlSugar;
using System.Text.Json;
using Oracle.ManagedDataAccess.Client;

namespace GSModbus.Database
{
    /// <summary>
    /// 数据库管理器 - 管理SqlSugar数据库连接和操作
    /// </summary>
    public class DatabaseManager : IDisposable
    {
        #region 私有字段

        private ISqlSugarClient? _db;
        private readonly DatabaseConfig _config;
        private readonly string _projectName;
        private readonly string _plcIpAddress;
        private bool _disposed = false;

        #endregion

        #region 事件

        /// <summary>
        /// 数据库操作日志事件
        /// </summary>
        public event EventHandler<string>? DatabaseLogOccurred;

        /// <summary>
        /// 数据库错误事件
        /// </summary>
        public event EventHandler<string>? DatabaseErrorOccurred;

        #endregion

        #region 构造函数

        /// <summary>
        /// 初始化数据库管理器
        /// </summary>
        /// <param name="config">数据库配置</param>
        /// <param name="projectName">项目名称</param>
        /// <param name="plcIpAddress">PLC IP地址</param>
        public DatabaseManager(DatabaseConfig config, string projectName, string plcIpAddress)
        {
            _config = config ?? throw new ArgumentNullException(nameof(config));
            _projectName = projectName ?? string.Empty;
            _plcIpAddress = plcIpAddress ?? string.Empty;

            if (_config.Enabled)
            {
                InitializeDatabase();
            }
        }

        #endregion

        #region 公共属性

        /// <summary>
        /// 数据库是否已启用
        /// </summary>
        public bool IsEnabled => _config.Enabled;

        /// <summary>
        /// 数据库是否已连接
        /// </summary>
        public bool IsConnected => _db != null;

        #endregion

        #region 初始化方法

        /// <summary>
        /// 初始化数据库连接
        /// </summary>
        private void InitializeDatabase()
        {
            try
            {
                OnDatabaseLog("开始初始化数据库连接...");
                
                // 对Oracle特殊处理，避免SqlSugar语法问题
                if (_config.Type.ToLower() == "oracle")
                {
                    InitializeOracleDirectly();
                }
                else
                {
                    InitializeWithSqlSugar();
                }

                OnDatabaseLog("数据库管理器初始化完成");
            }
            catch (Exception ex)
            {
                OnDatabaseError($"初始化数据库失败: {ex.Message}");
                throw;
            }
        }

        /// <summary>
        /// 直接初始化Oracle数据库
        /// </summary>
        private void InitializeOracleDirectly()
        {
            try
            {
                OnDatabaseLog("使用Oracle原生方式初始化...");
                
                // 先测试连接
                using (var testConnection = new OracleConnection(_config.ConnectionString))
                {
                    testConnection.Open();
                    OnDatabaseLog("Oracle数据库连接测试成功");
                    testConnection.Close();
                }

                // 创建SqlSugar客户端（只用于数据操作，不用于连接测试）
                var dbType = GetDbType(_config.Type);
                _db = new SqlSugarClient(new SqlSugar.ConnectionConfig()
                {
                    ConnectionString = _config.ConnectionString,
                    DbType = dbType,
                    IsAutoCloseConnection = true,
                    MoreSettings = new ConnMoreSettings()
                    {
                        IsAutoRemoveDataCache = true
                    }
                });

                OnDatabaseLog("SqlSugar客户端创建成功");

                // 自动创建表（使用直接SQL方式）
                if (_config.AutoCreateTables)
                {
                    CreateTablesIfNotExists();
                }
            }
            catch (Exception ex)
            {
                OnDatabaseError($"Oracle数据库初始化失败: {ex.Message}");
                throw;
            }
        }

        /// <summary>
        /// 使用SqlSugar初始化其他数据库
        /// </summary>
        private void InitializeWithSqlSugar()
        {
            // 根据数据库类型创建SqlSugarClient
            var dbType = GetDbType(_config.Type);
            
            _db = new SqlSugarClient(new SqlSugar.ConnectionConfig()
            {
                ConnectionString = _config.ConnectionString,
                DbType = dbType,
                IsAutoCloseConnection = true,
                
                // 配置SQL日志
                MoreSettings = new ConnMoreSettings()
                {
                    IsAutoRemoveDataCache = true
                }
            });
            
            // 配置日志事件
            _db.Aop.OnLogExecuting = (sql, pars) =>
            {
                OnDatabaseLog($"执行SQL: {sql}");
                if (pars?.Any() == true)
                {
                    OnDatabaseLog($"参数: {string.Join(", ", pars.Select(p => $"{p.ParameterName}={p.Value}"))}");
                }
            };

            // 测试连接
            var version = _db.Ado.GetString("SELECT @@VERSION");
            OnDatabaseLog($"数据库连接成功，版本: {version?.Substring(0, Math.Min(version.Length, 50))}...");

            // 自动创建表
            if (_config.AutoCreateTables)
            {
                CreateTablesIfNotExists();
            }
        }

        /// <summary>
        /// 获取SqlSugar数据库类型
        /// </summary>
        private DbType GetDbType(string dbType)
        {
            return dbType.ToLower() switch
            {
                "sqlserver" => DbType.SqlServer,
                "mysql" => DbType.MySql,
                "oracle" => DbType.Oracle,
                "sqlite" => DbType.Sqlite,
                "postgresql" => DbType.PostgreSQL,
                _ => throw new NotSupportedException($"不支持的数据库类型: {dbType}")
            };
        }

        /// <summary>
        /// 创建数据库表（如果不存在）
        /// </summary>
        private void CreateTablesIfNotExists()
        {
            if (_db == null) return;

            try
            {
                OnDatabaseLog("开始检查和创建数据库表...");

                // 对Oracle使用直接SQL建表，避免SqlSugar语法问题
                if (GetDbType(_config.Type) == DbType.Oracle)
                {
                    CreateOracleTablesDirectly();
                }
                else
                {
                    // 其他数据库使用SqlSugar CodeFirst
                    _db.CodeFirst.SetStringDefaultLength(200).InitTables(typeof(ModbusDataEntity));
                    OnDatabaseLog($"ModbusData表检查完成，表名: {_config.Tables.ModbusDataTable}");

                    _db.CodeFirst.SetStringDefaultLength(200).InitTables(typeof(CommunicationLogEntity));
                    OnDatabaseLog($"CommunicationLog表检查完成，表名: {_config.Tables.CommunicationLogTable}");

                    _db.CodeFirst.SetStringDefaultLength(200).InitTables(typeof(ErrorLogEntity));
                    OnDatabaseLog($"ErrorLog表检查完成，表名: {_config.Tables.ErrorLogTable}");

                    _db.CodeFirst.SetStringDefaultLength(200).InitTables(typeof(StatisticsEntity));
                    OnDatabaseLog($"Statistics表检查完成，表名: {_config.Tables.StatisticsTable}");
                }

                OnDatabaseLog("所有数据库表检查完成");
            }
            catch (Exception ex)
            {
                OnDatabaseError($"创建数据库表失败: {ex.Message}");
                throw;
            }
        }

        /// <summary>
        /// 直接使用SQL为Oracle创建表
        /// </summary>
        private void CreateOracleTablesDirectly()
        {
            try
            {
                OnDatabaseLog("开始使用原生Oracle SQL创建表...");
                
                using (var connection = new OracleConnection(_config.ConnectionString))
                {
                    connection.Open();
                    OnDatabaseLog("Oracle连接已打开，开始执行建表脚本...");

                    // 1. 创建ModbusData表（主要数据表）
                    var createModbusDataTable = $@"
                        BEGIN
                            EXECUTE IMMEDIATE 'CREATE TABLE {_config.Tables.ModbusDataTable} (
                                ID NUMBER PRIMARY KEY,
                                READ_TIME TIMESTAMP NOT NULL,
                                PROJECT_NAME VARCHAR2(100) NOT NULL,
                                PLC_IP_ADDRESS VARCHAR2(50) NOT NULL,
                                CONTROL_SIGNALS_JSON CLOB,
                                PRODUCT_DATA_JSON CLOB,
                                MEASUREMENT_DATA_JSON CLOB,
                                RAW_REGISTERS_JSON CLOB,
                                CREATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
                            )';
                        EXCEPTION
                            WHEN OTHERS THEN
                                IF SQLCODE != -955 THEN -- -955 = table already exists
                                    RAISE;
                                END IF;
                        END;";

                    using (var cmd = new OracleCommand(createModbusDataTable, connection))
                    {
                        cmd.ExecuteNonQuery();
                    }
                    OnDatabaseLog($"ModbusData表检查完成，表名: {_config.Tables.ModbusDataTable}");

                    // 2. 创建CommunicationLog表
                    var createCommLogTable = $@"
                        BEGIN
                            EXECUTE IMMEDIATE 'CREATE TABLE {_config.Tables.CommunicationLogTable} (
                                ID NUMBER PRIMARY KEY,
                                LOG_TIME TIMESTAMP NOT NULL,
                                PROJECT_NAME VARCHAR2(100) NOT NULL,
                                PLC_IP_ADDRESS VARCHAR2(50) NOT NULL,
                                EVENT_TYPE VARCHAR2(50) NOT NULL,
                                EVENT_DESCRIPTION VARCHAR2(500),
                                IS_SUCCESS NUMBER(1) DEFAULT 1,
                                DURATION_MS NUMBER,
                                ADDITIONAL_DATA_JSON CLOB
                            )';
                        EXCEPTION
                            WHEN OTHERS THEN
                                IF SQLCODE != -955 THEN
                                    RAISE;
                                END IF;
                        END;";

                    using (var cmd = new OracleCommand(createCommLogTable, connection))
                    {
                        cmd.ExecuteNonQuery();
                    }
                    OnDatabaseLog($"CommunicationLog表检查完成，表名: {_config.Tables.CommunicationLogTable}");

                    // 3. 创建ErrorLog表
                    var createErrorLogTable = $@"
                        BEGIN
                            EXECUTE IMMEDIATE 'CREATE TABLE {_config.Tables.ErrorLogTable} (
                                ID NUMBER PRIMARY KEY,
                                ERROR_TIME TIMESTAMP NOT NULL,
                                PROJECT_NAME VARCHAR2(100) NOT NULL,
                                PLC_IP_ADDRESS VARCHAR2(50) NOT NULL,
                                ERROR_TYPE VARCHAR2(100) NOT NULL,
                                ERROR_MESSAGE VARCHAR2(1000) NOT NULL,
                                STACK_TRACE CLOB,
                                SEVERITY VARCHAR2(20) DEFAULT ''Medium'',
                                RETRY_COUNT NUMBER DEFAULT 0,
                                IS_RESOLVED NUMBER(1) DEFAULT 0
                            )';
                        EXCEPTION
                            WHEN OTHERS THEN
                                IF SQLCODE != -955 THEN
                                    RAISE;
                                END IF;
                        END;";

                    using (var cmd = new OracleCommand(createErrorLogTable, connection))
                    {
                        cmd.ExecuteNonQuery();
                    }
                    OnDatabaseLog($"ErrorLog表检查完成，表名: {_config.Tables.ErrorLogTable}");

                    // 4. 创建Statistics表
                    var createStatsTable = $@"
                        BEGIN
                            EXECUTE IMMEDIATE 'CREATE TABLE {_config.Tables.StatisticsTable} (
                                ID NUMBER PRIMARY KEY,
                                STATISTICS_TIME TIMESTAMP NOT NULL,
                                PROJECT_NAME VARCHAR2(100) NOT NULL,
                                PLC_IP_ADDRESS VARCHAR2(50) NOT NULL,
                                TOTAL_CONNECTION_ATTEMPTS NUMBER NOT NULL,
                                SUCCESSFUL_CONNECTIONS NUMBER NOT NULL,
                                TOTAL_DATA_READS NUMBER NOT NULL,
                                SUCCESSFUL_DATA_READS NUMBER NOT NULL,
                                TOTAL_ERRORS NUMBER NOT NULL,
                                AVERAGE_RESPONSE_TIME_MS NUMBER NOT NULL,
                                MAX_RESPONSE_TIME_MS NUMBER NOT NULL,
                                MIN_RESPONSE_TIME_MS NUMBER NOT NULL,
                                CONNECTION_SUCCESS_RATE NUMBER NOT NULL,
                                DATA_READ_SUCCESS_RATE NUMBER NOT NULL,
                                PERIOD_START TIMESTAMP NOT NULL,
                                PERIOD_END TIMESTAMP NOT NULL
                            )';
                        EXCEPTION
                            WHEN OTHERS THEN
                                IF SQLCODE != -955 THEN
                                    RAISE;
                                END IF;
                        END;";

                    using (var cmd = new OracleCommand(createStatsTable, connection))
                    {
                        cmd.ExecuteNonQuery();
                    }
                    OnDatabaseLog($"Statistics表检查完成，表名: {_config.Tables.StatisticsTable}");

                    connection.Close();
                    OnDatabaseLog("所有Oracle表创建完成，连接已关闭");
                }
            }
            catch (Exception ex)
            {
                OnDatabaseError($"Oracle建表失败: {ex.Message}");
                throw;
            }
        }

        #endregion

        #region Modbus数据操作

        /// <summary>
        /// 保存Modbus数据到数据库
        /// </summary>
        /// <param name="data">动态Modbus数据</param>
        /// <returns>是否保存成功</returns>
        public async Task<bool> SaveModbusDataAsync(DynamicModbusData data)
        {
            if (!IsEnabled || _db == null || data == null) return false;

            try
            {
                var entity = new ModbusDataEntity
                {
                    ReadTime = data.ReadTime,
                    ProjectName = _projectName,
                    PlcIpAddress = _plcIpAddress,
                    CreatedAt = DateTime.Now
                };

                // 序列化各类别数据
                var controlSignals = GetFieldsByCategory(data, "ControlSignals");
                var productData = GetFieldsByCategory(data, "ProductData");
                var measurementData = GetFieldsByCategory(data, "MeasurementData");

                entity.ControlSignalsJson = controlSignals.Any() ? JsonSerializer.Serialize(controlSignals) : null;
                entity.ProductDataJson = productData.Any() ? JsonSerializer.Serialize(productData) : null;
                entity.MeasurementDataJson = measurementData.Any() ? JsonSerializer.Serialize(measurementData) : null;

                // 原始寄存器数据（调试用）
                if (data.RawRegisters?.Any() == true)
                {
                    entity.RawRegistersJson = JsonSerializer.Serialize(data.RawRegisters);
                }

                var result = await _db.Insertable(entity).ExecuteCommandAsync();
                
                if (result > 0)
                {
                    OnDatabaseLog($"保存Modbus数据成功，ID: {entity.Id}");
                    return true;
                }
                else
                {
                    OnDatabaseError("保存Modbus数据失败，返回结果为0");
                    return false;
                }
            }
            catch (Exception ex)
            {
                OnDatabaseError($"保存Modbus数据异常: {ex.Message}");
                return false;
            }
        }

        /// <summary>
        /// 批量保存Modbus数据
        /// </summary>
        /// <param name="dataList">数据列表</param>
        /// <returns>保存成功的数量</returns>
        public async Task<int> SaveModbusDataBatchAsync(IEnumerable<DynamicModbusData> dataList)
        {
            if (!IsEnabled || _db == null || !dataList.Any()) return 0;

            try
            {
                var entities = dataList.Select(data => new ModbusDataEntity
                {
                    ReadTime = data.ReadTime,
                    ProjectName = _projectName,
                    PlcIpAddress = _plcIpAddress,
                    ControlSignalsJson = JsonSerializer.Serialize(GetFieldsByCategory(data, "ControlSignals")),
                    ProductDataJson = JsonSerializer.Serialize(GetFieldsByCategory(data, "ProductData")),
                    MeasurementDataJson = JsonSerializer.Serialize(GetFieldsByCategory(data, "MeasurementData")),
                    RawRegistersJson = data.RawRegisters?.Any() == true ? JsonSerializer.Serialize(data.RawRegisters) : null,
                    CreatedAt = DateTime.Now
                }).ToList();

                var result = await _db.Insertable(entities).ExecuteCommandAsync();
                OnDatabaseLog($"批量保存Modbus数据完成，保存 {result} 条记录");
                return result;
            }
            catch (Exception ex)
            {
                OnDatabaseError($"批量保存Modbus数据异常: {ex.Message}");
                return 0;
            }
        }

        #endregion

        #region 日志操作

        /// <summary>
        /// 记录通信日志
        /// </summary>
        /// <param name="eventType">事件类型</param>
        /// <param name="description">事件描述</param>
        /// <param name="isSuccess">是否成功</param>
        /// <param name="durationMs">耗时毫秒</param>
        /// <param name="additionalData">附加数据</param>
        public async Task LogCommunicationAsync(string eventType, string? description = null, 
            bool isSuccess = true, int? durationMs = null, object? additionalData = null)
        {
            if (!IsEnabled || _db == null) return;

            try
            {
                var entity = new CommunicationLogEntity
                {
                    LogTime = DateTime.Now,
                    ProjectName = _projectName,
                    PlcIpAddress = _plcIpAddress,
                    EventType = eventType,
                    EventDescription = description,
                    IsSuccess = isSuccess,
                    DurationMs = durationMs ?? 0,
                    AdditionalDataJson = additionalData != null ? JsonSerializer.Serialize(additionalData) : null
                };

                await _db.Insertable(entity).ExecuteCommandAsync();
            }
            catch (Exception ex)
            {
                OnDatabaseError($"记录通信日志异常: {ex.Message}");
            }
        }

        /// <summary>
        /// 记录错误日志
        /// </summary>
        /// <param name="errorType">错误类型</param>
        /// <param name="errorMessage">错误消息</param>
        /// <param name="exception">异常对象</param>
        /// <param name="severity">严重级别</param>
        /// <param name="retryCount">重试次数</param>
        public async Task LogErrorAsync(string errorType, string errorMessage, 
            Exception? exception = null, string severity = ErrorSeverity.Medium, int retryCount = 0)
        {
            if (!IsEnabled || _db == null) return;

            try
            {
                var entity = new ErrorLogEntity
                {
                    ErrorTime = DateTime.Now,
                    ProjectName = _projectName,
                    PlcIpAddress = _plcIpAddress,
                    ErrorType = errorType,
                    ErrorMessage = errorMessage,
                    StackTrace = exception?.StackTrace,
                    Severity = severity,
                    RetryCount = retryCount,
                    IsResolved = false
                };

                await _db.Insertable(entity).ExecuteCommandAsync();
            }
            catch (Exception ex)
            {
                OnDatabaseError($"记录错误日志异常: {ex.Message}");
            }
        }

        #endregion

        #region 统计操作

        /// <summary>
        /// 保存统计数据
        /// </summary>
        /// <param name="stats">通信统计数据</param>
        /// <param name="periodStart">统计周期开始时间</param>
        /// <param name="periodEnd">统计周期结束时间</param>
        public async Task SaveStatisticsAsync(CommunicationStats stats, DateTime periodStart, DateTime periodEnd)
        {
            if (!IsEnabled || _db == null || stats == null) return;

            try
            {
                var entity = new StatisticsEntity
                {
                    StatisticsTime = DateTime.Now,
                    ProjectName = _projectName,
                    PlcIpAddress = _plcIpAddress,
                    TotalConnectionAttempts = (int)stats.TotalConnectionAttempts,
                    SuccessfulConnections = (int)stats.SuccessfulConnections,
                    TotalDataReads = (int)stats.TotalDataReads,
                    SuccessfulDataReads = (int)stats.SuccessfulReads,
                    TotalErrors = (int)stats.TotalErrors,
                    AverageResponseTimeMs = stats.AverageResponseTimeMs,
                    MaxResponseTimeMs = stats.MaxResponseTimeMs,
                    MinResponseTimeMs = stats.MinResponseTimeMs,
                    ConnectionSuccessRate = stats.ConnectionSuccessRate,
                    DataReadSuccessRate = stats.DataReadSuccessRate,
                    PeriodStart = periodStart,
                    PeriodEnd = periodEnd
                };

                await _db.Insertable(entity).ExecuteCommandAsync();
                OnDatabaseLog("统计数据保存成功");
            }
            catch (Exception ex)
            {
                OnDatabaseError($"保存统计数据异常: {ex.Message}");
            }
        }

        #endregion

        #region 数据查询

        /// <summary>
        /// 查询最近的Modbus数据
        /// </summary>
        /// <param name="count">数量</param>
        /// <returns>数据列表</returns>
        public async Task<List<ModbusDataEntity>> GetRecentModbusDataAsync(int count = 100)
        {
            if (!IsEnabled || _db == null) return new List<ModbusDataEntity>();

            try
            {
                return await _db.Queryable<ModbusDataEntity>()
                    .Where(x => x.ProjectName == _projectName)
                    .OrderBy(x => x.ReadTime, OrderByType.Desc)
                    .Take(count)
                    .ToListAsync();
            }
            catch (Exception ex)
            {
                OnDatabaseError($"查询Modbus数据异常: {ex.Message}");
                return new List<ModbusDataEntity>();
            }
        }

        /// <summary>
        /// 查询指定时间范围的数据统计
        /// </summary>
        /// <param name="startTime">开始时间</param>
        /// <param name="endTime">结束时间</param>
        /// <returns>统计信息</returns>
        public async Task<Dictionary<string, object>> GetDataStatisticsAsync(DateTime startTime, DateTime endTime)
        {
            if (!IsEnabled || _db == null) return new Dictionary<string, object>();

            try
            {
                var stats = new Dictionary<string, object>();

                // Modbus数据统计
                var dataCount = await _db.Queryable<ModbusDataEntity>()
                    .Where(x => x.ProjectName == _projectName && x.ReadTime >= startTime && x.ReadTime <= endTime)
                    .CountAsync();

                // 错误统计
                var errorCount = await _db.Queryable<ErrorLogEntity>()
                    .Where(x => x.ProjectName == _projectName && x.ErrorTime >= startTime && x.ErrorTime <= endTime)
                    .CountAsync();

                // 通信事件统计
                var logCount = await _db.Queryable<CommunicationLogEntity>()
                    .Where(x => x.ProjectName == _projectName && x.LogTime >= startTime && x.LogTime <= endTime)
                    .CountAsync();

                stats["DataCount"] = dataCount;
                stats["ErrorCount"] = errorCount;
                stats["LogCount"] = logCount;
                stats["Period"] = $"{startTime:yyyy-MM-dd HH:mm:ss} - {endTime:yyyy-MM-dd HH:mm:ss}";

                return stats;
            }
            catch (Exception ex)
            {
                OnDatabaseError($"查询数据统计异常: {ex.Message}");
                return new Dictionary<string, object>();
            }
        }

        #endregion

        #region 数据维护

        /// <summary>
        /// 清理过期数据
        /// </summary>
        /// <returns>清理的记录数</returns>
        public async Task<int> CleanupOldDataAsync()
        {
            if (!IsEnabled || _db == null || _config.DataRetentionDays <= 0) return 0;

            try
            {
                var cutoffDate = DateTime.Now.AddDays(-_config.DataRetentionDays);
                var totalDeleted = 0;

                // 清理Modbus数据
                var deletedData = await _db.Deleteable<ModbusDataEntity>()
                    .Where(x => x.ProjectName == _projectName && x.ReadTime < cutoffDate)
                    .ExecuteCommandAsync();

                // 清理通信日志
                var deletedLogs = await _db.Deleteable<CommunicationLogEntity>()
                    .Where(x => x.ProjectName == _projectName && x.LogTime < cutoffDate)
                    .ExecuteCommandAsync();

                // 清理已解决的错误日志
                var deletedErrors = await _db.Deleteable<ErrorLogEntity>()
                    .Where(x => x.ProjectName == _projectName && x.ErrorTime < cutoffDate && x.IsResolved)
                    .ExecuteCommandAsync();

                totalDeleted = deletedData + deletedLogs + deletedErrors;
                
                if (totalDeleted > 0)
                {
                    OnDatabaseLog($"数据清理完成，删除 {totalDeleted} 条记录（数据: {deletedData}, 日志: {deletedLogs}, 错误: {deletedErrors}）");
                }

                return totalDeleted;
            }
            catch (Exception ex)
            {
                OnDatabaseError($"清理过期数据异常: {ex.Message}");
                return 0;
            }
        }

        #endregion

        #region 连接测试方法

        /// <summary>
        /// 测试数据库连接
        /// </summary>
        /// <returns>连接测试结果</returns>
        public async Task<(bool Success, string Message)> TestConnectionAsync()
        {
            try
            {
                if (!_config.Enabled)
                {
                    return (false, "数据库功能已禁用");
                }

                if (string.IsNullOrEmpty(_config.ConnectionString))
                {
                    return (false, "数据库连接字符串为空");
                }

                // 对于Oracle数据库，使用原生连接方式测试
                if (_config.Type.ToLower() == "oracle")
                {
                    return await TestOracleConnectionAsync();
                }

                // 创建临时连接进行测试
                var dbType = GetDbType(_config.Type);
                using var testDb = new SqlSugarClient(new SqlSugar.ConnectionConfig()
                {
                    ConnectionString = _config.ConnectionString,
                    DbType = dbType,
                    IsAutoCloseConnection = true
                });

                // 执行简单查询测试连接
                string testQuery = dbType switch
                {
                    DbType.SqlServer => "SELECT 1",
                    DbType.MySql => "SELECT 1",
                    DbType.Sqlite => "SELECT 1",
                    DbType.PostgreSQL => "SELECT 1",
                    _ => "SELECT 1"
                };

                var result = await testDb.Ado.GetScalarAsync(testQuery);
                
                if (result != null)
                {
                    return (true, $"连接成功！数据库类型: {_config.Type}");
                }
                else
                {
                    return (false, "连接测试查询返回空结果");
                }
            }
            catch (Exception ex)
            {
                return (false, $"连接失败: {ex.Message}");
            }
        }

        /// <summary>
        /// 测试Oracle数据库连接的专用方法
        /// </summary>
        private async Task<(bool Success, string Message)> TestOracleConnectionAsync()
        {
            try
            {
                using var connection = new Oracle.ManagedDataAccess.Client.OracleConnection(_config.ConnectionString);
                await connection.OpenAsync();
                
                using var command = connection.CreateCommand();
                command.CommandText = "SELECT SYSDATE FROM DUAL";
                var result = await command.ExecuteScalarAsync();
                
                if (result != null)
                {
                    await connection.CloseAsync();
                    return (true, $"Oracle连接成功！当前时间: {result}");
                }
                else
                {
                    return (false, "Oracle连接测试查询返回空结果");
                }
            }
            catch (Exception ex)
            {
                return (false, $"Oracle连接失败: {ex.Message}");
            }
        }

        /// <summary>
        /// 静态方法：测试数据库连接（用于配置验证）
        /// </summary>
        /// <param name="config">数据库配置</param>
        /// <returns>连接测试结果</returns>
        public static async Task<(bool Success, string Message)> TestConnectionAsync(DatabaseConfig config)
        {
            if (config == null)
            {
                return (false, "数据库配置为空");
            }

            if (!config.Enabled)
            {
                return (false, "数据库功能已禁用");
            }

            if (string.IsNullOrEmpty(config.ConnectionString))
            {
                return (false, "数据库连接字符串为空");
            }

            try
            {
                // 对于Oracle，直接使用原生连接
                if (config.Type.ToLower() == "oracle")
                {
                    using var connection = new OracleConnection(config.ConnectionString);
                    await connection.OpenAsync();
                    
                    using var command = connection.CreateCommand();
                    command.CommandText = "SELECT SYSDATE FROM DUAL";
                    var result = await command.ExecuteScalarAsync();
                    
                    if (result != null)
                    {
                        await connection.CloseAsync();
                        return (true, $"Oracle连接成功！当前时间: {result}");
                    }
                    else
                    {
                        return (false, "Oracle连接测试查询返回空结果");
                    }
                }
                else
                {
                    // 其他数据库使用SqlSugar
                    var tempManager = new DatabaseManager(config, "TestConnection", "127.0.0.1");
                    try
                    {
                        return await tempManager.TestConnectionAsync();
                    }
                    finally
                    {
                        tempManager.Dispose();
                    }
                }
            }
            catch (Exception ex)
            {
                return (false, $"连接失败: {ex.Message}");
            }
        }

        #endregion

        #region 辅助方法

        /// <summary>
        /// 从动态数据中获取指定类别的字段
        /// </summary>
        private Dictionary<string, object?> GetFieldsByCategory(DynamicModbusData data, string category)
        {
            var result = new Dictionary<string, object?>();

            if (data.SourceConfiguration?.InputAddresses == null) return result;

            Dictionary<string, DataField>? fields = category switch
            {
                "ControlSignals" => data.SourceConfiguration.InputAddresses.ControlSignals,
                "ProductData" => data.SourceConfiguration.InputAddresses.ProductData,
                "MeasurementData" => data.SourceConfiguration.InputAddresses.MeasurementData,
                _ => null
            };

            if (fields != null)
            {
                foreach (var field in fields)
                {
                    if (data.HasField(field.Key))
                    {
                        result[field.Key] = data.GetFieldValue(field.Key);
                    }
                }
            }

            return result;
        }

        /// <summary>
        /// 触发数据库日志事件
        /// </summary>
        private void OnDatabaseLog(string message)
        {
            DatabaseLogOccurred?.Invoke(this, $"[数据库] {message}");
        }

        /// <summary>
        /// 触发数据库错误事件
        /// </summary>
        private void OnDatabaseError(string errorMessage)
        {
            DatabaseErrorOccurred?.Invoke(this, $"[数据库错误] {errorMessage}");
        }

        #endregion

        #region IDisposable实现

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        protected virtual void Dispose(bool disposing)
        {
            if (!_disposed)
            {
                if (disposing)
                {
                    try
                    {
                        _db?.Dispose();
                        OnDatabaseLog("数据库连接已关闭");
                    }
                    catch (Exception ex)
                    {
                        OnDatabaseError($"关闭数据库连接异常: {ex.Message}");
                    }
                }

                _db = null;
                _disposed = true;
            }
        }

        #endregion
    }
}