package de.avatic.lcc.repositories.properties;

import de.avatic.lcc.model.db.properties.PropertySet;
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.LocalDate;
import java.time.LocalDateTime;
import java.util.List;
import java.util.Optional;

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

/**
 * Integration tests for PropertySetRepository.
 * <p>
 * Tests critical functionality across both MySQL and MSSQL:
 * - Draft creation and retrieval
 * - State transitions (DRAFT → VALID → EXPIRED → INVALID)
 * - Date-based queries with dialect-specific date extraction
 * - Timestamp handling
 * <p>
 * Run with:
 * <pre>
 * mvn test -Dspring.profiles.active=test,mysql -Dtest=PropertySetRepositoryIntegrationTest
 * mvn test -Dspring.profiles.active=test,mssql -Dtest=PropertySetRepositoryIntegrationTest
 * </pre>
 */
class PropertySetRepositoryIntegrationTest extends AbstractRepositoryIntegrationTest {

    @Autowired
    private PropertySetRepository propertySetRepository;

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

    @Test
    void testGetDraftSet() {
        // When: Get draft set (creates if doesn't exist)
        PropertySet draft = propertySetRepository.getDraftSet();

        // Then: Should have draft
        assertNotNull(draft);
        assertEquals(ValidityPeriodState.DRAFT, draft.getState());
        assertNotNull(draft.getStartDate());
        assertNull(draft.getEndDate(), "Draft should not have end date");
    }

    @Test
    void testGetDraftSetIdempotent() {
        // Given: Get draft first time
        PropertySet draft1 = propertySetRepository.getDraftSet();

        // When: Get draft second time
        PropertySet draft2 = propertySetRepository.getDraftSet();

        // Then: Should be same draft
        assertEquals(draft1.getId(), draft2.getId(), "Should return same draft");
    }

    @Test
    void testGetDraftSetId() {
        // When: Get draft set ID
        Integer draftId = propertySetRepository.getDraftSetId();

        // Then: Should have valid ID
        assertNotNull(draftId);
        assertTrue(draftId > 0);

        // Verify it's actually a draft
        PropertySet draft = propertySetRepository.getById(draftId);
        assertEquals(ValidityPeriodState.DRAFT, draft.getState());
    }

    @Test
    void testListPropertySets() {
        // Given: Create draft
        propertySetRepository.getDraftSet();

        // When: List all property sets
        List<PropertySet> propertySets = propertySetRepository.listPropertySets();

        // Then: Should have at least draft
        assertNotNull(propertySets);
        assertFalse(propertySets.isEmpty(), "Should have at least one property set");

        // Verify draft is in list
        boolean hasDraft = propertySets.stream()
            .anyMatch(ps -> ps.getState() == ValidityPeriodState.DRAFT);
        assertTrue(hasDraft, "Should have a draft property set");
    }

    @Test
    void testApplyDraft() {
        // Given: Clean state - get draft
        PropertySet draft = propertySetRepository.getDraftSet();
        Integer draftId = draft.getId();

        // When: Apply draft (transitions DRAFT → VALID, creates new DRAFT)
        propertySetRepository.applyDraft();

        // Then: Old draft should now be VALID
        PropertySet nowValid = propertySetRepository.getById(draftId);
        assertEquals(ValidityPeriodState.VALID, nowValid.getState());
        assertNotNull(nowValid.getStartDate());
        assertNull(nowValid.getEndDate(), "Valid set should not have end date yet");

        // New draft should exist
        PropertySet newDraft = propertySetRepository.getDraftSet();
        assertNotEquals(draftId, newDraft.getId(), "Should have new draft");
        assertEquals(ValidityPeriodState.DRAFT, newDraft.getState());
    }

    @Test
    void testGetValidSet() {
        // Given: Apply draft to create valid set
        PropertySet draft = propertySetRepository.getDraftSet();
        propertySetRepository.applyDraft();

        // When: Get valid set
        Optional<PropertySet> validSet = propertySetRepository.getValidSet();

        // Then: Should have valid set
        assertTrue(validSet.isPresent(), "Should have valid property set after applying draft");
        assertEquals(ValidityPeriodState.VALID, validSet.get().getState());
        assertNotNull(validSet.get().getStartDate());
        assertNull(validSet.get().getEndDate(), "Valid set should not have end date");
    }

    @Test
    void testGetValidSetId() {
        // Given: Apply draft to create valid set
        propertySetRepository.getDraftSet();
        propertySetRepository.applyDraft();

        // When: Get valid set ID
        Integer validId = propertySetRepository.getValidSetId();

        // Then: Should have valid ID
        assertNotNull(validId);
        PropertySet validSet = propertySetRepository.getById(validId);
        assertEquals(ValidityPeriodState.VALID, validSet.getState());
    }

    @Test
    void testApplyDraftExpiresOldValid() {
        // Given: Manually create a VALID set (to avoid timing issues with applyDraft)
        LocalDateTime pastStart = LocalDateTime.now().minusDays(10);
        Integer firstValidId = createTestPropertySet(ValidityPeriodState.VALID, pastStart, null);

        // When: Apply draft (should expire the existing VALID set)
        propertySetRepository.getDraftSet(); // Creates draft
        propertySetRepository.applyDraft();

        // Then: First valid should now be expired
        PropertySet expired = propertySetRepository.getById(firstValidId);
        assertEquals(ValidityPeriodState.EXPIRED, expired.getState());
        assertNotNull(expired.getEndDate(), "Expired set should have end date");
        assertTrue(expired.getEndDate().isAfter(expired.getStartDate()),
            "End date must be after start date");
    }

