use std::collections::HashMap;
use std::path::Path;

use rusqlite::{params, Connection};

use astmap_core::model::*;
use astmap_core::{
    ConfigStore, DbError, DiffStore, ExportStore, QueryStore, ScanStore, SymbolLookup,
};

use super::schema;

/// Extension trait to convert rusqlite errors to DbError via `.map_storage_err()?` syntax.
trait SqliteExt<T> {
    fn map_storage_err(self) -> Result<T, DbError>;
}

impl<T> SqliteExt<T> for Result<T, rusqlite::Error> {
    fn map_storage_err(self) -> Result<T, DbError> {
        self.map_err(|e| DbError::Storage(e.to_string()))
    }
}

/// Parse a string column from a DB row into a domain enum via `FromStr`.
fn parse_enum<T: std::str::FromStr>(row: &rusqlite::Row, idx: usize) -> rusqlite::Result<T>
where
    T::Err: std::fmt::Display,
{
    let s: String = row.get(idx)?;
    s.parse::<T>().map_err(|e| {
        rusqlite::Error::ToSqlConversionFailure(Box::new(std::io::Error::new(
            std::io::ErrorKind::InvalidData,
            e.to_string(),
        )))
    })
}

/// Map a row to a Symbol struct. Used by all symbol-returning queries.
fn map_symbol_row(row: &rusqlite::Row) -> rusqlite::Result<Symbol> {
    Ok(Symbol {
        id: row.get(0)?,
        file_id: row.get(1)?,
        parent_id: row.get(2)?,
        name: row.get(3)?,
        kind: parse_enum(row, 4)?,
        signature: row.get(5)?,
        summary: row.get(6)?,
        start_line: row.get(7)?,
        end_line: row.get(8)?,
        start_byte: row.get(9)?,
        end_byte: row.get(10)?,
    })
}

/// Map a row to a SymbolResult struct. Used by all search/range queries.
fn map_symbol_result_row(row: &rusqlite::Row) -> rusqlite::Result<SymbolResult> {
    Ok(SymbolResult {
        id: row.get(0)?,
        name: row.get(1)?,
        kind: parse_enum(row, 2)?,
        file_path: row.get(3)?,
        start_line: row.get(4)?,
        end_line: row.get(5)?,
        signature: row.get(6)?,
        summary: row.get(7)?,
    })
}

pub struct SqliteRepository {
    conn: Connection,
}

impl SqliteRepository {
    pub fn open(project_dir: &Path) -> Result<Self, DbError> {
        let astmap_dir = project_dir.join(".astmap");
        if !astmap_dir.exists() {
            std::fs::create_dir_all(&astmap_dir).map_err(DbError::CreateDir)?;
        }
        let db_path = astmap_dir.join("index.db");
        let conn = Connection::open(&db_path).map_storage_err()?;
        conn.pragma_update(None, "journal_mode", "WAL")
            .map_storage_err()?;
        conn.pragma_update(None, "foreign_keys", "ON")
            .map_storage_err()?;
        schema::create_tables(&conn)?;
        schema::migrate(&conn)?;
        Ok(Self { conn })
    }

    pub fn open_existing(project_dir: &Path) -> Result<Self, DbError> {
        let db_path = project_dir.join(".astmap").join("index.db");
        if !db_path.exists() {
            return Err(DbError::NotFound);
        }
        let conn = Connection::open(&db_path).map_storage_err()?;
        conn.pragma_update(None, "journal_mode", "WAL")
            .map_storage_err()?;
        conn.pragma_update(None, "foreign_keys", "ON")
            .map_storage_err()?;
        schema::migrate(&conn)?;
        Ok(Self { conn })
    }

    #[cfg(test)]
    pub fn open_in_memory() -> Result<Self, DbError> {
        let conn = Connection::open_in_memory().map_storage_err()?;
        conn.pragma_update(None, "foreign_keys", "ON")
            .map_storage_err()?;
        schema::create_tables(&conn)?;
        schema::migrate(&conn)?;
        Ok(Self { conn })
    }
}

fn now_timestamp() -> u64 {
    std::time::SystemTime::now()
        .duration_since(std::time::UNIX_EPOCH)
        .unwrap_or_default()
        .as_secs()
}

/// Parse a TEXT column from a DB row into a u64 timestamp.
fn parse_timestamp(row: &rusqlite::Row, idx: usize) -> rusqlite::Result<u64> {
    let s: String = row.get(idx)?;
    s.parse::<u64>().map_err(|e| {
        rusqlite::Error::ToSqlConversionFailure(Box::new(std::io::Error::new(
            std::io::ErrorKind::InvalidData,
            e.to_string(),
        )))
    })
}

// ── ConfigStore ──

impl ConfigStore for SqliteRepository {
    fn config_get(&self, key: &str) -> Result<Option<String>, DbError> {
        match self.conn.query_row(
            "SELECT value FROM config WHERE key = ?1",
            params![key],
            |row| row.get::<_, String>(0),
        ) {
            Ok(v) => Ok(Some(v)),
            Err(rusqlite::Error::QueryReturnedNoRows) => Ok(None),
            Err(e) => Err(DbError::Storage(e.to_string())),
        }
    }

    fn config_set(&self, key: &str, value: &str) -> Result<(), DbError> {
        let now = now_timestamp().to_string();
        self.conn
            .execute(
                "INSERT INTO config (key, value, updated_at) VALUES (?1, ?2, ?3)
             ON CONFLICT(key) DO UPDATE SET value=excluded.value, updated_at=excluded.updated_at",
                params![key, value, now],
            )
            .map_storage_err()?;
        Ok(())
    }

    fn config_list(&self) -> Result<Vec<Config>, DbError> {
        let mut stmt = self
            .conn
            .prepare("SELECT key, value, updated_at FROM config ORDER BY key")
            .map_storage_err()?;
        let results = stmt
            .query_map([], |row| {
                Ok(Config {
                    key: row.get(0)?,
                    value: row.get(1)?,
                    updated_at: parse_timestamp(row, 2)?,
                })
            })
            .map_storage_err()?
            .filter_map(|r| r.ok())
            .collect();
        Ok(results)
    }
}

// ── SymbolLookup ──

