SQL Expressions and Functions

/**
 * Factory class for SQL expressions and functions
 */
public class SQLExpressions {
    /**
     * Creates a constant expression with the specified value
     * @param value Constant value
     * @return Expression representing the constant
     */
    public static <T> SimpleExpression<T> constant(T value);
    
    /**
     * Creates a case expression builder
     * @return CaseBuilder for constructing CASE statements
     */
    public static CaseBuilder cases();
    
    /**
     * Creates a SQL fragment expression
     * @param sql Raw SQL fragment
     * @param args Arguments for the SQL fragment
     * @return Expression wrapping the SQL fragment
     */
    public static SimpleExpression<Object> template(String sql, Object... args);
    
    /**
     * Creates a parameterized SQL template
     * @param type Return type of the expression
     * @param template SQL template with placeholders
     * @param args Template arguments
     * @return Typed expression from template
     */
    public static <T> SimpleExpression<T> template(Class<? extends T> type, String template, Object... args);
}

// Constant expressions
Expression<String> statusConstant = SQLExpressions.constant("ACTIVE");
Expression<Integer> zeroConstant = SQLExpressions.constant(0);

// SQL templates for database-specific features
Expression<Integer> customFunction = SQLExpressions.template(Integer.class, 
    "my_custom_function({0}, {1})", qUser.id, qUser.name);

// Case expressions
Expression<String> statusLabel = SQLExpressions.cases()
    .when(qUser.active.isTrue()).then("Active")
    .when(qUser.suspended.isTrue()).then("Suspended")
    .otherwise("Inactive");

/**
 * String concatenation function
 * @param args String expressions to concatenate
 * @return String expression for concatenated result
 */
public static StringExpression concat(Expression<String>... args);

/**
 * String length function
 * @param str String expression
 * @return Integer expression for string length
 */
public static NumberExpression<Integer> length(Expression<String> str);

/**
 * Substring function
 * @param str Source string expression
 * @param start Starting position (1-based)
 * @param length Length of substring
 * @return String expression for substring result
 */
public static StringExpression substring(Expression<String> str, int start, int length);

/**
 * String trimming function
 * @param str String expression to trim
 * @return String expression with whitespace removed
 */
public static StringExpression trim(Expression<String> str);

/**
 * Converts string to uppercase
 * @param str String expression
 * @return Uppercase string expression
 */
public static StringExpression upper(Expression<String> str);

/**
 * Converts string to lowercase
 * @param str String expression
 * @return Lowercase string expression
 */
public static StringExpression lower(Expression<String> str);

// String concatenation
StringExpression fullName = SQLExpressions.concat(qUser.firstName, 
    SQLExpressions.constant(" "), qUser.lastName);

// String manipulation in queries
List<Tuple> results = queryFactory
    .select(qUser.name, 
        SQLExpressions.upper(qUser.name).as("upper_name"),
        SQLExpressions.length(qUser.email).as("email_length"))
    .from(qUser)
    .fetch();

// Substring operations
List<String> areaCodes = queryFactory
    .select(SQLExpressions.substring(qUser.phone, 1, 3))
    .from(qUser)
    .where(qUser.phone.isNotNull())
    .fetch();

/**
 * Absolute value function
 * @param number Numeric expression
 * @return Numeric expression for absolute value
 */
public static <T extends Number & Comparable<T>> NumberExpression<T> abs(Expression<T> number);

/**
 * Ceiling function (round up)
 * @param number Numeric expression
 * @return Numeric expression rounded up to nearest integer
 */
public static NumberExpression<Double> ceil(Expression<? extends Number> number);

/**
 * Floor function (round down)
 * @param number Numeric expression
 * @return Numeric expression rounded down to nearest integer
 */
public static NumberExpression<Double> floor(Expression<? extends Number> number);

/**
 * Round function
 * @param number Numeric expression to round
 * @param precision Number of decimal places
 * @return Rounded numeric expression
 */
public static NumberExpression<Double> round(Expression<? extends Number> number, int precision);

/**
 * Square root function
 * @param number Numeric expression
 * @return Square root expression
 */
public static NumberExpression<Double> sqrt(Expression<? extends Number> number);

/**
 * Power function
 * @param base Base expression
 * @param exponent Exponent expression
 * @return Power expression
 */
public static NumberExpression<Double> power(Expression<? extends Number> base, 
                                           Expression<? extends Number> exponent);

