rustrails-record 0.1.2

use std::collections::HashMap;

use rustrails_support::{database, runtime};
use sea_orm::{ActiveModelTrait, ActiveValue::Set, ConnectionTrait, Schema};
use serde_json::{Value, json};

use crate::base::test_support::{TestUser, seed_users, test_user};
use crate::{OrderDirection, Querying, Relation};

fn run_calculation_test(seed: bool, test: impl FnOnce() + Send + 'static) {
    std::thread::spawn(move || {
        let _rt = runtime::init_runtime();
        database::establish("sqlite::memory:").expect("sqlite in-memory connection should succeed");
        runtime::block_on(async {
            let db = database::db();
            let schema = Schema::new(db.get_database_backend());
            db.execute(&schema.create_table_from_entity(test_user::Entity))
                .await
                .expect("test_users table should be created");
            if seed {
                seed_users(&db).await;
            }
        });
        test();
    })
    .join()
    .unwrap();
}

fn run_seeded_calculation_test(test: impl FnOnce() + Send + 'static) {
    run_calculation_test(true, test);
}

fn run_empty_calculation_test(test: impl FnOnce() + Send + 'static) {
    run_calculation_test(false, test);
}

fn insert_user(name: &str, email: &str) {
    database::with_db(|db| {
        runtime::block_on(async {
            test_user::ActiveModel {
                name: Set(name.to_owned()),
                email: Set(email.to_owned()),
                ..Default::default()
            }
            .insert(db)
            .await
            .expect("fixture insert should succeed");
        });
    });
}

fn load_relation(relation: Relation<TestUser>) -> Result<Vec<TestUser>, crate::RecordError> {
    relation.load_sync()
}

fn pick_relation(
    relation: Relation<TestUser>,
    column: &str,
) -> Result<Option<Value>, crate::RecordError> {
    relation.pick_sync(column)
}

fn pluck_columns_relation(
    relation: Relation<TestUser>,
    columns: &[&str],
) -> Result<Vec<Vec<Value>>, crate::RecordError> {
    relation.pluck_columns_sync(columns)
}

fn group_count_relation(
    relation: Relation<TestUser>,
) -> Result<HashMap<Value, Value>, crate::RecordError> {
    database::with_db(|db| runtime::block_on(relation.group_count(db)))
}

fn group_sum_relation(
    relation: Relation<TestUser>,
    column: &str,
) -> Result<HashMap<Value, Value>, crate::RecordError> {
    database::with_db(|db| runtime::block_on(relation.group_sum(column, db)))
}

fn group_average_relation(
    relation: Relation<TestUser>,
    column: &str,
) -> Result<HashMap<Value, Value>, crate::RecordError> {
    database::with_db(|db| runtime::block_on(relation.group_average(column, db)))
}

fn group_minimum_relation(
    relation: Relation<TestUser>,
    column: &str,
) -> Result<HashMap<Value, Value>, crate::RecordError> {
    database::with_db(|db| runtime::block_on(relation.group_minimum(column, db)))
}

fn group_maximum_relation(
    relation: Relation<TestUser>,
    column: &str,
) -> Result<HashMap<Value, Value>, crate::RecordError> {
    database::with_db(|db| runtime::block_on(relation.group_maximum(column, db)))
}

fn seeded_name_id_rows() -> Vec<Vec<Value>> {
    vec![
        vec![json!(1), json!("Alice")],
        vec![json!(2), json!("Bob")],
        vec![json!(3), json!("Carol")],
    ]
}

fn seeded_emails() -> Vec<Value> {
    vec![
        json!("alice@example.com"),
        json!("bob@example.com"),
        json!("carol@example.com"),
    ]
}

fn seeded_names() -> Vec<Value> {
    vec![json!("Alice"), json!("Bob"), json!("Carol")]
}

use rustrails_support::ignored_rails_tests;

#[test]
fn test_should_sum_field() {
    run_seeded_calculation_test(|| {
        assert_eq!(
            Relation::<TestUser>::new()
                .sum_sync("id")
                .expect("sum should succeed"),
            6.0,
        );
    });
}

ignored_rails_tests!(
    "Rails-specific: relation aliases, SQL fragments, custom types, and qualified column-name coercions are not implemented";
    test_should_sum_with_qualified_name_on_loaded,
    test_should_resolve_aliased_attributes,
    test_should_return_decimal_average_of_integer_field,
    test_should_return_integer_average_if_db_returns_such,
    test_should_return_float_average_if_db_returns_such,
    test_should_return_decimal_average_if_db_returns_such,
    test_should_return_nil_as_average,
    test_should_get_maximum_of_field_with_include,
    test_should_get_maximum_of_arel_attribute_with_include,
    test_should_group_by_multiple_fields_when_table_name_is_too_long,
    test_should_order_by_calculation,
    test_count_with_aliased_attribute,
    test_sum_expression_returns_zero_when_no_records_to_sum,
    test_maximum_with_not_auto_table_name_prefix_if_column_included,
    test_minimum_with_not_auto_table_name_prefix_if_column_included,
    test_sum_with_not_auto_table_name_prefix_if_column_included,
    test_pluck_with_qualified_column_name,
    test_pluck_auto_table_name_prefix,
    test_pluck_not_auto_table_name_prefix_if_column_joined,
    test_pluck_with_hash_argument,
    test_pluck_with_hash_argument_with_multiple_tables,
    test_pluck_with_hash_argument_containing_non_existent_field,
    test_group_by_with_quoted_count_and_order_by_alias,
    test_group_by_with_order_by_virtual_count_attribute,
    test_pluck_not_auto_table_name_prefix_if_column_included,
    test_pluck_with_line_endings,
    test_having_with_strong_parameters,
    test_count_takes_attribute_type_precedence_over_database_type,
    test_sum_takes_attribute_type_precedence_over_database_type,
    test_group_by_attribute_with_custom_type,
    test_aggregate_attribute_on_enum_type,
    test_minimum_and_maximum_on_time_attributes,
    test_minimum_and_maximum_on_tz_aware_attributes,
    test_select_avg_with_group_by_as_virtual_attribute_with_sql,
    test_select_avg_with_group_by_as_virtual_attribute_with_ar,
    test_select_avg_with_joins_and_group_by_as_virtual_attribute_with_sql,
    test_select_avg_with_joins_and_group_by_as_virtual_attribute_with_ar,
    test_count_with_block_and_column_name_raises_an_error
);

#[test]
fn test_should_sum_arel_attribute() {
    run_seeded_calculation_test(|| {
        // RustRails does not expose Arel nodes, so the port exercises the equivalent column-based API.
        assert_eq!(
            Relation::<TestUser>::new()
                .sum_sync("id")
                .expect("sum should succeed"),
            6.0,
        );
    });
}

#[test]
fn test_should_average_arel_attribute() {
    run_seeded_calculation_test(|| {
        assert_eq!(
            Relation::<TestUser>::new()
                .average_sync("id")
                .expect("average should succeed"),
            2.0,
        );
    });
}

#[test]
fn test_should_get_maximum_of_arel_attribute() {
    run_seeded_calculation_test(|| {
        assert_eq!(
            Relation::<TestUser>::new()
                .maximum_sync("id")
                .expect("maximum should succeed"),
            Some(json!(3)),
        );
    });
}

#[test]
fn test_should_get_minimum_of_arel_attribute() {
    run_seeded_calculation_test(|| {
        assert_eq!(
            Relation::<TestUser>::new()
                .minimum_sync("id")
                .expect("minimum should succeed"),
            Some(json!(1)),
        );
    });
}

#[test]
fn test_should_group_by_arel_attribute() {
    run_seeded_calculation_test(|| {
        insert_user("Bob", "bobby@example.com");

        let grouped = group_count_relation(Relation::<TestUser>::new().group("name"))
            .expect("group_count should succeed");

        assert_eq!(grouped.get(&json!("Alice")), Some(&json!(1)));
        assert_eq!(grouped.get(&json!("Bob")), Some(&json!(2)));
        assert_eq!(grouped.get(&json!("Carol")), Some(&json!(1)));
    });
}

#[test]
fn test_should_group_by_summed_field() {
    run_seeded_calculation_test(|| {
        insert_user("Bob", "bobby@example.com");

        let grouped = group_sum_relation(Relation::<TestUser>::new().group("name"), "id")
            .expect("group_sum should succeed");

        assert_eq!(grouped.get(&json!("Alice")), Some(&json!(1)));
        assert_eq!(grouped.get(&json!("Bob")), Some(&json!(6)));
        assert_eq!(grouped.get(&json!("Carol")), Some(&json!(3)));
    });
}

#[test]
fn test_should_not_use_alias_for_grouped_field() {
    run_seeded_calculation_test(|| {
        insert_user("Bob", "bobby@example.com");

        let grouped = group_sum_relation(Relation::<TestUser>::new().group("name"), "id")
            .expect("group_sum should succeed");

        assert!(grouped.contains_key(&json!("Bob")));
        assert!(!grouped.contains_key(&json!("test_users.name")));
    });
}

