using System.Collections;
using System.Data;
using System.Data.Common;
using System.Diagnostics.CodeAnalysis;
using MBN_STOCK_WEBVIEW.Core.Playout.Scenes;
using MMoneyCoderSharp.Data;
using Oracle.ManagedDataAccess.Client;

namespace MBN_STOCK_WEBVIEW.Infrastructure.Tests;

public sealed class OracleNamedPlaylistMutationExecutorTests
{
    [Fact]
    public void ConfigureOracleCommand_SetsBindByNameAndRejectsAnotherProviderCommand()
    {
        using var oracleCommand = new OracleCommand();

        OracleNamedPlaylistMutationExecutor.ConfigureOracleCommand(oracleCommand);

        Assert.True(oracleCommand.BindByName);
        using var fakeCommand = new MutationFakeCommand(
            new MutationFakeConnection(new MutationPlan()),
            new MutationPlan(),
            0);
        Assert.Throws<DatabaseConfigurationException>(
            () => OracleNamedPlaylistMutationExecutor.ConfigureOracleCommand(fakeCommand));
    }

    [Fact]
    public async Task ReplaceItemsAsync_UsesOneConnectionTransactionAndCommitWithBoundCommands()
    {
        var plan = new MutationPlan();
        var factory = new MutationConnectionFactory(plan, commandTimeoutSeconds: 17);
        var executor = CreateExecutor(factory);
        var service = CreateService(executor);

        await service.ReplaceItemsAsync(
            "00000011",
            [
                Item(0, "Alpha", "000001"),
                Item(1, "Beta", "000002")
            ]);

        Assert.Equal(1, factory.CreateCount);
        Assert.Equal([DataSourceKind.Oracle], factory.Sources);
        Assert.Equal(1, plan.OpenCount);
        Assert.Equal(1, plan.BeginCount);
        Assert.Equal(IsolationLevel.ReadCommitted, plan.ObservedIsolationLevel);
        Assert.Equal(1, plan.CommitCount);
        Assert.Equal(0, plan.RollbackCount);
        Assert.Equal(0, plan.SynchronousBeginCount);
        Assert.Equal(0, plan.SynchronousCommitCount);
        Assert.Equal(0, plan.SynchronousRollbackCount);
        Assert.True(plan.ConnectionDisposed);
        Assert.True(plan.TransactionDisposed);

        Assert.Collection(
            plan.Commands,
            command =>
            {
                Assert.Equal(NamedPlaylistDbCommandKind.DeleteItems, CommandKind(command));
                Assert.StartsWith(
                    "DELETE FROM PLAY_LIST",
                    command.CommandText.TrimStart(),
                    StringComparison.Ordinal);
                AssertParameter(
                    Assert.Single(command.ParametersSnapshot),
                    "program_code",
                    "00000011",
                    DbType.String);
            },
            command => AssertInsert(command, 0, "Alpha", "000001"),
            command => AssertInsert(command, 1, "Beta", "000002"));

        var transaction = Assert.IsType<MutationFakeTransaction>(plan.Transaction);
        foreach (var command in plan.Commands)
        {
            Assert.True(command.BindByName);
            Assert.Equal(CommandType.Text, command.CommandType);
            Assert.Equal(17, command.CommandTimeout);
            Assert.Same(transaction, command.ObservedTransaction);
            Assert.Equal(1, command.ExecuteCount);
            Assert.Equal(0, command.SynchronousExecuteCount);
            Assert.True(command.WasDisposed);
        }
    }

