use crate::parser::Triple;
use rusqlite::{params, Connection};
use sha2::{Digest, Sha256};
use std::path::Path;

pub struct Store {
    conn: Connection,
}

/// Schema migrations.  Each entry is a one-way step that brings the DB from
/// `user_version = i` to `i + 1`.  Append, never edit — once a migration has
/// shipped, treat it as immutable history.  Idempotent operations only:
/// migrations may run on a fresh DB or on an upgrading DB that already
/// happens to have some columns from a previous schema-on-every-open era.
type Migration = fn(&Connection) -> rusqlite::Result<()>;
const MIGRATIONS: &[Migration] = &[migrate_v1, migrate_v2, migrate_v3, migrate_v4];

impl Store {
    pub fn open(db_path: &Path) -> Result<Store, String> {
        // Ensure parent directory exists
        if let Some(parent) = db_path.parent() {
            std::fs::create_dir_all(parent)
                .map_err(|e| format!("Could not create DB directory: {e}"))?;
        }

        let conn =
            Connection::open(db_path).map_err(|e| format!("Could not open database: {e}"))?;

        // Connection PRAGMAs.  journal_mode=WAL and synchronous=NORMAL are
        // persistent (set on the database file once); temp_store=MEMORY and
        // foreign_keys=ON are per-connection.  Issuing all four every open is
        // cheap (a metadata read for the persistent ones) and keeps the open
        // path self-healing if the DB file was reset.
        conn.execute_batch(
            "PRAGMA journal_mode = WAL;
             PRAGMA synchronous = NORMAL;
             PRAGMA temp_store = MEMORY;
             PRAGMA foreign_keys = ON;",
        )
        .map_err(|e| format!("Could not set PRAGMAs: {e}"))?;

        let store = Store { conn };
        store
            .run_migrations()
            .map_err(|e| format!("Could not run migrations: {e}"))?;
        Ok(store)
    }

    /// Run any pending migrations.  Reads `PRAGMA user_version`, runs the
    /// migrations indexed `[current..target)`, then bumps `user_version` to
    /// the target.  No-op when current == target — this is the cheap path on
    /// every hook invocation.
    ///
    /// **Crash recovery semantics.**  The migration body and the
    /// `user_version` bump are NOT wrapped in a single transaction (PRAGMAs
    /// can't participate in a user txn).  If the process crashes between
    /// the migration body succeeding and the `user_version` bump, the next
    /// open re-runs the migration.  This is safe because every migration
    /// body is idempotent: `CREATE TABLE IF NOT EXISTS`, `add_column_if_missing`
    /// (which checks `PRAGMA table_info` first), `CREATE INDEX IF NOT EXISTS`,
    /// `INSERT OR IGNORE`.  Future migrations MUST preserve this — see the
    /// existing pattern in `migrate_v1` / `migrate_v2`.
    fn run_migrations(&self) -> rusqlite::Result<()> {
        let current: i64 = self
            .conn
            .query_row("PRAGMA user_version", [], |r| r.get(0))?;
        let target = MIGRATIONS.len() as i64;

        if current >= target {
            return Ok(());
        }

        for i in current..target {
            MIGRATIONS[i as usize](&self.conn)?;
        }

        // PRAGMAs cannot be parameterised; target is derived from the const
        // array length so there is no injection surface.
        self.conn
            .execute_batch(&format!("PRAGMA user_version = {};", target))?;
        Ok(())
    }

    /// Upsert a file and its triples. Skips if checksum unchanged.
    pub fn upsert_file(
        &self,
        path: &str,
        content: &str,
        triples: &[Triple],
        source_type: &str,
    ) -> rusqlite::Result<bool> {
        let checksum = compute_checksum(content);

        // Check if file exists with same checksum
        let existing_checksum: Option<String> = self
            .conn
            .query_row(
                "SELECT checksum FROM files WHERE path = ?1",
                params![path],
                |row| row.get(0),
            )
            .ok();

        if existing_checksum.as_deref() == Some(&checksum) {
            return Ok(false); // No change
        }

        let tx = self.conn.unchecked_transaction()?;

        // Get or create file_id
        tx.execute(
            "INSERT INTO files (path, checksum, scanned_at)
             VALUES (?1, ?2, datetime('now'))
             ON CONFLICT(path) DO UPDATE SET checksum = ?2, scanned_at = datetime('now')",
            params![path, checksum],
        )?;

        let file_id: i64 = tx.query_row(
            "SELECT id FROM files WHERE path = ?1",
            params![path],
            |row| row.get(0),
        )?;

        // Delete old triples for this file (triggers handle FTS cleanup)
        tx.execute("DELETE FROM triples WHERE file_id = ?1", params![file_id])?;

        // Insert new triples
        for triple in triples {
            // Serialise Tier to its string name for storage (e.g. "Strict").
            // NULL in the DB means Peer (backward-compat: older rows without
            // this column are read as Peer).
            let tier_str: Option<String> = triple
                .tier
                .as_ref()
                .map(|t| format!("{t:?}").to_lowercase());
            tx.execute(
                "INSERT INTO triples (file_id, s, p, o, domain, confidence, line_start, line_end, source_file, layer, expires_at, noenrich, tier, source_label)
                 VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12, ?13, ?14)",
                params![
                    file_id,
                    triple.subject,
                    triple.predicate,
                    triple.object,
                    format!("memory.{source_type}"),
                    triple.confidence,
                    triple.line_start,
                    triple.line_end,
                    path,
                    triple.layer.map(|l| l as i64),
                    triple.expires_at,
                    triple.noenrich as i32,
                    tier_str,
                    triple.source_label,
                ],
            )?;
        }

