using System.Data;
using MMoneyCoderSharp.Data;

namespace MBN_STOCK_WEBVIEW.Core.Tests;

public sealed class LegacyWorldStockSearchServiceTests
{
    [Fact]
    public async Task SearchAsync_UsesBoundOracleQueryAndReturnsExactIdentityFields()
    {
        var executor = new RecordingExecutor(_ => WorldStockTable(
            ("Apple", "AAPL", "US"),
            ("Taiwan Semiconductor", "2330", "TW")));
        var service = new LegacyWorldStockSearchService(executor);
        using var cancellation = new CancellationTokenSource();

        var result = await service.SearchAsync(" apple ", 25, cancellation.Token);

        Assert.Equal("apple", result.Query);
        Assert.False(result.IsTruncated);
        Assert.Collection(
            result.Items,
            item => Assert.Equal(new WorldStockSearchItem("Apple", "AAPL", "US"), item),
            item => Assert.Equal(
                new WorldStockSearchItem("Taiwan Semiconductor", "2330", "TW"),
                item));

        var call = Assert.Single(executor.Calls);
        Assert.Equal(DataSourceKind.Oracle, call.Source);
        Assert.Equal("WORLD_STOCK_SEARCH", call.TableName);
        Assert.Equal(cancellation.Token, call.CancellationToken);
        call.Spec.ValidateFor(DataSourceKind.Oracle);
        Assert.Contains("FROM T_WORLD_IX_EQ_MASTER", call.Spec.Sql, StringComparison.Ordinal);
        Assert.Contains("F_FDTC = '1'", call.Spec.Sql, StringComparison.Ordinal);
        Assert.Contains("F_NATC IN ('US', 'TW')", call.Spec.Sql, StringComparison.Ordinal);
        Assert.Contains("ESCAPE '!'", call.Spec.Sql, StringComparison.Ordinal);
        Assert.Contains("ROWNUM <= :row_limit", call.Spec.Sql, StringComparison.Ordinal);
        Assert.DoesNotContain("apple", call.Spec.Sql, StringComparison.OrdinalIgnoreCase);
        Assert.Collection(
            call.Spec.Parameters,
            parameter =>
            {
                Assert.Equal("search_term", parameter.Name);
                Assert.Equal("APPLE", parameter.Value);
                Assert.Equal(DbType.String, parameter.DbType);
            },
            parameter =>
            {
                Assert.Equal("row_limit", parameter.Name);
                Assert.Equal(
                    25 + LegacyWorldStockSearchService.MaximumIsolatedRows + 1,
                    parameter.Value);
                Assert.Equal(DbType.Int32, parameter.DbType);
            });
        Assert.Equal(0, executor.StringSqlCallCount);
    }

    [Fact]
    public async Task SearchAsync_EscapesLikeMetacharactersInsideBoundValue()
    {
        var executor = new RecordingExecutor(_ => WorldStockTable());
        var service = new LegacyWorldStockSearchService(executor);

        await service.SearchAsync(" a!_%b ");

        var call = Assert.Single(executor.Calls);
        Assert.Equal("A!!!_!%B", call.Spec.Parameters[0].Value);
        Assert.Contains("LIKE '%' || :search_term || '%' ESCAPE '!'", call.Spec.Sql, StringComparison.Ordinal);
    }

    [Fact]
    public async Task SearchAsync_AcceptsFiniteCompleteWorldCompatibilityCeiling()
    {
        var executor = new RecordingExecutor(_ => WorldStockTable());
        var service = new LegacyWorldStockSearchService(executor);

        var result = await service.SearchAsync(
            string.Empty,
            LegacyWorldStockSearchService.MaximumResults);

        Assert.False(result.IsTruncated);
        Assert.Equal(5_000, LegacyWorldStockSearchService.MaximumResults);
        Assert.Equal(
            LegacyWorldStockSearchService.MaximumResults +
                LegacyWorldStockSearchService.MaximumIsolatedRows + 1,
            Assert.Single(Assert.Single(executor.Calls).Spec.Parameters).Value);
    }