// Mathematical calculations
List<Tuple> calculations = queryFactory
    .select(qOrder.amount,
        SQLExpressions.abs(qOrder.amount.subtract(qOrder.discount)).as("net_amount"),
        SQLExpressions.round(qOrder.amount.multiply(0.1), 2).as("tax"),
        SQLExpressions.sqrt(qOrder.quantity).as("sqrt_qty"))
    .from(qOrder)
    .fetch();

// Price calculations with rounding
NumberExpression<Double> finalPrice = SQLExpressions.round(
    qProduct.price.multiply(1.0 - qDiscount.percentage.divide(100.0)), 2);

/**
 * Current timestamp function
 * @return Expression for current database timestamp
 */
public static DateTimeExpression<Date> currentTimestamp();

/**
 * Current date function
 * @return Expression for current database date
 */
public static DateExpression<Date> currentDate();

/**
 * Current time function
 * @return Expression for current database time
 */
public static TimeExpression<Time> currentTime();

/**
 * Date addition function
 * @param date Date expression
 * @param interval Interval to add
 * @param part Date part to add (day, month, year, etc.)
 * @return Date expression with added interval
 */
public static DateTimeExpression<Date> dateAdd(Expression<Date> date, int interval, DatePart part);

/**
 * Date difference function
 * @param date1 First date expression
 * @param date2 Second date expression
 * @param part Date part for difference calculation
 * @return Integer expression for date difference
 */
public static NumberExpression<Integer> dateDiff(DatePart part, 
                                               Expression<Date> date1, 
                                               Expression<Date> date2);

/**
 * Extract date part function
 * @param part Date part to extract
 * @param date Date expression
 * @return Integer expression for extracted part
 */
public static NumberExpression<Integer> extract(DatePart part, Expression<Date> date);

// Current timestamp operations
List<User> recentUsers = queryFactory
    .selectFrom(qUser)
    .where(qUser.createdAt.gt(SQLExpressions.dateAdd(
        SQLExpressions.currentTimestamp(), -7, DatePart.day)))
    .fetch();

// Date calculations
List<Tuple> ageCalculations = queryFactory
    .select(qUser.name,
        SQLExpressions.dateDiff(DatePart.year, qUser.birthDate, 
            SQLExpressions.currentDate()).as("age"),
        SQLExpressions.extract(DatePart.month, qUser.birthDate).as("birth_month"))
    .from(qUser)
    .fetch();

// Date formatting and extraction
List<Integer> birthYears = queryFactory
    .select(SQLExpressions.extract(DatePart.year, qUser.birthDate))
    .from(qUser)
    .distinct()
    .orderBy(SQLExpressions.extract(DatePart.year, qUser.birthDate).asc())
    .fetch();

/**
 * Count function
 * @param expr Expression to count (use * for row count)
 * @return Count expression
 */
public static NumberExpression<Long> count(Expression<?> expr);

/**
 * Count distinct function
 * @param expr Expression to count distinct values
 * @return Count distinct expression
 */
public static NumberExpression<Long> countDistinct(Expression<?> expr);

/**
 * Sum function
 * @param expr Numeric expression to sum
 * @return Sum expression
 */
public static <T extends Number> NumberExpression<T> sum(Expression<T> expr);

/**
 * Average function
 * @param expr Numeric expression to average
 * @return Average expression
 */
public static NumberExpression<Double> avg(Expression<? extends Number> expr);

/**
 * Maximum function
 * @param expr Expression to find maximum value
 * @return Maximum expression
 */
public static <T extends Comparable<T>> ComparableExpression<T> max(Expression<T> expr);

/**
 * Minimum function
 * @param expr Expression to find minimum value
 * @return Minimum expression
 */
public static <T extends Comparable<T>> ComparableExpression<T> min(Expression<T> expr);

// Basic aggregations
Tuple stats = queryFactory
    .select(SQLExpressions.count(qOrder.id).as("total_orders"),
        SQLExpressions.sum(qOrder.amount).as("total_amount"),
        SQLExpressions.avg(qOrder.amount).as("avg_amount"),
        SQLExpressions.max(qOrder.amount).as("max_amount"),
        SQLExpressions.min(qOrder.createdAt).as("first_order"))
    .from(qOrder)
    .fetchOne();

// Group aggregations
List<Tuple> customerStats = queryFactory
    .select(qOrder.customerId,
        SQLExpressions.count(qOrder.id).as("order_count"),
        SQLExpressions.sum(qOrder.amount).as("total_spent"),
        SQLExpressions.countDistinct(qOrderItem.productId).as("unique_products"))
    .from(qOrder)
    .join(qOrderItem).on(qOrder.id.eq(qOrderItem.orderId))
    .groupBy(qOrder.customerId)
    .fetch();