#[test]
fn test_should_order_by_grouped_field() {
    run_seeded_calculation_test(|| {
        insert_user("Bob", "bobby@example.com");

        assert_eq!(
            Relation::<TestUser>::new()
                .group("name")
                .order("name", OrderDirection::Asc)
                .limit(1)
                .count_sync()
                .expect("count should succeed"),
            1,
        );
    });
}

#[test]
fn test_limit_should_apply_before_count_arel_attribute() {
    run_seeded_calculation_test(|| {
        assert_eq!(
            Relation::<TestUser>::new()
                .select(&["id"])
                .order("id", OrderDirection::Asc)
                .limit(1)
                .count_sync()
                .expect("count should succeed"),
            1,
        );
    });
}

#[test]
fn test_count_selected_arel_attribute() {
    run_seeded_calculation_test(|| {
        assert_eq!(
            Relation::<TestUser>::new()
                .select(&["id"])
                .count_sync()
                .expect("count should succeed"),
            3,
        );
    });
}

#[test]
fn test_count_selected_arel_attributes() {
    run_seeded_calculation_test(|| {
        assert_eq!(
            Relation::<TestUser>::new()
                .select(&["id", "name"])
                .count_sync()
                .expect("count should succeed"),
            3,
        );
    });
}

#[test]
fn test_count_with_column_parameter() {
    run_seeded_calculation_test(|| {
        assert_eq!(
            Relation::<TestUser>::new()
                .select(&["id"])
                .r#where(HashMap::from([("name".to_owned(), json!("Bob"))]))
                .count_sync()
                .expect("count should succeed"),
            1,
        );
    });
}

#[test]
fn test_count_with_arel_attribute() {
    run_seeded_calculation_test(|| {
        assert_eq!(
            Relation::<TestUser>::new()
                .select(&["id"])
                .count_sync()
                .expect("count should succeed"),
            3,
        );
    });
}

#[test]
fn test_count_with_arel_star() {
    run_seeded_calculation_test(|| {
        assert_eq!(
            Relation::<TestUser>::new()
                .count_sync()
                .expect("count should succeed"),
            3
        );
    });
}

#[test]
fn test_pick_delegate_to_all() {
    run_seeded_calculation_test(|| {
        assert_eq!(
            TestUser::pick_sync("email").expect("pick_sync should succeed"),
            Some(json!("alice@example.com")),
        );
    });
}

#[test]
fn test_minimum_and_maximum_on_non_numeric_type() {
    run_seeded_calculation_test(|| {
        assert_eq!(
            Relation::<TestUser>::new()
                .minimum_sync("name")
                .expect("minimum should succeed"),
            Some(json!("Alice")),
        );
        assert_eq!(
            Relation::<TestUser>::new()
                .maximum_sync("name")
                .expect("maximum should succeed"),
            Some(json!("Carol")),
        );
    });
}

#[test]
fn test_should_count_with_group_by_qualified_name_on_loaded() {
    run_seeded_calculation_test(|| {
        insert_user("Bob", "bobby@example.com");

        assert_eq!(
            Relation::<TestUser>::new()
                .group("name")
                .count_sync()
                .expect("count should succeed"),
            3,
        );
    });
}

#[test]
fn test_should_average_field() {
    run_seeded_calculation_test(|| {
        assert_eq!(
            Relation::<TestUser>::new()
                .average_sync("id")
                .expect("average should succeed"),
            2.0,
        );
    });
}

#[test]
fn test_should_get_maximum_of_field() {
    run_seeded_calculation_test(|| {
        assert_eq!(
            Relation::<TestUser>::new()
                .maximum_sync("id")
                .expect("maximum should succeed"),
            Some(json!(3)),
        );
    });
}

#[test]
fn test_should_get_minimum_of_field() {
    run_seeded_calculation_test(|| {
        assert_eq!(
            Relation::<TestUser>::new()
                .minimum_sync("id")
                .expect("minimum should succeed"),
            Some(json!(1)),
        );
    });
}

#[test]
fn test_should_group_by_field() {
    run_seeded_calculation_test(|| {
        insert_user("Bob", "bobby@example.com");

        assert_eq!(
            Relation::<TestUser>::new()
                .group("name")
                .count_sync()
                .expect("count should succeed"),
            3,
        );
    });
}

#[test]
fn test_should_group_by_multiple_fields() {
    run_seeded_calculation_test(|| {
        insert_user("Bob", "bobby@example.com");

        assert_eq!(
            Relation::<TestUser>::new()
                .group("name")
                .group("email")
                .count_sync()
                .expect("count should succeed"),
            4,
        );
    });
}

#[test]
fn test_should_group_by_multiple_fields_having_functions() {
    run_seeded_calculation_test(|| {
        insert_user("Bob", "bobby@example.com");

        assert_eq!(
            Relation::<TestUser>::new()
                .group("name")
                .having("COUNT(*) > 1")
                .count_sync()
                .expect("count should succeed"),
            1,
        );
    });
}

ignored_rails_tests!(
    "Rails-specific: joins/includes/grouped association semantics are not implemented";
    test_should_generate_valid_sql_with_joins_and_group,
    test_count_with_eager_loading_and_custom_order,
    test_count_with_eager_loading_and_custom_select_and_order,
    test_count_with_eager_loading_and_custom_order_and_distinct,
    test_distinct_count_all_with_custom_select_and_order,
    test_distinct_joins_count_with_order_and_limit,
    test_distinct_joins_count_with_order_and_offset,
    test_distinct_joins_count_with_order_and_limit_and_offset,
    test_count_for_a_composite_primary_key_model,
    test_group_by_count_for_a_composite_primary_key_model,
    test_count_for_a_composite_primary_key_model_with_includes_and_references,
    test_should_group_by_summed_association,
    test_should_group_by_fields_with_table_alias,
    test_should_calculate_grouped_association_with_invalid_field,
    test_should_group_by_association_with_non_numeric_foreign_key,
    test_should_calculate_grouped_association_with_foreign_key_option,
    test_should_calculate_grouped_by_function,
    test_should_calculate_grouped_by_function_with_table_alias,
    test_should_group_by_summed_field_through_association_and_having,
    test_count_selected_field_with_include,
    test_should_not_perform_joined_include_by_default,
    test_should_perform_joined_include_when_referencing_included_tables,
    test_should_count_field_in_joined_table,
    test_count_arel_attribute_in_joined_table_with,
    test_count_selected_arel_attribute_in_joined_table,
    test_should_count_field_in_joined_table_with_group_by,
    test_should_count_field_in_joined_table_with_group_by_when_tables_share_column_names,
    test_should_count_field_of_root_table_with_conflicting_group_by_column,
    test_count_with_from_option,
    test_sum_with_from_option,
    test_average_with_from_option,
    test_minimum_with_from_option,
    test_maximum_with_from_option,
    test_from_option_with_specified_index,
    test_from_option_with_table_different_than_class,
    test_pluck_type_cast_with_joins_without_table_name_qualified_column,
    test_pluck_type_cast_with_left_joins_without_table_name_qualified_column,
    test_pluck_type_cast_with_eager_load_without_table_name_qualified_column,
    test_pluck_with_type_cast_does_not_corrupt_the_query_cache,
    test_pluck_on_aliased_attribute,
    test_pluck_if_table_included,
    test_ids_for_a_composite_primary_key,
    test_pluck_for_a_composite_primary_key,
    test_ids_for_a_composite_primary_key_with_scope,
    test_ids_for_a_composite_primary_key_on_loaded_relation,
    test_ids_with_join,
    test_ids_with_polymorphic_relation_join,
    test_ids_with_eager_load,
    test_ids_with_preload,
    test_ids_with_includes,
    test_ids_with_includes_and_non_primary_key_order,
    test_ids_with_includes_and_scope,
    test_ids_with_includes_and_table_scope,
    test_ids_on_loaded_relation_with_includes_and_table_scope,
    test_ids_with_includes_limit_and_empty_result,
    test_ids_with_includes_offset,
    test_pluck_with_includes_limit_and_empty_result,
    test_pluck_with_includes_offset,
    test_pluck_with_join,
    test_pluck_with_join_alias,
    test_pluck_with_multiple_columns_and_includes,
    test_calculation_with_polymorphic_relation,
    test_pluck_joined_with_polymorphic_relation,
    test_grouped_calculation_with_polymorphic_relation,
    test_calculation_grouped_by_association_doesnt_error_when_no_records_have_association,
    test_should_reference_correct_aliases_while_joining_tables_of_has_many_through_association
);

#[test]
fn test_should_calculate_against_given_relation() {
    run_seeded_calculation_test(|| {
        let total = Relation::<TestUser>::new()
            .r#where(HashMap::from([("name".to_owned(), json!("Bob"))]))
            .sum_sync("id")
            .expect("sum should succeed");

        assert_eq!(total, 2.0);
    });
}