    [Theory]
    [InlineData("")]
    [InlineData("   ")]
    public async Task SearchAsync_AllowsOriginalUc3BlankInitialList(string query)
    {
        var executor = new RecordingExecutor(_ => WorldStockTable());
        var service = new LegacyWorldStockSearchService(executor);

        var result = await service.SearchAsync(query);

        Assert.Equal(string.Empty, result.Query);
        var call = Assert.Single(executor.Calls);
        Assert.DoesNotContain("search_term", call.Spec.Sql, StringComparison.Ordinal);
        Assert.Equal("row_limit", Assert.Single(call.Spec.Parameters).Name);
    }

    [Fact]
    public async Task SearchAsync_InitialConsonantsUseTheBoundedUsTwSearchCandidateWindow()
    {
        var executor = new RecordingExecutor(_ => WorldStockTable(
            ("삼성전자", "SAMSUNG", "US"),
            ("Apple", "AAPL", "US"),
            ("신성전자", "SHINSEONG", "TW")));
        var service = new LegacyWorldStockSearchService(executor);

        var result = await service.SearchAsync(" ㅅㅅㅈㅈ ", maximumResults: 1);

        Assert.Equal("ㅅㅅㅈㅈ", result.Query);
        Assert.True(result.IsTruncated);
        Assert.Equal(
            new WorldStockSearchItem("삼성전자", "SAMSUNG", "US"),
            Assert.Single(result.Items));
        var call = Assert.Single(executor.Calls);
        Assert.Contains("F_NATC IN ('US', 'TW')", call.Spec.Sql, StringComparison.Ordinal);
        Assert.Contains(
            "LIKE '%' || :search_term || '%' ESCAPE '!'",
            call.Spec.Sql,
            StringComparison.Ordinal);
        Assert.Equal(string.Empty, call.Spec.Parameters[0].Value);
        Assert.Equal(
            LegacyWorldStockSearchService.MaximumResults +
                LegacyWorldStockSearchService.MaximumIsolatedRows + 1,
            call.Spec.Parameters[1].Value);
    }

    [Fact]
    public async Task SearchAsync_ReturnsBoundedRowsAndReportsTruncation()
    {
        var executor = new RecordingExecutor(_ => WorldStockTable(
            ("Alpha", "AAA", "US"),
            ("Beta", "BBB", "US"),
            ("Gamma", "CCC", "TW")));
        var service = new LegacyWorldStockSearchService(executor);

        var result = await service.SearchAsync("a", maximumResults: 2);

        Assert.True(result.IsTruncated);
        Assert.Equal(2, result.Items.Count);
        Assert.Equal(
            2 + LegacyWorldStockSearchService.MaximumIsolatedRows + 1,
            executor.Calls[0].Spec.Parameters[1].Value);
    }

    [Theory]
    [InlineData(0)]
    [InlineData(-1)]
    [InlineData(LegacyWorldStockSearchService.MaximumResults + 1)]
    [InlineData(int.MaxValue)]
    public async Task SearchAsync_RejectsInvalidMaximumBeforeDatabaseAccess(int maximumResults)
    {
        var executor = new RecordingExecutor(_ => WorldStockTable());
        var service = new LegacyWorldStockSearchService(executor);

        var exception = await Assert.ThrowsAsync<ArgumentOutOfRangeException>(
            () => service.SearchAsync("apple", maximumResults));

        Assert.Equal("maximumResults", exception.ParamName);
        Assert.Empty(executor.Calls);
    }

    [Theory]
    [InlineData("bad\u0000value")]
    [InlineData("\napple")]
    [InlineData("bad\u200Bvalue")]
    [InlineData("bad\uD800value")]
    public async Task SearchAsync_RejectsUnsafeQueryBeforeDatabaseAccess(string query)
    {
        var executor = new RecordingExecutor(_ => WorldStockTable());
        var service = new LegacyWorldStockSearchService(executor);

        var exception = await Assert.ThrowsAsync<ArgumentException>(
            () => service.SearchAsync(query));

        Assert.Equal("query", exception.ParamName);
        Assert.Empty(executor.Calls);
    }

