#nullable enable

using System.Data;
using MMoneyCoderSharp.Data;

namespace MBN_STOCK_WEBVIEW.Core.Tests;

public sealed class LegacyNxtThemeJoinAuditServiceTests
{
    [Fact]
    public async Task AuditAsync_UsesBoundAggregateReadsAndReturnsNoIdentifiers()
    {
        var executor = new RecordingExecutor(call =>
        {
            var current = Assert.IsType<string>(call.Spec.Parameters[0].Value);
            return current == "90000001"
                ? Table("3", "0", "2", "2", "1", "0", "0", "0", "0", "0", "0", "3",
                    "2", "0", "1", "0")
                : Table("4", "1", "0", "0", "0", "1", "1", "1", "0", "0", "0", "4",
                    "0", "0", "0", "0");
        });
        var service = new LegacyNxtThemeJoinAuditService(
            executor,
            new FixedTimeProvider(new DateTimeOffset(2026, 7, 12, 3, 4, 5, TimeSpan.Zero)));

        var result = await service.AuditAsync(
        [
            new LegacyNxtThemeJoinAuditIdentity("90000001", "111"),
            new LegacyNxtThemeJoinAuditIdentity("90000002", "222")
        ]);

        Assert.Equal(new DateTimeOffset(2026, 7, 12, 3, 4, 5, TimeSpan.Zero), result.RetrievedAt);
        Assert.Equal(2, result.Themes.Count);
        Assert.Equal(0, result.Themes[0].InputIndex);
        Assert.Equal(3, result.Themes[0].RawCurrentItemCount);
        Assert.Equal(2, result.Themes[0].CurrentOriginalJoinCount);
        Assert.Equal(1, result.Themes[0].CurrentLiveJoinCount);
        Assert.Equal(2, result.Themes[0].CurrentKospiMasterJoinCount);
        Assert.Equal(1, result.Themes[0].CurrentKospiOnlineJoinCount);
        Assert.Equal(1, result.Themes[1].InputIndex);
        Assert.Equal(4, result.Themes[1].RawCurrentItemCount);
        Assert.Equal(1, result.Themes[1].RawStoredItemCount);
        Assert.Equal(1, result.Themes[1].StoredLiveJoinCount);
        Assert.Equal(2, executor.Calls.Count);
        Assert.Equal(0, executor.StringSqlCallCount);

        Assert.All(executor.Calls, call =>
        {
            Assert.Equal(DataSourceKind.MariaDb, call.Source);
            Assert.Equal(LegacyNxtThemeJoinAuditService.QueryName, call.QueryName);
            call.Spec.ValidateFor(DataSourceKind.MariaDb);
            Assert.Contains(
                "SUBSTRING(i.SB_I_CODE, 2, 12) = q.F_STOCK_CODE",
                call.Spec.Sql,
                StringComparison.Ordinal);
            Assert.Contains("RIGHT(i.SB_I_CODE, 6)", call.Spec.Sql, StringComparison.Ordinal);
            Assert.Contains("FROM N_STOCK q", call.Spec.Sql, StringComparison.Ordinal);
            Assert.Contains("FROM N_KOSDAQ_ONLINE q", call.Spec.Sql, StringComparison.Ordinal);
            Assert.Contains("AND EXISTS (", call.Spec.Sql, StringComparison.Ordinal);
            Assert.DoesNotContain(
                Assert.IsType<string>(call.Spec.Parameters[0].Value),
                call.Spec.Sql,
                StringComparison.Ordinal);
            Assert.Collection(
                call.Spec.Parameters,
                current =>
                {
                    Assert.Equal("current_theme_code", current.Name);
                    Assert.Equal(DbType.String, current.DbType);
                },
                stored =>
                {
                    Assert.Equal("stored_theme_code", stored.Name);
                    Assert.Equal(DbType.String, stored.DbType);
                });
        });
    }

    [Theory]
    [MemberData(nameof(InvalidIdentities))]
    public async Task AuditAsync_RejectsInvalidOrDuplicateIdentitiesBeforeDatabaseAccess(
        IReadOnlyList<LegacyNxtThemeJoinAuditIdentity> identities)
    {
        var executor = new RecordingExecutor(_ => throw new InvalidOperationException());
        var service = new LegacyNxtThemeJoinAuditService(executor);

        await Assert.ThrowsAnyAsync<ArgumentException>(() => service.AuditAsync(identities));

        Assert.Empty(executor.Calls);
        Assert.Equal(0, executor.StringSqlCallCount);
    }

    public static TheoryData<IReadOnlyList<LegacyNxtThemeJoinAuditIdentity>> InvalidIdentities =>
        new()
        {
            Array.Empty<LegacyNxtThemeJoinAuditIdentity>(),
            new LegacyNxtThemeJoinAuditIdentity[] { new("12", "123") },
            new LegacyNxtThemeJoinAuditIdentity[] { new("123", "A123") },
            new LegacyNxtThemeJoinAuditIdentity[]
            {
                new("123", "111"),
                new("123", "222")
            }
        };

