//! Chat session, message, and attachment CRUD methods on `Database`.
//!
//! This file contributes a `impl Database { ... }` block to the type defined
//! in `super::Database`. Multiple `impl` blocks on the same type across files
//! are idiomatic Rust; the public method paths resolve identically to a
//! single-block layout.

use rusqlite::{OptionalExtension, params};

use crate::model::{AgentStatus, Attachment, AttachmentOrigin, ChatMessage, ChatSession};

use super::Database;

fn row_to_attachment(row: &rusqlite::Row) -> rusqlite::Result<Attachment> {
    let data: Vec<u8> = row.get(4)?;
    let origin_str: String = row.get(9)?;
    Ok(Attachment {
        id: row.get(0)?,
        message_id: row.get(1)?,
        filename: row.get(2)?,
        media_type: row.get(3)?,
        size_bytes: row.get(7)?,
        data,
        width: row.get(5)?,
        height: row.get(6)?,
        created_at: row.get(8)?,
        origin: AttachmentOrigin::from_sql_str(&origin_str),
        tool_use_id: row.get(10)?,
    })
}

const ATTACHMENT_COLUMNS: &str = "id, message_id, filename, media_type, data, width, height, size_bytes, created_at, origin, tool_use_id";

impl Database {
    // --- Chat Messages ---

