use anyhow::{Context, Result};
use chrono::Utc;
use serde::{Deserialize, Serialize};
use std::path::Path;
use std::sync::OnceLock;
use surrealdb::RecordId as SurrealRecordId;
use surrealdb::Surreal;
use surrealdb::engine::local::SurrealKv;
use surrealdb::engine::remote::ws::{Client as WsClient, Ws, Wss};
use surrealdb::opt::auth::{Database, Namespace, Root};
use surrealdb::sql::{Thing, Value};
use tokio::runtime::Runtime;

use crate::knowledge::KnowledgeEntry;
use crate::store::KnowledgeStore;
use crate::types::{
    Agent, ApplicabilityType, Category, ContentType, EntryType, Project, Relationship,
    RelationshipType, Session, SessionType, SourceType,
};

// ============================================================================
// CONNECTION MODE CONFIGURATION
// ============================================================================

/// Connection mode for SurrealDB
#[derive(Debug, Clone, PartialEq, Default)]
pub enum SurrealMode {
    /// Embedded SurrealKV (local file-based, default)
    #[default]
    Embedded,
    /// Network connection via WebSocket
    Network,
}

/// Authentication level for SurrealDB signin
#[derive(Debug, Clone, PartialEq, Default)]
pub enum AuthLevel {
    /// Root-level authentication (default)
    #[default]
    Root,
    /// Namespace-level authentication
    Namespace,
    /// Database-level authentication
    Database,
}

impl AuthLevel {
    /// Parse an auth level from an environment variable string.
    ///
    /// Accepts lowercase aliases:
    /// - "root" -> Root
    /// - "namespace" or "ns" -> Namespace
    /// - "database" or "db" -> Database
    pub fn from_env_str(s: &str) -> Result<Self> {
        match s.to_lowercase().as_str() {
            "root" => Ok(Self::Root),
            "namespace" | "ns" => Ok(Self::Namespace),
            "database" | "db" => Ok(Self::Database),
            other => anyhow::bail!(
                "Unknown MX_SURREAL_AUTH_LEVEL '{}'. Valid values: root, namespace (ns), database (db)",
                other
            ),
        }
    }
}

impl std::fmt::Display for AuthLevel {
    fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
        match self {
            Self::Root => write!(f, "root"),
            Self::Namespace => write!(f, "namespace"),
            Self::Database => write!(f, "database"),
        }
    }
}

/// Configuration for SurrealDB connection
///
/// Parsed from environment variables:
/// - `MX_SURREAL_MODE`: "embedded" (default) or "network"
/// - `MX_SURREAL_URL`: WebSocket URL for network mode (default: ws://localhost:8000)
/// - `MX_SURREAL_USER`: Username for network auth (default: root)
/// - `MX_SURREAL_PASS`: Password for network auth (direct value)
/// - `MX_SURREAL_PASS_FILE`: Path to file containing password (e.g., agenix secret)
/// - `MX_SURREAL_AUTH_LEVEL`: Auth level for signin: "root" (default), "namespace"/"ns", or "database"/"db"
/// - `MX_SURREAL_NS`: Namespace (default: memory)
/// - `MX_SURREAL_DB`: Database name (default: knowledge)
#[derive(Debug, Clone)]
pub struct SurrealConfig {
    /// Connection mode
    pub mode: SurrealMode,
    /// WebSocket URL for network mode
    pub url: String,
    /// Username for network authentication
    pub user: String,
    /// Password for network authentication
    pub pass: Option<String>,
    /// SurrealDB namespace
    pub namespace: String,
    /// SurrealDB database name
    pub database: String,
    /// Auth level for signin (root, namespace, or database)
    pub auth_level: AuthLevel,
}

impl Default for SurrealConfig {
    fn default() -> Self {
        Self {
            mode: SurrealMode::Embedded,
            url: "ws://localhost:8000".to_string(),
            user: "root".to_string(),
            pass: None,
            namespace: "memory".to_string(),
            database: "knowledge".to_string(),
            auth_level: AuthLevel::Root,
        }
    }
}

impl SurrealConfig {
    /// Parse configuration from environment variables
    pub fn from_env() -> Self {
        let mode = match std::env::var("MX_SURREAL_MODE")
            .unwrap_or_default()
            .to_lowercase()
            .as_str()
        {
            "network" => SurrealMode::Network,
            _ => SurrealMode::Embedded,
        };

        let url =
            std::env::var("MX_SURREAL_URL").unwrap_or_else(|_| "ws://localhost:8000".to_string());

        let user = std::env::var("MX_SURREAL_USER").unwrap_or_else(|_| "root".to_string());

        // Get password: try direct value first, then file path, filter empty strings
        let pass = std::env::var("MX_SURREAL_PASS")
            .ok()
            .or_else(|| {
                // Try reading from file path (e.g., agenix secret)
                std::env::var("MX_SURREAL_PASS_FILE")
                    .ok()
                    .and_then(|path| std::fs::read_to_string(path).ok())
            })
            .map(|s| s.trim().to_string())
            .filter(|p| !p.is_empty());

        let namespace = std::env::var("MX_SURREAL_NS").unwrap_or_else(|_| "memory".to_string());

        let database = std::env::var("MX_SURREAL_DB").unwrap_or_else(|_| "knowledge".to_string());

        let auth_level_str =
            std::env::var("MX_SURREAL_AUTH_LEVEL").unwrap_or_else(|_| "root".to_string());
        let auth_level = AuthLevel::from_env_str(&auth_level_str).unwrap_or_else(|e| {
            eprintln!("[mx] WARNING: {e}, defaulting to root");
            AuthLevel::Root
        });

        Self {
            mode,
            url,
            user,
            pass,
            namespace,
            database,
            auth_level,
        }
    }

    /// Check if we're in network mode
    pub fn is_network(&self) -> bool {
        self.mode == SurrealMode::Network
    }
}

/// Embedded SurrealDB schema - applied on database open
const SCHEMA: &str = include_str!("../schema/surrealdb-schema.surql");

/// Tag record for SurrealDB
#[derive(Debug, Clone, Serialize, Deserialize)]
pub struct Tag {
    pub name: String,
    #[serde(skip_serializing_if = "Option::is_none")]
    pub created_at: Option<String>,
}

/// DTO for deserializing knowledge records from SurrealDB queries.
///
/// SurrealDB returns record links as `Thing` types, which don't deserialize
/// to serde_json::Value properly. This DTO expects queries to use:
///   - `meta::id(id) AS id` for the record ID
///   - `meta::id(category) AS category_id` for record links
///   - `<string>created_at AS created_at` for datetime conversion
///
/// This allows direct deserialization without manual JSON field extraction.
#[derive(Debug, Clone, Serialize, Deserialize)]
pub struct SurrealKnowledgeRecord {
    /// Record ID (from `meta::id(id)`)
    pub id: String,

    /// Entry title
    pub title: String,

    /// Full body content
    #[serde(default)]
    pub body: Option<String>,

    /// Brief summary
    #[serde(default)]
    pub summary: Option<String>,

    /// Source file path (for markdown-sourced entries)
    #[serde(default)]
    pub file_path: Option<String>,

    /// Content hash for change detection
    #[serde(default)]
    pub content_hash: Option<String>,

    /// Whether this is ephemeral/session-scoped
    #[serde(default)]
    pub ephemeral: bool,

    /// Owner ID for private entries
    #[serde(default)]
    pub owner: Option<String>,

    /// Visibility: "public" or "private"
    #[serde(default = "default_visibility")]
    pub visibility: String,

    // === Record links (converted to strings via meta::id()) ===
    /// Category ID (from `meta::id(category)`)
    pub category_id: String,

    /// Source type ID (from `meta::id(source_type)`)
    #[serde(default)]
    pub source_type_id: Option<String>,

    /// Entry type ID (from `meta::id(entry_type)`)
    #[serde(default)]
    pub entry_type_id: Option<String>,

    /// Content type ID (from `meta::id(content_type)`)
    #[serde(default)]
    pub content_type_id: Option<String>,

    /// Source project ID
    #[serde(default)]
    pub source_project_id: Option<String>,

    /// Source agent ID
    #[serde(default)]
    pub source_agent_id: Option<String>,

    /// Session ID
    #[serde(default)]
    pub session_id: Option<String>,

    // === Timestamps (converted to strings via <string>cast) ===
    /// Created timestamp (from `<string>created_at`)
    #[serde(default)]
    pub created_at: Option<String>,

    /// Updated timestamp (from `<string>updated_at`)
    #[serde(default)]
    pub updated_at: Option<String>,

    // === Resonance fields (for wake-up cascade) ===
    /// Resonance level (1-10, with overflow for transcendent)
    #[serde(default)]
    pub resonance: i32,

    /// Resonance type: foundational, transformative, relational, operational, ephemeral
    #[serde(default)]
    pub resonance_type: Option<String>,

    /// Last activated timestamp
    #[serde(default)]
    pub last_activated: Option<String>,

    /// Number of times activated
    #[serde(default)]
    pub activation_count: i32,

    /// Decay rate (0.0-1.0)
    #[serde(default)]
    pub decay_rate: f64,

    /// Anchor IDs (related blooms this connects to)
    #[serde(default)]
    pub anchors: Vec<String>,

    // Issue #72: Multiple wake phrases
    #[serde(default)]
    pub wake_phrases: Vec<String>,

    // Issue #73: Custom wake order
    #[serde(default)]
    pub wake_order: Option<i32>,

    /// DEPRECATED: Wake phrase for memory ritual verification (kept for backward compat)
    #[serde(default)]
    pub wake_phrase: Option<String>,

    // === Vector embeddings (PR #89) ===
    /// 768-dimensional embedding vector (BGE-Base-EN-v1.5)
    #[serde(default)]
    pub embedding: Option<Vec<f32>>,

    /// Model ID that generated the embedding
    #[serde(default)]
    pub embedding_model: Option<String>,

    /// Timestamp when embedded
    #[serde(default)]
    pub embedded_at: Option<String>,

    // === Stele encoding (Issue #122) ===
    /// Content format: markdown (default), json, stele:markdown, stele:ascii, stele:light, stele:full
    #[serde(default = "default_format")]
    pub format: String,
}

fn default_visibility() -> String {
    "public".to_string()
}

fn default_format() -> String {
    "markdown".to_string()
}

impl SurrealKnowledgeRecord {
    /// Convert to domain KnowledgeEntry, fetching tags and applicability
    pub fn into_knowledge_entry(
        self,
        tags: Vec<String>,
        applicability: Vec<String>,
    ) -> KnowledgeEntry {
        KnowledgeEntry {
            id: format!("kn-{}", self.id),
            category_id: self.category_id,
            title: self.title,
            body: self.body,
            summary: self.summary,
            file_path: self.file_path,
            content_hash: self.content_hash,
            ephemeral: self.ephemeral,
            owner: self.owner,
            visibility: self.visibility,
            source_type_id: self.source_type_id,
            entry_type_id: self.entry_type_id,
            content_type_id: self.content_type_id,
            source_project_id: self.source_project_id,
            source_agent_id: self.source_agent_id,
            session_id: self.session_id,
            created_at: self.created_at,
            updated_at: self.updated_at,
            tags,
            applicability,
            resonance: self.resonance,
            resonance_type: self.resonance_type,
            last_activated: self.last_activated,
            activation_count: self.activation_count,
            decay_rate: self.decay_rate,
            anchors: self.anchors,
            wake_phrases: self.wake_phrases,
            wake_order: self.wake_order,
            wake_phrase: self.wake_phrase,
            embedding: self.embedding,
            embedding_model: self.embedding_model,
            embedded_at: self.embedded_at,
            format: self.format,
            effective_resonance: None,
        }
    }
}

/// SurrealDB Thing wrapper for typed record IDs
#[derive(Debug, Clone)]
pub(crate) struct RecordId(Thing);

impl RecordId {
    fn new(table: &str, id: &str) -> Self {
        Self(Thing::from((table, id)))
    }

    fn as_thing(&self) -> &Thing {
        &self.0
    }

    fn into_thing(self) -> Thing {
        self.0
    }

    fn to_record_id(&self) -> SurrealRecordId {
        SurrealRecordId::from((self.0.tb.as_str(), self.0.id.to_string().as_str()))
    }
}

/// Normalize datetime string to RFC3339 format for SurrealDB
fn normalize_datetime(s: &str) -> String {
    // If already looks like RFC3339 (has T and timezone), return as-is
    if s.contains('T') && (s.ends_with('Z') || s.contains('+') || s.contains("-0")) {
        return s.to_string();
    }

    // Space-separated format: "2025-11-29 08:10:33" -> "2025-11-29T08:10:33Z"
    if s.contains(' ') && !s.contains('T') {
        return s.replace(' ', "T") + "Z";
    }

    // Fallback: assume it's already good or add Z
    if !s.ends_with('Z') && !s.contains('+') {
        return format!("{}Z", s);
    }

    s.to_string()
}

/// Connection abstraction for SurrealDB - supports both embedded and network modes
pub enum SurrealConnection {
    /// Embedded SurrealKV database (local file-based)
    Embedded(Surreal<surrealdb::engine::local::Db>),
    /// Network connection via WebSocket
    Network(Surreal<WsClient>),
}

/// SurrealDB-backed knowledge store
pub struct SurrealDatabase {
    conn: SurrealConnection,
}

/// Macro to execute code with the appropriate database connection (embedded or network)
///
/// This macro handles the connection type dispatch, allowing the same query code
/// to work with both embedded (SurrealKV) and network (WebSocket) connections.
///
/// # Usage
/// ```rust,ignore
/// with_db!(self, db, {
///     db.query(&sql).bind(("key", value)).await?
/// })
/// ```
macro_rules! with_db {
    ($self:expr, $db:ident, $body:expr) => {
        match &$self.conn {
            SurrealConnection::Embedded($db) => $body,
            SurrealConnection::Network($db) => $body,
        }
    };
}

impl SurrealDatabase {}

