/* Day 6 - Views, transactions, object-oriented data, generated fields, time variant data
Tim Pierson, Dartmouth CS61, Spring 2026
*/

use nyc_data;

-- ********************************************************************************
-- PRACTICE 1 slide 6
-- ********************************************************************************
-- create view for mapping Manhattan fruit/vegetable restaurants
-- first look at fruit/vegetable restaurants
-- NOTE: we deleted one Juicy Lucy store on Day 4, but there is another Juicy Lucy on Avenue A
SELECT * FROM Restaurants 
	WHERE CuisineDescription LIKE '%fruit%'	
    AND boro = 'Manhattan'; 
    
-- 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 RestaurantID, RestaurantName, Building, Street, 
		ZipCode, Latitude, Longitude, AVG(score) as AvgScore, DATEDIFF(CURDATE(), MAX(i.InspectionDate)) AS DaysSinceInspection
		FROM restaurants r LEFT JOIN restaurant_inspections i ON r.RestaurantID = i.CAMIS
		WHERE boro = 'Manhattan' AND 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 RestaurantID, RestaurantName, Building, Street, 
    ZipCode, Latitude, Longitude, COALESCE(AVG(score),0) AS AvgScore, DATEDIFF(CURDATE(), MAX(i.InspectionDate)) AS DaysSinceInspection
	FROM restaurants r LEFT JOIN restaurant_inspections i ON r.RestaurantID = i.CAMIS
	WHERE boro = 'Manhattan' AND CuisineDescription LIKE '%fruit%'
	GROUP BY RestaurantID; 

SELECT * from MapData;

-- export results to .csv and import into Google Maps
-- see instructions https://www.google.com/earth/outreach/learn/visualize-your-data-on-a-custom-map-using-google-my-maps/




-- SLIDE 18
-- Inheritance
DROP SCHEMA IF EXISTS DartAir;
CREATE SCHEMA DartAir;
USE DartAir;
-- create one large table

CREATE TABLE EmployeesBigTable (
	-- common attributes 
    EmployeeID INT PRIMARY KEY AUTO_INCREMENT,
    FullName VARCHAR(100) NOT NULL,
    Salary DECIMAL(10, 2),
    HireDate DATE,
    EmployeeType ENUM('Pilot', 'Mechanic', 'Accountant', 'Other') NOT NULL,
    -- Pilot attributes
    LicenseType VARCHAR(50) DEFAULT NULL,
    FlightHours INT DEFAULT 0,
    MedicalType VARCHAR(50) DEFAULT NULL,
    -- Mechanic attributes
    CertificationType VARCHAR(50) DEFAULT NULL, -- e.g., 'Airframe', 'Powerplant'
    LastSafetyTraining DATE DEFAULT NULL,
    -- Accountant attributes
    Title VARCHAR(50) DEFAULT NULL, -- e.g., 'Payroll', 'Audit'
    CPADate DATE DEFAULT NULL
); 

INSERT INTO EmployeesBigTable (FullName, Salary, HireDate, EmployeeType, LicenseType, FlightHours, MedicalType) 
VALUES ('Chuck Yeager', 100000, '1960-6-1', 'Pilot', 'ATP', '5000', 'III'); -- Pilot

INSERT INTO EmployeesBigTable (FullName, Salary, HireDate, EmployeeType, CertificationType, LastSafetyTraining) 
VALUES ('Snuffy Smith', 70000, '1992-7-15', 'Mechanic', 'A&P', '2026-01-02'); -- Mechanic

INSERT INTO EmployeesBigTable (FullName, Salary, HireDate, EmployeeType, Title, CPADate) 
VALUES ('Scrooge McDuck', '350000', '2023-5-17', 'Accountant', 'CPA Accountant', '2021-10-11'); -- Accountant

INSERT INTO EmployeesBigTable (FullName, Salary, HireDate, EmployeeType) 
VALUES ('Nobody Special', '45000', '2026-2-27', 'Other'); -- Other

