vantage-sql 0.5.0

//! Vista integration: typed `Table<SqliteDB, _>` → `Vista` and YAML → `Vista`.
//!
//! Uses in-memory SQLite — no external setup required.

#![cfg(feature = "vista")]

use std::error::Error;

use ciborium::Value as CborValue;
use vantage_dataset::prelude::*;
use vantage_sql::sqlite::{AnySqliteType, SqliteDB};
use vantage_sql::sqlite_expr;
use vantage_table::table::Table;
use vantage_types::{EmptyEntity, Record, entity};
use vantage_vista::VistaFactory;

type TestResult = std::result::Result<(), Box<dyn Error>>;

async fn setup() -> SqliteDB {
    let db = SqliteDB::connect("sqlite::memory:").await.unwrap();

    sqlx::query(
        "CREATE TABLE product (
            id TEXT PRIMARY KEY,
            name TEXT NOT NULL,
            price INTEGER NOT NULL,
            is_deleted INTEGER NOT NULL DEFAULT 0
        )",
    )
    .execute(db.pool())
    .await
    .unwrap();

    sqlx::query(
        "INSERT INTO product VALUES \
         ('a', 'Alpha', 10, 0), \
         ('b', 'Beta', 20, 1), \
         ('c', 'Gamma', 30, 0)",
    )
    .execute(db.pool())
    .await
    .unwrap();

    db
}

fn product_table(db: SqliteDB) -> Table<SqliteDB, EmptyEntity> {
    Table::<SqliteDB, EmptyEntity>::new("product", db)
        .with_id_column("id")
        .with_column_of::<String>("name")
        .with_column_of::<i64>("price")
        .with_column_of::<bool>("is_deleted")
}

#[tokio::test]
async fn vista_lists_typed_sqlite_as_cbor() -> TestResult {
    let db = setup().await;
    let table = product_table(db.clone());
    let vista = db.vista_factory().from_table(table)?;

    assert_eq!(vista.name(), "product");
    assert_eq!(vista.get_id_column(), Some("id"));

    let rows = vista.list_values().await?;
    assert_eq!(rows.len(), 3);

    let alpha = rows.get("a").expect("row a");
    assert_eq!(
        alpha.get("name"),
        Some(&CborValue::Text("Alpha".to_string()))
    );
    assert_eq!(alpha.get("price"), Some(&CborValue::Integer(10i64.into())));
    Ok(())
}

#[tokio::test]
async fn vista_get_value_by_id() -> TestResult {
    let db = setup().await;
    let table = product_table(db.clone());
    let vista = db.vista_factory().from_table(table)?;

    let row = vista
        .get_value(&"b".to_string())
        .await?
        .expect("row b exists");
    assert_eq!(row.get("name"), Some(&CborValue::Text("Beta".to_string())));

    let missing = vista.get_value(&"nope".to_string()).await?;
    assert!(missing.is_none());
    Ok(())
}

#[tokio::test]
async fn vista_count_with_eq_condition() -> TestResult {
    let db = setup().await;
    let table = product_table(db.clone());
    let mut vista = db.vista_factory().from_table(table)?;

    assert_eq!(vista.get_count().await?, 3);

    vista.add_condition_eq("is_deleted", CborValue::Bool(false))?;
    assert_eq!(vista.get_count().await?, 2);

    let rows = vista.list_values().await?;
    assert_eq!(rows.len(), 2);
    assert!(rows.contains_key("a"));
    assert!(rows.contains_key("c"));
    assert!(!rows.contains_key("b"));
    Ok(())
}

#[tokio::test]
async fn vista_yaml_loads_table_and_columns() -> TestResult {
    let db = setup().await;

    let yaml = r#"
name: product_view
columns:
  id:
    type: string
    flags: [id]
  name:
    type: string
    flags: [title, searchable]
  price:
    type: int
sqlite:
  table: product
"#;

    let vista = db.vista_factory().from_yaml(yaml)?;

    assert_eq!(vista.name(), "product_view");
    assert_eq!(vista.get_id_column(), Some("id"));
    assert_eq!(vista.get_title_columns(), vec!["name"]);

    let rows = vista.list_values().await?;
    assert_eq!(rows.len(), 3);
    assert!(rows.contains_key("a"));
    Ok(())
}

