use crate::{
    model::{card::Card, entry::Entry},
    space_repetition,
    util::serialization,
};
use anyhow::Result;
use rand::{rngs::ThreadRng, Rng};
use rusqlite::{params, Connection};
use rusqlite_migration::{Migrations, M};

use crate::util::time;

pub fn init(database: String) -> Result<Connection> {
    let mut conn = Connection::open(database)?;
    let migrations = Migrations::new(vec![
        M::up(include_str!("sql/1-init.sql")),
        M::up(include_str!("sql/2-primary-key-question-responses.sql")),
    ]);
    migrations.to_latest(&mut conn)?;
    Ok(conn)
}

/// Synchronize the DB with the deck:
///
/// - insert new cards,
/// - keep existing cards,
/// - hide unused cards (keep state in case the card is added back afterward).
pub fn synchronize(conn: &Connection, entries: Vec<Entry>) -> Result<()> {
    let now = time::now()?;

    let state = serde_json::to_string(&space_repetition::init())?;
    let mut rng = rand::thread_rng();

    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 w in entry.part_1.iter() {
            insert(&conn, &mut rng, now, &w, &concat_2, &state)?;
        }

        for w in entry.part_2.iter() {
            insert(&conn, &mut rng, now, &w, &concat_1, &state)?;
        }
    }

    delete_read_before(&conn, now)?;

    Ok(())
}

fn insert(
    conn: &Connection,
    rng: &mut ThreadRng,
    now: u64,
    question: &String,
    responses: &String,
    state: &String,
) -> Result<()> {
    // Subtract a random amount of time so that cards are not initially given in the same order as
    // in the deck
    let ready_sub: u64 = rng.gen_range(0..100);

    conn.execute(
        "
        INSERT INTO cards (question, responses, state, created, deck_read, ready)
        VALUES (?, ?, ?, ?, ?, ?)
        ON CONFLICT (question, responses) DO UPDATE SET deck_read = ?, deleted = null
        ",
        params![question, responses, state, now, now, now - ready_sub, 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 next_ready(conn: &Connection) -> Option<Card> {
    let mut stmt = conn
        .prepare(
            "
        SELECT question, responses, state, ready
        FROM cards
        WHERE deleted IS NULL
        ORDER BY ready
        LIMIT 1
        ",
        )
        .ok()?;

    let mut rows = stmt.query([]).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()?,
        ready: row.get(3).ok()?,
    })
}

pub fn update(conn: &Connection, question: &String, state: &space_repetition::State) -> Result<()> {
    let now = time::now()?;
    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(())
}