using System.Data;
using MBN_STOCK_WEBVIEW.Core.Playout.Scenes;
using MMoneyCoderSharp.Data;

namespace MBN_STOCK_WEBVIEW.Core.Tests;

public sealed class ParameterizedMarketSceneDataLoadersTests
{
    [Fact]
    public async Task S5001_every_top_level_branch_uses_a_bounded_parameterized_query_and_builds_mutations()
    {
        S5001SceneLoadRequest[] requests =
        [
            new S5001BalticDryIndexLoadRequest(),
            new S5001CommodityLoadRequest(S5001CommodityTarget.Copper),
            new S5001DomesticIndexLoadRequest(S5001DomesticIndexTarget.Kospi, S5001IndexMode.Current),
            new S5001ForeignIndexLoadRequest(S5001ForeignIndexTarget.Nasdaq),
            new S5001ExchangeRateLoadRequest(S5001ExchangeRateTarget.WonDollar),
            new S5001DomesticIndustryLoadRequest(LegacyDomesticEquityMarket.Kosdaq, "반도체"),
            new S5001ForeignIndustryLoadRequest("필라델피아 반도체"),
            new S5001ForeignStockLoadRequest("NVIDIA"),
            new S5001DomesticStockLoadRequest(
                LegacyDomesticEquityMarket.Kospi,
                S5001DomesticStockMode.Current,
                S5001NxtBadge.None,
                S5001StockTradingStatus.Regular,
                "삼성전자")
        ];

        foreach (var request in requests)
        {
            var executor = new RecordingExecutor(QuoteTable("테스트", 100m, "+", 1m, 1m));
            var data = await new S5001SceneDataLoader(executor).LoadAsync(request);
            var mutations = new S5001SceneMutationBuilder().Build(data);

            Assert.Equal(request.Branch, data.Branch);
            Assert.NotEmpty(mutations);
            var call = Assert.Single(executor.Calls);
            AssertReadOnlyBounded(call);
            Assert.NotEmpty(call.Spec.Parameters);
            Assert.Equal(0, executor.StringCalls);
        }
    }

    [Fact]
    public async Task S5001_stock_modes_select_fixed_source_and_parameter_marker_without_sql_text_injection()
    {
        const string selected = "이름' OR '1'='1";
        var cases = new[]
        {
            (new S5001DomesticStockLoadRequest(
                    LegacyDomesticEquityMarket.Kospi,
                    S5001DomesticStockMode.ExpectedOpening,
                    S5001NxtBadge.None,
                    S5001StockTradingStatus.Regular,
                    selected),
                DataSourceKind.Oracle, "SCENE_EXPECTED_KOSPI_STOCK", ":stockName"),
            (new S5001DomesticStockLoadRequest(
                    LegacyDomesticEquityMarket.Kosdaq,
                    S5001DomesticStockMode.AfterHoursSinglePrice,
                    S5001NxtBadge.None,
                    S5001StockTradingStatus.Regular,
                    selected),
                DataSourceKind.Oracle, "SCENE_AFTER_HOURS_KOSDAQ_STOCK", ":stockName"),
            (new S5001DomesticStockLoadRequest(
                    LegacyDomesticEquityMarket.Kospi,
                    S5001DomesticStockMode.Current,
                    S5001NxtBadge.None,
                    S5001StockTradingStatus.Halted,
                    selected),
                DataSourceKind.Oracle, "SCENE_HALTED_KOSPI_STOCK", ":stockName"),
            (new S5001DomesticStockLoadRequest(
                    LegacyDomesticEquityMarket.NxtKospi,
                    S5001DomesticStockMode.Current,
                    S5001NxtBadge.PreMarket,
                    S5001StockTradingStatus.Regular,
                    selected + "(NXT)"),
                DataSourceKind.MariaDb, "SCENE_NXT_KOSPI_STOCK", "@stockName")
        };

        foreach (var (request, source, tableName, placeholder) in cases)
        {
            var executor = new RecordingExecutor(QuoteTable("테스트", 100m, "+", 1m, 1m));
            var data = await new S5001SceneDataLoader(executor).LoadAsync(request);
            var call = Assert.Single(executor.Calls);
            var parameter = Assert.Single(call.Spec.Parameters);

            Assert.Equal(source, call.Source);
            Assert.Equal(tableName, call.TableName);
            Assert.Contains(placeholder, call.Spec.Sql, StringComparison.Ordinal);
            Assert.DoesNotContain(selected, call.Spec.Sql, StringComparison.Ordinal);
            Assert.Equal(selected, parameter.Value);
            Assert.Equal(DbType.String, parameter.DbType);
            Assert.NotEmpty(new S5001SceneMutationBuilder().Build(data));
        }
    }

