using HrynCo.NotificationService.DAL.Abstract.Repositories;
using HrynCo.NotificationService.DAL.Abstract.Templates;
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 ChannelTemplateModelTests(TemplateDatabaseFixture fixture) : IClassFixture<TemplateDatabaseFixture>
{
    [Fact]
    public async Task MigrationPreservesEmailAndSupportsAllChannelCombinations()
    {
        var connection = await fixture.CreateDatabaseAsync(legacy: true);
        var originalId = await TemplateDatabaseFixture.InsertLegacyAsync(connection, "en", "[{\"name\":\"Name\",\"required\":true}]");
        await using var db = TemplateDatabaseFixture.CreateContext(connection);
        await db.Database.MigrateAsync();
        await db.Database.MigrateAsync();
        Assert.False(db.Database.HasPendingModelChanges());
        using var provider = Provider(connection);
        using var scope = provider.CreateScope();
        var emails = scope.ServiceProvider.GetRequiredService<IEmailTemplateRepository>();
        var original = await emails.GetAsync("TestService", "Welcome", "en");
        Assert.Equal(originalId, original!.Id);
        Assert.True(Assert.Single(original.Variables).Required);
        await SeedPushAsync(connection, "Welcome");
        await SeedPushAsync(connection, "PushOnly");
        Assert.Null(await emails.GetAsync("TestService", "PushOnly", "en"));
        var definitions = scope.ServiceProvider.GetRequiredService<IEmailTemplateDefinitionRepository>();
        await definitions.SaveDefinitionAsync(new EmailTemplateDefinition { ServiceName = "TestService", Key = "EmailOnly" }, true);
        await definitions.SaveDefinitionAsync(new EmailTemplateDefinition { ServiceName = "TestService", Key = "PushOnly" }, true);
        await TemplateDatabaseFixture.ExecuteAsync(connection, """
            DO $$ BEGIN
                IF (SELECT count(*) FROM templates) <> 3 OR (SELECT count(*) FROM push_template_definitions) <> 2 THEN
                    RAISE EXCEPTION 'Unexpected shared catalog or channel count';
                END IF;
                IF (SELECT variables FROM email_template_definitions WHERE key = 'Welcome') =
                   (SELECT variables FROM push_template_definitions WHERE key = 'Welcome') THEN
                    RAISE EXCEPTION 'Channel variables must remain independent';
                END IF;
            END $$;
            """);
    }

    [Fact]
    public async Task DeletingEmailRetainsPushAndBlocksServiceDeletion()
    {
        var connection = await fixture.CreateDatabaseAsync();
        using var provider = Provider(connection);
        using var scope = provider.CreateScope();
        var emails = scope.ServiceProvider.GetRequiredService<IEmailTemplateDefinitionRepository>();
        var email = new EmailTemplateDefinition { ServiceName = "TestService", Key = "Shared" };
        await emails.SaveDefinitionAsync(email, true);
        await SeedPushAsync(connection, "Shared");
        Assert.True(await emails.DeleteDefinitionAsync(email.ServiceId == Guid.Empty
            ? (await scope.ServiceProvider.GetRequiredService<INotificationServiceRepository>().GetByNameAsync("TestService"))!.Id
            : email.ServiceId, email.Key, email.Revision));
        await TemplateDatabaseFixture.ExecuteAsync(connection, """
            DO $$ BEGIN
                IF NOT EXISTS (SELECT 1 FROM templates WHERE key = 'Shared') OR
                   NOT EXISTS (SELECT 1 FROM push_templates) THEN
                    RAISE EXCEPTION 'Email deletion removed push';
                END IF;
            END $$;
            """);
        using var readScope = provider.CreateScope();
        var services = readScope.ServiceProvider.GetRequiredService<INotificationServiceRepository>();
        var service = (await services.GetByNameAsync("TestService"))!;
        await Assert.ThrowsAsync<EmailTemplateWriteException>(() => services.DeleteAsync(service.Id, service.Revision));
    }

    [Fact]
    public async Task LanguageDeletionCountsBothChannelsAndRetainsDefinitions()
    {
        var connection = await fixture.CreateDatabaseAsync();
        await SeedPushAsync(connection, "PushOnly");
        using var provider = Provider(connection);
        using var scope = provider.CreateScope();
        var services = scope.ServiceProvider.GetRequiredService<INotificationServiceRepository>();
        var service = (await services.GetByNameAsync("TestService"))!;
        var language = service.Languages.Single(l => l.Code == "en");
        Assert.Equal(1, language.VersionCount);
        service.Languages = service.Languages.Where(l => l.Id != language.Id).ToList();
        await Assert.ThrowsAsync<EmailTemplateWriteException>(() => services.SaveAsync(service, false));
        await services.RemoveLanguageAsync(service.Id, language.Id, 1, service.Revision);
        await TemplateDatabaseFixture.ExecuteAsync(connection, """
            DO $$ BEGIN
                IF EXISTS (SELECT 1 FROM push_templates) OR
                   NOT EXISTS (SELECT 1 FROM push_template_definitions WHERE key = 'PushOnly') THEN
                    RAISE EXCEPTION 'Language deletion did not retain the empty push definition';
                END IF;
            END $$;
            """);
    }

    [Fact]
    public async Task DatabaseRejectsDuplicatePushLanguageAndForeignServiceLanguage()
    {
        var connection = await fixture.CreateDatabaseAsync();
        await SeedPushAsync(connection, "PushOnly");
        await AssertSqlStateAsync(connection, "23505", """
            INSERT INTO push_templates
            SELECT gen_random_uuid(), definition_id, service_id, service_language_id, title, body, created, updated
            FROM push_templates LIMIT 1;
            """);
        await TemplateDatabaseFixture.ExecuteAsync(connection, """
            INSERT INTO services (id, name, revision, created) VALUES
                ('aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'Other', gen_random_uuid(), now());
            INSERT INTO service_languages (id, service_id, code, created) VALUES
                ('bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'en', now());
            """);
        await AssertSqlStateAsync(connection, "23503", """
            UPDATE push_templates SET service_language_id = 'bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb';
            """);
    }

    [Fact]
    public async Task RollbackRefusesToDiscardPushDefinitions()
    {
        var connection = await fixture.CreateDatabaseAsync();
        await SeedPushAsync(connection, "PushOnly");
        await using var db = TemplateDatabaseFixture.CreateContext(connection);
        var error = await Assert.ThrowsAsync<PostgresException>(() => db.GetService<IMigrator>()
            .MigrateAsync("20261024080457_AddPushChannels"));
        Assert.Contains("Cannot roll back channel templates", error.MessageText);
        await db.Database.MigrateAsync();
    }

    private static Task SeedPushAsync(string connection, string key) => TemplateDatabaseFixture.ExecuteAsync(connection, $"""
        INSERT INTO templates (id, service_id, key, created)
            SELECT gen_random_uuid(), id, '{key}', now() FROM services WHERE name = 'TestService'
            ON CONFLICT (service_id, key) DO NOTHING;
        INSERT INTO push_template_definitions (id, service_id, key, revision, variables, created)
            SELECT gen_random_uuid(), service_id, key, gen_random_uuid(), '[]'::jsonb, now()
            FROM templates WHERE key = '{key}';
        INSERT INTO push_templates (id, definition_id, service_id, service_language_id, title, body, created)
            SELECT gen_random_uuid(), d.id, d.service_id, l.id, 'Title', 'Body', now()
            FROM push_template_definitions d JOIN service_languages l ON l.service_id = d.service_id
            WHERE d.key = '{key}' AND l.code = 'en';
        """);

    private static ServiceProvider Provider(string connection) =>
        new ServiceCollection().AddNotificationDataAccess(connection).BuildServiceProvider();

    private static async Task AssertSqlStateAsync(string connection, string state, string sql)
    {
        var error = await Assert.ThrowsAsync<PostgresException>(() => TemplateDatabaseFixture.ExecuteAsync(connection, sql));
        Assert.Equal(state, error.SqlState);
    }
}