#nullable enable

using System.Data;
using System.Globalization;

namespace MMoneyCoderSharp.Data;

public enum LegacyNamedComparisonRestoreFailure
{
    InvalidRow,
    MissingMarketIdentity,
    IdentityNotFound,
    IdentityAmbiguous,
    UnsupportedAction,
    DatabaseDataInvalid
}

/// <summary>
/// One original PLAY_LIST comparison row. The values are the original seven
/// caret-delimited fields after the persistence layer has parsed them.
/// </summary>
public sealed record LegacyNamedComparisonRestoreRow(
    int ItemIndex,
    bool IsEnabled,
    string GroupCode,
    string Subject,
    string GraphicType,
    string Subtype,
    string PageText,
    string DataCode);

public sealed record LegacyNamedComparisonRestoreOutcome(
    int ItemIndex,
    string? ActionId,
    LegacyComparisonImportTarget? First,
    LegacyComparisonImportTarget? Second,
    LegacyNamedComparisonRestoreFailure? Failure)
{
    public bool IsResolved =>
        Failure is null &&
        ActionId is not null &&
        First is not null &&
        Second is not null;
}

public sealed record LegacyNamedComparisonRestoreResult(
    DateTimeOffset RetrievedAt,
    IReadOnlyList<LegacyNamedComparisonRestoreOutcome> Rows);

/// <summary>
/// Reconstructs only identities that the original PLAY_LIST row actually
/// persisted. In particular, a legacy two-column stock row contains only the
/// generic group "종목" and therefore cannot distinguish KOSPI, KOSDAQ, NXT or
/// world-stock masters. Those rows fail closed even when a name happens to be
/// unique in today's databases.
/// </summary>
public sealed class LegacyNamedComparisonRestoreService
{
    public const int MaximumRows = LegacyNamedPlaylistPersistenceService.MaximumItems;

    private const string StockNameColumn = "STOCK_NAME";
    private const string StockCodeColumn = "STOCK_CODE";

    private static readonly IReadOnlyDictionary<string, string> MarketTargets =
        new Dictionary<string, string>(StringComparer.Ordinal)
        {
            ["코스피 지수"] = "Kospi",
            ["코스닥 지수"] = "Kosdaq",
            ["코스피200 지수"] = "Kospi200",
            ["선물 지수"] = "Futures",
            ["KRX100지수"] = "Krx100",
            ["환율-원달러"] = "WonDollar",
            ["환율-원엔"] = "WonYen",
            ["환율-원위엔"] = "WonYuan",
            ["환율-원유로"] = "WonEuro",
            ["해외지수-다우"] = "Dow",
            ["해외지수-나스닥"] = "Nasdaq",
            ["해외지수-S&P"] = "Sp500",
            ["해외지수-독일"] = "GermanyDax",
            ["해외지수-영국"] = "UnitedKingdomFtse",
            ["해외지수-프랑스"] = "FranceCac",
            ["해외지수-일본"] = "Nikkei",
            ["해외지수-홍콩"] = "HangSeng",
            ["해외지수-대만"] = "TaiwanWeighted",
            ["해외지수-싱가포르"] = "SingaporeStraitsTimes",
            ["해외지수-태국"] = "ThailandSet",
            ["해외지수-필리핀"] = "PhilippinesComposite",
            ["해외지수-말레이시아"] = "MalaysiaKlse",
            ["해외지수-인도네시아"] = "IndonesiaComposite",
            ["해외지수-중국"] = "ShanghaiComposite"
        };

