CREATE TABLE `tblcoupons`(
    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `coupon_valid_for` ENUM('plan', 'coins', 'bluetooth_tag') NOT NULL DEFAULT 'plan',
    `name` VARCHAR(191) NOT NULL,
    `description` TEXT NULL DEFAULT NULL,
    `code` VARCHAR(191) NOT NULL,
    `value_type` ENUM('percent', 'amount') NOT NULL,
    `value` DOUBLE(8, 2) NOT NULL DEFAULT '0.00',
    `start_date` DATE NULL DEFAULT NULL,
    `end_date` DATE NULL DEFAULT NULL,
    `total_limit_of_coupon_usage` INT(10) NULL DEFAULT NULL,
    `is_specific` TINYINT(1) NOT NULL DEFAULT '0',
    `is_active` TINYINT(1) NOT NULL DEFAULT '1',
    `dateadded` DATETIME NOT NULL,
    PRIMARY KEY(`id`)
) ENGINE = INNODB;

CREATE TABLE `tblcoupon_valid_for`(
    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `coupon_id` BIGINT(20) UNSIGNED NOT NULL,
    `type` ENUM('tenant', 'plan') NULL DEFAULT NULL,
    `type_id` BIGINT(20) UNSIGNED NOT NULL,
    `allowed_for_plan_frequency` ENUM('monthly', 'yearly') NULL DEFAULT NULL,
    PRIMARY KEY(`id`)
) ENGINE = INNODB;

CREATE TABLE `tbltenant_plan_information`(
    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `tenant_id` BIGINT(20) UNSIGNED NOT NULL,
    `newreg_pricingpg_redirect_plan_id` BIGINT(20) UNSIGNED NULL DEFAULT NULL,
    `newreg_pricingpg_redirect_plan_purchase` TINYINT(1) NOT NULL DEFAULT '0',
    `is_plan_expired` TINYINT(1) NOT NULL DEFAULT '0',
    `plan_change_description` TEXT NULL DEFAULT NULL,
    `plans_id` BIGINT(20) UNSIGNED NOT NULL,
    `plan_purchase_frequency` ENUM('monthly', 'yearly') NULL DEFAULT NULL,
    `plan_monthly_price` DECIMAL(10, 2) NULL DEFAULT NULL,
    `plan_yearly_price` DECIMAL(10, 2) NULL DEFAULT NULL,
    `plan_monthly_to_yearly_discount` DECIMAL(10, 2) NULL DEFAULT NULL,
    `custom_plan_no_of_staff_allowed` int(11) NULL DEFAULT NULL,
    `custom_plan_purchase_frequency` ENUM('monthly', 'yearly') NULL DEFAULT NULL,
    `custom_plan_monthly_price` DECIMAL(10, 2) NULL DEFAULT NULL,
    `custom_plan_yearly_price` DECIMAL(10, 2) NULL DEFAULT NULL,
    `custom_plan_monthly_to_yearly_discount` DECIMAL(10, 2) NULL DEFAULT NULL,
    `plan_change_calculation_logs` TEXT NULL DEFAULT NULL,
    `plan_start_date` DATE NULL DEFAULT NULL,
    `last_subscription_paid_date` DATE NULL DEFAULT NULL,
    `next_subscription_date` DATE NULL DEFAULT NULL,
    `is_promotional_free_period_applicable` TINYINT(1) NOT NULL DEFAULT '0',
    `free_promotional_period_end_date` DATE NULL DEFAULT NULL,
    `coupon_code` VARCHAR(191) NULL DEFAULT NULL,
    `coupon_calculated_discount_amount` DECIMAL(10, 2) NULL DEFAULT NULL,
    `coupon_allowed_for_plan_frequency` DECIMAL(10, 2) NULL DEFAULT NULL,
    `final_plan_monthly_price` DECIMAL(10, 2) NULL DEFAULT NULL,
    `final_plan_yearly_price` DECIMAL(10, 2) NULL DEFAULT NULL,
    PRIMARY KEY(`id`)
) ENGINE = INNODB;

CREATE TABLE `tbltenant_plan_changes_history`(
    `uniquecode` VARCHAR(191) NOT NULL,
    `tenant_id` BIGINT(20) UNSIGNED NOT NULL,
    `is_plan_expired` TINYINT(1) NOT NULL DEFAULT '0',
    `expiry_description_logic` TEXT NULL DEFAULT NULL,
    `plan_change_description` TEXT NULL DEFAULT NULL,
    `plans_id` BIGINT(20) UNSIGNED NOT NULL,
    `plan_purchase_frequency` ENUM('monthly', 'yearly') NULL DEFAULT NULL,
    `plan_monthly_price` DECIMAL(10, 2) NULL DEFAULT NULL,
    `plan_yearly_price` DECIMAL(10, 2) NULL DEFAULT NULL,
    `plan_monthly_to_yearly_discount` DECIMAL(10, 2) NULL DEFAULT NULL,
    `custom_plan_no_of_staff_allowed` int(11) NULL DEFAULT NULL,
    `custom_plan_purchase_frequency` ENUM('monthly', 'yearly') NULL DEFAULT NULL,
    `custom_plan_monthly_price` DECIMAL(10, 2) NULL DEFAULT NULL,
    `custom_plan_yearly_price` DECIMAL(10, 2) NULL DEFAULT NULL,
    `custom_plan_monthly_to_yearly_discount` DECIMAL(10, 2) NULL DEFAULT NULL,
    `plan_change_calculation_logs` TEXT NULL DEFAULT NULL,
    `plan_start_date` DATE NULL DEFAULT NULL,
    `last_subscription_paid_date` DATE NULL DEFAULT NULL,
    `next_subscription_date` DATE NULL DEFAULT NULL,
    `is_promotional_free_period_applicable` TINYINT(1) NOT NULL DEFAULT '0',
    `free_promotional_period_end_date` DATE NULL DEFAULT NULL,
    PRIMARY KEY(`uniquecode`)
) ENGINE = INNODB;

