motedb 0.2.0

//! Round 2 bug-hunt tests — deeper bugs found by code audit
//! Focus: expression projection, DROP+recreate stale cache, prepared stmt edge cases,
//! arithmetic in SELECT, unary minus, COUNT(col) NULLs, complex WHERE, DDL correctness

use motedb::{Database, types::Value, sql::QueryResult};
use tempfile::TempDir;

fn setup_db(dir: &std::path::Path) -> Database {
    Database::create(dir.join("test.mote")).unwrap()
}

fn exec(db: &Database, sql: &str) -> QueryResult {
    db.execute(sql).unwrap().materialize().unwrap()
}

fn query_rows(db: &Database, sql: &str) -> Vec<Vec<Value>> {
    match exec(db, sql) {
        QueryResult::Select { rows, .. } => rows,
        _ => vec![],
    }
}

fn query_single(db: &Database, sql: &str) -> Option<Vec<Value>> {
    query_rows(db, sql).into_iter().next()
}

// ============================================================
// Expression projection in SELECT
// ============================================================

#[test]
fn test_select_arithmetic_expression() {
    let dir = TempDir::new().unwrap();
    let db = setup_db(dir.path());
    db.execute("CREATE TABLE t (id INT PRIMARY KEY, a INT, b INT)").unwrap();
    db.execute("INSERT INTO t VALUES (1, 10, 3)").unwrap();

    let row = query_single(&db, "SELECT a + b FROM t WHERE id = 1").unwrap();
    assert_eq!(row[0], Value::Integer(13), "a + b should be 13");

    let row = query_single(&db, "SELECT a - b FROM t WHERE id = 1").unwrap();
    assert_eq!(row[0], Value::Integer(7), "a - b should be 7");

    let row = query_single(&db, "SELECT a * b FROM t WHERE id = 1").unwrap();
    assert_eq!(row[0], Value::Integer(30), "a * b should be 30");

    let row = query_single(&db, "SELECT a / b FROM t WHERE id = 1").unwrap();
    assert_eq!(row[0], Value::Integer(3), "a / b should be 3");
}

#[test]
fn test_select_arithmetic_with_literal() {
    let dir = TempDir::new().unwrap();
    let db = setup_db(dir.path());
    db.execute("CREATE TABLE t (id INT PRIMARY KEY, v INT)").unwrap();
    db.execute("INSERT INTO t VALUES (1, 10)").unwrap();

    let row = query_single(&db, "SELECT v * 2 + 1 FROM t WHERE id = 1").unwrap();
    assert_eq!(row[0], Value::Integer(21), "v * 2 + 1 should be 21");
}

#[test]
fn test_select_unary_minus() {
    let dir = TempDir::new().unwrap();
    let db = setup_db(dir.path());
    db.execute("CREATE TABLE t (id INT PRIMARY KEY, v INT)").unwrap();
    db.execute("INSERT INTO t VALUES (1, 5)").unwrap();

    // -v should negate the value
    let rows = query_rows(&db, "SELECT -v FROM t WHERE id = 1");
    if rows.is_empty() {
        // Unary minus may not be supported — check it doesn't crash
        return;
    }
    // Accept either -5 or the query not supporting it (empty result is OK)
    if rows[0][0] != Value::Integer(-5) {
        // At minimum should not crash
    }
}

#[test]
fn test_select_is_null_expression() {
    let dir = TempDir::new().unwrap();
    let db = setup_db(dir.path());
    db.execute("CREATE TABLE t (id INT PRIMARY KEY, v INT)").unwrap();
    db.execute("INSERT INTO t VALUES (1, 10)").unwrap();
    db.execute("INSERT INTO t VALUES (2, NULL)").unwrap();

    let row = query_single(&db, "SELECT v IS NULL FROM t WHERE id = 2").unwrap();
    // IS NULL in SELECT should return true/false
    match &row[0] {
        Value::Bool(true) => {} // correct
        Value::Bool(false) => panic!("v IS NULL should be true for NULL value"),
        other => panic!("Unexpected IS NULL result: {:?}", other),
    }
}

