#nullable enable

using System.Data;
using MMoneyCoderSharp.Data;

namespace MBN_STOCK_WEBVIEW.Core.Playout.Scenes;

public sealed record S5037SceneLoadRequest(
    LegacyDomesticMarket Market,
    string StockName);

public sealed class S5037SceneDataLoader
{
    private static readonly string[] QuoteColumns =
        ["STOCK_NAME", "CURRENT_PRICE", "CHANGE_PRICE", "DIRECTION_CODE", "RATE"];
    private static readonly string[] DealerColumns = ["DEALER_NAME", "VOLUME"];

    private readonly IDataQueryExecutor _executor;

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

    public async Task<S5037SceneData> LoadAsync(
        S5037SceneLoadRequest request,
        CancellationToken cancellationToken = default)
    {
        ArgumentNullException.ThrowIfNull(request);
        var stockName = LegacyTabularSceneLoaderReader.Selector(request.StockName);
        var plan = TraderQuoteSceneQueries.S5037(request.Market, stockName);
        var quoteTable = await _executor.ExecuteAsync(
            DataSourceKind.Oracle,
            "SCENE_5037_QUOTE",
            plan.Quote,
            cancellationToken).ConfigureAwait(false);
        var buyTable = await _executor.ExecuteAsync(
            DataSourceKind.Oracle,
            "SCENE_5037_BUY",
            plan.Buy,
            cancellationToken).ConfigureAwait(false);
        var sellTable = await _executor.ExecuteAsync(
            DataSourceKind.Oracle,
            "SCENE_5037_SELL",
            plan.Sell,
            cancellationToken).ConfigureAwait(false);

        var quote = LegacyTabularSceneLoaderReader.ExactRows(quoteTable, 1, QuoteColumns)[0];
        var returnedName = LegacyTabularSceneLoaderReader.Text(quote, "STOCK_NAME");
        if (!string.Equals(returnedName, stockName, StringComparison.Ordinal))
        {
            throw LegacyTabularSceneLoaderReader.InvalidResult();
        }

        var data = new S5037SceneData(
            returnedName,
            LegacyTabularSceneLoaderReader.Int64(quote, "CURRENT_PRICE"),
            LegacyTabularSceneLoaderReader.Int64(quote, "CHANGE_PRICE"),
            LegacyTabularSceneLoaderReader.Double(quote, "RATE"),
            LegacyTabularSceneLoaderReader.Direction(quote, "DIRECTION_CODE"),
            MapDealers(buyTable),
            MapDealers(sellTable));
        return LegacyTabularSceneLoaderReader.Preflight(new S5037SceneMutationBuilder(), data);
    }

    private static IReadOnlyList<DealerVolumeData> MapDealers(DataTable? table)
    {
        var rows = LegacyTabularSceneLoaderReader.ExactRows(table, 5, DealerColumns);
        var dealers = new DealerVolumeData[rows.Count];
        long? previousVolume = null;
        for (var index = 0; index < rows.Count; index++)
        {
            var volume = LegacyTabularSceneLoaderReader.Int64(rows[index], "VOLUME");
            if (previousVolume.HasValue && volume > previousVolume.Value)
            {
                throw LegacyTabularSceneLoaderReader.InvalidResult();
            }

            previousVolume = volume;
            dealers[index] = new DealerVolumeData(
                LegacyTabularSceneLoaderReader.Text(rows[index], "DEALER_NAME"),
                volume);
        }

        return dealers;
    }
}

public sealed record S8003SceneLoadRequest(
    LegacyDomesticMarket Market,
    string StockCode);

public sealed class S8003SceneDataLoader
{
    private static readonly string[] Columns =
    [
        "STOCK_NAME", "CURRENT_PRICE", "CHANGE_PRICE", "DIRECTION_CODE", "RATE",
        "SELL_PRICE_1", "SELL_PRICE_2", "SELL_PRICE_3",
        "SELL_QUANTITY_1", "SELL_QUANTITY_2", "SELL_QUANTITY_3",
        "BUY_PRICE_1", "BUY_PRICE_2", "BUY_PRICE_3",
        "BUY_QUANTITY_1", "BUY_QUANTITY_2", "BUY_QUANTITY_3",
        "TOTAL_SELL_QUANTITY", "TOTAL_BUY_QUANTITY"
    ];

