package de.avatic.lcc.database.dialect;

import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.DisplayName;
import org.junit.jupiter.api.Nested;
import org.junit.jupiter.api.Test;

import java.util.Arrays;
import java.util.List;

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

/**
 * Unit tests for {@link MySQLDialectProvider}.
 */
@DisplayName("MySQLDialectProvider Tests")
class MySQLDialectProviderTest {

    private MySQLDialectProvider provider;

    @BeforeEach
    void setUp() {
        provider = new MySQLDialectProvider();
    }

    @Nested
    @DisplayName("Metadata Tests")
    class MetadataTests {

        @Test
        @DisplayName("Should return correct dialect name")
        void shouldReturnCorrectDialectName() {
            assertEquals("MySQL", provider.getDialectName());
        }

        @Test
        @DisplayName("Should return correct driver class name")
        void shouldReturnCorrectDriverClassName() {
            assertEquals("com.mysql.cj.jdbc.Driver", provider.getDriverClassName());
        }
    }

    @Nested
    @DisplayName("Pagination Tests")
    class PaginationTests {

        @Test
        @DisplayName("Should build correct pagination clause")
        void shouldBuildCorrectPaginationClause() {
            String result = provider.buildPaginationClause(10, 20);
            assertEquals("LIMIT ? OFFSET ?", result);
        }

        @Test
        @DisplayName("Should return pagination parameters in correct order")
        void shouldReturnPaginationParametersInCorrectOrder() {
            Object[] params = provider.getPaginationParameters(10, 20);
            assertArrayEquals(new Object[]{10, 20}, params);
        }
    }

    @Nested
    @DisplayName("Upsert Operation Tests")
    class UpsertOperationTests {

        @Test
        @DisplayName("Should build correct upsert statement")
        void shouldBuildCorrectUpsertStatement() {
            List<String> uniqueCols = Arrays.asList("id", "user_id");
            List<String> insertCols = Arrays.asList("id", "user_id", "name", "value");
            List<String> updateCols = Arrays.asList("name", "value");

            String result = provider.buildUpsertStatement("test_table", uniqueCols, insertCols, updateCols);

            assertTrue(result.contains("INSERT INTO test_table"));
            assertTrue(result.contains("(id, user_id, name, value)"));
            assertTrue(result.contains("VALUES (?, ?, ?, ?)"));
            assertTrue(result.contains("ON DUPLICATE KEY UPDATE"));
            assertTrue(result.contains("name = VALUES(name)"));
            assertTrue(result.contains("value = VALUES(value)"));
        }

        @Test
        @DisplayName("Should build correct insert ignore statement")
        void shouldBuildCorrectInsertIgnoreStatement() {
            List<String> columns = Arrays.asList("user_id", "group_id");
            List<String> uniqueCols = Arrays.asList("user_id", "group_id");

            String result = provider.buildInsertIgnoreStatement("mapping_table", columns, uniqueCols);

            assertEquals("INSERT IGNORE INTO mapping_table (user_id, group_id) VALUES (?, ?)", result);
        }
    }

    @Nested
    @DisplayName("Locking Strategy Tests")
    class LockingStrategyTests {

        @Test
        @DisplayName("Should build SELECT FOR UPDATE SKIP LOCKED")
        void shouldBuildSelectForUpdateSkipLocked() {
            String baseQuery = "SELECT * FROM calculation_job WHERE state = 'CREATED'";
            String result = provider.buildSelectForUpdateSkipLocked(baseQuery);

            assertTrue(result.endsWith("FOR UPDATE SKIP LOCKED"));
            assertTrue(result.startsWith("SELECT * FROM calculation_job"));
        }

        @Test
        @DisplayName("Should build SELECT FOR UPDATE")
        void shouldBuildSelectForUpdate() {
            String baseQuery = "SELECT * FROM calculation_job WHERE id = ?";
            String result = provider.buildSelectForUpdate(baseQuery);

            assertTrue(result.endsWith("FOR UPDATE"));
            assertFalse(result.contains("SKIP LOCKED"));
        }
    }

    @Nested
    @DisplayName("Date/Time Function Tests")
    class DateTimeFunctionTests {

        @Test
        @DisplayName("Should return NOW() for current timestamp")
        void shouldReturnNowForCurrentTimestamp() {
            assertEquals("NOW()", provider.getCurrentTimestamp());
        }

        @Test
        @DisplayName("Should build date subtraction with NOW()")
        void shouldBuildDateSubtractionWithNow() {
            String result = provider.buildDateSubtraction(null, "3", SqlDialectProvider.DateUnit.DAY);
            assertEquals("DATE_SUB(NOW(), INTERVAL 3 DAY)", result);
        }

        @Test
        @DisplayName("Should build date subtraction with custom base date")
        void shouldBuildDateSubtractionWithCustomBaseDate() {
            String result = provider.buildDateSubtraction("calculation_date", "60", SqlDialectProvider.DateUnit.MINUTE);
            assertEquals("DATE_SUB(calculation_date, INTERVAL 60 MINUTE)", result);
        }