impl SurrealDatabase {
    /// Get or initialize the global tokio runtime
    fn runtime() -> &'static Runtime {
        static RT: OnceLock<Runtime> = OnceLock::new();
        RT.get_or_init(|| Runtime::new().expect("Failed to create tokio runtime"))
    }

    /// Open database at path, create if not exists, apply schema
    ///
    /// This method checks environment variables first - if `MX_SURREAL_MODE=network`,
    /// the path is ignored and a network connection is established instead.
    pub fn open<P: AsRef<Path>>(path: P) -> Result<Self> {
        let config = SurrealConfig::from_env();
        Self::runtime().block_on(Self::open_with_config_async(path, &config, false))
    }

    /// Open database with verbose control
    pub fn open_with_verbose<P: AsRef<Path>>(path: P, verbose: bool) -> Result<Self> {
        let config = SurrealConfig::from_env();
        Self::runtime().block_on(Self::open_with_config_async(path, &config, verbose))
    }

    /// Connect using explicit configuration
    ///
    /// For embedded mode, `path` specifies the database location.
    /// For network mode, `path` is ignored.
    pub fn connect<P: AsRef<Path>>(path: P, config: &SurrealConfig) -> Result<Self> {
        Self::runtime().block_on(Self::open_with_config_async(path, config, false))
    }

    /// Internal: open with config, branching on mode
    async fn open_with_config_async<P: AsRef<Path>>(
        path: P,
        config: &SurrealConfig,
        verbose: bool,
    ) -> Result<Self> {
        match config.mode {
            SurrealMode::Embedded => Self::open_embedded_async(path, config, verbose).await,
            SurrealMode::Network => Self::open_network_async(config, verbose).await,
        }
    }

    /// Open embedded SurrealKV database
    async fn open_embedded_async<P: AsRef<Path>>(
        path: P,
        config: &SurrealConfig,
        verbose: bool,
    ) -> Result<Self> {
        let path = path.as_ref();

        // Diagnostic: Log connection mode (only if verbose)
        if verbose {
            eprintln!(
                "[mx] Connecting to SurrealDB in embedded mode: {}",
                path.display()
            );
        }

        // Create parent directory if needed
        if let Some(parent) = path.parent() {
            std::fs::create_dir_all(parent)
                .with_context(|| format!("Failed to create database directory: {:?}", parent))?;
        }

        // Connect to SurrealKv backend
        let db = Surreal::new::<SurrealKv>(path).await.with_context(|| {
            format!(
                "Failed to open SurrealDB at {} (check file permissions and disk space)",
                path.display()
            )
        })?;

        // Use namespace and database from config
        if verbose {
            eprintln!(
                "[mx] Using namespace '{}' and database '{}'",
                config.namespace, config.database
            );
        }
        db.use_ns(&config.namespace)
            .use_db(&config.database)
            .await
            .context("Failed to set namespace and database")?;

        // Apply schema (idempotent)
        if verbose {
            eprintln!("[mx] Applying database schema");
        }
        let mut response = db
            .query(SCHEMA)
            .await
            .context("Failed to apply database schema")?;

        // Check for errors - schema application returns multiple results
        let errors = response.take_errors();
        if !errors.is_empty() {
            return Err(anyhow::anyhow!("Schema application failed: {:?}", errors));
        }

        if verbose {
            eprintln!("[mx] Embedded connection established successfully");
        }

        Ok(Self {
            conn: SurrealConnection::Embedded(db),
        })
    }

    /// Check if URL is localhost (safe for unencrypted traffic)
    fn is_localhost_url(url: &str) -> bool {
        url.contains("://localhost") || url.contains("://127.0.0.1") || url.contains("://[::1]")
    }

    /// Strip protocol prefix from WebSocket URL
    ///
    /// The surrealdb crate expects just `host:port`, not `ws://host:port`.
    /// Users may provide the full URL with protocol, so we strip it if present.
    fn sanitize_ws_url(url: &str) -> String {
        url.strip_prefix("ws://")
            .or_else(|| url.strip_prefix("wss://"))
            .unwrap_or(url)
            .to_string()
    }

    /// Open network connection via WebSocket
    ///
    /// Authenticates with the remote SurrealDB server using credentials from config.
    async fn open_network_async(config: &SurrealConfig, verbose: bool) -> Result<Self> {
        // Diagnostic: Log connection attempt (to stderr, doesn't interfere with stdout)
        if verbose {
            eprintln!(
                "[mx] Connecting to SurrealDB in network mode: {}",
                config.url
            );
        }

        // Security warning: credentials over unencrypted WebSocket to non-localhost
        // (Always show warnings, regardless of verbose flag)
        if config.pass.is_some()
            && config.url.starts_with("ws://")
            && !Self::is_localhost_url(&config.url)
        {
            eprintln!(
                "[mx] WARNING: Sending credentials over unencrypted WebSocket to {}",
                config.url
            );
            eprintln!("[mx] WARNING: Consider using wss:// (TLS) for secure authentication");
        }

        // Strip protocol prefix from URL - surrealdb crate expects just host:port.
        // We lose the scheme in the sanitized form, so detect it up front to
        // pick the right engine (plain Ws vs TLS Wss).
        let is_tls = config.url.starts_with("wss://");
        let sanitized_url = Self::sanitize_ws_url(&config.url);

        // Connect to remote SurrealDB via WebSocket. Dispatch on scheme:
        // `Ws` speaks plain WebSocket; `Wss` is the TLS variant (enabled by
        // the `rustls` feature on the surrealdb crate). Using the wrong one
        // fails with a cryptic "HTTP version must be 1.1 or higher" because
        // the TLS handshake bytes get parsed as HTTP.
        let db = if is_tls {
            Surreal::new::<Wss>(sanitized_url.as_str())
                .await
                .with_context(|| {
                    format!(
                        "Failed to connect to SurrealDB at {} (check that server is running and URL is correct)",
                        config.url
                    )
                })?
        } else {
            Surreal::new::<Ws>(sanitized_url.as_str())
                .await
                .with_context(|| {
                    format!(
                        "Failed to connect to SurrealDB at {} (check that server is running and URL is correct)",
                        config.url
                    )
                })?
        };

        // Authenticate with the server
        // If no password is provided, attempt connection without auth (will fail if server requires it)
        if let Some(pass) = &config.pass {
            if verbose {
                eprintln!(
                    "[mx] Authenticating as user '{}' (auth level: {})",
                    config.user, config.auth_level
                );
            }
            match config.auth_level {
                AuthLevel::Namespace => {
                    db.signin(Namespace {
                        namespace: &config.namespace,
                        username: &config.user,
                        password: pass,
                    })
                    .await
                    .with_context(|| {
                        format!(
                            "Failed to authenticate to SurrealDB at {} as namespace-level user '{}' in namespace '{}' (check credentials in MX_SURREAL_USER and MX_SURREAL_PASS)",
                            config.url, config.user, config.namespace
                        )
                    })?;
                }
                AuthLevel::Database => {
                    db.signin(Database {
                        namespace: &config.namespace,
                        database: &config.database,
                        username: &config.user,
                        password: pass,
                    })
                    .await
                    .with_context(|| {
                        format!(
                            "Failed to authenticate to SurrealDB at {} as database-level user '{}' in namespace '{}' database '{}' (check credentials in MX_SURREAL_USER and MX_SURREAL_PASS)",
                            config.url, config.user, config.namespace, config.database
                        )
                    })?;
                }
                AuthLevel::Root => {
                    db.signin(Root {
                        username: &config.user,
                        password: pass,
                    })
                    .await
                    .with_context(|| {
                        format!(
                            "Failed to authenticate to SurrealDB at {} as user '{}' (check credentials in MX_SURREAL_USER and MX_SURREAL_PASS)",
                            config.url, config.user
                        )
                    })?;
                }
            }
        } else if verbose {
            eprintln!("[mx] No password provided, connecting without authentication");
        }

        // Use namespace and database from config
        if verbose {
            eprintln!(
                "[mx] Using namespace '{}' and database '{}'",
                config.namespace, config.database
            );
        }
        db.use_ns(&config.namespace)
            .use_db(&config.database)
            .await
            .with_context(|| {
                format!(
                    "Failed to set namespace '{}' and database '{}' (check that they exist on the server)",
                    config.namespace, config.database
                )
            })?;

        if verbose {
            eprintln!("[mx] Network connection established successfully");
        }

        // Note: Schema is NOT applied for network mode
        // The remote server should already have the schema
        // (Schema is applied via NixOS module or manual setup)

        Ok(Self {
            conn: SurrealConnection::Network(db),
        })
    }

    /// Legacy async open - kept for compatibility
    async fn open_async<P: AsRef<Path>>(path: P) -> Result<Self> {
        let config = SurrealConfig::from_env();
        Self::open_with_config_async(path, &config, false).await
    }

    /// Test helper - open temporary database
    ///
    /// Forces embedded mode regardless of `MX_SURREAL_MODE` env var,
    /// so tests never hit the live database.
    #[cfg(test)]
    pub fn open_in_memory() -> Result<Self> {
        use tempfile::tempdir;

        let temp_dir = tempdir()?;
        let config = SurrealConfig::default(); // always Embedded
        Self::connect(temp_dir.path(), &config)
    }

    /// Get reference to underlying Surreal instance (embedded only)
    ///
    /// Returns `None` if called on a network connection.
    /// Prefer using connection-agnostic methods instead.
    #[deprecated(note = "Use connection-agnostic methods instead")]
    pub fn inner(&self) -> Option<&Surreal<surrealdb::engine::local::Db>> {
        match &self.conn {
            SurrealConnection::Embedded(db) => Some(db),
            SurrealConnection::Network(_) => None,
        }
    }

    /// Build standard knowledge entry SELECT fields
    fn knowledge_select_fields() -> &'static str {
        "meta::id(id) AS id, title, body, summary, file_path, content_hash, ephemeral,
        owner, visibility,
        meta::id(category) AS category_id,
        meta::id(source_type) AS source_type_id,
        meta::id(entry_type) AS entry_type_id,
        meta::id(content_type) AS content_type_id,
        IF source_project THEN meta::id(source_project) ELSE null END AS source_project_id,
        IF source_agent THEN meta::id(source_agent) ELSE null END AS source_agent_id,
        IF session THEN meta::id(session) ELSE null END AS session_id,
        <string>created_at AS created_at, <string>updated_at AS updated_at,
        IF resonance THEN resonance ELSE 0 END AS resonance,
        IF resonance_type THEN <string>resonance_type ELSE null END AS resonance_type,
        IF last_activated THEN <string>last_activated ELSE null END AS last_activated,
        IF activation_count THEN activation_count ELSE 0 END AS activation_count,
        IF decay_rate THEN decay_rate ELSE 0.0 END AS decay_rate,
        IF anchors THEN anchors ELSE [] END AS anchors,
        IF wake_phrases THEN wake_phrases ELSE [] END AS wake_phrases,
        IF wake_order THEN wake_order ELSE null END AS wake_order,
        IF wake_phrase THEN wake_phrase ELSE null END AS wake_phrase,
        IF embedding THEN embedding ELSE null END AS embedding,
        IF embedding_model THEN embedding_model ELSE null END AS embedding_model,
        IF embedded_at THEN <string>embedded_at ELSE null END AS embedded_at,
        IF format THEN format ELSE 'markdown' END AS format"
    }

    /// Build visibility filter for privacy-aware queries
    fn build_visibility_filter(ctx: &crate::store::AgentContext) -> (String, Option<String>) {
        if ctx.include_private {
            if let Some(ref agent) = ctx.agent_id {
                (
                    "AND ((visibility = 'public') OR (visibility = 'private' AND owner = $current_agent))".to_string(),
                    Some(agent.clone())
                )
            } else {
                ("AND (visibility = 'public')".to_string(), None)
            }
        } else {
            ("AND (visibility = 'public')".to_string(), None)
        }
    }

    /// Returns the SurrealQL expression for computing effective_resonance with tiered decay.
    /// Single source of truth for the decay formula.
    ///
    /// Tiered decay rates:
    ///   resonance <= 3  -> 10%/week (base 0.90)
    ///   resonance 4-5   -> 5%/week  (base 0.95)
    ///   resonance 6+    -> 2.5%/week (base 0.975)
    /// foundational/transformative entries are exempt from decay (effective_resonance = resonance).
    ///
    /// All other resonance types -- including `session`, `ephemeral`, `relational`,
    /// and `operational` -- are subject to decay. `session` entries intentionally decay
    /// like ephemeral: they represent per-session context that should lose salience over
    /// time rather than persist at full resonance indefinitely.
    fn effective_resonance_expr() -> &'static str {
        "IF resonance_type IN ['foundational', 'transformative'] THEN resonance \
         ELSE resonance * math::pow(\
             IF resonance <= 3 THEN 0.90 \
             ELSE IF resonance <= 5 THEN 0.95 \
             ELSE 0.975 \
             END, \
             duration::days(time::now() - (last_activated ?? created_at)) / 7.0\
         ) \
         END"
    }

    /// Build resonance filter clauses using computed effective_resonance.
    /// Tiered decay rates:
    ///   resonance <= 3  -> 10%/week (base 0.90)
    ///   resonance 4-5   -> 5%/week  (base 0.95)
    ///   resonance 6+    -> 2.5%/week (base 0.975)
    /// foundational/transformative entries are exempt from decay.
    fn build_resonance_filter(filter: &crate::store::KnowledgeFilter) -> String {
        // SurrealDB doesn't support LET in WHERE, so we expand the expression directly.
        let effective_resonance_expr = Self::effective_resonance_expr();

        let mut clauses = Vec::new();

        if let Some(min) = filter.min_resonance {
            clauses.push(format!("({}) >= {}", effective_resonance_expr, min));
        }

        if let Some(max) = filter.max_resonance {
            clauses.push(format!("({}) <= {}", effective_resonance_expr, max));
        }

        if clauses.is_empty() {
            String::new()
        } else {
            format!("AND ({})", clauses.join(" AND "))
        }
    }

    /// Validate category name to prevent SQL injection
    /// Only allows alphanumeric characters, underscores, and hyphens
    fn is_valid_category_name(name: &str) -> bool {
        !name.is_empty()
            && name.len() <= 64
            && name
                .chars()
                .all(|c| c.is_ascii_alphanumeric() || c == '_' || c == '-')
    }

    /// Build category filter clauses
    /// Category names are validated to prevent SQL injection
    fn build_category_filter(filter: &crate::store::KnowledgeFilter) -> String {
        match &filter.categories {
            Some(cats) if !cats.is_empty() => {
                // Filter out invalid category names to prevent injection
                let valid_cats: Vec<&String> = cats
                    .iter()
                    .filter(|c| Self::is_valid_category_name(c))
                    .collect();

                if valid_cats.is_empty() {
                    return String::new();
                }

                if valid_cats.len() == 1 {
                    format!(
                        "AND category = type::thing('category', '{}')",
                        valid_cats[0]
                    )
                } else {
                    // Multiple categories: use IN clause
                    let quoted: Vec<String> = valid_cats
                        .iter()
                        .map(|c| format!("type::thing('category', '{}')", c))
                        .collect();
                    format!("AND category IN [{}]", quoted.join(", "))
                }
            }
            _ => String::new(),
        }
    }

    // =========================================================================
    // KNOWLEDGE CRUD OPERATIONS
    // =========================================================================

    /// Upsert a knowledge entry with tags and applicability edges (returns RecordId)
    pub fn upsert_knowledge_internal(&self, entry: &KnowledgeEntry) -> Result<RecordId> {
        Self::runtime().block_on(self.upsert_knowledge_async(entry))
    }

    async fn upsert_knowledge_async(&self, entry: &KnowledgeEntry) -> Result<RecordId> {
        // Extract ID from "kn-xxxxx" format
        let id_part = entry.id.strip_prefix("kn-").unwrap_or(&entry.id);
        let record_id = RecordId::new("knowledge", id_part);

        // Build base query with required fields
        let mut query = "UPSERT type::thing('knowledge', $id) SET
            title = $title,
            body = $body,
            summary = $summary,
            file_path = $file_path,
            content_hash = $content_hash,
            ephemeral = $ephemeral,
            owner = $owner,
            visibility = $visibility,
            category = type::thing('category', $category_id),
            source_type = type::thing('source_type', $source_type_id),
            entry_type = type::thing('entry_type', $entry_type_id),
            content_type = type::thing('content_type', $content_type_id),
            resonance = $resonance,
            resonance_type = $resonance_type,
            activation_count = $activation_count,
            decay_rate = $decay_rate,
            anchors = $anchors,
            wake_phrases = $wake_phrases,
            wake_order = $wake_order,
            wake_phrase = $wake_phrase,
            embedding = $embedding,
            embedding_model = $embedding_model,
            format = $format"
            .to_string();

        // Add optional fields
        if entry.source_project_id.is_some() {
            query.push_str(", source_project = type::thing('project', $source_project_id)");
        }
        if entry.source_agent_id.is_some() {
            query.push_str(", source_agent = type::thing('agent', $source_agent_id)");
        }
        if entry.session_id.is_some() {
            query.push_str(", session = type::thing('session', $session_id)");
        }
        if entry.created_at.is_some() {
            query.push_str(", created_at = <datetime>$created_at");
        }
        if entry.updated_at.is_some() {
            query.push_str(", updated_at = <datetime>$updated_at");
        }
        if entry.last_activated.is_some() {
            query.push_str(", last_activated = <datetime>$last_activated");
        }
        if entry.embedded_at.is_some() {
            query.push_str(", embedded_at = <datetime>$embedded_at");
        }

        // Bind all parameters and execute query
        let mut response = with_db!(self, db, {
            let mut q = db
                .query(&query)
                .bind(("id", id_part.to_string()))
                .bind(("title", entry.title.clone()))
                .bind(("body", entry.body.clone()))
                .bind(("summary", entry.summary.clone()))
                .bind(("file_path", entry.file_path.clone()))
                .bind((
                    "content_hash",
                    entry.content_hash.clone().unwrap_or_default(),
                ))
                .bind(("ephemeral", entry.ephemeral))
                .bind(("owner", entry.owner.clone()))
                .bind(("visibility", entry.visibility.clone()))
                .bind(("category_id", entry.category_id.clone()))
                .bind((
                    "source_type_id",
                    entry
                        .source_type_id
                        .clone()
                        .unwrap_or_else(|| "manual".to_string()),
                ))
                .bind((
                    "entry_type_id",
                    entry
                        .entry_type_id
                        .clone()
                        .unwrap_or_else(|| "primary".to_string()),
                ))
                .bind((
                    "content_type_id",
                    entry
                        .content_type_id
                        .clone()
                        .unwrap_or_else(|| "text".to_string()),
                ))
                .bind(("resonance", entry.resonance))
                .bind(("resonance_type", entry.resonance_type.clone()))
                .bind(("activation_count", entry.activation_count))
                .bind(("decay_rate", entry.decay_rate))
                .bind(("anchors", entry.anchors.clone()))
                .bind(("wake_phrases", entry.wake_phrases.clone()))
                .bind(("wake_order", entry.wake_order))
                .bind(("wake_phrase", entry.wake_phrase.clone()))
                .bind(("embedding", entry.embedding.clone()))
                .bind(("embedding_model", entry.embedding_model.clone()))
                .bind(("format", entry.format.clone()));

            // Bind optional parameters
            if let Some(ref proj) = entry.source_project_id {
                q = q.bind(("source_project_id", proj.clone()));
            }
            if let Some(ref agent) = entry.source_agent_id {
                q = q.bind(("source_agent_id", agent.clone()));
            }
            if let Some(ref sess) = entry.session_id {
                q = q.bind(("session_id", sess.clone()));
            }
            if let Some(ref created) = entry.created_at {
                q = q.bind(("created_at", normalize_datetime(created)));
            }
            if let Some(ref updated) = entry.updated_at {
                q = q.bind(("updated_at", normalize_datetime(updated)));
            }
            if let Some(ref activated) = entry.last_activated {
                q = q.bind(("last_activated", normalize_datetime(activated)));
            }
            if let Some(ref embedded) = entry.embedded_at {
                q = q.bind(("embedded_at", normalize_datetime(embedded)));
            }

            q.await.context("Failed to upsert knowledge record")
        })?;

        // Check for errors in the response
        let errors = response.take_errors();
        if !errors.is_empty() {
            return Err(anyhow::anyhow!("SurrealDB returned errors: {:?}", errors));
        }

        // Manage tags - delete old, create new
        let mut tag_delete_response = with_db!(self, db, {
            db.query("DELETE tagged_with WHERE in = $knowledge")
                .bind(("knowledge", record_id.0.clone()))
                .await
                .context("Failed to clear existing tags")
        })?;

        let tag_delete_errors = tag_delete_response.take_errors();
        if !tag_delete_errors.is_empty() {
            return Err(anyhow::anyhow!(
                "SurrealDB returned errors: {:?}",
                tag_delete_errors
            ));
        }

        for tag_name in &entry.tags {
            // Ensure tag exists - use query UPSERT to handle schema defaults
            let mut tag_response = with_db!(self, db, {
                db.query("UPSERT type::thing('tag', $tag_id) SET name = $tag_name")
                    .bind(("tag_id", tag_name.clone()))
                    .bind(("tag_name", tag_name.clone()))
                    .await
                    .context("Failed to create tag")
            })?;

            let tag_errors = tag_response.take_errors();
            if !tag_errors.is_empty() {
                return Err(anyhow::anyhow!("Failed to create tag: {:?}", tag_errors));
            }

            let tag_id = RecordId::new("tag", tag_name);

            // Create edge
            let mut tag_edge_response = with_db!(self, db, {
                db.query("RELATE $knowledge->tagged_with->$tag")
                    .bind(("knowledge", record_id.0.clone()))
                    .bind(("tag", tag_id.0.clone()))
                    .await
                    .context("Failed to create tag edge")
            })?;

            let tag_edge_errors = tag_edge_response.take_errors();
            if !tag_edge_errors.is_empty() {
                return Err(anyhow::anyhow!(
                    "SurrealDB returned errors: {:?}",
                    tag_edge_errors
                ));
            }
        }

        // Manage applicability - delete old, create new
        let mut app_delete_response = with_db!(self, db, {
            db.query("DELETE applies_to WHERE in = $knowledge")
                .bind(("knowledge", record_id.0.clone()))
                .await
                .context("Failed to clear existing applicability")
        })?;

        let app_delete_errors = app_delete_response.take_errors();
        if !app_delete_errors.is_empty() {
            return Err(anyhow::anyhow!(
                "SurrealDB returned errors: {:?}",
                app_delete_errors
            ));
        }

        for app_type in &entry.applicability {
            // Ensure applicability_type exists - use query UPSERT to handle schema defaults
            let mut app_type_response = with_db!(self, db, {
                db.query("UPSERT type::thing('applicability_type', $app_type_id) SET description = $app_type_desc")
                    .bind(("app_type_id", app_type.clone()))
                    .bind(("app_type_desc", format!("Applicability: {}", app_type)))
                    .await
                    .context("Failed to create applicability_type")
            })?;

            let app_type_errors = app_type_response.take_errors();
            if !app_type_errors.is_empty() {
                return Err(anyhow::anyhow!(
                    "Failed to create applicability_type: {:?}",
                    app_type_errors
                ));
            }

            let app_id = RecordId::new("applicability_type", app_type);

            // Create edge
            let mut app_edge_response = with_db!(self, db, {
                db.query("RELATE $knowledge->applies_to->$app_type")
                    .bind(("knowledge", record_id.0.clone()))
                    .bind(("app_type", app_id.0.clone()))
                    .await
                    .context("Failed to create applicability edge")
            })?;

            let app_edge_errors = app_edge_response.take_errors();
            if !app_edge_errors.is_empty() {
                return Err(anyhow::anyhow!(
                    "SurrealDB returned errors: {:?}",
                    app_edge_errors
                ));
            }
        }

        Ok(record_id)
    }

    /// Get a knowledge entry by ID
    pub fn get_knowledge(
        &self,
        id: &str,
        ctx: &crate::store::AgentContext,
    ) -> Result<Option<KnowledgeEntry>> {
        Self::runtime().block_on(self.get_knowledge_async(id, ctx))
    }

    async fn get_knowledge_async(
        &self,
        id: &str,
        ctx: &crate::store::AgentContext,
    ) -> Result<Option<KnowledgeEntry>> {
        let id_part = id.strip_prefix("kn-").unwrap_or(id);

        let (visibility_clause, current_agent) = Self::build_visibility_filter(ctx);

        let sql = format!(
            "SELECT {}
            FROM knowledge
            WHERE meta::id(id) = $id {}",
            Self::knowledge_select_fields(),
            visibility_clause
        );

        let mut response = with_db!(self, db, {
            let mut query = db.query(&sql).bind(("id", id_part.to_string()));
            if let Some(agent) = current_agent {
                query = query.bind(("current_agent", agent));
            }
            query.await.context("Failed to query knowledge record")
        })?;

        // Direct deserialization to DTO - no manual JSON parsing!
        let records: Vec<SurrealKnowledgeRecord> = response.take(0)?;

        if records.is_empty() {
            return Ok(None);
        }

        let record = records.into_iter().next().unwrap();

        // Fetch tags and applicability separately
        let tags = self
            .get_tags_for_entry_async(&format!("kn-{}", record.id))
            .await?;
        let applicability = self
            .get_applicability_for_entry_async(&format!("kn-{}", record.id))
            .await?;

        Ok(Some(record.into_knowledge_entry(tags, applicability)))
    }

    /// Delete a knowledge entry (edges cascade automatically).
    /// Respects visibility: agents can only delete entries they can see.
    /// Returns Ok(false) for entries that don't exist OR that the agent can't see
    /// (to avoid leaking existence of private entries).
    pub fn delete_knowledge(&self, id: &str, ctx: &crate::store::AgentContext) -> Result<bool> {
        Self::runtime().block_on(self.delete_knowledge_async(id, ctx))
    }

    async fn delete_knowledge_async(
        &self,
        id: &str,
        ctx: &crate::store::AgentContext,
    ) -> Result<bool> {
        let id_part = id.strip_prefix("kn-").unwrap_or(id);

        let (visibility_clause, current_agent) = Self::build_visibility_filter(ctx);

        // Check if the record exists AND is visible to the current agent.
        // If the entry exists but isn't visible, we return false (same as "not found")
        // to avoid leaking the existence of private entries.
        let check_sql = format!(
            "SELECT count() AS c FROM knowledge WHERE meta::id(id) = $id {} GROUP ALL",
            visibility_clause
        );

        let mut check_response = with_db!(self, db, {
            let mut query = db.query(&check_sql).bind(("id", id_part.to_string()));
            if let Some(ref agent) = current_agent {
                query = query.bind(("current_agent", agent.clone()));
            }
            query
                .await
                .context("Failed to check knowledge record existence")
        })?;

        let count_results: Vec<serde_json::Value> = check_response.take(0)?;
        let exists = count_results
            .first()
            .and_then(|v| v["c"].as_i64())
            .unwrap_or(0)
            > 0;

        if !exists {
            return Ok(false);
        }

        // Delete with the same visibility filter to prevent TOCTOU race conditions.
        // Even though we checked above, re-applying the filter on the DELETE ensures
        // no bypass is possible between check and delete.
        let delete_sql = format!(
            "DELETE FROM knowledge WHERE meta::id(id) = $id {}",
            visibility_clause
        );

        let mut response = with_db!(self, db, {
            let mut query = db.query(&delete_sql).bind(("id", id_part.to_string()));
            if let Some(ref agent) = current_agent {
                query = query.bind(("current_agent", agent.clone()));
            }
            query.await.context("Failed to delete knowledge record")
        })?;

        // Check for errors
        let errors = response.take_errors();
        if !errors.is_empty() {
            return Err(anyhow::anyhow!("Delete failed: {:?}", errors));
        }

        Ok(true)
    }

    // =========================================================================
    // BACKUP OPERATIONS (Issue #206)
    // =========================================================================

    /// Create a pre-mutation backup of entry content
    pub fn backup_content_internal(
        &self,
        entry: &KnowledgeEntry,
        operation: &str,
        agent: Option<&str>,
    ) -> Result<String> {
        Self::runtime().block_on(self.backup_content_async(entry, operation, agent))
    }

    async fn backup_content_async(
        &self,
        entry: &KnowledgeEntry,
        operation: &str,
        agent: Option<&str>,
    ) -> Result<String> {
        let entry_id = entry.id.clone();
        let content_hash = entry.content_hash.clone().unwrap_or_default();
        let backup_id = format!(
            "{}_{}",
            entry_id.replace("kn-", ""),
            Utc::now().format("%Y%m%dT%H%M%S%.3f")
        );

        let _response = with_db!(self, db, {
            db.query(
                "CREATE type::thing('memory_backup', $backup_id) SET
                    entry_id = $entry_id,
                    title = $title,
                    body = $body,
                    content_hash = $content_hash,
                    operation = $operation,
                    source_agent = $source_agent,
                    created_at = time::now()
                ",
            )
            .bind(("backup_id", backup_id.clone()))
            .bind(("entry_id", entry_id.clone()))
            .bind(("title", entry.title.clone()))
            .bind(("body", entry.body.clone()))
            .bind(("content_hash", content_hash))
            .bind(("operation", operation.to_string()))
            .bind(("source_agent", agent.map(|s| s.to_string())))
            .await
            .context("Failed to create memory backup")
        })?;

        // Purge old backups (keep 10 per entry) — non-fatal
        let _ = self.purge_backups_async(&entry_id, 10).await;

        Ok(backup_id)
    }

    /// List backups for an entry, newest first
    pub fn list_backups_internal(&self, entry_id: &str) -> Result<Vec<crate::types::MemoryBackup>> {
        Self::runtime().block_on(self.list_backups_async(entry_id))
    }

    async fn list_backups_async(&self, entry_id: &str) -> Result<Vec<crate::types::MemoryBackup>> {
        let mut response = with_db!(self, db, {
            db.query(
                "SELECT meta::id(id) AS id, entry_id, title, body, content_hash,
                        operation, source_agent, created_at
                 FROM memory_backup
                 WHERE entry_id = $entry_id
                 ORDER BY created_at DESC",
            )
            .bind(("entry_id", entry_id.to_string()))
            .await
            .context("Failed to list memory backups")
        })?;

        let backups: Vec<crate::types::MemoryBackup> = response.take(0)?;
        Ok(backups)
    }

    /// Get the most recent backup for an entry
    pub fn latest_backup_internal(
        &self,
        entry_id: &str,
    ) -> Result<Option<crate::types::MemoryBackup>> {
        Self::runtime().block_on(self.latest_backup_async(entry_id))
    }

    async fn latest_backup_async(
        &self,
        entry_id: &str,
    ) -> Result<Option<crate::types::MemoryBackup>> {
        let mut response = with_db!(self, db, {
            db.query(
                "SELECT meta::id(id) AS id, entry_id, title, body, content_hash,
                        operation, source_agent, created_at
                 FROM memory_backup
                 WHERE entry_id = $entry_id
                 ORDER BY created_at DESC
                 LIMIT 1",
            )
            .bind(("entry_id", entry_id.to_string()))
            .await
            .context("Failed to get latest backup")
        })?;

        let backups: Vec<crate::types::MemoryBackup> = response.take(0)?;
        Ok(backups.into_iter().next())
    }

    /// Purge old backups, keeping the most recent `keep` per entry
    pub fn purge_backups_internal(&self, entry_id: &str, keep: usize) -> Result<()> {
        Self::runtime().block_on(self.purge_backups_async(entry_id, keep))
    }

    async fn purge_backups_async(&self, entry_id: &str, keep: usize) -> Result<()> {
        // Delete backups older than the Nth newest
        let _response = with_db!(self, db, {
            db.query(
                "DELETE FROM memory_backup
                    WHERE entry_id = $entry_id
                    AND id NOT IN (
                        SELECT VALUE id FROM memory_backup
                        WHERE entry_id = $entry_id
                        ORDER BY created_at DESC
                        LIMIT $keep
                    )",
            )
            .bind(("entry_id", entry_id.to_string()))
            .bind(("keep", keep as i64))
            .await
            .context("Failed to purge old backups")
        })?;

        Ok(())
    }

    /// Search knowledge using BM25 full-text indexes
    pub fn search_knowledge(
        &self,
        query: &str,
        ctx: &crate::store::AgentContext,
        filter: &crate::store::KnowledgeFilter,
    ) -> Result<Vec<KnowledgeEntry>> {
        Self::runtime().block_on(self.search_knowledge_async(query, ctx, filter))
    }

    async fn search_knowledge_async(
        &self,
        query: &str,
        ctx: &crate::store::AgentContext,
        filter: &crate::store::KnowledgeFilter,
    ) -> Result<Vec<KnowledgeEntry>> {
        let query_owned = query.to_string();

        let (visibility_clause, current_agent) = Self::build_visibility_filter(ctx);
        let resonance_clause = Self::build_resonance_filter(filter);
        let category_clause = Self::build_category_filter(filter);

        let sql = format!(
            "SELECT {}
            FROM knowledge
            WHERE (title @@ $query OR body @@ $query OR summary @@ $query) {} {} {}",
            Self::knowledge_select_fields(),
            visibility_clause,
            resonance_clause,
            category_clause
        );

        let mut response = with_db!(self, db, {
            let mut query_builder = db.query(&sql).bind(("query", query_owned));
            if let Some(agent) = current_agent {
                query_builder = query_builder.bind(("current_agent", agent));
            }
            query_builder
                .await
                .context("Failed to execute search query")
        })?;

        let results: Vec<serde_json::Value> =
            response.take(0).context("Failed to parse search results")?;

        let mut entries = Vec::new();
        for obj in results {
            entries.push(self.value_to_knowledge_entry(obj).await?);
        }

        Ok(entries)
    }

    /// Semantic search using vector similarity (brute force cosine)
    pub fn semantic_search_knowledge(
        &self,
        query_embedding: &[f32],
        ctx: &crate::store::AgentContext,
        filter: &crate::store::KnowledgeFilter,
        limit: usize,
    ) -> Result<Vec<KnowledgeEntry>> {
        Self::runtime().block_on(self.semantic_search_knowledge_async(
            query_embedding,
            ctx,
            filter,
            limit,
        ))
    }

    async fn semantic_search_knowledge_async(
        &self,
        query_embedding: &[f32],
        ctx: &crate::store::AgentContext,
        filter: &crate::store::KnowledgeFilter,
        limit: usize,
    ) -> Result<Vec<KnowledgeEntry>> {
        let (visibility_clause, current_agent) = Self::build_visibility_filter(ctx);
        let resonance_clause = Self::build_resonance_filter(filter);
        let category_clause = Self::build_category_filter(filter);

        // Brute force vector similarity search (no HNSW index)
        let sql = format!(
            "SELECT {}, vector::similarity::cosine(embedding, $query_vec) AS score
            FROM knowledge
            WHERE embedding IS NOT NONE {} {} {}
            ORDER BY score DESC
            LIMIT $limit",
            Self::knowledge_select_fields(),
            visibility_clause,
            resonance_clause,
            category_clause
        );

        let mut response = with_db!(self, db, {
            let mut query_builder = db
                .query(&sql)
                .bind(("query_vec", query_embedding.to_vec()))
                .bind(("limit", limit));
            if let Some(agent) = current_agent {
                query_builder = query_builder.bind(("current_agent", agent));
            }
            query_builder
                .await
                .context("Failed to execute semantic search query")
        })?;

        let results: Vec<serde_json::Value> = response
            .take(0)
            .context("Failed to parse semantic search results")?;

        let mut entries = Vec::new();
        for obj in results {
            entries.push(self.value_to_knowledge_entry(obj).await?);
        }

        Ok(entries)
    }

    /// Helper: Convert SurrealDB query result to KnowledgeEntry
    async fn value_to_knowledge_entry(&self, obj: serde_json::Value) -> Result<KnowledgeEntry> {
        // Extract ID from string (queries use meta::id(id) AS id)
        let id_str = obj["id"].as_str().unwrap_or_default();
        let id = format!("kn-{}", id_str);

        // Extract category ID from string field
        let category_id = obj["category_id"].as_str().unwrap_or_default().to_string();

        // Extract optional string fields for record links
        let source_project_id = obj
            .get("source_project_id")
            .and_then(|v| v.as_str())
            .filter(|s| !s.is_empty())
            .map(|s| s.to_string());

        let source_agent_id = obj
            .get("source_agent_id")
            .and_then(|v| v.as_str())
            .filter(|s| !s.is_empty())
            .map(|s| s.to_string());

        let session_id = obj
            .get("session_id")
            .and_then(|v| v.as_str())
            .filter(|s| !s.is_empty())
            .map(|s| s.to_string());

        let source_type_id = obj
            .get("source_type_id")
            .and_then(|v| v.as_str())
            .filter(|s| !s.is_empty())
            .map(|s| s.to_string());

        let entry_type_id = obj
            .get("entry_type_id")
            .and_then(|v| v.as_str())
            .filter(|s| !s.is_empty())
            .map(|s| s.to_string());

        let content_type_id = obj
            .get("content_type_id")
            .and_then(|v| v.as_str())
            .filter(|s| !s.is_empty())
            .map(|s| s.to_string());

        // Fetch tags
        let knowledge_thing = Thing::from(("knowledge", id_str));
        let mut tags_response = with_db!(self, db, {
            db.query("SELECT VALUE out.name FROM tagged_with WHERE in = $knowledge")
                .bind(("knowledge", knowledge_thing.clone()))
                .await
                .context("Failed to query tags")
        })?;
        let tags: Vec<String> = tags_response.take(0).unwrap_or_default();

        // Fetch applicability
        let mut app_response = with_db!(self, db, {
            db.query("SELECT VALUE meta::id(out) FROM applies_to WHERE in = $knowledge")
                .bind(("knowledge", knowledge_thing))
                .await
                .context("Failed to query applicability")
        })?;
        let applicability_raw: Vec<Thing> = app_response.take(0).unwrap_or_default();
        let applicability: Vec<String> = applicability_raw
            .into_iter()
            .map(|t| t.id.to_string())
            .collect();

        Ok(KnowledgeEntry {
            id,
            category_id,
            title: serde_json::from_value(obj["title"].clone()).unwrap_or_default(),
            body: serde_json::from_value(obj["body"].clone()).ok(),
            summary: serde_json::from_value(obj["summary"].clone()).ok(),
            file_path: serde_json::from_value(obj["file_path"].clone()).ok(),
            content_hash: serde_json::from_value(obj["content_hash"].clone()).ok(),
            ephemeral: serde_json::from_value(obj["ephemeral"].clone()).unwrap_or(false),
            created_at: serde_json::from_value(obj["created_at"].clone()).ok(),
            updated_at: serde_json::from_value(obj["updated_at"].clone()).ok(),
            tags,
            applicability,
            source_project_id,
            source_agent_id,
            source_type_id,
            entry_type_id,
            content_type_id,
            session_id,
            owner: serde_json::from_value(obj["owner"].clone()).ok(),
            visibility: serde_json::from_value(obj["visibility"].clone())
                .unwrap_or_else(|_| "public".to_string()),
            resonance: serde_json::from_value(obj["resonance"].clone()).unwrap_or(0),
            resonance_type: serde_json::from_value(obj["resonance_type"].clone()).ok(),
            last_activated: serde_json::from_value(obj["last_activated"].clone()).ok(),
            activation_count: serde_json::from_value(obj["activation_count"].clone()).unwrap_or(0),
            decay_rate: serde_json::from_value(obj["decay_rate"].clone()).unwrap_or(0.0),
            anchors: serde_json::from_value(obj["anchors"].clone()).unwrap_or_default(),
            wake_phrases: serde_json::from_value(obj["wake_phrases"].clone()).unwrap_or_default(),
            wake_order: serde_json::from_value(obj["wake_order"].clone()).ok(),
            wake_phrase: serde_json::from_value(obj["wake_phrase"].clone()).ok(),
            embedding: serde_json::from_value(obj["embedding"].clone()).ok(),
            embedding_model: serde_json::from_value(obj["embedding_model"].clone()).ok(),
            embedded_at: serde_json::from_value(obj["embedded_at"].clone()).ok(),
            format: serde_json::from_value(obj["format"].clone())
                .unwrap_or_else(|_| "markdown".to_string()),
            effective_resonance: obj.get("effective_resonance").and_then(|v| v.as_f64()),
        })
    }

    // =========================================================================
    // WAKE CASCADE - Three-layer resonance query for identity loading
    // =========================================================================

    /// Wake-up cascade: Load Q's identity through three layers of resonance
    pub fn wake_cascade(
        &self,
        ctx: &crate::store::AgentContext,
        limit: usize,
        min_resonance: Option<i32>,
        days: i64,
    ) -> Result<crate::store::WakeCascade> {
        Self::runtime().block_on(self.wake_cascade_async(ctx, limit, min_resonance, days))
    }

    async fn wake_cascade_async(
        &self,
        ctx: &crate::store::AgentContext,
        limit: usize,
        min_resonance: Option<i32>,
        days: i64,
    ) -> Result<crate::store::WakeCascade> {
        // If min_resonance is set, use simple query for all blooms >= threshold
        if let Some(threshold) = min_resonance {
            let blooms = self.query_blooms_by_resonance(ctx, threshold).await?;
            return Ok(crate::store::WakeCascade {
                core: blooms,
                recent: Vec::new(),
                bridges: Vec::new(),
            });
        }

        // Sequential filling: core first, then recent, then bridges
        // This ensures we get the most important blooms first

        // Layer 1: Core foundational/transformative blooms (resonance 8+)
        // Use full limit for core - we'll subtract what we get
        let core = self.query_core_blooms(ctx, limit).await?;
        let remaining = limit.saturating_sub(core.len());

        // Layer 2: Recent blooms (last N days)
        // Exclude IDs already in core, use remaining quota
        let core_ids: std::collections::HashSet<String> =
            core.iter().map(|e| e.id.clone()).collect();

        let all_recent = self.query_recent_blooms(ctx, remaining * 2, days).await?;
        let recent: Vec<_> = all_recent
            .into_iter()
            .filter(|e| !core_ids.contains(&e.id))
            .take(remaining)
            .collect();
        let remaining = remaining.saturating_sub(recent.len());

        // Layer 3: Bridge blooms (anchored to core/recent, resonance 5+)
        // Use final remaining quota
        let mut anchor_ids: Vec<String> = core
            .iter()
            .chain(recent.iter())
            .map(|e| e.id.clone())
            .collect();

        // Deduplicate anchor IDs
        anchor_ids.sort();
        anchor_ids.dedup();

        let bridges = if anchor_ids.is_empty() || remaining == 0 {
            Vec::new()
        } else {
            // Exclude IDs already in core/recent
            let mut existing_ids = core_ids;
            existing_ids.extend(recent.iter().map(|e| e.id.clone()));

            let all_bridges = self
                .query_bridge_blooms(ctx, remaining * 2, &anchor_ids)
                .await?;
            all_bridges
                .into_iter()
                .filter(|e| !existing_ids.contains(&e.id))
                .take(remaining)
                .collect()
        };

        Ok(crate::store::WakeCascade {
            core,
            recent,
            bridges,
        })
    }

    /// Query all blooms with resonance >= threshold (for --min-resonance flag)
    async fn query_blooms_by_resonance(
        &self,
        ctx: &crate::store::AgentContext,
        threshold: i32,
    ) -> Result<Vec<crate::knowledge::KnowledgeEntry>> {
        let (visibility_clause, current_agent) = Self::build_visibility_filter(ctx);

        let sql = format!(
            "SELECT {}
            FROM knowledge
            WHERE resonance >= $threshold
            AND (resonance_type IS NONE OR resonance_type != 'ephemeral')
            {}
            ORDER BY resonance DESC",
            Self::knowledge_select_fields(),
            visibility_clause
        );

        let mut response = with_db!(self, db, {
            let mut query = db.query(&sql).bind(("threshold", threshold));
            if let Some(agent) = current_agent {
                query = query.bind(("current_agent", agent));
            }
            query.await.context("Failed to query blooms by resonance")
        })?;

        let results: Vec<serde_json::Value> = response.take(0)?;
        let mut entries = Vec::new();
        for obj in results {
            entries.push(self.value_to_knowledge_entry(obj).await?);
        }

        Ok(entries)
    }

    /// Layer 1: Query core blooms (resonance 8+, excludes ephemeral)
    async fn query_core_blooms(
        &self,
        ctx: &crate::store::AgentContext,
        limit: usize,
    ) -> Result<Vec<crate::knowledge::KnowledgeEntry>> {
        let (visibility_clause, current_agent) = Self::build_visibility_filter(ctx);

        let sql = format!(
            "SELECT *,
                (wake_order IS NOT NULL) AS has_wake_order,
                wake_order ?? 999999 AS effective_wake_order
            FROM (
                SELECT {}
                FROM knowledge
                WHERE resonance >= 8
                AND (resonance_type IS NONE OR resonance_type != 'ephemeral')
                {}
            )
            ORDER BY
                has_wake_order DESC,
                effective_wake_order ASC,
                resonance DESC
            LIMIT $limit",
            Self::knowledge_select_fields(),
            visibility_clause
        );

        let mut response = with_db!(self, db, {
            let mut query = db.query(&sql).bind(("limit", limit as i64));
            if let Some(agent) = current_agent {
                query = query.bind(("current_agent", agent));
            }
            query.await.context("Failed to query core blooms")
        })?;

        let results: Vec<serde_json::Value> = response.take(0)?;
        let mut entries = Vec::new();
        for obj in results {
            entries.push(self.value_to_knowledge_entry(obj).await?);
        }

        Ok(entries)
    }

    /// Layer 2: Query recent blooms (last N days, sorted by resonance)
    async fn query_recent_blooms(
        &self,
        ctx: &crate::store::AgentContext,
        limit: usize,
        days: i64,
    ) -> Result<Vec<crate::knowledge::KnowledgeEntry>> {
        let (visibility_clause, current_agent) = Self::build_visibility_filter(ctx);

        // Calculate cutoff date (N days ago)
        let cutoff = chrono::Utc::now() - chrono::Duration::days(days);
        let cutoff_str = cutoff.to_rfc3339();

        let sql = format!(
            "SELECT *,
                (wake_order IS NOT NULL) AS has_wake_order,
                wake_order ?? 999999 AS effective_wake_order
            FROM (
                SELECT {}
                FROM knowledge
                WHERE last_activated > <datetime>$cutoff
                AND (resonance_type IS NONE OR resonance_type != 'ephemeral')
                {}
            )
            ORDER BY
                has_wake_order DESC,
                effective_wake_order ASC,
                resonance DESC
            LIMIT $limit",
            Self::knowledge_select_fields(),
            visibility_clause
        );

        let mut response = with_db!(self, db, {
            let mut query = db
                .query(&sql)
                .bind(("cutoff", cutoff_str))
                .bind(("limit", limit as i64));
            if let Some(agent) = current_agent {
                query = query.bind(("current_agent", agent));
            }
            query.await.context("Failed to query recent blooms")
        })?;

        let results: Vec<serde_json::Value> = response.take(0)?;
        let mut entries = Vec::new();
        for obj in results {
            entries.push(self.value_to_knowledge_entry(obj).await?);
        }

        Ok(entries)
    }

    /// Layer 3: Query bridge blooms (anchored to core/recent, resonance 5+)
    async fn query_bridge_blooms(
        &self,
        ctx: &crate::store::AgentContext,
        limit: usize,
        anchor_ids: &[String],
    ) -> Result<Vec<crate::knowledge::KnowledgeEntry>> {
        let (visibility_clause, current_agent) = Self::build_visibility_filter(ctx);

        // Use array::intersect to check if anchors array has any overlap with anchor_ids
        // If intersection is non-empty, this bloom is anchored to a core/recent bloom
        let sql = format!(
            "SELECT *,
                (wake_order IS NOT NULL) AS has_wake_order,
                wake_order ?? 999999 AS effective_wake_order
            FROM (
                SELECT {}
                FROM knowledge
                WHERE array::len(array::intersect(anchors, $anchor_ids)) > 0
                AND resonance >= 5
                {}
            )
            ORDER BY
                has_wake_order DESC,
                effective_wake_order ASC,
                resonance DESC
            LIMIT $limit",
            Self::knowledge_select_fields(),
            visibility_clause
        );

        let mut response = with_db!(self, db, {
            let mut query = db
                .query(&sql)
                .bind(("anchor_ids", anchor_ids.to_vec()))
                .bind(("limit", limit as i64));
            if let Some(agent) = current_agent {
                query = query.bind(("current_agent", agent));
            }
            query.await.context("Failed to query bridge blooms")
        })?;

        let results: Vec<serde_json::Value> = response.take(0)?;
        let mut entries = Vec::new();
        for obj in results {
            entries.push(self.value_to_knowledge_entry(obj).await?);
        }

        Ok(entries)
    }

    /// Update activation counts for loaded blooms, resetting last_activated timestamp.
    /// Use this for intentional single-entry access (e.g. `show`, `fact-session`).
    pub fn update_activations(&self, ids: &[String]) -> Result<()> {
        Self::runtime().block_on(self.update_activations_async(ids))
    }

    async fn update_activations_async(&self, ids: &[String]) -> Result<()> {
        if ids.is_empty() {
            return Ok(());
        }

        // Strip "kn-" prefix from IDs if present
        let clean_ids: Vec<String> = ids
            .iter()
            .map(|id| id.strip_prefix("kn-").unwrap_or(id).to_string())
            .collect();

        // Build array of Thing references
        let things: Vec<Thing> = clean_ids
            .iter()
            .map(|id| Thing::from(("knowledge", id.as_str())))
            .collect();

        let mut response = with_db!(self, db, {
            db.query(
                "UPDATE knowledge SET
                activation_count += 1,
                last_activated = time::now()
                WHERE id IN $ids",
            )
            .bind(("ids", things))
            .await
            .context("Failed to update activations")
        })?;

        let errors = response.take_errors();
        if !errors.is_empty() {
            return Err(anyhow::anyhow!(
                "Failed to update activations: {:?}",
                errors
            ));
        }

        Ok(())
    }

    /// Update only the summary field of a knowledge entry.
    /// Respects visibility: agents can only update summaries on entries they can see.
    /// Returns Ok(false) for entries that don't exist OR that the agent can't see
    /// (to avoid leaking existence of private entries).
    pub fn update_summary(
        &self,
        id: &str,
        summary: &str,
        ctx: &crate::store::AgentContext,
    ) -> Result<bool> {
        Self::runtime().block_on(self.update_summary_async(id, summary, ctx))
    }

    async fn update_summary_async(
        &self,
        id: &str,
        summary: &str,
        ctx: &crate::store::AgentContext,
    ) -> Result<bool> {
        let id_part = id.strip_prefix("kn-").unwrap_or(id);

        let (visibility_clause, current_agent) = Self::build_visibility_filter(ctx);

        // Check if the record exists AND is visible to the current agent.
        // If the entry exists but isn't visible, we return false (same as "not found")
        // to avoid leaking the existence of private entries.
        let check_sql = format!(
            "SELECT count() AS c FROM knowledge WHERE meta::id(id) = $id {} GROUP ALL",
            visibility_clause
        );

        let mut check_response = with_db!(self, db, {
            let mut query = db.query(&check_sql).bind(("id", id_part.to_string()));
            if let Some(ref agent) = current_agent {
                query = query.bind(("current_agent", agent.clone()));
            }
            query
                .await
                .context("Failed to check knowledge record existence for summary update")
        })?;

        let count_results: Vec<serde_json::Value> = check_response.take(0)?;
        let exists = count_results
            .first()
            .and_then(|v| v["c"].as_i64())
            .unwrap_or(0)
            > 0;

        if !exists {
            return Ok(false);
        }

        // Update with the same visibility filter to prevent TOCTOU race conditions.
        // Even though we checked above, re-applying the filter on the UPDATE ensures
        // no bypass is possible between check and update.
        let update_sql = format!(
            "UPDATE knowledge SET summary = $summary WHERE meta::id(id) = $id {}",
            visibility_clause
        );

        let mut response = with_db!(self, db, {
            let mut query = db
                .query(&update_sql)
                .bind(("id", id_part.to_string()))
                .bind(("summary", summary.to_string()));
            if let Some(ref agent) = current_agent {
                query = query.bind(("current_agent", agent.clone()));
            }
            query.await.context("Failed to update summary")
        })?;

        let errors = response.take_errors();
        if !errors.is_empty() {
            return Err(anyhow::anyhow!("Failed to update summary: {:?}", errors));
        }

        Ok(true)
    }

    /// Increment activation_count only — does NOT reset last_activated.
    /// Use this for passive bulk surfacing (wake cascade, for-session view) where
    /// the entries were not intentionally accessed and should continue decaying
    /// at their normal rate.
    pub fn increment_activation_count(&self, ids: &[String]) -> Result<()> {
        Self::runtime().block_on(self.increment_activation_count_async(ids))
    }

    async fn increment_activation_count_async(&self, ids: &[String]) -> Result<()> {
        if ids.is_empty() {
            return Ok(());
        }

        // Strip "kn-" prefix from IDs if present
        let clean_ids: Vec<String> = ids
            .iter()
            .map(|id| id.strip_prefix("kn-").unwrap_or(id).to_string())
            .collect();

        // Build array of Thing references
        let things: Vec<Thing> = clean_ids
            .iter()
            .map(|id| Thing::from(("knowledge", id.as_str())))
            .collect();

        let mut response = with_db!(self, db, {
            db.query(
                "UPDATE knowledge SET
                activation_count += 1
                WHERE id IN $ids",
            )
            .bind(("ids", things))
            .await
            .context("Failed to increment activation counts")
        })?;

        let errors = response.take_errors();
        if !errors.is_empty() {
            return Err(anyhow::anyhow!(
                "Failed to increment activation counts: {:?}",
                errors
            ));
        }

        Ok(())
    }

    /// Query recent ephemeral facts with decay computation
    pub fn query_recent_facts(&self, days: i32) -> Result<Vec<KnowledgeEntry>> {
        Self::runtime().block_on(self.query_recent_facts_async(days))
    }

    async fn query_recent_facts_async(&self, days: i32) -> Result<Vec<KnowledgeEntry>> {
        // Query with computed effective_resonance for ordering and filtering.
        // Uses the shared decay formula from effective_resonance_expr().
        // This query only surfaces ephemeral entries (resonance_type = 'ephemeral');
        // foundational/transformative entries are excluded and never reach this path.
        let expr = Self::effective_resonance_expr();
        let sql = format!(
            "SELECT {},
                 ({expr}) AS effective_resonance
             FROM knowledge
             WHERE resonance_type = 'ephemeral'
             AND created_at > time::now() - duration::from::days($days)
             AND ({expr}) > 0.5
             ORDER BY effective_resonance DESC",
            Self::knowledge_select_fields(),
            expr = expr
        );

        let mut response = with_db!(self, db, {
            db.query(&sql)
                .bind(("days", days))
                .await
                .context("Failed to execute recent facts query")
        })?;

        let results: Vec<serde_json::Value> = response
            .take(0)
            .context("Failed to parse recent facts results")?;

        let mut entries = Vec::new();
        for obj in results {
            entries.push(self.value_to_knowledge_entry(obj).await?);
        }

        Ok(entries)
    }

    /// Query recent facts across ALL resonance types with decay computation.
    /// Foundational/transformative entries are exempt from decay (effective_resonance = resonance).
    pub fn query_recent_facts_all_types(&self, days: i32) -> Result<Vec<KnowledgeEntry>> {
        Self::runtime().block_on(self.query_recent_facts_all_types_async(days))
    }

    async fn query_recent_facts_all_types_async(&self, days: i32) -> Result<Vec<KnowledgeEntry>> {
        // Like query_recent_facts_async but without the resonance_type = 'ephemeral' filter.
        // Ephemeral entries are still decay-filtered (> 0.5). Foundational/transformative
        // entries are exempt from decay so they always surface here.
        let expr = Self::effective_resonance_expr();
        let sql = format!(
            "SELECT {},
                 ({expr}) AS effective_resonance
             FROM knowledge
             WHERE created_at > time::now() - duration::from::days($days)
             AND ({expr}) > 0.5
             ORDER BY effective_resonance DESC",
            Self::knowledge_select_fields(),
            expr = expr
        );

        let mut response = with_db!(self, db, {
            db.query(&sql)
                .bind(("days", days))
                .await
                .context("Failed to execute recent facts (all types) query")
        })?;

        let results: Vec<serde_json::Value> = response
            .take(0)
            .context("Failed to parse recent facts (all types) results")?;

        let mut entries = Vec::new();
        for obj in results {
            entries.push(self.value_to_knowledge_entry(obj).await?);
        }

        Ok(entries)
    }

    /// Reinforce a knowledge entry.
    /// Respects visibility: agents can only reinforce entries they can see.
    /// Returns Ok(None) for entries that don't exist OR that the agent can't see
    /// (to avoid leaking existence of private entries).
    pub fn reinforce(
        &self,
        id: &str,
        amount: i32,
        cap: Option<i32>,
        ctx: &crate::store::AgentContext,
    ) -> Result<Option<crate::store::ReinforcementResult>> {
        Self::runtime().block_on(self.reinforce_async(id, amount, cap, ctx))
    }

    async fn reinforce_async(
        &self,
        id: &str,
        amount: i32,
        cap: Option<i32>,
        ctx: &crate::store::AgentContext,
    ) -> Result<Option<crate::store::ReinforcementResult>> {
        // Normalize ID
        let normalized_id = if id.starts_with("kn-") {
            id.to_string()
        } else {
            format!("kn-{}", id)
        };

        let id_part = normalized_id.strip_prefix("kn-").unwrap_or(&normalized_id);

        let (visibility_clause, current_agent) = Self::build_visibility_filter(ctx);

        // Check if the record exists AND is visible to the current agent.
        // If the entry exists but isn't visible, we return None (same as "not found")
        // to avoid leaking the existence of private entries.
        let select_sql = format!(
            "SELECT resonance, activation_count FROM knowledge WHERE meta::id(id) = $id {}",
            visibility_clause
        );

        let mut response = with_db!(self, db, {
            let mut query = db.query(&select_sql).bind(("id", id_part.to_string()));
            if let Some(ref agent) = current_agent {
                query = query.bind(("current_agent", agent.clone()));
            }
            query.await.context("Failed to select entry for reinforce")
        })?;

        let results: Vec<serde_json::Value> = response
            .take(0)
            .context("Failed to parse entry for reinforce")?;

        let current = match results.first() {
            Some(v) => v,
            None => return Ok(None),
        };

        let old_resonance = current
            .get("resonance")
            .and_then(|v| v.as_i64())
            .unwrap_or(0) as i32;

        let old_activation_count = current
            .get("activation_count")
            .and_then(|v| v.as_i64())
            .unwrap_or(0) as i32;

        // Calculate new resonance
        let mut new_resonance = old_resonance + amount;
        let capped = if let Some(cap_value) = cap {
            if new_resonance > cap_value {
                new_resonance = cap_value;
                true
            } else {
                false
            }
        } else {
            false
        };

        let new_activation_count = old_activation_count + 1;

        // Update with the same visibility filter to prevent TOCTOU race conditions.
        // Even though we checked above, re-applying the filter on the UPDATE ensures
        // no bypass is possible between check and update.
        let update_sql = format!(
            "UPDATE knowledge SET
            resonance = $new_resonance,
            last_activated = time::now(),
            activation_count = $new_count,
            updated_at = time::now()
            WHERE meta::id(id) = $id {}",
            visibility_clause
        );

        let mut update_response = with_db!(self, db, {
            let mut query = db
                .query(&update_sql)
                .bind(("id", id_part.to_string()))
                .bind(("new_resonance", new_resonance))
                .bind(("new_count", new_activation_count));
            if let Some(ref agent) = current_agent {
                query = query.bind(("current_agent", agent.clone()));
            }
            query.await.context("Failed to update entry for reinforce")
        })?;

        let errors = update_response.take_errors();
        if !errors.is_empty() {
            return Err(anyhow::anyhow!("Failed to reinforce entry: {:?}", errors));
        }

        // Get current timestamp for response
        let now = Utc::now().to_rfc3339();

        Ok(Some(crate::store::ReinforcementResult {
            id: normalized_id,
            old_resonance,
            new_resonance,
            amount_added: amount,
            capped,
            last_activated: now,
            activation_count: new_activation_count,
        }))
    }

    // =========================================================================
    // LOOKUP OPERATIONS
    // =========================================================================

    /// List all categories
    pub fn list_categories(&self) -> Result<Vec<Category>> {
        Self::runtime().block_on(self.list_categories_async())
    }

    async fn list_categories_async(&self) -> Result<Vec<Category>> {
        let mut response = with_db!(self, db, {
            db.query("SELECT meta::id(id) AS id, description, <string>created_at AS created_at FROM category ORDER BY id")
                .await
                .context("Failed to list categories")
        })?;

        let results: Vec<serde_json::Value> = response.take(0)?;

        let mut categories = Vec::new();
        for obj in results {
            // Parse string from id field
            let id = obj["id"].as_str().unwrap_or_default().to_string();

            categories.push(Category {
                id,
                description: obj["description"].as_str().unwrap_or_default().to_string(),
                created_at: obj["created_at"].as_str().unwrap_or_default().to_string(),
            });
        }

        Ok(categories)
    }

    /// List all projects
    pub fn list_projects(&self) -> Result<Vec<Project>> {
        Self::runtime().block_on(self.list_projects_async())
    }

    async fn list_projects_async(&self) -> Result<Vec<Project>> {
        let mut response = with_db!(self, db, {
            db.query("SELECT meta::id(id) AS id, name, path, repo_url, description, active, <string>created_at AS created_at, <string>updated_at AS updated_at FROM project ORDER BY name")
                .await
                .context("Failed to list projects")
        })?;

        let results: Vec<serde_json::Value> = response.take(0)?;
        let mut projects = Vec::new();

        for obj in results {
            let id = obj["id"].as_str().unwrap_or_default().to_string();

            projects.push(Project {
                id,
                name: obj["name"].as_str().unwrap_or_default().to_string(),
                path: obj["path"].as_str().map(|s| s.to_string()),
                repo_url: obj["repo_url"].as_str().map(|s| s.to_string()),
                description: obj["description"].as_str().map(|s| s.to_string()),
                active: obj["active"].as_bool().unwrap_or(true),
                created_at: obj["created_at"].as_str().unwrap_or_default().to_string(),
                updated_at: obj["updated_at"].as_str().unwrap_or_default().to_string(),
            });
        }

        Ok(projects)
    }

    /// List all agents
    pub fn list_agents(&self) -> Result<Vec<Agent>> {
        Self::runtime().block_on(self.list_agents_async())
    }

    async fn list_agents_async(&self) -> Result<Vec<Agent>> {
        let mut response = with_db!(self, db, {
            db.query("SELECT meta::id(id) AS id, description, domain, <string>created_at AS created_at, <string>updated_at AS updated_at FROM agent ORDER BY id")
                .await
                .context("Failed to list agents")
        })?;

        let results: Vec<serde_json::Value> = response.take(0)?;
        let mut agents = Vec::new();

        for obj in results {
            let id = obj["id"].as_str().unwrap_or_default().to_string();

            agents.push(Agent {
                id,
                description: obj["description"].as_str().map(|s| s.to_string()),
                domain: obj["domain"].as_str().map(|s| s.to_string()),
                created_at: obj["created_at"].as_str().map(|s| s.to_string()),
                updated_at: obj["updated_at"].as_str().map(|s| s.to_string()),
            });
        }

        Ok(agents)
    }

    /// List all tags
    pub fn list_tags(&self) -> Result<Vec<Tag>> {
        Self::runtime().block_on(self.list_tags_async())
    }

    async fn list_tags_async(&self) -> Result<Vec<Tag>> {
        let mut response = with_db!(self, db, {
            db.query("SELECT meta::id(id) AS id, name, <string>created_at AS created_at FROM tag ORDER BY name")
                .await
                .context("Failed to list tags")
        })?;

        let results: Vec<serde_json::Value> = response.take(0)?;
        let mut tags = Vec::new();

        for obj in results {
            tags.push(Tag {
                name: obj["name"].as_str().unwrap_or_default().to_string(),
                created_at: obj["created_at"].as_str().map(|s| s.to_string()),
            });
        }

        Ok(tags)
    }

    /// List all distinct tag names, optionally filtered by category
    pub fn list_all_tags(&self, category: Option<&str>) -> Result<Vec<String>> {
        Self::runtime().block_on(self.list_all_tags_async(category.map(|s| s.to_string())))
    }

    async fn list_all_tags_async(&self, category: Option<String>) -> Result<Vec<String>> {
        let mut tags = if let Some(cat) = category {
            // Traverse from tag side: find tags whose knowledge entries belong to the category.
            // Filtering via `WHERE in.category = ...` on a graph edge table does not work in
            // SurrealDB 2.x — the predicate matches nothing even though the field is present.
            // Reverse traversal through the tag record works correctly.
            let mut response = with_db!(self, db, {
                db.query(
                    "SELECT VALUE name FROM tag \
                     WHERE <-tagged_with<-knowledge.category CONTAINS type::thing('category', $cat)",
                )
                .bind(("cat", cat))
                .await
                .context("Failed to list tags by category")
            })?;
            let tags: Vec<String> = response.take(0).unwrap_or_default();
            tags
        } else {
            // Only return tags that are actually in use (have at least one incoming edge).
            // The previous query used `array::distinct(out.name)` on the edge table, but
            // `out.name` is a scalar string per row — array::distinct expects an array and errors.
            let mut response = with_db!(self, db, {
                db.query("SELECT VALUE name FROM tag WHERE <-tagged_with")
                    .await
                    .context("Failed to list all tags")
            })?;
            let tags: Vec<String> = response.take(0).unwrap_or_default();
            tags
        };

        tags.sort();
        Ok(tags)
    }

    /// List all applicability types
    pub fn list_applicability_types(&self) -> Result<Vec<ApplicabilityType>> {
        Self::runtime().block_on(self.list_applicability_types_async())
    }

    async fn list_applicability_types_async(&self) -> Result<Vec<ApplicabilityType>> {
        let mut response = with_db!(self, db, {
            db.query("SELECT meta::id(id) AS id, description, scope, <string>created_at AS created_at FROM applicability_type ORDER BY id")
                .await
                .context("Failed to list applicability types")
        })?;

        let results: Vec<serde_json::Value> = response.take(0)?;
        let mut types = Vec::new();

        for obj in results {
            // Parse string from id field
            let id = obj["id"].as_str().unwrap_or_default().to_string();

            types.push(ApplicabilityType {
                id,
                description: obj["description"].as_str().unwrap_or_default().to_string(),
                scope: obj["scope"].as_str().map(|s| s.to_string()),
                created_at: obj["created_at"].as_str().unwrap_or_default().to_string(),
            });
        }

        Ok(types)
    }

    /// Upsert a project (returns RecordId)
    pub fn upsert_project_internal(&self, project: &Project) -> Result<RecordId> {
        Self::runtime().block_on(self.upsert_project_async(project))
    }

    async fn upsert_project_async(&self, project: &Project) -> Result<RecordId> {
        let record_id = RecordId::new("project", &project.id);

        // Always include datetimes - use current time if not provided
        let now = Utc::now().to_rfc3339();
        let created_at = if project.created_at.is_empty() {
            now.clone()
        } else {
            project.created_at.clone()
        };
        let updated_at = if project.updated_at.is_empty() {
            now.clone()
        } else {
            project.updated_at.clone()
        };

        let mut response = with_db!(self, db, {
            db.query(
                "UPSERT type::thing('project', $id) SET
                name = $name,
                path = $path,
                repo_url = $repo_url,
                description = $description,
                active = $active,
                created_at = <datetime>$created_at,
                updated_at = <datetime>$updated_at
            ",
            )
            .bind(("id", project.id.clone()))
            .bind(("name", project.name.clone()))
            .bind(("path", project.path.clone()))
            .bind(("repo_url", project.repo_url.clone()))
            .bind(("description", project.description.clone()))
            .bind(("active", project.active))
            .bind(("created_at", normalize_datetime(&created_at)))
            .bind(("updated_at", normalize_datetime(&updated_at)))
            .await
            .context("Failed to upsert project")
        })?;

        // Check for errors in the response
        let errors = response.take_errors();
        if !errors.is_empty() {
            return Err(anyhow::anyhow!("SurrealDB returned errors: {:?}", errors));
        }

        Ok(record_id)
    }

    // =========================================================================
    // RELATIONSHIP OPERATIONS
    // =========================================================================

    /// Add a relationship between knowledge entries
    pub fn add_relationship(&self, from: &str, to: &str, rel_type: &str) -> Result<()> {
        Self::runtime().block_on(self.add_relationship_async(from, to, rel_type))
    }

    async fn add_relationship_async(&self, from: &str, to: &str, rel_type: &str) -> Result<()> {
        let from_id = from.strip_prefix("kn-").unwrap_or(from);
        let to_id = to.strip_prefix("kn-").unwrap_or(to);

        let from_thing = Thing::from(("knowledge", from_id));
        let to_thing = Thing::from(("knowledge", to_id));
        let rel_type_thing = Thing::from(("relationship_type", rel_type));

        with_db!(self, db, {
            db.query("RELATE $from->relates_to->$to SET relationship_type = $rel_type")
                .bind(("from", from_thing))
                .bind(("to", to_thing))
                .bind(("rel_type", rel_type_thing))
                .await
                .context("Failed to create relationship")
        })?;

        Ok(())
    }

    /// List all relationships for a knowledge entry
    pub fn list_relationships(&self, entry_id: &str) -> Result<Vec<Relationship>> {
        Self::runtime().block_on(self.list_relationships_async(entry_id))
    }

    async fn list_relationships_async(&self, entry_id: &str) -> Result<Vec<Relationship>> {
        let id_part = entry_id.strip_prefix("kn-").unwrap_or(entry_id);
        let entry_thing = Thing::from(("knowledge", id_part));

        // Use meta::id() to extract plain string IDs from Thing record links.
        // Direct deserialization of Thing fields via serde_json::Value fails
        // because surrealdb::sql::Thing serializes as an untagged enum tuple
        // that serde_json cannot round-trip. meta::id() returns a plain string.
        #[derive(Debug, Deserialize)]
        struct RelRow {
            id: String,
            from_entry_id: String,
            to_entry_id: String,
            relationship_type: String,
            created_at: String,
        }

        let mut response = with_db!(self, db, {
            db.query(
                "SELECT meta::id(id) AS id,
                        meta::id(in) AS from_entry_id,
                        meta::id(out) AS to_entry_id,
                        meta::id(relationship_type) AS relationship_type,
                        <string>created_at AS created_at
                 FROM relates_to
                 WHERE in = $entry OR out = $entry
                 ORDER BY created_at DESC",
            )
            .bind(("entry", entry_thing))
            .await
            .context("Failed to query relationships")
        })?;

        let results: Vec<RelRow> = response.take(0)?;
        let relationships = results
            .into_iter()
            .map(|row| Relationship {
                id: row.id,
                from_entry_id: format!("kn-{}", row.from_entry_id),
                to_entry_id: format!("kn-{}", row.to_entry_id),
                relationship_type: row.relationship_type,
                created_at: row.created_at,
            })
            .collect();

        Ok(relationships)
    }

    /// Delete a relationship by from/to/type triple
    pub fn delete_relationship(&self, from: &str, to: &str, rel_type: &str) -> Result<bool> {
        Self::runtime().block_on(self.delete_relationship_async(from, to, rel_type))
    }

    /// Delete a relationship edge by its record ID (e.g. "abc123" or the raw SurrealDB ID).
    pub fn delete_relationship_by_id(&self, id: &str) -> Result<bool> {
        Self::runtime().block_on(self.delete_relationship_by_id_async(id))
    }

    async fn delete_relationship_by_id_async(&self, id: &str) -> Result<bool> {
        // SurrealDB's RETURN BEFORE yields Thing-typed fields that serde_json cannot
        // round-trip. Instead: SELECT with meta::id() to check existence, then DELETE
        // without a RETURN clause (which is safe to take as Vec<Value> empty).
        #[derive(Debug, Deserialize)]
        struct ExistsRow {
            id: String,
        }

        let mut check = with_db!(self, db, {
            db.query("SELECT meta::id(id) AS id FROM relates_to WHERE meta::id(id) = $id LIMIT 1")
                .bind(("id", id.to_string()))
                .await
                .context("Failed to check relationship existence")
        })?;

        let exists: Vec<ExistsRow> = check.take(0)?;
        if exists.is_empty() {
            return Ok(false);
        }

        with_db!(self, db, {
            db.query("DELETE relates_to WHERE meta::id(id) = $id")
                .bind(("id", id.to_string()))
                .await
                .context("Failed to delete relationship by id")
        })?;

        Ok(true)
    }

    async fn delete_relationship_async(
        &self,
        from: &str,
        to: &str,
        rel_type: &str,
    ) -> Result<bool> {
        let from_id = from.strip_prefix("kn-").unwrap_or(from);
        let to_id = to.strip_prefix("kn-").unwrap_or(to);

        let from_thing = Thing::from(("knowledge", from_id));
        let to_thing = Thing::from(("knowledge", to_id));
        let rel_type_thing = Thing::from(("relationship_type", rel_type));

        // SurrealDB's RETURN BEFORE yields Thing-typed fields that serde_json cannot
        // round-trip. Check existence with meta::id() SELECT first, then DELETE without
        // a RETURN clause to avoid the deserialization error.
        #[derive(Debug, Deserialize)]
        struct ExistsRow {
            id: String,
        }

        let mut check = with_db!(self, db, {
            db.query(
                "SELECT meta::id(id) AS id FROM relates_to
                 WHERE in = $from AND out = $to AND relationship_type = $rel_type
                 LIMIT 1",
            )
            .bind(("from", from_thing.clone()))
            .bind(("to", to_thing.clone()))
            .bind(("rel_type", rel_type_thing.clone()))
            .await
            .context("Failed to check relationship existence")
        })?;

        let exists: Vec<ExistsRow> = check.take(0)?;
        if exists.is_empty() {
            return Ok(false);
        }

        with_db!(self, db, {
            db.query(
                "DELETE relates_to
                 WHERE in = $from AND out = $to AND relationship_type = $rel_type",
            )
            .bind(("from", from_thing))
            .bind(("to", to_thing))
            .bind(("rel_type", rel_type_thing))
            .await
            .context("Failed to delete relationship")
        })?;

        Ok(true)
    }

    /// Get facts extracted from a specific session
    pub fn get_facts_for_session(&self, session_id: &str) -> Result<Vec<String>> {
        Self::runtime().block_on(self.get_facts_for_session_async(session_id))
    }

    async fn get_facts_for_session_async(&self, session_id: &str) -> Result<Vec<String>> {
        let session_id_normalized = session_id.strip_prefix("kn-").unwrap_or(session_id);
        let session_thing = Thing::from(("knowledge", session_id_normalized));

        let mut response = with_db!(self, db, {
            db.query(
                "SELECT VALUE meta::id(in) FROM relates_to
                 WHERE out = $session_id AND relationship_type = relationship_type:extracted_from",
            )
            .bind(("session_id", session_thing))
            .await
            .context("Failed to query facts for session")
        })?;

        let fact_ids: Vec<String> = response.take(0).unwrap_or_default();
        let facts_with_prefix: Vec<String> = fact_ids
            .into_iter()
            .map(|id| format!("kn-{}", id))
            .collect();

        Ok(facts_with_prefix)
    }

    /// Get the session a fact was extracted from
    pub fn get_session_for_fact(&self, fact_id: &str) -> Result<Option<String>> {
        Self::runtime().block_on(self.get_session_for_fact_async(fact_id))
    }

    async fn get_session_for_fact_async(&self, fact_id: &str) -> Result<Option<String>> {
        let fact_id_normalized = fact_id.strip_prefix("kn-").unwrap_or(fact_id);
        let fact_thing = Thing::from(("knowledge", fact_id_normalized));

        let mut response = with_db!(self, db, {
            db.query(
                "SELECT VALUE meta::id(out) FROM relates_to
                 WHERE in = $fact AND relationship_type = relationship_type:extracted_from",
            )
            .bind(("fact", fact_thing))
            .await
            .context("Failed to query session for fact")
        })?;

        let session_ids: Vec<String> = response.take(0).unwrap_or_default();

        Ok(session_ids.first().map(|id| format!("kn-{}", id)))
    }

    // =========================================================================
    // TAG OPERATIONS (not exposed in public API, handled via knowledge entry)
    // =========================================================================

    /// Get tags for an entry
    pub fn get_tags_for_entry(&self, entry_id: &str) -> Result<Vec<String>> {
        Self::runtime().block_on(self.get_tags_for_entry_async(entry_id))
    }

    async fn get_tags_for_entry_async(&self, entry_id: &str) -> Result<Vec<String>> {
        let id_part = entry_id.strip_prefix("kn-").unwrap_or(entry_id);
        let entry_thing = Thing::from(("knowledge", id_part));

        let mut tags_response = with_db!(self, db, {
            db.query("SELECT VALUE out.name FROM tagged_with WHERE in = $knowledge")
                .bind(("knowledge", entry_thing))
                .await
                .context("Failed to query tags")
        })?;

        let tags: Vec<String> = tags_response.take(0).unwrap_or_default();
        Ok(tags)
    }

    /// Set tags for an entry - handled automatically by upsert_knowledge
    pub fn set_tags_for_entry(&self, _entry_id: &str, _tags: &[String]) -> Result<()> {
        // Tags are managed via upsert_knowledge, this is a no-op for compatibility
        Ok(())
    }

    /// Get applicability for an entry
    pub fn get_applicability_for_entry(&self, entry_id: &str) -> Result<Vec<String>> {
        Self::runtime().block_on(self.get_applicability_for_entry_async(entry_id))
    }

    async fn get_applicability_for_entry_async(&self, entry_id: &str) -> Result<Vec<String>> {
        let id_part = entry_id.strip_prefix("kn-").unwrap_or(entry_id);
        let entry_thing = Thing::from(("knowledge", id_part));

        let mut app_response = with_db!(self, db, {
            db.query("SELECT VALUE meta::id(out) FROM applies_to WHERE in = $knowledge")
                .bind(("knowledge", entry_thing))
                .await
                .context("Failed to query applicability")
        })?;

        let applicability_raw: Vec<Thing> = app_response.take(0).unwrap_or_default();
        let applicability: Vec<String> = applicability_raw
            .into_iter()
            .map(|t| t.id.to_string())
            .collect();

        Ok(applicability)
    }

    /// Set applicability for an entry - handled automatically by upsert_knowledge
    pub fn set_applicability_for_entry(&self, _entry_id: &str, _ids: &[String]) -> Result<()> {
        // Applicability is managed via upsert_knowledge, this is a no-op for compatibility
        Ok(())
    }

    /// Upsert applicability type
    pub fn upsert_applicability_type(&self, atype: &ApplicabilityType) -> Result<()> {
        Self::runtime().block_on(self.upsert_applicability_type_async(atype))
    }

    async fn upsert_applicability_type_async(&self, atype: &ApplicabilityType) -> Result<()> {
        // Always include datetimes - use current time if not provided
        let now = Utc::now().to_rfc3339();
        let created_at = if atype.created_at.is_empty() {
            now
        } else {
            atype.created_at.clone()
        };

        let mut response = with_db!(self, db, {
            db.query(
                "UPSERT type::thing('applicability_type', $id) SET
                description = $description,
                scope = $scope,
                created_at = <datetime>$created_at
            ",
            )
            .bind(("id", atype.id.clone()))
            .bind(("description", atype.description.clone()))
            .bind(("scope", atype.scope.clone()))
            .bind(("created_at", normalize_datetime(&created_at)))
            .await
            .context("Failed to upsert applicability type")
        })?;

        // Check for errors in the response
        let errors = response.take_errors();
        if !errors.is_empty() {
            return Err(anyhow::anyhow!("SurrealDB returned errors: {:?}", errors));
        }

        Ok(())
    }

    /// Get category by ID
    pub fn get_category(&self, id: &str) -> Result<Option<Category>> {
        Self::runtime().block_on(self.get_category_async(id))
    }

    async fn get_category_async(&self, id: &str) -> Result<Option<Category>> {
        let mut response = with_db!(self, db, {
            db.query("SELECT meta::id(id) AS id, description, <string>created_at AS created_at FROM category WHERE id = type::thing('category', $id)")
                .bind(("id", id.to_string()))
                .await
                .context("Failed to query category")
        })?;

        let results: Vec<serde_json::Value> = response.take(0)?;

        if results.is_empty() {
            return Ok(None);
        }

        let obj = &results[0];
        let id_str = obj["id"].as_str().unwrap_or_default().to_string();

        Ok(Some(Category {
            id: id_str,
            description: obj["description"].as_str().unwrap_or_default().to_string(),
            created_at: obj["created_at"].as_str().unwrap_or_default().to_string(),
        }))
    }

    /// Upsert a category
    pub fn upsert_category(&self, category: &Category) -> Result<()> {
        Self::runtime().block_on(self.upsert_category_async(category))
    }

    async fn upsert_category_async(&self, category: &Category) -> Result<()> {
        // Always include datetime - use current time if not provided
        let now = Utc::now().to_rfc3339();
        let created_at = if category.created_at.is_empty() {
            now
        } else {
            category.created_at.clone()
        };

        let mut response = with_db!(self, db, {
            db.query(
                "UPSERT type::thing('category', $id) SET
                description = $description,
                created_at = <datetime>$created_at
            ",
            )
            .bind(("id", category.id.clone()))
            .bind(("description", category.description.clone()))
            .bind(("created_at", normalize_datetime(&created_at)))
            .await
            .context("Failed to upsert category")
        })?;

        // Check for errors in the response
        let errors = response.take_errors();
        if !errors.is_empty() {
            return Err(anyhow::anyhow!("SurrealDB returned errors: {:?}", errors));
        }

        Ok(())
    }

    /// Delete a category (only if no entries use it)
    pub fn delete_category(&self, id: &str) -> Result<bool> {
        Self::runtime().block_on(self.delete_category_async(id))
    }

    async fn delete_category_async(&self, id: &str) -> Result<bool> {
        let category_thing = Thing::from(("category", id));

        // Check if any knowledge entries use this category
        let mut count_response = with_db!(self, db, {
            db.query("SELECT count() AS c FROM knowledge WHERE category = $category GROUP ALL")
                .bind(("category", category_thing.clone()))
                .await
                .context("Failed to count knowledge entries for category")
        })?;

        let count_results: Vec<serde_json::Value> = count_response.take(0)?;
        let count = count_results
            .first()
            .and_then(|v| v["c"].as_i64())
            .unwrap_or(0);

        if count > 0 {
            return Err(anyhow::anyhow!(
                "Cannot remove category '{}': {} entries still use it",
                id,
                count
            ));
        }

        // Delete the category
        let record_id = RecordId::new("category", id);
        let result: Option<Value> = with_db!(self, db, {
            db.delete(record_id.to_record_id())
                .await
                .context("Failed to delete category")
        })?;

        Ok(result.is_some())
    }

    /// Get project by ID
    pub fn get_project(&self, id: &str) -> Result<Option<Project>> {
        Self::runtime().block_on(self.get_project_async(id))
    }

    async fn get_project_async(&self, id: &str) -> Result<Option<Project>> {
        let mut response = with_db!(self, db, {
            db.query("SELECT meta::id(id) AS id, name, path, repo_url, description, active, <string>created_at AS created_at, <string>updated_at AS updated_at FROM project WHERE id = type::thing('project', $id)")
                .bind(("id", id.to_string()))
                .await
                .context("Failed to query project")
        })?;

        let results: Vec<serde_json::Value> = response.take(0)?;

        if results.is_empty() {
            return Ok(None);
        }

        let obj = &results[0];
        let id_str = obj["id"].as_str().unwrap_or_default().to_string();

        Ok(Some(Project {
            id: id_str,
            name: obj["name"].as_str().unwrap_or_default().to_string(),
            path: obj["path"].as_str().map(|s| s.to_string()),
            repo_url: obj["repo_url"].as_str().map(|s| s.to_string()),
            description: obj["description"].as_str().map(|s| s.to_string()),
            active: obj["active"].as_bool().unwrap_or(true),
            created_at: obj["created_at"].as_str().unwrap_or_default().to_string(),
            updated_at: obj["updated_at"].as_str().unwrap_or_default().to_string(),
        }))
    }

    /// Get agent by ID
    pub fn get_agent(&self, id: &str) -> Result<Option<Agent>> {
        Self::runtime().block_on(self.get_agent_async(id))
    }

    async fn get_agent_async(&self, id: &str) -> Result<Option<Agent>> {
        let mut response = with_db!(self, db, {
            db.query("SELECT meta::id(id) AS id, description, domain, <string>created_at AS created_at, <string>updated_at AS updated_at FROM agent WHERE id = type::thing('agent', $id)")
                .bind(("id", id.to_string()))
                .await
                .context("Failed to query agent")
        })?;

        let results: Vec<serde_json::Value> = response.take(0)?;

        if results.is_empty() {
            return Ok(None);
        }

        let obj = &results[0];
        let id_str = obj["id"].as_str().unwrap_or_default().to_string();

        Ok(Some(Agent {
            id: id_str,
            description: obj["description"].as_str().map(|s| s.to_string()),
            domain: obj["domain"].as_str().map(|s| s.to_string()),
            created_at: obj["created_at"].as_str().map(|s| s.to_string()),
            updated_at: obj["updated_at"].as_str().map(|s| s.to_string()),
        }))
    }

    /// Upsert agent
    pub fn upsert_agent(&self, agent: &Agent) -> Result<()> {
        Self::runtime().block_on(self.upsert_agent_async(agent))
    }

    async fn upsert_agent_async(&self, agent: &Agent) -> Result<()> {
        // Always include datetimes - use current time if not provided
        let now = Utc::now().to_rfc3339();
        let created_at = agent.created_at.clone().unwrap_or_else(|| now.clone());
        let updated_at = agent.updated_at.clone().unwrap_or_else(|| now.clone());

        let mut response = with_db!(self, db, {
            db.query(
                "UPSERT type::thing('agent', $id) SET
                description = $description,
                domain = $domain,
                created_at = <datetime>$created_at,
                updated_at = <datetime>$updated_at
            ",
            )
            .bind(("id", agent.id.clone()))
            .bind(("description", agent.description.clone()))
            .bind(("domain", agent.domain.clone()))
            .bind(("created_at", normalize_datetime(&created_at)))
            .bind(("updated_at", normalize_datetime(&updated_at)))
            .await
            .context("Failed to upsert agent")
        })?;

        // Check for errors in the response
        let errors = response.take_errors();
        if !errors.is_empty() {
            return Err(anyhow::anyhow!("SurrealDB returned errors: {:?}", errors));
        }

        Ok(())
    }

    /// Get tags for a project
    pub fn get_tags_for_project(&self, _project_id: &str) -> Result<Vec<String>> {
        // Not implemented in SurrealDB schema yet
        Ok(vec![])
    }

    /// Set tags for a project
    pub fn set_tags_for_project(&self, _project_id: &str, _tags: &[String]) -> Result<()> {
        // Not implemented in SurrealDB schema yet
        Ok(())
    }

    /// Get applicability for a project
    pub fn get_applicability_for_project(&self, _project_id: &str) -> Result<Vec<String>> {
        // Not implemented in SurrealDB schema yet
        Ok(vec![])
    }

    /// Set applicability for a project
    pub fn set_applicability_for_project(&self, _project_id: &str, _ids: &[String]) -> Result<()> {
        // Not implemented in SurrealDB schema yet
        Ok(())
    }

    // =========================================================================
    // CONTENT PATCH OPERATIONS
    // =========================================================================

    /// Edit content by finding and replacing text
    pub fn edit_content(
        &self,
        id: &str,
        ctx: &crate::store::AgentContext,
        old_text: &str,
        new_text: &str,
        replace_all: bool,
        nth: Option<usize>,
    ) -> Result<crate::store::EditResult> {
        // Fetch entry
        let entry = self
            .get_knowledge(id, ctx)?
            .ok_or_else(|| anyhow::anyhow!("Entry not found: {}", id))?;

        let body = entry
            .body
            .as_ref()
            .ok_or_else(|| anyhow::anyhow!("Entry has no body content"))?;

        // Use shared content operation logic
        let result = crate::content_ops::edit_content(body, old_text, new_text, replace_all, nth)?;

        // Update the entry
        let mut updated = entry;
        let content_hash = KnowledgeEntry::compute_hash(&result.new_content);
        updated.body = Some(result.new_content.clone());
        updated.updated_at = Some(chrono::Utc::now().to_rfc3339());
        updated.content_hash = Some(content_hash);

        self.upsert_knowledge_internal(&updated)?;

        Ok(crate::store::EditResult {
            replacements: result.replacements,
            new_content: result.new_content,
        })
    }

    /// Append content to the end of an entry's body
    pub fn append_content(
        &self,
        id: &str,
        ctx: &crate::store::AgentContext,
        content: &str,
    ) -> Result<()> {
        let entry = self
            .get_knowledge(id, ctx)?
            .ok_or_else(|| anyhow::anyhow!("Entry not found: {}", id))?;

        // Use shared content operation logic
        let new_body = crate::content_ops::append_content(entry.body.as_deref(), content);

        let mut updated = entry;
        let content_hash = KnowledgeEntry::compute_hash(&new_body);
        updated.body = Some(new_body);
        updated.updated_at = Some(chrono::Utc::now().to_rfc3339());
        updated.content_hash = Some(content_hash);

        self.upsert_knowledge_internal(&updated)?;
        Ok(())
    }

    /// Prepend content to the start of an entry's body
    pub fn prepend_content(
        &self,
        id: &str,
        ctx: &crate::store::AgentContext,
        content: &str,
    ) -> Result<()> {
        let entry = self
            .get_knowledge(id, ctx)?
            .ok_or_else(|| anyhow::anyhow!("Entry not found: {}", id))?;

        // Use shared content operation logic
        let new_body = crate::content_ops::prepend_content(entry.body.as_deref(), content);

        let mut updated = entry;
        let content_hash = KnowledgeEntry::compute_hash(&new_body);
        updated.body = Some(new_body);
        updated.updated_at = Some(chrono::Utc::now().to_rfc3339());
        updated.content_hash = Some(content_hash);

        self.upsert_knowledge_internal(&updated)?;
        Ok(())
    }

    /// List tables - SurrealDB uses tables, return table names
    pub fn list_tables(&self) -> Result<Vec<String>> {
        Self::runtime().block_on(self.list_tables_async())
    }

    async fn list_tables_async(&self) -> Result<Vec<String>> {
        let mut response = with_db!(self, db, {
            db.query("INFO FOR DB")
                .await
                .context("Failed to query database info")
        })?;

        // SurrealDB INFO returns complex metadata - take as JSON directly
        let info: Option<serde_json::Value> = response.take(0)?;
        let mut tables = Vec::new();

        if let Some(info_json) = info
            && let Some(tables_obj) = info_json.get("tables").and_then(|v| v.as_object())
        {
            for table_name in tables_obj.keys() {
                tables.push(table_name.clone());
            }
            tables.sort();
        }

        Ok(tables)
    }

    /// Count total knowledge entries
    pub fn count(&self) -> Result<usize> {
        Self::runtime().block_on(self.count_async())
    }

    async fn count_async(&self) -> Result<usize> {
        let mut response = with_db!(self, db, {
            db.query("SELECT count() AS c FROM knowledge GROUP ALL")
                .await
                .context("Failed to count knowledge entries")
        })?;

        let results: Vec<serde_json::Value> = response.take(0)?;
        let count = results.first().and_then(|v| v["c"].as_i64()).unwrap_or(0) as usize;
        Ok(count)
    }

    /// List all knowledge entries
    pub fn list_all(&self, ctx: &crate::store::AgentContext) -> Result<Vec<KnowledgeEntry>> {
        Self::runtime().block_on(self.list_all_async(ctx))
    }

    async fn list_all_async(
        &self,
        ctx: &crate::store::AgentContext,
    ) -> Result<Vec<KnowledgeEntry>> {
        let (visibility_clause, current_agent) = Self::build_visibility_filter(ctx);

        // Convert AND to WHERE for list_all (no WHERE clause exists yet)
        let where_clause = visibility_clause.replacen("AND", "WHERE", 1);

        // ORDER BY id instead of title to avoid SurrealDB query planner
        // selecting BM25 full-text index (knowledge_title_fts) for sort
        // resolution, which crashes with "No iterator has been found".
        // See: coryzibell/mx#191
        let sql = format!(
            "SELECT {}
            FROM knowledge
            {}
            ORDER BY id",
            Self::knowledge_select_fields(),
            where_clause
        );

        let mut response = with_db!(self, db, {
            let mut query = db.query(&sql);
            if let Some(agent) = current_agent {
                query = query.bind(("current_agent", agent));
            }
            query.await.context("Failed to query all knowledge entries")
        })?;

        let results: Vec<serde_json::Value> = response.take(0)?;
        let mut entries = Vec::new();

        for obj in results {
            entries.push(self.value_to_knowledge_entry(obj).await?);
        }

        Ok(entries)
    }

    /// List entries by category
    pub fn list_by_category(
        &self,
        category: &str,
        ctx: &crate::store::AgentContext,
        filter: &crate::store::KnowledgeFilter,
    ) -> Result<Vec<KnowledgeEntry>> {
        Self::runtime().block_on(self.list_by_category_async(category, ctx, filter))
    }

    /// Fast count of entries in a category with the same visibility / resonance
    /// filtering as list_by_category, but returning only the integer count —
    /// no row hydration, no tag/applicability follow-up queries. Used by
    /// `mx memory stats` so it doesn't round-trip thousands of times per call
    /// when the db is remote.
    pub fn count_by_category(
        &self,
        category: &str,
        ctx: &crate::store::AgentContext,
        filter: &crate::store::KnowledgeFilter,
    ) -> Result<usize> {
        Self::runtime().block_on(self.count_by_category_async(category, ctx, filter))
    }

    async fn count_by_category_async(
        &self,
        category: &str,
        ctx: &crate::store::AgentContext,
        filter: &crate::store::KnowledgeFilter,
    ) -> Result<usize> {
        let category_thing = Thing::from(("category", category));
        let (visibility_clause, current_agent) = Self::build_visibility_filter(ctx);
        let resonance_clause = Self::build_resonance_filter(filter);

        // NOTE: `SELECT count() FROM knowledge WHERE ... GROUP ALL` returns
        // the wrong number in SurrealDB 2.6 when a WHERE clause is present
        // (observed on 2.6.1: bloom with visibility='public' reports 986
        // instead of 260 — off by ~3-4x, seemingly counting some join
        // product). Wrapping the filter in a subquery that projects id only
        // gives the correct count and still avoids row hydration.
        let sql = format!(
            "SELECT count() AS c FROM (
                SELECT id FROM knowledge
                WHERE category = $category {} {}
            ) GROUP ALL",
            visibility_clause, resonance_clause
        );

        let mut response = with_db!(self, db, {
            let mut query = db.query(&sql).bind(("category", category_thing));
            if let Some(agent) = current_agent {
                query = query.bind(("current_agent", agent));
            }
            query.await.context("Failed to count knowledge by category")
        })?;

        let results: Vec<serde_json::Value> = response.take(0)?;
        let count = results.first().and_then(|v| v["c"].as_i64()).unwrap_or(0) as usize;
        Ok(count)
    }

    async fn list_by_category_async(
        &self,
        category: &str,
        ctx: &crate::store::AgentContext,
        filter: &crate::store::KnowledgeFilter,
    ) -> Result<Vec<KnowledgeEntry>> {
        let category_thing = Thing::from(("category", category));

        let (visibility_clause, current_agent) = Self::build_visibility_filter(ctx);
        let resonance_clause = Self::build_resonance_filter(filter);

        // ORDER BY id instead of title — see comment in list_all_async
        let sql = format!(
            "SELECT {}
            FROM knowledge
            WHERE category = $category {} {}
            ORDER BY id",
            Self::knowledge_select_fields(),
            visibility_clause,
            resonance_clause
        );

        let mut response = with_db!(self, db, {
            let mut query = db.query(&sql).bind(("category", category_thing));
            if let Some(agent) = current_agent {
                query = query.bind(("current_agent", agent));
            }
            query.await.context("Failed to query knowledge by category")
        })?;

        let results: Vec<serde_json::Value> = response.take(0)?;
        let mut entries = Vec::new();

        for obj in results {
            entries.push(self.value_to_knowledge_entry(obj).await?);
        }

        Ok(entries)
    }

    /// List sessions
    pub fn list_sessions(&self, _project_id: Option<&str>) -> Result<Vec<Session>> {
        // Not fully implemented yet - return empty
        Ok(vec![])
    }

    /// Get session by ID
    pub fn get_session(&self, _id: &str) -> Result<Option<Session>> {
        // Not fully implemented yet
        Ok(None)
    }

    /// Upsert session
    pub fn upsert_session(&self, _session: &Session) -> Result<()> {
        // Not fully implemented yet
        Ok(())
    }

    /// List source types
    pub fn list_source_types(&self) -> Result<Vec<SourceType>> {
        Self::runtime().block_on(self.list_source_types_async())
    }

    async fn list_source_types_async(&self) -> Result<Vec<SourceType>> {
        let mut response = with_db!(self, db, {
            db.query("SELECT meta::id(id) AS id, description, <string>created_at AS created_at FROM source_type ORDER BY id")
                .await
                .context("Failed to list source types")
        })?;

        let results: Vec<serde_json::Value> = response.take(0)?;
        let mut types = Vec::new();

        for obj in results {
            // Parse string from id field
            let id = obj["id"].as_str().unwrap_or_default().to_string();

            types.push(SourceType {
                id,
                description: obj["description"].as_str().unwrap_or_default().to_string(),
                created_at: obj["created_at"].as_str().unwrap_or_default().to_string(),
            });
        }

        Ok(types)
    }

    /// List entry types
    pub fn list_entry_types(&self) -> Result<Vec<EntryType>> {
        Self::runtime().block_on(self.list_entry_types_async())
    }

    async fn list_entry_types_async(&self) -> Result<Vec<EntryType>> {
        let mut response = with_db!(self, db, {
            db.query("SELECT meta::id(id) AS id, description, <string>created_at AS created_at FROM entry_type ORDER BY id")
                .await
                .context("Failed to list entry types")
        })?;

        let results: Vec<serde_json::Value> = response.take(0)?;
        let mut types = Vec::new();

        for obj in results {
            // Parse string from id field
            let id = obj["id"].as_str().unwrap_or_default().to_string();

            types.push(EntryType {
                id,
                description: obj["description"].as_str().unwrap_or_default().to_string(),
                created_at: obj["created_at"].as_str().unwrap_or_default().to_string(),
            });
        }

        Ok(types)
    }

    /// List content types
    pub fn list_content_types(&self) -> Result<Vec<ContentType>> {
        Self::runtime().block_on(self.list_content_types_async())
    }

    async fn list_content_types_async(&self) -> Result<Vec<ContentType>> {
        let mut response = with_db!(self, db, {
            db.query("SELECT meta::id(id) AS id, description, file_extensions, <string>created_at AS created_at FROM content_type ORDER BY id")
                .await
                .context("Failed to list content types")
        })?;

        let results: Vec<serde_json::Value> = response.take(0)?;
        let mut types = Vec::new();

        for obj in results {
            // Parse string from id field
            let id = obj["id"].as_str().unwrap_or_default().to_string();

            // Parse array of file extensions
            let file_extensions = obj["file_extensions"].as_array().map(|arr| {
                arr.iter()
                    .filter_map(|v| v.as_str().map(|s| s.to_string()))
                    .collect()
            });

            types.push(ContentType {
                id,
                description: obj["description"].as_str().unwrap_or_default().to_string(),
                file_extensions,
                created_at: obj["created_at"].as_str().unwrap_or_default().to_string(),
            });
        }

        Ok(types)
    }

    /// List session types
    pub fn list_session_types(&self) -> Result<Vec<SessionType>> {
        Self::runtime().block_on(self.list_session_types_async())
    }

    async fn list_session_types_async(&self) -> Result<Vec<SessionType>> {
        let mut response = with_db!(self, db, {
            db.query("SELECT meta::id(id) AS id, description, <string>created_at AS created_at FROM session_type ORDER BY id")
                .await
                .context("Failed to list session types")
        })?;

        let results: Vec<serde_json::Value> = response.take(0)?;
        let mut types = Vec::new();

        for obj in results {
            // Parse string from id field
            let id = obj["id"].as_str().unwrap_or_default().to_string();

            types.push(SessionType {
                id,
                description: obj["description"].as_str().unwrap_or_default().to_string(),
                created_at: obj["created_at"].as_str().unwrap_or_default().to_string(),
            });
        }

        Ok(types)
    }

    /// List relationship types
    pub fn list_relationship_types(&self) -> Result<Vec<RelationshipType>> {
        Self::runtime().block_on(self.list_relationship_types_async())
    }

    async fn list_relationship_types_async(&self) -> Result<Vec<RelationshipType>> {
        let mut response = with_db!(self, db, {
            db.query("SELECT meta::id(id) AS id, description, directional, <string>created_at AS created_at FROM relationship_type ORDER BY id")
                .await
                .context("Failed to list relationship types")
        })?;

        let results: Vec<serde_json::Value> = response.take(0)?;
        let mut types = Vec::new();

        for obj in results {
            // Parse string from id field
            let id = obj["id"].as_str().unwrap_or_default().to_string();

            types.push(RelationshipType {
                id,
                description: obj["description"].as_str().unwrap_or_default().to_string(),
                directional: obj["directional"].as_bool().unwrap_or(false),
                created_at: obj["created_at"].as_str().unwrap_or_default().to_string(),
            });
        }

        Ok(types)
    }

    // =========================================================================
    // WAKE SESSION OPERATIONS
    // =========================================================================

    /// Create a wake session record, return the session_id
    pub fn create_wake_session(&self, session: &crate::wake_token::WakeSession) -> Result<String> {
        Self::runtime().block_on(self.create_wake_session_async(session))
    }

    async fn create_wake_session_async(
        &self,
        session: &crate::wake_token::WakeSession,
    ) -> Result<String> {
        // Serialize bloom_chunk_meta as a JSON array. The schema field is
        // `flexible array<object>` so SurrealDB will accept arbitrary shape.
        let bloom_chunk_meta_json = serde_json::to_value(&session.bloom_chunk_meta)?;
        let created_at = chrono::DateTime::from_timestamp(session.created_at, 0)
            .unwrap_or_else(chrono::Utc::now)
            .to_rfc3339();

        let mut response = with_db!(self, db, {
            db.query(
                "CREATE type::thing('wake_session', $session_id) SET
                    bloom_ids = $bloom_ids,
                    current_index = $current_index,
                    current_chunk_index = $current_chunk_index,
                    step = $step,
                    attempts_on_current = $attempts_on_current,
                    remembered_count = $remembered_count,
                    needed_help_count = $needed_help_count,
                    skipped_count = $skipped_count,
                    created_at = <datetime>$created_at,
                    bloom_chunk_meta = $bloom_chunk_meta
                ",
            )
            .bind(("session_id", session.session_id.clone()))
            .bind(("bloom_ids", session.bloom_ids.clone()))
            .bind(("current_index", session.current_index as i64))
            .bind(("current_chunk_index", session.current_chunk_index as i64))
            .bind(("step", session.step as i64))
            .bind(("attempts_on_current", session.attempts_on_current as i64))
            .bind(("remembered_count", session.remembered_count as i64))
            .bind(("needed_help_count", session.needed_help_count as i64))
            .bind(("skipped_count", session.skipped_count as i64))
            .bind(("created_at", normalize_datetime(&created_at)))
            .bind(("bloom_chunk_meta", bloom_chunk_meta_json))
            .await
            .context("Failed to create wake session")
        })?;

        let errors = response.take_errors();
        if !errors.is_empty() {
            return Err(anyhow::anyhow!(
                "SurrealDB error creating wake session: {:?}",
                errors
            ));
        }

        Ok(session.session_id.clone())
    }

    /// Get a wake session by ID
    pub fn get_wake_session(
        &self,
        session_id: &str,
    ) -> Result<Option<crate::wake_token::WakeSession>> {
        Self::runtime().block_on(self.get_wake_session_async(session_id))
    }

    async fn get_wake_session_async(
        &self,
        session_id: &str,
    ) -> Result<Option<crate::wake_token::WakeSession>> {
        let mut response = with_db!(self, db, {
            db.query(
                "SELECT
                    meta::id(id) AS session_id,
                    bloom_ids,
                    current_index,
                    current_chunk_index,
                    step,
                    attempts_on_current,
                    remembered_count,
                    needed_help_count,
                    skipped_count,
                    <int>time::unix(<datetime>created_at) AS created_at,
                    bloom_chunk_meta
                FROM type::thing('wake_session', $session_id)",
            )
            .bind(("session_id", session_id.to_string()))
            .await
            .context("Failed to get wake session")
        })?;

        let results: Vec<serde_json::Value> = response.take(0)?;

        if results.is_empty() {
            return Ok(None);
        }

        let obj = &results[0];

        let session_id_str = obj["session_id"].as_str().unwrap_or_default().to_string();
        let bloom_ids: Vec<String> = obj["bloom_ids"]
            .as_array()
            .unwrap_or(&vec![])
            .iter()
            .filter_map(|v| v.as_str().map(|s| s.to_string()))
            .collect();
        let current_index = obj["current_index"].as_u64().unwrap_or(0) as usize;
        // Diffi flagged the previous `as u64 as u16` pattern as a silent-wrap
        // footgun — reaching u16::MAX requires ~2000x default-threshold chunks
        // today but the cast hides the failure mode. `try_from` surfaces an
        // out-of-range stored value as a deserialization error instead of
        // quietly producing a wrong cursor value.
        let raw_chunk_idx = obj["current_chunk_index"].as_u64().unwrap_or(0);
        let current_chunk_index = u16::try_from(raw_chunk_idx).map_err(|_| {
            anyhow::anyhow!(
                "wake_session.current_chunk_index {} exceeds u16::MAX; \
                 session is corrupt or schema has drifted",
                raw_chunk_idx
            )
        })?;
        let step = obj["step"].as_u64().unwrap_or(0) as u32;
        let attempts_on_current = obj["attempts_on_current"].as_u64().unwrap_or(0) as u8;
        let remembered_count = obj["remembered_count"].as_u64().unwrap_or(0) as u32;
        let needed_help_count = obj["needed_help_count"].as_u64().unwrap_or(0) as u32;
        let skipped_count = obj["skipped_count"].as_u64().unwrap_or(0) as u32;
        let created_at = obj["created_at"]
            .as_i64()
            .unwrap_or_else(|| chrono::Utc::now().timestamp());

        // Deserialize bloom_chunk_meta via serde_json. Absent/empty → default
        // to one meta per bloom_id marking every bloom as phraseless (safe
        // fallback that keeps the session walkable via skips).
        let bloom_chunk_meta: Vec<crate::wake_token::BloomChunkMeta> =
            match obj.get("bloom_chunk_meta") {
                Some(v) if !v.is_null() => serde_json::from_value(v.clone()).unwrap_or_default(),
                _ => Vec::new(),
            };
        let bloom_chunk_meta = if bloom_chunk_meta.len() == bloom_ids.len() {
            bloom_chunk_meta
        } else {
            // Length mismatch — rebuild default metadata so the session can
            // at least walk (all phraseless, will drop through skip path).
            bloom_ids
                .iter()
                .map(|_| crate::wake_token::BloomChunkMeta {
                    authored_phrase_count: 0,
                    is_phraseless: true,
                    ..Default::default()
                })
                .collect()
        };

        Ok(Some(crate::wake_token::WakeSession {
            session_id: session_id_str,
            bloom_ids,
            current_index,
            current_chunk_index,
            step,
            attempts_on_current,
            remembered_count,
            needed_help_count,
            skipped_count,
            created_at,
            bloom_chunk_meta,
        }))
    }

    /// Update an existing wake session
    pub fn update_wake_session(&self, session: &crate::wake_token::WakeSession) -> Result<()> {
        Self::runtime().block_on(self.update_wake_session_async(session))
    }

    async fn update_wake_session_async(
        &self,
        session: &crate::wake_token::WakeSession,
    ) -> Result<()> {
        let bloom_chunk_meta_json = serde_json::to_value(&session.bloom_chunk_meta)?;

        let mut response = with_db!(self, db, {
            db.query(
                "UPDATE type::thing('wake_session', $session_id) SET
                    current_index = $current_index,
                    current_chunk_index = $current_chunk_index,
                    step = $step,
                    attempts_on_current = $attempts_on_current,
                    remembered_count = $remembered_count,
                    needed_help_count = $needed_help_count,
                    skipped_count = $skipped_count,
                    bloom_chunk_meta = $bloom_chunk_meta
                ",
            )
            .bind(("session_id", session.session_id.clone()))
            .bind(("current_index", session.current_index as i64))
            .bind(("current_chunk_index", session.current_chunk_index as i64))
            .bind(("step", session.step as i64))
            .bind(("attempts_on_current", session.attempts_on_current as i64))
            .bind(("remembered_count", session.remembered_count as i64))
            .bind(("needed_help_count", session.needed_help_count as i64))
            .bind(("skipped_count", session.skipped_count as i64))
            .bind(("bloom_chunk_meta", bloom_chunk_meta_json))
            .await
            .context("Failed to update wake session")
        })?;

        let errors = response.take_errors();
        if !errors.is_empty() {
            return Err(anyhow::anyhow!(
                "SurrealDB error updating wake session: {:?}",
                errors
            ));
        }

        Ok(())
    }

    /// Delete a wake session
    pub fn delete_wake_session(&self, session_id: &str) -> Result<()> {
        Self::runtime().block_on(self.delete_wake_session_async(session_id))
    }

    async fn delete_wake_session_async(&self, session_id: &str) -> Result<()> {
        let mut response = with_db!(self, db, {
            db.query("DELETE type::thing('wake_session', $session_id)")
                .bind(("session_id", session_id.to_string()))
                .await
                .context("Failed to delete wake session")
        })?;

        let errors = response.take_errors();
        if !errors.is_empty() {
            return Err(anyhow::anyhow!(
                "SurrealDB error deleting wake session: {:?}",
                errors
            ));
        }

        Ok(())
    }
}

