#nullable enable

using System.Data;
using System.Globalization;

namespace MMoneyCoderSharp.Data;

/// <summary>
/// UC5-only stock lookup contract. It is intentionally separate from the
/// comparison workflow's US/TW-only world-stock lookup.
/// </summary>
public interface IOverseasStockSearchService
{
    Task<WorldStockSearchResult> SearchAsync(
        string query,
        int maximumResults = LegacyOverseasStockSearchService.DefaultMaximumResults,
        CancellationToken cancellationToken = default);
}

/// <summary>
/// Parameterized read-only port of UC5.jongmok_search. The original initial
/// list is restricted to US/TW; a non-empty search intentionally spans every
/// foreign nation with F_FDTC = '1'.
/// </summary>
public sealed class LegacyOverseasStockSearchService : IOverseasStockSearchService
{
    public const int MaximumResults = 2_000;
    public const int MaximumIsolatedRows = 128;
    // UC5 populated every returned row. Keep the bounded migration at its
    // highest allowed size so the compatibility limit is not silently lower
    // than necessary; callers surface IsTruncated when the bound is reached.
    public const int DefaultMaximumResults = MaximumResults;
    public const int MaximumQueryLength = 64;

    private const int MaximumInputNameLength = 200;
    private const int MaximumSymbolLength = 64;
    private const int MaximumNationCodeLength = 8;
    private const string QueryName = "OVERSEAS_STOCK_SEARCH";

    private const string InitialListSql = """
        SELECT F_INPUT_NAME, F_SYMB, F_NATC
        FROM (
            SELECT F_INPUT_NAME, F_SYMB, F_NATC
            FROM T_WORLD_IX_EQ_MASTER
            WHERE F_FDTC = '1'
              AND F_NATC IN ('US', 'TW')
            ORDER BY F_INPUT_NAME
        )
        WHERE ROWNUM <= :row_limit
        """;

    private const string SearchSql = """
        SELECT F_INPUT_NAME, F_SYMB, F_NATC
        FROM (
            SELECT F_INPUT_NAME, F_SYMB, F_NATC
            FROM T_WORLD_IX_EQ_MASTER
            WHERE F_FDTC = '1'
              AND UPPER(F_INPUT_NAME) LIKE '%' || :search_term || '%' ESCAPE '!'
            ORDER BY F_INPUT_NAME
        )
        WHERE ROWNUM <= :row_limit
        """;

    private readonly IDataQueryExecutor _executor;

    public LegacyOverseasStockSearchService(IDataQueryExecutor executor) =>
        _executor = executor ?? throw new ArgumentNullException(nameof(executor));

    public async Task<WorldStockSearchResult> SearchAsync(
        string query,
        int maximumResults = DefaultMaximumResults,
        CancellationToken cancellationToken = default)
    {
        var normalizedQuery = ValidateAndNormalizeQuery(query);
        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);
        // Read enough additional rows to keep malformed display-only entries
        // from consuming the operator-visible result budget. The query remains
        // finite and one extra valid row is retained for truncation detection.
        // An initial-only query keeps the ordinary non-empty all-nation scope;
        // its empty bind reads the full finite candidate window for local match.
        var candidateLimit = isInitialSearch ? MaximumResults : maximumResults;
        var rowLimit = checked(candidateLimit + MaximumIsolatedRows + 1);
        var spec = CreateQuerySpec(normalizedQuery, rowLimit, isInitialSearch);
        var table = await _executor.ExecuteAsync(
            DataSourceKind.Oracle,
            QueryName,
            spec,
            cancellationToken).ConfigureAwait(false);
        cancellationToken.ThrowIfCancellationRequested();

