package de.avatic.lcc.repositories.calculation;

import de.avatic.lcc.dto.generic.ContainerType;
import de.avatic.lcc.dto.generic.RateType;
import de.avatic.lcc.dto.generic.TransportType;
import de.avatic.lcc.model.db.calculations.CalculationJobPriority;
import de.avatic.lcc.model.db.calculations.CalculationJobRouteSection;
import de.avatic.lcc.model.db.calculations.CalculationJobState;
import de.avatic.lcc.model.db.premises.PremiseState;
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.Map;

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

public class CalculationJobRouteSectionRepositoryIntegrationTest extends AbstractRepositoryIntegrationTest {

    @Autowired
    private CalculationJobRouteSectionRepository repository;

    private Integer testUserId;
    private Integer testCountryId;
    private Integer testNodeId;
    private Integer testMaterialId;
    private Integer testValidityPeriodId;
    private Integer testPropertySetId;
    private Integer testPremiseId;
    private Integer testCalculationJobDestinationId1;
    private Integer testCalculationJobDestinationId2;
    private Integer testPremiseRouteSectionId;

    @BeforeEach
    void setupTestData() {
        // Clean up calculation job dependent tables
        jdbcTemplate.update("DELETE FROM calculation_job_route_section");
        jdbcTemplate.update("DELETE FROM calculation_job_destination");
        jdbcTemplate.update("DELETE FROM calculation_job");

        // Clean up premise dependent tables
        jdbcTemplate.update("DELETE FROM premise_route_section");
        jdbcTemplate.update("DELETE FROM premise_route");
        jdbcTemplate.update("DELETE FROM premise_route_node");
        jdbcTemplate.update("DELETE FROM premise_destination");
        jdbcTemplate.update("DELETE FROM premise");
        jdbcTemplate.update("DELETE FROM packaging");
        jdbcTemplate.update("DELETE FROM material");

        // Clean up node-referencing tables
        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");

        // Clean up validity_period referencing tables
        jdbcTemplate.update("DELETE FROM country_property");
        jdbcTemplate.update("DELETE FROM validity_period");

        // Clean up property_set referencing tables
        jdbcTemplate.update("DELETE FROM system_property");
        jdbcTemplate.update("DELETE FROM property_set");

        // 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 validity period
        testValidityPeriodId = createValidityPeriod("VALID");

        // Create test property set
        testPropertySetId = createPropertySet("VALID");

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

        // Create premise destination
        Integer destinationId = createDestination(testPremiseId, testNodeId);

        // Create test calculation job
        Integer calculationJobId = createCalculationJob(testPremiseId, testValidityPeriodId, testPropertySetId, testUserId);

        // Create test calculation job destinations (minimal)
        testCalculationJobDestinationId1 = createMinimalCalculationJobDestination(calculationJobId, testPremiseId, destinationId);
        testCalculationJobDestinationId2 = createMinimalCalculationJobDestination(calculationJobId, testPremiseId, destinationId);

        // Create test premise route section
        Integer routeId = createRoute(destinationId);
        Integer fromNodeId = createRouteNode(testNodeId, testCountryId);
        Integer toNodeId = createRouteNode(testNodeId, testCountryId);
        testPremiseRouteSectionId = createRouteSection(routeId, fromNodeId, toNodeId);
    }

    // ========== INSERT TESTS ==========

    @Test
    void testInsertWithAllFields() {
        CalculationJobRouteSection section = createFullRouteSection();
        section.setCalculationJobDestinationId(testCalculationJobDestinationId1);
        section.setPremiseRouteSectionId(testPremiseRouteSectionId);

        Integer id = repository.insert(section);

        assertNotNull(id);
        assertTrue(id > 0);

        List<CalculationJobRouteSection> sections = repository.getRouteSectionsByDestinationId(testCalculationJobDestinationId1);
        assertEquals(1, sections.size());

        CalculationJobRouteSection retrieved = sections.get(0);
        assertEquals(id, retrieved.getId());
        assertEquals(testPremiseRouteSectionId, retrieved.getPremiseRouteSectionId());
        assertEquals(testCalculationJobDestinationId1, retrieved.getCalculationJobDestinationId());
        assertEquals(TransportType.SEA, retrieved.getTransportType());
        assertEquals(RateType.CONTAINER, retrieved.getRateType());
        assertTrue(retrieved.getUnmixedPrice());
        assertTrue(retrieved.isCbmPrice());
        assertFalse(retrieved.isWeightPrice());
        assertTrue(retrieved.getStacked());
        assertFalse(retrieved.getPreRun());
        assertTrue(retrieved.getMainRun());
        assertFalse(retrieved.getPostRun());
        assertEquals(0, new BigDecimal("500.00").compareTo(retrieved.getRate()));
        assertEquals(0, new BigDecimal("1500.50").compareTo(retrieved.getDistance()));
        assertEquals(0, new BigDecimal("100.00").compareTo(retrieved.getCbmPrice()));
        assertEquals(0, new BigDecimal("80.00").compareTo(retrieved.getWeightPrice()));
        assertEquals(0, new BigDecimal("25000.00").compareTo(retrieved.getAnnualCost()));
        assertEquals(15, retrieved.getTransitTime());
    }

