ScadaBridge/tests/ZB.MOM.WW.ScadaBridge.ConfigurationDatabase.Tests/Migrations/AddAuditLogTableMigrationTests.cs

using Microsoft.Data.SqlClient;
using Xunit;

namespace ZB.MOM.WW.ScadaBridge.ConfigurationDatabase.Tests.Migrations;

/// <summary>
/// Bundle C (#23 M1) integration tests: applies the EF migrations to a
/// freshly-created MSSQL test database on the running infra/mssql container
/// and asserts that the AddAuditLogTable migration produced the expected
/// partition function, partition scheme, partition-aligned table, named
/// indexes, and DB roles.
/// </summary>
/// <remarks>
/// Tests use <see cref="SkippableFactAttribute"/> + <c>Skip.IfNot(...)</c> from
/// the Xunit.SkippableFact package so the runner reports them as Skipped (not
/// Passed) when MSSQL is unreachable. xunit 2.9.x does not ship a native
/// <c>Assert.Skip</c>/<c>Assert.SkipUnless</c> — those land in xunit v3 — so
/// SkippableFact is the canonical equivalent for this project. The fixture
/// applies the migration once at construction time.
/// </remarks>
public class AddAuditLogTableMigrationTests : IClassFixture<MsSqlMigrationFixture>
{
    private readonly MsSqlMigrationFixture _fixture;

    public AddAuditLogTableMigrationTests(MsSqlMigrationFixture fixture)
    {
        _fixture = fixture;
    }

    [SkippableFact]
    public async Task AppliesMigration_CreatesAuditLogTable()
    {
        Skip.IfNot(_fixture.Available, _fixture.SkipReason);

        var exists = await ScalarAsync<int>(
            "SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES " +
            "WHERE TABLE_NAME = 'AuditLog' AND TABLE_SCHEMA = 'dbo';");

        Assert.Equal(1, exists);
    }

    [SkippableFact]
    public async Task AppliesMigration_CreatesPartitionFunction_pf_AuditLog_Month()
    {
        Skip.IfNot(_fixture.Available, _fixture.SkipReason);

        var functionExists = await ScalarAsync<int>(
            "SELECT COUNT(*) FROM sys.partition_functions WHERE name = 'pf_AuditLog_Month';");
        Assert.Equal(1, functionExists);

        // Specification (alog.md §4 / Bundle C plan): 24 monthly boundaries
        // covering 2026-01-01 through 2027-12-01 UTC.
        var boundaryCount = await ScalarAsync<int>(
            "SELECT COUNT(*) FROM sys.partition_range_values rv " +
            "INNER JOIN sys.partition_functions pf ON rv.function_id = pf.function_id " +
            "WHERE pf.name = 'pf_AuditLog_Month';");
        Assert.True(boundaryCount >= 24,
            $"Expected at least 24 monthly boundaries on pf_AuditLog_Month; got {boundaryCount}.");
    }

    [SkippableFact]
    public async Task AppliesMigration_CreatesPartitionScheme_ps_AuditLog_Month()
    {
        Skip.IfNot(_fixture.Available, _fixture.SkipReason);

        var schemeExists = await ScalarAsync<int>(
            "SELECT COUNT(*) FROM sys.partition_schemes WHERE name = 'ps_AuditLog_Month';");
        Assert.Equal(1, schemeExists);
    }

    [SkippableFact]
    public async Task AppliesMigration_TableIsPartitionAligned()
    {
        Skip.IfNot(_fixture.Available, _fixture.SkipReason);

        // The clustered (PK) index on AuditLog must live on the ps_AuditLog_Month
        // partition scheme; sys.indexes.data_space_id points at the scheme.
        var schemeName = await ScalarAsync<string?>(
            "SELECT ps.name FROM sys.indexes i " +
            "INNER JOIN sys.objects o ON i.object_id = o.object_id " +
            "INNER JOIN sys.partition_schemes ps ON i.data_space_id = ps.data_space_id " +
            "WHERE o.name = 'AuditLog' AND i.index_id = 1;");

        Assert.Equal("ps_AuditLog_Month", schemeName);
    }

