#nullable enable

using System.Data;
using System.Data.Common;
using MMoneyCoderSharp.Data;
using MySqlConnector;
using Oracle.ManagedDataAccess.Client;

namespace MBN_STOCK_WEBVIEW.Infrastructure;

/// <summary>
/// Executes the closed SB_LIST/SB_ITEM and EXPERT_LIST/RECOMMEND_LIST command
/// sets exactly once. Every operation uses one serializable transaction. There
/// is intentionally no transient retry path for state-changing commands.
/// </summary>
public sealed class OperatorCatalogMutationExecutor : IOperatorCatalogMutationExecutor
{
    private readonly IDatabaseConnectionFactory _connectionFactory;
    private readonly ITransientDatabaseErrorDetector _errorDetector;
    private readonly IDatabaseMutationAuthorization _mutationAuthorization;
    private readonly TimeSpan _operationTimeout;
    private readonly Action<DataSourceKind, DbCommand> _configureCommand;

    public OperatorCatalogMutationExecutor(
        IDatabaseConnectionFactory connectionFactory,
        DatabaseResilienceOptions resilienceOptions,
        ITransientDatabaseErrorDetector? errorDetector = null)
        : this(
            connectionFactory,
            resilienceOptions,
            errorDetector ?? new TransientDatabaseErrorDetector(),
            ConfigureProviderCommand,
            DatabaseMutationAuthorization.AllowAll)
    {
    }

    public OperatorCatalogMutationExecutor(
        IDatabaseConnectionFactory connectionFactory,
        DatabaseResilienceOptions resilienceOptions,
        ITransientDatabaseErrorDetector? errorDetector,
        IDatabaseMutationAuthorization mutationAuthorization)
        : this(
            connectionFactory,
            resilienceOptions,
            errorDetector ?? new TransientDatabaseErrorDetector(),
            ConfigureProviderCommand,
            mutationAuthorization)
    {
    }

    internal OperatorCatalogMutationExecutor(
        IDatabaseConnectionFactory connectionFactory,
        DatabaseResilienceOptions resilienceOptions,
        ITransientDatabaseErrorDetector errorDetector,
        Action<DataSourceKind, DbCommand> configureCommand)
        : this(
            connectionFactory,
            resilienceOptions,
            errorDetector,
            configureCommand,
            DatabaseMutationAuthorization.AllowAll)
    {
    }

    internal OperatorCatalogMutationExecutor(
        IDatabaseConnectionFactory connectionFactory,
        DatabaseResilienceOptions resilienceOptions,
        ITransientDatabaseErrorDetector errorDetector,
        Action<DataSourceKind, DbCommand> configureCommand,
        IDatabaseMutationAuthorization mutationAuthorization)
    {
        _connectionFactory = connectionFactory ??
            throw new ArgumentNullException(nameof(connectionFactory));
        ArgumentNullException.ThrowIfNull(resilienceOptions);
        _errorDetector = errorDetector ?? throw new ArgumentNullException(nameof(errorDetector));
        _configureCommand = configureCommand ??
            throw new ArgumentNullException(nameof(configureCommand));
        _mutationAuthorization = mutationAuthorization ??
            throw new ArgumentNullException(nameof(mutationAuthorization));

        var validationOptions = new DatabaseOptions { Resilience = resilienceOptions };
        validationOptions.ValidateRuntimeSettings();
        _operationTimeout = TimeSpan.FromSeconds(resilienceOptions.OperationTimeoutSeconds);
    }

