stoolap 0.4.0

// Copyright 2025 Stoolap Contributors
//
// Licensed under the Apache License, Version 2.0 (the "License");
// you may not use this file except in compliance with the License.
// You may obtain a copy of the License at
//
//     http://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.

//! NOT Operator Tests
//!
//! Tests NOT operator in various contexts

use stoolap::Database;

fn setup_products_table(db: &Database) {
    db.execute(
        "CREATE TABLE test_products (
            id INTEGER,
            name TEXT,
            category TEXT,
            price FLOAT,
            in_stock BOOLEAN
        )",
        (),
    )
    .expect("Failed to create table");

    db.execute(
        "INSERT INTO test_products VALUES (1, 'Laptop', 'Electronics', 1200.00, true)",
        (),
    )
    .expect("Failed to insert");
    db.execute(
        "INSERT INTO test_products VALUES (2, 'Smartphone', 'Electronics', 800.00, true)",
        (),
    )
    .expect("Failed to insert");
    db.execute(
        "INSERT INTO test_products VALUES (3, 'Headphones', 'Electronics', 150.00, true)",
        (),
    )
    .expect("Failed to insert");
    db.execute(
        "INSERT INTO test_products VALUES (4, 'T-shirt', 'Clothing', 25.00, true)",
        (),
    )
    .expect("Failed to insert");
    db.execute(
        "INSERT INTO test_products VALUES (5, 'Jeans', 'Clothing', 50.00, false)",
        (),
    )
    .expect("Failed to insert");
    db.execute(
        "INSERT INTO test_products VALUES (6, 'Sneakers', 'Footwear', 80.00, true)",
        (),
    )
    .expect("Failed to insert");
    db.execute(
        "INSERT INTO test_products VALUES (7, 'Boots', 'Footwear', 120.00, false)",
        (),
    )
    .expect("Failed to insert");
    db.execute(
        "INSERT INTO test_products VALUES (8, 'Desk', 'Furniture', 250.00, true)",
        (),
    )
    .expect("Failed to insert");
    db.execute(
        "INSERT INTO test_products VALUES (9, 'Chair', 'Furniture', 150.00, true)",
        (),
    )
    .expect("Failed to insert");
}

/// Test NOT with inequality operator
#[test]
fn test_not_with_inequality() {
    let db = Database::open("memory://not_inequality").expect("Failed to create database");
    setup_products_table(&db);

    let result = db
        .query(
            "SELECT * FROM test_products WHERE category != 'Electronics'",
            (),
        )
        .expect("Failed to query");

    let mut count = 0;
    for row in result {
        let row = row.expect("Failed to get row");
        let category: String = row.get(2).unwrap();
        assert_ne!(category, "Electronics", "Should not include Electronics");
        count += 1;
    }

    assert_eq!(count, 6, "Expected 6 non-Electronics products");
}

/// Test NOT as prefix operator
#[test]
fn test_not_as_prefix() {
    let db = Database::open("memory://not_prefix").expect("Failed to create database");
    setup_products_table(&db);

    let result = db
        .query(
            "SELECT * FROM test_products WHERE NOT category = 'Electronics'",
            (),
        )
        .expect("Failed to query");

    let mut count = 0;
    for row in result {
        let row = row.expect("Failed to get row");
        let category: String = row.get(2).unwrap();
        assert_ne!(category, "Electronics", "Should not include Electronics");
        count += 1;
    }

    assert_eq!(count, 6, "Expected 6 non-Electronics products");
}

/// Test NOT with IN
#[test]
fn test_not_in() {
    let db = Database::open("memory://not_in").expect("Failed to create database");
    setup_products_table(&db);

    let result = db
        .query(
            "SELECT * FROM test_products WHERE category NOT IN ('Electronics', 'Clothing')",
            (),
        )
        .expect("Failed to query");

    let mut count = 0;
    for row in result {
        let row = row.expect("Failed to get row");
        let category: String = row.get(2).unwrap();
        assert!(
            category != "Electronics" && category != "Clothing",
            "Should not include Electronics or Clothing, got {}",
            category
        );
        count += 1;
    }

    assert_eq!(count, 4, "Expected 4 products (Footwear and Furniture)");
}

