paddington 0.2.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)
}

#[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 started_at >= ?1 AND started_at < ?2",
        params![start, end],
        |row| row.get(0),
    )
}

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 started_at >= ?1 AND started_at < ?2",
        params![start, end],
        |row| row.get(0),
    )
}

/// 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(started_at, 9, 2) AS INTEGER) as day,
                SUM(cost_usd), COUNT(*)
         FROM sessions
         WHERE started_at >= ?1 AND started_at < ?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::<_, u64>(2)?,
        ))
    })?;
    rows.collect()
}

/// Returns (model_name, total_cost) sorted by cost descending.
pub fn model_breakdown(
    conn: &Connection,
    year: i32,
    month: u32,
) -> rusqlite::Result<Vec<(String, f64)>> {
    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)
         FROM sessions
         WHERE started_at >= ?1 AND started_at < ?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)?))
    })?;
    rows.collect()
}

/// Returns (project_basename, total_cost) sorted by cost descending.
/// project_basename is the last component of project_dir.
pub fn project_breakdown(
    conn: &Connection,
    year: i32,
    month: u32,
) -> rusqlite::Result<Vec<(String, f64)>> {
    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)
         FROM sessions
         WHERE started_at >= ?1 AND started_at < ?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();
        let name = std::path::Path::new(&dir)
            .file_name()
            .and_then(|n| n.to_str())
            .unwrap_or("(unknown)")
            .to_string();
        Ok((name, row.get::<_, f64>(1)?))
    })?;
    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,
        };
        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);
        // Sorted by cost descending
        assert_eq!(models[0].0, "Opus 4.6");
        assert!((models[0].1 - 3.50).abs() < f64::EPSILON);
        assert_eq!(models[1].0, "Sonnet 5");
        assert!((models[1].1 - 2.00).abs() < f64::EPSILON);
    }

    #[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);
        // Should use last path component, sorted by cost descending
        assert_eq!(projects[0].0, "proj-a");
        assert!((projects[0].1 - 4.75).abs() < f64::EPSILON); // s1 + s2 + s4 = 1.50 + 2.00 + 1.25
    }
}