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

using Elsa.Diagnostics.StructuredLogs.Models;
using Elsa.Diagnostics.StructuredLogs.Options;
using Elsa.Diagnostics.StructuredLogs.Persistence.Relational.Contracts;
using Elsa.Diagnostics.StructuredLogs.Persistence.Relational.Services;
using MicrosoftOptions = Microsoft.Extensions.Options.Options;

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

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

    [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 1000")]
    [InlineData(-5, "FETCH 0")]
    [InlineData(-1, "FETCH 0")]
    [InlineData(2000, "FETCH 1000")]
    [InlineData(5000, "FETCH 1000")]
    public void BuildQuery_ClampsTakeToMaxRecentLogQuerySize(int? take, string expectedLimit)
    {
        var query = _builder.BuildQuery(new() { Take = take });

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

    [Theory]
    [InlineData(null, "FETCH 50")]
    [InlineData(25, "FETCH 25")]
    [InlineData(200, "FETCH 50")]
    public void BuildQuery_UsesConfiguredMaxRecentLogQuerySize(int? take, string expectedLimit)
    {
        var builder = CreateBuilder(maxRecentLogQuerySize: 50);
        var query = builder.BuildQuery(new() { Take = take });

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

    [Theory]
    [InlineData(null)]
    [InlineData(-5)]
    [InlineData(25)]
    public void BuildQuery_WhenMaxRecentLogQuerySizeIsNegative_UsesZeroLimit(int? take)
    {
        var builder = CreateBuilder(maxRecentLogQuerySize: -10);
        var query = builder.BuildQuery(new() { Take = take });

        Assert.Contains("FETCH 0", query.Sql, StringComparison.Ordinal);
    }

    [Fact]
    public void BuildQuery_OrdersByTimestampReceivedAtSourceIdSequenceAndIdDescending()
    {
        var query = _builder.BuildQuery(new());

        Assert.Contains(
            "ORDER BY [Timestamp] DESC, [ReceivedAt] DESC, [SourceId] DESC, [Sequence] DESC, [Id] DESC",
            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 static RelationalStructuredLogSqlBuilder CreateBuilder(int? maxRecentLogQuerySize = null)
    {
        var options = new StructuredLogsOptions();
        if (maxRecentLogQuerySize is { } maxTake)
            options.MaxRecentLogQuerySize = maxTake;

        return new(new FakeDialect(), MicrosoftOptions.Create(options));
    }

    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}";
    }
}