paddington 0.6.0

use chrono::Utc;
use rusqlite::{params, Connection};
use std::path::PathBuf;

#[derive(Clone)]
pub struct SessionRecord {
    pub session_id: String,
    pub project_dir: Option<String>,
    pub model_id: Option<String>,
    pub model_name: Option<String>,
    pub cost_usd: f64,
    pub duration_ms: u64,
    pub lines_added: u64,
    pub lines_removed: u64,
    pub subagent_cost_usd: f64,
}

pub struct SubagentRunRecord {
    pub session_id: String,
    pub run_id: String,
    pub agent_name: Option<String>,
    pub model_name: Option<String>,
    pub cost_usd: f64,
    pub input_tokens: u64,
    pub output_tokens: u64,
    pub duration_ms: u64,
}

fn db_path() -> PathBuf {
    let data_dir = std::env::var("XDG_DATA_HOME")
        .map(PathBuf::from)
        .unwrap_or_else(|_| {
            let home = std::env::var("HOME").unwrap_or_else(|_| "/tmp".into());
            PathBuf::from(home).join(".local/share")
        });
    data_dir.join("paddington/sessions.db")
}

fn init_db(conn: &Connection) -> rusqlite::Result<()> {
    conn.execute_batch("PRAGMA journal_mode=WAL;")?;
    conn.execute_batch(
        "CREATE TABLE IF NOT EXISTS sessions (
            session_id    TEXT PRIMARY KEY,
            project_dir   TEXT,
            model_id      TEXT,
            model_name    TEXT,
            cost_usd      REAL NOT NULL DEFAULT 0,
            duration_ms   INTEGER NOT NULL DEFAULT 0,
            lines_added   INTEGER NOT NULL DEFAULT 0,
            lines_removed INTEGER NOT NULL DEFAULT 0,
            started_at    TEXT NOT NULL,
            updated_at    TEXT NOT NULL
        );",
    )?;

    // Migrate: add subagent_cost_usd column if missing
    let has_subagent_col: bool = conn
        .prepare("SELECT subagent_cost_usd FROM sessions LIMIT 0")
        .is_ok();
    if !has_subagent_col {
        conn.execute_batch(
            "ALTER TABLE sessions ADD COLUMN subagent_cost_usd REAL NOT NULL DEFAULT 0;",
        )?;
    }

    conn.execute_batch(
        "CREATE TABLE IF NOT EXISTS subagent_runs (
            id            INTEGER PRIMARY KEY AUTOINCREMENT,
            session_id    TEXT NOT NULL REFERENCES sessions(session_id),
            run_id        TEXT NOT NULL,
            agent_name    TEXT,
            model_name    TEXT,
            cost_usd      REAL NOT NULL DEFAULT 0,
            input_tokens  INTEGER NOT NULL DEFAULT 0,
            output_tokens INTEGER NOT NULL DEFAULT 0,
            duration_ms   INTEGER NOT NULL DEFAULT 0,
            UNIQUE(session_id, run_id)
        );",
    )?;

    Ok(())
}

pub fn open_db() -> rusqlite::Result<Connection> {
    let path = db_path();
    if let Some(parent) = path.parent() {
        let _ = std::fs::create_dir_all(parent);
    }
    let conn = Connection::open(&path)?;
    init_db(&conn)?;
    Ok(conn)
}

pub fn open_db_readonly() -> rusqlite::Result<Connection> {
    let path = db_path();
    // Try read-write first to run migrations, fall back to read-only
    if let Ok(conn) = open_db() {
        return Ok(conn);
    }
    Connection::open_with_flags(&path, rusqlite::OpenFlags::SQLITE_OPEN_READ_ONLY)
}

#[cfg(test)]
pub fn open_db_in_memory() -> rusqlite::Result<Connection> {
    let conn = Connection::open_in_memory()?;
    init_db(&conn)?;
    Ok(conn)
}

pub fn upsert_session(conn: &Connection, record: &SessionRecord) -> rusqlite::Result<()> {
    let now = Utc::now().to_rfc3339();
    conn.execute(
        "INSERT INTO sessions (session_id, project_dir, model_id, model_name, cost_usd, duration_ms, lines_added, lines_removed, subagent_cost_usd, started_at, updated_at)
         VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?10)
         ON CONFLICT(session_id) DO UPDATE SET
             project_dir = excluded.project_dir,
             model_id = excluded.model_id,
             model_name = excluded.model_name,
             cost_usd = excluded.cost_usd,
             duration_ms = excluded.duration_ms,
             lines_added = excluded.lines_added,
             lines_removed = excluded.lines_removed,
             subagent_cost_usd = excluded.subagent_cost_usd,
             updated_at = excluded.updated_at",
        params![
            record.session_id,
            record.project_dir,
            record.model_id,
            record.model_name,
            record.cost_usd,
            record.duration_ms as i64,
            record.lines_added as i64,
            record.lines_removed as i64,
            record.subagent_cost_usd,
            now,
        ],
    )?;
    Ok(())
}

pub fn upsert_subagent_runs(conn: &Connection, runs: &[SubagentRunRecord]) -> rusqlite::Result<()> {
    for run in runs {
        conn.execute(
            "INSERT INTO subagent_runs (session_id, run_id, agent_name, model_name, cost_usd, input_tokens, output_tokens, duration_ms)
             VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8)
             ON CONFLICT(session_id, run_id) DO UPDATE SET
                 agent_name = excluded.agent_name,
                 model_name = excluded.model_name,
                 cost_usd = excluded.cost_usd,
                 input_tokens = excluded.input_tokens,
                 output_tokens = excluded.output_tokens,
                 duration_ms = excluded.duration_ms",
            params![
                run.session_id,
                run.run_id,
                run.agent_name,
                run.model_name,
                run.cost_usd,
                run.input_tokens as i64,
                run.output_tokens as i64,
                run.duration_ms as i64,
            ],
        )?;
    }
    Ok(())
}

