package at.procon.dip.migration;

import static org.assertj.core.api.Assertions.assertThat;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import org.flywaydb.core.Flyway;
import org.flywaydb.core.api.MigrationVersion;
import org.junit.jupiter.api.Test;
import org.testcontainers.containers.PostgreSQLContainer;
import org.testcontainers.junit.jupiter.Container;
import org.testcontainers.junit.jupiter.Testcontainers;

@Testcontainers
class DocDocumentTimeEnumConstraintRepairMigrationTest {

    @Container
    static PostgreSQLContainer<?> postgres = new PostgreSQLContainer<>("postgres:16-alpine")
            .withDatabaseName("dip_migration_test")
            .withUsername("test")
            .withPassword("test");

    @Test
    void repairMigrationExpandsLegacyDocDocumentChecksForTimeDocuments() throws SQLException {
        createLegacyDocDocumentState();

        Flyway.configure()
                .dataSource(postgres.getJdbcUrl(), postgres.getUsername(), postgres.getPassword())
                .locations("filesystem:src/main/resources/db/migration")
                .schemas("doc")
                .defaultSchema("doc")
                .baselineOnMigrate(true)
                .baselineVersion(MigrationVersion.fromVersion("42"))
                .load()
                .migrate();

        try (Connection connection = openConnection();
             Statement statement = connection.createStatement()) {
            statement.executeUpdate("""
                    INSERT INTO doc.doc_document (id, document_type, document_family)
                    VALUES ('709e388b-19d9-4c21-8d06-82b295b33505', 'TIME_ENTRY', 'TIME')
                    """);
        }

        try (Connection connection = openConnection();
             var preparedStatement = connection.prepareStatement("""
                     SELECT pg_get_constraintdef(oid)
                     FROM pg_constraint
                     WHERE conrelid = 'doc.doc_document'::regclass
                       AND conname = ?
                     """)) {
            preparedStatement.setString(1, "doc_document_document_family_check");
            try (var resultSet = preparedStatement.executeQuery()) {
                assertThat(resultSet.next()).isTrue();
                assertThat(resultSet.getString(1)).contains("TIME");
            }
        }
    }

    private void createLegacyDocDocumentState() throws SQLException {
        try (Connection connection = openConnection();
             Statement statement = connection.createStatement()) {
            statement.execute("CREATE SCHEMA doc");
            statement.execute("""
                    CREATE TYPE doc.doc_document_type AS ENUM (
                        'TED_NOTICE',
                        'EMAIL',
                        'MIME_MESSAGE',
                        'PDF',
                        'DOCX',
                        'HTML',
                        'XML_GENERIC',
                        'TEXT',
                        'MARKDOWN',
                        'ZIP_ARCHIVE',
                        'GENERIC_BINARY',
                        'UNKNOWN'
                    )
                    """);
            statement.execute("""
                    CREATE TYPE doc.doc_document_family AS ENUM (
                        'PROCUREMENT',
                        'MAIL',
                        'ATTACHMENT',
                        'KNOWLEDGE',
                        'GENERIC'
                    )
                    """);
            statement.execute("""
                    CREATE TABLE doc.doc_document (
                        id UUID PRIMARY KEY,
                        document_type doc.doc_document_type NOT NULL,
                        document_family doc.doc_document_family NOT NULL,
                        CONSTRAINT doc_document_document_type_check
                            CHECK (
                                document_type IN (
                                    'TED_NOTICE',
                                    'EMAIL',
                                    'MIME_MESSAGE',
                                    'PDF',
                                    'DOCX',
                                    'HTML',
                                    'XML_GENERIC',
                                    'TEXT',
                                    'MARKDOWN',
                                    'ZIP_ARCHIVE',
                                    'GENERIC_BINARY',
                                    'UNKNOWN'
                                )
                            ),
                        CONSTRAINT doc_document_document_family_check
                            CHECK (
                                document_family IN (
                                    'PROCUREMENT',
                                    'MAIL',
                                    'ATTACHMENT',
                                    'KNOWLEDGE',
                                    'GENERIC'
                                )
                            )
                    )
                    """);
        }
    }

    private Connection openConnection() throws SQLException {
        return DriverManager.getConnection(
                postgres.getJdbcUrl(),
                postgres.getUsername(),
                postgres.getPassword()
        );
    }
}