impl SymbolLookup for SqliteRepository {
    fn get_file_id(&self, path: &str) -> Result<Option<i64>, DbError> {
        match self.conn.query_row(
            "SELECT id FROM file WHERE path = ?1",
            params![path],
            |row| row.get::<_, i64>(0),
        ) {
            Ok(id) => Ok(Some(id)),
            Err(rusqlite::Error::QueryReturnedNoRows) => Ok(None),
            Err(e) => Err(DbError::Storage(e.to_string())),
        }
    }

    fn get_symbol_by_id(&self, symbol_id: i64) -> Result<Option<Symbol>, DbError> {
        match self.conn.query_row(
            "SELECT id, file_id, parent_id, name, kind, signature, summary, start_line, end_line, start_byte, end_byte
             FROM symbol WHERE id = ?1",
            params![symbol_id],
            map_symbol_row,
        ) {
            Ok(s) => Ok(Some(s)),
            Err(rusqlite::Error::QueryReturnedNoRows) => Ok(None),
            Err(e) => Err(DbError::Storage(e.to_string())),
        }
    }

    fn search_symbols_by_name(&self, name: &str, limit: i64) -> Result<Vec<SymbolResult>, DbError> {
        let mut stmt = self
            .conn
            .prepare(
                "SELECT s.id, s.name, s.kind, f.path, s.start_line, s.end_line, s.signature, s.summary
             FROM symbol s JOIN file f ON f.id = s.file_id WHERE s.name LIKE ?1 ORDER BY s.name LIMIT ?2",
            )
            .map_storage_err()?;
        let pattern = format!("%{}%", name);
        let results = stmt
            .query_map(params![pattern, limit], map_symbol_result_row)
            .map_storage_err()?
            .filter_map(|r| r.ok())
            .collect();
        Ok(results)
    }

    fn get_symbols_for_file(&self, file_id: i64) -> Result<Vec<Symbol>, DbError> {
        let mut stmt = self
            .conn
            .prepare(
                "SELECT id, file_id, parent_id, name, kind, signature, summary,
                    start_line, end_line, start_byte, end_byte
             FROM symbol WHERE file_id = ?1 AND parent_id IS NULL ORDER BY start_line",
            )
            .map_storage_err()?;
        let results = stmt
            .query_map(params![file_id], map_symbol_row)
            .map_storage_err()?
            .collect::<Result<Vec<_>, _>>()
            .map_storage_err()?;
        Ok(results)
    }

    fn get_children(&self, symbol_id: i64) -> Result<Vec<Symbol>, DbError> {
        let mut stmt = self
            .conn
            .prepare(
                "SELECT id, file_id, parent_id, name, kind, signature, summary, start_line, end_line, start_byte, end_byte
             FROM symbol WHERE parent_id = ?1 ORDER BY start_line",
            )
            .map_storage_err()?;
        let results = stmt
            .query_map(params![symbol_id], map_symbol_row)
            .map_storage_err()?
            .filter_map(|r| r.ok())
            .collect();
        Ok(results)
    }

    fn impact_analysis(&self, symbol_id: i64, max_depth: i64) -> Result<Vec<ImpactEntry>, DbError> {
        let mut stmt = self
            .conn
            .prepare(
                "WITH RECURSIVE impact(symbol_id, depth, dep_type) AS (
                SELECT d.source_symbol_id, 1, d.dep_type FROM dependency d
                WHERE d.target_symbol_id = ?1 AND d.source_symbol_id IS NOT NULL
                UNION
                SELECT s2.id, 1, 'imports' FROM symbol s2
                JOIN dependency d ON d.source_file_id = s2.file_id
                WHERE d.target_file_id = (SELECT file_id FROM symbol WHERE id = ?1)
                  AND d.dep_type = 'imports' AND d.source_symbol_id IS NULL
                  AND s2.parent_id IS NULL
                UNION
                SELECT d.source_symbol_id, i.depth + 1, d.dep_type FROM dependency d
                JOIN impact i ON d.target_symbol_id = i.symbol_id
                WHERE d.source_symbol_id IS NOT NULL AND i.depth < ?2
                UNION
                SELECT s2.id, i.depth + 1, 'imports' FROM symbol s2
                JOIN dependency d ON d.source_file_id = s2.file_id
                JOIN symbol s3 ON s3.id = i.symbol_id AND d.target_file_id = s3.file_id
                JOIN impact i ON i.depth < ?2
                WHERE d.dep_type = 'imports' AND d.source_symbol_id IS NULL
                  AND s2.parent_id IS NULL
            )
            SELECT i.symbol_id, MIN(i.depth), s.name, s.kind, f.path, i.dep_type
            FROM impact i JOIN symbol s ON s.id = i.symbol_id JOIN file f ON f.id = s.file_id
            WHERE i.symbol_id != ?1
            GROUP BY i.symbol_id ORDER BY MIN(i.depth), s.name",
            )
            .map_storage_err()?;
        let results = stmt
            .query_map(params![symbol_id, max_depth], |row| {
                Ok(ImpactEntry {
                    symbol_id: row.get(0)?,
                    depth: row.get(1)?,
                    symbol_name: row.get(2)?,
                    symbol_kind: parse_enum(row, 3)?,
                    file_path: row.get(4)?,
                    dep_type: parse_enum(row, 5)?,
                })
            })
            .map_storage_err()?
            .filter_map(|r| r.ok())
            .collect();
        Ok(results)
    }

    fn stats(&self) -> Result<(i64, i64, i64), DbError> {
        let fc: i64 = self
            .conn
            .query_row("SELECT count(*) FROM file", [], |r| r.get(0))
            .map_storage_err()?;
        let sc: i64 = self
            .conn
            .query_row("SELECT count(*) FROM symbol", [], |r| r.get(0))
            .map_storage_err()?;
        let dc: i64 = self
            .conn
            .query_row("SELECT count(*) FROM dependency", [], |r| r.get(0))
            .map_storage_err()?;
        Ok((fc, sc, dc))
    }
}

// ── ScanStore ──

impl ScanStore for SqliteRepository {
    fn begin_scan(&self) -> Result<i64, DbError> {
        let now = now_timestamp().to_string();
        self.conn
            .execute(
                "INSERT INTO scan (started_at, status) VALUES (?1, 'running')",
                params![now],
            )
            .map_storage_err()?;
        Ok(self.conn.last_insert_rowid())
    }

