#nullable enable

using System.Data;
using MMoneyCoderSharp.Data;

namespace MBN_STOCK_WEBVIEW.Core.Playout.Scenes;

internal sealed record ParameterizedSceneQuery(
    DataSourceKind Source,
    string TableName,
    DataQuerySpec Spec);

internal static class ParameterizedMarketSceneQueries
{
    public static ParameterizedSceneQuery WorldFuture(string symbol) =>
        Oracle(
            "SCENE_WORLD_FUTURE_QUOTE",
            WorldFutureQuery,
            new DataQueryParameter("symbol", symbol, DbType.String));

    public static ParameterizedSceneQuery MarketQuote(
        LegacyMarketQuoteTarget target,
        bool expected)
    {
        S5001SceneMutationBuilder.RequireDefined(target, nameof(target));
        return expected ? ExpectedMarketQuote(target) : CurrentMarketQuote(target);
    }

    public static ParameterizedSceneQuery FuturesDetail() =>
        Oracle(
            "SCENE_FUTURES_DETAIL",
            CurrentFuturesDetailQuery,
            new DataQueryParameter("stockSequence", 1, DbType.Int32),
            new DataQueryParameter("monthCategory", "1", DbType.String));

    public static ParameterizedSceneQuery DomesticIndustry(
        LegacyDomesticEquityMarket market,
        string industryName) => market switch
        {
            LegacyDomesticEquityMarket.Kospi => Oracle(
                "SCENE_KOSPI_INDUSTRY",
                KospiIndustryQuery,
                new DataQueryParameter("industryName", industryName, DbType.String)),
            LegacyDomesticEquityMarket.Kosdaq => Oracle(
                "SCENE_KOSDAQ_INDUSTRY",
                KosdaqIndustryQuery,
                new DataQueryParameter("industryName", industryName, DbType.String)),
            LegacyDomesticEquityMarket.NxtKospi or LegacyDomesticEquityMarket.NxtKosdaq =>
                throw new LegacySceneDataException(
                    "The original DataManager has no NXT industry request implementation."),
            _ => throw new LegacySceneDataException("Unsupported domestic industry market.")
        };

    public static ParameterizedSceneQuery ForeignIndustry(string industryName) =>
        Oracle(
            "SCENE_FOREIGN_INDUSTRY",
            ForeignIndustryQuery,
            new DataQueryParameter("industryName", industryName, DbType.String));

    /// <summary>
    /// Exact UC5 industry route. All master identity fields selected by the
    /// operator are bound so duplicate Korean display names remain distinct.
    /// </summary>
    public static ParameterizedSceneQuery ForeignIndustry(
        string industryName,
        string inputName,
        string symbol,
        string nationCode) =>
        Oracle(
            "SCENE_FOREIGN_INDUSTRY",
            ForeignIndustryExactQuery,
            new DataQueryParameter("industryName", industryName, DbType.String),
            new DataQueryParameter("inputName", inputName, DbType.String),
            new DataQueryParameter("symbol", symbol, DbType.String),
            new DataQueryParameter("nationCode", nationCode, DbType.String));

    public static ParameterizedSceneQuery ForeignStock(string stockName) =>
        Oracle(
            "SCENE_FOREIGN_STOCK",
            ForeignStockQuery,
            new DataQueryParameter("stockName", stockName, DbType.String));

    /// <summary>
    /// Exact UC5 identity route. It deliberately does not inherit the old
    /// name-only US/TW scope: symbol and nation are both bound, so a shared
    /// input name cannot select a different master row.
    /// </summary>
    public static ParameterizedSceneQuery ForeignStock(
        string stockName,
        string symbol,
        string nationCode) =>
        Oracle(
            "SCENE_FOREIGN_STOCK",
            ForeignStockExactQuery,
            new DataQueryParameter("stockName", stockName, DbType.String),
            new DataQueryParameter("symbol", symbol, DbType.String),
            new DataQueryParameter("nationCode", nationCode, DbType.String));

