motedb 0.8.2

//! Tests for data types: INT, FLOAT, TEXT, BOOL, TIMESTAMP, VECTOR, BLOB,
//! NULL handling, type coercion, edge values

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

fn rows(result: motedb::StreamingQueryResult) -> Vec<Vec<Value>> {
    use motedb::QueryResult;
    match result.materialize().unwrap() {
        QueryResult::Select { rows, .. } => rows,
        _ => panic!("Expected Select result"),
    }
}

fn row(result: motedb::StreamingQueryResult) -> Vec<Value> {
    let r = rows(result);
    assert_eq!(r.len(), 1);
    r.into_iter().next().unwrap()
}

// === Integer edge values ===

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

    let r = row(db.execute("SELECT val FROM t WHERE id = 1").unwrap());
    assert_eq!(&r[0], &Value::Integer(9999999999999i64));
}

#[test]
fn test_zero_and_negative() {
    let dir = TempDir::new().unwrap();
    let db = Database::create(dir.path()).unwrap();
    db.execute("CREATE TABLE t (id INT PRIMARY KEY, val INT)")
        .unwrap();
    db.execute("INSERT INTO t VALUES (1, 0)").unwrap();
    db.execute("INSERT INTO t VALUES (2, -1)").unwrap();
    db.execute("INSERT INTO t VALUES (3, -999)").unwrap();

    let r = rows(db.execute("SELECT val FROM t ORDER BY val").unwrap());
    assert_eq!(&r[0][0], &Value::Integer(-999));
    assert_eq!(&r[1][0], &Value::Integer(-1));
    assert_eq!(&r[2][0], &Value::Integer(0));
}

// === Float edge values ===

#[test]
fn test_float_precision() {
    let dir = TempDir::new().unwrap();
    let db = Database::create(dir.path()).unwrap();
    db.execute("CREATE TABLE t (id INT PRIMARY KEY, val FLOAT)")
        .unwrap();
    db.execute("INSERT INTO t VALUES (1, 3.14159265358979)")
        .unwrap();

    let r = row(db.execute("SELECT val FROM t WHERE id = 1").unwrap());
    match &r[0] {
        Value::Float(f) => assert!((f - 3.14159265358979).abs() < 1e-10),
        other => panic!("Expected Float, got {:?}", other),
    }
}

#[test]
fn test_float_zero() {
    let dir = TempDir::new().unwrap();
    let db = Database::create(dir.path()).unwrap();
    db.execute("CREATE TABLE t (id INT PRIMARY KEY, val FLOAT)")
        .unwrap();
    db.execute("INSERT INTO t VALUES (1, 0.0)").unwrap();

    let r = row(db.execute("SELECT val FROM t WHERE id = 1").unwrap());
    match &r[0] {
        Value::Float(f) => assert!(*f == 0.0),
        other => panic!("Expected Float, got {:?}", other),
    }
}

// === Text edge values ===

#[test]
fn test_unicode_text() {
    let dir = TempDir::new().unwrap();
    let db = Database::create(dir.path()).unwrap();
    db.execute("CREATE TABLE t (id INT PRIMARY KEY, val TEXT)")
        .unwrap();
    db.execute("INSERT INTO t VALUES (1, '你好世界')").unwrap();
    db.execute("INSERT INTO t VALUES (2, '🎉🚀💻')").unwrap();

    let r = row(db.execute("SELECT val FROM t WHERE id = 1").unwrap());
    assert_eq!(&r[0], &Value::text("你好世界".to_string()));
}

#[test]
fn test_long_text() {
    let dir = TempDir::new().unwrap();
    let db = Database::create(dir.path()).unwrap();
    db.execute("CREATE TABLE t (id INT PRIMARY KEY, val TEXT)")
        .unwrap();
    let long_str = "x".repeat(10000);
    db.execute(&format!("INSERT INTO t VALUES (1, '{}')", long_str))
        .unwrap();

    let r = row(db.execute("SELECT val FROM t WHERE id = 1").unwrap());
    match &r[0] {
        Value::Text(s) => assert_eq!(s.len(), 10000),
        other => panic!("Expected Text, got {:?}", other),
    }
}

