package de.avatic.lcc.repositories.users;

import de.avatic.lcc.model.db.nodes.Node;
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.math.BigDecimal;
import java.util.Collection;
import java.util.List;
import java.util.Optional;

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

/**
 * Integration tests for UserNodeRepository.
 * <p>
 * Tests critical functionality across both MySQL and MSSQL:
 * - CRUD operations (Create, Read)
 * - Search with filtering and pagination
 * - Boolean literal compatibility (is_deprecated filtering)
 * - Bulk operations (getByIds, checkOwner)
 * <p>
 * Run with:
 * <pre>
 * mvn test -Dspring.profiles.active=test,mysql -Dtest=UserNodeRepositoryIntegrationTest
 * mvn test -Dspring.profiles.active=test,mssql -Dtest=UserNodeRepositoryIntegrationTest
 * </pre>
 */
class UserNodeRepositoryIntegrationTest extends AbstractRepositoryIntegrationTest {

    @Autowired
    private UserNodeRepository userNodeRepository;

    private Integer testUserId1;
    private Integer testUserId2;
    private Integer testCountryId;

    @BeforeEach
    void setupTestData() {
        // Create test users
        testUserId1 = createTestUser("user1@test.com", "WORKDAY001");
        testUserId2 = createTestUser("user2@test.com", "WORKDAY002");

        // Use existing country (id=1 should exist from Flyway migrations)
        testCountryId = 1;
    }

    @Test
    void testAddAndRetrieve() {
        // Given: Create user node
        Node node = createTestNode("Test Supplier Berlin", "Berlin, Germany", 52.5200, 13.4050);

        // When: Add
        Integer nodeId = userNodeRepository.add(testUserId1, node);

        // Then: Should be inserted successfully
        assertNotNull(nodeId);
        assertTrue(nodeId > 0);

        // When: Retrieve by ID
        Optional<Node> retrieved = userNodeRepository.getById(nodeId);

        // Then: Should retrieve successfully
        assertTrue(retrieved.isPresent(), "User node should be retrievable after insert");
        assertEquals("Test Supplier Berlin", retrieved.get().getName());
        assertEquals("Berlin, Germany", retrieved.get().getAddress());
        assertEquals(new BigDecimal("52.5200"), retrieved.get().getGeoLat());
        assertEquals(new BigDecimal("13.4050"), retrieved.get().getGeoLng());
        assertFalse(retrieved.get().getDeprecated());
        assertEquals(testCountryId, retrieved.get().getCountryId());
        assertTrue(retrieved.get().isUserNode());
    }