#[tokio::test]
async fn vista_writes_round_trip_via_cbor() -> TestResult {
    let db = setup().await;
    let table = product_table(db.clone());
    let vista = db.vista_factory().from_table(table)?;

    let record: Record<CborValue> = vec![
        ("name".to_string(), CborValue::Text("Delta".into())),
        ("price".to_string(), CborValue::Integer(99i64.into())),
        ("is_deleted".to_string(), CborValue::Bool(false)),
    ]
    .into_iter()
    .collect();

    vista.insert_value(&"d".to_string(), &record).await?;

    let fetched = vista.get_value(&"d".to_string()).await?.expect("inserted");
    assert_eq!(fetched.get("name"), Some(&CborValue::Text("Delta".into())));

    vista.delete(&"d".to_string()).await?;
    assert!(vista.get_value(&"d".to_string()).await?.is_none());
    Ok(())
}

/// Regression: `with_expression` survives `from_table`. Before the shell was
/// generic over `E`, the factory called `into_entity::<EmptyEntity>` which
/// reset the expressions map, silently dropping any computed columns.
#[tokio::test]
async fn vista_preserves_with_expression_columns() -> TestResult {
    let db = setup().await;

    #[entity(SqliteType)]
    #[derive(Debug, Clone, PartialEq, Default)]
    struct Product {
        name: String,
        price: i64,
    }

    let table = Table::<SqliteDB, Product>::new("product", db.clone())
        .with_id_column("id")
        .with_column_of::<String>("name")
        .with_column_of::<i64>("price")
        .with_column_of::<bool>("is_deleted")
        .with_expression("price_doubled", |_| sqlite_expr!("\"price\" * 2"));

    let vista = db.vista_factory().from_table(table)?;

    let rows = vista.list_values().await?;
    let alpha = rows.get("a").expect("row a");
    assert_eq!(
        alpha.get("price_doubled"),
        Some(&CborValue::Integer(20i64.into())),
        "computed column should appear in vista output"
    );
    let gamma = rows.get("c").expect("row c");
    assert_eq!(
        gamma.get("price_doubled"),
        Some(&CborValue::Integer(60i64.into()))
    );
    Ok(())
}

#[tokio::test]
async fn vista_capabilities_advertise_read_write() -> TestResult {
    let db = setup().await;
    let table = product_table(db.clone());
    let vista = db.vista_factory().from_table(table)?;

    let caps = vista.capabilities();
    assert!(caps.can_count);
    assert!(caps.can_insert);
    assert!(caps.can_update);
    assert!(caps.can_delete);
    assert!(!caps.can_subscribe);
    Ok(())
}

async fn setup_clients_orders() -> SqliteDB {
    let db = SqliteDB::connect("sqlite::memory:").await.unwrap();
    sqlx::query("CREATE TABLE client (id TEXT PRIMARY KEY, name TEXT NOT NULL)")
        .execute(db.pool())
        .await
        .unwrap();
    sqlx::query(
        "CREATE TABLE orders (id TEXT PRIMARY KEY, client_id TEXT NOT NULL, total INTEGER NOT NULL)",
    )
    .execute(db.pool())
    .await
    .unwrap();
    sqlx::query("INSERT INTO client VALUES ('alice','Alice'),('bob','Bob')")
        .execute(db.pool())
        .await
        .unwrap();
    sqlx::query("INSERT INTO orders VALUES ('o1','alice',10),('o2','alice',20),('o3','bob',30)")
        .execute(db.pool())
        .await
        .unwrap();
    db
}

fn orders_table(db: SqliteDB) -> Table<SqliteDB, EmptyEntity> {
    Table::<SqliteDB, EmptyEntity>::new("orders", db)
        .with_id_column("id")
        .with_column_of::<String>("client_id")
        .with_column_of::<i64>("total")
}