    [Fact]
    public async Task CommandFailure_RollsBackAndReportsKnownNonCommitWithoutRetry()
    {
        var plan = new MutationPlan
        {
            ExecuteAsync = (index, _) => index == 1
                ? Task.FromException<int>(new TestDatabaseException("command failure"))
                : Task.FromResult(1)
        };
        var factory = new MutationConnectionFactory(plan);
        var service = CreateService(CreateExecutor(factory));

        var exception = await Assert.ThrowsAsync<NamedPlaylistMutationException>(() =>
            service.ReplaceItemsAsync(
                "00000011",
                [Item(0, "Alpha", "000001"), Item(1, "Beta", "000002")]));

        Assert.False(exception.OutcomeUnknown);
        Assert.Equal(1, factory.CreateCount);
        Assert.Equal(2, plan.Commands.Count);
        Assert.Equal([1, 1], plan.Commands.Select(static command => command.ExecuteCount));
        Assert.Equal(1, plan.RollbackCount);
        Assert.Equal(0, plan.CommitCount);
    }

    [Fact]
    public async Task CommandTimeout_AttemptsRollbackButStillQuarantinesOutcome()
    {
        var plan = new MutationPlan
        {
            ExecuteAsync = (_, _) => Task.FromException<int>(new TimeoutException("timeout"))
        };
        var factory = new MutationConnectionFactory(plan);
        var service = CreateService(CreateExecutor(factory));

        var exception = await Assert.ThrowsAsync<NamedPlaylistMutationException>(
            () => service.DeleteAsync("00000011"));

        Assert.True(exception.OutcomeUnknown);
        Assert.Contains("do not retry", exception.Message, StringComparison.OrdinalIgnoreCase);
        Assert.Equal(1, plan.RollbackCount);
        Assert.Equal(0, plan.CommitCount);
        Assert.Equal(1, factory.CreateCount);
        Assert.Single(plan.Commands);
        Assert.Equal(1, plan.Commands[0].ExecuteCount);
    }

    [Fact]
    public async Task CommandCancellation_AttemptsRollbackButStillQuarantinesOutcome()
    {
        var plan = new MutationPlan
        {
            ExecuteAsync = (_, token) =>
                Task.FromException<int>(new OperationCanceledException(token))
        };
        var factory = new MutationConnectionFactory(plan);
        var service = CreateService(CreateExecutor(factory));

        var exception = await Assert.ThrowsAsync<NamedPlaylistMutationException>(
            () => service.DeleteAsync("00000011"));

        Assert.True(exception.OutcomeUnknown);
        Assert.Equal(1, plan.RollbackCount);
        Assert.Equal(0, plan.CommitCount);
        Assert.Equal(1, factory.CreateCount);
    }

    [Fact]
    public async Task OverallOperationTimeout_CancelsCommandOnceAndDoesNotRetry()
    {
        var plan = new MutationPlan
        {
            ExecuteAsync = (_, token) => WaitForCancellationAsync(token)
        };
        var factory = new MutationConnectionFactory(plan);
        var service = CreateService(CreateExecutor(factory, operationTimeoutSeconds: 1));

        var exception = await Assert.ThrowsAsync<NamedPlaylistMutationException>(
            () => service.DeleteAsync("00000011"));

        Assert.True(exception.OutcomeUnknown);
        Assert.Equal(1, factory.CreateCount);
        Assert.Single(plan.Commands);
        Assert.Equal(1, plan.Commands[0].ExecuteCount);
        Assert.Equal(1, plan.RollbackCount);
        Assert.Equal(0, plan.CommitCount);
    }

    [Fact]
    public async Task RollbackFailure_ChangesOrdinaryCommandFailureToOutcomeUnknown()
    {
        var plan = new MutationPlan
        {
            ExecuteAsync = (_, _) =>
                Task.FromException<int>(new TestDatabaseException("command failure")),
            RollbackAsync = _ =>
                Task.FromException(new TestDatabaseException("rollback failure"))
        };
        var factory = new MutationConnectionFactory(plan);
        var service = CreateService(CreateExecutor(factory));

        var exception = await Assert.ThrowsAsync<NamedPlaylistMutationException>(
            () => service.DeleteAsync("00000011"));

        Assert.True(exception.OutcomeUnknown);
        Assert.Equal(1, plan.RollbackCount);
        Assert.Equal(0, plan.CommitCount);
        Assert.Equal(1, factory.CreateCount);
    }

