//! i-Octree Integration Tests
//!
//! Tests for i-Octree 3D spatial index operations:
//! - POINT3D literal parsing
//! - CREATE OCTREE INDEX
//! - ST_KNN_3D, ST_WITHIN_3D, ST_RADIUS_3D queries
//! - ST_DISTANCE_3D ORDER BY optimization
//! - INSERT/UPDATE/DELETE index synchronization
//! - Flush + checkpoint persistence
//! - 2D + 3D indexes coexistence
//!
//! Run: cargo test --test test_ioctree -- --test-threads=1

use motedb::Database;
use motedb::types::Value;
use tempfile::TempDir;

fn create_db() -> (Database, TempDir) {
    let dir = TempDir::new().expect("temp dir");
    let db = Database::create(dir.path()).expect("create db");
    (db, dir)
}

fn exec(db: &Database, sql: &str) -> motedb::sql::QueryResult {
    db.execute(sql).unwrap_or_else(|e| panic!("SQL failed: {}\n  SQL: {}", e, sql))
        .materialize().expect("materialize")
}

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

// ============================================================================
// 1. POINT3D literal + table creation
// ============================================================================

#[test]
fn test_point3d_literal_insert() {
    let (db, _dir) = create_db();

    exec(&db, "CREATE TABLE lidar (id INTEGER PRIMARY KEY, pt GEOMETRY)");
    exec(&db, "INSERT INTO lidar VALUES (1, POINT3D(1.0, 2.0, 3.0))");
    exec(&db, "INSERT INTO lidar VALUES (2, POINT3D(4.0, 5.0, 6.0))");

    let r = rows(&db, "SELECT id FROM lidar");
    assert_eq!(r.len(), 2);
}

#[test]
fn test_point3d_and_point_2d_coexist() {
    let (db, _dir) = create_db();

    exec(&db, "CREATE TABLE mixed (id INTEGER PRIMARY KEY, pt GEOMETRY)");
    exec(&db, "INSERT INTO mixed VALUES (1, POINT(1.0, 2.0))");
    exec(&db, "INSERT INTO mixed VALUES (2, POINT3D(3.0, 4.0, 5.0))");

    let r = rows(&db, "SELECT id FROM mixed");
    assert_eq!(r.len(), 2);
}

// ============================================================================
// 2. CREATE OCTREE INDEX + backfill
// ============================================================================

#[test]
fn test_create_octree_index_basic() {
    let (db, _dir) = create_db();

    exec(&db, "CREATE TABLE cloud (id INTEGER PRIMARY KEY, pt GEOMETRY)");
    for i in 1..=20i64 {
        let x = i as f64;
        let y = (i + 10) as f64;
        let z = (i + 20) as f64;
        exec(&db, &format!("INSERT INTO cloud VALUES ({}, POINT3D({}, {}, {}))", i, x, y, z));
    }

    exec(&db, "CREATE OCTREE INDEX cloud_pt ON cloud(pt)");

    // KNN query
    let r = rows(&db, "SELECT id FROM cloud WHERE ST_KNN_3D(pt, 1.0, 11.0, 21.0, 3)");
    assert_eq!(r.len(), 3, "KNN should return 3 results");

    // Verify the first result is id=1 (closest to (1,11,21))
    let first_id = match &r[0][0] {
        Value::Integer(i) => *i,
        _ => panic!("Expected integer"),
    };
    assert_eq!(first_id, 1, "Closest point should be id=1");
}

#[test]
fn test_octree_index_with_prefix_syntax() {
    let (db, _dir) = create_db();

    exec(&db, "CREATE TABLE pts (id INTEGER PRIMARY KEY, location GEOMETRY)");
    exec(&db, "INSERT INTO pts VALUES (1, POINT3D(0.0, 0.0, 0.0))");
    exec(&db, "INSERT INTO pts VALUES (2, POINT3D(5.0, 5.0, 5.0))");

    exec(&db, "CREATE INDEX my_octree ON pts(location) USING OCTREE");

    let r = rows(&db, "SELECT id FROM pts WHERE ST_KNN_3D(location, 0.0, 0.0, 0.0, 1)");
    assert_eq!(r.len(), 1);
}

// ============================================================================
// 3. ST_WITHIN_3D range queries
// ============================================================================