        tx.commit()?;
        Ok(true)
    }

    /// Load all actionable guardrails, ordered by confidence.  Rules with an
    /// `expires_at` date in the past are filtered out — they self-prune
    /// without any manual cleanup.
    ///
    /// LEFT JOIN against `rule_intent` so each guardrail carries its
    /// classified intent (or `None` if unclassified) in a single query.  The
    /// hot path used to call `get_rule_intent` per matched rule — that's now
    /// a field access.
    pub fn load_guardrails(&self) -> rusqlite::Result<Vec<Guardrail>> {
        // `NOT EXISTS` filter excludes rules whose (s,p,o) appears in
        // `disabled_rules`.  `arai diff` and `rules_for_file` deliberately
        // skip this filter — disabled rules still belong to their source
        // file, they just don't fire on the hot path.
        let mut stmt = self.conn.prepare(&format!(
            "{} AND (t.expires_at IS NULL OR t.expires_at >= date('now')) \
             AND NOT EXISTS ( \
                SELECT 1 FROM disabled_rules d \
                WHERE d.s = t.s AND d.p = t.p AND d.o = t.o \
             ) \
             ORDER BY t.confidence DESC",
            GUARDRAIL_SELECT_PREDICATE_FILTERED
        ))?;

        let guardrails = stmt
            .query_map([], guardrail_from_row)?
            .collect::<Result<Vec<_>, _>>()?;

        Ok(guardrails)
    }

    /// Mark a rule (by content) as disabled — `load_guardrails` will skip it
    /// on every subsequent read.  Returns `true` if a new row was inserted,
    /// `false` if the rule was already disabled (idempotent).
    pub fn disable_rule(&self, s: &str, p: &str, o: &str) -> rusqlite::Result<bool> {
        let n = self.conn.execute(
            "INSERT OR IGNORE INTO disabled_rules (s, p, o) VALUES (?1, ?2, ?3)",
            params![s, p, o],
        )?;
        Ok(n > 0)
    }

    /// Re-enable a rule by deleting its `disabled_rules` row.  Returns `true`
    /// if a row was deleted, `false` if the rule wasn't disabled.
    pub fn enable_rule(&self, s: &str, p: &str, o: &str) -> rusqlite::Result<bool> {
        let n = self.conn.execute(
            "DELETE FROM disabled_rules WHERE s = ?1 AND p = ?2 AND o = ?3",
            params![s, p, o],
        )?;
        Ok(n > 0)
    }

    /// List currently disabled rules `(s, p, o, disabled_at)` — surfaced by
    /// `arai disable` (no args) so users can audit what's been silenced.
    pub fn list_disabled_rules(&self) -> rusqlite::Result<Vec<(String, String, String, String)>> {
        let mut stmt = self
            .conn
            .prepare("SELECT s, p, o, disabled_at FROM disabled_rules ORDER BY disabled_at DESC")?;
        let rows = stmt
            .query_map([], |row| {
                Ok((row.get(0)?, row.get(1)?, row.get(2)?, row.get(3)?))
            })?
            .collect::<Result<Vec<_>, _>>()?;
        Ok(rows)
    }

    /// Look up a guardrail by triple_id.  Used by `arai disable <id>` to
    /// resolve a stable display id to the (s,p,o) tuple that
    /// `disable_rule` stores.  Includes disabled rules so `arai enable <id>`
    /// can find them.
    pub fn rule_by_triple_id(
        &self,
        triple_id: i64,
    ) -> rusqlite::Result<Option<(String, String, String)>> {
        match self.conn.query_row(
            "SELECT s, p, o FROM triples WHERE id = ?1",
            params![triple_id],
            |row| Ok((row.get(0)?, row.get(1)?, row.get(2)?)),
        ) {
            Ok(t) => Ok(Some(t)),
            Err(rusqlite::Error::QueryReturnedNoRows) => Ok(None),
            Err(e) => Err(e),
        }
    }

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

    pub fn guardrail_count(&self) -> rusqlite::Result<i64> {
        self.conn.query_row(
            "SELECT COUNT(*) FROM triples WHERE p IN ('forbids','must_not','never','always','requires','enforces','prefers')",
            [],
            |row| row.get::<_, i64>(0),
        )
    }

    /// Count rules registered via the MCP server (`arai_add_guard`).  Used to
    /// bound the number of MCP-source rules so a runaway agent can't fill the
    /// store.  MCP rules live under synthetic file paths starting with
    /// `manual://arai-mcp/` (see `mcp::tool_add_guard`).
    pub fn count_mcp_rules(&self) -> rusqlite::Result<i64> {
        self.conn.query_row(
            "SELECT COUNT(*) FROM triples t \
             JOIN files f ON f.id = t.file_id \
             WHERE f.path LIKE 'manual://arai-mcp/%'",
            [],
            |row| row.get::<_, i64>(0),
        )
    }

    pub fn get_meta(&self, key: &str) -> rusqlite::Result<Option<String>> {
        self.conn
            .query_row(
                "SELECT value FROM meta WHERE key = ?1",
                params![key],
                |row| row.get(0),
            )
            .or_else(|e| match e {
                rusqlite::Error::QueryReturnedNoRows => Ok(None),
                _ => Err(e),
            })
    }

    pub fn set_meta(&self, key: &str, value: &str) -> rusqlite::Result<()> {
        self.conn.execute(
            "INSERT INTO meta (key, value) VALUES (?1, ?2)
             ON CONFLICT(key) DO UPDATE SET value = ?2",
            params![key, value],
        )?;
        Ok(())
    }

    // --- Code graph methods ---

    /// Bulk upsert code graph entries from a project scan.
    pub fn upsert_code_graph(
        &self,
        imports: &[crate::code_scanner::ImportInfo],
    ) -> rusqlite::Result<()> {
        let tx = self.conn.unchecked_transaction()?;

        // Clear existing entries
        tx.execute("DELETE FROM code_graph", [])?;

        for import in imports {
            tx.execute(
                "INSERT OR IGNORE INTO code_graph (directory, tool_name, source_file, scanned_at)
                 VALUES (?1, ?2, ?3, datetime('now'))",
                params![import.directory, import.tool_name, import.source_file],
            )?;
        }

        tx.commit()
    }

    /// Query what tools are imported by files in a given directory.
    pub fn query_tools_for_directory(&self, directory: &str) -> rusqlite::Result<Vec<String>> {
        let mut stmt = self
            .conn
            .prepare("SELECT DISTINCT tool_name FROM code_graph WHERE directory = ?1")?;
        let tools = stmt
            .query_map(params![directory], |row| row.get(0))?
            .collect::<Result<Vec<String>, _>>()?;
        Ok(tools)
    }

    /// Query tools for a file path by checking its parent directories.
    /// Walks up from the file's directory to find tools used by sibling files.
    pub fn query_tools_for_path(&self, file_path: &str) -> rusqlite::Result<Vec<String>> {
        let path = std::path::Path::new(file_path);
        let mut tools = Vec::new();

        // Check the file's own directory and up to 2 parent levels
        let mut dir = path.parent();
        for _ in 0..3 {
            if let Some(d) = dir {
                let dir_str = d.to_string_lossy();
                let mut dir_tools = self.query_tools_for_directory(&dir_str)?;
                tools.append(&mut dir_tools);
                dir = d.parent();
            } else {
                break;
            }
        }

        tools.sort();
        tools.dedup();
        Ok(tools)
    }

    /// Get the count of unique tools in the code graph.
    pub fn code_graph_tool_count(&self) -> rusqlite::Result<i64> {
        self.conn.query_row(
            "SELECT COUNT(DISTINCT tool_name) FROM code_graph",
            [],
            |row| row.get::<_, i64>(0),
        )
    }

    /// Get the count of files scanned for the code graph.
    pub fn code_graph_file_count(&self) -> rusqlite::Result<i64> {
        self.conn.query_row(
            "SELECT COUNT(DISTINCT source_file) FROM code_graph",
            [],
            |row| row.get::<_, i64>(0),
        )
    }

    // --- Rule intent methods ---

    /// Store classified intent for a triple.
    pub fn upsert_rule_intent(
        &self,
        triple_id: i64,
        intent: &crate::intent::RuleIntent,
    ) -> rusqlite::Result<()> {
        let tools_json =
            serde_json::to_string(&intent.tools).unwrap_or_else(|_| "[\"*\"]".to_string());
        self.conn.execute(
            "INSERT INTO rule_intent (triple_id, action, timing, tools, allow_inverse, enriched_by, enriched_at, severity)
             VALUES (?1, ?2, ?3, ?4, ?5, ?6, datetime('now'), ?7)
             ON CONFLICT(triple_id) DO UPDATE SET
                action = ?2, timing = ?3, tools = ?4, allow_inverse = ?5, enriched_by = ?6, enriched_at = datetime('now'), severity = ?7",
            params![
                triple_id,
                intent.action.as_str(),
                intent.timing.as_str(),
                tools_json,
                intent.allow_inverse as i32,
                intent.enriched_by,
                intent.severity.as_str(),
            ],
        )?;
        Ok(())
    }

    /// Get the intent for a specific triple.
    ///
    /// If a `severity_override` row is present, it takes precedence over the
    /// classified severity — that's the surface `arai severity` writes to so
    /// re-running `arai scan` doesn't stomp a manual rollout decision.
    pub fn get_rule_intent(
        &self,
        triple_id: i64,
    ) -> rusqlite::Result<Option<crate::intent::RuleIntent>> {
        match self.conn.query_row(
            "SELECT action, timing, tools, allow_inverse, enriched_by, severity, severity_override FROM rule_intent WHERE triple_id = ?1",
            params![triple_id],
            |row| {
                let action_str: String = row.get(0)?;
                let timing_str: String = row.get(1)?;
                let tools_json: String = row.get(2)?;
                let allow_inverse: i32 = row.get(3)?;
                let enriched_by: String = row.get(4)?;
                let severity_str: String = row.get::<_, Option<String>>(5)?.unwrap_or_else(|| "warn".to_string());
                let severity_override: Option<String> = row.get(6)?;

                let tools: Vec<String> = serde_json::from_str(&tools_json)
                    .unwrap_or_else(|_| vec!["*".to_string()]);

                let effective = severity_override
                    .as_deref()
                    .map(crate::intent::Severity::from_str)
                    .unwrap_or_else(|| crate::intent::Severity::from_str(&severity_str));

                Ok(crate::intent::RuleIntent {
                    action: crate::intent::Action::from_str(&action_str),
                    timing: crate::intent::Timing::from_str(&timing_str),
                    tools,
                    allow_inverse: allow_inverse != 0,
                    enriched_by,
                    severity: effective,
                })
            },
        ) {
            Ok(intent) => Ok(Some(intent)),
            Err(rusqlite::Error::QueryReturnedNoRows) => Ok(None),
            Err(e) => Err(e),
        }
    }

    /// Pin a rule's severity, surviving re-classification by `arai scan`.
    /// Matches against the rule's subject or object via case-insensitive
    /// substring; returns the rules that were updated (subject/predicate/
    /// object/old/new) so the caller can echo the change.
    ///
    /// Empty pattern matches every active guardrail — the CLI surface should
    /// reject empty input rather than rely on this; we don't here so unit
    /// tests can simulate "set everything" without scaffolding.
    pub fn set_severity_override(
        &self,
        pattern: &str,
        severity: crate::intent::Severity,
    ) -> rusqlite::Result<Vec<SeverityChange>> {
        let needle = pattern.to_lowercase();
        let guardrails = self.load_guardrails()?;
        let mut changed = Vec::new();
        for g in guardrails {
            let matches = needle.is_empty()
                || g.subject.to_lowercase().contains(&needle)
                || g.object.to_lowercase().contains(&needle);
            if !matches {
                continue;
            }

            // Read current effective severity so the caller can show the
            // delta in a single round-trip.
            let prev = self
                .get_rule_intent(g.triple_id)?
                .map(|i| i.severity)
                .unwrap_or_else(|| crate::intent::Severity::from_predicate(&g.predicate));

            // Ensure a row exists in rule_intent before setting the override —
            // a brand-new store may not have been classified yet.
            self.conn.execute(
                "INSERT OR IGNORE INTO rule_intent (triple_id, severity_override) VALUES (?1, ?2)",
                params![g.triple_id, severity.as_str()],
            )?;
            self.conn.execute(
                "UPDATE rule_intent SET severity_override = ?2 WHERE triple_id = ?1",
                params![g.triple_id, severity.as_str()],
            )?;

            changed.push(SeverityChange {
                triple_id: g.triple_id,
                subject: g.subject,
                predicate: g.predicate,
                object: g.object,
                source: g.file_path,
                from: prev,
                to: severity,
            });
        }
        Ok(changed)
    }

    /// Drop a severity override; the rule reverts to its predicate-derived
    /// classification.  Returns the rules whose override was cleared.
    pub fn clear_severity_override(&self, pattern: &str) -> rusqlite::Result<Vec<SeverityChange>> {
        let needle = pattern.to_lowercase();
        let guardrails = self.load_guardrails()?;
        let mut cleared = Vec::new();
        for g in guardrails {
            let matches = needle.is_empty()
                || g.subject.to_lowercase().contains(&needle)
                || g.object.to_lowercase().contains(&needle);
            if !matches {
                continue;
            }

            // Only clear rows that actually have an override — otherwise we
            // misreport "cleared 50 rules" when nothing was pinned.
            let had: Option<String> = self
                .conn
                .query_row(
                    "SELECT severity_override FROM rule_intent WHERE triple_id = ?1",
                    params![g.triple_id],
                    |row| row.get(0),
                )
                .ok()
                .flatten();
            if had.is_none() {
                continue;
            }

            self.conn.execute(
                "UPDATE rule_intent SET severity_override = NULL WHERE triple_id = ?1",
                params![g.triple_id],
            )?;

            let from = had
                .as_deref()
                .map(crate::intent::Severity::from_str)
                .unwrap_or_else(|| crate::intent::Severity::from_predicate(&g.predicate));
            let to = crate::intent::Severity::from_predicate(&g.predicate);
            cleared.push(SeverityChange {
                triple_id: g.triple_id,
                subject: g.subject,
                predicate: g.predicate,
                object: g.object,
                source: g.file_path,
                from,
                to,
            });
        }
        Ok(cleared)
    }

    /// List every active rule that carries a severity override.
    pub fn list_severity_overrides(&self) -> rusqlite::Result<Vec<SeverityChange>> {
        let mut stmt = self.conn.prepare(
            "SELECT t.id, t.s, t.p, t.o, f.path, ri.severity_override
             FROM triples t
             JOIN files f ON t.file_id = f.id
             JOIN rule_intent ri ON ri.triple_id = t.id
             WHERE ri.severity_override IS NOT NULL
               AND t.p IN ('forbids','must_not','never','always','requires','enforces','prefers')
               AND (t.expires_at IS NULL OR t.expires_at >= date('now'))
             ORDER BY t.s, t.p, t.o",
        )?;
        let rows = stmt.query_map([], |row| {
            let triple_id: i64 = row.get(0)?;
            let subject: String = row.get(1)?;
            let predicate: String = row.get(2)?;
            let object: String = row.get(3)?;
            let source: String = row.get(4)?;
            let override_str: String = row.get(5)?;
            let from = crate::intent::Severity::from_predicate(&predicate);
            let to = crate::intent::Severity::from_str(&override_str);
            Ok(SeverityChange {
                triple_id,
                subject,
                predicate,
                object,
                source,
                from,
                to,
            })
        })?;
        rows.collect()
    }

    /// Fetch the live SPO triples for a single source file path.  Used by
    /// `arai diff` to compare what's currently in the store against what the
    /// candidate file would extract — no `JOIN files` because we already know
    /// the path the caller passed in.
    pub fn rules_for_file(&self, file_path: &str) -> rusqlite::Result<Vec<Guardrail>> {
        let mut stmt = self.conn.prepare(&format!(
            "{} AND f.path = ?1 ORDER BY t.line_start, t.s, t.p, t.o",
            GUARDRAIL_SELECT_PREDICATE_FILTERED
        ))?;
        let guardrails = stmt
            .query_map(params![file_path], guardrail_from_row)?
            .collect::<Result<Vec<_>, _>>()?;
        Ok(guardrails)
    }

    /// Find pairs of rules that potentially conflict or duplicate.
    ///
    /// - **Duplicate**: the same (subject, predicate, object) appears in
    ///   two or more source files.  These are usually safe to consolidate
    ///   into one source to reduce CLAUDE.md drift.
    /// - **Opposing**: the same subject carries both a prohibitive
    ///   predicate (`never`, `must_not`, `avoid`) and a required predicate
    ///   (`always`, `must`, `requires`, `ensure`).  Not necessarily a real
    ///   conflict — the objects may be different — but worth a human look.
    pub fn find_rule_issues(&self) -> rusqlite::Result<RuleIssues> {
        let mut stmt = self.conn.prepare(
            "SELECT t.s, t.p, t.o, f.path FROM triples t \
             JOIN files f ON f.id = t.file_id \
             ORDER BY t.s, t.p, t.o",
        )?;
        let rows = stmt.query_map([], |r| {
            Ok((
                r.get::<_, String>(0)?,
                r.get::<_, String>(1)?,
                r.get::<_, String>(2)?,
                r.get::<_, String>(3)?,
            ))
        })?;

        let mut by_spo: std::collections::BTreeMap<(String, String, String), Vec<String>> =
            std::collections::BTreeMap::new();
        let mut by_subject: std::collections::BTreeMap<String, Vec<(String, String)>> =
            std::collections::BTreeMap::new();

        for row in rows {
            let (s, p, o, src) = row?;
            by_spo
                .entry((s.clone(), p.clone(), o.clone()))
                .or_default()
                .push(src);
            by_subject.entry(s).or_default().push((p, o));
        }

        let mut duplicates = Vec::new();
        for ((s, p, o), sources) in by_spo {
            let unique: std::collections::BTreeSet<_> = sources.iter().cloned().collect();
            if unique.len() >= 2 {
                let mut list: Vec<String> = unique.into_iter().collect();
                list.sort();
                duplicates.push(DuplicateRule {
                    subject: s,
                    predicate: p,
                    object: o,
                    sources: list,
                });
            }
        }

        let mut opposing = Vec::new();
        for (subject, entries) in by_subject {
            let has_prohibitive = entries.iter().any(|(p, _)| is_prohibitive(p));
            let has_required = entries.iter().any(|(p, _)| is_required(p));
            if has_prohibitive && has_required {
                let mut preds: Vec<String> = entries.iter().map(|(p, _)| p.clone()).collect();
                preds.sort();
                preds.dedup();
                opposing.push(OpposingRules {
                    subject,
                    predicates: preds,
                });
            }
        }

        Ok(RuleIssues {
            duplicates,
            opposing,
        })
    }

    /// Classify all existing guardrails using the taxonomy.
    pub fn classify_all_guardrails(&self) -> rusqlite::Result<usize> {
        let guardrails = self.load_guardrails()?;
        let mut count = 0;
        for g in &guardrails {
            let intent = crate::intent::classify_rule_with_subject(
                &g.predicate,
                &g.object,
                Some(&g.subject),
            );
            self.upsert_rule_intent(g.triple_id, &intent)?;
            count += 1;
        }
        Ok(count)
    }
}

