UPDATE `sa_zz_SettingsCategory` SET `name` = 'Contact Form' WHERE `sa_zz_SettingsCategory`.`id` = 2;
UPDATE `sa_zz_SettingsCategory` SET `name` = 'Account' WHERE `sa_zz_SettingsCategory`.`id` = 4;

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

INSERT INTO `sa_zz_SettingsCategory` (`id`, `name`, `system`, `sorter`) VALUES (8, 'Applications Portal', 'n', '7');

INSERT INTO `sa_zz_Settings` (`name`, `idCategory`, `fieldType`, `label`, `value`, `options`, `description`, `required`, `system`, `sorter`, `added`, `changed`) VALUES
('applicationUserRegistrationEmailHeader', 8, 'header', '', 'Applicant Password Notification Email Template', '', '', 'n', 'n', 10, NOW(), NOW()),
('applicationUserRegistrationEmailFrom', 8, 'text', 'From Email Address', '', '', '', 'y', 'n', 20, NOW(), NOW()),
('applicationUserRegistrationEmailSubject', 8, 'text', 'Subject', '', '', '', 'y', 'n', 30, NOW(), NOW()),
('applicationUserRegistrationEmailBody', 8, 'HtmlEditor', 'Message Body', '', '', 'Constants: {schoolName} {name} {position} {phone} {email} {password} {link}', 'y', 'n', 40, NOW(), NOW());

INSERT INTO `sa_zz_SettingsOptions` (`id`, `name`, `variable`, `value`, `type`) VALUES
(NULL, 'applicationUserRegistrationEmailBody', 'scriptOptions[Config][EnterMode]', 'br', 'string'),
(NULL, 'applicationUserRegistrationEmailBody', 'height', '200', 'int'),
(NULL, 'applicationUserRegistrationEmailBody', 'mode', 'view', 'string'),
(NULL, 'applicationUserRegistrationEmailBody', 'toolbarSet', 'Basic', 'string');

INSERT INTO `sa_zz_Settings` (`name`, `idCategory`, `fieldType`, `label`, `value`, `options`, `description`, `required`, `system`, `sorter`, `added`, `changed`) VALUES
('appUserForgotPasswordEmailHeader', 8, 'header', '', 'Applcant \"Forgot Password\" Email Template', '', '', 'n', 'n', 90, '2010-09-13 15:54:08', '2010-09-13 15:54:08'),
('appUserForgotPasswordEmailSubject', 8, 'text', 'Subject', 'Recovery account password at samnllc.org', '', '', 'y', 'n', 100, '2010-09-13 15:54:08', '2021-01-27 12:26:57'),
('appUserForgotPasswordEmailFrom', 8, 'text', 'From Email Address', 'Friends of Education <info@samnllc.org>', '', '', 'y', 'n', 120, '2010-09-13 15:54:08', '2021-01-27 12:26:57'),
('appUserForgotPasswordEmailBody', 8, 'HtmlEditor', 'Message Body', '<div style=\"font-family: Arial,Helvetica,sans-serif\">\r\n<p>Here is the new password your requested to login to the Authorized School Login section of www.samnllc.org. To activate it, please click below.</p>\r\n\r\n<p>Email Address: <strong>{email}</strong><br />\r\nNew Password: <strong>{newPassword}</strong><br />\r\nExpiration date for activation: <strong>{recoverCodeExpirationDate}</strong></p>\r\n\r\n<p>To activate your new password click <a href=\"{link}\" target=\"_blank\">here</a>..</p>\r\n</div>\r\n', '', 'Constants: {name} {email} {newPassword} {recoverCodeExpirationDate} {link}', 'y', 'n', 130, '2010-09-13 15:54:08', '2021-01-27 12:26:57');

INSERT INTO `sa_zz_SettingsOptions` (`id`, `name`, `variable`, `value`, `type`) VALUES
(NULL, 'appUserForgotPasswordEmailBody', 'scriptOptions[Config][EnterMode]', 'br', 'string'),
(NULL, 'appUserForgotPasswordEmailBody', 'height', '200', 'int'),
(NULL, 'appUserForgotPasswordEmailBody', 'mode', 'view', 'string'),
(NULL, 'appUserForgotPasswordEmailBody', 'toolbarSet', 'Basic', 'string');

INSERT INTO `sa_zz_DPageHandler` (`name`, `title`, `sorter`) VALUES
('applicant', 'Applicant Page', 4),
('applicantLogin', 'Applicant Login Page', 5);

