use std::path::Path;
use std::sync::Arc;
use anyhow::{Result, Context};
use rusqlite::{Connection, params};
use tokio::sync::Mutex;
use chrono::Utc;

use crate::types::*;

pub struct SqliteDb {
    conn: Arc<Mutex<Connection>>,
}

impl SqliteDb {
    pub fn new(path: &Path) -> Result<Self> {
        if let Some(parent) = path.parent() {
            std::fs::create_dir_all(parent)?;
        }
        let conn = Connection::open(path)
            .context("Failed to open SQLite database")?;

        conn.execute_batch("PRAGMA journal_mode=WAL; PRAGMA foreign_keys=ON;")?;

        Self::run_migrations(&conn)?;

        Ok(Self {
            conn: Arc::new(Mutex::new(conn)),
        })
    }

    fn run_migrations(conn: &Connection) -> Result<()> {
        conn.execute_batch(r#"
            CREATE TABLE IF NOT EXISTS scrolls (
                id TEXT PRIMARY KEY,
                title TEXT NOT NULL,
                intent TEXT NOT NULL,
                constraints_json TEXT NOT NULL DEFAULT '[]',
                metadata_json TEXT NOT NULL DEFAULT '{}',
                status TEXT NOT NULL DEFAULT 'Draft',
                created_at TEXT NOT NULL,
                updated_at TEXT NOT NULL
            );

            CREATE TABLE IF NOT EXISTS tasks (
                id TEXT PRIMARY KEY,
                scroll_id TEXT NOT NULL,
                description TEXT NOT NULL,
                target_file TEXT NOT NULL,
                task_type TEXT NOT NULL,
                status TEXT NOT NULL DEFAULT 'Pending',
                dependencies_json TEXT NOT NULL DEFAULT '[]',
                generated_code TEXT,
                diff TEXT,
                created_at TEXT NOT NULL,
                FOREIGN KEY (scroll_id) REFERENCES scrolls(id) ON DELETE CASCADE
            );
            CREATE INDEX IF NOT EXISTS idx_tasks_scroll_id ON tasks(scroll_id);

            CREATE TABLE IF NOT EXISTS validation_reports (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                scroll_id TEXT NOT NULL,
                is_valid BOOLEAN NOT NULL,
                report_json TEXT NOT NULL,
                validated_at TEXT NOT NULL,
                FOREIGN KEY (scroll_id) REFERENCES scrolls(id) ON DELETE CASCADE
            );
            CREATE INDEX IF NOT EXISTS idx_validation_reports_scroll_id
                ON validation_reports(scroll_id);

            CREATE TABLE IF NOT EXISTS search_queries (
                id TEXT PRIMARY KEY,
                query TEXT NOT NULL,
                query_type TEXT NOT NULL,
                strategy_used TEXT NOT NULL,
                tier1_tried BOOLEAN NOT NULL DEFAULT 0,
                tier1_success BOOLEAN NOT NULL DEFAULT 0,
                tier2_tried BOOLEAN NOT NULL DEFAULT 0,
                tier2_success BOOLEAN NOT NULL DEFAULT 0,
                tier3_tried BOOLEAN NOT NULL DEFAULT 0,
                tier3_success BOOLEAN NOT NULL DEFAULT 0,
                latency_ms INTEGER NOT NULL,
                cost_cents INTEGER NOT NULL,
                result_count INTEGER NOT NULL,
                user_satisfied BOOLEAN DEFAULT NULL,
                created_at INTEGER NOT NULL
            );
            CREATE INDEX IF NOT EXISTS idx_search_queries_query_type
                ON search_queries(query_type);
            CREATE INDEX IF NOT EXISTS idx_search_queries_created_at
                ON search_queries(created_at DESC);

            CREATE TABLE IF NOT EXISTS learned_strategies (
                query_pattern TEXT PRIMARY KEY,
                best_strategy TEXT NOT NULL,
                success_rate REAL NOT NULL,
                avg_latency_ms INTEGER NOT NULL,
                avg_cost_cents INTEGER NOT NULL,
                sample_count INTEGER NOT NULL,
                last_updated INTEGER NOT NULL
            );

            CREATE TABLE IF NOT EXISTS auto_forge_logs (
                id TEXT PRIMARY KEY,
                scroll_id TEXT NOT NULL,
                status TEXT NOT NULL DEFAULT 'running',
                report_json TEXT,
                decision TEXT,
                started_at TEXT NOT NULL,
                completed_at TEXT,
                FOREIGN KEY (scroll_id) REFERENCES scrolls(id) ON DELETE CASCADE
            );
            CREATE INDEX IF NOT EXISTS idx_auto_forge_logs_scroll_id
                ON auto_forge_logs(scroll_id);

            CREATE TABLE IF NOT EXISTS lifecycle_events (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                entity_type TEXT NOT NULL,
                entity_id TEXT NOT NULL,
                event_type TEXT NOT NULL,
                details_json TEXT,
                created_at TEXT NOT NULL
            );
            CREATE INDEX IF NOT EXISTS idx_lifecycle_entity
                ON lifecycle_events(entity_type, entity_id);

            CREATE TABLE IF NOT EXISTS external_refs (
                scroll_id TEXT NOT NULL,
                system TEXT NOT NULL,
                external_id TEXT NOT NULL,
                url TEXT,
                last_synced INTEGER NOT NULL,
                sync_direction TEXT NOT NULL DEFAULT 'Bidirectional',
                token_ref TEXT,
                PRIMARY KEY (scroll_id, system),
                FOREIGN KEY (scroll_id) REFERENCES scrolls(id) ON DELETE CASCADE
            );

            CREATE TABLE IF NOT EXISTS mcp_server_configs (
                system TEXT PRIMARY KEY,
                command TEXT NOT NULL,
                args_json TEXT NOT NULL DEFAULT '[]',
                env_vars_json TEXT NOT NULL DEFAULT '[]'
            );
        "#).context("Failed to run migrations")?;

        // Incremental migrations: add columns/tables that may not exist yet.
        // Each uses IF NOT EXISTS or ignores "duplicate column" errors.

        // Phase 1: cleanup_after column for scroll TTL
        let _ = conn.execute("ALTER TABLE scrolls ADD COLUMN cleanup_after TEXT", []);

        // Phase 2: daily_spend table for cost optimizer persistence
        conn.execute_batch(
            "CREATE TABLE IF NOT EXISTS daily_spend (
                date TEXT PRIMARY KEY,
                spend_micros INTEGER NOT NULL DEFAULT 0
            );"
        )?;

        // Phase 4: group_label column for task grouping
        let _ = conn.execute("ALTER TABLE tasks ADD COLUMN group_label TEXT", []);

        // Phase 3: agent_activities table for per-agent tracking
        conn.execute_batch(
            "CREATE TABLE IF NOT EXISTS agent_activities (
                id TEXT PRIMARY KEY,
                scroll_id TEXT NOT NULL,
                task_id TEXT NOT NULL,
                agent_name TEXT NOT NULL,
                action TEXT NOT NULL,
                detail TEXT NOT NULL DEFAULT '',
                started_at TEXT NOT NULL,
                completed_at TEXT,
                status TEXT NOT NULL DEFAULT 'Running',
                FOREIGN KEY (scroll_id) REFERENCES scrolls(id) ON DELETE CASCADE
            );
            CREATE INDEX IF NOT EXISTS idx_agent_activities_scroll
                ON agent_activities(scroll_id);
            CREATE INDEX IF NOT EXISTS idx_agent_activities_task
                ON agent_activities(task_id);"
        )?;

        // Phase 5: Ensure ON DELETE CASCADE on all FK declarations (for existing databases).
        Self::migrate_ensure_cascade(conn)?;

