#nullable enable

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

namespace MBN_STOCK_WEBVIEW.Core.Playout.Scenes;

public sealed record S5006DomesticSceneDataRequest(
    LegacyDomesticEquityMarket Market,
    string StockCode);

public sealed class S5006DomesticSceneDataLoader
{
    private static readonly string[] Columns =
    [
        "STOCK_NAME", "DIRECTION_CODE", "CURRENT_PRICE", "NET_CHANGE", "RATE",
        "OPEN_PRICE", "OPEN_RATE", "HIGH_PRICE", "HIGH_RATE", "LOW_PRICE", "LOW_RATE"
    ];

    private readonly IDataQueryExecutor _executor;

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

    public async Task<S5006SceneData> LoadAsync(
        S5006DomesticSceneDataRequest request,
        CancellationToken cancellationToken = default)
    {
        ArgumentNullException.ThrowIfNull(request);
        var view = request.Market switch
        {
            LegacyDomesticEquityMarket.Kospi => "V_2",
            LegacyDomesticEquityMarket.Kosdaq => "V_2_KOSDAQ",
            _ => throw EquityPanelLoaderReader.InvalidRequest()
        };
        var stockCode = EquityPanelLoaderReader.StockCode(request.StockCode);
        var sql = $"""
            SELECT
                a.stock_name STOCK_NAME,
                a.chg_type DIRECTION_CODE,
                a.curr_price CURRENT_PRICE,
                a.net_chg NET_CHANGE,
                a.rate RATE,
                a.init_price OPEN_PRICE,
                DECODE(
                    a.final_price,
                    0, ROUND(((a.init_price - a.base_price) / a.base_price) * 100, 2),
                    ROUND(((a.init_price - a.final_price) / a.final_price) * 100, 2)) OPEN_RATE,
                a.high_price HIGH_PRICE,
                DECODE(
                    a.final_price,
                    0, ROUND(((a.high_price - a.base_price) / a.base_price) * 100, 2),
                    ROUND(((a.high_price - a.final_price) / a.final_price) * 100, 2)) HIGH_RATE,
                a.low_price LOW_PRICE,
                DECODE(
                    a.final_price,
                    0, ROUND(((a.low_price - a.base_price) / a.base_price) * 100, 2),
                    ROUND(((a.low_price - a.final_price) / a.final_price) * 100, 2)) LOW_RATE
            FROM {view} a
            WHERE a.stock_code = :stockCode
            """;
        var spec = EquityPanelLoaderReader.Spec(
            DataSourceKind.Oracle,
            sql,
            new DataQueryParameter("stockCode", stockCode, DbType.String));
        var table = await _executor.ExecuteAsync(
            DataSourceKind.Oracle,
            "S5006_DOMESTIC",
            spec,
            cancellationToken).ConfigureAwait(false);
        var row = EquityPanelLoaderReader.SingleRow(table, Columns);
        var data = new S5006SceneData(
            request.Market,
            EquityPanelLoaderReader.Text(row, "STOCK_NAME"),
            EquityPanelLoaderReader.Direction(row, "DIRECTION_CODE"),
            EquityPanelLoaderReader.Int64(row, "CURRENT_PRICE"),
            EquityPanelLoaderReader.Int64(row, "NET_CHANGE"),
            EquityPanelLoaderReader.Decimal(row, "RATE"),
            EquityPanelLoaderReader.Int64(row, "OPEN_PRICE"),
            EquityPanelLoaderReader.Decimal(row, "OPEN_RATE"),
            EquityPanelLoaderReader.Int64(row, "HIGH_PRICE"),
            EquityPanelLoaderReader.Decimal(row, "HIGH_RATE"),
            EquityPanelLoaderReader.Int64(row, "LOW_PRICE"),
            EquityPanelLoaderReader.Decimal(row, "LOW_RATE"));
        _ = new S5006SceneMutationBuilder().Build(data);
        return data;
    }
}

public sealed record S5011SceneDataRequest(
    LegacyDomesticEquityMarket Market,
    S5011DetailBranch Branch,
    string StockCode);

public sealed class S5011SceneDataLoader
{
    private static readonly string[] Columns =
    [
        "STOCK_NAME", "DIRECTION_CODE", "CURRENT_PRICE", "NET_CHANGE", "RATE",
        "DETAIL_1", "DETAIL_2", "DETAIL_3", "DETAIL_4"
    ];