    [Fact]
    public async Task CommitFailure_IsOutcomeUnknownAndNeverIssuesRollbackOrRetry()
    {
        var plan = new MutationPlan
        {
            CommitAsync = _ =>
                Task.FromException(new TestDatabaseException("commit response lost"))
        };
        var factory = new MutationConnectionFactory(plan);
        var service = CreateService(CreateExecutor(factory));

        var exception = await Assert.ThrowsAsync<NamedPlaylistMutationException>(
            () => service.CreateDefinitionAsync("00000011", "Morning"));

        Assert.True(exception.OutcomeUnknown);
        Assert.Contains("do not retry", exception.Message, StringComparison.OrdinalIgnoreCase);
        Assert.Equal(1, plan.CommitCount);
        Assert.Equal(0, plan.RollbackCount);
        Assert.Equal(1, factory.CreateCount);
        Assert.Single(plan.Commands);
        Assert.Equal(1, plan.Commands[0].ExecuteCount);
    }

    [Fact]
    public async Task CommitCancellation_IsOutcomeUnknownAndNeverIssuesRollback()
    {
        var plan = new MutationPlan
        {
            CommitAsync = token =>
                Task.FromException(new OperationCanceledException(token))
        };
        var factory = new MutationConnectionFactory(plan);
        var service = CreateService(CreateExecutor(factory));

        var exception = await Assert.ThrowsAsync<NamedPlaylistMutationException>(
            () => service.CreateDefinitionAsync("00000011", "Morning"));

        Assert.True(exception.OutcomeUnknown);
        Assert.Equal(1, plan.CommitCount);
        Assert.Equal(0, plan.RollbackCount);
        Assert.Equal(1, factory.CreateCount);
    }

    [Fact]
    public async Task OpenFailure_HasKnownNonCommitAndCreatesNoTransaction()
    {
        var plan = new MutationPlan
        {
            OpenAsync = _ => Task.FromException(new TestDatabaseException("open failure"))
        };
        var factory = new MutationConnectionFactory(plan);
        var service = CreateService(CreateExecutor(factory));

        var exception = await Assert.ThrowsAsync<NamedPlaylistMutationException>(
            () => service.CreateDefinitionAsync("00000011", "Morning"));

        Assert.False(exception.OutcomeUnknown);
        Assert.Equal(1, factory.CreateCount);
        Assert.Equal(1, plan.OpenCount);
        Assert.Equal(0, plan.BeginCount);
        Assert.Equal(0, plan.RollbackCount);
        Assert.Equal(0, plan.CommitCount);
        Assert.Empty(plan.Commands);
        Assert.True(plan.ConnectionDisposed);
    }

    [Fact]
    public async Task BeginFailure_HasKnownNonCommitAndExecutesNoCommand()
    {
        var plan = new MutationPlan
        {
            BeginAsync = (_, _) =>
                Task.FromException<DbTransaction>(new TestDatabaseException("begin failure"))
        };
        var factory = new MutationConnectionFactory(plan);
        var service = CreateService(CreateExecutor(factory));

        var exception = await Assert.ThrowsAsync<NamedPlaylistMutationException>(
            () => service.CreateDefinitionAsync("00000011", "Morning"));

        Assert.False(exception.OutcomeUnknown);
        Assert.Equal(1, factory.CreateCount);
        Assert.Equal(1, plan.BeginCount);
        Assert.Equal(0, plan.RollbackCount);
        Assert.Equal(0, plan.CommitCount);
        Assert.Empty(plan.Commands);
    }