    [Fact]
    public async Task SearchAsync_RejectsNullAndOverlongQueriesBeforeDatabaseAccess()
    {
        var executor = new RecordingExecutor(_ => WorldStockTable());
        var service = new LegacyWorldStockSearchService(executor);

        await Assert.ThrowsAsync<ArgumentNullException>(() => service.SearchAsync(null!));
        await Assert.ThrowsAsync<ArgumentException>(
            () => service.SearchAsync(
                new string('A', LegacyWorldStockSearchService.MaximumQueryLength + 1)));

        Assert.Empty(executor.Calls);
    }

    [Fact]
    public async Task SearchAsync_RejectsAnythingOtherThanExactThreeStringColumnSchema()
    {
        var wrongCase = new DataTable();
        wrongCase.Columns.Add("f_input_name", typeof(string));
        wrongCase.Columns.Add("F_SYMB", typeof(string));
        wrongCase.Columns.Add("F_NATC", typeof(string));
        var executor = new RecordingExecutor(_ => wrongCase);
        var service = new LegacyWorldStockSearchService(executor);

        var exception = await Assert.ThrowsAsync<WorldStockSearchDataException>(
            () => service.SearchAsync("apple"));

        Assert.Contains("WORLD_STOCK_SEARCH", exception.Message, StringComparison.Ordinal);
        Assert.Single(executor.Calls);
    }

    [Fact]
    public async Task SearchAsync_RejectsExtraRowsBeyondTheBoundQueryContract()
    {
        var rows = Enumerable.Range(
                0,
                LegacyWorldStockSearchService.MaximumIsolatedRows + 3)
            .Select(index => ($"Name {index}", index.ToString(), "US"))
            .ToArray();
        var executor = new RecordingExecutor(_ => WorldStockTable(rows));
        var service = new LegacyWorldStockSearchService(executor);

        await Assert.ThrowsAsync<WorldStockSearchDataException>(
            () => service.SearchAsync("o", maximumResults: 1));

        Assert.Single(executor.Calls);
    }

    [Theory]
    [InlineData("Apple\nInc", "AAPL", "US")]
    [InlineData("Apple", "AA\u0000PL", "US")]
    [InlineData("Apple", "AAPL", "KR")]
    [InlineData("Apple", "AAPL", "us")]
    public async Task SearchAsync_RejectsNonCanonicalIdentityValues(
        string inputName,
        string symbol,
        string nationCode)
    {
        var executor = new RecordingExecutor(_ => WorldStockTable(
            (inputName, symbol, nationCode)));
        var service = new LegacyWorldStockSearchService(executor);

        await Assert.ThrowsAsync<WorldStockSearchDataException>(
            () => service.SearchAsync("apple"));
    }

    [Fact]
    public async Task SearchAsync_NormalizesCurrentOuterAsciiPadding()
    {
        var executor = new RecordingExecutor(_ => WorldStockTable(
            (" Apple ", " AAPL ", " US ")));
        var service = new LegacyWorldStockSearchService(executor);

        var result = await service.SearchAsync("apple");

        Assert.Equal(
            new WorldStockSearchItem("Apple", "AAPL", "US"),
            Assert.Single(result.Items));
    }

    [Fact]
    public async Task SearchAsync_IsolatesBlankDisplayRowsWithoutRejectingValidRows()
    {
        var table = WorldStockTable(
            ("Apple", "AAPL", "US"),
            ("Taiwan Semiconductor", "2330", "TW"));
        table.Rows.Add(DBNull.Value, "BLANK1", "US");
        table.Rows.Add("   ", "BLANK2", "TW");
        var executor = new RecordingExecutor(_ => table);
        var service = new LegacyWorldStockSearchService(executor);

        var result = await service.SearchAsync(string.Empty);

        Assert.Equal(2, result.Items.Count);
        Assert.Equal(2, result.IsolatedInvalidRowCount);
        Assert.True(result.IsTruncated);
    }

