use std::path::Path;
use std::sync::Mutex;

use chrono::Utc;
use rusqlite::{params, Connection, OptionalExtension};
use uuid::Uuid;

use crate::types::*;

// ── Open & schema ──────────────────────────────────

pub struct Database {
    conn: Mutex<Connection>,
}

impl Database {
    fn c(&self) -> std::sync::MutexGuard<'_, Connection> {
        self.conn.lock().unwrap_or_else(|e| e.into_inner())
    }

    pub fn open(path: impl AsRef<Path>) -> Result<Self, rusqlite::Error> {
        let conn = Connection::open(path)?;
        conn.execute_batch("PRAGMA journal_mode=WAL; PRAGMA foreign_keys=ON; PRAGMA busy_timeout=5000;")?;
        conn.execute_batch(
            "CREATE TABLE IF NOT EXISTS projects (
                id         TEXT PRIMARY KEY,
                name       TEXT NOT NULL,
                created_at TEXT NOT NULL
            );
            CREATE TABLE IF NOT EXISTS nodes (
                id         TEXT PRIMARY KEY,
                project_id TEXT NOT NULL,
                label      TEXT NOT NULL,
                node_type  TEXT NOT NULL,
                x          REAL,
                y          REAL,
                properties TEXT NOT NULL DEFAULT '{}',
                created_at TEXT NOT NULL,
                FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
            );
            CREATE TABLE IF NOT EXISTS edges (
                id             TEXT PRIMARY KEY,
                project_id     TEXT NOT NULL,
                source_node_id TEXT NOT NULL,
                target_node_id TEXT NOT NULL,
                label          TEXT NOT NULL DEFAULT '',
                created_at     TEXT NOT NULL,
                FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
            );
            CREATE INDEX IF NOT EXISTS idx_nodes_pid ON nodes(project_id);
            CREATE INDEX IF NOT EXISTS idx_edges_pid ON edges(project_id);
            CREATE TABLE IF NOT EXISTS credentials (
                id TEXT PRIMARY KEY, project_id TEXT NOT NULL, label TEXT NOT NULL,
                credential_type TEXT NOT NULL, username TEXT, secret TEXT NOT NULL,
                source_node_id TEXT, status TEXT NOT NULL DEFAULT 'unverified',
                notes TEXT, created_at TEXT NOT NULL,
                FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
                FOREIGN KEY (source_node_id) REFERENCES nodes(id) ON DELETE SET NULL
            );
            CREATE INDEX IF NOT EXISTS idx_credentials_pid ON credentials(project_id);
            CREATE TABLE IF NOT EXISTS evidence_files (
                id TEXT PRIMARY KEY, project_id TEXT NOT NULL, node_id TEXT NOT NULL,
                original_name TEXT NOT NULL, stored_name TEXT NOT NULL,
                file_size INTEGER NOT NULL DEFAULT 0, mime_type TEXT, created_at TEXT NOT NULL,
                FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
                FOREIGN KEY (node_id) REFERENCES nodes(id) ON DELETE CASCADE
            );
            CREATE INDEX IF NOT EXISTS idx_evidence_pid_nid ON evidence_files(project_id, node_id);
            CREATE TABLE IF NOT EXISTS agent_memory (
                id          TEXT PRIMARY KEY,
                project_id  TEXT NOT NULL,
                role        TEXT NOT NULL,
                content     TEXT NOT NULL,
                tool_calls  TEXT,
                run_id      TEXT NOT NULL,
                created_at  TEXT NOT NULL,
                FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
            );
            CREATE INDEX IF NOT EXISTS idx_agent_memory_pid_time 
                ON agent_memory(project_id, created_at DESC);
            CREATE TABLE IF NOT EXISTS intents (
                id TEXT PRIMARY KEY,
                project_id TEXT NOT NULL,
                node_id TEXT NOT NULL,
                direction TEXT NOT NULL,
                description TEXT,
                status TEXT NOT NULL DEFAULT 'open',
                worker TEXT,
                created_at TEXT NOT NULL,
                updated_at TEXT NOT NULL,
                FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
            );
            CREATE INDEX IF NOT EXISTS idx_intents_pid ON intents(project_id);
            CREATE INDEX IF NOT EXISTS idx_intents_node ON intents(node_id);
            CREATE INDEX IF NOT EXISTS idx_intents_status ON intents(status);",
        )?;
        Ok(Database {
            conn: Mutex::new(conn),
        })
    }
}

// ── Row deserializers ──────────────────────────────

fn row_project(row: &rusqlite::Row) -> rusqlite::Result<Project> {
    let id: String = row.get("id")?;
    let ca: String = row.get("created_at")?;
    Ok(Project {
        id: Uuid::parse_str(&id)
            .map_err(|e| rusqlite::Error::InvalidParameterName(e.to_string()))?,
        name: row.get("name")?,
        created_at: ca
            .parse()
            .map_err(|_| rusqlite::Error::InvalidParameterName("invalid created_at".into()))?,
    })
}

fn row_node(row: &rusqlite::Row) -> rusqlite::Result<Node> {
    let id_s: String = row.get("id")?;
    let pid_s: String = row.get("project_id")?;
    let nt_s: String = row.get("node_type")?;
    let props_s: String = row.get("properties")?;
    let ca: String = row.get("created_at")?;
    let x: Option<f64> = row.get("x")?;
    let y: Option<f64> = row.get("y")?;
    let nt: NodeType =
        serde_json::from_value(serde_json::Value::String(nt_s)).unwrap_or(NodeType::Note);
    Ok(Node {
        id: Uuid::parse_str(&id_s)
            .map_err(|e| rusqlite::Error::InvalidParameterName(e.to_string()))?,
        project_id: Uuid::parse_str(&pid_s)
            .map_err(|e| rusqlite::Error::InvalidParameterName(e.to_string()))?,
        label: row.get("label")?,
        node_type: nt,
        position: match (x, y) {
            (Some(x), Some(y)) => Some(NodePosition { x, y }),
            _ => None,
        },
        properties: serde_json::from_str(&props_s).unwrap_or_default(),
        created_at: ca
            .parse()
            .map_err(|_| rusqlite::Error::InvalidParameterName("invalid created_at".into()))?,
    })
}

fn row_edge(row: &rusqlite::Row) -> rusqlite::Result<Edge> {
    let id_s: String = row.get("id")?;
    let pid_s: String = row.get("project_id")?;
    let src_s: String = row.get("source_node_id")?;
    let tgt_s: String = row.get("target_node_id")?;
    let ca: String = row.get("created_at")?;
    Ok(Edge {
        id: Uuid::parse_str(&id_s)
            .map_err(|e| rusqlite::Error::InvalidParameterName(e.to_string()))?,
        project_id: Uuid::parse_str(&pid_s)
            .map_err(|e| rusqlite::Error::InvalidParameterName(e.to_string()))?,
        source_node_id: Uuid::parse_str(&src_s)
            .map_err(|e| rusqlite::Error::InvalidParameterName(e.to_string()))?,
        target_node_id: Uuid::parse_str(&tgt_s)
            .map_err(|e| rusqlite::Error::InvalidParameterName(e.to_string()))?,
        label: row.get::<_, Option<String>>("label")?,
        created_at: ca
            .parse()
            .map_err(|_| rusqlite::Error::InvalidParameterName("invalid created_at".into()))?,
    })
}

fn row_credential(row: &rusqlite::Row) -> rusqlite::Result<Credential> {
    let id_s: String = row.get("id")?;
    let pid_s: String = row.get("project_id")?;
    let ct_s: String = row.get("credential_type")?;
    let src_s: String = row.get("source_node_id")?;
    let status_s: String = row.get("status")?;
    let ca: String = row.get("created_at")?;
    let ct: CredentialType =
        serde_json::from_value(serde_json::Value::String(ct_s)).unwrap_or(CredentialType::Password);
    let status: CredentialStatus = serde_json::from_value(serde_json::Value::String(status_s))
        .unwrap_or(CredentialStatus::Unverified);
    Ok(Credential {
        id: Uuid::parse_str(&id_s)
            .map_err(|e| rusqlite::Error::InvalidParameterName(e.to_string()))?,
        project_id: Uuid::parse_str(&pid_s)
            .map_err(|e| rusqlite::Error::InvalidParameterName(e.to_string()))?,
        label: row.get("label")?,
        credential_type: ct,
        username: row.get("username")?,
        secret: row.get("secret")?,
        source_node_id: src_s.parse().ok(),
        status,
        notes: row.get("notes")?,
        created_at: ca
            .parse()
            .map_err(|_| rusqlite::Error::InvalidParameterName("invalid created_at".into()))?,
    })
}

