using System.Data;
using MMoneyCoderSharp.Data;

namespace MBN_STOCK_WEBVIEW.Core.Tests;

public sealed class LegacyExpertCatalogPersistenceServiceTests
{
    [Fact]
    public async Task SuggestNextCodeAsync_UsesOriginalFourDigitOracleSequenceReadOnly()
    {
        var query = new CatalogRecordingQueryExecutor(call =>
        {
            Assert.Equal(DataSourceKind.Oracle, call.Source);
            Assert.Equal(LegacyExpertCatalogPersistenceService.NextCodeQueryName, call.TableName);
            Assert.Contains("TO_NUMBER(TRIM(EP_CODE))", call.Spec.Sql, StringComparison.Ordinal);
            Assert.Empty(call.Spec.Parameters);
            return OperatorCatalogTestTables.SingleString("EXPERT_CODE", "0042");
        });
        var service = new LegacyExpertCatalogPersistenceService(query);

        Assert.Equal("0042", await service.SuggestNextCodeAsync());
        Assert.False(service.CanMutate);
        Assert.Single(query.Calls);
    }

    [Fact]
    public async Task SuggestNextCodeAsync_RejectsExhaustedCodeRange()
    {
        var service = new LegacyExpertCatalogPersistenceService(
            new CatalogRecordingQueryExecutor(_ =>
                OperatorCatalogTestTables.SingleString("EXPERT_CODE", "EXHAUSTED")));

        await Assert.ThrowsAsync<ExpertSelectionDataException>(() =>
            service.SuggestNextCodeAsync());
    }

    [Fact]
    public async Task CreateAsync_BindsExpertThenRecommendationsInOneOracleTransaction()
    {
        var mutation = new CatalogRecordingMutationExecutor();
        var service = new LegacyExpertCatalogPersistenceService(UnusedQuery(), mutation);
        using var cancellation = new CancellationTokenSource();
        var definition = new ExpertCatalogDefinition(
            new ExpertSelectionIdentity("0042", "홍'길동"),
            [
                new ExpertCatalogRecommendation(0, "005930", "삼성전자", 72000m),
                new ExpertCatalogRecommendation(1, "035720", "카카오", 45000m)
            ]);

        var receipt = await service.CreateAsync(definition, cancellation.Token);

        Assert.True(service.CanMutate);
        Assert.Equal(OperatorCatalogMutationKind.CreateExpert, receipt.Kind);
        var call = Assert.Single(mutation.Calls);
        Assert.Equal(cancellation.Token, call.Token);
        var transaction = call.Transaction;
        Assert.Equal(DataSourceKind.Oracle, transaction.Source);
        Assert.Equal("0042", transaction.IdentityCode);
        Assert.Equal(
            [
                OperatorCatalogDbCommandKind.InsertExpert,
                OperatorCatalogDbCommandKind.InsertRecommendation,
                OperatorCatalogDbCommandKind.InsertRecommendation
            ],
            transaction.Commands.Select(static command => command.Kind));
        Assert.All(transaction.Commands, command =>
            Assert.DoesNotContain("홍'길동", command.Sql, StringComparison.Ordinal));

        var parent = transaction.Commands[0];
        Assert.Contains("NOT EXISTS", parent.Sql, StringComparison.Ordinal);
        Assert.Equal("홍'길동", Parameter(parent, "expert_name").Value);
        Assert.DoesNotContain(parent.Parameters, parameter => parameter.Name == "conflict_name");
        var first = transaction.Commands[1];
        Assert.Equal("005930", Parameter(first, "stock_code").Value);
        Assert.Equal(72000m, Parameter(first, "buy_amount").Value);
        Assert.Equal(DbType.Decimal, Parameter(first, "buy_amount").DbType);
        Assert.Equal(0, Parameter(first, "play_index").Value);
    }

    [Fact]
    public async Task ReplaceAsync_UsesExpectedCodeAndNameBeforeChildReplacement()
    {
        var mutation = new CatalogRecordingMutationExecutor();
        var service = new LegacyExpertCatalogPersistenceService(UnusedQuery(), mutation);

        await service.ReplaceAsync(
            new ExpertSelectionIdentity("0010", "기존 전문가"),
            "새 전문가",
            [new ExpertCatalogRecommendation(0, "005930", "삼성전자", 70000m)]);

        var transaction = Assert.Single(mutation.Calls).Transaction;
        Assert.Equal(OperatorCatalogMutationKind.ReplaceExpert, transaction.Kind);
        Assert.Equal(
            [
                OperatorCatalogDbCommandKind.UpdateExpert,
                OperatorCatalogDbCommandKind.DeleteRecommendations,
                OperatorCatalogDbCommandKind.InsertRecommendation
            ],
            transaction.Commands.Select(static command => command.Kind));
        var update = transaction.Commands[0];
        Assert.Equal("기존 전문가", Parameter(update, "expected_name").Value);
        Assert.Equal("새 전문가", Parameter(update, "new_name").Value);
        Assert.DoesNotContain(update.Parameters, parameter => parameter.Name == "duplicate_name");
        Assert.DoesNotContain("NOT EXISTS", update.Sql, StringComparison.Ordinal);
        Assert.Equal(
            OperatorCatalogAffectedRowsRule.ExactlyOne,
            update.AffectedRowsRule);
        Assert.Equal(
            OperatorCatalogAffectedRowsRule.AnyNonNegative,
            transaction.Commands[1].AffectedRowsRule);
    }