#[test]
fn test_should_limit_calculation() {
    run_seeded_calculation_test(|| {
        let total = Relation::<TestUser>::new()
            .order("id", OrderDirection::Asc)
            .limit(2)
            .sum_sync("id")
            .expect("sum should succeed");

        assert_eq!(total, 3.0);
    });
}

#[test]
fn test_should_limit_calculation_with_offset() {
    run_seeded_calculation_test(|| {
        let total = Relation::<TestUser>::new()
            .order("id", OrderDirection::Asc)
            .offset(1)
            .limit(1)
            .sum_sync("id")
            .expect("sum should succeed");

        assert_eq!(total, 2.0);
    });
}

ignored_rails_tests!(
    "Rails-specific: count signature validation beyond relation-based scopes is not implemented";
    test_count_with_too_many_parameters_raises
);

#[test]
fn test_order_should_apply_before_count() {
    run_seeded_calculation_test(|| {
        assert_eq!(
            Relation::<TestUser>::new()
                .order("id", OrderDirection::Desc)
                .count_sync()
                .expect("count should succeed"),
            3,
        );
    });
}

#[test]
fn test_count_with_column_and_options_parameter() {
    run_seeded_calculation_test(|| {
        assert_eq!(
            Relation::<TestUser>::new()
                .select(&["id"])
                .order("id", OrderDirection::Desc)
                .r#where(HashMap::from([("name".to_owned(), json!("Bob"))]))
                .count_sync()
                .expect("count should succeed"),
            1,
        );
    });
}

#[test]
fn test_limit_should_apply_before_count() {
    run_seeded_calculation_test(|| {
        assert_eq!(
            Relation::<TestUser>::new()
                .order("id", OrderDirection::Asc)
                .limit(1)
                .count_sync()
                .expect("count should succeed"),
            1,
        );
    });
}

#[test]
fn test_count_should_shortcut_with_limit_zero() {
    run_seeded_calculation_test(|| {
        assert_eq!(
            Relation::<TestUser>::new()
                .limit(0)
                .count_sync()
                .expect("count should succeed"),
            0,
        );
    });
}

#[test]
fn test_limit_is_kept() {
    run_seeded_calculation_test(|| {
        let ids = Relation::<TestUser>::new()
            .order("id", OrderDirection::Asc)
            .limit(2)
            .ids_sync()
            .expect("ids should succeed");

        assert_eq!(ids, vec![1, 2]);
    });
}

#[test]
fn test_offset_is_kept() {
    run_seeded_calculation_test(|| {
        let ids = Relation::<TestUser>::new()
            .order("id", OrderDirection::Asc)
            .offset(1)
            .ids_sync()
            .expect("ids should succeed");

        assert_eq!(ids, vec![2, 3]);
    });
}

#[test]
fn test_limit_with_offset_is_kept() {
    run_seeded_calculation_test(|| {
        let ids = Relation::<TestUser>::new()
            .order("id", OrderDirection::Asc)
            .offset(1)
            .limit(1)
            .ids_sync()
            .expect("ids should succeed");

        assert_eq!(ids, vec![2]);
    });
}

#[test]
fn test_no_limit_no_offset() {
    run_seeded_calculation_test(|| {
        let ids = Relation::<TestUser>::new()
            .order("id", OrderDirection::Asc)
            .ids_sync()
            .expect("ids should succeed");

        assert_eq!(ids, vec![1, 2, 3]);
    });
}

#[test]
fn test_no_order_by_when_counting_all() {
    run_seeded_calculation_test(|| {
        assert_eq!(
            Relation::<TestUser>::new()
                .order("id", OrderDirection::Desc)
                .count_sync()
                .expect("count should succeed"),
            3,
        );
    });
}

ignored_rails_tests!(
    "Rails-specific: invalid-column count semantics diverge from ActiveRecord's richer calculation API";
    test_count_on_invalid_columns_raises
);

#[test]
fn test_distinct_count_with_group_by() {
    run_seeded_calculation_test(|| {
        insert_user("Bob", "bobby@example.com");

        assert_eq!(
            Relation::<TestUser>::new()
                .select(&["name"])
                .distinct()
                .group("name")
                .count_sync()
                .expect("count should succeed"),
            3,
        );
    });
}

#[test]
fn test_distinct_count_with_group_by_and_order_and_limit() {
    run_seeded_calculation_test(|| {
        insert_user("Bob", "bobby@example.com");

        assert_eq!(
            Relation::<TestUser>::new()
                .select(&["name"])
                .distinct()
                .group("name")
                .order("name", OrderDirection::Asc)
                .limit(2)
                .count_sync()
                .expect("count should succeed"),
            2,
        );
    });
}

#[test]
fn test_apply_distinct_in_count() {
    run_seeded_calculation_test(|| {
        insert_user("Bob", "bobby@example.com");

        assert_eq!(
            Relation::<TestUser>::new()
                .select(&["name"])
                .distinct()
                .count_sync()
                .expect("count should succeed"),
            3,
        );
    });
}

#[test]
fn test_should_group_by_summed_field_having_condition() {
    run_seeded_calculation_test(|| {
        insert_user("Bob", "bobby@example.com");

        assert_eq!(
            Relation::<TestUser>::new()
                .group("name")
                .having("COUNT(*) > 1")
                .count_sync()
                .expect("count should succeed"),
            1,
        );
    });
}

ignored_rails_tests!(
    "Rails-specific: grouped select aliases and table aliases are not implemented";
    test_should_group_by_summed_field_having_condition_from_select,
    test_should_group_by_summed_field_with_table_alias
);

#[test]
fn test_should_calculate_grouped_with_longer_field() {
    run_seeded_calculation_test(|| {
        let grouped = group_sum_relation(Relation::<TestUser>::new().group("email"), "id")
            .expect("group_sum should succeed");

        assert_eq!(grouped.get(&json!("alice@example.com")), Some(&json!(1)));
        assert_eq!(grouped.get(&json!("bob@example.com")), Some(&json!(2)));
        assert_eq!(grouped.get(&json!("carol@example.com")), Some(&json!(3)));
    });
}

#[test]
fn test_should_sum_field_with_conditions() {
    run_seeded_calculation_test(|| {
        assert_eq!(
            Relation::<TestUser>::new()
                .r#where(HashMap::from([("name".to_owned(), json!("Bob"))]))
                .sum_sync("id")
                .expect("sum should succeed"),
            2.0,
        );
    });
}

#[test]
fn test_should_return_zero_if_sum_conditions_return_nothing() {
    run_seeded_calculation_test(|| {
        assert_eq!(
            Relation::<TestUser>::new()
                .r#where(HashMap::from([("name".to_owned(), json!("Nobody"))]))
                .sum_sync("id")
                .expect("sum should succeed"),
            0.0,
        );
    });
}

ignored_rails_tests!(
    "Rails-specific: decimal serialization details are not implemented by the TestUser model";
    test_sum_should_return_valid_values_for_decimals,
    test_should_return_type_casted_values_with_group_and_expression,
    test_should_count_selected_field_with_include
);

#[test]
fn test_should_group_by_summed_field_with_conditions() {
    run_seeded_calculation_test(|| {
        insert_user("Bob", "bobby@example.com");

        assert_eq!(
            Relation::<TestUser>::new()
                .r#where(HashMap::from([("name".to_owned(), json!("Bob"))]))
                .group("name")
                .count_sync()
                .expect("count should succeed"),
            1,
        );
    });
}

#[test]
fn test_should_group_by_summed_field_with_conditions_and_having() {
    run_seeded_calculation_test(|| {
        insert_user("Bob", "bobby@example.com");

        assert_eq!(
            Relation::<TestUser>::new()
                .r#where(HashMap::from([("name".to_owned(), json!("Bob"))]))
                .group("name")
                .having("COUNT(*) > 1")
                .count_sync()
                .expect("count should succeed"),
            1,
        );
    });
}

ignored_rails_tests!(
    "Rails-specific: scoped-from, Enumerable blocks, and SQL expressions are not implemented";
    test_should_not_overshadow_enumerable_sum,
    test_should_sum_scoped_field_with_from,
    test_sum_uses_enumerable_version_when_block_is_given,
    test_should_sum_expression,
    test_count_with_block,
    test_count_with_empty_in
);

#[test]
fn test_should_group_by_scoped_field() {
    run_seeded_calculation_test(|| {
        let grouped = group_count_relation(
            Relation::<TestUser>::new()
                .group("name")
                .r#where(HashMap::from([("name".to_owned(), json!("Bob"))])),
        )
        .expect("group_count should succeed");

        assert_eq!(grouped, HashMap::from([(json!("Bob"), json!(1))]));
    });
}

#[test]
fn test_should_sum_scoped_field() {
    run_seeded_calculation_test(|| {
        assert_eq!(
            Relation::<TestUser>::new()
                .r#where(HashMap::from([(
                    "email".to_owned(),
                    json!("alice@example.com")
                )]))
                .sum_sync("id")
                .expect("sum should succeed"),
            1.0,
        );
    });
}

