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

namespace MBN_STOCK_WEBVIEW.Core.Tests;

public sealed class MarketPanelSceneDataLoadersTests
{
    private static readonly string[] PanelColumns =
    ["ORDINAL", "NAME", "PART_NAME", "PRICE", "CHANGE", "RATE", "SIGNAL"];

    private static readonly string[] QuoteColumns =
    ["TITLE", "PRICE", "CHANGE", "RATE", "SIGNAL"];

    private static readonly string[] WorldColumns =
    ["ORDINAL", "MARKET_CODE", "CURRENT_PRICE", "NET_CHANGE", "RATE", "SIGNAL"];

    public static TheoryData<PanelRouteCase> S5016Cases => new()
    {
        P(S5016PanelTarget.UnitedStatesIndices, "t_world_ix_eq_master", "symbol1", "DJI@DJI"),
        P(S5016PanelTarget.GreaterChinaIndices, "t_world_ix_eq_master", "symbol1", "HSI@HSI"),
        P(S5016PanelTarget.EuropeanIndices, "t_world_ix_eq_master", "symbol1", "LNS@FTSE100"),
        P(S5016PanelTarget.AsianIndices, "t_world_ix_eq_master", "symbol1", "SHS@000001"),
        P(S5016PanelTarget.UnitedStatesTreasuries, "t_world_ix_eq_master", "symbol1", "GVO@TR02Y"),
        P(S5016PanelTarget.BondYields, "t_world_ix_eq_master", "symbol1", "KIR@CMAA"),
        P(S5016PanelTarget.Oil, "t_world_future_master", "symbol1", "SPT@DU"),
        P(S5016PanelTarget.Minerals, "t_world_future_master", "symbol1", "COM@GC"),
        P(S5016PanelTarget.FoodMaterials, "t_world_future_master", "symbol1", "CBT$CORN"),
        P(S5016PanelTarget.OverseasExchangeRates, "t_world_forex_master", "symbol1", "USDJPYCOMP"),
        P(S5016PanelTarget.DomesticExchangeRates, "t_input_index", "symbol1", "A09"),
        P(S5016PanelTarget.AgricultureWheatCorn, "t_world_future_master", "symbol1", "MK@WHEAT"),
        P(S5016PanelTarget.AgricultureSoyRice, "t_world_future_master", "symbol1", "CBT@SOYBEAN"),
        P(S5016PanelTarget.AgricultureCoffeeCocoaSugar, "t_world_future_master", "symbol1", "NYB@KC"),
        P(S5016PanelTarget.AgricultureLivestock, "t_world_future_master", "symbol1", "MK@LC"),
        P(S5016PanelTarget.RawMaterialsCopperIronNickel, "t_world_future_master", "symbol1", "LME@CDY"),
        P(S5016PanelTarget.RawMaterialsGasPlatinumPalladium, "t_world_future_master", "symbol1", "NYM@NG"),
        P(S5016PanelTarget.RawMaterialsLeadZincTin, "t_world_future_master", "symbol1", "MK@PDA"),
        P(S5016PanelTarget.MajorKospiKosdaqKospi200, "FROM t_index", "partCode1", "001"),
        P(S5016PanelTarget.MajorKospiKosdaqFutures, "FROM t_index", "partCode1", "001"),
        P(S5016PanelTarget.ExpectedKospiKosdaqKospi200, "FROM t_fo_index", "partCode1", "001"),
        P(S5016PanelTarget.ExpectedKospiKosdaqFutures, "FROM t_fo_index", "partCode1", "001")
    };

    public static TheoryData<S50160PanelTarget, string> S50160Cases => new()
    {
        { S50160PanelTarget.Cotton, "MK@COTTON" },
        { S50160PanelTarget.InternationalPreciousMetals, "COM@GC" },
        { S50160PanelTarget.DomesticPreciousMetals, "MK@GC" }
    };

