admin.js

var mysql = require('promise-mysql');
var info = require('../config');



exports.createTables = async () => {

    try {

        const connection = await mysql.createConnection(info.config);

        //Clear out the old...
        sql = `DROP TABLE IF EXISTS users, questions, answers, quiz`;
        await connection.query(sql);


        //Set up the mySQL commands for the users table
        sql = `CREATE TABLE IF NOT EXISTS users (
            ID INT NOT NULL AUTO_INCREMENT,
            username VARCHAR(32) UNIQUE,
            password VARCHAR(256),
            PRIMARY KEY (ID)
        )`;
        //Create the users table
        await connection.query(sql);

        //Set up the mySQL commands for the quiz table
        sql = `CREATE TABLE IF NOT EXISTS quiz (
            ID INT NOT NULL AUTO_INCREMENT,
            title TEXT,
            instructions TEXT,
            timeAllowed TIME,
            creatorName TEXT,
            creatorID Int,
            allowReview BOOLEAN,
            allowFeedback BOOLEAN,
            allowPrevious BOOLEAN,
            passwordToView BOOLEAN,
            password VARCHAR(256),
            dateCreated DATE,
            dateExpires DATE,
            grade INT,
            PRIMARY KEY (ID)
        )`;
        //Create the quiz table
        await connection.query(sql);

        //Set up the mySQL commands for the questions table
        sql = `CREATE TABLE IF NOT EXISTS questions (
            ID INT NOT NULL AUTO_INCREMENT,
            questionText TEXT,
            grade INT,
            quizID INT,
            PRIMARY KEY (ID)
        )`;
        //Create the questions table
        await connection.query(sql);

        //Set up the mySQL commands for the answers table
        sql = `CREATE TABLE IF NOT EXISTS answers (
            ID INT NOT NULL AUTO_INCREMENT,
            answerText TEXT,
            correct BOOLEAN,
            questionID INT,
            PRIMARY KEY (ID)
        )`;
        //Create the answers table
        await connection.query(sql);



        //populate with dummy data to test - 3 quizzes - 2 to 4 questions - 3 to six options each
        sql = `INSERT INTO quiz VALUES (NULL, 'General Knowledge', 'Just answer as many as you can',
                003000, 'Josh', 1, TRUE, TRUE, FALSE, FALSE, NULL, 20200101, 20200808, 70)`;
        await connection.query(sql);


        sql = `INSERT INTO questions VALUES (NULL, 'What is the capital of France', 70, 1)`;
        await connection.query(sql);
        sql = `INSERT INTO answers VALUES (NULL, 'London', FALSE, 1)`;
        await connection.query(sql);
        sql = `INSERT INTO answers VALUES (NULL, 'Paris', TRUE, 1)`;
        await connection.query(sql);
        sql = `INSERT INTO answers VALUES (NULL, 'Rome', FALSE, 1)`;
        await connection.query(sql);
        sql = `INSERT INTO answers VALUES (NULL, 'Strasburg', FALSE, 1)`;
        await connection.query(sql);
        sql = `INSERT INTO answers VALUES (NULL, 'Amsterdam', FALSE, 1)`;
        await connection.query(sql);

        sql = `INSERT INTO questions VALUES (NULL, 'Do you come here often', 100, 1)`;
        await connection.query(sql);
        sql = `INSERT INTO answers VALUES (NULL, 'SOMETIMES', FALSE, 2)`;
        await connection.query(sql);
        sql = `INSERT INTO answers VALUES (NULL, 'YES', TRUE, 2)`;
        await connection.query(sql);
        sql = `INSERT INTO answers VALUES (NULL, 'NO', FALSE, 2)`;
        await connection.query(sql);






        sql = `INSERT INTO quiz VALUES (NULL, 'Food identification', 'Do you know the difference between foods and other stuff...',
                001000, 'Barry', 1, TRUE, TRUE, FALSE, FALSE, NULL, 20200101, 20200808, 70)`;
        await connection.query(sql);


        sql = `INSERT INTO questions VALUES (NULL, 'Which of these is a red food', 70, 2)`;
        await connection.query(sql);
        sql = `INSERT INTO answers VALUES (NULL, 'Cabbage', FALSE, 3)`;
        await connection.query(sql);
        sql = `INSERT INTO answers VALUES (NULL, 'Strawberry', TRUE, 3)`;
        await connection.query(sql);
        sql = `INSERT INTO answers VALUES (NULL, 'Bricks', FALSE, 3)`;
        await connection.query(sql);
        sql = `INSERT INTO answers VALUES (NULL, 'Milk', FALSE, 3)`;
        await connection.query(sql);

        sql = `INSERT INTO questions VALUES (NULL, 'Which ones of these can you eat', 70, 2)`;
        await connection.query(sql);
        sql = `INSERT INTO answers VALUES (NULL, 'Televisions', FALSE, 4)`;
        await connection.query(sql);
        sql = `INSERT INTO answers VALUES (NULL, 'Clouds',FALSE, 4)`;
        await connection.query(sql);
        sql = `INSERT INTO answers VALUES (NULL, 'People', FALSE, 4)`;
        await connection.query(sql);
        sql = `INSERT INTO answers VALUES (NULL, 'Sausages', TRUE, 4)`;
        await connection.query(sql);
        sql = `INSERT INTO answers VALUES (NULL, 'Doors', FALSE, 4)`;
        await connection.query(sql);

        sql = `INSERT INTO questions VALUES (NULL, 'How do you extract Oysters', 70, 2)`;
        await connection.query(sql);
        sql = `INSERT INTO answers VALUES (NULL, 'Crow bar', FALSE, 5)`;
        await connection.query(sql);
        sql = `INSERT INTO answers VALUES (NULL, 'Hammer',FALSE, 5)`;
        await connection.query(sql);
        sql = `INSERT INTO answers VALUES (NULL, 'Shuck', TRUE, 5)`;
        await connection.query(sql);
        sql = `INSERT INTO answers VALUES (NULL, 'Lick', FALSE, 5)`;
        await connection.query(sql);
        sql = `INSERT INTO answers VALUES (NULL, 'Tease', FALSE, 5)`;
        await connection.query(sql);



        sql = `INSERT INTO quiz VALUES (NULL, 'Games', 'Lots of questions about games',
                001200, 'Johnny John John', 1, TRUE, TRUE, FALSE, FALSE, NULL, 20200101, 20200808, 70)`;
        await connection.query(sql);

        sql = `INSERT INTO questions VALUES (NULL, 'Which ones of these are board games', 70, 3)`;
        await connection.query(sql);
        sql = `INSERT INTO answers VALUES (NULL, 'Crapulance', FALSE, 6)`;
        await connection.query(sql);
        sql = `INSERT INTO answers VALUES (NULL, 'Craps', FALSE, 6)`;
        await connection.query(sql);
        sql = `INSERT INTO answers VALUES (NULL, 'Poker', FALSE, 6)`;
        await connection.query(sql);
        sql = `INSERT INTO answers VALUES (NULL, 'Eating green chillie peppers', FALSE, 6)`;
        await connection.query(sql);
        sql = `INSERT INTO answers VALUES (NULL, 'Monopoly', TRUE, 6)`;
        await connection.query(sql);

        sql = `INSERT INTO questions VALUES (NULL, 'How many Queens in a deck of cards', 70, 3)`;
        await connection.query(sql);
        sql = `INSERT INTO answers VALUES (NULL, 'One', FALSE, 7)`;
        await connection.query(sql);
        sql = `INSERT INTO answers VALUES (NULL, 'Two', FALSE, 7)`;
        await connection.query(sql);
        sql = `INSERT INTO answers VALUES (NULL, 'Three', FALSE, 7)`;
        await connection.query(sql);
        sql = `INSERT INTO answers VALUES (NULL, 'Four', TRUE, 7)`;
        await connection.query(sql);
        sql = `INSERT INTO answers VALUES (NULL, 'Five', FALSE, 7)`;
        await connection.query(sql);
        sql = `INSERT INTO answers VALUES (NULL, 'Six', FALSE, 7)`;
        await connection.query(sql);

        sql = `INSERT INTO questions VALUES (NULL, 'What colour is Col Mustard', 70, 3)`;
        await connection.query(sql);
        sql = `INSERT INTO answers VALUES (NULL, 'Green', FALSE, 8)`;
        await connection.query(sql);
        sql = `INSERT INTO answers VALUES (NULL, 'Purple', FALSE, 8)`;
        await connection.query(sql);
        sql = `INSERT INTO answers VALUES (NULL, 'Orange', FALSE, 8)`;
        await connection.query(sql);
        sql = `INSERT INTO answers VALUES (NULL, 'Yellow', TRUE, 8)`;
        await connection.query(sql);
        sql = `INSERT INTO answers VALUES (NULL, 'Eat me', FALSE, 8)`;
        await connection.query(sql);
        sql = `INSERT INTO answers VALUES (NULL, 'Brown', FALSE, 8)`;
        await connection.query(sql);

        sql = `INSERT INTO questions VALUES (NULL, 'Which do you play with a ball', 70, 3)`;
        await connection.query(sql);
        sql = `INSERT INTO answers VALUES (NULL, 'Rounders', TRUE, 9)`;
        await connection.query(sql);
        sql = `INSERT INTO answers VALUES (NULL, 'Cricket', TRUE, 9)`;
        await connection.query(sql);
        sql = `INSERT INTO answers VALUES (NULL, 'Monoply', FALSE, 9)`;
        await connection.query(sql);
        sql = `INSERT INTO answers VALUES (NULL, 'Easter Egg Hunting', FALSE, 9)`;
        await connection.query(sql);
        sql = `INSERT INTO answers VALUES (NULL, 'Shotput', TRUE, 9)`;
        await connection.query(sql);

        sql = `INSERT INTO questions VALUES (NULL, 'Who plays football', 70, 3)`;
        await connection.query(sql);
        sql = `INSERT INTO answers VALUES (NULL, 'No one', FALSE, 10)`;
        await connection.query(sql);
        sql = `INSERT INTO answers VALUES (NULL, 'Gnomes', FALSE, 10)`;
        await connection.query(sql);
        sql = `INSERT INTO answers VALUES (NULL, 'People', TRUE, 10)`;
        await connection.query(sql);
        sql = `INSERT INTO answers VALUES (NULL, 'Monkeys', FALSE, 10)`;
        await connection.query(sql);
        sql = `INSERT INTO answers VALUES (NULL, 'Bricks', FALSE, 10)`;
        await connection.query(sql);
        

        return { message: "created successfully" };

    } catch (error) {
        console.log(error);
    }

}