    #[allow(dead_code)]
    pub fn insert_chat_message(&self, msg: &ChatMessage) -> Result<(), rusqlite::Error> {
        self.conn.execute(
            "INSERT INTO chat_messages (
                id, workspace_id, chat_session_id, role, content, cost_usd, duration_ms, thinking,
                input_tokens, output_tokens, cache_read_tokens, cache_creation_tokens
             ) VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12)",
            params![
                msg.id,
                msg.workspace_id,
                msg.chat_session_id,
                msg.role.as_str(),
                msg.content,
                msg.cost_usd,
                msg.duration_ms,
                msg.thinking,
                msg.input_tokens,
                msg.output_tokens,
                msg.cache_read_tokens,
                msg.cache_creation_tokens,
            ],
        )?;
        Ok(())
    }

    pub(super) fn parse_chat_message_row(row: &rusqlite::Row) -> rusqlite::Result<ChatMessage> {
        let role_str: String = row.get(3)?;
        let chat_session_id: String = row.get::<_, Option<String>>(2)?.unwrap_or_default();
        let role = role_str.parse().map_err(|e| {
            rusqlite::Error::FromSqlConversionFailure(3, rusqlite::types::Type::Text, Box::new(e))
        })?;
        Ok(ChatMessage {
            id: row.get(0)?,
            workspace_id: row.get(1)?,
            chat_session_id,
            role,
            content: row.get(4)?,
            cost_usd: row.get(5)?,
            duration_ms: row.get(6)?,
            created_at: row.get(7)?,
            thinking: row.get(8)?,
            input_tokens: row.get(9)?,
            output_tokens: row.get(10)?,
            cache_read_tokens: row.get(11)?,
            cache_creation_tokens: row.get(12)?,
        })
    }

    pub(super) const CHAT_MESSAGE_COLS: &str = "id, workspace_id, chat_session_id, role, content, cost_usd, \
         duration_ms, created_at, thinking, input_tokens, output_tokens, cache_read_tokens, \
         cache_creation_tokens";

    /// Predicate that filters out legacy empty assistant rows (assistant role,
    /// empty content, no thinking text). The frontend used to drop these in
    /// the renderer; we apply it here so `count_chat_messages_for_session`
    /// and `list_chat_messages_page` agree on what "exists" — which keeps the
    /// `globalOffset = totalCount - messages.length` invariant honest.
    const NON_LEGACY_MESSAGE_PREDICATE: &str =
        "NOT (role = 'assistant' AND TRIM(content) = '' AND COALESCE(TRIM(thinking), '') = '')";

    #[allow(dead_code)]
    /// For each workspace that has at least one chat message, return the
    /// number of seconds elapsed since its most recent message. Used to
    /// seed the SCM polling loop's per-workspace activity map after an
    /// app restart so workspaces with recent chat history start in the
    /// hot tier rather than the stale tier.
    pub fn workspace_last_activity_seconds_ago(
        &self,
    ) -> Result<Vec<(String, i64)>, rusqlite::Error> {
        let mut stmt = self.conn.prepare(
            "SELECT workspace_id, \
                    CAST(strftime('%s', 'now') - strftime('%s', MAX(created_at)) AS INTEGER) \
             FROM chat_messages GROUP BY workspace_id",
        )?;
        let rows = stmt.query_map([], |row| {
            Ok((row.get::<_, String>(0)?, row.get::<_, i64>(1)?))
        })?;
        rows.collect()
    }

    pub fn list_chat_messages(
        &self,
        workspace_id: &str,
    ) -> Result<Vec<ChatMessage>, rusqlite::Error> {
        let sql = format!(
            "SELECT {} FROM chat_messages WHERE workspace_id = ?1 ORDER BY created_at, rowid",
            Self::CHAT_MESSAGE_COLS
        );
        let mut stmt = self.conn.prepare(&sql)?;
        let rows = stmt.query_map(params![workspace_id], Self::parse_chat_message_row)?;
        rows.collect()
    }

    /// List all chat messages for a single chat session, ordered chronologically.
    pub fn list_chat_messages_for_session(
        &self,
        chat_session_id: &str,
    ) -> Result<Vec<ChatMessage>, rusqlite::Error> {
        let sql = format!(
            "SELECT {} FROM chat_messages WHERE chat_session_id = ?1 ORDER BY created_at, rowid",
            Self::CHAT_MESSAGE_COLS
        );
        let mut stmt = self.conn.prepare(&sql)?;
        let rows = stmt.query_map(params![chat_session_id], Self::parse_chat_message_row)?;
        rows.collect()
    }

    pub fn last_chat_message_id_for_session(
        &self,
        chat_session_id: &str,
    ) -> Result<Option<String>, rusqlite::Error> {
        self.conn
            .query_row(
                "SELECT id FROM chat_messages
                 WHERE chat_session_id = ?1
                 ORDER BY created_at DESC, rowid DESC
                 LIMIT 1",
                params![chat_session_id],
                |row| row.get::<_, String>(0),
            )
            .optional()
    }

    /// Count all non-legacy messages for a session (legacy = empty assistant
    /// rows; see `NON_LEGACY_MESSAGE_PREDICATE`). Used to compute pagination
    /// metadata (`total_count`) so callers can derive the global index offset
    /// of any loaded page without a separate round-trip.
    pub fn count_chat_messages_for_session(
        &self,
        chat_session_id: &str,
    ) -> Result<i64, rusqlite::Error> {
        let sql = format!(
            "SELECT COUNT(*) FROM chat_messages
             WHERE chat_session_id = ?1
               AND {predicate}",
            predicate = Self::NON_LEGACY_MESSAGE_PREDICATE,
        );
        self.conn
            .query_row(&sql, params![chat_session_id], |row| row.get(0))
    }

    /// Return up to `limit` messages for a session in ascending chronological
    /// order. When `before_message_id` is `None` the newest `limit` messages
    /// are returned; when it is `Some(id)` only messages whose `rowid` is
    /// strictly less than the row identified by that id are considered, so the
    /// caller can page backwards through history by passing the id of the
    /// oldest already-loaded message as the cursor.
    ///
    /// Legacy empty assistant rows are excluded — see
    /// `NON_LEGACY_MESSAGE_PREDICATE`.
    pub fn list_chat_messages_page(
        &self,
        chat_session_id: &str,
        limit: i64,
        before_message_id: Option<&str>,
    ) -> Result<Vec<ChatMessage>, rusqlite::Error> {
        let mut rows: Vec<ChatMessage> = match before_message_id {
            None => {
                let sql = format!(
                    "SELECT {cols} FROM chat_messages
                     WHERE chat_session_id = ?1
                       AND {predicate}
                     ORDER BY created_at DESC, rowid DESC
                     LIMIT ?2",
                    cols = Self::CHAT_MESSAGE_COLS,
                    predicate = Self::NON_LEGACY_MESSAGE_PREDICATE,
                );
                let mut stmt = self.conn.prepare(&sql)?;
                stmt.query_map(
                    params![chat_session_id, limit],
                    Self::parse_chat_message_row,
                )?
                .collect::<Result<Vec<_>, _>>()?
            }
            Some(cursor_id) => {
                let sql = format!(
                    "SELECT {cols} FROM chat_messages
                     WHERE chat_session_id = ?1
                       AND rowid < (SELECT rowid FROM chat_messages WHERE id = ?3)
                       AND {predicate}
                     ORDER BY created_at DESC, rowid DESC
                     LIMIT ?2",
                    cols = Self::CHAT_MESSAGE_COLS,
                    predicate = Self::NON_LEGACY_MESSAGE_PREDICATE,
                );
                let mut stmt = self.conn.prepare(&sql)?;
                stmt.query_map(
                    params![chat_session_id, limit, cursor_id],
                    Self::parse_chat_message_row,
                )?
                .collect::<Result<Vec<_>, _>>()?
            }
        };
        // Reverse so callers receive messages in ascending chronological order.
        rows.reverse();
        Ok(rows)
    }

    /// For pagination: find the most recent user message strictly older than
    /// the given message id, in the same session. Used to recover agent
    /// attachments anchored to a user message that lives on the previous page
    /// when the new page begins mid-turn.
    pub fn previous_user_message_id(
        &self,
        chat_session_id: &str,
        before_message_id: &str,
    ) -> Result<Option<String>, rusqlite::Error> {
        self.conn
            .query_row(
                "SELECT id FROM chat_messages
                 WHERE chat_session_id = ?1
                   AND role = 'user'
                   AND rowid < (SELECT rowid FROM chat_messages WHERE id = ?2)
                 ORDER BY rowid DESC
                 LIMIT 1",
                params![chat_session_id, before_message_id],
                |row| row.get::<_, String>(0),
            )
            .optional()
    }

    #[allow(dead_code)]
    pub fn update_chat_message_content(
        &self,
        id: &str,
        content: &str,
    ) -> Result<(), rusqlite::Error> {
        self.conn.execute(
            "UPDATE chat_messages SET content = ?1 WHERE id = ?2",
            params![content, id],
        )?;
        Ok(())
    }

    #[allow(dead_code)]
    pub fn update_chat_message_cost(
        &self,
        id: &str,
        cost_usd: f64,
        duration_ms: i64,
    ) -> Result<(), rusqlite::Error> {
        self.conn.execute(
            "UPDATE chat_messages SET cost_usd = ?1, duration_ms = ?2 WHERE id = ?3",
            params![cost_usd, duration_ms, id],
        )?;
        Ok(())
    }

    #[allow(dead_code)]
    pub fn update_chat_message_usage_if_missing(
        &self,
        id: &str,
        input_tokens: u64,
        output_tokens: u64,
        cache_read_tokens: Option<u64>,
        cache_creation_tokens: Option<u64>,
    ) -> Result<(), rusqlite::Error> {
        self.conn.execute(
            "UPDATE chat_messages
             SET input_tokens = COALESCE(input_tokens, ?1),
                 output_tokens = COALESCE(output_tokens, ?2),
                 cache_read_tokens = COALESCE(cache_read_tokens, ?3),
                 cache_creation_tokens = COALESCE(cache_creation_tokens, ?4)
             WHERE id = ?5",
            params![
                input_tokens as i64,
                output_tokens as i64,
                cache_read_tokens.map(|value| value as i64),
                cache_creation_tokens.map(|value| value as i64),
                id,
            ],
        )?;
        Ok(())
    }

    /// Get the most recent chat message for each workspace (for dashboard display).
    /// Uses a correlated subquery with rowid tie-breaking to guarantee exactly
    /// one row per workspace even when multiple messages share the same timestamp.
    pub fn last_message_per_workspace(&self) -> Result<Vec<ChatMessage>, rusqlite::Error> {
        // Prefix each column with `m.` so the correlated subquery references are unambiguous.
        let prefixed: String = Self::CHAT_MESSAGE_COLS
            .split(", ")
            .map(|c| format!("m.{}", c.trim()))
            .collect::<Vec<_>>()
            .join(", ");
        let sql = format!(
            "SELECT {prefixed} FROM chat_messages m
             WHERE m.rowid = (
                 SELECT rowid FROM chat_messages c2
                 WHERE c2.workspace_id = m.workspace_id
                 ORDER BY c2.created_at DESC, c2.rowid DESC
                 LIMIT 1
             )"
        );
        let mut stmt = self.conn.prepare(&sql)?;
        let rows = stmt.query_map([], Self::parse_chat_message_row)?;
        rows.collect()
    }

    #[allow(dead_code)]
    pub fn delete_chat_messages_for_workspace(
        &self,
        workspace_id: &str,
    ) -> Result<(), rusqlite::Error> {
        let deleted = self.conn.execute(
            "DELETE FROM chat_messages WHERE workspace_id = ?1",
            params![workspace_id],
        )?;
        self.best_effort_incremental_vacuum_after_delete(deleted);
        Ok(())
    }

    /// Delete all messages for a single chat session. Cascades to attachments.
    pub fn delete_chat_messages_for_session(
        &self,
        chat_session_id: &str,
    ) -> Result<(), rusqlite::Error> {
        let deleted = self.conn.execute(
            "DELETE FROM chat_messages WHERE chat_session_id = ?1",
            params![chat_session_id],
        )?;
        self.best_effort_incremental_vacuum_after_delete(deleted);
        Ok(())
    }

    pub fn delete_chat_message(&self, message_id: &str) -> Result<(), rusqlite::Error> {
        let deleted = self.conn.execute(
            "DELETE FROM chat_messages WHERE id = ?1",
            params![message_id],
        )?;
        self.best_effort_incremental_vacuum_after_delete(deleted);
        Ok(())
    }

    // --- Chat Sessions ---

    const CHAT_SESSION_COLS: &str = "id, workspace_id, session_id, name, name_edited, \
         turn_count, sort_order, status, created_at, archived_at, cli_invocation";

    fn parse_chat_session_row(row: &rusqlite::Row) -> rusqlite::Result<ChatSession> {
        let status_str: String = row.get(7)?;
        let status = status_str.parse().map_err(|e| {
            rusqlite::Error::FromSqlConversionFailure(7, rusqlite::types::Type::Text, Box::new(e))
        })?;
        Ok(ChatSession {
            id: row.get(0)?,
            workspace_id: row.get(1)?,
            session_id: row.get(2)?,
            name: row.get(3)?,
            name_edited: row.get::<_, i32>(4)? != 0,
            turn_count: row.get(5)?,
            sort_order: row.get(6)?,
            status,
            created_at: row.get(8)?,
            archived_at: row.get(9)?,
            cli_invocation: row.get(10)?,
            agent_status: AgentStatus::Idle,
            needs_attention: false,
            attention_kind: None,
        })
    }

    /// Insert a new active session. Returns the inserted row.
    pub fn create_chat_session(&self, workspace_id: &str) -> Result<ChatSession, rusqlite::Error> {
        // New sessions land at the end of the tab list. Surface DB errors so
        // a transient lock/read failure doesn't collapse the next sort_order
        // to 0 and produce duplicate tab-order values.
        let sort_order: i32 = self.conn.query_row(
            "SELECT COALESCE(MAX(sort_order), -1) + 1 FROM chat_sessions WHERE workspace_id = ?1",
            params![workspace_id],
            |row| row.get(0),
        )?;
        let id = uuid::Uuid::new_v4().to_string();
        self.conn.execute(
            "INSERT INTO chat_sessions
                (id, workspace_id, session_id, name, name_edited,
                 turn_count, sort_order, status)
             VALUES (?1, ?2, NULL, 'New chat', 0, 0, ?3, 'active')",
            params![id, workspace_id, sort_order],
        )?;
        self.get_chat_session(&id)?
            .ok_or_else(|| rusqlite::Error::QueryReturnedNoRows)
    }

    pub fn get_chat_session(
        &self,
        chat_session_id: &str,
    ) -> Result<Option<ChatSession>, rusqlite::Error> {
        let sql = format!(
            "SELECT {} FROM chat_sessions WHERE id = ?1",
            Self::CHAT_SESSION_COLS
        );
        self.conn
            .query_row(&sql, params![chat_session_id], Self::parse_chat_session_row)
            .optional()
    }

    /// List sessions for a workspace. `include_archived` toggles whether
    /// archived sessions are returned. Active sessions are always first,
    /// then archived; within each group, ordered by sort_order, then
    /// created_at as a stable tie-break.
    pub fn list_chat_sessions_for_workspace(
        &self,
        workspace_id: &str,
        include_archived: bool,
    ) -> Result<Vec<ChatSession>, rusqlite::Error> {
        let sql = if include_archived {
            format!(
                "SELECT {} FROM chat_sessions WHERE workspace_id = ?1
                 ORDER BY (status = 'archived'), sort_order, created_at",
                Self::CHAT_SESSION_COLS
            )
        } else {
            format!(
                "SELECT {} FROM chat_sessions
                 WHERE workspace_id = ?1 AND status = 'active'
                 ORDER BY sort_order, created_at",
                Self::CHAT_SESSION_COLS
            )
        };
        let mut stmt = self.conn.prepare(&sql)?;
        let rows = stmt.query_map(params![workspace_id], Self::parse_chat_session_row)?;
        rows.collect()
    }

    /// Rename a session. Sets `name_edited = 1` so Haiku auto-naming never
    /// overwrites the new name.
    pub fn rename_chat_session(
        &self,
        chat_session_id: &str,
        name: &str,
    ) -> Result<(), rusqlite::Error> {
        self.conn.execute(
            "UPDATE chat_sessions SET name = ?1, name_edited = 1 WHERE id = ?2",
            params![name, chat_session_id],
        )?;
        Ok(())
    }

    /// Set the captured `claude` invocation for a session. Only writes if
    /// the column is currently NULL — first-emit-wins, so a respawn caused
    /// by transient errors can't overwrite the original record.
    pub fn set_chat_session_cli_invocation(
        &self,
        chat_session_id: &str,
        invocation: &str,
    ) -> Result<(), rusqlite::Error> {
        self.conn.execute(
            "UPDATE chat_sessions SET cli_invocation = ?2 \
             WHERE id = ?1 AND cli_invocation IS NULL",
            params![chat_session_id, invocation],
        )?;
        Ok(())
    }

    /// Write a Haiku-generated session name — only if the user has not
    /// already renamed the session. Returns `true` if the name was written.
    pub fn set_session_name_from_haiku(
        &self,
        chat_session_id: &str,
        name: &str,
    ) -> Result<bool, rusqlite::Error> {
        let rows = self.conn.execute(
            "UPDATE chat_sessions SET name = ?1
             WHERE id = ?2 AND name_edited = 0",
            params![name, chat_session_id],
        )?;
        Ok(rows > 0)
    }

    /// Persist per-session Claude CLI state so turns can be resumed after a
    /// restart. Replaces the old workspace-scoped `save_agent_session`.
    pub fn save_chat_session_state(
        &self,
        chat_session_id: &str,
        session_id: &str,
        turn_count: u32,
    ) -> Result<(), rusqlite::Error> {
        self.conn.execute(
            "UPDATE chat_sessions SET session_id = ?1, turn_count = ?2 WHERE id = ?3",
            params![session_id, turn_count, chat_session_id],
        )?;
        Ok(())
    }

    /// Clear Claude CLI state (e.g. after a reset or failed init).
    pub fn clear_chat_session_state(&self, chat_session_id: &str) -> Result<(), rusqlite::Error> {
        self.conn.execute(
            "UPDATE chat_sessions
             SET session_id = NULL, turn_count = 0 WHERE id = ?1",
            params![chat_session_id],
        )?;
        Ok(())
    }

    /// Archive a session (soft-delete). Messages and checkpoints remain so
    /// they can be restored or purged later.
    pub fn archive_chat_session(&self, chat_session_id: &str) -> Result<(), rusqlite::Error> {
        self.conn.execute(
            "UPDATE chat_sessions
             SET status = 'archived', archived_at = datetime('now')
             WHERE id = ?1",
            params![chat_session_id],
        )?;
        Ok(())
    }

    /// Restore a previously archived session: flip its status back to
    /// active and clear `archived_at`. Used by the workspace empty-tabs
    /// view's "resume previous session" affordance. Returns the rehydrated
    /// session row; errors if the id doesn't exist.
    pub fn restore_chat_session(
        &self,
        chat_session_id: &str,
    ) -> Result<ChatSession, rusqlite::Error> {
        self.conn.execute(
            "UPDATE chat_sessions
             SET status = 'active', archived_at = NULL
             WHERE id = ?1",
            params![chat_session_id],
        )?;
        let sql = format!(
            "SELECT {} FROM chat_sessions WHERE id = ?1",
            Self::CHAT_SESSION_COLS
        );
        self.conn
            .query_row(&sql, params![chat_session_id], Self::parse_chat_session_row)
    }

    /// Archive a session without creating a replacement. Used by the
    /// "close last tab" path that wants the workspace's empty-tabs view
    /// to surface; callers that need the always-≥1 invariant should use
    /// [`Self::archive_chat_session_ensuring_active`] instead.
    pub fn archive_chat_session_only(&self, chat_session_id: &str) -> Result<(), rusqlite::Error> {
        self.conn.execute(
            "UPDATE chat_sessions
             SET status = 'archived', archived_at = datetime('now')
             WHERE id = ?1",
            params![chat_session_id],
        )?;
        Ok(())
    }

    /// Archive a session while preserving the "workspace has ≥1 active
    /// session" invariant. Runs archive + remaining-count + conditional
    /// replacement create as a single transaction so observers never see a
    /// transient zero-sessions window and a crash mid-sequence can't persist
    /// a tab-less workspace. Returns the newly created session if a
    /// replacement was needed, `None` otherwise.
    pub fn archive_chat_session_ensuring_active(
        &self,
        chat_session_id: &str,
        workspace_id: &str,
    ) -> Result<Option<ChatSession>, rusqlite::Error> {
        let tx = self.conn.unchecked_transaction()?;
        tx.execute(
            "UPDATE chat_sessions
             SET status = 'archived', archived_at = datetime('now')
             WHERE id = ?1",
            params![chat_session_id],
        )?;
        let remaining: i64 = tx.query_row(
            "SELECT COUNT(*) FROM chat_sessions
             WHERE workspace_id = ?1 AND status = 'active'",
            params![workspace_id],
            |row| row.get(0),
        )?;
        let fresh = if remaining == 0 {
            let sort_order: i32 = tx.query_row(
                "SELECT COALESCE(MAX(sort_order), -1) + 1 FROM chat_sessions WHERE workspace_id = ?1",
                params![workspace_id],
                |row| row.get(0),
            )?;
            let id = uuid::Uuid::new_v4().to_string();
            tx.execute(
                "INSERT INTO chat_sessions
                    (id, workspace_id, session_id, name, name_edited,
                     turn_count, sort_order, status)
                 VALUES (?1, ?2, NULL, 'New chat', 0, 0, ?3, 'active')",
                params![id, workspace_id, sort_order],
            )?;
            let sql = format!(
                "SELECT {} FROM chat_sessions WHERE id = ?1",
                Self::CHAT_SESSION_COLS
            );
            Some(tx.query_row(&sql, params![id], Self::parse_chat_session_row)?)
        } else {
            None
        };
        tx.commit()?;
        Ok(fresh)
    }

    /// Return the "default" session id for a workspace: the first active
    /// session, ordered by sort_order. Returns `None` when no active session
    /// exists (caller can create one to enforce the ≥1 invariant).
    pub fn default_session_id_for_workspace(
        &self,
        workspace_id: &str,
    ) -> Result<Option<String>, rusqlite::Error> {
        self.conn
            .query_row(
                "SELECT id FROM chat_sessions
                 WHERE workspace_id = ?1 AND status = 'active'
                 ORDER BY sort_order, created_at LIMIT 1",
                params![workspace_id],
                |row| row.get::<_, String>(0),
            )
            .optional()
    }

    /// Count of active sessions for a workspace. Used to enforce the
    /// "every workspace has ≥1 active session" invariant when archiving.
    pub fn active_session_count_for_workspace(
        &self,
        workspace_id: &str,
    ) -> Result<i64, rusqlite::Error> {
        self.conn.query_row(
            "SELECT COUNT(*) FROM chat_sessions
             WHERE workspace_id = ?1 AND status = 'active'",
            params![workspace_id],
            |row| row.get(0),
        )
    }

    /// Is this session the first (sort_order = 0) session for its workspace?
    /// Used to gate workspace-level branch auto-rename.
    pub fn is_initial_session(&self, chat_session_id: &str) -> Result<bool, rusqlite::Error> {
        let sort_order: Option<i32> = self
            .conn
            .query_row(
                "SELECT sort_order FROM chat_sessions WHERE id = ?1",
                params![chat_session_id],
                |row| row.get(0),
            )
            .optional()?;
        Ok(sort_order == Some(0))
    }

    /// Reassign `sort_order` of chat sessions within a workspace to match the
    /// supplied id sequence. Mirrors `reorder_repositories`. Ids that don't
    /// belong to `workspace_id` are ignored — the WHERE clause keeps cross-
    /// workspace edits from sneaking through. Wrapped in a transaction so a
    /// failed UPDATE leaves all sessions at their previous sort_order.
    pub fn reorder_chat_sessions(
        &self,
        workspace_id: &str,
        ids: &[String],
    ) -> Result<(), rusqlite::Error> {
        let tx = self.conn.unchecked_transaction()?;
        {
            let mut stmt = tx.prepare(
                "UPDATE chat_sessions SET sort_order = ?1 \
                 WHERE id = ?2 AND workspace_id = ?3",
            )?;
            for (i, id) in ids.iter().enumerate() {
                stmt.execute(params![i as i32, id, workspace_id])?;
            }
        }
        tx.commit()?;
        Ok(())
    }

    // --- Attachments ---

    pub fn insert_attachment(&self, att: &Attachment) -> Result<(), rusqlite::Error> {
        self.conn.execute(
            "INSERT INTO attachments (id, message_id, filename, media_type, data, width, height, size_bytes, origin, tool_use_id)
             VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10)",
            params![
                att.id,
                att.message_id,
                att.filename,
                att.media_type,
                att.data,
                att.width,
                att.height,
                att.size_bytes,
                att.origin.as_sql_str(),
                att.tool_use_id,
            ],
        )?;
        Ok(())
    }

    pub fn insert_attachments_batch(
        &self,
        attachments: &[Attachment],
    ) -> Result<(), rusqlite::Error> {
        if attachments.is_empty() {
            return Ok(());
        }
        let tx = self.conn.unchecked_transaction()?;
        for att in attachments {
            tx.execute(
                "INSERT INTO attachments (id, message_id, filename, media_type, data, width, height, size_bytes, origin, tool_use_id)
                 VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10)",
                params![
                    att.id,
                    att.message_id,
                    att.filename,
                    att.media_type,
                    att.data,
                    att.width,
                    att.height,
                    att.size_bytes,
                    att.origin.as_sql_str(),
                    att.tool_use_id,
                ],
            )?;
        }
        tx.commit()?;
        Ok(())
    }

    pub fn get_attachment(&self, id: &str) -> Result<Option<Attachment>, rusqlite::Error> {
        let sql = format!("SELECT {ATTACHMENT_COLUMNS} FROM attachments WHERE id = ?1");
        self.conn
            .query_row(&sql, params![id], row_to_attachment)
            .optional()
    }

    pub fn list_attachments_for_message(
        &self,
        message_id: &str,
    ) -> Result<Vec<Attachment>, rusqlite::Error> {
        let sql = format!(
            "SELECT {ATTACHMENT_COLUMNS} FROM attachments WHERE message_id = ?1 ORDER BY created_at"
        );
        let mut stmt = self.conn.prepare(&sql)?;
        let rows = stmt.query_map(params![message_id], row_to_attachment)?;
        rows.collect()
    }

    pub fn list_attachments_for_messages(
        &self,
        message_ids: &[String],
    ) -> Result<std::collections::HashMap<String, Vec<Attachment>>, rusqlite::Error> {
        use std::collections::HashMap;

        let mut result: HashMap<String, Vec<Attachment>> = HashMap::new();
        if message_ids.is_empty() {
            return Ok(result);
        }

        // Build a parameterised IN clause.
        let placeholders: Vec<String> = (1..=message_ids.len()).map(|i| format!("?{i}")).collect();
        let sql = format!(
            "SELECT {ATTACHMENT_COLUMNS} FROM attachments WHERE message_id IN ({})
             ORDER BY created_at",
            placeholders.join(", ")
        );

        let mut stmt = self.conn.prepare(&sql)?;
        let params: Vec<&dyn rusqlite::types::ToSql> = message_ids
            .iter()
            .map(|id| id as &dyn rusqlite::types::ToSql)
            .collect();
        let rows = stmt.query_map(&*params, row_to_attachment)?;

        for att in rows {
            let att = att?;
            result.entry(att.message_id.clone()).or_default().push(att);
        }
        Ok(result)
    }

    /// List agent-authored attachments associated with a specific MCP
    /// `tool_use_id`. Returns rows ordered by creation time so multiple
    /// `send_to_user` calls within a single tool activity render in order.
    pub fn list_attachments_by_tool_use(
        &self,
        tool_use_id: &str,
    ) -> Result<Vec<Attachment>, rusqlite::Error> {
        let sql = format!(
            "SELECT {ATTACHMENT_COLUMNS} FROM attachments
             WHERE tool_use_id = ?1 AND origin = 'agent' ORDER BY created_at"
        );
        let mut stmt = self.conn.prepare(&sql)?;
        let rows = stmt.query_map(params![tool_use_id], row_to_attachment)?;
        rows.collect()
    }
}