    fn complete_scan(
        &self,
        scan_id: i64,
        file_count: i64,
        symbol_count: i64,
        dep_count: i64,
    ) -> Result<(), DbError> {
        let now = now_timestamp().to_string();
        self.conn
            .execute(
                "UPDATE scan SET completed_at = ?1, status = 'completed',
             file_count = ?2, symbol_count = ?3, dep_count = ?4 WHERE id = ?5",
                params![now, file_count, symbol_count, dep_count, scan_id],
            )
            .map_storage_err()?;
        Ok(())
    }

    fn upsert_file(
        &self,
        scan_id: i64,
        path: &str,
        language: &str,
        size_bytes: i64,
        content_hash: &str,
        last_modified: u64,
        parse_status: ParseStatus,
    ) -> Result<i64, DbError> {
        let parse_status_str = parse_status.to_string();
        let last_modified_str = last_modified.to_string();
        self.conn
            .execute(
                "INSERT INTO file (scan_id, path, language, size_bytes, content_hash, last_modified, parse_status)
            VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7)
            ON CONFLICT(path) DO UPDATE SET
                scan_id=excluded.scan_id, size_bytes=excluded.size_bytes,
                content_hash=excluded.content_hash, last_modified=excluded.last_modified,
                parse_status=excluded.parse_status",
                params![scan_id, path, language, size_bytes, content_hash, last_modified_str, parse_status_str],
            )
            .map_storage_err()?;
        let file_id: i64 = self
            .conn
            .query_row(
                "SELECT id FROM file WHERE path = ?1",
                params![path],
                |row| row.get(0),
            )
            .map_storage_err()?;
        Ok(file_id)
    }

    fn get_file_hash(&self, path: &str) -> Result<Option<String>, DbError> {
        match self.conn.query_row(
            "SELECT content_hash FROM file WHERE path = ?1",
            params![path],
            |row| row.get::<_, String>(0),
        ) {
            Ok(h) => Ok(Some(h)),
            Err(rusqlite::Error::QueryReturnedNoRows) => Ok(None),
            Err(e) => Err(DbError::Storage(e.to_string())),
        }
    }

    fn get_all_file_paths(&self) -> Result<HashMap<String, i64>, DbError> {
        let mut stmt = self
            .conn
            .prepare("SELECT id, path FROM file")
            .map_storage_err()?;
        let map = stmt
            .query_map([], |row| {
                Ok((row.get::<_, String>(1)?, row.get::<_, i64>(0)?))
            })
            .map_storage_err()?
            .filter_map(|r| r.ok())
            .collect();
        Ok(map)
    }

    fn delete_file(&self, file_id: i64) -> Result<(), DbError> {
        self.conn
            .execute("DELETE FROM file WHERE id = ?1", params![file_id])
            .map_storage_err()?;
        Ok(())
    }

    fn insert_symbol(
        &self,
        file_id: i64,
        parent_id: Option<i64>,
        name: &str,
        kind: SymbolKind,
        signature: Option<&str>,
        start_line: i64,
        end_line: i64,
        start_byte: i64,
        end_byte: i64,
    ) -> Result<i64, DbError> {
        let kind_str = kind.to_string();
        self.conn
            .execute(
                "INSERT INTO symbol (file_id, parent_id, name, kind, signature, start_line, end_line, start_byte, end_byte)
                 VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9)
                 ON CONFLICT(file_id, COALESCE(parent_id, 0), name, kind) DO UPDATE SET
                    signature=excluded.signature, start_line=excluded.start_line,
                    end_line=excluded.end_line, start_byte=excluded.start_byte,
                    end_byte=excluded.end_byte",
                params![file_id, parent_id, name, kind_str, signature, start_line, end_line, start_byte, end_byte],
            )
            .map_storage_err()?;
        // ON CONFLICT DO UPDATE keeps the original rowid, so query it back
        let id: i64 = self
            .conn
            .query_row(
                "SELECT id FROM symbol WHERE file_id = ?1 AND COALESCE(parent_id, 0) = COALESCE(?2, 0) AND name = ?3 AND kind = ?4",
                params![file_id, parent_id, name, kind_str],
                |row| row.get(0),
            )
            .map_storage_err()?;
        Ok(id)
    }

    fn delete_symbols_for_file(&self, file_id: i64) -> Result<(), DbError> {
        self.conn
            .execute("DELETE FROM symbol WHERE file_id = ?1", params![file_id])
            .map_storage_err()?;
        Ok(())
    }

    fn insert_dependency(
        &self,
        source_file_id: i64,
        source_symbol_id: Option<i64>,
        target_file_id: Option<i64>,
        target_symbol_id: Option<i64>,
        dep_type: DepType,
        level: DepLevel,
        raw_import: Option<&str>,
    ) -> Result<i64, DbError> {
        let dep_type_str = dep_type.to_string();
        let level_str = level.to_string();
        self.conn
            .execute(
                "INSERT INTO dependency (source_file_id, source_symbol_id, target_file_id, target_symbol_id, dep_type, level, raw_import)
             VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7)",
                params![source_file_id, source_symbol_id, target_file_id, target_symbol_id, dep_type_str, level_str, raw_import],
            )
            .map_storage_err()?;
        Ok(self.conn.last_insert_rowid())
    }

    fn delete_deps_for_file(&self, file_id: i64) -> Result<(), DbError> {
        self.conn
            .execute(
                "DELETE FROM dependency WHERE source_file_id = ?1
                 OR target_symbol_id IN (SELECT id FROM symbol WHERE file_id = ?1)",
                params![file_id],
            )
            .map_storage_err()?;
        Ok(())
    }
}

// ── QueryStore ──

impl QueryStore for SqliteRepository {
    fn search_symbols(&self, query: &str, limit: i64) -> Result<Vec<SymbolResult>, DbError> {
        let mut stmt = self
            .conn
            .prepare(
                "SELECT s.id, s.name, s.kind, f.path, s.start_line, s.end_line, s.signature, s.summary
             FROM symbol_fts fts JOIN symbol s ON s.id = fts.rowid JOIN file f ON f.id = s.file_id
             WHERE symbol_fts MATCH ?1 ORDER BY rank LIMIT ?2",
            )
            .map_storage_err()?;
        let results = stmt
            .query_map(params![query, limit], map_symbol_result_row)
            .map_storage_err()?
            .filter_map(|r| r.ok())
            .collect();
        Ok(results)
    }