/// Summary of rule-set health issues surfaced by [`Store::find_rule_issues`].
#[derive(Debug, Default, serde::Serialize)]
pub struct RuleIssues {
    pub duplicates: Vec<DuplicateRule>,
    pub opposing: Vec<OpposingRules>,
}

#[derive(Debug, serde::Serialize)]
pub struct DuplicateRule {
    pub subject: String,
    pub predicate: String,
    pub object: String,
    pub sources: Vec<String>,
}

#[derive(Debug, serde::Serialize)]
pub struct OpposingRules {
    pub subject: String,
    pub predicates: Vec<String>,
}

/// One row in the output of `set_severity_override` / `list_severity_overrides`.
/// Captures both the previous and new severity so the CLI can render a
/// diff line per rule without a second query.
#[derive(Debug, serde::Serialize)]
pub struct SeverityChange {
    pub triple_id: i64,
    pub subject: String,
    pub predicate: String,
    pub object: String,
    pub source: String,
    pub from: crate::intent::Severity,
    pub to: crate::intent::Severity,
}

fn is_prohibitive(p: &str) -> bool {
    matches!(
        p,
        "never" | "must_not" | "avoid" | "do_not" | "dont" | "forbid"
    )
}

fn is_required(p: &str) -> bool {
    matches!(
        p,
        "always" | "must" | "requires" | "ensure" | "prefer" | "should"
    )
}