    private readonly IDataQueryExecutor _executor;

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

    public async Task<S5011SceneData> LoadAsync(
        S5011SceneDataRequest request,
        CancellationToken cancellationToken = default)
    {
        ArgumentNullException.ThrowIfNull(request);
        if (!Enum.IsDefined(request.Market) || !Enum.IsDefined(request.Branch))
        {
            throw EquityPanelLoaderReader.InvalidRequest();
        }

        var stockCode = EquityPanelLoaderReader.StockCode(request.StockCode);
        var plan = S5011QueryFactory.Create(request.Market, request.Branch, stockCode);
        var table = await _executor.ExecuteAsync(
            plan.Source,
            "S5011_DETAIL",
            plan.Spec,
            cancellationToken).ConfigureAwait(false);
        var row = EquityPanelLoaderReader.SingleRow(table, Columns);
        S5011DetailData detail = request.Branch switch
        {
            S5011DetailBranch.FaceValue => new S5011FaceValueDetail(
                EquityPanelLoaderReader.Int64(row, "DETAIL_1"),
                EquityPanelLoaderReader.Int64(row, "DETAIL_2"),
                EquityPanelLoaderReader.Int64(row, "DETAIL_3"),
                EquityPanelLoaderReader.Int64(row, "DETAIL_4")),
            S5011DetailBranch.Valuation => new S5011ValuationDetail(
                EquityPanelLoaderReader.Decimal(row, "DETAIL_1"),
                EquityPanelLoaderReader.Decimal(row, "DETAIL_2"),
                EquityPanelLoaderReader.Int64(row, "DETAIL_3"),
                EquityPanelLoaderReader.Int64(row, "DETAIL_4")),
            S5011DetailBranch.Volume => new S5011VolumeDetail(
                EquityPanelLoaderReader.Int64(row, "DETAIL_1"),
                EquityPanelLoaderReader.Int64(row, "DETAIL_2"),
                EquityPanelLoaderReader.Int64(row, "DETAIL_3"),
                EquityPanelLoaderReader.Int64(row, "DETAIL_4")),
            _ => throw EquityPanelLoaderReader.InvalidRequest()
        };
        var data = new S5011SceneData(
            request.Market,
            EquityPanelLoaderReader.Text(row, "STOCK_NAME"),
            EquityPanelLoaderReader.Direction(row, "DIRECTION_CODE"),
            EquityPanelLoaderReader.Int64(row, "CURRENT_PRICE"),
            EquityPanelLoaderReader.Int64(row, "NET_CHANGE"),
            EquityPanelLoaderReader.Decimal(row, "RATE"),
            detail);
        _ = new S5011SceneMutationBuilder().Build(data);
        return data;
    }
}

internal static class S5011QueryFactory
{
    public static QueryPlan Create(
        LegacyDomesticEquityMarket market,
        S5011DetailBranch branch,
        string stockCode)
    {
        if (market is LegacyDomesticEquityMarket.NxtKospi or
            LegacyDomesticEquityMarket.NxtKosdaq)
        {
            if (branch == S5011DetailBranch.Valuation)
            {
                // The legacy NXT query fabricated four zero valuation values.
                throw EquityPanelLoaderReader.InvalidRequest();
            }

            return Nxt(market, branch, stockCode);
        }

        return Oracle(market, branch, stockCode);
    }

