START TRANSACTION;
CREATE TABLE `tblonboarding_steps_sections` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `section_name` varchar(200) DEFAULT NULL,
  `section_order` bigint(20) unsigned DEFAULT NULL,
  `section_time` varchar(200) DEFAULT NULL,
  `section_desc` varchar(200) DEFAULT NULL,
  `active` tinyint(1) DEFAULT 1,
  `datecreated` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT  INTO `tblonboarding_steps_sections`(`section_name`,`section_order`,`section_time`,`section_desc`,`active`,`datecreated`) VALUES 
('Additional Workspace Setup Steps',1,'About 10 min',NULL,1,CURRENT_TIMESTAMP),
('Connect To Grow',2,'About 15 min',NULL,1,CURRENT_TIMESTAMP),
('The Final Stretch',3,'About 12 min',NULL,1,CURRENT_TIMESTAMP);

ALTER TABLE tblonboarding_steps_static ADD section_id bigint(20) DEFAULT NULL AFTER id;
ALTER TABLE tblonboarding_steps_static ADD card_image varchar(100) DEFAULT NULL AFTER icon;
ALTER TABLE tblonboarding_steps_static ADD show_video TINYINT(1) NOT NULL DEFAULT 0;
ALTER TABLE tblonboarding_steps_static ADD video_link varchar(299) DEFAULT NULL;

ALTER TABLE tblonboarding_steps_static MODIFY COLUMN `description` varchar(500) DEFAULT NULL;

ALTER TABLE tblonboarding_steps_sections ADD section_icon varchar(100) DEFAULT NULL AFTER section_time;

UPDATE tblonboarding_steps_sections SET section_icon = 'Additional-Workspace.svg' WHERE id = 1;

UPDATE tblonboarding_steps_sections SET section_icon = 'Connect-To-Grow.svg' WHERE id = 2;

UPDATE tblonboarding_steps_sections SET section_icon = 'The-Final-Stretch.svg' WHERE id = 3;

UPDATE tblonboarding_steps_static SET `section_id`= 1,`order` = 1,`skip_text`='I’ll Do This Later' ,`button_text` = 'Configure Business & Tax Information',`name`='Configure Your Business & Tax Information', `description` = 'We will populate your client reports such as Estimates, Invoices, Payment Receipts, Booking Confirmations and even W9’s based on your legal entity and tax settings. Let’s go ahead and complete this information and make sure everything looks right.' WHERE code_name = 'business_info';

UPDATE tblonboarding_steps_static SET `section_id`= 1,`order` = 2 ,`name`='Email Address Verification',`description` = 'Verify your email address so that you can send emails using Contractor+. Until your email is verified, many features will be disabled.',`skip_text`='I’ll Do This Later' WHERE code_name = 'email_verification';

UPDATE tblonboarding_steps_static SET `section_id`= 1,`order` = 3 ,`name`='Phone Number Verification',`description` = 'Verify your mobile phone number so that you can send reports to your clients via SMS, and receive important notifications. Until your mobile number is verified, many features will be disabled in Contractor+.',`skip_text`='I’ll Do This Later' WHERE code_name = 'mobile_verification';

UPDATE tblonboarding_steps_static SET `section_id`= 1,`order` = 4 ,`name`='How Big Is Your Team?',`description` = 'How many employees / team members do you have working for your business?',`skip_text`='I’ll Do This Later' WHERE code_name = 'select_company_size';

UPDATE tblonboarding_steps_static SET `section_id`= 1,`order` = 5 ,`skip_text`='I’ll Do This Later' WHERE code_name = 'why_using_cp';


UPDATE tblonboarding_steps_static SET `section_id`= 1,`order` = 6,`skip_text`='We Don\'t Have A Logo' ,`button_text` = 'Upload Logo', `name`='Upload Your Business Logo',`description` = 'We recommend you upload a professional logo for your business. This is part of your branding strategy as a business and is what people will visualize when they think of your business.' 
WHERE code_name = 'add_company_logo';