fn row_evidence_file(row: &rusqlite::Row) -> rusqlite::Result<EvidenceFile> {
    let id_s: String = row.get("id")?;
    let pid_s: String = row.get("project_id")?;
    let nid_s: String = row.get("node_id")?;
    let mime: String = row.get::<_, Option<String>>("mime_type")?.unwrap_or_default();
    let ca: String = row.get("created_at")?;
    Ok(EvidenceFile {
        id: Uuid::parse_str(&id_s)
            .map_err(|e| rusqlite::Error::InvalidParameterName(e.to_string()))?,
        project_id: Uuid::parse_str(&pid_s)
            .map_err(|e| rusqlite::Error::InvalidParameterName(e.to_string()))?,
        node_id: Uuid::parse_str(&nid_s)
            .map_err(|e| rusqlite::Error::InvalidParameterName(e.to_string()))?,
        original_name: row.get("original_name")?,
        stored_name: row.get("stored_name")?,
        file_size: row.get("file_size")?,
        mime_type: Some(mime),
        created_at: ca
            .parse()
            .map_err(|_| rusqlite::Error::InvalidParameterName("invalid created_at".into()))?,
    })
}

fn row_intent(row: &rusqlite::Row) -> rusqlite::Result<ExplorationIntent> {
    let id: String = row.get("id")?;
    let pid_s: String = row.get("project_id")?;
    let status_s: String = row.get("status")?;
    let ca: String = row.get("created_at")?;
    let ua: String = row.get("updated_at")?;
    Ok(ExplorationIntent {
        id,
        project_id: Uuid::parse_str(&pid_s)
            .map_err(|e| rusqlite::Error::InvalidParameterName(e.to_string()))?,
        node_id: row.get("node_id")?,
        direction: row.get("direction")?,
        description: row.get("description")?,
        status: IntentStatus::from_str(&status_s),
        worker: row.get("worker")?,
        created_at: ca,
        updated_at: ua,
    })
}

// ── CRUD: Projects ─────────────────────────────────

impl Database {
    pub fn create_project(&self, req: &CreateProjectRequest) -> rusqlite::Result<Project> {
        let p = Project {
            id: Uuid::new_v4(),
            name: req.name.clone(),
            created_at: Utc::now(),
        };
        self.c().execute(
            "INSERT INTO projects (id,name,created_at) VALUES (?1,?2,?3)",
            params![p.id.to_string(), p.name, p.created_at.to_rfc3339()],
        )?;
        Ok(p)
    }
    pub fn list_projects(&self) -> rusqlite::Result<Vec<Project>> {
        let c = self.c();
        let mut stmt =
            c.prepare("SELECT id,name,created_at FROM projects ORDER BY created_at DESC")?;
        let rows = stmt.query_map([], row_project)?;
        let result: Vec<Project> = rows.collect::<Result<Vec<_>, _>>()?;
        Ok(result)
    }
    pub fn get_project(&self, id: Uuid) -> rusqlite::Result<Option<Project>> {
        self.c()
            .query_row(
                "SELECT id,name,created_at FROM projects WHERE id=?1",
                params![id.to_string()],
                row_project,
            )
            .optional()
    }
    pub fn delete_project(&self, id: Uuid) -> rusqlite::Result<bool> {
        Ok(self
            .c()
            .execute("DELETE FROM projects WHERE id=?1", params![id.to_string()])?
            > 0)
    }
    pub fn find_project_by_name(&self, name: &str) -> rusqlite::Result<Option<Project>> {
        self.c()
            .query_row(
                "SELECT id,name,created_at FROM projects WHERE name=?1",
                params![name],
                row_project,
            )
            .optional()
    }
    pub fn count_nodes_for_project(&self, project_id: Uuid) -> rusqlite::Result<usize> {
        self.c().query_row(
            "SELECT COUNT(*) FROM nodes WHERE project_id=?1",
            params![project_id.to_string()],
            |row| row.get(0),
        )
    }
    pub fn count_edges_for_project(&self, project_id: Uuid) -> rusqlite::Result<usize> {
        self.c().query_row(
            "SELECT COUNT(*) FROM edges WHERE project_id=?1",
            params![project_id.to_string()],
            |row| row.get(0),
        )
    }
}

// ── CRUD: Nodes ────────────────────────────────────

impl Database {
    pub fn insert_node(&self, node: &Node) -> rusqlite::Result<()> {
        let (x, y) = match &node.position {
            Some(p) => (Some(p.x), Some(p.y)),
            None => (None, None),
        };
        self.c().execute(
            "INSERT INTO nodes (id,project_id,label,node_type,x,y,properties,created_at) VALUES (?1,?2,?3,?4,?5,?6,?7,?8)",
            params![node.id.to_string(), node.project_id.to_string(), node.label, node.node_type.to_string(), x, y, node.properties.to_string(), node.created_at.to_rfc3339()],
        )?;
        Ok(())
    }
    pub fn node_exists(&self, project_id: Uuid, node_id: Uuid) -> rusqlite::Result<bool> {
        self.c().query_row(
            "SELECT EXISTS(SELECT 1 FROM nodes WHERE id=?1 AND project_id=?2)",
            params![node_id.to_string(), project_id.to_string()],
            |r| r.get(0),
        )
    }
    pub fn list_nodes(&self, project_id: Uuid) -> rusqlite::Result<Vec<Node>> {
        let c = self.c();
        let mut stmt = c.prepare(
            "SELECT id,project_id,label,node_type,x,y,properties,created_at FROM nodes WHERE project_id=?1")?;
        let rows = stmt.query_map(params![project_id.to_string()], row_node)?;
        rows.collect::<Result<Vec<_>, _>>()
    }
    pub fn get_node(&self, project_id: Uuid, node_id: Uuid) -> rusqlite::Result<Option<Node>> {
        self.c().query_row(
            "SELECT id,project_id,label,node_type,x,y,properties,created_at FROM nodes WHERE id=?1 AND project_id=?2",
            params![node_id.to_string(), project_id.to_string()], row_node).optional()
    }
    pub fn update_node(
        &self,
        project_id: Uuid,
        node_id: Uuid,
        req: &UpdateNodeRequest,
    ) -> rusqlite::Result<Option<Node>> {
        let mut existing = match self.get_node(project_id, node_id)? {
            Some(n) => n,
            None => return Ok(None),
        };
        if let Some(ref l) = req.label {
            existing.label = l.clone();
        }
        if let Some(ref nt) = req.node_type {
            existing.node_type = nt.clone();
        }
        if let Some(ref pos) = req.position {
            existing.position = Some(pos.clone());
        }

        // Merge explicit properties
        if let Some(ref props) = req.properties {
            if let (serde_json::Value::Object(ref mut e), serde_json::Value::Object(ref i)) =
                (&mut existing.properties, props)
            {
                for (k, v) in i {
                    e.insert(k.clone(), v.clone());
                }
            } else {
                existing.properties = props.clone();
            }
        }

        // Handle content fields
        if let Some(ref c) = req.content {
            if let serde_json::Value::Object(ref mut map) = existing.properties {
                map.insert("content".into(), serde_json::Value::String(c.clone()));
            }
        }
        if let Some(ref ac) = req.append_content {
            if let serde_json::Value::Object(ref mut map) = existing.properties {
                let current = map.get("content").and_then(|v| v.as_str()).unwrap_or("");
                let new_content = format!("{current}\n{ac}");
                map.insert("content".into(), serde_json::Value::String(new_content));
            }
        }
        if let Some(ref v) = req.set_verdict {
            if let serde_json::Value::Object(ref mut map) = existing.properties {
                map.insert("verdict".into(), serde_json::Value::String(v.clone()));
            }
        }
        if let Some(ref e) = req.set_evidence {
            if let serde_json::Value::Object(ref mut map) = existing.properties {
                map.insert("evidence".into(), serde_json::Value::String(e.clone()));
            }
        }

        let (x, y) = match &existing.position {
            Some(p) => (Some(p.x), Some(p.y)),
            None => (None, None),
        };
        self.c().execute(
            "UPDATE nodes SET label=?1,node_type=?2,x=?3,y=?4,properties=?5 WHERE id=?6 AND project_id=?7",
            params![existing.label, existing.node_type.to_string(), x, y, existing.properties.to_string(), node_id.to_string(), project_id.to_string()],
        )?;
        Ok(Some(existing))
    }
    pub fn count_edges_for_node(&self, project_id: Uuid, node_id: Uuid) -> rusqlite::Result<usize> {
        self.c().query_row(
            "SELECT COUNT(*) FROM edges WHERE project_id=?1 AND (source_node_id=?2 OR target_node_id=?2)",
            params![project_id.to_string(), node_id.to_string()],
            |r| r.get(0),
        )
    }
    pub fn delete_node(&self, project_id: Uuid, node_id: Uuid) -> rusqlite::Result<bool> {
        let n = self.c().execute(
            "DELETE FROM nodes WHERE id=?1 AND project_id=?2",
            params![node_id.to_string(), project_id.to_string()],
        )?;
        if n > 0 {
            self.c().execute("DELETE FROM edges WHERE project_id=?1 AND (source_node_id=?2 OR target_node_id=?2)",
                params![project_id.to_string(), node_id.to_string()])?;
        }
        Ok(n > 0)
    }
}

// ── CRUD: Edges ────────────────────────────────────

