#nullable enable

using System.Data;
using System.Globalization;
using System.Text.RegularExpressions;

namespace MMoneyCoderSharp.Data;

public enum LegacyNamedNxtThemeRestoreFailure
{
    InvalidRow,
    UnsupportedAction,
    IdentityNotFound,
    IdentityAmbiguous,
    PreviewEmpty,
    LiveItemsEmpty,
    DatabaseDataInvalid
}

/// <summary>
/// One untrusted seven-field PLAY_LIST row routed to the NXT-theme verifier.
/// Every original field is retained so a successful restore can preserve the
/// old row while using the current MariaDB identity for playout.
/// </summary>
public sealed record LegacyNamedNxtThemeRestoreRow(
    int ItemIndex,
    bool IsEnabled,
    string GroupCode,
    string Subject,
    string GraphicType,
    string Subtype,
    string PageText,
    string DataCode);

/// <summary>
/// A closed current identity. StoredThemeCode is evidence only; PREPARE must
/// use CurrentThemeCode. The original application performed this same mapping
/// by exact title immediately before it constructed s5074/s5077/s5088.
/// </summary>
public sealed record LegacyNamedNxtThemeRestoreIdentity(
    string ActionId,
    string BuilderKey,
    string CutCode,
    int PageSize,
    string Sort,
    ThemeSession Session,
    string ThemeTitle,
    string StoredThemeCode,
    string CurrentThemeCode,
    int PreviewItemCount,
    int LiveItemCount,
    bool PreviewIsTruncated)
{
    public bool CodeWasRemapped =>
        !string.Equals(StoredThemeCode, CurrentThemeCode, StringComparison.Ordinal);
}

public sealed record LegacyNamedNxtThemeRestoreOutcome(
    int ItemIndex,
    LegacyNamedNxtThemeRestoreIdentity? Identity,
    LegacyNamedNxtThemeRestoreFailure? Failure,
    string? ObservedCurrentThemeCode = null)
{
    public bool IsResolved => Identity is not null && Failure is null;
}

public sealed record LegacyNamedNxtThemeRestoreResult(
    DateTimeOffset RetrievedAt,
    IReadOnlyList<LegacyNamedNxtThemeRestoreOutcome> Rows);

/// <summary>
/// Rehydrates legacy `테마_NXT` rows through exact, bound MariaDB reads.
/// Title is the only identity the old scene code actually used. A title is
/// accepted only when it names one current NXT SB_LIST row, that row's code is
/// unique, its preview validates through LegacyThemeSelectionService, and at
/// least one non-stopped item has a non-zero live price.
/// </summary>
public sealed partial class LegacyNamedNxtThemeRestoreService
{
    public const int MaximumRows = LegacyNamedPlaylistPersistenceService.MaximumItems;
    public const string IdentityQueryName = "LEGACY_NAMED_NXT_THEME_IDENTITY";

    private const string ExpectedGroupCode = "테마_NXT";
    private const string NxtSuffix = "(NXT)";
    private const int MaximumTitleLength = 128;

    private static readonly IReadOnlyDictionary<string, ActionProfile> Actions =
        new Dictionary<string, ActionProfile>(StringComparer.Ordinal)
        {
            ["5단 표그래프"] = new("five-row-current", "s5074", "5074", 5),
            ["6종목 현재가"] = new("six-row-current", "s5077", "5077", 6),
            ["12종목 현재가"] = new("twelve-row-current", "s5088", "5088", 12)
        };

    private static readonly IReadOnlyDictionary<string, string> CurrentSorts =
        new Dictionary<string, string>(StringComparer.Ordinal)
        {
            ["테마-현재가(입력순)"] = "INPUT_ORDER",
            ["테마-현재가(상승률순)"] = "GAIN_DESC",
            ["테마-현재가(하락률순)"] = "GAIN_ASC",
            // s5074/s5077/s5088 all fall through to their final loss-rate branch.
            ["테마-현재가"] = "GAIN_ASC"
        };