INSERT INTO `sa_zz_DPageItemType` (`id`, `type`, `title`, `sorter`) VALUES
(NULL, 'Application_User_Login_', 'Applicant Login Form', 500),
(NULL, 'Application_User_Logout_', 'Applicant Logout', 510),
(NULL, 'Application_User_ForgotPassword_', 'Applicant \"Forgot Password\" Form', 520);

INSERT INTO `sa_zz_DPageItemType` (`id`, `type`, `title`, `sorter`)
VALUES (NULL, 'Application_Document_', 'Application Documents', 530);

UPDATE `sa_zz_Settings` set sorter = sorter + 50 WHERE idCategory = 8;

INSERT INTO `sa_zz_Settings` (`name`, `idCategory`, `fieldType`, `label`, `value`, `options`, `description`, `required`, `system`, `sorter`, `added`, `changed`) VALUES
('applicationDeadlineDate', 8, 'date', 'Application Deadline Date', '2021-09-30', '', '', 'y', 'n', 20, NOW(), NOW()),
('applicationEnableApplicants', 8, 'checkbox', 'Allow Applicants Longin', 'y', '', '', 'n', 'n', 30, NOW(), NOW()),
('applicationEnableReviewers', 8, 'checkbox', 'Allow Reviewers Longin', 'y', '', '', 'n', 'n', 40, NOW(), NOW());

INSERT INTO `sa_zz_SettingsOptions` (`id`, `name`, `variable`, `value`, `type`) VALUES
(NULL, 'applicationEnableApplicants', 'decoration', 'simple', 'string'),
(NULL, 'applicationEnableReviewers', 'decoration', 'simple', 'string');

INSERT INTO `sa_zz_Settings` (`name`, `idCategory`, `fieldType`, `label`, `value`, `options`, `description`, `required`, `system`, `sorter`, `added`, `changed`) VALUES
('applicationDeadlineNotice', 8, 'HtmlEditor', 'Application Deadline Notice', '', '', 'Constants: {deadlineDate}', 'n', 'n', 25, NOW(), NOW());

INSERT INTO `sa_zz_SettingsOptions` (`id`, `name`, `variable`, `value`, `type`) VALUES
(NULL, 'applicationDeadlineNotice', 'scriptOptions[Config][EnterMode]', 'br', 'string'),
(NULL, 'applicationDeadlineNotice', 'height', '200', 'int'),
(NULL, 'applicationDeadlineNotice', 'mode', 'view', 'string'),
(NULL, 'applicationDeadlineNotice', 'toolbarSet', 'Basic', 'string');

INSERT INTO `sa_zz_DPageItemType` (`id`, `type`, `title`, `sorter`) VALUES
(NULL, 'Application_DeadlineNotice_', 'Application Deadline Notice', 540);

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

INSERT INTO `sa_zz_Settings` (`name`, `idCategory`, `fieldType`, `label`, `value`, `options`, `description`, `required`, `system`, `sorter`, `added`, `changed`) VALUES
('applicationReviewerRegistrationEmailHeader', 8, 'header', '', 'Reviewer Password Notification Email Template', '', '', 'n', 'n', 600, NOW(), NOW()),
('applicationReviewerRegistrationEmailFrom', 8, 'text', 'From Email Address', '', '', '', 'y', 'n', 610, NOW(), NOW()),
('applicationReviewerRegistrationEmailSubject', 8, 'text', 'Subject', '', '', '', 'y', 'n', 620, NOW(), NOW()),
('applicationReviewerRegistrationEmailBody', 8, 'HtmlEditor', 'Message Body', '', '', 'Constants: {name} {phone} {email} {password} {link}', 'y', 'n', 630, NOW(), NOW());

INSERT INTO `sa_zz_SettingsOptions` (`id`, `name`, `variable`, `value`, `type`) VALUES
(NULL, 'applicationReviewerRegistrationEmailBody', 'scriptOptions[Config][EnterMode]', 'br', 'string'),
(NULL, 'applicationReviewerRegistrationEmailBody', 'height', '200', 'int'),
(NULL, 'applicationReviewerRegistrationEmailBody', 'mode', 'view', 'string'),
(NULL, 'applicationReviewerRegistrationEmailBody', 'toolbarSet', 'Basic', 'string');