impl Database {
    pub fn insert_edge(&self, edge: &Edge) -> rusqlite::Result<()> {
        let label = edge.label.as_deref().unwrap_or("");
        self.c().execute(
            "INSERT INTO edges (id,project_id,source_node_id,target_node_id,label,created_at) VALUES (?1,?2,?3,?4,?5,?6)",
            params![edge.id.to_string(), edge.project_id.to_string(), edge.source_node_id.to_string(), edge.target_node_id.to_string(), label, edge.created_at.to_rfc3339()],
        )?;
        Ok(())
    }
    pub fn list_edges(&self, project_id: Uuid) -> rusqlite::Result<Vec<Edge>> {
        let c = self.c();
        let mut stmt = c.prepare(
            "SELECT id,project_id,source_node_id,target_node_id,label,created_at FROM edges WHERE project_id=?1")?;
        let rows = stmt.query_map(params![project_id.to_string()], row_edge)?;
        rows.collect::<Result<Vec<_>, _>>()
    }
    pub fn update_edge(
        &self,
        project_id: Uuid,
        edge_id: Uuid,
        label: &str,
    ) -> rusqlite::Result<Option<Edge>> {
        self.c().execute(
            "UPDATE edges SET label=?1 WHERE id=?2 AND project_id=?3",
            params![label, edge_id.to_string(), project_id.to_string()],
        )?;
        self.c().query_row(
            "SELECT id,project_id,source_node_id,target_node_id,label,created_at FROM edges WHERE id=?1 AND project_id=?2",
            params![edge_id.to_string(), project_id.to_string()], row_edge).optional()
    }

    pub fn delete_edge(&self, project_id: Uuid, edge_id: Uuid) -> rusqlite::Result<bool> {
        Ok(self.c().execute(
            "DELETE FROM edges WHERE id=?1 AND project_id=?2",
            params![edge_id.to_string(), project_id.to_string()],
        )? > 0)
    }

    pub fn edge_exists(
        &self,
        project_id: Uuid,
        source_node_id: Uuid,
        target_node_id: Uuid,
        label: &str,
    ) -> rusqlite::Result<bool> {
        let n: usize = self.c().query_row(
            "SELECT COUNT(*) FROM edges WHERE project_id=?1 AND source_node_id=?2 AND target_node_id=?3 AND label=?4",
            params![project_id.to_string(), source_node_id.to_string(), target_node_id.to_string(), label],
            |r| r.get(0),
        )?;
        Ok(n > 0)
    }

    /// Search nodes by keyword (matches label + content in properties)
    pub fn search_nodes(&self, project_id: Uuid, query: &str) -> rusqlite::Result<Vec<Node>> {
        let all = self.list_nodes(project_id)?;
        let q = query.to_lowercase();
        Ok(all
            .into_iter()
            .filter(|n| {
                n.label.to_lowercase().contains(&q)
                    || n.properties
                        .get("content")
                        .and_then(|v| v.as_str())
                        .map(|c| c.to_lowercase().contains(&q))
                        .unwrap_or(false)
            })
            .collect())
    }

    pub fn nodes_by_date(&self, project_id: Uuid, date: &str) -> rusqlite::Result<Vec<Node>> {
        let all = self.list_nodes(project_id)?;
        let cst = chrono::FixedOffset::east_opt(8 * 3600).unwrap();
        Ok(all
            .into_iter()
            .filter(|n| {
                n.created_at
                    .with_timezone(&cst)
                    .format("%Y-%m-%d")
                    .to_string()
                    == date
            })
            .collect())
    }

    pub fn find_node_by_label(
        &self,
        project_id: Uuid,
        label: &str,
    ) -> rusqlite::Result<Option<Node>> {
        self.c().query_row(
            "SELECT id,project_id,label,node_type,x,y,properties,created_at FROM nodes WHERE project_id=?1 AND label=?2",
            params![project_id.to_string(), label], row_node).optional()
    }

    pub fn find_nodes_by_label_fuzzy(
        &self,
        project_id: Uuid,
        label: &str,
    ) -> rusqlite::Result<Vec<Node>> {
        let c = self.c();
        let mut stmt = c.prepare(
            "SELECT id,project_id,label,node_type,x,y,properties,created_at FROM nodes WHERE project_id=?1 AND label LIKE '%' || ?2 || '%'")?;
        let rows = stmt.query_map(params![project_id.to_string(), label], row_node)?;
        rows.collect::<Result<Vec<_>, _>>()
    }
}

// ── CRUD: Credentials ─────────────────────────────────

impl Database {
    pub fn create_credential(
        &self,
        pid: Uuid,
        req: &CreateCredentialRequest,
    ) -> rusqlite::Result<Credential> {
        let c = Credential {
            id: Uuid::new_v4(),
            project_id: pid,
            label: req.label.clone(),
            credential_type: req.credential_type.clone(),
            username: req.username.clone(),
            secret: req.secret.clone(),
            source_node_id: req.source_node_id,
            status: req.status.clone(),
            notes: req.notes.clone(),
            created_at: Utc::now(),
        };
        self.c().execute(
            "INSERT INTO credentials (id,project_id,label,credential_type,username,secret,source_node_id,status,notes,created_at) VALUES (?1,?2,?3,?4,?5,?6,?7,?8,?9,?10)",
            params![
                c.id.to_string(), c.project_id.to_string(), c.label,
                c.credential_type.to_string(), c.username, c.secret,
                c.source_node_id.map(|u| u.to_string()), c.status.to_string(), c.notes,
                c.created_at.to_rfc3339()
            ],
        )?;
        Ok(c)
    }

    pub fn list_credentials(&self, pid: Uuid) -> rusqlite::Result<Vec<Credential>> {
        let c = self.c();
        let mut stmt = c.prepare(
            "SELECT id,project_id,label,credential_type,username,secret,source_node_id,status,notes,created_at FROM credentials WHERE project_id=?1")?;
        let rows = stmt.query_map(params![pid.to_string()], row_credential)?;
        rows.collect::<Result<Vec<_>, _>>()
    }

    pub fn get_credential(&self, pid: Uuid, cid: Uuid) -> rusqlite::Result<Option<Credential>> {
        self.c().query_row(
            "SELECT id,project_id,label,credential_type,username,secret,source_node_id,status,notes,created_at FROM credentials WHERE id=?1 AND project_id=?2",
            params![cid.to_string(), pid.to_string()], row_credential).optional()
    }

    pub fn update_credential(
        &self,
        pid: Uuid,
        cid: Uuid,
        req: &UpdateCredentialRequest,
    ) -> rusqlite::Result<Option<Credential>> {
        let existing = match self.get_credential(pid, cid)? {
            Some(c) => c,
            None => return Ok(None),
        };
        let mut c = existing;
        if let Some(ref l) = req.label {
            c.label = l.clone();
        }
        if let Some(ref ct) = req.credential_type {
            c.credential_type = ct.clone();
        }
        if let Some(ref u) = req.username {
            c.username = Some(u.clone());
        }
        if let Some(ref s) = req.secret {
            c.secret = s.clone();
        }
        if let Some(ref snid) = req.source_node_id {
            c.source_node_id = *snid;
        }
        if let Some(ref st) = req.status {
            c.status = st.clone();
        }
        if let Some(ref n) = req.notes {
            c.notes = n.clone();
        }
        self.c().execute(
            "UPDATE credentials SET label=?1,credential_type=?2,username=?3,secret=?4,source_node_id=?5,status=?6,notes=?7 WHERE id=?8 AND project_id=?9",
            params![c.label, c.credential_type.to_string(), c.username, c.secret, c.source_node_id.map(|u| u.to_string()), c.status.to_string(), c.notes, cid.to_string(), pid.to_string()],
        )?;
        Ok(Some(c))
    }

    pub fn delete_credential(&self, pid: Uuid, cid: Uuid) -> rusqlite::Result<bool> {
        Ok(self.c().execute(
            "DELETE FROM credentials WHERE id=?1 AND project_id=?2",
            params![cid.to_string(), pid.to_string()],
        )? > 0)
    }

    pub fn create_intent(
        &self,
        project_id: Uuid,
        node_id: &str,
        direction: &str,
        description: Option<&str>,
    ) -> Result<ExplorationIntent, rusqlite::Error> {
        let id = uuid::Uuid::new_v4().to_string();
        let now = chrono::Utc::now().to_rfc3339();
        self.c().execute(
            "INSERT INTO intents (id, project_id, node_id, direction, description, status, created_at, updated_at) VALUES (?1, ?2, ?3, ?4, ?5, 'open', ?6, ?7)",
            params![id, project_id.to_string(), node_id, direction, description, &now, &now],
        )?;
        self.get_intent(&id)
            .map(|opt| opt.expect("just inserted intent"))
    }