    @Test
    void testInsertWithNullPremiseRouteSectionId() {
        CalculationJobRouteSection section = createFullRouteSection();
        section.setCalculationJobDestinationId(testCalculationJobDestinationId1);
        section.setPremiseRouteSectionId(null);  // Nullable field

        Integer id = repository.insert(section);

        assertNotNull(id);

        List<CalculationJobRouteSection> sections = repository.getRouteSectionsByDestinationId(testCalculationJobDestinationId1);
        assertEquals(1, sections.size());
        // Note: ResultSet.getInt() returns 0 for NULL, so we can't distinguish null from 0
        assertEquals(0, sections.get(0).getPremiseRouteSectionId());
    }

    @Test
    void testInsertWithMatrixRateType() {
        CalculationJobRouteSection section = createMinimalRouteSection();
        section.setCalculationJobDestinationId(testCalculationJobDestinationId1);
        section.setTransportType(TransportType.ROAD);
        section.setRateType(RateType.MATRIX);

        Integer id = repository.insert(section);
        assertNotNull(id);

        List<CalculationJobRouteSection> sections = repository.getRouteSectionsByDestinationId(testCalculationJobDestinationId1);
        assertEquals(1, sections.size());

        // MATRIX rate type is stored as "MATRIX" in transport_type column and converted back
        assertEquals(TransportType.ROAD, sections.get(0).getTransportType());
        assertEquals(RateType.MATRIX, sections.get(0).getRateType());
    }

    @Test
    void testInsertWithD2DRateType() {
        CalculationJobRouteSection section = createMinimalRouteSection();
        section.setCalculationJobDestinationId(testCalculationJobDestinationId1);
        section.setTransportType(TransportType.ROAD);
        section.setRateType(RateType.D2D);

        Integer id = repository.insert(section);
        assertNotNull(id);

        List<CalculationJobRouteSection> sections = repository.getRouteSectionsByDestinationId(testCalculationJobDestinationId1);
        assertEquals(1, sections.size());

        // D2D rate type is stored as "D2D" in transport_type column and converted back
        assertEquals(TransportType.ROAD, sections.get(0).getTransportType());
        assertEquals(RateType.D2D, sections.get(0).getRateType());
    }

    @Test
    void testInsertWithContainerRateType() {
        CalculationJobRouteSection section = createMinimalRouteSection();
        section.setCalculationJobDestinationId(testCalculationJobDestinationId1);
        section.setTransportType(TransportType.RAIL);
        section.setRateType(RateType.CONTAINER);

        Integer id = repository.insert(section);
        assertNotNull(id);

        List<CalculationJobRouteSection> sections = repository.getRouteSectionsByDestinationId(testCalculationJobDestinationId1);
        assertEquals(1, sections.size());

        // CONTAINER rate type stores the transport type directly
        assertEquals(TransportType.RAIL, sections.get(0).getTransportType());
        assertEquals(RateType.CONTAINER, sections.get(0).getRateType());
    }

    @Test
    void testBooleanFlags() {
        CalculationJobRouteSection section = createMinimalRouteSection();
        section.setCalculationJobDestinationId(testCalculationJobDestinationId1);
        section.setUnmixedPrice(true);
        section.setCbmPrice(true);
        section.setWeightPrice(false);
        section.setStacked(false);
        section.setPreRun(true);
        section.setMainRun(false);
        section.setPostRun(true);

        Integer id = repository.insert(section);
        assertNotNull(id);

        List<CalculationJobRouteSection> sections = repository.getRouteSectionsByDestinationId(testCalculationJobDestinationId1);
        assertEquals(1, sections.size());

        CalculationJobRouteSection retrieved = sections.get(0);
        assertTrue(retrieved.getUnmixedPrice());
        assertTrue(retrieved.isCbmPrice());
        assertFalse(retrieved.isWeightPrice());
        assertFalse(retrieved.getStacked());
        assertTrue(retrieved.getPreRun());
        assertFalse(retrieved.getMainRun());
        assertTrue(retrieved.getPostRun());
    }

