SELECT

c.block_timestamp::date as date,

tx_receiver as governor,

tx_signer as staker,

c.tx_hash,

tx:actions[0]:FunctionCall:deposit::int as stake_amount

FROM near.core.fact_actions_events_function_call c

JOIN near.core.fact_transactions t

ON c.tx_hash = t.tx_hash

WHERE method_name = 'deposit_and_stake' AND NOT CONTAINS(tx::string, 'Smart contract panicked')

),

unstake_amounts AS (

SELECT

c.block_timestamp::date as date,

tx_receiver as governor,

tx_signer as unstaker,

c.tx_hash,

PARSE_JSON(args):amount::int as unstake_amount,

FALSE as is_unstake_all

FROM near.core.fact_actions_events_function_call c

JOIN near.core.fact_transactions t

ON c.tx_hash = t.tx_hash

WHERE method_name = 'unstake' AND TRY_PARSE_JSON(args) is not null AND TRY_CAST(TRY_PARSE_JSON(args):amount::string AS INT) is not null AND NOT CONTAINS(tx::string, 'Smart contract panicked')

SELECT

c.block_timestamp::date as date,

tx_receiver as governor,

tx_signer as unstaker,

c.tx_hash,

REPLACE(REPLACE(REGEXP_SUBSTR(tx::string, 'Total \\d+ unstaked'), 'Total ') , ' unstaked')::int as unstake_amount,

TRUE as is_unstake_all

FROM near.core.fact_actions_events_function_call c