    public static ParameterizedSceneQuery CurrentStock(
        LegacyDomesticEquityMarket market,
        string stockName,
        bool halted)
    {
        if (halted)
        {
            return market switch
            {
                LegacyDomesticEquityMarket.Kospi => Oracle(
                    "SCENE_HALTED_KOSPI_STOCK",
                    HaltedKospiStockQuery,
                    new DataQueryParameter("stockName", stockName, DbType.String)),
                LegacyDomesticEquityMarket.Kosdaq => Oracle(
                    "SCENE_HALTED_KOSDAQ_STOCK",
                    HaltedKosdaqStockQuery,
                    new DataQueryParameter("stockName", stockName, DbType.String)),
                _ => throw new LegacySceneDataException("Unsupported halted stock market.")
            };
        }

        return market switch
        {
            LegacyDomesticEquityMarket.Kospi => Oracle(
                "SCENE_KOSPI_STOCK",
                KospiStockQuery,
                new DataQueryParameter("stockName", stockName, DbType.String)),
            LegacyDomesticEquityMarket.Kosdaq => Oracle(
                "SCENE_KOSDAQ_STOCK",
                KosdaqStockQuery,
                new DataQueryParameter("stockName", stockName, DbType.String)),
            LegacyDomesticEquityMarket.NxtKospi => Maria(
                "SCENE_NXT_KOSPI_STOCK",
                NxtKospiStockQuery,
                new DataQueryParameter("stockName", stockName, DbType.String)),
            LegacyDomesticEquityMarket.NxtKosdaq => Maria(
                "SCENE_NXT_KOSDAQ_STOCK",
                NxtKosdaqStockQuery,
                new DataQueryParameter("stockName", stockName, DbType.String)),
            _ => throw new LegacySceneDataException("Unsupported current stock market.")
        };
    }

    public static ParameterizedSceneQuery ExpectedStock(
        LegacyDomesticEquityMarket market,
        string stockName) => market switch
        {
            LegacyDomesticEquityMarket.Kospi => Oracle(
                "SCENE_EXPECTED_KOSPI_STOCK",
                ExpectedKospiStockQuery,
                new DataQueryParameter("stockName", stockName, DbType.String)),
            LegacyDomesticEquityMarket.Kosdaq => Oracle(
                "SCENE_EXPECTED_KOSDAQ_STOCK",
                ExpectedKosdaqStockQuery,
                new DataQueryParameter("stockName", stockName, DbType.String)),
            _ => throw new LegacySceneDataException("Unsupported expected stock market.")
        };

    public static ParameterizedSceneQuery AfterHoursStock(
        LegacyDomesticEquityMarket market,
        string stockName) => market switch
        {
            LegacyDomesticEquityMarket.Kospi => Oracle(
                "SCENE_AFTER_HOURS_KOSPI_STOCK",
                AfterHoursKospiStockQuery,
                new DataQueryParameter("stockName", stockName, DbType.String)),
            LegacyDomesticEquityMarket.Kosdaq => Oracle(
                "SCENE_AFTER_HOURS_KOSDAQ_STOCK",
                AfterHoursKosdaqStockQuery,
                new DataQueryParameter("stockName", stockName, DbType.String)),
            _ => throw new LegacySceneDataException("Unsupported after-hours stock market.")
        };

    public static ParameterizedSceneQuery Sectors(S8001Market market) => market switch
    {
        S8001Market.Kospi => Oracle(
            "SCENE_8001_KOSPI_SECTORS",
            KospiSectorsQuery),
        S8001Market.Kosdaq => Oracle(
            "SCENE_8001_KOSDAQ_SECTORS",
            KosdaqSectorsQuery,
            KosdaqSectorParameters()),
        _ => throw new LegacySceneDataException("Unsupported s8001 market.")
    };

    private static ParameterizedSceneQuery CurrentMarketQuote(LegacyMarketQuoteTarget target) => target switch
    {
        LegacyMarketQuoteTarget.Kospi => DomesticIndex(KospiIndexQuery, "SCENE_KOSPI_INDEX", "001"),
        LegacyMarketQuoteTarget.Kosdaq => DomesticIndex(KosdaqIndexQuery, "SCENE_KOSDAQ_INDEX", "001"),
        LegacyMarketQuoteTarget.Kospi200 => DomesticIndex(Kospi200IndexQuery, "SCENE_KOSPI200_INDEX", "029"),
        LegacyMarketQuoteTarget.Krx100 => DomesticIndex(Krx100IndexQuery, "SCENE_KRX100_INDEX", "043"),
        LegacyMarketQuoteTarget.Futures => Oracle(
            "SCENE_FUTURES_INDEX",
            CurrentFuturesQuoteQuery,
            new DataQueryParameter("stockSequence", 1, DbType.Int32),
            new DataQueryParameter("monthCategory", "1", DbType.String)),
        LegacyMarketQuoteTarget.WonDollar => Exchange("원/달러", "A09"),
        LegacyMarketQuoteTarget.WonYen => Exchange("원/엔", "A10"),
        LegacyMarketQuoteTarget.WonYuan => Exchange("원/위엔", "A90"),
        LegacyMarketQuoteTarget.WonEuro => Exchange("원/유로", "A91"),
        _ => ForeignIndex(ForeignSymbol(target))
    };

