using System.Data;
using MMoneyCoderSharp.Data;

namespace MBN_STOCK_WEBVIEW.Core.Tests;

public sealed class LegacyExpertSelectionServiceTests
{
    [Fact]
    public async Task SearchAsync_UsesBoundOracleExpertListAndReturnsTypedDeterministicRows()
    {
        var executor = new RecordingExecutor(_ => SearchTable(
            ("Zulu", "0002"),
            ("Alpha", "0001")));
        var service = new LegacyExpertSelectionService(executor);
        using var cancellation = new CancellationTokenSource();

        var result = await service.SearchAsync(" a!_% ", 25, cancellation.Token);

        Assert.Equal("a!_%", result.Query);
        Assert.False(result.IsTruncated);
        Assert.Collection(
            result.Items,
            item =>
            {
                Assert.Equal(new ExpertSelectionIdentity("0001", "Alpha"), item.Identity);
                Assert.Equal(DataSourceKind.Oracle, item.Source);
            },
            item => Assert.Equal(new ExpertSelectionIdentity("0002", "Zulu"), item.Identity));

        var call = Assert.Single(executor.Calls);
        Assert.Equal(DataSourceKind.Oracle, call.Source);
        Assert.Equal("EXPERT_SEARCH", call.QueryName);
        Assert.Equal(cancellation.Token, call.CancellationToken);
        call.Spec.ValidateFor(DataSourceKind.Oracle);
        Assert.Contains("FROM EXPERT_LIST", call.Spec.Sql, StringComparison.Ordinal);
        Assert.Contains("EP_NAME EXPERT_NAME", call.Spec.Sql, StringComparison.Ordinal);
        Assert.Contains("EP_CODE EXPERT_CODE", call.Spec.Sql, StringComparison.Ordinal);
        Assert.Contains("ESCAPE '!'", call.Spec.Sql, StringComparison.Ordinal);
        Assert.Contains("ROWNUM <= :row_limit", call.Spec.Sql, StringComparison.Ordinal);
        Assert.DoesNotContain("a!_%", call.Spec.Sql, StringComparison.OrdinalIgnoreCase);
        Assert.Collection(
            call.Spec.Parameters,
            parameter =>
            {
                Assert.Equal("search_term", parameter.Name);
                Assert.Equal("A!!!_!%", parameter.Value);
                Assert.Equal(DbType.String, parameter.DbType);
            },
            parameter =>
            {
                Assert.Equal("row_limit", parameter.Name);
                Assert.Equal(26, parameter.Value);
                Assert.Equal(DbType.Int32, parameter.DbType);
            });
        Assert.Equal(0, executor.StringSqlCallCount);
    }

    [Fact]
    public async Task SearchAsync_AllowsBlankInitialLoadAndReportsGlobalBound()
    {
        var executor = new RecordingExecutor(_ => SearchTable(
            ("Alpha", "0001"),
            ("Beta", "0002")));
        var service = new LegacyExpertSelectionService(executor);

        var result = await service.SearchAsync("   ", maximumResults: 1);

        Assert.Equal(string.Empty, result.Query);
        Assert.True(result.IsTruncated);
        Assert.Single(result.Items);
        Assert.Equal(string.Empty, executor.Calls[0].Spec.Parameters[0].Value);
        Assert.Equal(2, executor.Calls[0].Spec.Parameters[1].Value);
    }

    [Fact]
    public async Task SearchAsync_MatchesHangulInitialConsonantsAfterBoundedCandidateRead()
    {
        var executor = new RecordingExecutor(_ => SearchTable(
            ("박전문", "0003"),
            ("김투자", "0002"),
            ("김전문", "0001"),
            ("강지민", "0004")));
        var service = new LegacyExpertSelectionService(executor);

        var result = await service.SearchAsync(" ㄱㅈㅁ ", maximumResults: 10);

        Assert.Equal("ㄱㅈㅁ", result.Query);
        Assert.False(result.IsTruncated);
        Assert.Equal(
            ["강지민", "김전문"],
            result.Items.Select(item => item.Identity.ExpertName));
        var call = Assert.Single(executor.Calls);
        Assert.Equal(string.Empty, call.Spec.Parameters[0].Value);
        Assert.Equal(LegacyExpertSelectionService.MaximumResults + 1,
            call.Spec.Parameters[1].Value);
        Assert.DoesNotContain("ㄱㅈㅁ", call.Spec.Sql, StringComparison.Ordinal);
    }

