CREATE TABLE `qs_ProductReviewStatus` (
  `id` varchar(16) NOT NULL,
  `title` varchar(255) NOT NULL,
  `sorter` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `qs_ProductReviewStatus` (`id`, `title`, `sorter`) VALUES
('approved', 'Approved', 2),
('disapproved', 'Disapproved', 3),
('pending', 'Pending', 1);

ALTER TABLE `qs_ProductReviewStatus` ADD PRIMARY KEY (`id`);

CREATE TABLE IF NOT EXISTS `qs_ProductReview` (
  `id` int(11) unsigned NOT NULL,
  `statusId` varchar(16) DEFAULT NULL,
  `productId` int(11) DEFAULT NULL,
  `userId` int(11) unsigned DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `message` mediumtext NOT NULL,
  `rating` tinyint(4) NOT NULL,
  `featured` enum('y','n') NOT NULL DEFAULT 'n',
  `added` datetime NOT NULL,
  `changed` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

ALTER TABLE `qs_ProductReview` ADD PRIMARY KEY (`id`), ADD KEY `statusId` (`statusId`), ADD KEY `productId` (`productId`), ADD KEY `userId` (`userId`);
ALTER TABLE `qs_ProductReview` MODIFY `id` int(11) unsigned NOT NULL AUTO_INCREMENT;

ALTER TABLE `qs_ProductReview` CHANGE `statusId` `statusId` VARCHAR(16) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'pending';

ALTER TABLE `qs_ProductReview`
ADD CONSTRAINT `qs_ProductReview_ibfk_1` FOREIGN KEY (`statusId`) REFERENCES `qs_ProductReviewStatus` (`id`) ON DELETE NO ACTION ON UPDATE CASCADE,
ADD CONSTRAINT `qs_ProductReview_ibfk_2` FOREIGN KEY (`productId`) REFERENCES `qs_Product` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `qs_ProductReview_ibfk_3` FOREIGN KEY (`userId`) REFERENCES `qs_User` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;

INSERT INTO `qs_Settings` (`name`, `idCategory`, `fieldType`, `label`, `value`, `options`, `description`, `required`, `system`, `sorter`, `added`, `changed`) VALUES
('reviewFormEmailHeader', 2, 'header', '', 'New Product Review Added (Admin Notification)', '', '', 'n', 'n', 300, NOW(), NOW()),
('reviewFormEmailSubject', 2, 'text', 'Subject', 'New Review has been submitted', '', '', 'y', 'n', 310, NOW(), NOW()),
('reviewFormEmailFrom', 2, 'text', 'From Email Address <span class="label_description">If you leave this field empty - the From Email Address will be the same as the one in General Settings.</span>', '', '', '', 'n', 'n', 320, NOW(), NOW()),
('reviewFormEmailTo', 2, 'emails', 'To Email Address(es)<span class="label_description">Enter email address(es) that should receive a notification email regarding Product Review form submissions on your website</span>', '', '', '', 'n', 'n', 330, NOW(), NOW()),
('reviewFormEmailBody', 2, 'htmlEditor', 'MessageBody', '<p>New Product Review has been added (Product: <strong>{productTitle}</strong>).</p>\r\n\r\n<p>Click <a href="{link}">here</a> to view this one.</p>\r\n\r\n<p>Name: <strong>{name}</strong></p>\r\n\r\n<p>Email: <strong>{email}</strong></p>\r\n\r\n<p>Message: <strong>{message}</strong></p>\r\n', '', 'Constants: {productTitle} {rating} {name} {email} {message} {link}', 'y', 'n', 340, NOW(), NOW());

insert into `qs_SettingsOptions` values 
(null, 'reviewFormEmailBody', 'scriptOptions[enterMode]', '2', 'string', null),
(null, 'reviewFormEmailBody', 'height', '200', 'int', null),
(null, 'reviewFormEmailBody', 'mode', 'view', 'string', null);

ALTER TABLE `qs_Product` ADD `rating` DECIMAL(2,1) NOT NULL AFTER `rawDescription`, ADD `reviewsCount` INT UNSIGNED NOT NULL AFTER `rating`;