    [Fact]
    public async Task Foreign_selector_queries_cannot_widen_beyond_their_master_scope()
    {
        const string industryName = "Sensitive US industry";
        var industryExecutor = new RecordingExecutor(
            QuoteTable(industryName, 100m, "+", 1m, 1m));
        await new S5001SceneDataLoader(industryExecutor).LoadAsync(
            new S5001ForeignIndustryLoadRequest(industryName));
        var industry = Assert.Single(industryExecutor.Calls);

        Assert.Equal("SCENE_FOREIGN_INDUSTRY", industry.TableName);
        Assert.Contains("b.f_fdtc = '0'", industry.Spec.Sql, StringComparison.OrdinalIgnoreCase);
        Assert.Contains("b.f_natc = 'US'", industry.Spec.Sql, StringComparison.OrdinalIgnoreCase);
        Assert.Contains("a.f_fdtc = '0'", industry.Spec.Sql, StringComparison.OrdinalIgnoreCase);
        Assert.DoesNotContain(industryName, industry.Spec.Sql, StringComparison.Ordinal);
        Assert.Equal(industryName, Assert.Single(industry.Spec.Parameters).Value);

        const string stockName = "Sensitive world stock";
        var stockExecutor = new RecordingExecutor(
            QuoteTable(stockName, 100m, "+", 1m, 1m));
        await new S5001SceneDataLoader(stockExecutor).LoadAsync(
            new S5001ForeignStockLoadRequest(stockName));
        var stock = Assert.Single(stockExecutor.Calls);

        Assert.Equal("SCENE_FOREIGN_STOCK", stock.TableName);
        Assert.Contains("b.f_fdtc = '1'", stock.Spec.Sql, StringComparison.OrdinalIgnoreCase);
        Assert.Contains(
            "b.f_natc IN ('US', 'TW')",
            stock.Spec.Sql,
            StringComparison.OrdinalIgnoreCase);
        Assert.Contains("a.f_fdtc = '1'", stock.Spec.Sql, StringComparison.OrdinalIgnoreCase);
        Assert.DoesNotContain(stockName, stock.Spec.Sql, StringComparison.Ordinal);
        Assert.Equal(stockName, Assert.Single(stock.Spec.Parameters).Value);
    }

    [Fact]
    public async Task Exact_foreign_stock_identity_allows_any_two_letter_nation_without_name_collision()
    {
        const string name = "Shared Name";
        const string symbol = "LNS@LON";
        const string nation = "GB";
        var executor = new RecordingExecutor(QuoteTable(name, 100m, "+", 1m, 1m));

        var data = await new S5001SceneDataLoader(executor).LoadAsync(
            new S5001ExactForeignStockLoadRequest(name, symbol, nation));

        Assert.IsType<S5001ForeignStockSceneData>(data);
        var call = Assert.Single(executor.Calls);
        Assert.Equal("SCENE_FOREIGN_STOCK", call.TableName);
        Assert.Contains("b.f_symb = :symbol", call.Spec.Sql, StringComparison.OrdinalIgnoreCase);
        Assert.Contains("b.f_natc = :nationCode", call.Spec.Sql, StringComparison.OrdinalIgnoreCase);
        Assert.DoesNotContain("b.f_natc IN ('US', 'TW')", call.Spec.Sql, StringComparison.OrdinalIgnoreCase);
        Assert.Equal(
            [name, symbol, nation],
            call.Spec.Parameters.Select(parameter => parameter.Value));
    }

    [Fact]
    public async Task Exact_foreign_industry_identity_binds_selected_tuple_despite_duplicate_display_name()
    {
        const string koreanName = "Shared Korean Name";
        var inputName = new string('I', 160);
        const string symbol = "NAS@USTECH";
        var executor = new RecordingExecutor(QuoteTable(koreanName, 100m, "+", 1m, 1m));

        var data = await new S5001SceneDataLoader(executor).LoadAsync(
            new S5001ExactForeignIndustryLoadRequest(
                koreanName,
                inputName,
                symbol,
                "US"));

        Assert.IsType<S5001ForeignIndustrySceneData>(data);
        var call = Assert.Single(executor.Calls);
        Assert.Equal("SCENE_FOREIGN_INDUSTRY", call.TableName);
        Assert.Contains("b.f_knam = :industryName", call.Spec.Sql, StringComparison.OrdinalIgnoreCase);
        Assert.Contains("b.f_input_name = :inputName", call.Spec.Sql, StringComparison.OrdinalIgnoreCase);
        Assert.Contains("b.f_symb = :symbol", call.Spec.Sql, StringComparison.OrdinalIgnoreCase);
        Assert.Contains("b.f_natc = :nationCode", call.Spec.Sql, StringComparison.OrdinalIgnoreCase);
        Assert.Equal(
            [koreanName, inputName, symbol, "US"],
            call.Spec.Parameters.Select(parameter => parameter.Value));
    }

