#nullable enable

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

namespace MBN_STOCK_WEBVIEW.Core.Playout.Scenes;

public sealed record S5078SceneDataRequest(SectorIndexMarket Market);

public sealed class S5078SceneDataLoader
{
    private static readonly string[] Columns =
        ["CATEGORY", "PART_INDEX", "CHG_TYPE", "PART_CHANGE", "RATE"];

    private readonly IDataQueryExecutor _executor;

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

    public async Task<S5078SceneData> LoadAsync(
        S5078SceneDataRequest request,
        CancellationToken cancellationToken = default)
    {
        ArgumentNullException.ThrowIfNull(request);
        var spec = S5078QueryFactory.Create(request.Market);
        var table = await _executor.ExecuteAsync(
            DataSourceKind.Oracle,
            "S5078_SECTOR_INDEX",
            spec,
            cancellationToken).ConfigureAwait(false);
        var rows = ChartSceneDataLoaderReader.Rows(table, Columns, 10, 10);
        var mapped = new SectorIndexBarData[rows.Count];
        for (var index = 0; index < rows.Count; index++)
        {
            var row = rows[index];
            // These values were returned and displayed by the original query even though
            // only RATE participates in the K3D mutation. Validate them fail-closed.
            _ = ChartSceneDataLoaderReader.Double(row, "PART_INDEX");
            _ = ChartSceneDataLoaderReader.Double(row, "PART_CHANGE");
            var changeType = ChartSceneDataLoaderReader.Text(
                row,
                "CHG_TYPE",
                allowEmpty: true).Trim();
            mapped[index] = new SectorIndexBarData(
                ChartSceneDataLoaderReader.Text(row, "CATEGORY"),
                ChartSceneDataLoaderReader.Double(row, "RATE"),
                changeType == "-"
                    ? SectorBarVariant.Primary
                    : SectorBarVariant.Secondary);
        }

        var data = new S5078SceneData(request.Market, mapped);
        _ = new S5078SceneMutationBuilder().Build(data);
        return data;
    }
}

internal static class S5078QueryFactory
{
    private const string KospiQuery = """
        SELECT
            b.f_part_name CATEGORY,
            a.F_PART_IDX / 100 PART_INDEX,
            a.F_CHG_TYPE CHG_TYPE,
            a.F_PART_CHG / 100 PART_CHANGE,
            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)),
                2) RATE
        FROM t_index a, t_part b
        WHERE a.f_part_code = b.f_part_code
          AND a.f_part_code IN
              ('013', '009', '011', '016', '018', '021', '008', '007', '015', '005')
        ORDER BY a.f_part_code
        """;

    private const string KosdaqQuery = """
        SELECT
            b.f_part_name CATEGORY,
            a.F_PART_IDX / 100 PART_INDEX,
            a.F_CHG_TYPE CHG_TYPE,
            a.F_PART_CHG / 100 PART_CHANGE,
            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)),
                2) RATE
        FROM t_kosdaq_index a, t_kosdaq_part b
        WHERE a.f_part_code = b.f_part_code
          AND a.f_part_code IN
              ('159', '160', '066', '154', '065', '031', '029', '070', '024', '153')
        ORDER BY a.f_part_code
        """;

    private const string DowQuery = """
        SELECT
            a.f_input_name CATEGORY,
            ROUND(b.f_last, 2) PART_INDEX,
            b.f_sign CHG_TYPE,
            ABS(ROUND(b.f_diff, 2)) PART_CHANGE,
            ROUND(b.f_rate, 2) RATE
        FROM t_world_ix_eq_master a, t_world_ix_eq_sise b
        WHERE a.f_symb = b.f_symb
          AND a.f_symb IN
              ('DJI@DJINET', 'DJI@DJUSAU', 'DJI@DJUSCH', 'DJI@DJUSNF', 'DJI@DJUSCN',
               'DJI@DJT', 'DJI@DJU', 'DJI@DJUSFB', 'DJI@DJUSCFT', 'DJI@DJUSEE')
        """;