// ============================================================================
// KNOWLEDGESTORE TRAIT IMPLEMENTATION
// ============================================================================

impl KnowledgeStore for SurrealDatabase {
    fn upsert_knowledge(&self, entry: &KnowledgeEntry) -> Result<()> {
        self.upsert_knowledge_internal(entry)?;
        Ok(())
    }

    fn get(&self, id: &str, ctx: &crate::store::AgentContext) -> Result<Option<KnowledgeEntry>> {
        self.get_knowledge(id, ctx)
    }

    fn delete(&self, id: &str, ctx: &crate::store::AgentContext) -> Result<bool> {
        self.delete_knowledge(id, ctx)
    }

    fn search(
        &self,
        query: &str,
        ctx: &crate::store::AgentContext,
        filter: &crate::store::KnowledgeFilter,
    ) -> Result<Vec<KnowledgeEntry>> {
        self.search_knowledge(query, ctx, filter)
    }

    fn semantic_search(
        &self,
        query_embedding: &[f32],
        ctx: &crate::store::AgentContext,
        filter: &crate::store::KnowledgeFilter,
        limit: usize,
    ) -> Result<Vec<KnowledgeEntry>> {
        self.semantic_search_knowledge(query_embedding, ctx, filter, limit)
    }

    fn list_by_category(
        &self,
        category: &str,
        ctx: &crate::store::AgentContext,
        filter: &crate::store::KnowledgeFilter,
    ) -> Result<Vec<KnowledgeEntry>> {
        self.list_by_category(category, ctx, filter)
    }

