#nullable enable

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

namespace MMoneyCoderSharp.Data;

public enum LegacyNamedForeignStockRestoreFailure
{
    InvalidRow,
    IdentityNotFound,
    IdentityAmbiguous,
    DatabaseDataInvalid
}

/// <summary>
/// Non-sensitive reason token for read-only diagnostics. No database value is
/// carried, so aggregate audits can distinguish schema, correlation and symbol
/// contract failures without printing an input name or symbol.
/// </summary>
public enum LegacyNamedForeignStockDatabaseIssue
{
    SchemaOrRowBound,
    ValueType,
    InputNameMismatch,
    InputNameInvalid,
    SymbolEmpty,
    SymbolTooLong,
    SymbolNotCanonical,
    SymbolDataDelimiter,
    SymbolCaret,
    SymbolUnsupportedSlash,
    SymbolUnsupportedPlus,
    SymbolUnsupportedAmpersand,
    SymbolUnsupportedApostrophe,
    SymbolUnsupportedComma,
    SymbolUnsupportedParenthesis,
    SymbolUnsupportedHash,
    SymbolUnsupportedPercent,
    SymbolUnsupportedAsterisk,
    SymbolUnsupportedEquals,
    SymbolUnsupportedBackslash,
    SymbolUnsupportedAsciiPunctuation,
    SymbolNonAsciiFullWidth,
    SymbolNonAsciiHangul,
    SymbolNonAsciiCjk,
    SymbolNonAsciiWhitespace,
    SymbolNonAsciiLetterOrNumber,
    SymbolNonAsciiPunctuation,
    SymbolNonAsciiOther,
    NationInvalid,
    ForeignDomesticInvalid
}

/// <summary>
/// One untrusted seven-field PLAY_LIST row from the original UC5 overseas-stock
/// selector. The row has an input name but did not persist its symbol or nation.
/// </summary>
public sealed record LegacyNamedForeignStockRestoreRow(
    int ItemIndex,
    bool IsEnabled,
    string GroupCode,
    string Subject,
    string GraphicType,
    string Subtype,
    string PageText,
    string DataCode);

public sealed record LegacyNamedForeignStockRestoreIdentity(
    string ActionId,
    string InputName,
    string Symbol,
    string NationCode,
    string ForeignDomesticCode,
    string BuilderKey,
    string CutCode,
    string ValueType,
    int? PeriodDays);

public sealed record LegacyNamedForeignStockRestoreOutcome(
    int ItemIndex,
    LegacyNamedForeignStockRestoreIdentity? Identity,
    LegacyNamedForeignStockRestoreFailure? Failure,
    LegacyNamedForeignStockDatabaseIssue? DatabaseIssue = null)
{
    public bool IsResolved =>
        Identity is not null && Failure is null && DatabaseIssue is null;
}

public sealed record LegacyNamedForeignStockRestoreResult(
    DateTimeOffset RetrievedAt,
    IReadOnlyList<LegacyNamedForeignStockRestoreOutcome> Rows);

/// <summary>
/// Resolves the original UC5 `해외종목` rows against today's Oracle master.
/// Only an exact F_INPUT_NAME that identifies one distinct US/TW symbol is
/// accepted. Queries are bound and read-only, and this service never retries.
/// </summary>
public sealed class LegacyNamedForeignStockRestoreService
{
    public const int MaximumRows = LegacyNamedPlaylistPersistenceService.MaximumItems;
    public const string QueryName = "LEGACY_NAMED_FOREIGN_STOCK_RESTORE";

    private const string ExpectedGroupCode = "해외종목";
    private const string ExpectedPageText = "1/1";
    private const string ForeignDomesticCode = "1";
    private const string InputNameColumn = "F_INPUT_NAME";
    private const string SymbolColumn = "F_SYMB";
    private const string NationCodeColumn = "F_NATC";
    private const string ForeignDomesticColumn = "F_FDTC";
    private const int MaximumInputNameLength = 200;
    private const int MaximumSymbolLength = 64;