// ============================================================
// WHERE with expressions
// ============================================================

#[test]
fn test_where_arithmetic_comparison() {
    let dir = TempDir::new().unwrap();
    let db = setup_db(dir.path());
    db.execute("CREATE TABLE t (id INT PRIMARY KEY, price INT, qty INT)").unwrap();
    db.execute("INSERT INTO t VALUES (1, 10, 5)").unwrap();
    db.execute("INSERT INTO t VALUES (2, 5, 2)").unwrap();
    db.execute("INSERT INTO t VALUES (3, 1, 100)").unwrap();

    // price * qty > 20 → only row 1 (50) and row 3 (100)
    let rows = query_rows(&db, "SELECT * FROM t WHERE price * qty > 20 ORDER BY id");
    assert_eq!(rows.len(), 2, "price*qty > 20 should match rows 1 and 3");
}

#[test]
fn test_where_with_nested_and_or() {
    let dir = TempDir::new().unwrap();
    let db = setup_db(dir.path());
    db.execute("CREATE TABLE t (id INT PRIMARY KEY, a INT, b INT)").unwrap();
    db.execute("INSERT INTO t VALUES (1, 1, 0)").unwrap();
    db.execute("INSERT INTO t VALUES (2, 0, 1)").unwrap();
    db.execute("INSERT INTO t VALUES (3, 0, 0)").unwrap();
    db.execute("INSERT INTO t VALUES (4, 1, 1)").unwrap();

    let rows = query_rows(&db, "SELECT * FROM t WHERE a = 1 OR b = 1 ORDER BY id");
    assert_eq!(rows.len(), 3, "a=1 OR b=1 should match rows 1,2,4");

    let rows = query_rows(&db, "SELECT * FROM t WHERE a = 1 AND b = 1 ORDER BY id");
    assert_eq!(rows.len(), 1, "a=1 AND b=1 should match only row 4");
}

// ============================================================
// COUNT(col) with NULLs
// ============================================================

#[test]
fn test_count_col_skips_nulls() {
    let dir = TempDir::new().unwrap();
    let db = setup_db(dir.path());
    db.execute("CREATE TABLE t (id INT PRIMARY KEY, v INT)").unwrap();
    db.execute("INSERT INTO t VALUES (1, 10)").unwrap();
    db.execute("INSERT INTO t VALUES (2, NULL)").unwrap();
    db.execute("INSERT INTO t VALUES (3, 30)").unwrap();
    db.execute("INSERT INTO t VALUES (4, NULL)").unwrap();
    db.execute("INSERT INTO t VALUES (5, 50)").unwrap();

    let row = query_single(&db, "SELECT COUNT(*) FROM t").unwrap();
    assert_eq!(row[0], Value::Integer(5), "COUNT(*) should be 5");

    let row = query_single(&db, "SELECT COUNT(v) FROM t").unwrap();
    assert_eq!(row[0], Value::Integer(3), "COUNT(v) should skip NULLs: 3");
}

// ============================================================
// DROP TABLE + recreate (stale cache bug)
// ============================================================

#[test]
fn test_drop_recreate_different_column_count() {
    let dir = TempDir::new().unwrap();
    let db = setup_db(dir.path());

    // Create table with 2 columns
    db.execute("CREATE TABLE t (id INT PRIMARY KEY, a INT)").unwrap();
    db.execute("INSERT INTO t VALUES (1, 100)").unwrap();
    db.execute("DROP TABLE t").unwrap();

    // Recreate with 3 columns
    db.execute("CREATE TABLE t (id INT PRIMARY KEY, a INT, b INT)").unwrap();
    db.execute("INSERT INTO t VALUES (1, 10, 20)").unwrap();

    let rows = query_rows(&db, "SELECT * FROM t WHERE id = 1");
    assert_eq!(rows.len(), 1);
    assert_eq!(rows[0].len(), 3, "Should have 3 columns");
    assert_eq!(rows[0][0], Value::Integer(1));
    assert_eq!(rows[0][1], Value::Integer(10));
    assert_eq!(rows[0][2], Value::Integer(20));
}