    [Fact]
    public async Task DeniedAuthorizationCreatesNoConnectionOrTransaction()
    {
        var plan = new MutationPlan();
        var factory = new MutationConnectionFactory(plan);
        var authorization = new DenyAllDatabaseMutationAuthorization();
        var service = CreateService(CreateExecutor(factory, mutationAuthorization: authorization));

        var exception = await Assert.ThrowsAsync<NamedPlaylistMutationException>(
            () => service.CreateDefinitionAsync("00000011", "Morning"));

        Assert.False(exception.OutcomeUnknown);
        var denied = Assert.IsType<DatabaseMutationAuthorizationException>(
            exception.InnerException);
        Assert.Equal(DatabaseMutationTarget.NamedPlaylist, denied.Request.Target);
        Assert.Equal(DataSourceKind.Oracle, denied.Request.Source);
        Assert.Equal(nameof(NamedPlaylistMutationKind.CreateDefinition), denied.Request.MutationKind);
        Assert.NotEqual(Guid.Empty, denied.Request.OperationId);
        Assert.Single(authorization.Requests);
        Assert.Equal(0, factory.CreateCount);
        Assert.Equal(0, plan.OpenCount);
        Assert.Equal(0, plan.BeginCount);
        Assert.Empty(plan.Commands);
    }

    [Fact]
    public void Constructor_RejectsInvalidCommandTimeout()
    {
        var factory = new MutationConnectionFactory(
            new MutationPlan(),
            commandTimeoutSeconds: 0);

        Assert.Throws<DatabaseConfigurationException>(() => CreateExecutor(factory));
        Assert.Equal(0, factory.CreateCount);
    }

    private static OracleNamedPlaylistMutationExecutor CreateExecutor(
        MutationConnectionFactory factory,
        int operationTimeoutSeconds = 30,
        IDatabaseMutationAuthorization? mutationAuthorization = null) =>
        new(
            factory,
            new DatabaseResilienceOptions
            {
                OperationTimeoutSeconds = operationTimeoutSeconds,
                MaximumRetryCount = 5,
                InitialRetryDelayMilliseconds = 30_000
            },
            new TransientDatabaseErrorDetector(),
            command => Assert.IsType<MutationFakeCommand>(command).BindByName = true,
            mutationAuthorization ?? DatabaseMutationAuthorization.AllowAll);

    private static LegacyNamedPlaylistPersistenceService CreateService(
        INamedPlaylistMutationExecutor mutations) =>
        new(new ForbiddenQueryExecutor(), mutations);

    private static NamedPlaylistStoredItem Item(
        int index,
        string subject,
        string dataCode) =>
        new(
            index,
            true,
            new LegacySceneSelection("KOSPI", subject, "1열판기본", "현재가", dataCode),
            new NamedPlaylistPageState(1, 1));

    private static void AssertInsert(
        MutationFakeCommand command,
        int expectedIndex,
        string expectedSubject,
        string expectedDataCode)
    {
        Assert.Equal(NamedPlaylistDbCommandKind.InsertItem, CommandKind(command));
        Assert.StartsWith(
            "INSERT INTO PLAY_LIST(",
            command.CommandText.TrimStart(),
            StringComparison.Ordinal);
        Assert.Equal(11, command.ParametersSnapshot.Count);
        AssertParameter(
            command.ParametersSnapshot[0],
            "program_code",
            "00000011",
            DbType.String);
        AssertParameter(
            command.ParametersSnapshot[1],
            "list_text",
            $"1^KOSPI^{expectedSubject}^1열판기본^현재가^1/1^{expectedDataCode}",
            DbType.String);
        foreach (var parameter in command.ParametersSnapshot.Skip(2).Take(8))
        {
            Assert.Same(DBNull.Value, parameter.Value);
            Assert.Equal(DbType.String, parameter.DbType);
            Assert.Equal(ParameterDirection.Input, parameter.Direction);
        }

        AssertParameter(
            command.ParametersSnapshot[10],
            "item_index",
            expectedIndex,
            DbType.Int32);
    }

    private static NamedPlaylistDbCommandKind CommandKind(MutationFakeCommand command)
    {
        var sql = command.CommandText.TrimStart();
        if (sql.StartsWith("INSERT INTO DC_LIST(", StringComparison.Ordinal))
        {
            return NamedPlaylistDbCommandKind.InsertDefinition;
        }

        if (sql.StartsWith("DELETE FROM PLAY_LIST", StringComparison.Ordinal))
        {
            return NamedPlaylistDbCommandKind.DeleteItems;
        }

        if (sql.StartsWith("INSERT INTO PLAY_LIST(", StringComparison.Ordinal))
        {
            return NamedPlaylistDbCommandKind.InsertItem;
        }

        if (sql.StartsWith("DELETE FROM DC_LIST", StringComparison.Ordinal))
        {
            return NamedPlaylistDbCommandKind.DeleteDefinition;
        }

        throw new InvalidOperationException("Unexpected command SQL.");
    }

