START TRANSACTION;

CREATE TABLE `tblusers` (
  `id` int(50) UNSIGNED NOT NULL,
  `email` varchar(255) NOT NULL,
  `password` varchar(100) DEFAULT NULL,
  `token` VARCHAR(250) DEFAULT NULL,
  `first_name` varchar(150) DEFAULT NULL,
  `last_name` varchar(150) DEFAULT NULL,
  `phonenumber_code` varchar(10) DEFAULT NULL,
  `phonenumber` varchar(20) DEFAULT NULL,
  `profile_image` varchar(255) DEFAULT NULL,
  `referral_code` varchar(255) DEFAULT NULL,
  `profile_step` int(1) NOT NULL DEFAULT 1,
  `signup_step` int(1) NOT NULL DEFAULT 0,
  `old_signup` int(1) NOT NULL DEFAULT 0,
  `hear_about_us` varchar(255) DEFAULT NULL,
  `from_referral` varchar(255) DEFAULT NULL,
  `google_login_used` int(1) NOT NULL DEFAULT 0,
  `google_data` varchar(255) DEFAULT NULL,
  `facebook_login_used` int(1) NOT NULL DEFAULT 0,
  `facebook_data` varchar(255) DEFAULT NULL,
  `apple_login_used` int(1) NOT NULL DEFAULT 0,
  `apple_data` varchar(255) DEFAULT NULL,
  `last_password_change` timestamp NULL DEFAULT NULL,
  `date_created` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

ALTER TABLE `tblusers` ADD PRIMARY KEY (`id`), ADD KEY `email` (`email`);

ALTER TABLE `tblusers` MODIFY `id` int(50) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `tbltenants` DROP INDEX `email`, ADD INDEX `email` (`email`) USING BTREE;

ALTER TABLE `tblstaff` ADD `user_id` INT(50) UNSIGNED NULL AFTER `staffid`;
CREATE INDEX user_id ON tblstaff (user_id);

ALTER TABLE `tblusers` ADD `device_id` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL AFTER `last_password_change`, ADD `fcm_token` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL AFTER `device_id`, ADD `device_info` TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL AFTER `fcm_token`;
ALTER TABLE `tblusers` ADD `mobile_type` ENUM('android', 'ios', 'web') NOT NULL AFTER `device_info`;

CREATE TABLE `tbltenant_service_categories` (
  `id` int(60) NOT NULL,
  `tenant_id` int(60) NOT NULL,
  `category_name` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; 

ALTER TABLE `tbltenant_service_categories`
  ADD PRIMARY KEY (`id`),
  ADD KEY `tenant_id` (`tenant_id`);

ALTER TABLE `tbltenant_service_categories`
  MODIFY `id` int(60) NOT NULL AUTO_INCREMENT;



ALTER TABLE `tblusers` 
  ADD COLUMN is_email_verified tinyint(1) DEFAULT 0 AFTER email,
  ADD COLUMN is_mobile_verified tinyint(1) DEFAULT 0 AFTER phonenumber;

UPDATE `tblservice_categories` SET is_top_category = 0 WHERE is_top_category = 1;

UPDATE `tblservice_categories` SET is_top_category = 1 WHERE id IN (402,40,1016,26,2,59,51,16,35,50,57,13,45);

ALTER TABLE tblstaff_app_usage_data ADD loginvia_ipaddr varchar(40) DEFAULT NULL;

UPDATE `tblapad_emailtemplates` SET `message` = '<h3>Join your team on Contractor+</h3><span style="font-size: 12pt;">{staff_name} ({staff_email}) has invited you to use Contractor+ with them, in a workspace called <b>{company_name}.</b></span><br/><br/><div class="o_col_i" style="vertical-align: top;"><div class="o_px-xs" style="padding-left: 8px;padding-right: 8px;"><table align="center" cellspacing="0" cellpadding="0" border="0" role="presentation"><tbody><tr><td class="o_btn o_bg-primary o_br o_heading o_text" align="center" style="font-family: Helvetica, Arial, sans-serif;font-weight: bold;margin-top: 0px;margin-bottom: 0px;font-size: 16px;line-height: 24px;mso-padding-alt: 12px 24px;background-color: #C81E1E;border-radius: 4px;"><a class="o_text-white" href="{invite_url}" style="color: #ffffff !important;text-decoration: none;outline: none;color: #ffffff;display: block;padding: 12px 24px;mso-text-raise: 3px;">JOIN NOW</a></td></tr></tbody></table></div></div><br/><span style="font-size: 12pt;"><b>What is Contractor+?</b></span><br/><span style="font-size: 12pt;">Contractor+ is a complete field service management app Solution that helps you manage your client relationships and work productively as a team. All your estimates, invoices, client contracts, post-inspections, and everything else you need to run your business is accessible in one dashboard like it should be. <a href="{cp_website}">Learn More About Contractor+.</a></span>' WHERE `tblapad_emailtemplates`.`slug` = 'invite-team-member';

CREATE TABLE `tblsignup_deleted_tenants` (
   `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
   `tenant_id` BIGINT(20) UNSIGNED NOT NULL,
   `email` VARCHAR(100) DEFAULT NULL,
   `is_email_verified` TINYINT(1) DEFAULT 0,
   `first_name` VARCHAR(255) DEFAULT NULL,
   `last_name` VARCHAR(255) DEFAULT NULL,
   `phonenumber` VARCHAR(30) DEFAULT NULL,
   `phonenumber_code` VARCHAR(6) DEFAULT '+1',
   `is_mobile_verified` TINYINT(1) DEFAULT 0,
   `company` VARCHAR(255) DEFAULT NULL,
   `signup_state` TINYINT(1) DEFAULT 0,
   `pro_plan_id` BIGINT(20) DEFAULT NULL,
   `google_login_used` INT(1) NOT NULL DEFAULT 0,
   `google_data` VARCHAR(255) DEFAULT NULL,
   `facebook_login_used` INT(1) NOT NULL DEFAULT 0,
   `facebook_data` VARCHAR(255) DEFAULT NULL,
   `apple_login_used` INT(1) NOT NULL DEFAULT 0,
   `apple_data` VARCHAR(255) DEFAULT NULL,
   `last_used` DATETIME DEFAULT NULL,
   `added_on` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  KEY `tenant_id` (`tenant_id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

COMMIT;