    private static readonly IReadOnlyDictionary<string, StockIdentityProfile> StockProfiles =
        new Dictionary<string, StockIdentityProfile>(StringComparer.Ordinal)
        {
            ["코스피"] = new(
                DataSourceKind.Oracle,
                "LEGACY_NAMED_COMPARISON_KOSPI",
                "kospi",
                LegacyComparisonImportTargetKind.KrxStock,
                """
                SELECT STOCK_NAME, STOCK_CODE
                FROM (
                    SELECT DISTINCT F_STOCK_WANNAME AS STOCK_NAME, F_STOCK_CODE AS STOCK_CODE
                    FROM T_STOCK
                    WHERE F_MKT_HALT = 'N'
                      AND F_STOCK_WANNAME = :stock_name
                    ORDER BY F_STOCK_CODE
                )
                WHERE ROWNUM <= 2
                """),
            ["코스닥"] = new(
                DataSourceKind.Oracle,
                "LEGACY_NAMED_COMPARISON_KOSDAQ",
                "kosdaq",
                LegacyComparisonImportTargetKind.KrxStock,
                """
                SELECT STOCK_NAME, STOCK_CODE
                FROM (
                    SELECT DISTINCT F_STOCK_WANNAME AS STOCK_NAME, F_STOCK_CODE AS STOCK_CODE
                    FROM T_KOSDAQ_STOCK
                    WHERE F_MKT_HALT = 'N'
                      AND F_STOCK_WANNAME = :stock_name
                    ORDER BY F_STOCK_CODE
                )
                WHERE ROWNUM <= 2
                """),
            ["코스피_NXT"] = new(
                DataSourceKind.MariaDb,
                "LEGACY_NAMED_COMPARISON_NXT_KOSPI",
                "kospi",
                LegacyComparisonImportTargetKind.NxtStock,
                """
                SELECT DISTINCT a.F_STOCK_NAME AS STOCK_NAME, a.F_STOCK_CODE AS STOCK_CODE
                FROM N_STOCK a
                INNER JOIN N_ONLINE b ON b.F_STOCK_CODE = a.F_STOCK_CODE
                WHERE a.F_STOP_GUBUN = 'N'
                  AND a.F_STOCK_NAME = @stock_name
                ORDER BY a.F_STOCK_CODE
                LIMIT 2
                """),
            ["코스닥_NXT"] = new(
                DataSourceKind.MariaDb,
                "LEGACY_NAMED_COMPARISON_NXT_KOSDAQ",
                "kosdaq",
                LegacyComparisonImportTargetKind.NxtStock,
                """
                SELECT DISTINCT a.F_STOCK_NAME AS STOCK_NAME, a.F_STOCK_CODE AS STOCK_CODE
                FROM N_KOSDAQ_STOCK a
                INNER JOIN N_KOSDAQ_ONLINE b ON b.F_STOCK_CODE = a.F_STOCK_CODE
                WHERE a.F_STOP_GUBUN = 'N'
                  AND a.F_STOCK_NAME = @stock_name
                ORDER BY a.F_STOCK_CODE
                LIMIT 2
                """)
        };

    private static readonly IReadOnlyDictionary<string, string> ReturnActionIds =
        new Dictionary<string, string>(StringComparer.Ordinal)
        {
            ["5일"] = "return-5d",
            ["1개월"] = "return-1m",
            ["3개월"] = "return-3m",
            ["6개월"] = "return-6m",
            ["12개월"] = "return-12m"
        };

    private static readonly HashSet<string> ExpectedMarketTargets =
        new(StringComparer.Ordinal)
        {
            "Kospi", "Kosdaq", "Kospi200", "Krx100"
        };

    private readonly IDataQueryExecutor _executor;

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

    /// <summary>
    /// A narrow, non-authoritative classifier used to decide which untrusted
    /// named rows should be submitted for the native exact-identity pass.
    /// ResolveAsync still validates every field independently.
    /// </summary>
    public static bool IsCandidate(LegacyNamedComparisonRestoreRow? row)
    {
        if (row is null || row.ItemIndex is < 0 or >= MaximumRows ||
            !IsCanonicalField(row.GroupCode, 256, allowEmpty: true) ||
            !IsCanonicalField(row.Subject, 4_000, allowEmpty: false) ||
            !IsCanonicalField(row.GraphicType, 256, allowEmpty: false) ||
            !IsCanonicalField(row.Subtype, 256, allowEmpty: true) ||
            !IsCanonicalField(row.DataCode, 1_024, allowEmpty: true) ||
            !string.Equals(row.PageText, "1/1", StringComparison.Ordinal) ||
            row.DataCode.Length != 0)
        {
            return false;
        }

        var names = row.Subject.Split(',');
        if (names.Length != 2 || names.Any(static value =>
                !IsCanonicalField(
                    value,
                    LegacyComparisonFileParser.MaximumTargetNameLength,
                    allowEmpty: false)))
        {
            return false;
        }

        return row.GraphicType switch
        {
            "2열판" => row.Subtype is "" or "예상체결",
            "종목별 비교분석_캔들 그래프" or
                "종목별 비교분석_라인 그래프" => row.Subtype.Length == 0,
            "종목별 수익률 비교" => ReturnActionIds.ContainsKey(row.Subtype),
            _ => false
        };
    }