    private static void AssertParameter(
        DbParameter parameter,
        string expectedName,
        object expectedValue,
        DbType expectedType)
    {
        Assert.Equal(expectedName, parameter.ParameterName);
        Assert.Equal(expectedValue, parameter.Value);
        Assert.Equal(expectedType, parameter.DbType);
        Assert.Equal(ParameterDirection.Input, parameter.Direction);
    }

    private static Task<int> WaitForCancellationAsync(CancellationToken token)
    {
        var completion = new TaskCompletionSource<int>(
            TaskCreationOptions.RunContinuationsAsynchronously);
        token.Register(() => completion.TrySetCanceled(token));
        return completion.Task;
    }

    private sealed class ForbiddenQueryExecutor : IDataQueryExecutor
    {
        public DataTable Execute(DataSourceKind source, string tableName, string query) =>
            throw new InvalidOperationException("A mutation test must not run a read query.");

        public Task<DataTable> ExecuteAsync(
            DataSourceKind source,
            string tableName,
            string query,
            CancellationToken cancellationToken = default) =>
            throw new InvalidOperationException("A mutation test must not run a read query.");
    }

    private sealed class MutationConnectionFactory(
        MutationPlan plan,
        int commandTimeoutSeconds = 15) : IDatabaseConnectionFactory
    {
        public int CreateCount { get; private set; }

        public List<DataSourceKind> Sources { get; } = [];

        public DbConnection Create(DataSourceKind source)
        {
            CreateCount++;
            Sources.Add(source);
            return new MutationFakeConnection(plan);
        }

        public int GetCommandTimeoutSeconds(DataSourceKind source)
        {
            Assert.Equal(DataSourceKind.Oracle, source);
            return commandTimeoutSeconds;
        }
    }

    private sealed class MutationPlan
    {
        public Func<CancellationToken, Task> OpenAsync { get; init; } =
            _ => Task.CompletedTask;

        public Func<IsolationLevel, CancellationToken, Task<DbTransaction>>? BeginAsync
        {
            get;
            init;
        }

        public Func<int, CancellationToken, Task<int>> ExecuteAsync { get; init; } =
            (_, _) => Task.FromResult(1);

        public Func<CancellationToken, Task> CommitAsync { get; init; } =
            _ => Task.CompletedTask;

        public Func<CancellationToken, Task> RollbackAsync { get; init; } =
            _ => Task.CompletedTask;

        public int OpenCount { get; set; }

        public int BeginCount { get; set; }

        public int CommitCount { get; set; }

        public int RollbackCount { get; set; }

        public int SynchronousBeginCount { get; set; }

        public int SynchronousCommitCount { get; set; }

        public int SynchronousRollbackCount { get; set; }

        public IsolationLevel? ObservedIsolationLevel { get; set; }

        public bool ConnectionDisposed { get; set; }

        public bool TransactionDisposed { get; set; }

        public MutationFakeTransaction? Transaction { get; set; }

        public List<MutationFakeCommand> Commands { get; } = [];
    }

    private sealed class MutationFakeConnection(MutationPlan plan) : DbConnection
    {
        private ConnectionState _state = ConnectionState.Closed;

        [AllowNull]
        public override string ConnectionString { get; set; } = string.Empty;

        public override string Database => "MutationFake";

        public override string DataSource => "MutationFake";

        public override string ServerVersion => "1.0";

        public override ConnectionState State => _state;

        public override void ChangeDatabase(string databaseName)
        {
        }

        public override void Close() => _state = ConnectionState.Closed;

