-- not applied foreign key 

INSERT INTO `tblapad_options` (`id`, `type`, `name`, `value`, `autoload`) VALUES (NULL, NULL, 'rewardcoins_to_referredby_staff', '100', '1'), (NULL, NULL, 'rewardcoins_to_registrationonreferral', '50', '1');

ALTER TABLE `tbltenants` ADD `referred_by_tenant_id` BIGINT NULL AFTER `pro_plan_id`, ADD `referred_by_tenant_staff_id` BIGINT NULL AFTER `referred_by_tenant_id`, ADD `referral_code_used` VARCHAR(255) NULL AFTER `referred_by_tenant_staff_id`, ADD `rewards_you_earned_from_referral` INT NULL AFTER `referral_code_used`, ADD `rewards_earned_by_referred_staff` INT NULL AFTER `rewards_you_earned_from_referral`, ADD `is_refer_reward_distributed` TINYINT(1) NOT NULL DEFAULT '0' AFTER `rewards_earned_by_referred_staff`;

ALTER TABLE `tbltenants` DROP `referral_code_used`;

ALTER TABLE `tbltenants` ADD `referral_code_used` TINYINT(1) NOT NULL DEFAULT '0' AFTER `referred_by_tenant_staff_id`;

ALTER TABLE `tblstaff` ADD `staff_referral_code` VARCHAR(255) NOT NULL AFTER `fcm_token`;

update tblstaff set staff_referral_code = LPAD(`staffid`, 7, '0')
update tblstaff set staff_referral_code = concat('REFERCP_', `staff_referral_code`)

ALTER TABLE `tblstaff` ADD UNIQUE(`staff_referral_code`);

ALTER TABLE `tbltenants` CHANGE `referral_code_used` `referral_code_used` VARCHAR(255) NULL;

update tbltenants set referral_code_used = LPAD(`id`, 7, '0');
update tbltenants set referral_code_used = concat('REFERCP_', `referral_code_used`);

update tblstaff set staff_referral_code = LPAD(`tenant_id`, 7, '0')
update tblstaff set staff_referral_code = concat('REFERCP_', `staff_referral_code`)

ALTER TABLE `tbltenants` CHANGE `referral_code_used` `referral_code_used` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;

ALTER TABLE `tbltenants` ADD UNIQUE(`referral_code_used`);

ALTER TABLE `tbltenants` CHANGE `referral_code_used` `referral_code_used_staff` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;

update tblstaff set staff_referral_code = LPAD(`staffid`, 7, '0');
update tblstaff set staff_referral_code = concat('REFERCP_', `staff_referral_code`)

ALTER TABLE `tbltenants` DROP INDEX `referral_code_used_staff`;

ALTER TABLE `tbltenants` ADD UNIQUE(`referral_code_used_staff`);

ALTER TABLE `tbltenants` CHANGE `company` `company` VARCHAR(500) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL;
