sqawk 0.8.2

//! Tests for SQL statement compiler
//!
//! Extracted from compiler.rs for maintainability.

use crate::database::Database;
use crate::error::SqawkError;
use crate::table::Table;
use crate::vm::bytecode::OpCode;
use crate::vm::compiler::SqlCompiler;

/// Create a test database with a simple table
fn create_test_database() -> Database {
    let mut database = Database::new();
    let mut test_table = Table::new("users", vec![], None);

    // Add columns: id (INTEGER), age (INTEGER), name (TEXT)
    test_table.add_column("id".to_string(), "INTEGER".to_string());
    test_table.add_column("age".to_string(), "INTEGER".to_string());
    test_table.add_column("name".to_string(), "TEXT".to_string());

    // Add test data
    test_table
        .add_row(vec![
            crate::table::Value::Integer(1),
            crate::table::Value::Integer(25),
            crate::table::Value::String("Alice".to_string().into()),
        ])
        .expect("Failed to add test row");

    test_table
        .add_row(vec![
            crate::table::Value::Integer(2),
            crate::table::Value::Integer(35),
            crate::table::Value::String("Bob".to_string().into()),
        ])
        .expect("Failed to add test row");

    database
        .add_table("users".to_string(), test_table)
        .expect("Failed to add table");
    database
}

#[test]
fn test_compile_select_with_where_gt() {
    let database = create_test_database();
    let mut compiler = SqlCompiler::new(&database, false); // Use non-verbose mode for predictable output

    // Test: SELECT * FROM users WHERE age > 30
    let sql = "SELECT * FROM users WHERE age > 30";
    let program = compiler.compile(sql).expect("Compilation failed");

    // Verify the generated bytecode contains the expected instructions
    let instructions = &program.instructions;

    // Should have: Init, OpenRead, Rewind, Column (for all columns),
    // Column (for age), Integer (30), Gt, IfZ, ResultRow, Next, Close, Halt
    assert!(instructions.len() >= 8, "Expected at least 8 instructions");

    // Find key instructions
    let gt_found = instructions.iter().any(|inst| inst.opcode == OpCode::Gt);
    let ifz_found = instructions.iter().any(|inst| inst.opcode == OpCode::IfZ);
    let integer_found = instructions
        .iter()
        .any(|inst| inst.opcode == OpCode::Integer && inst.p1 == 30);
    let halt_found = instructions.iter().any(|inst| inst.opcode == OpCode::Halt);

    assert!(gt_found, "Expected Gt comparison instruction");
    assert!(ifz_found, "Expected IfZ conditional jump instruction");
    assert!(integer_found, "Expected Integer instruction with value 30");
    assert!(halt_found, "Expected Halt instruction");
}

#[test]
fn test_compile_select_with_where_eq() {
    let database = create_test_database();
    let mut compiler = SqlCompiler::new(&database, true);

    // Test: SELECT * FROM users WHERE age = 25
    let sql = "SELECT * FROM users WHERE age = 25";
    let program = compiler.compile(sql).expect("Compilation failed");

    let instructions = &program.instructions;

    // Verify we have Eq comparison and the correct literal value
    let eq_found = instructions.iter().any(|inst| inst.opcode == OpCode::Eq);
    let value_found = instructions
        .iter()
        .any(|inst| inst.opcode == OpCode::Integer && inst.p1 == 25);

    assert!(eq_found, "Expected Eq comparison instruction");
    assert!(value_found, "Expected Integer instruction with value 25");
}

#[test]
fn test_compile_select_with_where_string() {
    let database = create_test_database();
    let mut compiler = SqlCompiler::new(&database, true);

    // Test: SELECT * FROM users WHERE name = 'Alice'
    let sql = "SELECT * FROM users WHERE name = 'Alice'";
    let program = compiler.compile(sql).expect("Compilation failed");

    let instructions = &program.instructions;

    // Verify we have Eq comparison and String instruction
    let eq_found = instructions.iter().any(|inst| inst.opcode == OpCode::Eq);
    let string_found = instructions
        .iter()
        .any(|inst| inst.opcode == OpCode::String && inst.p4.as_deref() == Some("Alice"));

    assert!(eq_found, "Expected Eq comparison instruction");
    assert!(
        string_found,
        "Expected String instruction with value 'Alice'"
    );
}