    // ========== QUERY TESTS ==========

    @Test
    void testGetRouteSectionsByDestinationId() {
        CalculationJobRouteSection section1 = createFullRouteSection();
        section1.setCalculationJobDestinationId(testCalculationJobDestinationId1);
        repository.insert(section1);

        CalculationJobRouteSection section2 = createFullRouteSection();
        section2.setCalculationJobDestinationId(testCalculationJobDestinationId1);
        section2.setTransportType(TransportType.RAIL);
        repository.insert(section2);

        CalculationJobRouteSection section3 = createFullRouteSection();
        section3.setCalculationJobDestinationId(testCalculationJobDestinationId2);
        repository.insert(section3);

        List<CalculationJobRouteSection> sections = repository.getRouteSectionsByDestinationId(testCalculationJobDestinationId1);

        assertEquals(2, sections.size());
        assertTrue(sections.stream().allMatch(s -> s.getCalculationJobDestinationId().equals(testCalculationJobDestinationId1)));
    }

    @Test
    void testGetRouteSectionsByDestinationIdNotFound() {
        List<CalculationJobRouteSection> sections = repository.getRouteSectionsByDestinationId(99999);
        assertTrue(sections.isEmpty());
    }

    @Test
    void testGetRouteSectionsByDestinationIds() {
        CalculationJobRouteSection section1 = createFullRouteSection();
        section1.setCalculationJobDestinationId(testCalculationJobDestinationId1);
        repository.insert(section1);

        CalculationJobRouteSection section2 = createFullRouteSection();
        section2.setCalculationJobDestinationId(testCalculationJobDestinationId1);
        repository.insert(section2);

        CalculationJobRouteSection section3 = createFullRouteSection();
        section3.setCalculationJobDestinationId(testCalculationJobDestinationId2);
        repository.insert(section3);

        Map<Integer, List<CalculationJobRouteSection>> grouped = repository.getRouteSectionsByDestinationIds(
            List.of(testCalculationJobDestinationId1, testCalculationJobDestinationId2)
        );

        assertEquals(2, grouped.size());
        assertTrue(grouped.containsKey(testCalculationJobDestinationId1));
        assertTrue(grouped.containsKey(testCalculationJobDestinationId2));
        assertEquals(2, grouped.get(testCalculationJobDestinationId1).size());
        assertEquals(1, grouped.get(testCalculationJobDestinationId2).size());
    }

    @Test
    void testGetRouteSectionsByDestinationIdsEmpty() {
        Map<Integer, List<CalculationJobRouteSection>> grouped = repository.getRouteSectionsByDestinationIds(List.of());
        assertTrue(grouped.isEmpty());
    }

    @Test
    void testGetRouteSectionsByDestinationIdsNull() {
        Map<Integer, List<CalculationJobRouteSection>> grouped = repository.getRouteSectionsByDestinationIds(null);
        assertTrue(grouped.isEmpty());
    }

    @Test
    void testGetRouteSectionsByDestinationIdsNotFound() {
        Map<Integer, List<CalculationJobRouteSection>> grouped = repository.getRouteSectionsByDestinationIds(
            List.of(99998, 99999)
        );
        assertTrue(grouped.isEmpty());
    }

    // ========== HELPER METHODS ==========

    private CalculationJobRouteSection createFullRouteSection() {
        CalculationJobRouteSection section = new CalculationJobRouteSection();
        section.setTransportType(TransportType.SEA);
        section.setRateType(RateType.CONTAINER);
        section.setUnmixedPrice(true);
        section.setCbmPrice(true);
        section.setWeightPrice(false);
        section.setStacked(true);
        section.setPreRun(false);
        section.setMainRun(true);
        section.setPostRun(false);
        section.setRate(new BigDecimal("500.00"));
        section.setDistance(new BigDecimal("1500.50"));
        section.setCbmPrice(new BigDecimal("100.00"));
        section.setWeightPrice(new BigDecimal("80.00"));
        section.setAnnualCost(new BigDecimal("25000.00"));
        section.setTransitTime(15);
        return section;
    }

