#nullable enable

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

namespace MBN_STOCK_WEBVIEW.Core.Playout.Scenes;

public sealed record ComparisonStockSelection(
    ComparisonEquityMarket Market,
    string StockName,
    string StockCode);

public sealed record ComparisonPairSceneLoadRequest(
    ComparisonGraphPeriod Period,
    ComparisonStockSelection First,
    ComparisonStockSelection Second);

public sealed record YieldSceneLoadRequest(
    YieldGraphPeriod Period,
    YieldGraphInstrument Instrument);

public sealed class S5026SceneDataLoader
{
    private readonly ComparisonAndYieldSceneLoaderCore _core;

    public S5026SceneDataLoader(IDataQueryExecutor executor)
    {
        _core = new ComparisonAndYieldSceneLoaderCore(executor);
    }

    public async Task<S5026SceneData> LoadAsync(
        ComparisonPairSceneLoadRequest request,
        CancellationToken cancellationToken = default)
    {
        ArgumentNullException.ThrowIfNull(request);
        if (request.Period != ComparisonGraphPeriod.FiveDays)
        {
            throw ComparisonAndYieldLoadGuard.InvalidRequest();
        }

        var series = await _core.LoadCandlePairAsync(
            request,
            "SCENE_5026",
            cancellationToken).ConfigureAwait(false);
        return new S5026SceneData(request.Period, series);
    }
}

public sealed class S5029SceneDataLoader
{
    private readonly ComparisonAndYieldSceneLoaderCore _core;

    public S5029SceneDataLoader(IDataQueryExecutor executor)
    {
        _core = new ComparisonAndYieldSceneLoaderCore(executor);
    }

    public async Task<S5029SceneData> LoadAsync(
        ComparisonPairSceneLoadRequest request,
        CancellationToken cancellationToken = default)
    {
        ArgumentNullException.ThrowIfNull(request);
        _ = ComparisonAndYieldLoadGuard.ComparisonRowLimit(request.Period);
        var series = await _core.LoadComparisonYieldPairAsync(
            request,
            "SCENE_5029",
            cancellationToken).ConfigureAwait(false);
        return new S5029SceneData(request.Period, series);
    }
}

public sealed class S5087SceneDataLoader
{
    private readonly ComparisonAndYieldSceneLoaderCore _core;

    public S5087SceneDataLoader(IDataQueryExecutor executor)
    {
        _core = new ComparisonAndYieldSceneLoaderCore(executor);
    }

    public async Task<S5087SceneData> LoadAsync(
        ComparisonPairSceneLoadRequest request,
        CancellationToken cancellationToken = default)
    {
        ArgumentNullException.ThrowIfNull(request);
        if (request.Period != ComparisonGraphPeriod.FiveDays)
        {
            throw ComparisonAndYieldLoadGuard.InvalidRequest();
        }

        var series = await _core.LoadComparisonYieldPairAsync(
            request,
            "SCENE_5087",
            cancellationToken).ConfigureAwait(false);
        return new S5087SceneData(request.Period, series);
    }
}

public sealed class S5086SceneDataLoader
{
    private readonly ComparisonAndYieldSceneLoaderCore _core;

    public S5086SceneDataLoader(IDataQueryExecutor executor)
    {
        _core = new ComparisonAndYieldSceneLoaderCore(executor);
    }

    public async Task<S5086SceneData> LoadAsync(
        YieldSceneLoadRequest request,
        CancellationToken cancellationToken = default)
    {
        ArgumentNullException.ThrowIfNull(request);
        var points = await _core.LoadSingleYieldAsync(
            request,
            "SCENE_5086_YIELD",
            allowMissingValues: true,
            cancellationToken).ConfigureAwait(false);
        return new S5086SceneData(request.Period, request.Instrument, points);
    }
}

public sealed class S50860SceneDataLoader
{
    private readonly ComparisonAndYieldSceneLoaderCore _core;

    public S50860SceneDataLoader(IDataQueryExecutor executor)
    {
        _core = new ComparisonAndYieldSceneLoaderCore(executor);
    }

    public async Task<S50860SceneData> LoadAsync(
        YieldSceneLoadRequest request,
        CancellationToken cancellationToken = default)
    {
        ArgumentNullException.ThrowIfNull(request);
        ComparisonAndYieldLoadGuard.ValidateInstrument(request.Instrument);
        if (!ComparisonAndYieldLoadGuard.IsFx(request.Instrument.Kind))
        {
            // The source reads cells 4/5/6 for every branch. Only Exchange supplies
            // net_chg/chg_type/rate at those positions; index, industry, and stock
            // result shapes cannot produce a valid s50860 quote and are rejected.
            throw ComparisonAndYieldLoadGuard.InvalidRequest();
        }

        var loaded = await _core.LoadQuotedFxYieldAsync(
            request,
            "SCENE_50860_YIELD",
            cancellationToken).ConfigureAwait(false);
        return new S50860SceneData(
            request.Period,
            request.Instrument,
            loaded.ChangePrice,
            loaded.Rate,
            loaded.Direction,
            loaded.Points);
    }
}

internal sealed class ComparisonAndYieldSceneLoaderCore
{
    private static readonly string[] CandleColumns =
    [
        "STOCK_NAME", "CURRENT_PRICE", "NET_CHANGE", "RATE", "DIRECTION",
        "OPEN_DAY", "HIGH_VALUE", "LOW_VALUE", "OPEN_VALUE", "CLOSE_VALUE"
    ];

