#nullable enable

using System.Data;
using System.Globalization;
using MMoneyCoderSharp.Data;

namespace MBN_STOCK_WEBVIEW.Core.Playout.Scenes;

/// <summary>
/// Closed request contract for the production s8010 candle queries. InstrumentName is a
/// bound value only; it is never interpolated into SQL or used as an identifier.
/// </summary>
public sealed record S8010SceneDataRequest(
    CandleCutAlias CutAlias,
    CandleMarketTarget MarketTarget,
    CandleChartMode ChartMode,
    string? InstrumentName,
    bool ShowMovingAverage5,
    bool ShowMovingAverage20,
    string? ForeignSymbol = null,
    string? ForeignNationCode = null);

/// <summary>
/// Loads the two legacy s8010 result sets (current quote, then ordered candles) through
/// parameterized, read-only Oracle queries and maps them to the trusted scene DTO.
/// </summary>
public sealed class S8010SceneDataLoader
{
    private const string CurrentResultName = "S8010_CURRENT";
    private const string RowsResultName = "S8010_ROWS";
    private readonly IDataQueryExecutor _executor;

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

    public async Task<S8010SceneData> LoadAsync(
        S8010SceneDataRequest request,
        CancellationToken cancellationToken = default)
    {
        ArgumentNullException.ThrowIfNull(request);
        var selection = S8010CandleQueryFactory.ValidateRequest(request);
        var plan = S8010CandleQueryFactory.Create(request, selection);

        var current = await _executor.ExecuteAsync(
            DataSourceKind.Oracle,
            CurrentResultName,
            plan.Current,
            cancellationToken).ConfigureAwait(false);
        var quote = S8010CandleTableMapper.MapQuote(current);

        var rows = await _executor.ExecuteAsync(
            DataSourceKind.Oracle,
            RowsResultName,
            plan.Rows,
            cancellationToken).ConfigureAwait(false);
        var candles = S8010CandleTableMapper.MapRows(
            rows,
            plan.IsIntraday,
            plan.MaximumRows);
        var data = new S8010SceneData(
            request.CutAlias,
            request.MarketTarget,
            request.ChartMode,
            quote.DisplayName,
            quote.Quote,
            candles,
            request.ShowMovingAverage5,
            request.ShowMovingAverage20);

        // Use the scene builder as the final DTO invariant check. No COM operation occurs.
        _ = new S8010SceneMutationBuilder().Build(data);
        return data;
    }
}

internal static class S8010CandleQueryFactory
{
    private const int MaximumSelectionLength = 256;
    private const string NullNumber = "CAST(NULL AS NUMBER)";
    private const string NullTime = "CAST(NULL AS VARCHAR2(6))";

    public static string? ValidateRequest(S8010SceneDataRequest request)
    {
        if (!Enum.IsDefined(request.CutAlias) ||
            !Enum.IsDefined(request.MarketTarget) ||
            !Enum.IsDefined(request.ChartMode))
        {
            throw InvalidRequest();
        }

        var requiresSelection = RequiresSelection(request.MarketTarget);
        if (!requiresSelection)
        {
            if (!string.IsNullOrWhiteSpace(request.InstrumentName) ||
                request.ForeignSymbol is not null ||
                request.ForeignNationCode is not null)
            {
                throw InvalidRequest();
            }

            return null;
        }

        var selection = LegacySceneBuilderGuard.Text(
            request.InstrumentName,
            nameof(request.InstrumentName));
        if (selection.Length > MaximumSelectionLength)
        {
            throw InvalidRequest();
        }

        var isForeign = request.MarketTarget is
            CandleMarketTarget.OverseasIndex or CandleMarketTarget.OverseasStock;
        if (!isForeign)
        {
            if (request.ForeignSymbol is not null || request.ForeignNationCode is not null)
            {
                throw InvalidRequest();
            }

            return selection;
        }

        var symbol = LegacySceneBuilderGuard.Text(
            request.ForeignSymbol,
            nameof(request.ForeignSymbol));
        var nationCode = LegacySceneBuilderGuard.Text(
            request.ForeignNationCode,
            nameof(request.ForeignNationCode));
        if (symbol.Length > 64 ||
            nationCode.Length != 2 ||
            nationCode.Any(character => character is < 'A' or > 'Z'))
        {
            throw InvalidRequest();
        }

        return selection;
    }

