SQLVision/SQLVision.Services/Services/SqlExecutionService.cs

using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Caching.Memory;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Options;
using SQLVision.Core.Enums;
using SQLVision.Core.Interfaces;
using SQLVision.Core.Models;
using SQLVision.Services.Configuration;
using System.Collections.Concurrent;
using System.Data;
using System.Diagnostics;
using System.Security.Cryptography;
using System.Text;

namespace SQLVision.Services.Services;

public class SqlExecutionService : ISqlExecutionService, IDisposable
{
    private readonly IMemoryCache _cache;
    private readonly ILogger<SqlExecutionService> _logger;
    private readonly IOptions<DatabaseOptions> _options;
    private readonly ConcurrentDictionary<string, Task<DataTable>> _loadingComboBoxData = new();

    public SqlExecutionService(
        IMemoryCache cache,
        ILogger<SqlExecutionService> logger,
        IOptions<DatabaseOptions> options)
    {
        _cache = cache;
        _logger = logger;
        _options = options;
    }

    public async Task<ExecutionResult> ExecuteAsync(
        ScriptMetadata script,
        Dictionary<string, object> parameters,
        CancellationToken cancellationToken = default)
    {
        var stopwatch = Stopwatch.StartNew();
        var result = new ExecutionResult
        {
            Parameters = new Dictionary<string, object>(parameters),
            ExecutionDate = DateTime.UtcNow,
            ConnectionName = script.ConnectionString
        };

        try
        {
            // Генерация ключа кэша
            var cacheKey = GenerateCacheKey(script, parameters);
            if (_options.Value.Cache.Enabled)
            {
                if (_cache.TryGetValue<ExecutionResult>(cacheKey, out var cachedResult))
                {
                    _logger.LogDebug("Returning cached result for {Script}", script.FileName);
                    cachedResult!.IsFromCache = true;
                    cachedResult.ExecutionTime = stopwatch.Elapsed;
                    return cachedResult;
                }
            }

            // Подготовка SQL с параметрами
            var (processedSql, dbParameters) = PrepareSql(
                script.ProcessedSql,
                parameters,
                script.DatabaseProvider);

            result.ExecutedSql = processedSql;

            // Выполнение запроса
            var dataSet = await ExecuteQueryAsync(
                processedSql,
                dbParameters,
                script.ConnectionString ?? _options.Value.DefaultConnection,
                cancellationToken);

            stopwatch.Stop();

            result.Data = dataSet;
            result.IsSuccess = true;
            result.ExecutionTime = stopwatch.Elapsed;
            result.RowCount = dataSet.Tables.Cast<DataTable>().Sum(t => t.Rows.Count);
            result.Metrics = new Dictionary<string, object>
            {
                ["ExecutionTimeMs"] = stopwatch.ElapsedMilliseconds,
                ["RowsAffected"] = result.RowCount,
                ["TablesCount"] = dataSet.Tables.Count
            };

            // Кэширование результата
            if (_options.Value.Cache.Enabled && result.RowCount > 0)
            {
                var cacheOptions = new MemoryCacheEntryOptions
                {
                    AbsoluteExpirationRelativeToNow = TimeSpan.FromMinutes(_options.Value.Cache.DurationMinutes),
                    Size = CalculateDataSetSize(dataSet)
                };
                _cache.Set(cacheKey, result, cacheOptions);
            }

            _logger.LogInformation(
                "Executed {Script} in {ElapsedMs}ms, returned {Rows} rows",
                script.FileName, stopwatch.ElapsedMilliseconds, result.RowCount);

            return result;
        }
        catch (Exception ex)
        {
            stopwatch.Stop();
            result.IsSuccess = false;
            result.ErrorMessage = ex.Message;
            result.ExecutionTime = stopwatch.Elapsed;

            _logger.LogError(ex, "Error executing script {Script}", script.FileName);
            return result;
        }
    }

    private async Task<DataSet> ExecuteQueryAsync(
        string sql,
        List<SqlParameter> parameters,
        string connectionString,
        CancellationToken cancellationToken)
    {
        var dataSet = new DataSet();

        await using var connection = new SqlConnection(connectionString);
        await connection.OpenAsync(cancellationToken);

        await using var command = new SqlCommand(sql, connection);
        command.CommandTimeout = _options.Value.CommandTimeout;

        // Добавление параметров
        command.Parameters.AddRange(parameters.ToArray());

        await using var reader = await command.ExecuteReaderAsync(cancellationToken);

        do
        {
            var dataTable = new DataTable();
            dataTable.Load(reader);
            dataSet.Tables.Add(dataTable);
        } while (!reader.IsClosed && await reader.NextResultAsync(cancellationToken));

        return dataSet;
    }