    [Fact]
    public async Task SearchAsync_AppliesRequestedLimitAfterInitialConsonantFiltering()
    {
        var executor = new RecordingExecutor(_ => SearchTable(
            ("김전문", "0001"),
            ("강지민", "0002"),
            ("고정민", "0003")));

        var result = await new LegacyExpertSelectionService(executor)
            .SearchAsync("ᄀᄌᄆ", maximumResults: 1);

        Assert.Single(result.Items);
        Assert.Equal("강지민", result.Items[0].Identity.ExpertName);
        Assert.True(result.IsTruncated);
    }

    [Theory]
    [InlineData(0)]
    [InlineData(-1)]
    [InlineData(501)]
    public async Task SearchAsync_RejectsInvalidLimitBeforeDatabaseAccess(int limit)
    {
        var executor = new RecordingExecutor(_ => SearchTable());
        var service = new LegacyExpertSelectionService(executor);

        await Assert.ThrowsAsync<ArgumentOutOfRangeException>(
            () => service.SearchAsync("", limit));

        Assert.Empty(executor.Calls);
    }

    [Theory]
    [InlineData("bad\u0000value")]
    [InlineData("bad\u200Bvalue")]
    [InlineData("bad\uD800value")]
    public async Task SearchAsync_RejectsUnsafeQueryBeforeDatabaseAccess(string query)
    {
        var executor = new RecordingExecutor(_ => SearchTable());
        var service = new LegacyExpertSelectionService(executor);

        await Assert.ThrowsAsync<ArgumentException>(() => service.SearchAsync(query));

        Assert.Empty(executor.Calls);
    }

    [Fact]
    public async Task SearchAsync_RejectsNullAndOverlongQueryBeforeDatabaseAccess()
    {
        var executor = new RecordingExecutor(_ => SearchTable());
        var service = new LegacyExpertSelectionService(executor);

        await Assert.ThrowsAsync<ArgumentNullException>(() => service.SearchAsync(null!));
        await Assert.ThrowsAsync<ArgumentException>(() => service.SearchAsync(
            new string('A', LegacyExpertSelectionService.MaximumQueryLength + 1)));

        Assert.Empty(executor.Calls);
    }

    [Fact]
    public async Task SearchAsync_RejectsExactSchemaAndRowBoundViolations()
    {
        var wrongSchema = SearchTable();
        wrongSchema.Columns[0].ColumnName = "expert_name";
        await Assert.ThrowsAsync<ExpertSelectionDataException>(() =>
            new LegacyExpertSelectionService(new RecordingExecutor(_ => wrongSchema))
                .SearchAsync());

        var overflow = SearchTable(
            ("A", "0001"),
            ("B", "0002"),
            ("C", "0003"));
        await Assert.ThrowsAsync<ExpertSelectionDataException>(() =>
            new LegacyExpertSelectionService(new RecordingExecutor(_ => overflow))
                .SearchAsync(maximumResults: 1));
    }

    [Theory]
    [InlineData("Analyst", "0001", "Other", "0001")]
    public async Task SearchAsync_RejectsDuplicateExpertCode(
        string name1,
        string code1,
        string name2,
        string code2)
    {
        var executor = new RecordingExecutor(_ => SearchTable(
            (name1, code1),
            (name2, code2)));
        var service = new LegacyExpertSelectionService(executor);

        var exception = await Assert.ThrowsAsync<ExpertSelectionDataException>(
            () => service.SearchAsync());

        Assert.Contains("duplicate expert code", exception.Message, StringComparison.Ordinal);
    }

    [Fact]
    public async Task SearchAsync_AllowsDuplicateExpertNamesWithDistinctCodes()
    {
        var executor = new RecordingExecutor(_ => SearchTable(
            ("Analyst", "0001"),
            ("Analyst", "0002")));

        var result = await new LegacyExpertSelectionService(executor).SearchAsync();

        Assert.Equal(2, result.Items.Count);
    }

    [Theory]
    [InlineData(" Analyst", "0001")]
    [InlineData("Analyst", "001")]
    [InlineData("Analyst", "00A1")]
    [InlineData("Bad\nName", "0001")]
    public async Task SearchAsync_RejectsNonCanonicalExpertIdentity(string name, string code)
    {
        var executor = new RecordingExecutor(_ => SearchTable((name, code)));

        await Assert.ThrowsAsync<ExpertSelectionDataException>(() =>
            new LegacyExpertSelectionService(executor).SearchAsync());
    }

