#nullable enable

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

namespace MMoneyCoderSharp.Data;

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

public sealed record ExpertCatalogDefinition(
    ExpertSelectionIdentity Identity,
    IReadOnlyList<ExpertCatalogRecommendation> Recommendations);

public interface IExpertCatalogPersistenceService
{
    bool CanMutate { get; }

    Task<string> SuggestNextCodeAsync(CancellationToken cancellationToken = default);

    Task<OperatorCatalogMutationReceipt> CreateAsync(
        ExpertCatalogDefinition definition,
        CancellationToken cancellationToken = default);

    Task<OperatorCatalogMutationReceipt> ReplaceAsync(
        ExpertSelectionIdentity expectedIdentity,
        string newName,
        IReadOnlyList<ExpertCatalogRecommendation> recommendations,
        CancellationToken cancellationToken = default);

    Task<OperatorCatalogMutationReceipt> DeleteAsync(
        ExpertSelectionIdentity expectedIdentity,
        CancellationToken cancellationToken = default);
}

/// <summary>
/// Atomic migration of EList/UC6 expert CRUD on Oracle. Code and name are a
/// closed optimistic identity, while stale edits are checked inside the same
/// serializable transaction that replaces recommendation rows.
/// </summary>
public sealed partial class LegacyExpertCatalogPersistenceService : IExpertCatalogPersistenceService
{
    public const int MaximumRecommendations = 100;

    internal const string NextCodeQueryName = "EXPERT_NEXT_CODE";

    private const int MaximumExpertNameLength = 128;
    private const int MaximumStockNameLength = 200;
    private const decimal MaximumWebSafeInteger = 9_007_199_254_740_991m;

    private const string NextCodeSql = """
        SELECT CASE
                   WHEN NVL(MAX(TO_NUMBER(TRIM(EP_CODE))), 0) < 9999
                   THEN LPAD(
                       TO_CHAR(NVL(MAX(TO_NUMBER(TRIM(EP_CODE))), 0) + 1),
                       4,
                       '0')
                   ELSE 'EXHAUSTED'
               END EXPERT_CODE
        FROM EXPERT_LIST
        """;

    private const string InsertExpertSql = """
        INSERT INTO EXPERT_LIST(EP_CODE, EP_NAME)
        SELECT :expert_code, :expert_name
        FROM DUAL
        WHERE NOT EXISTS (
            SELECT 1 FROM EXPERT_LIST WHERE EP_CODE = :conflict_code)
        """;

    private const string UpdateExpertSql = """
        UPDATE EXPERT_LIST target
        SET EP_NAME = :new_name
        WHERE target.EP_CODE = :expert_code
          AND target.EP_NAME = :expected_name
        """;

    private const string DeleteRecommendationsSql =
        "DELETE FROM RECOMMEND_LIST WHERE RC_CODE = :expert_code";

    private const string InsertRecommendationSql = """
        INSERT INTO RECOMMEND_LIST(
            RC_CODE,
            RC_STOCK_CODE,
            RC_STOCK_NAME,
            RC_BUY_AMOUNT,
            PLAYINDEX)
        VALUES (
            :expert_code,
            :stock_code,
            :stock_name,
            :buy_amount,
            :play_index)
        """;

    private const string DeleteExpertSql = """
        DELETE FROM EXPERT_LIST
        WHERE EP_CODE = :expert_code
          AND EP_NAME = :expected_name
        """;

    private readonly IDataQueryExecutor _queryExecutor;
    private readonly IOperatorCatalogMutationExecutor? _mutationExecutor;

    public LegacyExpertCatalogPersistenceService(IDataQueryExecutor queryExecutor)
        : this(queryExecutor, null)
    {
    }

    public LegacyExpertCatalogPersistenceService(
        IDataQueryExecutor queryExecutor,
        IOperatorCatalogMutationExecutor? mutationExecutor)
    {
        _queryExecutor = queryExecutor ?? throw new ArgumentNullException(nameof(queryExecutor));
        _mutationExecutor = mutationExecutor;
    }

    public bool CanMutate => _mutationExecutor is not null;

    public async Task<string> SuggestNextCodeAsync(
        CancellationToken cancellationToken = default)
    {
        cancellationToken.ThrowIfCancellationRequested();
        var query = new DataQuerySpec(NextCodeSql);
        query.ValidateFor(DataSourceKind.Oracle);
        var table = await _queryExecutor.ExecuteAsync(
            DataSourceKind.Oracle,
            NextCodeQueryName,
            query,
            cancellationToken).ConfigureAwait(false);
        cancellationToken.ThrowIfCancellationRequested();

        if (table is null ||
            table.Columns.Count != 1 ||
            !string.Equals(table.Columns[0].ColumnName, "EXPERT_CODE", StringComparison.Ordinal) ||
            table.Columns[0].DataType != typeof(string) ||
            table.Rows.Count != 1 ||
            table.Rows[0][0] is not string code ||
            !ExpertCodePattern().IsMatch(code))
        {
            throw new ExpertSelectionDataException(
                "Expert next-code data has an invalid schema, value, or exhausted code range.");
        }

        return code;
    }

