rustlite 0.8.0

/// Tests for GROUP BY, HAVING, and aggregate functions
use rustlite::{Column, Database, ExecutionContext, Row, Value};

#[test]
fn test_count_aggregate() {
    let db = Database::in_memory().unwrap();
    let plan = db.prepare("SELECT COUNT(*) AS total FROM users").unwrap();

    let mut context = ExecutionContext::new();
    context.data.insert(
        "users".to_string(),
        vec![
            Row {
                columns: vec![Column {
                    name: "id".to_string(),
                    alias: None,
                }],
                values: vec![Value::Integer(1)],
            },
            Row {
                columns: vec![Column {
                    name: "id".to_string(),
                    alias: None,
                }],
                values: vec![Value::Integer(2)],
            },
            Row {
                columns: vec![Column {
                    name: "id".to_string(),
                    alias: None,
                }],
                values: vec![Value::Integer(3)],
            },
        ],
    );

    let results = db.execute_plan(&plan, context).unwrap();
    assert_eq!(results.len(), 1);
    assert_eq!(results[0].values[0], Value::Integer(3));
}

#[test]
fn test_sum_aggregate() {
    let db = Database::in_memory().unwrap();
    let plan = db
        .prepare("SELECT SUM(amount) AS total FROM transactions")
        .unwrap();

    let mut context = ExecutionContext::new();
    context.data.insert(
        "transactions".to_string(),
        vec![
            Row {
                columns: vec![Column {
                    name: "amount".to_string(),
                    alias: None,
                }],
                values: vec![Value::Integer(100)],
            },
            Row {
                columns: vec![Column {
                    name: "amount".to_string(),
                    alias: None,
                }],
                values: vec![Value::Integer(200)],
            },
            Row {
                columns: vec![Column {
                    name: "amount".to_string(),
                    alias: None,
                }],
                values: vec![Value::Integer(300)],
            },
        ],
    );

    let results = db.execute_plan(&plan, context).unwrap();
    assert_eq!(results.len(), 1);
    assert_eq!(results[0].values[0], Value::Integer(600));
}

#[test]
fn test_avg_aggregate() {
    let db = Database::in_memory().unwrap();
    let plan = db
        .prepare("SELECT AVG(score) AS average FROM grades")
        .unwrap();

    let mut context = ExecutionContext::new();
    context.data.insert(
        "grades".to_string(),
        vec![
            Row {
                columns: vec![Column {
                    name: "score".to_string(),
                    alias: None,
                }],
                values: vec![Value::Integer(80)],
            },
            Row {
                columns: vec![Column {
                    name: "score".to_string(),
                    alias: None,
                }],
                values: vec![Value::Integer(90)],
            },
            Row {
                columns: vec![Column {
                    name: "score".to_string(),
                    alias: None,
                }],
                values: vec![Value::Integer(100)],
            },
        ],
    );

    let results = db.execute_plan(&plan, context).unwrap();
    assert_eq!(results.len(), 1);
    assert_eq!(results[0].values[0], Value::Float(90.0));
}

#[test]
fn test_min_max_aggregates() {
    let db = Database::in_memory().unwrap();
    let plan = db
        .prepare("SELECT MIN(price) AS min_price, MAX(price) AS max_price FROM products")
        .unwrap();

    let mut context = ExecutionContext::new();
    context.data.insert(
        "products".to_string(),
        vec![
            Row {
                columns: vec![Column {
                    name: "price".to_string(),
                    alias: None,
                }],
                values: vec![Value::Integer(10)],
            },
            Row {
                columns: vec![Column {
                    name: "price".to_string(),
                    alias: None,
                }],
                values: vec![Value::Integer(50)],
            },
            Row {
                columns: vec![Column {
                    name: "price".to_string(),
                    alias: None,
                }],
                values: vec![Value::Integer(25)],
            },
        ],
    );

    let results = db.execute_plan(&plan, context).unwrap();
    assert_eq!(results.len(), 1);
    assert_eq!(results[0].values[0], Value::Integer(10));
    assert_eq!(results[0].values[1], Value::Integer(50));
}