    [Fact]
    public async Task SearchAsync_PropagatesPreCanceledTokenWithoutDatabaseAccess()
    {
        var executor = new RecordingExecutor(_ => SearchTable());
        var service = new LegacyExpertSelectionService(executor);
        using var cancellation = new CancellationTokenSource();
        cancellation.Cancel();

        await Assert.ThrowsAnyAsync<OperationCanceledException>(
            () => service.SearchAsync(cancellationToken: cancellation.Token));

        Assert.Empty(executor.Calls);
    }

    [Fact]
    public async Task PreviewAsync_BindsCodeAndNameAndMapsOriginalRecommendationFields()
    {
        var identity = Identity();
        var executor = new RecordingExecutor(_ => PreviewTable(
            ("Analyst A", "0001", "005930", "Samsung", "70000", "0"),
            ("Analyst A", "0001", "035720", "Kakao", "42000", "2")));
        var service = new LegacyExpertSelectionService(executor);
        using var cancellation = new CancellationTokenSource();

        var result = await service.PreviewAsync(identity, 12, cancellation.Token);

        Assert.Equal(identity, result.Identity);
        Assert.False(result.IsTruncated);
        Assert.Equal(
            [
                new ExpertRecommendationPreview(0, "005930", "Samsung", 70000m),
                new ExpertRecommendationPreview(2, "035720", "Kakao", 42000m)
            ],
            result.Items);

        var call = Assert.Single(executor.Calls);
        Assert.Equal(DataSourceKind.Oracle, call.Source);
        Assert.Equal("EXPERT_PREVIEW", call.QueryName);
        Assert.Equal(cancellation.Token, call.CancellationToken);
        Assert.Contains("FROM EXPERT_LIST e", call.Spec.Sql, StringComparison.Ordinal);
        Assert.Contains("LEFT JOIN RECOMMEND_LIST r", call.Spec.Sql, StringComparison.Ordinal);
        Assert.Contains("ORDER BY r.PLAYINDEX", call.Spec.Sql, StringComparison.Ordinal);
        Assert.Contains("ROWNUM <= :row_limit", call.Spec.Sql, StringComparison.Ordinal);
        Assert.Collection(
            call.Spec.Parameters,
            parameter =>
            {
                Assert.Equal("expert_code", parameter.Name);
                Assert.Equal("0001", parameter.Value);
            },
            parameter =>
            {
                Assert.Equal("expert_name", parameter.Name);
                Assert.Equal("Analyst A", parameter.Value);
            },
            parameter =>
            {
                Assert.Equal("row_limit", parameter.Name);
                Assert.Equal(13, parameter.Value);
            });
        Assert.Equal(0, executor.StringSqlCallCount);
    }

    [Fact]
    public async Task PreviewAsync_MapsSingleNullRecommendationSentinelToEmptyExpert()
    {
        var executor = new RecordingExecutor(_ => PreviewTable(
            ("Analyst A", "0001", null, null, null, null)));

        var result = await new LegacyExpertSelectionService(executor).PreviewAsync(Identity());

        Assert.Empty(result.Items);
        Assert.False(result.IsTruncated);
    }

    [Fact]
    public async Task PreviewAsync_PreservesNullBuyAmountAsEditableIncompleteValue()
    {
        var executor = new RecordingExecutor(_ => PreviewTable(
            ("Analyst A", "0001", "005930", "Samsung", null, "0"),
            ("Analyst A", "0001", "035720", "Kakao", "42000", "1")));

        var result = await new LegacyExpertSelectionService(executor).PreviewAsync(Identity());

        Assert.Equal(2, result.Items.Count);
        Assert.Null(result.Items[0].BuyAmount);
        Assert.Equal(42_000m, result.Items[1].BuyAmount);
    }

    [Fact]
    public async Task PreviewAsync_AppliesFiveByTwentyMaximumAndReportsTruncation()
    {
        var executor = new RecordingExecutor(_ => PreviewTable(
            ("Analyst A", "0001", "000001", "Alpha", "100", "0"),
            ("Analyst A", "0001", "000002", "Beta", "200", "1")));
        var service = new LegacyExpertSelectionService(executor);

        var result = await service.PreviewAsync(Identity(), maximumItems: 1);

        Assert.True(result.IsTruncated);
        Assert.Single(result.Items);
        Assert.Equal(2, executor.Calls[0].Spec.Parameters[2].Value);
        Assert.Equal(100, LegacyExpertSelectionService.MaximumPreviewItems);
    }