    private readonly IDataQueryExecutor _executor;

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

    public async Task<S8003SceneData> LoadAsync(
        S8003SceneLoadRequest request,
        CancellationToken cancellationToken = default)
    {
        ArgumentNullException.ThrowIfNull(request);
        var stockCode = LegacyTabularSceneLoaderReader.StockCode(request.StockCode);
        var table = await _executor.ExecuteAsync(
            DataSourceKind.Oracle,
            "SCENE_8003",
            TraderQuoteSceneQueries.S8003(request.Market, stockCode),
            cancellationToken).ConfigureAwait(false);
        var row = LegacyTabularSceneLoaderReader.ExactRows(table, 1, Columns)[0];
        var data = new S8003SceneData(
            LegacyTabularSceneLoaderReader.Text(row, "STOCK_NAME"),
            LegacyTabularSceneLoaderReader.Int64(row, "CURRENT_PRICE"),
            LegacyTabularSceneLoaderReader.Int64(row, "CHANGE_PRICE"),
            LegacyTabularSceneLoaderReader.Double(row, "RATE"),
            LegacyTabularSceneLoaderReader.Direction(row, "DIRECTION_CODE"),
            Levels(row, "BUY"),
            Levels(row, "SELL"),
            LegacyTabularSceneLoaderReader.Int64(row, "TOTAL_BUY_QUANTITY"),
            LegacyTabularSceneLoaderReader.Int64(row, "TOTAL_SELL_QUANTITY"));
        return LegacyTabularSceneLoaderReader.Preflight(new S8003SceneMutationBuilder(), data);
    }

    private static IReadOnlyList<OrderBookLevelData?> Levels(DataRow row, string side)
    {
        var levels = new OrderBookLevelData?[3];
        for (var index = 0; index < levels.Length; index++)
        {
            var number = index + 1;
            levels[index] = new OrderBookLevelData(
                LegacyTabularSceneLoaderReader.Int64(row, $"{side}_PRICE_{number}"),
                LegacyTabularSceneLoaderReader.Int64(row, $"{side}_QUANTITY_{number}"));
        }

        return levels;
    }
}

internal static class TraderQuoteSceneQueries
{
    public static TraderQueryPlan S5037(LegacyDomesticMarket market, string stockName)
    {
        var source = market switch
        {
            LegacyDomesticMarket.Kospi => new TraderSource(
                "t_online1", "t_stock", "t_dealer"),
            LegacyDomesticMarket.Kosdaq => new TraderSource(
                "t_kosdaq_online1", "t_kosdaq_stock", "t_kosdaq_dealer"),
            _ => throw LegacyTabularSceneLoaderReader.InvalidRequest()
        };
        var quote = LegacyTabularSceneLoaderReader.Spec(
            DataSourceKind.Oracle,
            $"""
            SELECT
                b.f_stock_wanname STOCK_NAME,
                a.f_curr_price CURRENT_PRICE,
                a.f_net_chg CHANGE_PRICE,
                a.f_chg_type DIRECTION_CODE,
                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 {source.OnlineTable} a
            JOIN {source.StockTable} 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
            """,
            new DataQueryParameter("stockName", stockName, DbType.String));
        return new TraderQueryPlan(
            quote,
            Dealer(source, stockName, buy: true),
            Dealer(source, stockName, buy: false));
    }

