using System.Collections;
using System.Data;
using System.Data.Common;
using System.Diagnostics.CodeAnalysis;
using MBN_STOCK_WEBVIEW.Infrastructure;
using MMoneyCoderSharp.Data;

namespace MBN_STOCK_WEBVIEW.Infrastructure.Tests;

public sealed class OperatorCatalogMutationExecutorTests
{
    [Fact]
    public async Task ExecuteTransactionAsync_UsesOneSerializableTransactionAndCommitsOnce()
    {
        var plan = new CatalogMutationConnectionPlan();
        plan.NonQuerySteps.Enqueue(_ => Task.FromResult(1));
        plan.NonQuerySteps.Enqueue(_ => Task.FromResult(1));
        plan.NonQuerySteps.Enqueue(_ => Task.FromResult(1));
        var factory = new CatalogMutationConnectionFactory(plan);
        var executor = CreateExecutor(factory);
        var service = new LegacyExpertCatalogPersistenceService(
            new CatalogNoQueryExecutor(),
            executor);

        var receipt = await service.CreateAsync(new ExpertCatalogDefinition(
            new ExpertSelectionIdentity("0042", "전문가"),
            [
                new ExpertCatalogRecommendation(0, "005930", "삼성전자", 70000m),
                new ExpertCatalogRecommendation(1, "035720", "카카오", 45000m)
            ]));

        Assert.Equal(OperatorCatalogMutationKind.CreateExpert, receipt.Kind);
        Assert.Equal(1, factory.CreateCount);
        Assert.Equal(DataSourceKind.Oracle, factory.LastSource);
        Assert.Equal(IsolationLevel.Serializable, plan.IsolationLevel);
        Assert.Equal(1, plan.BeginCount);
        Assert.Equal(1, plan.CommitCount);
        Assert.Equal(0, plan.RollbackCount);
        Assert.Equal(3, plan.Commands.Count);
        Assert.Empty(plan.NonQuerySteps);
        Assert.All(plan.Commands, command =>
        {
            Assert.Equal(7, command.CommandTimeoutSeconds);
            Assert.True(command.HadTransaction);
            Assert.All(command.Parameters, parameter =>
                Assert.Equal(ParameterDirection.Input, parameter.Direction));
        });
        Assert.Contains("INSERT INTO EXPERT_LIST", plan.Commands[0].Sql, StringComparison.Ordinal);
        Assert.Equal("0042", Parameter(plan.Commands[0], "expert_code").Value);
        Assert.Equal("전문가", Parameter(plan.Commands[0], "expert_name").Value);
        Assert.Contains("INSERT INTO RECOMMEND_LIST", plan.Commands[1].Sql, StringComparison.Ordinal);
        Assert.Equal(70000m, Parameter(plan.Commands[1], "buy_amount").Value);
        Assert.Equal(1, plan.ConnectionDisposeCount);
        Assert.Equal(1, plan.TransactionDisposeCount);
    }

    [Fact]
    public async Task ExecuteTransactionAsync_SupportsMariaThemeWithSameAtomicContract()
    {
        var plan = new CatalogMutationConnectionPlan();
        plan.NonQuerySteps.Enqueue(_ => Task.FromResult(1));
        plan.NonQuerySteps.Enqueue(_ => Task.FromResult(1));
        var factory = new CatalogMutationConnectionFactory(plan);
        var executor = CreateExecutor(factory);
        var service = new LegacyThemeCatalogPersistenceService(
            new CatalogNoQueryExecutor(),
            executor);

        await service.CreateAsync(new ThemeCatalogDefinition(
            ThemeMarket.Nxt,
            "00000007",
            "반도체",
            [new ThemeCatalogItem(0, "X005930", "삼성전자")]));

        Assert.Equal(DataSourceKind.MariaDb, factory.LastSource);
        Assert.Equal(IsolationLevel.Serializable, plan.IsolationLevel);
        Assert.Equal(1, plan.CommitCount);
        Assert.Equal(2, plan.Commands.Count);
        Assert.All(plan.Commands, command => Assert.Contains('@', command.Sql));
    }

