paddington 0.5.0

use chrono::Utc;
use rusqlite::{Connection, params};
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,
}

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
        );",
    )?;
    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();
    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, started_at, updated_at)
         VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?9)
         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,
             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,
            now,
        ],
    )?;
    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), 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, 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), 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::<_, i64>(1)? as u64, row.get::<_, i64>(2)? as u64, row.get::<_, i64>(3)? 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), 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), 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) 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, 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), 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(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, 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, 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), 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) 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)
}

#[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,
        };
        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,
        };
        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, dur, added, removed) = monthly_totals(&conn, 2026, 7).unwrap();
        assert!((cost - 3.50).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);
    }
}