INSERT INTO `pl_DPageItemType` (`id`, `type`, `title`, `sorter`) VALUES
 (NULL, 'User\\Prize\\', 'User Prize', '0'),
 (NULL, 'User\\Register\\', 'User Sign Up', '0'),
 (NULL, 'User\\ShowcaseInfo\\', 'User Showcase Info', '2'),
 (NULL, 'User\\AboutInfo\\', 'User About Me Areas', '4'),
 (NULL, 'User\\Login\\', 'User Login', '5'),
 (NULL, 'User\\Logout\\', 'User Logout', '6'),
 (NULL, 'User\\ForgotPassword\\', 'User User Forgot Password', '7'),
 (NULL, 'User\\', 'User Profile', '8'),
 (NULL, 'User\\Member\\', 'Member Profile', '9');

SELECT "Add manually the /account branch of cms pages" as "TODO";

-- --------------------------------------------------------------------------------------------------------------------
-- MembershipType
-- --------------------------------------------------------------------------------------------------------------------
CREATE TABLE `pl_MembershipType` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `title` varchar(255) NOT NULL,
 `price` decimal(11,2) NOT NULL,
 `sorter` int(11) NOT NULL,
 `added` datetime NOT NULL,
 `changed` datetime NOT NULL,
 PRIMARY KEY (`id`),
 KEY `sorter` (`sorter`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;


INSERT INTO `pl_MembershipType` (`id`, `title`, `price`, `sorter`, `added`, `changed`) VALUES
(1, 'Associate', '100.00', 1, '2018-06-07 00:00:00', '2018-06-07 00:00:00'),
(2, 'VIP', '500.00', 2, '2018-06-07 00:00:00', '2018-06-07 00:00:00');

-- --------------------------------------------------------------------------------------------------------------------
-- pl_UserSecurityQuestion
-- --------------------------------------------------------------------------------------------------------------------

CREATE TABLE `pl_UserSecurityQuestion` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `title` varchar(255) NOT NULL,
 `sorter` int(11) NOT NULL,
 `added` datetime NOT NULL,
 `changed` datetime NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

INSERT INTO `pl_UserSecurityQuestion` (`id`, `title`, `sorter`, `added`, `changed`) VALUES
(1, 'What is the first name of the person you first kissed?', 1, '2018-05-31 18:11:16', '2018-05-31 18:11:16'),
(2, 'What is the last name of the teacher who gave you your first failing grade?', 2, '2018-05-31 18:11:16', '2018-05-31 18:11:16'),
(3, 'What was your favorite food as a child?', 3, '2018-05-31 18:11:16', '2018-05-31 18:11:16'),
(4, 'What was your favorite place to visit as a child?', 4, '2018-05-31 18:11:16', '2018-05-31 18:11:16'),
(5, 'What street did you grow up on?', 5, '2018-05-31 18:11:16', '2018-05-31 18:11:16');

-- --------------------------------------------------------------------------------------------------------------------
-- pl_User. Use "SHOW CREATE TABLE pl_User" query to update this patch
-- --------------------------------------------------------------------------------------------------------------------
DROP TABLE `pl_User`;

CREATE TABLE `pl_User` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `bought` enum('y') DEFAULT NULL,
 `firstName` varchar(255) NOT NULL,
 `lastName` varchar(255) NOT NULL,
 `email` varchar(128) NOT NULL,
 `phone` varchar(255) NOT NULL,
 `birthdate` date NOT NULL,
 `membershipTypeId` int(11) unsigned NOT NULL,
 `alias` varchar(255) NOT NULL,
 `metaTitle` varchar(255) NOT NULL DEFAULT '',
 `metaKeywords` text NOT NULL,
 `metaDescription` text NOT NULL,
 `photoH` varchar(255) DEFAULT NULL,
 `photoV` varchar(255) DEFAULT NULL,
 `video` varchar(255) DEFAULT NULL,
 `subtitle` varchar(255) DEFAULT NULL,
 `introduction` text,
 `aboutBlock1` text,
 `aboutBlock2` text,
 `aboutBlock3` text,
 `login` varchar(64) NOT NULL,
 `password` varchar(255) NOT NULL,
 `securityQuestionId` int(10) unsigned DEFAULT NULL,
 `securityAnswer` varchar(255) NOT NULL,
 `salt` char(8) NOT NULL,
 `autologinCode` char(32) DEFAULT NULL,
 `recoverCode` char(32) DEFAULT NULL,
 `recoverCodeExpirationDate` datetime DEFAULT NULL,
 `status` char(8) DEFAULT NULL,
 `transactionId` varchar(19) DEFAULT NULL,
 `added` datetime NOT NULL,
 `changed` datetime NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `login-bought` (`login`,`bought`),
 UNIQUE KEY `email-bought` (`email`,`bought`),
 UNIQUE KEY `recoverCode` (`recoverCode`),
 KEY `autologinCode` (`autologinCode`),
 KEY `status` (`status`),
 KEY `secutityQuestionId` (`securityQuestionId`),
 KEY `membershipTypeId` (`membershipTypeId`),
 CONSTRAINT `pl_User_ibfk_3` FOREIGN KEY (`membershipTypeId`) REFERENCES `pl_MembershipType` (`id`) ON UPDATE CASCADE,
 CONSTRAINT `pl_User_ibfk_1` FOREIGN KEY (`status`) REFERENCES `pl_UserStatus` (`id`) ON UPDATE CASCADE,
 CONSTRAINT `pl_User_ibfk_2` FOREIGN KEY (`securityQuestionId`) REFERENCES `pl_UserSecurityQuestion` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=91 DEFAULT CHARSET=utf8;

CREATE TABLE `pl_UserAddress` (
 `userId` int(11) unsigned NOT NULL,
 `type` enum('contact','billing','shipping') NOT NULL,
 `firstName` varchar(255) DEFAULT NULL,
 `lastName` varchar(255) DEFAULT NULL,
 `address` varchar(255) NOT NULL,
 `address2` varchar(255) DEFAULT NULL,
 `city` varchar(255) NOT NULL,
 `state` char(2) NOT NULL,
 `zip` varchar(16) NOT NULL,
 PRIMARY KEY (`userId`,`type`),
 CONSTRAINT `pl_UserAddress_ibfk_1` FOREIGN KEY (`userId`) REFERENCES `pl_User` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `pl_UserPrize` (
 `userId` int(10) unsigned NOT NULL,
 `shirtSize` varchar(255) NOT NULL,
 `shoeSize` varchar(255) NOT NULL,
 `dressSize` varchar(255) NOT NULL,
 `nailColor` varchar(255) NOT NULL,
 `added` datetime NOT NULL,
 `changed` datetime NOT NULL,
 PRIMARY KEY (`userId`),
 CONSTRAINT `pl_UserPrize_ibfk_1` FOREIGN KEY (`userId`) REFERENCES `pl_User` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ---------------------------------------------------------------------------------------------------------------------
-- Settings. Member Account
-- ---------------------------------------------------------------------------------------------------------------------
INSERT INTO `pl_SettingsCategory` (`id`, `name`, `system`, `sorter`) VALUES ('3', 'Member Account', 'n', '2');

INSERT INTO `pl_Settings` (`name`, `idCategory`, `fieldType`, `label`, `value`, `options`, `description`, `required`, `system`, `sorter`, `added`, `changed`) VALUES
('minAgeDialogHeader', 3, 'header', 'Dialog to show if registrant is under 13', '', '', '', 'y', 'n', 0, '2018-06-05 09:17:27', '2018-06-08 12:43:20'),
('minAgeDialogTitle', 3, 'text', 'Dialog Title', 'Registration is not allowed', '', '', 'y', 'n', 1, '2018-06-05 09:17:27', '2018-06-08 12:54:56'),
('minAgeDialogContent', 3, 'htmlEditor', 'Dialog Content', '<p>Lorem ipsum dolor sit amet, consectetur adipiscing elit. Fusce a sapien eu purus consectetur viverra. Proin eu tortor et est sodales posuere. Donec feugiat vel mi eu convallis. Morbi aliquam, sem eu fermentum mattis, urna neque semper justo, in maximus tellus libero in lorem. Nam auctor auctor felis, sit amet hendrerit odio accumsan sed. Duis vulputate metus vitae nulla interdum commodo. Fusce blandit tristique varius. Vestibulum gravida, diam ut posuere molestie, lacus odio feugiat metus, in commodo dolor est ac odio. Ut euismod at lectus id porta. Maecenas a dolor tincidunt, hendrerit diam in, rutrum metus. Maecenas sodales diam ut efficitur maximus. Sed leo lacus, cursus tempus ante nec, hendrerit tincidunt justo. Praesent dui mi, semper ac elit a, hendrerit interdum est. Donec massa velit, aliquet nec lectus vitae, porttitor faucibus ex.</p>\r\n\r\n<p>Curabitur semper ligula ac lorem iaculis luctus. Fusce a iaculis massa. Sed hendrerit nulla non consequat dictum. Duis vel pretium erat. Vestibulum congue egestas magna at efficitur. Vivamus quis tellus id ligula imperdiet ultricies. Nulla venenatis nibh nec fermentum sodales.</p>\r\n\r\n<p>Nulla eu tortor vitae sapien elementum lacinia convallis vitae ligula. Vestibulum ante ipsum primis in faucibus orci luctus et ultrices posuere cubilia Curae; Integer ante augue, rutrum eu sagittis ac, pulvinar nec ipsum. Sed elementum enim purus, a luctus erat laoreet a. Suspendisse aliquam fermentum pretium. Vivamus egestas facilisis nisi vitae hendrerit. Nulla at turpis arcu. Aenean aliquet lacinia nulla, at vulputate metus. Nam ac porttitor quam, non cursus orci.</p>\r\n', '', '', 'y', 'n', 2, '2018-06-05 09:17:27', '2018-06-08 13:45:24'),
('welcomeVideoHeader', 3, 'header', 'Welcome Video', '', '', '', 'n', 'n', 3, '2018-06-08 10:42:18', '2018-06-08 12:43:20'),
('welcomeYoutubeVideoUrl', 3, 'text', 'Youtube Video URL', '', '', 'If this field is left blank then the MP4 File below (if any) will be used instead.', 'n', 'n', 4, '2018-06-08 12:42:02', '2018-06-08 12:54:56'),
('welcomeVideoFile', 3, 'media', 'MP4 File', '', '', 'File extension must be .mp4', 'n', 'n', 5, '2018-06-08 10:48:31', '2018-06-08 12:54:56'),
('welcomeVideoThumbnail', 3, 'ExtendedImage', 'Thumbnail', '', '', '', 'n', 'n', 6, '2018-06-08 10:52:59', '2018-06-08 12:54:56'),
('welcomeVideoMessage', 3, 'HtmlEditor', 'Message to show after the video is done', '<p>Now you can either proceed in filling out the information in next steps or do that at another time.</p>\r\n', '', '', 'y', 'n', 241, '2018-06-08 14:08:19', '2018-06-08 14:10:38');

INSERT INTO `pl_SettingsOptions` (`name`, `variable`, `value`, `type`, `sorter`) VALUES
('welcomeImage', 'resize', '1280x720cc', 'string', NULL),
('welcomeVideoThumbnail', 'resize', '1280x720', 'string', NULL),
('welcomeYoutubeVideoUrl', 'validators[0]', 'YoutubeUrl', 'string', NULL),
('minAgeDialogContent', 'mode', 'view', 'string', NULL),
('welcomeVideoMessage', 'mode', 'view', 'string', NULL);

-- ---------------------------------------------------------------------------------------------------------------------
INSERT INTO `pl_DPageHandler` (`name`, `title`, `sorter`) VALUES
('user', 'User Page', 1),
('userLogin', 'User Login Page', 2);

INSERT INTO `pl_UserStatus` (`id`, `title`, `sorter`) VALUES
('active', 'Active', 0),
('inactive', 'Inactive', 1);
-- ---------------------------------------------------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `pl_UserSocialLink` (
  `userId` int(11) unsigned NOT NULL,
  `linkId` int(11) NOT NULL,
  `link` varchar(255) NOT NULL,
  `added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `pl_UserSocialLink`
 ADD UNIQUE KEY `idUserLink` (`userId`,`linkId`), ADD KEY `linkId` (`linkId`);

ALTER TABLE `pl_UserSocialLink`
ADD CONSTRAINT `pl_UserSocialLink_ibfk_1` FOREIGN KEY (`linkId`) REFERENCES `pl_SocialLinkDictionary` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `pl_UserSocialLink_ibfk_2` FOREIGN KEY (`userId`) REFERENCES `pl_User` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
-- ---------------------------------------------------------------------------------------------------------------------

INSERT INTO `pl_SettingsCategory` (`id`, `name`, `system`, `sorter`) VALUES (4, 'Account Email Templates', 'n', 3);

INSERT INTO `pl_Settings` (`name`, `idCategory`, `fieldType`, `label`, `value`, `options`, `description`, `required`, `system`, `sorter`, `added`, `changed`) VALUES
('forgotPasswordEmailBody', 4, 'HtmlEditor', 'Message Body', '<div style="font-family: Arial,Helvetica,sans-serif;">\r\n<p>You or someone on your behalf has requested to reset your password on Adapta Interactive Framework website.</p>\r\n\r\n<p>Email Address: <strong>{email}</strong><br />\r\nActivation Expiration Date: <strong>{recoverCodeExpirationDate}</strong></p>\r\n\r\n<p>Click <a href="{link}" target="_blank">here</a> to set your new password.<br />\r\nтрям</p>\r\n</div>\r\n', '', 'Constants: {firstName} {lastName} {email} {recoverCodeExpirationDate} {link}', 'y', 'n', 130, '2010-09-13 15:54:08', '2017-05-30 15:09:19'),
('forgotPasswordEmailFrom', 4, 'text', 'From Email Address &nbsp; <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', 120, '2010-09-13 15:54:08', '2017-05-30 15:09:19'),
('forgotPasswordEmailHeader', 4, 'header', '', '"Forgot Password" (User Notification)', '', '', 'n', 'n', 90, '2010-09-13 15:54:08', '2013-05-14 10:07:31'),
('forgotPasswordEmailSubject', 4, 'text', 'Subject', 'Recovery account password at Adapta Interactive Framework', '', '', 'y', 'n', 100, '2010-09-13 15:54:08', '2017-05-30 15:09:19');

INSERT INTO `pl_SettingsOptions` (`id`, `name`, `variable`, `value`, `type`, `sorter`) VALUES
(NULL, 'forgotPasswordEmailBody', 'scriptOptions[enterMode]', '2', 'string', NULL),
(NULL, 'forgotPasswordEmailBody', 'height', '200', 'int', NULL),
(NULL, 'forgotPasswordEmailBody', 'mode', 'view', 'string', NULL);

-- ---------------------------------------------------------------------------------------------------------------------
-- User Registration Email Templates
-- ---------------------------------------------------------------------------------------------------------------------
SET @template := 'userRegisteredToUser';
SET @categoryId := 4;
SET @sorter := IFNULL((SELECT MAX(sorter) FROM pl_Settings WHERE idCategory = @categoryId), -1) + 1;

INSERT INTO `pl_Settings` (`name`, `idCategory`, `fieldType`, `label`, `value`, `options`, `description`, `required`, `system`, `sorter`, `added`, `changed`) VALUES
(CONCAT(@template, 'Header'), @categoryId, 'header', '', 'User Registration (User Notification)', '', '', 'n', 'n', @sorter, NOW(), NOW()),
(CONCAT(@template, 'Subject'), @categoryId, 'text', 'Subject', 'You have created an account at Pageant Life', '', '', 'y', 'n', @sorter + 1, NOW(), NOW()),
(CONCAT(@template, 'From'), @categoryId, 'text', 'From Email Address &nbsp; <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', @sorter + 2, NOW(), NOW()),
(CONCAT(@template, 'Body'), @categoryId, 'HtmlEditor', 'Message Body', '<div><p>Congratulations! You have been successfully registered at Pageant Life web site!</p><p>Your Email: <strong>{email}</strong><br />Your Password: <strong>{password}</strong></p><p><a href="{loginUrl}" target="_blank">Click here to login at Pageant Life web site</a>.</p></div>', '', 'Constants: {firstName} {lastName} {phone} {email} {password} {membership} {loginUrl}', 'y', 'n', @sorter + 3, NOW(), NOW());

INSERT INTO `pl_SettingsOptions` (`name`, `variable`, `value`, `type`, `sorter`) VALUES (CONCAT(@template, 'Body'), 'mode', 'view', 'string', NULL);
-- ---------------------------------------------------------------------------------------------------------------------
SET @template := 'userRegisteredToAdmin';
SET @categoryId := 4;
SET @sorter := IFNULL((SELECT MAX(sorter) FROM pl_Settings WHERE idCategory = @categoryId), -1) + 1;

INSERT INTO `pl_Settings` (`name`, `idCategory`, `fieldType`, `label`, `value`, `options`, `description`, `required`, `system`, `sorter`, `added`, `changed`) VALUES
(CONCAT(@template, 'Header'), @categoryId, 'header', '', 'User Registration (Admin Notification)', '', '', 'n', 'n', @sorter, NOW(), NOW()),
(CONCAT(@template, 'Subject'), @categoryId, 'text', 'Subject', 'New User has been registered at Pageant Life', '', '', 'y', 'n', @sorter + 1, NOW(), NOW()),
(CONCAT(@template, 'From'), @categoryId, 'text', 'From Email Address &nbsp; <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', @sorter + 2, NOW(), NOW()),
(CONCAT(@template, 'Body'), @categoryId, 'HtmlEditor', 'Message Body', '<p>New User has been registered at Pageant Life website.</p><p>Name: <strong>{firstName}</strong> <strong>{lastName}</strong><br />Email: <strong><a href="mailto:{email}">{email}</a></strong><br />Phone Number:<strong> {phone}</strong><br /><br /><a href="{detailsUrl}" target="_blank">Click here to view details</a>.</p>', '', 'Constants: {firstName} {lastName} {phone} {email} {membership} {detailsUrl}', 'y', 'n', @sorter + 3, NOW(), NOW());

INSERT INTO `pl_SettingsOptions` (`name`, `variable`, `value`, `type`, `sorter`) VALUES (CONCAT(@template, 'Body'), 'mode', 'view', 'string', NULL);

-- ---------------------------------------------------------------------------------------------------------------------