INSERT INTO `ma_DPageItemType` (`id`, `type`, `title`, `sorter`) VALUES (NULL, 'LicensePremium\\', 'License Premium', '0');

CREATE TABLE `ma_Payment` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `userId` int(10) unsigned DEFAULT NULL,
 `transactionId` varchar(255) NOT NULL,
 `description` varchar(255) NOT NULL,
 `total` int(11) NOT NULL,
 `paymentMethod` enum('cc','check') NOT NULL,
 `ccNumber` varchar(19) DEFAULT NULL,
 `checkType` enum('business','personal','businessSaving','personalSaving') DEFAULT NULL,
 `accountName` varchar(255) DEFAULT NULL,
 `routingNumber` varchar(255) DEFAULT NULL,
 `bank` varchar(255) DEFAULT NULL,
 `accountNumber` varchar(255) DEFAULT NULL,
 `ip` varchar(15) NOT NULL,
 `added` datetime NOT NULL,
 `changed` datetime NOT NULL,
 PRIMARY KEY (`id`),
 KEY `transactionId` (`transactionId`),
 CONSTRAINT `ma_Payment_ibfk_1` FOREIGN KEY (`userId`) REFERENCES `ma_User` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


  
ALTER TABLE `ma_LicenseTribe` ADD `surcharge` decimal(10,2) NULL DEFAULT NULL AFTER `status`;
ALTER TABLE `ma_LicenseTribe` ADD `surchargePaid` datetime NULL DEFAULT NULL AFTER `surcharge`;
  
ALTER TABLE `ma_LicenseTribe` ADD `paymentId` INT UNSIGNED NULL DEFAULT NULL AFTER `surchargePaid`;
ALTER TABLE `ma_LicenseTribe` ADD INDEX(`paymentId`);
ALTER TABLE `ma_LicenseTribe` ADD FOREIGN KEY (`paymentId`) REFERENCES `ma_Payment`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

ALTER TABLE `ma_LicenseTribe` ADD `surchargeExpiredOn` DATE NULL DEFAULT NULL AFTER `surchargePaid`;

UPDATE `ma_SettingsCategory` SET sorter=sorter*10;

-- --------------------------------------------------------------------------------------------------------------------
-- Email Templates
-- --------------------------------------------------------------------------------------------------------------------
INSERT INTO `ma_SettingsCategory` (`id`, `name`, `system`, `sorter`) VALUES (11, 'Premium', 'n', '75');
-- DELETE FROM `ma_Settings` WHERE idCategory = 11;

SET @categoryId := 11;
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
('licenseSurchargeUserHeader', @categoryId, 'header', '', 'License Premium Notification (Vendor Users Notification)', '', '', 'n', 'n', @sorter+1, '2019-02-28 00:00:00', '2019-02-28 00:00:00'),
('licenseSurchargeUserSubject', @categoryId, 'text', 'Subject', 'License #{id} Premium ("{tribe}" tribe)', '', '', 'y', 'n', @sorter+2, '2019-02-28 00:00:00', '2019-02-28 00:00:00'),
('licenseSurchargeUserFrom', @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+3, '2019-02-28 00:00:00', '2019-02-28 00:00:00'),
('licenseSurchargeUserBody', @categoryId, 'HtmlEditor', 'Message Body', '<p>License #{id} Premium is waiting for payment:</p>\r\n\r\n<p><strong>Tribe</strong>: {tribe}<br/\r\n><strong>License #</strong>: {id}<br />\r\n<strong>Premium Amount</strong>: ${surcharge}</p><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}</p>\r\n\r\n<p><a href="{paymentUrl}">Go to payment page</a>&nbsp;</p>\r\n', '', 'Constants: {id} {vendorDbaName} {vendorSoleProprietorName} {vendorFederalId} {vendorPhone} {detailsUrl} {surcharge} {paymentUrl} {tribe}', 'y', 'n', @sorter+4, '2019-02-28 00:00:00', '2019-02-28 00:00:00')
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 ('licenseSurchargeUserBody', 'mode', 'view', 'string', NULL);

SET @template := 'licensePremiumPaidToUser';
SET @categoryId := 11;
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', '', 'New Premium Payment (Vendor Users Notification)', '', '', 'n', 'n', @sorter, NOW(), NOW()),
(CONCAT(@template, 'Subject'), @categoryId, 'text', 'Subject', 'License #{id} is upgraded to Premium ("{tribe}" tribe)', '', '', '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>License #{id} is upgraded to Premium:</p>\r\n\r\n<p><strong>Tribe</strong>: {tribe}<br/>\r\n<strong>License #</strong>: {id}<br/>\r\n<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}</p>\r\n\r\n<p><a href="{detailsUrl}">View License Details</a> | <a href="{receiptUrl}">Download Receipt</a></p>\r\n', '', 'Constants: {id} {tribe} {vendorDbaName} {vendorSoleProprietorName} {vendorFederalId} {vendorPhone} {detailsUrl} {receiptUrl}', 'y', 'n', @sorter + 3, 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);