/// Shared `SELECT … FROM triples … LEFT JOIN rule_intent` preamble for
/// callers that want a `Guardrail` with intent attached.  Ends mid-WHERE
/// (the predicate filter is included; callers append their own
/// `AND <extra>` and `ORDER BY`).  `LEFT JOIN` so unclassified rules still
/// surface — `intent` will be `None` on those.
///
/// Columns 0..=10: triple fields.  Columns 11..=17: intent fields.
/// Columns 18..=19: tier-provenance fields (tier, source_label).
/// NULL tier → Peer (backward-compat with pre-v4 rows).
const GUARDRAIL_SELECT_PREDICATE_FILTERED: &str =
    "SELECT t.id, t.s, t.p, t.o, t.confidence, t.source_file, f.path, t.layer, t.line_start, t.expires_at, t.noenrich,
            ri.action, ri.timing, ri.tools, ri.allow_inverse, ri.enriched_by, ri.severity, ri.severity_override,
            t.tier, t.source_label
     FROM triples t
     JOIN files f ON t.file_id = f.id
     LEFT JOIN rule_intent ri ON ri.triple_id = t.id
     WHERE t.p IN ('forbids','must_not','never','always','requires','enforces','prefers')";

/// Row mapper paired with `GUARDRAIL_SELECT_PREDICATE_FILTERED`.  Pulls
/// triple columns from indices 0..=10, intent columns from 11..=17, and
/// tier-provenance columns from 18..=19.
fn guardrail_from_row(row: &rusqlite::Row) -> rusqlite::Result<Guardrail> {
    // Deserialise the stored tier string back to a Tier variant.
    // NULL or unrecognised strings → None (Peer).
    let tier: Option<crate::extends::Tier> = row
        .get::<_, Option<String>>(18)?
        .and_then(|s| tier_from_str(&s));
    let source_label: Option<String> = row.get(19)?;
    Ok(Guardrail {
        triple_id: row.get(0)?,
        subject: row.get(1)?,
        predicate: row.get(2)?,
        object: row.get(3)?,
        confidence: row.get(4)?,
        source_file: row.get::<_, Option<String>>(5)?.unwrap_or_default(),
        file_path: row.get(6)?,
        layer: row.get::<_, Option<i64>>(7)?.map(|v| v as u8),
        line_start: row.get::<_, Option<i64>>(8)?,
        expires_at: row.get::<_, Option<String>>(9)?,
        noenrich: row.get::<_, i64>(10)? != 0,
        intent: parse_intent_from_row(row, 11)?,
        tier,
        source_label,
    })
}

/// Deserialise the stored lowercase tier string.  Returns `None` for NULL or
/// any unrecognised value — callers treat `None` as `Tier::Peer` per the
/// backward-compat invariant.
fn tier_from_str(s: &str) -> Option<crate::extends::Tier> {
    match s {
        "strict" => Some(crate::extends::Tier::Strict),
        "advisory" => Some(crate::extends::Tier::Advisory),
        "override" => Some(crate::extends::Tier::Override),
        "peer" => Some(crate::extends::Tier::Peer),
        _ => None,
    }
}

/// Parse an optional `RuleIntent` from columns starting at `start`.  Returns
/// `None` when the LEFT JOIN missed (no `rule_intent` row for the triple) —
/// detected via `action` being NULL because every populated intent has it.
fn parse_intent_from_row(
    row: &rusqlite::Row,
    start: usize,
) -> rusqlite::Result<Option<crate::intent::RuleIntent>> {
    let action_str: Option<String> = row.get(start)?;
    let Some(action_str) = action_str else {
        return Ok(None);
    };
    let timing_str: String = row.get(start + 1)?;
    let tools_json: String = row.get(start + 2)?;
    let allow_inverse: i32 = row.get(start + 3)?;
    let enriched_by: String = row.get(start + 4)?;
    let severity_str: String = row
        .get::<_, Option<String>>(start + 5)?
        .unwrap_or_else(|| "warn".to_string());
    let severity_override: Option<String> = row.get(start + 6)?;

    let tools: Vec<String> =
        serde_json::from_str(&tools_json).unwrap_or_else(|_| vec!["*".to_string()]);

    let effective = severity_override
        .as_deref()
        .map(crate::intent::Severity::from_str)
        .unwrap_or_else(|| crate::intent::Severity::from_str(&severity_str));

    Ok(Some(crate::intent::RuleIntent {
        action: crate::intent::Action::from_str(&action_str),
        timing: crate::intent::Timing::from_str(&timing_str),
        tools,
        allow_inverse: allow_inverse != 0,
        enriched_by,
        severity: effective,
    }))
}

#[derive(Debug, Clone, serde::Serialize, serde::Deserialize)]
pub struct Guardrail {
    pub triple_id: i64,
    pub subject: String,
    pub predicate: String,
    pub object: String,
    pub confidence: f64,
    pub source_file: String,
    pub file_path: String,
    /// Parser layer (1..=6) that produced this rule.  Preserved through the
    /// store so `arai audit` / `arai why` can trace a firing back to the
    /// exact regex family that extracted the rule.  `None` for rules
    /// ingested before the layer column existed, or rules added manually.
    #[serde(default, skip_serializing_if = "Option::is_none")]
    pub layer: Option<u8>,
    /// Line number in the source file where the rule was extracted.  Surfaced
    /// alongside the layer so the audit trace reads like "CLAUDE.md:42
    /// (layer-1 imperative)".  `None` for manually-added rules.
    #[serde(default, skip_serializing_if = "Option::is_none")]
    pub line_start: Option<i64>,
    /// Optional ISO date (`YYYY-MM-DD`) after which this rule is filtered
    /// out of `load_guardrails`.  Extracted at parse time from a trailing
    /// `(expires ...)` annotation; always `None` for rules without one.
    #[serde(default, skip_serializing_if = "Option::is_none")]
    pub expires_at: Option<String>,
    /// Per-rule opt-out from LLM/API enrichment.  Set by a trailing
    /// `(noenrich)` annotation in the rule text.  When `true`,
    /// `enrich::enrich_via_llm` and `enrich_via_api` filter the rule out of
    /// the prompt before it leaves the machine — useful for rules whose
    /// object mentions an internal codename the user does not want shipped
    /// to a third-party endpoint.  Default `false` preserves prior
    /// enrichment behaviour for every existing rule.
    #[serde(default, skip_serializing_if = "is_false_ref")]
    pub noenrich: bool,
    /// Classified intent.  Populated by `load_guardrails` / `rules_for_file`
    /// via a single LEFT JOIN against `rule_intent`, so the hot path no
    /// longer needs an N-way `get_rule_intent` round trip per matched rule.
    /// `None` for rules that haven't been classified yet (e.g. just added by
    /// `arai add`, or a freshly-scanned file before `arai classify` runs).
    #[serde(default, skip_serializing_if = "Option::is_none")]
    pub intent: Option<crate::intent::RuleIntent>,
    /// Tier provenance: how strongly this upstream block's rules bind.
    /// `None` means the rule is local (Peer tier, default).
    /// Set once at extraction time via the `arai:extends` path.
    #[serde(default, skip_serializing_if = "Option::is_none")]
    pub tier: Option<crate::extends::Tier>,
    /// Source provenance: the URL of the upstream block this rule came from.
    /// `None` for purely local rules.
    #[serde(default, skip_serializing_if = "Option::is_none")]
    pub source_label: Option<String>,
}

