package de.avatic.lcc.repositories.rates;

import de.avatic.lcc.model.db.rates.MatrixRate;
import de.avatic.lcc.model.db.rates.ValidityPeriodState;
import de.avatic.lcc.repositories.AbstractRepositoryIntegrationTest;
import de.avatic.lcc.repositories.pagination.SearchQueryPagination;
import de.avatic.lcc.repositories.pagination.SearchQueryResult;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;

import java.math.BigDecimal;
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 MatrixRateRepository.
 * <p>
 * Tests critical functionality across both MySQL and MSSQL:
 * - Pagination (LIMIT/OFFSET vs OFFSET/FETCH)
 * - UPSERT operations (ON DUPLICATE KEY UPDATE vs MERGE)
 * - Complex JOIN queries with filtering
 * - Copy operations between validity periods
 * <p>
 * Run with:
 * <pre>
 * mvn test -Dspring.profiles.active=test,mysql -Dtest=MatrixRateRepositoryIntegrationTest
 * mvn test -Dspring.profiles.active=test,mssql -Dtest=MatrixRateRepositoryIntegrationTest
 * </pre>
 */
class MatrixRateRepositoryIntegrationTest extends AbstractRepositoryIntegrationTest {

    @Autowired
    private MatrixRateRepository matrixRateRepository;

    private Integer testValidPeriodId;
    private Integer testDraftPeriodId;
    private Integer testCountryDeId;
    private Integer testCountryUsId;
    private Integer testCountryFrId;

    @BeforeEach
    void setupTestData() {
        // Clean up in correct order (foreign key constraints)
        jdbcTemplate.update("DELETE FROM country_matrix_rate");
        jdbcTemplate.update("DELETE FROM country_property");
        jdbcTemplate.update("DELETE FROM container_rate");
        jdbcTemplate.update("DELETE FROM validity_period");

        // Use existing countries from migrations (country table has initial data)
        // Query for countries by ISO code to get IDs
        testCountryDeId = jdbcTemplate.queryForObject(
            "SELECT id FROM country WHERE iso_code = 'DE'", Integer.class);
        testCountryUsId = jdbcTemplate.queryForObject(
            "SELECT id FROM country WHERE iso_code = 'US'", Integer.class);
        testCountryFrId = jdbcTemplate.queryForObject(
            "SELECT id FROM country WHERE iso_code = 'FR'", Integer.class);

        // Create test validity periods
        testValidPeriodId = createTestValidityPeriod(ValidityPeriodState.VALID,
            LocalDateTime.now().minusDays(1), null);
        testDraftPeriodId = createTestValidityPeriod(ValidityPeriodState.DRAFT,
            LocalDateTime.now(), null);

        // Create test matrix rates
        createTestMatrixRate(testCountryDeId, testCountryUsId, new BigDecimal("1.50"), testValidPeriodId);
        createTestMatrixRate(testCountryDeId, testCountryFrId, new BigDecimal("0.80"), testValidPeriodId);
        createTestMatrixRate(testCountryUsId, testCountryDeId, new BigDecimal("1.20"), testValidPeriodId);
    }

    @Test
    void testListRates() {
        // Given: Pagination
        SearchQueryPagination pagination = new SearchQueryPagination(1, 10);

        // When: List all rates
        SearchQueryResult<MatrixRate> result = matrixRateRepository.listRates(pagination);

        // Then: Should return rates with pagination
        assertNotNull(result);
        assertFalse(result.toList().isEmpty());
        assertEquals(3, result.getTotalElements());
        assertTrue(result.toList().size() <= 10);
    }

    @Test
    void testListRatesPagination() {
        // Given: Pagination with limit 1
        SearchQueryPagination pagination = new SearchQueryPagination(1, 1);

        // When: List rates
        SearchQueryResult<MatrixRate> result = matrixRateRepository.listRates(pagination);

        // Then: Should respect limit
        assertNotNull(result);
        assertEquals(1, result.toList().size());
        assertEquals(3, result.getTotalElements());
    }

