dirge-agent 0.18.5

//! SQLite-backed reusable-skill store (dirge-70ht).
//!
//! A skill is a named, procedural-like memory with supporting content —
//! authored by `/learn` (dirge-s99m) from source material or a
//! conversation, then reused across sessions. Skills live in the
//! `skills` table of the per-project session DB (created idempotently in
//! [`crate::extras::session_db`]) and reuse the same salience machinery
//! as memories ([`crate::extras::salience`]): reinforce on invoke, decay
//! on disuse, effectiveness from a success/failure record, confidence as
//! a tiebreak. That reuse is the whole point — an unused skill decays out
//! of the prompt, an invoked-but-failing one sinks on negative
//! effectiveness, a working one stays hot — so the library self-prunes
//! instead of growing stale.
//!
//! Where memories carry five kinds, a skill is uniformly procedural, so
//! the effectiveness term is always live (no per-kind gate). `source`
//! separates agent-`learned` skills (DB-resident, subject to curation)
//! from `file`-registered ones (dirge-izju; git-tracked, pinned exempt).
//!
// The store exposes a complete CRUD + ranking surface. The telemetry,
// ranking, decay, and archive APIs are wired (skill tool, preamble,
// curator); the DB-resident `create`/`invoke`/`search`/`set_pinned`
// paths are validated by tests and stand ready for their UI callers (a
// skill-search action, a pin command) rather than being wired
// speculatively. Allow the still-unwired-but-tested surface.
#![allow(dead_code)]

use std::sync::Mutex;

use rusqlite::{Connection, OptionalExtension, params};

use crate::extras::dirge_paths::ProjectPaths;
use crate::extras::salience::{
    DECAY_FLOOR, DEFAULT_CONFIDENCE, DISUSE_DECAY, RECENT_USE_BONUS, RECENT_USE_WINDOW_DAYS,
    USE_REINFORCEMENT, confidence_eviction_bonus, effectiveness_bonus,
};
use crate::extras::session_db::{SessionDb, redact_for_fts};

/// Base salience for a freshly learned skill. Skills are procedural-like,
/// so this matches `default_salience_for_kind(Procedural)` in the memory
/// store — the two stores start a playbook at the same importance.
const SKILL_BASE_SALIENCE: f64 = 0.5;

/// Max results returned by [`SkillStore::search`]. Mirrors the memory
/// store's search cap.
const SEARCH_RESULT_LIMIT: usize = 8;

/// One skill row as callers see it. Field-complete so the tool layer
/// (dirge-a47a) can render list/view/search without re-querying.
#[derive(Debug, Clone)]
pub struct SkillRow {
    pub uid: String,
    pub name: String,
    pub description: String,
    pub content: String,
    pub source: String,
    pub skill_path: Option<String>,
    pub status: String,
    pub tier: String,
    pub pinned: bool,
    pub confidence: f64,
    pub salience: f64,
    pub created_at: String,
    pub updated_at: String,
    pub last_used_at: Option<String>,
    pub last_viewed_at: Option<String>,
    pub last_patched_at: Option<String>,
    pub use_count: i64,
    pub view_count: i64,
    pub patch_count: i64,
    pub success_count: i64,
    pub failure_count: i64,
    pub last_success_at: Option<String>,
}

impl SkillRow {
    fn from_row(row: &rusqlite::Row) -> rusqlite::Result<Self> {
        Ok(SkillRow {
            uid: row.get("uid")?,
            name: row.get("name")?,
            description: row.get("description")?,
            content: row.get("content")?,
            source: row.get("source")?,
            skill_path: row.get("skill_path")?,
            status: row.get("status")?,
            tier: row.get("tier")?,
            pinned: row.get::<_, i64>("pinned")? != 0,
            confidence: row.get("confidence")?,
            salience: row.get("salience")?,
            created_at: row.get("created_at")?,
            updated_at: row.get("updated_at")?,
            last_used_at: row.get("last_used_at")?,
            last_viewed_at: row.get("last_viewed_at")?,
            last_patched_at: row.get("last_patched_at")?,
            use_count: row.get("use_count")?,
            view_count: row.get("view_count")?,
            patch_count: row.get("patch_count")?,
            success_count: row.get("success_count")?,
            failure_count: row.get("failure_count")?,
            last_success_at: row.get("last_success_at")?,
        })
    }

    /// Salience folded with the live signals used for ranking and
    /// eviction: recency of use, proven effectiveness, and confidence.
    /// Unlike memories this needs no per-kind gate — every skill is a
    /// playbook, so the effectiveness term always applies.
    pub fn effective_salience(&self, recent_use_cutoff: &str) -> f64 {
        let recent = self
            .last_used_at
            .as_deref()
            .is_some_and(|t| t >= recent_use_cutoff);
        self.salience
            + if recent { RECENT_USE_BONUS } else { 0.0 }
            + effectiveness_bonus(self.success_count, self.failure_count)
            + confidence_eviction_bonus(self.confidence)
    }

    /// [`effective_salience`](Self::effective_salience) against the
    /// current time — the form the curator (dirge-izju) uses to decide
    /// archival.
    pub fn effective_salience_now(&self) -> f64 {
        self.effective_salience(&recent_use_cutoff())
    }
}

