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;

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

    @Override
    public String getDialectName() {
        return "MySQL";
    }

    @Override
    public String getDriverClassName() {
        return "com.mysql.cj.jdbc.Driver";
    }

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

    @Override
    public String buildPaginationClause(int limit, int offset) {
        return "LIMIT ? OFFSET ?";
    }

    @Override
    public Object[] getPaginationParameters(int limit, int offset) {
        return new Object[]{limit, offset};
    }

    // ========== Upsert Operations ==========

    @Override
    public String buildUpsertStatement(
        String tableName,
        List<String> uniqueColumns,
        List<String> insertColumns,
        List<String> updateColumns
    ) {
        // INSERT INTO table (col1, col2, ...) VALUES (?, ?, ...)
        String insertPart = String.format(
            "INSERT INTO %s (%s) VALUES (%s)",
            tableName,
            String.join(", ", insertColumns),
            insertColumns.stream().map(c -> "?").collect(Collectors.joining(", "))
        );

        // ON DUPLICATE KEY UPDATE col1 = VALUES(col1), col2 = VALUES(col2), ...
        String updatePart = updateColumns.stream()
            .map(col -> col + " = VALUES(" + col + ")")
            .collect(Collectors.joining(", "));

        return insertPart + " ON DUPLICATE KEY UPDATE " + updatePart;
    }

    @Override
    public String buildInsertIgnoreStatement(
        String tableName,
        List<String> columns,
        List<String> uniqueColumns
    ) {
        return String.format(
            "INSERT IGNORE INTO %s (%s) VALUES (%s)",
            tableName,
            String.join(", ", columns),
            columns.stream().map(c -> "?").collect(Collectors.joining(", "))
        );
    }

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

    @Override
    public String buildSelectForUpdateSkipLocked(String selectStatement) {
        return selectStatement + " FOR UPDATE SKIP LOCKED";
    }

    @Override
    public String buildSelectForUpdate(String selectStatement) {
        return selectStatement + " FOR UPDATE";
    }

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

    @Override
    public String getCurrentTimestamp() {
        return "NOW()";
    }

    @Override
    public String buildDateSubtraction(String baseDate, String value, DateUnit unit) {
        String base = baseDate != null ? baseDate : "NOW()";
        return String.format("DATE_SUB(%s, INTERVAL %s %s)", base, value, unit.name());
    }

    @Override
    public String buildDateAddition(String baseDate, String value, DateUnit unit) {
        String base = baseDate != null ? baseDate : "NOW()";
        return String.format("DATE_ADD(%s, INTERVAL %s %s)", base, value, unit.name());
    }

    @Override
    public String extractDate(String columnOrExpression) {
        return "DATE(" + columnOrExpression + ")";
    }

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

    @Override
    public String buildAutoIncrementReset(String tableName) {
        return String.format("ALTER TABLE %s AUTO_INCREMENT = 1", tableName);
    }

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

    @Override
    public String buildHaversineDistance(String lat1, String lng1, String lat2, String lng2) {
        // Haversine formula: 6371 km (Earth radius) * acos(...)
        // Formula: d = 2R * arcsin(sqrt(sin²((lat2-lat1)/2) + cos(lat1)*cos(lat2)*sin²((lon2-lon1)/2)))
        // Simplified: R * acos(cos(lat1)*cos(lat2)*cos(lng2-lng1) + sin(lat1)*sin(lat2))
        // Returns distance in KILOMETERS
        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 ==========

    @Override
    public String buildConcat(String... expressions) {
        return "CONCAT(" + String.join(", ", expressions) + ")";
    }

    @Override
    public String castToString(String expression) {
        return "CAST(" + expression + " AS CHAR)";
    }

    // ========== Bulk Operations ==========

    @Override
    public String getMaxLimitValue() {
        // MySQL BIGINT UNSIGNED max value
        return "18446744073709551615";
    }

    @Override
    public boolean supportsReturningClause() {
        return false;
    }

    @Override
    public String buildReturningClause(String... columns) {
        throw new UnsupportedOperationException(
            "MySQL does not support RETURNING clause. Use LAST_INSERT_ID() or GeneratedKeyHolder instead."
        );
    }

    // ========== Schema/DDL ==========

    @Override
    public String getAutoIncrementDefinition() {
        return "INT NOT NULL AUTO_INCREMENT";
    }

    @Override
    public String getTimestampDefinition() {
        return "TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP";
    }

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

    @Override
    public String getBooleanTrue() {
        return "TRUE";
    }

    @Override
    public String getBooleanFalse() {
        return "FALSE";
    }

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

    @Override
    public String escapeIdentifier(String identifier) {
        // MySQL uses backticks for escaping reserved words
        return "`" + identifier + "`";
    }
}