minni 0.1.1

mod schema;

use anyhow::{Context, Result};
use rusqlite::{params, Connection, OptionalExtension};
use serde::{Deserialize, Serialize};
use std::path::{Path, PathBuf};

pub use schema::SCHEMA;

const MINNI_DIR: &str = ".minni";
const DB_FILE: &str = "minni.db";

/// Parsed code chunk stored in SQLite.
#[derive(Debug, Clone, Serialize, Deserialize)]
pub struct CodeChunk {
    pub id: String,
    pub file_path: String,
    pub content: String,
    pub start_line: u32,
    pub end_line: u32,
    pub chunk_type: String,
    pub language: String,
    pub symbol_name: Option<String>,
    pub content_hash: String,
    pub indexed_at: String,
    pub parent_symbol: Option<String>,
    pub signature: Option<String>,
    pub doc_comment: Option<String>,
    pub module_path: Option<String>,
}

/// Import or dependency edge.
#[derive(Debug, Clone, Serialize, Deserialize)]
pub struct SymbolEdge {
    pub id: String,
    pub source_file: String,
    pub target_symbol: String,
    pub edge_type: String,
    pub target_file: Option<String>,
    pub indexed_at: String,
}

/// Saved session context.
#[derive(Debug, Clone, Serialize, Deserialize)]
pub struct SessionContext {
    pub id: String,
    pub name: String,
    pub description: Option<String>,
    pub created_at: String,
    pub updated_at: String,
    pub project_path: String,
}

/// Key-value context item.
#[derive(Debug, Clone, Serialize, Deserialize)]
pub struct ContextItem {
    pub id: String,
    pub context_id: String,
    pub key: String,
    pub value: String,
    pub item_type: String,
    pub created_at: String,
}

/// SQLite-backed project database.
pub struct Database {
    conn: Connection,
    pub project_root: PathBuf,
}

impl Database {
    /// Expose the underlying connection for use by other modules (e.g. TaskManager).
    pub fn conn(&self) -> &Connection {
        &self.conn
    }

    /// Construct a Database from its parts; primarily intended for tests.
    pub fn from_parts(conn: Connection, project_root: PathBuf) -> Self {
        Self { conn, project_root }
    }

    pub fn open(project_root: &Path) -> Result<Self> {
        let minni_dir = project_root.join(MINNI_DIR);
        let db_path = minni_dir.join(DB_FILE);

        let conn = Connection::open(&db_path)
            .with_context(|| format!("Failed to open database at {:?}", db_path))?;
        conn.execute_batch(SCHEMA)
            .context("Failed to apply database schema updates")?;

        Self::migrate_schema(&conn)?;

        Ok(Self {
            conn,
            project_root: project_root.to_path_buf(),
        })
    }

    pub fn initialize(project_root: &Path) -> Result<Self> {
        let minni_dir = project_root.join(MINNI_DIR);
        std::fs::create_dir_all(&minni_dir)
            .with_context(|| format!("Failed to create .minni directory at {:?}", minni_dir))?;

        let db_path = minni_dir.join(DB_FILE);
        let conn = Connection::open(&db_path)
            .with_context(|| format!("Failed to create database at {:?}", db_path))?;

        conn.execute_batch(SCHEMA)
            .context("Failed to initialize database schema")?;

        Self::migrate_schema(&conn)?;

        Ok(Self {
            conn,
            project_root: project_root.to_path_buf(),
        })
    }

    pub fn minni_dir_exists(project_root: &Path) -> bool {
        project_root.join(MINNI_DIR).join(DB_FILE).exists()
    }