/// Provenance. `learned` = agent-created (via the skill tool or
/// `/learn`), so the curator manages it. `file` = a discovered on-disk
/// skill the agent didn't author (bundled/user); the curator leaves it
/// alone. Orthogonal to `pinned`, which is an explicit curation-exempt
/// flag set by the user on either kind.
#[derive(Debug, Clone, Copy, PartialEq, Eq)]
pub enum SkillSource {
    Learned,
    File,
}

impl SkillSource {
    fn as_str(&self) -> &'static str {
        match self {
            SkillSource::Learned => "learned",
            SkillSource::File => "file",
        }
    }
}

/// Validate a skill name: lowercase-hyphenated slug, ≤64 chars. Same
/// shape as the on-disk skill directory names and Hermes' rule, so a
/// learned skill and a file skill share one namespace.
pub fn validate_skill_name(name: &str) -> Result<(), String> {
    if name.is_empty() || name.len() > 64 {
        return Err("Skill name must be 1–64 characters".to_string());
    }
    if !name
        .chars()
        .all(|c| c.is_ascii_lowercase() || c.is_ascii_digit() || c == '-' || c == '.')
    {
        return Err(format!(
            "Skill name '{name}' must be lowercase letters, digits, hyphens, or dots"
        ));
    }
    if name.starts_with('-') || name.ends_with('-') {
        return Err("Skill name must not start or end with a hyphen".to_string());
    }
    Ok(())
}

/// Port of the memory store's UMP id: 128 random bits, base32, prefixed.
fn random_skill_id() -> String {
    crate::extras::memory_db::random_entry_id()
}

/// The redacted FTS projection — name + description + body so a skill is
/// findable by title, with secret shapes scrubbed like `memories_fts`.
fn fts_projection(name: &str, description: &str, content: &str) -> String {
    redact_for_fts(&format!("{name}\n{description}\n{content}"))
}

/// SQLite-backed skill store. Holds the live DB connection; unlike the
/// memory store it captures no frozen snapshot here — prompt rendering
/// (dirge-a47a) queries ranked rows on demand.
pub struct SkillStore {
    conn: Mutex<Connection>,
}

impl SkillStore {
    /// Open (and migrate) the per-project session DB and build a store.
    /// Shares `state.db` with sessions and memory; the skills tables are
    /// created idempotently on open.
    pub fn load(paths: &ProjectPaths) -> Result<Self, String> {
        std::fs::create_dir_all(paths.sessions_dir())
            .map_err(|e| format!("Failed to create sessions directory: {e}"))?;
        let db = SessionDb::open(&paths.session_db_path())?;
        Self::from_connection(db.conn)
    }

    /// Build a store from an open, migrated connection. The seam the
    /// tests use with an in-memory or temp DB.
    pub fn from_connection(conn: Connection) -> Result<Self, String> {
        conn.busy_timeout(std::time::Duration::from_secs(5))
            .map_err(|e| format!("Failed to set busy timeout: {e}"))?;
        Ok(SkillStore {
            conn: Mutex::new(conn),
        })
    }

    /// Insert a new skill. Validates the name, threat-scans and redacts
    /// the content, and rejects a duplicate name. `learned` skills start
    /// unpinned and curated; `file` skills are pinned (eviction/archival
    /// exempt) since they're intentional and git-tracked.
    pub fn create(
        &self,
        name: &str,
        description: &str,
        content: &str,
        source: SkillSource,
        skill_path: Option<&str>,
    ) -> Result<SkillRow, String> {
        validate_skill_name(name)?;
        let description = description.trim();
        if description.is_empty() {
            return Err("Skill description must not be empty".to_string());
        }
        let content = content.trim();
        if content.is_empty() {
            return Err("Skill content must not be empty".to_string());
        }
        crate::extras::memory_db::scan_for_threats(content)?;
        let content = redact_for_fts(content);

        let mut conn = self.conn.lock().unwrap();
        if Self::get_locked(&conn, name)?.is_some() {
            return Err(format!("A skill named '{name}' already exists"));
        }

        let uid = random_skill_id();
        let now = chrono::Utc::now().to_rfc3339();
        let tx = conn
            .transaction()
            .map_err(|e| format!("Failed to begin transaction: {e}"))?;
        tx.execute(
            "INSERT INTO skills
                 (uid, name, description, content, source, skill_path, status,
                  tier, pinned, confidence, salience, created_at, updated_at,
                  use_count, view_count, patch_count, success_count, failure_count)
             VALUES (?1, ?2, ?3, ?4, ?5, ?6, 'active', 'hot', 0, ?7, ?8, ?9, ?9, 0, 0, 0, 0, 0)",
            params![
                uid,
                name,
                description,
                content,
                source.as_str(),
                skill_path,
                DEFAULT_CONFIDENCE,
                SKILL_BASE_SALIENCE,
                now,
            ],
        )
        .map_err(|e| format!("Failed to insert skill: {e}"))?;
        let rowid = tx.last_insert_rowid();
        tx.execute(
            "INSERT INTO skills_fts(rowid, content) VALUES (?1, ?2)",
            params![rowid, fts_projection(name, description, &content)],
        )
        .map_err(|e| format!("Failed to index skill: {e}"))?;
        tx.commit().map_err(|e| format!("Failed to commit: {e}"))?;

        Self::get_locked(&conn, name)?
            .ok_or_else(|| "Skill vanished immediately after insert".to_string())
    }

