package de.avatic.lcc.repositories.users;

import de.avatic.lcc.model.db.users.Group;
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.util.List;

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

/**
 * Integration tests for GroupRepository.
 * <p>
 * Tests critical functionality across both MySQL and MSSQL:
 * - Pagination (LIMIT/OFFSET vs OFFSET/FETCH)
 * - UPSERT operations (ON DUPLICATE KEY UPDATE vs MERGE)
 * - IN clause with dynamic parameters
 * <p>
 * Run with:
 * <pre>
 * mvn test -Dspring.profiles.active=test,mysql -Dtest=GroupRepositoryIntegrationTest
 * mvn test -Dspring.profiles.active=test,mssql -Dtest=GroupRepositoryIntegrationTest
 * </pre>
 */
class GroupRepositoryIntegrationTest extends AbstractRepositoryIntegrationTest {

    @Autowired
    private GroupRepository groupRepository;

    @BeforeEach
    void setupTestData() {
        // Clean up groups
        jdbcTemplate.update("DELETE FROM sys_user_group_mapping");
        jdbcTemplate.update("DELETE FROM sys_group");

        // Create test groups
        createTestGroup("Administrators", "Admin users with full access");
        createTestGroup("Developers", "Software developers");
        createTestGroup("Analysts", "Data analysts");
        createTestGroup("Viewers", "Read-only users");
    }

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

        // When: List groups
        SearchQueryResult<Group> result = groupRepository.listGroups(pagination);

        // Then: Should return all groups
        assertNotNull(result);
        assertEquals(4, result.getTotalElements());
        assertFalse(result.toList().isEmpty());
    }

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

        // When: List groups
        SearchQueryResult<Group> result = groupRepository.listGroups(pagination);

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

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

        // When: List groups
        SearchQueryResult<Group> result = groupRepository.listGroups(pagination);

        // Then: Should be ordered by group_name
        assertNotNull(result);
        List<Group> groups = result.toList();
        for (int i = 1; i < groups.size(); i++) {
            assertTrue(groups.get(i - 1).getName().compareTo(groups.get(i).getName()) <= 0,
                "Groups should be ordered alphabetically by name");
        }
    }

    @Test
    void testFindGroupIds() {
        // When: Find group IDs by names
        List<Integer> ids = groupRepository.findGroupIds(List.of("Administrators", "Developers"));

        // Then: Should find 2 groups
        assertNotNull(ids);
        assertEquals(2, ids.size());
    }

    @Test
    void testFindGroupIdsSingle() {
        // When: Find single group ID
        List<Integer> ids = groupRepository.findGroupIds(List.of("Administrators"));

        // Then: Should find 1 group
        assertNotNull(ids);
        assertEquals(1, ids.size());
    }

    @Test
    void testFindGroupIdsNotFound() {
        // When: Find non-existent group
        List<Integer> ids = groupRepository.findGroupIds(List.of("NonExistent"));

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

    @Test
    void testFindGroupIdsEmptyList() {
        // When: Find with empty list
        List<Integer> ids = groupRepository.findGroupIds(List.of());

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

    @Test
    void testFindGroupIdsNull() {
        // When: Find with null
        List<Integer> ids = groupRepository.findGroupIds(null);

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

    @Test
    void testUpdateGroupInsert() {
        // Given: New group
        Group newGroup = new Group();
        newGroup.setName("Testers");
        newGroup.setDescription("QA testers");

        // When: Update (insert)
        groupRepository.updateGroup(newGroup);

        // Then: Should be inserted
        SearchQueryPagination pagination = new SearchQueryPagination(1, 10);
        SearchQueryResult<Group> result = groupRepository.listGroups(pagination);
        assertEquals(5, result.getTotalElements());

        // Verify the new group exists
        List<Integer> ids = groupRepository.findGroupIds(List.of("Testers"));
        assertEquals(1, ids.size());
    }

    @Test
    void testUpdateGroupUpsert() {
        // Given: Existing group name
        Group updateGroup = new Group();
        updateGroup.setName("Administrators");
        updateGroup.setDescription("Updated admin description");

        // When: Update (upsert)
        groupRepository.updateGroup(updateGroup);

        // Then: Should update description
        SearchQueryPagination pagination = new SearchQueryPagination(1, 10);
        SearchQueryResult<Group> result = groupRepository.listGroups(pagination);

        // Should still have 4 groups
        assertEquals(4, result.getTotalElements());

        // Find the updated group
        Group updated = result.toList().stream()
            .filter(g -> "Administrators".equals(g.getName()))
            .findFirst()
            .orElseThrow();
        assertEquals("Updated admin description", updated.getDescription());
    }

    @Test
    void testFindGroupIdsMultiple() {
        // When: Find multiple group IDs
        List<Integer> ids = groupRepository.findGroupIds(
            List.of("Administrators", "Developers", "Analysts"));

        // Then: Should find 3 groups
        assertNotNull(ids);
        assertEquals(3, ids.size());
    }

    @Test
    void testFindGroupIdsPartialMatch() {
        // When: Find mix of existing and non-existing groups
        List<Integer> ids = groupRepository.findGroupIds(
            List.of("Administrators", "NonExistent", "Developers"));

        // Then: Should find only existing groups
        assertNotNull(ids);
        assertEquals(2, ids.size());
    }

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

    private void createTestGroup(String name, String description) {
        String sql = "INSERT INTO sys_group (group_name, group_description) VALUES (?, ?)";
        executeRawSql(sql, name, description);
    }
}