    private static readonly string[] ComparisonYieldColumns =
    [
        "STOCK_NAME", "CURRENT_PRICE", "NET_CHANGE", "RATE", "DIRECTION",
        "DATA_DAY", "VALUE"
    ];

    private static readonly string[] SingleYieldColumns = ["DATA_DAY", "VALUE"];

    private static readonly string[] QuotedFxColumns =
        ["DATA_DAY", "VALUE", "CHANGE_PRICE", "RATE", "DIRECTION"];

    private readonly IDataQueryExecutor _executor;

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

    public async Task<IReadOnlyList<ComparisonCandleSeriesData>> LoadCandlePairAsync(
        ComparisonPairSceneLoadRequest request,
        string tablePrefix,
        CancellationToken cancellationToken)
    {
        var selections = ComparisonAndYieldLoadGuard.Pair(request);
        var result = new ComparisonCandleSeriesData[2];
        for (var index = 0; index < selections.Length; index++)
        {
            var target = index == 0
                ? ComparisonSeriesTarget.First
                : ComparisonSeriesTarget.Second;
            var selection = selections[index];
            var spec = ComparisonAndYieldQueryFactory.ComparisonCandle(selection);
            var table = await _executor.ExecuteAsync(
                DataSourceKind.Oracle,
                tablePrefix + "_SERIES_" + (index + 1).ToString(CultureInfo.InvariantCulture),
                spec,
                cancellationToken).ConfigureAwait(false);
            result[index] = MapCandleSeries(table, target, selection);
        }

        return result;
    }

    public async Task<IReadOnlyList<ComparisonYieldSeriesData>> LoadComparisonYieldPairAsync(
        ComparisonPairSceneLoadRequest request,
        string tablePrefix,
        CancellationToken cancellationToken)
    {
        var selections = ComparisonAndYieldLoadGuard.Pair(request);
        var rowLimit = ComparisonAndYieldLoadGuard.ComparisonRowLimit(request.Period);
        var result = new ComparisonYieldSeriesData[2];
        for (var index = 0; index < selections.Length; index++)
        {
            var target = index == 0
                ? ComparisonSeriesTarget.First
                : ComparisonSeriesTarget.Second;
            var selection = selections[index];
            var spec = ComparisonAndYieldQueryFactory.ComparisonYield(selection, rowLimit);
            var table = await _executor.ExecuteAsync(
                DataSourceKind.Oracle,
                tablePrefix + "_SERIES_" + (index + 1).ToString(CultureInfo.InvariantCulture),
                spec,
                cancellationToken).ConfigureAwait(false);
            result[index] = MapComparisonYieldSeries(
                table,
                target,
                selection,
                rowLimit);
        }

        return result;
    }

    public async Task<IReadOnlyList<YieldGraphPoint>> LoadSingleYieldAsync(
        YieldSceneLoadRequest request,
        string tableName,
        bool allowMissingValues,
        CancellationToken cancellationToken)
    {
        var rowLimit = ComparisonAndYieldLoadGuard.YieldRowLimit(request.Period);
        ComparisonAndYieldLoadGuard.ValidateInstrument(request.Instrument);
        var spec = ComparisonAndYieldQueryFactory.SingleYield(
            request.Instrument,
            rowLimit);
        var table = await _executor.ExecuteAsync(
            DataSourceKind.Oracle,
            tableName,
            spec,
            cancellationToken).ConfigureAwait(false);
        return MapSingleYield(table, rowLimit, allowMissingValues);
    }

    public async Task<QuotedFxYieldResult> LoadQuotedFxYieldAsync(
        YieldSceneLoadRequest request,
        string tableName,
        CancellationToken cancellationToken)
    {
        var rowLimit = ComparisonAndYieldLoadGuard.YieldRowLimit(request.Period);
        var spec = ComparisonAndYieldQueryFactory.QuotedFxYield(
            request.Instrument,
            rowLimit);
        var table = await _executor.ExecuteAsync(
            DataSourceKind.Oracle,
            tableName,
            spec,
            cancellationToken).ConfigureAwait(false);
        return MapQuotedFxYield(table, rowLimit);
    }

    private static ComparisonCandleSeriesData MapCandleSeries(
        DataTable? table,
        ComparisonSeriesTarget target,
        ComparisonStockSelection selection)
    {
        ComparisonAndYieldTableReader.RequireShape(table, 5, CandleColumns);
        var rows = table!.Rows.Cast<DataRow>().ToArray();
        var quote = ComparisonAndYieldTableReader.ComparisonQuote(
            rows[0],
            target,
            selection.Market);
        if (!string.Equals(quote.StockName, selection.StockName, StringComparison.Ordinal))
        {
            throw ComparisonAndYieldTableReader.InvalidResult();
        }
        var candles = new ComparisonCandlePoint[rows.Length];
        DateOnly? previousDate = null;
        for (var index = 0; index < rows.Length; index++)
        {
            var row = rows[index];
            ComparisonAndYieldTableReader.RequireSameQuote(row, quote);
            var date = ComparisonAndYieldTableReader.Date(row, "OPEN_DAY");
            if (previousDate.HasValue && date <= previousDate.Value)
            {
                throw ComparisonAndYieldTableReader.InvalidResult();
            }

            var high = ComparisonAndYieldTableReader.Double(row, "HIGH_VALUE");
            var low = ComparisonAndYieldTableReader.Double(row, "LOW_VALUE");
            var open = ComparisonAndYieldTableReader.Double(row, "OPEN_VALUE");
            var close = ComparisonAndYieldTableReader.Double(row, "CLOSE_VALUE");
            if (high < low)
            {
                throw ComparisonAndYieldTableReader.InvalidResult();
            }

            candles[index] = new ComparisonCandlePoint(date, high, low, open, close);
            previousDate = date;
        }

        return new ComparisonCandleSeriesData(quote, candles);
    }

