package de.avatic.lcc.repositories.bulk;

import de.avatic.lcc.dto.bulk.BulkFileType;
import de.avatic.lcc.dto.bulk.BulkOperationState;
import de.avatic.lcc.dto.bulk.BulkProcessingType;
import de.avatic.lcc.model.bulk.BulkOperation;
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.time.LocalDateTime;
import java.util.List;
import java.util.Optional;

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

/**
 * Integration tests for BulkOperationRepository.
 * <p>
 * Tests critical functionality across both MySQL and MSSQL:
 * - Pagination (LIMIT/OFFSET vs OFFSET/FETCH)
 * - Date subtraction (DATE_SUB vs DATEADD)
 * - Reserved word escaping ("file" column)
 * - Complex subqueries with pagination
 * - BLOB/VARBINARY handling
 * <p>
 * Run with:
 * <pre>
 * mvn test -Dspring.profiles.active=test,mysql -Dtest=BulkOperationRepositoryIntegrationTest
 * mvn test -Dspring.profiles.active=test,mssql -Dtest=BulkOperationRepositoryIntegrationTest
 * </pre>
 */
class BulkOperationRepositoryIntegrationTest extends AbstractRepositoryIntegrationTest {

    @Autowired
    private BulkOperationRepository bulkOperationRepository;

    private Integer testUserId1;
    private Integer testUserId2;
    private Integer testValidityPeriodId;

    @BeforeEach
    void setupTestData() {
        // Clean up in correct order (foreign key constraints)
        jdbcTemplate.update("UPDATE sys_error SET bulk_operation_id = NULL");
        jdbcTemplate.update("DELETE FROM bulk_operation");
        jdbcTemplate.update("DELETE FROM sys_user_group_mapping");
        jdbcTemplate.update("DELETE FROM sys_user");
        jdbcTemplate.update("DELETE FROM sys_group");

        // Clean up tables that reference validity_period
        jdbcTemplate.update("DELETE FROM container_rate");
        jdbcTemplate.update("DELETE FROM country_matrix_rate");
        jdbcTemplate.update("DELETE FROM country_property");

        jdbcTemplate.update("DELETE FROM validity_period");

        // Create test users
        testUserId1 = createTestUser("WD001", "user1@example.com", "User", "One", true);
        testUserId2 = createTestUser("WD002", "user2@example.com", "User", "Two", true);

        // Create test validity period
        testValidityPeriodId = createValidityPeriod("VALID");

        // Create some test operations
        createBulkOperation(testUserId1, BulkFileType.MATERIAL, BulkOperationState.COMPLETED,
            testValidityPeriodId, "file1".getBytes());
        createBulkOperation(testUserId1, BulkFileType.NODE, BulkOperationState.COMPLETED,
            null, "file2".getBytes());
        createBulkOperation(testUserId2, BulkFileType.CONTAINER_RATE, BulkOperationState.SCHEDULED,
            testValidityPeriodId, "file3".getBytes());
    }

    @Test
    void testInsertNewOperation() {
        // Given: New bulk operation
        BulkOperation newOp = new BulkOperation();
        newOp.setUserId(testUserId1);
        newOp.setFileType(BulkFileType.PACKAGING);
        newOp.setProcessingType(BulkProcessingType.IMPORT);
        newOp.setProcessState(BulkOperationState.SCHEDULED);
        newOp.setFile("test-file-content".getBytes());
        newOp.setValidityPeriodId(testValidityPeriodId);

        // When: Insert
        Integer id = bulkOperationRepository.insert(newOp);

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

        Optional<BulkOperation> inserted = bulkOperationRepository.getOperationById(id);
        assertTrue(inserted.isPresent());
        assertEquals(BulkFileType.PACKAGING, inserted.get().getFileType());
        assertEquals(BulkOperationState.SCHEDULED, inserted.get().getProcessState());
        assertArrayEquals("test-file-content".getBytes(), inserted.get().getFile());
    }

    @Test
    void testInsertWithNullValidityPeriod() {
        // Given: Operation without validity period
        BulkOperation newOp = new BulkOperation();
        newOp.setUserId(testUserId1);
        newOp.setFileType(BulkFileType.MATERIAL);
        newOp.setProcessingType(BulkProcessingType.IMPORT);
        newOp.setProcessState(BulkOperationState.SCHEDULED);
        newOp.setFile("test".getBytes());
        newOp.setValidityPeriodId(null);

        // When: Insert
        Integer id = bulkOperationRepository.insert(newOp);

        // Then: Should handle NULL validity_period_id
        Optional<BulkOperation> inserted = bulkOperationRepository.getOperationById(id);
        assertTrue(inserted.isPresent());
        assertNull(inserted.get().getValidityPeriodId());
    }

