-- to run this file do the following:
--   Open a new terminal (Alt-T)
--   run "mysql-ctl start" to start your MySQL server
--   run "mysql-ctl cli" to open the MySQL command line interface
--   run "source q-store/setup.sql" (replace "q-store" with whichever directory your file is in)


-- Create DB
CREATE DATABASE IF NOT EXISTS qstore;
USE qstore;


-- drop old versions of reviews and products tables
DROP TABLE IF EXISTS reviews;
DROP TABLE IF EXISTS products;

-- create products and reviews table
CREATE TABLE products(
    id INT NOT NULL AUTO_INCREMENT, -- sql will automatically choose ids for me
    name VARCHAR(255),
    img  VARCHAR(255),
    description TEXT,
    PRIMARY KEY(id)
);

CREATE TABLE reviews(
    id INT NOT NULL AUTO_INCREMENT, -- sql will automatically choose ids for me
    product_id INT NOT NULL,
    review TEXT,
    PRIMARY KEY(id),
    FOREIGN KEY (product_id) REFERENCES products(id) -- sql now knows that product_ids must match ids in the product table
);

-- add products
INSERT INTO products (name, img, description) 
  VALUES ("Quilt #1", "quilt1.jpg", "This is a lovely quilt!");
INSERT INTO products (name, img, description) 
  VALUES ("Quilt #2", "quilt2.jpg", "This is also a lovely quilt!");
INSERT INTO products (name, img, description) 
  VALUES ("Quilt #3", "quilt3.jpg", "This is also a lovely quilt!");
INSERT INTO products (name, img, description) 
  VALUES ("Quartz #1", "quartz1.jpg", "A nice piece of quartz!");
INSERT INTO products (name, img, description) 
  VALUES ("Quartz #2", "quartz2.jpg", "A nice piece of quartz!");
INSERT INTO products (name, img, description) 
  VALUES ("Quartz #3", "quartz3.jpg", "A nice piece of quartz!");
INSERT INTO products (name, img, description) 
  VALUES ("Quinoa #1", "quinoa1.png", "Yummy quinoa!");
INSERT INTO products (name, img, description) 
  VALUES ("Quinoa #2", "quinoa2.jpg", "Yummy quinoa!");
INSERT INTO products (name, img, description) 
  VALUES ("Quinoa #3", "quinoa3.jpg", "Yummy quinoa!");
 
-- add reviews
INSERT INTO reviews (product_id, review) 
  VALUES (
    (SELECT id FROM products WHERE name = "Quilt #1"), 
    "A perfect quilt! 5 stars!");
INSERT INTO reviews (product_id, review) 
  VALUES (
    (SELECT id FROM products WHERE name = "Quilt #1"), 
    "It was just scraps of cloth stitched together. Why not make it all one material! 0 stars!");
INSERT INTO reviews (product_id, review) 
  VALUES (
    (SELECT id FROM products WHERE name = "Quilt #2"), 
    "Very quilt-like!");
INSERT INTO reviews (product_id, review) 
  VALUES (
    (SELECT id FROM products WHERE name = "Quartz #1"), 
    "Meh. 2.5 stars");
INSERT INTO reviews (product_id, review) 
  VALUES (
    (SELECT id FROM products WHERE name = "Quartz #1"), 
    "These quartz were not magical! 0 stars!");
INSERT INTO reviews (product_id, review) 
  VALUES (
    (SELECT id FROM products WHERE name = "Quartz #2"), 
    "Ooohhh, it's pink! 4 stars.");
INSERT INTO reviews (product_id, review) 
  VALUES (
    (SELECT id FROM products WHERE name = "Quartz #2"), 
    "There are weird dark specks. 2 stars.");
INSERT INTO reviews (product_id, review) 
  VALUES (
    (SELECT id FROM products WHERE name = "Quartz #3"), 
    "Very moody! 5 stars.");
INSERT INTO reviews (product_id, review) 
  VALUES (
    (SELECT id FROM products WHERE name = "Quartz #3"), 
    "Sharp points. 2 stars.");
INSERT INTO reviews (product_id, review) 
  VALUES (
    (SELECT id FROM products WHERE name = "Quinoa #1"), 
    "Too grainy. 3 stars.");
INSERT INTO reviews (product_id, review) 
  VALUES (
    (SELECT id FROM products WHERE name = "Quinoa #1"), 
    "Quinoa is too popular now, I've moved on to organic kamut. 2 stars.");
INSERT INTO reviews (product_id, review) 
  VALUES (
    (SELECT id FROM products WHERE name = "Quinoa #1"), 
    "This is the tastiest of the quinoa options. 5 stars.");