    public static TheoryData<S6001RouteCase> S6001Cases => new()
    {
        Q(S6001QuoteTarget.DowJones, "DJI@DJI", "t_world_ix_eq_master"),
        Q(S6001QuoteTarget.Nasdaq, "NAS@IXIC", "t_world_ix_eq_master"),
        Q(S6001QuoteTarget.StandardAndPoor500, "SPI@SPX", "t_world_ix_eq_master"),
        Q(S6001QuoteTarget.Germany, "XTR@DAX30", "t_world_ix_eq_master"),
        Q(S6001QuoteTarget.UnitedKingdom, "LNS@FTSE100", "t_world_ix_eq_master"),
        Q(S6001QuoteTarget.France, "PAS@CAC40", "t_world_ix_eq_master"),
        Q(S6001QuoteTarget.Japan, "NII@NI225", "t_world_ix_eq_master"),
        Q(S6001QuoteTarget.China, "SHS@000001", "t_world_ix_eq_master"),
        Q(S6001QuoteTarget.HongKong, "HSI@HSI", "t_world_ix_eq_master"),
        Q(S6001QuoteTarget.Taiwan, "TWS@TI01", "t_world_ix_eq_master"),
        Q(S6001QuoteTarget.Singapore, "SGI@STI", "t_world_ix_eq_master"),
        Q(S6001QuoteTarget.Thailand, "THI@SET", "t_world_ix_eq_master"),
        Q(S6001QuoteTarget.Philippines, "PHI@COMP", "t_world_ix_eq_master"),
        Q(S6001QuoteTarget.Malaysia, "IDI@JKSE", "t_world_ix_eq_master"),
        Q(S6001QuoteTarget.Indonesia, "MYI@KLSE", "t_world_ix_eq_master"),
        Q(S6001QuoteTarget.DubaiCrude, "SPT@DU", "t_world_future_master"),
        Q(S6001QuoteTarget.BrentCrude, "SPT@EB", "t_world_future_master"),
        Q(S6001QuoteTarget.WtiCrude, "SPT@CL", "t_world_future_master"),
        Q(S6001QuoteTarget.Gold, "COM@GC", "t_world_future_master")
    };

    public static TheoryData<S8086DiagnosticTarget, string, CommodityUnit> S8086Cases => new()
    {
        { S8086DiagnosticTarget.DubaiCrude, "SPT@DU", CommodityUnit.DollarsPerBarrel },
        { S8086DiagnosticTarget.BrentCrude, "SPT@EB", CommodityUnit.DollarsPerBarrel },
        { S8086DiagnosticTarget.WtiCrude, "SPT@CL", CommodityUnit.DollarsPerBarrel },
        { S8086DiagnosticTarget.Gold, "COM@GC", CommodityUnit.DollarsPerOunce },
        { S8086DiagnosticTarget.Silver, "COM@SI", CommodityUnit.DollarsPerOunce },
        { S8086DiagnosticTarget.Copper, "LME@CDY", CommodityUnit.DollarsPerOunce }
    };

    [Theory]
    [MemberData(nameof(S5016Cases))]
    public async Task S5016_all_closed_targets_use_ordered_parameterized_three_row_queries(
        PanelRouteCase item)
    {
        var executor = new RecordingExecutor(PanelTable(3));

        var data = await new S5016SceneDataLoader(executor).LoadAsync(
            item.Target,
            CancellationToken.None);

        var call = Assert.Single(executor.Calls);
        Assert.Equal(DataSourceKind.Oracle, call.Source);
        Assert.Equal("S5016_PANEL", call.TableName);
        Assert.Contains(item.SourceFragment, call.Spec.Sql, StringComparison.OrdinalIgnoreCase);
        Assert.Contains("ORDER BY ORDINAL", call.Spec.Sql, StringComparison.OrdinalIgnoreCase);
        Assert.Contains(call.Spec.Parameters, parameter =>
            parameter.Name == item.ParameterName && Equals(parameter.Value, item.ParameterValue));
        Assert.All(call.Spec.Parameters, parameter => Assert.Equal(DbType.String, parameter.DbType));
        Assert.Equal(["Name1", "Name2", "Name3"], data.Quotes.Select(quote => quote.Name));
        Assert.Equal(0, executor.StringCalls);

        var mutations = new S5016SceneMutationBuilder().Build(data);
        Assert.Contains(mutations, mutation =>
            mutation is PlayoutSetValue { ObjectName: "title3" });
    }

