using Dapper;
using JdeScoping.Core.Models.Search;
using JdeScoping.Database.Tests.Infrastructure;
using Shouldly;

namespace JdeScoping.Database.Tests.Functions;

/// <summary>
/// Tests for scalar extraction functions: fn_GetSearchMinimumDt, fn_GetSearchMaximumDt, fn_GetSearchExtractMisData.
/// These functions extract simple values from Search.Criteria JSON.
/// </summary>
[Collection("DatabaseTests")]
public class ScalarFunctionTests : DatabaseTestBase
{
    #region fn_GetSearchMinimumDt Tests

    [Fact]
    public async Task fn_GetSearchMinimumDt_ValidSearch_ReturnsDateTime()
    {
        // Arrange
        var expectedDate = new DateTime(2024, 6, 15, 10, 30, 0);
        var criteria = new SearchCriteria { MinimumDt = expectedDate };
        var searchId = await InsertTestSearchAsync(criteria);

        // Act
        var result = await Connection.QuerySingleOrDefaultAsync<DateTime?>(
            "SELECT dbo.fn_GetSearchMinimumDt(@SearchId)",
            new { SearchId = searchId });

        // Assert
        result.ShouldBeCloseTo(expectedDate, TimeSpan.FromSeconds(1));
    }

    [Fact]
    public async Task fn_GetSearchMinimumDt_MissingProperty_ReturnsNull()
    {
        // Arrange - criteria with no MinimumDt (defaults to null)
        var criteria = new SearchCriteria { MinimumDt = null };
        var searchId = await InsertTestSearchAsync(criteria);

        // Act
        var result = await Connection.QuerySingleOrDefaultAsync<DateTime?>(
            "SELECT dbo.fn_GetSearchMinimumDt(@SearchId)",
            new { SearchId = searchId });

        // Assert
        result.ShouldBeNull();
    }

    [Fact]
    public async Task fn_GetSearchMinimumDt_SearchNotFound_ReturnsNull()
    {
        // Act
        var result = await Connection.QuerySingleOrDefaultAsync<DateTime?>(
            "SELECT dbo.fn_GetSearchMinimumDt(@SearchId)",
            new { SearchId = 99999 });

        // Assert
        result.ShouldBeNull();
    }

    [Fact]
    public async Task fn_GetSearchMinimumDt_NullCriteria_ReturnsNull()
    {
        // Arrange
        var searchId = await InsertTestSearchWithRawCriteriaAsync(null);

        // Act
        var result = await Connection.QuerySingleOrDefaultAsync<DateTime?>(
            "SELECT dbo.fn_GetSearchMinimumDt(@SearchId)",
            new { SearchId = searchId });

        // Assert
        result.ShouldBeNull();
    }

    [Fact]
    public async Task fn_GetSearchMinimumDt_InvalidJson_ReturnsNull()
    {
        // Arrange
        var searchId = await InsertTestSearchWithRawCriteriaAsync("not valid json");

        // Act
        var result = await Connection.QuerySingleOrDefaultAsync<DateTime?>(
            "SELECT dbo.fn_GetSearchMinimumDt(@SearchId)",
            new { SearchId = searchId });

        // Assert
        result.ShouldBeNull();
    }

    #endregion

    #region fn_GetSearchMaximumDt Tests

    [Fact]
    public async Task fn_GetSearchMaximumDt_ValidSearch_ReturnsDateTime()
    {
        // Arrange
        var expectedDate = new DateTime(2024, 12, 31, 23, 59, 59);
        var criteria = new SearchCriteria { MaximumDt = expectedDate };
        var searchId = await InsertTestSearchAsync(criteria);

        // Act
        var result = await Connection.QuerySingleOrDefaultAsync<DateTime?>(
            "SELECT dbo.fn_GetSearchMaximumDt(@SearchId)",
            new { SearchId = searchId });

        // Assert
        result.ShouldBeCloseTo(expectedDate, TimeSpan.FromSeconds(1));
    }

