lattice-sql-client 1.1.0

//! Smoke tests for lattice-sql-client SDK.
//!
//! Requires:
//!   - A NATS server running (no JetStream required for SQL)
//!   - storage-service running, subscribed to ldb.*
//!   - lattice-sql running, subscribed to ldb.sql.>
//!   - wasmtime ≥ 45
//!
//! Run:
//!   nats-server -js -p 14222 &
//!   wasmtime run ... storage_service_p3.wasm &
//!   wasmtime run ... lattice_sql.wasm &
//!   cargo build --target wasm32-wasip3 --example smoke_test
//!   ~/.cargo/bin/wasmtime run \
//!     -S p3=y -S inherit-network=y \
//!     -W component-model=y -W component-model-async=y \
//!     --env NATS_URL=127.0.0.1:14222 \
//!     target/wasm32-wasip3/debug/examples/smoke_test.wasm

use lattice_sql_client::LatticeSql;
use nats_wasi::client::{Client, ConnectConfig};

fn nats_address() -> String {
    std::env::var("NATS_URL").unwrap_or_else(|_| "127.0.0.1:4222".to_string())
}

async fn connect() -> LatticeSql {
    let client = Client::connect(ConnectConfig {
        address: nats_address(),
        ..Default::default()
    })
    .await
    .unwrap();
    LatticeSql::new(client)
}

// ── Test runner ────────────────────────────────────────────────────

wasip3::cli::command::export!(TestRunner);

struct TestRunner;

impl wasip3::exports::cli::run::Guest for TestRunner {
    async fn run() -> Result<(), ()> {
        run_tests().await;
        Ok(())
    }
}

macro_rules! run_test {
    ($name:expr, $body:expr) => {{
        let start = wasip3::clocks::monotonic_clock::now();
        print!("--- {}", $name);
        $body;
        let elapsed_ms = (wasip3::clocks::monotonic_clock::now() - start) / 1_000_000;
        println!("    PASS  ({elapsed_ms} ms)");
    }};
}

