3 files changed, 133 insertions, 0 deletions
diff --git a/src/db/db.rs b/src/db/db.rs
new file mode 100644
index 0000000..93c9564
--- /dev/null
+++ b/src/db/db.rs
@@ -0,0 +1,122 @@
+use crate::{
+    model::{card::Card, deck::Entry},
+    space_repetition,
+    util::serialization,
+};
+use anyhow::Result;
+use rusqlite::{params, Connection};
+use rusqlite_migration::{Migrations, M};
+use std::time::SystemTime;
+
+pub fn init() -> Result<Connection> {
+    let mut conn = Connection::open("database.db")?;
+    let migrations = Migrations::new(vec![M::up(include_str!("sql/1-init.sql"))]);
+    migrations.to_latest(&mut conn)?;
+    Ok(conn)
+}
+
+pub fn add_missing_deck_entries(conn: &Connection, entries: Vec<Entry>) -> Result<()> {
+    let now = get_current_time()?;
+    let ready = now;
+    let day: u64 = 60 * 60 * 24;
+
+    let state = serde_json::to_string(&space_repetition::init())?;
+
+    for entry in entries {
+        let concat_1 = serialization::words_to_line(&entry.part_1);
+        let concat_2 = serialization::words_to_line(&entry.part_2);
+
+        for (i, w) in entry.part_1.iter().enumerate() {
+            let r = ready + (i as u64) * day;
+            insert(&conn, now, r, &w, &concat_2, &state)?;
+        }
+
+        for (i, w2) in entry.part_2.iter().enumerate() {
+            let r = ready + ((entry.part_1.len() + i) as u64) * day;
+            insert(&conn, now, r, &w2, &concat_1, &state)?;
+        }
+    }
+
+    delete_read_before(&conn, now)?;
+
+    Ok(())
+}
+
+fn insert(
+    conn: &Connection,
+    now: u64,
+    ready: u64,
+    question: &String,
+    responses: &String,
+    state: &String,
+) -> Result<()> {
+    conn.execute(
+        "
+        INSERT INTO cards (question, responses, state, created, deck_read, ready)
+        VALUES (?, ?, ?, ?, ?, ?)
+        ON CONFLICT (question) DO UPDATE SET deck_read = ?, deleted = null
+        ",
+        params![question, responses, state, now, now, ready, now],
+    )?;
+
+    Ok(())
+}
+
+fn delete_read_before(conn: &Connection, t: u64) -> Result<()> {
+    conn.execute(
+        "UPDATE cards SET deleted = ? WHERE deck_read < ?",
+        params![t, t],
+    )?;
+
+    Ok(())
+}
+
+pub fn pick_ready(conn: &Connection) -> Option<Card> {
+    let now = get_current_time().ok()?;
+
+    let mut stmt = conn
+        .prepare(
+            "
+        SELECT question, responses, state
+        FROM cards
+        WHERE ready <= ? AND deleted IS NULL
+        ORDER BY ready
+        LIMIT 1
+        ",
+        )
+        .ok()?;
+
+    let mut rows = stmt.query([now]).ok()?;
+    let row = rows.next().ok()??;
+    let state_str: String = row.get(2).ok()?;
+    let responses_str: String = row.get(1).ok()?;
+
+    Some(Card {
+        question: row.get(0).ok()?,
+        responses: serialization::line_to_words(&responses_str),
+        state: serde_json::from_str(&state_str).ok()?,
+    })
+}
+
+pub fn update(conn: &Connection, question: &String, state: &space_repetition::State) -> Result<()> {
+    let now = get_current_time()?;
+    let ready = now + state.get_interval_seconds();
+    let state_str = serde_json::to_string(state)?;
+
+    conn.execute(
+        "
+        UPDATE cards
+        SET state = ?, updated = ?, ready = ?
+        WHERE question = ?
+        ",
+        params![state_str, now, ready, question],
+    )?;
+
+    Ok(())
+}
+
+fn get_current_time() -> Result<u64> {
+    Ok(SystemTime::now()
+        .duration_since(SystemTime::UNIX_EPOCH)?
+        .as_secs())
+}
diff --git a/src/db/mod.rs b/src/db/mod.rs
new file mode 100644
index 0000000..dec1023
--- /dev/null
+++ b/src/db/mod.rs
@@ -0,0 +1 @@
+pub mod db;
diff --git a/src/db/sql/1-init.sql b/src/db/sql/1-init.sql
new file mode 100644
index 0000000..29d70ed
--- /dev/null
+++ b/src/db/sql/1-init.sql
@@ -0,0 +1,10 @@
+CREATE TABLE IF NOT EXISTS cards (
+  question VARCHAR PRIMARY KEY,
+  responses VARCHAR NOT NULL,
+  state VARCHAR NOT NULL,
+  created TIMESTAMP NOT NULL,
+  updated TIMESTAMP NULL,
+  deleted TIMESTAMP NULL,
+  deck_read TIMESTAMP NOT NULL,
+  ready TIMESTAMP NOT NULL
+)