#nullable enable

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

namespace MMoneyCoderSharp.Data;

public enum ThemeMarket
{
    Krx,
    Nxt
}

public enum ThemeSession
{
    NotApplicable,
    PreMarket,
    AfterMarket
}

/// <summary>
/// Closed theme identity carried from selector to preview and eventually to the
/// paged scene request. Market and NXT session are explicit; the UI must never
/// infer either value from a display-title suffix.
/// </summary>
public sealed record ThemeSelectionIdentity(
    ThemeMarket Market,
    ThemeSession Session,
    string ThemeCode,
    string ThemeTitle);

public sealed record ThemeSelectorItem(
    ThemeSelectionIdentity Identity,
    DataSourceKind Source);

public sealed record ThemeSearchResult(
    string Query,
    ThemeSession NxtSession,
    DateTimeOffset RetrievedAt,
    IReadOnlyList<ThemeSelectorItem> Items,
    bool IsTruncated);

public sealed record ThemeItemPreview(
    int InputIndex,
    string ItemCode,
    string ItemName);

public sealed record ThemePreviewResult(
    ThemeSelectionIdentity Identity,
    DateTimeOffset RetrievedAt,
    IReadOnlyList<ThemeItemPreview> Items,
    bool IsTruncated);

public interface IThemeSelectionService
{
    Task<ThemeSearchResult> SearchAsync(
        string query,
        ThemeSession nxtSession,
        int maximumResults = LegacyThemeSelectionService.DefaultMaximumResults,
        CancellationToken cancellationToken = default);

    Task<ThemePreviewResult> PreviewAsync(
        ThemeSelectionIdentity identity,
        int maximumItems = LegacyThemeSelectionService.DefaultMaximumPreviewItems,
        CancellationToken cancellationToken = default);
}

public sealed class ThemeSelectionDataException : Exception
{
    public ThemeSelectionDataException(string message)
        : base(message)
    {
    }
}

/// <summary>
/// Read-only migration of UC4's theme list and selected-item preview. KRX data
/// remains on Oracle and NXT data remains on MariaDB, matching both the original
/// Nxt_ViewQuery path and the migrated PAGED_THEME/PAGED_NXT_THEME loaders.
/// </summary>
public sealed partial class LegacyThemeSelectionService : IThemeSelectionService
{
    // The deployed UC4 selector loads the whole current KRX/NXT theme catalog.
    // Keep a finite defensive ceiling, but do not silently cut the current
    // catalog at the earlier migration-only 100/500 row limits.
    public const int DefaultMaximumResults = 5_000;
    public const int MaximumResults = 5_000;
    public const int MaximumQueryLength = 64;
    public const int DefaultMaximumPreviewItems = 240;
    public const int MaximumPreviewItems = 240;

    private const int MaximumThemeTitleLength = 128;
    private const int MaximumItemNameLength = 200;
    private const int MaximumItemIndex = 9_999;

    private static readonly string[] SearchColumns =
        ["THEME_TITLE", "THEME_CODE", "THEME_MARKET"];
    private static readonly string[] PreviewColumns =
    [
        "THEME_TITLE",
        "THEME_CODE",
        "THEME_MARKET",
        "ITEM_NAME",
        "ITEM_CODE",
        "ITEM_INDEX"
    ];

    private readonly IDataQueryExecutor _executor;

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