    @Test
    void testInvalidateById() {
        // Given: Create expired property set manually
        LocalDateTime pastStart = LocalDateTime.now().minusDays(30);
        LocalDateTime pastEnd = LocalDateTime.now().minusDays(15);
        Integer expiredId = createTestPropertySet(ValidityPeriodState.EXPIRED, pastStart, pastEnd);

        // When: Invalidate expired set
        boolean invalidated = propertySetRepository.invalidateById(expiredId);

        // Then: Should be invalidated
        assertTrue(invalidated, "Should successfully invalidate expired property set");

        PropertySet invalidSet = propertySetRepository.getById(expiredId);
        assertEquals(ValidityPeriodState.INVALID, invalidSet.getState());
    }

    @Test
    void testInvalidateByIdFailsForNonExpired() {
        // Given: Valid property set
        propertySetRepository.getDraftSet();
        propertySetRepository.applyDraft();
        Integer validId = propertySetRepository.getValidSetId();

        // When: Try to invalidate valid set (should only work for EXPIRED)
        boolean invalidated = propertySetRepository.invalidateById(validId);

        // Then: Should fail
        assertFalse(invalidated, "Should not invalidate non-expired property set");

        PropertySet stillValid = propertySetRepository.getById(validId);
        assertEquals(ValidityPeriodState.VALID, stillValid.getState());
    }

    @Test
    void testHasPropertiesDraftWhenEmpty() {
        // Given: Draft with no properties
        propertySetRepository.getDraftSet();

        // When: Check if has properties
        Boolean hasProperties = propertySetRepository.hasPropertiesDraft();

        // Then: Should be false
        assertFalse(hasProperties, "Should return false when draft has no properties");
    }

    @Test
    void testGetState() {
        // Given: Draft property set
        Integer draftId = propertySetRepository.getDraftSetId();

        // When: Get state
        ValidityPeriodState state = propertySetRepository.getState(draftId);

        // Then: Should be DRAFT
        assertEquals(ValidityPeriodState.DRAFT, state);
    }

    @Test
    void testGetById() {
        // Given: Draft property set
        Integer draftId = propertySetRepository.getDraftSetId();

        // When: Get by ID
        PropertySet propertySet = propertySetRepository.getById(draftId);

        // Then: Should retrieve correctly
        assertNotNull(propertySet);
        assertEquals(draftId, propertySet.getId());
        assertEquals(ValidityPeriodState.DRAFT, propertySet.getState());
    }

    @Test
    void testGetByIdNotFound() {
        // When/Then: Get non-existent ID should throw exception
        assertThrows(IllegalArgumentException.class, () ->
            propertySetRepository.getById(99999)
        );
    }

    @Test
    void testGetByDate() {
        // Given: Create valid set manually with past date (to avoid timing issues)
        LocalDateTime validStart = LocalDateTime.now().minusDays(5);
        Integer validId = createTestPropertySet(ValidityPeriodState.VALID, validStart, null);

        // When: Get by today's date
        LocalDate today = LocalDate.now();
        Optional<PropertySet> result = propertySetRepository.getByDate(today);

        // Then: Should find valid set
        assertTrue(result.isPresent(), "Should find property set for today");
        assertEquals(ValidityPeriodState.VALID, result.get().getState());
        assertEquals(validId, result.get().getId());
    }

    @Test
    void testGetByDatePastDate() {
        // Given: Create expired property set in the past
        LocalDateTime pastStart = LocalDateTime.now().minusDays(30);
        LocalDateTime pastEnd = LocalDateTime.now().minusDays(15);
        Integer expiredId = createTestPropertySet(ValidityPeriodState.EXPIRED, pastStart, pastEnd);

        // When: Get by date within past range
        LocalDate pastDate = LocalDate.now().minusDays(20);
        Optional<PropertySet> result = propertySetRepository.getByDate(pastDate);

        // Then: Should find expired set
        assertTrue(result.isPresent(), "Should find property set for past date");
        assertEquals(expiredId, result.get().getId());
    }

    @Test
    void testGetByDateOrdering() {
        // Given: Create multiple property sets manually
        LocalDateTime old = LocalDateTime.now().minusDays(20);
        LocalDateTime recent = LocalDateTime.now().minusDays(1);

        Integer oldExpired = createTestPropertySet(ValidityPeriodState.EXPIRED, old, recent);
        Integer recentValid = createTestPropertySet(ValidityPeriodState.VALID, recent, null);

        // When: Get by today's date (recent VALID covers today, old EXPIRED does not)
        LocalDate today = LocalDate.now();
        Optional<PropertySet> result = propertySetRepository.getByDate(today);

        // Then: Should return the VALID one
        assertTrue(result.isPresent());
        assertEquals(ValidityPeriodState.VALID, result.get().getState());
        assertEquals(recentValid, result.get().getId());
    }

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

    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);
    }
}