pub fn get_monthly_total(conn: &Connection, year: i32, month: u32) -> rusqlite::Result<f64> {
    let start = format!("{year:04}-{month:02}-01");
    let (end_year, end_month) = if month == 12 {
        (year + 1, 1)
    } else {
        (year, month + 1)
    };
    let end = format!("{end_year:04}-{end_month:02}-01");
    conn.query_row(
        "SELECT COALESCE(SUM(cost_usd + subagent_cost_usd), 0.0) FROM sessions WHERE datetime(started_at, 'localtime') >= ?1 AND datetime(started_at, 'localtime') < ?2",
        params![start, end],
        |row| row.get(0),
    )
}

pub fn monthly_totals(
    conn: &Connection,
    year: i32,
    month: u32,
) -> rusqlite::Result<(f64, f64, u64, u64, u64)> {
    let start = format!("{year:04}-{month:02}-01");
    let (ey, em) = if month == 12 {
        (year + 1, 1)
    } else {
        (year, month + 1)
    };
    let end = format!("{ey:04}-{em:02}-01");
    conn.query_row(
        "SELECT COALESCE(SUM(cost_usd + subagent_cost_usd), 0.0), COALESCE(SUM(subagent_cost_usd), 0.0), COALESCE(SUM(duration_ms), 0), COALESCE(SUM(lines_added), 0), COALESCE(SUM(lines_removed), 0)
         FROM sessions WHERE datetime(started_at, 'localtime') >= ?1 AND datetime(started_at, 'localtime') < ?2",
        params![start, end],
        |row| Ok((row.get(0)?, row.get(1)?, row.get::<_, i64>(2)? as u64, row.get::<_, i64>(3)? as u64, row.get::<_, i64>(4)? as u64)),
    )
}

pub fn monthly_session_count(conn: &Connection, year: i32, month: u32) -> rusqlite::Result<u64> {
    let start = format!("{year:04}-{month:02}-01");
    let (ey, em) = if month == 12 {
        (year + 1, 1)
    } else {
        (year, month + 1)
    };
    let end = format!("{ey:04}-{em:02}-01");
    conn.query_row(
        "SELECT COUNT(*) FROM sessions WHERE datetime(started_at, 'localtime') >= ?1 AND datetime(started_at, 'localtime') < ?2",
        params![start, end],
        |row| row.get::<_, i64>(0).map(|v| v as u64),
    )
}

/// Returns (day_of_month, total_cost, session_count) for each day that has sessions.
pub fn daily_breakdown(
    conn: &Connection,
    year: i32,
    month: u32,
) -> rusqlite::Result<Vec<(u32, f64, u64)>> {
    let start = format!("{year:04}-{month:02}-01");
    let (ey, em) = if month == 12 {
        (year + 1, 1)
    } else {
        (year, month + 1)
    };
    let end = format!("{ey:04}-{em:02}-01");
    let mut stmt = conn.prepare(
        "SELECT CAST(SUBSTR(datetime(started_at, 'localtime'), 9, 2) AS INTEGER) as day,
                SUM(cost_usd + subagent_cost_usd), COUNT(*)
         FROM sessions
         WHERE datetime(started_at, 'localtime') >= ?1 AND datetime(started_at, 'localtime') < ?2
         GROUP BY day ORDER BY day",
    )?;
    let rows = stmt.query_map(params![start, end], |row| {
        Ok((
            row.get::<_, u32>(0)?,
            row.get::<_, f64>(1)?,
            row.get::<_, i64>(2)? as u64,
        ))
    })?;
    rows.collect()
}

/// Returns (model_name, total_cost, duration_ms) sorted by cost descending.
pub fn model_breakdown(
    conn: &Connection,
    year: i32,
    month: u32,
) -> rusqlite::Result<Vec<(String, f64, u64)>> {
    let start = format!("{year:04}-{month:02}-01");
    let (ey, em) = if month == 12 {
        (year + 1, 1)
    } else {
        (year, month + 1)
    };
    let end = format!("{ey:04}-{em:02}-01");
    let mut stmt = conn.prepare(
        "SELECT COALESCE(model_name, 'Unknown'), SUM(cost_usd + subagent_cost_usd), SUM(duration_ms)
         FROM sessions
         WHERE datetime(started_at, 'localtime') >= ?1 AND datetime(started_at, 'localtime') < ?2
         GROUP BY model_name ORDER BY SUM(cost_usd + subagent_cost_usd) DESC",
    )?;
    let rows = stmt.query_map(params![start, end], |row| {
        Ok((
            row.get::<_, String>(0)?,
            row.get::<_, f64>(1)?,
            row.get::<_, i64>(2)? as u64,
        ))
    })?;
    rows.collect()
}

pub struct ActiveSession {
    pub project: String,
    pub model: String,
    pub cost_usd: f64,
    pub duration_ms: u64,
    pub updated_at: String,
}