    private static ParameterizedSceneQuery ExpectedMarketQuote(LegacyMarketQuoteTarget target) => target switch
    {
        LegacyMarketQuoteTarget.Kospi => DomesticIndex(ExpectedKospiIndexQuery, "SCENE_EXPECTED_KOSPI_INDEX", "001"),
        LegacyMarketQuoteTarget.Kosdaq => DomesticIndex(ExpectedKosdaqIndexQuery, "SCENE_EXPECTED_KOSDAQ_INDEX", "001"),
        LegacyMarketQuoteTarget.Kospi200 => DomesticIndex(ExpectedKospi200IndexQuery, "SCENE_EXPECTED_KOSPI200_INDEX", "029"),
        LegacyMarketQuoteTarget.Krx100 => DomesticIndex(ExpectedKrx100IndexQuery, "SCENE_EXPECTED_KRX100_INDEX", "043"),
        LegacyMarketQuoteTarget.Futures => Oracle(
            "SCENE_EXPECTED_FUTURES_INDEX",
            ExpectedFuturesQuoteQuery,
            new DataQueryParameter("stockSequence", 1, DbType.Int32),
            new DataQueryParameter("monthCategory", "1", DbType.String)),
        _ => throw new LegacySceneDataException("Expected data is unavailable for this market target.")
    };

    private static ParameterizedSceneQuery DomesticIndex(
        string sql,
        string tableName,
        string partCode) => Oracle(
        tableName,
        sql,
        new DataQueryParameter("partCode", partCode, DbType.String));

    private static ParameterizedSceneQuery Exchange(string displayName, string inputCode) => Oracle(
        "SCENE_EXCHANGE_RATE",
        ExchangeQuery,
        new DataQueryParameter("displayName", displayName, DbType.String),
        new DataQueryParameter("inputCode", inputCode, DbType.String));

    private static ParameterizedSceneQuery ForeignIndex(string symbol) => Oracle(
        "SCENE_FOREIGN_INDEX",
        ForeignIndexQuery,
        new DataQueryParameter("symbol", symbol, DbType.String));

    private static string ForeignSymbol(LegacyMarketQuoteTarget target) => target switch
    {
        LegacyMarketQuoteTarget.Dow => "DJI@DJI",
        LegacyMarketQuoteTarget.Nasdaq => "NAS@IXIC",
        LegacyMarketQuoteTarget.Sp500 => "SPI@SPX",
        LegacyMarketQuoteTarget.GermanyDax => "XTR@DAX30",
        LegacyMarketQuoteTarget.UnitedKingdomFtse => "LNS@FTSE100",
        LegacyMarketQuoteTarget.FranceCac => "PAS@CAC40",
        LegacyMarketQuoteTarget.Nikkei => "NII@NI225",
        LegacyMarketQuoteTarget.ShanghaiComposite => "SHS@000001",
        LegacyMarketQuoteTarget.HangSeng => "HSI@HSI",
        LegacyMarketQuoteTarget.TaiwanWeighted => "TWS@TI01",
        LegacyMarketQuoteTarget.SingaporeStraitsTimes => "SGI@STI",
        LegacyMarketQuoteTarget.ThailandSet => "THI@SET",
        LegacyMarketQuoteTarget.PhilippinesComposite => "PHI@COMP",
        // Preserve the original request mapping, including its historical symbol labels.
        LegacyMarketQuoteTarget.MalaysiaKlse => "MYI@JKSE",
        LegacyMarketQuoteTarget.IndonesiaComposite => "IDI@KLSE",
        _ => throw new LegacySceneDataException("Unsupported foreign market target.")
    };

    private static ParameterizedSceneQuery Oracle(
        string tableName,
        string sql,
        params DataQueryParameter[] parameters) => new(
        DataSourceKind.Oracle,
        tableName,
        new DataQuerySpec(sql, parameters));

    private static ParameterizedSceneQuery Maria(
        string tableName,
        string sql,
        params DataQueryParameter[] parameters) => new(
        DataSourceKind.MariaDb,
        tableName,
        new DataQuerySpec(sql, parameters));

    private static DataQueryParameter[] KosdaqSectorParameters() =>
    [
        StringParameter("sector01", "기계장비"),
        StringParameter("sector02", "IT부품"),
        StringParameter("sector03", "운송"),
        StringParameter("sector04", "종이목재"),
        StringParameter("sector05", "반도체"),
        StringParameter("sector06", "인터넷")
    ];

    private static DataQueryParameter StringParameter(string name, string value) =>
        new(name, value, DbType.String);

    private const string WorldFutureQuery = """
        SELECT a.f_input_name TITLE,
               ABS(ROUND(b.f_last, 2)) PRICE,
               CASE WHEN TO_NUMBER(b.f_diff) > 0 THEN '+'
                    WHEN TO_NUMBER(b.f_diff) < 0 THEN '-'
                    ELSE '' END DIRECTION,
               ABS(ROUND(b.f_diff, 2)) NET_CHANGE,
               ROUND(b.f_rate, 2) RATE
        FROM t_world_future_master a
        JOIN t_world_future_sise b ON a.f_symb = b.f_symb
        WHERE a.f_symb = :symbol
        FETCH FIRST 2 ROWS ONLY
        """;

