CREATE TABLE IF NOT EXISTS tblotp_verification (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  email varchar(100) DEFAULT NULL,
  mobile_no varchar(10) DEFAULT NULL,
  mobile_no_code varchar(6) DEFAULT '+1'  NULL,
  otp varchar(10) NOT NULL,
  is_verified tinyint(1) DEFAULT 0,
  added_on datetime NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

CREATE TABLE IF NOT EXISTS tbltemp_signup (
  id varchar(100) NOT NULL ,
  email varchar(100) DEFAULT NULL,
  mobile_no varchar(10) DEFAULT NULL,
  mobile_no_code varchar(6) DEFAULT '+1' NULL,
  added_on datetime NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;


Alter table tbltenants 
ADD COLUMN mobile_no_code VARCHAR(6) DEFAULT '+1' NULL AFTER mobile_no,
ADD COLUMN is_email_verified tinyint(1) DEFAULT 0 AFTER mobile_no_code,
ADD COLUMN is_mobile_verified tinyint(1) DEFAULT 0 AFTER is_email_verified;

Alter table tblstaff 
ADD COLUMN phonenumber_code VARCHAR(6) DEFAULT '+1' NULL AFTER phonenumber;

Alter table tbltenants
ADD COLUMN google_business_details longtext DEFAULT NULL AFTER qb_oauth_resp_json;

Alter table tblstaff
ADD COLUMN why_using_cp longtext DEFAULT NULL AFTER how_found_cp;

CREATE TABLE IF NOT EXISTS tblonboarding_steps_static (
  id int unsigned NOT NULL AUTO_INCREMENT,
  code_name varchar(100) DEFAULT NULL,
  name varchar(100) DEFAULT NULL,  
  description varchar(250)  DEFAULT NULL,
  icon varchar(100) DEFAULT NULL,
  `order` tinyint ,
  parent int DEFAULT NULL,
  percentage float(5,2) DEFAULT NULL,
  button_text varchar(100) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS tbltenant_onboarding_steps (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  tenant_id bigint(20) NOT NULL,
  onboarding_steps_static_id int unsigned NOT NULL,
  status tinyint DEFAULT 0,
  added_on datetime NOT NULL,
  updated_on datetime NOT NULL,
  PRIMARY KEY (id) ,
  FOREIGN KEY (onboarding_steps_static_id) REFERENCES tblonboarding_steps_static(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `tblonboarding_steps_static` (`id`, `code_name`, `name`, `description`, `icon`, `order`, `parent`, `percentage`, `button_text`) VALUES
(1, 'business_info', 'Add Your Business Information', 'Add your company name, and business contacts that will be displayed in the header of your Estimates and Invoices.', 'business_info.png', 5, NULL, 15.00, 'Add Your Business Information'),
(2, 'email_verification', 'Email Address Verification', 'Verify your account email so that you’ll get alerts and updates from your clients, your team and Contractor+ App.', 'email_verification.png', 10, NULL, 0.00, 'Send Code To Email'),
(3, 'mobile_verification', 'Phone Number Verification', 'Verify your mobile phone number so that you can send reports to your clients via SMS, and receive important notifications.', 'mobile_verification.png', 15, NULL, 0.00, 'Send Code To Phone Number'),
(4, 'select_industry', 'Select Your Industry', 'Configure the main service industry you are in (i.e. roofing, HVAC,  general contractor, etc.)', 'select_industry.png', 20, NULL, 10.00, 'Select Your Industry'),
(5, 'add_company_logo', 'Add Your Company Logo', 'Upload your high-res logo for display on client Estimates, Invoices and all outgoing emails, or order a new logo!', 'add_company_logo.png', 25, NULL, 12.00, 'Upload Or Order Company Logo'),
(6, 'select_company_size', 'Select Your Company Size', 'Tell us how many employees / team members you have working for your Company.', 'select_company_size.png', 30, NULL, 6.00, 'Update'),
(7, 'why_using_cp', 'Tell Us Why You’re Using Contractor+', 'We like to know what brought you to Contractor+ App, so that we can add better features and services to help you manage and grow your business.', 'why_using_cp.png', 35, NULL, 2.00, 'Tell Us Why?'),
(8, 'add_profile_picture', 'Add Your Profile Picture', 'Your headshot image will be used in Team Chat so your team can identify you. It can also optionally be added to your Estimates and Invoices for your clients to see who prepared their reports.', 'add_profile_picture.png', 40, NULL, 10.00, 'Upload My Profile Picture'),
(9, 'smtp_configuration', 'Configure Send-From Email Address', 'This setting allows you to send your Estimates and Invoices to your clients using your own email address! You can add SMTP info or simply link a Gmail/Google hosted email.', 'smtp_configuration.png', 45, NULL, 12.00, 'Configure My Send-From Email'),
(10, 'add_team_members', 'Add/Invite Your Team Members', 'You can add all your employees under your Company’s account so you can all collaborate and work together.', 'add_team_members.png', 50, NULL, 8.00, 'Add Your Team Members'),
(11, 'quickbook_sync', 'Set Up QuickBooks Sync', 'If you use QuickBooks Online to manage your accounting, you can link your Contractor+ account so all your important financial data stays synced.', 'quickbook_sync.png', 55, NULL, 12.00, 'Link My QuickBooks Account'),
(12, 'configure_payment_methods', 'Configure Your Payment Methods', 'Setup Contractor+ Payments and let your customers know the payment methods you accept. We’ll display these options on your Invoices.', 'configure_payment_methods.png', 60, NULL, 10.00, 'Configure Payment Methods'),
(13, 'claim_your_business_listing', 'Claim Your Company’s Business Listing', 'Claim your free business listing to start getting more leads and customers from the Contractor+ network.', 'claim_your_business_listing.png', 65, NULL, 3.00, 'Claim My Business Listing'),
(14, 'add_service_areas', 'Configure Your Service Area', 'Let us know the areas you service, so that we’ll know where to display your listing to other contractors in the referral network.', 'steps.png', 70, 13, 0.00, 'Add My Service Areas'),
(15, 'select_service_categories', 'Configure Your Service Categories', 'List all the different types of services you provide your customers, so other contractors can find you in our referral network.', 'steps.png', 75, 13, 0.00, 'Add My Service Categories'),
(16, 'add_work_hours', 'Configure Your Business Hours', 'If you have specific hours of operation that you’d like to set up, you can add it here.', 'steps.png', 80, 13, 0.00, 'Add My Hours Of Operation'),
(17, 'insurance_and_license', 'Add Your Insurance & Licenses', 'If you’re licensed and insured, you can add your license and insurance information to show the “Licensed & Insured” badge on your Estimates.', 'insurance_and_license.png', 85, NULL, 0.00, 'Add My License & Insurance Information'),
(18, 'add_tools', 'Add Tools & Equipment', 'If you’d like to track your expensive tools and assets, you can start adding your equipment now!\r\n', 'add_tools.png', 90, NULL, 0.00, 'Add Now'),
(19, 'add_clients', 'Add Your Clients', 'Now it’s time to start adding your clients. You can also import them in bulk in the web dashboard on our website.', 'add_clients.png', 95, NULL, 0.00, 'Start Adding My Clients'),
(20, 'create_estimates', 'Create Your First Estimate', 'Now it’s time to create your first estimate and get familiar with how to send estimates using Contractor+ App.', 'create_estimates.png', 100, NULL, 0.00, 'Create My First Estimate'),
(21, 'print_shop', 'Order Company Apparel', 'Quality t-shirts with your Company’s Logo can help you look more professional to your clients. We’ve got great prices fast turnaround!', 'print_shop.png', 105, NULL, 0.00, 'Visit The Contractor Print Shop');

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 order by t.id , os.id asc ;

ALTER table tblindustry 
ADD COLUMN icon VARCHAR(100);

UPDATE tblindustry SET icon='icon.png';

ALTER table tbltemp_signup 
ADD COLUMN added_on_vm_drop tinyint default 0 after mobile_no_code,
ADD COLUMN added_on_sendgrid tinyint default 0 after added_on_vm_drop;

ALTER TABLE tbltenants 
MODIFY COLUMN email varchar(255) DEFAULT NULL;

Update tbloptions JOIN tblcities ON city_id = value  SET value = city_name where name ='company_city';


INSERT INTO tblapad_emailtemplates (type,slug,language,name,subject,message,fromname,fromemail,plaintext,active,`order`)
VALUES('registration_onboarding_steps','send-otp','english','Send OTP',"Your verification code is {otp}",'<span style="font-size: 12pt;">{otp} is your verification code. Copy and paste this code into Contractor+ App to verify your email. </span><br/><br/>','','',0,1,0)

