missions/tests/Azaion.Missions.E2E.Tests/Fixtures/DbResetFixture.cs

using Npgsql;

namespace Azaion.Missions.E2E.Fixtures;

/// <summary>
/// Class-scoped DB reset (xUnit <see cref="IClassFixture{TFixture}"/>).
/// Truncates all schema tables between test classes so read-path scenarios
/// (AC-1, AC-2, AC-4) start from a known state.
/// </summary>
/// <remarks>
/// CASCADE is used so FK chains (mission → waypoint, mission → media) flush
/// in one round-trip. Sequence resets are explicit because TRUNCATE alone
/// does not reset SERIAL/BIGSERIAL counters when RESTART IDENTITY is omitted.
/// </remarks>
public sealed class DbResetFixture : IDisposable
{
    public DbResetFixture()
    {
        ResetDatabase(TestEnvironment.DbSideChannel);
    }

    public void Dispose() { /* No-op — TRUNCATE is the only state owned. */ }

    public static void ResetDatabase(string connectionString)
    {
        using var conn = new NpgsqlConnection(connectionString);
        conn.Open();
        using var cmd = conn.CreateCommand();
        cmd.CommandText = """
            DO $$
            DECLARE
                t TEXT;
            BEGIN
                FOR t IN
                    SELECT tablename FROM pg_tables
                    WHERE schemaname = 'public' AND tablename NOT LIKE 'pg_%'
                LOOP
                    EXECUTE format('TRUNCATE TABLE %I RESTART IDENTITY CASCADE', t);
                END LOOP;
            END $$;
            """;
        cmd.ExecuteNonQuery();
    }
}