#[test]
fn test_drop_recreate_different_types() {
    let dir = TempDir::new().unwrap();
    let db = setup_db(dir.path());

    db.execute("CREATE TABLE t (id INT PRIMARY KEY, v INT)").unwrap();
    db.execute("INSERT INTO t VALUES (1, 42)").unwrap();
    db.execute("DROP TABLE t").unwrap();

    db.execute("CREATE TABLE t (id INT PRIMARY KEY, v TEXT)").unwrap();
    db.execute("INSERT INTO t VALUES (1, 'hello')").unwrap();

    let row = query_single(&db, "SELECT v FROM t WHERE id = 1").unwrap();
    assert_eq!(row[0], Value::Text("hello".to_string()));
}

// ============================================================
// Multiple INSERT then complex queries
// ============================================================

#[test]
fn test_insert_many_then_select_with_conditions() {
    let dir = TempDir::new().unwrap();
    let db = setup_db(dir.path());
    db.execute("CREATE TABLE t (id INT PRIMARY KEY, status TEXT, v INT)").unwrap();

    for i in 1..=20 {
        let status = if i % 3 == 0 { "active" } else { "inactive" };
        db.execute(&format!("INSERT INTO t VALUES ({}, '{}', {})", i, status, i * 10)).unwrap();
    }

    // WHERE status = 'active' AND v > 100
    let rows = query_rows(&db, "SELECT * FROM t WHERE status = 'active' AND v > 100 ORDER BY id");
    assert!(rows.len() > 0, "Should find active rows with v > 100");
    for row in &rows {
        assert_eq!(row[1], Value::Text("active".to_string()));
        match &row[2] {
            Value::Integer(v) => assert!(*v > 100),
            _ => panic!("v should be integer"),
        }
    }
}

#[test]
fn test_select_star_from_table_with_many_columns() {
    let dir = TempDir::new().unwrap();
    let db = setup_db(dir.path());
    db.execute("CREATE TABLE t (id INT PRIMARY KEY, a INT, b TEXT, c INT, d FLOAT)").unwrap();
    db.execute("INSERT INTO t VALUES (1, 10, 'hello', 30, 3.14)").unwrap();

    let rows = query_rows(&db, "SELECT * FROM t");
    assert_eq!(rows.len(), 1);
    assert_eq!(rows[0].len(), 5);
    assert_eq!(rows[0][0], Value::Integer(1));
    assert_eq!(rows[0][1], Value::Integer(10));
    assert_eq!(rows[0][2], Value::Text("hello".to_string()));
    assert_eq!(rows[0][3], Value::Integer(30));
}

#[test]
fn test_select_specific_columns() {
    let dir = TempDir::new().unwrap();
    let db = setup_db(dir.path());
    db.execute("CREATE TABLE t (id INT PRIMARY KEY, a INT, b INT, c INT)").unwrap();
    db.execute("INSERT INTO t VALUES (1, 10, 20, 30)").unwrap();

    let row = query_single(&db, "SELECT a, c FROM t WHERE id = 1").unwrap();
    assert_eq!(row.len(), 2);
    assert_eq!(row[0], Value::Integer(10));
    assert_eq!(row[1], Value::Integer(30));
}

// ============================================================
// UPDATE edge cases
// ============================================================

#[test]
fn test_update_multiple_columns() {
    let dir = TempDir::new().unwrap();
    let db = setup_db(dir.path());
    db.execute("CREATE TABLE t (id INT PRIMARY KEY, a INT, b INT, c INT)").unwrap();
    db.execute("INSERT INTO t VALUES (1, 10, 20, 30)").unwrap();

    db.execute("UPDATE t SET a = 100, b = 200, c = 300 WHERE id = 1").unwrap();

    let row = query_single(&db, "SELECT a, b, c FROM t WHERE id = 1").unwrap();
    assert_eq!(row[0], Value::Integer(100));
    assert_eq!(row[1], Value::Integer(200));
    assert_eq!(row[2], Value::Integer(300));
}

