CREATE TABLE tblchange_orders (
	  tenant_id bigint(20) unsigned NOT NULL DEFAULT 1,
	  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
      invoice_id bigint(20) unsigned DEFAULT NULL,
	  title varchar(191) DEFAULT NULL, 
	  sent tinyint(1) NOT NULL DEFAULT 0,
	  datesend datetime DEFAULT NULL,	
      subtotal decimal(15,2) NOT NULL,
      total_tax decimal(15,2) NOT NULL DEFAULT 0.00,
      tax_type enum('global','for_item') DEFAULT 'for_item',
      total decimal(15,2) NOT NULL,  
	  datecreated datetime NOT NULL,
	  date date DEFAULT NULL,
	  addedfrom bigint(20) unsigned NOT NULL,
	  hash varchar(32) NOT NULL,
	  clientnote text DEFAULT NULL,
	  adminnote text DEFAULT NULL, 
      signature varchar(40) DEFAULT NULL,
	  client_signature_updated_on datetime DEFAULT NULL,
      authorize_signature varchar(40) DEFAULT NULL,
	  authorize_signature_updated_on datetime DEFAULT NULL,
	  show_client_signature tinyint(1) NOT NULL DEFAULT 1,
	  show_authorize_signature tinyint(1) NOT NULL DEFAULT 0, 
	  term_contracts_id bigint(20) unsigned DEFAULT NULL,
	  term_contracts_title varchar(1000) DEFAULT NULL,
	  term_contracts_description longtext DEFAULT NULL,
	  PRIMARY KEY (id),
	  KEY invoice_id (invoice_id)
	) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;


ALTER TABLE tblinvoices ADD grand_total decimal(15,2) NOT NULL AFTER total;

ALTER TABLE tblmodule_taxes MODIFY COLUMN rel_type enum('estimate', 'invoice', 'detail_estimate','quickbid_estimate','change_order') NOT NULL;

UPDATE tblinvoices SET grand_total = total;

INSERT INTO tblplan_features (name, codename, description,type,added_on) 
VALUES('Change Order', 'allowed_change_order', 'Change Order','restrict',UTC_TIMESTAMP());

SET @last_id_in_table3 = LAST_INSERT_ID();
INSERT INTO tblplan_features_value (plans_id, plans_features_id, value,other_data,added_on) 
VALUES(9, @last_id_in_table3, 0,'{"outside_message":"Change Order is a pro feature, in order to use it Upgrade to PRO","inside_message":"Change Order is a pro feature, in order to use it Upgrade to PRO","tag_color":"#eb0e0e","tag_title":"PRO","link":"https:\/\/my.contractorplus.app\/","count":"0"}',UTC_TIMESTAMP()),
(10, @last_id_in_table3, 1,NULL,UTC_TIMESTAMP()),
(11, @last_id_in_table3, 1,NULL,UTC_TIMESTAMP()),
(12, @last_id_in_table3, 1,NULL,UTC_TIMESTAMP()); 

INSERT INTO tblstaff_permissions (tenant_id, staff_id, feature, capability) SELECT tenant_id, staffid as staff_id, 'change_orders' as feature, 'create' as capability FROM tblstaff WHERE is_tenant = 0;
INSERT INTO tblstaff_permissions (tenant_id, staff_id, feature, capability) SELECT tenant_id, staffid as staff_id, 'change_orders' as feature, 'edit' as capability FROM tblstaff WHERE is_tenant = 0;
INSERT INTO tblstaff_permissions (tenant_id, staff_id, feature, capability) SELECT tenant_id, staffid as staff_id, 'change_orders' as feature, 'view_own' as capability FROM tblstaff WHERE is_tenant = 0;
INSERT INTO tblstaff_permissions (tenant_id, staff_id, feature, capability) SELECT tenant_id, staffid as staff_id, 'change_orders' as feature, 'receive_notification' as capability FROM tblstaff WHERE is_tenant = 0;


INSERT INTO tblemailtemplates_static (type,slug,language,name,subject,message,fromname,fromemail,plaintext,active,`order`)
VALUES('change_order','change-order-send-to-client','english','Send Change Order to Client','{change_order_no} for {invoice_client_property_street_1} {invoice_client_property_street_2}','<span style="font-size: 12pt;">Hi {contact_firstname},</span><br/><br/><span style="font-size: 12pt;">{change_order_no} is ready for your review and requires your approval.</span><br/><br/><span style="font-size: 12pt;">You can view it here: <a href="{change_order_link}">{change_order_no}-{invoice_client_property_street_1} {invoice_client_property_street_2}.</a></span><br/><br/><span style="font-size: 12pt;"><span style="font-size: 12pt;">{email_signature}</span>','{companyname}','',0,1,0);


INSERT INTO tblemailtemplates_static (type,slug,language,name,subject,message,fromname,fromemail,plaintext,active,`order`)
VALUES('change_order','change-order-already-send-to-team-member','english','Change Order Already Sent to Client','{change_order_no} for {invoice_client_property_street_1} {invoice_client_property_street_2}','<span style="font-size: 12pt;">Hi {contact_firstname},</span><br/><br/><span style="font-size: 12pt;">{change_order_no} is ready for your review and requires your approval.</span><br/><br/><span style="font-size: 12pt;">You can view it here: <a href="{change_order_link}">{change_order_no}-{invoice_client_property_street_1} {invoice_client_property_street_2}.</a></span><br/><br/><span style="font-size: 12pt;"><span style="font-size: 12pt;">{email_signature}</span>','{companyname}','',0,1,0);

