scryer-db 0.3.0

use std::fs;
use tempfile::tempdir;

use scryer_db::{
    ArchitecturalDecision, CodeGraphEdge, Project, ProjectRegistry, Scope, ScryerDb, SourceFile,
    Symbol, SymbolReference, ToolInvocationMetric,
};

#[tokio::test]
async fn test_in_memory_db_and_migration() -> anyhow::Result<()> {
    let scryer_db = ScryerDb::new_in_memory().await?;
    let mut db = scryer_db.lock().await;

    // Verify Project creation and retrieval in in-memory database
    let project = Project::create()
        .slug("test-project".to_string())
        .root_path("/tmp/test-project".to_string())
        .default_branch("main".to_string())
        .is_active(true)
        .created_at("2026-10-06T00:00:00Z".to_string())
        .updated_at("2026-10-06T00:00:00Z".to_string())
        .exec(&mut *db)
        .await?;

    assert_eq!(project.slug, "test-project");

    let retrieved = Project::filter_by_slug("test-project")
        .first()
        .exec(&mut *db)
        .await?;

    assert!(retrieved.is_some());
    assert_eq!(retrieved.unwrap().id, project.id);

    Ok(())
}

#[tokio::test]
async fn test_file_based_turso_persistence() -> anyhow::Result<()> {
    let temp = tempdir()?;
    let db_path = temp.path().join("nested").join("storage.db");

    // 1. First connection creates directory and file, inserts project
    {
        let db1 = ScryerDb::connect_file(&db_path).await?;
        let mut conn = db1.lock().await;

        let project = Project::create()
            .slug("persisted-project".to_string())
            .root_path("/home/carl/persisted".to_string())
            .default_branch("main".to_string())
            .is_active(true)
            .created_at("2026-10-06T00:00:00Z".to_string())
            .updated_at("2026-10-06T00:00:00Z".to_string())
            .exec(&mut *conn)
            .await?;

        assert_eq!(project.slug, "persisted-project");
    }

    // Verify file exists on disk
    assert!(db_path.exists());

    // 2. Second connection re-opens file database and reads persisted data
    {
        let db2 = ScryerDb::connect_file(&db_path).await?;
        let mut conn = db2.lock().await;

        let retrieved = Project::filter_by_slug("persisted-project")
            .first()
            .exec(&mut *conn)
            .await?;

        assert!(retrieved.is_some());
        let p = retrieved.unwrap();
        assert_eq!(p.slug, "persisted-project");
        assert_eq!(p.root_path, "/home/carl/persisted");
    }

    Ok(())
}

#[tokio::test]
async fn test_project_registry_and_longest_prefix_resolution() -> anyhow::Result<()> {
    let scryer_db = ScryerDb::new_in_memory().await?;
    let registry = ProjectRegistry::new(scryer_db.inner()).await?;

    let temp = tempdir()?;
    let root_dir = temp.path().join("monorepo");
    let sub_dir = root_dir.join("packages").join("core");

    fs::create_dir_all(&sub_dir)?;

    let proj_root = registry
        .register_or_update(&root_dir, Some("monorepo-root"))
        .await?;
    let proj_sub = registry
        .register_or_update(&sub_dir, Some("monorepo-core"))
        .await?;

    assert_eq!(proj_root.slug, "monorepo-root");
    assert_eq!(proj_sub.slug, "monorepo-core");

    // Slug lookup
    let by_slug = registry.get_by_slug("monorepo-core").await;
    assert!(by_slug.is_some());
    assert_eq!(by_slug.unwrap().id, proj_sub.id);

    // Test longest prefix resolution
    // 1. Path inside sub_dir should resolve to proj_sub, not proj_root
    let sub_file = sub_dir.join("src").join("index.ts");
    let resolved_sub = registry.resolve_path(&sub_file).await;
    assert!(resolved_sub.is_some());
    let (match_proj, rel_path) = resolved_sub.unwrap();
    assert_eq!(match_proj.id, proj_sub.id);
    assert_eq!(rel_path, std::path::PathBuf::from("src/index.ts"));

    // 2. Path in root_dir outside sub_dir should resolve to proj_root
    let root_file = root_dir.join("README.md");
    let resolved_root = registry.resolve_path(&root_file).await;
    assert!(resolved_root.is_some());
    let (match_proj_root, rel_path_root) = resolved_root.unwrap();
    assert_eq!(match_proj_root.id, proj_root.id);
    assert_eq!(rel_path_root, std::path::PathBuf::from("README.md"));

    // 3. Path completely outside should return None
    let outside_file = temp.path().join("unrelated").join("foo.txt");
    assert!(registry.resolve_path(&outside_file).await.is_none());

    Ok(())
}