fn clients_table(db: SqliteDB) -> Table<SqliteDB, EmptyEntity> {
    let db_clone = db.clone();
    Table::<SqliteDB, EmptyEntity>::new("client", db)
        .with_id_column("id")
        .with_column_of::<String>("name")
        .with_many("orders", "client_id", move |_| {
            orders_table(db_clone.clone())
        })
}

#[tokio::test]
async fn vista_get_ref_has_many_via_row() -> TestResult {
    let db = setup_clients_orders().await;
    let mut clients = db.vista_factory().from_table(clients_table(db.clone()))?;

    let (id, alice) = clients
        .with_id(CborValue::Text("alice".into()))?
        .get_some_value()
        .await?
        .expect("alice exists");
    assert_eq!(id, "alice");
    assert_eq!(
        alice.get("name"),
        Some(&CborValue::Text("Alice".to_string()))
    );

    let alice_orders = clients.get_ref("orders", &alice)?;
    let rows = alice_orders.list_values().await?;
    assert_eq!(rows.len(), 2, "alice has 2 orders");
    assert!(rows.contains_key("o1"));
    assert!(rows.contains_key("o2"));
    assert!(!rows.contains_key("o3"));
    Ok(())
}

#[tokio::test]
async fn vista_list_references_surfaces_cardinality() -> TestResult {
    let db = setup_clients_orders().await;
    let clients = db.vista_factory().from_table(clients_table(db))?;

    let refs = clients.list_references();
    assert_eq!(refs.len(), 1);
    assert_eq!(refs[0].0, "orders");
    assert_eq!(refs[0].1, vantage_vista::ReferenceKind::HasMany);
    Ok(())
}

#[tokio::test]
async fn vista_with_foreign_lazy_no_eager_invocation() -> TestResult {
    use std::sync::Arc;
    use std::sync::atomic::{AtomicBool, Ordering};

    let db = setup_clients_orders().await;
    let mut clients = db.vista_factory().from_table(clients_table(db.clone()))?;

    let fired = Arc::new(AtomicBool::new(false));
    let fired_clone = fired.clone();
    let db_for_closure = db.clone();
    clients.with_foreign(
        "external",
        vantage_vista::ReferenceKind::HasMany,
        move |_row| {
            fired_clone.store(true, Ordering::SeqCst);
            db_for_closure
                .vista_factory()
                .from_table(orders_table(db_for_closure.clone()))
        },
    );
    assert!(
        !fired.load(Ordering::SeqCst),
        "with_foreign must not invoke the closure at registration"
    );

    // Verify list_references picks it up with the declared cardinality.
    let refs = clients.list_references();
    assert!(
        refs.iter().any(
            |(name, kind)| name == "external" && *kind == vantage_vista::ReferenceKind::HasMany
        )
    );
    Ok(())
}

#[tokio::test]
async fn vista_add_order_ascending_descending_and_clear() -> TestResult {
    use vantage_vista::SortDirection;

    let db = setup().await;
    let table = product_table(db.clone());
    let mut vista = db.vista_factory().from_table(table)?;

    // capability is advertised
    assert!(vista.capabilities().can_order);

    // sort ascending by price → a (10), b (20), c (30)
    vista.add_order("price", SortDirection::Ascending)?;
    let rows = vista.list_values().await?;
    let ids: Vec<&String> = rows.keys().collect();
    assert_eq!(ids, ["a", "b", "c"]);

    // replace-semantics: switch to descending name → c (Gamma), b (Beta), a (Alpha)
    vista.add_order("name", SortDirection::Descending)?;
    let rows = vista.list_values().await?;
    let ids: Vec<&String> = rows.keys().collect();
    assert_eq!(ids, ["c", "b", "a"]);

    // clear → back to insertion order
    vista.clear_orders()?;
    let rows = vista.list_values().await?;
    let ids: Vec<&String> = rows.keys().collect();
    assert_eq!(ids, ["a", "b", "c"]);
    Ok(())
}