    public static S8010CandleQueryPlan Create(
        S8010SceneDataRequest request,
        string? selection)
    {
        var isIntraday = request.CutAlias == CandleCutAlias.Cut8035;
        var maximumRows = MaximumRows(request.CutAlias);
        EnsureSupported(request, isIntraday);

        var queries = request.MarketTarget switch
        {
            CandleMarketTarget.KospiIndex => DomesticIndex(
                request.ChartMode,
                isIntraday,
                maximumRows,
                new DomesticIndexSource(
                    "t_index",
                    "t_fo_index",
                    "t_index_his_5M",
                    "t_index_his_day",
                    "001")),
            CandleMarketTarget.KosdaqIndex => DomesticIndex(
                request.ChartMode,
                isIntraday,
                maximumRows,
                new DomesticIndexSource(
                    "t_kosdaq_index",
                    "t_kosdaq_fo_index",
                    "t_kosdaq_index_his_5M",
                    "t_kosdaq_index_his_day",
                    "001")),
            CandleMarketTarget.Kospi200Index => DomesticIndex(
                request.ChartMode,
                isIntraday,
                maximumRows,
                new DomesticIndexSource(
                    "T_200_INDEX",
                    "T_200_FO_INDEX",
                    "t_200_index_his_5M",
                    "t_200_index_his_day",
                    "029")),
            CandleMarketTarget.Krx100Index => DomesticIndex(
                request.ChartMode,
                isIntraday,
                maximumRows,
                new DomesticIndexSource(
                    "t_krx100_index",
                    null,
                    "t_krx100_index_his_5M",
                    "t_krx100_index_his_day",
                    "043")),
            CandleMarketTarget.FuturesIndex => Futures(
                request.ChartMode,
                isIntraday,
                maximumRows),
            CandleMarketTarget.KospiIndustry => Industry(
                request.ChartMode,
                isIntraday,
                maximumRows,
                selection!,
                new IndustrySource(
                    "t_index",
                    "t_part",
                    "t_index_his_5M",
                    "t_index_his_day")),
            CandleMarketTarget.KosdaqIndustry => Industry(
                request.ChartMode,
                isIntraday,
                maximumRows,
                selection!,
                new IndustrySource(
                    "t_kosdaq_index",
                    "t_kosdaq_part",
                    "t_kosdaq_index_his_5M",
                    "t_kosdaq_index_his_day")),
            CandleMarketTarget.OverseasIndex => Foreign(
                maximumRows,
                selection!,
                request.ForeignSymbol!,
                request.ForeignNationCode!,
                ForeignInstrumentScope.GlobalIndex),
            CandleMarketTarget.OverseasStock => Foreign(
                maximumRows,
                selection!,
                request.ForeignSymbol!,
                request.ForeignNationCode!,
                ForeignInstrumentScope.ForeignStock),
            CandleMarketTarget.KospiStock => Stock(
                request.ChartMode,
                isIntraday,
                maximumRows,
                selection!,
                new StockSource(
                    "t_online1",
                    "t_online1_call",
                    "t_stop_online1",
                    "t_online_his_5M",
                    "t_candle_history",
                    "t_stock",
                    "t_batch_day")),
            CandleMarketTarget.KosdaqStock => Stock(
                request.ChartMode,
                isIntraday,
                maximumRows,
                selection!,
                new StockSource(
                    "t_kosdaq_online1",
                    "t_kosdaq_online1_call",
                    "t_stop_kosdaq_online1",
                    "t_kosdaq_online_his_5M",
                    "t_kosdaq_candle_history",
                    "t_kosdaq_stock",
                    "t_kosdaq_batch_day")),
            _ => throw InvalidRequest()
        };

        return new S8010CandleQueryPlan(
            queries.Current,
            queries.Rows,
            maximumRows,
            isIntraday);
    }

    private static QueryPair DomesticIndex(
        CandleChartMode mode,
        bool isIntraday,
        int maximumRows,
        DomesticIndexSource source)
    {
        var expected = mode == CandleChartMode.ExpectedExecution;
        var currentTable = expected ? source.ForecastTable! : source.CurrentTable;
        var changeExpression = expected
            ? "ABS(ROUND(f_part_chg / 100, 2))"
            : "ROUND(f_part_chg / 100, 2)";
        var currentSql = $"""
            SELECT
                :displayName DISPLAY_NAME,
                ROUND(f_part_idx / 100, 2) CURRENT_PRICE,
                {changeExpression} CHANGE_PRICE,
                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 DIRECTION_CODE
            FROM {currentTable}
            WHERE f_part_code = :partCode
            """;
        var current = Oracle(
            currentSql,
            new DataQueryParameter("displayName", "\uC9C0\uC218", DbType.String),
            new DataQueryParameter("partCode", source.PartCode, DbType.String));
        var volume = mode == CandleChartMode.PriceWithVolume
            ? "a.F_PART_VOL"
            : NullNumber;
        var rowsSql = isIntraday
            ? $"""
                SELECT
                    TRADING_DATE, TRADING_TIME,
                    OPEN_PRICE, HIGH_PRICE, LOW_PRICE, CLOSE_PRICE,
                    VOLUME, MA5, MA20
                FROM (
                    SELECT
                        a.F_DATA_DAY TRADING_DATE,
                        a.F_DATA_TIME TRADING_TIME,
                        a.F_PART_INIT_IDX / 100 OPEN_PRICE,
                        a.F_PART_HIGH_IDX / 100 HIGH_PRICE,
                        a.F_PART_LOW_IDX / 100 LOW_PRICE,
                        a.F_PART_IDX / 100 CLOSE_PRICE,
                        {volume} VOLUME,
                        {NullNumber} MA5,
                        {NullNumber} MA20
                    FROM {source.FiveMinuteTable} a
                    WHERE a.f_part_code = :partCode
                      AND a.f_data_day = (SELECT MAX(open_day) FROM v_open_day)
                    ORDER BY a.F_DATA_DAY, a.F_DATA_TIME
                )
                WHERE ROWNUM <= :rowLimit
                ORDER BY TRADING_DATE, TRADING_TIME
                """
            : $"""
                SELECT
                    TRADING_DATE, TRADING_TIME,
                    OPEN_PRICE, HIGH_PRICE, LOW_PRICE, CLOSE_PRICE,
                    VOLUME, MA5, MA20
                FROM (
                    SELECT
                        a.F_DATA_DAY TRADING_DATE,
                        {NullTime} TRADING_TIME,
                        a.F_PART_INIT_IDX / 100 OPEN_PRICE,
                        a.F_PART_HIGH_IDX / 100 HIGH_PRICE,
                        a.F_PART_LOW_IDX / 100 LOW_PRICE,
                        a.F_PART_IDX / 100 CLOSE_PRICE,
                        {volume} VOLUME,
                        a.F_PART_MA5_IDX / 100 MA5,
                        a.F_PART_MA20_IDX / 100 MA20
                    FROM {source.DailyTable} a
                    WHERE a.f_part_code = :partCode
                    ORDER BY a.F_DATA_DAY DESC
                )
                WHERE ROWNUM <= :rowLimit
                ORDER BY TRADING_DATE
                """;
        var rows = Oracle(
            rowsSql,
            new DataQueryParameter("partCode", source.PartCode, DbType.String),
            new DataQueryParameter("rowLimit", maximumRows, DbType.Int32));
        return new QueryPair(current, rows);
    }

