package de.avatic.lcc.database.dialect;

import org.springframework.context.annotation.Profile;
import org.springframework.stereotype.Component;

import java.util.Arrays;
import java.util.List;
import java.util.stream.Collectors;

/**
 * Microsoft SQL Server-specific implementation of {@link SqlDialectProvider}.
 *
 * <p>This provider generates SQL syntax compatible with SQL Server 2017+.
 * It is automatically activated when the "mssql" Spring profile is active.</p>
 *
 * @author LCC Team
 * @since 1.0
 */
@Component
@Profile("mssql")
public class MSSQLDialectProvider implements SqlDialectProvider {

    @Override
    public String getDialectName() {
        return "Microsoft SQL Server";
    }

    @Override
    public String getDriverClassName() {
        return "com.microsoft.sqlserver.jdbc.SQLServerDriver";
    }

    // ========== Pagination ==========

    /**
     * Builds MSSQL pagination clause using OFFSET/FETCH.
     *
     * <p>MSSQL syntax: {@code OFFSET ? ROWS FETCH NEXT ? ROWS ONLY}</p>
     *
     * @param limit  maximum number of rows to return
     * @param offset number of rows to skip
     * @return MSSQL pagination clause
     */
    @Override
    public String buildPaginationClause(int limit, int offset) {
        return "OFFSET ? ROWS FETCH NEXT ? ROWS ONLY";
    }

    /**
     * Returns pagination parameters for MSSQL in correct order: [offset, limit].
     *
     * <p>Note: MSSQL requires OFFSET first, then FETCH NEXT (opposite of MySQL).</p>
     *
     * @param limit  maximum number of rows
     * @param offset number of rows to skip
     * @return array with [offset, limit] (reversed compared to MySQL)
     */
    @Override
    public Object[] getPaginationParameters(int limit, int offset) {
        return new Object[]{offset, limit}; // MSSQL: offset first, then limit
    }

    /**
     * Returns the maximum LIMIT value for MSSQL.
     *
     * <p>MSSQL INT max value: {@code 2147483647}</p>
     *
     * @return "2147483647"
     */
    @Override
    public String getMaxLimitValue() {
        return "2147483647"; // INT max value in MSSQL
    }

    // ========== Upsert/Insert Ignore ==========

    /**
     * Builds MSSQL MERGE statement for upsert operations.
     *
     * <p>MSSQL uses MERGE instead of MySQL's ON DUPLICATE KEY UPDATE.</p>
     *
     * <p>Example generated SQL:</p>
     * <pre>
     * MERGE INTO table AS target
     * USING (SELECT ? AS col1, ? AS col2) AS source
     * ON target.key1 = source.key1 AND target.key2 = source.key2
     * WHEN MATCHED THEN
     *     UPDATE SET target.col3 = source.col3
     * WHEN NOT MATCHED THEN
     *     INSERT (col1, col2, col3) VALUES (source.col1, source.col2, source.col3);
     * </pre>
     *
     * @param tableName     target table name
     * @param uniqueColumns columns that define uniqueness (for ON clause)
     * @param insertColumns all columns to insert
     * @param updateColumns columns to update on match
     * @return MSSQL MERGE statement
     */
    @Override
    public String buildUpsertStatement(
            String tableName,
            List<String> uniqueColumns,
            List<String> insertColumns,
            List<String> updateColumns
    ) {
        if (tableName == null || uniqueColumns.isEmpty() || insertColumns.isEmpty()) {
            throw new IllegalArgumentException("tableName, uniqueColumns, and insertColumns must not be empty");
        }

        // Build source column list with placeholders
        String sourceColumns = insertColumns.stream()
                .map(col -> "? AS " + col)
                .collect(Collectors.joining(", "));

        // Build ON clause matching unique columns
        String onClause = uniqueColumns.stream()
                .map(col -> "target." + col + " = source." + col)
                .collect(Collectors.joining(" AND "));

        // Build UPDATE SET clause (only if updateColumns is not empty)
        String updateClause = "";
        if (updateColumns != null && !updateColumns.isEmpty()) {
            updateClause = "WHEN MATCHED THEN UPDATE SET " +
                    updateColumns.stream()
                            .map(col -> "target." + col + " = source." + col)
                            .collect(Collectors.joining(", ")) + " ";
        }

        // Build INSERT clause
        String insertColumnList = String.join(", ", insertColumns);
        String insertValueList = insertColumns.stream()
                .map(col -> "source." + col)
                .collect(Collectors.joining(", "));

        return String.format(
                "MERGE INTO %s AS target " +
                        "USING (SELECT %s) AS source " +
                        "ON %s " +
                        "%s" + // UPDATE clause (may be empty)
                        "WHEN NOT MATCHED THEN " +
                        "INSERT (%s) VALUES (%s);",
                tableName,
                sourceColumns,
                onClause,
                updateClause,
                insertColumnList,
                insertValueList
        );
    }