SELECT * FROM EmployeesBigTable;


-- SLIDE 20
-- Base Table: Common attributes for everyone
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY AUTO_INCREMENT,
    FullName VARCHAR(100) NOT NULL,
    Salary DECIMAL(10, 2),
    HireDate DATE,
    EmployeeType ENUM('Pilot', 'Mechanic', 'Accountant', 'Other') NOT NULL DEFAULT 'Other'
);

-- SLIDE 21
-- Subclass Table: Pilots
CREATE TABLE Pilots (
    EmployeeID INT PRIMARY KEY,
    LicenseType VARCHAR(50),
    FlightHours INT DEFAULT 0,
    MedicalType VARCHAR(50),
    FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID) ON DELETE CASCADE
);

-- Subclass Table: Mechanics
CREATE TABLE Mechanics (
    EmployeeID INT PRIMARY KEY,
    CertificationType VARCHAR(50), -- e.g., 'Airframe', 'Powerplant'
    LastSafetyTraining DATE,
    FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID) ON DELETE CASCADE
);

-- Subclass Table: Accountants
CREATE TABLE Accountants (
    EmployeeID INT PRIMARY KEY,
    Title VARCHAR(50), -- e.g., 'Payroll', 'Audit'
    CPADate DATE,
    FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID) ON DELETE CASCADE
);

-- SLIDE 25
SELECT *
	FROM Employees;

SELECT e.*, p.*
	FROM Employees e JOIN Pilots p USING (EmployeeID);

SELECT e.*, m.*
	FROM Employees e JOIN Mechanics m USING (EmployeeID);
    
SELECT e.*, a.*
	FROM Employees e JOIN Accountants a USING (EmployeeID);



-- ********************************************************************************
-- PRACTICE  slide 27
-- ********************************************************************************
-- Add employee
-- insert a pilot

INSERT INTO Employees (FullName,Salary,HireDate,EmployeeType) 
VALUES ('Chuck Yeager',100000,'1960-6-1','Pilot');

INSERT INTO Pilots (EmployeeID,LicenseType,FlightHours, MedicalType)
VALUES (LAST_INSERT_ID(),"ATP",5000,"III");

-- check it worked
SELECT e.*, p.*
FROM Employees e JOIN Pilots p USING (EmployeeID);

-- insert a mechanic
INSERT INTO Employees (FullName,Salary,HireDate,EmployeeType) 
VALUES ('Snuffy Smith',70000,'1992-7-15','Mechanic');

INSERT INTO Mechanics (EmployeeID, CertificationType, LastSafetyTraining)
VALUES (LAST_INSERT_ID(),'A&P','2026-01-02');

-- check it workd
SELECT e.*, m.*
FROM Employees e JOIN Mechanics m USING (EmployeeID);

-- insert accountant
INSERT INTO Employees (FullName,Salary,HireDate,EmployeeType) 
VALUES ('Scrooge McDuck',350000,'2023-5-17','Accountant');

INSERT INTO Accountants (EmployeeID, Title, CPADate)
VALUES (LAST_INSERT_ID(),"CPA accountant",'2021-10-11');

-- check it worked
SELECT e.*, a.*
FROM Employees e JOIN Accountants a USING (EmployeeID);


