#nullable enable

using System.Data;
using System.Globalization;

namespace MMoneyCoderSharp.Data;

public sealed record WorldStockSearchItem(
    string InputName,
    string Symbol,
    string NationCode);

public sealed record WorldStockSearchResult(
    string Query,
    DateTimeOffset RetrievedAt,
    IReadOnlyList<WorldStockSearchItem> Items,
    bool IsTruncated,
    int IsolatedInvalidRowCount = 0);

public interface IWorldStockSearchService
{
    Task<WorldStockSearchResult> SearchAsync(
        string query,
        int maximumResults = LegacyWorldStockSearchService.DefaultMaximumResults,
        CancellationToken cancellationToken = default);
}

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

/// <summary>
/// Searches the Oracle world-equity master used by the legacy overseas-stock
/// comparison workflow. Input is bound and LIKE metacharacters are escaped;
/// database output is accepted only when it exactly matches the required
/// three-column identity contract.
/// </summary>
public sealed class LegacyWorldStockSearchService : IWorldStockSearchService
{
    public const int DefaultMaximumResults = 100;
    public const int MaximumResults = 5_000;
    public const int MaximumIsolatedRows = 128;
    public const int MaximumQueryLength = 64;

    private const int MaximumInputNameLength = 200;
    private const int MaximumSymbolLength = 64;
    private const string InputNameColumn = "F_INPUT_NAME";
    private const string SymbolColumn = "F_SYMB";
    private const string NationCodeColumn = "F_NATC";
    private const string QueryName = "WORLD_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 UPPER(F_INPUT_NAME), F_SYMB, F_NATC
        )
        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 F_NATC IN ('US', 'TW')
              AND UPPER(F_INPUT_NAME) LIKE '%' || :search_term || '%' ESCAPE '!'
            ORDER BY UPPER(F_INPUT_NAME), F_SYMB, F_NATC
        )
        WHERE ROWNUM <= :row_limit
        """;

    private readonly IDataQueryExecutor _executor;

    public LegacyWorldStockSearchService(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);
        // Keep blank legacy display rows from consuming the visible result budget while
        // retaining a finite provider bound and one extra valid row for truncation proof.
        // Initial-only input uses the ordinary non-empty US/TW search scope with
        // an empty bind, then applies the shared matcher to validated identities.
        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 string ValidateAndNormalizeQuery(string query)
    {
        ArgumentNullException.ThrowIfNull(query);

        var normalized = query.Trim();
        if (!IsSafeText(query) ||
            normalized.Length > MaximumQueryLength ||
            !IsSafeText(normalized))
        {
            throw new ArgumentException(
                $"A world-stock search query must contain at most {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 RowValidationResult ValidateRows(
        DataTable? table,
        int rowLimit)
    {
        if (table is null ||
            table.Columns.Count != 3 ||
            !HasExactStringColumn(table.Columns[0], InputNameColumn) ||
            !HasExactStringColumn(table.Columns[1], SymbolColumn) ||
            !HasExactStringColumn(table.Columns[2], NationCodeColumn) ||
            table.Rows.Count > rowLimit)
        {
            throw InvalidSchema();
        }

        var identities = new HashSet<WorldStockSearchItem>();
        var matches = new List<WorldStockSearchItem>(table.Rows.Count);
        var isolatedInvalidRowCount = 0;
        foreach (DataRow row in table.Rows)
        {
            if (row[0] is DBNull ||
                row[0] is string blankName && blankName.Trim(' ').Length == 0)
            {
                isolatedInvalidRowCount++;
                continue;
            }

            if (row[0] is not string rawInputName ||
                row[1] is not string rawSymbol ||
                row[2] is not string rawNationCode)
            {
                throw InvalidSchema();
            }

            var inputName = rawInputName.Trim(' ');
            var symbol = rawSymbol.Trim(' ');
            var nationCode = rawNationCode.Trim(' ');
            if (!IsCanonicalDatabaseValue(inputName, MaximumInputNameLength) ||
                !IsCanonicalDatabaseValue(symbol, MaximumSymbolLength) ||
                nationCode is not ("US" or "TW"))
            {
                throw new WorldStockSearchDataException(
                    $"World-stock search data from {QueryName} contains an invalid value.");
            }

            var item = new WorldStockSearchItem(inputName, symbol, nationCode);
            if (!identities.Add(item))
            {
                throw new WorldStockSearchDataException(
                    $"World-stock search data from {QueryName} contains a duplicate identity.");
            }

            matches.Add(item);
        }

        return new RowValidationResult(matches, isolatedInvalidRowCount);
    }

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

    private static bool IsCanonicalDatabaseValue(string value, int maximumLength) =>
        value.Length is > 0 &&
        value.Length <= maximumLength &&
        string.Equals(value, value.Trim(), StringComparison.Ordinal) &&
        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 WorldStockSearchDataException InvalidSchema() =>
        new($"World-stock search data from {QueryName} does not match the required schema.");

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