    private static QueryPair Futures(
        CandleChartMode mode,
        bool isIntraday,
        int maximumRows)
    {
        var currentSql = mode == CandleChartMode.ExpectedExecution
            ? """
                SELECT
                    :displayName DISPLAY_NAME,
                    part_idx CURRENT_PRICE,
                    ABS(part_chg) CHANGE_PRICE,
                    ROUND(part_chg / DECODE(
                        chg_type,
                        '+', (part_idx / 100) - (part_chg / 100),
                        '-', (part_idx / 100) + (part_chg / 100),
                        1), 2) RATE,
                    chg_type DIRECTION_CODE
                FROM (
                    SELECT
                        DECODE(a.f_fo_price, 0, b.F_JUN_LAST_PRICE, a.f_fo_price) part_idx,
                        DECODE(a.f_fo_price, 0, 0,
                            (a.f_fo_price - b.F_JUN_LAST_PRICE) / 100) part_chg,
                        DECODE(a.f_fo_price, 0, ' ',
                            DECODE(SIGN(a.f_fo_price - b.F_JUN_LAST_PRICE),
                                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
                    :displayName DISPLAY_NAME,
                    part_idx CURRENT_PRICE,
                    ABS(part_chg) CHANGE_PRICE,
                    ABS(ROUND(part_chg / DECODE(
                        chg_type,
                        '+', (part_idx / 100) - (part_chg / 100),
                        '-', (part_idx / 100) + (part_chg / 100),
                        1), 2)) RATE,
                    chg_type DIRECTION_CODE
                FROM (
                    SELECT
                        DECODE(a.f_curr_price, 0,
                            b.F_JUN_LAST_PRICE / 100,
                            a.f_curr_price / 100) part_idx,
                        DECODE(a.f_curr_price, 0, 0,
                            (a.f_curr_price - b.F_JUN_LAST_PRICE * 100) / 100) part_chg,
                        DECODE(a.f_curr_price, 0, ' ',
                            DECODE(SIGN(a.f_curr_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'
                )
                """;
        var current = Oracle(
            currentSql,
            new DataQueryParameter("displayName", "\uC9C0\uC218", DbType.String));
        var rowsSql = isIntraday
            ? """
                SELECT
                    TRADING_DATE, TRADING_TIME,
                    OPEN_PRICE, HIGH_PRICE, LOW_PRICE, CLOSE_PRICE,
                    VOLUME, MA5, MA20
                FROM (
                    SELECT
                        a.F_DATA_DAY TRADING_DATE,
                        a.F_DATA_TIME TRADING_TIME,
                        a.F_PART_INIT_IDX / 100 OPEN_PRICE,
                        a.F_PART_HIGH_IDX / 100 HIGH_PRICE,
                        a.F_PART_LOW_IDX / 100 LOW_PRICE,
                        a.F_PART_IDX / 100 CLOSE_PRICE,
                        CAST(NULL AS NUMBER) VOLUME,
                        CAST(NULL AS NUMBER) MA5,
                        CAST(NULL AS NUMBER) MA20
                    FROM t_sunmul_his_5M a
                    WHERE a.f_data_day = (SELECT MAX(open_day) FROM v_open_day)
                    ORDER BY a.F_DATA_DAY, a.F_DATA_TIME
                )
                WHERE ROWNUM <= :rowLimit
                ORDER BY TRADING_DATE, TRADING_TIME
                """
            : """
                SELECT
                    TRADING_DATE, TRADING_TIME,
                    OPEN_PRICE, HIGH_PRICE, LOW_PRICE, CLOSE_PRICE,
                    VOLUME, MA5, MA20
                FROM (
                    SELECT
                        F_DATA_DAY TRADING_DATE,
                        CAST(NULL AS VARCHAR2(6)) TRADING_TIME,
                        F_PART_INIT_IDX / 100 OPEN_PRICE,
                        F_PART_HIGH_IDX / 100 HIGH_PRICE,
                        F_PART_LOW_IDX / 100 LOW_PRICE,
                        F_PART_IDX / 100 CLOSE_PRICE,
                        CAST(NULL AS NUMBER) VOLUME,
                        F_PART_MA5_IDX / 100 MA5,
                        F_PART_MA20_IDX / 100 MA20
                    FROM T_SUNMUL_HIS_DAY
                    ORDER BY F_DATA_DAY DESC
                )
                WHERE ROWNUM <= :rowLimit
                ORDER BY TRADING_DATE
                """;
        var rows = Oracle(
            rowsSql,
            new DataQueryParameter("rowLimit", maximumRows, DbType.Int32));
        return new QueryPair(current, rows);
    }

