database-mcp 0.7.0

//! Functional tests for `SQLite`.
//!
//! Tests exercise the MCP tool layer (not handler methods directly),
//! ensuring the same code path as real MCP clients. Assumes a pre-seeded
//! `database.db` generated by Docker Compose (`docker compose up sqlite`).
//! The `DB_PATH` env var points to the database.
//!
//! ```bash
//! ./tests/run.sh --filter sqlite
//! ```

use database_mcp_config::{DatabaseBackend, DatabaseConfig};
use database_mcp_sqlite::SqliteHandler;
use database_mcp_sqlite::types::{
    DropTableRequest, ExplainQueryRequest, GetTableSchemaRequest, ListTablesRequest, QueryRequest, ReadQueryRequest,
};
use serde_json::Value;

fn base_db_config(read_only: bool) -> DatabaseConfig {
    DatabaseConfig {
        backend: DatabaseBackend::Sqlite,
        name: Some(std::env::var("DB_PATH").expect("DB_PATH must be set")),
        read_only,
        ..DatabaseConfig::default()
    }
}

fn handler(read_only: bool) -> SqliteHandler {
    let config = base_db_config(read_only);
    SqliteHandler::new(&config)
}

fn handler_with_page_size(page_size: u16) -> SqliteHandler {
    let config = DatabaseConfig {
        page_size,
        ..base_db_config(false)
    };
    SqliteHandler::new(&config)
}

#[tokio::test]
async fn test_write_query_insert_and_verify() {
    let handler = handler(false);

    let insert = QueryRequest {
        query: "INSERT INTO users (name, email) VALUES ('WriteTest', 'write@test.com')".into(),
    };
    handler.write_query(insert).await.unwrap();

    // Verify
    let select = ReadQueryRequest {
        query: "SELECT name FROM users WHERE email = 'write@test.com'".into(),
        cursor: None,
    };
    let rows = handler.read_query(select).await.unwrap();
    let arr = &rows.rows;
    assert_eq!(arr.len(), 1);
    assert_eq!(arr[0]["name"], "WriteTest");

    // Clean up
    let delete = QueryRequest {
        query: "DELETE FROM users WHERE email = 'write@test.com'".into(),
    };
    handler.write_query(delete).await.unwrap();
}

#[tokio::test]
async fn test_write_query_update() {
    let handler = handler(false);

    let insert = QueryRequest {
        query: "INSERT INTO users (name, email) VALUES ('Before', 'update@test.com')".into(),
    };
    handler.write_query(insert).await.unwrap();

    let update = QueryRequest {
        query: "UPDATE users SET name = 'After' WHERE email = 'update@test.com'".into(),
    };
    handler.write_query(update).await.unwrap();

    let select = ReadQueryRequest {
        query: "SELECT name FROM users WHERE email = 'update@test.com'".into(),
        cursor: None,
    };
    let rows = handler.read_query(select).await.unwrap();
    let arr = &rows.rows;
    assert_eq!(arr[0]["name"], "After");

    // Clean up
    let delete = QueryRequest {
        query: "DELETE FROM users WHERE email = 'update@test.com'".into(),
    };
    handler.write_query(delete).await.unwrap();
}

#[tokio::test]
async fn test_write_query_delete() {
    let handler = handler(false);

    let insert = QueryRequest {
        query: "INSERT INTO users (name, email) VALUES ('Deletable', 'delete@test.com')".into(),
    };
    handler.write_query(insert).await.unwrap();

    let delete = QueryRequest {
        query: "DELETE FROM users WHERE email = 'delete@test.com'".into(),
    };
    handler.write_query(delete).await.unwrap();

    let select = ReadQueryRequest {
        query: "SELECT * FROM users WHERE email = 'delete@test.com'".into(),
        cursor: None,
    };
    let rows = handler.read_query(select).await.unwrap();
    let arr = &rows.rows;
    assert!(arr.is_empty(), "Row should be deleted");
}

#[tokio::test]
async fn test_lists_tables() {
    let handler = handler(false);

    let response = handler.list_tables(ListTablesRequest::default()).await.unwrap();
    let tables = response.tables;

    for expected in ["users", "posts", "tags", "post_tags"] {
        assert!(
            tables.iter().any(|t| t == expected),
            "Missing '{expected}' in: {tables:?}"
        );
    }
}

