package de.avatic.lcc.database.dialect;

import java.util.List;

/**
 * Provides database-specific SQL syntax for different RDBMS implementations.
 * Supports MySQL and MSSQL Server with identical semantic behavior.
 *
 * <p>This interface abstracts database-specific SQL patterns to enable multi-database support
 * in the LCC application. Each dialect provider implements the SQL syntax specific to
 * its target database while maintaining consistent semantics across all implementations.</p>
 *
 * @author LCC Team
 * @since 1.0
 */
public interface SqlDialectProvider {

    // ========== Metadata ==========

    /**
     * Returns the dialect name (e.g., "MySQL", "MSSQL").
     *
     * @return the name of the database dialect
     */
    String getDialectName();

    /**
     * Returns the JDBC driver class name for this dialect.
     *
     * @return the fully qualified JDBC driver class name
     */
    String getDriverClassName();

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

    /**
     * Generates the pagination clause for limiting and offsetting query results.
     *
     * <p>Examples:</p>
     * <ul>
     *   <li>MySQL: {@code LIMIT ? OFFSET ?}</li>
     *   <li>MSSQL: {@code OFFSET ? ROWS FETCH NEXT ? ROWS ONLY}</li>
     * </ul>
     *
     * <p><b>Note:</b> MSSQL requires an ORDER BY clause before OFFSET/FETCH.</p>
     *
     * @param limit maximum number of rows to return
     * @param offset number of rows to skip
     * @return SQL clause for pagination (without parameter values)
     */
    String buildPaginationClause(int limit, int offset);

    /**
     * Returns parameter values in the correct order for the pagination clause.
     *
     * <p>Parameter order varies by database:</p>
     * <ul>
     *   <li>MySQL: {@code [limit, offset]}</li>
     *   <li>MSSQL: {@code [offset, limit]}</li>
     * </ul>
     *
     * @param limit maximum number of rows to return
     * @param offset number of rows to skip
     * @return array of parameters in database-specific order
     */
    Object[] getPaginationParameters(int limit, int offset);

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

    /**
     * Builds an UPSERT (INSERT or UPDATE) statement.
     *
     * <p>Database-specific implementations:</p>
     * <ul>
     *   <li>MySQL: {@code INSERT ... ON DUPLICATE KEY UPDATE ...}</li>
     *   <li>MSSQL: {@code MERGE ... WHEN MATCHED THEN UPDATE WHEN NOT MATCHED THEN INSERT ...}</li>
     * </ul>
     *
     * @param tableName target table name
     * @param uniqueColumns columns that define uniqueness (for matching existing rows)
     * @param insertColumns all columns to insert in a new row
     * @param updateColumns columns to update if row exists
     * @return complete UPSERT SQL statement with placeholders
     */
    String buildUpsertStatement(
        String tableName,
        List<String> uniqueColumns,
        List<String> insertColumns,
        List<String> updateColumns
    );

    /**
     * Builds an INSERT IGNORE statement that inserts only if the row does not exist.
     *
     * <p>Database-specific implementations:</p>
     * <ul>
     *   <li>MySQL: {@code INSERT IGNORE INTO ...}</li>
     *   <li>MSSQL: {@code IF NOT EXISTS (...) INSERT INTO ...}</li>
     * </ul>
     *
     * @param tableName target table name
     * @param columns columns to insert
     * @param uniqueColumns columns that define uniqueness (for existence check)
     * @return INSERT IGNORE statement with placeholders
     */
    String buildInsertIgnoreStatement(
        String tableName,
        List<String> columns,
        List<String> uniqueColumns
    );

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

    /**
     * Builds SELECT FOR UPDATE with skip locked capability for pessimistic locking.
     *
     * <p>This is critical for {@code CalculationJobRepository} concurrent job processing.</p>
     *
     * <p>Database-specific implementations:</p>
     * <ul>
     *   <li>MySQL: {@code SELECT ... FOR UPDATE SKIP LOCKED}</li>
     *   <li>MSSQL: {@code SELECT ... WITH (UPDLOCK, READPAST)}</li>
     * </ul>
     *
     * @param selectStatement base SELECT statement (without locking clause)
     * @return complete statement with pessimistic locking that skips locked rows
     */
    String buildSelectForUpdateSkipLocked(String selectStatement);