    private CalculationJobRouteSection createMinimalRouteSection() {
        CalculationJobRouteSection section = new CalculationJobRouteSection();
        section.setTransportType(TransportType.ROAD);
        section.setRateType(RateType.MATRIX);
        section.setUnmixedPrice(false);
        section.setCbmPrice(false);
        section.setWeightPrice(false);
        section.setStacked(true); // Must satisfy constraint: is_unmixed_price IS TRUE OR is_stacked IS TRUE
        section.setPreRun(false);
        section.setMainRun(false);
        section.setPostRun(false);
        section.setRate(new BigDecimal("0.00"));
        section.setDistance(new BigDecimal("0.00"));
        section.setCbmPrice(new BigDecimal("0.00"));
        section.setWeightPrice(new BigDecimal("0.00"));
        section.setAnnualCost(new BigDecimal("0.00"));
        section.setTransitTime(0);
        return section;
    }

    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 createValidityPeriod(String state) {
        String sql = String.format(
            "INSERT INTO validity_period (state, start_date) VALUES (?, %s)",
            isMysql() ? "NOW()" : "GETDATE()");
        executeRawSql(sql, state);

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

    private Integer createPropertySet(String state) {
        String sql = String.format(
            "INSERT INTO property_set (state, start_date) VALUES (?, %s)",
            isMysql() ? "NOW()" : "GETDATE()");
        executeRawSql(sql, state);

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

    private Integer createPremise(Integer userId, Integer supplierNodeId, 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, supplierNodeId, materialId, countryId, state.name());

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

    private Integer createCalculationJob(Integer premiseId, Integer validityPeriodId, Integer propertySetId, Integer userId) {
        String sql = "INSERT INTO calculation_job (premise_id, validity_period_id, property_set_id, user_id, job_state, priority, retries) " +
            "VALUES (?, ?, ?, ?, ?, ?, 0)";
        executeRawSql(sql, premiseId, validityPeriodId, propertySetId, userId,
            CalculationJobState.VALID.name(), CalculationJobPriority.MEDIUM.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 createMinimalCalculationJobDestination(Integer calculationJobId, Integer premiseId, Integer premiseDestinationId) {
        // Simplified destination for testing route sections only
        String sql = String.format(
            "INSERT INTO calculation_job_destination (" +
            "calculation_job_id, premise_destination_id, container_type, hu_count, layer_count, " +
            "transport_weight_exceeded, is_d2d, is_small_unit, shipping_frequency, total_cost, " +
            "annual_amount, material_cost, fca_cost, annual_risk_cost, annual_chance_cost, " +
            "annual_repacking_cost, annual_handling_cost, annual_disposal_cost, " +
            "operational_stock, safety_stock, stocked_inventory, in_transport_stock, stock_before_payment, " +
            "annual_capital_cost, annual_storage_cost, custom_value, custom_duties, tariff_rate, annual_custom_cost, " +
            "air_freight_share_max, air_freight_share, air_freight_volumetric_weight, air_freight_weight, annual_air_freight_cost, " +
            "annual_transportation_cost, container_utilization, transit_time_in_days, safety_stock_in_days, rate_d2d" +
            ") VALUES (" +
            "?, ?, ?, 1, 1, " +
            "%s, %s, %s, 1, 1000, " +
            "1000, 50.0, 55.0, 0, 0, " +
            "0, 0, 0, " +
            "0, 0, 0, 0, 0, " +
            "0, 0, 0, 0, 0, 0, " +
            "0, 0, 0, 0, 0, " +
            "0, 0, 15, 5, 0)",
            dialectProvider.getBooleanFalse(),
            dialectProvider.getBooleanFalse(),
            dialectProvider.getBooleanFalse()
        );
        executeRawSql(sql, calculationJobId, premiseDestinationId, ContainerType.FEU.name());

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

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

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

    private Integer createRouteNode(Integer nodeId, Integer countryId) {
        String sql = String.format(
            "INSERT INTO premise_route_node (node_id, name, external_mapping_id, country_id, is_destination, is_intermediate, is_source, is_outdated) " +
            "VALUES (?, 'Route Node', 'RNODE-001', ?, %s, %s, %s, %s)",
            dialectProvider.getBooleanFalse(),
            dialectProvider.getBooleanFalse(),
            dialectProvider.getBooleanFalse(),
            dialectProvider.getBooleanFalse()
        );
        executeRawSql(sql, 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, ?, ?, %s, %s, %s, %s)",
            dialectProvider.getBooleanFalse(),
            dialectProvider.getBooleanTrue(),
            dialectProvider.getBooleanFalse(),
            dialectProvider.getBooleanFalse()
        );
        executeRawSql(sql, routeId, fromNodeId, toNodeId, TransportType.SEA.name(), RateType.CONTAINER.name());

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