postgrest-parser 0.2.0

//! # PostgREST Query Parser
//!
//! A high-performance Rust library for parsing PostgREST query strings into structured SQL queries.
//!
//! ## Features
//!
//! - **Complete PostgREST API Support**: All 22+ filter operators (eq, neq, gt, gte, lt, lte, like, ilike, match, imatch, in, is, fts, plfts, phfts, wfts, cs, cd, ov, sl, sr, nxl, nxr, adj)
//! - **Logic Operators**: AND, OR, NOT with arbitrary nesting
//! - **JSON Path Navigation**: `->` and `->>` operators for JSONB fields
//! - **Type Casting**: Cast fields with `::type` syntax
//! - **Full-Text Search**: Multiple FTS operators with language support
//! - **Quantifiers**: `any` and `all` quantifiers for array comparisons
//! - **Array/Range Operators**: PostgreSQL array and range type support
//! - **Ordering**: Multi-column ordering with nulls handling
//! - **Pagination**: `limit` and `offset` support
//! - **SQL Generation**: Convert parsed queries to parameterized PostgreSQL SQL
//!
//! ## Quick Start
//!
//! ```rust
//! use postgrest_parser::{parse_query_string, query_string_to_sql};
//!
//! // Parse a PostgREST query string
//! let query = "select=id,name,email&age=gte.18&status=in.(active,pending)&order=created_at.desc&limit=10";
//! let params = parse_query_string(query).unwrap();
//!
//! assert!(params.has_select());
//! assert!(params.has_filters());
//! assert_eq!(params.limit, Some(10));
//!
//! // Convert to SQL
//! let result = query_string_to_sql("users", query).unwrap();
//! println!("SQL: {}", result.query);
//! println!("Params: {:?}", result.params);
//! ```
//!
//! ## Filter Operators
//!
//! ### Comparison Operators
//! - `eq` - Equal to
//! - `neq` - Not equal to
//! - `gt` - Greater than
//! - `gte` - Greater than or equal to
//! - `lt` - Less than
//! - `lte` - Less than or equal to
//!
//! ### Pattern Matching
//! - `like` - SQL LIKE pattern matching
//! - `ilike` - Case-insensitive LIKE
//! - `match` - POSIX regex match
//! - `imatch` - Case-insensitive regex match
//!
//! ### Array Operators
//! - `in` - Value in list
//! - `cs` - Contains (array/range)
//! - `cd` - Contained in (array/range)
//! - `ov` - Overlaps (array)
//!
//! ### Full-Text Search
//! - `fts` - Full-text search using plainto_tsquery
//! - `plfts` - Plain full-text search (alias for fts)
//! - `phfts` - Phrase full-text search using phraseto_tsquery
//! - `wfts` - Websearch full-text search using websearch_to_tsquery
//!
//! ### Range Operators
//! - `sl` - Strictly left of
//! - `sr` - Strictly right of
//! - `nxl` - Does not extend to right of
//! - `nxr` - Does not extend to left of
//! - `adj` - Adjacent to
//!
//! ### Special Operators
//! - `is` - IS NULL, IS TRUE, IS FALSE, etc.
//!
//! ## Examples
//!
//! ### Simple Filtering
//! ```rust
//! use postgrest_parser::parse_query_string;
//!
//! let query = "age=gte.18&status=eq.active";
//! let params = parse_query_string(query).unwrap();
//! assert_eq!(params.filters.len(), 2);
//! ```
//!
//! ### Logic Operators
//! ```rust
//! use postgrest_parser::parse_query_string;
//!
//! let query = "and=(age.gte.18,status.eq.active)";
//! let params = parse_query_string(query).unwrap();
//! assert!(params.has_filters());
//! ```
//!
//! ### JSON Path Navigation
//! ```rust
//! use postgrest_parser::parse_query_string;
//!
//! let query = "data->name=eq.John&data->>email=like.*@example.com";
//! let params = parse_query_string(query).unwrap();
//! assert_eq!(params.filters.len(), 2);
//! ```
//!
//! ### Full-Text Search
//! ```rust
//! use postgrest_parser::parse_query_string;
//!
//! let query = "content=fts(english).search term";
//! let params = parse_query_string(query).unwrap();
//! ```
//!
//! ### Quantifiers
//! ```rust
//! use postgrest_parser::parse_query_string;
//!
//! let query = "tags=eq(any).{rust,elixir}";
//! let params = parse_query_string(query).unwrap();
//! ```

use serde::Serialize;

pub mod ast;
pub mod error;
pub mod parser;
pub mod sql;

#[cfg(any(feature = "postgres", feature = "wasm"))]
pub mod schema_cache;

#[cfg(feature = "wasm")]
pub mod wasm;

pub use ast::{
    Cardinality, Column, ConflictAction, Count, DeleteParams, Direction, Field, Filter,
    FilterOperator, FilterValue, InsertParams, InsertValues, ItemHint, ItemType, JsonOp, Junction,
    LogicCondition, LogicOperator, LogicTree, Missing, Nulls, OnConflict, Operation, OrderTerm,
    ParsedParams, Plurality, PreferOptions, Quantifier, Relationship, Resolution, ResolvedTable,
    ReturnRepresentation, RpcParams, SelectItem, Table, UpdateParams,
};
pub use error::{Error, ParseError, SqlError};
pub use parser::{
    field, get_profile_header, identifier, json_path, json_path_segment, logic_key,
    parse_delete_params, parse_filter, parse_insert_params, parse_json_body, parse_logic,
    parse_order, parse_order_term, parse_prefer_header, parse_qualified_table, parse_rpc_params,
    parse_select, parse_update_params, reserved_key, resolve_schema, type_cast,
    validate_insert_body, validate_update_body,
};
pub use sql::{QueryBuilder, QueryResult};

#[cfg(feature = "postgres")]
pub use schema_cache::{ForeignKey, RelationType, SchemaCache};

/// Parses a PostgREST query string into structured parameters.
///
/// # Arguments
///
/// * `query_string` - A query string in PostgREST format (e.g., "select=id,name&age=gte.18")
///
/// # Returns
///
/// Returns `Ok(ParsedParams)` containing parsed select, filters, order, limit, and offset,
/// or an `Err(Error)` if parsing fails.
///
/// # Examples
///
/// ```
/// use postgrest_parser::parse_query_string;
///
/// let query = "select=id,name&age=gte.18&order=created_at.desc&limit=10";
/// let params = parse_query_string(query).unwrap();
///
/// assert!(params.has_select());
/// assert!(params.has_filters());
/// assert_eq!(params.limit, Some(10));
/// ```
pub fn parse_query_string(query_string: &str) -> Result<ParsedParams, Error> {
    let pairs: Vec<(String, String)> = query_string
        .split('&')
        .filter_map(|pair| {
            let parts: Vec<&str> = pair.splitn(2, '=').collect();
            if parts.len() == 2 {
                Some((parts[0].to_string(), parts[1].to_string()))
            } else {
                None
            }
        })
        .collect();

    parse_params_from_pairs(pairs)
}

/// Parses query parameters from a HashMap into structured parameters.
///
/// This is useful when you already have parsed URL parameters (e.g., from a web framework).
///
/// # Arguments
///
/// * `params` - A HashMap containing query parameter key-value pairs
///
/// # Examples
///
/// ```
/// use postgrest_parser::parse_params;
/// use std::collections::HashMap;
///
/// let mut params = HashMap::new();
/// params.insert("select".to_string(), "id,name".to_string());
/// params.insert("age".to_string(), "gte.18".to_string());
///
/// let parsed = parse_params(&params).unwrap();
/// assert!(parsed.has_select());
/// assert!(parsed.has_filters());
/// ```
pub fn parse_params(
    params: &std::collections::HashMap<String, String>,
) -> Result<ParsedParams, Error> {
    let select_str = params.get("select").map(|s| s.to_string());
    let order_str = params.get("order").map(|s| s.to_string());
    let filters = parse_filters_from_map(params)?;
    let limit = params.get("limit").and_then(|s| s.parse::<u64>().ok());
    let offset = params.get("offset").and_then(|s| s.parse::<u64>().ok());

    let mut parsed = ParsedParams::new().with_filters(filters);

    if let Some(select_str) = select_str {
        parsed = parsed.with_select(parse_select(&select_str)?);
    }

    if let Some(order_str) = order_str {
        parsed = parsed.with_order(parse_order(&order_str)?);
    }

    if let Some(lim) = limit {
        parsed = parsed.with_limit(lim);
    }

    if let Some(off) = offset {
        parsed = parsed.with_offset(off);
    }

    Ok(parsed)
}