#[tokio::test]
async fn test_gets_table_schema() {
    let handler = handler(false);
    let request = GetTableSchemaRequest { table: "users".into() };

    let schema = handler.get_table_schema(request).await.unwrap();

    assert_eq!(schema.table, "users");
    let columns = schema.columns.as_object().expect("columns object");
    for col in ["id", "name", "email", "created_at"] {
        assert!(columns.contains_key(col), "Missing '{col}' in: {columns:?}");
    }
}

#[tokio::test]
async fn test_gets_table_schema_with_relations() {
    let handler = handler(false);
    let request = GetTableSchemaRequest { table: "posts".into() };

    let schema = handler.get_table_schema(request).await.unwrap();

    let columns = schema.columns.as_object().expect("columns object");
    assert!(columns.contains_key("user_id"), "Missing 'user_id' column");
    let user_id = columns["user_id"].as_object().expect("user_id object");
    assert!(
        user_id.contains_key("foreignKey"),
        "Missing 'foreignKey' in user_id column"
    );
    assert!(
        !user_id["foreignKey"].is_null(),
        "foreignKey should not be null for user_id"
    );
}

#[tokio::test]
async fn test_executes_sql() {
    let handler = handler(false);
    let request = ReadQueryRequest {
        query: "SELECT * FROM users ORDER BY id".into(),
        cursor: None,
    };

    let response = handler.read_query(request).await.unwrap();
    assert_eq!(response.rows.len(), 3, "Expected 3 users, got {}", response.rows.len());
}

#[tokio::test]
async fn test_blocks_writes_in_read_only_mode() {
    let handler = handler(false);
    let request = ReadQueryRequest {
        query: "INSERT INTO users (name, email) VALUES ('Hacker', 'hack@evil.com')".into(),
        cursor: None,
    };

    let response = handler.read_query(request).await;

    assert!(response.is_err(), "Expected error for write in read-only mode");
}

#[tokio::test]
async fn test_query_timeout_fast_query_succeeds() {
    let config = DatabaseConfig {
        query_timeout: Some(5),
        ..base_db_config(false)
    };
    let handler = SqliteHandler::new(&config);
    let request = ReadQueryRequest {
        query: "SELECT 1 AS value".into(),
        cursor: None,
    };

    let response = handler.read_query(request).await;
    assert!(response.is_ok(), "Fast query should succeed within timeout");
}

#[tokio::test]
async fn test_query_timeout_disabled_with_none() {
    let config = DatabaseConfig {
        query_timeout: None,
        ..base_db_config(false)
    };
    let handler = SqliteHandler::new(&config);
    let request = ReadQueryRequest {
        query: "SELECT * FROM users ORDER BY id".into(),
        cursor: None,
    };

    let response = handler.read_query(request).await;
    assert!(response.is_ok(), "Query should succeed without timeout");
}

#[tokio::test]
async fn test_drop_table_success() {
    let handler = handler(false);

    // Create a temporary table
    let create = QueryRequest {
        query: "CREATE TABLE drop_test_simple (id INTEGER PRIMARY KEY)".into(),
    };
    handler.write_query(create).await.unwrap();

    // Drop it
    let drop_request = DropTableRequest {
        table: "drop_test_simple".into(),
    };
    let response = handler.drop_table(drop_request).await.unwrap();
    assert!(response.message.contains("dropped successfully"));

    // Verify it's gone
    let response = handler.list_tables(ListTablesRequest::default()).await.unwrap();
    let tables = response.tables;
    assert!(
        !tables.iter().any(|t| t == "drop_test_simple"),
        "Table should not exist after drop"
    );
}

#[tokio::test]
async fn test_drop_table_nonexistent() {
    let handler = handler(false);
    let drop_request = DropTableRequest {
        table: "nonexistent_table_xyz".into(),
    };

    let response = handler.drop_table(drop_request).await;
    assert!(response.is_err(), "Expected error for nonexistent table");
}