    private const string IdentitySql = """
        SELECT F_INPUT_NAME, F_SYMB, F_NATC, F_FDTC
        FROM (
            SELECT DISTINCT F_INPUT_NAME, F_SYMB, F_NATC, F_FDTC
            FROM T_WORLD_IX_EQ_MASTER
            WHERE F_FDTC = '1'
              AND F_NATC IN ('US', 'TW')
              AND F_INPUT_NAME = :input_name
            ORDER BY F_SYMB, F_NATC
        )
        WHERE ROWNUM <= 2
        """;

    private static readonly IReadOnlyDictionary<string, ActionProfile> CandleActions =
        new Dictionary<string, ActionProfile>(StringComparer.Ordinal)
        {
            ["5일"] = new("stock-candle-5d", "s8010", "8061", "PRICE", 5),
            ["20일"] = new("stock-candle-20d", "s8010", "8040", "PRICE", 20),
            ["60일"] = new("stock-candle-60d", "s8010", "8046", "PRICE", 60),
            ["120일"] = new("stock-candle-120d", "s8010", "8051", "PRICE", 120),
            ["240일"] = new("stock-candle-240d", "s8010", "8056", "PRICE", 240)
        };

    private static readonly ActionProfile CurrentAction =
        new("stock-current", "s5001", "5001", "CURRENT", PeriodDays: null);

    private readonly IDataQueryExecutor _executor;
    private readonly TimeProvider _timeProvider;

    public LegacyNamedForeignStockRestoreService(IDataQueryExecutor executor)
        : this(executor, TimeProvider.System)
    {
    }

    internal LegacyNamedForeignStockRestoreService(
        IDataQueryExecutor executor,
        TimeProvider timeProvider)
    {
        _executor = executor ?? throw new ArgumentNullException(nameof(executor));
        _timeProvider = timeProvider ?? throw new ArgumentNullException(nameof(timeProvider));
    }

    /// <summary>
    /// Routes only the exact legacy group and closed UC5 action grammar. The
    /// canonical FOREIGN_STOCK format is deliberately handled by its existing
    /// identity-bearing workflow and cannot collide with this restore pass.
    /// </summary>
    public static bool IsCandidate(LegacyNamedForeignStockRestoreRow? row) =>
        row is not null &&
        row.ItemIndex is >= 0 and < MaximumRows &&
        string.Equals(row.GroupCode, ExpectedGroupCode, StringComparison.Ordinal) &&
        IsCanonicalText(row.Subject, MaximumInputNameLength, allowEmpty: false) &&
        TryGetAction(row.GraphicType, row.Subtype, out _) &&
        string.Equals(row.PageText, ExpectedPageText, StringComparison.Ordinal) &&
        string.Equals(row.DataCode, string.Empty, StringComparison.Ordinal);

    public async Task<LegacyNamedForeignStockRestoreResult> ResolveAsync(
        IReadOnlyList<LegacyNamedForeignStockRestoreRow> rows,
        CancellationToken cancellationToken = default)
    {
        ArgumentNullException.ThrowIfNull(rows);
        if (rows.Count is < 1 or > MaximumRows)
        {
            throw new ArgumentOutOfRangeException(
                nameof(rows),
                $"A named foreign-stock restore must contain 1 through {MaximumRows} rows.");
        }

        var itemIndexes = new HashSet<int>();
        foreach (var row in rows)
        {
            if (row is null || row.ItemIndex is < 0 or >= MaximumRows ||
                !itemIndexes.Add(row.ItemIndex))
            {
                throw new ArgumentException(
                    "Named foreign-stock restore rows must have unique bounded item indexes.",
                    nameof(rows));
            }
        }

        cancellationToken.ThrowIfCancellationRequested();
        var identityCache = new Dictionary<string, IdentityResolution>(StringComparer.Ordinal);
        var outcomes = new List<LegacyNamedForeignStockRestoreOutcome>(rows.Count);
        foreach (var row in rows)
        {
            cancellationToken.ThrowIfCancellationRequested();
            outcomes.Add(await ResolveRowAsync(row, identityCache, cancellationToken)
                .ConfigureAwait(false));
        }

        return new LegacyNamedForeignStockRestoreResult(
            _timeProvider.GetLocalNow(),
            outcomes.AsReadOnly());
    }

