package de.avatic.lcc.repositories.error;

import de.avatic.lcc.model.db.error.SysError;
import de.avatic.lcc.model.db.error.SysErrorTraceItem;
import de.avatic.lcc.model.db.error.SysErrorType;
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.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 SysErrorRepository.
 * <p>
 * Tests critical functionality across both MySQL and MSSQL:
 * - Insert single and multiple errors
 * - Trace item handling (one-to-many relationship)
 * - Pagination with filtering
 * - Reserved keyword handling ("file" column with escapeIdentifier)
 * - Enum handling (SysErrorType)
 * <p>
 * Run with:
 * <pre>
 * mvn test -Dspring.profiles.active=test,mysql -Dtest=SysErrorRepositoryIntegrationTest
 * mvn test -Dspring.profiles.active=test,mssql -Dtest=SysErrorRepositoryIntegrationTest
 * </pre>
 */
class SysErrorRepositoryIntegrationTest extends AbstractRepositoryIntegrationTest {

    @Autowired
    private SysErrorRepository sysErrorRepository;

    @Test
    void testInsertSingle() {
        // Given: Create error
        SysError error = createTestError("Test Error", "E001", "Test error message",
            SysErrorType.BACKEND);

        // When: Insert
        Integer errorId = sysErrorRepository.insert(error);

        // Then: Should have ID
        assertNotNull(errorId);
        assertTrue(errorId > 0);
    }

    @Test
    void testInsertWithTraceItems() {
        // Given: Create error with trace items
        SysError error = createTestError("Test Error", "E002", "Error with trace",
            SysErrorType.FRONTEND);

        List<SysErrorTraceItem> trace = new ArrayList<>();
        trace.add(createTraceItem(100, "TestClass.java", "testMethod", "/path/to/TestClass.java"));
        trace.add(createTraceItem(200, "AnotherClass.java", "anotherMethod", "/path/to/AnotherClass.java"));
        error.setTrace(trace);

        // When: Insert
        Integer errorId = sysErrorRepository.insert(error);

        // Then: Should insert error and trace items
        assertNotNull(errorId);
        assertTrue(errorId > 0);
    }

    @Test
    void testInsertMultiple() {
        // Given: Multiple errors
        List<SysError> errors = new ArrayList<>();
        errors.add(createTestError("Error 1", "E003", "First error", SysErrorType.BACKEND));
        errors.add(createTestError("Error 2", "E004", "Second error", SysErrorType.FRONTEND));

        // When: Insert all
        sysErrorRepository.insert(errors);

        // Then: Should succeed (no exception)
        // Verification happens implicitly through no exception
    }

    @Test
    void testListErrorsWithPagination() {
        // Given: Insert multiple errors
        for (int i = 1; i <= 5; i++) {
            SysError error = createTestError("Page Error " + i, "P" + String.format("%03d", i),
                "Error " + i, SysErrorType.BACKEND);
            sysErrorRepository.insert(error);
        }

        // When: List with pagination (page 1, size 3)
        SearchQueryPagination pagination = new SearchQueryPagination(1, 3);
        SearchQueryResult<SysError> result = sysErrorRepository.listErrors(Optional.empty(), pagination);

        // Then: Should respect pagination
        assertNotNull(result);
        assertNotNull(result.toList());
        assertTrue(result.toList().size() <= 3, "Should return at most 3 errors per page");
    }

    @Test
    void testListErrorsWithFilter() {
        // Given: Insert errors with different titles
        SysError error1 = createTestError("Database Connection Error", "F001",
            "Could not connect", SysErrorType.FRONTEND);
        sysErrorRepository.insert(error1);

        SysError error2 = createTestError("Validation Failed", "F002",
            "Invalid input", SysErrorType.BACKEND);
        sysErrorRepository.insert(error2);

        SysError error3 = createTestError("Database Query Error", "F003",
            "SQL syntax error", SysErrorType.FRONTEND);
        sysErrorRepository.insert(error3);

        // When: Filter by "Database"
        SearchQueryPagination pagination = new SearchQueryPagination(1, 10);
        SearchQueryResult<SysError> result = sysErrorRepository.listErrors(
            Optional.of("Database"), pagination);

        // Then: Should find errors with "Database" in title or message
        assertNotNull(result);
        assertTrue(result.toList().size() >= 2, "Should find at least 2 errors with 'Database'");

        for (SysError error : result.toList()) {
            boolean matches = error.getTitle().contains("Database") ||
                             error.getMessage().contains("Database") ||
                             error.getCode().contains("Database");
            assertTrue(matches, "Error should match filter: " + error.getTitle());
        }
    }