        var validation = ValidateRows(table, rowLimit);
        var matches = isInitialSearch
            ? validation.Items.Where(item => HangulSearchMatcher.IsMatch(
                item.InputName,
                normalizedQuery)).ToArray()
            : validation.Items;
        return new WorldStockSearchResult(
            normalizedQuery,
            DateTimeOffset.Now,
            matches.Take(maximumResults).ToArray(),
            matches.Count > maximumResults ||
                (isInitialSearch && validation.Items.Count > MaximumResults) ||
                table.Rows.Count == rowLimit ||
                validation.IsolatedInvalidRowCount > 0,
            validation.IsolatedInvalidRowCount);
    }

    private static DataQuerySpec CreateQuerySpec(
        string query,
        int rowLimit,
        bool isInitialSearch)
    {
        var parameters = new List<DataQueryParameter>
        {
            new("row_limit", rowLimit, DbType.Int32)
        };
        var sql = InitialListSql;
        if (query.Length != 0)
        {
            sql = SearchSql;
            parameters.Insert(0, new DataQueryParameter(
                "search_term",
                isInitialSearch
                    ? string.Empty
                    : EscapeLikePattern(query.ToUpperInvariant()),
                DbType.String));
        }

        var spec = new DataQuerySpec(sql, parameters);
        spec.ValidateFor(DataSourceKind.Oracle);
        return spec;
    }

    private static RowValidationResult ValidateRows(DataTable? table, int rowLimit)
    {
        if (table is null || table.Columns.Count != 3 || table.Rows.Count > rowLimit ||
            !HasExactStringColumn(table.Columns[0], "F_INPUT_NAME") ||
            !HasExactStringColumn(table.Columns[1], "F_SYMB") ||
            !HasExactStringColumn(table.Columns[2], "F_NATC"))
        {
            throw InvalidData("schema or row bound");
        }

        var identities = new HashSet<WorldStockSearchItem>();
        var matches = new List<WorldStockSearchItem>(table.Rows.Count);
        var isolatedInvalidRowCount = 0;
        foreach (DataRow row in table.Rows)
        {
            // Sixteen rows in the current US/TW master have no display name.
            // The original grid did not let those rows prevent every valid row
            // from loading. Preserve that behavior without making an unnamed
            // identity selectable or serializable.
            if (row[0] is DBNull ||
                row[0] is string blankName && blankName.Trim(' ').Length == 0)
            {
                isolatedInvalidRowCount++;
                continue;
            }

            var inputName = ReadCanonical(row, 0, MaximumInputNameLength);
            var symbol = ReadCanonical(row, 1, MaximumSymbolLength);
            var nationCode = ReadCanonical(row, 2, MaximumNationCodeLength);
            if (nationCode.Length is not (2 or 3) ||
                nationCode.Any(character => character is < 'A' or > 'Z'))
            {
                throw InvalidData("nation code");
            }

            var identity = new WorldStockSearchItem(inputName, symbol, nationCode);
            if (!identities.Add(identity))
            {
                throw InvalidData("duplicate exact identity");
            }

            matches.Add(identity);
        }

        return new RowValidationResult(matches, isolatedInvalidRowCount);
    }

    private static string ValidateAndNormalizeQuery(string query)
    {
        ArgumentNullException.ThrowIfNull(query);
        var normalized = query.Trim();
        if (!IsSafeText(query) || !IsSafeText(normalized) ||
            normalized.Length > MaximumQueryLength)
        {
            throw new ArgumentException(
                $"An overseas-stock search query must contain at most {MaximumQueryLength} visible characters.",
                nameof(query));
        }

        return normalized;
    }

    private static string ReadCanonical(DataRow row, int ordinal, int maximumLength)
    {
        if (row[ordinal] is not string rawValue || rawValue.Length < 1 ||
            rawValue.Length > maximumLength || !IsSafeText(rawValue))
        {
            throw InvalidData("value");
        }

        // The current legacy master contains one US/TW row whose name and symbol
        // are padded with an outer ASCII space. The original UC5 accepted those
        // values. Normalize only that closed padding form; the identity set below
        // still rejects any collision created by normalization.
        var value = rawValue.Trim(' ');
        if (value.Length < 1 || value.Length > maximumLength)
        {
            throw InvalidData("value");
        }

        return value;
    }

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

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

    private static bool IsSafeText(string value)
    {
        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 WorldStockSearchDataException InvalidData(string detail) =>
        new($"Overseas stock data from {QueryName} contains an invalid {detail}.");

    private sealed record RowValidationResult(
        IReadOnlyList<WorldStockSearchItem> Items,
        int IsolatedInvalidRowCount);
}