    [Fact]
    public async Task Historical_foreign_industry_name_only_fallback_rejects_duplicate_rows()
    {
        var duplicateRows = QuoteTable("Shared Korean Name", 100m, "+", 1m, 1m);
        duplicateRows.ImportRow(duplicateRows.Rows[0]);
        var executor = new RecordingExecutor(duplicateRows);

        await Assert.ThrowsAsync<LegacySceneDataException>(() =>
            new S5001SceneDataLoader(executor).LoadAsync(
                new S5001ForeignIndustryLoadRequest("Shared Korean Name")));

        var call = Assert.Single(executor.Calls);
        Assert.Single(call.Spec.Parameters);
        Assert.DoesNotContain("f_symb = :symbol", call.Spec.Sql, StringComparison.OrdinalIgnoreCase);
    }

    [Fact]
    public async Task Closed_foreign_index_symbols_keep_global_markets_but_reject_stock_master_rows()
    {
        var executor = new RecordingExecutor(QuoteTable("DAX", 100m, "+", 1m, 1m));

        await new S5001SceneDataLoader(executor).LoadAsync(
            new S5001ForeignIndexLoadRequest(S5001ForeignIndexTarget.GermanyDax));

        var call = Assert.Single(executor.Calls);
        Assert.Contains("a.f_fdtc = '0'", call.Spec.Sql, StringComparison.OrdinalIgnoreCase);
        Assert.Contains("b.f_fdtc = '0'", call.Spec.Sql, StringComparison.OrdinalIgnoreCase);
        Assert.DoesNotContain("f_natc", call.Spec.Sql, StringComparison.OrdinalIgnoreCase);
    }

    [Fact]
    public async Task S5001_closed_target_sets_all_construct_valid_specs_and_generate_typed_data()
    {
        foreach (var target in Enum.GetValues<S5001CommodityTarget>())
        {
            var executor = new RecordingExecutor(QuoteTable("상품", 1.25m, "+", 0.25m, 1m));
            var data = await new S5001SceneDataLoader(executor)
                .LoadAsync(new S5001CommodityLoadRequest(target));
            Assert.Equal(target, Assert.IsType<S5001CommoditySceneData>(data).Target);
            AssertReadOnlyBounded(Assert.Single(executor.Calls));
        }

        foreach (var target in Enum.GetValues<S5001DomesticIndexTarget>())
        {
            foreach (var mode in Enum.GetValues<S5001IndexMode>())
            {
                var executor = new RecordingExecutor(QuoteTable("지수", 1m, "+", 1m, 1m));
                var data = await new S5001SceneDataLoader(executor)
                    .LoadAsync(new S5001DomesticIndexLoadRequest(target, mode));
                Assert.Equal(target, Assert.IsType<S5001DomesticIndexSceneData>(data).Target);
                AssertReadOnlyBounded(Assert.Single(executor.Calls));
            }
        }

        foreach (var target in Enum.GetValues<S5001ForeignIndexTarget>())
        {
            var executor = new RecordingExecutor(QuoteTable("해외", 1m, "-", -1m, -1m));
            var data = await new S5001SceneDataLoader(executor)
                .LoadAsync(new S5001ForeignIndexLoadRequest(target));
            Assert.Equal(target, Assert.IsType<S5001ForeignIndexSceneData>(data).Target);
        }

        foreach (var target in Enum.GetValues<S5001ExchangeRateTarget>())
        {
            var executor = new RecordingExecutor(QuoteTable("환율", 1m, "", 0m, 0m));
            var data = await new S5001SceneDataLoader(executor)
                .LoadAsync(new S5001ExchangeRateLoadRequest(target));
            Assert.Equal(target, Assert.IsType<S5001ExchangeRateSceneData>(data).Target);
            Assert.Equal(
                new[] { "displayName", "inputCode" },
                Assert.Single(executor.Calls).Spec.Parameters.Select(parameter => parameter.Name));
        }
    }