#[tokio::test]
async fn vista_add_order_rejects_unknown_column() -> TestResult {
    use vantage_vista::SortDirection;

    let db = setup().await;
    let table = product_table(db.clone());
    let mut vista = db.vista_factory().from_table(table)?;

    let result = vista.add_order("not_a_column", SortDirection::Ascending);
    assert!(result.is_err(), "unknown column must fail");
    Ok(())
}

#[tokio::test]
async fn vista_add_search_filters_results_with_replace_semantics() -> TestResult {
    let db = setup().await;
    let table = product_table(db.clone());
    let mut vista = db.vista_factory().from_table(table)?;

    assert!(vista.capabilities().can_search);

    // search "alpha" → only row a (name = "Alpha")
    vista.add_search("alpha")?;
    let rows = vista.list_values().await?;
    let ids: Vec<&String> = rows.keys().collect();
    assert_eq!(ids, ["a"], "search 'alpha' must match only row a");

    // replace-semantics: search "amma" → only row c (name = "Gamma")
    vista.add_search("amma")?;
    let rows = vista.list_values().await?;
    let ids: Vec<&String> = rows.keys().collect();
    assert_eq!(
        ids,
        ["c"],
        "search 'amma' must match only row c after replace"
    );

    // clear → all rows back
    vista.clear_search()?;
    let rows = vista.list_values().await?;
    assert_eq!(rows.len(), 3, "clear_search must restore all rows");
    Ok(())
}

#[tokio::test]
async fn vista_clear_search_without_prior_search_is_noop() -> TestResult {
    let db = setup().await;
    let table = product_table(db.clone());
    let mut vista = db.vista_factory().from_table(table)?;

    // No prior search — clear should silently succeed.
    vista.clear_search()?;
    let rows = vista.list_values().await?;
    assert_eq!(rows.len(), 3);
    Ok(())
}

#[tokio::test]
async fn vista_fetch_page_offset_pagination() -> TestResult {
    let db = setup().await;
    let table = product_table(db.clone());
    let mut vista = db.vista_factory().from_table(table)?;

    assert!(vista.capabilities().can_set_page_size);
    assert!(vista.capabilities().can_fetch_page);

    vista.set_page_size(2)?;
    vista.add_order("id", vantage_vista::SortDirection::Ascending)?;

    let page1 = vista.fetch_page(1).await?;
    let ids1: Vec<&String> = page1.iter().map(|(id, _)| id).collect();
    assert_eq!(ids1, ["a", "b"], "page 1 should be the first two rows");

    let page2 = vista.fetch_page(2).await?;
    let ids2: Vec<&String> = page2.iter().map(|(id, _)| id).collect();
    assert_eq!(ids2, ["c"], "page 2 should be the third row");

    let page3 = vista.fetch_page(3).await?;
    assert!(page3.is_empty(), "page 3 should be empty");
    Ok(())
}

#[tokio::test]
async fn vista_fetch_page_without_set_page_size_errors() -> TestResult {
    let db = setup().await;
    let table = product_table(db.clone());
    let vista = db.vista_factory().from_table(table)?;

    // No set_page_size — fetch_page must reject loudly.
    let result = vista.fetch_page(1).await;
    assert!(
        result.is_err(),
        "fetch_page without set_page_size must error"
    );
    Ok(())
}

#[tokio::test]
async fn vista_set_page_size_zero_errors() -> TestResult {
    let db = setup().await;
    let table = product_table(db.clone());
    let mut vista = db.vista_factory().from_table(table)?;

    assert!(vista.set_page_size(0).is_err(), "size 0 must reject");
    Ok(())
}

