#nullable enable

using System.Data;
using MMoneyCoderSharp.Data;

namespace MBN_STOCK_WEBVIEW.Core.Playout.Scenes;

public sealed class S5016SceneDataLoader
{
    private readonly IDataQueryExecutor _executor;

    public S5016SceneDataLoader(IDataQueryExecutor executor)
    {
        _executor = executor ?? throw new ArgumentNullException(nameof(executor));
    }

    public async Task<S5016SceneData> LoadAsync(
        S5016PanelTarget target,
        CancellationToken cancellationToken = default)
    {
        if (!Enum.IsDefined(target))
        {
            throw EquityPanelLoaderReader.InvalidRequest();
        }

        var spec = PanelQuoteQueryFactory.Create(target);
        var table = await _executor.ExecuteAsync(
            DataSourceKind.Oracle,
            "S5016_PANEL",
            spec,
            cancellationToken).ConfigureAwait(false);
        var data = new S5016SceneData(target, PanelQuoteTableMapper.Map(table, 3));
        _ = new S5016SceneMutationBuilder().Build(data);
        return data;
    }
}

public sealed class S50160SceneDataLoader
{
    private readonly IDataQueryExecutor _executor;

    public S50160SceneDataLoader(IDataQueryExecutor executor)
    {
        _executor = executor ?? throw new ArgumentNullException(nameof(executor));
    }

    public async Task<S50160SceneData> LoadAsync(
        S50160PanelTarget target,
        CancellationToken cancellationToken = default)
    {
        if (!Enum.IsDefined(target))
        {
            throw EquityPanelLoaderReader.InvalidRequest();
        }

        var spec = PanelQuoteQueryFactory.Create(target);
        var table = await _executor.ExecuteAsync(
            DataSourceKind.Oracle,
            "S50160_PANEL",
            spec,
            cancellationToken).ConfigureAwait(false);
        var data = new S50160SceneData(target, PanelQuoteTableMapper.Map(table, 2));
        _ = new S50160SceneMutationBuilder().Build(data);
        return data;
    }
}

internal static class PanelQuoteQueryFactory
{
    public static DataQuerySpec Create(S5016PanelTarget target) =>
        Create(Profile(target));

    public static DataQuerySpec Create(S50160PanelTarget target) =>
        Create(Profile(target));

    private static DataQuerySpec Create(PanelProfile profile) => profile.Source switch
    {
        PanelSource.WorldIndex => World(profile, "t_world_ix_eq_master", "t_world_ix_eq_sise", "b.f_sign"),
        PanelSource.WorldFuture => World(
            profile,
            "t_world_future_master",
            "t_world_future_sise",
            "CASE WHEN TO_NUMBER(b.f_diff) > 0 THEN '+' " +
            "WHEN TO_NUMBER(b.f_diff) < 0 THEN '-' ELSE ' ' END"),
        PanelSource.WorldForex => World(
            profile,
            "t_world_forex_master",
            "t_world_forex_sise",
            "CASE WHEN TO_NUMBER(b.f_diff) > 0 THEN '+' " +
            "WHEN TO_NUMBER(b.f_diff) < 0 THEN '-' ELSE ' ' END"),
        PanelSource.DomesticExchange => DomesticExchange(profile),
        PanelSource.MajorIndex200 => Major(forecast: false, futures: false),
        PanelSource.MajorIndexFutures => Major(forecast: false, futures: true),
        PanelSource.ExpectedIndex200 => Major(forecast: true, futures: false),
        PanelSource.ExpectedIndexFutures => Major(forecast: true, futures: true),
        _ => throw EquityPanelLoaderReader.InvalidRequest()
    };

