jdescopingtool/NEW/tests/JdeScoping.Database.Tests/Procedures/ValidateSearchCriteriaProcedureTests.cs

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

namespace JdeScoping.Database.Tests.Procedures;

/// <summary>
/// Tests for usp_ValidateSearchCriteria stored procedure.
/// Validates that the procedure throws appropriate errors for invalid search criteria.
/// </summary>
[Collection("DatabaseTests")]
public class ValidateSearchCriteriaProcedureTests : DatabaseTestBase
{
    [Fact]
    public async Task usp_ValidateSearchCriteria_ValidSearch_Succeeds()
    {
        // Arrange
        var criteria = new SearchCriteria { WorkOrderNumbers = [12345] };
        var searchId = await InsertTestSearchAsync(criteria);

        // Act
        var act = async () => await Connection.ExecuteAsync(
            "EXEC dbo.usp_ValidateSearchCriteria @SearchId",
            new { SearchId = searchId });

        // Assert
        await Should.NotThrowAsync(act);
    }

    [Fact]
    public async Task usp_ValidateSearchCriteria_SearchNotFound_ThrowsError50001()
    {
        // Act
        var act = async () => await Connection.ExecuteAsync(
            "EXEC dbo.usp_ValidateSearchCriteria @SearchId",
            new { SearchId = 99999 });

        // Assert
        var ex = await Should.ThrowAsync<SqlException>(act);
        ex.Number.ShouldBe(50001);
        ex.Message.ShouldContain("Search ID 99999 not found");
    }

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

        // Act
        var act = async () => await Connection.ExecuteAsync(
            "EXEC dbo.usp_ValidateSearchCriteria @SearchId",
            new { SearchId = searchId });

        // Assert
        var ex = await Should.ThrowAsync<SqlException>(act);
        ex.Number.ShouldBe(50002);
        ex.Message.ShouldContain("has no criteria");
    }

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

        // Act
        var act = async () => await Connection.ExecuteAsync(
            "EXEC dbo.usp_ValidateSearchCriteria @SearchId",
            new { SearchId = searchId });

        // Assert
        var ex = await Should.ThrowAsync<SqlException>(act);
        ex.Number.ShouldBe(50002);
        ex.Message.ShouldContain("has no criteria");
    }

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

        // Act
        var act = async () => await Connection.ExecuteAsync(
            "EXEC dbo.usp_ValidateSearchCriteria @SearchId",
            new { SearchId = searchId });

        // Assert
        var ex = await Should.ThrowAsync<SqlException>(act);
        ex.Number.ShouldBe(50003);
        ex.Message.ShouldContain("has invalid JSON");
    }

    [Fact]
    public async Task usp_ValidateSearchCriteria_EmptyJsonObject_Succeeds()
    {
        // Arrange - empty JSON object is valid
        var searchId = await InsertTestSearchWithRawCriteriaAsync("{}");

        // Act
        var act = async () => await Connection.ExecuteAsync(
            "EXEC dbo.usp_ValidateSearchCriteria @SearchId",
            new { SearchId = searchId });

        // Assert
        await Should.NotThrowAsync(act);
    }
}