    private static QueryPair Industry(
        CandleChartMode mode,
        bool isIntraday,
        int maximumRows,
        string selection,
        IndustrySource source)
    {
        var currentSql = $"""
            SELECT
                b.f_part_name DISPLAY_NAME,
                ROUND(a.f_part_idx / 100, 2) CURRENT_PRICE,
                ROUND(a.f_part_chg / 100, 2) CHANGE_PRICE,
                ROUND((a.f_part_chg / DECODE(
                    a.f_chg_type,
                    '+', a.f_part_idx - a.f_part_chg,
                    '-', a.f_part_idx + a.f_part_chg,
                    1)) * 100, 2) RATE,
                a.f_chg_type DIRECTION_CODE
            FROM {source.CurrentTable} a, {source.MasterTable} b
            WHERE a.f_part_code = b.f_part_code
              AND b.f_part_name = :selection
            """;
        var current = Oracle(
            currentSql,
            new DataQueryParameter("selection", selection, DbType.String));
        var volume = mode == CandleChartMode.PriceWithVolume
            ? "a.F_PART_VOL"
            : NullNumber;
        var rowsSql = isIntraday
            ? $"""
                SELECT
                    TRADING_DATE, TRADING_TIME,
                    OPEN_PRICE, HIGH_PRICE, LOW_PRICE, CLOSE_PRICE,
                    VOLUME, MA5, MA20
                FROM (
                    SELECT
                        a.F_DATA_DAY TRADING_DATE,
                        a.F_DATA_TIME TRADING_TIME,
                        a.F_PART_INIT_IDX / 100 OPEN_PRICE,
                        a.F_PART_HIGH_IDX / 100 HIGH_PRICE,
                        a.F_PART_LOW_IDX / 100 LOW_PRICE,
                        a.F_PART_IDX / 100 CLOSE_PRICE,
                        {volume} VOLUME,
                        {NullNumber} MA5,
                        {NullNumber} MA20
                    FROM {source.FiveMinuteTable} a, {source.MasterTable} b
                    WHERE a.f_part_code = b.f_part_code
                      AND b.f_part_name = :selection
                      AND a.f_data_day = (SELECT MAX(open_day) FROM v_open_day)
                    ORDER BY a.F_DATA_DAY, a.F_DATA_TIME
                )
                WHERE ROWNUM <= :rowLimit
                ORDER BY TRADING_DATE, TRADING_TIME
                """
            : $"""
                SELECT
                    TRADING_DATE, TRADING_TIME,
                    OPEN_PRICE, HIGH_PRICE, LOW_PRICE, CLOSE_PRICE,
                    VOLUME, MA5, MA20
                FROM (
                    SELECT
                        a.F_DATA_DAY TRADING_DATE,
                        {NullTime} TRADING_TIME,
                        a.F_PART_INIT_IDX / 100 OPEN_PRICE,
                        a.F_PART_HIGH_IDX / 100 HIGH_PRICE,
                        a.F_PART_LOW_IDX / 100 LOW_PRICE,
                        a.F_PART_IDX / 100 CLOSE_PRICE,
                        {volume} VOLUME,
                        a.F_PART_MA5_IDX / 100 MA5,
                        a.F_PART_MA20_IDX / 100 MA20
                    FROM {source.DailyTable} a, {source.MasterTable} b
                    WHERE a.f_part_code = b.f_part_code
                      AND b.f_part_name = :selection
                    ORDER BY a.F_DATA_DAY DESC
                )
                WHERE ROWNUM <= :rowLimit
                ORDER BY TRADING_DATE
                """;
        var rows = Oracle(
            rowsSql,
            new DataQueryParameter("selection", selection, DbType.String),
            new DataQueryParameter("rowLimit", maximumRows, DbType.Int32));
        return new QueryPair(current, rows);
    }