    private const string IdentitySql = """
        SELECT s.SB_TITLE THEME_TITLE,
               s.SB_CODE THEME_CODE,
               s.SB_MARKET THEME_MARKET,
               CAST((SELECT COUNT(*)
                     FROM SB_LIST d
                     WHERE d.SB_MARKET = 'NXT'
                       AND BINARY d.SB_CODE = BINARY s.SB_CODE) AS CHAR) CODE_IDENTITY_COUNT,
               CAST((SELECT COUNT(*)
                     FROM SB_ITEM i
                     JOIN v_all_stock q
                       ON SUBSTRING(i.SB_I_CODE, 2, 12) = q.F_STOCK_CODE
                     WHERE i.SB_M_CODE = s.SB_CODE
                       AND q.F_STOP_GUBUN = 'N') AS CHAR) PREVIEW_ITEM_COUNT,
               CAST((SELECT COUNT(*)
                     FROM SB_ITEM i
                     JOIN v_all_stock q
                       ON SUBSTRING(i.SB_I_CODE, 2, 12) = q.F_STOCK_CODE
                     WHERE i.SB_M_CODE = s.SB_CODE
                       AND q.F_STOP_GUBUN = 'N'
                       AND q.F_CURR_PRICE != 0) AS CHAR) LIVE_ITEM_COUNT
        FROM SB_LIST s
        WHERE s.SB_MARKET = 'NXT'
          AND BINARY s.SB_TITLE = BINARY @theme_title
        ORDER BY BINARY s.SB_CODE
        LIMIT 2
        """;

    private static readonly string[] IdentityColumns =
    [
        "THEME_TITLE",
        "THEME_CODE",
        "THEME_MARKET",
        "CODE_IDENTITY_COUNT",
        "PREVIEW_ITEM_COUNT",
        "LIVE_ITEM_COUNT"
    ];

    private readonly IDataQueryExecutor _executor;
    private readonly LegacyThemeSelectionService _themeSelectionService;
    private readonly TimeProvider _timeProvider;

    public LegacyNamedNxtThemeRestoreService(IDataQueryExecutor executor)
        : this(executor, TimeProvider.System)
    {
    }

    internal LegacyNamedNxtThemeRestoreService(
        IDataQueryExecutor executor,
        TimeProvider timeProvider)
    {
        _executor = executor ?? throw new ArgumentNullException(nameof(executor));
        _themeSelectionService = new LegacyThemeSelectionService(_executor);
        _timeProvider = timeProvider ?? throw new ArgumentNullException(nameof(timeProvider));
    }

    /// <summary>
    /// Routes every exact `테마_NXT` group row, including malformed rows, so
    /// the native result can fail that row explicitly instead of guessing.
    /// </summary>
    public static bool IsCandidate(LegacyNamedNxtThemeRestoreRow? row) =>
        row is not null &&
        row.ItemIndex is >= 0 and < MaximumRows &&
        string.Equals(row.GroupCode, ExpectedGroupCode, StringComparison.Ordinal);

    public async Task<LegacyNamedNxtThemeRestoreResult> ResolveAsync(
        IReadOnlyList<LegacyNamedNxtThemeRestoreRow> rows,
        CancellationToken cancellationToken = default)
    {
        ArgumentNullException.ThrowIfNull(rows);
        if (rows.Count is < 1 or > MaximumRows)
        {
            throw new ArgumentOutOfRangeException(
                nameof(rows),
                $"An NXT-theme restore must contain 1 through {MaximumRows} rows.");
        }

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

        var session = SessionForLocalHour(_timeProvider.GetLocalNow().Hour);
        var verifications = new Dictionary<string, ThemeVerification>(StringComparer.Ordinal);
        var outcomes = new List<LegacyNamedNxtThemeRestoreOutcome>(rows.Count);
        foreach (var row in rows)
        {
            cancellationToken.ThrowIfCancellationRequested();
            outcomes.Add(await ResolveRowAsync(row, session, verifications, cancellationToken)
                .ConfigureAwait(false));
        }

        return new LegacyNamedNxtThemeRestoreResult(
            _timeProvider.GetLocalNow(),
            outcomes.AsReadOnly());
    }

    public static ThemeSession SessionForLocalHour(int hour) => hour switch
    {
        >= 0 and <= 12 => ThemeSession.PreMarket,
        >= 13 and <= 23 => ThemeSession.AfterMarket,
        _ => throw new ArgumentOutOfRangeException(nameof(hour))
    };