    private async Task<LegacyNamedForeignStockRestoreOutcome> ResolveRowAsync(
        LegacyNamedForeignStockRestoreRow row,
        IDictionary<string, IdentityResolution> identityCache,
        CancellationToken cancellationToken)
    {
        if (!IsCandidate(row) ||
            !TryGetAction(row.GraphicType, row.Subtype, out var action))
        {
            return Failed(row.ItemIndex, LegacyNamedForeignStockRestoreFailure.InvalidRow);
        }

        if (!identityCache.TryGetValue(row.Subject, out var resolution))
        {
            resolution = await ResolveIdentityAsync(row.Subject, cancellationToken)
                .ConfigureAwait(false);
            identityCache.Add(row.Subject, resolution);
        }

        if (resolution.Failure.HasValue)
        {
            return Failed(
                row.ItemIndex,
                resolution.Failure.Value,
                resolution.DatabaseIssue);
        }

        var identity = resolution.Identity!;
        return new LegacyNamedForeignStockRestoreOutcome(
            row.ItemIndex,
            new LegacyNamedForeignStockRestoreIdentity(
                action.ActionId,
                identity.InputName,
                identity.Symbol,
                identity.NationCode,
                ForeignDomesticCode,
                action.BuilderKey,
                action.CutCode,
                action.ValueType,
                action.PeriodDays),
            Failure: null,
            DatabaseIssue: null);
    }

    private async Task<IdentityResolution> ResolveIdentityAsync(
        string inputName,
        CancellationToken cancellationToken)
    {
        var spec = new DataQuerySpec(
            IdentitySql,
            [new DataQueryParameter("input_name", inputName, DbType.String)]);
        spec.ValidateFor(DataSourceKind.Oracle);

        var table = await _executor.ExecuteAsync(
                DataSourceKind.Oracle,
                QueryName,
                spec,
                cancellationToken)
            .ConfigureAwait(false);
        cancellationToken.ThrowIfCancellationRequested();

        return ValidateIdentityTable(table, inputName);
    }

    private static IdentityResolution ValidateIdentityTable(DataTable? table, string inputName)
    {
        if (table is null || table.Columns.Count != 4 || table.Rows.Count > 2 ||
            !HasExactStringColumn(table.Columns[0], InputNameColumn) ||
            !HasExactStringColumn(table.Columns[1], SymbolColumn) ||
            !HasExactStringColumn(table.Columns[2], NationCodeColumn) ||
            !HasExactStringColumn(table.Columns[3], ForeignDomesticColumn))
        {
            return InvalidDatabaseData(LegacyNamedForeignStockDatabaseIssue.SchemaOrRowBound);
        }

        var identities = new List<WorldStockSearchItem>(table.Rows.Count);
        foreach (DataRow row in table.Rows)
        {
            if (row[0] is not string foundInputName ||
                row[1] is not string symbol ||
                row[2] is not string nationCode ||
                row[3] is not string foreignDomestic)
            {
                return InvalidDatabaseData(LegacyNamedForeignStockDatabaseIssue.ValueType);
            }

            if (!string.Equals(foundInputName, inputName, StringComparison.Ordinal))
            {
                return InvalidDatabaseData(LegacyNamedForeignStockDatabaseIssue.InputNameMismatch);
            }

            if (!IsCanonicalText(foundInputName, MaximumInputNameLength, allowEmpty: false))
            {
                return InvalidDatabaseData(LegacyNamedForeignStockDatabaseIssue.InputNameInvalid);
            }

            var symbolIssue = GetSymbolIssue(symbol);
            if (symbolIssue.HasValue)
            {
                return InvalidDatabaseData(symbolIssue.Value);
            }

            if (nationCode is not ("US" or "TW"))
            {
                return InvalidDatabaseData(LegacyNamedForeignStockDatabaseIssue.NationInvalid);
            }

            if (!string.Equals(foreignDomestic, ForeignDomesticCode, StringComparison.Ordinal))
            {
                return InvalidDatabaseData(LegacyNamedForeignStockDatabaseIssue.ForeignDomesticInvalid);
            }

            identities.Add(new WorldStockSearchItem(foundInputName, symbol, nationCode));
        }

        return identities.Count switch
        {
            0 => new IdentityResolution(
                Identity: null,
                LegacyNamedForeignStockRestoreFailure.IdentityNotFound),
            1 => new IdentityResolution(identities[0], Failure: null),
            2 => new IdentityResolution(
                Identity: null,
                LegacyNamedForeignStockRestoreFailure.IdentityAmbiguous),
            _ => InvalidDatabaseData(LegacyNamedForeignStockDatabaseIssue.SchemaOrRowBound)
        };
    }

