package de.avatic.lcc.repositories.premise;

import de.avatic.lcc.model.db.premises.Premise;
import de.avatic.lcc.model.db.premises.PremiseListEntry;
import de.avatic.lcc.model.db.premises.PremiseState;
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.math.BigDecimal;
import java.util.List;
import java.util.Optional;

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

/**
 * Integration tests for PremiseRepository.
 * <p>
 * Tests critical functionality across both MySQL and MSSQL:
 * - Pagination (LIMIT/OFFSET vs OFFSET/FETCH)
 * - CURRENT_TIMESTAMP functions (NOW() vs GETDATE())
 * - Boolean literals (TRUE/FALSE vs 1/0)
 * - Dynamic IN clauses
 * - Complex JOIN queries with filtering
 * <p>
 * Run with:
 * <pre>
 * mvn test -Dspring.profiles.active=test,mysql -Dtest=PremiseRepositoryIntegrationTest
 * mvn test -Dspring.profiles.active=test,mssql -Dtest=PremiseRepositoryIntegrationTest
 * </pre>
 */
class PremiseRepositoryIntegrationTest extends AbstractRepositoryIntegrationTest {

    @Autowired
    private PremiseRepository premiseRepository;

    private Integer testUserId;
    private Integer testCountryId;
    private Integer testNodeId;
    private Integer testMaterialId;

    @BeforeEach
    void setupTestData() {
        // Clean up in correct order (respecting foreign key constraints)
        jdbcTemplate.update("DELETE FROM premise_destination");
        jdbcTemplate.update("DELETE FROM premise");
        jdbcTemplate.update("DELETE FROM material");

        // Clean up node-referencing tables before deleting nodes
        jdbcTemplate.update("DELETE FROM container_rate");
        jdbcTemplate.update("DELETE FROM country_matrix_rate");
        jdbcTemplate.update("DELETE FROM node_predecessor_entry");
        jdbcTemplate.update("DELETE FROM node_predecessor_chain");
        jdbcTemplate.update("DELETE FROM distance_matrix");

        jdbcTemplate.update("DELETE FROM node");
        jdbcTemplate.update("DELETE FROM sys_user");

        // Create test user
        testUserId = createUser("WD001", "test@example.com");

        // Get test country
        testCountryId = getCountryId("DE");

        // Create test node
        testNodeId = createNode("Test Supplier", "SUP-001", testCountryId);

        // Create test material
        testMaterialId = createMaterial("Test Material", "MAT-001");

        // Create some test premises
        createPremise(testUserId, testNodeId, testMaterialId, testCountryId, PremiseState.DRAFT);
        createPremise(testUserId, testNodeId, testMaterialId, testCountryId, PremiseState.COMPLETED);
        createPremise(testUserId, testNodeId, testMaterialId, testCountryId, PremiseState.ARCHIVED);
    }

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

        // When: List premises
        SearchQueryResult<PremiseListEntry> result = premiseRepository.listPremises(
            null, pagination, testUserId, null, null, null);

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

    @Test
    void testListPremisesPagination() {
        // Given: Create more premises and use pagination
        for (int i = 0; i < 10; i++) {
            createPremise(testUserId, testNodeId, testMaterialId, testCountryId, PremiseState.DRAFT);
        }

        SearchQueryPagination pagination = new SearchQueryPagination(1, 5);

        // When: List premises
        SearchQueryResult<PremiseListEntry> result = premiseRepository.listPremises(
            null, pagination, testUserId, null, null, null);

        // Then: Should respect limit
        assertNotNull(result);
        assertEquals(5, result.toList().size());
        assertTrue(result.getTotalElements() >= 13);
    }

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

        // When: List with filter
        SearchQueryResult<PremiseListEntry> result = premiseRepository.listPremises(
            "Test Supplier", pagination, testUserId, null, null, null);

