using HrynCo.NotificationService.DAL.Abstract.Providers;
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 EmailChannelServiceMigrationTests(TemplateDatabaseFixture fixture) : IClassFixture<TemplateDatabaseFixture>
{
    private const string PreviousMigration = "20261023223753_AddPushDeliveries";

    [Theory]
    [InlineData("StoreMate-Prod", "Invemory-Prod")]
    [InlineData("Invemory.Staging.Sandbox", "Invemory.Staging.Sandbox")]
    public async Task BackfillPreservesChannelSettingsLimitsAndUsage(string legacyName, string currentName)
    {
        var connection = await PrepareAsync(legacyName, currentName);
        await using (var db = TemplateDatabaseFixture.CreateContext(connection))
            await db.Database.MigrateAsync();
        using var provider = new ServiceCollection().AddNotificationDataAccess(connection).BuildServiceProvider();
        using var scope = provider.CreateScope();
        var channel = Assert.Single(await scope.ServiceProvider.GetRequiredService<IEmailChannelRepository>().GetAllAsync());
        var service = await scope.ServiceProvider.GetRequiredService<INotificationServiceRepository>().GetByNameAsync(currentName);
        Assert.Equal(service!.Id, channel.ServiceId);
        Assert.Equal(currentName, channel.ServiceName);
        Assert.Equal(7, channel.Priority);
        Assert.Equal(25, channel.DailyLimit);
        Assert.Equal(250, channel.MonthlyLimit);
        Assert.Equal(80, channel.WarnThresholdPercent);
        Assert.False(channel.IsActive);
        Assert.Equal("migration-test-value", Assert.IsType<SmtpChannelSettings>(channel.Settings).Password);
        Assert.Equal(12, await ScalarAsync(connection, "SELECT sent_count FROM email_channel_usage"));
        Assert.Equal(1L, await ScalarAsync(connection, "SELECT count(*) FROM email_channel_usage u JOIN email_channels c ON c.id = u.provider_id"));
        await using var rollback = TemplateDatabaseFixture.CreateContext(connection);
        await rollback.GetService<IMigrator>().MigrateAsync(PreviousMigration);
        Assert.Equal(currentName, await ScalarAsync(connection, "SELECT service_name FROM email_channels"));
    }

    [Theory]
    [InlineData("UnmappedService", false)]
    [InlineData("StoreMate-Prod", true)]
    public async Task MissingOrAmbiguousMappingAbortsWithoutChangingChannelData(string legacyName, bool ambiguous)
    {
        var connection = await PrepareAsync(legacyName, "Invemory-Prod");
        if (ambiguous)
            await TemplateDatabaseFixture.ExecuteAsync(connection,
                "INSERT INTO services(id, name, revision, created) VALUES (gen_random_uuid(), 'StoreMate-Prod', gen_random_uuid(), now())");
        var before = await ScalarAsync(connection, "SELECT row_to_json(c)::text FROM email_channels c");
        await using var db = TemplateDatabaseFixture.CreateContext(connection);
        var error = await Assert.ThrowsAsync<PostgresException>(() => db.Database.MigrateAsync());
        Assert.Contains("exactly one existing service", error.MessageText);
        Assert.Equal(before, await ScalarAsync(connection, "SELECT row_to_json(c)::text FROM email_channels c"));
        Assert.Equal(12, await ScalarAsync(connection, "SELECT sent_count FROM email_channel_usage"));
        Assert.Equal(0L, await ScalarAsync(connection,
            "SELECT count(*) FROM information_schema.columns WHERE table_name = 'email_channels' AND column_name = 'service_id'"));
    }

    private async Task<string> PrepareAsync(string legacyName, string currentName)
    {
        var connectionString = await fixture.CreateDatabaseAsync(legacy: true);
        await using (var db = TemplateDatabaseFixture.CreateContext(connectionString))
            await db.GetService<IMigrator>().MigrateAsync(PreviousMigration);
        await using var connection = new NpgsqlConnection(connectionString);
        await connection.OpenAsync();
        await using var command = new NpgsqlCommand("""
            INSERT INTO services(id, name, revision, created) VALUES (gen_random_uuid(), @current, gen_random_uuid(), now());
            INSERT INTO email_channels(id, service_name, priority, provider_type, settings, daily_limit, monthly_limit,
                warn_threshold_percent, is_active, created)
            VALUES (@channel, @legacy, 7, 1, '{"Host":"smtp.invalid","Password":"migration-test-value"}', 25, 250, 80, false, now());
            INSERT INTO email_channel_usage(id, provider_id, date, sent_count, created)
            VALUES (gen_random_uuid(), @channel, CURRENT_DATE, 12, now());
            """, connection);
        command.Parameters.AddWithValue("current", currentName);
        command.Parameters.AddWithValue("legacy", legacyName);
        command.Parameters.AddWithValue("channel", Guid.NewGuid());
        await command.ExecuteNonQueryAsync();
        return connectionString;
    }

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