#[test]
fn test_should_sum_scoped_field_with_conditions() {
    run_seeded_calculation_test(|| {
        assert_eq!(
            Relation::<TestUser>::new()
                .r#where(HashMap::from([(
                    "email".to_owned(),
                    json!("carol@example.com")
                )]))
                .sum_sync("id")
                .expect("sum should succeed"),
            3.0,
        );
    });
}

#[test]
fn test_should_count_scoped_select() {
    run_seeded_calculation_test(|| {
        assert_eq!(
            Relation::<TestUser>::new()
                .select(&["name"])
                .count_sync()
                .expect("count should succeed"),
            3,
        );
    });
}

#[test]
fn test_should_calculate_with_invalid_field() {
    run_seeded_calculation_test(|| {
        let error = Relation::<TestUser>::new()
            .sum_sync("missing")
            .expect_err("unknown column should fail");

        assert!(
            matches!(error, crate::RecordError::Invalid(message) if message.contains("missing"))
        );
    });
}

#[test]
fn test_should_calculate_grouped_with_invalid_field() {
    run_seeded_calculation_test(|| {
        let error = Relation::<TestUser>::new()
            .group("name")
            .sum_sync("missing")
            .expect_err("unknown column should fail");

        assert!(
            matches!(error, crate::RecordError::Invalid(message) if message.contains("missing"))
        );
    });
}

#[test]
fn test_count_with_distinct() {
    run_seeded_calculation_test(|| {
        insert_user("Bob", "bobby@example.com");

        assert_eq!(
            Relation::<TestUser>::new()
                .select(&["name"])
                .distinct()
                .count_sync()
                .expect("count should succeed"),
            3,
        );
    });
}

#[test]
fn test_should_count_manual_select_with_include() {
    run_seeded_calculation_test(|| {
        assert_eq!(
            Relation::<TestUser>::new()
                .select(&["name"])
                .count_sync()
                .expect("count should succeed"),
            3,
        );
    });
}

#[test]
fn test_should_count_manual_select_with_count_all() {
    run_seeded_calculation_test(|| {
        assert_eq!(
            Relation::<TestUser>::new()
                .select(&["id"])
                .count_sync()
                .expect("count should succeed"),
            3,
        );
    });
}

#[test]
fn test_should_count_with_manual_distinct_select_and_distinct() {
    run_seeded_calculation_test(|| {
        insert_user("Bob", "bobby@example.com");

        assert_eq!(
            Relation::<TestUser>::new()
                .select(&["name"])
                .distinct()
                .count_sync()
                .expect("count should succeed"),
            3,
        );
    });
}

#[test]
fn test_should_count_manual_select_with_group_with_count_all() {
    run_seeded_calculation_test(|| {
        insert_user("Bob", "bobby@example.com");

        assert_eq!(
            Relation::<TestUser>::new()
                .select(&["name"])
                .group("name")
                .count_sync()
                .expect("count should succeed"),
            3,
        );
    });
}

#[test]
fn test_should_count_manual_with_count_all() {
    run_seeded_calculation_test(|| {
        assert_eq!(
            Relation::<TestUser>::new()
                .select(&["email"])
                .count_sync()
                .expect("count should succeed"),
            3,
        );
    });
}

#[test]
fn test_count_with_no_parameters_isnt_deprecated() {
    run_seeded_calculation_test(|| {
        assert_eq!(
            Relation::<TestUser>::new()
                .count_sync()
                .expect("count should succeed"),
            3,
        );
    });
}

#[test]
fn test_count_with_order() {
    run_seeded_calculation_test(|| {
        assert_eq!(
            Relation::<TestUser>::new()
                .order("id", OrderDirection::Asc)
                .count_sync()
                .expect("count should succeed"),
            3,
        );
    });
}

#[test]
fn test_count_with_reverse_order() {
    run_seeded_calculation_test(|| {
        assert_eq!(
            Relation::<TestUser>::new()
                .order("id", OrderDirection::Desc)
                .count_sync()
                .expect("count should succeed"),
            3,
        );
    });
}

#[test]
fn test_count_with_where_and_order() {
    run_seeded_calculation_test(|| {
        assert_eq!(
            Relation::<TestUser>::new()
                .r#where(HashMap::from([("name".to_owned(), json!("Bob"))]))
                .order("id", OrderDirection::Desc)
                .count_sync()
                .expect("count should succeed"),
            1,
        );
    });
}

#[test]
fn test_pluck() {
    run_seeded_calculation_test(|| {
        assert_eq!(
            Relation::<TestUser>::new()
                .order("id", OrderDirection::Asc)
                .pluck_sync("name")
                .expect("pluck should succeed"),
            vec![json!("Alice"), json!("Bob"), json!("Carol")],
        );
    });
}

ignored_rails_tests!(
    "Rails-specific: async pluck, SQL fragments, qualified-name coercions, and hash arguments are not implemented";
    test_async_pluck_on_loaded_relation,
    test_async_pluck_none_relation,
    test_pluck_without_column_names,
    test_pluck_with_serialization,
    test_pluck_with_reserved_words,
    test_pluck_with_qualified_name_on_loaded,
    test_pluck_columns_with_same_name,
    test_pluck_functions_with_alias,
    test_pluck_functions_without_alias
);

#[test]
fn test_pluck_with_empty_in() {
    run_seeded_calculation_test(|| {
        assert_eq!(
            Relation::<TestUser>::new()
                .r#where(HashMap::from([("name".to_owned(), json!("Nobody"))]))
                .pluck_sync("name")
                .expect("pluck should succeed"),
            Vec::<Value>::new(),
        );
    });
}

#[test]
fn test_pluck_with_selection_clause() {
    run_seeded_calculation_test(|| {
        assert_eq!(
            Relation::<TestUser>::new()
                .select(&["email"])
                .order("id", OrderDirection::Asc)
                .pluck_sync("email")
                .expect("pluck should succeed"),
            seeded_emails(),
        );
    });
}

#[test]
fn test_pluck_replaces_select_clause() {
    run_seeded_calculation_test(|| {
        assert_eq!(
            Relation::<TestUser>::new()
                .select(&["name"])
                .reselect(&["email"])
                .order("id", OrderDirection::Asc)
                .pluck_sync("email")
                .expect("pluck should succeed"),
            seeded_emails(),
        );
    });
}

#[test]
fn test_pluck_loaded_relation() {
    run_seeded_calculation_test(|| {
        let relation = Relation::<TestUser>::new().order("id", OrderDirection::Asc);
        let loaded = load_relation(relation.clone()).expect("relation should load");

        assert_eq!(loaded.len(), 3);
        assert_eq!(
            relation.pluck_sync("name").expect("pluck should succeed"),
            seeded_names(),
        );
    });
}

#[test]
fn test_pluck_loaded_relation_multiple_columns() {
    run_seeded_calculation_test(|| {
        let relation = Relation::<TestUser>::new().order("id", OrderDirection::Asc);
        let loaded = load_relation(relation.clone()).expect("relation should load");

        assert_eq!(loaded.len(), 3);
        assert_eq!(
            pluck_columns_relation(relation, &["id", "name"])
                .expect("pluck_columns should succeed"),
            seeded_name_id_rows(),
        );
    });
}

#[test]
fn test_pluck_multiple_columns() {
    run_seeded_calculation_test(|| {
        assert_eq!(
            pluck_columns_relation(
                Relation::<TestUser>::new().order("id", OrderDirection::Asc),
                &["id", "name"],
            )
            .expect("pluck_columns should succeed"),
            seeded_name_id_rows(),
        );
    });
}

#[test]
fn test_pluck_with_multiple_columns_and_selection_clause() {
    run_seeded_calculation_test(|| {
        assert_eq!(
            pluck_columns_relation(
                Relation::<TestUser>::new()
                    .select(&["email"])
                    .reselect(&["id", "name"])
                    .order("id", OrderDirection::Asc),
                &["id", "name"],
            )
            .expect("pluck_columns should succeed"),
            seeded_name_id_rows(),
        );
    });
}

#[test]
fn test_pluck_type_cast() {
    run_seeded_calculation_test(|| {
        assert_eq!(
            Relation::<TestUser>::new()
                .order("id", OrderDirection::Asc)
                .pluck_sync("id")
                .expect("pluck should succeed"),
            vec![json!(1), json!(2), json!(3)],
        );
    });
}

ignored_rails_tests!(
    "Rails-specific: conflict-column pluck casting is not implemented with TestUser's flat schema";
    test_pluck_type_cast_with_conflict_column_names
);

#[test]
fn test_pluck_and_distinct() {
    run_seeded_calculation_test(|| {
        insert_user("Bob", "bobby@example.com");

        assert_eq!(
            Relation::<TestUser>::new()
                .select(&["name"])
                .distinct()
                .order("name", OrderDirection::Asc)
                .pluck_sync("name")
                .expect("pluck should succeed"),
            vec![json!("Alice"), json!("Bob"), json!("Carol")],
        );
    });
}