    private const string KospiIndexQuery = """
        SELECT '코스피' TITLE,
               ROUND(f_part_idx / 100, 2) PRICE,
               f_chg_type DIRECTION,
               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
        FROM t_index
        WHERE f_part_code = :partCode
        FETCH FIRST 2 ROWS ONLY
        """;

    private const string KosdaqIndexQuery = """
        SELECT '코스닥' TITLE,
               ROUND(f_part_idx / 100, 2) PRICE,
               f_chg_type DIRECTION,
               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
        FROM t_kosdaq_index
        WHERE f_part_code = :partCode
        FETCH FIRST 2 ROWS ONLY
        """;

    private const string Kospi200IndexQuery = """
        SELECT '코스피200' TITLE,
               f_part_idx / 100 PRICE,
               f_chg_type DIRECTION,
               f_part_chg / 100 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
        FROM t_200_index
        WHERE f_part_code = :partCode
        FETCH FIRST 2 ROWS ONLY
        """;

    private const string Krx100IndexQuery = """
        SELECT 'KRX100' TITLE,
               f_part_idx / 100 PRICE,
               f_chg_type DIRECTION,
               f_part_chg / 100 NET_CHANGE,
               ROUND(f_part_chg / DECODE(f_chg_type, '+', (f_part_idx / 100) - (f_part_chg / 100),
                                                       '-', (f_part_idx / 100) + (f_part_chg / 100), 1), 2) RATE
        FROM t_krx100_index
        WHERE f_part_code = :partCode
        FETCH FIRST 2 ROWS ONLY
        """;

    private const string ExpectedKospiIndexQuery = """
        SELECT '코스피 예상체결지수' TITLE,
               ROUND(f_part_idx / 100, 2) PRICE,
               f_chg_type DIRECTION,
               ABS(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
        FROM t_fo_index
        WHERE f_part_code = :partCode
        FETCH FIRST 2 ROWS ONLY
        """;

    private const string ExpectedKosdaqIndexQuery = """
        SELECT '코스닥 예상체결지수' TITLE,
               ROUND(f_part_idx / 100, 2) PRICE,
               f_chg_type DIRECTION,
               ABS(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
        FROM t_kosdaq_fo_index
        WHERE f_part_code = :partCode
        FETCH FIRST 2 ROWS ONLY
        """;

    private const string ExpectedKospi200IndexQuery = """
        SELECT '코스피200 예상체결지수' TITLE,
               f_part_idx / 100 PRICE,
               f_chg_type DIRECTION,
               ABS(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
        FROM t_200_fo_index
        WHERE f_part_code = :partCode
        FETCH FIRST 2 ROWS ONLY
        """;

    private const string ExpectedKrx100IndexQuery = """
        SELECT 'KRX100 예상체결지수' TITLE,
               f_part_idx / 100 PRICE,
               f_chg_type DIRECTION,
               f_part_chg / 100 NET_CHANGE,
               ROUND(f_part_chg / DECODE(f_chg_type, '+', (f_part_idx / 100) - (f_part_chg / 100),
                                                       '-', (f_part_idx / 100) + (f_part_chg / 100), 1), 2) RATE
        FROM t_krx100_fo_index
        WHERE f_part_code = :partCode
        FETCH FIRST 2 ROWS ONLY
        """;

    private const string CurrentFuturesQuoteQuery = """
        SELECT '선물' TITLE,
               DECODE(a.f_curr_price, 0, b.f_jun_last_price / 100, a.f_curr_price / 100) PRICE,
               DECODE(a.f_curr_price, 0, '',
                      DECODE(SIGN(a.f_curr_price - b.f_jun_last_price * 100), 1, '+', -1, '-', 0, '')) DIRECTION,
               ABS(DECODE(a.f_curr_price, 0, 0,
                          (a.f_curr_price - b.f_jun_last_price * 100) / 100)) NET_CHANGE,
               ABS(ROUND(DECODE(a.f_curr_price, 0, 0,
                                (a.f_curr_price - b.f_jun_last_price * 100) / 100) /
                   DECODE(SIGN(a.f_curr_price - b.f_jun_last_price * 100),
                          1, DECODE(a.f_curr_price, 0, b.f_jun_last_price / 100, a.f_curr_price / 100) -
                             DECODE(a.f_curr_price, 0, 0, (a.f_curr_price - b.f_jun_last_price * 100) / 100),
                         -1, DECODE(a.f_curr_price, 0, b.f_jun_last_price / 100, a.f_curr_price / 100) +
                             DECODE(a.f_curr_price, 0, 0, (a.f_curr_price - b.f_jun_last_price * 100) / 100), 1), 2)) RATE
        FROM t_sunmul_online a
        JOIN t_sunmul_batch b ON a.f_stock_code = b.f_stock_code
        WHERE a.f_stock_seq = :stockSequence
          AND b.f_market_date = (SELECT MAX(open_day) FROM v_open_day)
          AND b.f_month_gubun = :monthCategory
        FETCH FIRST 2 ROWS ONLY
        """;