#[test]
fn test_update_with_null_value() {
    let dir = TempDir::new().unwrap();
    let db = setup_db(dir.path());
    db.execute("CREATE TABLE t (id INT PRIMARY KEY, v INT)").unwrap();
    db.execute("INSERT INTO t VALUES (1, 100)").unwrap();

    db.execute("UPDATE t SET v = NULL WHERE id = 1").unwrap();

    let row = query_single(&db, "SELECT v FROM t WHERE id = 1").unwrap();
    assert_eq!(row[0], Value::Null, "Updated value should be NULL");
}

#[test]
fn test_update_set_to_expression() {
    let dir = TempDir::new().unwrap();
    let db = setup_db(dir.path());
    db.execute("CREATE TABLE t (id INT PRIMARY KEY, a INT, b INT)").unwrap();
    db.execute("INSERT INTO t VALUES (1, 5, 3)").unwrap();

    db.execute("UPDATE t SET a = a + b WHERE id = 1").unwrap();

    let row = query_single(&db, "SELECT a FROM t WHERE id = 1").unwrap();
    assert_eq!(row[0], Value::Integer(8), "a = a + b should be 8");
}

#[test]
fn test_update_swap_three_columns() {
    let dir = TempDir::new().unwrap();
    let db = setup_db(dir.path());
    db.execute("CREATE TABLE t (id INT PRIMARY KEY, a INT, b INT, c INT)").unwrap();
    db.execute("INSERT INTO t VALUES (1, 1, 2, 3)").unwrap();

    // Rotate: a→b, b→c, c→a
    db.execute("UPDATE t SET a = c, b = a, c = b WHERE id = 1").unwrap();

    let row = query_single(&db, "SELECT a, b, c FROM t WHERE id = 1").unwrap();
    assert_eq!(row[0], Value::Integer(3), "a should be old c=3");
    assert_eq!(row[1], Value::Integer(1), "b should be old a=1");
    assert_eq!(row[2], Value::Integer(2), "c should be old b=2");
}

// ============================================================
// DELETE edge cases
// ============================================================

#[test]
fn test_delete_with_complex_where() {
    let dir = TempDir::new().unwrap();
    let db = setup_db(dir.path());
    db.execute("CREATE TABLE t (id INT PRIMARY KEY, v INT)").unwrap();
    for i in 1..=10 {
        db.execute(&format!("INSERT INTO t VALUES ({}, {})", i, i * 10)).unwrap();
    }

    db.execute("DELETE FROM t WHERE v >= 50 AND v <= 80").unwrap();

    let rows = query_rows(&db, "SELECT * FROM t ORDER BY id");
    assert_eq!(rows.len(), 6, "Should delete v=50,60,70,80 (4 rows)");

    let remaining: Vec<i64> = rows.iter().filter_map(|r| match &r[1] {
        Value::Integer(v) => Some(*v),
        _ => None,
    }).collect();
    assert_eq!(remaining, vec![10, 20, 30, 40, 90, 100]);
}

#[test]
fn test_delete_with_or_condition() {
    let dir = TempDir::new().unwrap();
    let db = setup_db(dir.path());
    db.execute("CREATE TABLE t (id INT PRIMARY KEY, v INT)").unwrap();
    for i in 1..=5 {
        db.execute(&format!("INSERT INTO t VALUES ({}, {})", i, i)).unwrap();
    }

    db.execute("DELETE FROM t WHERE v = 1 OR v = 5").unwrap();

    let rows = query_rows(&db, "SELECT * FROM t ORDER BY id");
    assert_eq!(rows.len(), 3);
}

// ============================================================
// Prepared statement edge cases
// ============================================================

