#nullable enable

using System.Collections;
using System.Data;
using System.Data.Common;
using System.Diagnostics.CodeAnalysis;
using MMoneyCoderSharp.Data;
using Oracle.ManagedDataAccess.Client;

namespace MBN_STOCK_WEBVIEW.Infrastructure.Tests;

public sealed class OracleManualFinancialMutationExecutorTests
{
    private const string Version = "AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA";

    [Fact]
    public void ConfigureOracleCommand_sets_bind_by_name_and_rejects_another_provider()
    {
        using var oracleCommand = new OracleCommand();
        OracleManualFinancialMutationExecutor.ConfigureOracleCommand(oracleCommand);
        Assert.True(oracleCommand.BindByName);

        var plan = new Plan();
        using var fake = new FakeCommand(new FakeConnection(plan), plan, 0);
        Assert.Throws<DatabaseConfigurationException>(() =>
            OracleManualFinancialMutationExecutor.ConfigureOracleCommand(fake));
    }

    [Fact]
    public async Task Create_locks_then_executes_bound_command_once_and_commits_one_transaction()
    {
        var plan = new Plan
        {
            ExecuteAsync = (index, _) => Task.FromResult(index == 0 ? -1 : 1)
        };
        var factory = new Factory(plan, 17);
        var service = Service(Executor(factory));

        var receipt = await service.CreateAsync(Sales("Operator ' quoted"));

        Assert.Equal(1, factory.CreateCount);
        Assert.Equal(ManualFinancialMutationKind.Create, receipt.Kind);
        Assert.Equal(ManualFinancialScreenKind.Sales, receipt.Screen);
        Assert.Equal("Operator ' quoted", receipt.StockName);
        Assert.Equal(1, receipt.AffectedRows);
        Assert.NotEqual(Guid.Empty, receipt.OperationId);
        Assert.Equal(1, plan.OpenCount);
        Assert.Equal(1, plan.BeginCount);
        Assert.Equal(IsolationLevel.ReadCommitted, plan.IsolationLevel);
        Assert.Equal(1, plan.CommitCount);
        Assert.Equal(0, plan.RollbackCount);
        Assert.Equal(2, plan.Commands.Count);
        Assert.Equal("LOCK TABLE INPUT_SELL IN EXCLUSIVE MODE", plan.Commands[0].CommandText);
        Assert.Empty(plan.Commands[0].ParametersSnapshot);
        Assert.StartsWith("INSERT INTO INPUT_SELL", plan.Commands[1].CommandText.TrimStart(), StringComparison.Ordinal);
        Assert.DoesNotContain("Operator ' quoted", plan.Commands[1].CommandText, StringComparison.Ordinal);
        Assert.Equal("Operator ' quoted", plan.Commands[1].ParametersSnapshot
            .Single(parameter => parameter.ParameterName == "stock_name").Value);
        Assert.All(plan.Commands, command =>
        {
            Assert.True(command.BindByName);
            Assert.Equal(17, command.CommandTimeout);
            Assert.Equal(1, command.ExecuteCount);
            Assert.Equal(0, command.SynchronousExecuteCount);
            Assert.True(command.WasDisposed);
        });
        Assert.True(plan.ConnectionDisposed);
        Assert.True(plan.TransactionDisposed);
    }

    [Fact]
    public async Task Raw_update_accepts_Core_opaque_ROWID_command_and_binds_legacy_controls()
    {
        var plan = new Plan
        {
            ExecuteAsync = (index, _) => Task.FromResult(index == 0 ? -1 : 1)
        };
        var factory = new Factory(plan, 17);
        var service = new LegacyManualFinancialScreenService(
            new StaticRawQueryExecutor(),
            Executor(factory));
        var listed = Assert.Single((await service.SearchAsync(
            ManualFinancialScreenKind.Sales)).RawItems);
        var values = listed.Record.StorageValues.ToArray();
        values[0] = "  raw_\u0000value ";

        var receipt = await service.UpdateRawAsync(
            listed,
            new ManualFinancialRawRecord(listed.Record.Identity, values));

        Assert.Equal(ManualFinancialMutationKind.Update, receipt.Kind);
        Assert.Equal(2, plan.Commands.Count);
        var command = plan.Commands[1];
        Assert.Contains(
            "ROWID = CHARTOROWID(:row_handle)",
            command.CommandText,
            StringComparison.Ordinal);
        Assert.Equal("  raw_\u0000value ", command.ParametersSnapshot
            .Single(parameter => parameter.ParameterName == "field_1").Value);
        Assert.Equal("AAAAAAAAAAAAAAAAAA", command.ParametersSnapshot
            .Single(parameter => parameter.ParameterName == "row_handle").Value);
    }

