CREATE OR REPLACE VIEW FriendlyResults AS
SELECT 
	R.ResultID,
    Rc.CircuitName,
    Rc.Year,
    CONCAT(D.Forename," ",D.Surname) as Driver,
    C.ConstructorName as Constructor,
    R.grid as Started,
    R.EndPosition as Ended,
    S.End_Status,
    R.points,
    R.fastestLapTime,
    R.fastestLapSpeed
FROM Results R
JOIN Races Rc on R.RaceID=Rc.RaceID
JOIN Drivers D on R.DriverID=D.DriverID
JOIN Constructors C on R.ConstructorID=C.ConstructorID
JOIN Status S on R.StatusID=S.StatusID
ORDER BY R.ResultID;

CREATE OR REPLACE VIEW LostGained AS
SELECT 
ResultID, CircuitName, Year, Driver, Started, Ended, 
(CASE WHEN Ended - Started < 0 THEN "Gained position"
WHEN Ended - STARTED = 0 THEN "Kept place"
WHEN Ended - Started > 0 THEN "Lost position" END) AS Outcome,
Started - Ended AS Place_Change,
Points
FROM FriendlyResults
ORDER BY ResultID;

DELIMITER $$
CREATE PROCEDURE DriverRank(IN YearInput CHAR(4))
BEGIN
Select CONCAT(D.Forename," ",D.Surname) as FullName, sum(Points) as Result
FROM Results R
INNER JOIN
	(SELECT RaceID FROM RACES
	WHERE Year = YearInput) Y
	ON Y.RaceID=R.RaceID
INNER JOIN 
	Drivers D on R.DriverID=D.DriverID
GROUP BY FullName
ORDER BY Result DESC;
END $$
DELIMITER ;

CALL DriverRank(2008);

# FUNCTION: checking if the driver has finished the race
# Note: Status "+ _Laps" also means that the driver finished.

DELIMITER $$
CREATE FUNCTION Finished(StatusInput VARCHAR(20))
RETURNS VARCHAR(3)
DETERMINISTIC
BEGIN
	DECLARE Finished VARCHAR(3);
	CASE 
    WHEN StatusInput = 1 THEN SET Finished = "Yes";
    WHEN StatusInput IN (SELECT StatusID FROM Status WHERE End_Status LIKE '%Lap%') THEN SET Finished = "Yes";
    ELSE SET Finished = "No";
    END CASE;
RETURN Finished;
END $$
DELIMITER ;

# How does it work?
SELECT R.ResultID, D.Forename, D.Surname, Rc.Circuitname, Rc.Date, Finished(R.StatusID) as Finished
FROM Results R
JOIN Drivers D on R.DriverID=D.DriverID
JOIN Races RC on R.RaceID=Rc.RaceID
ORDER BY R.ResultID;