package de.avatic.lcc.repositories.users;

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

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

/**
 * Integration tests for UserRepository.
 * <p>
 * Tests critical functionality across both MySQL and MSSQL:
 * - Pagination (LIMIT/OFFSET vs OFFSET/FETCH)
 * - INSERT IGNORE (MySQL) vs MERGE (MSSQL)
 * - Complex group mapping operations
 * - User lookup by various fields
 * <p>
 * Run with:
 * <pre>
 * mvn test -Dspring.profiles.active=test,mysql -Dtest=UserRepositoryIntegrationTest
 * mvn test -Dspring.profiles.active=test,mssql -Dtest=UserRepositoryIntegrationTest
 * </pre>
 */
class UserRepositoryIntegrationTest extends AbstractRepositoryIntegrationTest {

    @Autowired
    private UserRepository userRepository;

    @Autowired
    private GroupRepository groupRepository;

    private Integer testGroupAdminId;
    private Integer testGroupDevId;

    @BeforeEach
    void setupTestData() {
        // Clean up in correct order
        jdbcTemplate.update("DELETE FROM sys_user_group_mapping");
        jdbcTemplate.update("DELETE FROM sys_user");
        jdbcTemplate.update("DELETE FROM sys_group");

        // Create test groups
        createTestGroup("Administrators", "Admin users");
        createTestGroup("Developers", "Dev users");
        createTestGroup("Viewers", "Read-only users");

        // Get group IDs
        testGroupAdminId = groupRepository.findGroupIds(List.of("Administrators")).getFirst();
        testGroupDevId = groupRepository.findGroupIds(List.of("Developers")).getFirst();

        // Create test users
        createTestUser("WD001", "john.doe@example.com", "John", "Doe", true);
        createTestUser("WD002", "jane.smith@example.com", "Jane", "Smith", true);
        createTestUser("WD003", "bob.inactive@example.com", "Bob", "Inactive", false);

        // Create group mappings
        createUserGroupMapping(getUserIdByWorkday("WD001"), testGroupAdminId);
        createUserGroupMapping(getUserIdByWorkday("WD002"), testGroupDevId);
    }

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

        // When: List users
        SearchQueryResult<User> result = userRepository.listUsers(pagination);

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

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

        // When: List users
        SearchQueryResult<User> result = userRepository.listUsers(pagination);

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

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

        // When: List users
        SearchQueryResult<User> result = userRepository.listUsers(pagination);