/// Returns sessions updated within the last `minutes` minutes.
pub fn active_sessions(conn: &Connection, minutes: i64) -> rusqlite::Result<Vec<ActiveSession>> {
    let cutoff = (Utc::now() - chrono::Duration::minutes(minutes)).to_rfc3339();
    let mut stmt = conn.prepare(
        "SELECT project_dir, model_name, cost_usd + subagent_cost_usd, duration_ms, updated_at
         FROM sessions WHERE updated_at >= ?1 ORDER BY updated_at DESC",
    )?;
    let rows = stmt.query_map(params![cutoff], |row| {
        let dir: String = row.get::<_, Option<String>>(0)?.unwrap_or_default();
        let resolved = resolve_project_dir(&dir);
        let project = std::path::Path::new(resolved)
            .file_name()
            .and_then(|n| n.to_str())
            .unwrap_or("(unknown)")
            .to_string();
        Ok(ActiveSession {
            project,
            model: row.get::<_, Option<String>>(1)?.unwrap_or_default(),
            cost_usd: row.get(2)?,
            duration_ms: row.get::<_, i64>(3)? as u64,
            updated_at: row.get(4)?,
        })
    })?;
    rows.collect()
}

pub fn all_time_totals(conn: &Connection) -> rusqlite::Result<(f64, u64, u64)> {
    conn.query_row(
        "SELECT COALESCE(SUM(cost_usd + subagent_cost_usd), 0.0), COUNT(*), COALESCE(SUM(duration_ms), 0) FROM sessions",
        [],
        |row| Ok((row.get(0)?, row.get::<_, i64>(1)? as u64, row.get::<_, i64>(2)? as u64)),
    )
}

pub fn all_time_totals_full(conn: &Connection) -> rusqlite::Result<(f64, f64, u64, u64, u64, u64)> {
    conn.query_row(
        "SELECT COALESCE(SUM(cost_usd + subagent_cost_usd), 0.0), COALESCE(SUM(subagent_cost_usd), 0.0), COUNT(*), COALESCE(SUM(duration_ms), 0), COALESCE(SUM(lines_added), 0), COALESCE(SUM(lines_removed), 0) FROM sessions",
        [],
        |row| Ok((
            row.get(0)?,
            row.get(1)?,
            row.get::<_, i64>(2)? as u64,
            row.get::<_, i64>(3)? as u64,
            row.get::<_, i64>(4)? as u64,
            row.get::<_, i64>(5)? as u64,
        )),
    )
}

type MonthlyStats = (i32, u32, f64, u64, u64);

/// Returns (year, month, total_cost, session_count, duration_ms) for each month with sessions.
pub fn monthly_breakdown(conn: &Connection) -> rusqlite::Result<Vec<MonthlyStats>> {
    let mut stmt = conn.prepare(
        "SELECT CAST(SUBSTR(datetime(started_at, 'localtime'), 1, 4) AS INTEGER) as year,
                CAST(SUBSTR(datetime(started_at, 'localtime'), 6, 2) AS INTEGER) as month,
                SUM(cost_usd + subagent_cost_usd), COUNT(*), SUM(duration_ms)
         FROM sessions
         GROUP BY year, month ORDER BY year, month",
    )?;
    let rows = stmt.query_map([], |row| {
        Ok((
            row.get::<_, i32>(0)?,
            row.get::<_, u32>(1)?,
            row.get::<_, f64>(2)?,
            row.get::<_, i64>(3)? as u64,
            row.get::<_, i64>(4)? as u64,
        ))
    })?;
    rows.collect()
}

/// Returns (model_name, total_cost, duration_ms) across all time, sorted by cost descending.
pub fn all_time_model_breakdown(conn: &Connection) -> rusqlite::Result<Vec<(String, f64, u64)>> {
    let mut stmt = conn.prepare(
        "SELECT COALESCE(model_name, 'Unknown'), SUM(cost_usd + subagent_cost_usd), SUM(duration_ms)
         FROM sessions
         GROUP BY model_name ORDER BY SUM(cost_usd + subagent_cost_usd) DESC",
    )?;
    let rows = stmt.query_map([], |row| {
        Ok((
            row.get::<_, String>(0)?,
            row.get::<_, f64>(1)?,
            row.get::<_, i64>(2)? as u64,
        ))
    })?;
    rows.collect()
}

/// Returns (project_basename, total_cost, duration_ms, lines_added, lines_removed) across all time.
pub fn all_time_project_breakdown(conn: &Connection) -> rusqlite::Result<Vec<ProjectStats>> {
    let mut stmt = conn.prepare(
        "SELECT project_dir, SUM(cost_usd + subagent_cost_usd), SUM(duration_ms), SUM(lines_added), SUM(lines_removed)
         FROM sessions
         GROUP BY project_dir ORDER BY SUM(cost_usd + subagent_cost_usd) DESC",
    )?;
    let rows = stmt.query_map([], |row| {
        let dir: String = row.get::<_, Option<String>>(0)?.unwrap_or_default();
        Ok((
            dir,
            row.get::<_, f64>(1)?,
            row.get::<_, i64>(2)? as u64,
            row.get::<_, i64>(3)? as u64,
            row.get::<_, i64>(4)? as u64,
        ))
    })?;

    let mut merged: std::collections::HashMap<String, (f64, u64, u64, u64)> =
        std::collections::HashMap::new();
    for row in rows {
        let (dir, cost, dur, added, removed) = row?;
        let resolved = resolve_project_dir(&dir);
        let name = std::path::Path::new(resolved)
            .file_name()
            .and_then(|n| n.to_str())
            .unwrap_or("(unknown)")
            .to_string();
        let entry = merged.entry(name).or_insert((0.0, 0, 0, 0));
        entry.0 += cost;
        entry.1 += dur;
        entry.2 += added;
        entry.3 += removed;
    }

    let mut result: Vec<_> = merged
        .into_iter()
        .map(|(name, (cost, dur, added, removed))| (name, cost, dur, added, removed))
        .collect();
    result.sort_by(|a, b| b.1.partial_cmp(&a.1).unwrap_or(std::cmp::Ordering::Equal));
    Ok(result)
}

