SELECT pd.packID, pb.receiptAddress AS wallet_address, pb.templateId AS template_id, nm.nftId AS nft_id, pd.momentsInPack AS moments_in_pack, pd.tx_id as tx_id

FROM (

-- Query for PackBought events

SELECT EVENT_DATA:receiptAddress AS receiptAddress, EVENT_DATA:templateId AS templateId, tx_id

FROM flow.core.fact_events

WHERE EVENT_CONTRACT LIKE '%AFL%' -- 0x8f9231920da9af6d.AFLPack

AND EVENT_TYPE = 'PackBought'

AND TX_SUCCEEDED = TRUE

AND BLOCK_HEIGHT > 46636000

) AS pb

JOIN (

-- Query for NFTMinted events

SELECT EVENT_DATA:mintNumber AS mintNumber, EVENT_DATA:nftId AS nftId, EVENT_DATA:templateId AS templateId, tx_id

FROM flow.core.fact_events

WHERE EVENT_CONTRACT LIKE '%AFL%' -- AFL NFT

AND EVENT_TYPE = 'NFTMinted'

AND TX_SUCCEEDED = TRUE

AND BLOCK_HEIGHT > 46636000

) AS nm ON pb.templateId = nm.templateId

LEFT JOIN (

-- Query for PackOpened events

SELECT EVENT_DATA:receiptAddress AS receiptAddress, EVENT_DATA:nftId AS nftId, tx_id

FROM flow.core.fact_events

WHERE EVENT_CONTRACT LIKE '%AFL%' -- Pack

AND EVENT_TYPE = 'PackOpened'

AND TX_SUCCEEDED = TRUE

AND BLOCK_HEIGHT > 46636000

) AS po ON nm.nftId = po.nftId

LEFT JOIN (

-- Query for PurchaseDetails events

SELECT EVENT_DATA:receiptAddress AS receiptAddress, EVENT_DATA:templateId AS templateId, EVENT_DATA:momentsInPack AS momentsInPack, tx_id, EVENT_DATA:packID::STRING as packID

FROM flow.core.fact_events

WHERE EVENT_CONTRACT LIKE '%AFL%'

AND EVENT_TYPE = 'PurchaseDetails' -- Pack?

AND TX_SUCCEEDED = TRUE