    /// Apply any pending schema migrations.
    fn migrate_schema(conn: &Connection) -> Result<()> {
        // Read current schema version
        let version: Option<String> = conn
            .query_row(
                "SELECT value FROM metadata WHERE key = 'schema_version'",
                [],
                |row| row.get(0),
            )
            .optional()
            .context("Failed to query schema version")?;

        let version_num: u32 = version.as_deref().unwrap_or("1").parse().unwrap_or(1);

        if version_num < 3 {
            // Migrate v1/v2 → v3: add four new metadata columns to chunks
            let migrations = [
                "ALTER TABLE chunks ADD COLUMN parent_symbol TEXT",
                "ALTER TABLE chunks ADD COLUMN signature TEXT",
                "ALTER TABLE chunks ADD COLUMN doc_comment TEXT",
                "ALTER TABLE chunks ADD COLUMN module_path TEXT",
            ];
            for sql in &migrations {
                if let Err(e) = conn.execute_batch(sql) {
                    // Only tolerate "duplicate column name" — the column already exists,
                    // which is fine (e.g. migration was partially applied before).
                    // Any other error (I/O, schema corruption, …) must be propagated.
                    let msg = e.to_string().to_lowercase();
                    if !msg.contains("duplicate column name") {
                        return Err(e).context(format!("Schema migration failed for: {sql}"));
                    }
                }
            }
            conn.execute_batch(
                "INSERT OR REPLACE INTO metadata (key, value) VALUES ('schema_version', '3')",
            )
            .context("Failed to update schema_version to 3")?;
        }

        if version_num < 4 {
            // Migrate v3 → v4: add symbol_edges table for import/dependency edges
            let migrations = [
                "CREATE TABLE IF NOT EXISTS symbol_edges (
                    id TEXT PRIMARY KEY,
                    source_file TEXT NOT NULL,
                    target_symbol TEXT NOT NULL,
                    edge_type TEXT NOT NULL DEFAULT 'imports',
                    target_file TEXT,
                    indexed_at TEXT NOT NULL
                )",
                "CREATE INDEX IF NOT EXISTS idx_edges_source_file ON symbol_edges(source_file)",
                "CREATE INDEX IF NOT EXISTS idx_edges_target_symbol ON symbol_edges(target_symbol)",
                "CREATE INDEX IF NOT EXISTS idx_edges_edge_type ON symbol_edges(edge_type)",
            ];
            for sql in &migrations {
                conn.execute_batch(sql)
                    .context(format!("Schema migration v3→v4 failed for: {sql}"))?;
            }
            conn.execute_batch(
                "INSERT OR REPLACE INTO metadata (key, value) VALUES ('schema_version', '4')",
            )
            .context("Failed to update schema_version to 4")?;
        }