#[cfg(test)]
mod tests {
    use super::*;
    use crate::db::test_support::*;
    use crate::model::ChatRole;

    fn make_attachment(id: &str, message_id: &str, filename: &str) -> Attachment {
        Attachment {
            id: id.into(),
            message_id: message_id.into(),
            filename: filename.into(),
            media_type: "image/png".into(),
            data: vec![0x89, 0x50, 0x4E, 0x47, 0x0D, 0x0A, 0x1A, 0x0A], // PNG header
            width: Some(100),
            height: Some(200),
            size_bytes: 8,
            created_at: String::new(),
            origin: AttachmentOrigin::User,
            tool_use_id: None,
        }
    }

    // --- Chat message tests ---

    #[test]
    fn test_insert_and_list_chat_messages() {
        let db = setup_db_with_workspace();
        db.insert_chat_message(&make_chat_msg(&db, "m1", "w1", ChatRole::User, "hello"))
            .unwrap();
        db.insert_chat_message(&make_chat_msg(
            &db,
            "m2",
            "w1",
            ChatRole::Assistant,
            "hi there",
        ))
        .unwrap();
        let msgs = db.list_chat_messages("w1").unwrap();
        assert_eq!(msgs.len(), 2);
        assert_eq!(msgs[0].content, "hello");
        assert_eq!(msgs[1].content, "hi there");
    }

