/* Day 6: Intermediate SQL -- views, transactions, integrity constraints
Tim Pierson, Dartmouth CS61, Spring 2020 
*/
use nyc_inspections;

-- PRACTICE slide 4
-- create view for mapping Manhattan fruit/vegetable restaurants
-- first look at Cuisine types to see fruit/vegetable restaurants
SELECT * FROM Cuisine WHERE CuisineDescription LIKE '%fruit%'; 
-- CuisineID 12 is Juice,Smoothies, Fruit Salads
-- CuisineID 83 is Fruits/Vegetables -- this is the one we want
-- can query for 'fruit%' and will get the one we want

-- make sure to get restaurants with no scores (Tim's Tasty Treats from day 5)
DROP VIEW IF EXISTS MapData;
CREATE VIEW MapData AS
SELECT r.RestaurantID, RestaurantName, Latitude, Longitude, AVG(score) as AvgScore
FROM restaurants r LEFT JOIN inspections i ON r.RestaurantID = i.RestaurantID
WHERE boro = 'Manhattan' AND CuisineID = (SELECT CuisineID FROM Cuisine WHERE CuisineDescription LIKE 'fruit%')
GROUP BY RestaurantID; 
-- why not group by RestaurantName, Latitude, Longitude? -- no need after join creates relation
-- Remember order of evaluation is: 
-- 1) FROM (makes one relation using Cartesian Product, here on restaurants and inspections)
-- 2) WHERE (remove needed rows)
-- 3) SELECT (remove uneeded attributes)
-- 4) GROUP BY (now operating on reduced relations created in 1) and altered in 2) and 3)
-- 5) HAVING (not used here)
-- 6) ORDER BY

SELECT * from MapData;

-- here is another version of the query that uses COALESCE to put a 0 for average score if it is NULL
DROP VIEW IF EXISTS MapData;
CREATE VIEW MapData AS
SELECT r.RestaurantID, RestaurantName, Latitude, Longitude, COALESCE(AVG(score),0) AS AvgScore
FROM restaurants r LEFT JOIN inspections i ON r.RestaurantID = i.RestaurantID
WHERE boro = 'Manhattan' AND CuisineID = (SELECT CuisineID FROM Cuisine WHERE CuisineDescription LIKE 'fruit%')
GROUP BY RestaurantID; 

SELECT * from MapData;




-- PRACTICE slide 7
-- enter new cusine type and new restaurant
-- if restaurant name and address not in Restaurant table, create new restaurant with ID = max +1
-- insert cuisine type
-- make sure new cuisine type is not created unless restaurant entry succeeds
SET @RestaurantName = 'Tim''s Untasty Treats';
SET @Building = 123;
SET @Street = '180 Riverside Blvd';
SET @Boro = 'Manhattan';
SET @CuisineDescription = 'Sludge-based drinks';

SET AUTOCOMMIT = 0; -- turn off MySQL auto commit features so we can create a multi-statement transaction
START TRANSACTION;
-- Cuisine table has auto_increment id, do insert and get last id
INSERT INTO Cuisine (CuisineDescription)
	VALUES (@CuisineDescription);
SET @CuisineID = LAST_INSERT_ID();
SELECT @CuisineID; -- 128 (you might get a different result based on the state of your database)
-- check that it worked!
SELECT * FROM Cuisine; -- sludge-based drinks has CuisineID = 128


DELETE FROM Restaurants WHERE RestaurantName LIKE 'Tim''s Untasty%';
INSERT INTO Restaurants (RestaurantID, RestaurantName, Building, Street, Boro, CuisineID)
	VALUES ((SELECT MAX(RestaurantID)+1 FROM Restaurants r), @RestaurantName, @Building, @Street, @Boro,@CuisineID);
-- check that it worked!
SET @RestaurantID = LAST_INSERT_ID(); -- try to get last ID
SELECT @RestaurantID; -- does not work if key is not auto_increment!  @RestaurantID is last CuisineID!
SELECT * FROM Restaurants WHERE RestaurantID = LAST_INSERT_ID(); -- WRONG! still have CusineID
SELECT * FROM Restaurants WHERE RestaurantName LIKE '%untasty%'; -- RestaurantID = 50101873

-- uncomment ONE of the next two lines to either rollback or commit
-- ROLLBACK; 
COMMIT;

-- put MySQL back to autocommit mode