    public static TheoryData<ExpertSelectionIdentity> UnsafeIdentities => new()
    {
        new("001", "Analyst A"),
        new("00A1", "Analyst A"),
        new("0001", " Analyst A"),
        new("0001", "Bad\u0000Name")
    };

    [Theory]
    [MemberData(nameof(UnsafeIdentities))]
    public async Task PreviewAsync_RejectsUnsafeIdentityBeforeDatabaseAccess(
        ExpertSelectionIdentity identity)
    {
        var executor = new RecordingExecutor(_ => PreviewTable());

        await Assert.ThrowsAsync<ArgumentException>(() =>
            new LegacyExpertSelectionService(executor).PreviewAsync(identity));

        Assert.Empty(executor.Calls);
    }

    [Theory]
    [InlineData(0)]
    [InlineData(-1)]
    [InlineData(101)]
    public async Task PreviewAsync_RejectsInvalidLimitBeforeDatabaseAccess(int limit)
    {
        var executor = new RecordingExecutor(_ => PreviewTable());

        await Assert.ThrowsAsync<ArgumentOutOfRangeException>(() =>
            new LegacyExpertSelectionService(executor).PreviewAsync(Identity(), limit));

        Assert.Empty(executor.Calls);
    }

    [Fact]
    public async Task PreviewAsync_RejectsMissingOrMismatchedSelectedIdentity()
    {
        await Assert.ThrowsAsync<ExpertSelectionDataException>(() =>
            new LegacyExpertSelectionService(new RecordingExecutor(_ => PreviewTable()))
                .PreviewAsync(Identity()));

        await Assert.ThrowsAsync<ExpertSelectionDataException>(() =>
            new LegacyExpertSelectionService(new RecordingExecutor(_ => PreviewTable(
                ("Other", "0001", "005930", "Samsung", "70000", "0"))))
                .PreviewAsync(Identity()));
    }

    [Fact]
    public async Task PreviewAsync_RejectsWrongSchemaPartialSentinelAndRowOverflow()
    {
        var wrongSchema = PreviewTable();
        wrongSchema.Columns[5].ColumnName = "play_index";
        await Assert.ThrowsAsync<ExpertSelectionDataException>(() =>
            new LegacyExpertSelectionService(new RecordingExecutor(_ => wrongSchema))
                .PreviewAsync(Identity()));

        await Assert.ThrowsAsync<ExpertSelectionDataException>(() =>
            new LegacyExpertSelectionService(new RecordingExecutor(_ => PreviewTable(
                ("Analyst A", "0001", "005930", null, null, null))))
                .PreviewAsync(Identity()));

        var overflow = PreviewTable(
            ("Analyst A", "0001", "000001", "One", "100", "0"),
            ("Analyst A", "0001", "000002", "Two", "200", "1"),
            ("Analyst A", "0001", "000003", "Three", "300", "2"));
        await Assert.ThrowsAsync<ExpertSelectionDataException>(() =>
            new LegacyExpertSelectionService(new RecordingExecutor(_ => overflow))
                .PreviewAsync(Identity(), maximumItems: 1));
    }

    [Theory]
    [InlineData("005930", "Samsung", "0", "0")]
    [InlineData("005930", "Samsung", "-1", "0")]
    [InlineData("005930", "Samsung", "70000.5", "0")]
    [InlineData("005930", "Samsung", "9007199254740992", "0")]
    [InlineData("005930", "Samsung", "070000", "0")]
    [InlineData("00-5930", "Samsung", "70000", "0")]
    [InlineData("005930", " Samsung", "70000", "0")]
    [InlineData("005930", "Samsung", "70000", "01")]
    [InlineData("005930", "Samsung", "70000", "-1")]
    public async Task PreviewAsync_RejectsInvalidRecommendationValues(
        string stockCode,
        string stockName,
        string buyAmount,
        string playIndex)
    {
        var executor = new RecordingExecutor(_ => PreviewTable(
            ("Analyst A", "0001", stockCode, stockName, buyAmount, playIndex)));

        await Assert.ThrowsAsync<ExpertSelectionDataException>(() =>
            new LegacyExpertSelectionService(executor).PreviewAsync(Identity()));
    }