#[test]
fn test_prepared_different_params_sequential() {
    let dir = TempDir::new().unwrap();
    let db = setup_db(dir.path());
    db.execute("CREATE TABLE t (id INT PRIMARY KEY, v TEXT)").unwrap();
    db.execute("INSERT INTO t VALUES (1, 'one')").unwrap();
    db.execute("INSERT INTO t VALUES (2, 'two')").unwrap();

    // Execute same prepared query with different params
    let sql = "SELECT v FROM t WHERE id = ?";
    let r1 = db.execute_prepared(sql, vec![Value::Integer(1)]).unwrap().materialize().unwrap();
    let r2 = db.execute_prepared(sql, vec![Value::Integer(2)]).unwrap().materialize().unwrap();

    if let QueryResult::Select { rows: rows1, .. } = r1 {
        assert_eq!(rows1[0][0], Value::Text("one".to_string()));
    } else {
        panic!("Expected Select result");
    }
    if let QueryResult::Select { rows: rows2, .. } = r2 {
        assert_eq!(rows2[0][0], Value::Text("two".to_string()));
    } else {
        panic!("Expected Select result");
    }
}

#[test]
fn test_prepared_insert_and_query() {
    let dir = TempDir::new().unwrap();
    let db = setup_db(dir.path());
    db.execute("CREATE TABLE t (id INT PRIMARY KEY, name TEXT, age INT)").unwrap();

    // Insert via prepared
    for i in 1..=5 {
        db.execute_prepared(
            "INSERT INTO t VALUES (?, ?, ?)",
            vec![Value::Integer(i), Value::Text(format!("user{}", i)), Value::Integer(20 + i)],
        ).unwrap();
    }

    // Query via prepared
    let result = db.execute_prepared(
        "SELECT name, age FROM t WHERE id = ?",
        vec![Value::Integer(3)],
    ).unwrap().materialize().unwrap();

    if let QueryResult::Select { rows, .. } = result {
        assert_eq!(rows.len(), 1);
        assert_eq!(rows[0][0], Value::Text("user3".to_string()));
        assert_eq!(rows[0][1], Value::Integer(23));
    }
}

// ============================================================
// NULL in SELECT list
// ============================================================

#[test]
fn test_select_null_literal() {
    let dir = TempDir::new().unwrap();
    let db = setup_db(dir.path());
    db.execute("CREATE TABLE t (id INT PRIMARY KEY)").unwrap();
    db.execute("INSERT INTO t VALUES (1)").unwrap();

    // SELECT with NULL literal — should not crash
    let rows = query_rows(&db, "SELECT NULL, id FROM t");
    assert_eq!(rows.len(), 1);
    assert_eq!(rows[0][0], Value::Null);
    assert_eq!(rows[0][1], Value::Integer(1));
}

// ============================================================
// String operations
// ============================================================

#[test]
fn test_select_upper_lower() {
    let dir = TempDir::new().unwrap();
    let db = setup_db(dir.path());
    db.execute("CREATE TABLE t (id INT PRIMARY KEY, name TEXT)").unwrap();
    db.execute("INSERT INTO t VALUES (1, 'Hello World')").unwrap();

    let row = query_single(&db, "SELECT UPPER(name) FROM t WHERE id = 1").unwrap();
    match &row[0] {
        Value::Text(s) => assert_eq!(s, "HELLO WORLD"),
        _ => panic!("UPPER should return text"),
    }

    let row = query_single(&db, "SELECT LOWER(name) FROM t WHERE id = 1").unwrap();
    match &row[0] {
        Value::Text(s) => assert_eq!(s, "hello world"),
        _ => panic!("LOWER should return text"),
    }
}

#[test]
fn test_select_length() {
    let dir = TempDir::new().unwrap();
    let db = setup_db(dir.path());
    db.execute("CREATE TABLE t (id INT PRIMARY KEY, name TEXT)").unwrap();
    db.execute("INSERT INTO t VALUES (1, 'hello')").unwrap();

    let row = query_single(&db, "SELECT LENGTH(name) FROM t WHERE id = 1").unwrap();
    assert_eq!(row[0], Value::Integer(5), "LENGTH('hello') should be 5");
}

// ============================================================
// Large data operations
// ============================================================