    #[test]
    fn test_last_chat_message_id_for_session_uses_rowid_tie_break() {
        let db = setup_db_with_workspace();
        let sid = db.default_session_id_for_workspace("w1").unwrap().unwrap();
        let other_session = db.create_chat_session("w1").unwrap();

        db.insert_chat_message(&make_chat_msg(&db, "m1", "w1", ChatRole::User, "first"))
            .unwrap();
        db.insert_chat_message(&make_chat_msg(
            &db,
            "m2",
            "w1",
            ChatRole::Assistant,
            "second",
        ))
        .unwrap();
        let mut other = make_chat_msg(&db, "m3", "w1", ChatRole::User, "other session");
        other.chat_session_id = other_session.id.clone();
        db.insert_chat_message(&other).unwrap();

        assert_eq!(
            db.last_chat_message_id_for_session(&sid)
                .unwrap()
                .as_deref(),
            Some("m2")
        );
        assert_eq!(
            db.last_chat_message_id_for_session(&other_session.id)
                .unwrap()
                .as_deref(),
            Some("m3")
        );
        assert_eq!(
            db.last_chat_message_id_for_session("missing").unwrap(),
            None
        );
    }

    #[test]
    fn test_chat_messages_filtered_by_workspace() {
        let db = setup_db_with_workspace();
        db.insert_workspace(&make_workspace("w2", "r1", "feature"))
            .unwrap();
        db.insert_chat_message(&make_chat_msg(&db, "m1", "w1", ChatRole::User, "for w1"))
            .unwrap();
        db.insert_chat_message(&make_chat_msg(&db, "m2", "w2", ChatRole::User, "for w2"))
            .unwrap();
        let msgs = db.list_chat_messages("w1").unwrap();
        assert_eq!(msgs.len(), 1);
        assert_eq!(msgs[0].content, "for w1");
    }