    @Override
    public String buildInsertIgnoreStatement(
            String tableName,
            List<String> columns,
            List<String> uniqueColumns
    ) {
        String columnList = String.join(", ", columns);
        String placeholders = columns.stream().map(c -> "?").collect(Collectors.joining(", "));
        String uniqueCondition = uniqueColumns.stream()
                .map(c -> String.format("target.%s = source.%s", c, c))
                .collect(Collectors.joining(" AND "));
        String sourceColumns = columns.stream()
                .map(c -> String.format("source.%s", c))
                .collect(Collectors.joining(", "));

        return String.format(
                "MERGE INTO %s AS target " +
                        "USING (SELECT %s) AS source (%s) " +
                        "ON %s " +
                        "WHEN NOT MATCHED THEN INSERT (%s) VALUES (%s);",
                tableName,
                placeholders,
                columnList,
                uniqueCondition,
                columnList,
                sourceColumns
        );
    }

    // ========== Locking Strategies ==========

    /**
     * Builds MSSQL SELECT with UPDLOCK and READPAST hints (equivalent to MySQL SKIP LOCKED).
     *
     * <p>MSSQL syntax: {@code SELECT ... FROM table WITH (UPDLOCK, READPAST)}</p>
     *
     * <p>The WITH hint must be placed after the table name in FROM clause.</p>
     *
     * @param selectStatement base SELECT statement
     * @return SELECT statement with UPDLOCK, READPAST hints
     */
    @Override
    public String buildSelectForUpdateSkipLocked(String selectStatement) {
        // Insert WITH (UPDLOCK, READPAST) after the first table name in FROM clause
        // This is a simplified approach - assumes "FROM tablename" pattern
        return selectStatement.replaceFirst(
                "FROM\\s+(\\w+)",
                "FROM $1 WITH (UPDLOCK, READPAST)"
        );
    }

    /**
     * Builds MSSQL SELECT with UPDLOCK hint (standard pessimistic locking).
     *
     * <p>MSSQL syntax: {@code SELECT ... FROM table WITH (UPDLOCK, ROWLOCK)}</p>
     *
     * @param selectStatement base SELECT statement
     * @return SELECT statement with UPDLOCK hint
     */
    @Override
    public String buildSelectForUpdate(String selectStatement) {
        return selectStatement.replaceFirst(
                "FROM\\s+(\\w+)",
                "FROM $1 WITH (UPDLOCK, ROWLOCK)"
        );
    }

    // ========== Date/Time Functions ==========

    /**
     * Returns MSSQL current timestamp function: {@code GETDATE()}.
     *
     * @return {@code GETDATE()}
     */
    @Override
    public String getCurrentTimestamp() {
        return "GETDATE()";
    }

    /**
     * Builds MSSQL date subtraction using DATEADD with negative value.
     *
     * <p>MSSQL syntax: {@code DATEADD(DAY, -?, GETDATE())}</p>
     *
     * @param baseDate base date expression (or null to use GETDATE())
     * @param value    placeholder for subtraction amount
     * @param unit     time unit (DAY, HOUR, MINUTE, etc.)
     * @return MSSQL DATEADD expression with negative value
     */
    @Override
    public String buildDateSubtraction(String baseDate, String value, DateUnit unit) {
        String base = (baseDate != null && !baseDate.isEmpty()) ? baseDate : "GETDATE()";
        // MSSQL uses DATEADD with negative value for subtraction
        return String.format("DATEADD(%s, -%s, %s)", unit.name(), value, base);
    }

    /**
     * Builds MSSQL date addition using DATEADD.
     *
     * <p>MSSQL syntax: {@code DATEADD(DAY, ?, GETDATE())}</p>
     *
     * @param baseDate base date expression (or null to use GETDATE())
     * @param value    placeholder for addition amount
     * @param unit     time unit (DAY, HOUR, MINUTE, etc.)
     * @return MSSQL DATEADD expression
     */
    @Override
    public String buildDateAddition(String baseDate, String value, DateUnit unit) {
        String base = (baseDate != null && !baseDate.isEmpty()) ? baseDate : "GETDATE()";
        return String.format("DATEADD(%s, %s, %s)", unit.name(), value, base);
    }

    /**
     * Extracts date part from datetime expression using CAST.
     *
     * <p>MSSQL syntax: {@code CAST(column AS DATE)}</p>
     *
     * @param columnOrExpression column name or expression
     * @return MSSQL CAST expression
     */
    @Override
    public String extractDate(String columnOrExpression) {
        return String.format("CAST(%s AS DATE)", columnOrExpression);
    }

    // ========== Auto-increment Reset ==========

    /**
     * Resets IDENTITY counter for a table using DBCC CHECKIDENT.
     *
     * <p>MSSQL syntax: {@code DBCC CHECKIDENT ('table', RESEED, 0)}</p>
     *
     * @param tableName table to reset IDENTITY counter
     * @return MSSQL DBCC CHECKIDENT statement
     */
    @Override
    public String buildAutoIncrementReset(String tableName) {
        return String.format("DBCC CHECKIDENT ('%s', RESEED, 0)", tableName);
    }