INSERT INTO `sa_zz_Settings` (`name`, `idCategory`, `fieldType`, `label`, `value`, `options`, `description`, `required`, `system`, `sorter`, `added`, `changed`) VALUES
('appReviewerForgotPasswordEmailHeader', 8, 'header', '', 'Reviewer \"Forgot Password\" Email Template', '', '', 'n', 'n', 700, '2010-09-13 15:54:08', '2010-09-13 15:54:08'),
('appReviewerForgotPasswordEmailSubject', 8, 'text', 'Subject', 'Recovery account password at samnllc.org', '', '', 'y', 'n', 710, '2010-09-13 15:54:08', '2021-01-27 12:26:57'),
('appReviewerForgotPasswordEmailFrom', 8, 'text', 'From Email Address', 'Friends of Education <info@samnllc.org>', '', '', 'y', 'n', 720, '2010-09-13 15:54:08', '2021-01-27 12:26:57'),
('appReviewerForgotPasswordEmailBody', 8, 'HtmlEditor', 'Message Body', '<div style=\"font-family: Arial,Helvetica,sans-serif\">\r\n<p>Here is the new password your requested to login to the Authorized School Login section of www.samnllc.org. To activate it, please click below.</p>\r\n\r\n<p>Email Address: <strong>{email}</strong><br />\r\nNew Password: <strong>{newPassword}</strong><br />\r\nExpiration date for activation: <strong>{recoverCodeExpirationDate}</strong></p>\r\n\r\n<p>To activate your new password click <a href=\"{link}\" target=\"_blank\">here</a>..</p>\r\n</div>\r\n', '', 'Constants: {name} {email} {newPassword} {recoverCodeExpirationDate} {link}', 'y', 'n', 730, '2010-09-13 15:54:08', '2021-01-27 12:26:57');

INSERT INTO `sa_zz_SettingsOptions` (`id`, `name`, `variable`, `value`, `type`) VALUES
(NULL, 'appReviewerForgotPasswordEmailBody', 'scriptOptions[Config][EnterMode]', 'br', 'string'),
(NULL, 'appReviewerForgotPasswordEmailBody', 'height', '200', 'int'),
(NULL, 'appReviewerForgotPasswordEmailBody', 'mode', 'view', 'string'),
(NULL, 'appReviewerForgotPasswordEmailBody', 'toolbarSet', 'Basic', 'string');

INSERT INTO `sa_zz_DPageHandler` (`name`, `title`, `sorter`) VALUES
('reviewer', 'Reviewer Page', 6),
('reviewerLogin', 'Reviewer Login Page', 7);

INSERT INTO `sa_zz_DPageItemType` (`id`, `type`, `title`, `sorter`) VALUES
(NULL, 'Application_Reviewer_Login_', 'Reviewer Login Form', 600),
(NULL, 'Application_Reviewer_Logout_', 'Reviewer Logout', 610),
(NULL, 'Application_Reviewer_ForgotPassword_', 'Reviewer \"Forgot Password\" Form', 620);

INSERT INTO `sa_zz_DPageItemType` (`id`, `type`, `title`, `sorter`) VALUES
(NULL, 'Application_User_', 'Applicants', 700);

INSERT INTO `sa_zz_Settings` (`name`, `idCategory`, `fieldType`, `label`, `value`, `options`, `description`, `required`, `system`, `sorter`, `added`, `changed`)
VALUES ('applicationGeneral', '8', 'header', '', 'General', '', '', 'n', 'n', '10', NOW(), NOW());

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

update `sa_zz_Settings` set `label` = 'Allow Applicants Logging in' where `name` = 'applicationEnableApplicants';
update `sa_zz_Settings` set `label` = 'Allow Reviewers Logging in' where `name` = 'applicationEnableReviewers';
UPDATE `sa_zz_Settings` SET `label` = 'Application Deadline Date (automatically disallow applicants to login after this date)' WHERE `name` = 'applicationDeadlineDate';

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