    [Fact]
    public async Task S5001_nxt_industry_fails_closed_because_original_has_no_request_implementation()
    {
        foreach (var market in new[]
                 {
                     LegacyDomesticEquityMarket.NxtKospi,
                     LegacyDomesticEquityMarket.NxtKosdaq
                 })
        {
            var executor = new RecordingExecutor(QuoteTable("반도체", 100m, "+", 1m, 1m));
            await Assert.ThrowsAsync<LegacySceneDataException>(() =>
                new S5001SceneDataLoader(executor).LoadAsync(
                    new S5001DomesticIndustryLoadRequest(market, "반도체")));
            Assert.Empty(executor.Calls);
        }
    }

    [Fact]
    public async Task Remaining_closed_stock_and_nxt_query_variants_construct_provider_valid_specs()
    {
        var cases = new S5001SceneLoadRequest[]
        {
            new S5001DomesticStockLoadRequest(
                LegacyDomesticEquityMarket.NxtKosdaq,
                S5001DomesticStockMode.Current,
                S5001NxtBadge.AfterMarket,
                S5001StockTradingStatus.Regular,
                "A(NXT)"),
            new S5001DomesticStockLoadRequest(
                LegacyDomesticEquityMarket.Kosdaq,
                S5001DomesticStockMode.Current,
                S5001NxtBadge.None,
                S5001StockTradingStatus.Halted,
                "B"),
            new S5001DomesticStockLoadRequest(
                LegacyDomesticEquityMarket.Kospi,
                S5001DomesticStockMode.AfterHoursSinglePrice,
                S5001NxtBadge.None,
                S5001StockTradingStatus.Regular,
                "C")
        };

        foreach (var request in cases)
        {
            var executor = new RecordingExecutor(QuoteTable("테스트", 1m, "+", 1m, 1m));
            var data = await new S5001SceneDataLoader(executor).LoadAsync(request);
            var call = Assert.Single(executor.Calls);
            AssertReadOnlyBounded(call);
            Assert.NotEmpty(new S5001SceneMutationBuilder().Build(data));
        }
    }

    [Fact]
    public async Task S5032_all_branches_preserve_query_order_and_generate_builder_mutations()
    {
        var indexExecutor = new RecordingExecutor(
            QuoteTable("코스피", 2_800m, "+", 10m, 0.36m),
            FutureTable("선물", 380m, "-", 2m, -0.52m, 100m, 200m, 3_000_000m));
        var index = await new S5032SceneDataLoader(indexExecutor).LoadAsync(
            new S5032IndexAndFuturesLoadRequest(LegacyMarketQuoteTarget.Kospi));
        Assert.Equal(
            new[] { "SCENE_KOSPI_INDEX", "SCENE_FUTURES_DETAIL" },
            indexExecutor.Calls.Select(call => call.TableName));
        Assert.NotEmpty(new S5032SceneMutationBuilder().Build(index));

        var industryExecutor = new RecordingExecutor(
            QuoteTable("기계", 100m, "+", 1m, 1m),
            QuoteTable("반도체", 200m, "", 0m, 0m));
        var industry = await new S5032SceneDataLoader(industryExecutor).LoadAsync(
            new S5032DomesticIndustryPairLoadRequest(
                LegacyDomesticEquityMarket.Kospi, "기계", "반도체"));
        Assert.Equal(
            new[] { "기계", "반도체" },
            industryExecutor.Calls.Select(call => Assert.Single(call.Spec.Parameters).Value));
        Assert.NotEmpty(new S5032SceneMutationBuilder().Build(industry));

        var stockExecutor = new RecordingExecutor(
            QuoteTable("A", 1_000m, "+", 100m, 10m),
            QuoteTable("B", 2_000m, "-", -200m, -10m));
        var stocks = await new S5032SceneDataLoader(stockExecutor).LoadAsync(
            new S5032DomesticStockPairLoadRequest(
                LegacyDomesticEquityMarket.Kospi, "A",
                LegacyDomesticEquityMarket.Kosdaq, "B"));
        Assert.Equal(
            new[] { "SCENE_KOSPI_STOCK", "SCENE_KOSDAQ_STOCK" },
            stockExecutor.Calls.Select(call => call.TableName));
        Assert.NotEmpty(new S5032SceneMutationBuilder().Build(stocks));
        Assert.All(indexExecutor.Calls.Concat(industryExecutor.Calls).Concat(stockExecutor.Calls), AssertReadOnlyBounded);
    }

