mcp-sql 0.2.0

use serde_json::Value;
#[allow(unused_imports)]
use sqlx::Row;

mod common;
#[allow(unused_imports)]
use common::*;

#[tokio::test]
async fn test_list_tables() {
    sqlx::any::install_default_drivers();
    let pool = create_test_pool().await;
    setup_test_schema(&pool).await;

    let tables = mcp_sql::db::dialect::list_tables(&pool, mcp_sql::db::DbBackend::Sqlite)
        .await
        .unwrap();

    let names: Vec<&str> = tables
        .iter()
        .filter_map(|t| t.get("table_name").and_then(|v| v.as_str()))
        .collect();
    assert!(names.contains(&"users"));
    assert!(names.contains(&"posts"));

    // Verify row counts are now accurate (not hardcoded 0)
    let users_count = tables
        .iter()
        .find(|t| t.get("table_name").and_then(|v| v.as_str()) == Some("users"))
        .and_then(|t| t.get("row_count").and_then(|v| v.as_i64()))
        .unwrap_or(0);
    assert_eq!(users_count, 2, "users table should have 2 rows");

    let posts_count = tables
        .iter()
        .find(|t| t.get("table_name").and_then(|v| v.as_str()) == Some("posts"))
        .and_then(|t| t.get("row_count").and_then(|v| v.as_i64()))
        .unwrap_or(0);
    assert_eq!(posts_count, 1, "posts table should have 1 row");
}

#[tokio::test]
async fn test_describe_table() {
    sqlx::any::install_default_drivers();
    let pool = create_test_pool().await;
    setup_test_schema(&pool).await;

    let columns = mcp_sql::db::dialect::describe_table(
        &pool,
        mcp_sql::db::DbBackend::Sqlite,
        "users",
    )
    .await
    .unwrap();

    assert!(!columns.is_empty());

    let col_names: Vec<&str> = columns
        .iter()
        .filter_map(|c| c.get("name").and_then(|v| v.as_str()))
        .collect();
    assert!(col_names.contains(&"id"));
    assert!(col_names.contains(&"name"));
    assert!(col_names.contains(&"email"));

    // Check that `id` is marked as primary key
    let id_col = columns
        .iter()
        .find(|c| c.get("name").and_then(|v| v.as_str()) == Some("id"))
        .unwrap();
    assert_eq!(id_col.get("primary_key").and_then(|v| v.as_str()), Some("YES"));
}

#[tokio::test]
async fn test_describe_nonexistent_table() {
    sqlx::any::install_default_drivers();
    let pool = create_test_pool().await;
    setup_test_schema(&pool).await;

    let result = mcp_sql::db::dialect::describe_table(
        &pool,
        mcp_sql::db::DbBackend::Sqlite,
        "nonexistent",
    )
    .await;

    assert!(result.is_err());
}

#[tokio::test]
async fn test_query_with_row_to_json() {
    sqlx::any::install_default_drivers();
    let pool = create_test_pool().await;
    setup_test_schema(&pool).await;

    let rows = sqlx::query("SELECT id, name, email, active FROM users ORDER BY id")
        .fetch_all(&pool)
        .await
        .unwrap();

    let results: Vec<Value> = rows.iter().map(mcp_sql::db::convert::row_to_json).collect();

    assert_eq!(results.len(), 2);

    let first = &results[0];
    assert_eq!(first.get("name").and_then(|v| v.as_str()), Some("Alice"));
    assert_eq!(
        first.get("email").and_then(|v| v.as_str()),
        Some("alice@example.com")
    );

    let second = &results[1];
    assert_eq!(second.get("name").and_then(|v| v.as_str()), Some("Bob"));
}

#[tokio::test]
async fn test_query_null_handling() {
    sqlx::any::install_default_drivers();
    let pool = create_test_pool().await;
    setup_test_schema(&pool).await;

    // Insert a row with NULL email
    sqlx::query("INSERT INTO users (name, email, active) VALUES ('Charlie', NULL, 1)")
        .execute(&pool)
        .await
        .unwrap();

    let rows = sqlx::query("SELECT name, email FROM users WHERE name = 'Charlie'")
        .fetch_all(&pool)
        .await
        .unwrap();

    let result = mcp_sql::db::convert::row_to_json(&rows[0]);
    assert_eq!(result.get("email"), Some(&Value::Null));
}

