ALTER TABLE `ma_Tribe`
    ADD `useW9form` ENUM('n','y') NOT NULL DEFAULT 'n' AFTER `rawMemorandum`,
    ADD `w9Description` TEXT DEFAULT NULL  AFTER `useW9form`,
    ADD `rawW9Description` TEXT DEFAULT NULL  AFTER `w9Description`,
    ADD `w9File` varchar(255) DEFAULT NULL AFTER `rawW9Description`;

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

ALTER TABLE `ma_LicenseTribe`
    ADD `w9File` VARCHAR(255) NULL DEFAULT NULL AFTER `expiredOn`;

ALTER TABLE `ma_LicenseTribe`
    ADD `isW9Approved` ENUM('n','y') NOT NULL DEFAULT 'n' AFTER `w9File`;
-- --
-- --
INSERT INTO `ma_Settings` (`name`, `idCategory`, `fieldType`, `label`, `value`, `options`, `description`, `required`, `system`, `sorter`, `added`, `changed`) VALUES
('licenseNeedW9UserHeader', 7, 'header', '', 'License Needs Upload Complete W-9 Form (Vendor Users Notification)', '', '', 'n', 'n', 800, NOW(), NOW()),
('licenseNeedW9UserSubject', 7, 'text', 'Subject', 'License #{id} needs upload complete W-9 Form ("{tribe}" tribe)', '', '', 'y', 'n', 810, NOW(), NOW()),
('licenseNeedW9UserFrom', 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', 820, NOW(), NOW()),
('licenseNeedW9UserBody', 7, 'HtmlEditor', 'Message Body', '<p>License #{id} needs Upload Complete W-9 Form:</p>\r\n\r\n<p><strong>Tribe</strong>: {tribe}<br/\r\n><strong>License #</strong>: {id}<br />\r\n</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>Go to <a href="{uploadUrl}">W-9 Form upload page</a> or <a href="{licenseSectionUrl}">Licensing section</a> of the website.</p></p>\r\n', '', 'Constants: {id} {vendorDbaName} {vendorSoleProprietorName} {vendorFederalId} {vendorPhone} {licenseSectionUrl} {uploadUrl} {tribe}', 'y', 'n', 830, NOW(), NOW());

INSERT INTO `ma_SettingsOptions` (`name`, `variable`, `value`, `type`, `sorter`) VALUES
('licenseNeedW9UserBody', 'mode', 'view', 'string', NULL);
-- --
-- --
INSERT INTO `ma_Settings` (`name`, `idCategory`, `fieldType`, `label`, `value`, `options`, `description`, `required`, `system`, `sorter`, `added`, `changed`) VALUES
('licenseW9NeedApproveAdminHeader', 7, 'header', '', 'License W-9 Form Needs Approval (Admin Notification)', '', '', 'n', 'n', 950, NOW(), NOW()),
('licenseW9NeedApproveAdminSubject', 7, 'text', 'Subject', 'License W-9 Form Needs Approval', '', '', 'y', 'n', 960, NOW(), NOW()),
('licenseW9NeedApproveAdminFrom', 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', 970, NOW(), NOW()),
('licenseW9NeedApproveAdminBody', 7, 'HtmlEditor', 'Message Body', '<p>The following vendor: \"{businessName}\" dba \"{vendorDbaName}\" uploaded complete W-9 form. Please review and Approve or Decline it.</p>\r\n\r\n<p>Click <a href=\"{detailsUrl}\">here</a> to Approve/Decline uploaded W-9 Form.</p>\r\n', '', 'Constants: {id} {vendorDbaName} {businessName} {primaryTribalNation} {detailsUrl}', 'y', 'n', 980, NOW(), NOW());

INSERT INTO `ma_SettingsOptions` (`name`, `variable`, `value`, `type`, `sorter`) VALUES
('licenseW9NeedApproveAdminBody', 'mode', 'view', 'string', NULL);
-- --
-- --
INSERT INTO `ma_Settings` (`name`, `idCategory`, `fieldType`, `label`, `value`, `options`, `description`, `required`, `system`, `sorter`, `added`, `changed`) VALUES
('declineW9UserHeader', 7, 'header', '', 'Tribe Decline Complete W-9 Form (Vendor Users Notification)', '', '', 'n', 'n', 1000, NOW(), NOW()),
('declineW9UserSubject', 7, 'text', 'Subject', 'Uploaded W-9 Form for License #{id} has been Declined ("{tribe}" tribe).', '', '', 'y', 'n', 1010, NOW(), NOW()),
('declineW9UserFrom', 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', 1020, NOW(), NOW()),
('declineW9UserBody', 7, 'HtmlEditor', 'Message Body', '<p>Uploaded W-9 Form for License #{id} has been Declined by "{tribe}" tribe.</p><p>License #{id} needs Upload Completed W-9 Form:</p>\r\n\r\n<p><strong>Tribe</strong>: {tribe}<br/\r\n><strong>License #</strong>: {id}<br />\r\n</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>Go to <a href="{uploadUrl}">W-9 Form upload page</a> or <a href="{licenseSectionUrl}">Licensing section</a> of the website.</p></p>\r\n', '', 'Constants: {id} {vendorDbaName} {vendorSoleProprietorName} {vendorFederalId} {vendorPhone} {licenseSectionUrl} {uploadUrl} {tribe}', 'y', 'n', 1030, NOW(), NOW());

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