    fn count_by_category(
        &self,
        category: &str,
        ctx: &crate::store::AgentContext,
        filter: &crate::store::KnowledgeFilter,
    ) -> Result<usize> {
        self.count_by_category(category, ctx, filter)
    }

    fn list_all(&self, ctx: &crate::store::AgentContext) -> Result<Vec<KnowledgeEntry>> {
        self.list_all(ctx)
    }

    fn count(&self) -> Result<usize> {
        self.count()
    }

    fn wake_cascade(
        &self,
        ctx: &crate::store::AgentContext,
        limit: usize,
        min_resonance: Option<i32>,
        days: i64,
    ) -> Result<crate::store::WakeCascade> {
        self.wake_cascade(ctx, limit, min_resonance, days)
    }

    fn update_activations(&self, ids: &[String]) -> Result<()> {
        self.update_activations(ids)
    }

    fn update_summary(
        &self,
        id: &str,
        summary: &str,
        ctx: &crate::store::AgentContext,
    ) -> Result<bool> {
        self.update_summary(id, summary, ctx)
    }

    fn increment_activation_count(&self, ids: &[String]) -> Result<()> {
        self.increment_activation_count(ids)
    }

    fn query_recent_facts(&self, days: i32) -> Result<Vec<KnowledgeEntry>> {
        self.query_recent_facts(days)
    }