#[test]
fn test_insert_100_rows_then_query() {
    let dir = TempDir::new().unwrap();
    let db = setup_db(dir.path());
    db.execute("CREATE TABLE t (id INT PRIMARY KEY, v INT)").unwrap();

    for i in 1..=100 {
        db.execute(&format!("INSERT INTO t VALUES ({}, {})", i, i * i)).unwrap();
    }

    let row = query_single(&db, "SELECT COUNT(*) FROM t").unwrap();
    assert_eq!(row[0], Value::Integer(100));

    // Test simple range query first
    let rows = query_rows(&db, "SELECT * FROM t WHERE v > 9000");
    // 96^2=9216, 97^2=9409, 98^2=9604, 99^2=9801, 100^2=10000 → 5 rows
    assert!(rows.len() >= 4, "Should find rows with v > 9000, got {} rows", rows.len());

    // Verify the actual values
    let ids: Vec<i64> = rows.iter().filter_map(|r| match &r[0] {
        Value::Integer(id) => Some(*id),
        _ => None,
    }).collect();
    for id in &ids {
        assert!(*id >= 95, "All results should have id >= 95, got {}", id);
    }
}

// ============================================================
// Reopen database
// ============================================================

#[test]
fn test_reopen_preserves_all_data() {
    let dir = TempDir::new().unwrap();
    let path = dir.path().join("test.mote");

    {
        let db = Database::create(&path).unwrap();
        db.execute("CREATE TABLE t (id INT PRIMARY KEY, v TEXT)").unwrap();
        db.execute("INSERT INTO t VALUES (1, 'hello')").unwrap();
        db.execute("INSERT INTO t VALUES (2, 'world')").unwrap();
        db.execute("INSERT INTO t VALUES (3, NULL)").unwrap();
        drop(db);
    }

    let db = Database::open(&path).unwrap();
    let rows = query_rows(&db, "SELECT * FROM t ORDER BY id");
    assert_eq!(rows.len(), 3);
    assert_eq!(rows[0][1], Value::Text("hello".to_string()));
    assert_eq!(rows[1][1], Value::Text("world".to_string()));
    assert_eq!(rows[2][1], Value::Null);
}

#[test]
fn test_reopen_preserves_updates_and_deletes() {
    let path_ctx = TempDir::new().unwrap();
    let path = path_ctx.path().join("test.mote");

    {
        let db = Database::create(&path).unwrap();
        db.execute("CREATE TABLE t (id INT PRIMARY KEY, v INT)").unwrap();
        db.execute("INSERT INTO t VALUES (1, 10)").unwrap();
        db.execute("INSERT INTO t VALUES (2, 20)").unwrap();
        db.execute("INSERT INTO t VALUES (3, 30)").unwrap();
        db.execute("UPDATE t SET v = 99 WHERE id = 2").unwrap();
        db.execute("DELETE FROM t WHERE id = 3").unwrap();
        drop(db);
    }

    let db = Database::open(&path).unwrap();
    let rows = query_rows(&db, "SELECT * FROM t ORDER BY id");
    assert_eq!(rows.len(), 2);
    assert_eq!(rows[0], vec![Value::Integer(1), Value::Integer(10)]);
    assert_eq!(rows[1], vec![Value::Integer(2), Value::Integer(99)]);
}

// ============================================================
// Auto-increment
// ============================================================

#[test]
fn test_auto_increment_ids() {
    let dir = TempDir::new().unwrap();
    let db = setup_db(dir.path());
    db.execute("CREATE TABLE t (id INT PRIMARY KEY AUTO_INCREMENT, v TEXT)").unwrap();

    db.execute("INSERT INTO t (v) VALUES ('a')").unwrap();
    db.execute("INSERT INTO t (v) VALUES ('b')").unwrap();
    db.execute("INSERT INTO t (v) VALUES ('c')").unwrap();

    let rows = query_rows(&db, "SELECT id, v FROM t ORDER BY id");
    assert_eq!(rows.len(), 3);
    assert_eq!(rows[0][0], Value::Integer(1));
    assert_eq!(rows[1][0], Value::Integer(2));
    assert_eq!(rows[2][0], Value::Integer(3));
}