    [Fact]
    public async Task DeleteAsync_DeletesRecommendationsBeforeIdentityBoundExpert()
    {
        var mutation = new CatalogRecordingMutationExecutor();
        var service = new LegacyExpertCatalogPersistenceService(UnusedQuery(), mutation);

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

        var transaction = Assert.Single(mutation.Calls).Transaction;
        Assert.Equal(
            [
                OperatorCatalogDbCommandKind.DeleteRecommendations,
                OperatorCatalogDbCommandKind.DeleteExpert
            ],
            transaction.Commands.Select(static command => command.Kind));
        Assert.Equal("전문가", Parameter(transaction.Commands[1], "expected_name").Value);
        Assert.Equal(
            OperatorCatalogAffectedRowsRule.ExactlyOne,
            transaction.Commands[1].AffectedRowsRule);
    }

    [Fact]
    public async Task Mutations_AllowRepeatedRecommendationsButRejectOrderAndUnsafeAmountsBeforeExecutor()
    {
        var mutation = new CatalogRecordingMutationExecutor();
        var service = new LegacyExpertCatalogPersistenceService(UnusedQuery(), mutation);
        var identity = new ExpertSelectionIdentity("0001", "전문가");

        await service.CreateAsync(
            new ExpertCatalogDefinition(
                identity,
                [
                    new ExpertCatalogRecommendation(0, "005930", "삼성전자", 1m),
                    new ExpertCatalogRecommendation(1, "005930", "삼성전자", 2m)
                ]));
        Assert.Equal(3, Assert.Single(mutation.Calls).Transaction.Commands.Count);
        mutation.Calls.Clear();
        await Assert.ThrowsAsync<ArgumentException>(() => service.CreateAsync(
            new ExpertCatalogDefinition(
                identity,
                [new ExpertCatalogRecommendation(1, "005930", "삼성전자", 1m)])));
        await Assert.ThrowsAsync<ArgumentOutOfRangeException>(() => service.CreateAsync(
            new ExpertCatalogDefinition(
                identity,
                [new ExpertCatalogRecommendation(0, "005930", "삼성전자", 1.5m)])));
        await Assert.ThrowsAsync<ArgumentOutOfRangeException>(() => service.CreateAsync(
            new ExpertCatalogDefinition(
                identity,
                [new ExpertCatalogRecommendation(0, "005930", "삼성전자", 0m)])));

        Assert.Empty(mutation.Calls);
    }

    [Fact]
    public async Task Mutations_FailClosedForReadOnlyCancellationAndKnownConflict()
    {
        var definition = new ExpertCatalogDefinition(
            new ExpertSelectionIdentity("0001", "전문가"),
            []);
        var readOnly = new LegacyExpertCatalogPersistenceService(UnusedQuery());
        await Assert.ThrowsAsync<InvalidOperationException>(() => readOnly.CreateAsync(definition));

        var mutation = new CatalogRecordingMutationExecutor();
        var service = new LegacyExpertCatalogPersistenceService(UnusedQuery(), mutation);
        using var cancellation = new CancellationTokenSource();
        cancellation.Cancel();
        await Assert.ThrowsAnyAsync<OperationCanceledException>(() =>
            service.CreateAsync(definition, cancellation.Token));
        Assert.Empty(mutation.Calls);

        mutation.Exception = new OperatorCatalogConflictException(
            OperatorCatalogMutationKind.CreateExpert,
            OperatorCatalogDbCommandKind.InsertExpert);
        var conflict = await Assert.ThrowsAsync<OperatorCatalogConflictException>(() =>
            service.CreateAsync(definition));
        Assert.False(conflict.OutcomeUnknown);
        Assert.Equal(OperatorCatalogDbCommandKind.InsertExpert, conflict.CommandKind);
    }

    [Fact]
    public async Task CreateAsync_TreatsUnclassifiedOrAmbiguousResultAsOutcomeUnknown()
    {
        var definition = new ExpertCatalogDefinition(
            new ExpertSelectionIdentity("0001", "전문가"),
            []);
        var mutation = new CatalogRecordingMutationExecutor
        {
            Exception = new TimeoutException("provider detail")
        };
        var service = new LegacyExpertCatalogPersistenceService(UnusedQuery(), mutation);

        var unclassified = await Assert.ThrowsAsync<OperatorCatalogMutationException>(() =>
            service.CreateAsync(definition));
        Assert.True(unclassified.OutcomeUnknown);
        Assert.Contains("do not retry automatically", unclassified.Message, StringComparison.Ordinal);

        var ambiguousMutation = new CatalogRecordingMutationExecutor(transaction =>
            new OperatorCatalogDbTransactionResult(
                transaction.OperationId,
                [0],
                DateTimeOffset.UtcNow));
        var ambiguousService = new LegacyExpertCatalogPersistenceService(
            UnusedQuery(),
            ambiguousMutation);
        var ambiguous = await Assert.ThrowsAsync<OperatorCatalogMutationException>(() =>
            ambiguousService.CreateAsync(definition));
        Assert.True(ambiguous.OutcomeUnknown);
    }

    private static CatalogRecordingQueryExecutor UnusedQuery() =>
        new(_ => throw new InvalidOperationException("No read query was expected."));

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