    private static DataQuerySpec World(
        PanelProfile profile,
        string masterTable,
        string quoteTable,
        string signalExpression)
    {
        var selects = profile.Symbols.Select((symbol, zeroBased) =>
        {
            var index = zeroBased + 1;
            return $"""
                SELECT
                    {index} ORDINAL,
                    a.f_input_name NAME,
                    a.f_input_name PART_NAME,
                    ROUND(b.f_last, 4) PRICE,
                    ROUND(b.f_diff, 4) CHANGE,
                    ROUND(b.f_rate, 4) RATE,
                    {signalExpression} SIGNAL
                FROM {masterTable} a, {quoteTable} b
                WHERE a.f_symb = b.f_symb
                  AND a.f_symb = :symbol{index}
                """;
        });
        var sql = string.Join("\nUNION ALL\n", selects) + "\nORDER BY ORDINAL";
        var parameters = profile.Symbols
            .Select((symbol, index) => new DataQueryParameter(
                "symbol" + (index + 1),
                symbol,
                DbType.String))
            .ToArray();
        return EquityPanelLoaderReader.Spec(DataSourceKind.Oracle, sql, parameters);
    }

    private static DataQuerySpec DomesticExchange(PanelProfile profile)
    {
        var selects = profile.Symbols.Select((symbol, zeroBased) =>
        {
            var index = zeroBased + 1;
            return $"""
                SELECT
                    {index} ORDINAL,
                    :display{index} NAME,
                    CAST(NULL AS VARCHAR2(256)) PART_NAME,
                    a.f_curr_price PRICE,
                    a.f_net_chg CHANGE,
                    ROUND(a.f_net_chg / DECODE(
                        a.f_chg_type,
                        '+', (a.f_curr_price / 100) - (ABS(a.f_net_chg) / 100),
                        '-', (a.f_curr_price / 100) + (a.f_net_chg / 100),
                        1), 2) RATE,
                    a.f_chg_type SIGNAL
                FROM t_input_index a, t_input_code b
                WHERE a.f_input_code = b.f_input_code
                  AND a.f_input_code = :symbol{index}
                """;
        });
        var sql = string.Join("\nUNION ALL\n", selects) + "\nORDER BY ORDINAL";
        var parameters = new List<DataQueryParameter>(profile.Symbols.Count * 2);
        for (var index = 0; index < profile.Symbols.Count; index++)
        {
            parameters.Add(new DataQueryParameter(
                "display" + (index + 1),
                profile.DisplayNames![index],
                DbType.String));
            parameters.Add(new DataQueryParameter(
                "symbol" + (index + 1),
                profile.Symbols[index],
                DbType.String));
        }

        return EquityPanelLoaderReader.Spec(
            DataSourceKind.Oracle,
            sql,
            parameters.ToArray());
    }

