package de.avatic.lcc.repositories.premise;

import de.avatic.lcc.dto.generic.RateType;
import de.avatic.lcc.dto.generic.TransportType;
import de.avatic.lcc.model.db.premises.PremiseState;
import de.avatic.lcc.model.db.premises.route.RouteSection;
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.util.List;
import java.util.Optional;

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

/**
 * Integration tests for RouteSectionRepository.
 * <p>
 * Tests critical functionality across both MySQL and MSSQL:
 * - Boolean literals (TRUE/FALSE vs 1/0)
 * - Enum handling (transport_type, rate_type)
 * - Dynamic IN clauses
 * - NULL handling for optional fields
 * - BigDecimal to Double conversion
 * <p>
 * Run with:
 * <pre>
 * mvn test -Dspring.profiles.active=test,mysql -Dtest=RouteSectionRepositoryIntegrationTest
 * mvn test -Dspring.profiles.active=test,mssql -Dtest=RouteSectionRepositoryIntegrationTest
 * </pre>
 */
class RouteSectionRepositoryIntegrationTest extends AbstractRepositoryIntegrationTest {

    @Autowired
    private RouteSectionRepository routeSectionRepository;

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

    @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 test route nodes
        testFromNodeId = createRouteNode("From Node", testNodeId, testCountryId);
        testToNodeId = createRouteNode("To Node", testNodeId, testCountryId);

