Skip to content

armgit / dbx

Public Imported from GitHub
0

25 MB lightweight cross-platform database client for 100+ databases, including MySQL, PostgreSQL, SQLite, Redis, MongoDB, DuckDB, SQL Server, and Dameng. Built-in AI, MCP Server, CLI, desktop and Docker. | 轻量级跨平台数据库管理工具,支持 MySQL、PostgreSQL、SQLite、Redis、MongoDB、达梦等 100+ 数据库,提供桌面端、Docker、CLI、内置 AI 助手和 MCP。

120 KB
Raw Blame
use dbx_core::mcp_access::{McpAccessLevel, McpCallDecision, McpConfirmPolicy};
use serde::{Deserialize, Serialize};
use std::collections::HashMap;
use std::path::{Path, PathBuf};
use std::sync::{Arc, Mutex, OnceLock};
use std::time::{Duration, Instant};
use tauri::{AppHandle, Emitter};
use tokio::io::{AsyncReadExt, AsyncWriteExt};
use tokio::net::TcpListener;
use tokio::sync::oneshot;

use super::connection::AppState;

use dbx_core::mcp_policy::McpConnectionGroupPath;
use dbx_core::storage::{McpDatabaseScope, McpGlobalPolicy};

const BIND_ADDR: &str = "127.0.0.1:0";
const MCP_BRIDGE_PORT_FILE: &str = "mcp-bridge-port";
const MCP_EXECUTE_AND_SHOW_SQL_ONLY: &str =
    "UNSUPPORTED_OPERATION: MCP execute-and-show only supports SQL connections.";

#[derive(Deserialize)]
struct OpenTableRequest {
    connection_name: String,
    connection_id: Option<String>,
    database: Option<String>,
    schema: Option<String>,
    table: String,
}

#[derive(Deserialize)]
struct ExecuteQueryRequest {
    connection_name: String,
    connection_id: Option<String>,
    database: Option<String>,
    sql: String,
    schema: Option<String>,
    approval_token: Option<String>,
}

#[derive(Deserialize)]
struct SqlApprovalRequest {
    connection_id: String,
    database: String,
    sql: String,
}

#[derive(Deserialize)]
struct ConsumeSqlApprovalRequest {
    token: String,
    connection_id: String,
    database: String,
    sql: String,
}

#[derive(Clone, Serialize)]
struct SqlApprovalEvent {
    allow_timed: bool,
    id: String,
    connection_name: String,
    database: String,
    sql: String,
}

/// A high-risk non-SQL tool call awaiting confirmation (plugin tool declared
/// `full`, dangerous Redis command). Mirrors `dbx_mcp::approval::ToolApprovalRequest`.
#[derive(Deserialize)]
struct ToolApprovalRequest {
    kind: String,
    tool: String,
    #[serde(default)]
    plugin: Option<String>,
    #[serde(default)]
    connection_id: Option<String>,
    #[serde(default)]
    database: Option<String>,
    detail: String,
}

/// Payload of the `mcp-tool-approval` event. Tool confirmations are one-shot:
/// unlike SQL there is no exact statement to bind a timed grant to.
#[derive(Clone, Serialize)]
struct ToolApprovalEvent {
    id: String,
    kind: String,
    tool: String,
    plugin: Option<String>,
    connection_name: Option<String>,
    database: Option<String>,
    detail: String,
}

#[derive(Clone, Copy)]
enum SqlApprovalDecision {
    Once,
    Hour,
    Day,
    Deny,
}

struct SqlApprovalGrant {
    connection: crate::models::connection::ConnectionConfig,
    database: String,
    sql: String,
    expires_at: Instant,
}

struct TimedSqlApproval {
    connection: crate::models::connection::ConnectionConfig,
    expires_at: Instant,
}

impl TimedSqlApproval {
    fn valid_for(&self, connection: &crate::models::connection::ConnectionConfig, now: Instant) -> bool {
        self.expires_at > now && self.connection == *connection
    }
}

#[derive(Default)]
struct SqlApprovals {
    generation: u64,
    pending: HashMap<String, oneshot::Sender<SqlApprovalDecision>>,
    tokens: HashMap<String, SqlApprovalGrant>,
    timed: HashMap<(String, String), TimedSqlApproval>,
}

static SQL_APPROVALS: OnceLock<Mutex<SqlApprovals>> = OnceLock::new();

fn sql_approvals() -> &'static Mutex<SqlApprovals> {
    SQL_APPROVALS.get_or_init(|| Mutex::new(SqlApprovals::default()))
}

pub(crate) fn clear_mcp_sql_approvals() {
    if let Ok(mut approvals) = sql_approvals().lock() {
        approvals.generation = approvals.generation.wrapping_add(1);
        approvals.timed.clear();
        approvals.tokens.clear();
        approvals.pending.clear();
    }
}

#[tauri::command]
pub fn respond_mcp_sql_approval(window: tauri::Window, id: String, decision: String) -> Result<(), String> {
    if window.label() != "main" {
        return Err("Only the main DBX window can approve MCP SQL.".to_string());
    }
    let decision = match decision.as_str() {
        "once" => SqlApprovalDecision::Once,
        "hour" => SqlApprovalDecision::Hour,
        "day" => SqlApprovalDecision::Day,
        "deny" => SqlApprovalDecision::Deny,
        _ => return Err("Invalid MCP SQL approval decision.".to_string()),
    };
    let sender = sql_approvals()
        .lock()
        .map_err(|_| "MCP approval state unavailable")?
        .pending
        .remove(&id)
        .ok_or_else(|| "MCP approval request expired.".to_string())?;
    sender.send(decision).map_err(|_| "MCP approval request expired.".to_string())
}

fn take_sql_approval(
    token: Option<&str>,
    connection: &crate::models::connection::ConnectionConfig,
    database: &str,
    sql: &str,
) -> bool {
    let Some(token) = token else { return false };
    let Ok(mut state) = sql_approvals().lock() else { return false };
    let Some(grant) = state.tokens.remove(token) else { return false };
    grant.expires_at > Instant::now()
        && grant.connection == *connection
        && grant.database == database
        && grant.sql == sql
}

#[derive(Deserialize)]
struct ListTablesRequest {
    connection_name: String,
    connection_id: Option<String>,
    database: Option<String>,
    schema: Option<String>,
}

#[derive(Deserialize)]
struct DescribeTableRequest {
    connection_name: String,
    connection_id: Option<String>,
    database: Option<String>,
    schema: Option<String>,
    table: String,
}

#[derive(Deserialize)]
struct MongoFindDocumentsRequest {
    connection_name: String,
    connection_id: Option<String>,
    database: Option<String>,
    collection: String,
    skip: Option<u64>,
    limit: Option<i64>,
    filter: Option<String>,
    projection: Option<String>,
    sort: Option<String>,
    collation: Option<String>,
}

#[derive(Deserialize)]
struct MongoCountDocumentsRequest {
    connection_name: String,
    connection_id: Option<String>,
    database: Option<String>,
    collection: String,
    filter: Option<String>,
    mode: Option<String>,
}

#[derive(Deserialize)]
struct MongoServerVersionRequest {
    connection_name: String,
    connection_id: Option<String>,
    database: Option<String>,
}

#[derive(Deserialize)]
struct MongoCollectionStatsRequest {
    connection_name: String,
    connection_id: Option<String>,
    database: Option<String>,
    collection: String,
    scale: Option<serde_json::Number>,
}

#[derive(Deserialize)]
struct MongoAggregateDocumentsRequest {
    connection_name: String,
    connection_id: Option<String>,
    database: Option<String>,
    collection: String,
    pipeline_json: String,
    max_rows: Option<usize>,
    options_json: Option<String>,
}

#[derive(Deserialize)]
struct MongoDistinctRequest {
    connection_name: String,
    connection_id: Option<String>,
    database: Option<String>,
    collection: String,
    field: String,
    filter: Option<String>,
}

#[derive(Deserialize)]
struct MongoCreateIndexRequest {
    connection_name: String,
    connection_id: Option<String>,
    database: Option<String>,
    collection: String,
    keys_json: String,
    options_json: Option<String>,
}

#[derive(Deserialize)]
struct MongoDropIndexesRequest {
    connection_name: String,
    connection_id: Option<String>,
    database: Option<String>,
    collection: String,
    indexes_json: Option<String>,
    single: bool,
}

#[derive(Deserialize)]
struct MongoDropCollectionRequest {
    connection_name: String,
    connection_id: Option<String>,
    database: Option<String>,
    collection: String,
}

#[derive(Deserialize)]
struct MongoInsertDocumentsRequest {
    connection_name: String,
    connection_id: Option<String>,
    database: Option<String>,
    collection: String,
    docs_json: String,
}

#[derive(Deserialize)]
struct MongoUpdateDocumentsRequest {
    connection_name: String,
    connection_id: Option<String>,
    database: Option<String>,
    collection: String,
    filter_json: String,
    update_json: String,
    many: bool,
    options_json: Option<String>,
}

#[derive(Deserialize)]
struct MongoDeleteDocumentsRequest {
    connection_name: String,
    connection_id: Option<String>,
    database: Option<String>,
    collection: String,
    filter_json: String,
    many: bool,
}

#[derive(Deserialize)]
struct RedisCommandRequest {
    connection_name: String,
    connection_id: Option<String>,
    db: u32,
    command: String,
    #[serde(rename = "skip_safety_check")]
    _skip_safety_check: Option<bool>,
}

#[derive(Clone, Serialize)]
pub struct McpOpenTableEvent {
    pub connection_id: String,
    pub database: String,
    pub schema: Option<String>,
    pub table: String,
}

#[derive(Clone, Serialize)]
pub struct McpExecuteQueryEvent {
    pub connection_id: String,
    pub database: String,
    pub sql: String,
    pub results: Vec<dbx_core::db::QueryResult>,
}

pub fn start(app_handle: AppHandle, state: Arc<AppState>, data_dir: PathBuf) {
    tauri::async_runtime::spawn(async move {
        let listener = match TcpListener::bind(BIND_ADDR).await {
            Ok(l) => l,
            Err(e) => {
                log::warn!("MCP bridge failed to bind {BIND_ADDR}: {e}");
                return;
            }
        };
        log::info!("MCP bridge listening on {BIND_ADDR}");
        let actual_port = listener.local_addr().map(|a| a.port()).unwrap_or(0);
        log::info!("MCP bridge assigned port {actual_port}");
        // Publish into DBX's resolved data dir so DBX_DATA_DIR and portable mode share the same discovery file.
        if let Err(err) = write_port_file(&data_dir, actual_port) {
            log::warn!("MCP bridge failed to write port file in {}: {err}", data_dir.display());
        }
        loop {
            let (mut stream, _) = match listener.accept().await {
                Ok(s) => s,
                Err(_) => continue,
            };
            let app = app_handle.clone();
            let st = state.clone();
            tokio::spawn(async move {
                let request = match read_bridge_request(&mut stream).await {
                    Some(request) => request,
                    None => return,
                };
                let mut parts = request.splitn(2, "\r\n\r\n");
                let head = parts.next().unwrap_or("");
                let body = parts.next().unwrap_or("");
                let first_line = head.lines().next().unwrap_or("");
                if !bridge_request_is_local_client(first_line, head) {
                    let _ = stream
                        .write_all(b"HTTP/1.1 403 Forbidden\r\nContent-Length: 0\r\nConnection: close\r\n\r\n")
                        .await;
                    return;
                }

                if first_line.starts_with("POST /open-table") {
                    handle_open_table(&app, &st, body, &mut stream).await;
                } else if first_line.starts_with("POST /call-plugin-tool") {
                    handle_call_plugin_tool(&app, &st, body, &mut stream).await;
                } else if first_line.starts_with("POST /list-plugin-connections") {
                    handle_list_plugin_connections(&st, body, &mut stream).await;
                } else if first_line.starts_with("POST /data/list-tables") {
                    handle_list_tables_data(&st, body, &mut stream).await;
                } else if first_line.starts_with("POST /data/describe-table") {
                    handle_describe_table_data(&st, body, &mut stream).await;
                } else if first_line.starts_with("POST /data/mongo/list-collections") {
                    handle_mongo_list_collections_data(&st, body, &mut stream).await;
                } else if first_line.starts_with("POST /data/mongo/count-documents") {
                    handle_mongo_count_documents_data(&st, body, &mut stream).await;
                } else if first_line.starts_with("POST /data/mongo/find-documents") {
                    handle_mongo_find_documents_data(&st, body, &mut stream).await;
                } else if first_line.starts_with("POST /data/mongo/server-version") {
                    handle_mongo_server_version_data(&st, body, &mut stream).await;
                } else if first_line.starts_with("POST /data/mongo/collection-stats") {
                    handle_mongo_collection_stats_data(&st, body, &mut stream).await;
                } else if first_line.starts_with("POST /data/mongo/aggregate-documents") {
                    handle_mongo_aggregate_documents_data(&st, body, &mut stream).await;
                } else if first_line.starts_with("POST /data/mongo/distinct") {
                    handle_mongo_distinct_data(&st, body, &mut stream).await;
                } else if first_line.starts_with("POST /data/mongo/create-index") {
                    handle_mongo_create_index_data(&st, body, &mut stream).await;
                } else if first_line.starts_with("POST /data/mongo/drop-indexes") {
                    handle_mongo_drop_indexes_data(&st, body, &mut stream).await;
                } else if first_line.starts_with("POST /data/mongo/drop-collection") {
                    handle_mongo_drop_collection_data(&st, body, &mut stream).await;
                } else if first_line.starts_with("POST /data/mongo/insert-documents") {
                    handle_mongo_insert_documents_data(&st, body, &mut stream).await;
                } else if first_line.starts_with("POST /data/mongo/update-documents") {
                    handle_mongo_update_documents_data(&st, body, &mut stream).await;
                } else if first_line.starts_with("POST /data/mongo/delete-documents") {
                    handle_mongo_delete_documents_data(&st, body, &mut stream).await;
                } else if first_line.starts_with("POST /data/redis/execute-command") {
                    handle_redis_execute_command_data(&st, body, &mut stream).await;
                } else if first_line.starts_with("POST /data/execute-query") {
                    handle_execute_query_data(&st, body, &mut stream).await;
                } else if first_line.starts_with("POST /approve-high-risk-sql") {
                    handle_sql_approval(&app, &st, body, &mut stream).await;
                } else if first_line.starts_with("POST /approve-high-risk-tool") {
                    handle_tool_approval(&app, &st, body, &mut stream).await;
                } else if first_line.starts_with("POST /consume-high-risk-sql-approval") {
                    handle_consume_sql_approval(&st, body, &mut stream).await;
                } else if first_line.starts_with("POST /execute-query") {
                    handle_execute_query(&app, &st, body, &mut stream).await;
                } else if first_line.starts_with("POST /reload-connections") {
                    let _ = app.emit("mcp-reload-connections", ());
                    respond(&mut stream, "200 OK", "ok").await;
                } else {
                    let _ = stream.write_all(b"HTTP/1.1 404 Not Found\r\nContent-Length: 0\r\n\r\n").await;
                }
            });
        }
    });
}