    private const string NasdaqQuery = """
        SELECT
            a.f_input_name CATEGORY,
            ROUND(b.f_last, 2) PART_INDEX,
            b.f_sign CHG_TYPE,
            ABS(ROUND(b.f_diff, 2)) PART_CHANGE,
            ROUND(b.f_rate, 2) RATE
        FROM t_world_ix_eq_master a, t_world_ix_eq_sise b
        WHERE a.f_symb = b.f_symb
          AND a.f_symb IN
              ('NAS@CXBT', 'NAS@NDX', 'NAS@NQSSSE', 'NAS@IXBK', 'NAS@IXIS',
               'NAS@IXUT', 'NAS@IXK', 'NAS@NBI', 'NAS@IXF', 'NAS@NDXT')
        """;

    private const string StandardAndPoorQuery = """
        SELECT
            a.f_input_name CATEGORY,
            ROUND(b.f_last, 2) PART_INDEX,
            b.f_sign CHG_TYPE,
            ABS(ROUND(b.f_diff, 2)) PART_CHANGE,
            ROUND(b.f_rate, 2) RATE
        FROM t_world_ix_eq_master a, t_world_ix_eq_sise b
        WHERE a.f_symb = b.f_symb
          AND a.f_symb IN
              ('SPI@S5ENRS', 'SPI@S5RLST', 'SPI@S5AUCO', 'SPI@S5INFT', 'SPI@S5COND',
               'SPI@S5INSU', 'SPI@S5ELUTX', 'SPI@S5HLTH', 'SPI@S5FINL', 'SPI@S5TELS')
        """;

    public static DataQuerySpec Create(SectorIndexMarket market)
    {
        var sql = market switch
        {
            SectorIndexMarket.Dow => DowQuery,
            SectorIndexMarket.Nasdaq => NasdaqQuery,
            SectorIndexMarket.StandardAndPoor => StandardAndPoorQuery,
            SectorIndexMarket.Kospi => KospiQuery,
            SectorIndexMarket.Kosdaq => KosdaqQuery,
            _ => throw ChartSceneDataLoaderReader.InvalidRequest()
        };
        return ChartSceneDataLoaderReader.Spec(sql);
    }
}

public sealed record S5079SceneDataRequest(string StockName);

public sealed class S5079SceneDataLoader
{
    private static readonly string[] Columns =
        ["STOCK_NAME", "GUSUNG_1", "GUSUNG_2", "GUSUNG_3", "GUSUNG_4", "BASE_DATE"];

    private const string Query = """
        SELECT STOCK_NAME, GUSUNG_1, GUSUNG_2, GUSUNG_3, GUSUNG_4, BASE_DATE
        FROM INPUT_GROW
        WHERE STOCK_NAME = :stockName
        """;

    private readonly IDataQueryExecutor _executor;

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

    public async Task<S5079SceneData> LoadAsync(
        S5079SceneDataRequest request,
        CancellationToken cancellationToken = default)
    {
        ArgumentNullException.ThrowIfNull(request);
        var stockName = ChartSceneDataLoaderReader.Selection(request.StockName);
        var spec = ChartSceneDataLoaderReader.Spec(
            Query,
            new DataQueryParameter("stockName", stockName, DbType.String));
        var table = await _executor.ExecuteAsync(
            DataSourceKind.Oracle,
            "INPUT_GROW",
            spec,
            cancellationToken).ConfigureAwait(false);
        var row = LegacyManualFinancialStorageCompatibility.SingleEquivalentSceneRow(
            table,
            Columns);
        var series = new GrowthMetricSeriesData[4];
        foreach (var path in Enum.GetValues<GrowthMetricPath>())
        {
            var column = "GUSUNG_" + (((int)path) + 1).ToString(CultureInfo.InvariantCulture);
            series[(int)path] = new GrowthMetricSeriesData(
                path,
                ParseGrowthSeries(ChartSceneDataLoaderReader.Text(row, column, allowEmpty: true)));
        }

        var dates = ChartSceneDataLoaderReader.Text(row, "BASE_DATE", allowEmpty: true)
            .Split('_', StringSplitOptions.None);
        if (dates.Length != 4)
        {
            throw ChartSceneDataLoaderReader.InvalidResult();
        }

        for (var index = 0; index < dates.Length; index++)
        {
            _ = LegacySceneBuilderGuard.Text(
                dates[index],
                $"Dates[{index}]",
                allowEmpty: true);
        }

        var data = new S5079SceneData(
            ChartSceneDataLoaderReader.Text(row, "STOCK_NAME"),
            series,
            dates);
        _ = new S5079SceneMutationBuilder().Build(data);
        return data;
    }

