#nullable enable

using System.Data;
using System.Globalization;

namespace MMoneyCoderSharp.Data;

public enum TradingHaltMarket
{
    Kospi,
    Kosdaq
}

public sealed record TradingHaltSelection(
    TradingHaltMarket Market,
    string StockCode,
    string DisplayName);

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

public interface ITradingHaltSelectionService
{
    Task<TradingHaltSelectionResult> SearchAsync(
        string query = "",
        int maximumResults = LegacyTradingHaltSelectionService.DefaultMaximumResults,
        CancellationToken cancellationToken = default);
}

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

/// <summary>
/// Reads the KOSPI and KOSDAQ trading-halt selectors used by legacy UC7.
/// The empty query returns the bounded selector contents; a non-empty query
/// applies the legacy name filter through a bound parameter. Database rows
/// must satisfy the exact market/code/name identity contract before they are
/// exposed to an operator surface.
/// </summary>
public sealed class LegacyTradingHaltSelectionService : ITradingHaltSelectionService
{
    public const int DefaultMaximumResults = 100;
    public const int MaximumResults = 500;
    public const int MaximumQueryLength = 64;

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

    private readonly IDataQueryExecutor _executor;

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

    public async Task<TradingHaltSelectionResult> 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);
        var rowLimit = checked(
            (isInitialSearch ? MaximumResults : maximumResults) + 1);
        var escapedQuery = isInitialSearch
            ? string.Empty
            : EscapeLikePattern(normalizedQuery.ToUpperInvariant());
        var matches = new List<TradingHaltSelection>(rowLimit * TradingHaltQueries.All.Count);
        var identities = new HashSet<(TradingHaltMarket Market, string StockCode)>();
        var lookupKeys = new HashSet<(TradingHaltMarket Market, string DisplayName)>();
        var candidateSetIsTruncated = false;

        // UC7 appends KOSPI followed by KOSDAQ. Keep that provider sequence,
        // then apply a deterministic final order independent of provider row order.
        foreach (var profile in TradingHaltQueries.All)
        {
            cancellationToken.ThrowIfCancellationRequested();
            var spec = profile.CreateSpec(escapedQuery, rowLimit);
            var table = await _executor.ExecuteAsync(
                DataSourceKind.Oracle,
                profile.QueryName,
                spec,
                cancellationToken).ConfigureAwait(false);
            cancellationToken.ThrowIfCancellationRequested();

            AppendValidatedRows(
                table,
                profile,
                rowLimit,
                matches,
                identities,
                lookupKeys);
            candidateSetIsTruncated |=
                isInitialSearch && table.Rows.Count == rowLimit;
        }

        IEnumerable<TradingHaltSelection> filteredMatches = isInitialSearch
            ? matches.Where(item => HangulSearchMatcher.IsMatch(
                item.DisplayName,
                normalizedQuery))
            : matches;
        var filtered = filteredMatches.ToArray();
        var ordered = filtered
            .OrderBy(static item => item.Market)
            .ThenBy(static item => item.DisplayName, StringComparer.Ordinal)
            .ThenBy(static item => item.StockCode, StringComparer.Ordinal)
            .Take(maximumResults)
            .ToArray();

        return new TradingHaltSelectionResult(
            normalizedQuery,
            DateTimeOffset.Now,
            ordered,
            filtered.Length > maximumResults ||
                candidateSetIsTruncated);
    }

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

        var normalized = query.Trim();
        if (query.Length > MaximumQueryLength ||
            normalized.Length > MaximumQueryLength ||
            !IsSafeText(query) ||
            !IsSafeText(normalized))
        {
            throw new ArgumentException(
                $"A trading-halt search query must contain at most {MaximumQueryLength} safe 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,
        TradingHaltQueryProfile profile,
        int rowLimit,
        ICollection<TradingHaltSelection> destination,
        ISet<(TradingHaltMarket Market, string StockCode)> identities,
        ISet<(TradingHaltMarket Market, string DisplayName)> lookupKeys)
    {
        if (table is null ||
            table.Columns.Count != 2 ||
            !HasExactStringColumn(table.Columns[0], StockCodeColumn) ||
            !HasExactStringColumn(table.Columns[1], StockNameColumn) ||
            table.Rows.Count > rowLimit)
        {
            throw InvalidSchema(profile.QueryName);
        }

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

            if (!IsCanonicalDatabaseValue(stockCode, MaximumStockCodeLength) ||
                !IsCanonicalDatabaseValue(displayName, MaximumDisplayNameLength))
            {
                throw new TradingHaltSelectionDataException(
                    $"Trading-halt selector data from {profile.QueryName} contains an invalid value.");
            }

            if (!identities.Add((profile.Market, stockCode)))
            {
                throw new TradingHaltSelectionDataException(
                    $"Trading-halt selector data from {profile.QueryName} contains a duplicate market/code identity.");
            }

            // Legacy downstream requests resolve a domestic stock by market and
            // display name. Distinct codes with the same key would be ambiguous.
            if (!lookupKeys.Add((profile.Market, displayName)))
            {
                throw new TradingHaltSelectionDataException(
                    $"Trading-halt selector data from {profile.QueryName} contains an ambiguous market/name lookup key.");
            }

            destination.Add(new TradingHaltSelection(profile.Market, stockCode, displayName));
        }
    }

    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 TradingHaltSelectionDataException InvalidSchema(string queryName) =>
        new($"Trading-halt selector data from {queryName} does not match the required schema or row bound.");
}