    // ========== Geospatial Distance Calculation ==========

    /**
     * Builds Haversine distance formula for MSSQL.
     *
     * <p>MSSQL supports the same trigonometric functions as MySQL (SIN, COS, ACOS, RADIANS),
     * so the formula is identical. Calculates great-circle distance in kilometers.</p>
     *
     * <p>Formula:</p>
     * <pre>
     * 6371 * ACOS(
     *   COS(RADIANS(lat1)) * COS(RADIANS(lat2)) * COS(RADIANS(lng2) - RADIANS(lng1)) +
     *   SIN(RADIANS(lat1)) * SIN(RADIANS(lat2))
     * )
     * </pre>
     *
     * @param lat1 first latitude column/expression
     * @param lng1 first longitude column/expression
     * @param lat2 second latitude column/expression
     * @param lng2 second longitude column/expression
     * @return Haversine distance expression in kilometers
     */
    @Override
    public String buildHaversineDistance(String lat1, String lng1, String lat2, String lng2) {
        return String.format(
                "6371 * ACOS(" +
                        "COS(RADIANS(%s)) * COS(RADIANS(%s)) * " +
                        "COS(RADIANS(%s) - RADIANS(%s)) + " +
                        "SIN(RADIANS(%s)) * SIN(RADIANS(%s))" +
                        ")",
                lat1, lat2, lng2, lng1, lat1, lat2
        );
    }

    // ========== String/Type Functions ==========

    /**
     * Builds string concatenation using CONCAT function (SQL Server 2012+).
     *
     * <p>MSSQL syntax: {@code CONCAT(a, b, c)}</p>
     *
     * @param expressions expressions to concatenate
     * @return MSSQL CONCAT expression
     */
    @Override
    public String buildConcat(String... expressions) {
        if (expressions == null || expressions.length == 0) {
            return "''";
        }
        return "CONCAT(" + String.join(", ", expressions) + ")";
    }

    /**
     * Casts expression to string type.
     *
     * <p>MSSQL syntax: {@code CAST(expression AS VARCHAR(MAX))}</p>
     *
     * @param expression expression to cast to string
     * @return MSSQL CAST expression
     */
    @Override
    public String castToString(String expression) {
        return String.format("CAST(%s AS VARCHAR(MAX))", expression);
    }

    // ========== RETURNING Clause Support ==========

    /**
     * MSSQL supports RETURNING clause via OUTPUT INSERTED.
     *
     * @return true
     */
    @Override
    public boolean supportsReturningClause() {
        return true;
    }

    /**
     * Builds MSSQL OUTPUT clause for INSERT statements.
     *
     * <p>MSSQL syntax: {@code OUTPUT INSERTED.column1, INSERTED.column2}</p>
     *
     * @param columns columns to return from inserted row
     * @return MSSQL OUTPUT INSERTED clause
     */
    @Override
    public String buildReturningClause(String... columns) {
        if (columns == null || columns.length == 0) {
            throw new IllegalArgumentException("At least one column must be specified");
        }
        String columnList = Arrays.stream(columns)
                .map(col -> "INSERTED." + col)
                .collect(Collectors.joining(", "));
        return "OUTPUT " + columnList;
    }

    /**
     * Returns MSSQL IDENTITY definition for auto-increment columns.
     *
     * <p>MSSQL syntax: {@code IDENTITY(1,1)}</p>
     *
     * @return {@code IDENTITY(1,1)}
     */
    @Override
    public String getAutoIncrementDefinition() {
        return "IDENTITY(1,1)";
    }

    /**
     * Returns MSSQL timestamp column definition.
     *
     * <p>MSSQL uses DATETIME2 with DEFAULT constraint.
     * Note: MSSQL doesn't support ON UPDATE CURRENT_TIMESTAMP like MySQL,
     * so updates must be handled via triggers or application logic.</p>
     *
     * @return DATETIME2 column definition
     */
    @Override
    public String getTimestampDefinition() {
        return "DATETIME2 DEFAULT GETDATE()";
    }

    // ========== Boolean Literals ==========

    /**
     * Returns MSSQL boolean TRUE literal as numeric 1.
     *
     * <p>MSSQL BIT type uses 1 for TRUE.</p>
     *
     * @return "1"
     */
    @Override
    public String getBooleanTrue() {
        return "1";
    }

    /**
     * Returns MSSQL boolean FALSE literal as numeric 0.
     *
     * <p>MSSQL BIT type uses 0 for FALSE.</p>
     *
     * @return "0"
     */
    @Override
    public String getBooleanFalse() {
        return "0";
    }

    // ========== Identifier Escaping ==========

    /**
     * Escapes identifier with square brackets for MSSQL reserved words.
     *
     * <p>MSSQL uses square brackets to escape reserved words like 'file', 'user', 'order'.</p>
     *
     * @param identifier column or table name to escape
     * @return escaped identifier with square brackets
     */
    @Override
    public String escapeIdentifier(String identifier) {
        // MSSQL uses square brackets for escaping reserved words
        return "[" + identifier + "]";
    }
}