    private static QueryPlan Oracle(
        LegacyDomesticEquityMarket market,
        S5011DetailBranch branch,
        string stockCode)
    {
        var source = market switch
        {
            LegacyDomesticEquityMarket.Kospi => new OracleSource(
                "V_2", "t_online1", "t_stock", "T_CANDLE_HISTORY"),
            LegacyDomesticEquityMarket.Kosdaq => new OracleSource(
                "V_2_KOSDAQ", "t_kosdaq_online1", "t_kosdaq_stock",
                "T_KOSDAQ_CANDLE_HISTORY"),
            _ => throw EquityPanelLoaderReader.InvalidRequest()
        };
        var sql = branch switch
        {
            S5011DetailBranch.FaceValue => $"""
                SELECT
                    a.stock_name STOCK_NAME,
                    a.chg_type DIRECTION_CODE,
                    a.curr_price CURRENT_PRICE,
                    a.net_chg NET_CHANGE,
                    a.rate RATE,
                    a.list_price DETAIL_1,
                    a.capital_price DETAIL_2,
                    a.siga_price DETAIL_3,
                    b.rank_num DETAIL_4
                FROM {source.View} a,
                    (SELECT
                        z.f_stock_code stock_code,
                        RANK() OVER (ORDER BY z.f_list_num * y.f_curr_price DESC) rank_num
                     FROM {source.OnlineTable} y, {source.StockTable} z
                     WHERE y.f_stock_code = z.f_stock_code
                       AND z.f_mkt_halt = 'N') b
                WHERE a.stock_code = b.stock_code
                  AND a.stock_code = :stockCode
                """,
            S5011DetailBranch.Valuation => $"""
                SELECT
                    a.stock_name STOCK_NAME,
                    a.chg_type DIRECTION_CODE,
                    a.curr_price CURRENT_PRICE,
                    a.net_chg NET_CHANGE,
                    a.rate RATE,
                    a.pbr DETAIL_1,
                    a.per DETAIL_2,
                    a.bps DETAIL_3,
                    a.eps DETAIL_4
                FROM {source.View} a
                WHERE a.stock_code = :stockCode
                """,
            S5011DetailBranch.Volume => $"""
                SELECT
                    a.stock_name STOCK_NAME,
                    a.chg_type DIRECTION_CODE,
                    a.curr_price CURRENT_PRICE,
                    a.net_chg NET_CHANGE,
                    a.rate RATE,
                    a.net_vol DETAIL_1,
                    a.net_turnover DETAIL_2,
                    NVL((
                        SELECT ROUND((SUM(recent.F_CURR_PRICE) + a.curr_price) / 5)
                        FROM (
                            SELECT F_CURR_PRICE
                            FROM (
                                SELECT F_CURR_PRICE
                                FROM {source.CandleTable}
                                WHERE F_DATA_DATE < (SELECT MAX(open_day) FROM v_open_day)
                                  AND f_stock_code = :stockCode5
                                ORDER BY F_DATA_DATE DESC
                            )
                            WHERE ROWNUM < 5
                        ) recent
                    ), 0) DETAIL_3,
                    NVL((
                        SELECT ROUND((SUM(recent.F_CURR_PRICE) + a.curr_price) / 20)
                        FROM (
                            SELECT F_CURR_PRICE
                            FROM (
                                SELECT F_CURR_PRICE
                                FROM {source.CandleTable}
                                WHERE F_DATA_DATE < (SELECT MAX(open_day) FROM v_open_day)
                                  AND f_stock_code = :stockCode20
                                ORDER BY F_DATA_DATE DESC
                            )
                            WHERE ROWNUM < 20
                        ) recent
                    ), 0) DETAIL_4
                FROM {source.View} a
                WHERE a.stock_code = :stockCode
                """,
            _ => throw EquityPanelLoaderReader.InvalidRequest()
        };
        var parameters = branch == S5011DetailBranch.Volume
            ? new[]
            {
                new DataQueryParameter("stockCode5", stockCode, DbType.String),
                new DataQueryParameter("stockCode20", stockCode, DbType.String),
                new DataQueryParameter("stockCode", stockCode, DbType.String)
            }
            : [new DataQueryParameter("stockCode", stockCode, DbType.String)];
        return new QueryPlan(
            DataSourceKind.Oracle,
            EquityPanelLoaderReader.Spec(DataSourceKind.Oracle, sql, parameters));
    }