#[test]
fn test_st_within_3d() {
    let (db, _dir) = create_db();

    exec(&db, "CREATE TABLE voxels (id INTEGER PRIMARY KEY, pt GEOMETRY)");
    for i in 0..10i64 {
        let v = i as f64;
        exec(&db, &format!("INSERT INTO voxels VALUES ({}, POINT3D({}, {}, {}))", i + 1, v, v, v));
    }

    exec(&db, "CREATE OCTREE INDEX voxels_pt ON voxels(pt)");

    // Points within [0,0,0] to [4,4,4] — should match id 1..5
    let r = rows(&db, "SELECT id FROM voxels WHERE ST_WITHIN_3D(pt, 0.0, 0.0, 0.0, 4.0, 4.0, 4.0)");
    assert_eq!(r.len(), 5, "Should find 5 points in range [0,4]");

    // Narrower range [0,0,0] to [2,2,2] — should match id 1..3
    let r2 = rows(&db, "SELECT id FROM voxels WHERE ST_WITHIN_3D(pt, 0.0, 0.0, 0.0, 2.0, 2.0, 2.0)");
    assert_eq!(r2.len(), 3, "Should find 3 points in range [0,2]");
}

// ============================================================================
// 4. ST_KNN_3D queries
// ============================================================================

#[test]
fn test_st_knn_3d_accuracy() {
    let (db, _dir) = create_db();

    exec(&db, "CREATE TABLE scan (id INTEGER PRIMARY KEY, pt GEOMETRY)");
    // Insert points at known positions
    exec(&db, "INSERT INTO scan VALUES (1, POINT3D(0.0, 0.0, 0.0))");
    exec(&db, "INSERT INTO scan VALUES (2, POINT3D(1.0, 0.0, 0.0))");
    exec(&db, "INSERT INTO scan VALUES (3, POINT3D(0.0, 1.0, 0.0))");
    exec(&db, "INSERT INTO scan VALUES (4, POINT3D(10.0, 10.0, 10.0))");
    exec(&db, "INSERT INTO scan VALUES (5, POINT3D(0.0, 0.0, 1.0))");

    exec(&db, "CREATE OCTREE INDEX scan_pt ON scan(pt)");

    // Query for 3 nearest to origin
    let r = rows(&db, "SELECT id FROM scan WHERE ST_KNN_3D(pt, 0.0, 0.0, 0.0, 3)");
    assert_eq!(r.len(), 3);

    // Should be ids 1, 2, 3 (distances 0, 1, 1) — id 5 also at distance 1
    let ids: Vec<i64> = r.iter().map(|row| match &row[0] {
        Value::Integer(i) => *i,
        _ => panic!("Expected integer"),
    }).collect();
    assert!(ids.contains(&1), "Origin point must be in result");
}

// ============================================================================
// 5. ST_RADIUS_3D queries
// ============================================================================

#[test]
fn test_st_radius_3d() {
    let (db, _dir) = create_db();

    exec(&db, "CREATE TABLE radar (id INTEGER PRIMARY KEY, pt GEOMETRY)");
    exec(&db, "INSERT INTO radar VALUES (1, POINT3D(0.0, 0.0, 0.0))");
    exec(&db, "INSERT INTO radar VALUES (2, POINT3D(1.0, 0.0, 0.0))");
    exec(&db, "INSERT INTO radar VALUES (3, POINT3D(0.0, 1.0, 0.0))");
    exec(&db, "INSERT INTO radar VALUES (4, POINT3D(5.0, 5.0, 5.0))");
    exec(&db, "INSERT INTO radar VALUES (5, POINT3D(0.0, 0.0, 1.5))");

    exec(&db, "CREATE OCTREE INDEX radar_pt ON radar(pt)");

    // Radius 1.1 from origin — should match ids 1, 2, 3 (dist 0, 1, 1) but not 5 (dist 1.5)
    let r = rows(&db, "SELECT id FROM radar WHERE ST_RADIUS_3D(pt, 0.0, 0.0, 0.0, 1.1)");
    assert_eq!(r.len(), 3, "Radius 1.1 should find 3 points");

    // Radius 2.0 from origin — should match 1, 2, 3, 5
    let r2 = rows(&db, "SELECT id FROM radar WHERE ST_RADIUS_3D(pt, 0.0, 0.0, 0.0, 2.0)");
    assert_eq!(r2.len(), 4, "Radius 2.0 should find 4 points");
}

// ============================================================================
// 6. ORDER BY ST_DISTANCE_3D
// ============================================================================