    fn find_path(
        &self,
        from_symbol_id: i64,
        to_symbol_id: i64,
        max_depth: i64,
    ) -> Result<Vec<PathStep>, DbError> {
        let mut visited = std::collections::HashSet::new();
        let mut queue = std::collections::VecDeque::new();
        let mut parent_map: HashMap<i64, (i64, DepType)> = HashMap::new();

        visited.insert(from_symbol_id);
        queue.push_back((from_symbol_id, 0i64));

        const NEIGHBOR_QUERIES: &[&str] = &[
            // symbol-level forward
            "SELECT d.target_symbol_id, d.dep_type FROM dependency d
                 WHERE d.source_symbol_id = ?1 AND d.target_symbol_id IS NOT NULL",
            // symbol-level reverse
            "SELECT d.source_symbol_id, d.dep_type FROM dependency d
                 WHERE d.target_symbol_id = ?1 AND d.source_symbol_id IS NOT NULL",
            // file-level forward
            "SELECT s2.id, 'imports' FROM symbol s2
                 JOIN dependency d ON d.target_file_id = s2.file_id
                 JOIN symbol s1 ON s1.id = ?1 AND d.source_file_id = s1.file_id
                 WHERE d.dep_type = 'imports' AND d.source_symbol_id IS NULL
                   AND s2.parent_id IS NULL",
            // file-level reverse
            "SELECT s2.id, 'imports' FROM symbol s2
                 JOIN dependency d ON d.source_file_id = s2.file_id
                 JOIN symbol s1 ON s1.id = ?1 AND d.target_file_id = s1.file_id
                 WHERE d.dep_type = 'imports' AND d.source_symbol_id IS NULL
                   AND s2.parent_id IS NULL",
        ];

        while let Some((current_id, depth)) = queue.pop_front() {
            if depth >= max_depth {
                continue;
            }

            for sql in NEIGHBOR_QUERIES {
                for (nid, dt) in self.query_neighbors(sql, current_id)? {
                    if visited.insert(nid) {
                        parent_map.insert(nid, (current_id, dt));
                        if nid == to_symbol_id {
                            return self.reconstruct_path(
                                from_symbol_id,
                                to_symbol_id,
                                &parent_map,
                            );
                        }
                        queue.push_back((nid, depth + 1));
                    }
                }
            }
        }
        Ok(vec![])
    }
}

// ── DiffStore ──

impl DiffStore for SqliteRepository {
    fn symbols_in_line_range(
        &self,
        file_path: &str,
        start_line: i64,
        end_line: i64,
    ) -> Result<Vec<SymbolResult>, DbError> {
        let mut stmt = self
            .conn
            .prepare(
                "SELECT s.id, s.name, s.kind, f.path, s.start_line, s.end_line, s.signature, s.summary
             FROM symbol s JOIN file f ON f.id = s.file_id
             WHERE f.path = ?1 AND s.start_line <= ?3 AND s.end_line >= ?2
             ORDER BY s.start_line",
            )
            .map_storage_err()?;
        let results = stmt
            .query_map(
                params![file_path, start_line, end_line],
                map_symbol_result_row,
            )
            .map_storage_err()?
            .filter_map(|r| r.ok())
            .collect();
        Ok(results)
    }

    fn all_symbols_for_file(&self, file_path: &str) -> Result<Vec<SymbolWithParent>, DbError> {
        let mut stmt = self
            .conn
            .prepare(
                "SELECT s.id, s.name, s.kind, f.path, s.start_line, s.end_line,
                    s.signature, p.name
             FROM symbol s
             JOIN file f ON f.id = s.file_id
             LEFT JOIN symbol p ON p.id = s.parent_id
             WHERE f.path = ?1
             ORDER BY s.start_line",
            )
            .map_storage_err()?;
        let results = stmt
            .query_map(params![file_path], |row| {
                Ok(SymbolWithParent {
                    id: row.get(0)?,
                    name: row.get(1)?,
                    kind: parse_enum(row, 2)?,
                    file_path: row.get(3)?,
                    start_line: row.get(4)?,
                    end_line: row.get(5)?,
                    signature: row.get(6)?,
                    parent_name: row.get(7)?,
                })
            })
            .map_storage_err()?
            .collect::<Result<Vec<_>, _>>()
            .map_storage_err()?;
        Ok(results)
    }
}

// ── ExportStore ──

impl ExportStore for SqliteRepository {
    fn list_files(&self, pattern: Option<&str>) -> Result<Vec<File>, DbError> {
        let map_row = |row: &rusqlite::Row| -> rusqlite::Result<File> {
            Ok(File {
                id: row.get(0)?,
                scan_id: row.get(1)?,
                path: row.get(2)?,
                language: row.get(3)?,
                size_bytes: row.get(4)?,
                content_hash: row.get(5)?,
                last_modified: parse_timestamp(row, 6)?,
                parse_status: parse_enum(row, 7)?,
            })
        };

        let mut result = Vec::new();
        if let Some(pat) = pattern {
            let like = format!("%{}%", pat);
            let mut stmt = self
                .conn
                .prepare(
                    "SELECT id, scan_id, path, language, size_bytes, content_hash, last_modified, parse_status
                 FROM file WHERE path LIKE ?1 ORDER BY path",
                )
                .map_storage_err()?;
            let rows = stmt.query_map(params![like], map_row).map_storage_err()?;
            for r in rows {
                result.push(r.map_storage_err()?);
            }
        } else {
            let mut stmt = self
                .conn
                .prepare(
                    "SELECT id, scan_id, path, language, size_bytes, content_hash, last_modified, parse_status
                 FROM file ORDER BY path",
                )
                .map_storage_err()?;
            let rows = stmt.query_map([], map_row).map_storage_err()?;
            for r in rows {
                result.push(r.map_storage_err()?);
            }
        }
        Ok(result)
    }