CREATE TABLE `tbltenant_plan_subscription_changes_history`(
    `uniquecode` VARCHAR(191) NOT NULL,
    `tenant_plan_changes_history_uniquecode` VARCHAR(191) NOT NULL,
    `subscription_start_date` DATE NULL DEFAULT NULL,
    `subscription_end_date` DATE NULL DEFAULT NULL,
    `subscription_amount` DECIMAL(10, 2) NULL DEFAULT NULL,
    `is_same_hereafter` TINYINT(1) NOT NULL DEFAULT '0',
    PRIMARY KEY(`uniquecode`)
) ENGINE = INNODB;


CREATE TABLE `tbltenant_subscription_payments`(
    `uniquecode` VARCHAR(191) NOT NULL,
    `tenant_plan_sub_chngs_his_uniquecode` VARCHAR(191) NULL DEFAULT NULL,
    `description` TEXT NULL DEFAULT NULL,
    `paymentdate` DATE NULL DEFAULT NULL,
    `amount` DECIMAL(10, 2) NULL DEFAULT NULL,
    `callbackdata` TEXT NULL DEFAULT NULL,
    `status` ENUM('completed', 'failed') NULL DEFAULT NULL,
    `mode` ENUM('subscription_callback', 'direct_via_web') NULL DEFAULT NULL,
    `coupon_code` VARCHAR(191) NULL DEFAULT NULL,
    `coupon_calculated_discount_amount` DECIMAL(10, 2) NULL DEFAULT NULL,
    `coupon_allowed_for_plan_frequency` DECIMAL(10, 2) NULL DEFAULT NULL,
    `final_plan_monthly_price` DECIMAL(10, 2) NULL DEFAULT NULL,
    `final_plan_yearly_price` DECIMAL(10, 2) NULL DEFAULT NULL,
    `newregplan_failpay_tenant_id` BIGINT(20) UNSIGNED NULL DEFAULT NULL,
    `newregplan_failpay_plans_id` BIGINT(20) UNSIGNED NULL DEFAULT NULL,
    `newregplan_failpay_plan_purchase_frequency` ENUM('monthly', 'yearly') NULL DEFAULT NULL,
    `newregplan_failpay_plan_monthly_price` DECIMAL(10, 2) NULL DEFAULT NULL,
    `newregplan_failpay_plan_yearly_price` DECIMAL(10, 2) NULL DEFAULT NULL,
    `newregplan_failpay_plan_monthly_to_yearly_discount` DECIMAL(10, 2) NULL DEFAULT NULL,
    PRIMARY KEY(`uniquecode`)
) ENGINE = INNODB;


ALTER TABLE `tblplan_sets` 
ADD `is_promotional` TINYINT(1) NOT NULL DEFAULT '0' AFTER `is_active`;

ALTER TABLE `tblplans` 
ADD `is_base_plan_of_set` TINYINT NOT NULL AFTER `discount`, 
ADD `base_plan_frequency` ENUM('monthly','yearly') NOT NULL AFTER `is_base_plan_of_set`, 
ADD `is_custom_plan` TINYINT(1) NOT NULL DEFAULT '0' AFTER `base_plan_frequency`;

ALTER TABLE `tblplans` CHANGE `discount` `plan_monthly_to_yearly_discount` DECIMAL(10,0) NOT NULL;
ALTER TABLE `tblplans` ADD `is_popular` TINYINT(1) NOT NULL DEFAULT '0' AFTER `is_custom_plan`;
ALTER TABLE `tblplans` ADD `stripe_data` TEXT NULL DEFAULT NULL AFTER `is_popular`;
ALTER TABLE `tbltenants` ADD `stripe_customer_id` VARCHAR(191) NULL DEFAULT NULL AFTER `custom_plan_monthly_to_yearly_discount`;
ALTER TABLE `tblcoupons` ADD `stripe_coupon_id` VARCHAR(191) NOT NULL AFTER `is_active`;

INSERT INTO `tblcoupons` (`id`, `coupon_valid_for`, `name`, `description`, `code`, `value_type`, `value`, `start_date`, `end_date`, `total_limit_of_coupon_usage`, `is_specific`, `is_active`, `stripe_coupon_id`, `dateadded`) VALUES (NULL, 'plan', 'DISCOUNT 20', NULL, 'DISCOUNT20', 'percent', '20', NULL, NULL, NULL, '0', '1', '8dqwAzZg', '2020-08-14 10:14:21');
ALTER TABLE `tbltenant_plan_information` ADD `stripe_subscription_id` VARCHAR(191) NULL DEFAULT NULL AFTER `final_plan_yearly_price`;
ALTER TABLE `tbltenant_plan_changes_history` ADD `stripe_subscription_id` VARCHAR(191) NULL DEFAULT NULL AFTER `free_promotional_period_end_date`;

ALTER TABLE `tblplan_sets` DROP INDEX `is_active`;