    private static bool TryGetAction(
        string? graphicType,
        string? subtype,
        out ActionProfile action)
    {
        action = null!;
        if (string.Equals(graphicType, "1열판기본", StringComparison.Ordinal) &&
            string.Equals(subtype, string.Empty, StringComparison.Ordinal))
        {
            action = CurrentAction;
            return true;
        }

        return string.Equals(graphicType, "캔들그래프", StringComparison.Ordinal) &&
            subtype is not null &&
            CandleActions.TryGetValue(subtype, out action!);
    }

    /// <summary>
    /// Shared native validation for a bound foreign-master symbol. It exposes
    /// validation only; it never returns or serializes an operator identity.
    /// </summary>
    public static bool IsCanonicalBoundSymbol(string? value) =>
        value is not null && GetSymbolIssue(value) is null;

    private static LegacyNamedForeignStockDatabaseIssue? GetSymbolIssue(string value)
    {
        if (value.Length == 0)
        {
            return LegacyNamedForeignStockDatabaseIssue.SymbolEmpty;
        }

        if (value.Length > MaximumSymbolLength)
        {
            return LegacyNamedForeignStockDatabaseIssue.SymbolTooLong;
        }

        if (!string.Equals(value, value.Trim(), StringComparison.Ordinal))
        {
            return LegacyNamedForeignStockDatabaseIssue.SymbolNotCanonical;
        }

        foreach (var rune in value.EnumerateRunes())
        {
            if (rune.Value == '|')
            {
                return LegacyNamedForeignStockDatabaseIssue.SymbolDataDelimiter;
            }

            if (rune.Value == '^')
            {
                return LegacyNamedForeignStockDatabaseIssue.SymbolCaret;
            }

            var category = Rune.GetUnicodeCategory(rune);
            if (Rune.IsControl(rune) || rune.Value == '\uFFFD' ||
                category is UnicodeCategory.Format or UnicodeCategory.Surrogate or
                    UnicodeCategory.LineSeparator or UnicodeCategory.ParagraphSeparator)
            {
                return LegacyNamedForeignStockDatabaseIssue.SymbolNotCanonical;
            }

            // Oracle currently contains a small set of provider symbols with
            // Hangul letters and internal ASCII spaces. They are safe identity
            // data: the runtime passes the symbol only as a bound parameter,
            // canonical validation rejects edge whitespace, and serialized
            // identity uses '|' (not a letter/number/space) as its delimiter.
            if (Rune.IsLetterOrDigit(rune))
            {
                continue;
            }

            if (rune.Value > 0x7F)
            {
                if (rune.Value is >= 0xFF01 and <= 0xFF5E)
                {
                    return LegacyNamedForeignStockDatabaseIssue.SymbolNonAsciiFullWidth;
                }

                if (rune.Value is >= 0xAC00 and <= 0xD7AF or
                    >= 0x1100 and <= 0x11FF or
                    >= 0x3130 and <= 0x318F)
                {
                    return LegacyNamedForeignStockDatabaseIssue.SymbolNonAsciiHangul;
                }

                if (rune.Value is >= 0x3400 and <= 0x9FFF or
                    >= 0xF900 and <= 0xFAFF)
                {
                    return LegacyNamedForeignStockDatabaseIssue.SymbolNonAsciiCjk;
                }

                if (Rune.IsWhiteSpace(rune))
                {
                    return LegacyNamedForeignStockDatabaseIssue.SymbolNonAsciiWhitespace;
                }

                if (category is UnicodeCategory.ConnectorPunctuation or
                    UnicodeCategory.DashPunctuation or
                    UnicodeCategory.OpenPunctuation or
                    UnicodeCategory.ClosePunctuation or
                    UnicodeCategory.InitialQuotePunctuation or
                    UnicodeCategory.FinalQuotePunctuation or
                    UnicodeCategory.OtherPunctuation)
                {
                    return LegacyNamedForeignStockDatabaseIssue.SymbolNonAsciiPunctuation;
                }

                return LegacyNamedForeignStockDatabaseIssue.SymbolNonAsciiOther;
            }

            var character = (char)rune.Value;
            if (character == '/')
            {
                return LegacyNamedForeignStockDatabaseIssue.SymbolUnsupportedSlash;
            }

            var punctuationIssue = character switch
            {
                '+' => LegacyNamedForeignStockDatabaseIssue.SymbolUnsupportedPlus,
                '\'' => LegacyNamedForeignStockDatabaseIssue.SymbolUnsupportedApostrophe,
                ',' => LegacyNamedForeignStockDatabaseIssue.SymbolUnsupportedComma,
                '#' => LegacyNamedForeignStockDatabaseIssue.SymbolUnsupportedHash,
                '%' => LegacyNamedForeignStockDatabaseIssue.SymbolUnsupportedPercent,
                '*' => LegacyNamedForeignStockDatabaseIssue.SymbolUnsupportedAsterisk,
                '=' => LegacyNamedForeignStockDatabaseIssue.SymbolUnsupportedEquals,
                '\\' => LegacyNamedForeignStockDatabaseIssue.SymbolUnsupportedBackslash,
                _ => (LegacyNamedForeignStockDatabaseIssue?)null
            };
            if (punctuationIssue.HasValue)
            {
                return punctuationIssue.Value;
            }

            // Current Oracle identities include ampersands and parenthesized
            // provider symbols. They are safe bound values and cannot collide
            // with the '|' or '^' serialization delimiters guarded above.
            if (character is not ('@' or '.' or '_' or '$' or ':' or '-' or ' ' or
                '&' or '(' or ')'))
            {
                return LegacyNamedForeignStockDatabaseIssue.SymbolUnsupportedAsciiPunctuation;
            }
        }

        return null;
    }