pub(crate) fn resolve_project_dir(dir: &str) -> &str {
    match dir.find("/.claude/worktrees/") {
        Some(idx) => &dir[..idx],
        None => dir,
    }
}

pub struct SessionSummary {
    pub project: String,
    pub model: String,
    pub cost_usd: f64,
    pub duration_ms: u64,
    pub lines_added: u64,
    pub lines_removed: u64,
    pub started_at: String,
}

pub fn list_sessions(
    conn: &Connection,
    limit: u32,
    project_filter: Option<&str>,
) -> rusqlite::Result<Vec<SessionSummary>> {
    let (sql, params): (&str, Vec<Box<dyn rusqlite::types::ToSql>>) = match project_filter {
        Some(filter) => (
            "SELECT project_dir, model_name, cost_usd + subagent_cost_usd, duration_ms, lines_added, lines_removed, started_at
             FROM sessions
             WHERE project_dir LIKE ?1
             ORDER BY started_at DESC LIMIT ?2",
            vec![Box::new(format!("%{filter}%")), Box::new(limit)],
        ),
        None => (
            "SELECT project_dir, model_name, cost_usd + subagent_cost_usd, duration_ms, lines_added, lines_removed, started_at
             FROM sessions
             ORDER BY started_at DESC LIMIT ?1",
            vec![Box::new(limit)],
        ),
    };

    let mut stmt = conn.prepare(sql)?;
    let params_refs: Vec<&dyn rusqlite::types::ToSql> = params.iter().map(|p| p.as_ref()).collect();

    let rows = stmt.query_map(params_refs.as_slice(), |row| {
        let dir: String = row.get::<_, Option<String>>(0)?.unwrap_or_default();
        let resolved = resolve_project_dir(&dir);
        let project = std::path::Path::new(resolved)
            .file_name()
            .and_then(|n| n.to_str())
            .unwrap_or("(unknown)")
            .to_string();
        Ok(SessionSummary {
            project,
            model: row.get::<_, Option<String>>(1)?.unwrap_or_default(),
            cost_usd: row.get(2)?,
            duration_ms: row.get::<_, i64>(3)? as u64,
            lines_added: row.get::<_, i64>(4)? as u64,
            lines_removed: row.get::<_, i64>(5)? as u64,
            started_at: row.get(6)?,
        })
    })?;
    rows.collect()
}

type ProjectStats = (String, f64, u64, u64, u64);

/// Returns (project_basename, total_cost, duration_ms, lines_added, lines_removed) sorted by cost descending.
/// project_basename is the last component of project_dir after worktree resolution.
pub fn project_breakdown(
    conn: &Connection,
    year: i32,
    month: u32,
) -> rusqlite::Result<Vec<ProjectStats>> {
    let start = format!("{year:04}-{month:02}-01");
    let (ey, em) = if month == 12 {
        (year + 1, 1)
    } else {
        (year, month + 1)
    };
    let end = format!("{ey:04}-{em:02}-01");
    let mut stmt = conn.prepare(
        "SELECT project_dir, SUM(cost_usd + subagent_cost_usd), SUM(duration_ms), SUM(lines_added), SUM(lines_removed)
         FROM sessions
         WHERE datetime(started_at, 'localtime') >= ?1 AND datetime(started_at, 'localtime') < ?2
         GROUP BY project_dir ORDER BY SUM(cost_usd + subagent_cost_usd) DESC",
    )?;
    let rows = stmt.query_map(params![start, end], |row| {
        let dir: String = row.get::<_, Option<String>>(0)?.unwrap_or_default();
        Ok((
            dir,
            row.get::<_, f64>(1)?,
            row.get::<_, i64>(2)? as u64,
            row.get::<_, i64>(3)? as u64,
            row.get::<_, i64>(4)? as u64,
        ))
    })?;

    // Merge worktree entries into parent projects
    let mut merged: std::collections::HashMap<String, (f64, u64, u64, u64)> =
        std::collections::HashMap::new();
    for row in rows {
        let (dir, cost, dur, added, removed) = row?;
        let resolved = resolve_project_dir(&dir);
        let name = std::path::Path::new(resolved)
            .file_name()
            .and_then(|n| n.to_str())
            .unwrap_or("(unknown)")
            .to_string();
        let entry = merged.entry(name).or_insert((0.0, 0, 0, 0));
        entry.0 += cost;
        entry.1 += dur;
        entry.2 += added;
        entry.3 += removed;
    }

    let mut result: Vec<_> = merged
        .into_iter()
        .map(|(name, (cost, dur, added, removed))| (name, cost, dur, added, removed))
        .collect();
    result.sort_by(|a, b| b.1.partial_cmp(&a.1).unwrap_or(std::cmp::Ordering::Equal));
    Ok(result)
}

/// (agent_name, total_cost, run_count, primary_model, total_tokens)
type SubagentStats = (String, f64, u64, String, u64);

