ALTER TABLE `tbljobs` ADD `job_completion_sign` INT(2) NULL DEFAULT '0' AFTER `added_on`;


INSERT INTO `tblplan_features` (`name`, `codename`, `description`, `type`, `added_on`) VALUES ('Job Completion Signature', 'job_completion_sign', 'Job Completion Signature', 'restrict', UTC_TIMESTAMP());
SET @job_completion_sign = LAST_INSERT_ID();

INSERT INTO `tblplan_features_value` (`plans_id`, `plans_features_id`, `value`, `other_data`, `added_on`) VALUES ('8', @job_completion_sign, '0', '{\"outside_message\":\"You do not have this permission in your current, please upgrade your plan to PRO.\",\"inside_message\":\"You do not have this permission in your current, please upgrade your plan to PRO.\",\"tag_color\":\"#eb0e0e\",\"tag_title\":\"PRO\",\"link\":\"https:\\/\\/dev.my.contractorplus.app\\/\"}', UTC_TIMESTAMP());
INSERT INTO `tblplan_features_value` (`plans_id`, `plans_features_id`, `value`, `other_data`, `added_on`) VALUES ('9', @job_completion_sign, '0', '{\"outside_message\":\"You do not have this permission in your current, please upgrade your plan to PRO.\",\"inside_message\":\"You do not have this permission in your current, please upgrade your plan to PRO.\",\"tag_color\":\"#eb0e0e\",\"tag_title\":\"PRO\",\"link\":\"https:\\/\\/dev.my.contractorplus.app\\/\"}', UTC_TIMESTAMP());

INSERT INTO `tblplan_features_value` (`id`, `plans_id`, `plans_features_id`, `value`, `other_data`, `added_on`) VALUES (NULL, '10', @job_completion_sign, '1', NULL, UTC_TIMESTAMP());
INSERT INTO `tblplan_features_value` (`id`, `plans_id`, `plans_features_id`, `value`, `other_data`, `added_on`) VALUES (NULL, '11', @job_completion_sign, '1', NULL, UTC_TIMESTAMP());
INSERT INTO `tblplan_features_value` (`id`, `plans_id`, `plans_features_id`, `value`, `other_data`, `added_on`) VALUES (NULL, '12', @job_completion_sign, '1', NULL, UTC_TIMESTAMP());


CREATE TABLE `tbljob_coc` (
  `id` bigint(20) UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL,
  `job_id` bigint(20) UNSIGNED NOT NULL,
  `tenant_id` bigint(20) UNSIGNED NOT NULL,
  `client_id` bigint(20) UNSIGNED NOT NULL,
  `signature` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL,
  `approved_on` datetime NOT NULL,
  `ip_address` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `client_agent` text COLLATE utf8_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


INSERT INTO `tblmobile_push_notification_config` (`id`, `codename`, `is_active`) VALUES (NULL, 'signed_job_coc', '1');


INSERT INTO `tblemailtemplates_static` (`type`, `slug`, `language`, `name`, `subject`, `message`, `fromname`, `fromemail`, `plaintext`, `active`, `order`) VALUES ('job_schedule', 'job-coc-mail', 'english', 'Job Certificate of Completion', 'Job Certificate of Completion', '<span style="font-size: 12pt;">Hi {contact_firstname},</span><br /><br /><span style="font-size: 12pt;">The job at <b>{client_property_address}</b> has been marked as completed by <b>{team_member_firstname}</b>&nbsp;at <b>{companyname}</b>. If the job was completed satisfactory, we ask that you please sign a Certificate of Completion: <a href="{job_coc_link}">{job_number}</a></span><br /><br /><span style="font-size: 12pt;">Please contact us for more information.</span><br /><br /><span style="font-size: 12pt;">{email_signature}</span>', '', NULL, '0', '1', '0');

INSERT INTO `tblemailtemplates` (`tenant_id`, `type`, `slug`, `language`, `name`, `subject`, `message`, `fromname`, `fromemail`, `plaintext`, `active`, `order`) SELECT id, 'job_schedule', 'job-coc-mail', 'english', 'Job Certificate of Completion', 'Job Certificate of Completion', '<span style="font-size: 12pt;">Hi {contact_firstname},</span><br /><br /><span style="font-size: 12pt;">The job at <b>{client_property_address}</b> has been marked as completed by <b>{team_member_firstname}</b>&nbsp;at <b>{companyname}</b>. If the job was completed satisfactory, we ask that you please sign a Certificate of Completion: <a href="{job_coc_link}">{job_number}</a></span><br /><br /><span style="font-size: 12pt;">Please contact us for more information.</span><br /><br /><span style="font-size: 12pt;">{email_signature}</span>', '', NULL, '0', '1', '0' FROM tbltenants;