    [Theory]
    [InlineData("123", ThemeMarket.Krx)]
    [InlineData("0123", ThemeMarket.Krx)]
    [InlineData("001234", ThemeMarket.Krx)]
    [InlineData("00001234", ThemeMarket.Krx)]
    [InlineData("123", ThemeMarket.Nxt)]
    [InlineData("0123", ThemeMarket.Nxt)]
    [InlineData("001234", ThemeMarket.Nxt)]
    [InlineData("00001234", ThemeMarket.Nxt)]
    public async Task ExecuteTransactionAsync_ReplaceAndDeleteAcceptLegacyThemeIdentityLengths(
        string code,
        ThemeMarket market)
    {
        var source = market == ThemeMarket.Krx ? DataSourceKind.Oracle : DataSourceKind.MariaDb;
        var session = market == ThemeMarket.Krx
            ? ThemeSession.NotApplicable
            : ThemeSession.PreMarket;
        var identity = new ThemeSelectionIdentity(market, session, code, "기존 테마");

        var replacePlan = new CatalogMutationConnectionPlan();
        replacePlan.NonQuerySteps.Enqueue(_ => Task.FromResult(1));
        replacePlan.NonQuerySteps.Enqueue(_ => Task.FromResult(0));
        var replaceFactory = new CatalogMutationConnectionFactory(replacePlan);
        var replaceService = new LegacyThemeCatalogPersistenceService(
            new CatalogNoQueryExecutor(),
            CreateExecutor(replaceFactory));

        await replaceService.ReplaceAsync(identity, "변경 테마", []);

        Assert.Equal(source, replaceFactory.LastSource);
        Assert.Equal(1, replacePlan.CommitCount);

        var deletePlan = new CatalogMutationConnectionPlan();
        deletePlan.NonQuerySteps.Enqueue(_ => Task.FromResult(0));
        deletePlan.NonQuerySteps.Enqueue(_ => Task.FromResult(1));
        var deleteFactory = new CatalogMutationConnectionFactory(deletePlan);
        var deleteService = new LegacyThemeCatalogPersistenceService(
            new CatalogNoQueryExecutor(),
            CreateExecutor(deleteFactory));

        await deleteService.DeleteAsync(identity);

        Assert.Equal(source, deleteFactory.LastSource);
        Assert.Equal(1, deletePlan.CommitCount);
    }

    [Fact]
    public async Task ExecuteTransactionAsync_AffectedRowConflictRollsBackBeforeNextCommand()
    {
        var plan = new CatalogMutationConnectionPlan();
        plan.NonQuerySteps.Enqueue(_ => Task.FromResult(0));
        plan.NonQuerySteps.Enqueue(_ => Task.FromResult(1));
        var factory = new CatalogMutationConnectionFactory(plan);
        var executor = CreateExecutor(factory);
        var service = new LegacyThemeCatalogPersistenceService(
            new CatalogNoQueryExecutor(),
            executor);

        var exception = await Assert.ThrowsAsync<OperatorCatalogConflictException>(() =>
            service.CreateAsync(new ThemeCatalogDefinition(
                ThemeMarket.Krx,
                "00000001",
                "중복 테마",
                [new ThemeCatalogItem(0, "P005930", "삼성전자")])));

        Assert.False(exception.OutcomeUnknown);
        Assert.Equal(OperatorCatalogDbCommandKind.InsertTheme, exception.CommandKind);
        Assert.Single(plan.Commands);
        Assert.Single(plan.NonQuerySteps);
        Assert.Equal(1, plan.RollbackCount);
        Assert.Equal(0, plan.CommitCount);
    }

    [Fact]
    public async Task ExecuteTransactionAsync_EmptyChildDeleteThenIdentityParentDeleteCommits()
    {
        var plan = new CatalogMutationConnectionPlan();
        plan.NonQuerySteps.Enqueue(_ => Task.FromResult(0));
        plan.NonQuerySteps.Enqueue(_ => Task.FromResult(1));
        var executor = CreateExecutor(new CatalogMutationConnectionFactory(plan));
        var service = new LegacyExpertCatalogPersistenceService(
            new CatalogNoQueryExecutor(),
            executor);

        await service.DeleteAsync(new ExpertSelectionIdentity("0001", "전문가"));

        Assert.Equal(2, plan.Commands.Count);
        Assert.Equal(1, plan.CommitCount);
        Assert.Equal(0, plan.RollbackCount);
    }

    [Theory]
    [InlineData(true)]
    [InlineData(false)]
    public async Task ExecuteTransactionAsync_TimeoutOrCancellationAfterBeginIsOutcomeUnknown(
        bool cancellation)
    {
        var plan = new CatalogMutationConnectionPlan();
        plan.NonQuerySteps.Enqueue(_ => cancellation
            ? Task.FromException<int>(new OperationCanceledException("cancelled in provider"))
            : Task.FromException<int>(new TimeoutException("provider timeout")));
        var factory = new CatalogMutationConnectionFactory(plan);
        var executor = CreateExecutor(factory);
        var service = new LegacyExpertCatalogPersistenceService(
            new CatalogNoQueryExecutor(),
            executor);

        var exception = await Assert.ThrowsAsync<OperatorCatalogMutationException>(() =>
            service.CreateAsync(new ExpertCatalogDefinition(
                new ExpertSelectionIdentity("0001", "전문가"),
                [])));

        Assert.True(exception.OutcomeUnknown);
        Assert.Contains("do not retry automatically", exception.Message, StringComparison.Ordinal);
        Assert.Single(plan.Commands);
        Assert.Empty(plan.NonQuerySteps);
        Assert.Equal(1, plan.RollbackCount);
        Assert.Equal(0, plan.CommitCount);
    }

