CREATE TABLE tblwork_orders (
	  tenant_id bigint(20) unsigned NOT NULL DEFAULT 1,
	  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
	  title varchar(191) DEFAULT NULL, 
	  sent tinyint(1) NOT NULL DEFAULT 0,
	  datesend datetime DEFAULT NULL,
	  clientid bigint(20) unsigned NOT NULL,
	  deleted_customer_name varchar(100) DEFAULT NULL,  
	  number bigint(20) unsigned NOT NULL,
	  prefix varchar(50) DEFAULT NULL,
	  number_format bigint(20) unsigned NOT NULL DEFAULT 0,
	  work_order_number_text varchar(191) DEFAULT NULL,
	  hash varchar(32) DEFAULT NULL,
	  datecreated datetime NOT NULL,
	  date date NOT NULL,
	  addedfrom bigint(20) unsigned NOT NULL,
	  status enum('pending','inprogress','completed','cancelled','sent','delegated') COLLATE utf8_unicode_ci DEFAULT 'pending',
	  clientnote text DEFAULT NULL,
	  adminnote text DEFAULT NULL,  
	  invoiceid bigint(20) unsigned DEFAULT NULL,
	  invoiced_date datetime DEFAULT NULL,
	  estimate_id bigint(20) unsigned DEFAULT NULL,
	  terms text DEFAULT NULL,	 
	  property_street varchar(200) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
	  property_street_2 varchar(200) DEFAULT NULL,
	  property_city varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
	  property_state varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
	  property_state_id bigint(20) unsigned DEFAULT NULL,
	  property_zip varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
	  property_country bigint(20) DEFAULT NULL,
	  property_id bigint(20) DEFAULT NULL,
	  PRIMARY KEY (id),
	  KEY clientid (clientid)
	) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;


INSERT INTO tbloptions_static (name, value, autoload) VALUES('work_order_prefix', 'WO-', 1);
INSERT INTO tbloptions_static (name, value, autoload) VALUES('next_work_order_number', '1', 1);
INSERT INTO tbloptions_static (name, value, autoload) VALUES('work_order_number_format', '1', 1);
INSERT INTO tbloptions_static (name, value, autoload) VALUES('default_work_order_item_unit_type', 'unit', 1);
INSERT INTO tbloptions_static (name, value, autoload) VALUES('predefined_terms_work_order', '', 1);
INSERT INTO tbloptions_static (name, value, autoload) VALUES('predefined_clientnote_work_order', '', 1);
INSERT INTO tbloptions_static (name, value, autoload) VALUES('predefined_adminnote_work_order', '', 1);

INSERT INTO tbloptions (tenant_id, name, value, autoload)
SELECT t.id,s.name,s.value,s.autoload
FROM tbltenants t
JOIN tbloptions_static s ON 1 = 1 AND s.name IN ('work_order_prefix','next_work_order_number','work_order_number_format','default_work_order_item_unit_type','predefined_terms_work_order','predefined_clientnote_work_order','predefined_adminnote_work_order');