    public Task<OperatorCatalogMutationReceipt> CreateAsync(
        ExpertCatalogDefinition definition,
        CancellationToken cancellationToken = default)
    {
        var canonical = ValidateDefinition(definition, nameof(definition));
        var commands = new List<OperatorCatalogDbCommand>(
            canonical.Recommendations.Count + 1)
        {
            Command(
                OperatorCatalogDbCommandKind.InsertExpert,
                InsertExpertSql,
                OperatorCatalogAffectedRowsRule.ExactlyOne,
                String("expert_code", canonical.Identity.ExpertCode),
                String("expert_name", canonical.Identity.ExpertName),
                String("conflict_code", canonical.Identity.ExpertCode))
        };
        commands.AddRange(canonical.Recommendations.Select(recommendation =>
            CreateRecommendationCommand(canonical.Identity.ExpertCode, recommendation)));

        return ExecuteAsync(
            new OperatorCatalogDbTransaction(
                Guid.NewGuid(),
                OperatorCatalogMutationKind.CreateExpert,
                DataSourceKind.Oracle,
                canonical.Identity.ExpertCode,
                commands),
            cancellationToken);
    }

    public Task<OperatorCatalogMutationReceipt> ReplaceAsync(
        ExpertSelectionIdentity expectedIdentity,
        string newName,
        IReadOnlyList<ExpertCatalogRecommendation> recommendations,
        CancellationToken cancellationToken = default)
    {
        var expected = ValidateIdentity(expectedIdentity, nameof(expectedIdentity));
        var canonicalName = ValidateCanonicalText(
            newName,
            MaximumExpertNameLength,
            nameof(newName));
        var canonicalRecommendations = ValidateRecommendations(
            recommendations,
            nameof(recommendations));
        var commands = new List<OperatorCatalogDbCommand>(
            canonicalRecommendations.Count + 2)
        {
            Command(
                OperatorCatalogDbCommandKind.UpdateExpert,
                UpdateExpertSql,
                OperatorCatalogAffectedRowsRule.ExactlyOne,
                String("new_name", canonicalName),
                String("expert_code", expected.ExpertCode),
                String("expected_name", expected.ExpertName)),
            Command(
                OperatorCatalogDbCommandKind.DeleteRecommendations,
                DeleteRecommendationsSql,
                OperatorCatalogAffectedRowsRule.AnyNonNegative,
                String("expert_code", expected.ExpertCode))
        };
        commands.AddRange(canonicalRecommendations.Select(recommendation =>
            CreateRecommendationCommand(expected.ExpertCode, recommendation)));

        return ExecuteAsync(
            new OperatorCatalogDbTransaction(
                Guid.NewGuid(),
                OperatorCatalogMutationKind.ReplaceExpert,
                DataSourceKind.Oracle,
                expected.ExpertCode,
                commands),
            cancellationToken);
    }

    public Task<OperatorCatalogMutationReceipt> DeleteAsync(
        ExpertSelectionIdentity expectedIdentity,
        CancellationToken cancellationToken = default)
    {
        var expected = ValidateIdentity(expectedIdentity, nameof(expectedIdentity));
        return ExecuteAsync(
            new OperatorCatalogDbTransaction(
                Guid.NewGuid(),
                OperatorCatalogMutationKind.DeleteExpert,
                DataSourceKind.Oracle,
                expected.ExpertCode,
                [
                    Command(
                        OperatorCatalogDbCommandKind.DeleteRecommendations,
                        DeleteRecommendationsSql,
                        OperatorCatalogAffectedRowsRule.AnyNonNegative,
                        String("expert_code", expected.ExpertCode)),
                    Command(
                        OperatorCatalogDbCommandKind.DeleteExpert,
                        DeleteExpertSql,
                        OperatorCatalogAffectedRowsRule.ExactlyOne,
                        String("expert_code", expected.ExpertCode),
                        String("expected_name", expected.ExpertName))
                ]),
            cancellationToken);
    }

    private async Task<OperatorCatalogMutationReceipt> ExecuteAsync(
        OperatorCatalogDbTransaction transaction,
        CancellationToken cancellationToken)
    {
        var executor = _mutationExecutor ?? throw new InvalidOperationException(
            "Expert catalog persistence is configured for read-only access.");
        cancellationToken.ThrowIfCancellationRequested();

        OperatorCatalogDbTransactionResult result;
        try
        {
            result = await executor.ExecuteTransactionAsync(transaction, cancellationToken)
                .ConfigureAwait(false);
        }
        catch (OperatorCatalogMutationException)
        {
            throw;
        }
        catch (Exception exception)
        {
            throw new OperatorCatalogMutationException(
                $"The {transaction.Kind} transaction outcome is unknown; do not retry automatically.",
                outcomeUnknown: true,
                exception);
        }

        if (result is null ||
            result.OperationId != transaction.OperationId ||
            result.AffectedRows is null ||
            result.AffectedRows.Count != transaction.Commands.Count ||
            result.AffectedRows.Any(static count => count < 0))
        {
            throw new OperatorCatalogMutationException(
                $"The {transaction.Kind} transaction returned an ambiguous commit receipt; do not retry automatically.",
                outcomeUnknown: true);
        }

        for (var index = 0; index < transaction.Commands.Count; index++)
        {
            if (transaction.Commands[index].AffectedRowsRule ==
                    OperatorCatalogAffectedRowsRule.ExactlyOne &&
                result.AffectedRows[index] != 1)
            {
                throw new OperatorCatalogMutationException(
                    $"The {transaction.Kind} transaction returned an ambiguous affected-row receipt; do not retry automatically.",
                    outcomeUnknown: true);
            }
        }

        return new OperatorCatalogMutationReceipt(
            result.OperationId,
            transaction.Kind,
            result.CommittedAt);
    }

