package de.avatic.lcc.repositories.premise;

import de.avatic.lcc.model.db.premises.PremiseState;
import de.avatic.lcc.model.db.premises.route.Route;
import de.avatic.lcc.repositories.AbstractRepositoryIntegrationTest;
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.util.List;
import java.util.Optional;

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

/**
 * Integration tests for RouteRepository.
 * <p>
 * Tests critical functionality across both MySQL and MSSQL:
 * - Boolean literals (TRUE/FALSE vs 1/0)
 * - Dynamic IN clauses
 * - Auto-generated keys
 * - CRUD operations
 * <p>
 * Run with:
 * <pre>
 * mvn test -Dspring.profiles.active=test,mysql -Dtest=RouteRepositoryIntegrationTest
 * mvn test -Dspring.profiles.active=test,mssql -Dtest=RouteRepositoryIntegrationTest
 * </pre>
 */
class RouteRepositoryIntegrationTest extends AbstractRepositoryIntegrationTest {

    @Autowired
    private RouteRepository routeRepository;

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

    @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 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 test premise
        testPremiseId = createPremise(testUserId, testNodeId, testMaterialId, testCountryId, PremiseState.DRAFT);

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

        // Create some test routes
        createRoute(testDestinationId, true, true, true);   // cheapest, fastest, selected
        createRoute(testDestinationId, false, false, false); // not cheapest, not fastest, not selected
        createRoute(testDestinationId, false, false, false); // not cheapest, not fastest, not selected
    }

    @Test
    void testGetByDestinationId() {
        // When: Get routes by destination
        List<Route> routes = routeRepository.getByDestinationId(testDestinationId);

        // Then: Should return all routes for destination
        assertNotNull(routes);
        assertEquals(3, routes.size());
        assertTrue(routes.stream().allMatch(r -> r.getDestinationId().equals(testDestinationId)));
    }

    @Test
    void testGetByDestinationIdEmpty() {
        // When: Get routes for non-existent destination
        List<Route> routes = routeRepository.getByDestinationId(99999);

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

    @Test
    void testGetSelectedByDestinationId() {
        // When: Get selected route
        Optional<Route> selected = routeRepository.getSelectedByDestinationId(testDestinationId);

        // Then: Should return the selected route
        assertTrue(selected.isPresent());
        assertEquals(testDestinationId, selected.get().getDestinationId());
        assertTrue(selected.get().getSelected());
        assertTrue(selected.get().getCheapest());
        assertTrue(selected.get().getFastest());
    }

    @Test
    void testGetSelectedByDestinationIdNotFound() {
        // Given: Destination with no selected routes
        Integer destinationId2 = createDestination(testPremiseId, testNodeId);
        createRoute(destinationId2, false, false, false);

        // When: Get selected route
        Optional<Route> selected = routeRepository.getSelectedByDestinationId(destinationId2);

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

    @Test
    void testGetSelectedByDestinationIdMultipleThrows() {
        // Given: Destination with multiple selected routes (invalid state)
        Integer destinationId2 = createDestination(testPremiseId, testNodeId);
        createRoute(destinationId2, false, false, true);
        createRoute(destinationId2, false, false, true);

        // When/Then: Should throw DatabaseException
        assertThrows(DatabaseException.class, () ->
            routeRepository.getSelectedByDestinationId(destinationId2));
    }

    @Test
    void testInsert() {
        // Given: New route
        Route newRoute = new Route();
        newRoute.setDestinationId(testDestinationId);
        newRoute.setCheapest(false);
        newRoute.setFastest(true);
        newRoute.setSelected(false);

        // When: Insert
        Integer id = routeRepository.insert(newRoute);

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

        // Verify insertion
        List<Route> routes = routeRepository.getByDestinationId(testDestinationId);
        assertTrue(routes.stream().anyMatch(r -> r.getId().equals(id) && r.getFastest()));
    }

    @Test
    void testDeleteAllById() {
        // Given: Multiple routes
        List<Route> routes = routeRepository.getByDestinationId(testDestinationId);
        assertEquals(3, routes.size());

        // Get first two route IDs
        List<Integer> idsToDelete = routes.stream()
            .limit(2)
            .map(Route::getId)
            .toList();

        // When: Delete by IDs
        routeRepository.deleteAllById(idsToDelete);

        // Then: Should delete specified routes
        List<Route> remaining = routeRepository.getByDestinationId(testDestinationId);
        assertEquals(1, remaining.size());
        assertFalse(idsToDelete.contains(remaining.getFirst().getId()));
    }

    @Test
    void testDeleteAllByIdEmpty() {
        // When: Delete with empty list
        routeRepository.deleteAllById(List.of());

        // Then: Should not throw error, routes remain
        List<Route> routes = routeRepository.getByDestinationId(testDestinationId);
        assertEquals(3, routes.size());
    }

    @Test
    void testDeleteAllByIdNull() {
        // When: Delete with null
        routeRepository.deleteAllById(null);

        // Then: Should not throw error, routes remain
        List<Route> routes = routeRepository.getByDestinationId(testDestinationId);
        assertEquals(3, routes.size());
    }

    @Test
    void testUpdateSelectedByDestinationId() {
        // Given: Get non-selected route
        List<Route> routes = routeRepository.getByDestinationId(testDestinationId);
        Route nonSelectedRoute = routes.stream()
            .filter(r -> !r.getSelected())
            .findFirst()
            .orElseThrow();

        // When: Update selected route
        routeRepository.updateSelectedByDestinationId(testDestinationId, nonSelectedRoute.getId());

        // Then: New route should be selected, old route should be deselected
        Optional<Route> newSelected = routeRepository.getSelectedByDestinationId(testDestinationId);
        assertTrue(newSelected.isPresent());
        assertEquals(nonSelectedRoute.getId(), newSelected.get().getId());
        assertTrue(newSelected.get().getSelected());

        // Verify only one route is selected
        List<Route> allRoutes = routeRepository.getByDestinationId(testDestinationId);
        long selectedCount = allRoutes.stream().filter(Route::getSelected).count();
        assertEquals(1, selectedCount, "Only one route should be selected");
    }

    @Test
    void testUpdateSelectedByDestinationIdInvalidRoute() {
        // When/Then: Update with non-existent route ID should throw
        assertThrows(DatabaseException.class, () ->
            routeRepository.updateSelectedByDestinationId(testDestinationId, 99999));
    }

    @Test
    void testBooleanLiterals() {
        // Given: Create routes with different boolean values
        Route route1 = new Route();
        route1.setDestinationId(testDestinationId);
        route1.setCheapest(true);
        route1.setFastest(false);
        route1.setSelected(true);

        Route route2 = new Route();
        route2.setDestinationId(testDestinationId);
        route2.setCheapest(false);
        route2.setFastest(true);
        route2.setSelected(false);

        // When: Insert
        Integer id1 = routeRepository.insert(route1);
        Integer id2 = routeRepository.insert(route2);

        // Then: Boolean values should be stored and retrieved correctly
        List<Route> routes = routeRepository.getByDestinationId(testDestinationId);

        Route retrieved1 = routes.stream().filter(r -> r.getId().equals(id1)).findFirst().orElseThrow();
        assertTrue(retrieved1.getCheapest());
        assertFalse(retrieved1.getFastest());

        Route retrieved2 = routes.stream().filter(r -> r.getId().equals(id2)).findFirst().orElseThrow();
        assertFalse(retrieved2.getCheapest());
        assertTrue(retrieved2.getFastest());
    }

    // ========== 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, boolean isCheapest, boolean isFastest, boolean isSelected) {
        String sql = String.format(
            "INSERT INTO premise_route (premise_destination_id, is_cheapest, is_fastest, is_selected) VALUES (?, %s, %s, %s)",
            isCheapest ? dialectProvider.getBooleanTrue() : dialectProvider.getBooleanFalse(),
            isFastest ? dialectProvider.getBooleanTrue() : dialectProvider.getBooleanFalse(),
            isSelected ? dialectProvider.getBooleanTrue() : dialectProvider.getBooleanFalse());
        executeRawSql(sql, destinationId);

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