#[test]
fn test_ids() {
    run_seeded_calculation_test(|| {
        assert_eq!(
            Relation::<TestUser>::new()
                .order("id", OrderDirection::Asc)
                .ids_sync()
                .expect("ids should succeed"),
            vec![1, 2, 3],
        );
    });
}

#[test]
fn test_ids_with_scope() {
    run_seeded_calculation_test(|| {
        assert_eq!(
            Relation::<TestUser>::new()
                .r#where(HashMap::from([("name".to_owned(), json!("Bob"))]))
                .ids_sync()
                .expect("ids should succeed"),
            vec![2],
        );
    });
}

#[test]
fn test_ids_on_relation() {
    run_seeded_calculation_test(|| {
        assert_eq!(
            Relation::<TestUser>::new()
                .order("id", OrderDirection::Desc)
                .ids_sync()
                .expect("ids should succeed"),
            vec![3, 2, 1],
        );
    });
}

ignored_rails_tests!(
    "Rails-specific: async ids variants are not implemented";
    test_ids_async_on_loaded_relation
);

#[test]
fn test_ids_on_loaded_relation() {
    run_seeded_calculation_test(|| {
        let relation = Relation::<TestUser>::new().order("id", OrderDirection::Asc);
        let loaded = load_relation(relation.clone()).expect("relation should load");

        assert_eq!(loaded.len(), 3);
        assert_eq!(
            relation.ids_sync().expect("ids should succeed"),
            vec![1, 2, 3]
        );
    });
}

#[test]
fn test_ids_on_loaded_relation_with_scope() {
    run_seeded_calculation_test(|| {
        let relation = Relation::<TestUser>::new()
            .r#where(HashMap::from([("name".to_owned(), json!("Bob"))]))
            .order("id", OrderDirection::Asc);
        let loaded = load_relation(relation.clone()).expect("relation should load");

        assert_eq!(loaded.len(), 1);
        assert_eq!(relation.ids_sync().expect("ids should succeed"), vec![2]);
    });
}

#[test]
fn test_ids_with_contradicting_scope() {
    run_seeded_calculation_test(|| {
        assert!(
            Relation::<TestUser>::new()
                .r#where(HashMap::from([("name".to_owned(), json!("Nobody"))]))
                .ids_sync()
                .expect("ids should succeed")
                .is_empty()
        );
    });
}

#[test]
fn test_group_by_with_limit() {
    run_seeded_calculation_test(|| {
        insert_user("Bob", "bobby@example.com");

        assert_eq!(
            Relation::<TestUser>::new()
                .group("name")
                .order("name", OrderDirection::Asc)
                .limit(2)
                .count_sync()
                .expect("count should succeed"),
            2,
        );
    });
}

#[test]
fn test_group_by_with_offset() {
    run_seeded_calculation_test(|| {
        insert_user("Bob", "bobby@example.com");

        assert_eq!(
            Relation::<TestUser>::new()
                .group("name")
                .order("name", OrderDirection::Asc)
                .offset(1)
                .count_sync()
                .expect("count should succeed"),
            2,
        );
    });
}

#[test]
fn test_group_by_with_limit_and_offset() {
    run_seeded_calculation_test(|| {
        insert_user("Bob", "bobby@example.com");

        assert_eq!(
            Relation::<TestUser>::new()
                .group("name")
                .order("name", OrderDirection::Asc)
                .offset(1)
                .limit(1)
                .count_sync()
                .expect("count should succeed"),
            1,
        );
    });
}

#[test]
fn test_group_by_multiple_same_field() {
    run_seeded_calculation_test(|| {
        insert_user("Bob", "bobby@example.com");

        assert_eq!(
            Relation::<TestUser>::new()
                .group("name")
                .group("name")
                .count_sync()
                .expect("count should succeed"),
            3,
        );
    });
}

#[test]
fn test_distinct_count_with_order_and_limit() {
    run_seeded_calculation_test(|| {
        insert_user("Bob", "bobby@example.com");

        assert_eq!(
            Relation::<TestUser>::new()
                .select(&["name"])
                .distinct()
                .order("name", OrderDirection::Asc)
                .limit(2)
                .count_sync()
                .expect("count should succeed"),
            2,
        );
    });
}

#[test]
fn test_distinct_count_with_order_and_offset() {
    run_seeded_calculation_test(|| {
        insert_user("Bob", "bobby@example.com");

        assert_eq!(
            Relation::<TestUser>::new()
                .select(&["name"])
                .distinct()
                .order("name", OrderDirection::Asc)
                .offset(1)
                .count_sync()
                .expect("count should succeed"),
            2,
        );
    });
}

#[test]
fn test_distinct_count_with_order_and_limit_and_offset() {
    run_seeded_calculation_test(|| {
        insert_user("Bob", "bobby@example.com");

        assert_eq!(
            Relation::<TestUser>::new()
                .select(&["name"])
                .distinct()
                .order("name", OrderDirection::Asc)
                .offset(1)
                .limit(1)
                .count_sync()
                .expect("count should succeed"),
            1,
        );
    });
}

#[test]
fn test_should_count_scoped_select_with_options() {
    run_seeded_calculation_test(|| {
        assert_eq!(
            Relation::<TestUser>::new()
                .select(&["name"])
                .r#where(HashMap::from([("name".to_owned(), json!("Bob"))]))
                .count_sync()
                .expect("count should succeed"),
            1,
        );
    });
}

#[test]
fn test_pluck_in_relation() {
    run_seeded_calculation_test(|| {
        assert_eq!(
            Relation::<TestUser>::new()
                .r#where(HashMap::from([("name".to_owned(), json!("Bob"))]))
                .order("id", OrderDirection::Asc)
                .pluck_sync("id")
                .expect("pluck should succeed"),
            vec![json!(2)],
        );
    });
}

#[test]
fn test_select_pluck_returns_default_values_for_unselected_column() {
    run_seeded_calculation_test(|| {
        assert_eq!(
            Relation::<TestUser>::new()
                .select(&["name"])
                .order("id", OrderDirection::Asc)
                .pluck_sync("email")
                .expect("pluck should succeed"),
            vec![json!(""), json!(""), json!("")],
        );
    });
}

#[test]
fn test_reselect_pluck_uses_new_projection() {
    run_seeded_calculation_test(|| {
        assert_eq!(
            Relation::<TestUser>::new()
                .select(&["name"])
                .reselect(&["email"])
                .order("id", OrderDirection::Asc)
                .pluck_sync("email")
                .expect("pluck should succeed"),
            vec![
                json!("alice@example.com"),
                json!("bob@example.com"),
                json!("carol@example.com"),
            ],
        );
    });
}

#[test]
fn test_select_pick_returns_default_values_for_unselected_column() {
    run_seeded_calculation_test(|| {
        assert_eq!(
            Relation::<TestUser>::new()
                .select(&["name"])
                .order("id", OrderDirection::Asc)
                .pick_sync("email")
                .expect("pick should succeed"),
            Some(json!("")),
        );
    });
}

#[test]
fn test_reselect_pick_uses_new_projection() {
    run_seeded_calculation_test(|| {
        assert_eq!(
            Relation::<TestUser>::new()
                .select(&["name"])
                .reselect(&["email"])
                .order("id", OrderDirection::Asc)
                .pick_sync("email")
                .expect("pick should succeed"),
            Some(json!("alice@example.com")),
        );
    });
}

#[test]
fn test_select_ids_returns_default_primary_keys_for_unselected_id() {
    run_seeded_calculation_test(|| {
        assert_eq!(
            Relation::<TestUser>::new()
                .select(&["name"])
                .order("id", OrderDirection::Asc)
                .ids_sync()
                .expect("ids should succeed"),
            vec![0, 0, 0],
        );
    });
}

#[test]
fn test_reselect_ids_restore_selected_primary_keys() {
    run_seeded_calculation_test(|| {
        assert_eq!(
            Relation::<TestUser>::new()
                .select(&["name"])
                .reselect(&["id"])
                .order("id", OrderDirection::Asc)
                .ids_sync()
                .expect("ids should succeed"),
            vec![1, 2, 3],
        );
    });
}

#[test]
fn test_pluck_with_limit_zero() {
    run_seeded_calculation_test(|| {
        assert_eq!(
            Relation::<TestUser>::new()
                .limit(0)
                .pluck_sync("name")
                .expect("pluck should succeed"),
            Vec::<serde_json::Value>::new(),
        );
    });
}

#[test]
fn test_reselect_distinct_sum_uses_selected_numeric_column() {
    run_seeded_calculation_test(|| {
        insert_user("Bob", "bobby@example.com");

        assert_eq!(
            Relation::<TestUser>::new()
                .select(&["email"])
                .reselect(&["id"])
                .distinct()
                .sum_sync("id")
                .expect("sum should succeed"),
            10.0,
        );
    });
}