INSERT INTO tblemailtemplates (tenant_id, type, slug, language, name, subject,message,fromname,fromemail,plaintext,active,`order`,is_tenant_email_config_allowed,is_tenant )
SELECT t.id,s.type,s.slug,s.language,s.name,s.subject,s.message,s.fromname,s.fromemail,s.plaintext,s.active,s.order,0,0
FROM tbltenants t
JOIN tblemailtemplates_static s ON 1 = 1 AND s.slug IN ('change-order-send-to-client','change-order-already-send-to-team-member');


INSERT INTO `tblemail_push_notification_config` (codename,is_active) VALUES ('change_order_send_to_client', 1), ('change_order_already_send_to_team_member', 1);


ALTER TABLE tblsms_templates_static MODIFY COLUMN type enum('estimate','invoice','post_inspection','business_review','client_referral','estimate_expiration_reminder','invoice_overdue_reminder','work_order','payment_schedule_reminder','change_order');

ALTER TABLE tblsms_templates MODIFY COLUMN type enum('estimate','invoice','post_inspection','business_review','client_referral','estimate_expiration_reminder','invoice_overdue_reminder','work_order','payment_schedule_reminder','change_order');

INSERT INTO tblsms_templates_static (type, template) 
VALUES('change_order','Thanks for your business! Your {change_order_no} can be viewed here: {change_order_link}');

INSERT INTO tblsms_templates (tenant_id, type,template,created_on,updated_on )
SELECT t.id,s.type,s.template,UTC_TIMESTAMP(),UTC_TIMESTAMP()
FROM tbltenants t
JOIN tblsms_templates_static s ON 1 = 1 AND s.type ='change_order';



ALTER TABLE tblsms_templates_static MODIFY COLUMN type enum('estimate','invoice','post_inspection','business_review','client_referral','estimate_expiration_reminder','invoice_overdue_reminder','work_order','payment_schedule_reminder','change_order','change_order_payment_schedule_reminder');

ALTER TABLE tblsms_templates MODIFY COLUMN type enum('estimate','invoice','post_inspection','business_review','client_referral','estimate_expiration_reminder','invoice_overdue_reminder','work_order','payment_schedule_reminder','change_order','change_order_payment_schedule_reminder');

INSERT INTO tblsms_templates_static (type, template) 
VALUES('change_order_payment_schedule_reminder','Hey {contact_firstname}!  This is {staff_firstname} from {companyname}, just a friendly reminder that a payment of {payment_schedule_amount} is due for {payment_schedule_name} on {change_order_no}. You can view the change order and pay the amount due at: {change_order_link}');

INSERT INTO tblsms_templates (tenant_id, type,template,created_on,updated_on )
SELECT t.id,s.type,s.template,UTC_TIMESTAMP(),UTC_TIMESTAMP()
FROM tbltenants t
JOIN tblsms_templates_static s ON 1 = 1 AND s.type ='change_order_payment_schedule_reminder';

INSERT INTO tblemail_push_notification_config (codename,is_active) VALUES ('change_order_payment_schedule_reminder', 1);

INSERT INTO tblsms_push_notification_config (codename,is_active) VALUES ('change_order_payment_schedule_reminder', 1);

INSERT INTO tblemailtemplates_static (type,slug,language,name,subject,message,fromname,fromemail,plaintext,active,`order`)
VALUES('change_order','change-order-payment-schedule-reminder','english','Send Change Order Payment Schedule Reminder To Client','{payment_schedule_amount} payment due on {change_order_no}','<span style="font-size: 12pt;">Hi {contact_firstname}, </span><br/><br/><span style="font-size: 12pt;">This is a friendly notification to let you know a payment of {payment_schedule_amount} for {payment_schedule_name} is due on {change_order_no}</span><br/><br/><span style="font-size: 12pt;">You can view the change order and pay the balance here {change_order_link} </span><br/><br/><span style="font-size: 12pt;">If you have any questions, feel free to give us a call at {staff_phonenumber}.</span><br/><br/><span style="font-size: 12pt;">{email_signature}</span>','{companyname}','',0,1,0);

INSERT INTO tblemailtemplates (tenant_id, type, slug, language, name, subject,message,fromname,fromemail,plaintext,active,`order`,is_tenant_email_config_allowed,is_tenant )
SELECT t.id,s.type,s.slug,s.language,s.name,s.subject,s.message,s.fromname,s.fromemail,s.plaintext,s.active,s.order,0,0
FROM tbltenants t
JOIN tblemailtemplates_static s ON 1 = 1 AND s.slug IN ('change-order-payment-schedule-reminder');

ALTER TABLE tblchange_orders ADD acceptance_firstname varchar(50) DEFAULT NULL;
ALTER TABLE tblchange_orders ADD acceptance_lastname varchar(50) DEFAULT NULL;
ALTER TABLE tblchange_orders ADD acceptance_email varchar(100) DEFAULT NULL;
ALTER TABLE tblchange_orders ADD acceptance_date datetime DEFAULT NULL;
ALTER TABLE tblchange_orders ADD acceptance_ip varchar(40) DEFAULT NULL;

ALTER TABLE tblinvoices ADD acceptance_firstname varchar(50) DEFAULT NULL;
ALTER TABLE tblinvoices ADD acceptance_lastname varchar(50) DEFAULT NULL;
ALTER TABLE tblinvoices ADD acceptance_email varchar(100) DEFAULT NULL;
ALTER TABLE tblinvoices ADD acceptance_date datetime DEFAULT NULL;
ALTER TABLE tblinvoices ADD acceptance_ip varchar(40) DEFAULT NULL;