    public async Task<OperatorCatalogDbTransactionResult> ExecuteTransactionAsync(
        OperatorCatalogDbTransaction transaction,
        CancellationToken cancellationToken = default)
    {
        ValidateTransaction(transaction);
        cancellationToken.ThrowIfCancellationRequested();
        try
        {
            DatabaseMutationAuthorizationGuard.Demand(
                _mutationAuthorization,
                new DatabaseMutationAuthorizationRequest(
                    DatabaseMutationTarget.OperatorCatalog,
                    transaction.Source,
                    transaction.OperationId,
                    transaction.Kind.ToString()));
        }
        catch (DatabaseMutationAuthorizationException exception)
        {
            throw new OperatorCatalogMutationException(
                $"The {transaction.Kind} transaction was denied before it started and did not commit.",
                outcomeUnknown: false,
                exception);
        }
        var commandTimeoutSeconds = _connectionFactory.GetCommandTimeoutSeconds(transaction.Source);
        if (commandTimeoutSeconds is < 1 or > 600)
        {
            throw new DatabaseConfigurationException(
                "The operator-catalog command timeout must be between 1 and 600 seconds.");
        }

        using var timeoutSource = CancellationTokenSource.CreateLinkedTokenSource(cancellationToken);
        timeoutSource.CancelAfter(_operationTimeout);
        var operationToken = timeoutSource.Token;

        DbConnection? connection = null;
        DbTransaction? databaseTransaction = null;
        var commitStarted = false;
        try
        {
            connection = _connectionFactory.Create(transaction.Source);
            await connection.OpenAsync(operationToken).ConfigureAwait(false);
            databaseTransaction = await connection.BeginTransactionAsync(
                    IsolationLevel.Serializable,
                    operationToken)
                .ConfigureAwait(false);

            var affectedRows = new List<int>(transaction.Commands.Count);
            foreach (var commandSpec in transaction.Commands)
            {
                operationToken.ThrowIfCancellationRequested();
                await using var command = connection.CreateCommand();
                command.CommandText = commandSpec.Sql;
                command.CommandType = CommandType.Text;
                command.CommandTimeout = commandTimeoutSeconds;
                command.Transaction = databaseTransaction;
                _configureCommand(transaction.Source, command);

                foreach (var parameterSpec in commandSpec.Parameters)
                {
                    var parameter = command.CreateParameter();
                    parameter.ParameterName = parameterSpec.Name;
                    parameter.Direction = ParameterDirection.Input;
                    parameter.DbType = parameterSpec.DbType;
                    parameter.Value = parameterSpec.Value ?? DBNull.Value;
                    command.Parameters.Add(parameter);
                }

                // Execute exactly once and enforce the optimistic/uniqueness
                // precondition before any later command can run.
                var count = await command.ExecuteNonQueryAsync(operationToken)
                    .ConfigureAwait(false);
                if (count < 0 ||
                    commandSpec.AffectedRowsRule ==
                        OperatorCatalogAffectedRowsRule.ExactlyOne && count != 1)
                {
                    throw new AffectedRowsConflictException(commandSpec.Kind, count);
                }

                affectedRows.Add(count);
            }

            commitStarted = true;
            await databaseTransaction.CommitAsync(operationToken).ConfigureAwait(false);
            return new OperatorCatalogDbTransactionResult(
                transaction.OperationId,
                Array.AsReadOnly(affectedRows.ToArray()),
                DateTimeOffset.UtcNow);
        }
        catch (Exception exception)
        {
            if (commitStarted)
            {
                // The provider may have committed even if the client observed a
                // timeout or disconnect. Do not issue rollback or retry commands.
                throw UnknownOutcome(transaction.Kind, exception);
            }

            if (databaseTransaction is null)
            {
                throw new OperatorCatalogMutationException(
                    $"The {transaction.Kind} transaction did not start and did not commit.",
                    outcomeUnknown: false,
                    exception);
            }

            var interrupted = exception is OperationCanceledException ||
                _errorDetector.IsTimeout(transaction.Source, exception);
            var rollbackSucceeded = await TryRollbackAsync(
                    databaseTransaction,
                    commandTimeoutSeconds)
                .ConfigureAwait(false);

            // Once a command is in flight, cancellation/timeout is treated as
            // OutcomeUnknown even when rollback returned. This is deliberately
            // conservative and prohibits an automatic retry.
            if (interrupted || !rollbackSucceeded)
            {
                throw UnknownOutcome(transaction.Kind, exception);
            }

            if (exception is AffectedRowsConflictException conflict)
            {
                throw new OperatorCatalogConflictException(
                    transaction.Kind,
                    conflict.CommandKind,
                    exception);
            }

            throw new OperatorCatalogMutationException(
                $"The {transaction.Kind} transaction was rolled back and did not commit.",
                outcomeUnknown: false,
                exception);
        }
        finally
        {
            await DisposeQuietlyAsync(databaseTransaction).ConfigureAwait(false);
            await DisposeQuietlyAsync(connection).ConfigureAwait(false);
        }
    }

