package de.avatic.lcc.repositories.premise;

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

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

/**
 * Integration tests for RouteNodeRepository.
 * <p>
 * Tests critical functionality across both MySQL and MSSQL:
 * - Boolean literals (TRUE/FALSE vs 1/0)
 * - Dynamic IN clauses
 * - JOIN queries
 * - NULL handling for optional foreign keys
 * - BigDecimal geo coordinates
 * <p>
 * Run with:
 * <pre>
 * mvn test -Dspring.profiles.active=test,mysql -Dtest=RouteNodeRepositoryIntegrationTest
 * mvn test -Dspring.profiles.active=test,mssql -Dtest=RouteNodeRepositoryIntegrationTest
 * </pre>
 */
class RouteNodeRepositoryIntegrationTest extends AbstractRepositoryIntegrationTest {

    @Autowired
    private RouteNodeRepository routeNodeRepository;

    private Integer testUserId;
    private Integer testCountryId;
    private Integer testNodeId;
    private Integer testMaterialId;
    private Integer testPremiseId;
    private Integer testDestinationId;
    private Integer testRouteId;

    @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_node");
        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 user
        testUserId = createUser("WD001", "test@example.com");

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

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

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

        // Create test premise
        testPremiseId = createPremise(testUserId, testNodeId, testMaterialId, testCountryId, PremiseState.DRAFT);

        // Create test destination
        testDestinationId = createDestination(testPremiseId, testNodeId);

        // Create test route
        testRouteId = createRoute(testDestinationId);