CREATE TABLE `sa_ApplicationDocument` (
    `id` int(11) UNSIGNED NOT NULL,
    `documentTypeId` int(11) UNSIGNED NOT NULL,
    `applicationUserId` int(11) UNSIGNED NOT NULL,
    `file` varchar(255) NOT NULL,
    `reviewed` enum('y','n') NOT NULL DEFAULT 'n',
    `reviewDate` datetime DEFAULT NULL,
    `added` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
    `changed` datetime NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `sa_ApplicationDocument`
    ADD PRIMARY KEY (`id`),
    ADD UNIQUE KEY `documentTypeId_2` (`documentTypeId`,`applicationUserId`),
    ADD KEY `documentTypeId` (`documentTypeId`),
    ADD KEY `applicationUserId` (`applicationUserId`),
    ADD KEY `reviewed` (`reviewed`);

ALTER TABLE `sa_ApplicationDocument`
    MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `sa_ApplicationDocument`
    ADD CONSTRAINT `sa_ApplicationDocument_ibfk_1` FOREIGN KEY (`applicationUserId`) REFERENCES `sa_ApplicationUser` (`id`) ON UPDATE CASCADE,
    ADD CONSTRAINT `sa_ApplicationDocument_ibfk_2` FOREIGN KEY (`documentTypeId`) REFERENCES `sa_ApplicationDocumentType` (`id`) ON UPDATE CASCADE;

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

CREATE TABLE `sa_ApplicationDocumentType` (
    `id` int(11) UNSIGNED NOT NULL,
    `title` varchar(255) NOT NULL,
    `description` text NOT NULL,
    `sorter` int(11) NOT NULL,
    `added` datetime NOT NULL,
    `changed` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

ALTER TABLE `sa_ApplicationDocumentType`
    MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT;

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

CREATE TABLE `sa_ApplicationReviewer` (
    `id` int(11) UNSIGNED NOT NULL,
    `name` varchar(255) NOT NULL DEFAULT '',
    `position` varchar(255) NOT NULL DEFAULT '',
    `phone` varchar(255) NOT NULL DEFAULT '',
    `email` varchar(128) NOT NULL DEFAULT '',
    `password` varchar(255) NOT NULL DEFAULT '',
    `salt` varchar(8) NOT NULL DEFAULT '',
    `autologinCode` varchar(32) DEFAULT NULL,
    `newPassword` varchar(32) DEFAULT NULL,
    `newSalt` varchar(8) DEFAULT NULL,
    `recoverCode` varchar(32) DEFAULT NULL,
    `recoverCodeExpirationDate` datetime DEFAULT NULL,
    `added` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
    `changed` datetime NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `sa_ApplicationReviewer`
    ADD PRIMARY KEY (`id`),
    ADD UNIQUE KEY `email` (`email`),
    ADD UNIQUE KEY `recoverCode` (`recoverCode`),
    ADD KEY `autologinCode` (`autologinCode`);

ALTER TABLE `sa_ApplicationReviewer`
    MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT;

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

CREATE TABLE `sa_ApplicationReviewer2User` (
    `reviewerId` int(11) UNSIGNED NOT NULL,
    `userId` int(11) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `sa_ApplicationReviewer2User`
    ADD PRIMARY KEY (`reviewerId`,`userId`),
    ADD KEY `userId` (`userId`);

ALTER TABLE `sa_ApplicationReviewer2User`
    ADD CONSTRAINT `sa_ApplicationReviewer2User_ibfk_1` FOREIGN KEY (`reviewerId`) REFERENCES `sa_ApplicationReviewer` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    ADD CONSTRAINT `sa_ApplicationReviewer2User_ibfk_2` FOREIGN KEY (`userId`) REFERENCES `sa_ApplicationUser` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

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

CREATE TABLE `sa_ApplicationUser` (
    `id` int(11) UNSIGNED NOT NULL,
    `schoolName` varchar(255) NOT NULL,
    `name` varchar(255) NOT NULL DEFAULT '',
    `position` varchar(255) NOT NULL DEFAULT '',
    `phone` varchar(255) NOT NULL DEFAULT '',
    `email` varchar(128) NOT NULL DEFAULT '',
    `password` varchar(255) NOT NULL DEFAULT '',
    `salt` varchar(8) NOT NULL DEFAULT '',
    `autologinCode` varchar(32) DEFAULT NULL,
    `newPassword` varchar(32) DEFAULT NULL,
    `newSalt` varchar(8) DEFAULT NULL,
    `recoverCode` varchar(32) DEFAULT NULL,
    `recoverCodeExpirationDate` datetime DEFAULT NULL,
    `added` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
    `changed` datetime NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `sa_ApplicationUser`
    ADD PRIMARY KEY (`id`),
    ADD UNIQUE KEY `email` (`email`),
    ADD UNIQUE KEY `recoverCode` (`recoverCode`),
    ADD KEY `autologinCode` (`autologinCode`);

ALTER TABLE `sa_ApplicationUser`
    MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT;

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