-- insert other type of employee (don't need LAST_INSERT_ID())
INSERT INTO Employees (FullName,Salary,HireDate,EmployeeType) 
VALUES ('Nobody Special', 45000, '2026-2-27','Other');

-- check it worked
SELECT e.*
FROM Employees e; 


-- create a view for pilots
CREATE VIEW PilotView AS
	SELECT e.*, p.LicenseType, p.FlightHours, p.MedicalType
		FROM Employees e JOIN Pilots p USING (EmployeeID);
        
SELECT * FROM PilotView;




-- SLIDE 28
-- use JSON for specialty properties
CREATE TABLE Employees2 (
    EmployeeID INT PRIMARY KEY AUTO_INCREMENT,
    FullName VARCHAR(100) NOT NULL,
    Salary DECIMAL(10, 2),
    HireDate DATE,
    EmployeeType ENUM('Pilot', 'Mechanic', 'Accountant', 'Other') NOT NULL,
    Properties JSON DEFAULT NULL
);

-- SLIDE 29
-- insert pilot
INSERT INTO Employees2 (FullName, Salary, HireDate, EmployeeType, Properties)
VALUES ("Ace Pilot", 110000, '2026-4-11', 'Pilot','{"LicenseType":"ATP", "FlightHours":2222, "MedicalType":"II"}');
-- insert mechanic
INSERT INTO Employees2 (FullName, Salary, HireDate, EmployeeType, Properties)
VALUES("Joe Mechanic", 77000, '2026-4-2','Mechanic','{"CertificationType":"A&P", "LastSafetyTraining":"2026-4-2"}');
-- insert accountant
INSERT INTO Employees2 (FullName, Salary, HireDate, EmployeeType, Properties)
VALUES("Alice Accountant", 123000, '2026-4-5','Accountant','{"Title":"CPA Accountant", "CPADate":"2026-4-5"}');
-- insert other
INSERT INTO Employees2 (FullName, Salary, HireDate, EmployeeType)
VALUES("Random Dude", 33000, '2026-4-6','Other');

-- check that it worked, should see JSON attribute called Properties
SELECT * FROM Employees2;

-- SLIDE 30
-- get pilots
SELECT FullName, Salary, HireDate, EmployeeType, 
	JSON_EXTRACT(Properties, '$.FlightHours') AS FlightHours,
    JSON_EXTRACT(Properties, '$.LicenseType') AS LicenseType,
    JSON_UNQUOTE(JSON_EXTRACT(Properties, '$.MedicalType')) AS MedicalType, -- remove quotes
	JSON_EXTRACT(Properties, '$.NonExistentField') AS NonExistentField -- set to NULL
    FROM Employees2
    WHERE EmployeeType = 'Pilot';



-- GENERATED COLUMNS
-- SLIDE 33
USE test_schema;
CREATE TABLE Products (
    Price DECIMAL(10,2),
    Quantity INT,
    TotalCost DECIMAL(10,2) GENERATED ALWAYS AS (price * quantity) VIRTUAL
);

-- SLIDE 34
INSERT INTO Products (Price, Quantity) VALUES (10.50, 3);

SELECT * FROM Products;
-- price: 10.50, quantity: 3, total_cost: 31.50

-- SLIDE 35
USE nyc_data;
ALTER TABLE restaurant_inspections
	ADD COLUMN InspectionYear INT GENERATED ALWAYS AS (YEAR(InspectionDate)) VIRTUAL;
    
SELECT * 
	FROM restaurant_inspections
    WHERE InspectionDate <> '1900-01-01';
    
-- SLIDE 36
USE DartAir;
ALTER TABLE Employees2
	ADD COLUMN FlightHours INT AS (JSON_UNQUOTE(Properties->'$.FlightHours')) STORED;
SELECT * FROM Employees2;



-- TIME VARIANT DATA
-- SLIDE 37
SELECT e.*, p.* 
	FROM Pilots p LEFT JOIN Employees e ON p.EmployeeID = e.EmployeeID;
    
-- Salary history lost if update Chuck's salary
UPDATE Employees 
	SET Salary = 120000 WHERE EmployeeID = 1;
SELECT e.*, p.* 
	FROM Pilots p LEFT JOIN Employees e ON p.EmployeeID = e.EmployeeID;
    
-- SLIDE 39
-- make a history table
CREATE TABLE EmployeeSalaryHistory(
	EmployeeID INT NOT NULL,
    Salary DECIMAL(10, 2),
    FromDate DATE,
    ToDate DATE DEFAULT NULL,
    PRIMARY KEY (EmployeeID, FromDate),
    CONSTRAINT fk_salary_history_employee
        FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID)
			ON DELETE RESTRICT
            ON UPDATE CASCADE
    );