#[tokio::test]
async fn test_explain_query_select() {
    let handler = handler(false);
    let request = ExplainQueryRequest {
        query: "SELECT * FROM users".into(),
    };

    let response = handler.explain_query(request).await.unwrap();
    let plan = &response.rows;
    assert!(!plan.is_empty(), "Expected non-empty execution plan");
}

#[tokio::test]
async fn test_explain_query_with_join() {
    let handler = handler(false);
    let request = ExplainQueryRequest {
        query: "SELECT p.title, u.name FROM posts p JOIN users u ON p.user_id = u.id".into(),
    };

    let response = handler.explain_query(request).await.unwrap();
    let plan = &response.rows;
    assert!(!plan.is_empty(), "EXPLAIN QUERY PLAN should return plan for JOIN query");
}

#[tokio::test]
async fn test_explain_query_invalid_sql() {
    let handler = handler(false);
    let request = ExplainQueryRequest {
        query: "NOT VALID SQL AT ALL".into(),
    };

    let response = handler.explain_query(request).await;
    assert!(response.is_err(), "Expected error for invalid SQL");
}

#[tokio::test]
async fn test_get_table_schema_nonexistent_table() {
    let handler = handler(false);
    let request = GetTableSchemaRequest {
        table: "nonexistent_table_xyz".into(),
    };

    let response = handler.get_table_schema(request).await;
    assert!(response.is_err(), "Expected error for nonexistent table");
}

#[tokio::test]
async fn test_get_table_schema_invalid_table_name() {
    let handler = handler(false);
    let request = GetTableSchemaRequest { table: String::new() };

    let response = handler.get_table_schema(request).await;
    assert!(response.is_err(), "Expected error for empty table name");
}

#[tokio::test]
async fn test_drop_table_invalid_identifier() {
    let handler = handler(false);
    let drop_request = DropTableRequest { table: String::new() };

    let response = handler.drop_table(drop_request).await;
    assert!(response.is_err(), "Expected error for empty table name");
}

#[tokio::test]
async fn test_read_query_empty_query() {
    let handler = handler(false);
    let request = ReadQueryRequest {
        query: String::new(),
        cursor: None,
    };

    let response = handler.read_query(request).await;
    assert!(response.is_err(), "Expected error for empty query");
}

#[tokio::test]
async fn test_read_query_whitespace_only_query() {
    let handler = handler(false);
    let request = ReadQueryRequest {
        query: "   \t\n  ".into(),
        cursor: None,
    };

    let response = handler.read_query(request).await;
    assert!(response.is_err(), "Expected error for whitespace-only query");
}

#[tokio::test]
async fn test_read_query_multi_statement_blocked() {
    let handler = handler(false);
    let request = ReadQueryRequest {
        query: "SELECT 1; DROP TABLE users".into(),
        cursor: None,
    };

    let response = handler.read_query(request).await;
    assert!(response.is_err(), "Expected error for multi-statement query");
}

#[tokio::test]
async fn test_read_query_into_outfile_blocked() {
    let handler = handler(false);
    let request = ReadQueryRequest {
        query: "SELECT * FROM users INTO OUTFILE '/tmp/out'".into(),
        cursor: None,
    };

    let response = handler.read_query(request).await;
    assert!(response.is_err(), "Expected error for INTO OUTFILE");
}

#[tokio::test]
async fn test_read_query_with_join() {
    let handler = handler(false);
    let request = ReadQueryRequest {
        query: "SELECT p.title, u.name FROM posts p JOIN users u ON p.user_id = u.id ORDER BY p.id".into(),
        cursor: None,
    };

    let response = handler.read_query(request).await.unwrap();
    let rows = &response.rows;
    assert!(!rows.is_empty(), "JOIN query should return results");
    assert!(rows[0].get("title").is_some(), "Should have title column");
    assert!(rows[0].get("name").is_some(), "Should have name column");
}

#[tokio::test]
async fn test_write_query_create_table() {
    let handler = handler(false);

    let create = QueryRequest {
        query: "CREATE TABLE write_test_create (id INTEGER PRIMARY KEY, value TEXT)".into(),
    };
    handler.write_query(create).await.unwrap();

    // Verify it appears in list_tables
    let tables = handler.list_tables(ListTablesRequest::default()).await.unwrap();
    assert!(
        tables.tables.iter().any(|t| t == "write_test_create"),
        "Created table should appear in list"
    );

    // Clean up
    let drop = DropTableRequest {
        table: "write_test_create".into(),
    };
    handler.drop_table(drop).await.unwrap();
}