#[tokio::test]
async fn test_all_models_insertion_and_composite_index_queries() -> anyhow::Result<()> {
    let scryer_db = ScryerDb::new_in_memory().await?;
    let mut db = scryer_db.lock().await;

    // 1. Create Project
    let project = Project::create()
        .slug("scryer-test".to_string())
        .root_path("/home/carl/scryer".to_string())
        .default_branch("main".to_string())
        .is_active(true)
        .created_at("2026-10-06T00:00:00Z".to_string())
        .updated_at("2026-10-06T00:00:00Z".to_string())
        .exec(&mut *db)
        .await?;

    // 2. Create SourceFile
    let source_file = SourceFile::create()
        .project_id(project.id)
        .path("src/lib.rs".to_string())
        .content_hash("blake3_hash_12345".to_string())
        .language("rust".to_string())
        .line_count(150)
        .byte_size(4200)
        .exec(&mut *db)
        .await?;

    // 3. Create Scope
    let scope = Scope::create()
        .project_id(project.id)
        .file_id(source_file.id)
        .scope_kind("function".to_string())
        .start_byte(100)
        .end_byte(500)
        .start_line(10)
        .end_line(35)
        .exec(&mut *db)
        .await?;

    // 4. Create Symbol
    let symbol = Symbol::create()
        .project_id(project.id)
        .file_id(source_file.id)
        .scope_id(Some(scope.id))
        .name("process_data".to_string())
        .qualified_name("crate::lib::process_data".to_string())
        .kind("fn".to_string())
        .visibility("public".to_string())
        .signature("pub fn process_data(input: &str) -> Result<String>".to_string())
        .docstring(Some("Processes input data and returns result.".to_string()))
        .start_byte(100)
        .end_byte(500)
        .start_line(10)
        .end_line(35)
        .exec(&mut *db)
        .await?;

    // 5. Create SymbolReference
    let reference = SymbolReference::create()
        .project_id(project.id)
        .symbol_id(symbol.id)
        .file_id(source_file.id)
        .role("call".to_string())
        .start_byte(600)
        .end_byte(612)
        .line_number(42)
        .via("exact".to_string())
        .exec(&mut *db)
        .await?;

    // 6. Create CodeGraphEdge
    let edge = CodeGraphEdge::create()
        .project_id(project.id)
        .source_symbol_id(symbol.id)
        .target_symbol_id(999)
        .file_id(source_file.id)
        .edge_type("calls".to_string())
        .exec(&mut *db)
        .await?;

    // 7. Create ArchitecturalDecision
    let adr = ArchitecturalDecision::create()
        .project_id(project.id)
        .adr_number(1)
        .title("Use Turso Multi-Tenancy".to_string())
        .status("accepted".to_string())
        .context("Single database for all repos".to_string())
        .decision("Adopt logical multi-tenancy with project_id".to_string())
        .consequences("Simpler connections, fast cross-project queries".to_string())
        .affected_paths("[\"crates/scryer-db/*\"]".to_string())
        .exec(&mut *db)
        .await?;

    // 8. Create ToolInvocationMetric
    let metric = ToolInvocationMetric::create()
        .session_id("sess_123".to_string())
        .project_id(Some(project.id))
        .tool_name("resolve_definition".to_string())
        .payload_tokens(180)
        .estimated_naive_tokens(2400)
        .tokens_saved(2220)
        .truncated(false)
        .execution_duration_ms(15)
        .created_at("2026-10-06T00:00:00Z".to_string())
        .exec(&mut *db)
        .await?;

    // Verify indexed lookups:
    // Files by project_id and path
    let found_file = SourceFile::filter(
        SourceFile::fields()
            .project_id()
            .eq(project.id)
            .and(SourceFile::fields().path().eq("src/lib.rs")),
    )
    .first()
    .exec(&mut *db)
    .await?;
    assert!(found_file.is_some());
    assert_eq!(found_file.unwrap().id, source_file.id);

    // Symbols by project_id and qualified_name
    let found_sym = Symbol::filter(
        Symbol::fields().project_id().eq(project.id).and(
            Symbol::fields()
                .qualified_name()
                .eq("crate::lib::process_data"),
        ),
    )
    .first()
    .exec(&mut *db)
    .await?;
    assert!(found_sym.is_some());
    assert_eq!(found_sym.unwrap().name, "process_data");

    // References by project_id and symbol_id
    let found_refs = SymbolReference::filter(
        SymbolReference::fields()
            .project_id()
            .eq(project.id)
            .and(SymbolReference::fields().symbol_id().eq(symbol.id)),
    )
    .exec(&mut *db)
    .await?;
    assert_eq!(found_refs.len(), 1);
    assert_eq!(found_refs[0].id, reference.id);

    // Edges by source_symbol_id
    let found_edges = CodeGraphEdge::filter(
        CodeGraphEdge::fields()
            .project_id()
            .eq(project.id)
            .and(CodeGraphEdge::fields().source_symbol_id().eq(symbol.id)),
    )
    .exec(&mut *db)
    .await?;
    assert_eq!(found_edges.len(), 1);
    assert_eq!(found_edges[0].id, edge.id);

    // ADRs by project_id
    let found_adrs = ArchitecturalDecision::filter_by_project_id(project.id)
        .exec(&mut *db)
        .await?;
    assert_eq!(found_adrs.len(), 1);
    assert_eq!(found_adrs[0].id, adr.id);

    // Metrics by session_id
    let found_metrics =
        ToolInvocationMetric::filter(ToolInvocationMetric::fields().session_id().eq("sess_123"))
            .exec(&mut *db)
            .await?;
    assert_eq!(found_metrics.len(), 1);
    assert_eq!(found_metrics[0].id, metric.id);
    assert_eq!(found_metrics[0].tokens_saved, 2220);

    Ok(())
}