#[test]
fn test_reselect_distinct_average_uses_selected_numeric_column() {
    run_seeded_calculation_test(|| {
        insert_user("Bob", "bobby@example.com");

        assert_eq!(
            Relation::<TestUser>::new()
                .select(&["email"])
                .reselect(&["id"])
                .distinct()
                .average_sync("id")
                .expect("average should succeed"),
            2.5,
        );
    });
}

#[test]
fn test_reselect_distinct_minimum_uses_selected_numeric_column() {
    run_seeded_calculation_test(|| {
        insert_user("Bob", "bobby@example.com");

        assert_eq!(
            Relation::<TestUser>::new()
                .select(&["email"])
                .reselect(&["id"])
                .distinct()
                .minimum_sync("id")
                .expect("minimum should succeed"),
            Some(json!(1)),
        );
    });
}

#[test]
fn test_reselect_distinct_maximum_uses_selected_numeric_column() {
    run_seeded_calculation_test(|| {
        insert_user("Bob", "bobby@example.com");

        assert_eq!(
            Relation::<TestUser>::new()
                .select(&["email"])
                .reselect(&["id"])
                .distinct()
                .maximum_sync("id")
                .expect("maximum should succeed"),
            Some(json!(4)),
        );
    });
}

#[test]
fn test_select_sum_returns_zero_for_unselected_numeric_column() {
    run_seeded_calculation_test(|| {
        assert_eq!(
            Relation::<TestUser>::new()
                .select(&["name"])
                .sum_sync("id")
                .expect("sum should succeed"),
            0.0,
        );
    });
}

#[test]
fn test_reselect_sum_uses_reselected_numeric_column() {
    run_seeded_calculation_test(|| {
        assert_eq!(
            Relation::<TestUser>::new()
                .select(&["name"])
                .reselect(&["id"])
                .sum_sync("id")
                .expect("sum should succeed"),
            6.0,
        );
    });
}

#[test]
fn test_select_average_returns_zero_for_unselected_numeric_column() {
    run_seeded_calculation_test(|| {
        assert_eq!(
            Relation::<TestUser>::new()
                .select(&["name"])
                .average_sync("id")
                .expect("average should succeed"),
            0.0,
        );
    });
}

#[test]
fn test_reselect_average_uses_reselected_numeric_column() {
    run_seeded_calculation_test(|| {
        assert_eq!(
            Relation::<TestUser>::new()
                .select(&["name"])
                .reselect(&["id"])
                .average_sync("id")
                .expect("average should succeed"),
            2.0,
        );
    });
}

#[test]
fn test_select_minimum_returns_zero_for_unselected_numeric_column() {
    run_seeded_calculation_test(|| {
        assert_eq!(
            Relation::<TestUser>::new()
                .select(&["name"])
                .minimum_sync("id")
                .expect("minimum should succeed"),
            Some(json!(0)),
        );
    });
}

#[test]
fn test_reselect_minimum_uses_reselected_numeric_column() {
    run_seeded_calculation_test(|| {
        assert_eq!(
            Relation::<TestUser>::new()
                .select(&["name"])
                .reselect(&["id"])
                .minimum_sync("id")
                .expect("minimum should succeed"),
            Some(json!(1)),
        );
    });
}

#[test]
fn test_select_maximum_returns_zero_for_unselected_numeric_column() {
    run_seeded_calculation_test(|| {
        assert_eq!(
            Relation::<TestUser>::new()
                .select(&["name"])
                .maximum_sync("id")
                .expect("maximum should succeed"),
            Some(json!(0)),
        );
    });
}

#[test]
fn test_reselect_maximum_uses_reselected_numeric_column() {
    run_seeded_calculation_test(|| {
        assert_eq!(
            Relation::<TestUser>::new()
                .select(&["name"])
                .reselect(&["id"])
                .maximum_sync("id")
                .expect("maximum should succeed"),
            Some(json!(3)),
        );
    });
}

#[test]
fn test_count_with_reselected_distinct_column() {
    run_seeded_calculation_test(|| {
        insert_user("Bob", "bobby@example.com");

        assert_eq!(
            Relation::<TestUser>::new()
                .select(&["email"])
                .reselect(&["name"])
                .distinct()
                .count_sync()
                .expect("count should succeed"),
            3,
        );
    });
}

#[test]
fn test_pluck_with_reselected_distinct_column() {
    run_seeded_calculation_test(|| {
        insert_user("Bob", "bobby@example.com");

        assert_eq!(
            Relation::<TestUser>::new()
                .select(&["email"])
                .reselect(&["name"])
                .distinct()
                .order("name", OrderDirection::Asc)
                .pluck_sync("name")
                .expect("pluck should succeed"),
            vec![json!("Alice"), json!("Bob"), json!("Carol")],
        );
    });
}

#[test]
fn test_pick_on_empty_relation() {
    run_empty_calculation_test(|| {
        assert_eq!(
            Relation::<TestUser>::new()
                .pick_sync("name")
                .expect("pick should succeed"),
            None,
        );
    });
}

#[test]
fn test_ids_with_limit_zero() {
    run_seeded_calculation_test(|| {
        assert_eq!(
            Relation::<TestUser>::new()
                .limit(0)
                .ids_sync()
                .expect("ids should succeed"),
            Vec::<i64>::new(),
        );
    });
}

#[test]
fn test_pick_one() {
    run_seeded_calculation_test(|| {
        assert_eq!(
            pick_relation(
                Relation::<TestUser>::new().order("id", OrderDirection::Asc),
                "name",
            )
            .expect("pick should succeed"),
            Some(json!("Alice")),
        );
    });
}

#[test]
fn test_pick_two() {
    run_seeded_calculation_test(|| {
        assert_eq!(
            pluck_columns_relation(
                Relation::<TestUser>::new()
                    .order("id", OrderDirection::Asc)
                    .limit(1),
                &["id", "name"],
            )
            .expect("pluck_columns should succeed"),
            vec![vec![json!(1), json!("Alice")]],
        );
    });
}

#[test]
fn test_pick_loaded_relation() {
    run_seeded_calculation_test(|| {
        let relation = Relation::<TestUser>::new().order("id", OrderDirection::Asc);
        let loaded = load_relation(relation.clone()).expect("relation should load");

        assert_eq!(loaded.len(), 3);
        assert_eq!(
            pick_relation(relation, "name").expect("pick should succeed"),
            Some(json!("Alice")),
        );
    });
}

#[test]
fn test_pick_loaded_relation_multiple_columns() {
    run_seeded_calculation_test(|| {
        let relation = Relation::<TestUser>::new().order("id", OrderDirection::Asc);
        let loaded = load_relation(relation.clone()).expect("relation should load");

        assert_eq!(loaded.len(), 3);
        assert_eq!(
            pluck_columns_relation(relation.limit(1), &["id", "name"])
                .expect("pluck_columns should succeed"),
            vec![vec![json!(1), json!("Alice")]],
        );
    });
}

#[test]
fn test_group_count_returns_group_sizes() {
    run_seeded_calculation_test(|| {
        insert_user("Bob", "bobby@example.com");

        let grouped = group_count_relation(Relation::<TestUser>::new().group("name"))
            .expect("group_count should succeed");

        assert_eq!(grouped.get(&json!("Alice")), Some(&json!(1)));
        assert_eq!(grouped.get(&json!("Bob")), Some(&json!(2)));
        assert_eq!(grouped.get(&json!("Carol")), Some(&json!(1)));
    });
}

#[test]
fn test_group_count_returns_empty_map_for_empty_scope() {
    run_seeded_calculation_test(|| {
        let grouped = group_count_relation(
            Relation::<TestUser>::new()
                .group("name")
                .r#where(HashMap::from([("name".to_owned(), json!("Nobody"))])),
        )
        .expect("group_count should succeed");

        assert!(grouped.is_empty());
    });
}

#[test]
fn test_group_count_respects_having_clause() {
    run_seeded_calculation_test(|| {
        insert_user("Bob", "bobby@example.com");

        let grouped = group_count_relation(
            Relation::<TestUser>::new()
                .group("name")
                .having("COUNT(*) > 1"),
        )
        .expect("group_count should succeed");

        assert_eq!(grouped, HashMap::from([(json!("Bob"), json!(2))]));
    });
}

#[test]
fn test_group_sum_returns_per_group_totals() {
    run_seeded_calculation_test(|| {
        insert_user("Bob", "bobby@example.com");

        let grouped = group_sum_relation(Relation::<TestUser>::new().group("name"), "id")
            .expect("group_sum should succeed");

        assert_eq!(grouped.get(&json!("Bob")), Some(&json!(6)));
    });
}

#[test]
fn test_group_average_returns_per_group_averages() {
    run_seeded_calculation_test(|| {
        insert_user("Bob", "bobby@example.com");

        let grouped = group_average_relation(Relation::<TestUser>::new().group("name"), "id")
            .expect("group_average should succeed");

        assert_eq!(grouped.get(&json!("Bob")), Some(&json!(3.0)));
    });
}