#[tokio::test]
async fn test_get_table_schema_junction_table() {
    let handler = handler(false);
    let request = GetTableSchemaRequest {
        table: "post_tags".into(),
    };

    let schema = handler.get_table_schema(request).await.unwrap();
    assert_eq!(schema.table, "post_tags");

    let columns = schema.columns.as_object().expect("columns object");
    assert!(columns.contains_key("post_id"), "Missing 'post_id'");
    assert!(columns.contains_key("tag_id"), "Missing 'tag_id'");

    // Both columns should have foreign keys
    let post_id = columns["post_id"].as_object().expect("post_id object");
    assert!(
        post_id.get("foreignKey").is_some_and(|fk| !fk.is_null()),
        "post_id should have a foreign key"
    );

    let tag_id = columns["tag_id"].as_object().expect("tag_id object");
    assert!(
        tag_id.get("foreignKey").is_some_and(|fk| !fk.is_null()),
        "tag_id should have a foreign key"
    );
}

#[tokio::test]
async fn test_read_query_empty_result_set() {
    let handler = handler(false);
    let request = ReadQueryRequest {
        query: "SELECT * FROM users WHERE email = 'nobody@nowhere.com'".into(),
        cursor: None,
    };

    let response = handler.read_query(request).await.unwrap();
    let rows = &response.rows;
    assert!(rows.is_empty(), "Expected empty result set");
}

#[tokio::test]
async fn test_read_query_aggregate() {
    let handler = handler(false);
    let request = ReadQueryRequest {
        query: "SELECT COUNT(*) AS total FROM users".into(),
        cursor: None,
    };

    let response = handler.read_query(request).await.unwrap();
    let rows = &response.rows;
    assert_eq!(rows.len(), 1);
    assert_eq!(rows[0]["total"], 3);
}

#[tokio::test]
async fn test_read_query_group_by() {
    let handler = handler(false);
    let request = ReadQueryRequest {
        query: "SELECT user_id, COUNT(*) AS post_count FROM posts GROUP BY user_id ORDER BY user_id".into(),
        cursor: None,
    };

    let response = handler.read_query(request).await.unwrap();
    let rows = &response.rows;
    assert!(rows.len() >= 2, "Expected at least 2 groups");
}

#[tokio::test]
async fn test_read_query_with_comments() {
    let handler = handler(false);
    let request = ReadQueryRequest {
        query: "/* fetch users */ SELECT * FROM users ORDER BY id".into(),
        cursor: None,
    };

    let response = handler.read_query(request).await.unwrap();
    let rows = &response.rows;
    assert_eq!(rows.len(), 3, "Comment-prefixed SELECT should work");
}

#[tokio::test]
async fn test_read_query_subquery() {
    let handler = handler(false);
    let request = ReadQueryRequest {
        query: "SELECT * FROM users WHERE id IN (SELECT user_id FROM posts WHERE published = 1)".into(),
        cursor: None,
    };

    let response = handler.read_query(request).await.unwrap();
    let rows = &response.rows;
    assert!(!rows.is_empty(), "Subquery should return results");
}

#[tokio::test]
async fn test_get_table_schema_no_foreign_keys() {
    let handler = handler(false);
    let request = GetTableSchemaRequest { table: "tags".into() };

    let schema = handler.get_table_schema(request).await.unwrap();
    assert_eq!(schema.table, "tags");

    let columns = schema.columns.as_object().expect("columns object");
    assert!(columns.contains_key("id"), "Missing 'id'");
    assert!(columns.contains_key("name"), "Missing 'name'");

    // id column should have no foreign key
    let id_col = columns["id"].as_object().expect("id object");
    let fk = id_col.get("foreignKey");
    assert!(fk.is_none_or(Value::is_null), "tags.id should not have a foreign key");
}

