package de.avatic.lcc.repositories;

import de.avatic.lcc.model.db.nodes.Distance;
import de.avatic.lcc.model.db.nodes.DistanceMatrixState;
import de.avatic.lcc.model.db.nodes.Node;
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.time.LocalDateTime;
import java.util.Optional;

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

/**
 * Integration tests for DistanceMatrixRepository.
 * <p>
 * Tests critical functionality across both MySQL and MSSQL:
 * - Distance lookup operations
 * - Save/update logic (INSERT or UPDATE based on existence)
 * - Retry counter updates
 * - Enum handling (DistanceMatrixState)
 * - Timestamp handling
 * <p>
 * Run with:
 * <pre>
 * mvn test -Dspring.profiles.active=test,mysql -Dtest=DistanceMatrixRepositoryIntegrationTest
 * mvn test -Dspring.profiles.active=test,mssql -Dtest=DistanceMatrixRepositoryIntegrationTest
 * </pre>
 */
class DistanceMatrixRepositoryIntegrationTest extends AbstractRepositoryIntegrationTest {

    @Autowired
    private DistanceMatrixRepository distanceMatrixRepository;

    private Integer testNodeId1;
    private Integer testNodeId2;
    private Integer testUserNodeId1;
    private Integer testUserNodeId2;

    @BeforeEach
    void setupTestData() {
        // Create test nodes
        testNodeId1 = createTestNode("Node 1", "Berlin", 52.5200, 13.4050);
        testNodeId2 = createTestNode("Node 2", "Munich", 48.1351, 11.5820);

        // Create test user nodes
        Integer userId = createTestUser("distancetest@test.com", "DISTWORK001");
        testUserNodeId1 = createTestUserNode(userId, "User Node 1", "Hamburg", 53.5511, 9.9937);
        testUserNodeId2 = createTestUserNode(userId, "User Node 2", "Frankfurt", 50.1109, 8.6821);
    }

    @Test
    void testGetDistanceNodeToNode() {
        // Given: Create distance entry
        Distance distance = createTestDistance(testNodeId1, testNodeId2, null, null,
            52.5200, 13.4050, 48.1351, 11.5820, 504.2);
        distanceMatrixRepository.saveDistance(distance);

        // When: Get distance
        Node from = createNodeObject(testNodeId1);
        Node to = createNodeObject(testNodeId2);
        Optional<Distance> result = distanceMatrixRepository.getDistance(from, false, to, false);

        // Then: Should find distance
        assertTrue(result.isPresent(), "Should find distance between nodes");
        assertEquals(0, new BigDecimal("504.2").compareTo(result.get().getDistance()),
            "Distance should be 504.2");
        assertEquals(DistanceMatrixState.VALID, result.get().getState());
        assertEquals(testNodeId1, result.get().getFromNodeId());
        assertEquals(testNodeId2, result.get().getToNodeId());
    }

    @Test
    void testGetDistanceUserNodeToUserNode() {
        // Given: Create user node distance entry
        Distance distance = createTestDistance(null, null, testUserNodeId1, testUserNodeId2,
            53.5511, 9.9937, 50.1109, 8.6821, 393.5);
        distanceMatrixRepository.saveDistance(distance);

        // When: Get distance
        Node from = createNodeObject(testUserNodeId1);
        Node to = createNodeObject(testUserNodeId2);
        Optional<Distance> result = distanceMatrixRepository.getDistance(from, true, to, true);

        // Then: Should find distance
        assertTrue(result.isPresent(), "Should find distance between user nodes");
        assertEquals(0, new BigDecimal("393.5").compareTo(result.get().getDistance()),
            "Distance should be 393.5");
        assertEquals(testUserNodeId1, result.get().getFromUserNodeId());
        assertEquals(testUserNodeId2, result.get().getToUserNodeId());
    }

    @Test
    void testGetDistanceNotFound() {
        // When: Get non-existent distance
        Node from = createNodeObject(testNodeId1);
        Node to = createNodeObject(testNodeId2);
        Optional<Distance> result = distanceMatrixRepository.getDistance(from, false, to, false);

        // Then: Should return empty
        assertFalse(result.isPresent(), "Should not find non-existent distance");
    }

