namespace HrynCo.NotificationService.Web.IntegrationTests;

public static class InterimEmailTemplateFixture
{
    public const string MigrationId = "20261017070315_EmailTemplateLocalizations";

    public static async Task<string> CreateAsync(TemplateDatabaseFixture fixture)
    {
        var connection = await fixture.CreateDatabaseAsync(legacy: true);
        await TemplateDatabaseFixture.ExecuteAsync(connection, """
            DROP INDEX "IX_email_templates_service_name_key_language_code";
            ALTER TABLE email_templates DROP COLUMN service_name, DROP COLUMN key, DROP COLUMN variables;
            ALTER TABLE email_templates ADD COLUMN definition_id uuid NOT NULL;
            CREATE TABLE services (id uuid PRIMARY KEY, name varchar(100) NOT NULL, created timestamptz NOT NULL, updated timestamptz);
            CREATE UNIQUE INDEX "IX_services_name" ON services(name);
            CREATE TABLE email_template_definitions (
                id uuid PRIMARY KEY, service_id uuid NOT NULL REFERENCES services(id) ON DELETE RESTRICT,
                key varchar(100) NOT NULL, revision uuid NOT NULL, variables jsonb NOT NULL,
                created timestamptz NOT NULL, updated timestamptz);
            CREATE UNIQUE INDEX "IX_email_template_definitions_service_id_key" ON email_template_definitions(service_id, key);
            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 CASCADE;
            CREATE UNIQUE INDEX "IX_email_templates_definition_id_language_code" ON email_templates(definition_id, language_code);
            INSERT INTO "__EFMigrationsHistory" VALUES ('20261017070315_EmailTemplateLocalizations', '10.0.7');
            INSERT INTO services VALUES
                ('11111111-1111-1111-1111-111111111111', 'InterimService', '2026-01-01Z', '2026-02-01Z'),
                ('88888888-8888-8888-8888-888888888888', 'UnusedService', '2026-01-02Z', null);
            INSERT INTO email_template_definitions VALUES
                ('22222222-2222-2222-2222-222222222222', '11111111-1111-1111-1111-111111111111', 'Welcome',
                    '44444444-4444-4444-4444-444444444444', '[{"name":"Name","required":true}]', '2026-01-03Z', '2026-02-03Z'),
                ('33333333-3333-3333-3333-333333333333', '11111111-1111-1111-1111-111111111111', 'Empty',
                    '55555555-5555-5555-5555-555555555555', '[]', '2026-01-04Z', null);
            INSERT INTO email_templates(id, definition_id, language_code, subject, html_body, text_body, created, updated) VALUES
                ('66666666-6666-6666-6666-666666666666', '22222222-2222-2222-2222-222222222222', 'ru-RU',
                    'Hello {{Name}}', '<p>{{Name}}</p>', 'Hello {{Name}}', '2026-01-05Z', '2026-02-05Z'),
                ('77777777-7777-7777-7777-777777777777', '22222222-2222-2222-2222-222222222222', 'ru',
                    'Welcome {{Name}}', '<p>Welcome {{Name}}</p>', 'Welcome {{Name}}', '2026-01-06Z', null);
            """);
        return connection;
    }
}