    private static IReadOnlyList<double?> ParseGrowthSeries(string value)
    {
        var tokens = value.Split('_', StringSplitOptions.None);
        if (tokens.Length != 4)
        {
            throw ChartSceneDataLoaderReader.InvalidResult();
        }

        var values = new double?[4];
        for (var index = 0; index < tokens.Length; index++)
        {
            if (tokens[index].Length == 0)
            {
                values[index] = null;
                continue;
            }

            if (!double.TryParse(
                    tokens[index],
                    NumberStyles.Float | NumberStyles.AllowThousands,
                    CultureInfo.InvariantCulture,
                    out var parsed) ||
                !double.IsFinite(parsed))
            {
                throw ChartSceneDataLoaderReader.InvalidResult();
            }

            values[index] = parsed;
        }

        return values;
    }
}

public sealed record S5080SceneDataRequest(string StockName);

public sealed class S5080SceneDataLoader
{
    private static readonly string[] Columns =
    [
        "STOCK_NAME", "GUSUNG_1", "GUSUNG_2", "GUSUNG_3",
        "GUSUNG_4", "GUSUNG_5", "GUSUNG_6"
    ];

    private const string Query = """
        SELECT STOCK_NAME, GUSUNG_1, GUSUNG_2, GUSUNG_3, GUSUNG_4, GUSUNG_5, GUSUNG_6
        FROM INPUT_SELL
        WHERE STOCK_NAME = :stockName
        """;

    private readonly IDataQueryExecutor _executor;

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

    public async Task<S5080SceneData> LoadAsync(
        S5080SceneDataRequest request,
        CancellationToken cancellationToken = default)
    {
        ArgumentNullException.ThrowIfNull(request);
        var stockName = ChartSceneDataLoaderReader.Selection(request.StockName);
        var spec = ChartSceneDataLoaderReader.Spec(
            Query,
            new DataQueryParameter("stockName", stockName, DbType.String));
        var table = await _executor.ExecuteAsync(
            DataSourceKind.Oracle,
            "INPUT_SELL",
            spec,
            cancellationToken).ConfigureAwait(false);
        var row = LegacyManualFinancialStorageCompatibility.SingleEquivalentSceneRow(
            table,
            Columns);
        var quarters = new QuarterlySalesData[6];
        for (var index = 0; index < quarters.Length; index++)
        {
            var raw = LegacyManualFinancialStorageCompatibility.RequiredQuarterStorageString(
                row,
                "GUSUNG_" + (index + 1).ToString(CultureInfo.InvariantCulture));
            quarters[index] = ParseQuarter(raw);
        }

        var data = new S5080SceneData(
            ChartSceneDataLoaderReader.Text(row, "STOCK_NAME"),
            quarters);
        _ = new S5080SceneMutationBuilder().Build(data);
        return data;
    }

    private static QuarterlySalesData ParseQuarter(string value)
    {
        var tokens = value.Split('_', StringSplitOptions.None);
        if (tokens.Length < 2)
        {
            throw ChartSceneDataLoaderReader.InvalidResult();
        }

        var number = 0;
        if (tokens[1].Length != 0 &&
            !int.TryParse(
                tokens[1],
                NumberStyles.Integer,
                CultureInfo.InvariantCulture,
                out number))
        {
            throw ChartSceneDataLoaderReader.InvalidResult();
        }

        return new QuarterlySalesData(
            LegacyManualFinancialStorageCompatibility.NormalizeQuarterLabel(tokens[0]),
            number);
    }
}

public sealed record S5083SceneDataRequest(InvestorFlowParticipant Participant);

public sealed class S5083SceneDataLoader
{
    private static readonly string[] SummaryColumns = ["MARKET", "AMOUNT"];
    private static readonly string[] TrendColumns = ["MARKET", "DATA_TIME", "PRICE"];

    private readonly IDataQueryExecutor _executor;

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