fn write_port_file(data_dir: &Path, actual_port: u16) -> std::io::Result<PathBuf> {
    std::fs::create_dir_all(data_dir)?;
    let path = data_dir.join(MCP_BRIDGE_PORT_FILE);
    // 0600: the published port is the discovery half of a local control
    // plane; other local users must not be able to read (and talk to) it.
    let mut options = std::fs::OpenOptions::new();
    options.write(true).create(true).truncate(true);
    #[cfg(unix)]
    {
        use std::os::unix::fs::OpenOptionsExt;
        options.mode(0o600);
    }
    let mut file = options.open(&path)?;
    #[cfg(unix)]
    {
        use std::os::unix::fs::PermissionsExt;
        file.set_permissions(std::fs::Permissions::from_mode(0o600))?;
    }
    std::io::Write::write_all(&mut file, actual_port.to_string().as_bytes())?;
    Ok(path)
}

/// Upper bound for one bridge request (headers + body). Generous for large
/// SQL batches; the previous fixed 64 KiB single read silently truncated
/// bigger requests — and a truncation that landed on a JSON boundary could
/// execute cut-down SQL.
const MAX_BRIDGE_REQUEST_BYTES: usize = 10 * 1024 * 1024;
const BRIDGE_HEADER_TERMINATOR: &[u8] = b"\r\n\r\n";

/// Reads one HTTP/1.1 request off the bridge connection: the header block
/// first, then exactly `Content-Length` body bytes. TCP delivers large
/// bodies across multiple segments, so a single read truncates them; a
/// closed or short read still returns whatever arrived so routing can
/// answer with a precise 400 instead of dropping the connection.
async fn read_bridge_request(stream: &mut tokio::net::TcpStream) -> Option<String> {
    let mut buf: Vec<u8> = Vec::with_capacity(8192);
    let mut chunk = [0u8; 16384];
    let mut header_end: Option<usize> = None;
    let mut content_length: usize = 0;
    let mut scanned = 0usize;
    loop {
        if let Some(end) = header_end {
            if buf.len() >= end + content_length {
                return Some(String::from_utf8_lossy(&buf).into_owned());
            }
        } else if let Some(pos) =
            buf[scanned..].windows(BRIDGE_HEADER_TERMINATOR.len()).position(|window| window == BRIDGE_HEADER_TERMINATOR)
        {
            let end = scanned + pos + BRIDGE_HEADER_TERMINATOR.len();
            header_end = Some(end);
            content_length = parse_content_length(&String::from_utf8_lossy(&buf[..end]));
            // The same read may already contain the complete body (or no body).
            continue;
        } else {
            scanned = buf.len().saturating_sub(BRIDGE_HEADER_TERMINATOR.len() - 1);
        }
        let n = match stream.read(&mut chunk).await {
            Ok(n) if n > 0 => n,
            _ => {
                return if buf.is_empty() { None } else { Some(String::from_utf8_lossy(&buf).into_owned()) };
            }
        };
        buf.extend_from_slice(&chunk[..n]);
        if buf.len() > MAX_BRIDGE_REQUEST_BYTES {
            log::warn!("MCP bridge dropped an oversized request ({} bytes)", buf.len());
            return None;
        }
    }
}

fn parse_content_length(header_block: &str) -> usize {
    header_block
        .lines()
        .find_map(|line| {
            let (name, value) = line.split_once(':')?;
            name.trim().eq_ignore_ascii_case("content-length").then(|| value.trim().parse::<usize>().ok())?
        })
        .unwrap_or(0)
}

/// The bridge is a loopback-only control plane for local, non-browser
/// clients. Browsers attach an `Origin` header to every cross-origin
/// request, and their non-preflighted cross-site POSTs cannot carry
/// `Content-Type: application/json` — either one marks a fire-and-forget
/// CSRF attempt. A `Host` outside the loopback forms marks DNS rebinding.
/// All are refused before routing; ordinary local clients (the plugin
/// sidecars, dbx-mcp, dbx-cli) send Host + application/json and no Origin.
fn bridge_request_is_local_client(first_line: &str, head: &str) -> bool {
    let header_value = |name: &str| -> Option<String> {
        head.lines().skip(1).find_map(|line| {
            let (line_name, value) = line.split_once(':')?;
            line_name.trim().eq_ignore_ascii_case(name).then(|| value.trim().to_string())
        })
    };
    if first_line.starts_with("POST") {
        let is_json = header_value("content-type")
            .map(|value| value.split(';').next().unwrap_or("").trim().eq_ignore_ascii_case("application/json"))
            .unwrap_or(false);
        if !is_json {
            return false;
        }
    }
    if header_value("origin").is_some() {
        return false;
    }
    match header_value("host") {
        Some(host) => {
            host.starts_with("127.0.0.1:")
                || host.starts_with("localhost:")
                || host == "127.0.0.1"
                || host == "localhost"
        }
        None => false,
    }
}

fn find_config_by_name<'a>(
    configs: &'a [crate::models::connection::ConnectionConfig],
    name: &str,
) -> Option<&'a crate::models::connection::ConnectionConfig> {
    configs.iter().find(|c| c.name.eq_ignore_ascii_case(name))
}

#[cfg(test)]
mod tests {
    use super::{
        bridge_request_is_local_client, clear_mcp_sql_approvals, effective_database_execution_policy,
        ensure_connection_in_mcp_scope, ensure_mcp_connection_sql_write_allowed, ensure_mcp_execute_and_show_supported,
        ensure_mcp_mongo_pipeline_target_allowed_by_id, ensure_mcp_sql_database_switch_allowed,
        is_terminal_routed_exec, mongo_filter_is_effectively_unbounded, mongo_pipeline_has_write_stage,
        parse_content_length, plugin_connection_summaries, plugin_connection_summary, read_bridge_request,
        resolve_call_plugin_target, resolve_connection, resolve_mongo_database, resolve_mongo_target_values,
        sql_approvals, take_sql_approval, write_port_file, AppState, SqlApprovalGrant, TimedSqlApproval,
    };
    use dbx_core::models::connection::{ConnectionConfig, DatabaseType};
    use dbx_core::storage::{McpConnectionPolicy, McpDatabasePolicy, McpDatabaseScope, McpGlobalPolicy};
    use std::sync::Arc;
    use std::time::{Duration, Instant};

    #[tokio::test]
    async fn tool_approval_bridge_keeps_resource_overrides_under_global_read() {
        let root = tempfile::tempdir().unwrap();
        let storage = dbx_core::persistence::test_storage::open(&root.path().join("storage.db")).await.unwrap();
        let config = mysql_config(false);
        storage.save_connections(std::slice::from_ref(&config)).await.unwrap();
        let state = Arc::new(AppState::new_with_plugin_dir(storage, root.path().join("plugins")));
        let mut policy = McpGlobalPolicy { read_only: true, prompt_high_risk_sql: true, ..Default::default() };
        policy.connection_policies.push(serde_json::from_value(serde_json::json!({
            "connectionId": config.id, "readOnly": false, "executionModeConfigured": true, "executionModePolicyVersion": 1
        })).unwrap());
        state.storage.save_mcp_global_policy(&policy).await.unwrap();
        let mut request = super::ToolApprovalRequest {
            kind: "product_tool".into(),
            tool: "dbx_nacos_delete_config".into(),
            plugin: None,
            connection_id: Some(config.id.clone()),
            database: Some("dev".into()),
            detail: "test".into(),
        };
        // The server has checked the resource-level grant; the bridge must not
        // replace that resolved decision with the unrelated global default.
        assert_eq!(super::tool_approval_target(&state, &request).await.unwrap(), Some(config.name.clone()));
        request.connection_id = None;
        assert!(super::tool_approval_target(&state, &request).await.is_err());
        request.connection_id = Some(config.id.clone());
        let mut protected = config.clone();
        protected.read_only = true;
        state.storage.save_connections(std::slice::from_ref(&protected)).await.unwrap();
        assert!(super::tool_approval_target(&state, &request).await.unwrap_err().contains("CONNECTION_READ_ONLY"));
        protected.read_only = false;
        protected.is_production = true;
        state.storage.save_connections(std::slice::from_ref(&protected)).await.unwrap();
        assert!(super::tool_approval_target(&state, &request).await.unwrap_err().contains("PRODUCTION_WRITE_BLOCKED"));
        policy.prompt_high_risk_sql = false;
        state.storage.save_mcp_global_policy(&policy).await.unwrap();
        assert!(super::tool_approval_target(&state, &request).await.unwrap_err().contains("TOOL_BLOCKED"));
    }

    #[tokio::test]
    async fn call_safety_bridge_requires_confirmation_without_elevating_permissions() {
        use dbx_core::mcp_access::McpConfirmPolicy;
        let root = tempfile::tempdir().unwrap();
        let storage = dbx_core::persistence::test_storage::open(&root.path().join("storage.db")).await.unwrap();
        let config = mysql_config(false);
        storage.save_connections(std::slice::from_ref(&config)).await.unwrap();
        let state = Arc::new(AppState::new_with_plugin_dir(storage, root.path().join("plugins")));
        for full in [false, true] {
            for approved in [false, true] {
                let policy = McpGlobalPolicy {
                    allow_dangerous_sql: full,
                    call_confirmation: Some(McpConfirmPolicy::HighRisk),
                    ..Default::default()
                };
                state.storage.save_mcp_global_policy(&policy).await.unwrap();
                let result =
                    super::ensure_mcp_sql_allowed_with_approval(&state, &config, "app", "DROP TABLE items", approved)
                        .await;
                assert_eq!(result.is_ok(), full && approved, "full={full} approved={approved}: {result:?}");
                assert!(super::ensure_mcp_sql_allowed_with_approval(&state, &config, "app", "SELECT 1", false)
                    .await
                    .is_ok());
            }
        }
        // The legacy boolean keeps its former meaning, including no prompt at Full.
        for full in [false, true] {
            state
                .storage
                .save_mcp_global_policy(&McpGlobalPolicy {
                    allow_dangerous_sql: full,
                    prompt_high_risk_sql: true,
                    ..Default::default()
                })
                .await
                .unwrap();
            assert!(super::ensure_mcp_sql_allowed_with_approval(&state, &config, "app", "DROP TABLE items", true)
                .await
                .is_ok());
            assert_eq!(
                super::ensure_mcp_sql_allowed_with_approval(&state, &config, "app", "DROP TABLE items", false)
                    .await
                    .is_ok(),
                full
            );
        }
    }

    #[test]
    fn sql_approval_token_is_single_use_and_bound_to_target_and_text() {
        let token = uuid::Uuid::new_v4().to_string();
        let mut config = mysql_config(false);
        config.id = "mysql".to_string();
        let insert = |expires_at| {
            sql_approvals().lock().unwrap().tokens.insert(
                token.clone(),
                SqlApprovalGrant {
                    connection: config.clone(),
                    database: "app".to_string(),
                    sql: "DROP TABLE t".to_string(),
                    expires_at,
                },
            );
        };
        insert(Instant::now() + Duration::from_secs(60));
        assert!(!take_sql_approval(Some(&token), &config, "other", "DROP TABLE t"));
        assert!(!take_sql_approval(Some(&token), &config, "app", "DROP TABLE t"));
        insert(Instant::now() - Duration::from_secs(1));
        assert!(!take_sql_approval(Some(&token), &config, "app", "DROP TABLE t"));
        insert(Instant::now() + Duration::from_secs(60));
        let mut changed = config.clone();
        changed.host = "other-host".to_string();
        assert!(!take_sql_approval(Some(&token), &changed, "app", "DROP TABLE t"));
        insert(Instant::now() + Duration::from_secs(60));
        assert!(take_sql_approval(Some(&token), &config, "app", "DROP TABLE t"));
        assert!(!take_sql_approval(Some(&token), &config, "app", "DROP TABLE t"));
        insert(Instant::now() + Duration::from_secs(60));
        let generation = sql_approvals().lock().unwrap().generation;
        clear_mcp_sql_approvals();
        assert_ne!(sql_approvals().lock().unwrap().generation, generation);
        assert!(!take_sql_approval(Some(&token), &config, "app", "DROP TABLE t"));
    }

    #[test]
    fn timed_sql_approval_does_not_follow_a_connection_edit() {
        let config = mysql_config(false);
        let grant =
            TimedSqlApproval { connection: config.clone(), expires_at: Instant::now() + Duration::from_secs(60) };
        assert!(grant.valid_for(&config, Instant::now()));
        let mut changed = config.clone();
        changed.host = "other-host".to_string();
        assert!(!grant.valid_for(&changed, Instant::now()));
        assert!(!grant.valid_for(&config, grant.expires_at));
    }

    fn mysql_config(read_only: bool) -> ConnectionConfig {
        serde_json::from_value(serde_json::json!({
            "id": "readonly-connection",
            "name": "Read-only connection",
            "db_type": "mysql",
            "host": "localhost",
            "port": 3306,
            "username": "tester",
            "password": "",
            "database": "test",
            "read_only": read_only
        }))
        .unwrap()
    }

    #[test]
    fn writes_bridge_port_file_to_resolved_data_dir() {
        let root = std::env::temp_dir().join(format!(
            "dbx-mcp-bridge-port-test-{}-{}",
            std::process::id(),
            uuid::Uuid::new_v4()
        ));
        let default_data_dir = root.join("default-app-data");
        let resolved_data_dir = root.join("resolved-data");
        std::fs::create_dir_all(&default_data_dir).unwrap();

        let port_file = write_port_file(&resolved_data_dir, 49152).unwrap();

        assert_eq!(port_file, resolved_data_dir.join("mcp-bridge-port"));
        assert_eq!(std::fs::read_to_string(&port_file).unwrap(), "49152");
        #[cfg(unix)]
        {
            use std::os::unix::fs::PermissionsExt;
            let mode = std::fs::metadata(&port_file).unwrap().permissions().mode();
            assert_eq!(mode & 0o777, 0o600, "the port file must not be world-readable");
        }
        assert!(!default_data_dir.join("mcp-bridge-port").exists());

        let _ = std::fs::remove_dir_all(root);
    }

    #[test]
    fn call_plugin_target_accepts_the_bound_plugin_with_or_without_an_echo() {
        assert_eq!(resolve_call_plugin_target(Some("io.dbx.ssh"), None).unwrap(), "io.dbx.ssh");
        assert_eq!(resolve_call_plugin_target(Some("io.dbx.ssh"), Some("io.dbx.ssh")).unwrap(), "io.dbx.ssh");
    }

    #[test]
    fn call_plugin_target_rejects_override_and_unbound_connections() {
        assert!(resolve_call_plugin_target(Some("io.dbx.ssh"), Some("io.dbx.files")).is_err());
        assert!(resolve_call_plugin_target(None, Some("io.dbx.ssh")).is_err());
        assert!(resolve_call_plugin_target(Some(""), Some("io.dbx.ssh")).is_err());
        assert_eq!(resolve_call_plugin_target(None, None).unwrap_err(), "connection is not bound to a plugin");
    }

    #[test]
    fn content_length_parsing_tolerates_case_and_whitespace() {
        assert_eq!(parse_content_length("POST /x HTTP/1.1\r\nContent-Length: 12\r\n\r\n"), 12);
        assert_eq!(parse_content_length("POST /x HTTP/1.1\r\ncontent-length:  7\r\n\r\n"), 7);
        assert_eq!(parse_content_length("POST /x HTTP/1.1\r\nContent-Length: bogus\r\n\r\n"), 0);
        assert_eq!(parse_content_length("POST /x HTTP/1.1\r\n\r\n"), 0);
    }

