use rusqlite::Connection;
use error::Result;

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

pub fn init(c: &Connection) -> Result<()> {
    info!("Initializing db");
    try!(c.execute_batch(
        r#"
        CREATE TABLE quotes (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          approved BOOL DEFAULT false,
          timestamp DATETIME NOT NULL,
          author TEXT NOT NULL,
          content TEXT NOT NULL,
          CONSTRAINT unique_content UNIQUE (timestamp, content)
        );

        CREATE TABLE 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, 3),
        (2, 4);
    "#,
    ));
    Ok(())
}

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

    let mut rows = stmt.query_map(&[], |row| {
        Quote {
            id: row.get(0),
            date: row.get(1),
            author: row.get(2),
            content: row.get(3),
            score: format!("{:.2}", row.get::<i32, f64>(4) / row.get::<i32, f64>(5)),
            votes: row.get(5),
        }
    })?;

    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| {
        queries.sort_by_key(|q| -q.id);
        queries
    })
}