#[test]
fn test_order_by_st_distance_3d() {
    let (db, _dir) = create_db();

    exec(&db, "CREATE TABLE points3d (id INTEGER PRIMARY KEY, pt GEOMETRY)");
    exec(&db, "INSERT INTO points3d VALUES (1, POINT3D(0.0, 0.0, 0.0))");
    exec(&db, "INSERT INTO points3d VALUES (2, POINT3D(1.0, 1.0, 1.0))");
    exec(&db, "INSERT INTO points3d VALUES (3, POINT3D(5.0, 5.0, 5.0))");
    exec(&db, "INSERT INTO points3d VALUES (4, POINT3D(10.0, 10.0, 10.0))");

    exec(&db, "CREATE OCTREE INDEX points3d_pt ON points3d(pt)");

    let r = rows(&db, "SELECT id FROM points3d ORDER BY ST_DISTANCE_3D(pt, 0.0, 0.0, 0.0) LIMIT 2");
    assert_eq!(r.len(), 2);

    let first_id = match &r[0][0] {
        Value::Integer(i) => *i,
        _ => panic!("Expected integer"),
    };
    assert_eq!(first_id, 1, "Origin should be first");
}

// ============================================================================
// 7. INSERT/UPDATE/DELETE index sync
// ============================================================================

#[test]
fn test_insert_delete_sync() {
    let (db, _dir) = create_db();

    exec(&db, "CREATE TABLE dyn (id INTEGER PRIMARY KEY, pt GEOMETRY)");
    exec(&db, "CREATE OCTREE INDEX dyn_pt ON dyn(pt)");

    exec(&db, "INSERT INTO dyn VALUES (1, POINT3D(1.0, 1.0, 1.0))");
    exec(&db, "INSERT INTO dyn VALUES (2, POINT3D(2.0, 2.0, 2.0))");

    // KNN before delete
    let r = rows(&db, "SELECT id FROM dyn WHERE ST_KNN_3D(pt, 0.0, 0.0, 0.0, 2)");
    assert_eq!(r.len(), 2);

    // Delete one
    exec(&db, "DELETE FROM dyn WHERE id = 1");

    // KNN after delete — should only find id 2
    let r2 = rows(&db, "SELECT id FROM dyn WHERE ST_KNN_3D(pt, 0.0, 0.0, 0.0, 2)");
    assert_eq!(r2.len(), 1);
    let id = match &r2[0][0] {
        Value::Integer(i) => *i,
        _ => panic!("Expected integer"),
    };
    assert_eq!(id, 2);
}

#[test]
fn test_update_sync() {
    let (db, _dir) = create_db();

    exec(&db, "CREATE TABLE movable (id INTEGER PRIMARY KEY, pt GEOMETRY)");
    exec(&db, "CREATE OCTREE INDEX movable_pt ON movable(pt)");

    exec(&db, "INSERT INTO movable VALUES (1, POINT3D(0.0, 0.0, 0.0))");

    // Before update — closest to (0,0,0)
    let r1 = rows(&db, "SELECT id FROM movable WHERE ST_KNN_3D(pt, 0.0, 0.0, 0.0, 1)");
    assert_eq!(r1.len(), 1);

    // Move the point far away
    exec(&db, "UPDATE movable SET pt = POINT3D(100.0, 100.0, 100.0) WHERE id = 1");

    // After update — closest to (0,0,0) should still be id=1 but at distance 100*sqrt(3)
    let r2 = rows(&db, "SELECT id FROM movable WHERE ST_KNN_3D(pt, 0.0, 0.0, 0.0, 1)");
    assert_eq!(r2.len(), 1);

    // Radius search should NOT find it near origin
    let r3 = rows(&db, "SELECT id FROM movable WHERE ST_RADIUS_3D(pt, 0.0, 0.0, 0.0, 10.0)");
    assert_eq!(r3.len(), 0, "Moved point should not be near origin");

    // But should find it near (100,100,100)
    let r4 = rows(&db, "SELECT id FROM movable WHERE ST_RADIUS_3D(pt, 100.0, 100.0, 100.0, 1.0)");
    assert_eq!(r4.len(), 1, "Moved point should be near (100,100,100)");
}

// ============================================================================
// 8. Flush + checkpoint persistence
// ============================================================================

#[test]
fn test_flush_and_reopen() {
    let dir = TempDir::new().expect("temp dir");

    // Create and populate
    {
        let db = Database::create(dir.path()).expect("create db");
        exec(&db, "CREATE TABLE persistent (id INTEGER PRIMARY KEY, pt GEOMETRY)");
        for i in 1..=10i64 {
            let v = i as f64;
            exec(&db, &format!("INSERT INTO persistent VALUES ({}, POINT3D({}, {}, {}))", i, v, v, v));
        }
        exec(&db, "CREATE OCTREE INDEX persistent_pt ON persistent(pt)");
        db.flush().expect("flush");
        db.checkpoint().expect("checkpoint");
    }

    // Reopen and query
    {
        let db = Database::open(dir.path()).expect("open db");
        let r = rows(&db, "SELECT id FROM persistent WHERE ST_KNN_3D(pt, 1.0, 1.0, 1.0, 3)");
        assert_eq!(r.len(), 3, "KNN should work after reopen");

        let r2 = rows(&db, "SELECT id FROM persistent WHERE ST_WITHIN_3D(pt, 0.0, 0.0, 0.0, 5.0, 5.0, 5.0)");
        assert_eq!(r2.len(), 5, "Range query should work after reopen");
    }
}

