#nullable enable

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

namespace MMoneyCoderSharp.Data;

public sealed record StockMasterIdentity(
    StockMarket Market,
    DataSourceKind Source,
    string Name,
    string Code);

public interface IStockMasterIdentityValidationService
{
    Task<IReadOnlyList<StockMasterIdentity>> ValidateThemeItemsAsync(
        IReadOnlyList<ThemeCatalogItem> items,
        CancellationToken cancellationToken = default);

    Task<IReadOnlyList<StockMasterIdentity>> ValidateExpertRecommendationsAsync(
        IReadOnlyList<ExpertCatalogRecommendation> recommendations,
        CancellationToken cancellationToken = default);
}

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

/// <summary>
/// Resolves the exact code/name/market identity used by the legacy jongmokCode
/// lookup immediately before ThemeA or EList mutation dispatch. All values are
/// bound, result schemas are closed, and zero, duplicate, or cross-market
/// matches fail closed.
/// </summary>
public sealed class LegacyStockMasterIdentityValidationService
    : IStockMasterIdentityValidationService
{
    private const int MaximumStockNameLength = 200;
    private const int MaximumStockCodeLength = 32;
    private const string StockNameColumn = "STOCK_NAME";
    private const string StockCodeColumn = "STOCK_CODE";

    private readonly IDataQueryExecutor _executor;

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

    public Task<IReadOnlyList<StockMasterIdentity>> ValidateThemeItemsAsync(
        IReadOnlyList<ThemeCatalogItem> items,
        CancellationToken cancellationToken = default)
    {
        ArgumentNullException.ThrowIfNull(items);
        if (items.Count > LegacyThemeCatalogPersistenceService.MaximumItems)
        {
            throw InvalidIdentity();
        }

        var candidates = new StockMasterCandidate[items.Count];
        for (var index = 0; index < items.Count; index++)
        {
            var item = items[index] ?? throw InvalidIdentity();
            if (item.InputIndex != index ||
                !TryMapThemeCode(item.ItemCode, out var market, out var code) ||
                !IsSafeCanonicalText(item.ItemName, MaximumStockNameLength))
            {
                throw InvalidIdentity();
            }

            candidates[index] = new StockMasterCandidate(
                index,
                market,
                code,
                item.ItemName);
        }

        return ResolveAsync(candidates, useNxtDisplayName: false, cancellationToken);
    }

    public Task<IReadOnlyList<StockMasterIdentity>> ValidateExpertRecommendationsAsync(
        IReadOnlyList<ExpertCatalogRecommendation> recommendations,
        CancellationToken cancellationToken = default)
    {
        ArgumentNullException.ThrowIfNull(recommendations);
        if (recommendations.Count > LegacyExpertCatalogPersistenceService.MaximumRecommendations)
        {
            throw InvalidIdentity();
        }

        var candidates = new StockMasterCandidate[recommendations.Count];
        for (var index = 0; index < recommendations.Count; index++)
        {
            var recommendation = recommendations[index] ?? throw InvalidIdentity();
            if (recommendation.PlayIndex != index ||
                !IsAsciiAlphaNumeric(recommendation.StockCode, MaximumStockCodeLength) ||
                !IsSafeCanonicalText(recommendation.StockName, MaximumStockNameLength))
            {
                throw InvalidIdentity();
            }

            // EList stores no market column. Resolve the exact pair against all
            // four legacy masters and require exactly one typed identity.
            candidates[index] = new StockMasterCandidate(
                index,
                ExpectedMarket: null,
                recommendation.StockCode,
                recommendation.StockName);
        }

        return ResolveAsync(candidates, useNxtDisplayName: true, cancellationToken);
    }

    private async Task<IReadOnlyList<StockMasterIdentity>> ResolveAsync(
        IReadOnlyList<StockMasterCandidate> candidates,
        bool useNxtDisplayName,
        CancellationToken cancellationToken)
    {
        cancellationToken.ThrowIfCancellationRequested();
        if (candidates.Count == 0)
        {
            return Array.Empty<StockMasterIdentity>();
        }

        var matches = candidates.ToDictionary(
            static candidate => candidate.Index,
            static _ => new List<StockMasterIdentity>());

        foreach (var profile in StockMasterValidationProfiles.All)
        {
            cancellationToken.ThrowIfCancellationRequested();
            var applicable = candidates
                .Where(candidate => candidate.ExpectedMarket is null ||
                    candidate.ExpectedMarket == profile.Market)
                .ToArray();
            if (applicable.Length == 0)
            {
                continue;
            }

            var query = profile.CreateSpec(applicable
                .Select(static candidate =>
                    new StockMasterLookup(candidate.Code, candidate.Name))
                .ToArray(),
                useNxtDisplayName);
            var table = await _executor.ExecuteAsync(
                profile.Source,
                profile.QueryName,
                query,
                cancellationToken).ConfigureAwait(false);
            cancellationToken.ThrowIfCancellationRequested();
            AppendValidatedRows(table, profile, applicable, matches);
        }

        var resolved = new StockMasterIdentity[candidates.Count];
        foreach (var candidate in candidates)
        {
            var candidateMatches = matches[candidate.Index];
            if (candidateMatches.Count != 1)
            {
                throw InvalidIdentity();
            }

            resolved[candidate.Index] = candidateMatches[0];
        }

        return Array.AsReadOnly(resolved);
    }

    private static void AppendValidatedRows(
        DataTable? table,
        StockMasterValidationProfile profile,
        IReadOnlyList<StockMasterCandidate> candidates,
        IDictionary<int, List<StockMasterIdentity>> matches)
    {
        if (table is null || table.Columns.Count != 2 ||
            !HasExactStringColumn(table.Columns[0], StockNameColumn) ||
            !HasExactStringColumn(table.Columns[1], StockCodeColumn))
        {
            throw InvalidIdentity();
        }

        var candidatesByKey = candidates
            .GroupBy(static candidate => new StockMasterKey(candidate.Code, candidate.Name))
            .ToDictionary(static group => group.Key, static group => group.ToArray());
        foreach (DataRow row in table.Rows)
        {
            if (row[0] is not string rawName || row[1] is not string rawCode)
            {
                throw InvalidIdentity();
            }

            var name = rawName.Trim();
            var code = rawCode.Trim();
            var key = new StockMasterKey(code, name);
            if (!IsSafeCanonicalText(name, MaximumStockNameLength) ||
                !IsAsciiAlphaNumeric(code, MaximumStockCodeLength) ||
                !candidatesByKey.TryGetValue(key, out var matchingCandidates))
            {
                throw InvalidIdentity();
            }

            foreach (var candidate in matchingCandidates)
            {
                matches[candidate.Index].Add(new StockMasterIdentity(
                    profile.Market,
                    profile.Source,
                    name,
                    code));
            }
        }
    }

    private static bool TryMapThemeCode(
        string? itemCode,
        out StockMarket market,
        out string code)
    {
        market = default;
        code = string.Empty;
        if (string.IsNullOrEmpty(itemCode) || itemCode.Length is < 2 or > 13)
        {
            return false;
        }

        market = itemCode[0] switch
        {
            'P' => StockMarket.Kospi,
            'D' => StockMarket.Kosdaq,
            'X' => StockMarket.NxtKospi,
            'T' => StockMarket.NxtKosdaq,
            _ => (StockMarket)(-1)
        };
        code = itemCode[1..];
        return Enum.IsDefined(market) &&
            IsAsciiAlphaNumeric(code, MaximumStockCodeLength);
    }

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

    private static bool IsAsciiAlphaNumeric(string? value, int maximumLength) =>
        !string.IsNullOrEmpty(value) && value.Length <= maximumLength &&
        value.All(char.IsAsciiLetterOrDigit);

    private static bool IsSafeCanonicalText(string? value, int maximumLength)
    {
        if (string.IsNullOrEmpty(value) || value.Length > maximumLength ||
            !string.Equals(value, value.Trim(), StringComparison.Ordinal))
        {
            return false;
        }

        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 StockMasterIdentityDataException InvalidIdentity() =>
        new("The stock master did not return exactly one current typed identity for every catalog item.");

    private sealed record StockMasterCandidate(
        int Index,
        StockMarket? ExpectedMarket,
        string Code,
        string Name);

    private readonly record struct StockMasterKey(string Code, string Name);
}

internal sealed record StockMasterValidationProfile(
    StockMarket Market,
    DataSourceKind Source,
    string QueryName,
    string SelectAndWhereSql,
    string CodeExpression,
    string NameExpression,
    string OrderBySql,
    string? DisplaySelectAndWhereSql = null,
    string? DisplayNameExpression = null)
{
    internal DataQuerySpec CreateSpec(
        IReadOnlyList<StockMasterLookup> candidates,
        bool useNxtDisplayName)
    {
        ArgumentNullException.ThrowIfNull(candidates);
        if (candidates.Count == 0)
        {
            throw new ArgumentException("At least one stock identity is required.", nameof(candidates));
        }

        var marker = Source == DataSourceKind.Oracle ? ':' : '@';
        var selectSql = useNxtDisplayName && DisplaySelectAndWhereSql is not null
            ? DisplaySelectAndWhereSql
            : SelectAndWhereSql;
        var nameExpression = useNxtDisplayName && DisplayNameExpression is not null
            ? DisplayNameExpression
            : NameExpression;
        var sql = new StringBuilder(selectSql);
        sql.AppendLine();
        sql.AppendLine("  AND (");
        var parameters = new List<DataQueryParameter>(candidates.Count * 2);
        for (var index = 0; index < candidates.Count; index++)
        {
            if (index > 0)
            {
                sql.AppendLine("    OR");
            }

            var codeParameter = $"stock_code_{index}";
            var nameParameter = $"stock_name_{index}";
            sql.Append("    (");
            sql.Append(CodeExpression);
            sql.Append(" = ");
            sql.Append(marker);
            sql.Append(codeParameter);
            sql.Append(" AND ");
            sql.Append(nameExpression);
            sql.Append(" = ");
            sql.Append(marker);
            sql.Append(nameParameter);
            sql.AppendLine(")");
            parameters.Add(new DataQueryParameter(
                codeParameter,
                candidates[index].Code,
                DbType.String));
            parameters.Add(new DataQueryParameter(
                nameParameter,
                candidates[index].Name,
                DbType.String));
        }

        sql.AppendLine("  )");
        sql.Append(OrderBySql);
        var spec = new DataQuerySpec(sql.ToString(), parameters);
        spec.ValidateFor(Source);
        return spec;
    }
}

internal sealed record StockMasterLookup(string Code, string Name);

internal static class StockMasterValidationProfiles
{
    internal static IReadOnlyList<StockMasterValidationProfile> All { get; } =
    [
        new(
            StockMarket.Kospi,
            DataSourceKind.Oracle,
            "STOCK_MASTER_VERIFY_KOSPI",
            """
            SELECT F_STOCK_WANNAME AS STOCK_NAME, F_STOCK_CODE AS STOCK_CODE
            FROM T_STOCK
            WHERE F_MKT_HALT = 'N'
            """,
            "F_STOCK_CODE",
            "F_STOCK_WANNAME",
            "ORDER BY F_STOCK_WANNAME, F_STOCK_CODE"),
        new(
            StockMarket.Kosdaq,
            DataSourceKind.Oracle,
            "STOCK_MASTER_VERIFY_KOSDAQ",
            """
            SELECT F_STOCK_WANNAME AS STOCK_NAME, F_STOCK_CODE AS STOCK_CODE
            FROM T_KOSDAQ_STOCK
            WHERE F_MKT_HALT = 'N'
            """,
            "F_STOCK_CODE",
            "F_STOCK_WANNAME",
            "ORDER BY F_STOCK_WANNAME, F_STOCK_CODE"),
        new(
            StockMarket.NxtKospi,
            DataSourceKind.MariaDb,
            "STOCK_MASTER_VERIFY_NXT_KOSPI",
            """
            SELECT a.F_STOCK_NAME AS STOCK_NAME, a.F_STOCK_CODE AS STOCK_CODE
            FROM N_STOCK a
            INNER JOIN N_ONLINE b ON b.F_STOCK_CODE = a.F_STOCK_CODE
            WHERE a.F_STOP_GUBUN = 'N'
            """,
            "a.F_STOCK_CODE",
            "a.F_STOCK_NAME",
            "ORDER BY a.F_STOCK_NAME, a.F_STOCK_CODE",
            DisplaySelectAndWhereSql: """
                SELECT CONCAT(a.F_STOCK_NAME, '(NXT)') AS STOCK_NAME,
                       a.F_STOCK_CODE AS STOCK_CODE
                FROM N_STOCK a
                INNER JOIN N_ONLINE b ON b.F_STOCK_CODE = a.F_STOCK_CODE
                WHERE a.F_STOP_GUBUN = 'N'
                """,
            DisplayNameExpression: "CONCAT(a.F_STOCK_NAME, '(NXT)')"),
        new(
            StockMarket.NxtKosdaq,
            DataSourceKind.MariaDb,
            "STOCK_MASTER_VERIFY_NXT_KOSDAQ",
            """
            SELECT a.F_STOCK_NAME AS STOCK_NAME, a.F_STOCK_CODE AS STOCK_CODE
            FROM N_KOSDAQ_STOCK a
            INNER JOIN N_KOSDAQ_ONLINE b ON b.F_STOCK_CODE = a.F_STOCK_CODE
            WHERE a.F_STOP_GUBUN = 'N'
            """,
            "a.F_STOCK_CODE",
            "a.F_STOCK_NAME",
            "ORDER BY a.F_STOCK_NAME, a.F_STOCK_CODE",
            DisplaySelectAndWhereSql: """
                SELECT CONCAT(a.F_STOCK_NAME, '(NXT)') AS STOCK_NAME,
                       a.F_STOCK_CODE AS STOCK_CODE
                FROM N_KOSDAQ_STOCK a
                INNER JOIN N_KOSDAQ_ONLINE b ON b.F_STOCK_CODE = a.F_STOCK_CODE
                WHERE a.F_STOP_GUBUN = 'N'
                """,
            DisplayNameExpression: "CONCAT(a.F_STOCK_NAME, '(NXT)')")
    ];
}