internal sealed record TradingHaltQueryProfile(
    TradingHaltMarket Market,
    string QueryName,
    string AllSql,
    string FilteredSql)
{
    internal DataQuerySpec CreateSpec(string escapedQuery, int rowLimit)
    {
        DataQuerySpec spec;
        if (escapedQuery.Length == 0)
        {
            spec = new DataQuerySpec(
                AllSql,
                [new DataQueryParameter("row_limit", rowLimit, DbType.Int32)]);
        }
        else
        {
            spec = new DataQuerySpec(
                FilteredSql,
                [
                    new DataQueryParameter("search_term", escapedQuery, DbType.String),
                    new DataQueryParameter("row_limit", rowLimit, DbType.Int32)
                ]);
        }

        spec.ValidateFor(DataSourceKind.Oracle);
        return spec;
    }
}

internal static class TradingHaltQueries
{
    private const string KospiAllSql = """
        SELECT STOCK_CODE, STOCK_NAME
        FROM (
            SELECT b.F_STOCK_CODE AS STOCK_CODE, b.F_STOCK_WANNAME AS STOCK_NAME
            FROM T_STOP_ONLINE1 a
            INNER JOIN T_STOCK b ON a.F_STOCK_CODE = b.F_STOCK_CODE
            WHERE b.F_MKT_HALT = 'Y'
            ORDER BY UPPER(b.F_STOCK_WANNAME), b.F_STOCK_CODE
        )
        WHERE ROWNUM <= :row_limit
        """;

    private const string KospiFilteredSql = """
        SELECT STOCK_CODE, STOCK_NAME
        FROM (
            SELECT b.F_STOCK_CODE AS STOCK_CODE, b.F_STOCK_WANNAME AS STOCK_NAME
            FROM T_STOP_ONLINE1 a
            INNER JOIN T_STOCK b ON a.F_STOCK_CODE = b.F_STOCK_CODE
            WHERE b.F_MKT_HALT = 'Y'
              AND UPPER(b.F_STOCK_WANNAME) LIKE '%' || :search_term || '%' ESCAPE '!'
            ORDER BY UPPER(b.F_STOCK_WANNAME), b.F_STOCK_CODE
        )
        WHERE ROWNUM <= :row_limit
        """;

    private const string KosdaqAllSql = """
        SELECT STOCK_CODE, STOCK_NAME
        FROM (
            SELECT b.F_STOCK_CODE AS STOCK_CODE, b.F_STOCK_WANNAME AS STOCK_NAME
            FROM T_STOP_KOSDAQ_ONLINE1 a
            INNER JOIN T_KOSDAQ_STOCK b ON a.F_STOCK_CODE = b.F_STOCK_CODE
            WHERE b.F_MKT_HALT = 'Y'
            ORDER BY UPPER(b.F_STOCK_WANNAME), b.F_STOCK_CODE
        )
        WHERE ROWNUM <= :row_limit
        """;

    private const string KosdaqFilteredSql = """
        SELECT STOCK_CODE, STOCK_NAME
        FROM (
            SELECT b.F_STOCK_CODE AS STOCK_CODE, b.F_STOCK_WANNAME AS STOCK_NAME
            FROM T_STOP_KOSDAQ_ONLINE1 a
            INNER JOIN T_KOSDAQ_STOCK b ON a.F_STOCK_CODE = b.F_STOCK_CODE
            WHERE b.F_MKT_HALT = 'Y'
              AND UPPER(b.F_STOCK_WANNAME) LIKE '%' || :search_term || '%' ESCAPE '!'
            ORDER BY UPPER(b.F_STOCK_WANNAME), b.F_STOCK_CODE
        )
        WHERE ROWNUM <= :row_limit
        """;

    internal static IReadOnlyList<TradingHaltQueryProfile> All { get; } =
    [
        new(
            TradingHaltMarket.Kospi,
            "TRADING_HALT_KOSPI",
            KospiAllSql,
            KospiFilteredSql),
        new(
            TradingHaltMarket.Kosdaq,
            "TRADING_HALT_KOSDAQ",
            KosdaqAllSql,
            KosdaqFilteredSql)
    ];
}