    public static DataQuerySpec S8003(LegacyDomesticMarket market, string stockCode)
    {
        var source = market switch
        {
            LegacyDomesticMarket.Kospi => new QuoteSource(
                "t_online1", "t_stock", "t_online1_call"),
            LegacyDomesticMarket.Kosdaq => new QuoteSource(
                "t_kosdaq_online1", "t_kosdaq_stock", "t_kosdaq_online1_call"),
            _ => throw LegacyTabularSceneLoaderReader.InvalidRequest()
        };
        return LegacyTabularSceneLoaderReader.Spec(
            DataSourceKind.Oracle,
            $"""
            SELECT
                b.f_stock_wanname STOCK_NAME,
                a.f_curr_price CURRENT_PRICE,
                a.f_net_chg CHANGE_PRICE,
                a.f_chg_type DIRECTION_CODE,
                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,
                c.f_call_sell_1 SELL_PRICE_1,
                c.f_call_sell_2 SELL_PRICE_2,
                c.f_call_sell_3 SELL_PRICE_3,
                c.f_call_sell_jan_1 SELL_QUANTITY_1,
                c.f_call_sell_jan_2 SELL_QUANTITY_2,
                c.f_call_sell_jan_3 SELL_QUANTITY_3,
                c.f_call_buy_1 BUY_PRICE_1,
                c.f_call_buy_2 BUY_PRICE_2,
                c.f_call_buy_3 BUY_PRICE_3,
                c.f_call_buy_jan_1 BUY_QUANTITY_1,
                c.f_call_buy_jan_2 BUY_QUANTITY_2,
                c.f_call_buy_jan_3 BUY_QUANTITY_3,
                c.f_t_call_sell_jan TOTAL_SELL_QUANTITY,
                c.f_t_call_buy_jan TOTAL_BUY_QUANTITY
            FROM {source.OnlineTable} a
            JOIN {source.StockTable} b ON a.f_stock_code = b.f_stock_code
            JOIN {source.CallTable} c ON a.f_stock_code = c.f_stock_code
            WHERE a.f_mkt_halt = 'N'
              AND a.f_curr_price > 0
              AND a.f_stock_code = :stockCode
            """,
            new DataQueryParameter("stockCode", stockCode, DbType.String));
    }

    private static DataQuerySpec Dealer(TraderSource source, string stockName, bool buy)
    {
        var side = buy ? "buy" : "sell";
        return LegacyTabularSceneLoaderReader.Spec(
            DataSourceKind.Oracle,
            $"""
            WITH target_stock AS (
                SELECT f_stock_code
                FROM {source.StockTable}
                WHERE f_stock_wanname = :stockName
                  AND f_mkt_halt = 'N'
            ), dealer_rows AS (
                SELECT 1 slot_order, b.f_sale_nick_kname dealer_name,
                       a.f_{side}_dealer_vol_1 volume
                FROM {source.DealerTable} a
                JOIN target_stock c ON a.f_stock_code = c.f_stock_code
                JOIN t_sale_member b ON a.f_{side}_dealer_no_1 = b.f_sale_no
                UNION ALL
                SELECT 2, b.f_sale_nick_kname, a.f_{side}_dealer_vol_2
                FROM {source.DealerTable} a
                JOIN target_stock c ON a.f_stock_code = c.f_stock_code
                JOIN t_sale_member b ON a.f_{side}_dealer_no_2 = b.f_sale_no
                UNION ALL
                SELECT 3, b.f_sale_nick_kname, a.f_{side}_dealer_vol_3
                FROM {source.DealerTable} a
                JOIN target_stock c ON a.f_stock_code = c.f_stock_code
                JOIN t_sale_member b ON a.f_{side}_dealer_no_3 = b.f_sale_no
                UNION ALL
                SELECT 4, b.f_sale_nick_kname, a.f_{side}_dealer_vol_4
                FROM {source.DealerTable} a
                JOIN target_stock c ON a.f_stock_code = c.f_stock_code
                JOIN t_sale_member b ON a.f_{side}_dealer_no_4 = b.f_sale_no
                UNION ALL
                SELECT 5, b.f_sale_nick_kname, a.f_{side}_dealer_vol_5
                FROM {source.DealerTable} a
                JOIN target_stock c ON a.f_stock_code = c.f_stock_code
                JOIN t_sale_member b ON a.f_{side}_dealer_no_5 = b.f_sale_no
            )
            SELECT dealer_name DEALER_NAME, volume VOLUME
            FROM dealer_rows
            ORDER BY volume DESC, slot_order
            """,
            new DataQueryParameter("stockName", stockName, DbType.String));
    }

    internal sealed record TraderQueryPlan(
        DataQuerySpec Quote,
        DataQuerySpec Buy,
        DataQuerySpec Sell);

    private sealed record TraderSource(
        string OnlineTable,
        string StockTable,
        string DealerTable);

    private sealed record QuoteSource(
        string OnlineTable,
        string StockTable,
        string CallTable);
}