    private static ComparisonYieldSeriesData MapComparisonYieldSeries(
        DataTable? table,
        ComparisonSeriesTarget target,
        ComparisonStockSelection selection,
        int rowLimit)
    {
        ComparisonAndYieldTableReader.RequireShape(
            table,
            rowLimit,
            ComparisonYieldColumns);
        var rows = table!.Rows.Cast<DataRow>().ToArray();
        var quote = ComparisonAndYieldTableReader.ComparisonQuote(
            rows[0],
            target,
            selection.Market);
        if (!string.Equals(quote.StockName, selection.StockName, StringComparison.Ordinal))
        {
            throw ComparisonAndYieldTableReader.InvalidResult();
        }
        var points = new ComparisonYieldPoint[rows.Length];
        DateOnly? previousDate = null;
        for (var index = 0; index < rows.Length; index++)
        {
            var row = rows[index];
            ComparisonAndYieldTableReader.RequireSameQuote(row, quote);
            var date = ComparisonAndYieldTableReader.Date(row, "DATA_DAY");
            if (previousDate.HasValue && date >= previousDate.Value)
            {
                throw ComparisonAndYieldTableReader.InvalidResult();
            }

            points[index] = new ComparisonYieldPoint(
                date,
                ComparisonAndYieldTableReader.Int32(row, "VALUE"));
            previousDate = date;
        }

        return new ComparisonYieldSeriesData(quote, points);
    }

    private static IReadOnlyList<YieldGraphPoint> MapSingleYield(
        DataTable? table,
        int rowLimit,
        bool allowMissingValues)
    {
        ComparisonAndYieldTableReader.RequireShape(table, rowLimit, SingleYieldColumns);
        var points = new YieldGraphPoint[rowLimit];
        DateOnly? previousDate = null;
        for (var index = 0; index < rowLimit; index++)
        {
            var row = table!.Rows[index];
            var date = ComparisonAndYieldTableReader.Date(row, "DATA_DAY");
            if (previousDate.HasValue && date >= previousDate.Value)
            {
                throw ComparisonAndYieldTableReader.InvalidResult();
            }

            var value = ComparisonAndYieldTableReader.NullableDouble(row, "VALUE");
            if ((!allowMissingValues && value is null) || (index == 0 && value is null))
            {
                throw ComparisonAndYieldTableReader.InvalidResult();
            }

            points[index] = new YieldGraphPoint(date, value);
            previousDate = date;
        }

        return points;
    }

    private static QuotedFxYieldResult MapQuotedFxYield(
        DataTable? table,
        int rowLimit)
    {
        ComparisonAndYieldTableReader.RequireShape(table, rowLimit, QuotedFxColumns);
        var points = new YieldGraphPoint[rowLimit];
        DateOnly? previousDate = null;
        for (var index = 0; index < rowLimit; index++)
        {
            var row = table!.Rows[index];
            var date = ComparisonAndYieldTableReader.Date(row, "DATA_DAY");
            if (previousDate.HasValue && date >= previousDate.Value)
            {
                throw ComparisonAndYieldTableReader.InvalidResult();
            }

            points[index] = new YieldGraphPoint(
                date,
                ComparisonAndYieldTableReader.Double(row, "VALUE"));
            previousDate = date;
        }

        var first = table!.Rows[0];
        return new QuotedFxYieldResult(
            ComparisonAndYieldTableReader.Double(first, "CHANGE_PRICE"),
            ComparisonAndYieldTableReader.Double(first, "RATE"),
            ComparisonAndYieldTableReader.YieldDirection(first, "DIRECTION"),
            points);
    }
}

internal sealed record QuotedFxYieldResult(
    double ChangePrice,
    double Rate,
    YieldQuoteDirection Direction,
    IReadOnlyList<YieldGraphPoint> Points);

internal static class ComparisonAndYieldLoadGuard
{
    public static ComparisonStockSelection[] Pair(ComparisonPairSceneLoadRequest request)
    {
        ArgumentNullException.ThrowIfNull(request);
        var first = ValidateSelection(request.First);
        var second = ValidateSelection(request.Second);
        return [first, second];
    }

    public static int ComparisonRowLimit(ComparisonGraphPeriod period) => period switch
    {
        ComparisonGraphPeriod.Daily => 1,
        ComparisonGraphPeriod.FiveDays => 5,
        ComparisonGraphPeriod.OneMonth => 20,
        ComparisonGraphPeriod.ThreeMonths => 60,
        ComparisonGraphPeriod.SixMonths => 120,
        ComparisonGraphPeriod.TwelveMonths => 240,
        _ => throw InvalidRequest()
    };