    private static QueryPair Foreign(
        int maximumRows,
        string selection,
        string symbol,
        string nationCode,
        ForeignInstrumentScope scope)
    {
        var (instrumentType, masterScope) = scope switch
        {
            ForeignInstrumentScope.GlobalIndex => (
                "0",
                "AND a.f_fdtc = '0'"),
            ForeignInstrumentScope.ForeignStock => (
                "1",
                "AND a.f_fdtc = '1'"),
            _ => throw InvalidRequest()
        };
        var currentSql = $"""
            SELECT DISTINCT
                a.f_input_name DISPLAY_NAME,
                ROUND(b.f_last, 2) CURRENT_PRICE,
                ABS(ROUND(b.f_diff, 2)) CHANGE_PRICE,
                ROUND(b.f_rate, 2) RATE,
                b.f_sign DIRECTION_CODE
            FROM t_world_ix_eq_master a, t_world_ix_eq_sise b
            WHERE a.f_symb = b.f_symb
              AND a.f_input_name = :selection
              AND a.f_symb = :symbol
              AND a.f_natc = :nationCode
              {masterScope}
              AND b.f_fdtc = :instrumentType
            """;
        var current = Oracle(
            currentSql,
            new DataQueryParameter("selection", selection, DbType.String),
            new DataQueryParameter("symbol", symbol, DbType.String),
            new DataQueryParameter("nationCode", nationCode, DbType.String),
            new DataQueryParameter("instrumentType", instrumentType, DbType.String));
        var rowsSql = $"""
            SELECT
                TRADING_DATE, TRADING_TIME,
                OPEN_PRICE, HIGH_PRICE, LOW_PRICE, CLOSE_PRICE,
                VOLUME, MA5, MA20
            FROM (
                SELECT DISTINCT
                    b.F_xYMD TRADING_DATE,
                    CAST(NULL AS VARCHAR2(6)) TRADING_TIME,
                    b.f_open OPEN_PRICE,
                    b.f_high HIGH_PRICE,
                    b.f_low LOW_PRICE,
                    b.f_last CLOSE_PRICE,
                    CAST(NULL AS NUMBER) VOLUME,
                    CAST(NULL AS NUMBER) MA5,
                    CAST(NULL AS NUMBER) MA20
                FROM t_world_ix_eq_master a, t_world_ix_eq_his b
                WHERE a.f_symb = b.f_symb
                  AND a.f_input_name = :selection
                  AND a.f_symb = :symbol
                  AND a.f_natc = :nationCode
                  {masterScope}
                  AND b.f_fdtc = :instrumentType
                ORDER BY b.F_xYMD DESC
            )
            WHERE ROWNUM <= :rowLimit
            ORDER BY TRADING_DATE
            """;
        var rows = Oracle(
            rowsSql,
            new DataQueryParameter("selection", selection, DbType.String),
            new DataQueryParameter("symbol", symbol, DbType.String),
            new DataQueryParameter("nationCode", nationCode, DbType.String),
            new DataQueryParameter("instrumentType", instrumentType, DbType.String),
            new DataQueryParameter("rowLimit", maximumRows, DbType.Int32));
        return new QueryPair(current, rows);
    }