    [Theory]
    [MemberData(nameof(S50160Cases))]
    public async Task S50160_targets_bind_source_symbols_and_require_two_ordered_rows(
        S50160PanelTarget target,
        string firstSymbol)
    {
        var executor = new RecordingExecutor(PanelTable(2));

        var data = await new S50160SceneDataLoader(executor).LoadAsync(
            target,
            CancellationToken.None);

        var call = Assert.Single(executor.Calls);
        Assert.Equal("S50160_PANEL", call.TableName);
        Assert.Contains("t_world_future_master", call.Spec.Sql, StringComparison.OrdinalIgnoreCase);
        Assert.Contains(call.Spec.Parameters, parameter =>
            parameter.Name == "symbol1" && Equals(parameter.Value, firstSymbol));
        Assert.Equal(2, data.Quotes.Count);
        Assert.NotEmpty(new S50160SceneMutationBuilder().Build(data));
    }

    [Theory]
    [MemberData(nameof(S6001Cases))]
    public async Task S6001_target_selects_one_bound_symbol_and_only_builder_owned_relative_asset(
        S6001RouteCase item)
    {
        var executor = new RecordingExecutor(QuoteTable());

        var data = await new S6001SceneDataLoader(executor).LoadAsync(
            item.Target,
            CancellationToken.None);

        var call = Assert.Single(executor.Calls);
        Assert.Equal(DataSourceKind.Oracle, call.Source);
        Assert.Equal("S6001_QUOTE", call.TableName);
        Assert.Contains(item.SourceFragment, call.Spec.Sql, StringComparison.OrdinalIgnoreCase);
        var parameter = Assert.Single(call.Spec.Parameters);
        Assert.Equal("symbol", parameter.Name);
        Assert.Equal(item.Symbol, parameter.Value);
        Assert.DoesNotContain(item.Symbol, call.Spec.Sql, StringComparison.Ordinal);

        var assets = new S6001SceneMutationBuilder().Build(data)
            .OfType<PlayoutSetAssetValue>()
            .ToArray();
        Assert.Contains(assets, asset => asset.ObjectName == "movie");
        Assert.All(assets, asset =>
        {
            Assert.False(Path.IsPathRooted(asset.AssetPath));
            Assert.DoesNotContain(':', asset.AssetPath);
            Assert.DoesNotContain("..", asset.AssetPath, StringComparison.Ordinal);
        });
        Assert.DoesNotContain(
            typeof(S6001SceneDataLoader).GetMethods().SelectMany(method => method.GetParameters()),
            parameterInfo => parameterInfo.Name?.Contains("path", StringComparison.OrdinalIgnoreCase) == true);
    }

    [Theory]
    [MemberData(nameof(S8086Cases))]
    public async Task S8086_diagnostic_loader_is_closed_source_only_and_has_no_cut_alias(
        S8086DiagnosticTarget target,
        string symbol,
        CommodityUnit unit)
    {
        var executor = new RecordingExecutor(QuoteTable());

        var data = await new S8086DiagnosticSceneDataLoader(executor).LoadAsync(
            target,
            CancellationToken.None);

        Assert.Equal(unit, data.Unit);
        var call = Assert.Single(executor.Calls);
        Assert.Equal("S8086_DIAGNOSTIC", call.TableName);
        Assert.Equal(symbol, Assert.Single(call.Spec.Parameters).Value);
        Assert.NotEmpty(new S8086SceneMutationBuilder().Build(data));

        var definition = Assert.Single(
            LegacySceneCatalog.Definitions,
            item => item.BuilderKey == "s8086");
        Assert.Empty(definition.CutAliases);
    }

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

        var data = await new S8067SceneDataLoader(executor).LoadAsync();

