tga 10.3.0

//! Tests for all `tga backfill` subcommands.
//!
//! Why: keeping tests separate from implementation files allows the production
//! modules to stay under the 500 SLOC cap while maintaining test coverage.

use rusqlite::params;
use tga::core::config::Config;
use tga::core::db::Database;
use tga::core::effort::FORMULA_VERSION;

use super::effort::{backfill_effort_tshirt, persist_effort_rows};
use super::effort_db::process_one_repo_db;
use super::flags::{
    backfill_ai_detection_commits, backfill_revert_flags, backfill_ticket_ids, backfill_ticketed,
    is_revert,
};
use super::misc::{backfill_complexity, backfill_quality, backfill_top_level};
use super::pm_effort::backfill_pm_effort_at;
use super::pm_work::backfill_pm_work;
use super::types::{ComplexityBackfillArgs, EffortBackfillArgs, EffortRow};

fn seed(db: &Database, sha: &str, message: &str) {
    db.connection()
        .execute(
            "INSERT INTO commits (sha, author_name, author_email, timestamp, message, repository) \
             VALUES (?1, 'n', 'e', '2024-01-01T00:00:00Z', ?2, 'r')",
            params![sha, message],
        )
        .expect("insert");
}

#[test]
fn revert_detector_matches_expected_forms() {
    assert!(is_revert("Revert \"feat: add login\""));
    assert!(is_revert("revert: bad merge"));
    assert!(is_revert("Revert this change"));
    assert!(!is_revert("Refactor revert handling"));
    assert!(!is_revert("Fix bug in feature"));
}

#[test]
fn ticket_id_extraction_prefers_specific_patterns() {
    use tga::collect::ticket::extract_ticket_id;
    assert_eq!(
        extract_ticket_id("AB#42 implement"),
        Some("AB#42".to_string())
    );
    assert_eq!(
        extract_ticket_id("ENG-123: feature"),
        Some("ENG-123".to_string())
    );
    assert_eq!(extract_ticket_id("fixes #99"), Some("#99".to_string()));
    assert_eq!(extract_ticket_id("misc cleanup"), None);
}

#[test]
fn backfill_revert_flags_updates_only_changed_rows() {
    let mut db = Database::open_in_memory().expect("open");
    seed(&db, "a", "Revert \"foo\"");
    seed(&db, "b", "feat: thing");
    backfill_revert_flags(&mut db, false, &[], None, None).expect("backfill");
    let reverts: i64 = db
        .connection()
        .query_row(
            "SELECT COUNT(*) FROM commits WHERE is_revert = 1",
            [],
            |r| r.get(0),
        )
        .expect("q");
    assert_eq!(reverts, 1);
}

#[test]
fn backfill_ticket_ids_populates_ticket_id() {
    let mut db = Database::open_in_memory().expect("open");
    seed(&db, "a", "ENG-7: thing");
    seed(&db, "b", "no ticket");
    backfill_ticket_ids(&mut db, false, &[], None, None).expect("backfill");
    let t: Option<String> = db
        .connection()
        .query_row("SELECT ticket_id FROM commits WHERE sha = 'a'", [], |r| {
            r.get(0)
        })
        .expect("q");
    assert_eq!(t, Some("ENG-7".to_string()));
    let n: i64 = db
        .connection()
        .query_row("SELECT COUNT(*) FROM commits WHERE ticketed = 1", [], |r| {
            r.get(0)
        })
        .expect("q");
    assert_eq!(n, 1);
}

#[test]
fn dry_run_does_not_modify_rows() {
    let mut db = Database::open_in_memory().expect("open");
    seed(&db, "a", "Revert \"foo\"");
    backfill_revert_flags(&mut db, true, &[], None, None).expect("dry run");
    let reverts: i64 = db
        .connection()
        .query_row(
            "SELECT COUNT(*) FROM commits WHERE is_revert = 1",
            [],
            |r| r.get(0),
        )
        .expect("q");
    assert_eq!(reverts, 0);
}

/// Why: regression guard for issue #397 bug 2. `tga backfill complexity`
/// must be wired and its dry-run path must report the count of NULL-complexity
/// candidates without invoking the LLM (so it works offline) and without
/// mutating any row.
/// What: seed one classification with `complexity IS NULL` (regex_rule,
/// eligible) and one already-scored row (must not be counted); run the
/// dry-run backfill; assert no LLM is needed and nothing is written.
/// Test: in-memory DB; dry_run=true short-circuits before any LLM call.
#[tokio::test]
async fn backfill_complexity_dry_run_reports_candidates_without_writing() {
    let mut db = Database::open_in_memory().expect("open");

    // Candidate: NULL complexity, non-exact method.
    db.connection()
        .execute(
            "INSERT INTO classifications (category, confidence, method, complexity) \
             VALUES ('feature', 0.5, 'regex_rule', NULL)",
            [],
        )
        .expect("insert null-complexity row");
    // Not a candidate: already scored.
    db.connection()
        .execute(
            "INSERT INTO classifications (category, confidence, method, complexity) \
             VALUES ('bugfix', 0.8, 'regex_rule', 3)",
            [],
        )
        .expect("insert scored row");

    let args = ComplexityBackfillArgs { use_llm: false };
    // dry_run=true must not hit the network; Config::default() has no LLM key.
    backfill_complexity(Config::default(), &mut db, args, true)
        .await
        .expect("dry-run complexity backfill");

    // Nothing changed: the NULL row is still NULL, the scored row still 3.
    let null_count: i64 = db
        .connection()
        .query_row(
            "SELECT COUNT(*) FROM classifications WHERE complexity IS NULL",
            [],
            |r| r.get(0),
        )
        .expect("count null");
    assert_eq!(null_count, 1, "dry-run must not write complexity scores");
}

/// Why: #111 review — the `tga backfill complexity --dry-run` count must match
/// the rows the real backfill sends, and the real backfill skips merges.
/// What: seed two NULL-complexity `regex_rule` rows, each linked to a commit,
/// one of them a merge; the dry-run count is 1.
/// Test: in-memory DB; no LLM.
#[test]
fn complexity_dry_run_count_excludes_merges() {
    let db = Database::open_in_memory().expect("open");
    for (sha, is_merge) in [("m", 1), ("n", 0)] {
        seed(&db, sha, "add the widget");
        db.connection()
            .execute(
                "INSERT INTO classifications (category, confidence, method, complexity) \
                 VALUES ('feature', 0.5, 'regex_rule', NULL)",
                [],
            )
            .expect("insert classification");
        db.connection()
            .execute(
                "UPDATE commits SET classification_id = ?1, is_merge = ?2 WHERE sha = ?3",
                params![db.connection().last_insert_rowid(), is_merge, sha],
            )
            .expect("link commit");
    }
    let count = tga::classify::ClassificationPipeline::count_complexity_backfill_candidates(&db)
        .expect("count");
    assert_eq!(count, 1, "the merge is not a candidate");
}

// ── effort backfill tests ─────────────────────────────────────────────────

/// Why: verify the schema migration and UPSERT INSERT path work end-to-end.
/// What: calls `persist_effort_rows` with known data and reads it back.
/// Test: this test itself.
#[test]
fn backfill_effort_persists_rows() {
    let mut db = Database::open_in_memory().expect("open");

    let rows = vec![EffortRow {
        sha: "abc123".to_string(),
        repository: "testrepo".to_string(),
        size: "M".to_string(),
        score: 9.1,
        loc: 50,
        files: 2,
        test_loc: 0,
        tests_factor: 1.0,
        formula_version: FORMULA_VERSION.to_string(),
        computed_at: 1_000_000,
        effort_tshirt: 3,
    }];

    persist_effort_rows(&mut db, &rows).expect("persist");

    let (size, score, loc, files): (String, f64, i64, i64) = db
        .connection()
        .query_row(
            "SELECT size, score, loc, files \
             FROM fact_commit_effort WHERE sha = 'abc123'",
            [],
            |r| Ok((r.get(0)?, r.get(1)?, r.get(2)?, r.get(3)?)),
        )
        .expect("query");

    assert_eq!(size, "M");
    assert!((score - 9.1).abs() < 0.001);
    assert_eq!(loc, 50);
    assert_eq!(files, 2);
}

/// Why: verify that `--force` semantics replace an existing row with
/// updated values rather than silently keeping the old one.
/// What: inserts a row with score=1.0, then re-inserts with score=9.9
/// (simulating --force); asserts the score was updated.
/// Test: this test itself.
#[test]
fn backfill_effort_force_recomputes() {
    let mut db = Database::open_in_memory().expect("open");

    // First pass: insert initial row.
    let first = vec![EffortRow {
        sha: "deadbeef".to_string(),
        repository: "repo".to_string(),
        size: "XS".to_string(),
        score: 1.0,
        loc: 1,
        files: 1,
        test_loc: 0,
        tests_factor: 1.0,
        formula_version: FORMULA_VERSION.to_string(),
        computed_at: 1_000_000,
        effort_tshirt: 1,
    }];
    persist_effort_rows(&mut db, &first).expect("first persist");

    // Second pass: replace with updated score.
    let second = vec![EffortRow {
        sha: "deadbeef".to_string(),
        repository: "repo".to_string(),
        size: "XL".to_string(),
        score: 99.9,
        loc: 100_000,
        files: 500,
        test_loc: 0,
        tests_factor: 1.0,
        formula_version: FORMULA_VERSION.to_string(),
        computed_at: 2_000_000,
        effort_tshirt: 5,
    }];
    persist_effort_rows(&mut db, &second).expect("second persist");

    // Only one row should exist (no duplicate).
    let count: i64 = db
        .connection()
        .query_row(
            "SELECT COUNT(*) FROM fact_commit_effort WHERE sha = 'deadbeef'",
            [],
            |r| r.get(0),
        )
        .expect("count");
    assert_eq!(count, 1, "UPSERT must not create duplicate rows");

    let score: f64 = db
        .connection()
        .query_row(
            "SELECT score FROM fact_commit_effort WHERE sha = 'deadbeef'",
            [],
            |r| r.get(0),
        )
        .expect("score");
    assert!(
        (score - 99.9).abs() < 0.001,
        "score must be updated to 99.9"
    );
}

