package de.avatic.lcc.repositories;

import de.avatic.lcc.model.db.materials.Material;
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 MaterialRepository.
 * <p>
 * Tests critical functionality across both MySQL and MSSQL:
 * - CRUD operations (Create, Read, Update, Delete)
 * - Pagination with ORDER BY (MSSQL requirement)
 * - Search with filters (name and part_number)
 * - Boolean literal compatibility (deprecated filtering)
 * - Bulk operations (getByPartNumbers, deleteByIds, findMissingIds)
 * <p>
 * Run with:
 * <pre>
 * mvn test -Dspring.profiles.active=test,mysql -Dtest=MaterialRepositoryIntegrationTest
 * mvn test -Dspring.profiles.active=test,mssql -Dtest=MaterialRepositoryIntegrationTest
 * </pre>
 */
class MaterialRepositoryIntegrationTest extends AbstractRepositoryIntegrationTest {

    @Autowired
    private MaterialRepository materialRepository;

    @Test
    void testInsertAndRetrieve() {
        // Given: Create material
        Material material = createTestMaterial("TEST-001", "Test Material 1");

        // When: Insert
        materialRepository.insert(material);

        // When: Retrieve by part number
        Optional<Material> retrieved = materialRepository.getByPartNumber("TEST-001");

        // Then: Should retrieve successfully
        assertTrue(retrieved.isPresent(), "Material should be retrievable after insert");
        assertEquals("TEST-001", retrieved.get().getPartNumber());
        assertEquals("Test Material 1", retrieved.get().getName());
        assertFalse(retrieved.get().getDeprecated());
    }

    @Test
    void testUpdate() {
        // Given: Insert material
        Material material = createTestMaterial("TEST-002", "Original Name");
        materialRepository.insert(material);

        // When: Update material
        Material toUpdate = materialRepository.getByPartNumber("TEST-002").orElseThrow();
        toUpdate.setName("Updated Name");
        toUpdate.setHsCode("12345678901");
        materialRepository.update(toUpdate);

        // Then: Verify update
        Material updated = materialRepository.getById(toUpdate.getId()).orElseThrow();
        assertEquals("Updated Name", updated.getName());
        assertEquals("12345678901", updated.getHsCode());
    }

    @Test
    void testUpdateByPartNumber() {
        // Given: Insert material
        Material material = createTestMaterial("TEST-003", "Original Name");
        materialRepository.insert(material);

        // When: Update by part number
        Material toUpdate = materialRepository.getByPartNumber("TEST-003").orElseThrow();
        toUpdate.setName("Updated via PartNumber");
        materialRepository.updateByPartNumber(toUpdate);

        // Then: Verify update
        Material updated = materialRepository.getByPartNumber("TEST-003").orElseThrow();
        assertEquals("Updated via PartNumber", updated.getName());
    }

    @Test
    void testSetDeprecatedById() {
        // Given: Insert material
        Material material = createTestMaterial("TEST-004", "Material to Deprecate");
        materialRepository.insert(material);
        Integer materialId = materialRepository.getByPartNumber("TEST-004").orElseThrow().getId();

        // When: Deprecate
        Optional<Integer> result = materialRepository.setDeprecatedById(materialId);

        // Then: Should be deprecated
        assertTrue(result.isPresent());

        // getById() excludes deprecated
        Optional<Material> deprecated = materialRepository.getById(materialId);
        assertFalse(deprecated.isPresent(), "getById() should exclude deprecated materials");

        // But getByIdIncludeDeprecated() should find it
        Optional<Material> includingDeprecated = materialRepository.getByIdIncludeDeprecated(materialId);
        assertTrue(includingDeprecated.isPresent(), "getByIdIncludeDeprecated() should find deprecated materials");
        assertTrue(includingDeprecated.get().getDeprecated());
    }

    @Test
    void testDeleteById() {
        // Given: Insert material
        Material material = createTestMaterial("TEST-005", "Material to Delete");
        materialRepository.insert(material);
        Integer materialId = materialRepository.getByPartNumber("TEST-005").orElseThrow().getId();

        // When: Delete (soft delete - sets deprecated)
        materialRepository.deleteById(materialId);

        // Then: Should be deprecated
        Optional<Material> deleted = materialRepository.getById(materialId);
        assertFalse(deleted.isPresent(), "Deleted material should not be retrievable via getById()");

        Optional<Material> includingDeleted = materialRepository.getByIdIncludeDeprecated(materialId);
        assertTrue(includingDeleted.isPresent());
        assertTrue(includingDeleted.get().getDeprecated());
    }

