slices / api / src / database / query_builder.rs

//! SQL query building utilities for dynamic WHERE and ORDER BY clauses.
//!
//! This module provides helpers for constructing SQL queries dynamically
//! based on user input while preventing SQL injection attacks.

use super::cursor::build_field_reference;
use super::types::{SortField, WhereClause, WhereCondition};
use crate::models::Record;

/// Builds an ORDER BY clause with optional datetime field information.
///
/// # Arguments
/// * `sort_by` - Optional array of fields to sort by
/// * `field_is_datetime` - Optional boolean indicating if primary field is datetime
///
/// # Returns
/// SQL ORDER BY clause string (without "ORDER BY" prefix)
pub fn build_order_by_clause_with_field_info(
    sort_by: Option<&Vec<SortField>>,
    field_is_datetime: Option<bool>,
) -> String {
    match sort_by {
        Some(sort_fields) if !sort_fields.is_empty() => {
            let mut order_clauses = Vec::new();
            for (index, sort_field) in sort_fields.iter().enumerate() {
                let field = &sort_field.field;
                let direction = match sort_field.direction.to_lowercase().as_str() {
                    "desc" => "DESC",
                    _ => "ASC",
                };

                let is_primary = index == 0;
                let is_datetime = is_primary && field_is_datetime == Some(true);

                if field
                    .chars()
                    .all(|c| c.is_alphanumeric() || c == '_' || c == '.')
                {
                    let field_ref = build_field_reference(field, is_datetime);

                    if matches!(
                        field.as_str(),
                        "indexed_at" | "uri" | "cid" | "did" | "collection"
                    ) {
                        order_clauses.push(format!("{field_ref} {direction}"));
                    } else {
                        order_clauses.push(format!("{field_ref} {direction} NULLS LAST"));
                    }
                }
            }
            if !order_clauses.is_empty() {
                let has_indexed_at = order_clauses
                    .iter()
                    .any(|clause| clause.contains("indexed_at"));
                if !has_indexed_at {
                    order_clauses.push("indexed_at DESC".to_string());
                }
                order_clauses.join(", ")
            } else {
                "indexed_at DESC".to_string()
            }
        }
        _ => "indexed_at DESC".to_string(),
    }
}

/// Builds WHERE conditions from a WhereClause structure.
///
/// Returns separate arrays for AND conditions and OR conditions
/// to be combined in the final query.
///
/// Supports both legacy flat conditions and nested and/or arrays.
///
/// # Arguments
/// * `where_clause` - Optional where clause with AND/OR conditions
/// * `param_count` - Mutable counter for parameter numbering ($1, $2, etc)
///
/// # Returns
/// Tuple of (and_conditions, or_conditions) as SQL strings
pub fn build_where_conditions(
    where_clause: Option<&WhereClause>,
    param_count: &mut usize,
) -> (Vec<String>, Vec<String>) {
    let mut where_clauses = Vec::new();
    let mut or_clauses = Vec::new();

    if let Some(clause) = where_clause {
        // Handle legacy flat conditions
        for (field, condition) in &clause.conditions {
            let field_clause = build_single_condition(field, condition, param_count);
            if !field_clause.is_empty() {
                where_clauses.push(field_clause);
            }
        }

        // Handle legacy or_conditions
        if let Some(or_conditions) = &clause.or_conditions {
            for (field, condition) in or_conditions {
                let field_clause = build_single_condition(field, condition, param_count);
                if !field_clause.is_empty() {
                    or_clauses.push(field_clause);
                }
            }
        }

        // Handle nested AND array
        if let Some(and_clauses) = &clause.and {
            for nested_clause in and_clauses {
                let nested_sql = build_nested_clause(nested_clause, param_count);
                if !nested_sql.is_empty() {
                    where_clauses.push(nested_sql);
                }
            }
        }

        // Handle nested OR array
        if let Some(or_clauses_nested) = &clause.or {
            let mut or_parts = Vec::new();
            for nested_clause in or_clauses_nested {
                let nested_sql = build_nested_clause(nested_clause, param_count);
                if !nested_sql.is_empty() {
                    or_parts.push(nested_sql);
                }
            }
            if !or_parts.is_empty() {
                let or_sql = format!("({})", or_parts.join(" OR "));
                where_clauses.push(or_sql);
            }
        }
    }

    (where_clauses, or_clauses)
}