    public static int YieldRowLimit(YieldGraphPeriod period) => period switch
    {
        YieldGraphPeriod.FiveDays => 5,
        YieldGraphPeriod.TwentyDays => 20,
        YieldGraphPeriod.SixtyDays => 60,
        YieldGraphPeriod.OneHundredTwentyDays => 120,
        YieldGraphPeriod.TwoHundredFortyDays => 240,
        _ => throw InvalidRequest()
    };

    public static void ValidateInstrument(YieldGraphInstrument? instrument)
    {
        var value = LegacySceneBuilderGuard.NotNull(instrument, "Instrument");
        if (!Enum.IsDefined(value.Kind))
        {
            throw InvalidRequest();
        }

        var requiresName = value.Kind is
            YieldGraphInstrumentKind.KospiIndustry or
            YieldGraphInstrumentKind.KosdaqIndustry or
            YieldGraphInstrumentKind.KospiStock or
            YieldGraphInstrumentKind.KosdaqStock;
        if (requiresName)
        {
            _ = LegacySceneBuilderGuard.Text(value.Name, "Instrument.Name");
        }
        else if (!string.IsNullOrEmpty(value.Name))
        {
            throw InvalidRequest();
        }
    }

    public static bool IsFx(YieldGraphInstrumentKind kind) => kind is
        YieldGraphInstrumentKind.WonDollar or
        YieldGraphInstrumentKind.WonYen or
        YieldGraphInstrumentKind.WonYuan or
        YieldGraphInstrumentKind.WonEuro;

    public static LegacySceneDataException InvalidRequest() =>
        new("The comparison or yield request contains an unsupported closed selection.");

    private static ComparisonStockSelection ValidateSelection(
        ComparisonStockSelection? selection)
    {
        var value = LegacySceneBuilderGuard.NotNull(selection, "StockSelection");
        if (!Enum.IsDefined(value.Market))
        {
            throw InvalidRequest();
        }

        var stockCode = value.StockCode;
        if (stockCode is null || stockCode.Length is < 1 or > 32 ||
            stockCode.Any(static character =>
                !(char.IsAsciiLetterOrDigit(character) || character is '.' or '_' or '-')))
        {
            throw InvalidRequest();
        }

        return value with
        {
            StockName = LegacySceneBuilderGuard.Text(value.StockName, "StockName"),
            StockCode = stockCode
        };
    }
}

internal static class ComparisonAndYieldTableReader
{
    public static void RequireShape(
        DataTable? table,
        int expectedRows,
        IReadOnlyList<string> expectedColumns)
    {
        if (table is null || table.Rows.Count != expectedRows ||
            table.Columns.Count != expectedColumns.Count)
        {
            throw InvalidResult();
        }

        var columns = table.Columns
            .Cast<DataColumn>()
            .Select(column => column.ColumnName)
            .ToHashSet(StringComparer.OrdinalIgnoreCase);
        if (expectedColumns.Any(column => !columns.Contains(column)))
        {
            throw InvalidResult();
        }
    }

    public static ComparisonQuoteData ComparisonQuote(
        DataRow row,
        ComparisonSeriesTarget target,
        ComparisonEquityMarket market)
    {
        var currentPrice = Int64(row, "CURRENT_PRICE");
        var netChange = Int64(row, "NET_CHANGE");
        if (currentPrice <= 0 || netChange < 0)
        {
            throw InvalidResult();
        }

        return new ComparisonQuoteData(
            target,
            market,
            Text(row, "STOCK_NAME"),
            currentPrice,
            netChange,
            Double(row, "RATE"),
            ComparisonDirection(row, "DIRECTION"));
    }

    public static void RequireSameQuote(DataRow row, ComparisonQuoteData quote)
    {
        var candidate = ComparisonQuote(row, quote.Series, quote.Market);
        if (candidate != quote)
        {
            throw InvalidResult();
        }
    }

    public static string Text(DataRow row, string column)
    {
        var value = Required(row, column);
        try
        {
            return LegacySceneBuilderGuard.Text(
                Convert.ToString(value, CultureInfo.InvariantCulture),
                column);
        }
        catch (Exception exception) when (
            exception is FormatException or InvalidCastException or OverflowException)
        {
            throw InvalidResult();
        }
    }

    public static DateOnly Date(DataRow row, string column)
    {
        var value = Required(row, column);
        if (value is DateOnly dateOnly)
        {
            return dateOnly;
        }

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

        string? text;
        try
        {
            text = Convert.ToString(value, CultureInfo.InvariantCulture)?.Trim();
        }
        catch (Exception exception) when (
            exception is FormatException or InvalidCastException or OverflowException)
        {
            throw InvalidResult();
        }

        return DateOnly.TryParseExact(
            text,
            "yyyyMMdd",
            CultureInfo.InvariantCulture,
            DateTimeStyles.None,
            out var parsed)
                ? parsed
                : throw InvalidResult();
    }

    public static long Int64(DataRow row, string column)
    {
        var value = Decimal(row, column);
        if (decimal.Truncate(value) != value || value is < long.MinValue or > long.MaxValue)
        {
            throw InvalidResult();
        }

        return decimal.ToInt64(value);
    }

    public static int Int32(DataRow row, string column)
    {
        var value = Decimal(row, column);
        if (decimal.Truncate(value) != value || value is < int.MinValue or > int.MaxValue)
        {
            throw InvalidResult();
        }

        return decimal.ToInt32(value);
    }

