//! SurrealDB 3 storage backend — `engine::any` dual-engine dispatcher
//!
//! Uses a single `Surreal<Any>` type for all engine variants, selected at
//! runtime via URL scheme. The dual-engine layout separates:
//!
//! - `core_db` → SurrealKV (default) — B-tree, suited for relational/graph data
//!   (projects, tasks, phases, checklists, tools, team)
//! - `hot_db` → RocksDB (default) — LSM, suited for append-heavy data
//!   (phase history / audit logs, future embeddings)
//!
//! ## SurrealDB 3 and type safety
//!
//! All I/O routes through `serde_json::Value`, which implements `SurrealValue`.
//! Domain types never touch the SurrealDB wire directly — all values go through
//! explicit `serde_json::to_value` / `serde_json::from_value` conversion.
//! SurrealDB 3 returns record IDs as `"table:key"` strings; `from_json_record`
//! normalizes them back to bare application IDs before deserialization.

use super::{
    Database, DbChecklistItem, DbPhaseHistory, DbPhaseTransition, DbProject, DbSecurityAssessment,
    DbSecurityAssessmentDetail, DbTeamMember, DbToolConfiguration, DbToolDependency,
};
use crate::error::{LifecycleError, Result};
use crate::persistence::config::{SurrealDbBackendConfig, SurrealEngineConfig};
use async_trait::async_trait;
use chrono::Utc;
use surrealdb::{engine::any, engine::any::Any, opt::auth::Root, Surreal};

// ── serialization helpers ─────────────────────────────────────────────────────

fn to_json<T: serde::Serialize>(v: &T) -> Result<serde_json::Value> {
    serde_json::to_value(v).map_err(|e| LifecycleError::Database(e.to_string()))
}

/// Deserialize from a SurrealDB 3 response, normalizing the `id` field.
///
/// SurrealDB 3 serializes record IDs as `"table:key"` strings. This strips
/// the `"table:"` prefix so domain structs with `id: String` deserialize
/// correctly.
fn from_json_record<T: serde::de::DeserializeOwned>(
    mut val: serde_json::Value,
    table: &str,
) -> Result<T> {
    if let Some(obj) = val.as_object_mut() {
        if let Some(id_val) = obj.get("id").cloned() {
            let prefix = format!("{table}:");
            let normalized = match &id_val {
                serde_json::Value::String(s) => s
                    .strip_prefix(&prefix)
                    .map(|s| s.to_string())
                    .unwrap_or_else(|| s.clone()),
                // SurrealDB 2.x-style structured Thing — kept for compatibility
                other => other
                    .get("id")
                    .and_then(|v| v.get("String"))
                    .and_then(|v| v.as_str())
                    .unwrap_or_default()
                    .to_string(),
            };
            if !normalized.is_empty() {
                obj.insert("id".to_string(), serde_json::Value::String(normalized));
            }
        }
    }
    serde_json::from_value(val).map_err(|e| LifecycleError::Database(e.to_string()))
}

fn from_json_records<T: serde::de::DeserializeOwned>(
    vals: Vec<serde_json::Value>,
    table: &str,
) -> Result<Vec<T>> {
    vals.into_iter()
        .map(|v| from_json_record(v, table))
        .collect()
}

// ── engine factory ────────────────────────────────────────────────────────────

/// Open a SurrealDB connection using `engine::any` URL dispatch.
///
/// Calls `use_ns` / `use_db` at connection time; callers never need to
/// repeat namespace/database selection per query.
async fn open_engine(url: &str, namespace: &str, database: &str) -> Result<Surreal<Any>> {
    let conn = any::connect(url)
        .await
        .map_err(|e| LifecycleError::Database(format!("connect '{url}' failed: {e}")))?;
    conn.use_ns(namespace)
        .use_db(database)
        .await
        .map_err(|e| LifecycleError::Database(format!("use_ns/use_db failed: {e}")))?;
    Ok(conn)
}

// ── storage type ──────────────────────────────────────────────────────────────