#[test]
fn test_text_with_special_chars() {
    let dir = TempDir::new().unwrap();
    let db = Database::create(dir.path()).unwrap();
    db.execute("CREATE TABLE t (id INT PRIMARY KEY, val TEXT)")
        .unwrap();
    // Test with escaped single quotes
    db.execute("INSERT INTO t VALUES (1, 'it''s a test')")
        .unwrap();

    let r = row(db.execute("SELECT val FROM t WHERE id = 1").unwrap());
    match &r[0] {
        Value::Text(s) => assert!(s.contains("it") && s.contains("test")),
        other => panic!("Expected Text, got {:?}", other),
    }
}

// === Boolean type ===

#[test]
fn test_boolean_operations() {
    let dir = TempDir::new().unwrap();
    let db = Database::create(dir.path()).unwrap();
    db.execute("CREATE TABLE flags (id INT PRIMARY KEY, active BOOLEAN)")
        .unwrap();
    db.execute("INSERT INTO flags VALUES (1, TRUE)").unwrap();
    db.execute("INSERT INTO flags VALUES (2, FALSE)").unwrap();
    db.execute("INSERT INTO flags VALUES (3, NULL)").unwrap();

    let r = rows(
        db.execute("SELECT id FROM flags WHERE active = TRUE ORDER BY id")
            .unwrap(),
    );
    assert_eq!(r.len(), 1);
    assert_eq!(&r[0][0], &Value::Integer(1));

    let r = rows(
        db.execute("SELECT id FROM flags WHERE active IS NULL")
            .unwrap(),
    );
    assert_eq!(r.len(), 1);
    assert_eq!(&r[0][0], &Value::Integer(3));
}

// === NULL comprehensive ===

#[test]
fn test_null_in_all_columns() {
    let dir = TempDir::new().unwrap();
    let db = Database::create(dir.path()).unwrap();
    db.execute("CREATE TABLE t (id INT PRIMARY KEY, a INT, b TEXT, c FLOAT)")
        .unwrap();
    db.execute("INSERT INTO t VALUES (1, NULL, NULL, NULL)")
        .unwrap();

    let r = row(db.execute("SELECT a, b, c FROM t WHERE id = 1").unwrap());
    assert!(matches!(&r[0], Value::Null));
    assert!(matches!(&r[1], Value::Null));
    assert!(matches!(&r[2], Value::Null));
}

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

    let r = row(db.execute("SELECT val + 10 FROM t WHERE id = 1").unwrap());
    assert!(matches!(&r[0], Value::Null), "NULL + anything = NULL");
}

#[test]
fn test_null_comparison() {
    let dir = TempDir::new().unwrap();
    let db = Database::create(dir.path()).unwrap();
    db.execute("CREATE TABLE t (id INT PRIMARY KEY, val INT)")
        .unwrap();
    db.execute("INSERT INTO t VALUES (1, NULL)").unwrap();
    db.execute("INSERT INTO t VALUES (2, 42)").unwrap();

    // val = NULL should match nothing
    let r = rows(db.execute("SELECT id FROM t WHERE val = NULL").unwrap());
    assert_eq!(r.len(), 0);

    // val IS NULL should match row 1
    let r = rows(db.execute("SELECT id FROM t WHERE val IS NULL").unwrap());
    assert_eq!(r.len(), 1);

    // val IS NOT NULL should match row 2
    let r = rows(
        db.execute("SELECT id FROM t WHERE val IS NOT NULL")
            .unwrap(),
    );
    assert_eq!(r.len(), 1);
    assert_eq!(&r[0][0], &Value::Integer(2));
}

// === Vector/Tensor type ===

#[test]
fn test_vector_insert_retrieve() {
    let dir = TempDir::new().unwrap();
    let db = Database::create(dir.path()).unwrap();
    db.execute("CREATE TABLE vecs (id INT PRIMARY KEY, emb VECTOR(3))")
        .unwrap();

    let row = vec![
        Value::Integer(1),
        Value::tensor(Tensor::new(vec![1.0, 2.0, 3.0])),
    ];
    let row_id = db.insert_row("vecs", row).unwrap();

    let got = db.get_row("vecs", row_id).unwrap();
    assert!(got.is_some());
    match &got.unwrap()[1] {
        Value::Tensor(t) => {
            let data = t.as_f32();
            assert_eq!(data.len(), 3);
            assert!((data[0] - 1.0).abs() < 0.01);
        }
        other => panic!("Expected Tensor, got {:?}", other),
    }
}