#[tokio::test]
async fn test_database_manager_single_db() {
    sqlx::any::install_default_drivers();
    let db = mcp_sql::db::DatabaseManager::new(&["sqlite::memory:".to_string()])
        .await
        .unwrap();

    // Should resolve without specifying database name
    let entry = db.resolve(None).unwrap();
    assert_eq!(entry.name, "memory");
    assert_eq!(entry.backend, mcp_sql::db::DbBackend::Sqlite);

    // Should also resolve with explicit name
    let entry = db.resolve(Some("memory")).unwrap();
    assert_eq!(entry.name, "memory");
}

#[tokio::test]
async fn test_database_manager_multiple_dbs() {
    sqlx::any::install_default_drivers();
    let db = mcp_sql::db::DatabaseManager::new(&[
        "sqlite::memory:".to_string(),
        "sqlite::memory:".to_string(),
    ])
    .await
    .unwrap();

    // Should fail without specifying database name
    assert!(db.resolve(None).is_err());
}

#[tokio::test]
async fn test_database_manager_not_found() {
    sqlx::any::install_default_drivers();
    let db = mcp_sql::db::DatabaseManager::new(&["sqlite::memory:".to_string()])
        .await
        .unwrap();

    assert!(db.resolve(Some("nonexistent")).is_err());
}

#[tokio::test]
async fn test_explain() {
    sqlx::any::install_default_drivers();
    let pool = create_test_pool().await;
    setup_test_schema(&pool).await;

    let prefix = mcp_sql::db::dialect::explain_prefix(mcp_sql::db::DbBackend::Sqlite);
    let explain_sql = format!("{prefix}SELECT * FROM users WHERE id = 1");
    let rows = sqlx::query(&explain_sql).fetch_all(&pool).await.unwrap();

    // EXPLAIN QUERY PLAN should return at least one row
    assert!(!rows.is_empty());
}

#[tokio::test]
async fn test_sample_data() {
    sqlx::any::install_default_drivers();
    let pool = create_test_pool().await;
    setup_test_schema(&pool).await;

    let rows = mcp_sql::db::dialect::sample_data(
        &pool,
        mcp_sql::db::DbBackend::Sqlite,
        "users",
        5,
    )
    .await
    .unwrap();

    // We inserted 2 users, so we should get 2 back
    assert_eq!(rows.len(), 2);

    // Each row should have expected columns
    let first = &rows[0];
    assert!(first.get("id").is_some());
    assert!(first.get("name").is_some());
    assert!(first.get("email").is_some());
}

#[tokio::test]
async fn test_sample_data_with_limit() {
    sqlx::any::install_default_drivers();
    let pool = create_test_pool().await;
    setup_test_schema(&pool).await;

    let rows = mcp_sql::db::dialect::sample_data(
        &pool,
        mcp_sql::db::DbBackend::Sqlite,
        "users",
        1,
    )
    .await
    .unwrap();

    assert_eq!(rows.len(), 1);
}

#[tokio::test]
async fn test_sample_data_invalid_table() {
    sqlx::any::install_default_drivers();
    let pool = create_test_pool().await;
    setup_test_schema(&pool).await;

    let result = mcp_sql::db::dialect::sample_data(
        &pool,
        mcp_sql::db::DbBackend::Sqlite,
        "nonexistent",
        5,
    )
    .await;

    assert!(result.is_err());
}

#[tokio::test]
async fn test_describe_table_foreign_keys() {
    sqlx::any::install_default_drivers();
    let pool = create_test_pool().await;
    setup_test_schema(&pool).await;

    // posts table has a FK from user_id -> users(id)
    let columns = mcp_sql::db::dialect::describe_table(
        &pool,
        mcp_sql::db::DbBackend::Sqlite,
        "posts",
    )
    .await
    .unwrap();

    // Find the user_id column
    let user_id_col = columns
        .iter()
        .find(|c| c.get("name").and_then(|v| v.as_str()) == Some("user_id"))
        .expect("user_id column should exist");

    // Should have a foreign_key field pointing to users.id
    let fk = user_id_col.get("foreign_key").and_then(|v| v.as_str());
    assert_eq!(fk, Some("users.id"));

    // The id column should have no FK
    let id_col = columns
        .iter()
        .find(|c| c.get("name").and_then(|v| v.as_str()) == Some("id"))
        .expect("id column should exist");

    assert!(
        id_col.get("foreign_key").unwrap().is_null(),
        "id column should have null foreign_key"
    );
}

