package de.avatic.lcc.repositories.users;

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

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

/**
 * Integration tests for AppRepository.
 * <p>
 * Tests critical functionality across both MySQL and MSSQL:
 * - CRUD operations for apps
 * - App-group mapping management
 * - INSERT IGNORE for mapping synchronization
 * - Group membership retrieval
 * <p>
 * Run with:
 * <pre>
 * mvn test -Dspring.profiles.active=test,mysql -Dtest=AppRepositoryIntegrationTest
 * mvn test -Dspring.profiles.active=test,mssql -Dtest=AppRepositoryIntegrationTest
 * </pre>
 */
class AppRepositoryIntegrationTest extends AbstractRepositoryIntegrationTest {

    @Autowired
    private AppRepository appRepository;

    private Integer testGroupId1;
    private Integer testGroupId2;

    @BeforeEach
    void setupTestData() {
        // Create test groups
        testGroupId1 = createTestGroup("TEST_GROUP_1", "Test Group 1");
        testGroupId2 = createTestGroup("TEST_GROUP_2", "Test Group 2");
    }

    @Test
    void testListApps() {
        // Given: Insert test apps
        App app1 = createTestApp("Test App 1", "client1", "secret1");
        Integer app1Id = appRepository.update(app1);

        App app2 = createTestApp("Test App 2", "client2", "secret2");
        Integer app2Id = appRepository.update(app2);

        // When: List all apps
        List<App> apps = appRepository.listApps();

        // Then: Should include test apps
        assertNotNull(apps);
        assertTrue(apps.size() >= 2, "Should have at least 2 apps");

        List<Integer> appIds = apps.stream().map(App::getId).toList();
        assertTrue(appIds.contains(app1Id));
        assertTrue(appIds.contains(app2Id));
    }

    @Test
    void testGetById() {
        // Given: Create app
        App app = createTestApp("Test App", "client123", "secret123");
        Integer appId = appRepository.update(app);

        // When: Get by ID
        Optional<App> retrieved = appRepository.getById(appId);

        // Then: Should retrieve app
        assertTrue(retrieved.isPresent());
        assertEquals("Test App", retrieved.get().getName());
        assertEquals("client123", retrieved.get().getClientId());
        assertEquals("secret123", retrieved.get().getClientSecret());
    }

    @Test
    void testGetByIdNotFound() {
        // When: Get non-existent app
        Optional<App> result = appRepository.getById(99999);

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

    @Test
    void testGetByClientId() {
        // Given: Create app with specific client ID
        App app = createTestApp("OAuth App", "oauth_client_id", "oauth_secret");
        appRepository.update(app);

        // When: Get by client ID
        Optional<App> retrieved = appRepository.getByClientId("oauth_client_id");

        // Then: Should retrieve app
        assertTrue(retrieved.isPresent());
        assertEquals("OAuth App", retrieved.get().getName());
    }

    @Test
    void testGetByClientIdNotFound() {
        // When: Get non-existent client ID
        Optional<App> result = appRepository.getByClientId("nonexistent");

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

    @Test
    void testInsertApp() {
        // Given: New app
        App app = createTestApp("New App", "new_client", "new_secret");

        // When: Insert (id is null)
        Integer appId = appRepository.update(app);

        // Then: Should have generated ID
        assertNotNull(appId);
        assertTrue(appId > 0);

        // Verify inserted
        Optional<App> saved = appRepository.getById(appId);
        assertTrue(saved.isPresent());
        assertEquals("New App", saved.get().getName());
    }

    @Test
    void testUpdateApp() {
        // Given: Existing app
        App app = createTestApp("Original Name", "update_client", "update_secret");
        Integer appId = appRepository.update(app);

        // When: Update app name
        app.setId(appId);
        app.setName("Updated Name");
        appRepository.update(app);

        // Then: Name should be updated
        Optional<App> updated = appRepository.getById(appId);
        assertTrue(updated.isPresent());
        assertEquals("Updated Name", updated.get().getName());
    }

    @Test
    void testDeleteApp() {
        // Given: Create app
        App app = createTestApp("Delete Me", "delete_client", "delete_secret");
        Integer appId = appRepository.update(app);

        // When: Delete
        appRepository.delete(appId);

        // Then: Should not exist
        Optional<App> deleted = appRepository.getById(appId);
        assertFalse(deleted.isPresent());
    }

    @Test
    void testAppWithGroups() {
        // Given: App with groups
        App app = createTestApp("App with Groups", "grouped_client", "grouped_secret");
        Group group1 = new Group();
        group1.setName("TEST_GROUP_1");
        Group group2 = new Group();
        group2.setName("TEST_GROUP_2");
        app.setGroups(List.of(group1, group2));

        // When: Insert app with groups
        Integer appId = appRepository.update(app);

        // Then: Should have group mappings
        Optional<App> saved = appRepository.getById(appId);
        assertTrue(saved.isPresent());
        assertEquals(2, saved.get().getGroups().size());

        List<String> groupNames = saved.get().getGroups().stream()
            .map(Group::getName)
            .toList();
        assertTrue(groupNames.contains("TEST_GROUP_1"));
        assertTrue(groupNames.contains("TEST_GROUP_2"));
    }

    @Test
    void testUpdateAppGroups() {
        // Given: App with one group
        App app = createTestApp("Group Update Test", "group_update_client", "group_update_secret");
        Group group1 = new Group();
        group1.setName("TEST_GROUP_1");
        app.setGroups(List.of(group1));
        Integer appId = appRepository.update(app);

        // When: Update to different group
        app.setId(appId);
        Group group2 = new Group();
        group2.setName("TEST_GROUP_2");
        app.setGroups(List.of(group2));
        appRepository.update(app);

        // Then: Should have new group only
        Optional<App> updated = appRepository.getById(appId);
        assertTrue(updated.isPresent());
        assertEquals(1, updated.get().getGroups().size());
        assertEquals("TEST_GROUP_2", updated.get().getGroups().get(0).getName());
    }

    @Test
    void testDeleteAppCascadesGroupMappings() {
        // Given: App with groups
        App app = createTestApp("Cascade Delete Test", "cascade_client", "cascade_secret");
        Group group1 = new Group();
        group1.setName("TEST_GROUP_1");
        app.setGroups(List.of(group1));
        Integer appId = appRepository.update(app);

        // When: Delete app
        appRepository.delete(appId);

        // Then: Group mappings should be deleted
        String sql = "SELECT COUNT(*) FROM sys_app_group_mapping WHERE app_id = ?";
        Integer count = jdbcTemplate.queryForObject(sql, Integer.class, appId);
        assertEquals(0, count, "Group mappings should be deleted with app");
    }

    @Test
    void testAppWithEmptyGroups() {
        // Given: App with empty groups list
        App app = createTestApp("No Groups App", "no_groups_client", "no_groups_secret");
        app.setGroups(new ArrayList<>());

        // When: Insert app
        Integer appId = appRepository.update(app);

        // Then: Should have no group mappings
        Optional<App> saved = appRepository.getById(appId);
        assertTrue(saved.isPresent());
        assertEquals(0, saved.get().getGroups().size());
    }

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

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

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

    private App createTestApp(String name, String clientId, String clientSecret) {
        App app = new App();
        app.setName(name);
        app.setClientId(clientId);
        app.setClientSecret(clientSecret);
        app.setGroups(new ArrayList<>());
        return app;
    }
}