    [Fact]
    public async Task ExecuteTransactionAsync_OrdinaryFailureWithKnownRollbackIsNotUnknown()
    {
        var plan = new CatalogMutationConnectionPlan();
        plan.NonQuerySteps.Enqueue(_ =>
            Task.FromException<int>(new TestDatabaseException("constraint")));
        var executor = CreateExecutor(new CatalogMutationConnectionFactory(plan));
        var service = new LegacyExpertCatalogPersistenceService(
            new CatalogNoQueryExecutor(),
            executor);

        var exception = await Assert.ThrowsAsync<OperatorCatalogMutationException>(() =>
            service.CreateAsync(new ExpertCatalogDefinition(
                new ExpertSelectionIdentity("0001", "전문가"),
                [])));

        Assert.False(exception.OutcomeUnknown);
        Assert.Equal(1, plan.RollbackCount);
        Assert.Equal(0, plan.CommitCount);
    }

    [Fact]
    public async Task ExecuteTransactionAsync_RollbackFailureMakesOutcomeUnknown()
    {
        var plan = new CatalogMutationConnectionPlan
        {
            RollbackAsync = _ => Task.FromException(new IOException("rollback disconnected"))
        };
        plan.NonQuerySteps.Enqueue(_ =>
            Task.FromException<int>(new TestDatabaseException("constraint")));
        var executor = CreateExecutor(new CatalogMutationConnectionFactory(plan));
        var service = new LegacyExpertCatalogPersistenceService(
            new CatalogNoQueryExecutor(),
            executor);

        var exception = await Assert.ThrowsAsync<OperatorCatalogMutationException>(() =>
            service.CreateAsync(new ExpertCatalogDefinition(
                new ExpertSelectionIdentity("0001", "전문가"),
                [])));

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

    [Fact]
    public async Task ExecuteTransactionAsync_CommitFailureIsUnknownAndDoesNotRollback()
    {
        var plan = new CatalogMutationConnectionPlan
        {
            CommitAsync = _ => Task.FromException(new TimeoutException("commit response lost"))
        };
        plan.NonQuerySteps.Enqueue(_ => Task.FromResult(1));
        var executor = CreateExecutor(new CatalogMutationConnectionFactory(plan));
        var service = new LegacyExpertCatalogPersistenceService(
            new CatalogNoQueryExecutor(),
            executor);

        var exception = await Assert.ThrowsAsync<OperatorCatalogMutationException>(() =>
            service.CreateAsync(new ExpertCatalogDefinition(
                new ExpertSelectionIdentity("0001", "전문가"),
                [])));

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

    [Fact]
    public async Task ExecuteTransactionAsync_PreCancelledTokenDoesNotOpenConnection()
    {
        var plan = new CatalogMutationConnectionPlan();
        var factory = new CatalogMutationConnectionFactory(plan);
        var executor = CreateExecutor(factory);
        var service = new LegacyExpertCatalogPersistenceService(
            new CatalogNoQueryExecutor(),
            executor);
        using var cancellation = new CancellationTokenSource();
        cancellation.Cancel();

        await Assert.ThrowsAnyAsync<OperationCanceledException>(() =>
            service.CreateAsync(
                new ExpertCatalogDefinition(
                    new ExpertSelectionIdentity("0001", "전문가"),
                    []),
                cancellation.Token));

        Assert.Equal(0, factory.CreateCount);
        Assert.Equal(0, plan.BeginCount);
    }

    [Fact]
    public async Task ExecuteTransactionAsync_DeniedAuthorizationCreatesNoConnectionOrTransaction()
    {
        var plan = new CatalogMutationConnectionPlan();
        var factory = new CatalogMutationConnectionFactory(plan);
        var authorization = new DenyAllDatabaseMutationAuthorization();
        var service = new LegacyExpertCatalogPersistenceService(
            new CatalogNoQueryExecutor(),
            CreateExecutor(factory, authorization));

        var exception = await Assert.ThrowsAsync<OperatorCatalogMutationException>(() =>
            service.CreateAsync(new ExpertCatalogDefinition(
                new ExpertSelectionIdentity("0001", "Expert"),
                [])));

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

    private static OperatorCatalogMutationExecutor CreateExecutor(
        IDatabaseConnectionFactory factory,
        IDatabaseMutationAuthorization? mutationAuthorization = null) =>
        new(
            factory,
            new DatabaseResilienceOptions
            {
                OperationTimeoutSeconds = 30,
                MaximumRetryCount = 5,
                InitialRetryDelayMilliseconds = 1
            },
            new StubErrorDetector(transient: true),
            static (_, _) => { },
            mutationAuthorization ?? DatabaseMutationAuthorization.AllowAll);

    private static ObservedCatalogMutationParameter Parameter(
        ObservedCatalogMutationCommand command,
        string name) =>
        Assert.Single(command.Parameters, parameter =>
            string.Equals(parameter.Name, name, StringComparison.Ordinal));
}

internal sealed class CatalogNoQueryExecutor : IDataQueryExecutor
{
    public DataTable Execute(DataSourceKind source, string tableName, string query) =>
        throw new InvalidOperationException("No query expected.");

    public Task<DataTable> ExecuteAsync(
        DataSourceKind source,
        string tableName,
        string query,
        CancellationToken cancellationToken = default) =>
        throw new InvalidOperationException("No query expected.");

    public Task<DataTable> ExecuteAsync(
        DataSourceKind source,
        string tableName,
        DataQuerySpec query,
        CancellationToken cancellationToken = default) =>
        throw new InvalidOperationException("No query expected.");
}

internal sealed class CatalogMutationConnectionPlan
{
    internal Queue<Func<CancellationToken, Task<int>>> NonQuerySteps { get; } = new();

    internal Func<CancellationToken, Task> OpenAsync { get; init; } =
        _ => Task.CompletedTask;

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

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

    internal List<ObservedCatalogMutationCommand> Commands { get; } = [];

    internal IsolationLevel? IsolationLevel { get; set; }

    internal int BeginCount { get; set; }

    internal int CommitCount { get; set; }

    internal int RollbackCount { get; set; }

    internal int ConnectionDisposeCount { get; set; }

    internal int TransactionDisposeCount { get; set; }
}

internal sealed record ObservedCatalogMutationCommand(
    string Sql,
    int CommandTimeoutSeconds,
    bool HadTransaction,
    IReadOnlyList<ObservedCatalogMutationParameter> Parameters);

internal sealed record ObservedCatalogMutationParameter(
    string Name,
    object? Value,
    DbType DbType,
    ParameterDirection Direction);

internal sealed class CatalogMutationConnectionFactory(CatalogMutationConnectionPlan plan)
    : IDatabaseConnectionFactory
{
    internal int CreateCount { get; private set; }

    internal DataSourceKind? LastSource { get; private set; }

    public DbConnection Create(DataSourceKind source)
    {
        CreateCount++;
        LastSource = source;
        return new CatalogMutationDbConnection(plan);
    }

    public int GetCommandTimeoutSeconds(DataSourceKind source) => 7;
}

internal sealed class CatalogMutationDbConnection(CatalogMutationConnectionPlan plan)
    : DbConnection
{
    private ConnectionState _state = ConnectionState.Closed;

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

    public override string Database => "CatalogFake";

    public override string DataSource => "CatalogFake";

    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 forbidden.");

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

    protected override DbTransaction BeginDbTransaction(IsolationLevel isolationLevel)
    {
        plan.BeginCount++;
        plan.IsolationLevel = isolationLevel;
        return new CatalogMutationDbTransaction(this, plan, isolationLevel);
    }

    protected override DbCommand CreateDbCommand() =>
        new CatalogMutationDbCommand(this, plan);

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

        base.Dispose(disposing);
    }
}

internal sealed class CatalogMutationDbTransaction(
    DbConnection connection,
    CatalogMutationConnectionPlan plan,
    IsolationLevel isolationLevel) : DbTransaction
{
    public override IsolationLevel IsolationLevel => isolationLevel;

    protected override DbConnection DbConnection => connection;

    public override void Commit() => throw new InvalidOperationException("Sync commit forbidden.");

    public override void Rollback() => throw new InvalidOperationException("Sync rollback forbidden.");

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

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

    protected override void Dispose(bool disposing)
    {
        if (disposing)
        {
            plan.TransactionDisposeCount++;
        }

        base.Dispose(disposing);
    }
}

internal sealed class CatalogMutationDbCommand(
    DbConnection connection,
    CatalogMutationConnectionPlan plan) : DbCommand
{
    private readonly FakeDbParameterCollection _parameters = new();

    [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() =>
        throw new InvalidOperationException("Synchronous execution forbidden.");

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

    public override void Prepare()
    {
    }

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

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

    public override Task<int> ExecuteNonQueryAsync(CancellationToken cancellationToken)
    {
        plan.Commands.Add(new ObservedCatalogMutationCommand(
            CommandText,
            CommandTimeout,
            DbTransaction is not null,
            _parameters.Cast<DbParameter>()
                .Select(parameter => new ObservedCatalogMutationParameter(
                    parameter.ParameterName,
                    parameter.Value,
                    parameter.DbType,
                    parameter.Direction))
                .ToArray()));

        if (!plan.NonQuerySteps.TryDequeue(out var step))
        {
            throw new InvalidOperationException("No non-query plan remains.");
        }

        return step(cancellationToken);
    }
}
