con <- dbConnect(MySQL(), user="trollbox", password="trollbox", dbname="trollbox-v1", host="localhost")
#on.exit(dbDisconnect(con))
rs <- dbSendQuery(con, "select DATE_FORMAT(
    MIN(created),
    '%H:%i:00'
  ) AS timestamp, sum(message.up) AS up, sum(message.down) AS down from message
join message_has_currency on message.id = message_has_currency.message_id join currency on message_has_currency.currency_id = currency.id
where currency.id = 1 and message.created > '2016-04-07 12:00:00'
GROUP BY ROUND(UNIX_TIMESTAMP(created) / 900)")
data <- fetch(rs)

rs2 <- dbSendQuery(con, "select DATE_FORMAT(
    MIN(created),
    '%H:%i:00'
  ) AS timestamp, sum(currency.id=1) as ethereum, sum(currency.id=2) as bitcoin from message join message_has_currency on message.id = message_has_currency.message_id join currency on message_has_currency.currency_id = currency.id
where message.created > '2016-04-07 12:00:00' GROUP BY ROUND(UNIX_TIMESTAMP(created) / 900)")
data2 <- fetch(rs2)

huh <- dbHasCompleted(rs)
huh2 <- dbHasCompleted(rs2)


# currency plot
ggplot(data2, aes(timestamp, y = count, color = currency)) + geom_line(aes(y = ethereum, col = "ethereum", group = 1)) + geom_line(aes(y = bitcoin, col = "bitcoin", group = 1))


# trend plot
ggplot(data, aes(timestamp, y = count, color = trend_category)) + geom_line(aes(y = up, col = "UP", group = 1)) + geom_line(aes(y = down, col = "DOWN", group = 1))

#dbClearResult(rs)
#dbDisconnect(con)