#nullable enable

using System.Data;
using System.Globalization;
using System.Text.RegularExpressions;

namespace MMoneyCoderSharp.Data;

public sealed record ExpertSelectionIdentity(
    string ExpertCode,
    string ExpertName);

public sealed record ExpertSelectorItem(
    ExpertSelectionIdentity Identity,
    DataSourceKind Source);

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

public sealed record ExpertRecommendationPreview(
    int PlayIndex,
    string StockCode,
    string StockName,
    decimal? BuyAmount);

public sealed record ExpertPreviewResult(
    ExpertSelectionIdentity Identity,
    DateTimeOffset RetrievedAt,
    IReadOnlyList<ExpertRecommendationPreview> Items,
    bool IsTruncated);

public interface IExpertSelectionService
{
    Task<ExpertSearchResult> SearchAsync(
        string query = "",
        int maximumResults = LegacyExpertSelectionService.DefaultMaximumResults,
        CancellationToken cancellationToken = default);

    Task<ExpertPreviewResult> PreviewAsync(
        ExpertSelectionIdentity identity,
        int maximumItems = LegacyExpertSelectionService.DefaultMaximumPreviewItems,
        CancellationToken cancellationToken = default);
}

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

/// <summary>
/// Read-only migration of UC6's Oracle expert list and recommendation preview.
/// Both expert code and name remain bound through preview and playout selection;
/// no CRUD statement or free-form table selection is exposed.
/// </summary>
public sealed partial class LegacyExpertSelectionService : IExpertSelectionService
{
    public const int DefaultMaximumResults = 100;
    public const int MaximumResults = 500;
    public const int MaximumQueryLength = 64;
    public const int DefaultMaximumPreviewItems = 100;
    public const int MaximumPreviewItems = 100;

    private const int MaximumExpertNameLength = 128;
    private const int MaximumStockNameLength = 200;
    private const int MaximumPlayIndex = 9_999;
    private const decimal MaximumWebSafeInteger = 9_007_199_254_740_991m;
    private const string SearchQueryName = "EXPERT_SEARCH";
    private const string PreviewQueryName = "EXPERT_PREVIEW";

    private static readonly string[] SearchColumns =
        ["EXPERT_NAME", "EXPERT_CODE"];

    private static readonly string[] PreviewColumns =
    [
        "EXPERT_NAME",
        "EXPERT_CODE",
        "STOCK_CODE",
        "STOCK_NAME",
        "BUY_AMOUNT",
        "PLAY_INDEX"
    ];

    private const string SearchSql = """
        SELECT EXPERT_NAME, EXPERT_CODE
        FROM (
            SELECT EP_NAME EXPERT_NAME,
                   EP_CODE EXPERT_CODE
            FROM EXPERT_LIST
            WHERE UPPER(EP_NAME) LIKE '%' || :search_term || '%' ESCAPE '!'
            ORDER BY UPPER(EP_NAME), EP_CODE
        )
        WHERE ROWNUM <= :row_limit
        """;

    private const string PreviewSql = """
        SELECT EXPERT_NAME, EXPERT_CODE,
               STOCK_CODE, STOCK_NAME, BUY_AMOUNT, PLAY_INDEX
        FROM (
            SELECT e.EP_NAME EXPERT_NAME,
                   e.EP_CODE EXPERT_CODE,
                   r.RC_STOCK_CODE STOCK_CODE,
                   r.RC_STOCK_NAME STOCK_NAME,
                   TO_CHAR(
                       r.RC_BUY_AMOUNT,
                       'TM9',
                       'NLS_NUMERIC_CHARACTERS=''.,''') BUY_AMOUNT,
                   TO_CHAR(r.PLAYINDEX, 'FM999999990') PLAY_INDEX
            FROM EXPERT_LIST e
            LEFT JOIN RECOMMEND_LIST r ON e.EP_CODE = r.RC_CODE
            WHERE e.EP_CODE = :expert_code
              AND e.EP_NAME = :expert_name
            ORDER BY r.PLAYINDEX
        )
        WHERE ROWNUM <= :row_limit
        """;