/// Returns (agent_name, total_cost, run_count, primary_model, total_tokens) for the month.
pub fn subagent_breakdown(
    conn: &Connection,
    year: i32,
    month: u32,
) -> rusqlite::Result<Vec<SubagentStats>> {
    let start = format!("{year:04}-{month:02}-01");
    let (ey, em) = if month == 12 {
        (year + 1, 1)
    } else {
        (year, month + 1)
    };
    let end = format!("{ey:04}-{em:02}-01");
    let mut stmt = conn.prepare(
        "SELECT COALESCE(sr.agent_name, 'unknown'), SUM(sr.cost_usd), COUNT(*),
                COALESCE(sr.model_name, 'unknown'), SUM(sr.input_tokens + sr.output_tokens)
         FROM subagent_runs sr
         JOIN sessions s ON sr.session_id = s.session_id
         WHERE datetime(s.started_at, 'localtime') >= ?1 AND datetime(s.started_at, 'localtime') < ?2
         GROUP BY sr.agent_name ORDER BY SUM(sr.cost_usd) DESC",
    )?;
    let rows = stmt.query_map(params![start, end], |row| {
        Ok((
            row.get::<_, String>(0)?,
            row.get::<_, f64>(1)?,
            row.get::<_, i64>(2)? as u64,
            row.get::<_, String>(3)?,
            row.get::<_, i64>(4)? as u64,
        ))
    })?;
    rows.collect()
}

