using System.Data;
using MMoneyCoderSharp.Data;

namespace MBN_STOCK_WEBVIEW.Core.Tests;

public sealed class LegacyThemeSelectionServiceTests
{
    [Fact]
    public async Task SearchAsync_UsesOriginalOracleAndMariaProvidersWithClosedIdentity()
    {
        var executor = new RecordingExecutor(call => call.TableName switch
        {
            "THEME_SEARCH_KRX" => SearchTable(("AI", "00000001", "KRX")),
            "THEME_SEARCH_NXT" => SearchTable(("Robotics", "00000002", "NXT")),
            _ => throw new InvalidOperationException(call.TableName)
        });
        var service = new LegacyThemeSelectionService(executor);
        using var cancellation = new CancellationTokenSource();

        var result = await service.SearchAsync(
            " ai ",
            ThemeSession.PreMarket,
            25,
            cancellation.Token);

        Assert.Equal("ai", result.Query);
        Assert.Equal(ThemeSession.PreMarket, result.NxtSession);
        Assert.False(result.IsTruncated);
        Assert.Collection(
            result.Items,
            item =>
            {
                Assert.Equal(DataSourceKind.Oracle, item.Source);
                Assert.Equal(
                    new ThemeSelectionIdentity(
                        ThemeMarket.Krx,
                        ThemeSession.NotApplicable,
                        "00000001",
                        "AI"),
                    item.Identity);
            },
            item =>
            {
                Assert.Equal(DataSourceKind.MariaDb, item.Source);
                Assert.Equal(
                    new ThemeSelectionIdentity(
                        ThemeMarket.Nxt,
                        ThemeSession.PreMarket,
                        "00000002",
                        "Robotics"),
                    item.Identity);
            });

        Assert.Collection(
            executor.Calls,
            call =>
            {
                Assert.Equal(DataSourceKind.Oracle, call.Source);
                Assert.Equal("THEME_SEARCH_KRX", call.TableName);
                Assert.Contains("SB_MARKET = 'KRX'", call.Spec.Sql, StringComparison.Ordinal);
                Assert.Contains("REGEXP_LIKE(s.SB_CODE, '^[0-9]{3,8}$'", call.Spec.Sql, StringComparison.Ordinal);
                Assert.Contains("d.SB_CODE = s.SB_CODE", call.Spec.Sql, StringComparison.Ordinal);
                Assert.DoesNotContain("SB_TITLE = TRIM", call.Spec.Sql, StringComparison.Ordinal);
                Assert.DoesNotContain("d.SB_TITLE = s.SB_TITLE", call.Spec.Sql, StringComparison.Ordinal);
                Assert.Contains("ROWNUM <= :row_limit", call.Spec.Sql, StringComparison.Ordinal);
                Assert.Equal("AI", call.Spec.Parameters[0].Value);
                Assert.Equal(26, call.Spec.Parameters[1].Value);
                Assert.Equal(cancellation.Token, call.CancellationToken);
            },
            call =>
            {
                Assert.Equal(DataSourceKind.MariaDb, call.Source);
                Assert.Equal("THEME_SEARCH_NXT", call.TableName);
                Assert.Contains("SB_MARKET = 'NXT'", call.Spec.Sql, StringComparison.Ordinal);
                Assert.Contains("s.SB_CODE REGEXP BINARY '^[0-9]{3,8}$'", call.Spec.Sql, StringComparison.Ordinal);
                Assert.Contains("BINARY d.SB_CODE = BINARY s.SB_CODE", call.Spec.Sql, StringComparison.Ordinal);
                Assert.DoesNotContain("TRIM(s.SB_TITLE)", call.Spec.Sql, StringComparison.Ordinal);
                Assert.DoesNotContain("BINARY d.SB_TITLE = BINARY s.SB_TITLE", call.Spec.Sql, StringComparison.Ordinal);
                Assert.Contains("LIMIT @row_limit", call.Spec.Sql, StringComparison.Ordinal);
                Assert.Equal("AI", call.Spec.Parameters[0].Value);
                Assert.Equal(26, call.Spec.Parameters[1].Value);
                Assert.Equal(cancellation.Token, call.CancellationToken);
            });
        Assert.All(executor.Calls, call =>
        {
            call.Spec.ValidateFor(call.Source);
            Assert.Contains("ESCAPE '!'", call.Spec.Sql, StringComparison.Ordinal);
            Assert.DoesNotContain("ORDER BY", call.Spec.Sql, StringComparison.OrdinalIgnoreCase);
            Assert.Equal(DbType.String, call.Spec.Parameters[0].DbType);
            Assert.Equal(DbType.Int32, call.Spec.Parameters[1].DbType);
        });
        Assert.Equal(0, executor.StringSqlCallCount);
    }

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

