package de.avatic.lcc.repositories.packaging;

import de.avatic.lcc.model.db.properties.PackagingProperty;
import de.avatic.lcc.model.db.properties.PropertyDataType;
import de.avatic.lcc.model.db.properties.PropertyType;
import de.avatic.lcc.repositories.AbstractRepositoryIntegrationTest;
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 PackagingPropertiesRepository.
 * <p>
 * Tests critical functionality across both MySQL and MSSQL:
 * - UPSERT operations (ON DUPLICATE KEY UPDATE vs MERGE)
 * - Complex JOIN queries with property types
 * - Boolean literals (TRUE/FALSE vs 1/0)
 * <p>
 * Run with:
 * <pre>
 * mvn test -Dspring.profiles.active=test,mysql -Dtest=PackagingPropertiesRepositoryIntegrationTest
 * mvn test -Dspring.profiles.active=test,mssql -Dtest=PackagingPropertiesRepositoryIntegrationTest
 * </pre>
 */
class PackagingPropertiesRepositoryIntegrationTest extends AbstractRepositoryIntegrationTest {

    @Autowired
    private PackagingPropertiesRepository packagingPropertiesRepository;

    private Integer testPackagingId;
    private Integer testTypeWeightId;
    private Integer testTypeDimensionId;
    private Integer testTypeMaterialId;

    @BeforeEach
    void setupTestData() {
        // Clean up in correct order (respect FK constraints)
        jdbcTemplate.update("DELETE FROM packaging_property");
        jdbcTemplate.update("DELETE FROM packaging");
        jdbcTemplate.update("DELETE FROM packaging_dimension");
        jdbcTemplate.update("DELETE FROM material");
        jdbcTemplate.update("DELETE FROM node_predecessor_entry");
        jdbcTemplate.update("DELETE FROM node_predecessor_chain");
        jdbcTemplate.update("DELETE FROM container_rate"); // Must delete before node due to FK
        jdbcTemplate.update("DELETE FROM node");
        jdbcTemplate.update("DELETE FROM packaging_property_type");

        // Create property types
        testTypeWeightId = createPropertyType("Weight", "CURRENCY", "WEIGHT", true, "^[0-9]+(\\.[0-9]+)?$");
        testTypeDimensionId = createPropertyType("Dimension", "TEXT", "DIMENSION", false, null);
        testTypeMaterialId = createPropertyType("Material", "TEXT", "MATERIAL", false, null);

        // Create test packaging
        testPackagingId = createPackaging("Box-001", "Cardboard Box");

        // Create some properties
        createPackagingProperty(testPackagingId, testTypeWeightId, "10.5");
        createPackagingProperty(testPackagingId, testTypeDimensionId, "30x40x50");
    }

    @Test
    void testGetByPackagingId() {
        // When: Get properties by packaging ID
        List<PackagingProperty> properties = packagingPropertiesRepository.getByPackagingId(testPackagingId);

        // Then: Should find 2 properties
        assertNotNull(properties);
        assertEquals(2, properties.size());
        assertTrue(properties.stream().anyMatch(p -> "Weight".equals(p.getType().getName())));
        assertTrue(properties.stream().anyMatch(p -> "Dimension".equals(p.getType().getName())));
    }

