ALTER TABLE `tbltenant_plan_information` ADD `charge_bee_customer_id` VARCHAR(500) NULL DEFAULT NULL AFTER `apple_subscription_id`;
ALTER TABLE `tbltenant_plan_information` ADD `charge_bee_subscription_id` VARCHAR(500) NULL DEFAULT NULL AFTER `charge_bee_customer_id`;
ALTER TABLE `tbltenant_plan_information` ADD `updated_on` DATETIME NULL DEFAULT NULL;
ALTER TABLE `tbltenant_plan_information` ADD `number_of_users_allowed` INT NULL DEFAULT '1' AFTER `charge_bee_subscription_id`;

ALTER TABLE `tbltenant_plan_changes_history` ADD `charge_bee_subscription_id` VARCHAR(500) NULL DEFAULT NULL AFTER `apple_subscription_id`;
ALTER TABLE `tbltenant_plan_changes_history` ADD `updated_on` DATETIME NULL DEFAULT NULL;
ALTER TABLE `tbltenant_plan_changes_history` ADD `number_of_users_allowed` INT DEFAULT 1 AFTER `charge_bee_subscription_id`;

ALTER TABLE tblplans ADD charge_bee_data LONGTEXT NULL DEFAULT NULL AFTER apple_data;

-- Manually make old plan set as inactive
UPDATE tblplan_sets SET is_active = 0 WHERE id = 7;

INSERT INTO `tblplan_sets` (`id`, `plan_set_name`, `plan_set_description`, `start_date`, `end_date`, `reason_for_switching_plan`, `is_active`, `is_promotional`, `added_on`) VALUES (NULL, 'Pay Per User Model', 'Pay Per User Model', NOW(), '2030-08-01 23:59:59', NULL, '1', '0', NOW());
SET @plan_set_id = LAST_INSERT_ID();

INSERT INTO `tblplans` (`id`, `plan_sets_id`, `name`, `codename`, `description`, `plan_order`, `monthly_price`, `yearly_price`, `plan_monthly_to_yearly_discount`, `is_base_plan_of_set`, `base_plan_frequency`, `is_custom_plan`, `is_popular`, `charge_bee_data`, `stripe_data`, `apple_data`, `plan_promotional_price`, `plan_promotional_free_period_in_days`, `plan_promotional_frequency`, `plan_promotional_monthly_price`, `plan_promotional_yearly_price`, `plan_promotional_monthly_to_yearly_discount`, `plan_promotional_off_percent_in_amount`, `plan_promotional_off_percent_fr_days`, `plan_promotional_off_stripe_coupon`, `added_on`, `icon`) VALUES (NULL, @plan_set_id, 'Basic', 'basic_per_user', 'Perfect For Soloprenuers', '1', '0', '0', '0', '1', 'monthly', '0', '0', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NOW(), 'free.png');
SET @basic_plan_id = LAST_INSERT_ID();
INSERT INTO tblplan_features_value (`plans_id`, `plans_features_id`, `value`, `other_data`, `added_on`) SELECT @basic_plan_id, `plans_features_id`, `value`, `other_data`, NOW() FROM `tblplan_features_value` WHERE plans_id = 9;