    #[test]
    fn test_update_chat_message_content() {
        let db = setup_db_with_workspace();
        db.insert_chat_message(&make_chat_msg(
            &db,
            "m1",
            "w1",
            ChatRole::Assistant,
            "partial",
        ))
        .unwrap();
        db.update_chat_message_content("m1", "partial response complete")
            .unwrap();
        let msgs = db.list_chat_messages("w1").unwrap();
        assert_eq!(msgs[0].content, "partial response complete");
    }

    #[test]
    fn test_update_chat_message_cost() {
        let db = setup_db_with_workspace();
        db.insert_chat_message(&make_chat_msg(&db, "m1", "w1", ChatRole::Assistant, "done"))
            .unwrap();
        db.update_chat_message_cost("m1", 0.005, 2000).unwrap();
        let msgs = db.list_chat_messages("w1").unwrap();
        assert!((msgs[0].cost_usd.unwrap() - 0.005).abs() < f64::EPSILON);
        assert_eq!(msgs[0].duration_ms.unwrap(), 2000);
    }

    #[test]
    fn test_delete_chat_messages_for_workspace() {
        let db = setup_db_with_workspace();
        db.insert_chat_message(&make_chat_msg(&db, "m1", "w1", ChatRole::User, "msg1"))
            .unwrap();
        db.insert_chat_message(&make_chat_msg(&db, "m2", "w1", ChatRole::Assistant, "msg2"))
            .unwrap();
        db.delete_chat_messages_for_workspace("w1").unwrap();
        let msgs = db.list_chat_messages("w1").unwrap();
        assert!(msgs.is_empty());
    }

    #[test]
    fn test_chat_messages_cascade_on_workspace_delete() {
        let db = setup_db_with_workspace();
        db.insert_chat_message(&make_chat_msg(&db, "m1", "w1", ChatRole::User, "hello"))
            .unwrap();
        db.insert_chat_message(&make_chat_msg(&db, "m2", "w1", ChatRole::Assistant, "hi"))
            .unwrap();
        db.delete_workspace("w1").unwrap();
        let msgs = db.list_chat_messages("w1").unwrap();
        assert!(msgs.is_empty());
    }

    #[test]
    fn test_chat_message_role_roundtrip() {
        let db = setup_db_with_workspace();
        db.insert_chat_message(&make_chat_msg(&db, "m1", "w1", ChatRole::User, "user msg"))
            .unwrap();
        db.insert_chat_message(&make_chat_msg(
            &db,
            "m2",
            "w1",
            ChatRole::Assistant,
            "asst msg",
        ))
        .unwrap();
        db.insert_chat_message(&make_chat_msg(&db, "m3", "w1", ChatRole::System, "sys msg"))
            .unwrap();
        let msgs = db.list_chat_messages("w1").unwrap();
        assert_eq!(msgs[0].role, ChatRole::User);
        assert_eq!(msgs[1].role, ChatRole::Assistant);
        assert_eq!(msgs[2].role, ChatRole::System);
    }

    #[test]
    fn test_chat_message_tokens_round_trip() {
        let db = setup_db_with_workspace();
        let mut msg = make_chat_msg(&db, "mt1", "w1", ChatRole::Assistant, "hello");
        msg.input_tokens = Some(1234);
        msg.output_tokens = Some(56);
        msg.cache_read_tokens = Some(100_000);
        msg.cache_creation_tokens = Some(7_000);
        db.insert_chat_message(&msg).unwrap();

        let msgs = db.list_chat_messages("w1").unwrap();
        assert_eq!(msgs.len(), 1);
        assert_eq!(msgs[0].input_tokens, Some(1234));
        assert_eq!(msgs[0].output_tokens, Some(56));
        assert_eq!(msgs[0].cache_read_tokens, Some(100_000));
        assert_eq!(msgs[0].cache_creation_tokens, Some(7_000));
    }

    #[test]
    fn test_chat_message_tokens_null_round_trip() {
        let db = setup_db_with_workspace();
        db.insert_chat_message(&make_chat_msg(&db, "mt2", "w1", ChatRole::Assistant, "hi"))
            .unwrap();

        let msgs = db.list_chat_messages("w1").unwrap();
        assert_eq!(msgs.len(), 1);
        assert_eq!(msgs[0].input_tokens, None);
        assert_eq!(msgs[0].output_tokens, None);
        assert_eq!(msgs[0].cache_read_tokens, None);
        assert_eq!(msgs[0].cache_creation_tokens, None);
    }

    #[test]
    fn test_update_chat_message_usage_if_missing_preserves_existing_values() {
        let db = setup_db_with_workspace();
        let mut msg = make_chat_msg(&db, "mt3", "w1", ChatRole::Assistant, "hello");
        msg.input_tokens = Some(12);
        db.insert_chat_message(&msg).unwrap();

        db.update_chat_message_usage_if_missing("mt3", 100, 20, Some(30), Some(40))
            .unwrap();

        let msgs = db.list_chat_messages("w1").unwrap();
        assert_eq!(msgs[0].input_tokens, Some(12));
        assert_eq!(msgs[0].output_tokens, Some(20));
        assert_eq!(msgs[0].cache_read_tokens, Some(30));
        assert_eq!(msgs[0].cache_creation_tokens, Some(40));
    }

    // --- Pagination tests ---

    fn session_id_for(db: &Database, workspace_id: &str) -> String {
        db.default_session_id_for_workspace(workspace_id)
            .unwrap()
            .expect("workspace must have a default session for tests")
    }

    #[test]
    fn test_count_messages_empty_session() {
        let db = setup_db_with_workspace();
        let sid = session_id_for(&db, "w1");
        assert_eq!(db.count_chat_messages_for_session(&sid).unwrap(), 0);
    }

    #[test]
    fn test_count_messages_excludes_legacy_empty_assistant() {
        let db = setup_db_with_workspace();
        let sid = session_id_for(&db, "w1");
        // Real user + assistant turn.
        db.insert_chat_message(&make_chat_msg(&db, "m1", "w1", ChatRole::User, "hi"))
            .unwrap();
        db.insert_chat_message(&make_chat_msg(
            &db,
            "m2",
            "w1",
            ChatRole::Assistant,
            "hello",
        ))
        .unwrap();
        // Legacy empty-assistant row (no content, no thinking) — should be filtered.
        db.insert_chat_message(&make_chat_msg(&db, "m3", "w1", ChatRole::Assistant, ""))
            .unwrap();
        // Empty-content assistant *with* thinking is real — should count.
        let mut thinking = make_chat_msg(&db, "m4", "w1", ChatRole::Assistant, "");
        thinking.thinking = Some("internal monologue".into());
        db.insert_chat_message(&thinking).unwrap();

        assert_eq!(db.count_chat_messages_for_session(&sid).unwrap(), 3);
    }