    fn get_deps_for_file(&self, file_id: i64) -> Result<Vec<Dependency>, DbError> {
        let mut stmt = self
            .conn
            .prepare(
                "SELECT id, source_file_id, source_symbol_id, target_file_id, target_symbol_id,
                    dep_type, level, raw_import
             FROM dependency WHERE source_file_id = ?1 ORDER BY dep_type",
            )
            .map_storage_err()?;
        let mut results = Vec::new();
        let rows = stmt
            .query_map(params![file_id], |row| {
                Ok(Dependency {
                    id: row.get(0)?,
                    source_file_id: row.get(1)?,
                    source_symbol_id: row.get(2)?,
                    target_file_id: row.get(3)?,
                    target_symbol_id: row.get(4)?,
                    dep_type: parse_enum(row, 5)?,
                    level: parse_enum(row, 6)?,
                    raw_import: row.get(7)?,
                })
            })
            .map_storage_err()?;
        for r in rows {
            results.push(r.map_storage_err()?);
        }
        Ok(results)
    }
}

impl SqliteRepository {
    fn query_neighbors(&self, sql: &str, id: i64) -> Result<Vec<(i64, DepType)>, DbError> {
        let mut stmt = self.conn.prepare(sql).map_storage_err()?;
        let rows = stmt
            .query_map(params![id], |row| {
                Ok((row.get::<_, i64>(0)?, parse_enum::<DepType>(row, 1)?))
            })
            .map_storage_err()?;
        let mut result = Vec::new();
        for r in rows {
            result.push(r.map_storage_err()?);
        }
        Ok(result)
    }

    fn reconstruct_path(
        &self,
        from_id: i64,
        to_id: i64,
        parent_map: &HashMap<i64, (i64, DepType)>,
    ) -> Result<Vec<PathStep>, DbError> {
        let mut path = Vec::new();
        let mut current = to_id;
        while current != from_id {
            if let Some(s) = self.get_symbol_by_id(current)? {
                let fp: String = self
                    .conn
                    .query_row(
                        "SELECT path FROM file WHERE id = ?1",
                        params![s.file_id],
                        |r| r.get(0),
                    )
                    .map_storage_err()?;
                let dt = parent_map
                    .get(&current)
                    .map(|(_, d)| *d)
                    .unwrap_or(DepType::Imports);
                path.push(PathStep {
                    symbol_id: s.id,
                    symbol_name: s.name,
                    file_path: fp,
                    dep_type: dt,
                });
            }
            match parent_map.get(&current) {
                Some((pid, _)) => current = *pid,
                None => break,
            }
        }
        if let Some(s) = self.get_symbol_by_id(from_id)? {
            let fp: String = self
                .conn
                .query_row(
                    "SELECT path FROM file WHERE id = ?1",
                    params![s.file_id],
                    |r| r.get(0),
                )
                .map_storage_err()?;
            path.push(PathStep {
                symbol_id: s.id,
                symbol_name: s.name,
                file_path: fp,
                dep_type: DepType::Imports,
            });
        }
        path.reverse();
        Ok(path)
    }
}

#[cfg(test)]
mod tests {
    use super::*;
    use astmap_core::{ConfigStore, DiffStore, ExportStore, QueryStore, ScanStore, SymbolLookup};

    fn setup() -> SqliteRepository {
        SqliteRepository::open_in_memory().unwrap()
    }

    /// Insert a file and return its id.
    fn insert_file(repo: &SqliteRepository, scan_id: i64, path: &str) -> i64 {
        repo.upsert_file(scan_id, path, "rust", 100, "hash1", 1000, ParseStatus::Ok)
            .unwrap()
    }

    /// Insert a top-level symbol and return its id.
    fn insert_sym(
        repo: &SqliteRepository,
        file_id: i64,
        name: &str,
        kind: SymbolKind,
        start_line: i64,
        end_line: i64,
    ) -> i64 {
        repo.insert_symbol(
            file_id,
            None,
            name,
            kind,
            Some(&format!("fn {}()", name)),
            start_line,
            end_line,
            0,
            100,
        )
        .unwrap()
    }

    // ── ConfigStore ──

    #[test]
    fn config_set_and_get() {
        let repo = setup();
        assert_eq!(repo.config_get("foo").unwrap(), None);
        repo.config_set("foo", "bar").unwrap();
        assert_eq!(repo.config_get("foo").unwrap(), Some("bar".into()));
    }

    #[test]
    fn config_set_overwrites() {
        let repo = setup();
        repo.config_set("k", "v1").unwrap();
        repo.config_set("k", "v2").unwrap();
        assert_eq!(repo.config_get("k").unwrap(), Some("v2".into()));
    }

    #[test]
    fn config_list_ordered() {
        let repo = setup();
        repo.config_set("beta", "2").unwrap();
        repo.config_set("alpha", "1").unwrap();
        let list = repo.config_list().unwrap();
        assert_eq!(list.len(), 2);
        assert_eq!(list[0].key, "alpha");
        assert_eq!(list[1].key, "beta");
    }

    // ── ScanStore basic ──

    #[test]
    fn begin_and_complete_scan() {
        let repo = setup();
        let scan_id = repo.begin_scan().unwrap();
        assert!(scan_id > 0);
        repo.complete_scan(scan_id, 10, 50, 20).unwrap();
    }

    #[test]
    fn upsert_file_returns_same_id_on_update() {
        let repo = setup();
        let scan_id = repo.begin_scan().unwrap();
        let id1 = repo
            .upsert_file(
                scan_id,
                "src/main.rs",
                "rust",
                100,
                "aaa",
                1000,
                ParseStatus::Ok,
            )
            .unwrap();
        let id2 = repo
            .upsert_file(
                scan_id,
                "src/main.rs",
                "rust",
                200,
                "bbb",
                2000,
                ParseStatus::Ok,
            )
            .unwrap();
        assert_eq!(id1, id2);
    }

    #[test]
    fn get_file_hash() {
        let repo = setup();
        let scan_id = repo.begin_scan().unwrap();
        assert_eq!(repo.get_file_hash("src/lib.rs").unwrap(), None);
        repo.upsert_file(
            scan_id,
            "src/lib.rs",
            "rust",
            50,
            "deadbeef",
            999,
            ParseStatus::Ok,
        )
        .unwrap();
        assert_eq!(
            repo.get_file_hash("src/lib.rs").unwrap(),
            Some("deadbeef".into())
        );
    }