insert into `tblonboarding_steps_static` (`section_id`, `code_name`, `name`, `description`, `skip_text`, `icon`, `order`, `button_text`,`percentage`) values(1,'add_social_media','Is Your Business On Social Media?','If you have social media profiles for your company, we can link to them from your business listing and client hub to help you grow your social following. This step is optional but great if you’re looking to increase your online audience.','We Don\'t Have Social Profiles.','add_social_media.png','2','Configure My Social Profile Links',0);

UPDATE tblonboarding_steps_static SET `section_id`= 1,`order` = 7 WHERE code_name = 'add_social_media';

UPDATE tblonboarding_steps_static SET `section_id`= 1,`order` = 9,`skip_text`='I’ll Do This Later' ,`button_text` = 'Add Your License Information',`name`='Are You Licensed?', `description` = 'If you\'re licensed, it\'s a good idea to let your leads and clients know. This can build trust and confidence and help you win more bids. Once we\'ve verified your license information, we\'ll add a "License Verified" badge to your Estimates, Invoices and Client Portal.' WHERE code_name = 'license';

UPDATE tblonboarding_steps_static SET `section_id`= 1,`order` = 8 ,`skip_text`='I’ll Do This Later',`button_text` = 'Add Proof of Insurance', `name`='Are you Insured?', `description` = 'If you have a general liability, business insurance or worker\'s compensation policy, it\'s important you make your clients aware of this up front. Attach your insurance information and we\'ll add the "Insurance Verified" badge to your Estimates, Invoices and Client Portal.' WHERE code_name = 'insurance';


UPDATE tblonboarding_steps_static SET `section_id`= 1,`order` = 10,`skip_text`='I’ll Do This Later' ,`button_text` = 'Configure Your Payment Methods',`name`='Accept Credit/Debit & ACH Payments', `description` = 'With Contractor+ Payments, you can accept all major credit cards, debit cards and bank transfers. You can also link your Venmo and CashApp, or configure offline payment methods such as cash, check and money order.' WHERE code_name = 'configure_payment_methods';

insert into `tblonboarding_steps_static` (`section_id`, `code_name`, `name`, `description`, `skip_text`, `icon`, `order`, `button_text`,`percentage`) values(1,'where_you_came_from','Are You Joining Contractor+ From Other Software?','If you are coming to us from somewhere else, we’d like to know where you’re coming from and why you decided to look elsewhere. This will help us make sure we can meet your needs. We really care about your experience.','No I’m New','where_you_came_from.png','5','Yes, Please Select Which Software',0);

UPDATE tblonboarding_steps_static SET `section_id`= 1,`order` = 11 WHERE code_name = 'where_you_came_from';

insert into `tblonboarding_steps_static` (`section_id`, `code_name`, `name`, `description`, `skip_text`, `icon`, `order`, `button_text`,`percentage`,`show_video`) values(2,'cp_grow_business','Contractor+ GROW Google Business Opt. + Syndication','If you want to rank at the top for your service area and categories, we have a monthly Google Business PRO Local SEO Optimization & Listing Syndication service. Contractor+ GROW maximizes your exposure, leads and business.','I’ll Do This Later','cp_grow_business.png',1,'Tell Me More',0,1);

insert into `tblonboarding_steps_static` (`section_id`, `code_name`, `name`, `description`, `skip_text`, `icon`, `order`, `button_text`,`percentage`,`show_video`) values(2,'config_client_review','Collect Client Reviews & Feedback','You can automatically invite your clients to leave you a review after completing a job. You can also sync your Google Business Reviews to Contractor+ to get you more exposure and business.','I’ll Do This Later','config_client_review.png',2,'Configure Client Review Settings',0,0);

UPDATE tblonboarding_steps_static SET `section_id`= 2,`order` = 3,`skip_text`='Delete this' ,`button_text` = 'Create First Estimate',`name`='Create Your First Estimate', `description` = 'Watch this tutorial video that explains how estimation works in Contractor+ and learn how to configure your estimate settings so that your estimates work the way you need them to.' WHERE code_name = 'create_estimates';