#[tokio::test]
async fn test_write_query_invalid_sql() {
    let handler = handler(false);
    let request = QueryRequest {
        query: "NOT VALID SQL AT ALL".into(),
    };

    let response = handler.write_query(request).await;
    assert!(response.is_err(), "Expected error for invalid SQL in write_query");
}

#[tokio::test]
async fn test_get_table_schema_column_details() {
    let handler = handler(false);
    let request = GetTableSchemaRequest { table: "users".into() };

    let schema = handler.get_table_schema(request).await.unwrap();
    let columns = schema.columns.as_object().expect("columns object");

    // Verify id column has key info (PRIMARY KEY)
    let id_col = columns["id"].as_object().expect("id object");
    let key = id_col.get("key").and_then(|v| v.as_str()).unwrap_or("");
    assert_eq!(key, "PRI", "id should be PRI key");

    // Verify email column type
    let email_col = columns["email"].as_object().expect("email object");
    let col_type = email_col.get("type").and_then(|v| v.as_str()).unwrap_or("");
    assert!(
        col_type.to_lowercase().contains("varchar") || col_type.to_lowercase().contains("text"),
        "email type should contain 'varchar' or 'text', got: {col_type}"
    );
}

#[tokio::test]
async fn test_read_query_with_limit() {
    let handler = handler(false);
    let request = ReadQueryRequest {
        query: "SELECT * FROM users ORDER BY id LIMIT 2".into(),
        cursor: None,
    };

    let response = handler.read_query(request).await.unwrap();
    let rows = &response.rows;
    assert_eq!(rows.len(), 2, "LIMIT 2 should return exactly 2 rows");
}

#[tokio::test]
async fn test_read_query_with_line_comment() {
    let handler = handler(false);
    let request = ReadQueryRequest {
        query: "-- get users\nSELECT * FROM users ORDER BY id".into(),
        cursor: None,
    };

    let response = handler.read_query(request).await.unwrap();
    let rows = &response.rows;
    assert_eq!(rows.len(), 3, "Line-comment prefixed SELECT should work");
}

#[tokio::test]
async fn test_read_query_null_values() {
    let handler = handler(false);
    let request = ReadQueryRequest {
        query: "SELECT title, body FROM posts WHERE title = 'My First Post'".into(),
        cursor: None,
    };

    let response = handler.read_query(request).await.unwrap();
    let rows = &response.rows;
    assert_eq!(rows.len(), 1);
    assert!(rows[0].get("body").is_some(), "body column should be present");
}

#[tokio::test]
async fn test_drop_table_blocked_in_read_only() {
    let handler = handler(true);
    let drop_request = DropTableRequest { table: "users".into() };

    let response = handler.drop_table(drop_request).await;
    assert!(response.is_err(), "drop_table should be blocked in read-only mode");
}

// === US2: Connection trait edge cases ===

#[tokio::test]
async fn test_list_tables_returns_empty_for_no_match() {
    let handler = handler(true);
    let request = ReadQueryRequest {
        query: "SELECT name FROM sqlite_master WHERE type='table' AND name='nonexistent_xyz'".into(),
        cursor: None,
    };
    let result = handler.read_query(request).await.unwrap();
    let rows = &result.rows;
    assert!(rows.is_empty(), "query for nonexistent table should return empty array");
}

// === US4: Special-character table name round-trip ===

#[tokio::test]
async fn test_create_drop_table_with_spaces() {
    let handler = handler(false);

    let create = QueryRequest {
        query: "CREATE TABLE \"table with spaces\" (id INTEGER PRIMARY KEY, name TEXT)".into(),
    };
    handler.write_query(create).await.unwrap();

    let schema = GetTableSchemaRequest {
        table: "table with spaces".into(),
    };
    let result = handler.get_table_schema(schema).await;
    assert!(
        result.is_ok(),
        "get_table_schema with spaced name should succeed: {result:?}"
    );

    let drop = DropTableRequest {
        table: "table with spaces".into(),
    };
    handler.drop_table(drop).await.unwrap();
}

// === Pagination ===

async fn collect_all_paged(handler: &SqliteHandler) -> Vec<String> {
    let mut all = Vec::new();
    let mut cursor: Option<database_mcp_server::pagination::Cursor> = None;
    loop {
        let request = ListTablesRequest { cursor };
        let response = handler.list_tables(request).await.expect("list page");
        all.extend(response.tables);
        match response.next_cursor {
            Some(c) => cursor = Some(c),
            None => break,
        }
    }
    all
}

