SELECT name , plugin, gauge

FROM (

VALUES

('Infrared HONEY-WETH','0x170d64fb2fcd6bb6639ed0d37b981f6af0e26c3a','0xb96dc7a373ccf6fb1027b4df2436e9c944eed35a'),

('Infrared bHONEY','0x7ab142c0fd1af1ee0c52e80b251b3cf153ad4033','0x157673c5a6e491025103ecf1683b3e054eb02757'),

('Kodiak HONEY-BERA','0xfe12b5f5adb8e20f7c43a6014844479e7dc8dc49','0x635ab6a4d89c1ee404d16bd1b285f6f67faced29'),

('Infrared HONEY-BERA','0x120e4b564d608ab8ea110df0a1429998cca580d0','0x5c3662274cecc6636a1f9683bf3a262e5ea72c1e'),

('BEX HONEY-BERA','0x37e888f8a28bf1da9761bbdd914fa4280da434a8','0x69cd39187c864d068c6ecd651c67086493371cc3'),

('BERPS bHONEY','0x9d7a7198ecfe07414c5e9b3e233878fcc30b9048','0x4a247ecedb329c10cf31ee80a3e2cece3989d78a'),

('Infrared HONEY-BTC','0xb5469370776d165e82d726f36e3e0933c307d4c4','0x38e1913cfa171d59c1a3ab27580c1ce116139809'),

('Infrared HONEY-USDC','0x017a47e19e02d4aaf88738b8c78de2a48904b2e1','0xa2560844c83644178c8064b4157864e4b8faaaeb'),

('Kodiak HONEY-USDC','0xb3d10c15360e444abb2673d772d6f2ee32aaab34','0xa2560844c83644178c8064b4157864e4b8faaaeb'),

('Trifecta YEET-BERA','0x80d7759fa55f6a1f661d5fcbb3bc5164dc63eb4d','0x981e491dd159f17009cf7cd27a98eab995c2fa6c'),

('Infrared iBGT','0xe9ee66a91f540a6e5297b1b1780061278ab1ac78','0x22de70e9b0d1ff2a8388f8528182bef475cfdc21')

) AS X(name,plugin, gauge)

),

plugins as (select concat('0x',substr(TOPICS[1], 27,64)) as plugin, concat('0x',substr(TOPICS[2], 27,64)) as gauge from berachain.testnet.fact_event_logs

where (ORIGIN_TO_ADDRESS=lower('0x580ABF764405aA82dC96788b356435474c5956A7') or ORIGIN_TO_ADDRESS=lower('0x2363BB86cD2ABF89cc059A654f89f11bCceffcA9')

or ORIGIN_TO_ADDRESS=lower('0x1f9505Ae18755915DcD2a95f38c7560Cab149d9C'))

and ORIGIN_FUNCTION_SIGNATURE='0xd8867fc8'

and topics[0]='0xb920b936f556d443772609e0cc06a72c3bb67fc606de10dcdd51323d1ddbc7fc')

,farm_reward_claim as (

select gauge, token, sum(amount) as amount from (

select ORIGIN_TO_ADDRESS as gauge,concat('0x',substr(TOPICS[2], 27,64)) as token,(utils.udf_hex_to_int(substr(data,3,66))::int)/1e18 as amount from berachain.testnet.fact_event_logs

where ORIGIN_TO_ADDRESS in (select gauge from plugins)

and ORIGIN_FUNCTION_SIGNATURE='0xc00007b0'

and topics[0]='0x773189df03a25c28916cd3a8c2609c1c3d8c0386d0fe0958aa3f62c888bac6aa'

group by 1,2

),

prices as (

select value['address'] as token_address, value['price'] as usd_price from (

select live.udf_api(

'GET',

'https://bartio.api.oogabooga.io/v1/prices',