#[test]
fn test_vector_null() {
    let dir = TempDir::new().unwrap();
    let db = Database::create(dir.path()).unwrap();
    db.execute("CREATE TABLE vecs (id INT PRIMARY KEY, emb VECTOR(3))")
        .unwrap();

    let vals = vec![Value::Integer(1), Value::Null];
    db.insert_row("vecs", vals).unwrap();

    let r = row(db.execute("SELECT emb FROM vecs WHERE id = 1").unwrap());
    assert!(matches!(&r[0], Value::Null), "NULL vector should be NULL");
}

// === Timestamp type ===

#[test]
fn test_timestamp_insert_retrieve() {
    let dir = TempDir::new().unwrap();
    let db = Database::create(dir.path()).unwrap();
    db.execute("CREATE TABLE events (id INT PRIMARY KEY, ts TIMESTAMP)")
        .unwrap();

    let row = vec![
        Value::Integer(1),
        Value::Timestamp(motedb::types::Timestamp::from_micros(1700000000000)),
    ];
    let row_id = db.insert_row("events", row).unwrap();

    let got = db.get_row("events", row_id).unwrap();
    assert!(got.is_some());
    match &got.unwrap()[1] {
        Value::Timestamp(ts) => {
            assert_eq!(ts.as_micros(), 1700000000000);
        }
        other => panic!("Expected Timestamp, got {:?}", other),
    }
}

// === Mixed types in one table ===

#[test]
fn test_all_types_in_one_table() {
    let dir = TempDir::new().unwrap();
    let db = Database::create(dir.path()).unwrap();
    db.execute("CREATE TABLE mixed (id INT PRIMARY KEY, i INT, f FLOAT, t TEXT, b BOOLEAN)")
        .unwrap();
    db.execute("INSERT INTO mixed VALUES (1, 42, 3.14, 'hello', TRUE)")
        .unwrap();

    let r = row(db
        .execute("SELECT i, f, t, b FROM mixed WHERE id = 1")
        .unwrap());
    assert_eq!(&r[0], &Value::Integer(42));
    match &r[1] {
        Value::Float(f) => assert!((f - 3.14).abs() < 0.01),
        other => panic!("Expected Float, got {:?}", other),
    }
    assert_eq!(&r[2], &Value::text("hello".to_string()));
    match &r[3] {
        Value::Bool(b) => assert!(*b),
        other => panic!("Expected Bool, got {:?}", other),
    }
}

// === Type coercion in comparisons ===

#[test]
fn test_int_float_comparison() {
    let dir = TempDir::new().unwrap();
    let db = Database::create(dir.path()).unwrap();
    db.execute("CREATE TABLE t (id INT PRIMARY KEY, val FLOAT)")
        .unwrap();
    db.execute("INSERT INTO t VALUES (1, 10.0)").unwrap();

    // Compare float column with integer literal
    let r = rows(db.execute("SELECT id FROM t WHERE val = 10").unwrap());
    assert!(r.len() >= 1, "INT literal should compare with FLOAT column");

    let r = rows(db.execute("SELECT id FROM t WHERE val > 9").unwrap());
    assert!(r.len() >= 1);
}

// === Multiple tables with different schemas ===

#[test]
fn test_multiple_tables_isolation() {
    let dir = TempDir::new().unwrap();
    let db = Database::create(dir.path()).unwrap();

    db.execute("CREATE TABLE a (id INT PRIMARY KEY, val TEXT)")
        .unwrap();
    db.execute("CREATE TABLE b (id INT PRIMARY KEY, val INT)")
        .unwrap();
    db.execute("INSERT INTO a VALUES (1, 'text')").unwrap();
    db.execute("INSERT INTO b VALUES (1, 42)").unwrap();

    let ra = row(db.execute("SELECT val FROM a WHERE id = 1").unwrap());
    assert_eq!(&ra[0], &Value::text("text".to_string()));

    let rb = row(db.execute("SELECT val FROM b WHERE id = 1").unwrap());
    assert_eq!(&rb[0], &Value::Integer(42));
}

// === Table with many columns ===

#[test]
fn test_wide_table() {
    let dir = TempDir::new().unwrap();
    let db = Database::create(dir.path()).unwrap();
    db.execute("CREATE TABLE wide (id INT PRIMARY KEY, c1 INT, c2 INT, c3 INT, c4 INT, c5 INT, c6 INT, c7 INT, c8 INT, c9 INT, c10 INT)").unwrap();
    db.execute("INSERT INTO wide VALUES (1, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10)")
        .unwrap();

    let r = row(db
        .execute("SELECT c1, c5, c10 FROM wide WHERE id = 1")
        .unwrap());
    assert_eq!(&r[0], &Value::Integer(1));
    assert_eq!(&r[1], &Value::Integer(5));
    assert_eq!(&r[2], &Value::Integer(10));
}