/// SurrealDB 3 database implementation with dual-engine layout.
///
/// `Surreal<Any>` is `Clone + Send + Sync`; no `Arc<RwLock<>>` needed.
pub struct SurrealDatabase {
    /// SurrealKV (default) — projects, tasks, phases, checklists, tools, team
    core_db: Surreal<Any>,
    /// RocksDB (default) — phase history, future audit embeddings
    hot_db: Surreal<Any>,
}

impl std::fmt::Debug for SurrealDatabase {
    fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
        f.debug_struct("SurrealDatabase").finish_non_exhaustive()
    }
}

impl Clone for SurrealDatabase {
    fn clone(&self) -> Self {
        Self {
            core_db: self.core_db.clone(),
            hot_db: self.hot_db.clone(),
        }
    }
}

impl SurrealDatabase {
    /// Construct from `SurrealDbBackendConfig`, opening both engine connections.
    ///
    /// # Errors
    ///
    /// Returns `LifecycleError::Database` if either engine connection or
    /// namespace/database selection fails.
    pub async fn from_config(cfg: &SurrealDbBackendConfig) -> Result<Self> {
        let core_url = cfg.core.to_url();
        let hot_url = cfg.hot.to_url();

        let core_db = open_engine(&core_url, &cfg.namespace, "core").await?;
        let hot_db = open_engine(&hot_url, &cfg.namespace, "hot").await?;

        // Authenticate for remote WS connections only
        if matches!(cfg.core, SurrealEngineConfig::Ws { .. }) {
            let user = cfg
                .username
                .as_deref()
                .unwrap_or("root");
            let pass = cfg
                .password
                .as_deref()
                .unwrap_or("");
            core_db
                .signin(Root {
                    username: user.to_string(),
                    password: pass.to_string(),
                })
                .await
                .map_err(|e| LifecycleError::Database(format!("core_db signin failed: {e}")))?;
        }

        if matches!(cfg.hot, SurrealEngineConfig::Ws { .. }) {
            let user = cfg.username.as_deref().unwrap_or("root");
            let pass = cfg.password.as_deref().unwrap_or("");
            hot_db
                .signin(Root {
                    username: user.to_string(),
                    password: pass.to_string(),
                })
                .await
                .map_err(|e| LifecycleError::Database(format!("hot_db signin failed: {e}")))?;
        }

        Ok(Self { core_db, hot_db })
    }

    /// Create an in-memory SurrealDB instance (both engines use `mem://`).
    ///
    /// Convenience constructor for tests and ephemeral use.
    pub async fn new_memory() -> Result<Self> {
        let cfg = SurrealDbBackendConfig {
            core: SurrealEngineConfig::Mem,
            hot: SurrealEngineConfig::Mem,
            namespace: "syntaxis".to_string(),
            username: None,
            password: None,
        };
        Self::from_config(&cfg).await
    }

    /// Create a file-based SurrealDB instance using SurrealKV for core and
    /// RocksDB for hot data.
    pub async fn new_file(core_path: &str, hot_path: &str) -> Result<Self> {
        let cfg = SurrealDbBackendConfig {
            core: SurrealEngineConfig::SurrealKv {
                path: core_path.to_string(),
            },
            hot: SurrealEngineConfig::RocksDb {
                path: hot_path.to_string(),
            },
            namespace: "syntaxis".to_string(),
            username: None,
            password: None,
        };
        Self::from_config(&cfg).await
    }
}

// ── Database trait implementation ─────────────────────────────────────────────

#[async_trait]
impl Database for SurrealDatabase {
    // ═══════════════════════════════════════════════════════════════════════
    // PROJECT OPERATIONS — core_db
    // ═══════════════════════════════════════════════════════════════════════

    async fn create_project(&self, project: &DbProject) -> Result<DbProject> {
        let _: Option<serde_json::Value> = self
            .core_db
            .upsert(("projects", project.id.as_str()))
            .content(to_json(project)?)
            .await
            .map_err(|e| LifecycleError::Database(format!("create_project: {e}")))?;
        Ok(project.clone())
    }