/// Builds a nested WHERE clause recursively.
///
/// Handles arbitrarily nested and/or structures.
///
/// # Arguments
/// * `clause` - The WhereClause to process
/// * `param_count` - Mutable counter for parameter numbering
///
/// # Returns
/// SQL condition string (may be wrapped in parentheses)
fn build_nested_clause(clause: &WhereClause, param_count: &mut usize) -> String {
    let mut parts = Vec::new();

    // Add flat conditions
    for (field, condition) in &clause.conditions {
        let field_clause = build_single_condition(field, condition, param_count);
        if !field_clause.is_empty() {
            parts.push(field_clause);
        }
    }

    // Add legacy or_conditions as an OR group
    if let Some(or_conditions) = &clause.or_conditions {
        let mut or_parts = Vec::new();
        for (field, condition) in or_conditions {
            let field_clause = build_single_condition(field, condition, param_count);
            if !field_clause.is_empty() {
                or_parts.push(field_clause);
            }
        }
        if !or_parts.is_empty() {
            parts.push(format!("({})", or_parts.join(" OR ")));
        }
    }

    // Add nested AND array
    if let Some(and_clauses) = &clause.and {
        for nested_clause in and_clauses {
            let nested_sql = build_nested_clause(nested_clause, param_count);
            if !nested_sql.is_empty() {
                parts.push(nested_sql);
            }
        }
    }

    // Add nested OR array
    if let Some(or_clauses) = &clause.or {
        let mut or_parts = Vec::new();
        for nested_clause in or_clauses {
            let nested_sql = build_nested_clause(nested_clause, param_count);
            if !nested_sql.is_empty() {
                or_parts.push(nested_sql);
            }
        }
        if !or_parts.is_empty() {
            parts.push(format!("({})", or_parts.join(" OR ")));
        }
    }

    if parts.is_empty() {
        String::new()
    } else if parts.len() == 1 {
        parts[0].clone()
    } else {
        format!("({})", parts.join(" AND "))
    }
}

/// Builds a single SQL condition clause for a field.
///
/// Supports equality (eq), array membership (in_values), pattern matching (contains),
/// fuzzy matching (fuzzy), and comparison operators (gt, gte, lt, lte)
/// for both table columns and JSON fields with nested paths.
///
/// # Arguments
/// * `field` - Field name (table column or JSON path)
/// * `condition` - The condition to apply
/// * `param_count` - Mutable counter for parameter numbering
///
/// # Returns
/// SQL condition string with parameter placeholder
pub fn build_single_condition(
    field: &str,
    condition: &WhereCondition,
    param_count: &mut usize,
) -> String {
    if let Some(_eq_value) = &condition.eq {
        let clause = match field {
            "did" | "collection" | "uri" | "cid" => {
                format!("{} = ${}", field, param_count)
            }
            _ => {
                let json_path = build_json_path(field);
                format!("{} = ${}", json_path, param_count)
            }
        };
        *param_count += 1;
        clause
    } else if let Some(_in_values) = &condition.in_values {
        let clause = match field {
            "did" | "collection" | "uri" | "cid" => {
                format!("{} = ANY(${})", field, param_count)
            }
            _ => {
                let json_path = build_json_path(field);
                format!("{} = ANY(${})", json_path, param_count)
            }
        };
        *param_count += 1;
        clause
    } else if let Some(_contains_value) = &condition.contains {
        let clause = match field {
            "did" | "collection" | "uri" | "cid" => {
                format!("{} ILIKE '%' || ${} || '%'", field, param_count)
            }
            "json" => {
                format!("json::text ILIKE '%' || ${} || '%'", param_count)
            }
            _ => {
                let json_path = build_json_path(field);
                format!("{} ILIKE '%' || ${} || '%'", json_path, param_count)
            }
        };
        *param_count += 1;
        clause
    } else if let Some(_fuzzy_value) = &condition.fuzzy {
        let clause = match field {
            "did" | "collection" | "uri" | "cid" => {
                format!("{} % ${}", field, param_count)
            }
            "json" => {
                format!("json::text % ${}", param_count)
            }
            _ => {
                let json_path = build_json_path(field);
                format!("({}) % ${}", json_path, param_count)
            }
        };
        *param_count += 1;
        clause
    } else if let Some(_gt_value) = &condition.gt {
        let clause = match field {
            "indexed_at" => {
                format!("{} > ${}::timestamptz", field, param_count)
            }
            "did" | "collection" | "uri" | "cid" => {
                format!("{} > ${}", field, param_count)
            }
            _ => {
                let json_path = build_json_path(field);
                format!("{} > ${}", json_path, param_count)
            }
        };
        *param_count += 1;
        clause
    } else if let Some(_gte_value) = &condition.gte {
        let clause = match field {
            "indexed_at" => {
                format!("{} >= ${}::timestamptz", field, param_count)
            }
            "did" | "collection" | "uri" | "cid" => {
                format!("{} >= ${}", field, param_count)
            }
            _ => {
                let json_path = build_json_path(field);
                format!("{} >= ${}", json_path, param_count)
            }
        };
        *param_count += 1;
        clause
    } else if let Some(_lt_value) = &condition.lt {
        let clause = match field {
            "indexed_at" => {
                format!("{} < ${}::timestamptz", field, param_count)
            }
            "did" | "collection" | "uri" | "cid" => {
                format!("{} < ${}", field, param_count)
            }
            _ => {
                let json_path = build_json_path(field);
                format!("{} < ${}", json_path, param_count)
            }
        };
        *param_count += 1;
        clause
    } else if let Some(_lte_value) = &condition.lte {
        let clause = match field {
            "indexed_at" => {
                format!("{} <= ${}::timestamptz", field, param_count)
            }
            "did" | "collection" | "uri" | "cid" => {
                format!("{} <= ${}", field, param_count)
            }
            _ => {
                let json_path = build_json_path(field);
                format!("{} <= ${}", json_path, param_count)
            }
        };
        *param_count += 1;
        clause
    } else {
        String::new()
    }
}