#[test]
fn registrable_roots_need_a_marker_and_exclude_home() -> anyhow::Result<()> {
    use scryer_db::is_registrable_root;

    let temp = tempfile::tempdir()?;
    let plain = temp.path().join("plain");
    let repo = temp.path().join("repo");
    std::fs::create_dir_all(&plain)?;
    std::fs::create_dir_all(repo.join(".git"))?;

    assert!(!is_registrable_root(&plain));
    assert!(is_registrable_root(&repo));
    assert!(!is_registrable_root(std::path::Path::new("/")));
    if let Some(home) = std::env::var_os("HOME") {
        assert!(!is_registrable_root(std::path::Path::new(&home)));
    }
    Ok(())
}

#[tokio::test]
async fn legacy_timestamps_upgrade_to_rfc3339() -> anyhow::Result<()> {
    let scryer_db = ScryerDb::new_in_memory().await?;
    let mut db = scryer_db.lock().await;

    let legacy = Project::create()
        .slug("legacy".to_string())
        .root_path("/tmp/legacy".to_string())
        .default_branch("main".to_string())
        .is_active(true)
        .created_at("1791334794.678Z".to_string())
        .updated_at("1791334800.000Z".to_string())
        .exec(&mut *db)
        .await?;
    let metric = ToolInvocationMetric::create()
        .session_id("s".to_string())
        .project_id(Some(legacy.id))
        .tool_name("list_projects".to_string())
        .payload_tokens(1)
        .estimated_naive_tokens(1)
        .tokens_saved(0)
        .truncated(false)
        .execution_duration_ms(1)
        .created_at("1791334794.678Z".to_string())
        .exec(&mut *db)
        .await?;

    // Running twice proves the upgrade is idempotent.
    scryer_db::upgrade_legacy_timestamps(&mut db).await?;
    scryer_db::upgrade_legacy_timestamps(&mut db).await?;

    let project = Project::filter_by_slug("legacy")
        .first()
        .exec(&mut *db)
        .await?
        .unwrap();
    assert_eq!(project.created_at, "2026-10-07T00:59:54.678Z");
    assert_eq!(project.updated_at, "2026-10-07T01:00:00.000Z");
    let metric = ToolInvocationMetric::filter(ToolInvocationMetric::fields().id().eq(metric.id))
        .first()
        .exec(&mut *db)
        .await?
        .unwrap();
    assert_eq!(metric.created_at, "2026-10-07T00:59:54.678Z");
    Ok(())
}