    internal static void ConfigureProviderCommand(
        DataSourceKind source,
        DbCommand command)
    {
        ArgumentNullException.ThrowIfNull(command);
        switch (source)
        {
            case DataSourceKind.Oracle when command is OracleCommand oracleCommand:
                oracleCommand.BindByName = true;
                break;
            case DataSourceKind.MariaDb when command is MySqlCommand:
                break;
            case DataSourceKind.Oracle:
            case DataSourceKind.MariaDb:
                throw new DatabaseConfigurationException(
                    "The operator-catalog connection created the wrong provider command.");
            default:
                throw new ArgumentOutOfRangeException(nameof(source), source, null);
        }
    }

    private static async Task<bool> TryRollbackAsync(
        DbTransaction transaction,
        int timeoutSeconds)
    {
        try
        {
            using var rollbackTimeout = new CancellationTokenSource(
                TimeSpan.FromSeconds(timeoutSeconds));
            await transaction.RollbackAsync(rollbackTimeout.Token).ConfigureAwait(false);
            return true;
        }
        catch
        {
            return false;
        }
    }

    private static async Task DisposeQuietlyAsync(IAsyncDisposable? disposable)
    {
        if (disposable is null)
        {
            return;
        }

        try
        {
            await disposable.DisposeAsync().ConfigureAwait(false);
        }
        catch
        {
            // Commit/rollback outcome has already been classified. Disposal must
            // not replace that evidence or trigger another database operation.
        }
    }