#[test]
fn test_group_minimum_returns_per_group_minima() {
    run_seeded_calculation_test(|| {
        insert_user("Bob", "bobby@example.com");

        let grouped = group_minimum_relation(Relation::<TestUser>::new().group("name"), "id")
            .expect("group_minimum should succeed");

        assert_eq!(grouped.get(&json!("Bob")), Some(&json!(2)));
    });
}

#[test]
fn test_group_maximum_returns_per_group_maxima() {
    run_seeded_calculation_test(|| {
        insert_user("Bob", "bobby@example.com");

        let grouped = group_maximum_relation(Relation::<TestUser>::new().group("name"), "id")
            .expect("group_maximum should succeed");

        assert_eq!(grouped.get(&json!("Bob")), Some(&json!(4)));
    });
}

#[test]
fn test_group_sum_respects_scope() {
    run_seeded_calculation_test(|| {
        let grouped = group_sum_relation(
            Relation::<TestUser>::new()
                .group("name")
                .r#where(HashMap::from([("name".to_owned(), json!("Bob"))])),
            "id",
        )
        .expect("group_sum should succeed");

        assert_eq!(grouped, HashMap::from([(json!("Bob"), json!(2))]));
    });
}

#[test]
fn test_group_average_respects_scope() {
    run_seeded_calculation_test(|| {
        let grouped = group_average_relation(
            Relation::<TestUser>::new()
                .group("name")
                .r#where(HashMap::from([("name".to_owned(), json!("Bob"))])),
            "id",
        )
        .expect("group_average should succeed");

        assert_eq!(grouped, HashMap::from([(json!("Bob"), json!(2.0))]));
    });
}

#[test]
fn test_group_minimum_respects_scope() {
    run_seeded_calculation_test(|| {
        let grouped = group_minimum_relation(
            Relation::<TestUser>::new()
                .group("name")
                .r#where(HashMap::from([("name".to_owned(), json!("Bob"))])),
            "id",
        )
        .expect("group_minimum should succeed");

        assert_eq!(grouped, HashMap::from([(json!("Bob"), json!(2))]));
    });
}

#[test]
fn test_group_maximum_respects_scope() {
    run_seeded_calculation_test(|| {
        let grouped = group_maximum_relation(
            Relation::<TestUser>::new()
                .group("name")
                .r#where(HashMap::from([("name".to_owned(), json!("Bob"))])),
            "id",
        )
        .expect("group_maximum should succeed");

        assert_eq!(grouped, HashMap::from([(json!("Bob"), json!(2))]));
    });
}

#[test]
fn test_pluck_columns_returns_requested_values() {
    run_seeded_calculation_test(|| {
        assert_eq!(
            pluck_columns_relation(
                Relation::<TestUser>::new().order("id", OrderDirection::Asc),
                &["id", "name"],
            )
            .expect("pluck_columns should succeed"),
            seeded_name_id_rows(),
        );
    });
}

#[test]
fn test_pluck_columns_returns_empty_for_empty_relation() {
    run_empty_calculation_test(|| {
        assert!(
            pluck_columns_relation(Relation::<TestUser>::new(), &["id", "name"])
                .expect("pluck_columns should succeed")
                .is_empty()
        );
    });
}

#[test]
fn test_pluck_columns_return_default_for_unselected_columns() {
    run_seeded_calculation_test(|| {
        assert_eq!(
            pluck_columns_relation(
                Relation::<TestUser>::new()
                    .select(&["name"])
                    .order("id", OrderDirection::Asc),
                &["id", "email"],
            )
            .expect("pluck_columns should succeed"),
            vec![
                vec![json!(0), json!("")],
                vec![json!(0), json!("")],
                vec![json!(0), json!("")],
            ],
        );
    });
}

#[test]
fn test_pick_on_scoped_relation() {
    run_seeded_calculation_test(|| {
        assert_eq!(
            pick_relation(
                Relation::<TestUser>::new()
                    .r#where(HashMap::from([("name".to_owned(), json!("Bob"))]))
                    .order("id", OrderDirection::Asc),
                "email",
            )
            .expect("pick should succeed"),
            Some(json!("bob@example.com")),
        );
    });
}

#[test]
fn test_pick_returns_default_for_unselected_column() {
    run_seeded_calculation_test(|| {
        assert_eq!(
            pick_relation(
                Relation::<TestUser>::new()
                    .select(&["name"])
                    .order("id", OrderDirection::Asc),
                "id",
            )
            .expect("pick should succeed"),
            Some(json!(0)),
        );
    });
}

#[test]
fn test_pick_uses_reselected_projection() {
    run_seeded_calculation_test(|| {
        assert_eq!(
            pick_relation(
                Relation::<TestUser>::new()
                    .select(&["name"])
                    .reselect(&["email"])
                    .order("id", OrderDirection::Asc),
                "email",
            )
            .expect("pick should succeed"),
            Some(json!("alice@example.com")),
        );
    });
}

#[test]
fn test_group_count_with_limit_returns_first_group() {
    run_seeded_calculation_test(|| {
        insert_user("Bob", "bobby@example.com");

        assert_eq!(
            Relation::<TestUser>::new()
                .group("name")
                .order("name", OrderDirection::Asc)
                .limit(1)
                .count_sync()
                .expect("count should succeed"),
            1,
        );
    });
}

#[test]
fn test_group_count_with_offset_skips_first_group() {
    run_seeded_calculation_test(|| {
        insert_user("Bob", "bobby@example.com");

        assert_eq!(
            Relation::<TestUser>::new()
                .group("name")
                .order("name", OrderDirection::Asc)
                .offset(1)
                .count_sync()
                .expect("count should succeed"),
            2,
        );
    });
}

#[test]
fn test_group_sum_with_having_clause() {
    run_seeded_calculation_test(|| {
        insert_user("Bob", "bobby@example.com");

        let grouped = group_sum_relation(
            Relation::<TestUser>::new()
                .group("name")
                .having("COUNT(*) > 1"),
            "id",
        )
        .expect("group_sum should succeed");

        assert_eq!(grouped, HashMap::from([(json!("Bob"), json!(6))]));
    });
}

#[test]
fn test_group_average_with_having_clause() {
    run_seeded_calculation_test(|| {
        insert_user("Bob", "bobby@example.com");

        let grouped = group_average_relation(
            Relation::<TestUser>::new()
                .group("name")
                .having("COUNT(*) > 1"),
            "id",
        )
        .expect("group_average should succeed");

        assert_eq!(grouped, HashMap::from([(json!("Bob"), json!(3.0))]));
    });
}

#[test]
fn test_group_minimum_with_having_clause() {
    run_seeded_calculation_test(|| {
        insert_user("Bob", "bobby@example.com");

        let grouped = group_minimum_relation(
            Relation::<TestUser>::new()
                .group("name")
                .having("COUNT(*) > 1"),
            "id",
        )
        .expect("group_minimum should succeed");

        assert_eq!(grouped, HashMap::from([(json!("Bob"), json!(2))]));
    });
}

#[test]
fn test_group_maximum_with_having_clause() {
    run_seeded_calculation_test(|| {
        insert_user("Bob", "bobby@example.com");

        let grouped = group_maximum_relation(
            Relation::<TestUser>::new()
                .group("name")
                .having("COUNT(*) > 1"),
            "id",
        )
        .expect("group_maximum should succeed");

        assert_eq!(grouped, HashMap::from([(json!("Bob"), json!(4))]));
    });
}

#[test]
fn test_pick_returns_none_for_empty_scope() {
    run_seeded_calculation_test(|| {
        assert_eq!(
            pick_relation(
                Relation::<TestUser>::new()
                    .r#where(HashMap::from([("name".to_owned(), json!("Nobody"))])),
                "name",
            )
            .expect("pick should succeed"),
            None,
        );
    });
}

#[test]
fn test_pluck_columns_with_limit_zero() {
    run_seeded_calculation_test(|| {
        assert_eq!(
            pluck_columns_relation(Relation::<TestUser>::new().limit(0), &["id", "name"])
                .expect("pluck_columns should succeed"),
            Vec::<Vec<Value>>::new(),
        );
    });
}

#[test]
fn test_pluck_columns_on_scoped_relation() {
    run_seeded_calculation_test(|| {
        assert_eq!(
            pluck_columns_relation(
                Relation::<TestUser>::new()
                    .r#where(HashMap::from([("name".to_owned(), json!("Bob"))]))
                    .order("id", OrderDirection::Asc),
                &["id", "email"],
            )
            .expect("pluck_columns should succeed"),
            vec![vec![json!(2), json!("bob@example.com")]],
        );
    });
}