#[tokio::test]
async fn vista_fetch_page_honors_search_and_order() -> TestResult {
    use vantage_vista::SortDirection;

    let db = setup().await;
    let table = product_table(db.clone());
    let mut vista = db.vista_factory().from_table(table)?;

    vista.set_page_size(10)?;
    vista.add_order("name", SortDirection::Descending)?;
    vista.add_search("a")?; // matches Alpha, Beta, Gamma (all contain 'a' case-insensitively)

    let page = vista.fetch_page(1).await?;
    let names: Vec<String> = page
        .iter()
        .map(|(_, rec)| match rec.get("name") {
            Some(CborValue::Text(s)) => s.clone(),
            _ => String::new(),
        })
        .collect();
    assert_eq!(
        names,
        vec!["Gamma".to_string(), "Beta".to_string(), "Alpha".to_string()],
        "page must honour both search and DESC order on name"
    );
    Ok(())
}

#[tokio::test]
async fn vista_fetch_next_chains_pages_until_exhausted() -> TestResult {
    use vantage_vista::SortDirection;

    let db = setup().await;
    let table = product_table(db.clone());
    let mut vista = db.vista_factory().from_table(table)?;

    assert!(vista.capabilities().can_fetch_next);

    vista.set_page_size(2)?;
    vista.add_order("id", SortDirection::Ascending)?;

    // First call — pass None.
    let (rows1, tok1) = vista.fetch_next(None).await?;
    let ids1: Vec<&String> = rows1.iter().map(|(id, _)| id).collect();
    assert_eq!(ids1, ["a", "b"], "first page");
    assert!(tok1.is_some(), "more pages available — token must be Some");

    // Second call — last partial page, 1 row → exhaustion signaled.
    let (rows2, tok2) = vista.fetch_next(tok1).await?;
    let ids2: Vec<&String> = rows2.iter().map(|(id, _)| id).collect();
    assert_eq!(ids2, ["c"], "last page");
    assert!(tok2.is_none(), "partial last page must exhaust");
    Ok(())
}

#[tokio::test]
async fn vista_fetch_next_resets_when_passed_none() -> TestResult {
    use vantage_vista::SortDirection;

    let db = setup().await;
    let table = product_table(db.clone());
    let mut vista = db.vista_factory().from_table(table)?;

    vista.set_page_size(2)?;
    vista.add_order("id", SortDirection::Ascending)?;

    // Walk to exhaustion.
    let (_p1, tok1) = vista.fetch_next(None).await?;
    let (_p2, tok2) = vista.fetch_next(tok1).await?;
    assert!(tok2.is_none());

    // Passing None again restarts.
    let (p_restart, _) = vista.fetch_next(None).await?;
    let ids: Vec<&String> = p_restart.iter().map(|(id, _)| id).collect();
    assert_eq!(ids, ["a", "b"], "passing None restarts at page 1");
    Ok(())
}

#[tokio::test]
async fn vista_fetch_next_rejects_bad_token_type() -> TestResult {
    let db = setup().await;
    let table = product_table(db.clone());
    let mut vista = db.vista_factory().from_table(table)?;

    vista.set_page_size(2)?;

    // SQLite expects CborValue::Integer; pass a Text and expect rejection.
    let bad_token = Some(CborValue::Text("not a page number".into()));
    let result = vista.fetch_next(bad_token).await;
    assert!(result.is_err(), "non-Integer token must be rejected");
    Ok(())
}

#[tokio::test]
async fn vista_fetch_next_without_set_page_size_errors() -> TestResult {
    let db = setup().await;
    let table = product_table(db.clone());
    let vista = db.vista_factory().from_table(table)?;

    let result = vista.fetch_next(None).await;
    assert!(
        result.is_err(),
        "fetch_next without set_page_size must error"
    );
    Ok(())
}

#[tokio::test]
async fn vista_columns_advertise_orderable_flag() -> TestResult {
    let db = setup().await;
    let table = product_table(db.clone());
    let vista = db.vista_factory().from_table(table)?;

    for col_name in ["id", "name", "price", "is_deleted"] {
        let col = vista.get_column(col_name).expect("column exists");
        assert!(
            col.has_flag(vantage_vista::flags::ORDERABLE),
            "column '{}' must carry ORDERABLE flag for SQLite",
            col_name
        );
    }
    Ok(())
}