        Ok(())
    }

    /// Recreate FK-bearing tables with ON DELETE CASCADE for databases created
    /// before this migration was added. Idempotent: skips if CASCADE already present.
    fn migrate_ensure_cascade(conn: &Connection) -> Result<()> {
        let ddl: String = match conn.query_row(
            "SELECT sql FROM sqlite_master WHERE type='table' AND name='tasks'",
            [],
            |row| row.get(0),
        ) {
            Ok(d) => d,
            Err(_) => return Ok(()), // table doesn't exist yet (fresh DB handled by CREATE IF NOT EXISTS)
        };

        if ddl.to_uppercase().contains("ON DELETE CASCADE") {
            return Ok(()); // Already migrated
        }

        tracing::info!("Migrating FK declarations to ON DELETE CASCADE");

        // Must disable foreign keys before table recreation
        conn.execute_batch("PRAGMA foreign_keys=OFF;")?;

        conn.execute_batch(r#"
            BEGIN TRANSACTION;

            -- tasks
            CREATE TABLE tasks_new (
                id TEXT PRIMARY KEY,
                scroll_id TEXT NOT NULL,
                description TEXT NOT NULL,
                target_file TEXT NOT NULL,
                task_type TEXT NOT NULL,
                status TEXT NOT NULL DEFAULT 'Pending',
                dependencies_json TEXT NOT NULL DEFAULT '[]',
                generated_code TEXT,
                diff TEXT,
                created_at TEXT NOT NULL,
                group_label TEXT,
                FOREIGN KEY (scroll_id) REFERENCES scrolls(id) ON DELETE CASCADE
            );
            INSERT INTO tasks_new SELECT id, scroll_id, description, target_file,
                task_type, status, dependencies_json, generated_code, diff,
                created_at, group_label FROM tasks;
            DROP TABLE tasks;
            ALTER TABLE tasks_new RENAME TO tasks;
            CREATE INDEX IF NOT EXISTS idx_tasks_scroll_id ON tasks(scroll_id);

            -- validation_reports
            CREATE TABLE validation_reports_new (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                scroll_id TEXT NOT NULL,
                is_valid BOOLEAN NOT NULL,
                report_json TEXT NOT NULL,
                validated_at TEXT NOT NULL,
                FOREIGN KEY (scroll_id) REFERENCES scrolls(id) ON DELETE CASCADE
            );
            INSERT INTO validation_reports_new SELECT id, scroll_id, is_valid,
                report_json, validated_at FROM validation_reports;
            DROP TABLE validation_reports;
            ALTER TABLE validation_reports_new RENAME TO validation_reports;
            CREATE INDEX IF NOT EXISTS idx_validation_reports_scroll_id
                ON validation_reports(scroll_id);

            -- auto_forge_logs
            CREATE TABLE auto_forge_logs_new (
                id TEXT PRIMARY KEY,
                scroll_id TEXT NOT NULL,
                status TEXT NOT NULL DEFAULT 'running',
                report_json TEXT,
                decision TEXT,
                started_at TEXT NOT NULL,
                completed_at TEXT,
                FOREIGN KEY (scroll_id) REFERENCES scrolls(id) ON DELETE CASCADE
            );
            INSERT INTO auto_forge_logs_new SELECT id, scroll_id, status,
                report_json, decision, started_at, completed_at FROM auto_forge_logs;
            DROP TABLE auto_forge_logs;
            ALTER TABLE auto_forge_logs_new RENAME TO auto_forge_logs;
            CREATE INDEX IF NOT EXISTS idx_auto_forge_logs_scroll_id
                ON auto_forge_logs(scroll_id);

            -- external_refs
            CREATE TABLE external_refs_new (
                scroll_id TEXT NOT NULL,
                system TEXT NOT NULL,
                external_id TEXT NOT NULL,
                url TEXT,
                last_synced INTEGER NOT NULL,
                sync_direction TEXT NOT NULL DEFAULT 'Bidirectional',
                token_ref TEXT,
                PRIMARY KEY (scroll_id, system),
                FOREIGN KEY (scroll_id) REFERENCES scrolls(id) ON DELETE CASCADE
            );
            INSERT INTO external_refs_new SELECT scroll_id, system, external_id,
                url, last_synced, sync_direction, token_ref FROM external_refs;
            DROP TABLE external_refs;
            ALTER TABLE external_refs_new RENAME TO external_refs;

            -- agent_activities
            CREATE TABLE agent_activities_new (
                id TEXT PRIMARY KEY,
                scroll_id TEXT NOT NULL,
                task_id TEXT NOT NULL,
                agent_name TEXT NOT NULL,
                action TEXT NOT NULL,
                detail TEXT NOT NULL DEFAULT '',
                started_at TEXT NOT NULL,
                completed_at TEXT,
                status TEXT NOT NULL DEFAULT 'Running',
                FOREIGN KEY (scroll_id) REFERENCES scrolls(id) ON DELETE CASCADE
            );
            INSERT INTO agent_activities_new SELECT id, scroll_id, task_id,
                agent_name, action, detail, started_at, completed_at, status
                FROM agent_activities;
            DROP TABLE agent_activities;
            ALTER TABLE agent_activities_new RENAME TO agent_activities;
            CREATE INDEX IF NOT EXISTS idx_agent_activities_scroll
                ON agent_activities(scroll_id);
            CREATE INDEX IF NOT EXISTS idx_agent_activities_task
                ON agent_activities(task_id);

            COMMIT;
        "#)?;

        conn.execute_batch("PRAGMA foreign_keys=ON;")?;
        tracing::info!("FK CASCADE migration complete");

        Ok(())
    }

    // ===== SCROLL Operations =====

    pub async fn create_scroll(&self, scroll: &Scroll) -> Result<()> {
        let conn = self.conn.lock().await;
        let cleanup_after = scroll.cleanup_after.as_ref().map(|dt| dt.to_rfc3339());
        conn.execute(
            "INSERT INTO scrolls (id, title, intent, constraints_json, metadata_json, status, created_at, updated_at, cleanup_after)
             VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9)",
            params![
                scroll.id,
                scroll.title,
                scroll.intent,
                serde_json::to_string(&scroll.constraints)?,
                serde_json::to_string(&scroll.metadata)?,
                format!("{:?}", scroll.status),
                scroll.created_at.to_rfc3339(),
                scroll.updated_at.to_rfc3339(),
                cleanup_after,
            ],
        )?;
        Ok(())
    }

    pub async fn get_scroll(&self, scroll_id: &str) -> Result<Scroll> {
        let conn = self.conn.lock().await;
        let mut stmt = conn.prepare(
            "SELECT id, title, intent, constraints_json, metadata_json, status, created_at, updated_at, cleanup_after
             FROM scrolls WHERE id = ?1"
        )?;

        let scroll = stmt.query_row(params![scroll_id], |row| {
            let constraints_json: String = row.get(3)?;
            let metadata_json: String = row.get(4)?;
            let status_str: String = row.get(5)?;
            let created_str: String = row.get(6)?;
            let updated_str: String = row.get(7)?;
            let cleanup_str: Option<String> = row.get(8)?;

            Ok(Scroll {
                id: row.get(0)?,
                title: row.get(1)?,
                intent: row.get(2)?,
                constraints: serde_json::from_str(&constraints_json).unwrap_or_default(),
                metadata: serde_json::from_str(&metadata_json).unwrap_or_else(|_| ScrollMetadata {
                    execution_mode: ExecutionMode::Forge,
                    auto_forge_config: None,
                    recipe_used: None,
                    search_context: vec![],
                }),
                status: Self::parse_scroll_status(&status_str),
                tasks: vec![],
                created_at: chrono::DateTime::parse_from_rfc3339(&created_str)
                    .map(|dt| dt.with_timezone(&Utc))
                    .unwrap_or_else(|_| Utc::now()),
                updated_at: chrono::DateTime::parse_from_rfc3339(&updated_str)
                    .map(|dt| dt.with_timezone(&Utc))
                    .unwrap_or_else(|_| Utc::now()),
                cleanup_after: cleanup_str.and_then(|s| {
                    chrono::DateTime::parse_from_rfc3339(&s)
                        .map(|dt| dt.with_timezone(&Utc))
                        .ok()
                }),
            })
        }).context(format!("Scroll not found: {}", scroll_id))?;

        Ok(scroll)
    }

    pub async fn update_scroll_status(&self, scroll_id: &str, status: &ScrollStatus) -> Result<()> {
        let conn = self.conn.lock().await;

        // Validate state machine transition
        let current_status: String = conn.query_row(
            "SELECT status FROM scrolls WHERE id = ?1",
            params![scroll_id],
            |row| row.get(0),
        ).context("Scroll not found for status update")?;
        let current: ScrollStatus = serde_json::from_str(&format!("\"{current_status}\""))
            .with_context(|| format!("Invalid stored status: {current_status}"))?;
        if !current.can_transition_to(status) {
            anyhow::bail!(
                "Invalid scroll status transition: {:?} → {:?} for scroll {scroll_id}",
                current, status
            );
        }

        let now = Utc::now();
        let cleanup_after = match status {
            ScrollStatus::Completed | ScrollStatus::Failed => {
                Some((now + chrono::Duration::days(1)).to_rfc3339())
            }
            _ => None, // Clear cleanup schedule if scroll re-enters active state
        };
        conn.execute(
            "UPDATE scrolls SET status = ?1, updated_at = ?2, cleanup_after = ?3 WHERE id = ?4",
            params![format!("{:?}", status), now.to_rfc3339(), cleanup_after, scroll_id],
        )?;
        Ok(())
    }

    pub async fn list_scrolls(&self, status: Option<&ScrollStatus>) -> Result<Vec<Scroll>> {
        let conn = self.conn.lock().await;
        let mut scrolls = Vec::new();

        let status_str = status.map(|s| format!("{:?}", s));
        let query = if status_str.is_some() {
            "SELECT id, title, intent, constraints_json, metadata_json, status, created_at, updated_at, cleanup_after
             FROM scrolls WHERE status = ?1 ORDER BY created_at DESC"
        } else {
            "SELECT id, title, intent, constraints_json, metadata_json, status, created_at, updated_at, cleanup_after
             FROM scrolls ORDER BY created_at DESC"
        };

        let mut stmt = conn.prepare(query)?;
        let rows = stmt.query_map(rusqlite::params_from_iter(status_str.iter()), |row| {
            let constraints_json: String = row.get(3)?;
            let metadata_json: String = row.get(4)?;
            let status_str: String = row.get(5)?;
            let created_str: String = row.get(6)?;
            let updated_str: String = row.get(7)?;
            let cleanup_str: Option<String> = row.get(8)?;

            Ok(Scroll {
                id: row.get(0)?,
                title: row.get(1)?,
                intent: row.get(2)?,
                constraints: serde_json::from_str(&constraints_json).unwrap_or_default(),
                metadata: serde_json::from_str(&metadata_json).unwrap_or_else(|_| ScrollMetadata {
                    execution_mode: ExecutionMode::Forge,
                    auto_forge_config: None,
                    recipe_used: None,
                    search_context: vec![],
                }),
                status: Self::parse_scroll_status(&status_str),
                tasks: vec![],
                created_at: chrono::DateTime::parse_from_rfc3339(&created_str)
                    .map(|dt| dt.with_timezone(&Utc))
                    .unwrap_or_else(|_| Utc::now()),
                updated_at: chrono::DateTime::parse_from_rfc3339(&updated_str)
                    .map(|dt| dt.with_timezone(&Utc))
                    .unwrap_or_else(|_| Utc::now()),
                cleanup_after: cleanup_str.and_then(|s| {
                    chrono::DateTime::parse_from_rfc3339(&s)
                        .map(|dt| dt.with_timezone(&Utc))
                        .ok()
                }),
            })
        })?;

        for row in rows {
            scrolls.push(row?);
        }

        Ok(scrolls)
    }

    // ===== TASK Operations =====

    pub async fn create_task(&self, task: &Task) -> Result<()> {
        let conn = self.conn.lock().await;
        conn.execute(
            "INSERT INTO tasks (id, scroll_id, description, target_file, task_type, status, dependencies_json, generated_code, diff, created_at, group_label)
             VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11)",
            params![
                task.id,
                task.scroll_id,
                task.description,
                task.target_file,
                format!("{:?}", task.task_type),
                format!("{:?}", task.status),
                serde_json::to_string(&task.dependencies)?,
                task.generated_code,
                task.diff,
                task.created_at.to_rfc3339(),
                task.group_label,
            ],
        )?;
        Ok(())
    }

    /// Return task counts keyed by scroll_id for all scrolls (single query).
    pub async fn get_task_counts(&self) -> Result<std::collections::HashMap<String, usize>> {
        let conn = self.conn.lock().await;
        let mut stmt = conn.prepare(
            "SELECT scroll_id, COUNT(*) FROM tasks GROUP BY scroll_id"
        )?;
        let rows = stmt.query_map([], |row| {
            Ok((row.get::<_, String>(0)?, row.get::<_, usize>(1)?))
        })?;
        let mut map = std::collections::HashMap::new();
        for row in rows {
            let (sid, count) = row?;
            map.insert(sid, count);
        }
        Ok(map)
    }

    pub async fn get_tasks_for_scroll(&self, scroll_id: &str) -> Result<Vec<Task>> {
        let conn = self.conn.lock().await;
        let mut stmt = conn.prepare(
            "SELECT id, scroll_id, description, target_file, task_type, status, dependencies_json, generated_code, diff, created_at, group_label
             FROM tasks WHERE scroll_id = ?1 ORDER BY created_at"
        )?;

        let mut tasks = Vec::new();
        let rows = stmt.query_map(params![scroll_id], |row| {
            let deps_json: String = row.get(6)?;
            let task_type_str: String = row.get(4)?;
            let status_str: String = row.get(5)?;
            let created_str: String = row.get(9)?;

            Ok(Task {
                id: row.get(0)?,
                scroll_id: row.get(1)?,
                description: row.get(2)?,
                target_file: row.get(3)?,
                task_type: Self::parse_task_type(&task_type_str),
                status: Self::parse_task_status(&status_str),
                dependencies: serde_json::from_str(&deps_json).unwrap_or_default(),
                generated_code: row.get(7)?,
                diff: row.get(8)?,
                created_at: chrono::DateTime::parse_from_rfc3339(&created_str)
                    .map(|dt| dt.with_timezone(&Utc))
                    .unwrap_or_else(|_| Utc::now()),
                group_label: row.get(10)?,
            })
        })?;

        for row in rows {
            tasks.push(row?);
        }

        Ok(tasks)
    }

    pub async fn update_task_status(&self, task_id: &str, status: &TaskStatus) -> Result<()> {
        let conn = self.conn.lock().await;
        conn.execute(
            "UPDATE tasks SET status = ?1 WHERE id = ?2",
            params![format!("{:?}", status), task_id],
        )?;
        Ok(())
    }

    pub async fn get_task_by_id(&self, task_id: &str) -> Result<Task> {
        let conn = self.conn.lock().await;
        let mut stmt = conn.prepare(
            "SELECT id, scroll_id, description, target_file, task_type, status, dependencies_json, generated_code, diff, created_at, group_label
             FROM tasks WHERE id = ?1"
        )?;

        let task = stmt.query_row(params![task_id], |row| {
            let deps_json: String = row.get(6)?;
            let task_type_str: String = row.get(4)?;
            let status_str: String = row.get(5)?;
            let created_str: String = row.get(9)?;

            Ok(Task {
                id: row.get(0)?,
                scroll_id: row.get(1)?,
                description: row.get(2)?,
                target_file: row.get(3)?,
                task_type: Self::parse_task_type(&task_type_str),
                status: Self::parse_task_status(&status_str),
                dependencies: serde_json::from_str(&deps_json).unwrap_or_default(),
                generated_code: row.get(7)?,
                diff: row.get(8)?,
                created_at: chrono::DateTime::parse_from_rfc3339(&created_str)
                    .map(|dt| dt.with_timezone(&Utc))
                    .unwrap_or_else(|_| Utc::now()),
                group_label: row.get(10)?,
            })
        }).context(format!("Task not found: {}", task_id))?;

        Ok(task)
    }

    pub async fn update_task_code(&self, task_id: &str, code: &str, diff: &str) -> Result<()> {
        let conn = self.conn.lock().await;
        conn.execute(
            "UPDATE tasks SET generated_code = ?1, diff = ?2 WHERE id = ?3",
            params![code, diff, task_id],
        )?;
        Ok(())
    }

    /// Set the group_label on an existing task.
    pub async fn update_task_group_label(&self, task_id: &str, group_label: &str) -> Result<()> {
        let conn = self.conn.lock().await;
        let updated = conn.execute(
            "UPDATE tasks SET group_label = ?1 WHERE id = ?2",
            params![group_label, task_id],
        )?;
        if updated == 0 {
            anyhow::bail!("Task not found: {}", task_id);
        }
        Ok(())
    }

    /// Update selected fields of a task. Only non-None fields are written.
    pub async fn update_task_fields(
        &self,
        task_id: &str,
        description: Option<&str>,
        target_file: Option<&str>,
        task_type: Option<&TaskType>,
        generated_code: Option<Option<&str>>,  // Some(None) clears, Some(Some(x)) sets, None=skip
        dependencies: Option<&[String]>,
    ) -> Result<()> {
        let conn = self.conn.lock().await;
        let mut sets = Vec::new();
        let mut values: Vec<Box<dyn rusqlite::ToSql>> = Vec::new();

        if let Some(desc) = description {
            sets.push("description = ?");
            values.push(Box::new(desc.to_string()));
        }
        if let Some(tf) = target_file {
            sets.push("target_file = ?");
            values.push(Box::new(tf.to_string()));
        }
        if let Some(tt) = task_type {
            sets.push("task_type = ?");
            values.push(Box::new(format!("{:?}", tt)));
        }
        if let Some(gc) = generated_code {
            sets.push("generated_code = ?");
            values.push(Box::new(gc.map(|s| s.to_string())));
        }
        if let Some(deps) = dependencies {
            sets.push("dependencies_json = ?");
            values.push(Box::new(serde_json::to_string(deps)?));
        }

        if sets.is_empty() {
            return Ok(());
        }

        values.push(Box::new(task_id.to_string()));
        let sql = format!(
            "UPDATE tasks SET {} WHERE id = ?",
            sets.join(", ")
        );
        let params: Vec<&dyn rusqlite::ToSql> = values.iter().map(|v| v.as_ref()).collect();
        conn.execute(&sql, params.as_slice())?;
        Ok(())
    }

    /// Update selected fields of a scroll. Only non-None fields are written.
    /// `execution_mode` and `auto_forge_config` are stored inside metadata_json,
    /// so this does a read-modify-write on that column.
    pub async fn update_scroll_fields(
        &self,
        scroll_id: &str,
        title: Option<&str>,
        intent: Option<&str>,
        execution_mode: Option<&ExecutionMode>,
        auto_forge_config: Option<Option<&AutoForgeConfig>>,  // Some(None) clears, Some(Some(x)) sets
    ) -> Result<()> {
        let conn = self.conn.lock().await;
        let now = Utc::now().to_rfc3339();

        // If metadata fields are being updated, read-modify-write metadata_json.
        let needs_metadata_update = execution_mode.is_some() || auto_forge_config.is_some();
        let metadata_json = if needs_metadata_update {
            let current: String = conn.query_row(
                "SELECT metadata_json FROM scrolls WHERE id = ?1",
                params![scroll_id],
                |row| row.get(0),
            )?;
            let mut metadata: ScrollMetadata = serde_json::from_str(&current)
                .unwrap_or(ScrollMetadata {
                    execution_mode: ExecutionMode::Forge,
                    auto_forge_config: None,
                    recipe_used: None,
                    search_context: vec![],
                });
            if let Some(em) = execution_mode {
                metadata.execution_mode = em.clone();
            }
            if let Some(afc) = auto_forge_config {
                metadata.auto_forge_config = afc.cloned();
            }
            Some(serde_json::to_string(&metadata)?)
        } else {
            None
        };

        let mut sets = vec!["updated_at = ?".to_string()];
        let mut values: Vec<Box<dyn rusqlite::ToSql>> = vec![Box::new(now)];

        if let Some(t) = title {
            sets.push("title = ?".to_string());
            values.push(Box::new(t.to_string()));
        }
        if let Some(i) = intent {
            sets.push("intent = ?".to_string());
            values.push(Box::new(i.to_string()));
        }
        if let Some(mj) = metadata_json {
            sets.push("metadata_json = ?".to_string());
            values.push(Box::new(mj));
        }

        values.push(Box::new(scroll_id.to_string()));
        let sql = format!(
            "UPDATE scrolls SET {} WHERE id = ?",
            sets.join(", ")
        );
        let params: Vec<&dyn rusqlite::ToSql> = values.iter().map(|v| v.as_ref()).collect();
        conn.execute(&sql, params.as_slice())?;
        Ok(())
    }

    /// Delete tasks by their IDs.
    pub async fn delete_tasks(&self, task_ids: &[String]) -> Result<usize> {
        if task_ids.is_empty() {
            return Ok(0);
        }
        let conn = self.conn.lock().await;
        let mut deleted = 0;
        for id in task_ids {
            deleted += conn.execute("DELETE FROM tasks WHERE id = ?1", params![id])?;
        }
        Ok(deleted)
    }

    // ===== TRANSACTIONAL IMPORT =====

    /// Apply a full scroll import diff atomically in a single transaction.
    /// All changes succeed together or are rolled back.
    pub async fn apply_import_changes(
        &self,
        scroll_id: &str,
        title: Option<&str>,
        intent: Option<&str>,
        execution_mode: Option<&ExecutionMode>,
        auto_forge_config: Option<Option<&AutoForgeConfig>>,
        tasks_to_create: &[Task],
        task_updates: &[(String, Option<String>, Option<String>, Option<TaskType>, Option<Option<String>>, Option<Vec<String>>, Option<Option<String>>)],
        task_ids_to_delete: &[String],
    ) -> Result<()> {
        let conn = self.conn.lock().await;
        let tx = conn.unchecked_transaction()?;
        let now = Utc::now().to_rfc3339();

        // 1. Update scroll fields
        let needs_metadata = execution_mode.is_some() || auto_forge_config.is_some();
        if title.is_some() || intent.is_some() || needs_metadata {
            let mut sets = vec!["updated_at = ?"];
            let mut vals: Vec<Box<dyn rusqlite::ToSql>> = vec![Box::new(now.clone())];

            if let Some(t) = title {
                sets.push("title = ?");
                vals.push(Box::new(t.to_string()));
            }
            if let Some(i) = intent {
                sets.push("intent = ?");
                vals.push(Box::new(i.to_string()));
            }
            if needs_metadata {
                let current: String = tx.query_row(
                    "SELECT metadata_json FROM scrolls WHERE id = ?1",
                    params![scroll_id],
                    |row| row.get(0),
                )?;
                let mut metadata: ScrollMetadata = serde_json::from_str(&current)
                    .unwrap_or(ScrollMetadata {
                        execution_mode: ExecutionMode::Forge,
                        auto_forge_config: None,
                        recipe_used: None,
                        search_context: vec![],
                    });
                if let Some(em) = execution_mode {
                    metadata.execution_mode = em.clone();
                }
                if let Some(afc) = auto_forge_config {
                    metadata.auto_forge_config = afc.cloned();
                }
                sets.push("metadata_json = ?");
                vals.push(Box::new(serde_json::to_string(&metadata)?));
            }
            vals.push(Box::new(scroll_id.to_string()));
            let sql = format!(
                "UPDATE scrolls SET {} WHERE id = ?",
                sets.join(", ")
            );
            let params: Vec<&dyn rusqlite::ToSql> = vals.iter().map(|v| v.as_ref()).collect();
            tx.execute(&sql, params.as_slice())?;
        }

        // 2. Create new tasks
        for task in tasks_to_create {
            tx.execute(
                "INSERT INTO tasks (id, scroll_id, description, target_file, task_type, status, dependencies_json, generated_code, diff, created_at, group_label)
                 VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11)",
                params![
                    task.id, task.scroll_id, task.description, task.target_file,
                    format!("{:?}", task.task_type), format!("{:?}", task.status),
                    serde_json::to_string(&task.dependencies)?,
                    task.generated_code, task.diff, task.created_at.to_rfc3339(),
                    task.group_label,
                ],
            )?;
        }

        // 3. Update existing tasks
        for (id, desc, target, ttype, code, deps, group) in task_updates {
            let mut sets = Vec::new();
            let mut vals: Vec<Box<dyn rusqlite::ToSql>> = Vec::new();
            if let Some(d) = desc {
                sets.push("description = ?");
                vals.push(Box::new(d.clone()));
            }
            if let Some(t) = target {
                sets.push("target_file = ?");
                vals.push(Box::new(t.clone()));
            }
            if let Some(tt) = ttype {
                sets.push("task_type = ?");
                vals.push(Box::new(format!("{:?}", tt)));
            }
            if let Some(gc) = code {
                sets.push("generated_code = ?");
                vals.push(Box::new(gc.clone()));
            }
            if let Some(d) = deps {
                sets.push("dependencies_json = ?");
                vals.push(Box::new(serde_json::to_string(d)?));
            }
            if let Some(gl) = group {
                sets.push("group_label = ?");
                vals.push(Box::new(gl.clone()));
            }
            if !sets.is_empty() {
                vals.push(Box::new(id.clone()));
                let sql = format!("UPDATE tasks SET {} WHERE id = ?", sets.join(", "));
                let params: Vec<&dyn rusqlite::ToSql> = vals.iter().map(|v| v.as_ref()).collect();
                tx.execute(&sql, params.as_slice())?;
            }
        }

        // 4. Delete removed tasks
        for id in task_ids_to_delete {
            tx.execute("DELETE FROM tasks WHERE id = ?1", params![id])?;
        }

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

    // ===== VALIDATION Operations =====

    pub async fn save_validation_report(&self, report: &ValidationReport) -> Result<()> {
        let conn = self.conn.lock().await;
        conn.execute(
            "INSERT INTO validation_reports (scroll_id, is_valid, report_json, validated_at)
             VALUES (?1, ?2, ?3, ?4)",
            params![
                report.scroll_id,
                report.is_valid,
                serde_json::to_string(report)?,
                report.validated_at.to_rfc3339(),
            ],
        )?;
        Ok(())
    }

    // ===== SEARCH METRICS Operations =====

    #[allow(clippy::too_many_arguments)]
    pub async fn record_search_query(
        &self,
        id: &str,
        query: &str,
        query_type: &str,
        strategy: &str,
        tier1_tried: bool,
        tier1_success: bool,
        tier2_tried: bool,
        tier2_success: bool,
        tier3_tried: bool,
        tier3_success: bool,
        latency_ms: i64,
        cost_cents: i64,
        result_count: i64,
    ) -> Result<()> {
        let conn = self.conn.lock().await;
        conn.execute(
            "INSERT INTO search_queries (id, query, query_type, strategy_used, tier1_tried, tier1_success, tier2_tried, tier2_success, tier3_tried, tier3_success, latency_ms, cost_cents, result_count, created_at)
             VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12, ?13, ?14)",
            params![
                id, query, query_type, strategy,
                tier1_tried, tier1_success,
                tier2_tried, tier2_success,
                tier3_tried, tier3_success,
                latency_ms, cost_cents, result_count,
                Utc::now().timestamp(),
            ],
        )?;
        Ok(())
    }

    pub async fn get_search_records_by_type(
        &self,
        query_type: &str,
        limit: usize,
    ) -> Result<Vec<crate::search::metrics::SearchRecord>> {
        let conn = self.conn.lock().await;
        let mut stmt = conn.prepare(
            "SELECT query_type, strategy_used, tier1_tried, tier1_success, tier2_tried, tier2_success, tier3_tried, tier3_success, latency_ms, cost_cents, result_count, created_at, user_satisfied
             FROM search_queries WHERE query_type = ?1 ORDER BY created_at DESC LIMIT ?2"
        )?;

        let mut records = Vec::new();
        let rows = stmt.query_map(params![query_type, limit as i64], |row| {
            Ok(crate::search::metrics::SearchRecord {
                query_type: row.get(0)?,
                strategy: row.get(1)?,
                tier1_tried: row.get(2)?,
                tier1_success: row.get(3)?,
                tier2_tried: row.get(4)?,
                tier2_success: row.get(5)?,
                tier3_tried: row.get(6)?,
                tier3_success: row.get(7)?,
                latency_ms: row.get(8)?,
                cost_cents: row.get(9)?,
                result_count: row.get(10)?,
                created_at: row.get(11)?,
                user_satisfied: row.get(12)?,
            })
        })?;

        for row in rows {
            records.push(row?);
        }

        Ok(records)
    }

    pub async fn update_search_satisfaction(
        &self,
        query_id: &str,
        satisfied: bool,
    ) -> Result<()> {
        let conn = self.conn.lock().await;
        conn.execute(
            "UPDATE search_queries SET user_satisfied = ?1 WHERE id = ?2",
            params![satisfied, query_id],
        )?;
        Ok(())
    }

    /// Look up the query_type for a search query by its ID.
    /// Used by the feedback handler to trigger re-learning for the correct query type.
    pub async fn get_search_query_type(&self, query_id: &str) -> Result<Option<String>> {
        let conn = self.conn.lock().await;
        let mut stmt = conn.prepare(
            "SELECT query_type FROM search_queries WHERE id = ?1"
        )?;
        let result = stmt.query_row(params![query_id], |row| row.get::<_, String>(0));
        match result {
            Ok(qt) => Ok(Some(qt)),
            Err(rusqlite::Error::QueryReturnedNoRows) => Ok(None),
            Err(e) => Err(e.into()),
        }
    }

    pub async fn get_learned_strategy(&self, query_pattern: &str) -> Result<Option<String>> {
        let conn = self.conn.lock().await;
        let mut stmt = conn.prepare(
            "SELECT best_strategy FROM learned_strategies WHERE query_pattern = ?1"
        )?;

        let result = stmt.query_row(params![query_pattern], |row| {
            row.get::<_, String>(0)
        });

        match result {
            Ok(strategy) => Ok(Some(strategy)),
            Err(rusqlite::Error::QueryReturnedNoRows) => Ok(None),
            Err(e) => Err(e.into()),
        }
    }

    pub async fn upsert_learned_strategy(
        &self,
        query_pattern: &str,
        best_strategy: &str,
        success_rate: f64,
        avg_latency_ms: i64,
        avg_cost_cents: i64,
        sample_count: i64,
    ) -> Result<()> {
        let conn = self.conn.lock().await;
        conn.execute(
            "INSERT INTO learned_strategies (query_pattern, best_strategy, success_rate, avg_latency_ms, avg_cost_cents, sample_count, last_updated)
             VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7)
             ON CONFLICT(query_pattern) DO UPDATE SET
                best_strategy = excluded.best_strategy,
                success_rate = excluded.success_rate,
                avg_latency_ms = excluded.avg_latency_ms,
                avg_cost_cents = excluded.avg_cost_cents,
                sample_count = excluded.sample_count,
                last_updated = excluded.last_updated",
            params![
                query_pattern, best_strategy, success_rate,
                avg_latency_ms, avg_cost_cents, sample_count,
                Utc::now().timestamp(),
            ],
        )?;
        Ok(())
    }

    // ===== EXTERNAL REF Operations =====

    pub async fn store_external_ref(
        &self,
        scroll_id: &str,
        ext_ref: &crate::sync::types::ExternalRef,
    ) -> Result<()> {
        let conn = self.conn.lock().await;
        conn.execute(
            "INSERT INTO external_refs (scroll_id, system, external_id, url, last_synced, sync_direction, token_ref)
             VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7)
             ON CONFLICT(scroll_id, system) DO UPDATE SET
                external_id = excluded.external_id,
                url = excluded.url,
                last_synced = excluded.last_synced,
                sync_direction = excluded.sync_direction,
                token_ref = excluded.token_ref",
            params![
                scroll_id,
                format!("{}", ext_ref.system),
                ext_ref.external_id,
                ext_ref.url,
                ext_ref.last_synced,
                format!("{}", ext_ref.sync_direction),
                ext_ref.token_ref,
            ],
        )?;
        Ok(())
    }

    pub async fn get_external_ref(
        &self,
        scroll_id: &str,
        system: &crate::sync::types::ExternalSystem,
    ) -> Result<Option<crate::sync::types::ExternalRef>> {
        let conn = self.conn.lock().await;
        let system_str = format!("{}", system);
        let mut stmt = conn.prepare(
            "SELECT system, external_id, url, last_synced, sync_direction, token_ref
             FROM external_refs WHERE scroll_id = ?1 AND system = ?2"
        )?;

        let result = stmt.query_row(params![scroll_id, system_str], |row| {
            let system_str: String = row.get(0)?;
            let direction_str: String = row.get(4)?;
            Ok(crate::sync::types::ExternalRef {
                system: Self::parse_external_system(&system_str),
                external_id: row.get(1)?,
                url: row.get::<_, Option<String>>(2)?.unwrap_or_default(),
                last_synced: row.get(3)?,
                sync_direction: Self::parse_sync_direction(&direction_str),
                token_ref: row.get(5)?,
            })
        });

        match result {
            Ok(ext_ref) => Ok(Some(ext_ref)),
            Err(rusqlite::Error::QueryReturnedNoRows) => Ok(None),
            Err(e) => Err(e.into()),
        }
    }

    /// Get all external refs for a given scroll, or all if scroll_id is None.
    pub async fn get_all_external_refs(
        &self,
        scroll_id: Option<&str>,
    ) -> Result<Vec<(String, crate::sync::types::ExternalRef)>> {
        let conn = self.conn.lock().await;
        let mut results = Vec::new();

        let (query, filter) = match scroll_id {
            Some(id) => (
                "SELECT scroll_id, system, external_id, url, last_synced, sync_direction, token_ref
                 FROM external_refs WHERE scroll_id = ?1",
                Some(id.to_string()),
            ),
            None => (
                "SELECT scroll_id, system, external_id, url, last_synced, sync_direction, token_ref
                 FROM external_refs",
                None,
            ),
        };

        let mut stmt = conn.prepare(query)?;
        let mut rows = match &filter {
            Some(id) => stmt.query(params![id])?,
            None => stmt.query([])?,
        };

        while let Some(row) = rows.next()? {
            let system_str: String = row.get(1)?;
            let direction_str: String = row.get(5)?;
            results.push((
                row.get::<_, String>(0)?,
                crate::sync::types::ExternalRef {
                    system: Self::parse_external_system(&system_str),
                    external_id: row.get(2)?,
                    url: row.get::<_, Option<String>>(3)?.unwrap_or_default(),
                    last_synced: row.get(4)?,
                    sync_direction: Self::parse_sync_direction(&direction_str),
                    token_ref: row.get(6)?,
                },
            ));
        }
        Ok(results)
    }

    /// Store an MCP server configuration for an external system.
    pub async fn store_mcp_server_config(
        &self,
        config: &crate::sync::types::McpServerConfig,
    ) -> Result<()> {
        let conn = self.conn.lock().await;
        conn.execute(
            "INSERT INTO mcp_server_configs (system, command, args_json, env_vars_json)
             VALUES (?1, ?2, ?3, ?4)
             ON CONFLICT(system) DO UPDATE SET
                command = excluded.command,
                args_json = excluded.args_json,
                env_vars_json = excluded.env_vars_json",
            params![
                format!("{}", config.system),
                config.command,
                serde_json::to_string(&config.args)?,
                serde_json::to_string(&config.env_vars)?,
            ],
        )?;
        Ok(())
    }

    /// Get MCP server configuration for an external system.
    pub async fn get_mcp_server_config(
        &self,
        system: &crate::sync::types::ExternalSystem,
    ) -> Result<Option<crate::sync::types::McpServerConfig>> {
        let conn = self.conn.lock().await;
        let mut stmt = conn.prepare(
            "SELECT system, command, args_json, env_vars_json
             FROM mcp_server_configs WHERE system = ?1"
        )?;

        let result = stmt.query_row(params![format!("{}", system)], |row| {
            let system_str: String = row.get(0)?;
            let args_json: String = row.get(2)?;
            let env_json: String = row.get(3)?;
            Ok(crate::sync::types::McpServerConfig {
                system: Self::parse_external_system(&system_str),
                command: row.get(1)?,
                args: serde_json::from_str(&args_json).unwrap_or_default(),
                env_vars: serde_json::from_str(&env_json).unwrap_or_default(),
            })
        });

        match result {
            Ok(config) => Ok(Some(config)),
            Err(rusqlite::Error::QueryReturnedNoRows) => Ok(None),
            Err(e) => Err(e.into()),
        }
    }

    // ===== AGENT ACTIVITY Operations =====

    /// Record a new agent activity (initially Running).
    pub async fn record_agent_activity(&self, activity: &crate::types::AgentActivity) -> Result<()> {
        let conn = self.conn.lock().await;
        conn.execute(
            "INSERT INTO agent_activities (id, scroll_id, task_id, agent_name, action, detail, started_at, completed_at, status)
             VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9)",
            params![
                activity.id,
                activity.scroll_id,
                activity.task_id,
                activity.agent_name,
                activity.action,
                activity.detail,
                activity.started_at.to_rfc3339(),
                activity.completed_at.as_ref().map(|dt| dt.to_rfc3339()),
                format!("{:?}", activity.status),
            ],
        )?;
        Ok(())
    }

    /// Mark an agent activity as completed or failed.
    pub async fn complete_agent_activity(
        &self,
        activity_id: &str,
        status: &crate::types::AgentActivityStatus,
        detail: &str,
    ) -> Result<()> {
        let conn = self.conn.lock().await;
        let now = Utc::now().to_rfc3339();
        conn.execute(
            "UPDATE agent_activities SET status = ?1, completed_at = ?2, detail = ?3 WHERE id = ?4",
            params![format!("{:?}", status), now, detail, activity_id],
        )?;
        Ok(())
    }

    /// Get all agent activities for a scroll.
    pub async fn get_agent_activities_for_scroll(
        &self,
        scroll_id: &str,
    ) -> Result<Vec<crate::types::AgentActivity>> {
        let conn = self.conn.lock().await;
        let mut stmt = conn.prepare(
            "SELECT id, scroll_id, task_id, agent_name, action, detail, started_at, completed_at, status
             FROM agent_activities WHERE scroll_id = ?1 ORDER BY started_at"
        )?;

        let mut activities = Vec::new();
        let rows = stmt.query_map(params![scroll_id], |row| {
            let started_str: String = row.get(6)?;
            let completed_str: Option<String> = row.get(7)?;
            let status_str: String = row.get(8)?;

            Ok(crate::types::AgentActivity {
                id: row.get(0)?,
                scroll_id: row.get(1)?,
                task_id: row.get(2)?,
                agent_name: row.get(3)?,
                action: row.get(4)?,
                detail: row.get(5)?,
                started_at: chrono::DateTime::parse_from_rfc3339(&started_str)
                    .map(|dt| dt.with_timezone(&Utc))
                    .unwrap_or_else(|_| Utc::now()),
                completed_at: completed_str.and_then(|s| {
                    chrono::DateTime::parse_from_rfc3339(&s)
                        .map(|dt| dt.with_timezone(&Utc))
                        .ok()
                }),
                status: Self::parse_agent_activity_status(&status_str),
            })
        })?;

        for row in rows {
            activities.push(row?);
        }

        Ok(activities)
    }

    // ===== CLEANUP Operations =====

    /// Delete scrolls whose cleanup_after has passed, along with all child records.
    pub async fn cleanup_expired_scrolls(&self) -> Result<CleanupStats> {
        let conn = self.conn.lock().await;
        let now = Utc::now().to_rfc3339();

        // Find expired scroll IDs.
        let mut stmt = conn.prepare(
            "SELECT id FROM scrolls WHERE cleanup_after IS NOT NULL AND cleanup_after < ?1"
        )?;
        let expired_ids: Vec<String> = stmt
            .query_map(params![now], |row| row.get(0))?
            .filter_map(|r| r.ok())
            .collect();

        if expired_ids.is_empty() {
            return Ok(CleanupStats::default());
        }

        let mut stats = CleanupStats::default();

        for scroll_id in &expired_ids {
            // Cascade delete children
            stats.tasks_deleted += conn.execute(
                "DELETE FROM tasks WHERE scroll_id = ?1", params![scroll_id],
            )?;
            stats.validation_reports_deleted += conn.execute(
                "DELETE FROM validation_reports WHERE scroll_id = ?1", params![scroll_id],
            )?;
            stats.auto_forge_logs_deleted += conn.execute(
                "DELETE FROM auto_forge_logs WHERE scroll_id = ?1", params![scroll_id],
            )?;
            // Delete agent activities (no counter in CleanupStats, use lifecycle)
            let _ = conn.execute(
                "DELETE FROM agent_activities WHERE scroll_id = ?1", params![scroll_id],
            );
            stats.external_refs_deleted += conn.execute(
                "DELETE FROM external_refs WHERE scroll_id = ?1", params![scroll_id],
            )?;
            stats.lifecycle_events_deleted += conn.execute(
                "DELETE FROM lifecycle_events WHERE entity_type = 'scroll' AND entity_id = ?1",
                params![scroll_id],
            )?;

            // Delete the scroll itself
            conn.execute("DELETE FROM scrolls WHERE id = ?1", params![scroll_id])?;
            stats.scrolls_deleted += 1;
        }

        Ok(stats)
    }

    /// Delete search queries older than the given retention period.
    pub async fn cleanup_old_search_queries(&self, retention_days: i64) -> Result<usize> {
        let conn = self.conn.lock().await;
        let cutoff = Utc::now().timestamp() - (retention_days * 86400);
        let deleted = conn.execute(
            "DELETE FROM search_queries WHERE created_at < ?1",
            params![cutoff],
        )?;
        Ok(deleted)
    }

    // ===== DAILY SPEND Operations =====

    /// Get the accumulated daily spend in micro-dollars for a given date string (YYYY-MM-DD).
    pub async fn get_daily_spend(&self, date: &str) -> Result<u64> {
        let conn = self.conn.lock().await;
        let mut stmt = conn.prepare(
            "SELECT spend_micros FROM daily_spend WHERE date = ?1"
        )?;
        match stmt.query_row(params![date], |row| row.get::<_, i64>(0)) {
            Ok(v) => Ok(v as u64),
            Err(rusqlite::Error::QueryReturnedNoRows) => Ok(0),
            Err(e) => Err(e.into()),
        }
    }

    /// Add micro-dollars to the daily spend for a given date string.
    pub async fn add_daily_spend(&self, date: &str, micros: u64) -> Result<()> {
        let conn = self.conn.lock().await;
        conn.execute(
            "INSERT INTO daily_spend (date, spend_micros) VALUES (?1, ?2)
             ON CONFLICT(date) DO UPDATE SET spend_micros = spend_micros + excluded.spend_micros",
            params![date, micros as i64],
        )?;
        Ok(())
    }

    // ===== LEARNED STRATEGY (full row) =====

    /// Get the full learned strategy row for a query pattern.
    pub async fn get_learned_strategy_full(
        &self,
        query_pattern: &str,
    ) -> Result<Option<LearnedStrategy>> {
        let conn = self.conn.lock().await;
        let mut stmt = conn.prepare(
            "SELECT query_pattern, best_strategy, success_rate, avg_latency_ms, avg_cost_cents, sample_count, last_updated
             FROM learned_strategies WHERE query_pattern = ?1"
        )?;

        let result = stmt.query_row(params![query_pattern], |row| {
            Ok(LearnedStrategy {
                query_pattern: row.get(0)?,
                best_strategy: row.get(1)?,
                success_rate: row.get(2)?,
                avg_latency_ms: row.get(3)?,
                avg_cost_cents: row.get(4)?,
                sample_count: row.get(5)?,
                last_updated: row.get(6)?,
            })
        });

        match result {
            Ok(strategy) => Ok(Some(strategy)),
            Err(rusqlite::Error::QueryReturnedNoRows) => Ok(None),
            Err(e) => Err(e.into()),
        }
    }

    // ===== Helper functions =====

    fn parse_scroll_status(s: &str) -> ScrollStatus {
        match s {
            "Draft" => ScrollStatus::Draft,
            "Validated" => ScrollStatus::Validated,
            "InProgress" => ScrollStatus::InProgress,
            "Completed" => ScrollStatus::Completed,
            "Failed" => ScrollStatus::Failed,
            "Archived" => ScrollStatus::Archived,
            _ => ScrollStatus::Draft,
        }
    }

    fn parse_task_type(s: &str) -> TaskType {
        match s {
            "Create" => TaskType::Create,
            "Modify" => TaskType::Modify,
            "Delete" => TaskType::Delete,
            "Test" => TaskType::Test,
            _ => TaskType::Create,
        }
    }

    fn parse_task_status(s: &str) -> TaskStatus {
        match s {
            "Pending" => TaskStatus::Pending,
            "InProgress" => TaskStatus::InProgress,
            "Completed" => TaskStatus::Completed,
            "Failed" => TaskStatus::Failed,
            "Skipped" => TaskStatus::Skipped,
            _ => TaskStatus::Pending,
        }
    }

    fn parse_external_system(s: &str) -> crate::sync::types::ExternalSystem {
        match s {
            "Linear" => crate::sync::types::ExternalSystem::Linear,
            "Jira" => crate::sync::types::ExternalSystem::Jira,
            "Notion" => crate::sync::types::ExternalSystem::Notion,
            _ => crate::sync::types::ExternalSystem::Linear,
        }
    }

    fn parse_agent_activity_status(s: &str) -> crate::types::AgentActivityStatus {
        match s {
            "Running" => crate::types::AgentActivityStatus::Running,
            "Completed" => crate::types::AgentActivityStatus::Completed,
            "Failed" => crate::types::AgentActivityStatus::Failed,
            _ => crate::types::AgentActivityStatus::Running,
        }
    }

    fn parse_sync_direction(s: &str) -> crate::sync::types::SyncDirection {
        match s {
            "Push" => crate::sync::types::SyncDirection::Push,
            "Pull" => crate::sync::types::SyncDirection::Pull,
            "Bidirectional" => crate::sync::types::SyncDirection::Bidirectional,
            _ => crate::sync::types::SyncDirection::Bidirectional,
        }
    }
}