/// Why: `fact_commit_effort` must allow the same SHA in two different
/// repositories (fork/mirror scenarios).
/// What: insert two rows with the same SHA but different repository; both
/// must persist without conflict.
/// Test: this test itself.
#[test]
fn backfill_effort_same_sha_different_repos() {
    let mut db = Database::open_in_memory().expect("open");

    let rows = vec![
        EffortRow {
            sha: "cafebabe".to_string(),
            repository: "repo-a".to_string(),
            size: "S".to_string(),
            score: 5.5,
            loc: 30,
            files: 2,
            test_loc: 0,
            tests_factor: 1.0,
            formula_version: FORMULA_VERSION.to_string(),
            computed_at: 1_000_000,
            effort_tshirt: 2, // S=2
        },
        EffortRow {
            sha: "cafebabe".to_string(),
            repository: "repo-b".to_string(),
            size: "M".to_string(),
            score: 8.0,
            loc: 60,
            files: 3,
            test_loc: 0,
            tests_factor: 1.0,
            formula_version: FORMULA_VERSION.to_string(),
            computed_at: 1_000_000,
            effort_tshirt: 3, // M=3
        },
    ];

    persist_effort_rows(&mut db, &rows).expect("persist");

    let count: i64 = db
        .connection()
        .query_row(
            "SELECT COUNT(*) FROM fact_commit_effort WHERE sha = 'cafebabe'",
            [],
            |r| r.get(0),
        )
        .expect("count");
    assert_eq!(count, 2, "same SHA in two repos must produce two rows");
}

/// Why: an effort backfill on an empty repo should produce zero rows and
/// no errors.
/// What: calls `persist_effort_rows` with an empty slice.
/// Test: this test itself.
#[test]
fn backfill_effort_empty_produces_no_rows() {
    let mut db = Database::open_in_memory().expect("open");
    persist_effort_rows(&mut db, &[]).expect("empty persist");
    let count: i64 = db
        .connection()
        .query_row("SELECT COUNT(*) FROM fact_commit_effort", [], |r| r.get(0))
        .expect("count");
    assert_eq!(count, 0);
}

// ── db-path tests ─────────────────────────────────────────────────────────

/// Seed a commit row and its associated files rows into an in-memory DB.
///
/// Why: shared helper for db-path tests; avoids repetitive SQL in each test.
/// What: inserts one commit row and one or more file rows, returning the
/// commit's integer id.
/// Test: used by `backfill_effort_db_path_*` tests below.
fn seed_commit_with_files(
    db: &Database,
    sha: &str,
    repo: &str,
    timestamp: &str,
    files: &[(&str, u32, u32)], // (path, insertions, deletions)
) -> i64 {
    let conn = db.connection();
    conn.execute(
        "INSERT INTO commits (sha, author_name, author_email, timestamp, message, repository) \
         VALUES (?1, 'tester', 'test@example.com', ?2, 'msg', ?3)",
        params![sha, timestamp, repo],
    )
    .expect("insert commit");
    let commit_id = conn.last_insert_rowid();
    for (path, ins, del) in files {
        conn.execute(
            "INSERT INTO files (commit_id, path, change_type, insertions, deletions) \
             VALUES (?1, ?2, 'modified', ?3, ?4)",
            params![commit_id, path, ins, del],
        )
        .expect("insert file");
    }
    commit_id
}

/// Why: verify the db-only path reads `commits JOIN files` and populates
/// `fact_commit_effort` correctly without touching a git repo.
/// What: seeds two commits with file rows; calls `process_one_repo_db` and
/// then persists; asserts both rows appear in `fact_commit_effort`.
/// Test: this test itself.
#[test]
fn backfill_effort_db_path_populates_fact_table() {
    let mut db = Database::open_in_memory().expect("open");

    seed_commit_with_files(
        &db,
        "aaa111",
        "myrepo",
        "2024-01-01T00:00:00Z",
        &[("src/main.rs", 30, 10), ("src/lib.rs", 5, 2)],
    );
    seed_commit_with_files(
        &db,
        "bbb222",
        "myrepo",
        "2024-01-02T00:00:00Z",
        &[("src/tests/foo_test.rs", 20, 0)],
    );

    let args = EffortBackfillArgs {
        range: None,
        force: false,
        notes: false,
        limit: None,
    };

    let (scored, skipped, _sizes, rows) =
        process_one_repo_db(db.connection(), "myrepo", &args, false).expect("db path");
    assert_eq!(scored, 2, "both commits should be scored");
    assert_eq!(skipped, 0, "nothing pre-scored");

    persist_effort_rows(&mut db, &rows).expect("persist");

    let count: i64 = db
        .connection()
        .query_row(
            "SELECT COUNT(*) FROM fact_commit_effort WHERE repository = 'myrepo'",
            [],
            |r| r.get(0),
        )
        .expect("count");
    assert_eq!(count, 2, "two effort rows expected");

    // Verify the test-file commit has a reduced tests_factor.
    let (size_b, tests_factor_b): (String, f64) = db
        .connection()
        .query_row(
            "SELECT size, tests_factor FROM fact_commit_effort WHERE sha = 'bbb222'",
            [],
            |r| Ok((r.get(0)?, r.get(1)?)),
        )
        .expect("bbb222 row");
    // 20 test LoC out of 20 total → ratio=1 → tests_factor=0.7
    assert!(
        (tests_factor_b - 0.7).abs() < 1e-6,
        "expected tests_factor=0.7 for all-test commit, got {tests_factor_b}"
    );
    // score = 1.0*log2(21) + 1.5*log2(2) + 1.0*0.7 ≈ 4.392 + 1.5 + 0.7 = 6.592 → S
    assert_eq!(size_b, "S", "all-test commit should be S");
}

/// Why: verify the db-path respects the `--force=false` default — commits
/// that already have an effort row must be skipped.
/// What: inserts a pre-existing effort row for one commit; runs db path;
/// asserts only the unscored commit is returned.
/// Test: this test itself.
#[test]
fn backfill_effort_db_path_skips_already_scored() {
    let mut db = Database::open_in_memory().expect("open");

    seed_commit_with_files(
        &db,
        "scored111",
        "repo",
        "2024-01-01T00:00:00Z",
        &[("src/a.rs", 10, 0)],
    );
    seed_commit_with_files(
        &db,
        "unscored222",
        "repo",
        "2024-01-02T00:00:00Z",
        &[("src/b.rs", 5, 5)],
    );

    // Pre-populate an effort row for scored111.
    let pre = vec![EffortRow {
        sha: "scored111".to_string(),
        repository: "repo".to_string(),
        size: "XS".to_string(),
        score: 1.0,
        loc: 10,
        files: 1,
        test_loc: 0,
        tests_factor: 1.0,
        formula_version: FORMULA_VERSION.to_string(),
        computed_at: 0,
        effort_tshirt: 1, // XS=1
    }];
    persist_effort_rows(&mut db, &pre).expect("pre-persist");

    let args = EffortBackfillArgs {
        range: None,
        force: false,
        notes: false,
        limit: None,
    };

    let (scored, skipped, _sizes, rows) =
        process_one_repo_db(db.connection(), "repo", &args, false).expect("db path");

    assert_eq!(scored, 1, "only unscored222 should be scored");
    assert_eq!(skipped, 1, "scored111 should be skipped");
    assert_eq!(rows.len(), 1);
    assert_eq!(rows[0].sha, "unscored222");
}

/// Why: verify `--force` causes already-scored commits to be re-scored
/// rather than skipped on the db path.
/// What: pre-populates effort for a commit; runs db path with force=true;
/// asserts the commit appears in the returned rows.
/// Test: this test itself.
#[test]
fn backfill_effort_db_path_force_rescores_all() {
    let mut db = Database::open_in_memory().expect("open");

    seed_commit_with_files(
        &db,
        "sha001",
        "repo",
        "2024-01-01T00:00:00Z",
        &[("src/x.rs", 100, 50)],
    );

    // Insert a stale effort row.
    let stale = vec![EffortRow {
        sha: "sha001".to_string(),
        repository: "repo".to_string(),
        size: "XS".to_string(),
        score: 0.1,
        loc: 1,
        files: 1,
        test_loc: 0,
        tests_factor: 1.0,
        formula_version: "v0".to_string(),
        computed_at: 0,
        effort_tshirt: 1, // XS=1
    }];
    persist_effort_rows(&mut db, &stale).expect("stale persist");

    let args = EffortBackfillArgs {
        range: None,
        force: true, // re-score everything
        notes: false,
        limit: None,
    };

    let (scored, skipped, _sizes, rows) =
        process_one_repo_db(db.connection(), "repo", &args, false).expect("db path");

    assert_eq!(scored, 1, "force path should score the commit");
    assert_eq!(skipped, 0, "nothing should be skipped with --force");
    // The new score should reflect 150 LoC, not the stale 0.1.
    assert!(
        rows[0].score > 1.0,
        "re-scored effort should be higher than stale 0.1"
    );
}

/// Why: commits present in the `commits` table but with no rows in `files`
/// (e.g., empty commits) must not cause errors — they should be silently
/// skipped with a warning.
/// What: inserts a commit with no file rows; runs db path; asserts zero
/// records returned and no error raised.
/// Test: this test itself.
#[test]
fn backfill_effort_db_path_skips_commit_with_no_files() {
    let db = Database::open_in_memory().expect("open");

    // Insert commit row but NO file rows.
    db.connection()
        .execute(
            "INSERT INTO commits (sha, author_name, author_email, timestamp, message, repository) \
             VALUES ('empty001', 'tester', 'test@example.com', '2024-01-01T00:00:00Z', 'empty', 'repo')",
            [],
        )
        .expect("insert commit");

    // The above commit has no files rows, so the JOIN returns no rows —
    // `process_one_repo_db` will not even see a SHA to group.
    let args = EffortBackfillArgs {
        range: None,
        force: false,
        notes: false,
        limit: None,
    };

    let (scored, skipped, _sizes, rows) =
        process_one_repo_db(db.connection(), "repo", &args, false).expect("db path");

    // Zero files rows → nothing scored.
    assert_eq!(scored, 0, "commit with no files should produce no records");
    assert_eq!(skipped, 0);
    assert!(rows.is_empty());
}

