/* Day 7 - Advanced SQL - Stored procedures/functions, transactions, triggers
Tim Pierson, Dartmouth CS61, Spring 2026
*/

use nyc_data;

-- SLIDE 4
SET @restaurantID = 1111;
SELECT @restaurantID;

SELECT RestaurantName, Boro INTO @restaurantName, @boro
	FROM Restaurants
    WHERE RestaurantID = @restaurantID;

SELECT @restaurantName, @boro;

-- SLIDE 7
SELECT * FROM Restaurants WHERE boro = 'Manhattan';

-- SLIDE 8
DROP PROCEDURE IF EXISTS GetManhattanRestaurants;

DELIMITER $$
CREATE PROCEDURE GetManhattanRestaurants()
	BEGIN
		SELECT * FROM Restaurants WHERE boro = 'Manhattan';
	END$$
    DELIMITER ;
    
    -- call stored procedure
    CALL GetManhattanRestaurants();
    
-- SLIDE 11
DROP PROCEDURE IF EXISTS GetRestaurantsByBoro;

DELIMITER $$
CREATE PROCEDURE GetRestaurantsByBoro(
	IN BoroName VARCHAR(20),
    OUT RestaurantCount INT)
BEGIN
	SELECT * FROM Restaurants WHERE boro = BoroName;
    SELECT count(*) INTO RestaurantCount
		FROM Restaurants WHERE boro = BoroName;
END$$
DELIMITER ;

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


-- SLIDE 13
DROP PROCEDURE IF EXISTS RestaurantInspectionCategory;
DELIMITER $$
CREATE PROCEDURE RestaurantInspectionCategory (
	IN RestaurantID INT,
    OUT Classification VARCHAR(20)
)
BEGIN
	DECLARE inspectionAvg INT;
    
	SELECT AVG(Score) INTO inspectionAvg
		FROM restaurant_inspections
        WHERE CAMIS = RestaurantID;
	IF inspectionAvg IS NULL THEN
		SET Classification = 'Uknown';
    ELSEIF inspectionAvg < 30 THEN
		SET Classification = 'Good';
	ELSEIF inspectionAvg > 100 THEN
		SET Classification = 'Sketchy';
	ELSE
		SET Classification = 'Ok';
	END IF;
END $$

DELIMITER ;

-- test
CALL RestaurantInspectionCategory(1111, @class); -- Tim's Tasty Treat's (no inspections)
SELECT @class; -- Unknown

-- try Morris Park Bake Shop, first get RestaurantID into @id
SELECT RestaurantID INTO @id 
	FROM Restaurants
	WHERE RestaurantName LIKE '%Morris Park Bake%';
SELECT @id; -- check we got a good id
SELECT AVG(Score) FROM restaurant_inspections WHERE CAMIS = @id; -- Avg = 19.6842
CALL RestaurantInspectionCategory(@id, @class); --  test stored procedure 
SELECT @class;
        


-- ********************************************************************************
-- PRACTICE SLIDE 15
-- ********************************************************************************
-- 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 restaurant_inspections WHERE CAMIS = RestaurantIDParam;
    SELECT MAX(Score) INTO MaxScore FROM restaurant_inspections WHERE CAMIS = RestaurantIDParam;
    SELECT AVG(Score) INTO AvgScore FROM restaurant_inspections WHERE CAMIS = RestaurantIDParam;
    SELECT count(*) INTO CountScores FROM restaurant_inspections WHERE CAMIS = RestaurantIDParam;
    
    -- instead could write
    /*
    SELECT MIN(Score), MAX(Score), AVG(Score), Count(*) 
		INTO MinScore, MaxScore, AvgScore, CountScores
		FROM restaurant_inspections WHERE CAMIS = RestaurantIDParam;
    */
END $$
DELIMITER ;

-- test with Morris Park Bake Shop located at 1007 Morris Park Avenue    
SELECT MIN(score), MAX(score), AVG(score), count(score) 
	FROM restaurant_inspections i JOIN Restaurants r  ON i.CAMIS = r.RestaurantID
	WHERE RestaurantName LIKE 'Morris Park Bake%'
	ORDER BY Score; -- Min Score 10, Max Score 38, Avg score 19.6842, count 19
    
SELECT RestaurantID FROM Restaurants WHERE RestaurantName LIKE 'Morris%' AND Building = 1007 AND Street = 'Morris Park Avenue' LIMIT 1;

CALL InspectionSummary((SELECT RestaurantID FROM Restaurants WHERE RestaurantName LIKE 'Morris%' AND Building = 1007 AND Street = 'Morris Park Avenue' LIMIT 1),
	@MinScore, @MaxScore, @AvgScore, @CountScores);
SELECT @MinScore, @MaxScore, @AvgScore, @CountScores; -- Min 10, Max 38, Avg 19.68, count 19

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