    // ── Telemetry superset (replaces the .usage.json UsageStore) ─────
    //
    // These mirror `UsageStore`'s best-effort counter bumps: they upsert
    // a bare row for a skill first seen outside the `create` path (e.g. a
    // pre-existing file skill invoked before it was registered), and swap
    // JSON-sidecar storage for the sqlite skills table so telemetry and
    // salience live together. Failures are logged, never propagated —
    // telemetry must not break the underlying skill operation.

    /// Insert a minimal row for `name` if none exists, so a best-effort
    /// counter bump always has a row to land on. A skill first seen this
    /// way is a `file` (not agent-created) skill with empty text until
    /// [`register_file_skill`](Self::register_file_skill) fills it in.
    fn ensure_row_locked(conn: &Connection, name: &str) -> Result<(), String> {
        if Self::get_locked(conn, name)?.is_some() {
            return Ok(());
        }
        let now = chrono::Utc::now().to_rfc3339();
        conn.execute(
            "INSERT INTO skills
                 (uid, name, description, content, source, status, tier,
                  pinned, confidence, salience, created_at, updated_at)
             VALUES (?1, ?2, '', '', 'file', 'active', 'hot', 0, ?3, ?4, ?5, ?5)",
            params![
                random_skill_id(),
                name,
                DEFAULT_CONFIDENCE,
                SKILL_BASE_SALIENCE,
                now,
            ],
        )
        .map_err(|e| format!("Failed to ensure skill row: {e}"))?;
        Ok(())
    }

    /// Record a skill invocation: bump `use_count`, stamp `last_used_at`,
    /// and reinforce salience (capped at 1.0) — being reached for IS the
    /// relevance signal. Best-effort.
    pub fn record_use(&self, name: &str) {
        if let Err(e) = self.bump_use(name) {
            tracing::debug!(target: "dirge::skills", error = %e, "record_use failed");
        }
    }

    fn bump_use(&self, name: &str) -> Result<(), String> {
        let conn = self.conn.lock().unwrap();
        Self::ensure_row_locked(&conn, name)?;
        let now = chrono::Utc::now().to_rfc3339();
        conn.execute(
            "UPDATE skills
             SET use_count = use_count + 1, last_used_at = ?1,
                 salience = MIN(1.0, salience + ?2)
             WHERE name = ?3",
            params![now, USE_REINFORCEMENT, name],
        )
        .map_err(|e| format!("bump use: {e}"))?;
        Ok(())
    }

    /// Record a skill view (content read). Best-effort.
    pub fn record_view(&self, name: &str) {
        if let Err(e) = self.bump_counter(name, "view_count", "last_viewed_at") {
            tracing::debug!(target: "dirge::skills", error = %e, "record_view failed");
        }
    }

    /// Record a skill patch (content edited). Best-effort.
    pub fn record_patch(&self, name: &str) {
        if let Err(e) = self.bump_counter(name, "patch_count", "last_patched_at") {
            tracing::debug!(target: "dirge::skills", error = %e, "record_patch failed");
        }
    }

    fn bump_counter(&self, name: &str, counter: &str, stamp: &str) -> Result<(), String> {
        let conn = self.conn.lock().unwrap();
        Self::ensure_row_locked(&conn, name)?;
        let now = chrono::Utc::now().to_rfc3339();
        // `counter`/`stamp` are internal constants, never user input.
        conn.execute(
            &format!("UPDATE skills SET {counter} = {counter} + 1, {stamp} = ?1 WHERE name = ?2"),
            params![now, name],
        )
        .map_err(|e| format!("bump {counter}: {e}"))?;
        Ok(())
    }

    /// Record a skill creation event, marking provenance. `created_by ==
    /// "agent"` flags the skill as agent-created (curator-managed);
    /// anything else leaves it a `file` (bundled/user) skill. Best-effort
    /// upsert — an existing row keeps its earlier provenance.
    pub fn record_create(&self, name: &str, created_by: &str) {
        if let Err(e) = self.do_record_create(name, created_by) {
            tracing::debug!(target: "dirge::skills", error = %e, "record_create failed");
        }
    }

    fn do_record_create(&self, name: &str, created_by: &str) -> Result<(), String> {
        let conn = self.conn.lock().unwrap();
        Self::ensure_row_locked(&conn, name)?;
        if created_by == "agent" {
            conn.execute(
                "UPDATE skills SET source = 'learned' WHERE name = ?1",
                params![name],
            )
            .map_err(|e| format!("record_create: {e}"))?;
        }
        Ok(())
    }

    /// Set the pinned flag (curation-exempt). Upserts a row if needed.
    pub fn set_pinned(&self, name: &str, pinned: bool) -> Result<(), String> {
        let conn = self.conn.lock().unwrap();
        Self::ensure_row_locked(&conn, name)?;
        conn.execute(
            "UPDATE skills SET pinned = ?1 WHERE name = ?2",
            params![pinned as i64, name],
        )
        .map_err(|e| format!("set_pinned: {e}"))?;
        Ok(())
    }

    /// Provenance filter: only agent-created skills are curator-managed
    /// (`source = 'learned'`). Bundled/user file skills return false.
    pub fn is_agent_created(&self, name: &str) -> bool {
        self.get(name)
            .ok()
            .flatten()
            .map(|r| r.source == "learned")
            .unwrap_or(false)
    }