/// Why: the `--limit N` flag must cap records at N even when more commits
/// are available in the db.
/// What: seeds 5 commits; runs db path with limit=3; asserts exactly 3
/// records are returned.
/// Test: this test itself.
#[test]
fn backfill_effort_db_path_respects_limit() {
    let db = Database::open_in_memory().expect("open");

    for i in 0..5u32 {
        seed_commit_with_files(
            &db,
            &format!("sha{i:03}"),
            "repo",
            &format!("2024-01-{:02}T00:00:00Z", i + 1),
            &[("src/foo.rs", 10, 5)],
        );
    }

    let args = EffortBackfillArgs {
        range: None,
        force: false,
        notes: false,
        limit: Some(3),
    };

    let (scored, _skipped, _sizes, rows) =
        process_one_repo_db(db.connection(), "repo", &args, false).expect("db path");

    assert_eq!(scored, 3, "limit=3 should cap at 3 records");
    assert_eq!(rows.len(), 3);
}

/// Why: the db path must correctly segregate commits by repository when
/// multiple repos share the same database.
/// What: seeds commits for two different repos; runs db path for one;
/// asserts only that repo's commits are scored.
/// Test: this test itself.
#[test]
fn backfill_effort_db_path_scoped_to_repo() {
    let db = Database::open_in_memory().expect("open");

    seed_commit_with_files(
        &db,
        "alpha001",
        "repo-alpha",
        "2024-01-01T00:00:00Z",
        &[("src/a.rs", 20, 10)],
    );
    seed_commit_with_files(
        &db,
        "beta001",
        "repo-beta",
        "2024-01-01T00:00:00Z",
        &[("src/b.rs", 50, 20)],
    );

    let args = EffortBackfillArgs {
        range: None,
        force: false,
        notes: false,
        limit: None,
    };

    // Process only repo-alpha.
    let (scored, _skipped, _sizes, rows) =
        process_one_repo_db(db.connection(), "repo-alpha", &args, false).expect("db path");

    assert_eq!(scored, 1);
    assert_eq!(rows[0].sha, "alpha001");
    assert_eq!(rows[0].repository, "repo-alpha");
}

/// Why: dry_run=true on the db path must return rows (for reporting) but
/// the caller must not persist them — this test verifies the path selection
/// in `backfill_effort` withholds `persist_effort_rows`.
/// What: directly calls `process_one_repo_db` with dry_run=true; asserts
/// rows are returned but `fact_commit_effort` remains empty.
/// Test: this test itself.
#[test]
fn backfill_effort_db_path_dry_run_returns_rows_without_persisting() {
    let db = Database::open_in_memory().expect("open");

    seed_commit_with_files(
        &db,
        "drysha1",
        "repo",
        "2024-01-01T00:00:00Z",
        &[("src/main.rs", 40, 10)],
    );

    let args = EffortBackfillArgs {
        range: None,
        force: false,
        notes: false,
        limit: None,
    };

    let (scored, _skipped, _sizes, rows) =
        process_one_repo_db(db.connection(), "repo", &args, true /* dry_run */).expect("db path");

    assert_eq!(
        scored, 1,
        "db path should return 1 scored row even in dry_run"
    );
    assert_eq!(rows.len(), 1);

    // Caller is responsible for not persisting in dry_run; here we do NOT
    // call persist_effort_rows, mirroring the behaviour in `backfill_effort`.
    let count: i64 = db
        .connection()
        .query_row("SELECT COUNT(*) FROM fact_commit_effort", [], |r| r.get(0))
        .expect("count");
    assert_eq!(count, 0, "dry_run must not write to fact_commit_effort");
}

// ── issue #445 backfill tests ─────────────────────────────────────────────

/// Why: regression guard for issue #445. `backfill_ticketed` must correct
/// rows where a bare `#N` was (incorrectly) stored as `ticketed=1` under the
/// old logic, setting them to `ticketed=0`. Rows with JIRA refs must stay 1.
/// What: seeds two commits (one bare-hash, one JIRA), runs the ticketed
/// backfill with dry_run=false, asserts the bare-hash row is now 0 and the
/// JIRA row remains 1.
/// Test: this test itself.
#[test]
fn backfill_ticketed_corrects_bare_hash_rows() {
    let mut db = Database::open_in_memory().expect("open");

    // Force-insert with ticketed=1 to simulate the pre-#445 incorrect state.
    db.connection()
        .execute(
            "INSERT INTO commits (sha, author_name, author_email, timestamp, message, \
             repository, ticketed) VALUES ('bare1', 'n', 'e', '2024-01-01T00:00:00Z', \
             'some note about #42', 'repo', 1)",
            [],
        )
        .expect("insert bare-hash commit");
    // JIRA ref — was and should remain ticketed.
    db.connection()
        .execute(
            "INSERT INTO commits (sha, author_name, author_email, timestamp, message, \
             repository, ticketed) VALUES ('jira1', 'n', 'e', '2024-01-02T00:00:00Z', \
             'ENG-7: add feature', 'repo', 1)",
            [],
        )
        .expect("insert JIRA commit");
    // Plain message — was and should remain 0.
    seed(&db, "plain1", "no ticket here");

    backfill_ticketed(&mut db, false, &[], None, None).expect("backfill ticketed");

    let bare_val: i64 = db
        .connection()
        .query_row(
            "SELECT ticketed FROM commits WHERE sha = 'bare1'",
            [],
            |r| r.get(0),
        )
        .expect("read bare");
    assert_eq!(bare_val, 0, "bare #N must be unticketed after backfill");

    let jira_val: i64 = db
        .connection()
        .query_row(
            "SELECT ticketed FROM commits WHERE sha = 'jira1'",
            [],
            |r| r.get(0),
        )
        .expect("read jira");
    assert_eq!(jira_val, 1, "JIRA ref must remain ticketed");
}

/// Why: verify `backfill_ai_detection_commits` detects Claude in an existing
/// commit message and sets `is_ai_assisted=1` / `ai_tool='claude'`.
/// What: seeds one Claude-co-authored commit and one plain human commit;
/// runs the backfill; asserts is_ai_assisted and ai_tool are set correctly.
/// Test: this test itself.
#[test]
fn backfill_ai_detection_commits_detects_claude() {
    let mut db = Database::open_in_memory().expect("open");

    // AI-assisted commit (Claude trailer).
    let ai_msg = "feat: add auth\n\nCo-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>";
    db.connection()
        .execute(
            "INSERT INTO commits (sha, author_name, author_email, timestamp, message, \
             repository) VALUES ('ai1', 'n', 'e', '2024-01-01T00:00:00Z', ?1, 'repo')",
            params![ai_msg],
        )
        .expect("insert AI commit");
    // Human-only commit.
    seed(&db, "human1", "fix: bug without AI help");

    backfill_ai_detection_commits(&mut db, false, &[], None, None).expect("backfill ai-detection");

    let (is_ai, tool): (i64, Option<String>) = db
        .connection()
        .query_row(
            "SELECT is_ai_assisted, ai_tool FROM commits WHERE sha = 'ai1'",
            [],
            |r| Ok((r.get(0)?, r.get(1)?)),
        )
        .expect("read ai1");
    assert_eq!(is_ai, 1, "AI-assisted commit must have is_ai_assisted=1");
    assert_eq!(tool, Some("claude".to_string()), "ai_tool must be 'claude'");

    let (human_ai, human_tool): (i64, Option<String>) = db
        .connection()
        .query_row(
            "SELECT is_ai_assisted, ai_tool FROM commits WHERE sha = 'human1'",
            [],
            |r| Ok((r.get(0)?, r.get(1)?)),
        )
        .expect("read human1");
    assert_eq!(human_ai, 0, "human commit must have is_ai_assisted=0");
    assert!(human_tool.is_none(), "human commit must have ai_tool=NULL");
}

/// Why: issue #1334 — the repair path left historical `agentic_mode` stuck at
/// its `'none'` default, so a Claude-co-authored commit backfilled after
/// migration v21 never became queryable as `agentic_mode = 'full_agentic'`.
/// What: seeds a Claude-trailer commit (defaulting to `agentic_mode = 'none'`),
/// a Cursor-trailer commit, and a plain human commit; runs the backfill; asserts
/// the Claude commit is now `'full_agentic'`, the Cursor commit `'ide_assisted'`,
/// and the human commit remains `'none'` — matching the forward `tga collect`
/// path.
/// Test: this test itself.
#[test]
fn backfill_ai_detection_commits_repairs_agentic_mode() {
    let mut db = Database::open_in_memory().expect("open");

    // Claude Code commit — must become full_agentic (the #1334 regression).
    let claude_msg = "feat: add auth\n\nCo-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>";
    seed(&db, "claude1", claude_msg);
    // Cursor IDE commit — must become ide_assisted.
    let cursor_msg = "fix: npe\n\nCo-Authored-By: Cursor <noreply@cursor.sh>";
    seed(&db, "cursor1", cursor_msg);
    // Human-only commit — must remain none.
    seed(&db, "human1", "chore: bump dep");

    // Sanity: all rows start at the migration default 'none' before the repair.
    let pre: String = db
        .connection()
        .query_row(
            "SELECT agentic_mode FROM commits WHERE sha = 'claude1'",
            [],
            |r| r.get(0),
        )
        .expect("read pre claude1");
    assert_eq!(
        pre, "none",
        "pre-backfill agentic_mode must default to 'none'"
    );

    backfill_ai_detection_commits(&mut db, false, &[], None, None).expect("backfill ai-detection");

    let claude_mode: String = db
        .connection()
        .query_row(
            "SELECT agentic_mode FROM commits WHERE sha = 'claude1'",
            [],
            |r| r.get(0),
        )
        .expect("read claude1 mode");
    assert_eq!(
        claude_mode, "full_agentic",
        "Claude commit must be repaired to agentic_mode='full_agentic' (#1334)"
    );

    let cursor_mode: String = db
        .connection()
        .query_row(
            "SELECT agentic_mode FROM commits WHERE sha = 'cursor1'",
            [],
            |r| r.get(0),
        )
        .expect("read cursor1 mode");
    assert_eq!(
        cursor_mode, "ide_assisted",
        "Cursor commit must be repaired to agentic_mode='ide_assisted'"
    );

    let human_mode: String = db
        .connection()
        .query_row(
            "SELECT agentic_mode FROM commits WHERE sha = 'human1'",
            [],
            |r| r.get(0),
        )
        .expect("read human1 mode");
    assert_eq!(
        human_mode, "none",
        "human commit must remain agentic_mode='none'"
    );
}