    [Fact]
    public async Task S8018_all_branches_preserve_original_first_then_second_query_order()
    {
        var marketExecutor = new RecordingExecutor(
            QuoteTable("다우", 1m, "+", 1m, 1m),
            QuoteTable("나스닥", 2m, "-", -1m, -1m));
        var market = await new S8018SceneDataLoader(marketExecutor).LoadAsync(
            new S8018MarketPairLoadRequest(
                S8018MarketDataMode.Current,
                LegacyMarketQuoteTarget.Dow,
                LegacyMarketQuoteTarget.Nasdaq));
        Assert.Equal(
            new[] { "DJI@DJI", "NAS@IXIC" },
            marketExecutor.Calls.Select(call => Assert.Single(call.Spec.Parameters).Value));
        Assert.NotEmpty(new S8018SceneMutationBuilder().Build(market));

        var mixedExecutor = new RecordingExecutor(
            QuoteTable("삼성전자", 70_000m, "+", 500m, 0.72m),
            QuoteTable("나스닥", 20_000m, "+", 100m, 0.5m));
        var mixed = await new S8018SceneDataLoader(mixedExecutor).LoadAsync(
            new S8018MixedMarketAndStockLoadRequest(
                S8018PairSide.First,
                LegacyMarketQuoteTarget.Nasdaq,
                LegacyDomesticEquityMarket.Kospi,
                "삼성전자"));
        Assert.Equal(
            new[] { "SCENE_KOSPI_STOCK", "SCENE_FOREIGN_INDEX" },
            mixedExecutor.Calls.Select(call => call.TableName));
        Assert.NotEmpty(new S8018SceneMutationBuilder().Build(mixed));

        var expectedExecutor = new RecordingExecutor(
            QuoteTable("A", 1_000m, "+", 10m, 1m),
            QuoteTable("B", 2_000m, "-", 20m, -1m));
        var expected = await new S8018SceneDataLoader(expectedExecutor).LoadAsync(
            new S8018ExpectedStockPairLoadRequest(
                LegacyDomesticEquityMarket.Kospi, "A",
                LegacyDomesticEquityMarket.Kosdaq, "B"));
        Assert.Equal(
            new[] { "SCENE_EXPECTED_KOSPI_STOCK", "SCENE_EXPECTED_KOSDAQ_STOCK" },
            expectedExecutor.Calls.Select(call => call.TableName));
        Assert.NotEmpty(new S8018SceneMutationBuilder().Build(expected));

        var currentExecutor = new RecordingExecutor(
            QuoteTable("NXT A", 1_000m, "+", 10m, 1m),
            QuoteTable("NVIDIA", 200.50m, "-", -2.25m, -1.1m));
        var current = await new S8018SceneDataLoader(currentExecutor).LoadAsync(
            new S8018CurrentStockPairLoadRequest(
                S8018SessionPhase.AfterKrxClose,
                new S8018CurrentStockSelection(S8018StockMarket.NxtKospi, "A(NXT)"),
                new S8018CurrentStockSelection(S8018StockMarket.World, "NVIDIA")));
        Assert.Equal(
            new[] { DataSourceKind.MariaDb, DataSourceKind.Oracle },
            currentExecutor.Calls.Select(call => call.Source));
        Assert.Equal(
            new[] { "SCENE_NXT_KOSPI_STOCK", "SCENE_FOREIGN_STOCK" },
            currentExecutor.Calls.Select(call => call.TableName));
        Assert.NotEmpty(new S8018SceneMutationBuilder().Build(current));

        var industryExecutor = new RecordingExecutor(
            QuoteTable("기계", 1m, "+", 1m, 1m),
            QuoteTable("반도체", 2m, "", 0m, 0m));
        var industry = await new S8018SceneDataLoader(industryExecutor).LoadAsync(
            new S8018DomesticIndustryPairLoadRequest(
                LegacyDomesticEquityMarket.Kosdaq, "기계", "반도체"));
        Assert.NotEmpty(new S8018SceneMutationBuilder().Build(industry));

        Assert.All(
            marketExecutor.Calls.Concat(mixedExecutor.Calls).Concat(expectedExecutor.Calls)
                .Concat(currentExecutor.Calls).Concat(industryExecutor.Calls),
            AssertReadOnlyBounded);
    }

    [Fact]
    public async Task S8018_current_mixed_market_and_nxt_stock_uses_the_existing_domestic_dto_safely()
    {
        var executor = new RecordingExecutor(
            QuoteTable("에이비엘바이오", 120_000m, "+", 1_000m, 0.84m),
            QuoteTable("코스피", 2_800m, "-", -10m, -0.36m));

        var data = await new S8018SceneDataLoader(executor).LoadAsync(
            new S8018MixedMarketAndStockLoadRequest(
                S8018PairSide.First,
                LegacyMarketQuoteTarget.Kospi,
                LegacyDomesticEquityMarket.NxtKosdaq,
                "에이비엘바이오",
                S8018MarketDataMode.Current));

        Assert.Equal(
            [DataSourceKind.MariaDb, DataSourceKind.Oracle],
            executor.Calls.Select(call => call.Source));
        Assert.Equal(
            ["SCENE_NXT_KOSDAQ_STOCK", "SCENE_KOSPI_INDEX"],
            executor.Calls.Select(call => call.TableName));
        Assert.All(executor.Calls, AssertReadOnlyBounded);
        Assert.NotEmpty(new S8018SceneMutationBuilder().Build(data));
    }