    /// Register (upsert) a skill discovered on disk so it's tracked and
    /// searchable: insert a row if absent, else refresh its description,
    /// content, and FTS projection so ranking/search see the current
    /// text. Preserves salience/usage lineage on an existing row.
    /// `agent_created` seeds provenance only on first insert.
    pub fn register_file_skill(
        &self,
        name: &str,
        description: &str,
        content: &str,
        agent_created: bool,
    ) -> Result<(), String> {
        validate_skill_name(name)?;
        let description = description.trim();
        let content = redact_for_fts(content.trim());
        let mut conn = self.conn.lock().unwrap();
        let now = chrono::Utc::now().to_rfc3339();
        let source = if agent_created { "learned" } else { "file" };
        let existed = Self::get_locked(&conn, name)?.is_some();
        let tx = conn
            .transaction()
            .map_err(|e| format!("Failed to begin transaction: {e}"))?;
        if existed {
            tx.execute(
                "UPDATE skills SET status = 'active', description = ?1, content = ?2, updated_at = ?3
                 WHERE name = ?4",
                params![description, content, now, name],
            )
            .map_err(|e| format!("Failed to refresh skill: {e}"))?;
        } else {
            tx.execute(
                "INSERT INTO skills
                     (uid, name, description, content, source, status, tier,
                      pinned, confidence, salience, created_at, updated_at)
                 VALUES (?1, ?2, ?3, ?4, ?5, 'active', 'hot', 0, ?6, ?7, ?8, ?8)",
                params![
                    random_skill_id(),
                    name,
                    description,
                    content,
                    source,
                    DEFAULT_CONFIDENCE,
                    SKILL_BASE_SALIENCE,
                    now,
                ],
            )
            .map_err(|e| format!("Failed to register skill: {e}"))?;
        }
        let rowid: i64 = tx
            .query_row(
                "SELECT id FROM skills WHERE name = ?1",
                params![name],
                |r| r.get(0),
            )
            .map_err(|e| format!("Failed to read skill id: {e}"))?;
        tx.execute("DELETE FROM skills_fts WHERE rowid = ?1", params![rowid])
            .map_err(|e| format!("Failed to reindex skill: {e}"))?;
        tx.execute(
            "INSERT INTO skills_fts(rowid, content) VALUES (?1, ?2)",
            params![rowid, fts_projection(name, description, &content)],
        )
        .map_err(|e| format!("Failed to reindex skill: {e}"))?;
        tx.commit().map_err(|e| format!("Failed to commit: {e}"))?;
        Ok(())
    }

    /// Fetch a skill by exact name (any status).
    pub fn get(&self, name: &str) -> Result<Option<SkillRow>, String> {
        let conn = self.conn.lock().unwrap();
        Self::get_locked(&conn, name)
    }

    fn get_locked(conn: &Connection, name: &str) -> Result<Option<SkillRow>, String> {
        conn.query_row(
            "SELECT * FROM skills WHERE name = ?1",
            params![name],
            SkillRow::from_row,
        )
        .optional()
        .map_err(|e| format!("Failed to fetch skill: {e}"))
    }

    /// All active skills, highest effective salience first (ties: oldest
    /// first, matching the memory store's stable ordering). This is the
    /// order the prompt index (dirge-a47a) renders and the curator
    /// (dirge-izju) evaluates.
    pub fn list_active(&self) -> Result<Vec<SkillRow>, String> {
        let conn = self.conn.lock().unwrap();
        let mut rows = Self::active_rows(&conn)?;
        let cutoff = recent_use_cutoff();
        rows.sort_by(|a, b| {
            b.effective_salience(&cutoff)
                .partial_cmp(&a.effective_salience(&cutoff))
                .unwrap_or(std::cmp::Ordering::Equal)
                .then_with(|| a.created_at.cmp(&b.created_at))
        });
        Ok(rows)
    }

    fn active_rows(conn: &Connection) -> Result<Vec<SkillRow>, String> {
        let mut stmt = conn
            .prepare("SELECT * FROM skills WHERE status = 'active' ORDER BY id")
            .map_err(|e| format!("Failed to prepare active-skills query: {e}"))?;
        let rows = stmt
            .query_map([], SkillRow::from_row)
            .map_err(|e| format!("Failed to query skills: {e}"))?
            .filter_map(|r| r.ok())
            .collect();
        Ok(rows)
    }

    /// Record that a skill was invoked: bump the usage counter, stamp
    /// `last_used_at`, and reinforce salience — being reached for IS the
    /// relevance signal, same as a memory `expand`. Capped at 1.0.
    pub fn invoke(&self, name: &str) -> Result<SkillRow, String> {
        let conn = self.conn.lock().unwrap();
        let now = chrono::Utc::now().to_rfc3339();
        let changed = conn
            .execute(
                "UPDATE skills
                 SET use_count = use_count + 1, last_used_at = ?1,
                     salience = MIN(1.0, salience + ?2)
                 WHERE name = ?3 AND status = 'active'",
                params![now, USE_REINFORCEMENT, name],
            )
            .map_err(|e| format!("Failed to record skill invocation: {e}"))?;
        if changed == 0 {
            return Err(format!("No active skill named '{name}'"));
        }
        Self::get_locked(&conn, name)?.ok_or_else(|| format!("No active skill named '{name}'"))
    }