/// Why: #5249 — a commit carrying only the house footer stored
/// `agentic_mode = 'none'`, indistinguishable from human work. On this repo
/// that was 1058 of 2434 commits. This test fails against the pre-#5249
/// detector, whose body pattern required the literal "Claude Code".
/// What: seeds a trusty-mpm-footer commit with no `Co-Authored-By:` trailer
/// and an OpenHands co-author commit; asserts the repair pass classifies both
/// as `full_agentic` with the right tool label.
/// Test: this test itself.
#[test]
fn backfill_ai_detection_commits_repairs_house_footer_and_openhands() {
    let mut db = Database::open_in_memory().expect("open");

    seed(
        &db,
        "mpm1",
        "docs: add website link to README (#5330)\n\n\
         🤖🤖🤖 Generated with trusty-mpm — https://github.com/bobmatnyc/trusty-tools",
    );
    seed(
        &db,
        "oh1",
        "Fix runtime startup\n\nCo-authored-by: openhands <openhands@all-hands.dev>",
    );

    backfill_ai_detection_commits(&mut db, false, &[], None, None).expect("backfill ai-detection");

    for (sha, tool) in [("mpm1", "trusty-mpm"), ("oh1", "openhands")] {
        let (mode, ai_tool, is_ai): (String, Option<String>, i64) = db
            .connection()
            .query_row(
                "SELECT agentic_mode, ai_tool, is_ai_assisted FROM commits WHERE sha = ?1",
                params![sha],
                |r| Ok((r.get(0)?, r.get(1)?, r.get(2)?)),
            )
            .expect("read row");
        assert_eq!(mode, "full_agentic", "{sha} must be full_agentic (#5249)");
        assert_eq!(ai_tool.as_deref(), Some(tool), "{sha} tool label");
        assert_eq!(is_ai, 1, "{sha} must be flagged AI-assisted");
    }
}

/// Why: #4418 — the repair command is the path an operator runs to fix a
/// database without a full walk, so it has to write the new column too. It
/// carries a second hazard the collect path does not: it rewrites a row only
/// when the verdict differs, and on a real corpus the tool and mode already
/// agree while the method is NULL. Diffing on tool and mode alone would skip
/// exactly the rows the repair exists for, and report "0 updated" while every
/// method stayed empty.
/// What: seeds one commit per builtin scope plus a human commit, runs the
/// repair, and asserts each recorded method names the family that matched and
/// the human commit records none. The email row is seeded with a bot AUTHOR
/// address because `commits` has no committer column on this path.
/// Test: this test itself.
#[test]
fn backfill_ai_detection_commits_records_the_detection_method() {
    let mut db = Database::open_in_memory().expect("open");

    seed(
        &db,
        "trailer1",
        "feat: add auth\n\nCo-Authored-By: Claude <noreply@anthropic.com>",
    );
    seed(
        &db,
        "footer1",
        "docs: add website link to README (#5330)\n\n\
         🤖🤖🤖 Generated with trusty-mpm — https://github.com/bobmatnyc/trusty-tools",
    );
    seed(&db, "human2", "chore: bump dep");
    db.connection()
        .execute(
            "INSERT INTO commits (sha, author_name, author_email, timestamp, message, repository) \
             VALUES ('email1', 'n', 'openhands@all-hands.dev', '2024-01-01T00:00:00Z', \
                     'Fix flaky test', 'r')",
            [],
        )
        .expect("insert bot-authored commit");

    // A row a pre-#4418 collector left behind: the tool and mode are already
    // right, only the method is missing. Nothing but the method can mark it.
    db.connection()
        .execute(
            "UPDATE commits SET is_ai_assisted = 1, ai_tool = 'claude', \
             agentic_mode = 'full_agentic' WHERE sha = 'trailer1'",
            [],
        )
        .expect("pre-set the trailer row's verdict");

    backfill_ai_detection_commits(&mut db, false, &[], None, None).expect("backfill ai-detection");

    let method = |sha: &str| -> Option<String> {
        db.connection()
            .query_row(
                "SELECT ai_detection_method FROM commits WHERE sha = ?1",
                params![sha],
                |r| r.get(0),
            )
            .expect("read method")
    };
    assert_eq!(
        method("trailer1").as_deref(),
        Some("trailer"),
        "a row whose tool and mode already agreed must still be repaired"
    );
    assert_eq!(method("footer1").as_deref(), Some("message"));
    assert_eq!(method("email1").as_deref(), Some("email"));
    assert_eq!(method("human2"), None, "no verdict, no method");
}

/// Why: `backfill_top_level` must fill `top_level_category` for existing
/// classifications where it is NULL, using the built-in taxonomy.
/// What: seeds a classification with subcategory='bugfix' and
/// top_level_category=NULL; runs the backfill; asserts top_level_category
/// is now 'bugfix'.
/// Test: this test itself.
#[test]
fn backfill_top_level_fills_known_subcategories() {
    let mut db = Database::open_in_memory().expect("open");

    db.connection()
        .execute(
            "INSERT INTO classifications (category, subcategory, confidence, method) \
             VALUES ('bugfix', 'bugfix', 0.9, 'exact_rule')",
            [],
        )
        .expect("insert classification");

    backfill_top_level(&mut db, false).expect("backfill top-level");

    let top: Option<String> = db
        .connection()
        .query_row(
            "SELECT top_level_category FROM classifications WHERE subcategory = 'bugfix' \
             ORDER BY id DESC LIMIT 1",
            [],
            |r| r.get(0),
        )
        .expect("read top");
    assert_eq!(
        top,
        Some("bugfix".to_string()),
        "bugfix subcategory must resolve to 'bugfix' top-level"
    );
}

/// Why: `backfill_effort_tshirt` must populate `effort_tshirt` from the
/// existing `size` TEXT column for rows where the integer is NULL.
/// What: inserts an effort row with size='L' and effort_tshirt=NULL; runs
/// the backfill; asserts effort_tshirt is now 4 (L=4).
/// Test: this test itself.
#[test]
fn backfill_effort_tshirt_fills_from_size() {
    let mut db = Database::open_in_memory().expect("open");

    // Insert a row with size='L' but no effort_tshirt (simulating pre-v17 row).
    db.connection()
        .execute(
            "INSERT INTO fact_commit_effort \
             (sha, repository, size, score, loc, files, test_loc, tests_factor, \
              formula_version, computed_at) \
             VALUES ('tshirt_test', 'repo', 'L', 15.5, 200, 5, 0, 1.0, 'v1', 1000000)",
            [],
        )
        .expect("insert effort row without tshirt");

    backfill_effort_tshirt(&mut db, false).expect("backfill effort-tshirt");

    let tshirt: Option<i64> = db
        .connection()
        .query_row(
            "SELECT effort_tshirt FROM fact_commit_effort WHERE sha = 'tshirt_test'",
            [],
            |r| r.get(0),
        )
        .expect("read effort_tshirt");
    assert_eq!(tshirt, Some(4), "L size must map to effort_tshirt=4");
}

/// Why: regression guard for issue #445 batch B. `backfill_quality` must
/// populate `fact_weekly_quality` for all historical weeks, and running it
/// twice must produce the same result (idempotent UPSERT).
/// What: seeds two commits by the same author in the same ISO week with known
/// quality signals (one revert, one ticketed feature). Runs `backfill_quality`
/// once and checks the written row; runs it again and checks the row count
/// is still 1 (UPSERT did not duplicate).
/// Test: this test itself.
#[test]
fn backfill_quality_populates_and_is_idempotent() {
    let mut db = Database::open_in_memory().expect("open");
    // Seed: one revert + one ticketed feature by Alice in 2024-W03.
    db.connection()
        .execute(
            "INSERT INTO commits (sha, author_name, author_email, timestamp, message, \
                 repository, files_changed, insertions, deletions, is_merge, ticketed) \
             VALUES ('q1', 'Alice', 'alice@example.com', '2024-01-15T10:00:00+00:00', \
                 'ENG-1 feature', 'repo-a', 1, 5, 1, 0, 1)",
            [],
        )
        .expect("seed ticketed feature");
    db.connection()
        .execute(
            "INSERT INTO commits (sha, author_name, author_email, timestamp, message, \
                 repository, files_changed, insertions, deletions, is_merge, ticketed) \
             VALUES ('q2', 'Alice', 'alice@example.com', '2024-01-16T10:00:00+00:00', \
                 'Revert \"ENG-1 feature\"', 'repo-a', 1, 2, 5, 0, 0)",
            [],
        )
        .expect("seed revert");

    // First backfill — should write 1 row.
    backfill_quality(&mut db, false).expect("first backfill");
    let count: i64 = db
        .connection()
        .query_row(
            "SELECT COUNT(*) FROM fact_weekly_quality WHERE author_email = 'alice@example.com'",
            [],
            |r| r.get(0),
        )
        .expect("count after first backfill");
    assert_eq!(count, 1, "first backfill must write exactly 1 row");

    // Second backfill — idempotent: still 1 row.
    backfill_quality(&mut db, false).expect("second backfill (idempotency)");
    let count2: i64 = db
        .connection()
        .query_row(
            "SELECT COUNT(*) FROM fact_weekly_quality WHERE author_email = 'alice@example.com'",
            [],
            |r| r.get(0),
        )
        .expect("count after second backfill");
    assert_eq!(
        count2, 1,
        "second backfill must not add duplicate rows (UPSERT semantics)"
    );

    // Verify the quality_score is plausible (1 revert, 1 ticketed, 2 commits,
    // 0 bugfixes): 0.35*(1-0.5) + 0.40*(1-0) + 0.25*0.5 = 0.175+0.40+0.125 = 0.7.
    let score: f64 = db
        .connection()
        .query_row(
            "SELECT quality_score FROM fact_weekly_quality WHERE author_email = 'alice@example.com'",
            [],
            |r| r.get(0),
        )
        .expect("read quality_score");
    assert!(
        (score - 0.700).abs() < 0.001,
        "quality_score must be ~0.70 for this fixture, got {score:.6}"
    );
}