    pub fn list_intents(
        &self,
        project_id: Uuid,
        node_id: Option<&str>,
        status: Option<&str>,
    ) -> Result<Vec<ExplorationIntent>, rusqlite::Error> {
        let mut intents = Vec::new();
        
        if node_id.is_none() && status.is_none() {
            let conn = self.c();
            let mut stmt = conn.prepare("SELECT * FROM intents WHERE project_id = ?1 ORDER BY created_at DESC")?;
            let rows = stmt.query_map(params![project_id.to_string()], row_intent)?;
            for row in rows {
                intents.push(row?);
            }
            return Ok(intents);
        }
        
        let mut sql = "SELECT * FROM intents WHERE project_id = ?1".to_string();
        let nid_str: Option<String> = node_id.map(|s| s.to_string());
        let s_str: Option<String> = status.map(|s| s.to_string());
        
        if node_id.is_some() {
            sql.push_str(" AND node_id = ?2");
        }
        if status.is_some() {
            sql.push_str(" AND status = ?3");
        }
        sql.push_str(" ORDER BY created_at DESC");
        
        let conn = self.c();
        let mut stmt = conn.prepare(&sql)?;
        let rows = match (&nid_str, &s_str) {
            (Some(n), Some(s)) => stmt.query_map(params![project_id.to_string(), n, s], row_intent)?,
            (Some(n), None) => stmt.query_map(params![project_id.to_string(), n], row_intent)?,
            (None, Some(s)) => stmt.query_map(params![project_id.to_string(), s], row_intent)?,
            (None, None) => unreachable!(),
        };
        for row in rows {
            intents.push(row?);
        }
        Ok(intents)
    }

    pub fn get_intent(&self, id: &str) -> Result<Option<ExplorationIntent>, rusqlite::Error> {
        let conn = self.c();
        let mut stmt = conn.prepare("SELECT * FROM intents WHERE id = ?1")?;
        let mut rows = stmt.query(params![id])?;
        if let Some(row) = rows.next()? {
            Ok(Some(row_intent(row)?))
        } else {
            Ok(None)
        }
    }

    pub fn update_intent_status(
        &self,
        id: &str,
        status: &str,
        worker: Option<&str>,
    ) -> Result<ExplorationIntent, rusqlite::Error> {
        let now = chrono::Utc::now().to_rfc3339();
        self.c().execute(
            "UPDATE intents SET status = ?1, worker = ?2, updated_at = ?3 WHERE id = ?4",
            params![status, worker, now, id],
        )?;
        self.get_intent(id)
            .map(|opt| opt.expect("intent should exist after update"))
    }

    pub fn delete_intent(&self, id: &str) -> Result<bool, rusqlite::Error> {
        Ok(self.c().execute("DELETE FROM intents WHERE id=?1", params![id])? > 0)
    }

    pub fn claim_intent(&self, id: &str, worker: &str) -> Result<ExplorationIntent, rusqlite::Error> {
        self.update_intent_status(id, "claimed", Some(worker))
    }

    pub fn complete_intent(&self, id: &str) -> Result<ExplorationIntent, rusqlite::Error> {
        self.update_intent_status(id, "completed", None)
    }
}

// ── CRUD: Evidence Files ────────────────────────────────

impl Database {
    pub fn insert_evidence_file(&self, file: &EvidenceFile) -> rusqlite::Result<()> {
        self.c().execute(
            "INSERT INTO evidence_files (id,project_id,node_id,original_name,stored_name,file_size,mime_type,created_at) VALUES (?1,?2,?3,?4,?5,?6,?7,?8)",
            params![file.id.to_string(), file.project_id.to_string(), file.node_id.to_string(), file.original_name, file.stored_name, file.file_size, file.mime_type, file.created_at.to_rfc3339()],
        )?;
        Ok(())
    }

    pub fn list_evidence_files(&self, pid: Uuid, nid: Uuid) -> rusqlite::Result<Vec<EvidenceFile>> {
        let c = self.c();
        let mut stmt = c.prepare(
            "SELECT id,project_id,node_id,original_name,stored_name,file_size,mime_type,created_at FROM evidence_files WHERE project_id=?1 AND node_id=?2")?;
        let rows = stmt.query_map(params![pid.to_string(), nid.to_string()], row_evidence_file)?;
        rows.collect::<Result<Vec<_>, _>>()
    }

    pub fn get_evidence_file(
        &self,
        pid: Uuid,
        nid: Uuid,
        stored_name: &str,
    ) -> rusqlite::Result<Option<EvidenceFile>> {
        self.c().query_row(
            "SELECT id,project_id,node_id,original_name,stored_name,file_size,mime_type,created_at FROM evidence_files WHERE project_id=?1 AND node_id=?2 AND stored_name=?3",
            params![pid.to_string(), nid.to_string(), stored_name], row_evidence_file).optional()
    }

    pub fn delete_evidence_file(
        &self,
        pid: Uuid,
        nid: Uuid,
        stored_name: &str,
    ) -> rusqlite::Result<bool> {
        Ok(self.c().execute(
            "DELETE FROM evidence_files WHERE project_id=?1 AND node_id=?2 AND stored_name=?3",
            params![pid.to_string(), nid.to_string(), stored_name],
        )? > 0)
    }
}

// ── Timeline ──────────────────────────────────────────

impl Database {
    pub fn project_timeline(&self, pid: Uuid) -> rusqlite::Result<Vec<TimelineItem>> {
        let nodes = self.list_nodes(pid)?;
        let edges = self.list_edges(pid)?;
        let node_by_id: std::collections::HashMap<Uuid, Node> =
            nodes.iter().cloned().map(|n| (n.id, n)).collect();
        let mut incoming: std::collections::HashMap<Uuid, Vec<&Edge>> =
            std::collections::HashMap::new();
        let mut outgoing: std::collections::HashMap<Uuid, Vec<&Edge>> =
            std::collections::HashMap::new();
        for e in &edges {
            incoming.entry(e.target_node_id).or_default().push(e);
            outgoing.entry(e.source_node_id).or_default().push(e);
        }
        let mut items: Vec<TimelineItem> = nodes
            .into_iter()
            .map(|n| {
                let incoming_edges: Vec<TimelineEdgeInfo> = incoming
                    .get(&n.id)
                    .cloned()
                    .unwrap_or_default()
                    .into_iter()
                    .map(|e| {
                        let other_id = e.source_node_id;
                        let other_label = node_by_id
                            .get(&other_id)
                            .map(|on| on.label.clone())
                            .unwrap_or_default();
                        TimelineEdgeInfo {
                            edge_id: e.id,
                            label: e.label.clone().unwrap_or_default(),
                            other_node_id: other_id,
                            other_node_label: other_label,
                            direction: "incoming".into(),
                        }
                    })
                    .collect();
                let outgoing_edges: Vec<TimelineEdgeInfo> = outgoing
                    .get(&n.id)
                    .cloned()
                    .unwrap_or_default()
                    .into_iter()
                    .map(|e| {
                        let other_id = e.target_node_id;
                        let other_label = node_by_id
                            .get(&other_id)
                            .map(|on| on.label.clone())
                            .unwrap_or_default();
                        TimelineEdgeInfo {
                            edge_id: e.id,
                            label: e.label.clone().unwrap_or_default(),
                            other_node_id: other_id,
                            other_node_label: other_label,
                            direction: "outgoing".into(),
                        }
                    })
                    .collect();
                TimelineItem {
                    node_id: n.id,
                    project_id: n.project_id,
                    node_label: n.label,
                    node_type: n.node_type.to_string(),
                    created_at: n.created_at,
                    properties: n.properties,
                    incoming: incoming_edges,
                    outgoing: outgoing_edges,
                }
            })
            .collect();
        items.sort_by(|a, b| a.created_at.cmp(&b.created_at));
        Ok(items)
    }
}

// ── Global Search ─────────────────────────────────────

impl Database {
    pub fn global_search(&self, query: &str) -> rusqlite::Result<Vec<SearchResult>> {
        let q = format!("%{}%", query.to_lowercase());
        let c = self.c();
        let mut stmt = c.prepare(
            "SELECT n.id, n.project_id, n.label, n.node_type, n.properties, p.name
             FROM nodes n
             JOIN projects p ON n.project_id = p.id
             WHERE LOWER(n.label) LIKE ?1 OR LOWER(n.properties) LIKE ?1
             LIMIT 50",
        )?;
        let rows = stmt.query_map(params![q], |row| {
            let nid_s: String = row.get(0)?;
            let pid_s: String = row.get(1)?;
            let label: String = row.get(2)?;
            let nt_s: String = row.get(3)?;
            let props_s: String = row.get(4)?;
            let project_name: String = row.get(5)?;
            let match_context = props_s.chars().take(200).collect::<String>();
            Ok(SearchResult {
                project_id: Uuid::parse_str(&pid_s).unwrap(),
                project_name,
                node_id: Uuid::parse_str(&nid_s).unwrap(),
                node_label: label,
                node_type: nt_s,
                match_context,
            })
        })?;
        rows.collect::<Result<Vec<_>, _>>()
    }
}


impl Database {
    pub fn check(&self, project_id: Uuid) -> rusqlite::Result<CheckResult> {
        let nodes = self.list_nodes(project_id)?;
        let edges = self.list_edges(project_id)?;
        Ok(self._check_graph(&nodes, &edges))
    }

