use crate::{
    model::{card::Card, entry::Entry},
    space_repetition,
    util::serialization,
};
use anyhow::Result;
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)
}

pub fn last_deck_read(conn: &Connection) -> Option<u64> {
    let mut stmt = conn
        .prepare("SELECT deck_read FROM cards ORDER BY deck_read DESC LIMIT 1")
        .ok()?;

    let mut rows = stmt.query([]).ok()?;
    let row = rows.next().ok()??;
    row.get(0).ok()?
}

/// 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::seconds_since_unix_epoch()?;

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

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

    delete_read_before(conn, now)?;

    Ok(())
}

fn insert(
    conn: &Connection,
    now: u64,
    question: &str,
    responses: &str,
    state: &str,
) -> Result<()> {
    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, 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_random_ready(conn: &Connection) -> Option<Card> {
    let now = time::seconds_since_unix_epoch().ok()?;

    let mut stmt = conn
        .prepare(
            "
        SELECT question, responses, state, ready
        FROM cards
        WHERE deleted IS NULL AND ready <= ?
        ORDER BY RANDOM()
        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()?,
        ready: row.get(3).ok()?,
    })
}

pub fn next_ready(conn: &Connection) -> Option<u64> {
    let mut stmt = conn
        .prepare(
            "
        SELECT ready 
        FROM cards 
        WHERE deleted IS NULL
        ORDER BY ready
        LIMIT 1
    ",
        )
        .ok()?;

    let mut rows = stmt.query([]).ok()?;
    let row = rows.next().ok()??;
    row.get(0).ok()?
}

pub fn count_available(conn: &Connection) -> Option<i32> {
    let now = time::seconds_since_unix_epoch().ok()?;
    let mut stmt = conn
        .prepare("SELECT COUNT(*) FROM cards WHERE ready <= ? AND deleted IS NULL")
        .ok()?;

    let mut rows = stmt.query([now]).ok()?;
    let row = rows.next().ok()??;
    row.get(0).ok()?
}

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