    private readonly IDataQueryExecutor _executor;

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

    public async Task<ExpertSearchResult> SearchAsync(
        string query = "",
        int maximumResults = DefaultMaximumResults,
        CancellationToken cancellationToken = default)
    {
        var normalizedQuery = ValidateAndNormalizeQuery(query);
        ValidateLimit(maximumResults, MaximumResults, nameof(maximumResults));
        cancellationToken.ThrowIfCancellationRequested();

        var isInitialSearch = HangulSearchMatcher.IsInitialConsonantQuery(normalizedQuery);
        // Oracle cannot compare compatibility-jamo initials with composed Hangul
        // syllables through LIKE. Expert lists are bounded to the service-wide
        // maximum, so an initial-only query reads that closed candidate window
        // and applies the shared matcher after strict row/schema validation.
        var rowLimit = isInitialSearch
            ? checked(MaximumResults + 1)
            : checked(maximumResults + 1);
        var spec = CreateSearchSpec(
            isInitialSearch
                ? string.Empty
                : EscapeLikePattern(normalizedQuery.ToUpperInvariant()),
            rowLimit);
        var table = await _executor.ExecuteAsync(
            DataSourceKind.Oracle,
            SearchQueryName,
            spec,
            cancellationToken).ConfigureAwait(false);
        cancellationToken.ThrowIfCancellationRequested();

        var matches = MapSearchRows(table, rowLimit);
        var filtered = isInitialSearch
            ? matches.Where(item => HangulSearchMatcher.IsMatch(
                item.Identity.ExpertName,
                normalizedQuery)).ToArray()
            : matches;
        var ordered = filtered
            .OrderBy(static item => item.Identity.ExpertName, StringComparer.Ordinal)
            .ThenBy(static item => item.Identity.ExpertCode, StringComparer.Ordinal)
            .Take(maximumResults)
            .ToArray();
        return new ExpertSearchResult(
            normalizedQuery,
            DateTimeOffset.Now,
            ordered,
            filtered.Count > maximumResults ||
                (isInitialSearch && matches.Count > MaximumResults));
    }

    public async Task<ExpertPreviewResult> PreviewAsync(
        ExpertSelectionIdentity identity,
        int maximumItems = DefaultMaximumPreviewItems,
        CancellationToken cancellationToken = default)
    {
        var canonicalIdentity = ValidateIdentity(identity);
        ValidateLimit(maximumItems, MaximumPreviewItems, nameof(maximumItems));
        cancellationToken.ThrowIfCancellationRequested();

        var rowLimit = checked(maximumItems + 1);
        var table = await _executor.ExecuteAsync(
            DataSourceKind.Oracle,
            PreviewQueryName,
            CreatePreviewSpec(canonicalIdentity, rowLimit),
            cancellationToken).ConfigureAwait(false);
        cancellationToken.ThrowIfCancellationRequested();

        var items = MapPreviewRows(table, canonicalIdentity, rowLimit);
        return new ExpertPreviewResult(
            canonicalIdentity,
            DateTimeOffset.Now,
            items.Take(maximumItems).ToArray(),
            items.Count > maximumItems);
    }

    private static IReadOnlyList<ExpertSelectorItem> MapSearchRows(
        DataTable? table,
        int rowLimit)
    {
        ValidateExactStringSchema(table, SearchColumns, rowLimit, SearchQueryName);

        var codes = new HashSet<string>(StringComparer.Ordinal);
        var results = new List<ExpertSelectorItem>(table!.Rows.Count);
        foreach (DataRow row in table.Rows)
        {
            var name = ReadCanonicalString(
                row,
                0,
                MaximumExpertNameLength,
                SearchQueryName);
            var code = ReadExpertCode(row, 1, SearchQueryName);

            if (!codes.Add(code))
            {
                throw InvalidData(SearchQueryName, "duplicate expert code");
            }

            results.Add(new ExpertSelectorItem(
                new ExpertSelectionIdentity(code, name),
                DataSourceKind.Oracle));
        }

        return results;
    }