    @Test
    void testListMaterialsWithPagination() {
        // Given: Insert multiple materials
        for (int i = 1; i <= 5; i++) {
            Material material = createTestMaterial("PAGE-" + String.format("%03d", i), "Pagination Material " + i);
            materialRepository.insert(material);
        }

        // When: List with pagination (page 1, size 3)
        SearchQueryPagination pagination = new SearchQueryPagination(1, 3);
        SearchQueryResult<Material> result = materialRepository.listMaterials(
            Optional.empty(), false, pagination
        );

        // Then: Verify pagination works
        assertNotNull(result);
        assertNotNull(result.toList());
        assertTrue(result.toList().size() <= 3, "Should return at most 3 materials per page");
        assertTrue(result.getTotalElements() >= 5, "Should have at least 5 materials total");
    }

    @Test
    void testListMaterialsWithFilter() {
        // Given: Insert materials with different names
        Material material1 = createTestMaterial("FILTER-001", "Special Widget");
        materialRepository.insert(material1);

        Material material2 = createTestMaterial("FILTER-002", "Normal Component");
        materialRepository.insert(material2);

        Material material3 = createTestMaterial("FILTER-003", "Special Gadget");
        materialRepository.insert(material3);

        // When: Search for "Special"
        SearchQueryPagination pagination = new SearchQueryPagination(1, 10);
        SearchQueryResult<Material> result = materialRepository.listMaterials(
            Optional.of("SPECIAL"), false, pagination
        );

        // Then: Should find materials with "Special" in name
        assertNotNull(result);
        assertTrue(result.toList().size() >= 2, "Should find at least 2 materials with 'Special'");

        for (Material m : result.toList()) {
            boolean matches = m.getName().toUpperCase().contains("SPECIAL") ||
                             m.getPartNumber().toUpperCase().contains("SPECIAL");
            assertTrue(matches, "Material should match filter");
        }
    }

    @Test
    void testListMaterialsExcludeDeprecated() {
        // Given: Insert deprecated and active materials
        Material deprecated = createTestMaterial("DEPR-001", "Deprecated Material");
        deprecated.setDeprecated(true);
        materialRepository.insert(deprecated);

        Material active = createTestMaterial("ACTIVE-001", "Active Material");
        materialRepository.insert(active);

        // When: List excluding deprecated
        SearchQueryPagination pagination = new SearchQueryPagination(1, 10);
        SearchQueryResult<Material> result = materialRepository.listMaterials(
            Optional.empty(), true, pagination
        );

        // Then: Should not include deprecated materials
        assertNotNull(result);
        for (Material m : result.toList()) {
            assertFalse(m.getDeprecated(), "Should not include deprecated materials");
        }
    }

    @Test
    void testListAllMaterials() {
        // Given: Insert materials
        Material material1 = createTestMaterial("ALL-001", "Material 1");
        materialRepository.insert(material1);

        Material material2 = createTestMaterial("ALL-002", "Material 2");
        materialRepository.insert(material2);

        // When: List all
        List<Material> materials = materialRepository.listAllMaterials();

        // Then: Should return all materials ordered by normalized_part_number
        assertNotNull(materials);
        assertFalse(materials.isEmpty());

        // Verify ordering
        for (int i = 1; i < materials.size(); i++) {
            String prev = materials.get(i - 1).getNormalizedPartNumber();
            String current = materials.get(i).getNormalizedPartNumber();
            assertTrue(prev.compareTo(current) <= 0,
                "Materials should be ordered by normalized_part_number");
        }
    }

    @Test
    void testGetByPartNumber() {
        // Given: Insert material
        Material material = createTestMaterial("BYPART-001", "Get By Part");
        materialRepository.insert(material);

        // When: Get by part number
        Optional<Material> result = materialRepository.getByPartNumber("BYPART-001");

        // Then: Should find material
        assertTrue(result.isPresent());
        assertEquals("BYPART-001", result.get().getPartNumber());
        assertEquals("Get By Part", result.get().getName());
    }