    /// <summary>
    /// Verifies all nine named non-clustered indexes exist on the final
    /// <c>dbo.AuditLog</c> table after all migrations have been applied.
    /// The original five indexes were created by <c>AddAuditLogTable</c>;
    /// the <c>CollapseAuditLogToCanonical</c> (C5, Task 2.5) migration rebuilt
    /// the table and added <c>IX_AuditLog_Execution</c>,
    /// <c>IX_AuditLog_ParentExecution</c>, <c>IX_AuditLog_Node_Occurred</c>,
    /// and <c>UX_AuditLog_EventId</c> — nine in total.
    /// </summary>
    [SkippableFact]
    public async Task AppliesMigration_CreatesNineNamedIndexes()
    {
        Skip.IfNot(_fixture.Available, _fixture.SkipReason);

        // All nine named non-clustered indexes present on dbo.AuditLog after
        // the full migration history is applied (AddAuditLogTable through
        // CollapseAuditLogToCanonical).
        var expected = new[]
        {
            // Original five (AddAuditLogTable / AddAuditLogSourceNode):
            "IX_AuditLog_OccurredAtUtc",
            "IX_AuditLog_Site_Occurred",
            "IX_AuditLog_CorrelationId",
            "IX_AuditLog_Channel_Status_Occurred",
            "IX_AuditLog_Target_Occurred",
            // Added by CollapseAuditLogToCanonical (C5, Task 2.5):
            "IX_AuditLog_Execution",
            "IX_AuditLog_ParentExecution",
            "IX_AuditLog_Node_Occurred",
            "UX_AuditLog_EventId",
        };

        foreach (var indexName in expected)
        {
            var count = await ScalarAsync<int>(
                "SELECT COUNT(*) FROM sys.indexes i " +
                "INNER JOIN sys.objects o ON i.object_id = o.object_id " +
                $"WHERE o.name = 'AuditLog' AND i.name = '{indexName}';");
            Assert.True(count == 1, $"Expected index '{indexName}' to exist on AuditLog; found {count}.");
        }
    }

    [SkippableFact]
    public async Task AppliesMigration_CreatesAuditWriterRole_WithExpectedGrants()
    {
        Skip.IfNot(_fixture.Available, _fixture.SkipReason);

        var roleExists = await ScalarAsync<int>(
            "SELECT COUNT(*) FROM sys.database_principals " +
            "WHERE name = 'scadabridge_audit_writer' AND type = 'R';");
        Assert.Equal(1, roleExists);

        // GRANT INSERT + GRANT SELECT must be present (G state = grant).
        var insertGranted = await ScalarAsync<int>(
            "SELECT COUNT(*) FROM sys.database_permissions p " +
            "INNER JOIN sys.database_principals pr ON p.grantee_principal_id = pr.principal_id " +
            "INNER JOIN sys.objects o ON p.major_id = o.object_id " +
            "WHERE pr.name = 'scadabridge_audit_writer' AND o.name = 'AuditLog' " +
            "  AND p.permission_name = 'INSERT' AND p.state IN ('G','W');");
        Assert.Equal(1, insertGranted);

        var selectGranted = await ScalarAsync<int>(
            "SELECT COUNT(*) FROM sys.database_permissions p " +
            "INNER JOIN sys.database_principals pr ON p.grantee_principal_id = pr.principal_id " +
            "INNER JOIN sys.objects o ON p.major_id = o.object_id " +
            "WHERE pr.name = 'scadabridge_audit_writer' AND o.name = 'AuditLog' " +
            "  AND p.permission_name = 'SELECT' AND p.state IN ('G','W');");
        Assert.Equal(1, selectGranted);

        // UPDATE / DELETE must NOT be granted — and DENY (state = 'D') is even
        // stronger. Treat presence of GRANT (state 'G' or 'W') as the failure.
        var updateGranted = await ScalarAsync<int>(
            "SELECT COUNT(*) FROM sys.database_permissions p " +
            "INNER JOIN sys.database_principals pr ON p.grantee_principal_id = pr.principal_id " +
            "INNER JOIN sys.objects o ON p.major_id = o.object_id " +
            "WHERE pr.name = 'scadabridge_audit_writer' AND o.name = 'AuditLog' " +
            "  AND p.permission_name = 'UPDATE' AND p.state IN ('G','W');");
        Assert.Equal(0, updateGranted);

        var deleteGranted = await ScalarAsync<int>(
            "SELECT COUNT(*) FROM sys.database_permissions p " +
            "INNER JOIN sys.database_principals pr ON p.grantee_principal_id = pr.principal_id " +
            "INNER JOIN sys.objects o ON p.major_id = o.object_id " +
            "WHERE pr.name = 'scadabridge_audit_writer' AND o.name = 'AuditLog' " +
            "  AND p.permission_name = 'DELETE' AND p.state IN ('G','W');");
        Assert.Equal(0, deleteGranted);
    }