#[tokio::test]
async fn test_list_tables_pagination_traverses_pages() {
    let handler_paged = handler_with_page_size(2);
    let handler_full = handler(true);

    let collected = collect_all_paged(&handler_paged).await;

    let single_page = handler_full
        .list_tables(ListTablesRequest::default())
        .await
        .expect("single page");

    assert_eq!(
        collected, single_page.tables,
        "paged traversal must yield identical results (and ordering) to a single full page"
    );
    let unique: std::collections::HashSet<&String> = collected.iter().collect();
    assert_eq!(unique.len(), collected.len(), "no duplicates across pages");
}

#[tokio::test]
async fn test_list_tables_pagination_small_table_set_no_next_cursor() {
    let handler = handler(true);
    let response = handler.list_tables(ListTablesRequest::default()).await.unwrap();
    assert!(
        response.next_cursor.is_none(),
        "seeded fixture below default page_size must not emit nextCursor"
    );
}

#[tokio::test]
async fn test_list_tables_pagination_boundary_page_size_equals_total() {
    let handler_full = handler(true);
    let total = handler_full
        .list_tables(ListTablesRequest::default())
        .await
        .expect("discover total")
        .tables
        .len();
    let page_size = u16::try_from(total).expect("seed total fits in u16");

    let handler_boundary = handler_with_page_size(page_size);
    let response = handler_boundary
        .list_tables(ListTablesRequest::default())
        .await
        .unwrap();
    assert_eq!(
        response.tables.len(),
        total,
        "page_size equal to total must return everything on one page"
    );
    assert!(
        response.next_cursor.is_none(),
        "page_size equal to total must NOT emit nextCursor"
    );
}

#[tokio::test]
async fn test_list_tables_pagination_off_the_end_cursor_returns_empty_page() {
    use database_mcp_server::pagination::Cursor;

    let handler = handler(true);
    let request = ListTablesRequest {
        cursor: Some(Cursor { offset: 10_000 }),
    };
    let response = handler.list_tables(request).await.unwrap();

    assert!(
        response.tables.is_empty(),
        "off-the-end cursor must return empty tables, got {:?}",
        response.tables
    );
    assert!(response.next_cursor.is_none(), "off-the-end must not emit nextCursor");
}

#[tokio::test]
async fn test_list_tables_respects_configured_page_size() {
    let handler = handler_with_page_size(2);
    let first = handler
        .list_tables(ListTablesRequest::default())
        .await
        .expect("first page");
    assert_eq!(first.tables.len(), 2, "configured page_size=2 must cap page 1");
    assert!(
        first.next_cursor.is_some(),
        "page 1 must emit nextCursor when total > page_size"
    );
}

#[tokio::test]
async fn test_list_tables_respects_configured_page_size_minimum() {
    let handler = handler_with_page_size(1);
    let first = handler
        .list_tables(ListTablesRequest::default())
        .await
        .expect("first page");
    assert_eq!(first.tables.len(), 1, "page_size=1 must return one table per page");
    assert!(first.next_cursor.is_some(), "page 1 must emit nextCursor");
}

#[tokio::test]
async fn test_list_tables_pagination_invalid_cursor_rejected_at_deserialize() {
    // Malformed cursors are rejected at serde deserialization time (before any
    // handler runs), which rmcp automatically maps to JSON-RPC `-32602`. Verify
    // the rejection here at the serde layer.
    use serde_json::json;

    let bad_cursors = [
        "!!!not-base64",
        "bm90LWpzb24",
        "eyJ4IjoxfQ",
        "eyJvIjowLCJ2Ijo5fQ",
        "eyJvIjotMSwidiI6MX0",
    ];

    for bad in bad_cursors {
        let err = serde_json::from_value::<ListTablesRequest>(json!({ "cursor": bad }))
            .expect_err(&format!("cursor {bad:?} should be rejected at deserialize time"));
        assert!(
            err.to_string().to_lowercase().contains("cursor")
                || err.to_string().to_lowercase().contains("base64")
                || err.to_string().to_lowercase().contains("malformed")
                || err.to_string().to_lowercase().contains("version"),
            "cursor {bad:?} error is not descriptive: {err}"
        );
    }
}

