-- PROCEDURE i3s_account_transfer
-- This procedure executes a account-to-account transfer
-- Transaction Management https://www.postgresql.org/docs/16/plpgsql-transactions.html
CREATE OR REPLACE PROCEDURE i3s_account_transfer (
	IN pSessionID TEXT,
	IN pFromAccount TEXT, 
	IN pToAccount TEXT, 
	IN pDescript TEXT,
	IN pAmount DECIMAL (15, 2), 
	OUT ok BOOLEAN,
	OUT fromUserID TEXT,
	OUT toUserID TEXT,
	OUT trxID TEXT,
	OUT msg TEXT) 
AS $$
DECLARE
	userID TEXT := NULL;
	rowID BIGINT := NULL;
	dailyLimit DECIMAL (15, 2) := NULL;
	dailyLimitDate DATE := NULL;
	today DATE;
	countGuardian INTEGER;

	t_is_active BOOLEAN;
	t_balance DECIMAL (15, 2);
	t_min_balance DECIMAL (15, 2);
	t_max_balance DECIMAL (15, 2);
	t_daily_limit DECIMAL (15, 2);
	t_daily_limit_date DATE;
	t_daily_limit_used DECIMAL (15, 2);
	t_min_trx_limit DECIMAL (15, 2);
	t_max_trx_limit DECIMAL (15, 2);
	
	-- Transaction fee
	t_trx_fee DECIMAL (15, 2);

