#nullable enable

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

namespace MBN_STOCK_WEBVIEW.Core.Playout.Scenes;

public sealed record S5023SceneLoadRequest(LegacyDomesticMarket Market);

public sealed class S5023SceneDataLoader
{
    private static readonly string[] DailyColumns =
        ["TRADING_DATE", "INDIVIDUAL_AMOUNT", "FOREIGN_AMOUNT", "INSTITUTION_AMOUNT"];
    private static readonly string[] TotalColumns =
        ["INDIVIDUAL_AMOUNT", "FOREIGN_AMOUNT", "INSTITUTION_AMOUNT"];

    private readonly IDataQueryExecutor _executor;

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

    public async Task<S5023SceneData> LoadAsync(
        S5023SceneLoadRequest request,
        CancellationToken cancellationToken = default)
    {
        ArgumentNullException.ThrowIfNull(request);
        var plan = GridMarketSceneQueries.S5023(request.Market);
        var dailyTable = await _executor.ExecuteAsync(
            DataSourceKind.Oracle,
            "SCENE_5023_DAILY",
            plan.Daily,
            cancellationToken).ConfigureAwait(false);
        var totalTable = await _executor.ExecuteAsync(
            DataSourceKind.Oracle,
            "SCENE_5023_MONTHLY",
            plan.Monthly,
            cancellationToken).ConfigureAwait(false);

        var dailyRows = LegacyTabularSceneLoaderReader.ExactRows(dailyTable, 5, DailyColumns);
        var mappedRows = new DailyInvestorFlowData?[dailyRows.Count];
        DateOnly? previous = null;
        for (var index = 0; index < dailyRows.Count; index++)
        {
            var row = dailyRows[index];
            var date = LegacyTabularSceneLoaderReader.Date(row, "TRADING_DATE");
            if (previous.HasValue && date >= previous.Value)
            {
                throw LegacyTabularSceneLoaderReader.InvalidResult();
            }

            previous = date;
            mappedRows[index] = new DailyInvestorFlowData(
                date,
                new InvestorFlowAmounts(
                    LegacyTabularSceneLoaderReader.Int32(row, "INDIVIDUAL_AMOUNT"),
                    LegacyTabularSceneLoaderReader.Int32(row, "FOREIGN_AMOUNT"),
                    LegacyTabularSceneLoaderReader.Int32(row, "INSTITUTION_AMOUNT")));
        }

        var total = LegacyTabularSceneLoaderReader.ExactRows(totalTable, 1, TotalColumns)[0];
        var data = new S5023SceneData(
            request.Market,
            mappedRows,
            new InvestorFlowAmounts(
                LegacyTabularSceneLoaderReader.Int32(total, "INDIVIDUAL_AMOUNT"),
                LegacyTabularSceneLoaderReader.Int32(total, "FOREIGN_AMOUNT"),
                LegacyTabularSceneLoaderReader.Int32(total, "INSTITUTION_AMOUNT")));
        return LegacyTabularSceneLoaderReader.Preflight(new S5023SceneMutationBuilder(), data);
    }
}

public sealed record S5024SceneLoadRequest(LegacyDomesticMarket Market);

public sealed class S5024SceneDataLoader
{
    private static readonly string[] Columns = ["PARTICIPANT_CODE", "AMOUNT"];
    private static readonly string[] ExpectedCodes = ["INDIVIDUAL", "FOREIGN", "INSTITUTION"];

    private readonly IDataQueryExecutor _executor;

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

    public async Task<S5024SceneData> LoadAsync(
        S5024SceneLoadRequest request,
        CancellationToken cancellationToken = default)
    {
        ArgumentNullException.ThrowIfNull(request);
        var table = await _executor.ExecuteAsync(
            DataSourceKind.Oracle,
            "SCENE_5024",
            GridMarketSceneQueries.S5024(request.Market),
            cancellationToken).ConfigureAwait(false);
        var rows = LegacyTabularSceneLoaderReader.ExactRows(table, 3, Columns);
        var amounts = new InvestorTradingAmountData[rows.Count];

        for (var index = 0; index < rows.Count; index++)
        {
            var code = LegacyTabularSceneLoaderReader.Text(rows[index], "PARTICIPANT_CODE");
            if (!string.Equals(code, ExpectedCodes[index], StringComparison.Ordinal))
            {
                throw LegacyTabularSceneLoaderReader.InvalidResult();
            }

            var participant = code switch
            {
                "INDIVIDUAL" => InvestorParticipant.Individual,
                "FOREIGN" => InvestorParticipant.Foreign,
                "INSTITUTION" => InvestorParticipant.Institution,
                _ => throw LegacyTabularSceneLoaderReader.InvalidResult()
            };
            amounts[index] = new InvestorTradingAmountData(
                participant,
                LegacyTabularSceneLoaderReader.Double(rows[index], "AMOUNT"));
        }

        var data = new S5024SceneData(request.Market, amounts);
        return LegacyTabularSceneLoaderReader.Preflight(new S5024SceneMutationBuilder(), data);
    }
}

