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

namespace HrynCo.NotificationService.Web.IntegrationTests;

public sealed class TemplateDatabaseFixture : IAsyncLifetime
{
    public const string LegacyMigration = "20260502154249_PendingChanges";
    private readonly PostgreSqlContainer _postgres = new PostgreSqlBuilder().WithImage("postgres:17-alpine").Build();

    public Task InitializeAsync() => _postgres.StartAsync();
    public Task DisposeAsync() => _postgres.DisposeAsync().AsTask();

    public async Task<string> CreateDatabaseAsync(bool legacy = false)
    {
        var name = "templates_" + Guid.NewGuid().ToString("N");
        await using var connection = new NpgsqlConnection(_postgres.GetConnectionString());
        await connection.OpenAsync();
        await using var command = new NpgsqlCommand($"CREATE DATABASE {name}", connection);
        await command.ExecuteNonQueryAsync();
        var builder = new NpgsqlConnectionStringBuilder(_postgres.GetConnectionString()) { Database = name };
        await using var db = CreateContext(builder.ConnectionString);
        await db.GetService<IMigrator>().MigrateAsync(legacy ? LegacyMigration : null);
        if (!legacy)
        {
            using var provider = new ServiceCollection().AddNotificationDataAccess(builder.ConnectionString).BuildServiceProvider();
            using var scope = provider.CreateScope();
            await scope.ServiceProvider.GetRequiredService<INotificationServiceRepository>().SaveAsync(new NotificationServiceInfo
            {
                Name = "TestService", Languages = new[] { "en", "uk", "de", "ru", "ru-RU" }
                    .Select(code => new ServiceLanguageInfo { Code = code }).ToList()
            }, true);
        }
        return builder.ConnectionString;
    }

    public static NotificationDbContext CreateContext(string connectionString) => new(
        new DbContextOptionsBuilder<NotificationDbContext>().UseNpgsql(connectionString).Options);

    public static async Task ExecuteAsync(string connectionString, string sql)
    {
        await using var connection = new NpgsqlConnection(connectionString);
        await connection.OpenAsync();
        await using var command = new NpgsqlCommand(sql, connection);
        await command.ExecuteNonQueryAsync();
    }

    public static async Task<Guid> InsertLegacyAsync(string connectionString, string language, string variables)
    {
        var id = Guid.NewGuid();
        await using var connection = new NpgsqlConnection(connectionString);
        await connection.OpenAsync();
        await using var command = new NpgsqlCommand("""
            INSERT INTO email_templates(id, service_name, key, language_code, subject, html_body, text_body, variables, created)
            VALUES (@id, 'TestService', 'Welcome', @language, @subject, '<p>{{Name}}</p>', 'Hello {{Name}}', @variables, now())
            """, connection);
        command.Parameters.AddWithValue("id", id);
        command.Parameters.AddWithValue("language", language);
        command.Parameters.AddWithValue("subject", "Subject " + language);
        command.Parameters.AddWithValue("variables", NpgsqlTypes.NpgsqlDbType.Jsonb, variables);
        await command.ExecuteNonQueryAsync();
        return id;
    }
}