    public static double Double(DataRow row, string column)
    {
        double value;
        try
        {
            value = Convert.ToDouble(Required(row, column), CultureInfo.InvariantCulture);
        }
        catch (Exception exception) when (
            exception is FormatException or InvalidCastException or OverflowException)
        {
            throw InvalidResult();
        }

        return double.IsFinite(value) ? value : throw InvalidResult();
    }

    public static double? NullableDouble(DataRow row, string column)
    {
        var value = row[column];
        return value is null or DBNull ? null : Double(row, column);
    }

    public static ComparisonChangeDirection ComparisonDirection(
        DataRow row,
        string column)
    {
        var value = DirectionText(row, column);
        return value switch
        {
            "1" or "\uC0C1\uD55C" => ComparisonChangeDirection.LimitUp,
            "2" or "+" or "\uC0C1\uC2B9" => ComparisonChangeDirection.Up,
            "3" or "" or "\uBCF4\uD569" => ComparisonChangeDirection.Flat,
            "5" or "-" or "\uD558\uB77D" => ComparisonChangeDirection.Down,
            "4" or "\uD558\uD55C" => ComparisonChangeDirection.LimitDown,
            _ => throw InvalidResult()
        };
    }

    public static YieldQuoteDirection YieldDirection(DataRow row, string column) =>
        DirectionText(row, column) switch
        {
            "+" or "1" or "2" or "\uC0C1\uD55C" or "\uC0C1\uC2B9" =>
                YieldQuoteDirection.Up,
            "" or "3" or "\uBCF4\uD569" => YieldQuoteDirection.Flat,
            "-" or "4" or "5" or "\uD558\uD55C" or "\uD558\uB77D" =>
                YieldQuoteDirection.Down,
            _ => throw InvalidResult()
        };

    public static LegacySceneDataException InvalidResult() =>
        new("The comparison or yield query returned an invalid result.");

    private static decimal Decimal(DataRow row, string column)
    {
        try
        {
            return Convert.ToDecimal(Required(row, column), CultureInfo.InvariantCulture);
        }
        catch (Exception exception) when (
            exception is FormatException or InvalidCastException or OverflowException)
        {
            throw InvalidResult();
        }
    }

    private static string DirectionText(DataRow row, string column)
    {
        if (row[column] is null or DBNull)
        {
            // Oracle materializes its empty-string flat marker as DBNull.
            return string.Empty;
        }

        try
        {
            return Convert.ToString(
                row[column],
                CultureInfo.InvariantCulture)?.Trim() ?? string.Empty;
        }
        catch (Exception exception) when (
            exception is FormatException or InvalidCastException or OverflowException)
        {
            throw InvalidResult();
        }
    }

    private static object Required(DataRow row, string column) =>
        row[column] is { } value && value is not DBNull
            ? value
            : throw InvalidResult();
}

internal static class ComparisonAndYieldQueryFactory
{
    public static DataQuerySpec ComparisonCandle(ComparisonStockSelection selection)
    {
        var (online, stock, history) = EquityTables(selection.Market);
        var sql = $"""
            WITH SELECTED_STOCK AS (
                SELECT :stockName STOCK_NAME, :stockCode STOCK_CODE FROM DUAL
            ), QUOTE_ROW AS (
                SELECT DISTINCT b.f_stock_wanname STOCK_NAME,
                       a.f_curr_price CURRENT_PRICE,
                       ABS(a.f_net_chg) NET_CHANGE,
                       {OracleEquityRate("a")} RATE,
                       a.f_chg_type DIRECTION
                FROM {online} a
                JOIN {stock} b ON a.f_stock_code = b.f_stock_code
                JOIN SELECTED_STOCK s ON b.f_stock_wanname = s.STOCK_NAME
                                     AND b.f_stock_code = s.STOCK_CODE
                WHERE b.f_mkt_halt = 'N'
                  AND a.f_curr_price <> 0
            ), OPEN_DAYS AS (
                SELECT OPEN_DAY
                FROM (
                    SELECT OPEN_DAY FROM v_open_day ORDER BY OPEN_DAY DESC
                )
                WHERE ROWNUM <= :rowLimit
            ), CANDLE_ROWS AS (
                SELECT h.f_data_date OPEN_DAY,
                       h.f_high_price HIGH_VALUE,
                       h.f_low_price LOW_VALUE,
                       h.f_init_price OPEN_VALUE,
                       h.f_curr_price CLOSE_VALUE
                FROM {history} h
                JOIN {stock} b ON h.f_stock_code = b.f_stock_code
                JOIN SELECTED_STOCK s ON b.f_stock_wanname = s.STOCK_NAME
                                     AND b.f_stock_code = s.STOCK_CODE
                WHERE b.f_mkt_halt = 'N'
            )
            SELECT q.STOCK_NAME, q.CURRENT_PRICE, q.NET_CHANGE, q.RATE, q.DIRECTION,
                   d.OPEN_DAY, c.HIGH_VALUE, c.LOW_VALUE, c.OPEN_VALUE, c.CLOSE_VALUE
            FROM QUOTE_ROW q
            CROSS JOIN OPEN_DAYS d
            LEFT JOIN CANDLE_ROWS c ON d.OPEN_DAY = c.OPEN_DAY
            ORDER BY d.OPEN_DAY
            """;
        return new DataQuerySpec(
            sql,
            [
                new DataQueryParameter("stockName", selection.StockName, DbType.String),
                new DataQueryParameter("stockCode", selection.StockCode, DbType.String),
                new DataQueryParameter("rowLimit", 5, DbType.Int32)
            ]);
    }