    [Fact]
    public async Task S8018_expected_mixed_nxt_stock_is_rejected_before_queries()
    {
        var executor = new RecordingExecutor();

        await Assert.ThrowsAsync<LegacySceneDataException>(() =>
            new S8018SceneDataLoader(executor).LoadAsync(
                new S8018MixedMarketAndStockLoadRequest(
                    S8018PairSide.Second,
                    LegacyMarketQuoteTarget.Kospi,
                    LegacyDomesticEquityMarket.NxtKospi,
                    "삼성전자",
                    S8018MarketDataMode.Expected)));

        Assert.Empty(executor.Calls);
    }

    [Fact]
    public async Task S8018_expected_market_mode_is_closed_to_domestic_targets()
    {
        var executor = new RecordingExecutor(
            QuoteTable("코스피 예상", 1m, "+", 1m, 1m),
            QuoteTable("선물 예상", 2m, "-", 1m, -1m));
        var data = await new S8018SceneDataLoader(executor).LoadAsync(
            new S8018MarketPairLoadRequest(
                S8018MarketDataMode.Expected,
                LegacyMarketQuoteTarget.Kospi,
                LegacyMarketQuoteTarget.Futures));

        Assert.Equal(
            new[] { "SCENE_EXPECTED_KOSPI_INDEX", "SCENE_EXPECTED_FUTURES_INDEX" },
            executor.Calls.Select(call => call.TableName));
        Assert.NotEmpty(new S8018SceneMutationBuilder().Build(data));

        var rejected = new RecordingExecutor(QuoteTable("X", 1m, "+", 1m, 1m));
        await Assert.ThrowsAsync<LegacySceneDataException>(() =>
            new S8018SceneDataLoader(rejected).LoadAsync(
                new S8018MarketPairLoadRequest(
                    S8018MarketDataMode.Expected,
                    LegacyMarketQuoteTarget.Nasdaq,
                    LegacyMarketQuoteTarget.Kospi)));
        Assert.Empty(rejected.Calls);
    }

    [Fact]
    public async Task S8018_expected_mixed_pair_queries_market_then_stock_when_stock_is_second()
    {
        var executor = new RecordingExecutor(
            QuoteTable("코스피 예상", 2_800m, "+", 10m, 0.36m),
            QuoteTable("삼성전자", 70_000m, "-", 500m, -0.72m));

        var data = await new S8018SceneDataLoader(executor).LoadAsync(
            new S8018MixedMarketAndStockLoadRequest(
                S8018PairSide.Second,
                LegacyMarketQuoteTarget.Kospi,
                LegacyDomesticEquityMarket.Kospi,
                "삼성전자",
                S8018MarketDataMode.Expected));

        Assert.Equal(
            new[] { "SCENE_EXPECTED_KOSPI_INDEX", "SCENE_EXPECTED_KOSPI_STOCK" },
            executor.Calls.Select(call => call.TableName));
        Assert.NotEmpty(new S8018SceneMutationBuilder().Build(data));
    }

    [Fact]
    public async Task S8001_maps_only_closed_fixed_sector_names_and_preserves_parameter_order()
    {
        var names = new[]
        {
            "통신", "금융", "건설", "기계", "유통", "종이목재", "운수장비",
            "운수창고", "음식료", "철강금속", "의료정밀", "섬유의복", "전기전자",
            "화학", "비금속광물"
        };
        var codes = new[]
        {
            "020", "021", "018", "012", "016", "007", "015", "019",
            "005", "011", "014", "006", "013", "008", "009"
        };
        var table = SectorTable(codes, names);
        var executor = new RecordingExecutor(table);

        var data = await new S8001SceneDataLoader(executor)
            .LoadAsync(new S8001SceneLoadRequest(S8001Market.Kospi));
        var mutations = new S8001SceneMutationBuilder().Build(data);
        var call = Assert.Single(executor.Calls);

        Assert.Equal(DataSourceKind.Oracle, call.Source);
        Assert.Equal("SCENE_8001_KOSPI_SECTORS", call.TableName);
        Assert.Contains("FETCH FIRST 15 ROWS ONLY", call.Spec.Sql, StringComparison.Ordinal);
        Assert.Empty(call.Spec.Parameters);
        Assert.All(codes, code => Assert.Contains($"'{code}'", call.Spec.Sql, StringComparison.Ordinal));
        Assert.Equal(15, data.Sectors.Count);
        Assert.Contains(data.Sectors, row => row.Sector == S8001Sector.Chemicals);
        Assert.Contains(data.Sectors, row => row.Sector == S8001Sector.Pharmaceuticals);
        Assert.NotEmpty(mutations);
        Assert.Equal(0, executor.StringCalls);
    }