    private static bool IsCanonicalText(string? value, int maximumLength, bool allowEmpty)
    {
        if (value is null || value.Length > maximumLength ||
            (!allowEmpty && value.Length == 0) ||
            !string.Equals(value, value.Trim(), StringComparison.Ordinal))
        {
            return false;
        }

        foreach (var rune in value.EnumerateRunes())
        {
            var category = Rune.GetUnicodeCategory(rune);
            if (Rune.IsControl(rune) || rune.Value == '\uFFFD' ||
                category is UnicodeCategory.Format or UnicodeCategory.Surrogate or
                    UnicodeCategory.LineSeparator or UnicodeCategory.ParagraphSeparator)
            {
                return false;
            }
        }

        return !value.Contains('^');
    }

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

    private static LegacyNamedForeignStockRestoreOutcome Failed(
        int itemIndex,
        LegacyNamedForeignStockRestoreFailure failure,
        LegacyNamedForeignStockDatabaseIssue? databaseIssue = null) =>
        new(itemIndex, Identity: null, failure, databaseIssue);

    private static IdentityResolution InvalidDatabaseData(
        LegacyNamedForeignStockDatabaseIssue issue) =>
        new(
            Identity: null,
            LegacyNamedForeignStockRestoreFailure.DatabaseDataInvalid,
            issue);

    private sealed record ActionProfile(
        string ActionId,
        string BuilderKey,
        string CutCode,
        string ValueType,
        int? PeriodDays);

    private sealed record IdentityResolution(
        WorldStockSearchItem? Identity,
        LegacyNamedForeignStockRestoreFailure? Failure,
        LegacyNamedForeignStockDatabaseIssue? DatabaseIssue = null);
}