#[tokio::test]
async fn test_schema_version_is_recorded_once_and_newer_databases_are_refused() -> anyhow::Result<()>
{
    use scryer_db::{SCHEMA_VERSION, SchemaVersion};

    let dir = tempfile::tempdir()?;
    let path = dir.path().join("scryer.db");

    // A new database is stamped with the current version; reopening doesn't add rows.
    drop(ScryerDb::connect_file(&path).await?);
    {
        let scryer_db = ScryerDb::connect_file(&path).await?;
        let mut db = scryer_db.lock().await;
        let rows = SchemaVersion::all().exec(&mut *db).await?;
        assert_eq!(rows.len(), 1, "{rows:?}");
        assert_eq!(rows[0].version, SCHEMA_VERSION);

        // Simulate a database written by a newer Scryer.
        SchemaVersion::create()
            .version(SCHEMA_VERSION + 1)
            .applied_at("2026-10-07T00:00:00Z".to_string())
            .exec(&mut *db)
            .await?;
    }

    let err = ScryerDb::connect_file(&path)
        .await
        .expect_err("a newer schema must be refused");
    assert!(err.to_string().contains("schema version"), "{err}");
    Ok(())
}

#[tokio::test]
async fn test_registry_remove_deletes_every_row_the_project_owns() -> anyhow::Result<()> {
    use scryer_db::{
        ArchitecturalDecision, DependencyPackage, ProjectDependency, ProjectRegistry,
        ToolInvocationMetric,
    };

    let scryer_db = ScryerDb::new_in_memory().await?;
    let registry = ProjectRegistry::new(scryer_db.inner()).await?;
    let dir = tempfile::tempdir()?;
    let project = registry
        .register_or_update(dir.path(), Some("owned"))
        .await?;

    {
        let mut db = scryer_db.lock().await;
        let pkg = DependencyPackage::create()
            .name("dep".to_string())
            .version("1.0.0".to_string())
            .source_type("cratesio".to_string())
            .package_hash("h".to_string())
            .root_path("/x".to_string())
            .manifest_path("/x/Cargo.toml".to_string())
            .indexed_at("2026-10-07T00:00:00Z".to_string())
            .exec(&mut *db)
            .await?;
        ProjectDependency::create()
            .project_id(project.id)
            .dependency_package_id(pkg.id)
            .is_direct(true)
            .features(String::new())
            .exec(&mut *db)
            .await?;
        ToolInvocationMetric::create()
            .session_id("s".to_string())
            .project_id(Some(project.id))
            .tool_name("t".to_string())
            .payload_tokens(1)
            .estimated_naive_tokens(1)
            .tokens_saved(0)
            .truncated(false)
            .execution_duration_ms(1)
            .created_at("2026-10-07T00:00:00Z".to_string())
            .exec(&mut *db)
            .await?;
        ArchitecturalDecision::create()
            .project_id(project.id)
            .adr_number(1)
            .title("t".to_string())
            .status("accepted".to_string())
            .context("c".to_string())
            .decision("d".to_string())
            .consequences(String::new())
            .affected_paths("[]".to_string())
            .exec(&mut *db)
            .await?;
    }

    registry.remove(project.id).await?;

    let mut db = scryer_db.lock().await;
    assert!(ProjectDependency::all().exec(&mut *db).await?.is_empty());
    assert!(ToolInvocationMetric::all().exec(&mut *db).await?.is_empty());
    assert!(
        ArchitecturalDecision::all()
            .exec(&mut *db)
            .await?
            .is_empty()
    );
    assert!(
        !DependencyPackage::all().exec(&mut *db).await?.is_empty(),
        "shared dependency packages belong to no project"
    );
    Ok(())
}