        // Create some test route nodes
        createRouteNode("Node A", testNodeId, testCountryId, true, false, false);
        createRouteNode("Node B", testNodeId, testCountryId, false, true, false);
        createRouteNode("Node C", testNodeId, testCountryId, false, false, true);
    }

    @Test
    void testGetById() {
        // Given: Create route node
        Integer nodeId = createRouteNode("Test Node", testNodeId, testCountryId, true, true, true);

        // When: Get by ID
        Optional<RouteNode> node = routeNodeRepository.getById(nodeId);

        // Then: Should retrieve
        assertTrue(node.isPresent());
        assertEquals(nodeId, node.get().getId());
        assertEquals("Test Node", node.get().getName());
        assertTrue(node.get().getDestination());
        assertTrue(node.get().getIntermediate());
        assertTrue(node.get().getSource());
    }

    @Test
    void testGetByIdNotFound() {
        // When: Get non-existent ID
        Optional<RouteNode> node = routeNodeRepository.getById(99999);

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

    @Test
    void testInsert() {
        // Given: New route node
        RouteNode newNode = new RouteNode();
        newNode.setName("New Route Node");
        newNode.setAddress("123 Test Street");
        newNode.setGeoLat(new BigDecimal("51.5074"));
        newNode.setGeoLng(new BigDecimal("0.1278"));
        newNode.setDestination(true);
        newNode.setIntermediate(false);
        newNode.setSource(true);
        newNode.setNodeId(testNodeId);
        newNode.setUserNodeId(null);
        newNode.setOutdated(false);
        newNode.setCountryId(testCountryId);
        newNode.setExternalMappingId("EXT-001");

        // When: Insert
        Integer id = routeNodeRepository.insert(newNode);

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

        Optional<RouteNode> inserted = routeNodeRepository.getById(id);
        assertTrue(inserted.isPresent());
        assertEquals("New Route Node", inserted.get().getName());
        assertTrue(inserted.get().getDestination());
        assertFalse(inserted.get().getIntermediate());
        assertTrue(inserted.get().getSource());
        assertFalse(inserted.get().getOutdated());
        assertEquals("EXT-001", inserted.get().getExternalMappingId());
    }

    @Test
    void testInsertWithNulls() {
        // Given: Route node with nullable fields as null
        RouteNode newNode = new RouteNode();
        newNode.setName("Minimal Node");
        newNode.setAddress("Address");
        newNode.setGeoLat(new BigDecimal("50.0"));
        newNode.setGeoLng(new BigDecimal("8.0"));
        newNode.setDestination(false);
        newNode.setIntermediate(false);
        newNode.setSource(false);
        newNode.setNodeId(null);  // nullable FK
        newNode.setUserNodeId(null);  // nullable FK
        newNode.setOutdated(false);
        newNode.setCountryId(testCountryId);
        newNode.setExternalMappingId("MIN-EXT");  // NOT NULL in schema

        // When: Insert
        Integer id = routeNodeRepository.insert(newNode);

        // Then: Should be inserted with nullable FKs as null
        assertNotNull(id);

        Optional<RouteNode> inserted = routeNodeRepository.getById(id);
        assertTrue(inserted.isPresent());
        assertEquals("Minimal Node", inserted.get().getName());
        assertEquals("MIN-EXT", inserted.get().getExternalMappingId());
    }

    @Test
    void testDeleteAllById() {
        // Given: Multiple route nodes
        Integer node1 = createRouteNode("Node 1", testNodeId, testCountryId, true, false, false);
        Integer node2 = createRouteNode("Node 2", testNodeId, testCountryId, false, true, false);
        Integer node3 = createRouteNode("Node 3", testNodeId, testCountryId, false, false, true);

        // When: Delete first two
        routeNodeRepository.deleteAllById(List.of(node1, node2));

        // Then: Should delete specified nodes
        assertFalse(routeNodeRepository.getById(node1).isPresent());
        assertFalse(routeNodeRepository.getById(node2).isPresent());
        assertTrue(routeNodeRepository.getById(node3).isPresent());
    }

    @Test
    void testDeleteAllByIdEmpty() {
        // Given: Some route nodes
        Integer nodeId = createRouteNode("Node", testNodeId, testCountryId, true, false, false);

        // When: Delete with empty list
        routeNodeRepository.deleteAllById(List.of());

        // Then: Should not delete anything
        assertTrue(routeNodeRepository.getById(nodeId).isPresent());
    }

    @Test
    void testDeleteAllByIdNull() {
        // Given: Some route nodes
        Integer nodeId = createRouteNode("Node", testNodeId, testCountryId, true, false, false);

        // When: Delete with null
        routeNodeRepository.deleteAllById(null);

        // Then: Should not delete anything
        assertTrue(routeNodeRepository.getById(nodeId).isPresent());
    }

    @Test
    void testGetFromNodeBySectionId() {
        // Given: Create route section with from and to nodes
        Integer fromNodeId = createRouteNode("From Node", testNodeId, testCountryId, false, false, true);
        Integer toNodeId = createRouteNode("To Node", testNodeId, testCountryId, true, false, false);
        Integer sectionId = createRouteSection(testRouteId, fromNodeId, toNodeId);

        // When: Get from node by section ID
        Optional<RouteNode> fromNode = routeNodeRepository.getFromNodeBySectionId(sectionId);

        // Then: Should retrieve from node (verify by name and properties, not ID due to JOIN)
        assertTrue(fromNode.isPresent());
        assertEquals("From Node", fromNode.get().getName());
        assertTrue(fromNode.get().getSource());
    }

    @Test
    void testGetFromNodeBySectionIdNotFound() {
        // When: Get from node for non-existent section
        Optional<RouteNode> fromNode = routeNodeRepository.getFromNodeBySectionId(99999);

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

    @Test
    void testGetToNodeBySectionId() {
        // Given: Create route section with from and to nodes
        Integer fromNodeId = createRouteNode("From Node", testNodeId, testCountryId, false, false, true);
        Integer toNodeId = createRouteNode("To Node", testNodeId, testCountryId, true, false, false);
        Integer sectionId = createRouteSection(testRouteId, fromNodeId, toNodeId);

        // When: Get to node by section ID
        Optional<RouteNode> toNode = routeNodeRepository.getToNodeBySectionId(sectionId);

        // Then: Should retrieve to node (verify by name and properties, not ID due to JOIN)
        assertTrue(toNode.isPresent());
        assertEquals("To Node", toNode.get().getName());
        assertTrue(toNode.get().getDestination());
    }

    @Test
    void testGetToNodeBySectionIdNotFound() {
        // When: Get to node for non-existent section
        Optional<RouteNode> toNode = routeNodeRepository.getToNodeBySectionId(99999);

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

    @Test
    void testBooleanFields() {
        // Given: Create nodes with different boolean combinations
        RouteNode node1 = new RouteNode();
        node1.setName("All True");
        node1.setAddress("Address 1");
        node1.setGeoLat(new BigDecimal("50.0"));
        node1.setGeoLng(new BigDecimal("8.0"));
        node1.setDestination(true);
        node1.setIntermediate(true);
        node1.setSource(true);
        node1.setOutdated(true);
        node1.setCountryId(testCountryId);
        node1.setExternalMappingId("EXT1");

        RouteNode node2 = new RouteNode();
        node2.setName("All False");
        node2.setAddress("Address 2");
        node2.setGeoLat(new BigDecimal("51.0"));
        node2.setGeoLng(new BigDecimal("9.0"));
        node2.setDestination(false);
        node2.setIntermediate(false);
        node2.setSource(false);
        node2.setOutdated(false);
        node2.setCountryId(testCountryId);
        node2.setExternalMappingId("EXT2");

        // When: Insert
        Integer id1 = routeNodeRepository.insert(node1);
        Integer id2 = routeNodeRepository.insert(node2);

        // Then: Boolean values should be stored and retrieved correctly
        Optional<RouteNode> retrieved1 = routeNodeRepository.getById(id1);
        assertTrue(retrieved1.isPresent());
        assertTrue(retrieved1.get().getDestination());
        assertTrue(retrieved1.get().getIntermediate());
        assertTrue(retrieved1.get().getSource());
        assertTrue(retrieved1.get().getOutdated());

        Optional<RouteNode> retrieved2 = routeNodeRepository.getById(id2);
        assertTrue(retrieved2.isPresent());
        assertFalse(retrieved2.get().getDestination());
        assertFalse(retrieved2.get().getIntermediate());
        assertFalse(retrieved2.get().getSource());
        assertFalse(retrieved2.get().getOutdated());
    }

    @Test
    void testGeoCoordinates() {
        // Given: Node with specific coordinates
        RouteNode node = new RouteNode();
        node.setName("Geo Node");
        node.setAddress("Geo Address");
        node.setGeoLat(new BigDecimal("52.5200"));
        node.setGeoLng(new BigDecimal("13.4050"));
        node.setDestination(true);
        node.setIntermediate(false);
        node.setSource(false);
        node.setCountryId(testCountryId);
        node.setExternalMappingId("GEO1");

        // When: Insert
        Integer id = routeNodeRepository.insert(node);

        // Then: Coordinates should be stored correctly
        Optional<RouteNode> retrieved = routeNodeRepository.getById(id);
        assertTrue(retrieved.isPresent());
        assertEquals(0, new BigDecimal("52.5200").compareTo(retrieved.get().getGeoLat()));
        assertEquals(0, new BigDecimal("13.4050").compareTo(retrieved.get().getGeoLng()));
    }

    // ========== 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) {
        String sql = "INSERT INTO premise_destination (premise_id, destination_node_id, annual_amount, country_id, geo_lat, geo_lng) " +
            "VALUES (?, ?, 1000, ?, 51.5, 7.5)";
        executeRawSql(sql, premiseId, nodeId, testCountryId);

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

    private Integer createRoute(Integer destinationId) {
        String sql = String.format(
            "INSERT INTO premise_route (premise_destination_id, is_cheapest, is_fastest, is_selected) VALUES (?, %s, %s, %s)",
            dialectProvider.getBooleanTrue(),
            dialectProvider.getBooleanTrue(),
            dialectProvider.getBooleanTrue());
        executeRawSql(sql, destinationId);

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

    private Integer createRouteNode(String name, Integer nodeId, Integer countryId, boolean isDestination, boolean isIntermediate, boolean isSource) {
        String sql = String.format(
            "INSERT INTO premise_route_node (name, address, geo_lat, geo_lng, is_destination, is_intermediate, is_source, " +
            "node_id, country_id, is_outdated, external_mapping_id) " +
            "VALUES (?, 'Address', 51.5, 7.5, %s, %s, %s, ?, ?, %s, 'EXT')",
            isDestination ? dialectProvider.getBooleanTrue() : dialectProvider.getBooleanFalse(),
            isIntermediate ? dialectProvider.getBooleanTrue() : dialectProvider.getBooleanFalse(),
            isSource ? dialectProvider.getBooleanTrue() : dialectProvider.getBooleanFalse(),
            dialectProvider.getBooleanFalse());
        executeRawSql(sql, name, nodeId, countryId);

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

    private Integer createRouteSection(Integer routeId, Integer fromNodeId, Integer toNodeId) {
        String sql = String.format(
            "INSERT INTO premise_route_section (premise_route_id, from_route_node_id, to_route_node_id, list_position, " +
            "transport_type, rate_type, is_pre_run, is_main_run, is_post_run, is_outdated) " +
            "VALUES (?, ?, ?, 1, 'SEA', 'CONTAINER', %s, %s, %s, %s)",
            dialectProvider.getBooleanFalse(),
            dialectProvider.getBooleanTrue(),
            dialectProvider.getBooleanFalse(),
            dialectProvider.getBooleanFalse());
        executeRawSql(sql, routeId, fromNodeId, toNodeId);

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