insert into `tblonboarding_steps_static` (`section_id`, `code_name`, `name`, `description`, `skip_text`, `icon`, `order`, `button_text`,`percentage`,`show_video`) values(2,'create_invoices','Invoicing & Automated Follow Ups','Learn how Invoicing works in Contractor+ and how you can configure your Invoice Settings to automate follow ups, past due reminders, and more.','Delete this','create_invoices.png',4,'Create First Invoice',0,0);

insert into `tblonboarding_steps_static` (`section_id`, `code_name`, `name`, `description`, `skip_text`, `icon`, `order`, `button_text`,`percentage`,`show_video`) values(2,'create_jobs','Effortlessly Manage All Your Jobs & Projects','Schedule your work using Jobs or Work Orders. Assign team members, manage your related tasks, log your expenses and track everything related to each Job, in the Jobs dashboard.','Delete this','create_jobs.png',5,'Create First Job',0,0);

insert into `tblonboarding_steps_static` (`section_id`, `code_name`, `name`, `description`, `skip_text`, `icon`, `order`, `button_text`,`percentage`,`show_video`) values(2,'track_time_log','Tracking Your Team’s Time & Mileage','We make it really easy to keep track of hours worked and miles driven. You can even attribute time sheets and mileage logs to a specific job.','I’ll Do This Later','track_time_log.png',6,'Open Time Clock',0,0);

insert into `tblonboarding_steps_static` (`section_id`, `code_name`, `name`, `description`, `skip_text`, `icon`, `order`, `button_text`,`percentage`,`show_video`) values(2,'add_line_item_template','Use Line Item Templates To Save Time Bidding','Our Line Item Templates are similar to “Products & Services” in Quickbooks, except you can configure both your labor and your labor and materials on a per task basis.','I’ll Do This Later','add_line_item_template.png',7,'Manage Line Item Templates',0,0);

UPDATE tblonboarding_steps_static SET `section_id`= 2,`order` = 8,`skip_text`='I’ll Do This Later' ,`button_text` = 'Configure Quickbooks Sync',`name`='Sync Your Account Data To Quickbooks', `description` = 'Contractor+ isn’t a full replacement of accounting software in that we don’t reconcile your bank accounts. We recommend Quickbooks for account reconciliation and generation of your Cash Flow, P&L and Balance Sheet reports.' WHERE code_name = 'quickbook_sync';

insert into `tblonboarding_steps_static` (`section_id`, `code_name`, `name`, `description`, `skip_text`, `icon`, `order`, `button_text`,`percentage`,`show_video`) values(2,'config_zapier_api','Integrate With Other Software & Services','We’ve got both a developers API and a Zapier integration that enables you to build your own custom solutions or integrate with over 5,000+ other software solutions.','I’ll Do This Later','config_zapier_api.png',9,'Configure API & Zapier Settings',0,0);


UPDATE tblonboarding_steps_static SET `section_id`= 3,`order` = 1,`skip_text`='I’ll Do This Later' ,`button_text` = 'Configure Email Settings',`name`='Connect Business Email Account(s)', `description` = 'If you want to send your Estimates, Invoices, Booking Confirmations & Payment Receipts via email, you need to link your email provider to Contractor+ and authorize the app to send email on your behalf. This only takes a minute.' WHERE code_name = 'smtp_configuration';

insert into `tblonboarding_steps_static` (`section_id`, `code_name`, `name`, `description`, `skip_text`, `icon`, `order`, `button_text`,`percentage`,`show_video`) values(3,'sms_phone_settings_config','Configure Workspace Phone Settings','You can send & receive texts, and make & receive phone calls, on Mobile, Tablet and Desktop from right within Contractor+. You can assign individual team members their own numbers or use one number for the entire business.','I’ll Do This Later','sms_phone_settings_config.png',2,'Configure Phone & SMS Settings',0,0);


insert into `tblonboarding_steps_static` (`section_id`, `code_name`, `name`, `description`, `skip_text`, `icon`, `order`, `button_text`,`percentage`,`show_video`) values(3,'offer_consumer_financing','Offer Consumer Financing & Win More Jobs','It’s a well known fact, contractors who charge more & offer financing win more bids and make a lot more money. We’ve partnered and integrated with Wisetack to enable you to offer your clients the option to pay over time on jobs between $500 and $25,000.','I’ll Do This Later','offer_consumer_financing.png',3,'Configure Consumer Financing',0,0);