public sealed class S5085SceneDataLoader
{
    private static readonly string[] Columns = ["METRIC_CODE", "METRIC_VALUE"];
    private static readonly (string Code, ProgramTradingMetric Metric)[] Expected =
    [
        ("NET_BUY", ProgramTradingMetric.NetBuy),
        ("ARBITRAGE", ProgramTradingMetric.Arbitrage),
        ("NON_ARBITRAGE", ProgramTradingMetric.NonArbitrage),
        ("BASIS", ProgramTradingMetric.Basis),
        ("KOSPI200", ProgramTradingMetric.Kospi200),
        ("FUTURES", ProgramTradingMetric.Futures)
    ];

    private readonly IDataQueryExecutor _executor;

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

    public async Task<S5085SceneData> LoadAsync(CancellationToken cancellationToken = default)
    {
        var table = await _executor.ExecuteAsync(
            DataSourceKind.Oracle,
            "SCENE_5085",
            GridMarketSceneQueries.S5085,
            cancellationToken).ConfigureAwait(false);
        var rows = LegacyTabularSceneLoaderReader.ExactRows(table, Expected.Length, Columns);
        var metrics = new ProgramTradingMetricData[rows.Count];

        for (var index = 0; index < rows.Count; index++)
        {
            var code = LegacyTabularSceneLoaderReader.Text(rows[index], "METRIC_CODE");
            if (!string.Equals(code, Expected[index].Code, StringComparison.Ordinal))
            {
                throw LegacyTabularSceneLoaderReader.InvalidResult();
            }

            metrics[index] = new ProgramTradingMetricData(
                Expected[index].Metric,
                LegacyTabularSceneLoaderReader.Double(rows[index], "METRIC_VALUE"));
        }

        var data = new S5085SceneData(metrics);
        return LegacyTabularSceneLoaderReader.Preflight(new S5085SceneMutationBuilder(), data);
    }
}

public sealed class S6067SceneDataLoader
{
    private static readonly string[] Columns = ["MARKET_CODE", "INSTITUTION_CODE", "AMOUNT"];
    private static readonly (string MarketCode, InstitutionMarket Market)[] Markets =
    [
        ("KOSPI", InstitutionMarket.Kospi),
        ("KOSDAQ", InstitutionMarket.Kosdaq),
        ("KOSPI200", InstitutionMarket.Other)
    ];
    private static readonly (string InstitutionCode, InstitutionKind Institution)[] Institutions =
    [
        ("SECURITIES", InstitutionKind.Securities),
        ("INSURANCE", InstitutionKind.Insurance),
        ("INVESTMENT_TRUST", InstitutionKind.InvestmentTrust),
        ("BANK", InstitutionKind.Bank),
        ("FUND", InstitutionKind.Fund)
    ];

    private readonly IDataQueryExecutor _executor;

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

    public async Task<S6067SceneData> LoadAsync(CancellationToken cancellationToken = default)
    {
        var table = await _executor.ExecuteAsync(
            DataSourceKind.Oracle,
            "SCENE_6067",
            GridMarketSceneQueries.S6067,
            cancellationToken).ConfigureAwait(false);
        var rows = LegacyTabularSceneLoaderReader.ExactRows(
            table,
            Markets.Length * Institutions.Length,
            Columns);
        var mapped = new InstitutionNetBuyData[rows.Count];

        for (var index = 0; index < rows.Count; index++)
        {
            var marketIndex = index / Institutions.Length;
            var institutionIndex = index % Institutions.Length;
            var row = rows[index];
            var marketCode = LegacyTabularSceneLoaderReader.Text(row, "MARKET_CODE");
            var institutionCode = LegacyTabularSceneLoaderReader.Text(row, "INSTITUTION_CODE");
            if (!string.Equals(marketCode, Markets[marketIndex].MarketCode, StringComparison.Ordinal) ||
                !string.Equals(
                    institutionCode,
                    Institutions[institutionIndex].InstitutionCode,
                    StringComparison.Ordinal))
            {
                throw LegacyTabularSceneLoaderReader.InvalidResult();
            }

            mapped[index] = new InstitutionNetBuyData(
                Markets[marketIndex].Market,
                Institutions[institutionIndex].Institution,
                LegacyTabularSceneLoaderReader.Int32(row, "AMOUNT"));
        }

        var data = new S6067SceneData(mapped);
        return LegacyTabularSceneLoaderReader.Preflight(new S6067SceneMutationBuilder(), data);
    }
}