    private static void ValidateTransaction(OperatorCatalogDbTransaction transaction)
    {
        ArgumentNullException.ThrowIfNull(transaction);
        if (transaction.OperationId == Guid.Empty ||
            !Enum.IsDefined(transaction.Kind) ||
            !Enum.IsDefined(transaction.Source) ||
            transaction.Commands.Count == 0 ||
            transaction.Commands.Any(static command => command is null))
        {
            throw new ArgumentException(
                "The operator-catalog transaction is invalid.",
                nameof(transaction));
        }

        var themeMutation = transaction.Kind is
            OperatorCatalogMutationKind.CreateTheme or
            OperatorCatalogMutationKind.ReplaceTheme or
            OperatorCatalogMutationKind.DeleteTheme;
        var validIdentityLength = transaction.Kind switch
        {
            OperatorCatalogMutationKind.CreateTheme => transaction.IdentityCode.Length == 8,
            OperatorCatalogMutationKind.ReplaceTheme or OperatorCatalogMutationKind.DeleteTheme =>
                transaction.IdentityCode.Length is >= 3 and <= 8,
            _ => transaction.IdentityCode.Length == 4
        };
        if (!validIdentityLength ||
            transaction.IdentityCode.Any(static character => !char.IsAsciiDigit(character)) ||
            !themeMutation && transaction.Source != DataSourceKind.Oracle)
        {
            throw new ArgumentException(
                "The operator-catalog transaction identity or source is invalid.",
                nameof(transaction));
        }

        var commandKinds = transaction.Commands.Select(static command => command.Kind).ToArray();
        var validShape = transaction.Kind switch
        {
            OperatorCatalogMutationKind.CreateTheme =>
                commandKinds[0] == OperatorCatalogDbCommandKind.InsertTheme &&
                commandKinds.Skip(1).All(static kind =>
                    kind == OperatorCatalogDbCommandKind.InsertThemeItem),
            OperatorCatalogMutationKind.ReplaceTheme =>
                commandKinds.Length >= 2 &&
                commandKinds[0] == OperatorCatalogDbCommandKind.UpdateTheme &&
                commandKinds[1] == OperatorCatalogDbCommandKind.DeleteThemeItems &&
                commandKinds.Skip(2).All(static kind =>
                    kind == OperatorCatalogDbCommandKind.InsertThemeItem),
            OperatorCatalogMutationKind.DeleteTheme =>
                commandKinds.SequenceEqual(
                    [
                        OperatorCatalogDbCommandKind.DeleteThemeItems,
                        OperatorCatalogDbCommandKind.DeleteTheme
                    ]),
            OperatorCatalogMutationKind.CreateExpert =>
                commandKinds[0] == OperatorCatalogDbCommandKind.InsertExpert &&
                commandKinds.Skip(1).All(static kind =>
                    kind == OperatorCatalogDbCommandKind.InsertRecommendation),
            OperatorCatalogMutationKind.ReplaceExpert =>
                commandKinds.Length >= 2 &&
                commandKinds[0] == OperatorCatalogDbCommandKind.UpdateExpert &&
                commandKinds[1] == OperatorCatalogDbCommandKind.DeleteRecommendations &&
                commandKinds.Skip(2).All(static kind =>
                    kind == OperatorCatalogDbCommandKind.InsertRecommendation),
            OperatorCatalogMutationKind.DeleteExpert =>
                commandKinds.SequenceEqual(
                    [
                        OperatorCatalogDbCommandKind.DeleteRecommendations,
                        OperatorCatalogDbCommandKind.DeleteExpert
                    ]),
            _ => false
        };
        if (!validShape)
        {
            throw new ArgumentException(
                "The operator-catalog transaction command order is invalid.",
                nameof(transaction));
        }

        var marker = transaction.Source == DataSourceKind.Oracle ? ':' : '@';
        var identityParameterName = themeMutation ? "theme_code" : "expert_code";
        foreach (var command in transaction.Commands)
        {
            if (string.IsNullOrWhiteSpace(command.Sql) ||
                command.Sql.Contains(';', StringComparison.Ordinal) ||
                !Enum.IsDefined(command.Kind) ||
                !Enum.IsDefined(command.AffectedRowsRule) ||
                command.Parameters.Count == 0 ||
                command.Parameters.Any(static parameter => parameter is null))
            {
                throw new ArgumentException(
                    "An operator-catalog command is invalid.",
                    nameof(transaction));
            }

            var names = new HashSet<string>(StringComparer.OrdinalIgnoreCase);
            foreach (var parameter in command.Parameters)
            {
                if (!names.Add(parameter.Name) ||
                    command.Sql.IndexOf(
                        string.Concat(marker, parameter.Name),
                        StringComparison.OrdinalIgnoreCase) < 0)
                {
                    throw new ArgumentException(
                        "An operator-catalog command parameter is invalid.",
                        nameof(transaction));
                }
            }

            var identityParameter = command.Parameters.SingleOrDefault(parameter =>
                string.Equals(
                    parameter.Name,
                    identityParameterName,
                    StringComparison.OrdinalIgnoreCase));
            if (identityParameter?.Value is not string commandIdentity ||
                !string.Equals(
                    commandIdentity,
                    transaction.IdentityCode,
                    StringComparison.Ordinal))
            {
                throw new ArgumentException(
                    "An operator-catalog command identity is invalid.",
                    nameof(transaction));
            }
        }
    }

    private static OperatorCatalogMutationException UnknownOutcome(
        OperatorCatalogMutationKind kind,
        Exception exception) =>
        new(
            $"The {kind} transaction outcome is unknown; do not retry automatically.",
            outcomeUnknown: true,
            exception);

    private sealed class AffectedRowsConflictException(
        OperatorCatalogDbCommandKind commandKind,
        int affectedRows)
        : Exception("An operator-catalog affected-row precondition failed.")
    {
        internal OperatorCatalogDbCommandKind CommandKind { get; } = commandKind;

        internal int AffectedRows { get; } = affectedRows;
    }
}