    fn query_recent_facts_all_types(&self, days: i32) -> Result<Vec<KnowledgeEntry>> {
        self.query_recent_facts_all_types(days)
    }

    fn reinforce(
        &self,
        id: &str,
        amount: i32,
        cap: Option<i32>,
        ctx: &crate::store::AgentContext,
    ) -> Result<Option<crate::store::ReinforcementResult>> {
        self.reinforce(id, amount, cap, ctx)
    }

    fn get_tags_for_entry(&self, entry_id: &str) -> Result<Vec<String>> {
        self.get_tags_for_entry(entry_id)
    }

    fn set_tags_for_entry(&self, entry_id: &str, tags: &[String]) -> Result<()> {
        self.set_tags_for_entry(entry_id, tags)
    }

    fn list_all_tags(&self, category: Option<&str>) -> Result<Vec<String>> {
        self.list_all_tags(category)
    }

    fn get_applicability_for_entry(&self, entry_id: &str) -> Result<Vec<String>> {
        self.get_applicability_for_entry(entry_id)
    }

    fn set_applicability_for_entry(&self, entry_id: &str, ids: &[String]) -> Result<()> {
        self.set_applicability_for_entry(entry_id, ids)
    }

    fn list_applicability_types(&self) -> Result<Vec<ApplicabilityType>> {
        self.list_applicability_types()
    }