    /// Record a confirmed outcome for a skill (dirge-ygm3's review pass
    /// is the intended caller). Success bumps `success_count` and stamps
    /// `last_success_at`; failure bumps `failure_count`. Feeds the
    /// effectiveness term so a skill that keeps working outranks one that
    /// keeps failing.
    pub fn record_outcome(&self, name: &str, success: bool) -> Result<SkillRow, String> {
        let conn = self.conn.lock().unwrap();
        let now = chrono::Utc::now().to_rfc3339();
        let changed = if success {
            conn.execute(
                "UPDATE skills
                 SET success_count = success_count + 1, last_success_at = ?1
                 WHERE name = ?2 AND status = 'active'",
                params![now, name],
            )
        } else {
            conn.execute(
                "UPDATE skills SET failure_count = failure_count + 1
                 WHERE name = ?1 AND status = 'active'",
                params![name],
            )
        }
        .map_err(|e| format!("Failed to record skill outcome: {e}"))?;
        if changed == 0 {
            return Err(format!("No active skill named '{name}'"));
        }
        Self::get_locked(&conn, name)?.ok_or_else(|| format!("No active skill named '{name}'"))
    }

    /// Full-text search over active skills, BM25-ranked. Ties break by
    /// proven effectiveness, then salience, then confidence, then
    /// recency — the same ordering the memory search uses, minus the
    /// procedural CASE (every skill carries the outcome signal).
    pub fn search(&self, query: &str) -> Result<Vec<SkillRow>, String> {
        let fts_query = crate::extras::fts::quote_terms(query);
        if fts_query.is_empty() {
            return Ok(Vec::new());
        }
        let conn = self.conn.lock().unwrap();
        let mut stmt = conn
            .prepare(
                "SELECT s.* FROM skills_fts
                 JOIN skills s ON s.id = skills_fts.rowid
                 WHERE skills_fts MATCH ?1 AND s.status = 'active'
                 ORDER BY rank,
                          (s.success_count - s.failure_count) DESC,
                          s.salience DESC, s.confidence DESC,
                          s.last_used_at DESC
                 LIMIT ?2",
            )
            .map_err(|e| format!("Failed to prepare skill search: {e}"))?;
        let rows = stmt
            .query_map(params![fts_query, SEARCH_RESULT_LIMIT as i64], |r| {
                SkillRow::from_row(r)
            })
            .map_err(|e| format!("Failed to search skills: {e}"))?
            .filter_map(|r| r.ok())
            .collect();
        Ok(rows)
    }

    /// Decay the salience of stale, unconsulted, unpinned skills — the
    /// curator's mechanical pass (dirge-izju). Mirrors the memory decay:
    /// floor at [`DECAY_FLOOR`], and a skill still working within the
    /// window (`last_success_at >= cutoff`) is exempt so proven
    /// effectiveness outranks mere recency. Pinned (file) skills never
    /// decay. Returns how many rows changed.
    pub fn apply_disuse_decay(&self, cutoff_days: i64) -> Result<usize, String> {
        let cutoff = (chrono::Utc::now() - chrono::Duration::days(cutoff_days)).to_rfc3339();
        let conn = self.conn.lock().unwrap();
        conn.execute(
            "UPDATE skills
             SET salience = MAX(?1, salience - ?2)
             WHERE status = 'active'
               AND pinned = 0
               AND NOT (last_success_at IS NOT NULL AND last_success_at >= ?3)
               AND created_at < ?3
               AND (last_used_at IS NULL OR last_used_at < ?3)
               AND salience > ?1",
            params![DECAY_FLOOR, DISUSE_DECAY, cutoff],
        )
        .map_err(|e| format!("Failed to apply skill disuse decay: {e}"))
    }

    /// Set a skill's raw salience directly. Test seam for simulating a
    /// decayed skill without waiting out the decay window.
    #[cfg(test)]
    pub fn set_salience_for_test(&self, name: &str, salience: f64) {
        let conn = self.conn.lock().unwrap();
        let _ = conn.execute(
            "UPDATE skills SET salience = ?1 WHERE name = ?2",
            params![salience, name],
        );
    }

    /// Archive a learned skill (soft state — never a hard delete, so it
    /// stays restorable and auditable like a memory tombstone). Pinned
    /// (file) skills are refused: they're git-tracked, so removal belongs
    /// in the repo, not the curator. Returns whether a row changed.
    pub fn archive(&self, name: &str) -> Result<bool, String> {
        let conn = self.conn.lock().unwrap();
        let now = chrono::Utc::now().to_rfc3339();
        let changed = conn
            .execute(
                "UPDATE skills SET status = 'archived', updated_at = ?1
                 WHERE name = ?2 AND status = 'active' AND pinned = 0",
                params![now, name],
            )
            .map_err(|e| format!("Failed to archive skill: {e}"))?;
        Ok(changed > 0)
    }

    /// Archive regardless of the pinned flag, and clear the pin. Used by
    /// the explicit force-delete path (dirge-8gdv.6): once the on-disk
    /// directory has been moved to the archive, the row must not stay
    /// active — a pinned row would survive plain [`archive`] and re-enter
    /// the curator pointing at a directory that no longer exists. Clearing
    /// pinned keeps directory-gone implying row-not-active.
    pub fn force_archive(&self, name: &str) -> Result<bool, String> {
        let conn = self.conn.lock().unwrap();
        let now = chrono::Utc::now().to_rfc3339();
        let changed = conn
            .execute(
                "UPDATE skills SET status = 'archived', pinned = 0, updated_at = ?1
                 WHERE name = ?2 AND status = 'active'",
                params![now, name],
            )
            .map_err(|e| format!("Failed to force-archive skill: {e}"))?;
        Ok(changed > 0)
    }
}