    @Test
    void testGetByPartNumberNotFound() {
        // When: Get by non-existent part number
        Optional<Material> result = materialRepository.getByPartNumber("NONEXISTENT-999");

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

    @Test
    void testGetByPartNumbers() {
        // Given: Insert multiple materials
        Material material1 = createTestMaterial("BULK-001", "Bulk Material 1");
        materialRepository.insert(material1);

        Material material2 = createTestMaterial("BULK-002", "Bulk Material 2");
        materialRepository.insert(material2);

        Material material3 = createTestMaterial("BULK-003", "Bulk Material 3");
        materialRepository.insert(material3);

        // When: Get by part numbers
        List<String> partNumbers = List.of("BULK-001", "BULK-002", "NONEXISTENT");
        List<Material> materials = materialRepository.getByPartNumbers(partNumbers);

        // Then: Should find existing materials (2 out of 3 part numbers)
        assertNotNull(materials);
        assertTrue(materials.size() >= 2, "Should find at least 2 materials");

        List<String> foundPartNumbers = materials.stream()
            .map(Material::getPartNumber)
            .toList();
        assertTrue(foundPartNumbers.contains("BULK-001"));
        assertTrue(foundPartNumbers.contains("BULK-002"));
    }

    @Test
    void testGetByPartNumbersEmptyList() {
        // When: Get by empty list
        List<Material> materials = materialRepository.getByPartNumbers(List.of());

        // Then: Should return empty list
        assertNotNull(materials);
        assertTrue(materials.isEmpty());
    }

    @Test
    void testDeleteByIds() {
        // Given: Insert multiple materials
        Material material1 = createTestMaterial("DELETE-001", "To Delete 1");
        materialRepository.insert(material1);
        Integer id1 = materialRepository.getByPartNumber("DELETE-001").orElseThrow().getId();

        Material material2 = createTestMaterial("DELETE-002", "To Delete 2");
        materialRepository.insert(material2);
        Integer id2 = materialRepository.getByPartNumber("DELETE-002").orElseThrow().getId();

        // When: Delete by IDs
        materialRepository.deleteByIds(List.of(id1, id2));

        // Then: Should be deprecated
        assertFalse(materialRepository.getById(id1).isPresent());
        assertFalse(materialRepository.getById(id2).isPresent());

        // But should exist with deprecated flag
        assertTrue(materialRepository.getByIdIncludeDeprecated(id1).orElseThrow().getDeprecated());
        assertTrue(materialRepository.getByIdIncludeDeprecated(id2).orElseThrow().getDeprecated());
    }

    @Test
    void testFindMissingIds() {
        // Given: Insert some materials
        Material material1 = createTestMaterial("MISSING-001", "Material 1");
        materialRepository.insert(material1);
        Integer existingId = materialRepository.getByPartNumber("MISSING-001").orElseThrow().getId();

        // When: Check for missing IDs
        List<Integer> idsToCheck = List.of(existingId, 99999, 99998);
        List<Integer> missingIds = materialRepository.findMissingIds(idsToCheck);

        // Then: Should return only non-existent IDs
        assertNotNull(missingIds);
        assertEquals(2, missingIds.size(), "Should find 2 missing IDs");
        assertTrue(missingIds.contains(99999));
        assertTrue(missingIds.contains(99998));
        assertFalse(missingIds.contains(existingId), "Existing ID should not be in missing list");
    }

    @Test
    void testFindMissingIdsEmptyList() {
        // When: Check empty list
        List<Integer> missingIds = materialRepository.findMissingIds(List.of());

        // Then: Should return empty list
        assertNotNull(missingIds);
        assertTrue(missingIds.isEmpty());
    }

    // ========== Helper Methods ==========

    private Material createTestMaterial(String partNumber, String name) {
        Material material = new Material();
        material.setPartNumber(partNumber);
        material.setNormalizedPartNumber(partNumber.toUpperCase());
        material.setName(name);
        material.setHsCode(null);
        material.setDeprecated(false);
        return material;
    }
}