#[test]
fn test_group_by_with_count() {
    let db = Database::in_memory().unwrap();
    let plan = db
        .prepare("SELECT department, COUNT(*) AS employee_count FROM employees GROUP BY department")
        .unwrap();

    let mut context = ExecutionContext::new();
    context.data.insert(
        "employees".to_string(),
        vec![
            Row {
                columns: vec![
                    Column {
                        name: "department".to_string(),
                        alias: None,
                    },
                    Column {
                        name: "name".to_string(),
                        alias: None,
                    },
                ],
                values: vec![
                    Value::String("Engineering".to_string()),
                    Value::String("Alice".to_string()),
                ],
            },
            Row {
                columns: vec![
                    Column {
                        name: "department".to_string(),
                        alias: None,
                    },
                    Column {
                        name: "name".to_string(),
                        alias: None,
                    },
                ],
                values: vec![
                    Value::String("Engineering".to_string()),
                    Value::String("Bob".to_string()),
                ],
            },
            Row {
                columns: vec![
                    Column {
                        name: "department".to_string(),
                        alias: None,
                    },
                    Column {
                        name: "name".to_string(),
                        alias: None,
                    },
                ],
                values: vec![
                    Value::String("Sales".to_string()),
                    Value::String("Charlie".to_string()),
                ],
            },
        ],
    );

    let results = db.execute_plan(&plan, context).unwrap();
    assert_eq!(results.len(), 2); // 2 departments

    // Check that we have both departments (order may vary)
    let eng_row = results
        .iter()
        .find(|r| r.values[0] == Value::String("Engineering".to_string()))
        .unwrap();
    assert_eq!(eng_row.values[1], Value::Integer(2));

    let sales_row = results
        .iter()
        .find(|r| r.values[0] == Value::String("Sales".to_string()))
        .unwrap();
    assert_eq!(sales_row.values[1], Value::Integer(1));
}

#[test]
fn test_group_by_with_sum() {
    let db = Database::in_memory().unwrap();
    let plan = db
        .prepare("SELECT customer_id, SUM(amount) AS total_spent FROM orders GROUP BY customer_id")
        .unwrap();

    let mut context = ExecutionContext::new();
    context.data.insert(
        "orders".to_string(),
        vec![
            Row {
                columns: vec![
                    Column {
                        name: "customer_id".to_string(),
                        alias: None,
                    },
                    Column {
                        name: "amount".to_string(),
                        alias: None,
                    },
                ],
                values: vec![Value::Integer(1), Value::Integer(100)],
            },
            Row {
                columns: vec![
                    Column {
                        name: "customer_id".to_string(),
                        alias: None,
                    },
                    Column {
                        name: "amount".to_string(),
                        alias: None,
                    },
                ],
                values: vec![Value::Integer(1), Value::Integer(200)],
            },
            Row {
                columns: vec![
                    Column {
                        name: "customer_id".to_string(),
                        alias: None,
                    },
                    Column {
                        name: "amount".to_string(),
                        alias: None,
                    },
                ],
                values: vec![Value::Integer(2), Value::Integer(150)],
            },
        ],
    );

    let results = db.execute_plan(&plan, context).unwrap();
    assert_eq!(results.len(), 2); // 2 customers

    let customer1 = results
        .iter()
        .find(|r| r.values[0] == Value::Integer(1))
        .unwrap();
    assert_eq!(customer1.values[1], Value::Integer(300));

    let customer2 = results
        .iter()
        .find(|r| r.values[0] == Value::Integer(2))
        .unwrap();
    assert_eq!(customer2.values[1], Value::Integer(150));
}

