hrynco-notification-service/HrynCo.NotificationService.Web.IntegrationTests/InterimEmailTemplateMigrationTests.cs

using HrynCo.NotificationService.DAL.Abstract.Repositories;
using HrynCo.NotificationService.DAL.EF;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Migrations;
using Microsoft.Extensions.DependencyInjection;
using Npgsql;

namespace HrynCo.NotificationService.Web.IntegrationTests;

public sealed class InterimEmailTemplateMigrationTests(TemplateDatabaseFixture fixture) : IClassFixture<TemplateDatabaseFixture>
{
    [Fact]
    public async Task KnownInterimLayout_UpgradesInPlacePreservingEveryExistingFieldAndHistory()
    {
        var connection = await InterimEmailTemplateFixture.CreateAsync(fixture);
        var original = await SourceSnapshotAsync(connection, upgraded: false);
        await MigrateAsync(connection);
        Assert.Equal(original, await SourceSnapshotAsync(connection, upgraded: true));
        var firstServices = await ScalarAsync(connection, "SELECT jsonb_agg(to_jsonb(s) ORDER BY id)::text FROM services s");
        await MigrateAsync(connection);
        Assert.Equal(firstServices, await ScalarAsync(connection, "SELECT jsonb_agg(to_jsonb(s) ORDER BY id)::text FROM services s"));
        await using var db = TemplateDatabaseFixture.CreateContext(connection);
        var history = (await db.Database.GetAppliedMigrationsAsync()).ToList();
        Assert.Equal(4, history.Count);
        Assert.Contains(InterimEmailTemplateFixture.MigrationId, history);
        using var provider = new ServiceCollection().AddNotificationDataAccess(connection).BuildServiceProvider();
        using var scope = provider.CreateScope();
        var repository = scope.ServiceProvider.GetRequiredService<IEmailTemplateRepository>();
        Assert.Equal(Guid.Parse("66666666-6666-6666-6666-666666666666"), (await repository.GetAsync("InterimService", "Welcome", "ru-RU"))!.Id);
        Assert.Equal(Guid.Parse("77777777-7777-7777-7777-777777777777"), (await repository.GetAsync("InterimService", "Welcome", "ru"))!.Id);
        var definitions = scope.ServiceProvider.GetRequiredService<IEmailTemplateDefinitionRepository>();
        Assert.Empty((await definitions.GetDefinitionAsync("InterimService", "Empty"))!.Languages);
        var services = await scope.ServiceProvider.GetRequiredService<INotificationServiceRepository>().GetAllAsync();
        Assert.Contains(services, s => s.Name == "UnusedService" && s.Languages.Count == 0);
        Assert.All(services, s => Assert.NotEqual(Guid.Empty, s.Revision));
    }

    [Theory]
    [InlineData("ALTER TABLE email_templates ADD COLUMN service_name varchar(100)")]
    [InlineData("ALTER TABLE email_templates ALTER COLUMN language_code TYPE varchar(11)")]
    [InlineData("DROP INDEX \"IX_email_templates_definition_id_language_code\"")]
    [InlineData("DROP INDEX \"IX_email_templates_definition_id_language_code\"; CREATE UNIQUE INDEX \"IX_email_templates_definition_id_language_code\" ON email_templates(id)")]
    [InlineData("ALTER TABLE email_templates DROP CONSTRAINT \"FK_email_templates_email_template_definitions_definition_id\"; ALTER TABLE email_templates ADD CONSTRAINT \"FK_email_templates_email_template_definitions_definition_id\" FOREIGN KEY(definition_id) REFERENCES email_template_definitions(id) ON DELETE RESTRICT")]
    [InlineData("DELETE FROM \"__EFMigrationsHistory\" WHERE \"MigrationId\" = '20261017070315_EmailTemplateLocalizations'")]
    [InlineData("CREATE TABLE service_languages(id uuid)")]
    public async Task HybridOrUnknownLayout_FailsWithoutAdoptingOrChangingIt(string change)
    {
        var connection = await InterimEmailTemplateFixture.CreateAsync(fixture);
        await TemplateDatabaseFixture.ExecuteAsync(connection, change);
        var original = await SourceSnapshotAsync(connection, upgraded: false);
        var error = await Assert.ThrowsAsync<PostgresException>(() => MigrateAsync(connection));
        Assert.Contains("Unsupported email-template source layout", error.MessageText);
        Assert.Equal(original, await SourceSnapshotAsync(connection, upgraded: false));
    }