#[tokio::test]
async fn test_describe_table_no_foreign_keys() {
    sqlx::any::install_default_drivers();
    let pool = create_test_pool().await;
    setup_test_schema(&pool).await;

    // users table has no FKs
    let columns = mcp_sql::db::dialect::describe_table(
        &pool,
        mcp_sql::db::DbBackend::Sqlite,
        "users",
    )
    .await
    .unwrap();

    // All columns should have null foreign_key
    for col in &columns {
        let fk = col.get("foreign_key");
        assert!(
            fk.is_none() || fk.unwrap().is_null(),
            "users table should have no foreign keys, but {:?} has {:?}",
            col.get("name"),
            fk
        );
    }
}

#[tokio::test]
async fn test_numeric_types() {
    sqlx::any::install_default_drivers();
    let pool = create_test_pool().await;

    sqlx::query(
        "CREATE TABLE numbers (
            int_val INTEGER,
            real_val REAL,
            text_val TEXT
        )",
    )
    .execute(&pool)
    .await
    .unwrap();

    sqlx::query("INSERT INTO numbers VALUES (42, 3.14, 'hello')")
        .execute(&pool)
        .await
        .unwrap();

    let rows = sqlx::query("SELECT * FROM numbers")
        .fetch_all(&pool)
        .await
        .unwrap();

    let result = mcp_sql::db::convert::row_to_json(&rows[0]);

    // Integer should come back as a number
    assert!(result.get("int_val").unwrap().is_number());
    assert_eq!(result.get("int_val").unwrap().as_i64(), Some(42));

    // Real should come back as a number
    assert!(result.get("real_val").unwrap().is_number());

    // Text should come back as a string
    assert_eq!(
        result.get("text_val").and_then(|v| v.as_str()),
        Some("hello")
    );
}

#[tokio::test]
async fn test_show_create_table() {
    sqlx::any::install_default_drivers();
    let pool = create_test_pool().await;
    setup_test_schema(&pool).await;

    let ddl = mcp_sql::db::dialect::show_create_table(
        &pool,
        mcp_sql::db::DbBackend::Sqlite,
        "users",
    )
    .await
    .unwrap();

    assert!(ddl.contains("CREATE TABLE"), "DDL should contain CREATE TABLE");
    assert!(ddl.contains("users"), "DDL should reference the table name");
    assert!(ddl.contains("name TEXT"), "DDL should include column definitions");
}

#[tokio::test]
async fn test_show_create_table_not_found() {
    sqlx::any::install_default_drivers();
    let pool = create_test_pool().await;
    setup_test_schema(&pool).await;

    let result = mcp_sql::db::dialect::show_create_table(
        &pool,
        mcp_sql::db::DbBackend::Sqlite,
        "nonexistent",
    )
    .await;

    assert!(result.is_err());
}

#[tokio::test]
async fn test_demo_database() {
    sqlx::any::install_default_drivers();
    let pool = mcp_sql::demo::create_demo_database().await.unwrap();

    // Verify tables exist
    let tables: Vec<sqlx::any::AnyRow> = sqlx::query(
        "SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%' ORDER BY name",
    )
    .fetch_all(&pool)
    .await
    .unwrap();
    let names: Vec<String> = tables.iter().map(|r| r.get::<String, _>("name")).collect();
    assert!(names.contains(&"users".to_string()));
    assert!(names.contains(&"posts".to_string()));
    assert!(names.contains(&"comments".to_string()));

    // Verify row counts
    let count: (i64,) = sqlx::query_as("SELECT COUNT(*) FROM users")
        .fetch_one(&pool)
        .await
        .unwrap();
    assert_eq!(count.0, 10);

    let count: (i64,) = sqlx::query_as("SELECT COUNT(*) FROM posts")
        .fetch_one(&pool)
        .await
        .unwrap();
    assert_eq!(count.0, 10);

    let count: (i64,) = sqlx::query_as("SELECT COUNT(*) FROM comments")
        .fetch_one(&pool)
        .await
        .unwrap();
    assert_eq!(count.0, 15);

    // Verify FK relationships
    let fk_rows: Vec<sqlx::any::AnyRow> = sqlx::query(
        "SELECT u.name, p.title FROM posts p JOIN users u ON p.user_id = u.id WHERE u.name = 'Alice Chen'",
    )
    .fetch_all(&pool)
    .await
    .unwrap();
    assert_eq!(fk_rows.len(), 3); // Alice has 3 posts
}