#[test]
fn test_compile_select_with_where_lt() {
    let database = create_test_database();
    let mut compiler = SqlCompiler::new(&database, true);

    // Test: SELECT * FROM users WHERE age < 30
    let sql = "SELECT * FROM users WHERE age < 30";
    let program = compiler.compile(sql).expect("Compilation failed");

    let instructions = &program.instructions;

    // Verify we have Lt comparison
    let lt_found = instructions.iter().any(|inst| inst.opcode == OpCode::Lt);
    assert!(lt_found, "Expected Lt comparison instruction");
}

#[test]
fn test_compile_select_without_where() {
    let database = create_test_database();
    let mut compiler = SqlCompiler::new(&database, true);

    // Test: SELECT * FROM users (no WHERE clause)
    let sql = "SELECT * FROM users";
    let program = compiler.compile(sql).expect("Compilation failed");

    let instructions = &program.instructions;

    // Should NOT have comparison or conditional jump instructions
    let comparison_found = instructions.iter().any(|inst| {
        matches!(
            inst.opcode,
            OpCode::Gt | OpCode::Lt | OpCode::Eq | OpCode::Ne | OpCode::Ge | OpCode::Le
        )
    });
    let ifz_found = instructions.iter().any(|inst| inst.opcode == OpCode::IfZ);

    assert!(
        !comparison_found,
        "Should not have comparison instructions without WHERE"
    );
    assert!(!ifz_found, "Should not have conditional jump without WHERE");
}

#[test]
fn test_column_not_found_error() {
    let database = create_test_database();
    let mut compiler = SqlCompiler::new(&database, false);

    // Test: SELECT * FROM users WHERE invalid_column > 10
    let sql = "SELECT * FROM users WHERE invalid_column > 10";
    let result = compiler.compile(sql);

    // Should fail with column not found error
    assert!(result.is_err(), "Expected compilation to fail");
    match result.unwrap_err() {
        SqawkError::ColumnNotFound(col) => {
            assert_eq!(col, "invalid_column");
        }
        other => panic!("Expected ColumnNotFound error, got {:?}", other),
    }
}

#[test]
fn test_in_subquery_compilation() {
    let database = create_test_database();
    let mut compiler = SqlCompiler::new(&database, false);

    // Test IN (SELECT ...) subquery - now supported in Phase 4F
    let sql = "SELECT * FROM users WHERE age IN (SELECT age FROM users)";
    let result = compiler.compile(sql);

    // Should compile successfully now
    assert!(
        result.is_ok(),
        "Expected compilation to succeed: {:?}",
        result.unwrap_err()
    );
}

#[test]
fn test_between_compilation() {
    let database = create_test_database();
    let mut compiler = SqlCompiler::new(&database, true);

    // Test: SELECT * FROM users WHERE age BETWEEN 20 AND 30
    let sql = "SELECT * FROM users WHERE age BETWEEN 20 AND 30";
    let program = compiler.compile(sql).expect("BETWEEN compilation failed");

    let instructions = &program.instructions;

    // Verify we have Ge and Le comparison instructions (for BETWEEN)
    let ge_found = instructions.iter().any(|inst| inst.opcode == OpCode::Ge);
    let le_found = instructions.iter().any(|inst| inst.opcode == OpCode::Le);

    assert!(ge_found, "Expected Ge comparison instruction for BETWEEN");
    assert!(le_found, "Expected Le comparison instruction for BETWEEN");
}

#[test]
fn test_in_list_compilation() {
    let database = create_test_database();
    let mut compiler = SqlCompiler::new(&database, true);

    // Test: SELECT * FROM users WHERE age IN (25, 30, 35)
    let sql = "SELECT * FROM users WHERE age IN (25, 30, 35)";
    let program = compiler.compile(sql).expect("IN list compilation failed");

    let instructions = &program.instructions;

    // Verify we have multiple Eq comparison instructions (one for each list item)
    let eq_count = instructions
        .iter()
        .filter(|inst| inst.opcode == OpCode::Eq)
        .count();

    assert!(
        eq_count >= 3,
        "Expected at least 3 Eq comparison instructions for IN list"
    );
}

