#nullable enable

using System.Data;
using System.Globalization;

namespace MMoneyCoderSharp.Data;

public enum StockMarket
{
    Kospi,
    Kosdaq,
    NxtKospi,
    NxtKosdaq
}

public sealed record StockSearchItem(
    StockMarket Market,
    DataSourceKind Source,
    string Name,
    string Code);

public sealed record StockSearchResult(
    string Query,
    DateTimeOffset RetrievedAt,
    IReadOnlyList<StockSearchItem> Items,
    bool IsTruncated);

public interface IStockSearchService
{
    Task<StockSearchResult> SearchAsync(
        string query,
        int maximumResults = LegacyStockSearchService.DefaultMaximumResults,
        CancellationToken cancellationToken = default);
}

/// <summary>
/// Keeps the legacy ThemeA/UC6 stock-picker "show all" action explicit and
/// bounded without making an empty query valid for every stock-search caller.
/// </summary>
public interface IOperatorCatalogStockSearchService : IStockSearchService
{
    Task<StockSearchResult> SearchAllAsync(
        int maximumResults = LegacyStockSearchService.DefaultMaximumResults,
        CancellationToken cancellationToken = default);
}

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

/// <summary>
/// Searches the four domestic stock masters used by the legacy operator lookup.
/// Input is always bound, LIKE metacharacters are escaped, and every provider
/// result must match the two-column contract before any value reaches the UI.
/// </summary>
public sealed class LegacyStockSearchService : IStockSearchService
{
    public const int DefaultMaximumResults = 100;
    public const int MaximumResults = 10_000;
    public const int MaximumQueryLength = 64;

    private const int MaximumStockNameLength = 200;
    private const int MaximumStockCodeLength = 32;
    private const string StockNameColumn = "STOCK_NAME";
    private const string StockCodeColumn = "STOCK_CODE";

    private readonly IDataQueryExecutor _executor;

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

    public async Task<StockSearchResult> SearchAsync(
        string query,
        int maximumResults = DefaultMaximumResults,
        CancellationToken cancellationToken = default)
    {
        var normalizedQuery = ValidateAndNormalizeQuery(query);
        return await SearchNormalizedAsync(
                normalizedQuery,
                maximumResults,
                cancellationToken)
            .ConfigureAwait(false);
    }

    internal Task<StockSearchResult> SearchAllForOperatorCatalogAsync(
        int maximumResults,
        CancellationToken cancellationToken) =>
        SearchNormalizedAsync(string.Empty, maximumResults, cancellationToken);

    private async Task<StockSearchResult> SearchNormalizedAsync(
        string normalizedQuery,
        int maximumResults,
        CancellationToken cancellationToken)
    {
        if (maximumResults is < 1 or > MaximumResults)
        {
            throw new ArgumentOutOfRangeException(
                nameof(maximumResults),
                $"The requested result limit must be between 1 and {MaximumResults}.");
        }

        cancellationToken.ThrowIfCancellationRequested();

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

        // Keep provider load predictable and cancellation responsive. The shared
        // application database gate grants this whole operation one read slot.
        foreach (var profile in StockSearchQueries.All)
        {
            cancellationToken.ThrowIfCancellationRequested();
            var spec = profile.CreateSpec(escapedQuery, perMarketLimit);
            var table = await _executor
                .ExecuteAsync(profile.Source, profile.TableName, spec, cancellationToken)
                .ConfigureAwait(false);
            cancellationToken.ThrowIfCancellationRequested();
            AppendValidatedRows(table, profile, matches);
            providerCandidateWindowSaturated |=
                isInitialSearch && table.Rows.Count >= perMarketLimit;
        }

        // Oracle and MariaDB cannot compare compatibility-jamo initials with
        // composed Hangul syllables through LIKE. Validate the complete finite
        // provider candidate windows first, then apply the shared matcher only
        // to the display name. Exact market/code identities remain unchanged.
        var filteredMatches = isInitialSearch
            ? matches.Where(item => HangulSearchMatcher.IsMatch(
                item.Name,
                normalizedQuery)).ToList()
            : matches;
        var ordered = filteredMatches
            .OrderBy(static item => item.Market)
            .ThenBy(static item => item.Name, StringComparer.Ordinal)
            .ThenBy(static item => item.Code, StringComparer.Ordinal)
            .Take(maximumResults)
            .ToArray();

        return new StockSearchResult(
            normalizedQuery,
            DateTimeOffset.Now,
            ordered,
            filteredMatches.Count > maximumResults ||
                providerCandidateWindowSaturated);
    }

    private static string ValidateAndNormalizeQuery(string query)
    {
        ArgumentNullException.ThrowIfNull(query);

        var normalized = query.Trim();
        if (!IsSafeText(query) ||
            normalized.Length is < 1 or > MaximumQueryLength ||
            !IsSafeText(normalized))
        {
            throw new ArgumentException(
                $"A stock search query must contain 1 to {MaximumQueryLength} visible characters.",
                nameof(query));
        }

        return normalized;
    }

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

