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 MSSQLDialectProvider}.
 */
@DisplayName("MSSQLDialectProvider Tests")
class MSSQLDialectProviderTest {

    private MSSQLDialectProvider provider;

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

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

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

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

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

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

        @Test
        @DisplayName("Should return pagination parameters in correct order (offset, limit)")
        void shouldReturnPaginationParametersInCorrectOrder() {
            Object[] params = provider.getPaginationParameters(10, 20);
            // MSSQL: offset first, then limit (reversed from MySQL)
            assertArrayEquals(new Object[]{20, 10}, params);
        }
    }

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

        @Test
        @DisplayName("Should build correct MERGE statement")
        void shouldBuildCorrectMergeStatement() {
            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("MERGE INTO test_table AS target"));
            assertTrue(result.contains("USING (SELECT"));
            assertTrue(result.contains("ON target.id = source.id AND target.user_id = source.user_id"));
            assertTrue(result.contains("WHEN MATCHED THEN UPDATE SET"));
            assertTrue(result.contains("WHEN NOT MATCHED THEN INSERT"));
            assertTrue(result.contains("name = source.name"));
            assertTrue(result.contains("value = source.value"));
        }

        @Test
        @DisplayName("Should build correct conditional INSERT 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);

            assertTrue(result.contains("IF NOT EXISTS"));
            assertTrue(result.contains("SELECT 1 FROM mapping_table"));
            assertTrue(result.contains("WHERE user_id = ? AND group_id = ?"));
            assertTrue(result.contains("INSERT INTO mapping_table (user_id, group_id) VALUES (?, ?)"));
        }
    }

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

        @Test
        @DisplayName("Should build WITH (UPDLOCK, READPAST) for SKIP LOCKED equivalent")
        void shouldBuildSelectForUpdateSkipLocked() {
            String baseQuery = "SELECT * FROM calculation_job WHERE state = 'CREATED'";
            String result = provider.buildSelectForUpdateSkipLocked(baseQuery);

            assertTrue(result.contains("WITH (UPDLOCK, READPAST)"));
            assertTrue(result.contains("FROM calculation_job WITH (UPDLOCK, READPAST)"));
        }

        @Test
        @DisplayName("Should build WITH (UPDLOCK, ROWLOCK) for standard locking")
        void shouldBuildSelectForUpdate() {
            String baseQuery = "SELECT * FROM calculation_job WHERE id = ?";
            String result = provider.buildSelectForUpdate(baseQuery);

            assertTrue(result.contains("WITH (UPDLOCK, ROWLOCK)"));
            assertTrue(result.contains("FROM calculation_job WITH (UPDLOCK, ROWLOCK)"));
            assertFalse(result.contains("READPAST"));
        }
    }

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

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

        @Test
        @DisplayName("Should build date subtraction with GETDATE() using DATEADD")
        void shouldBuildDateSubtractionWithGetDate() {
            String result = provider.buildDateSubtraction(null, "3", SqlDialectProvider.DateUnit.DAY);
            assertEquals("DATEADD(DAY, -3, GETDATE())", result);
        }

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

        @Test
        @DisplayName("Should build date addition with GETDATE() using DATEADD")
        void shouldBuildDateAdditionWithGetDate() {
            String result = provider.buildDateAddition(null, "7", SqlDialectProvider.DateUnit.DAY);
            assertEquals("DATEADD(DAY, 7, GETDATE())", result);
        }

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

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

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

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

        @Test
        @DisplayName("Should build DBCC CHECKIDENT reset statement")
        void shouldBuildAutoIncrementResetStatement() {
            String result = provider.buildAutoIncrementReset("test_table");
            assertEquals("DBCC CHECKIDENT ('test_table', RESEED, 0)", 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");

            // MSSQL uses 6371 km (not 6371000 m like MySQL)
            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 using VARCHAR")
        void shouldCastToString() {
            String result = provider.castToString("user_id");
            assertEquals("CAST(user_id AS VARCHAR(MAX))", result);
        }
    }

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

        @Test
        @DisplayName("Should return INT max value for MSSQL")
        void shouldReturnMSSQLIntMaxValue() {
            // MSSQL returns INT max value (not BIGINT)
            assertEquals("2147483647", provider.getMaxLimitValue());
        }

        @Test
        @DisplayName("Should support RETURNING clause via OUTPUT")
        void shouldSupportReturningClause() {
            assertTrue(provider.supportsReturningClause());
        }

        @Test
        @DisplayName("Should build OUTPUT clause for RETURNING")
        void shouldBuildOutputClause() {
            String result = provider.buildReturningClause("id", "name", "created_at");

            assertEquals("OUTPUT INSERTED.id, INSERTED.name, INSERTED.created_at", result);
        }
    }

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

        @Test
        @DisplayName("Should return IDENTITY definition")
        void shouldReturnIdentityDefinition() {
            String result = provider.getAutoIncrementDefinition();
            assertEquals("IDENTITY(1,1)", result);
        }

        @Test
        @DisplayName("Should return DATETIME2 with default for timestamp")
        void shouldReturnDateTimeWithDefaultDefinition() {
            String result = provider.getTimestampDefinition();
            assertEquals("DATETIME2 DEFAULT GETDATE()", result);
        }
    }

    @Nested
    @DisplayName("Boolean Literal Tests")
    class BooleanLiteralTests {

        @Test
        @DisplayName("Should return '1' for boolean true")
        void shouldReturnOneForBooleanTrue() {
            assertEquals("1", provider.getBooleanTrue());
        }

        @Test
        @DisplayName("Should return '0' for boolean false")
        void shouldReturnZeroForBooleanFalse() {
            assertEquals("0", provider.getBooleanFalse());
        }
    }
}