#[test]
fn test_like_pattern_compilation() {
    let database = create_test_database();
    let mut compiler = SqlCompiler::new(&database, true);

    // Test: SELECT * FROM users WHERE name LIKE 'A%'
    let sql = "SELECT * FROM users WHERE name LIKE 'A%'";
    let program = compiler.compile(sql).expect("LIKE compilation failed");

    let instructions = &program.instructions;

    // Verify we have Like instruction with the correct pattern
    let like_found = instructions
        .iter()
        .any(|inst| inst.opcode == OpCode::Like && inst.p4.as_deref() == Some("A%"));

    assert!(like_found, "Expected Like instruction with pattern 'A%'");
}

#[test]
fn test_ilike_pattern_compilation() {
    let database = create_test_database();
    let mut compiler = SqlCompiler::new(&database, true);

    // Test: SELECT * FROM users WHERE name ILIKE 'alice'
    let sql = "SELECT * FROM users WHERE name ILIKE 'alice'";
    let program = compiler.compile(sql).expect("ILIKE compilation failed");

    let instructions = &program.instructions;

    // Verify we have Like instruction with case-insensitive flag (bit 1 set = 2)
    let ilike_found = instructions.iter().any(|inst| {
        inst.opcode == OpCode::Like && inst.p2 == 2 // case_insensitive flag
    });

    assert!(
        ilike_found,
        "Expected Like instruction with case-insensitive flag"
    );
}

#[test]
fn test_case_compilation() {
    let database = create_test_database();
    let mut compiler = SqlCompiler::new(&database, true);

    // Test: Simple CASE expression in WHERE clause
    let sql = "SELECT * FROM users WHERE CASE WHEN age > 30 THEN 1 ELSE 0 END = 1";
    let program = compiler.compile(sql).expect("CASE compilation failed");

    let instructions = &program.instructions;

    // Verify we have Gt comparison for the WHEN condition
    let gt_found = instructions.iter().any(|inst| inst.opcode == OpCode::Gt);
    // Verify we have conditional jump (IfZ) for the CASE branching
    let ifz_found = instructions.iter().any(|inst| inst.opcode == OpCode::IfZ);
    // Verify we have Goto for jumping to the end
    let goto_found = instructions.iter().any(|inst| inst.opcode == OpCode::Goto);

    assert!(
        gt_found,
        "Expected Gt comparison instruction for CASE WHEN condition"
    );
    assert!(
        ifz_found,
        "Expected IfZ conditional jump instruction for CASE"
    );
    assert!(
        goto_found,
        "Expected Goto instruction for CASE branch jumps"
    );
}

#[test]
fn test_cast_compilation() {
    let database = create_test_database();
    let mut compiler = SqlCompiler::new(&database, true);

    // Test: CAST in WHERE clause
    let sql = "SELECT * FROM users WHERE CAST(age AS TEXT) = '25'";
    let program = compiler.compile(sql).expect("CAST compilation failed");

    let instructions = &program.instructions;

    // Verify we have Cast instruction with TEXT type
    let cast_found = instructions
        .iter()
        .any(|inst| inst.opcode == OpCode::Cast && inst.p4.as_deref() == Some("TEXT"));

    assert!(cast_found, "Expected Cast instruction with TEXT type");
}

#[test]
fn test_coalesce_compilation() {
    let database = create_test_database();
    let mut compiler = SqlCompiler::new(&database, true);

    // Test: COALESCE in WHERE clause
    let sql = "SELECT * FROM users WHERE COALESCE(name, 'default') = 'Alice'";
    let program = compiler.compile(sql).expect("COALESCE compilation failed");

    let instructions = &program.instructions;

    // Verify we have IsNull instruction for NULL checking
    let is_null_found = instructions
        .iter()
        .any(|inst| inst.opcode == OpCode::IsNull);

    assert!(is_null_found, "Expected IsNull instruction for COALESCE");
}

#[test]
fn test_nullif_compilation() {
    let database = create_test_database();
    let mut compiler = SqlCompiler::new(&database, true);

    // Test: NULLIF in WHERE clause
    let sql = "SELECT * FROM users WHERE NULLIF(name, 'Alice') IS NULL";
    let program = compiler.compile(sql).expect("NULLIF compilation failed");

    let instructions = &program.instructions;

    // Verify we have Eq instruction for NULLIF comparison
    let eq_found = instructions.iter().any(|inst| inst.opcode == OpCode::Eq);
    // Verify we have Null instruction for setting NULL result
    let null_found = instructions.iter().any(|inst| inst.opcode == OpCode::Null);

    assert!(eq_found, "Expected Eq instruction for NULLIF comparison");
    assert!(null_found, "Expected Null instruction for NULLIF result");
}