pub fn parse_params_from_pairs(pairs: Vec<(String, String)>) -> Result<ParsedParams, Error> {
    // Build a HashMap for single-value keys (select, order, limit, offset)
    // But keep ALL filter pairs to support multiple filters on same column
    let mut single_value_map = std::collections::HashMap::new();
    let mut filter_pairs = Vec::new();

    for (key, value) in pairs {
        if parser::filter::reserved_key(&key) {
            // Reserved keys: select, order, limit, offset - only keep last value
            single_value_map.insert(key, value);
        } else {
            // Filter keys: keep all pairs to support multiple filters on same column
            filter_pairs.push((key, value));
        }
    }

    // Parse single-value parameters
    let select_str = single_value_map.get("select").map(|s| s.to_string());
    let order_str = single_value_map.get("order").map(|s| s.to_string());
    let limit = single_value_map
        .get("limit")
        .and_then(|s| s.parse::<u64>().ok());
    let offset = single_value_map
        .get("offset")
        .and_then(|s| s.parse::<u64>().ok());

    // Parse filters from pairs (supports multiple filters on same column)
    let filters = parse_filters_from_pairs(&filter_pairs)?;

    let mut parsed = ParsedParams::new().with_filters(filters);

    if let Some(select_str) = select_str {
        parsed = parsed.with_select(parse_select(&select_str)?);
    }

    if let Some(order_str) = order_str {
        parsed = parsed.with_order(parse_order(&order_str)?);
    }

    if let Some(lim) = limit {
        parsed = parsed.with_limit(lim);
    }

    if let Some(off) = offset {
        parsed = parsed.with_offset(off);
    }

    Ok(parsed)
}

/// Converts parsed parameters into a parameterized PostgreSQL SELECT query.
///
/// # Arguments
///
/// * `table` - The table name to query
/// * `params` - Parsed parameters containing select, filters, order, limit, and offset
///
/// # Returns
///
/// Returns a `QueryResult` containing the SQL query string and parameter values.
///
/// # Examples
///
/// ```
/// use postgrest_parser::{parse_query_string, to_sql};
///
/// let params = parse_query_string("age=gte.18&order=name.asc&limit=10").unwrap();
/// let result = to_sql("users", &params).unwrap();
///
/// assert!(result.query.contains("SELECT"));
/// assert!(result.query.contains("WHERE"));
/// assert!(result.query.contains("ORDER BY"));
/// assert!(result.query.contains("LIMIT"));
/// ```
pub fn to_sql(table: &str, params: &ParsedParams) -> Result<QueryResult, Error> {
    if table.is_empty() {
        return Err(Error::Sql(SqlError::EmptyTableName));
    }

    let mut builder = QueryBuilder::new();
    builder.build_select(table, params).map_err(Error::Sql)
}

/// Parses a PostgREST query string and converts it directly to SQL.
///
/// This is a convenience function that combines `parse_query_string` and `to_sql`.
///
/// # Arguments
///
/// * `table` - The table name to query
/// * `query_string` - A PostgREST query string
///
/// # Returns
///
/// Returns a `QueryResult` containing the SQL query and parameters.
///
/// # Examples
///
/// ```
/// use postgrest_parser::query_string_to_sql;
///
/// let result = query_string_to_sql(
///     "users",
///     "select=id,name,email&age=gte.18&status=eq.active"
/// ).unwrap();
///
/// assert!(result.query.contains("SELECT"));
/// assert!(result.query.contains("\"id\""));
/// assert_eq!(result.tables, vec!["users"]);
/// ```
pub fn query_string_to_sql(table: &str, query_string: &str) -> Result<QueryResult, Error> {
    let params = parse_query_string(query_string)?;
    to_sql(table, &params)
}

/// Builds a WHERE clause from filter conditions without the full query.
///
/// Useful when you need just the filter clause portion of a query.
///
/// # Arguments
///
/// * `filters` - A slice of logic conditions (filters)
///
/// # Returns
///
/// Returns a `FilterClauseResult` containing the WHERE clause and parameters.
///
/// # Examples
///
/// ```
/// use postgrest_parser::{build_filter_clause, Filter, Field, FilterOperator, FilterValue, LogicCondition};
///
/// let filter = LogicCondition::Filter(Filter::new(
///     Field::new("age"),
///     FilterOperator::Gte,
///     FilterValue::Single("18".to_string()),
/// ));
///
/// let result = build_filter_clause(&[filter]).unwrap();
/// assert!(result.clause.contains("\"age\""));
/// assert!(result.clause.contains(">="));
/// ```
pub fn build_filter_clause(filters: &[LogicCondition]) -> Result<FilterClauseResult, Error> {
    let mut builder = QueryBuilder::new();
    builder.build_where_clause(filters).map_err(Error::Sql)?;

    Ok(FilterClauseResult {
        clause: builder.sql.clone(),
        params: builder.params.clone(),
    })
}

/// Unified parse function for all PostgREST operations
///
/// # Arguments
///
/// * `method` - HTTP method (GET, POST, PATCH, DELETE)
/// * `table` - Table name, optionally schema-qualified (e.g., "users" or "auth.users")
/// * `query_string` - Query parameters
/// * `body` - Optional JSON body for mutations
/// * `headers` - Optional headers for schema resolution
///
/// # Examples
///
/// ```
/// use postgrest_parser::parse;
/// use std::collections::HashMap;
///
/// // SELECT
/// let op = parse("GET", "users", "id=eq.123", None, None).unwrap();
///
/// // INSERT
/// let body = r#"{"name": "Alice"}"#;
/// let op = parse("POST", "users", "", Some(body), None).unwrap();
///
/// // UPDATE with schema
/// let mut headers = HashMap::new();
/// headers.insert("Content-Profile".to_string(), "auth".to_string());
/// let body = r#"{"status": "active"}"#;
/// let op = parse("PATCH", "users", "id=eq.123", Some(body), Some(&headers)).unwrap();
/// ```
pub fn parse(
    method: &str,
    table: &str,
    query_string: &str,
    body: Option<&str>,
    headers: Option<&std::collections::HashMap<String, String>>,
) -> Result<Operation, Error> {
    // Check if this is an RPC call (table starts with "rpc/")
    if let Some(function_name) = table.strip_prefix("rpc/") {
        // Validate function name
        if function_name.is_empty() {
            return Err(Error::Parse(ParseError::InvalidTableName(
                "RPC function name cannot be empty".to_string(),
            )));
        }

        // Validate schema for RPC
        let _resolved_table = resolve_schema(function_name, method, headers)?;

        // Extract Prefer header
        let prefer = headers
            .and_then(|h| {
                h.get("Prefer")
                    .or_else(|| h.get("prefer"))
                    .or_else(|| h.get("PREFER"))
            })
            .map(|p| parse_prefer_header(p))
            .transpose()?;

        // Parse RPC parameters (supports both GET and POST)
        let params = parse_rpc_params(function_name, query_string, body)?;
        return Ok(Operation::Rpc(params, prefer));
    }

    // Validate table name and schema (result used for validation)
    let _resolved_table = resolve_schema(table, method, headers)?;

    // Extract Prefer header (case-insensitive)
    let prefer = headers
        .and_then(|h| {
            h.get("Prefer")
                .or_else(|| h.get("prefer"))
                .or_else(|| h.get("PREFER"))
        })
        .map(|p| parse_prefer_header(p))
        .transpose()?;

    match method.to_uppercase().as_str() {
        "GET" => {
            let params = parse_query_string(query_string)?;
            Ok(Operation::Select(params, prefer))
        }
        "POST" => {
            let body = body.ok_or_else(|| {
                Error::Parse(ParseError::InvalidInsertBody(
                    "Body is required for INSERT".to_string(),
                ))
            })?;
            let params = parse_insert_params(query_string, body)?;
            Ok(Operation::Insert(params, prefer))
        }
        "PUT" => {
            // PUT is upsert: INSERT with automatic ON CONFLICT
            let body = body.ok_or_else(|| {
                Error::Parse(ParseError::InvalidInsertBody(
                    "Body is required for PUT/upsert".to_string(),
                ))
            })?;
            let mut params = parse_insert_params(query_string, body)?;

            // If no ON CONFLICT specified, auto-create one from query filters
            if params.on_conflict.is_none() {
                // Extract column names from query string filters to use as conflict target
                let conflict_columns = extract_conflict_columns_from_query(query_string);
                if !conflict_columns.is_empty() {
                    params = params.with_on_conflict(OnConflict::do_update(conflict_columns));
                }
            }

            Ok(Operation::Insert(params, prefer))
        }
        "PATCH" => {
            let body = body.ok_or_else(|| {
                Error::Parse(ParseError::InvalidUpdateBody(
                    "Body is required for UPDATE".to_string(),
                ))
            })?;
            let params = parse_update_params(query_string, body)?;
            Ok(Operation::Update(params, prefer))
        }
        "DELETE" => {
            let params = parse_delete_params(query_string)?;
            Ok(Operation::Delete(params, prefer))
        }
        _ => Err(Error::Parse(ParseError::UnsupportedMethod(format!(
            "Unsupported HTTP method: {}",
            method
        )))),
    }
}