    private static IReadOnlyList<ExpertRecommendationPreview> MapPreviewRows(
        DataTable? table,
        ExpertSelectionIdentity identity,
        int rowLimit)
    {
        ValidateExactStringSchema(table, PreviewColumns, rowLimit, PreviewQueryName);
        if (table!.Rows.Count < 1)
        {
            throw InvalidData(PreviewQueryName, "missing selected expert");
        }

        var playIndexes = new HashSet<int>();
        var items = new List<ExpertRecommendationPreview>(table.Rows.Count);
        var sawEmptyExpert = false;

        foreach (DataRow row in table.Rows)
        {
            var expertName = ReadCanonicalString(
                row,
                0,
                MaximumExpertNameLength,
                PreviewQueryName);
            var expertCode = ReadExpertCode(row, 1, PreviewQueryName);
            if (!string.Equals(expertName, identity.ExpertName, StringComparison.Ordinal) ||
                !string.Equals(expertCode, identity.ExpertCode, StringComparison.Ordinal))
            {
                throw InvalidData(PreviewQueryName, "selected expert identity");
            }

            var stockCodeValue = row[2];
            var stockNameValue = row[3];
            var buyAmountValue = row[4];
            var playIndexValue = row[5];
            var isEmptyExpert = stockCodeValue is DBNull &&
                stockNameValue is DBNull &&
                buyAmountValue is DBNull &&
                playIndexValue is DBNull;
            if (isEmptyExpert)
            {
                if (table.Rows.Count != 1)
                {
                    throw InvalidData(PreviewQueryName, "empty recommendation sentinel");
                }

                sawEmptyExpert = true;
                continue;
            }

            if (stockCodeValue is DBNull ||
                stockNameValue is DBNull ||
                playIndexValue is DBNull)
            {
                throw InvalidData(PreviewQueryName, "partial recommendation identity");
            }

            var stockCode = ReadCanonicalString(row, 2, 32, PreviewQueryName);
            if (!StockCodePattern().IsMatch(stockCode))
            {
                throw InvalidData(PreviewQueryName, "stock code");
            }

            var stockName = ReadCanonicalString(
                row,
                3,
                MaximumStockNameLength,
                PreviewQueryName);
            // The legacy EList grid displayed a database NULL buy amount as an
            // empty editable cell. Keep that incomplete value in the read model;
            // save and playout boundaries remain responsible for requiring a
            // positive integer amount.
            var buyAmount = buyAmountValue is DBNull
                ? (decimal?)null
                : ReadPositiveDecimal(row, 4, PreviewQueryName);
            var playIndex = ReadPlayIndex(row, 5, PreviewQueryName);
            if (!playIndexes.Add(playIndex))
            {
                throw InvalidData(PreviewQueryName, "duplicate play index");
            }

            items.Add(new ExpertRecommendationPreview(
                playIndex,
                stockCode,
                stockName,
                buyAmount));
        }

        if (sawEmptyExpert)
        {
            return Array.Empty<ExpertRecommendationPreview>();
        }

        for (var index = 1; index < items.Count; index++)
        {
            if (items[index - 1].PlayIndex >= items[index].PlayIndex)
            {
                throw InvalidData(PreviewQueryName, "recommendation order");
            }
        }

        return items;
    }