    #[tokio::test]
    async fn bridge_reads_bodies_arriving_in_multiple_segments() {
        let listener = tokio::net::TcpListener::bind("127.0.0.1:0").await.unwrap();
        let addr = listener.local_addr().unwrap();
        let reader = tokio::spawn(async move {
            let (mut stream, _) = listener.accept().await.unwrap();
            read_bridge_request(&mut stream).await.unwrap()
        });
        let mut client = tokio::net::TcpStream::connect(addr).await.unwrap();
        use tokio::io::AsyncWriteExt;
        let body = "x".repeat(100_000);
        let request = format!(
            "POST /execute-query HTTP/1.1\r\nHost: 127.0.0.1\r\nContent-Type: application/json\r\nContent-Length: {}\r\n\r\n{}",
            body.len(),
            body
        );
        let bytes = request.into_bytes();
        // Split the request well past the header so the body cannot arrive
        // in the same TCP segment the header rides in.
        client.write_all(&bytes[..4096]).await.unwrap();
        client.flush().await.unwrap();
        tokio::time::sleep(std::time::Duration::from_millis(20)).await;
        client.write_all(&bytes[4096..]).await.unwrap();
        let parsed = reader.await.unwrap();
        assert!(parsed.ends_with(&body), "body must arrive complete, not truncated");
    }

    async fn wait_for_queued_request(stream: &tokio::net::TcpStream, expected_len: usize) {
        let mut queued = vec![0u8; expected_len];
        tokio::time::timeout(std::time::Duration::from_secs(1), async {
            loop {
                if stream.peek(&mut queued).await.unwrap() >= expected_len {
                    break;
                }
                tokio::task::yield_now().await;
            }
        })
        .await
        .expect("request bytes must be queued before the reader starts");
    }

    async fn read_complete_request_while_writer_stays_open(request: &[u8]) -> String {
        let listener = tokio::net::TcpListener::bind("127.0.0.1:0").await.unwrap();
        let addr = listener.local_addr().unwrap();
        let mut client = tokio::net::TcpStream::connect(addr).await.unwrap();
        let (mut server, _) = listener.accept().await.unwrap();
        use tokio::io::AsyncWriteExt;
        client.write_all(request).await.unwrap();
        wait_for_queued_request(&server, request.len()).await;

        tokio::time::timeout(std::time::Duration::from_secs(1), read_bridge_request(&mut server))
            .await
            .expect("complete request must not wait for another read")
            .unwrap()
    }

    #[tokio::test]
    async fn bridge_reads_complete_small_request_while_writer_stays_open() {
        let request = b"POST /x HTTP/1.1\r\nHost: 127.0.0.1\r\nContent-Length: 2\r\n\r\n{}";

        assert_eq!(read_complete_request_while_writer_stays_open(request).await.as_bytes(), request);
    }

    #[tokio::test]
    async fn bridge_reads_zero_length_body_while_writer_stays_open() {
        let request = b"POST /x HTTP/1.1\r\nHost: 127.0.0.1\r\nContent-Length: 0\r\n\r\n";

        assert_eq!(read_complete_request_while_writer_stays_open(request).await.as_bytes(), request);
    }

    #[tokio::test]
    async fn bridge_reads_complete_body_in_fragment_that_finishes_header_delimiter() {
        let first = b"POST /x HTTP/1.1\r\nHost: 127.0.0.1\r\nContent-Length: 2\r\n\r";
        let last = b"\n{}";
        let listener = tokio::net::TcpListener::bind("127.0.0.1:0").await.unwrap();
        let addr = listener.local_addr().unwrap();
        let mut client = tokio::net::TcpStream::connect(addr).await.unwrap();
        let (mut server, _) = listener.accept().await.unwrap();
        use tokio::io::AsyncWriteExt;
        client.write_all(first).await.unwrap();
        wait_for_queued_request(&server, first.len()).await;
        let mut reader = Box::pin(read_bridge_request(&mut server));
        // Consume the queued prefix before providing the delimiter's final bytes.
        std::future::poll_fn(|cx| {
            assert!(std::future::Future::poll(reader.as_mut(), cx).is_pending());
            std::task::Poll::Ready(())
        })
        .await;
        client.write_all(last).await.unwrap();

        let parsed = tokio::time::timeout(std::time::Duration::from_secs(1), reader)
            .await
            .expect("complete final fragment must not wait for another read")
            .unwrap();
        let expected = [first.as_slice(), last.as_slice()].concat();
        assert_eq!(parsed.as_bytes(), expected);
    }

    #[test]
    fn bridge_rejects_browser_shaped_requests_and_keeps_local_clients() {
        assert!(bridge_request_is_local_client(
            "POST /call-plugin-tool HTTP/1.1",
            "POST /call-plugin-tool HTTP/1.1\r\nHost: 127.0.0.1:7777\r\nContent-Type: application/json"
        ));
        assert!(bridge_request_is_local_client("GET / HTTP/1.1", "GET / HTTP/1.1\r\nHost: localhost"));
        assert!(bridge_request_is_local_client(
            "POST /execute-query HTTP/1.1",
            "POST /execute-query HTTP/1.1\r\nHost: 127.0.0.1:7777\r\nContent-Type: application/json; charset=utf-8"
        ));
        // Browsers always attach Origin to cross-origin requests.
        assert!(!bridge_request_is_local_client(
            "POST /call-plugin-tool HTTP/1.1",
            "POST /call-plugin-tool HTTP/1.1\r\nHost: 127.0.0.1:7777\r\nContent-Type: application/json\r\nOrigin: http://evil.example"
        ));
        // Non-preflightable content types are the classic fire-and-forget form POST.
        assert!(!bridge_request_is_local_client(
            "POST /call-plugin-tool HTTP/1.1",
            "POST /call-plugin-tool HTTP/1.1\r\nHost: 127.0.0.1:7777\r\nContent-Type: text/plain"
        ));
        assert!(!bridge_request_is_local_client(
            "POST /call-plugin-tool HTTP/1.1",
            "POST /call-plugin-tool HTTP/1.1\r\nHost: 127.0.0.1:7777"
        ));
        // DNS rebinding presents a foreign Host.
        assert!(!bridge_request_is_local_client(
            "POST /call-plugin-tool HTTP/1.1",
            "POST /call-plugin-tool HTTP/1.1\r\nHost: evil.example\r\nContent-Type: application/json"
        ));
    }

    #[test]
    fn only_ssh_exec_tools_may_open_the_workbench_terminal() {
        // The workbench open (and its tab churn) exists solely so a
        // terminal-routed exec lands in a real PTY; forwarded sftp/metrics
        // calls are hidden-channel by design and must never trigger it,
        // whatever runInTerminal says.
        assert!(is_terminal_routed_exec("ssh_exec"));
        assert!(is_terminal_routed_exec("ssh_exec_sudo"));
        assert!(!is_terminal_routed_exec("sftp_list_dir"));
        assert!(!is_terminal_routed_exec("ssh_metrics"));
        assert!(!is_terminal_routed_exec("mcp_exec"));
    }

    #[test]
    fn mongo_database_uses_configured_default_for_missing_or_blank_request() {
        let configured = Some("sample_db".to_string());

        assert_eq!(resolve_mongo_database(None, configured.clone()), "sample_db");
        assert_eq!(resolve_mongo_database(Some(String::new()), configured.clone()), "sample_db");
        assert_eq!(resolve_mongo_database(Some("  ".to_string()), configured), "sample_db");
    }

    #[test]
    fn mongo_database_preserves_explicit_target() {
        assert_eq!(resolve_mongo_database(Some("admin".to_string()), Some("sample_db".to_string())), "admin");
    }

    #[test]
    fn mongo_target_keeps_connection_id_separate_from_database() {
        assert_eq!(
            resolve_mongo_target_values(
                "connection-id".to_string(),
                Some("sample_db".to_string()),
                Some("default_db".to_string()),
            ),
            ("connection-id".to_string(), "sample_db".to_string())
        );
    }

    #[test]
    fn execute_and_show_accepts_sql_connections_only() {
        assert!(ensure_mcp_execute_and_show_supported(&DatabaseType::Mysql).is_ok());
        assert!(ensure_mcp_execute_and_show_supported(&DatabaseType::MongoDb).is_err());
        assert!(ensure_mcp_execute_and_show_supported(&DatabaseType::Redis).is_err());
        assert!(ensure_mcp_execute_and_show_supported(&DatabaseType::Elasticsearch).is_err());
        assert!(ensure_mcp_execute_and_show_supported(&DatabaseType::Easysearch).is_err());
    }

    #[test]
    fn mcp_sql_checks_supplied_connection_read_only_flag() {
        let mut config = mysql_config(true);

        assert!(ensure_mcp_connection_sql_write_allowed(&config, false).is_ok());
        let error = ensure_mcp_connection_sql_write_allowed(&config, true).unwrap_err();
        assert!(error.starts_with("CONNECTION_READ_ONLY:"));

        config.read_only = false;
        assert!(ensure_mcp_connection_sql_write_allowed(&config, true).is_ok());
    }

    #[tokio::test]
    async fn resolve_connection_refreshes_read_only_from_storage() {
        let root = std::env::temp_dir().join(format!(
            "dbx-mcp-bridge-connection-refresh-test-{}-{}",
            std::process::id(),
            uuid::Uuid::new_v4()
        ));
        std::fs::create_dir_all(&root).unwrap();
        let storage = dbx_core::persistence::test_storage::open(&root.join("storage.db")).await.unwrap();
        let mut config = mysql_config(false);
        storage.save_connections(&[config.clone()]).await.unwrap();
        let state = Arc::new(AppState::new_with_plugin_dir(storage, root.join("plugins")));

        let initial = resolve_connection(&state, Some(&config.id), &config.name).await.unwrap();
        assert!(!initial.read_only);

        config.read_only = true;
        state.storage.save_connections(&[config.clone()]).await.unwrap();
        let refreshed = resolve_connection(&state, Some(&config.id), &config.name).await.unwrap();
        assert!(refreshed.read_only);

        drop(state);
        let _ = std::fs::remove_dir_all(root);
    }

    #[test]
    fn mcp_sql_rejects_persistent_database_switches() {
        assert!(ensure_mcp_sql_database_switch_allowed(DatabaseType::Mysql, "SELECT 1").is_ok());
        assert_eq!(
            ensure_mcp_sql_database_switch_allowed(DatabaseType::Mysql, "USE production").unwrap_err(),
            "SQL_BLOCKED: MCP does not allow USE or persistent database switching."
        );
    }

    #[test]
    fn mcp_allowlist_distinguishes_all_subset_and_none() {
        let all = McpGlobalPolicy {
            read_only: false,
            allow_dangerous_sql: false,
            allowed_connection_ids: None,
            ..Default::default()
        };
        assert!(ensure_connection_in_mcp_scope(&all, "conn-1").is_ok());

        let subset = McpGlobalPolicy {
            read_only: false,
            allow_dangerous_sql: false,
            allowed_connection_ids: Some(vec!["conn-1".to_string()]),
            ..Default::default()
        };
        assert!(ensure_connection_in_mcp_scope(&subset, "conn-1").is_ok());
        assert!(ensure_connection_in_mcp_scope(&subset, "conn-2").unwrap_err().starts_with("CONNECTION_OUT_OF_SCOPE:"));

        let none = McpGlobalPolicy {
            read_only: false,
            allow_dangerous_sql: false,
            allowed_connection_ids: Some(Vec::new()),
            ..Default::default()
        };
        assert!(ensure_connection_in_mcp_scope(&none, "conn-1").is_err());
    }

    #[test]
    fn database_execution_policy_overrides_connection_and_global_defaults() {
        let policy = McpGlobalPolicy {
            read_only: false,
            allow_dangerous_sql: false,
            connection_policies: vec![McpConnectionPolicy {
                connection_id: "conn-1".to_string(),
                read_only: false,
                allow_dangerous_sql: false,
                execution_mode_configured: true,
                execution_mode_policy_version: Some(dbx_core::mcp_policy::MCP_EXECUTION_POLICY_VERSION),
                database_scope: McpDatabaseScope::Selected,
                allowed_databases: vec!["aa".to_string(), "aaa".to_string()],
                database_policies: vec![
                    McpDatabasePolicy { database_name: "aa".to_string(), read_only: false, allow_dangerous_sql: true },
                    McpDatabasePolicy { database_name: "aaa".to_string(), read_only: true, allow_dangerous_sql: false },
                ],
                allow_salesforce_dml: false,
            }],
            ..Default::default()
        };

        assert_eq!(effective_database_execution_policy(&policy, "conn-1", "aa"), (false, true));
        assert_eq!(effective_database_execution_policy(&policy, "conn-1", "aaa"), (true, false));
        assert_eq!(effective_database_execution_policy(&policy, "conn-1", "unconfigured"), (false, false));
    }

    #[tokio::test]
    async fn database_execution_policy_blocks_cross_database_mongo_aggregate_output() {
        let root = std::env::temp_dir().join(format!(
            "dbx-mcp-bridge-database-policy-test-{}-{}",
            std::process::id(),
            uuid::Uuid::new_v4()
        ));
        std::fs::create_dir_all(&root).unwrap();
        let storage = dbx_core::persistence::test_storage::open(&root.join("storage.db")).await.unwrap();
        let mut config = mysql_config(false);
        config.id = "conn-1".to_string();
        storage.save_connections(&[config]).await.unwrap();
        storage
            .save_mcp_global_policy(&McpGlobalPolicy {
                connection_policies: vec![McpConnectionPolicy {
                    connection_id: "conn-1".to_string(),
                    read_only: false,
                    allow_dangerous_sql: true,
                    execution_mode_configured: true,
                    execution_mode_policy_version: Some(dbx_core::mcp_policy::MCP_EXECUTION_POLICY_VERSION),
                    database_scope: McpDatabaseScope::Selected,
                    allowed_databases: vec!["operations".to_string(), "reporting".to_string()],
                    database_policies: vec![McpDatabasePolicy {
                        database_name: "operations".to_string(),
                        read_only: false,
                        allow_dangerous_sql: true,
                    }],
                    allow_salesforce_dml: false,
                }],
                ..Default::default()
            })
            .await
            .unwrap();
        let state = Arc::new(AppState::new_with_plugin_dir(storage, root.join("plugins")));

        let error = ensure_mcp_mongo_pipeline_target_allowed_by_id(
            &state,
            "conn-1",
            "operations",
            r#"[{"$merge":{"into":{"db":"reporting","coll":"archive"}}}]"#,
        )
        .await
        .unwrap_err();
        assert!(error.starts_with("DATABASE_EXECUTION_POLICY_OUT_OF_SCOPE:"), "{error}");

        drop(state);
        let _ = std::fs::remove_dir_all(root);
    }