    private const string CurrentFuturesDetailQuery = """
        SELECT '선물' TITLE,
               DECODE(a.f_curr_price, 0, b.f_jun_last_price / 100, a.f_curr_price / 100) PRICE,
               DECODE(a.f_curr_price, 0, '',
                      DECODE(SIGN(a.f_curr_price - b.f_jun_last_price * 100), 1, '+', -1, '-', 0, '')) DIRECTION,
               ABS(DECODE(a.f_curr_price, 0, 0,
                          (a.f_curr_price - b.f_jun_last_price * 100) / 100)) NET_CHANGE,
               ABS(ROUND(DECODE(a.f_curr_price, 0, 0,
                                (a.f_curr_price - b.f_jun_last_price * 100) / 100) /
                   DECODE(SIGN(a.f_curr_price - b.f_jun_last_price * 100),
                          1, DECODE(a.f_curr_price, 0, b.f_jun_last_price / 100, a.f_curr_price / 100) -
                             DECODE(a.f_curr_price, 0, 0, (a.f_curr_price - b.f_jun_last_price * 100) / 100),
                         -1, DECODE(a.f_curr_price, 0, b.f_jun_last_price / 100, a.f_curr_price / 100) +
                             DECODE(a.f_curr_price, 0, 0, (a.f_curr_price - b.f_jun_last_price * 100) / 100), 1), 2)) RATE,
               a.f_net_vol NET_VOLUME,
               a.f_out_vol OUTSTANDING_VOLUME,
               a.f_net_turnover NET_TURNOVER
        FROM t_sunmul_online a
        JOIN t_sunmul_batch b ON a.f_stock_code = b.f_stock_code
        WHERE a.f_stock_seq = :stockSequence
          AND b.f_market_date = (SELECT MAX(open_day) FROM v_open_day)
          AND b.f_month_gubun = :monthCategory
        FETCH FIRST 2 ROWS ONLY
        """;

    private const string ExpectedFuturesQuoteQuery = """
        SELECT '선물 예상체결지수' TITLE,
               a.f_fo_price / 100 PRICE,
               DECODE(SIGN(a.f_fo_price - a.f_base_price), 1, '+', -1, '-', 0, '') DIRECTION,
               ABS(ROUND((a.f_fo_price - a.f_base_price) / 100, 2)) NET_CHANGE,
               ROUND(((a.f_fo_price - a.f_base_price) / 100) /
                     DECODE(SIGN(a.f_fo_price - a.f_base_price),
                            1, (a.f_fo_price / 100) - ((a.f_fo_price - a.f_base_price) / 100),
                           -1, (a.f_fo_price / 100) + ((a.f_fo_price - a.f_base_price) / 100), 1), 2) RATE
        FROM t_sunmul_online a
        JOIN t_sunmul_batch b ON a.f_stock_code = b.f_stock_code
        WHERE a.f_stock_seq = :stockSequence
          AND b.f_month_gubun = :monthCategory
        FETCH FIRST 2 ROWS ONLY
        """;

    private const string ForeignIndexQuery = """
        SELECT a.f_input_name TITLE,
               ROUND(b.f_last, 2) PRICE,
               b.f_sign DIRECTION,
               ROUND(b.f_diff, 2) NET_CHANGE,
               ROUND(b.f_rate, 2) RATE
        FROM t_world_ix_eq_master a
        JOIN t_world_ix_eq_sise b ON a.f_symb = b.f_symb
        WHERE a.f_symb = :symbol
          AND a.f_fdtc = '0'
          AND b.f_fdtc = '0'
        FETCH FIRST 2 ROWS ONLY
        """;

    private const string ExchangeQuery = """
        SELECT :displayName TITLE,
               a.f_curr_price PRICE,
               a.f_chg_type DIRECTION,
               a.f_net_chg NET_CHANGE,
               ROUND((a.f_net_chg / DECODE(a.f_chg_type,
                      '+', a.f_curr_price - a.f_net_chg,
                      '-', a.f_curr_price + a.f_net_chg, 1)) * 100, 2) RATE
        FROM t_input_index a
        JOIN t_input_code b ON a.f_input_code = b.f_input_code
        WHERE a.f_input_code = :inputCode
        FETCH FIRST 2 ROWS ONLY
        """;