    #[test]
    fn get_all_file_paths() {
        let repo = setup();
        let scan_id = repo.begin_scan().unwrap();
        let id_a = insert_file(&repo, scan_id, "a.rs");
        let id_b = insert_file(&repo, scan_id, "b.rs");
        let map = repo.get_all_file_paths().unwrap();
        assert_eq!(map.len(), 2);
        assert_eq!(map["a.rs"], id_a);
        assert_eq!(map["b.rs"], id_b);
    }

    #[test]
    fn delete_file_cascades_symbols() {
        let repo = setup();
        let scan_id = repo.begin_scan().unwrap();
        let fid = insert_file(&repo, scan_id, "src/main.rs");
        insert_sym(&repo, fid, "main", SymbolKind::Function, 1, 10);
        repo.delete_file(fid).unwrap();
        assert_eq!(repo.get_file_id("src/main.rs").unwrap(), None);
        let (_, sc, _) = repo.stats().unwrap();
        assert_eq!(sc, 0);
    }

    // ── Symbol insert / upsert ──

    #[test]
    fn insert_symbol_upsert_preserves_id() {
        let repo = setup();
        let scan_id = repo.begin_scan().unwrap();
        let fid = insert_file(&repo, scan_id, "lib.rs");
        let id1 = repo
            .insert_symbol(
                fid,
                None,
                "Foo",
                SymbolKind::Struct,
                Some("struct Foo"),
                1,
                10,
                0,
                50,
            )
            .unwrap();
        let id2 = repo
            .insert_symbol(
                fid,
                None,
                "Foo",
                SymbolKind::Struct,
                Some("struct Foo { x: i32 }"),
                1,
                15,
                0,
                80,
            )
            .unwrap();
        assert_eq!(id1, id2);
        let sym = repo.get_symbol_by_id(id1).unwrap().unwrap();
        assert_eq!(sym.signature.as_deref(), Some("struct Foo { x: i32 }"));
        assert_eq!(sym.end_line, 15);
    }

    #[test]
    fn insert_child_symbol() {
        let repo = setup();
        let scan_id = repo.begin_scan().unwrap();
        let fid = insert_file(&repo, scan_id, "lib.rs");
        let parent_id = insert_sym(&repo, fid, "MyStruct", SymbolKind::Struct, 1, 20);
        let child_id = repo
            .insert_symbol(
                fid,
                Some(parent_id),
                "new",
                SymbolKind::Method,
                Some("fn new()"),
                5,
                10,
                30,
                60,
            )
            .unwrap();
        let children = repo.get_children(parent_id).unwrap();
        assert_eq!(children.len(), 1);
        assert_eq!(children[0].id, child_id);
        assert_eq!(children[0].name, "new");
    }

    #[test]
    fn delete_symbols_for_file() {
        let repo = setup();
        let scan_id = repo.begin_scan().unwrap();
        let fid = insert_file(&repo, scan_id, "lib.rs");
        insert_sym(&repo, fid, "a", SymbolKind::Function, 1, 5);
        insert_sym(&repo, fid, "b", SymbolKind::Function, 6, 10);
        assert_eq!(repo.stats().unwrap().1, 2);
        repo.delete_symbols_for_file(fid).unwrap();
        assert_eq!(repo.stats().unwrap().1, 0);
    }

    // ── SymbolLookup ──

    #[test]
    fn get_file_id() {
        let repo = setup();
        let scan_id = repo.begin_scan().unwrap();
        assert_eq!(repo.get_file_id("nope.rs").unwrap(), None);
        let fid = insert_file(&repo, scan_id, "found.rs");
        assert_eq!(repo.get_file_id("found.rs").unwrap(), Some(fid));
    }

    #[test]
    fn get_symbol_by_id_not_found() {
        let repo = setup();
        assert!(repo.get_symbol_by_id(999).unwrap().is_none());
    }

    #[test]
    fn search_symbols_by_name_partial() {
        let repo = setup();
        let scan_id = repo.begin_scan().unwrap();
        let fid = insert_file(&repo, scan_id, "lib.rs");
        insert_sym(&repo, fid, "parse_config", SymbolKind::Function, 1, 10);
        insert_sym(&repo, fid, "parse_args", SymbolKind::Function, 11, 20);
        insert_sym(&repo, fid, "run", SymbolKind::Function, 21, 30);
        let results = repo.search_symbols_by_name("parse", 10).unwrap();
        assert_eq!(results.len(), 2);
    }

    #[test]
    fn get_symbols_for_file_returns_top_level_only() {
        let repo = setup();
        let scan_id = repo.begin_scan().unwrap();
        let fid = insert_file(&repo, scan_id, "lib.rs");
        let parent_id = insert_sym(&repo, fid, "App", SymbolKind::Struct, 1, 30);
        repo.insert_symbol(
            fid,
            Some(parent_id),
            "run",
            SymbolKind::Method,
            None,
            5,
            15,
            20,
            60,
        )
        .unwrap();
        let top = repo.get_symbols_for_file(fid).unwrap();
        assert_eq!(top.len(), 1);
        assert_eq!(top[0].name, "App");
    }

    #[test]
    fn stats() {
        let repo = setup();
        let scan_id = repo.begin_scan().unwrap();
        let f1 = insert_file(&repo, scan_id, "a.rs");
        let f2 = insert_file(&repo, scan_id, "b.rs");
        let s1 = insert_sym(&repo, f1, "foo", SymbolKind::Function, 1, 10);
        insert_sym(&repo, f2, "bar", SymbolKind::Function, 1, 10);
        repo.insert_dependency(
            f1,
            Some(s1),
            Some(f2),
            None,
            DepType::Imports,
            DepLevel::File,
            Some("b"),
        )
        .unwrap();
        let (fc, sc, dc) = repo.stats().unwrap();
        assert_eq!(fc, 2);
        assert_eq!(sc, 2);
        assert_eq!(dc, 1);
    }

    // ── Dependencies ──