    async fn get_project(&self, id: &str) -> Result<Option<DbProject>> {
        let raw: Option<serde_json::Value> = self
            .core_db
            .select(("projects", id))
            .await
            .map_err(|e| LifecycleError::Database(format!("get_project: {e}")))?;
        raw.map(|v| from_json_record(v, "projects")).transpose()
    }

    async fn list_projects(&self) -> Result<Vec<DbProject>> {
        let raw: Vec<serde_json::Value> = self
            .core_db
            .select("projects")
            .await
            .map_err(|e| LifecycleError::Database(format!("list_projects: {e}")))?;
        from_json_records(raw, "projects")
    }

    async fn update_project(&self, project: &DbProject) -> Result<DbProject> {
        let _: Option<serde_json::Value> = self
            .core_db
            .upsert(("projects", project.id.as_str()))
            .content(to_json(project)?)
            .await
            .map_err(|e| LifecycleError::Database(format!("update_project: {e}")))?;
        Ok(project.clone())
    }

    async fn delete_project(&self, id: &str) -> Result<()> {
        let _: Option<serde_json::Value> = self
            .core_db
            .delete(("projects", id))
            .await
            .map_err(|e| LifecycleError::Database(format!("delete_project: {e}")))?;
        Ok(())
    }

    // ═══════════════════════════════════════════════════════════════════════
    // CHECKLIST OPERATIONS — core_db
    // ═══════════════════════════════════════════════════════════════════════

    async fn create_checklist_item(&self, item: &DbChecklistItem) -> Result<DbChecklistItem> {
        let _: Option<serde_json::Value> = self
            .core_db
            .upsert(("checklist_items", item.id.as_str()))
            .content(to_json(item)?)
            .await
            .map_err(|e| LifecycleError::Database(format!("create_checklist_item: {e}")))?;
        Ok(item.clone())
    }

    async fn get_checklist_items(&self, project_id: &str) -> Result<Vec<DbChecklistItem>> {
        let raw: Vec<serde_json::Value> = self
            .core_db
            .query(
                "SELECT * FROM checklist_items WHERE project_id = $pid ORDER BY created_at",
            )
            .bind(("pid", project_id.to_string()))
            .await
            .map_err(|e| LifecycleError::Database(e.to_string()))?
            .take(0)
            .map_err(|e| LifecycleError::Database(e.to_string()))?;
        from_json_records(raw, "checklist_items")
    }

    async fn get_checklist_items_by_phase(
        &self,
        project_id: &str,
        phase: &str,
    ) -> Result<Vec<DbChecklistItem>> {
        let raw: Vec<serde_json::Value> = self
            .core_db
            .query(
                "SELECT * FROM checklist_items WHERE project_id = $pid AND phase = $ph ORDER BY created_at",
            )
            .bind(("pid", project_id.to_string()))
            .bind(("ph", phase.to_string()))
            .await
            .map_err(|e| LifecycleError::Database(e.to_string()))?
            .take(0)
            .map_err(|e| LifecycleError::Database(e.to_string()))?;
        from_json_records(raw, "checklist_items")
    }

    async fn update_checklist_item(&self, item: &DbChecklistItem) -> Result<DbChecklistItem> {
        let _: Option<serde_json::Value> = self
            .core_db
            .upsert(("checklist_items", item.id.as_str()))
            .content(to_json(item)?)
            .await
            .map_err(|e| LifecycleError::Database(format!("update_checklist_item: {e}")))?;
        Ok(item.clone())
    }

    async fn complete_checklist_item(&self, id: &str) -> Result<()> {
        let now = Utc::now().to_rfc3339();
        self.core_db
            .query(
                "UPDATE checklist_items SET completed = true, completed_at = $now WHERE id = $id",
            )
            .bind(("now", now))
            .bind(("id", id.to_string()))
            .await
            .map_err(|e| LifecycleError::Database(e.to_string()))?;
        Ok(())
    }

