pool_created as (

select

lower('0x' || substr(topics[1]::string, 27, 40) :: string) as token0,

lower('0x' || substr(topics[2]::string, 27, 40) :: string) as token1,

lower('0x' || substr(REGEXP_SUBSTR_ALL(SUBSTR(data, 3, len(data)), '.{64}')[1]::string, 25, 40 ) :: string) as pool_address

from kaia.core.fact_event_logs

where topics[0] = '0xf04da67755adf58739649e2fb9949a6328518141b7ac9e44aa10320688b04900'

and contract_address = '0x8c7d3063579bdb0b90997e18a770eae32e1ebb08' --kaiaFactory

and tx_succeeded

and block_timestamp::date >= '2024-05-07'

,

swap as (

select

block_timestamp,

block_number,

origin_from_address,

origin_to_address,

ORIGIN_FUNCTION_SIGNATURE,

tx_hash,

event_index,

contract_address,

REGEXP_SUBSTR_ALL(SUBSTR(data, 3, len(data)), '.{64}') as segmented_data,

livequery.utils.udf_hex_to_int(segmented_data[0]::string) :: int as type_in,

case when type_in = 0 then livequery.utils.udf_hex_to_int(segmented_data[2]::string) :: int else livequery.utils.udf_hex_to_int(segmented_data[1]::string) :: int end as amountIn,

case when type_in = 0 then livequery.utils.udf_hex_to_int(segmented_data[1]::string) :: int else livequery.utils.udf_hex_to_int(segmented_data[2]::string) :: int end as amountout,

case when type_in = 0 then lower('0x' || substr(topics[2]::string, 27, 40) :: string) else lower('0x' || substr(topics[1]::string, 27, 40) :: string) end as token_in,

case when type_in = 0 then lower('0x' || substr(topics[1]::string, 27, 40) :: string) else lower('0x' || substr(topics[2]::string, 27, 40) :: string) end as token_out

--livequery.utils.udf_hex_to_int(segmented_data[2]::string) :: int as amount0Out

-- livequery.utils.udf_hex_to_int(segmented_data[3]::string) :: int as amount1Out

from kaia.core.fact_event_logs

where topics[0]::string = '0x0fe977d619f8172f7fdbe8bb8928ef80952817d96936509f67d66346bc4cd10f'

and tx_succeeded

and contract_address IN (select pool_address from pool_created)

and block_timestamp::date >= '2024-05-07'