use anyhow::Result;
use rusqlite::{params, Connection};
use rusqlite_migration::{Migrations, M};

use crate::model::DbEntry;
use crate::util::time;
use crate::{
    model::{Card, Question},
    space_repetition,
    util::serialization,
};

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")),
        M::up(include_str!("sql/3-drop-deck-read.sql")),
    ]);
    migrations.to_latest(&mut conn)?;
    Ok(conn)
}

pub fn all(conn: &Connection) -> Result<Vec<DbEntry>> {
    let mut stmt = conn.prepare("SELECT question, responses, deleted FROM cards")?;

    let res: Result<Vec<DbEntry>, rusqlite::Error> = stmt
        .query_map([], |row| {
            let responses: String = row.get(1)?;
            Ok(DbEntry {
                question: row.get(0)?,
                responses: serialization::line_to_words(&responses),
                deleted: row.get(2)?,
            })
        })?
        .collect();

    Ok(res?)
}

pub fn insert(conn: &mut Connection, questions: &Vec<Question>) -> Result<()> {
    let now = time::seconds_since_unix_epoch()?;
    let state = serde_json::to_string(&space_repetition::init())?;

    let tx = conn.transaction()?;
    for Question {
        question,
        responses,
    } in questions
    {
        let responses = serialization::words_to_line(responses);
        tx.execute(
            "
            INSERT INTO cards (question, responses, state, created, ready)
            VALUES (?, ?, ?, ?, ?)
            ",
            params![question, responses, state, now, now],
        )?;
    }
    tx.commit()?;
    Ok(())
}

pub fn delete(conn: &mut Connection, questions: &Vec<Question>) -> Result<()> {
    let now = time::seconds_since_unix_epoch()?;

    let tx = conn.transaction()?;
    for Question {
        question,
        responses,
    } in questions
    {
        let responses = serialization::words_to_line(responses);
        tx.execute(
            "UPDATE cards SET deleted = ? WHERE question = ? AND responses = ?",
            params![now, question, responses],
        )?;
    }
    tx.commit()?;
    Ok(())
}

pub fn undelete(conn: &mut Connection, questions: &Vec<Question>) -> Result<()> {
    let tx = conn.transaction()?;
    for Question {
        question,
        responses,
    } in questions
    {
        let responses = serialization::words_to_line(responses);
        tx.execute(
            "UPDATE cards SET deleted = NULL WHERE question = ? AND responses = ?",
            params![question, responses],
        )?;
    }
    tx.commit()?;
    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(())
}