        if version_num < 5 {
            // Migrate v4 → v5: add tasks and task_todos tables
            let migrations = [
                "CREATE TABLE IF NOT EXISTS tasks (
                    id          TEXT PRIMARY KEY,
                    context_id  TEXT NOT NULL,
                    seq         INTEGER NOT NULL,
                    title       TEXT NOT NULL,
                    description TEXT,
                    status      TEXT NOT NULL DEFAULT 'pending',
                    priority    TEXT NOT NULL DEFAULT 'medium',
                    created_at  TEXT NOT NULL,
                    updated_at  TEXT NOT NULL,
                    FOREIGN KEY (context_id) REFERENCES contexts(id) ON DELETE CASCADE,
                    UNIQUE (context_id, seq)
                )",
                "CREATE INDEX IF NOT EXISTS idx_tasks_context ON tasks(context_id)",
                "CREATE INDEX IF NOT EXISTS idx_tasks_status  ON tasks(status)",
                "CREATE TABLE IF NOT EXISTS task_todos (
                    id          TEXT PRIMARY KEY,
                    task_id     TEXT NOT NULL,
                    seq         INTEGER NOT NULL,
                    text        TEXT NOT NULL,
                    done        INTEGER NOT NULL DEFAULT 0,
                    created_at  TEXT NOT NULL,
                    FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE,
                    UNIQUE (task_id, seq)
                )",
                "CREATE INDEX IF NOT EXISTS idx_task_todos_task ON task_todos(task_id)",
            ];
            for sql in &migrations {
                conn.execute_batch(sql)
                    .context(format!("Schema migration v4→v5 failed for: {sql}"))?;
            }
            conn.execute_batch(
                "UPDATE OR IGNORE metadata SET value = '5' WHERE key = 'schema_version' AND value = '4'",
            )
            .context("Failed to update schema_version to 5")?;
        }

        Ok(())
    }

    // Chunk operations
    pub fn insert_chunk(&self, chunk: &CodeChunk) -> Result<()> {
        self.conn.execute(
            "INSERT OR REPLACE INTO chunks (id, file_path, content, start_line, end_line, chunk_type, language, symbol_name, content_hash, indexed_at, parent_symbol, signature, doc_comment, module_path)
             VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12, ?13, ?14)",
            params![
                chunk.id,
                chunk.file_path,
                chunk.content,
                chunk.start_line,
                chunk.end_line,
                chunk.chunk_type,
                chunk.language,
                chunk.symbol_name,
                chunk.content_hash,
                chunk.indexed_at,
                chunk.parent_symbol,
                chunk.signature,
                chunk.doc_comment,
                chunk.module_path,
            ],
        )?;
        Ok(())
    }

    #[allow(dead_code)]
    pub fn get_chunk(&self, id: &str) -> Result<Option<CodeChunk>> {
        let mut stmt = self.conn.prepare(
            "SELECT id, file_path, content, start_line, end_line, chunk_type, language, symbol_name, content_hash, indexed_at, parent_symbol, signature, doc_comment, module_path
             FROM chunks WHERE id = ?1",
        )?;

        let chunk = stmt
            .query_row(params![id], |row| {
                Ok(CodeChunk {
                    id: row.get(0)?,
                    file_path: row.get(1)?,
                    content: row.get(2)?,
                    start_line: row.get(3)?,
                    end_line: row.get(4)?,
                    chunk_type: row.get(5)?,
                    language: row.get(6)?,
                    symbol_name: row.get(7)?,
                    content_hash: row.get(8)?,
                    indexed_at: row.get(9)?,
                    parent_symbol: row.get(10)?,
                    signature: row.get(11)?,
                    doc_comment: row.get(12)?,
                    module_path: row.get(13)?,
                })
            })
            .optional()?;

        Ok(chunk)
    }

    #[allow(dead_code)]
    pub fn get_chunks_by_file(&self, file_path: &str) -> Result<Vec<CodeChunk>> {
        let mut stmt = self.conn.prepare(
            "SELECT id, file_path, content, start_line, end_line, chunk_type, language, symbol_name, content_hash, indexed_at, parent_symbol, signature, doc_comment, module_path
             FROM chunks WHERE file_path = ?1 ORDER BY start_line",
        )?;

        let chunks = stmt
            .query_map(params![file_path], |row| {
                Ok(CodeChunk {
                    id: row.get(0)?,
                    file_path: row.get(1)?,
                    content: row.get(2)?,
                    start_line: row.get(3)?,
                    end_line: row.get(4)?,
                    chunk_type: row.get(5)?,
                    language: row.get(6)?,
                    symbol_name: row.get(7)?,
                    content_hash: row.get(8)?,
                    indexed_at: row.get(9)?,
                    parent_symbol: row.get(10)?,
                    signature: row.get(11)?,
                    doc_comment: row.get(12)?,
                    module_path: row.get(13)?,
                })
            })?
            .collect::<std::result::Result<Vec<_>, _>>()?;

        Ok(chunks)
    }

    pub fn delete_chunks_by_file(&self, file_path: &str) -> Result<usize> {
        let deleted = self.conn.execute(
            "DELETE FROM chunks WHERE file_path = ?1",
            params![file_path],
        )?;
        Ok(deleted)
    }

    // Edge operations
    pub fn insert_edge(&self, edge: &SymbolEdge) -> Result<()> {
        self.conn.execute(
            "INSERT OR REPLACE INTO symbol_edges (id, source_file, target_symbol, edge_type, target_file, indexed_at)
             VALUES (?1, ?2, ?3, ?4, ?5, ?6)",
            params![
                edge.id,
                edge.source_file,
                edge.target_symbol,
                edge.edge_type,
                edge.target_file,
                edge.indexed_at,
            ],
        )?;
        Ok(())
    }

    pub fn delete_edges_by_file(&self, source_file: &str) -> Result<usize> {
        let deleted = self.conn.execute(
            "DELETE FROM symbol_edges WHERE source_file = ?1",
            params![source_file],
        )?;
        Ok(deleted)
    }

    pub fn get_edges_by_source_file(&self, source_file: &str) -> Result<Vec<SymbolEdge>> {
        let mut stmt = self.conn.prepare(
            "SELECT id, source_file, target_symbol, edge_type, target_file, indexed_at
             FROM symbol_edges WHERE source_file = ?1 ORDER BY target_symbol",
        )?;

        let edges = stmt
            .query_map(params![source_file], |row| {
                Ok(SymbolEdge {
                    id: row.get(0)?,
                    source_file: row.get(1)?,
                    target_symbol: row.get(2)?,
                    edge_type: row.get(3)?,
                    target_file: row.get(4)?,
                    indexed_at: row.get(5)?,
                })
            })?
            .collect::<std::result::Result<Vec<_>, _>>()?;

        Ok(edges)
    }

    pub fn get_edges_by_target_symbol(&self, target_symbol: &str) -> Result<Vec<SymbolEdge>> {
        let mut stmt = self.conn.prepare(
            "SELECT id, source_file, target_symbol, edge_type, target_file, indexed_at
             FROM symbol_edges WHERE target_symbol LIKE '%' || ?1 || '%' ORDER BY source_file",
        )?;

        let edges = stmt
            .query_map(params![target_symbol], |row| {
                Ok(SymbolEdge {
                    id: row.get(0)?,
                    source_file: row.get(1)?,
                    target_symbol: row.get(2)?,
                    edge_type: row.get(3)?,
                    target_file: row.get(4)?,
                    indexed_at: row.get(5)?,
                })
            })?
            .collect::<std::result::Result<Vec<_>, _>>()?;

        Ok(edges)
    }

    pub fn get_all_chunks(&self) -> Result<Vec<CodeChunk>> {
        let mut stmt = self.conn.prepare(
            "SELECT id, file_path, content, start_line, end_line, chunk_type, language, symbol_name, content_hash, indexed_at, parent_symbol, signature, doc_comment, module_path
             FROM chunks ORDER BY file_path, start_line",
        )?;

        let chunks = stmt
            .query_map([], |row| {
                Ok(CodeChunk {
                    id: row.get(0)?,
                    file_path: row.get(1)?,
                    content: row.get(2)?,
                    start_line: row.get(3)?,
                    end_line: row.get(4)?,
                    chunk_type: row.get(5)?,
                    language: row.get(6)?,
                    symbol_name: row.get(7)?,
                    content_hash: row.get(8)?,
                    indexed_at: row.get(9)?,
                    parent_symbol: row.get(10)?,
                    signature: row.get(11)?,
                    doc_comment: row.get(12)?,
                    module_path: row.get(13)?,
                })
            })?
            .collect::<std::result::Result<Vec<_>, _>>()?;

        Ok(chunks)
    }

    pub fn get_file_hash(&self, file_path: &str) -> Result<Option<String>> {
        let mut stmt = self
            .conn
            .prepare("SELECT content_hash FROM file_hashes WHERE file_path = ?1")?;

        let hash = stmt
            .query_row(params![file_path], |row| row.get(0))
            .optional()?;

        Ok(hash)
    }

    pub fn set_file_hash(&self, file_path: &str, hash: &str) -> Result<()> {
        self.conn.execute(
            "INSERT OR REPLACE INTO file_hashes (file_path, content_hash, indexed_at)
             VALUES (?1, ?2, datetime('now'))",
            params![file_path, hash],
        )?;
        Ok(())
    }

    pub fn get_all_tracked_files(&self) -> Result<Vec<String>> {
        let mut stmt = self
            .conn
            .prepare("SELECT file_path FROM file_hashes ORDER BY file_path")?;
        let paths = stmt
            .query_map([], |row| row.get(0))?
            .collect::<std::result::Result<Vec<String>, _>>()?;
        Ok(paths)
    }

    pub fn get_chunk_ids_by_file(&self, file_path: &str) -> Result<Vec<String>> {
        let mut stmt = self
            .conn
            .prepare("SELECT id FROM chunks WHERE file_path = ?1")?;
        let ids = stmt
            .query_map(params![file_path], |row| row.get(0))?
            .collect::<std::result::Result<Vec<String>, _>>()?;
        Ok(ids)
    }

    pub fn delete_file_hash(&self, file_path: &str) -> Result<()> {
        self.conn.execute(
            "DELETE FROM file_hashes WHERE file_path = ?1",
            params![file_path],
        )?;
        Ok(())
    }

    // Dense embedding operations
    pub fn upsert_embedding(&self, chunk_id: &str, vector: &[f32]) -> Result<()> {
        let blob = serialize_embedding(vector);
        self.conn.execute(
            "INSERT OR REPLACE INTO embeddings (chunk_id, vector, indexed_at)
             VALUES (?1, ?2, datetime('now'))",
            params![chunk_id, blob],
        )?;
        Ok(())
    }

    pub fn clear_embeddings(&self) -> Result<()> {
        self.conn.execute("DELETE FROM embeddings", [])?;
        Ok(())
    }

    pub fn delete_embeddings_by_chunk_ids(&self, chunk_ids: &[String]) -> Result<usize> {
        if chunk_ids.is_empty() {
            return Ok(0);
        }

        const BATCH_SIZE: usize = 500;
        let mut total_deleted = 0usize;

        for batch in chunk_ids.chunks(BATCH_SIZE) {
            let placeholders: Vec<String> = (1..=batch.len()).map(|i| format!("?{}", i)).collect();
            let sql = format!(
                "DELETE FROM embeddings WHERE chunk_id IN ({})",
                placeholders.join(", ")
            );
            let params: Vec<&dyn rusqlite::ToSql> =
                batch.iter().map(|id| id as &dyn rusqlite::ToSql).collect();
            let deleted = self.conn.execute(&sql, params.as_slice())?;
            total_deleted += deleted;
        }

        Ok(total_deleted)
    }

    pub fn get_all_embeddings(&self) -> Result<Vec<(String, Vec<f32>)>> {
        let mut stmt = self
            .conn
            .prepare("SELECT chunk_id, vector FROM embeddings ORDER BY chunk_id")?;

        let rows = stmt.query_map([], |row| {
            let chunk_id: String = row.get(0)?;
            let vector_blob: Vec<u8> = row.get(1)?;
            let vector = deserialize_embedding(&vector_blob).map_err(|e| {
                rusqlite::Error::FromSqlConversionFailure(
                    vector_blob.len(),
                    rusqlite::types::Type::Blob,
                    Box::new(std::io::Error::new(
                        std::io::ErrorKind::InvalidData,
                        e.to_string(),
                    )),
                )
            })?;
            Ok((chunk_id, vector))
        })?;

        let mut embeddings = Vec::new();
        for row in rows {
            embeddings.push(row?);
        }
        Ok(embeddings)
    }

    // Context operations
    pub fn insert_context(&self, ctx: &SessionContext) -> Result<()> {
        self.conn.execute(
            "INSERT OR REPLACE INTO contexts (id, name, description, created_at, updated_at, project_path)
             VALUES (?1, ?2, ?3, ?4, ?5, ?6)",
            params![
                ctx.id,
                ctx.name,
                ctx.description,
                ctx.created_at,
                ctx.updated_at,
                ctx.project_path,
            ],
        )?;
        Ok(())
    }

    pub fn get_context(&self, id_or_name: &str) -> Result<Option<SessionContext>> {
        let mut stmt = self.conn.prepare(
            "SELECT id, name, description, created_at, updated_at, project_path
             FROM contexts WHERE id = ?1 OR name = ?1",
        )?;

        let ctx = stmt
            .query_row(params![id_or_name], |row| {
                Ok(SessionContext {
                    id: row.get(0)?,
                    name: row.get(1)?,
                    description: row.get(2)?,
                    created_at: row.get(3)?,
                    updated_at: row.get(4)?,
                    project_path: row.get(5)?,
                })
            })
            .optional()?;

        Ok(ctx)
    }

    pub fn list_contexts(&self) -> Result<Vec<SessionContext>> {
        let mut stmt = self.conn.prepare(
            "SELECT id, name, description, created_at, updated_at, project_path
             FROM contexts ORDER BY updated_at DESC",
        )?;

        let contexts = stmt
            .query_map([], |row| {
                Ok(SessionContext {
                    id: row.get(0)?,
                    name: row.get(1)?,
                    description: row.get(2)?,
                    created_at: row.get(3)?,
                    updated_at: row.get(4)?,
                    project_path: row.get(5)?,
                })
            })?
            .collect::<std::result::Result<Vec<_>, _>>()?;

        Ok(contexts)
    }

    pub fn delete_context(&self, id_or_name: &str) -> Result<bool> {
        // First get the actual ID
        if let Some(ctx) = self.get_context(id_or_name)? {
            // Delete context items first
            self.conn.execute(
                "DELETE FROM context_items WHERE context_id = ?1",
                params![ctx.id],
            )?;
            // Then delete the context
            let deleted = self
                .conn
                .execute("DELETE FROM contexts WHERE id = ?1", params![ctx.id])?;
            Ok(deleted > 0)
        } else {
            Ok(false)
        }
    }

    // Context item operations
    pub fn insert_context_item(&self, item: &ContextItem) -> Result<()> {
        self.conn.execute(
            "INSERT OR REPLACE INTO context_items (id, context_id, key, value, item_type, created_at)
             VALUES (?1, ?2, ?3, ?4, ?5, ?6)",
            params![
                item.id,
                item.context_id,
                item.key,
                item.value,
                item.item_type,
                item.created_at,
            ],
        )?;
        Ok(())
    }

    pub fn get_context_items(&self, context_id: &str) -> Result<Vec<ContextItem>> {
        let mut stmt = self.conn.prepare(
            "SELECT id, context_id, key, value, item_type, created_at
             FROM context_items WHERE context_id = ?1 ORDER BY created_at",
        )?;

        let items = stmt
            .query_map(params![context_id], |row| {
                Ok(ContextItem {
                    id: row.get(0)?,
                    context_id: row.get(1)?,
                    key: row.get(2)?,
                    value: row.get(3)?,
                    item_type: row.get(4)?,
                    created_at: row.get(5)?,
                })
            })?
            .collect::<std::result::Result<Vec<_>, _>>()?;

        Ok(items)
    }

    // Statistics
    pub fn get_stats(&self) -> Result<IndexStats> {
        let chunk_count: i64 = self
            .conn
            .query_row("SELECT COUNT(*) FROM chunks", [], |row| row.get(0))?;

        let file_count: i64 =
            self.conn
                .query_row("SELECT COUNT(DISTINCT file_path) FROM chunks", [], |row| {
                    row.get(0)
                })?;

        let context_count: i64 =
            self.conn
                .query_row("SELECT COUNT(*) FROM contexts", [], |row| row.get(0))?;

        let last_indexed: Option<String> = self
            .conn
            .query_row("SELECT MAX(indexed_at) FROM file_hashes", [], |row| {
                row.get(0)
            })
            .optional()?
            .flatten();

        Ok(IndexStats {
            chunk_count: chunk_count as usize,
            file_count: file_count as usize,
            context_count: context_count as usize,
            last_indexed,
        })
    }

    // Edge resolution

    /// Resolve `target_file` for edges whose target symbol matches a chunk's `symbol_name`.
    ///
    /// Uses a best-effort suffix match: e.g. `crate::db::Database` → matches chunk with
    /// `symbol_name = "Database"` in `src/db/mod.rs`.  Only rows where `target_file IS NULL`
    /// are updated, so this is safe to run multiple times.
    ///
    /// The suffix match uses `substr` rather than `LIKE` to avoid treating `%` and `_`
    /// in symbol names as SQL wildcards, and to skip empty symbol names.
    ///
    /// Returns the number of edges that now have a resolved `target_file`.
    pub fn resolve_edge_targets(&self) -> Result<usize> {
        self.conn.execute(
            "UPDATE symbol_edges
             SET target_file = (
                 SELECT c.file_path
                 FROM chunks c
                 WHERE c.symbol_name IS NOT NULL
                   AND c.symbol_name <> ''
                   AND substr(symbol_edges.target_symbol, -length(c.symbol_name)) = c.symbol_name
                 LIMIT 1
             )
             WHERE target_file IS NULL",
            [],
        )?;

        // Count only rows that were actually resolved (subquery found a match).
        let resolved: i64 = self.conn.query_row(
            "SELECT COUNT(*) FROM symbol_edges WHERE target_file IS NOT NULL",
            [],
            |row| row.get(0),
        )?;
        Ok(resolved as usize)
    }

    /// Return the total number of import edges stored.
    pub fn get_total_edge_count(&self) -> Result<usize> {
        let count: i64 = self
            .conn
            .query_row("SELECT COUNT(*) FROM symbol_edges", [], |row| row.get(0))?;
        Ok(count as usize)
    }

    /// Find chunks whose `symbol_name` contains `name` (case-insensitive substring).
    pub fn find_chunks_by_symbol_name(&self, name: &str) -> Result<Vec<CodeChunk>> {
        let mut stmt = self.conn.prepare(
            "SELECT id, file_path, content, start_line, end_line, chunk_type, language,
                    symbol_name, content_hash, indexed_at, parent_symbol, signature,
                    doc_comment, module_path
             FROM chunks
             WHERE symbol_name LIKE '%' || ?1 || '%'
             ORDER BY file_path, start_line",
        )?;

        let chunks = stmt
            .query_map(params![name], |row| {
                Ok(CodeChunk {
                    id: row.get(0)?,
                    file_path: row.get(1)?,
                    content: row.get(2)?,
                    start_line: row.get(3)?,
                    end_line: row.get(4)?,
                    chunk_type: row.get(5)?,
                    language: row.get(6)?,
                    symbol_name: row.get(7)?,
                    content_hash: row.get(8)?,
                    indexed_at: row.get(9)?,
                    parent_symbol: row.get(10)?,
                    signature: row.get(11)?,
                    doc_comment: row.get(12)?,
                    module_path: row.get(13)?,
                })
            })?
            .collect::<std::result::Result<Vec<_>, _>>()?;

        Ok(chunks)
    }

    /// Count edges where `target_symbol` contains `symbol_name` (same LIKE match as
    /// `get_edges_by_target_symbol`). Returns how many files/imports reference this symbol.
    pub fn get_referenced_by_count(&self, symbol_name: &str) -> Result<usize> {
        let count: i64 = self.conn.query_row(
            "SELECT COUNT(*) FROM symbol_edges WHERE target_symbol LIKE '%' || ?1 || '%'",
            params![symbol_name],
            |row| row.get(0),
        )?;
        Ok(count as usize)
    }
}