    #[test]
    fn test_list_page_first_returns_newest_in_asc_order() {
        let db = setup_db_with_workspace();
        let sid = session_id_for(&db, "w1");
        for i in 1..=5 {
            db.insert_chat_message(&make_chat_msg(
                &db,
                &format!("m{i}"),
                "w1",
                if i % 2 == 1 {
                    ChatRole::User
                } else {
                    ChatRole::Assistant
                },
                &format!("msg {i}"),
            ))
            .unwrap();
        }

        let page = db.list_chat_messages_page(&sid, 3, None).unwrap();
        assert_eq!(page.len(), 3);
        // Newest 3 messages, returned ASC — m3, m4, m5.
        assert_eq!(page[0].id, "m3");
        assert_eq!(page[1].id, "m4");
        assert_eq!(page[2].id, "m5");
    }

    #[test]
    fn test_list_page_cursor_returns_older_batch() {
        let db = setup_db_with_workspace();
        let sid = session_id_for(&db, "w1");
        for i in 1..=5 {
            db.insert_chat_message(&make_chat_msg(
                &db,
                &format!("m{i}"),
                "w1",
                ChatRole::User,
                &format!("msg {i}"),
            ))
            .unwrap();
        }
        // Use m3 as the cursor — should return m1 and m2 in ASC order.
        let older = db.list_chat_messages_page(&sid, 10, Some("m3")).unwrap();
        assert_eq!(older.len(), 2);
        assert_eq!(older[0].id, "m1");
        assert_eq!(older[1].id, "m2");
    }

    #[test]
    fn test_list_page_excludes_legacy_empty_assistant() {
        let db = setup_db_with_workspace();
        let sid = session_id_for(&db, "w1");
        db.insert_chat_message(&make_chat_msg(&db, "m1", "w1", ChatRole::User, "hi"))
            .unwrap();
        // Legacy empty-assistant row in the middle.
        db.insert_chat_message(&make_chat_msg(&db, "m2", "w1", ChatRole::Assistant, ""))
            .unwrap();
        db.insert_chat_message(&make_chat_msg(&db, "m3", "w1", ChatRole::Assistant, "real"))
            .unwrap();

        let page = db.list_chat_messages_page(&sid, 10, None).unwrap();
        let ids: Vec<&str> = page.iter().map(|m| m.id.as_str()).collect();
        assert_eq!(ids, vec!["m1", "m3"]);
    }

    #[test]
    fn test_list_page_exact_limit_signals_more_via_cursor() {
        let db = setup_db_with_workspace();
        let sid = session_id_for(&db, "w1");
        for i in 1..=4 {
            db.insert_chat_message(&make_chat_msg(
                &db,
                &format!("m{i}"),
                "w1",
                ChatRole::User,
                &format!("msg {i}"),
            ))
            .unwrap();
        }
        // First page returns 2 newest (m3, m4); count is 4 so caller knows more exist.
        let first = db.list_chat_messages_page(&sid, 2, None).unwrap();
        assert_eq!(first.len(), 2);
        assert_eq!(first[0].id, "m3");
        assert_eq!(first[1].id, "m4");
        let total = db.count_chat_messages_for_session(&sid).unwrap();
        assert!(total > first.len() as i64);

        // Second page (cursor = m3) returns m1, m2.
        let second = db.list_chat_messages_page(&sid, 2, Some("m3")).unwrap();
        assert_eq!(second.len(), 2);
        assert_eq!(second[0].id, "m1");
        assert_eq!(second[1].id, "m2");

        // Third page (cursor = m1) returns nothing — exhausted.
        let third = db.list_chat_messages_page(&sid, 2, Some("m1")).unwrap();
        assert!(third.is_empty());
    }

    #[test]
    fn test_list_page_tied_timestamps_disambiguate_via_rowid() {
        // make_chat_msg sets `created_at = ""` so all rows tie on timestamp; the
        // ORDER BY rowid tiebreaker must still produce a stable insertion order.
        let db = setup_db_with_workspace();
        let sid = session_id_for(&db, "w1");
        for i in 1..=4 {
            db.insert_chat_message(&make_chat_msg(
                &db,
                &format!("m{i}"),
                "w1",
                ChatRole::User,
                &format!("msg {i}"),
            ))
            .unwrap();
        }
        let page = db.list_chat_messages_page(&sid, 2, None).unwrap();
        assert_eq!(page[0].id, "m3");
        assert_eq!(page[1].id, "m4");
    }

    #[test]
    fn test_previous_user_message_id_finds_prior_user() {
        let db = setup_db_with_workspace();
        let sid = session_id_for(&db, "w1");
        db.insert_chat_message(&make_chat_msg(&db, "u1", "w1", ChatRole::User, "first"))
            .unwrap();
        db.insert_chat_message(&make_chat_msg(&db, "a1", "w1", ChatRole::Assistant, "ok"))
            .unwrap();
        db.insert_chat_message(&make_chat_msg(&db, "u2", "w1", ChatRole::User, "second"))
            .unwrap();
        db.insert_chat_message(&make_chat_msg(&db, "a2", "w1", ChatRole::Assistant, "done"))
            .unwrap();

        // Prior user before a2 is u2.
        assert_eq!(
            db.previous_user_message_id(&sid, "a2").unwrap(),
            Some("u2".into()),
        );
        // Prior user before u2 is u1 (skips a1).
        assert_eq!(
            db.previous_user_message_id(&sid, "u2").unwrap(),
            Some("u1".into()),
        );
    }

    #[test]
    fn test_previous_user_message_id_none_when_no_prior_user() {
        let db = setup_db_with_workspace();
        let sid = session_id_for(&db, "w1");
        db.insert_chat_message(&make_chat_msg(&db, "u1", "w1", ChatRole::User, "first"))
            .unwrap();
        // No user message exists strictly before u1.
        assert_eq!(db.previous_user_message_id(&sid, "u1").unwrap(), None);
    }

    // --- Attachment tests ---

    #[test]
    fn test_insert_and_list_attachments() {
        let db = setup_db_with_workspace();
        db.insert_chat_message(&make_chat_msg(
            &db,
            "m1",
            "w1",
            ChatRole::User,
            "look at this",
        ))
        .unwrap();
        db.insert_attachment(&make_attachment("a1", "m1", "screenshot.png"))
            .unwrap();

        let atts = db.list_attachments_for_message("m1").unwrap();
        assert_eq!(atts.len(), 1);
        assert_eq!(atts[0].id, "a1");
        assert_eq!(atts[0].filename, "screenshot.png");
        assert_eq!(atts[0].media_type, "image/png");
        assert_eq!(
            atts[0].data,
            vec![0x89, 0x50, 0x4E, 0x47, 0x0D, 0x0A, 0x1A, 0x0A]
        );
        assert_eq!(atts[0].width, Some(100));
        assert_eq!(atts[0].height, Some(200));
        assert_eq!(atts[0].size_bytes, 8);
    }

    #[test]
    fn test_attachment_cascade_on_message_delete() {
        let db = setup_db_with_workspace();
        db.insert_chat_message(&make_chat_msg(&db, "m1", "w1", ChatRole::User, "img"))
            .unwrap();
        db.insert_attachment(&make_attachment("a1", "m1", "pic.png"))
            .unwrap();

        // Deleting all messages for the workspace should cascade to attachments.
        db.delete_chat_messages_for_workspace("w1").unwrap();
        let atts = db.list_attachments_for_message("m1").unwrap();
        assert!(atts.is_empty());
    }

