package de.avatic.lcc.repositories.rates;

import de.avatic.lcc.model.db.rates.ValidityPeriod;
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 ValidityPeriodRepository.
 * <p>
 * Tests critical functionality across both MySQL and MSSQL:
 * - CRUD operations
 * - State management (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=ValidityPeriodRepositoryIntegrationTest
 * mvn test -Dspring.profiles.active=test,mssql -Dtest=ValidityPeriodRepositoryIntegrationTest
 * </pre>
 */
class ValidityPeriodRepositoryIntegrationTest extends AbstractRepositoryIntegrationTest {

    @Autowired
    private ValidityPeriodRepository validityPeriodRepository;

    private Integer testValidPeriodId;
    private Integer testExpiredPeriodId;

    @BeforeEach
    void setupTestData() {
        // Clean up any validity periods from other tests
        // Must delete in correct order due to foreign key constraints
        jdbcTemplate.update("DELETE FROM sys_error_trace_item");
        jdbcTemplate.update("DELETE FROM calculation_job_route_section");
        jdbcTemplate.update("DELETE FROM calculation_job_destination");
        jdbcTemplate.update("DELETE FROM sys_error");
        jdbcTemplate.update("DELETE FROM calculation_job");
        jdbcTemplate.update("DELETE FROM container_rate");
        jdbcTemplate.update("DELETE FROM country_matrix_rate");
        jdbcTemplate.update("DELETE FROM bulk_operation");
        jdbcTemplate.update("DELETE FROM validity_period");

        // Create test validity periods
        testValidPeriodId = createTestValidityPeriod(ValidityPeriodState.VALID,
            LocalDateTime.now().minusDays(1), null);
        testExpiredPeriodId = createTestValidityPeriod(ValidityPeriodState.EXPIRED,
            LocalDateTime.now().minusDays(30), LocalDateTime.now().minusDays(15));
    }

    @Test
    void testListPeriods() {
        // When: List all periods
        List<ValidityPeriod> periods = validityPeriodRepository.listPeriods();

        // Then: Should have at least our test periods
        assertNotNull(periods);
        assertTrue(periods.size() >= 2, "Should have at least 2 validity periods");

        // Verify our test periods are in the list
        boolean hasValid = periods.stream().anyMatch(p -> p.getId().equals(testValidPeriodId));
        boolean hasExpired = periods.stream().anyMatch(p -> p.getId().equals(testExpiredPeriodId));
        assertTrue(hasValid, "Should include VALID period");
        assertTrue(hasExpired, "Should include EXPIRED period");
    }

    @Test
    void testGetById() {
        // When: Get by ID
        ValidityPeriod period = validityPeriodRepository.getById(testValidPeriodId);

        // Then: Should retrieve correctly
        assertNotNull(period);
        assertEquals(testValidPeriodId, period.getId());
        assertEquals(ValidityPeriodState.VALID, period.getState());
        assertNotNull(period.getStartDate());
        assertNull(period.getEndDate(), "VALID period should not have end date");
    }

    @Test
    void testGetValidPeriod() {
        // When: Get valid period
        Optional<ValidityPeriod> period = validityPeriodRepository.getValidPeriod();

        // Then: Should find valid period
        assertTrue(period.isPresent(), "Should have a VALID period");
        assertEquals(ValidityPeriodState.VALID, period.get().getState());
        assertEquals(testValidPeriodId, period.get().getId());
    }

    @Test
    void testGetValidPeriodId() {
        // When: Get valid period ID
        Optional<Integer> periodId = validityPeriodRepository.getValidPeriodId();

        // Then: Should have valid period ID
        assertTrue(periodId.isPresent());
        assertEquals(testValidPeriodId, periodId.get());
    }

    @Test
    void testInvalidateById() {
        // When: Invalidate expired period
        boolean invalidated = validityPeriodRepository.invalidateById(testExpiredPeriodId);

        // Then: Should be invalidated
        assertTrue(invalidated, "Should successfully invalidate EXPIRED period");

        ValidityPeriod period = validityPeriodRepository.getById(testExpiredPeriodId);
        assertEquals(ValidityPeriodState.INVALID, period.getState());
    }

    @Test
    void testInvalidateByIdFailsForNonExpired() {
        // When: Try to invalidate VALID period (should only work for EXPIRED)
        boolean invalidated = validityPeriodRepository.invalidateById(testValidPeriodId);

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

        ValidityPeriod period = validityPeriodRepository.getById(testValidPeriodId);
        assertEquals(ValidityPeriodState.VALID, period.getState());
    }

    @Test
    void testGetPeriodId() {
        // Given: Time within valid period
        LocalDateTime now = LocalDateTime.now();

        // When: Get period ID by timestamp
        Optional<Integer> periodId = validityPeriodRepository.getPeriodId(now);

        // Then: Should find the valid period
        assertTrue(periodId.isPresent(), "Should find period for current timestamp");
        assertEquals(testValidPeriodId, periodId.get());
    }

    @Test
    void testGetPeriodIdForPastDate() {
        // Given: Time within expired period range
        LocalDateTime pastTime = LocalDateTime.now().minusDays(20);

        // When: Get period ID
        Optional<Integer> periodId = validityPeriodRepository.getPeriodId(pastTime);

        // Then: Should find expired period
        assertTrue(periodId.isPresent(), "Should find period for past timestamp");
        assertEquals(testExpiredPeriodId, periodId.get());
    }

    @Test
    void testGetPeriodIdNotFound() {
        // Given: Only expired periods (create valid period with end date so future is not covered)
        jdbcTemplate.update("DELETE FROM validity_period WHERE state = 'VALID'");

        // Time far in the future (no period covers it since all have end dates)
        LocalDateTime futureTime = LocalDateTime.now().plusYears(10);

        // When: Get period ID
        Optional<Integer> periodId = validityPeriodRepository.getPeriodId(futureTime);

        // Then: Should not find
        assertFalse(periodId.isPresent(), "Should not find period for far future timestamp");
    }

    @Test
    void testGetByDate() {
        // Given: Today's date
        LocalDate today = LocalDate.now();

        // When: Get by date
        Optional<ValidityPeriod> period = validityPeriodRepository.getByDate(today);

        // Then: Should find valid period
        assertTrue(period.isPresent(), "Should find period for today");
        assertEquals(ValidityPeriodState.VALID, period.get().getState());
        assertEquals(testValidPeriodId, period.get().getId());
    }

    @Test
    void testGetByDatePast() {
        // Given: Date within expired period range
        LocalDate pastDate = LocalDate.now().minusDays(20);

        // When: Get by date
        Optional<ValidityPeriod> period = validityPeriodRepository.getByDate(pastDate);

        // Then: Should find expired period
        assertTrue(period.isPresent(), "Should find period for past date");
        assertEquals(ValidityPeriodState.EXPIRED, period.get().getState());
        assertEquals(testExpiredPeriodId, period.get().getId());
    }

    @Test
    void testGetByDateNotFound() {
        // Given: Only expired periods (create valid period with end date so future is not covered)
        jdbcTemplate.update("DELETE FROM validity_period WHERE state = 'VALID'");

        // Date far in the future (no period covers it since all have end dates)
        LocalDate futureDate = LocalDate.now().plusYears(10);

        // When: Get by date
        Optional<ValidityPeriod> period = validityPeriodRepository.getByDate(futureDate);

        // Then: Should not find
        assertFalse(period.isPresent(), "Should not find period for far future date");
    }

    @Test
    void testHasRateDrafts() {
        // Given: Create draft period (but no associated rates)
        Integer draftId = createTestValidityPeriod(ValidityPeriodState.DRAFT,
            LocalDateTime.now(), null);

        // When: Check if has rate drafts
        boolean hasDrafts = validityPeriodRepository.hasRateDrafts();

        // Then: Should be false (no rates associated)
        assertFalse(hasDrafts, "Should return false when no associated rates");
    }

    @Test
    void testHasMatrixRateDrafts() {
        // Given: Create draft period (but no associated matrix rates)
        Integer draftId = createTestValidityPeriod(ValidityPeriodState.DRAFT,
            LocalDateTime.now(), null);

        // When: Check if has matrix rate drafts
        boolean hasDrafts = validityPeriodRepository.hasMatrixRateDrafts();

        // Then: Should be false (no matrix rates associated)
        assertFalse(hasDrafts, "Should return false when no associated matrix rates");
    }

    @Test
    void testHasContainerRateDrafts() {
        // Given: Create draft period (but no associated container rates)
        Integer draftId = createTestValidityPeriod(ValidityPeriodState.DRAFT,
            LocalDateTime.now(), null);

        // When: Check if has container rate drafts
        boolean hasDrafts = validityPeriodRepository.hasContainerRateDrafts();

        // Then: Should be false (no container rates associated)
        assertFalse(hasDrafts, "Should return false when no associated container rates");
    }

    @Test
    void testIncreaseRenewal() {
        // Given: Valid period with initial renewals
        ValidityPeriod before = validityPeriodRepository.getById(testValidPeriodId);
        int initialRenewals = before.getRenewals();

        // When: Increase renewal
        validityPeriodRepository.increaseRenewal(5);

        // Then: Renewals should be increased
        ValidityPeriod after = validityPeriodRepository.getById(testValidPeriodId);
        assertEquals(initialRenewals + 5, after.getRenewals(),
            "Renewals should be increased by 5");
    }

    @Test
    void testGetByDateOrderingWithPagination() {
        // Given: Multiple periods with overlapping date ranges
        // testExpiredPeriodId covers: -30 to -15 days
        // Create another period that also covers -20 days (overlaps with testExpiredPeriodId)
        Integer period2 = createTestValidityPeriod(ValidityPeriodState.EXPIRED,
            LocalDateTime.now().minusDays(25), LocalDateTime.now().minusDays(10));

        // When: Get by date that both periods cover (should use ORDER BY start_date DESC with LIMIT 1)
        LocalDate searchDate = LocalDate.now().minusDays(20);
        Optional<ValidityPeriod> result = validityPeriodRepository.getByDate(searchDate);

        // Then: Should return the most recent one (period2 has more recent start_date)
        assertTrue(result.isPresent());
        assertEquals(period2, result.get().getId(), "Should return period with most recent start_date");
    }

    @Test
    void testMultiplePeriods() {
        // Given: Create multiple periods
        Integer draft = createTestValidityPeriod(ValidityPeriodState.DRAFT,
            LocalDateTime.now(), null);
        Integer invalid = createTestValidityPeriod(ValidityPeriodState.INVALID,
            LocalDateTime.now().minusDays(100), LocalDateTime.now().minusDays(90));

        // When: List all periods
        List<ValidityPeriod> periods = validityPeriodRepository.listPeriods();

        // Then: Should have all 5 periods (2 from setup + 3 created here)
        assertTrue(periods.size() >= 4, "Should have at least 4 validity periods");

        // Verify all states are present
        List<ValidityPeriodState> states = periods.stream().map(ValidityPeriod::getState).toList();
        assertTrue(states.contains(ValidityPeriodState.VALID));
        assertTrue(states.contains(ValidityPeriodState.EXPIRED));
        assertTrue(states.contains(ValidityPeriodState.DRAFT));
        assertTrue(states.contains(ValidityPeriodState.INVALID));
    }

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

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

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

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

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