        // Create some test sections (respecting constraint: is_main_run must be TRUE unless transport_type is ROAD/POST_RUN)
        createRouteSection(testRouteId, testFromNodeId, testToNodeId, 1, TransportType.SEA, RateType.CONTAINER, true, true, false);
        createRouteSection(testRouteId, testFromNodeId, testToNodeId, 2, TransportType.ROAD, RateType.MATRIX, false, false, false);  // ROAD allows is_main_run=FALSE
        createRouteSection(testRouteId, testFromNodeId, testToNodeId, 3, TransportType.RAIL, RateType.NEAR_BY, false, true, true);
    }

    @Test
    void testGetByRouteId() {
        // When: Get sections by route ID
        List<RouteSection> sections = routeSectionRepository.getByRouteId(testRouteId);

        // Then: Should return all sections for route
        assertNotNull(sections);
        assertEquals(3, sections.size());
        assertTrue(sections.stream().allMatch(s -> s.getRouteId().equals(testRouteId)));
    }

    @Test
    void testGetByRouteIdEmpty() {
        // When: Get sections for non-existent route
        List<RouteSection> sections = routeSectionRepository.getByRouteId(99999);

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

    @Test
    void testGetById() {
        // Given: Create route section
        Integer sectionId = createRouteSection(testRouteId, testFromNodeId, testToNodeId, 10,
            TransportType.SEA, RateType.CONTAINER, true, true, false);

        // When: Get by ID
        Optional<RouteSection> section = routeSectionRepository.getById(sectionId);

        // Then: Should retrieve
        assertTrue(section.isPresent());
        assertEquals(sectionId, section.get().getId());
        assertEquals(testRouteId, section.get().getRouteId());
        assertEquals(10, section.get().getListPosition());
        assertEquals(TransportType.SEA, section.get().getTransportType());
        assertEquals(RateType.CONTAINER, section.get().getRateType());
        assertTrue(section.get().getPreRun());
        assertTrue(section.get().getMainRun());
        assertFalse(section.get().getPostRun());
    }

    @Test
    void testGetByIdNotFound() {
        // When: Get non-existent ID
        Optional<RouteSection> section = routeSectionRepository.getById(99999);

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

    @Test
    void testInsert() {
        // Given: New route section
        RouteSection newSection = new RouteSection();
        newSection.setRouteId(testRouteId);
        newSection.setFromRouteNodeId(testFromNodeId);
        newSection.setToRouteNodeId(testToNodeId);
        newSection.setListPosition(99);
        newSection.setTransportType(TransportType.POST_RUN);
        newSection.setRateType(RateType.MATRIX);
        newSection.setPreRun(false);
        newSection.setMainRun(true);
        newSection.setPostRun(true);
        newSection.setOutdated(false);
        newSection.setDistance(250.5);

        // When: Insert
        Integer id = routeSectionRepository.insert(newSection);

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

        Optional<RouteSection> inserted = routeSectionRepository.getById(id);
        assertTrue(inserted.isPresent());
        assertEquals(99, inserted.get().getListPosition());
        assertEquals(TransportType.POST_RUN, inserted.get().getTransportType());
        assertEquals(RateType.MATRIX, inserted.get().getRateType());
        assertFalse(inserted.get().getPreRun());
        assertTrue(inserted.get().getMainRun());
        assertTrue(inserted.get().getPostRun());
        assertNotNull(inserted.get().getDistance());
        assertEquals(250.5, inserted.get().getDistance(), 0.01);
    }

    @Test
    void testInsertWithNullDistance() {
        // Given: Route section with null distance
        RouteSection newSection = new RouteSection();
        newSection.setRouteId(testRouteId);
        newSection.setFromRouteNodeId(testFromNodeId);
        newSection.setToRouteNodeId(testToNodeId);
        newSection.setListPosition(50);
        newSection.setTransportType(TransportType.SEA);
        newSection.setRateType(RateType.CONTAINER);
        newSection.setPreRun(true);
        newSection.setMainRun(true);  // Must be TRUE for SEA (constraint)
        newSection.setPostRun(false);
        newSection.setOutdated(false);
        newSection.setDistance(null);  // nullable

        // When: Insert
        Integer id = routeSectionRepository.insert(newSection);

        // Then: Should be inserted with null distance
        assertNotNull(id);

        Optional<RouteSection> inserted = routeSectionRepository.getById(id);
        assertTrue(inserted.isPresent());
        assertNull(inserted.get().getDistance());
    }

    @Test
    void testDeleteAllById() {
        // Given: Multiple route sections
        List<RouteSection> sections = routeSectionRepository.getByRouteId(testRouteId);
        assertEquals(3, sections.size());

        // Get first two section IDs
        List<Integer> idsToDelete = sections.stream()
            .limit(2)
            .map(RouteSection::getId)
            .toList();

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

        // Then: Should delete specified sections
        List<RouteSection> remaining = routeSectionRepository.getByRouteId(testRouteId);
        assertEquals(1, remaining.size());
        assertFalse(idsToDelete.contains(remaining.getFirst().getId()));
    }

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

        // Then: Should not throw error, sections remain
        List<RouteSection> sections = routeSectionRepository.getByRouteId(testRouteId);
        assertEquals(3, sections.size());
    }

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

        // Then: Should not throw error, sections remain
        List<RouteSection> sections = routeSectionRepository.getByRouteId(testRouteId);
        assertEquals(3, sections.size());
    }

    @Test
    void testTransportTypeEnum() {
        // Given: Sections with different transport types
        RouteSection section1 = new RouteSection();
        section1.setRouteId(testRouteId);
        section1.setFromRouteNodeId(testFromNodeId);
        section1.setToRouteNodeId(testToNodeId);
        section1.setListPosition(101);
        section1.setTransportType(TransportType.RAIL);
        section1.setRateType(RateType.CONTAINER);
        section1.setPreRun(false);
        section1.setMainRun(true);  // Must be TRUE for RAIL (constraint)
        section1.setPostRun(false);
        section1.setOutdated(false);

        RouteSection section2 = new RouteSection();
        section2.setRouteId(testRouteId);
        section2.setFromRouteNodeId(testFromNodeId);
        section2.setToRouteNodeId(testToNodeId);
        section2.setListPosition(102);
        section2.setTransportType(TransportType.POST_RUN);  // POST_RUN allows is_main_run=FALSE
        section2.setRateType(RateType.NEAR_BY);
        section2.setPreRun(false);
        section2.setMainRun(false);
        section2.setPostRun(true);
        section2.setOutdated(false);

        // When: Insert
        Integer id1 = routeSectionRepository.insert(section1);
        Integer id2 = routeSectionRepository.insert(section2);

        // Then: Enum values should be stored and retrieved correctly
        Optional<RouteSection> retrieved1 = routeSectionRepository.getById(id1);
        assertTrue(retrieved1.isPresent());
        assertEquals(TransportType.RAIL, retrieved1.get().getTransportType());
        assertEquals(RateType.CONTAINER, retrieved1.get().getRateType());

        Optional<RouteSection> retrieved2 = routeSectionRepository.getById(id2);
        assertTrue(retrieved2.isPresent());
        assertEquals(TransportType.POST_RUN, retrieved2.get().getTransportType());
        assertEquals(RateType.NEAR_BY, retrieved2.get().getRateType());
    }

    @Test
    void testBooleanFlags() {
        // Given: Section with different boolean flags (respecting constraint)
        RouteSection section = new RouteSection();
        section.setRouteId(testRouteId);
        section.setFromRouteNodeId(testFromNodeId);
        section.setToRouteNodeId(testToNodeId);
        section.setListPosition(200);
        section.setTransportType(TransportType.ROAD);  // ROAD allows is_main_run=FALSE
        section.setRateType(RateType.CONTAINER);
        section.setPreRun(true);
        section.setMainRun(false);
        section.setPostRun(true);
        section.setOutdated(true);

        // When: Insert
        Integer id = routeSectionRepository.insert(section);

        // Then: Boolean flags should be stored correctly
        Optional<RouteSection> retrieved = routeSectionRepository.getById(id);
        assertTrue(retrieved.isPresent());
        assertTrue(retrieved.get().getPreRun());
        assertFalse(retrieved.get().getMainRun());
        assertTrue(retrieved.get().getPostRun());
        assertTrue(retrieved.get().getOutdated());
    }

    // ========== 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) {
        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')",
            dialectProvider.getBooleanTrue(),
            dialectProvider.getBooleanTrue(),
            dialectProvider.getBooleanTrue(),
            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, int listPosition,
                                       TransportType transportType, RateType rateType,
                                       boolean isPreRun, boolean isMainRun, boolean isPostRun) {
        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 (?, ?, ?, ?, ?, ?, %s, %s, %s, %s)",
            isPreRun ? dialectProvider.getBooleanTrue() : dialectProvider.getBooleanFalse(),
            isMainRun ? dialectProvider.getBooleanTrue() : dialectProvider.getBooleanFalse(),
            isPostRun ? dialectProvider.getBooleanTrue() : dialectProvider.getBooleanFalse(),
            dialectProvider.getBooleanFalse());
        executeRawSql(sql, routeId, fromNodeId, toNodeId, listPosition, transportType.name(), rateType.name());

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