    private async Task<LegacyNamedNxtThemeRestoreOutcome> ResolveRowAsync(
        LegacyNamedNxtThemeRestoreRow row,
        ThemeSession session,
        IDictionary<string, ThemeVerification> verifications,
        CancellationToken cancellationToken)
    {
        var classification = Classify(row);
        if (classification.Failure.HasValue)
        {
            return Failed(row.ItemIndex, classification.Failure.Value);
        }

        var title = classification.Title!;
        if (!verifications.TryGetValue(title, out var verification))
        {
            verification = await VerifyThemeAsync(title, session, cancellationToken)
                .ConfigureAwait(false);
            verifications.Add(title, verification);
        }

        if (verification.Failure.HasValue)
        {
            return Failed(
                row.ItemIndex,
                verification.Failure.Value,
                verification.CurrentThemeCode);
        }

        var profile = classification.Action!;
        return new LegacyNamedNxtThemeRestoreOutcome(
            row.ItemIndex,
            new LegacyNamedNxtThemeRestoreIdentity(
                profile.ActionId,
                profile.BuilderKey,
                profile.CutCode,
                profile.PageSize,
                classification.Sort!,
                session,
                title,
                row.DataCode,
                verification.CurrentThemeCode!,
                verification.PreviewItemCount,
                verification.LiveItemCount,
                verification.PreviewIsTruncated),
            Failure: null,
            ObservedCurrentThemeCode: verification.CurrentThemeCode);
    }

    private static RowClassification Classify(LegacyNamedNxtThemeRestoreRow row)
    {
        if (!string.Equals(row.GroupCode, ExpectedGroupCode, StringComparison.Ordinal) ||
            !IsSafeCanonicalText(row.Subject, MaximumTitleLength + NxtSuffix.Length) ||
            !row.Subject.EndsWith(NxtSuffix, StringComparison.Ordinal) ||
            row.Subject.Length == NxtSuffix.Length ||
            !StoredThemeCodePattern().IsMatch(row.DataCode ?? string.Empty) ||
            !IsValidStoredPage(row.PageText))
        {
            return new RowClassification(Failure: LegacyNamedNxtThemeRestoreFailure.InvalidRow);
        }

        var title = row.Subject[..^NxtSuffix.Length];
        if (!IsSafeCanonicalText(title, MaximumTitleLength) ||
            title.Contains(NxtSuffix, StringComparison.Ordinal))
        {
            return new RowClassification(Failure: LegacyNamedNxtThemeRestoreFailure.InvalidRow);
        }

        if (!Actions.TryGetValue(row.GraphicType ?? string.Empty, out var action) ||
            !CurrentSorts.TryGetValue(row.Subtype ?? string.Empty, out var sort))
        {
            return new RowClassification(Failure: LegacyNamedNxtThemeRestoreFailure.UnsupportedAction);
        }

        return new RowClassification(title, action, sort, Failure: null);
    }

    private async Task<ThemeVerification> VerifyThemeAsync(
        string title,
        ThemeSession session,
        CancellationToken cancellationToken)
    {
        var spec = new DataQuerySpec(
            IdentitySql,
            [new DataQueryParameter("theme_title", title, DbType.String)]);
        spec.ValidateFor(DataSourceKind.MariaDb);
        var table = await _executor.ExecuteAsync(
                DataSourceKind.MariaDb,
                IdentityQueryName,
                spec,
                cancellationToken)
            .ConfigureAwait(false);
        cancellationToken.ThrowIfCancellationRequested();

        var identity = ValidateIdentityTable(table, title);
        if (identity.Failure.HasValue)
        {
            return identity;
        }

        ThemePreviewResult preview;
        try
        {
            preview = await _themeSelectionService.PreviewAsync(
                    new ThemeSelectionIdentity(
                        ThemeMarket.Nxt,
                        session,
                        identity.CurrentThemeCode!,
                        title),
                    LegacyThemeSelectionService.MaximumPreviewItems,
                    cancellationToken)
                .ConfigureAwait(false);
        }
        catch (ThemeSelectionDataException)
        {
            return InvalidDatabaseData();
        }

        if (identity.PreviewItemCount == 0 || preview.Items.Count == 0)
        {
            return identity with
            {
                Failure = LegacyNamedNxtThemeRestoreFailure.PreviewEmpty
            };
        }

        var expectedVisibleCount = Math.Min(
            identity.PreviewItemCount,
            LegacyThemeSelectionService.MaximumPreviewItems);
        if (preview.Items.Count != expectedVisibleCount ||
            preview.IsTruncated !=
                (identity.PreviewItemCount > LegacyThemeSelectionService.MaximumPreviewItems) ||
            identity.LiveItemCount > identity.PreviewItemCount)
        {
            return InvalidDatabaseData();
        }

        if (identity.LiveItemCount == 0)
        {
            return identity with
            {
                Failure = LegacyNamedNxtThemeRestoreFailure.LiveItemsEmpty
            };
        }

        return identity with { PreviewIsTruncated = preview.IsTruncated };
    }

