use anyhow::Result;
use rusqlite::{named_params, Connection};

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

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

    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 query = r#"
        INSERT INTO cards (question, responses, state, created, ready)
        VALUES (:question, :responses, :state, :created, :ready)
    "#;

    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(
            query,
            named_params![
                ":question": question,
                ":responses": responses,
                ":state": state,
                ":created": now,
                ":ready": now
            ],
        )?;
    }
    tx.commit()?;
    Ok(())
}

pub fn delete(conn: &mut Connection, questions: &Vec<Question>) -> Result<()> {
    let query = r#"
        UPDATE cards
        SET deleted = :deleted
        WHERE
            question = :question
            AND responses = :responses
    "#;

    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(
            query,
            named_params![
                ":deleted": now,
                ":question": question,
                ":responses": responses
            ],
        )?;
    }
    tx.commit()?;
    Ok(())
}

pub fn undelete(conn: &mut Connection, questions: &Vec<Question>) -> Result<()> {
    let query = r#"
        UPDATE cards
        SET deleted = NULL
        WHERE
            question = :questions
            AND responses = :responses
    "#;

    let tx = conn.transaction()?;
    for Question {
        question,
        responses,
    } in questions
    {
        let responses = serialization::words_to_line(responses);
        tx.execute(
            query,
            named_params![
                ":question": question,
                ":responses": responses
            ],
        )?;
    }
    tx.commit()?;
    Ok(())
}

pub fn pick_random_ready(conn: &Connection) -> Option<Card> {
    let query = r#"
        SELECT question, responses, state, ready
        FROM cards
        WHERE deleted IS NULL AND ready <= :ready
        ORDER BY RANDOM()
        LIMIT 1
    "#;

    let now = time::seconds_since_unix_epoch().ok()?;

    let mut stmt = conn.prepare(query).ok()?;
    let mut rows = stmt.query(named_params![":ready": 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 next_ready(conn: &Connection) -> Option<u64> {
    let query = r#"
        SELECT ready
        FROM cards
        WHERE deleted IS NULL
        ORDER BY ready
        LIMIT 1
    "#;

    let mut stmt = conn.prepare(query).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 query = r#"
        UPDATE cards
        SET state = :state, updated = :updated, ready = :ready
        WHERE question = :question
    "#;

    let now = time::seconds_since_unix_epoch()?;
    let ready = now + state.get_interval_seconds();
    let state_str = serde_json::to_string(state)?;

    conn.execute(
        query,
        named_params![
            ":state": state_str,
            ":updated": now,
            ":ready": ready,
            ":question": question
        ],
    )?;

    Ok(())
}