#[test]
fn test_group_by_multiple_columns() {
    let db = Database::in_memory().unwrap();
    let plan = db
        .prepare("SELECT category, status FROM items GROUP BY category, status")
        .unwrap();

    let mut context = ExecutionContext::new();
    context.data.insert(
        "items".to_string(),
        vec![
            Row {
                columns: vec![
                    Column {
                        name: "category".to_string(),
                        alias: None,
                    },
                    Column {
                        name: "status".to_string(),
                        alias: None,
                    },
                ],
                values: vec![
                    Value::String("Electronics".to_string()),
                    Value::String("Active".to_string()),
                ],
            },
            Row {
                columns: vec![
                    Column {
                        name: "category".to_string(),
                        alias: None,
                    },
                    Column {
                        name: "status".to_string(),
                        alias: None,
                    },
                ],
                values: vec![
                    Value::String("Electronics".to_string()),
                    Value::String("Active".to_string()),
                ],
            },
            Row {
                columns: vec![
                    Column {
                        name: "category".to_string(),
                        alias: None,
                    },
                    Column {
                        name: "status".to_string(),
                        alias: None,
                    },
                ],
                values: vec![
                    Value::String("Electronics".to_string()),
                    Value::String("Inactive".to_string()),
                ],
            },
            Row {
                columns: vec![
                    Column {
                        name: "category".to_string(),
                        alias: None,
                    },
                    Column {
                        name: "status".to_string(),
                        alias: None,
                    },
                ],
                values: vec![
                    Value::String("Books".to_string()),
                    Value::String("Active".to_string()),
                ],
            },
        ],
    );

    let results = db.execute_plan(&plan, context).unwrap();
    assert_eq!(results.len(), 3); // 3 unique combinations

    // Check that we have Electronics-Active
    let electronics_active = results
        .iter()
        .find(|r| {
            r.values[0] == Value::String("Electronics".to_string())
                && r.values[1] == Value::String("Active".to_string())
        })
        .unwrap();
    // Just verify the combination exists (no count since we removed it)
    assert!(electronics_active.values.len() == 2);
}

#[test]
fn test_having_clause() {
    let db = Database::in_memory().unwrap();
    let plan = db
        .prepare(
            "SELECT department FROM employees GROUP BY department HAVING department = 'Engineering'",
        )
        .unwrap();

    let mut context = ExecutionContext::new();
    context.data.insert(
        "employees".to_string(),
        vec![
            Row {
                columns: vec![Column {
                    name: "department".to_string(),
                    alias: None,
                }],
                values: vec![Value::String("Engineering".to_string())],
            },
            Row {
                columns: vec![Column {
                    name: "department".to_string(),
                    alias: None,
                }],
                values: vec![Value::String("Engineering".to_string())],
            },
            Row {
                columns: vec![Column {
                    name: "department".to_string(),
                    alias: None,
                }],
                values: vec![Value::String("Sales".to_string())],
            },
            Row {
                columns: vec![Column {
                    name: "department".to_string(),
                    alias: None,
                }],
                values: vec![Value::String("HR".to_string())],
            },
        ],
    );

    let results = db.execute_plan(&plan, context).unwrap();

    // Only Engineering should appear (HAVING filters for it)
    assert_eq!(results.len(), 1);
    assert_eq!(
        results[0].values[0],
        Value::String("Engineering".to_string())
    );
}

#[test]
fn test_count_with_nulls() {
    let db = Database::in_memory().unwrap();

    // Test COUNT(*) vs COUNT(column) with NULL values
    let plan_star = db.prepare("SELECT COUNT(*) AS total FROM data").unwrap();
    let plan_column = db
        .prepare("SELECT COUNT(value) AS non_null FROM data")
        .unwrap();

    let mut context = ExecutionContext::new();
    context.data.insert(
        "data".to_string(),
        vec![
            Row {
                columns: vec![Column {
                    name: "value".to_string(),
                    alias: None,
                }],
                values: vec![Value::Integer(10)],
            },
            Row {
                columns: vec![Column {
                    name: "value".to_string(),
                    alias: None,
                }],
                values: vec![Value::Null], // NULL value
            },
            Row {
                columns: vec![Column {
                    name: "value".to_string(),
                    alias: None,
                }],
                values: vec![Value::Integer(20)],
            },
            Row {
                columns: vec![Column {
                    name: "value".to_string(),
                    alias: None,
                }],
                values: vec![Value::Null], // Another NULL
            },
        ],
    );

    // COUNT(*) should count all rows including NULLs
    let results_star = db.execute_plan(&plan_star, context.clone()).unwrap();
    assert_eq!(results_star.len(), 1);
    assert_eq!(results_star[0].values[0], Value::Integer(4)); // All 4 rows

    // COUNT(value) should count only non-NULL values
    let results_column = db.execute_plan(&plan_column, context).unwrap();
    assert_eq!(results_column.len(), 1);
    assert_eq!(results_column[0].values[0], Value::Integer(2)); // Only 2 non-NULL values
}