    private static QueryPair Stock(
        CandleChartMode mode,
        bool isIntraday,
        int maximumRows,
        string selection,
        StockSource source)
    {
        var haltCode = mode == CandleChartMode.TradingHalt ? "Y" : "N";
        string currentSql;
        if (mode == CandleChartMode.ExpectedExecution)
        {
            currentSql = $"""
                SELECT DISTINCT
                    b.F_STOCK_WANNAME DISPLAY_NAME,
                    a.f_fore_price CURRENT_PRICE,
                    ABS(a.f_fore_price - c.f_final_price) CHANGE_PRICE,
                    ROUND(((a.f_fore_price - c.f_final_price) / c.f_final_price) * 100, 2) RATE,
                    CASE
                        WHEN a.f_fore_price - c.f_final_price > 0 THEN '+'
                        WHEN a.f_fore_price - c.f_final_price = 0 THEN ' '
                        WHEN a.f_fore_price - c.f_final_price < 0 THEN '-'
                    END DIRECTION_CODE
                FROM {source.ForecastTable} a, {source.MasterTable} b, {source.BatchTable} c
                WHERE b.f_stock_wanname = :selection
                  AND b.f_mkt_halt = :haltCode
                  AND a.f_stock_code = b.f_stock_code
                  AND c.f_stock_code = b.f_stock_code
                """;
        }
        else
        {
            var currentTable = mode == CandleChartMode.TradingHalt
                ? source.HaltedCurrentTable
                : source.CurrentTable;
            currentSql = $"""
                SELECT DISTINCT
                    b.F_STOCK_WANNAME DISPLAY_NAME,
                    a.f_curr_price CURRENT_PRICE,
                    ABS(a.f_net_chg) CHANGE_PRICE,
                    ROUND((a.f_net_chg / DECODE(
                        a.f_chg_type,
                        '1', a.f_curr_price - a.f_net_chg,
                        '2', a.f_curr_price - a.f_net_chg,
                        '3', a.f_curr_price,
                        '4', -1 * (a.f_curr_price + a.f_net_chg),
                        '5', -1 * (a.f_curr_price + a.f_net_chg))) * 100, 2) RATE,
                    a.f_chg_type DIRECTION_CODE
                FROM {currentTable} a, {source.MasterTable} b
                WHERE b.f_stock_wanname = :selection
                  AND b.f_mkt_halt = :haltCode
                  AND a.f_curr_price <> 0
                  AND a.f_stock_code = b.f_stock_code
                """;
        }

        var current = Oracle(
            currentSql,
            new DataQueryParameter("selection", selection, DbType.String),
            new DataQueryParameter("haltCode", haltCode, DbType.String));
        var volume = mode == CandleChartMode.PriceWithVolume
            ? "a.F_NET_VOL"
            : NullNumber;
        var rowsSql = isIntraday
            ? $"""
                SELECT
                    TRADING_DATE, TRADING_TIME,
                    OPEN_PRICE, HIGH_PRICE, LOW_PRICE, CLOSE_PRICE,
                    VOLUME, MA5, MA20
                FROM (
                    SELECT
                        a.F_DATA_DAY TRADING_DATE,
                        a.F_DATA_TIME TRADING_TIME,
                        a.F_INIT_PRICE OPEN_PRICE,
                        a.F_HIGH_PRICE HIGH_PRICE,
                        a.F_LOW_PRICE LOW_PRICE,
                        a.F_CURR_PRICE CLOSE_PRICE,
                        {volume} VOLUME,
                        {NullNumber} MA5,
                        {NullNumber} MA20
                    FROM {source.FiveMinuteTable} a, {source.MasterTable} b
                    WHERE b.f_stock_wanname = :selection
                      AND b.f_mkt_halt = :haltCode
                      AND a.f_stock_code = b.f_stock_code
                      AND a.f_curr_price <> 0
                      AND a.f_data_day = (SELECT MAX(open_day) FROM v_open_day)
                    ORDER BY a.F_DATA_DAY, a.F_DATA_TIME
                )
                WHERE ROWNUM <= :rowLimit
                ORDER BY TRADING_DATE, TRADING_TIME
                """
            : $"""
                SELECT
                    TRADING_DATE, TRADING_TIME,
                    OPEN_PRICE, HIGH_PRICE, LOW_PRICE, CLOSE_PRICE,
                    VOLUME, MA5, MA20
                FROM (
                    SELECT
                        a.F_DATA_DATE TRADING_DATE,
                        {NullTime} TRADING_TIME,
                        a.F_INIT_PRICE OPEN_PRICE,
                        a.F_HIGH_PRICE HIGH_PRICE,
                        a.F_LOW_PRICE LOW_PRICE,
                        a.F_CURR_PRICE CLOSE_PRICE,
                        {volume} VOLUME,
                        a.F_MA5_PRICE MA5,
                        a.F_MA20_PRICE MA20
                    FROM {source.DailyTable} a, {source.MasterTable} b
                    WHERE b.f_stock_wanname = :selection
                      AND b.f_mkt_halt = :haltCode
                      AND a.f_stock_code = b.f_stock_code
                    ORDER BY a.F_DATA_DATE DESC
                )
                WHERE ROWNUM <= :rowLimit
                ORDER BY TRADING_DATE
                """;
        var rows = Oracle(
            rowsSql,
            new DataQueryParameter("selection", selection, DbType.String),
            new DataQueryParameter("haltCode", haltCode, DbType.String),
            new DataQueryParameter("rowLimit", maximumRows, DbType.Int32));
        return new QueryPair(current, rows);
    }

    private static void EnsureSupported(S8010SceneDataRequest request, bool isIntraday)
    {
        var supported = request.MarketTarget switch
        {
            CandleMarketTarget.KospiIndex or
            CandleMarketTarget.KosdaqIndex or
            CandleMarketTarget.Kospi200Index => request.ChartMode switch
            {
                CandleChartMode.Price or CandleChartMode.PriceWithVolume => true,
                CandleChartMode.ExpectedExecution => !isIntraday,
                _ => false
            },
            CandleMarketTarget.Krx100Index =>
                request.ChartMode is CandleChartMode.Price or CandleChartMode.PriceWithVolume,
            CandleMarketTarget.FuturesIndex =>
                request.ChartMode == CandleChartMode.Price ||
                (request.ChartMode == CandleChartMode.ExpectedExecution && !isIntraday),
            CandleMarketTarget.KospiIndustry or CandleMarketTarget.KosdaqIndustry =>
                request.ChartMode is CandleChartMode.Price or CandleChartMode.PriceWithVolume,
            CandleMarketTarget.OverseasIndex or CandleMarketTarget.OverseasStock =>
                request.ChartMode == CandleChartMode.Price && !isIntraday,
            CandleMarketTarget.KospiStock or CandleMarketTarget.KosdaqStock =>
                request.ChartMode switch
                {
                    CandleChartMode.Price or CandleChartMode.PriceWithVolume => true,
                    CandleChartMode.ExpectedExecution or CandleChartMode.TradingHalt => !isIntraday,
                    _ => false
                },
            _ => false
        };

        if (!supported)
        {
            throw InvalidRequest();
        }
    }