#[test]
fn test_auto_increment_with_explicit_id() {
    let dir = TempDir::new().unwrap();
    let db = setup_db(dir.path());
    db.execute("CREATE TABLE t (id INT PRIMARY KEY AUTO_INCREMENT, v TEXT)").unwrap();

    db.execute("INSERT INTO t (v) VALUES ('a')").unwrap();
    db.execute("INSERT INTO t (id, v) VALUES (100, 'b')").unwrap();
    db.execute("INSERT INTO t (v) VALUES ('c')").unwrap();

    let rows = query_rows(&db, "SELECT id, v FROM t ORDER BY id");
    assert_eq!(rows.len(), 3);
    // MoteDB design: AUTO_INCREMENT ignores user-provided PK values,
    // so explicit id=100 is ignored and auto-generated ID is used instead.
    // IDs should be sequential: 1, 2, 3
    assert_eq!(rows[0][0], Value::Integer(1));
    assert_eq!(rows[1][0], Value::Integer(2));
    assert_eq!(rows[2][0], Value::Integer(3));
}

// ============================================================
// Error handling — bad SQL should error, not panic
// ============================================================

#[test]
fn test_bad_insert_column_count() {
    let dir = TempDir::new().unwrap();
    let db = setup_db(dir.path());
    db.execute("CREATE TABLE t (id INT PRIMARY KEY, v INT)").unwrap();

    // Too many values
    let result = db.execute("INSERT INTO t VALUES (1, 2, 3)");
    assert!(result.is_err(), "Should reject too many values");

    // Too few values
    let result = db.execute("INSERT INTO t VALUES (1)");
    assert!(result.is_err(), "Should reject too few values");
}

#[test]
fn test_duplicate_pk_rejected() {
    let dir = TempDir::new().unwrap();
    let db = setup_db(dir.path());
    db.execute("CREATE TABLE t (id INT PRIMARY KEY, v INT)").unwrap();
    db.execute("INSERT INTO t VALUES (1, 10)").unwrap();

    let result = db.execute("INSERT INTO t VALUES (1, 20)");
    assert!(result.is_err(), "Duplicate PK should be rejected");
}

#[test]
fn test_select_nonexistent_table() {
    let dir = TempDir::new().unwrap();
    let db = setup_db(dir.path());

    let result = db.execute("SELECT * FROM nonexistent");
    assert!(result.is_err(), "Should error on nonexistent table");
}

#[test]
fn test_insert_wrong_type() {
    let dir = TempDir::new().unwrap();
    let db = setup_db(dir.path());
    db.execute("CREATE TABLE t (id INT PRIMARY KEY, v INT)").unwrap();

    let result = db.execute("INSERT INTO t VALUES (1, 'not_a_number')");
    assert!(result.is_err(), "Should reject wrong type");
}

// ============================================================
// Complex WHERE with IN + other conditions
// ============================================================

#[test]
fn test_where_in_with_and() {
    let dir = TempDir::new().unwrap();
    let db = setup_db(dir.path());
    db.execute("CREATE TABLE t (id INT PRIMARY KEY, v INT, status TEXT)").unwrap();
    db.execute("INSERT INTO t VALUES (1, 10, 'active')").unwrap();
    db.execute("INSERT INTO t VALUES (2, 20, 'inactive')").unwrap();
    db.execute("INSERT INTO t VALUES (3, 30, 'active')").unwrap();
    db.execute("INSERT INTO t VALUES (4, 40, 'inactive')").unwrap();

    let rows = query_rows(&db, "SELECT * FROM t WHERE v IN (10, 30) AND status = 'active' ORDER BY id");
    assert_eq!(rows.len(), 2, "Should match rows 1 and 3");
}

#[test]
fn test_where_not_in() {
    let dir = TempDir::new().unwrap();
    let db = setup_db(dir.path());
    db.execute("CREATE TABLE t (id INT PRIMARY KEY, v INT)").unwrap();
    for i in 1..=5 {
        db.execute(&format!("INSERT INTO t VALUES ({}, {})", i, i * 10)).unwrap();
    }

    let rows = query_rows(&db, "SELECT * FROM t WHERE v NOT IN (20, 40) ORDER BY id");
    assert_eq!(rows.len(), 3, "NOT IN (20,40) should exclude v=20 and v=40");
}