sqlinsight-pro/tests/SQLInsight.Core.Tests/QueryStatsDeltaTests.cs

using SQLInsight.Core.Data;

namespace SQLInsight.Core.Tests;

public class QueryStatsDeltaTests
{
    private static readonly DateTime Start = new(2026, 9, 20, 0, 0, 0);
    private static readonly DateTimeOffset T0 = new(2026, 9, 20, 3, 0, 0, TimeSpan.Zero);

    private static QueryStat Stat(string key, long executions, long cpu, DateTime created, DateTime lastRun, long reads = 0) => new()
    {
        Key = key,
        Text = "SELECT " + key,
        Executions = executions,
        WorkerTimeUs = cpu,
        ElapsedTimeUs = cpu * 2,
        LogicalReads = reads,
        PhysicalReads = 0,
        LogicalWrites = 0,
        CreatedAt = created,
        LastExecutionAt = lastRun,
    };

    private static QueryStatsSnapshot Snapshot(DateTimeOffset at, DateTime serverTime, params QueryStat[] queries) => new()
    {
        CapturedAt = at,
        ServerTime = serverTime,
        SqlServerStartTime = Start,
        Queries = queries,
    };

    [Fact]
    public void Statement_seen_before_with_the_same_plan_gets_the_difference()
    {
        var created = new DateTime(2026, 9, 20, 1, 0, 0);
        var before = Snapshot(T0, new DateTime(2026, 9, 20, 3, 0, 0), Stat("0xA", 100, 5_000, created, created.AddHours(1), reads: 1_000));
        var now = Snapshot(T0.AddMinutes(5), new DateTime(2026, 9, 20, 3, 5, 0), Stat("0xA", 130, 8_000, created, new DateTime(2026, 9, 20, 3, 4, 0), reads: 1_600));

        var delta = QueryStatsDelta.Compute(before, now)!;

        var q = Assert.Single(delta.Queries);
        Assert.Equal(30, q.Executions);
        Assert.Equal(3_000, q.WorkerTimeUs);
        Assert.Equal(6_000, q.ElapsedTimeUs);
        Assert.Equal(600, q.LogicalReads);
        Assert.Equal("SELECT 0xA", q.Text);
    }

    [Fact]
    public void Plan_compiled_after_the_previous_collection_counts_in_full()
    {
        var before = Snapshot(T0, new DateTime(2026, 9, 20, 3, 0, 0));
        var now = Snapshot(T0.AddMinutes(5), new DateTime(2026, 9, 20, 3, 5, 0),
            Stat("0xB", 4, 900, new DateTime(2026, 9, 20, 3, 2, 0), new DateTime(2026, 9, 20, 3, 4, 0)));

        var q = Assert.Single(QueryStatsDelta.Compute(before, now)!.Queries);

        Assert.Equal(4, q.Executions);
        Assert.Equal(900, q.WorkerTimeUs);
    }

    [Fact]
    public void Old_statement_seen_for_the_first_time_has_no_known_starting_point()
    {
        var before = Snapshot(T0, new DateTime(2026, 9, 20, 3, 0, 0));
        var now = Snapshot(T0.AddMinutes(5), new DateTime(2026, 9, 20, 3, 5, 0),
            Stat("0xC", 1_000_000, 9_000_000, new DateTime(2026, 9, 19, 8, 0, 0), new DateTime(2026, 9, 20, 3, 4, 0)));

        Assert.Empty(QueryStatsDelta.Compute(before, now)!.Queries);
    }

    [Fact]
    public void Recompiled_plan_starts_over_without_negative_numbers()
    {
        var oldPlan = new DateTime(2026, 9, 20, 1, 0, 0);
        var before = Snapshot(T0, new DateTime(2026, 9, 20, 3, 0, 0), Stat("0xD", 500, 50_000, oldPlan, oldPlan.AddHours(1)));

        // Evicted and compiled again at 03:03: smaller totals, newer creation time. Everything since 03:03 is new.
        var recompiled = Snapshot(T0.AddMinutes(5), new DateTime(2026, 9, 20, 3, 5, 0),
            Stat("0xD", 7, 700, new DateTime(2026, 9, 20, 3, 3, 0), new DateTime(2026, 9, 20, 3, 4, 0)));
        var q = Assert.Single(QueryStatsDelta.Compute(before, recompiled)!.Queries);
        Assert.Equal(7, q.Executions);
        Assert.Equal(700, q.WorkerTimeUs);

        // Counters went backwards but the oldest plan is from before: one of several plans was evicted. Skip, never negative.
        var partlyEvicted = Snapshot(T0.AddMinutes(5), new DateTime(2026, 9, 20, 3, 5, 0),
            Stat("0xD", 300, 30_000, oldPlan, new DateTime(2026, 9, 20, 3, 4, 0)));
        Assert.Empty(QueryStatsDelta.Compute(before, partlyEvicted)!.Queries);
    }

    [Fact]
    public void Restart_gives_no_delta_and_forgets_remembered_statements()
    {
        var before = Snapshot(T0, new DateTime(2026, 9, 20, 3, 0, 0), Stat("0xE", 1, 1, Start, Start));
        var after = Snapshot(T0.AddMinutes(5), new DateTime(2026, 9, 20, 3, 5, 0)) with { SqlServerStartTime = Start.AddHours(3) };

        Assert.Null(QueryStatsDelta.Compute(before, after));
        Assert.Empty(QueryStatsDelta.Remember(before, [], after.SqlServerStartTime));
    }

    [Fact]
    public void Statements_not_run_now_are_remembered_for_later_up_to_a_limit()
    {
        var created = new DateTime(2026, 9, 20, 1, 0, 0);
        var previous = Snapshot(T0, new DateTime(2026, 9, 20, 3, 0, 0),
            [.. Enumerable.Range(0, QueryStatsSnapshot.MaxRemembered + 10).Select(i => Stat("0x" + i, 1, 1, created, created.AddSeconds(i)))]);
        var current = new[] { Stat("0xNEW", 1, 1, created, created.AddHours(2)) };

        var kept = QueryStatsDelta.Remember(previous, current, Start);

        Assert.Equal(QueryStatsSnapshot.MaxRemembered, kept.Count);
        Assert.Equal("0xNEW", kept[0].Key);
        Assert.DoesNotContain(kept, q => q.Key == "0x0"); // least recently run is forgotten first
    }

    [Fact]
    public void Sql_server_2005_key_is_handle_and_offsets()
    {
        // The key is whatever the collector builds; on 2005 sql_handle:start:end, so the same batch's statements stay apart.
        var created = new DateTime(2026, 9, 20, 1, 0, 0);
        var first = "0x0200000012ab:0:120";
        var second = "0x0200000012ab:122:-1";
        var before = Snapshot(T0, new DateTime(2026, 9, 20, 3, 0, 0), Stat(first, 10, 100, created, created), Stat(second, 10, 100, created, created));
        var now = Snapshot(T0.AddMinutes(5), new DateTime(2026, 9, 20, 3, 5, 0),
            Stat(first, 12, 160, created, new DateTime(2026, 9, 20, 3, 1, 0)), Stat(second, 15, 400, created, new DateTime(2026, 9, 20, 3, 1, 0)));

        var delta = QueryStatsDelta.Compute(before, now)!;

        Assert.Equal(2, delta.Queries.Count);
        Assert.Equal(second, delta.Queries[0].Key); // most CPU first
        Assert.Equal(300, delta.Queries[0].WorkerTimeUs);
        Assert.Equal(60, delta.Queries[1].WorkerTimeUs);
    }
}