internal static class GridMarketSceneQueries
{
    public static (DataQuerySpec Daily, DataQuerySpec Monthly) S5023(LegacyDomesticMarket market)
    {
        var (currentTable, historyTable) = market switch
        {
            LegacyDomesticMarket.Kospi => ("t_invest", "t_invest_his"),
            LegacyDomesticMarket.Kosdaq => ("t_kosdaq_invest", "t_kosdaq_invest_his"),
            _ => throw LegacyTabularSceneLoaderReader.InvalidRequest()
        };

        var current = CurrentInvestorFlow(currentTable);
        var history = HistoryInvestorFlow(historyTable, 5);
        var monthlyHistory = HistoryInvestorFlow(historyTable, 20);
        var daily = $"""
            SELECT
                trading_date TRADING_DATE,
                individual_amount INDIVIDUAL_AMOUNT,
                foreign_amount FOREIGN_AMOUNT,
                institution_amount INSTITUTION_AMOUNT
            FROM (
                {current}
                UNION
                {history}
            )
            ORDER BY trading_date DESC
            """;
        var monthly = $"""
            SELECT
                SUM(individual_amount) INDIVIDUAL_AMOUNT,
                SUM(foreign_amount) FOREIGN_AMOUNT,
                SUM(institution_amount) INSTITUTION_AMOUNT
            FROM (
                {current}
                UNION
                {monthlyHistory}
            )
            """;
        return (Oracle(daily), Oracle(monthly));
    }

    public static DataQuerySpec S5024(LegacyDomesticMarket market)
    {
        var table = market switch
        {
            LegacyDomesticMarket.Kospi => "t_invest",
            LegacyDomesticMarket.Kosdaq => "t_kosdaq_invest",
            _ => throw LegacyTabularSceneLoaderReader.InvalidRequest()
        };
        return Oracle($"""
            SELECT participant_code PARTICIPANT_CODE, amount AMOUNT
            FROM (
                SELECT
                    DECODE(
                        f_invest_code,
                        '8000', 'INDIVIDUAL',
                        '9000', 'FOREIGN',
                        '9001', 'FOREIGN',
                        'INSTITUTION') participant_code,
                    ROUND((SUM(f_sell_turnover) - SUM(f_buy_turnover)) / 100000000) amount
                FROM {table}
                WHERE f_part_code = '001'
                  AND f_invest_code IN (
                      '1000', '2000', '3000', '3100', '4000', '5000',
                      '6000', '7000', '8000', '9000', '9001')
                GROUP BY DECODE(
                    f_invest_code,
                    '8000', 'INDIVIDUAL',
                    '9000', 'FOREIGN',
                    '9001', 'FOREIGN',
                    'INSTITUTION')
            )
            ORDER BY DECODE(participant_code, 'INDIVIDUAL', 1, 'FOREIGN', 2, 3)
            """);
    }