    private const string KospiIndustryQuery = """
        SELECT b.f_part_name TITLE,
               ROUND(a.f_part_idx / 100, 2) PRICE,
               a.f_chg_type DIRECTION,
               ROUND(a.f_part_chg / 100, 2) NET_CHANGE,
               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
        FROM t_index a
        JOIN t_part b ON a.f_part_code = b.f_part_code
        WHERE b.f_part_name = :industryName
        FETCH FIRST 2 ROWS ONLY
        """;

    private const string KosdaqIndustryQuery = """
        SELECT b.f_part_name TITLE,
               ROUND(a.f_part_idx / 100, 2) PRICE,
               a.f_chg_type DIRECTION,
               ROUND(a.f_part_chg / 100, 2) NET_CHANGE,
               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
        FROM t_kosdaq_index a
        JOIN t_kosdaq_part b ON a.f_part_code = b.f_part_code
        WHERE b.f_part_name = :industryName
        FETCH FIRST 2 ROWS ONLY
        """;

    private const string ForeignIndustryQuery = """
        SELECT b.f_knam TITLE,
               a.f_last PRICE,
               a.f_sign DIRECTION,
               ABS(ROUND(a.f_diff, 2)) NET_CHANGE,
               ROUND(a.f_rate, 2) RATE
        FROM t_world_ix_eq_sise a
        JOIN t_world_ix_eq_master b ON a.f_symb = b.f_symb
        WHERE b.f_knam = :industryName
          AND b.f_fdtc = '0'
          AND b.f_natc = 'US'
          AND a.f_fdtc = '0'
        FETCH FIRST 2 ROWS ONLY
        """;

    private const string ForeignIndustryExactQuery = """
        SELECT b.f_knam TITLE,
               a.f_last PRICE,
               a.f_sign DIRECTION,
               ABS(ROUND(a.f_diff, 2)) NET_CHANGE,
               ROUND(a.f_rate, 2) RATE
        FROM t_world_ix_eq_sise a
        JOIN t_world_ix_eq_master b ON a.f_symb = b.f_symb
        WHERE b.f_knam = :industryName
          AND b.f_input_name = :inputName
          AND b.f_symb = :symbol
          AND b.f_natc = :nationCode
          AND b.f_fdtc = '0'
          AND a.f_fdtc = '0'
        FETCH FIRST 2 ROWS ONLY
        """;

    private const string ForeignStockQuery = """
        SELECT b.f_input_name TITLE,
               a.f_last PRICE,
               a.f_sign DIRECTION,
               ABS(ROUND(a.f_diff, 2)) NET_CHANGE,
               ROUND(a.f_rate, 2) RATE
        FROM t_world_ix_eq_sise a
        JOIN t_world_ix_eq_master b ON a.f_symb = b.f_symb
        WHERE b.f_input_name = :stockName
          AND b.f_fdtc = '1'
          AND b.f_natc IN ('US', 'TW')
          AND a.f_fdtc = '1'
        FETCH FIRST 2 ROWS ONLY
        """;

    private const string ForeignStockExactQuery = """
        SELECT b.f_input_name TITLE,
               a.f_last PRICE,
               a.f_sign DIRECTION,
               ABS(ROUND(a.f_diff, 2)) NET_CHANGE,
               ROUND(a.f_rate, 2) RATE
        FROM t_world_ix_eq_sise a
        JOIN t_world_ix_eq_master b ON a.f_symb = b.f_symb
        WHERE b.f_input_name = :stockName
          AND b.f_symb = :symbol
          AND b.f_natc = :nationCode
          AND b.f_fdtc = '1'
          AND a.f_fdtc = '1'
        FETCH FIRST 2 ROWS ONLY
        """;

    private const string KospiStockQuery = """
        SELECT DISTINCT b.f_stock_wanname TITLE,
               a.f_curr_price PRICE,
               DECODE(a.f_chg_type, '1', '상한', '2', '상승', '3', '보합', '4', '하한', '5', '하락') DIRECTION,
               ABS(a.f_net_chg) NET_CHANGE,
               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
        FROM t_online1 a
        JOIN t_stock b ON a.f_stock_code = b.f_stock_code
        WHERE b.f_mkt_halt = 'N'
          AND a.f_curr_price <> 0
          AND b.f_stock_wanname = :stockName
        FETCH FIRST 2 ROWS ONLY
        """;

