package de.avatic.lcc.repositories;

import de.avatic.lcc.model.db.packaging.Packaging;
import de.avatic.lcc.repositories.packaging.PackagingRepository;
import de.avatic.lcc.repositories.pagination.SearchQueryPagination;
import de.avatic.lcc.repositories.pagination.SearchQueryResult;
import org.junit.jupiter.api.BeforeEach;
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 PackagingRepository.
 * <p>
 * Tests critical functionality across both MySQL and MSSQL:
 * - Basic CRUD operations
 * - Pagination with ORDER BY (MSSQL requirement)
 * - Filtering by materialId and supplierId
 * - Boolean literal compatibility (deprecated filtering)
 * <p>
 * Run with:
 * <pre>
 * mvn test -Dspring.profiles.active=test,mysql -Dtest=PackagingRepositoryIntegrationTest
 * mvn test -Dspring.profiles.active=test,mssql -Dtest=PackagingRepositoryIntegrationTest
 * </pre>
 */
class PackagingRepositoryIntegrationTest extends AbstractRepositoryIntegrationTest {

    @Autowired
    private PackagingRepository packagingRepository;

    private Integer testMaterialId1;
    private Integer testMaterialId2;
    private Integer testNodeId1;
    private Integer testNodeId2;
    private Integer testDimensionId1;
    private Integer testDimensionId2;

    @BeforeEach
    void setupTestData() {
        // Create test packaging dimensions (required by foreign key)
        // Dimensions: length, width, height in mm, weight in g, content_unit_count
        testDimensionId1 = createTestDimension(1000, 500, 300, 10000, 1);
        testDimensionId2 = createTestDimension(1200, 600, 400, 15000, 1);

        // Create test materials (required by foreign key)
        testMaterialId1 = createTestMaterial("TEST-MAT-001", "Test Material 1");
        testMaterialId2 = createTestMaterial("TEST-MAT-002", "Test Material 2");

        // Create test nodes (required by foreign key for supplier_node_id)
        testNodeId1 = createTestNode("Test Supplier 1", 1);
        testNodeId2 = createTestNode("Test Supplier 2", 1);
    }

    @Test
    void testInsertAndRetrieve() {
        // Given: Create packaging
        Packaging packaging = new Packaging();
        packaging.setMaterialId(testMaterialId1);
        packaging.setSupplierId(testNodeId1);
        packaging.setHuId(testDimensionId1); // Handling unit dimension
        packaging.setShuId(testDimensionId1); // Shipping handling unit dimension
        packaging.setDeprecated(false);

        // When: Insert
        Optional<Integer> packagingId = packagingRepository.insert(packaging);

        // Then: Should be inserted successfully
        assertTrue(packagingId.isPresent(), "Packaging ID should be present");
        assertTrue(packagingId.get() > 0, "Packaging ID should be positive");

        // When: Retrieve by ID
        Optional<Packaging> retrieved = packagingRepository.getById(packagingId.get());

        // Then: Should retrieve successfully
        assertTrue(retrieved.isPresent(), "Packaging should be retrievable after insert");
        assertEquals(packaging.getMaterialId(), retrieved.get().getMaterialId());
        assertEquals(packaging.getSupplierId(), retrieved.get().getSupplierId());
        assertEquals(packaging.getHuId(), retrieved.get().getHuId());
        assertEquals(packaging.getShuId(), retrieved.get().getShuId());
        assertFalse(retrieved.get().getDeprecated());
    }

    @Test
    void testUpdate() {
        // Given: Create and insert packaging
        Packaging packaging = createTestPackaging(testMaterialId1, testNodeId1, testDimensionId1, testDimensionId1, false);
        Integer packagingId = packagingRepository.insert(packaging).orElseThrow();

        // When: Update packaging
        Packaging toUpdate = packagingRepository.getById(packagingId).orElseThrow();
        toUpdate.setHuId(testDimensionId2);
        toUpdate.setShuId(testDimensionId2);
        packagingRepository.update(toUpdate);

        // Then: Verify update
        Packaging updated = packagingRepository.getById(packagingId).orElseThrow();
        assertEquals(testDimensionId2, updated.getHuId());
        assertEquals(testDimensionId2, updated.getShuId());
    }