/// Why: regression guard for issue #445 batch B. `backfill_quality --dry-run`
/// must estimate the row count without writing to `fact_weekly_quality`.
/// What: seed two commits; run dry-run backfill; assert `fact_weekly_quality`
/// is still empty and the exit code is Ok.
/// Test: this test itself.
#[test]
fn backfill_quality_dry_run_does_not_write() {
    let mut db = Database::open_in_memory().expect("open");
    db.connection()
        .execute(
            "INSERT INTO commits (sha, author_name, author_email, timestamp, message, \
                 repository, files_changed, insertions, deletions, is_merge) \
             VALUES ('dq1', 'Bob', 'bob@example.com', '2024-02-05T10:00:00+00:00', \
                 'feat: x', 'repo-b', 1, 3, 1, 0)",
            [],
        )
        .expect("seed commit");

    backfill_quality(&mut db, true).expect("dry-run quality backfill must not error");

    let count: i64 = db
        .connection()
        .query_row("SELECT COUNT(*) FROM fact_weekly_quality", [], |r| r.get(0))
        .expect("count after dry-run");
    assert_eq!(
        count, 0,
        "dry-run must not write any rows to fact_weekly_quality"
    );
}

// ── effort-tshirt percentile tests (#445 batch C) ─────────────────────────

/// Helper: insert an effort row with a given score and size (0 for effort_tshirt).
fn seed_effort_row_for_tshirt(db: &Database, sha: &str, repo: &str, score: f64, size: &str) {
    db.connection()
        .execute(
            "INSERT OR REPLACE INTO fact_commit_effort \
             (sha, repository, size, score, loc, files, test_loc, tests_factor, \
              formula_version, computed_at, effort_tshirt) \
             VALUES (?1, ?2, ?3, ?4, 10, 1, 0, 1.0, 'v1', 0, 0)",
            params![sha, repo, size, score],
        )
        .expect("insert effort row");
}

/// Why: `backfill_effort_tshirt` must use corpus-percentile binning
/// (not static size→integer mapping) after batch C (#445).
/// What: seed 10 effort rows with scores 1–10; run backfill; assert that
/// effort_tshirt values reflect percentile quintiles and thresholds persist.
/// Test: this test itself.
#[test]
fn backfill_effort_tshirt_uses_percentile_binning() {
    let mut db = Database::open_in_memory().expect("open");

    // Seed 10 rows with scores 1.0 to 10.0 (all labeled "M" for simplicity).
    for i in 1..=10u32 {
        seed_effort_row_for_tshirt(&db, &format!("pct{i:03}"), "repo", i as f64, "M");
    }

    backfill_effort_tshirt(&mut db, false).expect("backfill");

    // With nearest-rank on [1..10]:
    //   p20=2, p40=4, p60=6, p80=8.
    // band_for_score: score=1 < 2 → 1; score=10 ≥ 8 → 5.
    let score1_band: i64 = db
        .connection()
        .query_row(
            "SELECT effort_tshirt FROM fact_commit_effort WHERE sha = 'pct001'",
            [],
            |r| r.get(0),
        )
        .expect("band for score=1");
    assert_eq!(score1_band, 1, "score=1 (below p20=2) → band 1");

    let score10_band: i64 = db
        .connection()
        .query_row(
            "SELECT effort_tshirt FROM fact_commit_effort WHERE sha = 'pct010'",
            [],
            |r| r.get(0),
        )
        .expect("band for score=10");
    assert_eq!(score10_band, 5, "score=10 (above p80=8) → band 5");

    // Verify thresholds were persisted.
    let stored = tga::core::effort_percentile::load_thresholds(db.connection())
        .expect("load thresholds")
        .expect("must be Some after backfill of 10 rows");
    assert!((stored.p20 - 2.0).abs() < 1e-9, "stored p20 must be 2.0");
    assert!((stored.p80 - 8.0).abs() < 1e-9, "stored p80 must be 8.0");
}

/// Why: tiny corpus (< 5 rows) must not panic; falls back to static
/// mapping without persisting thresholds.
/// What: seed 3 rows (all "L"), run backfill, assert effort_tshirt=4 (L→4)
/// and no thresholds stored.
/// Test: this test itself.
#[test]
fn backfill_effort_tshirt_tiny_corpus_fallback() {
    let mut db = Database::open_in_memory().expect("open");

    // 3 rows — below MIN_CORPUS_SIZE=5.
    for i in 1..=3u32 {
        seed_effort_row_for_tshirt(&db, &format!("tiny{i}"), "repo", i as f64, "L");
    }

    // Must not panic.
    backfill_effort_tshirt(&mut db, false).expect("backfill tiny corpus");

    // All rows should get static L=4 mapping.
    let tshirts: Vec<i64> = {
        let conn = db.connection();
        let mut stmt = conn
            .prepare("SELECT effort_tshirt FROM fact_commit_effort")
            .expect("prepare");
        stmt.query_map([], |r| r.get(0))
            .expect("query")
            .map(|r| r.expect("row"))
            .collect()
    };
    assert!(
        tshirts.iter().all(|&v| v == 4),
        "all rows must get L=4 (static fallback), got {tshirts:?}"
    );

    // No thresholds should be stored for a tiny corpus.
    let stored = tga::core::effort_percentile::load_thresholds(db.connection()).expect("load");
    assert!(
        stored.is_none(),
        "no thresholds should be stored for tiny corpus"
    );
}

// --- #3916: PM work meaningfulness tier ------------------------------------

/// Seed one `work_items` row with a JIRA-shaped `raw_json` payload.
fn seed_work_item(db: &Database, id: &str, title: &str, reporter: &str, description: Option<&str>) {
    seed_work_item_in_source(db, id, "jira", title, reporter, description);
}

/// Seed one `work_items` row under an explicit `source`.
///
/// Separate from [`seed_work_item`] so a test can put the SAME ticket key in
/// two PM sources — `work_items` is keyed `(id, source)` precisely because
/// ticket ids are unique per source, not globally.
fn seed_work_item_in_source(
    db: &Database,
    id: &str,
    source: &str,
    title: &str,
    reporter: &str,
    description: Option<&str>,
) {
    let raw = serde_json::json!({
        "fields": {
            "reporter": { "displayName": reporter },
            "description": description,
            "created": "2026-01-15T09:30:00.000+0000",
        }
    })
    .to_string();
    db.connection()
        .execute(
            "INSERT OR REPLACE INTO work_items \
             (id, source, title, status, item_type, raw_json) \
             VALUES (?1, ?2, ?3, 'Done', 'Task', ?4)",
            params![id, source, title, raw],
        )
        .expect("insert work item");
}

/// Seed one `fact_ticket_transitions` row so the BOT_FILED split has input.
fn seed_transition(db: &Database, ticket: &str, author: &str) {
    db.connection()
        .execute(
            "INSERT OR REPLACE INTO fact_ticket_transitions \
             (ticket_key, project_key, from_status, to_status, transitioned_at, author, synced_at) \
             VALUES (?1, 'PM', 'To Do', 'In Progress', '2026-01-16T10:00:00Z', ?2, 1)",
            params![ticket, author],
        )
        .expect("insert transition");
}

/// Read back `(is_meaningful, exclusion_reason, formula_version)` for a ticket.
fn read_verdict(db: &Database, id: &str) -> (i64, String, String) {
    read_verdict_in_source(db, id, "jira")
}

/// Read back one verdict, scoped to an explicit `source`.
fn read_verdict_in_source(db: &Database, id: &str, source: &str) -> (i64, String, String) {
    db.connection()
        .query_row(
            "SELECT is_meaningful, exclusion_reason, formula_version FROM fact_pm_work \
             WHERE work_item_id = ?1 AND work_item_source = ?2",
            params![id, source],
            |r| Ok((r.get(0)?, r.get(1)?, r.get(2)?)),
        )
        .unwrap_or_else(|e| panic!("read verdict for {source}/{id}: {e}"))
}

/// Seed one ticket per exclusion reason plus one meaningful ticket, so a
/// single backfill run proves every branch of the classifier reaches the
/// table with the reason the rule names.
fn seed_pm_work_corpus(db: &Database) {
    // TERSE_TITLE — the Fabiana sub-task case from issue #3916.
    seed_work_item(db, "PM-10215", "Final", "Fabiana Calabrese", None);
    // AUTO_GENERATED — bot reporter, nobody ever moved it.
    seed_work_item(
        db,
        "PM-20001",
        "Bump serde from 1.0.203 to 1.0.204 across the workspace",
        "dependabot[bot]",
        Some("Automated dependency update."),
    );
    // BOT_FILED — bot reporter, a human transitioned it afterwards.
    seed_work_item(
        db,
        "PM-20002",
        "Nightly integration suite failed on the pricing service",
        "Jenkins Automation",
        Some("Six assertions failed in the rate-shop regression pack."),
    );
    seed_transition(db, "PM-20002", "Fabiana Calabrese");
    // NONE — a substantive story.
    seed_work_item(
        db,
        "PM-30001",
        "Blend the occupancy forecast across four comparable weeks",
        "Fabiana Calabrese",
        Some(
            "As a revenue manager I want the occupancy forecast to blend the last four \
             comparable weeks so that one anomalous weekend stops dominating next month's \
             rate recommendations.",
        ),
    );
}

#[test]
fn backfill_pm_work_classifies_each_exclusion_reason() {
    let mut db = Database::open_in_memory().expect("db");
    seed_pm_work_corpus(&db);

    backfill_pm_work(&mut db, false).expect("backfill");

    for (id, meaningful, reason) in [
        ("PM-10215", 0, "TERSE_TITLE"),
        ("PM-20001", 0, "AUTO_GENERATED"),
        ("PM-20002", 0, "BOT_FILED"),
        ("PM-30001", 1, "NONE"),
    ] {
        let (is_meaningful, stored_reason, version) = read_verdict(&db, id);
        assert_eq!(is_meaningful, meaningful, "{id} is_meaningful");
        assert_eq!(stored_reason, reason, "{id} exclusion_reason");
        assert_eq!(version, "pm-work-1", "{id} formula_version");
    }
}