    @Test
    void testSaveDistanceInsert() {
        // Given: New distance
        Distance distance = createTestDistance(testNodeId1, testNodeId2, null, null,
            52.5200, 13.4050, 48.1351, 11.5820, 504.2);

        // When: Save
        distanceMatrixRepository.saveDistance(distance);

        // Then: Should be inserted
        Node from = createNodeObject(testNodeId1);
        Node to = createNodeObject(testNodeId2);
        Optional<Distance> saved = distanceMatrixRepository.getDistance(from, false, to, false);

        assertTrue(saved.isPresent(), "Distance should be saved");
        assertEquals(0, new BigDecimal("504.2").compareTo(saved.get().getDistance()),
            "Distance should be 504.2");
        assertEquals(DistanceMatrixState.VALID, saved.get().getState());
    }

    @Test
    void testSaveDistanceUpdate() {
        // Given: Existing distance
        Distance distance = createTestDistance(testNodeId1, testNodeId2, null, null,
            52.5200, 13.4050, 48.1351, 11.5820, 504.2);
        distanceMatrixRepository.saveDistance(distance);

        // When: Update with new distance
        Distance updated = createTestDistance(testNodeId1, testNodeId2, null, null,
            52.5200, 13.4050, 48.1351, 11.5820, 510.0);
        updated.setState(DistanceMatrixState.STALE);
        distanceMatrixRepository.saveDistance(updated);

        // Then: Should be updated
        Node from = createNodeObject(testNodeId1);
        Node to = createNodeObject(testNodeId2);
        Optional<Distance> result = distanceMatrixRepository.getDistance(from, false, to, false);

        assertTrue(result.isPresent());
        assertEquals(0, new BigDecimal("510.0").compareTo(result.get().getDistance()),
            "Distance should be 510.0");
        assertEquals(DistanceMatrixState.STALE, result.get().getState());
    }

    @Test
    void testUpdateRetries() {
        // Given: Insert distance
        Distance distance = createTestDistance(testNodeId1, testNodeId2, null, null,
            52.5200, 13.4050, 48.1351, 11.5820, 504.2);
        distanceMatrixRepository.saveDistance(distance);

        // Get the ID
        Node from = createNodeObject(testNodeId1);
        Node to = createNodeObject(testNodeId2);
        Distance saved = distanceMatrixRepository.getDistance(from, false, to, false).orElseThrow();
        Integer distanceId = saved.getId();
        int initialRetries = saved.getRetries();

        // When: Update retries
        distanceMatrixRepository.updateRetries(distanceId);

        // Then: Retries should be incremented
        Distance afterUpdate = distanceMatrixRepository.getDistance(from, false, to, false).orElseThrow();
        assertEquals(initialRetries + 1, afterUpdate.getRetries(),
            "Retries should be incremented by 1");
    }

    @Test
    void testDistanceStates() {
        // Test different states
        for (DistanceMatrixState state : new DistanceMatrixState[]{
            DistanceMatrixState.VALID,
            DistanceMatrixState.STALE,
            DistanceMatrixState.EXCEPTION
        }) {
            // Given: Create distance with specific state
            Integer fromId = createTestNode("From " + state, "Address", 50.0, 10.0);
            Integer toId = createTestNode("To " + state, "Address", 51.0, 11.0);

            Distance distance = createTestDistance(fromId, toId, null, null,
                50.0, 10.0, 51.0, 11.0, 100.0);
            distance.setState(state);
            distanceMatrixRepository.saveDistance(distance);

            // When: Retrieve
            Node from = createNodeObject(fromId);
            Node to = createNodeObject(toId);
            Optional<Distance> result = distanceMatrixRepository.getDistance(from, false, to, false);

            // Then: Should have correct state
            assertTrue(result.isPresent(), "Should find distance with state " + state);
            assertEquals(state, result.get().getState(), "State should be " + state);
        }
    }