    private static OperatorCatalogDbCommand CreateRecommendationCommand(
        string expertCode,
        ExpertCatalogRecommendation recommendation) =>
        Command(
            OperatorCatalogDbCommandKind.InsertRecommendation,
            InsertRecommendationSql,
            OperatorCatalogAffectedRowsRule.ExactlyOne,
            String("expert_code", expertCode),
            String("stock_code", recommendation.StockCode),
            String("stock_name", recommendation.StockName),
            new OperatorCatalogDbParameter(
                "buy_amount",
                recommendation.BuyAmount,
                DbType.Decimal),
            new OperatorCatalogDbParameter(
                "play_index",
                recommendation.PlayIndex,
                DbType.Int32));

    private static ExpertCatalogDefinition ValidateDefinition(
        ExpertCatalogDefinition definition,
        string parameterName)
    {
        ArgumentNullException.ThrowIfNull(definition);
        return new ExpertCatalogDefinition(
            ValidateIdentity(definition.Identity, parameterName),
            ValidateRecommendations(definition.Recommendations, parameterName));
    }

    private static ExpertSelectionIdentity ValidateIdentity(
        ExpertSelectionIdentity identity,
        string parameterName)
    {
        ArgumentNullException.ThrowIfNull(identity);
        var code = ValidateCanonicalText(identity.ExpertCode, 4, parameterName);
        if (!ExpertCodePattern().IsMatch(code))
        {
            throw new ArgumentException(
                "An expert code must contain four digits.",
                parameterName);
        }

        var name = ValidateCanonicalText(
            identity.ExpertName,
            MaximumExpertNameLength,
            parameterName);
        return new ExpertSelectionIdentity(code, name);
    }

    private static IReadOnlyList<ExpertCatalogRecommendation> ValidateRecommendations(
        IReadOnlyList<ExpertCatalogRecommendation> recommendations,
        string parameterName)
    {
        ArgumentNullException.ThrowIfNull(recommendations);
        if (recommendations.Count > MaximumRecommendations)
        {
            throw new ArgumentOutOfRangeException(
                parameterName,
                $"An expert can contain at most {MaximumRecommendations} recommendations.");
        }

        var canonical = new ExpertCatalogRecommendation[recommendations.Count];
        for (var index = 0; index < recommendations.Count; index++)
        {
            var recommendation = recommendations[index] ?? throw new ArgumentException(
                "An expert recommendation is missing.",
                parameterName);
            if (recommendation.PlayIndex != index)
            {
                throw new ArgumentException(
                    "Recommendation indexes must be contiguous and zero based.",
                    parameterName);
            }

            var stockCode = ValidateCanonicalText(
                recommendation.StockCode,
                32,
                parameterName);
            if (!StockCodePattern().IsMatch(stockCode))
            {
                throw new ArgumentException("A recommendation stock code is invalid.", parameterName);
            }

            var stockName = ValidateCanonicalText(
                recommendation.StockName,
                MaximumStockNameLength,
                parameterName);
            var buyAmount = recommendation.BuyAmount;
            if (buyAmount <= 0 || buyAmount > MaximumWebSafeInteger ||
                decimal.Truncate(buyAmount) != buyAmount)
            {
                throw new ArgumentOutOfRangeException(
                    parameterName,
                    "A recommendation buy amount must be a positive whole web-safe integer.");
            }

            canonical[index] = new ExpertCatalogRecommendation(
                index,
                stockCode,
                stockName,
                buyAmount);
        }

        return Array.AsReadOnly(canonical);
    }

    private static string ValidateCanonicalText(
        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("An expert catalog value is invalid.", parameterName);
        }

        return value;
    }

    private static bool IsSafeText(string value)
    {
        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 OperatorCatalogDbParameter String(string name, string value) =>
        new(name, value, DbType.String);

    private static OperatorCatalogDbCommand Command(
        OperatorCatalogDbCommandKind kind,
        string sql,
        OperatorCatalogAffectedRowsRule rule,
        params OperatorCatalogDbParameter[] parameters) =>
        new(kind, sql, rule, parameters);

    [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();
}