/// Builds a PostgreSQL JSON path accessor string.
///
/// Converts dot notation (e.g., "user.name") into PostgreSQL JSON operators
/// (e.g., "json->'user'->>'name'").
fn build_json_path(field: &str) -> String {
    if field.contains('.') {
        let parts: Vec<&str> = field.split('.').collect();
        let mut path = String::from("json");
        for (i, part) in parts.iter().enumerate() {
            if i == parts.len() - 1 {
                path.push_str(&format!("->>'{}'", part));
            } else {
                path.push_str(&format!("->'{}'", part));
            }
        }
        path
    } else {
        format!("json->>'{}'", field)
    }
}

/// Binds WHERE clause parameters to a sqlx query.
///
/// Iterates through all conditions and binds their values in the correct order.
/// Supports both legacy flat conditions and nested and/or arrays.
///
/// # Arguments
/// * `query_builder` - The sqlx query to bind parameters to
/// * `where_clause` - Optional where clause with parameter values
///
/// # Returns
/// Query builder with all parameters bound
pub fn bind_where_parameters<'q>(
    mut query_builder: sqlx::query::QueryAs<
        'q,
        sqlx::Postgres,
        Record,
        sqlx::postgres::PgArguments,
    >,
    where_clause: Option<&'q WhereClause>,
) -> sqlx::query::QueryAs<'q, sqlx::Postgres, Record, sqlx::postgres::PgArguments> {
    if let Some(clause) = where_clause {
        query_builder = bind_clause_recursive(query_builder, clause);
    }
    query_builder
}

/// Recursively binds parameters from a WhereClause (including nested clauses).
fn bind_clause_recursive<'q>(
    mut query_builder: sqlx::query::QueryAs<
        'q,
        sqlx::Postgres,
        Record,
        sqlx::postgres::PgArguments,
    >,
    clause: &'q WhereClause,
) -> sqlx::query::QueryAs<'q, sqlx::Postgres, Record, sqlx::postgres::PgArguments> {
    // Bind legacy flat conditions
    for condition in clause.conditions.values() {
        query_builder = bind_single_condition(query_builder, condition);
    }

    // Bind legacy or_conditions
    if let Some(or_conditions) = &clause.or_conditions {
        for condition in or_conditions.values() {
            query_builder = bind_single_condition(query_builder, condition);
        }
    }

    // Bind nested AND array
    if let Some(and_clauses) = &clause.and {
        for nested_clause in and_clauses {
            query_builder = bind_clause_recursive(query_builder, nested_clause);
        }
    }

    // Bind nested OR array
    if let Some(or_clauses) = &clause.or {
        for nested_clause in or_clauses {
            query_builder = bind_clause_recursive(query_builder, nested_clause);
        }
    }

    query_builder
}

