ori_bridge_txs as (

select distinct

tt.block_timestamp,

tt.tx_hash,

tt.origin_from_address as bridger,

first_value(tt.contract_address) over (partition by tt.tx_hash order by "_LOG_ID") as from_token,

first_value(tt.raw_amount) over (partition by tt.tx_hash order by "_LOG_ID") as from_amount,

last_value(tt.contract_address) over (partition by tt.tx_hash order by "_LOG_ID") as bridged_token,

last_value(tt.raw_amount) over (partition by tt.tx_hash order by "_LOG_ID") as bridged_amount,

left(replace(replace(from_amount::string, '.'), 'e+', '000000'), 6)::decimal as upper_limit,

upper_limit * 0.95 as lower_limit,

len(lower_limit::int::string) as lower_limit_len -- for cases where the lower limit is one decimal place lower than upper limit, eg upper = 100000, lower = 95000

from polygon.core.fact_token_transfers tt

join polygon.core.fact_transactions t

on tt.tx_hash = t.tx_hash

where origin_to_address in (

'0xff0047e2156b2d62055a77fe9abbd01baa11d54a', -- zap

'0x1c6ae197ff4bf7ba96c66c5fd64cb22450af9cc8' -- zap 1

) -- zap bridge

and substr(input_data, 75, 64) in (

'0000000000000000000000000000000000000000000000000000000000000001', -- ethereum

'000000000000000000000000000000000000000000000000000000000000a86a', -- avalanche

'000000000000000000000000000000000000000000000000000000000000000a', -- optimism

'0000000000000000000000000000000000000000000000000000000000000038', -- bsc

'0000000000000000000000000000000000000000000000000000000000000089', -- polygon

'000000000000000000000000000000000000000000000000000000000000a4b1' -- arbitrum

)

),

bsc_receive_txs as (

*

from bsc.core.fact_token_transfers

where from_address = lower('0xd123f70ae324d34a9e76b67a27bf77593ba8749f') -- synapse main bridge