INSERT INTO `tblplans` (`id`, `plan_sets_id`, `name`, `codename`, `description`, `plan_order`, `monthly_price`, `yearly_price`, `plan_monthly_to_yearly_discount`, `is_base_plan_of_set`, `base_plan_frequency`, `is_custom_plan`, `is_popular`, `charge_bee_data`,`stripe_data`, `apple_data`, `plan_promotional_price`, `plan_promotional_free_period_in_days`, `plan_promotional_frequency`, `plan_promotional_monthly_price`, `plan_promotional_yearly_price`, `plan_promotional_monthly_to_yearly_discount`, `plan_promotional_off_percent_in_amount`, `plan_promotional_off_percent_fr_days`, `plan_promotional_off_stripe_coupon`, `added_on`, `icon`) VALUES (NULL, @plan_set_id, 'PRO', 'pro_per_user', 'Best For Small Teams', '2', '29', '14', '0', '0', 'monthly', '0', '0', '{"product_id":"PRO","monthly_price_id":"PRO-USD-Monthly","yearly_price_id":"PRO-USD-Yearly"}', NULL, '[{"quantity":"1","yearly_product_apple_id":"6445425359","yearly_product_code":"professional_1user_yearly","yearly_product_price":"169.99","yearly_amount_saved":"","monthly_product_apple_id":"6445425549","monthly_product_code":"professional_1user_monthly","monthly_product_price":"29.99","ten_percent_yearly_product_code":"","ten_percent_monthly_product_code":"","twenty_percent_yearly_product_code":"","twenty_percent_monthly_product_code":""},{"quantity":"2","yearly_product_apple_id":"6445425868","yearly_product_code":"professional_2users_yearly","yearly_product_price":"339.99","yearly_amount_saved":"","monthly_product_apple_id":"6445426091","monthly_product_code":"professional_2users_monthly","monthly_product_price":"59.99","ten_percent_yearly_product_code":"","ten_percent_monthly_product_code":"","twenty_percent_yearly_product_code":"","twenty_percent_monthly_product_code":""},{"quantity":"3","yearly_product_apple_id":"6445426595","yearly_product_code":"professional_3users_yearly","yearly_product_price":"509.99","yearly_amount_saved":"","monthly_product_apple_id":"6445426200","monthly_product_code":"professional_3users_monthly","monthly_product_price":"89.99","ten_percent_yearly_product_code":"","ten_percent_monthly_product_code":"","twenty_percent_yearly_product_code":"","twenty_percent_monthly_product_code":""},{"quantity":"4","yearly_product_apple_id":"6445426441","yearly_product_code":"professional_4users_yearly","yearly_product_price":"679.99","yearly_amount_saved":"","monthly_product_apple_id":"6445426518","monthly_product_code":"professional_4users_monthly","monthly_product_price":"116.99","ten_percent_yearly_product_code":"","ten_percent_monthly_product_code":"","twenty_percent_yearly_product_code":"","twenty_percent_monthly_product_code":""},{"quantity":"5","yearly_product_apple_id":"6445426970","yearly_product_code":"professional_5users_yearly","yearly_product_price":"839.99","yearly_amount_saved":"","monthly_product_apple_id":"6445426501","monthly_product_code":"professional_5users_monthly","monthly_product_price":"149.99","ten_percent_yearly_product_code":"","ten_percent_monthly_product_code":"","twenty_percent_yearly_product_code":"","twenty_percent_monthly_product_code":""},{"quantity":"10","yearly_product_apple_id":"","yearly_product_code":"","yearly_product_price":"","yearly_amount_saved":"","monthly_product_apple_id":"6445426975","monthly_product_code":"professional_10users_monthly","monthly_product_price":"294.99","ten_percent_yearly_product_code":"","ten_percent_monthly_product_code":"","twenty_percent_yearly_product_code":"","twenty_percent_monthly_product_code":""}]', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NOW(), 'pro.png');
SET @pro_plan_id = LAST_INSERT_ID();
INSERT INTO tblplan_features_value (`plans_id`, `plans_features_id`, `value`, `other_data`, `added_on`) SELECT @pro_plan_id, `plans_features_id`, `value`, `other_data`, NOW() FROM `tblplan_features_value` WHERE plans_id = 10;