    @Test
    void testSetDeprecatedById() {
        // Given: Create packaging
        Packaging packaging = createTestPackaging(testMaterialId1, testNodeId1, testDimensionId1, testDimensionId1, false);
        Integer packagingId = packagingRepository.insert(packaging).orElseThrow();

        // When: Deprecate
        Optional<Integer> result = packagingRepository.setDeprecatedById(packagingId);

        // Then: Should be deprecated
        assertTrue(result.isPresent());
        Packaging deprecated = packagingRepository.getById(packagingId).orElseThrow();
        assertTrue(deprecated.getDeprecated(), "Packaging should be marked as deprecated");
    }

    @Test
    void testListPackagingWithPagination() {
        // Given: Create multiple packagings
        for (int i = 0; i < 5; i++) {
            Packaging packaging = createTestPackaging(testMaterialId1, testNodeId1, testDimensionId1, testDimensionId1, false);
            packagingRepository.insert(packaging);
        }

        // When: List with pagination (page 1, size 3)
        SearchQueryPagination pagination = new SearchQueryPagination(1, 3);
        SearchQueryResult<Packaging> result = packagingRepository.listPackaging(
            null, null, false, pagination
        );

        // Then: Verify pagination works
        assertNotNull(result);
        assertNotNull(result.toList());
        assertTrue(result.toList().size() <= 3, "Should return at most 3 packagings per page");
    }

    @Test
    void testListPackagingFilterByMaterialId() {
        // Given: Create packagings with different materials
        Packaging packaging1 = createTestPackaging(testMaterialId1, testNodeId1, testDimensionId1, testDimensionId1, false);
        packagingRepository.insert(packaging1);

        Packaging packaging2 = createTestPackaging(testMaterialId2, testNodeId1, testDimensionId1, testDimensionId1, false);
        packagingRepository.insert(packaging2);

        // When: Filter by materialId
        SearchQueryPagination pagination = new SearchQueryPagination(1, 10);
        SearchQueryResult<Packaging> result = packagingRepository.listPackaging(
            testMaterialId1, null, false, pagination
        );

        // Then: Should only return material 1 packagings
        assertNotNull(result);
        for (Packaging p : result.toList()) {
            assertEquals(testMaterialId1, p.getMaterialId(), "Should only return packagings with correct materialId");
        }
    }

    @Test
    void testListPackagingFilterBySupplierId() {
        // Given: Create packagings with different suppliers
        Packaging packaging1 = createTestPackaging(testMaterialId1, testNodeId1, testDimensionId1, testDimensionId1, false);
        packagingRepository.insert(packaging1);

        Packaging packaging2 = createTestPackaging(testMaterialId1, testNodeId2, testDimensionId1, testDimensionId1, false);
        packagingRepository.insert(packaging2);

        // When: Filter by supplierId
        SearchQueryPagination pagination = new SearchQueryPagination(1, 10);
        SearchQueryResult<Packaging> result = packagingRepository.listPackaging(
            null, testNodeId1, false, pagination
        );

        // Then: Should only return supplier 1 packagings
        assertNotNull(result);
        for (Packaging p : result.toList()) {
            assertEquals(testNodeId1, p.getSupplierId(), "Should only return packagings with correct supplierId");
        }
    }

    @Test
    void testListPackagingExcludeDeprecated() {
        // Given: Create deprecated and non-deprecated packagings
        Packaging deprecated = createTestPackaging(testMaterialId1, testNodeId1, testDimensionId1, testDimensionId1, true);
        packagingRepository.insert(deprecated);

        Packaging active = createTestPackaging(testMaterialId1, testNodeId1, testDimensionId1, testDimensionId1, false);
        packagingRepository.insert(active);

        // When: List excluding deprecated
        SearchQueryPagination pagination = new SearchQueryPagination(1, 10);
        SearchQueryResult<Packaging> result = packagingRepository.listPackaging(
            null, null, true, pagination
        );

        // Then: Should not include deprecated packagings
        assertNotNull(result);
        for (Packaging p : result.toList()) {
            assertFalse(p.getDeprecated(), "Should not include deprecated packagings");
        }
    }

    @Test
    void testGetByMaterialId() {
        // Given: Create packagings for specific material
        Packaging packaging1 = createTestPackaging(testMaterialId1, testNodeId1, testDimensionId1, testDimensionId1, false);
        packagingRepository.insert(packaging1);

        Packaging packaging2 = createTestPackaging(testMaterialId1, testNodeId2, testDimensionId1, testDimensionId1, false);
        packagingRepository.insert(packaging2);

        // When: Get by materialId
        List<Packaging> packagings = packagingRepository.getByMaterialId(testMaterialId1);

        // Then: Should return all packagings for that material
        assertNotNull(packagings);
        assertTrue(packagings.size() >= 2, "Should find at least 2 packagings for material");
        for (Packaging p : packagings) {
            assertEquals(testMaterialId1, p.getMaterialId());
        }
    }