    public static DataQuerySpec S5085 { get; } = Oracle("""
        WITH future_row AS (
            SELECT DECODE(
                       SIGN(a.f_curr_price - a.f_base_price),
                       -1, (a.f_curr_price / 100) * -1,
                       a.f_curr_price / 100) futures_value
            FROM t_sunmul_online a
            JOIN t_sunmul_batch b ON a.f_stock_code = b.f_stock_code
            WHERE a.f_stock_seq = 1
              AND b.f_market_date = (SELECT MAX(open_day) FROM v_open_day)
              AND b.f_month_gubun = '1'
            FETCH FIRST 1 ROW ONLY
        )
        SELECT metric_code METRIC_CODE, metric_value METRIC_VALUE
        FROM (
            SELECT 1 sort_order, 'NET_BUY' metric_code,
                   ROUND((
                       (f_bcon_buy_w_amt + f_bcon_buy_j_amt) -
                       (f_bcon_sell_w_amt + f_bcon_sell_j_amt) +
                       (f_ccon_buy_w_amt + f_ccon_buy_j_amt) -
                       (f_ccon_sell_w_amt + f_ccon_sell_j_amt)) / 100000000) metric_value
            FROM t_pgm_tot
            UNION ALL
            SELECT 2, 'ARBITRAGE',
                   ROUND(((f_ccon_buy_w_amt + f_ccon_buy_j_amt) -
                          (f_ccon_sell_w_amt + f_ccon_sell_j_amt)) / 100000000)
            FROM t_pgm_tot
            UNION ALL
            SELECT 3, 'NON_ARBITRAGE',
                   ROUND(((f_bcon_buy_w_amt + f_bcon_buy_j_amt) -
                          (f_bcon_sell_w_amt + f_bcon_sell_j_amt)) / 100000000)
            FROM t_pgm_tot
            UNION ALL
            SELECT 4, 'BASIS', futures_value - kospi200_value
            FROM (
                SELECT DECODE(f_chg_type, '-', (f_part_idx / 100) * -1, f_part_idx / 100) kospi200_value
                FROM t_200_index
                WHERE f_part_code = '029'
            ), future_row
            UNION ALL
            SELECT 5, 'KOSPI200',
                   DECODE(f_chg_type, '-', (f_part_idx / 100) * -1, f_part_idx / 100)
            FROM t_200_index
            WHERE f_part_code = '029'
            UNION ALL
            SELECT 6, 'FUTURES', futures_value
            FROM future_row
        )
        ORDER BY sort_order
        """);

    public static DataQuerySpec S6067 { get; } = Oracle("""
        SELECT market_code MARKET_CODE, institution_code INSTITUTION_CODE, amount AMOUNT
        FROM (
            SELECT
                1 market_order,
                DECODE(f_invest_code, '1000', 1, '2000', 2, '3000', 3, '4000', 4, 5) institution_order,
                'KOSPI' market_code,
                DECODE(
                    f_invest_code,
                    '1000', 'SECURITIES',
                    '2000', 'INSURANCE',
                    '3000', 'INVESTMENT_TRUST',
                    '4000', 'BANK',
                    '6000', 'FUND') institution_code,
                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', '4000', '6000')
            GROUP BY f_invest_code
            UNION ALL
            SELECT
                2,
                DECODE(f_invest_code, '1000', 1, '2000', 2, '3000', 3, '4000', 4, 5),
                'KOSDAQ',
                DECODE(
                    f_invest_code,
                    '1000', 'SECURITIES',
                    '2000', 'INSURANCE',
                    '3000', 'INVESTMENT_TRUST',
                    '4000', 'BANK',
                    '6000', 'FUND'),
                ROUND((SUM(f_sell_turnover) - SUM(f_buy_turnover)) / 100000000)
            FROM t_kosdaq_invest
            WHERE f_part_code = '001'
              AND f_invest_code IN ('1000', '2000', '3000', '4000', '6000')
            GROUP BY f_invest_code
            UNION ALL
            SELECT
                3,
                DECODE(f_invest_code, '1000', 1, '2000', 2, '3000', 3, '4000', 4, 5),
                'KOSPI200',
                DECODE(
                    f_invest_code,
                    '1000', 'SECURITIES',
                    '2000', 'INSURANCE',
                    '3000', 'INVESTMENT_TRUST',
                    '4000', 'BANK',
                    '6000', 'FUND'),
                ROUND((SUM(f_sell_turnover) - SUM(f_buy_turnover)) / 100000000)
            FROM t_invest
            WHERE f_part_code = '029'
              AND f_invest_code IN ('1000', '2000', '3000', '4000', '6000')
            GROUP BY f_invest_code
        )
        ORDER BY market_order, institution_order
        """);

