package de.avatic.lcc.repositories.rates;

import de.avatic.lcc.dto.generic.TransportType;
import de.avatic.lcc.model.db.rates.ContainerRate;
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 ContainerRateRepository.
 * <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
 * - Transport type filtering (SEA, RAIL, POST_RUN, ROAD)
 * - Boolean literals (TRUE/FALSE vs 1/0)
 * <p>
 * Run with:
 * <pre>
 * mvn test -Dspring.profiles.active=test,mysql -Dtest=ContainerRateRepositoryIntegrationTest
 * mvn test -Dspring.profiles.active=test,mssql -Dtest=ContainerRateRepositoryIntegrationTest
 * </pre>
 */
class ContainerRateRepositoryIntegrationTest extends AbstractRepositoryIntegrationTest {

    @Autowired
    private ContainerRateRepository containerRateRepository;

    private Integer testValidPeriodId;
    private Integer testCountryDeId;
    private Integer testCountryUsId;
    private Integer testNodeHamburgId;
    private Integer testNodeBremenId;
    private Integer testNodeNewYorkId;

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

        // Use existing countries from migrations
        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);

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

        // Create test nodes
        testNodeHamburgId = createTestNode("Hamburg Port", "HAM", testCountryDeId, false, 53.5, 10.0);
        testNodeBremenId = createTestNode("Bremen Port", "BRE", testCountryDeId, false, 53.1, 8.8);
        testNodeNewYorkId = createTestNode("New York Port", "NYC", testCountryUsId, false, 40.7, -74.0);

        // Create test container rates
        createTestContainerRate(testNodeHamburgId, testNodeNewYorkId, TransportType.SEA,
            new BigDecimal("2000"), new BigDecimal("1000"), new BigDecimal("2200"), 14, testValidPeriodId);
        createTestContainerRate(testNodeBremenId, testNodeNewYorkId, TransportType.SEA,
            new BigDecimal("2100"), new BigDecimal("1050"), new BigDecimal("2300"), 15, testValidPeriodId);
        createTestContainerRate(testNodeHamburgId, testNodeBremenId, TransportType.RAIL,
            new BigDecimal("300"), new BigDecimal("150"), new BigDecimal("350"), 1, testValidPeriodId);
    }

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

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

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

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

        // When: List rates with filter
        SearchQueryResult<ContainerRate> result = containerRateRepository.listRatesByPeriodId("Hamburg", pagination, testValidPeriodId);

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

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

        // When: List rates with external mapping ID filter
        SearchQueryResult<ContainerRate> result = containerRateRepository.listRatesByPeriodId("HAM", pagination, testValidPeriodId);

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

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

        // When: List rates
        SearchQueryResult<ContainerRate> result = containerRateRepository.listRatesByPeriodId(null, pagination, testValidPeriodId);

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

    @Test
    void testGetById() {
        // Given: Get first rate ID
        SearchQueryPagination pagination = new SearchQueryPagination(1, 1);
        SearchQueryResult<ContainerRate> result = containerRateRepository.listRatesByPeriodId(null, pagination, testValidPeriodId);
        Integer rateId = result.toList().getFirst().getId();

        // When: Get by ID
        Optional<ContainerRate> rate = containerRateRepository.getById(rateId);

        // Then: Should retrieve correct rate
        assertTrue(rate.isPresent());
        assertEquals(rateId, rate.get().getId());
        assertNotNull(rate.get().getRateFeu());
        assertNotNull(rate.get().getRateTeu());
        assertNotNull(rate.get().getFromNodeId());
        assertNotNull(rate.get().getToNodeId());
    }

    @Test
    void testGetByIdNotFound() {
        // When: Get non-existent ID
        Optional<ContainerRate> rate = containerRateRepository.getById(99999);

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

    @Test
    void testListAllRatesByPeriodId() {
        // When: List all rates for valid period
        List<ContainerRate> rates = containerRateRepository.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 testFindRoutesByStartNodeIdAndDestinationCountryId() {
        // When: Find routes from Hamburg to US
        List<ContainerRate> routes = containerRateRepository.findRoutesByStartNodeIdAndDestinationCountryId(
            testNodeHamburgId, List.of(testCountryUsId));

        // Then: Should find Hamburg -> New York route
        assertNotNull(routes);
        assertEquals(1, routes.size());
        assertEquals(testNodeHamburgId, routes.getFirst().getFromNodeId());
        assertEquals(testNodeNewYorkId, routes.getFirst().getToNodeId());
        assertEquals(TransportType.SEA, routes.getFirst().getType());
    }

    @Test
    void testFindRoutesByStartNodeIdAndDestinationCountryIdMultiple() {
        // When: Find routes from Hamburg to DE or US
        List<ContainerRate> routes = containerRateRepository.findRoutesByStartNodeIdAndDestinationCountryId(
            testNodeHamburgId, List.of(testCountryDeId, testCountryUsId));

        // Then: Should find both routes (Hamburg -> Bremen and Hamburg -> New York)
        assertNotNull(routes);
        assertEquals(2, routes.size());
        assertTrue(routes.stream().allMatch(r -> r.getFromNodeId().equals(testNodeHamburgId)));
    }

    @Test
    void testFindRoutesByStartNodeIdAndDestinationCountryIdEmpty() {
        // When: Find routes with empty destination list
        List<ContainerRate> routes = containerRateRepository.findRoutesByStartNodeIdAndDestinationCountryId(
            testNodeHamburgId, List.of());

        // Then: Should return empty list
        assertNotNull(routes);
        assertTrue(routes.isEmpty());
    }

    @Test
    void testGetPostRunsFor() {
        // Given: Create a main run and post-run
        Integer testNodeWarehouseId = createTestNode("Warehouse", "WH1", testCountryUsId, false, 40.8, -74.1);
        createTestContainerRate(testNodeNewYorkId, testNodeWarehouseId, TransportType.POST_RUN,
            new BigDecimal("100"), new BigDecimal("50"), new BigDecimal("120"), 1, testValidPeriodId);

        ContainerRate mainRun = new ContainerRate();
        mainRun.setToNodeId(testNodeNewYorkId);

        // When: Get post runs
        List<ContainerRate> postRuns = containerRateRepository.getPostRunsFor(mainRun);

        // Then: Should find the post-run
        assertNotNull(postRuns);
        assertEquals(1, postRuns.size());
        assertEquals(testNodeNewYorkId, postRuns.getFirst().getFromNodeId());
        assertEquals(TransportType.POST_RUN, postRuns.getFirst().getType());
    }

    @Test
    void testFindRouteWithPeriodId() {
        // When: Find route Hamburg -> New York SEA in valid period
        Optional<ContainerRate> route = containerRateRepository.findRoute(
            testNodeHamburgId, testNodeNewYorkId, testValidPeriodId, TransportType.SEA);

        // Then: Should find route
        assertTrue(route.isPresent());
        assertEquals(testNodeHamburgId, route.get().getFromNodeId());
        assertEquals(testNodeNewYorkId, route.get().getToNodeId());
        assertEquals(TransportType.SEA, route.get().getType());
        assertEquals(0, new BigDecimal("2000").compareTo(route.get().getRateFeu()));
    }

    @Test
    void testFindRouteWithPeriodIdNotFound() {
        // When: Find route with wrong transport type
        Optional<ContainerRate> route = containerRateRepository.findRoute(
            testNodeHamburgId, testNodeNewYorkId, testValidPeriodId, TransportType.ROAD);

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

    @Test
    void testFindRouteWithoutPeriodId() {
        // When: Find route Hamburg -> New York SEA (uses VALID period)
        Optional<ContainerRate> route = containerRateRepository.findRoute(
            testNodeHamburgId, testNodeNewYorkId, TransportType.SEA);

        // Then: Should find route
        assertTrue(route.isPresent());
        assertEquals(testNodeHamburgId, route.get().getFromNodeId());
        assertEquals(testNodeNewYorkId, route.get().getToNodeId());
        assertEquals(TransportType.SEA, route.get().getType());
    }

    @Test
    void testInsertNewRate() {
        // Given: New container rate
        ContainerRate newRate = new ContainerRate();
        newRate.setFromNodeId(testNodeBremenId);
        newRate.setToNodeId(testNodeHamburgId);
        newRate.setType(TransportType.ROAD);
        newRate.setRateFeu(new BigDecimal("200"));
        newRate.setRateTeu(new BigDecimal("100"));
        newRate.setRateHc(new BigDecimal("220"));
        newRate.setLeadTime(1);
        newRate.setValidityPeriodId(testValidPeriodId);

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

        // Then: Should be inserted
        Optional<ContainerRate> inserted = containerRateRepository.findRoute(
            testNodeBremenId, testNodeHamburgId, testValidPeriodId, TransportType.ROAD);
        assertTrue(inserted.isPresent());
        assertEquals(0, new BigDecimal("200").compareTo(inserted.get().getRateFeu()));
    }

    @Test
    void testInsertUpsertExisting() {
        // Given: Existing rate Hamburg -> New York
        ContainerRate updateRate = new ContainerRate();
        updateRate.setFromNodeId(testNodeHamburgId);
        updateRate.setToNodeId(testNodeNewYorkId);
        updateRate.setType(TransportType.SEA);
        updateRate.setRateFeu(new BigDecimal("2500")); // Different rate
        updateRate.setRateTeu(new BigDecimal("1250"));
        updateRate.setRateHc(new BigDecimal("2700"));
        updateRate.setLeadTime(12);
        updateRate.setValidityPeriodId(testValidPeriodId);

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

        // Then: Rate should be updated
        Optional<ContainerRate> updated = containerRateRepository.findRoute(
            testNodeHamburgId, testNodeNewYorkId, testValidPeriodId, TransportType.SEA);
        assertTrue(updated.isPresent());
        assertEquals(0, new BigDecimal("2500").compareTo(updated.get().getRateFeu()));
        assertEquals(12, updated.get().getLeadTime());

        // Should still have only 3 rates total
        List<ContainerRate> allRates = containerRateRepository.listAllRatesByPeriodId(testValidPeriodId);
        assertEquals(3, allRates.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 Integer createTestNode(String name, String externalMappingId, Integer countryId, boolean isDeprecated,
                                     double geoLat, double geoLng) {
        String isDeprecatedValue = isDeprecated ? dialectProvider.getBooleanTrue() : dialectProvider.getBooleanFalse();
        String sql = String.format(
            "INSERT INTO node (name, external_mapping_id, country_id, is_deprecated, is_source, is_destination, is_intermediate, address, geo_lat, geo_lng) " +
            "VALUES (?, ?, ?, %s, %s, %s, %s, 'Test Address', ?, ?)",
            isDeprecatedValue,
            dialectProvider.getBooleanTrue(),
            dialectProvider.getBooleanTrue(),
            dialectProvider.getBooleanTrue());
        executeRawSql(sql, name, externalMappingId, countryId, geoLat, geoLng);

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

    private void createTestContainerRate(Integer fromNodeId, Integer toNodeId, TransportType type,
                                          BigDecimal rateFeu, BigDecimal rateTeu, BigDecimal rateHc,
                                          int leadTime, Integer validityPeriodId) {
        String sql = "INSERT INTO container_rate (from_node_id, to_node_id, container_rate_type, rate_feu, rate_teu, rate_hc, lead_time, validity_period_id) " +
                     "VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
        executeRawSql(sql, fromNodeId, toNodeId, type.name(), rateFeu, rateTeu, rateHc, leadTime, validityPeriodId);
    }
}