    #[test]
    fn mongo_aggregate_write_stages_are_detected_structurally() {
        assert!(mongo_pipeline_has_write_stage(r#"[{"$match":{}},{"$out":"archive"}]"#));
        assert!(mongo_pipeline_has_write_stage(r#"[{"$merge":{"into":"archive"}}]"#));
        assert!(!mongo_pipeline_has_write_stage(r#"[{"$project":{"label":"$out"}}]"#));
    }

    #[test]
    fn mongo_filters_distinguish_guarded_and_unbounded_writes() {
        for filter in [
            "{}",
            r#"{"$comment":"all rows"}"#,
            r#"{"$expr":true}"#,
            r#"{"$or":[{}, {"id":1}]}"#,
            r#"{"$nor":[{"$expr":false}]}"#,
            r#"{"$or":[{"id":{"$exists":true}},{"id":{"$exists":false}}]}"#,
            r#"{"$or":[{"id":{"$exists":true}},{"id":{"$not":{"$exists":true}}}]}"#,
            r#"{"$or":[{"id":{"$eq":1}},{"id":{"$ne":1}}]}"#,
            r#"{"$or":[{"$and":[{"id":{"$eq":1}}]},{"id":{"$ne":1}}]}"#,
            r#"{"$or":[{"$and":[{"id":{"$eq":1}},{}]},{"id":{"$ne":1}}]}"#,
            r#"{"$or":[{"$and":[{"id":{"$eq":1}},{"x":{"$exists":true}}]},{"id":{"$ne":1}},{"x":{"$exists":false}}]}"#,
            r#"{"$or":[{"id":1},{"id":{"$ne":1}}]}"#,
            r#"{"$or":[{"id":{"$gt":1}},{"id":{"$lte":1}}]}"#,
            r#"{"$or":[{"id":{"$gte":1}},{"id":{"$lt":1}}]}"#,
            r#"{"$or":[{"id":{"$in":[1,2]}},{"id":{"$nin":[2,1]}}]}"#,
            r#"{"_id":{"$exists":true}}"#,
            r#"{"id":{"$nin":[]}}"#,
            r#"{"_id":{"$oid":"not-an-object-id"}}"#,
            r#"{"sequence":{"$numberLong":"9223372036854775808"}}"#,
            r#"{"created_at":{"$date":"2026-02-30T00:00:00Z"}}"#,
            r#"{"name":{"$regex":".*"}}"#,
            r#"{"$or":[{"_id":{"$oid":"507f1f77bcf86cd799439011"}},{"_id":{"$ne":{"$oid":"507f1f77bcf86cd799439011"}}}]}"#,
            r#"{"$and":[{"tenant_id":1},{"$nor":[{"archived":true}]}]}"#,
            r#"{"$or":[]}"#,
            r#"{"$opaque":[{"id":1}]}"#,
        ] {
            assert!(mongo_filter_is_effectively_unbounded(filter), "{filter}");
        }
        for filter in [
            r#"{"id":1}"#,
            r#"{"created_at":{"$gte":"2026-01-01"}}"#,
            r#"{"$and":[{}, {"tenant_id":1}]}"#,
            r#"{"$or":[{"tenant_id":1},{"tenant_id":2}]}"#,
            r#"{"id":{"$ne":1}}"#,
            r#"{"id":{"$in":[1,2]}}"#,
            r#"{"id":{"$exists":true}}"#,
            r#"{"_id":{"$oid":"507f1f77bcf86cd799439011"}}"#,
            r#"{"sequence":{"$numberLong":"9223372036854775807"}}"#,
            r#"{"created_at":{"$date":"2026-01-01T00:00:00.000Z"}}"#,
            r#"{"tenant_id":1,"id":{"$nin":[]}}"#,
        ] {
            assert!(!mongo_filter_is_effectively_unbounded(filter), "{filter}");
        }
    }

    fn plugin_connection_config(id: &str, name: &str, external_config: serde_json::Value) -> ConnectionConfig {
        serde_json::from_value(serde_json::json!({
            "id": id,
            "name": name,
            "db_type": "plugin",
            "plugin_id": "io.dbx.ssh",
            "plugin_connection_type": "ssh",
            "host": "server.example.com",
            "port": 2222,
            "username": "deploy",
            "password": "",
            "connection_secrets": { "password": "hunter2", "totp_secret": "otpauth://secret" },
            "external_config": external_config
        }))
        .unwrap()
    }

    #[test]
    fn plugin_connection_summary_is_metadata_only() {
        let config = plugin_connection_config(
            "conn-ssh-1",
            "prod-bastion",
            serde_json::json!({ "authentication": "private-key", "read_only": true, "agent_socket": "/tmp/agent" }),
        );
        let value = serde_json::to_value(plugin_connection_summary(&config)).unwrap();
        let object = value.as_object().unwrap();
        let keys: std::collections::HashSet<_> = object.keys().cloned().collect();
        assert_eq!(
            keys,
            ["id", "name", "host", "port", "username", "authentication", "readOnly"]
                .into_iter()
                .map(String::from)
                .collect()
        );
        assert_eq!(value["id"], "conn-ssh-1");
        assert_eq!(value["host"], "server.example.com");
        assert_eq!(value["port"], 2222);
        assert_eq!(value["username"], "deploy");
        assert_eq!(value["authentication"], "private-key");
        assert_eq!(value["readOnly"], true);
        let rendered = value.to_string();
        for secret in ["hunter2", "otpauth", "password", "connection_secrets", "external_config", "agent_socket"] {
            assert!(!rendered.contains(secret), "summary must not leak {secret}");
        }
    }

    #[test]
    fn plugin_connection_summary_falls_back_to_safe_defaults() {
        let mut config = plugin_connection_config("conn-ssh-2", "fallback", serde_json::json!({}));
        config.read_only = true;
        let value = serde_json::to_value(plugin_connection_summary(&config)).unwrap();
        assert_eq!(value["authentication"], "password");
        assert_eq!(value["readOnly"], true);
    }

    #[test]
    fn plugin_connection_summaries_filter_by_plugin_and_sort_by_name() {
        let ssh_b = plugin_connection_config("b", "Beta", serde_json::json!({}));
        let ssh_a = plugin_connection_config("a", "alpha", serde_json::json!({}));
        let mut ldap = plugin_connection_config("l", "zeta", serde_json::json!({}));
        ldap.plugin_id = Some("io.dbx.ldap".to_string());
        let mysql = mysql_config(false);

        let summaries = plugin_connection_summaries(&[ssh_b, ldap, mysql, ssh_a], "io.dbx.ssh");
        let names: Vec<_> = summaries.iter().map(|s| s.name.as_str()).collect();
        assert_eq!(names, vec!["alpha", "Beta"]);

        assert!(plugin_connection_summaries(
            &[plugin_connection_config("a", "alpha", serde_json::json!({}))],
            "io.dbx.files"
        )
        .is_empty());
    }
}

async fn respond(stream: &mut tokio::net::TcpStream, status: &str, body: &str) {
    let resp = format!("HTTP/1.1 {status}\r\nContent-Length: {}\r\n\r\n{body}", body.len());
    let _ = stream.write_all(resp.as_bytes()).await;
}

async fn respond_json<T: Serialize>(stream: &mut tokio::net::TcpStream, data: &T) {
    let body = serde_json::to_string(data).unwrap_or_else(|_| "null".to_string());
    let resp =
        format!("HTTP/1.1 200 OK\r\nContent-Type: application/json\r\nContent-Length: {}\r\n\r\n{body}", body.len());
    let _ = stream.write_all(resp.as_bytes()).await;
}

async fn respond_error(stream: &mut tokio::net::TcpStream, status: &str, message: &str) {
    let body = serde_json::json!({ "error": message }).to_string();
    let resp =
        format!("HTTP/1.1 {status}\r\nContent-Type: application/json\r\nContent-Length: {}\r\n\r\n{body}", body.len());
    let _ = stream.write_all(resp.as_bytes()).await;
}

async fn resolve_connection(
    state: &Arc<AppState>,
    connection_id: Option<&str>,
    connection_name: &str,
) -> Result<crate::models::connection::ConnectionConfig, String> {
    let (policy, group_paths) = load_mcp_policy_context(state).await?;
    let configs = state.storage.load_connections().await.map_err(|e| mcp_policy_unavailable(e.to_string()))?;
    let config = if let Some(id) = connection_id.filter(|s| !s.is_empty()) {
        configs.iter().find(|c| c.id == id).ok_or_else(|| format!("Connection with id '{}' not found", id))?
    } else {
        find_config_by_name(&configs, connection_name).ok_or_else(|| "Connection not found".to_string())?
    };
    ensure_connection_in_mcp_scope_with_groups(&policy, group_paths.get(&config.id), &config.id)?;
    let mut state_configs = state.configs.write().await;
    if !state_configs.contains_key(&config.id) {
        state_configs.insert(config.id.clone(), config.clone());
    }
    drop(state_configs);
    Ok(config.clone())
}

async fn load_mcp_policy(state: &Arc<AppState>) -> Result<McpGlobalPolicy, String> {
    state
        .storage
        .load_enforced_mcp_policy()
        .await
        .map(|state| state.policy())
        .map_err(|error| mcp_policy_unavailable(error.to_string()))
}

async fn load_mcp_policy_context(
    state: &Arc<AppState>,
) -> Result<(McpGlobalPolicy, HashMap<String, McpConnectionGroupPath>), String> {
    let policy = load_mcp_policy(state).await?;
    let group_paths = match state.storage.load_sidebar_layout().await {
        Ok(Some(layout)) => dbx_core::mcp_policy::connection_group_paths(&layout),
        Ok(None) => Ok(HashMap::new()),
        Err(error) => Err(error),
    };
    match group_paths {
        Ok(paths) => Ok((policy, paths)),
        Err(error) if dbx_core::mcp_policy::policy_uses_connection_groups(&policy) => {
            Err(mcp_policy_unavailable(error))
        }
        Err(_) => Ok((policy, HashMap::new())),
    }
}

fn mcp_policy_unavailable(error: String) -> String {
    if error.starts_with("MCP_POLICY_UNAVAILABLE:") {
        error
    } else {
        format!("MCP_POLICY_UNAVAILABLE: {error}")
    }
}

#[cfg(test)]
fn ensure_connection_in_mcp_scope(policy: &McpGlobalPolicy, connection_id: &str) -> Result<(), String> {
    ensure_connection_in_mcp_scope_with_groups(policy, None, connection_id)
}

fn ensure_connection_in_mcp_scope_with_groups(
    policy: &McpGlobalPolicy,
    group_path: Option<&McpConnectionGroupPath>,
    connection_id: &str,
) -> Result<(), String> {
    if !dbx_core::mcp_policy::policy_allows_connection(policy, group_path, connection_id) {
        return Err(format!(
            "CONNECTION_OUT_OF_SCOPE: connection '{connection_id}' is not allowed by DBX MCP settings"
        ));
    }
    Ok(())
}

fn ensure_database_in_mcp_scope(policy: &McpGlobalPolicy, connection_id: &str, database: &str) -> Result<(), String> {
    let Some(rule) = policy.connection_policies.iter().find(|rule| rule.connection_id == connection_id) else {
        return Ok(());
    };
    let allowed = match rule.database_scope {
        McpDatabaseScope::All => true,
        McpDatabaseScope::Selected => rule.allowed_databases.iter().any(|allowed| allowed == database),
        McpDatabaseScope::None => false,
    };
    if allowed {
        Ok(())
    } else {
        Err(format!(
            "DATABASE_OUT_OF_SCOPE: database '{database}' is not allowed by DBX MCP settings for connection '{connection_id}'"
        ))
    }
}

#[cfg(test)]
fn effective_database_execution_policy(policy: &McpGlobalPolicy, connection_id: &str, database: &str) -> (bool, bool) {
    effective_database_execution_policy_with_groups(policy, &[], connection_id, database)
}

fn effective_database_execution_policy_with_groups(
    policy: &McpGlobalPolicy,
    group_ids: &[String],
    connection_id: &str,
    database: &str,
) -> (bool, bool) {
    dbx_core::mcp_policy::effective_database_execution_policy_with_groups(policy, group_ids, connection_id, database)
}

async fn ensure_mcp_write_allowed(
    state: &Arc<AppState>,
    config: &crate::models::connection::ConnectionConfig,
    database: &str,
    action: &str,
) -> Result<(), String> {
    ensure_mcp_write_allowed_with_risk(state, config, database, action, false).await
}

async fn ensure_mcp_write_allowed_with_risk(
    state: &Arc<AppState>,
    config: &crate::models::connection::ConnectionConfig,
    database: &str,
    action: &str,
    dangerous: bool,
) -> Result<(), String> {
    let (policy, group_paths) = load_mcp_policy_context(state).await?;
    let group_path = group_paths.get(&config.id);
    ensure_connection_in_mcp_scope_with_groups(&policy, group_path, &config.id)?;
    ensure_database_in_mcp_scope(&policy, &config.id, database)?;
    let group_ids = group_path.map(|path| path.ids.as_slice()).unwrap_or_default();
    let (read_only, allow_dangerous_sql) =
        effective_database_execution_policy_with_groups(&policy, group_ids, &config.id, database);
    if read_only {
        return Err(format!(
            "MCP_READ_ONLY: MCP execution permission for database '{database}' is read-only. {action} blocked."
        ));
    }
    if dangerous && !allow_dangerous_sql {
        return Err(format!("SQL_BLOCKED: High-risk operation '{action}' is disabled in DBX MCP settings."));
    }
    if config.read_only {
        return Err(format!(
            "CONNECTION_READ_ONLY: connection '{}' has read-only protection enabled. {action} blocked.",
            config.name
        ));
    }
    if dbx_core::production_safety::is_production_database(config, database) {
        return Err(format!("PRODUCTION_DATABASE_READ_ONLY: {action} blocked for production database '{database}'."));
    }
    Ok(())
}

pub(crate) async fn ensure_mcp_read_allowed_by_id(
    state: &Arc<AppState>,
    connection_id: &str,
    database: &str,
) -> Result<(), String> {
    let (policy, group_paths) = load_mcp_policy_context(state).await?;
    ensure_connection_in_mcp_scope_with_groups(&policy, group_paths.get(connection_id), connection_id)?;
    ensure_database_in_mcp_scope(&policy, connection_id, database)?;
    let configs = state.storage.load_connections().await.map_err(|e| format!("MCP_POLICY_UNAVAILABLE: {e}"))?;
    let config = configs
        .iter()
        .find(|config| config.id == connection_id)
        .ok_or_else(|| format!("Connection with id '{connection_id}' not found"))?;
    check_visible_database(config, database)
}

pub(crate) async fn ensure_mcp_write_allowed_by_id(
    state: &Arc<AppState>,
    connection_id: &str,
    database: &str,
    action: &str,
) -> Result<(), String> {
    ensure_mcp_write_allowed_by_id_with_risk(state, connection_id, database, action, false).await
}

pub(crate) async fn ensure_mcp_dangerous_write_allowed_by_id(
    state: &Arc<AppState>,
    connection_id: &str,
    database: &str,
    action: &str,
) -> Result<(), String> {
    ensure_mcp_write_allowed_by_id_with_risk(state, connection_id, database, action, true).await
}

async fn ensure_mcp_mongo_pipeline_target_allowed_by_id(
    state: &Arc<AppState>,
    connection_id: &str,
    database: &str,
    pipeline_json: &str,
) -> Result<(), String> {
    let (policy, group_paths) = load_mcp_policy_context(state).await?;
    ensure_connection_in_mcp_scope_with_groups(&policy, group_paths.get(connection_id), connection_id)?;
    dbx_core::mcp_policy::ensure_mongo_database_execution_scope(&policy, connection_id, database, pipeline_json)?;
    for target_database in mongo_pipeline_output_databases(pipeline_json, database)? {
        ensure_database_in_mcp_scope(&policy, connection_id, &target_database)?;
    }
    let configs = state.storage.load_connections().await.map_err(|e| format!("MCP_POLICY_UNAVAILABLE: {e}"))?;
    let config = configs
        .iter()
        .find(|config| config.id == connection_id)
        .ok_or_else(|| format!("Connection with id '{connection_id}' not found"))?;
    if dbx_core::production_safety::mongo_pipeline_targets_production_database(config, database, pipeline_json) {
        return Err(
            "PRODUCTION_DATABASE_READ_ONLY: MongoDB aggregate write targeting production scope is blocked.".to_string()
        );
    }
    Ok(())
}

fn mongo_pipeline_output_databases(pipeline_json: &str, active_database: &str) -> Result<Vec<String>, String> {
    dbx_core::mcp_policy::mongo_pipeline_output_databases(pipeline_json, active_database)
}

async fn ensure_mcp_write_allowed_by_id_with_risk(
    state: &Arc<AppState>,
    connection_id: &str,
    database: &str,
    action: &str,
    dangerous: bool,
) -> Result<(), String> {
    let configs = state.storage.load_connections().await.map_err(|e| format!("MCP_POLICY_UNAVAILABLE: {e}"))?;
    let config = configs
        .iter()
        .find(|config| config.id == connection_id)
        .ok_or_else(|| format!("Connection with id '{connection_id}' not found"))?;
    ensure_mcp_write_allowed_with_risk(state, config, database, action, dangerous).await
}

pub(crate) async fn ensure_mcp_mongo_filtered_write_allowed_by_id(
    state: &Arc<AppState>,
    connection_id: &str,
    database: &str,
    action: &str,
    filter_json: &str,
) -> Result<(), String> {
    if mongo_filter_is_effectively_unbounded(filter_json) {
        ensure_mcp_dangerous_write_allowed_by_id(state, connection_id, database, action).await
    } else {
        ensure_mcp_write_allowed_by_id(state, connection_id, database, action).await
    }
}

/// A bulk write is as dangerous as its least-bounded operation.
pub(crate) async fn ensure_mcp_mongo_bulk_write_allowed_by_id(
    state: &Arc<AppState>,
    connection_id: &str,
    database: &str,
    operations_json: &str,
) -> Result<(), String> {
    let command = dbx_core::mongo_shell::MongoCommand::BulkWrite {
        collection: String::new(),
        operations: operations_json.to_string(),
        options: None,
    };
    if command.has_effectively_unbounded_filter() {
        ensure_mcp_dangerous_write_allowed_by_id(state, connection_id, database, "BulkWrite").await
    } else {
        ensure_mcp_write_allowed_by_id(state, connection_id, database, "BulkWrite").await
    }
}

pub(crate) async fn ensure_mcp_mongo_aggregate_allowed_by_id(
    state: &Arc<AppState>,
    connection_id: &str,
    database: &str,
    pipeline_json: &str,
) -> Result<(), String> {
    if !mongo_pipeline_has_write_stage(pipeline_json) {
        return ensure_mcp_read_allowed_by_id(state, connection_id, database).await;
    }
    ensure_mcp_dangerous_write_allowed_by_id(state, connection_id, database, "MongoDB aggregate write").await?;
    ensure_mcp_mongo_pipeline_target_allowed_by_id(state, connection_id, database, pipeline_json).await
}

fn mongo_pipeline_has_write_stage(pipeline_json: &str) -> bool {
    serde_json::from_str::<serde_json::Value>(pipeline_json)
        .ok()
        .and_then(|value| value.as_array().cloned())
        .is_some_and(|stages| {
            stages.iter().any(|stage| {
                stage
                    .as_object()
                    .is_some_and(|document| document.contains_key("$out") || document.contains_key("$merge"))
            })
        })
}

fn mongo_filter_is_effectively_unbounded(filter_json: &str) -> bool {
    serde_json::from_str::<serde_json::Value>(filter_json)
        .ok()
        .as_ref()
        .is_none_or(|value| mongo_filter_contains_opaque_logic(value) || mongo_filter_value_is_unbounded(value))
}

fn mongo_filter_contains_opaque_logic(value: &serde_json::Value) -> bool {
    let Some(filter) = value.as_object() else {
        return true;
    };
    filter.iter().any(|(key, value)| match key.as_str() {
        "$comment" => false,
        "$where" | "$expr" | "$nor" => true,
        "$and" | "$or" => {
            let Some(clauses) = value.as_array() else {
                return true;
            };
            clauses.is_empty()
                || clauses.iter().any(|clause| !clause.is_object() || mongo_filter_contains_opaque_logic(clause))
                || (key == "$or"
                    && clauses
                        .iter()
                        .any(|clause| clause.as_object().is_some_and(|document| document.contains_key("$and"))))
                || (key == "$or" && mongo_or_has_complementary_field_clauses(clauses))
        }
        _ => key.starts_with('$') || mongo_field_predicate_contains_opaque_logic(value),
    })
}

fn mongo_field_predicate_contains_opaque_logic(value: &serde_json::Value) -> bool {
    let Some(predicate) = value.as_object() else {
        return false;
    };
    if mongo_extended_json_scalar_literal_is_valid(value) {
        return false;
    }
    let has_operator = predicate.keys().any(|key| key.starts_with('$'));
    has_operator
        && predicate.keys().any(|key| {
            !matches!(key.as_str(), "$eq" | "$ne" | "$gt" | "$gte" | "$lt" | "$lte" | "$in" | "$nin" | "$exists")
        })
}

fn mongo_extended_json_scalar_literal_is_valid(value: &serde_json::Value) -> bool {
    let Some(wrapper) = value.as_object().filter(|wrapper| wrapper.len() == 1) else {
        return false;
    };
    if let Some(value) = wrapper.get("$oid").and_then(serde_json::Value::as_str) {
        return value.len() == 24 && value.bytes().all(|byte| byte.is_ascii_hexdigit());
    }
    if let Some(value) = wrapper.get("$numberLong").and_then(serde_json::Value::as_str) {
        return value.parse::<i64>().is_ok();
    }
    wrapper
        .get("$date")
        .and_then(serde_json::Value::as_str)
        .is_some_and(|value| chrono::DateTime::parse_from_rfc3339(value).is_ok())
}

#[derive(Clone, Copy, PartialEq, Eq)]
enum MongoFieldOperator {
    Eq,
    Ne,
    Gt,
    Gte,
    Lt,
    Lte,
    In,
    Nin,
    Exists,
}

struct MongoPureFieldPredicate<'a> {
    field: &'a str,
    operator: MongoFieldOperator,
    operand: &'a serde_json::Value,
}

fn mongo_or_has_complementary_field_clauses(clauses: &[serde_json::Value]) -> bool {
    clauses.iter().enumerate().any(|(index, clause)| {
        let Some(predicate) = mongo_pure_field_predicate(clause) else {
            return false;
        };
        clauses[index + 1..]
            .iter()
            .filter_map(mongo_pure_field_predicate)
            .any(|other| mongo_field_predicates_are_complementary(&predicate, &other))
    })
}

fn mongo_pure_field_predicate(value: &serde_json::Value) -> Option<MongoPureFieldPredicate<'_>> {
    let filter = value.as_object()?;
    let mut entries = filter.iter().filter(|(key, _)| key.as_str() != "$comment");
    let (field, predicate) = entries.next()?;
    if entries.next().is_some() {
        return None;
    }
    if field == "$and" {
        let clauses = predicate.as_array()?;
        let mut bounded = clauses.iter().filter(|clause| !mongo_filter_value_is_unbounded(clause));
        let clause = bounded.next()?;
        if bounded.next().is_some() {
            return None;
        }
        return mongo_pure_field_predicate(clause);
    }
    if field == "$or" {
        let clauses = predicate.as_array()?;
        return (clauses.len() == 1).then(|| mongo_pure_field_predicate(&clauses[0])).flatten();
    }
    if field.starts_with('$') {
        return None;
    }
    let Some(operator_document) = predicate.as_object() else {
        return Some(MongoPureFieldPredicate { field, operator: MongoFieldOperator::Eq, operand: predicate });
    };
    if mongo_extended_json_scalar_literal_is_valid(predicate)
        || !operator_document.keys().any(|key| key.starts_with('$'))
    {
        return Some(MongoPureFieldPredicate { field, operator: MongoFieldOperator::Eq, operand: predicate });
    }
    let mut operators = operator_document.iter();
    let (operator, operand) = operators.next()?;
    if operators.next().is_some() {
        return None;
    }
    let operator = match operator.as_str() {
        "$eq" => MongoFieldOperator::Eq,
        "$ne" => MongoFieldOperator::Ne,
        "$gt" => MongoFieldOperator::Gt,
        "$gte" => MongoFieldOperator::Gte,
        "$lt" => MongoFieldOperator::Lt,
        "$lte" => MongoFieldOperator::Lte,
        "$in" => MongoFieldOperator::In,
        "$nin" => MongoFieldOperator::Nin,
        "$exists" => MongoFieldOperator::Exists,
        _ => return None,
    };
    Some(MongoPureFieldPredicate { field, operator, operand })
}

fn mongo_field_predicates_are_complementary(
    left: &MongoPureFieldPredicate<'_>,
    right: &MongoPureFieldPredicate<'_>,
) -> bool {
    if left.field != right.field {
        return false;
    }
    use MongoFieldOperator::{Eq, Exists, Gt, Gte, In, Lt, Lte, Ne, Nin};
    match (left.operator, right.operator) {
        (Exists, Exists) => {
            left.operand.as_bool().zip(right.operand.as_bool()).is_some_and(|(left, right)| left != right)
        }
        (In, Nin) | (Nin, In) => mongo_json_sets_equal(left.operand, right.operand),
        (Eq, Ne) | (Ne, Eq) | (Gt, Lte) | (Lte, Gt) | (Gte, Lt) | (Lt, Gte) => left.operand == right.operand,
        _ => false,
    }
}

fn mongo_json_sets_equal(left: &serde_json::Value, right: &serde_json::Value) -> bool {
    let (Some(left), Some(right)) = (left.as_array(), right.as_array()) else {
        return false;
    };
    left.iter().all(|value| right.contains(value)) && right.iter().all(|value| left.contains(value))
}

fn mongo_filter_value_is_unbounded(value: &serde_json::Value) -> bool {
    let Some(filter) = value.as_object() else {
        return true;
    };
    if filter.is_empty() || filter.contains_key("$where") || filter.contains_key("$expr") {
        return true;
    }
    filter.iter().all(|(key, value)| match key.as_str() {
        "$comment" => true,
        "$and" => value
            .as_array()
            .is_none_or(|clauses| clauses.is_empty() || clauses.iter().all(mongo_filter_value_is_unbounded)),
        "$or" => value
            .as_array()
            .is_none_or(|clauses| clauses.is_empty() || clauses.iter().any(mongo_filter_value_is_unbounded)),
        "$nor" => true,
        _ if mongo_field_predicate_is_empty_nin(value) => true,
        "_id" if mongo_field_predicate_is_exists_true(value) => true,
        _ => key.starts_with('$'),
    })
}

fn mongo_field_predicate_is_empty_nin(value: &serde_json::Value) -> bool {
    value.as_object().is_some_and(|predicate| {
        predicate.len() == 1 && predicate.get("$nin").and_then(serde_json::Value::as_array).is_some_and(Vec::is_empty)
    })
}

fn mongo_field_predicate_is_exists_true(value: &serde_json::Value) -> bool {
    value.as_object().is_some_and(|predicate| {
        predicate.len() == 1 && predicate.get("$exists").and_then(serde_json::Value::as_bool) == Some(true)
    })
}

async fn ensure_mcp_sql_allowed(
    state: &Arc<AppState>,
    config: &crate::models::connection::ConnectionConfig,
    database: &str,
    sql: &str,
) -> Result<(), String> {
    ensure_mcp_sql_allowed_with_approval(state, config, database, sql, false).await
}

async fn ensure_mcp_sql_allowed_with_approval(
    state: &Arc<AppState>,
    config: &crate::models::connection::ConnectionConfig,
    database: &str,
    sql: &str,
    approved: bool,
) -> Result<(), String> {
    let (policy, group_paths) = load_mcp_policy_context(state).await?;
    let group_path = group_paths.get(&config.id);
    ensure_connection_in_mcp_scope_with_groups(&policy, group_path, &config.id)?;
    ensure_database_in_mcp_scope(&policy, &config.id, database)?;
    if let Some(rule) = policy.connection_policies.iter().find(|rule| rule.connection_id == config.id) {
        if rule.database_scope == McpDatabaseScope::Selected
            && dbx_core::production_safety::sql_references_disallowed_database(
                sql,
                &config.db_type,
                database,
                &rule.allowed_databases,
            )
        {
            return Err(
                "DATABASE_OUT_OF_SCOPE: SQL references a database that is not allowed by DBX MCP settings for this connection."
                    .to_string(),
            );
        }
    }
    ensure_mcp_sql_database_switch_allowed(config.db_type, sql)?;
    let risk = dbx_core::sql_risk::classify_sql_risk_for_database(sql, config.db_type)
        .map_err(|error| format!("SQL_BLOCKED: {error}"))?;
    let is_write = risk != dbx_core::sql_risk::SqlRisk::ReadOnly
        || dbx_core::query_execution_sql::is_write_sql_for_database(sql, config.db_type);
    dbx_core::mcp_policy::ensure_sql_database_execution_scope(&policy, config, database, sql)?;
    let group_ids = group_path.map(|path| path.ids.as_slice()).unwrap_or_default();
    let (read_only, allow_dangerous_sql) =
        effective_database_execution_policy_with_groups(&policy, group_ids, &config.id, database);
    if read_only && is_write {
        return Err(format!(
            "MCP_READ_ONLY: MCP execution permission for database '{database}' is read-only. SQL write blocked."
        ));
    }
    let high_risk = risk == dbx_core::sql_risk::SqlRisk::Ddl
        || dbx_core::sql_risk::is_dangerous_sql_for_database(sql, config.db_type);
    if high_risk {
        match policy.confirmation_policy().decide(
            McpAccessLevel::from_flags(read_only, allow_dangerous_sql),
            McpAccessLevel::Full,
            true,
        ) {
            McpCallDecision::Deny => {
                return Err("SQL_BLOCKED: High-risk SQL is disabled in DBX MCP settings.".to_string())
            }
            McpCallDecision::Confirm if !approved => {
                return Err("SQL_BLOCKED: This high-risk call requires confirmation.".to_string())
            }
            _ => {}
        }
    }
    ensure_mcp_connection_sql_write_allowed(config, is_write)?;
    if is_write && dbx_core::production_safety::targets_production_database(config, database, sql) {
        return Err("PRODUCTION_DATABASE_READ_ONLY: SQL write targeting production scope is blocked.".to_string());
    }
    Ok(())
}

async fn handle_sql_approval(app: &AppHandle, state: &Arc<AppState>, body: &str, stream: &mut tokio::net::TcpStream) {
    let req: SqlApprovalRequest = match serde_json::from_str(body) {
        Ok(req) => req,
        Err(_) => {
            respond_error(stream, "400 Bad Request", "Invalid approval request").await;
            return;
        }
    };
    let config = match resolve_connection(state, Some(&req.connection_id), "").await {
        Ok(config) => config,
        Err(error) => {
            respond_error(stream, "403 Forbidden", &error).await;
            return;
        }
    };
    // A prompt must not be offered for an operation that cannot be executed
    // even with high-risk permission (scope, read-only, or production).
    let (policy, group_paths) = match load_mcp_policy_context(state).await {
        Ok(context) => context,
        Err(error) => {
            respond_error(stream, "403 Forbidden", &error).await;
            return;
        }
    };
    let group_ids = group_paths.get(&config.id).map(|path| path.ids.as_slice()).unwrap_or_default();
    let (read_only, allow_dangerous) =
        effective_database_execution_policy_with_groups(&policy, group_ids, &config.id, &req.database);
    let high_risk = dbx_core::sql_risk::classify_sql_risk_for_database(&req.sql, config.db_type)
        .is_ok_and(|risk| risk == dbx_core::sql_risk::SqlRisk::Ddl)
        || dbx_core::sql_risk::is_dangerous_sql_for_database(&req.sql, config.db_type);
    let confirmation = policy.confirmation_policy();
    let allow_timed = confirmation == McpConfirmPolicy::Full;
    if !high_risk
        || confirmation.decide(McpAccessLevel::from_flags(read_only, allow_dangerous), McpAccessLevel::Full, true)
            != McpCallDecision::Confirm
    {
        respond_error(stream, "403 Forbidden", "SQL_BLOCKED: High-risk SQL approval is unavailable.").await;
        return;
    }
    if let Err(error) = ensure_mcp_sql_allowed_with_approval(state, &config, &req.database, &req.sql, true).await {
        respond_error(stream, "403 Forbidden", &error).await;
        return;
    }
    let key = (config.id.clone(), req.database.clone());
    let now = Instant::now();
    let existing = (|| {
        let mut approvals = sql_approvals().lock().map_err(|_| ())?;
        approvals
            .timed
            .retain(|grant_key, grant| grant.expires_at > now && (grant_key != &key || grant.valid_for(&config, now)));
        Ok::<_, ()>((
            (allow_timed && approvals.timed.contains_key(&key)).then_some(SqlApprovalDecision::Hour),
            approvals.generation,
        ))
    })();
    let (decision, generation) = match existing {
        Ok(existing) => existing,
        Err(()) => {
            respond_error(stream, "500 Internal Server Error", "MCP approval state unavailable").await;
            return;
        }
    };
    let decision = if let Some(decision) = decision {
        decision
    } else {
        let id = uuid::Uuid::new_v4().to_string();
        let (sender, receiver) = oneshot::channel();
        let queued = (|| {
            let mut approvals =
                sql_approvals().lock().map_err(|_| ("500 Internal Server Error", "MCP approval state unavailable"))?;
            if approvals.pending.len() >= 16 {
                return Err(("429 Too Many Requests", "Too many pending MCP approvals"));
            }
            approvals.pending.insert(id.clone(), sender);
            Ok::<_, (&str, &str)>(())
        })();
        if let Err((status, message)) = queued {
            respond_error(stream, status, message).await;
            return;
        }
        let event = SqlApprovalEvent {
            allow_timed,
            id: id.clone(),
            connection_name: config.name.clone(),
            database: req.database.clone(),
            sql: req.sql.clone(),
        };
        if app.emit_to("main", "mcp-sql-approval", event).is_err() {
            sql_approvals().lock().ok().map(|mut approvals| approvals.pending.remove(&id));
            respond_error(stream, "503 Service Unavailable", "DBX approval window unavailable").await;
            return;
        }
        let answer = tokio::time::timeout(Duration::from_secs(90), receiver).await;
        sql_approvals().lock().ok().map(|mut approvals| approvals.pending.remove(&id));
        match answer {
            Ok(Ok(decision)) => decision,
            _ => {
                respond_error(stream, "403 Forbidden", "SQL_BLOCKED: MCP approval timed out.").await;
                return;
            }
        }
    };
    if matches!(decision, SqlApprovalDecision::Deny) || (!allow_timed && !matches!(decision, SqlApprovalDecision::Once))
    {
        respond_error(stream, "403 Forbidden", "SQL_BLOCKED: MCP approval denied.").await;
        return;
    }
    // Re-read mutable policy after the person responds. Revocation wins.
    let current = match resolve_connection(state, Some(&config.id), "").await {
        Ok(current) => current,
        Err(error) => {
            respond_error(stream, "403 Forbidden", &error).await;
            return;
        }
    };
    if current != config {
        respond_error(stream, "403 Forbidden", "SQL_BLOCKED: Connection changed while awaiting approval.").await;
        return;
    }
    if let Err(error) = ensure_mcp_sql_allowed_with_approval(state, &current, &req.database, &req.sql, true).await {
        respond_error(stream, "403 Forbidden", &error).await;
        return;
    }
    let token = uuid::Uuid::new_v4().to_string();
    let grant_result = (|| -> Result<(), &'static str> {
        let mut approvals = sql_approvals().lock().map_err(|_| "MCP approval state unavailable")?;
        if approvals.generation != generation {
            return Err("SQL_BLOCKED: Approval revoked by policy change.");
        }
        if let Some(duration) = match decision {
            SqlApprovalDecision::Hour => Some(Duration::from_secs(3600)),
            SqlApprovalDecision::Day => Some(Duration::from_secs(86400)),
            _ => None,
        } {
            // Do not renew an existing grant on use.
            approvals.timed.entry(key).or_insert_with(|| TimedSqlApproval {
                connection: config.clone(),
                expires_at: Instant::now() + duration,
            });
        }
        approvals.tokens.retain(|_, grant| grant.expires_at > Instant::now());
        approvals.tokens.insert(
            token.clone(),
            SqlApprovalGrant {
                connection: config,
                database: req.database,
                sql: req.sql,
                expires_at: Instant::now() + Duration::from_secs(90),
            },
        );
        Ok(())
    })();
    if let Err(error) = grant_result {
        respond_error(stream, "403 Forbidden", error).await;
        return;
    }
    respond_json(stream, &serde_json::json!({ "token": token })).await;
}

/// The MCP server validates the exact tool/sub-scope level before requesting
/// and after receiving confirmation. The bridge gates the shared switch and
/// independent protections; a global read default cannot veto resource overrides.
async fn tool_approval_target(state: &Arc<AppState>, req: &ToolApprovalRequest) -> Result<Option<String>, String> {
    let policy = load_mcp_policy(state).await?;
    if policy.confirmation_policy() == McpConfirmPolicy::Off || (req.connection_id.is_none() && policy.read_only) {
        return Err("TOOL_BLOCKED: High-risk confirmation is unavailable in DBX MCP settings.".to_string());
    }
    let Some(id) = req.connection_id.as_deref() else { return Ok(None) };
    let config = resolve_connection(state, Some(id), "").await?;
    if config.read_only {
        return Err("CONNECTION_READ_ONLY: The connection has read-only protection enabled.".to_string());
    }
    if dbx_core::production_safety::is_production_database(&config, req.database.as_deref().unwrap_or_default()) {
        return Err("PRODUCTION_WRITE_BLOCKED: The target has production protection enabled.".to_string());
    }
    Ok(Some(config.name))
}

/// Shows a one-shot confirmation for a high-risk non-SQL tool call and answers
/// once the person decided. Shares the pending queue (and its cap and policy
/// generation) with SQL approvals, so a policy save cancels it too.
async fn handle_tool_approval(app: &AppHandle, state: &Arc<AppState>, body: &str, stream: &mut tokio::net::TcpStream) {
    let req: ToolApprovalRequest = match serde_json::from_str(body) {
        Ok(req) => req,
        Err(_) => {
            respond_error(stream, "400 Bad Request", "Invalid tool approval request").await;
            return;
        }
    };
    let connection_name = match tool_approval_target(state, &req).await {
        Ok(name) => name,
        Err(error) => {
            respond_error(stream, "403 Forbidden", &error).await;
            return;
        }
    };
    let id = uuid::Uuid::new_v4().to_string();
    let (sender, receiver) = oneshot::channel();
    let queued = (|| {
        let mut approvals =
            sql_approvals().lock().map_err(|_| ("500 Internal Server Error", "MCP approval state unavailable"))?;
        if approvals.pending.len() >= 16 {
            return Err(("429 Too Many Requests", "Too many pending MCP approvals"));
        }
        approvals.pending.insert(id.clone(), sender);
        Ok::<_, (&str, &str)>(approvals.generation)
    })();
    let generation = match queued {
        Ok(generation) => generation,
        Err((status, message)) => {
            respond_error(stream, status, message).await;
            return;
        }
    };
    let event = ToolApprovalEvent {
        id: id.clone(),
        kind: req.kind,
        tool: req.tool,
        plugin: req.plugin,
        connection_name,
        database: req.database,
        detail: req.detail,
    };
    if app.emit_to("main", "mcp-tool-approval", event).is_err() {
        sql_approvals().lock().ok().map(|mut approvals| approvals.pending.remove(&id));
        respond_error(stream, "503 Service Unavailable", "DBX approval window unavailable").await;
        return;
    }
    let answer = tokio::time::timeout(Duration::from_secs(90), receiver).await;
    sql_approvals().lock().ok().map(|mut approvals| approvals.pending.remove(&id));
    let approved = matches!(answer, Ok(Ok(decision)) if !matches!(decision, SqlApprovalDecision::Deny));
    if !approved {
        let message = if answer.is_err() {
            "TOOL_BLOCKED: MCP confirmation timed out."
        } else {
            "TOOL_BLOCKED: MCP confirmation denied."
        };
        respond_error(stream, "403 Forbidden", message).await;
        return;
    }
    let revoked = sql_approvals().lock().map(|approvals| approvals.generation != generation).unwrap_or(true);
    if revoked {
        respond_error(stream, "403 Forbidden", "TOOL_BLOCKED: Approval revoked by policy change.").await;
        return;
    }
    respond_json(stream, &serde_json::json!({ "ok": true })).await;
}

async fn handle_consume_sql_approval(state: &Arc<AppState>, body: &str, stream: &mut tokio::net::TcpStream) {
    let req: ConsumeSqlApprovalRequest = match serde_json::from_str(body) {
        Ok(req) => req,
        Err(_) => {
            respond_error(stream, "400 Bad Request", "Invalid approval request").await;
            return;
        }
    };
    let config = match resolve_connection(state, Some(&req.connection_id), "").await {
        Ok(config) => config,
        Err(error) => {
            respond_error(stream, "403 Forbidden", &error).await;
            return;
        }
    };
    if !take_sql_approval(Some(&req.token), &config, &req.database, &req.sql) {
        respond_error(stream, "403 Forbidden", "SQL_BLOCKED: Approval expired or does not match this request.").await;
        return;
    }
    if let Err(error) = ensure_mcp_sql_allowed_with_approval(state, &config, &req.database, &req.sql, true).await {
        respond_error(stream, "403 Forbidden", &error).await;
        return;
    }
    respond_json(stream, &serde_json::json!({ "ok": true })).await;
}

fn ensure_mcp_sql_database_switch_allowed(
    database_type: crate::models::connection::DatabaseType,
    sql: &str,
) -> Result<(), String> {
    if dbx_core::sql_risk::mcp_sql_has_forbidden_database_switch(sql, database_type) {
        return Err("SQL_BLOCKED: MCP does not allow USE or persistent database switching.".to_string());
    }
    Ok(())
}

fn ensure_mcp_connection_sql_write_allowed(
    config: &crate::models::connection::ConnectionConfig,
    is_write: bool,
) -> Result<(), String> {
    if is_write && config.read_only {
        return Err(format!(
            "CONNECTION_READ_ONLY: connection '{}' has read-only protection enabled. SQL write blocked.",
            config.name
        ));
    }
    Ok(())
}

fn check_visible_database(config: &crate::models::connection::ConnectionConfig, database: &str) -> Result<(), String> {
    if let Some(ref visible) = config.visible_databases {
        if !visible.is_empty() && !visible.iter().any(|v| v == database) {
            return Err(format!("Database '{}' is not in the visible databases list for this connection", database));
        }
    }
    Ok(())
}

fn resolve_mongo_database(requested: Option<String>, configured: Option<String>) -> String {
    requested.filter(|database| !database.trim().is_empty()).or(configured).unwrap_or_default()
}

fn resolve_mongo_target_values(
    connection_id: String,
    requested_database: Option<String>,
    configured_database: Option<String>,
) -> (String, String) {
    (connection_id, resolve_mongo_database(requested_database, configured_database))
}

async fn resolve_mongo_target(
    state: &Arc<AppState>,
    connection_id: Option<&str>,
    connection_name: &str,
    database: Option<String>,
    stream: &mut tokio::net::TcpStream,
) -> Option<(String, String)> {
    let config = match resolve_connection(state, connection_id, connection_name).await {
        Ok(c) => c,
        Err(e) => {
            respond_error(stream, "404 Not Found", &e).await;
            return None;
        }
    };
    let (connection_id, database) = resolve_mongo_target_values(config.id.clone(), database, config.database.clone());
    if let Err(e) = check_visible_database(&config, &database) {
        respond_error(stream, "403 Forbidden", &e).await;
        return None;
    }
    Some((connection_id, database))
}

async fn handle_open_table(app: &AppHandle, state: &Arc<AppState>, body: &str, stream: &mut tokio::net::TcpStream) {
    let req: OpenTableRequest = match serde_json::from_str(body) {
        Ok(r) => r,
        Err(_) => {
            respond(stream, "400 Bad Request", "").await;
            return;
        }
    };
    let config = match resolve_connection(state, req.connection_id.as_deref(), &req.connection_name).await {
        Ok(c) => c,
        Err(e) => {
            respond(stream, "404 Not Found", &e).await;
            return;
        }
    };
    let event = McpOpenTableEvent {
        connection_id: config.id.clone(),
        database: req.database.unwrap_or_else(|| config.database.clone().unwrap_or_default()),
        schema: req.schema,
        table: req.table,
    };
    let _ = app.emit("mcp-open-table", &event);
    respond(stream, "200 OK", "ok").await;
}

#[derive(Deserialize)]
struct CallPluginToolRequest {
    connection_id: String,
    tool: String,
    #[serde(default)]
    arguments: serde_json::Value,
    plugin_id: Option<String>,
    timeout_ms: Option<u64>,
}

/// Tools whose terminal routing (`runInTerminal` / terminal MCP mode) can
/// require the workbench terminal to exist. Every other forwarded tool runs
/// on the hidden channel by design and must never open the workbench.
fn is_terminal_routed_exec(tool: &str) -> bool {
    tool == "ssh_exec" || tool == "ssh_exec_sudo"
}

/// Asks the plugin for a connection's terminal MCP mode (the toggle in the
/// SSH terminal toolbar). Any failure — an older plugin without the probe
/// route, a busy sidecar — degrades to off, so the silent path can never
/// regress into opening the workbench.
async fn plugin_agent_mode_on(state: &Arc<AppState>, plugin_id: &str, connection_id: &str) -> bool {
    let probe: Result<serde_json::Value, String> = state
        .plugin_host
        .invoke(
            plugin_id,
            "ssh/agent/mode/get",
            serde_json::json!({ "connectionId": connection_id }),
            None,
            Some(std::time::Duration::from_secs(5)),
        )
        .await;
    probe
        .ok()
        .and_then(|value| value.get("agentTerminalMode").and_then(serde_json::Value::as_str).map(str::to_string))
        .is_some_and(|mode| mode == "auto" || mode == "strict")
}

/// `/call-plugin-tool` may only talk to the saved connection's own plugin:
/// the lifecycle payload (which carries the connection's credentials) is
/// built from the config, so honoring a request-named `plugin_id` would hand
/// connection A's secrets to plugin B's sidecar. Callers may repeat the
/// binding, never override it.
fn resolve_call_plugin_target(
    config_plugin_id: Option<&str>,
    request_plugin_id: Option<&str>,
) -> Result<String, String> {
    let bound = config_plugin_id
        .filter(|id| !id.is_empty())
        .ok_or_else(|| "connection is not bound to a plugin".to_string())?;
    if let Some(requested) = request_plugin_id {
        if requested != bound {
            return Err(format!("plugin_id {requested:?} does not match the connection's plugin {bound:?}"));
        }
    }
    Ok(bound.to_string())
}

/// POST /call-plugin-tool: runs a plugin MCP tool on the desktop app's own
/// plugin session — the same sidecar process the workbench talks to. The
/// connection's workbench tab is opened only when the call will route into
/// the visible terminal (ssh runInTerminal / terminal MCP mode), so
/// agent-terminal commands execute in a real PTY while silent hidden-channel
/// calls never open or focus the app. The lifecycle payload comes from the
/// saved connection config, so callers never pass credentials.
async fn handle_call_plugin_tool(
    app: &AppHandle,
    state: &Arc<AppState>,
    body: &str,
    stream: &mut tokio::net::TcpStream,
) {
    let req: CallPluginToolRequest = match serde_json::from_str(body) {
        Ok(r) => r,
        Err(e) => {
            respond_error(stream, "400 Bad Request", &format!("invalid body: {e}")).await;
            return;
        }
    };
    let config = match resolve_connection(state, Some(&req.connection_id), "").await {
        Ok(c) => c,
        Err(e) => {
            respond_error(stream, "404 Not Found", &e).await;
            return;
        }
    };
    let plugin_id = match resolve_call_plugin_target(config.plugin_id.as_deref(), req.plugin_id.as_deref()) {
        Ok(plugin_id) => plugin_id,
        Err(message) => {
            respond_error(stream, "400 Bad Request", &message).await;
            return;
        }
    };
    // The workbench tab only needs to exist when the forwarded call will
    // actually route into the visible terminal: an explicit runInTerminal,
    // or no flag plus the connection's terminal MCP mode being on (asked
    // from the plugin, so the toggle in the terminal UI owns the decision).
    // Everything else — sftp listings, silent execs on the hidden channel —
    // must not open (let alone focus) the app while the agent works.
    let route_terminal = is_terminal_routed_exec(&req.tool)
        && match req.arguments.get("runInTerminal").and_then(serde_json::Value::as_bool) {
            Some(explicit) => explicit,
            None => plugin_agent_mode_on(state, &plugin_id, &config.id).await,
        };
    if route_terminal {
        let _ = app.emit("mcp-open-connection-workbench", serde_json::json!({ "connection_id": config.id }));
    }
    let lifecycle = match state.plugin_host.connection_params_standalone(&config) {
        Ok(l) => l,
        Err(e) => {
            respond_error(stream, "500 Internal Server Error", &e).await;
            return;
        }
    };
    let params = serde_json::json!({
        "tool": req.tool,
        "arguments": req.arguments,
        "lifecycle": lifecycle,
    });
    // Long ceiling: agent-terminal calls may sit in a workbench approval
    // prompt (default 120s) before the command even starts.
    let timeout = std::time::Duration::from_millis(req.timeout_ms.unwrap_or(300_000).clamp(1_000, 600_000));
    let result: Result<serde_json::Value, String> =
        state.plugin_host.invoke(&plugin_id, "mcp/call", params, None, Some(timeout)).await;
    match result {
        Ok(value) => respond_json(stream, &value).await,
        Err(e) => respond_error(stream, "502 Bad Gateway", &e).await,
    }
}

#[derive(Deserialize)]
struct ListPluginConnectionsRequest {
    plugin_id: String,
}

/// Metadata-only view of a saved plugin connection. This is a deliberate
/// field whitelist: credentials (`password`, `connection_secrets`, private
/// keys, `sudo_password`, `totp_secret`, ...) can never reach the response
/// because they are not part of this struct and `external_config` is only
/// probed for the two safe keys below.
#[derive(Serialize)]
#[serde(rename_all = "camelCase")]
struct PluginConnectionSummary {
    id: String,
    name: String,
    host: String,
    port: u16,
    username: String,
    authentication: String,
    read_only: bool,
}

fn plugin_connection_summary(config: &crate::models::connection::ConnectionConfig) -> PluginConnectionSummary {
    let external = config.external_config.as_ref();
    // The dialog persists every declared config-bound field (including
    // defaults), so `authentication` is normally present; fall back to the
    // provider's historical default ("password") for pre-existing rows.
    let authentication = external
        .and_then(|v| v.get("authentication"))
        .and_then(serde_json::Value::as_str)
        .unwrap_or("password")
        .to_string();
    let read_only =
        external.and_then(|v| v.get("read_only")).and_then(serde_json::Value::as_bool).unwrap_or(config.read_only);
    PluginConnectionSummary {
        id: config.id.clone(),
        name: config.name.clone(),
        host: config.host.clone(),
        port: config.port,
        username: config.username.clone(),
        authentication,
        read_only,
    }
}

fn plugin_connection_summaries(
    configs: &[crate::models::connection::ConnectionConfig],
    plugin_id: &str,
) -> Vec<PluginConnectionSummary> {
    let mut summaries: Vec<PluginConnectionSummary> = configs
        .iter()
        .filter(|c| {
            c.db_type == crate::models::connection::DatabaseType::Plugin && c.plugin_id.as_deref() == Some(plugin_id)
        })
        .map(plugin_connection_summary)
        .collect();
    summaries.sort_by_key(|summary| summary.name.to_lowercase());
    summaries
}

/// POST /list-plugin-connections: returns saved connection metadata for one
/// plugin so standalone stdio MCP agents can pick a connection without
/// reading the SQLite store. Metadata only — this route never returns
/// credentials; connecting still goes through /call-plugin-tool, whose
/// lifecycle payload comes from the host-side saved config.
async fn handle_list_plugin_connections(state: &Arc<AppState>, body: &str, stream: &mut tokio::net::TcpStream) {
    let req: ListPluginConnectionsRequest = match serde_json::from_str(body) {
        Ok(r) => r,
        Err(e) => {
            respond_error(stream, "400 Bad Request", &format!("invalid body: {e}")).await;
            return;
        }
    };
    if req.plugin_id.trim().is_empty() {
        respond_error(stream, "400 Bad Request", "plugin_id is required").await;
        return;
    }
    let configs = match state.storage.load_connections().await {
        Ok(c) => c,
        Err(e) => {
            respond_error(stream, "500 Internal Server Error", &e).await;
            return;
        }
    };
    let connections = plugin_connection_summaries(&configs, req.plugin_id.trim());
    respond_json(stream, &serde_json::json!({ "connections": connections })).await;
}

async fn handle_execute_query(app: &AppHandle, state: &Arc<AppState>, body: &str, stream: &mut tokio::net::TcpStream) {
    let req: ExecuteQueryRequest = match serde_json::from_str(body) {
        Ok(r) => r,
        Err(_) => {
            respond(stream, "400 Bad Request", "").await;
            return;
        }
    };
    let config = match resolve_connection(state, req.connection_id.as_deref(), &req.connection_name).await {
        Ok(c) => c,
        Err(e) => {
            respond(stream, "404 Not Found", &e).await;
            return;
        }
    };
    let database = req.database.unwrap_or_else(|| config.database.clone().unwrap_or_default());
    if let Err(error) = ensure_mcp_execute_and_show_supported(&config.db_type) {
        respond(stream, "400 Bad Request", error).await;
        return;
    }
    // Check the complete batch first so a session-changing statement cannot
    // hide a later production write, then recheck every statement immediately
    // before it reaches the database.
    let approved = take_sql_approval(req.approval_token.as_deref(), &config, &database, &req.sql);
    if let Err(e) = ensure_mcp_sql_allowed_with_approval(state, &config, &database, &req.sql, approved).await {
        respond(stream, "403 Forbidden", &e).await;
        return;
    }
    let statements = if config.db_type == crate::models::connection::DatabaseType::SqlServer {
        dbx_core::sql::split_sql_batches(&req.sql)
    } else {
        dbx_core::sql::split_sql_statements_for_database(&req.sql, config.db_type)
    };
    let statements = if statements.is_empty() { vec![req.sql.clone()] } else { statements };
    let mut results = Vec::with_capacity(statements.len());
    for statement in statements {
        let current = match resolve_connection(state, Some(&config.id), &config.name).await {
            Ok(config) => config,
            Err(e) => {
                respond(stream, "403 Forbidden", &e).await;
                return;
            }
        };
        if let Err(e) = ensure_mcp_sql_allowed_with_approval(state, &current, &database, &statement, approved).await {
            respond(stream, "403 Forbidden", &e).await;
            return;
        }
        match dbx_core::query::execute_sql_statement(state, &current.id, &database, &statement, None, None).await {
            Ok(result) => results.push(result),
            Err(e) => {
                respond(stream, "500 Internal Server Error", &format!("QUERY_ERROR: {e}")).await;
                return;
            }
        }
    }
    let event = McpExecuteQueryEvent { connection_id: config.id.clone(), database, sql: req.sql, results };
    let _ = app.emit("mcp-execute-query", &event);
    respond(stream, "200 OK", "ok").await;
}

fn ensure_mcp_execute_and_show_supported(
    database_type: &crate::models::connection::DatabaseType,
) -> Result<(), &'static str> {
    if dbx_core::query_execution_sql::supports_sql_query(*database_type) {
        Ok(())
    } else {
        Err(MCP_EXECUTE_AND_SHOW_SQL_ONLY)
    }
}

async fn handle_list_tables_data(state: &Arc<AppState>, body: &str, stream: &mut tokio::net::TcpStream) {
    let req: ListTablesRequest = match serde_json::from_str(body) {
        Ok(r) => r,
        Err(_) => {
            respond_error(stream, "400 Bad Request", "Invalid JSON").await;
            return;
        }
    };
    let config = match resolve_connection(state, req.connection_id.as_deref(), &req.connection_name).await {
        Ok(c) => c,
        Err(e) => {
            respond_error(stream, "404 Not Found", &e).await;
            return;
        }
    };
    let database = req.database.unwrap_or_else(|| config.database.clone().unwrap_or_default());
    let schema = req.schema.unwrap_or_default();
    if let Err(e) = check_visible_database(&config, &database) {
        respond_error(stream, "403 Forbidden", &e).await;
        return;
    }
    match dbx_core::schema::list_tables_core(state, &config.id, &database, &schema, None, None, None, None, None).await
    {
        Ok(tables) => respond_json(stream, &tables).await,
        Err(e) => respond_error(stream, "500 Internal Server Error", &e).await,
    }
}

async fn handle_describe_table_data(state: &Arc<AppState>, body: &str, stream: &mut tokio::net::TcpStream) {
    let req: DescribeTableRequest = match serde_json::from_str(body) {
        Ok(r) => r,
        Err(_) => {
            respond_error(stream, "400 Bad Request", "Invalid JSON").await;
            return;
        }
    };
    let config = match resolve_connection(state, req.connection_id.as_deref(), &req.connection_name).await {
        Ok(c) => c,
        Err(e) => {
            respond_error(stream, "404 Not Found", &e).await;
            return;
        }
    };
    let database = req.database.unwrap_or_else(|| config.database.clone().unwrap_or_default());
    let schema = req.schema.unwrap_or_default();
    if let Err(e) = check_visible_database(&config, &database) {
        respond_error(stream, "403 Forbidden", &e).await;
        return;
    }
    match dbx_core::schema::get_columns_core(state, &config.id, &database, &schema, &req.table).await {
        Ok(columns) => respond_json(stream, &columns).await,
        Err(e) => respond_error(stream, "500 Internal Server Error", &e).await,
    }
}

async fn handle_mongo_list_collections_data(state: &Arc<AppState>, body: &str, stream: &mut tokio::net::TcpStream) {
    let req: ListTablesRequest = match serde_json::from_str(body) {
        Ok(r) => r,
        Err(_) => {
            respond_error(stream, "400 Bad Request", "Invalid JSON").await;
            return;
        }
    };
    let Some((connection_id, database)) =
        resolve_mongo_target(state, req.connection_id.as_deref(), &req.connection_name, req.database, stream).await
    else {
        return;
    };
    match dbx_core::mongo_ops::mongo_list_collections_core(state, &connection_id, &database).await {
        Ok(collections) => respond_json(stream, &collections).await,
        Err(e) => respond_error(stream, "500 Internal Server Error", &e).await,
    }
}

async fn handle_mongo_find_documents_data(state: &Arc<AppState>, body: &str, stream: &mut tokio::net::TcpStream) {
    let req: MongoFindDocumentsRequest = match serde_json::from_str(body) {
        Ok(r) => r,
        Err(_) => {
            respond_error(stream, "400 Bad Request", "Invalid JSON").await;
            return;
        }
    };
    let Some((connection_id, database)) =
        resolve_mongo_target(state, req.connection_id.as_deref(), &req.connection_name, req.database, stream).await
    else {
        return;
    };
    match dbx_core::mongo_ops::mongo_find_documents_core(
        state,
        &connection_id,
        &database,
        &req.collection,
        req.skip.unwrap_or(0),
        req.limit.unwrap_or(100),
        req.filter.as_deref(),
        req.projection.as_deref(),
        req.sort.as_deref(),
        req.collation.as_deref(),
    )
    .await
    {
        Ok(result) => respond_json(stream, &result).await,
        Err(e) => respond_error(stream, "500 Internal Server Error", &e).await,
    }
}

async fn handle_mongo_count_documents_data(state: &Arc<AppState>, body: &str, stream: &mut tokio::net::TcpStream) {
    let req: MongoCountDocumentsRequest = match serde_json::from_str(body) {
        Ok(r) => r,
        Err(_) => {
            respond_error(stream, "400 Bad Request", "Invalid JSON").await;
            return;
        }
    };
    let Some((connection_id, database)) =
        resolve_mongo_target(state, req.connection_id.as_deref(), &req.connection_name, req.database, stream).await
    else {
        return;
    };
    match dbx_core::mongo_ops::mongo_count_documents_core(
        state,
        &connection_id,
        &database,
        &req.collection,
        req.filter.as_deref(),
        req.mode.as_deref(),
    )
    .await
    {
        Ok(total) => respond_json(stream, &total).await,
        Err(e) => respond_error(stream, "500 Internal Server Error", &e).await,
    }
}

async fn handle_mongo_server_version_data(state: &Arc<AppState>, body: &str, stream: &mut tokio::net::TcpStream) {
    let req: MongoServerVersionRequest = match serde_json::from_str(body) {
        Ok(r) => r,
        Err(_) => {
            respond_error(stream, "400 Bad Request", "Invalid JSON").await;
            return;
        }
    };
    let Some((connection_id, database)) =
        resolve_mongo_target(state, req.connection_id.as_deref(), &req.connection_name, req.database, stream).await
    else {
        return;
    };
    match dbx_core::mongo_ops::mongo_server_version_core(state, &connection_id, &database).await {
        Ok(version) => respond_json(stream, &version).await,
        Err(e) => respond_error(stream, "500 Internal Server Error", &e).await,
    }
}

async fn handle_mongo_collection_stats_data(state: &Arc<AppState>, body: &str, stream: &mut tokio::net::TcpStream) {
    let req: MongoCollectionStatsRequest = match serde_json::from_str(body) {
        Ok(r) => r,
        Err(_) => {
            respond_error(stream, "400 Bad Request", "Invalid JSON").await;
            return;
        }
    };
    let Some((connection_id, database)) =
        resolve_mongo_target(state, req.connection_id.as_deref(), &req.connection_name, req.database, stream).await
    else {
        return;
    };
    match dbx_core::mongo_ops::mongo_collection_stats_core(state, &connection_id, &database, &req.collection, req.scale)
        .await
    {
        Ok(result) => respond_json(stream, &result).await,
        Err(e) => respond_error(stream, "500 Internal Server Error", &e).await,
    }
}

async fn handle_mongo_aggregate_documents_data(state: &Arc<AppState>, body: &str, stream: &mut tokio::net::TcpStream) {
    let req: MongoAggregateDocumentsRequest = match serde_json::from_str(body) {
        Ok(r) => r,
        Err(_) => {
            respond_error(stream, "400 Bad Request", "Invalid JSON").await;
            return;
        }
    };
    let Some((connection_id, database)) =
        resolve_mongo_target(state, req.connection_id.as_deref(), &req.connection_name, req.database, stream).await
    else {
        return;
    };
    if let Err(e) = ensure_mcp_mongo_aggregate_allowed_by_id(state, &connection_id, &database, &req.pipeline_json).await
    {
        respond_error(stream, "403 Forbidden", &e).await;
        return;
    }
    match dbx_core::mongo_ops::mongo_aggregate_documents_core(
        state,
        &connection_id,
        &database,
        &req.collection,
        &req.pipeline_json,
        req.max_rows,
        req.options_json.as_deref(),
    )
    .await
    {
        Ok(result) => respond_json(stream, &result).await,
        Err(e) => respond_error(stream, "500 Internal Server Error", &e).await,
    }
}

async fn handle_mongo_distinct_data(state: &Arc<AppState>, body: &str, stream: &mut tokio::net::TcpStream) {
    let req: MongoDistinctRequest = match serde_json::from_str(body) {
        Ok(r) => r,
        Err(_) => {
            respond_error(stream, "400 Bad Request", "Invalid JSON").await;
            return;
        }
    };
    let Some((connection_id, database)) =
        resolve_mongo_target(state, req.connection_id.as_deref(), &req.connection_name, req.database, stream).await
    else {
        return;
    };
    match dbx_core::mongo_ops::mongo_distinct_core(
        state,
        &connection_id,
        &database,
        &req.collection,
        &req.field,
        req.filter.as_deref(),
    )
    .await
    {
        Ok(result) => respond_json(stream, &result).await,
        Err(e) => respond_error(stream, "500 Internal Server Error", &e).await,
    }
}

async fn handle_mongo_create_index_data(state: &Arc<AppState>, body: &str, stream: &mut tokio::net::TcpStream) {
    let req: MongoCreateIndexRequest = match serde_json::from_str(body) {
        Ok(r) => r,
        Err(_) => {
            respond_error(stream, "400 Bad Request", "Invalid JSON").await;
            return;
        }
    };
    let Some((connection_id, database)) =
        resolve_mongo_target(state, req.connection_id.as_deref(), &req.connection_name, req.database, stream).await
    else {
        return;
    };
    if let Err(e) = ensure_mcp_dangerous_write_allowed_by_id(state, &connection_id, &database, "Create index").await {
        respond_error(stream, "403 Forbidden", &e).await;
        return;
    }
    match dbx_core::mongo_ops::mongo_create_index_core(
        state,
        &connection_id,
        &database,
        &req.collection,
        &req.keys_json,
        req.options_json.as_deref(),
    )
    .await
    {
        Ok(name) => respond_json(stream, &serde_json::json!({ "name": name })).await,
        Err(e) => respond_error(stream, "500 Internal Server Error", &e).await,
    }
}

async fn handle_mongo_drop_indexes_data(state: &Arc<AppState>, body: &str, stream: &mut tokio::net::TcpStream) {
    let req: MongoDropIndexesRequest = match serde_json::from_str(body) {
        Ok(r) => r,
        Err(_) => {
            respond_error(stream, "400 Bad Request", "Invalid JSON").await;
            return;
        }
    };
    let Some((connection_id, database)) =
        resolve_mongo_target(state, req.connection_id.as_deref(), &req.connection_name, req.database, stream).await
    else {
        return;
    };
    if let Err(e) = ensure_mcp_dangerous_write_allowed_by_id(state, &connection_id, &database, "Drop indexes").await {
        respond_error(stream, "403 Forbidden", &e).await;
        return;
    }
    match dbx_core::mongo_ops::mongo_drop_indexes_core(
        state,
        &connection_id,
        &database,
        &req.collection,
        req.indexes_json.as_deref(),
        req.single,
    )
    .await
    {
        Ok(result) => respond_json(stream, &result).await,
        Err(e) => respond_error(stream, "500 Internal Server Error", &e).await,
    }
}

async fn handle_mongo_drop_collection_data(state: &Arc<AppState>, body: &str, stream: &mut tokio::net::TcpStream) {
    let req: MongoDropCollectionRequest = match serde_json::from_str(body) {
        Ok(r) => r,
        Err(_) => {
            respond_error(stream, "400 Bad Request", "Invalid JSON").await;
            return;
        }
    };
    let Some((connection_id, database)) =
        resolve_mongo_target(state, req.connection_id.as_deref(), &req.connection_name, req.database, stream).await
    else {
        return;
    };
    if let Err(e) = ensure_mcp_dangerous_write_allowed_by_id(state, &connection_id, &database, "Drop collection").await
    {
        respond_error(stream, "403 Forbidden", &e).await;
        return;
    }
    match dbx_core::mongo_ops::mongo_drop_collection_core(state, &connection_id, &database, &req.collection).await {
        Ok(()) => respond_json(stream, &serde_json::json!({ "ok": true })).await,
        Err(e) => respond_error(stream, "500 Internal Server Error", &e).await,
    }
}

async fn handle_mongo_insert_documents_data(state: &Arc<AppState>, body: &str, stream: &mut tokio::net::TcpStream) {
    let req: MongoInsertDocumentsRequest = match serde_json::from_str(body) {
        Ok(r) => r,
        Err(_) => {
            respond_error(stream, "400 Bad Request", "Invalid JSON").await;
            return;
        }
    };
    let Some((connection_id, database)) =
        resolve_mongo_target(state, req.connection_id.as_deref(), &req.connection_name, req.database, stream).await
    else {
        return;
    };
    if let Err(e) = ensure_mcp_write_allowed_by_id(state, &connection_id, &database, "Insert").await {
        respond_error(stream, "403 Forbidden", &e).await;
        return;
    }
    match dbx_core::mongo_ops::mongo_insert_documents_core(
        state,
        &connection_id,
        &database,
        &req.collection,
        &req.docs_json,
    )
    .await
    {
        Ok(inserted) => respond_json(stream, &serde_json::json!({ "affected_rows": inserted })).await,
        Err(e) => respond_error(stream, "500 Internal Server Error", &e).await,
    }
}

async fn handle_mongo_update_documents_data(state: &Arc<AppState>, body: &str, stream: &mut tokio::net::TcpStream) {
    let req: MongoUpdateDocumentsRequest = match serde_json::from_str(body) {
        Ok(r) => r,
        Err(_) => {
            respond_error(stream, "400 Bad Request", "Invalid JSON").await;
            return;
        }
    };
    let Some((connection_id, database)) =
        resolve_mongo_target(state, req.connection_id.as_deref(), &req.connection_name, req.database, stream).await
    else {
        return;
    };
    let policy_check = if mongo_filter_is_effectively_unbounded(&req.filter_json) {
        ensure_mcp_dangerous_write_allowed_by_id(state, &connection_id, &database, "Update").await
    } else {
        ensure_mcp_write_allowed_by_id(state, &connection_id, &database, "Update").await
    };
    if let Err(e) = policy_check {
        respond_error(stream, "403 Forbidden", &e).await;
        return;
    }
    match dbx_core::mongo_ops::mongo_update_documents_core(
        state,
        &connection_id,
        &database,
        &req.collection,
        &req.filter_json,
        &req.update_json,
        req.many,
        req.options_json.as_deref(),
    )
    .await
    {
        Ok(modified) => respond_json(stream, &serde_json::json!({ "affected_rows": modified })).await,
        Err(e) => respond_error(stream, "500 Internal Server Error", &e).await,
    }
}

async fn handle_mongo_delete_documents_data(state: &Arc<AppState>, body: &str, stream: &mut tokio::net::TcpStream) {
    let req: MongoDeleteDocumentsRequest = match serde_json::from_str(body) {
        Ok(r) => r,
        Err(_) => {
            respond_error(stream, "400 Bad Request", "Invalid JSON").await;
            return;
        }
    };
    let Some((connection_id, database)) =
        resolve_mongo_target(state, req.connection_id.as_deref(), &req.connection_name, req.database, stream).await
    else {
        return;
    };
    let policy_check = if mongo_filter_is_effectively_unbounded(&req.filter_json) {
        ensure_mcp_dangerous_write_allowed_by_id(state, &connection_id, &database, "Delete").await
    } else {
        ensure_mcp_write_allowed_by_id(state, &connection_id, &database, "Delete").await
    };
    if let Err(e) = policy_check {
        respond_error(stream, "403 Forbidden", &e).await;
        return;
    }
    match dbx_core::mongo_ops::mongo_delete_documents_core(
        state,
        &connection_id,
        &database,
        &req.collection,
        &req.filter_json,
        req.many,
    )
    .await
    {
        Ok(deleted) => respond_json(stream, &serde_json::json!({ "affected_rows": deleted })).await,
        Err(e) => respond_error(stream, "500 Internal Server Error", &e).await,
    }
}

async fn handle_redis_execute_command_data(state: &Arc<AppState>, body: &str, stream: &mut tokio::net::TcpStream) {
    let req: RedisCommandRequest = match serde_json::from_str(body) {
        Ok(r) => r,
        Err(_) => {
            respond_error(stream, "400 Bad Request", "Invalid JSON").await;
            return;
        }
    };
    let config = match resolve_connection(state, req.connection_id.as_deref(), &req.connection_name).await {
        Ok(c) => c,
        Err(e) => {
            respond_error(stream, "404 Not Found", &e).await;
            return;
        }
    };
    let database = req.db.to_string();
    if let Err(e) = check_visible_database(&config, &database) {
        respond_error(stream, "403 Forbidden", &e).await;
        return;
    }
    let argv = match dbx_core::db::redis_driver::parse_command_argv(&req.command) {
        Ok(argv) => argv,
        Err(error) => {
            respond_error(stream, "400 Bad Request", &format!("Invalid Redis command: {error}")).await;
            return;
        }
    };
    let cmd_name = String::from_utf8_lossy(&argv[0]).to_ascii_uppercase();
    let safety = dbx_core::db::redis_driver::classify_command(&cmd_name);
    let centrally_approved_high_risk = safety == dbx_core::db::redis_driver::RedisCommandSafety::Blocked;
    if safety != dbx_core::db::redis_driver::RedisCommandSafety::Allowed {
        let policy_check = if centrally_approved_high_risk {
            ensure_mcp_write_allowed_with_risk(state, &config, &database, &format!("Redis command '{cmd_name}'"), true)
                .await
        } else {
            ensure_mcp_write_allowed(state, &config, &database, &format!("Redis command '{cmd_name}'")).await
        };
        if let Err(e) = policy_check {
            respond_error(stream, "403 Forbidden", &e).await;
            return;
        }
    }
    match dbx_core::redis_ops::redis_execute_command_core(
        state,
        &config.id,
        req.db,
        &req.command,
        centrally_approved_high_risk,
    )
    .await
    {
        Ok(result) => respond_json(stream, &result).await,
        Err(e) => respond_error(stream, "500 Internal Server Error", &e).await,
    }
}

async fn handle_execute_query_data(state: &Arc<AppState>, body: &str, stream: &mut tokio::net::TcpStream) {
    let req: ExecuteQueryRequest = match serde_json::from_str(body) {
        Ok(r) => r,
        Err(_) => {
            respond_error(stream, "400 Bad Request", "Invalid JSON").await;
            return;
        }
    };
    let config = match resolve_connection(state, req.connection_id.as_deref(), &req.connection_name).await {
        Ok(c) => c,
        Err(e) => {
            respond_error(stream, "404 Not Found", &e).await;
            return;
        }
    };
    let database = req.database.unwrap_or_else(|| config.database.clone().unwrap_or_default());
    if let Err(e) = check_visible_database(&config, &database) {
        respond_error(stream, "403 Forbidden", &e).await;
        return;
    }
    if let Err(e) = ensure_mcp_sql_allowed(state, &config, &database, &req.sql).await {
        respond_error(stream, "403 Forbidden", &e).await;
        return;
    }
    let statements = if config.db_type == crate::models::connection::DatabaseType::SqlServer {
        dbx_core::sql::split_sql_batches(&req.sql)
    } else {
        dbx_core::sql::split_sql_statements_for_database(&req.sql, config.db_type)
    };
    let statements = if statements.is_empty() { vec![req.sql] } else { statements };
    let mut last_result = None;
    for statement in statements {
        // Re-read both policy and connection settings immediately before every
        // statement so a settings change can stop the remainder of the batch.
        let current = match resolve_connection(state, Some(&config.id), &config.name).await {
            Ok(config) => config,
            Err(e) => {
                respond_error(stream, "403 Forbidden", &e).await;
                return;
            }
        };
        if let Err(e) = ensure_mcp_sql_allowed(state, &current, &database, &statement).await {
            respond_error(stream, "403 Forbidden", &e).await;
            return;
        }
        match dbx_core::query::execute_sql_statement(
            state,
            &current.id,
            &database,
            &statement,
            req.schema.as_deref(),
            None,
        )
        .await
        {
            Ok(result) => last_result = Some(result),
            Err(e) => {
                respond_error(stream, "500 Internal Server Error", &e).await;
                return;
            }
        }
    }
    if let Some(result) = last_result {
        respond_json(stream, &result).await;
    } else {
        respond_error(stream, "500 Internal Server Error", "No SQL statement to execute").await;
    }
}