    private static DataQuerySpec CreateSearchSpec(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 DataQuerySpec CreatePreviewSpec(
        ExpertSelectionIdentity identity,
        int rowLimit)
    {
        var spec = new DataQuerySpec(
            PreviewSql,
            [
                new DataQueryParameter("expert_code", identity.ExpertCode, DbType.String),
                new DataQueryParameter("expert_name", identity.ExpertName, DbType.String),
                new DataQueryParameter("row_limit", rowLimit, DbType.Int32)
            ]);
        spec.ValidateFor(DataSourceKind.Oracle);
        return spec;
    }

    private static ExpertSelectionIdentity ValidateIdentity(ExpertSelectionIdentity identity)
    {
        ArgumentNullException.ThrowIfNull(identity);
        var code = ValidateCanonicalInput(identity.ExpertCode, 4, nameof(identity));
        var name = ValidateCanonicalInput(
            identity.ExpertName,
            MaximumExpertNameLength,
            nameof(identity));
        if (!ExpertCodePattern().IsMatch(code))
        {
            throw new ArgumentException("The expert selection code is invalid.", nameof(identity));
        }

        return identity with { ExpertCode = code, ExpertName = name };
    }

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

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

        return normalized;
    }

    private static string ValidateCanonicalInput(
        string value,
        int maximumLength,
        string parameterName)
    {
        ArgumentNullException.ThrowIfNull(value);
        if (value.Length is < 1 || value.Length > maximumLength ||
            !string.Equals(value, value.Trim(), StringComparison.Ordinal) ||
            !IsSafeText(value))
        {
            throw new ArgumentException("The expert selection value is invalid.", parameterName);
        }

        return value;
    }

    private static void ValidateLimit(int value, int maximum, string parameterName)
    {
        if (value is < 1 || value > maximum)
        {
            throw new ArgumentOutOfRangeException(
                parameterName,
                value,
                $"The requested limit must be between 1 and {maximum}.");
        }
    }

    private static void ValidateExactStringSchema(
        DataTable? table,
        IReadOnlyList<string> columns,
        int rowLimit,
        string queryName)
    {
        if (table is null || table.Columns.Count != columns.Count || table.Rows.Count > rowLimit)
        {
            throw InvalidData(queryName, "schema or row bound");
        }

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

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

        return value;
    }

    private static string ReadExpertCode(DataRow row, int ordinal, string queryName)
    {
        var code = ReadCanonicalString(row, ordinal, 4, queryName);
        if (!ExpertCodePattern().IsMatch(code))
        {
            throw InvalidData(queryName, "expert code");
        }

        return code;
    }

    private static decimal ReadPositiveDecimal(DataRow row, int ordinal, string queryName)
    {
        var raw = ReadCanonicalString(row, ordinal, 64, queryName);
        if (!decimal.TryParse(
                raw,
                NumberStyles.AllowLeadingSign | NumberStyles.AllowDecimalPoint,
                CultureInfo.InvariantCulture,
                out var value) ||
            value <= 0 ||
            value > MaximumWebSafeInteger ||
            decimal.Truncate(value) != value ||
            !string.Equals(
                raw,
                value.ToString(CultureInfo.InvariantCulture),
                StringComparison.Ordinal))
        {
            throw InvalidData(queryName, "buy amount");
        }

        return value;
    }

    private static int ReadPlayIndex(DataRow row, int ordinal, string queryName)
    {
        var raw = ReadCanonicalString(row, ordinal, 4, queryName);
        if (!int.TryParse(
                raw,
                NumberStyles.None,
                CultureInfo.InvariantCulture,
                out var value) ||
            value is < 0 or > MaximumPlayIndex ||
            !string.Equals(
                raw,
                value.ToString(CultureInfo.InvariantCulture),
                StringComparison.Ordinal))
        {
            throw InvalidData(queryName, "play index");
        }

        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 ExpertSelectionDataException InvalidData(string queryName, string detail) =>
        new($"Expert selection data from {queryName} contains an invalid {detail}.");

    [GeneratedRegex("^[0-9]{4}$", RegexOptions.CultureInvariant)]
    private static partial Regex ExpertCodePattern();

    [GeneratedRegex("^[A-Za-z0-9]{1,32}$", RegexOptions.CultureInvariant)]
    private static partial Regex StockCodePattern();
}