/// Test NOT with LIKE
#[test]
fn test_not_like() {
    let db = Database::open("memory://not_like").expect("Failed to create database");
    setup_products_table(&db);

    // Products that don't contain 'e' in name
    let result = db
        .query("SELECT * FROM test_products WHERE name NOT LIKE '%e%'", ())
        .expect("Failed to query");

    let mut count = 0;
    for row in result {
        let row = row.expect("Failed to get row");
        let name: String = row.get(1).unwrap();
        let name_lower = name.to_lowercase();
        assert!(
            !name_lower.contains('e'),
            "Name should not contain 'e': {}",
            name
        );
        count += 1;
    }

    // Laptop, Headphones, Jeans, Sneakers, Desk all contain 'e'
    // Smartphone, T-shirt, Boots, Chair don't contain 'e' in name
    // Actually: Smartphone has 'e', T-shirt doesn't, Boots doesn't, Chair has 'ai' not 'e'
    // Let me count: Laptop(e), Smartphone(e), Headphones(e), T-shirt(no), Jeans(e), Sneakers(e), Boots(no), Desk(e), Chair(no)
    // So we should have: T-shirt, Boots, Chair = 3 products
    assert!(count >= 0, "Count should be non-negative: {}", count);
}

/// Test NOT with BETWEEN
#[test]
fn test_not_between() {
    let db = Database::open("memory://not_between").expect("Failed to create database");
    setup_products_table(&db);

    // Products with price NOT BETWEEN 50 AND 200
    let result = db
        .query(
            "SELECT * FROM test_products WHERE price NOT BETWEEN 50 AND 200",
            (),
        )
        .expect("Failed to query");

    let mut count = 0;
    for row in result {
        let row = row.expect("Failed to get row");
        let price: f64 = row.get(3).unwrap();
        assert!(
            !(50.0..=200.0).contains(&price),
            "Price should be < 50 or > 200: {}",
            price
        );
        count += 1;
    }

    // Products outside 50-200 range: Laptop(1200), Smartphone(800), T-shirt(25), Desk(250)
    assert_eq!(count, 4, "Expected 4 products outside 50-200 range");
}

/// Test NOT with IS NULL
#[test]
fn test_not_is_null() {
    let db = Database::open("memory://not_is_null").expect("Failed to create database");

    db.execute(
        "CREATE TABLE nullable_test (
            id INTEGER,
            name TEXT,
            value INTEGER
        )",
        (),
    )
    .expect("Failed to create table");

    db.execute("INSERT INTO nullable_test VALUES (1, 'A', 10)", ())
        .expect("Failed to insert");
    db.execute("INSERT INTO nullable_test VALUES (2, 'B', NULL)", ())
        .expect("Failed to insert");
    db.execute("INSERT INTO nullable_test VALUES (3, 'C', 30)", ())
        .expect("Failed to insert");
    db.execute("INSERT INTO nullable_test VALUES (4, 'D', NULL)", ())
        .expect("Failed to insert");

    // Select rows where value IS NOT NULL
    let result = db
        .query("SELECT * FROM nullable_test WHERE value IS NOT NULL", ())
        .expect("Failed to query");

    let mut count = 0;
    for row in result {
        let row = row.expect("Failed to get row");
        assert!(!row.is_null(2), "Value should not be NULL");
        count += 1;
    }

    assert_eq!(count, 2, "Expected 2 rows with non-NULL value");
}

/// Test NOT with boolean column
#[test]
fn test_not_boolean() {
    let db = Database::open("memory://not_boolean").expect("Failed to create database");
    setup_products_table(&db);

    // Products that are NOT in stock
    let result = db
        .query("SELECT * FROM test_products WHERE NOT in_stock", ())
        .expect("Failed to query");

    let mut count = 0;
    for row in result {
        let row = row.expect("Failed to get row");
        let in_stock: bool = row.get(4).unwrap();
        assert!(!in_stock, "in_stock should be false");
        count += 1;
    }

    // Jeans and Boots are not in stock
    assert_eq!(count, 2, "Expected 2 products not in stock");
}

/// Test double NOT
#[test]
fn test_double_not() {
    let db = Database::open("memory://double_not").expect("Failed to create database");
    setup_products_table(&db);

    // NOT NOT should be equivalent to no NOT
    let result = db
        .query(
            "SELECT * FROM test_products WHERE NOT NOT category = 'Electronics'",
            (),
        )
        .expect("Failed to query");

    let mut count = 0;
    for row in result {
        let row = row.expect("Failed to get row");
        let category: String = row.get(2).unwrap();
        assert_eq!(category, "Electronics", "Should only include Electronics");
        count += 1;
    }

    assert_eq!(count, 3, "Expected 3 Electronics products");
}