    @Test
    void testRemoveOldKeepsNewest() {
        // Given: Create 15 operations for user1 (all COMPLETED)
        for (int i = 0; i < 15; i++) {
            createBulkOperation(testUserId1, BulkFileType.MATERIAL, BulkOperationState.COMPLETED,
                null, ("file" + i).getBytes());
        }

        // When: Insert new operation (triggers removeOld)
        BulkOperation newOp = new BulkOperation();
        newOp.setUserId(testUserId1);
        newOp.setFileType(BulkFileType.NODE);
        newOp.setProcessingType(BulkProcessingType.IMPORT);
        newOp.setProcessState(BulkOperationState.SCHEDULED);
        newOp.setFile("newest".getBytes());
        bulkOperationRepository.insert(newOp);

        // Then: Should keep only 10 newest operations (+ the new one = 11 total, but new one is SCHEDULED)
        List<BulkOperation> operations = bulkOperationRepository.listByUserId(testUserId1);
        assertTrue(operations.size() <= 10, "Should keep only 10 operations");
    }

    @Test
    void testRemoveOldPreservesScheduledAndProcessing() {
        // Given: Create 5 SCHEDULED/PROCESSING and 12 COMPLETED operations
        createBulkOperation(testUserId1, BulkFileType.MATERIAL, BulkOperationState.SCHEDULED, null, "sched1".getBytes());
        createBulkOperation(testUserId1, BulkFileType.MATERIAL, BulkOperationState.PROCESSING, null, "proc1".getBytes());

        for (int i = 0; i < 12; i++) {
            createBulkOperation(testUserId1, BulkFileType.MATERIAL, BulkOperationState.COMPLETED,
                null, ("done" + i).getBytes());
        }

        // When: Remove old operations
        bulkOperationRepository.removeOld(testUserId1);

        // Then: SCHEDULED and PROCESSING should be preserved
        String sql = "SELECT COUNT(*) FROM bulk_operation WHERE user_id = ? AND state IN ('SCHEDULED', 'PROCESSING')";
        Integer preservedCount = jdbcTemplate.queryForObject(sql, Integer.class, testUserId1);
        assertTrue(preservedCount >= 2, "SCHEDULED and PROCESSING operations should be preserved");
    }

    @Test
    void testUpdateState() {
        // Given: Existing operation
        Integer opId = createBulkOperation(testUserId1, BulkFileType.MATERIAL,
            BulkOperationState.SCHEDULED, null, "test".getBytes());

        // When: Update state
        bulkOperationRepository.updateState(opId, BulkOperationState.PROCESSING);

        // Then: State should be updated
        Optional<BulkOperation> updated = bulkOperationRepository.getOperationById(opId);
        assertTrue(updated.isPresent());
        assertEquals(BulkOperationState.PROCESSING, updated.get().getProcessState());
    }

    @Test
    void testListByUserId() {
        // When: List operations for user1
        List<BulkOperation> operations = bulkOperationRepository.listByUserId(testUserId1);

        // Then: Should return operations for user1 only
        assertNotNull(operations);
        assertFalse(operations.isEmpty());
        assertTrue(operations.stream().allMatch(op -> op.getUserId().equals(testUserId1)));
    }

    @Test
    void testListByUserIdLimit() {
        // Given: Create 15 operations
        for (int i = 0; i < 15; i++) {
            createBulkOperation(testUserId1, BulkFileType.MATERIAL, BulkOperationState.COMPLETED,
                null, ("file" + i).getBytes());
        }

        // When: List operations
        List<BulkOperation> operations = bulkOperationRepository.listByUserId(testUserId1);

        // Then: Should respect limit of 10
        assertTrue(operations.size() <= 10, "Should limit to 10 operations");
    }

    @Test
    void testListByUserIdSkipsFile() {
        // When: List operations
        List<BulkOperation> operations = bulkOperationRepository.listByUserId(testUserId1);

        // Then: File should be null (skipFile=true)
        assertFalse(operations.isEmpty());
        assertNull(operations.getFirst().getFile(), "File should not be loaded in list");
    }

    @Test
    void testGetOperationById() {
        // Given: Existing operation
        Integer opId = createBulkOperation(testUserId1, BulkFileType.MATERIAL,
            BulkOperationState.COMPLETED, testValidityPeriodId, "test-content".getBytes());

        // When: Get by ID
        Optional<BulkOperation> operation = bulkOperationRepository.getOperationById(opId);

        // Then: Should retrieve with all fields
        assertTrue(operation.isPresent());
        assertEquals(opId, operation.get().getId());
        assertEquals(testUserId1, operation.get().getUserId());
        assertEquals(BulkFileType.MATERIAL, operation.get().getFileType());
        assertEquals(BulkOperationState.COMPLETED, operation.get().getProcessState());
        assertNotNull(operation.get().getFile());
        assertArrayEquals("test-content".getBytes(), operation.get().getFile());
        assertEquals(testValidityPeriodId, operation.get().getValidityPeriodId());
    }

