WITH all_grants_raw AS

(SELECT block_timestamp AS "Proposal Creation Time", DATEADD('day', 7, "Proposal Creation Time") AS "Deposit End Time", tx_id, tx:tx_result.log[0].events[3].attributes[1].value AS "Proposal ID",

tx_sender AS "Proposer",tx:body.messages[0].content.title AS "Proposal Title", (tx:body.messages[0].content.amount[0].amount::number)/1e6 AS "Grant Amount",

tx:body.messages[0].content.amount[0].denom AS "Grant Currency",tx:body.messages[0].content.description AS "Grant Description",

tx:body.messages[0].content.recipient AS "Grant Target Wallet", tx:tx_result.log[0].events[4].attributes[2].key AS "Vote Started Status"

FROM terra.core.fact_transactions

WHERE tx:"body"."messages"[0]."content"."@type"='/cosmos.distribution.v1beta1.CommunityPoolSpendProposal'

AND tx_succeeded = TRUE

-- WHERE tx:body.messages[0].content.description ILIKE '%grant%'

ORDER BY block_timestamp),

all_grants AS

SELECT d.*, c.value

FROM all_grants_raw d,

LATERAL FLATTEN (input=>split(d."Grant Description", ' ')) c

WHERE value ILIKE '%https%'

AND value ILIKE '%discussion%'

),

raw_data AS

(SELECT *, msg:attributes[1].value AS "Proposal ID", REPLACE(msg:attributes[0].value, 'uluna') AS "Deposited Amount Raw",

TRY_TO_NUMERIC("Deposited Amount Raw")*1e-6 AS "Deposited Amount Raw1", IFF("Deposited Amount Raw1" IS NULL, 0, "Deposited Amount Raw1") AS "Deposited Amount"

FROM terra.core.fact_msgs

WHERE msg_type = 'proposal_deposit'

AND "Proposal ID" IN (SELECT "Proposal ID" FROM all_grants)