/**
 * ROW_NUMBER() window function
 * @return Window function for row numbering
 */
public static WindowFunction<Long> rowNumber();

/**
 * RANK() window function
 * @return Window function for ranking with gaps
 */
public static WindowFunction<Long> rank();

/**
 * DENSE_RANK() window function
 * @return Window function for ranking without gaps
 */
public static WindowFunction<Long> denseRank();

/**
 * LAG() window function - access previous row value
 * @param expr Expression to get previous value of
 * @param offset Number of rows back (default 1)
 * @return Window function for accessing previous row
 */
public static <T> WindowFunction<T> lag(Expression<T> expr, int offset);

/**
 * LEAD() window function - access next row value
 * @param expr Expression to get next value of
 * @param offset Number of rows forward (default 1)
 * @return Window function for accessing next row
 */
public static <T> WindowFunction<T> lead(Expression<T> expr, int offset);

/**
 * FIRST_VALUE() window function
 * @param expr Expression to get first value of
 * @return Window function for first value in partition
 */
public static <T> WindowFunction<T> firstValue(Expression<T> expr);

/**
 * LAST_VALUE() window function
 * @param expr Expression to get last value of
 * @return Window function for last value in partition
 */
public static <T> WindowFunction<T> lastValue(Expression<T> expr);

// Row numbering and ranking
List<Tuple> rankedProducts = queryFactory
    .select(qProduct.name, qProduct.price,
        SQLExpressions.rowNumber().over(qProduct.price.desc()).as("price_rank"),
        SQLExpressions.rank().over(qProduct.categoryId.asc(), qProduct.price.desc()).as("category_rank"))
    .from(qProduct)
    .fetch();

// Running calculations with window functions
List<Tuple> runningTotals = queryFactory
    .select(qOrder.date, qOrder.amount,
        SQLExpressions.sum(qOrder.amount).over(qOrder.date.asc()).as("running_total"),
        SQLExpressions.avg(qOrder.amount).over(qOrder.date.asc()).as("running_avg"))
    .from(qOrder)
    .orderBy(qOrder.date.asc())
    .fetch();

// Lag/Lead for comparisons
List<Tuple> priceChanges = queryFactory
    .select(qStockPrice.date, qStockPrice.price,
        SQLExpressions.lag(qStockPrice.price, 1).over(qStockPrice.date.asc()).as("prev_price"),
        SQLExpressions.lead(qStockPrice.price, 1).over(qStockPrice.date.asc()).as("next_price"))
    .from(qStockPrice)
    .where(qStockPrice.symbol.eq("AAPL"))
    .orderBy(qStockPrice.date.asc())
    .fetch();

/**
 * COALESCE function - returns first non-null value
 * @param exprs Expressions to evaluate in order
 * @return Expression with first non-null value
 */
public static <T> SimpleExpression<T> coalesce(Expression<T>... exprs);

/**
 * NULLIF function - returns null if expressions are equal
 * @param expr1 First expression
 * @param expr2 Second expression
 * @return Null if equal, otherwise first expression
 */
public static <T> SimpleExpression<T> nullif(Expression<T> expr1, Expression<T> expr2);

/**
 * Creates a CASE expression builder
 * @return CaseBuilder for constructing CASE statements
 */
public static CaseBuilder cases();

/**
 * CASE expression builder interface
 */
public interface CaseBuilder {
    /**
     * Adds a WHEN condition
     * @param condition Boolean condition to test
     * @return Case when builder for specifying result
     */
    CaseWhen when(Predicate condition);
    
    /**
     * Case when builder for specifying THEN result
     */
    interface CaseWhen {
        /**
         * Specifies result when condition is true
         * @param expr Result expression
         * @return Case builder for additional conditions
         */
        <T> CaseBuilder then(Expression<T> expr);
    }
    
    /**
     * Specifies default result for ELSE clause
     * @param expr Default expression
     * @return Final case expression
     */
    <T> SimpleExpression<T> otherwise(Expression<T> expr);
}

// CASE expressions for conditional logic
Expression<String> orderStatus = SQLExpressions.cases()
    .when(qOrder.shippedAt.isNotNull()).then("Shipped")
    .when(qOrder.paidAt.isNotNull()).then("Paid")
    .when(qOrder.createdAt.isNotNull()).then("Pending")
    .otherwise("Unknown");

// COALESCE for null handling
Expression<String> displayName = SQLExpressions.coalesce(
    qUser.nickname, qUser.firstName, qUser.username, 
    SQLExpressions.constant("Anonymous"));

