version: 2
models:
  - name: silver__decoded_instructions_combined
    columns:
      - name: BLOCK_TIMESTAMP
        description: "{{ doc('block_timestamp') }}"
        tests:
          - not_null
          - dbt_expectations.expect_row_values_to_have_recent_data:
              datepart: day
              interval: 1
      - name: BLOCK_ID
        description: "{{ doc('block_id') }}"
        tests:
          - not_null
      - name: TX_ID
        description: "{{ doc('tx_id') }}"
        tests:
          - not_null
          - dbt_utils.relationships_where:
              name: dbt_utils_relationships_where_silver__decoded_instructions_combined_nft_sales_tensorswap_cnft_tx_id
              to: ref('silver__nft_sales_tensorswap_cnft')
              field: tx_id
              from_condition: >
                program_id = 'TCMPhJdwDryooaGtiocG1u3xcYbRpiJzb283XfCZsDp' 
                and event_type = 'buy' 
                and _inserted_timestamp between current_date - 7 and current_timestamp() - INTERVAL '4 HOUR'
              to_condition: "_inserted_timestamp >= current_date - 7"
          - dbt_utils.relationships_where:
              name: dbt_utils_relationships_where_silver__decoded_instructions_combined_nft_sales_magic_eden_cnft_onchain_tx_id
              to: ref('silver__nft_sales_magic_eden_cnft_onchain')
              field: tx_id
              from_condition: 
                program_id = 'M3mxk5W2tt27WGT7THox7PmgRDp4m6NEhL5xvxrBfS1' 
                and event_type = 'buyNow' 
                and _inserted_timestamp between current_date - 7 and current_timestamp() - INTERVAL '4 HOUR'
              to_condition: "_inserted_timestamp >= current_date - 7"
          - dbt_utils.relationships_where:
              name: dbt_utils_relationships_where_silver__decoded_instructions_combined_swaps_intermediate_bonkswap_tx_id
              to: ref('silver__swaps_intermediate_bonkswap')
              field: tx_id
              from_condition: >
                program_id = 'BSwp6bEBihVLdqJRKGgzjcGLHkcTuzmSo1TQkHepzH8p' 
                and event_type = 'swap' 
                and decoded_instruction:args:deltaIn:v::int > 200000
                and _inserted_timestamp between current_date - 7 and current_timestamp() - INTERVAL '4 HOUR'
              to_condition: "_inserted_timestamp >= current_date - 7"
          - dbt_utils.relationships_where:
              name: dbt_utils_relationships_where_silver__decoded_instructions_combined_meteora_swaps_tx_id
              to: ref('silver__swaps_intermediate_meteora')
              field: tx_id
              from_condition: >
                program_id IN ('LBUZKhRxPF3XUpBCjp4YzTKgLccjZhTSDM9YuVaPwxo','Eo7WjKq67rjJQSZxS6z3YkapzY3eMj6Xy8X5EQVn5UaB') 
                and event_type = 'swap' 
                and coalesce(decoded_instruction:args:inAmount, decoded_instruction:args:amountIn)::int > 0
                and _inserted_timestamp between current_date - 7 and current_timestamp() - INTERVAL '4 HOUR'
              to_condition: "_inserted_timestamp >= current_date - 7"
          - dbt_utils.relationships_where:
              name: dbt_utils_relationships_where_silver__decoded_instructions_combined_swaps_intermedia_dooar_tx_id
              to: ref('silver__swaps_intermediate_dooar')
              field: tx_id
              from_condition: >
                program_id = 'Dooar9JkhdZ7J3LHN3A7YCuoGRUggXhQaG4kijfLGU2j'
                and event_type = 'swap' 
                and _inserted_timestamp between current_date - 7 and current_timestamp() - INTERVAL '4 HOUR'
                and block_timestamp::date <> '2022-07-04' /* upstream issue with missing inner instructions in events for some blocks in this date, remove this when it gets resolved */
              to_condition: "_inserted_timestamp >= current_date - 7"
          - dbt_utils.relationships_where:
              name: dbt_utils_relationships_where_silver__decoded_instructions_combined_nft_sales_tensorswap_tx_id
              to: ref('silver__nft_sales_tensorswap')
              field: tx_id
              from_condition: >
                program_id = 'TSWAPaqyCSx2KABk68Shruf4rp7CxcNi8hAsbdwmHbN'
                AND event_type IN (
                    'wnsBuySingleListing',
                    'buyNftT22',
                    'buySingleListing',
                    'buyNft',
                    'buySingleListingT22',
                    'wnsBuyNft',
                    'sellNftTokenPool',
                    'sellNftTokenPoolT22',
                    'sellNftTradePoolT22',
                    'wnsSellNftTradePool',
                    'wnsSellNftTokenPool',
                    'sellNftTradePool'
                )
                AND succeeded
                AND _inserted_timestamp between current_date - 7 AND current_timestamp() - INTERVAL '4 HOUR'
              to_condition: "_inserted_timestamp >= current_date - 7"
          - dbt_utils.relationships_where:
              name: dbt_utils_relationships_where_silver__decoded_instructions_combined_meteora_2_liquidity_pool_actions_tx_id
              to: ref('silver__liquidity_pool_actions_meteora_2')
              field: tx_id
              from_condition: >
                program_id = 'Eo7WjKq67rjJQSZxS6z3YkapzY3eMj6Xy8X5EQVn5UaB'
                AND event_type IN ('addBalanceLiquidity','addImbalanceLiquidity','bootstrapLiquidity','removeBalanceLiquidity','removeLiquiditySingleSide')
                AND succeeded
                AND coalesce(decoded_instruction:args:poolTokenAmount::int,-1) <> 0
                AND _inserted_timestamp BETWEEN current_date - 7 AND current_timestamp() - INTERVAL '4 HOUR'
          - dbt_utils.relationships_where:
              name: dbt_utils_relationships_where_silver__decoded_instructions_combined_meteora_dlmm_2_liquidity_pool_actions_tx_id
              to: ref('silver__liquidity_pool_actions_meteora_dlmm_2')
              field: tx_id
              from_condition: >
                program_id = 'LBUZKhRxPF3XUpBCjp4YzTKgLccjZhTSDM9YuVaPwxo'
                AND event_type IN (
                  'removeLiquidityByRange',
                  'removeLiquidity',
                  'removeAllLiquidity',
                  'addLiquidityByStrategyOneSide',
                  'addLiquidityOneSide',
                  'addLiquidity',
                  'addLiquidityByWeight',
                  'addLiquidityByStrategy'
                )
                AND succeeded
                AND _inserted_timestamp BETWEEN current_date - 7 AND current_timestamp() - INTERVAL '4 HOUR'
              to_condition: "_inserted_timestamp >= current_date - 7"
          - dbt_utils.relationships_where:
              name: dbt_utils_relationships_where_silver__decoded_instructions_combined_swaps_intermediate_raydium_clmm_tx_id
              to: ref('silver__swaps_intermediate_raydium_clmm')
              field: tx_id
              from_condition: >
                program_id = 'CAMMCzo5YL8w4VFF8KVHrK22GGUsp5VTaW7grrKgrWqK'
                AND event_type IN ('swapV2','swap')
                AND succeeded
                and _inserted_timestamp between current_date - 7 and current_timestamp() - INTERVAL '4 HOUR'
              to_condition: "_inserted_timestamp >= current_date - 7"
          - dbt_utils.relationships_where:
              name: dbt_utils_relationships_where_silver__decoded_instructions_combined_swaps_intermediate_raydium_v4_amm_tx_id
              to: ref('silver__swaps_intermediate_raydium_v4_amm')
              field: tx_id
              from_condition: >
                program_id = '675kPX9MHTjS2zt1qfr1NYHuzeLXfQM9H24wFSUt1Mp8'
                AND event_type IN ('swapBaseIn','swapBaseOut')
                AND succeeded
                and _inserted_timestamp between current_date - 7 and current_timestamp() - INTERVAL '4 HOUR'
              to_condition: "_inserted_timestamp >= current_date - 7"
          - dbt_utils.relationships_where:
              name: dbt_utils_relationships_where_silver__decoded_instructions_combined_swaps_intermediate_raydium_stable_tx_id
              to: ref('silver__swaps_intermediate_raydium_stable')
              field: tx_id
              from_condition: >
                program_id = '5quBtoiQqxF9Jv6KYKctB59NT3gtJD2Y65kdnB1Uev3h'
                AND event_type IN ('swapBaseIn')
                AND succeeded
                and _inserted_timestamp between current_date - 7 and current_timestamp() - INTERVAL '4 HOUR'
              to_condition: "_inserted_timestamp >= current_date - 7"
          - dbt_utils.relationships_where:
              name: dbt_utils_relationships_where_silver__decoded_instructions_combined_swaps_intermediate_jupiterv6_2_tx_id
              to: ref('silver__swaps_intermediate_jupiterv6_2')
              field: tx_id
              from_condition: >
                program_id = 'JUP6LkbZbjS1jKKwapdHNy74zcZ3tLUZoi5QNyVTaV4'
                AND event_type IN ('exactOutRoute','sharedAccountsExactOutRoute','sharedAccountsRoute','routeWithTokenLedger','route','sharedAccountsRouteWithTokenLedger')
                AND succeeded
                and _inserted_timestamp between current_date - 7 and current_timestamp() - INTERVAL '4 HOUR'
              to_condition: "_inserted_timestamp >= current_date - 7"
          - dbt_utils.relationships_where:
              name: dbt_utils_relationships_where_silver__decoded_instructions_combined_nft_sales_amm_sell_decoded_tx_id
              to: ref('silver__nft_sales_amm_sell_decoded')
              field: tx_id
              from_condition: >
                program_id = 'mmm3XBJg5gk8XJxEKBvdgptZz6SgK4tXvn36sodowMc'
                AND event_type IN ('solMip1FulfillSell','solFulfillSell','solFulfillBuy','solMip1FulfillBuy','solOcpFulfillBuy','solExtFulfillBuy')
                AND succeeded
                and _inserted_timestamp between current_date - 7 and current_timestamp() - INTERVAL '4 HOUR'
              to_condition: "_inserted_timestamp >= current_date - 7"
          - dbt_utils.relationships_where:
              name: dbt_utils_relationships_where_silver__decoded_instructions_combined_swaps_intermediate_jupiterv4_2_tx_id
              to: ref('silver__swaps_intermediate_jupiterv4_2')
              field: tx_id
              from_condition: >
                program_id = 'JUP4Fb2cqiRUcaTHdrPC8h2gNsA2ETXiPDD33WcGuJB'
                AND event_type IN ('route', 'raydiumSwapExactOutput', 'raydiumClmmSwapExactOutput', 'whirlpoolSwapExactOutput')
                AND succeeded
                and _inserted_timestamp between current_date - 7 and current_timestamp() - INTERVAL '4 HOUR'
          - dbt_utils.relationships_where:
              name: dbt_utils_relationships_where_silver__decoded_instructions_combined_swaps_intermediate_raydium_cpmm_tx_id
              to: ref('silver__swaps_intermediate_raydium_cpmm')
              field: tx_id
              from_condition: >
                program_id = 'CPMMoo8L3F4NbTegBCKVNunggL7H1ZpdTHKxQB5qKP1C'
                AND event_type IN ('swapBaseInput','swapBaseOutput')
                AND succeeded
                and _inserted_timestamp between current_date - 7 and current_timestamp() - INTERVAL '4 HOUR'
              to_condition: "_inserted_timestamp >= current_date - 7"
          - dbt_utils.relationships_where:
              name: dbt_utils_relationships_where_silver__decoded_instructions_combined_nft_sales_tensor_bid_tx_id
              to: ref('silver__nft_sales_tensor_bid')
              field: tx_id
              from_condition: >
                (
                  (program_id = 'TB1Dqt8JeKQh7RLDzfYDJsq8KS4fS2yt87avRjyRxMv' AND event_type = 'takeBid')
                  OR (program_id = 'TCMPhJdwDryooaGtiocG1u3xcYbRpiJzb283XfCZsDp' AND event_type = 'takeBidLegacy')
                )
                AND succeeded
                and _inserted_timestamp between current_date - 7 and current_timestamp() - INTERVAL '4 HOUR'
              to_condition: "_inserted_timestamp >= current_date - 7"
          - dbt_utils.relationships_where:
              name: dbt_utils_relationships_where_silver__decoded_instructions_combined_marinade_liquid_staking_actions_tx_id
              to: ref('silver__marinade_liquid_staking_actions')
              field: tx_id
              from_condition: >
                program_id = 'MarBmsSgKXdrN1egZf5sqe1TMai9K1rChYNDJgjq7aD'
                AND event_type IN ('deposit', 'depositStakeAccount', 'orderUnstake', 'claim')
                AND succeeded
                and _inserted_timestamp between current_date - 7 and current_timestamp() - INTERVAL '4 HOUR'
              to_condition: "_inserted_timestamp >= current_date - 7"
          - dbt_utils.relationships_where:
              name: dbt_utils_relationships_where_silver__decoded_instructions_combined_nft_sales_magic_eden_v2_decoded_tx_id
              to: ref('silver__nft_sales_magic_eden_v2_decoded')
              field: tx_id
              from_condition: >
                program_id = 'M2mx93ekt1fmXSVkTrUL9xVFHkmME8HTUi5Cyc5aF7K'
                AND event_type IN ('mip1ExecuteSaleV2', 'executeSaleV2', 'ocpExecuteSaleV2', 'coreExecuteSaleV2', 'extExecuteSaleV2')
                AND succeeded
                and _inserted_timestamp between current_date - 7 and current_timestamp() - INTERVAL '4 HOUR'
              to_condition: "_inserted_timestamp >= current_date - 7"
      - name: SIGNERS
        description: "{{ doc('signers') }}"
      - name: SUCCEEDED
        description: "{{ doc('tx_succeeded') }}"
      - name: INDEX
        description: "{{ doc('event_index') }}"
        tests:  
          - not_null
      - name: INNER_INDEX
        description: "{{ doc('inner_index') }}"
      - name: PROGRAM_ID
        description: "{{ doc('program_id') }}"
      - name: EVENT_TYPE
        description: "{{ doc('event_type') }}"
      - name: DECODED_INSTRUCTION
        description: An instruction specifies which program it is calling, which accounts it wants to read or modify, and additional data that serves as auxiliary input to the program
        tests: 
          - not_null:
              where: block_timestamp::date > current_date - 30
      - name: EVENT_TYPE
      - name: _INSERTED_TIMESTAMP
        description: "{{ doc('_inserted_timestamp') }}"
        tests: 
          - not_null