    private static QueryPlan Nxt(
        LegacyDomesticEquityMarket market,
        S5011DetailBranch branch,
        string stockCode)
    {
        var source = market switch
        {
            LegacyDomesticEquityMarket.NxtKospi => new NxtSource(
                "n_online", "n_stock"),
            LegacyDomesticEquityMarket.NxtKosdaq => new NxtSource(
                "n_kosdaq_online", "n_kosdaq_stock"),
            _ => throw EquityPanelLoaderReader.InvalidRequest()
        };
        var sql = branch switch
        {
            S5011DetailBranch.FaceValue => $"""
                SELECT
                    b.f_stock_name STOCK_NAME,
                    a.f_chg_type DIRECTION_CODE,
                    a.f_curr_price CURRENT_PRICE,
                    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,
                    b.f_list_price DETAIL_1,
                    b.f_capital_price DETAIL_2,
                    b.f_list_num * a.f_curr_price DETAIL_3,
                    b.rank_num DETAIL_4
                FROM {source.OnlineTable} a
                JOIN (
                    SELECT
                        z.f_stock_code,
                        z.f_stock_name,
                        z.f_list_price,
                        z.f_capital_price,
                        z.f_list_num,
                        RANK() OVER (ORDER BY z.f_list_num * y.f_curr_price DESC) rank_num
                    FROM {source.OnlineTable} y
                    JOIN {source.StockTable} z ON y.f_stock_code = z.f_stock_code
                    WHERE z.f_stop_gubun = 'N'
                ) b ON a.f_stock_code = b.f_stock_code
                WHERE a.f_stock_code = @stockCode
                """,
            S5011DetailBranch.Volume => $"""
                SELECT
                    z.f_stock_name STOCK_NAME,
                    a.f_chg_type DIRECTION_CODE,
                    a.f_curr_price CURRENT_PRICE,
                    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,
                    a.f_net_vol DETAIL_1,
                    a.f_net_turnover DETAIL_2,
                    0 DETAIL_3,
                    0 DETAIL_4
                FROM {source.OnlineTable} a
                JOIN {source.StockTable} z ON a.f_stock_code = z.f_stock_code
                WHERE a.f_stock_code = @stockCode
                """,
            _ => throw EquityPanelLoaderReader.InvalidRequest()
        };
        return new QueryPlan(
            DataSourceKind.MariaDb,
            EquityPanelLoaderReader.Spec(
                DataSourceKind.MariaDb,
                sql,
                new DataQueryParameter("stockCode", stockCode, DbType.String)));
    }

    internal sealed record QueryPlan(DataSourceKind Source, DataQuerySpec Spec);

    private sealed record OracleSource(
        string View,
        string OnlineTable,
        string StockTable,
        string CandleTable);

    private sealed record NxtSource(string OnlineTable, string StockTable);
}

internal static class EquityPanelLoaderReader
{
    private const int MaximumTextLength = 256;

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

    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 DataRow SingleRow(DataTable? table, IReadOnlyList<string> columns)
    {
        ExactSchema(table, columns);
        if (table!.Rows.Count != 1)
        {
            throw InvalidResult();
        }

        return table.Rows[0];
    }

    public static void ExactSchema(DataTable? table, IReadOnlyList<string> columns)
    {
        if (table is null || 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();
            }
        }
    }

    public static string Text(DataRow row, string column, bool allowNull = false)
    {
        var value = row[column];
        if (value is null or DBNull)
        {
            if (allowNull)
            {
                return string.Empty;
            }

            throw InvalidResult();
        }

        var text = Convert.ToString(value, CultureInfo.InvariantCulture)?.Trim();
        if (text is null || text.Length > MaximumTextLength || text.Any(char.IsControl) ||
            (!allowNull && text.Length == 0))
        {
            throw InvalidResult();
        }

        return text;
    }

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

    public static long Int64(DataRow row, string column)
    {
        try
        {
            return checked(Convert.ToInt64(Value(row, column), CultureInfo.InvariantCulture));
        }
        catch (Exception exception) when (
            exception is FormatException or InvalidCastException or OverflowException)
        {
            throw InvalidResult();
        }
    }

    public static decimal Decimal(DataRow row, string column)
    {
        try
        {
            return Convert.ToDecimal(Value(row, column), CultureInfo.InvariantCulture);
        }
        catch (Exception exception) when (
            exception is FormatException or InvalidCastException or OverflowException)
        {
            throw InvalidResult();
        }
    }

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

    public static ScenePriceDirection Direction(DataRow row, string column) =>
        Direction(Text(row, column, allowNull: true));

    public static ScenePriceDirection Direction(string? value) => value?.Trim() switch
    {
        "\uC0C1\uD55C" or "1" => ScenePriceDirection.LimitUp,
        "\uC0C1\uC2B9" or "+" or "2" => ScenePriceDirection.Up,
        null or "" or "\uBCF4\uD569" or "3" => ScenePriceDirection.Flat,
        "\uD558\uB77D" or "-" or "5" => ScenePriceDirection.Down,
        "\uD558\uD55C" or "4" => ScenePriceDirection.LimitDown,
        _ => throw InvalidResult()
    };

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

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