/// Binds parameters for a single condition to a sqlx query.
///
/// Handles eq (single value), in_values (array), contains (pattern), fuzzy (similarity), and comparison conditions.
fn bind_single_condition<'q>(
    mut query_builder: sqlx::query::QueryAs<
        'q,
        sqlx::Postgres,
        Record,
        sqlx::postgres::PgArguments,
    >,
    condition: &'q WhereCondition,
) -> sqlx::query::QueryAs<'q, sqlx::Postgres, Record, sqlx::postgres::PgArguments> {
    if let Some(eq_value) = &condition.eq {
        if let Some(str_val) = eq_value.as_str() {
            query_builder = query_builder.bind(str_val);
        } else {
            query_builder = query_builder.bind(eq_value);
        }
    }

    if let Some(in_values) = &condition.in_values {
        let str_values: Vec<String> = in_values
            .iter()
            .filter_map(|v| v.as_str().map(|s| s.to_string()))
            .collect();
        query_builder = query_builder.bind(str_values);
    }

    if let Some(contains_value) = &condition.contains {
        query_builder = query_builder.bind(contains_value);
    }

    if let Some(fuzzy_value) = &condition.fuzzy {
        query_builder = query_builder.bind(fuzzy_value);
    }

    if let Some(gt_value) = &condition.gt {
        if let Some(str_val) = gt_value.as_str() {
            query_builder = query_builder.bind(str_val);
        } else {
            query_builder = query_builder.bind(gt_value);
        }
    }

    if let Some(gte_value) = &condition.gte {
        if let Some(str_val) = gte_value.as_str() {
            query_builder = query_builder.bind(str_val);
        } else {
            query_builder = query_builder.bind(gte_value);
        }
    }

    if let Some(lt_value) = &condition.lt {
        if let Some(str_val) = lt_value.as_str() {
            query_builder = query_builder.bind(str_val);
        } else {
            query_builder = query_builder.bind(lt_value);
        }
    }

    if let Some(lte_value) = &condition.lte {
        if let Some(str_val) = lte_value.as_str() {
            query_builder = query_builder.bind(str_val);
        } else {
            query_builder = query_builder.bind(lte_value);
        }
    }

    query_builder
}

/// Binds WHERE clause parameters to a sqlx scalar query (for COUNT queries).
///
/// Iterates through all conditions and binds their values in the correct order.
/// Supports both legacy flat conditions and nested and/or arrays.
///
/// # Arguments
/// * `query_builder` - The sqlx scalar query to bind parameters to
/// * `where_clause` - Optional where clause with parameter values
///
/// # Returns
/// Query builder with all parameters bound
pub fn bind_where_parameters_scalar<'q, T>(
    mut query_builder: sqlx::query::QueryScalar<'q, sqlx::Postgres, T, sqlx::postgres::PgArguments>,
    where_clause: Option<&'q WhereClause>,
) -> sqlx::query::QueryScalar<'q, sqlx::Postgres, T, sqlx::postgres::PgArguments>
where
    T: Send + Unpin,
{
    if let Some(clause) = where_clause {
        query_builder = bind_clause_recursive_scalar(query_builder, clause);
    }
    query_builder
}

/// Recursively binds parameters from a WhereClause to a scalar query.
fn bind_clause_recursive_scalar<'q, T>(
    mut query_builder: sqlx::query::QueryScalar<'q, sqlx::Postgres, T, sqlx::postgres::PgArguments>,
    clause: &'q WhereClause,
) -> sqlx::query::QueryScalar<'q, sqlx::Postgres, T, sqlx::postgres::PgArguments>
where
    T: Send + Unpin,
{
    // Bind legacy flat conditions
    for condition in clause.conditions.values() {
        query_builder = bind_single_condition_scalar(query_builder, condition);
    }

    // Bind legacy or_conditions
    if let Some(or_conditions) = &clause.or_conditions {
        for condition in or_conditions.values() {
            query_builder = bind_single_condition_scalar(query_builder, condition);
        }
    }

    // Bind nested AND array
    if let Some(and_clauses) = &clause.and {
        for nested_clause in and_clauses {
            query_builder = bind_clause_recursive_scalar(query_builder, nested_clause);
        }
    }

    // Bind nested OR array
    if let Some(or_clauses) = &clause.or {
        for nested_clause in or_clauses {
            query_builder = bind_clause_recursive_scalar(query_builder, nested_clause);
        }
    }

    query_builder
}