        // Then: Should be ordered by workday_id
        assertNotNull(result);
        List<User> users = result.toList();
        for (int i = 1; i < users.size(); i++) {
            assertTrue(users.get(i - 1).getWorkdayId().compareTo(users.get(i).getWorkdayId()) <= 0,
                "Users should be ordered by workday_id");
        }
    }

    @Test
    void testUpdateInsertNewUser() {
        // Given: New user
        User newUser = new User();
        newUser.setWorkdayId("WD004");
        newUser.setEmail("new.user@example.com");
        newUser.setFirstName("New");
        newUser.setLastName("User");
        newUser.setActive(true);
        newUser.setGroups(List.of());

        // When: Update (insert)
        Integer userId = userRepository.update(newUser);

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

        User inserted = userRepository.getById(userId);
        assertNotNull(inserted);
        assertEquals("WD004", inserted.getWorkdayId());
        assertEquals("new.user@example.com", inserted.getEmail());
    }

    @Test
    void testUpdateExistingUser() {
        // Given: Existing user
        User user = userRepository.getByWorkdayId("WD001").orElseThrow();
        user.setEmail("john.updated@example.com");
        user.setFirstName("Johnny");

        // When: Update
        Integer userId = userRepository.update(user);

        // Then: Should be updated
        assertNotNull(userId);

        User updated = userRepository.getById(userId);
        assertEquals("john.updated@example.com", updated.getEmail());
        assertEquals("Johnny", updated.getFirstName());
        assertEquals("Doe", updated.getLastName()); // Unchanged
    }

    @Test
    void testUpdateUserWithGroups() {
        // Given: New user with groups
        User newUser = new User();
        newUser.setWorkdayId("WD005");
        newUser.setEmail("grouped.user@example.com");
        newUser.setFirstName("Grouped");
        newUser.setLastName("User");
        newUser.setActive(true);

        Group adminGroup = new Group();
        adminGroup.setName("Administrators");
        Group devGroup = new Group();
        devGroup.setName("Developers");
        newUser.setGroups(List.of(adminGroup, devGroup));

        // When: Update (insert)
        Integer userId = userRepository.update(newUser);

        // Then: Should have groups
        User inserted = userRepository.getById(userId);
        assertNotNull(inserted.getGroups());
        assertEquals(2, inserted.getGroups().size());
    }

    @Test
    void testUpdateUserRemoveGroups() {
        // Given: User with groups
        User user = userRepository.getByWorkdayId("WD001").orElseThrow();
        assertEquals(1, user.getGroups().size());

        // When: Update with empty groups
        user.setGroups(List.of());
        userRepository.update(user);

        // Then: Groups should be removed
        User updated = userRepository.getById(user.getId());
        assertTrue(updated.getGroups().isEmpty());
    }

    @Test
    void testUpdateUserChangeGroups() {
        // Given: User with Admin group
        User user = userRepository.getByWorkdayId("WD001").orElseThrow();
        assertEquals("Administrators", user.getGroups().getFirst().getName());

        // When: Change to Dev group
        Group devGroup = new Group();
        devGroup.setName("Developers");
        user.setGroups(List.of(devGroup));
        userRepository.update(user);

        // Then: Should have Dev group
        User updated = userRepository.getById(user.getId());
        assertEquals(1, updated.getGroups().size());
        assertEquals("Developers", updated.getGroups().getFirst().getName());
    }

    @Test
    void testCount() {
        // When: Count users
        Integer count = userRepository.count();

        // Then: Should return 3
        assertEquals(3, count);
    }

    @Test
    void testGetUserIdByWorkdayId() {
        // When: Get user ID by workday ID
        Integer userId = userRepository.getUserIdByWorkdayId("WD001");

        // Then: Should find user
        assertNotNull(userId);
        assertTrue(userId > 0);
    }

    @Test
    void testGetUserIdByWorkdayIdNotFound() {
        // When: Get non-existent user
        Integer userId = userRepository.getUserIdByWorkdayId("NONEXISTENT");

        // Then: Should return null
        assertNull(userId);
    }

    @Test
    void testGetByWorkdayId() {
        // When: Get user by workday ID
        Optional<User> user = userRepository.getByWorkdayId("WD001");

        // Then: Should find user
        assertTrue(user.isPresent());
        assertEquals("WD001", user.get().getWorkdayId());
        assertEquals("john.doe@example.com", user.get().getEmail());
        assertEquals("John", user.get().getFirstName());
    }

    @Test
    void testGetByWorkdayIdNotFound() {
        // When: Get non-existent user
        Optional<User> user = userRepository.getByWorkdayId("NONEXISTENT");

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

    @Test
    void testGetById() {
        // Given: User ID
        Integer userId = userRepository.getUserIdByWorkdayId("WD001");

        // When: Get by ID
        User user = userRepository.getById(userId);

        // Then: Should find user
        assertNotNull(user);
        assertEquals("WD001", user.getWorkdayId());
    }

    @Test
    void testGetByIdNotFound() {
        // When: Get non-existent ID
        User user = userRepository.getById(99999);

        // Then: Should return null
        assertNull(user);
    }

    @Test
    void testGetByEmail() {
        // When: Get user by email
        User user = userRepository.getByEmail("john.doe@example.com");

        // Then: Should find user
        assertNotNull(user);
        assertEquals("WD001", user.getWorkdayId());
        assertEquals("john.doe@example.com", user.getEmail());
    }

    @Test
    void testGetByEmailNotFound() {
        // When: Get non-existent email
        User user = userRepository.getByEmail("nonexistent@example.com");

        // Then: Should return null
        assertNull(user);
    }

    @Test
    void testUserWithGroupMemberships() {
        // When: Get user with groups
        User user = userRepository.getByWorkdayId("WD001").orElseThrow();

        // Then: Should have group memberships
        assertNotNull(user.getGroups());
        assertEquals(1, user.getGroups().size());
        assertEquals("Administrators", user.getGroups().getFirst().getName());
    }

    @Test
    void testUserWithoutGroupMemberships() {
        // When: Get user without groups
        User user = userRepository.getByWorkdayId("WD003").orElseThrow();

        // Then: Should have empty groups
        assertNotNull(user.getGroups());
        assertTrue(user.getGroups().isEmpty());
    }

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

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

    private void createTestUser(String workdayId, String email, String firstName, String lastName, boolean isActive) {
        String isActiveValue = isActive ? dialectProvider.getBooleanTrue() : dialectProvider.getBooleanFalse();
        String sql = String.format(
            "INSERT INTO sys_user (workday_id, email, firstname, lastname, is_active) VALUES (?, ?, ?, ?, %s)",
            isActiveValue);
        executeRawSql(sql, workdayId, email, firstName, lastName);
    }

    private Integer getUserIdByWorkday(String workdayId) {
        return jdbcTemplate.queryForObject("SELECT id FROM sys_user WHERE workday_id = ?", Integer.class, workdayId);
    }

    private void createUserGroupMapping(Integer userId, Integer groupId) {
        String sql = "INSERT INTO sys_user_group_mapping (user_id, group_id) VALUES (?, ?)";
        executeRawSql(sql, userId, groupId);
    }
}