    #[test]
    fn insert_and_delete_deps() {
        let repo = setup();
        let scan_id = repo.begin_scan().unwrap();
        let f1 = insert_file(&repo, scan_id, "a.rs");
        let f2 = insert_file(&repo, scan_id, "b.rs");
        let s1 = insert_sym(&repo, f1, "foo", SymbolKind::Function, 1, 10);
        let s2 = insert_sym(&repo, f2, "bar", SymbolKind::Function, 1, 10);
        repo.insert_dependency(
            f1,
            Some(s1),
            Some(f2),
            Some(s2),
            DepType::Calls,
            DepLevel::Symbol,
            None,
        )
        .unwrap();
        assert_eq!(repo.stats().unwrap().2, 1);
        repo.delete_deps_for_file(f1).unwrap();
        assert_eq!(repo.stats().unwrap().2, 0);
    }

    // ── QueryStore: FTS search ──

    #[test]
    fn search_symbols_fts() {
        let repo = setup();
        let scan_id = repo.begin_scan().unwrap();
        let fid = insert_file(&repo, scan_id, "lib.rs");
        insert_sym(&repo, fid, "calculate_total", SymbolKind::Function, 1, 10);
        insert_sym(&repo, fid, "validate_input", SymbolKind::Function, 11, 20);
        let results = repo.search_symbols("calculate", 10).unwrap();
        assert_eq!(results.len(), 1);
        assert_eq!(results[0].name, "calculate_total");
    }

    // ── QueryStore: find_path (BFS) ──

    #[test]
    fn find_path_direct_symbol_dep() {
        let repo = setup();
        let scan_id = repo.begin_scan().unwrap();
        let fid = insert_file(&repo, scan_id, "lib.rs");
        let a = insert_sym(&repo, fid, "A", SymbolKind::Function, 1, 5);
        let b = insert_sym(&repo, fid, "B", SymbolKind::Function, 6, 10);
        repo.insert_dependency(
            fid,
            Some(a),
            Some(fid),
            Some(b),
            DepType::Calls,
            DepLevel::Symbol,
            None,
        )
        .unwrap();
        let path = repo.find_path(a, b, 5).unwrap();
        assert_eq!(path.len(), 2);
        assert_eq!(path[0].symbol_name, "A");
        assert_eq!(path[1].symbol_name, "B");
    }

    #[test]
    fn find_path_transitive() {
        let repo = setup();
        let scan_id = repo.begin_scan().unwrap();
        let fid = insert_file(&repo, scan_id, "lib.rs");
        let a = insert_sym(&repo, fid, "A", SymbolKind::Function, 1, 5);
        let b = insert_sym(&repo, fid, "B", SymbolKind::Function, 6, 10);
        let c = insert_sym(&repo, fid, "C", SymbolKind::Function, 11, 15);
        repo.insert_dependency(
            fid,
            Some(a),
            Some(fid),
            Some(b),
            DepType::Calls,
            DepLevel::Symbol,
            None,
        )
        .unwrap();
        repo.insert_dependency(
            fid,
            Some(b),
            Some(fid),
            Some(c),
            DepType::Calls,
            DepLevel::Symbol,
            None,
        )
        .unwrap();
        let path = repo.find_path(a, c, 5).unwrap();
        assert_eq!(path.len(), 3);
        assert_eq!(path[0].symbol_name, "A");
        assert_eq!(path[1].symbol_name, "B");
        assert_eq!(path[2].symbol_name, "C");
    }

    #[test]
    fn find_path_no_connection() {
        let repo = setup();
        let scan_id = repo.begin_scan().unwrap();
        let fid = insert_file(&repo, scan_id, "lib.rs");
        let a = insert_sym(&repo, fid, "A", SymbolKind::Function, 1, 5);
        let b = insert_sym(&repo, fid, "B", SymbolKind::Function, 6, 10);
        let path = repo.find_path(a, b, 5).unwrap();
        assert!(path.is_empty());
    }

    #[test]
    fn find_path_via_file_level_import() {
        let repo = setup();
        let scan_id = repo.begin_scan().unwrap();
        let f1 = insert_file(&repo, scan_id, "a.rs");
        let f2 = insert_file(&repo, scan_id, "b.rs");
        let s1 = insert_sym(&repo, f1, "Foo", SymbolKind::Struct, 1, 10);
        let s2 = insert_sym(&repo, f2, "Bar", SymbolKind::Struct, 1, 10);
        repo.insert_dependency(
            f1,
            None,
            Some(f2),
            None,
            DepType::Imports,
            DepLevel::File,
            Some("b"),
        )
        .unwrap();
        let path = repo.find_path(s1, s2, 5).unwrap();
        assert_eq!(path.len(), 2);
        assert_eq!(path[0].symbol_name, "Foo");
        assert_eq!(path[1].symbol_name, "Bar");
    }

    // ── SymbolLookup: impact_analysis ──

    #[test]
    fn impact_analysis_direct() {
        let repo = setup();
        let scan_id = repo.begin_scan().unwrap();
        let fid = insert_file(&repo, scan_id, "lib.rs");
        let target = insert_sym(&repo, fid, "core_fn", SymbolKind::Function, 1, 10);
        let caller = insert_sym(&repo, fid, "caller_fn", SymbolKind::Function, 11, 20);
        repo.insert_dependency(
            fid,
            Some(caller),
            Some(fid),
            Some(target),
            DepType::Calls,
            DepLevel::Symbol,
            None,
        )
        .unwrap();
        let impact = repo.impact_analysis(target, 3).unwrap();
        assert_eq!(impact.len(), 1);
        assert_eq!(impact[0].symbol_name, "caller_fn");
        assert_eq!(impact[0].depth, 1);
    }

    #[test]
    fn impact_analysis_transitive() {
        let repo = setup();
        let scan_id = repo.begin_scan().unwrap();
        let fid = insert_file(&repo, scan_id, "lib.rs");
        let a = insert_sym(&repo, fid, "A", SymbolKind::Function, 1, 5);
        let b = insert_sym(&repo, fid, "B", SymbolKind::Function, 6, 10);
        let c = insert_sym(&repo, fid, "C", SymbolKind::Function, 11, 15);
        // C calls B, B calls A => impact of A = {B(depth=1), C(depth=2)}
        repo.insert_dependency(
            fid,
            Some(b),
            Some(fid),
            Some(a),
            DepType::Calls,
            DepLevel::Symbol,
            None,
        )
        .unwrap();
        repo.insert_dependency(
            fid,
            Some(c),
            Some(fid),
            Some(b),
            DepType::Calls,
            DepLevel::Symbol,
            None,
        )
        .unwrap();
        let impact = repo.impact_analysis(a, 3).unwrap();
        assert_eq!(impact.len(), 2);
        assert_eq!(impact[0].symbol_name, "B");
        assert_eq!(impact[0].depth, 1);
        assert_eq!(impact[1].symbol_name, "C");
        assert_eq!(impact[1].depth, 2);
    }

