//! 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.
///
/// # 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 {
        for (field, condition) in &clause.conditions {
            let field_clause = build_single_condition(field, condition, param_count);
            where_clauses.push(field_clause);
        }

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

    (where_clauses, or_clauses)
}

/// Builds a single SQL condition clause for a field.
///
/// Supports equality (eq), array membership (in_values), and pattern matching (contains)
/// for both table columns and JSON fields with nested paths.
///
/// # Arguments
/// * `field` - Field name (table column or JSON path)
/// * `condition` - The condition to apply (eq, in_values, or contains)
/// * `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 = if field == "json" {
            format!("json::text ILIKE '%' || ${} || '%'", param_count)
        } else {
            let json_path = build_json_path(field);
            format!("{} ILIKE '%' || ${} || '%'", 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.
///
/// # 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 {
        for condition in clause.conditions.values() {
            query_builder = bind_single_condition(query_builder, condition);
        }

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

/// Binds parameters for a single condition to a sqlx query.
///
/// Handles eq (single value), in_values (array), and contains (pattern) 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);
    }

    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"
        );
    }
}