        await service.SearchAsync("   ", ThemeSession.AfterMarket);
        Assert.All(executor.Calls, call => Assert.Equal(string.Empty, call.Spec.Parameters[0].Value));
        Assert.All(executor.Calls, call => Assert.Equal(5_001, call.Spec.Parameters[1].Value));
        Assert.Equal(5_000, LegacyThemeSelectionService.DefaultMaximumResults);
        Assert.Equal(5_000, LegacyThemeSelectionService.MaximumResults);

        executor.Calls.Clear();
        await service.SearchAsync(" a!_% ", ThemeSession.AfterMarket);
        Assert.All(executor.Calls, call => Assert.Equal("A!!!_!%", call.Spec.Parameters[0].Value));
    }

    [Fact]
    public async Task SearchAsync_InitialConsonantsReadFullProvidersThenFilterInProviderOrder()
    {
        var executor = new RecordingExecutor(call => call.Source switch
        {
            DataSourceKind.Oracle => SearchTable(
                ("AI", "00000001", "KRX"),
                ("반도체", "00000002", "KRX")),
            DataSourceKind.MariaDb => SearchTable(
                ("바다차", "00000003", "NXT")),
            _ => throw new InvalidOperationException()
        });
        var service = new LegacyThemeSelectionService(executor);

        var result = await service.SearchAsync(
            " ㅂㄷㅊ ",
            ThemeSession.PreMarket,
            maximumResults: 1);

        Assert.Equal("ㅂㄷㅊ", result.Query);
        Assert.True(result.IsTruncated);
        var item = Assert.Single(result.Items);
        Assert.Equal(ThemeMarket.Krx, item.Identity.Market);
        Assert.Equal("반도체", item.Identity.ThemeTitle);
        Assert.All(executor.Calls, call =>
        {
            Assert.Equal(string.Empty, call.Spec.Parameters[0].Value);
            Assert.Equal(
                LegacyThemeSelectionService.MaximumResults + 1,
                call.Spec.Parameters[1].Value);
        });
    }

    [Fact]
    public async Task SearchAsync_InitialConsonantsValidateNonMatchingCandidatesBeforeFiltering()
    {
        var executor = new RecordingExecutor(call => call.Source switch
        {
            DataSourceKind.Oracle => SearchTable(("AI", "invalid", "KRX")),
            DataSourceKind.MariaDb => SearchTable(),
            _ => throw new InvalidOperationException()
        });
        var service = new LegacyThemeSelectionService(executor);

        await Assert.ThrowsAsync<ThemeSelectionDataException>(
            () => service.SearchAsync("ㅂㄷㅊ", ThemeSession.PreMarket));

        Assert.Single(executor.Calls);
    }

    [Fact]
    public async Task SearchAsync_InitialConsonantsConservativelyReportProviderCandidateCap()
    {
        var cappedKrx = SearchTable(
            Enumerable.Range(0, LegacyThemeSelectionService.MaximumResults + 1)
                .Select(index => (
                    Title: "알파",
                    Code: index.ToString("D8", System.Globalization.CultureInfo.InvariantCulture),
                    Market: "KRX"))
                .ToArray());
        var executor = new RecordingExecutor(call => call.Source switch
        {
            DataSourceKind.Oracle => cappedKrx,
            DataSourceKind.MariaDb => SearchTable(),
            _ => throw new InvalidOperationException()
        });
        var service = new LegacyThemeSelectionService(executor);

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

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

    [Fact]
    public async Task SearchAsync_PreservesLegacyThreeAndFourDigitReadIdentities()
    {
        var executor = new RecordingExecutor(call => call.Source switch
        {
            DataSourceKind.Oracle => SearchTable(("Legacy KRX", "123", "KRX")),
            DataSourceKind.MariaDb => SearchTable(("Legacy NXT", "0123", "NXT")),
            _ => throw new InvalidOperationException()
        });
        var service = new LegacyThemeSelectionService(executor);

        var result = await service.SearchAsync("", ThemeSession.PreMarket);

        Assert.Equal(
            ["123", "0123"],
            result.Items.Select(static item => item.Identity.ThemeCode));
    }

    [Fact]
    public async Task SearchAsync_AppliesCombinedBoundAndPreservesKrxProviderOrderBeforeNxt()
    {
        var executor = new RecordingExecutor(call => call.Source switch
        {
            DataSourceKind.Oracle => SearchTable(
                ("Zulu", "00000002", "KRX"),
                ("Alpha", "00000001", "KRX")),
            DataSourceKind.MariaDb => SearchTable(("Beta", "00000003", "NXT")),
            _ => throw new InvalidOperationException()
        });
        var service = new LegacyThemeSelectionService(executor);

        var result = await service.SearchAsync("", ThemeSession.PreMarket, 2);

        Assert.True(result.IsTruncated);
        Assert.Equal(
            ["Zulu", "Alpha"],
            result.Items.Select(static item => item.Identity.ThemeTitle));
        Assert.All(executor.Calls, call => Assert.Equal(3, call.Spec.Parameters[1].Value));
    }

    [Theory]
    [InlineData(ThemeSession.NotApplicable)]
    [InlineData((ThemeSession)99)]
    public async Task SearchAsync_RejectsUnsafeNxtSessionBeforeDatabaseAccess(ThemeSession session)
    {
        var executor = new RecordingExecutor(_ => SearchTable());
        var service = new LegacyThemeSelectionService(executor);

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

        Assert.Empty(executor.Calls);
    }

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

        await Assert.ThrowsAsync<ArgumentOutOfRangeException>(
            () => service.SearchAsync("AI", ThemeSession.PreMarket, 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 LegacyThemeSelectionService(executor);

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

        Assert.Empty(executor.Calls);
    }

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

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

        Assert.Empty(executor.Calls);
    }

    [Fact]
    public async Task SearchAsync_RejectsSchemaMarketAndBoundViolations()
    {
        var wrongSchema = SearchTable();
        wrongSchema.Columns[0].ColumnName = "theme_title";
        var executor = new RecordingExecutor(_ => wrongSchema);
        var service = new LegacyThemeSelectionService(executor);

        await Assert.ThrowsAsync<ThemeSelectionDataException>(
            () => service.SearchAsync("", ThemeSession.PreMarket));

        executor = new RecordingExecutor(_ => SearchTable(("AI", "00000001", "NXT")));
        service = new LegacyThemeSelectionService(executor);
        await Assert.ThrowsAsync<ThemeSelectionDataException>(
            () => service.SearchAsync("", ThemeSession.PreMarket));

        executor = new RecordingExecutor(_ => SearchTable(
            ("A", "00000001", "KRX"),
            ("B", "00000002", "KRX"),
            ("C", "00000003", "KRX")));
        service = new LegacyThemeSelectionService(executor);
        await Assert.ThrowsAsync<ThemeSelectionDataException>(
            () => service.SearchAsync("", ThemeSession.PreMarket, 1));
    }

    [Theory]
    [InlineData("AI", "00000001", "Robotics", "00000001")]
    public async Task SearchAsync_RejectsDuplicateCodeWithinMarket(
        string title1,
        string code1,
        string title2,
        string code2)
    {
        var executor = new RecordingExecutor(_ => SearchTable(
            (title1, code1, "KRX"),
            (title2, code2, "KRX")));
        var service = new LegacyThemeSelectionService(executor);

        var exception = await Assert.ThrowsAsync<ThemeSelectionDataException>(
            () => service.SearchAsync("", ThemeSession.PreMarket));

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

    [Fact]
    public async Task SearchAsync_PreservesExactLegacyEdgeSpacesAndDuplicateTitlesWithDistinctCodes()
    {
        var executor = new RecordingExecutor(call => call.Source == DataSourceKind.Oracle
            ? SearchTable(
                (" AI", "00000001", "KRX"),
                ("AI", "00000002", "KRX"),
                ("AI", "00000003", "KRX"))
            : SearchTable(("NXT ", "00000004", "NXT")));

        var result = await new LegacyThemeSelectionService(executor)
            .SearchAsync("", ThemeSession.PreMarket);

        Assert.Equal([" AI", "AI", "AI", "NXT "],
            result.Items.Select(static item => item.Identity.ThemeTitle));
        Assert.Equal(["00000001", "00000002", "00000003", "00000004"],
            result.Items.Select(static item => item.Identity.ThemeCode));
    }

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

        await Assert.ThrowsAnyAsync<OperationCanceledException>(
            () => service.SearchAsync(
                "AI",
                ThemeSession.PreMarket,
                cancellationToken: cancellation.Token));

        Assert.Empty(executor.Calls);
    }

    [Fact]
    public async Task PreviewAsync_KrxUsesBoundIdentityAndMapsInputOrder()
    {
        var identity = new ThemeSelectionIdentity(
            ThemeMarket.Krx,
            ThemeSession.NotApplicable,
            "00000001",
            "AI");
        var executor = new RecordingExecutor(_ => PreviewTable(
            ("AI", "00000001", "KRX", "Alpha", "P005930", "0"),
            ("AI", "00000001", "KRX", "Beta", "D035720", "2")));
        var service = new LegacyThemeSelectionService(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 ThemeItemPreview(0, "P005930", "Alpha"),
                new ThemeItemPreview(2, "D035720", "Beta")
            ],
            result.Items);
        var call = Assert.Single(executor.Calls);
        Assert.Equal(DataSourceKind.Oracle, call.Source);
        Assert.Equal("THEME_PREVIEW_KRX", call.TableName);
        Assert.Contains("LEFT JOIN", call.Spec.Sql, StringComparison.Ordinal);
        Assert.Contains("F_MKT_HALT = 'N'", call.Spec.Sql, StringComparison.Ordinal);
        Assert.Contains("ROWNUM <= :row_limit", call.Spec.Sql, StringComparison.Ordinal);
        Assert.Equal(
            ["theme_code", "theme_title", "row_limit"],
            call.Spec.Parameters.Select(static parameter => parameter.Name));
        Assert.Equal("00000001", call.Spec.Parameters[0].Value);
        Assert.Equal("AI", call.Spec.Parameters[1].Value);
        Assert.Equal(13, call.Spec.Parameters[2].Value);
        Assert.Equal(cancellation.Token, call.CancellationToken);
        Assert.Equal(0, executor.StringSqlCallCount);
    }

    [Fact]
    public async Task PreviewAsync_NxtUsesMariaAndPreservesExplicitSession()
    {
        var identity = new ThemeSelectionIdentity(
            ThemeMarket.Nxt,
            ThemeSession.AfterMarket,
            "00000002",
            "Robotics");
        var executor = new RecordingExecutor(_ => PreviewTable(
            ("Robotics", "00000002", "NXT", "Gamma", "X005930", "0")));
        var service = new LegacyThemeSelectionService(executor);

        var result = await service.PreviewAsync(identity);

        Assert.Equal(ThemeSession.AfterMarket, result.Identity.Session);
        var call = Assert.Single(executor.Calls);
        Assert.Equal(DataSourceKind.MariaDb, call.Source);
        Assert.Equal("THEME_PREVIEW_NXT", call.TableName);
        Assert.Contains("F_STOP_GUBUN = 'N'", call.Spec.Sql, StringComparison.Ordinal);
        Assert.Contains("FROM N_STOCK", call.Spec.Sql, StringComparison.Ordinal);
        Assert.Contains("FROM N_KOSDAQ_STOCK", call.Spec.Sql, StringComparison.Ordinal);
        Assert.Contains(
            "l.SB_CODE REGEXP BINARY '^[0-9]{8}$'",
            call.Spec.Sql,
            StringComparison.Ordinal);
        Assert.DoesNotContain("v_all_stock", call.Spec.Sql, StringComparison.OrdinalIgnoreCase);
        Assert.Contains("LIMIT @row_limit", call.Spec.Sql, StringComparison.Ordinal);
        call.Spec.ValidateFor(DataSourceKind.MariaDb);
    }

    [Fact]
    public async Task PreviewAsync_MapsSingleNullItemSentinelToEmptyTheme()
    {
        var identity = KrxIdentity();
        var executor = new RecordingExecutor(_ => PreviewTable(
            ("AI", "00000001", "KRX", null, null, null)));
        var service = new LegacyThemeSelectionService(executor);

        var result = await service.PreviewAsync(identity);

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

    [Fact]
    public async Task PreviewAsync_AppliesMaximumAndReportsTruncation()
    {
        var identity = KrxIdentity();
        var executor = new RecordingExecutor(_ => PreviewTable(
            ("AI", "00000001", "KRX", "Alpha", "P000001", "0"),
            ("AI", "00000001", "KRX", "Beta", "P000002", "1")));
        var service = new LegacyThemeSelectionService(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);
    }

    public static TheoryData<ThemeSelectionIdentity> UnsafeIdentities => new()
    {
        new(ThemeMarket.Krx, ThemeSession.PreMarket, "00000001", "AI"),
        new(ThemeMarket.Nxt, ThemeSession.NotApplicable, "00000001", "AI"),
        new(ThemeMarket.Nxt, (ThemeSession)99, "00000001", "AI"),
        new((ThemeMarket)99, ThemeSession.NotApplicable, "00000001", "AI"),
        new(ThemeMarket.Krx, ThemeSession.NotApplicable, "12", "AI"),
        new(ThemeMarket.Krx, ThemeSession.NotApplicable, "0000000A", "AI"),
        new(ThemeMarket.Krx, ThemeSession.NotApplicable, "00000001", "   "),
        new(ThemeMarket.Krx, ThemeSession.NotApplicable, "00000001", new string('A', 129)),
        new(ThemeMarket.Krx, ThemeSession.NotApplicable, "00000001", "bad\u0000title")
    };

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

        await Assert.ThrowsAsync<ArgumentException>(() => service.PreviewAsync(identity));

        Assert.Empty(executor.Calls);
    }

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

        await Assert.ThrowsAsync<ArgumentOutOfRangeException>(
            () => service.PreviewAsync(KrxIdentity(), limit));

        Assert.Empty(executor.Calls);
    }

    [Fact]
    public async Task PreviewAsync_RejectsMissingOrMismatchedSelectedIdentity()
    {
        var executor = new RecordingExecutor(_ => PreviewTable());
        var service = new LegacyThemeSelectionService(executor);

        await Assert.ThrowsAsync<ThemeSelectionDataException>(
            () => service.PreviewAsync(KrxIdentity()));

        executor = new RecordingExecutor(_ => PreviewTable(
            ("Other", "00000001", "KRX", "Alpha", "P005930", "0")));
        service = new LegacyThemeSelectionService(executor);
        await Assert.ThrowsAsync<ThemeSelectionDataException>(
            () => service.PreviewAsync(KrxIdentity()));
    }

    [Fact]
    public async Task PreviewAsync_PreservesExactStoredTitleWithEdgeSpaces()
    {
        var identity = new ThemeSelectionIdentity(
            ThemeMarket.Krx,
            ThemeSession.NotApplicable,
            "00000001",
            " AI ");
        var executor = new RecordingExecutor(_ => PreviewTable(
            (" AI ", "00000001", "KRX", "Alpha", "P005930", "0")));

        var result = await new LegacyThemeSelectionService(executor).PreviewAsync(identity);

        Assert.Equal(" AI ", result.Identity.ThemeTitle);
        Assert.Equal(" AI ", Assert.Single(executor.Calls).Spec.Parameters[1].Value);
    }

    [Fact]
    public async Task PreviewAsync_NxtEmptyActiveSentinelRemainsAVisibleEmptySelection()
    {
        var identity = new ThemeSelectionIdentity(
            ThemeMarket.Nxt,
            ThemeSession.PreMarket,
            "00000004",
            " NXT Theme");
        var executor = new RecordingExecutor(_ => PreviewTable(
            (" NXT Theme", "00000004", "NXT", null, null, null)));

        var result = await new LegacyThemeSelectionService(executor).PreviewAsync(identity);

        Assert.Equal(identity, result.Identity);
        Assert.Empty(result.Items);
    }

    [Fact]
    public async Task PreviewAsync_RejectsPartialSentinelAndWrongMarketItemCode()
    {
        var executor = new RecordingExecutor(_ => PreviewTable(
            ("AI", "00000001", "KRX", "Alpha", null, null)));
        var service = new LegacyThemeSelectionService(executor);
        await Assert.ThrowsAsync<ThemeSelectionDataException>(
            () => service.PreviewAsync(KrxIdentity()));

        executor = new RecordingExecutor(_ => PreviewTable(
            ("AI", "00000001", "KRX", "Alpha", "X005930", "0")));
        service = new LegacyThemeSelectionService(executor);
        await Assert.ThrowsAsync<ThemeSelectionDataException>(
            () => service.PreviewAsync(KrxIdentity()));
    }

    [Theory]
    [InlineData("P005930", "0", "D035720", "0")]
    [InlineData("P005930", "2", "D035720", "1")]
    public async Task PreviewAsync_RejectsDuplicateOrNonIncreasingItemIndex(
        string code1,
        string index1,
        string code2,
        string index2)
    {
        var executor = new RecordingExecutor(_ => PreviewTable(
            ("AI", "00000001", "KRX", "Alpha", code1, index1),
            ("AI", "00000001", "KRX", "Beta", code2, index2)));
        var service = new LegacyThemeSelectionService(executor);

        await Assert.ThrowsAsync<ThemeSelectionDataException>(
            () => service.PreviewAsync(KrxIdentity()));
    }

    [Fact]
    public async Task PreviewAsync_AllowsRepeatedItemAtDistinctIndexes()
    {
        var executor = new RecordingExecutor(_ => PreviewTable(
            ("AI", "00000001", "KRX", "Samsung", "P005930", "0"),
            ("AI", "00000001", "KRX", "Samsung", "P005930", "1")));

        var result = await new LegacyThemeSelectionService(executor)
            .PreviewAsync(KrxIdentity());

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

    [Theory]
    [InlineData("01")]
    [InlineData("-1")]
    [InlineData("10000")]
    [InlineData("x")]
    public async Task PreviewAsync_RejectsNonCanonicalOrOutOfRangeIndex(string index)
    {
        var executor = new RecordingExecutor(_ => PreviewTable(
            ("AI", "00000001", "KRX", "Alpha", "P005930", index)));
        var service = new LegacyThemeSelectionService(executor);

        await Assert.ThrowsAsync<ThemeSelectionDataException>(
            () => service.PreviewAsync(KrxIdentity()));
    }

    [Fact]
    public async Task PreviewAsync_RejectsWrongSchemaAndProviderRowOverflow()
    {
        var wrongSchema = PreviewTable();
        wrongSchema.Columns[5].ColumnName = "item_index";
        var executor = new RecordingExecutor(_ => wrongSchema);
        var service = new LegacyThemeSelectionService(executor);
        await Assert.ThrowsAsync<ThemeSelectionDataException>(
            () => service.PreviewAsync(KrxIdentity()));

        executor = new RecordingExecutor(_ => PreviewTable(
            ("AI", "00000001", "KRX", "A", "P000001", "0"),
            ("AI", "00000001", "KRX", "B", "P000002", "1"),
            ("AI", "00000001", "KRX", "C", "P000003", "2")));
        service = new LegacyThemeSelectionService(executor);
        await Assert.ThrowsAsync<ThemeSelectionDataException>(
            () => service.PreviewAsync(KrxIdentity(), maximumItems: 1));
    }

    [Fact]
    public async Task PreviewAsync_PropagatesPreCanceledTokenWithoutDatabaseAccess()
    {
        var executor = new RecordingExecutor(_ => PreviewTable());
        var service = new LegacyThemeSelectionService(executor);
        using var cancellation = new CancellationTokenSource();
        cancellation.Cancel();

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

        Assert.Empty(executor.Calls);
    }

    private static ThemeSelectionIdentity KrxIdentity() => new(
        ThemeMarket.Krx,
        ThemeSession.NotApplicable,
        "00000001",
        "AI");

    private static DataTable SearchTable(
        params (string Title, string Code, string Market)[] rows)
    {
        var table = new DataTable();
        table.Columns.Add("THEME_TITLE", typeof(string));
        table.Columns.Add("THEME_CODE", typeof(string));
        table.Columns.Add("THEME_MARKET", typeof(string));
        foreach (var row in rows)
        {
            table.Rows.Add(row.Title, row.Code, row.Market);
        }

        return table;
    }

    private static DataTable PreviewTable(
        params (string Title, string Code, string Market, string? ItemName, string? ItemCode, string? Index)[] rows)
    {
        var table = new DataTable();
        table.Columns.Add("THEME_TITLE", typeof(string));
        table.Columns.Add("THEME_CODE", typeof(string));
        table.Columns.Add("THEME_MARKET", typeof(string));
        table.Columns.Add("ITEM_NAME", typeof(string));
        table.Columns.Add("ITEM_CODE", typeof(string));
        table.Columns.Add("ITEM_INDEX", typeof(string));
        foreach (var row in rows)
        {
            table.Rows.Add(
                row.Title,
                row.Code,
                row.Market,
                row.ItemName is null ? DBNull.Value : row.ItemName,
                row.ItemCode is null ? DBNull.Value : row.ItemCode,
                row.Index is null ? DBNull.Value : row.Index);
        }

        return table;
    }

    private sealed record RecordedCall(
        DataSourceKind Source,
        string TableName,
        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());
        }
    }
}
