package de.avatic.lcc.repositories;

import de.avatic.lcc.dto.generic.NodeType;
import de.avatic.lcc.repositories.pagination.SearchQueryPagination;
import de.avatic.lcc.repositories.pagination.SearchQueryResult;
import de.avatic.lcc.model.db.nodes.Node;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;

import java.math.BigDecimal;
import java.util.List;
import java.util.Optional;

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

/**
 * Integration tests for NodeRepository.
 * <p>
 * Tests critical functionality across both MySQL and MSSQL:
 * - Basic CRUD operations
 * - Pagination with ORDER BY (MSSQL requirement)
 * - Haversine distance calculations
 * - Complex search queries
 * <p>
 * Run with:
 * <pre>
 * mvn test -Dspring.profiles.active=test,mysql -Dtest=NodeRepositoryIntegrationTest
 * mvn test -Dspring.profiles.active=test,mssql -Dtest=NodeRepositoryIntegrationTest
 * </pre>
 */
class NodeRepositoryIntegrationTest extends AbstractRepositoryIntegrationTest {

    @Autowired
    private NodeRepository nodeRepository;

    @Test
    void testInsertAndRetrieveNode() {
        // Given
        Node node = new Node();
        node.setName("Test Node");
        node.setAddress("Test Address 123");
        node.setGeoLat(new BigDecimal("52.5200"));
        node.setGeoLng(new BigDecimal("13.4050"));
        node.setDeprecated(false);
        node.setCountryId(1); // Assuming country with id=1 exists in Flyway migrations

        // When
        Integer nodeId = nodeRepository.insert(node);

        // Then
        assertNotNull(nodeId, "Node ID should not be null");
        assertTrue(nodeId > 0, "Node ID should be positive");

        Optional<Node> retrieved = nodeRepository.getById(nodeId);
        assertTrue(retrieved.isPresent(), "Node should be retrievable after creation");
        assertEquals("Test Node", retrieved.get().getName());
        assertEquals("Test Address 123", retrieved.get().getAddress());
    }

    @Test
    void testUpdateNode() {
        // Given: Create a node first
        Node node = createTestNode("Original Name", "Original Address", "50.0", "10.0");
        Integer nodeId = nodeRepository.insert(node);

        // When: Update the node
        Node updatedNode = nodeRepository.getById(nodeId).orElseThrow();
        updatedNode.setName("Updated Name");
        updatedNode.setAddress("Updated Address");
        nodeRepository.update(updatedNode);

        // Then: Verify update
        Node result = nodeRepository.getById(nodeId).orElseThrow();
        assertEquals("Updated Name", result.getName());
        assertEquals("Updated Address", result.getAddress());
    }

    @Test
    void testDeprecateNode() {
        // Given: Create a node
        Node node = createTestNode("Node to Deprecate", "Address", "50.0", "10.0");
        Integer nodeId = nodeRepository.insert(node);

        // When: Deprecate the node
        nodeRepository.setDeprecatedById(nodeId);

        // Then: Verify node is deprecated
        Node deprecated = nodeRepository.getById(nodeId).orElseThrow();
        assertTrue(deprecated.getDeprecated(), "Node should be marked as deprecated");
    }

    @Test
    void testListNodesWithPagination() {
        // Given: Create multiple nodes
        for (int i = 1; i <= 5; i++) {
            Node node = createTestNode("Pagination Node " + i, "Address " + i, "50." + i, "10." + i);
            nodeRepository.insert(node);
        }

        // When: List nodes with pagination (page 1, size 3)
        SearchQueryPagination pagination = new SearchQueryPagination(1, 3);
        SearchQueryResult<Node> result = nodeRepository.listNodes(null, false, pagination);

        // Then: Verify pagination works (ORDER BY is required for MSSQL)
        assertNotNull(result);
        assertNotNull(result.toList());
        assertTrue(result.toList().size() <= 3, "Should return at most 3 nodes per page");
    }

