/* Day 7: Advanced SQL -- Stored procedures/functions and triggers
Tim Pierson, Dartmouth CS61, Spring 2020 
*/

use nyc_inspections;

-- consider this query that you might run frequently
SELECT * FROM Restaurants WHERE boro = 'Manhattan';

-- can save query as stored procedure
DROP PROCEDURE IF EXISTS GetManhattanRestaurants;
DELIMITER $$
CREATE PROCEDURE GetManhattanRestaurants()
BEGIN
    SELECT * FROM Restaurants WHERE boro = 'Manhattan';
END$$
DELIMITER ;

-- call stored procedure
CALL GetManhattanRestaurants();

-- stored procedures can take variables (in or out or inout)
DROP PROCEDURE IF EXISTS GetRestaurantsByBoro;
DELIMITER $$
CREATE PROCEDURE GetRestaurantsByBoro(
	IN BoroName VARCHAR(20),
    OUT RestaurantCount INT)
BEGIN
    SELECT * FROM Restaurants WHERE boro = BoroName; -- comment out if only want count
    SELECT count(*) INTO RestaurantCount
		FROM Restaurants WHERE boro = BoroName;
END$$
DELIMITER ;

CALL GetRestaurantsByBoro('Manhattan',@BoroCount);
SELECT @BoroCount; -- 10,651





-- PRACTICE slide 11
-- create stored procedure to return min, max, avg and count of inspection scores
-- for restaurant given RestaurantID
-- create stored procedure to get summary of inspection scores for the restaurant
-- with IN parameter matching RestauranID
DROP PROCEDURE IF EXISTS InspectionSummary;
DELIMITER $$
CREATE PROCEDURE InspectionSummary (
    IN RestaurantIDParam INT,
    OUT MinScore DECIMAL(6,2),
    OUT MaxScore DECIMAL(6,2),
    OUT AvgScore DECIMAL(6,2),
    OUT CountScores INT
) 
BEGIN
	-- demonstrate we can use multiple SQL statements in stored procedures
	SELECT MIN(Score) INTO MinScore FROM Inspections WHERE RestaurantID = RestaurantIDParam;
    SELECT MAX(Score) INTO MaxScore FROM Inspections WHERE RestaurantID = RestaurantIDParam;
    SELECT AVG(Score) INTO AvgScore FROM Inspections WHERE RestaurantID = RestaurantIDParam;
    SELECT count(*) INTO CountScores FROM Inspections WHERE RestaurantID = RestaurantIDParam;
    
    -- instead could write
    /*
    SELECT MIN(Score), MAX(Score), AVG(Score), Count(*) 
    INTO MinScore, MaxScore, AvgScore, CountScores
    FROM Inspections WHERE RestaurantID = RestaurantIDParam;
    */
END $$
DELIMITER ;

-- test with Morris Park Bake Shop located at 1007 Morris Park Ave
SELECT * 
FROM Inspections i JOIN Restaurants r  ON i.RestaurantID = r.RestaurantID
WHERE RestaurantName LIKE 'Morris Park Bake%'
ORDER BY Score; -- Min Score 5, Max Score 17, Avg score 9.33, count 6

CALL InspectionSummary((SELECT RestaurantID FROM Restaurants WHERE RestaurantName LIKE 'Morris%' AND Building = 1007 AND Street = 'Morris Park Ave' LIMIT 1),
	@MinScore, @MaxScore, @AvgScore, @CountScores);
SELECT @MinScore, @MaxScore, @AvgScore, @CountScores; -- Min 5, Max 17, Avg 9.33, count 6

-- see stored procedures available (could also click on Stored Procedures in MySQL Workbench)
SHOW PROCEDURE STATUS WHERE Db like 'nyc%';



-- FUNCTIONS
-- create a function to test if string is a NYC boro
DROP FUNCTION IF EXISTS IsBoro;
DELIMITER $$
CREATE FUNCTION IsBoro(
	BoroName VARCHAR(20))
RETURNS Boolean
DETERMINISTIC
BEGIN
    IF BoroName IN ('Manhattan','Brooklyn','Queens','Bronx','Staten Island') THEN
		RETURN 1;
	END IF;
	RETURN 0;
END$$
DELIMITER ;

SELECT IsBoro('Queens') AS Queens, IsBoro('New Hampshire') AS NH;