    fn _check_graph(&self, nodes: &[Node], edges: &[Edge]) -> CheckResult {
        use std::collections::HashMap;

        let mut children: HashMap<Uuid, Vec<&Edge>> = HashMap::new();
        for edge in edges {
            children.entry(edge.source_node_id).or_default().push(edge);
        }
        let mut parents: HashMap<Uuid, Vec<&Edge>> = HashMap::new();
        for edge in edges {
            parents.entry(edge.target_node_id).or_default().push(edge);
        }
        let node_by_id: HashMap<Uuid, &Node> = nodes.iter().map(|node| (node.id, node)).collect();
        let node_type = |node: &Node| -> String { node.node_type.to_string() };
        let has_parent_type = |node: &Node, allowed: &[&str]| -> bool {
            parents.get(&node.id).map_or(false, |incoming| {
                incoming.iter().any(|edge| {
                    node_by_id
                        .get(&edge.source_node_id)
                        .map_or(false, |source| {
                            allowed.contains(&node_type(source).as_str())
                        })
                })
            })
        };
        let has_child_type = |node: &Node, allowed: &[&str]| -> bool {
            children.get(&node.id).map_or(false, |outgoing| {
                outgoing.iter().any(|edge| {
                    node_by_id
                        .get(&edge.target_node_id)
                        .map_or(false, |target| {
                            allowed.contains(&node_type(target).as_str())
                        })
                })
            })
        };
        let mut issues = Vec::new();

        for node in nodes {
            let node_type = node.node_type.to_string();

            if (node_type == "vulnerability"
                || node_type == "credential"
                || node_type == "privilege_escalation")
                && !has_parent_type(
                    node,
                    &["reconnaissance", "attack_step", "exploitation", "finding"],
                )
            {
                let description = match node_type.as_str() {
                    "vulnerability" => "漏洞节点无侦察/攻击前置，来源不明".to_string(),
                    "credential" => "凭据节点无攻击步骤解释获取方式".to_string(),
                    _ => "提权节点无前置攻击路径".to_string(),
                };
                issues.push(CheckIssue {
                    rule: "missing_provenance".to_string(),
                    severity: "warning".to_string(),
                    node_id: node.id.to_string(),
                    node_label: node.label.clone(),
                    description,
                });
            }

            if node.properties.get("evidence").and_then(|v| v.as_str()) == Some("hypothesis")
                && !has_child_type(node, &["attack_step", "exploitation"])
            {
                issues.push(CheckIssue {
                    rule: "unverified_hypothesis".to_string(),
                    severity: "info".to_string(),
                    node_id: node.id.to_string(),
                    node_label: node.label.clone(),
                    description: format!("hypothesis 节点「{}」无验证计划", node.label),
                });
            }

            if node_type == "vulnerability"
                && node.properties.get("verdict").and_then(|v| v.as_str()) == Some("success")
                && !has_child_type(node, &["attack_step", "exploitation"])
            {
                issues.push(CheckIssue {
                    rule: "unused_vulnerability".to_string(),
                    severity: "warning".to_string(),
                    node_id: node.id.to_string(),
                    node_label: node.label.clone(),
                    description: format!("已确认漏洞「{}」未被利用", node.label),
                });
            }

            if (node_type == "attack_step" || node_type == "exploitation")
                && !has_parent_type(
                    node,
                    &["vulnerability", "credential", "privilege_escalation"],
                )
            {
                issues.push(CheckIssue {
                    rule: "dangling_exploit".to_string(),
                    severity: "warning".to_string(),
                    node_id: node.id.to_string(),
                    node_label: node.label.clone(),
                    description: format!("利用节点「{}」无前置漏洞/凭据——因果链断裂", node.label),
                });
            }
        }

        CheckResult {
            total_nodes: nodes.len(),
            total_edges: edges.len(),
            issues,
        }
    }

    pub fn analyze(&self, project_id: Uuid) -> rusqlite::Result<AttackAnalysis> {
        let nodes = self.list_nodes(project_id)?;
        let edges = self.list_edges(project_id)?;
        use std::collections::{HashMap, HashSet};

        let mut children: HashMap<Uuid, Vec<&Edge>> = HashMap::new();
        for e in &edges {
            children.entry(e.source_node_id).or_default().push(e);
        }
        let mut parents: HashMap<Uuid, Vec<&Edge>> = HashMap::new();
        for e in &edges {
            parents.entry(e.target_node_id).or_default().push(e);
        }
        let node_by_id: HashMap<Uuid, &Node> = nodes.iter().map(|n| (n.id, n)).collect();
        let verdict = |n: &Node| -> Option<String> {
            n.properties
                .get("verdict")
                .and_then(|v| v.as_str().map(String::from))
        };
        let is_vuln = |n: &Node| -> bool {
            let t = n.node_type.to_string();
            t == "vulnerability" || t == "privilege_escalation"
        };

        // Attack paths: BFS from entry points
        let mut attack_paths: Vec<AttackPath> = Vec::new();
        let entries: Vec<&Node> = nodes
            .iter()
            .filter(|n| {
                let t = n.node_type.to_string();
                t == "objective" || t == "entry_point"
            })
            .collect();
        for entry in &entries {
            let mut queue: Vec<(Uuid, Vec<Uuid>, Vec<String>)> =
                vec![(entry.id, vec![entry.id], vec![])];
            while let Some((cur, path, labels)) = queue.pop() {
                let has_child = children.get(&cur).map_or(false, |v| !v.is_empty());
                if !has_child || verdict(node_by_id.get(&cur).unwrap()) == Some("success".into()) {
                    let pn: Vec<PathNode> = path
                        .iter()
                        .filter_map(|id| {
                            node_by_id.get(id).map(|n| PathNode {
                                id: n.id.to_string(),
                                label: n.label.clone(),
                            })
                        })
                        .collect();
                    if pn.len() > 1 {
                        attack_paths.push(AttackPath {
                            nodes: pn,
                            edge_labels: labels.clone(),
                        });
                    }
                }
                if let Some(ces) = children.get(&cur) {
                    for e in ces {
                        // Skip self-loops and already-visited nodes in this path
                        if e.target_node_id == cur || path.contains(&e.target_node_id) {
                            continue;
                        }
                        let mut np = path.clone();
                        np.push(e.target_node_id);
                        let mut nl = labels.clone();
                        nl.push(e.label.clone().unwrap_or_default());
                        queue.push((e.target_node_id, np, nl));
                    }
                }
            }
        }
        let mut seen: HashSet<Vec<String>> = HashSet::new();
        attack_paths.retain(|p| {
            let k: Vec<String> = p.nodes.iter().map(|n| n.id.clone()).collect();
            seen.insert(k)
        });

        // Unexploited vulns
        let unexploited: Vec<TaggedNode> = nodes
            .iter()
            .filter(|n| {
                if !is_vuln(n) {
                    return false;
                }
                let exploited = children.get(&n.id).map_or(false, |ces| {
                    ces.iter().any(|e| {
                        node_by_id
                            .get(&e.target_node_id)
                            .map_or(false, |t| verdict(t) == Some("success".into()))
                    })
                });
                !exploited && verdict(n) != Some("failed".into())
            })
            .map(|n| {
                let content = n
                    .properties
                    .get("content")
                    .and_then(|v| v.as_str())
                    .unwrap_or("")
                    .to_string();
                TaggedNode {
                    id: n.id.to_string(),
                    label: n.label.clone(),
                    node_type: n.node_type.to_string(),
                    content,
                    properties: n.properties.clone(),
                }
            })
            .collect();

        // Dead ends
        let dead_ends: Vec<TaggedNode> = nodes
            .iter()
            .filter(|n| verdict(n) == Some("failed".into()))
            .map(|n| {
                let content = n
                    .properties
                    .get("content")
                    .and_then(|v| v.as_str())
                    .unwrap_or("")
                    .to_string();
                TaggedNode {
                    id: n.id.to_string(),
                    label: n.label.clone(),
                    node_type: n.node_type.to_string(),
                    content,
                    properties: n.properties.clone(),
                }
            })
            .collect();

        // Next steps
        let mut next_steps: Vec<NextStep> = Vec::new();
        for uv in &unexploited {
            let uv_id: Uuid = uv.id.parse().unwrap();
            for entry in &entries {
                let mut queue: Vec<(Uuid, Vec<Uuid>)> = vec![(entry.id, vec![entry.id])];
                let mut found: Option<Vec<String>> = None;
                while let Some((cur, path)) = queue.pop() {
                    if cur == uv_id {
                        found = Some(
                            path.iter()
                                .filter_map(|id| node_by_id.get(id).map(|n| n.label.clone()))
                                .collect(),
                        );
                        break;
                    }
                    if let Some(ces) = children.get(&cur) {
                        for e in ces {
                            if !path.contains(&e.target_node_id) {
                                let mut np = path.clone();
                                np.push(e.target_node_id);
                                queue.push((e.target_node_id, np));
                            }
                        }
                    }
                }
                if let Some(fp) = found {
                    let evidence_level = uv
                        .properties
                        .get("evidence")
                        .and_then(|v| v.as_str())
                        .unwrap_or("unverified");
                    let has_outgoing = children.contains_key(&uv_id);
                    let has_exploit_child = has_outgoing
                        && children.get(&uv_id).map_or(false, |ces| {
                            ces.iter().any(|e| {
                                node_by_id.get(&e.target_node_id).map_or(false, |t| {
                                    let tt = t.node_type.to_string();
                                    tt == "attack_step"
                                        || tt == "exploitation"
                                        || tt == "privilege_escalation"
                                })
                            })
                        });
                    let chain_status = if !has_outgoing {
                        "no_outgoing"
                    } else if !has_exploit_child {
                        "no_exploit"
                    } else {
                        "has_exploit"
                    };

                    let suggestion = match evidence_level {
                        "confirmed" => {
                            if !has_outgoing {
                                format!("🚩 「{}」已确认但未建立利用节点 — 建议添加 attack_step 节点记录利用尝试", uv.label)
                            } else if !has_exploit_child {
                                format!("🚩 「{}」已确认但后续节点不是利用步骤 — 考虑添加 exploitation 节点", uv.label)
                            } else {
                                format!("「{}」已确认，已有利用节点 — 检查利用结果", uv.label)
                            }
                        }
                        "unverified" | _ => {
                            if uv.content.is_empty() {
                                format!(
                                    "⚠️ 「{}」证据不足且无详细记录 — 先补充 content 再验证",
                                    uv.label
                                )
                            } else {
                                format!("⚠️ 「{}」尚未验证 — 先确认漏洞是否存在（evidence=confirmed），再考虑利用", uv.label)
                            }
                        }
                    };
                    next_steps.push(NextStep {
                        node_id: uv.id.clone(),
                        label: uv.label.clone(),
                        node_type: uv.node_type.clone(),
                        suggestion,
                        from_path: fp,
                        evidence: evidence_level.to_string(),
                        chain_status: chain_status.to_string(),
                    });
                    break;
                }
            }
        }
        // Isolated nodes: nodes with no edges (neither source nor target)
        let mut connected: HashSet<Uuid> = HashSet::new();
        for e in &edges {
            connected.insert(e.source_node_id);
            connected.insert(e.target_node_id);
        }
        let isolated_nodes: Vec<TaggedNode> = nodes
            .iter()
            .filter(|n| !connected.contains(&n.id))
            .map(|n| {
                let content = n
                    .properties
                    .get("content")
                    .and_then(|v| v.as_str())
                    .unwrap_or("")
                    .to_string();
                TaggedNode {
                    id: n.id.to_string(),
                    label: n.label.clone(),
                    node_type: n.node_type.to_string(),
                    content,
                    properties: n.properties.clone(),
                }
            })
            .collect();

        let mut health_checks: Vec<HealthIssue> = Vec::new();

        for entry in &entries {
            if !children.contains_key(&entry.id) {
                health_checks.push(HealthIssue {
                    severity: "warning".to_string(),
                    check_type: "entry_isolation".to_string(),
                    description: format!("入口节点「{}」没有出边——侦察未开始", entry.label),
                    node_ids: vec![entry.id.to_string()],
                    node_labels: vec![entry.label.clone()],
                });
            }
        }

        for node in &nodes {
            let node_type = node.node_type.to_string();
            if node_type != "exploitation"
                && node_type != "attack_step"
                && node_type != "privilege_escalation"
            {
                continue;
            }
            let Some(incoming) = parents.get(&node.id) else {
                continue;
            };
            let has_vuln_predecessor = incoming.iter().any(|edge| {
                node_by_id
                    .get(&edge.source_node_id)
                    .map_or(false, |predecessor| is_vuln(predecessor))
            });
            if !has_vuln_predecessor {
                health_checks.push(HealthIssue {
                    severity: "warning".to_string(),
                    check_type: "dangling_exploit".to_string(),
                    description: format!(
                        "利用节点「{}」的前置节点不是漏洞——因果链可能断裂",
                        node.label
                    ),
                    node_ids: vec![node.id.to_string()],
                    node_labels: vec![node.label.clone()],
                });
            }
        }

        for edge in &edges {
            let Some(source) = node_by_id.get(&edge.source_node_id) else {
                continue;
            };
            let Some(target) = node_by_id.get(&edge.target_node_id) else {
                continue;
            };
            if verdict(source) == Some("failed".into()) && verdict(target) == Some("success".into())
            {
                health_checks.push(HealthIssue {
                    severity: "error".to_string(),
                    check_type: "broken_chain".to_string(),
                    description: format!(
                        "断裂链路：「{}」(failed) → 「{}」(success) —— 失败节点后不应有成功节点",
                        source.label, target.label
                    ),
                    node_ids: vec![source.id.to_string(), target.id.to_string()],
                    node_labels: vec![source.label.clone(), target.label.clone()],
                });
            }
        }

        let isolated_ids: HashSet<Uuid> = isolated_nodes
            .iter()
            .filter_map(|n| n.id.parse().ok())
            .collect();
        let mut reachable: HashSet<Uuid> = HashSet::new();
        let mut queue: Vec<Uuid> = entries.iter().map(|n| n.id).collect();
        while let Some(current) = queue.pop() {
            if !reachable.insert(current) {
                continue;
            }
            if let Some(outgoing) = children.get(&current) {
                for edge in outgoing {
                    queue.push(edge.target_node_id);
                }
            }
        }
        for node in &nodes {
            if !reachable.contains(&node.id) && !isolated_ids.contains(&node.id) {
                health_checks.push(HealthIssue {
                    severity: "warning".to_string(),
                    check_type: "unreachable".to_string(),
                    description: format!("节点「{}」不在任何入口可达的路径上", node.label),
                    node_ids: vec![node.id.to_string()],
                    node_labels: vec![node.label.clone()],
                });
            }
        }

        Ok(AttackAnalysis {
            attack_paths,
            unexploited_vulns: unexploited,
            dead_ends,
            next_steps,
            cross_connections: vec![],
            isolated_nodes,
            health_checks,
            check_summary: Some(self._check_graph(&nodes, &edges)),
            ai_hint: None,
        })
    }
}