    [Theory]
    [InlineData("005930", "Samsung", "0", "035720", "Kakao", "0")]
    [InlineData("005930", "Samsung", "2", "035720", "Kakao", "1")]
    public async Task PreviewAsync_RejectsDuplicateOrNonIncreasingPlayIndex(
        string code1,
        string name1,
        string index1,
        string code2,
        string name2,
        string index2)
    {
        var executor = new RecordingExecutor(_ => PreviewTable(
            ("Analyst A", "0001", code1, name1, "100", index1),
            ("Analyst A", "0001", code2, name2, "200", index2)));

        await Assert.ThrowsAsync<ExpertSelectionDataException>(() =>
            new LegacyExpertSelectionService(executor).PreviewAsync(Identity()));
    }

    [Fact]
    public async Task PreviewAsync_AllowsRepeatedStockCodeAndNameAtDistinctPlayIndexes()
    {
        var executor = new RecordingExecutor(_ => PreviewTable(
            ("Analyst A", "0001", "005930", "Samsung", "100", "0"),
            ("Analyst A", "0001", "005930", "Samsung", "200", "1")));

        var result = await new LegacyExpertSelectionService(executor).PreviewAsync(Identity());

        Assert.Equal(2, result.Items.Count);
    }

    [Fact]
    public async Task PreviewAsync_StopsAfterProviderCancellation()
    {
        using var cancellation = new CancellationTokenSource();
        var executor = new RecordingExecutor(_ =>
        {
            cancellation.Cancel();
            return PreviewTable(
                ("Analyst A", "0001", "005930", "Samsung", "70000", "0"));
        });

        await Assert.ThrowsAnyAsync<OperationCanceledException>(() =>
            new LegacyExpertSelectionService(executor).PreviewAsync(
                Identity(),
                cancellationToken: cancellation.Token));

        Assert.Single(executor.Calls);
    }

    private static ExpertSelectionIdentity Identity() => new("0001", "Analyst A");

    private static DataTable SearchTable(params (string Name, string Code)[] rows)
    {
        var table = new DataTable();
        table.Columns.Add("EXPERT_NAME", typeof(string));
        table.Columns.Add("EXPERT_CODE", typeof(string));
        foreach (var row in rows)
        {
            table.Rows.Add(row.Name, row.Code);
        }

        return table;
    }

    private static DataTable PreviewTable(
        params (string ExpertName, string ExpertCode, string? StockCode,
            string? StockName, string? BuyAmount, string? PlayIndex)[] rows)
    {
        var table = new DataTable();
        table.Columns.Add("EXPERT_NAME", typeof(string));
        table.Columns.Add("EXPERT_CODE", typeof(string));
        table.Columns.Add("STOCK_CODE", typeof(string));
        table.Columns.Add("STOCK_NAME", typeof(string));
        table.Columns.Add("BUY_AMOUNT", typeof(string));
        table.Columns.Add("PLAY_INDEX", typeof(string));
        foreach (var row in rows)
        {
            table.Rows.Add(
                row.ExpertName,
                row.ExpertCode,
                row.StockCode is null ? DBNull.Value : row.StockCode,
                row.StockName is null ? DBNull.Value : row.StockName,
                row.BuyAmount is null ? DBNull.Value : row.BuyAmount,
                row.PlayIndex is null ? DBNull.Value : row.PlayIndex);
        }

        return table;
    }

    private sealed record RecordedCall(
        DataSourceKind Source,
        string QueryName,
        DataQuerySpec Spec,
        CancellationToken CancellationToken);

    private sealed class RecordingExecutor(Func<RecordedCall, DataTable> handler) : IDataQueryExecutor
    {
        private int _stringSqlCallCount;

        public List<RecordedCall> Calls { get; } = [];

        public int StringSqlCallCount => Volatile.Read(ref _stringSqlCallCount);

        public DataTable Execute(DataSourceKind source, string tableName, string query)
        {
            Interlocked.Increment(ref _stringSqlCallCount);
            throw new InvalidOperationException("String SQL fallback is forbidden.");
        }

        public Task<DataTable> ExecuteAsync(
            DataSourceKind source,
            string tableName,
            string query,
            CancellationToken cancellationToken = default)
        {
            Interlocked.Increment(ref _stringSqlCallCount);
            throw new InvalidOperationException("String SQL fallback is forbidden.");
        }

        public Task<DataTable> ExecuteAsync(
            DataSourceKind source,
            string tableName,
            DataQuerySpec query,
            CancellationToken cancellationToken = default)
        {
            cancellationToken.ThrowIfCancellationRequested();
            query.ValidateFor(source);
            var call = new RecordedCall(source, tableName, query, cancellationToken);
            Calls.Add(call);
            return Task.FromResult(handler(call).Copy());
        }
    }
}
