const CREATE_TABLE_IMAGES = "CREATE TABLE IF NOT EXISTS images (id INTEGER PRIMARY KEY, src TEXT, dataUri TEXT)";
const CREATE_TABLE_QUESTIONS = "CREATE TABLE IF NOT EXISTS questions (id INTEGER PRIMARY KEY, updated DATETIME DEFAULT CURRENT_TIMESTAMP, text TEXT)";
const CREATE_TABLE_ANSWERS = "CREATE TABLE IF NOT EXISTS answers (id INTEGER PRIMARY KEY, aid INTEGER, qid INTEGER, text TEXT, correct INTEGER, checked INTEGER)";
const CREATE_TABLE_ANSWERING = "CREATE TABLE IF NOT EXISTS answering (id INTEGER PRIMARY KEY, created DATETIME DEFAULT CURRENT_TIMESTAMP, version TEXT, href TEXT)";

const SELECT_ALL = "SELECT q.id AS qid, q.text AS question, i.dataUri AS image, T2.id AS aid, T2.text AS answer, T2.correct FROM questions q \
LEFT JOIN (SELECT qid, MAX(aid) AS maxid FROM answers GROUP BY qid) AS T1 ON q.id = T1.qid \
LEFT JOIN answers AS T2 ON T2.aid = T1.maxid AND T2.qid = q.id \
LEFT JOIN images AS i ON i.id = q.id ORDER BY T2.id DESC";



module.exports = {
  SELECT_ALL: SELECT_ALL
}


app.get('/top-mistakes', function (req, res) {
  db.all("SELECT q.id AS qid, q.text as question, a.acount AS mistakes FROM questions q " +
  "JOIN (SELECT qid, count(*) as acount FROM answers WHERE correct != checked GROUP BY aid,qid) AS a ON a.qid = q.id ORDER BY mistakes DESC",
  function (err, rows) {
    res.render('index', { questions: rows });
  });
});