SET @template := 'licensePremiumPaidToAdmin';
SET @categoryId := 11;
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', '', 'New Premium Payment (Admin Notification)', '', '', 'n', 'n', @sorter, NOW(), NOW()),
(CONCAT(@template, 'Subject'), @categoryId, 'text', 'Subject', 'License #{id} is upgraded to Premium ("{tribe}" tribe)', '', '', '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>License #{id} is upgraded to Premium:</p>\r\n\r\n<p><strong>Tribe</strong>: {tribe}<br/>\r\n<strong>License #</strong>: {id}<br/>\r\n<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}</p>\r\n\r\n<p>Click <a href="{detailsUrl}">here</a> to view details.</p>\r\n', '', 'Constants: {id} {tribe} {vendorDbaName} {vendorSoleProprietorName} {vendorFederalId} {vendorPhone} {detailsUrl}', 'y', 'n', @sorter + 3, 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);

SET @template := 'licensePremiumExpiresOnToUser';
SET @categoryId := 11;
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 Premium Expires on xx/xx/xx (Vendor Users Notification)', '', '', 'n', 'n', @sorter, NOW(), NOW()),
(CONCAT(@template, 'Subject'), @categoryId, 'text', 'Subject', 'License #{id} Premium expires on {expiredOn} ("{tribe}" tribe)', '', '', '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><a href="{licensePdfUrl}">License #{id}</a> Premium expires for <strong>{tribe}</strong> tribe on {expiredOn}.</p><p>It can be renewed&nbsp;in the <a href="{licenseSectionUrl}" >Licensing</a> section of the website.</p>', '', 'Constants: {id} {tribe} {vendorDbaName} {vendorSoleProprietorName} {vendorFederalId} {vendorPhone} {licensePdfUrl} {licenseSectionUrl}', 'y', 'n', @sorter + 3, 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);

SET @template := 'licensePremiumExpiredToUser';
SET @categoryId := 11;
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 Premium is Expired (Vendor Users Notification)', '', '', 'n', 'n', @sorter, NOW(), NOW()),
(CONCAT(@template, 'Subject'), @categoryId, 'text', 'Subject', 'License #{id} Premium is expired ("{tribe}" tribe)', '', '', '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><a href="{licensePdfUrl}">License #{id}</a> Premium expired for <strong>{tribe}</strong> tribe.</p><p>It can be renewed&nbsp;in the <a href="{licenseSectionUrl}" >Licensing</a> section of the website.</p>', '', 'Constants: {id} {tribe} {vendorDbaName} {vendorSoleProprietorName} {vendorFederalId} {vendorPhone} {licensePdfUrl} {licenseSectionUrl}', 'y', 'n', @sorter + 3, 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);

SET @template := 'licensePremiumRenewedToAdmin';
SET @categoryId := 11;
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 is Renewed (Admin Notification)', '', '', 'n', 'n', @sorter, NOW(), NOW()),
(CONCAT(@template, 'Subject'), @categoryId, 'text', 'Subject', 'License #{id} Premium is renewed by {expiredOn} ("{tribe}" tribe)', '', '', '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><a href="{licensePdfUrl}">License #{id}</a> is renewed by {expiredOn}.</p><p><a href="{detailsUrl}">View license details</a></p>', '', 'Constants: {id} {tribe} {expiredOn} {vendorDbaName} {vendorSoleProprietorName} {vendorFederalId} {vendorPhone} {licensePdfUrl} {detailsUrl}', 'y', 'n', @sorter + 3, 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);

SET @template := 'licensePremiumRenewedToUser';
SET @categoryId := 11;
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 Premium is Renewed (Vendor Users Notification)', '', '', 'n', 'n', @sorter, NOW(), NOW()),
(CONCAT(@template, 'Subject'), @categoryId, 'text', 'Subject', 'License #{id} Premium is renewed by {expiredOn} ("{tribe}" tribe)', '', '', '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><a href="{licensePdfUrl}">License #{id}</a> is renewed by {expiredOn}.</p><p><a href="{detailsUrl}">View license details</a></p>', '', 'Constants: {id} {tribe} {expiredOn} {vendorDbaName} {vendorSoleProprietorName} {vendorFederalId} {vendorPhone} {licensePdfUrl} {detailsUrl}', 'y', 'n', @sorter + 3, 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);

-- --------------------------------------------------------------------------------------------------------------------
-- Email Templates End
-- --------------------------------------------------------------------------------------------------------------------

ALTER TABLE `ma_LicenseTribe` CHANGE `status` `status` ENUM('pending','approved','declined','approved_pending' ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT 'pending';

ALTER TABLE `ma_Tribe` ADD `useSurcharge` ENUM('n','y') NOT NULL DEFAULT 'n' AFTER `signatureTitle`;
ALTER TABLE `ma_Tribe` ADD `merchantId` VARCHAR(255) NULL DEFAULT NULL AFTER `useSurcharge`;

ALTER TABLE `ma_Tribe` CHANGE `logo` `logo` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;