-- PRACTICE slide 14
-- Create a function to classify restaurants based on the number of inspections
-- return 'Low' if the number of inspection < 10, 'Frequent' if greater than 15, else 'Intermediate'
DROP FUNCTION IF EXISTS RestaurantInspectionFrequency;
DELIMITER $$
CREATE FUNCTION RestaurantInspectionFrequency (
    InspectionCount INT
) 
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
	DECLARE level VARCHAR(20);
	IF InspectionCount < 7 THEN
		SET level = 'Low';
	ELSEIF InspectionCount > 12 THEN
		SET level = 'High';
	ELSE
		SET level = 'Intermediate';
	END IF;
	RETURN (level);
END $$
DELIMITER ;

-- use the function in a SELECT
SELECT RestaurantName, count(*) AS Count, RestaurantInspectionFrequency(count(*)) AS Classification 
FROM Restaurants r JOIN Inspections i ON r.RestaurantID = i.RestaurantID 
GROUP BY r.RestaurantID; 




-- TRIGGERS

-- notice there are two columns we created early to track the average inspection score and a count of inspection scores
-- for each restaurant.  These scores are currently NULL for each restaurant
-- we want to update these values when a new inspection is added, one is updated, or deleted
SELECT RestaurantID, RestaurantName, InspectionCount, InspectionAvgScore 
FROM Restaurants; -- InspectionCount and InspectionAvgScores are all NULLs

-- can update counts and average score for all restaurants with the following command
UPDATE Restaurants r 
	SET InspectionCount = (SELECT count(*) from Inspections i WHERE i.RestaurantID = r.RestaurantID),
    InspectionAvgScore = (SELECT AVG(Score) from Inspections i WHERE i.RestaurantID = r.RestaurantID);


-- create trigger on insert to Inspection table to keep count and avg score updated 
-- in Restaurants table
DROP TRIGGER IF EXISTS UpdateInspectionStatsOnInsert;
DELIMITER $$
CREATE TRIGGER UpdateInspectionStatsOnInsert
	AFTER INSERT ON Inspections
    FOR EACH ROW
    BEGIN
		-- update both columns in Restaurants table
		UPDATE Restaurants SET 
			InspectionAvgScore = (SELECT AVG(Score) 
				FROM Inspections i WHERE i.RestaurantID = NEW.RestaurantID LIMIT 1) 
			WHERE RestaurantID = NEW.RestaurantID;
        UPDATE Restaurants SET 
			InspectionCount = (SELECT count(*) 
				FROM Inspections i WHERE i.RestaurantID = NEW.RestaurantID)  
			WHERE RestaurantID = NEW.RestaurantID;
    END$$
DELIMITER ;

-- create trigger on delete from Inspection table to keep count and avg score updated
DROP TRIGGER IF EXISTS UpdateInspectionStatsOnDelete;
DELIMITER $$
CREATE TRIGGER UpdateInspectionStatsOnDelete
	AFTER DELETE ON Inspections
    FOR EACH ROW
    BEGIN
		UPDATE Restaurants SET InspectionAvgScore = (SELECT AVG(Score) FROM Inspections i WHERE i.RestaurantID = OLD.RestaurantID LIMIT 1) 
			WHERE RestaurantID = OLD.RestaurantID;
		UPDATE Restaurants SET InspectionCount = (SELECT IF (count(*) >0,count(*),NULL) FROM Inspections i WHERE i.RestaurantID = OLD.RestaurantID)
			WHERE RestaurantID = OLD.RestaurantID;
    END$$
    
DELIMITER ;

-- create trigger on update to Inspection table to keep count and avg score updated
DROP TRIGGER IF EXISTS UpdateInspectionStatsOnUpdate;
DELIMITER $$
CREATE TRIGGER UpdateInspectionStatsOnUpdate
	AFTER Update ON Inspections
    FOR EACH ROW
    BEGIN
		UPDATE Restaurants SET InspectionAvgScore = (SELECT AVG(Score) FROM Inspections i WHERE i.RestaurantID = NEW.RestaurantID LIMIT 1) 
			WHERE RestaurantID = NEW.RestaurantID;
		UPDATE Restaurants SET InspectionCount = (SELECT IF (count(*) >0,count(*),NULL) FROM Inspections i WHERE i.RestaurantID = NEW.RestaurantID)
			WHERE RestaurantID = NEW.RestaurantID;
    END$$
    