    @Test
    void testGetOperationByIdNotFound() {
        // When: Get non-existent ID
        Optional<BulkOperation> operation = bulkOperationRepository.getOperationById(99999);

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

    @Test
    void testUpdate() {
        // Given: Existing operation
        Integer opId = createBulkOperation(testUserId1, BulkFileType.MATERIAL,
            BulkOperationState.SCHEDULED, null, "old-content".getBytes());

        Optional<BulkOperation> original = bulkOperationRepository.getOperationById(opId);
        assertTrue(original.isPresent());

        // When: Update operation
        BulkOperation updated = original.get();
        updated.setFileType(BulkFileType.NODE);
        updated.setProcessState(BulkOperationState.COMPLETED);
        updated.setFile("new-content".getBytes());
        updated.setValidityPeriodId(testValidityPeriodId);

        bulkOperationRepository.update(updated);

        // Then: Should be updated
        Optional<BulkOperation> result = bulkOperationRepository.getOperationById(opId);
        assertTrue(result.isPresent());
        assertEquals(BulkFileType.NODE, result.get().getFileType());
        assertEquals(BulkOperationState.COMPLETED, result.get().getProcessState());
        assertArrayEquals("new-content".getBytes(), result.get().getFile());
        assertEquals(testValidityPeriodId, result.get().getValidityPeriodId());
    }

    @Test
    void testCleanupTimeoutsViaListByUserId() {
        // Given: Create old PROCESSING operation (simulate timeout)
        Integer oldOpId = createBulkOperation(testUserId1, BulkFileType.MATERIAL,
            BulkOperationState.PROCESSING, null, "old".getBytes());

        // Set created_at to 2 hours ago
        String updateSql = isMysql()
            ? "UPDATE bulk_operation SET created_at = DATE_SUB(NOW(), INTERVAL 120 MINUTE) WHERE id = ?"
            : "UPDATE bulk_operation SET created_at = DATEADD(MINUTE, -120, GETDATE()) WHERE id = ?";
        jdbcTemplate.update(updateSql, oldOpId);

        // When: List operations (triggers cleanup)
        bulkOperationRepository.listByUserId(testUserId1);

        // Then: Old operation should be marked as EXCEPTION
        Optional<BulkOperation> cleaned = bulkOperationRepository.getOperationById(oldOpId);
        assertTrue(cleaned.isPresent());
        assertEquals(BulkOperationState.EXCEPTION, cleaned.get().getProcessState());
    }

    @Test
    void testCleanupTimeoutsDoesNotAffectRecent() {
        // Given: Recent PROCESSING operation
        Integer recentOpId = createBulkOperation(testUserId1, BulkFileType.MATERIAL,
            BulkOperationState.PROCESSING, null, "recent".getBytes());

        // When: List operations (triggers cleanup)
        bulkOperationRepository.listByUserId(testUserId1);

        // Then: Recent operation should remain PROCESSING
        Optional<BulkOperation> operation = bulkOperationRepository.getOperationById(recentOpId);
        assertTrue(operation.isPresent());
        assertEquals(BulkOperationState.PROCESSING, operation.get().getProcessState());
    }

    @Test
    void testRemoveOldDoesNotAffectOtherUsers() {
        // Given: Create 15 operations for user2
        for (int i = 0; i < 15; i++) {
            createBulkOperation(testUserId2, BulkFileType.MATERIAL, BulkOperationState.COMPLETED,
                null, ("file" + i).getBytes());
        }

        // When: Remove old for user1
        bulkOperationRepository.removeOld(testUserId1);

        // Then: User2 operations should remain unaffected
        String sql = "SELECT COUNT(*) FROM bulk_operation WHERE user_id = ?";
        Integer user2Count = jdbcTemplate.queryForObject(sql, Integer.class, testUserId2);
        assertTrue(user2Count >= 15, "User2 operations should not be affected");
    }

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

    private Integer 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);

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

    private Integer createValidityPeriod(String state) {
        String sql = "INSERT INTO validity_period (state, start_date) VALUES (?, " +
            (isMysql() ? "NOW()" : "GETDATE()") + ")";
        executeRawSql(sql, state);

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

    private Integer createBulkOperation(Integer userId, BulkFileType fileType, BulkOperationState state,
                                        Integer validityPeriodId, byte[] file) {
        String fileColumn = isMysql() ? "`file`" : "[file]";
        String sql = String.format(
            "INSERT INTO bulk_operation (user_id, bulk_file_type, bulk_processing_type, state, %s, validity_period_id) " +
            "VALUES (?, ?, ?, ?, ?, ?)",
            fileColumn);

        executeRawSql(sql, userId, fileType.name(), BulkProcessingType.IMPORT.name(), state.name(),
            file, validityPeriodId);

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