/// The RFC3339 cutoff before which a use no longer counts as "recent".
fn recent_use_cutoff() -> String {
    (chrono::Utc::now() - chrono::Duration::days(RECENT_USE_WINDOW_DAYS)).to_rfc3339()
}

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

    fn store() -> SkillStore {
        let conn = Connection::open_in_memory().expect("open in-memory db");
        // The skills tables live outside the version ladder; create them
        // directly the way `ensure_skills_tables` does on a real open.
        conn.execute_batch(
            "CREATE TABLE skills (
                 id INTEGER PRIMARY KEY AUTOINCREMENT,
                 uid TEXT NOT NULL UNIQUE, name TEXT NOT NULL UNIQUE,
                 description TEXT NOT NULL, content TEXT NOT NULL,
                 source TEXT NOT NULL DEFAULT 'learned',
                 skill_path TEXT,
                 status TEXT NOT NULL DEFAULT 'active',
                 tier TEXT NOT NULL DEFAULT 'hot',
                 pinned INTEGER NOT NULL DEFAULT 0,
                 confidence REAL NOT NULL DEFAULT 0.6,
                 salience REAL NOT NULL DEFAULT 0.5,
                 created_at TEXT NOT NULL, updated_at TEXT NOT NULL,
                 last_used_at TEXT, last_viewed_at TEXT, last_patched_at TEXT,
                 use_count INTEGER NOT NULL DEFAULT 0,
                 view_count INTEGER NOT NULL DEFAULT 0,
                 patch_count INTEGER NOT NULL DEFAULT 0,
                 success_count INTEGER NOT NULL DEFAULT 0,
                 failure_count INTEGER NOT NULL DEFAULT 0,
                 last_success_at TEXT, superseded_by TEXT, superseded_at TEXT);
             CREATE VIRTUAL TABLE skills_fts USING fts5(content);",
        )
        .expect("create skills tables");
        SkillStore::from_connection(conn).expect("build store")
    }

    #[test]
    fn create_and_get_roundtrip() {
        let s = store();
        let row = s
            .create(
                "deploy-web",
                "Deploy the web app to staging.",
                "# Deploy\n\nRun the deploy script.",
                SkillSource::Learned,
                None,
            )
            .expect("create");
        assert_eq!(row.name, "deploy-web");
        assert_eq!(row.source, "learned");
        assert!(!row.pinned);
        assert!((row.salience - SKILL_BASE_SALIENCE).abs() < 1e-9);
        let fetched = s.get("deploy-web").expect("get").expect("some");
        assert_eq!(fetched.content, "# Deploy\n\nRun the deploy script.");
    }

    #[test]
    fn duplicate_name_is_rejected() {
        let s = store();
        s.create("a-skill", "desc", "body", SkillSource::Learned, None)
            .expect("first");
        let err = s
            .create("a-skill", "other", "body2", SkillSource::Learned, None)
            .expect_err("dup rejected");
        assert!(err.contains("already exists"), "{err}");
    }

    #[test]
    fn invalid_name_is_rejected() {
        let s = store();
        assert!(
            s.create("Bad Name", "d", "b", SkillSource::Learned, None)
                .is_err()
        );
        assert!(
            s.create("-lead", "d", "b", SkillSource::Learned, None)
                .is_err()
        );
    }

    #[test]
    fn file_source_is_not_agent_created_and_unpinned() {
        let s = store();
        let row = s
            .create(
                "from-disk",
                "d",
                "b",
                SkillSource::File,
                Some("/repo/.dirge/skills/from-disk/SKILL.md"),
            )
            .expect("create file skill");
        // Provenance is orthogonal to pinning: a file skill is not
        // agent-created and not auto-pinned.
        assert!(!row.pinned);
        assert!(!s.is_agent_created("from-disk"));
        assert_eq!(
            row.skill_path.as_deref(),
            Some("/repo/.dirge/skills/from-disk/SKILL.md")
        );
        // A learned skill IS agent-created.
        s.create("mine", "d", "b", SkillSource::Learned, None)
            .expect("learned");
        assert!(s.is_agent_created("mine"));
    }

    #[test]
    fn telemetry_upserts_a_row_for_an_unregistered_skill() {
        let s = store();
        // No create() first — record_* must upsert a bare file row.
        s.record_view("ghost");
        s.record_use("ghost");
        s.record_patch("ghost");
        let row = s.get("ghost").expect("get").expect("row exists");
        assert_eq!(row.view_count, 1);
        assert_eq!(row.use_count, 1);
        assert_eq!(row.patch_count, 1);
        assert_eq!(row.source, "file");
        assert!(!s.is_agent_created("ghost"));
        // use reinforced salience; view/patch did not.
        assert!((row.salience - (SKILL_BASE_SALIENCE + USE_REINFORCEMENT)).abs() < 1e-9);
    }

    #[test]
    fn record_create_marks_agent_provenance() {
        let s = store();
        s.record_view("x"); // creates a bare file row first
        assert!(!s.is_agent_created("x"));
        s.record_create("x", "agent");
        assert!(s.is_agent_created("x"));
        // Non-agent creators don't flip provenance.
        s.record_create("y", "bundled");
        assert!(!s.is_agent_created("y"));
    }

    #[test]
    fn register_file_skill_upserts_and_indexes_for_search() {
        let s = store();
        s.register_file_skill(
            "linter",
            "Run the project linter.",
            "Invoke cargo clippy across the workspace.",
            false,
        )
        .expect("register");
        assert!(!s.is_agent_created("linter"));
        // Searchable by body after registration.
        let hits = s.search("clippy").expect("search");
        assert_eq!(hits.len(), 1);
        assert_eq!(hits[0].name, "linter");
        // Re-register refreshes content without losing usage lineage.
        s.record_use("linter");
        s.register_file_skill(
            "linter",
            "Run the linter.",
            "Now uses cargo fmt too.",
            false,
        )
        .expect("re-register");
        let row = s.get("linter").unwrap().unwrap();
        assert_eq!(row.use_count, 1, "usage lineage preserved across refresh");
        assert!(s.search("fmt").expect("search").len() == 1);
        assert!(s.search("clippy").expect("search").is_empty());
    }

    #[test]
    fn register_file_skill_reactivates_archived_skill() {
        let s = store();
        s.register_file_skill("reactivated", "desc one.", "body one.", false)
            .expect("register");
        // Bump usage/salience so we can assert lineage survives re-register.
        s.record_use("reactivated");
        let before = s.get("reactivated").unwrap().unwrap();
        assert_eq!(before.status, "active");
        let saved_created_at = before.created_at.clone();

        // Simulate the skill being archived (its dir moved into .archive/).
        {
            let conn = s.conn.lock().unwrap();
            conn.execute(
                "UPDATE skills SET status = 'archived' WHERE name = 'reactivated'",
                [],
            )
            .unwrap();
        }
        assert_eq!(
            s.get("reactivated").unwrap().unwrap().status,
            "archived",
            "sanity: status is archived before re-register"
        );

        // Directory presence is ground truth: re-discovering the skill on
        // disk must reactivate it.
        s.register_file_skill("reactivated", "desc two.", "body two.", false)
            .expect("re-register");
        let after = s.get("reactivated").unwrap().unwrap();
        assert_eq!(after.status, "active", "re-register must reactivate");
        assert_eq!(after.use_count, 1, "usage lineage preserved");
        assert!(
            (after.salience - (SKILL_BASE_SALIENCE + USE_REINFORCEMENT)).abs() < 1e-9,
            "salience lineage preserved"
        );
        assert_eq!(
            after.created_at, saved_created_at,
            "created_at preserved across re-register"
        );
    }

    #[test]
    fn set_pinned_toggles_the_flag() {
        let s = store();
        s.create("s", "d", "b", SkillSource::Learned, None)
            .expect("create");
        assert!(!s.get("s").unwrap().unwrap().pinned);
        s.set_pinned("s", true).expect("pin");
        assert!(s.get("s").unwrap().unwrap().pinned);
        s.set_pinned("s", false).expect("unpin");
        assert!(!s.get("s").unwrap().unwrap().pinned);
    }

    #[test]
    fn invoke_reinforces_salience_and_counts_use() {
        let s = store();
        s.create("s", "d", "b", SkillSource::Learned, None)
            .expect("create");
        let after = s.invoke("s").expect("invoke");
        assert_eq!(after.use_count, 1);
        assert!((after.salience - (SKILL_BASE_SALIENCE + USE_REINFORCEMENT)).abs() < 1e-9);
        assert!(after.last_used_at.is_some());
    }

    #[test]
    fn invoke_unknown_skill_errors() {
        let s = store();
        assert!(s.invoke("nope").is_err());
    }

    #[test]
    fn record_outcome_feeds_effectiveness_ordering() {
        let s = store();
        s.create("winner", "d", "b", SkillSource::Learned, None)
            .expect("w");
        s.create("loser", "d", "b", SkillSource::Learned, None)
            .expect("l");
        for _ in 0..5 {
            s.record_outcome("winner", true).expect("success");
        }
        for _ in 0..5 {
            s.record_outcome("loser", false).expect("failure");
        }
        let ranked = s.list_active().expect("list");
        assert_eq!(ranked.first().unwrap().name, "winner");
        assert_eq!(ranked.last().unwrap().name, "loser");
        // Effective salience reflects the record: winner up, loser down.
        let cutoff = recent_use_cutoff();
        let winner = s.get("winner").unwrap().unwrap();
        let loser = s.get("loser").unwrap().unwrap();
        assert!(winner.effective_salience(&cutoff) > SKILL_BASE_SALIENCE);
        assert!(loser.effective_salience(&cutoff) < SKILL_BASE_SALIENCE);
    }

    #[test]
    fn record_outcome_only_success_stamps_last_success_at() {
        let s = store();
        s.create("s", "d", "b", SkillSource::Learned, None)
            .expect("create");
        let ok = s.record_outcome("s", true).expect("success");
        assert_eq!(ok.success_count, 1);
        assert!(ok.last_success_at.is_some());
        let bad = s.record_outcome("s", false).expect("failure");
        assert_eq!(bad.failure_count, 1);
    }

    #[test]
    fn search_finds_by_title_and_body() {
        let s = store();
        s.create(
            "postgres-backup",
            "Back up the production database.",
            "Use pg_dump nightly.",
            SkillSource::Learned,
            None,
        )
        .expect("create");
        // Match on description ("database")…
        let by_desc = s.search("database").expect("search");
        assert_eq!(by_desc.len(), 1);
        assert_eq!(by_desc[0].name, "postgres-backup");
        // …and on body ("pg_dump").
        let by_body = s.search("pg_dump").expect("search");
        assert_eq!(by_body.len(), 1);
    }

    #[test]
    fn create_is_atomic_skills_row_and_fts_projection_both_exist() {
        // Regression guard for the bare-autocommit write path (dirge-if4v):
        // a successful create must leave BOTH the skills row and its
        // skills_fts projection, so the skill is immediately searchable.
        // The two inserts now run in one transaction, so a failure between
        // them rolls both back rather than stranding an unsearchable row.
        let s = store();
        let row = s
            .create(
                "deploy-cache",
                "Flush and warm the edge cache after a deploy.",
                "Run cachectl purge --all then cachectl prefetch sitemap.",
                SkillSource::Learned,
                None,
            )
            .expect("create");
        assert_eq!(row.name, "deploy-cache");
        assert!(s.get("deploy-cache").expect("get").is_some());
        let by_desc = s.search("edge").expect("search");
        assert_eq!(by_desc.len(), 1);
        assert_eq!(by_desc[0].name, "deploy-cache");
        let by_body = s.search("cachectl").expect("search");
        assert_eq!(by_body.len(), 1);
        assert_eq!(by_body[0].name, "deploy-cache");
    }

    #[test]
    fn search_orders_effective_first() {
        let s = store();
        s.create(
            "plain",
            "handles widgets",
            "widget body",
            SkillSource::Learned,
            None,
        )
        .expect("plain");
        s.create(
            "proven",
            "handles widgets",
            "widget body",
            SkillSource::Learned,
            None,
        )
        .expect("proven");
        for _ in 0..3 {
            s.record_outcome("proven", true).expect("ok");
        }
        let hits = s.search("widget").expect("search");
        assert_eq!(hits.len(), 2);
        assert_eq!(hits[0].name, "proven", "proven track record ranks first");
    }

    #[test]
    fn disuse_decay_lowers_stale_unpinned_salience_with_floor() {
        let s = store();
        s.create("stale", "d", "b", SkillSource::Learned, None)
            .expect("create");
        // Backdate creation so it's older than the cutoff window.
        {
            let conn = s.conn.lock().unwrap();
            conn.execute(
                "UPDATE skills SET created_at = '2000-01-01T00:00:00Z' WHERE name = 'stale'",
                [],
            )
            .unwrap();
        }
        let changed = s.apply_disuse_decay(14).expect("decay");
        assert_eq!(changed, 1);
        let after = s.get("stale").unwrap().unwrap();
        assert!((after.salience - (SKILL_BASE_SALIENCE - DISUSE_DECAY)).abs() < 1e-9);
    }

    #[test]
    fn disuse_decay_exempts_recently_successful_and_pinned() {
        let s = store();
        s.create("proven", "d", "b", SkillSource::Learned, None)
            .expect("proven");
        s.create("pinned", "d", "b", SkillSource::Learned, None)
            .expect("pinned");
        s.set_pinned("pinned", true).expect("pin");
        // Both backdated; proven has a fresh success, pinned is user-pinned.
        {
            let conn = s.conn.lock().unwrap();
            conn.execute("UPDATE skills SET created_at = '2000-01-01T00:00:00Z'", [])
                .unwrap();
        }
        s.record_outcome("proven", true).expect("recent success");
        let changed = s.apply_disuse_decay(14).expect("decay");
        assert_eq!(
            changed, 0,
            "recently-successful and pinned skills are exempt"
        );
    }

    #[test]
    fn archive_soft_removes_learned_but_refuses_pinned() {
        let s = store();
        s.create("learned", "d", "b", SkillSource::Learned, None)
            .expect("learned");
        s.create("filed", "d", "b", SkillSource::Learned, None)
            .expect("filed");
        s.set_pinned("filed", true).expect("pin");
        assert!(s.archive("learned").expect("archive learned"));
        assert_eq!(s.get("learned").unwrap().unwrap().status, "archived");
        assert!(!s.archive("filed").expect("refuse pinned"));
        assert_eq!(s.get("filed").unwrap().unwrap().status, "active");
        // Archived skills drop out of the active listing.
        let active = s.list_active().expect("list");
        assert!(active.iter().all(|r| r.name != "learned"));
    }

    #[test]
    fn force_archive_removes_a_pinned_row_and_clears_the_pin() {
        // dirge-8gdv.6: a force-delete of a pinned skill moves the
        // directory, so the row must not stay active — otherwise it
        // re-enters the curator pointing at a directory that's gone.
        let s = store();
        s.create("filed", "d", "b", SkillSource::Learned, None)
            .expect("filed");
        s.set_pinned("filed", true).expect("pin");
        // The pinned guard makes plain archive a no-op here.
        assert!(!s.archive("filed").expect("plain archive refuses pinned"));
        assert_eq!(s.get("filed").unwrap().unwrap().status, "active");
        // force_archive archives regardless and clears the pin.
        assert!(s.force_archive("filed").expect("force archive"));
        let row = s.get("filed").unwrap().unwrap();
        assert_eq!(row.status, "archived");
        assert!(!row.pinned);
        let active = s.list_active().expect("list");
        assert!(active.iter().all(|r| r.name != "filed"));
    }
}