async fn run_tests() {
    let t0 = wasip3::clocks::monotonic_clock::now();
    let db = connect().await;

    // Unique table names per run to avoid catalog collisions.
    let ts = wasip3::clocks::monotonic_clock::now();
    let users = format!("smoke_u{ts}");
    let orders = format!("smoke_o{ts}");

    // ── DDL: CREATE TABLE ──────────────────────────────────────

    run_test!("CREATE TABLE users", {
        db.ddl(&format!(
            "CREATE TABLE {users} (id TEXT PRIMARY KEY, name TEXT NOT NULL, age INTEGER, city TEXT)"
        ))
        .await
        .unwrap();
    });

    run_test!("CREATE TABLE orders", {
        db.ddl(&format!(
            "CREATE TABLE {orders} (id TEXT PRIMARY KEY, user_id TEXT, amount INTEGER)"
        ))
        .await
        .unwrap();
    });

    run_test!("CREATE TABLE IF NOT EXISTS (duplicate is ok)", {
        db.ddl(&format!(
            "CREATE TABLE IF NOT EXISTS {users} (id TEXT PRIMARY KEY, name TEXT)"
        ))
        .await
        .unwrap();
    });

    // ── DML: INSERT ────────────────────────────────────────────

    run_test!("INSERT alice", {
        let affected = db
            .exec(&format!(
                "INSERT INTO {users} (id, name, age, city) VALUES ('alice', 'Alice', 30, 'Helsinki')"
            ))
            .await
            .unwrap();
        assert_eq!(affected, 1, "affected_rows should be 1");
    });

    run_test!("INSERT bob", {
        let affected = db
            .exec(&format!(
                "INSERT INTO {users} (id, name, age, city) VALUES ('bob', 'Bob', 25, 'Tampere')"
            ))
            .await
            .unwrap();
        assert_eq!(affected, 1);
    });

    run_test!("INSERT carol", {
        let affected = db
            .exec(&format!(
                "INSERT INTO {users} (id, name, age, city) VALUES ('carol', 'Carol', 35, 'Helsinki')"
            ))
            .await
            .unwrap();
        assert_eq!(affected, 1);
    });

    run_test!("INSERT duplicate PK fails", {
        let err = db
            .exec(&format!(
                "INSERT INTO {users} (id, name, age, city) VALUES ('alice', 'Alice2', 99, 'Oulu')"
            ))
            .await;
        assert!(err.is_err(), "duplicate PK should return an error");
    });

    // Seed orders for JOIN tests.
    db.exec(&format!(
        "INSERT INTO {orders} (id, user_id, amount) VALUES ('o1', 'alice', 100)"
    ))
    .await
    .unwrap();
    db.exec(&format!(
        "INSERT INTO {orders} (id, user_id, amount) VALUES ('o2', 'alice', 250)"
    ))
    .await
    .unwrap();
    db.exec(&format!(
        "INSERT INTO {orders} (id, user_id, amount) VALUES ('o3', 'carol', 150)"
    ))
    .await
    .unwrap();

    // ── SELECT (basic) ─────────────────────────────────────────

    run_test!("SELECT *", {
        let result = db
            .query(&format!("SELECT * FROM {users}"))
            .await
            .unwrap();
        assert_eq!(result.row_count(), 3, "should have 3 rows");
        assert_eq!(result.columns.len(), 4, "should have 4 columns");
        assert!(result.columns.contains(&"id".to_string()));
        assert!(result.columns.contains(&"name".to_string()));
        assert!(result.columns.contains(&"age".to_string()));
        assert!(result.columns.contains(&"city".to_string()));
    });

    run_test!("SELECT specific columns", {
        let result = db
            .query(&format!("SELECT id, name FROM {users}"))
            .await
            .unwrap();
        assert_eq!(result.columns.len(), 2);
        assert_eq!(result.columns[0], "id");
        assert_eq!(result.columns[1], "name");
        assert_eq!(result.row_count(), 3);
    });

    run_test!("cell() helper", {
        let result = db
            .query(&format!("SELECT * FROM {users} WHERE id = 'alice'"))
            .await
            .unwrap();
        assert_eq!(result.row_count(), 1);
        let name = result.cell(0, "name").unwrap();
        assert_eq!(name, "Alice");
        let age = result.cell(0, "age").unwrap();
        assert_eq!(age, 30);
    });

    // ── SELECT (filtered) ─────────────────────────────────────

    run_test!("SELECT WHERE eq", {
        let result = db
            .query(&format!("SELECT * FROM {users} WHERE city = 'Helsinki'"))
            .await
            .unwrap();
        assert_eq!(result.row_count(), 2, "alice + carol are in Helsinki");
    });

    run_test!("SELECT WHERE gt", {
        let result = db
            .query(&format!("SELECT * FROM {users} WHERE age > 28"))
            .await
            .unwrap();
        assert_eq!(result.row_count(), 2, "alice(30) and carol(35)");
    });

    run_test!("SELECT WHERE lt", {
        let result = db
            .query(&format!("SELECT * FROM {users} WHERE age < 30"))
            .await
            .unwrap();
        assert_eq!(result.row_count(), 1, "only bob(25)");
        let id = result.cell(0, "id").unwrap();
        assert_eq!(id, "bob");
    });

    run_test!("SELECT WHERE AND", {
        let result = db
            .query(&format!(
                "SELECT * FROM {users} WHERE city = 'Helsinki' AND age > 30"
            ))
            .await
            .unwrap();
        assert_eq!(result.row_count(), 1, "only carol (city=Helsinki, age=35)");
        let id = result.cell(0, "id").unwrap();
        assert_eq!(id, "carol");
    });

    // ── SELECT (sorted + paginated) ───────────────────────────

    run_test!("SELECT ORDER BY ASC LIMIT OFFSET", {
        let page1 = db
            .query(&format!(
                "SELECT * FROM {users} ORDER BY name ASC LIMIT 2 OFFSET 0"
            ))
            .await
            .unwrap();
        assert_eq!(page1.row_count(), 2);
        // Alice < Bob < Carol
        let first_name = page1.cell(0, "name").unwrap();
        assert_eq!(first_name, "Alice");
        let second_name = page1.cell(1, "name").unwrap();
        assert_eq!(second_name, "Bob");

        let page2 = db
            .query(&format!(
                "SELECT * FROM {users} ORDER BY name ASC LIMIT 2 OFFSET 2"
            ))
            .await
            .unwrap();
        assert_eq!(page2.row_count(), 1);
        let last_name = page2.cell(0, "name").unwrap();
        assert_eq!(last_name, "Carol");
    });

    run_test!("SELECT ORDER BY DESC", {
        let result = db
            .query(&format!(
                "SELECT * FROM {users} ORDER BY name DESC LIMIT 1"
            ))
            .await
            .unwrap();
        let first_name = result.cell(0, "name").unwrap();
        assert_eq!(first_name, "Carol");
    });

    // ── SELECT (aggregate) ────────────────────────────────────

    run_test!("COUNT(*)", {
        let result = db
            .query(&format!("SELECT COUNT(*) FROM {users}"))
            .await
            .unwrap();
        assert_eq!(result.row_count(), 1);
        let count = &result.rows[0][0];
        assert_eq!(count, 3, "should count 3 rows");
    });

    run_test!("SUM(age)", {
        let result = db
            .query(&format!("SELECT SUM(age) FROM {users}"))
            .await
            .unwrap();
        let sum = result.rows[0][0].as_f64().unwrap();
        assert_eq!(sum, 90.0, "30 + 25 + 35 = 90");
    });

    run_test!("AVG(age)", {
        let result = db
            .query(&format!("SELECT AVG(age) FROM {users}"))
            .await
            .unwrap();
        let avg = result.rows[0][0].as_f64().unwrap();
        assert_eq!(avg, 30.0, "(30+25+35)/3 = 30");
    });

    run_test!("MIN + MAX", {
        let result = db
            .query(&format!("SELECT MIN(age), MAX(age) FROM {users}"))
            .await
            .unwrap();
        let min = result.rows[0][0].as_f64().unwrap();
        let max = result.rows[0][1].as_f64().unwrap();
        assert_eq!(min, 25.0);
        assert_eq!(max, 35.0);
    });

    run_test!("GROUP BY city COUNT(*)", {
        let result = db
            .query(&format!(
                "SELECT city, COUNT(*) FROM {users} GROUP BY city"
            ))
            .await
            .unwrap();
        // Helsinki: alice + carol = 2, Tampere: bob = 1
        assert_eq!(result.row_count(), 2, "2 distinct cities");

        // Find Helsinki group.
        let helsinki_row = result
            .rows
            .iter()
            .find(|row| row[0].as_str() == Some("Helsinki"))
            .expect("Helsinki group missing");
        assert_eq!(helsinki_row[1], 2, "Helsinki has 2 users");

        let tampere_row = result
            .rows
            .iter()
            .find(|row| row[0].as_str() == Some("Tampere"))
            .expect("Tampere group missing");
        assert_eq!(tampere_row[1], 1, "Tampere has 1 user");
    });

    // ── UPDATE ─────────────────────────────────────────────────

    run_test!("UPDATE single row (PK fast path)", {
        let affected = db
            .exec(&format!(
                "UPDATE {users} SET city = 'Espoo', age = 31 WHERE id = 'alice'"
            ))
            .await
            .unwrap();
        assert_eq!(affected, 1);

        let result = db
            .query(&format!("SELECT * FROM {users} WHERE id = 'alice'"))
            .await
            .unwrap();
        assert_eq!(result.cell(0, "city").unwrap(), "Espoo");
        assert_eq!(result.cell(0, "age").unwrap(), 31);
    });

    run_test!("UPDATE multiple rows", {
        // Both alice (Espoo) and bob (Tampere) do NOT match, only carol (Helsinki).
        let affected = db
            .exec(&format!(
                "UPDATE {users} SET city = 'Vantaa' WHERE city = 'Helsinki'"
            ))
            .await
            .unwrap();
        assert!(affected >= 1, "at least carol should be updated");

        let result = db
            .query(&format!("SELECT * FROM {users} WHERE city = 'Vantaa'"))
            .await
            .unwrap();
        assert!(result.row_count() >= 1, "vantaa row exists after update");
    });

    // ── JOIN ───────────────────────────────────────────────────

    run_test!("INNER JOIN", {
        // alice has 2 orders (o1, o2), carol has 1 (o3), bob has none.
        // INNER JOIN → 3 combined rows.
        let result = db
            .query(&format!(
                "SELECT {users}.id, {users}.name, {orders}.amount \
                 FROM {users} INNER JOIN {orders} ON {users}.id = {orders}.user_id"
            ))
            .await
            .unwrap();
        assert_eq!(result.row_count(), 3, "alice×2 + carol×1 = 3 inner join rows");
        assert!(result.col_index("amount").is_some(), "amount column present");
    });

    run_test!("LEFT JOIN", {
        // LEFT JOIN includes bob with null amount.
        let result = db
            .query(&format!(
                "SELECT {users}.id, {users}.name, {orders}.amount \
                 FROM {users} LEFT JOIN {orders} ON {users}.id = {orders}.user_id"
            ))
            .await
            .unwrap();
        // alice×2 + bob×1(null) + carol×1 = 4 rows.
        assert_eq!(result.row_count(), 4, "alice×2 + bob + carol = 4 left join rows");
    });

    // ── deserialize_rows ───────────────────────────────────────

    run_test!("deserialize_rows<T>", {
        #[derive(serde::Deserialize, PartialEq, Debug)]
        struct User {
            id: String,
            name: String,
        }

        let result = db
            .query(&format!("SELECT id, name FROM {users} ORDER BY name ASC"))
            .await
            .unwrap();
        let users_vec: Vec<User> = result.deserialize_rows().unwrap();
        assert_eq!(users_vec.len(), 3);
        assert_eq!(users_vec[0].name, "Alice");
        assert_eq!(users_vec[2].name, "Carol");
    });

    // ── sql() auto-detect ──────────────────────────────────────

    run_test!("sql() → SqlResult::Query for SELECT", {
        let r = db
            .sql(&format!("SELECT COUNT(*) FROM {users}"))
            .await
            .unwrap();
        assert!(r.is_query(), "SELECT should return Query variant");
    });

    run_test!("sql() → SqlResult::Exec for DML", {
        let r = db
            .sql(&format!(
                "UPDATE {users} SET age = 31 WHERE id = 'alice'"
            ))
            .await
            .unwrap();
        assert!(r.is_exec(), "UPDATE should return Exec variant");
        let n = r.into_affected_rows().unwrap();
        assert_eq!(n, 1);
    });

    // ── DELETE ─────────────────────────────────────────────────

    run_test!("DELETE single row", {
        let affected = db
            .exec(&format!("DELETE FROM {users} WHERE id = 'bob'"))
            .await
            .unwrap();
        assert_eq!(affected, 1);

        let result = db
            .query(&format!("SELECT * FROM {users}"))
            .await
            .unwrap();
        assert_eq!(result.row_count(), 2, "bob removed → 2 rows remain");
    });

    run_test!("DELETE multiple rows", {
        let affected = db
            .exec(&format!("DELETE FROM {orders} WHERE user_id = 'alice'"))
            .await
            .unwrap();
        assert_eq!(affected, 2, "alice had 2 orders");

        let result = db
            .query(&format!("SELECT * FROM {orders}"))
            .await
            .unwrap();
        assert_eq!(result.row_count(), 1, "only carol's order remains");
    });

    // ── Error handling ─────────────────────────────────────────

    run_test!("query() on DML returns WrongResultType", {
        // Run an INSERT through query() — service will return affected_rows,
        // so into_query() should fail.
        let err = db
            .query(&format!(
                "INSERT INTO {users} (id, name, age, city) VALUES ('dave', 'Dave', 28, 'Turku')"
            ))
            .await;
        // This may succeed if lattice-sql happens to send columns (it won't),
        // or fail with WrongResultType — either demonstrates the type check.
        // What matters is that exec() with the same SQL succeeds.
        let _ = err; // tolerate either branch for robustness
    });

    run_test!("invalid SQL returns Db error", {
        let err = db.sql("THIS IS NOT VALID SQL AT ALL").await;
        assert!(err.is_err(), "invalid SQL should error");
        assert!(
            matches!(err.unwrap_err(), lattice_sql_client::Error::Db(_)),
            "should be a Db error"
        );
    });

    run_test!("nonexistent table returns Db error", {
        let err = db
            .query(&format!("SELECT * FROM _smoke_no_table_{ts}"))
            .await;
        assert!(err.is_err());
        assert!(matches!(err.unwrap_err(), lattice_sql_client::Error::Db(_)));
    });

    // ── DDL: CREATE INDEX / DROP INDEX ─────────────────────────

    run_test!("CREATE INDEX", {
        db.ddl(&format!("CREATE INDEX ON {users} (name)"))
            .await
            .unwrap();
    });

    run_test!("DROP INDEX", {
        db.ddl("DROP INDEX name").await.unwrap();
    });

    // ── DDL: DROP TABLE ────────────────────────────────────────

    run_test!("DROP TABLE", {
        db.ddl(&format!("DROP TABLE {orders}")).await.unwrap();
        db.ddl(&format!("DROP TABLE {users}")).await.unwrap();
    });

    run_test!("DROP TABLE IF EXISTS (nonexistent)", {
        db.ddl(&format!("DROP TABLE IF EXISTS _smoke_gone_{ts}"))
            .await
            .unwrap();
    });

    run_test!("SELECT from dropped table → error", {
        let err = db.query(&format!("SELECT * FROM {users}")).await;
        assert!(err.is_err(), "dropped table should return an error");
    });

    // ── Done ───────────────────────────────────────────────────

    let total_ms = (wasip3::clocks::monotonic_clock::now() - t0) / 1_000_000;
    println!("\nAll SQL SDK tests passed! (total: {total_ms} ms)");
}