insert into `tblonboarding_steps_static` (`section_id`, `code_name`, `name`, `description`, `skip_text`, `icon`, `order`, `button_text`,`percentage`,`show_video`) values(3,'cp_relationship_management','Relationship Management For Contractors','Contractor+ lets you add all the contacts you do business with including your Leads, Clients, Team Members/Employees, Subcontractors & Vendors/Suppliers!','I’ll Do This Later','cp_relationship_management.png',4,'View',0,0);

insert into `tblonboarding_steps_static` (`section_id`, `code_name`, `name`, `description`, `skip_text`, `icon`, `order`, `button_text`,`percentage`,`show_video`) values(3,'import_data','Import Your Existing Leads, Clients & Other Data','We make it easy to import your data from other places. Just export your data from your old platform(s) and import & map the data in Contractor+.','I’ll Do This Later','import_data.png',5,'Import Data',0,0);

insert into `tblonboarding_steps_static` (`section_id`, `code_name`, `name`, `description`, `skip_text`, `icon`, `order`, `button_text`,`percentage`,`show_video`) values(3,'add_roles','Set Up Your Roles & Permissions, Clients & Other Data','There’s default roles that are pre-configured in Contractor+. You can also add your own roles & control each Team Member’s level of access.','I’ll Do This Later','add_roles.png',6,'Configure Roles & Permissions',0,0);

UPDATE tblonboarding_steps_static SET `section_id`= 3,`order` = 7,`skip_text`='I’ll Do This Later' ,`button_text` = 'Invite Team Member',`name`='Invite Your Team Members & Subcontractors', `description` = 'If a Team Member already has a Contractor+ account, they will get an invite to join your business workspace. If they don’t have an account, we’ll send them an invite to create an account and join your workspace.' WHERE code_name = 'add_team_members';

insert into `tblonboarding_steps_static` (`section_id`, `code_name`, `name`, `description`, `skip_text`, `icon`, `order`, `button_text`,`percentage`,`show_video`) values(3,'invite_sub_contractors','Invite Your Subcontractors & Collaborators','If a Team Member already has a Contractor+ account, they will get an invite to join your business workspace. If they don\'t already have an account, we\'ll send them a Text Message or Email Inviting them to create one.','I’ll Do This Later','invite_sub_contractors.png',8,'Invite Team Member',0,0);

insert into `tblonboarding_steps_static` (`section_id`, `code_name`, `name`, `description`, `skip_text`, `icon`, `order`, `button_text`,`percentage`,`show_video`) values(3,'get_thumbtack_leads','Get Qualified Leads & Bookings From Thumbtack','With Thumbtack you can get your qualified leads and bookings directly inside Contractor+. New to Thumbtack? All Contractor+ members get $100 in free leads when they sign up!','I’ll Do This Later','get_thumbtack_leads.png',9,'Connect Thumbtack',0,0);

insert into `tblonboarding_steps_static` (`section_id`, `code_name`, `name`, `description`, `skip_text`, `icon`, `order`, `button_text`,`percentage`,`show_video`) values(3,'add_in_network_referrals','Get Exclusive & Qualified In-Network Referrals','We have our own In-Network PRO Referral Program that allows you to set your own lead price, quality standards and start making and receiving referrals from other PRO’s in the Contractor+ network. More leads and revenue for {businessname}!','I’ll Do This Later','add_in_network_referrals.png',10,'Configure In-Network Referrals',0,0);

insert into `tblonboarding_steps_static` (`section_id`, `code_name`, `name`, `description`, `skip_text`, `icon`, `order`, `button_text`,`percentage`,`show_video`) values(3,'add_reviews','Leave Us A Review & Get a $45!','Take 5 minutes to leave us a review at G2 Crowd and Capterra & we’ll give you $45 in Amazon gift cards for your time!','I don’t want $45','add_reviews.png',11,'Review Us On G2 Crowd',0,0);