// === read_query pagination (spec 034) ===

async fn collect_all_paged_read_query(handler: &SqliteHandler, query: &str) -> Vec<Value> {
    let mut all = Vec::new();
    let mut cursor: Option<database_mcp_server::pagination::Cursor> = None;
    loop {
        let request = ReadQueryRequest {
            query: query.into(),
            cursor,
        };
        let response = handler.read_query(request).await.expect("read_query page");
        all.extend(response.rows);
        match response.next_cursor {
            Some(c) => cursor = Some(c),
            None => break,
        }
    }
    all
}

#[tokio::test]
async fn test_read_query_pagination_traverses_pages() {
    let handler_paged = handler_with_page_size(2);
    let handler_full = handler(true);
    let query = "SELECT id FROM users ORDER BY id";

    let collected = collect_all_paged_read_query(&handler_paged, query).await;

    let single = handler_full
        .read_query(ReadQueryRequest {
            query: query.into(),
            cursor: None,
        })
        .await
        .expect("single page");
    assert_eq!(
        collected, single.rows,
        "paged traversal must yield identical rows (and ordering) to a single full page"
    );
    let ids: Vec<i64> = collected
        .iter()
        .map(|row| row["id"].as_i64().expect("id is integer"))
        .collect();
    assert_eq!(ids, vec![1, 2, 3], "seeded users should be ids 1..=3");
}

#[tokio::test]
async fn test_read_query_pagination_small_result_no_next_cursor() {
    let handler = handler_with_page_size(2);
    let response = handler
        .read_query(ReadQueryRequest {
            query: "SELECT id FROM users WHERE id = 1".into(),
            cursor: None,
        })
        .await
        .unwrap();
    assert!(
        response.next_cursor.is_none(),
        "single-row result must not emit nextCursor"
    );
    assert_eq!(response.rows.len(), 1);
}

#[tokio::test]
async fn test_read_query_pagination_empty_result_no_next_cursor() {
    let handler = handler_with_page_size(2);
    let response = handler
        .read_query(ReadQueryRequest {
            query: "SELECT id FROM users WHERE id = -1".into(),
            cursor: None,
        })
        .await
        .unwrap();
    assert!(&response.rows.is_empty());
    assert!(response.next_cursor.is_none());
}

#[tokio::test]
async fn test_read_query_pagination_preserves_inner_limit() {
    // Caller's inner LIMIT/OFFSET stays inside the subquery wrap.
    let handler = handler_with_page_size(2);
    let response = handler
        .read_query(ReadQueryRequest {
            query: "SELECT id FROM users ORDER BY id LIMIT 1 OFFSET 1".into(),
            cursor: None,
        })
        .await
        .unwrap();
    let rows = &response.rows;
    assert_eq!(rows.len(), 1);
    assert_eq!(
        rows[0]["id"].as_i64(),
        Some(2),
        "inner OFFSET 1 LIMIT 1 must return id=2"
    );
    assert!(response.next_cursor.is_none(), "inner 1-row result fits on one page");
}

#[tokio::test]
async fn test_read_query_pagination_off_the_end_cursor_returns_empty() {
    use database_mcp_server::pagination::Cursor;
    let handler = handler_with_page_size(2);
    let response = handler
        .read_query(ReadQueryRequest {
            query: "SELECT id FROM users ORDER BY id".into(),
            cursor: Some(Cursor { offset: 10_000 }),
        })
        .await
        .unwrap();
    assert!(&response.rows.is_empty());
    assert!(response.next_cursor.is_none());
}

#[tokio::test]
async fn test_read_query_pagination_survives_trailing_line_comment() {
    // A trailing `-- comment` in the caller's SQL must not swallow the
    // subquery-wrap's closing paren + LIMIT/OFFSET. Regression guard for
    // the newline-before-`)` fix in database_mcp_sql::pagination::with_limit_offset.
    let handler = handler_with_page_size(2);
    let response = handler
        .read_query(ReadQueryRequest {
            query: "SELECT id FROM users ORDER BY id -- fetch users".into(),
            cursor: None,
        })
        .await
        .expect("trailing comment must not break the subquery wrap");
    let rows = &response.rows;
    assert_eq!(
        rows.len(),
        2,
        "page 1 should cap at page_size=2 even with trailing comment"
    );
    assert!(response.next_cursor.is_some());
}