    @Test
    void testGetByMaterialIdAndSupplierId() {
        // Given: Create packaging with specific material and supplier
        Packaging packaging = createTestPackaging(testMaterialId1, testNodeId1, testDimensionId1, testDimensionId1, false);
        Integer packagingId = packagingRepository.insert(packaging).orElseThrow();

        // When: Get by materialId and supplierId
        Optional<Packaging> result = packagingRepository.getByMaterialIdAndSupplierId(testMaterialId1, testNodeId1);

        // Then: Should find the packaging
        assertTrue(result.isPresent(), "Should find packaging with correct IDs");
        assertEquals(packagingId, result.get().getId());
        assertEquals(testMaterialId1, result.get().getMaterialId());
        assertEquals(testNodeId1, result.get().getSupplierId());
    }

    @Test
    void testGetByMaterialIdAndSupplierIdNotFound() {
        // When: Get by non-existent combination
        Optional<Packaging> result = packagingRepository.getByMaterialIdAndSupplierId(99999, 99999);

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

    @Test
    void testListAllPackaging() {
        // Given: Create packagings
        Packaging packaging1 = createTestPackaging(testMaterialId1, testNodeId1, testDimensionId1, testDimensionId1, false);
        packagingRepository.insert(packaging1);

        Packaging packaging2 = createTestPackaging(testMaterialId2, testNodeId2, testDimensionId1, testDimensionId1, false);
        packagingRepository.insert(packaging2);

        // When: List all
        List<Packaging> packagings = packagingRepository.listAllPackaging();

        // Then: Should return all packagings ordered by id
        assertNotNull(packagings);
        assertFalse(packagings.isEmpty());

        // Verify ordering
        for (int i = 1; i < packagings.size(); i++) {
            assertTrue(packagings.get(i - 1).getId() <= packagings.get(i).getId(),
                "Packagings should be ordered by ID");
        }
    }

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

    private Integer createTestDimension(Integer length, Integer width, Integer height, Integer weight, Integer contentUnitCount) {
        String sql = "INSERT INTO packaging_dimension (length, width, height, weight, content_unit_count) VALUES (?, ?, ?, ?, ?)";
        executeRawSql(sql, length, width, height, weight, contentUnitCount);

        // Get last inserted ID
        String selectSql = isMysql() ? "SELECT LAST_INSERT_ID()" : "SELECT CAST(@@IDENTITY AS INT)";
        return jdbcTemplate.queryForObject(selectSql, Integer.class);
    }

    private Integer createTestMaterial(String partNumber, String name) {
        String sql = "INSERT INTO material (part_number, normalized_part_number, name, is_deprecated) VALUES (?, ?, ?, " +
                     dialectProvider.getBooleanFalse() + ")";
        executeRawSql(sql, partNumber, partNumber.toUpperCase(), name);

        // Get last inserted ID (works for both MySQL and MSSQL after ServiceConnection auto-config)
        String selectSql = isMysql() ? "SELECT LAST_INSERT_ID()" : "SELECT CAST(@@IDENTITY AS INT)";
        return jdbcTemplate.queryForObject(selectSql, Integer.class);
    }

    private Integer createTestNode(String name, Integer countryId) {
        String sql = "INSERT INTO node (name, address, geo_lat, geo_lng, is_deprecated, is_destination, is_source, is_intermediate, country_id, predecessor_required) " +
                     "VALUES (?, ?, ?, ?, " + dialectProvider.getBooleanFalse() + ", " +
                     dialectProvider.getBooleanTrue() + ", " + dialectProvider.getBooleanTrue() + ", " +
                     dialectProvider.getBooleanFalse() + ", ?, " + dialectProvider.getBooleanFalse() + ")";
        executeRawSql(sql, name, "Test Address", 50.0, 10.0, countryId);

        // Get last inserted ID
        String selectSql = isMysql() ? "SELECT LAST_INSERT_ID()" : "SELECT CAST(@@IDENTITY AS INT)";
        return jdbcTemplate.queryForObject(selectSql, Integer.class);
    }

    private Packaging createTestPackaging(Integer materialId, Integer supplierId, Integer huId, Integer shuId, boolean deprecated) {
        Packaging packaging = new Packaging();
        packaging.setMaterialId(materialId);
        packaging.setSupplierId(supplierId);
        packaging.setHuId(huId);
        packaging.setShuId(shuId);
        packaging.setDeprecated(deprecated);
        return packaging;
    }
}