// Complex conditional aggregations
Expression<Long> activeOrderCount = SQLExpressions.cases()
    .when(qOrder.status.eq("ACTIVE")).then(1L)
    .otherwise(0L);

List<Tuple> customerSummary = queryFactory
    .select(qCustomer.name,
        SQLExpressions.sum(activeOrderCount).as("active_orders"),
        SQLExpressions.coalesce(SQLExpressions.max(qOrder.amount), 
            SQLExpressions.constant(0.0)).as("max_order"))
    .from(qCustomer)
    .leftJoin(qOrder).on(qCustomer.id.eq(qOrder.customerId))
    .groupBy(qCustomer.id, qCustomer.name)
    .fetch();

/**
 * PostgreSQL-specific functions
 */
public class PostgreSQLExpressions {
    /**
     * PostgreSQL array contains operator
     * @param array Array expression
     * @param element Element to check for
     * @return Boolean expression for containment check
     */
    public static BooleanExpression arrayContains(Expression<?> array, Expression<?> element);
    
    /**
     * PostgreSQL JSONB path query
     * @param jsonb JSONB column expression
     * @param path JSON path expression
     * @return Expression for JSONB path result
     */
    public static SimpleExpression<String> jsonbPath(Expression<String> jsonb, String path);
}

/**
 * MySQL-specific functions
 */
public class MySQLExpressions {
    /**
     * MySQL MATCH AGAINST full-text search
     * @param columns Columns to search in
     * @param searchText Search text
     * @return Boolean expression for full-text match
     */
    public static BooleanExpression match(Expression<?>[] columns, String searchText);
}

/**
 * Oracle-specific functions
 */
public class OracleExpressions {
    /**
     * Oracle CONNECT BY hierarchical query
     * @param condition Connect by condition
     * @return Hierarchical query expression
     */
    public static BooleanExpression connectBy(Predicate condition);
    
    /**
     * Oracle ROWNUM pseudo-column
     * @return Row number expression
     */
    public static NumberExpression<Long> rownum();
}

// PostgreSQL array operations
List<User> usersWithTag = queryFactory
    .selectFrom(qUser)
    .where(PostgreSQLExpressions.arrayContains(qUser.tags, 
        SQLExpressions.constant("premium")))
    .fetch();

// PostgreSQL JSONB queries
List<String> emailsFromJson = queryFactory
    .select(PostgreSQLExpressions.jsonbPath(qUser.metadata, "$.email"))
    .from(qUser)
    .where(qUser.metadata.isNotNull())
    .fetch();

// MySQL full-text search
List<Article> searchResults = queryFactory
    .selectFrom(qArticle)
    .where(MySQLExpressions.match(
        new Expression<?>[]{qArticle.title, qArticle.content}, 
        "java spring boot"))
    .fetch();

// Oracle hierarchical queries
List<Employee> hierarchy = queryFactory
    .selectFrom(qEmployee)
    .where(OracleExpressions.connectBy(qEmployee.managerId.eq(qEmployee.id)))
    .fetch();

/**
 * Represents a custom SQL function call
 * @param <T> Return type of the function
 */
public class RelationalFunctionCall<T> extends SimpleExpression<T> {
    /**
     * Creates a custom function call
     * @param type Return type
     * @param function Function name
     * @param args Function arguments
     */
    public RelationalFunctionCall(Class<? extends T> type, String function, Expression<?>... args);
}

// Custom function registration
public class CustomFunctions {
    public static NumberExpression<Double> calculateDistance(
            NumberExpression<Double> lat1, NumberExpression<Double> lon1,
            NumberExpression<Double> lat2, NumberExpression<Double> lon2) {
        return new RelationalFunctionCall<>(Double.class, "calculate_distance", 
            lat1, lon1, lat2, lon2);
    }
    
    public static StringExpression formatCurrency(NumberExpression<? extends Number> amount, 
                                                StringExpression currencyCode) {
        return new RelationalFunctionCall<>(String.class, "format_currency", 
            amount, currencyCode);
    }
}

// Usage of custom functions
List<Tuple> storeDistances = queryFactory
    .select(qStore.name,
        CustomFunctions.calculateDistance(
            SQLExpressions.constant(40.7128), // NYC latitude
            SQLExpressions.constant(-74.0060), // NYC longitude
            qStore.latitude,
            qStore.longitude).as("distance"))
    .from(qStore)
    .orderBy(CustomFunctions.calculateDistance(
        SQLExpressions.constant(40.7128),
        SQLExpressions.constant(-74.0060),
        qStore.latitude,
        qStore.longitude).asc())
    .fetch();