    public async Task<LegacyNamedComparisonRestoreResult> ResolveAsync(
        IReadOnlyList<LegacyNamedComparisonRestoreRow> rows,
        CancellationToken cancellationToken = default)
    {
        ArgumentNullException.ThrowIfNull(rows);
        if (rows.Count is < 1 or > MaximumRows)
        {
            throw new ArgumentOutOfRangeException(
                nameof(rows),
                $"A named comparison restore must contain 1 through {MaximumRows} rows.");
        }

        var itemIndexes = new HashSet<int>();
        foreach (var row in rows)
        {
            if (row is null || row.ItemIndex is < 0 or >= MaximumRows ||
                !itemIndexes.Add(row.ItemIndex))
            {
                throw new ArgumentException(
                    "Named comparison restore rows must have unique bounded item indexes.",
                    nameof(rows));
            }
        }

        cancellationToken.ThrowIfCancellationRequested();
        var cache = new Dictionary<string, IdentityResolution>(StringComparer.Ordinal);
        var outcomes = new List<LegacyNamedComparisonRestoreOutcome>(rows.Count);
        foreach (var row in rows)
        {
            cancellationToken.ThrowIfCancellationRequested();
            outcomes.Add(await ResolveRowAsync(row, cache, cancellationToken)
                .ConfigureAwait(false));
        }

        return new LegacyNamedComparisonRestoreResult(
            DateTimeOffset.Now,
            outcomes.AsReadOnly());
    }

    private async Task<LegacyNamedComparisonRestoreOutcome> ResolveRowAsync(
        LegacyNamedComparisonRestoreRow row,
        IDictionary<string, IdentityResolution> cache,
        CancellationToken cancellationToken)
    {
        if (!TryClassify(row, out var actionId, out var targetNames, out var marketLabels,
                out var failure))
        {
            return Failed(row.ItemIndex, failure);
        }

        if (marketLabels is null)
        {
            var targets = new LegacyComparisonImportTarget[2];
            for (var index = 0; index < targets.Length; index++)
            {
                if (!MarketTargets.TryGetValue(targetNames[index], out var target))
                {
                    return Failed(
                        row.ItemIndex,
                        LegacyNamedComparisonRestoreFailure.MissingMarketIdentity);
                }

                targets[index] = new LegacyComparisonImportTarget(
                    LegacyComparisonImportTargetKind.MarketTarget,
                    MarketTarget: target);
            }

            if (!IsFixedPairActionSupported(actionId, targets))
            {
                return Failed(
                    row.ItemIndex,
                    LegacyNamedComparisonRestoreFailure.UnsupportedAction);
            }

            return Resolved(row.ItemIndex, actionId, targets[0], targets[1]);
        }

        // UC1 rejects every NXT comparison graph/return before MainForm builds
        // a scene, so an NXT master lookup cannot make this historical action
        // playable and would only add unnecessary MariaDB work.
        if (marketLabels.Any(static value =>
                value.EndsWith("_NXT", StringComparison.Ordinal)))
        {
            return Failed(
                row.ItemIndex,
                LegacyNamedComparisonRestoreFailure.UnsupportedAction);
        }

        var resolved = new LegacyComparisonImportTarget[2];
        for (var index = 0; index < resolved.Length; index++)
        {
            var resolution = await ResolveStockAsync(
                    targetNames[index],
                    marketLabels[index],
                    cache,
                    cancellationToken)
                .ConfigureAwait(false);
            if (resolution.Target is null)
            {
                return Failed(row.ItemIndex, resolution.Failure!.Value);
            }

            resolved[index] = resolution.Target;
        }

        return Resolved(row.ItemIndex, actionId, resolved[0], resolved[1]);
    }