pub fn all_time_subagent_breakdown(conn: &Connection) -> rusqlite::Result<Vec<SubagentStats>> {
    let mut stmt = conn.prepare(
        "SELECT COALESCE(agent_name, 'unknown'), SUM(cost_usd), COUNT(*),
                COALESCE(model_name, 'unknown'), SUM(input_tokens + output_tokens)
         FROM subagent_runs
         GROUP BY agent_name ORDER BY SUM(cost_usd) DESC",
    )?;
    let rows = stmt.query_map([], |row| {
        Ok((
            row.get::<_, String>(0)?,
            row.get::<_, f64>(1)?,
            row.get::<_, i64>(2)? as u64,
            row.get::<_, String>(3)?,
            row.get::<_, i64>(4)? as u64,
        ))
    })?;
    rows.collect()
}

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

    fn seed_test_data(conn: &Connection) {
        let rows = [
            (
                "s1",
                "/home/u/proj-a",
                "opus-4-6",
                "Opus 4.6",
                1.50,
                "2026-07-10T10:00:00+00:00",
            ),
            (
                "s2",
                "/home/u/proj-a",
                "opus-4-6",
                "Opus 4.6",
                2.00,
                "2026-07-10T14:00:00+00:00",
            ),
            (
                "s3",
                "/home/u/proj-b",
                "sonnet-5",
                "Sonnet 5",
                0.75,
                "2026-07-11T09:00:00+00:00",
            ),
            (
                "s4",
                "/home/u/proj-a",
                "sonnet-5",
                "Sonnet 5",
                1.25,
                "2026-07-12T09:00:00+00:00",
            ),
            (
                "s5",
                "/home/u/proj-b",
                "opus-4-6",
                "Opus 4.6",
                3.00,
                "2026-06-15T09:00:00+00:00",
            ), // June — excluded
        ];
        for (sid, proj, mid, mname, cost, started) in rows {
            conn.execute(
                "INSERT INTO sessions (session_id, project_dir, model_id, model_name, cost_usd, duration_ms, lines_added, lines_removed, started_at, updated_at)
                 VALUES (?1, ?2, ?3, ?4, ?5, 0, 0, 0, ?6, ?6)",
                params![sid, proj, mid, mname, cost, started],
            ).unwrap();
        }
    }

    #[test]
    fn upsert_creates_row() {
        let conn = open_db_in_memory().unwrap();
        let record = SessionRecord {
            session_id: "s1".into(),
            project_dir: Some("/home/user/project".into()),
            model_id: Some("claude-opus-4-6".into()),
            model_name: Some("Opus 4.6".into()),
            cost_usd: 0.50,
            duration_ms: 30000,
            lines_added: 10,
            lines_removed: 5,
            subagent_cost_usd: 0.0,
        };
        upsert_session(&conn, &record).unwrap();

        let cost: f64 = conn
            .query_row(
                "SELECT cost_usd FROM sessions WHERE session_id = 's1'",
                [],
                |r| r.get(0),
            )
            .unwrap();
        assert!((cost - 0.50).abs() < f64::EPSILON);
    }

    #[test]
    fn upsert_updates_cost_preserves_started_at() {
        let conn = open_db_in_memory().unwrap();
        let record = SessionRecord {
            session_id: "s1".into(),
            project_dir: None,
            model_id: None,
            model_name: None,
            cost_usd: 0.10,
            duration_ms: 1000,
            lines_added: 0,
            lines_removed: 0,
            subagent_cost_usd: 0.0,
        };
        upsert_session(&conn, &record).unwrap();

        let started: String = conn
            .query_row(
                "SELECT started_at FROM sessions WHERE session_id = 's1'",
                [],
                |r| r.get(0),
            )
            .unwrap();

        // upsert again with higher cost
        let record2 = SessionRecord {
            cost_usd: 0.75,
            ..record.clone()
        };
        upsert_session(&conn, &record2).unwrap();

        let new_cost: f64 = conn
            .query_row(
                "SELECT cost_usd FROM sessions WHERE session_id = 's1'",
                [],
                |r| r.get(0),
            )
            .unwrap();
        let new_started: String = conn
            .query_row(
                "SELECT started_at FROM sessions WHERE session_id = 's1'",
                [],
                |r| r.get(0),
            )
            .unwrap();

        assert!((new_cost - 0.75).abs() < f64::EPSILON);
        assert_eq!(started, new_started, "started_at must not change on update");
    }

    #[test]
    fn monthly_total_sums_current_month() {
        let conn = open_db_in_memory().unwrap();
        // Insert two sessions in July 2026
        conn.execute(
            "INSERT INTO sessions (session_id, cost_usd, duration_ms, lines_added, lines_removed, started_at, updated_at)
             VALUES ('a', 1.50, 0, 0, 0, '2026-07-10T10:00:00+00:00', '2026-07-10T10:00:00+00:00')",
            [],
        ).unwrap();
        conn.execute(
            "INSERT INTO sessions (session_id, cost_usd, duration_ms, lines_added, lines_removed, started_at, updated_at)
             VALUES ('b', 2.25, 0, 0, 0, '2026-07-15T10:00:00+00:00', '2026-07-15T10:00:00+00:00')",
            [],
        ).unwrap();
        // Insert one session in June 2026 (should be excluded)
        conn.execute(
            "INSERT INTO sessions (session_id, cost_usd, duration_ms, lines_added, lines_removed, started_at, updated_at)
             VALUES ('c', 5.00, 0, 0, 0, '2026-06-20T10:00:00+00:00', '2026-06-20T10:00:00+00:00')",
            [],
        ).unwrap();

        let total = get_monthly_total(&conn, 2026, 7).unwrap();
        assert!((total - 3.75).abs() < f64::EPSILON);
    }

    #[test]
    fn monthly_session_count_correct() {
        let conn = open_db_in_memory().unwrap();
        seed_test_data(&conn);
        assert_eq!(monthly_session_count(&conn, 2026, 7).unwrap(), 4);
    }

    #[test]
    fn daily_breakdown_returns_days_with_sessions() {
        let conn = open_db_in_memory().unwrap();
        seed_test_data(&conn);
        let days = daily_breakdown(&conn, 2026, 7).unwrap();
        // Should have entries for days 10, 11, 12 (days with sessions)
        assert_eq!(days.len(), 3);
        // Day 10: two sessions totaling $3.50
        assert_eq!(days[0].0, 10);
        assert!((days[0].1 - 3.50).abs() < f64::EPSILON);
        assert_eq!(days[0].2, 2);
    }

    #[test]
    fn model_breakdown_groups_correctly() {
        let conn = open_db_in_memory().unwrap();
        seed_test_data(&conn);
        let models = model_breakdown(&conn, 2026, 7).unwrap();
        assert_eq!(models.len(), 2);
        assert_eq!(models[0].0, "Opus 4.6");
        assert!((models[0].1 - 3.50).abs() < f64::EPSILON);
        assert_eq!(models[0].2, 0);
        assert_eq!(models[1].0, "Sonnet 5");
        assert!((models[1].1 - 2.00).abs() < f64::EPSILON);
        assert_eq!(models[1].2, 0);
    }

    #[test]
    fn project_breakdown_uses_dir_basename() {
        let conn = open_db_in_memory().unwrap();
        seed_test_data(&conn);
        let projects = project_breakdown(&conn, 2026, 7).unwrap();
        assert_eq!(projects.len(), 2);
        assert_eq!(projects[0].0, "proj-a");
        assert!((projects[0].1 - 4.75).abs() < f64::EPSILON);
    }

    #[test]
    fn resolve_project_dir_strips_worktree() {
        assert_eq!(
            resolve_project_dir("/home/u/code/quartermaster/.claude/worktrees/convoy-guid"),
            "/home/u/code/quartermaster"
        );
        assert_eq!(
            resolve_project_dir("/home/u/code/paddington"),
            "/home/u/code/paddington"
        );
        assert_eq!(resolve_project_dir(""), "");
    }

    #[test]
    fn project_breakdown_merges_worktrees() {
        let conn = open_db_in_memory().unwrap();
        // s1: main project dir
        conn.execute(
            "INSERT INTO sessions (session_id, project_dir, model_id, model_name, cost_usd, duration_ms, lines_added, lines_removed, started_at, updated_at)
             VALUES ('w1', '/home/u/proj-a', 'opus', 'Opus', 1.00, 60000, 10, 5, '2026-07-10T10:00:00+00:00', '2026-07-10T10:00:00+00:00')",
            [],
        ).unwrap();
        // s2: worktree under same project
        conn.execute(
            "INSERT INTO sessions (session_id, project_dir, model_id, model_name, cost_usd, duration_ms, lines_added, lines_removed, started_at, updated_at)
             VALUES ('w2', '/home/u/proj-a/.claude/worktrees/feature-x', 'opus', 'Opus', 2.00, 30000, 20, 3, '2026-07-11T10:00:00+00:00', '2026-07-11T10:00:00+00:00')",
            [],
        ).unwrap();

        let projects = project_breakdown(&conn, 2026, 7).unwrap();
        // Should be merged into one entry
        assert_eq!(projects.len(), 1);
        assert_eq!(projects[0].0, "proj-a");
        assert!((projects[0].1 - 3.00).abs() < f64::EPSILON); // cost merged
    }

    #[test]
    fn monthly_totals_sums_all_fields() {
        let conn = open_db_in_memory().unwrap();
        conn.execute(
            "INSERT INTO sessions (session_id, cost_usd, duration_ms, lines_added, lines_removed, started_at, updated_at)
             VALUES ('t1', 1.50, 60000, 10, 5, '2026-07-10T10:00:00+00:00', '2026-07-10T10:00:00+00:00')",
            [],
        ).unwrap();
        conn.execute(
            "INSERT INTO sessions (session_id, cost_usd, duration_ms, lines_added, lines_removed, started_at, updated_at)
             VALUES ('t2', 2.00, 120000, 20, 3, '2026-07-15T10:00:00+00:00', '2026-07-15T10:00:00+00:00')",
            [],
        ).unwrap();
        let (cost, subagent_cost, dur, added, removed) = monthly_totals(&conn, 2026, 7).unwrap();
        assert!((cost - 3.50).abs() < f64::EPSILON);
        assert!((subagent_cost - 0.0).abs() < f64::EPSILON);
        assert_eq!(dur, 180000);
        assert_eq!(added, 30);
        assert_eq!(removed, 8);
    }

    #[test]
    fn list_sessions_returns_recent_first() {
        let conn = open_db_in_memory().unwrap();
        seed_test_data(&conn);
        let sessions = list_sessions(&conn, 10, None).unwrap();
        // Should return all 5 sessions (including June), most recent first
        assert_eq!(sessions.len(), 5);
        assert_eq!(sessions[0].project, "proj-a"); // s4 = Jul 12
        assert_eq!(sessions[1].project, "proj-b"); // s3 = Jul 11
    }

    #[test]
    fn list_sessions_respects_limit() {
        let conn = open_db_in_memory().unwrap();
        seed_test_data(&conn);
        let sessions = list_sessions(&conn, 2, None).unwrap();
        assert_eq!(sessions.len(), 2);
    }

    #[test]
    fn list_sessions_filters_by_project() {
        let conn = open_db_in_memory().unwrap();
        seed_test_data(&conn);
        // Filter for "proj-a" — should match 3 sessions (s1, s2, s4)
        let sessions = list_sessions(&conn, 10, Some("proj-a")).unwrap();
        assert_eq!(sessions.len(), 3);
        assert!(sessions.iter().all(|s| s.project == "proj-a"));
    }

    #[test]
    fn all_time_totals_spans_all_months() {
        let conn = open_db_in_memory().unwrap();
        seed_test_data(&conn);
        let (cost, count, _dur) = all_time_totals(&conn).unwrap();
        // All 5 rows: 1.50 + 2.00 + 0.75 + 1.25 + 3.00 = 8.50
        assert!((cost - 8.50).abs() < f64::EPSILON);
        assert_eq!(count, 5);
    }

    #[test]
    fn all_time_totals_full_includes_lines() {
        let conn = open_db_in_memory().unwrap();
        conn.execute(
            "INSERT INTO sessions (session_id, cost_usd, duration_ms, lines_added, lines_removed, started_at, updated_at)
             VALUES ('f1', 1.00, 60000, 10, 5, '2026-07-10T10:00:00+00:00', '2026-07-10T10:00:00+00:00')",
            [],
        ).unwrap();
        conn.execute(
            "INSERT INTO sessions (session_id, cost_usd, duration_ms, lines_added, lines_removed, started_at, updated_at)
             VALUES ('f2', 2.00, 120000, 20, 3, '2026-06-15T10:00:00+00:00', '2026-06-15T10:00:00+00:00')",
            [],
        ).unwrap();
        let (cost, subagent_cost, count, dur, added, removed) =
            all_time_totals_full(&conn).unwrap();
        assert!((cost - 3.00).abs() < f64::EPSILON);
        assert!((subagent_cost - 0.0).abs() < f64::EPSILON);
        assert_eq!(count, 2);
        assert_eq!(dur, 180000);
        assert_eq!(added, 30);
        assert_eq!(removed, 8);
    }

    #[test]
    fn monthly_breakdown_groups_by_month() {
        let conn = open_db_in_memory().unwrap();
        seed_test_data(&conn);
        let months = monthly_breakdown(&conn).unwrap();
        // seed_test_data has June (s5) and July (s1-s4)
        assert_eq!(months.len(), 2);
        // First entry is June (s5 = $3.00)
        assert_eq!(months[0].0, 2026);
        assert_eq!(months[0].1, 6);
        assert!((months[0].2 - 3.00).abs() < f64::EPSILON);
        assert_eq!(months[0].3, 1);
        // Second entry is July (s1+s2+s3+s4 = $5.50)
        assert_eq!(months[1].0, 2026);
        assert_eq!(months[1].1, 7);
        assert!((months[1].2 - 5.50).abs() < f64::EPSILON);
        assert_eq!(months[1].3, 4);
    }

    #[test]
    fn all_time_model_breakdown_spans_all_months() {
        let conn = open_db_in_memory().unwrap();
        seed_test_data(&conn);
        let models = all_time_model_breakdown(&conn).unwrap();
        assert_eq!(models.len(), 2);
        // Opus 4.6: s1($1.50) + s2($2.00) + s5($3.00) = $6.50
        assert_eq!(models[0].0, "Opus 4.6");
        assert!((models[0].1 - 6.50).abs() < f64::EPSILON);
        // Sonnet 5: s3($0.75) + s4($1.25) = $2.00
        assert_eq!(models[1].0, "Sonnet 5");
        assert!((models[1].1 - 2.00).abs() < f64::EPSILON);
    }

    #[test]
    fn all_time_project_breakdown_merges_worktrees() {
        let conn = open_db_in_memory().unwrap();
        conn.execute(
            "INSERT INTO sessions (session_id, project_dir, model_id, model_name, cost_usd, duration_ms, lines_added, lines_removed, started_at, updated_at)
             VALUES ('at1', '/home/u/proj-a', 'opus', 'Opus', 1.00, 60000, 10, 5, '2026-07-10T10:00:00+00:00', '2026-07-10T10:00:00+00:00')",
            [],
        ).unwrap();
        conn.execute(
            "INSERT INTO sessions (session_id, project_dir, model_id, model_name, cost_usd, duration_ms, lines_added, lines_removed, started_at, updated_at)
             VALUES ('at2', '/home/u/proj-a/.claude/worktrees/feat-x', 'opus', 'Opus', 2.00, 30000, 20, 3, '2026-06-15T10:00:00+00:00', '2026-06-15T10:00:00+00:00')",
            [],
        ).unwrap();
        let projects = all_time_project_breakdown(&conn).unwrap();
        assert_eq!(projects.len(), 1);
        assert_eq!(projects[0].0, "proj-a");
        assert!((projects[0].1 - 3.00).abs() < f64::EPSILON);
        assert_eq!(projects[0].3, 30); // lines_added merged
    }

    #[test]
    fn upsert_session_stores_subagent_cost() {
        let conn = open_db_in_memory().unwrap();
        let record = SessionRecord {
            session_id: "sa1".into(),
            project_dir: Some("/home/u/proj".into()),
            model_id: None,
            model_name: None,
            cost_usd: 10.0,
            duration_ms: 60000,
            lines_added: 0,
            lines_removed: 0,
            subagent_cost_usd: 5.0,
        };
        upsert_session(&conn, &record).unwrap();
        let cost: f64 = conn
            .query_row(
                "SELECT subagent_cost_usd FROM sessions WHERE session_id = 'sa1'",
                [],
                |r| r.get(0),
            )
            .unwrap();
        assert!((cost - 5.0).abs() < f64::EPSILON);
    }

    #[test]
    fn upsert_subagent_runs_creates_and_updates() {
        let conn = open_db_in_memory().unwrap();
        let record = SessionRecord {
            session_id: "sa2".into(),
            project_dir: None,
            model_id: None,
            model_name: None,
            cost_usd: 10.0,
            duration_ms: 0,
            lines_added: 0,
            lines_removed: 0,
            subagent_cost_usd: 3.0,
        };
        upsert_session(&conn, &record).unwrap();
        let runs = vec![SubagentRunRecord {
            session_id: "sa2".into(),
            run_id: "abc123".into(),
            agent_name: Some("reviewer".into()),
            model_name: Some("Opus 4.6".into()),
            cost_usd: 3.0,
            input_tokens: 50000,
            output_tokens: 1000,
            duration_ms: 30000,
        }];
        upsert_subagent_runs(&conn, &runs).unwrap();
        let count: i64 = conn
            .query_row(
                "SELECT COUNT(*) FROM subagent_runs WHERE session_id = 'sa2'",
                [],
                |r| r.get(0),
            )
            .unwrap();
        assert_eq!(count, 1);
        // Upsert again with updated cost
        let runs2 = vec![SubagentRunRecord {
            session_id: "sa2".into(),
            run_id: "abc123".into(),
            agent_name: Some("reviewer".into()),
            model_name: Some("Opus 4.6".into()),
            cost_usd: 5.0,
            input_tokens: 80000,
            output_tokens: 2000,
            duration_ms: 45000,
        }];
        upsert_subagent_runs(&conn, &runs2).unwrap();
        let cost: f64 = conn
            .query_row(
                "SELECT cost_usd FROM subagent_runs WHERE session_id = 'sa2' AND run_id = 'abc123'",
                [],
                |r| r.get(0),
            )
            .unwrap();
        assert!((cost - 5.0).abs() < f64::EPSILON);
    }

    #[test]
    fn monthly_total_includes_subagent_cost() {
        let conn = open_db_in_memory().unwrap();
        conn.execute(
            "INSERT INTO sessions (session_id, cost_usd, subagent_cost_usd, duration_ms, lines_added, lines_removed, started_at, updated_at)
             VALUES ('sc1', 10.00, 5.00, 0, 0, 0, '2026-07-10T10:00:00+00:00', '2026-07-10T10:00:00+00:00')",
            [],
        ).unwrap();
        let total = get_monthly_total(&conn, 2026, 7).unwrap();
        assert!((total - 15.0).abs() < f64::EPSILON);
    }

    #[test]
    fn subagent_breakdown_groups_by_agent() {
        let conn = open_db_in_memory().unwrap();
        conn.execute(
            "INSERT INTO sessions (session_id, cost_usd, subagent_cost_usd, duration_ms, lines_added, lines_removed, started_at, updated_at)
             VALUES ('sb1', 10.00, 8.00, 0, 0, 0, '2026-07-10T10:00:00+00:00', '2026-07-10T10:00:00+00:00')",
            [],
        ).unwrap();
        conn.execute(
            "INSERT INTO subagent_runs (session_id, run_id, agent_name, model_name, cost_usd, input_tokens, output_tokens, duration_ms)
             VALUES ('sb1', 'r1', 'reviewer', 'Opus 4.6', 5.00, 50000, 1000, 30000)",
            [],
        ).unwrap();
        conn.execute(
            "INSERT INTO subagent_runs (session_id, run_id, agent_name, model_name, cost_usd, input_tokens, output_tokens, duration_ms)
             VALUES ('sb1', 'r2', 'reviewer', 'Opus 4.6', 3.00, 30000, 500, 20000)",
            [],
        ).unwrap();
        let breakdown = subagent_breakdown(&conn, 2026, 7).unwrap();
        assert_eq!(breakdown.len(), 1);
        assert_eq!(breakdown[0].0, "reviewer");
        assert!((breakdown[0].1 - 8.0).abs() < f64::EPSILON);
        assert_eq!(breakdown[0].2, 2); // run_count
    }
}