    public async Task<ThemeSearchResult> SearchAsync(
        string query,
        ThemeSession nxtSession,
        int maximumResults = DefaultMaximumResults,
        CancellationToken cancellationToken = default)
    {
        var normalizedQuery = ValidateAndNormalizeSearchQuery(query);
        ValidateNxtSession(nxtSession, nameof(nxtSession));
        ValidateLimit(maximumResults, MaximumResults, nameof(maximumResults));
        cancellationToken.ThrowIfCancellationRequested();

        var isInitialSearch =
            HangulSearchMatcher.IsInitialConsonantQuery(normalizedQuery);
        var escapedQuery = isInitialSearch
            ? string.Empty
            : EscapeLikePattern(normalizedQuery.ToUpperInvariant());
        var perMarketLimit = checked(
            (isInitialSearch ? MaximumResults : maximumResults) + 1);
        var matches = new List<ThemeSelectorItem>(perMarketLimit * ThemeQueries.All.Count);
        var candidateSetIsTruncated = false;

        foreach (var profile in ThemeQueries.All)
        {
            cancellationToken.ThrowIfCancellationRequested();
            var table = await _executor.ExecuteAsync(
                profile.Source,
                profile.SearchName,
                profile.CreateSearchSpec(escapedQuery, perMarketLimit),
                cancellationToken).ConfigureAwait(false);
            cancellationToken.ThrowIfCancellationRequested();
            AppendSearchRows(table, profile, nxtSession, perMarketLimit, matches);
            candidateSetIsTruncated |=
                isInitialSearch && table.Rows.Count == perMarketLimit;
        }

        IReadOnlyList<ThemeSelectorItem> filteredMatches = isInitialSearch
            ? matches.Where(item => HangulSearchMatcher.IsMatch(
                item.Identity.ThemeTitle,
                normalizedQuery)).ToArray()
            : matches;
        var ordered = filteredMatches
            .Take(maximumResults)
            .ToArray();

        return new ThemeSearchResult(
            normalizedQuery,
            nxtSession,
            DateTimeOffset.Now,
            ordered,
            filteredMatches.Count > maximumResults ||
                candidateSetIsTruncated);
    }

    public async Task<ThemePreviewResult> PreviewAsync(
        ThemeSelectionIdentity identity,
        int maximumItems = DefaultMaximumPreviewItems,
        CancellationToken cancellationToken = default)
    {
        var canonicalIdentity = ValidateIdentity(identity);
        ValidateLimit(maximumItems, MaximumPreviewItems, nameof(maximumItems));
        cancellationToken.ThrowIfCancellationRequested();

        var profile = ThemeQueries.For(canonicalIdentity.Market);
        var rowLimit = checked(maximumItems + 1);
        var table = await _executor.ExecuteAsync(
            profile.Source,
            profile.PreviewName,
            profile.CreatePreviewSpec(canonicalIdentity, rowLimit),
            cancellationToken).ConfigureAwait(false);
        cancellationToken.ThrowIfCancellationRequested();

        var items = MapPreviewRows(table, profile, canonicalIdentity, rowLimit);
        return new ThemePreviewResult(
            canonicalIdentity,
            DateTimeOffset.Now,
            items.Take(maximumItems).ToArray(),
            items.Count > maximumItems);
    }

    private static void AppendSearchRows(
        DataTable? table,
        ThemeQueryProfile profile,
        ThemeSession nxtSession,
        int rowLimit,
        ICollection<ThemeSelectorItem> destination)
    {
        ValidateExactStringSchema(table, SearchColumns, rowLimit, profile.SearchName);

        var codes = new HashSet<string>(StringComparer.Ordinal);
        foreach (DataRow row in table!.Rows)
        {
            var title = ReadStoredThemeTitle(row, 0, profile.SearchName);
            var code = ReadThemeCode(row, 1, profile.SearchName);
            var market = ReadCanonicalString(row, 2, 3, profile.SearchName);
            if (!string.Equals(market, profile.DatabaseMarket, StringComparison.Ordinal))
            {
                throw InvalidData(profile.SearchName, "market identity");
            }

            // The deployed catalog contains distinct codes that intentionally
            // share a title. Code remains the closed per-market identity; the
            // exact stored title is carried alongside it for correlated reads.
            if (!codes.Add(code))
            {
                throw InvalidData(profile.SearchName, "duplicate theme code");
            }

            var session = profile.Market == ThemeMarket.Krx
                ? ThemeSession.NotApplicable
                : nxtSession;
            destination.Add(new ThemeSelectorItem(
                new ThemeSelectionIdentity(profile.Market, session, code, title),
                profile.Source));
        }
    }