    async fn delete_checklist_item(&self, id: &str) -> Result<()> {
        let _: Option<serde_json::Value> = self
            .core_db
            .delete(("checklist_items", id))
            .await
            .map_err(|e| LifecycleError::Database(format!("delete_checklist_item: {e}")))?;
        Ok(())
    }

    async fn get_completion_percentage(&self, project_id: &str, phase: &str) -> Result<f64> {
        // Compute in Rust rather than SurrealQL to remain engine-agnostic
        let raw: Vec<serde_json::Value> = self
            .core_db
            .query(
                "SELECT completed FROM checklist_items WHERE project_id = $pid AND phase = $ph",
            )
            .bind(("pid", project_id.to_string()))
            .bind(("ph", phase.to_string()))
            .await
            .map_err(|e| LifecycleError::Database(e.to_string()))?
            .take(0)
            .map_err(|e| LifecycleError::Database(e.to_string()))?;

        if raw.is_empty() {
            return Ok(0.0);
        }

        let completed = raw
            .iter()
            .filter(|v| {
                v.get("completed")
                    .and_then(|c| c.as_bool())
                    .unwrap_or(false)
            })
            .count();

        Ok(completed as f64 / raw.len() as f64 * 100.0)
    }

    // ═══════════════════════════════════════════════════════════════════════
    // PHASE TRANSITION OPERATIONS — core_db
    // ═══════════════════════════════════════════════════════════════════════

    async fn record_phase_transition(
        &self,
        transition: &DbPhaseTransition,
    ) -> Result<DbPhaseTransition> {
        let _: Option<serde_json::Value> = self
            .core_db
            .upsert(("phase_transitions", transition.id.as_str()))
            .content(to_json(transition)?)
            .await
            .map_err(|e| LifecycleError::Database(format!("record_phase_transition: {e}")))?;
        Ok(transition.clone())
    }

    async fn get_phase_history(&self, project_id: &str) -> Result<Vec<DbPhaseTransition>> {
        let raw: Vec<serde_json::Value> = self
            .core_db
            .query(
                "SELECT * FROM phase_transitions WHERE project_id = $pid ORDER BY timestamp",
            )
            .bind(("pid", project_id.to_string()))
            .await
            .map_err(|e| LifecycleError::Database(e.to_string()))?
            .take(0)
            .map_err(|e| LifecycleError::Database(e.to_string()))?;
        from_json_records(raw, "phase_transitions")
    }

    async fn get_current_phase(&self, project_id: &str) -> Result<String> {
        #[derive(serde::Deserialize)]
        struct PhaseResult {
            current_phase: String,
        }

        let raw: Vec<serde_json::Value> = self
            .core_db
            .query("SELECT current_phase FROM projects WHERE id = $pid")
            .bind(("pid", project_id.to_string()))
            .await
            .map_err(|e| LifecycleError::Database(e.to_string()))?
            .take(0)
            .map_err(|e| LifecycleError::Database(e.to_string()))?;

        let results: Vec<PhaseResult> = raw
            .into_iter()
            .map(|v| serde_json::from_value(v).map_err(|e| LifecycleError::Database(e.to_string())))
            .collect::<Result<_>>()?;

        results
            .into_iter()
            .next()
            .map(|r| r.current_phase)
            .ok_or_else(|| LifecycleError::Database("Project not found".to_string()))
    }

    // ═══════════════════════════════════════════════════════════════════════
    // SECURITY ASSESSMENT OPERATIONS — core_db
    // ═══════════════════════════════════════════════════════════════════════

    async fn create_security_assessment(
        &self,
        assessment: &DbSecurityAssessment,
    ) -> Result<DbSecurityAssessment> {
        let _: Option<serde_json::Value> = self
            .core_db
            .upsert(("security_assessments", assessment.id.as_str()))
            .content(to_json(assessment)?)
            .await
            .map_err(|e| LifecycleError::Database(format!("create_security_assessment: {e}")))?;
        Ok(assessment.clone())
    }