    public async Task<S5083SceneData> LoadAsync(
        S5083SceneDataRequest request,
        CancellationToken cancellationToken = default)
    {
        ArgumentNullException.ThrowIfNull(request);
        var plan = S5083QueryFactory.Create(request.Participant);
        var summaryTable = await _executor.ExecuteAsync(
            DataSourceKind.Oracle,
            "S5083_SUMMARY",
            plan.Summary,
            cancellationToken).ConfigureAwait(false);
        var trendTable = await _executor.ExecuteAsync(
            DataSourceKind.Oracle,
            "S5083_TREND",
            plan.Trend,
            cancellationToken).ConfigureAwait(false);

        var summaryRows = ChartSceneDataLoaderReader.Rows(summaryTable, SummaryColumns, 3, 3);
        var summary = summaryRows
            .Select(row => new InvestorFlowSummaryData(
                ChartSceneDataLoaderReader.Market(row, "MARKET"),
                ChartSceneDataLoaderReader.Int32(row, "AMOUNT")))
            .ToArray();

        var trendRows = ChartSceneDataLoaderReader.Rows(trendTable, TrendColumns, 3, 10_000);
        var trend = trendRows
            .Select(row => new InvestorFlowTrendPoint(
                ChartSceneDataLoaderReader.Market(row, "MARKET"),
                ChartSceneDataLoaderReader.Int32(row, "PRICE"),
                ChartSceneDataLoaderReader.Time(row, "DATA_TIME")))
            .ToArray();

        var data = new S5083SceneData(request.Participant, summary, trend);
        _ = new S5083SceneMutationBuilder().Build(data);
        return data;
    }
}

internal sealed record S5083QueryPlan(DataQuerySpec Summary, DataQuerySpec Trend);

internal static class S5083QueryFactory
{
    private const string IndividualSummary = """
        SELECT '코스피' MARKET,
               ROUND((SUM(F_SELL_turnover) - SUM(F_BUY_turnover)) / 100000000) AMOUNT
        FROM t_invest
        WHERE F_PART_CODE = '001'
          AND F_INVEST_CODE = '8000'
        UNION
        SELECT '코스닥' MARKET,
               ROUND((SUM(F_SELL_turnover) - SUM(F_BUY_turnover)) / 100000000) AMOUNT
        FROM t_kosdaq_invest
        WHERE F_PART_CODE = '001'
          AND F_INVEST_CODE = '8000'
        UNION
        SELECT '코스피200' MARKET,
               ROUND((SUM(F_SELL_turnover) / 100000000) -
                     (SUM(F_BUY_turnover) / 100000000)) AMOUNT
        FROM t_invest
        WHERE F_PART_CODE = '029'
          AND F_INVEST_CODE = '8000'
        """;

    private const string IndividualTrend = """
        SELECT '코스피' MARKET,
               f_data_time DATA_TIME,
               ROUND((F_SELL_turnover - F_BUY_turnover) / 100000000) PRICE
        FROM t_invest_his
        WHERE F_PART_CODE = '001'
          AND F_INVEST_CODE = '8000'
          AND f_data_time LIKE (SELECT MAX(open_day) || '%' FROM v_open_day)
        UNION
        SELECT '코스닥' MARKET,
               f_data_time DATA_TIME,
               ROUND((F_SELL_turnover - F_BUY_turnover) / 100000000) PRICE
        FROM t_kosdaq_invest_his
        WHERE F_PART_CODE = '001'
          AND F_INVEST_CODE = '8000'
          AND f_data_time LIKE (SELECT MAX(open_day) || '%' FROM v_open_day)
        UNION
        SELECT '코스피200' MARKET,
               f_data_time DATA_TIME,
               ROUND((F_SELL_turnover - F_BUY_turnover) / 100000000) PRICE
        FROM t_invest_his
        WHERE F_PART_CODE = '029'
          AND F_INVEST_CODE = '8000'
          AND f_data_time LIKE (SELECT MAX(open_day) || '%' FROM v_open_day)
        ORDER BY MARKET, DATA_TIME
        """;