    [Fact]
    public async Task S8001_kosdaq_requires_all_six_fixed_targets_and_builds_only_enum_names()
    {
        var names = new[] { "기계장비", "IT부품", "운송", "종이목재", "반도체", "인터넷" };
        var executor = new RecordingExecutor(SectorTable(
            ["001", "002", "003", "004", "005", "006"],
            names));

        var data = await new S8001SceneDataLoader(executor)
            .LoadAsync(new S8001SceneLoadRequest(S8001Market.Kosdaq));
        var call = Assert.Single(executor.Calls);
        var mutations = new S8001SceneMutationBuilder().Build(data);

        Assert.Equal("SCENE_8001_KOSDAQ_SECTORS", call.TableName);
        Assert.Contains("FETCH FIRST 7 ROWS ONLY", call.Spec.Sql, StringComparison.Ordinal);
        Assert.Equal(names, call.Spec.Parameters.Select(parameter => parameter.Value));
        Assert.Equal(6, data.Sectors.Count);
        Assert.Equal(6 + data.Sectors.Count(row => row.Signal != S8001SectorSignal.Flat), mutations.Count);
    }

    [Theory]
    [InlineData(0)]
    [InlineData(2)]
    public async Task Single_quote_loaders_reject_missing_or_ambiguous_rows(int rowCount)
    {
        var table = QuoteTable("A", 1m, "+", 1m, 1m);
        if (rowCount == 0)
        {
            table.Rows.Clear();
        }
        else
        {
            table.ImportRow(table.Rows[0]);
        }

        await Assert.ThrowsAsync<LegacySceneDataException>(() =>
            new S5001SceneDataLoader(new RecordingExecutor(table)).LoadAsync(
                new S5001ForeignStockLoadRequest("A")));
    }

    [Fact]
    public async Task Loaders_fail_closed_for_schema_null_direction_and_invalid_enum_before_mutation()
    {
        var wrongSchema = QuoteTable("A", 1m, "+", 1m, 1m);
        wrongSchema.Columns.Add("EXTRA", typeof(object));
        await Assert.ThrowsAsync<LegacySceneDataException>(() =>
            new S5001SceneDataLoader(new RecordingExecutor(wrongSchema)).LoadAsync(
                new S5001ForeignStockLoadRequest("A")));

        var nullValue = QuoteTable("A", 1m, "+", 1m, 1m);
        nullValue.Rows[0]["PRICE"] = DBNull.Value;
        await Assert.ThrowsAsync<LegacySceneDataException>(() =>
            new S5001SceneDataLoader(new RecordingExecutor(nullValue)).LoadAsync(
                new S5001ForeignStockLoadRequest("A")));

        var unknownDirection = QuoteTable("A", 1m, "UNKNOWN", 1m, 1m);
        await Assert.ThrowsAsync<LegacySceneDataException>(() =>
            new S5001SceneDataLoader(new RecordingExecutor(unknownDirection)).LoadAsync(
                new S5001ForeignStockLoadRequest("A")));

        var invalid = new RecordingExecutor(QuoteTable("A", 1m, "+", 1m, 1m));
        await Assert.ThrowsAsync<LegacySceneDataException>(() =>
            new S5001SceneDataLoader(invalid).LoadAsync(
                new S5001CommodityLoadRequest((S5001CommodityTarget)999)));
        Assert.Empty(invalid.Calls);

        var nullSelector = new RecordingExecutor(QuoteTable("A", 1m, "+", 1m, 1m));
        await Assert.ThrowsAsync<LegacySceneDataException>(() =>
            new S5001SceneDataLoader(nullSelector).LoadAsync(
                new S5001DomesticStockLoadRequest(
                    LegacyDomesticEquityMarket.Kospi,
                    S5001DomesticStockMode.Current,
                    S5001NxtBadge.None,
                    S5001StockTradingStatus.Regular,
                    null!)));
        Assert.Empty(nullSelector.Calls);
    }