/// Converts an Operation to SQL
///
/// # Arguments
///
/// * `table` - Table name (can be schema-qualified)
/// * `operation` - The operation to convert
///
/// # Examples
///
/// ```
/// use postgrest_parser::{parse, operation_to_sql};
///
/// let op = parse("GET", "users", "id=eq.123", None, None).unwrap();
/// let result = operation_to_sql("users", &op).unwrap();
/// assert!(result.query.contains("SELECT"));
/// ```
pub fn operation_to_sql(table: &str, operation: &Operation) -> Result<QueryResult, Error> {
    #[cfg(any(feature = "postgres", feature = "wasm"))]
    {
        operation_to_sql_with_cache(table, operation, None)
    }
    #[cfg(not(any(feature = "postgres", feature = "wasm")))]
    {
        operation_to_sql_inner(table, operation, QueryBuilder::new)
    }
}

/// Converts an Operation to SQL with an optional schema cache for relation resolution.
///
/// This variant allows passing a `SchemaCache` for resolving foreign key
/// relationships in embedded resource queries (e.g., `select=*,posts(*)`)
/// without requiring the `postgres` feature for database connectivity.
#[cfg(any(feature = "postgres", feature = "wasm"))]
pub fn operation_to_sql_with_cache(
    table: &str,
    operation: &Operation,
    schema_cache: Option<std::sync::Arc<schema_cache::SchemaCache>>,
) -> Result<QueryResult, Error> {
    let make_builder = move || -> QueryBuilder {
        let mut builder = QueryBuilder::new();
        if let Some(cache) = &schema_cache {
            builder = builder.with_schema_cache(cache.clone());
        }
        builder
    };
    operation_to_sql_inner(table, operation, make_builder)
}

fn operation_to_sql_inner(
    table: &str,
    operation: &Operation,
    make_builder: impl Fn() -> QueryBuilder,
) -> Result<QueryResult, Error> {
    match operation {
        Operation::Select(params, _prefer) => {
            if table.is_empty() {
                return Err(Error::Sql(SqlError::EmptyTableName));
            }
            let mut builder = make_builder();
            builder.build_select(table, params).map_err(Error::Sql)
        }
        Operation::Insert(params, _prefer) => {
            let resolved_table = resolve_schema(table, "POST", None)?;
            let mut builder = make_builder();
            builder
                .build_insert(&resolved_table, params)
                .map_err(Error::Sql)
        }
        Operation::Update(params, _prefer) => {
            let resolved_table = resolve_schema(table, "PATCH", None)?;
            let mut builder = make_builder();
            builder
                .build_update(&resolved_table, params)
                .map_err(Error::Sql)
        }
        Operation::Delete(params, _prefer) => {
            let resolved_table = resolve_schema(table, "DELETE", None)?;
            let mut builder = make_builder();
            builder
                .build_delete(&resolved_table, params)
                .map_err(Error::Sql)
        }
        Operation::Rpc(params, _prefer) => {
            let function_name = table.strip_prefix("rpc/").unwrap_or(table);
            let resolved_table = resolve_schema(function_name, "POST", None)?;
            let mut builder = make_builder();
            builder
                .build_rpc(&resolved_table, params)
                .map_err(Error::Sql)
        }
    }
}

/// Extracts column names from query string filters for use as ON CONFLICT target
///
/// Used by PUT requests to automatically determine conflict columns
fn extract_conflict_columns_from_query(query_string: &str) -> Vec<String> {
    if query_string.is_empty() {
        return Vec::new();
    }

    let mut columns = Vec::new();
    for pair in query_string.split('&') {
        let parts: Vec<&str> = pair.splitn(2, '=').collect();
        if parts.len() == 2 {
            let key = parts[0];
            // Skip reserved keys
            if !parser::filter::reserved_key(key) && !parser::logic::logic_key(key) {
                // Extract base column name (before any JSON operators)
                let column_name = if let Some(arrow_pos) = key.find("->") {
                    &key[..arrow_pos]
                } else {
                    key
                };
                if !columns.contains(&column_name.to_string()) {
                    columns.push(column_name.to_string());
                }
            }
        }
    }
    columns
}

fn parse_filters_from_map(
    params: &std::collections::HashMap<String, String>,
) -> Result<Vec<LogicCondition>, Error> {
    let mut filters = Vec::new();

    for (key, value) in params {
        if parser::filter::reserved_key(key) {
            continue;
        }

        if parser::logic::logic_key(key) {
            let tree = parse_logic(key, value)?;
            filters.push(LogicCondition::Logic(tree));
        } else {
            let filter = parse_filter(key, value)?;
            filters.push(LogicCondition::Filter(filter));
        }
    }

    Ok(filters)
}

/// Parses filters from a list of key-value pairs.
///
/// Unlike parse_filters_from_map, this function processes pairs sequentially,
/// allowing multiple filters on the same column (e.g., price=gte.50&price=lte.150).
fn parse_filters_from_pairs(pairs: &[(String, String)]) -> Result<Vec<LogicCondition>, Error> {
    let mut filters = Vec::new();

    for (key, value) in pairs {
        if parser::filter::reserved_key(key) {
            continue;
        }

        if parser::logic::logic_key(key) {
            let tree = parse_logic(key, value)?;
            filters.push(LogicCondition::Logic(tree));
        } else {
            let filter = parse_filter(key, value)?;
            filters.push(LogicCondition::Filter(filter));
        }
    }

    Ok(filters)
}