        // Then: Should filter results
        assertNotNull(result);
        assertTrue(result.getTotalElements() >= 3);
        assertTrue(result.toList().stream()
            .allMatch(p -> p.getSupplierName().contains("Test Supplier")));
    }

    @Test
    void testListPremisesWithDraftFilter() {
        // Given: Pagination with draft filter
        SearchQueryPagination pagination = new SearchQueryPagination(1, 10);

        // When: List only drafts
        SearchQueryResult<PremiseListEntry> result = premiseRepository.listPremises(
            null, pagination, testUserId, true, false, false);

        // Then: Should return only DRAFT premises
        assertNotNull(result);
        assertTrue(result.toList().stream()
            .allMatch(p -> p.getState() == de.avatic.lcc.dto.calculation.PremiseState.DRAFT));
    }

    @Test
    void testInsert() {
        // When: Insert new premise
        Integer premiseId = premiseRepository.insert(
            testMaterialId, testNodeId, null,
            new BigDecimal("51.5"), new BigDecimal("7.5"),
            testCountryId, testUserId);

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

        Optional<Premise> inserted = premiseRepository.getPremiseById(premiseId);
        assertTrue(inserted.isPresent());
        assertEquals(testMaterialId, inserted.get().getMaterialId());
        assertEquals(testNodeId, inserted.get().getSupplierNodeId());
        assertEquals(PremiseState.DRAFT, inserted.get().getState());
    }

    @Test
    void testGetPremiseById() {
        // Given: Create premise
        Integer premiseId = createPremise(testUserId, testNodeId, testMaterialId, testCountryId, PremiseState.DRAFT);

        // When: Get by ID
        Optional<Premise> premise = premiseRepository.getPremiseById(premiseId);

        // Then: Should retrieve
        assertTrue(premise.isPresent());
        assertEquals(premiseId, premise.get().getId());
        assertEquals(testMaterialId, premise.get().getMaterialId());
    }

    @Test
    void testGetPremiseByIdNotFound() {
        // When: Get non-existent ID
        Optional<Premise> premise = premiseRepository.getPremiseById(99999);

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

    @Test
    void testGetPremisesById() {
        // Given: Create multiple premises
        Integer id1 = createPremise(testUserId, testNodeId, testMaterialId, testCountryId, PremiseState.DRAFT);
        Integer id2 = createPremise(testUserId, testNodeId, testMaterialId, testCountryId, PremiseState.DRAFT);

        // When: Get by IDs
        List<Premise> premises = premiseRepository.getPremisesById(List.of(id1, id2));

        // Then: Should retrieve both
        assertNotNull(premises);
        assertEquals(2, premises.size());
        assertTrue(premises.stream().anyMatch(p -> p.getId().equals(id1)));
        assertTrue(premises.stream().anyMatch(p -> p.getId().equals(id2)));
    }

    @Test
    void testGetPremisesByIdEmpty() {
        // When: Get with empty list
        List<Premise> premises = premiseRepository.getPremisesById(List.of());

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

    @Test
    void testResetPrice() {
        // Given: Create premise with price
        Integer premiseId = createPremise(testUserId, testNodeId, testMaterialId, testCountryId, PremiseState.DRAFT);

        // Set initial price
        premiseRepository.updatePrice(List.of(premiseId), new BigDecimal("100.50"), true, new BigDecimal("0.5"));

        // When: Reset price
        premiseRepository.resetPrice(List.of(premiseId));

        // Then: Price should be null
        Optional<Premise> premise = premiseRepository.getPremiseById(premiseId);
        assertTrue(premise.isPresent());
        assertNull(premise.get().getMaterialCost());
        assertFalse(premise.get().getFcaEnabled());
    }

    @Test
    void testUpdatePrice() {
        // Given: Create premise
        Integer premiseId = createPremise(testUserId, testNodeId, testMaterialId, testCountryId, PremiseState.DRAFT);

        // When: Update price
        premiseRepository.updatePrice(List.of(premiseId), new BigDecimal("200.75"), true, new BigDecimal("0.6"));

        // Then: Price should be updated
        Optional<Premise> premise = premiseRepository.getPremiseById(premiseId);
        assertTrue(premise.isPresent());
        assertEquals(0, new BigDecimal("200.75").compareTo(premise.get().getMaterialCost()));
        assertTrue(premise.get().getFcaEnabled());
        assertEquals(0, new BigDecimal("0.6").compareTo(premise.get().getOverseaShare()));
    }

    @Test
    void testUpdateMaterial() {
        // Given: Create premise
        Integer premiseId = createPremise(testUserId, testNodeId, testMaterialId, testCountryId, PremiseState.DRAFT);

        // When: Update material properties
        premiseRepository.updateMaterial(List.of(premiseId), "12345678", new BigDecimal("0.05"), true);

        // Then: Material properties should be updated
        Optional<Premise> premise = premiseRepository.getPremiseById(premiseId);
        assertTrue(premise.isPresent());
        assertEquals("12345678", premise.get().getHsCode());
        assertEquals(0, new BigDecimal("0.05").compareTo(premise.get().getTariffRate()));
        assertTrue(premise.get().getTariffUnlocked());
    }

    @Test
    void testSetMaterialId() {
        // Given: Create premise and new material
        Integer premiseId = createPremise(testUserId, testNodeId, testMaterialId, testCountryId, PremiseState.DRAFT);
        Integer newMaterialId = createMaterial("New Material", "MAT-002");

        // When: Set new material ID
        premiseRepository.setMaterialId(List.of(premiseId), newMaterialId);

        // Then: Material should be changed
        Optional<Premise> premise = premiseRepository.getPremiseById(premiseId);
        assertTrue(premise.isPresent());
        assertEquals(newMaterialId, premise.get().getMaterialId());
    }

    @Test
    void testDeletePremisesById() {
        // Given: Create DRAFT premises
        Integer draftId = createPremise(testUserId, testNodeId, testMaterialId, testCountryId, PremiseState.DRAFT);
        Integer completedId = createPremise(testUserId, testNodeId, testMaterialId, testCountryId, PremiseState.COMPLETED);

        // When: Delete (should only delete DRAFT)
        premiseRepository.deletePremisesById(List.of(draftId, completedId));

        // Then: DRAFT should be deleted, COMPLETED should remain
        assertFalse(premiseRepository.getPremiseById(draftId).isPresent());
        assertTrue(premiseRepository.getPremiseById(completedId).isPresent());
    }

    @Test
    void testSetStatus() {
        // Given: Create DRAFT premise
        Integer premiseId = createPremise(testUserId, testNodeId, testMaterialId, testCountryId, PremiseState.DRAFT);

        // When: Set status to COMPLETED
        premiseRepository.setStatus(List.of(premiseId), PremiseState.COMPLETED);

        // Then: Status should be updated
        Optional<Premise> premise = premiseRepository.getPremiseById(premiseId);
        assertTrue(premise.isPresent());
        assertEquals(PremiseState.COMPLETED, premise.get().getState());
    }

    @Test
    void testFindByMaterialIdAndSupplierId() {
        // Given: Create premise
        createPremise(testUserId, testNodeId, testMaterialId, testCountryId, PremiseState.DRAFT);

        // When: Find by material and supplier
        List<Premise> premises = premiseRepository.findByMaterialIdAndSupplierId(
            testMaterialId, testNodeId, null, testUserId);

        // Then: Should find premise
        assertNotNull(premises);
        assertFalse(premises.isEmpty());
        assertTrue(premises.stream().anyMatch(p ->
            p.getMaterialId().equals(testMaterialId) && p.getSupplierNodeId().equals(testNodeId)));
    }

    @Test
    void testGetPremisesByMaterialIdsAndSupplierIds() {
        // Given: Create premises
        createPremise(testUserId, testNodeId, testMaterialId, testCountryId, PremiseState.DRAFT);

        // When: Get by IDs
        List<Premise> premises = premiseRepository.getPremisesByMaterialIdsAndSupplierIds(
            List.of(testMaterialId), List.of(testNodeId), null, testUserId, true);

        // Then: Should find premises
        assertNotNull(premises);
        assertFalse(premises.isEmpty());
        assertTrue(premises.stream().allMatch(p -> p.getState() == PremiseState.DRAFT));
    }

    @Test
    void testFindAssociatedSuppliers() {
        // Given: Premises with suppliers
        createPremise(testUserId, testNodeId, testMaterialId, testCountryId, PremiseState.DRAFT);

        // When: Find associated suppliers
        List<Integer> supplierIds = premiseRepository.findAssociatedSuppliers(List.of(testMaterialId));

        // Then: Should find supplier
        assertNotNull(supplierIds);
        assertFalse(supplierIds.isEmpty());
        assertTrue(supplierIds.contains(testNodeId));
    }

    @Test
    void testGetIdsWithUnlockedTariffs() {
        // Given: Create premises with different tariff states
        Integer unlockedId = createPremise(testUserId, testNodeId, testMaterialId, testCountryId, PremiseState.DRAFT);
        Integer lockedId = createPremise(testUserId, testNodeId, testMaterialId, testCountryId, PremiseState.DRAFT);

        // Set one as unlocked
        premiseRepository.updateMaterial(List.of(unlockedId), null, null, true);

        // When: Get unlocked IDs
        List<Integer> unlockedIds = premiseRepository.getIdsWithUnlockedTariffs(List.of(unlockedId, lockedId));

        // Then: Should return only unlocked
        assertNotNull(unlockedIds);
        assertTrue(unlockedIds.contains(unlockedId));
        assertFalse(unlockedIds.contains(lockedId));
    }

    @Test
    void testGetPremiseCompletedCountByUserId() {
        // When: Get count
        Integer count = premiseRepository.getPremiseCompletedCountByUserId(testUserId);

        // Then: Should count COMPLETED premises
        assertNotNull(count);
        assertTrue(count >= 1, "Should have at least 1 COMPLETED premise from setup");
    }

    @Test
    void testGetPremiseDraftCountByUserId() {
        // When: Get count
        Integer count = premiseRepository.getPremiseDraftCountByUserId(testUserId);

        // Then: Should count DRAFT premises
        assertNotNull(count);
        assertTrue(count >= 1, "Should have at least 1 DRAFT premise from setup");
    }

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

    private Integer createUser(String workdayId, String email) {
        String sql = String.format(
            "INSERT INTO sys_user (workday_id, email, firstname, lastname, is_active) VALUES (?, ?, ?, ?, %s)",
            dialectProvider.getBooleanTrue());
        executeRawSql(sql, workdayId, email, "Test", "User");

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

    private Integer getCountryId(String isoCode) {
        return jdbcTemplate.queryForObject("SELECT id FROM country WHERE iso_code = ?", Integer.class, isoCode);
    }

    private Integer createNode(String name, String externalId, Integer countryId) {
        String sql = String.format(
            "INSERT INTO node (name, external_mapping_id, country_id, is_deprecated, is_source, is_destination, is_intermediate, address) " +
            "VALUES (?, ?, ?, %s, %s, %s, %s, 'Test Address')",
            dialectProvider.getBooleanFalse(),
            dialectProvider.getBooleanTrue(),
            dialectProvider.getBooleanTrue(),
            dialectProvider.getBooleanTrue());
        executeRawSql(sql, name, externalId, countryId);

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

    private Integer createMaterial(String name, String partNumber) {
        String sql = String.format(
            "INSERT INTO material (name, part_number, normalized_part_number, hs_code, is_deprecated) VALUES (?, ?, ?, '123456', %s)",
            dialectProvider.getBooleanFalse());
        executeRawSql(sql, name, partNumber, partNumber);

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

    private Integer createPremise(Integer userId, Integer nodeId, Integer materialId, Integer countryId, PremiseState state) {
        String sql = String.format(
            "INSERT INTO premise (user_id, supplier_node_id, material_id, country_id, state, geo_lat, geo_lng, created_at, updated_at) " +
            "VALUES (?, ?, ?, ?, ?, 51.5, 7.5, %s, %s)",
            isMysql() ? "NOW()" : "GETDATE()",
            isMysql() ? "NOW()" : "GETDATE()");
        executeRawSql(sql, userId, nodeId, materialId, countryId, state.name());

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