-- -----------------------------------------------------------------------------
-- function updateProductratingAndReviewCount qs_Product
-- -----------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS updateProductRatingAndReviewCount ;

DELIMITER $$$

CREATE PROCEDURE  updateProductRatingAndReviewCount(_id int unsigned)
not deterministic modifies sql data
BEGIN
    SET @reviewsCount := 0;
    SET @ratingSum := 0;

SELECT COUNT(*), IFNULL(SUM(`rating`), 0) INTO @reviewsCount, @ratingSum
    FROM `qs_ProductReview`
    WHERE `productId` = _id AND `statusId` = 'approved';

IF (@reviewsCount > 0)  THEN
    SET @rating := @ratingSum / @reviewsCount;
    UPDATE `qs_Product` SET `rating` = @rating, `reviewsCount` = @reviewsCount WHERE `id` = _id;
ELSE
    UPDATE `qs_Product` SET `rating` = 0, `reviewsCount` = 0 WHERE `id` = _id;
END IF;

END $$$
DELIMITER ;

-- -----------------------------------------------------------------------------
-- triggers

DELIMITER $$$
-- -----------------------------------------------------------------------------

-- -----------------------------------------------------------------------------
-- qs_ProductReview
-- -----------------------------------------------------------------------------

DROP TRIGGER IF EXISTS `ProductReview_au` $$$
CREATE TRIGGER `ProductReview_au` AFTER UPDATE
ON `qs_ProductReview` FOR EACH ROW
begin
    IF NEW.statusId != OLD.statusId OR NEW.rating != OLD.rating THEN
        CALL updateProductRatingAndReviewCount(NEW.productId);
    END IF;
end $$$

DROP TRIGGER IF EXISTS `ProductReview_ai` $$$
CREATE TRIGGER `ProductReview_ai` AFTER INSERT
ON `qs_ProductReview` FOR EACH ROW
begin
    IF 'approved' = NEW.statusId  THEN
        CALL updateProductRatingAndReviewCount(NEW.productId);
    END IF;

end $$$

DROP TRIGGER IF EXISTS `ProductReview_ad` $$$
CREATE TRIGGER `ProductReview_ad` AFTER DELETE
ON `qs_ProductReview` FOR EACH ROW
begin
    IF 'approved' = OLD.statusId  THEN
        CALL updateProductRatingAndReviewCount(OLD.productId);
    END IF;
end $$$

-- -----------------------------------------------------------------------------
DELIMITER ;

-- end triggers
-- -----------------------------------------------------------------------------


-- -----------------------------------------------------------------------------
-- function updateViewControllerLogLastActivityId qs_ViewControllerLogSession
-- -----------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS updateViewControllerLogLastActivityId ;

DELIMITER $$$

CREATE PROCEDURE  updateViewControllerLogLastActivityId(_sessionId char(64))
not deterministic modifies sql data
BEGIN
    SET @lastActivityId := 0;

SELECT MAX(`id`) INTO @lastActivityId
    FROM `qs_ViewControllerLog`
    WHERE `sessionId` = _sessionId
    GROUP BY `sessionId`;

IF (@lastActivityId > 0) THEN
    UPDATE `qs_ViewControllerLogSession` SET `lastActivityId` = @lastActivityId WHERE `id` = _sessionId;
ELSE
    UPDATE `qs_ViewControllerLogSession` SET `lastActivityId` = NULL WHERE `id` = _sessionId;
END IF;

END $$$
DELIMITER ;

-- -----------------------------------------------------------------------------
-- triggers

DELIMITER $$$
-- -----------------------------------------------------------------------------

-- -----------------------------------------------------------------------------
-- qs_ViewControllerLog
-- -----------------------------------------------------------------------------

DROP TRIGGER IF EXISTS `ViewControllerLogSession_ai` $$$
CREATE TRIGGER `ViewControllerLogSession_ai` AFTER INSERT
ON `qs_ViewControllerLog` FOR EACH ROW
begin
    CALL updateViewControllerLogLastActivityId(NEW.sessionId);
end $$$

DROP TRIGGER IF EXISTS `ViewControllerLogSession_ad` $$$
CREATE TRIGGER `ViewControllerLogSession_ad` AFTER DELETE
ON `qs_ViewControllerLog` FOR EACH ROW
begin
    CALL updateViewControllerLogLastActivityId(OLD.sessionId);
end $$$

-- -----------------------------------------------------------------------------
DELIMITER ;

-- end triggers
-- -----------------------------------------------------------------------------