#nullable enable

using System.Data;

namespace MMoneyCoderSharp.Data;

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

/// <summary>
/// One untrusted legacy playlist row selected for the foreign-index candle
/// restoration pass. All persisted fields retain their exact text. IsEnabled
/// is carried so the caller can preserve disabled rows; it is not an identity
/// discriminator.
/// </summary>
public sealed record LegacyForeignIndexCandleRestoreRow(
    int ItemIndex,
    bool IsEnabled,
    string GroupCode,
    string Subject,
    string GraphicType,
    string Subtype,
    string PageText,
    string DataCode);

/// <summary>
/// A closed, database-verified selection that may be converted into an s8010
/// playlist entry. None of these values are copied from an untrusted result.
/// </summary>
public sealed record LegacyForeignIndexCandleRestoreIdentity(
    string TargetKey,
    string InputName,
    string Symbol,
    string NationCode,
    string ForeignDomesticCode,
    string SceneCode,
    string ValueType,
    int PeriodDays,
    string CutCode);

public sealed record LegacyForeignIndexCandleRestoreOutcome(
    int ItemIndex,
    LegacyForeignIndexCandleRestoreIdentity? Identity,
    LegacyForeignIndexCandleRestoreFailure? Failure)
{
    public bool IsResolved => Identity is not null && Failure is null;
}

public sealed record LegacyForeignIndexCandleRestoreResult(
    DateTimeOffset RetrievedAt,
    IReadOnlyList<LegacyForeignIndexCandleRestoreOutcome> Rows);

/// <summary>
/// Reconstructs the three closed UC5 foreign-index candle targets. The legacy
/// playlist did not persist symbol, nation or F_FDTC, so the expected identity
/// is checked against Oracle before a trusted selection is issued. Queries are
/// exact, parameterized and read-only; failures are never retried here.
/// </summary>
public sealed class LegacyForeignIndexCandleRestoreService
{
    public const int MaximumRows = LegacyNamedPlaylistPersistenceService.MaximumItems;
    public const string QueryName = "LEGACY_FOREIGN_INDEX_CANDLE_RESTORE";

    private const string InputNameColumn = "F_INPUT_NAME";
    private const string SymbolColumn = "F_SYMB";
    private const string NationColumn = "F_NATC";
    private const string ForeignDomesticColumn = "F_FDTC";
    private const string NationCode = "US";
    private const string ForeignDomesticCode = "0";
    private const string SceneCode = "s8010";
    private const string ValueType = "PRICE";
    private const string ExpectedGroupCode = "해외지수";
    private const string ExpectedGraphicType = "캔들그래프";
    private const string ExpectedPageText = "1/1";

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

    private static readonly IReadOnlyDictionary<string, TargetProfile> Targets =
        new Dictionary<string, TargetProfile>(StringComparer.Ordinal)
        {
            ["다우존스"] = new("Dow", "다우존스", "DJI@DJI"),
            ["나스닥"] = new("Nasdaq", "나스닥", "NAS@IXIC"),
            ["S&P500"] = new("Sp500", "S&P500", "SPI@SPX")
        };

    private static readonly IReadOnlyDictionary<string, PeriodProfile> Periods =
        new Dictionary<string, PeriodProfile>(StringComparer.Ordinal)
        {
            ["5일"] = new(5, "8061"),
            ["20일"] = new(20, "8040"),
            ["60일"] = new(60, "8046"),
            ["120일"] = new(120, "8051"),
            ["240일"] = new(240, "8056")
        };

    private readonly IDataQueryExecutor _executor;

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

    /// <summary>
    /// A narrow classifier for routing only exact closed-table candidates to
    /// the native verifier. ResolveAsync independently validates every row.
    /// </summary>
    public static bool IsCandidate(LegacyForeignIndexCandleRestoreRow? row) =>
        row is not null &&
        row.ItemIndex is >= 0 and < MaximumRows &&
        string.Equals(row.GroupCode, ExpectedGroupCode, StringComparison.Ordinal) &&
        row.Subject is not null && Targets.ContainsKey(row.Subject) &&
        string.Equals(row.GraphicType, ExpectedGraphicType, StringComparison.Ordinal) &&
        row.Subtype is not null && Periods.ContainsKey(row.Subtype) &&
        string.Equals(row.PageText, ExpectedPageText, StringComparison.Ordinal) &&
        string.Equals(row.DataCode, string.Empty, StringComparison.Ordinal);

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

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

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

