use rusqlite::Connection;
use error::{Result, LinoError};

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

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,
          timestamp DATETIME NOT NULL,
          author TEXT NOT NULL,
          content TEXT NOT NULL,
          CONSTRAINT unique_content UNIQUE (timestamp, content)
        );

        CREATE TABLE IF NOT EXISTS votes (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          quote_id INTEGER NOT NULL,
          score INTEGER NOT NULL,
          FOREIGN KEY(quote_id) REFERENCES quotes(id)
        );
    "#,
    )?;
    Ok(())
}

pub fn populate_test_db(c: &Connection) -> Result<()> {
    info!("Populating test db");
    try!(c.execute_batch(
        r#"
      INSERT INTO quotes (id, approved, timestamp, 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, score) VALUES
        (1, 2),
        (1, 3),
        (1, 1),
        (2, 1),
        (2, 2);
    "#,
    ));
    Ok(())
}

pub fn new_quote(c: &Connection, date: &str, author: &str, content: &str) -> Result<()> {
    c.execute(
        r#"
            INSERT INTO quotes (timestamp, author, content) VALUES
            (?1, ?2, ?3);
        "#,
        &[&date, &author, &content],
    )?;
    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(())
}

fn internal_get_quotes(c: &Connection, id: Option<i64>, ordering: &str) -> Result<Vec<Quote>> {
    let mut stmt = c.prepare(
        r#"
    SELECT q.id, q.timestamp, q.author, q.content,
           COALESCE(sum(v.score), 0), count(v.score)
    FROM quotes q
    LEFT JOIN votes v ON (q.id = v.quote_id)
    WHERE (?1 AND q.id = ?2) OR (NOT ?1 AND q.approved)
    GROUP BY 1, 2, 3, 4;
    ORDER BY q.id DESC;
  "#,
    )?;

    let rows = stmt.query_map(&[&id.is_some(), &id.unwrap_or(-1)], |row| {
        let points = row.get(4);
        let votes = row.get(5);
        let score = if votes > 0 {
            format!("{:.1}", points as f64 / votes as f64)
        } else {
            "—".to_string()
        };
        Quote {
            id: row.get(0),
            date: row.get(1),
            author: row.get(2),
            content: row.get(3),
            score: score,
            points: points,
            votes: votes,
        }
    })?;

    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, ordering: &str) -> Result<Vec<Quote>> {
    internal_get_quotes(c, None, ordering)
}

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