//! SQLite storage. One file in WAL mode; every query is scoped to a list.

use std::path::Path;
use std::sync::{Arc, Mutex};

use rusqlite::{Connection, OptionalExtension, params};

/// Schema steps, applied in order and tracked by `PRAGMA user_version`.
/// Only ever append to this list: a migration may add, never drop or rewrite.
const MIGRATIONS: &[&str] = &[
    "CREATE TABLE lists (
        id         INTEGER PRIMARY KEY,
        token      TEXT    NOT NULL UNIQUE,
        created_at INTEGER NOT NULL DEFAULT (unixepoch())
    );
    CREATE TABLE todos (
        id        INTEGER PRIMARY KEY,
        list_id   INTEGER NOT NULL REFERENCES lists(id),
        title     TEXT    NOT NULL,
        completed INTEGER NOT NULL DEFAULT 0
    );
    CREATE INDEX todos_by_list ON todos(list_id, id);",
];

#[derive(Debug, Clone, PartialEq, Eq)]
pub struct Todo {
    pub id: i64,
    pub title: String,
    pub completed: bool,
}

/// A shared handle to the one connection. SQLite serialises writers anyway,
/// and every statement here takes microseconds, so a mutex is all it needs.
#[derive(Clone)]
pub struct Db(Arc<Mutex<Connection>>);

impl Db {
    pub fn open(path: &Path) -> rusqlite::Result<Db> {
        let conn = Connection::open(path)?;
        conn.pragma_update(None, "journal_mode", "WAL")?;
        conn.pragma_update(None, "synchronous", "NORMAL")?;
        conn.pragma_update(None, "foreign_keys", "ON")?;
        conn.busy_timeout(std::time::Duration::from_secs(5))?;
        migrate(&conn)?;
        Ok(Db(Arc::new(Mutex::new(conn))))
    }

    /// Runs `f` with the connection on tokio's blocking pool.
    pub async fn run<T, F>(&self, f: F) -> rusqlite::Result<T>
    where
        T: Send + 'static,
        F: FnOnce(&mut Connection) -> rusqlite::Result<T> + Send + 'static,
    {
        let db = self.0.clone();
        tokio::task::spawn_blocking(move || {
            let mut conn = db.lock().unwrap_or_else(|e| e.into_inner());
            f(&mut conn)
        })
        .await
        .expect("database task panicked")
    }
}

fn migrate(conn: &Connection) -> rusqlite::Result<()> {
    let applied: i64 = conn.pragma_query_value(None, "user_version", |r| r.get(0))?;
    for (i, step) in MIGRATIONS.iter().enumerate().skip(applied as usize) {
        let tx = conn.unchecked_transaction()?;
        tx.execute_batch(step)?;
        tx.pragma_update(None, "user_version", i as i64 + 1)?;
        tx.commit()?;
    }
    Ok(())
}

pub fn find_list(conn: &Connection, token: &str) -> rusqlite::Result<Option<i64>> {
    conn.query_row("SELECT id FROM lists WHERE token = ?1", [token], |r| r.get(0))
        .optional()
}

pub fn create_list(conn: &Connection, token: &str) -> rusqlite::Result<i64> {
    conn.execute("INSERT INTO lists (token) VALUES (?1)", [token])?;
    Ok(conn.last_insert_rowid())
}

pub fn todos(conn: &Connection, list: i64) -> rusqlite::Result<Vec<Todo>> {
    let mut stmt = conn.prepare_cached(
        "SELECT id, title, completed FROM todos WHERE list_id = ?1 ORDER BY id",
    )?;
    stmt.query_map([list], |r| {
        Ok(Todo { id: r.get(0)?, title: r.get(1)?, completed: r.get(2)? })
    })?
    .collect()
}

pub fn add(conn: &Connection, list: i64, title: &str) -> rusqlite::Result<()> {
    conn.prepare_cached("INSERT INTO todos (list_id, title) VALUES (?1, ?2)")?
        .execute(params![list, title])?;
    Ok(())
}

/// Each of these returns whether the item was in the list.
pub fn toggle(conn: &Connection, list: i64, id: i64) -> rusqlite::Result<bool> {
    let n = conn
        .prepare_cached("UPDATE todos SET completed = NOT completed WHERE id = ?1 AND list_id = ?2")?
        .execute([id, list])?;
    Ok(n > 0)
}

pub fn rename(conn: &Connection, list: i64, id: i64, title: &str) -> rusqlite::Result<bool> {
    let n = conn
        .prepare_cached("UPDATE todos SET title = ?3 WHERE id = ?1 AND list_id = ?2")?
        .execute(params![id, list, title])?;
    Ok(n > 0)
}

pub fn destroy(conn: &Connection, list: i64, id: i64) -> rusqlite::Result<bool> {
    let n = conn
        .prepare_cached("DELETE FROM todos WHERE id = ?1 AND list_id = ?2")?
        .execute([id, list])?;
    Ok(n > 0)
}

/// Completes every item, or reopens them all if every one already was.
pub fn toggle_all(conn: &Connection, list: i64) -> rusqlite::Result<()> {
    let any_active: bool = conn
        .prepare_cached("SELECT EXISTS (SELECT 1 FROM todos WHERE list_id = ?1 AND NOT completed)")?
        .query_row([list], |r| r.get(0))?;
    conn.prepare_cached("UPDATE todos SET completed = ?2 WHERE list_id = ?1")?
        .execute(params![list, any_active])?;
    Ok(())
}

pub fn clear_completed(conn: &Connection, list: i64) -> rusqlite::Result<()> {
    conn.prepare_cached("DELETE FROM todos WHERE list_id = ?1 AND completed")?
        .execute([list])?;
    Ok(())
}