package de.avatic.lcc.repositories.premise;

import de.avatic.lcc.model.db.premises.PremiseState;
import de.avatic.lcc.model.db.premises.route.Destination;
import de.avatic.lcc.repositories.AbstractRepositoryIntegrationTest;
import de.avatic.lcc.util.exception.base.ForbiddenException;
import de.avatic.lcc.util.exception.internalerror.DatabaseException;
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.*;

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

/**
 * Integration tests for DestinationRepository.
 * <p>
 * Tests critical functionality across both MySQL and MSSQL:
 * - Dynamic IN clauses
 * - Named parameters
 * - JOIN queries
 * - NULL handling
 * - Authorization checks
 * - BigDecimal field operations
 * <p>
 * Run with:
 * <pre>
 * mvn test -Dspring.profiles.active=test,mysql -Dtest=DestinationRepositoryIntegrationTest
 * mvn test -Dspring.profiles.active=test,mssql -Dtest=DestinationRepositoryIntegrationTest
 * </pre>
 */
class DestinationRepositoryIntegrationTest extends AbstractRepositoryIntegrationTest {

    @Autowired
    private DestinationRepository destinationRepository;

    private Integer testUserId1;
    private Integer testUserId2;
    private Integer testCountryId;
    private Integer testNodeId1;
    private Integer testNodeId2;
    private Integer testMaterialId;
    private Integer testPremiseId1;
    private Integer testPremiseId2;

    @BeforeEach
    void setupTestData() {
        // Clean up in correct order (respecting foreign key constraints)
        jdbcTemplate.update("DELETE FROM premise_route_section");
        jdbcTemplate.update("DELETE FROM premise_route");
        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 users
        testUserId1 = createUser("WD001", "user1@example.com");
        testUserId2 = createUser("WD002", "user2@example.com");

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

        // Create test nodes
        testNodeId1 = createNode("Node 1", "NODE-001", testCountryId);
        testNodeId2 = createNode("Node 2", "NODE-002", testCountryId);

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

        // Create test premises
        testPremiseId1 = createPremise(testUserId1, testNodeId1, testMaterialId, testCountryId, PremiseState.DRAFT);
        testPremiseId2 = createPremise(testUserId2, testNodeId1, testMaterialId, testCountryId, PremiseState.DRAFT);

        // Create some test destinations
        createDestination(testPremiseId1, testNodeId1, 1000);
        createDestination(testPremiseId1, testNodeId2, 2000);
        createDestination(testPremiseId2, testNodeId1, 3000);
    }

    @Test
    void testGetById() {
        // Given: Create destination
        Integer destinationId = createDestination(testPremiseId1, testNodeId1, 500);

        // When: Get by ID
        Optional<Destination> destination = destinationRepository.getById(destinationId);

        // Then: Should retrieve
        assertTrue(destination.isPresent());
        assertEquals(destinationId, destination.get().getId());
        assertEquals(testPremiseId1, destination.get().getPremiseId());
        assertEquals(testNodeId1, destination.get().getDestinationNodeId());
        assertEquals(500, destination.get().getAnnualAmount());
    }