    private static IReadOnlyList<ThemeItemPreview> MapPreviewRows(
        DataTable? table,
        ThemeQueryProfile profile,
        ThemeSelectionIdentity identity,
        int rowLimit)
    {
        ValidateExactStringSchema(table, PreviewColumns, rowLimit, profile.PreviewName);
        if (table!.Rows.Count < 1)
        {
            throw InvalidData(profile.PreviewName, "missing selected theme");
        }

        var items = new List<ThemeItemPreview>(table.Rows.Count);
        var indexes = new HashSet<int>();
        var sawEmptyThemeRow = false;
        foreach (DataRow row in table.Rows)
        {
            var title = ReadStoredThemeTitle(row, 0, profile.PreviewName);
            var code = ReadThemeCode(row, 1, profile.PreviewName);
            var market = ReadCanonicalString(row, 2, 3, profile.PreviewName);
            if (!string.Equals(title, identity.ThemeTitle, StringComparison.Ordinal) ||
                !string.Equals(code, identity.ThemeCode, StringComparison.Ordinal) ||
                !string.Equals(market, profile.DatabaseMarket, StringComparison.Ordinal))
            {
                throw InvalidData(profile.PreviewName, "selected theme identity");
            }

            var nameValue = row[3];
            var codeValue = row[4];
            var indexValue = row[5];
            var isEmptyThemeRow = nameValue is DBNull && codeValue is DBNull && indexValue is DBNull;
            if (isEmptyThemeRow)
            {
                if (table.Rows.Count != 1)
                {
                    throw InvalidData(profile.PreviewName, "empty item sentinel");
                }

                sawEmptyThemeRow = true;
                continue;
            }

            if (nameValue is DBNull || codeValue is DBNull || indexValue is DBNull)
            {
                throw InvalidData(profile.PreviewName, "partial item identity");
            }

            var itemName = ReadCanonicalString(row, 3, MaximumItemNameLength, profile.PreviewName);
            var itemCode = ReadCanonicalString(row, 4, 13, profile.PreviewName);
            if (!IsItemCodeForMarket(itemCode, identity.Market))
            {
                throw InvalidData(profile.PreviewName, "item code");
            }

            var rawIndex = ReadCanonicalString(row, 5, 4, profile.PreviewName);
            if (!int.TryParse(rawIndex, NumberStyles.None, CultureInfo.InvariantCulture, out var inputIndex) ||
                inputIndex is < 0 or > MaximumItemIndex ||
                !string.Equals(
                    rawIndex,
                    inputIndex.ToString(CultureInfo.InvariantCulture),
                    StringComparison.Ordinal))
            {
                throw InvalidData(profile.PreviewName, "item index");
            }

            if (!indexes.Add(inputIndex))
            {
                throw InvalidData(profile.PreviewName, "duplicate item index");
            }

            items.Add(new ThemeItemPreview(inputIndex, itemCode, itemName));
        }

        if (sawEmptyThemeRow)
        {
            return Array.Empty<ThemeItemPreview>();
        }

        for (var index = 1; index < items.Count; index++)
        {
            if (items[index - 1].InputIndex >= items[index].InputIndex)
            {
                throw InvalidData(profile.PreviewName, "item order");
            }
        }

        return items;
    }

    private static ThemeSelectionIdentity ValidateIdentity(ThemeSelectionIdentity identity)
    {
        ArgumentNullException.ThrowIfNull(identity);
        if (!Enum.IsDefined(identity.Market) || !Enum.IsDefined(identity.Session))
        {
            throw new ArgumentException("The theme selection identity is invalid.", nameof(identity));
        }

        if (identity.Market == ThemeMarket.Krx && identity.Session != ThemeSession.NotApplicable ||
            identity.Market == ThemeMarket.Nxt &&
            identity.Session is not (ThemeSession.PreMarket or ThemeSession.AfterMarket))
        {
            throw new ArgumentException(
                "The theme selection market and session do not form a safe identity.",
                nameof(identity));
        }

        var title = ValidateStoredThemeTitle(identity.ThemeTitle, nameof(identity));
        var code = ValidateCanonicalInput(identity.ThemeCode, 8, nameof(identity));
        if (!ThemeCodePattern().IsMatch(code))
        {
            throw new ArgumentException("The theme selection code is invalid.", nameof(identity));
        }

        return identity with { ThemeTitle = title, ThemeCode = code };
    }