// ===========================================================================
// Tests
// ===========================================================================

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

    async fn make_db() -> (Arc<SqliteDb>, NamedTempFile) {
        let tmp = NamedTempFile::new().expect("temp file");
        let db = Arc::new(SqliteDb::new(tmp.path()).expect("sqlite"));
        (db, tmp)
    }

    fn make_scroll(id: &str, status: ScrollStatus) -> Scroll {
        let now = Utc::now();
        Scroll {
            id: id.into(),
            title: format!("Test {}", id),
            intent: "testing".into(),
            constraints: vec![],
            tasks: vec![],
            metadata: ScrollMetadata {
                execution_mode: ExecutionMode::Forge,
                auto_forge_config: None,
                recipe_used: None,
                search_context: vec![],
            },
            status,
            created_at: now,
            updated_at: now,
            cleanup_after: None,
        }
    }

    // -- Phase 1: Lifecycle Cleanup Tests --

    #[tokio::test]
    async fn test_status_transition_sets_cleanup_after() {
        let (db, _tmp) = make_db().await;
        db.create_scroll(&make_scroll("s1", ScrollStatus::Draft)).await.unwrap();

        // Walk through valid transitions: Draft → Validated → InProgress → Completed
        db.update_scroll_status("s1", &ScrollStatus::Validated).await.unwrap();
        db.update_scroll_status("s1", &ScrollStatus::InProgress).await.unwrap();
        db.update_scroll_status("s1", &ScrollStatus::Completed).await.unwrap();
        let scroll = db.get_scroll("s1").await.unwrap();
        assert!(scroll.cleanup_after.is_some(), "Completed should set cleanup_after");

        let cleanup = scroll.cleanup_after.unwrap();
        let diff = cleanup - Utc::now();
        // Should be ~24 hours in the future (allow some margin)
        assert!(diff.num_hours() >= 23 && diff.num_hours() <= 25);
    }

    #[tokio::test]
    async fn test_status_transition_clears_cleanup_after() {
        let (db, _tmp) = make_db().await;
        db.create_scroll(&make_scroll("s2", ScrollStatus::Draft)).await.unwrap();

        // Walk through valid transitions: Draft → Validated → InProgress → Failed
        db.update_scroll_status("s2", &ScrollStatus::Validated).await.unwrap();
        db.update_scroll_status("s2", &ScrollStatus::InProgress).await.unwrap();
        db.update_scroll_status("s2", &ScrollStatus::Failed).await.unwrap();
        let scroll = db.get_scroll("s2").await.unwrap();
        assert!(scroll.cleanup_after.is_some(), "Failed should set cleanup_after");

        // Resurrection (Failed → InProgress) is now forbidden
        let err = db.update_scroll_status("s2", &ScrollStatus::InProgress).await;
        assert!(err.is_err(), "Failed → InProgress should be rejected");
    }

    #[tokio::test]
    async fn test_cleanup_expired_scrolls_deletes_past_ttl() {
        let (db, _tmp) = make_db().await;

        // Create a scroll with cleanup_after in the past
        let mut scroll = make_scroll("expired", ScrollStatus::Completed);
        scroll.cleanup_after = Some(Utc::now() - chrono::Duration::hours(2));
        db.create_scroll(&scroll).await.unwrap();

        // Create a child task
        let task = Task {
            id: "t1".into(),
            scroll_id: "expired".into(),
            description: "test task".into(),
            target_file: "test.rs".into(),
            task_type: TaskType::Create,
            status: TaskStatus::Completed,
            dependencies: vec![],
            generated_code: None,
            diff: None,
            created_at: Utc::now(),
            group_label: None,
        };
        db.create_task(&task).await.unwrap();

        let stats = db.cleanup_expired_scrolls().await.unwrap();
        assert_eq!(stats.scrolls_deleted, 1);
        assert_eq!(stats.tasks_deleted, 1);

        // Scroll should be gone
        assert!(db.get_scroll("expired").await.is_err());
    }

    #[tokio::test]
    async fn test_cleanup_preserves_within_ttl() {
        let (db, _tmp) = make_db().await;

        let mut scroll = make_scroll("future", ScrollStatus::Completed);
        scroll.cleanup_after = Some(Utc::now() + chrono::Duration::hours(20));
        db.create_scroll(&scroll).await.unwrap();

        let stats = db.cleanup_expired_scrolls().await.unwrap();
        assert_eq!(stats.scrolls_deleted, 0);

        // Scroll should still exist
        assert!(db.get_scroll("future").await.is_ok());
    }

    #[tokio::test]
    async fn test_cleanup_preserves_in_progress() {
        let (db, _tmp) = make_db().await;
        db.create_scroll(&make_scroll("active", ScrollStatus::InProgress)).await.unwrap();

        let stats = db.cleanup_expired_scrolls().await.unwrap();
        assert_eq!(stats.scrolls_deleted, 0);
        assert!(db.get_scroll("active").await.is_ok());
    }

    #[tokio::test]
    async fn test_cleanup_old_search_queries() {
        let (db, _tmp) = make_db().await;

        // Insert a search query with a timestamp 100 days ago
        let old_ts = Utc::now().timestamp() - (100 * 86400);
        {
            let conn = db.conn.lock().await;
            conn.execute(
                "INSERT INTO search_queries (id, query, query_type, strategy_used, tier1_tried, tier1_success, tier2_tried, tier2_success, tier3_tried, tier3_success, latency_ms, cost_cents, result_count, created_at)
                 VALUES ('old-q', 'test', 'Semantic', 'Tier1Only', 0, 0, 0, 0, 0, 0, 10, 0, 0, ?1)",
                params![old_ts],
            ).unwrap();
        }

        let deleted = db.cleanup_old_search_queries(90).await.unwrap();
        assert_eq!(deleted, 1);
    }

    // -- Phase 2: Daily Spend Tests --

    #[tokio::test]
    async fn test_daily_spend_roundtrip() {
        let (db, _tmp) = make_db().await;

        assert_eq!(db.get_daily_spend("2026-02-07").await.unwrap(), 0);

        db.add_daily_spend("2026-02-07", 500_000).await.unwrap();
        assert_eq!(db.get_daily_spend("2026-02-07").await.unwrap(), 500_000);

        db.add_daily_spend("2026-02-07", 300_000).await.unwrap();
        assert_eq!(db.get_daily_spend("2026-02-07").await.unwrap(), 800_000);
    }

    // -- Phase 3: Learned Strategy Full Tests --

    #[tokio::test]
    async fn test_get_learned_strategy_full() {
        let (db, _tmp) = make_db().await;

        db.upsert_learned_strategy("Semantic", "Tier1ThenTier2", 0.75, 100, 5, 50).await.unwrap();
        let strategy = db.get_learned_strategy_full("Semantic").await.unwrap();
        assert!(strategy.is_some());
        let s = strategy.unwrap();
        assert_eq!(s.best_strategy, "Tier1ThenTier2");
        assert!((s.success_rate - 0.75).abs() < f64::EPSILON);
        assert_eq!(s.sample_count, 50);

        assert!(db.get_learned_strategy_full("Unknown").await.unwrap().is_none());
    }
}