    private const string InstitutionSummary = """
        SELECT '코스피' MARKET,
               ROUND((SUM(F_SELL_turnover) - SUM(F_BUY_turnover)) / 100000000) AMOUNT
        FROM t_invest
        WHERE F_PART_CODE = '001'
          AND F_INVEST_CODE IN ('1000', '2000', '3000', '3100', '4000', '5000', '6000', '7000')
        GROUP BY f_part_code
        UNION
        SELECT '코스닥' MARKET,
               ROUND((SUM(F_SELL_turnover) - SUM(F_BUY_turnover)) / 100000000) AMOUNT
        FROM t_kosdaq_invest
        WHERE F_PART_CODE = '001'
          AND F_INVEST_CODE IN ('1000', '2000', '3000', '3100', '4000', '5000', '6000', '7000')
        GROUP BY f_part_code
        UNION
        SELECT '코스피200' MARKET,
               ROUND((SUM(F_SELL_turnover) / 100000000) -
                     (SUM(F_BUY_turnover) / 100000000)) AMOUNT
        FROM t_invest
        WHERE F_PART_CODE = '029'
          AND F_INVEST_CODE IN ('1000', '2000', '3000', '3100', '4000', '5000', '6000', '7000')
        """;

    private const string InstitutionTrend = """
        SELECT '코스피' MARKET,
               f_data_time DATA_TIME,
               ROUND((SUM(F_SELL_turnover) - SUM(F_BUY_turnover)) / 100000000) PRICE
        FROM t_invest_his
        WHERE F_PART_CODE = '001'
          AND F_INVEST_CODE IN ('1000', '2000', '3000', '3100', '4000', '5000', '6000', '7000')
          AND f_data_time LIKE (SELECT MAX(open_day) || '%' FROM v_open_day)
        GROUP BY f_data_time
        UNION
        SELECT '코스닥' MARKET,
               f_data_time DATA_TIME,
               ROUND((SUM(F_SELL_turnover) - SUM(F_BUY_turnover)) / 100000000) PRICE
        FROM t_kosdaq_invest_his
        WHERE F_PART_CODE = '001'
          AND F_INVEST_CODE IN ('1000', '2000', '3000', '3100', '4000', '5000', '6000', '7000')
          AND f_data_time LIKE (SELECT MAX(open_day) || '%' FROM v_open_day)
        GROUP BY f_data_time
        UNION
        SELECT '코스피200' MARKET,
               f_data_time DATA_TIME,
               ROUND((SUM(F_SELL_turnover) - SUM(F_BUY_turnover)) / 100000000) PRICE
        FROM t_invest_his
        WHERE F_PART_CODE = '029'
          AND F_INVEST_CODE IN ('1000', '2000', '3000', '3100', '4000', '5000', '6000', '7000')
          AND f_data_time LIKE (SELECT MAX(open_day) || '%' FROM v_open_day)
        GROUP BY f_data_time
        ORDER BY MARKET, DATA_TIME
        """;

    private const string ForeignSummary = """
        SELECT '코스피' MARKET,
               ROUND((SUM(F_SELL_turnover) - SUM(F_BUY_turnover)) / 100000000) AMOUNT
        FROM t_invest
        WHERE F_PART_CODE = '001'
          AND F_INVEST_CODE IN ('9000', '9001')
        UNION
        SELECT '코스닥' MARKET,
               ROUND((SUM(F_SELL_turnover) - SUM(F_BUY_turnover)) / 100000000) AMOUNT
        FROM t_kosdaq_invest
        WHERE F_PART_CODE = '001'
          AND F_INVEST_CODE IN ('9000', '9001')
        UNION
        SELECT '코스피200' MARKET,
               ROUND((SUM(F_SELL_turnover) / 100000000) -
                     (SUM(F_BUY_turnover) / 100000000)) AMOUNT
        FROM t_invest
        WHERE F_PART_CODE = '029'
          AND F_INVEST_CODE IN ('9000', '9001')
        """;