insert into `tblonboarding_steps_static` (`section_id`, `code_name`, `name`, `description`, `skip_text`, `icon`, `order`, `button_text`,`percentage`,`show_video`) values(3,'join_mindset_monday','Join us for Growth Mindset Monday','Every Monday at 12:30 PM ET we host a 30 minute discussion on growing your contractor business with Gerritt Bake of American Contractor Network.','No thanks','join_mindset_monday.png',12,'Add To Calendar',0,0);


UPDATE `tblonboarding_steps_static` SET card_image = CONCAT(code_name,'.svg');

ALTER TABLE tblonboarding_steps_static ADD is_active TINYINT(1) NOT NULL DEFAULT 1;

ALTER TABLE tbltenants ADD where_you_came_from varchar(100) DEFAULT NULL AFTER industry;
ALTER TABLE tbltenants ADD where_you_came_from_details varchar(500) DEFAULT NULL AFTER industry;

UPDATE `tblonboarding_steps_static` SET is_active = 0 WHERE code_name IN ('cp_grow_business','cp_relationship_management','invite_sub_contractors','join_mindset_monday','sms_phone_settings_config','add_in_network_referrals');

UPDATE tblonboarding_steps_static SET  `card_image`= 'create_estimates.png',`show_video`= 1,`video_link` = 'https://www.youtube.com/embed/EiFYfliUkGI?&enablejsapi=1' WHERE code_name = 'create_estimates';

UPDATE tblonboarding_steps_static SET `card_image`= 'business_info.png',`show_video`= 1,`video_link` = 'https://www.youtube.com/embed/Un0zyX2Gqx8?&enablejsapi=1' WHERE code_name = 'business_info';

UPDATE tblonboarding_steps_static SET `card_image`= 'get_thumbtack_leads.png',`show_video`= 1,`video_link` = 'https://www.youtube.com/embed/LLMoh4_xhEQ?&enablejsapi=1' WHERE code_name = 'get_thumbtack_leads';

UPDATE tblonboarding_steps_static SET `card_image`= 'config_client_review.png',`show_video`= 1,`video_link` = 'https://www.youtube.com/embed/h0ZCXaO6T3c?&enablejsapi=1' WHERE code_name = 'config_client_review';

UPDATE tblonboarding_steps_static SET `card_image`= 'offer_consumer_financing.png',`show_video`= 1,`video_link` = 'https://www.youtube.com/embed/6JIhG2wim44?&enablejsapi=1' WHERE code_name = 'offer_consumer_financing';

UPDATE tblonboarding_steps_static SET `card_image`= 'config_zapier_api.png' WHERE code_name = 'config_zapier_api';
UPDATE tblonboarding_steps_static SET `card_image`= 'configure_payment_methods.png' WHERE code_name = 'configure_payment_methods';


-- Delete Old Cards

DELETE FROM  `tbltenant_onboarding_steps` WHERE onboarding_steps_static_id IN (SELECT id FROM tblonboarding_steps_static WHERE  code_name in ('select_industry', 'add_profile_picture','claim_your_business_listing','add_service_areas','select_service_categories','add_work_hours','add_tools','add_clients','next_insurance','simply_business','grandfather_campaign','print_shop'));


DELETE FROM  tblonboarding_steps_static WHERE  code_name in ('select_industry', 'add_profile_picture','claim_your_business_listing','add_service_areas','select_service_categories','add_work_hours','add_tools','add_clients','next_insurance','simply_business','grandfather_campaign','print_shop');


-- Insert Newly Added Cards to tenant Card Make Sure id
INSERT INTO tbltenant_onboarding_steps (tenant_id, onboarding_steps_static_id, STATUS,added_on,updated_on)
SELECT t.id AS tenant_id,os.id AS onboarding_steps_static_id, 0 AS STATUS, NOW() AS added_on , NOW() AS updated_on FROM tbltenants AS t 
JOIN tblonboarding_steps_static AS os ON 1=1 WHERE os.id>25 ORDER BY t.id , os.id ASC ;