fn is_false_ref(b: &bool) -> bool {
    !b
}

fn compute_checksum(content: &str) -> String {
    let mut hasher = Sha256::new();
    hasher.update(content.as_bytes());
    let hash = hasher.finalize();
    hash.iter().map(|b| format!("{b:02x}")).collect()
}

/// Add a column only if it isn't already on the table.  SQLite has no
/// `ALTER TABLE ... ADD COLUMN IF NOT EXISTS`; the previous codepath relied
/// on swallowing duplicate-column errors via `let _ = ...`.  This helper
/// keeps migrations idempotent on user databases that may already have the
/// column from a pre-versioned-schema era.
///
/// Defensive identifier validation: today's callers pass const string
/// literals from `migrate_v1`, so the `format!()` interpolation is safe.
/// But the function is a tempting future target for refactors that pass a
/// caller-controlled string — pinning the shape here closes that future
/// SQLi window before it can open.  Panics on invalid identifiers because
/// they would only ever appear in code, never in user data.
fn add_column_if_missing(
    conn: &Connection,
    table: &str,
    column: &str,
    column_def: &str,
) -> rusqlite::Result<()> {
    assert!(
        is_safe_sql_identifier(table),
        "add_column_if_missing: table identifier {table:?} contains characters \
         outside [A-Za-z0-9_]; this is a programming error, not user input"
    );
    assert!(
        is_safe_sql_identifier(column),
        "add_column_if_missing: column identifier {column:?} contains characters \
         outside [A-Za-z0-9_]; this is a programming error, not user input"
    );
    let mut stmt = conn.prepare(&format!("PRAGMA table_info({table})"))?;
    let mut rows = stmt.query([])?;
    while let Some(row) = rows.next()? {
        let name: String = row.get(1)?;
        if name == column {
            return Ok(());
        }
    }
    // `column_def` carries the full type clause — `TEXT NOT NULL DEFAULT
    // 'foo'`.  We don't try to validate that grammar (would re-implement
    // SQLite's parser); we trust callers, who are inside this crate.
    conn.execute(
        &format!("ALTER TABLE {table} ADD COLUMN {column} {column_def}"),
        [],
    )?;
    Ok(())
}

/// Cheap allow-list check for SQL identifiers.  Matches `^[A-Za-z_][A-Za-z0-9_]*$`
/// without dragging in a regex.
fn is_safe_sql_identifier(s: &str) -> bool {
    let mut chars = s.chars();
    match chars.next() {
        Some(c) if c.is_ascii_alphabetic() || c == '_' => {}
        _ => return false,
    }
    chars.all(|c| c.is_ascii_alphanumeric() || c == '_')
}

/// Migration v0 -> v1: initial schema.  Includes the original CREATE TABLE
/// batch plus idempotent ALTERs for the four columns that were added in
/// v0.2.x via the schema-on-every-open ALTER pattern.  After this migration
/// runs, dbs from any prior version converge on the same shape.
fn migrate_v1(conn: &Connection) -> rusqlite::Result<()> {
    conn.execute_batch(
        "CREATE TABLE IF NOT EXISTS files (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            path TEXT NOT NULL UNIQUE,
            checksum TEXT NOT NULL DEFAULT '',
            mtime REAL DEFAULT 0,
            scanned_at TEXT
        );

        CREATE TABLE IF NOT EXISTS triples (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            file_id INTEGER NOT NULL REFERENCES files(id) ON DELETE CASCADE,
            s TEXT NOT NULL,
            p TEXT NOT NULL,
            o TEXT NOT NULL,
            domain TEXT DEFAULT 'general',
            confidence REAL DEFAULT 0.7,
            line_start INTEGER,
            line_end INTEGER,
            source_file TEXT,
            layer INTEGER,
            expires_at TEXT
        );

        CREATE INDEX IF NOT EXISTS idx_triples_file ON triples(file_id);
        CREATE INDEX IF NOT EXISTS idx_triples_p ON triples(p);
        CREATE INDEX IF NOT EXISTS idx_triples_s ON triples(s);

        CREATE VIRTUAL TABLE IF NOT EXISTS triples_fts USING fts5(
            s, p, o,
            content='triples',
            content_rowid='id'
        );

        CREATE TRIGGER IF NOT EXISTS triples_ai AFTER INSERT ON triples BEGIN
            INSERT INTO triples_fts(rowid, s, p, o) VALUES (new.id, new.s, new.p, new.o);
        END;

        CREATE TRIGGER IF NOT EXISTS triples_ad AFTER DELETE ON triples BEGIN
            INSERT INTO triples_fts(triples_fts, rowid, s, p, o)
                VALUES('delete', old.id, old.s, old.p, old.o);
        END;

        CREATE TABLE IF NOT EXISTS meta (
            key TEXT PRIMARY KEY,
            value TEXT
        );

        CREATE TABLE IF NOT EXISTS code_graph (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            directory TEXT NOT NULL,
            tool_name TEXT NOT NULL,
            source_file TEXT NOT NULL,
            scanned_at TEXT,
            UNIQUE(directory, tool_name, source_file)
        );

        CREATE INDEX IF NOT EXISTS idx_code_graph_dir ON code_graph(directory);

        CREATE TABLE IF NOT EXISTS rule_intent (
            triple_id INTEGER PRIMARY KEY REFERENCES triples(id) ON DELETE CASCADE,
            action TEXT NOT NULL DEFAULT 'general',
            timing TEXT NOT NULL DEFAULT 'tool_call',
            tools TEXT NOT NULL DEFAULT '*',
            allow_inverse INTEGER DEFAULT 0,
            enriched_by TEXT DEFAULT 'taxonomy',
            enriched_at TEXT,
            severity TEXT NOT NULL DEFAULT 'warn',
            severity_override TEXT
        );",
    )?;

    // Bridge for v0.2.x dbs: these columns are now in the CREATE TABLE
    // statements above, so on a fresh DB the helper is a no-op.  On an
    // upgrading DB whose tables predate one or more of these columns, the
    // helper backfills them once.
    add_column_if_missing(
        conn,
        "rule_intent",
        "severity",
        "TEXT NOT NULL DEFAULT 'warn'",
    )?;
    add_column_if_missing(conn, "rule_intent", "severity_override", "TEXT")?;
    add_column_if_missing(conn, "triples", "layer", "INTEGER")?;
    add_column_if_missing(conn, "triples", "expires_at", "TEXT")?;

    Ok(())
}

/// Migration v1 -> v2: per-rule disable list.  Survives `arai scan` because
/// it keys by the rule's content (s/p/o) — re-extracting the same imperative
/// from CLAUDE.md hits the same key, so the disable persists.  If the user
/// later edits the rule text, the new content has a fresh key and the
/// disable no longer applies — that's intentional (changed rule = re-evaluate).
fn migrate_v2(conn: &Connection) -> rusqlite::Result<()> {
    conn.execute_batch(
        "CREATE TABLE IF NOT EXISTS disabled_rules (
            s TEXT NOT NULL,
            p TEXT NOT NULL,
            o TEXT NOT NULL,
            disabled_at TEXT NOT NULL DEFAULT (datetime('now')),
            PRIMARY KEY (s, p, o)
        );",
    )
}