    private const string ForeignTrend = """
        SELECT '코스피' MARKET,
               f_data_time DATA_TIME,
               ROUND((SUM(F_SELL_turnover) - SUM(F_BUY_turnover)) / 100000000) PRICE
        FROM t_invest_his
        WHERE F_PART_CODE = '001'
          AND F_INVEST_CODE IN ('9000', '9001')
          AND f_data_time LIKE (SELECT MAX(open_day) || '%' FROM v_open_day)
        GROUP BY f_data_time
        UNION
        SELECT '코스닥' MARKET,
               f_data_time DATA_TIME,
               ROUND((SUM(F_SELL_turnover) - SUM(F_BUY_turnover)) / 100000000) PRICE
        FROM t_kosdaq_invest_his
        WHERE F_PART_CODE = '001'
          AND F_INVEST_CODE IN ('9000', '9001')
          AND f_data_time LIKE (SELECT MAX(open_day) || '%' FROM v_open_day)
        GROUP BY f_data_time
        UNION
        SELECT '코스피200' MARKET,
               f_data_time DATA_TIME,
               ROUND((SUM(F_SELL_turnover) - SUM(F_BUY_turnover)) / 100000000) PRICE
        FROM t_invest_his
        WHERE F_PART_CODE = '029'
          AND F_INVEST_CODE IN ('9000', '9001')
          AND f_data_time LIKE (SELECT MAX(open_day) || '%' FROM v_open_day)
        GROUP BY f_data_time
        ORDER BY MARKET, DATA_TIME
        """;

    public static S5083QueryPlan Create(InvestorFlowParticipant participant)
    {
        var queries = participant switch
        {
            InvestorFlowParticipant.Individual => (IndividualSummary, IndividualTrend),
            InvestorFlowParticipant.Institution => (InstitutionSummary, InstitutionTrend),
            InvestorFlowParticipant.Foreign => (ForeignSummary, ForeignTrend),
            _ => throw ChartSceneDataLoaderReader.InvalidRequest()
        };
        return new S5083QueryPlan(
            ChartSceneDataLoaderReader.Spec(queries.Item1),
            ChartSceneDataLoaderReader.Spec(queries.Item2));
    }
}

public sealed record S5084SceneDataRequest(InvestorFlowIndex Index);

public sealed class S5084SceneDataLoader
{
    private static readonly string[] SummaryColumns =
        ["INDIVIDUAL", "FOREIGN", "INSTITUTION"];
    private static readonly string[] TrendColumns =
        ["DATA_TIME", "INDIVIDUAL", "FOREIGN", "INSTITUTION"];

    private readonly IDataQueryExecutor _executor;

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

    public async Task<S5084SceneData> LoadAsync(
        S5084SceneDataRequest request,
        CancellationToken cancellationToken = default)
    {
        ArgumentNullException.ThrowIfNull(request);
        var plan = S5084QueryFactory.Create(request.Index);
        var summaryTable = await _executor.ExecuteAsync(
            DataSourceKind.Oracle,
            "S5084_SUMMARY",
            plan.Summary,
            cancellationToken).ConfigureAwait(false);
        var trendTable = await _executor.ExecuteAsync(
            DataSourceKind.Oracle,
            "S5084_TREND",
            plan.Trend,
            cancellationToken).ConfigureAwait(false);
        var summaryRow = ChartSceneDataLoaderReader.SingleRow(summaryTable, SummaryColumns);
        var totals = new InvestorFlowTotalsData(
            ChartSceneDataLoaderReader.Int32(summaryRow, "INDIVIDUAL"),
            ChartSceneDataLoaderReader.Int32(summaryRow, "FOREIGN"),
            ChartSceneDataLoaderReader.Int32(summaryRow, "INSTITUTION"));
        var trendRows = ChartSceneDataLoaderReader.Rows(trendTable, TrendColumns, 1, 10_000);
        var trend = trendRows
            .Select(row => new InvestorFlowIndexTrendPoint(
                ChartSceneDataLoaderReader.Int32(row, "INDIVIDUAL"),
                ChartSceneDataLoaderReader.Int32(row, "FOREIGN"),
                ChartSceneDataLoaderReader.Int32(row, "INSTITUTION"),
                ChartSceneDataLoaderReader.Time(row, "DATA_TIME")))
            .ToArray();
        var data = new S5084SceneData(request.Index, totals, trend);
        _ = new S5084SceneMutationBuilder().Build(data);
        return data;
    }
}