    public static DataQuerySpec ComparisonYield(
        ComparisonStockSelection selection,
        int rowLimit)
    {
        var (online, stock, history) = EquityTables(selection.Market);
        var sql = $"""
            WITH SELECTED_STOCK AS (
                SELECT :stockName STOCK_NAME, :stockCode STOCK_CODE FROM DUAL
            ), CURRENT_DAY AS (
                SELECT MAX(SUBSTR(f_data_time, 1, 8)) DATA_DAY
                FROM t_index
                WHERE f_part_code = '001'
            ), QUOTE_ROW AS (
                SELECT DISTINCT b.f_stock_wanname STOCK_NAME,
                       a.f_curr_price CURRENT_PRICE,
                       ABS(a.f_net_chg) NET_CHANGE,
                       {OracleEquityRate("a")} RATE,
                       a.f_chg_type DIRECTION
                FROM {online} a
                JOIN {stock} b ON a.f_stock_code = b.f_stock_code
                JOIN SELECTED_STOCK s ON b.f_stock_wanname = s.STOCK_NAME
                                     AND b.f_stock_code = s.STOCK_CODE
                WHERE b.f_mkt_halt = 'N'
                  AND a.f_curr_price <> 0
            ), STOCK_HISTORY AS (
                SELECT h.f_data_date DATA_DAY, h.f_curr_price VALUE
                FROM {history} h
                JOIN {stock} b ON h.f_stock_code = b.f_stock_code
                JOIN SELECTED_STOCK s ON b.f_stock_wanname = s.STOCK_NAME
                                     AND b.f_stock_code = s.STOCK_CODE
                WHERE b.f_mkt_halt = 'N'
            ), RAW_POINTS AS (
                SELECT d.open_day DATA_DAY, h.VALUE
                FROM v_open_day d
                LEFT JOIN STOCK_HISTORY h ON d.open_day = h.DATA_DAY
                WHERE d.open_day <> (SELECT DATA_DAY FROM CURRENT_DAY)
                UNION ALL
                SELECT (SELECT DATA_DAY FROM CURRENT_DAY) DATA_DAY,
                       a.f_curr_price VALUE
                FROM {online} a
                JOIN {stock} b ON a.f_stock_code = b.f_stock_code
                JOIN SELECTED_STOCK s ON b.f_stock_wanname = s.STOCK_NAME
                                     AND b.f_stock_code = s.STOCK_CODE
                WHERE b.f_mkt_halt = 'N'
                  AND a.f_curr_price <> 0
            ), BOUNDED_POINTS AS (
                SELECT DATA_DAY, VALUE
                FROM (
                    SELECT DATA_DAY, VALUE FROM RAW_POINTS ORDER BY DATA_DAY DESC
                )
                WHERE ROWNUM <= :rowLimit
            )
            SELECT q.STOCK_NAME, q.CURRENT_PRICE, q.NET_CHANGE, q.RATE, q.DIRECTION,
                   p.DATA_DAY, p.VALUE
            FROM QUOTE_ROW q
            CROSS JOIN BOUNDED_POINTS p
            ORDER BY p.DATA_DAY DESC
            """;
        return new DataQuerySpec(
            sql,
            [
                new DataQueryParameter("stockName", selection.StockName, DbType.String),
                new DataQueryParameter("stockCode", selection.StockCode, DbType.String),
                new DataQueryParameter("rowLimit", rowLimit, DbType.Int32)
            ]);
    }

    public static DataQuerySpec SingleYield(
        YieldGraphInstrument instrument,
        int rowLimit)
    {
        var raw = RawSingleYield(instrument);
        var sql = $"""
            {raw.Prefix}
            SELECT DATA_DAY, VALUE
            FROM (
                SELECT DATA_DAY, VALUE FROM RAW_POINTS ORDER BY DATA_DAY DESC
            )
            WHERE ROWNUM <= :rowLimit
            """;
        var parameters = raw.Parameters.ToList();
        parameters.Add(new DataQueryParameter("rowLimit", rowLimit, DbType.Int32));
        return new DataQuerySpec(sql, parameters);
    }

    public static DataQuerySpec QuotedFxYield(
        YieldGraphInstrument instrument,
        int rowLimit)
    {
        var inputCode = FxCode(instrument.Kind);
        var sql = $"""
            WITH SELECTED_INPUT AS (
                SELECT :inputCode INPUT_CODE FROM DUAL
            ), RAW_POINTS AS (
                SELECT a.f_data_day DATA_DAY,
                       a.f_curr_price VALUE,
                       a.f_net_chg CHANGE_PRICE,
                       {OracleFxRate("a")} RATE,
                       a.f_chg_type DIRECTION
                FROM t_week_index a
                JOIN SELECTED_INPUT i ON a.f_input_code = i.INPUT_CODE
                WHERE a.f_data_day <> (
                      SELECT MAX(t.f_data_day)
                      FROM t_input_index t
                      JOIN SELECTED_INPUT i2 ON t.f_input_code = i2.INPUT_CODE
                  )
                  AND a.f_data_day IN (SELECT open_day FROM v_open_day)
                UNION ALL
                SELECT a.f_data_day DATA_DAY,
                       a.f_curr_price VALUE,
                       a.f_net_chg CHANGE_PRICE,
                       {OracleFxRate("a")} RATE,
                       a.f_chg_type DIRECTION
                FROM t_input_index a
                JOIN SELECTED_INPUT i ON a.f_input_code = i.INPUT_CODE
                WHERE a.f_data_day = (
                      SELECT MAX(t.f_data_day)
                      FROM t_input_index t
                      JOIN SELECTED_INPUT i2 ON t.f_input_code = i2.INPUT_CODE
                  )
            )
            SELECT DATA_DAY, VALUE, CHANGE_PRICE, RATE, DIRECTION
            FROM (
                SELECT DATA_DAY, VALUE, CHANGE_PRICE, RATE, DIRECTION
                FROM RAW_POINTS
                ORDER BY DATA_DAY DESC
            )
            WHERE ROWNUM <= :rowLimit
            """;
        return new DataQuerySpec(
            sql,
            [
                new DataQueryParameter("inputCode", inputCode, DbType.String),
                new DataQueryParameter("rowLimit", rowLimit, DbType.Int32)
            ]);
    }