// ── Agent Memory ──────────────────────────────────

impl Database {
    /// 保存 Agent 记忆消息
    pub fn save_memory(
        &self,
        project_id: Uuid,
        role: &str,
        content: &str,
        tool_calls: Option<&str>,
        run_id: &str,
    ) -> Result<(), rusqlite::Error> {
        let id = Uuid::new_v4().to_string();
        let now = Utc::now().to_rfc3339();
        let pid = project_id.to_string();
        self.c().execute(
            "INSERT INTO agent_memory (id, project_id, role, content, tool_calls, run_id, created_at) VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7)",
            params![id, pid, role, content, tool_calls, run_id, now],
        )?;
        Ok(())
    }

    /// 加载最近的 Agent 记忆
    pub fn load_recent_memory(
        &self,
        project_id: Uuid,
        limit: u32,
    ) -> Result<Vec<AgentMemoryMessage>, rusqlite::Error> {
        let pid = project_id.to_string();
        let conn = self.c();
        let mut stmt = conn.prepare(
            "SELECT id, project_id, role, content, tool_calls, run_id, created_at 
             FROM agent_memory 
             WHERE project_id = ?1 
             ORDER BY created_at DESC 
             LIMIT ?2",
        )?;
        let rows = stmt.query_map(params![pid, limit], |row| {
            let id: String = row.get(0)?;
            let pid_s: String = row.get(1)?;
            let ca: String = row.get(6)?;
            Ok(AgentMemoryMessage {
                id,
                project_id: Uuid::parse_str(&pid_s).unwrap(),
                role: row.get(2)?,
                content: row.get(3)?,
                tool_calls: row.get(4)?,
                run_id: row.get(5)?,
                created_at: ca.parse().unwrap(),
            })
        })?;
        let mut messages = Vec::new();
        for row in rows {
            messages.push(row?);
        }
        messages.reverse(); // 按时间正序
        Ok(messages)
    }

    /// 清理旧记忆（保留最近 N 条）
    pub fn prune_memory(&self, project_id: Uuid, keep: u32) -> Result<(), rusqlite::Error> {
        let pid = project_id.to_string();
        self.c().execute(
            "DELETE FROM agent_memory WHERE project_id = ?1 AND id NOT IN (
                SELECT id FROM agent_memory WHERE project_id = ?1 ORDER BY created_at DESC LIMIT ?2
            )",
            params![pid, keep],
        )?;
        Ok(())
    }
}

#[cfg(test)]
mod tests {
    use super::*;
    use chrono::{TimeZone, Utc};
    use rusqlite::ErrorCode;
    use serde_json::json;

    fn test_db() -> Database {
        Database::open(":memory:").expect("open in-memory database")
    }

    fn create_project(db: &Database, name: &str) -> Project {
        db.create_project(&CreateProjectRequest {
            name: name.to_string(),
        })
        .expect("create project")
    }

    fn make_node(
        project_id: Uuid,
        label: &str,
        node_type: NodeType,
        created_at: chrono::DateTime<Utc>,
        properties: serde_json::Value,
    ) -> Node {
        Node {
            id: Uuid::new_v4(),
            project_id,
            label: label.to_string(),
            node_type,
            position: None,
            properties,
            created_at,
        }
    }

    fn insert_node(
        db: &Database,
        project_id: Uuid,
        label: &str,
        node_type: NodeType,
        created_at: chrono::DateTime<Utc>,
        properties: serde_json::Value,
    ) -> Node {
        let node = make_node(project_id, label, node_type, created_at, properties);
        db.insert_node(&node).expect("insert node");
        node
    }