    [Fact]
    public async Task Raw_update_row_version_mismatch_rolls_back_once_without_retry()
    {
        var plan = new Plan
        {
            ExecuteAsync = (index, _) => Task.FromResult(index == 0 ? -1 : 0)
        };
        var factory = new Factory(plan, 17);
        var service = new LegacyManualFinancialScreenService(
            new StaticRawQueryExecutor(),
            Executor(factory));
        var listed = Assert.Single((await service.SearchAsync(
            ManualFinancialScreenKind.Sales)).RawItems);
        var values = listed.Record.StorageValues.ToArray();
        values[0] = "A\u001fB";
        values[1] = "C\0";

        var exception = await Assert.ThrowsAsync<ManualFinancialMutationRejectedException>(() =>
            service.UpdateRawAsync(
                listed,
                new ManualFinancialRawRecord(listed.Record.Identity, values)));

        Assert.Equal(
            ManualFinancialMutationRejectionReason.NotFoundOrChanged,
            exception.Reason);
        Assert.False(exception.OutcomeUnknown);
        Assert.Equal(1, factory.CreateCount);
        Assert.Equal(1, plan.RollbackCount);
        Assert.Equal(0, plan.CommitCount);
        Assert.Equal(2, plan.Commands.Count);
        Assert.Equal([1, 1], plan.Commands.Select(command => command.ExecuteCount));
        Assert.Contains("CASE WHEN STOCK_NAME IS NULL THEN 'N'", plan.Commands[1].CommandText,
            StringComparison.Ordinal);
        Assert.DoesNotContain("CHR(31)", plan.Commands[1].CommandText, StringComparison.Ordinal);
    }