-- When a new employee is hired:
-- 		Insert an entry with their starting salary
-- 		FromDate = HireDate
-- 		Leave ToDate NULL
-- here I'll fill history table with current salary and hire date
INSERT INTO EmployeeSalaryHistory (EmployeeID, Salary, FromDate, ToDate)
	SELECT EmployeeID, Salary, HireDate, NULL
		FROM Employees;

SELECT * FROM EmployeeSalaryHistory;

-- SLIDE 40
-- giving Chuck Yeager a raise requires three queries
SET @effectiveDate = '2026-4-16';
SET @employeeID = 1;
SET @newSalary = 125000;
-- update history, finish old salary
UPDATE EmployeeSalaryHistory
	SET ToDate = @effectiveDate
    WHERE EmployeeID = @employeeID
		AND ToDate IS NULL;
-- insert entry for new salary
INSERT INTO EmployeeSalaryHistory
	VALUES (@employeeID, @newSalary, @effectiveDate, NULL);
    
-- update employees
UPDATE Employees
	SET Salary = @newSalary
    WHERE EmployeeID = @employeeID;
        
SELECT * FROM Employees;
SELECT * FROM EmployeeSalaryHistory ORDER BY EmployeeID, ToDate;

-- ********************************************************************************
-- PRACTICE  slide 43
-- ********************************************************************************
USE nyc_data;

-- make history table with foreign key constraint that references Restauarnts
CREATE TABLE RestaurantGradeHistory (
	RestaurantID BIGINT NOT NULL,
    Grade VARCHAR(4),
	StartDate DATE NOT NULL,
    EndDate DATE DEFAULT NULL,
    PRIMARY KEY (RestaurantID, StartDate),
    CONSTRAINT fk_grade_history
        FOREIGN KEY (RestaurantID) REFERENCES Restaurants(RestaurantID)
			ON DELETE RESTRICT
            ON UPDATE CASCADE
    );
    
-- fill history table with most recent Grades
INSERT INTO RestaurantGradeHistory
	SELECT DISTINCT CAMIS AS RestaurantID, Grade, GradeDate AS StartDate, NULL
		FROM restaurant_inspections i1
		WHERE i1.GradeDate = (SELECT MAX(i2.GradeDate) FROM restaurant_inspections i2 WHERE i2.CAMIS = i1.CAMIS)
			-- AND i1.Grade <> ''
		ORDER BY CAMIS;
    
-- check that it worked
SELECT * FROM RestaurantGradeHistory;

-- insert new inspection for camis=30075445
-- set variables for convenience
SET @restaurantID = 30075445;
SET @inspectionDate = '2026-4-16'; 
SET @grade = 'C';
INSERT INTO restaurant_inspections (CAMIS, InspectionDate, Grade, GradeDate)
	VALUES (@restaurantID, @inspectionDate,@grade,@inspectionDate);
    
-- Update history by closing current entry
UPDATE RestaurantGradeHistory
	SET EndDate = @inspectionDate 
    WHERE RestaurantID = @restaurantID;
   
-- add new entry in history table for this most recent grade
INSERT INTO RestaurantGradeHistory 
	VALUES (@restaurantID, @grade, @inspectionDate, NULL);
    
-- check that it worked
SELECT * FROM RestaurantGradeHistory 
	WHERE RestaurantID = @restaurantID
    ORDER BY StartDate desc;

-- testing, removing new entries in restauarnt_inspections table
-- DELETE FROM restaurant_inspections WHERE CAMIS = @restaurantID and InspectionDate = @inspectionDate;


-- ********************************************************************************
-- END DAY 6
-- ********************************************************************************