    #[test]
    fn test_delete_chat_message_deletes_exact_row_and_cascades_attachments() {
        let db = setup_db_with_workspace();
        db.insert_chat_message(&make_chat_msg(&db, "m1", "w1", ChatRole::User, "first"))
            .unwrap();
        db.insert_chat_message(&make_chat_msg(&db, "m2", "w1", ChatRole::User, "second"))
            .unwrap();
        db.insert_attachment(&make_attachment("a1", "m1", "first.png"))
            .unwrap();
        db.insert_attachment(&make_attachment("a2", "m2", "second.png"))
            .unwrap();

        db.delete_chat_message("m2").unwrap();

        let msgs = db.list_chat_messages("w1").unwrap();
        assert_eq!(msgs.len(), 1);
        assert_eq!(msgs[0].id, "m1");
        assert_eq!(db.list_attachments_for_message("m1").unwrap().len(), 1);
        assert!(db.list_attachments_for_message("m2").unwrap().is_empty());
    }

    #[test]
    fn test_attachment_cascade_on_delete_messages_after() {
        let db = setup_db_with_workspace();
        db.insert_chat_message(&make_chat_msg(&db, "m1", "w1", ChatRole::User, "first"))
            .unwrap();
        db.insert_attachment(&make_attachment("a1", "m1", "first.png"))
            .unwrap();
        db.insert_chat_message(&make_chat_msg(&db, "m2", "w1", ChatRole::User, "second"))
            .unwrap();
        db.insert_attachment(&make_attachment("a2", "m2", "second.png"))
            .unwrap();

        // Delete messages after m1 — m2 and its attachment should be gone.
        db.delete_messages_after("w1", "m1").unwrap();

        let atts_m1 = db.list_attachments_for_message("m1").unwrap();
        assert_eq!(atts_m1.len(), 1, "first message attachment should survive");
        let atts_m2 = db.list_attachments_for_message("m2").unwrap();
        assert!(
            atts_m2.is_empty(),
            "second message attachment should be deleted"
        );
    }

    #[test]
    fn test_insert_attachments_batch() {
        let db = setup_db_with_workspace();
        db.insert_chat_message(&make_chat_msg(&db, "m1", "w1", ChatRole::User, "images"))
            .unwrap();

        let attachments = vec![
            make_attachment("a1", "m1", "one.png"),
            make_attachment("a2", "m1", "two.jpg"),
            make_attachment("a3", "m1", "three.gif"),
        ];
        db.insert_attachments_batch(&attachments).unwrap();

        let atts = db.list_attachments_for_message("m1").unwrap();
        assert_eq!(atts.len(), 3);
    }

    #[test]
    fn test_insert_attachments_batch_empty() {
        let db = setup_db_with_workspace();
        // Should be a no-op, not an error.
        db.insert_attachments_batch(&[]).unwrap();
    }

    #[test]
    fn test_get_attachment_by_id() {
        let db = setup_db_with_workspace();
        db.insert_chat_message(&make_chat_msg(&db, "m1", "w1", ChatRole::User, "hello"))
            .unwrap();
        db.insert_attachment(&make_attachment("a1", "m1", "doc.pdf"))
            .unwrap();

        let att = db.get_attachment("a1").unwrap().unwrap();
        assert_eq!(att.filename, "doc.pdf");
        assert_eq!(att.data.len(), 8); // PNG header bytes from make_attachment
    }

    #[test]
    fn test_get_attachment_not_found() {
        let db = setup_db_with_workspace();
        let result = db.get_attachment("nonexistent").unwrap();
        assert!(result.is_none());
    }

    #[test]
    fn test_list_attachments_for_messages_batch() {
        let db = setup_db_with_workspace();
        db.insert_chat_message(&make_chat_msg(&db, "m1", "w1", ChatRole::User, "msg1"))
            .unwrap();
        db.insert_chat_message(&make_chat_msg(&db, "m2", "w1", ChatRole::User, "msg2"))
            .unwrap();
        db.insert_chat_message(&make_chat_msg(&db, "m3", "w1", ChatRole::User, "msg3"))
            .unwrap();
        db.insert_attachment(&make_attachment("a1", "m1", "pic1.png"))
            .unwrap();
        db.insert_attachment(&make_attachment("a2", "m1", "pic2.png"))
            .unwrap();
        db.insert_attachment(&make_attachment("a3", "m2", "pic3.jpg"))
            .unwrap();
        // m3 has no attachments.

        let ids = vec!["m1".into(), "m2".into(), "m3".into()];
        let map = db.list_attachments_for_messages(&ids).unwrap();

        assert_eq!(map.get("m1").map(|v| v.len()), Some(2));
        assert_eq!(map.get("m2").map(|v| v.len()), Some(1));
        assert!(!map.contains_key("m3"), "m3 should have no entry");
    }

    #[test]
    fn test_insert_agent_attachment_round_trips_with_tool_use_id() {
        let db = setup_db_with_workspace();
        db.insert_chat_message(&make_chat_msg(&db, "m1", "w1", ChatRole::Assistant, "here"))
            .unwrap();
        let att = Attachment {
            id: "ag1".into(),
            message_id: "m1".into(),
            filename: "shot.png".into(),
            media_type: "image/png".into(),
            data: vec![0x89, 0x50, 0x4E, 0x47],
            width: Some(640),
            height: Some(480),
            size_bytes: 4,
            created_at: String::new(),
            origin: AttachmentOrigin::Agent,
            tool_use_id: Some("toolu_42".into()),
        };
        db.insert_attachment(&att).unwrap();

        let got = db.get_attachment("ag1").unwrap().unwrap();
        assert_eq!(got.origin, AttachmentOrigin::Agent);
        assert_eq!(got.tool_use_id.as_deref(), Some("toolu_42"));
        assert_eq!(got.filename, "shot.png");
        assert_eq!(got.size_bytes, 4);
    }

    #[test]
    fn test_list_attachments_by_tool_use_id_filters_correctly() {
        let db = setup_db_with_workspace();
        db.insert_chat_message(&make_chat_msg(&db, "m1", "w1", ChatRole::Assistant, "x"))
            .unwrap();
        let mk = |id: &str, tuid: Option<&str>| Attachment {
            id: id.into(),
            message_id: "m1".into(),
            filename: format!("{id}.png"),
            media_type: "image/png".into(),
            data: vec![0],
            width: None,
            height: None,
            size_bytes: 1,
            created_at: String::new(),
            origin: AttachmentOrigin::Agent,
            tool_use_id: tuid.map(String::from),
        };
        db.insert_attachment(&mk("a1", Some("toolu_1"))).unwrap();
        db.insert_attachment(&mk("a2", Some("toolu_1"))).unwrap();
        db.insert_attachment(&mk("a3", Some("toolu_2"))).unwrap();
        db.insert_attachment(&mk("a4", None)).unwrap();

        let one = db.list_attachments_by_tool_use("toolu_1").unwrap();
        assert_eq!(one.len(), 2);
        assert!(
            one.iter()
                .all(|a| a.tool_use_id.as_deref() == Some("toolu_1"))
        );

        let two = db.list_attachments_by_tool_use("toolu_2").unwrap();
        assert_eq!(two.len(), 1);

        let none = db.list_attachments_by_tool_use("missing").unwrap();
        assert!(none.is_empty());
    }

    #[test]
    fn test_existing_user_attachment_loads_with_user_origin() {
        // Verifies row_to_attachment correctly populates origin for legacy
        // rows inserted via the User-shaped path.
        let db = setup_db_with_workspace();
        db.insert_chat_message(&make_chat_msg(&db, "m1", "w1", ChatRole::User, "hi"))
            .unwrap();
        db.insert_attachment(&make_attachment("a1", "m1", "u.png"))
            .unwrap();
        let got = db.get_attachment("a1").unwrap().unwrap();
        assert_eq!(got.origin, AttachmentOrigin::User);
        assert!(got.tool_use_id.is_none());
    }