    private static bool TryClassify(
        LegacyNamedComparisonRestoreRow row,
        out string actionId,
        out string[] targetNames,
        out string[]? marketLabels,
        out LegacyNamedComparisonRestoreFailure failure)
    {
        actionId = string.Empty;
        targetNames = [];
        marketLabels = null;
        failure = LegacyNamedComparisonRestoreFailure.InvalidRow;

        if (!IsCanonicalField(row.GroupCode, 256, allowEmpty: true) ||
            !IsCanonicalField(row.Subject, 4_000, allowEmpty: false) ||
            !IsCanonicalField(row.GraphicType, 256, allowEmpty: false) ||
            !IsCanonicalField(row.Subtype, 256, allowEmpty: true) ||
            !IsCanonicalField(row.DataCode, 1_024, allowEmpty: true) ||
            !string.Equals(row.PageText, "1/1", StringComparison.Ordinal) ||
            row.DataCode.Length != 0)
        {
            return false;
        }

        var names = row.Subject.Split(',');
        if (names.Length != 2 ||
            names.Any(static value =>
                !IsCanonicalField(value, LegacyComparisonFileParser.MaximumTargetNameLength, allowEmpty: false)))
        {
            return false;
        }

        targetNames = names;
        if (string.Equals(row.GraphicType, "2열판", StringComparison.Ordinal))
        {
            actionId = row.Subtype switch
            {
                "" => "two-column-current",
                "예상체결" => "two-column-expected",
                _ => string.Empty
            };
            if (actionId.Length == 0 ||
                row.GroupCode is not ("지수" or "종목"))
            {
                return false;
            }

            if (row.GroupCode == "종목")
            {
                failure = LegacyNamedComparisonRestoreFailure.MissingMarketIdentity;
                return false;
            }

            return true;
        }

        actionId = row.GraphicType switch
        {
            "종목별 비교분석_캔들 그래프" when row.Subtype.Length == 0 =>
                "comparison-candle",
            "종목별 비교분석_라인 그래프" when row.Subtype.Length == 0 =>
                "comparison-line",
            "종목별 수익률 비교" when ReturnActionIds.TryGetValue(row.Subtype, out var value) =>
                value,
            _ => string.Empty
        };
        if (actionId.Length == 0)
        {
            return false;
        }

        var markets = row.GroupCode.Split(',');
        if (markets.Length != 2 || markets.Any(static value => !StockProfiles.ContainsKey(value)))
        {
            failure = LegacyNamedComparisonRestoreFailure.MissingMarketIdentity;
            return false;
        }

        marketLabels = markets;
        return true;
    }

    private async Task<IdentityResolution> ResolveStockAsync(
        string sourceName,
        string marketLabel,
        IDictionary<string, IdentityResolution> cache,
        CancellationToken cancellationToken)
    {
        var cacheKey = marketLabel + "\u001f" + sourceName;
        if (cache.TryGetValue(cacheKey, out var existing))
        {
            return existing;
        }

        var profile = StockProfiles[marketLabel];
        var nxtSuffix = "(NXT)";
        var isNxtName = sourceName.EndsWith(nxtSuffix, StringComparison.Ordinal);
        var isNxtProfile = profile.Kind == LegacyComparisonImportTargetKind.NxtStock;
        if (isNxtName != isNxtProfile ||
            isNxtProfile && sourceName.Length == nxtSuffix.Length)
        {
            var invalid = new IdentityResolution(
                null,
                LegacyNamedComparisonRestoreFailure.InvalidRow);
            cache.Add(cacheKey, invalid);
            return invalid;
        }

        var lookupName = isNxtProfile
            ? sourceName[..^nxtSuffix.Length]
            : sourceName;
        var spec = new DataQuerySpec(
            profile.Sql,
            [new DataQueryParameter("stock_name", lookupName, DbType.String)]);
        spec.ValidateFor(profile.Source);
        var table = await _executor.ExecuteAsync(
                profile.Source,
                profile.QueryName,
                spec,
                cancellationToken)
            .ConfigureAwait(false);
        cancellationToken.ThrowIfCancellationRequested();

        var resolution = ValidateStockTable(table, lookupName, profile);
        cache.Add(cacheKey, resolution);
        return resolution;
    }

