ALTER TABLE `ma_LicenseTribe` 
	ADD `approvedOn` DATE NULL DEFAULT NULL AFTER `paymentId`,
	ADD `expiredOn` DATE NULL DEFAULT NULL AFTER `approvedOn`;
	
ALTER TABLE `ma_LicenseTribe` CHANGE `status` `status` ENUM('pending','approved','declined','approved_pending','expired') CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT 'pending';

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

INSERT INTO `ma_Settings` (`name`, `idCategory`, `fieldType`, `label`, `value`, `options`, `description`, `required`, `system`, `sorter`, `added`, `changed`) VALUES
('licenseNeedRenewHeader', 7, 'header', '', 'License need Membership Renew (Vendor Users Notification)', '', '', 'n', 'n', 500, '2019-06-12 14:00:00', '2019-06-12 14:00:00'),
('licenseNeedRenewSubject', 7, 'text', 'Subject', 'License #{id} need Membership Renew', '', '', 'y', 'n', 510, '2019-06-12 14:00:00', '2019-06-12 14:00:00'),
('licenseNeedRenewFrom', 7, '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', 520, '2019-06-12 14:00:00', '2019-06-12 14:00:00'),
('licenseNeedRenewBody', 7, 'HtmlEditor', 'Message Body', '<p>License #{id} can\'t be Renewed/Approved because your membership is expired.</p>\r\n\r\n<p><span style="font-size: 15px;">It can renewed&nbsp;in the </span><a href="{licenseSectionUrl}" style="font-size: 15px;">Licensing</a><span style="font-size: 15px;"> section of the website.</span></p>\r\n', '', 'Constants: {id} {vendorDbaName} {vendorSoleProprietorName} {vendorFederalId} {vendorPhone} {licenseSectionUrl}', 'y', 'n', 530, '2017-04-07 14:54:38', '2019-06-12 14:00:00');


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

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

INSERT INTO `ma_Settings` (`name`, `idCategory`, `fieldType`, `label`, `value`, `options`, `description`, `required`, `system`, `sorter`, `added`, `changed`) VALUES
('membershipExpiresOnHeader', 7, 'header', '', 'Membership Expires on xx/xx/xx (Vendor Users Notification)', '', '', 'n', 'n', 600, '2019-06-12 14:00:00', '2019-06-12 14:00:00'),
('membershipExpiresOnSubject', 7, 'text', 'Subject', 'Membership #{id} expires on {expiredOn}', '', '', 'y', 'n', 610, '2019-06-12 14:00:00', '2019-06-12 14:00:00'),
('membershipExpiresOnFrom', 7, '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', 620, '2019-06-12 14:00:00', '2019-06-12 14:00:00'),
('membershipExpiresOnBody', 7, 'HtmlEditor', 'Message Body', '<p>Membership #{id}<span style="font-size: 15px;"> expires on {expiredOn}. </span></p>\r\n\r\n<p><span style="font-size: 15px;">It can be renewed&nbsp;in the </span><a href="{licenseSectionUrl}" style="font-size: 15px;">Licensing</a><span style="font-size: 15px;"> section of the website.</span></p>\r\n', '', 'Constants: {id} {vendorDbaName} {vendorSoleProprietorName} {vendorFederalId} {vendorPhone} {licenseSectionUrl}', 'y', 'n', 630, '2019-06-12 14:00:00', '2019-06-12 14:00:00');

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

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

INSERT INTO `ma_Settings` (`name`, `idCategory`, `fieldType`, `label`, `value`, `options`, `description`, `required`, `system`, `sorter`, `added`, `changed`) VALUES
('membershipExpiredHeader', 7, 'header', '', 'Membership is Expired (Vendor Users Notification)', '', '', 'n', 'n', 700, '2019-06-12 14:00:00', '2019-06-12 14:00:00'),
('membershipExpiredSubject', 7, 'text', 'Subject', 'Membership #{id} is expired', '', '', 'y', 'n', 710, '2019-06-12 14:00:00', '2019-06-12 14:00:00'),
('membershipExpiredFrom', 7, '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', 720, '2019-06-12 14:00:00', '2019-06-12 14:00:00'),
('membershipExpiredBody', 7, 'HtmlEditor', 'Message Body', '<p>Membership #{id}<span style="font-size: 15px;">&nbsp;is expired today but will be active next {gracePeriod} days. </span></p>\r\n\r\n<p><span style="font-size: 15px;">It can renewed&nbsp;in the </span><a href="{licenseSectionUrl}" style="font-size: 15px;">Licensing</a><span style="font-size: 15px;"> section of the website.</span></p>\r\n', '', 'Constants: {id} {expiredOn} {vendorDbaName} {vendorSoleProprietorName} {vendorFederalId} {vendorPhone} {detailsUrl}', 'y', 'n', 730, '2019-06-12 14:00:00', '2019-06-12 14:00:00');

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

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

UPDATE `ma_Settings` SET `value` = '<p><a href="{licensePdfUrl}" style="font-size: 15px;">License #{id}</a><span style="font-size: 15px;"> expires for <strong>{tribe}</strong> tribe on {expiredOn}.</span></p><p><span style="font-size: 15px;">It can be renewed&nbsp;in the </span><a href="{licenseSectionUrl}" style="font-size: 15px;">Licensing</a><span style="font-size: 15px;"> section of the website.</span></p>' WHERE `ma_Settings`.`name` = 'licenseExpiresOnBody';

UPDATE `ma_Settings` SET `description` = 'Constants: {id} {vendorDbaName} {vendorSoleProprietorName} {vendorFederalId} {vendorPhone} {licenseSectionUrl} {tribe}' WHERE `ma_Settings`.`name` = 'licenseExpiresOnBody';

UPDATE `ma_Settings` SET `value` = 'License #{id} expires on {expiredOn} ("{tribe}" tribe)' WHERE `ma_Settings`.`name` = 'licenseExpiresOnSubject';

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

UPDATE `ma_Settings` SET `value` = '<p><a href="{licensePdfUrl}" style="font-size: 15px;">License #{id}</a><span style="font-size: 15px;">&nbsp;is expired today for <strong>{tribe}</strong> tribe, but will be active next {gracePeriod} days. </span></p><p><span style="font-size: 15px;">It can renewed&nbsp;in the </span><a href="{licenseSectionUrl}" style="font-size: 15px;">Licensing</a><span style="font-size: 15px;"> section of the website.</span></p>' WHERE `ma_Settings`.`name` = 'licenseExpiredBody';

UPDATE `ma_Settings` SET `description` = 'Constants: {id} {expiredOn} {vendorDbaName} {vendorSoleProprietorName} {vendorFederalId} {vendorPhone} {detailsUrl} {tribe}' WHERE `ma_Settings`.`name` = 'licenseExpiredBody';

UPDATE `ma_Settings` SET `value` = 'License #{id} is expired ("{tribe}" tribe)' WHERE `ma_Settings`.`name` = 'licenseExpiredSubject';

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