#nullable enable

using System.Data;
using System.Globalization;

namespace MMoneyCoderSharp.Data;

/// <summary>
/// Exact US foreign-industry/index identity from T_WORLD_IX_EQ_MASTER.
/// The complete tuple is the identity. Individual display or downstream
/// lookup fields are not required to be globally unique.
/// </summary>
public sealed record OverseasIndustryIndexItem(
    string KoreanName,
    string InputName,
    string Symbol);

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

public interface IOverseasIndustryIndexSearchService
{
    Task<OverseasIndustryIndexSearchResult> SearchAsync(
        string query,
        int maximumResults = LegacyOverseasIndustryIndexSearchService.DefaultMaximumResults,
        CancellationToken cancellationToken = default);
}

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

/// <summary>
/// Parameterized, read-only migration of UC5.upjong_search. Blank input keeps
/// the original initial-list behavior while all non-blank LIKE metacharacters
/// remain data through an escaped Oracle bind value.
/// </summary>
public sealed class LegacyOverseasIndustryIndexSearchService
    : IOverseasIndustryIndexSearchService
{
    public const int DefaultMaximumResults = 100;
    public const int MaximumResults = 2_000;
    public const int MaximumQueryLength = 64;

    private const int MaximumKoreanNameLength = 200;
    private const int MaximumInputNameLength = 200;
    private const int MaximumSymbolLength = 64;
    private const string QueryName = "OVERSEAS_INDUSTRY_INDEX_SEARCH";

    private static readonly string[] ResultColumns =
        ["F_KNAM", "F_INPUT_NAME", "F_SYMB"];

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

    private readonly IDataQueryExecutor _executor;

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

    public async Task<OverseasIndustryIndexSearchResult> 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);
        // Oracle LIKE cannot compare compatibility-jamo initials with composed
        // Hangul names. Keep the same US index search scope, but read its finite
        // service-wide candidate window and filter only after strict validation.
        var rowLimit = isInitialSearch
            ? checked(MaximumResults + 1)
            : checked(maximumResults + 1);
        var spec = CreateQuerySpec(
            isInitialSearch
                ? string.Empty
                : EscapeLikePattern(normalizedQuery.ToUpperInvariant()),
            rowLimit);
        var table = await _executor.ExecuteAsync(
            DataSourceKind.Oracle,
            QueryName,
            spec,
            cancellationToken).ConfigureAwait(false);
        cancellationToken.ThrowIfCancellationRequested();

        var matches = ValidateRows(table, rowLimit);
        var filtered = isInitialSearch
            ? matches.Where(item => HangulSearchMatcher.IsMatch(
                item.KoreanName,
                normalizedQuery)).ToArray()
            : matches;
        return new OverseasIndustryIndexSearchResult(
            normalizedQuery,
            DateTimeOffset.Now,
            filtered.Take(maximumResults).ToArray(),
            filtered.Count > maximumResults ||
                (isInitialSearch && matches.Count > MaximumResults));
    }

    private static DataQuerySpec CreateQuerySpec(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(DataSourceKind.Oracle);
        return spec;
    }

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

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

        return normalized;
    }

    private static IReadOnlyList<OverseasIndustryIndexItem> ValidateRows(
        DataTable? table,
        int rowLimit)
    {
        if (table is null ||
            table.Columns.Count != ResultColumns.Length ||
            table.Rows.Count > rowLimit)
        {
            throw InvalidData("schema or row bound");
        }

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

        var identities = new HashSet<OverseasIndustryIndexItem>();
        var results = new List<OverseasIndustryIndexItem>(table.Rows.Count);
        foreach (DataRow row in table.Rows)
        {
            var koreanName = ReadCanonicalValue(row, 0, MaximumKoreanNameLength);
            var inputName = ReadCanonicalValue(row, 1, MaximumInputNameLength);
            var symbol = ReadCanonicalValue(row, 2, MaximumSymbolLength);

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

            results.Add(identity);
        }

        return results;
    }

    private static string ReadCanonicalValue(DataRow row, int ordinal, int maximumLength)
    {
        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("value");
        }

        return value;
    }

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

    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 OverseasIndustryIndexSearchDataException InvalidData(string detail) =>
        new($"Overseas industry/index data from {QueryName} contains an invalid {detail}.");
}