CREATE TABLE tblwork_order_files (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  work_order_id bigint(20) unsigned DEFAULT NULL,
  file_name varchar(191) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  title varchar(255) DEFAULT NULL,
  description longtext DEFAULT NULL,
  filetype varchar(40) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  visible_to_customer bigint(20) unsigned NOT NULL DEFAULT 0,
  attachment_key varchar(32) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  external varchar(40) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  external_link text CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  thumbnail_link text CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'For external usage',
  original_file_name varchar(191) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  dateadded datetime NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE tblwork_order_image_files (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  work_order_id bigint(20) unsigned DEFAULT NULL,
  file_name varchar(191) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  title varchar(255) DEFAULT NULL,
  description longtext DEFAULT NULL,
  filetype varchar(40) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  visible_to_customer bigint(20) unsigned NOT NULL DEFAULT 0,
  attachment_key varchar(32) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  external varchar(40) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  external_link text CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  thumbnail_link text CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'For external usage',
  original_file_name varchar(191) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  dateadded datetime NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO tblapad_options (type, name, value, autoload)
VALUES('work_order_files','allowed_file_extention','{"image":["jpeg","jpg","png","gif"],"video":["mp4","mpeg","webp", "avi"],"audio":[],"document":["pdf","doc","docx"]}',1),
      ('work_order_files','allowed_file_size','5120',1),
      ('work_order_image_files','allowed_file_extention','{"image": ["jpeg", "jpg", "png", "gif"]}',1),
      ('work_order_image_files','allowed_file_size','5120',1);

INSERT INTO tblplan_features (name, codename, description,type,added_on) 
VALUES('Work Order', 'work_order_module', 'Work Order','allow','2021-12-02 12:12:14');

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":"Work Order is a pro feature, in order to use it Upgrade to PRO","inside_message":"Work Order 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"}','2021-12-02 12:12:14'),
(10, @last_id_in_table3, 1,NULL,'2021-12-02 12:12:14'),
(11, @last_id_in_table3, 1,NULL,'2021-12-02 12:12:14'),
(12, @last_id_in_table3, 1,NULL,'2021-12-02 12:12:14'); 

INSERT INTO tbltrial_details (module, no_of_trial_days, module_live_date,details,addedon) 
VALUES('work_order_module',15,'2021-12-02', '{"trial_active_details":{"icon":"assets\/images\/trial_icons\/trial_active.png","title":"Work Orders (Trial)","description":"Work Orders is a PRO feature. You have {no_of_trial_days_left} left on your free trial, after that, you\'ll need to choose a plan.","button_text":"UPGRADE PLAN"},"trial_expired_detials":{"icon":"assets\/images\/trial_icons\/trial_expired.png","title":"Trial Expired!","description":"Your trial of Work Orders Module has expired. To continue using Work Orders Module, choose a PRO plan now.","button_text":"UPGRADE PLAN"}}', '2021-12-02');


INSERT INTO tblstaff_permissions (tenant_id, staff_id, feature, capability) SELECT tenant_id, staffid as staff_id, 'work_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, 'work_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, 'work_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, 'work_orders' as feature, 'receive_notification' as capability FROM tblstaff WHERE is_tenant = 0;

INSERT INTO tblplan_features (name, codename, description,type,added_on) 
VALUES('No of Work Order Images', 'no_of_work_order_images_allowed', 'No of Work Order images allowed in plan.','restrict','2021-12-02 12:12:14');

SET @last_id_in_table2 = LAST_INSERT_ID();

INSERT INTO tblplan_features_value (plans_id, plans_features_id, value,other_data,added_on) 
VALUES(9, @last_id_in_table2, 0,'{"outside_message":"If you want to add photos please upgrade to Pro.","inside_message":"If you want to add photos please upgrade to Pro.","tag_color":"#eb0e0e","tag_title":"PRO","link":"https:\/\/my.contractorplus.app\/","count":"0"}','2021-12-02 12:12:14'),
(10, @last_id_in_table2, 5,'{"outside_message":"You have limit of 5 photos on your plan. If you want to add more please upgrade.","inside_message":"You have limit of 5 photos on your plan, we have selected first {!current_count!} photos. If you want to add more please upgrade.","tag_color":"#eb0e0e","tag_title":"UPGRADE","link":"https:\/\/my.contractorplus.app\/","count":"5"}','2021-12-02 12:12:14'),
(11, @last_id_in_table2, 10,'{"outside_message":"You have limit of 10 photos on your plan. If you want to add more please upgrade.","inside_message":"You have limit of 10 photos on your plan, we have selected first {!current_count!} photos. If you want to add more please upgrade.","tag_color":"#eb0e0e","tag_title":"UPGRADE","link":"https:\/\/my.contractorplus.app\/","count":"10"}','2021-12-02 12:12:14'),
(12, @last_id_in_table2, 20,'{"outside_message":"You have limit of 20 photos on your plan. If you want to add more please upgrade.","inside_message":"You have limit of 20 photos on your plan, we have selected first {!current_count!} photos. If you want to add more please upgrade.","tag_color":"#eb0e0e","tag_title":"","link":"https:\/\/my.contractorplus.app\/","count":"20"}','2021-12-02 12:12:14'); 


INSERT INTO tblplan_features (name, codename, description,type,added_on) 
VALUES('No of Work Order Files', 'no_of_work_order_files_allowed', 'No of Work Order files allowed in plan.','restrict','2021-12-02 12:12:14');

SET @last_id_in_table1 = LAST_INSERT_ID();
INSERT INTO tblplan_features_value (plans_id, plans_features_id, value,other_data,added_on) 
VALUES(9, @last_id_in_table1, 0,'{ "outside_message":"If you want to add files please upgrade to Pro.", "inside_message":"If you want to add files please upgrade to Pro.","tag_color":"#eb0e0e","tag_title":"PRO","link":"https:\/\/my.contractorplus.app\/","count":"0"}','2021-12-02 12:12:14'),
(10, @last_id_in_table1, 5,'{ "outside_message":"You have limit of 5 files on your plan. If you want to add more please upgrade.", "inside_message":"You have limit of 5 files on your plan, we have selected first {!current_count!} files. If you want to add more please upgrade.", "tag_color":"#eb0e0e","tag_title":"UPGRADE","link":"https:\/\/my.contractorplus.app\/","count":"5"}','2021-12-02 12:12:14'),
(11, @last_id_in_table1, 10,'{ "outside_message":"You have limit of 10 files on your plan. If you want to add more please upgrade.", "inside_message":"You have limit of 10 photos on your plan, we have selected first {!current_count!} files. If you want to add more please upgrade.", "tag_color":"#eb0e0e","tag_title":"UPGRADE","link":"https:\/\/my.contractorplus.app\/","count":"10"}','2021-12-02 12:12:14'),
(12, @last_id_in_table1, 20,'{ "outside_message":"You have limit of 20 files on your plan. If you want to add more please upgrade.", "inside_message":"You have limit of 20 files on your plan, we have selected first {!current_count!} files. If you want to add more please upgrade.", "tag_color":"#eb0e0e","tag_title":"","link":"https:\/\/my.contractorplus.app\/","count":"20"}','2021-12-02 12:12:14'); 


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');

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

INSERT INTO tblsms_templates_static (type, template) 
VALUES('work_order','{staff_firstname}, you\'ve been assigned to {work_order_address} on {work_order_schedule_date}. It\'s been added to your schedule.');

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


INSERT INTO tblemailtemplates_static (type,slug,language,name,subject,message,fromname,fromemail,plaintext,active,`order`)
VALUES('work_order','work-order-send-to-team-member','english','Send Work Order to Team Member','New Work Order for {work_order_property_street_1} {work_order_property_street_2}','<span style="font-size: 12pt;">Hi {staff_firstname}, </span><br/><br/><span style="font-size: 12pt;">You\'ve been assigned a new Work Order.</span><br/><br/><span style="font-size: 12pt;">Where: {work_order_address} <br/> When: on {work_order_schedule_date} <br/> Client/Tenant: {work_order_client} <br/> Gate Code: {work_order_gate_code} <br/> Lockbox Code: {work_order_lockbox_code} </span><br/><br/><span style="font-size: 12pt;">View/Print the Work Order details: <a href="{work_order_link}">{work_order_number}</a></span><br/><br/><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('work_order','work-order-already-send-to-team-member','english','Send Work Order Already Sent to Team Member','New Work Order for {work_order_property_street_1} {work_order_property_street_2}','<span style="font-size: 12pt;">Hi {staff_firstname}, </span><br/><br/><span style="font-size: 12pt;">You\'ve been assigned a new Work Order.</span><br/><br/><span style="font-size: 12pt;">Where: {work_order_address} <br/> When: on {work_order_schedule_date} <br/> Client/Tenant: {work_order_client} <br/> Gate Code: {work_order_gate_code} <br/> Lockbox Code: {work_order_lockbox_code} </span><br/><br/><span style="font-size: 12pt;">View/Print the Work Order details: <a href="{work_order_link}">{work_order_number}</a></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 ('work-order-send-to-team-member','work-order-already-send-to-team-member');


INSERT INTO `tblemail_push_notification_config` (codename,is_active) VALUES ('work_order_send_to_team_member', 1), ('work_order_already_send_to_team_member', 1);

INSERT INTO `tblsms_push_notification_config` (codename,is_active) VALUES ('work_order_send_to_team_member', 1), ('work_order_already_send_to_team_member', 1);

INSERT INTO tbloptions_static (name, value, autoload) VALUES('work_order_display_team_members_info', '', 1);
INSERT INTO tbloptions_static (name, value, autoload) VALUES('work_order_show_phone_number_in_header', '0', 1);
INSERT INTO tbloptions_static (name, value, autoload) VALUES('work_order_show_email_in_header', '0', 1);
INSERT INTO tbloptions_static (name, value, autoload) VALUES('work_order_show_website_in_header', '0', 1);
INSERT INTO tbloptions_static (name, value, autoload) VALUES('work_order_show_company_address', '1', 1);

INSERT INTO tbloptions (tenant_id, name, value, autoload)
SELECT t.id,s.name,s.value,s.autoload
FROM tbltenants t
JOIN tbloptions_static s ON 1 = 1 AND s.name IN ('work_order_display_team_members_info','work_order_show_phone_number_in_header','work_order_show_email_in_header','work_order_show_website_in_header','work_order_show_company_address');


CREATE TABLE IF NOT EXISTS `tblwork_order_delegate_staff` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint(20) unsigned NOT NULL,
  `work_order_id` bigint(20) unsigned NOT NULL,
  `staff_id` bigint(20) unsigned NOT NULL,
  `added_on` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE tblwork_orders ADD schedule_date date DEFAULT NULL;

INSERT INTO tblmobile_push_notification_config (codename,is_active) VALUES ('work_order_delegated',1);