    @Test
    void testGetByPackagingIdEmpty() {
        // Given: Packaging with no properties
        Integer emptyPackagingId = createPackaging("Box-002", "Empty Box");

        // When: Get properties
        List<PackagingProperty> properties = packagingPropertiesRepository.getByPackagingId(emptyPackagingId);

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

    @Test
    void testGetByPackagingIdAndType() {
        // When: Get specific property by packaging and type
        Optional<PackagingProperty> property = packagingPropertiesRepository.getByPackagingIdAndType(
            testPackagingId, "WEIGHT");

        // Then: Should find weight property
        assertTrue(property.isPresent());
        assertEquals("Weight", property.get().getType().getName());
        assertEquals("10.5", property.get().getValue());
        assertEquals(testPackagingId, property.get().getPackagingId());
    }

    @Test
    void testGetByPackagingIdAndTypeNotFound() {
        // When: Get non-existent property
        Optional<PackagingProperty> property = packagingPropertiesRepository.getByPackagingIdAndType(
            testPackagingId, "NONEXISTENT");

        // Then: Should not find
        assertFalse(property.isPresent());
    }

    @Test
    void testGetByPackagingIdAndTypeDifferentPackaging() {
        // Given: Different packaging
        Integer otherPackagingId = createPackaging("Box-003", "Other Box");

        // When: Get property from wrong packaging
        Optional<PackagingProperty> property = packagingPropertiesRepository.getByPackagingIdAndType(
            otherPackagingId, "WEIGHT");

        // Then: Should not find
        assertFalse(property.isPresent());
    }

    @Test
    void testListTypes() {
        // When: List all property types
        List<PropertyType> types = packagingPropertiesRepository.listTypes();

        // Then: Should find all 3 types
        assertNotNull(types);
        assertEquals(3, types.size());
        assertTrue(types.stream().anyMatch(t -> "Weight".equals(t.getName())));
        assertTrue(types.stream().anyMatch(t -> "Dimension".equals(t.getName())));
        assertTrue(types.stream().anyMatch(t -> "Material".equals(t.getName())));
    }

    @Test
    void testListTypesProperties() {
        // When: List types
        List<PropertyType> types = packagingPropertiesRepository.listTypes();

        // Then: Verify type properties
        PropertyType weightType = types.stream()
            .filter(t -> "Weight".equals(t.getName()))
            .findFirst()
            .orElseThrow();

        assertEquals(PropertyDataType.CURRENCY, weightType.getDataType());
        assertEquals("WEIGHT", weightType.getExternalMappingId());
        assertTrue(weightType.getRequired());
        assertEquals("^[0-9]+(\\.[0-9]+)?$", weightType.getValidationRule());
    }

    @Test
    void testUpdateInsert() {
        // Given: New property for Material
        // When: Update (insert)
        packagingPropertiesRepository.update(testPackagingId, testTypeMaterialId, "Cardboard");

        // Then: Should be inserted
        Optional<PackagingProperty> property = packagingPropertiesRepository.getByPackagingIdAndType(
            testPackagingId, "MATERIAL");
        assertTrue(property.isPresent());
        assertEquals("Cardboard", property.get().getValue());
    }

    @Test
    void testUpdateUpsert() {
        // Given: Existing Weight property with value "10.5"
        // When: Update with new value
        packagingPropertiesRepository.update(testPackagingId, testTypeWeightId, "15.0");

        // Then: Should be updated
        Optional<PackagingProperty> property = packagingPropertiesRepository.getByPackagingIdAndType(
            testPackagingId, "WEIGHT");
        assertTrue(property.isPresent());
        assertEquals("15.0", property.get().getValue());

        // Should still have only 2 properties (not creating duplicate)
        List<PackagingProperty> allProperties = packagingPropertiesRepository.getByPackagingId(testPackagingId);
        assertEquals(2, allProperties.size());
    }

    @Test
    void testUpdateWithTypeId() {
        // When: Update using Integer type ID
        packagingPropertiesRepository.update(testPackagingId, testTypeMaterialId, "Plastic");

        // Then: Should work
        Optional<PackagingProperty> property = packagingPropertiesRepository.getByPackagingIdAndType(
            testPackagingId, "MATERIAL");
        assertTrue(property.isPresent());
        assertEquals("Plastic", property.get().getValue());
    }

    @Test
    void testUpdateWithTypeIdString() {
        // When: Update using String type ID
        packagingPropertiesRepository.update(testPackagingId, String.valueOf(testTypeMaterialId), "Wood");

        // Then: Should work
        Optional<PackagingProperty> property = packagingPropertiesRepository.getByPackagingIdAndType(
            testPackagingId, "MATERIAL");
        assertTrue(property.isPresent());
        assertEquals("Wood", property.get().getValue());
    }

    @Test
    void testGetTypeIdByMappingId() {
        // When: Get type ID by mapping ID
        Integer typeId = packagingPropertiesRepository.getTypeIdByMappingId("WEIGHT");

        // Then: Should find type
        assertNotNull(typeId);
        assertEquals(testTypeWeightId, typeId);
    }

    @Test
    void testGetTypeIdByMappingIdDifferentTypes() {
        // When: Get different type IDs
        Integer weightId = packagingPropertiesRepository.getTypeIdByMappingId("WEIGHT");
        Integer dimensionId = packagingPropertiesRepository.getTypeIdByMappingId("DIMENSION");
        Integer materialId = packagingPropertiesRepository.getTypeIdByMappingId("MATERIAL");

        // Then: Should find all and be different
        assertNotNull(weightId);
        assertNotNull(dimensionId);
        assertNotNull(materialId);
        assertNotEquals(weightId, dimensionId);
        assertNotEquals(weightId, materialId);
        assertNotEquals(dimensionId, materialId);
    }

    @Test
    void testUpdateMultipleProperties() {
        // Given: Packaging with properties
        // When: Update multiple properties
        packagingPropertiesRepository.update(testPackagingId, testTypeWeightId, "20.0");
        packagingPropertiesRepository.update(testPackagingId, testTypeDimensionId, "50x60x70");
        packagingPropertiesRepository.update(testPackagingId, testTypeMaterialId, "Metal");

        // Then: Should have all 3 properties
        List<PackagingProperty> properties = packagingPropertiesRepository.getByPackagingId(testPackagingId);
        assertEquals(3, properties.size());

        // Verify values
        assertEquals("20.0", properties.stream()
            .filter(p -> "Weight".equals(p.getType().getName()))
            .findFirst().orElseThrow().getValue());
        assertEquals("50x60x70", properties.stream()
            .filter(p -> "Dimension".equals(p.getType().getName()))
            .findFirst().orElseThrow().getValue());
        assertEquals("Metal", properties.stream()
            .filter(p -> "Material".equals(p.getType().getName()))
            .findFirst().orElseThrow().getValue());
    }

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

    private Integer createPropertyType(String name, String dataType, String externalMappingId,
                                       boolean isRequired, String validationRule) {
        String isRequiredValue = isRequired ? dialectProvider.getBooleanTrue() : dialectProvider.getBooleanFalse();
        String sql = String.format(
            "INSERT INTO packaging_property_type (name, data_type, external_mapping_id, is_required, validation_rule, description, property_group, sequence_number) " +
            "VALUES (?, ?, ?, %s, ?, ?, 'GENERAL', 1)",
            isRequiredValue);
        executeRawSql(sql, name, dataType, externalMappingId, validationRule, name + " description");

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

    private Integer createPackaging(String externalId, String description) {
        // Create required referenced data
        Integer countryId = jdbcTemplate.queryForObject("SELECT id FROM country WHERE iso_code = 'DE'", Integer.class);

        // Create node for supplier
        Integer nodeId = createNode("Test Supplier", "SUP-" + externalId, countryId);

        // Create material
        Integer materialId = createMaterial("Test Material " + externalId, "MAT-" + externalId);

        // Create dimensions
        Integer huDimensionId = createPackagingDimension();
        Integer shuDimensionId = createPackagingDimension();

        // Create packaging
        String isDeprecatedValue = dialectProvider.getBooleanFalse();
        String sql = String.format(
            "INSERT INTO packaging (supplier_node_id, material_id, hu_dimension_id, shu_dimension_id, is_deprecated) " +
            "VALUES (?, ?, ?, ?, %s)",
            isDeprecatedValue);
        executeRawSql(sql, nodeId, materialId, huDimensionId, shuDimensionId);

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

    private Integer createNode(String name, String externalMappingId, Integer countryId) {
        String sql = String.format(
            "INSERT INTO node (name, external_mapping_id, country_id, is_deprecated, is_source, is_destination, is_intermediate, address) " +
            "VALUES (?, ?, ?, %s, %s, %s, %s, 'Test Address')",
            dialectProvider.getBooleanFalse(),
            dialectProvider.getBooleanTrue(),
            dialectProvider.getBooleanTrue(),
            dialectProvider.getBooleanTrue());
        executeRawSql(sql, name, externalMappingId, countryId);

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

    private Integer createMaterial(String name, String partNumber) {
        String sql = String.format(
            "INSERT INTO material (name, part_number, normalized_part_number, hs_code, is_deprecated) VALUES (?, ?, ?, '123456', %s)",
            dialectProvider.getBooleanFalse());
        executeRawSql(sql, name, partNumber, partNumber.toUpperCase());

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

    private Integer createPackagingDimension() {
        String sql = String.format(
            "INSERT INTO packaging_dimension (type, length, width, height, weight, content_unit_count, is_deprecated) " +
            "VALUES ('HU', 100, 100, 100, 10, 1, %s)",
            dialectProvider.getBooleanFalse());
        executeRawSql(sql);

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

    private void createPackagingProperty(Integer packagingId, Integer typeId, String value) {
        String sql = "INSERT INTO packaging_property (packaging_id, packaging_property_type_id, property_value) " +
                     "VALUES (?, ?, ?)";
        executeRawSql(sql, packagingId, typeId, value);
    }
}