    #[test]
    fn test_attachments_origin_defaults_to_user_for_existing_rows() {
        // Migration adds `origin TEXT NOT NULL DEFAULT 'user'` so any pre-
        // existing row is implicitly user-supplied without a backfill step.
        let db = setup_db_with_workspace();
        db.insert_chat_message(&make_chat_msg(&db, "m1", "w1", ChatRole::User, "img"))
            .unwrap();
        db.insert_attachment(&make_attachment("a1", "m1", "u.png"))
            .unwrap();

        let origin: String = db
            .conn
            .query_row(
                "SELECT origin FROM attachments WHERE id = ?1",
                params!["a1"],
                |r| r.get(0),
            )
            .unwrap();
        assert_eq!(origin, "user");

        let tool_use_id: Option<String> = db
            .conn
            .query_row(
                "SELECT tool_use_id FROM attachments WHERE id = ?1",
                params!["a1"],
                |r| r.get(0),
            )
            .unwrap();
        assert!(tool_use_id.is_none());
    }

    #[test]
    fn test_attachments_origin_check_rejects_invalid_values() {
        // The CHECK constraint enforces origin ∈ {'user','agent'}; arbitrary
        // strings must be rejected at write time so the column can be trusted
        // as an enum from Rust's side.
        let db = setup_db_with_workspace();
        db.insert_chat_message(&make_chat_msg(&db, "m1", "w1", ChatRole::User, "x"))
            .unwrap();
        let res = db.conn.execute(
            "INSERT INTO attachments (id, message_id, filename, media_type, data, size_bytes, origin)
             VALUES ('a1', 'm1', 'x.png', 'image/png', x'00', 1, 'bogus')",
            [],
        );
        assert!(res.is_err(), "CHECK should reject bogus origin");
    }

    #[test]
    fn test_attachments_can_insert_agent_origin_with_tool_use_id() {
        // Direct-SQL canary: confirms an agent-origin row with a tool_use_id
        // can be written. The Rust API for this lands in slice 2.
        let db = setup_db_with_workspace();
        db.insert_chat_message(&make_chat_msg(&db, "m1", "w1", ChatRole::Assistant, "here"))
            .unwrap();
        db.conn.execute(
            "INSERT INTO attachments (id, message_id, filename, media_type, data, size_bytes, origin, tool_use_id)
             VALUES ('a1', 'm1', 'shot.png', 'image/png', x'89504E47', 4, 'agent', 'toolu_123')",
            [],
        ).unwrap();

        let (origin, tool_use_id): (String, Option<String>) = db
            .conn
            .query_row(
                "SELECT origin, tool_use_id FROM attachments WHERE id = 'a1'",
                [],
                |r| Ok((r.get(0)?, r.get(1)?)),
            )
            .unwrap();
        assert_eq!(origin, "agent");
        assert_eq!(tool_use_id.as_deref(), Some("toolu_123"));
    }

    #[test]
    fn test_last_message_per_workspace() {
        let db = setup_db_with_workspace();
        db.insert_workspace(&make_workspace("w2", "r1", "feature"))
            .unwrap();
        db.insert_chat_message(&make_chat_msg(&db, "m1", "w1", ChatRole::User, "first"))
            .unwrap();
        db.insert_chat_message(&make_chat_msg(
            &db,
            "m2",
            "w1",
            ChatRole::Assistant,
            "second",
        ))
        .unwrap();
        db.insert_chat_message(&make_chat_msg(
            &db,
            "m3",
            "w2",
            ChatRole::User,
            "other workspace",
        ))
        .unwrap();

        let last = db.last_message_per_workspace().unwrap();
        assert_eq!(last.len(), 2);

        let w1_msg = last.iter().find(|m| m.workspace_id == "w1").unwrap();
        assert_eq!(w1_msg.content, "second");

        let w2_msg = last.iter().find(|m| m.workspace_id == "w2").unwrap();
        assert_eq!(w2_msg.content, "other workspace");
    }

    #[test]
    fn test_last_message_per_workspace_same_timestamp() {
        let db = setup_db_with_workspace();
        // Insert two messages with identical timestamps — the later rowid should win.
        let mut m1 = make_chat_msg(&db, "m1", "w1", ChatRole::User, "first");
        m1.created_at = "2026-01-01 00:00:00".into();
        let mut m2 = make_chat_msg(&db, "m2", "w1", ChatRole::Assistant, "second");
        m2.created_at = "2026-01-01 00:00:00".into();
        db.insert_chat_message(&m1).unwrap();
        db.insert_chat_message(&m2).unwrap();

        let last = db.last_message_per_workspace().unwrap();
        assert_eq!(last.len(), 1);
        assert_eq!(last[0].content, "second");
    }

    #[test]
    fn test_last_message_empty_when_no_messages() {
        let db = setup_db_with_workspace();
        let last = db.last_message_per_workspace().unwrap();
        assert!(last.is_empty());
    }

    /// Regression: an unknown `role` string in the `chat_messages` table must
    /// surface as a `FromSqlConversionFailure`, not silently become
    /// `ChatRole::User`. See issue #485.
    ///
    /// We bypass the table's `CHECK(role IN (...))` constraint via
    /// `PRAGMA ignore_check_constraints` so this test can simulate corrupted
    /// data or a future-version row whose role string post-dates the CHECK.
    #[test]
    fn test_list_chat_messages_unknown_role_returns_error() {
        let db = setup_db_with_workspace();
        db.insert_chat_message(&make_chat_msg(&db, "m1", "w1", ChatRole::User, "hi"))
            .unwrap();
        db.conn
            .execute_batch("PRAGMA ignore_check_constraints = ON;")
            .unwrap();
        db.conn
            .execute(
                "UPDATE chat_messages SET role = 'unknown_role' WHERE id = 'm1'",
                [],
            )
            .unwrap();
        let result = db.list_chat_messages("w1");
        match result {
            Err(rusqlite::Error::FromSqlConversionFailure(idx, ty, _)) => {
                assert_eq!(idx, 3, "expected chat_messages.role column index 3");
                assert_eq!(
                    ty,
                    rusqlite::types::Type::Text,
                    "expected chat_messages.role to be reported as TEXT"
                );
            }
            other => panic!("expected FromSqlConversionFailure for unknown role, got: {other:?}",),
        }
    }

    #[test]
    fn cli_invocation_round_trip() {
        let db = setup_db_with_workspace();
        let session = db.create_chat_session("w1").unwrap();

        // Initial value is None.
        let loaded = db.get_chat_session(&session.id).unwrap().unwrap();
        assert!(loaded.cli_invocation.is_none());

        // First write succeeds.
        db.set_chat_session_cli_invocation(&session.id, "claude --foo")
            .unwrap();
        let after_first = db.get_chat_session(&session.id).unwrap().unwrap();
        assert_eq!(after_first.cli_invocation.as_deref(), Some("claude --foo"));

        // Second write does NOT overwrite (first-emit-wins).
        db.set_chat_session_cli_invocation(&session.id, "claude --bar")
            .unwrap();
        let after_second = db.get_chat_session(&session.id).unwrap().unwrap();
        assert_eq!(
            after_second.cli_invocation.as_deref(),
            Some("claude --foo"),
            "second write must not clobber first"
        );
    }

    /// Regression: an unknown `status` string in the `chat_sessions` table
    /// must surface as a `FromSqlConversionFailure`, not silently coerce to
    /// `SessionStatus::Active`. See issue #485.
    #[test]
    fn test_list_chat_sessions_unknown_status_returns_error() {
        let db = setup_db_with_workspace();
        // The workspace was seeded with a default chat session — corrupt its status.
        db.conn
            .execute(
                "UPDATE chat_sessions SET status = 'pending' WHERE workspace_id = 'w1'",
                [],
            )
            .unwrap();
        // include_archived = true so the WHERE filter doesn't pre-exclude the
        // corrupt row before parse_chat_session_row gets a chance to see it.
        let result = db.list_chat_sessions_for_workspace("w1", true);
        match result {
            Err(rusqlite::Error::FromSqlConversionFailure(idx, ty, _)) => {
                assert_eq!(idx, 7, "expected chat_sessions.status column index 7");
                assert_eq!(
                    ty,
                    rusqlite::types::Type::Text,
                    "expected chat_sessions.status to be reported as TEXT"
                );
            }
            other => panic!(
                "expected FromSqlConversionFailure for unknown session status, got: {other:?}",
            ),
        }
    }
}