    private static RawYieldQuery RawSingleYield(YieldGraphInstrument instrument) =>
        instrument.Kind switch
        {
            YieldGraphInstrumentKind.WonDollar or
            YieldGraphInstrumentKind.WonYen or
            YieldGraphInstrumentKind.WonYuan or
            YieldGraphInstrumentKind.WonEuro => RawFxYield(instrument.Kind),
            YieldGraphInstrumentKind.KospiIndex => RawIndexYield(
                "t_index_his_day", "t_index", "001"),
            YieldGraphInstrumentKind.KosdaqIndex => RawIndexYield(
                "t_kosdaq_index_his_day", "t_kosdaq_index", "001"),
            YieldGraphInstrumentKind.Kospi200Index => RawIndexYield(
                "t_200_index_his_day", "t_200_index", "029"),
            YieldGraphInstrumentKind.Krx100Index => RawIndexYield(
                "t_krx100_index_his_day", "t_krx100_index", "043"),
            YieldGraphInstrumentKind.KospiIndustry => RawIndustryYield(
                instrument.Name!,
                "t_part",
                "t_index_his",
                "t_index"),
            YieldGraphInstrumentKind.KosdaqIndustry => RawIndustryYield(
                instrument.Name!,
                "t_kosdaq_part",
                "t_kosdaq_index_his",
                "t_kosdaq_index"),
            YieldGraphInstrumentKind.KospiStock => RawStockYield(
                instrument.Name!,
                "t_stock",
                "t_candle_history",
                "t_online1"),
            YieldGraphInstrumentKind.KosdaqStock => RawStockYield(
                instrument.Name!,
                "t_kosdaq_stock",
                "t_kosdaq_candle_history",
                "t_kosdaq_online1"),
            _ => throw ComparisonAndYieldLoadGuard.InvalidRequest()
        };

    private static RawYieldQuery RawFxYield(YieldGraphInstrumentKind kind)
    {
        var inputCode = FxCode(kind);
        return new RawYieldQuery(
            $"""
            WITH SELECTED_INPUT AS (
                SELECT :inputCode INPUT_CODE FROM DUAL
            ), RAW_POINTS AS (
                SELECT a.f_data_day DATA_DAY, a.f_curr_price VALUE
                FROM t_week_index a
                JOIN SELECTED_INPUT i ON a.f_input_code = i.INPUT_CODE
                WHERE a.f_data_day <> (
                      SELECT MAX(t.f_data_day)
                      FROM t_input_index t
                      JOIN SELECTED_INPUT i2 ON t.f_input_code = i2.INPUT_CODE
                  )
                  AND a.f_data_day IN (SELECT open_day FROM v_open_day)
                UNION ALL
                SELECT a.f_data_day DATA_DAY, a.f_curr_price VALUE
                FROM t_input_index a
                JOIN SELECTED_INPUT i ON a.f_input_code = i.INPUT_CODE
                WHERE a.f_data_day = (
                      SELECT MAX(t.f_data_day)
                      FROM t_input_index t
                      JOIN SELECTED_INPUT i2 ON t.f_input_code = i2.INPUT_CODE
                  )
            )
            """,
            [new DataQueryParameter("inputCode", inputCode, DbType.String)]);
    }

    private static RawYieldQuery RawIndexYield(
        string historyTable,
        string currentTable,
        string partCode) => new(
            $"""
            WITH CURRENT_DAY AS (
                SELECT MAX(SUBSTR(f_data_time, 1, 8)) DATA_DAY
                FROM {currentTable}
                WHERE f_part_code = '{partCode}'
            ), RAW_POINTS AS (
                SELECT h.f_data_day DATA_DAY, h.f_part_idx / 100 VALUE
                FROM {historyTable} h
                WHERE h.f_part_code = '{partCode}'
                  AND h.f_data_day <> (SELECT DATA_DAY FROM CURRENT_DAY)
                  AND RTRIM(h.f_data_day) IN (SELECT open_day FROM v_open_day)
                UNION ALL
                SELECT SUBSTR(c.f_data_time, 1, 8) DATA_DAY,
                       c.f_part_idx / 100 VALUE
                FROM {currentTable} c
                WHERE c.f_part_code = '{partCode}'
                  AND SUBSTR(c.f_data_time, 1, 8) = (
                      SELECT DATA_DAY FROM CURRENT_DAY
                  )
            )
            """,
            []);