    @Test
    void testListRatesByPeriodId() {
        // Given: Valid period ID
        SearchQueryPagination pagination = new SearchQueryPagination(1, 10);

        // When: List rates by period
        SearchQueryResult<MatrixRate> result = matrixRateRepository.listRatesByPeriodId(null, pagination, testValidPeriodId);

        // Then: Should return rates for this period
        assertNotNull(result);
        assertEquals(3, result.getTotalElements());
        assertTrue(result.toList().stream()
            .allMatch(rate -> rate.getValidityPeriodId().equals(testValidPeriodId)));
    }

    @Test
    void testListRatesByPeriodIdWithFilter() {
        // Given: Filter for "Germany"
        SearchQueryPagination pagination = new SearchQueryPagination(1, 10);

        // When: List rates with filter
        SearchQueryResult<MatrixRate> result = matrixRateRepository.listRatesByPeriodId("Germany", pagination, testValidPeriodId);

        // Then: Should return rates involving Germany
        assertNotNull(result);
        assertTrue(result.getTotalElements() >= 2, "Should find at least 2 rates with Germany");
    }

    @Test
    void testListRatesByPeriodIdWithIsoCodeFilter() {
        // Given: Filter for "US"
        SearchQueryPagination pagination = new SearchQueryPagination(1, 10);

        // When: List rates with ISO code filter
        SearchQueryResult<MatrixRate> result = matrixRateRepository.listRatesByPeriodId("US", pagination, testValidPeriodId);

        // Then: Should return rates involving US
        assertNotNull(result);
        assertTrue(result.getTotalElements() >= 2, "Should find at least 2 rates with US");
    }

    @Test
    void testGetById() {
        // Given: Get first rate ID
        SearchQueryPagination pagination = new SearchQueryPagination(1, 1);
        SearchQueryResult<MatrixRate> result = matrixRateRepository.listRates(pagination);
        Integer rateId = result.toList().getFirst().getId();

        // When: Get by ID
        MatrixRate rate = matrixRateRepository.getById(rateId);

        // Then: Should retrieve correct rate
        assertNotNull(rate);
        assertEquals(rateId, rate.getId());
        assertNotNull(rate.getRate());
        assertNotNull(rate.getFromCountry());
        assertNotNull(rate.getToCountry());
    }

    @Test
    void testListAllRatesByPeriodId() {
        // When: List all rates for valid period
        List<MatrixRate> rates = matrixRateRepository.listAllRatesByPeriodId(testValidPeriodId);

        // Then: Should return all 3 rates
        assertNotNull(rates);
        assertEquals(3, rates.size());
        assertTrue(rates.stream().allMatch(rate -> rate.getValidityPeriodId().equals(testValidPeriodId)));
    }

    @Test
    void testGetByCountryIds() {
        // When: Get rate from DE to US
        Optional<MatrixRate> rate = matrixRateRepository.getByCountryIds(testCountryDeId, testCountryUsId);

        // Then: Should find rate
        assertTrue(rate.isPresent());
        assertEquals(testCountryDeId, rate.get().getFromCountry());
        assertEquals(testCountryUsId, rate.get().getToCountry());
        assertEquals(new BigDecimal("1.50"), rate.get().getRate());
    }

