package de.avatic.lcc.repositories;

import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;

import java.util.List;

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

/**
 * Integration tests for NomenclatureRepository.
 * <p>
 * Tests critical functionality across both MySQL and MSSQL:
 * - Search with LIKE and CONCAT functions
 * - Pagination (LIMIT/OFFSET vs OFFSET/FETCH)
 * - ORDER BY compatibility
 * <p>
 * Run with:
 * <pre>
 * mvn test -Dspring.profiles.active=test,mysql -Dtest=NomenclatureRepositoryIntegrationTest
 * mvn test -Dspring.profiles.active=test,mssql -Dtest=NomenclatureRepositoryIntegrationTest
 * </pre>
 */
class NomenclatureRepositoryIntegrationTest extends AbstractRepositoryIntegrationTest {

    @Autowired
    private NomenclatureRepository nomenclatureRepository;

    @BeforeEach
    void setupTestData() {
        // Clean up nomenclature table
        jdbcTemplate.update("DELETE FROM nomenclature");

        // Insert test HS codes into the nomenclature table (only hs_code column exists)
        String sql = "INSERT INTO nomenclature (hs_code) VALUES (?)";

        executeRawSql(sql, "8471300000");
        executeRawSql(sql, "8471410000");
        executeRawSql(sql, "8471420000");
        executeRawSql(sql, "8471490000");
        executeRawSql(sql, "8471500000");
        executeRawSql(sql, "8471600000");
        executeRawSql(sql, "8471700000");
        executeRawSql(sql, "8471800000");
        executeRawSql(sql, "9403200000");
        executeRawSql(sql, "9403300000");
    }

    @Test
    void testSearchHsCodeWithExactMatch() {
        // Given: Search for exact HS code prefix
        String search = "847130";

        // When: Search
        List<String> results = nomenclatureRepository.searchHsCode(search);

        // Then: Should find matching HS codes starting with 847130
        assertNotNull(results);
        assertFalse(results.isEmpty(), "Should find HS codes starting with 847130");
        assertTrue(results.stream().anyMatch(code -> code.startsWith("847130")),
            "Results should contain codes starting with 847130");
    }

    @Test
    void testSearchHsCodeWithPartialMatch() {
        // Given: Search for partial HS code
        String search = "8471";

        // When: Search
        List<String> results = nomenclatureRepository.searchHsCode(search);

        // Then: Should find all HS codes starting with 8471
        assertNotNull(results);
        assertTrue(results.size() >= 7, "Should find at least 7 codes starting with 8471");

        // Verify all results start with the search term
        for (String code : results) {
            assertTrue(code.startsWith(search),
                "All results should start with search term: " + code);
        }
    }

    @Test
    void testSearchHsCodeOrdering() {
        // Given: Search for codes
        String search = "8471";

        // When: Search
        List<String> results = nomenclatureRepository.searchHsCode(search);

        // Then: Should be ordered by hs_code
        assertNotNull(results);
        assertFalse(results.isEmpty());

        // Verify ordering
        for (int i = 1; i < results.size(); i++) {
            assertTrue(results.get(i - 1).compareTo(results.get(i)) <= 0,
                "Results should be ordered by hs_code");
        }
    }

    @Test
    void testSearchHsCodeWithPagination() {
        // Given: Search that returns many results
        String search = "8471";

        // When: Search (limit is 10 as per repository implementation)
        List<String> results = nomenclatureRepository.searchHsCode(search);

        // Then: Should respect pagination limit
        assertNotNull(results);
        assertTrue(results.size() <= 10, "Should return at most 10 results (pagination limit)");
    }

    @Test
    void testSearchHsCodeNotFound() {
        // Given: Search for non-existent HS code
        String search = "9999";

        // When: Search
        List<String> results = nomenclatureRepository.searchHsCode(search);

        // Then: Should return empty list
        assertNotNull(results);
        assertTrue(results.isEmpty(), "Should return empty list for non-existent HS code");
    }

    @Test
    void testSearchHsCodeDifferentPrefix() {
        // Given: Search for different category (furniture)
        String search = "9403";

        // When: Search
        List<String> results = nomenclatureRepository.searchHsCode(search);

        // Then: Should find furniture codes
        assertNotNull(results);
        assertTrue(results.size() >= 2, "Should find at least 2 codes starting with 9403");
        assertTrue(results.stream().allMatch(code -> code.startsWith("9403")),
            "All results should start with 9403");
    }

    @Test
    void testSearchHsCodeConcatFunction() {
        // This test verifies that the CONCAT function works across both databases
        // MySQL: CONCAT(?, '%')
        // MSSQL: ? + '%'

        // Given: Search with single digit
        String search = "8";

        // When: Search
        List<String> results = nomenclatureRepository.searchHsCode(search);

        // Then: Should find all codes starting with 8
        assertNotNull(results);
        assertTrue(results.size() >= 7, "Should find at least 7 codes starting with 8");
        assertTrue(results.stream().allMatch(code -> code.startsWith("8")),
            "All results should start with 8");
    }
}
