-- Author: Stephen J Sweeney
-- Web: www.battleforthesolarsystem.com
-- Email: stephenjsweeney@battleforthesolarsystem.com
--
-- The purpose of this file is to help anyone interested in porting the
-- Battle for the Solar System quiz to a mobile device. This SQL is currently
-- used in the Android version of the app. 
-- 
-- All below SQL is written for SQLite
--
-- Visit http://www.battleforthesolarsystem.com/quiz/ for more details
--
-------------------

-- create the categories table
CREATE TABLE quiz_categories (id int PRIMARY KEY, name varchar(64), lastUpdated timestamp)

-- create the chapters table
CREATE TABLE quiz_chapters (id int PRIMARY KEY, name varchar(64), category_id int, lastUpdated timestamp)

-- create the questions table
CREATE TABLE quiz_questions (id int PRIMARY KEY, category_id int, chapter_id int
question text, correctAnswer varchar(64), answer1 varchar(64), answer2 varchar(64)
answer3 varchar(64), creator varchar(64), lastUpdated timestamp)

-- delete everything in the tables
DELETE FROM quiz_categories
DELETE FROM quiz_chapters
DELETE FROM quiz_questions

-- insert into categories
INSERT INTO quiz_categories (id, name, lastUpdated) VALUES (?, ?, ?)

-- insert into chapters
INSERT INTO quiz_chapters (id, name, category_id, lastUpdated) VALUES (?, ?, ?, ?)

-- insert into questions
INSERT INTO quiz_questions 
(id, category_id, chapter_id, question, correctAnswer, answer1, answer2, answer3, creator, lastUpdated)
VALUES (?, ?, ?, ? , ?, ?, ?, ?, ?, ?)
			
-- count total number of questions
SELECT count(1) 
FROM quiz_questions

-- count total number of questions in a category
SELECT count(1) 
FROM quiz_questions 
WHERE category_id = ?

-- list categories
-- the "having count(1)" should be greater than a certain number,
-- such as 25, to stop the app from misbehaving if there are only 1 or 2
-- questions in a category
SELECT c.id, c.name
FROM quiz_questions
JOIN quiz_categories c ON (c.id = category_id)
GROUP BY name
HAVING COUNT(1) > ?
ORDER BY c.id

-- get the last update time
SELECT max(max(a.lastUpdated), max(b.lastUpdated), max(c.lastUpdated))
FROM quiz_questions a, quiz_chapters b, quiz_categories c

-- get some random questions from a particular category
SELECT qch.name, qq.id, qq.question, qq.correctAnswer, qq.answer1, qq.answer2, qq.answer3, qq.creator, qc.name
FROM quiz_questions qq
JOIN quiz_chapters qch ON qch.id = qq.chapter_id
JOIN quiz_categories qc ON qc.id = qq.category_id
WHERE qq.category_id = ?
ORDER BY RANDOM()
LIMIT ?

-- get some random questions
SELECT qch.name, qq.id, qq.question, qq.correctAnswer, qq.answer1, qq.answer2, qq.answer3, qq.creator, qc.name
FROM quiz_questions qq
JOIN quiz_chapters qch ON qch.id = qq.chapter_id
JOIN quiz_categories qc ON qc.id = qq.category_id
ORDER BY RANDOM()
LIMIT ?