/// The BOT_FILED / AUTO_GENERATED split reads `fact_ticket_transitions`, so
/// the same bot-filed ticket must flip reason once a human moves it — and
/// must NOT flip when the mover is another bot.
#[test]
fn pm_work_backfill_marks_a_bot_filed_ticket_when_a_human_moved_it() {
    let mut db = Database::open_in_memory().expect("db");
    seed_work_item(
        &db,
        "PM-40001",
        "Dependency scan flagged three advisories in the pricing service",
        "dependabot[bot]",
        Some("Three advisories, one of them high severity."),
    );

    backfill_pm_work(&mut db, false).expect("first backfill");
    assert_eq!(read_verdict(&db, "PM-40001").1, "AUTO_GENERATED");

    // A second bot moving it is not human contact.
    seed_transition(&db, "PM-40001", "github-actions");
    backfill_pm_work(&mut db, false).expect("second backfill");
    assert_eq!(
        read_verdict(&db, "PM-40001").1,
        "AUTO_GENERATED",
        "a bot transition must not count as human triage"
    );

    seed_transition(&db, "PM-40001", "Charles Abbott");
    backfill_pm_work(&mut db, false).expect("third backfill");
    assert_eq!(read_verdict(&db, "PM-40001").1, "BOT_FILED");
}

/// Re-running the classifier over an unchanged corpus must rewrite the same
/// rows and create none: the upsert is keyed on
/// `(work_item_id, work_item_source)`.
#[test]
fn backfill_pm_work_is_idempotent() {
    let mut db = Database::open_in_memory().expect("db");
    seed_pm_work_corpus(&db);

    backfill_pm_work(&mut db, false).expect("first run");
    let first: Vec<(String, i64, String)> = read_all_verdicts(&db);
    assert_eq!(first.len(), 4, "one row per work item");

    backfill_pm_work(&mut db, false).expect("second run");
    let second = read_all_verdicts(&db);
    assert_eq!(
        first, second,
        "a second run must produce identical rows, not duplicates"
    );
}

/// Every `(work_item_id, is_meaningful, exclusion_reason)` in id order.
fn read_all_verdicts(db: &Database) -> Vec<(String, i64, String)> {
    let conn = db.connection();
    let mut stmt = conn
        .prepare(
            "SELECT work_item_id, is_meaningful, exclusion_reason FROM fact_pm_work \
             ORDER BY work_item_id",
        )
        .expect("prepare");
    let rows = stmt
        .query_map([], |r| Ok((r.get(0)?, r.get(1)?, r.get(2)?)))
        .expect("query");
    rows.map(|r| r.expect("row")).collect()
}

#[test]
fn backfill_pm_work_dry_run_writes_nothing() {
    let mut db = Database::open_in_memory().expect("db");
    seed_pm_work_corpus(&db);

    backfill_pm_work(&mut db, true).expect("dry run");

    let count: i64 = db
        .connection()
        .query_row("SELECT COUNT(*) FROM fact_pm_work", [], |r| r.get(0))
        .expect("count");
    assert_eq!(count, 0, "--dry-run must not write any row");
}

/// A ticket whose payload carries no reporter, description, or created date
/// must still get a row — the classifier treats an absent reporter as human,
/// so only the title decides.
#[test]
fn backfill_pm_work_handles_a_payload_with_no_extractable_fields() {
    let mut db = Database::open_in_memory().expect("db");
    db.connection()
        .execute(
            "INSERT INTO work_items (id, source, title, status, item_type) \
             VALUES ('PM-50001', 'jira', 'Historical Occupancy', 'Done', 'Sub-task')",
            [],
        )
        .expect("insert");

    backfill_pm_work(&mut db, false).expect("backfill");

    let (meaningful, reason, _) = read_verdict(&db, "PM-50001");
    assert_eq!(meaningful, 0);
    assert_eq!(reason, "TERSE_TITLE");

    let (pm_name, week_key): (Option<String>, Option<String>) = db
        .connection()
        .query_row(
            "SELECT pm_name, week_key FROM fact_pm_work WHERE work_item_id = 'PM-50001'",
            [],
            |r| Ok((r.get(0)?, r.get(1)?)),
        )
        .expect("read");
    assert_eq!(pm_name, None, "no reporter in the payload");
    assert_eq!(week_key, None, "no creation date in the payload");
}

/// A ticket key is unique per PM source, not globally (`work_items` is keyed
/// `(id, source)`), but `fact_ticket_transitions` carries no source column —
/// every row in it comes from the JIRA sync (#3966). Looking a candidate up
/// by bare ticket key therefore let a JIRA ticket's human transition leak
/// onto a same-keyed ticket from another source, flipping that one from
/// AUTO_GENERATED to BOT_FILED.
#[test]
fn a_human_transition_does_not_leak_across_pm_sources() {
    let mut db = Database::open_in_memory().expect("db");
    let title = "Retry budget exhausted on the rate-shop worker";
    let body = Some("The worker gave up after 40 attempts.");
    // Same key, two sources, both filed by a bot.
    seed_work_item_in_source(&db, "ENG-42", "jira", title, "dependabot[bot]", body);
    seed_work_item_in_source(&db, "ENG-42", "linear", title, "dependabot[bot]", body);
    // Only the JIRA ticket was ever moved by a person.
    seed_transition(&db, "ENG-42", "Charles Abbott");

    backfill_pm_work(&mut db, false).expect("backfill");

    assert_eq!(
        read_verdict_in_source(&db, "ENG-42", "jira").1,
        "BOT_FILED",
        "the JIRA ticket really was transitioned by a human"
    );
    assert_eq!(
        read_verdict_in_source(&db, "ENG-42", "linear").1,
        "AUTO_GENERATED",
        "the Linear ticket has no transitions of its own; JIRA's must not leak onto it"
    );
}

// --- `tga backfill pm-effort` (#3915) ---------------------------------------

/// One seeded ticket for the effort-tier tests.
struct EffortItemSpec<'a> {
    id: &'a str,
    source: &'a str,
    title: &'a str,
    item_type: &'a str,
    reporter: &'a str,
    description: &'a str,
    created: &'a str,
    parent: Option<&'a str>,
    story_points: Option<f64>,
}

impl<'a> EffortItemSpec<'a> {
    /// A substantive JIRA story authored well before the recency floor.
    fn new(id: &'a str, title: &'a str) -> Self {
        Self {
            id,
            source: "jira",
            title,
            item_type: "Story",
            reporter: "Fabiana Calabrese",
            description: "",
            created: "2026-01-15T09:30:00.000+0000",
            parent: None,
            story_points: None,
        }
    }
}

/// Seed one `work_items` row with the effort-tier fields: item type, parent
/// key, story points, description and creation date.
///
/// Separate from [`seed_work_item`], which seeds only what the WORK tier
/// reads. Both write the same `raw_json` shape, so a ticket seeded here is
/// also classifiable by `backfill_pm_work` — which is how these tests get
/// the `fact_pm_work` rows the effort tier gates on.
fn seed_effort_item(spec: &EffortItemSpec<'_>, db: &Database) {
    let mut fields = serde_json::json!({
        "reporter": { "displayName": spec.reporter },
        "description": spec.description,
        "created": spec.created,
    });
    if let Some(parent) = spec.parent {
        fields["parent"] = serde_json::json!({ "key": parent });
    }
    if let Some(points) = spec.story_points {
        fields["customfield_10004"] = serde_json::json!(points);
    }
    let raw = serde_json::json!({ "fields": fields }).to_string();
    db.connection()
        .execute(
            "INSERT OR REPLACE INTO work_items \
             (id, source, title, status, item_type, raw_json) \
             VALUES (?1, ?2, ?3, 'In Progress', ?4, ?5)",
            params![spec.id, spec.source, spec.title, spec.item_type, raw],
        )
        .expect("insert effort work item");
}

/// A description body of exactly `words` words, so a test can state the
/// description term it expects instead of counting prose by hand.
fn body_of(words: usize) -> String {
    vec!["forecast"; words].join(" ")
}

/// Seed one `fact_jira_comment_detail` row.
fn seed_comment(db: &Database, ticket: &str, comment_id: &str) {
    db.connection()
        .execute(
            "INSERT OR REPLACE INTO fact_jira_comment_detail \
             (ticket_key, comment_id, project_key, author, created_at, body_len, synced_at) \
             VALUES (?1, ?2, 'PM', 'Fabiana Calabrese', '2026-01-16T10:00:00Z', 120, 1)",
            params![ticket, comment_id],
        )
        .expect("insert comment");
}

/// Seed one `fact_ticket_transitions` row at a distinct timestamp, so
/// repeat transitions on one ticket do not collide on the primary key.
fn seed_transition_at(db: &Database, ticket: &str, at: &str) {
    db.connection()
        .execute(
            "INSERT OR REPLACE INTO fact_ticket_transitions \
             (ticket_key, project_key, from_status, to_status, transitioned_at, author, synced_at) \
             VALUES (?1, 'PM', 'To Do', 'In Progress', ?2, 'Fabiana Calabrese', 1)",
            params![ticket, at],
        )
        .expect("insert transition");
}

/// The fixed instant every effort test scores at, so ticket age is a stated
/// number of days rather than whatever `Utc::now()` happens to be.
fn scoring_instant() -> chrono::DateTime<chrono::Utc> {
    "2026-03-01T00:00:00Z".parse().expect("parse instant")
}

/// An RFC3339 creation date `days` before [`scoring_instant`].
fn created_days_before(days: i64) -> String {
    (scoring_instant() - chrono::Duration::days(days)).to_rfc3339()
}

/// `(effort_score, effort_bucket, score_status, inputs_present, age_days)`.
type EffortRowValues = (Option<f64>, Option<String>, String, String, Option<i64>);

/// Read back the score row a test asserts on.
fn read_effort(db: &Database, id: &str) -> EffortRowValues {
    read_effort_in_source(db, id, "jira")
}

/// Read back one score row, scoped to an explicit `source`.
fn read_effort_in_source(db: &Database, id: &str, source: &str) -> EffortRowValues {
    db.connection()
        .query_row(
            "SELECT effort_score, effort_bucket, score_status, inputs_present, \
             age_days_at_score FROM fact_pm_effort \
             WHERE work_item_id = ?1 AND work_item_source = ?2",
            params![id, source],
            |r| Ok((r.get(0)?, r.get(1)?, r.get(2)?, r.get(3)?, r.get(4)?)),
        )
        .unwrap_or_else(|e| panic!("read effort row for {source}/{id}: {e}"))
}