    [Fact]
    public async Task DeniedAuthorizationCreatesNoConnectionOrTransaction()
    {
        var plan = new Plan();
        var factory = new Factory(plan);
        var authorization = new DenyAllDatabaseMutationAuthorization();
        var service = Service(Executor(factory, mutationAuthorization: authorization));

        var exception = await Assert.ThrowsAsync<ManualFinancialMutationException>(() =>
            service.CreateAsync(Sales("Alpha")));

        Assert.False(exception.OutcomeUnknown);
        var denied = Assert.IsType<DatabaseMutationAuthorizationException>(
            exception.InnerException);
        Assert.Equal(DatabaseMutationTarget.ManualFinancial, denied.Request.Target);
        Assert.Equal(DataSourceKind.Oracle, denied.Request.Source);
        Assert.Equal(nameof(ManualFinancialMutationKind.Create), 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 async Task Zero_affected_create_rolls_back_as_known_duplicate_before_commit()
    {
        var plan = new Plan
        {
            ExecuteAsync = (index, _) => Task.FromResult(index == 0 ? -1 : 0)
        };
        var factory = new Factory(plan);
        var service = Service(Executor(factory));

        var exception = await Assert.ThrowsAsync<ManualFinancialMutationRejectedException>(() =>
            service.CreateAsync(Sales("Alpha")));

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

    [Fact]
    public async Task Command_timeout_is_OutcomeUnknown_and_is_never_retried()
    {
        var plan = new Plan
        {
            ExecuteAsync = (index, _) => index == 0
                ? Task.FromResult(-1)
                : Task.FromException<int>(new TimeoutException("late"))
        };
        var factory = new Factory(plan);
        var service = Service(Executor(factory));

        var exception = await Assert.ThrowsAsync<ManualFinancialMutationException>(() =>
            service.CreateAsync(Sales("Alpha")));

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

    [Fact]
    public async Task Commit_response_loss_is_OutcomeUnknown_without_rollback_or_retry()
    {
        var plan = new Plan
        {
            ExecuteAsync = (index, _) => Task.FromResult(index == 0 ? -1 : 1),
            CommitAsync = _ => Task.FromException(new TestDatabaseException("lost"))
        };
        var factory = new Factory(plan);
        var service = Service(Executor(factory));

        var exception = await Assert.ThrowsAsync<ManualFinancialMutationException>(() =>
            service.DeleteAsync(new ManualFinancialSnapshot(Sales("Alpha"), Version)));

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

    private static LegacyManualFinancialScreenService Service(
        IManualFinancialMutationExecutor executor) =>
        new(new ForbiddenQueryExecutor(), executor);

    private static OracleManualFinancialMutationExecutor Executor(
        Factory factory,
        int operationTimeoutSeconds = 30,
        IDatabaseMutationAuthorization? mutationAuthorization = null) =>
        new(
            factory,
            new DatabaseResilienceOptions
            {
                OperationTimeoutSeconds = operationTimeoutSeconds,
                MaximumRetryCount = 5,
                InitialRetryDelayMilliseconds = 30_000
            },
            new StubErrorDetector(false),
            command => Assert.IsType<FakeCommand>(command).BindByName = true,
            mutationAuthorization ?? DatabaseMutationAuthorization.AllowAll);

    private static ManualSalesRecord Sales(string stockName) =>
        new(
            new ManualFinancialIdentity(ManualFinancialScreenKind.Sales, stockName),
            [
                new("1Q", 10),
                new("2Q", 20),
                new("3Q", 30),
                new("4Q", 40),
                new("5Q", 50),
                new("6Q", 60)
            ]);

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

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

    private sealed class StaticRawQueryExecutor : IDataQueryExecutor
    {
        public DataTable Execute(DataSourceKind source, string tableName, string query) =>
            throw new NotSupportedException();

        public Task<DataTable> ExecuteAsync(
            DataSourceKind source,
            string tableName,
            string query,
            CancellationToken cancellationToken = default) =>
            throw new NotSupportedException();

        public Task<DataTable> ExecuteAsync(
            DataSourceKind source,
            string tableName,
            DataQuerySpec query,
            CancellationToken cancellationToken = default)
        {
            var table = new DataTable();
            foreach (var column in ManualFinancialCutContracts.Get(
                         ManualFinancialScreenKind.Sales).StorageColumns)
            {
                table.Columns.Add(column, typeof(string));
            }
            table.Columns.Add("ROW_VERSION", typeof(string));
            table.Columns.Add("ROW_HANDLE", typeof(string));
            table.Rows.Add(
                "Alpha",
                "_10",
                "2Q_20",
                "3Q_30",
                "4Q_40",
                "5Q_50",
                "6Q_60",
                Version,
                "AAAAAAAAAAAAAAAAAA");
            return Task.FromResult(table);
        }
    }

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

        public DbConnection Create(DataSourceKind source)
        {
            Assert.Equal(DataSourceKind.Oracle, source);
            CreateCount++;
            return new FakeConnection(plan);
        }

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

    private sealed class Plan
    {
        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 IsolationLevel? IsolationLevel { get; set; }

        public bool ConnectionDisposed { get; set; }

        public bool TransactionDisposed { get; set; }

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

    private sealed class FakeConnection(Plan plan) : DbConnection
    {
        private ConnectionState _state = ConnectionState.Closed;

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

        public override string Database => "Fake";

        public override string DataSource => "Fake";

        public override string ServerVersion => "1";

        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 Task OpenAsync(CancellationToken cancellationToken)
        {
            plan.OpenCount++;
            _state = ConnectionState.Open;
            return Task.CompletedTask;
        }

        protected override DbTransaction BeginDbTransaction(IsolationLevel isolationLevel) =>
            throw new InvalidOperationException("Synchronous begin is forbidden.");

        protected override ValueTask<DbTransaction> BeginDbTransactionAsync(
            IsolationLevel isolationLevel,
            CancellationToken cancellationToken)
        {
            plan.BeginCount++;
            plan.IsolationLevel = isolationLevel;
            return ValueTask.FromResult<DbTransaction>(new FakeTransaction(this, plan, isolationLevel));
        }

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

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

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

        protected override DbConnection DbConnection => connection;

        public override void Commit() => 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() => throw new InvalidOperationException("Synchronous rollback is forbidden.");

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

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

    private sealed class FakeCommand(
        DbConnection connection,
        Plan plan,
        int commandIndex) : DbCommand
    {
        private readonly FakeParameterCollection _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 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 FakeParameter();

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

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

    private sealed class FakeParameter : 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 FakeParameterCollection : 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(Require(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, Require(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 Require(object value) =>
            value as DbParameter ?? throw new ArgumentException("A parameter is required.");
    }
}