    /**
     * Builds standard SELECT FOR UPDATE for pessimistic locking (waits for locks).
     *
     * <p>Database-specific implementations:</p>
     * <ul>
     *   <li>MySQL: {@code SELECT ... FOR UPDATE}</li>
     *   <li>MSSQL: {@code SELECT ... WITH (UPDLOCK, ROWLOCK)}</li>
     * </ul>
     *
     * @param selectStatement base SELECT statement (without locking clause)
     * @return complete statement with pessimistic locking
     */
    String buildSelectForUpdate(String selectStatement);

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

    /**
     * Returns the SQL function for getting the current timestamp.
     *
     * <p>Database-specific implementations:</p>
     * <ul>
     *   <li>MySQL: {@code NOW()}</li>
     *   <li>MSSQL: {@code GETDATE()}</li>
     * </ul>
     *
     * @return SQL function for current timestamp
     */
    String getCurrentTimestamp();

    /**
     * Builds a date subtraction expression.
     *
     * <p>Database-specific implementations:</p>
     * <ul>
     *   <li>MySQL: {@code DATE_SUB(NOW(), INTERVAL ? DAY)}</li>
     *   <li>MSSQL: {@code DATEADD(DAY, -?, GETDATE())}</li>
     * </ul>
     *
     * @param baseDate base date expression (or null to use current timestamp)
     * @param value placeholder for number of time units to subtract (e.g., "?")
     * @param unit time unit (DAY, HOUR, MINUTE, etc.)
     * @return date subtraction expression
     */
    String buildDateSubtraction(String baseDate, String value, DateUnit unit);

    /**
     * Builds a date addition expression.
     *
     * <p>Database-specific implementations:</p>
     * <ul>
     *   <li>MySQL: {@code DATE_ADD(NOW(), INTERVAL ? DAY)}</li>
     *   <li>MSSQL: {@code DATEADD(DAY, ?, GETDATE())}</li>
     * </ul>
     *
     * @param baseDate base date expression (or null to use current timestamp)
     * @param value placeholder for number of time units to add (e.g., "?")
     * @param unit time unit (DAY, HOUR, MINUTE, etc.)
     * @return date addition expression
     */
    String buildDateAddition(String baseDate, String value, DateUnit unit);

    /**
     * Extracts the date part from a datetime expression (ignoring time component).
     *
     * <p>Database-specific implementations:</p>
     * <ul>
     *   <li>MySQL: {@code DATE(column)}</li>
     *   <li>MSSQL: {@code CAST(column AS DATE)}</li>
     * </ul>
     *
     * @param columnOrExpression column name or expression to extract date from
     * @return expression that extracts date component
     */
    String extractDate(String columnOrExpression);

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

    /**
     * Resets the auto-increment counter for a table (primarily used in tests).
     *
     * <p>Database-specific implementations:</p>
     * <ul>
     *   <li>MySQL: {@code ALTER TABLE table AUTO_INCREMENT = 1}</li>
     *   <li>MSSQL: {@code DBCC CHECKIDENT ('table', RESEED, 0)}</li>
     * </ul>
     *
     * @param tableName table to reset auto-increment counter
     * @return SQL statement to reset auto-increment
     */
    String buildAutoIncrementReset(String tableName);

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

    /**
     * Builds a Haversine distance calculation expression.
     *
     * <p>Used in {@code NodeRepository} for finding nearby nodes based on geographic coordinates.
     * Calculates the great-circle distance between two points on Earth's surface.</p>
     *
     * <p>Both MySQL and MSSQL support trigonometric functions (SIN, COS, ACOS, RADIANS),
     * so the implementation is similar across databases.</p>
     *
     * @param lat1 first latitude column or expression
     * @param lng1 first longitude column or expression
     * @param lat2 second latitude column or expression
     * @param lng2 second longitude column or expression
     * @return expression calculating distance in meters
     */
    String buildHaversineDistance(String lat1, String lng1, String lat2, String lng2);

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

    /**
     * Builds a string concatenation expression.
     *
     * <p>Database-specific implementations:</p>
     * <ul>
     *   <li>MySQL: {@code CONCAT(a, b, c)}</li>
     *   <li>MSSQL: {@code CONCAT(a, b, c)} (SQL Server 2012+) or {@code a + b + c}</li>
     * </ul>
     *
     * @param expressions expressions to concatenate
     * @return concatenation expression
     */
    String buildConcat(String... expressions);

