SIG-CM2.0/tests/SIGCM2.Application.Tests/Infrastructure/Audit/SecurityEventRepositoryTests.cs

using Dapper;
using FluentAssertions;
using Microsoft.Data.SqlClient;
using SIGCM2.Infrastructure.Audit;
using SIGCM2.Infrastructure.Persistence;
using Xunit;

namespace SIGCM2.Application.Tests.Infrastructure.Audit;

/// UDT-010 Batch 5 — SecurityEventRepository integration tests against SIGCM2_Test.
[Collection("Database")]
public sealed class SecurityEventRepositoryTests : IAsyncLifetime
{
    private const string ConnectionString = TestConnectionStrings.AppTestDb;

    private SqlConnection _connection = null!;
    private SecurityEventRepository _repo = null!;

    public async Task InitializeAsync()
    {
        _connection = new SqlConnection(ConnectionString);
        await _connection.OpenAsync();
        await _connection.ExecuteAsync("DELETE FROM dbo.SecurityEvent;");

        var factory = new SqlConnectionFactory(ConnectionString);
        _repo = new SecurityEventRepository(factory);
    }

    public async Task DisposeAsync()
    {
        await _connection.ExecuteAsync("DELETE FROM dbo.SecurityEvent;");
        await _connection.CloseAsync();
        await _connection.DisposeAsync();
    }

    [Fact]
    public async Task InsertAsync_LoginSuccess_PersistsAllFields()
    {
        var sessionId = Guid.NewGuid();
        var occurredAt = DateTime.UtcNow;

        var id = await _repo.InsertAsync(
            occurredAt: occurredAt,
            actorUserId: 42,
            attemptedUsername: null,
            sessionId: sessionId,
            action: "login",
            result: "success",
            failureReason: null,
            ipAddress: "1.2.3.4",
            userAgent: "ua/1.0",
            metadata: """{"route":"/login"}""");

        id.Should().BeGreaterThan(0);

        var row = await _connection.QuerySingleAsync<(int? ActorUserId, Guid? SessionId, string Action, string Result, string? FailureReason)>(
            "SELECT ActorUserId, SessionId, Action, Result, FailureReason FROM dbo.SecurityEvent WHERE Id = @Id",
            new { Id = id });
        row.ActorUserId.Should().Be(42);
        row.SessionId.Should().Be(sessionId);
        row.Action.Should().Be("login");
        row.Result.Should().Be("success");
        row.FailureReason.Should().BeNull();
    }

    [Fact]
    public async Task InsertAsync_LoginFailure_SupportsNullActorAndAttemptedUsername()
    {
        var id = await _repo.InsertAsync(
            occurredAt: DateTime.UtcNow,
            actorUserId: null,
            attemptedUsername: "juan",
            sessionId: null,
            action: "login",
            result: "failure",
            failureReason: "invalid_password",
            ipAddress: "1.2.3.4",
            userAgent: null,
            metadata: null);

        id.Should().BeGreaterThan(0);
        var row = await _connection.QuerySingleAsync<(int? ActorUserId, string? AttemptedUsername, string Result, string? FailureReason)>(
            "SELECT ActorUserId, AttemptedUsername, Result, FailureReason FROM dbo.SecurityEvent WHERE Id = @Id",
            new { Id = id });
        row.ActorUserId.Should().BeNull();
        row.AttemptedUsername.Should().Be("juan");
        row.Result.Should().Be("failure");
        row.FailureReason.Should().Be("invalid_password");
    }

    [Fact]
    public async Task InsertAsync_InvalidResult_FailsCheckConstraint()
    {
        var act = async () => await _repo.InsertAsync(
            DateTime.UtcNow, null, null, null, "login", "neutral", null, null, null, null);

        await act.Should().ThrowAsync<SqlException>()
            .Where(e => e.Message.Contains("CK_SecurityEvent_Result"));
    }
}