#[tokio::test]
async fn test_read_query_pagination_strips_trailing_semicolon() {
    let handler = handler_with_page_size(2);
    let response = handler
        .read_query(ReadQueryRequest {
            query: "SELECT id FROM users WHERE id = 1;".into(),
            cursor: None,
        })
        .await
        .unwrap();
    let rows = &response.rows;
    assert_eq!(rows.len(), 1);
    assert_eq!(rows[0]["id"].as_i64(), Some(1));
}

#[tokio::test]
async fn test_read_query_pagination_invalid_cursor_rejected_at_deserialize() {
    use serde_json::json;

    let bad_cursors = ["!!!not-base64", "bm90LWpzb24", "eyJ4IjoxfQ", "eyJvZmZzZXQiOi0xfQ"];

    for bad in bad_cursors {
        let err = serde_json::from_value::<ReadQueryRequest>(json!({
            "query": "SELECT 1",
            "cursor": bad,
        }))
        .expect_err(&format!("cursor {bad:?} should be rejected at deserialize time"));
        let msg = err.to_string().to_lowercase();
        assert!(
            msg.contains("cursor") || msg.contains("base64") || msg.contains("malformed"),
            "cursor {bad:?} error is not descriptive: {err}"
        );
    }
}

#[tokio::test]
async fn test_read_query_cursor_does_not_bypass_read_only() {
    use database_mcp_server::pagination::Cursor;
    let handler = handler_with_page_size(2);
    let result = handler
        .read_query(ReadQueryRequest {
            query: "DELETE FROM users WHERE id = 1".into(),
            cursor: Some(Cursor { offset: 0 }),
        })
        .await;
    assert!(
        result.is_err(),
        "DELETE must fail read-only check even with a valid cursor"
    );
}

#[tokio::test]
async fn test_read_query_non_select_single_page_with_cursor_ignored() {
    // EXPLAIN is classified as NonSelect; cursor must be ignored (no error,
    // no nextCursor, response identical to the no-cursor call).
    use database_mcp_server::pagination::Cursor;
    let handler = handler_with_page_size(2);

    let without_cursor = handler
        .read_query(ReadQueryRequest {
            query: "EXPLAIN SELECT 1".into(),
            cursor: None,
        })
        .await
        .expect("EXPLAIN without cursor should succeed");

    let with_cursor = handler
        .read_query(ReadQueryRequest {
            query: "EXPLAIN SELECT 1".into(),
            cursor: Some(Cursor { offset: 100 }),
        })
        .await
        .expect("EXPLAIN with cursor should succeed — cursor must be ignored");

    assert!(without_cursor.next_cursor.is_none());
    assert!(with_cursor.next_cursor.is_none());
    assert_eq!(
        without_cursor.rows, with_cursor.rows,
        "cursor must be silently ignored for non-SELECT statements"
    );
}

#[tokio::test]
async fn test_read_query_temporal_columns_preserved() {
    // Feature 038: SQLite stores temporal values as TEXT verbatim. This test
    // is a regression guard ensuring the existing TEXT decode path keeps
    // returning the exact stored text (no formatting applied by the server).
    let handler = handler(false);

    let response = handler
        .read_query(ReadQueryRequest {
            query: "SELECT date, time, timestamp FROM temporal WHERE id = 1".into(),
            cursor: None,
        })
        .await
        .expect("temporal SELECT should succeed");

    let arr = &response.rows;
    assert_eq!(arr.len(), 1, "temporal seeds exactly one row");
    assert_eq!(arr[0]["date"], "2026-04-20");
    assert_eq!(arr[0]["time"], "14:30:00");
    // Note the space (not T) between date and time — SQLite stores the
    // literal text we inserted; this is the existing behavior we are
    // explicitly asserting does not regress.
    assert_eq!(arr[0]["timestamp"], "2026-04-20 14:30:00");
}