ALTER TABLE tblestimates ADD request_deposit_percent decimal(15,2) DEFAULT 0.00;
ALTER TABLE tblestimates ADD request_deposit_total decimal(15,2) DEFAULT 0.00;

ALTER TABLE tblinvoices ADD request_deposit_percent decimal(15,2) DEFAULT 0.00;
ALTER TABLE tblinvoices ADD request_deposit_total decimal(15,2) DEFAULT 0.00;


CREATE TABLE tblpayment_schedule_details (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  tenant_id bigint(20) unsigned NOT NULL DEFAULT 1,
  rel_id bigint(20) unsigned NOT NULL,
  rel_type varchar(20) NOT NULL,
  schedule_name varchar(255) DEFAULT NULL,
  amount decimal(15,2) DEFAULT 0.00,
  percent decimal(15,2) DEFAULT 0.00,
  schedule_order tinyint(1) DEFAULT 1,
  added_on datetime NOT NULL,
  modified_on datetime DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


ALTER TABLE tblpayment_schedule_details ADD is_percent tinyint(1) DEFAULT 1 AFTER schedule_name;
ALTER TABLE tblpayment_schedule_details ADD is_date_specific tinyint(1) DEFAULT 0 AFTER schedule_order;
ALTER TABLE tblpayment_schedule_details ADD schedule_date date DEFAULT NULL AFTER is_date_specific;

ALTER TABLE tblinvoices ADD request_deposit_is_date_specific tinyint(1) DEFAULT NULL AFTER request_deposit_total;
ALTER TABLE tblinvoices ADD request_deposit_date date DEFAULT NULL AFTER request_deposit_is_date_specific;

ALTER TABLE tblestimates ADD request_deposit_is_date_specific tinyint(1) DEFAULT NULL AFTER request_deposit_total;
ALTER TABLE tblestimates ADD request_deposit_date date DEFAULT NULL AFTER request_deposit_is_date_specific;

ALTER TABLE tblterm_contracts ADD show_payment_deposit_in_contract tinyint(1) DEFAULT 1 AFTER is_default_for_estimate;
ALTER TABLE tblterm_contracts_static ADD show_payment_deposit_in_contract tinyint(1) DEFAULT 1 AFTER is_default_for_estimate;

ALTER TABLE tblestimates ADD show_payment_deposit_in_contract tinyint(1) DEFAULT 1 AFTER term_contracts_description;
ALTER TABLE tblinvoices  ADD show_payment_deposit_in_contract tinyint(1) DEFAULT 1 AFTER term_contracts_description;

INSERT INTO tbloptions SELECT id, null, 'invoice_send_payment_schedule_reminders_staff','0',1 FROM tbltenants;
INSERT INTO tbloptions_static VALUES (null,'invoice_send_payment_schedule_reminders_staff','0',1);

INSERT INTO tbloptions SELECT id, null, 'automatically_send_invoice_payment_schedule_reminder_to_client_via','email',1 FROM tbltenants;
INSERT INTO tbloptions_static VALUES (null,'automatically_send_invoice_payment_schedule_reminder_to_client_via','email',1);

INSERT INTO tbloptions SELECT id, null, 'invoice_send_payment_schedule_reminders_client','0',1 FROM tbltenants;
INSERT INTO tbloptions_static VALUES (null,'invoice_send_payment_schedule_reminders_client','0',1);

INSERT INTO tblmobile_push_notification_config (codename,is_active) VALUES ('payment_schedule_reminder',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');
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');

INSERT INTO tblsms_templates_static (type, template) 
VALUES('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 invoice {invoice_number}. You can view the invoice and pay the amount due at: {invoice_link}');

INSERT INTO tblsms_templates (tenant_id, type,template,created_on,updated_on )
SELECT t.id,s.type,s.template,'2022-06-03 06:51:20','2022-06-03 06:51:20'
FROM tbltenants t
JOIN tblsms_templates_static s ON 1 = 1 AND s.type ='payment_schedule_reminder';

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

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

INSERT INTO tblemailtemplates_static (type,slug,language,name,subject,message,fromname,fromemail,plaintext,active,`order`)
VALUES('invoice','payment-schedule-reminder','english','Send Payment Schedule Reminder To Client','{payment_schedule_amount} payment due on invoice {invoice_number}','<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 Invoice {invoice_number}</span><br/><br/><span style="font-size: 12pt;">You can view the invoice and pay the balance here {invoice_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 ('payment-schedule-reminder');

INSERT INTO tblplan_features (name, codename, description,type,added_on) 
VALUES('Payment Schedule', 'allowed_payment_schedule', 'Payment Schedule','restrict','2022-06-16 13:16:16');

SET @plans_features_id = LAST_INSERT_ID();

INSERT INTO tblplan_features_value (plans_id, plans_features_id, value,other_data,added_on) 
VALUES(9, @plans_features_id, 0,'{"outside_message":"Payment Schedule & Required Deposit is a pro feature, in order to use it Upgrade to PRO","inside_message":"Payment Schedule & Required Deposit is a pro feature, in order to use it Upgrade to PRO","tag_color":"#eb0e0e","tag_title":"UPGRADE","link":"https:\/\/my.contractorplus.app\/","count":"0"}','2022-06-16 13:16:16'),
(10, @plans_features_id, 1,NULL,'2022-06-16 13:16:16'),
(11, @plans_features_id, 1,NULL,'2022-06-16 13:16:16'),
(12, @plans_features_id, 1,NULL,'2022-06-16 13:16:16'); 

UPDATE tblstaff SET refresh_permissions = 1;

UPDATE tblplan_features_value as pfv
LEFT JOIN tblplan_features as pf ON  pfv.plans_features_id = pf.id 
SET pfv.other_data = '{"outside_message":"Payment Schedule & Required Deposit is a pro feature, in order to use it Upgrade to PRO","inside_message":"Payment Schedule & Required Deposit 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"}'
WHERE pf.codename = "allowed_payment_schedule" AND pfv.plans_id = 9 ;