    private static string ValidateAndNormalizeSearchQuery(string query)
    {
        ArgumentNullException.ThrowIfNull(query);
        if (!IsSafeText(query))
        {
            throw new ArgumentException("The theme search query is invalid.", nameof(query));
        }

        var normalized = query.Trim();
        if (normalized.Length > MaximumQueryLength || !IsSafeText(normalized))
        {
            throw new ArgumentException(
                $"A theme search query must contain at most {MaximumQueryLength} visible characters.",
                nameof(query));
        }

        return normalized;
    }

    private static string ValidateCanonicalInput(
        string value,
        int maximumLength,
        string parameterName)
    {
        ArgumentNullException.ThrowIfNull(value);
        if (value.Length is < 1 || value.Length > maximumLength ||
            !string.Equals(value, value.Trim(), StringComparison.Ordinal) ||
            !IsSafeText(value))
        {
            throw new ArgumentException("The theme selection value is invalid.", parameterName);
        }

        return value;
    }

    private static string ValidateStoredThemeTitle(string value, string parameterName)
    {
        ArgumentNullException.ThrowIfNull(value);
        if (value.Length is < 1 or > MaximumThemeTitleLength ||
            string.IsNullOrWhiteSpace(value) ||
            !IsSafeText(value))
        {
            throw new ArgumentException("The stored theme title is invalid.", parameterName);
        }

        // Legacy SB_LIST titles can contain meaningful leading or trailing
        // spaces. Preserve the exact database value as part of the identity;
        // trimming it can collapse distinct deployed rows.
        return value;
    }

    private static void ValidateNxtSession(ThemeSession session, string parameterName)
    {
        if (session is not (ThemeSession.PreMarket or ThemeSession.AfterMarket))
        {
            throw new ArgumentOutOfRangeException(
                parameterName,
                session,
                "An explicit NXT pre-market or after-market session is required.");
        }
    }

    private static void ValidateLimit(int value, int maximum, string parameterName)
    {
        if (value is < 1 || value > maximum)
        {
            throw new ArgumentOutOfRangeException(
                parameterName,
                value,
                $"The requested limit must be between 1 and {maximum}.");
        }
    }

    private static void ValidateExactStringSchema(
        DataTable? table,
        IReadOnlyList<string> columns,
        int rowLimit,
        string queryName)
    {
        if (table is null || table.Columns.Count != columns.Count || table.Rows.Count > rowLimit)
        {
            throw InvalidData(queryName, "schema or row bound");
        }

        for (var index = 0; index < columns.Count; index++)
        {
            if (!string.Equals(
                    table.Columns[index].ColumnName,
                    columns[index],
                    StringComparison.Ordinal) ||
                table.Columns[index].DataType != typeof(string))
            {
                throw InvalidData(queryName, "schema");
            }
        }
    }

    private static string ReadCanonicalString(
        DataRow row,
        int ordinal,
        int maximumLength,
        string queryName)
    {
        if (row[ordinal] is not string value ||
            value.Length is < 1 || value.Length > maximumLength ||
            !string.Equals(value, value.Trim(), StringComparison.Ordinal) ||
            !IsSafeText(value))
        {
            throw InvalidData(queryName, "value");
        }

        return value;
    }

    private static string ReadStoredThemeTitle(
        DataRow row,
        int ordinal,
        string queryName)
    {
        if (row[ordinal] is not string value ||
            value.Length is < 1 or > MaximumThemeTitleLength ||
            string.IsNullOrWhiteSpace(value) ||
            !IsSafeText(value))
        {
            throw InvalidData(queryName, "stored theme title");
        }

        return value;
    }

    private static string ReadThemeCode(DataRow row, int ordinal, string queryName)
    {
        var code = ReadCanonicalString(row, ordinal, 8, queryName);
        if (!ThemeCodePattern().IsMatch(code))
        {
            throw InvalidData(queryName, "theme code");
        }

        return code;
    }

    private static bool IsItemCodeForMarket(string value, ThemeMarket market)
    {
        var pattern = market == ThemeMarket.Krx
            ? KrxItemCodePattern()
            : NxtItemCodePattern();
        return pattern.IsMatch(value);
    }