/// Binds parameters for a single condition to a sqlx scalar query.
fn bind_single_condition_scalar<'q, T>(
    mut query_builder: sqlx::query::QueryScalar<'q, sqlx::Postgres, T, sqlx::postgres::PgArguments>,
    condition: &'q WhereCondition,
) -> sqlx::query::QueryScalar<'q, sqlx::Postgres, T, sqlx::postgres::PgArguments>
where
    T: Send + Unpin,
{
    if let Some(eq_value) = &condition.eq {
        if let Some(str_val) = eq_value.as_str() {
            query_builder = query_builder.bind(str_val);
        } else {
            query_builder = query_builder.bind(eq_value);
        }
    }

    if let Some(in_values) = &condition.in_values {
        let str_values: Vec<String> = in_values
            .iter()
            .filter_map(|v| v.as_str().map(|s| s.to_string()))
            .collect();
        query_builder = query_builder.bind(str_values);
    }

    if let Some(contains_value) = &condition.contains {
        query_builder = query_builder.bind(contains_value);
    }

    if let Some(fuzzy_value) = &condition.fuzzy {
        query_builder = query_builder.bind(fuzzy_value);
    }

    if let Some(gt_value) = &condition.gt {
        if let Some(str_val) = gt_value.as_str() {
            query_builder = query_builder.bind(str_val);
        } else {
            query_builder = query_builder.bind(gt_value);
        }
    }

    if let Some(gte_value) = &condition.gte {
        if let Some(str_val) = gte_value.as_str() {
            query_builder = query_builder.bind(str_val);
        } else {
            query_builder = query_builder.bind(gte_value);
        }
    }

    if let Some(lt_value) = &condition.lt {
        if let Some(str_val) = lt_value.as_str() {
            query_builder = query_builder.bind(str_val);
        } else {
            query_builder = query_builder.bind(lt_value);
        }
    }

    if let Some(lte_value) = &condition.lte {
        if let Some(str_val) = lte_value.as_str() {
            query_builder = query_builder.bind(str_val);
        } else {
            query_builder = query_builder.bind(lte_value);
        }
    }

    query_builder
}

#[cfg(test)]
mod tests {
    use super::*;

    #[test]
    fn test_build_order_by_clause_default() {
        let result = build_order_by_clause_with_field_info(None, None);
        assert_eq!(result, "indexed_at DESC");
    }

    #[test]
    fn test_build_order_by_clause_single_field() {
        let sort_by = vec![SortField {
            field: "createdAt".to_string(),
            direction: "desc".to_string(),
        }];

        let result = build_order_by_clause_with_field_info(Some(&sort_by), None);
        assert_eq!(
            result,
            "json->>'createdAt' DESC NULLS LAST, indexed_at DESC"
        );
    }

    #[test]
    fn test_build_order_by_clause_datetime_field() {
        let sort_by = vec![SortField {
            field: "createdAt".to_string(),
            direction: "desc".to_string(),
        }];

        let result = build_order_by_clause_with_field_info(Some(&sort_by), Some(true));
        assert_eq!(
            result,
            "(json->>'createdAt')::timestamp DESC NULLS LAST, indexed_at DESC"
        );
    }

    #[test]
    fn test_build_order_by_clause_table_column() {
        let sort_by = vec![SortField {
            field: "indexed_at".to_string(),
            direction: "asc".to_string(),
        }];

        let result = build_order_by_clause_with_field_info(Some(&sort_by), None);
        assert_eq!(result, "indexed_at ASC");
    }

    #[test]
    fn test_build_order_by_clause_nested_json() {
        let sort_by = vec![SortField {
            field: "author.name".to_string(),
            direction: "asc".to_string(),
        }];

        let result = build_order_by_clause_with_field_info(Some(&sort_by), None);
        assert_eq!(
            result,
            "json->'author'->>'name' ASC NULLS LAST, indexed_at DESC"
        );
    }

    #[test]
    fn test_build_order_by_clause_multiple_fields() {
        let sort_by = vec![
            SortField {
                field: "text".to_string(),
                direction: "desc".to_string(),
            },
            SortField {
                field: "createdAt".to_string(),
                direction: "asc".to_string(),
            },
        ];

        let result = build_order_by_clause_with_field_info(Some(&sort_by), None);
        assert_eq!(
            result,
            "json->>'text' DESC NULLS LAST, json->>'createdAt' ASC NULLS LAST, indexed_at DESC"
        );
    }
}