        return new LegacyForeignIndexCandleRestoreResult(
            DateTimeOffset.Now,
            outcomes.AsReadOnly());
    }

    private async Task<LegacyForeignIndexCandleRestoreOutcome> ResolveRowAsync(
        LegacyForeignIndexCandleRestoreRow row,
        IDictionary<string, TargetVerification> verifiedTargets,
        CancellationToken cancellationToken)
    {
        if (!string.Equals(row.GroupCode, ExpectedGroupCode, StringComparison.Ordinal) ||
            row.Subject is null ||
            !Targets.TryGetValue(row.Subject, out var target) ||
            !string.Equals(row.GraphicType, ExpectedGraphicType, StringComparison.Ordinal) ||
            row.Subtype is null ||
            !Periods.TryGetValue(row.Subtype, out var period) ||
            !string.Equals(row.PageText, ExpectedPageText, StringComparison.Ordinal) ||
            !string.Equals(row.DataCode, string.Empty, StringComparison.Ordinal))
        {
            return Failed(row.ItemIndex, LegacyForeignIndexCandleRestoreFailure.InvalidRow);
        }

        if (!verifiedTargets.TryGetValue(target.TargetKey, out var verification))
        {
            verification = await VerifyTargetAsync(target, cancellationToken)
                .ConfigureAwait(false);
            verifiedTargets.Add(target.TargetKey, verification);
        }

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

        return new LegacyForeignIndexCandleRestoreOutcome(
            row.ItemIndex,
            new LegacyForeignIndexCandleRestoreIdentity(
                target.TargetKey,
                target.InputName,
                target.Symbol,
                NationCode,
                ForeignDomesticCode,
                SceneCode,
                ValueType,
                period.Days,
                period.CutCode),
            Failure: null);
    }

    private async Task<TargetVerification> VerifyTargetAsync(
        TargetProfile target,
        CancellationToken cancellationToken)
    {
        var spec = new DataQuerySpec(
            IdentitySql,
            [
                new DataQueryParameter("input_name", target.InputName, DbType.String),
                new DataQueryParameter("symbol", target.Symbol, DbType.String)
            ]);
        spec.ValidateFor(DataSourceKind.Oracle);

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

        return ValidateIdentityTable(table, target);
    }

    private static TargetVerification ValidateIdentityTable(
        DataTable? table,
        TargetProfile target)
    {
        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], NationColumn) ||
            !HasExactStringColumn(table.Columns[3], ForeignDomesticColumn))
        {
            return InvalidDatabaseData();
        }

        foreach (DataRow row in table.Rows)
        {
            if (row[0] is not string inputName ||
                row[1] is not string symbol ||
                row[2] is not string nation ||
                row[3] is not string foreignDomestic ||
                !string.Equals(inputName, target.InputName, StringComparison.Ordinal) ||
                !string.Equals(symbol, target.Symbol, StringComparison.Ordinal) ||
                !string.Equals(nation, NationCode, StringComparison.Ordinal) ||
                !string.Equals(foreignDomestic, ForeignDomesticCode, StringComparison.Ordinal))
            {
                return InvalidDatabaseData();
            }
        }

        return table.Rows.Count switch
        {
            0 => new TargetVerification(
                LegacyForeignIndexCandleRestoreFailure.IdentityNotFound),
            1 => new TargetVerification(Failure: null),
            2 => new TargetVerification(
                LegacyForeignIndexCandleRestoreFailure.IdentityAmbiguous),
            _ => InvalidDatabaseData()
        };
    }

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

    private static LegacyForeignIndexCandleRestoreOutcome Failed(
        int itemIndex,
        LegacyForeignIndexCandleRestoreFailure failure) =>
        new(itemIndex, Identity: null, failure);

    private static TargetVerification InvalidDatabaseData() =>
        new(LegacyForeignIndexCandleRestoreFailure.DatabaseDataInvalid);

    private sealed record TargetProfile(
        string TargetKey,
        string InputName,
        string Symbol);

    private sealed record PeriodProfile(int Days, string CutCode);

    private sealed record TargetVerification(
        LegacyForeignIndexCandleRestoreFailure? Failure);
}