internal sealed record S5084QueryPlan(DataQuerySpec Summary, DataQuerySpec Trend);

internal static class S5084QueryFactory
{
    private const string KospiSummary = """
        SELECT
            SUM(DECODE(
                f_invest_code,
                '8000', ROUND((SUM(F_SELL_turnover) - SUM(F_BUY_turnover)) / 100000000),
                0)) INDIVIDUAL,
            SUM(DECODE(
                f_invest_code,
                '9000', ROUND((SUM(F_SELL_turnover) - SUM(F_BUY_turnover)) / 100000000),
                '9001', ROUND((SUM(F_SELL_turnover) - SUM(F_BUY_turnover)) / 100000000),
                0)) FOREIGN,
            SUM(DECODE(
                f_invest_code,
                '8000', 0,
                '9000', 0,
                '9001', 0,
                ROUND((SUM(F_SELL_turnover) - SUM(F_BUY_turnover)) / 100000000))) INSTITUTION
        FROM t_invest
        WHERE F_PART_CODE = '001'
          AND F_INVEST_CODE IN
              ('1000', '2000', '3000', '3100', '4000', '5000', '6000', '7000',
               '8000', '9000', '9001')
        GROUP BY f_invest_code
        """;

    private const string KosdaqSummary = """
        SELECT
            SUM(DECODE(
                f_invest_code,
                '8000', ROUND((SUM(F_SELL_turnover) - SUM(F_BUY_turnover)) / 100000000),
                0)) INDIVIDUAL,
            SUM(DECODE(
                f_invest_code,
                '9000', ROUND((SUM(F_SELL_turnover) - SUM(F_BUY_turnover)) / 100000000),
                '9001', ROUND((SUM(F_SELL_turnover) - SUM(F_BUY_turnover)) / 100000000),
                0)) FOREIGN,
            SUM(DECODE(
                f_invest_code,
                '8000', 0,
                '9000', 0,
                '9001', 0,
                ROUND((SUM(F_SELL_turnover) - SUM(F_BUY_turnover)) / 100000000))) INSTITUTION
        FROM t_kosdaq_invest
        WHERE F_PART_CODE = '001'
          AND F_INVEST_CODE IN
              ('1000', '2000', '3000', '3100', '4000', '5000', '6000', '7000',
               '8000', '9000', '9001')
        GROUP BY f_invest_code
        """;

    private const string KospiTrend = """
        SELECT
            f_data_time DATA_TIME,
            SUM(p_1) INDIVIDUAL,
            SUM(p_2) FOREIGN,
            SUM(p_3) INSTITUTION
        FROM (
            SELECT
                f_data_time,
                DECODE(
                    f_invest_code,
                    '8000', ROUND((SUM(F_SELL_turnover) - SUM(F_BUY_turnover)) / 100000000),
                    0) p_1,
                DECODE(
                    f_invest_code,
                    '9000', ROUND((SUM(F_SELL_turnover) - SUM(F_BUY_turnover)) / 100000000),
                    '9001', ROUND((SUM(F_SELL_turnover) - SUM(F_BUY_turnover)) / 100000000),
                    0) p_2,
                DECODE(
                    f_invest_code,
                    '8000', 0,
                    '9000', 0,
                    '9001', 0,
                    ROUND((SUM(F_SELL_turnover) - SUM(F_BUY_turnover)) / 100000000)) p_3
            FROM t_invest_his
            WHERE F_PART_CODE = '001'
              AND F_INVEST_CODE IN
                  ('1000', '2000', '3000', '3100', '4000', '5000', '6000', '7000',
                   '8000', '9000', '9001')
              AND f_data_time LIKE (SELECT MAX(open_day) || '%' FROM v_open_day)
            GROUP BY f_data_time, f_invest_code
        )
        GROUP BY f_data_time
        ORDER BY f_data_time
        """;

