#nullable enable

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

namespace MBN_STOCK_WEBVIEW.Core.Playout.Scenes;

public sealed record InvestorTradingValues(
    int Individual,
    int Foreign,
    int Institution);

public sealed record S5082SceneData(
    InvestorTradingValues Kospi,
    InvestorTradingValues Kosdaq,
    InvestorTradingValues Kospi200) : ILegacySceneData;

public sealed class S5082SceneMutationBuilder : ILegacySceneMutationBuilder<S5082SceneData>
{
    public string BuilderKey => "s5082";

    public IReadOnlyList<PlayoutMutation> Build(S5082SceneData data)
    {
        ArgumentNullException.ThrowIfNull(data);
        InvestorTradingValues[] rows =
        [
            LegacySceneBuilderGuard.NotNull(data.Kospi, nameof(data.Kospi)),
            LegacySceneBuilderGuard.NotNull(data.Kosdaq, nameof(data.Kosdaq)),
            LegacySceneBuilderGuard.NotNull(data.Kospi200, nameof(data.Kospi200))
        ];
        var mutations = new List<PlayoutMutation>(54);
        for (var rowIndex = 0; rowIndex < rows.Length; rowIndex++)
        {
            int[] values =
            [
                rows[rowIndex].Individual,
                rows[rowIndex].Foreign,
                rows[rowIndex].Institution
            ];
            for (var columnIndex = 0; columnIndex < values.Length; columnIndex++)
            {
                var text = values[columnIndex].ToString("#,##0", CultureInfo.InvariantCulture);
                var prefix = $"row{rowIndex + 1}_value{columnIndex + 1}_";
                for (var variant = 1; variant <= 3; variant++)
                {
                    mutations.Add(new PlayoutSetValue(prefix + variant, text));
                }

                var visibleVariant = values[columnIndex] > 0
                    ? 1
                    : values[columnIndex] < 0
                        ? 3
                        : 2;
                for (var variant = 1; variant <= 3; variant++)
                {
                    mutations.Add(new PlayoutSetVisible(
                        prefix + variant,
                        variant == visibleVariant));
                }
            }
        }

        return mutations;
    }
}

public sealed class S5082SceneDataLoader
{
    private const string Query = """
        SELECT name,
               SUM(DECODE(invest, '개인', org_amt, 0)) individual_value,
               SUM(DECODE(invest, '외국인', org_amt, 0)) foreign_value,
               SUM(DECODE(invest, '기관', org_amt, 0)) institution_value
        FROM (
            SELECT 1 s, '코스피' name,
                   DECODE(f_invest_code, '8000', '개인', '9000', '외국인', '9001', '외국인', '기관') invest,
                   ROUND((SUM(f_sell_turnover) / 100000000) - (SUM(f_buy_turnover) / 100000000)) org_amt
            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 DECODE(f_invest_code, '8000', '개인', '9000', '외국인', '9001', '외국인', '기관')
            UNION
            SELECT 2 s, '코스닥' name,
                   DECODE(f_invest_code, '8000', '개인', '9000', '외국인', '9001', '외국인', '기관') invest,
                   ROUND((SUM(f_sell_turnover) / 100000000) - (SUM(f_buy_turnover) / 100000000)) org_amt
            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 DECODE(f_invest_code, '8000', '개인', '9000', '외국인', '9001', '외국인', '기관')
            UNION
            SELECT 3 s, '코스피200' name,
                   DECODE(f_invest_code, '8000', '개인', '9000', '외국인', '9001', '외국인', '기관') invest,
                   ROUND((SUM(f_sell_turnover) / 100000000) - (SUM(f_buy_turnover) / 100000000)) org_amt
            FROM t_invest
            WHERE f_part_code = '029'
              AND f_invest_code IN ('1000','2000','3000','3100','4000','5000','6000','7000','8000','9000','9001')
            GROUP BY DECODE(f_invest_code, '8000', '개인', '9000', '외국인', '9001', '외국인', '기관')
        )
        GROUP BY name, s
        ORDER BY s
        """;

    private readonly IDataQueryExecutor _executor;

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

    public async Task<S5082SceneData> LoadAsync(
        CancellationToken cancellationToken = default)
    {
        var table = await _executor.ExecuteAsync(
            DataSourceKind.Oracle,
            "SCENE_5082",
            new DataQuerySpec(Query),
            cancellationToken).ConfigureAwait(false);
        var rows = MapRows(table);
        return new S5082SceneData(rows["코스피"], rows["코스닥"], rows["코스피200"]);
    }

    private static IReadOnlyDictionary<string, InvestorTradingValues> MapRows(DataTable? table)
    {
        string[] required = ["NAME", "INDIVIDUAL_VALUE", "FOREIGN_VALUE", "INSTITUTION_VALUE"];
        if (table is null || table.Rows.Count != 3 || required.Any(name => !table.Columns.Contains(name)))
        {
            throw new LegacySceneDataException("Scene 5082 returned an unexpected result shape.");
        }

        var mapped = new Dictionary<string, InvestorTradingValues>(StringComparer.Ordinal);
        foreach (DataRow row in table.Rows)
        {
            var name = Convert.ToString(row["NAME"], CultureInfo.InvariantCulture) ?? string.Empty;
            if (name is not ("코스피" or "코스닥" or "코스피200") ||
                !mapped.TryAdd(name, new InvestorTradingValues(
                    RequiredInt(row, "INDIVIDUAL_VALUE"),
                    RequiredInt(row, "FOREIGN_VALUE"),
                    RequiredInt(row, "INSTITUTION_VALUE"))))
            {
                throw new LegacySceneDataException("Scene 5082 returned an unknown or duplicate market row.");
            }
        }

        if (mapped.Count != 3)
        {
            throw new LegacySceneDataException("Scene 5082 did not return every required market row.");
        }

        return mapped;
    }

    private static int RequiredInt(DataRow row, string column)
    {
        if (row[column] is null or DBNull)
        {
            throw new LegacySceneDataException("Scene 5082 returned a required null value.");
        }

        try
        {
            return checked(Convert.ToInt32(row[column], CultureInfo.InvariantCulture));
        }
        catch (Exception exception) when (exception is FormatException or InvalidCastException or OverflowException)
        {
            throw new LegacySceneDataException("Scene 5082 returned an invalid numeric value.");
        }
    }
}