    private static DataQuerySpec Major(bool forecast, bool futures)
    {
        var firstTable = forecast ? "t_fo_index" : "t_index";
        var secondTable = forecast ? "T_KOSDAQ_FO_INDEX" : "T_KOSDAQ_INDEX";
        var thirdTable = forecast ? "T_200_FO_INDEX" : "T_200_INDEX";
        var third = futures
            ? $"""
                SELECT
                    3 ORDINAL,
                    :display3 NAME,
                    CAST(NULL AS VARCHAR2(256)) PART_NAME,
                    part_idx PRICE,
                    part_chg CHANGE,
                    ROUND(part_chg / DECODE(
                        chg_type,
                        '+', (part_idx / 100) - (part_chg / 100),
                        '-', (part_idx / 100) + (part_chg / 100),
                        1), 2) RATE,
                    chg_type SIGNAL
                FROM (
                    SELECT
                        DECODE(
                            a.{(forecast ? "f_fo_price" : "f_curr_price")},
                            0,
                            {(forecast ? "b.F_JUN_LAST_PRICE" : "b.F_JUN_LAST_PRICE / 100")},
                            {(forecast ? "a.f_fo_price" : "a.f_curr_price / 100")}) part_idx,
                        DECODE(
                            a.{(forecast ? "f_fo_price" : "f_curr_price")},
                            0,
                            0,
                            (a.{(forecast ? "f_fo_price" : "f_curr_price")} -
                                {(forecast ? "b.F_JUN_LAST_PRICE" : "b.F_JUN_LAST_PRICE * 100")}) / 100) part_chg,
                        DECODE(
                            a.{(forecast ? "f_fo_price" : "f_curr_price")},
                            0,
                            ' ',
                            DECODE(SIGN(
                                a.{(forecast ? "f_fo_price" : "f_curr_price")} -
                                {(forecast ? "b.F_JUN_LAST_PRICE" : "b.F_JUN_LAST_PRICE * 100")}),
                                1, '+', -1, '-', 0, ' ')) chg_type
                    FROM t_sunmul_online a, t_sunmul_batch b
                    WHERE a.f_stock_code = b.f_stock_code
                      AND a.f_stock_seq = 1
                      AND b.f_market_date = (SELECT MAX(OPEN_DAY) FROM v_open_day)
                      AND b.F_MONTH_GUBUN = '1'
                )
                """
            : $"""
                SELECT
                    3 ORDINAL,
                    :display3 NAME,
                    CAST(NULL AS VARCHAR2(256)) PART_NAME,
                    f_part_idx / 100 PRICE,
                    f_part_chg / 100 CHANGE,
                    ROUND((f_part_chg / DECODE(
                        f_chg_type,
                        '+', f_part_idx - f_part_chg,
                        '-', f_part_idx + f_part_chg,
                        1)) * 100, 2) RATE,
                    f_chg_type SIGNAL
                FROM {thirdTable}
                WHERE f_part_code = :partCode3
                """;
        var sql = $"""
            SELECT
                1 ORDINAL,
                :display1 NAME,
                CAST(NULL AS VARCHAR2(256)) PART_NAME,
                f_part_idx / 100 PRICE,
                f_part_chg / 100 CHANGE,
                ROUND((f_part_chg / DECODE(
                    f_chg_type,
                    '+', f_part_idx - f_part_chg,
                    '-', f_part_idx + f_part_chg,
                    1)) * 100, 2) RATE,
                f_chg_type SIGNAL
            FROM {firstTable}
            WHERE f_part_code = :partCode1
            UNION ALL
            SELECT
                2 ORDINAL,
                :display2 NAME,
                CAST(NULL AS VARCHAR2(256)) PART_NAME,
                f_part_idx / 100 PRICE,
                f_part_chg / 100 CHANGE,
                ROUND((f_part_chg / DECODE(
                    f_chg_type,
                    '+', f_part_idx - f_part_chg,
                    '-', f_part_idx + f_part_chg,
                    1)) * 100, 2) RATE,
                f_chg_type SIGNAL
            FROM {secondTable}
            WHERE f_part_code = :partCode2
            UNION ALL
            {third}
            ORDER BY ORDINAL
            """;
        var parameters = new List<DataQueryParameter>
        {
            new("display1", "KOSPI", DbType.String),
            new("partCode1", "001", DbType.String),
            new("display2", "KOSQ", DbType.String),
            new("partCode2", "001", DbType.String),
            new("display3", futures ? "\uC120\uBB3C" : "KOSPI200", DbType.String)
        };
        if (!futures)
        {
            parameters.Add(new DataQueryParameter("partCode3", "029", DbType.String));
        }

        return EquityPanelLoaderReader.Spec(
            DataSourceKind.Oracle,
            sql,
            parameters.ToArray());
    }