    @Test
    void testSearchNodeWithFilter() {
        // Given: Create nodes with different names
        Node node1 = createTestNode("Berlin Node Test", "Berlin Street 1", "52.5200", "13.4050");
        Node node2 = createTestNode("Munich Node Test", "Munich Street 1", "48.1351", "11.5820");
        Node node3 = createTestNode("Hamburg Node Test", "Hamburg Street 1", "53.5511", "9.9937");
        nodeRepository.insert(node1);
        nodeRepository.insert(node2);
        nodeRepository.insert(node3);

        // When: Search for nodes containing "Berlin"
        List<Node> results = nodeRepository.searchNode("Berlin", 10, null, false);

        // Then: Should find Berlin node
        assertFalse(results.isEmpty(), "Should find at least one node");
        assertTrue(results.stream().anyMatch(n -> n.getName().contains("Berlin")),
                "Should contain Berlin node");
    }

    @Test
    void testGetByDistanceWithHaversineFormula() {
        // Given: Create a reference node (Berlin)
        Node referenceNode = createTestNode("Berlin Distance Test", "Berlin Center", "52.5200", "13.4050");
        referenceNode.setUserNode(false);
        Integer refId = nodeRepository.insert(referenceNode);
        referenceNode.setId(refId);

        // Create a nearby node (Potsdam, ~30km from Berlin)
        Node nearbyNode = createTestNode("Potsdam Distance Test", "Potsdam Center", "52.3906", "13.0645");
        nodeRepository.insert(nearbyNode);

        // Create a far node (Munich, ~500km from Berlin)
        Node farNode = createTestNode("Munich Distance Test", "Munich Center", "48.1351", "11.5820");
        nodeRepository.insert(farNode);

        // When: Get nodes within 100km radius
        // The Haversine formula returns distance in kilometers for both MySQL and MSSQL
        List<Node> nodesWithin100km = nodeRepository.getByDistance(referenceNode, 100);

        // Then: Should find nearby node but not far node
        assertNotNull(nodesWithin100km);
        assertTrue(nodesWithin100km.stream().anyMatch(n -> n.getName().contains("Potsdam")),
                "Should find Potsdam (30km away)");
        assertFalse(nodesWithin100km.stream().anyMatch(n -> n.getName().contains("Munich")),
                "Should not find Munich (500km away)");
    }

    @Test
    void testGetByDistanceExcludingReferenceNode() {
        // Given: Create reference node
        Node referenceNode = createTestNode("Reference Node Distance", "Ref Address", "50.0", "10.0");
        referenceNode.setUserNode(false);
        Integer refId = nodeRepository.insert(referenceNode);
        referenceNode.setId(refId);

        // Create nearby node
        Node nearbyNode = createTestNode("Nearby Node Distance", "Nearby Address", "50.1", "10.1");
        nodeRepository.insert(nearbyNode);

        // When: Get nodes within large radius
        List<Node> results = nodeRepository.getByDistance(referenceNode, 1000);

        // Then: Reference node itself should be excluded (via id != ?)
        assertFalse(results.stream().anyMatch(n -> n.getId().equals(refId)),
                "Reference node should be excluded from results");
    }

    @Test
    void testBooleanLiteralCompatibility() {
        // Given: Create deprecated and non-deprecated nodes
        Node deprecatedNode = createTestNode("Deprecated Boolean Test", "Addr1", "50.0", "10.0");
        Integer depId = nodeRepository.insert(deprecatedNode);
        nodeRepository.setDeprecatedById(depId);

        Node activeNode = createTestNode("Active Boolean Test", "Addr2", "50.1", "10.1");
        nodeRepository.insert(activeNode);

        // When: Search excluding deprecated nodes
        List<Node> activeNodes = nodeRepository.searchNode("Boolean Test", 100, null, true);

        // Then: Should not include deprecated node
        assertFalse(activeNodes.stream().anyMatch(n -> n.getId().equals(depId)),
                "Should exclude deprecated nodes when excludeDeprecated=true");
    }

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

    private Node createTestNode(String name, String address, String lat, String lng) {
        Node node = new Node();
        node.setName(name);
        node.setAddress(address);
        node.setGeoLat(new BigDecimal(lat));
        node.setGeoLng(new BigDecimal(lng));
        node.setDeprecated(false);
        node.setCountryId(1); // Assuming country id=1 exists
        node.setUserNode(false);
        return node;
    }
}