    async fn get_latest_assessment(
        &self,
        project_id: &str,
    ) -> Result<Option<DbSecurityAssessment>> {
        let raw: Vec<serde_json::Value> = self
            .core_db
            .query(
                "SELECT * FROM security_assessments WHERE project_id = $pid ORDER BY assessment_date DESC LIMIT 1",
            )
            .bind(("pid", project_id.to_string()))
            .await
            .map_err(|e| LifecycleError::Database(e.to_string()))?
            .take(0)
            .map_err(|e| LifecycleError::Database(e.to_string()))?;
        from_json_records::<DbSecurityAssessment>(raw, "security_assessments")
            .map(|v| v.into_iter().next())
    }

    async fn list_assessments(&self, project_id: &str) -> Result<Vec<DbSecurityAssessment>> {
        let raw: Vec<serde_json::Value> = self
            .core_db
            .query(
                "SELECT * FROM security_assessments WHERE project_id = $pid ORDER BY assessment_date",
            )
            .bind(("pid", project_id.to_string()))
            .await
            .map_err(|e| LifecycleError::Database(e.to_string()))?
            .take(0)
            .map_err(|e| LifecycleError::Database(e.to_string()))?;
        from_json_records(raw, "security_assessments")
    }

    // ═══════════════════════════════════════════════════════════════════════
    // PHASE HISTORY OPERATIONS — hot_db (append-heavy audit log)
    // ═══════════════════════════════════════════════════════════════════════

    async fn record_phase_history(&self, history: &DbPhaseHistory) -> Result<DbPhaseHistory> {
        let _: Option<serde_json::Value> = self
            .hot_db
            .upsert(("phase_history", history.id.as_str()))
            .content(to_json(history)?)
            .await
            .map_err(|e| LifecycleError::Database(format!("record_phase_history: {e}")))?;
        Ok(history.clone())
    }

    async fn get_phase_history_records(&self, project_id: &str) -> Result<Vec<DbPhaseHistory>> {
        let raw: Vec<serde_json::Value> = self
            .hot_db
            .query("SELECT * FROM phase_history WHERE project_id = $pid ORDER BY started_at")
            .bind(("pid", project_id.to_string()))
            .await
            .map_err(|e| LifecycleError::Database(e.to_string()))?
            .take(0)
            .map_err(|e| LifecycleError::Database(e.to_string()))?;
        from_json_records(raw, "phase_history")
    }

    // ═══════════════════════════════════════════════════════════════════════
    // TOOL CONFIGURATION OPERATIONS — core_db
    // ═══════════════════════════════════════════════════════════════════════

    async fn create_tool_config(
        &self,
        config: &DbToolConfiguration,
    ) -> Result<DbToolConfiguration> {
        let _: Option<serde_json::Value> = self
            .core_db
            .upsert(("tool_configurations", config.id.as_str()))
            .content(to_json(config)?)
            .await
            .map_err(|e| LifecycleError::Database(format!("create_tool_config: {e}")))?;
        Ok(config.clone())
    }

    async fn get_tool_config(&self, id: &str) -> Result<Option<DbToolConfiguration>> {
        let raw: Option<serde_json::Value> = self
            .core_db
            .select(("tool_configurations", id))
            .await
            .map_err(|e| LifecycleError::Database(format!("get_tool_config: {e}")))?;
        raw.map(|v| from_json_record(v, "tool_configurations")).transpose()
    }

    async fn list_tool_configs(&self, project_id: &str) -> Result<Vec<DbToolConfiguration>> {
        let raw: Vec<serde_json::Value> = self
            .core_db
            .query(
                "SELECT * FROM tool_configurations WHERE project_id = $pid ORDER BY created_at",
            )
            .bind(("pid", project_id.to_string()))
            .await
            .map_err(|e| LifecycleError::Database(e.to_string()))?
            .take(0)
            .map_err(|e| LifecycleError::Database(e.to_string()))?;
        from_json_records(raw, "tool_configurations")
    }