    fn insert_edge(
        db: &Database,
        project_id: Uuid,
        source_node_id: Uuid,
        target_node_id: Uuid,
        label: Option<&str>,
        created_at: chrono::DateTime<Utc>,
    ) -> Edge {
        let edge = Edge {
            id: Uuid::new_v4(),
            project_id,
            source_node_id,
            target_node_id,
            label: label.map(str::to_string),
            created_at,
        };
        db.insert_edge(&edge).expect("insert edge");
        edge
    }

    fn sample_credential_request(source_node_id: Option<Uuid>) -> CreateCredentialRequest {
        CreateCredentialRequest {
            label: "Admin password".to_string(),
            credential_type: CredentialType::Password,
            username: Some("admin".to_string()),
            secret: "p@ssw0rd".to_string(),
            source_node_id,
            status: CredentialStatus::Unverified,
            notes: Some("initial dump".to_string()),
        }
    }

    fn sample_evidence(project_id: Uuid, node_id: Uuid, stored_name: &str) -> EvidenceFile {
        EvidenceFile {
            id: Uuid::new_v4(),
            project_id,
            node_id,
            original_name: "screenshot.png".to_string(),
            stored_name: stored_name.to_string(),
            file_size: 1337,
            mime_type: Some("image/png".to_string()),
            created_at: Utc.with_ymd_and_hms(2026, 5, 28, 8, 0, 0).unwrap(),
        }
    }

    #[test]
    fn project_crud_and_counts_work() {
        let db = test_db();
        let older = create_project(&db, "Older Project");
        let newer = create_project(&db, "Newer Project");

        let projects = db.list_projects().expect("list projects");
        assert_eq!(projects.len(), 2);
        assert_eq!(projects[0].id, newer.id);
        assert_eq!(projects[1].id, older.id);

        let fetched = db
            .get_project(newer.id)
            .expect("get project")
            .expect("project exists");
        assert_eq!(fetched.name, "Newer Project");

        let found = db
            .find_project_by_name("Older Project")
            .expect("find project by name")
            .expect("project found by name");
        assert_eq!(found.id, older.id);

        assert_eq!(db.count_nodes_for_project(newer.id).unwrap(), 0);
        assert_eq!(db.count_edges_for_project(newer.id).unwrap(), 0);

        assert!(db
            .delete_project(older.id)
            .expect("delete existing project"));
        assert!(!db.delete_project(older.id).expect("delete missing project"));
        assert!(db.get_project(older.id).unwrap().is_none());
        assert!(db.find_project_by_name("missing").unwrap().is_none());
    }

    #[test]
    fn node_crud_search_and_delete_behaviors_work() {
        let db = test_db();
        let project = create_project(&db, "Node Project");
        let created_at = Utc.with_ymd_and_hms(2026, 5, 28, 1, 2, 3).unwrap();

        let node = insert_node(
            &db,
            project.id,
            "Web Panel",
            NodeType::Service,
            created_at,
            json!({"content":"nginx login page","verdict":"pending"}),
        );
        let sibling = insert_node(
            &db,
            project.id,
            "Admin API",
            NodeType::Note,
            created_at + chrono::Duration::seconds(1),
            json!({"content":"contains SSRF primitive"}),
        );

        assert!(db.node_exists(project.id, node.id).unwrap());
        assert!(!db.node_exists(project.id, Uuid::new_v4()).unwrap());

        let nodes = db.list_nodes(project.id).expect("list nodes");
        assert_eq!(nodes.len(), 2);

        let fetched = db
            .get_node(project.id, node.id)
            .expect("get node")
            .expect("node exists");
        assert_eq!(fetched.label, "Web Panel");
        assert_eq!(fetched.node_type, NodeType::Service);

        let updated = db
            .update_node(
                project.id,
                node.id,
                &UpdateNodeRequest {
                    label: Some("Web Portal".to_string()),
                    node_type: Some(NodeType::EntryPoint),
                    position: Some(NodePosition { x: 10.5, y: 20.0 }),
                    properties: Some(json!({"phase":"recon","content":"base note"})),
                    content: Some("replaced content".to_string()),
                    append_content: Some("extra line".to_string()),
                    set_verdict: Some("success".to_string()),
                    set_evidence: Some("confirmed".to_string()),
                },
            )
            .expect("update node")
            .expect("updated node returned");
        assert_eq!(updated.label, "Web Portal");
        assert_eq!(updated.node_type, NodeType::EntryPoint);
        assert_eq!(
            updated.position.as_ref().map(|p| (p.x, p.y)),
            Some((10.5, 20.0))
        );
        assert_eq!(updated.properties["phase"], "recon");
        assert_eq!(updated.properties["verdict"], "success");
        assert_eq!(updated.properties["evidence"], "confirmed");
        assert_eq!(
            updated.properties["content"],
            "replaced content\nextra line"
        );

        assert!(db
            .search_nodes(project.id, "portal")
            .unwrap()
            .iter()
            .any(|n| n.id == node.id));
        assert!(db
            .search_nodes(project.id, "ssrf")
            .unwrap()
            .iter()
            .any(|n| n.id == sibling.id));
        assert!(db
            .find_node_by_label(project.id, "Web Portal")
            .unwrap()
            .is_some());
        assert_eq!(
            db.find_nodes_by_label_fuzzy(project.id, "API")
                .unwrap()
                .len(),
            1
        );
        assert_eq!(db.nodes_by_date(project.id, "2026-05-28").unwrap().len(), 2);

        assert!(db
            .update_node(
                project.id,
                Uuid::new_v4(),
                &UpdateNodeRequest {
                    label: Some("missing".to_string()),
                    node_type: None,
                    position: None,
                    properties: None,
                    content: None,
                    append_content: None,
                    set_verdict: None,
                    set_evidence: None,
                }
            )
            .unwrap()
            .is_none());

        assert!(db.delete_node(project.id, sibling.id).unwrap());
        assert!(!db.delete_node(project.id, sibling.id).unwrap());
        assert!(db.get_node(project.id, sibling.id).unwrap().is_none());
    }

    #[test]
    fn edge_crud_and_node_delete_cascade_work() {
        let db = test_db();
        let project = create_project(&db, "Edge Project");
        let t0 = Utc.with_ymd_and_hms(2026, 5, 28, 2, 0, 0).unwrap();

        let source = insert_node(
            &db,
            project.id,
            "Source",
            NodeType::EntryPoint,
            t0,
            json!({}),
        );
        let target = insert_node(
            &db,
            project.id,
            "Target",
            NodeType::Vulnerability,
            t0 + chrono::Duration::seconds(1),
            json!({}),
        );
        let extra = insert_node(
            &db,
            project.id,
            "Extra",
            NodeType::AttackStep,
            t0 + chrono::Duration::seconds(2),
            json!({}),
        );

        let edge = insert_edge(&db, project.id, source.id, target.id, Some("finds"), t0);
        let second = insert_edge(
            &db,
            project.id,
            target.id,
            extra.id,
            None,
            t0 + chrono::Duration::seconds(1),
        );

        let edges = db.list_edges(project.id).expect("list edges");
        assert_eq!(edges.len(), 2);
        assert!(db
            .edge_exists(project.id, source.id, target.id, "finds")
            .unwrap());
        assert!(!db
            .edge_exists(project.id, source.id, target.id, "missing")
            .unwrap());
        assert_eq!(db.count_edges_for_node(project.id, target.id).unwrap(), 2);

        let updated = db
            .update_edge(project.id, edge.id, "validated")
            .expect("update edge")
            .expect("updated edge returned");
        assert_eq!(updated.label.as_deref(), Some("validated"));
        assert!(db
            .update_edge(project.id, Uuid::new_v4(), "noop")
            .unwrap()
            .is_none());

        assert!(db.delete_edge(project.id, second.id).unwrap());
        assert!(!db.delete_edge(project.id, second.id).unwrap());

        let third = insert_edge(
            &db,
            project.id,
            source.id,
            target.id,
            Some("relinked"),
            t0 + chrono::Duration::seconds(3),
        );
        assert!(db.delete_node(project.id, target.id).unwrap());
        assert!(!db
            .edge_exists(project.id, source.id, target.id, "relinked")
            .unwrap());
        assert!(db
            .list_edges(project.id)
            .unwrap()
            .into_iter()
            .all(|e| e.id != third.id));
    }