    private static PanelProfile Profile(S5016PanelTarget target) => target switch
    {
        S5016PanelTarget.UnitedStatesIndices => WorldIndex("DJI@DJI", "NAS@IXIC", "SPI@SPX"),
        S5016PanelTarget.GreaterChinaIndices => WorldIndex("HSI@HSI", "SHS@000001", "TWS@TI01"),
        S5016PanelTarget.EuropeanIndices => WorldIndex("LNS@FTSE100", "PAS@CAC40", "XTR@DAX30"),
        S5016PanelTarget.AsianIndices => WorldIndex("SHS@000001", "TWS@TI01", "NII@NI225"),
        S5016PanelTarget.UnitedStatesTreasuries => WorldIndex("GVO@TR02Y", "GVO@TR03Y", "GVO@TR05Y"),
        S5016PanelTarget.BondYields => WorldIndex("KIR@CMAA", "KIR@NB03Y", "KIR@CD91"),
        S5016PanelTarget.Oil => WorldFuture("SPT@DU", "SPT@EB", "SPT@CL"),
        S5016PanelTarget.Minerals => WorldFuture("COM@GC", "COM@SI", "LME@CDY"),
        S5016PanelTarget.FoodMaterials => WorldFuture("CBT$CORN", "CBT@SOYBEAN", "CBT@WHEAT"),
        S5016PanelTarget.OverseasExchangeRates => new(
            PanelSource.WorldForex,
            ["USDJPYCOMP", "EURUSDCOMP", "USDCNYCOMP"]),
        S5016PanelTarget.DomesticExchangeRates => new(
            PanelSource.DomesticExchange,
            ["A09", "A10", "A91"],
            ["\uC6D0/\uB2EC\uB7EC", "\uC6D0/\uC5D4", "\uC6D0/\uC720\uB85C"]),
        S5016PanelTarget.AgricultureWheatCorn => WorldFuture("MK@WHEAT", "CBT$CORN", "CBT@WHEAT"),
        S5016PanelTarget.AgricultureSoyRice => WorldFuture("CBT@SOYBEAN", "MK@BEAN", "MK@RICE"),
        S5016PanelTarget.AgricultureCoffeeCocoaSugar => WorldFuture("NYB@KC", "MK@COCOA", "NYB@SB"),
        S5016PanelTarget.AgricultureLivestock => WorldFuture("MK@LC", "MK@FC", "MK@PORK"),
        S5016PanelTarget.RawMaterialsCopperIronNickel => WorldFuture("LME@CDY", "MK@IRON", "LME@NDY"),
        S5016PanelTarget.RawMaterialsGasPlatinumPalladium => WorldFuture("NYM@NG", "NYM@PL", "NYM@PA"),
        S5016PanelTarget.RawMaterialsLeadZincTin => WorldFuture("MK@PDA", "MK@ZDA", "LME@SDY"),
        S5016PanelTarget.MajorKospiKosdaqKospi200 => new(PanelSource.MajorIndex200, []),
        S5016PanelTarget.MajorKospiKosdaqFutures => new(PanelSource.MajorIndexFutures, []),
        S5016PanelTarget.ExpectedKospiKosdaqKospi200 => new(PanelSource.ExpectedIndex200, []),
        S5016PanelTarget.ExpectedKospiKosdaqFutures => new(PanelSource.ExpectedIndexFutures, []),
        _ => throw EquityPanelLoaderReader.InvalidRequest()
    };

    private static PanelProfile Profile(S50160PanelTarget target) => target switch
    {
        S50160PanelTarget.Cotton => WorldFuture("MK@COTTON", "NYB@CT"),
        S50160PanelTarget.InternationalPreciousMetals => WorldFuture("COM@GC", "COM@SI"),
        S50160PanelTarget.DomesticPreciousMetals => WorldFuture("MK@GC", "MK@SI"),
        _ => throw EquityPanelLoaderReader.InvalidRequest()
    };

    private static PanelProfile WorldIndex(params string[] symbols) =>
        new(PanelSource.WorldIndex, symbols);

    private static PanelProfile WorldFuture(params string[] symbols) =>
        new(PanelSource.WorldFuture, symbols);

    private enum PanelSource
    {
        WorldIndex,
        WorldFuture,
        WorldForex,
        DomesticExchange,
        MajorIndex200,
        MajorIndexFutures,
        ExpectedIndex200,
        ExpectedIndexFutures
    }

    private sealed record PanelProfile(
        PanelSource Source,
        IReadOnlyList<string> Symbols,
        IReadOnlyList<string>? DisplayNames = null);
}

