package de.avatic.lcc.repositories.calculation;

import de.avatic.lcc.dto.generic.ContainerType;
import de.avatic.lcc.model.db.calculations.CalculationJobDestination;
import de.avatic.lcc.model.db.calculations.CalculationJobPriority;
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 static org.junit.jupiter.api.Assertions.*;

/**
 * Integration tests for CalculationJobDestinationRepository.
 * <p>
 * Tests critical functionality across both MySQL and MSSQL:
 * - Complex entity with many BigDecimal fields
 * - Enum handling (ContainerType)
 * - Boolean fields
 * - NULL handling for optional fields
 * - Large INSERT statements
 * <p>
 * Run with:
 * <pre>
 * mvn test -Dspring.profiles.active=test,mysql -Dtest=CalculationJobDestinationRepositoryIntegrationTest
 * mvn test -Dspring.profiles.active=test,mssql -Dtest=CalculationJobDestinationRepositoryIntegrationTest
 * </pre>
 */
class CalculationJobDestinationRepositoryIntegrationTest extends AbstractRepositoryIntegrationTest {

    @Autowired
    private CalculationJobDestinationRepository calculationJobDestinationRepository;

    private Integer testUserId;
    private Integer testCountryId;
    private Integer testNodeId;
    private Integer testMaterialId;
    private Integer testPremiseId;
    private Integer testDestinationId;
    private Integer testValidityPeriodId;
    private Integer testPropertySetId;
    private Integer testCalculationJobId;

    @BeforeEach
    void setupTestData() {
        // Clean up in correct order
        jdbcTemplate.update("DELETE FROM calculation_job_route_section");
        jdbcTemplate.update("DELETE FROM calculation_job_destination");
        jdbcTemplate.update("DELETE FROM calculation_job");
        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
        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 test destination
        testDestinationId = createDestination(testPremiseId, testNodeId);

        // Create test calculation job
        testCalculationJobId = createCalculationJob(testPremiseId, testValidityPeriodId, testPropertySetId, testUserId);
    }

    @Test
    void testInsertAndGetByJobId() {
        // Given: New calculation job destination
        CalculationJobDestination destination = createFullCalculationJobDestination();

        // When: Insert
        Integer id = calculationJobDestinationRepository.insert(destination);

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

        // And: Should be retrievable by job ID
        List<CalculationJobDestination> destinations =
            calculationJobDestinationRepository.getDestinationsByJobId(testCalculationJobId);

        assertEquals(1, destinations.size());
        CalculationJobDestination retrieved = destinations.get(0);

        assertEquals(testCalculationJobId, retrieved.getCalculationJobId());
        assertEquals(testDestinationId, retrieved.getPremiseDestinationId());
        assertEquals(ContainerType.FEU, retrieved.getContainerType());
        assertEquals(10, retrieved.getShippingFrequency());
        assertTrue(retrieved.getSmallUnit());
        assertFalse(retrieved.getTransportWeightExceeded());
    }

