using JdeScoping.DataAccess.Interfaces;
using JdeScoping.DataAccess.QueryBuilders;
using Shouldly;
using SqlKata.Compilers;
using Xunit;

namespace JdeScoping.DataAccess.Tests.QueryBuilders;

/// <summary>
/// Unit tests for SqlKataSearchQueryBuilder.
/// </summary>
public sealed class SqlKataSearchQueryBuilderTests
{
    private readonly SqlServerCompiler _compiler = new();

    [Fact]
    public void BuildSearchQuery_WithSearchId_ProducesValidQuery()
    {
        // Arrange
        var builder = new SqlKataSearchQueryBuilder(_compiler);
        var searchId = 123;

        // Act
        var result = builder.BuildSearchQuery(searchId);

        // Assert
        result.ShouldNotBeNull();
        result.Sql.ShouldNotBeNullOrEmpty();
        result.TempTableSetupSql.ShouldNotBeEmpty();
        result.Parameters.ShouldContainKey("SearchId");
        result.Parameters["SearchId"].ShouldBe(searchId);
    }

    [Fact]
    public void BuildSearchQuery_ContainsTempTableCreation()
    {
        // Arrange
        var builder = new SqlKataSearchQueryBuilder(_compiler);

        // Act
        var result = builder.BuildSearchQuery(1);

        // Assert
        var setupSql = string.Join("\n", result.TempTableSetupSql);
        setupSql.ShouldContain("#Temp_WO");
        setupSql.ShouldContain("CREATE TABLE");
    }

    [Fact]
    public void BuildSearchQuery_ContainsValidationCall()
    {
        // Arrange
        var builder = new SqlKataSearchQueryBuilder(_compiler);

        // Act
        var result = builder.BuildSearchQuery(1);

        // Assert
        var setupSql = string.Join("\n", result.TempTableSetupSql);
        setupSql.ShouldContain("usp_ValidateSearchCriteria");
        setupSql.ShouldContain("@SearchId");
    }

    [Fact]
    public void BuildSearchQuery_UsesExtractionFunctions()
    {
        // Arrange
        var builder = new SqlKataSearchQueryBuilder(_compiler);

        // Act
        var result = builder.BuildSearchQuery(1);

        // Assert
        var setupSql = string.Join("\n", result.TempTableSetupSql);

        // Should use extraction functions instead of TVPs
        setupSql.ShouldContain("fn_GetSearchWorkOrders(@SearchId)");
        setupSql.ShouldContain("fn_GetSearchItemNumbers(@SearchId)");
        setupSql.ShouldContain("fn_GetSearchProfitCenters(@SearchId)");
        setupSql.ShouldContain("fn_GetSearchWorkCenters(@SearchId)");
        setupSql.ShouldContain("fn_GetSearchOperatorIDs(@SearchId)");
        setupSql.ShouldContain("fn_GetSearchComponentLots(@SearchId)");
        setupSql.ShouldContain("fn_GetSearchPartOperations(@SearchId)");
        setupSql.ShouldContain("fn_GetSearchMinimumDt(@SearchId)");
        setupSql.ShouldContain("fn_GetSearchMaximumDt(@SearchId)");
    }

    [Fact]
    public void BuildSearchQuery_CreatesFilterTempTables()
    {
        // Arrange
        var builder = new SqlKataSearchQueryBuilder(_compiler);

        // Act
        var result = builder.BuildSearchQuery(1);

        // Assert
        var setupSql = string.Join("\n", result.TempTableSetupSql);

        // Should create filter temp tables
        setupSql.ShouldContain("#P_WorkOrders");
        setupSql.ShouldContain("#P_ItemNumbers");
        setupSql.ShouldContain("#P_WorkCenters");
        setupSql.ShouldContain("#P_OperatorIDs");
        setupSql.ShouldContain("#P_ComponentLots");
        setupSql.ShouldContain("#P_PartOperations");
    }

    [Fact]
    public void BuildSearchQuery_ResultSqlContainsSelect()
    {
        // Arrange
        var builder = new SqlKataSearchQueryBuilder(_compiler);

        // Act
        var result = builder.BuildSearchQuery(1);

        // Assert
        result.Sql.ShouldContain("SELECT");
        result.Sql.ShouldContain("WorkOrderNumber");
    }

    [Fact]
    public void BuildSearchQuery_ContainsStepFlaggingQuery()
    {
        // Arrange
        var builder = new SqlKataSearchQueryBuilder(_compiler);

        // Act
        var result = builder.BuildSearchQuery(1);

        // Assert
        var setupSql = string.Join("\n", result.TempTableSetupSql);
        setupSql.ShouldContain("LU_WO");
        setupSql.ShouldContain("Flagged");
        setupSql.ShouldContain("MERGE");
    }

    [Fact]
    public void BuildMisQuery_ReturnsValidResult()
    {
        // Arrange
        var builder = new SqlKataSearchQueryBuilder(_compiler);

        // Act
        var result = builder.BuildMisQuery(1);

        // Assert
        result.ShouldNotBeNull();
        result.Sql.ShouldNotBeNullOrEmpty();
        result.Sql.ShouldContain("#TempMisData");
        result.Parameters.ShouldContainKey("SearchId");
    }

    [Fact]
    public void BuildMisNonMatchQuery_ReturnsValidResult()
    {
        // Arrange
        var builder = new SqlKataSearchQueryBuilder(_compiler);

        // Act
        var result = builder.BuildMisNonMatchQuery(1);

        // Assert
        result.ShouldNotBeNull();
        result.Sql.ShouldNotBeNullOrEmpty();
        result.Sql.ShouldContain("WasJobStepAdded");
        result.Sql.ShouldContain("MatchedJobStepNumber");
        result.Parameters.ShouldContainKey("SearchId");
    }

    [Fact]
    public void BuildSearchQuery_DifferentSearchIds_ProduceDifferentParameters()
    {
        // Arrange
        var builder = new SqlKataSearchQueryBuilder(_compiler);

        // Act
        var result1 = builder.BuildSearchQuery(100);
        var result2 = builder.BuildSearchQuery(200);

        // Assert
        result1.Parameters["SearchId"].ShouldBe(100);
        result2.Parameters["SearchId"].ShouldBe(200);
    }
}