DROP DATABASE IF EXISTS Bug20084;
CREATE DATABASE Bug20084;
USE Bug20084;
CREATE TABLE IF NOT EXISTS `Bug20084`.`Person` (
        id INT AUTO_INCREMENT PRIMARY KEY NOT NULL ,
        FirstName VARCHAR(50) NOT NULL ,
        LastName VARCHAR(50) NOT NULL ,
        Phone VARCHAR(25) NULL ,
        Email VARCHAR(129) NULL) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS `Bug20084`.`Ratings` (
        RecipeID INT NOT NULL ,
        PersonID INT NOT NULL ,
        Rating INT NOT NULL ,
        Date DATETIME NOT NULL 
) ENGINE=InnoDB;

 CREATE  INDEX Index1 ON `Bug20084`.`Ratings` (RecipeID, PersonID) ;

ALTER TABLE `Bug20084`.`Ratings` ADD 
        CONSTRAINT `FK_Ratings_Person` FOREIGN KEY 
        (
                PersonID
        ) REFERENCES `Bug20084`.`Person` (
                id
        );
--
USE Bug20084;
INSERT INTO Person
(id, FirstName, LastName, Phone, Email)
VALUES 
(1, 'Diana', 'Johnson', '(902) xxx-xxxx', 'xxx@xxx.xxx'),
(2, 'Mark', 'Johnson', '(403) xxx-xxxx', 'mrj001@shaw.ca'),
(3, 'Stephanie', 'Johnson', '(403) xxx-xxxx', 'xxx@xxxxl.xxx'),
(4, 'Ken', 'DeleteMe', '555-1212', 'nobody@nowhere.com'),
(5, 'Jane', 'Doe', NULL, NULL),
(6, 'Susan', 'Muise', NULL, NULL),
(7, 'Barbie', 'Doll', NULL, NULL),
(8, 'Melodee', 'Richards', NULL, NULL),
(9, 'Shauna', 'Welch', NULL, NULL),
(10, 'Violi', 'J', NULL, NULL),
(11, 'n', 'n', NULL, NULL);

INSERT INTO Ratings
(RecipeID, PersonID, Rating, Date)
VALUES 
(8, 2, 5, '0000-00-00 00:00:00'),
(7, 2, 3, '2002-04-28 00:00:00'),
(8, 4, 3, '0000-00-00 00:00:00');
--
DELIMITER $$

CREATE PROCEDURE `mj_spListRatingsOfRecipe`(
RecipeID INT
)
BEGIN

SELECT r.Rating, r.`Date`, p.FirstName, p.LastName
FROM Ratings r, Person p
WHERE r.RecipeID = RecipeID AND r.PersonID = p.id
ORDER BY r.Rating DESC, p.LastName, p.FirstName;

END$$

DELIMITER ;