package de.avatic.lcc.repositories;

import de.avatic.lcc.database.dialect.SqlDialectProvider;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;

import static org.junit.jupiter.api.Assertions.*;

/**
 * Smoke test to verify TestContainers and Flyway setup.
 * <p>
 * Validates:
 * - TestContainers starts correctly
 * - Flyway migrations run successfully
 * - Database contains expected test data
 * - Correct SqlDialectProvider is loaded
 * <p>
 * Run with:
 * <pre>
 * mvn test -Dspring.profiles.active=test,mysql -Dtest=DatabaseConfigurationSmokeTest
 * mvn test -Dspring.profiles.active=test,mssql -Dtest=DatabaseConfigurationSmokeTest
 * </pre>
 */
class DatabaseConfigurationSmokeTest extends AbstractRepositoryIntegrationTest {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Autowired
    private SqlDialectProvider dialectProvider;

    @Test
    void testDatabaseConnectionIsEstablished() {
        // When: Query database
        Integer result = jdbcTemplate.queryForObject("SELECT 1", Integer.class);

        // Then: Connection works
        assertNotNull(result);
        assertEquals(1, result);
    }

    @Test
    void testFlywayMigrationsRanSuccessfully() {
        // When: Check if core tables exist
        Integer propertySetCount = jdbcTemplate.queryForObject(
                "SELECT COUNT(*) FROM property_set", Integer.class);

        // Then: Table exists (migrations ran)
        assertNotNull(propertySetCount);
    }

    @Test
    void testCountriesWereLoadedFromMigrations() {
        // When: Count countries
        Integer countryCount = jdbcTemplate.queryForObject(
                "SELECT COUNT(*) FROM country", Integer.class);

        // Then: Countries exist (V4__Country.sql ran)
        assertNotNull(countryCount);
        assertTrue(countryCount > 0, "Countries should be loaded from V4__Country.sql migration");
        System.out.println("Found " + countryCount + " countries in database");
    }

    @Test
    void testNodesWereLoadedFromMigrations() {
        // When: Count nodes
        Integer nodeCount = jdbcTemplate.queryForObject(
                "SELECT COUNT(*) FROM node", Integer.class);

        // Then: Nodes exist (V5__Nodes.sql ran)
        assertNotNull(nodeCount);
        assertTrue(nodeCount > 0, "Nodes should be loaded from V5__Nodes.sql migration");
        System.out.println("Found " + nodeCount + " nodes in database");
    }

    @Test
    void testCorrectSqlDialectProviderIsLoaded() {
        // Debug: Print active profiles
        String[] activeProfiles = jdbcTemplate.getDataSource() != null ?
            new String[]{getDatabaseProfile()} : new String[]{};
        System.out.println("Active Spring profiles from getDatabaseProfile(): " + getDatabaseProfile());
        System.out.println("System property spring.profiles.active: " + System.getProperty("spring.profiles.active"));

        // When: Check which dialect provider is active
        String booleanTrue = dialectProvider.getBooleanTrue();

        // Then: Correct provider based on profile
        if (isMysql()) {
            assertEquals("TRUE", booleanTrue, "MySQL should use TRUE literal");
        } else if (isMssql()) {
            assertEquals("1", booleanTrue, "MSSQL should use 1 literal");
        }

        System.out.println("Active database profile: " + getDatabaseProfile());
        System.out.println("Dialect provider class: " + dialectProvider.getClass().getSimpleName());
    }

    @Test
    void testBooleanLiteralInQuery() {
        // When: Query with boolean literal from dialect provider
        String query = "SELECT COUNT(*) FROM node WHERE is_deprecated = " +
                dialectProvider.getBooleanFalse();
        Integer activeNodeCount = jdbcTemplate.queryForObject(query, Integer.class);

        // Then: Query executes without syntax error
        assertNotNull(activeNodeCount);
        System.out.println("Active (non-deprecated) nodes: " + activeNodeCount);
    }

    @Test
    void testPaginationQuery() {
        // When: Execute query with pagination (requires ORDER BY in MSSQL)
        String paginationClause = dialectProvider.buildPaginationClause(5, 0);
        Object[] paginationParams = dialectProvider.getPaginationParameters(5, 0);

        String query = "SELECT id FROM node ORDER BY id " + paginationClause;
        var nodeIds = jdbcTemplate.query(query,
                (rs, rowNum) -> rs.getInt("id"),
                paginationParams[0], paginationParams[1]);

        // Then: Query executes successfully and returns up to 5 results
        assertNotNull(nodeIds);
        assertFalse(nodeIds.isEmpty(), "Should return at least one node");
        assertTrue(nodeIds.size() <= 5, "Should return at most 5 nodes");
        System.out.println("Returned " + nodeIds.size() + " nodes with pagination: " + nodeIds);
    }
}