    fn upsert_applicability_type(&self, atype: &ApplicabilityType) -> Result<()> {
        self.upsert_applicability_type(atype)
    }

    fn list_categories(&self) -> Result<Vec<Category>> {
        self.list_categories()
    }

    fn get_category(&self, id: &str) -> Result<Option<Category>> {
        self.get_category(id)
    }

    fn upsert_category(&self, category: &Category) -> Result<()> {
        self.upsert_category(category)
    }

    fn delete_category(&self, id: &str) -> Result<bool> {
        self.delete_category(id)
    }

    fn list_projects(&self, _active_only: bool) -> Result<Vec<Project>> {
        // SurrealDB implementation doesn't filter by active yet
        self.list_projects()
    }

    fn get_project(&self, id: &str) -> Result<Option<Project>> {
        self.get_project(id)
    }

    fn upsert_project(&self, project: &Project) -> Result<()> {
        self.upsert_project_internal(project)?;
        Ok(())
    }

    fn get_tags_for_project(&self, project_id: &str) -> Result<Vec<String>> {
        self.get_tags_for_project(project_id)
    }

    fn set_tags_for_project(&self, project_id: &str, tags: &[String]) -> Result<()> {
        self.set_tags_for_project(project_id, tags)
    }

    fn get_applicability_for_project(&self, project_id: &str) -> Result<Vec<String>> {
        self.get_applicability_for_project(project_id)
    }

    fn set_applicability_for_project(&self, project_id: &str, ids: &[String]) -> Result<()> {
        self.set_applicability_for_project(project_id, ids)
    }

    fn list_agents(&self) -> Result<Vec<Agent>> {
        self.list_agents()
    }

    fn get_agent(&self, id: &str) -> Result<Option<Agent>> {
        self.get_agent(id)
    }

    fn upsert_agent(&self, agent: &Agent) -> Result<()> {
        self.upsert_agent(agent)
    }

    fn list_relationships_for_entry(&self, entry_id: &str) -> Result<Vec<Relationship>> {
        self.list_relationships(entry_id)
    }

    fn add_relationship(&self, from: &str, to: &str, rel_type: &str) -> Result<String> {
        self.add_relationship(from, to, rel_type)?;
        // Return a synthetic ID since SurrealDB edge records don't have simple IDs
        Ok(format!("rel-{}-{}", from, to))
    }

    fn delete_relationship(&self, id: &str) -> Result<bool> {
        self.delete_relationship_by_id(id)
    }

    fn get_facts_for_session(&self, session_id: &str) -> Result<Vec<String>> {
        self.get_facts_for_session(session_id)
    }

    fn get_session_for_fact(&self, fact_id: &str) -> Result<Option<String>> {
        self.get_session_for_fact(fact_id)
    }

    fn list_tables(&self) -> Result<Vec<String>> {
        self.list_tables()
    }

    fn list_sessions(&self, project_id: Option<&str>) -> Result<Vec<Session>> {
        self.list_sessions(project_id)
    }

    fn get_session(&self, id: &str) -> Result<Option<Session>> {
        self.get_session(id)
    }

    fn upsert_session(&self, session: &Session) -> Result<()> {
        self.upsert_session(session)
    }

    fn list_source_types(&self) -> Result<Vec<SourceType>> {
        self.list_source_types()
    }

    fn list_entry_types(&self) -> Result<Vec<EntryType>> {
        self.list_entry_types()
    }

    fn list_content_types(&self) -> Result<Vec<ContentType>> {
        self.list_content_types()
    }

    fn list_session_types(&self) -> Result<Vec<SessionType>> {
        self.list_session_types()
    }

    fn list_relationship_types(&self) -> Result<Vec<RelationshipType>> {
        self.list_relationship_types()
    }

    fn edit_content(
        &self,
        id: &str,
        ctx: &crate::store::AgentContext,
        old_text: &str,
        new_text: &str,
        replace_all: bool,
        nth: Option<usize>,
    ) -> Result<crate::store::EditResult> {
        self.edit_content(id, ctx, old_text, new_text, replace_all, nth)
    }

    fn append_content(
        &self,
        id: &str,
        ctx: &crate::store::AgentContext,
        content: &str,
    ) -> Result<()> {
        self.append_content(id, ctx, content)
    }

    fn prepend_content(
        &self,
        id: &str,
        ctx: &crate::store::AgentContext,
        content: &str,
    ) -> Result<()> {
        self.prepend_content(id, ctx, content)
    }

    fn backup_content(
        &self,
        entry: &KnowledgeEntry,
        operation: &str,
        agent: Option<&str>,
    ) -> Result<String> {
        self.backup_content_internal(entry, operation, agent)
    }

    fn list_backups(&self, entry_id: &str) -> Result<Vec<crate::types::MemoryBackup>> {
        self.list_backups_internal(entry_id)
    }

    fn latest_backup(&self, entry_id: &str) -> Result<Option<crate::types::MemoryBackup>> {
        self.latest_backup_internal(entry_id)
    }

    fn purge_backups(&self, entry_id: &str, keep: usize) -> Result<()> {
        self.purge_backups_internal(entry_id, keep)
    }

    fn create_wake_session(&self, session: &crate::wake_token::WakeSession) -> Result<String> {
        self.create_wake_session(session)
    }

    fn get_wake_session(&self, session_id: &str) -> Result<Option<crate::wake_token::WakeSession>> {
        self.get_wake_session(session_id)
    }

    fn update_wake_session(&self, session: &crate::wake_token::WakeSession) -> Result<()> {
        self.update_wake_session(session)
    }

    fn delete_wake_session(&self, session_id: &str) -> Result<()> {
        self.delete_wake_session(session_id)
    }
}

#[cfg(test)]
mod tests {
    use super::*;