    [Fact]
    public async Task Loader_errors_do_not_echo_selected_database_keys()
    {
        const string sensitive = "SENSITIVE-STOCK-SELECTION";
        var exception = await Assert.ThrowsAsync<LegacySceneDataException>(() =>
            new S5001SceneDataLoader(new RecordingExecutor(new DataTable())).LoadAsync(
                new S5001ForeignStockLoadRequest(sensitive)));

        Assert.DoesNotContain(sensitive, exception.Message, StringComparison.Ordinal);
    }

    [Fact]
    public void Public_load_requests_expose_no_raw_sql_symbol_object_method_or_path_fields()
    {
        var requestTypes = typeof(S5001SceneLoadRequest).Assembly.GetTypes()
            .Where(type => type.IsPublic)
            .Where(type => typeof(S5001SceneLoadRequest).IsAssignableFrom(type) ||
                           typeof(S5032SceneLoadRequest).IsAssignableFrom(type) ||
                           typeof(S8018SceneLoadRequest).IsAssignableFrom(type) ||
                           type == typeof(S8001SceneLoadRequest) ||
                           type == typeof(S8018CurrentStockSelection));

        foreach (var property in requestTypes.SelectMany(type => type.GetProperties()))
        {
            Assert.NotEqual(typeof(object), property.PropertyType);
            Assert.DoesNotContain("Sql", property.Name, StringComparison.OrdinalIgnoreCase);
            Assert.DoesNotContain("Query", property.Name, StringComparison.OrdinalIgnoreCase);
            Assert.DoesNotContain("Symbol", property.Name, StringComparison.OrdinalIgnoreCase);
            Assert.DoesNotContain("Object", property.Name, StringComparison.OrdinalIgnoreCase);
            Assert.DoesNotContain("Method", property.Name, StringComparison.OrdinalIgnoreCase);
            Assert.DoesNotContain("Path", property.Name, StringComparison.OrdinalIgnoreCase);
        }
    }

    private static void AssertReadOnlyBounded(RecordedCall call)
    {
        call.Spec.ValidateFor(call.Source);
        Assert.StartsWith("SELECT", call.Spec.Sql.TrimStart(), StringComparison.OrdinalIgnoreCase);
        Assert.True(
            call.Spec.Sql.Contains("FETCH FIRST 2 ROWS ONLY", StringComparison.OrdinalIgnoreCase) ||
            call.Spec.Sql.Contains("LIMIT 2", StringComparison.OrdinalIgnoreCase));
    }

    private static DataTable QuoteTable(
        string title,
        decimal price,
        string direction,
        decimal change,
        decimal rate)
    {
        var table = Table("TITLE", "PRICE", "DIRECTION", "NET_CHANGE", "RATE");
        table.Rows.Add(title, price, direction, change, rate);
        return table;
    }

    private static DataTable FutureTable(
        string title,
        decimal price,
        string direction,
        decimal change,
        decimal rate,
        decimal netVolume,
        decimal outstandingVolume,
        decimal netTurnover)
    {
        var table = Table(
            "TITLE", "PRICE", "DIRECTION", "NET_CHANGE", "RATE",
            "NET_VOLUME", "OUTSTANDING_VOLUME", "NET_TURNOVER");
        table.Rows.Add(
            title, price, direction, change, rate,
            netVolume, outstandingVolume, netTurnover);
        return table;
    }

    private static DataTable SectorTable(
        IReadOnlyList<string> codes,
        IReadOnlyList<string> names)
    {
        Assert.Equal(codes.Count, names.Count);
        var table = Table("SECTOR_CODE", "SECTOR_NAME", "DIRECTION", "MAGNITUDE");
        for (var index = 0; index < names.Count; index++)
        {
            table.Rows.Add(
                codes[index],
                names[index],
                index % 3 == 0 ? "+" : index % 3 == 1 ? "" : "-",
                index / 10m);
        }

        return table;
    }

    private static DataTable Table(params string[] columns)
    {
        var table = new DataTable();
        foreach (var column in columns)
        {
            table.Columns.Add(column, typeof(object));
        }

        return table;
    }

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

    private sealed class RecordingExecutor : IDataQueryExecutor
    {
        private readonly Queue<DataTable> _results;

        public RecordingExecutor(params DataTable[] results)
        {
            _results = new Queue<DataTable>(results);
        }

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

        public int StringCalls { get; private set; }

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

        public Task<DataTable> ExecuteAsync(
            DataSourceKind source,
            string tableName,
            string query,
            CancellationToken cancellationToken = default)
        {
            StringCalls++;
            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);
            Calls.Add(new RecordedCall(source, tableName, query));
            if (_results.Count == 0)
            {
                throw new InvalidOperationException("No fake query result remains.");
            }

            return Task.FromResult(_results.Dequeue().Copy());
        }
    }
}