        public override void Open() =>
            throw new InvalidOperationException("Synchronous open is forbidden.");

        public override async Task OpenAsync(CancellationToken cancellationToken)
        {
            plan.OpenCount++;
            await plan.OpenAsync(cancellationToken);
            _state = ConnectionState.Open;
        }

        protected override DbTransaction BeginDbTransaction(IsolationLevel isolationLevel)
        {
            plan.SynchronousBeginCount++;
            throw new InvalidOperationException("Synchronous begin is forbidden.");
        }

        protected override async ValueTask<DbTransaction> BeginDbTransactionAsync(
            IsolationLevel isolationLevel,
            CancellationToken cancellationToken)
        {
            plan.BeginCount++;
            plan.ObservedIsolationLevel = isolationLevel;
            if (plan.BeginAsync is not null)
            {
                return await plan.BeginAsync(isolationLevel, cancellationToken);
            }

            var transaction = new MutationFakeTransaction(this, plan, isolationLevel);
            plan.Transaction = transaction;
            return transaction;
        }

        protected override DbCommand CreateDbCommand()
        {
            var command = new MutationFakeCommand(this, plan, plan.Commands.Count);
            plan.Commands.Add(command);
            return command;
        }

        protected override void Dispose(bool disposing)
        {
            if (disposing)
            {
                plan.ConnectionDisposed = true;
                _state = ConnectionState.Closed;
            }

            base.Dispose(disposing);
        }

        public override ValueTask DisposeAsync()
        {
            plan.ConnectionDisposed = true;
            _state = ConnectionState.Closed;
            return ValueTask.CompletedTask;
        }
    }

    private sealed class MutationFakeTransaction(
        DbConnection connection,
        MutationPlan plan,
        IsolationLevel isolationLevel) : DbTransaction
    {
        public override IsolationLevel IsolationLevel => isolationLevel;

        protected override DbConnection DbConnection => connection;

        public override void Commit()
        {
            plan.SynchronousCommitCount++;
            throw new InvalidOperationException("Synchronous commit is forbidden.");
        }

        public override async Task CommitAsync(CancellationToken cancellationToken = default)
        {
            plan.CommitCount++;
            await plan.CommitAsync(cancellationToken);
        }

        public override void Rollback()
        {
            plan.SynchronousRollbackCount++;
            throw new InvalidOperationException("Synchronous rollback is forbidden.");
        }

        public override async Task RollbackAsync(CancellationToken cancellationToken = default)
        {
            plan.RollbackCount++;
            await plan.RollbackAsync(cancellationToken);
        }

        protected override void Dispose(bool disposing)
        {
            if (disposing)
            {
                plan.TransactionDisposed = true;
            }

            base.Dispose(disposing);
        }

        public override ValueTask DisposeAsync()
        {
            plan.TransactionDisposed = true;
            return ValueTask.CompletedTask;
        }
    }

    private sealed class MutationFakeCommand(
        DbConnection connection,
        MutationPlan plan,
        int commandIndex) : DbCommand
    {
        private readonly MutationFakeParameterCollection _parameters = new();

        public bool BindByName { get; set; }

        public int ExecuteCount { get; private set; }

        public int SynchronousExecuteCount { get; private set; }

        public bool WasDisposed { get; private set; }

        public DbTransaction? ObservedTransaction => DbTransaction;

        public IReadOnlyList<DbParameter> ParametersSnapshot =>
            _parameters.Cast<DbParameter>().ToArray();

        [AllowNull]
        public override string CommandText { get; set; } = string.Empty;

        public override int CommandTimeout { get; set; }

        public override CommandType CommandType { get; set; }

        public override bool DesignTimeVisible { get; set; }

        public override UpdateRowSource UpdatedRowSource { get; set; }

        protected override DbConnection? DbConnection { get; set; } = connection;

        protected override DbParameterCollection DbParameterCollection => _parameters;

        protected override DbTransaction? DbTransaction { get; set; }

        public override void Cancel()
        {
        }

        public override int ExecuteNonQuery()
        {
            SynchronousExecuteCount++;
            throw new InvalidOperationException("Synchronous execution is forbidden.");
        }

        public override async Task<int> ExecuteNonQueryAsync(
            CancellationToken cancellationToken)
        {
            ExecuteCount++;
            return await plan.ExecuteAsync(commandIndex, cancellationToken);
        }

        public override object? ExecuteScalar() => throw new NotSupportedException();

        public override void Prepare()
        {
        }

        protected override DbParameter CreateDbParameter() => new MutationFakeParameter();

        protected override DbDataReader ExecuteDbDataReader(CommandBehavior behavior) =>
            throw new NotSupportedException();

        protected override void Dispose(bool disposing)
        {
            if (disposing)
            {
                WasDisposed = true;
            }

            base.Dispose(disposing);
        }

        public override ValueTask DisposeAsync()
        {
            WasDisposed = true;
            return ValueTask.CompletedTask;
        }
    }

