package de.avatic.lcc.repositories.properties;

import de.avatic.lcc.dto.generic.PropertyDTO;
import de.avatic.lcc.model.db.properties.SystemPropertyMappingId;
import de.avatic.lcc.model.db.rates.ValidityPeriodState;
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.sql.Timestamp;
import java.time.LocalDateTime;
import java.util.List;
import java.util.Optional;

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

/**
 * Integration tests for PropertyRepository.
 * <p>
 * Tests critical functionality across both MySQL and MSSQL:
 * - Upsert operations (buildUpsertStatement)
 * - INSERT IGNORE operations (buildInsertIgnoreStatement)
 * - Complex queries with CASE statements
 * - Property retrieval by mapping ID
 * - Property set state management
 * <p>
 * Run with:
 * <pre>
 * mvn test -Dspring.profiles.active=test,mysql -Dtest=PropertyRepositoryIntegrationTest
 * mvn test -Dspring.profiles.active=test,mssql -Dtest=PropertyRepositoryIntegrationTest
 * </pre>
 */
class PropertyRepositoryIntegrationTest extends AbstractRepositoryIntegrationTest {

    @Autowired
    private PropertyRepository propertyRepository;

    @Autowired
    private PropertySetRepository propertySetRepository;

    private Integer testDraftSetId;
    private Integer testValidSetId;
    private Integer testPropertyTypeId;
    private SystemPropertyMappingId testMappingId = SystemPropertyMappingId.PAYMENT_TERMS;

    @BeforeEach
    void setupTestData() {
        // Clean up any property data from other tests
        jdbcTemplate.update("DELETE FROM system_property");
        jdbcTemplate.update("DELETE FROM country_property");
        jdbcTemplate.update("DELETE FROM property_set");

        // Get property type ID for existing mapping
        testPropertyTypeId = getPropertyTypeId(testMappingId.name());

        // Create draft and valid property sets
        testDraftSetId = propertySetRepository.getDraftSetId();

        // Create valid set by first creating draft, then applying it
        propertySetRepository.applyDraft();
        testValidSetId = propertySetRepository.getValidSetId();

        // Get new draft
        testDraftSetId = propertySetRepository.getDraftSetId();
    }

    @Test
    void testSetPropertyUpsert() {
        // Given: Create a property in valid set first (required by setProperty logic)
        String validValue = "30";
        createTestProperty(testValidSetId, testPropertyTypeId, validValue);

        // Property doesn't exist in draft yet
        String value = "45";

        // When: Set property (INSERT)
        propertyRepository.setProperty(testDraftSetId, testMappingId.name(), value);

        // Then: Property should be inserted
        String sql = "SELECT property_value FROM system_property WHERE property_set_id = ? AND system_property_type_id = ?";
        String savedValue = jdbcTemplate.queryForObject(sql, String.class, testDraftSetId, testPropertyTypeId);
        assertEquals(value, savedValue);

        // When: Update property (UPDATE)
        String newValue = "60";
        propertyRepository.setProperty(testDraftSetId, testMappingId.name(), newValue);

        // Then: Property should be updated
        String updatedValue = jdbcTemplate.queryForObject(sql, String.class, testDraftSetId, testPropertyTypeId);
        assertEquals(newValue, updatedValue);
    }

    @Test
    void testSetPropertyDeletesWhenMatchesValidValue() {
        // Given: Create valid property with value
        String validValue = "30";
        createTestProperty(testValidSetId, testPropertyTypeId, validValue);

        // Create draft property with different value
        String draftValue = "45";
        createTestProperty(testDraftSetId, testPropertyTypeId, draftValue);

        // When: Set property to match valid value (should delete draft)
        propertyRepository.setProperty(testDraftSetId, testMappingId.name(), validValue);

        // Then: Draft property should be deleted
        String sql = "SELECT COUNT(*) FROM system_property WHERE property_set_id = ? AND system_property_type_id = ?";
        Integer count = jdbcTemplate.queryForObject(sql, Integer.class, testDraftSetId, testPropertyTypeId);
        assertEquals(0, count, "Draft property should be deleted when it matches valid value");
    }

    @Test
    void testListProperties() {
        // Given: Create properties in draft and valid sets
        createTestProperty(testDraftSetId, testPropertyTypeId, "45");
        createTestProperty(testValidSetId, testPropertyTypeId, "30");

        // When: List properties
        List<PropertyDTO> properties = propertyRepository.listProperties();

        // Then: Should include properties with both draft and valid values
        assertNotNull(properties);
        assertFalse(properties.isEmpty());

        Optional<PropertyDTO> testProp = properties.stream()
            .filter(p -> testMappingId.name().equals(p.getExternalMappingId()))
            .findFirst();

        assertTrue(testProp.isPresent(), "Should find test property");
        assertEquals("45", testProp.get().getDraftValue());
        assertEquals("30", testProp.get().getCurrentValue());
    }

    @Test
    void testListPropertiesBySetId() {
        // Given: Create expired property set with properties
        Integer expiredSetId = createTestPropertySet(ValidityPeriodState.EXPIRED,
            LocalDateTime.now().minusDays(30), LocalDateTime.now().minusDays(15));
        createTestProperty(expiredSetId, testPropertyTypeId, "60");

        // When: List properties by expired set ID
        List<PropertyDTO> properties = propertyRepository.listPropertiesBySetId(expiredSetId);

        // Then: Should include property from expired set
        assertNotNull(properties);
        assertFalse(properties.isEmpty());

        Optional<PropertyDTO> testProp = properties.stream()
            .filter(p -> testMappingId.name().equals(p.getExternalMappingId()))
            .findFirst();

        assertTrue(testProp.isPresent());
        assertEquals("60", testProp.get().getCurrentValue());
        assertNull(testProp.get().getDraftValue(), "Draft value should be null for expired set");
    }

