using System.Data;
using System.Data.Common;
using MMoneyCoderSharp.Data;

namespace MBN_STOCK_WEBVIEW.Infrastructure;

public sealed class ResilientDataQueryExecutor : IDataQueryExecutor
{
    private readonly IDatabaseConnectionFactory _connectionFactory;
    private readonly ITransientDatabaseErrorDetector _errorDetector;
    private readonly TimeSpan _operationTimeout;
    private readonly int _maximumRetryCount;
    private readonly TimeSpan _initialRetryDelay;

    public ResilientDataQueryExecutor(
        IDatabaseConnectionFactory connectionFactory,
        DatabaseResilienceOptions resilienceOptions,
        ITransientDatabaseErrorDetector? errorDetector = null)
    {
        _connectionFactory = connectionFactory ?? throw new ArgumentNullException(nameof(connectionFactory));
        ArgumentNullException.ThrowIfNull(resilienceOptions);

        var validationOptions = new DatabaseOptions { Resilience = resilienceOptions };
        validationOptions.ValidateRuntimeSettings();

        _errorDetector = errorDetector ?? new TransientDatabaseErrorDetector();
        _operationTimeout = TimeSpan.FromSeconds(resilienceOptions.OperationTimeoutSeconds);
        _maximumRetryCount = resilienceOptions.MaximumRetryCount;
        _initialRetryDelay = TimeSpan.FromMilliseconds(resilienceOptions.InitialRetryDelayMilliseconds);
    }

    public DataTable Execute(DataSourceKind source, string tableName, string query) =>
        ExecuteAsync(source, tableName, query, CancellationToken.None)
            .ConfigureAwait(false)
            .GetAwaiter()
            .GetResult();

    public Task<DataTable> ExecuteAsync(
        DataSourceKind source,
        string tableName,
        string query,
        CancellationToken cancellationToken = default)
    {
        ArgumentException.ThrowIfNullOrWhiteSpace(query);

        return ExecuteAsyncCore(
            source,
            tableName,
            query,
            parameters: null,
            cancellationToken);
    }

    public Task<DataTable> ExecuteAsync(
        DataSourceKind source,
        string tableName,
        DataQuerySpec query,
        CancellationToken cancellationToken = default)
    {
        ArgumentNullException.ThrowIfNull(query);
        query.ValidateFor(source);

        return ExecuteAsyncCore(
            source,
            tableName,
            query.Sql,
            query.Parameters,
            cancellationToken);
    }

    private async Task<DataTable> ExecuteAsyncCore(
        DataSourceKind source,
        string tableName,
        string query,
        IReadOnlyList<DataQueryParameter>? parameters,
        CancellationToken cancellationToken)
    {
        using var timeoutSource = CancellationTokenSource.CreateLinkedTokenSource(cancellationToken);
        timeoutSource.CancelAfter(_operationTimeout);
        var operationToken = timeoutSource.Token;
        var retryableQuery = IsIdempotentReadQuery(query);

        for (var attempt = 0; ; attempt++)
        {
            try
            {
                return await ExecuteAttemptAsync(
                        source,
                        tableName,
                        query,
                        parameters,
                        operationToken)
                    .ConfigureAwait(false);
            }
            catch (OperationCanceledException) when (cancellationToken.IsCancellationRequested)
            {
                throw new OperationCanceledException(
                    "The database query was canceled.",
                    cancellationToken);
            }
            catch (OperationCanceledException)
            {
                throw new DatabaseQueryTimeoutException(source);
            }
            catch (DatabaseNotConfiguredException)
            {
                throw;
            }
            catch (DatabaseConfigurationException)
            {
                throw;
            }
            catch (Exception exception)
            {
                if (_errorDetector.IsTimeout(source, exception))
                {
                    throw new DatabaseQueryTimeoutException(source);
                }

                var isTransient = _errorDetector.IsTransient(source, exception);
                if (retryableQuery && isTransient && attempt < _maximumRetryCount)
                {
                    await DelayBeforeRetryAsync(source, attempt, operationToken, cancellationToken)
                        .ConfigureAwait(false);
                    continue;
                }

                throw new DatabaseQueryException(
                    source,
                    isTransient,
                    _errorDetector.GetProviderErrorCode(source, exception));
            }
        }
    }

    private async Task<DataTable> ExecuteAttemptAsync(
        DataSourceKind source,
        string tableName,
        string query,
        IReadOnlyList<DataQueryParameter>? parameters,
        CancellationToken cancellationToken)
    {
        await using var connection = _connectionFactory.Create(source);
        await connection.OpenAsync(cancellationToken).ConfigureAwait(false);

        await using var command = connection.CreateCommand();
        command.CommandText = query;
        command.CommandType = CommandType.Text;
        command.CommandTimeout = _connectionFactory.GetCommandTimeoutSeconds(source);

        if (parameters is not null)
        {
            foreach (var parameterSpec in parameters)
            {
                var parameter = command.CreateParameter();
                parameter.ParameterName = parameterSpec.Name;
                parameter.Direction = ParameterDirection.Input;
                parameter.Value = parameterSpec.Value ?? DBNull.Value;
                if (parameterSpec.DbType.HasValue)
                {
                    parameter.DbType = parameterSpec.DbType.Value;
                }

                command.Parameters.Add(parameter);
            }
        }

        await using var reader = await command
            .ExecuteReaderAsync(CommandBehavior.SequentialAccess, cancellationToken)
            .ConfigureAwait(false);

        return await ReadDataTableAsync(reader, tableName, cancellationToken)
            .ConfigureAwait(false);
    }