        @Test
        @DisplayName("Should build date addition with NOW()")
        void shouldBuildDateAdditionWithNow() {
            String result = provider.buildDateAddition(null, "7", SqlDialectProvider.DateUnit.DAY);
            assertEquals("DATE_ADD(NOW(), INTERVAL 7 DAY)", result);
        }

        @Test
        @DisplayName("Should build date addition with custom base date")
        void shouldBuildDateAdditionWithCustomBaseDate() {
            String result = provider.buildDateAddition("start_date", "1", SqlDialectProvider.DateUnit.MONTH);
            assertEquals("DATE_ADD(start_date, INTERVAL 1 MONTH)", result);
        }

        @Test
        @DisplayName("Should extract date from column")
        void shouldExtractDateFromColumn() {
            String result = provider.extractDate("created_at");
            assertEquals("DATE(created_at)", result);
        }

        @Test
        @DisplayName("Should extract date from expression")
        void shouldExtractDateFromExpression() {
            String result = provider.extractDate("NOW()");
            assertEquals("DATE(NOW())", result);
        }
    }

    @Nested
    @DisplayName("Auto-increment Reset Tests")
    class AutoIncrementResetTests {

        @Test
        @DisplayName("Should build auto-increment reset statement")
        void shouldBuildAutoIncrementResetStatement() {
            String result = provider.buildAutoIncrementReset("test_table");
            assertEquals("ALTER TABLE test_table AUTO_INCREMENT = 1", result);
        }
    }

    @Nested
    @DisplayName("Geospatial Distance Tests")
    class GeospatialDistanceTests {

        @Test
        @DisplayName("Should build Haversine distance calculation in kilometers")
        void shouldBuildHaversineDistanceCalculation() {
            String result = provider.buildHaversineDistance("50.1", "8.6", "node.geo_lat", "node.geo_lng");

            // MySQL now uses 6371 km (not 6371000 m) for consistency with MSSQL
            assertTrue(result.contains("6371"));
            assertFalse(result.contains("6371000")); // Should NOT be in meters
            assertTrue(result.contains("ACOS"));
            assertTrue(result.contains("COS"));
            assertTrue(result.contains("SIN"));
            assertTrue(result.contains("RADIANS"));
            assertTrue(result.contains("50.1"));
            assertTrue(result.contains("8.6"));
            assertTrue(result.contains("node.geo_lat"));
            assertTrue(result.contains("node.geo_lng"));
        }
    }

    @Nested
    @DisplayName("String/Type Function Tests")
    class StringTypeFunctionTests {

        @Test
        @DisplayName("Should build CONCAT with multiple expressions")
        void shouldBuildConcatWithMultipleExpressions() {
            String result = provider.buildConcat("first_name", "' '", "last_name");
            assertEquals("CONCAT(first_name, ' ', last_name)", result);
        }

        @Test
        @DisplayName("Should build CONCAT with single expression")
        void shouldBuildConcatWithSingleExpression() {
            String result = provider.buildConcat("column_name");
            assertEquals("CONCAT(column_name)", result);
        }

        @Test
        @DisplayName("Should cast to string")
        void shouldCastToString() {
            String result = provider.castToString("user_id");
            assertEquals("CAST(user_id AS CHAR)", result);
        }
    }

    @Nested
    @DisplayName("Bulk Operation Tests")
    class BulkOperationTests {

        @Test
        @DisplayName("Should return MySQL BIGINT UNSIGNED max value")
        void shouldReturnMySQLBigIntUnsignedMaxValue() {
            assertEquals("18446744073709551615", provider.getMaxLimitValue());
        }

        @Test
        @DisplayName("Should not support RETURNING clause")
        void shouldNotSupportReturningClause() {
            assertFalse(provider.supportsReturningClause());
        }

        @Test
        @DisplayName("Should throw exception when building RETURNING clause")
        void shouldThrowExceptionWhenBuildingReturningClause() {
            UnsupportedOperationException exception = assertThrows(
                UnsupportedOperationException.class,
                () -> provider.buildReturningClause("id", "name")
            );

            assertTrue(exception.getMessage().contains("MySQL does not support RETURNING"));
            assertTrue(exception.getMessage().contains("LAST_INSERT_ID"));
        }
    }

    @Nested
    @DisplayName("Schema/DDL Tests")
    class SchemaDDLTests {

        @Test
        @DisplayName("Should return AUTO_INCREMENT definition")
        void shouldReturnAutoIncrementDefinition() {
            String result = provider.getAutoIncrementDefinition();
            assertEquals("INT NOT NULL AUTO_INCREMENT", result);
        }

        @Test
        @DisplayName("Should return TIMESTAMP with ON UPDATE definition")
        void shouldReturnTimestampWithOnUpdateDefinition() {
            String result = provider.getTimestampDefinition();
            assertEquals("TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP", result);
        }
    }
}