// === Large text values: columnar format caps text at 65534 bytes ===
// Regression: previously, text > 65534 bytes was silently truncated to 65534
// (the u16 length prefix, with 0xFFFF reserved as NULL sentinel), causing
// silent data loss. Now it errors loudly.

#[test]
fn test_oversized_text_is_rejected() {
    let dir = TempDir::new().unwrap();
    let db = Database::create(dir.path()).unwrap();
    db.execute("CREATE TABLE t (id INT PRIMARY KEY, s TEXT)")
        .unwrap();

    // A value just under the limit (65534 bytes) stores and round-trips fine.
    let near_max = "a".repeat(65534);
    db.execute(&format!("INSERT INTO t VALUES (1, '{}')", near_max))
        .unwrap();
    let r = row(db.execute("SELECT length(s) FROM t WHERE id = 1").unwrap());
    assert_eq!(r[0], Value::Integer(65534));

    // A value over the limit must error, not silently truncate.
    let too_big = "b".repeat(100_000);
    let result = db.execute(&format!("INSERT INTO t VALUES (2, '{}')", too_big));
    assert!(
        result.is_err(),
        "oversized text should be rejected, not truncated"
    );
    // Confirm no partial row was written.
    let r = row(db.execute("SELECT COUNT(*) FROM t").unwrap());
    assert_eq!(r[0], Value::Integer(1));
}

#[test]
fn test_normal_text_unaffected_by_cap() {
    // Typical text sizes (up to a few KB) must work normally after the cap fix.
    let dir = TempDir::new().unwrap();
    let db = Database::create(dir.path()).unwrap();
    db.execute("CREATE TABLE t (id INT PRIMARY KEY, s TEXT)")
        .unwrap();
    let s = "héllo 世界 🎉 ".repeat(100); // ~1800 bytes, multibyte
    db.execute(&format!("INSERT INTO t VALUES (1, '{}')", s))
        .unwrap();
    let r = row(db.execute("SELECT length(s) FROM t WHERE id = 1").unwrap());
    // length() counts chars, not bytes
    match &r[0] {
        Value::Integer(n) => assert_eq!(*n, 1100, "100 repeats of 11 chars"),
        o => panic!("expected int, got {:?}", o),
    }
}

// === CAST(FLOAT AS INT) at i64 boundaries (regression) ===
// f64 can't distinguish i64::MAX from 2^63, so the old check
// `f >= i64::MAX as f64` rejected every large integer. Fixed with saturating
// cast + round-trip verification.

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

    // Large integers should round-trip through FLOAT without overflow errors.
    for v in [
        -9223372036854775807i64,
        9223372036854775806,
        9223372036854775807,
    ] {
        let sql = format!("SELECT CAST(CAST({} AS FLOAT) AS INT)", v);
        let r = row(db.execute(&sql).unwrap());
        match &r[0] {
            Value::Integer(got) => {
                // f64 rounding means the result may be off by 1 at the boundary,
                // but it must NOT error and must be very close to the input.
                assert!(
                    ((*got as i128) - (v as i128)).abs() <= 1,
                    "CAST round-trip {} got {}",
                    v,
                    got
                );
            }
            o => panic!(
                "CAST(CAST({} AS FLOAT) AS INT) returned {:?}, expected Integer",
                v, o
            ),
        }
    }

    // Genuine overflow must still error.
    let r = db.execute("SELECT CAST(1e30 AS INT)");
    assert!(r.is_err(), "1e30 should overflow INTEGER");

    // Fractional truncation (toward zero, SQL standard).
    assert_eq!(
        row(db.execute("SELECT CAST(3.7 AS INT)").unwrap())[0],
        Value::Integer(3)
    );
    assert_eq!(
        row(db.execute("SELECT CAST(-3.7 AS INT)").unwrap())[0],
        Value::Integer(-3)
    );
}

