
CREATE TABLE `tblplans` (
  `id` int(11) NOT NULL,
  `plan_sets_id` int(11) NOT NULL,
  `name` varchar(191) NOT NULL,
  `codename` varchar(191) NOT NULL,
  `description` text NOT NULL,
  `monthly_price` decimal(10,0) NOT NULL,
  `yearly_price` decimal(10,0) NOT NULL,
  `discount` decimal(10,0) NOT NULL,
  `added_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE `tblplan_features` (
  `id` int(11) NOT NULL,
  `name` varchar(191) NOT NULL,
  `codename` varchar(191) NOT NULL,
  `description` text NOT NULL,
  `type` enum('allow','restrict','custom') NOT NULL,
  `added_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE `tblplan_features_value` (
  `id` int(11) NOT NULL,
  `plans_id` int(11) NOT NULL,
  `plans_features_id` int(11) NOT NULL,
  `value` varchar(191) NOT NULL,
  `added_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE `tblplan_sets` (
  `id` int(11) NOT NULL,
  `plan_set_name` varchar(191) NOT NULL,
  `plan_set_description` text,
  `start_date` datetime DEFAULT NULL,
  `end_date` datetime DEFAULT NULL,
  `reason_for_switching_plan` text,
  `is_active` tinyint(1) DEFAULT NULL,
  `added_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `tblplans`
  ADD PRIMARY KEY (`id`),
  ADD KEY `plan_sets_id` (`plan_sets_id`);

ALTER TABLE `tblplan_features`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `tblplan_features_value`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `tblplan_sets`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `is_active` (`is_active`);

ALTER TABLE `tblplans`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;

ALTER TABLE `tblplan_features`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `tblplan_features_value`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `tblplan_sets`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

-
ALTER TABLE `tblplans`
  ADD CONSTRAINT `tblplans_ibfk_1` FOREIGN KEY (`plan_sets_id`) REFERENCES `tblplan_sets` (`id`) ON DELETE NO ACTION ON UPDATE CASCADE;
COMMIT;



ALTER TABLE `tbltenants` ADD `plan_purchase_frequency` ENUM('monthly','yearly') NULL DEFAULT NULL AFTER `pro_plan_id`, ADD `plan_monthly_price` DECIMAL(10,2) NULL DEFAULT NULL AFTER `plan_purchase_frequency`, ADD `plan_yearly_price` DECIMAL(10,2) NULL DEFAULT NULL AFTER `plan_monthly_price`, ADD `plan_monthly_to_yearly_discount` DECIMAL(10,2) NULL DEFAULT NULL AFTER `plan_yearly_price`, ADD `custom_plan_no_of_staff_allowed` INT(11) NULL DEFAULT NULL AFTER `plan_monthly_to_yearly_discount`, ADD `custom_plan_monthly_price` DECIMAL(10,2) NULL DEFAULT NULL AFTER `custom_plan_no_of_staff_allowed`, ADD `custom_plan_yearly_price` DECIMAL(10,2) NULL DEFAULT NULL AFTER `custom_plan_monthly_price`, ADD `custom_plan_monthly_to_yearly_discount` DECIMAL(10,2) NULL DEFAULT NULL AFTER `custom_plan_yearly_price`;