/// Whether `fact_pm_effort` holds a row for this ticket at all.
fn has_effort_row(db: &Database, id: &str) -> bool {
    let count: i64 = db
        .connection()
        .query_row(
            "SELECT COUNT(*) FROM fact_pm_effort WHERE work_item_id = ?1",
            params![id],
            |r| r.get(0),
        )
        .expect("count effort rows");
    count > 0
}

/// Read one integer column of one `fact_pm_effort` row.
fn read_effort_int(db: &Database, id: &str, source: &str, column: &str) -> i64 {
    let sql = format!(
        "SELECT {column} FROM fact_pm_effort \
         WHERE work_item_id = ?1 AND work_item_source = ?2"
    );
    db.connection()
        .query_row(&sql, params![id, source], |r| r.get(0))
        .unwrap_or_else(|e| panic!("read {column} for {source}/{id}: {e}"))
}

/// Run the WORK tier, then the EFFORT tier at [`scoring_instant`]. The
/// effort tier reads `fact_pm_work`, so a test that skipped the first call
/// would be asserting against an empty candidate set.
fn run_both_tiers(db: &mut Database) {
    backfill_pm_work(db, false).expect("pm-work backfill");
    backfill_pm_effort_at(db, false, scoring_instant()).expect("pm-effort backfill");
}

/// Three tickets whose inputs put them in the three different buckets, so a
/// single run proves the bucket boundaries reach the table.
#[test]
fn backfill_pm_effort_scores_each_bucket() {
    let mut db = Database::open_in_memory().expect("db");

    // LOW: a real story with a short body and nothing else. 1.0 + 40/40 = 2.0.
    let low_body = body_of(40);
    let mut low = EffortItemSpec::new("PM-60001", "Cap the rate-shop retry budget");
    low.description = &low_body;
    seed_effort_item(&low, &db);

    // MEDIUM: 1.0 + 320/40 + 6*0.8 + 4*0.5 = 15.8.
    let medium_body = body_of(320);
    let mut medium = EffortItemSpec::new("PM-60002", "Blend the occupancy forecast");
    medium.description = &medium_body;
    seed_effort_item(&medium, &db);
    for n in 1..=6 {
        seed_comment(&db, "PM-60002", &format!("c{n}"));
    }
    for n in 1..=4 {
        seed_transition_at(&db, "PM-60002", &format!("2026-01-2{n}T10:00:00Z"));
    }

    // HIGH: an epic with nine children. 1.0 + 18.0 + 10.0 + 4*0.8 + 3.0 = 35.2.
    let high_body = body_of(400);
    let mut high = EffortItemSpec::new("PM-60003", "ML Plat - Observability and Monitoring");
    high.item_type = "Epic";
    high.description = &high_body;
    high.story_points = Some(8.0);
    seed_effort_item(&high, &db);
    for n in 1..=9 {
        let child_id = format!("PM-6100{n}");
        let mut child = EffortItemSpec::new(&child_id, "Wire the metric exporter into the mesh");
        child.parent = Some("PM-60003");
        seed_effort_item(&child, &db);
    }
    for n in 1..=4 {
        seed_comment(&db, "PM-60003", &format!("h{n}"));
    }

    run_both_tiers(&mut db);

    assert_eq!(
        read_effort(&db, "PM-60001"),
        (
            Some(2.0),
            Some("LOW".to_string()),
            "SCORED".to_string(),
            "DESCRIPTION".to_string(),
            Some(44)
        )
    );
    assert_eq!(
        read_effort(&db, "PM-60002"),
        (
            Some(15.8),
            Some("MEDIUM".to_string()),
            "SCORED".to_string(),
            "DESCRIPTION,COMMENTS,TRANSITIONS".to_string(),
            Some(44)
        )
    );
    assert_eq!(
        read_effort(&db, "PM-60003"),
        (
            Some(35.2),
            Some("HIGH".to_string()),
            "SCORED".to_string(),
            "CHILDREN,DESCRIPTION,COMMENTS,STORY_POINTS".to_string(),
            Some(44)
        )
    );

    let version: String = db
        .connection()
        .query_row(
            "SELECT DISTINCT formula_version FROM fact_pm_effort",
            [],
            |r| r.get(0),
        )
        .expect("read formula_version");
    assert_eq!(version, "pm-effort-1");
}

/// Issue #3915's motivating case: three epics authored the same day showed
/// zero children at scoring time. Scoring them would have read "not yet
/// decomposed" as "simple"; the row must say the score was deferred.
#[test]
fn a_recent_epic_is_recorded_as_deferred_not_scored_zero() {
    let mut db = Database::open_in_memory().expect("db");
    let body = body_of(60);

    for (id, age) in [("ML-2314", 0), ("ML-2315", 3), ("ML-2316", 6)] {
        let created = created_days_before(age);
        let mut epic = EffortItemSpec::new(id, "ML Plat - Orchestration Layer");
        epic.item_type = "Epic";
        epic.reporter = "Rohit Puntambekar";
        epic.description = &body;
        epic.created = &created;
        seed_effort_item(&epic, &db);
    }
    // The same epic exactly at the floor IS scored.
    let created = created_days_before(7);
    let mut old_epic = EffortItemSpec::new("ML-2200", "ML Plat - Feature Store");
    old_epic.item_type = "Epic";
    old_epic.reporter = "Rohit Puntambekar";
    old_epic.description = &body;
    old_epic.created = &created;
    seed_effort_item(&old_epic, &db);

    run_both_tiers(&mut db);

    for (id, age) in [("ML-2314", 0), ("ML-2315", 3), ("ML-2316", 6)] {
        let (score, bucket, status, inputs, age_days) = read_effort(&db, id);
        assert_eq!(score, None, "{id} must not carry a score");
        assert_eq!(bucket, None, "{id} must not carry a bucket");
        assert_eq!(status, "DEFERRED_RECENT", "{id} status");
        assert_eq!(inputs, "NONE", "{id} contributed no terms");
        assert_eq!(age_days, Some(age), "{id} age is still recorded");
    }

    let (score, bucket, status, _, age_days) = read_effort(&db, "ML-2200");
    assert_eq!(score, Some(2.5), "1.0 + 60/40 = 2.5");
    assert_eq!(bucket.as_deref(), Some("LOW"));
    assert_eq!(status, "SCORED");
    assert_eq!(age_days, Some(7));
}

/// The recency floor guards types whose child count accrues after creation.
/// A bug filed yesterday has no such input, so deferring it would withhold a
/// score for nothing.
#[test]
fn a_recent_bug_is_scored_rather_than_deferred() {
    let mut db = Database::open_in_memory().expect("db");
    let body = body_of(80);
    let created = created_days_before(1);
    let mut bug = EffortItemSpec::new("PM-70001", "Rate-shop worker drops the retry budget");
    bug.item_type = "Bug";
    bug.description = &body;
    bug.created = &created;
    seed_effort_item(&bug, &db);

    run_both_tiers(&mut db);

    let (score, bucket, status, _, age_days) = read_effort(&db, "PM-70001");
    assert_eq!(score, Some(3.0), "1.0 + 80/40 = 3.0");
    assert_eq!(bucket.as_deref(), Some("LOW"));
    assert_eq!(status, "SCORED");
    assert_eq!(age_days, Some(1));
}

/// A ticket whose payload carries no parseable creation date cannot be
/// aged, so the floor cannot fire; it is scored on its other inputs and the
/// row records the unknown age as NULL.
#[test]
fn a_ticket_with_no_creation_date_is_scored_not_deferred() {
    let mut db = Database::open_in_memory().expect("db");
    db.connection()
        .execute(
            "INSERT INTO work_items (id, source, title, status, item_type, raw_json) \
             VALUES ('PM-70002', 'jira', 'ML Plat - Serving Layer', 'To Do', 'Epic', \
                     '{\"fields\":{\"description\":\"one two three four\"}}')",
            [],
        )
        .expect("insert");

    run_both_tiers(&mut db);

    let (score, bucket, status, inputs, age_days) = read_effort(&db, "PM-70002");
    assert_eq!(score, Some(1.1), "1.0 + 4/40 = 1.1");
    assert_eq!(bucket.as_deref(), Some("LOW"));
    assert_eq!(status, "SCORED");
    assert_eq!(inputs, "DESCRIPTION");
    assert_eq!(age_days, None, "an unparseable created date stores NULL");
}

/// Issue #3915: story_points is 76% NULL across four per-project
/// custom-field IDs. Two otherwise identical tickets must differ only by the
/// story-point term, and the one without must still carry a real score.
#[test]
fn missing_story_points_degrade_the_score_and_are_named_on_the_row() {
    let mut db = Database::open_in_memory().expect("db");
    let body = body_of(200);

    let mut with_points = EffortItemSpec::new("PM-80001", "Blend the occupancy forecast");
    with_points.description = &body;
    with_points.story_points = Some(5.0);
    seed_effort_item(&with_points, &db);

    let mut without = EffortItemSpec::new("PM-80002", "Blend the compression forecast");
    without.description = &body;
    seed_effort_item(&without, &db);

    // A value parked in the story-point field that is not an estimate must
    // be treated as absent rather than crashing or skewing the score.
    let mut nonsense = EffortItemSpec::new("PM-80003", "Blend the pickup forecast");
    nonsense.description = &body;
    nonsense.story_points = Some(86_400_000.0);
    seed_effort_item(&nonsense, &db);

    run_both_tiers(&mut db);

    let (score, _, _, inputs, _) = read_effort(&db, "PM-80001");
    assert_eq!(score, Some(8.0), "1.0 + 200/40 + 5*0.4 = 8.0");
    assert_eq!(inputs, "DESCRIPTION,STORY_POINTS");

    for id in ["PM-80002", "PM-80003"] {
        let (score, bucket, status, inputs, _) = read_effort(&db, id);
        assert_eq!(score, Some(6.0), "{id}: the description term still scores");
        assert_eq!(bucket.as_deref(), Some("LOW"), "{id} bucket");
        assert_eq!(status, "SCORED", "{id} status");
        assert_eq!(
            inputs, "DESCRIPTION",
            "{id}: the row must say the story-point term did not fire"
        );
    }

    let stored: Option<f64> = db
        .connection()
        .query_row(
            "SELECT story_points FROM fact_pm_effort WHERE work_item_id = 'PM-80003'",
            [],
            |r| r.get(0),
        )
        .expect("read story_points");
    assert_eq!(stored, None, "an implausible value is stored as NULL");
}