internal static class PanelQuoteTableMapper
{
    private static readonly string[] Columns =
    ["ORDINAL", "NAME", "PART_NAME", "PRICE", "CHANGE", "RATE", "SIGNAL"];

    public static IReadOnlyList<LegacyPanelQuoteData> Map(DataTable? table, int expectedRows)
    {
        EquityPanelLoaderReader.ExactSchema(table, Columns);
        if (table!.Rows.Count != expectedRows)
        {
            throw EquityPanelLoaderReader.InvalidResult();
        }

        var result = new LegacyPanelQuoteData[expectedRows];
        for (var index = 0; index < expectedRows; index++)
        {
            var row = table.Rows[index];
            if (EquityPanelLoaderReader.Int64(row, "ORDINAL") != index + 1)
            {
                throw EquityPanelLoaderReader.InvalidResult();
            }

            var change = EquityPanelLoaderReader.Decimal(row, "CHANGE");
            result[index] = new LegacyPanelQuoteData(
                EquityPanelLoaderReader.Text(row, "NAME"),
                row["PART_NAME"] is DBNull
                    ? null
                    : EquityPanelLoaderReader.Text(row, "PART_NAME"),
                EquityPanelLoaderReader.Decimal(row, "PRICE"),
                change,
                EquityPanelLoaderReader.Decimal(row, "RATE"),
                Signal(EquityPanelLoaderReader.Text(row, "SIGNAL", allowNull: true)));
        }

        return result;
    }

    private static LegacyPanelQuoteSignal Signal(string value) => value switch
    {
        "+" or "1" or "2" => LegacyPanelQuoteSignal.Positive,
        "-" or "4" or "5" => LegacyPanelQuoteSignal.Negative,
        "" or "3" => LegacyPanelQuoteSignal.Flat,
        _ => throw EquityPanelLoaderReader.InvalidResult()
    };
}

public sealed class S6001SceneDataLoader
{
    private static readonly string[] Columns =
    ["TITLE", "PRICE", "CHANGE", "RATE", "SIGNAL"];

    private readonly IDataQueryExecutor _executor;

    public S6001SceneDataLoader(IDataQueryExecutor executor)
    {
        _executor = executor ?? throw new ArgumentNullException(nameof(executor));
    }

    public async Task<S6001SceneData> LoadAsync(
        S6001QuoteTarget target,
        CancellationToken cancellationToken = default)
    {
        if (!Enum.IsDefined(target))
        {
            throw EquityPanelLoaderReader.InvalidRequest();
        }

        var profile = CommodityQuoteQueryFactory.S6001(target);
        var spec = CommodityQuoteQueryFactory.Create(profile);
        var table = await _executor.ExecuteAsync(
            DataSourceKind.Oracle,
            "S6001_QUOTE",
            spec,
            cancellationToken).ConfigureAwait(false);
        var row = EquityPanelLoaderReader.SingleRow(table, Columns);
        var data = new S6001SceneData(
            target,
            EquityPanelLoaderReader.Text(row, "TITLE"),
            EquityPanelLoaderReader.Decimal(row, "PRICE"),
            EquityPanelLoaderReader.Decimal(row, "CHANGE"),
            EquityPanelLoaderReader.Decimal(row, "RATE"),
            EquityPanelLoaderReader.Direction(row, "SIGNAL"));
        _ = new S6001SceneMutationBuilder().Build(data);
        return data;
    }
}

public enum S8086DiagnosticTarget
{
    DubaiCrude,
    BrentCrude,
    WtiCrude,
    Gold,
    Silver,
    Copper
}

public sealed class S8086DiagnosticSceneDataLoader
{
    private static readonly string[] Columns =
    ["TITLE", "PRICE", "CHANGE", "RATE", "SIGNAL"];

    private readonly IDataQueryExecutor _executor;

    public S8086DiagnosticSceneDataLoader(IDataQueryExecutor executor)
    {
        _executor = executor ?? throw new ArgumentNullException(nameof(executor));
    }

