INSERT INTO `ma_SettingsCategory` (`id`, `name`, `system`, `sorter`) VALUES (NULL, 'Background Screening', 'n', '110');

SET @categoryId := (SELECT id FROM ma_SettingsCategory WHERE name = 'Background Screening');

INSERT INTO `ma_Settings` (`name`, `idCategory`, `fieldType`, `label`, `value`, `options`, `description`, `required`, `system`, `sorter`, `added`, `changed`) VALUES
('backgroundScreeningFee', @categoryId, 'numeric', 'Background Screening Fee', '30', '', '', 'y', 'n', 0, NOW(), NOW()),
('backgroundScreeningPopupContent', @categoryId, 'HtmlEditor', 'Popup Content', '<p>Lorem ipsum dolor sit amet, consectetur adipiscing elit. Vivamus quis purus rhoncus, hendrerit risus et, congue enim. Donec convallis gravida odio sed condimentum. In in porttitor erat. Sed libero lorem, pharetra id ligula lobortis, laoreet tristique risus. Nunc tempus, metus in aliquam malesuada, nisi nibh fermentum tellus, non accumsan turpis lectus eu purus. Cras arcu elit, dapibus eu felis vel, dapibus posuere ex. Nam lacinia, lectus sed porttitor rutrum, justo dui cursus urna, ac facilisis dolor ex et nunc. Fusce nec dolor metus. Fusce ornare hendrerit varius. Nulla luctus at elit quis commodo. Curabitur ipsum eros, varius ac ultrices in, cursus sit amet ex.</p>\r\n\r\n<p>Donec tempor condimentum neque, et rutrum neque vestibulum eget. Curabitur volutpat neque eget fringilla consequat. Vivamus vel eros hendrerit, imperdiet felis ut, hendrerit ex. Donec sit amet elementum leo. Proin pulvinar posuere consequat. Aliquam sit amet sem quis erat feugiat volutpat. Quisque maximus eu ipsum nec rhoncus. Donec volutpat eros arcu, et pretium turpis rhoncus nec. Maecenas sollicitudin tortor et auctor rhoncus. Integer condimentum, risus ac venenatis ultrices, risus metus semper orci, id malesuada nulla leo in urna. Nullam sit amet tincidunt eros. Nam augue purus, sodales feugiat tortor vitae, consectetur facilisis erat. Aenean vitae porta ligula, vitae vestibulum tortor.</p>\r\n\r\n<p>Proin nec ipsum porta, finibus erat sit amet, fermentum nulla. Morbi maximus, sem eget pretium pharetra, arcu sem convallis turpis, cursus ultricies purus magna in leo. Donec mollis, mi ac vestibulum faucibus, purus turpis finibus libero, eu porttitor justo arcu non enim. Curabitur bibendum tempus nunc, a fringilla odio congue ac. Pellentesque maximus odio at nulla hendrerit, ac scelerisque massa viverra. In hac habitasse platea dictumst. Quisque euismod pharetra mollis. Nam placerat gravida sapien, vel condimentum nisi pulvinar quis. Cras sit amet turpis risus. Duis velit justo, dictum in condimentum vel, aliquam non turpis. Nam sed tempus libero.</p>\r\n', '', 'Content which shows in popup after the Background Questionnaire step', 'y', 'n', 1, NOW(), NOW());

INSERT INTO `ma_SettingsOptions` (`name`, `variable`, `value`, `type`, `sorter`) VALUES ('backgroundScreeningPopupContent', 'mode', 'view', 'string', NULL);

SET @template := 'bgScreeningToBgCompany';
SET @sorter := IFNULL((SELECT MAX(sorter) FROM ma_Settings WHERE idCategory = @categoryId), -1) + 1;

INSERT INTO `ma_Settings` (`name`, `idCategory`, `fieldType`, `label`, `value`, `options`, `description`, `required`, `system`, `sorter`, `added`, `changed`) VALUES
(CONCAT(@template, 'Header'), @categoryId, 'header', '', 'License Submitted (Background Screening Company Notification)', '', '', 'n', 'n', @sorter, NOW(), NOW()),
(CONCAT(@template, 'Subject'), @categoryId, 'text', 'Subject', 'New license has been submitted on Minnesota American Indian Chamber of Commerce', '', '', '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, 'To'), @categoryId, 'emails', 'To Email Address(es) &nbsp; <span class="label_description">Enter email address(es) that should receive a notification</span>', '', '', 'Use commas to separate email addresses', 'n', 'n',  @sorter + 3, NOW(), NOW()),
(CONCAT(@template, 'Body'), @categoryId, 'HtmlEditor', 'Message Body', '<p>New license has been submitted:</p>\r\n\r\n<p><strong>DBA Name</strong>: {vendorDbaName}<br />\r\n<strong>Sole Proprietor:</strong> {vendorSoleProprietorName}<br />\r\n<strong>Federal ID</strong>: {vendorFederalId}<br />\r\n<strong>Phone</strong>: {vendorPhone}<br />\r\n<strong>License #</strong>: {id}</p>\r\n\r\n<p>Click <a href="{detailsUrl}">here</a> to view details.</p>\r\n', '', 'Constants: {id} {vendorDbaName} {vendorSoleProprietorName} {vendorFederalId} {vendorPhone} {detailsUrl}', 'y', 'n', @sorter + 4, NOW(), NOW())
ON DUPLICATE KEY UPDATE
  `idCategory` = VALUES(`idCategory`),
  `label` = VALUES(`label`),
  `value` = VALUES(`value`),
  `description` = VALUES(`description`);

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

INSERT INTO `ma_DAdminAclResource` (`id`, `title`, `sorter`) VALUES (12, 'Licensing (background screening only)', '31');

ALTER TABLE `ma_License` ADD `bgScreeningReport` VARCHAR(255) NULL DEFAULT NULL AFTER `status`;
ALTER TABLE `ma_License` CHANGE `subtotal` `price` INT(11) NOT NULL;
ALTER TABLE `ma_License` CHANGE `total` `registrationFee` INT(11) NOT NULL;
ALTER TABLE `ma_License` ADD `total` INT(11) NOT NULL AFTER `registrationFee`;
UPDATE `ma_License` SET `total` = `registrationFee`;
ALTER TABLE `ma_License` ADD `backgroundScreeningFee` INT NOT NULL AFTER `registrationFee`;