use rusqlite::Connection;
use error::{Result, LinoError};
use rand::{OsRng, Rng};

#[derive(Serialize, Debug, Clone)]
pub struct Vote {
    value: i32,
    is_user_vote: bool,
}

#[derive(Serialize, Debug, Clone)]
pub struct Quote {
    id: i64,
    date: String,
    author: String,
    score: String,
    points: i32,
    votes: i32,
    content: String,
    vote_options: Vec<Vote>,
}

fn make_quote(
    id: i64,
    date: String,
    author: String,
    content: String,
    user_vote: Option<i32>,
    points: i32,
    votes: i32,
) -> Quote {
    fn make_vote_options(user_vote: Option<i32>) -> Vec<Vote> {
        (1..6)
            .map({
                |v| {
                    Vote {
                        value: v,
                        is_user_vote: Some(v) == user_vote,
                    }
                }
            })
            .collect()
    }

    let score = if votes > 0 {
        format!("{:.1}", points as f64 / votes as f64)
    } else {
        "—".to_string()
    };
    Quote {
        id: id,
        date: date,
        author: author,
        points: points,
        votes: votes,
        content: content,
        score: score,
        vote_options: make_vote_options(user_vote),
    }
}

pub fn init(c: &Connection) -> Result<()> {
    info!("Initializing db");
    c.execute_batch(
        r#"
        CREATE TABLE IF NOT EXISTS quotes (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          approved BOOL DEFAULT 0,
          quote_date DATE CHECK (DATE(quote_date, '+0 days') IS quote_date),
          author TEXT NOT NULL,
          content TEXT NOT NULL,
          CONSTRAINT unique_content UNIQUE (quote_date, content)
        );

        CREATE TABLE IF NOT EXISTS votes (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          user_id INTEGER NOT NULL,
          quote_id INTEGER NOT NULL,
          score INTEGER NOT NULL,
          FOREIGN KEY(quote_id) REFERENCES quotes(id)
          CONSTRAINT unique_vote UNIQUE (user_id, quote_id)
        );

        CREATE TABLE IF NOT EXISTS keys (
          id TEXT PRIMARY KEY,
          value BLOB NOT NULL
        );
    "#,
    )?;

    let key = {
        let mut v = vec![0u8; 32];
        (OsRng::new()?).fill_bytes(v.as_mut());
        v
    };
    c.execute(
        r#"
               INSERT OR IGNORE INTO keys (id, value) VALUES
               ('session', ?1);
        "#,
        &[&key],
    )?;

    Ok(())
}

pub fn populate_test_db(c: &Connection) -> Result<()> {
    info!("Populating test db");
    try!(c.execute_batch(
        r#"
      INSERT INTO quotes (id, approved, quote_date, author, content) VALUES
        (1, 1, '2017-07-09', 'orbekk', 'test quote'),
        (2, 1, '2017-07-09', 'orbekk', 'test quote2'),
        (3, 0, '2017-07-09', 'orbekk', 'test quote3');

      INSERT INTO votes (quote_id, user_id, score) VALUES
        (1, 1, 2),
        (1, 2, 3),
        (1, 3, 1),
        (2, 1, 1),
        (2, 4, 2);
    "#,
    ));
    Ok(())
}

pub fn get_key(c: &Connection, id: &str) -> Result<Vec<u8>> {
    let key = c.query_row(
        r#"
               SELECT value FROM keys WHERE id = ?1
        "#,
        &[&id],
        |row| row.get(0),
    )?;
    Ok(key)
}

pub fn new_quote(c: &Connection, date: &str, author: &str, content: &str) -> Result<()> {
    c.execute(
        r#"
            INSERT INTO quotes (quote_date, author, content) VALUES
            (?1, ?2, ?3);
        "#,
        &[&date, &author, &content],
    )?;
    info!("New quote added by {}", author);
    Ok(())
}

pub fn new_vote(c: &Connection, user_id: i64, quote_id: i64, score: i32) -> Result<()> {
    c.execute(
        r#"
           INSERT OR REPLACE INTO votes (quote_id, user_id, score) VALUES
           (?1, ?2, ?3)
       "#,
        &[&quote_id, &user_id, &score],
    )?;
    info!("New vote: quote_id({}) score({})", quote_id, score);
    Ok(())
}

pub fn approve_quote(c: &Connection, quote_id: i64) -> Result<()> {
    c.execute(
        r#"
            UPDATE quotes
            SET approved = 1
            WHERE id = ?1
        "#,
        &[&quote_id],
    )?;
    Ok(())
}

pub fn delete_quote(c: &Connection, quote_id: i64) -> Result<()> {
    c.execute(
        r#"
            DELETE FROM quotes
            WHERE id = ?1
        "#,
        &[&quote_id],
    )?;
    Ok(())
}

fn internal_get_quotes(
    c: &Connection,
    id: Option<i64>,
    user_id: i64,
    ordering: &str,
    approved: bool,
) -> Result<Vec<Quote>> {
    // Arguments:
    // ?1: bool, whether to query for a specific quote
    // ?2: i64, quote id to use if ?1 is true
    // ?3: bool, whether to fetch approved or not approved quotes
    // ?4: i64, user_id
    let mut stmt = c.prepare(
        r#"
    SELECT q.id, q.quote_date, q.author, q.content, uv.score,
           COALESCE(sum(v.score), 0), count(v.score)
    FROM quotes q
    LEFT JOIN votes v ON (q.id = v.quote_id)
    LEFT JOIN votes uv ON (q.id = uv.quote_id AND uv.user_id = ?4)
    WHERE ((?1 AND q.id = ?2) OR (NOT ?1 AND q.approved = ?3))
    GROUP BY 1, 2, 3, 4, 5;
    ORDER BY q.id DESC;
  "#,
    )?;

    let rows = stmt.query_map(
        &[&id.is_some(), &id.unwrap_or(-1), &approved, &user_id],
        |row| {
            make_quote(
                row.get(0),
                row.get(1),
                row.get(2),
                row.get(3),
                row.get(4),
                row.get(5),
                row.get(6),
            )
        },
    )?;

    let result = rows.map(|r| r.map_err(|e| From::from(e)))
        .collect::<Result<Vec<Quote>>>();
    // For some reason, the ordering from sqlite doesn't work.
    result.map(|mut queries| {
        if ordering == "score" {
            queries.sort_by_key(|q| if q.votes > 0 {
                -(q.points / q.votes)
            } else {
                0
            });
        } else {
            queries.sort_by_key(|q| -q.id);
        }
        queries
    })
}

pub fn get_quotes(c: &Connection, user_id: i64, ordering: &str) -> Result<Vec<Quote>> {
    internal_get_quotes(c, None, user_id, ordering, true)
}

pub fn get_pending_quotes(c: &Connection) -> Result<Vec<Quote>> {
    internal_get_quotes(c, None, 0, "", false)
}

pub fn get_quote(c: &Connection, id: i64, user_id: i64) -> Result<Quote> {
    let quotes = internal_get_quotes(c, Some(id), user_id, "", true)?;
    quotes.into_iter().next().ok_or(LinoError::NotFound(
        format!("quote with id {}", id),
    ))
}