    private static bool RequiresSelection(CandleMarketTarget target) => target is
        CandleMarketTarget.KospiIndustry or CandleMarketTarget.KosdaqIndustry or
        CandleMarketTarget.OverseasIndex or CandleMarketTarget.OverseasStock or
        CandleMarketTarget.KospiStock or CandleMarketTarget.KosdaqStock;

    private static int MaximumRows(CandleCutAlias alias) => alias switch
    {
        CandleCutAlias.Cut8035 => 78,
        CandleCutAlias.Cut8061 => 5,
        CandleCutAlias.Cut8040 => 20,
        CandleCutAlias.Cut8046 => 60,
        CandleCutAlias.Cut8051 => 120,
        CandleCutAlias.Cut8056 => 240,
        _ => throw InvalidRequest()
    };

    private static DataQuerySpec Oracle(
        string sql,
        params DataQueryParameter[] parameters)
    {
        var spec = new DataQuerySpec(sql, parameters);
        spec.ValidateFor(DataSourceKind.Oracle);
        return spec;
    }

    private static LegacySceneDataException InvalidRequest() =>
        new("The candle scene data request is unsupported or invalid.");

    private sealed record DomesticIndexSource(
        string CurrentTable,
        string? ForecastTable,
        string FiveMinuteTable,
        string DailyTable,
        string PartCode);

    private sealed record IndustrySource(
        string CurrentTable,
        string MasterTable,
        string FiveMinuteTable,
        string DailyTable);

    private sealed record StockSource(
        string CurrentTable,
        string ForecastTable,
        string HaltedCurrentTable,
        string FiveMinuteTable,
        string DailyTable,
        string MasterTable,
        string BatchTable);

    private enum ForeignInstrumentScope
    {
        GlobalIndex,
        ForeignStock
    }

    private sealed record QueryPair(DataQuerySpec Current, DataQuerySpec Rows);
}

internal sealed record S8010CandleQueryPlan(
    DataQuerySpec Current,
    DataQuerySpec Rows,
    int MaximumRows,
    bool IsIntraday);

internal static class S8010CandleTableMapper
{
    private static readonly string[] QuoteColumns =
    [
        "DISPLAY_NAME", "CURRENT_PRICE", "CHANGE_PRICE", "RATE", "DIRECTION_CODE"
    ];

    private static readonly string[] RowColumns =
    [
        "TRADING_DATE", "TRADING_TIME",
        "OPEN_PRICE", "HIGH_PRICE", "LOW_PRICE", "CLOSE_PRICE",
        "VOLUME", "MA5", "MA20"
    ];

    public static MappedQuote MapQuote(DataTable? table)
    {
        RequireSchema(table, QuoteColumns);
        if (table!.Rows.Count != 1)
        {
            throw InvalidResult();
        }

        var row = table.Rows[0];
        var displayName = RequiredText(row, "DISPLAY_NAME");
        var quote = new CandleQuoteData(
            RequiredDouble(row, "CURRENT_PRICE"),
            RequiredDouble(row, "CHANGE_PRICE"),
            RequiredDouble(row, "RATE"),
            Direction(row["DIRECTION_CODE"]));
        return new MappedQuote(displayName, quote);
    }

    public static IReadOnlyList<CandleRowData> MapRows(
        DataTable? table,
        bool isIntraday,
        int maximumRows)
    {
        RequireSchema(table, RowColumns);
        if (table!.Rows.Count is < 1 || table.Rows.Count > maximumRows)
        {
            throw InvalidResult();
        }

        var result = new CandleRowData[table.Rows.Count];
        DateOnly? previousDate = null;
        TimeOnly? previousTime = null;
        for (var index = 0; index < table.Rows.Count; index++)
        {
            var row = table.Rows[index];
            var date = RequiredDate(row, "TRADING_DATE");
            var time = OptionalTime(row, "TRADING_TIME");
            if (isIntraday != time.HasValue)
            {
                throw InvalidResult();
            }

            if (previousDate.HasValue)
            {
                var compare = date.CompareTo(previousDate.Value);
                if (compare < 0 ||
                    (compare == 0 && (!isIntraday ||
                        time!.Value.CompareTo(previousTime!.Value) <= 0)))
                {
                    throw InvalidResult();
                }
            }

            result[index] = new CandleRowData(
                date,
                time,
                RequiredDouble(row, "OPEN_PRICE"),
                RequiredDouble(row, "HIGH_PRICE"),
                RequiredDouble(row, "LOW_PRICE"),
                RequiredDouble(row, "CLOSE_PRICE"),
                OptionalDouble(row, "VOLUME"),
                OptionalDouble(row, "MA5"),
                OptionalDouble(row, "MA20"));
            previousDate = date;
            previousTime = time;
        }

        return result;
    }