#[tokio::test]
async fn test_show_schema_mermaid() {
    sqlx::any::install_default_drivers();
    let pool = create_test_pool().await;
    setup_test_schema(&pool).await;

    let diagram = mcp_sql::schema::generate_mermaid_er(
        &pool,
        mcp_sql::db::DbBackend::Sqlite,
    )
    .await
    .unwrap();

    assert!(diagram.starts_with("erDiagram"), "should start with erDiagram");
    assert!(diagram.contains("users"), "should contain users table");
    assert!(diagram.contains("posts"), "should contain posts table");
    assert!(diagram.contains("PK"), "should mark primary keys");
    assert!(diagram.contains("FK"), "should mark foreign keys");
    // FK relationship line
    assert!(diagram.contains("||--o{"), "should contain relationship lines");
}

#[tokio::test]
async fn test_show_schema_empty_database() {
    sqlx::any::install_default_drivers();
    let pool = create_test_pool().await;
    // Don't create schema — empty database

    let diagram = mcp_sql::schema::generate_mermaid_er(
        &pool,
        mcp_sql::db::DbBackend::Sqlite,
    )
    .await
    .unwrap();

    assert!(diagram.contains("No tables found"));
}

#[tokio::test]
async fn test_list_indexes() {
    sqlx::any::install_default_drivers();
    let pool = create_test_pool().await;
    setup_test_schema(&pool).await;

    // Create an explicit index
    sqlx::query("CREATE INDEX idx_users_email ON users(email)")
        .execute(&pool)
        .await
        .unwrap();

    let indexes = mcp_sql::db::dialect::list_indexes(
        &pool,
        mcp_sql::db::DbBackend::Sqlite,
        "users",
    )
    .await
    .unwrap();

    assert!(!indexes.is_empty(), "should have at least one index");
    let idx = indexes.iter().find(|i| {
        i.get("index_name").and_then(|v| v.as_str()) == Some("idx_users_email")
    });
    assert!(idx.is_some(), "should find idx_users_email");
    let cols = idx
        .unwrap()
        .get("columns")
        .and_then(|v| v.as_array())
        .unwrap();
    assert!(cols.iter().any(|c| c.as_str() == Some("email")));
}

#[tokio::test]
async fn test_list_indexes_empty() {
    sqlx::any::install_default_drivers();
    let pool = create_test_pool().await;
    setup_test_schema(&pool).await;

    // users table has no explicit indexes (only the implicit rowid/PK)
    // SQLite's PRAGMA index_list only returns explicitly created indexes
    let indexes = mcp_sql::db::dialect::list_indexes(
        &pool,
        mcp_sql::db::DbBackend::Sqlite,
        "users",
    )
    .await
    .unwrap();

    // The autoincrement PK may or may not appear; just verify it doesn't error
    // If there are results, they should have the expected shape
    for idx in &indexes {
        assert!(idx.get("index_name").is_some());
        assert!(idx.get("columns").is_some());
        assert!(idx.get("unique").is_some());
    }
}

#[tokio::test]
async fn test_query_dry_run_valid() {
    sqlx::any::install_default_drivers();
    let pool = create_test_pool().await;
    setup_test_schema(&pool).await;

    let explain_sql = format!(
        "{}SELECT * FROM users WHERE name = 'Alice'",
        mcp_sql::db::dialect::explain_prefix(mcp_sql::db::DbBackend::Sqlite),
    );
    let rows = sqlx::query(&explain_sql).fetch_all(&pool).await;
    assert!(rows.is_ok(), "valid SQL should produce a query plan");
}

#[tokio::test]
async fn test_query_dry_run_invalid() {
    sqlx::any::install_default_drivers();
    let pool = create_test_pool().await;
    setup_test_schema(&pool).await;

    let explain_sql = format!(
        "{}SELECT * FROM nonexistent_table",
        mcp_sql::db::dialect::explain_prefix(mcp_sql::db::DbBackend::Sqlite),
    );
    let result = sqlx::query(&explain_sql).fetch_all(&pool).await;
    assert!(result.is_err(), "invalid SQL should produce an error");
}