    public async Task<S8086SceneData> LoadAsync(
        S8086DiagnosticTarget target,
        CancellationToken cancellationToken = default)
    {
        if (!Enum.IsDefined(target))
        {
            throw EquityPanelLoaderReader.InvalidRequest();
        }

        var profile = CommodityQuoteQueryFactory.S8086(target);
        var spec = CommodityQuoteQueryFactory.Create(profile);
        var table = await _executor.ExecuteAsync(
            DataSourceKind.Oracle,
            "S8086_DIAGNOSTIC",
            spec,
            cancellationToken).ConfigureAwait(false);
        var row = EquityPanelLoaderReader.SingleRow(table, Columns);
        var data = new S8086SceneData(
            EquityPanelLoaderReader.Text(row, "TITLE"),
            profile.Unit,
            EquityPanelLoaderReader.Double(row, "PRICE"),
            EquityPanelLoaderReader.Double(row, "CHANGE"),
            EquityPanelLoaderReader.Double(row, "RATE"),
            CommodityQuoteQueryFactory.Trend(
                EquityPanelLoaderReader.Text(row, "SIGNAL", allowNull: true)));
        _ = new S8086SceneMutationBuilder().Build(data);
        return data;
    }
}

internal static class CommodityQuoteQueryFactory
{
    public static QuoteProfile S6001(S6001QuoteTarget target) => target switch
    {
        S6001QuoteTarget.DowJones => Index("DJI@DJI"),
        S6001QuoteTarget.Nasdaq => Index("NAS@IXIC"),
        S6001QuoteTarget.StandardAndPoor500 => Index("SPI@SPX"),
        S6001QuoteTarget.Germany => Index("XTR@DAX30"),
        S6001QuoteTarget.UnitedKingdom => Index("LNS@FTSE100"),
        S6001QuoteTarget.France => Index("PAS@CAC40"),
        S6001QuoteTarget.Japan => Index("NII@NI225"),
        S6001QuoteTarget.China => Index("SHS@000001"),
        S6001QuoteTarget.HongKong => Index("HSI@HSI"),
        S6001QuoteTarget.Taiwan => Index("TWS@TI01"),
        S6001QuoteTarget.Singapore => Index("SGI@STI"),
        S6001QuoteTarget.Thailand => Index("THI@SET"),
        S6001QuoteTarget.Philippines => Index("PHI@COMP"),
        // Preserve the legacy source selections, including its Malaysia/Indonesia swap.
        S6001QuoteTarget.Malaysia => Index("IDI@JKSE"),
        S6001QuoteTarget.Indonesia => Index("MYI@KLSE"),
        S6001QuoteTarget.DubaiCrude => Future("SPT@DU", CommodityUnit.DollarsPerBarrel),
        S6001QuoteTarget.BrentCrude => Future("SPT@EB", CommodityUnit.DollarsPerBarrel),
        S6001QuoteTarget.WtiCrude => Future("SPT@CL", CommodityUnit.DollarsPerBarrel),
        S6001QuoteTarget.Gold => Future(
            "COM@GC",
            CommodityUnit.DollarsPerOunce,
            absoluteValues: true),
        _ => throw EquityPanelLoaderReader.InvalidRequest()
    };

    public static QuoteProfile S8086(S8086DiagnosticTarget target) => target switch
    {
        S8086DiagnosticTarget.DubaiCrude => Future("SPT@DU", CommodityUnit.DollarsPerBarrel),
        S8086DiagnosticTarget.BrentCrude => Future("SPT@EB", CommodityUnit.DollarsPerBarrel),
        S8086DiagnosticTarget.WtiCrude => Future("SPT@CL", CommodityUnit.DollarsPerBarrel),
        S8086DiagnosticTarget.Gold => Future(
            "COM@GC", CommodityUnit.DollarsPerOunce, absoluteValues: true),
        S8086DiagnosticTarget.Silver => Future(
            "COM@SI", CommodityUnit.DollarsPerOunce, absoluteValues: true),
        S8086DiagnosticTarget.Copper => Future(
            "LME@CDY", CommodityUnit.DollarsPerOunce, absoluteValues: true),
        _ => throw EquityPanelLoaderReader.InvalidRequest()
    };

