when len(data) = '1474' then tokenflow_eth.hextoint(substr(data,3+18*64,64))

else tokenflow_eth.hextoint(substr(data,3+19*64,64)) end as sport_id,

case sport_id WHEN '9001' then 'NCAA Mens Football'

WHEN '9002' then 'NFL'

WHEN '9003' then 'MLB'

WHEN '9004' then 'NBA'

WHEN '9005' then 'NCAA'

WHEN '9006' then 'NHL'

WHEN '9007' then 'UFC'

WHEN '9008' then 'WNBA'

WHEN '9010' then 'MLS'

WHEN '9011' then 'EPL'

WHEN '9012' then 'Ligue 1'

WHEN '9013' then 'Bundesliga'

WHEN '9014' then 'La Liga'

WHEN '9015' then 'Serie A'

WHEN '9016' then 'UEFA Champions League'

END AS sport,

case when len(data) = '1410' then HEX_DECODE_STRING(substr(data,3+15*64,24))

when len(data) = '1474' then HEX_DECODE_STRING(substr(data,3+16*64,24))

else HEX_DECODE_STRING(substr(data,3+17*64,24)) end as outhome,

HEX_DECODE_STRING(substr(data,3+13*64,24)) as inhome,

concat('0x',substr(data,27,40)) as game_contract,

concat(inhome,' - ',outhome) as game

FROM optimism.core.fact_event_logs

where topics[0] = '0x889e2060e46779287c2fcbf489c195ef20f5b44a74e3dcb58d491ae073c1370f'

AND origin_to_address = '0x2b91c14ce9aa828ed124d12541452a017d8a2148'

order by event_index

),

contracts as (select tx_hash,

concat('0x',substr(data,27+64,40)) as game_contract

FROM optimism.core.fact_event_logs

WHERE ORIGIN_FUNCTION_SIGNATURE in ('0x6cc5a6ff', '0x9f916c9f', '0x8875eb84')

and ORIGIN_TO_ADDRESS = '0x170a5714112daeff20e798b6e92e25b86ea603c1'

and topics[0] = '0xf3bfbc0822d1ed667a2b298e71e0304f2c1f4685398189d7c39e412f733150f4'