    private const string KosdaqStockQuery = """
        SELECT DISTINCT b.f_stock_wanname TITLE,
               a.f_curr_price PRICE,
               DECODE(a.f_chg_type, '1', '상한', '2', '상승', '3', '보합', '4', '하한', '5', '하락') DIRECTION,
               a.f_net_chg NET_CHANGE,
               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
        FROM t_kosdaq_online1 a
        JOIN t_kosdaq_stock b ON a.f_stock_code = b.f_stock_code
        WHERE b.f_mkt_halt = 'N'
          AND a.f_curr_price <> 0
          AND b.f_stock_wanname = :stockName
        FETCH FIRST 2 ROWS ONLY
        """;

    private const string HaltedKospiStockQuery = """
        SELECT DISTINCT b.f_stock_wanname TITLE,
               a.f_curr_price PRICE,
               DECODE(a.f_chg_type, '1', '상한', '2', '상승', '3', '보합', '4', '하한', '5', '하락') DIRECTION,
               ABS(a.f_net_chg) NET_CHANGE,
               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
        FROM t_stop_online1 a
        JOIN t_stock b ON a.f_stock_code = b.f_stock_code
        WHERE b.f_mkt_halt = 'Y'
          AND a.f_curr_price <> 0
          AND b.f_stock_wanname = :stockName
        FETCH FIRST 2 ROWS ONLY
        """;

    private const string HaltedKosdaqStockQuery = """
        SELECT DISTINCT b.f_stock_wanname TITLE,
               a.f_curr_price PRICE,
               DECODE(a.f_chg_type, '1', '상한', '2', '상승', '3', '보합', '4', '하한', '5', '하락') DIRECTION,
               ABS(a.f_net_chg) NET_CHANGE,
               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
        FROM t_stop_kosdaq_online1 a
        JOIN t_kosdaq_stock b ON a.f_stock_code = b.f_stock_code
        WHERE b.f_mkt_halt = 'Y'
          AND a.f_curr_price <> 0
          AND b.f_stock_wanname = :stockName
        FETCH FIRST 2 ROWS ONLY
        """;

    private const string NxtKospiStockQuery = """
        SELECT DISTINCT b.f_stock_name TITLE,
               a.f_curr_price PRICE,
               CASE WHEN a.f_chg_type = '1' THEN '상한'
                    WHEN a.f_chg_type = '2' THEN '상승'
                    WHEN a.f_chg_type = '3' THEN '보합'
                    WHEN a.f_chg_type = '4' THEN '하한'
                    WHEN a.f_chg_type = '5' THEN '하락' END DIRECTION,
               ABS(a.f_net_chg) NET_CHANGE,
               ROUND((a.f_net_chg /
                   CASE WHEN a.f_chg_type IN ('1', '2') THEN a.f_curr_price - a.f_net_chg
                        WHEN a.f_chg_type = '3' THEN a.f_curr_price
                        WHEN a.f_chg_type IN ('4', '5') THEN -1 * (a.f_curr_price + a.f_net_chg) END) * 100, 2) RATE
        FROM n_online a
        JOIN n_stock b ON a.f_stock_code = b.f_stock_code
        WHERE b.f_stop_gubun = 'N'
          AND a.f_curr_price != 0
          AND b.f_stock_name = @stockName
        LIMIT 2
        """;

    private const string NxtKosdaqStockQuery = """
        SELECT DISTINCT b.f_stock_name TITLE,
               a.f_curr_price PRICE,
               CASE WHEN a.f_chg_type = '1' THEN '상한'
                    WHEN a.f_chg_type = '2' THEN '상승'
                    WHEN a.f_chg_type = '3' THEN '보합'
                    WHEN a.f_chg_type = '4' THEN '하한'
                    WHEN a.f_chg_type = '5' THEN '하락' END DIRECTION,
               a.f_net_chg NET_CHANGE,
               ROUND((a.f_net_chg /
                   CASE WHEN a.f_chg_type IN ('1', '2') THEN a.f_curr_price - a.f_net_chg
                        WHEN a.f_chg_type = '3' THEN a.f_curr_price
                        WHEN a.f_chg_type IN ('4', '5') THEN -1 * (a.f_curr_price + a.f_net_chg) END) * 100, 2) RATE
        FROM n_kosdaq_online a
        JOIN n_kosdaq_stock b ON a.f_stock_code = b.f_stock_code
        WHERE b.f_stop_gubun = 'N'
          AND a.f_curr_price != 0
          AND b.f_stock_name = @stockName
        LIMIT 2
        """;