        var call = Assert.Single(executor.Calls);
        Assert.Equal(DataSourceKind.Oracle, call.Source);
        Assert.Equal("S8067_WORLD_MAP", call.TableName);
        Assert.Equal(11, call.Spec.Parameters.Count);
        Assert.Contains("UNION ALL", call.Spec.Sql, StringComparison.OrdinalIgnoreCase);
        Assert.Contains("ORDER BY ORDINAL", call.Spec.Sql, StringComparison.OrdinalIgnoreCase);
        Assert.Equal(Enum.GetValues<WorldMarket>(), data.Quotes.Select(quote => quote.Market));
        Assert.NotEmpty(new S8067SceneMutationBuilder().Build(data));
    }

    [Fact]
    public async Task Panel_schemas_counts_ordinals_and_signals_fail_closed()
    {
        var shortPanel = PanelTable(2);
        await Assert.ThrowsAsync<LegacySceneDataException>(() =>
            new S5016SceneDataLoader(new RecordingExecutor(shortPanel)).LoadAsync(
                S5016PanelTarget.Oil,
                CancellationToken.None));

        var wrongOrder = PanelTable(3);
        wrongOrder.Rows[1]["ORDINAL"] = 3;
        await Assert.ThrowsAsync<LegacySceneDataException>(() =>
            new S5016SceneDataLoader(new RecordingExecutor(wrongOrder)).LoadAsync(
                S5016PanelTarget.Oil,
                CancellationToken.None));

        var unknownSignal = QuoteTable();
        unknownSignal.Rows[0]["SIGNAL"] = "UNKNOWN";
        await Assert.ThrowsAsync<LegacySceneDataException>(() =>
            new S6001SceneDataLoader(new RecordingExecutor(unknownSignal)).LoadAsync(
                S6001QuoteTarget.Gold,
                CancellationToken.None));

        var reordered = Table(
            ["NAME", "ORDINAL", "PART_NAME", "PRICE", "CHANGE", "RATE", "SIGNAL"]);
        await Assert.ThrowsAsync<LegacySceneDataException>(() =>
            new S50160SceneDataLoader(new RecordingExecutor(reordered)).LoadAsync(
                S50160PanelTarget.Cotton,
                CancellationToken.None));
    }

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

        await Assert.ThrowsAsync<LegacySceneDataException>(() =>
            new S5016SceneDataLoader(executor).LoadAsync((S5016PanelTarget)999));
        await Assert.ThrowsAsync<LegacySceneDataException>(() =>
            new S50160SceneDataLoader(executor).LoadAsync((S50160PanelTarget)999));
        await Assert.ThrowsAsync<LegacySceneDataException>(() =>
            new S6001SceneDataLoader(executor).LoadAsync((S6001QuoteTarget)999));
        await Assert.ThrowsAsync<LegacySceneDataException>(() =>
            new S8086DiagnosticSceneDataLoader(executor).LoadAsync((S8086DiagnosticTarget)999));

        Assert.Empty(executor.Calls);
    }

    private static PanelRouteCase P(
        S5016PanelTarget target,
        string sourceFragment,
        string parameterName,
        string parameterValue) =>
        new(target, sourceFragment, parameterName, parameterValue);

    private static S6001RouteCase Q(
        S6001QuoteTarget target,
        string symbol,
        string sourceFragment) =>
        new(target, symbol, sourceFragment);

    private static DataTable PanelTable(int count)
    {
        var table = Table(PanelColumns);
        for (var index = 1; index <= count; index++)
        {
            table.Rows.Add(
                index,
                "Name" + index,
                "Part" + index,
                100m + index,
                index == 2 ? -2m : index,
                index == 2 ? -1.5m : index,
                index switch { 1 => "+", 2 => "-", _ => "" });
        }

        return table;
    }

    private static DataTable QuoteTable() => Table(
        QuoteColumns,
        ["Quote", 100.25m, -1.5m, -1.48m, "-"]);

    private static DataTable WorldTable()
    {
        string[] codes =
        [
            "KOREA", "CHINA", "HONG_KONG", "JAPAN", "TAIWAN", "DOW",
            "NASDAQ", "SP", "UNITED_KINGDOM", "GERMANY", "FRANCE"
        ];
        var table = Table(WorldColumns);
        for (var index = 0; index < codes.Length; index++)
        {
            table.Rows.Add(
                index + 1,
                codes[index],
                100d + index,
                index == 2 ? 0d : 1d,
                1d,
                index == 2 ? "-" : "+");
        }

        return table;
    }

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

        if (row is not null)
        {
            table.Rows.Add(row);
        }

        return table;
    }

    public sealed record PanelRouteCase(
        S5016PanelTarget Target,
        string SourceFragment,
        string ParameterName,
        string ParameterValue);

    public sealed record S6001RouteCase(
        S6001QuoteTarget Target,
        string Symbol,
        string SourceFragment);

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

    private sealed class RecordingExecutor(params DataTable[] results) : IDataQueryExecutor
    {
        private readonly Queue<DataTable> _results = new(results);

        public List<QueryCall> 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 QueryCall(source, tableName, query));
            if (_results.Count == 0)
            {
                throw new InvalidOperationException("No result was configured.");
            }

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