    async fn find_tool_config(
        &self,
        project_id: &str,
        tool_name: &str,
    ) -> Result<Option<DbToolConfiguration>> {
        let raw: Vec<serde_json::Value> = self
            .core_db
            .query(
                "SELECT * FROM tool_configurations WHERE project_id = $pid AND tool_name = $tn LIMIT 1",
            )
            .bind(("pid", project_id.to_string()))
            .bind(("tn", tool_name.to_string()))
            .await
            .map_err(|e| LifecycleError::Database(e.to_string()))?
            .take(0)
            .map_err(|e| LifecycleError::Database(e.to_string()))?;
        from_json_records::<DbToolConfiguration>(raw, "tool_configurations")
            .map(|v| v.into_iter().next())
    }

    async fn update_tool_config(
        &self,
        config: &DbToolConfiguration,
    ) -> Result<DbToolConfiguration> {
        let _: Option<serde_json::Value> = self
            .core_db
            .upsert(("tool_configurations", config.id.as_str()))
            .content(to_json(config)?)
            .await
            .map_err(|e| LifecycleError::Database(format!("update_tool_config: {e}")))?;
        Ok(config.clone())
    }

    // ═══════════════════════════════════════════════════════════════════════
    // TOOL DEPENDENCY OPERATIONS — core_db
    // ═══════════════════════════════════════════════════════════════════════

    async fn create_tool_dependency(
        &self,
        dependency: &DbToolDependency,
    ) -> Result<DbToolDependency> {
        let _: Option<serde_json::Value> = self
            .core_db
            .upsert(("tool_dependencies", dependency.id.as_str()))
            .content(to_json(dependency)?)
            .await
            .map_err(|e| LifecycleError::Database(format!("create_tool_dependency: {e}")))?;
        Ok(dependency.clone())
    }

    async fn get_tool_dependencies(&self, project_id: &str) -> Result<Vec<DbToolDependency>> {
        let raw: Vec<serde_json::Value> = self
            .core_db
            .query(
                "SELECT * FROM tool_dependencies WHERE project_id = $pid ORDER BY tool_name",
            )
            .bind(("pid", project_id.to_string()))
            .await
            .map_err(|e| LifecycleError::Database(e.to_string()))?
            .take(0)
            .map_err(|e| LifecycleError::Database(e.to_string()))?;
        from_json_records(raw, "tool_dependencies")
    }

    async fn delete_tool_dependency(&self, id: &str) -> Result<()> {
        let _: Option<serde_json::Value> = self
            .core_db
            .delete(("tool_dependencies", id))
            .await
            .map_err(|e| LifecycleError::Database(format!("delete_tool_dependency: {e}")))?;
        Ok(())
    }

    // ═══════════════════════════════════════════════════════════════════════
    // SECURITY ASSESSMENT DETAIL OPERATIONS — core_db
    // ═══════════════════════════════════════════════════════════════════════

    async fn create_assessment_detail(
        &self,
        detail: &DbSecurityAssessmentDetail,
    ) -> Result<DbSecurityAssessmentDetail> {
        let _: Option<serde_json::Value> = self
            .core_db
            .upsert(("security_assessment_details", detail.id.as_str()))
            .content(to_json(detail)?)
            .await
            .map_err(|e| {
                LifecycleError::Database(format!("create_assessment_detail: {e}"))
            })?;
        Ok(detail.clone())
    }

    async fn get_assessment_details(
        &self,
        assessment_id: &str,
    ) -> Result<Vec<DbSecurityAssessmentDetail>> {
        let raw: Vec<serde_json::Value> = self
            .core_db
            .query(
                "SELECT * FROM security_assessment_details WHERE assessment_id = $aid",
            )
            .bind(("aid", assessment_id.to_string()))
            .await
            .map_err(|e| LifecycleError::Database(e.to_string()))?
            .take(0)
            .map_err(|e| LifecycleError::Database(e.to_string()))?;
        from_json_records(raw, "security_assessment_details")
    }

    async fn delete_assessment_detail(&self, id: &str) -> Result<()> {
        let _: Option<serde_json::Value> = self
            .core_db
            .delete(("security_assessment_details", id))
            .await
            .map_err(|e| {
                LifecycleError::Database(format!("delete_assessment_detail: {e}"))
            })?;
        Ok(())
    }