    private (string Sql, List<SqlParameter> Parameters) PrepareSql(
        string sql,
        Dictionary<string, object> parameters,
        DatabaseProvider provider)
    {
        var dbParameters = new List<SqlParameter>();
        var processedSql = new StringBuilder(sql);

        foreach (var (key, value) in parameters)
        {
            var paramName = $"@{key}";
            var sqlParam = CreateSqlParameter(paramName, value);
            dbParameters.Add(sqlParam);
        }

        return (processedSql.ToString(), dbParameters);
    }

    private SqlParameter CreateSqlParameter(string name, object? value)
    {
        var sqlParam = new SqlParameter(name, value ?? DBNull.Value);

        // Автоматическое определение типа данных
        if (value is DateTime dateTime)
        {
            sqlParam.SqlDbType = SqlDbType.DateTime2;
            sqlParam.Value = dateTime;
        }
        else if (value is int intValue)
        {
            sqlParam.SqlDbType = SqlDbType.Int;
            sqlParam.Value = intValue;
        }
        else if (value is decimal decimalValue)
        {
            sqlParam.SqlDbType = SqlDbType.Decimal;
            sqlParam.Value = decimalValue;
        }
        else if (value is bool boolValue)
        {
            sqlParam.SqlDbType = SqlDbType.Bit;
            sqlParam.Value = boolValue;
        }
        else if (value is string stringValue)
        {
            sqlParam.SqlDbType = SqlDbType.NVarChar;
            sqlParam.Value = stringValue;
            sqlParam.Size = Math.Min(stringValue.Length * 2, 4000); // Ограничение для NVARCHAR
        }

        return sqlParam;
    }

    public async Task<ExecutionResult> ExecuteAsync(
        string sql,
        Dictionary<string, object> parameters,
        string connectionString,
        CancellationToken cancellationToken = default)
    {
        var script = new ScriptMetadata
        {
            ProcessedSql = sql,
            ConnectionString = connectionString,
            DatabaseProvider = DatabaseProvider.SqlServer
        };

        return await ExecuteAsync(script, parameters, cancellationToken);
    }

    public async Task<bool> TestConnectionAsync(
        string connectionString,
        DatabaseProvider provider,
        CancellationToken cancellationToken = default)
    {
        try
        {
            await using var connection = new SqlConnection(connectionString);
            await connection.OpenAsync(cancellationToken);
            await connection.CloseAsync();
            return true;
        }
        catch (Exception ex)
        {
            _logger.LogWarning(ex, "Connection test failed for {Provider}", provider);
            return false;
        }
    }

    public async Task<DataTable> LoadComboBoxDataAsync(
        string query,
        string connectionString,
        DatabaseProvider provider,
        CancellationToken cancellationToken = default)
    {
        var cacheKey = $"ComboBox_{provider}_{connectionString}_{query.GetHashCode()}";

        return await _loadingComboBoxData.GetOrAdd(cacheKey, async key =>
        {
            try
            {
                await using var connection = new SqlConnection(connectionString);
                await using var command = new SqlCommand(query, connection);
                command.CommandTimeout = 30;

                await connection.OpenAsync(cancellationToken);
                await using var reader = await command.ExecuteReaderAsync(cancellationToken);
                var dataTable = new DataTable();
                dataTable.Load(reader);

                return dataTable;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Failed to load combo box data for query: {Query}", query);
                throw;
            }
            finally
            {
                _loadingComboBoxData.TryRemove(key, out _);
            }
        });
    }

    private string GenerateCacheKey(ScriptMetadata script, Dictionary<string, object> parameters)
    {
        using var sha256 = SHA256.Create();

        // Создаем строку для хэширования
        var keyBuilder = new StringBuilder();
        keyBuilder.Append(script.Id);

        foreach (var param in parameters.OrderBy(p => p.Key))
        {
            keyBuilder.Append($"|{param.Key}={param.Value}");
        }

        var keyData = keyBuilder.ToString();
        var hash = sha256.ComputeHash(Encoding.UTF8.GetBytes(keyData));
        return Convert.ToBase64String(hash);
    }

    private long CalculateDataSetSize(DataSet dataSet)
    {
        long size = 0;
        foreach (DataTable table in dataSet.Tables)
        {
            // Примерный расчет размера: кол-во строк * кол-во столбцов * средний размер
            size += table.Rows.Count * table.Columns.Count * 64; // 64 байта на ячейку
        }
        return size;
    }

    public void Dispose()
    {
        // Очищаем кэш загрузки данных для ComboBox
        _loadingComboBoxData.Clear();
    }
}