-- confirm rollback (or commit worked) and no changes were made to either Cuisine or Restaurants tables
SELECT * FROM Cuisine; -- sludge-based drinks had CuisineID = 128, now gone
SELECT * FROM Restaurants WHERE RestaurantName LIKE '%untasty%'; -- no entry
-- leave Tim's untasty treats and Sludge-based drinks in table




-- example from class creating constrains on employee table
CREATE TABLE Employees (
	EmployeeID	INT NOT NULL AUTO_INCREMENT,
	Name VARCHAR(20) NOT NULL,
	Phone INT UNIQUE,
	Salary INT,
	PRIMARY KEY (EmployeeID),
	CHECK (Salary > 0)
);

-- SHOW CREATE TABLE Employees; -- gives commands to create table (useful for existing tables)
INSERT INTO Employees VALUES (1,'Tim',123,100); -- succeeds
INSERT INTO Employees VALUES (2,'Ted',123,100); -- fails phone not unique
INSERT INTO Employees VALUES (3,'Tom',124,NULL); -- succeeds even though salary null
INSERT INTO Employees VALUES (4,'Tessa',124,0); -- fails, salary is not > 0

SELECT * FROM Employees; -- only Tim and Tom entered

DROP TABLE Employees;




-- PRACTICE slide 11
-- count how many times each score values appears
SELECT Score, count(*) FROM Inspections 
GROUP BY Score 
ORDER BY Score;
-- scores: NULL occurs 14,187, -1 occurs 24 times, 0 occurs 1653 times, etc...
 
-- currently many scores are null, 24 are -1, 1653 are 0, ...
-- devise a way to make sure we always get a score > 0
-- one solution: create a new column and add a CHECK constraint
-- create new col called AdjustedScore and set score of -1 to null
ALTER TABLE Inspections ADD COLUMN AdjustedScore INT;
UPDATE Inspections SET AdjustedScore = Score;
UPDATE Inspections 
	SET AdjustedScore = NULL 
	WHERE AdjustedScore = -1;
-- add constraint on AdjustedScore so value >=0
ALTER TABLE Inspections ADD CONSTRAINT CheckScore CHECK (AdjustedScore >=0);

-- Set up foreign key so that if delete a cuisine type, also set cuisineid to null in Restaurants
-- update foreign key constraint on Restaurants to Cuisine
-- add constraint ON DELETE to set values in Restaurant CuisineID to null if CuisineID deleted in Cuisine table
-- first find foreign keys by querying info schema 
-- easiest to look up foreign keys in MySQL Workbench! Hover on table, then click wrench, then foreign keys tab (at bottom)
-- here I look up foreign keys using SQL directly and query the database system's tables
SELECT * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA='nyc_inspections' AND TABLE_NAME='Restaurants';
-- the foreign key constraint is named 'fk_Restaurants_Cuisine1

-- remove existing foreign key called fk_Restaurants_Cuisine1
ALTER TABLE Restaurants DROP FOREIGN KEY fk_Restaurants_Cuisine1;

-- remove NOT NULL constraint on CuisineID in Restaurants table
ALTER TABLE Restaurants CHANGE COLUMN CuisineID CuisineID INT NULL ;

-- add new foreign key with ON DELETE
ALTER TABLE Restaurants 
	ADD CONSTRAINT fk_Restaurants_Cuisine2 
    FOREIGN KEY (CuisineID) REFERENCES Cuisine(CuisineID) 
    ON DELETE SET NULL; -- set CuisineID to null if it is deleted in Cuisine table
    

-- confirm Tim's Untasty Treats has CUISINE (this query has USING for the JOIN)
SELECT RestaurantID, RestaurantName, c.CuisineID, CuisineDescription
FROM Restaurants r LEFT JOIN Cuisine c USING (CuisineID)
WHERE RestaurantName like 'Tim''s%'; 

-- delete sludge-based drinks
DELETE FROM Cuisine WHERE CuisineDescription like 'Sludge%';

-- confirm untasty treats has CusineID of null (this query has ON for the JOIN)
SELECT RestaurantID, RestaurantName, c.CuisineID, CuisineDescription
FROM Restaurants r LEFT JOIN Cuisine c ON r.CuisineID = c.CuisineID 
WHERE RestaurantName like 'Tim''s%'; 

-- DELETE FROM Restaurants WHERE RestaurantID = 50101873;
    