INSERT INTO `tblplans` (`id`, `plan_sets_id`, `name`, `codename`, `description`, `plan_order`, `monthly_price`, `yearly_price`, `plan_monthly_to_yearly_discount`, `is_base_plan_of_set`, `base_plan_frequency`, `is_custom_plan`, `is_popular`, `charge_bee_data`,`stripe_data`, `apple_data`, `plan_promotional_price`, `plan_promotional_free_period_in_days`, `plan_promotional_frequency`, `plan_promotional_monthly_price`, `plan_promotional_yearly_price`, `plan_promotional_monthly_to_yearly_discount`, `plan_promotional_off_percent_in_amount`, `plan_promotional_off_percent_fr_days`, `plan_promotional_off_stripe_coupon`, `added_on`, `icon`) VALUES (NULL, @plan_set_id, 'Ultimate', 'ultimate_per_user', 'For Teams That Need It ALL!', '3', '49', '29', '0', '0', 'monthly', '0', '0', '{"product_id":"Ultimate","monthly_price_id":"Ultimate-USD-Monthly","yearly_price_id":"Ultimate-USD-Yearly"}', NULL, '[{"quantity":"1","yearly_product_apple_id":"6445425379","yearly_product_code":"ultimate_1user_yearly","yearly_product_price":"349.99","yearly_amount_saved":"","monthly_product_apple_id":"6445425299","monthly_product_code":"ultimate_1user_monthly","monthly_product_price":"49.99","ten_percent_yearly_product_code":"","ten_percent_monthly_product_code":"","twenty_percent_yearly_product_code":"","twenty_percent_monthly_product_code":""},{"quantity":"2","yearly_product_apple_id":"6445425934","yearly_product_code":"ultimate_2users_yearly","yearly_product_price":"699.99","yearly_amount_saved":"","monthly_product_apple_id":"6445426065","monthly_product_code":"ultimate_2users_monthly","monthly_product_price":"99.99","ten_percent_yearly_product_code":"","ten_percent_monthly_product_code":"","twenty_percent_yearly_product_code":"","twenty_percent_monthly_product_code":""},{"quantity":"3","yearly_product_apple_id":"","yearly_product_code":"","yearly_product_price":"","yearly_amount_saved":"","monthly_product_apple_id":"6445426039","monthly_product_code":"ultimate_3users_monthly","monthly_product_price":"149.99","ten_percent_yearly_product_code":"","ten_percent_monthly_product_code":"","twenty_percent_yearly_product_code":"","twenty_percent_monthly_product_code":""},{"quantity":"4","yearly_product_apple_id":"","yearly_product_code":"","yearly_product_price":"","yearly_amount_saved":"","monthly_product_apple_id":"6445426338","monthly_product_code":"ultimate_4users_monthly","monthly_product_price":"199.99","ten_percent_yearly_product_code":"","ten_percent_monthly_product_code":"","twenty_percent_yearly_product_code":"","twenty_percent_monthly_product_code":""},{"quantity":"5","yearly_product_apple_id":"","yearly_product_code":"","yearly_product_price":"","yearly_amount_saved":"","monthly_product_apple_id":"6445426839","monthly_product_code":"ultimate_5users_monthly","monthly_product_price":"249.99","ten_percent_yearly_product_code":"","ten_percent_monthly_product_code":"","twenty_percent_yearly_product_code":"","twenty_percent_monthly_product_code":""}]', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NOW(), 'pro_exclusive.png');
SET @ultimate_plan_id = LAST_INSERT_ID();
INSERT INTO tblplan_features_value (`plans_id`, `plans_features_id`, `value`, `other_data`, `added_on`) SELECT @ultimate_plan_id, `plans_features_id`, `value`, `other_data`, NOW() FROM `tblplan_features_value` WHERE plans_id = 12;

ALTER TABLE `tblplan_features` ADD `is_highlight` TINYINT(1) NOT NULL DEFAULT '0' AFTER `type`;
ALTER TABLE `tblplan_features` ADD `order` TINYINT NOT NULL DEFAULT 99 AFTER `is_highlight`;

UPDATE tbltenant_plan_information SET number_of_users_allowed = 1 WHERE plans_id = 9;
UPDATE tbltenant_plan_information SET number_of_users_allowed = 4 WHERE plans_id = 10;
UPDATE tbltenant_plan_information SET number_of_users_allowed = 11 WHERE plans_id = 11;
UPDATE tbltenant_plan_information SET number_of_users_allowed = 31 WHERE plans_id = 12;

UPDATE tblplans SET charge_bee_data = '{"product_id":"Old-Pro-Plan","montly_price_id":"Old-Pro-Plan-USD-Monthly","yearly_price_id":"Old-Pro-Plan-USD-Yearly"}' WHERE id = 10;
UPDATE tblplans SET charge_bee_data = '{"product_id":"Old-Corporate-Plan","montly_price_id":"Old-Corporate-Plan-USD-Monthly","yearly_price_id":"Old-Corporate-Plan-USD-Yearly"}' WHERE id = 11;
UPDATE tblplans SET charge_bee_data = '{"product_id":"Old-Ultimate-Plan","montly_price_id":"Old-Ultimate-Plan-USD-Monthly","yearly_price_id":"Old-Ultimate-Plan-USD-Yearly"}' WHERE id = 12;

CREATE TABLE tblcancellation_attempts(
    id BIGINT PRIMARY KEY AUTO_INCREMENT NOT NULL,    
    tenant_id BIGINT UNSIGNED NOT NULL,
    FOREIGN KEY (tenant_id) REFERENCES tbltenants(id) ON DELETE CASCADE ON UPDATE CASCADE,
    cancellation_reason VARCHAR(255) DEFAULT NULL,
    data TEXT DEFAULT NULL,
    action_taken VARCHAR(255) DEFAULT NULL,
    is_actually_plan_cancelled TINYINT(1) NOT NULL DEFAULT 0,
    added_on DATETIME,
    updated_on DATETIME
);

DELETE FROM `tbltenant_onboarding_steps` WHERE `onboarding_steps_static_id` = 23;
DELETE FROM `tblonboarding_steps_static` WHERE `id` = 23;