with

linear_staking as (

select BLOCK_TIMESTAMP, try_parse_json(CLEAN_LOG) as log,

log:data[0]:account_id as account,

case when try_parse_json(CLEAN_LOG):event='stake' then 'stake' else 'unstake' end as event,

coalesce(log:data[0]:staked_amount::double/1e24, log:data[0]:unstaked_amount::double/1e24) as amount,

log:data[0]:new_stake_shares::double/1e24 as new_stake_shares

from near.core.fact_logs

where RECEIVER_ID='linear-protocol.near'

and try_parse_json(CLEAN_LOG):standard='linear'

and try_parse_json(CLEAN_LOG):event in ('instant_unstake','stake','unstake')),

linear_staking_rewards as (

select BLOCK_TIMESTAMP, try_parse_json(CLEAN_LOG) as log,

log:data[0]:validator_id as validator_id,

'reward' as event,

log:data[0]:rewards::double/1e24 as amount,

log:data[0]:new_stake_shares::double/1e24 as new_stake_shares

from near.core.fact_logs

where RECEIVER_ID='linear-protocol.near'

and try_parse_json(CLEAN_LOG):standard='linear'

and try_parse_json(CLEAN_LOG):event in ('epoch_update_rewards')),

metapool_staking as (

select BLOCK_TIMESTAMP, try_parse_json(CLEAN_LOG) as log,

coalesce(log:account_id, log:account) as account,

case when try_parse_json(CLEAN_LOG):event='STAKE' then 'stake' else 'unstake' end as event,

coalesce(log:amount::double/1e24, log:near::double/1e24, log:near::double/1e24) as amount

from near.core.fact_logs

where RECEIVER_ID='meta-pool.near'

and try_parse_json(CLEAN_LOG):event in ('STAKE','D-UNSTK','LIQ.U')),

metapool_staking_rewards as (

select *, REGEXP_SUBSTR(CLEAN_LOG, 'rewards:(\\d+)', 1, 1, 'e', 1)::double/1e24 as amount, 'reward' as event