package de.avatic.lcc.repositories.country;

import de.avatic.lcc.dto.generic.PropertyDTO;
import de.avatic.lcc.model.db.properties.CountryPropertyMappingId;
import de.avatic.lcc.model.db.rates.ValidityPeriodState;
import de.avatic.lcc.repositories.AbstractRepositoryIntegrationTest;
import de.avatic.lcc.repositories.properties.PropertySetRepository;
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 CountryPropertyRepository.
 * <p>
 * Tests critical functionality across both MySQL and MSSQL:
 * - Upsert operations (buildUpsertStatement)
 * - INSERT IGNORE operations (buildInsertIgnoreStatement)
 * - Country-specific property management
 * - Property retrieval by country and mapping ID
 * <p>
 * Run with:
 * <pre>
 * mvn test -Dspring.profiles.active=test,mysql -Dtest=CountryPropertyRepositoryIntegrationTest
 * mvn test -Dspring.profiles.active=test,mssql -Dtest=CountryPropertyRepositoryIntegrationTest
 * </pre>
 */
class CountryPropertyRepositoryIntegrationTest extends AbstractRepositoryIntegrationTest {

    @Autowired
    private CountryPropertyRepository countryPropertyRepository;

    @Autowired
    private PropertySetRepository propertySetRepository;

    private Integer testDraftSetId;
    private Integer testValidSetId;
    private Integer testCountryId;
    private Integer testPropertyTypeId;
    private CountryPropertyMappingId testMappingId = CountryPropertyMappingId.SAFETY_STOCK;

    @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");

        // Use existing country (id=1 should exist from migrations)
        testCountryId = 1;

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

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

        // Create valid set by applying draft
        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";
        createTestCountryProperty(testValidSetId, testCountryId, testPropertyTypeId, validValue);

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

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

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

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

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

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

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

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

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

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

        // When: Get property by mapping ID and country ID
        Optional<PropertyDTO> property = countryPropertyRepository.getByMappingIdAndCountryId(
            testMappingId, testCountryId);

        // Then: Should retrieve property with both draft and valid values
        assertTrue(property.isPresent(), "Should find property by mapping ID and country ID");
        assertEquals("45", property.get().getDraftValue());
        assertEquals("30", property.get().getCurrentValue());
        assertEquals(testMappingId.name(), property.get().getExternalMappingId());
    }

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

        // When: Get property by mapping ID, set ID, and country ID
        Optional<PropertyDTO> property = countryPropertyRepository.getByMappingIdAndCountryId(
            testMappingId, testDraftSetId, testCountryId);

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

    @Test
    void testListPropertiesByCountryId() {
        // Given: Create properties for country
        createTestCountryProperty(testDraftSetId, testCountryId, testPropertyTypeId, "45");
        createTestCountryProperty(testValidSetId, testCountryId, testPropertyTypeId, "30");

        // When: List properties by country ID
        List<PropertyDTO> properties = countryPropertyRepository.listPropertiesByCountryId(testCountryId);

        // 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 testListPropertiesByCountryIdAndPropertySetId() {
        // Given: Create properties in specific set
        createTestCountryProperty(testDraftSetId, testCountryId, testPropertyTypeId, "45");

        // When: List properties by country ID and property set ID
        var properties = countryPropertyRepository.listPropertiesByCountryIdAndPropertySetId(
            testCountryId, testDraftSetId);

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

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

        assertTrue(testProp.isPresent());
        assertEquals("45", testProp.get().getCurrentValue());
    }

    @Test
    void testFillDraft() {
        // Given: Create properties in valid set for multiple property types
        Integer propertyType2 = getPropertyTypeId(CountryPropertyMappingId.WAGE.name());
        createTestCountryProperty(testValidSetId, testCountryId, testPropertyTypeId, "30");
        createTestCountryProperty(testValidSetId, testCountryId, propertyType2, "100%");

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

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

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

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

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

    @Test
    void testMultipleCountries() {
        // Given: Create properties for different countries
        Integer country2 = 2; // Assuming country 2 exists from migrations

        createTestCountryProperty(testValidSetId, testCountryId, testPropertyTypeId, "30");
        createTestCountryProperty(testValidSetId, country2, testPropertyTypeId, "45");

        // When: Get property for country 1
        Optional<PropertyDTO> property1 = countryPropertyRepository.getByMappingIdAndCountryId(
            testMappingId, testCountryId);

        // When: Get property for country 2
        Optional<PropertyDTO> property2 = countryPropertyRepository.getByMappingIdAndCountryId(
            testMappingId, country2);

        // Then: Should retrieve different values for different countries
        assertTrue(property1.isPresent());
        assertTrue(property2.isPresent());
        assertEquals("30", property1.get().getCurrentValue());
        assertEquals("45", property2.get().getCurrentValue());
    }

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

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

    private void createTestCountryProperty(Integer setId, Integer countryId, Integer typeId, String value) {
        String sql = "INSERT INTO country_property (property_set_id, country_id, country_property_type_id, property_value) VALUES (?, ?, ?, ?)";
        executeRawSql(sql, setId, countryId, 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);
    }
}
