-- A simplified wpl setup file as an alterative breakdown for a wpl queue database.
-- Note that this version has a single-column tas table instead of a staff table used in lecture (which includes
-- admin/instructors) and some new data.
CREATE DATABASE IF NOT EXISTS miniwpldb;

USE miniwpldb;

DROP TABLE IF EXISTS questions;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS tas;

-- A table to include all users in the db, including students and tas
CREATE TABLE users(
  id VARCHAR(7) PRIMARY KEY,
  email VARCHAR(255) NOT NULL,
  name VARCHAR(255) NOT NULL
);

INSERT INTO users
VALUES ("1234567", "bndlss@uw.edu", "Dubs"),
       ("7654321", "twolman@uw.edu", "Tal"),
       ("3141592", "em66@uw.edu", "Manny"),
       ("1333337", "dbgdg@uw.edu", "Mowgli");

-- A single id for representing a TA
CREATE TABLE tas(
  id VARCHAR(7) PRIMARY KEY,
  FOREIGN KEY(id) REFERENCES users(id)
);

INSERT INTO tas
VALUES ("3141592"),
       ("7654321");

-- A table to hold information for each question entered in the queue
CREATE TABLE questions(
  id INT PRIMARY KEY AUTO_INCREMENT,
  question VARCHAR(255) NOT NULL,
  length TINYINT NOT NULL,
  student VARCHAR(7) NOT NULL,
  ta VARCHAR(7) NOT NULL,
  FOREIGN KEY(student) REFERENCES users(id),
  FOREIGN KEY(ta) REFERENCES tas(id)
);

INSERT INTO questions(question, length, student, ta)
VALUES ("I can't find my bone!", 2, "1234567", "7654321"),
       ("Who's bone is this?", 10, "1333337", "7654321"),
       ("Nevermind, I found it", 2, "1234567", "3141592");