    public static DataQuerySpec Create(QuoteProfile profile)
    {
        var (master, quote, signal) = profile.Source switch
        {
            QuoteSource.WorldIndex => (
                "t_world_ix_eq_master",
                "t_world_ix_eq_sise",
                "b.f_sign"),
            QuoteSource.WorldFuture => (
                "t_world_future_master",
                "t_world_future_sise",
                "CASE WHEN TO_NUMBER(b.f_diff) > 0 THEN '+' " +
                "WHEN TO_NUMBER(b.f_diff) < 0 THEN '-' ELSE ' ' END"),
            _ => throw EquityPanelLoaderReader.InvalidRequest()
        };
        var priceExpression = profile.AbsoluteValues
            ? "ABS(ROUND(b.f_last, 4))"
            : "ROUND(b.f_last, 4)";
        var changeExpression = profile.AbsoluteValues
            ? "ABS(ROUND(b.f_diff, 4))"
            : "ROUND(b.f_diff, 4)";
        var sql = $"""
            SELECT
                a.f_input_name TITLE,
                {priceExpression} PRICE,
                {changeExpression} CHANGE,
                ROUND(b.f_rate, 4) RATE,
                {signal} SIGNAL
            FROM {master} a, {quote} b
            WHERE a.f_symb = b.f_symb
              AND a.f_symb = :symbol
            """;
        return EquityPanelLoaderReader.Spec(
            DataSourceKind.Oracle,
            sql,
            new DataQueryParameter("symbol", profile.Symbol, DbType.String));
    }

    public static SceneMarketTrend Trend(string value) => value switch
    {
        "+" or "1" or "2" => SceneMarketTrend.Up,
        "-" or "4" or "5" => SceneMarketTrend.Down,
        "" or "3" => SceneMarketTrend.Flat,
        _ => throw EquityPanelLoaderReader.InvalidResult()
    };

    private static QuoteProfile Index(string symbol) =>
        new(QuoteSource.WorldIndex, symbol, CommodityUnit.DollarsPerOunce, false);

    private static QuoteProfile Future(
        string symbol,
        CommodityUnit unit,
        bool absoluteValues = false) =>
        new(QuoteSource.WorldFuture, symbol, unit, absoluteValues);

    internal enum QuoteSource
    {
        WorldIndex,
        WorldFuture
    }

    internal sealed record QuoteProfile(
        QuoteSource Source,
        string Symbol,
        CommodityUnit Unit,
        bool AbsoluteValues);
}

public sealed class S8067SceneDataLoader
{
    private static readonly string[] Columns =
    ["ORDINAL", "MARKET_CODE", "CURRENT_PRICE", "NET_CHANGE", "RATE", "SIGNAL"];

    private static readonly (string Code, string Symbol)[] Markets =
    [
        ("CHINA", "SHS@000001"),
        ("HONG_KONG", "HSI@HSI"),
        ("JAPAN", "NII@NI225"),
        ("TAIWAN", "TWS@TI01"),
        ("DOW", "DJI@DJI"),
        ("NASDAQ", "NAS@IXIC"),
        ("SP", "SPI@SPX"),
        ("UNITED_KINGDOM", "LNS@FTSE100"),
        ("GERMANY", "XTR@DAX30"),
        ("FRANCE", "PAS@CAC40")
    ];

    private readonly IDataQueryExecutor _executor;

    public S8067SceneDataLoader(IDataQueryExecutor executor)
    {
        _executor = executor ?? throw new ArgumentNullException(nameof(executor));
    }