    @Test
    void testListErrorsLoadsTraceItems() {
        // Given: Insert error with trace
        SysError error = createTestError("Error with Trace", "T001",
            "Has stack trace", SysErrorType.FRONTEND);

        List<SysErrorTraceItem> trace = new ArrayList<>();
        trace.add(createTraceItem(150, "TraceTest.java", "testMethod", "/path/to/TraceTest.java"));
        trace.add(createTraceItem(250, "Helper.java", "helperMethod", "/path/to/Helper.java"));
        error.setTrace(trace);

        Integer errorId = sysErrorRepository.insert(error);

        // When: List errors (should load trace items)
        SearchQueryPagination pagination = new SearchQueryPagination(1, 10);
        SearchQueryResult<SysError> result = sysErrorRepository.listErrors(
            Optional.of("T001"), pagination);

        // Then: Should have trace items loaded
        assertFalse(result.toList().isEmpty());
        SysError loaded = result.toList().stream()
            .filter(e -> e.getCode().equals("T001"))
            .findFirst()
            .orElseThrow();

        assertNotNull(loaded.getTrace());
        assertEquals(2, loaded.getTrace().size(), "Should have 2 trace items");

        // Verify trace items
        assertEquals("TraceTest.java", loaded.getTrace().get(0).getFile());
        assertEquals("Helper.java", loaded.getTrace().get(1).getFile());
    }

    // Skipping bulk operation tests - requires complex setup with proper bulk_operation table schema

    @Test
    void testGetByBulkOperationIdNotFound() {
        // When: Get by non-existent bulk operation ID
        Optional<SysError> result = sysErrorRepository.getByBulkOperationId(99999);

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

    @Test
    void testErrorTypes() {
        // Test different error types
        for (SysErrorType type : SysErrorType.values()) {
            // Given: Create error with specific type
            SysError error = createTestError("Type Test " + type,
                "TYPE_" + type.name(), "Testing type " + type, type);

            // When: Insert
            Integer errorId = sysErrorRepository.insert(error);

            // Then: Should succeed
            assertNotNull(errorId);
        }
    }

    @Test
    void testReservedKeywordHandling() {
        // Test that "file" column (reserved keyword) is properly escaped
        // Given: Error with trace (trace has "file" column)
        SysError error = createTestError("Reserved Keyword Test", "RK001",
            "Testing reserved keyword", SysErrorType.FRONTEND);

        List<SysErrorTraceItem> trace = new ArrayList<>();
        trace.add(createTraceItem(100, "ReservedTest.java", "method", "/path/ReservedTest.java"));
        error.setTrace(trace);

        // When: Insert (should use dialectProvider.escapeIdentifier("file"))
        Integer errorId = sysErrorRepository.insert(error);

        // Then: Should succeed without SQL syntax error
        assertNotNull(errorId);

        // Verify retrieval also works
        SearchQueryPagination pagination = new SearchQueryPagination(1, 10);
        SearchQueryResult<SysError> result = sysErrorRepository.listErrors(
            Optional.of("RK001"), pagination);

        assertFalse(result.toList().isEmpty());
        SysError loaded = result.toList().get(0);
        assertEquals("ReservedTest.java", loaded.getTrace().get(0).getFile());
    }

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

    private SysError createTestError(String title, String code, String message, SysErrorType type) {
        SysError error = new SysError();
        error.setTitle(title);
        error.setCode(code);
        error.setMessage(message);
        error.setType(type);
        error.setRequest("TEST_REQUEST");
        error.setPinia("{}");
        return error;
    }

    private SysErrorTraceItem createTraceItem(Integer line, String file, String method, String fullPath) {
        SysErrorTraceItem item = new SysErrorTraceItem();
        item.setLine(line);
        item.setFile(file);
        item.setMethod(method);
        item.setFullPath(fullPath);
        return item;
    }
}