    private static void AppendValidatedRows(
        DataTable? table,
        StockSearchQueryProfile profile,
        ICollection<StockSearchItem> destination)
    {
        if (table is null ||
            table.Columns.Count != 2 ||
            !HasExactStringColumn(table.Columns[0], StockNameColumn) ||
            !HasExactStringColumn(table.Columns[1], StockCodeColumn))
        {
            throw InvalidSchema(profile.TableName);
        }

        foreach (DataRow row in table.Rows)
        {
            if (row[0] is not string rawName || row[1] is not string rawCode)
            {
                throw InvalidSchema(profile.TableName);
            }

            var name = rawName.Trim();
            var code = rawCode.Trim();
            if (!IsSafeDatabaseValue(name, MaximumStockNameLength) ||
                !IsSafeDatabaseValue(code, MaximumStockCodeLength))
            {
                throw new StockSearchDataException(
                    $"Stock search data from {profile.TableName} contains an invalid value.");
            }

            destination.Add(new StockSearchItem(profile.Market, profile.Source, name, code));
        }
    }

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

    private static bool IsSafeDatabaseValue(string value, int maximumLength) =>
        value.Length is > 0 && value.Length <= maximumLength && IsSafeText(value);

    private static bool IsSafeText(string value)
    {
        for (var index = 0; index < value.Length; index++)
        {
            var character = value[index];
            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 StockSearchDataException InvalidSchema(string tableName) =>
        new($"Stock search data from {tableName} does not match the required schema.");
}

internal sealed record StockSearchQueryProfile(
    StockMarket Market,
    DataSourceKind Source,
    string TableName,
    string Sql)
{
    internal DataQuerySpec CreateSpec(string escapedQuery, int rowLimit)
    {
        var queryParameter = new DataQueryParameter(
            "search_term",
            escapedQuery,
            DbType.String);
        var limitParameter = new DataQueryParameter(
            "row_limit",
            rowLimit,
            DbType.Int32);
        var spec = new DataQuerySpec(Sql, [queryParameter, limitParameter]);
        spec.ValidateFor(Source);
        return spec;
    }
}

/// <summary>
/// Operator-catalog-only adapter for the original blank stock-picker action.
/// Non-empty searches retain the normal validation contract; only SearchAllAsync
/// can issue a bound empty LIKE term.
/// </summary>
public sealed class LegacyOperatorCatalogStockSearchService :
    IOperatorCatalogStockSearchService
{
    private readonly LegacyStockSearchService _stockSearchService;

    public LegacyOperatorCatalogStockSearchService(IDataQueryExecutor executor)
        : this(new LegacyStockSearchService(executor))
    {
    }

    public LegacyOperatorCatalogStockSearchService(
        LegacyStockSearchService stockSearchService)
    {
        _stockSearchService = stockSearchService ??
            throw new ArgumentNullException(nameof(stockSearchService));
    }

    public Task<StockSearchResult> SearchAsync(
        string query,
        int maximumResults = LegacyStockSearchService.DefaultMaximumResults,
        CancellationToken cancellationToken = default) =>
        _stockSearchService.SearchAsync(query, maximumResults, cancellationToken);

    public Task<StockSearchResult> SearchAllAsync(
        int maximumResults = LegacyStockSearchService.DefaultMaximumResults,
        CancellationToken cancellationToken = default) =>
        _stockSearchService.SearchAllForOperatorCatalogAsync(
            maximumResults,
            cancellationToken);
}

internal static class StockSearchQueries
{
    internal static IReadOnlyList<StockSearchQueryProfile> All { get; } =
    [
        new(
            StockMarket.Kospi,
            DataSourceKind.Oracle,
            "STOCK_SEARCH_KOSPI",
            """
            SELECT STOCK_NAME, STOCK_CODE
            FROM (
                SELECT F_STOCK_WANNAME AS STOCK_NAME, F_STOCK_CODE AS STOCK_CODE
                FROM T_STOCK
                WHERE F_MKT_HALT = 'N'
                  AND UPPER(F_STOCK_WANNAME) LIKE '%' || :search_term || '%' ESCAPE '!'
                ORDER BY UPPER(F_STOCK_WANNAME), F_STOCK_CODE
            )
            WHERE ROWNUM <= :row_limit
            """),
        new(
            StockMarket.Kosdaq,
            DataSourceKind.Oracle,
            "STOCK_SEARCH_KOSDAQ",
            """
            SELECT STOCK_NAME, STOCK_CODE
            FROM (
                SELECT F_STOCK_WANNAME AS STOCK_NAME, F_STOCK_CODE AS STOCK_CODE
                FROM T_KOSDAQ_STOCK
                WHERE F_MKT_HALT = 'N'
                  AND UPPER(F_STOCK_WANNAME) LIKE '%' || :search_term || '%' ESCAPE '!'
                ORDER BY UPPER(F_STOCK_WANNAME), F_STOCK_CODE
            )
            WHERE ROWNUM <= :row_limit
            """),
        new(
            StockMarket.NxtKospi,
            DataSourceKind.MariaDb,
            "STOCK_SEARCH_NXT_KOSPI",
            """
            SELECT CONCAT(a.F_STOCK_NAME, '(NXT)') 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 UPPER(a.F_STOCK_NAME) LIKE CONCAT('%', @search_term, '%') ESCAPE '!'
            ORDER BY UPPER(a.F_STOCK_NAME), a.F_STOCK_CODE
            LIMIT @row_limit
            """),
        new(
            StockMarket.NxtKosdaq,
            DataSourceKind.MariaDb,
            "STOCK_SEARCH_NXT_KOSDAQ",
            """
            SELECT CONCAT(a.F_STOCK_NAME, '(NXT)') 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 UPPER(a.F_STOCK_NAME) LIKE CONCAT('%', @search_term, '%') ESCAPE '!'
            ORDER BY UPPER(a.F_STOCK_NAME), a.F_STOCK_CODE
            LIMIT @row_limit
            """)
    ];
}