    private const string KosdaqTrend = """
        SELECT
            f_data_time DATA_TIME,
            SUM(p_1) INDIVIDUAL,
            SUM(p_2) FOREIGN,
            SUM(p_3) INSTITUTION
        FROM (
            SELECT
                f_data_time,
                DECODE(
                    f_invest_code,
                    '8000', ROUND((SUM(F_SELL_turnover) - SUM(F_BUY_turnover)) / 100000000),
                    0) p_1,
                DECODE(
                    f_invest_code,
                    '9000', ROUND((SUM(F_SELL_turnover) - SUM(F_BUY_turnover)) / 100000000),
                    '9001', ROUND((SUM(F_SELL_turnover) - SUM(F_BUY_turnover)) / 100000000),
                    0) p_2,
                DECODE(
                    f_invest_code,
                    '8000', 0,
                    '9000', 0,
                    '9001', 0,
                    ROUND((SUM(F_SELL_turnover) - SUM(F_BUY_turnover)) / 100000000)) p_3
            FROM t_kosdaq_invest_his
            WHERE F_PART_CODE = '001'
              AND F_INVEST_CODE IN
                  ('1000', '2000', '3000', '3100', '4000', '5000', '6000', '7000',
                   '8000', '9000', '9001')
              AND f_data_time LIKE (SELECT MAX(open_day) || '%' FROM v_open_day)
            GROUP BY f_data_time, f_invest_code
        )
        GROUP BY f_data_time
        ORDER BY f_data_time
        """;

    public static S5084QueryPlan Create(InvestorFlowIndex index)
    {
        var queries = index switch
        {
            InvestorFlowIndex.Kospi => (KospiSummary, KospiTrend),
            InvestorFlowIndex.Kosdaq => (KosdaqSummary, KosdaqTrend),
            _ => throw ChartSceneDataLoaderReader.InvalidRequest()
        };
        return new S5084QueryPlan(
            ChartSceneDataLoaderReader.Spec(queries.Item1),
            ChartSceneDataLoaderReader.Spec(queries.Item2));
    }
}

internal static class ChartSceneDataLoaderReader
{
    private const int MaximumSelectionLength = 256;
    private const int MaximumTextLength = 4_096;

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

    public static string Selection(string? value)
    {
        if (string.IsNullOrWhiteSpace(value) || value.Length > MaximumSelectionLength ||
            value.Any(char.IsControl))
        {
            throw InvalidRequest();
        }

        return value;
    }

    public static DataRow SingleRow(DataTable? table, IReadOnlyList<string> columns) =>
        Rows(table, columns, 1, 1)[0];

    public static IReadOnlyList<DataRow> Rows(
        DataTable? table,
        IReadOnlyList<string> columns,
        int minimum,
        int maximum)
    {
        if (table is null || table.Columns.Count != columns.Count ||
            table.Rows.Count < minimum || table.Rows.Count > maximum)
        {
            throw InvalidResult();
        }

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

        return table.Rows.Cast<DataRow>().ToArray();
    }

    public static string Text(DataRow row, string column, bool allowEmpty = false)
    {
        if (row[column] is not { } value || value is DBNull)
        {
            throw InvalidResult();
        }

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

        if (text.Length > MaximumTextLength || text.Any(char.IsControl) ||
            (!allowEmpty && string.IsNullOrWhiteSpace(text)))
        {
            throw InvalidResult();
        }

        return text;
    }

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

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

            return decimal.ToInt32(value);
        }
        catch (Exception exception) when (
            exception is FormatException or InvalidCastException or OverflowException)
        {
            throw InvalidResult();
        }
    }

    public static InvestorFlowMarket Market(DataRow row, string column) =>
        Text(row, column).Trim() switch
        {
            "코스피" => InvestorFlowMarket.Kospi,
            "코스닥" => InvestorFlowMarket.Kosdaq,
            "코스피200" => InvestorFlowMarket.Kospi200,
            _ => throw InvalidResult()
        };

    public static TimeOnly Time(DataRow row, string column)
    {
        var value = Value(row, column);
        if (value is DateTime dateTime)
        {
            return TimeOnly.FromDateTime(dateTime);
        }

        var text = Text(row, column).Trim();
        if (text.Length < 4)
        {
            throw InvalidResult();
        }

        var timeText = text[^4..];
        if (!TimeOnly.TryParseExact(
                timeText,
                "HHmm",
                CultureInfo.InvariantCulture,
                DateTimeStyles.None,
                out var time))
        {
            throw InvalidResult();
        }

        return time;
    }

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

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

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