#[test]
fn test_group_sum_returns_empty_map_for_empty_scope() {
    run_seeded_calculation_test(|| {
        let grouped = group_sum_relation(
            Relation::<TestUser>::new()
                .group("name")
                .r#where(HashMap::from([("name".to_owned(), json!("Nobody"))])),
            "id",
        )
        .expect("group_sum should succeed");

        assert!(grouped.is_empty());
    });
}

#[test]
fn test_group_average_returns_empty_map_for_empty_scope() {
    run_seeded_calculation_test(|| {
        let grouped = group_average_relation(
            Relation::<TestUser>::new()
                .group("name")
                .r#where(HashMap::from([("name".to_owned(), json!("Nobody"))])),
            "id",
        )
        .expect("group_average should succeed");

        assert!(grouped.is_empty());
    });
}

#[cfg(test)]
mod wave3_calculation_sanitization_ports {
    use std::collections::HashMap;

    use crate::sanitization::{sanitize_sql_array, sanitize_sql_hash};
    use serde_json::{Value, json};

    macro_rules! sanitize_sql_array_case {
        ($name:ident, [$($value:expr),* $(,)?], $expected:expr) => {
            #[test]
            fn $name() {
                let values = vec![$($value),*];
                assert_eq!(sanitize_sql_array(&values), $expected);
            }
        };
    }

    sanitize_sql_array_case!(sanitize_sql_array_empty_case, [], "");
    sanitize_sql_array_case!(sanitize_sql_array_single_integer_case, [json!(1)], "1");
    sanitize_sql_array_case!(sanitize_sql_array_single_string_case, [json!("Alice")], "'Alice'");
    sanitize_sql_array_case!(sanitize_sql_array_single_null_case, [Value::Null], "NULL");
    sanitize_sql_array_case!(sanitize_sql_array_boolean_values_case, [json!(true), json!(false)], "TRUE, FALSE");
    sanitize_sql_array_case!(sanitize_sql_array_mixed_scalars_case, [json!(1), json!("Alice"), Value::Null], "1, 'Alice', NULL");
    sanitize_sql_array_case!(sanitize_sql_array_apostrophe_case, [json!("O'Brien")], "'O''Brien'");
    sanitize_sql_array_case!(sanitize_sql_array_unicode_case, [json!("Здравствуйте")], "'Здравствуйте'");
    sanitize_sql_array_case!(sanitize_sql_array_emoji_case, [json!("😀")], "'😀'");
    sanitize_sql_array_case!(sanitize_sql_array_multiline_case, [json!("line1\nline2")], "'line1\nline2'");
    sanitize_sql_array_case!(sanitize_sql_array_backslash_case, [json!("C:\\Temp")], "'C:\\Temp'");
    sanitize_sql_array_case!(sanitize_sql_array_positive_float_case, [json!(3.14)], "3.14");
    sanitize_sql_array_case!(sanitize_sql_array_negative_float_case, [json!(-2.5)], "-2.5");
    sanitize_sql_array_case!(sanitize_sql_array_zero_case, [json!(0)], "0");
    sanitize_sql_array_case!(sanitize_sql_array_negative_integer_case, [json!(-7)], "-7");
    sanitize_sql_array_case!(sanitize_sql_array_object_case, [json!({"role": "admin"})], "'{\"role\":\"admin\"}'");
    sanitize_sql_array_case!(sanitize_sql_array_array_case, [json!([1, 2, 3])], "'[1,2,3]'");
    sanitize_sql_array_case!(sanitize_sql_array_multiple_strings_case, [json!("Alice"), json!("Bob")], "'Alice', 'Bob'");
    sanitize_sql_array_case!(sanitize_sql_array_mixed_json_values_case, [json!({"ok": true}), json!([4, 5])], "'{\"ok\":true}', '[4,5]'");
    sanitize_sql_array_case!(sanitize_sql_array_injection_literal_case, [json!("Robert'); DROP TABLE users;--")], "'Robert''); DROP TABLE users;--'");
    sanitize_sql_array_case!(sanitize_sql_array_empty_and_text_case, [json!(""), json!("filled")], "'', 'filled'");
    sanitize_sql_array_case!(sanitize_sql_array_numbers_and_bools_case, [json!(5), json!(true), json!(8)], "5, TRUE, 8");
    sanitize_sql_array_case!(sanitize_sql_array_null_between_scalars_case, [json!(1), Value::Null, json!(2)], "1, NULL, 2");
    sanitize_sql_array_case!(sanitize_sql_array_percent_and_underscore_case, [json!("100%_done")], "'100%_done'");
    sanitize_sql_array_case!(sanitize_sql_array_duplicate_values_case, [json!(1), json!(1), json!(1)], "1, 1, 1");

    macro_rules! sanitize_sql_hash_case {
        ($name:ident, { $($key:literal => $value:expr),* $(,)? }, $expected:expr) => {
            #[test]
            fn $name() {
                let hash = HashMap::from([$(($key.to_owned(), $value)),*]);
                assert_eq!(sanitize_sql_hash(&hash), $expected);
            }
        };
    }

    sanitize_sql_hash_case!(sanitize_sql_hash_empty_case, {}, "");
    sanitize_sql_hash_case!(sanitize_sql_hash_single_string_case, { "name" => json!("Alice") }, "name = 'Alice'");
    sanitize_sql_hash_case!(sanitize_sql_hash_single_null_case, { "deleted_at" => Value::Null }, "deleted_at IS NULL");
    sanitize_sql_hash_case!(sanitize_sql_hash_sorts_two_keys_case, { "name" => json!("Alice"), "age" => json!(30) }, "age = 30 AND name = 'Alice'");
    sanitize_sql_hash_case!(sanitize_sql_hash_sorts_three_keys_case, { "status" => json!("open"), "age" => json!(30), "name" => json!("Alice") }, "age = 30 AND name = 'Alice' AND status = 'open'");
    sanitize_sql_hash_case!(sanitize_sql_hash_apostrophe_case, { "name" => json!("O'Brien") }, "name = 'O''Brien'");
    sanitize_sql_hash_case!(sanitize_sql_hash_true_case, { "active" => json!(true) }, "active = TRUE");
    sanitize_sql_hash_case!(sanitize_sql_hash_false_case, { "active" => json!(false) }, "active = FALSE");
    sanitize_sql_hash_case!(sanitize_sql_hash_positive_float_case, { "score" => json!(3.5) }, "score = 3.5");
    sanitize_sql_hash_case!(sanitize_sql_hash_negative_float_case, { "score" => json!(-2.5) }, "score = -2.5");
    sanitize_sql_hash_case!(sanitize_sql_hash_negative_integer_case, { "delta" => json!(-7) }, "delta = -7");
    sanitize_sql_hash_case!(sanitize_sql_hash_object_case, { "meta" => json!({"role": "admin"}) }, "meta = '{\"role\":\"admin\"}'");
    sanitize_sql_hash_case!(sanitize_sql_hash_array_case, { "ids" => json!([1, 2, 3]) }, "ids = '[1,2,3]'");
    sanitize_sql_hash_case!(sanitize_sql_hash_mixed_null_and_string_case, { "name" => json!("Alice"), "deleted_at" => Value::Null }, "deleted_at IS NULL AND name = 'Alice'");
    sanitize_sql_hash_case!(sanitize_sql_hash_injection_literal_case, { "name" => json!("Robert'); DROP TABLE users;--") }, "name = 'Robert''); DROP TABLE users;--'");
    sanitize_sql_hash_case!(sanitize_sql_hash_unicode_value_case, { "display_name" => json!("Здравствуйте") }, "display_name = 'Здравствуйте'");
    sanitize_sql_hash_case!(sanitize_sql_hash_emoji_value_case, { "emoji" => json!("😀") }, "emoji = '😀'");
    sanitize_sql_hash_case!(sanitize_sql_hash_backslash_value_case, { "path" => json!("C:\\Temp") }, "path = 'C:\\Temp'");
    sanitize_sql_hash_case!(sanitize_sql_hash_empty_string_case, { "title" => json!("") }, "title = ''");
    sanitize_sql_hash_case!(sanitize_sql_hash_percent_value_case, { "pattern" => json!("100%") }, "pattern = '100%'");
    sanitize_sql_hash_case!(sanitize_sql_hash_mixed_case_key_sorting_case, { "b" => json!(1), "A" => json!(2) }, "A = 2 AND b = 1");
    sanitize_sql_hash_case!(sanitize_sql_hash_duplicate_style_values_case, { "left" => json!(1), "right" => json!(1) }, "left = 1 AND right = 1");
    sanitize_sql_hash_case!(sanitize_sql_hash_null_and_boolean_case, { "active" => json!(true), "deleted_at" => Value::Null }, "active = TRUE AND deleted_at IS NULL");
    sanitize_sql_hash_case!(sanitize_sql_hash_json_mix_case, { "meta" => json!({"ok": true}), "ids" => json!([4, 5]) }, "ids = '[4,5]' AND meta = '{\"ok\":true}'");
    sanitize_sql_hash_case!(sanitize_sql_hash_zero_case, { "count" => json!(0) }, "count = 0");
}