    #[test]
    fn impact_analysis_respects_max_depth() {
        let repo = setup();
        let scan_id = repo.begin_scan().unwrap();
        let fid = insert_file(&repo, scan_id, "lib.rs");
        let a = insert_sym(&repo, fid, "A", SymbolKind::Function, 1, 5);
        let b = insert_sym(&repo, fid, "B", SymbolKind::Function, 6, 10);
        let c = insert_sym(&repo, fid, "C", SymbolKind::Function, 11, 15);
        repo.insert_dependency(
            fid,
            Some(b),
            Some(fid),
            Some(a),
            DepType::Calls,
            DepLevel::Symbol,
            None,
        )
        .unwrap();
        repo.insert_dependency(
            fid,
            Some(c),
            Some(fid),
            Some(b),
            DepType::Calls,
            DepLevel::Symbol,
            None,
        )
        .unwrap();
        let impact = repo.impact_analysis(a, 1).unwrap();
        assert_eq!(impact.len(), 1);
        assert_eq!(impact[0].symbol_name, "B");
    }

    #[test]
    fn impact_analysis_file_level_import() {
        let repo = setup();
        let scan_id = repo.begin_scan().unwrap();
        let f1 = insert_file(&repo, scan_id, "core.rs");
        let f2 = insert_file(&repo, scan_id, "consumer.rs");
        let target = insert_sym(&repo, f1, "CoreStruct", SymbolKind::Struct, 1, 20);
        insert_sym(&repo, f2, "Consumer", SymbolKind::Struct, 1, 20);
        repo.insert_dependency(
            f2,
            None,
            Some(f1),
            None,
            DepType::Imports,
            DepLevel::File,
            Some("core"),
        )
        .unwrap();
        let impact = repo.impact_analysis(target, 3).unwrap();
        assert_eq!(impact.len(), 1);
        assert_eq!(impact[0].symbol_name, "Consumer");
    }

    // ── DiffStore ──

    #[test]
    fn symbols_in_line_range_overlap() {
        let repo = setup();
        let scan_id = repo.begin_scan().unwrap();
        let fid = insert_file(&repo, scan_id, "lib.rs");
        insert_sym(&repo, fid, "foo", SymbolKind::Function, 1, 10);
        insert_sym(&repo, fid, "bar", SymbolKind::Function, 8, 20);
        insert_sym(&repo, fid, "baz", SymbolKind::Function, 25, 30);
        let results = repo.symbols_in_line_range("lib.rs", 5, 15).unwrap();
        assert_eq!(results.len(), 2);
        let names: Vec<&str> = results.iter().map(|r| r.name.as_str()).collect();
        assert!(names.contains(&"foo"));
        assert!(names.contains(&"bar"));
    }

    #[test]
    fn symbols_in_line_range_exact_boundary() {
        let repo = setup();
        let scan_id = repo.begin_scan().unwrap();
        let fid = insert_file(&repo, scan_id, "lib.rs");
        insert_sym(&repo, fid, "edge", SymbolKind::Function, 10, 20);
        // Query range ends exactly at symbol start
        let results = repo.symbols_in_line_range("lib.rs", 5, 10).unwrap();
        assert_eq!(results.len(), 1);
        // Query range starts exactly at symbol end
        let results = repo.symbols_in_line_range("lib.rs", 20, 25).unwrap();
        assert_eq!(results.len(), 1);
        // Query range is just outside
        let results = repo.symbols_in_line_range("lib.rs", 21, 25).unwrap();
        assert_eq!(results.len(), 0);
    }

    #[test]
    fn all_symbols_for_file_with_parent() {
        let repo = setup();
        let scan_id = repo.begin_scan().unwrap();
        let fid = insert_file(&repo, scan_id, "lib.rs");
        let parent = insert_sym(&repo, fid, "MyStruct", SymbolKind::Struct, 1, 30);
        repo.insert_symbol(
            fid,
            Some(parent),
            "method_a",
            SymbolKind::Method,
            None,
            5,
            15,
            20,
            60,
        )
        .unwrap();
        let all = repo.all_symbols_for_file("lib.rs").unwrap();
        assert_eq!(all.len(), 2);
        assert_eq!(all[0].name, "MyStruct");
        assert_eq!(all[0].parent_name, None);
        assert_eq!(all[1].name, "method_a");
        assert_eq!(all[1].parent_name, Some("MyStruct".into()));
    }

    // ── ExportStore ──

    #[test]
    fn list_files_all() {
        let repo = setup();
        let scan_id = repo.begin_scan().unwrap();
        insert_file(&repo, scan_id, "src/a.rs");
        insert_file(&repo, scan_id, "src/b.rs");
        let files = repo.list_files(None).unwrap();
        assert_eq!(files.len(), 2);
    }

    #[test]
    fn list_files_with_pattern() {
        let repo = setup();
        let scan_id = repo.begin_scan().unwrap();
        insert_file(&repo, scan_id, "src/main.rs");
        insert_file(&repo, scan_id, "tests/test_main.rs");
        let files = repo.list_files(Some("tests")).unwrap();
        assert_eq!(files.len(), 1);
        assert_eq!(files[0].path, "tests/test_main.rs");
    }

    #[test]
    fn get_deps_for_file() {
        let repo = setup();
        let scan_id = repo.begin_scan().unwrap();
        let f1 = insert_file(&repo, scan_id, "a.rs");
        let f2 = insert_file(&repo, scan_id, "b.rs");
        repo.insert_dependency(
            f1,
            None,
            Some(f2),
            None,
            DepType::Imports,
            DepLevel::File,
            Some("b"),
        )
        .unwrap();
        repo.insert_dependency(
            f2,
            None,
            Some(f1),
            None,
            DepType::Imports,
            DepLevel::File,
            Some("a"),
        )
        .unwrap();
        let deps = repo.get_deps_for_file(f1).unwrap();
        assert_eq!(deps.len(), 1);
        assert_eq!(deps[0].source_file_id, f1);
    }
}