/// The effort tier scores the WORK tier's output. A ticket `fact_pm_work`
/// excluded must get no row at all — scoring it would re-admit through the
/// effort tier exactly the boilerplate the work tier removed.
#[test]
fn an_excluded_ticket_gets_no_effort_row() {
    let mut db = Database::open_in_memory().expect("db");
    seed_pm_work_corpus(&db);

    run_both_tiers(&mut db);

    for excluded in ["PM-10215", "PM-20001", "PM-20002"] {
        assert!(
            !has_effort_row(&db, excluded),
            "{excluded} was excluded by fact_pm_work and must not be scored"
        );
    }
    assert!(
        has_effort_row(&db, "PM-30001"),
        "the meaningful story must be scored"
    );

    let count: i64 = db
        .connection()
        .query_row("SELECT COUNT(*) FROM fact_pm_effort", [], |r| r.get(0))
        .expect("count");
    assert_eq!(count, 1, "exactly one of the four tickets is meaningful");
}

/// The meaningfulness gate is only durable if it is re-applied: a ticket
/// that `pm-work` reclassifies as boilerplate must lose the effort row an
/// earlier run wrote, or it goes on feeding every dashboard reading it.
#[test]
fn a_ticket_that_becomes_non_meaningful_loses_its_effort_row() {
    let mut db = Database::open_in_memory().expect("db");
    let body = body_of(120);
    let mut story = EffortItemSpec::new("PM-90001", "Blend the occupancy forecast");
    story.description = &body;
    seed_effort_item(&story, &db);

    run_both_tiers(&mut db);
    assert!(has_effort_row(&db, "PM-90001"));

    // The ticket is re-collected as a terse stub with no body, so the work
    // tier now excludes it.
    db.connection()
        .execute(
            "UPDATE work_items SET title = 'Final', raw_json = '{}' WHERE id = 'PM-90001'",
            [],
        )
        .expect("update");

    run_both_tiers(&mut db);
    assert!(
        !has_effort_row(&db, "PM-90001"),
        "the stale effort row must be pruned once the ticket stops being meaningful"
    );
}

/// Children are counted per source from the parent keys their own payloads
/// carry, and a child's own meaningfulness verdict is irrelevant — a terse
/// sub-task is still evidence the parent was decomposed.
#[test]
fn epic_children_are_counted_from_parent_keys() {
    let mut db = Database::open_in_memory().expect("db");
    let mut epic = EffortItemSpec::new("PM-91000", "ML Plat - Deployment and Serving Layer");
    epic.item_type = "Epic";
    seed_effort_item(&epic, &db);

    // Two substantive children and one terse stub the work tier excludes.
    for id in ["PM-91001", "PM-91002"] {
        let mut child = EffortItemSpec::new(id, "Wire the exporter into the service mesh");
        child.parent = Some("PM-91000");
        seed_effort_item(&child, &db);
    }
    let mut stub = EffortItemSpec::new("PM-91003", "Final");
    stub.parent = Some("PM-91000");
    seed_effort_item(&stub, &db);
    // A same-keyed epic in another source must not inherit the JIRA children.
    let mut linear_epic = EffortItemSpec::new("PM-91000", "ML Plat - Deployment and Serving Layer");
    linear_epic.source = "linear";
    linear_epic.item_type = "Epic";
    seed_effort_item(&linear_epic, &db);

    run_both_tiers(&mut db);

    assert_eq!(
        read_effort_int(&db, "PM-91000", "jira", "epic_children_count"),
        3,
        "the terse stub still counts as decomposition"
    );
    assert_eq!(
        read_effort(&db, "PM-91000").0,
        Some(7.0),
        "1.0 + 3 * 2.0 = 7.0"
    );
    assert_eq!(
        read_effort_int(&db, "PM-91000", "linear", "epic_children_count"),
        0,
        "JIRA's children must not leak onto a same-keyed Linear epic"
    );
}

/// `fact_jira_comment_detail` and `fact_ticket_transitions` carry no source
/// column — every row in both comes from the JIRA sync (#3966). Looking a
/// candidate up by bare ticket key would let a JIRA ticket's discussion
/// inflate the score of a same-keyed ticket from another source.
#[test]
fn jira_comments_and_transitions_do_not_leak_across_pm_sources() {
    let mut db = Database::open_in_memory().expect("db");
    let body = body_of(40);
    for source in ["jira", "linear"] {
        let mut item = EffortItemSpec::new("ENG-42", "Retry budget exhausted on the worker");
        item.source = source;
        item.description = &body;
        seed_effort_item(&item, &db);
    }
    for n in 1..=5 {
        seed_comment(&db, "ENG-42", &format!("c{n}"));
        seed_transition_at(&db, "ENG-42", &format!("2026-01-2{n}T10:00:00Z"));
    }

    run_both_tiers(&mut db);

    assert_eq!(
        read_effort_in_source(&db, "ENG-42", "jira"),
        (
            Some(8.5), // 1.0 + 1.0 + 5*0.8 + 5*0.5
            Some("LOW".to_string()),
            "SCORED".to_string(),
            "DESCRIPTION,COMMENTS,TRANSITIONS".to_string(),
            Some(44)
        )
    );
    assert_eq!(
        read_effort_in_source(&db, "ENG-42", "linear"),
        (
            Some(2.0), // 1.0 + 1.0, and nothing else
            Some("LOW".to_string()),
            "SCORED".to_string(),
            "DESCRIPTION".to_string(),
            Some(44)
        ),
        "the Linear ticket has no discussion of its own; JIRA's must not leak onto it"
    );
}

/// Every stored column except `computed_at` — which is by design the
/// wall-clock instant of the write — rendered as one comparable string per
/// row, so the idempotency check compares whole rows rather than a subset.
fn read_all_effort_rows(db: &Database) -> Vec<String> {
    let conn = db.connection();
    let mut stmt = conn
        .prepare(
            "SELECT work_item_id, work_item_source, pm_name, week_key, effort_score, \
             effort_bucket, score_status, epic_children_count, description_word_count, \
             comment_count, transition_count, story_points, inputs_present, \
             age_days_at_score, formula_version FROM fact_pm_effort \
             ORDER BY work_item_source, work_item_id",
        )
        .expect("prepare");
    let rows = stmt
        .query_map([], |r| {
            Ok(format!(
                "{}|{}|{:?}|{:?}|{:?}|{:?}|{}|{}|{}|{}|{}|{:?}|{}|{:?}|{}",
                r.get::<_, String>(0)?,
                r.get::<_, String>(1)?,
                r.get::<_, Option<String>>(2)?,
                r.get::<_, Option<String>>(3)?,
                r.get::<_, Option<f64>>(4)?,
                r.get::<_, Option<String>>(5)?,
                r.get::<_, String>(6)?,
                r.get::<_, i64>(7)?,
                r.get::<_, i64>(8)?,
                r.get::<_, i64>(9)?,
                r.get::<_, i64>(10)?,
                r.get::<_, Option<f64>>(11)?,
                r.get::<_, String>(12)?,
                r.get::<_, Option<i64>>(13)?,
                r.get::<_, String>(14)?,
            ))
        })
        .expect("query");
    rows.map(|r| r.expect("row")).collect()
}

#[test]
fn backfill_pm_effort_is_idempotent() {
    let mut db = Database::open_in_memory().expect("db");
    let body = body_of(200);

    let mut epic = EffortItemSpec::new("PM-92000", "ML Plat - Orchestration Layer");
    epic.item_type = "Epic";
    epic.description = &body;
    epic.story_points = Some(8.0);
    seed_effort_item(&epic, &db);
    for n in 1..=3 {
        let child_id = format!("PM-9200{n}");
        let mut child = EffortItemSpec::new(&child_id, "Wire the scheduler into the mesh");
        child.parent = Some("PM-92000");
        child.description = &body;
        seed_effort_item(&child, &db);
    }
    // A deferred epic, so the idempotency check covers the NULL-score shape.
    let created = created_days_before(2);
    let mut recent = EffortItemSpec::new("PM-92100", "ML Plat - Observability");
    recent.item_type = "Epic";
    recent.description = &body;
    recent.created = &created;
    seed_effort_item(&recent, &db);
    seed_comment(&db, "PM-92000", "c1");
    seed_transition_at(&db, "PM-92000", "2026-01-21T10:00:00Z");

    run_both_tiers(&mut db);
    let first = read_all_effort_rows(&db);
    assert_eq!(first.len(), 5, "one row per meaningful ticket");

    backfill_pm_effort_at(&mut db, false, scoring_instant()).expect("second run");
    let second = read_all_effort_rows(&db);
    assert_eq!(
        first, second,
        "a second run at the same instant must produce identical rows, not duplicates"
    );
}

#[test]
fn backfill_pm_effort_dry_run_writes_nothing() {
    let mut db = Database::open_in_memory().expect("db");
    seed_pm_work_corpus(&db);
    backfill_pm_work(&mut db, false).expect("pm-work backfill");

    backfill_pm_effort_at(&mut db, true, scoring_instant()).expect("dry run");

    let count: i64 = db
        .connection()
        .query_row("SELECT COUNT(*) FROM fact_pm_effort", [], |r| r.get(0))
        .expect("count");
    assert_eq!(count, 0, "--dry-run must not write any row");
}

/// `fact_pm_work` is the gate. With no work-tier rows there are no
/// candidates, and the run must succeed with an empty table rather than
/// scoring every ticket ungated.
#[test]
fn backfill_pm_effort_scores_nothing_when_the_work_tier_has_not_run() {
    let mut db = Database::open_in_memory().expect("db");
    seed_pm_work_corpus(&db);

    backfill_pm_effort_at(&mut db, false, scoring_instant()).expect("backfill");

    let count: i64 = db
        .connection()
        .query_row("SELECT COUNT(*) FROM fact_pm_effort", [], |r| r.get(0))
        .expect("count");
    assert_eq!(count, 0, "no fact_pm_work rows means no effort candidates");
}