    private async Task DelayBeforeRetryAsync(
        DataSourceKind source,
        int completedAttempt,
        CancellationToken operationToken,
        CancellationToken callerToken)
    {
        if (_initialRetryDelay <= TimeSpan.Zero)
        {
            operationToken.ThrowIfCancellationRequested();
            return;
        }

        var multiplier = 1L << Math.Min(completedAttempt, 10);
        var delayMilliseconds = Math.Min(
            _initialRetryDelay.TotalMilliseconds * multiplier,
            30_000d);

        try
        {
            await Task.Delay(TimeSpan.FromMilliseconds(delayMilliseconds), operationToken)
                .ConfigureAwait(false);
        }
        catch (OperationCanceledException) when (callerToken.IsCancellationRequested)
        {
            throw;
        }
        catch (OperationCanceledException)
        {
            throw new DatabaseQueryTimeoutException(source);
        }
    }

    private static async Task<DataTable> ReadDataTableAsync(
        DbDataReader reader,
        string tableName,
        CancellationToken cancellationToken)
    {
        var table = new DataTable(string.IsNullOrWhiteSpace(tableName) ? "Result" : tableName)
        {
            Locale = System.Globalization.CultureInfo.InvariantCulture
        };

        var columnNames = new HashSet<string>(StringComparer.OrdinalIgnoreCase);
        for (var ordinal = 0; ordinal < reader.FieldCount; ordinal++)
        {
            var baseName = string.IsNullOrWhiteSpace(reader.GetName(ordinal))
                ? $"Column{ordinal + 1}"
                : reader.GetName(ordinal);
            var uniqueName = baseName;
            for (var suffix = 2; !columnNames.Add(uniqueName); suffix++)
            {
                uniqueName = $"{baseName}_{suffix}";
            }

            var fieldType = reader.GetFieldType(ordinal);
            table.Columns.Add(uniqueName, fieldType);
        }

        var values = new object[reader.FieldCount];
        table.BeginLoadData();
        try
        {
            while (await reader.ReadAsync(cancellationToken).ConfigureAwait(false))
            {
                reader.GetValues(values);
                table.LoadDataRow(values, true);
            }
        }
        finally
        {
            table.EndLoadData();
        }

        return table;
    }

    internal static bool IsIdempotentReadQuery(string query)
    {
        var span = query.AsSpan().TrimStart();
        while (span.StartsWith("--", StringComparison.Ordinal))
        {
            var lineEnd = span.IndexOfAny('\r', '\n');
            if (lineEnd < 0)
            {
                return false;
            }

            span = span[(lineEnd + 1)..].TrimStart();
        }

        if (!StartsWithToken(span, "SELECT") && !StartsWithToken(span, "WITH"))
        {
            return false;
        }

        // This is deliberately conservative. False negatives only disable a retry;
        // a false positive could repeat a state-changing statement.
        ReadOnlySpan<string> stateChangingTokens =
        [
            "INSERT",
            "UPDATE",
            "DELETE",
            "REPLACE",
            "MERGE",
            "CALL",
            "EXEC",
            "EXECUTE",
            "CREATE",
            "ALTER",
            "DROP",
            "TRUNCATE",
            "GRANT",
            "REVOKE",
            "INTO",
            "LOCK",
            "SET",
            "USE",
            "DO",
            "LOAD",
            "BEGIN",
            "DECLARE",
            "COMMIT",
            "ROLLBACK",
            "SAVEPOINT",
            "FUNCTION",
            "PROCEDURE",
            "PRAGMA"
        ];

        foreach (var token in stateChangingTokens)
        {
            if (ContainsToken(span, token))
            {
                return false;
            }
        }

        return true;
    }

    private static bool StartsWithToken(ReadOnlySpan<char> sql, ReadOnlySpan<char> token) =>
        sql.StartsWith(token, StringComparison.OrdinalIgnoreCase)
        && (sql.Length == token.Length || !IsIdentifierCharacter(sql[token.Length]));

    private static bool ContainsToken(ReadOnlySpan<char> sql, ReadOnlySpan<char> token)
    {
        for (var index = 0; index <= sql.Length - token.Length; index++)
        {
            if (!sql[index..].StartsWith(token, StringComparison.OrdinalIgnoreCase))
            {
                continue;
            }

            var hasLeftBoundary = index == 0 || !IsIdentifierCharacter(sql[index - 1]);
            var rightIndex = index + token.Length;
            var hasRightBoundary = rightIndex == sql.Length || !IsIdentifierCharacter(sql[rightIndex]);
            if (hasLeftBoundary && hasRightBoundary)
            {
                return true;
            }
        }

        return false;
    }

    private static bool IsIdentifierCharacter(char value) =>
        char.IsLetterOrDigit(value) || value is '_' or '$' or '#';
}