    private static IdentityResolution ValidateStockTable(
        DataTable? table,
        string expectedName,
        StockIdentityProfile profile)
    {
        if (table is null || table.Columns.Count != 2 || table.Rows.Count > 2 ||
            !HasExactStringColumn(table.Columns[0], StockNameColumn) ||
            !HasExactStringColumn(table.Columns[1], StockCodeColumn))
        {
            return InvalidDatabaseData();
        }

        var codes = new HashSet<string>(StringComparer.Ordinal);
        foreach (DataRow row in table.Rows)
        {
            if (row[0] is not string name || row[1] is not string code ||
                !string.Equals(name, expectedName, StringComparison.Ordinal) ||
                !IsCanonicalField(name, LegacyComparisonFileParser.MaximumTargetNameLength, allowEmpty: false) ||
                !IsCanonicalAsciiIdentity(code, 32) ||
                !codes.Add(code))
            {
                return InvalidDatabaseData();
            }
        }

        if (table.Rows.Count == 0)
        {
            return new IdentityResolution(
                null,
                LegacyNamedComparisonRestoreFailure.IdentityNotFound);
        }

        if (table.Rows.Count != 1)
        {
            return new IdentityResolution(
                null,
                LegacyNamedComparisonRestoreFailure.IdentityAmbiguous);
        }

        return new IdentityResolution(
            new LegacyComparisonImportTarget(
                profile.Kind,
                Market: profile.Market,
                StockName: (string)table.Rows[0][0],
                StockCode: (string)table.Rows[0][1]),
            null);
    }

    private static bool HasExactStringColumn(DataColumn column, string name) =>
        string.Equals(column.ColumnName, name, StringComparison.Ordinal) &&
        column.DataType == typeof(string);

    private static bool IsFixedPairActionSupported(
        string actionId,
        IReadOnlyList<LegacyComparisonImportTarget> targets)
    {
        var values = targets.Select(static target => target.MarketTarget!).ToArray();
        var futuresCount = values.Count(static value => value == "Futures");
        if (futuresCount > 1)
        {
            return false;
        }

        if (actionId == "two-column-current" || futuresCount == 1)
        {
            return true;
        }

        return actionId == "two-column-expected" &&
            values.All(ExpectedMarketTargets.Contains);
    }

    private static bool IsCanonicalAsciiIdentity(string value, int maximumLength) =>
        value.Length is > 0 && value.Length <= maximumLength &&
        value.All(static character =>
            char.IsAsciiLetterOrDigit(character) || character is '.' or '_' or '-');

    private static bool IsCanonicalField(
        string? value,
        int maximumLength,
        bool allowEmpty)
    {
        if (value is null || value.Length > maximumLength ||
            (!allowEmpty && value.Length == 0) ||
            !string.Equals(value, value.Trim(), StringComparison.Ordinal) ||
            value.Contains('^'))
        {
            return false;
        }

        foreach (var character in value)
        {
            var category = CharUnicodeInfo.GetUnicodeCategory(character);
            if (char.IsControl(character) || char.IsSurrogate(character) ||
                character == '\uFFFD' ||
                category is UnicodeCategory.Format or
                    UnicodeCategory.LineSeparator or
                    UnicodeCategory.ParagraphSeparator)
            {
                return false;
            }
        }

        return true;
    }

    private static LegacyNamedComparisonRestoreOutcome Resolved(
        int itemIndex,
        string actionId,
        LegacyComparisonImportTarget first,
        LegacyComparisonImportTarget second) =>
        new(itemIndex, actionId, first, second, null);

    private static LegacyNamedComparisonRestoreOutcome Failed(
        int itemIndex,
        LegacyNamedComparisonRestoreFailure failure) =>
        new(itemIndex, null, null, null, failure);

    private static IdentityResolution InvalidDatabaseData() =>
        new(null, LegacyNamedComparisonRestoreFailure.DatabaseDataInvalid);

    private sealed record IdentityResolution(
        LegacyComparisonImportTarget? Target,
        LegacyNamedComparisonRestoreFailure? Failure);

    private sealed record StockIdentityProfile(
        DataSourceKind Source,
        string QueryName,
        string Market,
        LegacyComparisonImportTargetKind Kind,
        string Sql);
}