BEGIN
	-- Sanitise parameters
	pSessionID := UPPER(TRIM(pSessionID));
	pFromAccount := UPPER(TRIM(pFromAccount));
	pToAccount := UPPER(TRIM(pToAccount));
	pDescript := TRIM(pDescript);

	ok := TRUE;

	-- Accounts must not be nulls
	IF pFromAccount IS NULL THEN
		RAISE NOTICE 'Invalid source account';
		ok := FALSE;
		msg := 'Invalid source account';
		RETURN;
	END IF;
	IF pToAccount IS NULL THEN
		RAISE NOTICE 'Invalid destination account';
		ok := FALSE;
		msg := 'Invalid destination account';
		RETURN;
	END IF;

	-- Do not allow transfer to the same account
	IF pFromAccount = pToAccount THEN
		RAISE NOTICE 'No transfer to the same account';
		ok := FALSE;
		msg := 'No transfer to the same account';
		RETURN;
	END IF;

	-- Retrieve user info using session ID as key
	SELECT user_id INTO userID FROM i3s_user_session WHERE session_id = pSessionID AND is_active;
	IF userID IS NULL THEN
		RAISE NOTICE 'Invalid session ID: %', pSessionID;
		ok := FALSE;
		msg := 'Invalid session ID';
		RETURN;
	END IF;

	-- Get owners of accounts
	SELECT user_id INTO fromUserID FROM i3s_account
		WHERE account_id = pFromAccount;
	IF fromUserID IS NULL THEN
		RAISE NOTICE 'Invalid source account: %', pFromAccount;
		ok := FALSE;
		msg := 'Invalid source account';
		RETURN;
	END IF;

	SELECT user_id INTO toUserID FROM i3s_account
		WHERE account_id = pToAccount;
	IF toUserID IS NULL THEN
		RAISE NOTICE 'Invalid destination account: %', pToAccount;
		ok := FALSE;
		msg := 'Invalid destination account';
		RETURN;
	END IF;

	-- Does session belong to the transferor?
	IF userID <> fromUserID THEN
		RAISE NOTICE 'Session % does not belong to transferor: %', pSessionID, fromUserID;
		ok := FALSE;
		msg := 'Invalid transferror';
		RETURN;
	END IF;

	-- Initialize `ok` to TRUE
	-- This will be used to set the is_success column in i3s_account_trx
	ok := true;

	-- Generates a unique transaction ID
	trxID := i3s_unique_id();

	-- Is it a valid amount?
	IF ok THEN
		IF pAmount <= 0 THEN
			RAISE NOTICE 'Invalid amount: %', pAmount;
			ok := FALSE;
			msg := 'Invalid amount: ' || pAmount;
		END IF;
	END IF;

	-- Verify that the transfer is allowed

	-- Checks if transferor is the guardian
	SELECT count(*) INTO countGuardian FROM i3s_link_users l
	INNER JOIN i3s_user u1 ON l.user_id_from = u1.user_id
	INNER JOIN i3s_user u2 ON l.user_id_to = u2.user_id
	INNER JOIN i3s_status s1 ON u1.status_code = s1.status_code
	INNER JOIN i3s_status s2 ON u2.status_code = s2.status_code
	WHERE l.user_id_from = fromUserID AND l.user_id_to = toUserID
	AND l.is_guardian
	AND s1.is_active AND s2.is_active;
	-- RAISE NOTICE 'from user = %, to user = %, guardian count = %', fromUserID, toUserID, countGuardian;

	-- TODO Are there other relationships to check?
	-- Check counts and determine if this transfer is allowed
	IF ok THEN
		IF countGuardian < 1 THEN
			ok := FALSE;
			msg := 'Transfer is not allowed from ' || fromUserID || ' to ' || toUserID;
			RAISE NOTICE '%', msg;
		END IF;
	END IF;

	--
	--
	-- Validation passed - execute the transfer
	--
	--

	today := CURRENT_DATE;

	IF ok THEN
		-- 24 Mar 2025 - switched to SELECT / PERFORM FOR UPDATE
		-- Set isolation level to ensure integrity
		-- SET TRANSACTION ISOLATION LEVEL 'SERIALIZABLE';
		
		-- Lock the account rows
		PERFORM 1 FROM i3s_account WHERE account_id = pFromAccount FOR UPDATE;
		PERFORM 1 FROM i3s_account WHERE account_id = pToAccount FOR UPDATE;

		-- Debiting the transferor's account
		-- Ensures limits are not burst during the update
		-- Updates the daily limit used
		RAISE NOTICE 'Debiting % from user = %, account = %', pAmount, fromUserID, pFromAccount;
		rowid := NULL;
		UPDATE i3s_account AS a SET balance = balance - pAmount,
			daily_limit_date = CASE
				WHEN daily_limit_date IS NULL OR daily_limit_used IS NULL OR daily_limit_date <> today THEN today
				ELSE daily_limit_date
			END,
			daily_limit_used = CASE
				WHEN daily_limit_date IS NULL OR daily_limit_used IS NULL OR daily_limit_date <> today THEN pAmount
				ELSE daily_limit_used + pAmount
			END
		FROM i3s_status AS s
		WHERE s.status_code = a.status_code
			AND s.is_active 
			AND account_id = pFromAccount
			AND (pAmount >= min_trx_limit)
			AND (max_trx_limit IS NULL OR pAmount <= max_trx_limit)
			AND balance >= pAmount
			AND (min_balance IS NULL OR balance - pAmount >= min_balance)
			AND (daily_limit_date IS NULL OR daily_limit IS NULL OR daily_limit_date = today AND daily_limit_used + pAmount <= daily_limit)
				RETURNING a.id, a.daily_limit_date, a.daily_limit INTO rowID, dailyLimitDate, dailyLimit;
		IF rowID IS NULL THEN
			-- Transfer failed
			ok := FALSE;
			
			-- Give a descriptive error
			msg := 'Failed on transferor''s side';
			t_is_active := NULL;
			SELECT s.is_active, a.balance, a.min_balance, a.max_balance, a.daily_limit, a.daily_limit_date, a.daily_limit_used, a.min_trx_limit, a.max_trx_limit
				INTO t_is_active, t_balance, t_min_balance, t_max_balance, t_daily_limit, t_daily_limit_date, t_daily_limit_used, t_min_trx_limit, t_max_trx_limit
			FROM i3s_account AS a
			INNER JOIN i3s_status s ON a.status_code = s.status_code
				WHERE a.account_id = pFromAccount;
			IF t_is_active IS NOT NULL THEN
				IF t_is_active = FALSE THEN
					msg := 'Transferor''s account is not active';
				ELSIF pAmount < t_min_trx_limit THEN
					msg := 'Amount ' || pAmount || ' is below the minimum transaction limit of ' || t_min_trx_limit;
				ELSIF t_max_trx_limit IS NOT NULL AND pAmount > t_max_trx_limit THEN
					msg := 'Amount ' || pAmount || ' is above the maximum transaction limit of ' || t_max_trx_limit;
				ELSIF t_balance < pAmount THEN
					msg := 'Insufficient balance available to transfer the amount of ' || pAmount;
				ELSIF t_min_balance IS NOT NULL AND t_balance - pAmount < t_min_balance THEN
					msg := 'Insufficient minimum balance of ' || t_min_balance || ' after transfer';
				ELSIF t_daily_limit_date IS NOT NULL AND t_daily_limit IS NOT NULL AND t_daily_limit_used + pAmount > t_daily_limit THEN
					msg := 'Amount ' || pAmount || ' exceeds daily transfer limit';
				END IF;
			END IF;

			RAISE NOTICE '%', msg;

			ROLLBACK;
		END IF;
	END IF;

	IF ok THEN
		-- Crediting the transferee's account
		RAISE NOTICE 'Crediting % to user = %, account = %', pAmount, toUserID, pToAccount;
		rowid := NULL;
		UPDATE i3s_account AS a SET balance = balance + pAmount
		FROM i3s_status AS s
		WHERE s.status_code = a.status_code
			AND s.is_active 
			AND account_id = pToAccount
			AND (max_balance IS NULL OR balance + pAmount <= max_balance)
				RETURNING a.id INTO rowID;
		IF rowID IS NULL THEN
			ok := FALSE;

			-- Give a descriptive error
			msg := 'Failed on transferee''s side';
			t_is_active := NULL;
			SELECT s.is_active, balance, min_balance, max_balance, daily_limit, daily_limit_date, daily_limit_used, min_trx_limit, max_trx_limit
				INTO t_is_active, t_balance, t_min_balance, t_max_balance, t_daily_limit, t_daily_limit_date, t_daily_limit_used, t_min_trx_limit, t_max_trx_limit
			FROM i3s_account AS a
			INNER JOIN i3s_status s ON a.status_code = s.status_code
				WHERE a.account_id = pToAccount;
			IF t_is_active IS NOT NULL THEN
				IF t_is_active = FALSE THEN
					msg := 'Transferee''s account is not active';
				ELSIF t_max_balance IS NOT NULL AND t_balance + pAmount < t_max_balance THEN
					msg := 'Exceeding maximum balance of ' || t_max_balance || ' after transfer';
				END IF;
			END IF;

			RAISE NOTICE '%', msg;

			ROLLBACK;
		END IF;
	END IF;

	IF ok THEN
		-- Insert a row each into transferor's and transferee's accounts
		-- The debit transaction
		-- TODO Calculate transaction fee
		t_trx_fee := 0;
		INSERT INTO i3s_account_trx (account_id, trx_id, trx_type, descript, amount, trx_fee, is_sync, is_success, err_reason)
			VALUES (pFromAccount, trxID, 'T', pDescript, -pAmount, t_trx_fee, false, ok, msg);

		-- The credit transaction
		-- TODO Calculate transaction fee
		t_trx_fee := 0;
		INSERT INTO i3s_account_trx (account_id, trx_id, trx_type, descript, amount, trx_fee, is_sync, is_success, err_reason)
			VALUES (pToAccount, trxID, 'T', pDescript, pAmount, t_trx_fee, false, ok, msg);

		-- Commit this transaction
		COMMIT;
	END IF;
END;
$$
LANGUAGE plpgsql;