/// Result of building a filter clause.
///
/// Contains the SQL WHERE clause fragment and associated parameter values.
#[derive(Debug, Clone, Serialize)]
#[serde(rename_all = "camelCase")]
pub struct FilterClauseResult {
    /// The WHERE clause SQL fragment (without the "WHERE" keyword)
    pub clause: String,
    /// Parameter values referenced in the clause
    pub params: Vec<serde_json::Value>,
}

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

    #[test]
    fn test_parse_query_string_empty() {
        let result = parse_query_string("");
        assert!(result.is_ok());
        let params = result.unwrap();
        assert!(params.is_empty());
    }

    #[test]
    fn test_parse_query_string_simple() {
        let result = parse_query_string("select=id,name&id=eq.1");
        assert!(result.is_ok());
        let params = result.unwrap();
        assert!(params.has_select());
        assert!(params.has_filters());
    }

    #[test]
    fn test_parse_query_string_with_order() {
        let result = parse_query_string("select=id&order=id.desc");
        assert!(result.is_ok());
        let params = result.unwrap();
        assert!(params.has_select());
        assert!(!params.order.is_empty());
    }

    #[test]
    fn test_parse_query_string_with_limit() {
        let result = parse_query_string("select=id&limit=10");
        assert!(result.is_ok());
        let params = result.unwrap();
        assert_eq!(params.limit, Some(10));
    }

    #[test]
    fn test_to_sql_simple() {
        let params = ParsedParams::new()
            .with_select(vec![SelectItem::field("id"), SelectItem::field("name")]);

        let result = to_sql("users", &params);
        assert!(result.is_ok());
        let query = result.unwrap();
        assert!(query.query.contains("SELECT"));
        assert!(query.query.contains("users"));
    }

    #[test]
    fn test_query_string_to_sql() {
        let result = query_string_to_sql("users", "select=id,name");
        assert!(result.is_ok());
        let query = result.unwrap();
        assert!(query.query.contains("SELECT"));
        assert!(query.query.contains("users"));
        assert_eq!(query.tables, vec!["users"]);
    }

    #[test]
    fn test_build_filter_clause() {
        let filter = LogicCondition::Filter(Filter::new(
            Field::new("id"),
            FilterOperator::Eq,
            FilterValue::Single("1".to_string()),
        ));

        let result = build_filter_clause(&[filter]);
        assert!(result.is_ok());
        let clause = result.unwrap();
        assert!(clause.clause.contains("\"id\""));
        assert!(clause.clause.contains("="));
    }

    #[test]
    fn test_complex_query_with_multiple_filters() {
        let query_str = "select=id,name,email&age=gte.18&status=in.(active,pending)&order=created_at.desc&limit=10";
        let result = parse_query_string(query_str);
        assert!(result.is_ok());
        let params = result.unwrap();

        assert!(params.has_select());
        assert!(params.has_filters());
        assert_eq!(params.filters.len(), 2);
        assert_eq!(params.order.len(), 1);
        assert_eq!(params.limit, Some(10));
    }

    #[test]
    fn test_query_with_logic_operators() {
        let query_str = "and=(age.gte.18,status.eq.active)";
        let result = parse_query_string(query_str);
        assert!(result.is_ok());
        let params = result.unwrap();
        assert!(params.has_filters());
    }

    #[test]
    fn test_query_with_json_path() {
        let query_str = "data->name=eq.John&data->age=gt.25";
        let result = parse_query_string(query_str);
        assert!(result.is_ok());
        let params = result.unwrap();
        assert_eq!(params.filters.len(), 2);
    }

    #[test]
    fn test_query_with_type_cast() {
        let query_str = "price::numeric=gt.100";
        let result = parse_query_string(query_str);
        assert!(result.is_ok());
        let params = result.unwrap();
        assert_eq!(params.filters.len(), 1);
    }

    #[test]
    fn test_query_to_sql_with_comparison_operators() {
        let query_str = "age=gte.18&price=lte.100";
        let result = query_string_to_sql("users", query_str);
        assert!(result.is_ok());
        let query = result.unwrap();
        assert!(query.query.contains(">="));
        assert!(query.query.contains("<="));
        assert_eq!(query.params.len(), 2);
    }

    #[test]
    fn test_multiple_filters_same_column() {
        // Test that multiple filters on the same column are ALL applied
        let query_str = "price=gte.50&price=lte.150";
        let params = parse_query_string(query_str).unwrap();

        // Should have 2 filters, not 1 (bug was overwriting with HashMap)
        assert_eq!(params.filters.len(), 2, "Should have both filters");

        // Verify SQL generation includes both conditions
        let result = query_string_to_sql("products", query_str).unwrap();
        assert!(result.query.contains(">="), "Should have >= operator");
        assert!(result.query.contains("<="), "Should have <= operator");
        assert_eq!(result.params.len(), 2, "Should have 2 parameter values");

        // Verify both conditions are in WHERE clause (AND logic)
        assert!(result.query.contains("WHERE"));
        assert!(result.query.contains("AND") || result.query.matches("price").count() == 2);
    }

    #[test]
    fn test_query_to_sql_with_fts() {
        let query_str = "content=fts(english).search term";
        let result = query_string_to_sql("articles", query_str);
        assert!(result.is_ok());
        let query = result.unwrap();
        assert!(query.query.contains("to_tsvector"));
        assert!(query.query.contains("plainto_tsquery"));
        assert!(query.query.contains("english"));
    }

    #[test]
    fn test_query_to_sql_with_array_operators() {
        let query_str = "tags=cs.{rust}";
        let result = query_string_to_sql("posts", query_str);
        assert!(result.is_ok());
        let query = result.unwrap();
        assert!(query.query.contains("@>"));
    }

    #[test]
    fn test_query_to_sql_with_negation() {
        let query_str = "status=not.eq.deleted";
        let result = query_string_to_sql("users", query_str);
        assert!(result.is_ok());
        let query = result.unwrap();
        assert!(query.query.contains("<>"));
    }

    #[test]
    fn test_complex_nested_query() {
        let query_str = "select=id,name,orders(id,total)&status=eq.active&age=gte.18&order=created_at.desc&limit=10&offset=20";
        let result = parse_query_string(query_str);
        assert!(result.is_ok());
        let params = result.unwrap();

        assert!(params.has_select());
        assert_eq!(params.filters.len(), 2);
        assert_eq!(params.order.len(), 1);
        assert_eq!(params.limit, Some(10));
        assert_eq!(params.offset, Some(20));
    }

    #[test]
    fn test_query_with_quantifiers() {
        let query_str = "tags=eq(any).{rust,elixir,go}";
        let result = query_string_to_sql("posts", query_str);
        assert!(result.is_ok());
        let query = result.unwrap();
        assert!(query.query.contains("= ANY"));
    }

    // Prefer header tests - Real-world scenarios

    #[test]
    fn test_insert_with_return_representation() {
        // Real-world: User signup returning full user object
        use std::collections::HashMap;
        let mut headers = HashMap::new();
        headers.insert("Prefer".to_string(), "return=representation".to_string());

        let body = r#"{"email": "alice@example.com", "name": "Alice"}"#;
        let op = parse("POST", "users", "", Some(body), Some(&headers)).unwrap();

        match op {
            Operation::Insert(_, Some(prefer)) => {
                assert_eq!(
                    prefer.return_representation,
                    Some(ReturnRepresentation::Full)
                );
            }
            _ => panic!("Expected Insert operation with Prefer"),
        }
    }

    #[test]
    fn test_insert_with_minimal_return() {
        // Real-world: Bulk insert with minimal response
        use std::collections::HashMap;
        let mut headers = HashMap::new();
        headers.insert("Prefer".to_string(), "return=minimal".to_string());

        let body = r#"[{"name": "Alice"}, {"name": "Bob"}]"#;
        let op = parse("POST", "users", "", Some(body), Some(&headers)).unwrap();

        match op {
            Operation::Insert(_, Some(prefer)) => {
                assert_eq!(
                    prefer.return_representation,
                    Some(ReturnRepresentation::Minimal)
                );
            }
            _ => panic!("Expected Insert with minimal return"),
        }
    }

    #[test]
    fn test_upsert_with_merge_duplicates() {
        // Real-world: Upsert user preferences
        use std::collections::HashMap;
        let mut headers = HashMap::new();
        headers.insert(
            "Prefer".to_string(),
            "resolution=merge-duplicates".to_string(),
        );

        let body = r#"{"user_id": 123, "theme": "dark"}"#;
        let op = parse(
            "POST",
            "preferences",
            "on_conflict=user_id",
            Some(body),
            Some(&headers),
        )
        .unwrap();

        match op {
            Operation::Insert(params, Some(prefer)) => {
                assert_eq!(prefer.resolution, Some(Resolution::MergeDuplicates));
                assert!(params.on_conflict.is_some());
            }
            _ => panic!("Expected Insert with resolution preference"),
        }
    }

    #[test]
    fn test_select_with_count_exact() {
        // Real-world: Pagination with total count
        use std::collections::HashMap;
        let mut headers = HashMap::new();
        headers.insert("Prefer".to_string(), "count=exact".to_string());

        let op = parse("GET", "users", "limit=10&offset=0", None, Some(&headers)).unwrap();

        match op {
            Operation::Select(_, Some(prefer)) => {
                assert_eq!(prefer.count, Some(Count::Exact));
            }
            _ => panic!("Expected Select with count"),
        }
    }

    #[test]
    fn test_multiple_prefer_options() {
        // Real-world: Complex mutation with multiple preferences
        use std::collections::HashMap;
        let mut headers = HashMap::new();
        headers.insert(
            "Prefer".to_string(),
            "return=representation, missing=default, plurality=singular".to_string(),
        );

        let body = r#"{"name": "Bob"}"#;
        let op = parse("POST", "users", "", Some(body), Some(&headers)).unwrap();

        match op {
            Operation::Insert(_, Some(prefer)) => {
                assert_eq!(
                    prefer.return_representation,
                    Some(ReturnRepresentation::Full)
                );
                assert_eq!(prefer.missing, Some(Missing::Default));
                assert_eq!(prefer.plurality, Some(Plurality::Singular));
            }
            _ => panic!("Expected Insert with multiple preferences"),
        }
    }

    #[test]
    fn test_update_with_prefer_headers() {
        // Real-world: Update with return preference
        use std::collections::HashMap;
        let mut headers = HashMap::new();
        headers.insert("Prefer".to_string(), "return=representation".to_string());

        let body = r#"{"status": "active"}"#;
        let op = parse("PATCH", "users", "id=eq.123", Some(body), Some(&headers)).unwrap();

        match op {
            Operation::Update(_, Some(prefer)) => {
                assert_eq!(
                    prefer.return_representation,
                    Some(ReturnRepresentation::Full)
                );
            }
            _ => panic!("Expected Update with Prefer"),
        }
    }

    #[test]
    fn test_delete_with_prefer_headers() {
        // Real-world: Delete with headers-only return
        use std::collections::HashMap;
        let mut headers = HashMap::new();
        headers.insert("Prefer".to_string(), "return=headers-only".to_string());

        let op = parse("DELETE", "users", "status=eq.deleted", None, Some(&headers)).unwrap();

        match op {
            Operation::Delete(_, Some(prefer)) => {
                assert_eq!(
                    prefer.return_representation,
                    Some(ReturnRepresentation::HeadersOnly)
                );
            }
            _ => panic!("Expected Delete with Prefer"),
        }
    }

    #[test]
    fn test_prefer_header_case_insensitive() {
        // Test that Prefer header is case-insensitive
        use std::collections::HashMap;
        let mut headers = HashMap::new();
        headers.insert("prefer".to_string(), "count=exact".to_string());

        let op = parse("GET", "users", "", None, Some(&headers)).unwrap();

        match op {
            Operation::Select(_, Some(prefer)) => {
                assert_eq!(prefer.count, Some(Count::Exact));
            }
            _ => panic!("Expected Select with Prefer"),
        }
    }

    #[test]
    fn test_no_prefer_headers() {
        // Test operation without Prefer headers
        let op = parse("GET", "users", "id=eq.123", None, None).unwrap();

        match op {
            Operation::Select(_, prefer) => {
                assert!(prefer.is_none());
            }
            _ => panic!("Expected Select without Prefer"),
        }
    }

    #[test]
    fn test_prefer_with_schema_headers() {
        // Real-world: Combine Prefer with Content-Profile
        use std::collections::HashMap;
        let mut headers = HashMap::new();
        headers.insert("Prefer".to_string(), "return=representation".to_string());
        headers.insert("Content-Profile".to_string(), "auth".to_string());

        let body = r#"{"email": "alice@example.com"}"#;
        let op = parse("POST", "users", "", Some(body), Some(&headers)).unwrap();

        match op {
            Operation::Insert(_, Some(prefer)) => {
                assert_eq!(
                    prefer.return_representation,
                    Some(ReturnRepresentation::Full)
                );
            }
            _ => panic!("Expected Insert with both Prefer and schema headers"),
        }
    }

    // RPC Integration Tests

    #[test]
    fn test_rpc_post_with_args() {
        // Real-world: Call stored procedure with arguments
        let body = r#"{"user_id": 123, "status": "active"}"#;
        let op = parse("POST", "rpc/get_user_posts", "", Some(body), None).unwrap();

        match op {
            Operation::Rpc(params, prefer) => {
                assert_eq!(params.function_name, "get_user_posts");
                assert_eq!(params.args.len(), 2);
                assert!(prefer.is_none());
            }
            _ => panic!("Expected RPC operation"),
        }
    }

    #[test]
    fn test_rpc_get_no_args() {
        // Real-world: Health check or utility function
        let op = parse("GET", "rpc/health_check", "", None, None).unwrap();

        match op {
            Operation::Rpc(params, _) => {
                assert_eq!(params.function_name, "health_check");
                assert!(params.args.is_empty());
            }
            _ => panic!("Expected RPC operation"),
        }
    }

    #[test]
    fn test_rpc_with_filters() {
        // Real-world: Call function and filter results
        let body = r#"{"department_id": 5}"#;
        let query = "age=gte.25&salary=lt.100000";
        let op = parse("POST", "rpc/find_employees", query, Some(body), None).unwrap();

        match op {
            Operation::Rpc(params, _) => {
                assert_eq!(params.function_name, "find_employees");
                assert_eq!(params.filters.len(), 2);
            }
            _ => panic!("Expected RPC operation"),
        }
    }

    #[test]
    fn test_rpc_with_order_limit() {
        // Real-world: Paginated function results
        let query = "order=created_at.desc&limit=10&offset=20";
        let op = parse("GET", "rpc/list_recent_posts", query, None, None).unwrap();

        match op {
            Operation::Rpc(params, _) => {
                assert_eq!(params.function_name, "list_recent_posts");
                assert_eq!(params.order.len(), 1);
                assert_eq!(params.limit, Some(10));
                assert_eq!(params.offset, Some(20));
            }
            _ => panic!("Expected RPC operation"),
        }
    }

    #[test]
    fn test_rpc_with_select() {
        // Real-world: Select specific columns from function results
        let body = r#"{"search_term": "laptop"}"#;
        let query = "select=id,name,price";
        let op = parse("POST", "rpc/search_products", query, Some(body), None).unwrap();

        match op {
            Operation::Rpc(params, _) => {
                assert_eq!(params.function_name, "search_products");
                assert!(params.returning.is_some());
                assert_eq!(params.returning.unwrap().len(), 3);
            }
            _ => panic!("Expected RPC operation"),
        }
    }

    #[test]
    fn test_rpc_with_prefer_headers() {
        // Real-world: RPC with Prefer header for response preferences
        use std::collections::HashMap;
        let mut headers = HashMap::new();
        headers.insert("Prefer".to_string(), "return=representation".to_string());

        let body = r#"{"amount": 100.50}"#;
        let op = parse(
            "POST",
            "rpc/process_payment",
            "",
            Some(body),
            Some(&headers),
        )
        .unwrap();

        match op {
            Operation::Rpc(params, Some(prefer)) => {
                assert_eq!(params.function_name, "process_payment");
                assert_eq!(
                    prefer.return_representation,
                    Some(ReturnRepresentation::Full)
                );
            }
            _ => panic!("Expected RPC operation with Prefer header"),
        }
    }

    #[test]
    fn test_rpc_to_sql_simple() {
        // Real-world: Generate SQL for simple function call
        let body = r#"{"user_id": 42}"#;
        let op = parse("POST", "rpc/get_profile", "", Some(body), None).unwrap();
        let result = operation_to_sql("rpc/get_profile", &op).unwrap();

        assert!(result.query.contains(r#"FROM "public"."get_profile"("#));
        assert!(result.query.contains(r#""user_id" := $1"#));
        assert_eq!(result.params.len(), 1);
    }

    #[test]
    fn test_rpc_to_sql_with_schema() {
        // Real-world: Function in custom schema using qualified name
        let body = r#"{"query": "test"}"#;
        let op = parse("POST", "rpc/api.search", "", Some(body), None).unwrap();
        let result = operation_to_sql("rpc/api.search", &op).unwrap();

        assert!(result.query.contains(r#"FROM "api"."search"("#));
    }

    #[test]
    fn test_rpc_to_sql_complex() {
        // Real-world: Complex function call with all features
        let body = r#"{"min_price": 100, "max_price": 1000}"#;
        let query = "category=eq.electronics&in_stock=eq.true&order=price.asc&limit=20&select=id,name,price";
        let op = parse("POST", "rpc/find_products", query, Some(body), None).unwrap();
        let result = operation_to_sql("rpc/find_products", &op).unwrap();

        assert!(result.query.contains(r#"FROM "public"."find_products"("#));
        assert!(result.query.contains(r#""max_price" := $1"#));
        assert!(result.query.contains(r#""min_price" := $2"#));
        assert!(result.query.contains("WHERE"));
        assert!(result.query.contains("ORDER BY"));
        assert!(result.query.contains("LIMIT"));
        assert!(result.params.len() > 2);
    }

    #[test]
    fn test_rpc_invalid_empty_function_name() {
        // Edge case: Empty function name
        let result = parse("POST", "rpc/", "", None, None);
        assert!(result.is_err());
    }

    #[test]
    fn test_rpc_get_with_query_params() {
        // Real-world: GET request with query parameters (args in query string)
        // Note: This is less common but supported
        let query = "limit=5";
        let op = parse("GET", "rpc/get_stats", query, None, None).unwrap();

        match op {
            Operation::Rpc(params, _) => {
                assert_eq!(params.function_name, "get_stats");
                assert_eq!(params.limit, Some(5));
            }
            _ => panic!("Expected RPC operation"),
        }
    }

    // Phase 5: Resource Embedding Tests

    #[test]
    fn test_insert_with_select_parameter() {
        // Real-world: Insert and return specific columns using 'select' parameter
        let body = r#"{"email": "bob@example.com", "name": "Bob"}"#;
        let query = "select=id,email,created_at";
        let op = parse("POST", "users", query, Some(body), None).unwrap();

        match op {
            Operation::Insert(params, _) => {
                assert!(params.returning.is_some());
                let returning = params.returning.unwrap();
                assert_eq!(returning.len(), 3);
                assert_eq!(returning[0].name, "id");
                assert_eq!(returning[1].name, "email");
                assert_eq!(returning[2].name, "created_at");
            }
            _ => panic!("Expected Insert with select"),
        }
    }

    #[test]
    fn test_update_with_select_parameter() {
        // Real-world: Update and return specific columns
        let body = r#"{"status": "verified"}"#;
        let query = "id=eq.123&select=id,status,updated_at";
        let op = parse("PATCH", "users", query, Some(body), None).unwrap();

        match op {
            Operation::Update(params, _) => {
                assert!(params.returning.is_some());
                let returning = params.returning.unwrap();
                assert_eq!(returning.len(), 3);
            }
            _ => panic!("Expected Update with select"),
        }
    }

    #[test]
    fn test_delete_with_select_parameter() {
        // Real-world: Delete and return deleted rows
        let query = "status=eq.inactive&select=id,email";
        let op = parse("DELETE", "users", query, None, None).unwrap();

        match op {
            Operation::Delete(params, _) => {
                assert!(params.returning.is_some());
                let returning = params.returning.unwrap();
                assert_eq!(returning.len(), 2);
            }
            _ => panic!("Expected Delete with select"),
        }
    }

    #[test]
    fn test_insert_with_returning_backwards_compat() {
        // Backwards compatibility: 'returning' parameter still works
        let body = r#"{"email": "alice@example.com"}"#;
        let query = "returning=id,created_at";
        let op = parse("POST", "users", query, Some(body), None).unwrap();

        match op {
            Operation::Insert(params, _) => {
                assert!(params.returning.is_some());
                assert_eq!(params.returning.unwrap().len(), 2);
            }
            _ => panic!("Expected Insert with returning"),
        }
    }

    #[test]
    fn test_select_takes_precedence_over_returning() {
        // Real-world: If both 'select' and 'returning' are provided, 'select' wins
        let body = r#"{"email": "test@example.com"}"#;
        let query = "select=id&returning=id,email,name";
        let op = parse("POST", "users", query, Some(body), None).unwrap();

        match op {
            Operation::Insert(params, _) => {
                assert!(params.returning.is_some());
                let returning = params.returning.unwrap();
                // Should use 'select' parameter, which has only 'id'
                assert_eq!(returning.len(), 1);
                assert_eq!(returning[0].name, "id");
            }
            _ => panic!("Expected Insert"),
        }
    }

    #[test]
    fn test_mutation_select_to_sql() {
        // Real-world: Verify SQL generation with select parameter
        let body = r#"{"name": "New Product", "price": 99.99}"#;
        let query = "select=id,name,created_at";
        let op = parse("POST", "products", query, Some(body), None).unwrap();
        let result = operation_to_sql("products", &op).unwrap();

        assert!(result.query.contains("RETURNING"));
        assert!(result.query.contains(r#""id""#));
        assert!(result.query.contains(r#""name""#));
        assert!(result.query.contains(r#""created_at""#));
    }

    // Phase 6: PUT Upsert Tests

    #[test]
    fn test_put_upsert_basic() {
        // Real-world: PUT upserts based on query filter columns
        let body = r#"{"email": "alice@example.com", "name": "Alice Updated"}"#;
        let query = "email=eq.alice@example.com";
        let op = parse("PUT", "users", query, Some(body), None).unwrap();

        match op {
            Operation::Insert(params, _) => {
                assert!(params.on_conflict.is_some());
                let conflict = params.on_conflict.unwrap();
                assert_eq!(conflict.columns, vec!["email"]);
                assert_eq!(conflict.action, ConflictAction::DoUpdate);
            }
            _ => panic!("Expected Insert (upsert) operation"),
        }
    }

    #[test]
    fn test_put_upsert_multiple_columns() {
        // Real-world: Upsert with multiple conflict columns
        let body = r#"{"email": "bob@example.com", "team": "engineering", "role": "senior"}"#;
        let query = "email=eq.bob@example.com&team=eq.engineering";
        let op = parse("PUT", "users", query, Some(body), None).unwrap();

        match op {
            Operation::Insert(params, _) => {
                assert!(params.on_conflict.is_some());
                let conflict = params.on_conflict.unwrap();
                assert_eq!(conflict.columns.len(), 2);
                assert!(conflict.columns.contains(&"email".to_string()));
                assert!(conflict.columns.contains(&"team".to_string()));
            }
            _ => panic!("Expected Insert with multi-column conflict"),
        }
    }

    #[test]
    fn test_put_with_explicit_on_conflict() {
        // Real-world: PUT with explicit ON CONFLICT overrides auto-detection
        let body = r#"{"id": 123, "name": "Test"}"#;
        let query = "id=eq.123&on_conflict=id";
        let op = parse("PUT", "items", query, Some(body), None).unwrap();

        match op {
            Operation::Insert(params, _) => {
                assert!(params.on_conflict.is_some());
                // Explicit on_conflict from query string should be used
                let conflict = params.on_conflict.unwrap();
                assert_eq!(conflict.columns, vec!["id"]);
            }
            _ => panic!("Expected Insert"),
        }
    }

    #[test]
    fn test_put_without_filters() {
        // Edge case: PUT without filters doesn't add ON CONFLICT
        let body = r#"{"name": "New Item"}"#;
        let op = parse("PUT", "items", "", Some(body), None).unwrap();

        match op {
            Operation::Insert(params, _) => {
                // No conflict columns from filters, so no ON CONFLICT
                assert!(params.on_conflict.is_none());
            }
            _ => panic!("Expected Insert"),
        }
    }

    #[test]
    fn test_put_to_sql() {
        // Real-world: Verify PUT generates proper upsert SQL
        let body = r#"{"email": "test@example.com", "name": "Test User"}"#;
        let query = "email=eq.test@example.com&select=id,email,name";
        let op = parse("PUT", "users", query, Some(body), None).unwrap();
        let result = operation_to_sql("users", &op).unwrap();

        assert!(result.query.contains("INSERT INTO"));
        assert!(result.query.contains("ON CONFLICT"));
        assert!(result.query.contains("DO UPDATE SET"));
        assert!(result.query.contains("RETURNING"));
    }

    #[test]
    fn test_put_requires_body() {
        // Error case: PUT without body should fail
        let result = parse("PUT", "users", "id=eq.123", None, None);
        assert!(result.is_err());
    }

    // Phase 7: Advanced ON CONFLICT Tests

    #[test]
    fn test_on_conflict_with_where_clause() {
        // Real-world: Partial unique index with WHERE clause
        use crate::parser::parse_filter;

        let body = r#"{"email": "alice@example.com", "name": "Alice"}"#;
        let mut params = parse_insert_params("", body).unwrap();

        // Manually create advanced ON CONFLICT (parser extension would go here)
        let filter = parse_filter("deleted_at", "is.null").unwrap();
        let conflict = OnConflict::do_update(vec!["email".to_string()])
            .with_where_clause(vec![LogicCondition::Filter(filter)]);

        params = params.with_on_conflict(conflict);
        let op = Operation::Insert(params, None);
        let result = operation_to_sql("users", &op).unwrap();

        assert!(result.query.contains("ON CONFLICT"));
        assert!(result.query.contains(r#"("email")"#));
        assert!(result.query.contains("WHERE"));
        assert!(result.query.contains("deleted_at"));
    }

    #[test]
    fn test_on_conflict_with_specific_update_columns() {
        // Real-world: Only update specific columns on conflict
        let body = r#"{"email": "bob@example.com", "name": "Bob", "role": "admin"}"#;
        let mut params = parse_insert_params("", body).unwrap();

        // Create ON CONFLICT that only updates 'name' column, not 'role'
        let conflict = OnConflict::do_update(vec!["email".to_string()])
            .with_update_columns(vec!["name".to_string()]);

        params = params.with_on_conflict(conflict);
        let op = Operation::Insert(params, None);
        let result = operation_to_sql("users", &op).unwrap();

        assert!(result.query.contains("ON CONFLICT"));
        assert!(result.query.contains(r#""name" = EXCLUDED."name""#));
        // Role should NOT be in the update
        assert!(!result.query.contains(r#""role" = EXCLUDED."role""#));
    }

    #[test]
    fn test_on_conflict_complex() {
        // Real-world: Partial unique index with specific update columns
        use crate::parser::parse_filter;

        let body = r#"{"user_id": 123, "post_id": 456, "reaction": "like"}"#;
        let mut params = parse_insert_params("", body).unwrap();

        let filter = parse_filter("deleted_at", "is.null").unwrap();
        let conflict = OnConflict::do_update(vec!["user_id".to_string(), "post_id".to_string()])
            .with_where_clause(vec![LogicCondition::Filter(filter)])
            .with_update_columns(vec!["reaction".to_string()]);

        params = params.with_on_conflict(conflict);
        let op = Operation::Insert(params, None);
        let result = operation_to_sql("reactions", &op).unwrap();

        // Columns might be in either order
        println!("SQL: {}", result.query);
        assert!(
            result
                .query
                .contains(r#"ON CONFLICT ("post_id", "user_id")"#)
                || result
                    .query
                    .contains(r#"ON CONFLICT ("user_id", "post_id")"#)
        );
        assert!(result.query.contains("WHERE"));
        assert!(result.query.contains(r#""reaction" = EXCLUDED."reaction""#));
    }

    // Real-World Scenario Tests (100% Parity Demonstration)

    #[test]
    fn test_ecommerce_workflow() {
        use std::collections::HashMap;

        // 1. Bulk insert order items with return representation
        let body = r#"[
            {"product_id": 1, "quantity": 2, "price": 29.99},
            {"product_id": 3, "quantity": 1, "price": 49.99}
        ]"#;
        let mut headers = HashMap::new();
        headers.insert("Prefer".to_string(), "return=representation".to_string());
        headers.insert("Content-Profile".to_string(), "sales".to_string());

        let op = parse(
            "POST",
            "order_items",
            "select=*",
            Some(body),
            Some(&headers),
        )
        .unwrap();
        match op {
            Operation::Insert(params, Some(prefer)) => {
                assert_eq!(
                    prefer.return_representation,
                    Some(ReturnRepresentation::Full)
                );
                assert!(params.returning.is_some());
            }
            _ => panic!("Expected Insert with Prefer"),
        }

        // 2. Update order status with specific columns returned
        let body = r#"{"status": "shipped", "shipped_at": "2024-01-15"}"#;
        let op = parse(
            "PATCH",
            "orders",
            "id=eq.123&select=id,status,shipped_at",
            Some(body),
            None,
        )
        .unwrap();
        match op {
            Operation::Update(params, _) => {
                assert!(params.has_filters());
                assert!(params.returning.is_some());
            }
            _ => panic!("Expected Update"),
        }

        // 3. Calculate total with RPC
        let body = r#"{"order_id": 123}"#;
        let op = parse("POST", "rpc/calculate_order_total", "", Some(body), None).unwrap();
        match op {
            Operation::Rpc(params, _) => {
                assert_eq!(params.function_name, "calculate_order_total");
            }
            _ => panic!("Expected RPC"),
        }
    }

    #[test]
    fn test_social_media_workflow() {
        use std::collections::HashMap;

        // 1. Create post with embedded user data
        let body = r#"{"content": "Hello World!", "user_id": 456}"#;
        let mut headers = HashMap::new();
        headers.insert("Prefer".to_string(), "return=representation".to_string());

        let op = parse(
            "POST",
            "posts",
            "select=id,content,user_id",
            Some(body),
            Some(&headers),
        )
        .unwrap();
        match op {
            Operation::Insert(_, Some(prefer)) => {
                assert_eq!(
                    prefer.return_representation,
                    Some(ReturnRepresentation::Full)
                );
            }
            _ => panic!("Expected Insert"),
        }

        // 2. Upsert like with PUT
        let body = r#"{"user_id": 789, "post_id": 123}"#;
        let op = parse(
            "PUT",
            "likes",
            "user_id=eq.789&post_id=eq.123",
            Some(body),
            None,
        )
        .unwrap();
        match op {
            Operation::Insert(params, _) => {
                assert!(params.on_conflict.is_some());
            }
            _ => panic!("Expected upsert"),
        }

        // 3. Delete old posts with limit
        let op = parse(
            "DELETE",
            "posts",
            "created_at=lt.2020-01-01&order=created_at.asc&limit=100",
            None,
            None,
        )
        .unwrap();
        match op {
            Operation::Delete(params, _) => {
                assert!(params.has_filters());
                assert_eq!(params.limit, Some(100));
            }
            _ => panic!("Expected Delete"),
        }
    }

    #[test]
    fn test_analytics_workflow() {
        use std::collections::HashMap;

        // 1. Bulk upsert metrics with merge duplicates
        let body = r#"[
            {"metric": "pageviews", "value": 1234, "date": "2024-01-15"},
            {"metric": "signups", "value": 56, "date": "2024-01-15"}
        ]"#;
        let mut headers = HashMap::new();
        headers.insert(
            "Prefer".to_string(),
            "resolution=merge-duplicates".to_string(),
        );

        let op = parse(
            "POST",
            "metrics",
            "on_conflict=metric,date",
            Some(body),
            Some(&headers),
        )
        .unwrap();
        match op {
            Operation::Insert(params, Some(prefer)) => {
                assert!(params.on_conflict.is_some());
                assert_eq!(prefer.resolution, Some(Resolution::MergeDuplicates));
            }
            _ => panic!("Expected Insert with resolution"),
        }

        // 2. Get aggregated stats with RPC and filtering
        let body = r#"{"start_date": "2024-01-01", "end_date": "2024-01-31"}"#;
        let op = parse(
            "POST",
            "rpc/get_monthly_stats",
            "metric=eq.pageviews",
            Some(body),
            None,
        )
        .unwrap();
        match op {
            Operation::Rpc(params, _) => {
                assert_eq!(params.function_name, "get_monthly_stats");
                assert!(!params.filters.is_empty());
            }
            _ => panic!("Expected RPC with filters"),
        }

        // 3. Count with prefer header
        let mut headers = HashMap::new();
        headers.insert("Prefer".to_string(), "count=exact".to_string());

        let op = parse(
            "GET",
            "events",
            "created_at=gte.2024-01-01",
            None,
            Some(&headers),
        )
        .unwrap();
        match op {
            Operation::Select(_, Some(prefer)) => {
                assert_eq!(prefer.count, Some(Count::Exact));
            }
            _ => panic!("Expected Select with count"),
        }
    }

    // Resource Embedding Tests (PostgREST select with relations)

    #[test]
    fn test_embedding_many_to_one_via_fk() {
        let result = query_string_to_sql("posts", "select=*,profiles(username,avatar_url)");
        assert!(result.is_ok());
        let query = result.unwrap();
        assert!(query.query.contains("SELECT"));
        assert!(query.query.contains("profiles"));
        // row_to_json takes a single record, not individual columns
        assert!(
            !query.query.contains("row_to_json(profiles.\"username\""),
            "row_to_json must not receive individual columns: {}",
            query.query
        );
        assert!(
            query.query.contains("row_to_json("),
            "should use row_to_json with a subquery record: {}",
            query.query
        );
    }

    #[test]
    fn test_embedding_one_to_many() {
        let result = query_string_to_sql("posts", "select=title,comments(id,body)");
        assert!(result.is_ok());
        let query = result.unwrap();
        assert!(query.query.contains("\"title\""));
        assert!(query.query.contains("comments"));
        // row_to_json takes a single record, not individual columns
        assert!(
            !query.query.contains("row_to_json(comments.\"id\""),
            "row_to_json must not receive individual columns: {}",
            query.query
        );
    }

    #[test]
    fn test_embedding_select_star_produces_valid_row_to_json() {
        let result = query_string_to_sql("posts", "select=*,comments(*)");
        assert!(result.is_ok());
        let query = result.unwrap();
        assert!(
            query.query.contains("row_to_json("),
            "should use row_to_json: {}",
            query.query
        );
    }

    #[test]
    fn test_embedding_nested_produces_valid_sql() {
        let result = query_string_to_sql(
            "posts",
            "select=id,comments(id,body,author:profiles(name,avatar_url))",
        );
        assert!(result.is_ok());
        let query = result.unwrap();
        assert!(
            !query.query.contains("row_to_json(profiles.\"name\""),
            "nested row_to_json must not receive individual columns: {}",
            query.query
        );
    }

    #[test]
    fn test_embedding_aliased_relation() {
        // select("*, author:profiles(name)") — aliased relation
        let params = parse_query_string("select=*,author:profiles(name)").unwrap();
        let select = params.select.as_ref().unwrap();
        let relation = &select[1];
        assert_eq!(relation.name, "profiles");
        assert_eq!(relation.alias, Some("author".to_string()));
        assert_eq!(relation.item_type, ItemType::Relation);
    }

    #[test]
    fn test_embedding_nested_with_alias() {
        // select("*, comments(id, author:profiles(name))") — nested embedding with alias
        let params = parse_query_string("select=*,comments(id,author:profiles(name))").unwrap();
        let select = params.select.as_ref().unwrap();
        let comments = &select[1];
        assert_eq!(comments.name, "comments");
        let children = comments.children.as_ref().unwrap();
        assert_eq!(children[1].name, "profiles");
        assert_eq!(children[1].alias, Some("author".to_string()));
        assert_eq!(children[1].item_type, ItemType::Relation);
        let nested = children[1].children.as_ref().unwrap();
        assert_eq!(nested[0].name, "name");
    }

    #[test]
    fn test_embedding_fk_hint_disambiguation() {
        // select("*, author:profiles!author_id_fkey(name)") — FK hint
        let params = parse_query_string("select=*,author:profiles!author_id_fkey(name)").unwrap();
        let select = params.select.as_ref().unwrap();
        let relation = &select[1];
        assert_eq!(relation.name, "profiles");
        assert_eq!(relation.alias, Some("author".to_string()));
        assert!(relation.hint.is_some());
        assert_eq!(
            relation.hint,
            Some(ItemHint::Inner("author_id_fkey".to_string()))
        );
    }

    #[test]
    fn test_embedding_with_filters_and_ordering() {
        // Real-world: Select with embedding + filters + ordering
        let query_str = "select=id,title,author:profiles(name,avatar_url),comments(id,body)&status=eq.published&order=created_at.desc&limit=10";
        let params = parse_query_string(query_str).unwrap();

        assert!(params.has_select());
        let select = params.select.as_ref().unwrap();
        assert_eq!(select.len(), 4); // id, title, profiles (aliased as author), comments
        assert_eq!(select[2].alias, Some("author".to_string()));
        assert_eq!(select[3].name, "comments");

        assert!(params.has_filters());
        assert_eq!(params.order.len(), 1);
        assert_eq!(params.limit, Some(10));
    }

    #[test]
    fn test_embedding_supabase_blog_example() {
        // Real Supabase use case: Blog post with author and comments
        let query_str = "select=id,title,content,author:profiles!author_id_fkey(name,avatar_url),comments(id,body,created_at,commenter:profiles!commenter_id_fkey(name))&published=eq.true&order=created_at.desc&limit=20";
        let params = parse_query_string(query_str).unwrap();

        let select = params.select.as_ref().unwrap();
        assert_eq!(select.len(), 5); // id, title, content, author:profiles, comments

        // Author relation with FK hint
        let author = &select[3];
        assert_eq!(author.name, "profiles");
        assert_eq!(author.alias, Some("author".to_string()));
        assert_eq!(
            author.hint,
            Some(ItemHint::Inner("author_id_fkey".to_string()))
        );

        // Comments with nested commenter relation
        let comments = &select[4];
        assert_eq!(comments.name, "comments");
        let comment_children = comments.children.as_ref().unwrap();
        assert_eq!(comment_children.len(), 4); // id, body, created_at, commenter:profiles

        let commenter = &comment_children[3];
        assert_eq!(commenter.name, "profiles");
        assert_eq!(commenter.alias, Some("commenter".to_string()));
        assert_eq!(
            commenter.hint,
            Some(ItemHint::Inner("commenter_id_fkey".to_string()))
        );
    }

    #[test]
    fn test_100_percent_parity_demonstration() {
        // Comprehensive test demonstrating all PostgREST features
        use std::collections::HashMap;

        // Feature 1: Full mutation support (INSERT, UPDATE, DELETE, PUT)
        let body = r#"{"email": "test@example.com"}"#;
        assert!(parse("POST", "users", "", Some(body), None).is_ok());
        assert!(parse("PUT", "users", "id=eq.1", Some(body), None).is_ok());
        assert!(parse("PATCH", "users", "id=eq.1", Some(body), None).is_ok());
        assert!(parse("DELETE", "users", "id=eq.1", None, None).is_ok());

        // Feature 2: RPC function calls
        assert!(parse("POST", "rpc/my_function", "", Some(body), None).is_ok());
        assert!(parse("GET", "rpc/my_function", "", None, None).is_ok());

        // Feature 3: Prefer headers (all 5 types)
        let mut headers = HashMap::new();
        headers.insert(
            "Prefer".to_string(),
            "return=representation, count=exact, resolution=merge-duplicates, plurality=singular, missing=default".to_string(),
        );
        let op = parse("GET", "users", "", None, Some(&headers)).unwrap();
        match op {
            Operation::Select(_, Some(prefer)) => {
                assert_eq!(
                    prefer.return_representation,
                    Some(ReturnRepresentation::Full)
                );
                assert_eq!(prefer.count, Some(Count::Exact));
                assert_eq!(prefer.resolution, Some(Resolution::MergeDuplicates));
                assert_eq!(prefer.plurality, Some(Plurality::Singular));
                assert_eq!(prefer.missing, Some(Missing::Default));
            }
            _ => panic!("Expected all prefer options"),
        }

        // Feature 4: Schema qualification via headers
        let mut headers = HashMap::new();
        headers.insert("Accept-Profile".to_string(), "api".to_string());
        assert!(parse("GET", "users", "", None, Some(&headers)).is_ok());

        // Feature 5: Advanced filtering, ordering, pagination
        assert!(parse(
            "GET",
            "users",
            "age=gte.18&status=in.(active,verified)&order=created_at.desc&limit=10&offset=20&select=id,name",
            None,
            None
        )
        .is_ok());

        // Feature 6: ON CONFLICT (basic and advanced)
        assert!(parse("POST", "users", "on_conflict=email", Some(body), None).is_ok());

        println!("✅ 100% PostgREST Parity Achieved!");
    }

    #[cfg(any(feature = "postgres", feature = "wasm"))]
    mod operation_to_sql_with_cache_tests {
        use super::*;
        use crate::schema_cache::ForeignKey;

        #[test]
        fn test_with_cache_none_matches_without_cache() {
            let op = parse("GET", "users", "id=eq.1", None, None).unwrap();

            let with_cache = operation_to_sql_with_cache("users", &op, None).unwrap();
            let without_cache = operation_to_sql("users", &op).unwrap();

            assert_eq!(with_cache.query, without_cache.query);
            assert_eq!(with_cache.params, without_cache.params);
            assert_eq!(with_cache.tables, without_cache.tables);
        }

        #[test]
        fn test_with_cache_select() {
            let cache = std::sync::Arc::new(
                crate::schema_cache::SchemaCache::from_foreign_keys(vec![]),
            );
            let op = parse("GET", "users", "age=gte.18&limit=5", None, None).unwrap();
            let result =
                operation_to_sql_with_cache("users", &op, Some(cache)).unwrap();

            assert!(result.query.contains("SELECT"));
            assert!(result.query.contains("WHERE"));
            assert!(result.query.contains("LIMIT"));
        }

        #[test]
        fn test_with_cache_insert() {
            let cache = std::sync::Arc::new(
                crate::schema_cache::SchemaCache::from_foreign_keys(vec![]),
            );
            let body = r#"{"name":"Alice"}"#;
            let op = parse("POST", "users", "", Some(body), None).unwrap();
            let result =
                operation_to_sql_with_cache("users", &op, Some(cache)).unwrap();

            assert!(result.query.contains("INSERT"));
        }

        #[test]
        fn test_with_cache_update() {
            let cache = std::sync::Arc::new(
                crate::schema_cache::SchemaCache::from_foreign_keys(vec![]),
            );
            let body = r#"{"status":"active"}"#;
            let op = parse("PATCH", "users", "id=eq.1", Some(body), None).unwrap();
            let result =
                operation_to_sql_with_cache("users", &op, Some(cache)).unwrap();

            assert!(result.query.contains("UPDATE"));
        }

        #[test]
        fn test_with_cache_delete() {
            let cache = std::sync::Arc::new(
                crate::schema_cache::SchemaCache::from_foreign_keys(vec![]),
            );
            let op = parse("DELETE", "users", "id=eq.1", None, None).unwrap();
            let result =
                operation_to_sql_with_cache("users", &op, Some(cache)).unwrap();

            assert!(result.query.contains("DELETE"));
        }

        #[test]
        fn test_with_cache_rpc() {
            let cache = std::sync::Arc::new(
                crate::schema_cache::SchemaCache::from_foreign_keys(vec![]),
            );
            let body = r#"{"user_id": 1}"#;
            let op =
                parse("POST", "rpc/get_profile", "", Some(body), None).unwrap();
            let result = operation_to_sql_with_cache(
                "rpc/get_profile",
                &op,
                Some(cache),
            )
            .unwrap();

            assert!(result.query.contains("get_profile"));
        }

        #[test]
        fn test_with_cache_empty_table_errors() {
            let op = parse("GET", "users", "id=eq.1", None, None).unwrap();
            let result = operation_to_sql_with_cache("", &op, None);
            assert!(result.is_err());
        }

        #[test]
        fn test_with_cache_resolves_many_to_one_relation() {
            let fks = vec![ForeignKey::test("orders", "customer_id", "customers", "id")];
            let cache = std::sync::Arc::new(
                crate::schema_cache::SchemaCache::from_foreign_keys(fks),
            );

            // select=id,customers(name) on orders table — Many-to-One embed
            let op = parse(
                "GET",
                "orders",
                "select=id,customers(name)",
                None,
                None,
            )
            .unwrap();
            let result =
                operation_to_sql_with_cache("orders", &op, Some(cache)).unwrap();

            // Should produce a correlated subquery for the M2O relation
            assert!(result.query.contains("customers"));
            assert!(result.query.contains("customer_id"));
        }

        #[test]
        fn test_with_cache_resolves_one_to_many_relation() {
            let fks = vec![ForeignKey::test("orders", "customer_id", "customers", "id")];
            let cache = std::sync::Arc::new(
                crate::schema_cache::SchemaCache::from_foreign_keys(fks),
            );

            // select=id,orders(id,total) on customers table — One-to-Many embed
            let op = parse(
                "GET",
                "customers",
                "select=id,orders(id)",
                None,
                None,
            )
            .unwrap();
            let result =
                operation_to_sql_with_cache("customers", &op, Some(cache))
                    .unwrap();

            // Should produce a json_agg subquery for the O2M relation
            assert!(result.query.contains("orders"));
            assert!(result.query.contains("json_agg"));
        }

        #[test]
        fn test_with_cache_relation_not_found_errors() {
            let cache = std::sync::Arc::new(
                crate::schema_cache::SchemaCache::from_foreign_keys(vec![]),
            );

            // Try to embed a relation that doesn't exist in the cache
            let op = parse(
                "GET",
                "orders",
                "select=id,nonexistent(name)",
                None,
                None,
            )
            .unwrap();
            let result =
                operation_to_sql_with_cache("orders", &op, Some(cache));

            assert!(result.is_err());
        }

        #[test]
        fn test_without_cache_relation_uses_placeholder() {
            // Without a cache, relations produce placeholder SQL
            let op = parse(
                "GET",
                "orders",
                "select=id,customers(name)",
                None,
                None,
            )
            .unwrap();
            let result = operation_to_sql_with_cache("orders", &op, None).unwrap();

            // Should still produce SQL (placeholder mode), not error
            assert!(result.query.contains("SELECT"));
        }
    }
}