DELIMITER ;


SELECT RestaurantID, RestaurantName, InspectionCount, InspectionAvgScore FROM Restaurants; -- InspectionCount and Avg are NULL (Tim's Tasty Treats ID = 1111)
INSERT INTO Inspections (RestaurantID, Score) VALUES (1111,15); -- add inspection score for Tim's Tasty Treats
SELECT RestaurantID, RestaurantName, InspectionCount, InspectionAvgScore FROM Restaurants WHERE RestaurantName like 'Tim''s%'; -- count is 1, avg score is 15
-- update inspection score for Tim's Tasty Treats
UPDATE Inspections SET Score = 20 WHERE RestaurantID = 1111; -- update score to 20
SELECT RestaurantID, RestaurantName, InspectionCount, InspectionAvgScore FROM Restaurants WHERE RestaurantName like 'Tim''s%'; -- count is 1, avg score is 20
-- insert another inspection score for Tim's
INSERT INTO Inspections (RestaurantID, Score) VALUES (1111,10); -- add inspection score for Tim's Tasty Treats
SELECT RestaurantID, RestaurantName, InspectionCount, InspectionAvgScore FROM Restaurants WHERE RestaurantName like 'Tim''s%'; -- count is 2, avg score is 15 ((20+10)/2)
-- delete the first inspection
DELETE FROM Inspections WHERE RestaurantID = 1111 AND Score = 20;
SELECT RestaurantID, RestaurantName, InspectionCount, InspectionAvgScore FROM Restaurants WHERE RestaurantName like 'Tim''s%'; -- count is 1, avg score is 10 

-- uncomment to reset to starting point
-- UPDATE Restaurants SET InspectionCount = NULL, InspectionAvgScore = NULL;
-- DELETE FROM Inspections WHERE RestaurantID = 1111;


-- PRACTICE slide 21
-- create a trigger to make an entry in an audit table if a score is updated in Inspections table
-- first make an audit table to save table and attriute changed, old and new values, who 
-- made the change and when
CREATE TABLE Audit (
  AuditID INT NOT NULL AUTO_INCREMENT,
  TableName VARCHAR(45) NULL DEFAULT NULL, -- what table is changed
  RowID INT DEFAULT NULL, -- what row was changed
  Attribute VARCHAR(45) NULL DEFAULT NULL, -- what attribute was changed
  OldValue TEXT NULL DEFAULT NULL, -- store value generically as text
  NewValue TEXT NULL DEFAULT NULL, -- store value generically as text
  `User` VARCHAR(45) NULL DEFAULT NULL,
  `DateTime` DATETIME NULL DEFAULT CURRENT_TIMESTAMP, -- will auto enter the time of the change
  PRIMARY KEY (`AuditID`));
  
  -- create audit trigger on update to Inspections table to track score changes
DROP TRIGGER IF EXISTS UpdateInspectionScoreAudit;
DELIMITER $$
CREATE TRIGGER UpdateInspectionScoreAudit
	AFTER Update ON Inspections
    FOR EACH ROW
    BEGIN
		IF (OLD.Score <> NEW.Score) THEN -- only log changes to score, not other fields
			INSERT INTO Audit (TableName, RowID, Attribute, OldValue, NewValue, `User`)
			VALUES ('Inspection',New.InspectionID,'Score',CAST(OLD.Score AS CHAR(3)),CAST(NEW.Score AS CHAR(3)),USER());
        END IF; -- in real life would probably log changes to *any* field
    END$$
    
DELIMITER ;

-- check audit table is empty
SELECT * FROM Audit; -- yep, empty

-- find inspection for Morris Park Bake Shop
SELECT * FROM Inspections where RestaurantID = (SELECT RestaurantID FROM Restaurants WHERE RestaurantName like 'Morris Park Bake%');

-- InspectionID 26070 has a score = 5, change it to a 4
UPDATE Inspections SET Score = 4 WHERE InspectionID = 26070; 

-- check for entry in Audit table
SELECT * FROM Audit; -- have entry for Score OldValue 5, NewValue 4 with user and date

-- reset score back to 5
UPDATE Inspections SET Score = 5 WHERE InspectionID = 26070; 

-- confirm entry in Audit table
SELECT * FROM Audit;