    private static RawYieldQuery RawIndustryYield(
        string name,
        string partTable,
        string historyTable,
        string currentTable) => new(
            $"""
            WITH SELECTED_NAME AS (
                SELECT :instrumentName INSTRUMENT_NAME FROM DUAL
            ), SELECTED_PART AS (
                SELECT p.f_part_code PART_CODE
                FROM {partTable} p
                JOIN SELECTED_NAME n ON p.f_part_name = n.INSTRUMENT_NAME
            ), CURRENT_DAY AS (
                SELECT MAX(SUBSTR(c.f_data_time, 1, 8)) DATA_DAY
                FROM {currentTable} c
                JOIN SELECTED_PART p ON c.f_part_code = p.PART_CODE
            ), RAW_POINTS AS (
                SELECT SUBSTR(h.f_data_time, 1, 8) DATA_DAY,
                       h.f_part_idx / 100 VALUE
                FROM {historyTable} h
                JOIN SELECTED_PART p ON h.f_part_code = p.PART_CODE
                WHERE SUBSTR(h.f_data_time, 1, 8) <>
                      (SELECT DATA_DAY FROM CURRENT_DAY)
                  AND RTRIM(h.f_data_time) IN (
                      SELECT open_day || '1535' FROM v_open_day
                  )
                UNION ALL
                SELECT SUBSTR(c.f_data_time, 1, 8) DATA_DAY,
                       c.f_part_idx / 100 VALUE
                FROM {currentTable} c
                JOIN SELECTED_PART p ON c.f_part_code = p.PART_CODE
                WHERE SUBSTR(c.f_data_time, 1, 8) =
                      (SELECT DATA_DAY FROM CURRENT_DAY)
            )
            """,
            [new DataQueryParameter("instrumentName", name, DbType.String)]);

    private static RawYieldQuery RawStockYield(
        string name,
        string stockTable,
        string historyTable,
        string onlineTable) => new(
            $"""
            WITH SELECTED_NAME AS (
                SELECT :instrumentName INSTRUMENT_NAME FROM DUAL
            ), CURRENT_DAY AS (
                SELECT MAX(SUBSTR(f_data_time, 1, 8)) DATA_DAY
                FROM t_index
                WHERE f_part_code = '001'
            ), STOCK_HISTORY AS (
                SELECT h.f_data_date DATA_DAY, h.f_curr_price VALUE
                FROM {historyTable} h
                JOIN {stockTable} b ON h.f_stock_code = b.f_stock_code
                JOIN SELECTED_NAME n ON b.f_stock_wanname = n.INSTRUMENT_NAME
                WHERE b.f_mkt_halt = 'N'
            ), RAW_POINTS AS (
                SELECT d.open_day DATA_DAY, h.VALUE
                FROM v_open_day d
                LEFT JOIN STOCK_HISTORY h ON d.open_day = h.DATA_DAY
                WHERE d.open_day <> (SELECT DATA_DAY FROM CURRENT_DAY)
                UNION ALL
                SELECT (SELECT DATA_DAY FROM CURRENT_DAY) DATA_DAY,
                       o.f_curr_price VALUE
                FROM {onlineTable} o
                JOIN {stockTable} b ON o.f_stock_code = b.f_stock_code
                JOIN SELECTED_NAME n ON b.f_stock_wanname = n.INSTRUMENT_NAME
                WHERE b.f_mkt_halt = 'N'
                  AND o.f_curr_price <> 0
            )
            """,
            [new DataQueryParameter("instrumentName", name, DbType.String)]);

    private static (string Online, string Stock, string History) EquityTables(
        ComparisonEquityMarket market) => market switch
    {
        ComparisonEquityMarket.Kospi =>
            ("t_online1", "t_stock", "t_candle_history"),
        ComparisonEquityMarket.Kosdaq =>
            ("t_kosdaq_online1", "t_kosdaq_stock", "t_kosdaq_candle_history"),
        _ => throw ComparisonAndYieldLoadGuard.InvalidRequest()
    };

    private static string FxCode(YieldGraphInstrumentKind kind) => kind switch
    {
        YieldGraphInstrumentKind.WonDollar => "A09",
        YieldGraphInstrumentKind.WonYen => "A10",
        YieldGraphInstrumentKind.WonYuan => "A90",
        YieldGraphInstrumentKind.WonEuro => "A91",
        _ => throw ComparisonAndYieldLoadGuard.InvalidRequest()
    };

    private static string OracleEquityRate(string alias) => $"""
        ROUND(({alias}.f_net_chg /
               DECODE({alias}.f_chg_type,
                      '1', {alias}.f_curr_price - {alias}.f_net_chg,
                      '2', {alias}.f_curr_price - {alias}.f_net_chg,
                      '3', {alias}.f_curr_price,
                      '4', -1 * ({alias}.f_curr_price + {alias}.f_net_chg),
                      '5', -1 * ({alias}.f_curr_price + {alias}.f_net_chg))) * 100, 2)
        """;

    private static string OracleFxRate(string alias) => $"""
        ROUND(({alias}.f_net_chg /
               DECODE({alias}.f_chg_type,
                      '+', {alias}.f_curr_price - {alias}.f_net_chg,
                      '', {alias}.f_curr_price,
                      '-', {alias}.f_curr_price + {alias}.f_net_chg,
                      1)) * 100, 2)
        """;

    private sealed record RawYieldQuery(
        string Prefix,
        IReadOnlyList<DataQueryParameter> Parameters);
}