-- SLIDE 16
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 18
-- ********************************************************************************
DROP FUNCTION IF EXISTS RestaurantInspectionFrequency;
DELIMITER $$
CREATE FUNCTION RestaurantInspectionFrequency (
    InspectionCount INT
) 
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
	-- DECLARE level VARCHAR(20);
	IF InspectionCount < 7 THEN
		RETURN 'Low';
	ELSEIF InspectionCount > 12 THEN
		RETURN 'High';
	ELSE
		RETURN 'Intermediate';
	END IF;
END $$
DELIMITER ;

-- error, don't CALL functions, use them in SELECT
-- CALL RestaurantInspectionFrequency(5);

-- use the function in a SELECT
SELECT RestaurantName, COUNT(*) AS Count, RestaurantInspectionFrequency(COUNT(*)) AS Classification 
	FROM Restaurants r JOIN restaurant_inspections i ON r.RestaurantID = i.CAMIS 
	GROUP BY r.RestaurantID; 
    
-- ********************************************************************************
-- Transactions
-- ********************************************************************************
-- SLIDE 21. show an update can be rolled back
SELECT * FROM Restaurants WHERE RestaurantID = 1111; -- Tim's Tasty Treats

START TRANSACTION;
	UPDATE Restaurants
		SET RestaurantName = 'Tim''s UNtasty Treats'
        WHERE RestaurantID = 1111;
ROLLBACK;

SELECT * FROM Restaurants WHERE RestaurantID = 1111;


-- SLIDE 22
USE test_schema;
CREATE TABLE Accounts (
	AccountID INT PRIMARY KEY,
    Balance DECIMAL(10,2),  CHECK(Balance >= 0)
    );
    
INSERT INTO Accounts VALUES (1, 1000), (2, 2000);
SELECT * FROM Accounts;

-- SLIDE 24
UPDATE Accounts
	SET Balance = Balance - 500
	WHERE AccountID=1;

-- SLIDE 25
UPDATE Accounts
	SET Balance = Balance + 500
	WHERE AccountID=2;
    
-- SLIDE 27
START TRANSACTION;
	UPDATE Accounts
	SET Balance = Balance - 500
	WHERE AccountID=1;

	UPDATE Accounts
	SET Balance = Balance + 500
	WHERE AccountID=2;

COMMIT; -- commit both commands if successful
SELECT * FROM Accounts;

-- SLIDE 29
DROP PROCEDURE IF EXISTS TransferMoney;
DELIMITER $$
CREATE PROCEDURE TransferMoney()
BEGIN
    -- 1. Declare the "Catch" logic first
    DECLARE EXIT HANDLER FOR SQLEXCEPTION 
    BEGIN
        -- This block runs ONLY if an error occurs
        ROLLBACK; -- rollback on any error
        -- Optional: Log the error or send a custom message
    END;

    -- 2. The "Try" block: your actual logic
    START TRANSACTION;
    UPDATE Accounts -- Update 1
	SET Balance = Balance - 500
	WHERE AccountID=1;

	UPDATE Accounts -- Update 2
	SET Balance = Balance + 500
	WHERE AcountID=1;
    COMMIT; -- commit if both updates are successful
END $$
DELIMITER ;

-- test stored procedure, but first add some money back to account 1
UPDATE Accounts -- Update 1
	SET Balance = Balance + 500
	WHERE AccountID=1;
SELECT * FROM Accounts;

CALL TransferMoney();
SELECT * FROM Accounts;

-- ********************************************************************************
-- PRACTICE SLIDE 30
-- ********************************************************************************
USE nyc_data;
DROP PROCEDURE IF EXISTS NewRestaurantInspection;
DELIMITER $$
CREATE PROCEDURE NewRestaurantInspection(
	IN restaurantID INT,
    IN restaurantName VARCHAR(100),
    IN inspectionDate DATE,
    IN score INT)
BEGIN
    -- 1. Declare the "Catch" logic first
    DECLARE EXIT HANDLER FOR SQLEXCEPTION 
    BEGIN
        -- This block runs ONLY if an error occurs
        ROLLBACK; -- rollback on any error
        -- Optional: Log the error or send a custom message
    END;

    -- 2. The "Try" block: your actual logic
    START TRANSACTION;
    INSERT INTO Restaurants (RestaurantID, RestaurantName)
		VALUES (restaurantID, restaurantName);
        
	INSERT INTO restaurant_inspections (CAMIS, InspectionDate, Score)
		VALUES (restaurantID, inspectionDate, score);
        
    COMMIT; -- commit if both updates are successful
END $$
DELIMITER ;

-- call stored procedure
CALL NewRestaurantInspection(1, 'Test', CURDATE(),5);
SELECT * FROM Restaurants WHERE RestaurantID = 1; -- new restaurant appears
SELECT * FROM restaurant_inspections WHERE CAMIS = 1; -- note, we still have the old `inspection date` field which is not filled here

    
    

-- ********************************************************************************
-- Triggers
-- ********************************************************************************
-- SLIDE 35
-- add InspectionAvg and InspectionCount to Restaurants
ALTER TABLE Restaurants ADD COLUMN InspectionAvg FLOAT;
ALTER TABLE Restaurants ADD COLUMN InspectionCount FLOAT;

-- 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, InspectionAvg 
	FROM Restaurants; -- InspectionCount and InspectionAvgScores are all NULLs