    [Fact]
    public async Task AuditAsync_FailsClosedOnWrongSchemaNonCanonicalCountOrInconsistentSubset()
    {
        var tables = new Queue<DataTable>(
        [
            new DataTable(),
            Table("01", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0",
                "0", "0", "0", "0"),
            Table("1", "0", "1", "2", "0", "0", "0", "0", "0", "0", "0", "1",
                "0", "0", "0", "0"),
            Table("1", "0", "2", "0", "0", "0", "0", "0", "0", "0", "0", "1",
                "0", "0", "0", "0"),
            Table("1", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "1",
                "2", "0", "0", "0"),
            Table("2", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "2",
                "1", "2", "0", "0")
        ]);
        var service = new LegacyNxtThemeJoinAuditService(
            new RecordingExecutor(_ => tables.Dequeue()));
        var identity = new[] { new LegacyNxtThemeJoinAuditIdentity("123", "456") };

        await Assert.ThrowsAsync<InvalidDataException>(() => service.AuditAsync(identity));
        await Assert.ThrowsAsync<InvalidDataException>(() => service.AuditAsync(identity));
        await Assert.ThrowsAsync<InvalidDataException>(() => service.AuditAsync(identity));
        await Assert.ThrowsAsync<InvalidDataException>(() => service.AuditAsync(identity));
        await Assert.ThrowsAsync<InvalidDataException>(() => service.AuditAsync(identity));
        await Assert.ThrowsAsync<InvalidDataException>(() => service.AuditAsync(identity));
    }

    [Fact]
    public async Task AuditAsync_HonorsPreCanceledTokenWithoutDatabaseAccess()
    {
        var executor = new RecordingExecutor(_ => throw new InvalidOperationException());
        using var cancellation = new CancellationTokenSource();
        cancellation.Cancel();

        await Assert.ThrowsAnyAsync<OperationCanceledException>(() =>
            new LegacyNxtThemeJoinAuditService(executor).AuditAsync(
                [new("123", "456")],
                cancellation.Token));

        Assert.Empty(executor.Calls);
    }

    private static DataTable Table(params string[] counts)
    {
        Assert.Equal(16, counts.Length);
        var table = new DataTable();
        table.Columns.Add("RAW_CURRENT_ITEM_COUNT", typeof(string));
        table.Columns.Add("RAW_STORED_ITEM_COUNT", typeof(string));
        table.Columns.Add("CURRENT_ORIGINAL_JOIN_COUNT", typeof(string));
        table.Columns.Add("CURRENT_NON_STOPPED_JOIN_COUNT", typeof(string));
        table.Columns.Add("CURRENT_LIVE_JOIN_COUNT", typeof(string));
        table.Columns.Add("STORED_ORIGINAL_JOIN_COUNT", typeof(string));
        table.Columns.Add("STORED_NON_STOPPED_JOIN_COUNT", typeof(string));
        table.Columns.Add("STORED_LIVE_JOIN_COUNT", typeof(string));
        table.Columns.Add("CURRENT_EXACT_JOIN_COUNT", typeof(string));
        table.Columns.Add("CURRENT_STRIP_BOTH_JOIN_COUNT", typeof(string));
        table.Columns.Add("CURRENT_RIGHT_SIX_JOIN_COUNT", typeof(string));
        table.Columns.Add("CURRENT_VALID_ITEM_CODE_COUNT", typeof(string));
        table.Columns.Add("CURRENT_KOSPI_MASTER_JOIN_COUNT", typeof(string));
        table.Columns.Add("CURRENT_KOSDAQ_MASTER_JOIN_COUNT", typeof(string));
        table.Columns.Add("CURRENT_KOSPI_ONLINE_JOIN_COUNT", typeof(string));
        table.Columns.Add("CURRENT_KOSDAQ_ONLINE_JOIN_COUNT", typeof(string));
        table.Rows.Add(counts.Cast<object>().ToArray());
        return table;
    }

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

    private sealed class RecordingExecutor(Func<RecordedCall, DataTable> handler)
        : IDataQueryExecutor
    {
        public List<RecordedCall> Calls { get; } = [];

        public int StringSqlCallCount { get; private set; }

        public DataTable Execute(DataSourceKind source, string tableName, string query)
        {
            StringSqlCallCount++;
            throw new InvalidOperationException("String SQL must not be used.");
        }

        public Task<DataTable> ExecuteAsync(
            DataSourceKind source,
            string tableName,
            string sql,
            CancellationToken cancellationToken = default)
        {
            StringSqlCallCount++;
            throw new InvalidOperationException("String SQL must not be used.");
        }

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

    private sealed class FixedTimeProvider(DateTimeOffset value) : TimeProvider
    {
        public override DateTimeOffset GetUtcNow() => value.ToUniversalTime();

        public override TimeZoneInfo LocalTimeZone => TimeZoneInfo.Utc;
    }
}
