core_delegations as (

select

block_timestamp,

tx_hash,

contract_address,

origin_from_address,

origin_to_address,

origin_function_signature,

event_name,

split_part(address_name, 'validator: ', 2) as operator,

decoded_log:agent as operator_address,

decoded_log:delegator as delegator,

decoded_log:blockHeight as bitcoin_block_number,

lower(to_char(reverse(try_to_binary(replace(decoded_log:txid, '0x', ''))))) as bitcoin_tx_id,

decoded_log:outputIndex as bitcoin_output_index,

concat('0', trim(decoded_log:script, '0x')) as bitcoin_script

from

core.core.ez_decoded_event_logs logs

left join core.core.dim_labels labels on logs.decoded_log:agent = labels.address

where

tx_succeeded

and contract_address = '0x0000000000000000000000000000000000001007' -- Staking Contract

and origin_to_address = '0x0000000000000000000000000000000000001007'

and topics[0] = '0xd9bac531f4c140de07ef9de431f5233b0d6ead3f0aa7834fcffd828c882dfdd2' -- Bitcoin Staking on Core Chain

),

stakings as (

select

bitcoin.block_number,

bitcoin.block_timestamp,

bitcoin.tx_id,

bitcoin.index,

core.tx_hash as core_staking_tx_hash,

bitcoin.pubkey_script_address as bitcoin_delegator,