usenet_reborn 0.2.2

use crate::threads::FlatArticle;
use rusqlite::{params, Connection, Result};

/// Initialize (or open) the SQLite database and apply our schema.
pub fn init_db(path: &str) -> Result<Connection> {
    let conn = Connection::open(path)?;
    conn.execute_batch(
        "
        PRAGMA foreign_keys = ON;

        CREATE TABLE IF NOT EXISTS group_state (
            group_name TEXT PRIMARY KEY,
            last_seen_id INTEGER NOT NULL
        );
        CREATE TABLE IF NOT EXISTS article_cache (
            group_name TEXT NOT NULL,
            article_id TEXT NOT NULL,
            subject TEXT,
            sender TEXT,
            date TEXT,
            message_id TEXT,
            article_references TEXT,
            read_flag INTEGER NOT NULL DEFAULT 0,
            PRIMARY KEY(group_name, article_id)
        );
        ",
    )?;
    Ok(conn)
}

/// Get the last‐seen article ID for this group (or 0 if none).
pub fn get_last_seen(conn: &Connection, group: &str) -> Result<u32> {
    conn.query_row(
        "SELECT last_seen_id FROM group_state WHERE group_name = ?1",
        params![group],
        |row| row.get(0),
    )
    .or(Ok(0))
}

/// Set (or update) the last‐seen article ID for this group.
pub fn set_last_seen(conn: &Connection, group: &str, last_seen_id: u32) -> Result<()> {
    conn.execute(
        "INSERT INTO group_state (group_name, last_seen_id) VALUES (?1, ?2)
         ON CONFLICT(group_name) DO UPDATE SET last_seen_id = excluded.last_seen_id",
        params![group, last_seen_id],
    )?;
    Ok(())
}

/// Cache a subject line for one article.
pub fn cache_subject(
    conn: &Connection,
    group: &str,
    article_id: &str,
    subject: &str,
    sender: &str,
    date: &str,
    message_id: Option<&str>,
    article_references: Option<&str>,
) -> Result<()> {
    conn.execute(
        "INSERT OR IGNORE INTO article_cache (group_name, article_id, subject, sender, date, message_id, article_references)
         VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7)",
        params![
            group,
            article_id,
            subject,
            sender,
            date,
            message_id.unwrap_or(""),
            article_references.unwrap_or(""),
        ],
    )?;
    Ok(())
}

/// Load all unread subjects for a group in newest‐first order.
/// Load all unread subjects (with sender + date) for a group in newest‐first order.
pub fn load_unread_subjects(
    conn: &Connection,
    group: &str,
) -> Result<Vec<(String, String, String, String)>> {
    let mut stmt = conn.prepare(
        "SELECT article_id, subject, sender, date FROM article_cache
         WHERE group_name = ?1 AND read_flag = 0
         ORDER BY CAST(article_id AS INTEGER) DESC",
    )?;
    let rows = stmt
        .query_map(params![group], |row| {
            Ok((
                row.get(0)?, // article_id
                row.get(1)?, // subject
                row.get(2)?, // sender
                row.get(3)?, // date
            ))
        })?
        .collect::<Result<Vec<_>, _>>()?;
    Ok(rows)
}

/// Mark an article as “read” in the cache.
pub fn mark_as_read(conn: &Connection, group: &str, id: &str) -> Result<(), rusqlite::Error> {
    conn.execute(
        "UPDATE article_cache SET read_flag = 1 WHERE group_name = ?1 AND article_id = ?2",
        params![group, id],
    )?;
    Ok(())
}

/// Mark an article as “unread” in the cache.
pub fn mark_as_unread(conn: &Connection, group: &str, id: &str) -> Result<(), rusqlite::Error> {
    conn.execute(
        "UPDATE article_cache SET read_flag = 0 WHERE group_name = ?1 AND article_id = ?2",
        params![group, id],
    )?;
    Ok(())
}

// For threads
pub fn load_threadable_articles(
    conn: &Connection,
    group: &str,
) -> Result<Vec<FlatArticle>, rusqlite::Error> {
    let mut stmt = conn.prepare(
        "SELECT article_id, subject, sender, date, message_id, article_references FROM article_cache
         WHERE group_name = ?1 AND read_flag = 0
         ORDER BY CAST(article_id AS INTEGER) DESC",
    )?;
    let rows = stmt
        .query_map(params![group], |row| {
            Ok(FlatArticle {
                article_id: row.get(0)?,
                subject: row.get(1)?,
                sender: row.get(2)?,
                date: row.get(3)?,
                message_id: row.get(4)?,
                article_references: row.get(5)?,
            })
        })?
        .collect::<Result<Vec<_>, _>>()?;
    Ok(rows)
}