    private static string EscapeLikePattern(string value) => value
        .Replace("!", "!!", StringComparison.Ordinal)
        .Replace("%", "!%", StringComparison.Ordinal)
        .Replace("_", "!_", StringComparison.Ordinal);

    private static bool IsSafeText(string value)
    {
        foreach (var rune in value.EnumerateRunes())
        {
            var category = Rune.GetUnicodeCategory(rune);
            if (Rune.IsControl(rune) || rune.Value == '\uFFFD' ||
                category is UnicodeCategory.Format or UnicodeCategory.Surrogate or
                    UnicodeCategory.LineSeparator or
                    UnicodeCategory.ParagraphSeparator)
            {
                return false;
            }
        }

        return true;
    }

    private static ThemeSelectionDataException InvalidData(string queryName, string detail) =>
        new($"Theme selection data from {queryName} contains an invalid {detail}.");

    // ThemeA writes new identifiers with D8, while the deployed SB_LIST still
    // contains legacy three- and four-digit identifiers that remain valid read identities.
    [GeneratedRegex("^[0-9]{3,8}$", RegexOptions.CultureInvariant)]
    private static partial Regex ThemeCodePattern();

    [GeneratedRegex("^[PD][A-Za-z0-9]{1,12}$", RegexOptions.CultureInvariant)]
    private static partial Regex KrxItemCodePattern();

    [GeneratedRegex("^[XT][A-Za-z0-9]{1,12}$", RegexOptions.CultureInvariant)]
    private static partial Regex NxtItemCodePattern();
}

internal sealed record ThemeQueryProfile(
    ThemeMarket Market,
    DataSourceKind Source,
    string DatabaseMarket,
    string SearchName,
    string PreviewName,
    string SearchSql,
    string PreviewSql)
{
    internal DataQuerySpec CreateSearchSpec(string escapedQuery, int rowLimit)
    {
        var spec = new DataQuerySpec(
            SearchSql,
            [
                new DataQueryParameter("search_term", escapedQuery, DbType.String),
                new DataQueryParameter("row_limit", rowLimit, DbType.Int32)
            ]);
        spec.ValidateFor(Source);
        return spec;
    }

    internal DataQuerySpec CreatePreviewSpec(ThemeSelectionIdentity identity, int rowLimit)
    {
        var spec = new DataQuerySpec(
            PreviewSql,
            [
                new DataQueryParameter("theme_code", identity.ThemeCode, DbType.String),
                new DataQueryParameter("theme_title", identity.ThemeTitle, DbType.String),
                new DataQueryParameter("row_limit", rowLimit, DbType.Int32)
            ]);
        spec.ValidateFor(Source);
        return spec;
    }
}

internal static class ThemeQueries
{
    private static readonly ThemeQueryProfile Krx = new(
        ThemeMarket.Krx,
        DataSourceKind.Oracle,
        "KRX",
        "THEME_SEARCH_KRX",
        "THEME_PREVIEW_KRX",
        """
        SELECT THEME_TITLE, THEME_CODE, THEME_MARKET
        FROM (
            SELECT s.SB_TITLE THEME_TITLE,
                   s.SB_CODE THEME_CODE,
                   s.SB_MARKET THEME_MARKET
            FROM SB_LIST s
            WHERE s.SB_MARKET = 'KRX'
              AND s.SB_TITLE IS NOT NULL
              AND LENGTH(s.SB_TITLE) BETWEEN 1 AND 128
              AND REGEXP_LIKE(s.SB_CODE, '^[0-9]{3,8}$', 'c')
              AND (SELECT COUNT(*)
                   FROM SB_LIST d
                   WHERE d.SB_MARKET = s.SB_MARKET
                     AND d.SB_CODE = s.SB_CODE) = 1
              AND UPPER(s.SB_TITLE) LIKE '%' || :search_term || '%' ESCAPE '!'
        )
        WHERE ROWNUM <= :row_limit
        """,
        """
        SELECT THEME_TITLE, THEME_CODE, THEME_MARKET,
               ITEM_NAME, ITEM_CODE, ITEM_INDEX
        FROM (
            SELECT l.SB_TITLE THEME_TITLE,
                   l.SB_CODE THEME_CODE,
                   l.SB_MARKET THEME_MARKET,
                   a.SB_I_NAME ITEM_NAME,
                   a.SB_I_CODE ITEM_CODE,
                   TO_CHAR(a.SB_I_INDEX, 'FM999999990') ITEM_INDEX
            FROM SB_LIST l
            LEFT JOIN (
                SELECT i.SB_M_CODE, i.SB_I_NAME, i.SB_I_CODE, i.SB_I_INDEX
                FROM SB_ITEM i
                JOIN v_all_stock s
                  ON SUBSTR(i.SB_I_CODE, 2, 12) = s.F_STOCK_CODE
                WHERE s.F_MKT_HALT = 'N'
            ) a ON l.SB_CODE = a.SB_M_CODE
            WHERE l.SB_CODE = :theme_code
              AND l.SB_TITLE = :theme_title
              AND l.SB_MARKET = 'KRX'
            ORDER BY a.SB_I_INDEX
        )
        WHERE ROWNUM <= :row_limit
        """);