-- SLIDE 36
-- can update counts and average score for all restaurants with the following command
UPDATE Restaurants r 
	SET InspectionCount = (SELECT COUNT(Score) FROM restaurant_inspections i WHERE i.CAMIS = r.RestaurantID),
		InspectionAvg = (SELECT AVG(Score) FROM restaurant_inspections i WHERE i.CAMIS = r.RestaurantID);

-- check that update worked
SELECT RestaurantID, RestaurantName, InspectionCount, InspectionAvg 
	FROM Restaurants; -- InspectionCount and InspectionAvg are filled, Morris Park Bake Shop count 19, avg 19.6842 as seen before


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

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

-- SLIDE 39
-- create trigger on update to restaurant_inspection table to keep count and avg score updated
DROP TRIGGER IF EXISTS UpdateInspectionStatsOnUpdate;
DELIMITER $$
CREATE TRIGGER UpdateInspectionStatsOnUpdate
	AFTER UPDATE ON restaurant_inspections
    FOR EACH ROW
    BEGIN
		UPDATE Restaurants SET InspectionAvg = (SELECT IF (AVG(score) >0,AVG(score),NULL) FROM restaurant_inspections i WHERE i.CAMIS = NEW.CAMIS) 
			WHERE RestaurantID = NEW.CAMIS;
		-- no need to change count on UPDATE
    END$$
    
DELIMITER ;

-- SLIDE 40
-- DELETE FROM restaurant_inspections WHERE CAMIS = 1111; -- reset in case old inspections in the table
-- UPDATE Restaurants SET InspectionCount = 0, InspectionAvg = NULL WHERE RestaurantID = 1111;
SELECT RestaurantID, RestaurantName, InspectionCount, InspectionAvg FROM Restaurants; -- InspectionCount and Avg are NULL for Tim's Tasty Treats ID = 1111
INSERT INTO restaurant_inspections (CAMIS, Score) VALUES (1111,15); -- add inspection score for Tim's Tasty Treats
SELECT RestaurantID, RestaurantName, InspectionCount, InspectionAvg FROM Restaurants WHERE RestaurantName like 'Tim''s%'; -- count is 1, avg score is 15

-- SLIDE 41
-- update inspection score for Tim's Tasty Treats
UPDATE restaurant_inspections SET Score = 20 WHERE CAMIS = 1111; -- update score to 20
SELECT RestaurantID, RestaurantName, InspectionCount, InspectionAvg FROM Restaurants WHERE RestaurantName like 'Tim''s%'; -- count is 1, avg score is 20

-- SLIDE 42
-- insert another inspection score for Tim's
INSERT INTO restaurant_inspections (CAMIS, Score) VALUES (1111,10); -- add inspection score for Tim's Tasty Treats
SELECT RestaurantID, RestaurantName, InspectionCount, InspectionAvg FROM Restaurants WHERE RestaurantName like 'Tim''s%'; -- count is 2, avg score is 15 ((20+10)/2)

-- SLIDE 43
-- delete the first inspection
DELETE FROM restaurant_inspections WHERE CAMIS = 1111 AND Score = 20;
SELECT RestaurantID, RestaurantName, InspectionCount, InspectionAvg 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, InspectionAvg = NULL;
DELETE FROM restaurant_inspections WHERE CAMIS = 1111;
SELECT RestaurantID, RestaurantName, InspectionCount, InspectionAvg FROM Restaurants WHERE RestaurantName like 'Tim''s%'; -- count is null, avg score is null


-- ********************************************************************************
-- PRACTICE SLIDE 44
-- ********************************************************************************
-- 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
  CAMIS BIGINT DEFAULT NULL, -- what row was changed
  InspectionDate DATE NULL,
  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 restaurant_inspections
    FOR EACH ROW
    BEGIN
		IF (OLD.Score <> NEW.Score) THEN -- only log changes to score, not other fields
			INSERT INTO Audit (TableName, CAMIS, InspectionDate, Attribute, OldValue, NewValue, `User`)
			VALUES ('restaurant_inspections',New.CAMIS,OLD.InspectionDate,'Score',OLD.Score,NEW.Score,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
-- get ID
SELECT RestaurantID INTO @rID FROM Restaurants WHERE RestaurantName like 'Morris Park Bake%';
SELECT @rID;

SELECT RestaurantID, RestaurantName, i.* 
	FROM Restaurants r JOIN restaurant_inspections i ON r.RestaurantID = i.CAMIS
	WHERE RestaurantID = @rID 
		AND i.InspectionDate = '2023-01-31';

-- Inspection on 2023-01-31 has a score = 21, change it to a 4
UPDATE restaurant_inspections 
	SET Score = 4 
    WHERE CAMIS = @rID AND InspectionDate = '2023-01-31'; 

-- 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 21
UPDATE restaurant_inspections 
	SET Score = 21 
    WHERE CAMIS = @rID AND InspectionDate = '2023-01-31'; 

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


-- ********************************************************************************
-- END DAY 7
-- ********************************************************************************

