#nullable enable

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

namespace MBN_STOCK_WEBVIEW.Core.Playout.Scenes;

public sealed class S5006NxtSceneDataLoader
{
    private const string KospiQuery = """
        SELECT a.f_stock_code stock_code, b.f_stock_name stock_name,
               a.f_curr_price curr_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 chg_type,
               a.f_net_chg net_chg,
               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,
               a.f_init_price init_price,
               IF(c.f_final_price = 0,
                  ROUND(((a.f_init_price - c.f_base_price) / c.f_base_price) * 100, 2),
                  ROUND(((a.f_init_price - c.f_final_price) / c.f_final_price) * 100, 2)) init_rate,
               a.f_high_price high_price,
               IF(c.f_final_price = 0,
                  ROUND(((a.f_high_price - c.f_base_price) / c.f_base_price) * 100, 2),
                  ROUND(((a.f_high_price - c.f_final_price) / c.f_final_price) * 100, 2)) high_rate,
               a.f_low_price low_price,
               IF(c.f_final_price = 0,
                  ROUND(((a.f_low_price - c.f_base_price) / c.f_base_price) * 100, 2),
                  ROUND(((a.f_low_price - c.f_final_price) / c.f_final_price) * 100, 2)) low_rate
        FROM n_online a
        JOIN n_stock b ON a.f_stock_code = b.f_stock_code
        JOIN n_batch_his c ON a.f_stock_code = c.f_stock_code
        WHERE a.f_curr_price <> 0
          AND c.f_data_day = (SELECT MAX(h.f_data_day) FROM n_batch_his h WHERE h.f_stock_code = a.f_stock_code)
          AND a.f_stock_code = @stockCode
        """;

    private const string KosdaqQuery = """
        SELECT a.f_stock_code stock_code, b.f_stock_name stock_name,
               a.f_curr_price curr_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 chg_type,
               a.f_net_chg net_chg,
               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,
               a.f_init_price init_price,
               IF(c.f_final_price = 0,
                  ROUND(((a.f_init_price - c.f_base_price) / c.f_base_price) * 100, 2),
                  ROUND(((a.f_init_price - c.f_final_price) / c.f_final_price) * 100, 2)) init_rate,
               a.f_high_price high_price,
               IF(c.f_final_price = 0,
                  ROUND(((a.f_high_price - c.f_base_price) / c.f_base_price) * 100, 2),
                  ROUND(((a.f_high_price - c.f_final_price) / c.f_final_price) * 100, 2)) high_rate,
               a.f_low_price low_price,
               IF(c.f_final_price = 0,
                  ROUND(((a.f_low_price - c.f_base_price) / c.f_base_price) * 100, 2),
                  ROUND(((a.f_low_price - c.f_final_price) / c.f_final_price) * 100, 2)) low_rate
        FROM n_kosdaq_online a
        JOIN n_kosdaq_stock b ON a.f_stock_code = b.f_stock_code
        JOIN n_kosdaq_batch_his c ON a.f_stock_code = c.f_stock_code
        WHERE a.f_curr_price <> 0
          AND c.f_data_day = (SELECT MAX(h.f_data_day) FROM n_kosdaq_batch_his h WHERE h.f_stock_code = a.f_stock_code)
          AND a.f_stock_code = @stockCode
        """;

    private readonly IDataQueryExecutor _executor;

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

    public async Task<S5006SceneData> LoadAsync(
        LegacyDomesticEquityMarket market,
        string stockCode,
        CancellationToken cancellationToken = default)
    {
        var query = market switch
        {
            LegacyDomesticEquityMarket.NxtKospi => KospiQuery,
            LegacyDomesticEquityMarket.NxtKosdaq => KosdaqQuery,
            _ => throw new LegacySceneDataException("The NXT scene loader requires an NXT market.")
        };
        stockCode = RequireStockCode(stockCode);
        var spec = new DataQuerySpec(
            query,
            [new DataQueryParameter("stockCode", stockCode, DbType.String)]);
        var table = await _executor.ExecuteAsync(
            DataSourceKind.MariaDb,
            "SCENE_5006_NXT",
            spec,
            cancellationToken).ConfigureAwait(false);
        var row = SceneDataTableReader.SingleRow(
            table,
            "STOCK_NAME", "CURR_PRICE", "CHG_TYPE", "NET_CHG", "RATE",
            "INIT_PRICE", "INIT_RATE", "HIGH_PRICE", "HIGH_RATE", "LOW_PRICE", "LOW_RATE");
        var directionText = SceneDataTableReader.RequiredString(row, "CHG_TYPE");
        var direction = LegacyPriceDirectionMutations.Parse(directionText);
        if (direction == ScenePriceDirection.Unknown)
        {
            throw new LegacySceneDataException("The NXT quote returned an unknown direction.");
        }

        return new S5006SceneData(
            market,
            SceneDataTableReader.RequiredString(row, "STOCK_NAME"),
            direction,
            Int64(row, "CURR_PRICE"),
            Int64(row, "NET_CHG"),
            Decimal(row, "RATE"),
            Int64(row, "INIT_PRICE"),
            Decimal(row, "INIT_RATE"),
            Int64(row, "HIGH_PRICE"),
            Decimal(row, "HIGH_RATE"),
            Int64(row, "LOW_PRICE"),
            Decimal(row, "LOW_RATE"));
    }

    private static string RequireStockCode(string? value)
    {
        if (string.IsNullOrWhiteSpace(value) || value.Length > 32 ||
            value.Any(character => !char.IsAsciiLetterOrDigit(character)))
        {
            throw new LegacySceneDataException("The NXT stock code is invalid.");
        }

        return value;
    }

    private static long Int64(DataRow row, string column)
    {
        try
        {
            return checked(Convert.ToInt64(RequiredValue(row, column), CultureInfo.InvariantCulture));
        }
        catch (Exception exception) when (exception is FormatException or InvalidCastException or OverflowException)
        {
            throw new LegacySceneDataException("The NXT quote returned an invalid integer.");
        }
    }

    private static decimal Decimal(DataRow row, string column)
    {
        try
        {
            return Convert.ToDecimal(RequiredValue(row, column), CultureInfo.InvariantCulture);
        }
        catch (Exception exception) when (exception is FormatException or InvalidCastException or OverflowException)
        {
            throw new LegacySceneDataException("The NXT quote returned an invalid decimal.");
        }
    }

    private static object RequiredValue(DataRow row, string column) => row[column] is { } value && value is not DBNull
        ? value
        : throw new LegacySceneDataException("The NXT quote returned a required null value.");
}
