select 1207 as pool_id, 'ETH' as token1, 'NEAR' as token2, 18 as decimal1, 24 as decimal2

),

tokens AS (

select 'wrap.near' as contract_address, 'NEAR' as symbol, 24 as decimal, 'non-stable' as type

union

select 'aurora' as contract_address, 'ETH' as symbol, 18 as decimal, 'non-stable' as type

),

add_liquidity as (

SELECT

DATE_trunc('{{Frequency}}' , block_timestamp) as date,

SUM(SPLIT_PART(REGEXP_SUBSTR(logs[0], 'liquidity added \\["(.+)\",' , 1, 1, 'ei'), ' ', 1) / POW(10, decimal1)) as token1,

SUM(SPLIT_PART(REGEXP_SUBSTR(logs[0], ', \"(.+)\"]' , 1, 1, 'ei'), ' ', 1) / POW(10, decimal2)) as token2

FROM near.core.fact_receipts

JOIN tokens t ON t.contract_address ILIKE SPLIT_PART(REGEXP_SUBSTR(logs[0], 'liquidity added \\["(.+)\",' , 1, 1, 'ei'), ' ', 2)

JOIN tokens t2 ON t2.contract_address ILIKE SPLIT_PART(REGEXP_SUBSTR(logs[0], ', \"(.+)\"]' , 1, 1, 'ei'), ' ', 2)

JOIN pools p ON (p.token1 = t.symbol AND p.token2 = t2.symbol)

WHERE receiver_id = 'v2.ref-finance.near'

AND REGEXP_SUBSTR(status_value, 'Success') IS NOT NULL

AND REGEXP_SUBSTR(logs[0], 'liquidity added \\["(.+)\",' , 1, 1, 'ei') IS NOT NULL

AND (p.token1 ILIKE 'ETH' AND p.token2 ILIKE 'NEAR')

AND block_timestamp::date >= CURRENT_DATE - INTERVAL '{{Past_Weeks}} WEEK'

GROUP BY 1

),

remove_liquidity as (

SELECT

DATE_trunc('{{Frequency}}' , t.block_timestamp) as date,

SUM(TRY_PARSE_JSON(TRY_BASE64_DECODE_STRING(tx:actions[0]:FunctionCall:args::string)):min_amounts[0] / POW(10, decimal1)) as token1,

SUM(TRY_PARSE_JSON(TRY_BASE64_DECODE_STRING(tx:actions[0]:FunctionCall:args::string)):min_amounts[1] / POW(10, decimal2)) as token2

FROM near.core.fact_transactions t

JOIN pools p ON p.pool_id = TRY_PARSE_JSON(TRY_BASE64_DECODE_STRING(tx:actions[0]:FunctionCall:args::string)):pool_id

JOIN near.core.fact_receipts h ON h.tx_hash = t.tx_hash

WHERE TRY_PARSE_JSON(TRY_BASE64_DECODE_STRING(tx:actions[0]:FunctionCall:args::string)):min_amounts IS NOT NULL

AND (p.token1 ILIKE 'ETH' AND p.token2 ILIKE 'NEAR')

AND REGEXP_SUBSTR(status_value, 'Success') IS NOT NULL

AND h.receiver_id = 'v2.ref-finance.near'