DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `AddUnilevel`(
	IN i_subscriber_id VARCHAR(100),
	IN i_amount decimal(10,2),
	IN i_transaction_type VARCHAR(20),
	IN i_trackno VARCHAR(30)
	)
BEGIN
	DECLARE v_amount INT UNSIGNED;
	declare v_price decimal unsigned;
	DECLARE v_points DECIMAL UNSIGNED;
	declare v_referral_code_price decimal unsigned;
	DECLARE v_referral_account_type VARCHAR(30);
	DECLARE v_referred_by_id VARCHAR(30);
	DECLARE v_statuserror TINYINT(1);
	DECLARE done INT DEFAULT FALSE;
	DECLARE o_success BOOLEAN;
	DECLARE o_message VARCHAR(255);

	DECLARE EXIT HANDLER FOR SQLEXCEPTION
	BEGIN
	      ROLLBACK;
		SELECT o_success AS `status`,o_message AS message, v_statuserror AS error;
	  END;

	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

	START TRANSACTION;
	-- DECLARE EXIT HANDLER FOR SQLEXCEPTION

	SET o_success = FALSE;
	SET o_message = 'Failed 1 Add Unilevel Points';

	select referred_by into v_referred_by_id from `user_referrals` where subscriber_id = i_subscriber_id;

	SELECT `subscription_code` INTO v_referral_account_type
	FROM user_subscriptions t1
	WHERE t1.`created_at` = (SELECT MAX(t2.`created_at`)
                 FROM user_subscriptions t2
                 WHERE t2.`subscriber_id` = t1.`subscriber_id`)
	AND t1.`subscriber_id` COLLATE utf8mb4_unicode_ci = v_referred_by_id;

	SET o_message = 'Failed 2 Add Unilevel Points';

	if v_referral_account_type = 'master' then

		case
			when i_transaction_type = 'dom_air_travel' then set v_amount = 20;
			WHEN i_transaction_type = 'int_air_travel' THEN  set v_amount = 20;
			WHEN i_transaction_type = 'hotel_transaction' THEN  set v_amount = i_amount * 0.03;
			else set v_amount = 0;
		end case ;
	end if;

	if v_referral_account_type = 'mentor' THEN
		CASE
			WHEN i_transaction_type = 'dom_air_travel' THEN set v_amount = 10;
			WHEN i_transaction_type = 'int_air_travel' THEN  set v_amount = 10;
			WHEN i_transaction_type = 'hotel_transaction' THEN  set v_amount = i_amount * 0.02;
			ELSE set v_amount = 0;
		END case;

	end if;

	select `price` into v_referral_code_price from `subscriptions` where `code` = v_referral_account_type;
	select `points` into v_points from `user_points` where `subscriber_id` = v_referred_by_id;

	IF v_referral_code_price < 9990 or v_amount = 0 then

		SET o_success = TRUE;
		SET o_message = 'Successful';
		SET v_statuserror = 1;

	else
		INSERT INTO `user_point_transactions` (
			`subscriber_id`,
			`reference_number`,
			`tracking_number`,
			`transaction_type`,
			`amount`,
			`balance_before`,
			`balance_after`,
			`type`,
			`status`,
			`created_at`,
			`updated_at`
		)
		VALUES (
			v_referred_by_id,
			CONCAT('REF-',v_referred_by_id,'-',CHAR( FLOOR(65 + (RAND() * 25))),CHAR( FLOOR(65 + (RAND() * 25))),SUBSTR(UNIX_TIMESTAMP(),4,6)),
			i_trackno,
			i_transaction_type,
			v_amount,
			v_points,
			v_points + v_amount,
			2,
			1,
			now(),
			now()
		);


			UPDATE `user_points`
			SET `points` = `points` +  v_amount
			WHERE `subscriber_id` = v_referred_by_id;


	END IF;


	COMMIT;
		SET o_success = TRUE;
		SET o_message = 'Successful';
		SET v_statuserror = 1;
		SELECT o_success AS `status`,o_message AS message, v_statuserror AS error;
END;;
DELIMITER ;