    #[test]
    fn credential_crud_and_foreign_key_errors_work() {
        let db = test_db();
        let project = create_project(&db, "Credential Project");
        let node = insert_node(
            &db,
            project.id,
            "Jump Host",
            NodeType::Host,
            Utc.with_ymd_and_hms(2026, 5, 28, 3, 0, 0).unwrap(),
            json!({}),
        );

        let created = db
            .create_credential(project.id, &sample_credential_request(Some(node.id)))
            .expect("create credential");
        assert_eq!(created.label, "Admin password");

        let credentials = db.list_credentials(project.id).expect("list credentials");
        assert_eq!(credentials.len(), 1);

        let fetched = db
            .get_credential(project.id, created.id)
            .expect("get credential")
            .expect("credential exists");
        assert_eq!(fetched.username.as_deref(), Some("admin"));

        let updated = db
            .update_credential(
                project.id,
                created.id,
                &UpdateCredentialRequest {
                    label: Some("Rotated admin password".to_string()),
                    credential_type: Some(CredentialType::Token),
                    username: Some("root".to_string()),
                    secret: Some("token-123".to_string()),
                    source_node_id: Some(None),
                    status: Some(CredentialStatus::Verified),
                    notes: Some(None),
                },
            )
            .expect("update credential")
            .expect("updated credential returned");
        assert_eq!(updated.label, "Rotated admin password");
        assert_eq!(updated.credential_type, CredentialType::Token);
        assert_eq!(updated.username.as_deref(), Some("root"));
        assert_eq!(updated.secret, "token-123");
        assert_eq!(updated.source_node_id, None);
        assert_eq!(updated.status, CredentialStatus::Verified);
        assert_eq!(updated.notes, None);

        assert!(db
            .update_credential(
                project.id,
                Uuid::new_v4(),
                &UpdateCredentialRequest {
                    label: Some("missing".to_string()),
                    credential_type: None,
                    username: None,
                    secret: None,
                    source_node_id: None,
                    status: None,
                    notes: None,
                },
            )
            .unwrap()
            .is_none());

        let fk_error = db
            .create_credential(project.id, &sample_credential_request(Some(Uuid::new_v4())))
            .expect_err("foreign key violation for missing source node");
        match fk_error {
            rusqlite::Error::SqliteFailure(err, _) => {
                assert_eq!(err.code, ErrorCode::ConstraintViolation);
            }
            other => panic!("unexpected error: {other:?}"),
        }

        assert!(db.delete_credential(project.id, created.id).unwrap());
        assert!(!db.delete_credential(project.id, created.id).unwrap());
        assert!(db.get_credential(project.id, created.id).unwrap().is_none());
    }

    #[test]
    fn evidence_crud_and_foreign_key_errors_work() {
        let db = test_db();
        let project = create_project(&db, "Evidence Project");
        let node = insert_node(
            &db,
            project.id,
            "Landing Page",
            NodeType::EntryPoint,
            Utc.with_ymd_and_hms(2026, 5, 28, 4, 0, 0).unwrap(),
            json!({}),
        );

        let file = sample_evidence(project.id, node.id, "abc123.png");
        db.insert_evidence_file(&file)
            .expect("insert evidence file");

        let files = db
            .list_evidence_files(project.id, node.id)
            .expect("list evidence files");
        assert_eq!(files.len(), 1);
        assert_eq!(files[0].mime_type.as_deref(), Some("image/png"));

        let fetched = db
            .get_evidence_file(project.id, node.id, "abc123.png")
            .expect("get evidence file")
            .expect("evidence file exists");
        assert_eq!(fetched.original_name, "screenshot.png");

        let missing_node_file = sample_evidence(project.id, Uuid::new_v4(), "missing.png");
        let fk_error = db
            .insert_evidence_file(&missing_node_file)
            .expect_err("foreign key violation for missing node");
        match fk_error {
            rusqlite::Error::SqliteFailure(err, _) => {
                assert_eq!(err.code, ErrorCode::ConstraintViolation);
            }
            other => panic!("unexpected error: {other:?}"),
        }

        assert!(db
            .delete_evidence_file(project.id, node.id, "abc123.png")
            .unwrap());
        assert!(!db
            .delete_evidence_file(project.id, node.id, "abc123.png")
            .unwrap());
        assert!(db
            .get_evidence_file(project.id, node.id, "abc123.png")
            .unwrap()
            .is_none());
    }

    #[test]
    fn analysis_timeline_and_global_search_work() {
        let db = test_db();
        let project = create_project(&db, "Analysis Project");
        let other_project = create_project(&db, "Other Project");
        let base = Utc.with_ymd_and_hms(2026, 5, 28, 0, 0, 0).unwrap();

        let entry = insert_node(
            &db,
            project.id,
            "Public Login",
            NodeType::EntryPoint,
            base,
            json!({"content":"internet-facing portal"}),
        );
        let vuln = insert_node(
            &db,
            project.id,
            "SQL Injection",
            NodeType::Vulnerability,
            base + chrono::Duration::minutes(1),
            json!({"content":"blind injection in login form","evidence":"confirmed","verdict":"pending"}),
        );
        let exploit_failed = insert_node(
            &db,
            project.id,
            "Dump creds attempt",
            NodeType::AttackStep,
            base + chrono::Duration::minutes(2),
            json!({"verdict":"failed"}),
        );
        let success = insert_node(
            &db,
            project.id,
            "Shell Access",
            NodeType::Custom("exploitation".to_string()),
            base + chrono::Duration::minutes(3),
            json!({"verdict":"success"}),
        );
        let unattached = insert_node(
            &db,
            project.id,
            "Notes Only",
            NodeType::Note,
            base + chrono::Duration::minutes(4),
            json!({"content":"isolated observation"}),
        );
        let disconnected_parent = insert_node(
            &db,
            project.id,
            "Disconnected Finding",
            NodeType::Finding,
            base + chrono::Duration::minutes(5),
            json!({"content":"not reachable from entry"}),
        );
        let unreachable = insert_node(
            &db,
            project.id,
            "Internal Host",
            NodeType::Host,
            base + chrono::Duration::minutes(6),
            json!({"content":"disconnected cluster"}),
        );

        insert_edge(
            &db,
            project.id,
            entry.id,
            vuln.id,
            Some("finds"),
            base + chrono::Duration::seconds(30),
        );
        insert_edge(
            &db,
            project.id,
            vuln.id,
            exploit_failed.id,
            Some("attempts"),
            base + chrono::Duration::minutes(2),
        );
        insert_edge(
            &db,
            project.id,
            exploit_failed.id,
            success.id,
            Some("unexpected success"),
            base + chrono::Duration::minutes(3),
        );
        insert_edge(
            &db,
            project.id,
            disconnected_parent.id,
            unreachable.id,
            Some("mentions"),
            base + chrono::Duration::minutes(6),
        );

        let unrelated = insert_node(
            &db,
            other_project.id,
            "Legacy SQL Injection",
            NodeType::Vulnerability,
            base,
            json!({"content":"another project finding"}),
        );
        assert_eq!(unrelated.project_id, other_project.id);

        let timeline = db.project_timeline(project.id).expect("project timeline");
        assert_eq!(timeline.len(), 7);
        assert_eq!(timeline[0].node_id, entry.id);
        let timeline_vuln = timeline
            .iter()
            .find(|item| item.node_id == vuln.id)
            .expect("timeline contains vulnerability node");
        assert_eq!(timeline_vuln.incoming.len(), 1);
        assert_eq!(timeline_vuln.outgoing.len(), 1);
        assert_eq!(timeline_vuln.incoming[0].other_node_label, "Public Login");
        assert_eq!(
            timeline_vuln.outgoing[0].other_node_label,
            "Dump creds attempt"
        );

        let search = db.global_search("sql injection").expect("global search");
        assert_eq!(search.len(), 2);
        assert!(search.iter().any(|hit| hit.project_id == project.id));
        assert!(search.iter().any(|hit| hit.project_id == other_project.id));
        assert!(search.iter().any(|hit| hit.node_label == "SQL Injection"
            && hit.match_context.contains("blind injection")));

        let check = db.check(project.id).expect("graph check");
        assert_eq!(check.total_nodes, 7);
        assert_eq!(check.total_edges, 4);
        assert!(check
            .issues
            .iter()
            .any(|issue| issue.rule == "dangling_exploit"));

        let analysis = db.analyze(project.id).expect("attack analysis");
        assert!(analysis.attack_paths.iter().any(|path| {
            path.nodes
                .iter()
                .map(|n| n.label.as_str())
                .collect::<Vec<_>>()
                == vec![
                    "Public Login",
                    "SQL Injection",
                    "Dump creds attempt",
                    "Shell Access",
                ]
        }));
        assert!(analysis
            .unexploited_vulns
            .iter()
            .any(|node| node.label == "SQL Injection"));
        assert!(analysis
            .dead_ends
            .iter()
            .any(|node| node.label == "Dump creds attempt"));
        assert!(analysis
            .next_steps
            .iter()
            .any(|step| step.label == "SQL Injection" && step.chain_status == "has_exploit"));
        assert!(analysis
            .isolated_nodes
            .iter()
            .any(|node| node.label == "Notes Only"));
        assert!(analysis
            .isolated_nodes
            .iter()
            .all(|node| node.label != "Internal Host"));
        assert!(analysis
            .health_checks
            .iter()
            .any(|issue| issue.check_type == "broken_chain"));
        assert!(analysis
            .health_checks
            .iter()
            .any(|issue| issue.check_type == "unreachable"
                && issue
                    .node_labels
                    .iter()
                    .any(|label| label == "Internal Host")));
        assert_eq!(
            analysis.check_summary.as_ref().map(|s| s.total_edges),
            Some(4)
        );

        let delete_missing = db
            .delete_project(Uuid::new_v4())
            .expect("delete missing project id");
        assert!(!delete_missing);
        let _ = unattached;
    }
}