    @Test
    void testGetByIdNotFound() {
        // When: Get non-existent ID
        Optional<Destination> destination = destinationRepository.getById(99999);

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

    @Test
    void testGetByPremiseId() {
        // When: Get destinations for premise1
        List<Destination> destinations = destinationRepository.getByPremiseId(testPremiseId1);

        // Then: Should return all destinations for premise1
        assertNotNull(destinations);
        assertEquals(2, destinations.size());
        assertTrue(destinations.stream().allMatch(d -> d.getPremiseId().equals(testPremiseId1)));
    }

    @Test
    void testGetByPremiseIdAndUserId() {
        // When: Get destinations for premise1 with correct user
        List<Destination> destinations = destinationRepository.getByPremiseIdAndUserId(testPremiseId1, testUserId1);

        // Then: Should return destinations
        assertNotNull(destinations);
        assertEquals(2, destinations.size());
    }

    @Test
    void testGetByPremiseIdAndUserIdWrongUser() {
        // When: Get destinations for premise1 with wrong user
        List<Destination> destinations = destinationRepository.getByPremiseIdAndUserId(testPremiseId1, testUserId2);

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

    @Test
    void testGetByPremiseIdAndUserIdNonExistent() {
        // When: Get destinations for non-existent premise
        List<Destination> destinations = destinationRepository.getByPremiseIdAndUserId(99999, testUserId1);

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

    @Test
    void testUpdate() {
        // Given: Create destination
        Integer destinationId = createDestination(testPremiseId1, testNodeId1, 500);

        // When: Update
        destinationRepository.update(
            destinationId,
            1500,  // annualAmount
            new BigDecimal("10.50"),  // repackingCost
            new BigDecimal("5.25"),   // disposalCost
            new BigDecimal("8.75"),   // handlingCost
            true,                     // isD2d
            new BigDecimal("100.00"), // d2dRate
            new BigDecimal("48.00"),  // d2dLeadTime
            new BigDecimal("150.5")   // distanceD2d
        );

        // Then: Should be updated
        Optional<Destination> updated = destinationRepository.getById(destinationId);
        assertTrue(updated.isPresent());
        assertEquals(1500, updated.get().getAnnualAmount());
        assertEquals(0, new BigDecimal("10.50").compareTo(updated.get().getRepackingCost()));
        assertEquals(0, new BigDecimal("5.25").compareTo(updated.get().getDisposalCost()));
        assertEquals(0, new BigDecimal("8.75").compareTo(updated.get().getHandlingCost()));
        assertTrue(updated.get().getD2d());
        assertEquals(0, new BigDecimal("100.00").compareTo(updated.get().getRateD2d()));
    }

    @Test
    void testUpdateWithNulls() {
        // Given: Create destination
        Integer destinationId = createDestination(testPremiseId1, testNodeId1, 500);

        // When: Update with null values
        destinationRepository.update(
            destinationId,
            null,  // annualAmount
            null,  // repackingCost
            null,  // disposalCost
            null,  // handlingCost
            false, // isD2d
            null,  // d2dRate (should be null when isD2d is false)
            null,  // d2dLeadTime
            null   // distanceD2d
        );

        // Then: Should be updated with nulls
        Optional<Destination> updated = destinationRepository.getById(destinationId);
        assertTrue(updated.isPresent());
        assertFalse(updated.get().getD2d());
        assertNull(updated.get().getRateD2d());
    }

    @Test
    void testUpdateNonExistent() {
        // When/Then: Update non-existent destination should throw
        assertThrows(DatabaseException.class, () ->
            destinationRepository.update(99999, 100, null, null, null, false, null, null, null));
    }

    @Test
    void testDeleteById() {
        // Given: Create destination
        Integer destinationId = createDestination(testPremiseId1, testNodeId1, 500);

        // When: Delete
        destinationRepository.deleteById(destinationId);

        // Then: Should be deleted
        Optional<Destination> deleted = destinationRepository.getById(destinationId);
        assertFalse(deleted.isPresent());
    }

    @Test
    void testDeleteByIdNull() {
        // When: Delete with null (should not throw)
        destinationRepository.deleteById(null);

        // Then: No error
        assertTrue(true);
    }

    @Test
    void testGetOwnerIdById() {
        // Given: Create destination for user1
        Integer destinationId = createDestination(testPremiseId1, testNodeId1, 500);

        // When: Get owner ID
        Optional<Integer> ownerId = destinationRepository.getOwnerIdById(destinationId);

        // Then: Should return user1's ID
        assertTrue(ownerId.isPresent());
        assertEquals(testUserId1, ownerId.get());
    }

    @Test
    void testGetOwnerIdByIdNotFound() {
        // When: Get owner ID for non-existent destination
        Optional<Integer> ownerId = destinationRepository.getOwnerIdById(99999);

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

    @Test
    void testGetOwnerIdsByIds() {
        // Given: Create destinations for different users
        Integer dest1 = createDestination(testPremiseId1, testNodeId1, 500);
        Integer dest2 = createDestination(testPremiseId1, testNodeId2, 600);
        Integer dest3 = createDestination(testPremiseId2, testNodeId1, 700);

        // When: Get owner IDs
        Map<Integer, Integer> ownerMap = destinationRepository.getOwnerIdsByIds(List.of(dest1, dest2, dest3));

        // Then: Should return correct mappings
        assertEquals(3, ownerMap.size());
        assertEquals(testUserId1, ownerMap.get(dest1));
        assertEquals(testUserId1, ownerMap.get(dest2));
        assertEquals(testUserId2, ownerMap.get(dest3));
    }

    @Test
    void testGetOwnerIdsByIdsEmpty() {
        // When: Get owner IDs for empty list
        Map<Integer, Integer> ownerMap = destinationRepository.getOwnerIdsByIds(List.of());

        // Then: Should return empty map
        assertTrue(ownerMap.isEmpty());
    }

    @Test
    void testGetOwnerIdsByIdsNull() {
        // When: Get owner IDs for null
        Map<Integer, Integer> ownerMap = destinationRepository.getOwnerIdsByIds(null);

        // Then: Should return empty map
        assertTrue(ownerMap.isEmpty());
    }

    @Test
    void testGetByPremiseIdsAndNodeIdsMap() {
        // Given: Map of premise IDs to node IDs
        Map<Integer, List<Integer>> premiseToNodes = new HashMap<>();
        premiseToNodes.put(testPremiseId1, List.of(testNodeId1, testNodeId2));
        premiseToNodes.put(testPremiseId2, List.of(testNodeId1));

        // When: Get destinations
        Map<Integer, List<Destination>> result =
            destinationRepository.getByPremiseIdsAndNodeIds(premiseToNodes, testUserId1);

        // Then: Should return destinations for user1's premises only
        assertNotNull(result);
        assertTrue(result.containsKey(testPremiseId1));
        assertEquals(2, result.get(testPremiseId1).size());
    }

    @Test
    void testGetByPremiseIdsAndNodeIdsMapEmpty() {
        // When: Get with empty map
        Map<Integer, List<Destination>> result =
            destinationRepository.getByPremiseIdsAndNodeIds(Map.of(), testUserId1);

        // Then: Should return empty map
        assertTrue(result.isEmpty());
    }

    @Test
    void testGetByPremiseIdsAndNodeIdsLists() {
        // When: Get destinations by lists
        Map<Integer, List<Destination>> result = destinationRepository.getByPremiseIdsAndNodeIds(
            List.of(testPremiseId1, testPremiseId2),
            List.of(testNodeId1, testNodeId2),
            testUserId1
        );

        // Then: Should return only user1's destinations
        // Note: The method queries for all premises in the list, then filters by userId in the JOIN
        // So premise2 (owned by user2) should NOT be returned
        assertNotNull(result);
        assertTrue(result.containsKey(testPremiseId1));

        // The method returns empty list for premises not owned by the user
        if (result.containsKey(testPremiseId2)) {
            assertTrue(result.get(testPremiseId2).isEmpty(), "User2's premise should return empty list");
        }
    }

    @Test
    void testInsert() {
        // Given: New destination
        Destination newDest = new Destination();
        newDest.setPremiseId(testPremiseId1);
        newDest.setDestinationNodeId(testNodeId1);
        newDest.setCountryId(testCountryId);
        newDest.setAnnualAmount(999);
        newDest.setRepackingCost(new BigDecimal("12.50"));
        newDest.setHandlingCost(new BigDecimal("8.25"));
        newDest.setDisposalCost(new BigDecimal("3.75"));
        newDest.setD2d(true);
        newDest.setRateD2d(new BigDecimal("50.00"));
        newDest.setLeadTimeD2d(24);
        newDest.setGeoLat(new BigDecimal("51.5"));
        newDest.setGeoLng(new BigDecimal("7.5"));
        newDest.setDistanceD2d(new BigDecimal("100.0"));

        // When: Insert
        Integer id = destinationRepository.insert(newDest);

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

        Optional<Destination> inserted = destinationRepository.getById(id);
        assertTrue(inserted.isPresent());
        assertEquals(999, inserted.get().getAnnualAmount());
        assertEquals(0, new BigDecimal("12.50").compareTo(inserted.get().getRepackingCost()));
        assertTrue(inserted.get().getD2d());
    }

    @Test
    void testInsertWithNulls() {
        // Given: New destination with nullable fields as null
        Destination newDest = new Destination();
        newDest.setPremiseId(testPremiseId1);
        newDest.setDestinationNodeId(testNodeId1);
        newDest.setCountryId(testCountryId);
        newDest.setAnnualAmount(null);  // nullable
        newDest.setRepackingCost(null);
        newDest.setHandlingCost(null);
        newDest.setDisposalCost(null);
        newDest.setD2d(false);
        newDest.setRateD2d(null);
        newDest.setLeadTimeD2d(null);  // nullable
        newDest.setGeoLat(new BigDecimal("51.5"));
        newDest.setGeoLng(new BigDecimal("7.5"));
        newDest.setDistanceD2d(null);

        // When: Insert
        Integer id = destinationRepository.insert(newDest);

        // Then: Should be inserted successfully
        assertNotNull(id);

        Optional<Destination> inserted = destinationRepository.getById(id);
        assertTrue(inserted.isPresent());
        // Note: annualAmount might be null or 0 depending on database behavior with nullable INT
        // Just verify the record was inserted with the non-null fields
        assertEquals(testPremiseId1, inserted.get().getPremiseId());
        assertEquals(testNodeId1, inserted.get().getDestinationNodeId());
        assertFalse(inserted.get().getD2d());
    }

    @Test
    void testCheckOwnerSuccess() {
        // Given: Destination owned by user1
        Integer destId = createDestination(testPremiseId1, testNodeId1, 500);

        // When/Then: Check with correct owner should not throw
        assertDoesNotThrow(() -> destinationRepository.checkOwner(destId, testUserId1));
    }

    @Test
    void testCheckOwnerWrongUser() {
        // Given: Destination owned by user1
        Integer destId = createDestination(testPremiseId1, testNodeId1, 500);

        // When/Then: Check with wrong user should throw
        assertThrows(ForbiddenException.class, () ->
            destinationRepository.checkOwner(destId, testUserId2));
    }

    @Test
    void testCheckOwnerNonExistent() {
        // When/Then: Check non-existent destination should throw
        assertThrows(ForbiddenException.class, () ->
            destinationRepository.checkOwner(99999, testUserId1));
    }

    @Test
    void testCheckOwnerListSuccess() {
        // Given: Destinations owned by user1
        Integer dest1 = createDestination(testPremiseId1, testNodeId1, 500);
        Integer dest2 = createDestination(testPremiseId1, testNodeId2, 600);

        // When/Then: Check with correct owner should not throw
        assertDoesNotThrow(() -> destinationRepository.checkOwner(List.of(dest1, dest2), testUserId1));
    }

    @Test
    void testCheckOwnerListMixedOwners() {
        // Given: Destinations with different owners
        Integer dest1 = createDestination(testPremiseId1, testNodeId1, 500);
        Integer dest2 = createDestination(testPremiseId2, testNodeId1, 600);

        // When/Then: Check with user1 should throw (dest2 is owned by user2)
        assertThrows(ForbiddenException.class, () ->
            destinationRepository.checkOwner(List.of(dest1, dest2), testUserId1));
    }

    @Test
    void testCheckOwnerListEmpty() {
        // When/Then: Check with empty list should not throw
        assertDoesNotThrow(() -> destinationRepository.checkOwner(List.of(), testUserId1));
    }

    @Test
    void testCheckOwnerListNull() {
        // When/Then: Check with null should not throw
        assertDoesNotThrow(() -> destinationRepository.checkOwner((List<Integer>) null, testUserId1));
    }

    // ========== 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);
    }

    private Integer createDestination(Integer premiseId, Integer nodeId, Integer annualAmount) {
        String sql = "INSERT INTO premise_destination (premise_id, destination_node_id, annual_amount, country_id, geo_lat, geo_lng) " +
            "VALUES (?, ?, ?, ?, 51.5, 7.5)";
        executeRawSql(sql, premiseId, nodeId, annualAmount, testCountryId);

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