Draft: 4.(balance_tracker) Addicional reward tracking, more optimalization of processing functions

db/process_delegations.sql

-CREATE OR REPLACE FUNCTION btracker_app.process_delegate_vesting_shares_operation(body hive.operation, _source_op BIGINT, _source_op_block INT)
+CREATE OR REPLACE FUNCTION btracker_app.process_delegate_vesting_shares_operation(body jsonb, _source_op BIGINT, _source_op_block INT)
 RETURNS VOID
 LANGUAGE 'plpgsql'
 AS
 $$
 DECLARE
-    _delegator TEXT;
-    _delegatee TEXT;
+    _delegator INT;
+    _delegatee INT;
     _balance BIGINT;
     _current_balance BIGINT;
     ___current_blocked_balance BIGINT;
 BEGIN
 
-SELECT (body::jsonb)->'value'->>'delegator',
-       (body::jsonb)->'value'->>'delegatee',
-       ((body::jsonb)->'value'->'vesting_shares'->>'amount')::BIGINT
+SELECT (SELECT id FROM hive.btracker_app_accounts_view WHERE name = (body)->'value'->>'delegator'),
+       (SELECT id FROM hive.btracker_app_accounts_view WHERE name = (body)->'value'->>'delegatee'),
+       ((body)->'value'->'vesting_shares'->>'amount')::BIGINT
 INTO _delegator, _delegatee, _balance;
 
 --  DELEGATIONS TRACKING
@@ -81,19 +81,6 @@ INTO _delegator, _delegatee, _balance;
 
   ___current_blocked_balance = GREATEST(_current_balance - _balance , 0);
 
-  --DELEGATOR'S DELAGATION BALANCE DOESN'T CHANGE, BLOCKED VESTS SAVED IN TMP
-
-      INSERT INTO btracker_app.current_account_vests
-      (
-      account,
-      tmp
-      ) 
-      SELECT
-        _delegator,
-        ___current_blocked_balance
-      ON CONFLICT ON CONSTRAINT pk_temp_vests
-      DO UPDATE SET
-          tmp = btracker_app.current_account_vests.tmp + EXCLUDED.tmp;
   
   --DELEGATEE'S RECEIVED VESTS ARE BEING LOWERED INSTANTLY
 
@@ -111,12 +98,12 @@ INTO _delegator, _delegatee, _balance;
   ELSE
 
   --IF DELEGATION BETWEEN ACCOUNTS HAPPENED BUT THE DELEGATION IS HIGHER
-
+      
     ___current_blocked_balance = GREATEST(_balance - _current_balance, 0);
-
+    
   --ADD THE DIFFERENCE TO BOTH ACCOUNTS DELEGATED AND RECEIVED
 
-          INSERT INTO btracker_app.current_account_vests
+      INSERT INTO btracker_app.current_account_vests
       (
       account,
       delegated_vests
@@ -155,22 +142,20 @@ END
 $$
 ;
 
-CREATE OR REPLACE FUNCTION btracker_app.process_account_create_with_delegation_operation(body hive.operation, _source_op BIGINT, _source_op_block INT)
+CREATE OR REPLACE FUNCTION btracker_app.process_account_create_with_delegation_operation(body jsonb, _source_op BIGINT, _source_op_block INT)
 RETURNS VOID
 LANGUAGE 'plpgsql'
 AS
 $$
-DECLARE
-    _delegator TEXT;
-    _delegatee TEXT;
-    _balance BIGINT;
 BEGIN
-
-SELECT (body::jsonb)->'value'->>'creator',
-       (body::jsonb)->'value'->>'new_account_name',
-       ((body::jsonb)->'value'->'delegation'->>'amount')::BIGINT
-INTO _delegator, _delegatee, _balance;
-
+WITH account_create_with_delegation_operation AS 
+(
+SELECT (SELECT id FROM hive.btracker_app_accounts_view WHERE name = (body)->'value'->>'creator') AS _delegator,
+       (SELECT id FROM hive.btracker_app_accounts_view WHERE name = (body)->'value'->>'new_account_name')AS _delegatee,
+       ((body)->'value'->'delegation'->>'amount')::BIGINT AS _balance
+),
+create_delegation AS 
+(
     INSERT INTO btracker_app.current_accounts_delegations 
     (
       delegator,
@@ -184,8 +169,11 @@ INTO _delegator, _delegatee, _balance;
         _delegatee,
         _balance,
         _source_op,
-        _source_op_block;
-
+        _source_op_block
+      FROM account_create_with_delegation_operation
+),
+increase_delegations AS 
+(
       INSERT INTO btracker_app.current_account_vests
       (
       account,
@@ -194,10 +182,11 @@ INTO _delegator, _delegatee, _balance;
       SELECT
         _delegator,
         _balance
+      FROM account_create_with_delegation_operation
       ON CONFLICT ON CONSTRAINT pk_temp_vests
       DO UPDATE SET
-          delegated_vests = btracker_app.current_account_vests.delegated_vests + EXCLUDED.delegated_vests;
-
+          delegated_vests = btracker_app.current_account_vests.delegated_vests + EXCLUDED.delegated_vests
+)
       INSERT INTO btracker_app.current_account_vests
       (
       account,
@@ -206,6 +195,7 @@ INTO _delegator, _delegatee, _balance;
       SELECT
         _delegatee,
         _balance
+      FROM account_create_with_delegation_operation
       ON CONFLICT ON CONSTRAINT pk_temp_vests
       DO UPDATE SET
           received_vests = btracker_app.current_account_vests.received_vests + EXCLUDED.received_vests;
@@ -214,7 +204,7 @@ END
 $$
 ;
 
-CREATE OR REPLACE FUNCTION btracker_app.process_return_vesting_delegation_operation(body hive.operation, source_op BIGINT, source_op_block INT)
+CREATE OR REPLACE FUNCTION btracker_app.process_return_vesting_delegation_operation(body jsonb, source_op BIGINT, source_op_block INT)
 RETURNS VOID
 LANGUAGE 'plpgsql'
 AS
@@ -222,18 +212,17 @@ $$
 BEGIN
 WITH return_vesting_delegation_operation AS 
 (
-SELECT (body::jsonb)->'value'->>'account' AS _account,
-       ((body::jsonb)->'value'->'vesting_shares'->>'amount')::BIGINT AS _balance
+SELECT (SELECT id FROM hive.btracker_app_accounts_view WHERE name = (body)->'value'->>'account') AS _account,
+       ((body)->'value'->'vesting_shares'->>'amount')::BIGINT AS _balance
 )
-  INSERT INTO btracker_app.current_account_vests (account, delegated_vests, tmp)
-  SELECT _account, _balance, _balance
+
+  INSERT INTO btracker_app.current_account_vests (account, delegated_vests)
+  SELECT _account, _balance
   FROM return_vesting_delegation_operation
 
   ON CONFLICT ON CONSTRAINT pk_temp_vests DO UPDATE SET
-    delegated_vests = btracker_app.current_account_vests.delegated_vests - EXCLUDED.delegated_vests,
-    tmp = btracker_app.current_account_vests.tmp - EXCLUDED.tmp
-    ;
-    
+    delegated_vests = btracker_app.current_account_vests.delegated_vests - EXCLUDED.delegated_vests;
+
 END
 $$
 ;