package de.avatic.lcc.repositories;

import de.avatic.lcc.model.db.country.Country;
import de.avatic.lcc.model.db.country.IsoCode;
import de.avatic.lcc.repositories.country.CountryRepository;
import de.avatic.lcc.repositories.pagination.SearchQueryPagination;
import de.avatic.lcc.repositories.pagination.SearchQueryResult;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;

import java.util.List;
import java.util.Optional;

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

/**
 * Integration tests for CountryRepository.
 * <p>
 * Tests critical functionality across both MySQL and MSSQL:
 * - Basic retrieval operations (getById, getByIsoCode)
 * - Pagination with ORDER BY (MSSQL requirement)
 * - Search with filters
 * - Boolean literal compatibility (deprecated filtering)
 * <p>
 * Countries are populated via Flyway migrations, so no insert tests are needed.
 * <p>
 * Run with:
 * <pre>
 * mvn test -Dspring.profiles.active=test,mysql -Dtest=CountryRepositoryIntegrationTest
 * mvn test -Dspring.profiles.active=test,mssql -Dtest=CountryRepositoryIntegrationTest
 * </pre>
 */
class CountryRepositoryIntegrationTest extends AbstractRepositoryIntegrationTest {

    @Autowired
    private CountryRepository countryRepository;

    @Test
    void testGetById() {
        // Given: Country with id=1 should exist (from Flyway migrations)
        Integer countryId = 1;

        // When: Retrieve by ID
        Optional<Country> result = countryRepository.getById(countryId);

        // Then: Should find the country
        assertTrue(result.isPresent(), "Country with id=1 should exist");
        assertEquals(countryId, result.get().getId());
        assertNotNull(result.get().getIsoCode());
        assertNotNull(result.get().getName());
    }

    @Test
    void testGetByIdNotFound() {
        // Given: Non-existent country ID
        Integer nonExistentId = 99999;

        // When: Retrieve by ID
        Optional<Country> result = countryRepository.getById(nonExistentId);

        // Then: Should return empty
        assertFalse(result.isPresent(), "Should not find country with non-existent ID");
    }

    @Test
    void testGetByIsoCode() {
        // Given: Germany should exist (from Flyway migrations)
        IsoCode isoCode = IsoCode.DE;

        // When: Retrieve by ISO code
        Optional<Country> result = countryRepository.getByIsoCode(isoCode);

        // Then: Should find Germany
        assertTrue(result.isPresent(), "Should find country with ISO code DE");
        assertEquals(IsoCode.DE, result.get().getIsoCode());
        assertTrue(result.get().getName().contains("German") || result.get().getName().contains("Deutschland"));
    }

    @Test
    void testGetByIsoCodeNotFound() {
        // Given: Invalid ISO code that shouldn't exist
        // Note: This will throw IllegalArgumentException if the enum doesn't exist
        // So we test with a valid enum that might not be in the database

        // When/Then: Just verify the method works with any valid IsoCode
        Optional<Country> result = countryRepository.getByIsoCode(IsoCode.US);

        // We don't assert empty here because US might exist in migrations
        // Just verify it doesn't throw an exception
        assertNotNull(result);
    }

    @Test
    void testListAllCountries() {
        // When: List all countries
        List<Country> countries = countryRepository.listAllCountries();

        // Then: Should have countries from Flyway migrations
        assertNotNull(countries);
        assertFalse(countries.isEmpty(), "Should have countries from migrations");

        // Verify ordering by ISO code
        for (int i = 1; i < countries.size(); i++) {
            String prevIso = countries.get(i - 1).getIsoCode().name();
            String currentIso = countries.get(i).getIsoCode().name();
            assertTrue(prevIso.compareTo(currentIso) <= 0,
                "Countries should be ordered by ISO code");
        }
    }

    @Test
    void testListCountriesWithPagination() {
        // Given: Pagination settings (page 1, size 5)
        SearchQueryPagination pagination = new SearchQueryPagination(1, 5);

        // When: List countries with pagination
        SearchQueryResult<Country> result = countryRepository.listCountries(
            Optional.empty(), false, pagination
        );

        // Then: Verify pagination works
        assertNotNull(result);
        assertNotNull(result.toList());
        assertTrue(result.toList().size() <= 5, "Should return at most 5 countries per page");
        assertTrue(result.getTotalElements() > 0, "Total elements should be positive");
    }

    @Test
    void testListCountriesWithFilter() {
        // Given: Filter for "German" or "Deutschland"
        String filter = "German";

        // When: List countries with filter
        SearchQueryResult<Country> result = countryRepository.listCountries(
            Optional.of(filter), false
        );

        // Then: Should find matching countries
        assertNotNull(result);
        assertFalse(result.toList().isEmpty(), "Should find countries matching 'German'");

        // Verify all results match the filter (name, iso_code, or region_code)
        for (Country country : result.toList()) {
            boolean matches = country.getName().toLowerCase().contains(filter.toLowerCase()) ||
                             country.getIsoCode().name().toLowerCase().contains(filter.toLowerCase()) ||
                             country.getRegionCode().name().toLowerCase().contains(filter.toLowerCase());
            assertTrue(matches, "Country should match filter: " + country.getName());
        }
    }

    @Test
    void testListCountriesWithFilterAndPagination() {
        // Given: Filter + Pagination
        String filter = "a"; // Should match many countries
        SearchQueryPagination pagination = new SearchQueryPagination(1, 3);

        // When: List countries with filter and pagination
        SearchQueryResult<Country> result = countryRepository.listCountries(
            Optional.of(filter), false, pagination
        );

        // Then: Should apply both filter and pagination
        assertNotNull(result);
        assertTrue(result.toList().size() <= 3, "Should respect pagination limit");

        for (Country country : result.toList()) {
            boolean matches = country.getName().toLowerCase().contains(filter.toLowerCase()) ||
                             country.getIsoCode().name().toLowerCase().contains(filter.toLowerCase()) ||
                             country.getRegionCode().name().toLowerCase().contains(filter.toLowerCase());
            assertTrue(matches, "Country should match filter");
        }
    }

    @Test
    void testBooleanLiteralCompatibility() {
        // This test verifies that boolean literals work across MySQL (TRUE/FALSE) and MSSQL (1/0)

        // When: List countries excluding deprecated
        SearchQueryResult<Country> result = countryRepository.listCountries(
            Optional.empty(), true // excludeDeprecated = true
        );

        // Then: Should only return non-deprecated countries
        assertNotNull(result);
        for (Country country : result.toList()) {
            assertFalse(country.getDeprecated(),
                "Should not include deprecated countries when excludeDeprecated=true");
        }
    }

    @Test
    void testGetByIsoCodes() {
        // Given: List of ISO codes
        List<IsoCode> isoCodes = List.of(IsoCode.DE, IsoCode.FR, IsoCode.US);

        // When: Get countries by ISO codes
        List<Country> countries = countryRepository.getByIsoCodes(isoCodes);

        // Then: Should return matching countries
        assertNotNull(countries);
        assertFalse(countries.isEmpty(), "Should find countries");

        // Verify all returned countries are in the requested list
        for (Country country : countries) {
            assertTrue(isoCodes.contains(country.getIsoCode()),
                "Returned country should be in requested ISO codes");
        }
    }

    @Test
    void testGetByIsoCodesEmptyList() {
        // Given: Empty list
        List<IsoCode> emptyList = List.of();

        // When: Get countries by empty ISO codes
        List<Country> countries = countryRepository.getByIsoCodes(emptyList);

        // Then: Should return empty list
        assertNotNull(countries);
        assertTrue(countries.isEmpty(), "Should return empty list for empty input");
    }
}