    private static void RequireSchema(DataTable? table, IReadOnlyList<string> expected)
    {
        if (table is null || table.Columns.Count != expected.Count)
        {
            throw InvalidResult();
        }

        for (var index = 0; index < expected.Count; index++)
        {
            if (!string.Equals(
                    table.Columns[index].ColumnName,
                    expected[index],
                    StringComparison.OrdinalIgnoreCase))
            {
                throw InvalidResult();
            }
        }
    }

    private static string RequiredText(DataRow row, string column)
    {
        var value = row[column];
        if (value is null or DBNull)
        {
            throw InvalidResult();
        }

        var text = Convert.ToString(value, CultureInfo.InvariantCulture);
        try
        {
            return LegacySceneBuilderGuard.Text(text, column);
        }
        catch (LegacySceneDataException)
        {
            throw InvalidResult();
        }
    }

    private static double RequiredDouble(DataRow row, string column)
    {
        var value = OptionalDouble(row, column);
        return value ?? throw InvalidResult();
    }

    private static double? OptionalDouble(DataRow row, string column)
    {
        var value = row[column];
        if (value is null or DBNull)
        {
            return null;
        }

        try
        {
            var number = value is string text
                ? double.Parse(text, NumberStyles.Float, CultureInfo.InvariantCulture)
                : Convert.ToDouble(value, CultureInfo.InvariantCulture);
            if (!double.IsFinite(number))
            {
                throw InvalidResult();
            }

            return number;
        }
        catch (Exception exception) when (
            exception is FormatException or InvalidCastException or OverflowException)
        {
            throw InvalidResult();
        }
    }

    private static DateOnly RequiredDate(DataRow row, string column)
    {
        var value = row[column];
        if (value is DateOnly dateOnly)
        {
            return dateOnly;
        }

        if (value is DateTime dateTime)
        {
            return DateOnly.FromDateTime(dateTime);
        }

        if (value is DateTimeOffset offset)
        {
            return DateOnly.FromDateTime(offset.DateTime);
        }

        if (value is null or DBNull)
        {
            throw InvalidResult();
        }

        var text = Convert.ToString(value, CultureInfo.InvariantCulture)?.Trim();
        string[] formats = ["yyyyMMdd", "yyyy-MM-dd", "yyyy/MM/dd", "yyyy.MM.dd"];
        if (text is null || !DateOnly.TryParseExact(
                text,
                formats,
                CultureInfo.InvariantCulture,
                DateTimeStyles.None,
                out var result))
        {
            throw InvalidResult();
        }

        return result;
    }

    private static TimeOnly? OptionalTime(DataRow row, string column)
    {
        var value = row[column];
        if (value is null or DBNull)
        {
            return null;
        }

        if (value is TimeOnly timeOnly)
        {
            return timeOnly;
        }

        if (value is DateTime dateTime)
        {
            return TimeOnly.FromDateTime(dateTime);
        }

        if (value is TimeSpan span && span >= TimeSpan.Zero && span < TimeSpan.FromDays(1))
        {
            return TimeOnly.FromTimeSpan(span);
        }

        var text = Convert.ToString(value, CultureInfo.InvariantCulture)?.Trim();
        if (string.IsNullOrEmpty(text))
        {
            return null;
        }

        if (text.All(char.IsAsciiDigit))
        {
            text = text.Length switch
            {
                <= 4 => text.PadLeft(4, '0'),
                <= 6 => text.PadLeft(6, '0'),
                _ => text
            };
        }

        string[] formats = ["HHmm", "HHmmss", "HH:mm", "HH:mm:ss"];
        if (!TimeOnly.TryParseExact(
                text,
                formats,
                CultureInfo.InvariantCulture,
                DateTimeStyles.None,
                out var result))
        {
            throw InvalidResult();
        }

        return result;
    }

    private static ScenePriceDirection Direction(object? value)
    {
        var text = value is null or DBNull
            ? string.Empty
            : Convert.ToString(value, CultureInfo.InvariantCulture)?.Trim();
        return text switch
        {
            "\uC0C1\uD55C" or "1" => ScenePriceDirection.LimitUp,
            "\uC0C1\uC2B9" or "+" or "2" => ScenePriceDirection.Up,
            null or "" or "\uBCF4\uD569" or "3" => ScenePriceDirection.Flat,
            "\uD558\uB77D" or "-" or "5" => ScenePriceDirection.Down,
            "\uD558\uD55C" or "4" => ScenePriceDirection.LimitDown,
            _ => throw InvalidResult()
        };
    }

    private static LegacySceneDataException InvalidResult() =>
        new("The candle scene query returned invalid data or an unexpected schema.");

    internal sealed record MappedQuote(string DisplayName, CandleQuoteData Quote);
}