    // ═══════════════════════════════════════════════════════════════════════
    // TEAM MEMBER OPERATIONS — core_db
    // ═══════════════════════════════════════════════════════════════════════

    async fn create_team_member(&self, member: &DbTeamMember) -> Result<DbTeamMember> {
        let _: Option<serde_json::Value> = self
            .core_db
            .upsert(("users", member.id.as_str()))
            .content(to_json(member)?)
            .await
            .map_err(|e| LifecycleError::Database(format!("create_team_member: {e}")))?;
        Ok(member.clone())
    }

    async fn get_team_member(&self, id: &str) -> Result<Option<DbTeamMember>> {
        let raw: Option<serde_json::Value> = self
            .core_db
            .select(("users", id))
            .await
            .map_err(|e| LifecycleError::Database(format!("get_team_member: {e}")))?;
        raw.map(|v| from_json_record(v, "users")).transpose()
    }

    async fn list_team_members(&self) -> Result<Vec<DbTeamMember>> {
        let raw: Vec<serde_json::Value> = self
            .core_db
            .query("SELECT * FROM users ORDER BY name")
            .await
            .map_err(|e| LifecycleError::Database(e.to_string()))?
            .take(0)
            .map_err(|e| LifecycleError::Database(e.to_string()))?;
        from_json_records(raw, "users")
    }

    async fn update_team_member(&self, member: &DbTeamMember) -> Result<DbTeamMember> {
        let _: Option<serde_json::Value> = self
            .core_db
            .upsert(("users", member.id.as_str()))
            .content(to_json(member)?)
            .await
            .map_err(|e| LifecycleError::Database(format!("update_team_member: {e}")))?;
        Ok(member.clone())
    }

    // ═══════════════════════════════════════════════════════════════════════
    // HEALTH / UTILITY
    // ═══════════════════════════════════════════════════════════════════════

    async fn ping(&self) -> Result<()> {
        let _: Vec<serde_json::Value> = self
            .core_db
            .query("SELECT 1")
            .await
            .map_err(|e| LifecycleError::Database(format!("Ping failed: {e}")))?
            .take(0)
            .map_err(|e| LifecycleError::Database(format!("Ping parse failed: {e}")))?;
        Ok(())
    }

    /// SurrealDB 3 operates in SCHEMALESS mode by default — no table definitions
    /// required. This verifies connectivity on both engines instead.
    async fn init_schema(&self) -> Result<()> {
        self.ping().await?;

        let _: Vec<serde_json::Value> = self
            .hot_db
            .query("SELECT 1")
            .await
            .map_err(|e| LifecycleError::Database(format!("Hot-db ping failed: {e}")))?
            .take(0)
            .map_err(|e| LifecycleError::Database(format!("Hot-db ping parse failed: {e}")))?;

        Ok(())
    }
}