    [Fact]
    public async Task SearchAsync_RejectsDuplicateCompositeIdentity()
    {
        var executor = new RecordingExecutor(_ => WorldStockTable(
            ("Apple", "AAPL", "US"),
            ("Apple", "AAPL", "US")));
        var service = new LegacyWorldStockSearchService(executor);

        var exception = await Assert.ThrowsAsync<WorldStockSearchDataException>(
            () => service.SearchAsync("apple"));

        Assert.Contains("duplicate identity", exception.Message, StringComparison.Ordinal);
    }

    [Fact]
    public async Task SearchAsync_AllowsDuplicateInputNameWhenExactIdentitiesDiffer()
    {
        var executor = new RecordingExecutor(_ => WorldStockTable(
            ("Shared Name", "SHARED", "US"),
            ("Shared Name", "2330", "TW")));
        var service = new LegacyWorldStockSearchService(executor);

        var result = await service.SearchAsync("shared");

        Assert.Equal(2, result.Items.Count);
        Assert.Equal(["SHARED", "2330"], result.Items.Select(item => item.Symbol));
    }

    [Fact]
    public async Task SearchAsync_AllowsSameSymbolForDistinctInputNameKeys()
    {
        var executor = new RecordingExecutor(_ => WorldStockTable(
            ("First Name", "SHARED", "US"),
            ("Second Name", "SHARED", "TW")));
        var service = new LegacyWorldStockSearchService(executor);

        var result = await service.SearchAsync("name");

        Assert.Equal(2, result.Items.Count);
    }

    [Fact]
    public async Task SearchAsync_PropagatesPreCanceledTokenWithoutDatabaseAccess()
    {
        var executor = new RecordingExecutor(_ => WorldStockTable());
        var service = new LegacyWorldStockSearchService(executor);
        using var cancellation = new CancellationTokenSource();
        cancellation.Cancel();

        await Assert.ThrowsAnyAsync<OperationCanceledException>(
            () => service.SearchAsync("apple", cancellationToken: cancellation.Token));

        Assert.Empty(executor.Calls);
    }

    private static DataTable WorldStockTable(
        params (string InputName, string Symbol, string NationCode)[] rows)
    {
        var table = new DataTable();
        table.Columns.Add("F_INPUT_NAME", typeof(string));
        table.Columns.Add("F_SYMB", typeof(string));
        table.Columns.Add("F_NATC", typeof(string));
        foreach (var row in rows)
        {
            table.Rows.Add(row.InputName, row.Symbol, row.NationCode);
        }

        return table;
    }

    private sealed record RecordedCall(
        DataSourceKind Source,
        string TableName,
        DataQuerySpec Spec,
        CancellationToken CancellationToken);

    private sealed class RecordingExecutor(Func<RecordedCall, DataTable> handler) : IDataQueryExecutor
    {
        private int _stringSqlCallCount;

        public List<RecordedCall> Calls { get; } = [];

        public int StringSqlCallCount => Volatile.Read(ref _stringSqlCallCount);

        public DataTable Execute(DataSourceKind source, string tableName, string query)
        {
            Interlocked.Increment(ref _stringSqlCallCount);
            throw new InvalidOperationException("String SQL fallback is forbidden.");
        }

        public Task<DataTable> ExecuteAsync(
            DataSourceKind source,
            string tableName,
            string query,
            CancellationToken cancellationToken = default)
        {
            Interlocked.Increment(ref _stringSqlCallCount);
            throw new InvalidOperationException("String SQL fallback is forbidden.");
        }

        public Task<DataTable> ExecuteAsync(
            DataSourceKind source,
            string tableName,
            DataQuerySpec query,
            CancellationToken cancellationToken = default)
        {
            cancellationToken.ThrowIfCancellationRequested();
            query.ValidateFor(source);
            var call = new RecordedCall(source, tableName, query, cancellationToken);
            Calls.Add(call);
            return Task.FromResult(handler(call).Copy());
        }
    }
}