    private static ThemeVerification ValidateIdentityTable(DataTable? table, string title)
    {
        if (table is null || table.Columns.Count != IdentityColumns.Length ||
            table.Rows.Count > 2)
        {
            return InvalidDatabaseData();
        }

        for (var index = 0; index < IdentityColumns.Length; index++)
        {
            if (!string.Equals(
                    table.Columns[index].ColumnName,
                    IdentityColumns[index],
                    StringComparison.Ordinal) ||
                table.Columns[index].DataType != typeof(string))
            {
                return InvalidDatabaseData();
            }
        }

        if (table.Rows.Count == 0)
        {
            return new ThemeVerification(
                Failure: LegacyNamedNxtThemeRestoreFailure.IdentityNotFound);
        }

        if (table.Rows.Count > 1)
        {
            return new ThemeVerification(
                Failure: LegacyNamedNxtThemeRestoreFailure.IdentityAmbiguous);
        }

        var row = table.Rows[0];
        if (row[0] is not string actualTitle ||
            row[1] is not string code ||
            row[2] is not string market ||
            !string.Equals(actualTitle, title, StringComparison.Ordinal) ||
            !string.Equals(market, "NXT", StringComparison.Ordinal) ||
            !StoredThemeCodePattern().IsMatch(code) ||
            !TryReadCount(row[3], out var codeIdentityCount) ||
            !TryReadCount(row[4], out var previewItemCount) ||
            !TryReadCount(row[5], out var liveItemCount))
        {
            return InvalidDatabaseData();
        }

        if (codeIdentityCount != 1)
        {
            return new ThemeVerification(
                Failure: LegacyNamedNxtThemeRestoreFailure.IdentityAmbiguous);
        }

        return new ThemeVerification(
            CurrentThemeCode: code,
            PreviewItemCount: previewItemCount,
            LiveItemCount: liveItemCount,
            PreviewIsTruncated: false,
            Failure: null);
    }

    private static bool TryReadCount(object value, out int result)
    {
        result = 0;
        return value is string text &&
            int.TryParse(text, NumberStyles.None, CultureInfo.InvariantCulture, out result) &&
            result is >= 0 and <= 1_000_000 &&
            string.Equals(
                text,
                result.ToString(CultureInfo.InvariantCulture),
                StringComparison.Ordinal);
    }

    private static bool IsValidStoredPage(string value)
    {
        var match = StoredPagePattern().Match(value ?? string.Empty);
        if (!match.Success ||
            !int.TryParse(match.Groups[1].Value, NumberStyles.None, CultureInfo.InvariantCulture, out var current) ||
            !int.TryParse(match.Groups[2].Value, NumberStyles.None, CultureInfo.InvariantCulture, out var total))
        {
            return false;
        }

        return current is >= 1 and <= LegacyNamedPlaylistPersistenceService.MaximumStoredPageCount &&
            total is >= 0 and <= LegacyNamedPlaylistPersistenceService.MaximumStoredPageCount &&
            (total == 0 ? current == 1 : current <= total);
    }

    private static bool IsSafeCanonicalText(string? value, int maximumLength)
    {
        if (string.IsNullOrEmpty(value) || value.Length > maximumLength ||
            !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 LegacyNamedNxtThemeRestoreOutcome Failed(
        int itemIndex,
        LegacyNamedNxtThemeRestoreFailure failure,
        string? observedCurrentThemeCode = null) =>
        new(itemIndex, Identity: null, failure, observedCurrentThemeCode);

    private static ThemeVerification InvalidDatabaseData() =>
        new(Failure: LegacyNamedNxtThemeRestoreFailure.DatabaseDataInvalid);

    private sealed record ActionProfile(
        string ActionId,
        string BuilderKey,
        string CutCode,
        int PageSize);

    private sealed record RowClassification(
        string? Title = null,
        ActionProfile? Action = null,
        string? Sort = null,
        LegacyNamedNxtThemeRestoreFailure? Failure = null);

    private sealed record ThemeVerification(
        string? CurrentThemeCode = null,
        int PreviewItemCount = 0,
        int LiveItemCount = 0,
        bool PreviewIsTruncated = false,
        LegacyNamedNxtThemeRestoreFailure? Failure = null);

    [GeneratedRegex("^[0-9]{3,8}$", RegexOptions.CultureInvariant)]
    private static partial Regex StoredThemeCodePattern();

    [GeneratedRegex("^([1-9][0-9]{0,3})/([0-9]{1,4})$", RegexOptions.CultureInvariant)]
    private static partial Regex StoredPagePattern();
}