    @Test
    void testGetByIdNotFound() {
        // When: Get by non-existent ID
        Optional<Node> result = userNodeRepository.getById(99999);

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

    @Test
    void testSearchNodeWithFilter() {
        // Given: Insert multiple user nodes
        Node node1 = createTestNode("Berlin Supplier", "Berlin", 52.5200, 13.4050);
        userNodeRepository.add(testUserId1, node1);

        Node node2 = createTestNode("Munich Supplier", "Munich", 48.1351, 11.5820);
        userNodeRepository.add(testUserId1, node2);

        Node node3 = createTestNode("Hamburg Distribution", "Hamburg", 53.5511, 9.9937);
        userNodeRepository.add(testUserId1, node3);

        // When: Search for "Supplier"
        Collection<Node> results = userNodeRepository.searchNode("Supplier", 10, testUserId1, false);

        // Then: Should find nodes with "Supplier" in name
        assertNotNull(results);
        assertTrue(results.size() >= 2, "Should find at least 2 nodes with 'Supplier'");

        for (Node node : results) {
            assertTrue(node.getName().contains("Supplier") || node.getAddress().contains("Supplier"),
                "Results should match filter");
        }
    }

    @Test
    void testSearchNodeWithPagination() {
        // Given: Insert multiple user nodes
        for (int i = 1; i <= 5; i++) {
            Node node = createTestNode("Supplier " + i, "Address " + i, 50.0 + i, 10.0 + i);
            userNodeRepository.add(testUserId1, node);
        }

        // When: Search with limit 3
        Collection<Node> results = userNodeRepository.searchNode(null, 3, testUserId1, false);

        // Then: Should respect pagination limit
        assertNotNull(results);
        assertTrue(results.size() <= 3, "Should return at most 3 nodes");
    }

    @Test
    void testSearchNodeExcludeDeprecated() {
        // Given: Insert deprecated and non-deprecated user nodes
        Node deprecated = createTestNode("Deprecated Supplier", "Old Address", 50.0, 10.0);
        deprecated.setDeprecated(true);
        userNodeRepository.add(testUserId1, deprecated);

        Node active = createTestNode("Active Supplier", "New Address", 51.0, 11.0);
        userNodeRepository.add(testUserId1, active);

        // When: Search excluding deprecated
        Collection<Node> results = userNodeRepository.searchNode(null, 10, testUserId1, true);

        // Then: Should not include deprecated nodes
        assertNotNull(results);
        for (Node node : results) {
            assertFalse(node.getDeprecated(), "Should not include deprecated nodes");
        }
    }

    @Test
    void testSearchNodeByUserId() {
        // Given: Insert nodes for different users
        Node user1Node = createTestNode("User 1 Supplier", "User 1 Address", 50.0, 10.0);
        userNodeRepository.add(testUserId1, user1Node);

        Node user2Node = createTestNode("User 2 Supplier", "User 2 Address", 51.0, 11.0);
        userNodeRepository.add(testUserId2, user2Node);

        // When: Search for user1 nodes
        Collection<Node> user1Results = userNodeRepository.searchNode(null, 10, testUserId1, false);

        // Then: Should only return user1 nodes
        assertNotNull(user1Results);
        // Can't assert exact count because other tests might have created nodes
        // Just verify all returned nodes belong to user1
        for (Node node : user1Results) {
            // Note: We can't directly verify userId in the Node object since it's not stored there
            // The verification happens implicitly through the WHERE clause
            assertNotNull(node.getName());
        }
    }

    @Test
    void testGetByIds() {
        // Given: Insert multiple user nodes
        Node node1 = createTestNode("Bulk Node 1", "Address 1", 50.0, 10.0);
        Integer id1 = userNodeRepository.add(testUserId1, node1);

        Node node2 = createTestNode("Bulk Node 2", "Address 2", 51.0, 11.0);
        Integer id2 = userNodeRepository.add(testUserId1, node2);

        Node node3 = createTestNode("Bulk Node 3", "Address 3", 52.0, 12.0);
        Integer id3 = userNodeRepository.add(testUserId1, node3);

        // When: Get by IDs
        List<Integer> ids = List.of(id1, id2, id3);
        Collection<Node> nodes = userNodeRepository.getByIds(ids);

        // Then: Should return all requested nodes
        assertNotNull(nodes);
        assertEquals(3, nodes.size(), "Should return exactly 3 nodes");

        List<Integer> retrievedIds = nodes.stream().map(Node::getId).toList();
        assertTrue(retrievedIds.contains(id1));
        assertTrue(retrievedIds.contains(id2));
        assertTrue(retrievedIds.contains(id3));
    }

    @Test
    void testGetByIdsEmptyList() {
        // When: Get by empty list
        Collection<Node> nodes = userNodeRepository.getByIds(List.of());

        // Then: Should return empty collection
        assertNotNull(nodes);
        assertTrue(nodes.isEmpty());
    }

    @Test
    void testGetOwnerById() {
        // Given: Insert user node
        Node node = createTestNode("Owner Test Node", "Address", 50.0, 10.0);
        Integer nodeId = userNodeRepository.add(testUserId1, node);

        // When: Get owner
        Optional<Integer> owner = userNodeRepository.getOwnerById(nodeId);

        // Then: Should return correct user ID
        assertTrue(owner.isPresent());
        assertEquals(testUserId1, owner.get());
    }

    @Test
    void testGetOwnerByIdNotFound() {
        // When: Get owner of non-existent node
        Optional<Integer> owner = userNodeRepository.getOwnerById(99999);

        // Then: Should return empty
        assertFalse(owner.isPresent());
    }

    @Test
    void testCheckOwnerValid() {
        // Given: Insert user nodes
        Node node1 = createTestNode("Owner Check 1", "Address 1", 50.0, 10.0);
        Integer id1 = userNodeRepository.add(testUserId1, node1);

        Node node2 = createTestNode("Owner Check 2", "Address 2", 51.0, 11.0);
        Integer id2 = userNodeRepository.add(testUserId1, node2);

        // When/Then: Should not throw exception for valid owner
        assertDoesNotThrow(() ->
            userNodeRepository.checkOwner(List.of(id1, id2), testUserId1)
        );
    }

    @Test
    void testCheckOwnerInvalid() {
        // Given: Insert user node for user1
        Node node = createTestNode("Owner Violation", "Address", 50.0, 10.0);
        Integer nodeId = userNodeRepository.add(testUserId1, node);

        // When/Then: Should throw exception when user2 tries to access user1's node
        assertThrows(Exception.class, () ->
            userNodeRepository.checkOwner(List.of(nodeId), testUserId2)
        );
    }

    @Test
    void testCheckOwnerEmptyList() {
        // When/Then: Should not throw exception for empty list
        assertDoesNotThrow(() ->
            userNodeRepository.checkOwner(List.of(), testUserId1)
        );
    }

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

    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 Node createTestNode(String name, String address, double geoLat, double geoLng) {
        Node node = new Node();
        node.setName(name);
        node.setAddress(address);
        node.setGeoLat(new BigDecimal(String.valueOf(geoLat)));
        node.setGeoLng(new BigDecimal(String.valueOf(geoLng)));
        node.setDeprecated(false);
        node.setCountryId(testCountryId);
        return node;
    }
}