    @Test
    void testGetPropertyByMappingId() {
        // Given: Create valid property
        createTestProperty(testValidSetId, testPropertyTypeId, "30");

        // When: Get property by mapping ID
        Optional<PropertyDTO> property = propertyRepository.getPropertyByMappingId(testMappingId);

        // Then: Should retrieve property
        assertTrue(property.isPresent(), "Should find property by mapping ID");
        assertEquals("30", property.get().getCurrentValue());
        assertEquals(testMappingId.name(), property.get().getExternalMappingId());
    }

    @Test
    void testGetPropertyByMappingIdWithSetId() {
        // Given: Create property in specific set
        createTestProperty(testDraftSetId, testPropertyTypeId, "45");

        // When: Get property by mapping ID and set ID
        Optional<PropertyDTO> property = propertyRepository.getPropertyByMappingId(testMappingId, testDraftSetId);

        // Then: Should retrieve property
        assertTrue(property.isPresent(), "Should find property by mapping ID and set ID");
        assertEquals("45", property.get().getCurrentValue());
    }

    @Test
    void testGetPropertyByMappingIdNotFound() {
        // When: Get property that has no value in VALID set (WORKDAYS without creating it)
        Optional<PropertyDTO> property = propertyRepository.getPropertyByMappingId(
            SystemPropertyMappingId.WORKDAYS);

        // Then: Should return empty (no property in valid set)
        assertFalse(property.isPresent(), "Should not find property without value in valid set");
    }

    @Test
    void testFillDraft() {
        // Given: Create properties in valid set
        Integer propertyType2 = getPropertyTypeId(SystemPropertyMappingId.WORKDAYS.name());
        createTestProperty(testValidSetId, testPropertyTypeId, "30");
        createTestProperty(testValidSetId, propertyType2, "210");

        // Create new draft set (empty)
        Integer newDraftId = createTestPropertySet(ValidityPeriodState.DRAFT,
            LocalDateTime.now(), null);

        // When: Fill draft with valid values
        propertyRepository.fillDraft(newDraftId);

        // Then: Draft should have copies of valid properties
        String sql = "SELECT COUNT(*) FROM system_property WHERE property_set_id = ?";
        Integer count = jdbcTemplate.queryForObject(sql, Integer.class, newDraftId);
        assertTrue(count >= 2, "Draft should have at least 2 properties copied from valid set");

        // Verify values are copied
        String valueSql = "SELECT property_value FROM system_property WHERE property_set_id = ? AND system_property_type_id = ?";
        String copiedValue1 = jdbcTemplate.queryForObject(valueSql, String.class, newDraftId, testPropertyTypeId);
        assertEquals("30", copiedValue1);

        String copiedValue2 = jdbcTemplate.queryForObject(valueSql, String.class, newDraftId, propertyType2);
        assertEquals("210", copiedValue2);
    }

    @Test
    void testFillDraftIgnoresDuplicates() {
        // Given: Create property in valid set
        createTestProperty(testValidSetId, testPropertyTypeId, "30");

        // Create draft with same property but different value
        createTestProperty(testDraftSetId, testPropertyTypeId, "45");

        Integer initialCount = jdbcTemplate.queryForObject(
            "SELECT COUNT(*) FROM system_property WHERE property_set_id = ?",
            Integer.class, testDraftSetId);

        // When: Fill draft (should ignore existing)
        propertyRepository.fillDraft(testDraftSetId);

        // Then: Should not create duplicates
        Integer finalCount = jdbcTemplate.queryForObject(
            "SELECT COUNT(*) FROM system_property WHERE property_set_id = ?",
            Integer.class, testDraftSetId);

        assertEquals(initialCount, finalCount, "Should not create duplicate properties");

        // Verify existing value is unchanged (INSERT IGNORE doesn't update)
        String value = jdbcTemplate.queryForObject(
            "SELECT property_value FROM system_property WHERE property_set_id = ? AND system_property_type_id = ?",
            String.class, testDraftSetId, testPropertyTypeId);
        assertEquals("45", value, "Existing draft value should not be overwritten");
    }

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

    private Integer getPropertyTypeId(String mappingId) {
        String sql = "SELECT id FROM system_property_type WHERE external_mapping_id = ?";
        return jdbcTemplate.queryForObject(sql, Integer.class, mappingId);
    }

    private void createTestProperty(Integer setId, Integer typeId, String value) {
        String sql = "INSERT INTO system_property (property_set_id, system_property_type_id, property_value) VALUES (?, ?, ?)";
        executeRawSql(sql, setId, typeId, value);
    }

    private Integer createTestPropertySet(ValidityPeriodState state, LocalDateTime startDate, LocalDateTime endDate) {
        String sql = "INSERT INTO property_set (state, start_date, end_date) VALUES (?, ?, ?)";

        Timestamp startTs = Timestamp.valueOf(startDate);
        Timestamp endTs = endDate != null ? Timestamp.valueOf(endDate) : null;

        executeRawSql(sql, state.name(), startTs, endTs);

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