    private sealed class MutationFakeParameter : DbParameter
    {
        public override DbType DbType { get; set; }

        public override ParameterDirection Direction { get; set; }

        public override bool IsNullable { get; set; }

        [AllowNull]
        public override string ParameterName { get; set; } = string.Empty;

        public override int Size { get; set; }

        [AllowNull]
        public override string SourceColumn { get; set; } = string.Empty;

        public override bool SourceColumnNullMapping { get; set; }

        public override object? Value { get; set; }

        public override void ResetDbType() => DbType = default;
    }

    private sealed class MutationFakeParameterCollection : DbParameterCollection
    {
        private readonly List<DbParameter> _parameters = [];

        public override int Count => _parameters.Count;

        public override object SyncRoot => ((ICollection)_parameters).SyncRoot;

        public override int Add(object value)
        {
            _parameters.Add(RequireParameter(value));
            return _parameters.Count - 1;
        }

        public override void AddRange(Array values)
        {
            foreach (var value in values)
            {
                Add(value ?? throw new ArgumentNullException(nameof(values)));
            }
        }

        public override void Clear() => _parameters.Clear();

        public override bool Contains(object value) =>
            value is DbParameter parameter && _parameters.Contains(parameter);

        public override bool Contains(string value) => IndexOf(value) >= 0;

        public override void CopyTo(Array array, int index) =>
            ((ICollection)_parameters).CopyTo(array, index);

        public override IEnumerator GetEnumerator() => _parameters.GetEnumerator();

        public override int IndexOf(object value) =>
            value is DbParameter parameter ? _parameters.IndexOf(parameter) : -1;

        public override int IndexOf(string parameterName) =>
            _parameters.FindIndex(parameter => string.Equals(
                parameter.ParameterName,
                parameterName,
                StringComparison.OrdinalIgnoreCase));

        public override void Insert(int index, object value) =>
            _parameters.Insert(index, RequireParameter(value));

        public override void Remove(object value)
        {
            if (value is DbParameter parameter)
            {
                _parameters.Remove(parameter);
            }
        }

        public override void RemoveAt(int index) => _parameters.RemoveAt(index);

        public override void RemoveAt(string parameterName)
        {
            var index = IndexOf(parameterName);
            if (index >= 0)
            {
                RemoveAt(index);
            }
        }

        protected override DbParameter GetParameter(int index) => _parameters[index];

        protected override DbParameter GetParameter(string parameterName)
        {
            var index = IndexOf(parameterName);
            return index >= 0 ? _parameters[index] : throw new IndexOutOfRangeException();
        }

        protected override void SetParameter(int index, DbParameter value) =>
            _parameters[index] = value;

        protected override void SetParameter(string parameterName, DbParameter value)
        {
            var index = IndexOf(parameterName);
            if (index >= 0)
            {
                _parameters[index] = value;
            }
            else
            {
                _parameters.Add(value);
            }
        }

        private static DbParameter RequireParameter(object value) =>
            value as DbParameter ??
            throw new ArgumentException("A DbParameter is required.", nameof(value));
    }
}