    /**
     * Converts an expression to string type.
     *
     * <p>Database-specific implementations:</p>
     * <ul>
     *   <li>MySQL: {@code CAST(x AS CHAR)}</li>
     *   <li>MSSQL: {@code CAST(x AS VARCHAR(MAX))}</li>
     * </ul>
     *
     * @param expression expression to convert to string
     * @return cast-to-string expression
     */
    String castToString(String expression);

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

    /**
     * Returns the maximum safe value for LIMIT clause.
     *
     * <p>Used for workarounds in queries that need to skip LIMIT but still use OFFSET.</p>
     * <ul>
     *   <li>MySQL: {@code 18446744073709551615} (BIGINT UNSIGNED max)</li>
     *   <li>MSSQL: {@code 2147483647} (INT max)</li>
     * </ul>
     *
     * @return maximum limit value as string
     */
    String getMaxLimitValue();

    /**
     * Checks if the dialect supports RETURNING clause for INSERT statements.
     *
     * <ul>
     *   <li>MySQL: {@code false} (use LAST_INSERT_ID())</li>
     *   <li>MSSQL: {@code true} (supports OUTPUT INSERTED.id)</li>
     * </ul>
     *
     * @return true if RETURNING clause is supported
     */
    boolean supportsReturningClause();

    /**
     * Builds a RETURNING clause for INSERT statement.
     *
     * <p>MSSQL example: {@code OUTPUT INSERTED.id}</p>
     *
     * @param columns columns to return
     * @return RETURNING clause
     * @throws UnsupportedOperationException if dialect does not support RETURNING
     */
    String buildReturningClause(String... columns);

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

    /**
     * Returns the auto-increment column definition for schema creation.
     *
     * <p>Database-specific implementations:</p>
     * <ul>
     *   <li>MySQL: {@code INT NOT NULL AUTO_INCREMENT}</li>
     *   <li>MSSQL: {@code INT NOT NULL IDENTITY(1,1)}</li>
     * </ul>
     *
     * @return auto-increment column definition
     */
    String getAutoIncrementDefinition();

    /**
     * Returns the timestamp column definition with automatic update capability.
     *
     * <p>Database-specific implementations:</p>
     * <ul>
     *   <li>MySQL: {@code TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP}</li>
     *   <li>MSSQL: {@code DATETIME2 NOT NULL DEFAULT GETDATE()} (requires trigger for ON UPDATE)</li>
     * </ul>
     *
     * <p><b>Note:</b> For MSSQL, triggers must be created separately to handle ON UPDATE behavior.</p>
     *
     * @return timestamp column definition
     */
    String getTimestampDefinition();

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

    /**
     * Returns the SQL literal for boolean TRUE value.
     *
     * <p>Database-specific implementations:</p>
     * <ul>
     *   <li>MySQL: {@code TRUE}</li>
     *   <li>MSSQL: {@code 1}</li>
     * </ul>
     *
     * @return SQL literal for true
     */
    String getBooleanTrue();

    /**
     * Returns the SQL literal for boolean FALSE value.
     *
     * <p>Database-specific implementations:</p>
     * <ul>
     *   <li>MySQL: {@code FALSE}</li>
     *   <li>MSSQL: {@code 0}</li>
     * </ul>
     *
     * @return SQL literal for false
     */
    String getBooleanFalse();

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

    /**
     * Escapes a column or table identifier if it conflicts with reserved words.
     *
     * <p>Database-specific implementations:</p>
     * <ul>
     *   <li>MySQL: {@code `identifier`}</li>
     *   <li>MSSQL: {@code [identifier]}</li>
     * </ul>
     *
     * <p>Used for reserved words like "file", "user", "order", etc.</p>
     *
     * @param identifier column or table name to escape
     * @return escaped identifier
     */
    String escapeIdentifier(String identifier);

    // ========== Helper Enums ==========

    /**
     * Time units for date arithmetic operations.
     */
    enum DateUnit {
        /** Year unit */
        YEAR,
        /** Month unit */
        MONTH,
        /** Day unit */
        DAY,
        /** Hour unit */
        HOUR,
        /** Minute unit */
        MINUTE,
        /** Second unit */
        SECOND
    }
}