    [Theory]
    [InlineData("UPDATE email_template_definitions SET variables = '{}'", "Invalid interim shared variables array")]
    [InlineData("UPDATE email_template_definitions SET variables = '[null]'", "Invalid interim shared variables shape")]
    [InlineData("UPDATE email_templates SET language_code = '' WHERE id = '66666666-6666-6666-6666-666666666666'", "Invalid blank identity")]
    public async Task InvalidInterimData_FailsBeforeSchemaChanges(string change, string diagnostic)
    {
        var connection = await InterimEmailTemplateFixture.CreateAsync(fixture);
        await TemplateDatabaseFixture.ExecuteAsync(connection, change);
        var original = await SourceSnapshotAsync(connection, upgraded: false);
        var error = await Assert.ThrowsAsync<PostgresException>(() => MigrateAsync(connection));
        Assert.Contains(diagnostic, error.MessageText);
        Assert.Equal(original, await SourceSnapshotAsync(connection, upgraded: false));
        Assert.Equal("0", await ScalarAsync(connection, "SELECT count(*)::text FROM information_schema.columns WHERE table_name = 'services' AND column_name = 'revision'"));
    }

    [Fact]
    public async Task InterruptedInterimUpgrade_RollsBackAndCanBeRetried()
    {
        var connectionString = await InterimEmailTemplateFixture.CreateAsync(fixture);
        var original = await SourceSnapshotAsync(connectionString, upgraded: false);
        await using var db = TemplateDatabaseFixture.CreateContext(connectionString);
        var script = db.GetService<IMigrator>().GenerateScript(TemplateDatabaseFixture.LegacyMigration, options: MigrationsSqlGenerationOptions.NoTransactions);
        script = script.Replace("ALTER TABLE email_templates DROP COLUMN language_code;", "PERFORM pg_sleep(2); ALTER TABLE email_templates DROP COLUMN language_code;");
        await using (var connection = new NpgsqlConnection(connectionString))
        {
            await connection.OpenAsync();
            await using var transaction = await connection.BeginTransactionAsync();
            await using var command = new NpgsqlCommand("SET LOCAL statement_timeout = '500ms'; " + script, connection, transaction);
            Assert.Equal("57014", (await Assert.ThrowsAsync<PostgresException>(() => command.ExecuteNonQueryAsync())).SqlState);
            await transaction.RollbackAsync();
        }
        Assert.Equal(original, await SourceSnapshotAsync(connectionString, upgraded: false));
        await MigrateAsync(connectionString);
        Assert.Equal(original, await SourceSnapshotAsync(connectionString, upgraded: true));
    }

    [Fact]
    public async Task InterimOrigin_DownIsBlockedBeforeAnyChanges()
    {
        var connection = await InterimEmailTemplateFixture.CreateAsync(fixture);
        await MigrateAsync(connection);
        var original = await SourceSnapshotAsync(connection, upgraded: true);
        await using var db = TemplateDatabaseFixture.CreateContext(connection);
        var error = await Assert.ThrowsAsync<PostgresException>(() => db.GetService<IMigrator>().MigrateAsync(TemplateDatabaseFixture.LegacyMigration));
        Assert.Contains("Cannot downgrade an interim-origin database", error.MessageText);
        Assert.Equal(original, await SourceSnapshotAsync(connection, upgraded: true));
    }

    private static async Task MigrateAsync(string connection)
    {
        await using var db = TemplateDatabaseFixture.CreateContext(connection);
        await db.Database.MigrateAsync();
    }

    private static Task<string> SourceSnapshotAsync(string connection, bool upgraded)
    {
        var versions = upgraded
            ? "SELECT (to_jsonb(t) - 'service_id' - 'service_language_id') || jsonb_build_object('language_code', l.code) AS value FROM email_templates t JOIN service_languages l ON l.id = t.service_language_id"
            : "SELECT to_jsonb(t) AS value FROM email_templates t";
        return ScalarAsync(connection, $"""
            SELECT jsonb_build_object(
                'services', (SELECT jsonb_agg(to_jsonb(s) - 'revision' ORDER BY id) FROM services s),
                'definitions', (SELECT jsonb_agg(to_jsonb(d) ORDER BY id) FROM email_template_definitions d),
                'versions', (SELECT jsonb_agg(value ORDER BY value->>'id') FROM ({versions}) q))::text
            """);
    }

    private static async Task<string> ScalarAsync(string connectionString, string sql)
    {
        await using var connection = new NpgsqlConnection(connectionString);
        await connection.OpenAsync();
        await using var command = new NpgsqlCommand(sql, connection);
        return (string)(await command.ExecuteScalarAsync())!;
    }
}