    public async Task<S8067SceneData> LoadAsync(CancellationToken cancellationToken = default)
    {
        var spec = CreateSpec();
        var table = await _executor.ExecuteAsync(
            DataSourceKind.Oracle,
            "S8067_WORLD_MAP",
            spec,
            cancellationToken).ConfigureAwait(false);
        EquityPanelLoaderReader.ExactSchema(table, Columns);
        if (table.Rows.Count != 11)
        {
            throw EquityPanelLoaderReader.InvalidResult();
        }

        var quotes = new WorldMarketQuoteData[11];
        for (var index = 0; index < quotes.Length; index++)
        {
            var row = table.Rows[index];
            if (EquityPanelLoaderReader.Int64(row, "ORDINAL") != index + 1)
            {
                throw EquityPanelLoaderReader.InvalidResult();
            }

            quotes[index] = new WorldMarketQuoteData(
                Market(EquityPanelLoaderReader.Text(row, "MARKET_CODE")),
                EquityPanelLoaderReader.Double(row, "CURRENT_PRICE"),
                EquityPanelLoaderReader.Double(row, "NET_CHANGE"),
                EquityPanelLoaderReader.Double(row, "RATE"),
                CommodityQuoteQueryFactory.Trend(
                    EquityPanelLoaderReader.Text(row, "SIGNAL", allowNull: true)));
        }

        var data = new S8067SceneData(quotes);
        _ = new S8067SceneMutationBuilder().Build(data);
        return data;
    }

    private static DataQuerySpec CreateSpec()
    {
        const string korea = """
            SELECT
                1 ORDINAL,
                'KOREA' MARKET_CODE,
                ROUND(f_part_idx / 100, 2) CURRENT_PRICE,
                ROUND(f_part_chg / 100, 2) NET_CHANGE,
                ROUND((f_part_chg / DECODE(
                    f_chg_type,
                    '+', f_part_idx - f_part_chg,
                    '-', f_part_idx + f_part_chg,
                    1)) * 100, 2) RATE,
                f_chg_type SIGNAL
            FROM t_index
            WHERE f_part_code = :partCode
            """;
        var world = Markets.Select((market, zeroBased) =>
        {
            var ordinal = zeroBased + 2;
            return $"""
                SELECT
                    {ordinal} ORDINAL,
                    '{market.Code}' MARKET_CODE,
                    TO_NUMBER(f_last) CURRENT_PRICE,
                    TO_NUMBER(f_diff) NET_CHANGE,
                    TO_NUMBER(f_rate) RATE,
                    f_sign SIGNAL
                FROM t_world_ix_eq_sise
                WHERE f_symb = :symbol{ordinal}
                """;
        });
        var sql = korea + "\nUNION ALL\n" +
            string.Join("\nUNION ALL\n", world) + "\nORDER BY ORDINAL";
        var parameters = new List<DataQueryParameter>
        {
            new("partCode", "001", DbType.String)
        };
        parameters.AddRange(Markets.Select((market, zeroBased) =>
            new DataQueryParameter(
                "symbol" + (zeroBased + 2),
                market.Symbol,
                DbType.String)));
        return EquityPanelLoaderReader.Spec(
            DataSourceKind.Oracle,
            sql,
            parameters.ToArray());
    }

    private static WorldMarket Market(string code) => code switch
    {
        "KOREA" => WorldMarket.Korea,
        "CHINA" => WorldMarket.China,
        "HONG_KONG" => WorldMarket.HongKong,
        "JAPAN" => WorldMarket.Japan,
        "TAIWAN" => WorldMarket.Taiwan,
        "DOW" => WorldMarket.Dow,
        "NASDAQ" => WorldMarket.Nasdaq,
        "SP" => WorldMarket.StandardAndPoor,
        "UNITED_KINGDOM" => WorldMarket.UnitedKingdom,
        "GERMANY" => WorldMarket.Germany,
        "FRANCE" => WorldMarket.France,
        _ => throw EquityPanelLoaderReader.InvalidResult()
    };
}