    [Fact]
    public async Task fn_GetSearchMaximumDt_MissingProperty_ReturnsNull()
    {
        // Arrange - criteria with no MaximumDt (defaults to null)
        var criteria = new SearchCriteria { MaximumDt = null };
        var searchId = await InsertTestSearchAsync(criteria);

        // Act
        var result = await Connection.QuerySingleOrDefaultAsync<DateTime?>(
            "SELECT dbo.fn_GetSearchMaximumDt(@SearchId)",
            new { SearchId = searchId });

        // Assert
        result.ShouldBeNull();
    }

    [Fact]
    public async Task fn_GetSearchMaximumDt_SearchNotFound_ReturnsNull()
    {
        // Act
        var result = await Connection.QuerySingleOrDefaultAsync<DateTime?>(
            "SELECT dbo.fn_GetSearchMaximumDt(@SearchId)",
            new { SearchId = 99999 });

        // Assert
        result.ShouldBeNull();
    }

    [Fact]
    public async Task fn_GetSearchMaximumDt_NullCriteria_ReturnsNull()
    {
        // Arrange
        var searchId = await InsertTestSearchWithRawCriteriaAsync(null);

        // Act
        var result = await Connection.QuerySingleOrDefaultAsync<DateTime?>(
            "SELECT dbo.fn_GetSearchMaximumDt(@SearchId)",
            new { SearchId = searchId });

        // Assert
        result.ShouldBeNull();
    }

    [Fact]
    public async Task fn_GetSearchMaximumDt_InvalidJson_ReturnsNull()
    {
        // Arrange
        var searchId = await InsertTestSearchWithRawCriteriaAsync("not valid json");

        // Act
        var result = await Connection.QuerySingleOrDefaultAsync<DateTime?>(
            "SELECT dbo.fn_GetSearchMaximumDt(@SearchId)",
            new { SearchId = searchId });

        // Assert
        result.ShouldBeNull();
    }

    #endregion

    #region fn_GetSearchExtractMisData Tests

    [Fact]
    public async Task fn_GetSearchExtractMisData_True_ReturnsTrue()
    {
        // Arrange
        var criteria = new SearchCriteria { ExtractMisData = true };
        var searchId = await InsertTestSearchAsync(criteria);

        // Act
        var result = await Connection.QuerySingleOrDefaultAsync<bool?>(
            "SELECT dbo.fn_GetSearchExtractMisData(@SearchId)",
            new { SearchId = searchId });

        // Assert
        result.ShouldNotBeNull();
        result.Value.ShouldBeTrue();
    }

    [Fact]
    public async Task fn_GetSearchExtractMisData_False_ReturnsFalse()
    {
        // Arrange
        var criteria = new SearchCriteria { ExtractMisData = false };
        var searchId = await InsertTestSearchAsync(criteria);

        // Act
        var result = await Connection.QuerySingleOrDefaultAsync<bool?>(
            "SELECT dbo.fn_GetSearchExtractMisData(@SearchId)",
            new { SearchId = searchId });

        // Assert
        result.ShouldNotBeNull();
        result.Value.ShouldBeFalse();
    }

    [Fact]
    public async Task fn_GetSearchExtractMisData_SearchNotFound_ReturnsNull()
    {
        // Act
        var result = await Connection.QuerySingleOrDefaultAsync<bool?>(
            "SELECT dbo.fn_GetSearchExtractMisData(@SearchId)",
            new { SearchId = 99999 });

        // Assert
        result.ShouldBeNull();
    }

    [Fact]
    public async Task fn_GetSearchExtractMisData_NullCriteria_ReturnsNull()
    {
        // Arrange
        var searchId = await InsertTestSearchWithRawCriteriaAsync(null);

        // Act
        var result = await Connection.QuerySingleOrDefaultAsync<bool?>(
            "SELECT dbo.fn_GetSearchExtractMisData(@SearchId)",
            new { SearchId = searchId });

        // Assert
        result.ShouldBeNull();
    }

    [Fact]
    public async Task fn_GetSearchExtractMisData_InvalidJson_ReturnsNull()
    {
        // Arrange
        var searchId = await InsertTestSearchWithRawCriteriaAsync("not valid json");

        // Act
        var result = await Connection.QuerySingleOrDefaultAsync<bool?>(
            "SELECT dbo.fn_GetSearchExtractMisData(@SearchId)",
            new { SearchId = searchId });

        // Assert
        result.ShouldBeNull();
    }

    #endregion
}