// ============================================================================
// 9. 2D + 3D indexes on same table
// ============================================================================

#[test]
fn test_2d_3d_coexistence() {
    let (db, _dir) = create_db();

    exec(&db, "CREATE TABLE geo (id INTEGER PRIMARY KEY, location GEOMETRY, cloud GEOMETRY)");

    // Insert 2D + 3D data
    for i in 1..=5i64 {
        let v = i as f64;
        exec(&db, &format!(
            "INSERT INTO geo VALUES ({}, POINT({}, {}), POINT3D({}, {}, {}))",
            i, v, v, v * 2.0, v * 2.0, v * 2.0
        ));
    }

    // Create both 2D and 3D indexes
    exec(&db, "CREATE GEOMETRY INDEX geo_location ON geo(location)");
    exec(&db, "CREATE OCTREE INDEX geo_cloud ON geo(cloud)");

    // 2D query
    let r2d = rows(&db, "SELECT id FROM geo WHERE ST_KNN(location, 3.0, 3.0, 3)");
    assert_eq!(r2d.len(), 3, "2D KNN should work");

    // 3D query
    let r3d = rows(&db, "SELECT id FROM geo WHERE ST_KNN_3D(cloud, 6.0, 6.0, 6.0, 3)");
    assert_eq!(r3d.len(), 3, "3D KNN should work");
}

// ============================================================================
// 10. DROP INDEX for Octree
// ============================================================================

#[test]
fn test_drop_octree_index() {
    let (db, _dir) = create_db();

    exec(&db, "CREATE TABLE droptest (id INTEGER PRIMARY KEY, pt GEOMETRY)");
    exec(&db, "INSERT INTO droptest VALUES (1, POINT3D(1.0, 2.0, 3.0))");
    exec(&db, "CREATE OCTREE INDEX droptest_pt ON droptest(pt)");

    // Verify index works
    let r = rows(&db, "SELECT id FROM droptest WHERE ST_KNN_3D(pt, 1.0, 2.0, 3.0, 1)");
    assert_eq!(r.len(), 1);

    // Drop the index
    exec(&db, "DROP INDEX droptest_pt");

    // Should still work via full-scan fallback (evaluator handles it)
    let r2 = rows(&db, "SELECT id FROM droptest WHERE ST_KNN_3D(pt, 1.0, 2.0, 3.0, 1)");
    // After drop, ST_KNN_3D falls back to evaluator which needs __row_id__
    // In full-scan path, this will be available, so it should still work
    assert!(r2.len() <= 1, "After drop, query should fall back to scan");
}

// ============================================================================
// 11. Larger dataset — performance sanity check
// ============================================================================

#[test]
fn test_ioctree_1000_points() {
    let (db, _dir) = create_db();

    exec(&db, "CREATE TABLE large_cloud (id INTEGER PRIMARY KEY, pt GEOMETRY)");

    for i in 1..=1000i64 {
        let x = (i % 10) as f64;
        let y = ((i / 10) % 10) as f64;
        let z = ((i / 100) % 10) as f64;
        exec(&db, &format!(
            "INSERT INTO large_cloud VALUES ({}, POINT3D({}, {}, {}))",
            i, x, y, z
        ));
    }

    exec(&db, "CREATE OCTREE INDEX large_cloud_pt ON large_cloud(pt)");

    // KNN — find 5 nearest to (5,5,5)
    let r = rows(&db, "SELECT id FROM large_cloud WHERE ST_KNN_3D(pt, 5.0, 5.0, 5.0, 5)");
    assert_eq!(r.len(), 5);

    // Range — points in [0,0,0]-[3,3,3]
    let r2 = rows(&db, "SELECT id FROM large_cloud WHERE ST_WITHIN_3D(pt, 0.0, 0.0, 0.0, 3.0, 3.0, 3.0)");
    // Should be 4*4*4 = 64 points (x,y,z each 0..3)
    assert_eq!(r2.len(), 64, "Expected 64 points in [0,3]^3");

    // Radius — points within sqrt(3) ≈ 1.73 of (5,5,5)
    let r3 = rows(&db, "SELECT id FROM large_cloud WHERE ST_RADIUS_3D(pt, 5.0, 5.0, 5.0, 1.74)");
    assert!(r3.len() >= 1, "Should find at least the exact point");
}
