#nullable enable

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

namespace MBN_STOCK_WEBVIEW.Infrastructure;

/// <summary>
/// Executes the closed DC_LIST/PLAY_LIST mutation contract on one Oracle
/// connection and one transaction. Unlike the read executor, this class has no
/// retry loop: repeating a write after a timeout or ambiguous commit could
/// duplicate or destroy an operator playlist.
/// </summary>
public sealed class OracleNamedPlaylistMutationExecutor : INamedPlaylistMutationExecutor
{
    private readonly IDatabaseConnectionFactory _connectionFactory;
    private readonly ITransientDatabaseErrorDetector _errorDetector;
    private readonly IDatabaseMutationAuthorization _mutationAuthorization;
    private readonly TimeSpan _operationTimeout;
    private readonly int _commandTimeoutSeconds;
    private readonly Action<DbCommand> _configureCommand;

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

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

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

    internal OracleNamedPlaylistMutationExecutor(
        IDatabaseConnectionFactory connectionFactory,
        DatabaseResilienceOptions resilienceOptions,
        ITransientDatabaseErrorDetector errorDetector,
        Action<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);
        _commandTimeoutSeconds = connectionFactory.GetCommandTimeoutSeconds(DataSourceKind.Oracle);
        if (_commandTimeoutSeconds is < 1 or > 600)
        {
            throw new DatabaseConfigurationException(
                "The Oracle named-playlist command timeout must be between 1 and 600 seconds.");
        }
    }

    public async Task<NamedPlaylistDbTransactionResult> ExecuteTransactionAsync(
        DataSourceKind source,
        NamedPlaylistDbTransaction transaction,
        CancellationToken cancellationToken = default)
    {
        if (source != DataSourceKind.Oracle)
        {
            throw new ArgumentOutOfRangeException(
                nameof(source),
                source,
                "Named playlists are stored in Oracle.");
        }

        ValidateTransaction(transaction);
        cancellationToken.ThrowIfCancellationRequested();
        try
        {
            DatabaseMutationAuthorizationGuard.Demand(
                _mutationAuthorization,
                new DatabaseMutationAuthorizationRequest(
                    DatabaseMutationTarget.NamedPlaylist,
                    DataSourceKind.Oracle,
                    transaction.OperationId,
                    transaction.Kind.ToString()));
        }
        catch (DatabaseMutationAuthorizationException exception)
        {
            throw new NamedPlaylistMutationException(
                $"The {transaction.Kind} transaction was denied before it started and did not commit.",
                outcomeUnknown: false,
                exception);
        }

        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(DataSourceKind.Oracle);
            await connection.OpenAsync(operationToken).ConfigureAwait(false);
            databaseTransaction = await connection.BeginTransactionAsync(
                    IsolationLevel.ReadCommitted,
                    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(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. There is deliberately no transient retry.
                var count = await command.ExecuteNonQueryAsync(operationToken)
                    .ConfigureAwait(false);
                affectedRows.Add(count);
            }

            commitStarted = true;
            await databaseTransaction.CommitAsync(operationToken).ConfigureAwait(false);
            return new NamedPlaylistDbTransactionResult(
                transaction.OperationId,
                Array.AsReadOnly(affectedRows.ToArray()),
                DateTimeOffset.UtcNow);
        }
        catch (Exception exception)
        {
            if (commitStarted)
            {
                // Commit may have reached Oracle even when the client observed a
                // timeout/cancellation/disconnect. A rollback here cannot prove
                // whether commit succeeded, so do not issue another DB command.
                throw UnknownOutcome(transaction.Kind, exception);
            }

            var isCancellationOrTimeout = exception is OperationCanceledException ||
                _errorDetector.IsTimeout(DataSourceKind.Oracle, exception);
            var rollbackFailed = false;
            if (databaseTransaction is not null)
            {
                rollbackFailed = !await TryRollbackAsync(databaseTransaction)
                    .ConfigureAwait(false);
            }

            var outcomeUnknown = databaseTransaction is not null &&
                (isCancellationOrTimeout || rollbackFailed);
            if (outcomeUnknown)
            {
                throw UnknownOutcome(transaction.Kind, exception);
            }

            throw new NamedPlaylistMutationException(
                $"The {transaction.Kind} Oracle transaction did not commit.",
                outcomeUnknown: false,
                exception);
        }
        finally
        {
            if (databaseTransaction is not null)
            {
                await databaseTransaction.DisposeAsync().ConfigureAwait(false);
            }

            if (connection is not null)
            {
                await connection.DisposeAsync().ConfigureAwait(false);
            }
        }
    }

    internal static void ConfigureOracleCommand(DbCommand command)
    {
        ArgumentNullException.ThrowIfNull(command);
        if (command is not OracleCommand oracleCommand)
        {
            throw new DatabaseConfigurationException(
                "The named-playlist mutation connection did not create an Oracle command.");
        }

        oracleCommand.BindByName = true;
    }

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

    private static void ValidateTransaction(NamedPlaylistDbTransaction transaction)
    {
        ArgumentNullException.ThrowIfNull(transaction);
        if (transaction.OperationId == Guid.Empty ||
            transaction.ProgramCode.Length != 8 ||
            transaction.ProgramCode.Any(static character => !char.IsAsciiDigit(character)) ||
            transaction.Commands.Count == 0 ||
            transaction.Commands.Any(static command => command is null))
        {
            throw new ArgumentException(
                "The named-playlist transaction is invalid.",
                nameof(transaction));
        }

        var validShape = transaction.Kind switch
        {
            NamedPlaylistMutationKind.CreateDefinition =>
                transaction.Commands.Count == 1 &&
                transaction.Commands[0].Kind == NamedPlaylistDbCommandKind.InsertDefinition,
            NamedPlaylistMutationKind.ReplaceItems =>
                transaction.Commands[0].Kind == NamedPlaylistDbCommandKind.DeleteItems &&
                transaction.Commands.Skip(1).All(static command =>
                    command.Kind == NamedPlaylistDbCommandKind.InsertItem),
            NamedPlaylistMutationKind.DeletePlaylist =>
                transaction.Commands.Count == 2 &&
                transaction.Commands[0].Kind == NamedPlaylistDbCommandKind.DeleteItems &&
                transaction.Commands[1].Kind == NamedPlaylistDbCommandKind.DeleteDefinition,
            _ => false
        };
        if (!validShape)
        {
            throw new ArgumentException(
                "The named-playlist transaction command order is invalid.",
                nameof(transaction));
        }

        foreach (var command in transaction.Commands)
        {
            if (string.IsNullOrWhiteSpace(command.Sql) || command.Sql.Contains(';'))
            {
                throw new ArgumentException(
                    "The named-playlist transaction SQL is invalid.",
                    nameof(transaction));
            }

            var programCodeParameters = command.Parameters.Where(static parameter =>
                string.Equals(parameter.Name, "program_code", StringComparison.OrdinalIgnoreCase));
            var programCodeParameter = programCodeParameters.SingleOrDefault();
            if (programCodeParameter?.Value is not string commandProgramCode ||
                !string.Equals(
                    commandProgramCode,
                    transaction.ProgramCode,
                    StringComparison.Ordinal))
            {
                throw new ArgumentException(
                    "The named-playlist transaction program identity is invalid.",
                    nameof(transaction));
            }
        }
    }

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