    private const string ExpectedKospiStockQuery = """
        SELECT DISTINCT b.f_stock_wanname TITLE,
               a.f_fore_price PRICE,
               CASE WHEN a.f_fore_price - c.f_final_price > 0 THEN '+'
                    WHEN a.f_fore_price - c.f_final_price < 0 THEN '-'
                    ELSE '' END DIRECTION,
               ABS(a.f_fore_price - c.f_final_price) NET_CHANGE,
               ROUND(((a.f_fore_price - c.f_final_price) / c.f_final_price) * 100, 2) RATE
        FROM t_online1_call a
        JOIN t_stock b ON b.f_stock_code = a.f_stock_code
        JOIN t_batch_day c ON c.f_stock_code = a.f_stock_code
        WHERE b.f_mkt_halt = 'N'
          AND a.f_fore_price <> 0
          AND b.f_stock_wanname = :stockName
        FETCH FIRST 2 ROWS ONLY
        """;

    private const string ExpectedKosdaqStockQuery = """
        SELECT DISTINCT b.f_stock_wanname TITLE,
               a.f_fore_price PRICE,
               CASE WHEN a.f_fore_price - c.f_final_price > 0 THEN '+'
                    WHEN a.f_fore_price - c.f_final_price < 0 THEN '-'
                    ELSE '' END DIRECTION,
               ABS(a.f_fore_price - c.f_final_price) NET_CHANGE,
               ROUND(((a.f_fore_price - c.f_final_price) / c.f_final_price) * 100, 2) RATE
        FROM t_kosdaq_online1_call a
        JOIN t_kosdaq_stock b ON a.f_stock_code = b.f_stock_code
        JOIN t_kosdaq_batch_day c ON c.f_stock_code = b.f_stock_code
        WHERE b.f_mkt_halt = 'N'
          AND a.f_fore_price <> 0
          AND b.f_stock_wanname = :stockName
        FETCH FIRST 2 ROWS ONLY
        """;

    private const string AfterHoursKospiStockQuery = """
        SELECT DISTINCT b.f_stock_wanname TITLE,
               a.f_curr_price PRICE,
               DECODE(a.f_chg_type, '1', '상한', '2', '상승', '3', '보합', '4', '하한', '5', '하락') DIRECTION,
               a.f_net_chg NET_CHANGE,
               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
        FROM t_overtime_sise a
        JOIN t_stock b ON a.f_stock_code = b.f_stock_code
        WHERE b.f_mkt_halt = 'N'
          AND b.f_sect_code = 'ST'
          AND a.f_curr_price <> 0
          AND b.f_stock_wanname = :stockName
        FETCH FIRST 2 ROWS ONLY
        """;

    private const string AfterHoursKosdaqStockQuery = """
        SELECT DISTINCT b.f_stock_wanname TITLE,
               a.f_curr_price PRICE,
               DECODE(a.f_chg_type, '1', '상한', '2', '상승', '3', '보합', '4', '하한', '5', '하락') DIRECTION,
               a.f_net_chg NET_CHANGE,
               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
        FROM t_kosdaq_overtime_sise a
        JOIN t_kosdaq_stock b ON a.f_stock_code = b.f_stock_code
        WHERE b.f_mkt_halt = 'N'
          AND b.f_sect_code = 'ST'
          AND a.f_curr_price <> 0
          AND b.f_stock_wanname = :stockName
        FETCH FIRST 2 ROWS ONLY
        """;

    private const string KospiSectorsQuery = """
        SELECT a.f_part_code SECTOR_CODE,
               b.f_part_name SECTOR_NAME,
               a.f_chg_type DIRECTION,
               ABS(ROUND(a.f_part_chg /
                   DECODE(a.f_chg_type,
                          '+', (a.f_part_idx / 100) - (a.f_part_chg / 100),
                          '-', (a.f_part_idx / 100) + (a.f_part_chg / 100), 1), 2)) MAGNITUDE
        FROM t_index a
        JOIN t_part b ON a.f_part_code = b.f_part_code
        WHERE a.f_part_code IN (
            '013', '009', '011', '016', '018',
            '021', '008', '020', '007', '012',
            '015', '005', '014', '006', '019')
        ORDER BY a.f_part_code
        FETCH FIRST 15 ROWS ONLY
        """;

    private const string KosdaqSectorsQuery = """
        SELECT a.f_part_code SECTOR_CODE,
               b.f_part_name SECTOR_NAME,
               a.f_chg_type DIRECTION,
               ABS(ROUND(a.f_part_chg /
                   DECODE(a.f_chg_type,
                          '+', (a.f_part_idx / 100) - (a.f_part_chg / 100),
                          '-', (a.f_part_idx / 100) + (a.f_part_chg / 100), 1), 2)) MAGNITUDE
        FROM t_kosdaq_index a
        JOIN t_kosdaq_part b ON a.f_part_code = b.f_part_code
        WHERE b.f_part_name IN (
            :sector01, :sector02, :sector03,
            :sector04, :sector05, :sector06)
        ORDER BY a.f_part_code
        FETCH FIRST 7 ROWS ONLY
        """;
}