/// A version 4 database (no `unresolved_reference` table, no `code_graph_edge.file_id`) opens,
/// gives its existing edges the file of their source symbol, and is stamped version 5.
#[tokio::test]
async fn test_v4_database_upgrades_to_v5() -> anyhow::Result<()> {
    use scryer_db::{SCHEMA_VERSION, SchemaVersion, UnresolvedReference};

    let dir = tempdir()?;
    let path = dir.path().join("scryer.db");
    let (project_id, file_id) = {
        let scryer_db = ScryerDb::connect_file(&path).await?;
        let mut db = scryer_db.lock().await;
        let project = Project::create()
            .slug("old".to_string())
            .root_path("/tmp/old".to_string())
            .default_branch("main".to_string())
            .is_active(true)
            .created_at("2026-10-06T00:00:00Z".to_string())
            .updated_at("2026-10-06T00:00:00Z".to_string())
            .exec(&mut *db)
            .await?;
        let file = SourceFile::create()
            .project_id(project.id)
            .path("src/lib.rs".to_string())
            .content_hash("h".to_string())
            .language("rust".to_string())
            .line_count(1)
            .byte_size(1)
            .exec(&mut *db)
            .await?;
        let symbol = Symbol::create()
            .project_id(project.id)
            .file_id(file.id)
            .name("f".to_string())
            .qualified_name("crate::f".to_string())
            .kind("fn".to_string())
            .visibility("public".to_string())
            .signature("fn f()".to_string())
            .start_byte(0)
            .end_byte(1)
            .start_line(1)
            .end_line(1)
            .exec(&mut *db)
            .await?;
        CodeGraphEdge::create()
            .project_id(project.id)
            .source_symbol_id(symbol.id)
            .target_symbol_id(symbol.id)
            .file_id(file.id)
            .edge_type("calls".to_string())
            .exec(&mut *db)
            .await?;
        // Take the database back to version 4.
        for stmt in [
            "DROP INDEX index_code_graph_edge_by_file_id;",
            "ALTER TABLE code_graph_edge DROP COLUMN file_id;",
            "DROP TABLE unresolved_reference;",
            "DELETE FROM schema_version;",
            "INSERT INTO schema_version (version, applied_at) VALUES (4, '2026-10-06T00:00:00Z');",
        ] {
            toasty::sql::statement(stmt).exec(&mut *db).await?;
        }
        (project.id, file.id)
    };

    let scryer_db = ScryerDb::connect_file(&path).await?;
    let mut db = scryer_db.lock().await;
    let edges = CodeGraphEdge::filter_by_project_id(project_id)
        .exec(&mut *db)
        .await?;
    assert_eq!(edges.len(), 1);
    assert_eq!(
        edges[0].file_id, file_id,
        "backfilled from the source symbol"
    );
    let versions: Vec<u64> = SchemaVersion::all()
        .exec(&mut *db)
        .await?
        .into_iter()
        .map(|v| v.version)
        .collect();
    assert_eq!(versions.iter().max(), Some(&SCHEMA_VERSION));
    UnresolvedReference::create()
        .project_id(project_id)
        .file_id(file_id)
        .target_name("missing".to_string())
        .bare_name("missing".to_string())
        .kind("reference".to_string())
        .role("call".to_string())
        .start_byte(0)
        .end_byte(7)
        .line_number(1)
        .via("name".to_string())
        .source_symbol_id(0)
        .edge_type(String::new())
        .exec(&mut *db)
        .await?;
    Ok(())
}