    private static string CurrentInvestorFlow(string table) => $"""
        SELECT
            TO_CHAR((SELECT MAX(open_day) FROM v_open_day)) trading_date,
            individual_amount,
            foreign_amount,
            institution_amount
        FROM (
            SELECT
                SUM(CASE WHEN f_invest_code = '8000' THEN amount ELSE 0 END) individual_amount,
                SUM(CASE WHEN f_invest_code IN ('9000', '9001') THEN amount ELSE 0 END) foreign_amount,
                SUM(CASE WHEN f_invest_code NOT IN ('8000', '9000', '9001') THEN amount ELSE 0 END) institution_amount
            FROM (
                SELECT
                    f_invest_code,
                    ROUND((SUM(f_sell_turnover) - SUM(f_buy_turnover)) / 100000000) amount
                FROM {table}
                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 static string HistoryInvestorFlow(string table, int exclusiveRowNumber) => $"""
        SELECT
            SUBSTR(f_data_time, 1, 8) trading_date,
            SUM(CASE WHEN f_invest_code = '8000' THEN amount ELSE 0 END) individual_amount,
            SUM(CASE WHEN f_invest_code IN ('9000', '9001') THEN amount ELSE 0 END) foreign_amount,
            SUM(CASE WHEN f_invest_code NOT IN ('8000', '9000', '9001') THEN amount ELSE 0 END) institution_amount
        FROM (
            SELECT
                f_data_time,
                f_invest_code,
                ROUND((SUM(f_sell_turnover) - SUM(f_buy_turnover)) / 100000000) amount
            FROM {table}
            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 IN (
                  SELECT data_time || '1535'
                  FROM (
                      SELECT open_day data_time FROM v_open_day
                      MINUS
                      SELECT SUBSTR(f_data_time, 1, 8) data_time
                      FROM t_index
                      WHERE f_part_code = '001'
                      ORDER BY data_time DESC
                  )
                  WHERE ROWNUM < {exclusiveRowNumber})
            GROUP BY f_data_time, f_invest_code
        )
        GROUP BY SUBSTR(f_data_time, 1, 8)
        """;

    private static DataQuerySpec Oracle(string sql) =>
        LegacyTabularSceneLoaderReader.Spec(DataSourceKind.Oracle, sql);
}

internal static class LegacyTabularSceneLoaderReader
{
    private const int MaximumTextLength = 256;
    private const int MaximumSelectorLength = 128;

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

    public static IReadOnlyList<DataRow> ExactRows(
        DataTable? table,
        int rowCount,
        IReadOnlyList<string> columns)
    {
        if (table is null || table.Rows.Count != rowCount || table.Columns.Count != columns.Count)
        {
            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)
    {
        var text = Convert.ToString(Required(row, column), CultureInfo.InvariantCulture) ?? string.Empty;
        if (text.Length > MaximumTextLength)
        {
            throw InvalidResult();
        }

        try
        {
            return LegacySceneBuilderGuard.Text(text, column, allowEmpty);
        }
        catch (LegacySceneDataException)
        {
            throw InvalidResult();
        }
    }

    public static string Selector(string? value)
    {
        try
        {
            var selector = LegacySceneBuilderGuard.Text(value, "selector");
            return selector.Length <= MaximumSelectorLength
                ? selector
                : throw InvalidRequest();
        }
        catch (LegacySceneDataException)
        {
            throw InvalidRequest();
        }
    }

    public static string StockCode(string? value)
    {
        if (string.IsNullOrWhiteSpace(value) || value.Length > 32 ||
            value.Any(character => !char.IsAsciiLetterOrDigit(character)))
        {
            throw InvalidRequest();
        }

        return value;
    }

    public static DateOnly Date(DataRow row, string column)
    {
        var text = Text(row, column);
        if (text.Length != 8 || text.Any(character => !char.IsAsciiDigit(character)) ||
            !DateOnly.TryParseExact(
                text,
                "yyyyMMdd",
                CultureInfo.InvariantCulture,
                DateTimeStyles.None,
                out var value))
        {
            throw InvalidResult();
        }

        return 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 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 double Double(DataRow row, string column)
    {
        try
        {
            var value = Convert.ToDouble(Required(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 ScenePriceDirection Direction(DataRow row, string column) =>
        Text(row, column) switch
        {
            "1" => ScenePriceDirection.LimitUp,
            "2" => ScenePriceDirection.Up,
            "3" => ScenePriceDirection.Flat,
            "4" => ScenePriceDirection.LimitDown,
            "5" => ScenePriceDirection.Down,
            _ => throw InvalidResult()
        };

    public static TData Preflight<TData>(
        ILegacySceneMutationBuilder<TData> builder,
        TData data)
        where TData : ILegacySceneData
    {
        _ = builder.Build(data);
        return data;
    }

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

    public static LegacySceneDataException InvalidResult() =>
        new("The scene 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 object Required(DataRow row, string column) =>
        row[column] is { } value && value is not DBNull
            ? value
            : throw InvalidResult();
}