    private static readonly ThemeQueryProfile Nxt = new(
        ThemeMarket.Nxt,
        DataSourceKind.MariaDb,
        "NXT",
        "THEME_SEARCH_NXT",
        "THEME_PREVIEW_NXT",
        """
        SELECT s.SB_TITLE THEME_TITLE,
               s.SB_CODE THEME_CODE,
               s.SB_MARKET THEME_MARKET
        FROM SB_LIST s
        WHERE s.SB_MARKET = 'NXT'
          AND s.SB_TITLE IS NOT NULL
          AND CHAR_LENGTH(s.SB_TITLE) BETWEEN 1 AND 128
          AND s.SB_CODE REGEXP BINARY '^[0-9]{3,8}$'
          AND (SELECT COUNT(*)
               FROM SB_LIST d
               WHERE d.SB_MARKET = s.SB_MARKET
                 AND BINARY d.SB_CODE = BINARY s.SB_CODE) = 1
          AND UPPER(s.SB_TITLE) LIKE CONCAT('%', @search_term, '%') ESCAPE '!'
        LIMIT @row_limit
        """,
        """
        SELECT l.SB_TITLE THEME_TITLE,
               l.SB_CODE THEME_CODE,
               l.SB_MARKET THEME_MARKET,
               a.SB_I_NAME ITEM_NAME,
               a.SB_I_CODE ITEM_CODE,
               CAST(a.SB_I_INDEX AS CHAR) ITEM_INDEX
        FROM SB_LIST l
        LEFT JOIN (
            SELECT i.SB_M_CODE, i.SB_I_NAME, i.SB_I_CODE, i.SB_I_INDEX
            FROM SB_ITEM i
            JOIN (
                SELECT CONCAT('X', s.F_STOCK_CODE) ITEM_CODE
                FROM N_STOCK s
                WHERE s.F_STOP_GUBUN = 'N'
                UNION ALL
                SELECT CONCAT('T', s.F_STOCK_CODE) ITEM_CODE
                FROM N_KOSDAQ_STOCK s
                WHERE s.F_STOP_GUBUN = 'N'
            ) current_nxt
              ON BINARY i.SB_I_CODE = BINARY current_nxt.ITEM_CODE
        ) a ON BINARY l.SB_CODE = BINARY a.SB_M_CODE
           AND l.SB_CODE REGEXP BINARY '^[0-9]{8}$'
        WHERE l.SB_CODE = @theme_code
          AND l.SB_TITLE = @theme_title
          AND l.SB_MARKET = 'NXT'
        ORDER BY a.SB_I_INDEX
        LIMIT @row_limit
        """);

    internal static IReadOnlyList<ThemeQueryProfile> All { get; } = [Krx, Nxt];

    internal static ThemeQueryProfile For(ThemeMarket market) => market switch
    {
        ThemeMarket.Krx => Krx,
        ThemeMarket.Nxt => Nxt,
        _ => throw new ArgumentOutOfRangeException(nameof(market), market, "Unsupported theme market.")
    };
}