    #[test]
    fn test_open_in_memory() {
        // Test that database opens without error
        let _db = SurrealDatabase::open_in_memory().unwrap();
    }

    #[test]
    fn test_schema_applies_without_error() {
        // Opening applies schema - if this succeeds, schema is valid
        let _db = SurrealDatabase::open_in_memory().unwrap();
    }

    #[test]
    fn test_open_with_path() {
        use tempfile::tempdir;

        let temp_dir = tempdir().unwrap();
        let db_path = temp_dir.path().join("test.surreal");

        // Open database at specific path
        let _db = SurrealDatabase::open(&db_path).unwrap();

        // Verify directory was created
        assert!(db_path.exists());
        assert!(db_path.is_dir());
    }

    #[test]
    fn test_upsert_applicability_type_with_datetime() {
        use crate::types::ApplicabilityType;

        let db = SurrealDatabase::open_in_memory().unwrap();

        // Create an applicability type with RFC3339 datetime
        let atype = ApplicabilityType {
            id: "test_type".to_string(),
            description: "Test applicability type".to_string(),
            scope: Some("test".to_string()),
            created_at: "2025-11-29T12:00:00Z".to_string(),
        };

        // Upsert should succeed without datetime parsing errors
        // This was previously failing with: "Found '2025-11-29T...' for field `created_at`, but expected a datetime"
        db.upsert_applicability_type(&atype).unwrap();
    }

    #[test]
    fn test_upsert_project_with_datetime() {
        use crate::types::Project;

        let db = SurrealDatabase::open_in_memory().unwrap();

        // Create a project with RFC3339 datetimes
        let project = Project {
            id: "test_project".to_string(),
            name: "Test Project".to_string(),
            path: Some("/test/path".to_string()),
            repo_url: None,
            description: Some("Test description".to_string()),
            active: true,
            created_at: "2025-11-29T12:00:00Z".to_string(),
            updated_at: "2025-11-29T12:30:00Z".to_string(),
        };

        // Upsert should succeed without datetime parsing errors
        // This was previously failing with: "Found '2025-11-29T...' for field `created_at`, but expected a datetime"
        db.upsert_project(&project).unwrap();
    }

    #[test]
    fn test_upsert_agent_with_datetime() {
        use crate::types::Agent;

        let db = SurrealDatabase::open_in_memory().unwrap();

        // Create an agent with RFC3339 datetimes
        let agent = Agent {
            id: "test_agent".to_string(),
            description: Some("Test agent".to_string()),
            domain: Some("testing".to_string()),
            created_at: Some("2025-11-29T12:00:00Z".to_string()),
            updated_at: Some("2025-11-29T12:30:00Z".to_string()),
        };

        // Upsert should succeed without datetime parsing errors
        // This was previously failing with: "Found '2025-11-29T...' for field `created_at`, but expected a datetime"
        db.upsert_agent(&agent).unwrap();
    }

    // =========================================================================
    // PR #118 EDGE CASE TESTS
    // =========================================================================
    // These tests cover edge cases identified during code review of the
    // memory/fact unification. They ensure robustness of:
    // - Decay formula computation
    // - ID normalization
    // - Thread duplicate detection
    // - Session linkage
    // =========================================================================

    fn make_test_entry(
        id: &str,
        resonance: i32,
        decay_rate: f64,
    ) -> crate::knowledge::KnowledgeEntry {
        use chrono::Utc;
        let now = Utc::now().to_rfc3339();

        crate::knowledge::KnowledgeEntry {
            id: id.to_string(),
            category_id: "test".to_string(),
            title: format!("Test Entry {}", id),
            body: Some("Test body".to_string()),
            summary: None,
            applicability: vec![],
            source_project_id: None,
            source_agent_id: None,
            file_path: None,
            tags: vec![],
            created_at: Some(now.clone()),
            updated_at: Some(now.clone()),
            content_hash: Some("test-hash".to_string()),
            source_type_id: Some("manual".to_string()),
            entry_type_id: Some("primary".to_string()),
            session_id: None,
            ephemeral: false,
            content_type_id: Some("text".to_string()),
            owner: None,
            visibility: "public".to_string(),
            resonance,
            resonance_type: Some("ephemeral".to_string()),
            last_activated: Some(now),
            activation_count: 0,
            decay_rate,
            anchors: vec![],
            wake_phrases: vec![],
            wake_order: None,
            wake_phrase: None,
            embedding: None,
            embedding_model: None,
            embedded_at: None,
            format: "markdown".to_string(),
            effective_resonance: None,
        }
    }

    #[test]
    fn test_id_normalization_double_prefix() {
        // Edge case: IDs that already have "kn-" prefix get doubled during processing
        // Example: "kn-123" -> strip_prefix -> "123" -> add prefix -> "kn-123"
        // But what if someone passes "kn-kn-123"?

        let db = SurrealDatabase::open_in_memory().unwrap();

        // Insert with normal ID
        let entry = make_test_entry("kn-test123", 5, 0.5);
        db.upsert_knowledge(&entry).unwrap();

        // Try to retrieve with double prefix
        let ctx = crate::store::AgentContext::public_only();
        let result = db.get("kn-kn-test123", &ctx).unwrap();

        // Should NOT find it (this is expected behavior - double prefix is invalid)
        assert!(result.is_none(), "Double prefix should not match");

        // But normal retrieval should work
        let result = db.get("kn-test123", &ctx).unwrap();
        assert!(result.is_some(), "Normal prefix should match");
    }

    #[test]
    fn test_id_normalization_case_sensitivity() {
        // Edge case: Are IDs case-sensitive? "KN-123" vs "kn-123"

        let db = SurrealDatabase::open_in_memory().unwrap();

        // Insert with lowercase
        let entry = make_test_entry("kn-test456", 5, 0.5);
        db.upsert_knowledge(&entry).unwrap();

        // Try to retrieve with uppercase
        let ctx = crate::store::AgentContext::public_only();
        let result = db.get("KN-test456", &ctx).unwrap();

        // SurrealDB IDs are case-sensitive, so this should NOT match
        assert!(
            result.is_none(),
            "Uppercase KN should not match lowercase kn"
        );
    }

    #[test]
    fn test_id_normalization_empty_suffix() {
        // Edge case: What happens with just "kn-" and no suffix?

        let db = SurrealDatabase::open_in_memory().unwrap();
        let ctx = crate::store::AgentContext::public_only();

        // Try to get an entry with empty suffix
        let result = db.get("kn-", &ctx);

        // Should handle gracefully (likely return None, not panic)
        assert!(result.is_ok(), "Empty suffix should not panic");
    }

    #[test]
    fn test_id_normalization_no_prefix() {
        // Edge case: What if someone passes just "123" without "kn-"?

        let db = SurrealDatabase::open_in_memory().unwrap();

        // Insert with full ID
        let entry = make_test_entry("kn-test789", 5, 0.5);
        db.upsert_knowledge(&entry).unwrap();

        // Try to retrieve without prefix
        let ctx = crate::store::AgentContext::public_only();
        let result = db.get("test789", &ctx).unwrap();

        // This SHOULD work because strip_prefix returns the original if no prefix found
        // and that gets stored as-is in SurrealDB
        // Actually, the ID gets normalized during insert, so "test789" should find it
        assert!(result.is_some(), "ID without prefix should still match");
    }

    #[test]
    fn test_decay_formula_zero_days() {
        // Edge case: What happens when last_activated is NOW (0 days ago)?
        // Formula: resonance * 0.95^(days / 7)
        // If days = 0: resonance * 0.95^0 = resonance * 1 = resonance

        let db = SurrealDatabase::open_in_memory().unwrap();

        // Create entry with ephemeral type and recent activation
        let entry = make_test_entry("kn-fresh", 10, 0.5);
        db.upsert_knowledge(&entry).unwrap();

        // Query recent facts (should include entries from today)
        let facts = db.query_recent_facts(1).unwrap();

        // Should find the entry
        assert!(!facts.is_empty(), "Should find fresh facts");

        // The effective_resonance should be close to original resonance (no decay yet)
        // We can't directly check the computed value here, but it shouldn't crash
    }

    #[test]
    fn test_decay_formula_negative_days() {
        // Edge case: What if duration::days() returns negative?
        // This shouldn't happen with (now - last_activated), but let's test boundary

        let db = SurrealDatabase::open_in_memory().unwrap();

        // Query with negative days parameter
        let result = db.query_recent_facts(-1);

        // Should handle gracefully (likely return empty or error)
        assert!(result.is_ok(), "Negative days should not panic");
    }

    #[test]
    fn test_decay_formula_extreme_resonance() {
        // Edge case: Resonance can be > 10 for "transcendent" blooms
        // Make sure formula doesn't overflow or break

        let db = SurrealDatabase::open_in_memory().unwrap();

        // Create entry with extreme resonance (like Ori at 13)
        let mut entry = make_test_entry("kn-transcendent", 13, 0.0);
        entry.resonance_type = Some("ephemeral".to_string());
        db.upsert_knowledge(&entry).unwrap();

        // Query recent facts
        let result = db.query_recent_facts(30);

        // Should not crash or overflow
        assert!(
            result.is_ok(),
            "Extreme resonance should not break decay formula"
        );

        let facts = result.unwrap();
        assert!(!facts.is_empty(), "Should find transcendent fact");
    }

    #[test]
    fn test_decay_formula_max_int_resonance() {
        // Edge case: What if resonance is i32::MAX?

        let db = SurrealDatabase::open_in_memory().unwrap();

        // Create entry with maximum resonance
        let mut entry = make_test_entry("kn-maxres", i32::MAX, 0.0);
        entry.resonance_type = Some("ephemeral".to_string());
        db.upsert_knowledge(&entry).unwrap();

        // Query recent facts
        let result = db.query_recent_facts(30);

        // Should handle without overflow
        assert!(result.is_ok(), "MAX resonance should not overflow");
    }

    // =========================================================================
    // TIERED DECAY & BLOOM EXEMPTION TESTS
    // =========================================================================

    #[test]
    fn test_tiered_decay_low_resonance_ephemeral() {
        // Ephemeral entries with resonance <= 3 use 0.90^(weeks) decay rate (10%/week).
        // At 0 days, effective_resonance == resonance. Entry should be returned.

        let db = SurrealDatabase::open_in_memory().unwrap();

        let mut entry = make_test_entry("kn-low-res", 2, 0.0);
        entry.resonance_type = Some("ephemeral".to_string());
        db.upsert_knowledge(&entry).unwrap();

        let result = db.query_recent_facts(7).unwrap();
        assert!(
            !result.is_empty(),
            "Low-resonance ephemeral entry should be returned when freshly created"
        );
    }

    #[test]
    fn test_tiered_decay_mid_resonance_ephemeral() {
        // Ephemeral entries with resonance 4-5 use 0.95^(weeks) decay rate (5%/week).

        let db = SurrealDatabase::open_in_memory().unwrap();

        let mut entry = make_test_entry("kn-mid-res", 5, 0.0);
        entry.resonance_type = Some("ephemeral".to_string());
        db.upsert_knowledge(&entry).unwrap();

        let result = db.query_recent_facts(7).unwrap();
        assert!(
            !result.is_empty(),
            "Mid-resonance ephemeral entry should be returned when freshly created"
        );
    }

    #[test]
    fn test_tiered_decay_high_resonance_ephemeral() {
        // Ephemeral entries with resonance >= 6 use 0.975^(weeks) decay rate (2.5%/week).

        let db = SurrealDatabase::open_in_memory().unwrap();

        let mut entry = make_test_entry("kn-high-res", 7, 0.0);
        entry.resonance_type = Some("ephemeral".to_string());
        db.upsert_knowledge(&entry).unwrap();

        let result = db.query_recent_facts(7).unwrap();
        assert!(
            !result.is_empty(),
            "High-resonance ephemeral entry should be returned when freshly created"
        );
    }

    #[test]
    fn test_tiered_decay_ordering_over_time() {
        // Verify that tiered decay produces different effective_resonance values over time.
        // A low-resonance entry (3, 10%/week) should decay faster than a high-resonance
        // entry (7, 2.5%/week) when both have the same last_activated 30 days ago.
        //
        // After 30 days (~4.3 weeks):
        //   low  (res=3): 3 * 0.90^(30/7) ≈ 3 * 0.64 ≈ 1.9 — below 0.5? No. Well above.
        //   high (res=7): 7 * 0.975^(30/7) ≈ 7 * 0.87 ≈ 6.1
        // High should rank higher. Both should pass the > 0.5 filter.
        use chrono::Utc;

        let db = SurrealDatabase::open_in_memory().unwrap();

        // Backdate last_activated by 30 days so decay has measurably occurred
        let thirty_days_ago = (Utc::now() - chrono::Duration::days(30)).to_rfc3339();

        let mut low = make_test_entry("kn-decay-low", 3, 0.0);
        low.resonance_type = Some("ephemeral".to_string());
        low.last_activated = Some(thirty_days_ago.clone());
        db.upsert_knowledge(&low).unwrap();

        let mut high = make_test_entry("kn-decay-high", 7, 0.0);
        high.resonance_type = Some("ephemeral".to_string());
        high.last_activated = Some(thirty_days_ago);
        db.upsert_knowledge(&high).unwrap();

        // Query over 60 days so both entries fall within the window
        let results = db.query_recent_facts(60).unwrap();

        // Both entries should survive the > 0.5 filter
        let low_found = results.iter().any(|e| e.id == "kn-decay-low");
        let high_found = results.iter().any(|e| e.id == "kn-decay-high");
        assert!(
            low_found,
            "Low-resonance entry should still pass > 0.5 filter after 30 days"
        );
        assert!(
            high_found,
            "High-resonance entry should pass > 0.5 filter after 30 days"
        );

        // Results are ordered by effective_resonance DESC — high-res should appear first
        let low_pos = results.iter().position(|e| e.id == "kn-decay-low").unwrap();
        let high_pos = results
            .iter()
            .position(|e| e.id == "kn-decay-high")
            .unwrap();
        assert!(
            high_pos < low_pos,
            "High-resonance entry (slower decay) should rank above low-resonance entry after 30 days"
        );
    }

    #[test]
    fn test_bloom_exemption_foundational() {
        // Foundational entries are exempt from decay: effective_resonance == resonance.
        // They should NOT appear in query_recent_facts (which filters resonance_type = 'ephemeral'),
        // but should be directly retrievable.

        let db = SurrealDatabase::open_in_memory().unwrap();

        let mut entry = make_test_entry("kn-foundational", 9, 0.0);
        entry.resonance_type = Some("foundational".to_string());
        db.upsert_knowledge(&entry).unwrap();

        // query_recent_facts only returns ephemeral — foundational should NOT appear here
        let ephemeral_results = db.query_recent_facts(30).unwrap();
        let found_in_ephemeral = ephemeral_results.iter().any(|e| e.id == "kn-foundational");
        assert!(
            !found_in_ephemeral,
            "Foundational entry should not appear in ephemeral fact query"
        );

        // Should still be accessible via direct get
        let ctx = crate::store::AgentContext::public_only();
        let direct = db.get("kn-foundational", &ctx).unwrap();
        assert!(
            direct.is_some(),
            "Foundational entry should be directly retrievable"
        );
    }

    #[test]
    fn test_bloom_exemption_transformative() {
        // Transformative entries are exempt from decay, same as foundational.

        let db = SurrealDatabase::open_in_memory().unwrap();

        let mut entry = make_test_entry("kn-transformative", 8, 0.0);
        entry.resonance_type = Some("transformative".to_string());
        db.upsert_knowledge(&entry).unwrap();

        // Should NOT appear in ephemeral query
        let ephemeral_results = db.query_recent_facts(30).unwrap();
        let found_in_ephemeral = ephemeral_results
            .iter()
            .any(|e| e.id == "kn-transformative");
        assert!(
            !found_in_ephemeral,
            "Transformative entry should not appear in ephemeral fact query"
        );

        let ctx = crate::store::AgentContext::public_only();
        let direct = db.get("kn-transformative", &ctx).unwrap();
        assert!(
            direct.is_some(),
            "Transformative entry should be directly retrievable"
        );
    }

    #[test]
    fn test_increment_activation_count_no_timestamp_reset() {
        // increment_activation_count should bump activation_count but leave
        // last_activated unchanged.

        let db = SurrealDatabase::open_in_memory().unwrap();

        let entry = make_test_entry("kn-incr-test", 5, 0.0);
        db.upsert_knowledge(&entry).unwrap();

        let ctx = crate::store::AgentContext::public_only();

        // Record initial state
        let before = db.get("kn-incr-test", &ctx).unwrap().unwrap();
        let initial_count = before.activation_count;
        let initial_last_activated = before.last_activated.clone();

        // Increment count only
        db.increment_activation_count(&["kn-incr-test".to_string()])
            .unwrap();

        let after = db.get("kn-incr-test", &ctx).unwrap().unwrap();

        assert_eq!(
            after.activation_count,
            initial_count + 1,
            "activation_count should increment by 1"
        );

        assert_eq!(
            after.last_activated, initial_last_activated,
            "last_activated should not be reset by increment_activation_count"
        );
    }

    #[test]
    fn test_thread_duplicate_detection() {
        // Edge case: How does duplicate detection work with normalized content?
        // KnowledgeEntry::normalize_content() is used for fuzzy matching

        let db = SurrealDatabase::open_in_memory().unwrap();

        // Create two entries with similar content but different formatting
        let entry1 = make_test_entry("kn-thread1", 5, 0.5);
        let mut entry2 = make_test_entry("kn-thread2", 5, 0.5);
        entry2.body = Some("  TEST   BODY  ".to_string()); // Different whitespace

        db.upsert_knowledge(&entry1).unwrap();
        db.upsert_knowledge(&entry2).unwrap();

        // Both should be stored (deduplication happens at application level, not DB)
        let ctx = crate::store::AgentContext::public_only();
        assert!(db.get("kn-thread1", &ctx).unwrap().is_some());
        assert!(db.get("kn-thread2", &ctx).unwrap().is_some());
    }

    #[test]
    fn test_session_linkage_round_trip() {
        // Edge case: Can we link a fact to a session and retrieve it back?

        let db = SurrealDatabase::open_in_memory().unwrap();

        // Create a session entry
        let session = make_test_entry("kn-session123", 0, 0.0);
        db.upsert_knowledge(&session).unwrap();

        // Create a fact linked to that session
        let mut fact = make_test_entry("kn-fact456", 5, 0.5);
        fact.session_id = Some("kn-session123".to_string());
        db.upsert_knowledge(&fact).unwrap();

        // Create relationship
        db.add_relationship("kn-fact456", "kn-session123", "extracted_from")
            .unwrap();

        // Query facts for session
        let facts = db.get_facts_for_session("kn-session123").unwrap();

        // Should find the linked fact
        assert_eq!(facts.len(), 1, "Should find one fact for session");
        assert_eq!(
            facts[0], "kn-fact456",
            "Should return full fact ID with prefix"
        );

        // Reverse lookup: get session for fact
        let session_id = db.get_session_for_fact("kn-fact456").unwrap();
        assert!(session_id.is_some(), "Should find session for fact");
        assert_eq!(
            session_id.unwrap(),
            "kn-session123",
            "Should return full session ID with prefix"
        );
    }

    #[test]
    fn test_session_linkage_multiple_facts() {
        // Edge case: Multiple facts from same session

        let db = SurrealDatabase::open_in_memory().unwrap();

        // Create session
        let session = make_test_entry("kn-multisession", 0, 0.0);
        db.upsert_knowledge(&session).unwrap();

        // Create multiple facts
        for i in 1..=5 {
            let mut fact = make_test_entry(&format!("kn-fact{}", i), 5, 0.5);
            fact.session_id = Some("kn-multisession".to_string());
            db.upsert_knowledge(&fact).unwrap();
            db.add_relationship(
                &format!("kn-fact{}", i),
                "kn-multisession",
                "extracted_from",
            )
            .unwrap();
        }

        // Query facts for session
        let facts = db.get_facts_for_session("kn-multisession").unwrap();

        // Should find all 5 facts
        assert_eq!(facts.len(), 5, "Should find all 5 facts for session");
    }

    #[test]
    fn test_session_linkage_orphaned_fact() {
        // Edge case: Fact with session_id but no relationship

        let db = SurrealDatabase::open_in_memory().unwrap();

        // Create fact with session_id but don't create relationship
        let mut fact = make_test_entry("kn-orphan", 5, 0.5);
        fact.session_id = Some("kn-ghost".to_string());
        db.upsert_knowledge(&fact).unwrap();

        // Query for session that doesn't exist
        let facts = db.get_facts_for_session("kn-ghost").unwrap();

        // Should return empty (relationship is what matters, not just session_id field)
        assert_eq!(
            facts.len(),
            0,
            "Orphaned fact should not appear without relationship"
        );

        // Reverse lookup should also fail
        let session = db.get_session_for_fact("kn-orphan").unwrap();
        assert!(session.is_none(), "Orphaned fact should have no session");
    }

    #[test]
    fn test_normalize_content_edge_cases() {
        // Test the normalize_content function used for thread matching
        use crate::knowledge::KnowledgeEntry;

        // Empty string
        assert_eq!(KnowledgeEntry::normalize_content(""), "");

        // Only whitespace
        assert_eq!(KnowledgeEntry::normalize_content("   \n\t  "), "");

        // Unicode characters
        let unicode = "Hello 世界! Привет мир!";
        let normalized = KnowledgeEntry::normalize_content(unicode);
        assert!(normalized.contains("hello"), "Should lowercase ASCII");
        assert!(normalized.contains("世界"), "Should preserve unicode");

        // Multiple spaces and newlines
        let messy = "  hello\n\n  world\t\ttest  ";
        assert_eq!(KnowledgeEntry::normalize_content(messy), "hello world test");
    }

    #[test]
    fn test_wake_cascade_empty_anchors() {
        // Edge case: What if a bloom has empty anchors array?

        let db = SurrealDatabase::open_in_memory().unwrap();
        let ctx = crate::store::AgentContext::public_only();

        // Create bloom with no anchors
        let mut entry = make_test_entry("kn-solo", 9, 0.0);
        entry.resonance_type = Some("foundational".to_string());
        entry.anchors = vec![];
        db.upsert_knowledge(&entry).unwrap();

        // Query wake cascade
        let cascade = db.wake_cascade(&ctx, 50, Some(7), 7).unwrap();

        // Should still include the entry in core (high resonance)
        assert!(!cascade.core.is_empty(), "Should find core bloom");
        // Bridges might be empty since no anchors
        // This is expected behavior
    }

