using System.Data;
using MMoneyCoderSharp.Data;

namespace MBN_STOCK_WEBVIEW.Core.Tests;

public sealed class LegacyOverseasIndustryIndexSearchServiceTests
{
    [Fact]
    public async Task SearchAsync_UsesBoundUsIndexMasterQueryAndMapsAllLoaderKeys()
    {
        var executor = new RecordingExecutor(_ => ResultTable(
            ("Philadelphia Semiconductor", "US_SEMICONDUCTOR", "SOX"),
            ("Dow Transportation", "US_DOW_TRANSPORT", "DJT")));
        var service = new LegacyOverseasIndustryIndexSearchService(executor);
        using var cancellation = new CancellationTokenSource();

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

        Assert.Equal("semiconductor", result.Query);
        Assert.False(result.IsTruncated);
        Assert.Collection(
            result.Items,
            item => Assert.Equal(
                new OverseasIndustryIndexItem(
                    "Philadelphia Semiconductor",
                    "US_SEMICONDUCTOR",
                    "SOX"),
                item),
            item => Assert.Equal(
                new OverseasIndustryIndexItem(
                    "Dow Transportation",
                    "US_DOW_TRANSPORT",
                    "DJT"),
                item));

        var call = Assert.Single(executor.Calls);
        Assert.Equal(DataSourceKind.Oracle, call.Source);
        Assert.Equal("OVERSEAS_INDUSTRY_INDEX_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 = '0'", call.Spec.Sql, StringComparison.Ordinal);
        Assert.Contains("F_NATC = 'US'", call.Spec.Sql, StringComparison.Ordinal);
        Assert.DoesNotContain("COUNT(*) OVER", call.Spec.Sql, StringComparison.Ordinal);
        Assert.DoesNotContain("KNAM_COUNT", call.Spec.Sql, StringComparison.Ordinal);
        Assert.DoesNotContain("INPUT_NAME_COUNT", call.Spec.Sql, StringComparison.Ordinal);
        Assert.DoesNotContain("SYMBOL_COUNT", call.Spec.Sql, StringComparison.Ordinal);
        Assert.Contains("UPPER(F_KNAM)", 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("semiconductor", call.Spec.Sql, StringComparison.OrdinalIgnoreCase);
        Assert.Collection(
            call.Spec.Parameters,
            parameter =>
            {
                Assert.Equal("search_term", parameter.Name);
                Assert.Equal("SEMICONDUCTOR", parameter.Value);
                Assert.Equal(DbType.String, parameter.DbType);
            },
            parameter =>
            {
                Assert.Equal("row_limit", parameter.Name);
                Assert.Equal(26, parameter.Value);
                Assert.Equal(DbType.Int32, parameter.DbType);
            });
        Assert.Equal(0, executor.StringSqlCallCount);
    }

    [Theory]
    [InlineData("")]
    [InlineData("   ")]
    public async Task SearchAsync_AllowsOriginalUc5BlankInitialList(string query)
    {
        var executor = new RecordingExecutor(_ => ResultTable());
        var service = new LegacyOverseasIndustryIndexSearchService(executor);

        var result = await service.SearchAsync(query);

        Assert.Equal(string.Empty, result.Query);
        var call = Assert.Single(executor.Calls);
        Assert.Equal(string.Empty, call.Spec.Parameters[0].Value);
    }

    [Fact]
    public async Task SearchAsync_InitialConsonantsFilterTheFullBoundedUsCandidateWindow()
    {
        var executor = new RecordingExecutor(_ => ResultTable(
            ("필라델피아 반도체", "US_SEMICONDUCTOR", "SOX"),
            ("다우 운송", "US_DOW_TRANSPORT", "DJT"),
            ("필라델피아 은행", "US_BANK", "BKX")));
        var service = new LegacyOverseasIndustryIndexSearchService(executor);

        var result = await service.SearchAsync(" ㅍㄹㄷㅍㅇ ", maximumResults: 1);

        Assert.Equal("ㅍㄹㄷㅍㅇ", result.Query);
        Assert.True(result.IsTruncated);
        Assert.Equal(
            "필라델피아 반도체",
            Assert.Single(result.Items).KoreanName);
        var call = Assert.Single(executor.Calls);
        Assert.Contains("F_NATC = 'US'", call.Spec.Sql, StringComparison.Ordinal);
        Assert.Equal(string.Empty, call.Spec.Parameters[0].Value);
        Assert.Equal(
            LegacyOverseasIndustryIndexSearchService.MaximumResults + 1,
            call.Spec.Parameters[1].Value);
    }

    [Fact]
    public async Task SearchAsync_EscapesLikeMetacharactersOnlyInsideBoundValue()
    {
        var executor = new RecordingExecutor(_ => ResultTable());
        var service = new LegacyOverseasIndustryIndexSearchService(executor);

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

        var call = Assert.Single(executor.Calls);
        Assert.Equal("A!!!_!%B", call.Spec.Parameters[0].Value);
        Assert.DoesNotContain("A!!!_!%B", call.Spec.Sql, StringComparison.Ordinal);
    }

    [Fact]
    public async Task SearchAsync_ReturnsBoundedRowsAndReportsTruncation()
    {
        var executor = new RecordingExecutor(_ => ResultTable(
            ("Alpha", "ALPHA", "A"),
            ("Beta", "BETA", "B"),
            ("Gamma", "GAMMA", "C")));
        var service = new LegacyOverseasIndustryIndexSearchService(executor);

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

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

    [Theory]
    [InlineData(0)]
    [InlineData(-1)]
    [InlineData(2001)]
    [InlineData(int.MaxValue)]
    public async Task SearchAsync_RejectsInvalidMaximumBeforeDatabaseAccess(int maximumResults)
    {
        var executor = new RecordingExecutor(_ => ResultTable());
        var service = new LegacyOverseasIndustryIndexSearchService(executor);

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

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

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

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

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

    [Fact]
    public async Task SearchAsync_RejectsNullAndOverlongQueryBeforeDatabaseAccess()
    {
        var executor = new RecordingExecutor(_ => ResultTable());
        var service = new LegacyOverseasIndustryIndexSearchService(executor);

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

        Assert.Empty(executor.Calls);
    }

    [Fact]
    public async Task SearchAsync_RejectsAnythingOtherThanExactOrderedStringSchema()
    {
        var wrongCase = ResultTable();
        wrongCase.Columns[0].ColumnName = "f_knam";
        var executor = new RecordingExecutor(_ => wrongCase);
        var service = new LegacyOverseasIndustryIndexSearchService(executor);

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

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

    [Fact]
    public async Task SearchAsync_RejectsProviderRowsBeyondBoundQueryContract()
    {
        var executor = new RecordingExecutor(_ => ResultTable(
            ("One", "ONE", "1"),
            ("Two", "TWO", "2"),
            ("Three", "THREE", "3")));
        var service = new LegacyOverseasIndustryIndexSearchService(executor);

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

    [Theory]
    [InlineData(" Leading", "INPUT", "SYM")]
    [InlineData("Name", "INPUT ", "SYM")]
    [InlineData("Name", "INPUT", " SYM")]
    [InlineData("Name\nBad", "INPUT", "SYM")]
    [InlineData("Name", "IN\u0000PUT", "SYM")]
    [InlineData("Name", "INPUT", "SY\u200BM")]
    public async Task SearchAsync_RejectsNonCanonicalDatabaseValues(
        string koreanName,
        string inputName,
        string symbol)
    {
        var executor = new RecordingExecutor(_ => ResultTable(
            (koreanName, inputName, symbol)));
        var service = new LegacyOverseasIndustryIndexSearchService(executor);

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

    [Theory]
    [InlineData("Shared", "ONE", "1", "Shared", "TWO", "2")]
    [InlineData("One", "SHARED", "1", "Two", "SHARED", "2")]
    [InlineData("One", "ONE", "SHARED", "Two", "TWO", "SHARED")]
    public async Task SearchAsync_PreservesRowsThatShareOnlyOneDisplayOrLookupField(
        string koreanName1,
        string inputName1,
        string symbol1,
        string koreanName2,
        string inputName2,
        string symbol2)
    {
        var executor = new RecordingExecutor(_ => ResultTable(
            (koreanName1, inputName1, symbol1),
            (koreanName2, inputName2, symbol2)));
        var service = new LegacyOverseasIndustryIndexSearchService(executor);

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

        Assert.Equal(2, result.Items.Count);
        Assert.NotEqual(result.Items[0], result.Items[1]);
    }

    [Fact]
    public async Task SearchAsync_RejectsOnlyDuplicateExactIdentity()
    {
        var executor = new RecordingExecutor(_ => ResultTable(
            ("One", "ONE", "1"),
            ("One", "ONE", "1")));

        var exception = await Assert.ThrowsAsync<OverseasIndustryIndexSearchDataException>(() =>
            new LegacyOverseasIndustryIndexSearchService(executor).SearchAsync(""));

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

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

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

        Assert.Empty(executor.Calls);
    }

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

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