// ── tests ─────────────────────────────────────────────────────────────────────

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

    #[tokio::test]
    #[ignore = "embedded SurrealDB tests must run serially; use: cargo test -- --test-threads=1 --ignored"]
    async fn test_surrealdb_memory_creation() {
        let db = SurrealDatabase::new_memory().await;
        assert!(db.is_ok(), "failed: {:?}", db.err());
    }

    #[tokio::test]
    #[ignore = "embedded SurrealDB tests must run serially; use: cargo test -- --test-threads=1 --ignored"]
    async fn test_surrealdb_ping() {
        let db = SurrealDatabase::new_memory().await.unwrap();
        assert!(db.ping().await.is_ok());
    }

    #[tokio::test]
    #[ignore = "embedded SurrealDB tests must run serially; use: cargo test -- --test-threads=1 --ignored"]
    async fn test_surrealdb_init_schema() {
        let db = SurrealDatabase::new_memory().await.unwrap();
        assert!(db.init_schema().await.is_ok());
    }

    #[tokio::test]
    #[ignore = "embedded SurrealDB tests must run serially; use: cargo test -- --test-threads=1 --ignored"]
    async fn test_surrealdb_project_crud() {
        let db = SurrealDatabase::new_memory().await.unwrap();

        let project = DbProject {
            id: "proj-surreal-1".to_string(),
            name: "Test Project".to_string(),
            version: "1.0.0".to_string(),
            description: "A test project".to_string(),
            project_type: "MultiLang".to_string(),
            current_phase: "Creation".to_string(),
            created_at: Utc::now().to_rfc3339(),
            updated_at: Utc::now().to_rfc3339(),
        };

        let created = db.create_project(&project).await.unwrap();
        assert_eq!(created.name, "Test Project");

        let fetched = db.get_project("proj-surreal-1").await.unwrap();
        assert!(fetched.is_some(), "project should be fetchable after create");

        let updated = DbProject {
            current_phase: "Development".to_string(),
            ..project.clone()
        };
        db.update_project(&updated).await.unwrap();

        let listed = db.list_projects().await.unwrap();
        assert!(!listed.is_empty());

        db.delete_project("proj-surreal-1").await.unwrap();
        let deleted = db.get_project("proj-surreal-1").await.unwrap();
        assert!(deleted.is_none());
    }

    #[tokio::test]
    #[ignore = "embedded SurrealDB tests must run serially; use: cargo test -- --test-threads=1 --ignored"]
    async fn test_surrealdb_checklist_operations() {
        let db = SurrealDatabase::new_memory().await.unwrap();

        let item = DbChecklistItem {
            id: "item-surreal-1".to_string(),
            project_id: "proj-1".to_string(),
            phase: "Creation".to_string(),
            task_id: "task-1".to_string(),
            description: "Setup project".to_string(),
            completed: false,
            completed_at: None,
            created_at: Utc::now().to_rfc3339(),
            task_type: "Setup".to_string(),
            task_priority: "High".to_string(),
            task_due: None,
            task_estimation: Some("5d".to_string()),
            task_deps: "[]".to_string(),
            task_note: None,
            task_name: Some("Setup Task".to_string()),
        };

        db.create_checklist_item(&item).await.unwrap();

        let fetched = db.get_checklist_items("proj-1").await.unwrap();
        assert!(!fetched.is_empty());

        let by_phase = db
            .get_checklist_items_by_phase("proj-1", "Creation")
            .await
            .unwrap();
        assert!(!by_phase.is_empty());

        db.complete_checklist_item("item-surreal-1").await.unwrap();
        db.delete_checklist_item("item-surreal-1").await.unwrap();
    }

    #[tokio::test]
    #[ignore = "embedded SurrealDB tests must run serially; use: cargo test -- --test-threads=1 --ignored"]
    async fn test_surrealdb_phase_history_routes_to_hot_db() {
        let db = SurrealDatabase::new_memory().await.unwrap();

        let history = DbPhaseHistory {
            id: "hist-1".to_string(),
            project_id: "proj-1".to_string(),
            phase: "Creation".to_string(),
            duration_days: 3,
            started_at: Utc::now().to_rfc3339(),
            completed_at: Some(Utc::now().to_rfc3339()),
            notes: Some("completed".to_string()),
        };

        db.record_phase_history(&history).await.unwrap();
        let records = db.get_phase_history_records("proj-1").await.unwrap();
        assert!(!records.is_empty());
    }

    #[tokio::test]
    #[ignore = "embedded SurrealDB tests must run serially; use: cargo test -- --test-threads=1 --ignored"]
    async fn test_surrealdb_team_member_crud() {
        let db = SurrealDatabase::new_memory().await.unwrap();

        let member = DbTeamMember {
            id: "user-1".to_string(),
            name: "Alice".to_string(),
            email: "alice@example.com".to_string(),
        };

        db.create_team_member(&member).await.unwrap();

        let fetched = db.get_team_member("user-1").await.unwrap();
        assert!(fetched.is_some());

        let all = db.list_team_members().await.unwrap();
        assert!(!all.is_empty());
    }
}