/// Database stats snapshot.
#[derive(Debug)]
pub struct IndexStats {
    pub chunk_count: usize,
    pub file_count: usize,
    pub context_count: usize,
    pub last_indexed: Option<String>,
}

fn serialize_embedding(vector: &[f32]) -> Vec<u8> {
    let mut bytes = Vec::with_capacity(vector.len() * 4);
    for &value in vector {
        bytes.extend_from_slice(&value.to_le_bytes());
    }
    bytes
}

fn deserialize_embedding(bytes: &[u8]) -> Result<Vec<f32>> {
    if !bytes.len().is_multiple_of(4) {
        return Err(anyhow::anyhow!(
            "Invalid embedding blob length: {}",
            bytes.len()
        ));
    }

    let mut vector = Vec::with_capacity(bytes.len() / 4);
    for chunk in bytes.chunks_exact(4) {
        vector.push(f32::from_le_bytes([chunk[0], chunk[1], chunk[2], chunk[3]]));
    }
    Ok(vector)
}

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

    /// Build an in-memory Database for testing (no filesystem dependency).
    fn in_memory_db() -> Database {
        let conn = Connection::open_in_memory().expect("in-memory DB");
        conn.execute_batch(SCHEMA).expect("schema");
        Database {
            conn,
            project_root: std::path::PathBuf::from("/tmp/test"),
        }
    }

    fn make_edge(source_file: &str, target_symbol: &str) -> SymbolEdge {
        SymbolEdge {
            id: uuid::Uuid::new_v4().to_string(),
            source_file: source_file.to_string(),
            target_symbol: target_symbol.to_string(),
            edge_type: "imports".to_string(),
            target_file: None,
            indexed_at: Utc::now().to_rfc3339(),
        }
    }

    #[test]
    fn test_get_referenced_by_count_zero_when_no_edges() {
        let db = in_memory_db();
        let count = db.get_referenced_by_count("SomeSymbol").unwrap();
        assert_eq!(count, 0);
    }

    #[test]
    fn test_get_referenced_by_count_exact_match() {
        let db = in_memory_db();
        db.insert_edge(&make_edge("src/a.rs", "crate::db::Database"))
            .unwrap();
        db.insert_edge(&make_edge("src/b.rs", "crate::db::Database"))
            .unwrap();
        db.insert_edge(&make_edge("src/c.rs", "crate::other::Thing"))
            .unwrap();

        let count = db.get_referenced_by_count("Database").unwrap();
        assert_eq!(count, 2);
    }

    #[test]
    fn test_get_referenced_by_count_substring_match() {
        let db = in_memory_db();
        // "Db" appears in both "MyDb" and "DbHelper"
        db.insert_edge(&make_edge("src/a.rs", "pkg::MyDb")).unwrap();
        db.insert_edge(&make_edge("src/b.rs", "pkg::DbHelper"))
            .unwrap();
        db.insert_edge(&make_edge("src/c.rs", "pkg::Unrelated"))
            .unwrap();

        let count = db.get_referenced_by_count("Db").unwrap();
        assert_eq!(count, 2);
    }

    #[test]
    fn test_get_referenced_by_count_no_match() {
        let db = in_memory_db();
        db.insert_edge(&make_edge("src/a.rs", "crate::db::Database"))
            .unwrap();

        let count = db.get_referenced_by_count("Unrelated").unwrap();
        assert_eq!(count, 0);
    }

    fn insert_test_chunk(db: &Database, chunk_id: &str) {
        db.insert_chunk(&CodeChunk {
            id: chunk_id.to_string(),
            file_path: "src/test.rs".to_string(),
            content: "test content".to_string(),
            start_line: 1,
            end_line: 5,
            chunk_type: "block".to_string(),
            language: "rust".to_string(),
            symbol_name: None,
            content_hash: chunk_id.to_string(),
            indexed_at: Utc::now().to_rfc3339(),
            parent_symbol: None,
            signature: None,
            doc_comment: None,
            module_path: None,
        })
        .unwrap();
    }

    fn insert_test_embedding(db: &Database, chunk_id: &str) {
        insert_test_chunk(db, chunk_id);
        db.upsert_embedding(chunk_id, &[1.0f32, 2.0, 3.0]).unwrap();
    }

    fn count_embeddings(db: &Database) -> usize {
        let count: usize = db
            .conn
            .query_row("SELECT COUNT(*) FROM embeddings", [], |row| row.get(0))
            .unwrap();
        count
    }

    #[test]
    fn test_delete_embeddings_by_chunk_ids_empty_slice() {
        let db = in_memory_db();
        insert_test_embedding(&db, "chunk-1");
        let deleted = db.delete_embeddings_by_chunk_ids(&[]).unwrap();
        assert_eq!(deleted, 0);
        assert_eq!(count_embeddings(&db), 1); // nothing deleted
    }

    #[test]
    fn test_delete_embeddings_by_chunk_ids_deletes_matched() {
        let db = in_memory_db();
        insert_test_embedding(&db, "chunk-1");
        insert_test_embedding(&db, "chunk-2");
        insert_test_embedding(&db, "chunk-3");

        let to_delete = vec!["chunk-1".to_string(), "chunk-3".to_string()];
        let deleted = db.delete_embeddings_by_chunk_ids(&to_delete).unwrap();
        assert_eq!(deleted, 2);
        assert_eq!(count_embeddings(&db), 1);
    }

    #[test]
    fn test_delete_embeddings_by_chunk_ids_nonexistent_ids() {
        let db = in_memory_db();
        insert_test_embedding(&db, "chunk-1");

        let to_delete = vec!["nonexistent".to_string()];
        let deleted = db.delete_embeddings_by_chunk_ids(&to_delete).unwrap();
        assert_eq!(deleted, 0);
        assert_eq!(count_embeddings(&db), 1);
    }

    #[test]
    fn test_delete_embeddings_by_chunk_ids_batches_large_input() {
        let db = in_memory_db();
        // Insert 1200 embeddings (spans more than 2 batches of 500)
        let ids: Vec<String> = (0..1200).map(|i| format!("chunk-{}", i)).collect();
        for id in &ids {
            insert_test_embedding(&db, id);
        }
        assert_eq!(count_embeddings(&db), 1200);

        let deleted = db.delete_embeddings_by_chunk_ids(&ids).unwrap();
        assert_eq!(deleted, 1200);
        assert_eq!(count_embeddings(&db), 0);
    }
}