    #[test]
    fn test_wake_cascade_circular_anchors() {
        // Edge case: What if bloom A anchors to B, and B anchors to A?

        let db = SurrealDatabase::open_in_memory().unwrap();

        // Create two blooms that reference each other
        let mut bloom_a = make_test_entry("kn-circular-a", 9, 0.0);
        bloom_a.resonance_type = Some("foundational".to_string());
        bloom_a.anchors = vec!["kn-circular-b".to_string()];

        let mut bloom_b = make_test_entry("kn-circular-b", 9, 0.0);
        bloom_b.resonance_type = Some("foundational".to_string());
        bloom_b.anchors = vec!["kn-circular-a".to_string()];

        db.upsert_knowledge(&bloom_a).unwrap();
        db.upsert_knowledge(&bloom_b).unwrap();

        // Query wake cascade
        let ctx = crate::store::AgentContext::public_only();
        let result = db.wake_cascade(&ctx, 50, Some(7), 7);

        // Should handle circular references without infinite loop
        assert!(
            result.is_ok(),
            "Circular anchors should not cause infinite loop"
        );
    }

    #[test]
    fn test_privacy_filtering_public_only() {
        // Edge case: Public-only context should not see private entries

        let db = SurrealDatabase::open_in_memory().unwrap();

        // Create public entry
        let public_entry = make_test_entry("kn-public", 5, 0.5);
        db.upsert_knowledge(&public_entry).unwrap();

        // Create private entry
        let mut private_entry = make_test_entry("kn-private", 5, 0.5);
        private_entry.visibility = "private".to_string();
        private_entry.owner = Some("test_agent".to_string());
        db.upsert_knowledge(&private_entry).unwrap();

        // Query with public-only context
        let ctx = crate::store::AgentContext::public_only();

        // Should see public
        assert!(
            db.get("kn-public", &ctx).unwrap().is_some(),
            "Should see public entry"
        );

        // Should NOT see private
        assert!(
            db.get("kn-private", &ctx).unwrap().is_none(),
            "Should not see private entry"
        );
    }

    #[test]
    fn test_privacy_filtering_agent_context() {
        // Edge case: Agent should see their own private entries

        let db = SurrealDatabase::open_in_memory().unwrap();

        // Create private entry for test_agent
        let mut private_entry = make_test_entry("kn-my-private", 5, 0.5);
        private_entry.visibility = "private".to_string();
        private_entry.owner = Some("test_agent".to_string());
        db.upsert_knowledge(&private_entry).unwrap();

        // Create private entry for other_agent
        let mut other_entry = make_test_entry("kn-other-private", 5, 0.5);
        other_entry.visibility = "private".to_string();
        other_entry.owner = Some("other_agent".to_string());
        db.upsert_knowledge(&other_entry).unwrap();

        // Query as test_agent
        let ctx = crate::store::AgentContext::for_agent("test_agent");

        // Should see own private entry
        assert!(
            db.get("kn-my-private", &ctx).unwrap().is_some(),
            "Should see own private entry"
        );

        // Should NOT see other agent's private entry
        assert!(
            db.get("kn-other-private", &ctx).unwrap().is_none(),
            "Should not see other's private entry"
        );
    }

    // =========================================================================
    // CROSS-AGENT VISIBILITY BYPASS TESTS (PR #186 / PR #187)
    // =========================================================================
    // These tests prove that the visibility filter on delete and update_summary
    // prevents cross-agent operations on private entries. Agent-b must not be
    // able to delete or update_summary on agent-a's private entries.

    #[test]
    fn test_delete_cross_agent_visibility_blocked() {
        // PR #186: delete must respect visibility. Agent-b cannot delete
        // agent-a's private entry.
        let db = SurrealDatabase::open_in_memory().unwrap();

        // Agent-a creates a private entry
        let mut entry = make_test_entry("kn-private-del-target", 5, 0.0);
        entry.visibility = "private".to_string();
        entry.owner = Some("agent-a".to_string());
        db.upsert_knowledge(&entry).unwrap();

        // Agent-b attempts to delete it
        let ctx_b = crate::store::AgentContext::for_agent("agent-b");
        let result = db.delete("kn-private-del-target", &ctx_b).unwrap();
        assert!(
            !result,
            "agent-b should not be able to delete agent-a's private entry"
        );

        // Verify entry still exists for agent-a
        let ctx_a = crate::store::AgentContext::for_agent("agent-a");
        let still_exists = db.get("kn-private-del-target", &ctx_a).unwrap();
        assert!(
            still_exists.is_some(),
            "Entry should still exist for agent-a after failed cross-agent delete"
        );
    }

    #[test]
    fn test_update_summary_cross_agent_visibility_blocked() {
        // This branch's fix: update_summary must respect visibility.
        // Agent-b cannot update the summary of agent-a's private entry.
        let db = SurrealDatabase::open_in_memory().unwrap();

        // Agent-a creates a private entry with a summary
        let mut entry = make_test_entry("kn-private-summary-target", 5, 0.0);
        entry.visibility = "private".to_string();
        entry.owner = Some("agent-a".to_string());
        entry.summary = Some(r#"{"state":"open"}"#.to_string());
        db.upsert_knowledge(&entry).unwrap();

        // Agent-b attempts to update the summary
        let ctx_b = crate::store::AgentContext::for_agent("agent-b");
        let result = db
            .update_summary(
                "kn-private-summary-target",
                r#"{"state":"compromised"}"#,
                &ctx_b,
            )
            .unwrap();
        assert!(
            !result,
            "agent-b should not be able to update summary on agent-a's private entry"
        );

        // Verify the original summary is unchanged for agent-a
        let ctx_a = crate::store::AgentContext::for_agent("agent-a");
        let unchanged = db
            .get("kn-private-summary-target", &ctx_a)
            .unwrap()
            .unwrap();
        let summary: serde_json::Value =
            serde_json::from_str(unchanged.summary.as_deref().unwrap()).unwrap();
        assert_eq!(
            summary["state"], "open",
            "Summary should be unchanged after failed cross-agent update"
        );
    }
    #[test]
    fn test_reinforce_basic() {
        // Test basic reinforcement functionality
        let db = SurrealDatabase::open_in_memory().unwrap();
        let ctx = crate::store::AgentContext::public_only();

        // Create an entry with resonance 5
        let mut entry = make_test_entry("kn-test-reinforce", 5, 0.0);
        entry.activation_count = 10;
        db.upsert_knowledge(&entry).unwrap();

        // Reinforce by 2, with cap of 10
        let result = db
            .reinforce("kn-test-reinforce", 2, Some(10), &ctx)
            .unwrap()
            .expect("reinforce should return Some for visible entry");

        // Verify results
        assert_eq!(result.id, "kn-test-reinforce");
        assert_eq!(result.old_resonance, 5);
        assert_eq!(result.new_resonance, 7);
        assert_eq!(result.amount_added, 2);
        assert!(!result.capped);
        assert_eq!(result.activation_count, 11);

        // Verify the entry was actually updated
        let updated = db.get("kn-test-reinforce", &ctx).unwrap().unwrap();
        assert_eq!(updated.resonance, 7);
        assert_eq!(updated.activation_count, 11);
        assert!(updated.last_activated.is_some());
    }

    #[test]
    fn test_reinforce_with_cap() {
        // Test that cap is enforced
        let db = SurrealDatabase::open_in_memory().unwrap();
        let ctx = crate::store::AgentContext::public_only();

        // Create an entry with resonance 9
        let entry = make_test_entry("kn-test-cap", 9, 0.0);
        db.upsert_knowledge(&entry).unwrap();

        // Try to reinforce by 5, but cap at 10
        let result = db
            .reinforce("kn-test-cap", 5, Some(10), &ctx)
            .unwrap()
            .expect("reinforce should return Some for visible entry");

        // Should be capped at 10
        assert_eq!(result.old_resonance, 9);
        assert_eq!(result.new_resonance, 10);
        assert_eq!(result.amount_added, 5);
        assert!(result.capped);

        // Verify the entry was capped
        let updated = db.get("kn-test-cap", &ctx).unwrap().unwrap();
        assert_eq!(updated.resonance, 10);
    }

    #[test]
    fn test_reinforce_without_cap() {
        // Test reinforcement without a cap (for transcendent blooms)
        let db = SurrealDatabase::open_in_memory().unwrap();
        let ctx = crate::store::AgentContext::public_only();

        // Create an entry with resonance 9
        let entry = make_test_entry("kn-test-no-cap", 9, 0.0);
        db.upsert_knowledge(&entry).unwrap();

        // Reinforce by 5 with no cap
        let result = db
            .reinforce("kn-test-no-cap", 5, None, &ctx)
            .unwrap()
            .expect("reinforce should return Some for visible entry");

        // Should go above 10
        assert_eq!(result.old_resonance, 9);
        assert_eq!(result.new_resonance, 14);
        assert!(!result.capped);

        // Verify the entry was updated
        let updated = db.get("kn-test-no-cap", &ctx).unwrap().unwrap();
        assert_eq!(updated.resonance, 14);
    }

    #[test]
    fn test_reinforce_nonexistent() {
        // Test that reinforcing a nonexistent entry returns None
        let db = SurrealDatabase::open_in_memory().unwrap();
        let ctx = crate::store::AgentContext::public_only();

        let result = db.reinforce("kn-nonexistent", 1, Some(10), &ctx).unwrap();
        assert!(
            result.is_none(),
            "reinforce should return None for nonexistent entry"
        );
    }

    #[test]
    fn test_reinforce_id_normalization() {
        // Test that ID normalization works
        let db = SurrealDatabase::open_in_memory().unwrap();
        let ctx = crate::store::AgentContext::public_only();

        // Create entry with full ID
        let entry = make_test_entry("kn-test-norm", 5, 0.0);
        db.upsert_knowledge(&entry).unwrap();

        // Reinforce with partial ID (no "kn-" prefix)
        let result = db
            .reinforce("test-norm", 2, Some(10), &ctx)
            .unwrap()
            .expect("reinforce should return Some for visible entry");

        // Should normalize correctly
        assert_eq!(result.id, "kn-test-norm");
        assert_eq!(result.new_resonance, 7);
    }

    #[test]
    fn test_reinforce_cross_agent_visibility_blocked() {
        // Fix #157: reinforce must respect visibility.
        // Agent-b cannot reinforce agent-a's private entry.
        let db = SurrealDatabase::open_in_memory().unwrap();

        // Agent-a creates a private entry with known resonance
        let mut entry = make_test_entry("kn-private-reinforce-target", 5, 0.0);
        entry.visibility = "private".to_string();
        entry.owner = Some("agent-a".to_string());
        entry.activation_count = 3;
        db.upsert_knowledge(&entry).unwrap();

        // Agent-b attempts to reinforce it
        let ctx_b = crate::store::AgentContext::for_agent("agent-b");
        let result = db
            .reinforce("kn-private-reinforce-target", 2, Some(10), &ctx_b)
            .unwrap();
        assert!(
            result.is_none(),
            "agent-b should not be able to reinforce agent-a's private entry"
        );

        // Verify the entry is unchanged for agent-a
        let ctx_a = crate::store::AgentContext::for_agent("agent-a");
        let unchanged = db
            .get("kn-private-reinforce-target", &ctx_a)
            .unwrap()
            .unwrap();
        assert_eq!(
            unchanged.resonance, 5,
            "Resonance should be unchanged after failed cross-agent reinforce"
        );
        assert_eq!(
            unchanged.activation_count, 3,
            "Activation count should be unchanged after failed cross-agent reinforce"
        );
    }

    #[test]
    fn test_reinforce_own_private_entry() {
        // Agent-a should be able to reinforce their own private entry
        let db = SurrealDatabase::open_in_memory().unwrap();

        // Agent-a creates a private entry
        let mut entry = make_test_entry("kn-private-reinforce-own", 5, 0.0);
        entry.visibility = "private".to_string();
        entry.owner = Some("agent-a".to_string());
        entry.activation_count = 3;
        db.upsert_knowledge(&entry).unwrap();

        // Agent-a reinforces their own entry
        let ctx_a = crate::store::AgentContext::for_agent("agent-a");
        let result = db
            .reinforce("kn-private-reinforce-own", 2, Some(10), &ctx_a)
            .unwrap()
            .expect("agent-a should be able to reinforce their own private entry");

        assert_eq!(result.old_resonance, 5);
        assert_eq!(result.new_resonance, 7);
        assert_eq!(result.activation_count, 4);

        // Verify it actually persisted
        let updated = db.get("kn-private-reinforce-own", &ctx_a).unwrap().unwrap();
        assert_eq!(updated.resonance, 7);
        assert_eq!(updated.activation_count, 4);
    }

    #[test]
    fn test_update_summary_persists() {
        // Regression: thread_closed handler modified summary in memory but
        // upsert_knowledge() silently failed on SCHEMAFULL tables. The new
        // update_summary() path must actually persist the change.
        let db = SurrealDatabase::open_in_memory().unwrap();
        let ctx = crate::store::AgentContext::public_only();

        // Create entry with initial summary (simulating an open thread)
        let mut entry = make_test_entry("kn-summary-test", 5, 0.0);
        entry.summary = Some(r#"{"state":"open","topic":"test thread"}"#.to_string());
        db.upsert_knowledge(&entry).unwrap();

        // Update summary to closed state (mirrors thread_closed handler)
        let new_summary = r#"{"state":"closed","topic":"test thread"}"#;
        let result = db
            .update_summary("kn-summary-test", new_summary, &ctx)
            .unwrap();
        assert!(
            result,
            "update_summary should return true for visible entry"
        );

        // Read it back and verify the change persisted
        let updated = db.get("kn-summary-test", &ctx).unwrap().unwrap();
        let summary: serde_json::Value =
            serde_json::from_str(updated.summary.as_deref().unwrap()).unwrap();
        assert_eq!(summary["state"], "closed");
        assert_eq!(summary["topic"], "test thread");
    }

    #[test]
    fn test_update_summary_id_normalization() {
        // update_summary should accept IDs with or without "kn-" prefix,
        // consistent with get(), delete(), reinforce(), etc.
        let db = SurrealDatabase::open_in_memory().unwrap();
        let ctx = crate::store::AgentContext::public_only();

        let mut entry = make_test_entry("kn-summary-norm", 5, 0.0);
        entry.summary = Some(r#"{"state":"open"}"#.to_string());
        db.upsert_knowledge(&entry).unwrap();

        // Update using raw ID (no prefix) - should still work
        let result = db
            .update_summary("summary-norm", r#"{"state":"closed"}"#, &ctx)
            .unwrap();
        assert!(result, "update_summary should return true with raw ID");

        let updated = db.get("kn-summary-norm", &ctx).unwrap().unwrap();
        let summary: serde_json::Value =
            serde_json::from_str(updated.summary.as_deref().unwrap()).unwrap();
        assert_eq!(summary["state"], "closed");

        // Update using prefixed ID - should also work
        let result2 = db
            .update_summary("kn-summary-norm", r#"{"state":"reopened"}"#, &ctx)
            .unwrap();
        assert!(
            result2,
            "update_summary should return true with prefixed ID"
        );

        let updated2 = db.get("kn-summary-norm", &ctx).unwrap().unwrap();
        let summary2: serde_json::Value =
            serde_json::from_str(updated2.summary.as_deref().unwrap()).unwrap();
        assert_eq!(summary2["state"], "reopened");
    }

    #[test]
    fn test_close_thread_with_no_summary() {
        // A thread entry with no summary (pre-convention) should accept a
        // closed-state summary written by the thread_closed handler.
        let db = SurrealDatabase::open_in_memory().unwrap();
        let ctx = crate::store::AgentContext::public_only();

        // Create a thread entry with no summary (pre-convention style)
        let mut entry = make_test_entry("kn-no-summary-thread", 5, 0.0);
        entry.summary = None;
        db.upsert_knowledge(&entry).unwrap();

        // The thread_closed handler writes the closed state via update_summary
        let closed_summary = r#"{"state":"closed","topic":"pre-convention thread"}"#;
        let result = db
            .update_summary("kn-no-summary-thread", closed_summary, &ctx)
            .unwrap();
        assert!(
            result,
            "update_summary should return true for entry with no prior summary"
        );

        // Verify the state persisted correctly
        let updated = db.get("kn-no-summary-thread", &ctx).unwrap().unwrap();
        let summary: serde_json::Value =
            serde_json::from_str(updated.summary.as_deref().unwrap()).unwrap();
        assert_eq!(summary["state"], "closed");
        assert_eq!(summary["topic"], "pre-convention thread");
    }

    #[test]
    fn test_get_summary_state_returns_none_for_no_summary() {
        // Confirms the get_summary_state() helper returns None for entries
        // with no summary — the condition that find_open_thread_by_content
        // treats as "potentially open" (pre-convention threads).
        let entry = make_test_entry("kn-state-none", 5, 0.0);
        // make_test_entry sets summary: None by default
        assert!(
            entry.summary.is_none(),
            "make_test_entry should produce summary: None"
        );
        assert_eq!(
            entry.get_summary_state(),
            None,
            "get_summary_state() must return None when summary is absent"
        );
    }

    #[test]
    fn test_query_recent_facts_all_types_includes_foundational() {
        // query_recent_facts_all_types should return foundational entries that would
        // be excluded from query_recent_facts (ephemeral-only).

        let db = SurrealDatabase::open_in_memory().unwrap();

        let mut foundational = make_test_entry("kn-all-types-foundational", 9, 0.0);
        foundational.resonance_type = Some("foundational".to_string());
        db.upsert_knowledge(&foundational).unwrap();

        let mut ephemeral = make_test_entry("kn-all-types-ephemeral", 5, 0.0);
        ephemeral.resonance_type = Some("ephemeral".to_string());
        db.upsert_knowledge(&ephemeral).unwrap();

        // Baseline: ephemeral-only query should not include foundational
        let ephemeral_results = db.query_recent_facts(30).unwrap();
        assert!(
            !ephemeral_results
                .iter()
                .any(|e| e.id == "kn-all-types-foundational"),
            "Foundational entry should not appear in ephemeral-only query"
        );
        assert!(
            ephemeral_results
                .iter()
                .any(|e| e.id == "kn-all-types-ephemeral"),
            "Ephemeral entry should appear in ephemeral-only query"
        );

        // All-types query should include both
        let all_results = db.query_recent_facts_all_types(30).unwrap();
        assert!(
            all_results
                .iter()
                .any(|e| e.id == "kn-all-types-foundational"),
            "Foundational entry should appear in all-types query"
        );
        assert!(
            all_results.iter().any(|e| e.id == "kn-all-types-ephemeral"),
            "Ephemeral entry should appear in all-types query"
        );
    }

    #[test]
    fn test_query_recent_facts_all_types_includes_transformative() {
        // query_recent_facts_all_types should return transformative entries.

        let db = SurrealDatabase::open_in_memory().unwrap();

        let mut transformative = make_test_entry("kn-all-types-transformative", 8, 0.0);
        transformative.resonance_type = Some("transformative".to_string());
        db.upsert_knowledge(&transformative).unwrap();

        let all_results = db.query_recent_facts_all_types(30).unwrap();
        assert!(
            all_results
                .iter()
                .any(|e| e.id == "kn-all-types-transformative"),
            "Transformative entry should appear in all-types query"
        );
    }

    #[test]
    fn test_query_recent_facts_all_types_respects_decay_threshold() {
        // Entries with near-zero effective resonance (very old, low base) should
        // be excluded even from the all-types query (threshold > 0.5).

        let db = SurrealDatabase::open_in_memory().unwrap();

        // Resonance 1 with heavy decay (80 weeks ago equivalent = decay_rate abuse).
        // We simulate a very old entry by setting last_activated far in the past.
        // For this test we just confirm high-resonance entries are returned.
        let mut high = make_test_entry("kn-all-types-high", 8, 0.0);
        high.resonance_type = Some("ephemeral".to_string());
        db.upsert_knowledge(&high).unwrap();

        let results = db.query_recent_facts_all_types(30).unwrap();
        assert!(
            results.iter().any(|e| e.id == "kn-all-types-high"),
            "High-resonance ephemeral entry should appear in all-types query"
        );
    }

    // =========================================================================
    // list_all_tags TESTS (PR #147)
    // =========================================================================

    fn make_tagged_entry(
        id: &str,
        category: &str,
        tags: Vec<String>,
    ) -> crate::knowledge::KnowledgeEntry {
        let mut entry = make_test_entry(id, 5, 0.0);
        entry.category_id = category.to_string();
        entry.tags = tags;
        entry
    }

    #[test]
    fn test_list_all_tags_returns_distinct_tags() {
        let db = SurrealDatabase::open_in_memory().unwrap();

        let entry1 = make_tagged_entry(
            "kn-tag1",
            "pattern",
            vec!["rust".to_string(), "async".to_string()],
        );
        db.upsert_knowledge(&entry1).unwrap();

        let entry2 = make_tagged_entry(
            "kn-tag2",
            "technique",
            vec!["rust".to_string(), "error-handling".to_string()],
        );
        db.upsert_knowledge(&entry2).unwrap();

        let tags = db.list_all_tags(None).unwrap();
        assert_eq!(tags.len(), 3);
        assert_eq!(tags, vec!["async", "error-handling", "rust"]);
    }

    #[test]
    fn test_list_all_tags_with_category_filter() {
        let db = SurrealDatabase::open_in_memory().unwrap();

        let entry1 = make_tagged_entry(
            "kn-tag3",
            "pattern",
            vec!["rust".to_string(), "async".to_string()],
        );
        db.upsert_knowledge(&entry1).unwrap();

        let entry2 = make_tagged_entry(
            "kn-tag4",
            "technique",
            vec!["rust".to_string(), "error-handling".to_string()],
        );
        db.upsert_knowledge(&entry2).unwrap();

        let pattern_tags = db.list_all_tags(Some("pattern")).unwrap();
        assert_eq!(pattern_tags.len(), 2);
        assert_eq!(pattern_tags, vec!["async", "rust"]);

        let technique_tags = db.list_all_tags(Some("technique")).unwrap();
        assert_eq!(technique_tags.len(), 2);
        assert_eq!(technique_tags, vec!["error-handling", "rust"]);
    }

    #[test]
    fn test_list_all_tags_empty_database() {
        let db = SurrealDatabase::open_in_memory().unwrap();

        let tags = db.list_all_tags(None).unwrap();
        assert!(tags.is_empty());

        let tags = db.list_all_tags(Some("pattern")).unwrap();
        assert!(tags.is_empty());
    }
}