/// Migration v2 -> v3: per-rule LLM/API enrichment opt-out.  Backed by a
/// trailing `(noenrich)` annotation in the rule text (parsed in `parser.rs`),
/// surfaced through `Guardrail::noenrich`, and consumed by
/// `enrich::enrich_via_llm` / `enrich_via_api` to filter rules out of the
/// prompt before it leaves the machine.  Default 0 means existing rules
/// remain enrichable — opt-in semantics, never silent removal.
fn migrate_v3(conn: &Connection) -> rusqlite::Result<()> {
    add_column_if_missing(conn, "triples", "noenrich", "INTEGER NOT NULL DEFAULT 0")
}

/// Migration v3 -> v4: tier-provenance fields on triples.
///
/// Two additive nullable columns:
///
/// - `tier` TEXT: lowercase tier name ("strict"|"advisory"|"override"|"peer").
///   NULL means Peer (backward-compat: all existing rows read as Peer).
/// - `source_label` TEXT: the upstream URL the rule came from.
///   NULL for local rules.
///
/// These columns are written by `upsert_file` when rules carry provenance set
/// by the `arai:extends` resolve path, and read back by `guardrail_from_row`.
/// Existing rows with NULL values are treated as Peer/no-label — no behaviour
/// change for the current un-annotated path (backward-compat invariant AC1).
fn migrate_v4(conn: &Connection) -> rusqlite::Result<()> {
    add_column_if_missing(conn, "triples", "tier", "TEXT")?;
    add_column_if_missing(conn, "triples", "source_label", "TEXT")
}

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

    use std::sync::atomic::{AtomicU64, Ordering};
    static TEST_COUNTER: AtomicU64 = AtomicU64::new(0);

    fn temp_db() -> (Store, PathBuf) {
        let id = TEST_COUNTER.fetch_add(1, Ordering::SeqCst);
        let dir = std::env::temp_dir().join(format!("arai_test_{}_{}", std::process::id(), id));
        std::fs::create_dir_all(&dir).unwrap();
        let db_path = dir.join("test.db");
        let store = Store::open(&db_path).unwrap();
        (store, dir)
    }

    #[test]
    fn test_upsert_and_load() {
        let (store, dir) = temp_db();

        let triples = vec![Triple {
            subject: "Git".to_string(),
            predicate: "never".to_string(),
            object: "force-push to main".to_string(),
            confidence: 0.92,
            domain: "memory.claude_md_project".to_string(),
            source_file: "CLAUDE.md".to_string(),
            line_start: Some(1),
            line_end: Some(1),
            layer: None,
            expires_at: None,
            noenrich: false,

            tier: None,

            source_label: None,
        }];

        let changed = store
            .upsert_file(
                "CLAUDE.md",
                "- Never force-push to main",
                &triples,
                "claude_md_project",
            )
            .unwrap();
        assert!(changed);

        let guardrails = store.load_guardrails().unwrap();
        assert_eq!(guardrails.len(), 1);
        assert_eq!(guardrails[0].subject, "Git");
        assert_eq!(guardrails[0].predicate, "never");

        // Upsert again with same content — should skip
        let changed = store
            .upsert_file(
                "CLAUDE.md",
                "- Never force-push to main",
                &triples,
                "claude_md_project",
            )
            .unwrap();
        assert!(!changed);

        std::fs::remove_dir_all(&dir).ok();
    }

    #[test]
    fn test_find_rule_issues_detects_duplicates() {
        let (store, dir) = temp_db();

        let t = Triple {
            subject: "git".to_string(),
            predicate: "never".to_string(),
            object: "force-push to main".to_string(),
            confidence: 0.92,
            domain: "general".to_string(),
            source_file: "CLAUDE.md".to_string(),
            line_start: Some(1),
            line_end: Some(1),
            layer: None,
            expires_at: None,
            noenrich: false,

            tier: None,

            source_label: None,
        };
        store
            .upsert_file(
                "CLAUDE.md",
                "- a",
                std::slice::from_ref(&t),
                "claude_md_project",
            )
            .unwrap();
        store
            .upsert_file(
                "global.md",
                "- a",
                &[Triple {
                    source_file: "global.md".to_string(),
                    ..t.clone()
                }],
                "claude_md_global",
            )
            .unwrap();

        let issues = store.find_rule_issues().unwrap();
        assert_eq!(issues.duplicates.len(), 1);
        assert_eq!(issues.duplicates[0].subject, "git");
        assert_eq!(issues.duplicates[0].sources.len(), 2);
        assert!(issues.opposing.is_empty());

        std::fs::remove_dir_all(&dir).ok();
    }

    #[test]
    fn test_find_rule_issues_detects_opposing() {
        let (store, dir) = temp_db();

        let never = Triple {
            subject: "alembic".to_string(),
            predicate: "never".to_string(),
            object: "hand-write migrations".to_string(),
            confidence: 0.9,
            domain: "general".to_string(),
            source_file: "CLAUDE.md".to_string(),
            line_start: Some(1),
            line_end: Some(1),
            layer: None,
            expires_at: None,
            noenrich: false,

            tier: None,

            source_label: None,
        };
        let always = Triple {
            subject: "alembic".to_string(),
            predicate: "always".to_string(),
            object: "hand-write migrations".to_string(),
            confidence: 0.9,
            domain: "general".to_string(),
            source_file: "CLAUDE.md".to_string(),
            line_start: Some(2),
            line_end: Some(2),
            layer: None,
            expires_at: None,
            noenrich: false,

            tier: None,

            source_label: None,
        };
        store
            .upsert_file(
                "CLAUDE.md",
                "- a\n- b",
                &[never, always],
                "claude_md_project",
            )
            .unwrap();

        let issues = store.find_rule_issues().unwrap();
        assert_eq!(issues.opposing.len(), 1);
        assert_eq!(issues.opposing[0].subject, "alembic");
        assert!(issues.opposing[0].predicates.contains(&"never".to_string()));
        assert!(issues.opposing[0]
            .predicates
            .contains(&"always".to_string()));

        std::fs::remove_dir_all(&dir).ok();
    }

    #[test]
    fn test_find_rule_issues_clean_set_is_empty() {
        let (store, dir) = temp_db();

        let t1 = Triple {
            subject: "git".to_string(),
            predicate: "never".to_string(),
            object: "force-push".to_string(),
            confidence: 0.9,
            domain: "general".to_string(),
            source_file: "CLAUDE.md".to_string(),
            line_start: Some(1),
            line_end: Some(1),
            layer: None,
            expires_at: None,
            noenrich: false,

            tier: None,

            source_label: None,
        };
        let t2 = Triple {
            subject: "alembic".to_string(),
            predicate: "never".to_string(),
            object: "hand-write".to_string(),
            confidence: 0.9,
            domain: "general".to_string(),
            source_file: "CLAUDE.md".to_string(),
            line_start: Some(2),
            line_end: Some(2),
            layer: None,
            expires_at: None,
            noenrich: false,

            tier: None,

            source_label: None,
        };
        store
            .upsert_file("CLAUDE.md", "- a\n- b", &[t1, t2], "claude_md_project")
            .unwrap();

        let issues = store.find_rule_issues().unwrap();
        assert!(issues.duplicates.is_empty());
        assert!(issues.opposing.is_empty());

        std::fs::remove_dir_all(&dir).ok();
    }

    #[test]
    fn test_meta() {
        let (store, dir) = temp_db();

        assert_eq!(store.get_meta("last_scan").unwrap(), None);
        store.set_meta("last_scan", "12345").unwrap();
        assert_eq!(
            store.get_meta("last_scan").unwrap(),
            Some("12345".to_string())
        );

        std::fs::remove_dir_all(&dir).ok();
    }

    #[test]
    fn test_severity_override_round_trip() {
        let (store, dir) = temp_db();
        let t = Triple {
            subject: "alembic".to_string(),
            predicate: "prefers".to_string(),
            object: "autogenerate".to_string(),
            confidence: 0.9,
            domain: "general".to_string(),
            source_file: "CLAUDE.md".to_string(),
            line_start: Some(1),
            line_end: Some(1),
            layer: Some(1),
            expires_at: None,
            noenrich: false,

            tier: None,

            source_label: None,
        };
        store
            .upsert_file("CLAUDE.md", "x", &[t], "claude_md_project")
            .unwrap();
        store.classify_all_guardrails().unwrap();

        // Predicate-derived: prefers → Inform.
        let g = &store.load_guardrails().unwrap()[0];
        assert_eq!(
            store
                .get_rule_intent(g.triple_id)
                .unwrap()
                .unwrap()
                .severity,
            crate::intent::Severity::Inform
        );

        // Pin to Block.
        let changed = store
            .set_severity_override("alembic", crate::intent::Severity::Block)
            .unwrap();
        assert_eq!(changed.len(), 1);
        assert_eq!(changed[0].from, crate::intent::Severity::Inform);
        assert_eq!(changed[0].to, crate::intent::Severity::Block);
        assert_eq!(
            store
                .get_rule_intent(g.triple_id)
                .unwrap()
                .unwrap()
                .severity,
            crate::intent::Severity::Block,
            "override should win over classified severity"
        );

        // Re-classify: must NOT stomp the override.
        store.classify_all_guardrails().unwrap();
        assert_eq!(
            store
                .get_rule_intent(g.triple_id)
                .unwrap()
                .unwrap()
                .severity,
            crate::intent::Severity::Block,
            "classify_all_guardrails should preserve manual overrides"
        );

        // List shows the pinned rule.
        let listed = store.list_severity_overrides().unwrap();
        assert_eq!(listed.len(), 1);
        assert_eq!(listed[0].subject, "alembic");
        assert_eq!(listed[0].to, crate::intent::Severity::Block);

        // Clear it: severity reverts to predicate-derived.
        let cleared = store.clear_severity_override("alembic").unwrap();
        assert_eq!(cleared.len(), 1);
        assert!(store.list_severity_overrides().unwrap().is_empty());
        assert_eq!(
            store
                .get_rule_intent(g.triple_id)
                .unwrap()
                .unwrap()
                .severity,
            crate::intent::Severity::Inform,
            "after clear, severity should fall back to classification"
        );

        std::fs::remove_dir_all(&dir).ok();
    }

    #[test]
    fn test_severity_override_pattern_match_is_substring() {
        let (store, dir) = temp_db();
        let mk = |s: &str, line: i64| Triple {
            subject: s.to_string(),
            predicate: "never".to_string(),
            object: format!("{s}-action"),
            confidence: 0.9,
            domain: "general".to_string(),
            source_file: "CLAUDE.md".to_string(),
            line_start: Some(line),
            line_end: Some(line),
            layer: Some(1),
            expires_at: None,
            noenrich: false,

            tier: None,

            source_label: None,
        };
        store
            .upsert_file(
                "CLAUDE.md",
                "x",
                &[mk("alembic", 1), mk("git", 2), mk("cargo", 3)],
                "claude_md_project",
            )
            .unwrap();
        store.classify_all_guardrails().unwrap();

        // Pattern matches by case-insensitive substring on subject OR object.
        let changed = store
            .set_severity_override("ALEM", crate::intent::Severity::Inform)
            .unwrap();
        assert_eq!(changed.len(), 1);
        assert_eq!(changed[0].subject, "alembic");

        // Clear with a no-match pattern is a no-op (doesn't reset other rules).
        let cleared = store.clear_severity_override("nope").unwrap();
        assert!(cleared.is_empty());
        assert_eq!(store.list_severity_overrides().unwrap().len(), 1);

        std::fs::remove_dir_all(&dir).ok();
    }

    #[test]
    fn test_rules_for_file_returns_only_that_file() {
        let (store, dir) = temp_db();
        let mk_in = |path_prefix: &str, line: i64| Triple {
            subject: format!("{path_prefix}-rule"),
            predicate: "never".to_string(),
            object: "do bad".to_string(),
            confidence: 0.9,
            domain: "general".to_string(),
            source_file: path_prefix.to_string(),
            line_start: Some(line),
            line_end: Some(line),
            layer: Some(1),
            expires_at: None,
            noenrich: false,

            tier: None,

            source_label: None,
        };
        store
            .upsert_file(
                "CLAUDE.md",
                "a",
                &[mk_in("CLAUDE.md", 1)],
                "claude_md_project",
            )
            .unwrap();
        store
            .upsert_file(
                "memory/feedback.md",
                "b",
                &[mk_in("memory/feedback.md", 1)],
                "memory",
            )
            .unwrap();

        let claude_rules = store.rules_for_file("CLAUDE.md").unwrap();
        assert_eq!(claude_rules.len(), 1);
        assert_eq!(claude_rules[0].subject, "CLAUDE.md-rule");

        let memory_rules = store.rules_for_file("memory/feedback.md").unwrap();
        assert_eq!(memory_rules.len(), 1);
        assert_eq!(memory_rules[0].subject, "memory/feedback.md-rule");

        let nothing = store.rules_for_file("nonexistent.md").unwrap();
        assert!(nothing.is_empty());

        std::fs::remove_dir_all(&dir).ok();
    }

    #[test]
    fn test_expired_rules_are_filtered_on_load() {
        let (store, dir) = temp_db();
        let alive = Triple {
            subject: "git".to_string(),
            predicate: "never".to_string(),
            object: "force-push to main".to_string(),
            confidence: 0.92,
            domain: "general".to_string(),
            source_file: "CLAUDE.md".to_string(),
            line_start: Some(1),
            line_end: Some(1),
            layer: Some(1),
            expires_at: Some("2099-01-01".to_string()), // far future
            noenrich: false,

            tier: None,

            source_label: None,
        };
        let dead = Triple {
            subject: "legacy".to_string(),
            predicate: "never".to_string(),
            object: "touch the old api".to_string(),
            confidence: 0.92,
            domain: "general".to_string(),
            source_file: "CLAUDE.md".to_string(),
            line_start: Some(2),
            line_end: Some(2),
            layer: Some(1),
            expires_at: Some("2000-01-01".to_string()), // long past
            noenrich: false,

            tier: None,

            source_label: None,
        };
        store
            .upsert_file("CLAUDE.md", "x", &[alive, dead], "claude_md_project")
            .unwrap();

        let rails = store.load_guardrails().unwrap();
        let subjects: Vec<_> = rails.iter().map(|g| g.subject.clone()).collect();
        assert!(
            subjects.iter().any(|s| s == "git"),
            "unexpired rule should load"
        );
        assert!(
            !subjects.iter().any(|s| s == "legacy"),
            "expired rule should NOT load"
        );

        std::fs::remove_dir_all(&dir).ok();
    }

    #[test]
    fn test_fresh_db_user_version_at_target_after_open() {
        let (_store, dir) = temp_db();
        let db_path = dir.join("test.db");
        let conn = Connection::open(&db_path).unwrap();
        let v: i64 = conn
            .query_row("PRAGMA user_version", [], |r| r.get(0))
            .unwrap();
        assert_eq!(v, MIGRATIONS.len() as i64);
        std::fs::remove_dir_all(&dir).ok();
    }

    #[test]
    fn test_pragmas_set_journal_mode_wal() {
        let (_store, dir) = temp_db();
        let db_path = dir.join("test.db");
        let conn = Connection::open(&db_path).unwrap();
        let mode: String = conn
            .query_row("PRAGMA journal_mode", [], |r| r.get(0))
            .unwrap();
        assert_eq!(mode.to_lowercase(), "wal");
        std::fs::remove_dir_all(&dir).ok();
    }

    #[test]
    fn test_reopen_skips_migrations_when_at_target() {
        // First open runs migration v1 and bumps user_version.
        let id = TEST_COUNTER.fetch_add(1, Ordering::SeqCst);
        let dir =
            std::env::temp_dir().join(format!("arai_test_reopen_{}_{}", std::process::id(), id));
        std::fs::create_dir_all(&dir).unwrap();
        let db_path = dir.join("test.db");

        let _s1 = Store::open(&db_path).unwrap();
        // Reopen — must succeed without error and user_version unchanged.
        let _s2 = Store::open(&db_path).unwrap();

        let conn = Connection::open(&db_path).unwrap();
        let v: i64 = conn
            .query_row("PRAGMA user_version", [], |r| r.get(0))
            .unwrap();
        assert_eq!(v, MIGRATIONS.len() as i64);

        std::fs::remove_dir_all(&dir).ok();
    }

    #[test]
    fn test_disabled_rules_are_filtered_from_load() {
        let (store, dir) = temp_db();
        let triples = vec![
            Triple {
                subject: "git".to_string(),
                predicate: "never".to_string(),
                object: "force-push to main".to_string(),
                confidence: 0.92,
                domain: "test".to_string(),
                source_file: "CLAUDE.md".to_string(),
                line_start: Some(1),
                line_end: Some(1),
                layer: Some(1),
                expires_at: None,
                noenrich: false,

                tier: None,

                source_label: None,
            },
            Triple {
                subject: "cargo".to_string(),
                predicate: "always".to_string(),
                object: "run tests before commit".to_string(),
                confidence: 0.92,
                domain: "test".to_string(),
                source_file: "CLAUDE.md".to_string(),
                line_start: Some(2),
                line_end: Some(2),
                layer: Some(1),
                expires_at: None,
                noenrich: false,

                tier: None,

                source_label: None,
            },
        ];
        store
            .upsert_file("CLAUDE.md", "x", &triples, "test")
            .unwrap();

        // Both rules visible initially.
        assert_eq!(store.load_guardrails().unwrap().len(), 2);

        // Disable one — load_guardrails drops it; rules_for_file keeps it.
        let inserted = store
            .disable_rule("git", "never", "force-push to main")
            .unwrap();
        assert!(inserted, "disable should insert a new row");
        let rails = store.load_guardrails().unwrap();
        assert_eq!(rails.len(), 1);
        assert_eq!(rails[0].subject, "cargo");
        let all_for_file = store.rules_for_file("CLAUDE.md").unwrap();
        assert_eq!(all_for_file.len(), 2, "rules_for_file ignores disable");

        // Disable is idempotent.
        let inserted_again = store
            .disable_rule("git", "never", "force-push to main")
            .unwrap();
        assert!(!inserted_again);

        // Enable restores.
        let removed = store
            .enable_rule("git", "never", "force-push to main")
            .unwrap();
        assert!(removed);
        assert_eq!(store.load_guardrails().unwrap().len(), 2);

        // Listing reports disabled rows in newest-first order.
        store
            .disable_rule("cargo", "always", "run tests before commit")
            .unwrap();
        let listed = store.list_disabled_rules().unwrap();
        assert_eq!(listed.len(), 1);
        assert_eq!(listed[0].0, "cargo");

        std::fs::remove_dir_all(&dir).ok();
    }

    #[test]
    fn test_disabled_rules_survive_arai_scan_re_extraction() {
        // Re-running `arai scan` deletes triples for a file and re-inserts
        // them.  triple_id changes; (s,p,o) does not.  Verify the disable
        // sticks across a re-upsert.
        let (store, dir) = temp_db();
        let t = Triple {
            subject: "git".to_string(),
            predicate: "never".to_string(),
            object: "force-push to main".to_string(),
            confidence: 0.92,
            domain: "test".to_string(),
            source_file: "CLAUDE.md".to_string(),
            line_start: Some(1),
            line_end: Some(1),
            layer: Some(1),
            expires_at: None,
            noenrich: false,

            tier: None,

            source_label: None,
        };
        store
            .upsert_file("CLAUDE.md", "x", std::slice::from_ref(&t), "test")
            .unwrap();
        store
            .disable_rule("git", "never", "force-push to main")
            .unwrap();
        assert_eq!(store.load_guardrails().unwrap().len(), 0);

        // Simulate a re-scan: same content → upsert short-circuits, but force
        // a re-insert by changing the file content checksum.
        store
            .upsert_file("CLAUDE.md", "x changed", &[t], "test")
            .unwrap();
        assert_eq!(
            store.load_guardrails().unwrap().len(),
            0,
            "re-extracted rule should still be disabled"
        );

        std::fs::remove_dir_all(&dir).ok();
    }

    #[test]
    fn test_migration_bridges_pre_versioned_db() {
        // Build a v0.2.x-shape DB by hand: rule_intent without severity /
        // severity_override, triples without layer / expires_at, and
        // user_version=0.  Open via Store and verify migration v1 backfills
        // the missing columns idempotently.
        let id = TEST_COUNTER.fetch_add(1, Ordering::SeqCst);
        let dir =
            std::env::temp_dir().join(format!("arai_test_bridge_{}_{}", std::process::id(), id));
        std::fs::create_dir_all(&dir).unwrap();
        let db_path = dir.join("test.db");

        {
            let conn = Connection::open(&db_path).unwrap();
            conn.execute_batch(
                "CREATE TABLE files (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    path TEXT NOT NULL UNIQUE,
                    checksum TEXT NOT NULL DEFAULT '',
                    scanned_at TEXT
                );
                CREATE TABLE triples (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    file_id INTEGER NOT NULL REFERENCES files(id) ON DELETE CASCADE,
                    s TEXT NOT NULL,
                    p TEXT NOT NULL,
                    o TEXT NOT NULL,
                    domain TEXT DEFAULT 'general',
                    confidence REAL DEFAULT 0.7,
                    line_start INTEGER,
                    line_end INTEGER,
                    source_file TEXT
                );
                CREATE TABLE rule_intent (
                    triple_id INTEGER PRIMARY KEY REFERENCES triples(id) ON DELETE CASCADE,
                    action TEXT NOT NULL DEFAULT 'general',
                    timing TEXT NOT NULL DEFAULT 'tool_call',
                    tools TEXT NOT NULL DEFAULT '*',
                    allow_inverse INTEGER DEFAULT 0,
                    enriched_by TEXT DEFAULT 'taxonomy',
                    enriched_at TEXT
                );
                PRAGMA user_version = 0;",
            )
            .unwrap();
        }

        // Run migrations
        let _store = Store::open(&db_path).unwrap();

        let conn = Connection::open(&db_path).unwrap();
        let v: i64 = conn
            .query_row("PRAGMA user_version", [], |r| r.get(0))
            .unwrap();
        assert_eq!(
            v,
            MIGRATIONS.len() as i64,
            "user_version should advance to target"
        );

        let columns_for = |table: &str| -> Vec<String> {
            let mut stmt = conn
                .prepare(&format!("PRAGMA table_info({table})"))
                .unwrap();
            stmt.query_map([], |r| r.get::<_, String>(1))
                .unwrap()
                .collect::<Result<Vec<_>, _>>()
                .unwrap()
        };

        let ri = columns_for("rule_intent");
        assert!(ri.contains(&"severity".to_string()), "severity backfilled");
        assert!(
            ri.contains(&"severity_override".to_string()),
            "severity_override backfilled"
        );

        let tr = columns_for("triples");
        assert!(tr.contains(&"layer".to_string()), "layer backfilled");
        assert!(
            tr.contains(&"expires_at".to_string()),
            "expires_at backfilled"
        );

        std::fs::remove_dir_all(&dir).ok();
    }

    proptest::proptest! {
        /// `is_safe_sql_identifier` matches the regex `^[A-Za-z_][A-Za-z0-9_]*$`
        /// for any input.  Independently re-derives the predicate from the
        /// raw chars and asserts equivalence — catches off-by-one regressions
        /// in the implementation (e.g. allowing a leading digit, accepting
        /// `-`, treating non-ASCII letter codepoints as alphabetic).
        #[test]
        fn prop_safe_sql_identifier_matches_regex(s in ".{0,40}") {
            let mut chars = s.chars();
            let manual = match chars.next() {
                Some(c) if c.is_ascii_alphabetic() || c == '_' => {
                    chars.all(|c| c.is_ascii_alphanumeric() || c == '_')
                }
                _ => false,
            };
            proptest::prop_assert_eq!(is_safe_sql_identifier(&s), manual,
                "is_safe_sql_identifier disagreed with manual check on {:?}", s);
        }

        /// Anything with a hyphen is rejected — pinning a property the
        /// audit agent specifically cared about (since SQL grammar would
        /// treat `foo-bar` as `foo MINUS bar`, which is the SQLi vector).
        #[test]
        fn prop_safe_sql_identifier_rejects_hyphen(
            head in "[A-Za-z_][A-Za-z0-9_]{0,10}",
            tail in "[A-Za-z0-9_]{1,10}",
        ) {
            let s = format!("{head}-{tail}");
            proptest::prop_assert!(!is_safe_sql_identifier(&s),
                "hyphen must be rejected; checked {s:?}");
        }
    }
}