    @Test
    void testMixedNodeTypes() {
        // Given: Distance from regular node to user node
        Distance distance = createTestDistance(testNodeId1, null, null, testUserNodeId1,
            52.5200, 13.4050, 53.5511, 9.9937, 289.3);
        distanceMatrixRepository.saveDistance(distance);

        // When: Get distance
        Node from = createNodeObject(testNodeId1);
        Node to = createNodeObject(testUserNodeId1);
        Optional<Distance> result = distanceMatrixRepository.getDistance(from, false, to, true);

        // Then: Should find distance
        assertTrue(result.isPresent(), "Should find distance between mixed node types");
        assertEquals(0, new BigDecimal("289.3").compareTo(result.get().getDistance()),
            "Distance should be 289.3");
        assertEquals(testNodeId1, result.get().getFromNodeId());
        assertEquals(testUserNodeId1, result.get().getToUserNodeId());
        assertNull(result.get().getToNodeId());
        assertNull(result.get().getFromUserNodeId());
    }

    @Test
    void testTimestampHandling() {
        // Given: Create distance with timestamp
        Distance distance = createTestDistance(testNodeId1, testNodeId2, null, null,
            52.5200, 13.4050, 48.1351, 11.5820, 504.2);
        LocalDateTime beforeSave = LocalDateTime.now().minusSeconds(1);
        distanceMatrixRepository.saveDistance(distance);

        // When: Retrieve
        Node from = createNodeObject(testNodeId1);
        Node to = createNodeObject(testNodeId2);
        Optional<Distance> result = distanceMatrixRepository.getDistance(from, false, to, false);

        // Then: Should have valid timestamp
        assertTrue(result.isPresent());
        assertNotNull(result.get().getUpdatedAt(), "Updated timestamp should be set");
        assertTrue(result.get().getUpdatedAt().isAfter(beforeSave),
            "Updated timestamp should be recent");
    }

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

    private Integer createTestNode(String name, String address, double geoLat, double geoLng) {
        String sql = "INSERT INTO node (name, address, geo_lat, geo_lng, is_deprecated, is_destination, is_source, is_intermediate, country_id, predecessor_required) " +
                     "VALUES (?, ?, ?, ?, " + dialectProvider.getBooleanFalse() + ", " +
                     dialectProvider.getBooleanTrue() + ", " + dialectProvider.getBooleanTrue() + ", " +
                     dialectProvider.getBooleanFalse() + ", ?, " + dialectProvider.getBooleanFalse() + ")";
        executeRawSql(sql, name, address, new BigDecimal(geoLat), new BigDecimal(geoLng), 1);

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

    private Integer createTestUser(String email, String workdayId) {
        String sql = "INSERT INTO sys_user (email, workday_id, firstname, lastname, is_active) VALUES (?, ?, ?, ?, " +
                     dialectProvider.getBooleanTrue() + ")";
        executeRawSql(sql, email, workdayId, "Test", "User");

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

    private Integer createTestUserNode(Integer userId, String name, String address, double geoLat, double geoLng) {
        String sql = "INSERT INTO sys_user_node (name, address, geo_lat, geo_lng, is_deprecated, country_id, user_id) " +
                     "VALUES (?, ?, ?, ?, " + dialectProvider.getBooleanFalse() + ", ?, ?)";
        executeRawSql(sql, name, address, new BigDecimal(geoLat), new BigDecimal(geoLng), 1, userId);

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

    private Distance createTestDistance(Integer fromNodeId, Integer toNodeId,
                                       Integer fromUserNodeId, Integer toUserNodeId,
                                       double fromLat, double fromLng,
                                       double toLat, double toLng,
                                       double distance) {
        Distance d = new Distance();
        d.setFromNodeId(fromNodeId);
        d.setToNodeId(toNodeId);
        d.setFromUserNodeId(fromUserNodeId);
        d.setToUserNodeId(toUserNodeId);
        d.setFromGeoLat(new BigDecimal(fromLat));
        d.setFromGeoLng(new BigDecimal(fromLng));
        d.setToGeoLat(new BigDecimal(toLat));
        d.setToGeoLng(new BigDecimal(toLng));
        d.setDistance(new BigDecimal(distance));
        d.setState(DistanceMatrixState.VALID);
        d.setUpdatedAt(LocalDateTime.now());
        d.setRetries(0);
        return d;
    }

    private Node createNodeObject(Integer id) {
        Node node = new Node();
        node.setId(id);
        return node;
    }
}
