elsa-core/test/unit/Elsa.Diagnostics.StructuredLogs.Persistence.Relational.UnitTests/RelationalStructuredLogSqlBuilderTests.cs

using Elsa.Diagnostics.StructuredLogs.Models;
using Elsa.Diagnostics.StructuredLogs.Persistence.Relational.Contracts;
using Elsa.Diagnostics.StructuredLogs.Persistence.Relational.Services;

namespace Elsa.Diagnostics.StructuredLogs.Persistence.Relational.UnitTests;

public class RelationalStructuredLogSqlBuilderTests
{
    private readonly RelationalStructuredLogSqlBuilder _builder = new(new FakeDialect());

    [Fact]
    public void BuildInsert_UsesDialectQuotingAndParameters()
    {
        var sql = _builder.BuildInsert();

        Assert.StartsWith("INSERT INTO [StructuredLogEvents]", sql, StringComparison.Ordinal);
        Assert.Contains("[TraceId]", sql, StringComparison.Ordinal);
        Assert.Contains("@TraceId", sql, StringComparison.Ordinal);
    }

    [Fact]
    public void BuildQuery_AddsReusableFilterPredicates()
    {
        var query = _builder.BuildQuery(new()
        {
            MinimumLevel = StructuredLogLevel.Warning,
            Levels = [StructuredLogLevel.Warning, StructuredLogLevel.Error],
            CategoryPrefix = "Elsa.Workflow",
            SourceId = "source-a",
            WorkflowDefinitionId = "definition-a",
            WorkflowInstanceId = "instance-a",
            TenantId = "tenant-a",
            CorrelationId = "correlation-a",
            TraceId = "trace-a",
            SpanId = "span-a",
            Text = "needle",
            From = DateTimeOffset.UtcNow.AddMinutes(-5),
            To = DateTimeOffset.UtcNow,
            Take = 42
        });

        Assert.Contains("[Level] >=", query.Sql, StringComparison.Ordinal);
        Assert.Contains("[Level] IN (@Level0, @Level1)", query.Sql, StringComparison.Ordinal);
        Assert.Contains("[Category] LIKE @Category", query.Sql, StringComparison.Ordinal);
        Assert.Contains("[SourceId] = @SourceId", query.Sql, StringComparison.Ordinal);
        Assert.Contains("[WorkflowDefinitionId] = @WorkflowDefinitionId", query.Sql, StringComparison.Ordinal);
        Assert.Contains("[WorkflowInstanceId] = @WorkflowInstanceId", query.Sql, StringComparison.Ordinal);
        Assert.Contains("[TenantId] = @TenantId", query.Sql, StringComparison.Ordinal);
        Assert.Contains("[CorrelationId] = @CorrelationId", query.Sql, StringComparison.Ordinal);
        Assert.Contains("[TraceId] = @TraceId", query.Sql, StringComparison.Ordinal);
        Assert.Contains("[SpanId] = @SpanId", query.Sql, StringComparison.Ordinal);
        foreach (var textColumn in new[] { "Message", "MessageTemplate", "Category", "ExceptionJson", "ScopesJson", "PropertiesJson" })
            Assert.Contains($"[{textColumn}] LIKE @Text", query.Sql, StringComparison.Ordinal);
        Assert.Contains("[Timestamp] >= @TimestampFrom", query.Sql, StringComparison.Ordinal);
        Assert.Contains("[Timestamp] <= @TimestampTo", query.Sql, StringComparison.Ordinal);
        Assert.Contains("FETCH 42", query.Sql, StringComparison.Ordinal);
        Assert.Equal("Elsa.Workflow%", query.Parameters["Category"]);
        Assert.Equal("%needle%", query.Parameters["Text"]);
        Assert.Contains("TimestampFrom", query.Parameters.Keys);
        Assert.Contains("TimestampTo", query.Parameters.Keys);
    }

    [Theory]
    [InlineData(null, "FETCH 100")]
    [InlineData(-5, "FETCH 0")]
    [InlineData(-1, "FETCH 0")]
    [InlineData(2000, "FETCH 1000")]
    [InlineData(5000, "FETCH 1000")]
    public void BuildQuery_ClampsTakeToSupportedRange(int? take, string expectedLimit)
    {
        var query = _builder.BuildQuery(new() { Take = take });

        Assert.Contains(expectedLimit, query.Sql, StringComparison.Ordinal);
    }

    [Fact]
    public void BuildListSources_GroupsBySourceAndOrdersBySource()
    {
        var sql = _builder.BuildListSources();

        Assert.Equal("SELECT [SourceId], MAX([ReceivedAt]) AS [LastSeen] FROM [StructuredLogEvents] GROUP BY [SourceId] ORDER BY [SourceId]", sql);
    }

    [Fact]
    public void BuildDeleteOlderThan_UsesReceivedAtCutoffParameter()
    {
        var query = _builder.BuildDeleteOlderThan("2026-05-13T13:00:00.0000000+00:00");

        Assert.Equal("DELETE FROM [StructuredLogEvents] WHERE [ReceivedAt] < @Cutoff", query.Sql);
        Assert.Equal("2026-05-13T13:00:00.0000000+00:00", query.Parameters["Cutoff"]);
    }

    [Fact]
    public void BuildQuery_AddsTextPredicateAcrossSearchableColumns()
    {
        var query = _builder.BuildQuery(new()
        {
            Text = "failure",
            TenantId = "tenant-a",
            SpanId = "span-a",
            Take = 25
        });

        const string expectedTextPredicate = "([Message] LIKE @Text OR [MessageTemplate] LIKE @Text OR [Category] LIKE @Text OR [ExceptionJson] LIKE @Text OR [ScopesJson] LIKE @Text OR [PropertiesJson] LIKE @Text)";

        Assert.Contains(expectedTextPredicate, query.Sql, StringComparison.Ordinal);
        Assert.Contains("[TenantId] = @TenantId", query.Sql, StringComparison.Ordinal);
        Assert.Contains("[SpanId] = @SpanId", query.Sql, StringComparison.Ordinal);
        Assert.Equal("%failure%", query.Parameters["Text"]);
    }

    [Fact]
    public void BuildDeleteRowsBeyondMax_DelegatesOffsetSyntaxToDialect()
    {
        var query = _builder.BuildDeleteRowsBeyondMax(250);

        Assert.Contains("SKIP 250", query.Sql, StringComparison.Ordinal);
        Assert.DoesNotContain("LIMIT -1", query.Sql, StringComparison.Ordinal);
    }

    private class FakeDialect : IRelationalStructuredLogDialect
    {
        public string ProviderName => "Fake";
        public string ParameterPrefix => "@";
        public string QuoteIdentifier(string identifier) => $"[{identifier}]";
        public string ApplyLimit(string sql, int limit) => $"{sql} FETCH {limit}";
        public string ApplyOffset(string sql, int offset) => $"{sql} SKIP {offset}";
    }
}