    [SkippableFact]
    public async Task AppliesMigration_CreatesAuditPurgerRole_WithExpectedGrants()
    {
        Skip.IfNot(_fixture.Available, _fixture.SkipReason);

        var roleExists = await ScalarAsync<int>(
            "SELECT COUNT(*) FROM sys.database_principals " +
            "WHERE name = 'scadabridge_audit_purger' AND type = 'R';");
        Assert.Equal(1, roleExists);

        // SELECT on AuditLog.
        var selectGranted = await ScalarAsync<int>(
            "SELECT COUNT(*) FROM sys.database_permissions p " +
            "INNER JOIN sys.database_principals pr ON p.grantee_principal_id = pr.principal_id " +
            "INNER JOIN sys.objects o ON p.major_id = o.object_id " +
            "WHERE pr.name = 'scadabridge_audit_purger' AND o.name = 'AuditLog' " +
            "  AND p.permission_name = 'SELECT' AND p.state IN ('G','W');");
        Assert.Equal(1, selectGranted);

        // ALTER on SCHEMA::dbo (class 3 = SCHEMA).
        var alterSchema = await ScalarAsync<int>(
            "SELECT COUNT(*) FROM sys.database_permissions p " +
            "INNER JOIN sys.database_principals pr ON p.grantee_principal_id = pr.principal_id " +
            "INNER JOIN sys.schemas s ON p.major_id = s.schema_id " +
            "WHERE pr.name = 'scadabridge_audit_purger' AND s.name = 'dbo' " +
            "  AND p.class = 3 AND p.permission_name = 'ALTER' AND p.state IN ('G','W');");
        Assert.Equal(1, alterSchema);
    }

    [SkippableFact]
    public async Task AuditWriterRole_CannotUpdateAuditLog()
    {
        Skip.IfNot(_fixture.Available, _fixture.SkipReason);

        // Set up a dedicated user mapped to scadabridge_audit_writer, then EXECUTE AS
        // and attempt UPDATE — DENY UPDATE on the role must reject the statement.
        // Use a guid-suffixed user name so reruns in the same fixture don't collide.
        var testUser = $"audit_writer_smoke_{Guid.NewGuid():N}".Substring(0, 32);

        await using (var setup = new SqlConnection(_fixture.ConnectionString))
        {
            await setup.OpenAsync();
            await using var setupCmd = setup.CreateCommand();
            setupCmd.CommandText =
                $"CREATE USER [{testUser}] WITHOUT LOGIN; " +
                $"ALTER ROLE scadabridge_audit_writer ADD MEMBER [{testUser}];";
            await setupCmd.ExecuteNonQueryAsync();
        }

        var ex = await Assert.ThrowsAsync<SqlException>(async () =>
        {
            await using var conn = new SqlConnection(_fixture.ConnectionString);
            await conn.OpenAsync();
            await using var cmd = conn.CreateCommand();
            // WHERE 1=0 guarantees no rows are touched even if the permission check
            // somehow passes — the test asserts the engine rejects the statement
            // at permission-check time, not via a side effect on data.
            // C5 (Task 2.5): target a CANONICAL (non-computed) column. Status is now a
            // persisted computed column, and an UPDATE that sets a computed column is
            // rejected with a "cannot be modified" error BEFORE the permission check —
            // which would mask the DENY UPDATE this test exercises. Actor is a plain
            // writable column, so the permission check is the one that fires.
            cmd.CommandText =
                $"EXECUTE AS USER = '{testUser}'; " +
                $"UPDATE dbo.AuditLog SET Actor = 'X' WHERE 1 = 0; " +
                $"REVERT;";
            await cmd.ExecuteNonQueryAsync();
        });

        // SQL Server permission-denied errors carry number 229 (e.g. "The UPDATE
        // permission was denied"). Assert the message mentions permission rather
        // than pinning to the exact code, in case the engine version drifts.
        Assert.Contains("permission", ex.Message, StringComparison.OrdinalIgnoreCase);
    }

    // --- helpers ------------------------------------------------------------

    private async Task<T> ScalarAsync<T>(string sql)
    {
        await using var conn = _fixture.OpenConnection();
        await using var cmd = conn.CreateCommand();
        cmd.CommandText = sql;
        var result = await cmd.ExecuteScalarAsync();
        if (result is null || result is DBNull)
        {
            return default!;
        }
        return (T)Convert.ChangeType(result, typeof(T) == typeof(string) ? typeof(string) : Nullable.GetUnderlyingType(typeof(T)) ?? typeof(T))!;
    }
}