    @Test
    void testGetByCountryIdsNotFound() {
        // Given: Non-existent country combination
        Integer nonExistentCountryId = 99999;

        // When: Get rate
        Optional<MatrixRate> rate = matrixRateRepository.getByCountryIds(nonExistentCountryId, testCountryUsId);

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

    @Test
    void testGetByCountryIdsWithPeriodId() {
        // When: Get rate from DE to US in valid period
        Optional<MatrixRate> rate = matrixRateRepository.getByCountryIds(testCountryDeId, testCountryUsId, testValidPeriodId);

        // Then: Should find rate
        assertTrue(rate.isPresent());
        assertEquals(testCountryDeId, rate.get().getFromCountry());
        assertEquals(testCountryUsId, rate.get().getToCountry());
        assertEquals(testValidPeriodId, rate.get().getValidityPeriodId());
    }

    @Test
    void testGetByCountryIdsWithWrongPeriodId() {
        // When: Get rate with wrong period ID
        Optional<MatrixRate> rate = matrixRateRepository.getByCountryIds(testCountryDeId, testCountryUsId, testDraftPeriodId);

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

    @Test
    void testInsertNewRate() {
        // Given: New matrix rate
        MatrixRate newRate = new MatrixRate();
        newRate.setFromCountry(testCountryFrId);
        newRate.setToCountry(testCountryUsId);
        newRate.setRate(new BigDecimal("2.50"));
        newRate.setValidityPeriodId(testDraftPeriodId);

        // When: Insert
        matrixRateRepository.insert(newRate);

        // Then: Should be inserted
        Optional<MatrixRate> inserted = matrixRateRepository.getByCountryIds(testCountryFrId, testCountryUsId, testDraftPeriodId);
        assertTrue(inserted.isPresent());
        assertEquals(new BigDecimal("2.50"), inserted.get().getRate());
    }

    @Test
    void testInsertUpsertExisting() {
        // Given: Existing rate DE -> US
        MatrixRate updateRate = new MatrixRate();
        updateRate.setFromCountry(testCountryDeId);
        updateRate.setToCountry(testCountryUsId);
        updateRate.setRate(new BigDecimal("3.00")); // Different rate
        updateRate.setValidityPeriodId(testValidPeriodId);

        // When: Insert (should upsert)
        matrixRateRepository.insert(updateRate);

        // Then: Rate should be updated
        Optional<MatrixRate> updated = matrixRateRepository.getByCountryIds(testCountryDeId, testCountryUsId, testValidPeriodId);
        assertTrue(updated.isPresent());
        assertEquals(new BigDecimal("3.00"), updated.get().getRate());

        // Should still have only 3 rates total
        List<MatrixRate> allRates = matrixRateRepository.listAllRatesByPeriodId(testValidPeriodId);
        assertEquals(3, allRates.size());
    }

    @Test
    void testCopyCurrentToDraft() {
        // Given: Valid period has 3 rates, draft has 0
        List<MatrixRate> draftRatesBefore = matrixRateRepository.listAllRatesByPeriodId(testDraftPeriodId);
        assertEquals(0, draftRatesBefore.size());

        // When: Copy current to draft
        matrixRateRepository.copyCurrentToDraft();

        // Then: Draft should have copies of all valid rates
        List<MatrixRate> draftRatesAfter = matrixRateRepository.listAllRatesByPeriodId(testDraftPeriodId);
        assertEquals(3, draftRatesAfter.size());

        // Verify rates are copied with correct values
        Optional<MatrixRate> copiedRate = matrixRateRepository.getByCountryIds(testCountryDeId, testCountryUsId, testDraftPeriodId);
        assertTrue(copiedRate.isPresent());
        assertEquals(new BigDecimal("1.50"), copiedRate.get().getRate());

        // Original rates should still exist
        List<MatrixRate> validRates = matrixRateRepository.listAllRatesByPeriodId(testValidPeriodId);
        assertEquals(3, validRates.size());
    }

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

    private Integer createTestValidityPeriod(ValidityPeriodState state, LocalDateTime startDate, LocalDateTime endDate) {
        String sql = "INSERT INTO validity_period (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);
    }

    private void createTestMatrixRate(Integer fromCountryId, Integer toCountryId, BigDecimal rate, Integer validityPeriodId) {
        String sql = "INSERT INTO country_matrix_rate (from_country_id, to_country_id, rate, validity_period_id) VALUES (?, ?, ?, ?)";
        executeRawSql(sql, fromCountryId, toCountryId, rate, validityPeriodId);
    }
}