-- Run These Queries through terminal It may take time to execute
UPDATE `tbltenant_onboarding_steps` SET STATUS = 1 
 WHERE  onboarding_steps_static_id = (SELECT id FROM tblonboarding_steps_static WHERE code_name = 'add_social_media') AND  tenant_id IN 
(SELECT op.tenant_id
FROM tblsocial_media_links_static sml
JOIN tbloptions op ON sml.codename = op.name COLLATE utf8_unicode_ci AND IFNULL(op.value,"") != ""
GROUP BY op.tenant_id);


UPDATE `tbltenant_onboarding_steps` SET STATUS = 1 
 WHERE  onboarding_steps_static_id = (SELECT id FROM tblonboarding_steps_static WHERE code_name = 'config_client_review') AND  tenant_id IN 
(SELECT tenant_id FROM tbloptions WHERE NAME = 'business_review' AND VALUE LIKE '%"enable_business_review":"1"%');

UPDATE `tbltenant_onboarding_steps` SET STATUS = 1 
 WHERE  onboarding_steps_static_id = (SELECT id FROM tblonboarding_steps_static WHERE code_name = 'create_invoices') AND  tenant_id IN 
(SELECT tenant_id FROM tblinvoices GROUP BY tenant_id);

UPDATE `tbltenant_onboarding_steps` SET STATUS = 1 
 WHERE  onboarding_steps_static_id = (SELECT id FROM tblonboarding_steps_static WHERE code_name = 'create_jobs') AND  tenant_id IN 
(SELECT tenant_id FROM tbljobs GROUP BY tenant_id);

UPDATE `tbltenant_onboarding_steps` SET STATUS = 1 
 WHERE  onboarding_steps_static_id = (SELECT id FROM tblonboarding_steps_static WHERE code_name = 'track_time_log') AND  tenant_id IN 
(SELECT tenant_id FROM tbltaskstimers GROUP BY tenant_id);


UPDATE `tbltenant_onboarding_steps` SET STATUS = 1 
 WHERE  onboarding_steps_static_id = (SELECT id FROM tblonboarding_steps_static WHERE code_name = 'add_line_item_template') AND  tenant_id IN 
(SELECT tenant_id FROM tblitems GROUP BY tenant_id);


UPDATE `tbltenant_onboarding_steps` SET STATUS = 1 
WHERE  onboarding_steps_static_id = (SELECT id FROM tblonboarding_steps_static WHERE code_name = 'sms_phone_settings_config') AND  tenant_id IN 
(SELECT tenant_id FROM tbloptions WHERE NAME = 'send_sms_via' AND VALUE = 'api');


UPDATE `tbltenant_onboarding_steps` SET STATUS = 1 
 WHERE  onboarding_steps_static_id = (SELECT id FROM tblonboarding_steps_static WHERE code_name = 'offer_consumer_financing') AND  tenant_id IN 
(SELECT tenant_id FROM tbloptions WHERE NAME= 'wisetack_status' AND (IFNULL(VALUE,'') != '' AND VALUE !='PENDING'));


UPDATE `tbltenant_onboarding_steps` SET STATUS = 1 
 WHERE  onboarding_steps_static_id = (SELECT id FROM tblonboarding_steps_static WHERE code_name = 'import_data') AND  tenant_id IN 
(SELECT tenant_id FROM tblimport_data GROUP BY tenant_id);


UPDATE `tbltenant_onboarding_steps` SET STATUS = 1 
 WHERE  onboarding_steps_static_id = (SELECT id FROM tblonboarding_steps_static WHERE code_name = 'add_roles') AND  tenant_id IN 
(SELECT tenant_id FROM tblroles WHERE NAME != "Default" GROUP BY tenant_id);

UPDATE `tbltenant_onboarding_steps` SET STATUS = 1 
 WHERE  onboarding_steps_static_id = (SELECT id FROM tblonboarding_steps_static WHERE code_name = 'get_thumbtack_leads') AND  tenant_id IN 
(SELECT tenant_id FROM tbltenant_thumbtack_mapping GROUP BY tenant_id);





COMMIT;