    @Test
    void testGetDestinationsByJobIdEmpty() {
        // When: Get destinations for job with no destinations
        List<CalculationJobDestination> destinations =
            calculationJobDestinationRepository.getDestinationsByJobId(testCalculationJobId);

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

    @Test
    void testGetDestinationsByJobIdMultiple() {
        // Given: Multiple destinations for same job
        CalculationJobDestination dest1 = createFullCalculationJobDestination();
        dest1.setContainerType(ContainerType.FEU);

        CalculationJobDestination dest2 = createFullCalculationJobDestination();
        dest2.setContainerType(ContainerType.TEU);

        CalculationJobDestination dest3 = createFullCalculationJobDestination();
        dest3.setContainerType(ContainerType.HC);

        // When: Insert all
        calculationJobDestinationRepository.insert(dest1);
        calculationJobDestinationRepository.insert(dest2);
        calculationJobDestinationRepository.insert(dest3);

        // Then: Should retrieve all for job
        List<CalculationJobDestination> destinations =
            calculationJobDestinationRepository.getDestinationsByJobId(testCalculationJobId);

        assertEquals(3, destinations.size());
    }

    @Test
    void testContainerTypeEnum() {
        // Given: Destinations with different container types
        CalculationJobDestination dest1 = createFullCalculationJobDestination();
        dest1.setContainerType(ContainerType.FEU);

        CalculationJobDestination dest2 = createFullCalculationJobDestination();
        dest2.setContainerType(ContainerType.TEU);

        CalculationJobDestination dest3 = createFullCalculationJobDestination();
        dest3.setContainerType(ContainerType.TRUCK);

        // When: Insert
        calculationJobDestinationRepository.insert(dest1);
        calculationJobDestinationRepository.insert(dest2);
        calculationJobDestinationRepository.insert(dest3);

        // Then: Container types should be stored correctly
        List<CalculationJobDestination> destinations =
            calculationJobDestinationRepository.getDestinationsByJobId(testCalculationJobId);

        assertTrue(destinations.stream().anyMatch(d -> d.getContainerType() == ContainerType.FEU));
        assertTrue(destinations.stream().anyMatch(d -> d.getContainerType() == ContainerType.TEU));
        assertTrue(destinations.stream().anyMatch(d -> d.getContainerType() == ContainerType.TRUCK));
    }

    @Test
    void testBooleanFields() {
        // Given: Destination with specific boolean values
        CalculationJobDestination destination = createFullCalculationJobDestination();
        destination.setSmallUnit(true);
        destination.setTransportWeightExceeded(false);
        destination.setD2D(true);

        // When: Insert
        Integer id = calculationJobDestinationRepository.insert(destination);

        // Then: Boolean values should be stored correctly
        List<CalculationJobDestination> destinations =
            calculationJobDestinationRepository.getDestinationsByJobId(testCalculationJobId);

        assertEquals(1, destinations.size());
        CalculationJobDestination retrieved = destinations.get(0);

        assertTrue(retrieved.getSmallUnit());
        assertFalse(retrieved.getTransportWeightExceeded());
    }

    @Test
    void testBigDecimalFields() {
        // Given: Destination with specific decimal values
        CalculationJobDestination destination = createFullCalculationJobDestination();
        destination.setTotalCost(new BigDecimal("12345.67"));
        destination.setAnnualAmount(new BigDecimal("50000.00"));
        destination.setAnnualTransportationCost(new BigDecimal("8500.50"));
        destination.setContainerUtilization(new BigDecimal("0.85"));

        // When: Insert
        calculationJobDestinationRepository.insert(destination);

        // Then: Decimal values should be stored correctly
        List<CalculationJobDestination> destinations =
            calculationJobDestinationRepository.getDestinationsByJobId(testCalculationJobId);

        assertEquals(1, destinations.size());
        CalculationJobDestination retrieved = destinations.get(0);

        assertEquals(0, new BigDecimal("12345.67").compareTo(retrieved.getTotalCost()));
        assertEquals(0, new BigDecimal("50000.00").compareTo(retrieved.getAnnualAmount()));
        assertEquals(0, new BigDecimal("8500.50").compareTo(retrieved.getAnnualTransportationCost()));
        assertEquals(0, new BigDecimal("0.85").compareTo(retrieved.getContainerUtilization()));
    }

    @Test
    void testNullableFields() {
        // Given: Destination with some nullable fields as null
        CalculationJobDestination destination = createMinimalCalculationJobDestination();

        // When: Insert
        Integer id = calculationJobDestinationRepository.insert(destination);

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

        List<CalculationJobDestination> destinations =
            calculationJobDestinationRepository.getDestinationsByJobId(testCalculationJobId);

        assertEquals(1, destinations.size());
    }

    // ========== Helper Methods ==========

    private CalculationJobDestination createFullCalculationJobDestination() {
        CalculationJobDestination destination = new CalculationJobDestination();

        // Core identifiers
        destination.setCalculationJobId(testCalculationJobId);
        destination.setPremiseDestinationId(testDestinationId);
        destination.setShippingFrequency(10);
        destination.setTotalCost(new BigDecimal("10000.00"));
        destination.setAnnualAmount(new BigDecimal("50000.00"));

        // Risk calculations
        destination.setTotalRiskCost(new BigDecimal("500.00"));
        destination.setTotalChanceCost(new BigDecimal("300.00"));

        // Handling costs
        destination.setSmallUnit(true);
        destination.setAnnualRepackingCost(new BigDecimal("200.00"));
        destination.setAnnualHandlingCost(new BigDecimal("150.00"));
        destination.setAnnualDisposalCost(new BigDecimal("100.00"));

        // Inventory management
        destination.setOperationalStock(new BigDecimal("1000.00"));
        destination.setSafetyStock(new BigDecimal("500.00"));
        destination.setStockedInventory(new BigDecimal("1500.00"));
        destination.setInTransportStock(new BigDecimal("300.00"));
        destination.setStockBeforePayment(new BigDecimal("800.00"));
        destination.setAnnualCapitalCost(new BigDecimal("250.00"));
        destination.setAnnualStorageCost(new BigDecimal("400.00"));

        // Customs
        destination.setCustomValue(new BigDecimal("5000.00"));
        destination.setCustomDuties(new BigDecimal("750.00"));
        destination.setTariffRate(new BigDecimal("0.15"));
        destination.setAnnualCustomCost(new BigDecimal("900.00"));

        // Air freight risk
        destination.setAirFreightShareMax(new BigDecimal("0.20"));
        destination.setAirFreightShare(new BigDecimal("0.10"));
        destination.setAirFreightVolumetricWeight(new BigDecimal("150.00"));
        destination.setAirFreightWeight(new BigDecimal("120.00"));
        destination.setAnnualAirFreightCost(new BigDecimal("1200.00"));

        // Transportation
        destination.setContainerType(ContainerType.FEU);
        destination.setHuCount(20);
        destination.setLayerStructure(null);  // JSON column, set to null (TODO in production code)
        destination.setLayerCount(3);
        destination.setTransportWeightExceeded(false);
        destination.setAnnualTransportationCost(new BigDecimal("8000.00"));
        destination.setContainerUtilization(new BigDecimal("0.85"));
        destination.setTotalTransitTime(30);
        destination.setSafetyStockInDays(new BigDecimal("10.00"));

        // Material costs
        destination.setMaterialCost(new BigDecimal("50.00"));
        destination.setFcaCost(new BigDecimal("55.00"));

        destination.setD2D(false);
        destination.setRateD2D(new BigDecimal("0.00"));

        return destination;
    }

    private CalculationJobDestination createMinimalCalculationJobDestination() {
        CalculationJobDestination destination = new CalculationJobDestination();

        // Only required fields
        destination.setCalculationJobId(testCalculationJobId);
        destination.setPremiseDestinationId(testDestinationId);
        destination.setShippingFrequency(5);
        destination.setTotalCost(new BigDecimal("5000.00"));
        destination.setAnnualAmount(new BigDecimal("25000.00"));
        destination.setTotalRiskCost(new BigDecimal("0.00"));
        destination.setTotalChanceCost(new BigDecimal("0.00"));
        destination.setSmallUnit(false);
        destination.setAnnualRepackingCost(new BigDecimal("0.00"));
        destination.setAnnualHandlingCost(new BigDecimal("0.00"));
        destination.setAnnualDisposalCost(new BigDecimal("0.00"));
        destination.setOperationalStock(new BigDecimal("0.00"));
        destination.setSafetyStock(new BigDecimal("0.00"));
        destination.setStockedInventory(new BigDecimal("0.00"));
        destination.setInTransportStock(new BigDecimal("0.00"));
        destination.setStockBeforePayment(new BigDecimal("0.00"));
        destination.setAnnualCapitalCost(new BigDecimal("0.00"));
        destination.setAnnualStorageCost(new BigDecimal("0.00"));
        destination.setCustomValue(new BigDecimal("0.00"));
        destination.setCustomDuties(new BigDecimal("0.00"));
        destination.setTariffRate(new BigDecimal("0.00"));
        destination.setAnnualCustomCost(new BigDecimal("0.00"));
        destination.setAirFreightShareMax(new BigDecimal("0.00"));
        destination.setAirFreightShare(new BigDecimal("0.00"));
        destination.setAirFreightVolumetricWeight(new BigDecimal("0.00"));
        destination.setAirFreightWeight(new BigDecimal("0.00"));
        destination.setAnnualAirFreightCost(new BigDecimal("0.00"));
        destination.setContainerType(ContainerType.FEU);
        destination.setHuCount(10);
        destination.setLayerStructure(null);  // JSON column, set to null
        destination.setLayerCount(2);
        destination.setTransportWeightExceeded(false);
        destination.setAnnualTransportationCost(new BigDecimal("0.00"));
        destination.setContainerUtilization(new BigDecimal("0.50"));
        destination.setTotalTransitTime(15);
        destination.setSafetyStockInDays(new BigDecimal("5.00"));
        destination.setMaterialCost(new BigDecimal("0.00"));
        destination.setFcaCost(new BigDecimal("0.00"));
        destination.setD2D(false);
        destination.setRateD2D(new BigDecimal("0.00"));

        return destination;
    }

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