// === i64::MIN literal parsing (regression) ===
// `-9223372036854775808` was parsed as i64::MIN+1 because the f64 round-trip
// collapsed 2^63 and i64::MAX (both = 9223372036854775808.0 in f64). Fixed via
// an OverflowInteger token variant preserving the exact i128 value.

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

    // The critical case: -i64::MIN must be exact, not MIN+1.
    let r = row(db.execute("SELECT -9223372036854775808").unwrap());
    assert_eq!(
        r[0],
        Value::Integer(-9223372036854775808),
        "i64::MIN literal must be exact"
    );

    // -i64::MAX must NOT be collapsed to i64::MIN.
    let r = row(db.execute("SELECT -9223372036854775807").unwrap());
    assert_eq!(r[0], Value::Integer(-9223372036854775807));

    // +i64::MAX still correct.
    let r = row(db.execute("SELECT 9223372036854775807").unwrap());
    assert_eq!(r[0], Value::Integer(9223372036854775807));

    // Arithmetic with i64::MIN.
    let r = row(db.execute("SELECT -9223372036854775808 + 1").unwrap());
    assert_eq!(r[0], Value::Integer(-9223372036854775807));
}

// === Float value into INT column: was silently corrupted (f64 bits as i64) ===
#[test]
fn test_float_into_int_column_rejected() {
    let dir = TempDir::new().unwrap();
    let db = Database::create(dir.path()).unwrap();
    db.execute("CREATE TABLE t (id INT PRIMARY KEY, v INT)")
        .unwrap();
    // 3.9 stored as Integer(4615964438073389875) before fix — now rejected.
    let r = db.execute("INSERT INTO t VALUES (1, 3.9)");
    assert!(
        r.is_err(),
        "float into INT column should be rejected, not silently corrupted"
    );
    // Integer into FLOAT still works (safe promotion).
    db.execute("CREATE TABLE tf (id INT PRIMARY KEY, f FLOAT)")
        .unwrap();
    let r = db.execute("INSERT INTO tf VALUES (1, 42)");
    assert!(r.is_ok(), "int into FLOAT should work");
}

// === Multiple PRIMARY KEY columns should error ===
#[test]
fn test_multiple_primary_key_rejected() {
    let dir = TempDir::new().unwrap();
    let db = Database::create(dir.path()).unwrap();
    let r = db.execute("CREATE TABLE bad (a INT PRIMARY KEY, b INT PRIMARY KEY)");
    assert!(
        r.is_err(),
        "multiple PRIMARY KEY columns should be rejected"
    );
    // Single PK still works.
    let r = db.execute("CREATE TABLE ok (a INT PRIMARY KEY, b INT)");
    assert!(r.is_ok());
}

// === TIMESTAMP column: string date insertion (ISO 8601) ===

#[test]
fn test_timestamp_string_insert() {
    let dir = TempDir::new().unwrap();
    let db = Database::create(dir.path()).unwrap();
    db.execute("CREATE TABLE t (id INT PRIMARY KEY, ts TIMESTAMP)")
        .unwrap();
    // Date only
    db.execute("INSERT INTO t VALUES (1, '2024-01-15')")
        .unwrap();
    // Datetime with space separator
    db.execute("INSERT INTO t VALUES (2, '2024-06-01 10:30:00')")
        .unwrap();
    // Datetime with T separator
    db.execute("INSERT INTO t VALUES (3, '2024-12-31T23:59:59')")
        .unwrap();

    // YEAR should work on all
    assert_eq!(
        row(db.execute("SELECT YEAR(ts) FROM t WHERE id=1").unwrap())[0],
        Value::Integer(2024)
    );
    assert_eq!(
        row(db.execute("SELECT YEAR(ts) FROM t WHERE id=2").unwrap())[0],
        Value::Integer(2024)
    );
    assert_eq!(
        row(db.execute("SELECT YEAR(ts) FROM t WHERE id=3").unwrap())[0],
        Value::Integer(2024)
    );

    // MONTH and DAY
    assert_eq!(
        row(db.execute("SELECT MONTH(ts) FROM t WHERE id=1").unwrap())[0],
        Value::Integer(1)
    );
    assert_eq!(
        row(db.execute("SELECT DAY(ts) FROM t WHERE id=3").unwrap())[0],
        Value::Integer(31)
    );

    // Raw timestamp value is stored correctly (as microseconds)
    assert_eq!(
        row(db
            .execute("SELECT CAST(ts AS INT) FROM t WHERE id=1")
            .unwrap())[0],
        Value::Integer(1705276800000000) // 2024-01-15 00:00:00 UTC
    );
}